شركة التطبيقات المتكاملة لتصميم النظم البرمجية الخاصة

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » ServiceRequest

Public general use code classes and xml files that we've compiled and used over the years:

Service Request support class for Next Generation Network (NGN) data model.

   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:  }