hytos / DTI_PID / ID2PSN / DB.cs @ 8f24b438
이력 | 보기 | 이력해설 | 다운로드 (43.9 KB)
1 | 0dae5645 | gaqhf | using System; |
---|---|---|---|
2 | using System.Collections.Generic; |
||
3 | using System.Linq; |
||
4 | using System.Text; |
||
5 | using System.Threading.Tasks; |
||
6 | 6b9e7a56 | gaqhf | using System.Data.SQLite; |
7 | using System.Data; |
||
8 | using System.Globalization; |
||
9 | using System.Data.SQLite; |
||
10 | using System.Data; |
||
11 | 8f24b438 | gaqhf | using System.Text.RegularExpressions; |
12 | 0dae5645 | gaqhf | |
13 | namespace ID2PSN |
||
14 | { |
||
15 | 6b9e7a56 | gaqhf | public static class DB |
16 | 0dae5645 | gaqhf | { |
17 | 6b9e7a56 | gaqhf | const string PSN_TOPOLOGY_RULE = "T_PSN_TOPOLOGY_RULE"; |
18 | const string PSN_HEADER_SETTING = "T_PSN_HEADER_SETTING"; |
||
19 | 8f24b438 | gaqhf | const string PSN_PATHITEMS = "SPPIDPathItem"; |
20 | const string PSN_SEQUENCEDATA = "SPPIDSequenceData"; |
||
21 | const string PSN_PIPESYSTEMNETWORK = "SPPIDPipeSystemNetwork"; |
||
22 | const string PSN_EQUIPMENT = "SPPIDEquipment"; |
||
23 | const string PSN_NOZZLE = "SPPIDNozzle"; |
||
24 | const string PSN_FLUIDCODE = "SPPIDFluidCode"; |
||
25 | const string PSN_PIPINGMATLCLASS = "SPPIDPipingMatClass"; |
||
26 | 6b9e7a56 | gaqhf | public static bool ConnTestAndCreateTable() |
27 | { |
||
28 | bool result = false; |
||
29 | ID2Info id2Info = ID2Info.GetInstance(); |
||
30 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, @"Data Source = {0}", id2Info.DBFilePath), true)) |
||
31 | { |
||
32 | try |
||
33 | { |
||
34 | connection.Open(); |
||
35 | if (connection.State == ConnectionState.Open) |
||
36 | { |
||
37 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
38 | { |
||
39 | cmd.CommandText = "SELECT NAME FROM sqlite_master WHERE type='table'"; |
||
40 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
41 | using (DataTable dt = new DataTable()) |
||
42 | { |
||
43 | dt.Load(dr); |
||
44 | 0dae5645 | gaqhf | |
45 | 6b9e7a56 | gaqhf | if (dt.Select(string.Format("NAME = '{0}'", PSN_HEADER_SETTING)).Length == 0) |
46 | { |
||
47 | cmd.CommandText = string.Format("CREATE TABLE {0} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)", PSN_HEADER_SETTING); |
||
48 | cmd.ExecuteNonQuery(); |
||
49 | } |
||
50 | if (dt.Select(string.Format("NAME = '{0}'", PSN_TOPOLOGY_RULE)).Length == 0) |
||
51 | { |
||
52 | cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT)", PSN_TOPOLOGY_RULE); |
||
53 | cmd.ExecuteNonQuery(); |
||
54 | } |
||
55 | if (dt.Select(string.Format("NAME = '{0}'", PSN_PATHITEMS)).Length == 0) |
||
56 | { |
||
57 | cmd.CommandText = string.Format("CREATE TABLE {0} (OID TEXT, SequenceData_OID TEXT, TopologySet_OID TEXT, BranchTopologySet_OID TEXT, PipeLine_OID TEXT, ITEMNAME TEXT, ITEMTAG TEXT, TYPE TEXT, PIDNAME TEXT, NPD TEXT, PipeSystemNetwork_OID TEXT, PipeRun_OID TEXT)", PSN_PATHITEMS); |
||
58 | cmd.ExecuteNonQuery(); |
||
59 | } |
||
60 | if (dt.Select(string.Format("NAME = '{0}'", PSN_SEQUENCEDATA)).Length == 0) |
||
61 | { |
||
62 | cmd.CommandText = string.Format("CREATE TABLE {0} (OID TEXT, SERIALNUMBER TEXT, PathItem_OID TEXT, TopologySet_OID_Key TEXT)", PSN_SEQUENCEDATA); |
||
63 | cmd.ExecuteNonQuery(); |
||
64 | } |
||
65 | if (dt.Select(string.Format("NAME = '{0}'", PSN_PIPESYSTEMNETWORK)).Length == 0) |
||
66 | { |
||
67 | cmd.CommandText = string.Format("CREATE TABLE {0} (OID TEXT, Type TEXT, OrderNumber TEXT, Pipeline_OID TEXT, FROM_DATA TEXT, TO_DATA TEXT, TopologySet_OID_Key TEXT, PSNRevisionNumber TEXT, PathOID TEXT, PBS TEXT, PIDDrawings TEXT)", PSN_PIPESYSTEMNETWORK); |
||
68 | cmd.ExecuteNonQuery(); |
||
69 | } |
||
70 | |||
71 | if (dt.Select(string.Format("NAME = '{0}'", PSN_EQUIPMENT)).Length == 0) |
||
72 | { |
||
73 | cmd.CommandText = string.Format("CREATE TABLE {0} (OID TEXT, ITEMTAG TEXT, XCOORDS TEXT, YCOORDS TEXT)", PSN_EQUIPMENT); |
||
74 | cmd.ExecuteNonQuery(); |
||
75 | } |
||
76 | if (dt.Select(string.Format("NAME = '{0}'", PSN_NOZZLE)).Length == 0) |
||
77 | { |
||
78 | cmd.CommandText = string.Format("CREATE TABLE {0} (OID TEXT, ITEMTAG TEXT, XCOORDS TEXT, YCOORDS TEXT, Equipment_OID TEXT, FLUID TEXT, NPD TEXT, ROTATION TEXT, FlowDirection TEXT)", PSN_NOZZLE); |
||
79 | cmd.ExecuteNonQuery(); |
||
80 | } |
||
81 | if (dt.Select(string.Format("NAME = '{0}'", PSN_FLUIDCODE)).Length == 0) |
||
82 | { |
||
83 | 8f24b438 | gaqhf | cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT, Code TEXT, Description TEXT, Condition TEXT, Remarks TEXT, GroundLevel TEXT)", PSN_FLUIDCODE); |
84 | 6b9e7a56 | gaqhf | cmd.ExecuteNonQuery(); |
85 | } |
||
86 | if (dt.Select(string.Format("NAME = '{0}'", PSN_PIPINGMATLCLASS)).Length == 0) |
||
87 | { |
||
88 | 8f24b438 | gaqhf | cmd.CommandText = string.Format("CREATE TABLE {0} (UID TEXT, Priority TEXT, Code TEXT, Description TEXT, Condition TEXT, Remarks TEXT, GroundLevel TEXT)", PSN_PIPINGMATLCLASS); |
89 | 6b9e7a56 | gaqhf | cmd.ExecuteNonQuery(); |
90 | } |
||
91 | } |
||
92 | } |
||
93 | result = true; |
||
94 | } |
||
95 | connection.Close(); |
||
96 | } |
||
97 | catch (Exception ex) |
||
98 | { |
||
99 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
100 | } |
||
101 | finally |
||
102 | { |
||
103 | connection.Dispose(); |
||
104 | } |
||
105 | } |
||
106 | return result; |
||
107 | } |
||
108 | |||
109 | public static DataTable SelectHeaderSetting() |
||
110 | { |
||
111 | DataTable dt = new DataTable(); |
||
112 | ID2Info id2Info = ID2Info.GetInstance(); |
||
113 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
114 | { |
||
115 | try |
||
116 | { |
||
117 | connection.Open(); |
||
118 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
119 | { |
||
120 | cmd.CommandText = string.Format(@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {0};", PSN_HEADER_SETTING); |
||
121 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
122 | dt.Load(dr); |
||
123 | } |
||
124 | connection.Close(); |
||
125 | } |
||
126 | catch (Exception ex) |
||
127 | { |
||
128 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
129 | } |
||
130 | finally |
||
131 | { |
||
132 | connection.Dispose(); |
||
133 | } |
||
134 | } |
||
135 | return dt; |
||
136 | } |
||
137 | |||
138 | public static bool SaveHeaderSetting(List<HeaderInfo> headerInfos) |
||
139 | { |
||
140 | ID2Info id2Info = ID2Info.GetInstance(); |
||
141 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
142 | { |
||
143 | try |
||
144 | { |
||
145 | connection.Open(); |
||
146 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
147 | { |
||
148 | cmd.CommandText = string.Format("DELETE FROM {0}", PSN_HEADER_SETTING); |
||
149 | cmd.ExecuteNonQuery(); |
||
150 | |||
151 | foreach (HeaderInfo headerInfo in headerInfos) |
||
152 | { |
||
153 | foreach (HeaderItem item in headerInfo.HeaderItems) |
||
154 | { |
||
155 | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)", PSN_HEADER_SETTING); |
||
156 | cmd.Parameters.Clear(); |
||
157 | cmd.Parameters.AddWithValue("@GROUP_ID", headerInfo.UID); |
||
158 | cmd.Parameters.AddWithValue("@DESCRIPTION", headerInfo.Description); |
||
159 | cmd.Parameters.AddWithValue("@INDEX", item.Index); |
||
160 | cmd.Parameters.AddWithValue("@NAME", item.Name); |
||
161 | cmd.ExecuteNonQuery(); |
||
162 | } |
||
163 | } |
||
164 | } |
||
165 | connection.Close(); |
||
166 | } |
||
167 | catch (Exception ex) |
||
168 | { |
||
169 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
170 | return false; |
||
171 | } |
||
172 | finally |
||
173 | { |
||
174 | connection.Dispose(); |
||
175 | } |
||
176 | } |
||
177 | return true; |
||
178 | } |
||
179 | |||
180 | public static DataTable SelectOPCRelations() |
||
181 | { |
||
182 | DataTable dt = new DataTable(); |
||
183 | ID2Info id2Info = ID2Info.GetInstance(); |
||
184 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
185 | { |
||
186 | try |
||
187 | { |
||
188 | connection.Open(); |
||
189 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
190 | { |
||
191 | cmd.CommandText = "SELECT * FROM OPCRelations;"; |
||
192 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
193 | dt.Load(dr); |
||
194 | } |
||
195 | connection.Close(); |
||
196 | } |
||
197 | catch (Exception ex) |
||
198 | { |
||
199 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
200 | } |
||
201 | finally |
||
202 | { |
||
203 | connection.Dispose(); |
||
204 | } |
||
205 | } |
||
206 | return dt; |
||
207 | } |
||
208 | |||
209 | public static DataTable SelectDrawings() |
||
210 | { |
||
211 | DataTable dt = new DataTable(); |
||
212 | ID2Info id2Info = ID2Info.GetInstance(); |
||
213 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
214 | { |
||
215 | try |
||
216 | { |
||
217 | connection.Open(); |
||
218 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
219 | { |
||
220 | cmd.CommandText = "SELECT * FROM Drawings;"; |
||
221 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
222 | dt.Load(dr); |
||
223 | } |
||
224 | connection.Close(); |
||
225 | } |
||
226 | catch (Exception ex) |
||
227 | { |
||
228 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
229 | } |
||
230 | finally |
||
231 | { |
||
232 | connection.Dispose(); |
||
233 | } |
||
234 | } |
||
235 | return dt; |
||
236 | } |
||
237 | |||
238 | public static DataTable SelectLineProperties() |
||
239 | { |
||
240 | DataTable dt = new DataTable(); |
||
241 | ID2Info id2Info = ID2Info.GetInstance(); |
||
242 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
243 | { |
||
244 | try |
||
245 | { |
||
246 | connection.Open(); |
||
247 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
248 | { |
||
249 | cmd.CommandText = "SELECT * FROM LineProperties;"; |
||
250 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
251 | dt.Load(dr); |
||
252 | } |
||
253 | connection.Close(); |
||
254 | } |
||
255 | catch (Exception ex) |
||
256 | { |
||
257 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
258 | } |
||
259 | finally |
||
260 | { |
||
261 | connection.Dispose(); |
||
262 | } |
||
263 | } |
||
264 | return dt; |
||
265 | } |
||
266 | public static DataTable SelectTopologyRule() |
||
267 | { |
||
268 | DataTable dt = new DataTable(); |
||
269 | ID2Info id2Info = ID2Info.GetInstance(); |
||
270 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
271 | { |
||
272 | try |
||
273 | { |
||
274 | connection.Open(); |
||
275 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
276 | { |
||
277 | cmd.CommandText = string.Format(@"SELECT * FROM {0};", PSN_TOPOLOGY_RULE); |
||
278 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
279 | dt.Load(dr); |
||
280 | } |
||
281 | connection.Close(); |
||
282 | } |
||
283 | catch (Exception ex) |
||
284 | { |
||
285 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
286 | } |
||
287 | finally |
||
288 | { |
||
289 | connection.Dispose(); |
||
290 | } |
||
291 | } |
||
292 | return dt; |
||
293 | } |
||
294 | public static bool SaveTopologyRule(DataTable dt) |
||
295 | { |
||
296 | ID2Info id2Info = ID2Info.GetInstance(); |
||
297 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
298 | { |
||
299 | try |
||
300 | { |
||
301 | connection.Open(); |
||
302 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
303 | { |
||
304 | cmd.CommandText = string.Format("DELETE FROM {0}", PSN_TOPOLOGY_RULE); |
||
305 | cmd.ExecuteNonQuery(); |
||
306 | |||
307 | foreach (DataRow row in dt.Rows) |
||
308 | { |
||
309 | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@UID)", PSN_TOPOLOGY_RULE); |
||
310 | cmd.Parameters.Clear(); |
||
311 | cmd.Parameters.AddWithValue("@UID", row["NAME"].ToString()); |
||
312 | cmd.ExecuteNonQuery(); |
||
313 | } |
||
314 | } |
||
315 | connection.Close(); |
||
316 | } |
||
317 | catch (Exception ex) |
||
318 | { |
||
319 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
320 | return false; |
||
321 | } |
||
322 | finally |
||
323 | { |
||
324 | connection.Dispose(); |
||
325 | } |
||
326 | } |
||
327 | return true; |
||
328 | } |
||
329 | |||
330 | public static DataTable SelectFluidCode() |
||
331 | { |
||
332 | DataTable dt = new DataTable(); |
||
333 | ID2Info id2Info = ID2Info.GetInstance(); |
||
334 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
335 | { |
||
336 | try |
||
337 | { |
||
338 | connection.Open(); |
||
339 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
340 | { |
||
341 | cmd.CommandText = "SELECT * FROM FluidCode;"; |
||
342 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
343 | dt.Load(dr); |
||
344 | } |
||
345 | connection.Close(); |
||
346 | } |
||
347 | catch (Exception ex) |
||
348 | { |
||
349 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
350 | } |
||
351 | finally |
||
352 | { |
||
353 | connection.Dispose(); |
||
354 | } |
||
355 | } |
||
356 | return dt; |
||
357 | } |
||
358 | public static DataTable SelectPipingMaterialsClass() |
||
359 | { |
||
360 | DataTable dt = new DataTable(); |
||
361 | ID2Info id2Info = ID2Info.GetInstance(); |
||
362 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
363 | { |
||
364 | try |
||
365 | { |
||
366 | connection.Open(); |
||
367 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
368 | { |
||
369 | cmd.CommandText = "SELECT * FROM PipingMaterialsClass;"; |
||
370 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
371 | dt.Load(dr); |
||
372 | } |
||
373 | connection.Close(); |
||
374 | } |
||
375 | catch (Exception ex) |
||
376 | { |
||
377 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
378 | } |
||
379 | finally |
||
380 | { |
||
381 | connection.Dispose(); |
||
382 | } |
||
383 | } |
||
384 | return dt; |
||
385 | } |
||
386 | |||
387 | public static DataTable SelectPSNFluidCode() |
||
388 | { |
||
389 | DataTable dt = new DataTable(); |
||
390 | ID2Info id2Info = ID2Info.GetInstance(); |
||
391 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
392 | { |
||
393 | try |
||
394 | { |
||
395 | connection.Open(); |
||
396 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
397 | { |
||
398 | cmd.CommandText = string.Format("SELECT * FROM {0}", PSN_FLUIDCODE); |
||
399 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
400 | dt.Load(dr); |
||
401 | } |
||
402 | connection.Close(); |
||
403 | } |
||
404 | catch (Exception ex) |
||
405 | { |
||
406 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
407 | } |
||
408 | finally |
||
409 | { |
||
410 | connection.Dispose(); |
||
411 | } |
||
412 | } |
||
413 | return dt; |
||
414 | } |
||
415 | public static DataTable SelectPSNPIPINGMATLCLASS() |
||
416 | { |
||
417 | DataTable dt = new DataTable(); |
||
418 | ID2Info id2Info = ID2Info.GetInstance(); |
||
419 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
420 | { |
||
421 | try |
||
422 | { |
||
423 | connection.Open(); |
||
424 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
425 | { |
||
426 | cmd.CommandText = string.Format("SELECT * FROM {0}", PSN_PIPINGMATLCLASS); |
||
427 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
428 | dt.Load(dr); |
||
429 | } |
||
430 | connection.Close(); |
||
431 | } |
||
432 | catch (Exception ex) |
||
433 | { |
||
434 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
435 | } |
||
436 | finally |
||
437 | { |
||
438 | connection.Dispose(); |
||
439 | } |
||
440 | } |
||
441 | return dt; |
||
442 | } |
||
443 | public static DataTable SelectNominalDiameter() |
||
444 | { |
||
445 | DataTable dt = new DataTable(); |
||
446 | ID2Info id2Info = ID2Info.GetInstance(); |
||
447 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
448 | { |
||
449 | try |
||
450 | { |
||
451 | connection.Open(); |
||
452 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
453 | { |
||
454 | cmd.CommandText = "SELECT * FROM NominalDiameter;"; |
||
455 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
456 | dt.Load(dr); |
||
457 | } |
||
458 | connection.Close(); |
||
459 | } |
||
460 | catch (Exception ex) |
||
461 | { |
||
462 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
463 | } |
||
464 | finally |
||
465 | { |
||
466 | connection.Dispose(); |
||
467 | } |
||
468 | } |
||
469 | return dt; |
||
470 | } |
||
471 | |||
472 | public static bool SavePSNFluidCode(DataTable dt) |
||
473 | { |
||
474 | ID2Info id2Info = ID2Info.GetInstance(); |
||
475 | |||
476 | bool result = true; |
||
477 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
478 | { |
||
479 | try |
||
480 | { |
||
481 | connection.Open(); |
||
482 | |||
483 | using (SQLiteTransaction transaction = connection.BeginTransaction()) |
||
484 | { |
||
485 | try |
||
486 | { |
||
487 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
488 | { |
||
489 | cmd.CommandText = string.Format("DELETE FROM {0}", PSN_FLUIDCODE); |
||
490 | cmd.ExecuteNonQuery(); |
||
491 | |||
492 | foreach (DataRow row in dt.Rows) |
||
493 | { |
||
494 | 8f24b438 | gaqhf | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@UID, @Code, @Description, @Condition, @Remarks, @GroundLevel)", PSN_FLUIDCODE); |
495 | 6b9e7a56 | gaqhf | cmd.Parameters.Clear(); |
496 | cmd.Parameters.AddWithValue("@UID", row["UID"].ToString()); |
||
497 | cmd.Parameters.AddWithValue("@Code", row["Code"].ToString()); |
||
498 | cmd.Parameters.AddWithValue("@Description", row["Description"].ToString()); |
||
499 | cmd.Parameters.AddWithValue("@Condition", row["Condition"].ToString()); |
||
500 | 8f24b438 | gaqhf | cmd.Parameters.AddWithValue("@Remarks", row["Remarks"].ToString()); |
501 | 6b9e7a56 | gaqhf | cmd.Parameters.AddWithValue("@GroundLevel", row["GroundLevel"].ToString()); |
502 | cmd.ExecuteNonQuery(); |
||
503 | } |
||
504 | } |
||
505 | transaction.Commit(); |
||
506 | connection.Close(); |
||
507 | } |
||
508 | catch (Exception ex) |
||
509 | { |
||
510 | transaction.Rollback(); |
||
511 | result = false; |
||
512 | } |
||
513 | finally |
||
514 | { |
||
515 | transaction.Dispose(); |
||
516 | } |
||
517 | } |
||
518 | } |
||
519 | catch (Exception ex) |
||
520 | { |
||
521 | System.Windows.Forms.MessageBox.Show(ex.Message); |
||
522 | result = false; |
||
523 | } |
||
524 | finally |
||
525 | { |
||
526 | connection.Dispose(); |
||
527 | } |
||
528 | } |
||
529 | |||
530 | return result; |
||
531 | } |
||
532 | |||
533 | public static bool SavePSNPMC(DataTable dt) |
||
534 | { |
||
535 | ID2Info id2Info = ID2Info.GetInstance(); |
||
536 | |||
537 | bool result = true; |
||
538 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
539 | { |
||
540 | try |
||
541 | { |
||
542 | connection.Open(); |
||
543 | |||
544 | using (SQLiteTransaction transaction = connection.BeginTransaction()) |
||
545 | { |
||
546 | try |
||
547 | { |
||
548 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
549 | { |
||
550 | cmd.CommandText = string.Format("DELETE FROM {0}", PSN_PIPINGMATLCLASS); |
||
551 | cmd.ExecuteNonQuery(); |
||
552 | |||
553 | foreach (DataRow row in dt.Rows) |
||
554 | { |
||
555 | 8f24b438 | gaqhf | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@UID, @Priority, @Code, @Description, @Condition, @Remarks, @GroundLevel)", PSN_PIPINGMATLCLASS); |
556 | 6b9e7a56 | gaqhf | cmd.Parameters.Clear(); |
557 | cmd.Parameters.AddWithValue("@UID", row["UID"].ToString()); |
||
558 | cmd.Parameters.AddWithValue("@Priority", row["Priority"].ToString()); |
||
559 | cmd.Parameters.AddWithValue("@Code", row["Code"].ToString()); |
||
560 | cmd.Parameters.AddWithValue("@Description", row["Description"].ToString()); |
||
561 | cmd.Parameters.AddWithValue("@Condition", row["Condition"].ToString()); |
||
562 | 8f24b438 | gaqhf | cmd.Parameters.AddWithValue("@Remarks", row["Remarks"].ToString()); |
563 | 6b9e7a56 | gaqhf | cmd.Parameters.AddWithValue("@GroundLevel", row["GroundLevel"].ToString()); |
564 | cmd.ExecuteNonQuery(); |
||
565 | } |
||
566 | } |
||
567 | transaction.Commit(); |
||
568 | connection.Close(); |
||
569 | } |
||
570 | catch (Exception ex) |
||
571 | { |
||
572 | transaction.Rollback(); |
||
573 | result = false; |
||
574 | } |
||
575 | finally |
||
576 | { |
||
577 | transaction.Dispose(); |
||
578 | } |
||
579 | } |
||
580 | } |
||
581 | catch (Exception ex) |
||
582 | { |
||
583 | System.Windows.Forms.MessageBox.Show(ex.Message); |
||
584 | result = false; |
||
585 | } |
||
586 | finally |
||
587 | { |
||
588 | connection.Dispose(); |
||
589 | } |
||
590 | } |
||
591 | |||
592 | return result; |
||
593 | } |
||
594 | |||
595 | public static bool SavePathItems(DataTable dt) |
||
596 | { |
||
597 | ID2Info id2Info = ID2Info.GetInstance(); |
||
598 | |||
599 | bool result = true; |
||
600 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
601 | { |
||
602 | try |
||
603 | { |
||
604 | connection.Open(); |
||
605 | |||
606 | using (SQLiteTransaction transaction = connection.BeginTransaction()) |
||
607 | { |
||
608 | try |
||
609 | { |
||
610 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
611 | { |
||
612 | cmd.CommandText = string.Format("DELETE FROM {0}", PSN_PATHITEMS); |
||
613 | cmd.ExecuteNonQuery(); |
||
614 | |||
615 | foreach (DataRow row in dt.Rows) |
||
616 | { |
||
617 | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@OID, @SequenceData_OID, @TopologySet_OID, @BranchTopologySet_OID, @PipeLine_OID, @ITEMNAME, @ITEMTAG, @TYPE, @PIDNAME, @NPD, @PipeSystemNetwork_OID, @PipeRun_OID)", PSN_PATHITEMS); |
||
618 | cmd.Parameters.Clear(); |
||
619 | cmd.Parameters.AddWithValue("@OID", row["OID"].ToString()); |
||
620 | cmd.Parameters.AddWithValue("@SequenceData_OID", row["SequenceData_OID"].ToString()); |
||
621 | cmd.Parameters.AddWithValue("@TopologySet_OID", row["TopologySet_OID"].ToString()); |
||
622 | cmd.Parameters.AddWithValue("@BranchTopologySet_OID", row["BranchTopologySet_OID"].ToString()); |
||
623 | cmd.Parameters.AddWithValue("@PipeLine_OID", row["PipeLine_OID"].ToString()); |
||
624 | cmd.Parameters.AddWithValue("@ITEMNAME", row["ITEMNAME"].ToString()); |
||
625 | cmd.Parameters.AddWithValue("@ITEMTAG", row["ITEMTAG"].ToString()); |
||
626 | cmd.Parameters.AddWithValue("@TYPE", row["TYPE"].ToString()); |
||
627 | cmd.Parameters.AddWithValue("@PIDNAME", row["PIDNAME"].ToString()); |
||
628 | cmd.Parameters.AddWithValue("@NPD", row["NPD"].ToString()); |
||
629 | cmd.Parameters.AddWithValue("@PipeSystemNetwork_OID", row["PipeSystemNetwork_OID"].ToString()); |
||
630 | cmd.Parameters.AddWithValue("@PipeRun_OID", row["PipeRun_OID"].ToString()); |
||
631 | cmd.ExecuteNonQuery(); |
||
632 | } |
||
633 | } |
||
634 | transaction.Commit(); |
||
635 | connection.Close(); |
||
636 | } |
||
637 | catch (Exception ex) |
||
638 | { |
||
639 | transaction.Rollback(); |
||
640 | result = false; |
||
641 | } |
||
642 | finally |
||
643 | { |
||
644 | transaction.Dispose(); |
||
645 | } |
||
646 | } |
||
647 | } |
||
648 | catch (Exception ex) |
||
649 | { |
||
650 | System.Windows.Forms.MessageBox.Show(ex.Message); |
||
651 | result = false; |
||
652 | } |
||
653 | finally |
||
654 | { |
||
655 | connection.Dispose(); |
||
656 | } |
||
657 | } |
||
658 | |||
659 | return result; |
||
660 | } |
||
661 | |||
662 | public static bool SaveSequenceData(DataTable dt) |
||
663 | { |
||
664 | ID2Info id2Info = ID2Info.GetInstance(); |
||
665 | |||
666 | bool result = true; |
||
667 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
668 | { |
||
669 | try |
||
670 | { |
||
671 | connection.Open(); |
||
672 | |||
673 | using (SQLiteTransaction transaction = connection.BeginTransaction()) |
||
674 | { |
||
675 | try |
||
676 | { |
||
677 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
678 | { |
||
679 | cmd.CommandText = string.Format("DELETE FROM {0}", PSN_SEQUENCEDATA); |
||
680 | cmd.ExecuteNonQuery(); |
||
681 | |||
682 | foreach (DataRow row in dt.Rows) |
||
683 | { |
||
684 | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@OID, @SERIALNUMBER, @PathItem_OID, @TopologySet_OID_Key)", PSN_SEQUENCEDATA); |
||
685 | cmd.Parameters.Clear(); |
||
686 | cmd.Parameters.AddWithValue("@OID", row["OID"].ToString()); |
||
687 | cmd.Parameters.AddWithValue("@SERIALNUMBER", row["SERIALNUMBER"].ToString()); |
||
688 | cmd.Parameters.AddWithValue("@PathItem_OID", row["PathItem_OID"].ToString()); |
||
689 | cmd.Parameters.AddWithValue("@TopologySet_OID_Key", row["TopologySet_OID_Key"].ToString()); |
||
690 | cmd.ExecuteNonQuery(); |
||
691 | } |
||
692 | } |
||
693 | transaction.Commit(); |
||
694 | connection.Close(); |
||
695 | } |
||
696 | catch (Exception ex) |
||
697 | { |
||
698 | transaction.Rollback(); |
||
699 | result = false; |
||
700 | } |
||
701 | finally |
||
702 | { |
||
703 | transaction.Dispose(); |
||
704 | } |
||
705 | } |
||
706 | } |
||
707 | catch (Exception ex) |
||
708 | { |
||
709 | System.Windows.Forms.MessageBox.Show(ex.Message); |
||
710 | result = false; |
||
711 | } |
||
712 | finally |
||
713 | { |
||
714 | connection.Dispose(); |
||
715 | } |
||
716 | } |
||
717 | |||
718 | return result; |
||
719 | } |
||
720 | |||
721 | 8f24b438 | gaqhf | public static bool SavePipeSystemNetwork(DataTable dt, string revision) |
722 | 6b9e7a56 | gaqhf | { |
723 | ID2Info id2Info = ID2Info.GetInstance(); |
||
724 | |||
725 | bool result = true; |
||
726 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
727 | { |
||
728 | try |
||
729 | { |
||
730 | connection.Open(); |
||
731 | |||
732 | using (SQLiteTransaction transaction = connection.BeginTransaction()) |
||
733 | { |
||
734 | try |
||
735 | { |
||
736 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
737 | { |
||
738 | 8f24b438 | gaqhf | cmd.CommandText = string.Format("DELETE FROM {0} WHERE PSNRevisionNumber = '{1}'", PSN_PIPESYSTEMNETWORK, revision); |
739 | 6b9e7a56 | gaqhf | cmd.ExecuteNonQuery(); |
740 | |||
741 | foreach (DataRow row in dt.Rows) |
||
742 | { |
||
743 | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@OID, @Type, @OrderNumber, @Pipeline_OID, @FROM_DATA, @TO_DATA, @TopologySet_OID_Key, @PSNRevisionNumber, @PathOID, @PBS, @PIDDrawings)", PSN_PIPESYSTEMNETWORK); |
||
744 | cmd.Parameters.Clear(); |
||
745 | cmd.Parameters.AddWithValue("@OID", row["OID"].ToString()); |
||
746 | cmd.Parameters.AddWithValue("@Type", row["Type"].ToString()); |
||
747 | cmd.Parameters.AddWithValue("@OrderNumber", row["OrderNumber"].ToString()); |
||
748 | cmd.Parameters.AddWithValue("@Pipeline_OID", row["Pipeline_OID"].ToString()); |
||
749 | cmd.Parameters.AddWithValue("@FROM_DATA", row["FROM_DATA"].ToString()); |
||
750 | cmd.Parameters.AddWithValue("@TO_DATA", row["TO_DATA"].ToString()); |
||
751 | cmd.Parameters.AddWithValue("@TopologySet_OID_Key", row["TopologySet_OID_Key"].ToString()); |
||
752 | cmd.Parameters.AddWithValue("@PSNRevisionNumber", row["PSNRevisionNumber"].ToString()); |
||
753 | cmd.Parameters.AddWithValue("@PathOID", row["PathOID"].ToString()); |
||
754 | cmd.Parameters.AddWithValue("@PBS", row["PBS"].ToString()); |
||
755 | cmd.Parameters.AddWithValue("@PIDDrawings", row["PIDDrawings"].ToString()); |
||
756 | cmd.ExecuteNonQuery(); |
||
757 | } |
||
758 | } |
||
759 | transaction.Commit(); |
||
760 | connection.Close(); |
||
761 | } |
||
762 | catch (Exception ex) |
||
763 | { |
||
764 | transaction.Rollback(); |
||
765 | result = false; |
||
766 | } |
||
767 | finally |
||
768 | { |
||
769 | transaction.Dispose(); |
||
770 | } |
||
771 | } |
||
772 | } |
||
773 | catch (Exception ex) |
||
774 | { |
||
775 | System.Windows.Forms.MessageBox.Show(ex.Message); |
||
776 | result = false; |
||
777 | } |
||
778 | finally |
||
779 | { |
||
780 | connection.Dispose(); |
||
781 | } |
||
782 | } |
||
783 | |||
784 | return result; |
||
785 | } |
||
786 | |||
787 | public static bool SaveEquipment(DataTable dt) |
||
788 | { |
||
789 | ID2Info id2Info = ID2Info.GetInstance(); |
||
790 | |||
791 | bool result = true; |
||
792 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
793 | { |
||
794 | try |
||
795 | { |
||
796 | connection.Open(); |
||
797 | |||
798 | using (SQLiteTransaction transaction = connection.BeginTransaction()) |
||
799 | { |
||
800 | try |
||
801 | { |
||
802 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
803 | { |
||
804 | cmd.CommandText = string.Format("DELETE FROM {0}", PSN_EQUIPMENT); |
||
805 | cmd.ExecuteNonQuery(); |
||
806 | |||
807 | foreach (DataRow row in dt.Rows) |
||
808 | { |
||
809 | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS)", PSN_EQUIPMENT); |
||
810 | cmd.Parameters.Clear(); |
||
811 | cmd.Parameters.AddWithValue("@OID", row["OID"].ToString()); |
||
812 | cmd.Parameters.AddWithValue("@ITEMTAG", row["ITEMTAG"].ToString()); |
||
813 | cmd.Parameters.AddWithValue("@XCOORDS", row["XCOORDS"].ToString()); |
||
814 | cmd.Parameters.AddWithValue("@YCOORDS", row["YCOORDS"].ToString()); |
||
815 | cmd.ExecuteNonQuery(); |
||
816 | } |
||
817 | } |
||
818 | transaction.Commit(); |
||
819 | connection.Close(); |
||
820 | } |
||
821 | catch (Exception ex) |
||
822 | { |
||
823 | transaction.Rollback(); |
||
824 | result = false; |
||
825 | } |
||
826 | finally |
||
827 | { |
||
828 | transaction.Dispose(); |
||
829 | } |
||
830 | } |
||
831 | } |
||
832 | catch (Exception ex) |
||
833 | { |
||
834 | System.Windows.Forms.MessageBox.Show(ex.Message); |
||
835 | result = false; |
||
836 | } |
||
837 | finally |
||
838 | { |
||
839 | connection.Dispose(); |
||
840 | } |
||
841 | } |
||
842 | |||
843 | return result; |
||
844 | } |
||
845 | |||
846 | public static bool SaveNozzle(DataTable dt) |
||
847 | { |
||
848 | ID2Info id2Info = ID2Info.GetInstance(); |
||
849 | |||
850 | bool result = true; |
||
851 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
852 | { |
||
853 | try |
||
854 | { |
||
855 | connection.Open(); |
||
856 | |||
857 | using (SQLiteTransaction transaction = connection.BeginTransaction()) |
||
858 | { |
||
859 | try |
||
860 | { |
||
861 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
862 | { |
||
863 | cmd.CommandText = string.Format("DELETE FROM {0}", PSN_NOZZLE); |
||
864 | cmd.ExecuteNonQuery(); |
||
865 | |||
866 | foreach (DataRow row in dt.Rows) |
||
867 | { |
||
868 | cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@OID, @ITEMTAG, @XCOORDS, @YCOORDS, @Equipment_OID, @FLUID, @NPD, @ROTATION, @FlowDirection)", PSN_NOZZLE); |
||
869 | cmd.Parameters.Clear(); |
||
870 | cmd.Parameters.AddWithValue("@OID", row["OID"].ToString()); |
||
871 | cmd.Parameters.AddWithValue("@ITEMTAG", row["ITEMTAG"].ToString()); |
||
872 | cmd.Parameters.AddWithValue("@XCOORDS", row["XCOORDS"].ToString()); |
||
873 | cmd.Parameters.AddWithValue("@YCOORDS", row["YCOORDS"].ToString()); |
||
874 | cmd.Parameters.AddWithValue("@Equipment_OID", row["Equipment_OID"].ToString()); |
||
875 | cmd.Parameters.AddWithValue("@FLUID", row["FLUID"].ToString()); |
||
876 | cmd.Parameters.AddWithValue("@NPD", row["NPD"].ToString()); |
||
877 | cmd.Parameters.AddWithValue("@ROTATION", row["ROTATION"].ToString()); |
||
878 | cmd.Parameters.AddWithValue("@FlowDirection", row["FlowDirection"].ToString()); |
||
879 | cmd.ExecuteNonQuery(); |
||
880 | } |
||
881 | } |
||
882 | transaction.Commit(); |
||
883 | connection.Close(); |
||
884 | } |
||
885 | catch (Exception ex) |
||
886 | { |
||
887 | transaction.Rollback(); |
||
888 | result = false; |
||
889 | } |
||
890 | finally |
||
891 | { |
||
892 | transaction.Dispose(); |
||
893 | } |
||
894 | } |
||
895 | } |
||
896 | catch (Exception ex) |
||
897 | { |
||
898 | System.Windows.Forms.MessageBox.Show(ex.Message); |
||
899 | result = false; |
||
900 | } |
||
901 | finally |
||
902 | { |
||
903 | connection.Dispose(); |
||
904 | } |
||
905 | } |
||
906 | |||
907 | return result; |
||
908 | } |
||
909 | 8f24b438 | gaqhf | |
910 | public static int GetRevision() |
||
911 | { |
||
912 | int result = 0; |
||
913 | DataTable dt = new DataTable(); |
||
914 | ID2Info id2Info = ID2Info.GetInstance(); |
||
915 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
916 | { |
||
917 | try |
||
918 | { |
||
919 | connection.Open(); |
||
920 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
921 | { |
||
922 | cmd.CommandText = string.Format("SELECT DISTINCT PSNRevisionNumber FROM {0};", PSN_PIPESYSTEMNETWORK); |
||
923 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
924 | dt.Load(dr); |
||
925 | |||
926 | foreach (DataRow row in dt.Rows) |
||
927 | { |
||
928 | int revisionNumber = Convert.ToInt32(row["PSNRevisionNumber"]); |
||
929 | if (result < revisionNumber) |
||
930 | result = revisionNumber; |
||
931 | } |
||
932 | |||
933 | result++; |
||
934 | } |
||
935 | connection.Close(); |
||
936 | } |
||
937 | catch (Exception ex) |
||
938 | { |
||
939 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
940 | result = -1; |
||
941 | } |
||
942 | finally |
||
943 | { |
||
944 | connection.Dispose(); |
||
945 | } |
||
946 | } |
||
947 | |||
948 | return result; |
||
949 | } |
||
950 | |||
951 | public static double[] GetDrawingSize() |
||
952 | { |
||
953 | double[] result = null; |
||
954 | |||
955 | DataTable dt = new DataTable(); |
||
956 | ID2Info id2Info = ID2Info.GetInstance(); |
||
957 | using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", id2Info.DBFilePath), true)) |
||
958 | { |
||
959 | try |
||
960 | { |
||
961 | connection.Open(); |
||
962 | using (SQLiteCommand cmd = connection.CreateCommand()) |
||
963 | { |
||
964 | cmd.CommandText = "SELECT value FROM Configuration WHERE Section = 'Area' AND Key = 'Drawing';"; |
||
965 | using (SQLiteDataReader dr = cmd.ExecuteReader()) |
||
966 | dt.Load(dr); |
||
967 | |||
968 | if (dt.Rows.Count == 1) |
||
969 | { |
||
970 | string value = dt.Rows[0][0].ToString(); |
||
971 | string[] split = value.Split(new char[] { ',' }); |
||
972 | result = new double[] { |
||
973 | Convert.ToDouble(Regex.Replace(split[0], @"[^0-9]", "")), |
||
974 | Convert.ToDouble(Regex.Replace(split[1], @"[^0-9]", "")), |
||
975 | Convert.ToDouble(Regex.Replace(split[2], @"[^0-9]", "")), |
||
976 | Convert.ToDouble(Regex.Replace(split[3], @"[^0-9]", "")) |
||
977 | }; |
||
978 | result = new double[] { |
||
979 | Math.Min(result[0], result[2]), |
||
980 | Math.Min(result[1], result[3]), |
||
981 | Math.Max(result[0], result[2]), |
||
982 | Math.Max(result[1], result[3]) |
||
983 | }; |
||
984 | } |
||
985 | } |
||
986 | connection.Close(); |
||
987 | } |
||
988 | catch (Exception ex) |
||
989 | { |
||
990 | Log.Write(ex.Message + "\r\n" + ex.StackTrace); |
||
991 | } |
||
992 | finally |
||
993 | { |
||
994 | connection.Dispose(); |
||
995 | } |
||
996 | } |
||
997 | |||
998 | return result; |
||
999 | } |
||
1000 | 0dae5645 | gaqhf | } |
1001 | } |