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-2017 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 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 SortedList ServiceCategorySortedList
57: {
58: get
59: {
60: int id;
61:
62: if (serviceCategorySortedList == null)
63: {
64: serviceCategorySortedList = new SortedList(10);
65:
66: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("categoryList").Elements("category"))
67: {
68: id = int.Parse(x.Attribute("id").Value);
69:
70: serviceCategorySortedList[id] = x.Attribute("arabicName").Value;
71: }
72: }
73:
74: return serviceCategorySortedList;
75: }
76: }
77:
78: ////////////////////////////////////////////////////////////////////////////
79:
80: /// <summary>
81: ///
82: /// </summary>
83: public static SortedList ServiceSortedList
84: {
85: get
86: {
87: int id;
88:
89: if (serviceSortedList == null)
90: {
91: serviceSortedList = new SortedList(10);
92:
93: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
94: {
95: id = int.Parse(x.Attribute("id").Value);
96:
97: serviceSortedList[id] = x.Attribute("arabicName").Value;
98: }
99: }
100:
101: return serviceSortedList;
102: }
103: }
104:
105: ////////////////////////////////////////////////////////////////////////////
106:
107: /// <summary>
108: ///
109: /// </summary>
110: public static SortedList CustomerCategorySortedList
111: {
112: get
113: {
114: int id;
115:
116: if (customerCategorySortedList == null)
117: {
118: customerCategorySortedList = new SortedList(10);
119:
120: foreach (XElement x in XDocument.Element("serviceRequest").Elements("customer").Elements("categoryList").Elements("category"))
121: {
122: id = int.Parse(x.Attribute("id").Value);
123:
124: customerCategorySortedList[id] = x.Attribute("arabicName").Value;
125: }
126: }
127:
128: return customerCategorySortedList;
129: }
130: }
131:
132: ////////////////////////////////////////////////////////////////////////////
133:
134: /// <summary>
135: ///
136: /// </summary>
137: public static SortedList StatusSortedList
138: {
139: get
140: {
141: int id;
142:
143: if (statusSortedList == null)
144: {
145: statusSortedList = new SortedList(10);
146:
147: foreach (XElement x in XDocument.Element("serviceRequest").Elements("statusList").Elements("status"))
148: {
149: id = int.Parse(x.Attribute("id").Value);
150:
151: statusSortedList[id] = x.Attribute("arabicName").Value;
152: }
153: }
154:
155: return statusSortedList;
156: }
157: }
158:
159: ////////////////////////////////////////////////////////////////////////////
160:
161: /// <summary>
162: ///
163: /// </summary>
164: public static List<int> ServiceIdAllowedForProcessingList
165: {
166: get
167: {
168: int id;
169:
170: if (serviceIdAllowedForProcessingList == null)
171: {
172: serviceIdAllowedForProcessingList = new List<int>(100);
173:
174: foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
175: {
176: if (x.HasAttributes && x.Attribute("allowProcessing") != null)
177: {
178: if (x.Attribute("allowProcessing").Value == "true")
179: {
180: id = int.Parse(x.Attribute("id").Value);
181: serviceIdAllowedForProcessingList.Add(id);
182: }
183: }
184: }
185: }
186:
187: return serviceIdAllowedForProcessingList;
188: }
189: }
190:
191: ////////////////////////////////////////////////////////////////////////////
192:
193: /// <summary>
194: ///
195: /// </summary>
196: public static string OracleSqlCommandForServiceRequestIdRange(Tuple<int, int> startEndRange)
197: {
198: return OracleSqlCommandForServiceRequestIdRange(startEndRange.Item1, startEndRange.Item2);
199: }
200:
201: ////////////////////////////////////////////////////////////////////////////
202:
203: /// <summary>
204: ///
205: /// </summary>
206: private static string OracleSqlCommandForServiceRequestIdRange(int start, int end)
207: {
208: string sql;
209:
210: // select * from SRV_REQ_FIPER where SRV_REQ_ID >= 110000 and SRV_REQ_ID <= 321203 order by REQ_DATE asc, SRV_REQ_ID asc
211: sql = @"select * from SRV_REQ_FIPER where SRV_REQ_ID >= " + start + " and SRV_REQ_ID <= " + end + " order by SRV_REQ_ID asc";
212:
213: return sql;
214: }
215:
216: ////////////////////////////////////////////////////////////////////////////
217:
218: /// <summary>
219: ///
220: /// </summary>
221: private static string OracleSqlCommandForGivenDateTime(DateTime dateTime)
222: {
223: string sql;
224:
225: //sql = @"select * from SRV_REQ_FIPER LEFT OUTER JOIN SRV_REQ_FIPER_TECH ON SRV_REQ_FIPER_TECH.SRV_REQ_ID = SRV_REQ_FIPER.SRV_REQ_ID where REQ_DATE >= '" + dateTime.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture) + "' and REQ_DATE < '" + dateTime.AddDays(1).ToString("dd/MM/yyyy", CultureInfo.InvariantCulture) + "' order by REQ_DATE asc, SRV_REQ_ID asc";
226: sql = @"select * from SRV_REQ_FIPER where REQ_DATE >= '" + dateTime.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture) + "' and REQ_DATE < '" + dateTime.AddDays(1).ToString("dd/MM/yyyy", CultureInfo.InvariantCulture) + "' order by SRV_REQ_ID asc";
227:
228: return sql;
229: }
230:
231: ////////////////////////////////////////////////////////////////////////////
232:
233: /// <summary>
234: ///
235: /// </summary>
236: private static string OracleSqlCommandForSingleRandomDateTimeWithinTheLastNDays(int rangeOfPastDays, out DateTime selectedDate)
237: {
238: // below:
239: int i;
240: string sql;
241:
242: i = Ia.Cl.Model.Default.Random(rangeOfPastDays);
243:
244: selectedDate = DateTime.UtcNow.AddDays(-i);
245:
246: sql = OracleSqlCommandForGivenDateTime(selectedDate);
247:
248: return sql;
249: }
250:
251: ////////////////////////////////////////////////////////////////////////////
252:
253: /// <summary>
254: ///
255: /// </summary>
256: private static string OracleSqlCommandForSingleDateTimeUsingDayIndexBetweenNowAndEarliestDate(ref int index, out DateTime selectedDate)
257: {
258: // below: select a date between now and the earliest date using an variable index value
259: string sql;
260: DateTime now;
261:
262: now = DateTime.UtcNow.AddHours(3);
263:
264: // below: check that inIndex is an index of a day between earliest day and now, and reset it to 0 if it is bigger than now
265: if (DateTime.Compare(Ia.Ngn.Cl.Model.Business.Administration.EarliestServiceRequestDate.AddDays(index), now) < 0)
266: {
267: // below: within range
268: }
269: else index = 0;
270:
271: selectedDate = Ia.Ngn.Cl.Model.Business.Administration.EarliestServiceRequestDate.AddDays(index++);
272:
273: sql = OracleSqlCommandForGivenDateTime(selectedDate);
274:
275: return sql;
276: }
277:
278: ////////////////////////////////////////////////////////////////////////////
279:
280: /// <summary>
281: /// Return the Tuple range but with a reset of list so to get edge records
282: /// </summary>
283: public static Tuple<int, int> ServiceRequestIdStartEndRangeManagerWithReset(ref int index, out string result)
284: {
285: serviceRequestIdStartEndRangeBufferedListIndex = 0;
286:
287: return ServiceRequestIdStartEndRangeManager(ref index, out result);
288: }
289:
290: ////////////////////////////////////////////////////////////////////////////
291:
292: /// <summary>
293: ///
294: /// </summary>
295: public static Tuple<int, int> ServiceRequestIdStartEndRangeManager(ref int index, out string result)
296: {
297: int count, edgeBufferRange, lastestRangeList;
298: Tuple<int, int> tuple;
299: List<int> serviceRequestIdList;
300:
301: count = 50;
302: edgeBufferRange = 5000;
303: lastestRangeList = 21;
304:
305: if (serviceRequestIdStartEndRangeBufferedTupleList == null || serviceRequestIdStartEndRangeBufferedListIndex == 0)
306: {
307: serviceRequestIdStartEndRangeBufferedListIndex = index;
308:
309: using (var db = new Ia.Ngn.Cl.Model.Ngn())
310: {
311: serviceRequestIdList = (from sr in db.ServiceRequests orderby sr.Id ascending select sr.Id).ToList();
312: }
313:
314: serviceRequestIdStartEndRangeBufferedTupleList = Ia.Cl.Model.Default.OptimizedStartEndRangeBufferedList(serviceRequestIdList, count, edgeBufferRange);
315:
316: serviceRequestIdStartEndRangeBufferedListIndex = 0;
317: }
318:
319: if (serviceRequestIdStartEndRangeBufferedTupleList.Count > 0)
320: {
321: if (Ia.Ngn.Cl.Model.Business.Administration.NowIsOfficialWorkingTime)
322: {
323: // flip between last and latest range to favor reading fresh work orders quickly
324: if (Ia.Cl.Model.Default.RandomBool)
325: {
326: serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - Ia.Cl.Model.Default.Random(lastestRangeList) - 1;
327: serviceRequestIdLatestStartEndRangeBufferedListIndex = (serviceRequestIdLatestStartEndRangeBufferedListIndex >= 0) ? serviceRequestIdLatestStartEndRangeBufferedListIndex : 0;
328: }
329: else serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - 1;
330:
331: tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdLatestStartEndRangeBufferedListIndex];
332:
333: if (serviceRequestIdLatestStartEndRangeBufferedListIndex == serviceRequestIdStartEndRangeBufferedTupleList.Count - 1)
334: {
335: tuple = new Tuple<int, int>(tuple.Item1, tuple.Item2 + edgeBufferRange);
336: }
337:
338: result = "(latest: " + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdLatestStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
339: }
340: else
341: {
342: tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdStartEndRangeBufferedListIndex];
343:
344: result = "(historic: " + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
345:
346: serviceRequestIdStartEndRangeBufferedListIndex = Ia.Cl.Model.Default.IncrementListIndexOrRestart(serviceRequestIdStartEndRangeBufferedTupleList, serviceRequestIdStartEndRangeBufferedListIndex);
347: }
348: }
349: else
350: {
351: result = "(0-0 0/0)";
352:
353: tuple = null;
354: }
355:
356: index = serviceRequestIdStartEndRangeBufferedListIndex;
357:
358: return tuple;
359: }
360:
361: /*
362: ////////////////////////////////////////////////////////////////////////////
363:
364: /// <summary>
365: ///
366: /// </summary>
367: private static ArrayList SqlCommandsForSingleDaysArrayList
368: {
369: get
370: {
371: string sql;
372: DateTime startDateTime, endDateTime;
373: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
374:
375: if (sqlCommandsForSingleDaysArrayList == null || sqlCommandsForSingleDaysArrayList.Count == 0)
376: {
377: // below: start from the date with the oldest updated time
378: using (var db = new Ia.Ngn.Cl.Model.Ngn())
379: {
380: serviceRequest = (from q in db.ServiceRequests orderby q.Updated select q).Take(1).SingleOrDefault();
381:
382: if (serviceRequest != null)
383: {
384: startDateTime = serviceRequest.RequestDateTime;
385:
386: endDateTime = DateTime.UtcNow.AddHours(3);
387:
388: // below: the date format on each database to ensuer they are the same
389: //sql = @"select * from nls_session_parameters";
390:
391: // sql = @"SELECT * FROM SRV_REQ_FIPER WHERE ROWNUM = 1"; // @"DESCRIBE SRV_REQ_FIPER;";
392:
393: sqlCommandsForSingleDaysArrayList = new ArrayList();
394:
395: for (DateTime dateTime = startDateTime; dateTime < endDateTime; dateTime = dateTime.AddDays(1))
396: {
397: sql = OracleSqlCommandForGivenDateTime(dateTime);
398:
399: sqlCommandsForSingleDaysArrayList.Add(sql);
400: }
401: }
402: }
403: }
404:
405: return sqlCommandsForSingleDaysArrayList;
406: }
407: }*/
408:
409: ////////////////////////////////////////////////////////////////////////////
410:
411: /// <summary>
412: ///
413: /// </summary>
414: public static string AlterSessionOfCustomerDepartmentOracleDatabase
415: {
416: get
417: {
418: return @"alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS'";
419: }
420: }
421:
422: ////////////////////////////////////////////////////////////////////////////
423:
424: /// <summary>
425: ///
426: /// </summary>
427: public static void UpdateWithServiceList(List<string> serviceList, List<Ia.Ngn.Cl.Model.ServiceRequest> newServiceRequestList, out string result)
428: {
429: int readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount, serviceRequestId;
430: string serviceRequestServiceId;
431: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
432:
433: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
434: result = "";
435:
436: readItemCount = newServiceRequestList.Count;
437:
438: using (var db = new Ia.Ngn.Cl.Model.Ngn())
439: {
440: foreach (Ia.Ngn.Cl.Model.ServiceRequest newServiceRequest in newServiceRequestList)
441: {
442: serviceRequestId = newServiceRequest.Id;
443:
444: serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequestId select sr).SingleOrDefault();
445:
446: existingItemCount = (serviceRequest != null) ? 1 : 0;
447:
448: if (newServiceRequest.ServiceRequestService != null)
449: {
450: serviceRequestServiceId = newServiceRequest.ServiceRequestService.Id;
451:
452: newServiceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestServiceId select srs).SingleOrDefault();
453: }
454:
455: if (serviceRequest.Update(newServiceRequest))
456: {
457: db.ServiceRequests.Attach(serviceRequest);
458: db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
459:
460: updatedItemCount++;
461: }
462: }
463:
464: db.SaveChanges();
465:
466: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") ";
467: }
468: }
469:
470: ////////////////////////////////////////////////////////////////////////////
471:
472: /// <summary>
473: ///
474: /// </summary>
475: public static bool UpdateServiceRequestService(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, Ia.Ngn.Cl.Model.ServiceRequestService serviceRequestService, out string result)
476: {
477: bool b;
478:
479: using (var db = new Ia.Ngn.Cl.Model.Ngn())
480: {
481: serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequest.Id select sr).SingleOrDefault();
482:
483: if (serviceRequest.ServiceRequestService != serviceRequestService)
484: {
485: serviceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestService.Id select srs).SingleOrDefault();
486:
487: db.ServiceRequests.Attach(serviceRequest);
488: db.Entry(serviceRequest).Property(x => x.ServiceRequestService).IsModified = true;
489:
490: db.SaveChanges();
491:
492: result = "Success: ServiceRequests ServiceRequestService updated. ";
493: b = true;
494: }
495: else
496: {
497: result = "Warning: ServiceRequests ServiceRequestService value was not updated because its the same. ";
498:
499: b = false;
500: }
501: }
502:
503: return b;
504: }
505:
506: ////////////////////////////////////////////////////////////////////////////
507:
508: /// <summary>
509: ///
510: /// </summary>
511: public static void UpdateForADateTimeRangeWithOutputDataTable(DataTable dataTable, DateTime dateTime, out string result)
512: {
513: // below: the SQL statement should be within the dataTable.TableName variable
514: int number, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
515: int serviceRequestId;
516: string sql, r, customerAddress;
517: ArrayList newServiceRequestIdArryList;
518: DateTime startDateTime, endDateTime;
519: Match match;
520: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
521: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
522: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
523: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
524:
525: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
526: result = r = "";
527:
528: startDateTime = DateTime.MinValue;
529:
530: if (dataTable != null)
531: {
532: sql = dataTable.TableName;
533:
534: // 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;
535: // 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;
536:
537: match = Regex.Match(sql, @".+'(\d{2})\/(\d{2})\/(\d{4})'.+'(\d{2})\/(\d{2})\/(\d{4})'.+", RegexOptions.Singleline);
538: // 1 2 3 4 5 6
539:
540: if (match.Success)
541: {
542: using (var db = new Ia.Ngn.Cl.Model.Ngn())
543: {
544: readItemCount = dataTable.Rows.Count;
545:
546: //if (dataTable.Rows.Count > 0)
547: //{
548: startDateTime = DateTime.Parse(match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value);
549: endDateTime = DateTime.Parse(match.Groups[6].Value + "-" + match.Groups[5].Value + "-" + match.Groups[4].Value);
550:
551: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ReadListWithinDateTimeRange(startDateTime, endDateTime);
552: existingItemCount = serviceRequestList.Count;
553:
554: newServiceRequestIdArryList = new ArrayList(dataTable.Rows.Count + 1);
555:
556: foreach (DataRow dataRow in dataTable.Rows)
557: {
558: number = int.Parse(dataRow["SRV_NO"].ToString());
559:
560: if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
561: {
562: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
563:
564: customerAddress = dataRow["ADDRESS"].ToString();
565: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.StatisticalServiceAddress(number.ToString(), customerAddress, out string level);
566:
567: newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
568: {
569: Id = serviceRequestId,
570: Number = number,
571:
572: CustomerAddress = customerAddress,
573: AreaId = serviceAddress.AreaId,
574:
575: CustomerCategoryId = int.Parse(dataRow["CUST_CAT_ID"].ToString()),
576: CustomerId = int.Parse(dataRow["ACCOUNT_NO"].ToString()),
577: CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
578: RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
579: Serial = int.Parse(dataRow["SRV_SER_NO"].ToString()),
580: ServiceCategoryId = int.Parse(dataRow["SRV_CAT_ID"].ToString()),
581: ServiceId = int.Parse(dataRow["SRV_ID"].ToString()),
582: Balance = double.Parse(dataRow["BALANCE"].ToString()),
583: Status = int.Parse(dataRow["STATUS"].ToString())
584: };
585:
586: serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
587:
588: if (serviceRequest == null)
589: {
590: newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
591:
592: db.ServiceRequests.Add(newServiceRequest);
593:
594: insertedItemCount++;
595: }
596: else
597: {
598: // below: copy values from newServiceRequest to serviceRequest
599:
600: if (serviceRequest.Update(newServiceRequest))
601: {
602: db.ServiceRequests.Attach(serviceRequest);
603: db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
604:
605: updatedItemCount++;
606: }
607: }
608:
609: newServiceRequestIdArryList.Add(serviceRequestId);
610: }
611: else
612: {
613: r += "Number: " + number + " is not within allowed domain list, ";
614: }
615: }
616:
617: // below: this function will remove values that were not present in the reading
618: if (serviceRequestList.Count > 0)
619: {
620: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
621: {
622: if (!newServiceRequestIdArryList.Contains(sr.Id))
623: {
624: serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
625:
626: db.ServiceRequests.Remove(serviceRequest);
627:
628: // below: we will also remove SRT records referensing this SR
629: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
630:
631: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
632:
633: deletedItemCount++;
634: }
635: }
636: }
637:
638: db.SaveChanges();
639:
640: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
641: //}
642: //else
643: //{
644: // result = "(" + readItemCount + "/?/?) ";
645: //}
646: }
647: }
648: else
649: {
650: result = "(?/?/?: SQL in TableName is unmatched) ";
651: }
652: }
653: else
654: {
655: result = "(dataTable == null/?/?) ";
656: }
657: }
658:
659: ////////////////////////////////////////////////////////////////////////////
660:
661: /// <summary>
662: ///
663: /// </summary>
664: public static void UpdateForServiceRequetIdRangeWithOutputDataTable(DataTable dataTable, Tuple<int, int> startEndRange, out bool isUpdated, out string result)
665: {
666: // below: the SQL statement should be within the dataTable.TableName variable
667: int serviceRequestId, start, end, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
668: string sql, r, customerAddress;
669: ArrayList newServiceRequestIdArrayList;
670: Match match;
671: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
672: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
673: List<int> numbersNotWithinAllowedDomainList;
674: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
675: List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
676:
677: isUpdated = false;
678: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
679: result = r = "";
680: numbersNotWithinAllowedDomainList = new List<int>();
681:
682: if (dataTable != null)
683: {
684: sql = dataTable.TableName;
685:
686: // select * from SRV_REQ_FIPER where SRV_REQ_ID >= 110000 and SRV_REQ_ID <= 321203 order by REQ_DATE asc, SRV_REQ_ID asc
687: match = Regex.Match(sql, @"SRV_REQ_ID >= (\d+) and SRV_REQ_ID <= (\d+) ", RegexOptions.Singleline);
688: // 1 2
689:
690: if (match.Success)
691: {
692: using (var db = new Ia.Ngn.Cl.Model.Ngn())
693: {
694: readItemCount = dataTable.Rows.Count;
695:
696: start = int.Parse(match.Groups[1].Value);
697: end = int.Parse(match.Groups[2].Value);
698:
699: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ReadListWithinIdRange(start, end);
700: existingItemCount = serviceRequestList.Count;
701:
702: newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
703:
704: foreach (DataRow dataRow in dataTable.Rows)
705: {
706: if (int.TryParse(dataRow["SRV_NO"].ToString(), out int number))
707: {
708: if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
709: {
710: serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
711: customerAddress = dataRow["ADDRESS"].ToString();
712:
713: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.StatisticalServiceAddress(number.ToString(), customerAddress, out string level);
714:
715: newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
716: {
717: Id = serviceRequestId,
718: Number = number,
719:
720: CustomerAddress = customerAddress,
721: AreaId = serviceAddress.AreaId,
722:
723: CustomerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0,
724: CustomerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0,
725: CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
726: RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
727: Serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0,
728: ServiceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0,
729: ServiceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0,
730: Balance = double.Parse(dataRow["BALANCE"].ToString()),
731:
732: Status = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0
733: };
734:
735: serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
736:
737: if (serviceRequest == null)
738: {
739: newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
740:
741: db.ServiceRequests.Add(newServiceRequest);
742:
743: insertedItemCount++;
744: }
745: else
746: {
747: // below: copy values from newServiceRequest to serviceRequest
748:
749: if (serviceRequest.UpdateSkipServiceRequestService(newServiceRequest))
750: {
751: db.ServiceRequests.Attach(serviceRequest);
752: db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
753:
754: updatedItemCount++;
755: }
756: }
757:
758: newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
759: }
760: else
761: {
762: numbersNotWithinAllowedDomainList.Add(number);
763: }
764: }
765: else
766: {
767: //
768: }
769: }
770:
771: /*
772: if (numbersNotWithinAllowedDomainList.Count > 0)
773: {
774: r = "Numbers not within allowed domain list: ";
775:
776: foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
777:
778: r = r.Trim(',');
779: }
780: */
781:
782: // below: this function will remove values that were not present in the reading
783: if (serviceRequestList.Count > 0)
784: {
785: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
786: {
787: if (!newServiceRequestIdArrayList.Contains(sr.Id))
788: {
789: serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
790:
791: db.ServiceRequests.Remove(serviceRequest);
792:
793: // below: we will also remove SRT records referensing this SR
794: serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
795:
796: foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
797:
798: deletedItemCount++;
799: }
800: }
801: }
802:
803: db.SaveChanges();
804:
805: if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
806: else isUpdated = false;
807:
808: result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
809: }
810: }
811: else
812: {
813: result = "(?/?/?: SQL in TableName is unmatched) ";
814: }
815: }
816: else
817: {
818: result = "(dataTable == null/?/?) ";
819: }
820: }
821:
822: ////////////////////////////////////////////////////////////////////////////
823:
824: /// <summary>
825: /// Return list of service requests
826: /// </summary>
827: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List()
828: {
829: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
830:
831: using (var db = new Ia.Ngn.Cl.Model.Ngn())
832: {
833: serviceRequestList = (from sr in db.ServiceRequests select sr).ToList();
834: }
835:
836: return serviceRequestList;
837: }
838:
839: ////////////////////////////////////////////////////////////////////////////
840:
841: /// <summary>
842: /// Return a list of service requests that have numbers-serials within the passed list
843: /// </summary>
844: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<Ia.Ngn.Cl.Model.Business.ServiceRequest.NumberSerial> numberSerialList)
845: {
846: List<long> idList;
847: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
848:
849: idList = numberSerialList.IdList();
850:
851: using (var db = new Ia.Ngn.Cl.Model.Ngn())
852: {
853: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
854: where
855: //numberSerialList.Contains(q.Number, q.Serial) does not work
856: //((from r in numberSerialList where r.Number == q.Number && r.Serial == q.Serial select r) != null) does not work
857: //numberList.Any<int>(i=> i == q.Number) does not work
858: idList.Contains((long)sr.Number * 100 + sr.Serial)
859: select sr).ToList();
860: }
861:
862: return serviceRequestList;
863: }
864:
865: ////////////////////////////////////////////////////////////////////////////
866:
867: /// <summary>
868: /// Return a list of service requests that have numbers within the passed list
869: /// </summary>
870: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<int> numberList)
871: {
872: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
873:
874: using (var db = new Ia.Ngn.Cl.Model.Ngn())
875: {
876: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService) where numberList.Contains(sr.Number) select sr).ToList();
877: }
878:
879: return serviceRequestList;
880: }
881:
882: ////////////////////////////////////////////////////////////////////////////
883:
884: /// <summary>
885: /// Return list of service requests with number
886: /// </summary>
887: public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(int number)
888: {
889: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
890:
891: using (var db = new Ia.Ngn.Cl.Model.Ngn())
892: {
893: serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
894: }
895:
896: return serviceRequestList;
897: }
898:
899: ////////////////////////////////////////////////////////////////////////////
900:
901: /// <summary>
902: /// Return a list of service requests that have numbers within the passed domain list
903: /// </summary>
904: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListByDomain(List<int> domainList)
905: {
906: List<string> stringDomainList;
907: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
908:
909: stringDomainList = new List<string>();
910:
911: using (var db = new Ia.Ngn.Cl.Model.Ngn())
912: {
913: if (domainList != null)
914: {
915: foreach (int i in domainList) stringDomainList.Add(i.ToString());
916:
917: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService) where domainList.Any(u => sr.Number.ToString().StartsWith(u.ToString())) select sr).ToList();
918: }
919: else
920: {
921: serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
922: }
923: }
924:
925: return serviceRequestList;
926: }
927:
928: ////////////////////////////////////////////////////////////////////////////
929:
930: /// <summary>
931: ///
932: /// </summary>
933: public static Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> ServiceToServiceRequestDictionary(List<int> domainList)
934: {
935: string key;
936: List<string> stringDomainList;
937: List<Ia.Ngn.Cl.Model.ServiceRequest> list;
938: Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> dictionary;
939:
940: stringDomainList = new List<string>();
941:
942: using (var db = new Ia.Ngn.Cl.Model.Ngn())
943: {
944: if (domainList != null)
945: {
946: foreach (int i in domainList) stringDomainList.Add(i.ToString());
947:
948: 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();
949:
950: dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>(list.Count);
951:
952: foreach (var sr in list)
953: {
954: key = sr.Number.ToString();
955:
956: if (dictionary.ContainsKey(key))
957: {
958: dictionary[key] = sr;
959: }
960: else dictionary[key] = sr;
961: }
962: }
963: else
964: {
965: dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>();
966: }
967: }
968:
969: return dictionary;
970: }
971:
972: ////////////////////////////////////////////////////////////////////////////
973:
974: /// <summary>
975: ///
976: /// </summary>
977: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ReadListWithinDateTimeRange(DateTime startDateTime, DateTime endDateTime)
978: {
979: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
980:
981: using (var db = new Ia.Ngn.Cl.Model.Ngn())
982: {
983: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService) where sr.RequestDateTime >= startDateTime && sr.RequestDateTime < endDateTime select sr).ToList();
984: }
985:
986: return serviceRequestList;
987: }
988:
989: ////////////////////////////////////////////////////////////////////////////
990:
991: /// <summary>
992: ///
993: /// </summary>
994: public static List<Ia.Ngn.Cl.Model.ServiceRequest> ReadListWithinIdRange(int start, int end)
995: {
996: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
997:
998: using (var db = new Ia.Ngn.Cl.Model.Ngn())
999: {
1000: serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService) where sr.Id >= start && sr.Id <= end select sr).ToList();
1001: }
1002:
1003: return serviceRequestList;
1004: }
1005:
1006: ////////////////////////////////////////////////////////////////////////////
1007:
1008: /// <summary>
1009: ///
1010: /// </summary>
1011: public static Hashtable NumberToCustomerAddressHashtable(List<int> domainList)
1012: {
1013: Hashtable ht;
1014: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1015:
1016: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1017: {
1018: if (domainList != null)
1019: {
1020: serviceRequestList = (from sr in db.ServiceRequests where domainList.Contains(sr.Number / 10000) || domainList.Contains(sr.Number / 1000) select sr).ToList();
1021:
1022: ht = new Hashtable(serviceRequestList.Count);
1023:
1024: foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList.OrderBy(u => u.Id)) ht[sr.Number.ToString()] = sr.CustomerAddress;
1025: }
1026: else
1027: {
1028: ht = new Hashtable();
1029: }
1030: }
1031:
1032: return ht;
1033: }
1034:
1035: ////////////////////////////////////////////////////////////////////////////
1036:
1037: /// <summary>
1038: /// Return the latest RequestDateTime for number
1039: /// </summary>
1040: public static DateTime LatestRequestDateTime(int number)
1041: {
1042: DateTime dateTime;
1043: Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
1044: List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1045:
1046: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1047: {
1048: serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(number);
1049:
1050: if (serviceRequestList.Count > 0)
1051: {
1052: serviceRequest = serviceRequestList.OrderByDescending(u => u.Id).FirstOrDefault();
1053:
1054: dateTime = serviceRequest.RequestDateTime;
1055: }
1056: else
1057: {
1058: dateTime = DateTime.MinValue;
1059: }
1060: }
1061:
1062: return dateTime;
1063: }
1064:
1065: ////////////////////////////////////////////////////////////////////////////
1066:
1067: /// <summary>
1068: ///
1069: /// </summary>
1070: public static string ToSimpleTextString(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest)
1071: {
1072: StringBuilder sb;
1073:
1074: sb = new StringBuilder();
1075:
1076: // Id Number Serial Status RequestDateTime Service ServiceCategory CustomerId CustomerName CustomerCategory CustomerAddress Balance
1077: sb.AppendLine("Id: " + serviceRequest.Id);
1078: sb.AppendLine("Number: " + serviceRequest.Number + "/" + serviceRequest.Serial);
1079: //sb.AppendLine("Serial: " + serviceRequest.Serial);
1080: sb.AppendLine("Status: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.StatusSortedList[serviceRequest.Status].ToString());
1081: sb.AppendLine("RequestDateTime: " + serviceRequest.RequestDateTime.ToString("yyyy-MM-dd HH:mm"));
1082: sb.AppendLine("Service: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceSortedList[serviceRequest.ServiceId].ToString());
1083: //sb.AppendLine("ServiceCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceCategorySortedList[serviceRequest.ServiceCategoryId].ToString());
1084: //sb.AppendLine("CustomerId: " + serviceRequest.CustomerId);
1085: sb.AppendLine("CustomerName: " + serviceRequest.CustomerName);
1086: //sb.AppendLine("CustomerCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.CustomerCategorySortedList[serviceRequest.CustomerCategoryId].ToString());
1087:
1088: sb.AppendLine("CustomerAddress: " + serviceRequest.CustomerAddress);
1089: //sb.AppendLine("Balance: " + serviceRequest.Balance);
1090:
1091: return sb.ToString();
1092: }
1093:
1094: ////////////////////////////////////////////////////////////////////////////
1095:
1096: /// <summary>
1097: ///
1098: /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
1099: ///
1100: /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
1101: /// 2. Add "using System.Reflection".
1102: /// 3. See sample below.
1103: ///
1104: /// </summary>
1105:
1106: private static XDocument XDocument
1107: {
1108: get
1109: {
1110: if (xDocument == null)
1111: {
1112: Assembly _assembly;
1113: StreamReader streamReader;
1114:
1115: _assembly = Assembly.GetExecutingAssembly();
1116: streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.service-request.xml"));
1117:
1118: try
1119: {
1120: if (streamReader.Peek() != -1)
1121: {
1122: xDocument = System.Xml.Linq.XDocument.Load(streamReader);
1123: }
1124: }
1125: catch (Exception)
1126: {
1127: }
1128: finally
1129: {
1130: }
1131: }
1132:
1133: return xDocument;
1134: }
1135: }
1136:
1137: ////////////////////////////////////////////////////////////////////////////
1138: ////////////////////////////////////////////////////////////////////////////
1139: }
1140:
1141: ////////////////////////////////////////////////////////////////////////////
1142: ////////////////////////////////////////////////////////////////////////////
1143: }