)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Home » Code Library » ServiceRequestType (Ia.Ftn.Cl.Models.Data)

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

Service Request Type support class for Fixed Telecommunications Network (FTN) data model.

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