1: using Ia.Ngn.Cl.Model.Business; // Needed for ServerExtension
2: using Microsoft.EntityFrameworkCore;
3: using System;
4: using System.Collections;
5: using System.Collections.Generic;
6: using System.Data;
7: using System.IO;
8: using System.Linq;
9: using System.Reflection;
10: using System.Text.RegularExpressions;
11: using System.Xml.Linq;
12:
13: namespace Ia.Ngn.Cl.Model.Data
14: {
15: ////////////////////////////////////////////////////////////////////////////
16:
17: /// <summary publish="true">
18: /// Service Request Type support class for Optical Fiber Network (OFN) data model.
19: /// </summary>
20: ///
21: /// <remarks>
22: /// Copyright © 2006-2022 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
23: ///
24: /// This library is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
25: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
26: ///
27: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
28: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
29: ///
30: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
31: ///
32: /// Copyright notice: This notice may not be removed or altered from any source distribution.
33: /// </remarks>
34: public class ServiceRequestType
35: {
36: private static XDocument xDocument;
37: private static SortedList serviceRequestTypeTypeList;
38:
39: private static readonly object objectLock = new object();
40:
41: /// <summary/>
42: public ServiceRequestType() { }
43:
44: ////////////////////////////////////////////////////////////////////////////
45:
46: /// <summary>
47: ///
48: /// </summary>
49: public static SortedList ServiceRequestTypeTypeList
50: {
51: get
52: {
53: if (serviceRequestTypeTypeList == null || serviceRequestTypeTypeList.Count == 0)
54: {
55: lock (objectLock)
56: {
57: serviceRequestTypeTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType._ServiceRequestTypeTypeList;
58: }
59: }
60:
61: return serviceRequestTypeTypeList;
62: }
63: }
64:
65: ////////////////////////////////////////////////////////////////////////////
66:
67: /// <summary>
68: ///
69: /// </summary>
70: private static SortedList _ServiceRequestTypeTypeList
71: {
72: get
73: {
74: int id;
75:
76: serviceRequestTypeTypeList = new SortedList(100);
77:
78: foreach (XElement x in XDocument.Element("serviceRequest").Elements("serviceRequestType").Elements("typeList").Elements("type"))
79: {
80: id = int.Parse(x.Attribute("id").Value);
81:
82: serviceRequestTypeTypeList[id] = x.Attribute("oracleFieldName").Value;
83: }
84:
85: return serviceRequestTypeTypeList;
86: }
87: }
88:
89: ////////////////////////////////////////////////////////////////////////////
90:
91: /// <summary>
92: ///
93: /// </summary>
94: public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List()
95: {
96: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
97:
98: using (var db = new Ia.Ngn.Cl.Model.Ngn())
99: {
100: serviceRequestTypeList = (from srt in db.ServiceRequestTypes select srt).ToList();
101: }
102:
103: return serviceRequestTypeList;
104: }
105:
106: ////////////////////////////////////////////////////////////////////////////
107:
108: /// <summary>
109: ///
110: /// </summary>
111: public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(int number)
112: {
113: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
114:
115: using (var db = new Ia.Ngn.Cl.Model.Ngn())
116: {
117: serviceRequestTypeList = (from srt in db.ServiceRequestTypes
118: where srt.ServiceRequest.Number == number
119: select srt).Include(u => u.ServiceRequest).ToList();
120: }
121:
122: return serviceRequestTypeList;
123: }
124:
125: ////////////////////////////////////////////////////////////////////////////
126:
127: /// <summary>
128: ///
129: /// </summary>
130: public static List<Ia.Ngn.Cl.Model.ServiceRequestType> ListByServiceRequestId(int serviceRequestId)
131: {
132: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
133:
134: using (var db = new Ia.Ngn.Cl.Model.Ngn())
135: {
136: serviceRequestTypeList = (from srt in db.ServiceRequestTypes
137: where srt.ServiceRequest.Id == serviceRequestId
138: select srt).AsNoTracking().ToList(); //.Include(u => u.ServiceRequest).ToList();
139: }
140:
141: return serviceRequestTypeList;
142: }
143:
144: ////////////////////////////////////////////////////////////////////////////
145:
146: /// <summary>
147: ///
148: /// </summary>
149: public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(string service)
150: {
151: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
152:
153: if (!string.IsNullOrEmpty(service))
154: {
155: if (int.TryParse(service, out int number))
156: {
157: using (var db = new Ia.Ngn.Cl.Model.Ngn())
158: {
159: serviceRequestTypeList = (from srt in db.ServiceRequestTypes
160: where srt.ServiceRequest.Number == number
161: select srt).Include(u => u.ServiceRequest).ToList();
162: }
163: }
164: else
165: {
166: throw new ArgumentException(@"List(): service is not a number, service: " + service);
167: }
168: }
169: else serviceRequestTypeList = new List<Ia.Ngn.Cl.Model.ServiceRequestType>();
170:
171: return serviceRequestTypeList;
172: }
173:
174: ////////////////////////////////////////////////////////////////////////////
175:
176: /// <summary>
177: ///
178: /// </summary>
179: public static List<Ia.Ngn.Cl.Model.ServiceRequestType> ReadListThatHasServiceRequestIdsWithinIdRange(int start, int end)
180: {
181: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
182:
183: using (var db = new Ia.Ngn.Cl.Model.Ngn())
184: {
185: serviceRequestTypeList = (from srt in db.ServiceRequestTypes
186: join sr in db.ServiceRequests
187: on srt.ServiceRequest.Id equals sr.Id
188: where sr.Id >= start && sr.Id <= end
189: select srt)/*.Include(u => u.ServiceRequest)*/.AsNoTracking().ToList();
190: }
191:
192: return serviceRequestTypeList;
193: }
194:
195: ////////////////////////////////////////////////////////////////////////////
196:
197: /// <summary>
198: ///
199: /// </summary>
200: public static List<Ia.Ngn.Cl.Model.ServiceRequestType> ReadListThatHaveServiceRequestsWithinGivenDateRange(DateTime startDateTime, DateTime endDateTime)
201: {
202: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
203:
204: using (var db = new Ia.Ngn.Cl.Model.Ngn())
205: {
206: serviceRequestTypeList = (from srt in db.ServiceRequestTypes
207: join sr in db.ServiceRequests
208: on srt.ServiceRequest.Id equals sr.Id
209: where sr.RequestDateTime >= startDateTime && sr.RequestDateTime < endDateTime
210: select srt).ToList();
211: }
212:
213: return serviceRequestTypeList;
214: }
215:
216: ////////////////////////////////////////////////////////////////////////////
217:
218: /// <summary>
219: ///
220: /// </summary>
221: public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(List<Ia.Ngn.Cl.Model.Business.ServiceRequest.NumberSerial> numberSerialList)
222: {
223: List<long> idList;
224: List<Ia.Ngn.Cl.Model.ServiceRequestType> list;
225:
226: if (numberSerialList.Count > 0)
227: {
228: idList = numberSerialList.IdList();
229:
230: using (var db = new Ia.Ngn.Cl.Model.Ngn())
231: {
232: list = (from srt in db.ServiceRequestTypes
233: where
234: //numberSerialList.Contains(q.ServiceRequest.Number, q.ServiceRequest.Serial)
235: idList.Contains((long)srt.ServiceRequest.Number * 100 + srt.ServiceRequest.Serial)
236: select srt).Include(u => u.ServiceRequest).ToList();
237: }
238: }
239: else list = new List<Ia.Ngn.Cl.Model.ServiceRequestType>();
240:
241: return list;
242: }
243:
244: ////////////////////////////////////////////////////////////////////////////
245:
246: /// <summary>
247: ///
248: /// </summary>
249: public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(List<int> numberList)
250: {
251: List<string> serviceList;
252: List<Ia.Ngn.Cl.Model.ServiceRequestType> list;
253:
254: if (numberList.Count > 0)
255: {
256: serviceList = (from n in numberList select n.ToString()).ToList();
257:
258: using (var db = new Ia.Ngn.Cl.Model.Ngn())
259: {
260: list = (from srt in db.ServiceRequestTypes
261: where numberList.Contains(srt.ServiceRequest.Number) || (srt.TypeId == 11 && serviceList.Contains(srt.Value))
262: select srt).Include(u => u.ServiceRequest).AsNoTracking().ToList();
263: }
264: }
265: else list = new List<Ia.Ngn.Cl.Model.ServiceRequestType>();
266:
267: /*
268: * For the corrections of //.ToList() above see https://stackoverflow.com/questions/18086005/linq-to-entities-does-not-recognize-the-method-generic-listint-to-generic-ienu
269: */
270:
271: return list.Distinct().ToList();
272: }
273:
274: ////////////////////////////////////////////////////////////////////////////
275:
276: /// <summary>
277: ///
278: /// </summary>
279: public static string UpdateForServiceRequestIdRangeWithOutputDataTable(DataTable dataTable, out List<string> insertedOrUpdatedOrDeletedServiceList)
280: {
281: // below: the SQL statement should be within the dataTable.TableName variable
282: int serviceRequestId, serviceRequestTypeId, start, end, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
283: string sql, r, result;
284: ArrayList newServiceRequestTypeIdArryList;
285: Match match;
286: Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
287: List<int> serviceRequestTypeWithNoServiceRequestIdList;
288: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
289:
290: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
291: result = r = string.Empty;
292: serviceRequestTypeWithNoServiceRequestIdList = new List<int>();
293:
294: insertedOrUpdatedOrDeletedServiceList = new List<string>();
295:
296: if (dataTable != null)
297: {
298: sql = dataTable.TableName;
299:
300: // select SRV_REQ_FIPER_TECH.SRV_REQ_ID, SRV_REQ_FIPER_TECH.TECH_TYPE_ID, SRV_REQ_FIPER_TECH.VAL from SRV_REQ_FIPER left outer join SRV_REQ_FIPER_TECH on SRV_REQ_FIPER_TECH.SRV_REQ_ID = SRV_REQ_FIPER.SRV_REQ_ID where SRV_REQ_FIPER_TECH.SRV_REQ_ID >= 110000 and SRV_REQ_FIPER_TECH.SRV_REQ_ID <= 321203 and SRV_REQ_FIPER_TECH.SRV_REQ_ID is not null and SRV_REQ_FIPER_TECH.TECH_TYPE_ID is not null and SRV_REQ_FIPER_TECH.VAL is not null order by REQ_DATE asc, SRV_REQ_FIPER.SRV_REQ_ID asc
301: match = Regex.Match(sql, @"SRV_REQ_FIPER_TECH\.SRV_REQ_ID >= (\d+) and SRV_REQ_FIPER_TECH\.SRV_REQ_ID <= (\d+) ", RegexOptions.Singleline);
302:
303: if (match.Success)
304: {
305: using (var db = new Ia.Ngn.Cl.Model.Ngn())
306: {
307: readItemCount = dataTable.Rows.Count;
308:
309: start = int.Parse(match.Groups[1].Value);
310: end = int.Parse(match.Groups[2].Value);
311:
312: serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.ReadListThatHasServiceRequestIdsWithinIdRange(start, end);
313: existingItemCount = serviceRequestTypeList.Count;
314:
315: newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
316:
317: foreach (DataRow dataRow in dataTable.Rows)
318: {
319: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
320:
321: if (Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceRequestIdIsAllowedForProcessing(serviceRequestId))
322: {
323: serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + dataRow["TECH_TYPE_ID"].ToString().PadLeft(2, '0'));
324:
325: newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
326:
327: newServiceRequestType.Id = serviceRequestTypeId;
328:
329: newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests
330: where sr.Id == serviceRequestId
331: select sr).SingleOrDefault();
332:
333: // below: we will not add any type that does not have a service request
334: if (newServiceRequestType.ServiceRequest != null)
335: {
336: newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
337: newServiceRequestType.Value = dataRow["VAL"].ToString();
338:
339: FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
340:
341: serviceRequestType = (from srt in serviceRequestTypeList
342: where srt.Id == newServiceRequestType.Id
343: select srt).SingleOrDefault();
344:
345: if (serviceRequestType == null)
346: {
347: newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
348:
349: db.ServiceRequestTypes.Add(newServiceRequestType);
350:
351: if (newServiceRequestType.ServiceRequest != null)
352: {
353: insertedOrUpdatedOrDeletedServiceList.Add(newServiceRequestType.ServiceRequest.Number.ToString());
354: }
355:
356: insertedItemCount++;
357: }
358: else
359: {
360: // below: copy values from newServiceRequestType to serviceRequestType
361:
362: if (serviceRequestType.Update(newServiceRequestType))
363: {
364: db.ServiceRequestTypes.Attach(serviceRequestType);
365: db.Entry(serviceRequestType).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
366:
367: if (serviceRequestType.ServiceRequest != null)
368: {
369: insertedOrUpdatedOrDeletedServiceList.Add(serviceRequestType.ServiceRequest.Number.ToString());
370: }
371:
372: updatedItemCount++;
373: }
374: }
375:
376: // below: this will enable the removal of SRT that don't have a valid SR
377: newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
378: }
379: else
380: {
381: serviceRequestTypeWithNoServiceRequestIdList.Add(newServiceRequestType.Id);
382: }
383: }
384: else
385: {
386:
387: }
388: }
389:
390: /*
391: if (serviceRequestTypeWithNoServiceRequestIdList.Count > 0)
392: {
393: r = "SRT with no SR: ";
394:
395: foreach (int n in serviceRequestTypeWithNoServiceRequestIdList) r += n + ",";
396:
397: r = r.Trim(',');
398: }
399: */
400:
401: // below: this function will remove values that were not present in the reading
402: if (serviceRequestTypeList.Count > 0)
403: {
404: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
405: {
406: if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
407: {
408: serviceRequestType = (from srt2 in db.ServiceRequestTypes
409: where srt2.Id == srt.Id
410: select srt2).SingleOrDefault();
411:
412: db.ServiceRequestTypes.Remove(serviceRequestType);
413:
414: insertedOrUpdatedOrDeletedServiceList.Add(serviceRequestType.ServiceRequest.Number.ToString());
415: deletedItemCount++;
416: }
417: }
418: }
419:
420: db.SaveChanges();
421:
422: //if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
423: //else isUpdated = false;
424:
425: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
426: }
427: }
428: else
429: {
430: result = "(?/?/?: SQL in TableName is unmatched) ";
431: }
432: }
433: else
434: {
435: result = "(dataTable == null/?/?) ";
436: }
437:
438: return result;
439: }
440:
441: ////////////////////////////////////////////////////////////////////////////
442:
443: /// <summary>
444: ///
445: /// </summary>
446: public static void UpdateForServiceRequestTypeWithOutputDataTableService(DataTable dataTable, string service, out bool isUpdated, out Ia.Cl.Model.Result result)
447: {
448: int serviceRequestId, serviceRequestTypeId, serviceRequestTypeTypeId, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
449: string sql, sqlService, exception;
450: ArrayList newServiceRequestTypeIdArryList;
451: Match match;
452: Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
453: List<int> serviceRequestTypeWithNoServiceRequestIdList;
454: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
455:
456: isUpdated = false;
457: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
458: result = new Ia.Cl.Model.Result();
459: exception = string.Empty;
460: serviceRequestTypeWithNoServiceRequestIdList = new List<int>();
461:
462: result = new Ia.Cl.Model.Result();
463:
464: if (dataTable != null)
465: {
466: // below: the SQL statement should be within the dataTable.TableName variable
467: sql = dataTable.TableName;
468:
469: /*
470: select SRV_REQ_FIPER_TECH.SRV_REQ_ID, SRV_REQ_FIPER_TECH.TECH_TYPE_ID, SRV_REQ_FIPER_TECH.VAL from SRV_REQ_FIPER
471: left outer join SRV_REQ_FIPER_TECH on SRV_REQ_FIPER_TECH.SRV_REQ_ID = SRV_REQ_FIPER.SRV_REQ_ID
472: where SRV_REQ_FIPER.SRV_NO = " + service + @" and SRV_REQ_FIPER_TECH.SRV_REQ_ID is not null and SRV_REQ_FIPER_TECH.TECH_TYPE_ID is not null and SRV_REQ_FIPER_TECH.VAL is not null
473: order by SRV_REQ_FIPER.SRV_REQ_ID asc
474: */
475: match = Regex.Match(sql, @"SRV_REQ_FIPER.SRV_NO = (\d+) and SRV_REQ_FIPER_TECH.SRV_REQ_ID", RegexOptions.Singleline);
476:
477: if (match.Success)
478: {
479: using (var db = new Ia.Ngn.Cl.Model.Ngn())
480: {
481: readItemCount = dataTable.Rows.Count;
482:
483: sqlService = match.Groups[1].Value;
484:
485: if (service == sqlService)
486: {
487: serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.List(service);
488: existingItemCount = serviceRequestTypeList.Count;
489:
490: newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
491:
492: foreach (DataRow dataRow in dataTable.Rows)
493: {
494: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
495: serviceRequestTypeTypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
496:
497: serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + serviceRequestTypeTypeId.ToString().PadLeft(2, '0'));
498:
499: if (Ia.Ngn.Cl.Model.Data.ServiceRequestType.ServiceRequestTypeTypeList.ContainsKey(serviceRequestTypeTypeId))
500: {
501: newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
502:
503: newServiceRequestType.Id = serviceRequestTypeId;
504:
505: newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests/*.AsNoTracking()*/ where sr.Id == serviceRequestId select sr).SingleOrDefault();
506: // System.InvalidOperationException: Attaching an entity of type 'Ia.Ngn.Cl.Model.ServiceRequest' failed because another entity of the same type already has the same primary key value.
507: // .AsNoTracking() see https://stackoverflow.com/questions/41376161/attaching-an-entity-of-type-x-failed-because-another-entity-of-the-same-type-a?rq=1
508: // and see https://stackoverflow.com/questions/18122723/asnotracking-using-linq-query-syntax-instead-of-method-syntax/18125658
509: // https://stackoverflow.com/questions/23201907/asp-net-mvc-attaching-an-entity-of-type-modelname-failed-because-another-ent
510:
511: try
512: {
513: // below: we will not add any type that does not have a service request
514: if (newServiceRequestType.ServiceRequest != null)
515: {
516: newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
517: newServiceRequestType.Value = dataRow["VAL"].ToString();
518:
519: FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
520:
521: serviceRequestType = (from srt in serviceRequestTypeList
522: where srt.Id == newServiceRequestType.Id
523: select srt).SingleOrDefault();
524:
525: if (serviceRequestType == null)
526: {
527: newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
528:
529: db.ServiceRequestTypes.Add(newServiceRequestType);
530:
531: insertedItemCount++;
532: }
533: else
534: {
535: // below: copy values from newServiceRequestType to serviceRequestType
536:
537: if (serviceRequestType.Update(newServiceRequestType))
538: {
539: db.Entry(serviceRequestType).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
540: db.ServiceRequestTypes.Attach(serviceRequestType);
541:
542: updatedItemCount++;
543: }
544: }
545:
546: // below: this will enable the removal of SRT that don't have a valid SR
547: newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
548: }
549: else
550: {
551: serviceRequestTypeWithNoServiceRequestIdList.Add(newServiceRequestType.Id);
552: }
553: }
554: catch (Exception ex)
555: {
556: exception += "Exception: serviceRequestId: " + serviceRequestId + ", exception:" + ex.ToString();
557: }
558: }
559: else result.AddError("Type " + serviceRequestTypeTypeId + " is undefined.");
560: }
561:
562: /*
563: if (serviceRequestTypeWithNoServiceRequestIdList.Count > 0)
564: {
565: r = "SRT with no SR: ";
566:
567: foreach (int n in serviceRequestTypeWithNoServiceRequestIdList) r += n + ",";
568:
569: r = r.Trim(',');
570: }
571: */
572:
573: // below: this function will remove values that were not present in the reading
574: if (serviceRequestTypeList.Count > 0)
575: {
576: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
577: {
578: if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
579: {
580: serviceRequestType = (from srt2 in db.ServiceRequestTypes where srt2.Id == srt.Id select srt2).SingleOrDefault();
581:
582: db.ServiceRequestTypes.Remove(serviceRequestType);
583:
584: deletedItemCount++;
585: }
586: }
587: }
588:
589: db.SaveChanges();
590:
591: if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
592: else isUpdated = false;
593:
594: result.AddSuccess("(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") (" + exception + ")");
595: }
596: else
597: {
598: throw new ArgumentException(@"UpdateForServiceRequestWithOutputDataTableService(): service != sqlService, service: " + service + ", sqlService: " + sqlService);
599: }
600: }
601: }
602: else result.AddError("(?/?/?: SQL in TableName is unmatched)");
603: }
604: else result.AddError("(dataTable == null/?/?)"); ;
605: }
606:
607: ////////////////////////////////////////////////////////////////////////////
608:
609: /// <summary>
610: ///
611: /// </summary>
612: public static void UpdateForADateTimeRangeWithOutputDataTable(DataTable dataTable, Tuple<int, int> dateTime, out string result)
613: {
614: // below: the SQL statement should be within the dataTable.TableName variable
615: int readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
616: int serviceRequestId, serviceRequestTypeId;
617: string sql, r;
618: ArrayList newServiceRequestTypeIdArryList;
619: DateTime startDateTime, endDateTime;
620: Match match;
621: Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
622: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
623:
624: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
625: result = r = string.Empty;
626:
627: startDateTime = endDateTime = DateTime.MinValue;
628:
629: if (dataTable != null)
630: {
631: sql = dataTable.TableName;
632:
633: // select * from SRV_REQ_FIPER LEFT OUTER JOIN SRV_REQ_FIPER_TECH ON SRV_REQ_FIPER_TECH.SRV_REQ_ID = SRV_REQ_FIPER.SRV_REQ_ID where REQ_DATE >= '06/01/2007' and REQ_DATE < '07/01/2007' order by REQ_DATE asc, SRV_REQ_FIPER.SRV_REQ_ID asc
634:
635: match = Regex.Match(sql, @".+'(\d{2})\/(\d{2})\/(\d{4})'.+'(\d{2})\/(\d{2})\/(\d{4})'.+", RegexOptions.Singleline);
636: // 1 2 3 4 5 6
637:
638: if (match.Success)
639: {
640: using (var db = new Ia.Ngn.Cl.Model.Ngn())
641: {
642: readItemCount = dataTable.Rows.Count;
643:
644: //if (dataTable.Rows.Count > 0)
645: //{
646: startDateTime = DateTime.Parse(match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value);
647: endDateTime = DateTime.Parse(match.Groups[6].Value + "-" + match.Groups[5].Value + "-" + match.Groups[4].Value);
648:
649: serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.ReadListThatHaveServiceRequestsWithinGivenDateRange(startDateTime, endDateTime);
650: existingItemCount = serviceRequestTypeList.Count;
651:
652: newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
653:
654: foreach (DataRow dataRow in dataTable.Rows)
655: {
656: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
657: serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + dataRow["TECH_TYPE_ID"].ToString().PadLeft(2, '0'));
658:
659: newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
660:
661: newServiceRequestType.Id = serviceRequestTypeId;
662:
663: newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequestId select sr).SingleOrDefault();
664:
665: // below: we will not add any type that does not have a service request
666: if (newServiceRequestType.ServiceRequest != null)
667: {
668: newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
669: newServiceRequestType.Value = dataRow["VAL"].ToString();
670:
671: FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
672:
673: serviceRequestType = (from srt in serviceRequestTypeList where srt.Id == newServiceRequestType.Id select srt).SingleOrDefault();
674:
675: if (serviceRequestType == null)
676: {
677: newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
678:
679: db.ServiceRequestTypes.Add(newServiceRequestType);
680:
681: insertedItemCount++;
682: }
683: else
684: {
685: // below: copy values from newServiceRequestType to serviceRequestType
686:
687: if (serviceRequestType.Update(newServiceRequestType))
688: {
689: db.ServiceRequestTypes.Attach(serviceRequestType);
690: db.Entry(serviceRequestType).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
691:
692: updatedItemCount++;
693: }
694: }
695:
696: // below: this will enable the removal of SRT that don't have a valid SR
697: newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
698: }
699: else
700: {
701: r += "newServiceRequestType.Id: " + newServiceRequestType.Id + " newServiceRequestType.ServiceRequest == null, ";
702: }
703: }
704:
705: // below: this function will remove values that were not present in the reading
706: if (serviceRequestTypeList.Count > 0)
707: {
708: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
709: {
710: if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
711: {
712: serviceRequestType = (from srt2 in db.ServiceRequestTypes where srt2.Id == srt.Id select srt2).SingleOrDefault();
713:
714: db.ServiceRequestTypes.Remove(srt);
715:
716: deletedItemCount++;
717: }
718: }
719: }
720:
721: db.SaveChanges();
722:
723: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
724: //}
725: //else
726: //{
727: // result = "(" + readItemCount + "/?/?) ";
728: //}
729: }
730: }
731: else
732: {
733: result = "(?/?/?: SQL in TableName is unmatched) ";
734: }
735: }
736: else
737: {
738: result = "(dataTable == null/?/?) ";
739: }
740: }
741:
742: ////////////////////////////////////////////////////////////////////////////
743:
744: /// <summary>
745: ///
746: /// </summary>
747: private static void FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType)
748: {
749: // below: procedure to fix service request records from the common mistakes
750:
751: bool b;
752: int number;
753:
754: // below: convert 7 digit numbers to 8 digits
755: // <type id="11" name="dn" arabicName="dn" oracleFieldName="الرقم الجديد"/>
756: if (serviceRequestType.TypeId == 11)
757: {
758: b = int.TryParse(serviceRequestType.Value.Trim(), out number);
759:
760: if (b)
761: {
762: number = Ia.Ngn.Cl.Model.Business.Default.ChangeOldSevenDigitNumbersToEightDigitFormat(number);
763:
764: if (Ia.Ngn.Cl.Model.Business.Service.ServiceHasEightDigitsAndIsWithinAllowedDomainList(number))
765: {
766: serviceRequestType.Value = number.ToString();
767: }
768: else serviceRequestType.Value = null;
769: }
770: else serviceRequestType.Value = null;
771: }
772: }
773:
774: ////////////////////////////////////////////////////////////////////////////
775:
776: /// <summary>
777: ///
778: /// </summary>
779: public static Dictionary<int, string> NumberToServiceRequestTypeStringDictionary(List<int> domainList)
780: {
781: int number;
782: Dictionary<int, string> dictionary;
783: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
784:
785: using (var db = new Ia.Ngn.Cl.Model.Ngn())
786: {
787: if (domainList.Count > 0)
788: {
789: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where domainList.Contains(srt.ServiceRequest.Number / 10000) || domainList.Contains(srt.ServiceRequest.Number / 1000) select srt).ToList();
790:
791: if (serviceRequestTypeList != null)
792: {
793: dictionary = new Dictionary<int, string>(serviceRequestTypeList.Count);
794:
795: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList.OrderBy(u => u.Id))
796: {
797: number = int.Parse(srt.ServiceRequest.Number.ToString());
798:
799: if (dictionary.ContainsKey(number)) dictionary[number] = dictionary[number] + "," + srt.Value;
800: else dictionary[number] = srt.Value;
801: }
802: }
803: else dictionary = new Dictionary<int, string>();
804: }
805: else dictionary = new Dictionary<int, string>();
806: }
807:
808: return dictionary;
809: }
810:
811: ////////////////////////////////////////////////////////////////////////////
812:
813: /// <summary>
814: ///
815: /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
816: ///
817: /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
818: /// 2. Add "using System.Reflection".
819: /// 3. Manifest resource stream will start with the project namespace, the location of XML file.
820: ///
821: /// </summary>
822:
823: private static XDocument XDocument
824: {
825: get
826: {
827: if (xDocument == null)
828: {
829: lock (objectLock)
830: {
831: Assembly _assembly;
832: StreamReader streamReader;
833:
834: _assembly = Assembly.GetExecutingAssembly();
835: streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.service-request.xml"));
836:
837: try
838: {
839: if (streamReader.Peek() != -1)
840: {
841: xDocument = System.Xml.Linq.XDocument.Load(streamReader);
842: }
843: }
844: catch (Exception)
845: {
846: }
847: finally
848: {
849: }
850: }
851: }
852:
853: return xDocument;
854: }
855: }
856:
857: ////////////////////////////////////////////////////////////////////////////
858: ////////////////////////////////////////////////////////////////////////////
859: }
860:
861: ////////////////////////////////////////////////////////////////////////////
862: ////////////////////////////////////////////////////////////////////////////
863: }