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

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » ServiceRequestType

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

Service Request Type support class for Optical Fiber Network (OFN) data model.

   1:  using Ia.Ngn.Cl.Model.Business; // Needed for ServerExtension
   2:  using Microsoft.EntityFrameworkCore;
   3:  using System;
   4:  using System.Collections;
   5:  using System.Collections.Generic;
   6:  using System.Data;
   7:  using System.IO;
   8:  using System.Linq;
   9:  using System.Reflection;
  10:  using System.Text.RegularExpressions;
  11:  using System.Xml.Linq;
  12:   
  13:  namespace Ia.Ngn.Cl.Model.Data
  14:  {
  15:      ////////////////////////////////////////////////////////////////////////////
  16:   
  17:      /// <summary publish="true">
  18:      /// Service Request Type support class for Optical Fiber Network (OFN) data model.
  19:      /// </summary>
  20:      /// 
  21:      /// <remarks> 
  22:      /// Copyright © 2006-2022 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
  23:      ///
  24:      /// 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
  25:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  26:      ///
  27:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  28:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  29:      /// 
  30:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  31:      /// 
  32:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  33:      /// </remarks> 
  34:      public class ServiceRequestType
  35:      {
  36:          private static XDocument xDocument;
  37:          private static SortedList serviceRequestTypeTypeList;
  38:   
  39:          private static readonly object objectLock = new object();
  40:   
  41:          /// <summary/>
  42:          public ServiceRequestType() { }
  43:   
  44:          ////////////////////////////////////////////////////////////////////////////
  45:   
  46:          /// <summary>
  47:          ///
  48:          /// </summary>
  49:          public static SortedList ServiceRequestTypeTypeList
  50:          {
  51:              get
  52:              {
  53:                  if (serviceRequestTypeTypeList == null || serviceRequestTypeTypeList.Count == 0)
  54:                  {
  55:                      lock (objectLock)
  56:                      {
  57:                          serviceRequestTypeTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType._ServiceRequestTypeTypeList;
  58:                      }
  59:                  }
  60:   
  61:                  return serviceRequestTypeTypeList;
  62:              }
  63:          }
  64:   
  65:          ////////////////////////////////////////////////////////////////////////////
  66:   
  67:          /// <summary>
  68:          ///
  69:          /// </summary>
  70:          private static SortedList _ServiceRequestTypeTypeList
  71:          {
  72:              get
  73:              {
  74:                  int id;
  75:   
  76:                  serviceRequestTypeTypeList = new SortedList(100);
  77:   
  78:                  foreach (XElement x in XDocument.Element("serviceRequest").Elements("serviceRequestType").Elements("typeList").Elements("type"))
  79:                  {
  80:                      id = int.Parse(x.Attribute("id").Value);
  81:   
  82:                      serviceRequestTypeTypeList[id] = x.Attribute("oracleFieldName").Value;
  83:                  }
  84:   
  85:                  return serviceRequestTypeTypeList;
  86:              }
  87:          }
  88:   
  89:          ////////////////////////////////////////////////////////////////////////////
  90:   
  91:          /// <summary>
  92:          ///
  93:          /// </summary>
  94:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List()
  95:          {
  96:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
  97:   
  98:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
  99:              {
 100:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes select srt).ToList();
 101:              }
 102:   
 103:              return serviceRequestTypeList;
 104:          }
 105:   
 106:          ////////////////////////////////////////////////////////////////////////////
 107:   
 108:          /// <summary>
 109:          ///
 110:          /// </summary>
 111:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(int number)
 112:          {
 113:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 114:   
 115:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 116:              {
 117:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 118:                                            where srt.ServiceRequest.Number == number
 119:                                            select srt).Include(u => u.ServiceRequest).ToList();
 120:              }
 121:   
 122:              return serviceRequestTypeList;
 123:          }
 124:   
 125:          ////////////////////////////////////////////////////////////////////////////
 126:   
 127:          /// <summary>
 128:          ///
 129:          /// </summary>
 130:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> ListByServiceRequestId(int serviceRequestId)
 131:          {
 132:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 133:   
 134:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 135:              {
 136:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 137:                                            where srt.ServiceRequest.Id == serviceRequestId
 138:                                            select srt).AsNoTracking().ToList(); //.Include(u => u.ServiceRequest).ToList();
 139:              }
 140:   
 141:              return serviceRequestTypeList;
 142:          }
 143:   
 144:          ////////////////////////////////////////////////////////////////////////////
 145:   
 146:          /// <summary>
 147:          ///
 148:          /// </summary>
 149:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(string service)
 150:          {
 151:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 152:   
 153:              if (!string.IsNullOrEmpty(service))
 154:              {
 155:                  if (int.TryParse(service, out int number))
 156:                  {
 157:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 158:                      {
 159:                          serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 160:                                                    where srt.ServiceRequest.Number == number
 161:                                                    select srt).Include(u => u.ServiceRequest).ToList();
 162:                      }
 163:                  }
 164:                  else
 165:                  {
 166:                      throw new ArgumentException(@"List(): service is not a number, service: " + service);
 167:                  }
 168:              }
 169:              else serviceRequestTypeList = new List<Ia.Ngn.Cl.Model.ServiceRequestType>();
 170:   
 171:              return serviceRequestTypeList;
 172:          }
 173:   
 174:          ////////////////////////////////////////////////////////////////////////////
 175:   
 176:          /// <summary>
 177:          ///
 178:          /// </summary>
 179:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> ReadListThatHasServiceRequestIdsWithinIdRange(int start, int end)
 180:          {
 181:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 182:   
 183:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 184:              {
 185:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 186:                                            join sr in db.ServiceRequests
 187:                                            on srt.ServiceRequest.Id equals sr.Id
 188:                                            where sr.Id >= start && sr.Id <= end
 189:                                            select srt)/*.Include(u => u.ServiceRequest)*/.AsNoTracking().ToList();
 190:              }
 191:   
 192:              return serviceRequestTypeList;
 193:          }
 194:   
 195:          ////////////////////////////////////////////////////////////////////////////
 196:   
 197:          /// <summary>
 198:          ///
 199:          /// </summary>
 200:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> ReadListThatHaveServiceRequestsWithinGivenDateRange(DateTime startDateTime, DateTime endDateTime)
 201:          {
 202:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 203:   
 204:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 205:              {
 206:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 207:                                            join sr in db.ServiceRequests
 208:                                            on srt.ServiceRequest.Id equals sr.Id
 209:                                            where sr.RequestDateTime >= startDateTime && sr.RequestDateTime < endDateTime
 210:                                            select srt).ToList();
 211:              }
 212:   
 213:              return serviceRequestTypeList;
 214:          }
 215:   
 216:          ////////////////////////////////////////////////////////////////////////////
 217:   
 218:          /// <summary>
 219:          ///
 220:          /// </summary>
 221:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(List<Ia.Ngn.Cl.Model.Business.ServiceRequest.NumberSerial> numberSerialList)
 222:          {
 223:              List<long> idList;
 224:              List<Ia.Ngn.Cl.Model.ServiceRequestType> list;
 225:   
 226:              if (numberSerialList.Count > 0)
 227:              {
 228:                  idList = numberSerialList.IdList();
 229:   
 230:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 231:                  {
 232:                      list = (from srt in db.ServiceRequestTypes
 233:                              where
 234:                              //numberSerialList.Contains(q.ServiceRequest.Number, q.ServiceRequest.Serial) 
 235:                              idList.Contains((long)srt.ServiceRequest.Number * 100 + srt.ServiceRequest.Serial)
 236:                              select srt).Include(u => u.ServiceRequest).ToList();
 237:                  }
 238:              }
 239:              else list = new List<Ia.Ngn.Cl.Model.ServiceRequestType>();
 240:   
 241:              return list;
 242:          }
 243:   
 244:          ////////////////////////////////////////////////////////////////////////////
 245:   
 246:          /// <summary>
 247:          /// 
 248:          /// </summary>
 249:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(List<int> numberList)
 250:          {
 251:              List<string> serviceList;
 252:              List<Ia.Ngn.Cl.Model.ServiceRequestType> list;
 253:   
 254:              if (numberList.Count > 0)
 255:              {
 256:                  serviceList = (from n in numberList select n.ToString()).ToList();
 257:   
 258:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 259:                  {
 260:                      list = (from srt in db.ServiceRequestTypes
 261:                              where numberList.Contains(srt.ServiceRequest.Number) || (srt.TypeId == 11 && serviceList.Contains(srt.Value))
 262:                              select srt).Include(u => u.ServiceRequest).AsNoTracking().ToList();
 263:                  }
 264:              }
 265:              else list = new List<Ia.Ngn.Cl.Model.ServiceRequestType>();
 266:   
 267:              /*
 268:               * For the corrections of //.ToList() above see https://stackoverflow.com/questions/18086005/linq-to-entities-does-not-recognize-the-method-generic-listint-to-generic-ienu
 269:               */
 270:   
 271:              return list.Distinct().ToList();
 272:          }
 273:   
 274:          ////////////////////////////////////////////////////////////////////////////
 275:   
 276:          /// <summary>
 277:          ///
 278:          /// </summary>
 279:          public static string UpdateForServiceRequestIdRangeWithOutputDataTable(DataTable dataTable, out List<string> insertedOrUpdatedOrDeletedServiceList)
 280:          {
 281:              // below: the SQL statement should be within the dataTable.TableName variable
 282:              int serviceRequestId, serviceRequestTypeId, start, end, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
 283:              string sql, r, result;
 284:              ArrayList newServiceRequestTypeIdArryList;
 285:              Match match;
 286:              Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
 287:              List<int> serviceRequestTypeWithNoServiceRequestIdList;
 288:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 289:   
 290:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 291:              result = r = string.Empty;
 292:              serviceRequestTypeWithNoServiceRequestIdList = new List<int>();
 293:   
 294:              insertedOrUpdatedOrDeletedServiceList = new List<string>();
 295:   
 296:              if (dataTable != null)
 297:              {
 298:                  sql = dataTable.TableName;
 299:   
 300:                  // select SRV_REQ_FIPER_TECH.SRV_REQ_ID, SRV_REQ_FIPER_TECH.TECH_TYPE_ID, SRV_REQ_FIPER_TECH.VAL 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 SRV_REQ_FIPER_TECH.SRV_REQ_ID >= 110000 and SRV_REQ_FIPER_TECH.SRV_REQ_ID <= 321203 and SRV_REQ_FIPER_TECH.SRV_REQ_ID is not null and SRV_REQ_FIPER_TECH.TECH_TYPE_ID is not null and SRV_REQ_FIPER_TECH.VAL is not null order by REQ_DATE asc, SRV_REQ_FIPER.SRV_REQ_ID asc
 301:                  match = Regex.Match(sql, @"SRV_REQ_FIPER_TECH\.SRV_REQ_ID >= (\d+) and SRV_REQ_FIPER_TECH\.SRV_REQ_ID <= (\d+) ", RegexOptions.Singleline);
 302:   
 303:                  if (match.Success)
 304:                  {
 305:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 306:                      {
 307:                          readItemCount = dataTable.Rows.Count;
 308:   
 309:                          start = int.Parse(match.Groups[1].Value);
 310:                          end = int.Parse(match.Groups[2].Value);
 311:   
 312:                          serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.ReadListThatHasServiceRequestIdsWithinIdRange(start, end);
 313:                          existingItemCount = serviceRequestTypeList.Count;
 314:   
 315:                          newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
 316:   
 317:                          foreach (DataRow dataRow in dataTable.Rows)
 318:                          {
 319:                              serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
 320:   
 321:                              if (Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceRequestIdIsAllowedForProcessing(serviceRequestId))
 322:                              {
 323:                                  serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + dataRow["TECH_TYPE_ID"].ToString().PadLeft(2, '0'));
 324:   
 325:                                  newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
 326:   
 327:                                  newServiceRequestType.Id = serviceRequestTypeId;
 328:   
 329:                                  newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests
 330:                                                                          where sr.Id == serviceRequestId
 331:                                                                          select sr).SingleOrDefault();
 332:   
 333:                                  // below: we will not add any type that does not have a service request
 334:                                  if (newServiceRequestType.ServiceRequest != null)
 335:                                  {
 336:                                      newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
 337:                                      newServiceRequestType.Value = dataRow["VAL"].ToString();
 338:   
 339:                                      FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
 340:   
 341:                                      serviceRequestType = (from srt in serviceRequestTypeList
 342:                                                            where srt.Id == newServiceRequestType.Id
 343:                                                            select srt).SingleOrDefault();
 344:   
 345:                                      if (serviceRequestType == null)
 346:                                      {
 347:                                          newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
 348:   
 349:                                          db.ServiceRequestTypes.Add(newServiceRequestType);
 350:   
 351:                                          if (newServiceRequestType.ServiceRequest != null)
 352:                                          {
 353:                                              insertedOrUpdatedOrDeletedServiceList.Add(newServiceRequestType.ServiceRequest.Number.ToString());
 354:                                          }
 355:   
 356:                                          insertedItemCount++;
 357:                                      }
 358:                                      else
 359:                                      {
 360:                                          // below: copy values from newServiceRequestType to serviceRequestType
 361:   
 362:                                          if (serviceRequestType.Update(newServiceRequestType))
 363:                                          {
 364:                                              db.ServiceRequestTypes.Attach(serviceRequestType);
 365:                                              db.Entry(serviceRequestType).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
 366:   
 367:                                              if (serviceRequestType.ServiceRequest != null)
 368:                                              {
 369:                                                  insertedOrUpdatedOrDeletedServiceList.Add(serviceRequestType.ServiceRequest.Number.ToString());
 370:                                              }
 371:   
 372:                                              updatedItemCount++;
 373:                                          }
 374:                                      }
 375:   
 376:                                      // below: this will enable the removal of SRT that don't have a valid SR
 377:                                      newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
 378:                                  }
 379:                                  else
 380:                                  {
 381:                                      serviceRequestTypeWithNoServiceRequestIdList.Add(newServiceRequestType.Id);
 382:                                  }
 383:                              }
 384:                              else
 385:                              {
 386:   
 387:                              }
 388:                          }
 389:   
 390:                          /*
 391:                          if (serviceRequestTypeWithNoServiceRequestIdList.Count > 0)
 392:                          {
 393:                              r = "SRT with no SR: ";
 394:  
 395:                              foreach (int n in serviceRequestTypeWithNoServiceRequestIdList) r += n + ",";
 396:  
 397:                              r = r.Trim(',');
 398:                          }
 399:                          */
 400:   
 401:                          // below: this function will remove values that were not present in the reading
 402:                          if (serviceRequestTypeList.Count > 0)
 403:                          {
 404:                              foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
 405:                              {
 406:                                  if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
 407:                                  {
 408:                                      serviceRequestType = (from srt2 in db.ServiceRequestTypes
 409:                                                            where srt2.Id == srt.Id
 410:                                                            select srt2).SingleOrDefault();
 411:   
 412:                                      db.ServiceRequestTypes.Remove(serviceRequestType);
 413:   
 414:                                      insertedOrUpdatedOrDeletedServiceList.Add(serviceRequestType.ServiceRequest.Number.ToString());
 415:                                      deletedItemCount++;
 416:                                  }
 417:                              }
 418:                          }
 419:   
 420:                          db.SaveChanges();
 421:   
 422:                          //if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
 423:                          //else isUpdated = false;
 424:   
 425:                          result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
 426:                      }
 427:                  }
 428:                  else
 429:                  {
 430:                      result = "(?/?/?: SQL in TableName is unmatched) ";
 431:                  }
 432:              }
 433:              else
 434:              {
 435:                  result = "(dataTable == null/?/?) ";
 436:              }
 437:   
 438:              return result;
 439:          }
 440:   
 441:          ////////////////////////////////////////////////////////////////////////////
 442:   
 443:          /// <summary>
 444:          ///
 445:          /// </summary>
 446:          public static void UpdateForServiceRequestTypeWithOutputDataTableService(DataTable dataTable, string service, out bool isUpdated, out Ia.Cl.Model.Result result)
 447:          {
 448:              int serviceRequestId, serviceRequestTypeId, serviceRequestTypeTypeId, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
 449:              string sql, sqlService, exception;
 450:              ArrayList newServiceRequestTypeIdArryList;
 451:              Match match;
 452:              Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
 453:              List<int> serviceRequestTypeWithNoServiceRequestIdList;
 454:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 455:   
 456:              isUpdated = false;
 457:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 458:              result = new Ia.Cl.Model.Result();
 459:              exception = string.Empty;
 460:              serviceRequestTypeWithNoServiceRequestIdList = new List<int>();
 461:   
 462:              result = new Ia.Cl.Model.Result();
 463:   
 464:              if (dataTable != null)
 465:              {
 466:                  // below: the SQL statement should be within the dataTable.TableName variable
 467:                  sql = dataTable.TableName;
 468:   
 469:                  /*
 470:  select SRV_REQ_FIPER_TECH.SRV_REQ_ID, SRV_REQ_FIPER_TECH.TECH_TYPE_ID, SRV_REQ_FIPER_TECH.VAL from SRV_REQ_FIPER 
 471:  left outer join SRV_REQ_FIPER_TECH on SRV_REQ_FIPER_TECH.SRV_REQ_ID = SRV_REQ_FIPER.SRV_REQ_ID 
 472:  where SRV_REQ_FIPER.SRV_NO = " + service + @" and SRV_REQ_FIPER_TECH.SRV_REQ_ID is not null and SRV_REQ_FIPER_TECH.TECH_TYPE_ID is not null and SRV_REQ_FIPER_TECH.VAL is not null 
 473:  order by SRV_REQ_FIPER.SRV_REQ_ID asc
 474:  */
 475:                  match = Regex.Match(sql, @"SRV_REQ_FIPER.SRV_NO = (\d+) and SRV_REQ_FIPER_TECH.SRV_REQ_ID", RegexOptions.Singleline);
 476:   
 477:                  if (match.Success)
 478:                  {
 479:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 480:                      {
 481:                          readItemCount = dataTable.Rows.Count;
 482:   
 483:                          sqlService = match.Groups[1].Value;
 484:   
 485:                          if (service == sqlService)
 486:                          {
 487:                              serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.List(service);
 488:                              existingItemCount = serviceRequestTypeList.Count;
 489:   
 490:                              newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
 491:   
 492:                              foreach (DataRow dataRow in dataTable.Rows)
 493:                              {
 494:                                  serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
 495:                                  serviceRequestTypeTypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
 496:   
 497:                                  serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + serviceRequestTypeTypeId.ToString().PadLeft(2, '0'));
 498:   
 499:                                  if (Ia.Ngn.Cl.Model.Data.ServiceRequestType.ServiceRequestTypeTypeList.ContainsKey(serviceRequestTypeTypeId))
 500:                                  {
 501:                                      newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
 502:   
 503:                                      newServiceRequestType.Id = serviceRequestTypeId;
 504:   
 505:                                      newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests/*.AsNoTracking()*/ where sr.Id == serviceRequestId select sr).SingleOrDefault();
 506:                                      // System.InvalidOperationException: Attaching an entity of type 'Ia.Ngn.Cl.Model.ServiceRequest' failed because another entity of the same type already has the same primary key value.
 507:                                      // .AsNoTracking() see https://stackoverflow.com/questions/41376161/attaching-an-entity-of-type-x-failed-because-another-entity-of-the-same-type-a?rq=1
 508:                                      // and see https://stackoverflow.com/questions/18122723/asnotracking-using-linq-query-syntax-instead-of-method-syntax/18125658
 509:                                      // https://stackoverflow.com/questions/23201907/asp-net-mvc-attaching-an-entity-of-type-modelname-failed-because-another-ent
 510:   
 511:                                      try
 512:                                      {
 513:                                          // below: we will not add any type that does not have a service request
 514:                                          if (newServiceRequestType.ServiceRequest != null)
 515:                                          {
 516:                                              newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
 517:                                              newServiceRequestType.Value = dataRow["VAL"].ToString();
 518:   
 519:                                              FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
 520:   
 521:                                              serviceRequestType = (from srt in serviceRequestTypeList
 522:                                                                    where srt.Id == newServiceRequestType.Id
 523:                                                                    select srt).SingleOrDefault();
 524:   
 525:                                              if (serviceRequestType == null)
 526:                                              {
 527:                                                  newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
 528:   
 529:                                                  db.ServiceRequestTypes.Add(newServiceRequestType);
 530:   
 531:                                                  insertedItemCount++;
 532:                                              }
 533:                                              else
 534:                                              {
 535:                                                  // below: copy values from newServiceRequestType to serviceRequestType
 536:   
 537:                                                  if (serviceRequestType.Update(newServiceRequestType))
 538:                                                  {
 539:                                                      db.Entry(serviceRequestType).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
 540:                                                      db.ServiceRequestTypes.Attach(serviceRequestType);
 541:   
 542:                                                      updatedItemCount++;
 543:                                                  }
 544:                                              }
 545:   
 546:                                              // below: this will enable the removal of SRT that don't have a valid SR
 547:                                              newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
 548:                                          }
 549:                                          else
 550:                                          {
 551:                                              serviceRequestTypeWithNoServiceRequestIdList.Add(newServiceRequestType.Id);
 552:                                          }
 553:                                      }
 554:                                      catch (Exception ex)
 555:                                      {
 556:                                          exception += "Exception: serviceRequestId: " + serviceRequestId + ", exception:" + ex.ToString();
 557:                                      }
 558:                                  }
 559:                                  else result.AddError("Type " + serviceRequestTypeTypeId + " is undefined.");
 560:                              }
 561:   
 562:                              /*
 563:                              if (serviceRequestTypeWithNoServiceRequestIdList.Count > 0)
 564:                              {
 565:                                  r = "SRT with no SR: ";
 566:  
 567:                                  foreach (int n in serviceRequestTypeWithNoServiceRequestIdList) r += n + ",";
 568:  
 569:                                  r = r.Trim(',');
 570:                              }
 571:                              */
 572:   
 573:                              // below: this function will remove values that were not present in the reading
 574:                              if (serviceRequestTypeList.Count > 0)
 575:                              {
 576:                                  foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
 577:                                  {
 578:                                      if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
 579:                                      {
 580:                                          serviceRequestType = (from srt2 in db.ServiceRequestTypes where srt2.Id == srt.Id select srt2).SingleOrDefault();
 581:   
 582:                                          db.ServiceRequestTypes.Remove(serviceRequestType);
 583:   
 584:                                          deletedItemCount++;
 585:                                      }
 586:                                  }
 587:                              }
 588:   
 589:                              db.SaveChanges();
 590:   
 591:                              if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
 592:                              else isUpdated = false;
 593:   
 594:                              result.AddSuccess("(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") (" + exception + ")");
 595:                          }
 596:                          else
 597:                          {
 598:                              throw new ArgumentException(@"UpdateForServiceRequestWithOutputDataTableService(): service != sqlService, service: " + service + ", sqlService: " + sqlService);
 599:                          }
 600:                      }
 601:                  }
 602:                  else result.AddError("(?/?/?: SQL in TableName is unmatched)");
 603:              }
 604:              else result.AddError("(dataTable == null/?/?)"); ;
 605:          }
 606:   
 607:          ////////////////////////////////////////////////////////////////////////////
 608:   
 609:          /// <summary>
 610:          ///
 611:          /// </summary>
 612:          public static void UpdateForADateTimeRangeWithOutputDataTable(DataTable dataTable, Tuple<int, int> dateTime, out string result)
 613:          {
 614:              // below: the SQL statement should be within the dataTable.TableName variable
 615:              int readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
 616:              int serviceRequestId, serviceRequestTypeId;
 617:              string sql, r;
 618:              ArrayList newServiceRequestTypeIdArryList;
 619:              DateTime startDateTime, endDateTime;
 620:              Match match;
 621:              Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
 622:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 623:   
 624:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 625:              result = r = string.Empty;
 626:   
 627:              startDateTime = endDateTime = DateTime.MinValue;
 628:   
 629:              if (dataTable != null)
 630:              {
 631:                  sql = dataTable.TableName;
 632:   
 633:                  // 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 >= '06/01/2007' and REQ_DATE < '07/01/2007'  order by REQ_DATE asc, SRV_REQ_FIPER.SRV_REQ_ID asc
 634:   
 635:                  match = Regex.Match(sql, @".+'(\d{2})\/(\d{2})\/(\d{4})'.+'(\d{2})\/(\d{2})\/(\d{4})'.+", RegexOptions.Singleline);
 636:                  //                             1        2        3        4          5        6
 637:   
 638:                  if (match.Success)
 639:                  {
 640:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 641:                      {
 642:                          readItemCount = dataTable.Rows.Count;
 643:   
 644:                          //if (dataTable.Rows.Count > 0)
 645:                          //{
 646:                          startDateTime = DateTime.Parse(match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value);
 647:                          endDateTime = DateTime.Parse(match.Groups[6].Value + "-" + match.Groups[5].Value + "-" + match.Groups[4].Value);
 648:   
 649:                          serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.ReadListThatHaveServiceRequestsWithinGivenDateRange(startDateTime, endDateTime);
 650:                          existingItemCount = serviceRequestTypeList.Count;
 651:   
 652:                          newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
 653:   
 654:                          foreach (DataRow dataRow in dataTable.Rows)
 655:                          {
 656:                              serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
 657:                              serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + dataRow["TECH_TYPE_ID"].ToString().PadLeft(2, '0'));
 658:   
 659:                              newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
 660:   
 661:                              newServiceRequestType.Id = serviceRequestTypeId;
 662:   
 663:                              newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequestId select sr).SingleOrDefault();
 664:   
 665:                              // below: we will not add any type that does not have a service request
 666:                              if (newServiceRequestType.ServiceRequest != null)
 667:                              {
 668:                                  newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
 669:                                  newServiceRequestType.Value = dataRow["VAL"].ToString();
 670:   
 671:                                  FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
 672:   
 673:                                  serviceRequestType = (from srt in serviceRequestTypeList where srt.Id == newServiceRequestType.Id select srt).SingleOrDefault();
 674:   
 675:                                  if (serviceRequestType == null)
 676:                                  {
 677:                                      newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
 678:   
 679:                                      db.ServiceRequestTypes.Add(newServiceRequestType);
 680:   
 681:                                      insertedItemCount++;
 682:                                  }
 683:                                  else
 684:                                  {
 685:                                      // below: copy values from newServiceRequestType to serviceRequestType
 686:   
 687:                                      if (serviceRequestType.Update(newServiceRequestType))
 688:                                      {
 689:                                          db.ServiceRequestTypes.Attach(serviceRequestType);
 690:                                          db.Entry(serviceRequestType).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
 691:   
 692:                                          updatedItemCount++;
 693:                                      }
 694:                                  }
 695:   
 696:                                  // below: this will enable the removal of SRT that don't have a valid SR
 697:                                  newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
 698:                              }
 699:                              else
 700:                              {
 701:                                  r += "newServiceRequestType.Id: " + newServiceRequestType.Id + " newServiceRequestType.ServiceRequest == null, ";
 702:                              }
 703:                          }
 704:   
 705:                          // below: this function will remove values that were not present in the reading
 706:                          if (serviceRequestTypeList.Count > 0)
 707:                          {
 708:                              foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
 709:                              {
 710:                                  if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
 711:                                  {
 712:                                      serviceRequestType = (from srt2 in db.ServiceRequestTypes where srt2.Id == srt.Id select srt2).SingleOrDefault();
 713:   
 714:                                      db.ServiceRequestTypes.Remove(srt);
 715:   
 716:                                      deletedItemCount++;
 717:                                  }
 718:                              }
 719:                          }
 720:   
 721:                          db.SaveChanges();
 722:   
 723:                          result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
 724:                          //}
 725:                          //else
 726:                          //{
 727:                          //    result = "(" + readItemCount + "/?/?) ";
 728:                          //}
 729:                      }
 730:                  }
 731:                  else
 732:                  {
 733:                      result = "(?/?/?: SQL in TableName is unmatched) ";
 734:                  }
 735:              }
 736:              else
 737:              {
 738:                  result = "(dataTable == null/?/?) ";
 739:              }
 740:          }
 741:   
 742:          ////////////////////////////////////////////////////////////////////////////    
 743:   
 744:          /// <summary>
 745:          ///
 746:          /// </summary>
 747:          private static void FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType)
 748:          {
 749:              // below: procedure to fix service request records from the common mistakes
 750:   
 751:              bool b;
 752:              int number;
 753:   
 754:              // below: convert 7 digit numbers to 8 digits
 755:              // <type id="11" name="dn" arabicName="dn" oracleFieldName="الرقم الجديد"/>
 756:              if (serviceRequestType.TypeId == 11)
 757:              {
 758:                  b = int.TryParse(serviceRequestType.Value.Trim(), out number);
 759:   
 760:                  if (b)
 761:                  {
 762:                      number = Ia.Ngn.Cl.Model.Business.Default.ChangeOldSevenDigitNumbersToEightDigitFormat(number);
 763:   
 764:                      if (Ia.Ngn.Cl.Model.Business.Service.ServiceHasEightDigitsAndIsWithinAllowedDomainList(number))
 765:                      {
 766:                          serviceRequestType.Value = number.ToString();
 767:                      }
 768:                      else serviceRequestType.Value = null;
 769:                  }
 770:                  else serviceRequestType.Value = null;
 771:              }
 772:          }
 773:   
 774:          ////////////////////////////////////////////////////////////////////////////
 775:   
 776:          /// <summary>
 777:          ///
 778:          /// </summary>
 779:          public static Dictionary<int, string> NumberToServiceRequestTypeStringDictionary(List<int> domainList)
 780:          {
 781:              int number;
 782:              Dictionary<int, string> dictionary;
 783:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 784:   
 785:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 786:              {
 787:                  if (domainList.Count > 0)
 788:                  {
 789:                      serviceRequestTypeList = (from srt in db.ServiceRequestTypes where domainList.Contains(srt.ServiceRequest.Number / 10000) || domainList.Contains(srt.ServiceRequest.Number / 1000) select srt).ToList();
 790:   
 791:                      if (serviceRequestTypeList != null)
 792:                      {
 793:                          dictionary = new Dictionary<int, string>(serviceRequestTypeList.Count);
 794:   
 795:                          foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList.OrderBy(u => u.Id))
 796:                          {
 797:                              number = int.Parse(srt.ServiceRequest.Number.ToString());
 798:   
 799:                              if (dictionary.ContainsKey(number)) dictionary[number] = dictionary[number] + "," + srt.Value;
 800:                              else dictionary[number] = srt.Value;
 801:                          }
 802:                      }
 803:                      else dictionary = new Dictionary<int, string>();
 804:                  }
 805:                  else dictionary = new Dictionary<int, string>();
 806:              }
 807:   
 808:              return dictionary;
 809:          }
 810:   
 811:          ////////////////////////////////////////////////////////////////////////////
 812:   
 813:          /// <summary>
 814:          /// 
 815:          /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
 816:          /// 
 817:          /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
 818:          /// 2. Add "using System.Reflection".
 819:          /// 3. Manifest resource stream will start with the project namespace, the location of XML file.
 820:          /// 
 821:          /// </summary>
 822:   
 823:          private static XDocument XDocument
 824:          {
 825:              get
 826:              {
 827:                  if (xDocument == null)
 828:                  {
 829:                      lock (objectLock)
 830:                      {
 831:                          Assembly _assembly;
 832:                          StreamReader streamReader;
 833:   
 834:                          _assembly = Assembly.GetExecutingAssembly();
 835:                          streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.service-request.xml"));
 836:   
 837:                          try
 838:                          {
 839:                              if (streamReader.Peek() != -1)
 840:                              {
 841:                                  xDocument = System.Xml.Linq.XDocument.Load(streamReader);
 842:                              }
 843:                          }
 844:                          catch (Exception)
 845:                          {
 846:                          }
 847:                          finally
 848:                          {
 849:                          }
 850:                      }
 851:                  }
 852:   
 853:                  return xDocument;
 854:              }
 855:          }
 856:   
 857:          ////////////////////////////////////////////////////////////////////////////    
 858:          ////////////////////////////////////////////////////////////////////////////    
 859:      }
 860:   
 861:      ////////////////////////////////////////////////////////////////////////////
 862:      ////////////////////////////////////////////////////////////////////////////
 863:  }