using System;
using System.Linq;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Bt;
using CsvReadWrite;
using System.Threading;
using Bt.FileLib;
using Bt.SysLib;
using System.IO;
using System.Runtime.InteropServices;
using Bt.CommLib;

namespace SCAN
{
    public partial class Form_DS : Form
    {
        public static Form_DS _Form_Instance;
        private string AppPath;
        private String outputPrefix = "";
        private lib.LogWriter logger = lib.LogWriter.getInstance();

        public Form_DS()
        {
            InitializeComponent();
        }

        private void Form_DS_Load(object sender, EventArgs e)
        {
            // 画面サイズ調整
            if (System.Windows.Forms.Screen.PrimaryScreen.Bounds.Width == 240)
            {
                resolution.ScreenSize.VGAtoQVGA(this);
            }

            // フォームの最大化・最小化ボタン非表示
            this.MaximizeBox = !this.MaximizeBox;
            this.MinimizeBox = !this.MinimizeBox;

            _Form_Instance = this;


            // 実行パスを取得
            String path = this.GetType().Assembly.GetModules()[0].FullyQualifiedName;
            Int32 en = path.LastIndexOf("\\");
            AppPath = path.Substring(0, en);
            Bt.ScanLib.Control.btScanDisable();

            IntPtr pTermId = Marshal.AllocCoTaskMem(Marshal.SizeOf(typeof(Char)) * (32 + 1));
            try
            {
                Bt.SysLib.Terminal.btGetHandyParameter(Bt.LibDef.BT_SYS_PRM_HTNAME, pTermId);
                //MessageBox.Show(Marshal.PtrToStringUni(pTermId), "Terminal Name");
                outputPrefix = Marshal.PtrToStringUni(pTermId) + "_";
            }
            finally
            {
                Marshal.FreeCoTaskMem(pTermId);
            }
        }

        private void btnDs1_Click(object sender, EventArgs e)
        {
            btn_off(sender, e);
            lib.BtCommLib.openUsb();

            // try to connect
            int max_retry = 10;
            while (lib.BtCommLib.checkUsbStatus() != 1 && max_retry > 0) 
            {
                max_retry--;
                Thread.Sleep(1000);
            }

            if (max_retry == 0) {
                MessageBox.Show("Please attach it on the cradle.", "Error", 
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                btn_on(sender, e);
                return;
            }

            lib.BtCommLib.listen(AppPath + "\\KPListen.txt");

            importCSV2Db(Form_Login._Form_LoginInstance.CSV_IMPORT_DIR);

            Form_Menu._Form_MenuInstance.Show();

            this.Close();

        }

        private void btnDs2_Click(object sender, EventArgs e)
        {
            btn_off(sender, e);
            string buffer_dir = Form_Login._Form_LoginInstance.CSV_EXPORT_DIR;

            //mkdir if not exist
            logger.Log("create dir if not exists [" + buffer_dir + "]");
            lib.Utils.mkdir(buffer_dir);

            // Write csv output
            outputCSVFile(buffer_dir);

            // Upload files
            List<FileInfo> files = lib.Utils.listFile(buffer_dir, "*.csv");
            logger.Log(String.Format("Info: {0} file(s) found.", files.Count));

            if (files.Count == 0) { btn_on(sender, e); return; }

            // Check cable connected
            lib.BtCommLib.openUsb();

            // try to connect
            int max_retry = 10;
            while (lib.BtCommLib.checkUsbStatus() != 1 && max_retry > 0)
            {
                max_retry--;
                Thread.Sleep(1000);
            }

            if (max_retry == 0)
            {
                MessageBox.Show("Please attach it on the cradle.", "Error",
                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                btn_on(sender, e);
                return;
            }

            // uploading process
            int ret = lib.BtCommLib.connect(10);
            try
            {
                if (ret == Bt.LibDef.BT_OK)
                {
                    string remote_dir = Form_Login._Form_LoginInstance.CSV_REMOTE_DIR;
                    foreach (FileInfo file in files)
                    {
                        bool result = lib.BtCommLib.uploadFile(file.FullName,
                            remote_dir + Path.GetFileName(file.FullName),
                            true,
                            true
                            );

                        if (result)
                            lib.Utils.removeFile(file.FullName);
                    }
                }
            }
            finally
            {
                lib.BtCommLib.disconnect(10);
            }
            btn_on(sender, e);
        }

        private void btnDs3_Click(object sender, EventArgs e)
        {
           // importCSV2Db(Form_Login._Form_LoginInstance.CSV_IMPORT_DIR);

            Form_Menu._Form_MenuInstance.Show();

            this.Close();
        }

        ///
        /// Import CSV to DB
        ///
        private void importCSV2Db(string buffer_dir)
        {
            logger.Log("create dir if not exists [" + buffer_dir + "]");
            lib.Utils.mkdir(buffer_dir);

            // read csv input file
            List<FileInfo> files = lib.Utils.listFile(buffer_dir, "*.csv");
            if (files.Count == 0) return;


            Form frmMsg = new Form_Msg("Please wait while import data", Color.Blue);
            frmMsg.ShowDialog();
            Thread.Sleep(200);

            logger.Log(String.Format("Importing CSV {0} files", files.Count));

            string line;

            foreach (FileInfo file in files)
            {
                int counter = 0;
                StreamReader streamReader = new StreamReader(file.FullName);
                try
                {
                    string filename = Path.GetFileNameWithoutExtension(file.FullName);
                    // remove datetime in case of multiple files e.g. ht_ob.20xx.csv
                    if (filename.IndexOf('.') > 0)
                        filename = filename.Substring(0, filename.IndexOf('.'));

                    logger.Log(String.Format("Filename: {0}", filename));

                    SCAN.lib.DbTable table;
                    bool isMasterTable = false;
                    Dictionary<string, string> cols = null;
                    switch (filename.ToLower())
                    {
                        case "customer":
                            table = new SCAN.lib.db.Customer();
                            isMasterTable = true;
                            break;
                        case "item":
                            table = new SCAN.lib.db.Item();
                            isMasterTable = true;
                            break;
                        case "location":
                            table = new SCAN.lib.db.Location();
                            isMasterTable = true;
                            break;
                        case "packagetype":
                            table = new SCAN.lib.db.PackageType();
                            isMasterTable = true;
                            break;
                        case "stock":
                            table = new SCAN.lib.db.Stock();
                            isMasterTable = true;
                            break;
                        case "ht_ob":
                            table = new SCAN.lib.db.HT_OB();
                            cols = new Dictionary<string, string>(table.__COLUMNS);
                            cols["create_date"] = DateTime.Now.ToString("yyyyMMdd");
                            cols["create_time"] = DateTime.Now.ToString("hhmmss");
                            cols["create_by"] = Form_Login._Form_LoginInstance.userName;
                            foreach (string c in new string[]{"id", "pkg_qty_act", "pkg_type_act", "pc_qty_act", "schedule_id", "status"
                                , "update_date", "update_time", "update_by"}) 
                            {
                                cols.Remove(c);
                            }
                            break;
                        default:
                            logger.Log("Filename[" + filename + "] not supported!");
                            continue;
                    }

                    // TRUNCATE TABLE
                    if (isMasterTable)
                        table.remove("1=1");

                    while ((line = streamReader.ReadLine()) != null)
                    {
                        logger.Log("read line: " + line);
                        List<string> csv = lib.CsvParser.Parse(line);

                        try
                        {
                            cols = table.bindCsvData(csv, cols);
                            counter += table.add(cols);
                        }
                        catch (Exception ex) {
                            logger.Log("error on binding: " + ex.Message);
                        }
                    }

                }
                catch (Exception e)
                {
                    logger.Log(e.Message);
                }
                finally
                {
                    streamReader.Close();
                }

                logger.Log(String.Format("total import {0} records.", counter));
            }

            lib.Utils.removeDirectory(buffer_dir, false);

            // Revoke all static master tables
            lib.db.Customer.CustomerList = null;
            lib.db.Location.LocationList = null;
            lib.db.PackageType.BoxSizeList = null;
            lib.db.Item.ItemList = null;

            frmMsg.Close();
        }

        /////////////////////////////////////////////////////////////
        /// CSV output
        /////////////////////////////////////////////////////////////
        private void outputCSVFile(string buffer_dir)
        {
            lib.DbTable[] tables = new lib.DbTable[]{
                new lib.db.HT_IB(),
                new lib.db.HT_IB_RC(),
                new lib.db.HT_IB_RP(),
                new lib.db.HT_OB(),
                new lib.db.HT_OB_RC(),
                new lib.db.HT_OB_RP(),
                new lib.db.HT_ST(),
                new lib.db.HT_CL()
            };

            //string[] status_ = new string[] { "Complete", "Complete", "Complete", "Complete", "Complete", "Complete", "Complete", "Complete" };
            string[] status_ = new string[] { "Confirm", "Complete", "Complete", "Confirm", "Complete", "Complete", "Complete", "Complete" };

            int i = 0;
            string new_text_status = "";
            foreach (lib.DbTable table in tables)
            {
                string create_date =  DateTime.Now.ToString("yyyyMMdd");
                List<string[]> rows = table.get(table.__COLUMNS, "status='" + status_[i] + "' AND sync=0 AND create_date<='"+ create_date +"'", null, null, null);
                lib.FileUtil.Write(buffer_dir + "\\" + table.__TABLE + ".csv",
                    table.toCsvFormat(rows));

                if (status_[i] == "Confirm") { new_text_status = "OK"; } else { new_text_status = status_[i]; }
                
                string sql = @"UPDATE {0} 
                    SET sync=1
                        , update_date='{1}'
                        , update_time='{2}'
                        , update_by='{3}'
                        , status='{4}' 
                    WHERE status='{5}' AND sync=0 AND create_date<='"+ create_date +"'";
                table.executeQuery(String.Format(sql
                    , table.__TABLE
                    , DateTime.Now.ToString("yyMMdd")
                    , DateTime.Now.ToString("HHmmss")
                    , Form_Login._Form_LoginInstance.userName
                    , new_text_status
                    , status_[i]
                    )); 

                
                int REMOVE_OLD_SYNC_BEFORE_DAY = 7;
                sql = @"DELETE FROM {0}
                        WHERE sync=1
                        AND CAST(update_date as decimal) < {1}
                    ";
                table.executeQuery(String.Format(sql
                    , table.__TABLE
                    , DateTime.Now.AddDays(-1 * REMOVE_OLD_SYNC_BEFORE_DAY).ToString("yyMMdd")
                    ));

                i++;
            }

        }

        private void btn_off(object sender, EventArgs e)
        {
            btnDs1.Enabled = false;
            btnDs2.Enabled = false;
            btnDs3.Enabled = false;
            btnDs1.Refresh();
            btnDs2.Refresh();
            btnDs3.Refresh();
        }

        private void btn_on(object sender, EventArgs e)
        {
            btnDs1.Enabled = true;
            btnDs2.Enabled = true;
            btnDs3.Enabled = true;
            btnDs1.Refresh();
            btnDs2.Refresh();
            btnDs3.Refresh();
        }
    }
}
