HYTOS

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Data.SQLite;

using System.Linq;

using System.Text.RegularExpressions;

namespace ID2PSN

{

    public class DB

    {

        const string PSN_TOPOLOGY_RULE = "T_PSN_TOPOLOGY_RULE";

        const string PSN_HEADER_SETTING = "T_PSN_HEADER_SETTING";

        const string PSN_VENTDRAIN_SETTING = "T_PSN_VENTDRAIN_SETTING";

        const string PSN_VIEW = "T_PSN_VIEW";

        const string PSN_TRANSFORMKEYWORD_SETTING = "T_PSN_TRANSFORMKEYWORD_SETTING"; 

        const string PSN_PATHITEMS = "SPPIDPathItem";

        const string PSN_SEQUENCEDATA = "SPPIDSequenceData";

        const string PSN_PIPESYSTEMNETWORK = "SPPIDPipeSystemNetwork";

        const string PSN_EQUIPMENT = "SPPIDEquipment";

        const string PSN_NOZZLE = "SPPIDNozzle";

        const string PSN_FLUIDCODE = "SPPIDFluidCode";

        const string PSN_PIPINGMATLCLASS = "SPPIDPipingMatlClass";

        const string PSN_TOPOLOGYSET = "SPPIDTopologySet";

        //2021.11.17 추가 

        const string PSN_PIPELINE = "SPPIDPipeLine";

        /// <summary>

        ///  ID2 Project.db 데이터를 가져온다. 

        ///  DB 접속 정보 및 DBType (Sqlite, Mssql) 정보를 가져옴

        ///  - JY

        /// </summary>

        /// <returns></returns>

        public static DataTable GetProject()

        {

            DataTable dt = new DataTable();

            ID2Info id2Info = ID2Info.GetInstance();

            try

            {

                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source = C:\ProgramData\Digital PID\Project.db", true))

                {

                    connection.Open();

                    if (connection.State.Equals(ConnectionState.Open))

                    {

                        using (SQLiteCommand cmd = connection.CreateCommand())

                        {

                            cmd.CommandText = "SELECT p.[Id], p.Name, p.Path, s.DBTypes_UID, s.Host, s.[User], s.[Password], s.FilePath FROM DBSettings s, Projects p WHERE s.Projects_UID = p.[Id]";

                            using (SQLiteDataReader dr = cmd.ExecuteReader())

                                dt.Load(dr);

                        }

                    }

                    connection.Close();

                }

            }

            catch (Exception ex)

            {

                System.Windows.Forms.MessageBox.Show(ex.Message);

            }

            dt.AcceptChanges();

            dt.DefaultView.Sort = "Name";

            dt = dt.DefaultView.ToTable();

            return dt;

        }

        /// <summary>

        ///  SQLite에 초기 DB 생성

        ///  - JY

        /// </summary>

        /// <returns></returns>

        public static bool ConnTestAndCreateTable()

        {

            bool result = false;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                try

                {

                    var names = connection.GetTableNames();

                    var matched = names.FirstOrDefault(param => param == PSN_HEADER_SETTING);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_HEADER_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_VENTDRAIN_SETTING);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_VENTDRAIN_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGY_RULE);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_TOPOLOGY_RULE} (UID TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                        DataTable topologyRule = new DataTable();

                        topologyRule.Columns.Add("NAME", typeof(string));

                        topologyRule.Rows.Add("FluidCode");

                        topologyRule.Rows.Add("-");

                        topologyRule.Rows.Add("PipingMaterialsClass");

                        topologyRule.Rows.Add("-");

                        topologyRule.Rows.Add("Tag Seq No");

                        SaveTopologyRule(topologyRule);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEMNETWORK);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PIPESYSTEMNETWORK} (OID NVARCHAR(255), Type NVARCHAR(255), OrderNumber NVARCHAR(255), Pipeline_OID NVARCHAR(255), From_Data NVARCHAR(255), " +

                            "To_Data NVARCHAR(255), TopologySet_OID_Key NVARCHAR(125), PSNRevisionNumber NVARCHAR(255), IsValid INTEGER, Status NVARCHAR(255), PBS NVARCHAR(255), Drawings NVARCHAR(255), " +

                            "IncludingVirtualData NVARCHAR(10),  PSNAccuracy REAL)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_EQUIPMENT);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_EQUIPMENT} (OID NVARCHAR(255), ItemTag NVARCHAR(255), Xcoords REAL, Ycoords REAL)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_NOZZLE);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_NOZZLE} (OID NVARCHAR(255), ItemTag NVARCHAR(255), Xcoords REAL, Ycoords REAL, Equipment_OID NVARCHAR(255), " +

                            "Fluid NVARCHAR(255), NPD NVARCHAR(255), Rotation REAL, FlowDirection NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_FLUIDCODE);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_FLUIDCODE} (UID NVARCHAR(50), Code NVARCHAR(255), Description NVARCHAR(255), Condition NVARCHAR(255), Remarks NVARCHAR(255), GroundLevel NVARCHAR(20))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_PIPINGMATLCLASS);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PIPINGMATLCLASS} (UID NVARCHAR(50), Priority INTEGER, Code NVARCHAR(255), Description NVARCHAR(255), Condition NVARCHAR(255), Remarks NVARCHAR(255), GroundLevel NVARCHAR(20))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_SEQUENCEDATA);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_SEQUENCEDATA} (OID NVARCHAR(255), SERIALNUMBER NVARCHAR(255), PathItem_OID NVARCHAR(255), TopologySet_OID_Key NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_PATHITEMS);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PATHITEMS} (OID NVARCHAR(255), SequenceData_OID NVARCHAR(255), " +

                            "TopologySet_OID NVARCHAR(255), BranchTopologySet_OID NVARCHAR(255), PipeLine_OID NVARCHAR(255), ItemName NVARCHAR(255), ItemTag NVARCHAR(255), DESCRIPTION NVARCHAR(255), " +

                            "CLASS NVARCHAR(255), SUBCLASS NVARCHAR(255), TYPE NVARCHAR(255), PIDNAME NVARCHAR(255), Equipment_OID NVARCHAR(255), NPD NVARCHAR(255), PipeSystemNetwork_OID NVARCHAR(255), PipeRun_OID NVARCHAR(255), " +

                            "ViewPipeSystemNetwork_OID NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_VIEW);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_VIEW} (OID TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGYSET);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE { PSN_TOPOLOGYSET} (OID NVARCHAR(255), Type NVARCHAR(255), SubType NVARCHAR(255), HeadItemTag NVARCHAR(255), TailItemTag NVARCHAR(255), HeadItemSPID NVARCHAR(255), TailItemSPID NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_TRANSFORMKEYWORD_SETTING);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX] INTEGER, [NAME] TEXT, [KEYWORD] TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_PIPELINE);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PIPELINE} (OID NVARCHAR(255), PipeSystem_OID NVARCHAR(255), FLUID NVARCHAR(255), PMC NVARCHAR(255), SEQNUMBER NVARCHAR(255), INSULATION NVARCHAR(255), " +

                            $"FROM_DATA NVARCHAR(255), TO_DATA NVARCHAR(255), Unit NVARCHAR(100))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    result = true;

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return result;

        }

        // ID2 DB 데이터

        /// <summary>

        /// ID2 데이타베이스에서 OPC 데이터를 조회

        /// </summary>

        /// <returns></returns>

        public static DataTable SelectOPCRelations()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM OPCRelations;";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        /// <summary>

        /// ID2 데이타베이스에서 도면 데이터를 조회

        /// </summary>

        /// <returns></returns>

        public static DataTable SelectDrawings()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM Drawings";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectLineProperties()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM LineProperties";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectFluidCode()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM FluidCode";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectPipingMaterialsClass()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM PipingMaterialsClass";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectPSNPIPINGMATLCLASS()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                try

                {

                    var query = $"SELECT * FROM {PSN_PIPINGMATLCLASS}";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectNominalDiameter()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM NominalDiameter ORDER BY Metric DESC";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            ///TODO: need to check below code

            dt.Rows.RemoveAt(0);

            dt.Rows.RemoveAt(0);

            dt.Rows.RemoveAt(0);

            dt.Rows.RemoveAt(0);

            return dt;

        }

        public static DataTable SelectSymbolAttribute()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                   // var query = "SELECT DISTINCT Attribute, DisplayAttribute FROM SymbolAttribute;";

                    var query = "SELECT DISTINCT Attribute FROM SymbolAttribute;";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectSymbolName()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM SymbolName;";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static double[] GetDrawingSize()

        {

            double[] result = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT value FROM Configuration WHERE Section = 'Area' AND [Key] = 'Drawing';";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        if (ds.Tables[0].Rows.Count == 1)

                        {

                            string value = ds.Tables[0].Rows[0][0].ToString();

                            string[] split = value.Split(new char[] { ',' });

                            result = new double[] {

                                Convert.ToDouble(Regex.Replace(split[0], @"[^0-9]", string.Empty)),

                                Convert.ToDouble(Regex.Replace(split[1], @"[^0-9]", string.Empty)),

                                Convert.ToDouble(Regex.Replace(split[2], @"[^0-9]", string.Empty)),

                                Convert.ToDouble(Regex.Replace(split[3], @"[^0-9]", string.Empty))

                                };

                            result = new double[] {

                                Math.Min(result[0], result[2]),

                                Math.Min(result[1], result[3]),

                                Math.Max(result[0], result[2]),

                                Math.Max(result[1], result[3])

                                };

                        }

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return result;

        }

        public static DataTable GetEquipmentType()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT Type FROM SymbolType WHERE Category = 'Equipment';";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        /// <summary>

        ///  Zoom 을 위하여 저장 T_PSN_VIEW 데이터로 PSN 데이터지만 Msqql 과 Sqlite 둘다 저장되어 있음

        ///  => ID2 가 DB 타입에 따라서 바라보는 DB가 mssql이냐 sqlite냐로 지정되기 때문에 zoom 기능을 그대로 사용하려면 해당 데이터는 psn도 id2 dbtype에 맞춰서 저장 및 로드해야함

        ///  => 더 이상 사용하지 않음

        /// </summary>

        /// <param name="values"></param>

        /// <returns></returns>

        public static bool SaveView(List<string> values)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            bool result = true;

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    using (var txn = connection.BeginTransaction())

                    {

                        try

                        {

                            var query = $"DELETE FROM {PSN_VIEW}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            foreach (string value in values)

                            {

                                query = $"INSERT INTO {PSN_VIEW} VALUES (@OID)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@OID", value);

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                            txn.Commit();

                        }

                        catch (Exception ex)

                        {

                            txn.Rollback();

                            result = false;

                        }

                    }

                }

                catch (Exception ex)

                {

                    System.Windows.Forms.MessageBox.Show(ex.Message);

                    result = false;

                }

            }

            return result;

        }

        public static bool DeleteView()

        {

            ID2Info id2Info = ID2Info.GetInstance();

            bool result = true;

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    using (var txn = connection.BeginTransaction())

                    {

                        try

                        {

                            var query = $"DELETE FROM {PSN_VIEW}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            txn.Commit();

                        }

                        catch (Exception ex)

                        {

                            txn.Rollback();

                            result = false;

                        }

                    }

                }

                catch (Exception ex)

                {

                    System.Windows.Forms.MessageBox.Show(ex.Message);

                    result = false;

                }

            }

            return result;

        }

        //PSN Sqlite 

        public static DataTable SelectHeaderSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                try

                {

                    var query = $@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {PSN_HEADER_SETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectVentDrainSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                try

                {

                    var query = $@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {PSN_VENTDRAIN_SETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectKeywordsSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                try

                {

                    var query = $@"SELECT [KEYWORD], [INDEX], [NAME] FROM {PSN_TRANSFORMKEYWORD_SETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static bool SaveHeaderSetting(List<HeaderInfo> headerInfos)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                try

                {

                    using (var txn = connection.BeginTransaction())

                    {

                        var query = $"DELETE FROM {PSN_HEADER_SETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (HeaderInfo headerInfo in headerInfos)

                        {

                            foreach (HeaderItem item in headerInfo.HeaderItems)

                            {

                                query = $"INSERT INTO {PSN_HEADER_SETTING} VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@GROUP_ID", headerInfo.UID);

                                AddWithValue(cmd, "@DESCRIPTION", headerInfo.Description);

                                AddWithValue(cmd, "@INDEX", item.Index);

                                AddWithValue(cmd, "@NAME", item.Name);

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                        }

                        txn.Commit();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                    return false;

                }

            }

            return true;

        }

        public static bool SaveVentDrainSetting(List<VentDrainInfo> ventDrainInfos)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_VENTDRAIN_SETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (VentDrainInfo ventDrainInfo in ventDrainInfos)

                        {

                            foreach (VentDrainItem item in ventDrainInfo.VentDrainItems)

                            {

                                query = $"INSERT INTO {PSN_VENTDRAIN_SETTING} VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@GROUP_ID", ventDrainInfo.UID);

                                AddWithValue(cmd, "@DESCRIPTION", ventDrainInfo.Description);

                                AddWithValue(cmd, "@INDEX", item.Index);

                                AddWithValue(cmd, "@NAME", item.Name);

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static bool SaveKeywordsSetting(List<KeywordItem> keywordItems)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_TRANSFORMKEYWORD_SETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (KeywordItem item in keywordItems)

                        {

                            query = $"INSERT INTO {PSN_TRANSFORMKEYWORD_SETTING} VALUES (@INDEX, @NAME, @KEYWORD)";

                            var cmd = connection.GetSqlStringCommand(query);

                            AddWithValue(cmd, "@INDEX", item.Index);

                            AddWithValue(cmd, "@NAME", item.Name);

                            AddWithValue(cmd, "@KEYWORD", item.Keyword);                            

                            connection.ExecuteNonQuery(cmd, txn);

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static bool SaveTopologyRule(DataTable dt)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_TOPOLOGY_RULE}";

                        var cmd = connection.GetSqlStringCommand(query);

                        cmd.ExecuteNonQuery();

                        foreach (DataRow row in dt.Rows)

                        {

                            query = $"INSERT INTO {PSN_TOPOLOGY_RULE} VALUES (@UID)";

                            cmd = connection.GetSqlStringCommand(query);

                            AddWithValue(cmd, "@UID", row["NAME"].ToString());

                            connection.ExecuteNonQuery(cmd, txn);

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        txn.Rollback();

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static DataTable SelectTopologyRule()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                try

                {

                    var query = $"SELECT * FROM {PSN_TOPOLOGY_RULE}";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        private static void AddWithValue(DbCommand cmd, string PropName, object Value)

        {

            var param = cmd.CreateParameter();

            param.ParameterName = PropName;

            param.Value = Value;

            cmd.Parameters.Add(param);

        }

        public static bool SavePSNData(PSN item)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            bool result = true;

            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))

            {

                try

                {

                    using (var txn = connection.BeginTransaction())

                    {

                        try

                        {

                            // Path Items

                            var query = $"DELETE FROM {PSN_PATHITEMS}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            for (int i = 0; i < item.PathItems.Rows.Count; i++)

                            {

                                DataRow row = item.PathItems.Rows[i];

                                query = $"INSERT INTO {PSN_PATHITEMS} VALUES (@OID, @SequenceData_OID, @TopologySet_OID, @BranchTopologySet_OID, @PipeLine_OID, @ITEMNAME, @ITEMTAG, @DESCRIPTION, @Class, @SubClass, @TYPE, @PIDNAME, @Equipment_OID, @NPD, @PipeSystemNetwork_OID, @ViewPipeSystemNetwork_OID, @PipeRun_OID)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());

                                AddWithValue(cmd, "@SequenceData_OID", string.IsNullOrEmpty(row["SequenceData_OID"].ToString()) ? "" : row["SequenceData_OID"].ToString());

                                AddWithValue(cmd, "@TopologySet_OID", string.IsNullOrEmpty(row["TopologySet_OID"].ToString()) ? "" : row["TopologySet_OID"].ToString());

                                AddWithValue(cmd, "@BranchTopologySet_OID", string.IsNullOrEmpty(row["BranchTopologySet_OID"].ToString()) ? "" : row["BranchTopologySet_OID"].ToString());

                                AddWithValue(cmd, "@PipeLine_OID", string.IsNullOrEmpty(row["PipeLine_OID"].ToString()) ? "" : row["PipeLine_OID"].ToString());

                                AddWithValue(cmd, "@ITEMNAME", string.IsNullOrEmpty(row["ITEMNAME"].ToString()) ? "" : row["ITEMNAME"].ToString());

                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());

                                AddWithValue(cmd, "@DESCRIPTION", string.IsNullOrEmpty(row["DESCRIPTION"].ToString()) ? "" : row["DESCRIPTION"].ToString()); 

                                AddWithValue(cmd, "@Class", string.IsNullOrEmpty(row["Class"].ToString()) ? "" : row["Class"].ToString());

                                AddWithValue(cmd, "@SubClass", string.IsNullOrEmpty(row["SubClass"].ToString()) ? "" : row["SubClass"].ToString());

                                AddWithValue(cmd, "@TYPE", string.IsNullOrEmpty(row["TYPE"].ToString()) ? "" : row["TYPE"].ToString());

                                AddWithValue(cmd, "@PIDNAME", string.IsNullOrEmpty(row["PIDNAME"].ToString()) ? "" : row["PIDNAME"].ToString());

                                AddWithValue(cmd, "@Equipment_OID", string.IsNullOrEmpty(row["Equipment_OID"].ToString()) ? "" : row["Equipment_OID"].ToString()); 

                                AddWithValue(cmd, "@NPD", string.IsNullOrEmpty(row["NPD"].ToString()) ? "" : row["NPD"].ToString());

                                AddWithValue(cmd, "@PipeSystemNetwork_OID", string.IsNullOrEmpty(row["PipeSystemNetwork_OID"].ToString()) ? "" : row["PipeSystemNetwork_OID"].ToString());

                                AddWithValue(cmd, "@ViewPipeSystemNetwork_OID", string.IsNullOrEmpty(row["ViewPipeSystemNetwork_OID"].ToString()) ? "" : row["ViewPipeSystemNetwork_OID"].ToString());

                                AddWithValue(cmd, "@PipeRun_OID", string.IsNullOrEmpty(row["PipeRun_OID"].ToString()) ? "" : row["PipeRun_OID"].ToString());

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                            // Sequence

                            query = $"DELETE FROM {PSN_SEQUENCEDATA}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            foreach (DataRow row in item.SequenceData.Rows)

                            {

                                query = $"INSERT INTO {PSN_SEQUENCEDATA} VALUES (@OID, @SERIALNUMBER, @PathItem_OID, @TopologySet_OID_Key)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());

                                AddWithValue(cmd, "@SERIALNUMBER", string.IsNullOrEmpty(row["SERIALNUMBER"].ToString()) ? "" : row["SERIALNUMBER"].ToString());

                                AddWithValue(cmd, "@PathItem_OID", string.IsNullOrEmpty(row["PathItem_OID"].ToString()) ? "" : row["PathItem_OID"].ToString());

                                AddWithValue(cmd, "@TopologySet_OID_Key", string.IsNullOrEmpty(row["TopologySet_OID_Key"].ToString()) ? "" : row["TopologySet_OID_Key"].ToString());

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                            // Nozzle

                            query = $"DELETE FROM {PSN_NOZZLE}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            foreach (DataRow row in item.Nozzle.Rows)

                            {

                                query = $"INSERT INTO {PSN_NOZZLE} VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS, @Equipment_OID, @FLUID, @NPD, @ROTATION, @FlowDirection)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());

                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());

                                if (string.IsNullOrEmpty(row["XCOORDS"].ToString()))

                                    AddWithValue(cmd, "@XCOORDS", DBNull.Value);

                                else

                                    AddWithValue(cmd, "@XCOORDS", row["XCOORDS"].ToString());

                                if (string.IsNullOrEmpty(row["YCOORDS"].ToString()))

                                    AddWithValue(cmd, "@YCOORDS", DBNull.Value);

                                else

                                    AddWithValue(cmd, "@YCOORDS", row["YCOORDS"].ToString());

                                AddWithValue(cmd, "@Equipment_OID", string.IsNullOrEmpty(row["Equipment_OID"].ToString()) ? "" : row["Equipment_OID"].ToString());

                                AddWithValue(cmd, "@FLUID", string.IsNullOrEmpty(row["FLUID"].ToString()) ? "" : row["FLUID"].ToString());

                                AddWithValue(cmd, "@NPD", string.IsNullOrEmpty(row["NPD"].ToString()) ? "" : row["NPD"].ToString());

                                if (string.IsNullOrEmpty(row["ROTATION"].ToString()))

                                    AddWithValue(cmd, "@ROTATION", DBNull.Value);

                                else

                                    AddWithValue(cmd, "@ROTATION", row["ROTATION"].ToString());

                                AddWithValue(cmd, "@FlowDirection", string.IsNullOrEmpty(row["FlowDirection"].ToString()) ? "" : row["FlowDirection"].ToString());

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                            //Equipment

                            query = $"DELETE FROM {PSN_EQUIPMENT}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            foreach (DataRow row in item.Equipment.Rows)

                            {

                                query = $"INSERT INTO {PSN_EQUIPMENT} VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());

                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());

                                if (string.IsNullOrEmpty(row["XCOORDS"].ToString()))

                                    AddWithValue(cmd, "@XCOORDS", DBNull.Value);

                                else

                                    AddWithValue(cmd, "@XCOORDS", row["XCOORDS"].ToString());

                                if (string.IsNullOrEmpty(row["YCOORDS"].ToString()))

                                    AddWithValue(cmd, "@YCOORDS", DBNull.Value);

                                else

                                    AddWithValue(cmd, "@YCOORDS", row["YCOORDS"].ToString());

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                            // TopologySet

                            query = $"DELETE FROM {PSN_TOPOLOGYSET}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            foreach (DataRow row in item.TopologySet.Rows)

                            {

                                query = $"INSERT INTO {PSN_TOPOLOGYSET} VALUES (@OID, @Type, @SubType, @HeadItemTag, @TailItemTag, @HeadItemSPID, @TailItemSPID)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());

                                AddWithValue(cmd, "@Type", string.IsNullOrEmpty(row["Type"].ToString()) ? "" : row["Type"].ToString());

                                AddWithValue(cmd, "@SubType", string.IsNullOrEmpty(row["SubType"].ToString()) ? "" : row["SubType"].ToString());

                                AddWithValue(cmd, "@HeadItemTag", string.IsNullOrEmpty(row["HeadItemTag"].ToString()) ? "" : row["HeadItemTag"].ToString());

                                AddWithValue(cmd, "@TailItemTag", string.IsNullOrEmpty(row["TailItemTag"].ToString()) ? "" : row["TailItemTag"].ToString());

                                AddWithValue(cmd, "@HeadItemSPID", string.IsNullOrEmpty(row["HeadItemSPID"].ToString()) ? "" : row["HeadItemSPID"].ToString());

                                AddWithValue(cmd, "@TailItemSPID", string.IsNullOrEmpty(row["TailItemSPID"].ToString()) ? "" : row["TailItemSPID"].ToString());

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                            // PSN

                            query = $"DELETE FROM {PSN_PIPESYSTEMNETWORK} WHERE PSNRevisionNumber = '{string.Format("V{0:D4}", item.Revision)}'";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            foreach (DataRow row in item.PipeSystemNetwork.Rows)

                            {

                                query = $"INSERT INTO {PSN_PIPESYSTEMNETWORK} " +

                                    $"(OID, Type, OrderNumber, Pipeline_OID, FROM_DATA, TO_DATA, TopologySet_OID_Key, PSNRevisionNumber, PBS, Drawings, IsValid, Status, IncludingVirtualData, PSNAccuracy) VALUES " +

                                    $"(@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber,  @PBS, @Drawings, @IsValid, @Status, @IncludingVirtualData, @PSNAccuracy)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());

                                AddWithValue(cmd, "@Type", string.IsNullOrEmpty(row["Type"].ToString()) ? "" : row["Type"].ToString());

                                AddWithValue(cmd, "@OrderNumber", string.IsNullOrEmpty(row["OrderNumber"].ToString()) ? "" : row["OrderNumber"].ToString());

                                AddWithValue(cmd, "@Pipeline_OID", string.IsNullOrEmpty(row["Pipeline_OID"].ToString()) ? "" : row["Pipeline_OID"].ToString());

                                AddWithValue(cmd, "@FROM_DATA", string.IsNullOrEmpty(row["FROM_DATA"].ToString()) ? "" : row["FROM_DATA"].ToString());

                                AddWithValue(cmd, "@TO_DATA", string.IsNullOrEmpty(row["TO_DATA"].ToString()) ? "" : row["TO_DATA"].ToString());

                                AddWithValue(cmd, "@TopologySet_OID_Key", string.IsNullOrEmpty(row["TopologySet_OID_Key"].ToString()) ? "" : row["TopologySet_OID_Key"].ToString());

                                AddWithValue(cmd, "@PSNRevisionNumber", string.IsNullOrEmpty(row["PSNRevisionNumber"].ToString()) ? "" : row["PSNRevisionNumber"].ToString());

                                int IsValid = 0;

                                if (row["IsValid"].ToString() == string.Empty || row["IsValid"].ToString() == "OK")

                                    IsValid = 0;

                                else if (row["IsValid"].ToString() == "InValid")

                                    IsValid = 1;

                                else if (row["IsValid"].ToString() == "Error")

                                    IsValid = -1;

                                AddWithValue(cmd, "@IsValid", IsValid);

                                AddWithValue(cmd, "@Status", string.IsNullOrEmpty(row["Status"].ToString()) ? "" : row["Status"].ToString());

                                AddWithValue(cmd, "@PBS", string.IsNullOrEmpty(row["PBS"].ToString()) ? "" : row["PBS"].ToString());

                                AddWithValue(cmd, "@Drawings", string.IsNullOrEmpty(row["Drawings"].ToString()) ? "" : row["Drawings"].ToString());

                                AddWithValue(cmd, "@IncludingVirtualData", string.IsNullOrEmpty(row["IncludingVirtualData"].ToString()) ? "" : row["IncludingVirtualData"].ToString());

                                if (string.IsNullOrEmpty(row["PSNAccuracy"].ToString()))

                                    AddWithValue(cmd, "@PSNAccuracy", DBNull.Value);

                                else

                                    AddWithValue(cmd, "@PSNAccuracy", row["PSNAccuracy"].ToString());

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                            //Pipeline