Professional Applications Programmers/Consultants برمجة واستشارات تطبيقات الإنترنت
Skip Navigation LinksHome » Code Library » ServiceRequest

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

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

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