프로젝트

일반

사용자정보

통계
| 개정판:

hytos / DTI_PID / ID2PSN / DB.cs @ 0a72bba1

이력 | 보기 | 이력해설 | 다운로드 (66.8 KB)

1 0dae5645 gaqhf
using System;
2
using System.Collections.Generic;
3 6b9e7a56 gaqhf
using System.Data;
4 757ab2f2 LJIYEON
using System.Data.Common;
5 45529c16 LJIYEON
using System.Data.SQLite;
6
using System.Linq;
7
using System.Text.RegularExpressions;
8 0dae5645 gaqhf
9
namespace ID2PSN
10
{
11 45529c16 LJIYEON
    public class DB
12 0dae5645 gaqhf
    {
13 6b9e7a56 gaqhf
        const string PSN_TOPOLOGY_RULE = "T_PSN_TOPOLOGY_RULE";
14
        const string PSN_HEADER_SETTING = "T_PSN_HEADER_SETTING";
15 36a45f13 gaqhf
        const string PSN_VENTDRAIN_SETTING = "T_PSN_VENTDRAIN_SETTING";
16 5dfc785c LJIYEON
        const string PSN_VIEW = "T_PSN_VIEW";
17 ddc1c369 LJIYEON
        const string PSN_TRANSFORMKEYWORD_SETTING = "T_PSN_TRANSFORMKEYWORD_SETTING"; 
18 5dfc785c LJIYEON
19 8f24b438 gaqhf
        const string PSN_PATHITEMS = "SPPIDPathItem";
20
        const string PSN_SEQUENCEDATA = "SPPIDSequenceData";
21
        const string PSN_PIPESYSTEMNETWORK = "SPPIDPipeSystemNetwork";
22
        const string PSN_EQUIPMENT = "SPPIDEquipment";
23
        const string PSN_NOZZLE = "SPPIDNozzle";
24
        const string PSN_FLUIDCODE = "SPPIDFluidCode";
25 67638be0 LJIYEON
        const string PSN_PIPINGMATLCLASS = "SPPIDPipingMatlClass";
26 36a45f13 gaqhf
        const string PSN_TOPOLOGYSET = "SPPIDTopologySet";
27 a36541fb LJIYEON
        //2021.11.17 추가 
28
        const string PSN_PIPELINE = "SPPIDPipeLine";
29 1ae1a1c6 LJIYEON
        /// <summary>
30
        ///  ID2 Project.db 데이터를 가져온다. 
31
        ///  DB 접속 정보 및 DBType (Sqlite, Mssql) 정보를 가져옴
32
        ///  - JY
33
        /// </summary>
34
        /// <returns></returns>
35
        public static DataTable GetProject()
36 757ab2f2 LJIYEON
        {
37 1ae1a1c6 LJIYEON
            DataTable dt = new DataTable();
38
            ID2Info id2Info = ID2Info.GetInstance();
39
            try
40
            {
41
                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source = C:\ProgramData\Digital PID\Project.db", true))
42
                {
43
                    connection.Open();
44
                    if (connection.State.Equals(ConnectionState.Open))
45
                    {
46
                        using (SQLiteCommand cmd = connection.CreateCommand())
47
                        {
48
                            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]";
49
                            using (SQLiteDataReader dr = cmd.ExecuteReader())
50
                                dt.Load(dr);
51
                        }
52
53
                    }
54
                    connection.Close();
55
                }
56
            }
57
            catch (Exception ex)
58
            {
59
                System.Windows.Forms.MessageBox.Show(ex.Message);
60
            }
61
62
            dt.AcceptChanges();
63
            dt.DefaultView.Sort = "Name";
64
            dt = dt.DefaultView.ToTable();
65 757ab2f2 LJIYEON
66 1ae1a1c6 LJIYEON
            return dt;
67
        }
68 757ab2f2 LJIYEON
69 1ae1a1c6 LJIYEON
        /// <summary>
70 c4a35107 humkyung
        ///  SQLite에 초기 DB 생성
71 1ae1a1c6 LJIYEON
        ///  - JY
72
        /// </summary>
73
        /// <returns></returns>
74 6b9e7a56 gaqhf
        public static bool ConnTestAndCreateTable()
75
        {
76
            bool result = false;
77
            ID2Info id2Info = ID2Info.GetInstance();
78 1ae1a1c6 LJIYEON
79 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
80 6b9e7a56 gaqhf
            {
81 0fe04b33 LJIYEON
                try
82 6b9e7a56 gaqhf
                {
83 23a96301 humkyung
                    var names = connection.GetTableNames();
84
                    var matched = names.FirstOrDefault(param => param == PSN_HEADER_SETTING);
85
                    if (matched == null)
86 6b9e7a56 gaqhf
                    {
87 23a96301 humkyung
                        var query = $"CREATE TABLE {PSN_HEADER_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";
88
                        using (var cmd = connection.GetSqlStringCommand(query))
89 6b9e7a56 gaqhf
                        {
90 23a96301 humkyung
                            cmd.ExecuteNonQuery();
91
                        }
92
                    }
93 757ab2f2 LJIYEON
94 23a96301 humkyung
                    matched = names.FirstOrDefault(param => param == PSN_VENTDRAIN_SETTING);
95
                    if (matched == null)
96
                    {
97
                        var query = $"CREATE TABLE {PSN_VENTDRAIN_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";
98
                        using (var cmd = connection.GetSqlStringCommand(query))
99
                        {
100
                            cmd.ExecuteNonQuery();
101
                        }
102
                    }
103 757ab2f2 LJIYEON
104 23a96301 humkyung
                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGY_RULE);
105
                    if (matched == null)
106
                    {
107
                        var query = $"CREATE TABLE {PSN_TOPOLOGY_RULE} (UID TEXT)";
108
                        using (var cmd = connection.GetSqlStringCommand(query))
109
                        {
110
                            cmd.ExecuteNonQuery();
111
                        }
112 757ab2f2 LJIYEON
113 23a96301 humkyung
                        DataTable topologyRule = new DataTable();
114
                        topologyRule.Columns.Add("NAME", typeof(string));
115 757ab2f2 LJIYEON
116 23a96301 humkyung
                        topologyRule.Rows.Add("FluidCode");
117
                        topologyRule.Rows.Add("-");
118
                        topologyRule.Rows.Add("PipingMaterialsClass");
119
                        topologyRule.Rows.Add("-");
120
                        topologyRule.Rows.Add("Tag Seq No");
121
122
                        SaveTopologyRule(topologyRule);
123
                    }
124
125 72775f2e LJIYEON
126
                    matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEMNETWORK);
127 23a96301 humkyung
                    if (matched == null)
128
                    {
129 72775f2e LJIYEON
                        var query = $"CREATE TABLE {PSN_PIPESYSTEMNETWORK} (OID NVARCHAR(50), Type NVARCHAR(20), OrderNumber NVARCHAR(20), Pipeline_OID NVARCHAR(125), FROM_DATA NVARCHAR(255), " +
130
                            "TO_DATA NVARCHAR(255), TopologySet_OID_Key NVARCHAR(125), PSNRevisionNumber NVARCHAR(255), IsValid INTEGER, Status NVARCHAR(255), PBS NVARCHAR(255), Drawings NVARCHAR(255), " +
131
                            "IncludingVirtualData NVARCHAR(10),  PSNAccuracy REAL)";
132 23a96301 humkyung
                        using (var cmd = connection.GetSqlStringCommand(query))
133
                        {
134
                            cmd.ExecuteNonQuery();
135 757ab2f2 LJIYEON
                        }
136
                    }
137 23a96301 humkyung
138 72775f2e LJIYEON
                    matched = names.FirstOrDefault(param => param == PSN_EQUIPMENT);
139 23a96301 humkyung
                    if (matched == null)
140
                    {
141 72775f2e LJIYEON
                        var query = $"CREATE TABLE {PSN_EQUIPMENT} (OID NVARCHAR(50), ITEMTAG NVARCHAR(50), XCOORDS REAL, YCOORDS REAL)";
142 23a96301 humkyung
                        using (var cmd = connection.GetSqlStringCommand(query))
143
                        {
144
                            cmd.ExecuteNonQuery();
145
                        }
146
                    }
147
148 72775f2e LJIYEON
                    matched = names.FirstOrDefault(param => param == PSN_NOZZLE);
149 23a96301 humkyung
                    if (matched == null)
150
                    {
151 72775f2e LJIYEON
                        var query = $"CREATE TABLE {PSN_NOZZLE} (OID NVARCHAR(50), ITEMTAG NVARCHAR(255), XCOORDS REAL, YCOORDS REAL, Equipment_OID NVARCHAR(255), " +
152
                            "FLUID NVARCHAR(255), NPD NVARCHAR(255), ROTATION REAL, FlowDirection NVARCHAR(255))";
153 23a96301 humkyung
                        using (var cmd = connection.GetSqlStringCommand(query))
154
                        {
155
                            cmd.ExecuteNonQuery();
156
                        }
157
                    }
158
159 72775f2e LJIYEON
                    matched = names.FirstOrDefault(param => param == PSN_FLUIDCODE);
160 23a96301 humkyung
                    if (matched == null)
161
                    {
162 72775f2e LJIYEON
                        var query = $"CREATE TABLE {PSN_FLUIDCODE} (UID NVARCHAR(50), Code NVARCHAR(255), Description NVARCHAR(255), Condition NVARCHAR(255), Remarks NVARCHAR(255), GroundLevel NVARCHAR(20))";
163 23a96301 humkyung
                        using (var cmd = connection.GetSqlStringCommand(query))
164
                        {
165
                            cmd.ExecuteNonQuery();
166
                        }
167
                    }
168
169 72775f2e LJIYEON
170
                    matched = names.FirstOrDefault(param => param == PSN_PIPINGMATLCLASS);
171 23a96301 humkyung
                    if (matched == null)
172
                    {
173 72775f2e LJIYEON
                        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))";
174 23a96301 humkyung
                        using (var cmd = connection.GetSqlStringCommand(query))
175
                        {
176
                            cmd.ExecuteNonQuery();
177
                        }
178
                    }
179
180 72775f2e LJIYEON
                    matched = names.FirstOrDefault(param => param == PSN_SEQUENCEDATA);
181 23a96301 humkyung
                    if (matched == null)
182
                    {
183 72775f2e LJIYEON
                        var query = $"CREATE TABLE {PSN_SEQUENCEDATA} (OID NVARCHAR(125), SERIALNUMBER NVARCHAR(255), PathItem_OID NVARCHAR(255), TopologySet_OID_Key NVARCHAR(255))";
184 23a96301 humkyung
                        using (var cmd = connection.GetSqlStringCommand(query))
185
                        {
186
                            cmd.ExecuteNonQuery();
187
                        }
188
                    }
189
190 72775f2e LJIYEON
                    matched = names.FirstOrDefault(param => param == PSN_PATHITEMS);
191 23a96301 humkyung
                    if (matched == null)
192
                    {
193 72775f2e LJIYEON
                        var query = $"CREATE TABLE {PSN_PATHITEMS} (OID NVARCHAR(50), SequenceData_OID NVARCHAR(125), " +
194 a36541fb LJIYEON
                            "TopologySet_OID NVARCHAR(125), BranchTopologySet_OID NVARCHAR(125), PipeLine_OID NVARCHAR(125), ITEMNAME NVARCHAR(50), ITEMTAG NVARCHAR(125), DESCRIPTION NVARCHAR(255), " +
195
                            "Class NVARCHAR(80), SubClass NVARCHAR(80), TYPE NVARCHAR(80), PIDNAME NVARCHAR(10), Equipment_OID NVARCHAR(255), NPD NVARCHAR(20), PipeSystemNetwork_OID NVARCHAR(20), PipeRun_OID NVARCHAR(255), " +
196 72775f2e LJIYEON
                            "ViewPipeSystemNetwork_OID NVARCHAR(255))";
197 23a96301 humkyung
                        using (var cmd = connection.GetSqlStringCommand(query))
198
                        {
199
                            cmd.ExecuteNonQuery();
200
                        }
201
                    }
202 72775f2e LJIYEON
                    
203 23a96301 humkyung
                    matched = names.FirstOrDefault(param => param == PSN_VIEW);
204
                    if (matched == null)
205
                    {
206
                        var query = $"CREATE TABLE {PSN_VIEW} (OID TEXT)";
207
                        using (var cmd = connection.GetSqlStringCommand(query))
208
                        {
209
                            cmd.ExecuteNonQuery();
210
                        }
211
                    }
212
213
                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGYSET);
214
                    if (matched == null)
215
                    {
216 72775f2e LJIYEON
                        var query = $"CREATE TABLE { PSN_TOPOLOGYSET} (OID NVARCHAR(125), Type NVARCHAR(30), SubType NVARCHAR(30), HeadItemTag NVARCHAR(50), TailItemTag NVARCHAR(50), HeadItemSPID NVARCHAR(50), TailItemSPID NVARCHAR(50))";
217 23a96301 humkyung
                        using (var cmd = connection.GetSqlStringCommand(query))
218
                        {
219
                            cmd.ExecuteNonQuery();
220
                        }
221
                    }
222 ddc1c369 LJIYEON
                   
223
                    matched = names.FirstOrDefault(param => param == PSN_TRANSFORMKEYWORD_SETTING);
224
                    if (matched == null)
225
                    {
226 d4b1ab29 LJIYEON
                        var query = $"CREATE TABLE {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX] INTEGER, [NAME] TEXT, [KEYWORD] TEXT)";
227 ddc1c369 LJIYEON
                        using (var cmd = connection.GetSqlStringCommand(query))
228
                        {
229
                            cmd.ExecuteNonQuery();
230
                        }
231
                    }
232
233 a36541fb LJIYEON
                    matched = names.FirstOrDefault(param => param == PSN_PIPELINE);
234
                    if (matched == null)
235
                    {
236
                        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), " +
237
                            $"FROM_DATA NVARCHAR(255), TO_DATA NVARCHAR(255), Unit NVARCHAR(100))";
238
                        using (var cmd = connection.GetSqlStringCommand(query))
239
                        {
240
                            cmd.ExecuteNonQuery();
241
                        }
242
                    }
243
244 23a96301 humkyung
                    result = true;
245 0fe04b33 LJIYEON
                }
246
                catch (Exception ex)
247
                {
248
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
249
                }
250 6b9e7a56 gaqhf
            }
251 45529c16 LJIYEON
252 6b9e7a56 gaqhf
            return result;
253
        }
254
255 1ae1a1c6 LJIYEON
        // ID2 DB 데이터
256 c4a35107 humkyung
        /// <summary>
257
        /// ID2 데이타베이스에서 OPC 데이터를 조회
258
        /// </summary>
259
        /// <returns></returns>
260 1ae1a1c6 LJIYEON
        public static DataTable SelectOPCRelations()
261 6b9e7a56 gaqhf
        {
262 23a96301 humkyung
            DataTable dt = null;
263 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
264 0fe04b33 LJIYEON
265 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
266 6b9e7a56 gaqhf
            {
267 0fe04b33 LJIYEON
                try
268 6b9e7a56 gaqhf
                {
269 23a96301 humkyung
                    var query = "SELECT * FROM OPCRelations;";
270
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
271 757ab2f2 LJIYEON
                    {
272 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
273 6b9e7a56 gaqhf
                    }
274
                }
275 0fe04b33 LJIYEON
                catch (Exception ex)
276 36a45f13 gaqhf
                {
277 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
278
                }
279 36a45f13 gaqhf
            }
280 1ae1a1c6 LJIYEON
281 36a45f13 gaqhf
            return dt;
282
        }
283 6b9e7a56 gaqhf
284 c4a35107 humkyung
        /// <summary>
285
        /// ID2 데이타베이스에서 도면 데이터를 조회
286
        /// </summary>
287
        /// <returns></returns>
288 1ae1a1c6 LJIYEON
        public static DataTable SelectDrawings()
289 6b9e7a56 gaqhf
        {
290 23a96301 humkyung
            DataTable dt = null;
291 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
292 0fe04b33 LJIYEON
293 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
294 6b9e7a56 gaqhf
            {
295 0fe04b33 LJIYEON
                try
296 6b9e7a56 gaqhf
                {
297 23a96301 humkyung
                    var query = "SELECT * FROM Drawings";
298
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
299 1ae1a1c6 LJIYEON
                    {
300 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
301 6b9e7a56 gaqhf
                    }
302
                }
303 0fe04b33 LJIYEON
                catch (Exception ex)
304 36a45f13 gaqhf
                {
305 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
306
                }
307 36a45f13 gaqhf
            }
308 1ae1a1c6 LJIYEON
309
            return dt;
310 36a45f13 gaqhf
        }
311 abee404a LJIYEON
312 1ae1a1c6 LJIYEON
        public static DataTable SelectLineProperties()
313 6b9e7a56 gaqhf
        {
314 23a96301 humkyung
            DataTable dt = null;
315 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
316 23a96301 humkyung
317
            using (IAbstractDatabase connection = id2Info.CreateConnection())
318 6b9e7a56 gaqhf
            {
319 0fe04b33 LJIYEON
                try
320 6b9e7a56 gaqhf
                {
321 23a96301 humkyung
                    var query = "SELECT * FROM LineProperties";
322
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
323 1ae1a1c6 LJIYEON
                    {
324 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
325 6b9e7a56 gaqhf
                    }
326
                }
327 0fe04b33 LJIYEON
                catch (Exception ex)
328 6b9e7a56 gaqhf
                {
329 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
330
                }
331 6b9e7a56 gaqhf
            }
332 0fe04b33 LJIYEON
333 6b9e7a56 gaqhf
            return dt;
334
        }
335 45529c16 LJIYEON
336 1ae1a1c6 LJIYEON
        public static DataTable SelectFluidCode()
337 6b9e7a56 gaqhf
        {
338 23a96301 humkyung
            DataTable dt = null;
339 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
340 0fe04b33 LJIYEON
341 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
342 6b9e7a56 gaqhf
            {
343 0fe04b33 LJIYEON
                try
344 6b9e7a56 gaqhf
                {
345 23a96301 humkyung
                    var query = "SELECT * FROM FluidCode";
346 45529c16 LJIYEON
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
347 1ae1a1c6 LJIYEON
                    {
348 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
349 6b9e7a56 gaqhf
                    }
350
                }
351 0fe04b33 LJIYEON
                catch (Exception ex)
352 6b9e7a56 gaqhf
                {
353 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
354
                }
355 1ae1a1c6 LJIYEON
            }
356
357
            return dt;
358
        }
359
360
        public static DataTable SelectPipingMaterialsClass()
361
        {
362 23a96301 humkyung
            DataTable dt = null;
363 1ae1a1c6 LJIYEON
            ID2Info id2Info = ID2Info.GetInstance();
364 0fe04b33 LJIYEON
365 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
366 1ae1a1c6 LJIYEON
            {
367 0fe04b33 LJIYEON
                try
368 6b9e7a56 gaqhf
                {
369 23a96301 humkyung
                    var query = "SELECT * FROM PipingMaterialsClass";
370
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
371 1ae1a1c6 LJIYEON
                    {
372 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
373 1ae1a1c6 LJIYEON
                    }
374
                }
375 0fe04b33 LJIYEON
                catch (Exception ex)
376 1ae1a1c6 LJIYEON
                {
377 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
378
                }
379 6b9e7a56 gaqhf
            }
380 1ae1a1c6 LJIYEON
381 6b9e7a56 gaqhf
            return dt;
382
        }
383
384 1ae1a1c6 LJIYEON
        public static DataTable SelectPSNPIPINGMATLCLASS()
385 6b9e7a56 gaqhf
        {
386 23a96301 humkyung
            DataTable dt = null;
387 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
388 0fe04b33 LJIYEON
389 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
390 1ae1a1c6 LJIYEON
            {
391 0fe04b33 LJIYEON
                try
392
                {
393 23a96301 humkyung
                    var query = $"SELECT * FROM {PSN_PIPINGMATLCLASS}";
394
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
395 1ae1a1c6 LJIYEON
                    {
396 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
397 1ae1a1c6 LJIYEON
                    }
398 0fe04b33 LJIYEON
                }
399
                catch (Exception ex)
400
                {
401
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
402
                }
403 1ae1a1c6 LJIYEON
            }
404
405
            return dt;
406
        }
407
408
        public static DataTable SelectNominalDiameter()
409
        {
410 23a96301 humkyung
            DataTable dt = null;
411 1ae1a1c6 LJIYEON
            ID2Info id2Info = ID2Info.GetInstance();
412 0fe04b33 LJIYEON
413 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
414 1ae1a1c6 LJIYEON
            {
415 0fe04b33 LJIYEON
                try
416 6b9e7a56 gaqhf
                {
417 23a96301 humkyung
                    var query = "SELECT * FROM NominalDiameter ORDER BY Metric DESC";
418
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
419 1ae1a1c6 LJIYEON
                    {
420 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
421 1ae1a1c6 LJIYEON
                    }
422 6b9e7a56 gaqhf
                }
423 0fe04b33 LJIYEON
                catch (Exception ex)
424 1ae1a1c6 LJIYEON
                {
425 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
426
                }
427 1ae1a1c6 LJIYEON
            }
428
429 c4a35107 humkyung
            ///TODO: need to check below code
430 1ae1a1c6 LJIYEON
            dt.Rows.RemoveAt(0);
431
            dt.Rows.RemoveAt(0);
432
            dt.Rows.RemoveAt(0);
433
            dt.Rows.RemoveAt(0);
434
435 6b9e7a56 gaqhf
            return dt;
436
        }
437 abee404a LJIYEON
438 1ae1a1c6 LJIYEON
        public static DataTable SelectSymbolAttribute()
439 6b9e7a56 gaqhf
        {
440 23b86f8b LJIYEON
            DataTable dt = null;
441 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
442 0fe04b33 LJIYEON
443 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
444 6b9e7a56 gaqhf
            {
445 0fe04b33 LJIYEON
                try
446 6b9e7a56 gaqhf
                {
447 23b86f8b LJIYEON
                   // var query = "SELECT DISTINCT Attribute, DisplayAttribute FROM SymbolAttribute;";
448 502d1d50 LJIYEON
                    var query = "SELECT DISTINCT Attribute FROM SymbolAttribute;";
449 23a96301 humkyung
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
450 1ae1a1c6 LJIYEON
                    {
451 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
452 1ae1a1c6 LJIYEON
                    }
453
                }
454 0fe04b33 LJIYEON
                catch (Exception ex)
455 1ae1a1c6 LJIYEON
                {
456 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
457
                }
458 1ae1a1c6 LJIYEON
            }
459 0fe04b33 LJIYEON
460 1ae1a1c6 LJIYEON
            return dt;
461
        }
462
463
        public static DataTable SelectSymbolName()
464
        {
465 23a96301 humkyung
            DataTable dt = null;
466 1ae1a1c6 LJIYEON
            ID2Info id2Info = ID2Info.GetInstance();
467 0fe04b33 LJIYEON
468 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
469 1ae1a1c6 LJIYEON
            {
470 0fe04b33 LJIYEON
                try
471 1ae1a1c6 LJIYEON
                {
472 23a96301 humkyung
                    var query = "SELECT * FROM SymbolName;";
473
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
474 1ae1a1c6 LJIYEON
                    {
475 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
476 1ae1a1c6 LJIYEON
                    }
477
                }
478 0fe04b33 LJIYEON
                catch (Exception ex)
479 1ae1a1c6 LJIYEON
                {
480 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
481
                }
482 1ae1a1c6 LJIYEON
            }
483 0fe04b33 LJIYEON
484 1ae1a1c6 LJIYEON
            return dt;
485 45529c16 LJIYEON
        }
486 1ae1a1c6 LJIYEON
487
        public static double[] GetDrawingSize()
488
        {
489
            double[] result = null;
490
491
            ID2Info id2Info = ID2Info.GetInstance();
492 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
493 1ae1a1c6 LJIYEON
            {
494 0fe04b33 LJIYEON
                try
495 1ae1a1c6 LJIYEON
                {
496 d36e2fe0 esham21
                    var query = "SELECT value FROM Configuration WHERE Section = 'Area' AND [Key] = 'Drawing';";
497 23a96301 humkyung
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
498 1ae1a1c6 LJIYEON
                    {
499 23a96301 humkyung
                        if (ds.Tables[0].Rows.Count == 1)
500 0fe04b33 LJIYEON
                        {
501 23a96301 humkyung
                            string value = ds.Tables[0].Rows[0][0].ToString();
502 0fe04b33 LJIYEON
                            string[] split = value.Split(new char[] { ',' });
503
                            result = new double[] {
504 45529c16 LJIYEON
                                Convert.ToDouble(Regex.Replace(split[0], @"[^0-9]", string.Empty)),
505
                                Convert.ToDouble(Regex.Replace(split[1], @"[^0-9]", string.Empty)),
506
                                Convert.ToDouble(Regex.Replace(split[2], @"[^0-9]", string.Empty)),
507
                                Convert.ToDouble(Regex.Replace(split[3], @"[^0-9]", string.Empty))
508 1ae1a1c6 LJIYEON
                                };
509 0fe04b33 LJIYEON
                            result = new double[] {
510 1ae1a1c6 LJIYEON
                                Math.Min(result[0], result[2]),
511
                                Math.Min(result[1], result[3]),
512
                                Math.Max(result[0], result[2]),
513
                                Math.Max(result[1], result[3])
514
                                };
515
                        }
516
                    }
517
                }
518 0fe04b33 LJIYEON
                catch (Exception ex)
519 1ae1a1c6 LJIYEON
                {
520 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
521
                }
522 1ae1a1c6 LJIYEON
            }
523
524
            return result;
525
        }
526
527
        public static DataTable GetEquipmentType()
528
        {
529 23a96301 humkyung
            DataTable dt = null;
530 1ae1a1c6 LJIYEON
            ID2Info id2Info = ID2Info.GetInstance();
531 0fe04b33 LJIYEON
532 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
533 1ae1a1c6 LJIYEON
            {
534 0fe04b33 LJIYEON
                try
535 1ae1a1c6 LJIYEON
                {
536 23a96301 humkyung
                    var query = "SELECT Type FROM SymbolType WHERE Category = 'Equipment';";
537
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
538 1ae1a1c6 LJIYEON
                    {
539 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
540 1ae1a1c6 LJIYEON
                    }
541
                }
542 0fe04b33 LJIYEON
                catch (Exception ex)
543 1ae1a1c6 LJIYEON
                {
544 0fe04b33 LJIYEON
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
545
                }
546 6b9e7a56 gaqhf
            }
547 0fe04b33 LJIYEON
548 6b9e7a56 gaqhf
            return dt;
549
        }
550 abee404a LJIYEON
551 1ae1a1c6 LJIYEON
        /// <summary>
552
        ///  Zoom 을 위하여 저장 T_PSN_VIEW 데이터로 PSN 데이터지만 Msqql 과 Sqlite 둘다 저장되어 있음
553
        ///  => ID2 가 DB 타입에 따라서 바라보는 DB가 mssql이냐 sqlite냐로 지정되기 때문에 zoom 기능을 그대로 사용하려면 해당 데이터는 psn도 id2 dbtype에 맞춰서 저장 및 로드해야함
554 c4a35107 humkyung
        ///  => 더 이상 사용하지 않음
555 1ae1a1c6 LJIYEON
        /// </summary>
556
        /// <param name="values"></param>
557
        /// <returns></returns>
558
        public static bool SaveView(List<string> values)
559 6b9e7a56 gaqhf
        {
560
            ID2Info id2Info = ID2Info.GetInstance();
561 1ae1a1c6 LJIYEON
562
            bool result = true;
563
564 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
565
            {
566
                try
567 6b9e7a56 gaqhf
                {
568 23a96301 humkyung
                    using (var txn = connection.BeginTransaction())
569 6b9e7a56 gaqhf
                    {
570 23a96301 humkyung
                        try
571 6b9e7a56 gaqhf
                        {
572 23a96301 humkyung
                            var query = $"DELETE FROM {PSN_VIEW}";
573
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
574
575
                            foreach (string value in values)
576 1ae1a1c6 LJIYEON
                            {
577 23a96301 humkyung
                                query = $"INSERT INTO {PSN_VIEW} VALUES (@OID)";
578
                                var cmd = connection.GetSqlStringCommand(query);
579
                                AddWithValue(cmd, "@OID", value);
580
                                connection.ExecuteNonQuery(cmd, txn);
581 1ae1a1c6 LJIYEON
                            }
582 23a96301 humkyung
                            txn.Commit();
583
                        }
584
                        catch (Exception ex)
585
                        {
586
                            txn.Rollback();
587
                            result = false;
588 6b9e7a56 gaqhf
                        }
589 1ae1a1c6 LJIYEON
                    }
590 6b9e7a56 gaqhf
                }
591 23a96301 humkyung
                catch (Exception ex)
592
                {
593
                    System.Windows.Forms.MessageBox.Show(ex.Message);
594
                    result = false;
595
                }
596
            }
597
598 1ae1a1c6 LJIYEON
            return result;
599 6b9e7a56 gaqhf
        }
600
601 1ae1a1c6 LJIYEON
        public static bool DeleteView()
602 6b9e7a56 gaqhf
        {
603
            ID2Info id2Info = ID2Info.GetInstance();
604 1ae1a1c6 LJIYEON
605
            bool result = true;
606 23a96301 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection())
607
            {
608
                try
609 6b9e7a56 gaqhf
                {
610 23a96301 humkyung
                    using (var txn = connection.BeginTransaction())
611 6b9e7a56 gaqhf
                    {
612 23a96301 humkyung
                        try
613 1ae1a1c6 LJIYEON
                        {
614 23a96301 humkyung
                            var query = $"DELETE FROM {PSN_VIEW}";
615
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
616
                            txn.Commit();
617
                        }
618
                        catch (Exception ex)
619
                        {
620
                            txn.Rollback();
621
                            result = false;
622 1ae1a1c6 LJIYEON
                        }
623 6b9e7a56 gaqhf
                    }
624
                }
625 23a96301 humkyung
                catch (Exception ex)
626
                {
627
                    System.Windows.Forms.MessageBox.Show(ex.Message);
628
                    result = false;
629
                }
630
            }
631 1ae1a1c6 LJIYEON
632
            return result;
633 6b9e7a56 gaqhf
        }
634 abee404a LJIYEON
635 1ae1a1c6 LJIYEON
        //PSN Sqlite 
636
        public static DataTable SelectHeaderSetting()
637 6b9e7a56 gaqhf
        {
638 23a96301 humkyung
            DataTable dt = null;
639 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
640 1ae1a1c6 LJIYEON
641 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
642 6b9e7a56 gaqhf
            {
643
                try
644
                {
645 23a96301 humkyung
                    var query = $@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {PSN_HEADER_SETTING};";
646
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
647 6b9e7a56 gaqhf
                    {
648 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
649 6b9e7a56 gaqhf
                    }
650
                }
651
                catch (Exception ex)
652
                {
653
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
654
                }
655
            }
656 1ae1a1c6 LJIYEON
657 6b9e7a56 gaqhf
            return dt;
658
        }
659
660 1ae1a1c6 LJIYEON
        public static DataTable SelectVentDrainSetting()
661 6b9e7a56 gaqhf
        {
662 23a96301 humkyung
            DataTable dt = null;
663 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
664 23a96301 humkyung
665 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
666 6b9e7a56 gaqhf
            {
667
                try
668
                {
669 23a96301 humkyung
                    var query = $@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {PSN_VENTDRAIN_SETTING};";
670
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
671 6b9e7a56 gaqhf
                    {
672 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
673 6b9e7a56 gaqhf
                    }
674
                }
675
                catch (Exception ex)
676
                {
677
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
678
                }
679
            }
680 23a96301 humkyung
681 6b9e7a56 gaqhf
            return dt;
682
        }
683 abee404a LJIYEON
684 ddc1c369 LJIYEON
        public static DataTable SelectKeywordsSetting()
685
        {
686
            DataTable dt = null;
687
            ID2Info id2Info = ID2Info.GetInstance();
688
689
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
690
            {
691
                try
692
                {
693 d4b1ab29 LJIYEON
                    var query = $@"SELECT [KEYWORD], [INDEX], [NAME] FROM {PSN_TRANSFORMKEYWORD_SETTING};";
694 ddc1c369 LJIYEON
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
695
                    {
696
                        dt = ds.Tables[0].Copy();
697
                    }
698
                }
699
                catch (Exception ex)
700
                {
701
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
702
                }
703
            }
704
705
            return dt;
706
        }
707
708 1ae1a1c6 LJIYEON
        public static bool SaveHeaderSetting(List<HeaderInfo> headerInfos)
709 6b9e7a56 gaqhf
        {
710
            ID2Info id2Info = ID2Info.GetInstance();
711 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
712 6b9e7a56 gaqhf
            {
713
                try
714
                {
715 23a96301 humkyung
                    using (var txn = connection.BeginTransaction())
716 6b9e7a56 gaqhf
                    {
717 23a96301 humkyung
                        var query = $"DELETE FROM {PSN_HEADER_SETTING}";
718
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
719 1ae1a1c6 LJIYEON
720
                        foreach (HeaderInfo headerInfo in headerInfos)
721
                        {
722
                            foreach (HeaderItem item in headerInfo.HeaderItems)
723
                            {
724 23a96301 humkyung
                                query = $"INSERT INTO {PSN_HEADER_SETTING} VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";
725
                                var cmd = connection.GetSqlStringCommand(query);
726
                                AddWithValue(cmd, "@GROUP_ID", headerInfo.UID);
727
                                AddWithValue(cmd, "@DESCRIPTION", headerInfo.Description);
728
                                AddWithValue(cmd, "@INDEX", item.Index);
729
                                AddWithValue(cmd, "@NAME", item.Name);
730
                                connection.ExecuteNonQuery(cmd, txn);
731 1ae1a1c6 LJIYEON
                            }
732
                        }
733 81bdaeed LJIYEON
                        txn.Commit();
734 6b9e7a56 gaqhf
                    }
735 81bdaeed LJIYEON
                    
736 6b9e7a56 gaqhf
                }
737
                catch (Exception ex)
738
                {
739
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
740 1ae1a1c6 LJIYEON
                    return false;
741 6b9e7a56 gaqhf
                }
742
            }
743 1ae1a1c6 LJIYEON
            return true;
744 6b9e7a56 gaqhf
        }
745 abee404a LJIYEON
746 1ae1a1c6 LJIYEON
        public static bool SaveVentDrainSetting(List<VentDrainInfo> ventDrainInfos)
747 6b9e7a56 gaqhf
        {
748
            ID2Info id2Info = ID2Info.GetInstance();
749 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
750 6b9e7a56 gaqhf
            {
751 23a96301 humkyung
                using (var txn = connection.BeginTransaction())
752 6b9e7a56 gaqhf
                {
753 23a96301 humkyung
                    try
754 6b9e7a56 gaqhf
                    {
755 23a96301 humkyung
                        var query = $"DELETE FROM {PSN_VENTDRAIN_SETTING}";
756
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
757 1ae1a1c6 LJIYEON
758
                        foreach (VentDrainInfo ventDrainInfo in ventDrainInfos)
759
                        {
760
                            foreach (VentDrainItem item in ventDrainInfo.VentDrainItems)
761
                            {
762 23a96301 humkyung
                                query = $"INSERT INTO {PSN_VENTDRAIN_SETTING} VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";
763
                                var cmd = connection.GetSqlStringCommand(query);
764
                                AddWithValue(cmd, "@GROUP_ID", ventDrainInfo.UID);
765
                                AddWithValue(cmd, "@DESCRIPTION", ventDrainInfo.Description);
766
                                AddWithValue(cmd, "@INDEX", item.Index);
767
                                AddWithValue(cmd, "@NAME", item.Name);
768
                                connection.ExecuteNonQuery(cmd, txn);
769 1ae1a1c6 LJIYEON
                            }
770
                        }
771 81bdaeed LJIYEON
772
                        txn.Commit();
773 6b9e7a56 gaqhf
                    }
774 23a96301 humkyung
                    catch (Exception ex)
775
                    {
776
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
777
                        return false;
778
                    }
779 6b9e7a56 gaqhf
                }
780
            }
781 23a96301 humkyung
782 1ae1a1c6 LJIYEON
            return true;
783 6b9e7a56 gaqhf
        }
784 abee404a LJIYEON
785 8ab98ea3 LJIYEON
        public static bool SaveKeywordsSetting(List<KeywordItem> keywordItems)
786 ddc1c369 LJIYEON
        {
787
            ID2Info id2Info = ID2Info.GetInstance();
788
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
789
            {
790
                using (var txn = connection.BeginTransaction())
791
                {
792
                    try
793
                    {
794 81bdaeed LJIYEON
                        var query = $"DELETE FROM {PSN_TRANSFORMKEYWORD_SETTING}";
795 ddc1c369 LJIYEON
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
796 8ab98ea3 LJIYEON
                        
797
                        foreach (KeywordItem item in keywordItems)
798 ddc1c369 LJIYEON
                        {
799 8ab98ea3 LJIYEON
                            query = $"INSERT INTO {PSN_TRANSFORMKEYWORD_SETTING} VALUES (@INDEX, @NAME, @KEYWORD)";
800
                            var cmd = connection.GetSqlStringCommand(query);
801
                            AddWithValue(cmd, "@INDEX", item.Index);
802
                            AddWithValue(cmd, "@NAME", item.Name);
803
                            AddWithValue(cmd, "@KEYWORD", item.Keyword);                            
804
                            connection.ExecuteNonQuery(cmd, txn);
805 ddc1c369 LJIYEON
                        }
806 8ab98ea3 LJIYEON
                        
807 81bdaeed LJIYEON
                        txn.Commit();
808 ddc1c369 LJIYEON
                    }
809
                    catch (Exception ex)
810
                    {
811
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
812
                        return false;
813
                    }
814
                }
815
            }
816
817
            return true;
818
        }
819
820 1ae1a1c6 LJIYEON
        public static bool SaveTopologyRule(DataTable dt)
821 c6503eaa gaqhf
        {
822
            ID2Info id2Info = ID2Info.GetInstance();
823 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
824 c6503eaa gaqhf
            {
825 23a96301 humkyung
                using (var txn = connection.BeginTransaction())
826 c6503eaa gaqhf
                {
827 23a96301 humkyung
                    try
828 c6503eaa gaqhf
                    {
829 23a96301 humkyung
                        var query = $"DELETE FROM {PSN_TOPOLOGY_RULE}";
830
                        var cmd = connection.GetSqlStringCommand(query);
831 1ae1a1c6 LJIYEON
                        cmd.ExecuteNonQuery();
832
833
                        foreach (DataRow row in dt.Rows)
834
                        {
835 23a96301 humkyung
                            query = $"INSERT INTO {PSN_TOPOLOGY_RULE} VALUES (@UID)";
836
                            cmd = connection.GetSqlStringCommand(query);
837
                            AddWithValue(cmd, "@UID", row["NAME"].ToString());
838
                            connection.ExecuteNonQuery(cmd, txn);
839 1ae1a1c6 LJIYEON
                        }
840 23a96301 humkyung
841
                        txn.Commit();
842
                    }
843
                    catch (Exception ex)
844
                    {
845
                        txn.Rollback();
846
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
847
                        return false;
848 c6503eaa gaqhf
                    }
849
                }
850
            }
851 23a96301 humkyung
852 1ae1a1c6 LJIYEON
            return true;
853 c6503eaa gaqhf
        }
854 abee404a LJIYEON
855 1ae1a1c6 LJIYEON
        public static DataTable SelectTopologyRule()
856 7881ec8f gaqhf
        {
857 23a96301 humkyung
            DataTable dt = null;
858
859 7881ec8f gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
860 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
861 7881ec8f gaqhf
            {
862
                try
863
                {
864 23a96301 humkyung
                    var query = $"SELECT * FROM {PSN_TOPOLOGY_RULE}";
865
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
866 7881ec8f gaqhf
                    {
867 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
868 7881ec8f gaqhf
                    }
869
                }
870
                catch (Exception ex)
871
                {
872
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
873
                }
874
            }
875 23a96301 humkyung
876 7881ec8f gaqhf
            return dt;
877
        }
878 45529c16 LJIYEON
879 23a96301 humkyung
        private static void AddWithValue(DbCommand cmd, string PropName, object Value)
880
        {
881
            var param = cmd.CreateParameter();
882
            param.ParameterName = PropName;
883
            param.Value = Value;
884
            cmd.Parameters.Add(param);
885
        }
886
887 5c248ee3 gaqhf
        public static bool SavePSNData(PSN item)
888 6b9e7a56 gaqhf
        {
889
            ID2Info id2Info = ID2Info.GetInstance();
890
891
            bool result = true;
892 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
893 6b9e7a56 gaqhf
            {
894
                try
895
                {
896 23a96301 humkyung
                    using (var txn = connection.BeginTransaction())
897 6b9e7a56 gaqhf
                    {
898
                        try
899
                        {
900 23a96301 humkyung
                            // Path Items
901
                            var query = $"DELETE FROM {PSN_PATHITEMS}";
902
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
903 9c151350 gaqhf
                            for (int i = 0; i < item.PathItems.Rows.Count; i++)
904 6b9e7a56 gaqhf
                            {
905 9c151350 gaqhf
                                DataRow row = item.PathItems.Rows[i];
906 a36541fb LJIYEON
                                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)";
907 23a96301 humkyung
                                var cmd = connection.GetSqlStringCommand(query);
908
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
909
                                AddWithValue(cmd, "@SequenceData_OID", row["SequenceData_OID"].ToString());
910
                                AddWithValue(cmd, "@TopologySet_OID", row["TopologySet_OID"].ToString());
911
                                AddWithValue(cmd, "@BranchTopologySet_OID", row["BranchTopologySet_OID"].ToString());
912
                                AddWithValue(cmd, "@PipeLine_OID", row["PipeLine_OID"].ToString());
913
                                AddWithValue(cmd, "@ITEMNAME", row["ITEMNAME"].ToString());
914
                                AddWithValue(cmd, "@ITEMTAG", row["ITEMTAG"].ToString());
915 a36541fb LJIYEON
                                AddWithValue(cmd, "@DESCRIPTION", row["DESCRIPTION"].ToString()); 
916 23a96301 humkyung
                                AddWithValue(cmd, "@Class", row["Class"].ToString());
917
                                AddWithValue(cmd, "@SubClass", row["SubClass"].ToString());
918
                                AddWithValue(cmd, "@TYPE", row["TYPE"].ToString());
919
                                AddWithValue(cmd, "@PIDNAME", row["PIDNAME"].ToString());
920 a36541fb LJIYEON
                                AddWithValue(cmd, "@Equipment_OID", row["Equipment_OID"].ToString()); 
921 23a96301 humkyung
                                AddWithValue(cmd, "@NPD", row["NPD"].ToString());
922
                                AddWithValue(cmd, "@PipeSystemNetwork_OID", row["PipeSystemNetwork_OID"].ToString());
923
                                AddWithValue(cmd, "@ViewPipeSystemNetwork_OID", row["ViewPipeSystemNetwork_OID"].ToString());
924
                                AddWithValue(cmd, "@PipeRun_OID", row["PipeRun_OID"].ToString());
925
                                connection.ExecuteNonQuery(cmd, txn);
926
                            }
927 6b9e7a56 gaqhf
928 23a96301 humkyung
                            // Sequence
929
                            query = $"DELETE FROM {PSN_SEQUENCEDATA}";
930
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
931
                            foreach (DataRow row in item.SequenceData.Rows)
932
                            {
933
                                query = $"INSERT INTO {PSN_SEQUENCEDATA} VALUES (@OID, @SERIALNUMBER, @PathItem_OID, @TopologySet_OID_Key)";
934
                                var cmd = connection.GetSqlStringCommand(query);
935
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
936
                                AddWithValue(cmd, "@SERIALNUMBER", row["SERIALNUMBER"].ToString());
937
                                AddWithValue(cmd, "@PathItem_OID", row["PathItem_OID"].ToString());
938
                                AddWithValue(cmd, "@TopologySet_OID_Key", row["TopologySet_OID_Key"].ToString());
939
                                connection.ExecuteNonQuery(cmd, txn);
940
                            }
941 5c248ee3 gaqhf
942 23a96301 humkyung
                            // Nozzle
943
                            query = $"DELETE FROM {PSN_NOZZLE}";
944
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
945
                            foreach (DataRow row in item.Nozzle.Rows)
946
                            {
947
                                query = $"INSERT INTO {PSN_NOZZLE} VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS, @Equipment_OID, @FLUID, @NPD, @ROTATION, @FlowDirection)";
948
                                var cmd = connection.GetSqlStringCommand(query);
949
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
950
                                AddWithValue(cmd, "@ITEMTAG", row["ITEMTAG"].ToString());
951
                                AddWithValue(cmd, "@XCOORDS", row["XCOORDS"].ToString());
952
                                AddWithValue(cmd, "@YCOORDS", row["YCOORDS"].ToString());
953
                                AddWithValue(cmd, "@Equipment_OID", row["Equipment_OID"].ToString());
954
                                AddWithValue(cmd, "@FLUID", row["FLUID"].ToString());
955
                                AddWithValue(cmd, "@NPD", row["NPD"].ToString());
956
                                AddWithValue(cmd, "@ROTATION", row["ROTATION"].ToString());
957
                                AddWithValue(cmd, "@FlowDirection", row["FlowDirection"].ToString());
958
                                connection.ExecuteNonQuery(cmd, txn);
959
                            }
960 5c248ee3 gaqhf
961 23a96301 humkyung
                            //Equipment
962
                            query = $"DELETE FROM {PSN_EQUIPMENT}";
963
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
964
                            foreach (DataRow row in item.Equipment.Rows)
965
                            {
966
                                query = $"INSERT INTO {PSN_EQUIPMENT} VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS)";
967
                                var cmd = connection.GetSqlStringCommand(query);
968
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
969
                                AddWithValue(cmd, "@ITEMTAG", row["ITEMTAG"].ToString());
970
                                AddWithValue(cmd, "@XCOORDS", row["XCOORDS"].ToString());
971
                                AddWithValue(cmd, "@YCOORDS", row["YCOORDS"].ToString());
972
                                connection.ExecuteNonQuery(cmd, txn);
973
                            }
974 5c248ee3 gaqhf
975 23a96301 humkyung
                            // TopologySet
976
                            query = $"DELETE FROM {PSN_TOPOLOGYSET}";
977
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
978
                            foreach (DataRow row in item.TopologySet.Rows)
979
                            {
980 72775f2e LJIYEON
                                query = $"INSERT INTO {PSN_TOPOLOGYSET} VALUES (@OID, @Type, @SubType, @HeadItemTag, @TailItemTag, @HeadItemSPID, @TailItemSPID)";
981 23a96301 humkyung
                                var cmd = connection.GetSqlStringCommand(query);
982
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
983
                                AddWithValue(cmd, "@Type", row["Type"].ToString());
984
                                AddWithValue(cmd, "@SubType", row["SubType"].ToString());
985
                                AddWithValue(cmd, "@HeadItemTag", row["HeadItemTag"].ToString());
986
                                AddWithValue(cmd, "@TailItemTag", row["TailItemTag"].ToString());
987 72775f2e LJIYEON
                                AddWithValue(cmd, "@HeadItemSPID", row["HeadItemSPID"].ToString());
988
                                AddWithValue(cmd, "@TailItemSPID", row["TailItemSPID"].ToString());
989 23a96301 humkyung
                                connection.ExecuteNonQuery(cmd, txn);
990
                            }
991 36a45f13 gaqhf
992 23a96301 humkyung
                            // PSN
993
                            query = $"DELETE FROM {PSN_PIPESYSTEMNETWORK} WHERE PSNRevisionNumber = '{string.Format("V{0:D4}", item.Revision)}'";
994
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
995
                            foreach (DataRow row in item.PipeSystemNetwork.Rows)
996
                            {
997 eb44d82c LJIYEON
                                query = $"INSERT INTO {PSN_PIPESYSTEMNETWORK} " +
998
                                    $"(OID, Type, OrderNumber, Pipeline_OID, FROM_DATA, TO_DATA, TopologySet_OID_Key, PSNRevisionNumber, PBS, Drawings, IsValid, Status, IncludingVirtualData, PSNAccuracy) VALUES " +
999
                                    $"(@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber,  @PBS, @Drawings, @IsValid, @Status, @IncludingVirtualData, @PSNAccuracy)";
1000 23a96301 humkyung
                                var cmd = connection.GetSqlStringCommand(query);
1001
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
1002
                                AddWithValue(cmd, "@Type", row["Type"].ToString());
1003
                                AddWithValue(cmd, "@OrderNumber", row["OrderNumber"].ToString());
1004
                                AddWithValue(cmd, "@Pipeline_OID", row["Pipeline_OID"].ToString());
1005
                                AddWithValue(cmd, "@FROM_DATA", row["FROM_DATA"].ToString());
1006
                                AddWithValue(cmd, "@TO_DATA", row["TO_DATA"].ToString());
1007
                                AddWithValue(cmd, "@TopologySet_OID_Key", row["TopologySet_OID_Key"].ToString());
1008
                                AddWithValue(cmd, "@PSNRevisionNumber", row["PSNRevisionNumber"].ToString());
1009 eb44d82c LJIYEON
1010 72775f2e LJIYEON
                                int IsValid = 0;
1011 eb44d82c LJIYEON
                                if (row["IsValid"].ToString() == string.Empty || row["IsValid"].ToString() == "OK")
1012 72775f2e LJIYEON
                                    IsValid = 0;
1013
                                else if (row["IsValid"].ToString() == "InValid")
1014
                                    IsValid = 1;
1015
                                else if (row["IsValid"].ToString() == "Error")
1016
                                    IsValid = -1;
1017
                                AddWithValue(cmd, "@IsValid", IsValid);
1018 23a96301 humkyung
                                AddWithValue(cmd, "@Status", row["Status"].ToString());
1019 eb44d82c LJIYEON
1020
                                AddWithValue(cmd, "@PBS", row["PBS"].ToString());
1021
                                AddWithValue(cmd, "@Drawings", row["Drawings"].ToString());
1022
                             
1023 ddc1c369 LJIYEON
                                AddWithValue(cmd, "@IncludingVirtualData", row["IncludingVirtualData"].ToString());
1024
                                AddWithValue(cmd, "@PSNAccuracy", row["PSNAccuracy"].ToString()); 
1025 23a96301 humkyung
                                connection.ExecuteNonQuery(cmd, txn);
1026 6b9e7a56 gaqhf
                            }
1027 23a96301 humkyung
1028 a36541fb LJIYEON
                            // Pipeline
1029
                            //query = $"DELETE FROM {PSN_PIPELINE}";
1030
                            //connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1031
                            //foreach (DataRow row in item.PipeLine.Rows)
1032
                            //{
1033
                            //    query = $"INSERT INTO {PSN_PIPELINE} VALUES (@OID, @PipeSystem_OID, @FLUID, @PMC, @SEQNUMBER, @INSULATION, @FROM_DATA, @TO_DATA, @Unit)";
1034
                            //    var cmd = connection.GetSqlStringCommand(query);
1035
                            //    AddWithValue(cmd, "@OID", row["OID"].ToString());
1036
                            //    AddWithValue(cmd, "@PipeSystem_OID", row["PipeSystem_OID"].ToString());
1037
                            //    AddWithValue(cmd, "@FLUID", row["FLUID"].ToString());
1038
                            //    AddWithValue(cmd, "@PMC", row["PMC"].ToString());
1039
                            //    AddWithValue(cmd, "@SEQNUMBER", row["SEQNUMBER"].ToString());
1040
                            //    AddWithValue(cmd, "@INSULATION", row["INSULATION"].ToString());
1041
                            //    AddWithValue(cmd, "@FROM_DATA", row["FROM_DATA"].ToString());
1042
                            //    AddWithValue(cmd, "@TO_DATA", row["TO_DATA"].ToString());
1043
                            //    AddWithValue(cmd, "@Unit", row["Unit"].ToString());
1044
                            //    connection.ExecuteNonQuery(cmd, txn);
1045
                            //}
1046
1047 23a96301 humkyung
                            txn.Commit();
1048 6b9e7a56 gaqhf
                        }
1049
                        catch (Exception ex)
1050
                        {
1051 23a96301 humkyung
                            txn.Rollback();
1052 6b9e7a56 gaqhf
                            result = false;
1053
                        }
1054
                    }
1055
                }
1056
                catch (Exception ex)
1057
                {
1058
                    System.Windows.Forms.MessageBox.Show(ex.Message);
1059
                    result = false;
1060
                }
1061
            }
1062
1063
            return result;
1064
        }
1065
1066 5c248ee3 gaqhf
        public static bool SavePSNFluidCode(DataTable dt)
1067 6b9e7a56 gaqhf
        {
1068
            ID2Info id2Info = ID2Info.GetInstance();
1069
1070
            bool result = true;
1071 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1072 6b9e7a56 gaqhf
            {
1073 1ae1a1c6 LJIYEON
                try
1074 6b9e7a56 gaqhf
                {
1075 23a96301 humkyung
                    using (var txn = connection.BeginTransaction())
1076 1ae1a1c6 LJIYEON
                    {
1077
                        try
1078 757ab2f2 LJIYEON
                        {
1079 23a96301 humkyung
                            var query = $"DELETE FROM {PSN_FLUIDCODE}";
1080
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1081
1082
                            foreach (DataRow row in dt.Rows)
1083 757ab2f2 LJIYEON
                            {
1084 23a96301 humkyung
                                query = $"INSERT INTO {PSN_FLUIDCODE} VALUES (@UID, @Code, @Description, @Condition, @Remarks, @GroundLevel)";
1085
                                var cmd = connection.GetSqlStringCommand(query);
1086
                                cmd.Parameters.Clear();
1087
1088
                                {
1089
                                    var param = cmd.CreateParameter();
1090
                                    param.ParameterName = "@UID";
1091
                                    param.Value = row["UID"].ToString();
1092
                                    cmd.Parameters.Add(param);
1093
                                }
1094
1095
                                {
1096
                                    var param = cmd.CreateParameter();
1097
                                    param.ParameterName = "@Code";
1098
                                    param.Value = row["Code"].ToString();
1099
                                    cmd.Parameters.Add(param);
1100
                                }
1101
1102
                                {
1103
                                    var param = cmd.CreateParameter();
1104
                                    param.ParameterName = "@Description";
1105
                                    param.Value = row["Description"].ToString();
1106
                                    cmd.Parameters.Add(param);
1107
                                }
1108
1109
                                {
1110
                                    var param = cmd.CreateParameter();
1111
                                    param.ParameterName = "@Condition";
1112
                                    param.Value = row["Condition"].ToString();
1113
                                    cmd.Parameters.Add(param);
1114
                                }
1115
1116
                                {
1117
                                    var param = cmd.CreateParameter();
1118
                                    param.ParameterName = "@Remarks";
1119
                                    param.Value = row["Remarks"].ToString();
1120
                                    cmd.Parameters.Add(param);
1121
                                }
1122 1ae1a1c6 LJIYEON
1123 757ab2f2 LJIYEON
                                {
1124 23a96301 humkyung
                                    var param = cmd.CreateParameter();
1125
                                    param.ParameterName = "@GroundLevel";
1126
                                    param.Value = row["GroundLevel"].ToString();
1127
                                    cmd.Parameters.Add(param);
1128 757ab2f2 LJIYEON
                                }
1129 23a96301 humkyung
1130
                                connection.ExecuteNonQuery(cmd, txn);
1131 757ab2f2 LJIYEON
                            }
1132 23a96301 humkyung
                            txn.Commit();
1133 757ab2f2 LJIYEON
                        }
1134 1ae1a1c6 LJIYEON
                        catch (Exception ex)
1135 6b9e7a56 gaqhf
                        {
1136 23a96301 humkyung
                            txn.Rollback();
1137 1ae1a1c6 LJIYEON
                            result = false;
1138 6b9e7a56 gaqhf
                        }
1139
                    }
1140 1ae1a1c6 LJIYEON
                }
1141
                catch (Exception ex)
1142
                {
1143
                    System.Windows.Forms.MessageBox.Show(ex.Message);
1144
                    result = false;
1145
                }
1146 6b9e7a56 gaqhf
            }
1147
1148
            return result;
1149
        }
1150
1151 1ae1a1c6 LJIYEON
        public static DataTable SelectPSNFluidCode()
1152 31d37d58 LJIYEON
        {
1153 23a96301 humkyung
            DataTable dt = null;
1154 31d37d58 LJIYEON
            ID2Info id2Info = ID2Info.GetInstance();
1155 1ae1a1c6 LJIYEON
1156 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1157 31d37d58 LJIYEON
            {
1158 1ae1a1c6 LJIYEON
                try
1159 31d37d58 LJIYEON
                {
1160 23a96301 humkyung
                    var query = $"SELECT * FROM {PSN_FLUIDCODE}";
1161
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1162 31d37d58 LJIYEON
                    {
1163 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
1164 31d37d58 LJIYEON
                    }
1165
                }
1166 1ae1a1c6 LJIYEON
                catch (Exception ex)
1167
                {
1168
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1169
                }
1170 31d37d58 LJIYEON
            }
1171 23a96301 humkyung
1172 1ae1a1c6 LJIYEON
            return dt;
1173
        }
1174
1175
        public static bool SavePSNPMC(DataTable dt)
1176
        {
1177
            ID2Info id2Info = ID2Info.GetInstance();
1178
1179
            bool result = true;
1180 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1181 31d37d58 LJIYEON
            {
1182 1ae1a1c6 LJIYEON
                try
1183
                {
1184 23a96301 humkyung
                    using (var txn = connection.BeginTransaction())
1185 1ae1a1c6 LJIYEON
                    {
1186
                        try
1187
                        {
1188 23a96301 humkyung
                            var query = $"DELETE FROM {PSN_PIPINGMATLCLASS}";
1189
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1190
1191
                            foreach (DataRow row in dt.Rows)
1192 1ae1a1c6 LJIYEON
                            {
1193 23a96301 humkyung
                                query = $"INSERT INTO {PSN_PIPINGMATLCLASS} VALUES (@UID, @Priority, @Code, @Description, @Condition, @Remarks, @GroundLevel)";
1194
                                var cmd = connection.GetSqlStringCommand(query);
1195
                                cmd.Parameters.Clear();
1196
1197
                                {
1198
                                    var param = cmd.CreateParameter();
1199
                                    param.ParameterName = "@UID";
1200
                                    param.Value = row["UID"].ToString();
1201
                                    cmd.Parameters.Add(param);
1202
                                }
1203
1204
                                {
1205
                                    var param = cmd.CreateParameter();
1206
                                    param.ParameterName = "@Priority";
1207
                                    param.Value = row["Priority"].ToString();
1208
                                    cmd.Parameters.Add(param);
1209
                                }
1210
1211
                                {
1212
                                    var param = cmd.CreateParameter();
1213
                                    param.ParameterName = "@Code";
1214
                                    param.Value = row["Code"].ToString();
1215
                                    cmd.Parameters.Add(param);
1216
                                }
1217
1218
                                {
1219
                                    var param = cmd.CreateParameter();
1220
                                    param.ParameterName = "@Description";
1221
                                    param.Value = row["Description"].ToString();
1222
                                    cmd.Parameters.Add(param);
1223
                                }
1224 31d37d58 LJIYEON
1225 1ae1a1c6 LJIYEON
                                {
1226 23a96301 humkyung
                                    var param = cmd.CreateParameter();
1227
                                    param.ParameterName = "@Condition";
1228
                                    param.Value = row["Condition"].ToString();
1229
                                    cmd.Parameters.Add(param);
1230 1ae1a1c6 LJIYEON
                                }
1231 23a96301 humkyung
1232
                                {
1233
                                    var param = cmd.CreateParameter();
1234
                                    param.ParameterName = "@Remarks";
1235
                                    param.Value = row["Remarks"].ToString();
1236
                                    cmd.Parameters.Add(param);
1237
                                }
1238
1239
                                {
1240
                                    var param = cmd.CreateParameter();
1241
                                    param.ParameterName = "@GroundLevel";
1242
                                    param.Value = row["GroundLevel"].ToString();
1243
                                    cmd.Parameters.Add(param);
1244
                                }
1245
1246
                                connection.ExecuteNonQuery(cmd, txn);
1247 1ae1a1c6 LJIYEON
                            }
1248 23a96301 humkyung
1249
                            txn.Commit();
1250 1ae1a1c6 LJIYEON
                        }
1251
                        catch (Exception ex)
1252
                        {
1253 23a96301 humkyung
                            txn.Rollback();
1254 1ae1a1c6 LJIYEON
                            result = false;
1255
                        }
1256
                    }
1257
                }
1258
                catch (Exception ex)
1259
                {
1260
                    System.Windows.Forms.MessageBox.Show(ex.Message);
1261
                    result = false;
1262
                }
1263
            }
1264 31d37d58 LJIYEON
1265 1ae1a1c6 LJIYEON
            return result;
1266 31d37d58 LJIYEON
        }
1267
1268 5c248ee3 gaqhf
        public static PSN GetDBPSN()
1269 6b9e7a56 gaqhf
        {
1270 5c248ee3 gaqhf
            PSN result = new PSN();
1271 6b9e7a56 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
1272
1273 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1274 6b9e7a56 gaqhf
            {
1275
                try
1276
                {
1277 eb44d82c LJIYEON
                    //var query = $"SELECT * FROM {PSN_PIPESYSTEMNETWORK} WHERE PSNRevisionNumber = '{string.Format("V{0:D4}", GetRevision())}'";
1278
                    //using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1279
                    //{
1280
                    //    result.PipeSystemNetwork = ds.Tables[0].Clone();
1281
                    //    result.PipeSystemNetwork.Columns["IsValid"].DataType = typeof(string);
1282
1283
                    //    foreach (DataRow row in ds.Tables[0].Rows)
1284
                    //    {
1285
                    //        DataRow newRow = result.PipeSystemNetwork.NewRow();
1286
                    //        newRow["OID"] = row["OID"].ToString();
1287
                    //        newRow["Type"] = row["Type"].ToString();
1288
                    //        newRow["OrderNumber"] = row["OrderNumber"].ToString();
1289
                    //        newRow["Pipeline_OID"] = row["Pipeline_OID"].ToString();
1290
                    //        newRow["FROM_DATA"] = row["FROM_DATA"].ToString();
1291
                    //        newRow["TO_DATA"] = row["TO_DATA"].ToString();
1292
                    //        newRow["TopologySet_OID_Key"] = row["TopologySet_OID_Key"].ToString();
1293
                    //        newRow["PSNRevisionNumber"] = row["PSNRevisionNumber"].ToString();
1294
                           
1295
                    //        string IsValid = string.Empty;
1296
1297
                    //        if (Convert.ToInt32(row["IsValid"].ToString()) == 0)
1298
                    //            IsValid = string.Empty;//"OK";
1299
                    //        else if (Convert.ToInt32(row["IsValid"].ToString()) == 1)
1300
                    //            IsValid = "InValid";
1301
                    //        else if (Convert.ToInt32(row["IsValid"].ToString()) == -1)
1302
                    //            IsValid = "Error";
1303
1304
                    //        newRow["IsValid"] = IsValid;
1305
1306
                    //        newRow["Status"] = row["Status"].ToString();
1307
                    //        newRow["PBS"] = row["PBS"].ToString();
1308
                    //        newRow["Drawings"] = row["Drawings"].ToString();
1309
1310
                    //        newRow["IncludingVirtualData"] = row["IncludingVirtualData"].ToString();
1311
                    //        newRow["PSNAccuracy"] = row["PSNAccuracy"].ToString();
1312
1313
                    //        result.PipeSystemNetwork.Rows.Add(newRow);
1314
                    //    }
1315
                    //}
1316
1317
                    var query = $"SELECT * FROM {PSN_EQUIPMENT}";
1318 23a96301 humkyung
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1319 6b9e7a56 gaqhf
                    {
1320 23a96301 humkyung
                        result.Equipment = ds.Tables[0].Copy();
1321
                    }
1322
1323
                    query = $"SELECT * FROM {PSN_NOZZLE}";
1324
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1325
                    {
1326
                        result.Nozzle = ds.Tables[0].Copy();
1327 6b9e7a56 gaqhf
                    }
1328 23a96301 humkyung
1329
                    query = $"SELECT * FROM {PSN_PATHITEMS}";
1330
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1331
                    {
1332
                        result.PathItems = ds.Tables[0].Copy();
1333
                    }
1334
1335
                    query = $"SELECT * FROM {PSN_SEQUENCEDATA}";
1336
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1337
                    {
1338
                        result.SequenceData = ds.Tables[0].Copy();
1339
                    }
1340
1341 aadd8450 LJIYEON
                    query = $"SELECT * FROM {PSN_TOPOLOGYSET}";
1342
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1343
                    {
1344
                        result.TopologySet = ds.Tables[0].Copy();
1345
                    }
1346
1347 a36541fb LJIYEON
                    query = $"SELECT * FROM {PSN_PIPELINE}";
1348
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1349
                    {
1350
                        result.PipeLine = ds.Tables[0].Copy();
1351
                    }                    
1352
1353 23a96301 humkyung
                    result.Revision = GetRevision();
1354 6b9e7a56 gaqhf
                }
1355
                catch (Exception ex)
1356
                {
1357
                    System.Windows.Forms.MessageBox.Show(ex.Message);
1358 5c248ee3 gaqhf
                    result = null;
1359 6b9e7a56 gaqhf
                }
1360
            }
1361 23a96301 humkyung
1362 6b9e7a56 gaqhf
            return result;
1363
        }
1364 8f24b438 gaqhf
1365
        public static int GetRevision()
1366
        {
1367
            int result = 0;
1368
            ID2Info id2Info = ID2Info.GetInstance();
1369 23a96301 humkyung
1370 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1371 8f24b438 gaqhf
            {
1372
                try
1373
                {
1374 23a96301 humkyung
                    var query = $"SELECT DISTINCT PSNRevisionNumber FROM {PSN_PIPESYSTEMNETWORK};";
1375
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1376 8f24b438 gaqhf
                    {
1377 23a96301 humkyung
                        foreach (DataRow row in ds.Tables[0].Rows)
1378 8f24b438 gaqhf
                        {
1379 d5637426 gaqhf
                            string value = row["PSNRevisionNumber"].ToString();
1380 36a45f13 gaqhf
                            if (value.StartsWith("V"))
1381 d5637426 gaqhf
                                value = value.Remove(0, 1);
1382
                            int revisionNumber = Convert.ToInt32(value);
1383 8f24b438 gaqhf
                            if (result < revisionNumber)
1384
                                result = revisionNumber;
1385
                        }
1386
                    }
1387
                }
1388
                catch (Exception ex)
1389
                {
1390
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1391
                    result = -1;
1392
                }
1393
            }
1394
1395
            return result;
1396
        }
1397
1398 5c248ee3 gaqhf
        public static DataTable GetPathItem()
1399
        {
1400 23a96301 humkyung
            DataTable dt = null;
1401
1402 5c248ee3 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
1403 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1404 5c248ee3 gaqhf
            {
1405
                try
1406
                {
1407 23a96301 humkyung
                    var query = $"SELECT * FROM {PSN_PATHITEMS}";
1408
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1409 5c248ee3 gaqhf
                    {
1410 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
1411 5c248ee3 gaqhf
                    }
1412
                }
1413
                catch (Exception ex)
1414
                {
1415
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1416
                }
1417
            }
1418 23a96301 humkyung
1419 5c248ee3 gaqhf
            return dt;
1420
        }
1421 757ab2f2 LJIYEON
1422 36a45f13 gaqhf
        public static DataTable GetTopologySet()
1423 5c248ee3 gaqhf
        {
1424 23a96301 humkyung
            DataTable dt = null;
1425
1426 5c248ee3 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
1427 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1428 5c248ee3 gaqhf
            {
1429
                try
1430
                {
1431 23a96301 humkyung
                    var query = $"SELECT * FROM {PSN_TOPOLOGYSET}";
1432
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1433 5c248ee3 gaqhf
                    {
1434 aadd8450 LJIYEON
                        dt = ds.Tables[0].Clone();
1435
                        foreach (DataRow row in ds.Tables[0].Rows)
1436
                        {
1437
                            DataRow newRow = dt.NewRow();
1438
                            newRow["OID"] = row["OID"].ToString();
1439
                            newRow["Type"] = row["Type"].ToString();
1440
                            newRow["SubType"] = row["SubType"].ToString();
1441
                            newRow["HeadItemTag"] = row["HeadItemTag"].ToString();
1442
                            newRow["TailItemTag"] = row["TailItemTag"].ToString();
1443 72775f2e LJIYEON
                            newRow["HeadItemSPID"] = row["HeadItemSPID"].ToString();
1444
                            newRow["TailItemSPID"] = row["TailItemSPID"].ToString();
1445 aadd8450 LJIYEON
                            dt.Rows.Add(newRow);
1446
                        }
1447 5c248ee3 gaqhf
                    }
1448
                }
1449
                catch (Exception ex)
1450
                {
1451
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1452
                }
1453
            }
1454 23a96301 humkyung
1455 5c248ee3 gaqhf
            return dt;
1456
        }
1457 757ab2f2 LJIYEON
1458 5c248ee3 gaqhf
        public static DataTable GetPipeSystemNetwork()
1459
        {
1460 23a96301 humkyung
            DataTable dt = null;
1461
1462 5c248ee3 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
1463 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1464 5c248ee3 gaqhf
            {
1465
                try
1466
                {
1467 23a96301 humkyung
                    var query = $"SELECT * FROM {PSN_PIPESYSTEMNETWORK} WHERE PSNRevisionNumber = '{string.Format("V{0:D4}", GetRevision())}'";
1468
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1469 5c248ee3 gaqhf
                    {
1470 45529c16 LJIYEON
                        dt = ds.Tables[0].Clone();
1471 72775f2e LJIYEON
                        dt.Columns["IsValid"].DataType = typeof(string);
1472 45529c16 LJIYEON
                        foreach (DataRow row in ds.Tables[0].Rows)
1473
                        {
1474
                            DataRow newRow = dt.NewRow();
1475
                            newRow["OID"] = row["OID"].ToString();
1476
                            newRow["Type"] = row["Type"].ToString();
1477
                            newRow["OrderNumber"] = row["OrderNumber"].ToString();
1478
                            newRow["Pipeline_OID"] = row["Pipeline_OID"].ToString();
1479
                            newRow["FROM_DATA"] = row["FROM_DATA"].ToString();
1480
                            newRow["TO_DATA"] = row["TO_DATA"].ToString();
1481
                            newRow["TopologySet_OID_Key"] = row["TopologySet_OID_Key"].ToString();
1482
                            newRow["PSNRevisionNumber"] = row["PSNRevisionNumber"].ToString();
1483 eb44d82c LJIYEON
1484 72775f2e LJIYEON
                            string IsValid = string.Empty;
1485 45529c16 LJIYEON
1486 72775f2e LJIYEON
                            if (Convert.ToInt32(row["IsValid"].ToString()) == 0)
1487
                                IsValid = string.Empty;//"OK";
1488
                            else if (Convert.ToInt32(row["IsValid"].ToString()) == 1)
1489
                                IsValid = "InValid";
1490
                            else if (Convert.ToInt32(row["IsValid"].ToString()) == -1)
1491
                                IsValid = "Error";
1492 45529c16 LJIYEON
1493 72775f2e LJIYEON
                            newRow["IsValid"] = IsValid;
1494 45529c16 LJIYEON
                            newRow["Status"] = row["Status"].ToString();
1495 eb44d82c LJIYEON
1496
                            newRow["PBS"] = row["PBS"].ToString();
1497
                            newRow["Drawings"] = row["Drawings"].ToString();
1498
                            
1499 ddc1c369 LJIYEON
                            newRow["IncludingVirtualData"] = row["IncludingVirtualData"].ToString();
1500 eb44d82c LJIYEON
                            newRow["PSNAccuracy"] = row["PSNAccuracy"].ToString();                                           
1501
                                                        
1502 45529c16 LJIYEON
                            dt.Rows.Add(newRow);
1503
                        }
1504 5c248ee3 gaqhf
                    }
1505
                }
1506
                catch (Exception ex)
1507
                {
1508
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1509
                }
1510
            }
1511 23a96301 humkyung
1512 5c248ee3 gaqhf
            return dt;
1513
        }
1514 757ab2f2 LJIYEON
1515 5c248ee3 gaqhf
        public static DataTable GetSequenceData()
1516
        {
1517 23a96301 humkyung
            DataTable dt = null;
1518
1519 5c248ee3 gaqhf
            ID2Info id2Info = ID2Info.GetInstance();
1520 c4a35107 humkyung
            using (IAbstractDatabase connection = id2Info.CreateConnection(ID2DB_Type.SQLite))
1521 5c248ee3 gaqhf
            {
1522
                try
1523
                {
1524 23a96301 humkyung
                    var query = $"SELECT * FROM {PSN_SEQUENCEDATA}";
1525
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1526 5c248ee3 gaqhf
                    {
1527 23a96301 humkyung
                        dt = ds.Tables[0].Copy();
1528 5c248ee3 gaqhf
                    }
1529
                }
1530
                catch (Exception ex)
1531
                {
1532
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1533
                }
1534
            }
1535 23a96301 humkyung
1536 5c248ee3 gaqhf
            return dt;
1537
        }
1538 0dae5645 gaqhf
    }
1539
}