using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;

namespace SCAN
{
    class UnidentifySerialNo
    {
        public static string table_name = "Undetect_sno";
        public static List<UnidentifySerialNo> unidentifySerialNos { get; set; }

        private static int REMOVE_OLD_EXPIRED_BEFORE_DAY = 10;


        public int Id { get; set; }
        public string Barcode_txt { get; set; }
        public string Create_date { get; set; }
        public string Create_time { get; set; }
        public string Create_by { get; set; }
        public int Export { get; set; }

        public static class Column
        {
            public static string id = "id";
            public static string barcode_txt = "barcodetxt";
            public static string create_date = "create_date";
            public static string create_time = "create_time";
            public static string create_by = "create_by";
            public static string export = "export";
        }

        public static class DisplayColumnName
        {
            public static string id = "Id";
            public static string barcode_txt = "Serial No";
            public static string create_date = "Date";
            public static string create_time = "Time";
            public static string create_by = "User";
            public static string export = "Export";
        }

        public UnidentifySerialNo(int Id, 
            string barcode_txt, 
            string create_date,
            string create_time,
            string create_by,
            int export
            )
        {
            this.Id = Id;
            this.Barcode_txt = barcode_txt;
            this.Create_date = create_date;
            this.Create_time = create_time;
            this.Create_by = create_by;
            this.Export = export;
        }

        public UnidentifySerialNo(string[] token, string user)
        {
            this.Barcode_txt = token[0];
            this.Create_date = token[1];
            this.Create_time = token[2];
            this.Create_by = user;
        }

        public StringBuilder getInsertSql()
        {
            StringBuilder stringBuilder = new StringBuilder(
                "INSERT INTO " + table_name + "(" +
                Column.barcode_txt + "," +
                Column.create_date + "," +
                Column.create_time + "," +
                Column.create_by +
                " ) VALUES(" +
                "\"" + this.Barcode_txt + "\"," +
                "\"" + this.Create_date + "\"," +
                "\"" + this.Create_time + "\"," +
                "\"" + this.Create_by + "\"" +
                ");");

            return stringBuilder;
        }

        public static StringBuilder getSelectSql()
        {
            StringBuilder stringBuilder = new StringBuilder(
                "SELECT " +
                Column.create_date + "," +
                Column.create_time + "," +
                Column.barcode_txt + ", 'NG'," +
                Column.create_by +
                " FROM " + table_name +
                " WHERE " + Column.export + "=0");

            return stringBuilder;
        }

        public static StringBuilder getCsvHeaderColumnName(string delimiter)
        {
            StringBuilder stringBuilder = new StringBuilder(
                DisplayColumnName.create_date + delimiter +
                DisplayColumnName.create_time + delimiter +
                DisplayColumnName.barcode_txt + delimiter +
                "Status" + delimiter +
                DisplayColumnName.create_by);

            return stringBuilder;
        }

        /**
         * Function: getRemoveExpireSql
         * Description: get SQL that create date is older than expire date
         * Return: SQL column => filename, maxdate
         * */
        public static string getRemoveExpireSql()
        {
            string date = DateTime.Now.AddDays(-1 * REMOVE_OLD_EXPIRED_BEFORE_DAY).ToString("yyMMdd");
            string sql = String.Format(
                @"  DELETE
                    FROM Undetect_sno 
                    WHERE CAST(create_date as decimal) < {0}
                    ", date
                );

            return sql;
        }
    }
}
