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 |