1: using Ia.Ngn.Cl.Model.Business; // Needed for ServerExtension
2: using System;
3: using System.Collections;
4: using System.Collections.Generic;
5: using System.Data;
6: using System.Data.Entity;
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-2019 Jasem Y. Al-Shamlan (info@ia.com.kw), Internet 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:
44: ////////////////////////////////////////////////////////////////////////////
45:
46: /// <summary>
47: ///
48: /// </summary>
49: public ServiceRequest() { }
50:
51: ////////////////////////////////////////////////////////////////////////////
52:
53: /// <summary>
54: ///
55: /// </summary>
56: public static Dictionary<int, string> SystemCode
57: {
58: get
59: {
60: if (systemCode == null || systemCode.Count == 0)
61: {
62: // select code_id, code_name from system_codes
63:
64: systemCode = new Dictionary<int, string>(100)
65: {
66: [6008] = "قطع مطالبات",
67: [6009] = "قطع إدارى",
68: [4007] = "إفراج",
69: [4008] = "قيد التجديد",
70: [4009] = "قيد إخطار الجهة",
71: [4010] = "تسيل جزئي",
72: [1001] = "جديد",
73: [1002] = "مطلوب",
74: [1003] = "متاح",
75: [2001] = "قيد الموافقة الفنية",
76: [2002] = "قيد الدفع",
77: [2003] = "قيد التنفيذ",
78: [2004] = "تعذر التنفيذ",
79: [2005] = "تم التنفيذ",
80: [3001] = "نقل من رصيد خدمة - دائن",
81: [3002] = "نقل من رصيد خدمة - مدين",
82: [4001] = "سارية",
83: [4002] = "منتهية",
84: [4003] = "مستردة",
85: [4004] = "تسييل",
86: [5001] = "سارية",
87: [5002] = "منتهية",
88: [5003] = "ملغاة",
89: [4005] = "ملغاة",
90: [5004] = "مبدئية",
91: [6001] = "قيد التنفيذ",
92: [6002] = "تم التنفيذ",
93: [6003] = "تعذر التنفيذ",
94: [4006] = "جديدة",
95: [7001] = "يعمل",
96: [7002] = "قطع مؤقت",
97: [7003] = "قطع مبرمج",
98: [1004] = "مقطوع",
99: [5005] = "متأخرة السداد",
100: [7004] = "قيد التنفيذ",
101: [8001] = "قيد التنفيذ",
102: [8002] = "تم التنفيذ",
103: [8003] = "تعذر التنفيذ",
104: [1005] = "يعمل",
105: [7005] = "تعذر التنفيذ",
106: [9001] = "دائن",
107: [9002] = "مدين",
108: [7006] = "رفع نهائي",
109: [1203] = "مبدئية",
110: [1201] = "سارية",
111: [1202] = "ملغاة",
112: [1101] = "انهاء",
113: [1102] = "قطع مؤقت",
114: [1301] = "قطع مبرمج",
115: [1302] = "أمر عمل",
116: [7007] = "رفع محاسبة",
117: [1401] = "نص",
118: [1402] = "رقم",
119: [1403] = "تاريخ",
120: [1404] = "تاريخ/وقت",
121: [7008] = "قطع معاكسة",
122: [1501] = "خدمة أساسية",
123: [1502] = "تشغيل خدمة فرعية",
124: [1503] = "إيقاف خدمة فرعية",
125: [1504] = "عملية",
126: [1505] = "منتج",
127: [1510] = "خدمات متنوعة",
128: [1103] = "إعادة",
129: [1506] = "خدمة اساسية - مجموعة",
130: [1507] = "خدمة تابعة",
131: [1508] = "خدمة مدمجة",
132: [2101] = "جديد",
133: [2102] = "تم الحساب",
134: [2103] = "نسخة جديدة",
135: [2201] = "ملف المكالمات الدولية",
136: [6004] = "إعادة الحالة",
137: [1601] = "مبدئية",
138: [1602] = "نهائية",
139: [1603] = "ملغاة",
140: [2006] = "ملغاة",
141: [7009] = "ملغى",
142: [7010] = "لا يعمل",
143: [2104] = "مفقود",
144: [5006] = "سارية - مؤقتة",
145: [5007] = "مرحلة",
146: [1701] = "عادي",
147: [1702] = "نطاق",
148: [1509] = "إيقاف خدمة أساسية"
149: };
150: }
151:
152: return systemCode;
153: }
154: }
155:
156: ////////////////////////////////////////////////////////////////////////////
157:
158: /// <summary>
159: ///
160: /// </summary>
161: public static SortedList ServiceCategorySortedList
162: {
163: get
164: {
165: int id;
166:
167: if (serviceCategorySortedList == null)
168: {
169: serviceCategorySortedList = new SortedList(10);
170:
171: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("categoryList").Elements("category"))
172: {
173: id = int.Parse(x.Attribute("id").Value);
174:
175: serviceCategorySortedList[id] = x.Attribute("arabicName").Value;
176: }
177: }
178:
179: return serviceCategorySortedList;
180: }
181: }
182:
183: ////////////////////////////////////////////////////////////////////////////
184:
185: /// <summary>
186: ///
187: /// </summary>
188: public static SortedList ServiceSortedList
189: {
190: get
191: {
192: int id;
193:
194: if (serviceSortedList == null)
195: {
196: serviceSortedList = new SortedList(10);
197:
198: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
199: {
200: id = int.Parse(x.Attribute("id").Value);
201:
202: serviceSortedList[id] = x.Attribute("arabicName").Value;
203: }
204: }
205:
206: return serviceSortedList;
207: }
208: }
209:
210: ////////////////////////////////////////////////////////////////////////////
211:
212: /// <summary>
213: ///
214: /// </summary>
215: public static SortedList CustomerCategorySortedList
216: {
217: get
218: {
219: int id;
220:
221: if (customerCategorySortedList == null)
222: {
223: customerCategorySortedList = new SortedList(10);
224:
225: foreach (XElement x in XDocument.Element("serviceRequest").Elements("customer").Elements("categoryList").Elements("category"))
226: {
227: id = int.Parse(x.Attribute("id").Value);
228:
229: customerCategorySortedList[id] = x.Attribute("arabicName").Value;
230: }
231: }
232:
233: return customerCategorySortedList;
234: }
235: }
236:
237: ////////////////////////////////////////////////////////////////////////////
238:
239: /// <summary>
240: ///
241: /// </summary>
242: public static SortedList StatusSortedList
243: {
244: get
245: {
246: int id;
247:
248: if (statusSortedList == null)
249: {
250: statusSortedList = new SortedList(10);
251:
252: foreach (XElement x in XDocument.Element("serviceRequest").Elements("statusList").Elements("status"))
253: {
254: id = int.Parse(x.Attribute("id").Value);
255:
256: statusSortedList[id] = x.Attribute("arabicName").Value;
257: }
258: }
259:
260: return statusSortedList;
261: }
262: }
263:
264: ////////////////////////////////////////////////////////////////////////////
265:
266: /// <summary>
267: ///
268: /// </summary>
269: public static List<int> ServiceIdAllowedForProcessingList
270: {
271: get
272: {
273: int id;
274:
275: if (serviceIdAllowedForProcessingList == null)
276: {
277: serviceIdAllowedForProcessingList = new List<int>(100);
278:
279: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
280: {
281: if (x.HasAttributes && x.Attribute("allowProcessing") != null)
282: {
283: if (x.Attribute("allowProcessing").Value == "true")
284: {
285: id = int.Parse(x.Attribute("id").Value);
286: serviceIdAllowedForProcessingList.Add(id);
287: }
288: }
289: }
290: }
291:
292: return serviceIdAllowedForProcessingList;
293: }
294: }
295:
296: ////////////////////////////////////////////////////////////////////////////
297:
298: /// <summary>
299: /// Return the Tuple range but with a reset of list so to get edge records
300: /// </summary>
301: public static Tuple<int, int> ServiceRequestIdStartEndRangeManagerWithReset(ref int index, out string result)
302: {
303: serviceRequestIdStartEndRangeBufferedListIndex = 0;
304:
305: return ServiceRequestIdStartEndRangeManager(ref index, out result);
306: }
307:
308: ////////////////////////////////////////////////////////////////////////////
309:
310: /// <summary>
311: ///
312: /// </summary>
313: public static Tuple<int, int> ServiceRequestIdStartEndRangeManager(ref int index, out string result)
314: {
315: int count, edgeBufferRange, lastestRangeList;
316: Tuple<int, int> tuple;
317: List<int> serviceRequestIdList;
318:
319: count = 50;
320: edgeBufferRange = 5000;
321: lastestRangeList = 21;
322:
323: if (serviceRequestIdStartEndRangeBufferedTupleList == null || serviceRequestIdStartEndRangeBufferedListIndex == 0)
324: {
325: serviceRequestIdStartEndRangeBufferedListIndex = index;
326:
327: using (var db = new Ia.Ngn.Cl.Model.Ngn())
328: {
329: serviceRequestIdList = (from sr in db.ServiceRequests orderby sr.Id ascending select sr.Id).ToList();
330: }
331:
332: serviceRequestIdStartEndRangeBufferedTupleList = Ia.Cl.Model.Default.OptimizedStartEndRangeBufferedList(serviceRequestIdList, count, edgeBufferRange);
333:
334: serviceRequestIdStartEndRangeBufferedListIndex = 0;
335: }
336:
337: if (serviceRequestIdStartEndRangeBufferedTupleList.Count > 0)
338: {
339: if (Ia.Ngn.Cl.Model.Business.Administration.NowIsOfficialWorkingTime)
340: {
341: // flip between last and latest range to favor reading fresh work orders quickly
342: if (Ia.Cl.Model.Default.RandomBool)
343: {
344: serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - Ia.Cl.Model.Default.Random(lastestRangeList) - 1;
345: serviceRequestIdLatestStartEndRangeBufferedListIndex = (serviceRequestIdLatestStartEndRangeBufferedListIndex >= 0) ? serviceRequestIdLatestStartEndRangeBufferedListIndex : 0;
346: }
347: else serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - 1;
348:
349: tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdLatestStartEndRangeBufferedListIndex];
350:
351: if (serviceRequestIdLatestStartEndRangeBufferedListIndex == serviceRequestIdStartEndRangeBufferedTupleList.Count - 1)
352: {
353: tuple = new Tuple<int, int>(tuple.Item1, tuple.Item2 + edgeBufferRange);
354: }
355:
356: result = "(latest: " + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdLatestStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
357: }
358: else
359: {
360: tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdStartEndRangeBufferedListIndex];
361:
362: result = "(historic: " + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
363:
364: serviceRequestIdStartEndRangeBufferedListIndex = Ia.Cl.Model.Default.IncrementListIndexOrRestart(serviceRequestIdStartEndRangeBufferedTupleList, serviceRequestIdStartEndRangeBufferedListIndex);
365: }
366: }
367: else
368: {
369: result = "(0-0 0/0)";
370:
371: tuple = null;
372: }
373:
374: index = serviceRequestIdStartEndRangeBufferedListIndex;
375:
376: return tuple;
377: }
378:
379: /*
380: ////////////////////////////////////////////////////////////////////////////
381:
382: /// <summary>
383: ///
384: /// </summary>
385: private static ArrayList SqlCommandsForSingleDaysArrayList
386: {
387: get
388: {
389: string sql;
390: DateTime startDateTime, endDateTime;
391: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
392:
393: if (sqlCommandsForSingleDaysArrayList == null || sqlCommandsForSingleDaysArrayList.Count == 0)
394: {
395: // below: start from the date with the oldest updated time
396: using (var db = new Ia.Ngn.Cl.Model.Ngn())
397: {
398: serviceRequest = (from q in db.ServiceRequests orderby q.Updated select q).Take(1).SingleOrDefault();
399:
400: if (serviceRequest != null)
401: {
402: startDateTime = serviceRequest.RequestDateTime;
403:
404: endDateTime = DateTime.UtcNow.AddHours(3);
405:
406: // below: the date format on each database to ensuer they are the same
407: //sql = @"select * from nls_session_parameters";
408:
409: // sql = @"SELECT * FROM SRV_REQ_FIPER WHERE ROWNUM = 1"; // @"DESCRIBE SRV_REQ_FIPER;";
410:
411: sqlCommandsForSingleDaysArrayList = new ArrayList();
412:
413: for (DateTime dateTime = startDateTime; dateTime < endDateTime; dateTime = dateTime.AddDays(1))
414: {
415: sql = OracleSqlCommandForGivenDateTime(dateTime);
416:
417: sqlCommandsForSingleDaysArrayList.Add(sql);
418: }
419: }
420: }
421: }
422:
423: return sqlCommandsForSingleDaysArrayList;
424: }
425: }*/
426:
427: ////////////////////////////////////////////////////////////////////////////
428:
429: /// <summary>
430: ///
431: /// </summary>
432: public static string AlterSessionOfCustomerDepartmentOracleDatabase
433: {
434: get
435: {
436: return @"alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS'";
437: }
438: }
439:
440: ////////////////////////////////////////////////////////////////////////////
441:
442: /// <summary>
443: /// Return list of service requests
444: /// </summary>
445: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List()
446: {
447: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
448:
449: using (var db = new Ia.Ngn.Cl.Model.Ngn())
450: {
451: serviceRequestList = (from sr in db.ServiceRequests select sr).ToList();
452: }
453:
454: return serviceRequestList;
455: }
456:
457: ////////////////////////////////////////////////////////////////////////////
458:
459: /// <summary>
460: /// Return a list of service requests that have numbers-serials within the passed list
461: /// </summary>
462: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<Ia.Ngn.Cl.Model.Business.ServiceRequest.NumberSerial> numberSerialList)
463: {
464: List<long> idList;
465: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
466:
467: idList = numberSerialList.IdList();
468:
469: using (var db = new Ia.Ngn.Cl.Model.Ngn())
470: {
471: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
472: where
473: //numberSerialList.Contains(q.Number, q.Serial) does not work
474: //((from r in numberSerialList where r.Number == q.Number && r.Serial == q.Serial select r) != null) does not work
475: //numberList.Any<int>(i=> i == q.Number) does not work
476: idList.Contains((long)sr.Number * 100 + sr.Serial)
477: select sr).ToList();
478: }
479:
480: return serviceRequestList;
481: }
482:
483: ////////////////////////////////////////////////////////////////////////////
484:
485: /// <summary>
486: /// Return a list of numbers, including changed-to numbers, that are related to the passed number list
487: /// </summary>
488: public static List<int> NumberList(List<int> numberList)
489: {
490: List<int> li, li2, li3;
491: List<string> serviceList;
492: List<Ia.Ngn.Cl.Model.ServiceRequest> list1, list2, list3, list4, list5, serviceRequestList;
493:
494: serviceList = (from n in numberList select n.ToString()).ToList();
495:
496: using (var db = new Ia.Ngn.Cl.Model.Ngn())
497: {
498: //
499: list1 = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
500: where numberList.Contains(sr.Number)
501: select sr).ToList();
502:
503: if (list1.Count > 0)
504: {
505: li = (from sr in list1 select sr.Number).Distinct().ToList();
506:
507: li2 = new List<int>();
508:
509: foreach (var srt in list1.SelectMany(u => u.ServiceRequestTypes))
510: {
511: if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) li2.Add(i);
512: }
513:
514: // below: this is the collect first changed-to number information
515: if (li2.Count > 0)
516: {
517: list2 = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
518: where li2.Contains(sr.Number)
519: select sr).ToList();
520:
521: if (list2.Count > 0)
522: {
523: li = (from sr in list2 select sr.Number).Distinct().ToList();
524:
525: li3 = new List<int>();
526:
527: foreach (var srt in list2.SelectMany(u => u.ServiceRequestTypes))
528: {
529: if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) li3.Add(i);
530: }
531:
532: // below: this is the collect second changed-to number information
533: if (li3.Count > 0)
534: {
535: list3 = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
536: where li3.Contains(sr.Number)
537: select sr).ToList();
538: }
539: else list3 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
540: }
541: else
542: {
543: list2 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
544: list3 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
545: }
546: }
547: else
548: {
549: list2 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
550: list3 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
551: }
552: }
553: else
554: {
555: list2 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
556: list3 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
557: }
558:
559:
560: //
561: list4 = (from srt in db.ServiceRequestTypes.Include(a => a.ServiceRequest)
562: where srt.TypeId == 11 && serviceList.Contains(srt.Value)
563: select srt.ServiceRequest).ToList();
564:
565: if (list4.Count > 0)
566: {
567: li = (from sr in list4 select sr.Number).Distinct().ToList();
568:
569: list5 = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
570: where li.Contains(sr.Number)
571: select sr).ToList();
572: }
573: else list5 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
574:
575: //
576: serviceRequestList = list1.Union(list2).Union(list3).Union(list4).Union(list5).Distinct().ToList();
577:
578: if (serviceRequestList.Count > 0)
579: {
580: numberList = (from n in serviceRequestList select n.Number).Distinct().ToList();
581: }
582: else numberList = new List<int>();
583: }
584:
585: return numberList.ToList();
586: }
587:
588: ////////////////////////////////////////////////////////////////////////////
589:
590: /// <summary>
591: /// Return a list of service requests that have numbers (including changed-to numbers) within the passed list
592: /// </summary>
593: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<int> numberList)
594: {
595: List<string> serviceList;
596: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList, viaServiceRequestTypeList;
597:
598: serviceList = (from n in numberList select n.ToString()).ToList();
599:
600: using (var db = new Ia.Ngn.Cl.Model.Ngn())
601: {
602: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService).Include(a => a.ServiceRequestTypes)
603: where numberList.Contains(sr.Number)
604: select sr).ToList();
605:
606: viaServiceRequestTypeList = (from srt in db.ServiceRequestTypes.Include(a => a.ServiceRequest).Include(a => a.ServiceRequest.ServiceRequestService)
607: where srt.TypeId == 11 && serviceList.Contains(srt.Value)
608: select srt.ServiceRequest).Include(a => a.ServiceRequestTypes).ToList();
609:
610: serviceRequestList = serviceRequestList.Union(viaServiceRequestTypeList).Distinct().ToList();
611: }
612:
613: return serviceRequestList.ToList();
614: }
615:
616: ////////////////////////////////////////////////////////////////////////////
617:
618: /// <summary>
619: /// Return a list of numbers, including changed-to numbers, that are related to the given service request list
620: /// </summary>
621: public static List<int> NumberFromServiceRequestListList(List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList)
622: {
623: List<int> list, changedList, numberList;
624:
625: if (serviceRequestList != null)
626: {
627: list = (from sr in serviceRequestList select sr.Number).ToList();
628:
629: changedList = new List<int>();
630:
631: foreach (var sr in serviceRequestList)
632: {
633: foreach (var srt in sr.ServiceRequestTypes)
634: {
635: if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) changedList.Add(i);
636: }
637: }
638:
639: numberList = list.Union(changedList).Distinct().ToList();
640: }
641: else numberList = new List<int>();
642:
643: return numberList;
644: }
645:
646: ////////////////////////////////////////////////////////////////////////////
647: ////////////////////////////////////////////////////////////////////////////
648:
649: /// <summary>
650: /// Return list of service requests with number
651: /// </summary>
652: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(int number)
653: {
654: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
655:
656: using (var db = new Ia.Ngn.Cl.Model.Ngn())
657: {
658: serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
659: }
660:
661: return serviceRequestList;
662: }
663:
664: ////////////////////////////////////////////////////////////////////////////
665:
666: /// <summary>
667: /// Return list of service requests with service
668: /// </summary>
669: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(string service)
670: {
671: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
672:
673: if (!string.IsNullOrEmpty(service))
674: {
675: if (int.TryParse(service, out int number))
676: {
677: using (var db = new Ia.Ngn.Cl.Model.Ngn())
678: {
679: serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
680: }
681: }
682: else
683: {
684: throw new ArgumentException(@"List(): service is not a number, service: " + service);
685: }
686: }
687: else serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
688:
689: return serviceRequestList;
690: }
691:
692: ////////////////////////////////////////////////////////////////////////////
693:
694: /// <summary>
695: /// Return a list of service requests that have numbers within the passed domain list
696: /// </summary>
697: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListByDomain(List<int> domainList)
698: {
699: List<string> stringDomainList;
700: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
701:
702: stringDomainList = new List<string>();
703:
704: using (var db = new Ia.Ngn.Cl.Model.Ngn())
705: {
706: if (domainList != null)
707: {
708: foreach (int i in domainList) stringDomainList.Add(i.ToString());
709:
710: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
711: where domainList.Any(u => sr.Number.ToString().StartsWith(u.ToString()))
712: select sr).ToList();
713: }
714: else
715: {
716: serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
717: }
718: }
719:
720: return serviceRequestList;
721: }
722:
723: ////////////////////////////////////////////////////////////////////////////
724:
725: /// <summary>
726: ///
727: /// </summary>
728: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinDateTimeRange(DateTime startDateTime, DateTime endDateTime)
729: {
730: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
731:
732: using (var db = new Ia.Ngn.Cl.Model.Ngn())
733: {
734: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService) where sr.RequestDateTime >= startDateTime && sr.RequestDateTime < endDateTime select sr).ToList();
735: }
736:
737: return serviceRequestList;
738: }
739:
740: ////////////////////////////////////////////////////////////////////////////
741:
742: /// <summary>
743: ///
744: /// </summary>
745: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinIdRange(int start, int end)
746: {
747: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
748:
749: using (var db = new Ia.Ngn.Cl.Model.Ngn())
750: {
751: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService) where sr.Id >= start && sr.Id <= end select sr).ToList();
752: }
753:
754: return serviceRequestList;
755: }
756:
757: ////////////////////////////////////////////////////////////////////////////
758: ////////////////////////////////////////////////////////////////////////////
759:
760: /// <summary>
761: ///
762: /// </summary>
763: public static void UpdateWithServiceList(List<string> serviceList, List<Ia.Ngn.Cl.Model.ServiceRequest> newServiceRequestList, out string result)
764: {
765: int readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount, serviceRequestId;
766: string serviceRequestServiceId;
767: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
768:
769: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
770: result = string.Empty;
771:
772: readItemCount = newServiceRequestList.Count;
773:
774: using (var db = new Ia.Ngn.Cl.Model.Ngn())
775: {
776: foreach (Ia.Ngn.Cl.Model.ServiceRequest newServiceRequest in newServiceRequestList)
777: {
778: serviceRequestId = newServiceRequest.Id;
779:
780: serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequestId select sr).SingleOrDefault();
781:
782: existingItemCount = (serviceRequest != null) ? 1 : 0;
783:
784: try
785: {
786: if (newServiceRequest.ServiceRequestService != null)
787: {
788: serviceRequestServiceId = newServiceRequest.ServiceRequestService.Id;
789:
790: newServiceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestServiceId select srs).SingleOrDefault();
791: }
792:
793: if (serviceRequest.Update(newServiceRequest))
794: {
795: db.ServiceRequests.Attach(serviceRequest);
796: db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
797:
798: updatedItemCount++;
799: }
800: }
801: catch (Exception e)
802: {
803:
804: }
805: }
806:
807: db.SaveChanges();
808:
809: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") ";
810: }
811: }
812:
813: ////////////////////////////////////////////////////////////////////////////
814:
815: /// <summary>
816: ///
817: /// </summary>
818: public static bool UpdateServiceRequestService(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, Ia.Ngn.Cl.Model.ServiceRequestService serviceRequestService, out string result)
819: {
820: bool b;
821:
822: using (var db = new Ia.Ngn.Cl.Model.Ngn())
823: {
824: serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequest.Id select sr).SingleOrDefault();
825:
826: if (serviceRequest.ServiceRequestService != serviceRequestService)
827: {
828: serviceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestService.Id select srs).SingleOrDefault();
829:
830: db.ServiceRequests.Attach(serviceRequest);
831: db.Entry(serviceRequest).Property(x => x.ServiceRequestService).IsModified = true;
832:
833: db.SaveChanges();
834:
835: result = "Success: ServiceRequests ServiceRequestService updated. ";
836: b = true;
837: }
838: else
839: {
840: result = "Warning: ServiceRequests ServiceRequestService value was not updated because its the same. ";
841:
842: b = false;
843: }
844: }
845:
846: return b;
847: }
848:
849: ////////////////////////////////////////////////////////////////////////////
850:
851: /// <summary>
852: ///
853: /// </summary>
854: public static void UpdateForADateTimeRangeWithOutputDataTable(DataTable dataTable, DateTime dateTime, out string result)
855: {
856: // below: the SQL statement should be within the dataTable.TableName variable
857: int number, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
858: int serviceRequestId;
859: string sql, r, customerAddress;
860: ArrayList newServiceRequestIdArryList;
861: DateTime startDateTime, endDateTime;
862: Match match;
863: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
864: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
865: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
866: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
867:
868: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
869: result = r = "";
870:
871: startDateTime = DateTime.MinValue;
872:
873: if (dataTable != null)
874: {
875: sql = dataTable.TableName;
876:
877: // 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;
878: // 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;
879:
880: match = Regex.Match(sql, @".+'(\d{2})\/(\d{2})\/(\d{4})'.+'(\d{2})\/(\d{2})\/(\d{4})'.+", RegexOptions.Singleline);
881: // 1 2 3 4 5 6
882:
883: if (match.Success)
884: {
885: using (var db = new Ia.Ngn.Cl.Model.Ngn())
886: {
887: readItemCount = dataTable.Rows.Count;
888:
889: //if (dataTable.Rows.Count > 0)
890: //{
891: startDateTime = DateTime.Parse(match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value);
892: endDateTime = DateTime.Parse(match.Groups[6].Value + "-" + match.Groups[5].Value + "-" + match.Groups[4].Value);
893:
894: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ListWithinDateTimeRange(startDateTime, endDateTime);
895: existingItemCount = serviceRequestList.Count;
896:
897: newServiceRequestIdArryList = new ArrayList(dataTable.Rows.Count + 1);
898:
899: foreach (DataRow dataRow in dataTable.Rows)
900: {
901: number = int.Parse(dataRow["SRV_NO"].ToString());
902:
903: if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
904: {
905: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
906:
907: customerAddress = dataRow["ADDRESS"].ToString();
908: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.StatisticalServiceAddress(number.ToString(), customerAddress, out string level);
909:
910: newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
911: {
912: Id = serviceRequestId,
913: Number = number,
914:
915: CustomerAddress = customerAddress,
916: AreaId = serviceAddress.AreaId,
917:
918: CustomerCategoryId = int.Parse(dataRow["CUST_CAT_ID"].ToString()),
919: CustomerId = int.Parse(dataRow["ACCOUNT_NO"].ToString()),
920: CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
921: RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
922: Serial = int.Parse(dataRow["SRV_SER_NO"].ToString()),
923: ServiceCategoryId = int.Parse(dataRow["SRV_CAT_ID"].ToString()),
924: ServiceId = int.Parse(dataRow["SRV_ID"].ToString()),
925: Balance = double.Parse(dataRow["BALANCE"].ToString()),
926: Status = int.Parse(dataRow["STATUS"].ToString())
927: };
928:
929: serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
930:
931: if (serviceRequest == null)
932: {
933: newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
934:
935: db.ServiceRequests.Add(newServiceRequest);
936:
937: insertedItemCount++;
938: }
939: else
940: {
941: // below: copy values from newServiceRequest to serviceRequest
942:
943: if (serviceRequest.Update(newServiceRequest))
944: {
945: db.ServiceRequests.Attach(serviceRequest);
946: db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
947:
948: updatedItemCount++;
949: }
950: }
951:
952: newServiceRequestIdArryList.Add(serviceRequestId);
953: }
954: else
955: {
956: r += "Number: " + number + " is not within allowed domain list, ";
957: }
958: }
959:
960: // below: this function will remove values that were not present in the reading
961: if (serviceRequestList.Count > 0)
962: {
963: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
964: {
965: if (!newServiceRequestIdArryList.Contains(sr.Id))
966: {
967: serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
968:
969: db.ServiceRequests.Remove(serviceRequest);
970:
971: // below: we will also remove SRT records referensing this SR
972: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
973:
974: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
975:
976: deletedItemCount++;
977: }
978: }
979: }
980:
981: db.SaveChanges();
982:
983: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
984: //}
985: //else
986: //{
987: // result = "(" + readItemCount + "/?/?) ";
988: //}
989: }
990: }
991: else
992: {
993: result = "(?/?/?: SQL in TableName is unmatched) ";
994: }
995: }
996: else
997: {
998: result = "(dataTable == null/?/?) ";
999: }
1000: }
1001:
1002: ////////////////////////////////////////////////////////////////////////////
1003:
1004: /// <summary>
1005: ///
1006: /// </summary>
1007: public static void UpdateForServiceRequestWithOutputDataTableAndIdRange(DataTable dataTable, Tuple<int, int> startEndRange, out bool isUpdated, out string result)
1008: {
1009: // below: the SQL statement should be within the dataTable.TableName variable
1010: int serviceRequestId, start, end, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
1011: string sql, r, customerAddress;
1012: ArrayList newServiceRequestIdArrayList;
1013: Match match;
1014: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
1015: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
1016: List<int> numbersNotWithinAllowedDomainList;
1017: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1018: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
1019:
1020: isUpdated = false;
1021: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
1022: result = r = "";
1023: numbersNotWithinAllowedDomainList = new List<int>();
1024:
1025: if (dataTable != null)
1026: {
1027: sql = dataTable.TableName;
1028:
1029: // select * from SRV_REQ_FIPER where SRV_REQ_ID >= 110000 and SRV_REQ_ID <= 321203 order by REQ_DATE asc, SRV_REQ_ID asc
1030: match = Regex.Match(sql, @"SRV_REQ_ID >= (\d+) and SRV_REQ_ID <= (\d+) ", RegexOptions.Singleline);
1031: // 1 2
1032:
1033: if (match.Success)
1034: {
1035: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1036: {
1037: readItemCount = dataTable.Rows.Count;
1038:
1039: start = int.Parse(match.Groups[1].Value);
1040: end = int.Parse(match.Groups[2].Value);
1041:
1042: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ListWithinIdRange(start, end);
1043: existingItemCount = serviceRequestList.Count;
1044:
1045: newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
1046:
1047: foreach (DataRow dataRow in dataTable.Rows)
1048: {
1049: if (int.TryParse(dataRow["SRV_NO"].ToString(), out int number))
1050: {
1051: if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
1052: {
1053: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
1054: customerAddress = dataRow["ADDRESS"].ToString();
1055:
1056: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.StatisticalServiceAddress(number.ToString(), customerAddress, out string level);
1057:
1058: newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
1059: {
1060: Id = serviceRequestId,
1061: Number = number,
1062:
1063: CustomerAddress = customerAddress,
1064: AreaId = serviceAddress.AreaId,
1065:
1066: CustomerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0,
1067: CustomerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0,
1068: CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
1069: RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
1070: Serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0,
1071: ServiceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0,
1072: ServiceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0,
1073: Balance = double.Parse(dataRow["BALANCE"].ToString()),
1074:
1075: Status = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0
1076: };
1077:
1078: serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
1079:
1080: if (serviceRequest == null)
1081: {
1082: newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
1083:
1084: db.ServiceRequests.Add(newServiceRequest);
1085:
1086: insertedItemCount++;
1087: }
1088: else
1089: {
1090: // below: copy values from newServiceRequest to serviceRequest
1091:
1092: if (serviceRequest.UpdateSkipServiceRequestService(newServiceRequest))
1093: {
1094: db.ServiceRequests.Attach(serviceRequest);
1095: db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
1096:
1097: updatedItemCount++;
1098: }
1099: }
1100:
1101: newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
1102: }
1103: else
1104: {
1105: numbersNotWithinAllowedDomainList.Add(number);
1106: }
1107: }
1108: else
1109: {
1110: //
1111: }
1112: }
1113:
1114: /*
1115: if (numbersNotWithinAllowedDomainList.Count > 0)
1116: {
1117: r = "Numbers not within allowed domain list: ";
1118:
1119: foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
1120:
1121: r = r.Trim(',');
1122: }
1123: */
1124:
1125: // below: this function will remove values that were not present in the reading
1126: if (serviceRequestList.Count > 0)
1127: {
1128: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
1129: {
1130: if (!newServiceRequestIdArrayList.Contains(sr.Id))
1131: {
1132: serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
1133:
1134: db.ServiceRequests.Remove(serviceRequest);
1135:
1136: // below: we will also remove SRT records referensing this SR
1137: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
1138:
1139: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
1140:
1141: deletedItemCount++;
1142: }
1143: }
1144: }
1145:
1146: db.SaveChanges();
1147:
1148: if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
1149: else isUpdated = false;
1150:
1151: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
1152: }
1153: }
1154: else
1155: {
1156: result = "(?/?/?: SQL in TableName is unmatched) ";
1157: }
1158: }
1159: else
1160: {
1161: result = "(dataTable == null/?/?) ";
1162: }
1163: }
1164:
1165: ////////////////////////////////////////////////////////////////////////////
1166:
1167: /// <summary>
1168: ///
1169: /// </summary>
1170: public static void UpdateForServiceRequestWithOutputDataTableAndService(DataTable dataTable, string service, out bool isUpdated, out string result)
1171: {
1172: int serviceRequestId, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
1173: string sql, sqlService, r, customerAddress;
1174: ArrayList newServiceRequestIdArrayList;
1175: Match match;
1176: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
1177: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
1178: List<int> numbersNotWithinAllowedDomainList;
1179: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1180: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
1181:
1182: isUpdated = false;
1183: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
1184: result = r = "";
1185: numbersNotWithinAllowedDomainList = new List<int>();
1186:
1187: if (dataTable != null)
1188: {
1189: // below: the SQL statement should be within the dataTable.TableName variable
1190: sql = dataTable.TableName;
1191:
1192: // select * from SRV_REQ_FIPER where SRV_NO = 23632222 order by SRV_REQ_ID asc
1193: match = Regex.Match(sql, @"SRV_NO = (\d+) order by SRV_REQ_ID asc", RegexOptions.Singleline);
1194:
1195: if (match.Success)
1196: {
1197: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1198: {
1199: readItemCount = dataTable.Rows.Count;
1200:
1201: sqlService = match.Groups[1].Value;
1202:
1203: if (service == sqlService)
1204: {
1205: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(service);
1206: existingItemCount = serviceRequestList.Count;
1207:
1208: newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
1209:
1210: foreach (DataRow dataRow in dataTable.Rows)
1211: {
1212: if (int.TryParse(dataRow["SRV_NO"].ToString(), out int number))
1213: {
1214: if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
1215: {
1216: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
1217: customerAddress = dataRow["ADDRESS"].ToString();
1218:
1219: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.StatisticalServiceAddress(number.ToString(), customerAddress, out string level);
1220:
1221: newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
1222: {
1223: Id = serviceRequestId,
1224: Number = number,
1225: CustomerAddress = customerAddress,
1226: AreaId = serviceAddress.AreaId,
1227: CustomerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0,
1228: CustomerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0,
1229: CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
1230: RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
1231: Serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0,
1232: ServiceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0,
1233: ServiceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0,
1234: Balance = double.Parse(dataRow["BALANCE"].ToString()),
1235: Status = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0
1236: };
1237:
1238: serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
1239:
1240: if (serviceRequest == null)
1241: {
1242: newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
1243:
1244: db.ServiceRequests.Add(newServiceRequest);
1245:
1246: insertedItemCount++;
1247: }
1248: else
1249: {
1250: // below: copy values from newServiceRequest to serviceRequest
1251:
1252: if (serviceRequest.UpdateSkipServiceRequestService(newServiceRequest))
1253: {
1254: db.ServiceRequests.Attach(serviceRequest);
1255: db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
1256:
1257: updatedItemCount++;
1258: }
1259: }
1260:
1261: newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
1262: }
1263: else
1264: {
1265: numbersNotWithinAllowedDomainList.Add(number);
1266: }
1267: }
1268: else
1269: {
1270: //
1271: }
1272: }
1273:
1274: /*
1275: if (numbersNotWithinAllowedDomainList.Count > 0)
1276: {
1277: r = "Numbers not within allowed domain list: ";
1278:
1279: foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
1280:
1281: r = r.Trim(',');
1282: }
1283: */
1284:
1285: // below: this function will remove values that were not present in the reading
1286: if (serviceRequestList.Count > 0)
1287: {
1288: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
1289: {
1290: if (!newServiceRequestIdArrayList.Contains(sr.Id))
1291: {
1292: serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
1293:
1294: db.ServiceRequests.Remove(serviceRequest);
1295:
1296: // below: we will also remove SRT records referencing this SR
1297: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
1298:
1299: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
1300:
1301: deletedItemCount++;
1302: }
1303: }
1304: }
1305:
1306: db.SaveChanges();
1307:
1308: if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
1309: else isUpdated = false;
1310:
1311: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
1312: }
1313: else
1314: {
1315: throw new ArgumentException(@"UpdateForServiceRequestWithOutputDataTableAndService(): service != sqlService, service: " + service + ", sqlService: " + sqlService);
1316: }
1317: }
1318: }
1319: else
1320: {
1321: result = "(?/?/?: SQL in TableName is unmatched) ";
1322: }
1323: }
1324: else
1325: {
1326: result = "(dataTable == null/?/?) ";
1327: }
1328: }
1329:
1330: ////////////////////////////////////////////////////////////////////////////
1331:
1332: /// <summary>
1333: ///
1334: /// </summary>
1335: public static Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> ServiceToServiceRequestDictionary(List<int> domainList)
1336: {
1337: string key;
1338: List<string> stringDomainList;
1339: List<Ia.Ngn.Cl.Model.ServiceRequest> list;
1340: Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> dictionary;
1341:
1342: stringDomainList = new List<string>();
1343:
1344: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1345: {
1346: if (domainList != null)
1347: {
1348: foreach (int i in domainList) stringDomainList.Add(i.ToString());
1349:
1350: 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();
1351:
1352: dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>(list.Count);
1353:
1354: foreach (var sr in list)
1355: {
1356: key = sr.Number.ToString();
1357:
1358: if (dictionary.ContainsKey(key))
1359: {
1360: dictionary[key] = sr;
1361: }
1362: else dictionary[key] = sr;
1363: }
1364: }
1365: else
1366: {
1367: dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>();
1368: }
1369: }
1370:
1371: return dictionary;
1372: }
1373:
1374: ////////////////////////////////////////////////////////////////////////////
1375:
1376: /// <summary>
1377: ///
1378: /// </summary>
1379: public static Hashtable NumberToCustomerAddressHashtable(List<int> domainList)
1380: {
1381: Hashtable ht;
1382: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1383:
1384: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1385: {
1386: if (domainList != null)
1387: {
1388: serviceRequestList = (from sr in db.ServiceRequests where domainList.Contains(sr.Number / 10000) || domainList.Contains(sr.Number / 1000) select sr).ToList();
1389:
1390: ht = new Hashtable(serviceRequestList.Count);
1391:
1392: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList.OrderBy(u => u.Id)) ht[sr.Number.ToString()] = sr.CustomerAddress;
1393: }
1394: else
1395: {
1396: ht = new Hashtable();
1397: }
1398: }
1399:
1400: return ht;
1401: }
1402:
1403: ////////////////////////////////////////////////////////////////////////////
1404:
1405: /// <summary>
1406: /// Return the latest RequestDateTime for number
1407: /// </summary>
1408: public static DateTime LatestRequestDateTime(int number)
1409: {
1410: DateTime dateTime;
1411: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
1412: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1413:
1414: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1415: {
1416: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(number);
1417:
1418: if (serviceRequestList.Count > 0)
1419: {
1420: serviceRequest = serviceRequestList.OrderByDescending(u => u.Id).FirstOrDefault();
1421:
1422: dateTime = serviceRequest.RequestDateTime;
1423: }
1424: else
1425: {
1426: dateTime = DateTime.MinValue;
1427: }
1428: }
1429:
1430: return dateTime;
1431: }
1432:
1433: ////////////////////////////////////////////////////////////////////////////
1434:
1435: /// <summary>
1436: ///
1437: /// </summary>
1438: public static List<Ia.Ngn.Cl.Model.ServiceRequest> LastN(int numberOfServiceRequests)
1439: {
1440: // below:
1441: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1442:
1443: serviceRequestList = null;
1444:
1445: try
1446: {
1447: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1448: {
1449: serviceRequestList = (from sr in db.ServiceRequests orderby sr.RequestDateTime descending select sr).Take(numberOfServiceRequests).ToList();
1450: }
1451: }
1452: catch (Exception)
1453: {
1454: //resultLabel.Text = "Error during retrieval of data for \"" + ip + "\": " + ex.Message + ". ";
1455: //resultLabel.CssClass = "error";
1456: }
1457:
1458: return serviceRequestList;
1459: }
1460:
1461: ////////////////////////////////////////////////////////////////////////////
1462:
1463: /// <summary>
1464: ///
1465: /// </summary>
1466: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ForRequestDate(DateTime requestDate)
1467: {
1468: // below:
1469: DateTime nextDate;
1470: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1471:
1472: serviceRequestList = null;
1473:
1474: // below: 00:00 time values
1475: requestDate = requestDate.Date;
1476:
1477: nextDate = requestDate.AddDays(1);
1478:
1479: try
1480: {
1481: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1482: {
1483: serviceRequestList = (from sr in db.ServiceRequests where sr.RequestDateTime >= requestDate && sr.RequestDateTime < nextDate orderby sr.RequestDateTime descending select sr).ToList();
1484: }
1485: }
1486: catch (Exception)
1487: {
1488: //resultLabel.Text = "Error during retrieval of data for \"" + ip + "\": " + ex.Message + ". ";
1489: //resultLabel.CssClass = "error";
1490: }
1491:
1492: return serviceRequestList;
1493: }
1494:
1495: ////////////////////////////////////////////////////////////////////////////
1496: ////////////////////////////////////////////////////////////////////////////
1497:
1498: /// <summary>
1499: ///
1500: /// </summary>
1501: public static string ToSimpleTextString(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest)
1502: {
1503: StringBuilder sb;
1504:
1505: sb = new StringBuilder();
1506:
1507: // Id Number Serial Status RequestDateTime Service ServiceCategory CustomerId CustomerName CustomerCategory CustomerAddress Balance
1508: sb.AppendLine("Id: " + serviceRequest.Id);
1509: sb.AppendLine("Number: " + serviceRequest.Number + "/" + serviceRequest.Serial);
1510: //sb.AppendLine("Serial: " + serviceRequest.Serial);
1511: sb.AppendLine("Status: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.StatusSortedList[serviceRequest.Status].ToString());
1512: sb.AppendLine("RequestDateTime: " + serviceRequest.RequestDateTime.ToString("yyyy-MM-dd HH:mm"));
1513: sb.AppendLine("Service: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceSortedList[serviceRequest.ServiceId].ToString());
1514: //sb.AppendLine("ServiceCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceCategorySortedList[serviceRequest.ServiceCategoryId].ToString());
1515: //sb.AppendLine("CustomerId: " + serviceRequest.CustomerId);
1516: sb.AppendLine("CustomerName: " + serviceRequest.CustomerName);
1517: //sb.AppendLine("CustomerCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.CustomerCategorySortedList[serviceRequest.CustomerCategoryId].ToString());
1518:
1519: sb.AppendLine("CustomerAddress: " + serviceRequest.CustomerAddress);
1520: //sb.AppendLine("Balance: " + serviceRequest.Balance);
1521:
1522: return sb.ToString();
1523: }
1524:
1525: ////////////////////////////////////////////////////////////////////////////
1526:
1527: /// <summary>
1528: ///
1529: /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
1530: ///
1531: /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
1532: /// 2. Add "using System.Reflection".
1533: /// 3. See sample below.
1534: ///
1535: /// </summary>
1536:
1537: private static XDocument XDocument
1538: {
1539: get
1540: {
1541: if (xDocument == null)
1542: {
1543: Assembly _assembly;
1544: StreamReader streamReader;
1545:
1546: _assembly = Assembly.GetExecutingAssembly();
1547: streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.service-request.xml"));
1548:
1549: try
1550: {
1551: if (streamReader.Peek() != -1)
1552: {
1553: xDocument = System.Xml.Linq.XDocument.Load(streamReader);
1554: }
1555: }
1556: catch (Exception)
1557: {
1558: }
1559: finally
1560: {
1561: }
1562: }
1563:
1564: return xDocument;
1565: }
1566: }
1567:
1568: ////////////////////////////////////////////////////////////////////////////
1569: ////////////////////////////////////////////////////////////////////////////
1570: }
1571:
1572: ////////////////////////////////////////////////////////////////////////////
1573: ////////////////////////////////////////////////////////////////////////////
1574: }