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 = "SPPIDPipingMatClass";

        const string PSN_TOPOLOGYSET = "SPPIDTopologySet";

        /// <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_PATHITEMS);

                    if (matched == null)

                    {

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

                            "TopologySet_OID NVARCHAR(125), BranchTopologySet_OID NVARCHAR(125), PipeLine_OID NVARCHAR(125), ITEMNAME NVARCHAR(50), ITEMTAG NVARCHAR(125), " +

                            "Class NVARCHAR(80), SubClass NVARCHAR(80), TYPE NVARCHAR(80), PIDNAME NVARCHAR(10), NPD NVARCHAR(20), PipeSystemNetwork_OID NVARCHAR(20), " +

                            "ViewPipeSystemNetwork_OID NVARCHAR(255), PipeRun_OID NVARCHAR(255))";

                        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(125), SERIALNUMBER NVARCHAR(10), PathItem_OID NVARCHAR(50), TopologySet_OID_Key NVARCHAR(125))";

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

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

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

                    if (matched == null)

                    {

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

                            "TO_DATA NVARCHAR(255), TopologySet_OID_Key NVARCHAR(125), PSNRevisionNumber NVARCHAR(255), PBS NVARCHAR(255), PIDDrawings NVARCHAR(255), " +

                            "Validity INTEGER, Status 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(50), ITEMTAG NVARCHAR(50), 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(50), ITEMTAG NVARCHAR(50), XCOORDS REAL, YCOORDS REAL, Equipment_OID NVARCHAR(50), " +

                            "FLUID NVARCHAR(50), NPD NVARCHAR(20), ROTATION REAL, FlowDirection NVARCHAR(10))";

                        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(50), Description NVARCHAR(255), Condition NVARCHAR(50), Remarks NVARCHAR(255), GroundLevel NVARCHAR(50))";

                        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(50), Condition NVARCHAR(255), Remarks NVARCHAR(80), GroundLevel NVARCHAR(50))";

                        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(125), Type NVARCHAR(30), SubType NVARCHAR(30), HeadItemTag NVARCHAR(50), TailItemTag NVARCHAR(50), HeadItemID NVARCHAR(50), TailItemID NVARCHAR(50))";

                        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} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, [NAME] TEXT, [KEYWORD] TEXT)";

                        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 GROUP_ID, DESCRIPTION, [INDEX], [NAME], [KEYWORD] 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<KeywordInfo> keywordInfos)

        {

            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 (KeywordInfo keywordInfo in keywordInfos)

                        {

                            foreach (KeywordItem item in keywordInfo.KeywordItems)

                            {

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

                                var cmd = connection.GetSqlStringCommand(query);

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

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

                                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);

                            foreach (DataRow row in item.PathItems.Rows)

                            {

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

                                var cmd = connection.GetSqlStringCommand(query);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                                AddWithValue(cmd, "@PipeRun_OID", 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", row["OID"].ToString());

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

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

                                AddWithValue(cmd, "@TopologySet_OID_Key", 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", row["OID"].ToString());

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

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

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

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

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

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

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

                                AddWithValue(cmd, "@FlowDirection", 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", row["OID"].ToString());

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

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

                                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, @HeadItemID, @TailItemID)";

                                var cmd = connection.GetSqlStringCommand(query);

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

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

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

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

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

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

                                AddWithValue(cmd, "@TailItemID", row["TailItemID"].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} VALUES (@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber, " +

                                    $"@PBS, @PIDDrawings, @Validity, @Status, @IncludingVirtualData, @PSNAccuracy)";

                                var cmd = connection.GetSqlStringCommand(query);

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

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

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

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

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

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

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

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

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

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

                                int Validity = 0;

                                if (row["Validity"].ToString() == "OK")

                                    Validity = 0;

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

                                    Validity = 1;

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

                                    Validity = -1;

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

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

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

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

                                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 SavePSNFluidCode(DataTable dt)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            bool result = true;

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

            {

                try

                {

                    using (var txn = connection.BeginTransaction())

                    {

                        try

                        {

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

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

                            foreach (DataRow row in dt.Rows)

                            {

                                query = $"INSERT INTO {PSN_FLUIDCODE} VALUES (@UID, @Code, @Description, @Condition, @Remarks, @GroundLevel)";

                                var cmd = connection.GetSqlStringCommand(query);

                                cmd.Parameters.Clear();

                                {

                                    var param = cmd.CreateParameter();

                                    param.ParameterName = "@UID";

                                    param.Value = row["UID"].ToString();

                                    cmd.Parameters.Add(param);

                                }

                                {

                                    var param = cmd.CreateParameter();

                                    param.ParameterName = "@Code";

                                    param.Value = row["Code"].ToString();