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

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