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

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