using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Windows.Forms;

using System.Data.SQLite;
using System.Data.SqlClient;

using LFP_Manager.DataStructure;

namespace LFP_Manager.Utils
{
    class csDbUtils
    {
        // Query - SELECT * FROM TABLE_NAME like('%neon%',field_name) - 문자를 포함하는 데이터 검색
        public static string DbFileName = @"PR_57150.db";
        public static string DbFilePath = @"\db\";
        //public static string DbFileNameFormat = @"\db\{0}_PR_57150.db";
        public static string MdDbFileNameFormat = @"\db\{0}_{1}.db";        // 0: Lot no., 1: Module type
        public static string DbSchemaTableFileName = @"\sql\DB_SCHEMA_TABLE.sql";
        public static string ModuleTableName = @"TModule";

        public static string LogDbFilePath = @"\db";
        public static string LogDbFileNameFormat = @"\{0}\{1}_{2}_LOG.DB";
        public static string LogDbSchemaTableFileName = @"\sql\LOG_DB_SCHEMA_TABLE.sql";

        public static DateTime Delay(int MS)
        {
            DateTime thisMoment = DateTime.Now;
            TimeSpan duration = new TimeSpan(0, 0, 0, 0, MS);
            DateTime afterMoment = thisMoment.Add(duration);

            while (afterMoment >= thisMoment)
            {
                System.Windows.Forms.Application.DoEvents();

                thisMoment = DateTime.Now;
            }

            return DateTime.Now;
        }

        //트랜잭션 시작
        public static void BeginTran(SQLiteConnection conn)
        {
            SQLiteCommand command = new SQLiteCommand("Begin", conn);
            command.ExecuteNonQuery();
            command.Dispose();
        }

        //트랜잭션 완료
        public static void CommitTran(SQLiteConnection conn)
        {
            SQLiteCommand command = new SQLiteCommand("Commit", conn);
            command.ExecuteNonQuery();
            command.Dispose();
        }

        public static string GetDbFolder(string AppPath)
        {
            string path = System.IO.Path.GetDirectoryName(AppPath);

            if (Directory.Exists(path + DbFilePath) == false)
                Directory.CreateDirectory(path + DbFilePath);

            return path + DbFilePath;
        }

        #region CREATE DATABASE

        public static void MdDbCreate(CommConfig aConfig, string mSN)
        {
            string result = "";
            
            string dbFilename = String.Format(MdDbFileNameFormat, MakeMdSnLotNumber(mSN), csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex]);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                _ = Directory.CreateDirectory(Path.GetDirectoryName(dbFilePath));
            }

            if (File.Exists(dbFilePath) == true)
            {
                //throw new Exception("Already have db file - Failed to create db file");
                return;
            }
            else
            {
                // Create database
                SQLiteConnection.CreateFile(dbFilePath);
            }

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                connection.Open();
                try
                {
                    // Create table
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        string schemaFile = Path.GetDirectoryName(Application.ExecutablePath) + DbSchemaTableFileName;
                        string query = File.ReadAllText(schemaFile);

                        command.CommandText = query;
                        SQLiteDataReader reader = null;
                        reader = command.ExecuteReader();
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return;
        }

        public static void LogDbCreate(string aModel)
        {
            string result = "";
            string ModelName = aModel;
         //public static string LogDbFileNameFormat = @"\{0}\{1}_{2}_LOG.DB";
            string dbFilename = String.Format(LogDbFileNameFormat
                , String.Format("{0:yyMM}", DateTime.Now)
                , String.Format("{0:yyMMdd}", DateTime.Now)
                , ModelName
                );
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + LogDbFilePath + dbFilename;

            if (Directory.Exists(System.IO.Path.GetDirectoryName(dbFilePath)) == false)
                Directory.CreateDirectory(System.IO.Path.GetDirectoryName(dbFilePath));

            if (File.Exists(dbFilePath) == true)
            {
                //throw new Exception("Already have db file - Failed to create db file");
                return;
            }
            else
            {
                // Create database
                SQLiteConnection.CreateFile(dbFilePath);
            }

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                connection.Open();
                try
                {
                    // Create table
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        string schemaFile = Path.GetDirectoryName(Application.ExecutablePath) + LogDbSchemaTableFileName;
                        string query = File.ReadAllText(schemaFile);

                        command.CommandText = query;
                        SQLiteDataReader reader = null;
                        reader = command.ExecuteReader();
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return;
        }
        #endregion

        #region MAKE LOT NUMBER
        //public static string MakeLotNumber(string mSN)
        //{
        //    string LotNo = mSN.Substring(7, 4) + "0000";
        //    return LotNo;
        //}

        public static string MakeMdSnLotNumber(string mSN)
        {
            string LotNo = mSN.Substring(0, mSN.Length - 10) + "0000000000";
            return LotNo;
        }

        #endregion

        #region Excute Database
        public static DataTable DbSqlExcute(string path, string query, bool create_db_flag = false)
        {
            string result = "";
            DataTable dtResult = null;
            string dbFilename = Path.GetDirectoryName(path) + DbFileName;

            if (Directory.Exists(Path.GetDirectoryName(dbFilename)) == false)
                Directory.CreateDirectory(Path.GetDirectoryName(dbFilename));

            // Create database
            if (File.Exists(dbFilename) == false)
            {
                if (create_db_flag == true)
                    SQLiteConnection.CreateFile(dbFilename);
                else
                    throw new Exception("No database file - " + DbFileName);
            }

            // Open database
            string strConn = @"Data Source=" + dbFilename;
            using (var connection = new SQLiteConnection(strConn))
            {
                connection.Open();
                try
                {
                    // Create table
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //command.CommandText = "CREATE TABLE File (Name text, Size bigint, Modified datetime);";
                        command.CommandText = query;
                        SQLiteDataReader reader = null;
                        //command.ExecuteNonQuery();
                        reader = command.ExecuteReader();
                        dtResult = new DataTable();
                        dtResult.Load(reader);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return dtResult;
        }

        public static DataTable DbSqlExcuteA1(string path, string query)
        {
            DataTable dtResult = null;
            string dbFilename = System.IO.Path.GetDirectoryName(path) + DbFileName;

            if (Directory.Exists(System.IO.Path.GetDirectoryName(dbFilename)) == false)
                Directory.CreateDirectory(System.IO.Path.GetDirectoryName(dbFilename));

            if (File.Exists(dbFilename) == false)
                // Create database
                SQLiteConnection.CreateFile(dbFilename);

            // Open database
            string strConn = @"Data Source=" + dbFilename;
            using (var connection = new SQLiteConnection(strConn))
            {
                connection.Open();
                try
                {
                    // Excute Query
                    var adpt = new SQLiteDataAdapter(query, connection);
                    DataSet ds = new DataSet();
                    adpt.Fill(ds);

                    dtResult = ds.Tables[0];
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
            return dtResult;
        }

        public static DataTable DbSqlExcuteA(CommConfig aConfig, string lot, string query, bool create_db_flag = false)
        {
            string result = "";
            DataTable dtResult = new DataTable();
            string lotNo = lot;
            string dbFilename = String.Format(MdDbFileNameFormat, lotNo, csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex]);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                if (create_db_flag)
                    Directory.CreateDirectory(Path.GetDirectoryName(dbFilePath));
                else
                    throw new Exception("No DB file path - " + Path.GetDirectoryName(dbFilePath));
            }
            if (File.Exists(dbFilePath) == false)
            {
                if (create_db_flag)
                    SQLiteConnection.CreateFile(dbFilePath);
                else
                    throw new Exception(String.Format("No DB file - DbSqlExcuteA ({0})", dbFilename));
            }

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                connection.Open();
                try
                {
                    // Excute Query
                    var adpt = new SQLiteDataAdapter(query, connection);
                    DataSet ds = new DataSet();
                    adpt.Fill(ds);
                    if (ds.Tables.Count > 0)
                        dtResult = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                }
                finally
                {
                    connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return dtResult;
        }

        public static DataTable DbSqlExcuteA2(string dbFileName, string query, bool create_db_flag = false)
        {
            string result = "";
            DataTable dtResult = new DataTable();
            string lotNo = dbFileName.Substring(0, 9);
            string dbFilename = String.Format(DbFilePath, lotNo) + dbFileName;
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                if (create_db_flag)
                    Directory.CreateDirectory(Path.GetDirectoryName(dbFilePath));
                else
                    throw new Exception("No DB file path - " + Path.GetDirectoryName(dbFilePath));
            }
            if (File.Exists(dbFilePath) == false)
            {
                if (create_db_flag)
                    SQLiteConnection.CreateFile(dbFilePath);
                else
                    throw new Exception(String.Format("No DB file - DbSqlExcuteA ({0})", dbFilename));
            }

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                connection.Open();
                try
                {
                    // Excute Query
                    var adpt = new SQLiteDataAdapter(query, connection);
                    DataSet ds = new DataSet();
                    adpt.Fill(ds);
                    if (ds.Tables.Count > 0)
                        dtResult = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                }
                finally
                {
                    connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return dtResult;
        }
        public static DataTable DbSqlExcuteA3(string dbFileName, string query, bool create_db_flag = false)
        {
            string result = "";
            DataTable dtResult = new DataTable();
            string dbFilePath = dbFileName;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                if (create_db_flag)
                    Directory.CreateDirectory(Path.GetDirectoryName(dbFilePath));
                else
                    throw new Exception("No DB file path - " + Path.GetDirectoryName(dbFilePath));
            }
            if (File.Exists(dbFilePath) == false)
            {
                if (create_db_flag)
                    SQLiteConnection.CreateFile(dbFilePath);
                else
                    throw new Exception(String.Format("No DB file - DbSqlExcuteA ({0})", dbFileName));
            }

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                connection.Open();
                try
                {
                    // Excute Query
                    var adpt = new SQLiteDataAdapter(query, connection);
                    DataSet ds = new DataSet();
                    adpt.Fill(ds);
                    if (ds.Tables.Count > 0)
                        dtResult = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                }
                finally
                {
                    connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return dtResult;
        }

        public static DataTable DbSqlExcuteB(CommConfig aConfig, string mSN, string query, bool create_db_flag = false)
        {
            string result = "";
            DataTable dtResult = new DataTable();
            string lotNo = csDbUtils.MakeMdSnLotNumber(mSN);
            string dbFilename = String.Format(MdDbFileNameFormat, lotNo);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                if (create_db_flag)
                    Directory.CreateDirectory(Path.GetDirectoryName(dbFilePath));
                else
                    throw new Exception("No DB file path - " + Path.GetDirectoryName(dbFilePath));
            }
            if (File.Exists(dbFilePath) == false)
            {
                if (create_db_flag)
                    SQLiteConnection.CreateFile(dbFilePath);
                else
                    throw new Exception(String.Format("No DB file - DbSqlExcuteB ({0})", dbFilename));
            }

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                connection.Open();
                try
                {
                    // Excute Query
                    var adpt = new SQLiteDataAdapter(query, connection);
                    DataSet ds = new DataSet();
                    adpt.Fill(ds);
                    if (ds.Tables.Count > 0)
                        dtResult = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                }
                finally
                {
                    connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return dtResult;
        }

        #endregion

        #region SELECT QUARY

        public static DataTable BmsDataSelectToDataTable(CommConfig aConfig, DateTime aDate, string qry)
        {
            string result = "";
            DataTable dtResult = null;
            string ModelName = csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex];
            string dbFilename = String.Format(LogDbFileNameFormat
                , String.Format("{0:yyMM}", aDate)
                , String.Format("{0:yyMMdd}", aDate)
                , ModelName
                );
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + LogDbFilePath + dbFilename;

            if (Directory.Exists(System.IO.Path.GetDirectoryName(dbFilePath)) == false)
                return dtResult;
            if (File.Exists(dbFilePath) == false)
                return dtResult;

            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = String.Format("SELECT * FROM TModuleValue {0}", qry);
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                        adapter.AcceptChangesDuringFill = false;
                        dtResult = new DataTable();
                        adapter.Fill(dtResult);
                        dtResult.TableName = "TModuleValue";

                        CommitTran(connection);

                        System.Windows.Forms.Application.DoEvents();
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return dtResult;
        }
        #endregion

        #region BMS DATA INSERT AND UPDATE

        public static void BmsDataInsert(CommConfig aConfig, DeviceSystemData aSystemData, string mSN)
        {
            string result = "";
            string lotNo = csDbUtils.MakeMdSnLotNumber(mSN);
            string dbFilename = String.Format(MdDbFileNameFormat, lotNo, csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex]);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                throw new Exception("No DB file path");
            }
            if (File.Exists(dbFilePath) == false)
            {
                throw new Exception(String.Format("No DB file - BmsDataInsert ({0})", dbFilename));
            }

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = "INSERT INTO"
                            + " TInventoryData"
                            + " (manufacture_date, pcb_sn,"                                                     // 4
                            + " create_date, modify_date)"                                                      // 2
                            + " Values (?,?,?,?);";
                        SQLiteParameter[] p = new SQLiteParameter[4];

                        for (int i = 0; i < 4; i++)
                        {
                            p[i] = new SQLiteParameter();
                            command.Parameters.Add(p[i]);
                        }

                        int j = 0;
                        p[j++].Value = aSystemData.Information.ManufactureDate;                                 // manufacture date
                        p[j++].Value = Encoding.UTF8.GetString(aSystemData.Information.pcb_sn);               // pcb_sn
                        p[j++].Value = DateTime.Now;                                                            // create_date
                        p[j++].Value = DateTime.Now;                                                            // modify_date

                        command.ExecuteNonQuery();

                        CommitTran(connection);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                    if (result != "")
                    {
                        throw new Exception(result);
                    }
                }
            }
        }

        public static void BmsLogDataInsert(ref CommConfig aConfig, ref DeviceSystemData aSystemData, DateTime dateTime, int cvUnit)
        {
            string result = "";
            string ModelName = csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex];
            //public static string LogDbFileNameFormat = @"\{0}\{1}_{2}_LOG.DB";
            string dbFilename = String.Format(LogDbFileNameFormat
                , String.Format("{0:yyMM}", DateTime.Now)
                , String.Format("{0:yyMMdd}", DateTime.Now)
                , ModelName
                );
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + LogDbFilePath + dbFilename;

            if (Directory.Exists(System.IO.Path.GetDirectoryName(dbFilePath)) == false)
                throw new Exception("No Log DB file path");
            if (File.Exists(dbFilePath) == false)
                throw new Exception(String.Format("No Log DB file - BmsLogDataInsert ({0})", dbFilename));

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = String.Format("INSERT INTO TModuleValue (");
                        command.CommandText += "create_date"                                // 0
                            + ", module_no"                                                 // 1
                            + ", pcb_sn"                                                    // 2
                            + ", module_sn"                                                 // 3
                            + ", comm_fail"                                                 // 4
                            + ", op_status"                                                 // 5
                            + ", alarm_status"                                              // 6
                            + ", module_voltage"                                            // 7
                            + ", module_current"                                            // 8
                            + ", module_soc"                                                // 9
                            + ", module_soh"                                                // 10
                            ;
                        for (int i = 0; i < aSystemData.cellQty; i++)
                        {
                            command.CommandText += String.Format(", cell_voltage_{0:00}", i + 1);
                        }
                        for (int i = 0; i < aSystemData.tempQty; i++)
                        {
                            command.CommandText += String.Format(", temperature_{0:00}", i + 1);
                        }
                        command.CommandText +=
                            ", warning"                                                     // 37
                            + ", fault"                                                     // 38
                            + ")"
                            + " Values (";

                        int total = 11 + aSystemData.cellQty + aSystemData.tempQty + 2;

                        for (int i = 0; i < (total - 1); i++) command.CommandText += "?,";
                        command.CommandText += "?);";

                        SQLiteParameter[] p = new SQLiteParameter[total];

                        for (int i = 0; i < total; i++)
                        {
                            if (i == 0)
                                p[i] = new SQLiteParameter(DbType.DateTime);
                            else
                                p[i] = new SQLiteParameter();
                            command.Parameters.Add(p[i]);
                        }

                        int j = 0;
                        p[j++].Value = string.Format("{0:yyyy-MM-dd HH:mm:ss}", dateTime);                      //  0 create_date
                        p[j++].Value = aSystemData.mNo;                                                         //  1 Module No
                        p[j++].Value = Encoding.Default.GetString(aSystemData.Information.pcb_sn).Trim('\0');                 //  2 pcb_sn
                        p[j++].Value = Encoding.Default.GetString(aSystemData.Information.module_sn).Trim('\0');              //  3 module_sn
                        p[j++].Value = aSystemData.ShelfCommFail;                                               //  4 comm_fail
                        p[j++].Value = aSystemData.StatusData.status;                                           //  5 op_status
                        p[j++].Value = aSystemData.StatusData.alarm;                                            //  6 alarm_status
                        p[j++].Value = ((float)aSystemData.ValueData.voltageOfPack / 10).ToString();            //  7 module_voltage
                        p[j++].Value = ((float)aSystemData.ValueData.current / 10).ToString();                  //  8 module_current
                        p[j++].Value = ((float)aSystemData.ValueData.rSOC / 10).ToString();                     //  9 module_soc
                        p[j++].Value = ((float)aSystemData.ValueData.stateOfHealth / 10).ToString();            // 10 module_soh

                        for (int i = 0; i < aSystemData.cellQty; i++)
                        {
                            p[j++].Value = ((float)aSystemData.ValueData.CellVoltage[i] / cvUnit).ToString();                // 11 cell_voltage_xx
                        }
                        for (int i = 0; i < aSystemData.tempQty; i++)
                        {
                            p[j++].Value = ((float)aSystemData.ValueData.CellTemperature[i] / 10).ToString();                // 29 temperature_01
                        }
                        p[j++].Value = aSystemData.StatusData.warning;                                          // 0 warning
                        p[j++].Value = aSystemData.StatusData.protect;                                       // 1 fault

                        command.ExecuteNonQuery();

                        CommitTran(connection);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
        }

        public static DataTable GetDataTable(CommConfig aConfig, string bSN, string aTableName)
        {
            string result = "";
            DataTable dtResult = null;
            string lotNo = csDbUtils.MakeMdSnLotNumber(bSN);
            string dbFilename = String.Format(MdDbFileNameFormat, lotNo, csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex]);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                throw new Exception("No DB file path");
            }

            if (File.Exists(dbFilePath) == false)
            {
                throw new Exception("No DB file");
            }
            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = String.Format("SELECT * FROM {0}", aTableName);
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                        adapter.AcceptChangesDuringFill = false;
                        dtResult = new DataTable();
                        adapter.Fill(dtResult);
                        dtResult.TableName = aTableName;

                        CommitTran(connection);

                        System.Windows.Forms.Application.DoEvents();
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
            return dtResult;
        }

        public static DataTable GetDataTableBySelect(CommConfig aConfig, string bSN, string sql, string aTableName)
        {
            string result = "";
            DataTable dtResult = null;
            string lotNo = csDbUtils.MakeMdSnLotNumber(bSN);
            string dbFilename = String.Format(MdDbFileNameFormat, lotNo, csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex]);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                throw new Exception("No DB file path");
            }

            if (File.Exists(dbFilePath) == false)
            {
                throw new Exception(String.Format("No DB file - {0}", dbFilePath));
            }
            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = sql;
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                        adapter.AcceptChangesDuringFill = false;
                        dtResult = new DataTable();
                        adapter.Fill(dtResult);
                        dtResult.TableName = aTableName;

                        System.Windows.Forms.Application.DoEvents();

                        CommitTran(connection);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "")
                        throw new Exception(result);
                }
            }
            return dtResult;
        }

        public static DataTable GetDataTableBySelectFromDbName(string dbFileName, string sql, string aTableName)
        {
            string result = "";
            DataTable dtResult = null;

            string lotNo = csDbUtils.MakeMdSnLotNumber(dbFileName.Substring(0, 8));
            string dbFilename = String.Format(@"\db\{0}\{1}", lotNo, dbFileName);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                throw new Exception("No DB file path");
            }

            if (File.Exists(dbFilePath) == false)
            {
                throw new Exception(String.Format("No DB file - {0}", dbFilePath));
            }
            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = sql;
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                        adapter.AcceptChangesDuringFill = false;
                        dtResult = new DataTable();
                        adapter.Fill(dtResult);
                        dtResult.TableName = aTableName;

                        System.Windows.Forms.Application.DoEvents();

                        CommitTran(connection);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "")
                        throw new Exception(result);
                }
            }
            return dtResult;
        }

        public static DataTable GetDataTableByFileName(string fileName, string sql, string aTableName)
        {
            string result = "";
            DataTable dtResult = null;

            if (Directory.Exists(System.IO.Path.GetDirectoryName(fileName)) == false)
                throw new Exception("No DB file path");
            if (File.Exists(fileName) == false)
                throw new Exception("No DB file");
            // Open database
            string strConn = @"Data Source=" + fileName;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = sql;
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                        adapter.AcceptChangesDuringFill = false;
                        dtResult = new DataTable();
                        adapter.Fill(dtResult);
                        dtResult.TableName = aTableName;

                        System.Windows.Forms.Application.DoEvents();

                        CommitTran(connection);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "")
                        throw new Exception(result);
                }
            }
            return dtResult;
        }

        public static void BmsPcbMatchDataInsert(string dbFileName, DataRow sRow)
        {
            string result = "";
            int resultCode = (int)SQLiteErrorCode.Unknown;

            if (Directory.Exists(System.IO.Path.GetDirectoryName(dbFileName)) == false)
                throw new Exception("No DB file path");
            if (File.Exists(dbFileName) == false)
                throw new Exception(String.Format("No DB file - BmsDataInsert ({0})", dbFileName));

            // Open database
            string strConn = @"Data Source=" + dbFileName;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = "INSERT INTO"
                            + " TBmsPcbMatchTable"
                            + " (PCB1_SERIAL_NUMBER, MODULE_SERIAL_NUMBER, create_date, modify_date)"                                                          // 2
                            + " Values (?,?,?,?);";
                        SQLiteParameter[] p = new SQLiteParameter[4];

                        for (int i = 0; i < 4; i++)
                        {
                            p[i] = new SQLiteParameter();
                            command.Parameters.Add(p[i]);
                        }

                        int j = 0;
                        p[j++].Value = sRow["PCB1_SERIAL_NUMBER"];                          // pcb_sn
                        p[j++].Value = sRow["MODULE_SERIAL_NUMBER"];                        // module_sn
                        p[j++].Value = DateTime.Now;                                        // create_date
                        p[j++].Value = DateTime.Now;                                        // modify_date

                        command.ExecuteNonQuery();

                        System.Windows.Forms.Application.DoEvents();

                        CommitTran(connection);
                    }
                }
                catch (SQLiteException sqle)
                {
                    // Handle DB exception
                    result = sqle.Message;
                    resultCode = sqle.ErrorCode;
                }
                catch (IndexOutOfRangeException ie)
                {
                    // If you think there might be a problem with index range in the loop, for example
                    result = ie.Message;
                    resultCode = 9999;
                }
                catch (Exception ex)
                {
                    result = ex.Message;
                    resultCode = 9998;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "")
                    {
                        if (resultCode == 9998) throw new Exception(result);
                        else if (resultCode == 9999) throw new IndexOutOfRangeException(result);
                        else throw new SQLiteException((SQLiteErrorCode)resultCode, result);
                    }
                }
            }
        }

        public static void ErrorLogInsert(string dbFileName, DataRow sRow, string Process, string ErrType, string ErrMsg)
        {
            string result = "";

            if (Directory.Exists(System.IO.Path.GetDirectoryName(dbFileName)) == false)
                throw new Exception("No DB file path");
            if (File.Exists(dbFileName) == false)
                throw new Exception(String.Format("No DB file - ErrorLogInsert ({0})", dbFileName));

            // Open database
            string strConn = @"Data Source=" + dbFileName;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = "INSERT INTO"
                            + " TErrorLogTable"
                            + " (create_date, MODULE_SERIAL_NUMBER, PCB1_SERIAL_NUMBER, PROCESS, ERROR_TYPE, ERROR_MSG)"                                                          // 2
                            + " Values (?,?,?,?,?,?);";
                        SQLiteParameter[] p = new SQLiteParameter[6];

                        for (int i = 0; i < 6; i++)
                        {
                            p[i] = new SQLiteParameter();
                            command.Parameters.Add(p[i]);
                        }

                        int j = 0;
                        p[j++].Value = DateTime.Now;                                        // create_date
                        p[j++].Value = sRow["MODULE_SERIAL_NUMBER"];                        // module_sn
                        p[j++].Value = sRow["PCB1_SERIAL_NUMBER"];                          // pcb_sn
                        p[j++].Value = Process;                                             // process
                        p[j++].Value = ErrType;                                             // error type
                        p[j++].Value = ErrMsg;                                              // error message

                        command.ExecuteNonQuery();

                        CommitTran(connection);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
        }

        #endregion

        #region MODULE SN INSERT AND UPDATE

        public static void MdSnDataInsert(CommConfig aConfig, DeviceSystemData aSystemData, string mSN)
        {
            string result = "";
            string lotNo = csDbUtils.MakeMdSnLotNumber(mSN);
            string dbFilename = String.Format(MdDbFileNameFormat, lotNo, csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex]);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(Path.GetDirectoryName(dbFilePath)) == false)
            {
                throw new Exception("No DB file path");
            }
            if (File.Exists(dbFilePath) == false)
            {
                throw new Exception(String.Format("No DB file - BmsDataInsert ({0})", dbFilename));
            }

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = "INSERT INTO"
                            + " TInventoryData"
                            + " ("
                            + " manufacture_date,"                          // 0
                            + " module_name,"                               // 1
                            + " pcb_sn,"                                    // 2
                            + " module_sn,"                                 // 3
                            + " create_date,"                               // 4
                            + " modify_date"                                // 5
                            + ")"
                            + " Values (?,?,?,?,?,?);";
                        SQLiteParameter[] p = new SQLiteParameter[6];

                        for (int i = 0; i < 6; i++)
                        {
                            p[i] = new SQLiteParameter();
                            command.Parameters.Add(p[i]);
                        }

                        int j = 0;
                        p[j++].Value = aSystemData.Information.ManufactureDate;                                 // manufacture date
                        p[j++].Value = csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex];                         // module_name
                        p[j++].Value = Encoding.UTF8.GetString(aSystemData.Information.pcb_sn);                 // pcb_sn
                        p[j++].Value = mSN;                                                                     // module_sn
                        p[j++].Value = DateTime.Now;                                                            // create_date
                        p[j++].Value = DateTime.Now;                                                            // modify_date

                        command.ExecuteNonQuery();

                        Application.DoEvents();

                        CommitTran(connection);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
        }

        public static void MdLogDataInsert(ref CommConfig aConfig, string mSN, ref DeviceSystemData aSystemData, DateTime dateTime, int cvUnit)
        {
            string result = "";
            string ModelName = csConstData.CommType.CAN_MODEL[aConfig.TargetModelIndex];
            string lotNo = csDbUtils.MakeMdSnLotNumber(mSN);
            string dbFilename = String.Format(MdDbFileNameFormat, lotNo, ModelName);
            string dbFilePath = Path.GetDirectoryName(Application.ExecutablePath) + dbFilename;

            if (Directory.Exists(System.IO.Path.GetDirectoryName(dbFilePath)) == false)
                throw new Exception("No Log DB file path");
            if (File.Exists(dbFilePath) == false)
                throw new Exception(String.Format("No Log DB file - BmsLogDataInsert ({0})", dbFilename));

            // Open database
            string strConn = @"Data Source=" + dbFilePath;
            using (var connection = new SQLiteConnection(strConn))
            {
                try
                {
                    connection.Open();
                    BeginTran(connection);

                    // Insert data
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        //sSQL = "insert into TrendTable ( TrendStamp, TagName, TagValue) Values ( " + IntToStr(stamp) + "," + name + "," + value + ");";
                        command.CommandText = String.Format("INSERT INTO TModuleValue (");
                        command.CommandText += "create_date"                                // 0
                            + ", module_no"                                                 // 1
                            + ", pcb_sn"                                                    // 2
                            + ", module_sn"                                                 // 3
                            + ", comm_fail"                                                 // 4
                            + ", op_status"                                                 // 5
                            + ", alarm_status"                                              // 6
                            + ", module_voltage"                                            // 7
                            + ", module_current"                                            // 8
                            + ", module_soc"                                                // 9
                            + ", module_soh"                                                // 10
                            ;
                        for (int i = 0; i < aSystemData.cellQty; i++)
                        {
                            command.CommandText += String.Format(", cell_voltage_{0:00}", i + 1);
                        }
                        for (int i = 0; i < aSystemData.tempQty; i++)
                        {
                            command.CommandText += String.Format(", temperature_{0:00}", i + 1);
                        }
                        command.CommandText +=
                            ", warning"                                                     // 37
                            + ", fault"                                                     // 38
                            + ")"
                            + " Values (";

                        int total = 11 + aSystemData.cellQty + aSystemData.tempQty + 2;

                        for (int i = 0; i < (total - 1); i++) command.CommandText += "?,";
                        command.CommandText += "?);";

                        SQLiteParameter[] p = new SQLiteParameter[total];

                        for (int i = 0; i < total; i++)
                        {
                            if (i == 0)
                                p[i] = new SQLiteParameter(DbType.DateTime);
                            else
                                p[i] = new SQLiteParameter();
                            command.Parameters.Add(p[i]);
                        }

                        int j = 0;
                        p[j++].Value = dateTime;                                                                //  0 create_date
                        p[j++].Value = aSystemData.mNo;                                                         //  1 Module No
                        p[j++].Value = Encoding.UTF8.GetString(aSystemData.Information.pcb_sn);                 //  2 pcb_sn
                        p[j++].Value = mSN;                                                                     //  3 module_sn
                        p[j++].Value = aSystemData.ShelfCommFail;                                               //  4 comm_fail
                        p[j++].Value = aSystemData.StatusData.status;                                           //  5 op_status
                        p[j++].Value = aSystemData.StatusData.alarm;                                            //  6 alarm_status
                        p[j++].Value = ((float)aSystemData.ValueData.voltageOfPack / 10).ToString();            //  7 module_voltage
                        p[j++].Value = ((float)aSystemData.ValueData.current / 10).ToString();                  //  8 module_current
                        p[j++].Value = ((float)aSystemData.ValueData.rSOC / 10).ToString();                     //  9 module_soc
                        p[j++].Value = ((float)aSystemData.ValueData.stateOfHealth / 10).ToString();            // 10 module_soh

                        for (int i = 0; i < aSystemData.cellQty; i++)
                        {
                            p[j++].Value = ((float)aSystemData.ValueData.CellVoltage[i] / cvUnit).ToString();                // 11 cell_voltage_xx
                        }
                        for (int i = 0; i < aSystemData.tempQty; i++)
                        {
                            p[j++].Value = ((float)aSystemData.ValueData.CellTemperature[i] / 10).ToString();                // 29 temperature_01
                        }
                        p[j++].Value = aSystemData.StatusData.warning;                                          // 0 warning
                        p[j++].Value = aSystemData.StatusData.protect;                                       // 1 fault

                        command.ExecuteNonQuery();

                        CommitTran(connection);
                    }
                }
                catch (Exception e)
                {
                    result = e.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                    if (result != "") throw new Exception(result);
                }
            }
        }

        #endregion

        #region SQLITE EXAMPLE

        public class SQLITE
        {
            private SQLiteConnection con;
            private SQLiteCommand cmd;
            private SQLiteDataAdapter adapter;

            public SQLITE(string databasename)
            {
                con = new SQLiteConnection(string.Format("Data Source={0};Compress=True;", databasename));
            }

            public int Execute(string sql_statement)
            {
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = sql_statement;
                int row_updated;
                try
                {
                    row_updated = cmd.ExecuteNonQuery();
                }
                catch
                {
                    con.Close();
                    return 0;
                }
                con.Close();
                return row_updated;
            }
            public DataTable GetDataTable(string tablename)
            {
                DataTable DT = new DataTable();
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT * FROM {0}", tablename);
                adapter = new SQLiteDataAdapter(cmd);
                adapter.AcceptChangesDuringFill = false;
                adapter.Fill(DT);
                con.Close();
                DT.TableName = tablename;
                return DT;
            }
            public void SaveDataTable(DataTable DT)
            {
                try
                {
                    Execute(string.Format("DELETE FROM {0}", DT.TableName));
                    con.Open();
                    cmd = con.CreateCommand();
                    cmd.CommandText = string.Format("SELECT * FROM {0}", DT.TableName);
                    adapter = new SQLiteDataAdapter(cmd);
                    SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
                    adapter.Update(DT);
                    con.Close();
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                }
            }
        }

        #endregion
    }
}