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

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 Next Generation Network (NGN) data model.

   1:  using Ia.Ngn.Cl.Model.Business; // Needed for ServerExtension
   2:  using System;
   3:  using System.Collections;
   4:  using System.Collections.Generic;
   5:  using System.Data;
   6:  using System.Data.Entity;
   7:  using System.Linq;
   8:  using System.Text.RegularExpressions;
   9:   
  10:  namespace Ia.Ngn.Cl.Model.Data
  11:  {
  12:      ////////////////////////////////////////////////////////////////////////////
  13:   
  14:      /// <summary publish="true">
  15:      /// Service Request Type support class for Next Generation Network (NGN) data model.
  16:      /// </summary>
  17:      /// 
  18:      /// <remarks> 
  19:      /// Copyright © 2006-2019 Jasem Y. Al-Shamlan (info@ia.com.kw), Internet Applications - Kuwait. All Rights Reserved.
  20:      ///
  21:      /// 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
  22:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  23:      ///
  24:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  25:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  26:      /// 
  27:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  28:      /// 
  29:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  30:      /// </remarks> 
  31:      public partial class ServiceRequestType
  32:      {
  33:          /// <summary/>
  34:          public ServiceRequestType() { }
  35:   
  36:          ////////////////////////////////////////////////////////////////////////////
  37:   
  38:          /// <summary>
  39:          ///
  40:          /// </summary>
  41:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List()
  42:          {
  43:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
  44:   
  45:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
  46:              {
  47:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes select srt).ToList();
  48:              }
  49:   
  50:              return serviceRequestTypeList;
  51:          }
  52:   
  53:          ////////////////////////////////////////////////////////////////////////////
  54:   
  55:          /// <summary>
  56:          /// Return a list of service request types for service requests that has the same number as the one passed
  57:          /// </summary>
  58:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(int number)
  59:          {
  60:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
  61:   
  62:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
  63:              {
  64:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Number == number select srt).Include(x => x.ServiceRequest).ToList();
  65:              }
  66:   
  67:              return serviceRequestTypeList;
  68:          }
  69:   
  70:          ////////////////////////////////////////////////////////////////////////////
  71:   
  72:          /// <summary>
  73:          /// Return a list of service request types for service requests that has the same service as the one passed
  74:          /// </summary>
  75:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(string service)
  76:          {
  77:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
  78:   
  79:              if (!string.IsNullOrEmpty(service))
  80:              {
  81:                  if (int.TryParse(service, out int number))
  82:                  {
  83:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
  84:                      {
  85:                          serviceRequestTypeList = (from srt in db.ServiceRequestTypes where srt.ServiceRequest.Number == number select srt).Include(x => x.ServiceRequest).ToList();
  86:                      }
  87:                  }
  88:                  else
  89:                  {
  90:                      throw new ArgumentException(@"List(): service is not a number, service: " + service);
  91:                  }
  92:              }
  93:              else serviceRequestTypeList = new List<Ia.Ngn.Cl.Model.ServiceRequestType>();
  94:   
  95:              return serviceRequestTypeList;
  96:          }
  97:   
  98:          ////////////////////////////////////////////////////////////////////////////
  99:   
 100:          /// <summary>
 101:          ///
 102:          /// </summary>
 103:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> ReadListThatHasServiceRequestIdsWithinIdRange(int start, int end)
 104:          {
 105:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 106:   
 107:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 108:              {
 109:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 110:                                            join sr in db.ServiceRequests
 111:                                            on srt.ServiceRequest.Id equals sr.Id
 112:                                            where sr.Id >= start && sr.Id <= end
 113:                                            select srt).ToList();
 114:              }
 115:   
 116:              return serviceRequestTypeList;
 117:          }
 118:   
 119:          ////////////////////////////////////////////////////////////////////////////
 120:   
 121:          /// <summary>
 122:          ///
 123:          /// </summary>
 124:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> ReadListThatHaveServiceRequestsWithinGivenDateRange(DateTime startDateTime, DateTime endDateTime)
 125:          {
 126:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 127:   
 128:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 129:              {
 130:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 131:                                            join sr in db.ServiceRequests
 132:                                            on srt.ServiceRequest.Id equals sr.Id
 133:                                            where sr.RequestDateTime >= startDateTime && sr.RequestDateTime < endDateTime
 134:                                            select srt).ToList();
 135:              }
 136:   
 137:              return serviceRequestTypeList;
 138:          }
 139:   
 140:          ////////////////////////////////////////////////////////////////////////////
 141:   
 142:          /// <summary>
 143:          /// Return a list of service request types for service requests that have numbers within the passed number-serial list
 144:          /// </summary>
 145:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(List<Ia.Ngn.Cl.Model.Business.ServiceRequest.NumberSerial> numberSerialList)
 146:          {
 147:              List<long> idList;
 148:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 149:   
 150:              idList = numberSerialList.IdList();
 151:   
 152:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 153:              {
 154:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 155:                                            where
 156:                                            //numberSerialList.Contains(q.ServiceRequest.Number, q.ServiceRequest.Serial) 
 157:                                            idList.Contains((long)srt.ServiceRequest.Number * 100 + srt.ServiceRequest.Serial)
 158:                                            select srt).Include(x => x.ServiceRequest).ToList();
 159:              }
 160:   
 161:              return serviceRequestTypeList;
 162:          }
 163:   
 164:          ////////////////////////////////////////////////////////////////////////////
 165:   
 166:          /// <summary>
 167:          /// Return a list of service request types for service requests that have numbers (including changed-to numbers) within the passed number list
 168:          /// </summary>
 169:          public static List<Ia.Ngn.Cl.Model.ServiceRequestType> List(List<int> numberList)
 170:          {
 171:              List<string> serviceList;
 172:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 173:   
 174:              serviceList = (from n in numberList select n.ToString()).ToList();
 175:   
 176:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 177:              {
 178:                  serviceRequestTypeList = (from srt in db.ServiceRequestTypes
 179:                                            where numberList.Contains(srt.ServiceRequest.Number) || (srt.TypeId == 11 && serviceList.Contains(srt.Value))
 180:                                            select srt).Include(x => x.ServiceRequest).ToList();
 181:              }
 182:   
 183:              return serviceRequestTypeList;
 184:          }
 185:   
 186:          ////////////////////////////////////////////////////////////////////////////
 187:   
 188:          /// <summary>
 189:          ///
 190:          /// </summary>
 191:          public static void UpdateForServiceRequestIdRangeWithOutputDataTable(DataTable dataTable, Tuple<int, int> startEndRange, out bool isUpdated, out string result)
 192:          {
 193:              // below: the SQL statement should be within the dataTable.TableName variable
 194:              int serviceRequestId, serviceRequestTypeId, start, end, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
 195:              string sql, r;
 196:              ArrayList newServiceRequestTypeIdArryList;
 197:              Match match;
 198:              Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
 199:              List<int> serviceRequestTypeWithNoServiceRequestIdList;
 200:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 201:   
 202:              isUpdated = false;
 203:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 204:              result = r = "";
 205:              serviceRequestTypeWithNoServiceRequestIdList = new List<int>();
 206:   
 207:              if (dataTable != null)
 208:              {
 209:                  sql = dataTable.TableName;
 210:   
 211:                  // 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
 212:                  match = Regex.Match(sql, @"SRV_REQ_FIPER_TECH\.SRV_REQ_ID >= (\d+) and SRV_REQ_FIPER_TECH\.SRV_REQ_ID <= (\d+) ", RegexOptions.Singleline);
 213:   
 214:                  if (match.Success)
 215:                  {
 216:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 217:                      {
 218:                          readItemCount = dataTable.Rows.Count;
 219:   
 220:                          start = int.Parse(match.Groups[1].Value);
 221:                          end = int.Parse(match.Groups[2].Value);
 222:   
 223:                          serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.ReadListThatHasServiceRequestIdsWithinIdRange(start, end);
 224:                          existingItemCount = serviceRequestTypeList.Count;
 225:   
 226:                          newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
 227:   
 228:                          foreach (DataRow dataRow in dataTable.Rows)
 229:                          {
 230:                              serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
 231:                              serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + dataRow["TECH_TYPE_ID"].ToString().PadLeft(2, '0'));
 232:   
 233:                              newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
 234:   
 235:                              newServiceRequestType.Id = serviceRequestTypeId;
 236:   
 237:                              newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequestId select sr).SingleOrDefault();
 238:   
 239:                              // below: we will not add any type that does not have a service request
 240:                              if (newServiceRequestType.ServiceRequest != null)
 241:                              {
 242:                                  newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
 243:                                  newServiceRequestType.Value = dataRow["VAL"].ToString();
 244:   
 245:                                  FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
 246:   
 247:                                  serviceRequestType = (from srt in serviceRequestTypeList where srt.Id == newServiceRequestType.Id select srt).SingleOrDefault();
 248:   
 249:                                  if (serviceRequestType == null)
 250:                                  {
 251:                                      newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
 252:   
 253:                                      db.ServiceRequestTypes.Add(newServiceRequestType);
 254:   
 255:                                      insertedItemCount++;
 256:                                  }
 257:                                  else
 258:                                  {
 259:                                      // below: copy values from newServiceRequestType to serviceRequestType
 260:   
 261:                                      if (serviceRequestType.Update(newServiceRequestType))
 262:                                      {
 263:                                          db.ServiceRequestTypes.Attach(serviceRequestType);
 264:                                          db.Entry(serviceRequestType).State = System.Data.Entity.EntityState.Modified;
 265:   
 266:                                          updatedItemCount++;
 267:                                      }
 268:                                  }
 269:   
 270:                                  // below: this will enable the removal of SRT that don't have a valid SR
 271:                                  newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
 272:                              }
 273:                              else
 274:                              {
 275:                                  serviceRequestTypeWithNoServiceRequestIdList.Add(newServiceRequestType.Id);
 276:                              }
 277:                          }
 278:   
 279:                          /*
 280:                          if (serviceRequestTypeWithNoServiceRequestIdList.Count > 0)
 281:                          {
 282:                              r = "SRT with no SR: ";
 283:  
 284:                              foreach (int n in serviceRequestTypeWithNoServiceRequestIdList) r += n + ",";
 285:  
 286:                              r = r.Trim(',');
 287:                          }
 288:                          */
 289:   
 290:                          // below: this function will remove values that were not present in the reading
 291:                          if (serviceRequestTypeList.Count > 0)
 292:                          {
 293:                              foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
 294:                              {
 295:                                  if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
 296:                                  {
 297:                                      serviceRequestType = (from srt2 in db.ServiceRequestTypes where srt2.Id == srt.Id select srt2).SingleOrDefault();
 298:   
 299:                                      db.ServiceRequestTypes.Remove(serviceRequestType);
 300:   
 301:                                      deletedItemCount++;
 302:                                  }
 303:                              }
 304:                          }
 305:   
 306:                          db.SaveChanges();
 307:   
 308:                          if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
 309:                          else isUpdated = false;
 310:   
 311:                          result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
 312:                      }
 313:                  }
 314:                  else
 315:                  {
 316:                      result = "(?/?/?: SQL in TableName is unmatched) ";
 317:                  }
 318:              }
 319:              else
 320:              {
 321:                  result = "(dataTable == null/?/?) ";
 322:              }
 323:          }
 324:   
 325:          ////////////////////////////////////////////////////////////////////////////
 326:   
 327:          /// <summary>
 328:          ///
 329:          /// </summary>
 330:          public static void UpdateForServiceRequestTypeWithOutputDataTableService(DataTable dataTable, string service, out bool isUpdated, out string result)
 331:          {
 332:              int serviceRequestId, serviceRequestTypeId, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
 333:              string sql, sqlService, r;
 334:              ArrayList newServiceRequestTypeIdArryList;
 335:              Match match;
 336:              Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
 337:              List<int> serviceRequestTypeWithNoServiceRequestIdList;
 338:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 339:   
 340:              isUpdated = false;
 341:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 342:              result = r = "";
 343:              serviceRequestTypeWithNoServiceRequestIdList = new List<int>();
 344:   
 345:              if (dataTable != null)
 346:              {
 347:                  // below: the SQL statement should be within the dataTable.TableName variable
 348:                  sql = dataTable.TableName;
 349:   
 350:                  /*
 351:  select SRV_REQ_FIPER_TECH.SRV_REQ_ID, SRV_REQ_FIPER_TECH.TECH_TYPE_ID, SRV_REQ_FIPER_TECH.VAL from SRV_REQ_FIPER 
 352:  left outer join SRV_REQ_FIPER_TECH on SRV_REQ_FIPER_TECH.SRV_REQ_ID = SRV_REQ_FIPER.SRV_REQ_ID 
 353:  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 
 354:  order by SRV_REQ_FIPER.SRV_REQ_ID asc
 355:  */
 356:                  match = Regex.Match(sql, @"SRV_REQ_FIPER.SRV_NO = (\d+) and SRV_REQ_FIPER_TECH.SRV_REQ_ID", RegexOptions.Singleline);
 357:   
 358:                  if (match.Success)
 359:                  {
 360:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 361:                      {
 362:                          readItemCount = dataTable.Rows.Count;
 363:   
 364:                          sqlService = match.Groups[1].Value;
 365:   
 366:                          if (service == sqlService)
 367:                          {
 368:                              serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.List(service);
 369:                              existingItemCount = serviceRequestTypeList.Count;
 370:   
 371:                              newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
 372:   
 373:                              foreach (DataRow dataRow in dataTable.Rows)
 374:                              {
 375:                                  serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
 376:                                  serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + dataRow["TECH_TYPE_ID"].ToString().PadLeft(2, '0'));
 377:   
 378:                                  newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
 379:   
 380:                                  newServiceRequestType.Id = serviceRequestTypeId;
 381:   
 382:                                  newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests.AsNoTracking() where sr.Id == serviceRequestId select sr).SingleOrDefault();
 383:                                  // 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.
 384:                                  // .AsNoTracking() see https://stackoverflow.com/questions/41376161/attaching-an-entity-of-type-x-failed-because-another-entity-of-the-same-type-a?rq=1
 385:                                  // and see https://stackoverflow.com/questions/18122723/asnotracking-using-linq-query-syntax-instead-of-method-syntax/18125658
 386:   
 387:                                  // below: we will not add any type that does not have a service request
 388:                                  if (newServiceRequestType.ServiceRequest != null)
 389:                                  {
 390:                                      newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
 391:                                      newServiceRequestType.Value = dataRow["VAL"].ToString();
 392:   
 393:                                      FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
 394:   
 395:                                      serviceRequestType = (from srt in serviceRequestTypeList where srt.Id == newServiceRequestType.Id select srt).SingleOrDefault();
 396:   
 397:                                      if (serviceRequestType == null)
 398:                                      {
 399:                                          newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
 400:   
 401:                                          db.ServiceRequestTypes.Add(newServiceRequestType);
 402:   
 403:                                          insertedItemCount++;
 404:                                      }
 405:                                      else
 406:                                      {
 407:                                          // below: copy values from newServiceRequestType to serviceRequestType
 408:   
 409:                                          if (serviceRequestType.Update(newServiceRequestType))
 410:                                          {
 411:                                              db.Entry(serviceRequestType).State = System.Data.Entity.EntityState.Modified;
 412:                                              db.ServiceRequestTypes.Attach(serviceRequestType);
 413:   
 414:                                              updatedItemCount++;
 415:                                          }
 416:                                      }
 417:   
 418:                                      // below: this will enable the removal of SRT that don't have a valid SR
 419:                                      newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
 420:                                  }
 421:                                  else
 422:                                  {
 423:                                      serviceRequestTypeWithNoServiceRequestIdList.Add(newServiceRequestType.Id);
 424:                                  }
 425:                              }
 426:   
 427:                              /*
 428:                              if (serviceRequestTypeWithNoServiceRequestIdList.Count > 0)
 429:                              {
 430:                                  r = "SRT with no SR: ";
 431:  
 432:                                  foreach (int n in serviceRequestTypeWithNoServiceRequestIdList) r += n + ",";
 433:  
 434:                                  r = r.Trim(',');
 435:                              }
 436:                              */
 437:   
 438:                              // below: this function will remove values that were not present in the reading
 439:                              if (serviceRequestTypeList.Count > 0)
 440:                              {
 441:                                  foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
 442:                                  {
 443:                                      if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
 444:                                      {
 445:                                          serviceRequestType = (from srt2 in db.ServiceRequestTypes where srt2.Id == srt.Id select srt2).SingleOrDefault();
 446:   
 447:                                          db.ServiceRequestTypes.Remove(serviceRequestType);
 448:   
 449:                                          deletedItemCount++;
 450:                                      }
 451:                                  }
 452:                              }
 453:   
 454:                              db.SaveChanges();
 455:   
 456:                              if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
 457:                              else isUpdated = false;
 458:   
 459:                              result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
 460:                          }
 461:                          else
 462:                          {
 463:                              throw new ArgumentException(@"UpdateForServiceRequestWithOutputDataTableService(): service != sqlService, service: " + service + ", sqlService: " + sqlService);
 464:                          }
 465:                      }
 466:                  }
 467:                  else
 468:                  {
 469:                      result = "(?/?/?: SQL in TableName is unmatched) ";
 470:                  }
 471:              }
 472:              else
 473:              {
 474:                  result = "(dataTable == null/?/?) ";
 475:              }
 476:          }
 477:   
 478:          ////////////////////////////////////////////////////////////////////////////
 479:   
 480:          /// <summary>
 481:          ///
 482:          /// </summary>
 483:          public static void UpdateForADateTimeRangeWithOutputDataTable(DataTable dataTable, Tuple<int, int> dateTime, out string result)
 484:          {
 485:              // below: the SQL statement should be within the dataTable.TableName variable
 486:              int readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
 487:              int serviceRequestId, serviceRequestTypeId;
 488:              string sql, r;
 489:              ArrayList newServiceRequestTypeIdArryList;
 490:              DateTime startDateTime, endDateTime;
 491:              Match match;
 492:              Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType, newServiceRequestType;
 493:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 494:   
 495:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 496:              result = r = "";
 497:   
 498:              startDateTime = endDateTime = DateTime.MinValue;
 499:   
 500:              if (dataTable != null)
 501:              {
 502:                  sql = dataTable.TableName;
 503:   
 504:                  // 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
 505:   
 506:                  match = Regex.Match(sql, @".+'(\d{2})\/(\d{2})\/(\d{4})'.+'(\d{2})\/(\d{2})\/(\d{4})'.+", RegexOptions.Singleline);
 507:                  //                             1        2        3        4          5        6
 508:   
 509:                  if (match.Success)
 510:                  {
 511:                      using (var db = new Ia.Ngn.Cl.Model.Ngn())
 512:                      {
 513:                          readItemCount = dataTable.Rows.Count;
 514:   
 515:                          //if (dataTable.Rows.Count > 0)
 516:                          //{
 517:                          startDateTime = DateTime.Parse(match.Groups[3].Value + "-" + match.Groups[2].Value + "-" + match.Groups[1].Value);
 518:                          endDateTime = DateTime.Parse(match.Groups[6].Value + "-" + match.Groups[5].Value + "-" + match.Groups[4].Value);
 519:   
 520:                          serviceRequestTypeList = Ia.Ngn.Cl.Model.Data.ServiceRequestType.ReadListThatHaveServiceRequestsWithinGivenDateRange(startDateTime, endDateTime);
 521:                          existingItemCount = serviceRequestTypeList.Count;
 522:   
 523:                          newServiceRequestTypeIdArryList = new ArrayList(dataTable.Rows.Count + 1);
 524:   
 525:                          foreach (DataRow dataRow in dataTable.Rows)
 526:                          {
 527:                              serviceRequestId = int.Parse(dataRow["SRV_REQ_ID"].ToString());
 528:                              serviceRequestTypeId = int.Parse(serviceRequestId.ToString() + dataRow["TECH_TYPE_ID"].ToString().PadLeft(2, '0'));
 529:   
 530:                              newServiceRequestType = new Ia.Ngn.Cl.Model.ServiceRequestType();
 531:   
 532:                              newServiceRequestType.Id = serviceRequestTypeId;
 533:   
 534:                              newServiceRequestType.ServiceRequest = (from sr in db.ServiceRequests where sr.Id == serviceRequestId select sr).SingleOrDefault();
 535:   
 536:                              // below: we will not add any type that does not have a service request
 537:                              if (newServiceRequestType.ServiceRequest != null)
 538:                              {
 539:                                  newServiceRequestType.TypeId = int.Parse(dataRow["TECH_TYPE_ID"].ToString());
 540:                                  newServiceRequestType.Value = dataRow["VAL"].ToString();
 541:   
 542:                                  FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref newServiceRequestType);
 543:   
 544:                                  serviceRequestType = (from srt in serviceRequestTypeList where srt.Id == newServiceRequestType.Id select srt).SingleOrDefault();
 545:   
 546:                                  if (serviceRequestType == null)
 547:                                  {
 548:                                      newServiceRequestType.Created = newServiceRequestType.Updated = DateTime.UtcNow.AddHours(3);
 549:   
 550:                                      db.ServiceRequestTypes.Add(newServiceRequestType);
 551:   
 552:                                      insertedItemCount++;
 553:                                  }
 554:                                  else
 555:                                  {
 556:                                      // below: copy values from newServiceRequestType to serviceRequestType
 557:   
 558:                                      if (serviceRequestType.Update(newServiceRequestType))
 559:                                      {
 560:                                          db.ServiceRequestTypes.Attach(serviceRequestType);
 561:                                          db.Entry(serviceRequestType).State = System.Data.Entity.EntityState.Modified;
 562:   
 563:                                          updatedItemCount++;
 564:                                      }
 565:                                  }
 566:   
 567:                                  // below: this will enable the removal of SRT that don't have a valid SR
 568:                                  newServiceRequestTypeIdArryList.Add(serviceRequestTypeId);
 569:                              }
 570:                              else
 571:                              {
 572:                                  r += "newServiceRequestType.Id: " + newServiceRequestType.Id + " newServiceRequestType.ServiceRequest == null, ";
 573:                              }
 574:                          }
 575:   
 576:                          // below: this function will remove values that were not present in the reading
 577:                          if (serviceRequestTypeList.Count > 0)
 578:                          {
 579:                              foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList)
 580:                              {
 581:                                  if (!newServiceRequestTypeIdArryList.Contains(srt.Id))
 582:                                  {
 583:                                      serviceRequestType = (from srt2 in db.ServiceRequestTypes where srt2.Id == srt.Id select srt2).SingleOrDefault();
 584:   
 585:                                      db.ServiceRequestTypes.Remove(srt);
 586:   
 587:                                      deletedItemCount++;
 588:                                  }
 589:                              }
 590:                          }
 591:   
 592:                          db.SaveChanges();
 593:   
 594:                          result = "(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") " + r;
 595:                          //}
 596:                          //else
 597:                          //{
 598:                          //    result = "(" + readItemCount + "/?/?) ";
 599:                          //}
 600:                      }
 601:                  }
 602:                  else
 603:                  {
 604:                      result = "(?/?/?: SQL in TableName is unmatched) ";
 605:                  }
 606:              }
 607:              else
 608:              {
 609:                  result = "(dataTable == null/?/?) ";
 610:              }
 611:          }
 612:   
 613:          ////////////////////////////////////////////////////////////////////////////    
 614:   
 615:          /// <summary>
 616:          ///
 617:          /// </summary>
 618:          private static void FixCommonMistakesAndCheckValidityOfServiceRequestTypeRecords(ref Ia.Ngn.Cl.Model.ServiceRequestType serviceRequestType)
 619:          {
 620:              // below: procedure to fix service request records from the common mistakes
 621:   
 622:              bool b;
 623:              int number;
 624:   
 625:              // below: convert 7 digit numbers to 8 digits
 626:              // <type id="11" name="dn" arabicName="dn" oracleFieldName="الرقم الجديد"/>
 627:              if (serviceRequestType.TypeId == 11)
 628:              {
 629:                  b = int.TryParse(serviceRequestType.Value.Trim(), out number);
 630:   
 631:                  if (b)
 632:                  {
 633:                      number = Ia.Ngn.Cl.Model.Business.Default.ChangeOldSevenDigitNumbersToEightDigitFormat(number);
 634:   
 635:                      if (Ia.Ngn.Cl.Model.Business.Service.NumberIsWithinAllowedDomainList(number))
 636:                      {
 637:                          serviceRequestType.Value = number.ToString();
 638:                      }
 639:                      else serviceRequestType.Value = null;
 640:                  }
 641:                  else serviceRequestType.Value = null;
 642:              }
 643:          }
 644:   
 645:          ////////////////////////////////////////////////////////////////////////////
 646:   
 647:          /// <summary>
 648:          ///
 649:          /// </summary>
 650:          public static Dictionary<int, string> NumberToServiceRequestTypeStringDictionary(List<int> domainList)
 651:          {
 652:              int number;
 653:              Dictionary<int, string> dictionary;
 654:              List<Ia.Ngn.Cl.Model.ServiceRequestType> serviceRequestTypeList;
 655:   
 656:              dictionary = new Dictionary<int, string>();
 657:   
 658:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 659:              {
 660:                  if (domainList != null)
 661:                  {
 662:                      serviceRequestTypeList = (from srt in db.ServiceRequestTypes where domainList.Contains(srt.ServiceRequest.Number / 10000) || domainList.Contains(srt.ServiceRequest.Number / 1000) select srt).ToList();
 663:   
 664:                      if (serviceRequestTypeList != null)
 665:                      {
 666:                          dictionary = new Dictionary<int, string>(serviceRequestTypeList.Count);
 667:   
 668:                          foreach (Ia.Ngn.Cl.Model.ServiceRequestType srt in serviceRequestTypeList.OrderBy(u => u.Id))
 669:                          {
 670:                              number = int.Parse(srt.ServiceRequest.Number.ToString());
 671:   
 672:                              if (dictionary.ContainsKey(number)) dictionary[number] = dictionary[number] + "," + srt.Value;
 673:                              else dictionary[number] = srt.Value;
 674:                          }
 675:                      }
 676:                  }
 677:              }
 678:   
 679:              return dictionary;
 680:          }
 681:   
 682:          ////////////////////////////////////////////////////////////////////////////    
 683:          ////////////////////////////////////////////////////////////////////////////    
 684:      }
 685:   
 686:      ////////////////////////////////////////////////////////////////////////////
 687:      ////////////////////////////////////////////////////////////////////////////
 688:  }