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