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