프로젝트

일반

사용자정보

통계
| 브랜치(Branch): | 개정판:

hytos / DTI_PID / ID2PSN / DB.cs @ dd27861e

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

1
using System;
2
using System.Collections.Generic;
3
using System.Data;
4
using System.Data.Common;
5
using System.Data.SqlClient;
6
using System.Data.SQLite;
7
using System.Linq;
8
using System.Text.RegularExpressions;
9

    
10
namespace ID2PSN
11
{
12
    public class DB
13
    {
14
        const string PSN_TOPOLOGY_RULE = "T_PSN_TOPOLOGY_RULE";
15
        const string PSN_HEADER_SETTING = "T_PSN_HEADER_SETTING";
16
        const string PSN_VENTDRAIN_SETTING = "T_PSN_VENTDRAIN_SETTING";
17
        const string PSN_VIEW = "T_PSN_VIEW";
18
        const string PSN_TRANSFORMKEYWORD_SETTING = "T_PSN_TRANSFORMKEYWORD_SETTING";
19
        const string PSN_VALVEGROUP_SETTING = "SPPIDValveGroup";
20

    
21
        const string PSN_PATHITEMS = "SPPIDPathItem";
22
        const string PSN_SEQUENCEDATA = "SPPIDSequenceData";
23
        const string PSN_PIPESYSTEMNETWORK = "SPPIDPipeSystemNetwork";
24
        const string PSN_EQUIPMENT = "SPPIDEquipment";
25
        const string PSN_NOZZLE = "SPPIDNozzle";
26
        const string PSN_FLUIDCODE = "SPPIDFluidCode";
27
        const string PSN_PIPINGMATLCLASS = "SPPIDPipingMatlClass";
28
        const string PSN_TOPOLOGYSET = "SPPIDTopologySet";
29
        //2021.11.17 추가 
30
        const string PSN_PIPELINE = "SPPIDPipeLine";
31
        //2021.11.26 추가
32
        const string PSN_INSULATIONPURPOSE = "SPPIDInsulationPurpose";
33
        //2022.01.12 추가 //ARS_COMMON DB
34
        const string PSN_REVISION = "PSNRevision";
35
        const string PSN_COMMON = "ARS_COMMON";
36
        //2022.01.19 추가
37
        const string PSN_PIPESYSTEM = "SPPIDPipeSystem";
38

    
39
        //2022.02.03 추가
40
        const string PSN_NOPOCKETSETTING = "T_PSN_NOPOCKET_SETTING";
41
        const string PSN_AIRFINCOOLERSETTING = "T_PSN_AIRFINCOOLER_SETTING";
42
        /// <summary>
43
        ///  ID2 Project.db 데이터를 가져온다. 
44
        ///  DB 접속 정보 및 DBType (Sqlite, Mssql) 정보를 가져옴
45
        ///  - JY
46
        /// </summary>
47
        /// <returns></returns>
48
        public static DataTable GetProject()
49
        {
50
            DataTable dt = new DataTable();
51
            ID2Info id2Info = ID2Info.GetInstance();
52
            try
53
            {
54
                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source = C:\ProgramData\Digital PID\Project.db", true))
55
                {
56
                    connection.Open();
57
                    if (connection.State.Equals(ConnectionState.Open))
58
                    {
59
                        using (SQLiteCommand cmd = connection.CreateCommand())
60
                        {
61
                            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]";
62
                            using (SQLiteDataReader dr = cmd.ExecuteReader())
63
                                dt.Load(dr);
64
                        }
65

    
66
                    }
67
                    connection.Close();
68
                }
69
            }
70
            catch (Exception ex)
71
            {
72
                System.Windows.Forms.MessageBox.Show(ex.Message);
73
            }
74

    
75
            dt.AcceptChanges();
76
            dt.DefaultView.Sort = "Name";
77
            dt = dt.DefaultView.ToTable();
78

    
79
            return dt;
80
        }
81

    
82
        /// <summary>
83
        ///  SQLite에 초기 DB 생성
84
        ///  - JY
85
        /// </summary>
86
        /// <returns></returns>
87
        public static bool ConnTestAndCreateTable()
88
        {
89
            bool result = false;
90
            ID2Info id2Info = ID2Info.GetInstance();
91

    
92
            using (IAbstractDatabase connection = id2Info.CreateConnection())
93
            {
94
                try
95
                {
96
                    var names = connection.GetTableNames();
97
                    var matched = names.FirstOrDefault(param => param == PSN_HEADER_SETTING);
98
                    Dictionary<string, string> dicColCheck = new Dictionary<string, string>();
99
                    dicColCheck.Add("GROUP_ID", "TEXT");
100
                    dicColCheck.Add("DESCRIPTION", "TEXT");
101
                    dicColCheck.Add("INDEX", "INTEGER");
102
                    dicColCheck.Add("NAME", "TEXT");
103

    
104
                    if (matched == null)
105
                    {
106
                        var query = $"CREATE TABLE {PSN_HEADER_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";
107
                        using (var cmd = connection.GetSqlStringCommand(query))
108
                        {
109
                            cmd.ExecuteNonQuery();
110
                        }
111
                    }
112
                    else
113
                    {
114
                        AddColumn(PSN_HEADER_SETTING, dicColCheck);
115
                    }
116

    
117
                    matched = names.FirstOrDefault(param => param == PSN_VENTDRAIN_SETTING);
118
                    dicColCheck.Clear();
119
                    dicColCheck.Add("GROUP_ID", "TEXT");
120
                    dicColCheck.Add("DESCRIPTION", "TEXT");
121
                    dicColCheck.Add("INDEX", "INTEGER");
122
                    dicColCheck.Add("NAME", "TEXT");
123
                    if (matched == null)
124
                    {
125
                        var query = $"CREATE TABLE {PSN_VENTDRAIN_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";
126
                        using (var cmd = connection.GetSqlStringCommand(query))
127
                        {
128
                            cmd.ExecuteNonQuery();
129
                        }
130
                    }
131
                    else
132
                    {
133
                        AddColumn(PSN_VENTDRAIN_SETTING, dicColCheck);
134
                    }
135

    
136
                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGY_RULE);
137
                    dicColCheck.Clear();
138
                    dicColCheck.Add("UID", "TEXT");
139
                    if (matched == null)
140
                    {
141
                        var query = $"CREATE TABLE {PSN_TOPOLOGY_RULE} (UID TEXT)";
142
                        using (var cmd = connection.GetSqlStringCommand(query))
143
                        {
144
                            cmd.ExecuteNonQuery();
145
                        }
146

    
147
                        DataTable topologyRule = new DataTable();
148
                        topologyRule.Columns.Add("NAME", typeof(string));
149

    
150
                        topologyRule.Rows.Add("FluidCode");
151
                        topologyRule.Rows.Add("-");
152
                        topologyRule.Rows.Add("PipingMaterialsClass");
153
                        topologyRule.Rows.Add("-");
154
                        topologyRule.Rows.Add("Tag Seq No");
155

    
156
                        SaveTopologyRule(topologyRule);
157
                    }
158
                    //else
159
                    //{
160
                    //    AddColumn(PSN_TOPOLOGY_RULE, dicColCheck);
161
                    //}
162

    
163
                    matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEMNETWORK);
164
                    dicColCheck.Clear();
165
                    dicColCheck.Add("OID", "NVARCHAR(255)");
166
                    dicColCheck.Add("Type", "NVARCHAR(255)");
167
                    dicColCheck.Add("OrderNumber", "NVARCHAR(255)");
168
                    dicColCheck.Add("Pipeline_OID", "NVARCHAR(255)");
169
                    dicColCheck.Add("From_Data", "NVARCHAR(255)");
170
                    dicColCheck.Add("To_Data", "NVARCHAR(255)");
171
                    dicColCheck.Add("TopologySet_OID_Key", "NVARCHAR(125)");
172
                    dicColCheck.Add("PSNRevisionNumber", "NVARCHAR(255)");
173
                    dicColCheck.Add("IsValid", "INT");
174
                    dicColCheck.Add("Status", "NVARCHAR(255)");
175
                    dicColCheck.Add("PBS", "NVARCHAR(255)");
176
                    dicColCheck.Add("Drawings", "NVARCHAR(255)");
177
                    dicColCheck.Add("IncludingVirtualData", "NVARCHAR(10)");
178
                    dicColCheck.Add("PSNAccuracy", "REAL");
179
                    dicColCheck.Add("Pocket", "NVARCHAR(10)");
180
                    dicColCheck.Add("EGTag", "NVARCHAR(255)");
181
                    dicColCheck.Add("HasMLTags", "NVARCHAR(50)");
182

    
183
                    if (matched == null)
184
                    {
185
                        var query = $"CREATE TABLE {PSN_PIPESYSTEMNETWORK} (OID NVARCHAR(255), Type NVARCHAR(255), OrderNumber NVARCHAR(255), Pipeline_OID NVARCHAR(255), From_Data NVARCHAR(255), " +
186
                            "To_Data NVARCHAR(255), TopologySet_OID_Key NVARCHAR(125), PSNRevisionNumber NVARCHAR(255), IsValid INTEGER, Status NVARCHAR(255), PBS NVARCHAR(255), Drawings NVARCHAR(255), " +
187
                            "IncludingVirtualData NVARCHAR(10), PSNAccuracy REAL, Pocket NVARCHAR(10), EGTag NVARCHAR(255), HasMLTags NVARCHAR(50))";
188
                        using (var cmd = connection.GetSqlStringCommand(query))
189
                        {
190
                            cmd.ExecuteNonQuery();
191
                        }
192
                    }
193
                    else
194
                    {
195
                        AddColumn(PSN_PIPESYSTEMNETWORK, dicColCheck);
196
                    }
197

    
198
                    matched = names.FirstOrDefault(param => param == PSN_EQUIPMENT);
199
                    dicColCheck.Clear();
200
                    dicColCheck.Add("OID", "NVARCHAR(255)");
201
                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");
202
                    dicColCheck.Add("Xcoords", "REAL");
203
                    dicColCheck.Add("Ycoords", "REAL");
204
                    if (matched == null)
205
                    {
206
                        var query = $"CREATE TABLE {PSN_EQUIPMENT} (OID NVARCHAR(255), ItemTag NVARCHAR(255), Xcoords REAL, Ycoords REAL)";
207
                        using (var cmd = connection.GetSqlStringCommand(query))
208
                        {
209
                            cmd.ExecuteNonQuery();
210
                        }
211
                    }
212
                    else
213
                    {
214
                        AddColumn(PSN_EQUIPMENT, dicColCheck);
215
                    }
216

    
217
                    dicColCheck.Clear();
218
                    dicColCheck.Add("OID", "NVARCHAR(255)");
219
                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");
220
                    dicColCheck.Add("Xcoords", "REAL");
221
                    dicColCheck.Add("Ycoords", "REAL");
222
                    dicColCheck.Add("Equipment_OID", "NVARCHAR(255)");
223
                    dicColCheck.Add("Fluid", "NVARCHAR(255)");
224
                    dicColCheck.Add("NPD", "NVARCHAR(255)");
225
                    dicColCheck.Add("PMC", "NVARCHAR(255)");
226
                    dicColCheck.Add("Rotation", "REAL");
227
                    dicColCheck.Add("FlowDirection", "NVARCHAR(255)");
228

    
229
                    matched = names.FirstOrDefault(param => param == PSN_NOZZLE);
230
                    if (matched == null)
231
                    {
232
                        var query = $"CREATE TABLE {PSN_NOZZLE} (OID NVARCHAR(255), ItemTag NVARCHAR(255), Xcoords REAL, Ycoords REAL, Equipment_OID NVARCHAR(255), " +
233
                            "Fluid NVARCHAR(255), NPD NVARCHAR(255), PMC NVARCHAR(255), Rotation REAL, FlowDirection NVARCHAR(255))";
234
                        using (var cmd = connection.GetSqlStringCommand(query))
235
                        {
236
                            cmd.ExecuteNonQuery();
237
                        }
238
                    }
239
                    else
240
                    {
241
                        AddColumn(PSN_NOZZLE, dicColCheck);
242
                    }
243

    
244
                    matched = names.FirstOrDefault(param => param == PSN_FLUIDCODE);
245
                    dicColCheck.Clear();
246
                    dicColCheck.Add("UID", "NVARCHAR(50)");
247
                    dicColCheck.Add("Code", "NVARCHAR(255)");
248
                    dicColCheck.Add("Description", "NVARCHAR(255)");
249
                    dicColCheck.Add("Condition", "NVARCHAR(255)");
250
                    dicColCheck.Add("Remarks", "NVARCHAR(255)");
251
                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");
252
                    if (matched == null)
253
                    {
254
                        var query = $"CREATE TABLE {PSN_FLUIDCODE} (UID NVARCHAR(50), Code NVARCHAR(255), Description NVARCHAR(255), Condition NVARCHAR(255), Remarks NVARCHAR(255), GroundLevel NVARCHAR(20))";
255
                        using (var cmd = connection.GetSqlStringCommand(query))
256
                        {
257
                            cmd.ExecuteNonQuery();
258
                        }
259
                    }
260
                    else
261
                    {
262
                        AddColumn(PSN_FLUIDCODE, dicColCheck);
263
                    }
264

    
265
                    matched = names.FirstOrDefault(param => param == PSN_PIPINGMATLCLASS);
266
                    dicColCheck.Clear();
267
                    dicColCheck.Add("UID", "NVARCHAR(50)");
268
                    dicColCheck.Add("Priority", "INTEGER");
269
                    dicColCheck.Add("Code", "NVARCHAR(255)");
270
                    dicColCheck.Add("Description", "NVARCHAR(255)");
271
                    dicColCheck.Add("Condition", "NVARCHAR(255)");
272
                    dicColCheck.Add("Remarks", "NVARCHAR(255)");
273
                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");
274
                    if (matched == null)
275
                    {
276
                        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))";
277
                        using (var cmd = connection.GetSqlStringCommand(query))
278
                        {
279
                            cmd.ExecuteNonQuery();
280
                        }
281
                    }
282
                    else
283
                    {
284
                        AddColumn(PSN_PIPINGMATLCLASS, dicColCheck);
285
                    }
286

    
287
                    matched = names.FirstOrDefault(param => param == PSN_SEQUENCEDATA);
288
                    dicColCheck.Clear();
289
                    dicColCheck.Add("OID", "NVARCHAR(255)");
290
                    dicColCheck.Add("SERIALNUMBER", "NVARCHAR(255)");
291
                    dicColCheck.Add("PathItem_OID", "NVARCHAR(255)");
292
                    dicColCheck.Add("TopologySet_OID_Key", "NVARCHAR(255)");
293
                    if (matched == null)
294
                    {
295
                        var query = $"CREATE TABLE {PSN_SEQUENCEDATA} (OID NVARCHAR(255), SERIALNUMBER NVARCHAR(255), PathItem_OID NVARCHAR(255), TopologySet_OID_Key NVARCHAR(255))";
296
                        using (var cmd = connection.GetSqlStringCommand(query))
297
                        {
298
                            cmd.ExecuteNonQuery();
299
                        }
300
                    }
301
                    else
302
                    {
303
                        AddColumn(PSN_SEQUENCEDATA, dicColCheck);
304
                    }
305

    
306
                    matched = names.FirstOrDefault(param => param == PSN_PATHITEMS);
307
                    dicColCheck.Clear();
308
                    dicColCheck.Add("OID", "NVARCHAR(255)");
309
                    dicColCheck.Add("SequenceData_OID", "NVARCHAR(255)");
310
                    dicColCheck.Add("TopologySet_OID", "NVARCHAR(255)");
311
                    dicColCheck.Add("BranchTopologySet_OID", "NVARCHAR(255)");
312
                    dicColCheck.Add("PipeLine_OID", "NVARCHAR(255)");
313
                    dicColCheck.Add("ItemName", "NVARCHAR(255)");
314
                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");
315
                    dicColCheck.Add("DESCRIPTION", "NVARCHAR(255)");
316
                    dicColCheck.Add("CLASS", "NVARCHAR(255)");
317
                    dicColCheck.Add("SUBCLASS", "NVARCHAR(255)");
318
                    dicColCheck.Add("TYPE", "NVARCHAR(255)");
319
                    dicColCheck.Add("PIDNAME", "NVARCHAR(255)");
320
                    dicColCheck.Add("Equipment_OID", "NVARCHAR(255)");
321
                    dicColCheck.Add("NPD", "NVARCHAR(255)");
322
                    dicColCheck.Add("GROUPTAG", "NVARCHAR(255)");
323
                    dicColCheck.Add("PipeSystemNetwork_OID", "NVARCHAR(255)");
324
                    dicColCheck.Add("PipeRun_OID", "NVARCHAR(255)");
325
                    dicColCheck.Add("ViewPipeSystemNetwork_OID", "NVARCHAR(255)");
326

    
327
                    dicColCheck.Add("EqpGroupTag", "NVARCHAR(255)");
328
                    dicColCheck.Add("MainLineTag", "NVARCHAR(255)");
329

    
330
                    dicColCheck.Add("EGTConnectedPoint", "INT");
331
                    dicColCheck.Add("EGFlowDirection", "NVARCHAR(10)");
332

    
333
                    if (matched == null)
334
                    {
335
                        var query = $"CREATE TABLE {PSN_PATHITEMS} (OID NVARCHAR(255), SequenceData_OID NVARCHAR(255), " +
336
                            "TopologySet_OID NVARCHAR(255), BranchTopologySet_OID NVARCHAR(255), PipeLine_OID NVARCHAR(255), ItemName NVARCHAR(255), ItemTag NVARCHAR(255), DESCRIPTION NVARCHAR(255), " +
337
                            "CLASS NVARCHAR(255), SUBCLASS NVARCHAR(255), TYPE NVARCHAR(255), PIDNAME NVARCHAR(255), Equipment_OID NVARCHAR(255), NPD NVARCHAR(255), GROUPTAG NVARCHAR(255), PipeSystemNetwork_OID NVARCHAR(255), PipeRun_OID NVARCHAR(255), " +
338
                            "ViewPipeSystemNetwork_OID NVARCHAR(255), EqpGroupTag NVARCHAR(255), MainLineTag NVARCHAR(255), EGTConnectedPoint INT, EGFlowDirection NVARCHAR(10))";
339
                        using (var cmd = connection.GetSqlStringCommand(query))
340
                        {
341
                            cmd.ExecuteNonQuery();
342
                        }
343
                    }
344
                    else
345
                    {
346
                        AddColumn(PSN_PATHITEMS, dicColCheck);
347
                    }
348

    
349
                    matched = names.FirstOrDefault(param => param == PSN_VIEW);
350
                    dicColCheck.Clear();
351
                    dicColCheck.Add("OID", "TEXT");
352
                    if (matched == null)
353
                    {
354
                        var query = $"CREATE TABLE {PSN_VIEW} (OID TEXT)";
355
                        using (var cmd = connection.GetSqlStringCommand(query))
356
                        {
357
                            cmd.ExecuteNonQuery();
358
                        }
359
                    }
360

    
361
                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGYSET);
362
                    dicColCheck.Clear();
363
                    dicColCheck.Add("OID", "NVARCHAR(255)");
364
                    dicColCheck.Add("Type", "NVARCHAR(255)");
365
                    dicColCheck.Add("SubType", "NVARCHAR(255)");
366
                    dicColCheck.Add("HeadItemTag", "NVARCHAR(255)");
367
                    dicColCheck.Add("TailItemTag", "NVARCHAR(255)");
368
                    dicColCheck.Add("HeadItemSPID", "NVARCHAR(255)");
369
                    dicColCheck.Add("TailItemSPID", "NVARCHAR(255)");
370

    
371
                    if (matched == null)
372
                    {
373
                        var query = $"CREATE TABLE {PSN_TOPOLOGYSET} (OID NVARCHAR(255), Type NVARCHAR(255), SubType NVARCHAR(255), HeadItemTag NVARCHAR(255), TailItemTag NVARCHAR(255), HeadItemSPID NVARCHAR(255), TailItemSPID NVARCHAR(255))";
374
                        using (var cmd = connection.GetSqlStringCommand(query))
375
                        {
376
                            cmd.ExecuteNonQuery();
377
                        }
378
                    }
379
                    else
380
                    {
381
                        AddColumn(PSN_TOPOLOGYSET, dicColCheck);
382
                    }
383

    
384
                    matched = names.FirstOrDefault(param => param == PSN_TRANSFORMKEYWORD_SETTING);
385
                    dicColCheck.Clear();
386
                    dicColCheck.Add("[INDEX]", "INTEGER");
387
                    dicColCheck.Add("[NAME]", "TEXT");
388
                    dicColCheck.Add("[KEYWORD]", "TEXT");
389
                    if (matched == null)
390
                    {
391
                        var query = $"CREATE TABLE {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX] INTEGER, [NAME] TEXT, [KEYWORD] TEXT)";
392
                        using (var cmd = connection.GetSqlStringCommand(query))
393
                        {
394
                            cmd.ExecuteNonQuery();
395
                        }
396
                    }
397
                    else
398
                    {
399
                        AddColumn(PSN_TRANSFORMKEYWORD_SETTING, dicColCheck);
400
                    }
401

    
402
                    matched = names.FirstOrDefault(param => param == PSN_PIPELINE);
403
                    dicColCheck.Clear();
404
                    dicColCheck.Add("OID", "NVARCHAR(255)");
405
                    dicColCheck.Add("PipeSystem_OID", "NVARCHAR(255)");
406
                    dicColCheck.Add("FLUID", "NVARCHAR(255)");
407
                    dicColCheck.Add("PMC", "NVARCHAR(255)");
408
                    dicColCheck.Add("SEQNUMBER", "NVARCHAR(255)");
409
                    dicColCheck.Add("INSULATION", "NVARCHAR(255)");
410
                    dicColCheck.Add("FROM_DATA", "NVARCHAR(255)");
411
                    dicColCheck.Add("TO_DATA", "NVARCHAR(255)");
412
                    dicColCheck.Add("Unit", "NVARCHAR(100)");
413
                    if (matched == null)
414
                    {
415
                        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), " +
416
                            $"FROM_DATA NVARCHAR(255), TO_DATA NVARCHAR(255), Unit NVARCHAR(100))";
417
                        using (var cmd = connection.GetSqlStringCommand(query))
418
                        {
419
                            cmd.ExecuteNonQuery();
420
                        }
421
                    }
422
                    else
423
                    {
424
                        AddColumn(PSN_PIPELINE, dicColCheck);
425
                    }
426

    
427
                    matched = names.FirstOrDefault(param => param == PSN_INSULATIONPURPOSE);
428
                    dicColCheck.Clear();
429
                    dicColCheck.Add("UID", "NVARCHAR(50)");
430
                    dicColCheck.Add("Code", "NVARCHAR(255)");
431
                    dicColCheck.Add("Description", "NVARCHAR(255)");
432
                    dicColCheck.Add("Remarks", "NVARCHAR(255)");
433
                    if (matched == null)
434
                    {
435
                        var query = $"CREATE TABLE {PSN_INSULATIONPURPOSE} (UID NVARCHAR(50), Code NVARCHAR(255), Description NVARCHAR(255), Remarks NVARCHAR(255))";
436
                        using (var cmd = connection.GetSqlStringCommand(query))
437
                        {
438
                            cmd.ExecuteNonQuery();
439
                        }
440
                    }
441
                    else
442
                    {
443
                        AddColumn(PSN_INSULATIONPURPOSE, dicColCheck);
444
                    }
445

    
446
                    matched = names.FirstOrDefault(param => param == PSN_VALVEGROUP_SETTING);
447
                    dicColCheck.Clear();
448
                    dicColCheck.Add("OID", "NVARCHAR(255)");
449
                    dicColCheck.Add("GroupType", "NVARCHAR(255)");
450
                    dicColCheck.Add("TagIdentifier", "NVARCHAR(50)");
451
                    dicColCheck.Add("AttributeName", "NVARCHAR(255)");
452
                    dicColCheck.Add("SppidSymbolName", "NVARCHAR(255)");
453
                    if (matched == null)
454
                    {
455
                        var query = $"CREATE TABLE {PSN_VALVEGROUP_SETTING} (OID NVARCHAR(255), GroupType NVARCHAR(255), TagIdentifier NVARCHAR(50), AttributeName NVARCHAR(255), SppidSymbolName NVARCHAR(255))";
456
                        using (var cmd = connection.GetSqlStringCommand(query))
457
                        {
458
                            cmd.ExecuteNonQuery();
459
                        }
460
                    }
461
                    else
462
                    {
463
                        AddColumn(PSN_VALVEGROUP_SETTING, dicColCheck);
464
                    }
465

    
466
                    matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEM);
467
                    dicColCheck.Clear();
468
                    dicColCheck.Add("OID", "NVARCHAR(255)");
469
                    dicColCheck.Add("DESCRIPTION", "NVARCHAR(255)");
470
                    dicColCheck.Add("FLUID", "NVARCHAR(255)");
471
                    dicColCheck.Add("PMC", "NVARCHAR(255)");
472
                    dicColCheck.Add("PipeLineQty", "NVARCHAR(255)");
473
                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");
474
                    if (matched == null)
475
                    {
476
                        var query = $"CREATE TABLE {PSN_PIPESYSTEM} (OID NVARCHAR(255), DESCRIPTION NVARCHAR(255), FLUID NVARCHAR(255), PMC NVARCHAR(255), PipeLineQty NVARCHAR(255), GroundLevel NVARCHAR(20))";
477
                        using (var cmd = connection.GetSqlStringCommand(query))
478
                        {
479
                            cmd.ExecuteNonQuery();
480
                        }
481
                    }
482
                    else
483
                    {
484
                        AddColumn(PSN_PIPESYSTEM, dicColCheck);
485
                    }
486

    
487
                    matched = names.FirstOrDefault(param => param == PSN_NOPOCKETSETTING);
488
                    dicColCheck.Clear();
489
                    dicColCheck.Add("[INDEX]", "INTEGER");
490
                    dicColCheck.Add("[TYPE]", "TEXT");
491
                    dicColCheck.Add("[NAME]", "TEXT");
492
                    if (matched == null)
493
                    {
494
                        var query = $"CREATE TABLE {PSN_NOPOCKETSETTING} ([INDEX] INTEGER, [TYPE] TEXT, [NAME] TEXT)";
495
                        using (var cmd = connection.GetSqlStringCommand(query))
496
                        {
497
                            cmd.ExecuteNonQuery();
498
                        }
499
                    }
500
                    else
501
                    {
502
                        AddColumn(PSN_NOPOCKETSETTING, dicColCheck);
503
                    }
504

    
505
                    matched = names.FirstOrDefault(param => param == PSN_AIRFINCOOLERSETTING);
506
                    dicColCheck.Clear();
507
                    dicColCheck.Add("[INDEX]", "INTEGER");
508
                    dicColCheck.Add("[TYPE]", "TEXT");
509
                    dicColCheck.Add("[NAME]", "TEXT");
510
                    if (matched == null)
511
                    {
512
                        var query = $"CREATE TABLE {PSN_AIRFINCOOLERSETTING} ([INDEX] INTEGER, [TYPE] TEXT, [NAME] TEXT)";
513
                        using (var cmd = connection.GetSqlStringCommand(query))
514
                        {
515
                            cmd.ExecuteNonQuery();
516
                        }
517
                    }
518
                    else
519
                    {
520
                        AddColumn(PSN_AIRFINCOOLERSETTING, dicColCheck);
521
                    }
522

    
523

    
524
                    void AddColumn(string TableName, Dictionary<string, string> dicCol)
525
                    {
526
                        var colnames = connection.GetColumnNames(TableName);
527
                        bool check = false;
528
                        if (colnames != null)
529
                        {
530
                            foreach (KeyValuePair<string, string> col in dicCol)
531
                            {
532
                                check = false;
533
                                foreach (string c in colnames)
534
                                {
535
                                    if (col.Key.Contains(c))
536
                                    {
537
                                        check = true;
538
                                        break;
539
                                    }
540
                                }
541

    
542
                                if (!check) //없으면 추가
543
                                {
544
                                    string i = string.Empty;
545
                                    if (col.Value.ToString().ToUpper().Contains("INT") || col.Value.ToString().ToUpper().Contains("REAL"))
546
                                        i = "DEFAULT 0";
547

    
548
                                    var query = $"ALTER TABLE " + TableName + " ADD [" + col.Key + "] " + col.Value + " " + i + ";";
549
                                    using (var cmd = connection.GetSqlStringCommand(query))
550
                                    {
551
                                        cmd.ExecuteNonQuery();
552
                                    }
553
                                }
554
                            }
555
                        }
556
                    }
557

    
558
                    result = true;
559
                }
560
                catch (Exception ex)
561
                {
562
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
563
                }
564
            }
565

    
566
            return result;
567
        }
568

    
569
        // ID2 DB 데이터
570
        /// <summary>
571
        /// ID2 데이타베이스에서 OPC 데이터를 조회
572
        /// </summary>
573
        /// <returns></returns>
574
        public static DataTable SelectOPCRelations()
575
        {
576
            DataTable dt = null;
577
            ID2Info id2Info = ID2Info.GetInstance();
578

    
579
            using (IAbstractDatabase connection = id2Info.CreateConnection())
580
            {
581
                try
582
                {
583
                    var query = "SELECT * FROM OPCRelations;";
584
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
585
                    {
586
                        dt = ds.Tables[0].Copy();
587
                    }
588
                }
589
                catch (Exception ex)
590
                {
591
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
592
                }
593
            }
594

    
595
            return dt;
596
        }
597

    
598
        /// <summary>
599
        /// ID2 데이타베이스에서 도면 데이터를 조회
600
        /// </summary>
601
        /// <returns></returns>
602
        public static DataTable SelectDrawings()
603
        {
604
            DataTable dt = null;
605
            ID2Info id2Info = ID2Info.GetInstance();
606

    
607
            using (IAbstractDatabase connection = id2Info.CreateConnection())
608
            {
609
                try
610
                {
611
                    var query = "SELECT DISTINCT NAME FROM [Drawings] order by Name";
612
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
613
                    {
614
                        dt = ds.Tables[0].Copy();
615
                    }
616
                }
617
                catch (Exception ex)
618
                {
619
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
620
                }
621
            }
622

    
623
            return dt;
624
        }
625

    
626
        public static DataTable AllDrawings()
627
        {
628
            DataTable dt = null;
629
            ID2Info id2Info = ID2Info.GetInstance();
630

    
631
            using (IAbstractDatabase connection = id2Info.CreateConnection())
632
            {
633
                try
634
                {
635
                    var query = "SELECT * FROM [Drawings]";
636
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
637
                    {
638
                        dt = ds.Tables[0].Copy();
639
                    }
640
                }
641
                catch (Exception ex)
642
                {
643
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
644
                }
645
            }
646

    
647
            return dt;
648
        }
649

    
650
        public static DataTable SelectLineProperties()
651
        {
652
            DataTable dt = null;
653
            ID2Info id2Info = ID2Info.GetInstance();
654

    
655
            using (IAbstractDatabase connection = id2Info.CreateConnection())
656
            {
657
                try
658
                {
659
                    var query = "SELECT * FROM LineProperties";
660
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
661
                    {
662
                        dt = ds.Tables[0].Copy();
663
                    }
664
                }
665
                catch (Exception ex)
666
                {
667
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
668
                }
669
            }
670

    
671
            return dt;
672
        }
673

    
674
        public static DataTable SelectFluidCode()
675
        {
676
            DataTable dt = null;
677
            ID2Info id2Info = ID2Info.GetInstance();
678

    
679
            using (IAbstractDatabase connection = id2Info.CreateConnection())
680
            {
681
                try
682
                {
683
                    var query = "SELECT * FROM FluidCode";
684
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
685
                    {
686
                        dt = ds.Tables[0].Copy();
687
                    }
688
                }
689
                catch (Exception ex)
690
                {
691
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
692
                }
693
            }
694

    
695
            return dt;
696
        }
697

    
698
        public static DataTable SelectPipingMaterialsClass()
699
        {
700
            DataTable dt = null;
701
            ID2Info id2Info = ID2Info.GetInstance();
702

    
703
            using (IAbstractDatabase connection = id2Info.CreateConnection())
704
            {
705
                try
706
                {
707
                    var query = "SELECT * FROM PipingMaterialsClass";
708
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
709
                    {
710
                        dt = ds.Tables[0].Copy();
711
                    }
712
                }
713
                catch (Exception ex)
714
                {
715
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
716
                }
717
            }
718

    
719
            return dt;
720
        }
721

    
722
        public static DataTable SelectPSNPIPINGMATLCLASS()
723
        {
724
            DataTable dt = null;
725
            ID2Info id2Info = ID2Info.GetInstance();
726

    
727
            using (IAbstractDatabase connection = id2Info.CreateConnection())
728
            {
729
                try
730
                {
731
                    var query = $"SELECT * FROM {PSN_PIPINGMATLCLASS}";
732
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
733
                    {
734
                        dt = ds.Tables[0].Copy();
735
                    }
736
                }
737
                catch (Exception ex)
738
                {
739
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
740
                }
741
            }
742

    
743
            return dt;
744
        }
745

    
746
        public static DataTable SelectInsulationPurpose()
747
        {
748
            DataTable dt = null;
749
            ID2Info id2Info = ID2Info.GetInstance();
750

    
751
            using (IAbstractDatabase connection = id2Info.CreateConnection())
752
            {
753
                try
754
                {
755
                    var query = "SELECT * FROM InsulationPurpose";
756
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
757
                    {
758
                        dt = ds.Tables[0].Copy();
759
                    }
760
                }
761
                catch (Exception ex)
762
                {
763
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
764
                }
765
            }
766

    
767
            return dt;
768
        }
769

    
770
        public static DataTable SelectPSNINSULATIONPURPOSE()
771
        {
772
            DataTable dt = null;
773
            ID2Info id2Info = ID2Info.GetInstance();
774

    
775
            using (IAbstractDatabase connection = id2Info.CreateConnection())
776
            {
777
                try
778
                {
779
                    var query = $"SELECT * FROM {PSN_INSULATIONPURPOSE}";
780
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
781
                    {
782
                        dt = ds.Tables[0].Copy();
783
                    }
784
                }
785
                catch (Exception ex)
786
                {
787
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
788
                }
789
            }
790

    
791
            return dt;
792
        }
793

    
794
        public static DataTable SelectNominalDiameter()
795
        {
796
            DataTable dt = null;
797
            ID2Info id2Info = ID2Info.GetInstance();
798

    
799
            using (IAbstractDatabase connection = id2Info.CreateConnection())
800
            {
801
                try
802
                {
803
                    var query = "SELECT * FROM NominalDiameter ORDER BY Metric DESC";
804
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
805
                    {
806
                        dt = ds.Tables[0].Copy();
807
                    }
808
                }
809
                catch (Exception ex)
810
                {
811
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
812
                }
813
            }
814

    
815
            ///TODO: need to check below code
816
            dt.Rows.RemoveAt(0);
817
            dt.Rows.RemoveAt(0);
818
            dt.Rows.RemoveAt(0);
819
            dt.Rows.RemoveAt(0);
820

    
821
            return dt;
822
        }
823

    
824
        public static DataTable SelectAllSymbolAttribute()
825
        {
826
            DataTable dt = null;
827
            ID2Info id2Info = ID2Info.GetInstance();
828

    
829
            using (IAbstractDatabase connection = id2Info.CreateConnection())
830
            {
831
                try
832
                {
833
                    var query = "SELECT B.Name AS SymbolName, Attribute AS SymbolAttribute, A.SymbolType_UID ,Attribute ,DisplayAttribute ,AttributeType, Expression FROM SymbolAttribute A INNER JOIN Symbol B ON A.SymbolType_UID = B.SymbolType_UID; ";
834
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
835
                    {
836
                        dt = ds.Tables[0].Copy();
837
                    }
838
                }
839
                catch (Exception ex)
840
                {
841
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
842
                }
843
            }
844

    
845
            return dt;
846
        }
847

    
848
        public static DataTable SelectSymbolAttribute()
849
        {
850
            DataTable dt = null;
851
            ID2Info id2Info = ID2Info.GetInstance();
852

    
853
            using (IAbstractDatabase connection = id2Info.CreateConnection())
854
            {
855
                try
856
                {
857
                    // var query = "SELECT DISTINCT Attribute, DisplayAttribute FROM SymbolAttribute;";
858
                    var query = "SELECT DISTINCT Attribute FROM SymbolAttribute;";
859
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
860
                    {
861
                        dt = ds.Tables[0].Copy();
862
                    }
863
                }
864
                catch (Exception ex)
865
                {
866
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
867
                }
868
            }
869

    
870
            return dt;
871
        }
872

    
873
        public static DataTable SelectSymbolName()
874
        {
875
            DataTable dt = null;
876
            ID2Info id2Info = ID2Info.GetInstance();
877

    
878
            using (IAbstractDatabase connection = id2Info.CreateConnection())
879
            {
880
                try
881
                {
882
                    var query = "SELECT * FROM SymbolName;";
883
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
884
                    {
885
                        dt = ds.Tables[0].Copy();
886
                    }
887
                }
888
                catch (Exception ex)
889
                {
890
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
891
                }
892
            }
893

    
894
            return dt;
895
        }
896

    
897
        public static double[] GetDrawingSize()
898
        {
899
            double[] result = null;
900

    
901
            ID2Info id2Info = ID2Info.GetInstance();
902
            using (IAbstractDatabase connection = id2Info.CreateConnection())
903
            {
904
                try
905
                {
906
                    var query = "SELECT value FROM Configuration WHERE Section = 'Area' AND [Key] = 'Drawing';";
907
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
908
                    {
909
                        if (ds.Tables[0].Rows.Count == 1)
910
                        {
911
                            string value = ds.Tables[0].Rows[0][0].ToString();
912
                            string[] split = value.Split(new char[] { ',' });
913
                            result = new double[] {
914
                                Convert.ToDouble(Regex.Replace(split[0], @"[^0-9]", string.Empty)),
915
                                Convert.ToDouble(Regex.Replace(split[1], @"[^0-9]", string.Empty)),
916
                                Convert.ToDouble(Regex.Replace(split[2], @"[^0-9]", string.Empty)),
917
                                Convert.ToDouble(Regex.Replace(split[3], @"[^0-9]", string.Empty))
918
                                };
919
                            result = new double[] {
920
                                Math.Min(result[0], result[2]),
921
                                Math.Min(result[1], result[3]),
922
                                Math.Max(result[0], result[2]),
923
                                Math.Max(result[1], result[3])
924
                                };
925
                        }
926
                    }
927
                }
928
                catch (Exception ex)
929
                {
930
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
931
                }
932
            }
933

    
934
            return result;
935
        }
936

    
937
        public static DataTable GetEquipmentType()
938
        {
939
            DataTable dt = null;
940
            ID2Info id2Info = ID2Info.GetInstance();
941

    
942
            using (IAbstractDatabase connection = id2Info.CreateConnection())
943
            {
944
                try
945
                {
946
                    var query = "SELECT Type FROM SymbolType WHERE Category = 'Equipment';";
947
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
948
                    {
949
                        dt = ds.Tables[0].Copy();
950
                    }
951
                }
952
                catch (Exception ex)
953
                {
954
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
955
                }
956
            }
957

    
958
            return dt;
959
        }
960

    
961
        /// <summary>
962
        ///  Zoom 을 위하여 저장 T_PSN_VIEW 데이터로 PSN 데이터지만 Msqql 과 Sqlite 둘다 저장되어 있음
963
        ///  => ID2 가 DB 타입에 따라서 바라보는 DB가 mssql이냐 sqlite냐로 지정되기 때문에 zoom 기능을 그대로 사용하려면 해당 데이터는 psn도 id2 dbtype에 맞춰서 저장 및 로드해야함
964
        ///  => 더 이상 사용하지 않음
965
        /// </summary>
966
        /// <param name="values"></param>
967
        /// <returns></returns>
968
        public static bool SaveView(List<string> values)
969
        {
970
            ID2Info id2Info = ID2Info.GetInstance();
971

    
972
            bool result = true;
973

    
974
            using (IAbstractDatabase connection = id2Info.CreateConnection())
975
            {
976
                try
977
                {
978
                    using (var txn = connection.BeginTransaction())
979
                    {
980
                        try
981
                        {
982
                            var query = $"DELETE FROM {PSN_VIEW}";
983
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
984

    
985
                            foreach (string value in values)
986
                            {
987
                                query = $"INSERT INTO {PSN_VIEW} (OID) VALUES (@OID)";
988
                                var cmd = connection.GetSqlStringCommand(query);
989
                                AddWithValue(cmd, "@OID", value);
990
                                connection.ExecuteNonQuery(cmd, txn);
991
                            }
992
                            txn.Commit();
993
                        }
994
                        catch (Exception ex)
995
                        {
996
                            txn.Rollback();
997
                            result = false;
998
                        }
999
                    }
1000
                }
1001
                catch (Exception ex)
1002
                {
1003
                    System.Windows.Forms.MessageBox.Show(ex.Message);
1004
                    result = false;
1005
                }
1006
            }
1007

    
1008
            return result;
1009
        }
1010

    
1011
        public static bool DeleteView()
1012
        {
1013
            ID2Info id2Info = ID2Info.GetInstance();
1014

    
1015
            bool result = true;
1016
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1017
            {
1018
                try
1019
                {
1020
                    using (var txn = connection.BeginTransaction())
1021
                    {
1022
                        try
1023
                        {
1024
                            var query = $"DELETE FROM {PSN_VIEW}";
1025
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1026
                            txn.Commit();
1027
                        }
1028
                        catch (Exception ex)
1029
                        {
1030
                            txn.Rollback();
1031
                            result = false;
1032
                        }
1033
                    }
1034
                }
1035
                catch (Exception ex)
1036
                {
1037
                    System.Windows.Forms.MessageBox.Show(ex.Message);
1038
                    result = false;
1039
                }
1040
            }
1041

    
1042
            return result;
1043
        }
1044

    
1045
        //PSN Sqlite 
1046
        public static DataTable SelectHeaderSetting()
1047
        {
1048
            DataTable dt = null;
1049
            ID2Info id2Info = ID2Info.GetInstance();
1050

    
1051
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1052
            {
1053
                try
1054
                {
1055
                    var query = $@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {PSN_HEADER_SETTING};";
1056
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1057
                    {
1058
                        dt = ds.Tables[0].Copy();
1059
                    }
1060
                }
1061
                catch (Exception ex)
1062
                {
1063
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1064
                }
1065
            }
1066

    
1067
            return dt;
1068
        }
1069

    
1070
        public static DataTable SelectVentDrainSetting()
1071
        {
1072
            DataTable dt = null;
1073
            ID2Info id2Info = ID2Info.GetInstance();
1074

    
1075
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1076
            {
1077
                try
1078
                {
1079
                    var query = $@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {PSN_VENTDRAIN_SETTING};";
1080
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1081
                    {
1082
                        dt = ds.Tables[0].Copy();
1083
                    }
1084
                }
1085
                catch (Exception ex)
1086
                {
1087
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1088
                }
1089
            }
1090

    
1091
            return dt;
1092
        }
1093

    
1094
        public static DataTable SelectKeywordsSetting()
1095
        {
1096
            DataTable dt = null;
1097
            ID2Info id2Info = ID2Info.GetInstance();
1098

    
1099
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1100
            {
1101
                try
1102
                {
1103
                    var query = $@"SELECT [KEYWORD], [INDEX], [NAME] FROM {PSN_TRANSFORMKEYWORD_SETTING};";
1104
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1105
                    {
1106
                        dt = ds.Tables[0].Copy();
1107
                    }
1108
                }
1109
                catch (Exception ex)
1110
                {
1111
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1112
                }
1113
            }
1114

    
1115
            return dt;
1116
        }
1117

    
1118
        public static DataTable SelectValveGroupItemsSetting()
1119
        {
1120
            DataTable dt = null;
1121
            ID2Info id2Info = ID2Info.GetInstance();
1122

    
1123
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1124
            {
1125
                try
1126
                {
1127
                    var query = $@"SELECT OID, GroupType, TagIdentifier, AttributeName, SppidSymbolName FROM {PSN_VALVEGROUP_SETTING};";
1128
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1129
                    {
1130
                        dt = ds.Tables[0].Copy();
1131
                    }
1132
                }
1133
                catch (Exception ex)
1134
                {
1135
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1136
                }
1137
            }
1138

    
1139
            return dt;
1140
        }
1141

    
1142

    
1143
        public static DataTable SelectEquipmentNoPocketSetting()
1144
        {
1145
            DataTable dt = null;
1146
            ID2Info id2Info = ID2Info.GetInstance();
1147

    
1148
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1149
            {
1150
                try
1151
                {
1152
                    var query = $@"SELECT [INDEX], [TYPE], [NAME] FROM {PSN_NOPOCKETSETTING};";
1153
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1154
                    {
1155
                        dt = ds.Tables[0].Copy();
1156
                    }
1157
                }
1158
                catch (Exception ex)
1159
                {
1160
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1161
                }
1162
            }
1163

    
1164
            return dt;
1165
        }
1166

    
1167
        public static DataTable SelectAirFinCoolerSetting()
1168
        {
1169
            DataTable dt = null;
1170
            ID2Info id2Info = ID2Info.GetInstance();
1171

    
1172
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1173
            {
1174
                try
1175
                {
1176
                    var query = $@"SELECT [INDEX], [TYPE], [NAME] FROM {PSN_AIRFINCOOLERSETTING};";
1177
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1178
                    {
1179
                        dt = ds.Tables[0].Copy();
1180
                    }
1181
                }
1182
                catch (Exception ex)
1183
                {
1184
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1185
                }
1186
            }
1187

    
1188
            return dt;
1189
        }
1190

    
1191
        public static bool SaveHeaderSetting(List<HeaderInfo> headerInfos)
1192
        {
1193
            ID2Info id2Info = ID2Info.GetInstance();
1194
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1195
            {
1196
                try
1197
                {
1198
                    using (var txn = connection.BeginTransaction())
1199
                    {
1200
                        var query = $"DELETE FROM {PSN_HEADER_SETTING}";
1201
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1202

    
1203
                        foreach (HeaderInfo headerInfo in headerInfos)
1204
                        {
1205
                            foreach (HeaderItem item in headerInfo.HeaderItems)
1206
                            {
1207
                                query = $"INSERT INTO {PSN_HEADER_SETTING} (GROUP_ID, DESCRIPTION, [INDEX], NAME) VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";
1208
                                var cmd = connection.GetSqlStringCommand(query);
1209
                                AddWithValue(cmd, "@GROUP_ID", headerInfo.UID);
1210
                                AddWithValue(cmd, "@DESCRIPTION", headerInfo.Description);
1211
                                AddWithValue(cmd, "@INDEX", item.Index);
1212
                                AddWithValue(cmd, "@NAME", item.Name);
1213
                                connection.ExecuteNonQuery(cmd, txn);
1214
                            }
1215
                        }
1216
                        txn.Commit();
1217
                    }
1218

    
1219
                }
1220
                catch (Exception ex)
1221
                {
1222
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1223
                    return false;
1224
                }
1225
            }
1226
            return true;
1227
        }
1228

    
1229
        public static bool SaveVentDrainSetting(List<VentDrainInfo> ventDrainInfos)
1230
        {
1231
            ID2Info id2Info = ID2Info.GetInstance();
1232
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1233
            {
1234
                using (var txn = connection.BeginTransaction())
1235
                {
1236
                    try
1237
                    {
1238
                        var query = $"DELETE FROM {PSN_VENTDRAIN_SETTING}";
1239
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1240

    
1241
                        foreach (VentDrainInfo ventDrainInfo in ventDrainInfos)
1242
                        {
1243
                            foreach (VentDrainItem item in ventDrainInfo.VentDrainItems)
1244
                            {
1245
                                query = $"INSERT INTO {PSN_VENTDRAIN_SETTING} (GROUP_ID, DESCRIPTION, [INDEX], NAME) VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";
1246
                                var cmd = connection.GetSqlStringCommand(query);
1247
                                AddWithValue(cmd, "@GROUP_ID", ventDrainInfo.UID);
1248
                                AddWithValue(cmd, "@DESCRIPTION", ventDrainInfo.Description);
1249
                                AddWithValue(cmd, "@INDEX", item.Index);
1250
                                AddWithValue(cmd, "@NAME", item.Name);
1251
                                connection.ExecuteNonQuery(cmd, txn);
1252
                            }
1253
                        }
1254

    
1255
                        txn.Commit();
1256
                    }
1257
                    catch (Exception ex)
1258
                    {
1259
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1260
                        return false;
1261
                    }
1262
                }
1263
            }
1264

    
1265
            return true;
1266
        }
1267

    
1268
        public static bool SaveValveGroupItemsSetting(List<ValveGroupItem> valveGroupItems)
1269
        {
1270
            ID2Info id2Info = ID2Info.GetInstance();
1271
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1272
            {
1273
                using (var txn = connection.BeginTransaction())
1274
                {
1275
                    try
1276
                    {
1277
                        var query = $"DELETE FROM {PSN_VALVEGROUP_SETTING}";
1278
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1279

    
1280
                        foreach (ValveGroupItem item in valveGroupItems)
1281
                        {
1282
                            query = $"INSERT INTO {PSN_VALVEGROUP_SETTING} (OID, GroupType, TagIdentifier, AttributeName, SppidSymbolName) " +
1283
                                $"VALUES (@OID, @GroupType, @TagIdentifier, @AttributeName, @SppidSymbolName)";
1284
                            var cmd = connection.GetSqlStringCommand(query);
1285
                            AddWithValue(cmd, "@OID", item.OID);
1286
                            AddWithValue(cmd, "@GroupType", item.GroupType);
1287
                            AddWithValue(cmd, "@TagIdentifier", item.TagIdentifier);
1288
                            AddWithValue(cmd, "@AttributeName", item.AttributeName);
1289
                            AddWithValue(cmd, "@SppidSymbolName", item.SppidSymbolName);
1290
                            connection.ExecuteNonQuery(cmd, txn);
1291
                        }
1292

    
1293
                        txn.Commit();
1294
                    }
1295
                    catch (Exception ex)
1296
                    {
1297
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1298
                        return false;
1299
                    }
1300
                }
1301
            }
1302

    
1303
            return true;
1304
        }
1305

    
1306
        public static bool SaveKeywordsSetting(List<KeywordItem> keywordItems)
1307
        {
1308
            ID2Info id2Info = ID2Info.GetInstance();
1309
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1310
            {
1311
                using (var txn = connection.BeginTransaction())
1312
                {
1313
                    try
1314
                    {
1315
                        var query = $"DELETE FROM {PSN_TRANSFORMKEYWORD_SETTING}";
1316
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1317

    
1318
                        foreach (KeywordItem item in keywordItems)
1319
                        {
1320
                            query = $"INSERT INTO {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX], NAME, KEYWORD) VALUES (@INDEX, @NAME, @KEYWORD)";
1321
                            var cmd = connection.GetSqlStringCommand(query);
1322
                            AddWithValue(cmd, "@INDEX", item.Index);
1323
                            AddWithValue(cmd, "@NAME", item.Name);
1324
                            AddWithValue(cmd, "@KEYWORD", item.Keyword);
1325
                            connection.ExecuteNonQuery(cmd, txn);
1326
                        }
1327

    
1328
                        txn.Commit();
1329
                    }
1330
                    catch (Exception ex)
1331
                    {
1332
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1333
                        return false;
1334
                    }
1335
                }
1336
            }
1337

    
1338
            return true;
1339
        }
1340

    
1341
        public static bool SaveEquipmentNopocketSetting(List<EquipmentNoPocketItem> keywordItems)
1342
        {
1343
            ID2Info id2Info = ID2Info.GetInstance();
1344
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1345
            {
1346
                using (var txn = connection.BeginTransaction())
1347
                {
1348
                    try
1349
                    {
1350
                        var query = $"DELETE FROM {PSN_NOPOCKETSETTING}";
1351
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1352

    
1353
                        foreach (EquipmentNoPocketItem item in keywordItems)
1354
                        {
1355
                            query = $"INSERT INTO {PSN_NOPOCKETSETTING} ([INDEX], [TYPE], [NAME]) VALUES (@INDEX, @TYPE, @NAME)";
1356
                            var cmd = connection.GetSqlStringCommand(query);
1357
                            AddWithValue(cmd, "@INDEX", item.Index);
1358
                            AddWithValue(cmd, "@TYPE", item.Type);
1359
                            AddWithValue(cmd, "@NAME", item.Name);
1360
                            connection.ExecuteNonQuery(cmd, txn);
1361
                        }
1362

    
1363
                        txn.Commit();
1364
                    }
1365
                    catch (Exception ex)
1366
                    {
1367
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1368
                        return false;
1369
                    }
1370
                }
1371
            }
1372

    
1373
            return true;
1374
        }
1375

    
1376
        public static bool SaveAirFinCoolerSetting(List<EquipmentAirFinCoolerItem> keywordItems)
1377
        {
1378
            ID2Info id2Info = ID2Info.GetInstance();
1379
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1380
            {
1381
                using (var txn = connection.BeginTransaction())
1382
                {
1383
                    try
1384
                    {
1385
                        var query = $"DELETE FROM {PSN_AIRFINCOOLERSETTING}";
1386
                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1387

    
1388
                        foreach (EquipmentAirFinCoolerItem item in keywordItems)
1389
                        {
1390
                            query = $"INSERT INTO {PSN_AIRFINCOOLERSETTING} ([INDEX], [TYPE], [NAME]) VALUES (@INDEX, @TYPE, @NAME)";
1391
                            var cmd = connection.GetSqlStringCommand(query);
1392
                            AddWithValue(cmd, "@INDEX", item.Index);
1393
                            AddWithValue(cmd, "@TYPE", item.Type);
1394
                            AddWithValue(cmd, "@NAME", item.Name);
1395
                            connection.ExecuteNonQuery(cmd, txn);
1396
                        }
1397

    
1398
                        txn.Commit();
1399
                    }
1400
                    catch (Exception ex)
1401
                    {
1402
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1403
                        return false;
1404
                    }
1405
                }
1406
            }
1407

    
1408
            return true;
1409
        }
1410

    
1411
        public static bool SaveTopologyRule(DataTable dt)
1412
        {
1413
            ID2Info id2Info = ID2Info.GetInstance();
1414
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1415
            {
1416
                using (var txn = connection.BeginTransaction())
1417
                {
1418
                    try
1419
                    {
1420
                        var query = $"DELETE FROM {PSN_TOPOLOGY_RULE}";
1421
                        var cmd = connection.GetSqlStringCommand(query);
1422
                        //connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1423
                        connection.ExecuteNonQuery(cmd, txn);
1424

    
1425
                        foreach (DataRow row in dt.Rows)
1426
                        {
1427
                            query = $"INSERT INTO {PSN_TOPOLOGY_RULE} (UID) VALUES (@UID)";
1428
                            cmd = connection.GetSqlStringCommand(query);
1429
                            AddWithValue(cmd, "@UID", row["NAME"].ToString());
1430
                            connection.ExecuteNonQuery(cmd, txn);
1431
                        }
1432

    
1433
                        txn.Commit();
1434
                    }
1435
                    catch (Exception ex)
1436
                    {
1437
                        txn.Rollback();
1438
                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1439
                        return false;
1440
                    }
1441
                }
1442
            }
1443

    
1444
            return true;
1445
        }
1446

    
1447
        public static DataTable SelectTopologyRule()
1448
        {
1449
            DataTable dt = null;
1450

    
1451
            ID2Info id2Info = ID2Info.GetInstance();
1452
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1453
            {
1454
                try
1455
                {
1456
                    var query = $"SELECT * FROM {PSN_TOPOLOGY_RULE}";
1457
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1458
                    {
1459
                        dt = ds.Tables[0].Copy();
1460
                    }
1461
                }
1462
                catch (Exception ex)
1463
                {
1464
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1465
                }
1466
            }
1467

    
1468
            return dt;
1469
        }
1470

    
1471
        private static void AddWithValue(DbCommand cmd, string PropName, object Value)
1472
        {
1473
            var param = cmd.CreateParameter();
1474
            param.ParameterName = PropName;
1475
            param.Value = Value;
1476
            cmd.Parameters.Add(param);
1477
        }
1478

    
1479
        public static DataTable SelectRevisionTable()
1480
        {
1481
            DataTable dt = null;
1482
            ID2Info id2Info = ID2Info.GetInstance();
1483

    
1484
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1485
            {
1486
                try
1487
                {
1488
                    var query = $"SELECT T.name AS table_name, C.name AS column_name FROM ARS_COMMON.sys.tables AS T INNER JOIN ARS_COMMON.sys.columns AS C ON T.object_id = C.object_id WHERE T.name = 'PSNREVISION'";
1489
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1490
                    {
1491
                        dt = ds.Tables[0].Copy();
1492
                    }
1493
                }
1494
                catch (Exception ex)
1495
                {
1496
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1497
                }
1498
            }
1499

    
1500
            return dt;
1501
        }
1502

    
1503
        public static DataTable SelectRevision()
1504
        {
1505
            DataTable dt = null;
1506
            ID2Info id2Info = ID2Info.GetInstance();
1507

    
1508
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1509
            {
1510
                try
1511
                {
1512
                    var query = $"SELECT * FROM ARS_COMMON.dbo.PSNREVISION";
1513

    
1514
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
1515
                    {
1516
                        dt = ds.Tables[0].Copy();
1517
                    }
1518
                }
1519
                catch (Exception ex)
1520
                {
1521
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1522
                }
1523
            }
1524

    
1525
            return dt;
1526
        }
1527

    
1528
        public static bool ConnCreateRevisionTable(Dictionary<string, string> dicColCheck, List<string> names)
1529
        {
1530
            bool result = false;
1531
            ID2Info id2Info = ID2Info.GetInstance();
1532

    
1533
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1534
            {
1535
                try
1536
                {
1537
                    if (names.Count == 0)
1538
                    {
1539

    
1540
                        var query = $"CREATE TABLE ARS_COMMON.dbo.PSNRevision ([OID] [uniqueidentifier] ,[Project_OID] [uniqueidentifier] ," +
1541
                            $"[ProjectCode] nvarchar(255), [RevNumber] int ,[UserName] nvarchar(255) ,[TimeData] nvarchar(255) ," +
1542
                            $"[PSNDatabasePath] nvarchar(255) ,	[PsnByPBSFilter] varchar(20) ,[PsnByNPDFilter] varchar(20) ,[PidDrawings] int ,	" +
1543
                            $"[PipeSystems] int ,[PipeLines] int ,[Topologies] int ,[PipeSystemNetworks] int ,[InValidPSNs] int ,[E2E] int ," +
1544
                            $"[E2B] int ,[B2E] int ,[HDE] int ,[HD2] int ,[HDB] int ,[B2B] int, [LastModificationdate] nvarchar(255), [ID2_PSN] nvarchar(10))";
1545
                        using (var cmd = connection.GetSqlStringCommand(query))
1546
                        {
1547
                            cmd.ExecuteNonQuery();
1548
                        }
1549
                    }
1550
                    else
1551
                    {
1552
                        AddColumn(PSN_REVISION, dicColCheck, names);
1553
                    }
1554

    
1555
                    void AddColumn(string TableName, Dictionary<string, string> dicCol, List<string> colnames)
1556
                    {
1557
                        bool check = false;
1558
                        if (colnames != null)
1559
                        {
1560
                            foreach (KeyValuePair<string, string> col in dicCol)
1561
                            {
1562
                                check = false;
1563
                                foreach (string c in colnames)
1564
                                {
1565
                                    if (col.Key.Contains(c))
1566
                                    {
1567
                                        check = true;
1568
                                        break;
1569
                                    }
1570
                                }
1571

    
1572
                                if (!check) //없으면 추가
1573
                                {
1574
                                    string i = string.Empty;
1575
                                    if (col.Value.ToString().ToUpper().Contains("INT") || col.Value.ToString().ToUpper().Contains("REAL"))
1576
                                        i = "DEFAULT 0";
1577

    
1578
                                    var queryalter = $"ALTER TABLE ARS_COMMON.dbo.PSNRevision ADD [" + col.Key + "] " + col.Value + " " + i + ";";
1579
                                    using (var cmd = connection.GetSqlStringCommand(queryalter))
1580
                                    {
1581
                                        cmd.ExecuteNonQuery();
1582
                                    }
1583
                                }
1584
                            }
1585
                        }
1586
                    }
1587

    
1588
                    result = true;
1589
                }
1590
                catch (Exception ex)
1591
                {
1592
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
1593
                }
1594
            }
1595

    
1596
            return result;
1597
        }
1598

    
1599
        public static bool UpdateRevision(string TimeData, string ProjectCode, int RevNumber, string UserName, string PSNDatabasePath, string PsnByPBSFilter, string PsnByNPDFilter,
1600
           int PidDrawings, int PipeSystems, int PipeLines, int Topologies, int PipeSystemNetworks, int InValidPSNs, int E2E, int E2B, int B2E, int HDE, int HD2, int HDB, int B2B)
1601
        {
1602
            ID2Info id2Info = ID2Info.GetInstance();
1603
            bool result = true;
1604

    
1605
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1606
            {
1607
                try
1608
                {
1609
                    using (var txn = connection.BeginTransaction())
1610
                    {
1611
                        try
1612
                        {
1613
                            string where = string.Format("DELETE FROM ARS_COMMON.dbo.PSNRevision WHERE ProjectCode = '{0}' AND PSNDatabasePath = '{1}' AND RevNumber = {2}", ProjectCode, PSNDatabasePath, RevNumber);
1614
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(where), txn);
1615
                            var query = $"INSERT INTO ARS_COMMON.dbo.PSNRevision (OID, Project_OID, ProjectCode, RevNumber, UserName, TimeData, PSNDatabasePath, " +
1616
                                $"PsnByPBSFilter, PsnByNPDFilter, PidDrawings, PipeSystems, PipeLines, Topologies, PipeSystemNetworks, InValidPSNs, E2E, E2B, B2E, HDE, " +
1617
                                $"HD2, HDB, B2B, LastModificationdate, ID2_PSN) VALUES (NEWID(), NEWID(), @ProjectCode, @RevNumber, @UserName, @TimeData, @PSNDatabasePath" +
1618
                                $", @PsnByPBSFilter, @PsnByNPDFilter, @PidDrawings, @PipeSystems, @PipeLines, @Topologies, @PipeSystemNetworks, @InValidPSNs, " +
1619
                                $"@E2E, @E2B, @B2E, @HDE, @HD2, @HDB, @B2B, @LastModificationdate, @ID2_PSN)";
1620
                            var cmd = connection.GetSqlStringCommand(query);
1621

    
1622
                            AddWithValue(cmd, "@ProjectCode", ProjectCode);
1623
                            AddWithValue(cmd, "@RevNumber", RevNumber);
1624
                            AddWithValue(cmd, "@UserName", UserName);
1625
                            AddWithValue(cmd, "@TimeData", TimeData); //String.Format("{0:G}", dt) // DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
1626

    
1627
                            AddWithValue(cmd, "@PSNDatabasePath", PSNDatabasePath);
1628

    
1629
                            AddWithValue(cmd, "@PsnByPBSFilter", PsnByPBSFilter);
1630
                            AddWithValue(cmd, "@PsnByNPDFilter", PsnByNPDFilter);
1631
                            AddWithValue(cmd, "@PidDrawings", PidDrawings);
1632
                            AddWithValue(cmd, "@PipeSystems", PipeSystems);
1633
                            AddWithValue(cmd, "@PipeLines", PipeLines);
1634

    
1635
                            AddWithValue(cmd, "@Topologies", Topologies);
1636
                            AddWithValue(cmd, "@PipeSystemNetworks", PipeSystemNetworks);
1637
                            AddWithValue(cmd, "@InValidPSNs", InValidPSNs);
1638
                            AddWithValue(cmd, "@E2E", E2E);
1639
                            AddWithValue(cmd, "@E2B", E2B);
1640
                            AddWithValue(cmd, "@B2E", B2E);
1641
                            AddWithValue(cmd, "@HDE", HDE);
1642
                            AddWithValue(cmd, "@HD2", HD2);
1643
                            AddWithValue(cmd, "@HDB", HDB);
1644
                            AddWithValue(cmd, "@B2B", B2B);
1645
                            System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
1646
                            DateTime oDateTime = DateTime.Now;
1647
                            AddWithValue(cmd, "@LastModificationdate", oDateTime.ToString(ci));
1648
                            AddWithValue(cmd, "@ID2_PSN", "Y");
1649
                            connection.ExecuteNonQuery(cmd, txn);
1650

    
1651
                            txn.Commit();
1652
                        }
1653
                        catch (Exception ex)
1654
                        {
1655
                            txn.Rollback();
1656
                            result = false;
1657
                        }
1658
                    }
1659
                }
1660
                catch (Exception ex)
1661
                {
1662
                    System.Windows.Forms.MessageBox.Show(ex.Message);
1663
                    result = false;
1664
                }
1665
            }
1666

    
1667
            return result;
1668
        }
1669

    
1670

    
1671
        public static bool SaveRevision(string ProjectCode, int RevNumber, string UserName, string PSNDatabasePath, string PsnByPBSFilter, string PsnByNPDFilter,
1672
            int PidDrawings, int PipeSystems, int PipeLines, int Topologies, int PipeSystemNetworks, int InValidPSNs, int E2E, int E2B, int B2E, int HDE, int HD2, int HDB, int B2B)
1673
        {
1674
            ID2Info id2Info = ID2Info.GetInstance();
1675

    
1676
            bool result = true;
1677

    
1678
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1679
            {
1680
                try
1681
                {
1682
                    using (var txn = connection.BeginTransaction())
1683
                    {
1684
                        try
1685
                        {
1686

    
1687
                            var query = $"INSERT INTO ARS_COMMON.dbo.PSNRevision (OID, Project_OID, ProjectCode, RevNumber, UserName, TimeData, PSNDatabasePath, " +
1688
                                $"PsnByPBSFilter, PsnByNPDFilter, PidDrawings, PipeSystems, PipeLines, Topologies, PipeSystemNetworks, InValidPSNs, E2E, E2B, B2E, HDE, " +
1689
                                $"HD2, HDB, B2B, LastModificationdate, ID2_PSN) VALUES (NEWID(), NEWID(), @ProjectCode, @RevNumber, @UserName, @TimeData, @PSNDatabasePath" +
1690
                                $", @PsnByPBSFilter, @PsnByNPDFilter, @PidDrawings, @PipeSystems, @PipeLines, @Topologies, @PipeSystemNetworks, @InValidPSNs, " +
1691
                                $"@E2E, @E2B, @B2E, @HDE, @HD2, @HDB, @B2B, @LastModificationdate, @ID2_PSN)";
1692
                            var cmd = connection.GetSqlStringCommand(query);
1693
                            AddWithValue(cmd, "@OID", Guid.NewGuid().ToString());
1694
                            AddWithValue(cmd, "@Project_OID", Guid.NewGuid().ToString());
1695
                            AddWithValue(cmd, "@ProjectCode", ProjectCode);
1696
                            AddWithValue(cmd, "@RevNumber", RevNumber);
1697
                            AddWithValue(cmd, "@UserName", UserName);
1698

    
1699
                            System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
1700
                            DateTime oDateTime = DateTime.Now;
1701

    
1702
                            AddWithValue(cmd, "@TimeData", oDateTime.ToString(ci)); //String.Format("{0:G}", dt) // DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
1703

    
1704
                            AddWithValue(cmd, "@PSNDatabasePath", PSNDatabasePath);
1705

    
1706
                            AddWithValue(cmd, "@PsnByPBSFilter", PsnByPBSFilter);
1707
                            AddWithValue(cmd, "@PsnByNPDFilter", PsnByNPDFilter);
1708
                            AddWithValue(cmd, "@PidDrawings", PidDrawings);
1709
                            AddWithValue(cmd, "@PipeSystems", PipeSystems);
1710
                            AddWithValue(cmd, "@PipeLines", PipeLines);
1711

    
1712
                            AddWithValue(cmd, "@Topologies", Topologies);
1713
                            AddWithValue(cmd, "@PipeSystemNetworks", PipeSystemNetworks);
1714
                            AddWithValue(cmd, "@InValidPSNs", InValidPSNs);
1715
                            AddWithValue(cmd, "@E2E", E2E);
1716
                            AddWithValue(cmd, "@E2B", E2B);
1717
                            AddWithValue(cmd, "@B2E", B2E);
1718
                            AddWithValue(cmd, "@HDE", HDE);
1719
                            AddWithValue(cmd, "@HD2", HD2);
1720
                            AddWithValue(cmd, "@HDB", HDB);
1721
                            AddWithValue(cmd, "@B2B", B2B);
1722
                            // AddWithValue(cmd, "@LastModificationdate", oDateTime.ToString(ci));
1723
                            AddWithValue(cmd, "@LastModificationdate", "");
1724
                            AddWithValue(cmd, "@ID2_PSN", "Y");
1725

    
1726
                            connection.ExecuteNonQuery(cmd, txn);
1727

    
1728
                            txn.Commit();
1729
                        }
1730
                        catch (Exception ex)
1731
                        {
1732
                            txn.Rollback();
1733
                            result = false;
1734
                        }
1735
                    }
1736
                }
1737
                catch (Exception ex)
1738
                {
1739
                    System.Windows.Forms.MessageBox.Show(ex.Message);
1740
                    result = false;
1741
                }
1742
            }
1743

    
1744
            return result;
1745
        }
1746

    
1747
        public static bool SavePSNData(PSN item)
1748
        {
1749
            ID2Info id2Info = ID2Info.GetInstance();
1750

    
1751
            bool result = true;
1752

    
1753
            using (IAbstractDatabase connection = id2Info.CreateConnection())
1754
            {
1755
                try
1756
                {
1757
                    using (var txn = connection.BeginTransaction())
1758
                    {
1759
                        try
1760
                        {
1761
                            // Path Items
1762
                            var query = $"DELETE FROM {PSN_PATHITEMS}";
1763
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1764
                            for (int i = 0; i < item.PathItems.Rows.Count; i++)
1765
                            {
1766
                                DataRow row = item.PathItems.Rows[i];
1767
                                query = $"INSERT INTO {PSN_PATHITEMS} " +
1768
                                    $"(OID, SequenceData_OID, TopologySet_OID, BranchTopologySet_OID, PipeLine_OID, ItemName, ItemTag, DESCRIPTION, CLASS, SUBCLASS, TYPE, " +
1769
                                    $"PIDNAME, Equipment_OID, NPD, GROUPTAG, PipeSystemNetwork_OID, PipeRun_OID, ViewPipeSystemNetwork_OID, EqpGroupTag, MainLineTag, EGTConnectedPoint, EGFlowDirection) VALUES " +
1770
                                    $"(@OID, @SequenceData_OID, @TopologySet_OID, @BranchTopologySet_OID, @PipeLine_OID, @ITEMNAME, @ITEMTAG, @DESCRIPTION, @Class, @SubClass, @TYPE, @PIDNAME, " +
1771
                                    $"@Equipment_OID, @NPD, @GROUPTAG, @PipeSystemNetwork_OID,@PipeRun_OID, @ViewPipeSystemNetwork_OID, @EqpGroupTag , @MainLineTag, @EGTConnectedPoint, @EGFlowDirection)";
1772
                                var cmd = connection.GetSqlStringCommand(query);
1773
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
1774
                                AddWithValue(cmd, "@SequenceData_OID", string.IsNullOrEmpty(row["SequenceData_OID"].ToString()) ? "" : row["SequenceData_OID"].ToString());
1775
                                AddWithValue(cmd, "@TopologySet_OID", string.IsNullOrEmpty(row["TopologySet_OID"].ToString()) ? "" : row["TopologySet_OID"].ToString());
1776
                                AddWithValue(cmd, "@BranchTopologySet_OID", string.IsNullOrEmpty(row["BranchTopologySet_OID"].ToString()) ? "" : row["BranchTopologySet_OID"].ToString());
1777
                                AddWithValue(cmd, "@PipeLine_OID", string.IsNullOrEmpty(row["PipeLine_OID"].ToString()) ? "" : row["PipeLine_OID"].ToString());
1778
                                AddWithValue(cmd, "@ITEMNAME", string.IsNullOrEmpty(row["ITEMNAME"].ToString()) ? "" : row["ITEMNAME"].ToString());
1779
                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());
1780
                                AddWithValue(cmd, "@DESCRIPTION", string.IsNullOrEmpty(row["DESCRIPTION"].ToString()) ? "" : row["DESCRIPTION"].ToString());
1781
                                AddWithValue(cmd, "@Class", string.IsNullOrEmpty(row["Class"].ToString()) ? "" : row["Class"].ToString());
1782
                                AddWithValue(cmd, "@SubClass", string.IsNullOrEmpty(row["SubClass"].ToString()) ? "" : row["SubClass"].ToString());
1783
                                AddWithValue(cmd, "@TYPE", string.IsNullOrEmpty(row["TYPE"].ToString()) ? "" : row["TYPE"].ToString());
1784
                                AddWithValue(cmd, "@PIDNAME", string.IsNullOrEmpty(row["PIDNAME"].ToString()) ? "" : row["PIDNAME"].ToString());
1785
                                AddWithValue(cmd, "@Equipment_OID", string.IsNullOrEmpty(row["Equipment_OID"].ToString()) ? "" : row["Equipment_OID"].ToString());
1786
                                AddWithValue(cmd, "@NPD", string.IsNullOrEmpty(row["NPD"].ToString()) ? "" : row["NPD"].ToString());
1787
                                AddWithValue(cmd, "@GROUPTAG", string.IsNullOrEmpty(row["GROUPTAG"].ToString()) ? "" : row["GROUPTAG"].ToString());
1788
                                AddWithValue(cmd, "@PipeSystemNetwork_OID", string.IsNullOrEmpty(row["PipeSystemNetwork_OID"].ToString()) ? "" : row["PipeSystemNetwork_OID"].ToString());
1789
                                AddWithValue(cmd, "@ViewPipeSystemNetwork_OID", string.IsNullOrEmpty(row["ViewPipeSystemNetwork_OID"].ToString()) ? "" : row["ViewPipeSystemNetwork_OID"].ToString());
1790
                                AddWithValue(cmd, "@PipeRun_OID", string.IsNullOrEmpty(row["PipeRun_OID"].ToString()) ? "" : row["PipeRun_OID"].ToString());
1791
                                AddWithValue(cmd, "@EqpGroupTag", string.IsNullOrEmpty(row["EqpGroupTag"].ToString()) ? "" : row["EqpGroupTag"].ToString());
1792
                                AddWithValue(cmd, "@MainLineTag", string.IsNullOrEmpty(row["MainLineTag"].ToString()) ? "" : row["MainLineTag"].ToString());
1793

    
1794
                                AddWithValue(cmd, "@EGTConnectedPoint", string.IsNullOrEmpty(row["EGTConnectedPoint"].ToString()) ? "0" : row["EGTConnectedPoint"].ToString());
1795
                                AddWithValue(cmd, "@EGFlowDirection", string.IsNullOrEmpty(row["EGFlowDirection"].ToString()) ? "" : row["EGFlowDirection"].ToString());
1796
                                connection.ExecuteNonQuery(cmd, txn);
1797
                            }
1798

    
1799
                            // Sequence
1800
                            query = $"DELETE FROM {PSN_SEQUENCEDATA}";
1801
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1802
                            foreach (DataRow row in item.SequenceData.Rows)
1803
                            {
1804
                                query = $"INSERT INTO {PSN_SEQUENCEDATA} (OID, SERIALNUMBER, PathItem_OID, TopologySet_OID_Key) VALUES (@OID, @SERIALNUMBER, @PathItem_OID, @TopologySet_OID_Key)";
1805
                                var cmd = connection.GetSqlStringCommand(query);
1806
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
1807
                                AddWithValue(cmd, "@SERIALNUMBER", string.IsNullOrEmpty(row["SERIALNUMBER"].ToString()) ? "" : row["SERIALNUMBER"].ToString());
1808
                                AddWithValue(cmd, "@PathItem_OID", string.IsNullOrEmpty(row["PathItem_OID"].ToString()) ? "" : row["PathItem_OID"].ToString());
1809
                                AddWithValue(cmd, "@TopologySet_OID_Key", string.IsNullOrEmpty(row["TopologySet_OID_Key"].ToString()) ? "" : row["TopologySet_OID_Key"].ToString());
1810
                                connection.ExecuteNonQuery(cmd, txn);
1811
                            }
1812

    
1813
                            // Nozzle
1814
                            query = $"DELETE FROM {PSN_NOZZLE}";
1815
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1816
                            foreach (DataRow row in item.Nozzle.Rows)
1817
                            {
1818
                                query = $"INSERT INTO {PSN_NOZZLE} (OID, ItemTag, Xcoords, Ycoords, Equipment_OID, Fluid, NPD, PMC, Rotation, FlowDirection) VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS, @Equipment_OID, @FLUID, @NPD, @PMC, @ROTATION, @FlowDirection)";
1819
                                var cmd = connection.GetSqlStringCommand(query);
1820
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
1821
                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());
1822

    
1823
                                if (string.IsNullOrEmpty(row["XCOORDS"].ToString()))
1824
                                    AddWithValue(cmd, "@XCOORDS", DBNull.Value);
1825
                                else
1826
                                    AddWithValue(cmd, "@XCOORDS", row["XCOORDS"].ToString());
1827

    
1828
                                if (string.IsNullOrEmpty(row["YCOORDS"].ToString()))
1829
                                    AddWithValue(cmd, "@YCOORDS", DBNull.Value);
1830
                                else
1831
                                    AddWithValue(cmd, "@YCOORDS", row["YCOORDS"].ToString());
1832

    
1833
                                AddWithValue(cmd, "@Equipment_OID", string.IsNullOrEmpty(row["Equipment_OID"].ToString()) ? "" : row["Equipment_OID"].ToString());
1834
                                AddWithValue(cmd, "@FLUID", string.IsNullOrEmpty(row["FLUID"].ToString()) ? "" : row["FLUID"].ToString());
1835
                                AddWithValue(cmd, "@NPD", string.IsNullOrEmpty(row["NPD"].ToString()) ? "" : row["NPD"].ToString());
1836
                                AddWithValue(cmd, "@PMC", string.IsNullOrEmpty(row["PMC"].ToString()) ? "" : row["PMC"].ToString());
1837

    
1838
                                if (string.IsNullOrEmpty(row["ROTATION"].ToString()))
1839
                                    AddWithValue(cmd, "@ROTATION", DBNull.Value);
1840
                                else
1841
                                    AddWithValue(cmd, "@ROTATION", row["ROTATION"].ToString());
1842

    
1843
                                AddWithValue(cmd, "@FlowDirection", string.IsNullOrEmpty(row["FlowDirection"].ToString()) ? "" : row["FlowDirection"].ToString());
1844
                                connection.ExecuteNonQuery(cmd, txn);
1845
                            }
1846

    
1847
                            //Equipment
1848
                            query = $"DELETE FROM {PSN_EQUIPMENT}";
1849
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1850
                            foreach (DataRow row in item.Equipment.Rows)
1851
                            {
1852
                                query = $"INSERT INTO {PSN_EQUIPMENT} (OID, ItemTag, Xcoords, Ycoords) VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS)";
1853
                                var cmd = connection.GetSqlStringCommand(query);
1854
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
1855
                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());
1856

    
1857
                                if (string.IsNullOrEmpty(row["XCOORDS"].ToString()))
1858
                                    AddWithValue(cmd, "@XCOORDS", DBNull.Value);
1859
                                else
1860
                                    AddWithValue(cmd, "@XCOORDS", row["XCOORDS"].ToString());
1861

    
1862
                                if (string.IsNullOrEmpty(row["YCOORDS"].ToString()))
1863
                                    AddWithValue(cmd, "@YCOORDS", DBNull.Value);
1864
                                else
1865
                                    AddWithValue(cmd, "@YCOORDS", row["YCOORDS"].ToString());
1866

    
1867
                                connection.ExecuteNonQuery(cmd, txn);
1868
                            }
1869

    
1870
                            // TopologySet
1871
                            query = $"DELETE FROM {PSN_TOPOLOGYSET}";
1872
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1873
                            foreach (DataRow row in item.TopologySet.Rows)
1874
                            {
1875
                                query = $"INSERT INTO {PSN_TOPOLOGYSET} (OID, Type, SubType, HeadItemTag, TailItemTag, HeadItemSPID, TailItemSPID) VALUES (@OID, @Type, @SubType, @HeadItemTag, @TailItemTag, @HeadItemSPID, @TailItemSPID)";
1876
                                var cmd = connection.GetSqlStringCommand(query);
1877
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
1878
                                AddWithValue(cmd, "@Type", string.IsNullOrEmpty(row["Type"].ToString()) ? "" : row["Type"].ToString());
1879
                                AddWithValue(cmd, "@SubType", string.IsNullOrEmpty(row["SubType"].ToString()) ? "" : row["SubType"].ToString());
1880
                                AddWithValue(cmd, "@HeadItemTag", string.IsNullOrEmpty(row["HeadItemTag"].ToString()) ? "" : row["HeadItemTag"].ToString());
1881
                                AddWithValue(cmd, "@TailItemTag", string.IsNullOrEmpty(row["TailItemTag"].ToString()) ? "" : row["TailItemTag"].ToString());
1882
                                AddWithValue(cmd, "@HeadItemSPID", string.IsNullOrEmpty(row["HeadItemSPID"].ToString()) ? "" : row["HeadItemSPID"].ToString());
1883
                                AddWithValue(cmd, "@TailItemSPID", string.IsNullOrEmpty(row["TailItemSPID"].ToString()) ? "" : row["TailItemSPID"].ToString());
1884
                                connection.ExecuteNonQuery(cmd, txn);
1885
                            }
1886

    
1887
                            // PSN
1888
                            query = $"DELETE FROM {PSN_PIPESYSTEMNETWORK} WHERE PSNRevisionNumber = '{string.Format("V{0:D4}", item.Revision)}'";
1889
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1890
                            foreach (DataRow row in item.PipeSystemNetwork.Rows)
1891
                            {
1892
                                query = $"INSERT INTO {PSN_PIPESYSTEMNETWORK} " +
1893
                                    $"(OID, Type, OrderNumber, Pipeline_OID, FROM_DATA, TO_DATA, TopologySet_OID_Key, PSNRevisionNumber, PBS, Drawings, IsValid, Status, IncludingVirtualData, PSNAccuracy, Pocket, EGTag, HasMLTags) VALUES " +
1894
                                    $"(@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber,  @PBS, @Drawings, @IsValid, @Status, @IncludingVirtualData, @PSNAccuracy, @Pocket, @EGTag, @HasMLTags)";
1895
                                var cmd = connection.GetSqlStringCommand(query);
1896
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
1897
                                AddWithValue(cmd, "@Type", string.IsNullOrEmpty(row["Type"].ToString()) ? "" : row["Type"].ToString());
1898
                                AddWithValue(cmd, "@OrderNumber", string.IsNullOrEmpty(row["OrderNumber"].ToString()) ? "" : row["OrderNumber"].ToString());
1899
                                AddWithValue(cmd, "@Pipeline_OID", string.IsNullOrEmpty(row["Pipeline_OID"].ToString()) ? "" : row["Pipeline_OID"].ToString());
1900
                                AddWithValue(cmd, "@FROM_DATA", string.IsNullOrEmpty(row["FROM_DATA"].ToString()) ? "" : row["FROM_DATA"].ToString());
1901
                                AddWithValue(cmd, "@TO_DATA", string.IsNullOrEmpty(row["TO_DATA"].ToString()) ? "" : row["TO_DATA"].ToString());
1902
                                AddWithValue(cmd, "@TopologySet_OID_Key", string.IsNullOrEmpty(row["TopologySet_OID_Key"].ToString()) ? "" : row["TopologySet_OID_Key"].ToString());
1903
                                AddWithValue(cmd, "@PSNRevisionNumber", string.IsNullOrEmpty(row["PSNRevisionNumber"].ToString()) ? "" : row["PSNRevisionNumber"].ToString());
1904

    
1905
                                int IsValid = 0;
1906
                                if (row["IsValid"].ToString() == string.Empty || row["IsValid"].ToString() == "OK")
1907
                                    IsValid = 0;
1908
                                else if (row["IsValid"].ToString() == "InValid")
1909
                                    IsValid = 1;
1910
                                else if (row["IsValid"].ToString() == "Error")
1911
                                    IsValid = -1;
1912

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

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

    
1919
                                AddWithValue(cmd, "@IncludingVirtualData", string.IsNullOrEmpty(row["IncludingVirtualData"].ToString()) ? "" : row["IncludingVirtualData"].ToString());
1920
                                if (string.IsNullOrEmpty(row["PSNAccuracy"].ToString()))
1921
                                    AddWithValue(cmd, "@PSNAccuracy", DBNull.Value);
1922
                                else
1923
                                    AddWithValue(cmd, "@PSNAccuracy", row["PSNAccuracy"].ToString().Replace("%", ""));
1924

    
1925
                                AddWithValue(cmd, "@Pocket", string.IsNullOrEmpty(row["Pocket"].ToString()) ? "Yes" : row["Pocket"].ToString());
1926

    
1927
                                AddWithValue(cmd, "@EGTag", string.IsNullOrEmpty(row["EGTag"].ToString()) ? "" : row["EGTag"].ToString());
1928
                                AddWithValue(cmd, "@HasMLTags", string.IsNullOrEmpty(row["HasMLTags"].ToString()) ? "False" : row["HasMLTags"].ToString());
1929
                                connection.ExecuteNonQuery(cmd, txn);
1930
                            }
1931

    
1932
                            //Pipeline
1933
                            query = $"DELETE FROM {PSN_PIPELINE}";
1934
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1935
                            foreach (DataRow row in item.PipeLine.Rows)
1936
                            {
1937
                                query = $"INSERT INTO {PSN_PIPELINE} (OID, PipeSystem_OID, FLUID, PMC, SEQNUMBER, INSULATION, FROM_DATA, TO_DATA, Unit) VALUES (@OID, @PipeSystem_OID, @FLUID, @PMC, @SEQNUMBER, @INSULATION, @FROM_DATA, @TO_DATA, @Unit)";
1938
                                var cmd = connection.GetSqlStringCommand(query);
1939
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
1940
                                AddWithValue(cmd, "@PipeSystem_OID", row["PipeSystem_OID"].ToString());
1941
                                AddWithValue(cmd, "@FLUID", row["FLUID"].ToString());
1942
                                AddWithValue(cmd, "@PMC", row["PMC"].ToString());
1943
                                AddWithValue(cmd, "@SEQNUMBER", row["SEQNUMBER"].ToString());
1944
                                AddWithValue(cmd, "@INSULATION", row["INSULATION"].ToString());
1945
                                AddWithValue(cmd, "@FROM_DATA", row["FROM_DATA"].ToString());
1946
                                AddWithValue(cmd, "@TO_DATA", row["TO_DATA"].ToString());
1947
                                AddWithValue(cmd, "@Unit", row["Unit"].ToString());
1948
                                connection.ExecuteNonQuery(cmd, txn);
1949
                            }
1950

    
1951
                            //PipeSystem
1952
                            query = $"DELETE FROM {PSN_PIPESYSTEM}";
1953
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1954
                            foreach (DataRow row in item.PipeSystem.Rows)
1955
                            {
1956
                                query = $"INSERT INTO {PSN_PIPESYSTEM} (OID, DESCRIPTION, FLUID, PMC, PipeLineQty, GroundLevel) VALUES (@OID, @DESCRIPTION, @FLUID, @PMC, @PipeLineQty, @GroundLevel)";
1957
                                var cmd = connection.GetSqlStringCommand(query);
1958
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
1959
                                AddWithValue(cmd, "@DESCRIPTION", row["DESCRIPTION"].ToString());
1960
                                AddWithValue(cmd, "@FLUID", row["FLUID"].ToString());
1961
                                AddWithValue(cmd, "@PMC", row["PMC"].ToString());
1962
                                AddWithValue(cmd, "@PipeLineQty", row["PipeLineQty"].ToString());
1963
                                AddWithValue(cmd, "@GroundLevel", row["GroundLevel"].ToString());
1964
                                connection.ExecuteNonQuery(cmd, txn);
1965
                            }
1966

    
1967
                            if (id2Info.ID2DBType == ID2DB_Type.MSSQL)
1968
                            {
1969
                                query = $"If(db_id(N'" + PSN_COMMON + "') IS NULL) CREATE DATABASE [" + PSN_COMMON + "]";
1970
                                connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
1971
                            }
1972

    
1973

    
1974
                            txn.Commit();
1975
                        }
1976
                        catch (Exception ex)
1977
                        {
1978
                            txn.Rollback();
1979
                            result = false;
1980
                        }
1981

    
1982
                    }
1983
                    void AddColumn(string TableName, Dictionary<string, string> dicCol, List<string> colnames)
1984
                    {
1985
                        bool check = false;
1986
                        if (colnames != null)
1987
                        {
1988
                            foreach (KeyValuePair<string, string> col in dicCol)
1989
                            {
1990
                                check = false;
1991
                                foreach (string c in colnames)
1992
                                {
1993
                                    if (col.Key.Contains(c))
1994
                                    {
1995
                                        check = true;
1996
                                        break;
1997
                                    }
1998
                                }
1999

    
2000
                                if (!check) //없으면 추가
2001
                                {
2002
                                    string i = string.Empty;
2003
                                    if (col.Value.ToString().ToUpper().Contains("INT") || col.Value.ToString().ToUpper().Contains("REAL"))
2004
                                        i = "DEFAULT 0";
2005

    
2006
                                    var queryalter = $"ALTER TABLE " + TableName + " ADD [" + col.Key + "] " + col.Value + " " + i + ";";
2007
                                    using (var cmd = connection.GetSqlStringCommand(queryalter))
2008
                                    {
2009
                                        cmd.ExecuteNonQuery();
2010
                                    }
2011
                                }
2012
                            }
2013
                        }
2014
                    }
2015
                }
2016
                catch (Exception ex)
2017
                {
2018
                    System.Windows.Forms.MessageBox.Show(ex.Message);
2019
                    result = false;
2020
                }
2021
            }
2022

    
2023
            return result;
2024
        }
2025

    
2026
        public static bool SavePSNFluidCode(DataTable dt)
2027
        {
2028
            ID2Info id2Info = ID2Info.GetInstance();
2029

    
2030
            bool result = true;
2031
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2032
            {
2033
                try
2034
                {
2035
                    using (var txn = connection.BeginTransaction())
2036
                    {
2037
                        try
2038
                        {
2039
                            var query = $"DELETE FROM {PSN_FLUIDCODE}";
2040
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
2041

    
2042
                            foreach (DataRow row in dt.Rows)
2043
                            {
2044
                                query = $"INSERT INTO {PSN_FLUIDCODE} (UID, Code, Description, Condition, Remarks, GroundLevel) VALUES (@UID, @Code, @Description, @Condition, @Remarks, @GroundLevel)";
2045
                                var cmd = connection.GetSqlStringCommand(query);
2046
                                cmd.Parameters.Clear();
2047

    
2048
                                {
2049
                                    var param = cmd.CreateParameter();
2050
                                    param.ParameterName = "@UID";
2051
                                    param.Value = row["UID"].ToString();
2052
                                    cmd.Parameters.Add(param);
2053
                                }
2054

    
2055
                                {
2056
                                    var param = cmd.CreateParameter();
2057
                                    param.ParameterName = "@Code";
2058
                                    param.Value = row["Code"].ToString();
2059
                                    cmd.Parameters.Add(param);
2060
                                }
2061

    
2062
                                {
2063
                                    var param = cmd.CreateParameter();
2064
                                    param.ParameterName = "@Description";
2065
                                    param.Value = row["Description"].ToString();
2066
                                    cmd.Parameters.Add(param);
2067
                                }
2068

    
2069
                                {
2070
                                    var param = cmd.CreateParameter();
2071
                                    param.ParameterName = "@Condition";
2072
                                    param.Value = row["Condition"].ToString();
2073
                                    cmd.Parameters.Add(param);
2074
                                }
2075

    
2076
                                {
2077
                                    var param = cmd.CreateParameter();
2078
                                    param.ParameterName = "@Remarks";
2079
                                    param.Value = row["Remarks"].ToString();
2080
                                    cmd.Parameters.Add(param);
2081
                                }
2082

    
2083
                                {
2084
                                    var param = cmd.CreateParameter();
2085
                                    param.ParameterName = "@GroundLevel";
2086
                                    param.Value = row["GroundLevel"].ToString();
2087
                                    cmd.Parameters.Add(param);
2088
                                }
2089

    
2090
                                connection.ExecuteNonQuery(cmd, txn);
2091
                            }
2092
                            txn.Commit();
2093
                        }
2094
                        catch (Exception ex)
2095
                        {
2096
                            txn.Rollback();
2097
                            result = false;
2098
                        }
2099
                    }
2100
                }
2101
                catch (Exception ex)
2102
                {
2103
                    System.Windows.Forms.MessageBox.Show(ex.Message);
2104
                    result = false;
2105
                }
2106
            }
2107

    
2108
            return result;
2109
        }
2110

    
2111
        public static DataTable SelectPSNFluidCode()
2112
        {
2113
            DataTable dt = null;
2114
            ID2Info id2Info = ID2Info.GetInstance();
2115

    
2116
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2117
            {
2118
                try
2119
                {
2120
                    var query = $"SELECT * FROM {PSN_FLUIDCODE}";
2121
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2122
                    {
2123
                        dt = ds.Tables[0].Copy();
2124
                    }
2125
                }
2126
                catch (Exception ex)
2127
                {
2128
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
2129
                }
2130
            }
2131

    
2132
            return dt;
2133
        }
2134

    
2135
        public static bool SavePSNPMC(DataTable dt)
2136
        {
2137
            ID2Info id2Info = ID2Info.GetInstance();
2138

    
2139
            bool result = true;
2140
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2141
            {
2142
                try
2143
                {
2144
                    using (var txn = connection.BeginTransaction())
2145
                    {
2146
                        try
2147
                        {
2148
                            var query = $"DELETE FROM {PSN_PIPINGMATLCLASS}";
2149
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
2150

    
2151
                            foreach (DataRow row in dt.Rows)
2152
                            {
2153
                                query = $"INSERT INTO {PSN_PIPINGMATLCLASS} ( UID, Priority, Code, Description, Condition, Remarks, GroundLevel) VALUES (@UID, @Priority, @Code, @Description, @Condition, @Remarks, @GroundLevel)";
2154
                                var cmd = connection.GetSqlStringCommand(query);
2155
                                cmd.Parameters.Clear();
2156

    
2157
                                {
2158
                                    var param = cmd.CreateParameter();
2159
                                    param.ParameterName = "@UID";
2160
                                    param.Value = row["UID"].ToString();
2161
                                    cmd.Parameters.Add(param);
2162
                                }
2163

    
2164
                                {
2165
                                    var param = cmd.CreateParameter();
2166
                                    param.ParameterName = "@Priority";
2167
                                    param.Value = row["Priority"].ToString();
2168
                                    cmd.Parameters.Add(param);
2169
                                }
2170

    
2171
                                {
2172
                                    var param = cmd.CreateParameter();
2173
                                    param.ParameterName = "@Code";
2174
                                    param.Value = row["Code"].ToString();
2175
                                    cmd.Parameters.Add(param);
2176
                                }
2177

    
2178
                                {
2179
                                    var param = cmd.CreateParameter();
2180
                                    param.ParameterName = "@Description";
2181
                                    param.Value = row["Description"].ToString();
2182
                                    cmd.Parameters.Add(param);
2183
                                }
2184

    
2185
                                {
2186
                                    var param = cmd.CreateParameter();
2187
                                    param.ParameterName = "@Condition";
2188
                                    param.Value = row["Condition"].ToString();
2189
                                    cmd.Parameters.Add(param);
2190
                                }
2191

    
2192
                                {
2193
                                    var param = cmd.CreateParameter();
2194
                                    param.ParameterName = "@Remarks";
2195
                                    param.Value = row["Remarks"].ToString();
2196
                                    cmd.Parameters.Add(param);
2197
                                }
2198

    
2199
                                {
2200
                                    var param = cmd.CreateParameter();
2201
                                    param.ParameterName = "@GroundLevel";
2202
                                    param.Value = row["GroundLevel"].ToString();
2203
                                    cmd.Parameters.Add(param);
2204
                                }
2205

    
2206
                                connection.ExecuteNonQuery(cmd, txn);
2207
                            }
2208

    
2209
                            txn.Commit();
2210
                        }
2211
                        catch (Exception ex)
2212
                        {
2213
                            txn.Rollback();
2214
                            result = false;
2215
                        }
2216
                    }
2217
                }
2218
                catch (Exception ex)
2219
                {
2220
                    System.Windows.Forms.MessageBox.Show(ex.Message);
2221
                    result = false;
2222
                }
2223
            }
2224

    
2225
            return result;
2226
        }
2227

    
2228
        public static bool SavePSNInsulation(DataTable dt)
2229
        {
2230
            ID2Info id2Info = ID2Info.GetInstance();
2231

    
2232
            bool result = true;
2233
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2234
            {
2235
                try
2236
                {
2237
                    using (var txn = connection.BeginTransaction())
2238
                    {
2239
                        try
2240
                        {
2241
                            var query = $"DELETE FROM {PSN_INSULATIONPURPOSE}";
2242
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
2243

    
2244
                            foreach (DataRow row in dt.Rows)
2245
                            {
2246
                                query = $"INSERT INTO {PSN_INSULATIONPURPOSE} (UID, Code, Description, Remarks) VALUES (@UID, @Code, @Description, @Remarks)";
2247
                                var cmd = connection.GetSqlStringCommand(query);
2248
                                cmd.Parameters.Clear();
2249

    
2250
                                {
2251
                                    var param = cmd.CreateParameter();
2252
                                    param.ParameterName = "@UID";
2253
                                    param.Value = row["UID"].ToString();
2254
                                    cmd.Parameters.Add(param);
2255
                                }
2256

    
2257
                                {
2258
                                    var param = cmd.CreateParameter();
2259
                                    param.ParameterName = "@Code";
2260
                                    param.Value = row["Code"].ToString();
2261
                                    cmd.Parameters.Add(param);
2262
                                }
2263

    
2264
                                {
2265
                                    var param = cmd.CreateParameter();
2266
                                    param.ParameterName = "@Description";
2267
                                    param.Value = row["Description"].ToString();
2268
                                    cmd.Parameters.Add(param);
2269
                                }
2270

    
2271
                                {
2272
                                    var param = cmd.CreateParameter();
2273
                                    param.ParameterName = "@Remarks";
2274
                                    param.Value = row["Remarks"].ToString();
2275
                                    cmd.Parameters.Add(param);
2276
                                }
2277

    
2278
                                connection.ExecuteNonQuery(cmd, txn);
2279
                            }
2280

    
2281
                            txn.Commit();
2282
                        }
2283
                        catch (Exception ex)
2284
                        {
2285
                            txn.Rollback();
2286
                            result = false;
2287
                        }
2288
                    }
2289
                }
2290
                catch (Exception ex)
2291
                {
2292
                    System.Windows.Forms.MessageBox.Show(ex.Message);
2293
                    result = false;
2294
                }
2295
            }
2296

    
2297
            return result;
2298
        }
2299

    
2300
        public static PSN GetDBPSN()
2301
        {
2302
            PSN result = new PSN();
2303
            ID2Info id2Info = ID2Info.GetInstance();
2304

    
2305
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2306
            {
2307
                try
2308
                {
2309
                    //query = $"SELECT * FROM {PSN_PIPESYSTEMNETWORK}";
2310
                    //using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2311
                    //{
2312
                    //    result.PipeSystemNetwork = ds.Tables[0].Copy();
2313
                    //}
2314

    
2315
                    var query = $"SELECT * FROM {PSN_PIPESYSTEMNETWORK} WHERE PSNRevisionNumber = '{string.Format("V{0:D4}", GetRevision())}'";
2316
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2317
                    {
2318
                        result.PipeSystemNetwork = ds.Tables[0].Clone();
2319
                        result.PipeSystemNetwork.Columns["IsValid"].DataType = typeof(string);
2320

    
2321
                        foreach (DataRow row in ds.Tables[0].Rows)
2322
                        {
2323
                            DataRow newRow = result.PipeSystemNetwork.NewRow();
2324
                            newRow["OID"] = row["OID"].ToString();
2325
                            newRow["Type"] = row["Type"].ToString();
2326
                            newRow["OrderNumber"] = row["OrderNumber"].ToString();
2327
                            newRow["Pipeline_OID"] = row["Pipeline_OID"].ToString();
2328
                            newRow["FROM_DATA"] = row["FROM_DATA"].ToString();
2329
                            newRow["TO_DATA"] = row["TO_DATA"].ToString();
2330
                            newRow["TopologySet_OID_Key"] = row["TopologySet_OID_Key"].ToString();
2331
                            newRow["PSNRevisionNumber"] = row["PSNRevisionNumber"].ToString();
2332

    
2333
                            string IsValid = string.Empty;
2334

    
2335
                            if (Convert.ToInt32(row["IsValid"].ToString()) == 0)
2336
                                IsValid = string.Empty;//"OK";
2337
                            else if (Convert.ToInt32(row["IsValid"].ToString()) == 1)
2338
                                IsValid = "InValid";
2339
                            else if (Convert.ToInt32(row["IsValid"].ToString()) == -1)
2340
                                IsValid = "Error";
2341

    
2342
                            newRow["IsValid"] = IsValid;
2343

    
2344
                            newRow["Status"] = row["Status"].ToString();
2345
                            newRow["PBS"] = row["PBS"].ToString();
2346
                            newRow["Drawings"] = row["Drawings"].ToString();
2347

    
2348
                            newRow["IncludingVirtualData"] = row["IncludingVirtualData"].ToString();
2349
                            newRow["PSNAccuracy"] = row["PSNAccuracy"].ToString();
2350

    
2351
                            newRow["Pocket"] = row["Pocket"].ToString();
2352

    
2353
                            newRow["EGTag"] = row["EGTag"].ToString();
2354
                            newRow["HasMLTags"] = row["HasMLTags"].ToString();
2355
                            result.PipeSystemNetwork.Rows.Add(newRow);
2356
                        }
2357
                    }
2358

    
2359
                    query = $"SELECT * FROM {PSN_EQUIPMENT}";
2360
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2361
                    {
2362
                        result.Equipment = ds.Tables[0].Copy();
2363
                    }
2364

    
2365
                    query = $"SELECT * FROM {PSN_NOZZLE}";
2366
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2367
                    {
2368
                        result.Nozzle = ds.Tables[0].Copy();
2369
                    }
2370

    
2371
                    query = $"SELECT * FROM {PSN_PATHITEMS}";
2372
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2373
                    {
2374
                        result.PathItems = ds.Tables[0].Copy();
2375
                    }
2376

    
2377
                    query = $"SELECT * FROM {PSN_SEQUENCEDATA}";
2378
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2379
                    {
2380
                        result.SequenceData = ds.Tables[0].Copy();
2381
                    }
2382

    
2383
                    query = $"SELECT * FROM {PSN_TOPOLOGYSET}";
2384
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2385
                    {
2386
                        result.TopologySet = ds.Tables[0].Copy();
2387
                    }
2388

    
2389
                    query = $"SELECT * FROM {PSN_PIPELINE}";
2390
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2391
                    {
2392
                        result.PipeLine = ds.Tables[0].Copy();
2393
                    }
2394

    
2395
                    query = $"SELECT * FROM {PSN_PIPESYSTEM}";
2396
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2397
                    {
2398
                        result.PipeSystem = ds.Tables[0].Copy();
2399
                    }
2400

    
2401
                    result.Revision = GetRevision();
2402
                }
2403
                catch (Exception ex)
2404
                {
2405
                    System.Windows.Forms.MessageBox.Show(ex.Message);
2406
                    result = null;
2407
                }
2408
            }
2409

    
2410
            return result;
2411
        }
2412

    
2413
        public static int GetRevision()
2414
        {
2415
            int result = 0;
2416
            ID2Info id2Info = ID2Info.GetInstance();
2417

    
2418
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2419
            {
2420
                try
2421
                {
2422
                    var query = $"SELECT DISTINCT PSNRevisionNumber FROM {PSN_PIPESYSTEMNETWORK};";
2423
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2424
                    {
2425
                        foreach (DataRow row in ds.Tables[0].Rows)
2426
                        {
2427
                            string value = row["PSNRevisionNumber"].ToString();
2428
                            if (value.StartsWith("V"))
2429
                                value = value.Remove(0, 1);
2430
                            int revisionNumber = Convert.ToInt32(value);
2431
                            if (result < revisionNumber)
2432
                                result = revisionNumber;
2433
                        }
2434
                    }
2435
                }
2436
                catch (Exception ex)
2437
                {
2438
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
2439
                    result = -1;
2440
                }
2441
            }
2442

    
2443
            return result;
2444
        }
2445

    
2446
        public static DataTable GetPathItem()
2447
        {
2448
            DataTable dt = null;
2449

    
2450
            ID2Info id2Info = ID2Info.GetInstance();
2451
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2452
            {
2453
                try
2454
                {
2455
                    var query = $"SELECT * FROM {PSN_PATHITEMS}";
2456
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2457
                    {
2458
                        dt = ds.Tables[0].Copy();
2459
                    }
2460
                }
2461
                catch (Exception ex)
2462
                {
2463
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
2464
                }
2465
            }
2466

    
2467
            return dt;
2468
        }
2469

    
2470
        public static DataTable GetTopologySet()
2471
        {
2472
            DataTable dt = null;
2473

    
2474
            ID2Info id2Info = ID2Info.GetInstance();
2475
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2476
            {
2477
                try
2478
                {
2479
                    var query = $"SELECT * FROM {PSN_TOPOLOGYSET}";
2480
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2481
                    {
2482
                        dt = ds.Tables[0].Clone();
2483
                        foreach (DataRow row in ds.Tables[0].Rows)
2484
                        {
2485
                            DataRow newRow = dt.NewRow();
2486
                            newRow["OID"] = row["OID"].ToString();
2487
                            newRow["Type"] = row["Type"].ToString();
2488
                            newRow["SubType"] = row["SubType"].ToString();
2489
                            newRow["HeadItemTag"] = row["HeadItemTag"].ToString();
2490
                            newRow["TailItemTag"] = row["TailItemTag"].ToString();
2491
                            newRow["HeadItemSPID"] = row["HeadItemSPID"].ToString();
2492
                            newRow["TailItemSPID"] = row["TailItemSPID"].ToString();
2493
                            dt.Rows.Add(newRow);
2494
                        }
2495
                    }
2496
                }
2497
                catch (Exception ex)
2498
                {
2499
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
2500
                }
2501
            }
2502

    
2503
            return dt;
2504
        }
2505

    
2506
        public static DataTable GetPipeSystemNetwork()
2507
        {
2508
            DataTable dt = null;
2509

    
2510
            ID2Info id2Info = ID2Info.GetInstance();
2511
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2512
            {
2513
                try
2514
                {
2515
                    var query = $"SELECT * FROM {PSN_PIPESYSTEMNETWORK} WHERE PSNRevisionNumber = '{string.Format("V{0:D4}", GetRevision())}' ORDER BY OID, OrderNumber";
2516
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2517
                    {
2518
                        dt = ds.Tables[0].Clone();
2519
                        dt.Columns["IsValid"].DataType = typeof(string);
2520
                        foreach (DataRow row in ds.Tables[0].Rows)
2521
                        {
2522
                            DataRow newRow = dt.NewRow();
2523
                            newRow["OID"] = row["OID"].ToString();
2524
                            newRow["Type"] = row["Type"].ToString();
2525
                            newRow["OrderNumber"] = row["OrderNumber"].ToString();
2526
                            newRow["Pipeline_OID"] = row["Pipeline_OID"].ToString();
2527
                            newRow["FROM_DATA"] = row["FROM_DATA"].ToString();
2528
                            newRow["TO_DATA"] = row["TO_DATA"].ToString();
2529
                            newRow["TopologySet_OID_Key"] = row["TopologySet_OID_Key"].ToString();
2530
                            newRow["PSNRevisionNumber"] = row["PSNRevisionNumber"].ToString();
2531

    
2532
                            string IsValid = string.Empty;
2533

    
2534
                            if (Convert.ToInt32(row["IsValid"].ToString()) == 0)
2535
                                IsValid = string.Empty;//"OK";
2536
                            else if (Convert.ToInt32(row["IsValid"].ToString()) == 1)
2537
                                IsValid = "InValid";
2538
                            else if (Convert.ToInt32(row["IsValid"].ToString()) == -1)
2539
                                IsValid = "Error";
2540

    
2541
                            newRow["IsValid"] = IsValid;
2542
                            newRow["Status"] = row["Status"].ToString();
2543

    
2544
                            newRow["PBS"] = row["PBS"].ToString();
2545
                            newRow["Drawings"] = row["Drawings"].ToString();
2546

    
2547
                            newRow["IncludingVirtualData"] = row["IncludingVirtualData"].ToString();
2548
                            newRow["PSNAccuracy"] = row["PSNAccuracy"].ToString();
2549

    
2550
                            newRow["Pocket"] = row["Pocket"].ToString();
2551
                            newRow["EGTag"] = row["EGTag"].ToString();
2552
                            newRow["HasMLTags"] = row["HasMLTags"].ToString();
2553

    
2554
                            dt.Rows.Add(newRow);
2555
                        }
2556
                    }
2557
                }
2558
                catch (Exception ex)
2559
                {
2560
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
2561
                }
2562
            }
2563

    
2564
            return dt;
2565
        }
2566

    
2567
        public static DataTable GetSequenceData()
2568
        {
2569
            DataTable dt = null;
2570

    
2571
            ID2Info id2Info = ID2Info.GetInstance();
2572
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2573
            {
2574
                try
2575
                {
2576
                    var query = $"SELECT * FROM {PSN_SEQUENCEDATA}";
2577
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
2578
                    {
2579
                        dt = ds.Tables[0].Copy();
2580
                    }
2581
                }
2582
                catch (Exception ex)
2583
                {
2584
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
2585
                }
2586
            }
2587

    
2588
            return dt;
2589
        }
2590

    
2591

    
2592
        //Anohter DB
2593
        public static bool ConnTestAndCreateAnotherTable()
2594
        {
2595
            bool result = false;
2596
            AnotherID2Info id2Info = AnotherID2Info.GetInstance();
2597

    
2598
            using (IAbstractDatabase connection = id2Info.CreateConnection())
2599
            {
2600
                try
2601
                {
2602
                    var names = connection.GetTableNames();
2603
                    var matched = names.FirstOrDefault(param => param == PSN_HEADER_SETTING);
2604
                    Dictionary<string, string> dicColCheck = new Dictionary<string, string>();
2605
                    dicColCheck.Add("GROUP_ID", "TEXT");
2606
                    dicColCheck.Add("DESCRIPTION", "TEXT");
2607
                    dicColCheck.Add("INDEX", "INTEGER");
2608
                    dicColCheck.Add("NAME", "TEXT");
2609

    
2610
                    if (matched == null)
2611
                    {
2612
                        var query = $"CREATE TABLE {PSN_HEADER_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";
2613
                        using (var cmd = connection.GetSqlStringCommand(query))
2614
                        {
2615
                            cmd.ExecuteNonQuery();
2616
                        }
2617
                    }
2618
                    else
2619
                    {
2620
                        AddColumn(PSN_HEADER_SETTING, dicColCheck);
2621
                    }
2622

    
2623
                    matched = names.FirstOrDefault(param => param == PSN_VENTDRAIN_SETTING);
2624
                    dicColCheck.Clear();
2625
                    dicColCheck.Add("GROUP_ID", "TEXT");
2626
                    dicColCheck.Add("DESCRIPTION", "TEXT");
2627
                    dicColCheck.Add("INDEX", "INTEGER");
2628
                    dicColCheck.Add("NAME", "TEXT");
2629
                    if (matched == null)
2630
                    {
2631
                        var query = $"CREATE TABLE {PSN_VENTDRAIN_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";
2632
                        using (var cmd = connection.GetSqlStringCommand(query))
2633
                        {
2634
                            cmd.ExecuteNonQuery();
2635
                        }
2636
                    }
2637
                    else
2638
                    {
2639
                        AddColumn(PSN_VENTDRAIN_SETTING, dicColCheck);
2640
                    }
2641

    
2642
                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGY_RULE);
2643
                    dicColCheck.Clear();
2644
                    dicColCheck.Add("UID", "TEXT");
2645
                    if (matched == null)
2646
                    {
2647
                        var query = $"CREATE TABLE {PSN_TOPOLOGY_RULE} (UID TEXT)";
2648
                        using (var cmd = connection.GetSqlStringCommand(query))
2649
                        {
2650
                            cmd.ExecuteNonQuery();
2651
                        }
2652

    
2653
                        DataTable topologyRule = new DataTable();
2654
                        topologyRule.Columns.Add("NAME", typeof(string));
2655

    
2656
                        topologyRule.Rows.Add("FluidCode");
2657
                        topologyRule.Rows.Add("-");
2658
                        topologyRule.Rows.Add("PipingMaterialsClass");
2659
                        topologyRule.Rows.Add("-");
2660
                        topologyRule.Rows.Add("Tag Seq No");
2661

    
2662
                        SaveTopologyRule(topologyRule);
2663
                    }
2664
                    //else
2665
                    //{
2666
                    //    AddColumn(PSN_TOPOLOGY_RULE, dicColCheck);
2667
                    //}
2668

    
2669
                    matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEMNETWORK);
2670
                    dicColCheck.Clear();
2671
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2672
                    dicColCheck.Add("Type", "NVARCHAR(255)");
2673
                    dicColCheck.Add("OrderNumber", "NVARCHAR(255)");
2674
                    dicColCheck.Add("Pipeline_OID", "NVARCHAR(255)");
2675
                    dicColCheck.Add("From_Data", "NVARCHAR(255)");
2676
                    dicColCheck.Add("To_Data", "NVARCHAR(255)");
2677
                    dicColCheck.Add("TopologySet_OID_Key", "NVARCHAR(125)");
2678
                    dicColCheck.Add("PSNRevisionNumber", "NVARCHAR(255)");
2679
                    dicColCheck.Add("IsValid", "INT");
2680
                    dicColCheck.Add("Status", "NVARCHAR(255)");
2681
                    dicColCheck.Add("PBS", "NVARCHAR(255)");
2682
                    dicColCheck.Add("Drawings", "NVARCHAR(255)");
2683
                    dicColCheck.Add("IncludingVirtualData", "NVARCHAR(10)");
2684
                    dicColCheck.Add("PSNAccuracy", "REAL");
2685
                    dicColCheck.Add("Pocket", "NVARCHAR(10)");
2686
                    dicColCheck.Add("EGTag", "NVARCHAR(255)");
2687
                    dicColCheck.Add("HasMLTags", "NVARCHAR(50)");
2688

    
2689
                    if (matched == null)
2690
                    {
2691
                        var query = $"CREATE TABLE {PSN_PIPESYSTEMNETWORK} (OID NVARCHAR(255), Type NVARCHAR(255), OrderNumber NVARCHAR(255), Pipeline_OID NVARCHAR(255), From_Data NVARCHAR(255), " +
2692
                            "To_Data NVARCHAR(255), TopologySet_OID_Key NVARCHAR(125), PSNRevisionNumber NVARCHAR(255), IsValid INTEGER, Status NVARCHAR(255), PBS NVARCHAR(255), Drawings NVARCHAR(255), " +
2693
                            "IncludingVirtualData NVARCHAR(10),  PSNAccuracy REAL, Pocket NVARCHAR(10), EGTag NVARCHAR(255), HasMLTags NVARCHAR(50))";
2694
                        using (var cmd = connection.GetSqlStringCommand(query))
2695
                        {
2696
                            cmd.ExecuteNonQuery();
2697
                        }
2698
                    }
2699
                    else
2700
                    {
2701
                        AddColumn(PSN_PIPESYSTEMNETWORK, dicColCheck);
2702
                    }
2703

    
2704
                    matched = names.FirstOrDefault(param => param == PSN_EQUIPMENT);
2705
                    dicColCheck.Clear();
2706
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2707
                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");
2708
                    dicColCheck.Add("Xcoords", "REAL");
2709
                    dicColCheck.Add("Ycoords", "REAL");
2710
                    if (matched == null)
2711
                    {
2712
                        var query = $"CREATE TABLE {PSN_EQUIPMENT} (OID NVARCHAR(255), ItemTag NVARCHAR(255), Xcoords REAL, Ycoords REAL)";
2713
                        using (var cmd = connection.GetSqlStringCommand(query))
2714
                        {
2715
                            cmd.ExecuteNonQuery();
2716
                        }
2717
                    }
2718
                    else
2719
                    {
2720
                        AddColumn(PSN_EQUIPMENT, dicColCheck);
2721
                    }
2722

    
2723
                    dicColCheck.Clear();
2724
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2725
                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");
2726
                    dicColCheck.Add("Xcoords", "REAL");
2727
                    dicColCheck.Add("Ycoords", "REAL");
2728
                    dicColCheck.Add("Equipment_OID", "NVARCHAR(255)");
2729
                    dicColCheck.Add("Fluid", "NVARCHAR(255)");
2730
                    dicColCheck.Add("NPD", "NVARCHAR(255)");
2731
                    dicColCheck.Add("PMC", "NVARCHAR(255)");
2732
                    dicColCheck.Add("Rotation", "REAL");
2733
                    dicColCheck.Add("FlowDirection", "NVARCHAR(255)");
2734

    
2735
                    matched = names.FirstOrDefault(param => param == PSN_NOZZLE);
2736
                    if (matched == null)
2737
                    {
2738
                        var query = $"CREATE TABLE {PSN_NOZZLE} (OID NVARCHAR(255), ItemTag NVARCHAR(255), Xcoords REAL, Ycoords REAL, Equipment_OID NVARCHAR(255), " +
2739
                            "Fluid NVARCHAR(255), NPD NVARCHAR(255), PMC NVARCHAR(255), Rotation REAL, FlowDirection NVARCHAR(255))";
2740
                        using (var cmd = connection.GetSqlStringCommand(query))
2741
                        {
2742
                            cmd.ExecuteNonQuery();
2743
                        }
2744
                    }
2745
                    else
2746
                    {
2747
                        AddColumn(PSN_NOZZLE, dicColCheck);
2748
                    }
2749

    
2750
                    matched = names.FirstOrDefault(param => param == PSN_FLUIDCODE);
2751
                    dicColCheck.Clear();
2752
                    dicColCheck.Add("UID", "NVARCHAR(50)");
2753
                    dicColCheck.Add("Code", "NVARCHAR(255)");
2754
                    dicColCheck.Add("Description", "NVARCHAR(255)");
2755
                    dicColCheck.Add("Condition", "NVARCHAR(255)");
2756
                    dicColCheck.Add("Remarks", "NVARCHAR(255)");
2757
                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");
2758
                    if (matched == null)
2759
                    {
2760
                        var query = $"CREATE TABLE {PSN_FLUIDCODE} (UID NVARCHAR(50), Code NVARCHAR(255), Description NVARCHAR(255), Condition NVARCHAR(255), Remarks NVARCHAR(255), GroundLevel NVARCHAR(20))";
2761
                        using (var cmd = connection.GetSqlStringCommand(query))
2762
                        {
2763
                            cmd.ExecuteNonQuery();
2764
                        }
2765
                    }
2766
                    else
2767
                    {
2768
                        AddColumn(PSN_FLUIDCODE, dicColCheck);
2769
                    }
2770

    
2771
                    matched = names.FirstOrDefault(param => param == PSN_PIPINGMATLCLASS);
2772
                    dicColCheck.Clear();
2773
                    dicColCheck.Add("UID", "NVARCHAR(50)");
2774
                    dicColCheck.Add("Priority", "INTEGER");
2775
                    dicColCheck.Add("Code", "NVARCHAR(255)");
2776
                    dicColCheck.Add("Description", "NVARCHAR(255)");
2777
                    dicColCheck.Add("Condition", "NVARCHAR(255)");
2778
                    dicColCheck.Add("Remarks", "NVARCHAR(255)");
2779
                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");
2780
                    if (matched == null)
2781
                    {
2782
                        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))";
2783
                        using (var cmd = connection.GetSqlStringCommand(query))
2784
                        {
2785
                            cmd.ExecuteNonQuery();
2786
                        }
2787
                    }
2788
                    else
2789
                    {
2790
                        AddColumn(PSN_PIPINGMATLCLASS, dicColCheck);
2791
                    }
2792

    
2793
                    matched = names.FirstOrDefault(param => param == PSN_SEQUENCEDATA);
2794
                    dicColCheck.Clear();
2795
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2796
                    dicColCheck.Add("SERIALNUMBER", "NVARCHAR(255)");
2797
                    dicColCheck.Add("PathItem_OID", "NVARCHAR(255)");
2798
                    dicColCheck.Add("TopologySet_OID_Key", "NVARCHAR(255)");
2799
                    if (matched == null)
2800
                    {
2801
                        var query = $"CREATE TABLE {PSN_SEQUENCEDATA} (OID NVARCHAR(255), SERIALNUMBER NVARCHAR(255), PathItem_OID NVARCHAR(255), TopologySet_OID_Key NVARCHAR(255))";
2802
                        using (var cmd = connection.GetSqlStringCommand(query))
2803
                        {
2804
                            cmd.ExecuteNonQuery();
2805
                        }
2806
                    }
2807
                    else
2808
                    {
2809
                        AddColumn(PSN_SEQUENCEDATA, dicColCheck);
2810
                    }
2811

    
2812
                    matched = names.FirstOrDefault(param => param == PSN_PATHITEMS);
2813
                    dicColCheck.Clear();
2814
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2815
                    dicColCheck.Add("SequenceData_OID", "NVARCHAR(255)");
2816
                    dicColCheck.Add("TopologySet_OID", "NVARCHAR(255)");
2817
                    dicColCheck.Add("BranchTopologySet_OID", "NVARCHAR(255)");
2818
                    dicColCheck.Add("PipeLine_OID", "NVARCHAR(255)");
2819
                    dicColCheck.Add("ItemName", "NVARCHAR(255)");
2820
                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");
2821
                    dicColCheck.Add("DESCRIPTION", "NVARCHAR(255)");
2822
                    dicColCheck.Add("CLASS", "NVARCHAR(255)");
2823
                    dicColCheck.Add("SUBCLASS", "NVARCHAR(255)");
2824
                    dicColCheck.Add("TYPE", "NVARCHAR(255)");
2825
                    dicColCheck.Add("PIDNAME", "NVARCHAR(255)");
2826
                    dicColCheck.Add("Equipment_OID", "NVARCHAR(255)");
2827
                    dicColCheck.Add("NPD", "NVARCHAR(255)");
2828
                    dicColCheck.Add("GROUPTAG", "NVARCHAR(255)");
2829
                    dicColCheck.Add("PipeSystemNetwork_OID", "NVARCHAR(255)");
2830
                    dicColCheck.Add("PipeRun_OID", "NVARCHAR(255)");
2831
                    dicColCheck.Add("ViewPipeSystemNetwork_OID", "NVARCHAR(255)");
2832
                    dicColCheck.Add("EqpGroupTag", "NVARCHAR(255)");
2833
                    dicColCheck.Add("MainLineTag", "NVARCHAR(255)");
2834
                    dicColCheck.Add("EGTConnectedPoint", "INT");
2835
                    dicColCheck.Add("EGFlowDirection", "NVARCHAR(10)");
2836
                    if (matched == null)
2837
                    {
2838
                        var query = $"CREATE TABLE {PSN_PATHITEMS} (OID NVARCHAR(255), SequenceData_OID NVARCHAR(255), " +
2839
                            "TopologySet_OID NVARCHAR(255), BranchTopologySet_OID NVARCHAR(255), PipeLine_OID NVARCHAR(255), ItemName NVARCHAR(255), ItemTag NVARCHAR(255), DESCRIPTION NVARCHAR(255), " +
2840
                            "CLASS NVARCHAR(255), SUBCLASS NVARCHAR(255), TYPE NVARCHAR(255), PIDNAME NVARCHAR(255), Equipment_OID NVARCHAR(255), NPD NVARCHAR(255), GROUPTAG NVARCHAR(255), PipeSystemNetwork_OID NVARCHAR(255), PipeRun_OID NVARCHAR(255), " +
2841
                            "ViewPipeSystemNetwork_OID NVARCHAR(255), EqpGroupTag NVARCHAR(255), MainLineTag NVARCHAR(255), EGTConnectedPoint INT, EGFlowDirection NVARCHAR(10))";
2842
                        using (var cmd = connection.GetSqlStringCommand(query))
2843
                        {
2844
                            cmd.ExecuteNonQuery();
2845
                        }
2846
                    }
2847
                    else
2848
                    {
2849
                        AddColumn(PSN_PATHITEMS, dicColCheck);
2850
                    }
2851

    
2852
                    matched = names.FirstOrDefault(param => param == PSN_VIEW);
2853
                    dicColCheck.Clear();
2854
                    dicColCheck.Add("OID", "TEXT");
2855
                    if (matched == null)
2856
                    {
2857
                        var query = $"CREATE TABLE {PSN_VIEW} (OID TEXT)";
2858
                        using (var cmd = connection.GetSqlStringCommand(query))
2859
                        {
2860
                            cmd.ExecuteNonQuery();
2861
                        }
2862
                    }
2863

    
2864
                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGYSET);
2865
                    dicColCheck.Clear();
2866
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2867
                    dicColCheck.Add("Type", "NVARCHAR(255)");
2868
                    dicColCheck.Add("SubType", "NVARCHAR(255)");
2869
                    dicColCheck.Add("HeadItemTag", "NVARCHAR(255)");
2870
                    dicColCheck.Add("TailItemTag", "NVARCHAR(255)");
2871
                    dicColCheck.Add("HeadItemSPID", "NVARCHAR(255)");
2872
                    dicColCheck.Add("TailItemSPID", "NVARCHAR(255)");
2873

    
2874
                    if (matched == null)
2875
                    {
2876
                        var query = $"CREATE TABLE { PSN_TOPOLOGYSET} (OID NVARCHAR(255), Type NVARCHAR(255), SubType NVARCHAR(255), HeadItemTag NVARCHAR(255), TailItemTag NVARCHAR(255), HeadItemSPID NVARCHAR(255), TailItemSPID NVARCHAR(255))";
2877
                        using (var cmd = connection.GetSqlStringCommand(query))
2878
                        {
2879
                            cmd.ExecuteNonQuery();
2880
                        }
2881
                    }
2882
                    else
2883
                    {
2884
                        AddColumn(PSN_TOPOLOGYSET, dicColCheck);
2885
                    }
2886

    
2887
                    matched = names.FirstOrDefault(param => param == PSN_TRANSFORMKEYWORD_SETTING);
2888
                    dicColCheck.Clear();
2889
                    dicColCheck.Add("INDEX", "INTEGER");
2890
                    dicColCheck.Add("NAME", "TEXT");
2891
                    dicColCheck.Add("KEYWORD", "TEXT");
2892
                    if (matched == null)
2893
                    {
2894
                        var query = $"CREATE TABLE {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX] INTEGER, [NAME] TEXT, [KEYWORD] TEXT)";
2895
                        using (var cmd = connection.GetSqlStringCommand(query))
2896
                        {
2897
                            cmd.ExecuteNonQuery();
2898
                        }
2899
                    }
2900
                    else
2901
                    {
2902
                        AddColumn(PSN_TRANSFORMKEYWORD_SETTING, dicColCheck);
2903
                    }
2904

    
2905
                    matched = names.FirstOrDefault(param => param == PSN_PIPELINE);
2906
                    dicColCheck.Clear();
2907
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2908
                    dicColCheck.Add("PipeSystem_OID", "NVARCHAR(255)");
2909
                    dicColCheck.Add("FLUID", "NVARCHAR(255)");
2910
                    dicColCheck.Add("PMC", "NVARCHAR(255)");
2911
                    dicColCheck.Add("SEQNUMBER", "NVARCHAR(255)");
2912
                    dicColCheck.Add("INSULATION", "NVARCHAR(255)");
2913
                    dicColCheck.Add("FROM_DATA", "NVARCHAR(255)");
2914
                    dicColCheck.Add("TO_DATA", "NVARCHAR(255)");
2915
                    dicColCheck.Add("Unit", "NVARCHAR(100)");
2916
                    if (matched == null)
2917
                    {
2918
                        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), " +
2919
                            $"FROM_DATA NVARCHAR(255), TO_DATA NVARCHAR(255), Unit NVARCHAR(100))";
2920
                        using (var cmd = connection.GetSqlStringCommand(query))
2921
                        {
2922
                            cmd.ExecuteNonQuery();
2923
                        }
2924
                    }
2925
                    else
2926
                    {
2927
                        AddColumn(PSN_PIPELINE, dicColCheck);
2928
                    }
2929

    
2930
                    matched = names.FirstOrDefault(param => param == PSN_INSULATIONPURPOSE);
2931
                    dicColCheck.Clear();
2932
                    dicColCheck.Add("UID", "NVARCHAR(50)");
2933
                    dicColCheck.Add("Code", "NVARCHAR(255)");
2934
                    dicColCheck.Add("Description", "NVARCHAR(255)");
2935
                    dicColCheck.Add("Remarks", "NVARCHAR(255)");
2936
                    if (matched == null)
2937
                    {
2938
                        var query = $"CREATE TABLE {PSN_INSULATIONPURPOSE} (UID NVARCHAR(50), Code NVARCHAR(255), Description NVARCHAR(255), Remarks NVARCHAR(255))";
2939
                        using (var cmd = connection.GetSqlStringCommand(query))
2940
                        {
2941
                            cmd.ExecuteNonQuery();
2942
                        }
2943
                    }
2944
                    else
2945
                    {
2946
                        AddColumn(PSN_INSULATIONPURPOSE, dicColCheck);
2947
                    }
2948

    
2949
                    matched = names.FirstOrDefault(param => param == PSN_VALVEGROUP_SETTING);
2950
                    dicColCheck.Clear();
2951
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2952
                    dicColCheck.Add("GroupType", "NVARCHAR(255)");
2953
                    dicColCheck.Add("TagIdentifier", "NVARCHAR(50)");
2954
                    dicColCheck.Add("AttributeName", "NVARCHAR(255)");
2955
                    dicColCheck.Add("SppidSymbolName", "NVARCHAR(255)");
2956
                    if (matched == null)
2957
                    {
2958
                        var query = $"CREATE TABLE {PSN_VALVEGROUP_SETTING} (OID NVARCHAR(50), GroupType NVARCHAR(255), TagIdentifier NVARCHAR(50), AttributeName NVARCHAR(255), SppidSymbolName NVARCHAR(255))";
2959
                        using (var cmd = connection.GetSqlStringCommand(query))
2960
                        {
2961
                            cmd.ExecuteNonQuery();
2962
                        }
2963
                    }
2964
                    else
2965
                    {
2966
                        AddColumn(PSN_VALVEGROUP_SETTING, dicColCheck);
2967
                    }
2968

    
2969
                    matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEM);
2970
                    dicColCheck.Clear();
2971
                    dicColCheck.Add("OID", "NVARCHAR(255)");
2972
                    dicColCheck.Add("DESCRIPTION", "NVARCHAR(255)");
2973
                    dicColCheck.Add("FLUID", "NVARCHAR(255)");
2974
                    dicColCheck.Add("PMC", "NVARCHAR(255)");
2975
                    dicColCheck.Add("PipeLineQty", "NVARCHAR(255)");
2976
                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");
2977
                    if (matched == null)
2978
                    {
2979
                        var query = $"CREATE TABLE {PSN_PIPESYSTEM} (OID NVARCHAR(255), DESCRIPTION NVARCHAR(255), FLUID NVARCHAR(255), PMC NVARCHAR(255), PipeLineQty NVARCHAR(255), GroundLevel NVARCHAR(20))";
2980
                        using (var cmd = connection.GetSqlStringCommand(query))
2981
                        {
2982
                            cmd.ExecuteNonQuery();
2983
                        }
2984
                    }
2985
                    else
2986
                    {
2987
                        AddColumn(PSN_PIPESYSTEM, dicColCheck);
2988
                    }
2989

    
2990
                    matched = names.FirstOrDefault(param => param == PSN_NOPOCKETSETTING);
2991
                    dicColCheck.Clear();
2992
                    dicColCheck.Add("[INDEX]", "INTEGER");
2993
                    dicColCheck.Add("[TYPE]", "TEXT");
2994
                    dicColCheck.Add("[NAME]", "TEXT");
2995
                    if (matched == null)
2996
                    {
2997
                        var query = $"CREATE TABLE {PSN_NOPOCKETSETTING} ([INDEX] INTEGER, [TYPE] TEXT, [NAME] TEXT)";
2998
                        using (var cmd = connection.GetSqlStringCommand(query))
2999
                        {
3000
                            cmd.ExecuteNonQuery();
3001
                        }
3002
                    }
3003
                    else
3004
                    {
3005
                        AddColumn(PSN_NOPOCKETSETTING, dicColCheck);
3006
                    }
3007

    
3008
                    matched = names.FirstOrDefault(param => param == PSN_AIRFINCOOLERSETTING);
3009
                    dicColCheck.Clear();
3010
                    dicColCheck.Add("[INDEX]", "INTEGER");
3011
                    dicColCheck.Add("[TYPE]", "TEXT");
3012
                    dicColCheck.Add("[NAME]", "TEXT");
3013
                    if (matched == null)
3014
                    {
3015
                        var query = $"CREATE TABLE {PSN_AIRFINCOOLERSETTING}  ([INDEX] INTEGER, [TYPE] TEXT, [NAME] TEXT)";
3016
                        using (var cmd = connection.GetSqlStringCommand(query))
3017
                        {
3018
                            cmd.ExecuteNonQuery();
3019
                        }
3020
                    }
3021
                    else
3022
                    {
3023
                        AddColumn(PSN_AIRFINCOOLERSETTING, dicColCheck);
3024
                    }
3025

    
3026

    
3027
                    var query2 = $"If(db_id(N'" + PSN_COMMON + "') IS NULL) CREATE DATABASE [" + PSN_COMMON + "]";
3028
                    if (id2Info.ID2DBType == AnotherID2DB_Type.MSSQL)
3029
                    {
3030
                        //connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3031
                        using (var cmd = connection.GetSqlStringCommand(query2))
3032
                        {
3033
                            cmd.ExecuteNonQuery();
3034
                        }
3035
                    }
3036

    
3037
                    void AddColumn(string TableName, Dictionary<string, string> dicCol)
3038
                    {
3039
                        var colnames = connection.GetColumnNames(TableName);
3040
                        bool check = false;
3041
                        if (colnames != null)
3042
                        {
3043
                            foreach (KeyValuePair<string, string> col in dicCol)
3044
                            {
3045
                                check = false;
3046
                                foreach (string c in colnames)
3047
                                {
3048
                                    if (col.Key.ToUpper().Equals(c.ToUpper()))
3049
                                    {
3050
                                        check = true;
3051
                                        break;
3052
                                    }
3053
                                }
3054

    
3055
                                if (!check) //없으면 추가
3056
                                {
3057
                                    string i = string.Empty;
3058
                                    if (col.Value.ToString().ToUpper().Contains("INT") || col.Value.ToString().ToUpper().Contains("REAL"))
3059
                                        i = "DEFAULT 0";
3060

    
3061
                                    var query = $"ALTER TABLE " + TableName + " ADD [" + col.Key + "] " + col.Value + " " + i + ";";
3062
                                    using (var cmd = connection.GetSqlStringCommand(query))
3063
                                    {
3064
                                        cmd.ExecuteNonQuery();
3065
                                    }
3066
                                }
3067
                            }
3068
                        }
3069
                    }
3070

    
3071
                    result = true;
3072
                }
3073
                catch (Exception ex)
3074
                {
3075
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
3076
                }
3077
            }
3078

    
3079
            return result;
3080
        }
3081

    
3082
        public static bool SaveAnotherRevision(string ProjectCode, int RevNumber, string UserName, string PSNDatabasePath, string PsnByPBSFilter, string PsnByNPDFilter,
3083
         int PidDrawings, int PipeSystems, int PipeLines, int Topologies, int PipeSystemNetworks, int InValidPSNs, int E2E, int E2B, int B2E, int HDE, int HD2, int HDB, int B2B)
3084
        {
3085
            AnotherID2Info id2Info = AnotherID2Info.GetInstance();
3086

    
3087
            bool result = true;
3088

    
3089
            using (IAbstractDatabase connection = id2Info.CreateConnection())
3090
            {
3091
                try
3092
                {
3093
                    using (var txn = connection.BeginTransaction())
3094
                    {
3095
                        try
3096
                        {
3097

    
3098
                            var query = $"INSERT INTO ARS_COMMON.dbo.PSNRevision (OID, Project_OID, ProjectCode, RevNumber, UserName, TimeData, PSNDatabasePath, " +
3099
                                $"PsnByPBSFilter, PsnByNPDFilter, PidDrawings, PipeSystems, PipeLines, Topologies, PipeSystemNetworks, InValidPSNs, E2E, E2B, B2E, HDE, " +
3100
                                $"HD2, HDB, B2B, LastModificationdate, ID2_PSN) VALUES (NEWID(), NEWID(), @ProjectCode, @RevNumber, @UserName, @TimeData, @PSNDatabasePath" +
3101
                                $", @PsnByPBSFilter, @PsnByNPDFilter, @PidDrawings, @PipeSystems, @PipeLines, @Topologies, @PipeSystemNetworks, @InValidPSNs, " +
3102
                                $"@E2E, @E2B, @B2E, @HDE, @HD2, @HDB, @B2B, @LastModificationdate, @ID2_PSN)";
3103
                            var cmd = connection.GetSqlStringCommand(query);
3104
                            AddWithValue(cmd, "@OID", Guid.NewGuid().ToString());
3105
                            AddWithValue(cmd, "@Project_OID", Guid.NewGuid().ToString());
3106
                            AddWithValue(cmd, "@ProjectCode", ProjectCode);
3107
                            AddWithValue(cmd, "@RevNumber", RevNumber);
3108
                            AddWithValue(cmd, "@UserName", UserName);
3109

    
3110
                            System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");
3111
                            DateTime oDateTime = DateTime.Now;
3112

    
3113
                            AddWithValue(cmd, "@TimeData", oDateTime.ToString(ci)); //String.Format("{0:G}", dt) // DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
3114

    
3115
                            AddWithValue(cmd, "@PSNDatabasePath", PSNDatabasePath);
3116

    
3117
                            AddWithValue(cmd, "@PsnByPBSFilter", PsnByPBSFilter);
3118
                            AddWithValue(cmd, "@PsnByNPDFilter", PsnByNPDFilter);
3119
                            AddWithValue(cmd, "@PidDrawings", PidDrawings);
3120
                            AddWithValue(cmd, "@PipeSystems", PipeSystems);
3121
                            AddWithValue(cmd, "@PipeLines", PipeLines);
3122

    
3123
                            AddWithValue(cmd, "@Topologies", Topologies);
3124
                            AddWithValue(cmd, "@PipeSystemNetworks", PipeSystemNetworks);
3125
                            AddWithValue(cmd, "@InValidPSNs", InValidPSNs);
3126
                            AddWithValue(cmd, "@E2E", E2E);
3127
                            AddWithValue(cmd, "@E2B", E2B);
3128
                            AddWithValue(cmd, "@B2E", B2E);
3129
                            AddWithValue(cmd, "@HDE", HDE);
3130
                            AddWithValue(cmd, "@HD2", HD2);
3131
                            AddWithValue(cmd, "@HDB", HDB);
3132
                            AddWithValue(cmd, "@B2B", B2B);
3133

    
3134
                            AddWithValue(cmd, "@LastModificationdate", oDateTime.ToString(ci));
3135
                            AddWithValue(cmd, "@ID2_PSN", "Y");
3136

    
3137
                            connection.ExecuteNonQuery(cmd, txn);
3138

    
3139
                            txn.Commit();
3140
                        }
3141
                        catch (Exception ex)
3142
                        {
3143
                            txn.Rollback();
3144
                            result = false;
3145
                        }
3146
                    }
3147
                }
3148
                catch (Exception ex)
3149
                {
3150
                    System.Windows.Forms.MessageBox.Show(ex.Message);
3151
                    result = false;
3152
                }
3153
            }
3154

    
3155
            return result;
3156
        }
3157

    
3158
        public static bool ConnAnotherCreateRevisionTable(Dictionary<string, string> dicColCheck, List<string> names)
3159
        {
3160
            bool result = false;
3161
            AnotherID2Info id2Info = AnotherID2Info.GetInstance();
3162

    
3163
            using (IAbstractDatabase connection = id2Info.CreateConnection())
3164
            {
3165
                try
3166
                {
3167
                    if (names.Count == 0)
3168
                    {
3169

    
3170
                        var query = $"CREATE TABLE ARS_COMMON.dbo.PSNRevision ([OID] [uniqueidentifier] ,[Project_OID] [uniqueidentifier] ," +
3171
                            $"[ProjectCode] nvarchar(255), [RevNumber] int ,[UserName] nvarchar(255) ,[TimeData] nvarchar(255) ," +
3172
                            $"[PSNDatabasePath] nvarchar(255) ,	[PsnByPBSFilter] varchar(20) ,[PsnByNPDFilter] varchar(20) ,[PidDrawings] int ,	" +
3173
                            $"[PipeSystems] int ,[PipeLines] int ,[Topologies] int ,[PipeSystemNetworks] int ,[InValidPSNs] int ,[E2E] int ," +
3174
                            $"[E2B] int ,[B2E] int ,[HDE] int ,[HD2] int ,[HDB] int ,[B2B] int , [LastModificationdate] nvarchar(255), [ID2_PSN] nvarchar(10))";
3175
                        using (var cmd = connection.GetSqlStringCommand(query))
3176
                        {
3177
                            cmd.ExecuteNonQuery();
3178
                        }
3179
                    }
3180
                    else
3181
                    {
3182
                        AddColumn(PSN_REVISION, dicColCheck, names);
3183
                    }
3184

    
3185
                    void AddColumn(string TableName, Dictionary<string, string> dicCol, List<string> colnames)
3186
                    {
3187
                        bool check = false;
3188
                        if (colnames != null)
3189
                        {
3190
                            foreach (KeyValuePair<string, string> col in dicCol)
3191
                            {
3192
                                check = false;
3193
                                foreach (string c in colnames)
3194
                                {
3195
                                    if (col.Key.Contains(c))
3196
                                    {
3197
                                        check = true;
3198
                                        break;
3199
                                    }
3200
                                }
3201

    
3202
                                if (!check) //없으면 추가
3203
                                {
3204
                                    string i = string.Empty;
3205
                                    if (col.Value.ToString().ToUpper().Contains("INT") || col.Value.ToString().ToUpper().Contains("REAL"))
3206
                                        i = "DEFAULT 0";
3207

    
3208
                                    var queryalter = $"ALTER TABLE ARS_COMMON.dbo.PSNRevision ADD [" + col.Key + "] " + col.Value + " " + i + ";";
3209
                                    using (var cmd = connection.GetSqlStringCommand(queryalter))
3210
                                    {
3211
                                        cmd.ExecuteNonQuery();
3212
                                    }
3213
                                }
3214
                            }
3215
                        }
3216
                    }
3217

    
3218
                    result = true;
3219
                }
3220
                catch (Exception ex)
3221
                {
3222
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
3223
                }
3224
            }
3225

    
3226
            return result;
3227
        }
3228

    
3229
        public static DataTable SelectAnotherRevision()
3230
        {
3231
            DataTable dt = null;
3232
            AnotherID2Info id2Info = AnotherID2Info.GetInstance();
3233

    
3234
            using (IAbstractDatabase connection = id2Info.CreateConnection())
3235
            {
3236
                try
3237
                {
3238
                    var query = $"SELECT * FROM ARS_COMMON.dbo.PSNREVISION";
3239

    
3240
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
3241
                    {
3242
                        dt = ds.Tables[0].Copy();
3243
                    }
3244
                }
3245
                catch (Exception ex)
3246
                {
3247
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
3248
                }
3249
            }
3250

    
3251
            return dt;
3252
        }
3253

    
3254
        public static DataTable SelectAnotherRevisionTable()
3255
        {
3256
            DataTable dt = null;
3257
            AnotherID2Info id2Info = AnotherID2Info.GetInstance();
3258

    
3259
            using (IAbstractDatabase connection = id2Info.CreateConnection())
3260
            {
3261
                try
3262
                {
3263
                    var query = $"SELECT T.name AS table_name, C.name AS column_name FROM ARS_COMMON.sys.tables AS T INNER JOIN ARS_COMMON.sys.columns AS C ON T.object_id = C.object_id WHERE T.name = 'PSNREVISION'";
3264
                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))
3265
                    {
3266
                        dt = ds.Tables[0].Copy();
3267
                    }
3268
                }
3269
                catch (Exception ex)
3270
                {
3271
                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);
3272
                }
3273
            }
3274

    
3275
            return dt;
3276
        }
3277

    
3278
        public static bool SaveAnotherPSNData(PSN item, List<HeaderInfo> headerInfos, List<VentDrainInfo> ventDrainInfos,
3279
          List<KeywordItem> keywordItems, DataTable dtTopologyRule, DataTable dtFluidCode, DataTable dtPMC, DataTable dtInsulation, DataTable dtvalvegrouping, DataTable dtnopocket)
3280
        {
3281
            AnotherID2Info id2Info = AnotherID2Info.GetInstance();
3282

    
3283
            bool result = true;
3284
            using (IAbstractDatabase connection = id2Info.CreateConnection())
3285
            {
3286
                try
3287
                {
3288
                    using (var txn = connection.BeginTransaction())
3289
                    {
3290
                        try
3291
                        {
3292
                            // Path Items
3293
                            var query = $"DELETE FROM {PSN_PATHITEMS}";
3294
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3295
                            for (int i = 0; i < item.PathItems.Rows.Count; i++)
3296
                            {
3297
                                DataRow row = item.PathItems.Rows[i];
3298
                                query = $"INSERT INTO {PSN_PATHITEMS} " +
3299
                                    $"(OID, SequenceData_OID, TopologySet_OID, BranchTopologySet_OID, PipeLine_OID, ItemName, ItemTag, DESCRIPTION, CLASS, SUBCLASS, TYPE, " +
3300
                                    $"PIDNAME, Equipment_OID, NPD, GROUPTAG, PipeSystemNetwork_OID, PipeRun_OID, ViewPipeSystemNetwork_OID, EqpGroupTag , MainLineTag, EGTConnectedPoint, EGFlowDirection ) VALUES " +
3301
                                    $"(@OID, @SequenceData_OID, @TopologySet_OID, @BranchTopologySet_OID, @PipeLine_OID, @ITEMNAME, @ITEMTAG, @DESCRIPTION, @Class, @SubClass, @TYPE, @PIDNAME," +
3302
                                    $" @Equipment_OID, @NPD, @GROUPTAG, @PipeSystemNetwork_OID,@PipeRun_OID, @ViewPipeSystemNetwork_OID, @EqpGroupTag , @MainLineTag, @EGTConnectedPoint, @EGFlowDirection )";
3303
                                var cmd = connection.GetSqlStringCommand(query);
3304
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
3305
                                AddWithValue(cmd, "@SequenceData_OID", string.IsNullOrEmpty(row["SequenceData_OID"].ToString()) ? "" : row["SequenceData_OID"].ToString());
3306
                                AddWithValue(cmd, "@TopologySet_OID", string.IsNullOrEmpty(row["TopologySet_OID"].ToString()) ? "" : row["TopologySet_OID"].ToString());
3307
                                AddWithValue(cmd, "@BranchTopologySet_OID", string.IsNullOrEmpty(row["BranchTopologySet_OID"].ToString()) ? "" : row["BranchTopologySet_OID"].ToString());
3308
                                AddWithValue(cmd, "@PipeLine_OID", string.IsNullOrEmpty(row["PipeLine_OID"].ToString()) ? "" : row["PipeLine_OID"].ToString());
3309
                                AddWithValue(cmd, "@ITEMNAME", string.IsNullOrEmpty(row["ITEMNAME"].ToString()) ? "" : row["ITEMNAME"].ToString());
3310
                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());
3311
                                AddWithValue(cmd, "@DESCRIPTION", string.IsNullOrEmpty(row["DESCRIPTION"].ToString()) ? "" : row["DESCRIPTION"].ToString());
3312
                                AddWithValue(cmd, "@Class", string.IsNullOrEmpty(row["Class"].ToString()) ? "" : row["Class"].ToString());
3313
                                AddWithValue(cmd, "@SubClass", string.IsNullOrEmpty(row["SubClass"].ToString()) ? "" : row["SubClass"].ToString());
3314
                                AddWithValue(cmd, "@TYPE", string.IsNullOrEmpty(row["TYPE"].ToString()) ? "" : row["TYPE"].ToString());
3315
                                AddWithValue(cmd, "@PIDNAME", string.IsNullOrEmpty(row["PIDNAME"].ToString()) ? "" : row["PIDNAME"].ToString());
3316
                                AddWithValue(cmd, "@Equipment_OID", string.IsNullOrEmpty(row["Equipment_OID"].ToString()) ? "" : row["Equipment_OID"].ToString());
3317
                                AddWithValue(cmd, "@NPD", string.IsNullOrEmpty(row["NPD"].ToString()) ? "" : row["NPD"].ToString());
3318
                                AddWithValue(cmd, "@GROUPTAG", string.IsNullOrEmpty(row["GROUPTAG"].ToString()) ? "" : row["GROUPTAG"].ToString());
3319
                                AddWithValue(cmd, "@PipeSystemNetwork_OID", string.IsNullOrEmpty(row["PipeSystemNetwork_OID"].ToString()) ? "" : row["PipeSystemNetwork_OID"].ToString());
3320
                                AddWithValue(cmd, "@ViewPipeSystemNetwork_OID", string.IsNullOrEmpty(row["ViewPipeSystemNetwork_OID"].ToString()) ? "" : row["ViewPipeSystemNetwork_OID"].ToString());
3321
                                AddWithValue(cmd, "@PipeRun_OID", string.IsNullOrEmpty(row["PipeRun_OID"].ToString()) ? "" : row["PipeRun_OID"].ToString());
3322
                                AddWithValue(cmd, "@EqpGroupTag", string.IsNullOrEmpty(row["EqpGroupTag"].ToString()) ? "" : row["EqpGroupTag"].ToString());
3323
                                AddWithValue(cmd, "@MainLineTag", string.IsNullOrEmpty(row["MainLineTag"].ToString()) ? "" : row["MainLineTag"].ToString());
3324
                                AddWithValue(cmd, "@EGTConnectedPoint", string.IsNullOrEmpty(row["EGTConnectedPoint"].ToString()) ? "0" : row["EGTConnectedPoint"].ToString());
3325
                                AddWithValue(cmd, "@EGFlowDirection", string.IsNullOrEmpty(row["EGFlowDirection"].ToString()) ? "" : row["EGFlowDirection"].ToString());
3326

    
3327
                                connection.ExecuteNonQuery(cmd, txn);
3328
                            }
3329

    
3330
                            // Sequence
3331
                            query = $"DELETE FROM {PSN_SEQUENCEDATA}";
3332
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3333
                            foreach (DataRow row in item.SequenceData.Rows)
3334
                            {
3335
                                query = $"INSERT INTO {PSN_SEQUENCEDATA} (OID, SERIALNUMBER, PathItem_OID, TopologySet_OID_Key) VALUES (@OID, @SERIALNUMBER, @PathItem_OID, @TopologySet_OID_Key)";
3336
                                var cmd = connection.GetSqlStringCommand(query);
3337
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
3338
                                AddWithValue(cmd, "@SERIALNUMBER", string.IsNullOrEmpty(row["SERIALNUMBER"].ToString()) ? "" : row["SERIALNUMBER"].ToString());
3339
                                AddWithValue(cmd, "@PathItem_OID", string.IsNullOrEmpty(row["PathItem_OID"].ToString()) ? "" : row["PathItem_OID"].ToString());
3340
                                AddWithValue(cmd, "@TopologySet_OID_Key", string.IsNullOrEmpty(row["TopologySet_OID_Key"].ToString()) ? "" : row["TopologySet_OID_Key"].ToString());
3341
                                connection.ExecuteNonQuery(cmd, txn);
3342
                            }
3343

    
3344
                            // Nozzle
3345
                            query = $"DELETE FROM {PSN_NOZZLE}";
3346
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3347
                            foreach (DataRow row in item.Nozzle.Rows)
3348
                            {
3349
                                query = $"INSERT INTO {PSN_NOZZLE} (OID, ItemTag, Xcoords, Ycoords, Equipment_OID, Fluid, NPD, PMC, Rotation, FlowDirection) VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS, @Equipment_OID, @FLUID, @NPD, @PMC, @ROTATION, @FlowDirection)";
3350
                                var cmd = connection.GetSqlStringCommand(query);
3351
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
3352
                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());
3353

    
3354
                                if (string.IsNullOrEmpty(row["XCOORDS"].ToString()))
3355
                                    AddWithValue(cmd, "@XCOORDS", DBNull.Value);
3356
                                else
3357
                                    AddWithValue(cmd, "@XCOORDS", row["XCOORDS"].ToString());
3358

    
3359
                                if (string.IsNullOrEmpty(row["YCOORDS"].ToString()))
3360
                                    AddWithValue(cmd, "@YCOORDS", DBNull.Value);
3361
                                else
3362
                                    AddWithValue(cmd, "@YCOORDS", row["YCOORDS"].ToString());
3363

    
3364
                                AddWithValue(cmd, "@Equipment_OID", string.IsNullOrEmpty(row["Equipment_OID"].ToString()) ? "" : row["Equipment_OID"].ToString());
3365
                                AddWithValue(cmd, "@FLUID", string.IsNullOrEmpty(row["FLUID"].ToString()) ? "" : row["FLUID"].ToString());
3366
                                AddWithValue(cmd, "@NPD", string.IsNullOrEmpty(row["NPD"].ToString()) ? "" : row["NPD"].ToString());
3367
                                AddWithValue(cmd, "@PMC", string.IsNullOrEmpty(row["PMC"].ToString()) ? "" : row["PMC"].ToString());
3368

    
3369
                                if (string.IsNullOrEmpty(row["ROTATION"].ToString()))
3370
                                    AddWithValue(cmd, "@ROTATION", DBNull.Value);
3371
                                else
3372
                                    AddWithValue(cmd, "@ROTATION", row["ROTATION"].ToString());
3373

    
3374
                                AddWithValue(cmd, "@FlowDirection", string.IsNullOrEmpty(row["FlowDirection"].ToString()) ? "" : row["FlowDirection"].ToString());
3375
                                connection.ExecuteNonQuery(cmd, txn);
3376
                            }
3377

    
3378
                            //Equipment
3379
                            query = $"DELETE FROM {PSN_EQUIPMENT}";
3380
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3381
                            foreach (DataRow row in item.Equipment.Rows)
3382
                            {
3383
                                query = $"INSERT INTO {PSN_EQUIPMENT} (OID, ItemTag, Xcoords, Ycoords) VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS)";
3384
                                var cmd = connection.GetSqlStringCommand(query);
3385
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
3386
                                AddWithValue(cmd, "@ITEMTAG", string.IsNullOrEmpty(row["ITEMTAG"].ToString()) ? "" : row["ITEMTAG"].ToString());
3387

    
3388
                                if (string.IsNullOrEmpty(row["XCOORDS"].ToString()))
3389
                                    AddWithValue(cmd, "@XCOORDS", DBNull.Value);
3390
                                else
3391
                                    AddWithValue(cmd, "@XCOORDS", row["XCOORDS"].ToString());
3392

    
3393
                                if (string.IsNullOrEmpty(row["YCOORDS"].ToString()))
3394
                                    AddWithValue(cmd, "@YCOORDS", DBNull.Value);
3395
                                else
3396
                                    AddWithValue(cmd, "@YCOORDS", row["YCOORDS"].ToString());
3397

    
3398
                                connection.ExecuteNonQuery(cmd, txn);
3399
                            }
3400

    
3401
                            // TopologySet
3402
                            query = $"DELETE FROM {PSN_TOPOLOGYSET}";
3403
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3404
                            foreach (DataRow row in item.TopologySet.Rows)
3405
                            {
3406
                                query = $"INSERT INTO {PSN_TOPOLOGYSET} (OID, Type, SubType, HeadItemTag, TailItemTag, HeadItemSPID, TailItemSPID) VALUES (@OID, @Type, @SubType, @HeadItemTag, @TailItemTag, @HeadItemSPID, @TailItemSPID)";
3407
                                var cmd = connection.GetSqlStringCommand(query);
3408
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
3409
                                AddWithValue(cmd, "@Type", string.IsNullOrEmpty(row["Type"].ToString()) ? "" : row["Type"].ToString());
3410
                                AddWithValue(cmd, "@SubType", string.IsNullOrEmpty(row["SubType"].ToString()) ? "" : row["SubType"].ToString());
3411
                                AddWithValue(cmd, "@HeadItemTag", string.IsNullOrEmpty(row["HeadItemTag"].ToString()) ? "" : row["HeadItemTag"].ToString());
3412
                                AddWithValue(cmd, "@TailItemTag", string.IsNullOrEmpty(row["TailItemTag"].ToString()) ? "" : row["TailItemTag"].ToString());
3413
                                AddWithValue(cmd, "@HeadItemSPID", string.IsNullOrEmpty(row["HeadItemSPID"].ToString()) ? "" : row["HeadItemSPID"].ToString());
3414
                                AddWithValue(cmd, "@TailItemSPID", string.IsNullOrEmpty(row["TailItemSPID"].ToString()) ? "" : row["TailItemSPID"].ToString());
3415
                                connection.ExecuteNonQuery(cmd, txn);
3416
                            }
3417

    
3418
                            // PSN
3419
                            query = $"DELETE FROM {PSN_PIPESYSTEMNETWORK} WHERE PSNRevisionNumber = '{string.Format("V{0:D4}", item.Revision)}'";
3420
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3421
                            foreach (DataRow row in item.PipeSystemNetwork.Rows)
3422
                            {
3423
                                query = $"INSERT INTO {PSN_PIPESYSTEMNETWORK} " +
3424
                                    $"(OID, Type, OrderNumber, Pipeline_OID, FROM_DATA, TO_DATA, TopologySet_OID_Key, PSNRevisionNumber, PBS, Drawings, IsValid, Status, IncludingVirtualData, PSNAccuracy, Pocket, EGTag, HasMLTags) VALUES " +
3425
                                    $"(@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber,  @PBS, @Drawings, @IsValid, @Status, @IncludingVirtualData, @PSNAccuracy, @Pocket, @EGTag, @HasMLTags)";
3426
                                var cmd = connection.GetSqlStringCommand(query);
3427
                                AddWithValue(cmd, "@OID", string.IsNullOrEmpty(row["OID"].ToString()) ? "" : row["OID"].ToString());
3428
                                AddWithValue(cmd, "@Type", string.IsNullOrEmpty(row["Type"].ToString()) ? "" : row["Type"].ToString());
3429
                                AddWithValue(cmd, "@OrderNumber", string.IsNullOrEmpty(row["OrderNumber"].ToString()) ? "" : row["OrderNumber"].ToString());
3430
                                AddWithValue(cmd, "@Pipeline_OID", string.IsNullOrEmpty(row["Pipeline_OID"].ToString()) ? "" : row["Pipeline_OID"].ToString());
3431
                                AddWithValue(cmd, "@FROM_DATA", string.IsNullOrEmpty(row["FROM_DATA"].ToString()) ? "" : row["FROM_DATA"].ToString());
3432
                                AddWithValue(cmd, "@TO_DATA", string.IsNullOrEmpty(row["TO_DATA"].ToString()) ? "" : row["TO_DATA"].ToString());
3433
                                AddWithValue(cmd, "@TopologySet_OID_Key", string.IsNullOrEmpty(row["TopologySet_OID_Key"].ToString()) ? "" : row["TopologySet_OID_Key"].ToString());
3434
                                AddWithValue(cmd, "@PSNRevisionNumber", string.IsNullOrEmpty(row["PSNRevisionNumber"].ToString()) ? "" : row["PSNRevisionNumber"].ToString());
3435

    
3436
                                int IsValid = 0;
3437
                                if (row["IsValid"].ToString() == string.Empty || row["IsValid"].ToString() == "OK")
3438
                                    IsValid = 0;
3439
                                else if (row["IsValid"].ToString() == "InValid")
3440
                                    IsValid = 1;
3441
                                else if (row["IsValid"].ToString() == "Error")
3442
                                    IsValid = -1;
3443

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

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

    
3450
                                AddWithValue(cmd, "@IncludingVirtualData", string.IsNullOrEmpty(row["IncludingVirtualData"].ToString()) ? "" : row["IncludingVirtualData"].ToString());
3451
                                if (string.IsNullOrEmpty(row["PSNAccuracy"].ToString()))
3452
                                    AddWithValue(cmd, "@PSNAccuracy", DBNull.Value);
3453
                                else
3454
                                    AddWithValue(cmd, "@PSNAccuracy", row["PSNAccuracy"].ToString().Replace("%", ""));
3455

    
3456
                                AddWithValue(cmd, "@Pocket", string.IsNullOrEmpty(row["Pocket"].ToString()) ? "Yes" : row["Pocket"].ToString());
3457
                                AddWithValue(cmd, "@EGTag", string.IsNullOrEmpty(row["EGTag"].ToString()) ? "" : row["EGTag"].ToString());
3458
                                AddWithValue(cmd, "@HasMLTags", string.IsNullOrEmpty(row["HasMLTags"].ToString()) ? "False" : row["HasMLTags"].ToString());
3459

    
3460
                                connection.ExecuteNonQuery(cmd, txn);
3461
                            }
3462

    
3463
                            //Pipeline
3464
                            query = $"DELETE FROM {PSN_PIPELINE}";
3465
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3466
                            foreach (DataRow row in item.PipeLine.Rows)
3467
                            {
3468
                                query = $"INSERT INTO {PSN_PIPELINE} (OID, PipeSystem_OID, FLUID, PMC, SEQNUMBER, INSULATION, FROM_DATA, TO_DATA, Unit) VALUES (@OID, @PipeSystem_OID, @FLUID, @PMC, @SEQNUMBER, @INSULATION, @FROM_DATA, @TO_DATA, @Unit)";
3469
                                var cmd = connection.GetSqlStringCommand(query);
3470
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
3471
                                AddWithValue(cmd, "@PipeSystem_OID", row["PipeSystem_OID"].ToString());
3472
                                AddWithValue(cmd, "@FLUID", row["FLUID"].ToString());
3473
                                AddWithValue(cmd, "@PMC", row["PMC"].ToString());
3474
                                AddWithValue(cmd, "@SEQNUMBER", row["SEQNUMBER"].ToString());
3475
                                AddWithValue(cmd, "@INSULATION", row["INSULATION"].ToString());
3476
                                AddWithValue(cmd, "@FROM_DATA", row["FROM_DATA"].ToString());
3477
                                AddWithValue(cmd, "@TO_DATA", row["TO_DATA"].ToString());
3478
                                AddWithValue(cmd, "@Unit", row["Unit"].ToString());
3479
                                connection.ExecuteNonQuery(cmd, txn);
3480
                            }
3481

    
3482
                            //PipeSystem
3483
                            query = $"DELETE FROM {PSN_PIPESYSTEM}";
3484
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3485
                            foreach (DataRow row in item.PipeSystem.Rows)
3486
                            {
3487
                                query = $"INSERT INTO {PSN_PIPESYSTEM} (OID, DESCRIPTION, FLUID, PMC, PipeLineQty, GroundLevel) VALUES (@OID, @DESCRIPTION, @FLUID, @PMC, @PipeLineQty, @GroundLevel)";
3488
                                var cmd = connection.GetSqlStringCommand(query);
3489
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
3490
                                AddWithValue(cmd, "@DESCRIPTION", row["DESCRIPTION"].ToString());
3491
                                AddWithValue(cmd, "@FLUID", row["FLUID"].ToString());
3492
                                AddWithValue(cmd, "@PMC", row["PMC"].ToString());
3493
                                AddWithValue(cmd, "@PipeLineQty", row["PipeLineQty"].ToString());
3494
                                AddWithValue(cmd, "@GroundLevel", row["GroundLevel"].ToString());
3495
                                connection.ExecuteNonQuery(cmd, txn);
3496
                            }
3497

    
3498
                            //Header Setting
3499
                            query = $"DELETE FROM {PSN_HEADER_SETTING}";
3500
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3501

    
3502
                            foreach (HeaderInfo headerInfo in headerInfos)
3503
                            {
3504
                                foreach (HeaderItem itemheader in headerInfo.HeaderItems)
3505
                                {
3506
                                    query = $"INSERT INTO {PSN_HEADER_SETTING} (GROUP_ID, DESCRIPTION, [INDEX], NAME) VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";
3507
                                    var cmd = connection.GetSqlStringCommand(query);
3508
                                    AddWithValue(cmd, "@GROUP_ID", headerInfo.UID);
3509
                                    AddWithValue(cmd, "@DESCRIPTION", headerInfo.Description);
3510
                                    AddWithValue(cmd, "@INDEX", itemheader.Index);
3511
                                    AddWithValue(cmd, "@NAME", itemheader.Name);
3512
                                    connection.ExecuteNonQuery(cmd, txn);
3513
                                }
3514
                            }
3515

    
3516
                            //Vent/Drain Setting
3517
                            query = $"DELETE FROM {PSN_VENTDRAIN_SETTING}";
3518
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3519

    
3520
                            foreach (VentDrainInfo ventDrainInfo in ventDrainInfos)
3521
                            {
3522
                                foreach (VentDrainItem itemventdrain in ventDrainInfo.VentDrainItems)
3523
                                {
3524
                                    query = $"INSERT INTO {PSN_VENTDRAIN_SETTING} (GROUP_ID, DESCRIPTION, [INDEX], NAME) VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";
3525
                                    var cmd = connection.GetSqlStringCommand(query);
3526
                                    AddWithValue(cmd, "@GROUP_ID", ventDrainInfo.UID);
3527
                                    AddWithValue(cmd, "@DESCRIPTION", ventDrainInfo.Description);
3528
                                    AddWithValue(cmd, "@INDEX", itemventdrain.Index);
3529
                                    AddWithValue(cmd, "@NAME", itemventdrain.Name);
3530
                                    connection.ExecuteNonQuery(cmd, txn);
3531
                                }
3532
                            }
3533

    
3534
                            //Keyword Setting
3535
                            query = $"DELETE FROM {PSN_TRANSFORMKEYWORD_SETTING}";
3536
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3537

    
3538
                            foreach (KeywordItem itemKeyword in keywordItems)
3539
                            {
3540
                                query = $"INSERT INTO {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX], NAME, KEYWORD) VALUES (@INDEX, @NAME, @KEYWORD)";
3541
                                var cmd = connection.GetSqlStringCommand(query);
3542
                                AddWithValue(cmd, "@INDEX", itemKeyword.Index);
3543
                                AddWithValue(cmd, "@NAME", itemKeyword.Name);
3544
                                AddWithValue(cmd, "@KEYWORD", itemKeyword.Keyword);
3545
                                connection.ExecuteNonQuery(cmd, txn);
3546
                            }
3547

    
3548
                            //FulidCode
3549
                            query = $"DELETE FROM {PSN_FLUIDCODE}";
3550
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3551

    
3552
                            foreach (DataRow row in dtFluidCode.Rows)
3553
                            {
3554
                                query = $"INSERT INTO {PSN_FLUIDCODE} (UID, Code, Description, Condition, Remarks, GroundLevel) VALUES (@UID, @Code, @Description, @Condition, @Remarks, @GroundLevel)";
3555
                                var cmd = connection.GetSqlStringCommand(query);
3556
                                cmd.Parameters.Clear();
3557

    
3558
                                {
3559
                                    var param = cmd.CreateParameter();
3560
                                    param.ParameterName = "@UID";
3561
                                    param.Value = row["UID"].ToString();
3562
                                    cmd.Parameters.Add(param);
3563
                                }
3564

    
3565
                                {
3566
                                    var param = cmd.CreateParameter();
3567
                                    param.ParameterName = "@Code";
3568
                                    param.Value = row["Code"].ToString();
3569
                                    cmd.Parameters.Add(param);
3570
                                }
3571

    
3572
                                {
3573
                                    var param = cmd.CreateParameter();
3574
                                    param.ParameterName = "@Description";
3575
                                    param.Value = row["Description"].ToString();
3576
                                    cmd.Parameters.Add(param);
3577
                                }
3578

    
3579
                                {
3580
                                    var param = cmd.CreateParameter();
3581
                                    param.ParameterName = "@Condition";
3582
                                    param.Value = row["Condition"].ToString();
3583
                                    cmd.Parameters.Add(param);
3584
                                }
3585

    
3586
                                {
3587
                                    var param = cmd.CreateParameter();
3588
                                    param.ParameterName = "@Remarks";
3589
                                    param.Value = row["Remarks"].ToString();
3590
                                    cmd.Parameters.Add(param);
3591
                                }
3592

    
3593
                                {
3594
                                    var param = cmd.CreateParameter();
3595
                                    param.ParameterName = "@GroundLevel";
3596
                                    param.Value = row["GroundLevel"].ToString();
3597
                                    cmd.Parameters.Add(param);
3598
                                }
3599

    
3600
                                connection.ExecuteNonQuery(cmd, txn);
3601
                            }
3602

    
3603
                            //PMC
3604
                            query = $"DELETE FROM {PSN_PIPINGMATLCLASS}";
3605
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3606

    
3607
                            foreach (DataRow row in dtPMC.Rows)
3608
                            {
3609
                                query = $"INSERT INTO {PSN_PIPINGMATLCLASS} ( UID, Priority, Code, Description, Condition, Remarks, GroundLevel) VALUES (@UID, @Priority, @Code, @Description, @Condition, @Remarks, @GroundLevel)";
3610
                                var cmd = connection.GetSqlStringCommand(query);
3611
                                cmd.Parameters.Clear();
3612

    
3613
                                {
3614
                                    var param = cmd.CreateParameter();
3615
                                    param.ParameterName = "@UID";
3616
                                    param.Value = row["UID"].ToString();
3617
                                    cmd.Parameters.Add(param);
3618
                                }
3619

    
3620
                                {
3621
                                    var param = cmd.CreateParameter();
3622
                                    param.ParameterName = "@Priority";
3623
                                    param.Value = row["Priority"].ToString();
3624
                                    cmd.Parameters.Add(param);
3625
                                }
3626

    
3627
                                {
3628
                                    var param = cmd.CreateParameter();
3629
                                    param.ParameterName = "@Code";
3630
                                    param.Value = row["Code"].ToString();
3631
                                    cmd.Parameters.Add(param);
3632
                                }
3633

    
3634
                                {
3635
                                    var param = cmd.CreateParameter();
3636
                                    param.ParameterName = "@Description";
3637
                                    param.Value = row["Description"].ToString();
3638
                                    cmd.Parameters.Add(param);
3639
                                }
3640

    
3641
                                {
3642
                                    var param = cmd.CreateParameter();
3643
                                    param.ParameterName = "@Condition";
3644
                                    param.Value = row["Condition"].ToString();
3645
                                    cmd.Parameters.Add(param);
3646
                                }
3647

    
3648
                                {
3649
                                    var param = cmd.CreateParameter();
3650
                                    param.ParameterName = "@Remarks";
3651
                                    param.Value = row["Remarks"].ToString();
3652
                                    cmd.Parameters.Add(param);
3653
                                }
3654

    
3655
                                {
3656
                                    var param = cmd.CreateParameter();
3657
                                    param.ParameterName = "@GroundLevel";
3658
                                    param.Value = row["GroundLevel"].ToString();
3659
                                    cmd.Parameters.Add(param);
3660
                                }
3661

    
3662
                                connection.ExecuteNonQuery(cmd, txn);
3663
                            }
3664

    
3665
                            //Insulation
3666
                            query = $"DELETE FROM {PSN_INSULATIONPURPOSE}";
3667
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3668

    
3669
                            foreach (DataRow row in dtInsulation.Rows)
3670
                            {
3671
                                query = $"INSERT INTO {PSN_INSULATIONPURPOSE} (UID, Code, Description, Remarks) VALUES (@UID, @Code, @Description, @Remarks)";
3672
                                var cmd = connection.GetSqlStringCommand(query);
3673
                                cmd.Parameters.Clear();
3674

    
3675
                                {
3676
                                    var param = cmd.CreateParameter();
3677
                                    param.ParameterName = "@UID";
3678
                                    param.Value = row["UID"].ToString();
3679
                                    cmd.Parameters.Add(param);
3680
                                }
3681

    
3682
                                {
3683
                                    var param = cmd.CreateParameter();
3684
                                    param.ParameterName = "@Code";
3685
                                    param.Value = row["Code"].ToString();
3686
                                    cmd.Parameters.Add(param);
3687
                                }
3688

    
3689
                                {
3690
                                    var param = cmd.CreateParameter();
3691
                                    param.ParameterName = "@Description";
3692
                                    param.Value = row["Description"].ToString();
3693
                                    cmd.Parameters.Add(param);
3694
                                }
3695

    
3696
                                {
3697
                                    var param = cmd.CreateParameter();
3698
                                    param.ParameterName = "@Remarks";
3699
                                    param.Value = row["Remarks"].ToString();
3700
                                    cmd.Parameters.Add(param);
3701
                                }
3702

    
3703
                                connection.ExecuteNonQuery(cmd, txn);
3704
                            }
3705

    
3706
                            //Topology Rule
3707
                            query = $"DELETE FROM {PSN_TOPOLOGY_RULE}";
3708
                            var cmdtopology = connection.GetSqlStringCommand(query);
3709
                            //connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3710
                            connection.ExecuteNonQuery(cmdtopology, txn);
3711

    
3712
                            foreach (DataRow row in dtTopologyRule.Rows)
3713
                            {
3714
                                query = $"INSERT INTO {PSN_TOPOLOGY_RULE} (UID) VALUES (@UID)";
3715
                                cmdtopology = connection.GetSqlStringCommand(query);
3716
                                AddWithValue(cmdtopology, "@UID", row["UID"].ToString());
3717
                                connection.ExecuteNonQuery(cmdtopology, txn);
3718
                            }
3719

    
3720
                            //valve grouping
3721
                            query = $"DELETE FROM {PSN_VALVEGROUP_SETTING}";
3722
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3723

    
3724
                            foreach (DataRow row in dtvalvegrouping.Rows)
3725
                            {
3726
                                query = $"INSERT INTO {PSN_VALVEGROUP_SETTING} (OID, GroupType, TagIdentifier, AttributeName, SppidSymbolName) " +
3727
                                    $"VALUES (@OID, @GroupType, @TagIdentifier, @AttributeName, @SppidSymbolName)";
3728
                                var cmd = connection.GetSqlStringCommand(query);
3729
                                AddWithValue(cmd, "@OID", row["OID"].ToString());
3730
                                AddWithValue(cmd, "@GroupType", row["GroupType"].ToString());
3731
                                AddWithValue(cmd, "@TagIdentifier", row["TagIdentifier"].ToString());
3732
                                AddWithValue(cmd, "@AttributeName", row["AttributeName"].ToString());
3733
                                AddWithValue(cmd, "@SppidSymbolName", row["SppidSymbolName"].ToString());
3734
                                connection.ExecuteNonQuery(cmd, txn);
3735
                            }
3736

    
3737
                            //no pocket Setting
3738
                            query = $"DELETE FROM {PSN_NOPOCKETSETTING}";
3739
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3740

    
3741
                            foreach (DataRow row in dtnopocket.Rows)
3742
                            {
3743
                                query = $"INSERT INTO {PSN_NOPOCKETSETTING} ([INDEX], [TYPE], [NAME]) VALUES (@INDEX, @TYPE, @NAME)";
3744
                                var cmd = connection.GetSqlStringCommand(query);
3745
                                AddWithValue(cmd, "@INDEX", row["INDEX"].ToString());
3746
                                AddWithValue(cmd, "@TYPE", row["TYPE"].ToString());
3747
                                AddWithValue(cmd, "@NAME", row["NAME"].ToString());
3748
                                connection.ExecuteNonQuery(cmd, txn);
3749
                            }
3750

    
3751
                            //air fin cooler Setting
3752
                            query = $"DELETE FROM {PSN_AIRFINCOOLERSETTING}";
3753
                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3754

    
3755
                            foreach (DataRow row in dtnopocket.Rows)
3756
                            {
3757
                                query = $"INSERT INTO {PSN_AIRFINCOOLERSETTING} ([INDEX], [TYPE], [NAME]) VALUES (@INDEX, @TYPE, @NAME)";
3758
                                var cmd = connection.GetSqlStringCommand(query);
3759
                                AddWithValue(cmd, "@INDEX", row["INDEX"].ToString());
3760
                                AddWithValue(cmd, "@TYPE", row["TYPE"].ToString());
3761
                                AddWithValue(cmd, "@NAME", row["NAME"].ToString());
3762
                                connection.ExecuteNonQuery(cmd, txn);
3763
                            }
3764

    
3765

    
3766
                            txn.Commit();
3767
                        }
3768
                        catch (Exception ex)
3769
                        {
3770
                            txn.Rollback();
3771
                            result = false;
3772
                        }
3773
                    }
3774
                }
3775
                catch (Exception ex)
3776
                {
3777
                    System.Windows.Forms.MessageBox.Show(ex.Message);
3778
                    result = false;
3779
                }
3780
            }
3781

    
3782
            return result;
3783
        }
3784

    
3785
        public static bool CreatePSN_COMMON()
3786
        {
3787
            AnotherID2Info id2Info = AnotherID2Info.GetInstance();
3788

    
3789
            bool result = true;
3790
            using (IAbstractDatabase connection = id2Info.CreateConnection())
3791
            {
3792
                try
3793
                {
3794
                    using (var txn = connection.BeginTransaction())
3795
                    {
3796
                        try
3797
                        {
3798

    
3799
                            var query = $"If(db_id(N'" + PSN_COMMON + "') IS NULL) CREATE DATABASE [" + PSN_COMMON + "]";
3800
                            if (id2Info.ID2DBType == AnotherID2DB_Type.MSSQL)
3801
                            {
3802
                                connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);
3803
                            }
3804

    
3805
                            txn.Commit();
3806
                        }
3807
                        catch (Exception ex)
3808
                        {
3809
                            txn.Rollback();
3810
                            result = false;
3811
                        }
3812
                    }
3813
                }
3814
                catch (Exception ex)
3815
                {
3816
                    System.Windows.Forms.MessageBox.Show(ex.Message);
3817
                    result = false;
3818
                }
3819
            }
3820

    
3821
            return result;
3822
        }
3823
    }
3824
}
3825

    
3826