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

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 Optical Fiber Network (OFN) data model.

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