using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.SQLite;

using System.IO;

using LFP_Manager.DataStructure;
using LFP_Manager.Function;
using LFP_Manager.Utils;

namespace LFP_Manager
{
    public partial class fmxExcelFile : DevExpress.XtraEditors.XtraForm
    {
        #region VARIABLES

        string[] dbFiles;
        //private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        //private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        //Workbook workbook;
        DataSet dsCell;
        DataTable dtCell;

        CommConfig Config;

        #endregion

        #region CONSTRUCTORS

        public fmxExcelFile(CommConfig aConfig)
        {
            InitializeComponent();

            Config = aConfig;
            //tbSchema.Text = "select * from TInventoryData" + "\r\n" + "where pcb_sn like 'BMU18SA20120169%'";
        }

        #endregion

        #region FORM EVENT
        private void fmxExcelFile_Load(object sender, EventArgs e)
        {
            string dbfilePath = Path.GetDirectoryName(Application.ExecutablePath) + @"\db";
            dbFiles = Directory.GetFiles(dbfilePath, "*.db", SearchOption.AllDirectories);

            if (dbFiles.Length > 0)
            {
                for (int i = 0; i < dbFiles.Length; i++)
                {
                    cbLotNo.Properties.Items.Add(Path.GetFileName(dbFiles[i]));
                }
                cbLotNo.SelectedIndex = 0;
            }
        }
        #endregion

        // Copy Table Value
        // INSERT INTO TModuleResultBak SELECT * FROM TModuleResult
        // INSERT INTO [TModuleBmsBak] (sn, result, module_ocv_b)  SELECT sn, result, module_ocv_b FROM TModuleBms

        #region BUTTON EVENT
        private void btnFindFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog oDialog = new OpenFileDialog();
            oDialog.DefaultExt = "*.*";
            oDialog.Filter = "excel files 2003 (*.xls)|*.xls|excel files 2007 (*.xlsx)|*.xlsx|All files (*.*)|*.*";

            if (oDialog.ShowDialog() == DialogResult.OK)
            {
                edFileName.Text = oDialog.FileName;

                try
                {
                    dsCell = csExcelControl.OpenExcelDB(edFileName.Text);

                    cbResultList.Properties.Items.Clear();
                    for (int i = 0; i < dsCell.Tables.Count; i++)
                    {
                        cbResultList.Properties.Items.Add(dsCell.Tables[i].TableName);
                    }
                    cbResultList.SelectedIndex = 0;

                    dtCell = dsCell.Tables[0];

                    gridCell.DataSource = dtCell;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void btnDbFileFind_Click(object sender, EventArgs e)
        {
            OpenFileDialog oDialog = new OpenFileDialog();
            oDialog.DefaultExt = "*.*";
            oDialog.Filter = "db files (*.db)|*.db|All files (*.*)|*.*";

            if (oDialog.ShowDialog() == DialogResult.OK)
            {
                Forms.fmxWait WaitForm = new Forms.fmxWait();
                WaitForm.StartPosition = FormStartPosition.CenterScreen;

                edFileName.Text = oDialog.FileName;

                try
                {
                    WaitForm.Show();

                    int count = 0;
                    DateTime bakTime = new DateTime();
                    DataTable dtRD = new DataTable();
                    DataTable dtWR = new DataTable();
                    if (csDbUtils.DbQuaryExcuteToDataTable(edFileName.Text, "select * from TModuleValue", ref dtRD) > 0)
                    {
                        string dbfilepath = Path.GetDirectoryName(edFileName.Text);
                        string dbfilename_new = dbfilepath + @"\" + Path.GetFileNameWithoutExtension(edFileName.Text) + @"_new.db";
                        csDbUtils.CreateLogDbFile(dbfilename_new);

                        foreach (DataRow aRow in dtRD.Rows)
                        {
                            DateTime bTime = Convert.ToDateTime(String.Format("{0:yyyy/MM/dd HH:mm:ss}", aRow["create_date"]));
                            if (bakTime != bTime)
                            {
                                csDbUtils.DbInsertDataToDbFile(dbfilename_new, aRow);
                                bakTime = bTime;
                            }
                            //aRow["create_date"] = String.Format("{0:yyyy/MM/dd HH:mm:ss}", aRow["create_date"]);

                            WaitForm.SetDescription(String.Format("{0}//{1}", count, dtRD.Rows.Count));
                            Application.DoEvents();
                            count++;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    if (WaitForm != null)
                        WaitForm.Close();
                }
            }
        }

        private void btnExcuteTCell_Click(object sender, EventArgs e)
        {
            if (tbSchema.Text != string.Empty)
            {
                try
                {
                    //string path = cbLotNo.Text.Substring(0, 9);
                    //string path = cbLotNo.Text;
                    string path = dbFiles[cbLotNo.SelectedIndex];
                    DataTable aaa = csDbUtils.DbSqlExcuteA3(path, tbSchema.Text);
                    if (aaa != null)
                    {
                        gridCell.DataSource = aaa;
                        edResultCount.Text = String.Format("{0}", aaa.Rows.Count);

                        if (aaa.Rows.Count > 0)
                        {
                            var ddd = aaa.Rows[0]["module_sn"];
                            byte[] ccc = Encoding.UTF8.GetBytes(ddd.ToString());
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
            }
        }

        private void btnOpenSchema_Click(object sender, EventArgs e)
        {
            OpenFileDialog oDialog = new OpenFileDialog();
            oDialog.DefaultExt = "*.*";
            oDialog.Filter = "sql files (*.sql)|*.sql|All files (*.*)|*.*";

            if (oDialog.ShowDialog() == DialogResult.OK)
            {
                string text = System.IO.File.ReadAllText(oDialog.FileName);
                tbSchema.Text = text;
            }
        }

        private void btnQueryExcute_Click(object sender, EventArgs e)
        {
            if (tbSchema.Text != string.Empty)
            {
                gridCell.DataSource = csDbUtils.DbSqlExcuteA(Application.ExecutablePath, tbSchema.Text, "mBMS_16S");
            }
        }

        private void btnInsertDbByTable_Click(object sender, EventArgs e)
        {
        }

        private void btnCheckGroupData_Click(object sender, EventArgs e)
        {
            tbSchema.Text = "select * from TInventoryData";
        }

        private void btnCreateNewDb_Click(object sender, EventArgs e)
        {
            csDbUtils.DbCreateLOG(Config);
        }

        private void btnCheckMeasData_Click(object sender, EventArgs e)
        {
            tbSchema.Text = "delete from TInventoryData where pcb_sn=''";
        }
        private void btnOutputAll_Click(object sender, EventArgs e)
        {
            if (cbResultList.Properties.Items.Count > 0)
            {
                for (int i = 0; i < cbResultList.Properties.Items.Count; i++)
                {
                    string sn = cbResultList.Properties.Items[i].ToString();
                    MakeOutpuDataExcel(sn, cbLotNo.Text);
                }
            }
        }
        private void btnModuleResult_Click(object sender, EventArgs e)
        {
            //
            try
            {
                string dbFilePath = dbFiles[cbLotNo.SelectedIndex];
                string sql = String.Format("SELECT * FROM TInventoryData")
                    + String.Format(" where pcb_sn like '{0}%'", tbPcbSn.Text)
                    ;
                DataTable dtBmsData = csDbUtils.DbSqlExcuteA3(dbFilePath, sql);
                dtBmsData.TableName = "TInventoryData";

                if ((dtBmsData != null) && (dtBmsData.Rows.Count > 0))
                {
                    cbResultList.Properties.Items.Clear();

                    for (int i = 0; i < dtBmsData.Rows.Count; i++)
                    {
                        cbResultList.Properties.Items.Add(String.Format("{0}", dtBmsData.Rows[i]["pcb_sn"]));
                    }
                    gridCell.DataSource = dtBmsData;
                    edResultCount.Text = dtBmsData.Rows.Count.ToString();
                    cbResultList.SelectedIndex = 0;
                }
                else
                {
                    MessageBox.Show(String.Format("No TModuleResult data - {0}", dbFilePath));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void btnResultListClear_Click(object sender, EventArgs e)
        {
            cbResultList.Properties.Items.Clear();
        }

        private void btnBmsBmsDataMerse_Click(object sender, EventArgs e)
        {
        }

        private void btnOpenDbFolder_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process ps = new System.Diagnostics.Process();
            ps.StartInfo.FileName = "explorer.exe";
            ps.StartInfo.Arguments = csLog.GetDbFolder(Application.ExecutablePath);
            ps.StartInfo.WorkingDirectory = csLog.GetDbFolder(Application.ExecutablePath);
            ps.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;

            ps.Start();
        }
        #endregion

        #region COMPONENT EVETNT
        private void cbResultList_SelectedIndexChanged(object sender, EventArgs e)
        {
            string tbName = "";
            tbName = cbResultList.Text;

            if ((dtCell != null) && (dtCell.Rows.Count > 0))
            {
                if (tbName != "")
                {
                    int index = cbResultList.SelectedIndex;
                    dtCell = dsCell.Tables[index];
                    gridCell.DataSource = dtCell;
                }
            }
        }
        #endregion

        #region MAKE OUTPUT FUNCTION
        private void MakeOutpuDataExcel(string mSN, string dbFile)
        {
            DateTime aDate = DateTime.Now;
            string filename = String.Format("{0}_PR_57150.xls", mSN);
            string filepath =
                Path.GetDirectoryName(Application.ExecutablePath)
                + String.Format(@"\output\{0}\", csDbUtils.MakeLotNumber(mSN))
                + filename;

            DataTable dtModuleResult = new DataTable();
            DataTable dtCellResult = new DataTable();

            string sql;
            sql = String.Format("SELECT * FROM TModuleResult where m_sn = {0}", mSN);
            dtModuleResult = csDbUtils.GetDataTableBySelectFromDbName(dbFile, sql, "TModuleResult", "mBMS-16S");
            dtModuleResult.TableName = "TModuleResult";
            dtModuleResult.AcceptChanges();

            sql = String.Format("SELECT * FROM TCellResult where m_sn = {0}", mSN);
            dtCellResult = csDbUtils.GetDataTableBySelectFromDbName(dbFile, sql, "TCellResult", "mBMS-16S");
            dtCellResult.TableName = "TCellResult";
            dtCellResult.AcceptChanges();

            if (dtModuleResult.Rows.Count == 0) throw new Exception(String.Format("No ModuleResult data ({0})", mSN));
            if (dtCellResult.Rows.Count == 0) throw new Exception(String.Format("No CellResult data ({0})", mSN));

            csExcelExport.ExportToExcel(dtModuleResult, filepath);
            csExcelExport.ExportToExcel(dtCellResult, filepath, false);
        }

        private DataTable GetModuleResultTable(string dbFileName)
        {
            DateTime aDate = DateTime.Now;

            DataTable dtModuleResult = new DataTable();

            string sql;
            sql = String.Format("SELECT * FROM TModuleResult");
            dtModuleResult = csDbUtils.GetDataTableBySelectFromDbName(dbFileName, sql, "TModuleResult", "mBMS-16S");
            dtModuleResult.TableName = "TModuleResult";
            dtModuleResult.AcceptChanges();

            if (dtModuleResult.Rows.Count == 0) throw new Exception(String.Format("No ModuleResult data ({0})", dbFileName));

            return dtModuleResult;
        }

        #endregion

    }
}