1: using Ia.Ngn.Cl.Model.Business; // Needed for ServerExtension
2: using Ia.Ngn.Cl.Model.Ui;
3: using Microsoft.EntityFrameworkCore;
4: using System;
5: using System.Collections;
6: using System.Collections.Generic;
7: using System.Data;
8: using System.Data.Objects.SqlClient;
9: using System.IO;
10: using System.Linq;
11: using System.Reflection;
12: using System.Text;
13: using System.Text.RegularExpressions;
14: using System.Xml.Linq;
15:
16: namespace Ia.Ngn.Cl.Model.Data
17: {
18: ////////////////////////////////////////////////////////////////////////////
19:
20: /// <summary publish="true">
21: /// Service Request support class for Optical Fiber Network (OFN) data model.
22: /// </summary>
23: ///
24: /// <remarks>
25: /// Copyright © 2006-2022 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
26: ///
27: /// 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
28: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
29: ///
30: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
31: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
32: ///
33: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
34: ///
35: /// Copyright notice: This notice may not be removed or altered from any source distribution.
36: /// </remarks>
37: public class ServiceRequest
38: {
39: private static int serviceRequestIdStartEndRangeBufferedListIndex, serviceRequestIdLatestStartEndRangeBufferedListIndex, serviceRequestIdLatestStartEndRangeBufferedListCount = 0;
40: private static SortedList serviceCategorySortedList, serviceSortedList, customerCategorySortedList, statusSortedList;
41: private static XDocument xDocument;
42: private static List<int> serviceIdAllowedForProcessingList, serviceRequestDomainList;
43: private static Dictionary<int, string> systemCode;
44: private static List<Tuple<int, int>> serviceRequestIdStartEndRangeBufferedTupleList;
45: private static Dictionary<string, string> serviceToDbNameDictionary, serviceToCustomerAddressDictionary;
46: private static Dictionary<int, List<int>> changedServiceNumberToChangedToServiceNumberListDictionary;
47: private static Dictionary<int, List<int>> pbxServiceKeyToPbxServiceBranchListDictionary;
48: private static Dictionary<string, List<string>> changedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary;
49: private static DateTime serviceToDbNameDictionaryAndServiceToCustomerAddressDictionaryTimestamp;
50: private static Dictionary<string, List<string>> dbNameToServiceListDictionary, accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
51: private static DateTime dbNameToServiceListDictionaryTimestamp, accessNameToServiceListWithinAllowedToBeMigratedOltDictionaryTimestamp;
52: private static Dictionary<string, string> serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
53:
54: private static readonly object objectLock = new object();
55:
56: ////////////////////////////////////////////////////////////////////////////
57:
58: /// <summary>
59: ///
60: /// </summary>
61: public ServiceRequest() { }
62:
63: ////////////////////////////////////////////////////////////////////////////
64:
65: /// <summary>
66: ///
67: /// </summary>
68: public static Dictionary<int, string> SystemCode
69: {
70: get
71: {
72: if (systemCode == null || systemCode.Count == 0)
73: {
74: lock (objectLock)
75: {
76: systemCode = Ia.Ngn.Cl.Model.Data.ServiceRequest._SystemCode;
77: }
78: }
79:
80: return systemCode;
81: }
82: }
83:
84: ////////////////////////////////////////////////////////////////////////////
85:
86: /// <summary>
87: ///
88: /// </summary>
89: private static Dictionary<int, string> _SystemCode
90: {
91: get
92: {
93: // select code_id, code_name from system_codes
94:
95: systemCode = new Dictionary<int, string>(100)
96: {
97: [6008] = "قطع مطالبات",
98: [6009] = "قطع إدارى",
99: [4007] = "إفراج",
100: [4008] = "قيد التجديد",
101: [4009] = "قيد إخطار الجهة",
102: [4010] = "تسيل جزئي",
103: [1001] = "جديد",
104: [1002] = "مطلوب",
105: [1003] = "متاح",
106: [2001] = "قيد الموافقة الفنية",
107: [2002] = "قيد الدفع",
108: [2003] = "قيد التنفيذ",
109: [2004] = "تعذر التنفيذ",
110: [2005] = "تم التنفيذ",
111: [3001] = "نقل من رصيد خدمة - دائن",
112: [3002] = "نقل من رصيد خدمة - مدين",
113: [4001] = "سارية",
114: [4002] = "منتهية",
115: [4003] = "مستردة",
116: [4004] = "تسييل",
117: [5001] = "سارية",
118: [5002] = "منتهية",
119: [5003] = "ملغاة",
120: [4005] = "ملغاة",
121: [5004] = "مبدئية",
122: [6001] = "قيد التنفيذ",
123: [6002] = "تم التنفيذ",
124: [6003] = "تعذر التنفيذ",
125: [4006] = "جديدة",
126: [7001] = "يعمل",
127: [7002] = "قطع مؤقت",
128: [7003] = "قطع مبرمج",
129: [1004] = "مقطوع",
130: [5005] = "متأخرة السداد",
131: [7004] = "قيد التنفيذ",
132: [8001] = "قيد التنفيذ",
133: [8002] = "تم التنفيذ",
134: [8003] = "تعذر التنفيذ",
135: [1005] = "يعمل",
136: [7005] = "تعذر التنفيذ",
137: [9001] = "دائن",
138: [9002] = "مدين",
139: [7006] = "رفع نهائي",
140: [1203] = "مبدئية",
141: [1201] = "سارية",
142: [1202] = "ملغاة",
143: [1101] = "انهاء",
144: [1102] = "قطع مؤقت",
145: [1301] = "قطع مبرمج",
146: [1302] = "أمر عمل",
147: [7007] = "رفع محاسبة",
148: [1401] = "نص",
149: [1402] = "رقم",
150: [1403] = "تاريخ",
151: [1404] = "تاريخ/وقت",
152: [7008] = "قطع معاكسة",
153: [1501] = "خدمة أساسية",
154: [1502] = "تشغيل خدمة فرعية",
155: [1503] = "إيقاف خدمة فرعية",
156: [1504] = "عملية",
157: [1505] = "منتج",
158: [1510] = "خدمات متنوعة",
159: [1103] = "إعادة",
160: [1506] = "خدمة اساسية - مجموعة",
161: [1507] = "خدمة تابعة",
162: [1508] = "خدمة مدمجة",
163: [2101] = "جديد",
164: [2102] = "تم الحساب",
165: [2103] = "نسخة جديدة",
166: [2201] = "ملف المكالمات الدولية",
167: [6004] = "إعادة الحالة",
168: [1601] = "مبدئية",
169: [1602] = "نهائية",
170: [1603] = "ملغاة",
171: [2006] = "ملغاة",
172: [7009] = "ملغى",
173: [7010] = "لا يعمل",
174: [2104] = "مفقود",
175: [5006] = "سارية - مؤقتة",
176: [5007] = "مرحلة",
177: [1701] = "عادي",
178: [1702] = "نطاق",
179: [1509] = "إيقاف خدمة أساسية"
180: };
181:
182: return systemCode;
183: }
184: }
185:
186: ////////////////////////////////////////////////////////////////////////////
187:
188: /// <summary>
189: ///
190: /// </summary>
191: public static SortedList ServiceCategorySortedList
192: {
193: get
194: {
195: if (serviceCategorySortedList == null || serviceCategorySortedList.Count == 0)
196: {
197: lock (objectLock)
198: {
199: serviceCategorySortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceCategorySortedList;
200: }
201: }
202:
203: return serviceCategorySortedList;
204: }
205: }
206:
207: ////////////////////////////////////////////////////////////////////////////
208:
209: /// <summary>
210: ///
211: /// </summary>
212: private static SortedList _ServiceCategorySortedList
213: {
214: get
215: {
216: int id;
217:
218: serviceCategorySortedList = new SortedList(10);
219:
220: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("categoryList").Elements("category"))
221: {
222: id = int.Parse(x.Attribute("id").Value);
223:
224: serviceCategorySortedList[id] = x.Attribute("arabicName").Value;
225: }
226:
227: return serviceCategorySortedList;
228: }
229: }
230:
231: ////////////////////////////////////////////////////////////////////////////
232:
233: /// <summary>
234: ///
235: /// </summary>
236: public static SortedList ServiceSortedList
237: {
238: get
239: {
240: if (serviceSortedList == null || serviceSortedList.Count == 0)
241: {
242: lock (objectLock)
243: {
244: serviceSortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceSortedList;
245: }
246: }
247:
248: return serviceSortedList;
249: }
250: }
251:
252: ////////////////////////////////////////////////////////////////////////////
253:
254: /// <summary>
255: ///
256: /// </summary>
257: private static SortedList _ServiceSortedList
258: {
259: get
260: {
261: int id;
262:
263: serviceSortedList = new SortedList(10);
264:
265: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
266: {
267: id = int.Parse(x.Attribute("id").Value);
268:
269: serviceSortedList[id] = x.Attribute("arabicName").Value;
270: }
271:
272: return serviceSortedList;
273: }
274: }
275:
276: ////////////////////////////////////////////////////////////////////////////
277:
278: /// <summary>
279: ///
280: /// </summary>
281: public static SortedList CustomerCategorySortedList
282: {
283: get
284: {
285: if (customerCategorySortedList == null || customerCategorySortedList.Count == 0)
286: {
287: lock (objectLock)
288: {
289: customerCategorySortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._CustomerCategorySortedList;
290: }
291: }
292:
293: return customerCategorySortedList;
294: }
295: }
296:
297: ////////////////////////////////////////////////////////////////////////////
298:
299: /// <summary>
300: ///
301: /// </summary>
302: private static SortedList _CustomerCategorySortedList
303: {
304: get
305: {
306: int id;
307:
308: customerCategorySortedList = new SortedList(10);
309:
310: foreach (XElement x in XDocument.Element("serviceRequest").Elements("customer").Elements("categoryList").Elements("category"))
311: {
312: id = int.Parse(x.Attribute("id").Value);
313:
314: customerCategorySortedList[id] = x.Attribute("arabicName").Value;
315: }
316:
317: return customerCategorySortedList;
318: }
319: }
320:
321: ////////////////////////////////////////////////////////////////////////////
322:
323: /// <summary>
324: ///
325: /// </summary>
326: public static SortedList StatusSortedList
327: {
328: get
329: {
330: if (statusSortedList == null || statusSortedList.Count == 0)
331: {
332: lock (objectLock)
333: {
334: statusSortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._StatusSortedList;
335: }
336: }
337:
338: return statusSortedList;
339: }
340: }
341:
342: ////////////////////////////////////////////////////////////////////////////
343:
344: /// <summary>
345: ///
346: /// </summary>
347: private static SortedList _StatusSortedList
348: {
349: get
350: {
351: int id;
352:
353: statusSortedList = new SortedList(10);
354:
355: foreach (XElement x in XDocument.Element("serviceRequest").Elements("statusList").Elements("status"))
356: {
357: id = int.Parse(x.Attribute("id").Value);
358:
359: statusSortedList[id] = x.Attribute("arabicName").Value;
360: }
361:
362: return statusSortedList;
363: }
364: }
365:
366: ////////////////////////////////////////////////////////////////////////////
367:
368: /// <summary>
369: ///
370: /// </summary>
371: public static List<int> ServiceIdAllowedForProcessingList
372: {
373: get
374: {
375: if (serviceIdAllowedForProcessingList == null || serviceIdAllowedForProcessingList.Count == 0)
376: {
377: lock (objectLock)
378: {
379: serviceIdAllowedForProcessingList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceIdAllowedForProcessingList;
380: }
381: }
382:
383: return serviceIdAllowedForProcessingList;
384: }
385: }
386:
387: ////////////////////////////////////////////////////////////////////////////
388:
389: /// <summary>
390: ///
391: /// </summary>
392: private static List<int> _ServiceIdAllowedForProcessingList
393: {
394: get
395: {
396: int id;
397:
398: serviceIdAllowedForProcessingList = new List<int>(100);
399:
400: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
401: {
402: if (x.HasAttributes && x.Attribute("allowProcessing") != null)
403: {
404: if (x.Attribute("allowProcessing").Value == "true")
405: {
406: id = int.Parse(x.Attribute("id").Value);
407: serviceIdAllowedForProcessingList.Add(id);
408: }
409: }
410: }
411:
412: return serviceIdAllowedForProcessingList;
413: }
414: }
415:
416: ////////////////////////////////////////////////////////////////////////////
417: ////////////////////////////////////////////////////////////////////////////
418:
419: /// <summary>
420: ///
421: /// </summary>
422: public static Tuple<int, int> ServiceRequestIdStartEndRangeManager(ref int index, out string result)
423: {
424: int count, edgeBufferRange, latestFromNDaysBeforeNow;
425: Tuple<int, int> tuple;
426: List<int> serviceRequestIdList;
427:
428: count = 40;
429: edgeBufferRange = 400;
430: latestFromNDaysBeforeNow = 7;
431:
432: if (serviceRequestIdStartEndRangeBufferedTupleList == null || serviceRequestIdStartEndRangeBufferedListIndex == 0)
433: {
434: serviceRequestIdStartEndRangeBufferedListIndex = index;
435:
436: using (var db = new Ia.Ngn.Cl.Model.Ngn())
437: {
438: serviceRequestIdList = (from sr in db.ServiceRequests
439: orderby sr.Id ascending
440: select sr.Id).ToList();
441: }
442:
443: serviceRequestIdStartEndRangeBufferedTupleList = Ia.Ngn.Cl.Model.Business.ServiceRequest.OptimizedStartEndRangeBufferedList(serviceRequestIdList, count, edgeBufferRange);
444:
445: var firstServiceRequestIdAfterDateTime = Ia.Ngn.Cl.Model.Data.ServiceRequest.FirstIdAfterDateTime(DateTime.UtcNow.AddHours(3).AddDays(-latestFromNDaysBeforeNow));
446:
447: serviceRequestIdLatestStartEndRangeBufferedListCount = 0;
448: for (int i = serviceRequestIdStartEndRangeBufferedTupleList.Count - 1; i > 0; i--)
449: {
450: if (serviceRequestIdStartEndRangeBufferedTupleList[i].Item1 < firstServiceRequestIdAfterDateTime) break;
451:
452: serviceRequestIdLatestStartEndRangeBufferedListCount++;
453: }
454: }
455:
456: if (serviceRequestIdStartEndRangeBufferedTupleList.Count > 0)
457: {
458: if (Ia.Ngn.Cl.Model.Business.Administration.NowIsOfficialWorkingTime)
459: {
460: // flip between last and latest range to favor reading fresh work orders quickly
461: if (Ia.Cl.Model.Default.RandomBool)
462: {
463: serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - Ia.Cl.Model.Default.Random(serviceRequestIdLatestStartEndRangeBufferedListCount) - 1;
464: serviceRequestIdLatestStartEndRangeBufferedListIndex = (serviceRequestIdLatestStartEndRangeBufferedListIndex >= 0) ? serviceRequestIdLatestStartEndRangeBufferedListIndex : 0;
465: }
466: else serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - 1;
467:
468: tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdLatestStartEndRangeBufferedListIndex];
469:
470: if (serviceRequestIdLatestStartEndRangeBufferedListIndex == serviceRequestIdStartEndRangeBufferedTupleList.Count - 1)
471: {
472: tuple = new Tuple<int, int>(tuple.Item1, tuple.Item2 + edgeBufferRange);
473: }
474:
475: result = "(latest:" + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdLatestStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
476: }
477: else
478: {
479: tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdStartEndRangeBufferedListIndex];
480:
481: result = "(historic:" + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
482:
483: serviceRequestIdStartEndRangeBufferedListIndex = Ia.Cl.Model.Default.IncrementListIndexOrRestart(serviceRequestIdStartEndRangeBufferedTupleList, serviceRequestIdStartEndRangeBufferedListIndex);
484: }
485: }
486: else
487: {
488: result = "(0-0 0/0)";
489:
490: tuple = null;
491: }
492:
493: index = serviceRequestIdStartEndRangeBufferedListIndex;
494:
495: return tuple;
496: }
497:
498: ////////////////////////////////////////////////////////////////////////////
499:
500: /// <summary>
501: ///
502: /// </summary>
503: public static string AlterSessionOfCustomerDepartmentOracleDatabase
504: {
505: get
506: {
507: return @"alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS'";
508: }
509: }
510:
511: ////////////////////////////////////////////////////////////////////////////
512:
513: /// <summary>
514: /// Return list of service requests
515: /// </summary>
516: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List()
517: {
518: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
519:
520: using (var db = new Ia.Ngn.Cl.Model.Ngn())
521: {
522: serviceRequestList = (from sr in db.ServiceRequests select sr).ToList();
523: }
524:
525: return serviceRequestList;
526: }
527:
528: ////////////////////////////////////////////////////////////////////////////
529:
530: /// <summary>
531: ///
532: /// </summary>
533: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(int number)
534: {
535: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
536:
537: using (var db = new Ia.Ngn.Cl.Model.Ngn())
538: {
539: serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
540: }
541:
542: return serviceRequestList;
543: }
544:
545: ////////////////////////////////////////////////////////////////////////////
546:
547: /// <summary>
548: ///
549: /// </summary>
550: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(string service)
551: {
552: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
553:
554: if (!string.IsNullOrEmpty(service))
555: {
556: if (int.TryParse(service, out int number))
557: {
558: using (var db = new Ia.Ngn.Cl.Model.Ngn())
559: {
560: serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
561: }
562: }
563: else
564: {
565: throw new ArgumentException(@"List(): service is not a number, service: " + service);
566: }
567: }
568: else serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
569:
570: return serviceRequestList;
571: }
572:
573: ////////////////////////////////////////////////////////////////////////////
574:
575: /// <summary>
576: ///
577: /// </summary>
578: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListById(int id)
579: {
580: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
581:
582: if (id > 0)
583: {
584: using (var db = new Ia.Ngn.Cl.Model.Ngn())
585: {
586: serviceRequestList = (from sr in db.ServiceRequests where sr.Id == id select sr).ToList();
587: }
588: }
589: else serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
590:
591: return serviceRequestList;
592: }
593:
594: ////////////////////////////////////////////////////////////////////////////
595:
596: /// <summary>
597: ///
598: /// </summary>
599: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<int> numberList)
600: {
601: List<string> serviceList;
602:
603: serviceList = (from n in numberList select n.ToString()).ToList();
604:
605: return List(serviceList);
606: }
607:
608: ////////////////////////////////////////////////////////////////////////////
609:
610: /// <summary>
611: ///
612: /// </summary>
613: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<string> serviceList)
614: {
615: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList, viaServiceRequestTypeList;
616:
617: using (var db = new Ia.Ngn.Cl.Model.Ngn())
618: {
619: if (serviceList.Count > 0)
620: {
621: serviceRequestList = (from sr in db.ServiceRequests
622: where serviceList.Contains(sr.Number.ToString())
623: select sr).Include(a => a.ServiceRequestService).Include(a => a.ServiceRequestTypes).ToList();
624:
625: viaServiceRequestTypeList = (from srt in db.ServiceRequestTypes
626: join sr in db.ServiceRequests on srt.ServiceRequest.Id equals sr.Id
627: where srt.TypeId == 11 && serviceList.Contains(srt.Value)
628: select sr).Include(a => a.ServiceRequestService).ToList();
629:
630: serviceRequestList = serviceRequestList.Union(viaServiceRequestTypeList).Distinct().ToList();
631: }
632: else serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
633: }
634:
635: return serviceRequestList.ToList();
636: }
637:
638: ////////////////////////////////////////////////////////////////////////////
639:
640: /// <summary>
641: ///
642: /// </summary>
643: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<Ia.Ngn.Cl.Model.Business.ServiceRequest.NumberSerial> numberSerialList)
644: {
645: List<long> idList;
646: List<Ia.Ngn.Cl.Model.ServiceRequest> list;
647:
648: if (numberSerialList.Count > 0)
649: {
650: idList = numberSerialList.IdList();
651:
652: using (var db = new Ia.Ngn.Cl.Model.Ngn())
653: {
654: list = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
655: where
656: //numberSerialList.Contains(q.Number, q.Serial) does not work
657: //((from r in numberSerialList where r.Number == q.Number && r.Serial == q.Serial select r) != null) does not work
658: //numberList.Any<int>(i=> i == q.Number) does not work
659: idList.Contains((long)sr.Number * 100 + sr.Serial)
660: select sr).ToList();
661: }
662: }
663: else list = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
664:
665: return list;
666: }
667:
668: ////////////////////////////////////////////////////////////////////////////
669:
670: /// <summary>
671: ///
672: /// </summary>
673: public static int FirstIdAfterDateTime(DateTime dateTime)
674: {
675: int serviceRequestId;
676:
677: using (var db = new Ia.Ngn.Cl.Model.Ngn())
678: {
679: serviceRequestId = (from sr in db.ServiceRequests
680: where sr.RequestDateTime >= dateTime
681: select sr.Id).FirstOrDefault();
682: }
683:
684: return serviceRequestId;
685: }
686:
687: /*
688: ////////////////////////////////////////////////////////////////////////////
689:
690: /// <summary>
691: ///
692: /// </summary>
693: [Obsolete]
694: public static List<int> OldNumberAndChangedAndChangedToList(List<int> numberList)
695: {
696: List<int> number1List, number2List, number3List;//, number4List, number5List;
697: List<string> serviceList; //, service1List, service2List, service3List, service4List, service5List;
698: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList, serviceRequest1List, serviceRequest2List, serviceRequest3List, serviceRequest4List, serviceRequest5List;
699:
700: using (var db = new Ia.Ngn.Cl.Model.Ngn())
701: {
702: if (numberList.Count > 0)
703: {
704: serviceList = (from n in numberList select n.ToString()).ToList();
705:
706: serviceRequest1List = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
707: where numberList.Contains(sr.Number)
708: select sr).ToList();
709:
710: if (serviceRequest1List.Count > 0)
711: {
712: number1List = (from sr in serviceRequest1List select sr.Number).Distinct().ToList();
713:
714: number2List = new List<int>();
715:
716: foreach (var srt in serviceRequest1List.SelectMany(u => u.ServiceRequestTypes))
717: {
718: if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) number2List.Add(i);
719: }
720:
721: // below: this is the collect first changed-to number information
722: if (number2List.Count > 0)
723: {
724: serviceRequest2List = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
725: where number2List.Contains(sr.Number)
726: select sr).ToList();
727:
728: if (serviceRequest2List.Count > 0)
729: {
730: number1List = (from sr in serviceRequest2List select sr.Number).Distinct().ToList();
731:
732: number3List = new List<int>();
733:
734: foreach (var srt in serviceRequest2List.SelectMany(u => u.ServiceRequestTypes))
735: {
736: if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) number3List.Add(i);
737: }
738:
739: // below: this is the collect second changed-to number information
740: if (number3List.Count > 0)
741: {
742: serviceRequest3List = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
743: where number3List.Contains(sr.Number)
744: select sr).ToList();
745: }
746: else serviceRequest3List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
747: }
748: else
749: {
750: serviceRequest2List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
751: serviceRequest3List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
752: }
753: }
754: else
755: {
756: serviceRequest2List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
757: serviceRequest3List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
758: }
759: }
760: else
761: {
762: serviceRequest2List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
763: serviceRequest3List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
764: }
765:
766:
767: //
768: serviceRequest4List = (from srt in db.ServiceRequestTypes.Include(a => a.ServiceRequest)
769: where srt.TypeId == 11 && serviceList.Contains(srt.Value)
770: select srt.ServiceRequest).ToList();
771:
772: if (serviceRequest4List.Count > 0)
773: {
774: number1List = (from sr in serviceRequest4List select sr.Number).Distinct().ToList();
775:
776: serviceRequest5List = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
777: where number1List.Contains(sr.Number)
778: select sr).ToList();
779: }
780: else serviceRequest5List = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
781:
782: //
783: serviceRequestList = serviceRequest1List.Union(serviceRequest2List).Union(serviceRequest3List).Union(serviceRequest4List).Union(serviceRequest5List).Distinct().ToList();
784:
785: if (serviceRequestList.Count > 0)
786: {
787: numberList = (from n in serviceRequestList select n.Number).Distinct().ToList();
788: }
789: else numberList = new List<int>();
790: }
791: else numberList = new List<int>();
792: }
793:
794: return numberList.ToList();
795: }
796: */
797:
798: ////////////////////////////////////////////////////////////////////////////
799:
800: /// <summary>
801: ///
802: /// </summary>
803: public static List<int> NumberAndChangedNumberAndChangedToNumberList(List<int> numberList)
804: {
805: List<int> list;
806:
807: if (numberList.Count > 0)
808: {
809: var changedServiceNumberToChangedToServiceNumberDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ChangedServiceNumberToChangedToServiceNumberListDictionary();
810:
811: list = new List<int>();
812:
813: foreach (var kvp in changedServiceNumberToChangedToServiceNumberDictionary)
814: {
815: if (numberList.Contains(kvp.Key) || list.Contains(kvp.Key))
816: {
817: list.Add(kvp.Key);
818:
819: foreach (var i in kvp.Value) list.Add(i);
820: }
821: else
822: {
823: foreach (var i in kvp.Value)
824: {
825: if (numberList.Contains(i)) list.Add(i);
826: }
827: }
828: }
829:
830: list.AddRange(numberList);
831: }
832: else list = new List<int>();
833:
834: return list.Distinct().ToList();
835: }
836:
837: /*
838: ////////////////////////////////////////////////////////////////////////////
839:
840: /// <summary>
841: ///
842: /// </summary>
843: [Obsolete]
844: private static void IterativeNumberAndChangedAndChangedToList(List<int> numberList, ref List<int> list)
845: {
846: List<int> list0, changedList, changedToList, changedExceptList, changedToExceptList;
847: List<string> serviceList;
848:
849: using (var db = new Ia.Ngn.Cl.Model.Ngn())
850: {
851: if (numberList.Count > 0)
852: {
853: changedList = new List<int>();
854: changedToList = new List<int>();
855:
856: serviceList = numberList.ConvertAll(delegate (int i) { return i.ToString(); });
857:
858: // list
859: var serviceRequestNumberAndSrtTypeIdAndSrtValueList = (from srt in db.ServiceRequestTypes
860: where srt.ServiceRequest != null && numberList.Contains(srt.ServiceRequest.Number)
861: select new { srt.ServiceRequest.Number, srt.TypeId, srt.Value }).AsNoTracking().ToList();
862:
863: list0 = (from sr in serviceRequestNumberAndSrtTypeIdAndSrtValueList select sr.Number).Distinct().ToList();
864:
865: list = list.Union(list0).Distinct().ToList();
866:
867: // changedToList
868: foreach (var srntv in serviceRequestNumberAndSrtTypeIdAndSrtValueList)
869: {
870: if (srntv.TypeId == 11 && int.TryParse(srntv.Value, out int i)) changedToList.Add(i);
871: }
872:
873: // changedList
874: changedList = (from srt in db.ServiceRequestTypes
875: where srt.TypeId == 11 && serviceList.Contains(srt.Value)
876: select srt.ServiceRequest.Number).Distinct().ToList();
877:
878: changedToExceptList = changedToList.Except(list).ToList();
879: if (changedToExceptList.Count > 0) IterativeNumberAndChangedAndChangedToList(changedToExceptList, ref list);
880:
881: list = list.Union(changedToExceptList).Distinct().ToList();
882:
883: changedExceptList = changedList.Except(list).ToList();
884: if (changedExceptList.Count > 0) IterativeNumberAndChangedAndChangedToList(changedExceptList, ref list);
885:
886: list = list.Union(changedExceptList).Distinct().ToList();
887: }
888: else
889: {
890:
891: }
892: }
893: }
894: */
895:
896: ////////////////////////////////////////////////////////////////////////////
897:
898: /// <summary>
899: ///
900: /// </summary>
901: public static Dictionary<string, List<string>> ChangedServiceToChangedToServiceListDictionary()
902: {
903: Dictionary<string, List<string>> dictionary;
904:
905: dictionary = new Dictionary<string, List<string>>();
906:
907: var list = ChangedServiceNumberToChangedToServiceNumberListDictionary();
908:
909: foreach (var kvp in list)
910: {
911: dictionary[kvp.Key.ToString()] = kvp.Value.ConvertAll<string>(x => x.ToString());
912: }
913:
914: return dictionary;
915: }
916:
917: ////////////////////////////////////////////////////////////////////////////
918:
919: /// <summary>
920: ///
921: /// </summary>
922: public static Dictionary<int, List<int>> ChangedServiceNumberToChangedToServiceNumberListDictionary()
923: {
924: if (changedServiceNumberToChangedToServiceNumberListDictionary == null || changedServiceNumberToChangedToServiceNumberListDictionary.Count == 0)
925: {
926: lock (objectLock)
927: {
928: changedServiceNumberToChangedToServiceNumberListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._ChangedServiceNumberToChangedToServiceNumberListDictionary();
929: }
930: }
931:
932: return changedServiceNumberToChangedToServiceNumberListDictionary;
933: }
934:
935: ////////////////////////////////////////////////////////////////////////////
936:
937: /// <summary>
938: ///
939: /// </summary>
940: private static Dictionary<int, List<int>> _ChangedServiceNumberToChangedToServiceNumberListDictionary()
941: {
942: int i;
943: Dictionary<int, List<int>> dictionary;
944:
945: using (var db = new Ia.Ngn.Cl.Model.Ngn())
946: {
947: /*
948: * select sr.Number, srt.Value from ServiceRequests as sr
949: * left outer join ServiceRequestTypes as srt on srt.ServiceRequest_Id = sr.Id
950: * where srt.TypeId = 11 and srt.Value is not null
951: */
952:
953: dictionary = new Dictionary<int, List<int>>();
954:
955: // <status id="2003" arabicName="قيد التنفيذ" />
956: // <status id="2005" arabicName="تم التنفيذ" />
957:
958: var list = (from sr in db.ServiceRequests
959: join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id
960: where srt.TypeId == 11 && srt.Value != null && (sr.Status == 2003 || sr.Status == 2005)
961: select new { Changed = sr.Number, ChangedTo = srt.Value }).ToList();
962:
963: foreach (var v in list)
964: {
965: if (!dictionary.ContainsKey(v.Changed)) dictionary[v.Changed] = new List<int>();
966:
967: i = int.Parse(v.ChangedTo);
968:
969: if (!dictionary[v.Changed].Contains(i)) dictionary[v.Changed].Add(i);
970: }
971: }
972:
973: return dictionary;
974: }
975:
976: ////////////////////////////////////////////////////////////////////////////
977:
978: /// <summary>
979: ///
980: /// </summary>
981: public static Dictionary<int, List<int>> PbxServiceKeyToPbxServiceBranchListDictionary()
982: {
983: if (pbxServiceKeyToPbxServiceBranchListDictionary == null || pbxServiceKeyToPbxServiceBranchListDictionary.Count == 0)
984: {
985: lock (objectLock)
986: {
987: pbxServiceKeyToPbxServiceBranchListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._PbxServiceKeyToPbxServiceBranchListDictionary();
988: }
989: }
990:
991: return pbxServiceKeyToPbxServiceBranchListDictionary;
992: }
993:
994: ////////////////////////////////////////////////////////////////////////////
995:
996: /// <summary>
997: ///
998: /// </summary>
999: private static Dictionary<int, List<int>> _PbxServiceKeyToPbxServiceBranchListDictionary()
{
int key, branch;
string s, key0;
Match match;
List<int> branchList;
Dictionary<int, List<int>> dictionary;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
/*
select sr.Number, srt.Value from ServiceRequests as sr
left outer join ServiceRequestTypes as srt on srt.ServiceRequest_Id = sr.Id
where srt.TypeId = 12 and srt.Value is not null
*/
var dictionary0 = new Dictionary<string, List<int>>();
dictionary = new Dictionary<int, List<int>>();
// <status id="2003" arabicName="قيد التنفيذ" />
// <status id="2005" arabicName="تم التنفيذ" />
var list = (from sr in db.ServiceRequests
join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id
where srt.TypeId == 12 && srt.Value != null && (sr.Status == 2003 || sr.Status == 2005)
select new { ServiceBranch = sr.Number, ServiceKey = srt.Value }).ToList();
foreach (var l in list)
{
branch = l.ServiceBranch;
key0 = l.ServiceKey;
// pad the single digit serial with a 0 because serials might have two digits, to preserve order
key0 = Regex.Replace(key0, @"(\d{8})\/(\d)", @"$1/0$2");
if (!dictionary0.ContainsKey(key0)) dictionary0[key0] = new List<int>();
if (!dictionary0[key0].Contains(branch)) dictionary0[key0].Add(branch);
}
foreach (var l in dictionary0.Keys.OrderBy(u => u))
{
match = Regex.Match(l, @"(\d+)");
if (match.Groups[1].Success)
{
s = match.Groups[1].Captures[0].Value;
key = int.Parse(s);
branchList = dictionary0[l];
// this will replace numbers with earlier serials
dictionary[key] = branchList;
}
else
{
}
}
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, List<string>> ChangedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary()
{
if (changedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary == null || changedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary.Count == 0)
{
lock (objectLock)
{
changedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._ChangedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary();
}
}
return changedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
private static Dictionary<string, List<string>> _ChangedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary()
{
var dictionary0 = new Dictionary<string, List<string>>();
var changedServiceToChangedToServiceListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ChangedServiceToChangedToServiceListDictionary();
foreach (var kvp in changedServiceToChangedToServiceListDictionary)
{
var changed = kvp.Key;
var changedToList = kvp.Value;
if (!dictionary0.ContainsKey(changed)) dictionary0[changed] = new List<string>();
if (!dictionary0[changed].Contains(changed)) dictionary0[changed].Add(changed);
foreach (var changedTo in changedToList)
{
if (!dictionary0[changed].Contains(changedTo)) dictionary0[changed].Add(changedTo);
}
}
foreach (var kvp in changedServiceToChangedToServiceListDictionary)
{
var changed = kvp.Key;
var changedToList = kvp.Value;
foreach (var changedTo in changedToList)
{
if (!dictionary0.ContainsKey(changedTo)) dictionary0[changedTo] = new List<string>();
if (!dictionary0[changedTo].Contains(changedTo)) dictionary0[changedTo].Add(changedTo);
if (!dictionary0[changedTo].Contains(changed)) dictionary0[changedTo].Add(changed);
}
}
var dictionary = new Dictionary<string, List<string>>();
foreach (var kvp in dictionary0)
{
var changed = kvp.Key;
var changedToList = kvp.Value;
if (!dictionary.ContainsKey(changed)) dictionary[changed] = new List<string>();
if (!dictionary[changed].Contains(changed)) dictionary[changed].Add(changed);
foreach (var changedTo in changedToList)
{
if (!dictionary[changed].Contains(changedTo)) dictionary[changed].Add(changedTo);
var s = dictionary0[changedTo];
foreach (var t in s)
{
if (!dictionary[changed].Contains(t)) dictionary[changed].Add(t);
}
}
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<string> RelatedChangedAndChangedToServiceListByService(string service)
{
List<string> list;
var dictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ChangedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary();
if (dictionary.ContainsKey(service))
{
list = dictionary[service];
}
else list = new List<string>();
list = list.Distinct().ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<int> RelatedChangedAndChangedToServiceNumberListByServiceNumber(int serviceNumber)
{
List<int> list;
List<string> list0;
var dictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ChangedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary();
var service = serviceNumber.ToString();
if (dictionary.ContainsKey(service))
{
list0 = dictionary[service];
}
else list0 = new List<string>();
list0 = list0.Distinct().ToList();
list = list0.Select(int.Parse).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<string> ServiceListAndRelatedChangedAndChangedToServiceListByServiceList(List<string> serviceList)
{
List<string> list;
if (serviceList.Count > 0)
{
var dictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ChangedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary();
list = new List<string>();
foreach (var service in serviceList)
{
if (dictionary.ContainsKey(service))
{
list.AddRange(dictionary[service]);
}
}
list = list.Distinct().ToList();
list.AddRange(serviceList);
}
else list = new List<string>();
return list.Distinct().ToList();
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<int> ServiceNumberListAndRelatedChangedAndChangedToServiceNumberListByServiceNumberList(List<int> serviceNumberList)
{
List<int> list;
List<string> list0;
if (serviceNumberList.Count > 0)
{
var dictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ChangedAndChangedToServiceToRelatedChangedAndChangedToServiceListDictionary();
var serviceList = serviceNumberList.ConvertAll<string>(delegate (int i) { return i.ToString(); });
list0 = new List<string>();
foreach (var service in serviceList)
{
if (dictionary.ContainsKey(service))
{
list0.AddRange(dictionary[service]);
}
}
list0 = list0.Distinct().ToList();
list = list0.Select(int.Parse).ToList();
list.AddRange(serviceNumberList);
}
else list = new List<int>();
return list.Distinct().ToList();
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<string> ChangedAndChangedToServiceList()
{
var changedServiceToChangedToServiceDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ChangedServiceNumberToChangedToServiceNumberListDictionary();
var list = new List<string>();
foreach (var kvp in changedServiceToChangedToServiceDictionary)
{
var changed = kvp.Key;
var changedToList = kvp.Value;
if (!list.Contains(changed.ToString())) list.Add(changed.ToString());
foreach (var changedTo in changedToList)
{
if (!list.Contains(changedTo.ToString())) list.Add(changedTo.ToString());
}
}
list.Sort();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<string> ChangedAndChangedToServiceIdList()
{
string serviceId;
var changedAndChangedToServiceList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ChangedAndChangedToServiceList();
var serviceType = Ia.Ngn.Cl.Model.Business.Service.ServiceType.ImsService;
var list = new List<string>();
foreach (var service in changedAndChangedToServiceList)
{
serviceId = Ia.Ngn.Cl.Model.Business.Service.ServiceToServiceId(service, serviceType);
list.Add(serviceId);
}
list.Sort();
return list;
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListByDomain(List<int> domainList)
{
List<string> stringDomainList;
List<Ia.Ngn.Cl.Model.ServiceRequest> list;
stringDomainList = new List<string>();
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
if (domainList.Count > 0)
{
foreach (int i in domainList) stringDomainList.Add(i.ToString());
list = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
where domainList.Any(u => sr.Number.ToString().StartsWith(u.ToString()))
select sr).ToList();
}
else list = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
}
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinDateTimeRange(DateTime startDateTime, DateTime endDateTime)
{
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
where sr.RequestDateTime >= startDateTime && sr.RequestDateTime < endDateTime
select sr).ToList();
}
return serviceRequestList;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinIdRange(int start, int end)
{
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
where sr.Id >= start && sr.Id <= end
select sr)/*.AsNoTracking()*/.ToList(); // do not use .AsNoTracking() here, it will cause exception: The instance of entity type 'ServiceRequestService' cannot be tracked because another instance with the same key value for {'Id'} is ...
}
return serviceRequestList;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinIdRangeWithoutInclude(int start, int end)
{
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
serviceRequestList = (from sr in db.ServiceRequests
where sr.Id >= start && sr.Id <= end
select sr).ToList();
}
return serviceRequestList;
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static void UpdateWithServiceList(List<string> serviceList, List<Ia.Ngn.Cl.Model.ServiceRequest> newServiceRequestList, out string result)
{
int readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount, serviceRequestId;
string serviceRequestServiceId;
Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
result = string.Empty;
readItemCount = newServiceRequestList.Count;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
foreach (Ia.Ngn.Cl.Model.ServiceRequest newServiceRequest in newServiceRequestList)
{
serviceRequestId = newServiceRequest.Id;
serviceRequest = (from sr in db.ServiceRequests
where sr.Id == serviceRequestId
select sr).SingleOrDefault();
if (serviceRequest != null) existingItemCount++;
try
{
if (newServiceRequest.ServiceRequestService != null)
{
serviceRequestServiceId = newServiceRequest.ServiceRequestService.Id;
newServiceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices
where srs.Id == serviceRequestServiceId
select srs).SingleOrDefault();
}
if (serviceRequest.Update(newServiceRequest))
{
db.ServiceRequests.Attach(serviceRequest);
db.Entry(serviceRequest).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
updatedItemCount++;
}
}
catch (Exception)// e)
{
}
}
db.SaveChanges();
result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ")";
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static bool UpdateServiceRequestService(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, Ia.Ngn.Cl.Model.ServiceRequestService serviceRequestService, out string result)
{
bool b;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequest.Id select sr).SingleOrDefault();
if (serviceRequest.ServiceRequestService != serviceRequestService)
{
serviceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestService.Id select srs).SingleOrDefault();
db.ServiceRequests.Attach(serviceRequest);
db.Entry(serviceRequest).Property(u => u.ServiceRequestService).IsModified = true;
db.SaveChanges();
result = "Success: ServiceRequests ServiceRequestService updated. ";
b = true;
}
else
{
result = "Warning: ServiceRequests ServiceRequestService value was not updated because its the same. ";
b = false;
}
}
return b;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static void UpdateForADateTimeRangeWithOutputDataTable(DataTable dataTable, DateTime dateTime, out string result)
{
// below: the SQL statement should be within the dataTable.TableName variable
int number, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
int serviceRequestId;
string sql, r, customerAddress;
ArrayList newServiceRequestIdArryList;
DateTime startDateTime, endDateTime;
Match match;
Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
result = r = string.Empty;
startDateTime = DateTime.MinValue;
if (dataTable != null)
{
sql = dataTable.TableName;
// 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;
// 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;
match = Regex.Match(sql, @".+'(\d{2})\/(\d{2})\/(\d{4})'.+'(\d{2})\/(\d{2})\/(\d{4})'.+", RegexOptions.Singleline);
// 1 2 3 4 5 6
if (match.Success)
{
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
readItemCount = dataTable.Rows.Count;
//if (dataTable.Rows.Count > 0)
//{
startDateTime = DateTime.Parse(match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value);
endDateTime = DateTime.Parse(match.Groups[6].Value + "-" + match.Groups[5].Value + "-" + match.Groups[4].Value);
serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ListWithinDateTimeRange(startDateTime, endDateTime);
existingItemCount = serviceRequestList.Count;
newServiceRequestIdArryList = new ArrayList(dataTable.Rows.Count + 1);
foreach (DataRow dataRow in dataTable.Rows)
{
number = int.Parse(dataRow["SRV_NO"].ToString());
if (Ia.Ngn.Cl.Model.Business.Service.ServiceHasEightDigitsAndIsWithinAllowedDomainList(number))
{
serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
customerAddress = dataRow["ADDRESS"].ToString();
serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
{
Id = serviceRequestId,
Number = number,
CustomerAddress = customerAddress,
AreaId = serviceAddress.AreaId,
CustomerCategoryId = int.Parse(dataRow["CUST_CAT_ID"].ToString()),
CustomerId = int.Parse(dataRow["ACCOUNT_NO"].ToString()),
CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
Serial = int.Parse(dataRow["SRV_SER_NO"].ToString()),
ServiceCategoryId = int.Parse(dataRow["SRV_CAT_ID"].ToString()),
ServiceId = int.Parse(dataRow["SRV_ID"].ToString()),
Balance = double.Parse(dataRow["BALANCE"].ToString()),
Status = int.Parse(dataRow["STATUS"].ToString())
};
serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
if (serviceRequest == null)
{
newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
db.ServiceRequests.Add(newServiceRequest);
insertedItemCount++;
}
else
{
// below: copy values from newServiceRequest to serviceRequest
if (serviceRequest.Update(newServiceRequest))
{
db.ServiceRequests.Attach(serviceRequest);
db.Entry(serviceRequest).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
updatedItemCount++;
}
}
newServiceRequestIdArryList.Add(serviceRequestId);
}
else
{
r += "Number: " + number + " is not within allowed domain list, ";
}
}
// below: this function will remove values that were not present in the reading
if (serviceRequestList.Count > 0)
{
foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
{
if (!newServiceRequestIdArryList.Contains(sr.Id))
{
serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
db.ServiceRequests.Remove(serviceRequest);
// below: we will also remove SRT records referensing this SR
serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
deletedItemCount++;
}
}
}
db.SaveChanges();
result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ")" + r;
//}
//else
//{
// result = "(" + readItemCount + "/?/?)";
//}
}
}
else
{
result = "(?/?/?: SQL in TableName is unmatched)";
}
}
else
{
result = "(dataTable == null/?/?)";
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static string UpdateForServiceRequestWithOutputDataTableAndIdRange(DataTable dataTable, out List<string> insertedOrUpdatedOrDeletedServiceList)
{
// below: the SQL statement should be within the dataTable.TableName variable
int serviceRequestId, start, end, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
string sql, r, customerAddress, result;
ArrayList newServiceRequestIdArrayList;
Match match;
Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
List<int> numbersNotWithinAllowedDomainList;
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
result = r = string.Empty;
numbersNotWithinAllowedDomainList = new List<int>();
insertedOrUpdatedOrDeletedServiceList = new List<string>();
if (dataTable != null)
{
sql = dataTable.TableName;
// select * from SRV_REQ_FIPER where SRV_REQ_ID >= 110000 and SRV_REQ_ID <= 321203 order by REQ_DATE asc, SRV_REQ_ID asc
match = Regex.Match(sql, @"SRV_REQ_ID >= (\d+) and SRV_REQ_ID <= (\d+) ", RegexOptions.Singleline);
// 1 2
if (match.Success)
{
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
readItemCount = dataTable.Rows.Count;
start = int.Parse(match.Groups[1].Value);
end = int.Parse(match.Groups[2].Value);
serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ListWithinIdRange(start, end);
existingItemCount = serviceRequestList.Count;
newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
foreach (DataRow dataRow in dataTable.Rows)
{
if (int.TryParse(dataRow["SRV_NO"].ToString(), out int number))
{
if (Ia.Ngn.Cl.Model.Business.Service.ServiceHasEightDigitsAndIsWithinAllowedDomainList(number))
{
serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
if (Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceRequestIdIsAllowedForProcessing(serviceRequestId))
{
customerAddress = dataRow["ADDRESS"].ToString();
serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
{
Id = serviceRequestId,
Number = number,
CustomerAddress = customerAddress,
AreaId = serviceAddress.AreaId,
CustomerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0,
CustomerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0,
CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
Serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0,
ServiceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0,
ServiceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0,
Balance = double.Parse(dataRow["BALANCE"].ToString()),
Status = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0
};
serviceRequest = (from sr in serviceRequestList
where sr.Id == newServiceRequest.Id
select sr).SingleOrDefault();
if (serviceRequest == null)
{
newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
db.ServiceRequests.Add(newServiceRequest);
insertedOrUpdatedOrDeletedServiceList.Add(newServiceRequest.Number.ToString());
insertedItemCount++;
}
else
{
// below: copy values from newServiceRequest to serviceRequest
if (serviceRequest.UpdateSkipServiceRequestServiceAndCheckIfOnlyBalanceWasUpdated(newServiceRequest, out bool onlyBalanceWasUpdated))
{
db.ServiceRequests.Attach(serviceRequest);
db.Entry(serviceRequest).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
if (!onlyBalanceWasUpdated) insertedOrUpdatedOrDeletedServiceList.Add(serviceRequest.Number.ToString());
updatedItemCount++;
}
}
newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
}
else
{
}
}
else
{
numbersNotWithinAllowedDomainList.Add(number);
}
}
else
{
//
}
}
/*
if (numbersNotWithinAllowedDomainList.Count > 0)
{
r = "Numbers not within allowed domain list: ";
foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
r = r.Trim(',');
}
*/
// below: this function will remove values that were not present in the reading
if (serviceRequestList.Count > 0)
{
foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
{
if (!newServiceRequestIdArrayList.Contains(sr.Id))
{
serviceRequest = (from sr2 in db.ServiceRequests
where sr2.Id == sr.Id
select sr2).SingleOrDefault();
db.ServiceRequests.Remove(serviceRequest);
// below: we will also remove SRT records referensing this SR
serviceRequestTypeList = (from srt in db.ServiceRequestTypes
where srt.ServiceRequest.Id == sr.Id
select srt).ToList();
foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
insertedOrUpdatedOrDeletedServiceList.Add(serviceRequest.Number.ToString());
deletedItemCount++;
}
}
}
db.SaveChanges();
//if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
//else isUpdated = false;
result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ")" + r;
}
}
else
{
result = "(?/?/?: SQL in TableName is unmatched)";
}
}
else
{
result = "(dataTable == null/?/?)";
}
return result;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static void UpdateForServiceRequestWithOutputDataTableAndService(DataTable dataTable, string service, out bool isUpdated, out Ia.Cl.Model.Result result)
{
int serviceRequestId, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
string sql, sqlService, r;
int id, number, areaId, customerCategoryId, customerId, serial, serviceCategoryId, serviceId, statusId;
double balance;
string customerAddress, customerName;
DateTime requestDateTime;
ArrayList newServiceRequestIdArrayList;
Match match;
Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
List<int> numbersNotWithinAllowedDomainList;
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
isUpdated = false;
readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
numbersNotWithinAllowedDomainList = new List<int>();
result = new Ia.Cl.Model.Result();
if (dataTable != null)
{
// below: the SQL statement should be within the dataTable.TableName variable
sql = dataTable.TableName;
// select * from SRV_REQ_FIPER where SRV_NO = 23632222 order by SRV_REQ_ID asc
match = Regex.Match(sql, @"SRV_NO = (\d+) order by SRV_REQ_ID asc", RegexOptions.Singleline);
if (match.Success)
{
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
readItemCount = dataTable.Rows.Count;
sqlService = match.Groups[1].Value;
if (service == sqlService)
{
serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(service);
existingItemCount = serviceRequestList.Count;
newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
foreach (DataRow dataRow in dataTable.Rows)
{
if (int.TryParse(dataRow["SRV_NO"].ToString(), out number))
{
if (Ia.Ngn.Cl.Model.Business.Service.ServiceHasEightDigitsAndIsWithinAllowedDomainList(number))
{
serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
customerAddress = dataRow["ADDRESS"].ToString();
customerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0;
customerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0;
customerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString());
requestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString());
serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0;
serviceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0;
serviceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0;
balance = double.Parse(dataRow["BALANCE"].ToString());
statusId = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0;
if (Ia.Ngn.Cl.Model.Data.ServiceRequest.StatusSortedList.ContainsKey(statusId))
{
if (Ia.Ngn.Cl.Model.Data.ServiceRequest.CustomerCategorySortedList.ContainsKey(customerCategoryId))
{
if (Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceCategorySortedList.ContainsKey(serviceCategoryId))
{
if (Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceSortedList.ContainsKey(serviceId))
{
serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
{
Id = serviceRequestId,
Number = number,
CustomerAddress = customerAddress,
AreaId = serviceAddress.AreaId,
CustomerCategoryId = customerCategoryId,
CustomerId = customerId,
CustomerName = customerName,
RequestDateTime = requestDateTime,
Serial = serial,
ServiceCategoryId = serviceCategoryId,
ServiceId = serviceId,
Balance = balance,
Status = statusId
};
serviceRequest = (from sr in serviceRequestList
where sr.Id == newServiceRequest.Id
select sr).SingleOrDefault();
if (serviceRequest == null)
{
newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
db.ServiceRequests.Add(newServiceRequest);
insertedItemCount++;
}
else
{
// below: copy values from newServiceRequest to serviceRequest
if (serviceRequest.UpdateSkipServiceRequestService(newServiceRequest))
{
db.ServiceRequests.Attach(serviceRequest);
db.Entry(serviceRequest).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
updatedItemCount++;
}
}
newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
}
else result.AddError("Service " + serviceId + " is undefined.");
}
else result.AddError("ServiceCategory " + serviceCategoryId + " is undefined.");
}
else result.AddError("CustomerCategory " + customerCategoryId + " is undefined.");
}
else result.AddError("Status " + statusId + " is undefined.");
}
else numbersNotWithinAllowedDomainList.Add(number);
}
else
{
//
}
}
/*
if (numbersNotWithinAllowedDomainList.Count > 0)
{
r = "Numbers not within allowed domain list: ";
foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
r = r.Trim(',');
}
*/
// below: this function will remove values that were not present in the reading
if (serviceRequestList.Count > 0)
{
foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
{
if (!newServiceRequestIdArrayList.Contains(sr.Id))
{
serviceRequest = (from sr2 in db.ServiceRequests
where sr2.Id == sr.Id
select sr2).SingleOrDefault();
db.ServiceRequests.Remove(serviceRequest);
// below: we will also remove SRT records referencing this SR
serviceRequestTypeList = (from srt in db.ServiceRequestTypes
where srt.ServiceRequest.Id == sr.Id
select srt).ToList();
foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
deletedItemCount++;
}
}
}
db.SaveChanges();
if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
else isUpdated = false;
result.AddSuccess("(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ")");
}
else
{
throw new ArgumentException(@"UpdateForServiceRequestWithOutputDataTableAndService(): service != sqlService, service: " + service + ", sqlService: " + sqlService);
}
}
}
else result.AddError("(?/?/?: SQL in TableName is unmatched)");
}
else result.AddError("(dataTable == null/?/?)");
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> ServiceToServiceRequestDictionary(List<int> domainList)
{
string key;
List<string> stringDomainList;
List<Ia.Ngn.Cl.Model.ServiceRequest> list;
Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> dictionary;
stringDomainList = new List<string>();
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
if (domainList.Count > 0)
{
foreach (int i in domainList) stringDomainList.Add(i.ToString());
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();
dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>(list.Count);
foreach (var sr in list)
{
key = sr.Number.ToString();
if (dictionary.ContainsKey(key))
{
dictionary[key] = sr;
}
else dictionary[key] = sr;
}
}
else dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>();
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Hashtable NumberToCustomerAddressHashtable(List<int> domainList)
{
Hashtable hashtable;
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
if (domainList.Count > 0)
{
serviceRequestList = (from sr in db.ServiceRequests
where domainList.Contains(sr.Number / 10000) || domainList.Contains(sr.Number / 1000)
select sr).AsNoTracking().ToList();
hashtable = new Hashtable(serviceRequestList.Count);
foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList.OrderBy(u => u.Id)) hashtable[sr.Number.ToString()] = sr.CustomerAddress;
}
else hashtable = new Hashtable();
}
return hashtable;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static DateTime LatestRequestDateTime(int number)
{
DateTime dateTime;
Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(number);
if (serviceRequestList.Count > 0)
{
serviceRequest = serviceRequestList.OrderByDescending(u => u.Id).FirstOrDefault();
dateTime = serviceRequest.RequestDateTime;
}
else
{
dateTime = DateTime.MinValue;
}
}
return dateTime;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.ServiceRequest> LastN(int numberOfServiceRequests)
{
// below:
List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
serviceRequestList = null;
try
{
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
serviceRequestList = (from sr in db.ServiceRequests orderby sr.RequestDateTime descending select sr).Take(numberOfServiceRequests).ToList();
}
}
catch (Exception)
{
//resultLabel.Text = "Error during retrieval of data for \"" + ip + "\": " + ex.Message + ". ";
//resultLabel.CssClass = "error";
}
return serviceRequestList;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.ServiceRequest> ForRequestDate(DateTime requestDate)
{
DateTime nextDate;
List<Ia.Ngn.Cl.Model.ServiceRequest> list;
// below: 00:00 time values
requestDate = requestDate.Date;
nextDate = requestDate.AddDays(1);
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
list = (from sr in db.ServiceRequests
where sr.RequestDateTime >= requestDate && sr.RequestDateTime < nextDate
orderby sr.RequestDateTime descending
select sr).AsNoTracking().ToList();
}
return list;
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, string> ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary
{
get
{
if (serviceToAccessNameWithinAllowedToBeMigratedOltDictionary == null || serviceToAccessNameWithinAllowedToBeMigratedOltDictionary.Count == 0)
{
lock (objectLock)
{
serviceToAccessNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary;
}
}
return serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
private static Dictionary<string, string> _ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary
{
get
{
Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
Dictionary<string, Ia.Ngn.Cl.Model.Business.ServiceAddress> serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary;
var areaSymbolList = Ia.Ngn.Cl.Model.Business.Service.AllowedToBeMigratedOltSymbolList;
var kuwaitOfnAreaList = (from k in Ia.Ngn.Cl.Model.Data.Service.KuwaitOfnAreaList
where areaSymbolList.Contains(k.Symbol)
select k.Id).ToList();
var serviceToDbNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToDbNameWithinAllowedToBeMigratedOltDictionary;
var serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToCustomerAddressWithinAllowedToBeMigratedOltDictionary;
serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, Ia.Ngn.Cl.Model.Business.ServiceAddress>(serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary.Count);
foreach (KeyValuePair<string, string> kvp in serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary)
{
var service = kvp.Key;
var address = kvp.Value;
serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(service, address, out string level);
serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary[service] = serviceAddress;
}
serviceToAccessNameWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, string>(serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary.Count);
var ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary;
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
var accessList = (from a in db.Accesses
where kuwaitOfnAreaList.Contains(a.AreaId)
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();
foreach (var access in accessList)
{
if (ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary.ContainsKey(access.Id))
{
access.Name = ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary[access.Id];
}
else access.Name = string.Empty;
}
foreach (var kvp in serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary)
{
serviceAddress = new Ia.Ngn.Cl.Model.Business.ServiceAddress();
serviceAddress.AreaId = kvp.Value.AreaId;
serviceAddress.Block = kvp.Value.Block;
serviceAddress.Street = kvp.Value.Street;
serviceAddress.Boulevard = kvp.Value.Boulevard;
serviceAddress.PremisesOld = kvp.Value.PremisesOld;
serviceAddress.PremisesNew = kvp.Value.PremisesNew;
serviceAddress.Paci = kvp.Value.Paci;
var statisticalAccess = Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList, out string note2);
if (statisticalAccess != null) statisticalAccess.Note = note2;
serviceToAccessNameWithinAllowedToBeMigratedOltDictionary[kvp.Key] = statisticalAccess != null ? statisticalAccess.Name : string.Empty;
}
}
return serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, List<string>> AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary
{
get
{
DateTime now;
Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
now = DateTime.UtcNow.AddHours(3);
validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.TwelveHours;
if (accessNameToServiceListWithinAllowedToBeMigratedOltDictionary == null || accessNameToServiceListWithinAllowedToBeMigratedOltDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, accessNameToServiceListWithinAllowedToBeMigratedOltDictionaryTimestamp))
{
lock (objectLock)
{
accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary();
}
}
return accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
private static Dictionary<string, List<string>> _AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary()
{
string key, value;
List<string> valueList;
var now = DateTime.UtcNow.AddHours(3);
var serviceToAccessNameDictionary = ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary;
accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, List<string>>(serviceToAccessNameDictionary.Count);
foreach (var kvp in serviceToAccessNameDictionary)
{
key = kvp.Value;
if (!string.IsNullOrEmpty(key))
{
value = kvp.Key;
valueList = new List<string>();
if (!accessNameToServiceListWithinAllowedToBeMigratedOltDictionary.ContainsKey(key))
{
valueList.Add(value);
accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key] = valueList;
}
else
{
valueList = accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key];
valueList.Add(value);
accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key] = valueList;
}
}
}
accessNameToServiceListWithinAllowedToBeMigratedOltDictionaryTimestamp = now;
return accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<string> DbPeerServiceList(List<string> serviceList)
{
List<string> list;
if (serviceList.Count > 0)
{
var dbNameToServiceListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.DbNameToServiceListDictionary;
var listList = (from d in dbNameToServiceListDictionary
where serviceList.Any(u => d.Value.Contains(u))
select d.Value).ToList();
var list0 = listList.SelectMany(x => x).ToList();
list = list0.Distinct().ToList();
}
else list = new List<string>();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, List<string>> DbNameToServiceListDictionary
{
get
{
Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
var dateTime = DateTime.UtcNow.AddHours(3);
validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.TwelveHours;
if (dbNameToServiceListDictionary == null || dbNameToServiceListDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, dateTime, dbNameToServiceListDictionaryTimestamp))
{
lock (objectLock)
{
dbNameToServiceListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._DbNameToServiceListDictionary();
}
}
return dbNameToServiceListDictionary;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
private static Dictionary<string, List<string>> _DbNameToServiceListDictionary()
{
string key, value;
DateTime now;
List<string> valueList;
now = DateTime.UtcNow.AddHours(3);
var serviceToDbNameDictionary = ServiceToDbNameWithinAllowedToBeMigratedOltDictionary;
dbNameToServiceListDictionary = new Dictionary<string, List<string>>(serviceToDbNameDictionary.Count);
foreach (KeyValuePair<string, string> kvp in serviceToDbNameDictionary)
{
key = kvp.Value;
if (!string.IsNullOrEmpty(key) && key != ":")
{
value = kvp.Key;
valueList = new List<string>();
if (!dbNameToServiceListDictionary.ContainsKey(key))
{
valueList.Add(value);
dbNameToServiceListDictionary[key] = valueList;
}
else
{
valueList = dbNameToServiceListDictionary[key];
valueList.Add(value);
dbNameToServiceListDictionary[key] = valueList;
}
}
}
dbNameToServiceListDictionaryTimestamp = now;
return dbNameToServiceListDictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, string> ServiceToDbNameWithinAllowedToBeMigratedOltDictionary
{
get
{
DateTime now;
Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
now = DateTime.UtcNow.AddHours(3);
validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.TwelveHours;
if (serviceToDbNameDictionary == null || serviceToDbNameDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, serviceToDbNameDictionaryAndServiceToCustomerAddressDictionaryTimestamp))
{
lock (objectLock)
{
Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(out serviceToDbNameDictionary, out serviceToCustomerAddressDictionary);
}
}
return serviceToDbNameDictionary;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, string> ServiceToCustomerAddressWithinAllowedToBeMigratedOltDictionary
{
get
{
DateTime now;
Ia.Ngn.Cl.Model.Business.Default.ValidityOfData validityOfData;
now = DateTime.UtcNow.AddHours(3);
validityOfData = Ia.Ngn.Cl.Model.Business.Default.ValidityOfData.Day;
if (serviceToCustomerAddressDictionary == null || serviceToCustomerAddressDictionary.Count == 0 || !Ia.Ngn.Cl.Model.Business.Default.DataIsValid(validityOfData, now, serviceToDbNameDictionaryAndServiceToCustomerAddressDictionaryTimestamp))
{
lock (objectLock)
{
Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(out serviceToDbNameDictionary, out serviceToCustomerAddressDictionary);
}
}
return serviceToCustomerAddressDictionary;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
private static void _ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(out Dictionary<string, string> serviceToDbNameDictionary, out Dictionary<string, string> serviceToCustomerAddressDictionary)
{
Dictionary<string, int> serviceToLastSerialDictionary;
var areaSymbolList = Ia.Ngn.Cl.Model.Business.Service.AllowedToBeMigratedOltSymbolList;
var areaIdList = (from k in Ia.Ngn.Cl.Model.Data.Service.KuwaitOfnAreaList
where areaSymbolList.Contains(k.Symbol)
select k.Id).ToList();
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
var statisticalVariableList = (from sr in db.ServiceRequests
//join srs in db.ServiceRequestServices on sr.Number.ToString() equals srs.Service // on sr.ServiceRequestService.Id equals srs.Id
join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id into srts
from srt in srts.DefaultIfEmpty()
where areaIdList.Contains(sr.AreaId) && (srt == null || srt.TypeId == 1 || srt.TypeId == 2)
select new Ia.Ngn.Cl.Model.Business.ServiceRequestStatisticalVariable
{
//Provisioned = srs.Provisioned,
ServiceRequestId = sr.Id,
Service = sr.Number.ToString(),
Serial = sr.Serial,
AreaId = sr.AreaId,
CustomerAddress = sr.CustomerAddress,
/* CustomerName = sr.CustomerName, */
ServiceRequestTypeValue = srt == null ? string.Empty : srt.Value,
}).ToList();
serviceToLastSerialDictionary = new Dictionary<string, int>(statisticalVariableList.Count);
serviceToDbNameDictionary = new Dictionary<string, string>(statisticalVariableList.Count);
serviceToCustomerAddressDictionary = new Dictionary<string, string>(statisticalVariableList.Count);
foreach (var sv in statisticalVariableList/*.Where(u => u.Provisioned)*/.OrderBy(u => u.ServiceRequestId))
{
serviceToLastSerialDictionary[sv.Service] = sv.Serial;
}
foreach (var sv in statisticalVariableList/*.Where(u => u.Provisioned)*/.OrderBy(u => u.ServiceRequestId))
{
if (serviceToLastSerialDictionary[sv.Service] == sv.Serial)
{
if (serviceToDbNameDictionary.ContainsKey(sv.Service))
{
// ServiceRequestTypes values do not contain ":"
if (!serviceToDbNameDictionary[sv.Service].Contains(":")) serviceToDbNameDictionary[sv.Service] += ":" + sv.ServiceRequestTypeValue;
}
else serviceToDbNameDictionary[sv.Service] = sv.ServiceRequestTypeValue;
if (!serviceToCustomerAddressDictionary.ContainsKey(sv.Service)) serviceToCustomerAddressDictionary[sv.Service] = sv.CustomerAddress;
}
}
}
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<int> ServiceRequestDomainList
{
get
{
if (serviceRequestDomainList == null || serviceRequestDomainList.Count == 0)
{
lock (objectLock)
{
serviceRequestDomainList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceRequestDomainList;
}
}
return serviceRequestDomainList;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
private static List<int> _ServiceRequestDomainList
{
get
{
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
var fourDigitList = (from sr in db.ServiceRequests select sr.Number.ToString().Substring(0, 4)).Distinct().ToList();
var fiveDigitList = (from sr in db.ServiceRequests select sr.Number.ToString().Substring(0, 5)).Distinct().ToList();
var list = new List<string>();
foreach (var fourDigit in fourDigitList)
{
if (
fiveDigitList.Any(u => u == fourDigit + "0")
&& fiveDigitList.Any(u => u == fourDigit + "1")
&& fiveDigitList.Any(u => u == fourDigit + "2")
&& fiveDigitList.Any(u => u == fourDigit + "3")
&& fiveDigitList.Any(u => u == fourDigit + "4")
&& fiveDigitList.Any(u => u == fourDigit + "5")
&& fiveDigitList.Any(u => u == fourDigit + "6")
&& fiveDigitList.Any(u => u == fourDigit + "7")
&& fiveDigitList.Any(u => u == fourDigit + "8")
&& fiveDigitList.Any(u => u == fourDigit + "9")
) list.Add(fourDigit);
else
{
foreach (var fiveDigit in fiveDigitList)
{
if (fiveDigit.StartsWith(fourDigit)) list.Add(fiveDigit);
}
}
}
serviceRequestDomainList = list.Select(int.Parse).ToList();
serviceRequestDomainList.Sort();
}
return serviceRequestDomainList;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<DateTime, int> RequestDateTimeWeekToCountDictionary()
{
var dictionary = new Dictionary<DateTime, int>();
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
// too complicated
/*
dictionary = (from sr in db.ServiceRequests
group sr by SqlFunctions.DateAdd("ww", SqlFunctions.DateDiff("ww", DateTime.MinValue, sr.RequestDateTime), DateTime.MinValue) into sr1
select new { RequestDateTime = sr1.Key }).ToList();//, ServiceCount = sr1.Count() }).AsNoTracking().ToDictionary(t => t.RequestDateTime, t => t.ServiceCount);
*/
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static string ToSimpleTextString(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest)
{
StringBuilder sb;
sb = new StringBuilder();
// Id Number Serial Status RequestDateTime Service ServiceCategory CustomerId CustomerName CustomerCategory CustomerAddress Balance
sb.AppendLine("Id: " + serviceRequest.Id);
sb.AppendLine("Number: " + serviceRequest.Number + "/" + serviceRequest.Serial);
//sb.AppendLine("Serial: " + serviceRequest.Serial);
sb.AppendLine("Status: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.StatusSortedList[serviceRequest.Status].ToString());
sb.AppendLine("RequestDateTime: " + serviceRequest.RequestDateTime.ToString("yyyy-MM-dd HH:mm"));
sb.AppendLine("Service: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceSortedList[serviceRequest.ServiceId].ToString());
//sb.AppendLine("ServiceCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceCategorySortedList[serviceRequest.ServiceCategoryId].ToString());
//sb.AppendLine("CustomerId: " + serviceRequest.CustomerId);
sb.AppendLine("CustomerName: " + serviceRequest.CustomerName);
//sb.AppendLine("CustomerCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.CustomerCategorySortedList[serviceRequest.CustomerCategoryId].ToString());
sb.AppendLine("CustomerAddress: " + serviceRequest.CustomerAddress);
//sb.AppendLine("Balance: " + serviceRequest.Balance);
return sb.ToString();
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
///
/// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
/// 2. Add "using System.Reflection".
/// 3. Manifest resource stream will start with the project namespace, the location of XML file.
///
/// </summary>
private static XDocument XDocument
{
get
{
if (xDocument == null)
{
lock (objectLock)
{
Assembly _assembly;
StreamReader streamReader;
_assembly = Assembly.GetExecutingAssembly();
streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.service-request.xml"));
try
{
if (streamReader.Peek() != -1)
{
xDocument = System.Xml.Linq.XDocument.Load(streamReader);
}
}
catch (Exception)
{
}
finally
{
}
}
}
return xDocument;
}
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
}