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

namespace SCAN
{
    public class Schedule
    {
        public static string table_name = "Schedule";
        public static List<Schedule> schedules { get; set; }

        private static int REMOVE_OLD_SUCCESS_BEFORE_DAY = 10;
        private static int REMOVE_OLD_EXPIRED_BEFORE_DAY = 30;


        public int Id { get; set; }
        public string Shipping_date { get; set; }
        public string Customer { get; set; }
        public string Seq_no { get; set; }
        public string Ctn_no { get; set; }
        public string Kjt_no { get; set; }
        public string Tmt_no { get; set; }
        public string Pallet_no { get; set; }
        public string Back_no { get; set; }
        public int Qty { get; set; }
        public int Parts_read_qty { get; set; }
        public int Label_read_qty { get; set; }
        public string Filename { get; set; }
        public string Create_date { get; set; }
        public string Create_time { get; set; }
        public string Create_by { get; set; }
        public string Update_date { get; set; }
        public string Update_time { get; set; }
        public string Update_by { get; set; }
        public string Status { get; set; }
        public string Status1 { get; set; }
        public int Match_count { get; set; }
        public int Export { get; set; }

        public static class Column
        {
            public static string id = "id";
            public static string shipping_date = "shipping_date";
            public static string customer = "customer";
            public static string seq_no = "seq_no";
            public static string ctn_no = "ctn_no";
            public static string kjt_no = "kjt_no";
            public static string tmt_no = "tmt_no";
            public static string pallet_no = "pallet_no";
            public static string back_no = "back_no";
            public static string qty = "qty";
            public static string parts_read_qty = "parts_read_qty";
            public static string label_read_qty = "label_read_qty";
            public static string filename = "filename";
            public static string create_date = "create_date";
            public static string create_time = "create_time";
            public static string create_by = "create_by";
            public static string update_date = "update_date";
            public static string update_time = "update_time";
            public static string update_by = "update_by";
            public static string status = "status";
            public static string status1 = "status1";
            public static string match_count = "match_count";
            public static string export = "export";
        }

        public static class DisplayColumnName
        {
            public static string id = "Id";
            public static string shipping_date = "Shipping date";
            public static string customer = "Customer";
            public static string seq_no = "No";
            public static string ctn_no = "CTN No";
            public static string kjt_no = "KJT P/N";
            public static string tmt_no = "Customer P/N";
            public static string pallet_no = "PALLET NO.";
            public static string back_no = "BACK NO.";
            public static string qty = "Qty";
            public static string parts_read_qty = "Parts read";
            public static string label_read_qty = "Label read";
            public static string filename = "Filename";
            public static string create_date = "Work date";
            public static string create_time = "Work time";
            public static string create_by = "User ID";
            public static string update_date = "Work date";
            public static string update_time = "Work time";
            public static string update_by = "User ID";
            public static string status = "STATUS";
            public static string status1 = "STATUS";
            public static string match_count = "Match count";
            public static string export = "Export";
        }

        public Schedule() { }

        public Schedule(string user, string filename)
        {
            this.Filename = filename;
            this.Create_date = DateTime.Now.ToString("yyMMdd");
            this.Create_time = DateTime.Now.ToString("HHmmss");
            this.Create_by = user;
        }

        public static List<Schedule> LoadCollectionData()
        {
            return schedules;
        }

        public StringBuilder getInsertSql()
        {
            StringBuilder stringBuilder = new StringBuilder(
                "INSERT INTO " + table_name + "(" +
                Column.shipping_date + "," +
                Column.customer + "," +
                Column.seq_no + "," +
                Column.ctn_no + "," +
                Column.kjt_no + "," +
                Column.tmt_no + "," +
                Column.pallet_no + "," +
                Column.back_no + "," +
                Column.qty + "," +
                Column.filename + "," +
                Column.create_date + "," +
                Column.create_time + "," +
                Column.create_by +
                " ) VALUES(" +
                "\"" + this.Shipping_date + "\"," +
                "\"" + this.Customer + "\"," +
                "\"" + this.Seq_no + "\"," +
                "\"" + this.Ctn_no + "\"," +
                "\"" + this.Kjt_no + "\"," +
                "\"" + this.Tmt_no + "\"," +
                "\"" + this.Pallet_no + "\"," +
                "\"" + this.Back_no + "\"," +
                "" + this.Qty+ "," +
                "\"" + this.Filename + "\"," +
                "\"" + this.Create_date + "\"," +
                "\"" + this.Create_time + "\"," +
                "\"" + this.Create_by + "\"" +
                ");");

            return stringBuilder;
        }

        public static StringBuilder getCsvHeaderColumnName(string delimiter)
        {
            StringBuilder stringBuilder = new StringBuilder(
                "#" + delimiter +
                DisplayColumnName.shipping_date + delimiter +
                DisplayColumnName.create_date + delimiter +
                DisplayColumnName.create_time + delimiter +
                DisplayColumnName.seq_no + delimiter +
                DisplayColumnName.kjt_no + delimiter +
                DisplayColumnName.tmt_no + delimiter +
                DisplayColumnName.qty + delimiter +
                DisplayColumnName.update_by + delimiter +
                DisplayColumnName.status);

            return stringBuilder;
        }

        /**
         * Function: getSuccessWorkbookSql
         * Description: get un-exported workbook filename that ready to export
         * Return: SQL column => filename, maxdate
         * */
        public static string getSuccessWorkbookSql()
        {
            string sql =
                @"  SELECT s.filename
	                    , MAX(
		                    CASE s.update_date
			                    WHEN ''
				                    THEN CAST(s.create_date as decimal)
			                    ELSE CAST(s.update_date as decimal)
		                    END
	                    ) AS maxdate
                    FROM Schedule s
                    INNER JOIN
                    (
	                    SELECT filename 
		                    , MIN(status) AS status
		                    , count(*) AS c
	                    FROM
	                    (
		                    SELECT filename, status, count(*)
		                    FROM
		                    (
			                    SELECT filename, 
				                    CASE status
                                        WHEN 'OK'
                                            THEN 'WORKING'
                                        WHEN 'Complete'
                                            THEN 'OK'
						                WHEN ''
							                THEN CASE status1
								                WHEN 'OK'
									                THEN 'WORKING'
								                WHEN 'Complete'
									                THEN 'WORKING'
								                WHEN 'NG'
									                THEN 'NG'
								                ELSE status1
								                END
                                        ELSE status
                                        END status
			                    FROM Schedule
		                    ) a
		                    GROUP BY filename, status
	                    )
	                    GROUP BY filename
	                    HAVING status = 'OK'
	                    AND c = 1
                    ) a
                    ON a.filename = s.filename
                    WHERE s.export = 0
                    GROUP BY s.filename
                    ";

            return sql;
        }

        /**
         * Function: getUnsuccessWorkbookSql
         * Description: get unsuccess workbook filename
         * Return: SQL column => filename, maxdate
         * */
        public static string getUnsuccessWorkbookSql()
        {
            string sql =
                @"  SELECT s.filename
	                    , MAX(
		                    CASE s.update_date
			                    WHEN ''
				                    THEN CAST(s.create_date as decimal)
			                    ELSE CAST(s.update_date as decimal)
		                    END
	                    ) AS maxdate
                    FROM Schedule s
                    INNER JOIN
                    (
	                    SELECT filename 
		                    , MIN(status) AS status
		                    , count(*) AS c
	                    FROM
	                    (
		                    SELECT filename, status, count(*)
		                    FROM
		                    (
			                    SELECT filename, 
				                    CASE status
                                        WHEN 'OK'
                                            THEN 'WORKING'
                                        WHEN 'Complete'
                                            THEN 'OK'
						                WHEN ''
							                THEN CASE status1
								                WHEN 'OK'
									                THEN 'WORKING'
								                WHEN 'Complete'
									                THEN 'WORKING'
								                WHEN 'NG'
									                THEN 'NG'
								                ELSE status1
								                END
                                        ELSE status
                                        END status
			                    FROM Schedule
		                    ) a
		                    GROUP BY filename, status
	                    )
	                    GROUP BY filename
                        HAVING c > 1
                    ) a
                    ON a.filename = s.filename
                    GROUP BY s.filename
                    ";

            return sql;
        }

        /**
         * Function: getRemoveExpireSql
         * Description: get workbook filename that last update date is older than expire day
         * 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(
                @"  SELECT filename, 
                    MAX(
	                    CASE update_date
		                    WHEN ''
			                    THEN CAST(create_date as decimal)
		                    ELSE CAST(update_date as decimal)
	                    END
	                    ) AS maxdate
                    FROM Schedule 
                    GROUP BY filename
                    HAVING maxdate < {0}
                    ", date
                );

            return sql;
        }

        /**
         * Function: getRemoveOldSuccessSql
         * Description: get workbook filename that entire are OK or recheck
         * Return: SQL column => filename, maxdate
         * */
        public static string getRemoveOldSuccessSql()
        {
            string date = DateTime.Now.AddDays(-1 * REMOVE_OLD_SUCCESS_BEFORE_DAY).ToString("yyMMdd");
            string sql = String.Format(
                @"  SELECT s.filename
	                    , MAX(
		                    CASE s.update_date
			                    WHEN ''
				                    THEN CAST(s.create_date as decimal)
			                    ELSE CAST(s.update_date as decimal)
		                    END
	                    ) AS maxdate
                    FROM Schedule s
                    INNER JOIN
                    (
	                    SELECT filename 
		                    , MIN(status) AS status
		                    , count(*) AS c
	                    FROM
	                    (
		                    SELECT filename, status, count(*)
		                    FROM
		                    (
			                    SELECT filename, 
				                    CASE status
                                        WHEN 'OK'
                                            THEN 'WORKING'
                                        WHEN 'Complete'
                                            THEN 'OK'
						                WHEN ''
							                THEN CASE status1
								                WHEN 'OK'
									                THEN 'WORKING'
								                WHEN 'Complete'
									                THEN 'WORKING'
								                WHEN 'NG'
									                THEN 'NG'
								                ELSE status1
								                END
                                        ELSE status
                                        END status
			                    FROM Schedule
		                    ) a
		                    GROUP BY filename, status
	                    )
	                    GROUP BY filename
	                    HAVING status = 'OK'
	                    AND c = 1
                    ) a
                    ON a.filename = s.filename
                    GROUP BY s.filename
                    HAVING maxdate < {0}
                    ", date
                );
            return sql;
        }
    }
}
