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;
11: using System.Text.RegularExpressions;
12: using System.Xml.Linq;
13:
14: namespace Ia.Ngn.Cl.Model.Data
15: {
16: ////////////////////////////////////////////////////////////////////////////
17:
18: /// <summary publish="true">
19: /// Service Request support class for Next Generation Network (NGN) data model.
20: /// </summary>
21: ///
22: /// <remarks>
23: /// Copyright © 2006-2020 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
24: ///
25: /// 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
26: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
27: ///
28: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
29: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
30: ///
31: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
32: ///
33: /// Copyright notice: This notice may not be removed or altered from any source distribution.
34: /// </remarks>
35: public partial class ServiceRequest
36: {
37: private static int serviceRequestIdStartEndRangeBufferedListIndex, serviceRequestIdLatestStartEndRangeBufferedListIndex;
38: private static SortedList serviceCategorySortedList, serviceSortedList, customerCategorySortedList, statusSortedList;
39: private static XDocument xDocument;
40: private static List<int> serviceIdAllowedForProcessingList;
41: private static Dictionary<int, string> systemCode;
42: private static List<Tuple<int, int>> serviceRequestIdStartEndRangeBufferedTupleList;
43: private static Dictionary<string, string> serviceToDbNameDictionary, serviceToCustomerAddressDictionary;
44: private static DateTime serviceToDbNameDictionaryAndServiceToCustomerAddressDictionaryTimestamp;
45: private static Dictionary<string, List<string>> dbNameToServiceListDictionary, accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
46: private static DateTime dbNameToServiceListDictionaryTimestamp, accessNameToServiceListWithinAllowedToBeMigratedOltDictionaryTimestamp;
47: private static Dictionary<string, string> serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
48:
49: private static readonly object objectLock = new object();
50:
51: ////////////////////////////////////////////////////////////////////////////
52:
53: /// <summary>
54: ///
55: /// </summary>
56: public ServiceRequest() { }
57:
58: ////////////////////////////////////////////////////////////////////////////
59:
60: /// <summary>
61: ///
62: /// </summary>
63: public static Dictionary<int, string> SystemCode
64: {
65: get
66: {
67: lock (objectLock)
68: {
69: if (systemCode == null || systemCode.Count == 0) systemCode = Ia.Ngn.Cl.Model.Data.ServiceRequest._SystemCode;
70:
71: return systemCode;
72: }
73: }
74: }
75:
76: ////////////////////////////////////////////////////////////////////////////
77:
78: /// <summary>
79: ///
80: /// </summary>
81: private static Dictionary<int, string> _SystemCode
82: {
83: get
84: {
85: // select code_id, code_name from system_codes
86:
87: systemCode = new Dictionary<int, string>(100)
88: {
89: [6008] = "قطع مطالبات",
90: [6009] = "قطع إدارى",
91: [4007] = "إفراج",
92: [4008] = "قيد التجديد",
93: [4009] = "قيد إخطار الجهة",
94: [4010] = "تسيل جزئي",
95: [1001] = "جديد",
96: [1002] = "مطلوب",
97: [1003] = "متاح",
98: [2001] = "قيد الموافقة الفنية",
99: [2002] = "قيد الدفع",
100: [2003] = "قيد التنفيذ",
101: [2004] = "تعذر التنفيذ",
102: [2005] = "تم التنفيذ",
103: [3001] = "نقل من رصيد خدمة - دائن",
104: [3002] = "نقل من رصيد خدمة - مدين",
105: [4001] = "سارية",
106: [4002] = "منتهية",
107: [4003] = "مستردة",
108: [4004] = "تسييل",
109: [5001] = "سارية",
110: [5002] = "منتهية",
111: [5003] = "ملغاة",
112: [4005] = "ملغاة",
113: [5004] = "مبدئية",
114: [6001] = "قيد التنفيذ",
115: [6002] = "تم التنفيذ",
116: [6003] = "تعذر التنفيذ",
117: [4006] = "جديدة",
118: [7001] = "يعمل",
119: [7002] = "قطع مؤقت",
120: [7003] = "قطع مبرمج",
121: [1004] = "مقطوع",
122: [5005] = "متأخرة السداد",
123: [7004] = "قيد التنفيذ",
124: [8001] = "قيد التنفيذ",
125: [8002] = "تم التنفيذ",
126: [8003] = "تعذر التنفيذ",
127: [1005] = "يعمل",
128: [7005] = "تعذر التنفيذ",
129: [9001] = "دائن",
130: [9002] = "مدين",
131: [7006] = "رفع نهائي",
132: [1203] = "مبدئية",
133: [1201] = "سارية",
134: [1202] = "ملغاة",
135: [1101] = "انهاء",
136: [1102] = "قطع مؤقت",
137: [1301] = "قطع مبرمج",
138: [1302] = "أمر عمل",
139: [7007] = "رفع محاسبة",
140: [1401] = "نص",
141: [1402] = "رقم",
142: [1403] = "تاريخ",
143: [1404] = "تاريخ/وقت",
144: [7008] = "قطع معاكسة",
145: [1501] = "خدمة أساسية",
146: [1502] = "تشغيل خدمة فرعية",
147: [1503] = "إيقاف خدمة فرعية",
148: [1504] = "عملية",
149: [1505] = "منتج",
150: [1510] = "خدمات متنوعة",
151: [1103] = "إعادة",
152: [1506] = "خدمة اساسية - مجموعة",
153: [1507] = "خدمة تابعة",
154: [1508] = "خدمة مدمجة",
155: [2101] = "جديد",
156: [2102] = "تم الحساب",
157: [2103] = "نسخة جديدة",
158: [2201] = "ملف المكالمات الدولية",
159: [6004] = "إعادة الحالة",
160: [1601] = "مبدئية",
161: [1602] = "نهائية",
162: [1603] = "ملغاة",
163: [2006] = "ملغاة",
164: [7009] = "ملغى",
165: [7010] = "لا يعمل",
166: [2104] = "مفقود",
167: [5006] = "سارية - مؤقتة",
168: [5007] = "مرحلة",
169: [1701] = "عادي",
170: [1702] = "نطاق",
171: [1509] = "إيقاف خدمة أساسية"
172: };
173:
174: return systemCode;
175: }
176: }
177:
178: ////////////////////////////////////////////////////////////////////////////
179:
180: /// <summary>
181: ///
182: /// </summary>
183: public static SortedList ServiceCategorySortedList
184: {
185: get
186: {
187: lock (objectLock)
188: {
189: if (serviceCategorySortedList == null || serviceCategorySortedList.Count == 0) serviceCategorySortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceCategorySortedList;
190:
191: return serviceCategorySortedList;
192: }
193: }
194: }
195:
196: ////////////////////////////////////////////////////////////////////////////
197:
198: /// <summary>
199: ///
200: /// </summary>
201: private static SortedList _ServiceCategorySortedList
202: {
203: get
204: {
205: int id;
206:
207: serviceCategorySortedList = new SortedList(10);
208:
209: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("categoryList").Elements("category"))
210: {
211: id = int.Parse(x.Attribute("id").Value);
212:
213: serviceCategorySortedList[id] = x.Attribute("arabicName").Value;
214: }
215:
216: return serviceCategorySortedList;
217: }
218: }
219:
220: ////////////////////////////////////////////////////////////////////////////
221:
222: /// <summary>
223: ///
224: /// </summary>
225: public static SortedList ServiceSortedList
226: {
227: get
228: {
229: lock (objectLock)
230: {
231: if (serviceSortedList == null || serviceSortedList.Count == 0) serviceSortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceSortedList;
232:
233: return serviceSortedList;
234: }
235: }
236: }
237:
238: ////////////////////////////////////////////////////////////////////////////
239:
240: /// <summary>
241: ///
242: /// </summary>
243: private static SortedList _ServiceSortedList
244: {
245: get
246: {
247: int id;
248:
249: serviceSortedList = new SortedList(10);
250:
251: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
252: {
253: id = int.Parse(x.Attribute("id").Value);
254:
255: serviceSortedList[id] = x.Attribute("arabicName").Value;
256: }
257:
258: return serviceSortedList;
259: }
260: }
261:
262: ////////////////////////////////////////////////////////////////////////////
263:
264: /// <summary>
265: ///
266: /// </summary>
267: public static SortedList CustomerCategorySortedList
268: {
269: get
270: {
271: lock (objectLock)
272: {
273: if (customerCategorySortedList == null || customerCategorySortedList.Count == 0) customerCategorySortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._CustomerCategorySortedList;
274:
275: return customerCategorySortedList;
276: }
277: }
278: }
279:
280: ////////////////////////////////////////////////////////////////////////////
281:
282: /// <summary>
283: ///
284: /// </summary>
285: private static SortedList _CustomerCategorySortedList
286: {
287: get
288: {
289: int id;
290:
291: customerCategorySortedList = new SortedList(10);
292:
293: foreach (XElement x in XDocument.Element("serviceRequest").Elements("customer").Elements("categoryList").Elements("category"))
294: {
295: id = int.Parse(x.Attribute("id").Value);
296:
297: customerCategorySortedList[id] = x.Attribute("arabicName").Value;
298: }
299:
300: return customerCategorySortedList;
301: }
302: }
303:
304: ////////////////////////////////////////////////////////////////////////////
305:
306: /// <summary>
307: ///
308: /// </summary>
309: public static SortedList StatusSortedList
310: {
311: get
312: {
313: lock (objectLock)
314: {
315: if (statusSortedList == null || statusSortedList.Count == 0) statusSortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._StatusSortedList;
316:
317: return statusSortedList;
318: }
319: }
320: }
321:
322: ////////////////////////////////////////////////////////////////////////////
323:
324: /// <summary>
325: ///
326: /// </summary>
327: private static SortedList _StatusSortedList
328: {
329: get
330: {
331: int id;
332:
333: statusSortedList = new SortedList(10);
334:
335: foreach (XElement x in XDocument.Element("serviceRequest").Elements("statusList").Elements("status"))
336: {
337: id = int.Parse(x.Attribute("id").Value);
338:
339: statusSortedList[id] = x.Attribute("arabicName").Value;
340: }
341:
342: return statusSortedList;
343: }
344: }
345:
346: ////////////////////////////////////////////////////////////////////////////
347:
348: /// <summary>
349: ///
350: /// </summary>
351: public static List<int> ServiceIdAllowedForProcessingList
352: {
353: get
354: {
355: lock (objectLock)
356: {
357: if (serviceIdAllowedForProcessingList == null || serviceIdAllowedForProcessingList.Count == 0) serviceIdAllowedForProcessingList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceIdAllowedForProcessingList;
358:
359: return serviceIdAllowedForProcessingList;
360: }
361: }
362: }
363:
364: ////////////////////////////////////////////////////////////////////////////
365:
366: /// <summary>
367: ///
368: /// </summary>
369: private static List<int> _ServiceIdAllowedForProcessingList
370: {
371: get
372: {
373: int id;
374:
375: serviceIdAllowedForProcessingList = new List<int>(100);
376:
377: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
378: {
379: if (x.HasAttributes && x.Attribute("allowProcessing") != null)
380: {
381: if (x.Attribute("allowProcessing").Value == "true")
382: {
383: id = int.Parse(x.Attribute("id").Value);
384: serviceIdAllowedForProcessingList.Add(id);
385: }
386: }
387: }
388:
389: return serviceIdAllowedForProcessingList;
390: }
391: }
392:
393: ////////////////////////////////////////////////////////////////////////////
394: ////////////////////////////////////////////////////////////////////////////
395:
396: /// <summary>
397: ///
398: /// </summary>
399: public static Tuple<int, int> ServiceRequestIdStartEndRangeManager(ref int index, out string result)
400: {
401: int count, edgeBufferRange, lastestRangeList;
402: Tuple<int, int> tuple;
403: List<int> serviceRequestIdList;
404:
405: count = 10;
406: edgeBufferRange = 5000;
407: lastestRangeList = 21;
408:
409: if (serviceRequestIdStartEndRangeBufferedTupleList == null || serviceRequestIdStartEndRangeBufferedListIndex == 0)
410: {
411: serviceRequestIdStartEndRangeBufferedListIndex = index;
412:
413: using (var db = new Ia.Ngn.Cl.Model.Ngn())
414: {
415: serviceRequestIdList = (from sr in db.ServiceRequests orderby sr.Id ascending select sr.Id).ToList();
416: }
417:
418: serviceRequestIdStartEndRangeBufferedTupleList = Ia.Ngn.Cl.Model.Business.ServiceRequest.OptimizedStartEndRangeBufferedList(serviceRequestIdList, count, edgeBufferRange);
419: }
420:
421: if (serviceRequestIdStartEndRangeBufferedTupleList.Count > 0)
422: {
423: if (Ia.Ngn.Cl.Model.Business.Administration.NowIsOfficialWorkingTime)
424: {
425: // flip between last and latest range to favor reading fresh work orders quickly
426: if (Ia.Cl.Model.Default.RandomBool)
427: {
428: serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - Ia.Cl.Model.Default.Random(lastestRangeList) - 1;
429: serviceRequestIdLatestStartEndRangeBufferedListIndex = (serviceRequestIdLatestStartEndRangeBufferedListIndex >= 0) ? serviceRequestIdLatestStartEndRangeBufferedListIndex : 0;
430: }
431: else serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - 1;
432:
433: tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdLatestStartEndRangeBufferedListIndex];
434:
435: if (serviceRequestIdLatestStartEndRangeBufferedListIndex == serviceRequestIdStartEndRangeBufferedTupleList.Count - 1)
436: {
437: tuple = new Tuple<int, int>(tuple.Item1, tuple.Item2 + edgeBufferRange);
438: }
439:
440: result = "(latest: " + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdLatestStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
441: }
442: else
443: {
444: tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdStartEndRangeBufferedListIndex];
445:
446: result = "(historic: " + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
447:
448: serviceRequestIdStartEndRangeBufferedListIndex = Ia.Cl.Model.Default.IncrementListIndexOrRestart(serviceRequestIdStartEndRangeBufferedTupleList, serviceRequestIdStartEndRangeBufferedListIndex);
449: }
450: }
451: else
452: {
453: result = "(0-0 0/0)";
454:
455: tuple = null;
456: }
457:
458: index = serviceRequestIdStartEndRangeBufferedListIndex;
459:
460: return tuple;
461: }
462:
463: ////////////////////////////////////////////////////////////////////////////
464:
465: /// <summary>
466: ///
467: /// </summary>
468: public static string AlterSessionOfCustomerDepartmentOracleDatabase
469: {
470: get
471: {
472: return @"alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS'";
473: }
474: }
475:
476: ////////////////////////////////////////////////////////////////////////////
477:
478: /// <summary>
479: /// Return list of service requests
480: /// </summary>
481: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List()
482: {
483: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
484:
485: using (var db = new Ia.Ngn.Cl.Model.Ngn())
486: {
487: serviceRequestList = (from sr in db.ServiceRequests select sr).ToList();
488: }
489:
490: return serviceRequestList;
491: }
492:
493: ////////////////////////////////////////////////////////////////////////////
494:
495: /// <summary>
496: /// Return list of service requests with number
497: /// </summary>
498: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(int number)
499: {
500: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
501:
502: using (var db = new Ia.Ngn.Cl.Model.Ngn())
503: {
504: serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
505: }
506:
507: return serviceRequestList;
508: }
509:
510: ////////////////////////////////////////////////////////////////////////////
511:
512: /// <summary>
513: /// Return list of service requests with service
514: /// </summary>
515: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(string service)
516: {
517: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
518:
519: if (!string.IsNullOrEmpty(service))
520: {
521: if (int.TryParse(service, out int number))
522: {
523: using (var db = new Ia.Ngn.Cl.Model.Ngn())
524: {
525: serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
526: }
527: }
528: else
529: {
530: throw new ArgumentException(@"List(): service is not a number, service: " + service);
531: }
532: }
533: else serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
534:
535: return serviceRequestList;
536: }
537:
538: ////////////////////////////////////////////////////////////////////////////
539:
540: /// <summary>
541: /// Return a list of service requests that have numbers (including changed-to numbers) within the passed list
542: /// </summary>
543: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<int> numberList)
544: {
545: List<string> serviceList;
546:
547: serviceList = (from n in numberList select n.ToString()).ToList();
548:
549: return List(serviceList);
550: }
551:
552: ////////////////////////////////////////////////////////////////////////////
553:
554: /// <summary>
555: /// Return a list of service requests that have numbers (including changed-to numbers) within the passed list
556: /// </summary>
557: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<string> serviceList)
558: {
559: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList, viaServiceRequestTypeList;
560:
561: using (var db = new Ia.Ngn.Cl.Model.Ngn())
562: {
563: serviceRequestList = (from sr in db.ServiceRequests
564: where serviceList.Contains(sr.Number.ToString())
565: select sr).Include(a => a.ServiceRequestService).Include(a => a.ServiceRequestTypes).ToList();
566:
567: viaServiceRequestTypeList = (from srt in db.ServiceRequestTypes
568: join sr in db.ServiceRequests on srt.ServiceRequest.Id equals sr.Id
569: where srt.TypeId == 11 && serviceList.Contains(srt.Value)
570: select sr).Include(a => a.ServiceRequestService).ToList();
571:
572: serviceRequestList = serviceRequestList.Union(viaServiceRequestTypeList).Distinct().ToList();
573: }
574:
575: return serviceRequestList.ToList();
576: }
577:
578: ////////////////////////////////////////////////////////////////////////////
579:
580: /// <summary>
581: /// Return a list of service requests that have numbers-serials within the passed list
582: /// </summary>
583: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<Ia.Ngn.Cl.Model.Business.ServiceRequest.NumberSerial> numberSerialList)
584: {
585: List<long> idList;
586: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
587:
588: idList = numberSerialList.IdList();
589:
590: using (var db = new Ia.Ngn.Cl.Model.Ngn())
591: {
592: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
593: where
594: //numberSerialList.Contains(q.Number, q.Serial) does not work
595: //((from r in numberSerialList where r.Number == q.Number && r.Serial == q.Serial select r) != null) does not work
596: //numberList.Any<int>(i=> i == q.Number) does not work
597: idList.Contains((long)sr.Number * 100 + sr.Serial)
598: select sr).ToList();
599: }
600:
601: return serviceRequestList;
602: }
603:
604: ////////////////////////////////////////////////////////////////////////////
605:
606: /// <summary>
607: /// Return a list of numbers, including changed-to numbers, that are related to the passed number list
608: /// </summary>
609: public static List<int> OldNumberAndChangedAndChangedToList(List<int> numberList)
610: {
611: List<int> number1List, number2List, number3List;//, number4List, number5List;
612: List<string> serviceList; //, service1List, service2List, service3List, service4List, service5List;
613: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList, serviceRequest1List, serviceRequest2List, serviceRequest3List, serviceRequest4List, serviceRequest5List;
614:
615: using (var db = new Ia.Ngn.Cl.Model.Ngn())
616: {
617: serviceList = (from n in numberList select n.ToString()).ToList();
618:
619: serviceRequest1List = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
620: where numberList.Contains(sr.Number)
621: select sr).ToList();
622:
623: if (serviceRequest1List.Count > 0)
624: {
625: number1List = (from sr in serviceRequest1List select sr.Number).Distinct().ToList();
626:
627: number2List = new List<int>();
628:
629: foreach (var srt in serviceRequest1List.SelectMany(u => u.ServiceRequestTypes))
630: {
631: if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) number2List.Add(i);
632: }
633:
634: // below: this is the collect first changed-to number information
635: if (number2List.Count > 0)
636: {
637: serviceRequest2List = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
638: where number2List.Contains(sr.Number)
639: select sr).ToList();
640:
641: if (serviceRequest2List.Count > 0)
642: {
643: number1List = (from sr in serviceRequest2List select sr.Number).Distinct().ToList();
644:
645: number3List = new List<int>();
646:
647: foreach (var srt in serviceRequest2List.SelectMany(u => u.ServiceRequestTypes))
648: {
649: if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) number3List.Add(i);
650: }
651:
652: // below: this is the collect second changed-to number information
653: if (number3List.Count > 0)
654: {
655: serviceRequest3List = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
656: where number3List.Contains(sr.Number)
657: select sr).ToList();
658: }
659: else serviceRequest3List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
660: }
661: else
662: {
663: serviceRequest2List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
664: serviceRequest3List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
665: }
666: }
667: else
668: {
669: serviceRequest2List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
670: serviceRequest3List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
671: }
672: }
673: else
674: {
675: serviceRequest2List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
676: serviceRequest3List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
677: }
678:
679:
680: //
681: serviceRequest4List = (from srt in db.ServiceRequestTypes.Include(a => a.ServiceRequest)
682: where srt.TypeId == 11 && serviceList.Contains(srt.Value)
683: select srt.ServiceRequest).ToList();
684:
685: if (serviceRequest4List.Count > 0)
686: {
687: number1List = (from sr in serviceRequest4List select sr.Number).Distinct().ToList();
688:
689: serviceRequest5List = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
690: where number1List.Contains(sr.Number)
691: select sr).ToList();
692: }
693: else serviceRequest5List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
694:
695: //
696: serviceRequestList = serviceRequest1List.Union(serviceRequest2List).Union(serviceRequest3List).Union(serviceRequest4List).Union(serviceRequest5List).Distinct().ToList();
697:
698: if (serviceRequestList.Count > 0)
699: {
700: numberList = (from n in serviceRequestList select n.Number).Distinct().ToList();
701: }
702: else numberList = new List<int>();
703: }
704:
705: return numberList.ToList();
706: }
707:
708: ////////////////////////////////////////////////////////////////////////////
709:
710: /// <summary>
711: /// Return a list of numbers, including changed-to numbers, that are related to the passed number list
712: /// </summary>
713: public static List<int> NumberAndChangedAndChangedToList(List<int> numberList)
714: {
715: List<int> list;
716:
717: list = new List<int>();
718:
719: _NumberAndChangedAndChangedToList(numberList, ref list);
720:
721: return list;
722: }
723:
724: ////////////////////////////////////////////////////////////////////////////
725:
726: /// <summary>
727: /// Return a list of numbers, including changed-to numbers, that are related to the passed number list
728: /// </summary>
729: private static void _NumberAndChangedAndChangedToList(List<int> numberList, ref List<int> list)
730: {
731: List<int> _list, changedList, changedToList, changedExceptList, changedToExceptList;
732: List<string> serviceList;
733: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequests;
734:
735: changedList = new List<int>();
736: changedToList = new List<int>();
737:
738: using (var db = new Ia.Ngn.Cl.Model.Ngn())
739: {
740: serviceList = (from n in numberList select n.ToString()).ToList();
741:
742: // list
743: serviceRequests = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes) where numberList.Contains(sr.Number) select sr).ToList();
744:
745: _list = (from sr in serviceRequests select sr.Number).Distinct().ToList();
746:
747: list = list.Union(_list).Distinct().ToList();
748:
749: // changedToList
750: foreach (var srt in serviceRequests.SelectMany(u => u.ServiceRequestTypes))
751: {
752: if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) changedToList.Add(i);
753: }
754:
755: // changedList
756: serviceRequests = (from srt in db.ServiceRequestTypes.Include(a => a.ServiceRequest) where srt.TypeId == 11 && serviceList.Contains(srt.Value) select srt.ServiceRequest).ToList();
757: changedList = (from sr in serviceRequests select sr.Number).Distinct().ToList();
758:
759: changedToExceptList = changedToList.Except(list).ToList();
760: if (changedToExceptList.Count > 0) _NumberAndChangedAndChangedToList(changedToExceptList, ref list);
761:
762: list = list.Union(changedToExceptList).Distinct().ToList();
763:
764: changedExceptList = changedList.Except(list).ToList();
765: if (changedExceptList.Count > 0) _NumberAndChangedAndChangedToList(changedExceptList, ref list);
766:
767: list = list.Union(changedExceptList).Distinct().ToList();
768: }
769: }
770:
771: ////////////////////////////////////////////////////////////////////////////
772: ////////////////////////////////////////////////////////////////////////////
773:
774: /// <summary>
775: /// Return a list of service requests that have numbers within the passed domain list
776: /// </summary>
777: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListByDomain(List<int> domainList)
778: {
779: List<string> stringDomainList;
780: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
781:
782: stringDomainList = new List<string>();
783:
784: using (var db = new Ia.Ngn.Cl.Model.Ngn())
785: {
786: if (domainList != null)
787: {
788: foreach (int i in domainList) stringDomainList.Add(i.ToString());
789:
790: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
791: where domainList.Any(u => sr.Number.ToString().StartsWith(u.ToString()))
792: select sr).ToList();
793: }
794: else
795: {
796: serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
797: }
798: }
799:
800: return serviceRequestList;
801: }
802:
803: ////////////////////////////////////////////////////////////////////////////
804:
805: /// <summary>
806: ///
807: /// </summary>
808: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinDateTimeRange(DateTime startDateTime, DateTime endDateTime)
809: {
810: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
811:
812: using (var db = new Ia.Ngn.Cl.Model.Ngn())
813: {
814: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
815: where sr.RequestDateTime >= startDateTime && sr.RequestDateTime < endDateTime
816: select sr).ToList();
817: }
818:
819: return serviceRequestList;
820: }
821:
822: ////////////////////////////////////////////////////////////////////////////
823:
824: /// <summary>
825: ///
826: /// </summary>
827: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinIdRange(int start, int end)
828: {
829: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
830:
831: using (var db = new Ia.Ngn.Cl.Model.Ngn())
832: {
833: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
834: where sr.Id >= start && sr.Id <= end
835: select sr).ToList();
836: }
837:
838: return serviceRequestList;
839: }
840:
841: ////////////////////////////////////////////////////////////////////////////
842: ////////////////////////////////////////////////////////////////////////////
843:
844: /// <summary>
845: ///
846: /// </summary>
847: public static void UpdateWithServiceList(List<string> serviceList, List<Ia.Ngn.Cl.Model.ServiceRequest> newServiceRequestList, out string result)
848: {
849: int readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount, serviceRequestId;
850: string serviceRequestServiceId;
851: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
852:
853: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
854: result = string.Empty;
855:
856: readItemCount = newServiceRequestList.Count;
857:
858: using (var db = new Ia.Ngn.Cl.Model.Ngn())
859: {
860: foreach (Ia.Ngn.Cl.Model.ServiceRequest newServiceRequest in newServiceRequestList)
861: {
862: serviceRequestId = newServiceRequest.Id;
863:
864: serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequestId select sr).SingleOrDefault();
865:
866: existingItemCount = (serviceRequest != null) ? 1 : 0;
867:
868: try
869: {
870: if (newServiceRequest.ServiceRequestService != null)
871: {
872: serviceRequestServiceId = newServiceRequest.ServiceRequestService.Id;
873:
874: newServiceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestServiceId select srs).SingleOrDefault();
875: }
876:
877: if (serviceRequest.Update(newServiceRequest))
878: {
879: db.ServiceRequests.Attach(serviceRequest);
880: db.Entry(serviceRequest).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
881:
882: updatedItemCount++;
883: }
884: }
885: catch (Exception)// e)
886: {
887:
888: }
889: }
890:
891: db.SaveChanges();
892:
893: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") ";
894: }
895: }
896:
897: ////////////////////////////////////////////////////////////////////////////
898:
899: /// <summary>
900: ///
901: /// </summary>
902: public static bool UpdateServiceRequestService(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, Ia.Ngn.Cl.Model.ServiceRequestService serviceRequestService, out string result)
903: {
904: bool b;
905:
906: using (var db = new Ia.Ngn.Cl.Model.Ngn())
907: {
908: serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequest.Id select sr).SingleOrDefault();
909:
910: if (serviceRequest.ServiceRequestService != serviceRequestService)
911: {
912: serviceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestService.Id select srs).SingleOrDefault();
913:
914: db.ServiceRequests.Attach(serviceRequest);
915: db.Entry(serviceRequest).Property(u => u.ServiceRequestService).IsModified = true;
916:
917: db.SaveChanges();
918:
919: result = "Success: ServiceRequests ServiceRequestService updated. ";
920: b = true;
921: }
922: else
923: {
924: result = "Warning: ServiceRequests ServiceRequestService value was not updated because its the same. ";
925:
926: b = false;
927: }
928: }
929:
930: return b;
931: }
932:
933: ////////////////////////////////////////////////////////////////////////////
934:
935: /// <summary>
936: ///
937: /// </summary>
938: public static void UpdateForADateTimeRangeWithOutputDataTable(DataTable dataTable, DateTime dateTime, out string result)
939: {
940: // below: the SQL statement should be within the dataTable.TableName variable
941: int number, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
942: int serviceRequestId;
943: string sql, r, customerAddress;
944: ArrayList newServiceRequestIdArryList;
945: DateTime startDateTime, endDateTime;
946: Match match;
947: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
948: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
949: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
950: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
951:
952: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
953: result = r = string.Empty;
954:
955: startDateTime = DateTime.MinValue;
956:
957: if (dataTable != null)
958: {
959: sql = dataTable.TableName;
960:
961: // select * from SRV_REQ_FIPER where REQ_DATE >= 'dd/MM/yyyy' and REQ_DATE < 'dd/MM/yyyy' order by REQ_DATE ASC, SRV_REQ_ID ASC;
962: // select * from SRV_REQ_FIPER where REQ_DATE >= '01-10-2006' and REQ_DATE < '02-10-2006' order by REQ_DATE asc, SRV_REQ_ID asc;
963:
964: match = Regex.Match(sql, @".+'(\d{2})\/(\d{2})\/(\d{4})'.+'(\d{2})\/(\d{2})\/(\d{4})'.+", RegexOptions.Singleline);
965: // 1 2 3 4 5 6
966:
967: if (match.Success)
968: {
969: using (var db = new Ia.Ngn.Cl.Model.Ngn())
970: {
971: readItemCount = dataTable.Rows.Count;
972:
973: //if (dataTable.Rows.Count > 0)
974: //{
975: startDateTime = DateTime.Parse(match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value);
976: endDateTime = DateTime.Parse(match.Groups[6].Value + "-" + match.Groups[5].Value + "-" + match.Groups[4].Value);
977:
978: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ListWithinDateTimeRange(startDateTime, endDateTime);
979: existingItemCount = serviceRequestList.Count;
980:
981: newServiceRequestIdArryList = new ArrayList(dataTable.Rows.Count + 1);
982:
983: foreach (DataRow dataRow in dataTable.Rows)
984: {
985: number = int.Parse(dataRow["SRV_NO"].ToString());
986:
987: if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
988: {
989: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
990:
991: customerAddress = dataRow["ADDRESS"].ToString();
992: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
993:
994: newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
995: {
996: Id = serviceRequestId,
997: Number = number,
998:
999: CustomerAddress = customerAddress,
1000: AreaId = serviceAddress.AreaId,
1001:
1002: CustomerCategoryId = int.Parse(dataRow["CUST_CAT_ID"].ToString()),
1003: CustomerId = int.Parse(dataRow["ACCOUNT_NO"].ToString()),
1004: CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
1005: RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
1006: Serial = int.Parse(dataRow["SRV_SER_NO"].ToString()),
1007: ServiceCategoryId = int.Parse(dataRow["SRV_CAT_ID"].ToString()),
1008: ServiceId = int.Parse(dataRow["SRV_ID"].ToString()),
1009: Balance = double.Parse(dataRow["BALANCE"].ToString()),
1010: Status = int.Parse(dataRow["STATUS"].ToString())
1011: };
1012:
1013: serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
1014:
1015: if (serviceRequest == null)
1016: {
1017: newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
1018:
1019: db.ServiceRequests.Add(newServiceRequest);
1020:
1021: insertedItemCount++;
1022: }
1023: else
1024: {
1025: // below: copy values from newServiceRequest to serviceRequest
1026:
1027: if (serviceRequest.Update(newServiceRequest))
1028: {
1029: db.ServiceRequests.Attach(serviceRequest);
1030: db.Entry(serviceRequest).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
1031:
1032: updatedItemCount++;
1033: }
1034: }
1035:
1036: newServiceRequestIdArryList.Add(serviceRequestId);
1037: }
1038: else
1039: {
1040: r += "Number: " + number + " is not within allowed domain list, ";
1041: }
1042: }
1043:
1044: // below: this function will remove values that were not present in the reading
1045: if (serviceRequestList.Count > 0)
1046: {
1047: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
1048: {
1049: if (!newServiceRequestIdArryList.Contains(sr.Id))
1050: {
1051: serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
1052:
1053: db.ServiceRequests.Remove(serviceRequest);
1054:
1055: // below: we will also remove SRT records referensing this SR
1056: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
1057:
1058: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
1059:
1060: deletedItemCount++;
1061: }
1062: }
1063: }
1064:
1065: db.SaveChanges();
1066:
1067: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
1068: //}
1069: //else
1070: //{
1071: // result = "(" + readItemCount + "/?/?) ";
1072: //}
1073: }
1074: }
1075: else
1076: {
1077: result = "(?/?/?: SQL in TableName is unmatched) ";
1078: }
1079: }
1080: else
1081: {
1082: result = "(dataTable == null/?/?) ";
1083: }
1084: }
1085:
1086: ////////////////////////////////////////////////////////////////////////////
1087:
1088: /// <summary>
1089: ///
1090: /// </summary>
1091: public static string UpdateForServiceRequestWithOutputDataTableAndIdRange(DataTable dataTable, out List<string> insertedOrUpdatedOrDeletedServiceList)
1092: {
1093: // below: the SQL statement should be within the dataTable.TableName variable
1094: int serviceRequestId, start, end, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
1095: string sql, r, customerAddress, result;
1096: ArrayList newServiceRequestIdArrayList;
1097: Match match;
1098: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
1099: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
1100: List<int> numbersNotWithinAllowedDomainList;
1101: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1102: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
1103:
1104: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
1105: result = r = string.Empty;
1106: numbersNotWithinAllowedDomainList = new List<int>();
1107:
1108: insertedOrUpdatedOrDeletedServiceList = new List<string>();
1109:
1110: if (dataTable != null)
1111: {
1112: sql = dataTable.TableName;
1113:
1114: // select * from SRV_REQ_FIPER where SRV_REQ_ID >= 110000 and SRV_REQ_ID <= 321203 order by REQ_DATE asc, SRV_REQ_ID asc
1115: match = Regex.Match(sql, @"SRV_REQ_ID >= (\d+) and SRV_REQ_ID <= (\d+) ", RegexOptions.Singleline);
1116: // 1 2
1117:
1118: if (match.Success)
1119: {
1120: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1121: {
1122: readItemCount = dataTable.Rows.Count;
1123:
1124: start = int.Parse(match.Groups[1].Value);
1125: end = int.Parse(match.Groups[2].Value);
1126:
1127: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ListWithinIdRange(start, end);
1128: existingItemCount = serviceRequestList.Count;
1129:
1130: newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
1131:
1132: foreach (DataRow dataRow in dataTable.Rows)
1133: {
1134: if (int.TryParse(dataRow["SRV_NO"].ToString(), out int number))
1135: {
1136: if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
1137: {
1138: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
1139:
1140: if (Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceRequestIdIsAllowedForProcessing(serviceRequestId))
1141: {
1142: customerAddress = dataRow["ADDRESS"].ToString();
1143:
1144: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
1145:
1146: newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
1147: {
1148: Id = serviceRequestId,
1149: Number = number,
1150:
1151: CustomerAddress = customerAddress,
1152: AreaId = serviceAddress.AreaId,
1153:
1154: CustomerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0,
1155: CustomerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0,
1156: CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
1157: RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
1158: Serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0,
1159: ServiceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0,
1160: ServiceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0,
1161: Balance = double.Parse(dataRow["BALANCE"].ToString()),
1162:
1163: Status = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0
1164: };
1165:
1166: serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
1167:
1168: if (serviceRequest == null)
1169: {
1170: newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
1171:
1172: db.ServiceRequests.Add(newServiceRequest);
1173:
1174: insertedOrUpdatedOrDeletedServiceList.Add(newServiceRequest.Number.ToString());
1175: insertedItemCount++;
1176: }
1177: else
1178: {
1179: // below: copy values from newServiceRequest to serviceRequest
1180:
1181: if (serviceRequest.UpdateSkipServiceRequestService(newServiceRequest))
1182: {
1183: db.ServiceRequests.Attach(serviceRequest);
1184: db.Entry(serviceRequest).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
1185:
1186: insertedOrUpdatedOrDeletedServiceList.Add(serviceRequest.Number.ToString());
1187: updatedItemCount++;
1188: }
1189: }
1190:
1191: newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
1192: }
1193: else
1194: {
1195:
1196: }
1197: }
1198: else
1199: {
1200: numbersNotWithinAllowedDomainList.Add(number);
1201: }
1202: }
1203: else
1204: {
1205: //
1206: }
1207: }
1208:
1209: /*
1210: if (numbersNotWithinAllowedDomainList.Count > 0)
1211: {
1212: r = "Numbers not within allowed domain list: ";
1213:
1214: foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
1215:
1216: r = r.Trim(',');
1217: }
1218: */
1219:
1220: // below: this function will remove values that were not present in the reading
1221: if (serviceRequestList.Count > 0)
1222: {
1223: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
1224: {
1225: if (!newServiceRequestIdArrayList.Contains(sr.Id))
1226: {
1227: serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
1228:
1229: db.ServiceRequests.Remove(serviceRequest);
1230:
1231: // below: we will also remove SRT records referensing this SR
1232: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
1233:
1234: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
1235:
1236: insertedOrUpdatedOrDeletedServiceList.Add(serviceRequest.Number.ToString());
1237: deletedItemCount++;
1238: }
1239: }
1240: }
1241:
1242: db.SaveChanges();
1243:
1244: //if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
1245: //else isUpdated = false;
1246:
1247: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
1248: }
1249: }
1250: else
1251: {
1252: result = "(?/?/?: SQL in TableName is unmatched) ";
1253: }
1254: }
1255: else
1256: {
1257: result = "(dataTable == null/?/?) ";
1258: }
1259:
1260: return result;
1261: }
1262:
1263: ////////////////////////////////////////////////////////////////////////////
1264:
1265: /// <summary>
1266: ///
1267: /// </summary>
1268: public static void UpdateForServiceRequestWithOutputDataTableAndService(DataTable dataTable, string service, out bool isUpdated, out string result)
1269: {
1270: int serviceRequestId, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
1271: string sql, sqlService, r, customerAddress;
1272: ArrayList newServiceRequestIdArrayList;
1273: Match match;
1274: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
1275: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
1276: List<int> numbersNotWithinAllowedDomainList;
1277: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1278: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
1279:
1280: isUpdated = false;
1281: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
1282: result = r = string.Empty;
1283: numbersNotWithinAllowedDomainList = new List<int>();
1284:
1285: if (dataTable != null)
1286: {
1287: // below: the SQL statement should be within the dataTable.TableName variable
1288: sql = dataTable.TableName;
1289:
1290: // select * from SRV_REQ_FIPER where SRV_NO = 23632222 order by SRV_REQ_ID asc
1291: match = Regex.Match(sql, @"SRV_NO = (\d+) order by SRV_REQ_ID asc", RegexOptions.Singleline);
1292:
1293: if (match.Success)
1294: {
1295: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1296: {
1297: readItemCount = dataTable.Rows.Count;
1298:
1299: sqlService = match.Groups[1].Value;
1300:
1301: if (service == sqlService)
1302: {
1303: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(service);
1304: existingItemCount = serviceRequestList.Count;
1305:
1306: newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
1307:
1308: foreach (DataRow dataRow in dataTable.Rows)
1309: {
1310: if (int.TryParse(dataRow["SRV_NO"].ToString(), out int number))
1311: {
1312: if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
1313: {
1314: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
1315: customerAddress = dataRow["ADDRESS"].ToString();
1316:
1317: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
1318:
1319: newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
1320: {
1321: Id = serviceRequestId,
1322: Number = number,
1323: CustomerAddress = customerAddress,
1324: AreaId = serviceAddress.AreaId,
1325: CustomerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0,
1326: CustomerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0,
1327: CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
1328: RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
1329: Serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0,
1330: ServiceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0,
1331: ServiceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0,
1332: Balance = double.Parse(dataRow["BALANCE"].ToString()),
1333: Status = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0
1334: };
1335:
1336: serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
1337:
1338: if (serviceRequest == null)
1339: {
1340: newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
1341:
1342: db.ServiceRequests.Add(newServiceRequest);
1343:
1344: insertedItemCount++;
1345: }
1346: else
1347: {
1348: // below: copy values from newServiceRequest to serviceRequest
1349:
1350: if (serviceRequest.UpdateSkipServiceRequestService(newServiceRequest))
1351: {
1352: db.ServiceRequests.Attach(serviceRequest);
1353: db.Entry(serviceRequest).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
1354:
1355: updatedItemCount++;
1356: }
1357: }
1358:
1359: newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
1360: }
1361: else
1362: {
1363: numbersNotWithinAllowedDomainList.Add(number);
1364: }
1365: }
1366: else
1367: {
1368: //
1369: }
1370: }
1371:
1372: /*
1373: if (numbersNotWithinAllowedDomainList.Count > 0)
1374: {
1375: r = "Numbers not within allowed domain list: ";
1376:
1377: foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
1378:
1379: r = r.Trim(',');
1380: }
1381: */
1382:
1383: // below: this function will remove values that were not present in the reading
1384: if (serviceRequestList.Count > 0)
1385: {
1386: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
1387: {
1388: if (!newServiceRequestIdArrayList.Contains(sr.Id))
1389: {
1390: serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
1391:
1392: db.ServiceRequests.Remove(serviceRequest);
1393:
1394: // below: we will also remove SRT records referencing this SR
1395: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
1396:
1397: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
1398:
1399: deletedItemCount++;
1400: }
1401: }
1402: }
1403:
1404: db.SaveChanges();
1405:
1406: if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
1407: else isUpdated = false;
1408:
1409: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
1410: }
1411: else
1412: {
1413: throw new ArgumentException(@"UpdateForServiceRequestWithOutputDataTableAndService(): service != sqlService, service: " + service + ", sqlService: " + sqlService);
1414: }
1415: }
1416: }
1417: else
1418: {
1419: result = "(?/?/?: SQL in TableName is unmatched) ";
1420: }
1421: }
1422: else
1423: {
1424: result = "(dataTable == null/?/?) ";
1425: }
1426: }
1427:
1428: ////////////////////////////////////////////////////////////////////////////
1429:
1430: /// <summary>
1431: ///
1432: /// </summary>
1433: public static Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> ServiceToServiceRequestDictionary(List<int> domainList)
1434: {
1435: string key;
1436: List<string> stringDomainList;
1437: List<Ia.Ngn.Cl.Model.ServiceRequest> list;
1438: Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> dictionary;
1439:
1440: stringDomainList = new List<string>();
1441:
1442: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1443: {
1444: if (domainList != null)
1445: {
1446: foreach (int i in domainList) stringDomainList.Add(i.ToString());
1447:
1448: list = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService).Include(a => a.ServiceRequestTypes) where domainList.Any(u => sr.Number.ToString().StartsWith(u.ToString())) orderby sr.RequestDateTime ascending select sr).ToList();
1449:
1450: dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>(list.Count);
1451:
1452: foreach (var sr in list)
1453: {
1454: key = sr.Number.ToString();
1455:
1456: if (dictionary.ContainsKey(key))
1457: {
1458: dictionary[key] = sr;
1459: }
1460: else dictionary[key] = sr;
1461: }
1462: }
1463: else
1464: {
1465: dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>();
1466: }
1467: }
1468:
1469: return dictionary;
1470: }
1471:
1472: ////////////////////////////////////////////////////////////////////////////
1473:
1474: /// <summary>
1475: ///
1476: /// </summary>
1477: public static Hashtable NumberToCustomerAddressHashtable(List<int> domainList)
1478: {
1479: Hashtable ht;
1480: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1481:
1482: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1483: {
1484: if (domainList != null)
1485: {
1486: serviceRequestList = (from sr in db.ServiceRequests where domainList.Contains(sr.Number / 10000) || domainList.Contains(sr.Number / 1000) select sr).ToList();
1487:
1488: ht = new Hashtable(serviceRequestList.Count);
1489:
1490: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList.OrderBy(u => u.Id)) ht[sr.Number.ToString()] = sr.CustomerAddress;
1491: }
1492: else
1493: {
1494: ht = new Hashtable();
1495: }
1496: }
1497:
1498: return ht;
1499: }
1500:
1501: ////////////////////////////////////////////////////////////////////////////
1502:
1503: /// <summary>
1504: /// Return the latest RequestDateTime for number
1505: /// </summary>
1506: public static DateTime LatestRequestDateTime(int number)
1507: {
1508: DateTime dateTime;
1509: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
1510: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1511:
1512: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1513: {
1514: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(number);
1515:
1516: if (serviceRequestList.Count > 0)
1517: {
1518: serviceRequest = serviceRequestList.OrderByDescending(u => u.Id).FirstOrDefault();
1519:
1520: dateTime = serviceRequest.RequestDateTime;
1521: }
1522: else
1523: {
1524: dateTime = DateTime.MinValue;
1525: }
1526: }
1527:
1528: return dateTime;
1529: }
1530:
1531: ////////////////////////////////////////////////////////////////////////////
1532:
1533: /// <summary>
1534: ///
1535: /// </summary>
1536: public static List<Ia.Ngn.Cl.Model.ServiceRequest> LastN(int numberOfServiceRequests)
1537: {
1538: // below:
1539: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1540:
1541: serviceRequestList = null;
1542:
1543: try
1544: {
1545: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1546: {
1547: serviceRequestList = (from sr in db.ServiceRequests orderby sr.RequestDateTime descending select sr).Take(numberOfServiceRequests).ToList();
1548: }
1549: }
1550: catch (Exception)
1551: {
1552: //resultLabel.Text = "Error during retrieval of data for \"" + ip + "\": " + ex.Message + ". ";
1553: //resultLabel.CssClass = "error";
1554: }
1555:
1556: return serviceRequestList;
1557: }
1558:
1559: ////////////////////////////////////////////////////////////////////////////
1560:
1561: /// <summary>
1562: ///
1563: /// </summary>
1564: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ForRequestDate(DateTime requestDate)
1565: {
1566: // below:
1567: DateTime nextDate;
1568: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1569:
1570: serviceRequestList = null;
1571:
1572: // below: 00:00 time values
1573: requestDate = requestDate.Date;
1574:
1575: nextDate = requestDate.AddDays(1);
1576:
1577: try
1578: {
1579: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1580: {
1581: serviceRequestList = (from sr in db.ServiceRequests
1582: where sr.RequestDateTime >= requestDate && sr.RequestDateTime < nextDate
1583: orderby sr.RequestDateTime descending
1584: select sr).ToList();
1585: }
1586: }
1587: catch (Exception)// e)
1588: {
1589: //resultLabel.Text = "Error during retrieval of data for \"" + ip + "\": " + ex.Message + ". ";
1590: //resultLabel.CssClass = "error";
1591: }
1592:
1593: return serviceRequestList;
1594: }
1595:
1596: ////////////////////////////////////////////////////////////////////////////
1597: ////////////////////////////////////////////////////////////////////////////
1598:
1599: /// <summary>
1600: ///
1601: /// </summary>
1602: public static Dictionary<string, string> ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary
1603: {
1604: get
1605: {
1606: lock (objectLock)
1607: {
1608: if (serviceToAccessNameWithinAllowedToBeMigratedOltDictionary == null || serviceToAccessNameWithinAllowedToBeMigratedOltDictionary.Count == 0) serviceToAccessNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary;
1609:
1610: return serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
1611: }
1612: }
1613: }
1614:
1615: ////////////////////////////////////////////////////////////////////////////
1616:
1617: /// <summary>
1618: ///
1619: /// </summary>
1620: private static Dictionary<string, string> _ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary
1621: {
1622: get
1623: {
1624: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
1625: Dictionary<string, Ia.Ngn.Cl.Model.Business.ServiceAddress> serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary;
1626:
1627: var areaSymbolList = Ia.Ngn.Cl.Model.Business.Service.AllowedToBeMigratedOltSymbolList;
1628:
1629: var kuwaitNgnAreaList = (from k in Ia.Ngn.Cl.Model.Data.Service.KuwaitNgnAreaList where areaSymbolList.Contains(k.Symbol) select k.Id).ToList();
1630:
1631: var serviceToDbNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToDbNameWithinAllowedToBeMigratedOltDictionary;
1632: var serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToCustomerAddressWithinAllowedToBeMigratedOltDictionary;
1633:
1634: serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, Ia.Ngn.Cl.Model.Business.ServiceAddress>(serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary.Count);
1635:
1636: foreach (KeyValuePair<string, string> kvp in serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary)
1637: {
1638: var service = kvp.Key;
1639: var address = kvp.Value;
1640: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(service, address, out string level);
1641:
1642: serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary[service] = serviceAddress;
1643: }
1644:
1645: serviceToAccessNameWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, string>(serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary.Count);
1646: var ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary;
1647:
1648: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1649: {
1650: var accessList = (from a in db.Accesses
1651: where kuwaitNgnAreaList.Contains(a.AreaId)
1652: select new Ia.Ngn.Cl.Model.Ui.Access { Id = a.Id, AreaId = a.AreaId, Block = a.Block, Street = a.Street, PremisesOld = a.PremisesOld, PremisesNew = a.PremisesNew }).ToList();
1653:
1654: foreach (var access in accessList)
1655: {
1656: if (ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary.ContainsKey(access.Id))
1657: {
1658: access.Name = ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary[access.Id];
1659: }
1660: else access.Name = string.Empty;
1661: }
1662:
1663: foreach (var kvp in serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary)
1664: {
1665: serviceAddress = new Ia.Ngn.Cl.Model.Business.ServiceAddress();
1666:
1667: serviceAddress.AreaId = kvp.Value.AreaId;
1668: serviceAddress.Block = kvp.Value.Block;
1669: serviceAddress.Street = kvp.Value.Street;
1670: serviceAddress.Boulevard = kvp.Value.Boulevard;
1671: serviceAddress.PremisesOld = kvp.Value.PremisesOld;
1672: serviceAddress.PremisesNew = kvp.Value.PremisesNew;
1673: serviceAddress.Paci = kvp.Value.Paci;
1674:
1675: var statisticalAccess = Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList, out string note2);
1676: if (statisticalAccess != null) statisticalAccess.Note = note2;
1677:
1678: serviceToAccessNameWithinAllowedToBeMigratedOltDictionary[kvp.Key] = statisticalAccess != null ? statisticalAccess.Name : string.Empty;
1679: }
1680: }
1681:
1682: return serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
1683: }
1684: }
1685:
1686: ////////////////////////////////////////////////////////////////////////////
1687:
1688: /// <summary>
1689: ///
1690: /// </summary>
1691: public static Dictionary<string, List<string>> AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary
1692: {
1693: get
1694: {
1695: DateTime now;
1696: Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
1697:
1698: now = DateTime.UtcNow.AddHours(3);
1699: validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.TwelveHours;
1700:
1701: lock (objectLock)
1702: {
1703: if (accessNameToServiceListWithinAllowedToBeMigratedOltDictionary == null || accessNameToServiceListWithinAllowedToBeMigratedOltDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, accessNameToServiceListWithinAllowedToBeMigratedOltDictionaryTimestamp))
1704: {
1705: accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary(validityOfData);
1706: }
1707:
1708: return accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
1709: }
1710: }
1711: }
1712:
1713: ////////////////////////////////////////////////////////////////////////////
1714:
1715: /// <summary>
1716: ///
1717: /// </summary>
1718: private static Dictionary<string, List<string>> _AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary(Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData)
1719: {
1720: string title;
1721: DateTime now, timestamp;
1722:
1723: now = DateTime.UtcNow.AddHours(3);
1724:
1725: title = "AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary";
1726:
1727: timestamp = Ia.Ngn.Cl.Model.Data.Miscellaneous.ReadUpdatedDateTime(title);// + "Timestamp");
1728:
1729: if (!Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, timestamp))
1730: {
1731: accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = _AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary();
1732: Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title, accessNameToServiceListWithinAllowedToBeMigratedOltDictionary);
1733:
1734: timestamp = now;
1735: //Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title + "Timestamp", timestamp);
1736: }
1737: else
1738: {
1739: accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<Dictionary<string, List<string>>>(title);
1740: }
1741:
1742: accessNameToServiceListWithinAllowedToBeMigratedOltDictionaryTimestamp = timestamp;
1743:
1744: return accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
1745: }
1746:
1747: ////////////////////////////////////////////////////////////////////////////
1748:
1749: /// <summary>
1750: ///
1751: /// </summary>
1752: private static Dictionary<string, List<string>> _AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary()
1753: {
1754: string key, value;
1755: List<string> valueList;
1756:
1757: var serviceToAccessNameDictionary = ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary;
1758:
1759: accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, List<string>>(serviceToAccessNameDictionary.Count);
1760:
1761: foreach (var kvp in serviceToAccessNameDictionary)
1762: {
1763: key = kvp.Value;
1764:
1765: if (!string.IsNullOrEmpty(key))
1766: {
1767: value = kvp.Key;
1768:
1769: valueList = new List<string>();
1770:
1771: if (!accessNameToServiceListWithinAllowedToBeMigratedOltDictionary.ContainsKey(key))
1772: {
1773: valueList.Add(value);
1774:
1775: accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key] = valueList;
1776: }
1777: else
1778: {
1779: valueList = accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key];
1780: valueList.Add(value);
1781:
1782: accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key] = valueList;
1783: }
1784: }
1785: }
1786:
1787: return accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
1788: }
1789:
1790: ////////////////////////////////////////////////////////////////////////////
1791:
1792: /// <summary>
1793: ///
1794: /// </summary>
1795: public static List<string> DbPeerServiceList(List<string> serviceList)
1796: {
1797: var dbNameToServiceListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.DbNameToServiceListDictionary;
1798:
1799: var listList = (from d in dbNameToServiceListDictionary where serviceList.Any(u => d.Value.Contains(u)) select d.Value).ToList();
1800:
1801: var list = listList.SelectMany(x => x).ToList();
1802:
1803: var peerServiceList = list.Distinct().ToList();
1804:
1805: return peerServiceList;
1806: }
1807:
1808: ////////////////////////////////////////////////////////////////////////////
1809:
1810: /// <summary>
1811: ///
1812: /// </summary>
1813: public static Dictionary<string, List<string>> DbNameToServiceListDictionary
1814: {
1815: get
1816: {
1817: DateTime now;
1818: Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
1819:
1820: now = DateTime.UtcNow.AddHours(3);
1821: validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.TwelveHours;
1822:
1823: lock (objectLock)
1824: {
1825: if (dbNameToServiceListDictionary == null || dbNameToServiceListDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, dbNameToServiceListDictionaryTimestamp))
1826: {
1827: dbNameToServiceListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._DbNameToServiceListDictionary;
1828: }
1829:
1830: return dbNameToServiceListDictionary;
1831: }
1832: }
1833: }
1834:
1835: ////////////////////////////////////////////////////////////////////////////
1836:
1837: /// <summary>
1838: ///
1839: /// </summary>
1840: private static Dictionary<string, List<string>> _DbNameToServiceListDictionary
1841: {
1842: get
1843: {
1844: string key, value;
1845: DateTime now;
1846:
1847: List<string> valueList;
1848:
1849: now = DateTime.UtcNow.AddHours(3);
1850:
1851: var serviceToDbNameDictionary = ServiceToDbNameWithinAllowedToBeMigratedOltDictionary;
1852:
1853: dbNameToServiceListDictionary = new Dictionary<string, List<string>>(serviceToDbNameDictionary.Count);
1854:
1855: foreach (KeyValuePair<string, string> kvp in serviceToDbNameDictionary)
1856: {
1857: key = kvp.Value;
1858:
1859: if (!string.IsNullOrEmpty(key) && key != ":")
1860: {
1861: value = kvp.Key;
1862:
1863: valueList = new List<string>();
1864:
1865: if (!dbNameToServiceListDictionary.ContainsKey(key))
1866: {
1867: valueList.Add(value);
1868:
1869: dbNameToServiceListDictionary[key] = valueList;
1870: }
1871: else
1872: {
1873: valueList = dbNameToServiceListDictionary[key];
1874: valueList.Add(value);
1875:
1876: dbNameToServiceListDictionary[key] = valueList;
1877: }
1878: }
1879: }
1880:
1881: dbNameToServiceListDictionaryTimestamp = now;
1882:
1883: return dbNameToServiceListDictionary;
1884: }
1885: }
1886:
1887: ////////////////////////////////////////////////////////////////////////////
1888:
1889: /// <summary>
1890: ///
1891: /// </summary>
1892: public static Dictionary<string, string> ServiceToDbNameWithinAllowedToBeMigratedOltDictionary
1893: {
1894: get
1895: {
1896: DateTime now;
1897: Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
1898:
1899: now = DateTime.UtcNow.AddHours(3);
1900: validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.TwelveHours;
1901:
1902: lock (objectLock)
1903: {
1904: if (serviceToDbNameDictionary == null || serviceToDbNameDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, serviceToDbNameDictionaryAndServiceToCustomerAddressDictionaryTimestamp))
1905: {
1906: Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(validityOfData, out serviceToDbNameDictionary, out serviceToCustomerAddressDictionary);
1907: }
1908:
1909: return serviceToDbNameDictionary;
1910: }
1911: }
1912: }
1913:
1914: ////////////////////////////////////////////////////////////////////////////
1915:
1916: /// <summary>
1917: ///
1918: /// </summary>
1919: public static Dictionary<string, string> ServiceToCustomerAddressWithinAllowedToBeMigratedOltDictionary
1920: {
1921: get
1922: {
1923: DateTime now;
1924: Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
1925:
1926: now = DateTime.UtcNow.AddHours(3);
1927: validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.Day;
1928:
1929: lock (objectLock)
1930: {
1931: if (serviceToCustomerAddressDictionary == null || serviceToCustomerAddressDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, serviceToDbNameDictionaryAndServiceToCustomerAddressDictionaryTimestamp))
1932: {
1933: Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(validityOfData, out serviceToDbNameDictionary, out serviceToCustomerAddressDictionary);
1934: }
1935:
1936: return serviceToCustomerAddressDictionary;
1937: }
1938: }
1939: }
1940:
1941: ////////////////////////////////////////////////////////////////////////////
1942:
1943: /// <summary>
1944: ///
1945: /// </summary>
1946: private static void _ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData, out Dictionary<string, string> serviceToDbNameDictionary, out Dictionary<string, string> serviceToCustomerAddressDictionary)
1947: {
1948: string title1, title2;
1949: DateTime now, timestamp, timestamp1, timestamp2;
1950:
1951: now = DateTime.UtcNow.AddHours(3);
1952:
1953: title1 = "ServiceToDbNameDictionary";
1954: title2 = "ServiceToCustomerAddressDictionary";
1955:
1956: timestamp1 = Ia.Ngn.Cl.Model.Data.Miscellaneous.ReadUpdatedDateTime(title1);// + "Timestamp");
1957: timestamp2 = Ia.Ngn.Cl.Model.Data.Miscellaneous.ReadUpdatedDateTime(title2);// + "Timestamp");
1958:
1959: timestamp = (timestamp1 < timestamp2) ? timestamp1 : timestamp2;
1960:
1961: if (!Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, timestamp))
1962: {
1963: _ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(out serviceToDbNameDictionary, out serviceToCustomerAddressDictionary);
1964:
1965: Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title1, serviceToDbNameDictionary);
1966:
1967: Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title2, serviceToCustomerAddressDictionary);
1968:
1969: timestamp = now;
1970: //Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title + "Timestamp", timestamp);
1971: }
1972: else
1973: {
1974: serviceToDbNameDictionary = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<Dictionary<string, string>>(title1);
1975:
1976: serviceToCustomerAddressDictionary = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<Dictionary<string, string>>(title2);
1977: }
1978:
1979: serviceToDbNameDictionaryAndServiceToCustomerAddressDictionaryTimestamp = timestamp;
1980: }
1981:
1982: ////////////////////////////////////////////////////////////////////////////
1983:
1984: /// <summary>
1985: ///
1986: /// </summary>
1987: private static void _ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(out Dictionary<string, string> serviceToDbNameDictionary, out Dictionary<string, string> serviceToCustomerAddressDictionary)
1988: {
1989: Dictionary<string, int> serviceToLastSerialDictionary;
1990:
1991: var areaSymbolList = Ia.Ngn.Cl.Model.Business.Service.AllowedToBeMigratedOltSymbolList;
1992:
1993: var areaIdList = (from k in Ia.Ngn.Cl.Model.Data.Service.KuwaitNgnAreaList where areaSymbolList.Contains(k.Symbol) select k.Id).ToList();
1994:
1995: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1996: {
1997: var statisticalVariableList = (from sr in db.ServiceRequests
1998: //join srs in db.ServiceRequestServices on sr.Number.ToString() equals srs.Service // on sr.ServiceRequestService.Id equals srs.Id
1999: join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id into srts
2000: from srt in srts.DefaultIfEmpty()
2001: where areaIdList.Contains(sr.AreaId) && (srt == null || srt.TypeId == 1 || srt.TypeId == 2)
2002: select new Ia.Ngn.Cl.Model.Business.ServiceRequestStatisticalVariable
2003: {
2004: //Provisioned = srs.Provisioned,
2005: ServiceRequestId = sr.Id,
2006: Service = sr.Number.ToString(),
2007: Serial = sr.Serial,
2008: AreaId = sr.AreaId,
2009: CustomerAddress = sr.CustomerAddress,
2010: /* CustomerName = sr.CustomerName, */
2011: ServiceRequestTypeValue = srt == null ? string.Empty : srt.Value,
2012: }).ToList();
2013:
2014: serviceToLastSerialDictionary = new Dictionary<string, int>(statisticalVariableList.Count);
2015: serviceToDbNameDictionary = new Dictionary<string, string>(statisticalVariableList.Count);
2016: serviceToCustomerAddressDictionary = new Dictionary<string, string>(statisticalVariableList.Count);
2017:
2018: foreach (var sv in statisticalVariableList/*.Where(u => u.Provisioned)*/.OrderBy(u => u.ServiceRequestId))
2019: {
2020: serviceToLastSerialDictionary[sv.Service] = sv.Serial;
2021: }
2022:
2023: foreach (var sv in statisticalVariableList/*.Where(u => u.Provisioned)*/.OrderBy(u => u.ServiceRequestId))
2024: {
2025: if (serviceToLastSerialDictionary[sv.Service] == sv.Serial)
2026: {
2027: if (serviceToDbNameDictionary.ContainsKey(sv.Service))
2028: {
2029: // ServiceRequestTypes values do not contain ":"
2030: if (!serviceToDbNameDictionary[sv.Service].Contains(":")) serviceToDbNameDictionary[sv.Service] += ":" + sv.ServiceRequestTypeValue;
2031: }
2032: else serviceToDbNameDictionary[sv.Service] = sv.ServiceRequestTypeValue;
2033:
2034: if (!serviceToCustomerAddressDictionary.ContainsKey(sv.Service)) serviceToCustomerAddressDictionary[sv.Service] = sv.CustomerAddress;
2035: }
2036: }
2037: }
2038: }
2039:
2040: ////////////////////////////////////////////////////////////////////////////
2041: ////////////////////////////////////////////////////////////////////////////
2042:
2043: /// <summary>
2044: ///
2045: /// </summary>
2046: public static string ToSimpleTextString(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest)
2047: {
2048: StringBuilder sb;
2049:
2050: sb = new StringBuilder();
2051:
2052: // Id Number Serial Status RequestDateTime Service ServiceCategory CustomerId CustomerName CustomerCategory CustomerAddress Balance
2053: sb.AppendLine("Id: " + serviceRequest.Id);
2054: sb.AppendLine("Number: " + serviceRequest.Number + "/" + serviceRequest.Serial);
2055: //sb.AppendLine("Serial: " + serviceRequest.Serial);
2056: sb.AppendLine("Status: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.StatusSortedList[serviceRequest.Status].ToString());
2057: sb.AppendLine("RequestDateTime: " + serviceRequest.RequestDateTime.ToString("yyyy-MM-dd HH:mm"));
2058: sb.AppendLine("Service: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceSortedList[serviceRequest.ServiceId].ToString());
2059: //sb.AppendLine("ServiceCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceCategorySortedList[serviceRequest.ServiceCategoryId].ToString());
2060: //sb.AppendLine("CustomerId: " + serviceRequest.CustomerId);
2061: sb.AppendLine("CustomerName: " + serviceRequest.CustomerName);
2062: //sb.AppendLine("CustomerCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.CustomerCategorySortedList[serviceRequest.CustomerCategoryId].ToString());
2063:
2064: sb.AppendLine("CustomerAddress: " + serviceRequest.CustomerAddress);
2065: //sb.AppendLine("Balance: " + serviceRequest.Balance);
2066:
2067: return sb.ToString();
2068: }
2069:
2070: ////////////////////////////////////////////////////////////////////////////
2071:
2072: /// <summary>
2073: ///
2074: /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
2075: ///
2076: /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
2077: /// 2. Add "using System.Reflection".
2078: /// 3. See sample below.
2079: ///
2080: /// </summary>
2081:
2082: private static XDocument XDocument
2083: {
2084: get
2085: {
2086: if (xDocument == null)
2087: {
2088: Assembly _assembly;
2089: StreamReader streamReader;
2090:
2091: _assembly = Assembly.GetExecutingAssembly();
2092: streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.service-request.xml"));
2093:
2094: try
2095: {
2096: if (streamReader.Peek() != -1)
2097: {
2098: xDocument = System.Xml.Linq.XDocument.Load(streamReader);
2099: }
2100: }
2101: catch (Exception)
2102: {
2103: }
2104: finally
2105: {
2106: }
2107: }
2108:
2109: return xDocument;
2110: }
2111: }
2112:
2113: ////////////////////////////////////////////////////////////////////////////
2114: ////////////////////////////////////////////////////////////////////////////
2115: }
2116:
2117: ////////////////////////////////////////////////////////////////////////////
2118: ////////////////////////////////////////////////////////////////////////////
2119: }