HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Globalization;

using System.Data.SQLite;

using System.Data;

namespace Converter.BaseModel

{

    public class Project_DB

    {

        const string SPPID_DB_INFO_TABLE = "T_SPPID_CONNECTION_INFO";

        const string SPPID_SYMBOL_MAPPING_TABLE = "T_SPPID_SYMBOL_MAPPING";

        const string SPPID_ATTRIBUTE_MAPPING_TABLE = "T_SPPID_ATTRIBUTE_MAPPING";

        const string SPPID_ETC_SETTING_TABLE = "T_SPPID_ETC_SETTING_TABLE";

        const string SPPID_LABEL_INFO_TABLE = "T_SPPID_LABEL_INFO";

        const string LineProperties_TABLE = "LineProperties";

        const string LineTypes_TABLE = "LineTypes";

        const string SymbolType_TABLE = "SymbolType";

        const string SymbolAttribute_TABLE = "SymbolAttribute";

        const string Symbol_TABLE = "Symbol";

        public static bool ConnTestAndCreateTable()

        {

            bool result = false;

            Project_Info projectInfo = Project_Info.GetInstance();

            SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, @"Data Source = {0}", projectInfo.DBFilePath));

            try

            {

                connection.Open();

                if (connection.State == ConnectionState.Open)

                {

                    CreateTable(connection);

                    result = true;

                }

                connection.Close();

            }

            catch (Exception ex)

            {

            }

            finally

            {

                connection.Dispose();

            }

            return result;

        }

        public static bool SaveSPPID_DB_INFO(string jsonString)

        {

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format("DELETE FROM {0}", SPPID_DB_INFO_TABLE);

                        cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@jsonString)", SPPID_DB_INFO_TABLE);

                        cmd.Parameters.AddWithValue("@jsonString", jsonString);

                        cmd.ExecuteNonQuery();

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                    return false;

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return true;

        }

        public static DataTable SelectSPPID_DB_INFO()

        {

            DataTable dt = new DataTable();

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format("SELECT * FROM {0}", SPPID_DB_INFO_TABLE);

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return dt;

        }

        public static bool SaveETCSetting(string jsonString)

        {

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format("DELETE FROM {0}", SPPID_ETC_SETTING_TABLE);

                        cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@jsonString)", SPPID_ETC_SETTING_TABLE);

                        cmd.Parameters.AddWithValue("@jsonString", jsonString);

                        cmd.ExecuteNonQuery();

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                    return false;

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return true;

        }

        public static DataTable SelectETCSetting()

        {

            DataTable dt = new DataTable();

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format("SELECT * FROM {0}", SPPID_ETC_SETTING_TABLE);

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return dt;

        }

        private static void CreateTable(SQLiteConnection connection)

        {

            using (SQLiteCommand cmd = connection.CreateCommand())

            {

                cmd.CommandText = "SELECT NAME FROM sqlite_master WHERE type='table'";

                using (SQLiteDataReader dr = cmd.ExecuteReader())

                using (DataTable dt = new DataTable())

                {

                    dt.Load(dr);

                    if (dt.Select(string.Format("NAME = '{0}'", SPPID_DB_INFO_TABLE)).Length == 0)

                    {

                        cmd.CommandText = string.Format("CREATE TABLE {0} (JsonString TEXT)", SPPID_DB_INFO_TABLE);

                        cmd.ExecuteNonQuery();

                    }

                    if (dt.Select(string.Format("NAME = '{0}'", SPPID_ETC_SETTING_TABLE)).Length == 0)

                    {

                        cmd.CommandText = string.Format("CREATE TABLE {0} (JsonString TEXT)", SPPID_ETC_SETTING_TABLE);

                        cmd.ExecuteNonQuery();

                    }

                    if (dt.Select(string.Format("NAME = '{0}'", SPPID_SYMBOL_MAPPING_TABLE)).Length == 0)

                    {

                        cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT PRIMARY KEY, NAME TEXT, SPPID_SYMBOL_PATH TEXT, LEADERLINE BOOLEAN)", SPPID_SYMBOL_MAPPING_TABLE);

                        cmd.ExecuteNonQuery();

                    }

                    if (dt.Select(string.Format("NAME = '{0}'", SPPID_ATTRIBUTE_MAPPING_TABLE)).Length == 0)

                    {

                        cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT PRIMARY KEY, SPPID_ATTRIBUTE TEXT)", SPPID_ATTRIBUTE_MAPPING_TABLE);

                        cmd.ExecuteNonQuery();

                    }

                    if (dt.Select(string.Format("NAME = '{0}'", SPPID_LABEL_INFO_TABLE)).Length == 0)

                    {

                        cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT PRIMARY KEY, LOCATION INT DEFAULT 0, LEADERLINE BOOLEAN)", SPPID_LABEL_INFO_TABLE);

                        cmd.ExecuteNonQuery();

                    }

                }

                #region Check Column 업데이트시 예비용

                cmd.CommandText = string.Format("SELECT * FROM {0}", SPPID_SYMBOL_MAPPING_TABLE);

                using (SQLiteDataReader dr = cmd.ExecuteReader())

                using (DataTable dt = new DataTable())

                {

                    dt.Load(dr);

                    if (!dt.Columns.Contains("LEADERLINE"))

                    {

                        cmd.CommandText = string.Format("ALTER TABLE {0} ADD COLUMN LEADERLINE BOOLEAN", SPPID_SYMBOL_MAPPING_TABLE);

                        cmd.ExecuteNonQuery();

                    }

                }

                #endregion

            }

        }

        public static DataTable SelectProjectSymbol()

        {

            DataTable dt = new DataTable();

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format(@"

                            SELECT s.UID, s.Name, st.Type, sp.SPPID_SYMBOL_PATH, sp.LEADERLINE FROM {1} as st, {0} as s 

                                LEFT OUTER JOIN {2} as sp 

                                    ON s.UID = SP.UID 

                            WHERE s.SymbolType_UID = st.UID 

                            ORDER BY st.TYPE ASC;", Symbol_TABLE, SymbolType_TABLE, SPPID_SYMBOL_MAPPING_TABLE);

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                        DataTable dtClone = dt.Clone();

                        dtClone.Columns["UID"].DataType = typeof(string);

                        foreach (DataRow row in dt.Rows)

                        {

                            dtClone.ImportRow(row);

                        }

                        dt.Dispose();

                        dt = dtClone;

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return dt;

        }

        public static DataTable SelectProjectChildSymbol()

        {

            DataTable result = new DataTable();

            result.Columns.Add("UID");

            result.Columns.Add("Name");

            result.Columns.Add("Type");

            result.Columns.Add("SPPID_SYMBOL_PATH");

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            using (DataTable dt = new DataTable())

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format(@"

                            SELECT AdditionalSymbol FROM Symbol");

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                        List<string> childList = new List<string>();

                        foreach (DataRow row in dt.Rows)

                        {

                            if (!string.IsNullOrEmpty((string)row["AdditionalSymbol"]))

                            {

                                string[] array = row["AdditionalSymbol"].ToString().Split(new char[] { '/' });

                                foreach (var childString in array)

                                {

                                    childList.Add(childString.Split(new char[] { ',' })[2]);

                                }

                            }

                        }

                        dt.Clear();

                        cmd.Reset();

                        cmd.CommandText = string.Format(@"

                            SELECT * FROM {0}", SPPID_SYMBOL_MAPPING_TABLE);

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                        childList = childList.Distinct().ToList();

                        foreach (var child in childList)

                        {

                            string mappingPath = string.Empty;

                            DataRow[] rows = dt.Select(string.Format("UID = '{0}'", child));

                            if (rows.Length == 1)

                                mappingPath = !DBNull.Value.Equals(rows[0]["SPPID_SYMBOL_PATH"]) ? (string)rows[0]["SPPID_SYMBOL_PATH"] : null;

                            DataRow newRow = result.NewRow();

                            newRow["UID"] = child;

                            newRow["Name"] = child;

                            newRow["Type"] = "Child Symbol";

                            newRow["SPPID_SYMBOL_PATH"] = mappingPath;

                            result.Rows.Add(newRow);

                        }

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return result;

        }

        public static DataTable SelectProjectLine()

        {

            DataTable dt = new DataTable();

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format(@"

                            SELECT l.UID, l.Name, sp.SPPID_SYMBOL_PATH FROM {0} as l 

                                LEFT OUTER JOIN {1} as sp 

                                    ON l.UID = SP.UID ;", LineTypes_TABLE, SPPID_SYMBOL_MAPPING_TABLE);

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return dt;

        }

        public static DataTable SelectProjectLineProperties()

        {

            DataTable dt = new DataTable();

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format(@"

                            SELECT lp.UID, lp.DisplayName, sp.SPPID_SYMBOL_PATH, spa.SPPID_ATTRIBUTE

                            FROM {0} as lp 

                                 LEFT OUTER JOIN {1} as sp 

                                      ON lp.UID = sp.UID

                                 LEFT OUTER JOIN {2} as spa 

                                      ON lp.UID = spa.UID;", LineProperties_TABLE, SPPID_SYMBOL_MAPPING_TABLE, SPPID_ATTRIBUTE_MAPPING_TABLE);

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return dt;

        }

        public static DataTable SelectProjectAttribute()

        {

            DataTable dt = new DataTable();

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format(@"

                            SELECT sa.UID, sa.DisplayAttribute, st.TYPE, spa.SPPID_ATTRIBUTE, sp.SPPID_SYMBOL_PATH, spl.LOCATION, spl.LEADERLINE, sa.Property

                            FROM {1} as sa, {0} as st 

                                 LEFT OUTER JOIN {2} as sp 

                                      ON sa.UID = SP.UID 

                                LEFT OUTER JOIN {3} as spa 

                                     ON sa.UID = spa.UID

                                LEFT OUTER JOIN {4} as spl 

                                     ON sa.UID = spl.UID

                            WHERE sa.SymbolType_UID = st.UID AND (sa.Property != 2 OR sa.Property IS NULL);", SymbolType_TABLE, SymbolAttribute_TABLE, SPPID_SYMBOL_MAPPING_TABLE, SPPID_ATTRIBUTE_MAPPING_TABLE, SPPID_LABEL_INFO_TABLE);

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return dt;

        }

        public static bool InsertSymbolMapping(List<Tuple<string, string, string, bool>> datas)

        {

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteTransaction transaction = connection.BeginTransaction())

                    {

                        try

                        {

                            using (SQLiteCommand cmd = connection.CreateCommand())

                            {

                                foreach (var item in datas)

                                {

                                    cmd.Parameters.Clear();

                                    cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, NAME, SPPID_SYMBOL_PATH, LEADERLINE) VALUES (@UID, @NAME, @SPPID_SYMBOL_PATH, @LEADERLINE)", SPPID_SYMBOL_MAPPING_TABLE);

                                    cmd.Parameters.AddWithValue("@UID", item.Item1);

                                    cmd.Parameters.AddWithValue("@NAME", item.Item2);

                                    cmd.Parameters.AddWithValue("@SPPID_SYMBOL_PATH", item.Item3);

                                    cmd.Parameters.AddWithValue("@LEADERLINE", item.Item4);

                                    cmd.ExecuteNonQuery();

                                }

                            }

                            transaction.Commit();

                            connection.Close();

                        }

                        catch (Exception ex)

                        {

                            transaction.Rollback();

                        }

                        finally

                        {

                            transaction.Dispose();

                        }

                    }

                }

                catch (Exception ex)

                {

                    return false;

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return true;

        }

        public static bool InsertAttributeMapping(List<Tuple<string, string>> datas)

        {

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteTransaction transaction = connection.BeginTransaction())

                    {

                        try

                        {

                            using (SQLiteCommand cmd = connection.CreateCommand())

                            {

                                foreach (var item in datas)

                                {

                                    cmd.Parameters.Clear();

                                    cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, SPPID_ATTRIBUTE) VALUES (@UID, @SPPID_ATTRIBUTE)", SPPID_ATTRIBUTE_MAPPING_TABLE);

                                    cmd.Parameters.AddWithValue("@UID", item.Item1);

                                    cmd.Parameters.AddWithValue("@SPPID_ATTRIBUTE", item.Item2);

                                    cmd.ExecuteNonQuery();

                                }

                            }

                            transaction.Commit();

                            connection.Close();

                        }

                        catch (Exception ex)

                        {

                            transaction.Rollback();

                        }

                        finally

                        {

                            transaction.Dispose();

                        }

                    }

                }

                catch (Exception ex)

                {

                    return false;

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return true;

        }

        public static bool InsertLabelInfoMapping(List<Tuple<string, int, bool>> datas)

        {

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteTransaction transaction = connection.BeginTransaction())

                    {

                        try

                        {

                            using (SQLiteCommand cmd = connection.CreateCommand())

                            {

                                foreach (var item in datas)

                                {

                                    cmd.Parameters.Clear();

                                    cmd.CommandText = string.Format("INSERT OR REPLACE INTO {0} (UID, LOCATION, LEADERLINE) VALUES (@UID, @LOCATION, @LEADERLINE)", SPPID_LABEL_INFO_TABLE);

                                    cmd.Parameters.AddWithValue("@UID", item.Item1);

                                    cmd.Parameters.AddWithValue("@LOCATION", item.Item2);

                                    cmd.Parameters.AddWithValue("@LEADERLINE", item.Item3);

                                    cmd.ExecuteNonQuery();

                                }

                            }

                            transaction.Commit();

                            connection.Close();

                        }

                        catch (Exception ex)

                        {

                            transaction.Rollback();

                        }

                        finally

                        {

                            transaction.Dispose();

                        }

                    }

                }

                catch (Exception ex)

                {

                    return false;

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return true;

        }

    }

}