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 System.Data.OleDb;
using LFP_Manager.DataStructure;
using LFP_Manager.Function;
using LFP_Manager.Utils;
using DevExpress.Utils.FormShadow;

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

        CommConfig Config;
        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;

        #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 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]["pcb_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(Config, Application.ExecutablePath, tbSchema.Text);
            }
        }

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

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

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

        private void btnCheckMeasData_Click(object sender, EventArgs e)
        {
            tbSchema.Text = "select * from TModuleMeasurement";
        }
        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 btnBmsDuplicateCheck_Click(object sender, EventArgs e)
        {
            List<string> result = new List<string>();
            DateTime aDate = DateTime.Now;

            DataTable dtBattPropValue = new DataTable();
            DataTable dtBmsMatch = new DataTable();
            DataTable dtErrorLog = new DataTable();

            string dbFilePath = dbFiles[cbLotNo.SelectedIndex]; ;
            string sql;

            sql = String.Format("SELECT * FROM TBattPropValue");
            dtBattPropValue = csDbUtils.DbSqlExcuteA3(dbFilePath, sql);
            dtBattPropValue.TableName = "TBattPropValue";

            if (dtBattPropValue.Rows.Count == 0)
            {
                MessageBox.Show("No Battery Property Vaule data", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            Forms.fmxWait WaitForm = new Forms.fmxWait
            {
                StartPosition = FormStartPosition.CenterScreen
            };

            try
            {
                WaitForm.Show();

                sql = String.Format("SELECT * FROM TBmsPcbMatchTable");
                dtBmsMatch = csDbUtils.DbSqlExcuteA3(dbFilePath, sql);
                dtBmsMatch.TableName = "TBmsPcbMatchTable";

                for (int i = 0; i < dtBattPropValue.Rows.Count; i++)
                {
                    try
                    {
                        csDbUtils.BmsPcbMatchDataInsert(dbFilePath, dtBattPropValue.Rows[i]);
                    }
                    catch (SQLiteException sqle)
                    {
                        // Handle DB exception
                        string eMsg = String.Format("[{0}]{1} - [{2:##0}] - {3}", sqle.ErrorCode, sqle.Message, i, dtBattPropValue.Rows[i]["PCB1_SERIAL_NUMBER"]);
                        result.Add(eMsg);
                        csDbUtils.ErrorLogInsert(dbFilePath, dtBattPropValue.Rows[i], "DUPLICATION CHECK", "SQLiteException", eMsg);
                    }
                    catch (IndexOutOfRangeException ie)
                    {
                        // If you think there might be a problem with index range in the loop, for example
                        string eMsg = String.Format("{0} - [{1:##0}] - {2}", ie.Message, i, dtBattPropValue.Rows[i]["PCB1_SERIAL_NUMBER"]);
                        result.Add(eMsg);
                        csDbUtils.ErrorLogInsert(dbFilePath, dtBattPropValue.Rows[i], "DUPLICATION CHECK", "IndexOutOfRangeException", eMsg);
                    }
                    catch (Exception ex)
                    {
                        string eMsg = String.Format("{0} - [{1:##0}] - {2}", ex.Message, i, dtBattPropValue.Rows[i]["PCB1_SERIAL_NUMBER"]);
                        result.Add(eMsg);
                        csDbUtils.ErrorLogInsert(dbFilePath, dtBattPropValue.Rows[i], "DUPLICATION CHECK", "Exception", eMsg);
                    }
                    WaitForm.SetDescription(String.Format("{0}//{1}", i + 1, dtBattPropValue.Rows.Count));
                    System.Windows.Forms.Application.DoEvents();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            finally
            {
                sql = String.Format("SELECT * FROM TErrorLogTable");
                dtErrorLog = csDbUtils.DbSqlExcuteA3(dbFilePath, sql);
                dtErrorLog.TableName = "TErrorLogTable";

                gridCell.DataSource = dtErrorLog;

                if (WaitForm != null)
                    WaitForm.Close();
            }
        }

        private void btnOpenDbFolder_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process ps = new System.Diagnostics.Process();
            ps.StartInfo.FileName = "explorer.exe";
            ps.StartInfo.Arguments = csDbUtils.GetDbFolder(Application.ExecutablePath);
            ps.StartInfo.WorkingDirectory = csDbUtils.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.MakeMdSnLotNumber(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");
            dtModuleResult.TableName = "TModuleResult";
            dtModuleResult.AcceptChanges();

            sql = String.Format("SELECT * FROM TCellResult where m_sn = {0}", mSN);
            dtCellResult = csDbUtils.GetDataTableBySelectFromDbName(dbFile, sql, "TCellResult");
            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");
            dtModuleResult.TableName = "TModuleResult";
            dtModuleResult.AcceptChanges();

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

            return dtModuleResult;
        }

        #endregion



        private void MakeBattPropValue()
        {
        }

    }
}