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

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » ServiceRequest

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

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

   1:  using Ia.Ngn.Cl.Model.Business; // Needed for ServerExtension
   2:  using Newtonsoft.Json;
   3:  using System;
   4:  using System.Collections;
   5:  using System.Collections.Generic;
   6:  using System.Data;
   7:  using System.Data.Entity;
   8:  using System.IO;
   9:  using System.Linq;
  10:  using System.Reflection;
  11:  using System.Text;
  12:  using System.Text.RegularExpressions;
  13:  using System.Xml.Linq;
  14:   
  15:  namespace Ia.Ngn.Cl.Model.Data
  16:  {
  17:      ////////////////////////////////////////////////////////////////////////////
  18:   
  19:      /// <summary publish="true">
  20:      /// Service Request support class for Next Generation Network (NGN) data model.
  21:      /// </summary>
  22:      /// 
  23:      /// <remarks> 
  24:      /// Copyright © 2006-2020 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
  25:      ///
  26:      /// This library is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
  27:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  28:      ///
  29:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  30:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  31:      /// 
  32:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  33:      /// 
  34:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  35:      /// </remarks> 
  36:      public partial class ServiceRequest
  37:      {
  38:          private static int serviceRequestIdStartEndRangeBufferedListIndex, serviceRequestIdLatestStartEndRangeBufferedListIndex;
  39:          private static SortedList serviceCategorySortedList, serviceSortedList, customerCategorySortedList, statusSortedList;
  40:          private static XDocument xDocument;
  41:          private static List<int> serviceIdAllowedForProcessingList;
  42:          private static Dictionary<int, string> systemCode;
  43:          private static List<Tuple<int, int>> serviceRequestIdStartEndRangeBufferedTupleList;
  44:          private static Dictionary<string, string> serviceToDbNameDictionary, serviceToCustomerAddressDictionary;
  45:          private static Dictionary<string, List<string>> dbNameToServiceListDictionary, accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
  46:          private static Dictionary<string, string> serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
  47:   
  48:          private static readonly object objectLock = new object();
  49:   
  50:          ////////////////////////////////////////////////////////////////////////////
  51:   
  52:          /// <summary>
  53:          ///
  54:          /// </summary>
  55:          public ServiceRequest() { }
  56:   
  57:          ////////////////////////////////////////////////////////////////////////////
  58:   
  59:          /// <summary>
  60:          ///
  61:          /// </summary>
  62:          public static Dictionary<int, string> SystemCode
  63:          {
  64:              get
  65:              {
  66:                  lock (objectLock)
  67:                  {
  68:                      if (systemCode == null || systemCode.Count == 0) systemCode = Ia.Ngn.Cl.Model.Data.ServiceRequest._SystemCode;
  69:   
  70:                      return systemCode;
  71:                  }
  72:              }
  73:          }
  74:   
  75:          ////////////////////////////////////////////////////////////////////////////
  76:   
  77:          /// <summary>
  78:          ///
  79:          /// </summary>
  80:          private static Dictionary<int, string> _SystemCode
  81:          {
  82:              get
  83:              {
  84:                  // select code_id, code_name from system_codes
  85:   
  86:                  systemCode = new Dictionary<int, string>(100)
  87:                  {
  88:                      [6008] = "قطع مطالبات",
  89:                      [6009] = "قطع إدارى",
  90:                      [4007] = "إفراج",
  91:                      [4008] = "قيد التجديد",
  92:                      [4009] = "قيد إخطار الجهة",
  93:                      [4010] = "تسيل جزئي",
  94:                      [1001] = "جديد",
  95:                      [1002] = "مطلوب",
  96:                      [1003] = "متاح",
  97:                      [2001] = "قيد الموافقة الفنية",
  98:                      [2002] = "قيد الدفع",
  99:                      [2003] = "قيد التنفيذ",
 100:                      [2004] = "تعذر التنفيذ",
 101:                      [2005] = "تم التنفيذ",
 102:                      [3001] = "نقل من رصيد خدمة - دائن",
 103:                      [3002] = "نقل من رصيد خدمة - مدين",
 104:                      [4001] = "سارية",
 105:                      [4002] = "منتهية",
 106:                      [4003] = "مستردة",
 107:                      [4004] = "تسييل",
 108:                      [5001] = "سارية",
 109:                      [5002] = "منتهية",
 110:                      [5003] = "ملغاة",
 111:                      [4005] = "ملغاة",
 112:                      [5004] = "مبدئية",
 113:                      [6001] = "قيد التنفيذ",
 114:                      [6002] = "تم التنفيذ",
 115:                      [6003] = "تعذر التنفيذ",
 116:                      [4006] = "جديدة",
 117:                      [7001] = "يعمل",
 118:                      [7002] = "قطع مؤقت",
 119:                      [7003] = "قطع مبرمج",
 120:                      [1004] = "مقطوع",
 121:                      [5005] = "متأخرة السداد",
 122:                      [7004] = "قيد التنفيذ",
 123:                      [8001] = "قيد التنفيذ",
 124:                      [8002] = "تم التنفيذ",
 125:                      [8003] = "تعذر التنفيذ",
 126:                      [1005] = "يعمل",
 127:                      [7005] = "تعذر التنفيذ",
 128:                      [9001] = "دائن",
 129:                      [9002] = "مدين",
 130:                      [7006] = "رفع نهائي",
 131:                      [1203] = "مبدئية",
 132:                      [1201] = "سارية",
 133:                      [1202] = "ملغاة",
 134:                      [1101] = "انهاء",
 135:                      [1102] = "قطع مؤقت",
 136:                      [1301] = "قطع مبرمج",
 137:                      [1302] = "أمر عمل",
 138:                      [7007] = "رفع محاسبة",
 139:                      [1401] = "نص",
 140:                      [1402] = "رقم",
 141:                      [1403] = "تاريخ",
 142:                      [1404] = "تاريخ/وقت",
 143:                      [7008] = "قطع معاكسة",
 144:                      [1501] = "خدمة أساسية",
 145:                      [1502] = "تشغيل خدمة فرعية",
 146:                      [1503] = "إيقاف خدمة فرعية",
 147:                      [1504] = "عملية",
 148:                      [1505] = "منتج",
 149:                      [1510] = "خدمات متنوعة",
 150:                      [1103] = "إعادة",
 151:                      [1506] = "خدمة اساسية - مجموعة",
 152:                      [1507] = "خدمة تابعة",
 153:                      [1508] = "خدمة مدمجة",
 154:                      [2101] = "جديد",
 155:                      [2102] = "تم الحساب",
 156:                      [2103] = "نسخة جديدة",
 157:                      [2201] = "ملف المكالمات الدولية",
 158:                      [6004] = "إعادة الحالة",
 159:                      [1601] = "مبدئية",
 160:                      [1602] = "نهائية",
 161:                      [1603] = "ملغاة",
 162:                      [2006] = "ملغاة",
 163:                      [7009] = "ملغى",
 164:                      [7010] = "لا يعمل",
 165:                      [2104] = "مفقود",
 166:                      [5006] = "سارية - مؤقتة",
 167:                      [5007] = "مرحلة",
 168:                      [1701] = "عادي",
 169:                      [1702] = "نطاق",
 170:                      [1509] = "إيقاف خدمة أساسية"
 171:                  };
 172:   
 173:                  return systemCode;
 174:              }
 175:          }
 176:   
 177:          ////////////////////////////////////////////////////////////////////////////
 178:   
 179:          /// <summary>
 180:          ///
 181:          /// </summary>
 182:          public static SortedList ServiceCategorySortedList
 183:          {
 184:              get
 185:              {
 186:                  lock (objectLock)
 187:                  {
 188:                      if (serviceCategorySortedList == null || serviceCategorySortedList.Count == 0) serviceCategorySortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceCategorySortedList;
 189:   
 190:                      return serviceCategorySortedList;
 191:                  }
 192:              }
 193:          }
 194:   
 195:          ////////////////////////////////////////////////////////////////////////////
 196:   
 197:          /// <summary>
 198:          ///
 199:          /// </summary>
 200:          private static SortedList _ServiceCategorySortedList
 201:          {
 202:              get
 203:              {
 204:                  int id;
 205:   
 206:                  serviceCategorySortedList = new SortedList(10);
 207:   
 208:                  foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("categoryList").Elements("category"))
 209:                  {
 210:                      id = int.Parse(x.Attribute("id").Value);
 211:   
 212:                      serviceCategorySortedList[id] = x.Attribute("arabicName").Value;
 213:                  }
 214:   
 215:                  return serviceCategorySortedList;
 216:              }
 217:          }
 218:   
 219:          ////////////////////////////////////////////////////////////////////////////
 220:   
 221:          /// <summary>
 222:          ///
 223:          /// </summary>
 224:          public static SortedList ServiceSortedList
 225:          {
 226:              get
 227:              {
 228:                  lock (objectLock)
 229:                  {
 230:                      if (serviceSortedList == null || serviceSortedList.Count == 0) serviceSortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceSortedList;
 231:   
 232:                      return serviceSortedList;
 233:                  }
 234:              }
 235:          }
 236:   
 237:          ////////////////////////////////////////////////////////////////////////////
 238:   
 239:          /// <summary>
 240:          ///
 241:          /// </summary>
 242:          private static SortedList _ServiceSortedList
 243:          {
 244:              get
 245:              {
 246:                  int id;
 247:   
 248:                  serviceSortedList = new SortedList(10);
 249:   
 250:                  foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
 251:                  {
 252:                      id = int.Parse(x.Attribute("id").Value);
 253:   
 254:                      serviceSortedList[id] = x.Attribute("arabicName").Value;
 255:                  }
 256:   
 257:                  return serviceSortedList;
 258:              }
 259:          }
 260:   
 261:          ////////////////////////////////////////////////////////////////////////////
 262:   
 263:          /// <summary>
 264:          ///
 265:          /// </summary>
 266:          public static SortedList CustomerCategorySortedList
 267:          {
 268:              get
 269:              {
 270:                  lock (objectLock)
 271:                  {
 272:                      if (customerCategorySortedList == null || customerCategorySortedList.Count == 0) customerCategorySortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._CustomerCategorySortedList;
 273:   
 274:                      return customerCategorySortedList;
 275:                  }
 276:              }
 277:          }
 278:   
 279:          ////////////////////////////////////////////////////////////////////////////
 280:   
 281:          /// <summary>
 282:          ///
 283:          /// </summary>
 284:          private static SortedList _CustomerCategorySortedList
 285:          {
 286:              get
 287:              {
 288:                  int id;
 289:   
 290:                  customerCategorySortedList = new SortedList(10);
 291:   
 292:                  foreach (XElement x in XDocument.Element("serviceRequest").Elements("customer").Elements("categoryList").Elements("category"))
 293:                  {
 294:                      id = int.Parse(x.Attribute("id").Value);
 295:   
 296:                      customerCategorySortedList[id] = x.Attribute("arabicName").Value;
 297:                  }
 298:   
 299:                  return customerCategorySortedList;
 300:              }
 301:          }
 302:   
 303:          ////////////////////////////////////////////////////////////////////////////
 304:   
 305:          /// <summary>
 306:          ///
 307:          /// </summary>
 308:          public static SortedList StatusSortedList
 309:          {
 310:              get
 311:              {
 312:                  lock (objectLock)
 313:                  {
 314:                      if (statusSortedList == null || statusSortedList.Count == 0) statusSortedList = Ia.Ngn.Cl.Model.Data.ServiceRequest._StatusSortedList;
 315:   
 316:                      return statusSortedList;
 317:                  }
 318:              }
 319:          }
 320:   
 321:          ////////////////////////////////////////////////////////////////////////////
 322:   
 323:          /// <summary>
 324:          ///
 325:          /// </summary>
 326:          private static SortedList _StatusSortedList
 327:          {
 328:              get
 329:              {
 330:                  int id;
 331:   
 332:                  statusSortedList = new SortedList(10);
 333:   
 334:                  foreach (XElement x in XDocument.Element("serviceRequest").Elements("statusList").Elements("status"))
 335:                  {
 336:                      id = int.Parse(x.Attribute("id").Value);
 337:   
 338:                      statusSortedList[id] = x.Attribute("arabicName").Value;
 339:                  }
 340:   
 341:                  return statusSortedList;
 342:              }
 343:          }
 344:   
 345:          ////////////////////////////////////////////////////////////////////////////
 346:   
 347:          /// <summary>
 348:          ///
 349:          /// </summary>
 350:          public static List<int> ServiceIdAllowedForProcessingList
 351:          {
 352:              get
 353:              {
 354:                  lock (objectLock)
 355:                  {
 356:                      if (serviceIdAllowedForProcessingList == null || serviceIdAllowedForProcessingList.Count == 0) serviceIdAllowedForProcessingList = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceIdAllowedForProcessingList;
 357:   
 358:                      return serviceIdAllowedForProcessingList;
 359:                  }
 360:              }
 361:          }
 362:   
 363:          ////////////////////////////////////////////////////////////////////////////
 364:   
 365:          /// <summary>
 366:          ///
 367:          /// </summary>
 368:          private static List<int> _ServiceIdAllowedForProcessingList
 369:          {
 370:              get
 371:              {
 372:                  int id;
 373:   
 374:                  serviceIdAllowedForProcessingList = new List<int>(100);
 375:   
 376:                  foreach (XElement x in XDocument.Element("serviceRequest").Elements("service").Elements("serviceList").Elements("service"))
 377:                  {
 378:                      if (x.HasAttributes && x.Attribute("allowProcessing") != null)
 379:                      {
 380:                          if (x.Attribute("allowProcessing").Value == "true")
 381:                          {
 382:                              id = int.Parse(x.Attribute("id").Value);
 383:                              serviceIdAllowedForProcessingList.Add(id);
 384:                          }
 385:                      }
 386:                  }
 387:   
 388:                  return serviceIdAllowedForProcessingList;
 389:              }
 390:          }
 391:   
 392:          ////////////////////////////////////////////////////////////////////////////    
 393:          ////////////////////////////////////////////////////////////////////////////    
 394:   
 395:          /// <summary>
 396:          /// Return the Tuple range but with a reset of list so to get edge records
 397:          /// </summary>
 398:          public static Tuple<int, int> ServiceRequestIdStartEndRangeManagerWithReset(ref int index, out string result)
 399:          {
 400:              serviceRequestIdStartEndRangeBufferedListIndex = 0;
 401:   
 402:              return ServiceRequestIdStartEndRangeManager(ref index, out result);
 403:          }
 404:   
 405:          ////////////////////////////////////////////////////////////////////////////    
 406:   
 407:          /// <summary>
 408:          ///
 409:          /// </summary>
 410:          public static Tuple<int, int> ServiceRequestIdStartEndRangeManager(ref int index, out string result)
 411:          {
 412:              int count, edgeBufferRange, lastestRangeList;
 413:              Tuple<int, int> tuple;
 414:              List<int> serviceRequestIdList;
 415:   
 416:              count = 50;
 417:              edgeBufferRange = 5000;
 418:              lastestRangeList = 21;
 419:   
 420:              if (serviceRequestIdStartEndRangeBufferedTupleList == null || serviceRequestIdStartEndRangeBufferedListIndex == 0)
 421:              {
 422:                  serviceRequestIdStartEndRangeBufferedListIndex = index;
 423:   
 424:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 425:                  {
 426:                      serviceRequestIdList = (from sr in db.ServiceRequests orderby sr.Id ascending select sr.Id).ToList();
 427:                  }
 428:   
 429:                  serviceRequestIdStartEndRangeBufferedTupleList = Ia.Cl.Model.Default.OptimizedStartEndRangeBufferedList(serviceRequestIdList, count, edgeBufferRange);
 430:   
 431:                  serviceRequestIdStartEndRangeBufferedListIndex = 0;
 432:              }
 433:   
 434:              if (serviceRequestIdStartEndRangeBufferedTupleList.Count > 0)
 435:              {
 436:                  if (Ia.Ngn.Cl.Model.Business.Administration.NowIsOfficialWorkingTime)
 437:                  {
 438:                      // flip between last and latest range to favor reading fresh work orders quickly
 439:                      if (Ia.Cl.Model.Default.RandomBool)
 440:                      {
 441:                          serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - Ia.Cl.Model.Default.Random(lastestRangeList) - 1;
 442:                          serviceRequestIdLatestStartEndRangeBufferedListIndex = (serviceRequestIdLatestStartEndRangeBufferedListIndex >= 0) ? serviceRequestIdLatestStartEndRangeBufferedListIndex : 0;
 443:                      }
 444:                      else serviceRequestIdLatestStartEndRangeBufferedListIndex = serviceRequestIdStartEndRangeBufferedTupleList.Count - 1;
 445:   
 446:                      tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdLatestStartEndRangeBufferedListIndex];
 447:   
 448:                      if (serviceRequestIdLatestStartEndRangeBufferedListIndex == serviceRequestIdStartEndRangeBufferedTupleList.Count - 1)
 449:                      {
 450:                          tuple = new Tuple<int, int>(tuple.Item1, tuple.Item2 + edgeBufferRange);
 451:                      }
 452:   
 453:                      result = "(latest: " + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdLatestStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
 454:                  }
 455:                  else
 456:                  {
 457:                      tuple = serviceRequestIdStartEndRangeBufferedTupleList[serviceRequestIdStartEndRangeBufferedListIndex];
 458:   
 459:                      result = "(historic: " + tuple.Item1 + "-" + tuple.Item2 + " " + serviceRequestIdStartEndRangeBufferedListIndex + "/" + serviceRequestIdStartEndRangeBufferedTupleList.Count + ")";
 460:   
 461:                      serviceRequestIdStartEndRangeBufferedListIndex = Ia.Cl.Model.Default.IncrementListIndexOrRestart(serviceRequestIdStartEndRangeBufferedTupleList, serviceRequestIdStartEndRangeBufferedListIndex);
 462:                  }
 463:              }
 464:              else
 465:              {
 466:                  result = "(0-0 0/0)";
 467:   
 468:                  tuple = null;
 469:              }
 470:   
 471:              index = serviceRequestIdStartEndRangeBufferedListIndex;
 472:   
 473:              return tuple;
 474:          }
 475:   
 476:          ////////////////////////////////////////////////////////////////////////////    
 477:   
 478:          /// <summary>
 479:          ///
 480:          /// </summary>
 481:          public static string AlterSessionOfCustomerDepartmentOracleDatabase
 482:          {
 483:              get
 484:              {
 485:                  return @"alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS'";
 486:              }
 487:          }
 488:   
 489:          ////////////////////////////////////////////////////////////////////////////
 490:   
 491:          /// <summary>
 492:          /// Return list of service requests
 493:          /// </summary>
 494:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> List()
 495:          {
 496:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 497:   
 498:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 499:              {
 500:                  serviceRequestList = (from sr in db.ServiceRequests select sr).ToList();
 501:              }
 502:   
 503:              return serviceRequestList;
 504:          }
 505:   
 506:          ////////////////////////////////////////////////////////////////////////////
 507:   
 508:          /// <summary>
 509:          /// Return list of service requests with number
 510:          /// </summary>
 511:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(int number)
 512:          {
 513:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 514:   
 515:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 516:              {
 517:                  serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
 518:              }
 519:   
 520:              return serviceRequestList;
 521:          }
 522:   
 523:          ////////////////////////////////////////////////////////////////////////////
 524:   
 525:          /// <summary>
 526:          /// Return list of service requests with service
 527:          /// </summary>
 528:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(string service)
 529:          {
 530:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 531:   
 532:              if (!string.IsNullOrEmpty(service))
 533:              {
 534:                  if (int.TryParse(service, out int number))
 535:                  {
 536:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 537:                      {
 538:                          serviceRequestList = (from sr in db.ServiceRequests where sr.Number == number select sr).ToList();
 539:                      }
 540:                  }
 541:                  else
 542:                  {
 543:                      throw new ArgumentException(@"List(): service is not a number, service: " + service);
 544:                  }
 545:              }
 546:              else serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 547:   
 548:              return serviceRequestList;
 549:          }
 550:   
 551:          ////////////////////////////////////////////////////////////////////////////
 552:   
 553:          /// <summary>
 554:          /// Return a list of service requests that have numbers (including changed-to numbers) within the passed list
 555:          /// </summary>
 556:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<int> numberList)
 557:          {
 558:              List<string> serviceList;
 559:   
 560:              serviceList = (from n in numberList select n.ToString()).ToList();
 561:   
 562:              return List(serviceList);
 563:          }
 564:   
 565:          ////////////////////////////////////////////////////////////////////////////
 566:   
 567:          /// <summary>
 568:          /// Return a list of service requests that have numbers (including changed-to numbers) within the passed list
 569:          /// </summary>
 570:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<string> serviceList)
 571:          {
 572:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList, viaServiceRequestTypeList;
 573:   
 574:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 575:              {
 576:                  serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService).Include(a => a.ServiceRequestTypes)
 577:                                        where serviceList.Contains(sr.Number.ToString())
 578:                                        select sr).ToList();
 579:   
 580:                  viaServiceRequestTypeList = (from srt in db.ServiceRequestTypes.Include(a => a.ServiceRequest).Include(a => a.ServiceRequest.ServiceRequestService)
 581:                                               where srt.TypeId == 11 && serviceList.Contains(srt.Value)
 582:                                               select srt.ServiceRequest).Include(a => a.ServiceRequestTypes).ToList();
 583:   
 584:                  serviceRequestList = serviceRequestList.Union(viaServiceRequestTypeList).Distinct().ToList();
 585:              }
 586:   
 587:              return serviceRequestList.ToList();
 588:          }
 589:   
 590:          ////////////////////////////////////////////////////////////////////////////
 591:   
 592:          /// <summary>
 593:          /// Return a list of service requests that have numbers-serials within the passed list
 594:          /// </summary>
 595:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> List(List<Ia.Ngn.Cl.Model.Business.ServiceRequest.NumberSerial> numberSerialList)
 596:          {
 597:              List<long> idList;
 598:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 599:   
 600:              idList = numberSerialList.IdList();
 601:   
 602:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 603:              {
 604:                  serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
 605:                                        where
 606:                                        //numberSerialList.Contains(q.Number, q.Serial) does not work
 607:                                        //((from r in numberSerialList where r.Number == q.Number && r.Serial == q.Serial select r) != null) does not work
 608:                                        //numberList.Any<int>(i=> i == q.Number)  does not work
 609:                                        idList.Contains((long)sr.Number * 100 + sr.Serial)
 610:                                        select sr).ToList();
 611:              }
 612:   
 613:              return serviceRequestList;
 614:          }
 615:   
 616:          ////////////////////////////////////////////////////////////////////////////
 617:   
 618:          /// <summary>
 619:          /// Return a list of numbers, including changed-to numbers, that are related to the passed number list
 620:          /// </summary>
 621:          public static List<int> NumberAndChangedAndChangedToList(List<int> numberList)
 622:          {
 623:              List<int> li, li2, li3;
 624:              List<string> serviceList;
 625:              List<Ia.Ngn.Cl.Model.ServiceRequest> list1, list2, list3, list4, list5, serviceRequestList;
 626:   
 627:              serviceList = (from n in numberList select n.ToString()).ToList();
 628:   
 629:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 630:              {
 631:                  //
 632:                  list1 = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
 633:                           where numberList.Contains(sr.Number)
 634:                           select sr).ToList();
 635:   
 636:                  if (list1.Count > 0)
 637:                  {
 638:                      li = (from sr in list1 select sr.Number).Distinct().ToList();
 639:   
 640:                      li2 = new List<int>();
 641:   
 642:                      foreach (var srt in list1.SelectMany(u => u.ServiceRequestTypes))
 643:                      {
 644:                          if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) li2.Add(i);
 645:                      }
 646:   
 647:                      // below: this is the collect first changed-to number information
 648:                      if (li2.Count > 0)
 649:                      {
 650:                          list2 = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
 651:                                   where li2.Contains(sr.Number)
 652:                                   select sr).ToList();
 653:   
 654:                          if (list2.Count > 0)
 655:                          {
 656:                              li = (from sr in list2 select sr.Number).Distinct().ToList();
 657:   
 658:                              li3 = new List<int>();
 659:   
 660:                              foreach (var srt in list2.SelectMany(u => u.ServiceRequestTypes))
 661:                              {
 662:                                  if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) li3.Add(i);
 663:                              }
 664:   
 665:                              // below: this is the collect second changed-to number information
 666:                              if (li3.Count > 0)
 667:                              {
 668:                                  list3 = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
 669:                                           where li3.Contains(sr.Number)
 670:                                           select sr).ToList();
 671:                              }
 672:                              else list3 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 673:                          }
 674:                          else
 675:                          {
 676:                              list2 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 677:                              list3 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 678:                          }
 679:                      }
 680:                      else
 681:                      {
 682:                          list2 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 683:                          list3 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 684:                      }
 685:                  }
 686:                  else
 687:                  {
 688:                      list2 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 689:                      list3 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 690:                  }
 691:   
 692:   
 693:                  //
 694:                  list4 = (from srt in db.ServiceRequestTypes.Include(a => a.ServiceRequest)
 695:                           where srt.TypeId == 11 && serviceList.Contains(srt.Value)
 696:                           select srt.ServiceRequest).ToList();
 697:   
 698:                  if (list4.Count > 0)
 699:                  {
 700:                      li = (from sr in list4 select sr.Number).Distinct().ToList();
 701:   
 702:                      list5 = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestTypes)
 703:                               where li.Contains(sr.Number)
 704:                               select sr).ToList();
 705:                  }
 706:                  else list5 = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 707:   
 708:                  //
 709:                  serviceRequestList = list1.Union(list2).Union(list3).Union(list4).Union(list5).Distinct().ToList();
 710:   
 711:                  if (serviceRequestList.Count > 0)
 712:                  {
 713:                      numberList = (from n in serviceRequestList select n.Number).Distinct().ToList();
 714:                  }
 715:                  else numberList = new List<int>();
 716:              }
 717:   
 718:              return numberList.ToList();
 719:          }
 720:   
 721:          ////////////////////////////////////////////////////////////////////////////
 722:   
 723:          /// <summary>
 724:          /// Return a list of numbers, including changed-to numbers, that are related to the given service request list
 725:          /// </summary>
 726:          public static List<int> NumberFromServiceRequestListList(List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList)
 727:          {
 728:              List<int> list, changedList, numberList;
 729:   
 730:              if (serviceRequestList != null)
 731:              {
 732:                  list = (from sr in serviceRequestList select sr.Number).ToList();
 733:   
 734:                  changedList = new List<int>();
 735:   
 736:                  foreach (var sr in serviceRequestList)
 737:                  {
 738:                      foreach (var srt in sr.ServiceRequestTypes)
 739:                      {
 740:                          if (srt.TypeId == 11 && int.TryParse(srt.Value, out int i)) changedList.Add(i);
 741:                      }
 742:                  }
 743:   
 744:                  numberList = list.Union(changedList).Distinct().ToList();
 745:              }
 746:              else numberList = new List<int>();
 747:   
 748:              return numberList;
 749:          }
 750:   
 751:          ////////////////////////////////////////////////////////////////////////////
 752:          ////////////////////////////////////////////////////////////////////////////
 753:   
 754:          /// <summary>
 755:          /// Return a list of service requests that have numbers within the passed domain list
 756:          /// </summary>
 757:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListByDomain(List<int> domainList)
 758:          {
 759:              List<string> stringDomainList;
 760:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 761:   
 762:              stringDomainList = new List<string>();
 763:   
 764:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 765:              {
 766:                  if (domainList != null)
 767:                  {
 768:                      foreach (int i in domainList) stringDomainList.Add(i.ToString());
 769:   
 770:                      serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService)
 771:                                            where domainList.Any(u => sr.Number.ToString().StartsWith(u.ToString()))
 772:                                            select sr).ToList();
 773:                  }
 774:                  else
 775:                  {
 776:                      serviceRequestList = new List<Ia.Ngn.Cl.Model.ServiceRequest>();
 777:                  }
 778:              }
 779:   
 780:              return serviceRequestList;
 781:          }
 782:   
 783:          ////////////////////////////////////////////////////////////////////////////
 784:   
 785:          /// <summary>
 786:          ///
 787:          /// </summary>
 788:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinDateTimeRange(DateTime startDateTime, DateTime endDateTime)
 789:          {
 790:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 791:   
 792:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 793:              {
 794:                  serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService) where sr.RequestDateTime >= startDateTime && sr.RequestDateTime < endDateTime select sr).ToList();
 795:              }
 796:   
 797:              return serviceRequestList;
 798:          }
 799:   
 800:          ////////////////////////////////////////////////////////////////////////////
 801:   
 802:          /// <summary>
 803:          ///
 804:          /// </summary>
 805:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> ListWithinIdRange(int start, int end)
 806:          {
 807:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 808:   
 809:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 810:              {
 811:                  serviceRequestList = (from sr in db.ServiceRequests.Include(a => a.ServiceRequestService) where sr.Id >= start && sr.Id <= end select sr).ToList();
 812:              }
 813:   
 814:              return serviceRequestList;
 815:          }
 816:   
 817:          ////////////////////////////////////////////////////////////////////////////
 818:          ////////////////////////////////////////////////////////////////////////////
 819:   
 820:          /// <summary>
 821:          ///
 822:          /// </summary>
 823:          public static void UpdateWithServiceList(List<string> serviceList, List<Ia.Ngn.Cl.Model.ServiceRequest> newServiceRequestList, out string result)
 824:          {
 825:              int readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount, serviceRequestId;
 826:              string serviceRequestServiceId;
 827:              Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
 828:   
 829:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 830:              result = string.Empty;
 831:   
 832:              readItemCount = newServiceRequestList.Count;
 833:   
 834:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 835:              {
 836:                  foreach (Ia.Ngn.Cl.Model.ServiceRequest newServiceRequest in newServiceRequestList)
 837:                  {
 838:                      serviceRequestId = newServiceRequest.Id;
 839:   
 840:                      serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequestId select sr).SingleOrDefault();
 841:   
 842:                      existingItemCount = (serviceRequest != null) ? 1 : 0;
 843:   
 844:                      try
 845:                      {
 846:                          if (newServiceRequest.ServiceRequestService != null)
 847:                          {
 848:                              serviceRequestServiceId = newServiceRequest.ServiceRequestService.Id;
 849:   
 850:                              newServiceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestServiceId select srs).SingleOrDefault();
 851:                          }
 852:   
 853:                          if (serviceRequest.Update(newServiceRequest))
 854:                          {
 855:                              db.ServiceRequests.Attach(serviceRequest);
 856:                              db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
 857:   
 858:                              updatedItemCount++;
 859:                          }
 860:                      }
 861:                      catch (Exception e)
 862:                      {
 863:   
 864:                      }
 865:                  }
 866:   
 867:                  db.SaveChanges();
 868:   
 869:                  result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") ";
 870:              }
 871:          }
 872:   
 873:          ////////////////////////////////////////////////////////////////////////////
 874:   
 875:          /// <summary>
 876:          ///
 877:          /// </summary>
 878:          public static bool UpdateServiceRequestService(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, Ia.Ngn.Cl.Model.ServiceRequestService serviceRequestService, out string result)
 879:          {
 880:              bool b;
 881:   
 882:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 883:              {
 884:                  serviceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequest.Id select sr).SingleOrDefault();
 885:   
 886:                  if (serviceRequest.ServiceRequestService != serviceRequestService)
 887:                  {
 888:                      serviceRequest.ServiceRequestService = (from srs in db.ServiceRequestServices where srs.Id == serviceRequestService.Id select srs).SingleOrDefault();
 889:   
 890:                      db.ServiceRequests.Attach(serviceRequest);
 891:                      db.Entry(serviceRequest).Property(x => x.ServiceRequestService).IsModified = true;
 892:   
 893:                      db.SaveChanges();
 894:   
 895:                      result = "Success: ServiceRequests ServiceRequestService updated. ";
 896:                      b = true;
 897:                  }
 898:                  else
 899:                  {
 900:                      result = "Warning: ServiceRequests ServiceRequestService value was not updated because its the same. ";
 901:   
 902:                      b = false;
 903:                  }
 904:              }
 905:   
 906:              return b;
 907:          }
 908:   
 909:          ////////////////////////////////////////////////////////////////////////////
 910:   
 911:          /// <summary>
 912:          ///
 913:          /// </summary>
 914:          public static void UpdateForADateTimeRangeWithOutputDataTable(DataTable dataTable, DateTime dateTime, out string result)
 915:          {
 916:              // below: the SQL statement should be within the dataTable.TableName variable
 917:              int number, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
 918:              int serviceRequestId;
 919:              string sql, r, customerAddress;
 920:              ArrayList newServiceRequestIdArryList;
 921:              DateTime startDateTime, endDateTime;
 922:              Match match;
 923:              Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
 924:              Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
 925:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 926:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 927:   
 928:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 929:              result = r = string.Empty;
 930:   
 931:              startDateTime = DateTime.MinValue;
 932:   
 933:              if (dataTable != null)
 934:              {
 935:                  sql = dataTable.TableName;
 936:   
 937:                  // 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;
 938:                  // 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;
 939:   
 940:                  match = Regex.Match(sql, @".+'(\d{2})\/(\d{2})\/(\d{4})'.+'(\d{2})\/(\d{2})\/(\d{4})'.+", RegexOptions.Singleline);
 941:                  //                             1        2        3        4          5        6
 942:   
 943:                  if (match.Success)
 944:                  {
 945:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 946:                      {
 947:                          readItemCount = dataTable.Rows.Count;
 948:   
 949:                          //if (dataTable.Rows.Count > 0)
 950:                          //{
 951:                          startDateTime = DateTime.Parse(match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value);
 952:                          endDateTime = DateTime.Parse(match.Groups[6].Value + "-" + match.Groups[5].Value + "-" + match.Groups[4].Value);
 953:   
 954:                          serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ListWithinDateTimeRange(startDateTime, endDateTime);
 955:                          existingItemCount = serviceRequestList.Count;
 956:   
 957:                          newServiceRequestIdArryList = new ArrayList(dataTable.Rows.Count + 1);
 958:   
 959:                          foreach (DataRow dataRow in dataTable.Rows)
 960:                          {
 961:                              number = int.Parse(dataRow["SRV_NO"].ToString());
 962:   
 963:                              if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
 964:                              {
 965:                                  serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
 966:   
 967:                                  customerAddress = dataRow["ADDRESS"].ToString();
 968:                                  serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
 969:   
 970:                                  newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
 971:                                  {
 972:                                      Id = serviceRequestId,
 973:                                      Number = number,
 974:   
 975:                                      CustomerAddress = customerAddress,
 976:                                      AreaId = serviceAddress.AreaId,
 977:   
 978:                                      CustomerCategoryId = int.Parse(dataRow["CUST_CAT_ID"].ToString()),
 979:                                      CustomerId = int.Parse(dataRow["ACCOUNT_NO"].ToString()),
 980:                                      CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
 981:                                      RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
 982:                                      Serial = int.Parse(dataRow["SRV_SER_NO"].ToString()),
 983:                                      ServiceCategoryId = int.Parse(dataRow["SRV_CAT_ID"].ToString()),
 984:                                      ServiceId = int.Parse(dataRow["SRV_ID"].ToString()),
 985:                                      Balance = double.Parse(dataRow["BALANCE"].ToString()),
 986:                                      Status = int.Parse(dataRow["STATUS"].ToString())
 987:                                  };
 988:   
 989:                                  serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
 990:   
 991:                                  if (serviceRequest == null)
 992:                                  {
 993:                                      newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
 994:   
 995:                                      db.ServiceRequests.Add(newServiceRequest);
 996:   
 997:                                      insertedItemCount++;
 998:                                  }
 999:                                  else
1000:                                  {
1001:                                      // below: copy values from newServiceRequest to serviceRequest
1002:   
1003:                                      if (serviceRequest.Update(newServiceRequest))
1004:                                      {
1005:                                          db.ServiceRequests.Attach(serviceRequest);
1006:                                          db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
1007:   
1008:                                          updatedItemCount++;
1009:                                      }
1010:                                  }
1011:   
1012:                                  newServiceRequestIdArryList.Add(serviceRequestId);
1013:                              }
1014:                              else
1015:                              {
1016:                                  r += "Number: " + number + " is not within allowed domain list, ";
1017:                              }
1018:                          }
1019:   
1020:                          // below: this function will remove values that were not present in the reading
1021:                          if (serviceRequestList.Count > 0)
1022:                          {
1023:                              foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
1024:                              {
1025:                                  if (!newServiceRequestIdArryList.Contains(sr.Id))
1026:                                  {
1027:                                      serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
1028:   
1029:                                      db.ServiceRequests.Remove(serviceRequest);
1030:   
1031:                                      // below: we will also remove SRT records referensing this SR
1032:                                      serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
1033:   
1034:                                      foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
1035:   
1036:                                      deletedItemCount++;
1037:                                  }
1038:                              }
1039:                          }
1040:   
1041:                          db.SaveChanges();
1042:   
1043:                          result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
1044:                          //}
1045:                          //else
1046:                          //{
1047:                          //    result = "(" + readItemCount + "/?/?) ";
1048:                          //}
1049:                      }
1050:                  }
1051:                  else
1052:                  {
1053:                      result = "(?/?/?: SQL in TableName is unmatched) ";
1054:                  }
1055:              }
1056:              else
1057:              {
1058:                  result = "(dataTable == null/?/?) ";
1059:              }
1060:          }
1061:   
1062:          ////////////////////////////////////////////////////////////////////////////
1063:   
1064:          /// <summary>
1065:          ///
1066:          /// </summary>
1067:          public static void UpdateForServiceRequestWithOutputDataTableAndIdRange(DataTable dataTable, Tuple<int, int> startEndRange, out bool isUpdated, out string result)
1068:          {
1069:              // below: the SQL statement should be within the dataTable.TableName variable
1070:              int serviceRequestId, start, end, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
1071:              string sql, r, customerAddress;
1072:              ArrayList newServiceRequestIdArrayList;
1073:              Match match;
1074:              Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
1075:              Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
1076:              List<int> numbersNotWithinAllowedDomainList;
1077:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1078:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
1079:   
1080:              isUpdated = false;
1081:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
1082:              result = r = string.Empty;
1083:              numbersNotWithinAllowedDomainList = new List<int>();
1084:   
1085:              if (dataTable != null)
1086:              {
1087:                  sql = dataTable.TableName;
1088:   
1089:                  // select * from SRV_REQ_FIPER where SRV_REQ_ID >= 110000 and SRV_REQ_ID <= 321203 order by REQ_DATE asc, SRV_REQ_ID asc
1090:                  match = Regex.Match(sql, @"SRV_REQ_ID >= (\d+) and SRV_REQ_ID <= (\d+) ", RegexOptions.Singleline);
1091:                  //                                       1                       2
1092:   
1093:                  if (match.Success)
1094:                  {
1095:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
1096:                      {
1097:                          readItemCount = dataTable.Rows.Count;
1098:   
1099:                          start = int.Parse(match.Groups[1].Value);
1100:                          end = int.Parse(match.Groups[2].Value);
1101:   
1102:                          serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.ListWithinIdRange(start, end);
1103:                          existingItemCount = serviceRequestList.Count;
1104:   
1105:                          newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
1106:   
1107:                          foreach (DataRow dataRow in dataTable.Rows)
1108:                          {
1109:                              if (int.TryParse(dataRow["SRV_NO"].ToString(), out int number))
1110:                              {
1111:                                  if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
1112:                                  {
1113:                                      serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
1114:                                      customerAddress = dataRow["ADDRESS"].ToString();
1115:   
1116:                                      serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
1117:   
1118:                                      newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
1119:                                      {
1120:                                          Id = serviceRequestId,
1121:                                          Number = number,
1122:   
1123:                                          CustomerAddress = customerAddress,
1124:                                          AreaId = serviceAddress.AreaId,
1125:   
1126:                                          CustomerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0,
1127:                                          CustomerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0,
1128:                                          CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
1129:                                          RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
1130:                                          Serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0,
1131:                                          ServiceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0,
1132:                                          ServiceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0,
1133:                                          Balance = double.Parse(dataRow["BALANCE"].ToString()),
1134:   
1135:                                          Status = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0
1136:                                      };
1137:   
1138:                                      serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
1139:   
1140:                                      if (serviceRequest == null)
1141:                                      {
1142:                                          newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
1143:   
1144:                                          db.ServiceRequests.Add(newServiceRequest);
1145:   
1146:                                          insertedItemCount++;
1147:                                      }
1148:                                      else
1149:                                      {
1150:                                          // below: copy values from newServiceRequest to serviceRequest
1151:   
1152:                                          if (serviceRequest.UpdateSkipServiceRequestService(newServiceRequest))
1153:                                          {
1154:                                              db.ServiceRequests.Attach(serviceRequest);
1155:                                              db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
1156:   
1157:                                              updatedItemCount++;
1158:                                          }
1159:                                      }
1160:   
1161:                                      newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
1162:                                  }
1163:                                  else
1164:                                  {
1165:                                      numbersNotWithinAllowedDomainList.Add(number);
1166:                                  }
1167:                              }
1168:                              else
1169:                              {
1170:                                  //
1171:                              }
1172:                          }
1173:   
1174:                          /*
1175:                          if (numbersNotWithinAllowedDomainList.Count > 0)
1176:                          {
1177:                              r = "Numbers not within allowed domain list: ";
1178:  
1179:                              foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
1180:  
1181:                              r = r.Trim(',');
1182:                          }
1183:                          */
1184:   
1185:                          // below: this function will remove values that were not present in the reading
1186:                          if (serviceRequestList.Count > 0)
1187:                          {
1188:                              foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
1189:                              {
1190:                                  if (!newServiceRequestIdArrayList.Contains(sr.Id))
1191:                                  {
1192:                                      serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
1193:   
1194:                                      db.ServiceRequests.Remove(serviceRequest);
1195:   
1196:                                      // below: we will also remove SRT records referensing this SR
1197:                                      serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
1198:   
1199:                                      foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
1200:   
1201:                                      deletedItemCount++;
1202:                                  }
1203:                              }
1204:                          }
1205:   
1206:                          db.SaveChanges();
1207:   
1208:                          if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
1209:                          else isUpdated = false;
1210:   
1211:                          result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
1212:                      }
1213:                  }
1214:                  else
1215:                  {
1216:                      result = "(?/?/?: SQL in TableName is unmatched) ";
1217:                  }
1218:              }
1219:              else
1220:              {
1221:                  result = "(dataTable == null/?/?) ";
1222:              }
1223:          }
1224:   
1225:          ////////////////////////////////////////////////////////////////////////////
1226:   
1227:          /// <summary>
1228:          ///
1229:          /// </summary>
1230:          public static void UpdateForServiceRequestWithOutputDataTableAndService(DataTable dataTable, string service, out bool isUpdated, out string result)
1231:          {
1232:              int serviceRequestId, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
1233:              string sql, sqlService, r, customerAddress;
1234:              ArrayList newServiceRequestIdArrayList;
1235:              Match match;
1236:              Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
1237:              Ia.Ngn.Cl.Model.ServiceRequest serviceRequest, newServiceRequest;
1238:              List<int> numbersNotWithinAllowedDomainList;
1239:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1240:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
1241:   
1242:              isUpdated = false;
1243:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
1244:              result = r = string.Empty;
1245:              numbersNotWithinAllowedDomainList = new List<int>();
1246:   
1247:              if (dataTable != null)
1248:              {
1249:                  // below: the SQL statement should be within the dataTable.TableName variable
1250:                  sql = dataTable.TableName;
1251:   
1252:                  // select * from SRV_REQ_FIPER where SRV_NO = 23632222 order by SRV_REQ_ID asc
1253:                  match = Regex.Match(sql, @"SRV_NO = (\d+) order by SRV_REQ_ID asc", RegexOptions.Singleline);
1254:   
1255:                  if (match.Success)
1256:                  {
1257:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
1258:                      {
1259:                          readItemCount = dataTable.Rows.Count;
1260:   
1261:                          sqlService = match.Groups[1].Value;
1262:   
1263:                          if (service == sqlService)
1264:                          {
1265:                              serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(service);
1266:                              existingItemCount = serviceRequestList.Count;
1267:   
1268:                              newServiceRequestIdArrayList = new ArrayList(dataTable.Rows.Count + 1);
1269:   
1270:                              foreach (DataRow dataRow in dataTable.Rows)
1271:                              {
1272:                                  if (int.TryParse(dataRow["SRV_NO"].ToString(), out int number))
1273:                                  {
1274:                                      if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
1275:                                      {
1276:                                          serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
1277:                                          customerAddress = dataRow["ADDRESS"].ToString();
1278:   
1279:                                          serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(number.ToString(), customerAddress, out string level);
1280:   
1281:                                          newServiceRequest = new Ia.Ngn.Cl.Model.ServiceRequest()
1282:                                          {
1283:                                              Id = serviceRequestId,
1284:                                              Number = number,
1285:                                              CustomerAddress = customerAddress,
1286:                                              AreaId = serviceAddress.AreaId,
1287:                                              CustomerCategoryId = int.TryParse(dataRow["CUST_CAT_ID"].ToString(), out int i) ? i : 0,
1288:                                              CustomerId = int.TryParse(dataRow["ACCOUNT_NO"].ToString(), out i) ? i : 0,
1289:                                              CustomerName = Ia.Ngn.Cl.Model.Business.Default.CorrectCustomerName(dataRow["NAME"].ToString()),
1290:                                              RequestDateTime = DateTime.Parse(dataRow["REQ_DATE"].ToString()),
1291:                                              Serial = int.TryParse(dataRow["SRV_SER_NO"].ToString(), out i) ? i : 0,
1292:                                              ServiceCategoryId = int.TryParse(dataRow["SRV_CAT_ID"].ToString(), out i) ? i : 0,
1293:                                              ServiceId = int.TryParse(dataRow["SRV_ID"].ToString(), out i) ? i : 0,
1294:                                              Balance = double.Parse(dataRow["BALANCE"].ToString()),
1295:                                              Status = int.TryParse(dataRow["STATUS"].ToString(), out i) ? i : 0
1296:                                          };
1297:   
1298:                                          serviceRequest = (from sr in serviceRequestList where sr.Id == newServiceRequest.Id select sr).SingleOrDefault();
1299:   
1300:                                          if (serviceRequest == null)
1301:                                          {
1302:                                              newServiceRequest.Created = newServiceRequest.Updated = DateTime.UtcNow.AddHours(3);
1303:   
1304:                                              db.ServiceRequests.Add(newServiceRequest);
1305:   
1306:                                              insertedItemCount++;
1307:                                          }
1308:                                          else
1309:                                          {
1310:                                              // below: copy values from newServiceRequest to serviceRequest
1311:   
1312:                                              if (serviceRequest.UpdateSkipServiceRequestService(newServiceRequest))
1313:                                              {
1314:                                                  db.ServiceRequests.Attach(serviceRequest);
1315:                                                  db.Entry(serviceRequest).State = System.Data.Entity.EntityState.Modified;
1316:   
1317:                                                  updatedItemCount++;
1318:                                              }
1319:                                          }
1320:   
1321:                                          newServiceRequestIdArrayList.Add(serviceRequestId); // keep at the end
1322:                                      }
1323:                                      else
1324:                                      {
1325:                                          numbersNotWithinAllowedDomainList.Add(number);
1326:                                      }
1327:                                  }
1328:                                  else
1329:                                  {
1330:                                      //
1331:                                  }
1332:                              }
1333:   
1334:                              /*
1335:                              if (numbersNotWithinAllowedDomainList.Count > 0)
1336:                              {
1337:                                  r = "Numbers not within allowed domain list: ";
1338:  
1339:                                  foreach (int n in numbersNotWithinAllowedDomainList) r += n + ",";
1340:  
1341:                                  r = r.Trim(',');
1342:                              }
1343:                              */
1344:   
1345:                              // below: this function will remove values that were not present in the reading
1346:                              if (serviceRequestList.Count > 0)
1347:                              {
1348:                                  foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList)
1349:                                  {
1350:                                      if (!newServiceRequestIdArrayList.Contains(sr.Id))
1351:                                      {
1352:                                          serviceRequest = (from sr2 in db.ServiceRequests where sr2.Id == sr.Id select sr2).SingleOrDefault();
1353:   
1354:                                          db.ServiceRequests.Remove(serviceRequest);
1355:   
1356:                                          // below: we will also remove SRT records referencing this SR
1357:                                          serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Id == sr.Id select srt).ToList();
1358:   
1359:                                          foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList) db.ServiceRequestTypes.Remove(srt);
1360:   
1361:                                          deletedItemCount++;
1362:                                      }
1363:                                  }
1364:                              }
1365:   
1366:                              db.SaveChanges();
1367:   
1368:                              if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
1369:                              else isUpdated = false;
1370:   
1371:                              result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
1372:                          }
1373:                          else
1374:                          {
1375:                              throw new ArgumentException(@"UpdateForServiceRequestWithOutputDataTableAndService(): service != sqlService, service: " + service + ", sqlService: " + sqlService);
1376:                          }
1377:                      }
1378:                  }
1379:                  else
1380:                  {
1381:                      result = "(?/?/?: SQL in TableName is unmatched) ";
1382:                  }
1383:              }
1384:              else
1385:              {
1386:                  result = "(dataTable == null/?/?) ";
1387:              }
1388:          }
1389:   
1390:          ////////////////////////////////////////////////////////////////////////////
1391:   
1392:          /// <summary>
1393:          /// 
1394:          /// </summary>
1395:          public static Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> ServiceToServiceRequestDictionary(List<int> domainList)
1396:          {
1397:              string key;
1398:              List<string> stringDomainList;
1399:              List<Ia.Ngn.Cl.Model.ServiceRequest> list;
1400:              Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest> dictionary;
1401:   
1402:              stringDomainList = new List<string>();
1403:   
1404:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1405:              {
1406:                  if (domainList != null)
1407:                  {
1408:                      foreach (int i in domainList) stringDomainList.Add(i.ToString());
1409:   
1410:                      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();
1411:   
1412:                      dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>(list.Count);
1413:   
1414:                      foreach (var sr in list)
1415:                      {
1416:                          key = sr.Number.ToString();
1417:   
1418:                          if (dictionary.ContainsKey(key))
1419:                          {
1420:                              dictionary[key] = sr;
1421:                          }
1422:                          else dictionary[key] = sr;
1423:                      }
1424:                  }
1425:                  else
1426:                  {
1427:                      dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.ServiceRequest>();
1428:                  }
1429:              }
1430:   
1431:              return dictionary;
1432:          }
1433:   
1434:          ////////////////////////////////////////////////////////////////////////////
1435:   
1436:          /// <summary>
1437:          ///
1438:          /// </summary>
1439:          public static Hashtable NumberToCustomerAddressHashtable(List<int> domainList)
1440:          {
1441:              Hashtable ht;
1442:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1443:   
1444:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1445:              {
1446:                  if (domainList != null)
1447:                  {
1448:                      serviceRequestList = (from sr in db.ServiceRequests where domainList.Contains(sr.Number / 10000) || domainList.Contains(sr.Number / 1000) select sr).ToList();
1449:   
1450:                      ht = new Hashtable(serviceRequestList.Count);
1451:   
1452:                      foreach (Ia.Ngn.Cl.Model.ServiceRequest sr in serviceRequestList.OrderBy(u => u.Id)) ht[sr.Number.ToString()] = sr.CustomerAddress;
1453:                  }
1454:                  else
1455:                  {
1456:                      ht = new Hashtable();
1457:                  }
1458:              }
1459:   
1460:              return ht;
1461:          }
1462:   
1463:          ////////////////////////////////////////////////////////////////////////////
1464:   
1465:          /// <summary>
1466:          /// Return the latest RequestDateTime for number
1467:          /// </summary>
1468:          public static DateTime LatestRequestDateTime(int number)
1469:          {
1470:              DateTime dateTime;
1471:              Ia.Ngn.Cl.Model.ServiceRequest serviceRequest;
1472:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1473:   
1474:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1475:              {
1476:                  serviceRequestList = Ia.Ngn.Cl.Model.Data.ServiceRequest.List(number);
1477:   
1478:                  if (serviceRequestList.Count > 0)
1479:                  {
1480:                      serviceRequest = serviceRequestList.OrderByDescending(u => u.Id).FirstOrDefault();
1481:   
1482:                      dateTime = serviceRequest.RequestDateTime;
1483:                  }
1484:                  else
1485:                  {
1486:                      dateTime = DateTime.MinValue;
1487:                  }
1488:              }
1489:   
1490:              return dateTime;
1491:          }
1492:   
1493:          ////////////////////////////////////////////////////////////////////////////
1494:   
1495:          /// <summary>
1496:          ///
1497:          /// </summary>
1498:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> LastN(int numberOfServiceRequests)
1499:          {
1500:              // below:
1501:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1502:   
1503:              serviceRequestList = null;
1504:   
1505:              try
1506:              {
1507:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
1508:                  {
1509:                      serviceRequestList = (from sr in db.ServiceRequests orderby sr.RequestDateTime descending select sr).Take(numberOfServiceRequests).ToList();
1510:                  }
1511:              }
1512:              catch (Exception)
1513:              {
1514:                  //resultLabel.Text = "Error during retrieval of data for \"" + ip + "\": " + ex.Message + ". ";
1515:                  //resultLabel.CssClass = "error";
1516:              }
1517:   
1518:              return serviceRequestList;
1519:          }
1520:   
1521:          ////////////////////////////////////////////////////////////////////////////
1522:   
1523:          /// <summary>
1524:          ///
1525:          /// </summary>
1526:          public static List<Ia.Ngn.Cl.Model.ServiceRequest> ForRequestDate(DateTime requestDate)
1527:          {
1528:              // below:
1529:              DateTime nextDate;
1530:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
1531:   
1532:              serviceRequestList = null;
1533:   
1534:              // below: 00:00 time values
1535:              requestDate = requestDate.Date;
1536:   
1537:              nextDate = requestDate.AddDays(1);
1538:   
1539:              try
1540:              {
1541:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
1542:                  {
1543:                      serviceRequestList = (from sr in db.ServiceRequests where sr.RequestDateTime >= requestDate && sr.RequestDateTime < nextDate orderby sr.RequestDateTime descending select sr).ToList();
1544:                  }
1545:              }
1546:              catch (Exception)
1547:              {
1548:                  //resultLabel.Text = "Error during retrieval of data for \"" + ip + "\": " + ex.Message + ". ";
1549:                  //resultLabel.CssClass = "error";
1550:              }
1551:   
1552:              return serviceRequestList;
1553:          }
1554:   
1555:          ////////////////////////////////////////////////////////////////////////////
1556:          ////////////////////////////////////////////////////////////////////////////
1557:   
1558:          /// <summary>
1559:          ///
1560:          /// </summary>
1561:          public static Dictionary<string, string> ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary
1562:          {
1563:              get
1564:              {
1565:                  lock (objectLock)
1566:                  {
1567:                      if (serviceToAccessNameWithinAllowedToBeMigratedOltDictionary == null || serviceToAccessNameWithinAllowedToBeMigratedOltDictionary.Count == 0) serviceToAccessNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary;
1568:   
1569:                      return serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
1570:                  }
1571:              }
1572:          }
1573:   
1574:          ////////////////////////////////////////////////////////////////////////////
1575:   
1576:          /// <summary>
1577:          ///
1578:          /// </summary>
1579:          private static Dictionary<string, string> _ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary
1580:          {
1581:              get
1582:              {
1583:                  Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
1584:                  Dictionary<string, Ia.Ngn.Cl.Model.Business.ServiceAddress> serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary;
1585:   
1586:                  var areaSymbolList = Ia.Ngn.Cl.Model.Business.Service.AllowedToBeMigratedOltSymbolList;
1587:   
1588:                  var kuwaitNgnAreaList = (from k in Ia.Ngn.Cl.Model.Data.Service.KuwaitNgnAreaList where areaSymbolList.Contains(k.Symbol) select k.Id).ToList();
1589:   
1590:                  var serviceToDbNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToDbNameWithinAllowedToBeMigratedOltDictionary;
1591:                  var serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToCustomerAddressWithinAllowedToBeMigratedOltDictionary;
1592:   
1593:                  serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, Ia.Ngn.Cl.Model.Business.ServiceAddress>(serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary.Count);
1594:   
1595:                  foreach (KeyValuePair<string, string> kvp in serviceToCustomerAddressWithinAllowedToBeMigratedOltDictionary)
1596:                  {
1597:                      var service = kvp.Key;
1598:                      var address = kvp.Value;
1599:                      serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(service, address, out string level);
1600:   
1601:                      serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary[service] = serviceAddress;
1602:                  }
1603:   
1604:                  serviceToAccessNameWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, string>(serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary.Count);
1605:                  var ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary;
1606:   
1607:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
1608:                  {
1609:                      var accessList = (from a in db.Accesses
1610:                                        where kuwaitNgnAreaList.Contains(a.AreaId)
1611:                                        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();
1612:   
1613:                      foreach (var access in accessList)
1614:                      {
1615:                          if (ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary.ContainsKey(access.Id))
1616:                          {
1617:                              access.Name = ontAccessIdToOntAccessNameWithinAllowedToBeMigratedOltDictionary[access.Id];
1618:                          }
1619:                          else access.Name = string.Empty;
1620:                      }
1621:   
1622:                      foreach (var kvp in serviceToServiceAddressWithinAllowedToBeMigratedOltDictionary)
1623:                      {
1624:                          serviceAddress = new Ia.Ngn.Cl.Model.Business.ServiceAddress();
1625:   
1626:                          serviceAddress.AreaId = kvp.Value.AreaId;
1627:                          serviceAddress.Block = kvp.Value.Block;
1628:                          serviceAddress.Street = kvp.Value.Street;
1629:                          serviceAddress.Boulevard = kvp.Value.Boulevard;
1630:                          serviceAddress.PremisesOld = kvp.Value.PremisesOld;
1631:                          serviceAddress.PremisesNew = kvp.Value.PremisesNew;
1632:                          serviceAddress.Paci = kvp.Value.Paci;
1633:   
1634:                          var statisticalAccess = Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList, out string note2);
1635:                          if (statisticalAccess != null) statisticalAccess.Note = note2;
1636:   
1637:                          serviceToAccessNameWithinAllowedToBeMigratedOltDictionary[kvp.Key] = statisticalAccess != null ? statisticalAccess.Name : string.Empty;
1638:                      }
1639:                  }
1640:   
1641:                  return serviceToAccessNameWithinAllowedToBeMigratedOltDictionary;
1642:              }
1643:          }
1644:   
1645:          ////////////////////////////////////////////////////////////////////////////
1646:   
1647:          /// <summary>
1648:          ///
1649:          /// </summary>
1650:          public static Dictionary<string, List<string>> AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary
1651:          {
1652:              get
1653:              {
1654:                  lock (objectLock)
1655:                  {
1656:                      if (accessNameToServiceListWithinAllowedToBeMigratedOltDictionary == null || accessNameToServiceListWithinAllowedToBeMigratedOltDictionary.Count == 0) accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary(1440);
1657:   
1658:                      return accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
1659:                  }
1660:              }
1661:          }
1662:   
1663:          ////////////////////////////////////////////////////////////////////////////
1664:   
1665:          /// <summary>
1666:          ///
1667:          /// </summary>
1668:          private static Dictionary<string, List<string>> _AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary(int minutesToKeepDataValid)
1669:          {
1670:              string title;
1671:              DateTime now, timestamp;
1672:   
1673:              now = DateTime.UtcNow.AddHours(3);
1674:   
1675:              title = "AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary";
1676:   
1677:              timestamp = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<DateTime>(title + "Timestamp");
1678:   
1679:              if (now.AddMinutes(-minutesToKeepDataValid) > timestamp)
1680:              {
1681:                  accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = _AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary();
1682:                  Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title, accessNameToServiceListWithinAllowedToBeMigratedOltDictionary);
1683:   
1684:                  timestamp = now;
1685:                  Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title + "Timestamp", timestamp);
1686:              }
1687:              else
1688:              {
1689:                  accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<Dictionary<string, List<string>>>(title);
1690:              }
1691:   
1692:              return accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
1693:          }
1694:   
1695:          ////////////////////////////////////////////////////////////////////////////
1696:   
1697:          /// <summary>
1698:          ///
1699:          /// </summary>
1700:          private static Dictionary<string, List<string>> _AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary()
1701:          {
1702:              string key, value;
1703:              List<string> valueList;
1704:   
1705:              var serviceToAccessNameDictionary = ServiceToAccessNameWithinAllowedToBeMigratedOltDictionary;
1706:   
1707:              accessNameToServiceListWithinAllowedToBeMigratedOltDictionary = new Dictionary<string, List<string>>(serviceToAccessNameDictionary.Count);
1708:   
1709:              foreach (var kvp in serviceToAccessNameDictionary)
1710:              {
1711:                  key = kvp.Value;
1712:   
1713:                  if (!string.IsNullOrEmpty(key))
1714:                  {
1715:                      value = kvp.Key;
1716:   
1717:                      valueList = new List<string>();
1718:   
1719:                      if (!accessNameToServiceListWithinAllowedToBeMigratedOltDictionary.ContainsKey(key))
1720:                      {
1721:                          valueList.Add(value);
1722:   
1723:                          accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key] = valueList;
1724:                      }
1725:                      else
1726:                      {
1727:                          valueList = accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key];
1728:                          valueList.Add(value);
1729:   
1730:                          accessNameToServiceListWithinAllowedToBeMigratedOltDictionary[key] = valueList;
1731:                      }
1732:                  }
1733:              }
1734:   
1735:              return accessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
1736:          }
1737:   
1738:          ////////////////////////////////////////////////////////////////////////////
1739:   
1740:          /// <summary>
1741:          ///
1742:          /// </summary>
1743:          public static List<string> DbPeerServiceList(List<string> serviceList)
1744:          {
1745:              var dbNameToServiceListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.DbNameToServiceListDictionary;
1746:   
1747:              var listList = (from d in dbNameToServiceListDictionary where serviceList.Any(u => d.Value.Contains(u)) select d.Value).ToList();
1748:   
1749:              var list = listList.SelectMany(x => x).ToList();
1750:   
1751:              var peerServiceList = list.Distinct().ToList();
1752:   
1753:              return peerServiceList;
1754:          }
1755:   
1756:          ////////////////////////////////////////////////////////////////////////////
1757:   
1758:          /// <summary>
1759:          ///
1760:          /// </summary>
1761:          public static Dictionary<string, List<string>> DbNameToServiceListDictionary
1762:          {
1763:              get
1764:              {
1765:                  lock (objectLock)
1766:                  {
1767:                      if (dbNameToServiceListDictionary == null || dbNameToServiceListDictionary.Count == 0) dbNameToServiceListDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest._DbNameToServiceListDictionary;
1768:   
1769:                      return dbNameToServiceListDictionary;
1770:                  }
1771:              }
1772:          }
1773:   
1774:          ////////////////////////////////////////////////////////////////////////////
1775:   
1776:          /// <summary>
1777:          ///
1778:          /// </summary>
1779:          private static Dictionary<string, List<string>> _DbNameToServiceListDictionary
1780:          {
1781:              get
1782:              {
1783:                  string key, value;
1784:                  List<string> valueList;
1785:   
1786:                  var serviceToDbNameDictionary = ServiceToDbNameWithinAllowedToBeMigratedOltDictionary;
1787:   
1788:                  dbNameToServiceListDictionary = new Dictionary<string, List<string>>(serviceToDbNameDictionary.Count);
1789:   
1790:                  foreach (KeyValuePair<string, string> kvp in serviceToDbNameDictionary)
1791:                  {
1792:                      key = kvp.Value;
1793:   
1794:                      if (!string.IsNullOrEmpty(key) && key != ":")
1795:                      {
1796:                          value = kvp.Key;
1797:   
1798:                          valueList = new List<string>();
1799:   
1800:                          if (!dbNameToServiceListDictionary.ContainsKey(key))
1801:                          {
1802:                              valueList.Add(value);
1803:   
1804:                              dbNameToServiceListDictionary[key] = valueList;
1805:                          }
1806:                          else
1807:                          {
1808:                              valueList = dbNameToServiceListDictionary[key];
1809:                              valueList.Add(value);
1810:   
1811:                              dbNameToServiceListDictionary[key] = valueList;
1812:                          }
1813:                      }
1814:                  }
1815:   
1816:                  return dbNameToServiceListDictionary;
1817:              }
1818:          }
1819:   
1820:          ////////////////////////////////////////////////////////////////////////////
1821:   
1822:          /// <summary>
1823:          ///
1824:          /// </summary>
1825:          public static Dictionary<string, string> ServiceToDbNameWithinAllowedToBeMigratedOltDictionary
1826:          {
1827:              get
1828:              {
1829:                  lock (objectLock)
1830:                  {
1831:                      if (serviceToDbNameDictionary == null || serviceToDbNameDictionary.Count == 0)
1832:                      {
1833:                          Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(3600, out serviceToDbNameDictionary, out serviceToCustomerAddressDictionary);
1834:                      }
1835:   
1836:                      return serviceToDbNameDictionary;
1837:                  }
1838:              }
1839:          }
1840:   
1841:          ////////////////////////////////////////////////////////////////////////////
1842:   
1843:          /// <summary>
1844:          ///
1845:          /// </summary>
1846:          public static Dictionary<string, string> ServiceToCustomerAddressWithinAllowedToBeMigratedOltDictionary
1847:          {
1848:              get
1849:              {
1850:                  lock (objectLock)
1851:                  {
1852:                      if (serviceToCustomerAddressDictionary == null || serviceToCustomerAddressDictionary.Count == 0)
1853:                      {
1854:                          Ia.Ngn.Cl.Model.Data.ServiceRequest._ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(3600, out serviceToDbNameDictionary, out serviceToCustomerAddressDictionary);
1855:                      }
1856:   
1857:                      return serviceToCustomerAddressDictionary;
1858:                  }
1859:              }
1860:          }
1861:   
1862:          ////////////////////////////////////////////////////////////////////////////
1863:   
1864:          /// <summary>
1865:          ///
1866:          /// </summary>
1867:          private static void _ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(int minutesToKeepDataValid, out Dictionary<string, string> serviceToDbNameDictionary, out Dictionary<string, string> serviceToCustomerAddressDictionary)
1868:          {
1869:              string title, title1, title2;
1870:              DateTime now, timestamp;
1871:   
1872:              now = DateTime.UtcNow.AddHours(3);
1873:   
1874:              title1 = "ServiceToDbNameDictionary";
1875:              title2 = "ServiceToCustomerAddressDictionary";
1876:              title = title1 + "And" + title2;
1877:   
1878:              timestamp = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<DateTime>(title + "Timestamp");
1879:   
1880:              if (now.AddMinutes(-minutesToKeepDataValid) > timestamp)
1881:              {
1882:                  _ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(out serviceToDbNameDictionary, out serviceToCustomerAddressDictionary);
1883:   
1884:                  Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title1, serviceToDbNameDictionary);
1885:   
1886:                  Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title2, serviceToCustomerAddressDictionary);
1887:   
1888:                  timestamp = now;
1889:                  Ia.Ngn.Cl.Model.Data.Miscellaneous.CreateOrUpdate(title + "Timestamp", timestamp);
1890:              }
1891:              else
1892:              {
1893:                  serviceToDbNameDictionary = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<Dictionary<string, string>>(title1);
1894:   
1895:                  serviceToCustomerAddressDictionary = Ia.Ngn.Cl.Model.Data.Miscellaneous.Read<Dictionary<string, string>>(title2);
1896:              }
1897:          }
1898:   
1899:          ////////////////////////////////////////////////////////////////////////////
1900:   
1901:          /// <summary>
1902:          ///
1903:          /// </summary>
1904:          private static void _ServiceToDbNameDictionaryAndServiceToCustomerAddressDictionary(out Dictionary<string, string> serviceToDbNameDictionary, out Dictionary<string, string> serviceToCustomerAddressDictionary)
1905:          {
1906:              Dictionary<string, int> serviceToLastSerialDictionary;
1907:   
1908:              var areaSymbolList = Ia.Ngn.Cl.Model.Business.Service.AllowedToBeMigratedOltSymbolList;
1909:   
1910:              var areaIdList = (from k in Ia.Ngn.Cl.Model.Data.Service.KuwaitNgnAreaList where areaSymbolList.Contains(k.Symbol) select k.Id).ToList();
1911:   
1912:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1913:              {
1914:                  var statisticalVariableList = (from sr in db.ServiceRequests
1915:                                                     //join srs in db.ServiceRequestServices on sr.Number.ToString() equals srs.Service // on sr.ServiceRequestService.Id equals srs.Id
1916:                                                 join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id into srts
1917:                                                 from srt in srts.DefaultIfEmpty()
1918:                                                 where areaIdList.Contains(sr.AreaId) && (srt == null || srt.TypeId == 1 || srt.TypeId == 2)
1919:                                                 select new Ia.Ngn.Cl.Model.Business.ServiceRequestStatisticalVariable
1920:                                                 {
1921:                                                     //Provisioned = srs.Provisioned,
1922:                                                     ServiceRequestId = sr.Id,
1923:                                                     Service = sr.Number.ToString(),
1924:                                                     Serial = sr.Serial,
1925:                                                     AreaId = sr.AreaId,
1926:                                                     CustomerAddress = sr.CustomerAddress,
1927:                                                     /* CustomerName = sr.CustomerName, */
1928:                                                     ServiceRequestTypeValue = srt == null ? string.Empty : srt.Value,
1929:                                                 }).ToList();
1930:   
1931:                  serviceToLastSerialDictionary = new Dictionary<string, int>(statisticalVariableList.Count);
1932:                  serviceToDbNameDictionary = new Dictionary<string, string>(statisticalVariableList.Count);
1933:                  serviceToCustomerAddressDictionary = new Dictionary<string, string>(statisticalVariableList.Count);
1934:   
1935:                  foreach (var sv in statisticalVariableList/*.Where(u => u.Provisioned)*/.OrderBy(u => u.ServiceRequestId))
1936:                  {
1937:                      serviceToLastSerialDictionary[sv.Service] = sv.Serial;
1938:                  }
1939:   
1940:                  foreach (var sv in statisticalVariableList/*.Where(u => u.Provisioned)*/.OrderBy(u => u.ServiceRequestId))
1941:                  {
1942:                      if (serviceToLastSerialDictionary[sv.Service] == sv.Serial)
1943:                      {
1944:                          if (serviceToDbNameDictionary.ContainsKey(sv.Service))
1945:                          {
1946:                              // ServiceRequestTypes values do not contain ":"
1947:                              if (!serviceToDbNameDictionary[sv.Service].Contains(":")) serviceToDbNameDictionary[sv.Service] += ":" + sv.ServiceRequestTypeValue;
1948:                          }
1949:                          else serviceToDbNameDictionary[sv.Service] = sv.ServiceRequestTypeValue;
1950:   
1951:                          if (!serviceToCustomerAddressDictionary.ContainsKey(sv.Service)) serviceToCustomerAddressDictionary[sv.Service] = sv.CustomerAddress;
1952:                      }
1953:                  }
1954:              }
1955:          }
1956:   
1957:          ////////////////////////////////////////////////////////////////////////////
1958:          ////////////////////////////////////////////////////////////////////////////
1959:   
1960:          /// <summary>
1961:          ///
1962:          /// </summary>
1963:          public static string ToSimpleTextString(Ia.Ngn.Cl.Model.ServiceRequest serviceRequest)
1964:          {
1965:              StringBuilder sb;
1966:   
1967:              sb = new StringBuilder();
1968:   
1969:              // Id    Number    Serial    Status    RequestDateTime    Service    ServiceCategory    CustomerId    CustomerName    CustomerCategory    CustomerAddress    Balance
1970:              sb.AppendLine("Id: " + serviceRequest.Id);
1971:              sb.AppendLine("Number: " + serviceRequest.Number + "/" + serviceRequest.Serial);
1972:              //sb.AppendLine("Serial: " + serviceRequest.Serial);
1973:              sb.AppendLine("Status: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.StatusSortedList[serviceRequest.Status].ToString());
1974:              sb.AppendLine("RequestDateTime: " + serviceRequest.RequestDateTime.ToString("yyyy-MM-dd HH:mm"));
1975:              sb.AppendLine("Service: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceSortedList[serviceRequest.ServiceId].ToString());
1976:              //sb.AppendLine("ServiceCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceCategorySortedList[serviceRequest.ServiceCategoryId].ToString());
1977:              //sb.AppendLine("CustomerId: " + serviceRequest.CustomerId);
1978:              sb.AppendLine("CustomerName: " + serviceRequest.CustomerName);
1979:              //sb.AppendLine("CustomerCategory: " + Ia.Ngn.Cl.Model.Data.ServiceRequest.CustomerCategorySortedList[serviceRequest.CustomerCategoryId].ToString());
1980:   
1981:              sb.AppendLine("CustomerAddress: " + serviceRequest.CustomerAddress);
1982:              //sb.AppendLine("Balance: " + serviceRequest.Balance);
1983:   
1984:              return sb.ToString();
1985:          }
1986:   
1987:          ////////////////////////////////////////////////////////////////////////////
1988:   
1989:          /// <summary>
1990:          /// 
1991:          /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
1992:          /// 
1993:          /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
1994:          /// 2. Add "using System.Reflection".
1995:          /// 3. See sample below.
1996:          /// 
1997:          /// </summary>
1998:   
1999:          private static XDocument XDocument
2000:          {
2001:              get
2002:              {
2003:                  if (xDocument == null)
2004:                  {
2005:                      Assembly _assembly;
2006:                      StreamReader streamReader;
2007:   
2008:                      _assembly = Assembly.GetExecutingAssembly();
2009:                      streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.service-request.xml"));
2010:   
2011:                      try
2012:                      {
2013:                          if (streamReader.Peek() != -1)
2014:                          {
2015:                              xDocument = System.Xml.Linq.XDocument.Load(streamReader);
2016:                          }
2017:                      }
2018:                      catch (Exception)
2019:                      {
2020:                      }
2021:                      finally
2022:                      {
2023:                      }
2024:                  }
2025:   
2026:                  return xDocument;
2027:              }
2028:          }
2029:   
2030:          ////////////////////////////////////////////////////////////////////////////    
2031:          ////////////////////////////////////////////////////////////////////////////    
2032:      }
2033:   
2034:      ////////////////////////////////////////////////////////////////////////////
2035:      ////////////////////////////////////////////////////////////////////////////
2036:  }