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

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

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

    1: using Microsoft.EntityFrameworkCore;
    2: using System;
    3: using System.Collections.Generic;
    4: using System.Data;
    5: using System.Linq;
    6:  
    7: namespace Ia.Ftn.Cl.Models.Data
    8: {
    9:     ////////////////////////////////////////////////////////////////////////////
   10:  
   11:     /// <summary publish="true">
   12:     /// Service Request Ont support class for Fixed Telecommunications Network (FTN) data model.
   13:     /// </summary>
   14:     /// 
   15:     /// <remarks> 
   16:     /// Copyright © 2006-2018 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
   17:     /// </remarks> 
   18:     public class ServiceRequestOnt
   19:     {
   20:         private const int LengthOfRequestOntIdRange = 100;
   21:         private static Dictionary<string, string> ontAccessIdToOntAccessNameDictionary = new Dictionary<string, string>();
   22:         private static Queue<Ia.Ftn.Cl.Models.ServiceRequestOnt> serviceRequestOntNotInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ftn.Cl.Models.ServiceRequestOnt>();
   23:         private static Queue<Ia.Ftn.Cl.Models.ServiceRequestOnt> serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ftn.Cl.Models.ServiceRequestOnt>();
   24:         private static Queue<Tuple<string, string>> serviceRequestAccessIdOptimizedStartEndRangeTupleQueue = new Queue<Tuple<string, string>>();
   25:  
   26:         ////////////////////////////////////////////////////////////////////////////
   27:  
   28:         /// <summary>
   29:         ///
   30:         /// </summary>
   31:         public ServiceRequestOnt() { }
   32:  
   33:         ////////////////////////////////////////////////////////////////////////////    
   34:  
   35:         /// <summary>
   36:         ///
   37:         /// </summary>
   38:         public static bool ServiceRequestOntNotInCustomerDepartmentDatabaseQueueCountIsNotZero
   39:         {
   40:             get
   41:             {
   42:                 return serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Count > 0;
   43:             }
   44:         }
   45:  
   46:         ////////////////////////////////////////////////////////////////////////////
   47:  
   48:         /// <summary>
   49:         ///
   50:         /// </summary>
   51:         public static List<string> IdList
   52:         {
   53:             get
   54:             {
   55:                 List<string> list;
   56:  
   57:                 using (var db = new Ia.Ftn.Cl.Db())
   58:                 {
   59:                     list = (from a in db.ServiceRequestOnts select a.Id).ToList();
   60:                 }
   61:  
   62:                 return list;
   63:             }
   64:         }
   65:  
   66:         ////////////////////////////////////////////////////////////////////////////
   67:  
   68:         /// <summary>
   69:         ///
   70:         /// </summary>
   71:         public static List<Ia.Ftn.Cl.Models.ServiceRequestOnt> List
   72:         {
   73:             get
   74:             {
   75:                 List<Ia.Ftn.Cl.Models.ServiceRequestOnt> list;
   76:  
   77:                 using (var db = new Ia.Ftn.Cl.Db())
   78:                 {
   79:                     list = (from a in db.ServiceRequestOnts select a).ToList();
   80:                 }
   81:  
   82:                 return list;
   83:             }
   84:         }
   85:  
   86:         ////////////////////////////////////////////////////////////////////////////    
   87:         ////////////////////////////////////////////////////////////////////////////    
   88:  
   89:         /// <summary>
   90:         ///
   91:         /// </summary>
   92:         public static Ia.Ftn.Cl.Models.ServiceRequestOnt ServiceRequestOntNotInCustomerDepartmentDatabase(out Ia.Cl.Models.Result result)
   93:         {
   94:             Ia.Ftn.Cl.Models.ServiceRequestOnt serviceRequestOnt;
   95:             List<Ia.Ftn.Cl.Models.ServiceRequestOnt> serviceRequestOntList;
   96:             List<Ia.Ftn.Cl.Models.Access> accessList;
   97:  
   98:             result = new Ia.Cl.Models.Result();
   99:  
  100:             if (serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Count == 0)
  101:             {
  102:                 accessList = Ia.Ftn.Cl.Models.Data.Access.ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabase();
  103:  
  104:                 serviceRequestOntList = PrepareServiceRequestOntListFromAccessList(accessList);
  105:  
  106:                 serviceRequestOntNotInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ftn.Cl.Models.ServiceRequestOnt>(serviceRequestOntList);
  107:             }
  108:  
  109:             if (serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Count > 0)
  110:             {
  111:                 serviceRequestOnt = serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Dequeue();
  112:  
  113:                 result.AddSuccess("(" + serviceRequestOnt.Name + "/" + serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Count + ")");
  114:             }
  115:             else
  116:             {
  117:                 serviceRequestOnt = null;
  118:  
  119:                 result.AddSuccess("No missing records between FTN and billing database.");
  120:             }
  121:  
  122:             return serviceRequestOnt;
  123:         }
  124:  
  125:         ////////////////////////////////////////////////////////////////////////////    
  126:  
  127:         /// <summary>
  128:         ///
  129:         /// </summary>
  130:         public static List<Ia.Ftn.Cl.Models.ServiceRequestOnt> PrepareServiceRequestOntListFromAccessList(List<Ia.Ftn.Cl.Models.Access> accessList)
  131:         {
  132:             // Similar to Ia.Ftn.Cl.Model.Data.Access.AccessCapacityDictionary()
  133:  
  134:             bool familyExists;
  135:             int possibleNumberOfTd, possibleNumberOfEthernet;
  136:             string areaSymbol, accessOntFamilyTypeCapacityString;
  137:             Ia.Ftn.Cl.Models.Business.NetworkDesignDocument.Ont ont;
  138:             Ia.Ftn.Cl.Models.ServiceRequestOnt serviceRequestOnt;
  139:             List<Ia.Ftn.Cl.Models.ServiceRequestOnt> serviceRequestOntList;
  140:  
  141:             serviceRequestOntList = new List<Ia.Ftn.Cl.Models.ServiceRequestOnt>();
  142:  
  143:             accessList = accessList.OrderByDescending(a => a.Created).ToList();
  144:  
  145:             foreach (Ia.Ftn.Cl.Models.Access access in accessList)
  146:             {
  147:                 if (access.Onts != null && access.Onts.Count > 0)
  148:                 {
  149:                     if (access.Onts.First().FamilyType != Ia.Ftn.Cl.Models.Business.Nokia.Ont.FamilyType.Undefined)
  150:                     {
  151:                         accessOntFamilyTypeCapacityString = Ia.Ftn.Cl.Models.Data.Nokia.Ont.FamilyTypeStringFromId(access.Onts.FirstOrDefault().FamilyTypeId);
  152:  
  153:                         possibleNumberOfTd = Ia.Ftn.Cl.Models.Business.Nokia.Ont.PossibleNumberOfTdForOntFamilyType(access.Onts.FirstOrDefault().FamilyTypeId);
  154:                         possibleNumberOfEthernet = Ia.Ftn.Cl.Models.Business.Nokia.Ont.PossibleNumberOfHsiCardPortServiceConfigurationForOntFamilyType(access.Onts.FirstOrDefault().FamilyTypeId);
  155:  
  156:                         accessOntFamilyTypeCapacityString += " (" + possibleNumberOfTd + ")";
  157:  
  158:                         familyExists = true;
  159:                     }
  160:                     else
  161:                     {
  162:                         possibleNumberOfTd = 0;
  163:                         possibleNumberOfEthernet = 0;
  164:  
  165:                         accessOntFamilyTypeCapacityString = string.Empty;
  166:  
  167:                         familyExists = false;
  168:                     }
  169:                 }
  170:                 else if (access.EmsOnts != null && access.EmsOnts.Count > 0)
  171:                 {
  172:                     var familyType = access.EmsOnts.First().FamilyType;
  173:                     var equipmentType = access.EmsOnts.First().EquipmentType;
  174:  
  175:                     if (familyType != Ia.Ftn.Cl.Models.Business.Huawei.Ont.FamilyType.Undefined && equipmentType != null)
  176:                     {
  177:                         accessOntFamilyTypeCapacityString = familyType.ToString().ToUpper();
  178:  
  179:                         possibleNumberOfTd = equipmentType.TelPorts;
  180:                         possibleNumberOfEthernet = equipmentType.EthernetPorts;
  181:  
  182:                         accessOntFamilyTypeCapacityString += " (" + possibleNumberOfTd + ")";
  183:  
  184:                         familyExists = true;
  185:                     }
  186:                     else
  187:                     {
  188:                         possibleNumberOfTd = 0;
  189:                         possibleNumberOfEthernet = 0;
  190:  
  191:                         accessOntFamilyTypeCapacityString = string.Empty;
  192:  
  193:                         familyExists = false;
  194:                     }
  195:                 }
  196:                 else
  197:                 {
  198:                     possibleNumberOfTd = 0;
  199:                     possibleNumberOfEthernet = 0;
  200:  
  201:                     accessOntFamilyTypeCapacityString = string.Empty;
  202:  
  203:                     familyExists = false;
  204:                 }
  205:  
  206:                 if (familyExists)
  207:                 {
  208:                     if (possibleNumberOfTd > 0 && possibleNumberOfEthernet > 0)
  209:                     {
  210:                         ont = (from o in Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.OntList
  211:                                where o.Pon.PonGroup.Olt.Id == access.Olt && o.Pon.Number == access.Pon && o.Number == access.Ont
  212:                                select o).SingleOrDefault();
  213:  
  214:                         if (ont != null)
  215:                         {
  216:                             areaSymbol = ont.Pon.PonGroup.Symbol;
  217:  
  218:                             var serviceRequestOntId = Ia.Ftn.Cl.Models.Business.ServiceRequestOnt.ServiceRequestOntId(access.Id);
  219:  
  220:                             serviceRequestOnt = new Ia.Ftn.Cl.Models.ServiceRequestOnt()
  221:                             {
  222:                                 Id = serviceRequestOntId,
  223:                                 Pon = access.Pon,
  224:                                 Ont = access.Ont,
  225:                                 Paci = access.Paci,
  226:                                 AreaSymbol = areaSymbol,
  227:                                 Block = access.Block,
  228:                                 Street = access.Street,
  229:                                 PremisesOld = access.PremisesOld,
  230:                                 PremisesNew = access.PremisesNew,
  231:  
  232:                                 PossibleNumberOfTd = possibleNumberOfTd,
  233:                                 PossibleNumberOfEthernet = possibleNumberOfEthernet
  234:                             };
  235:  
  236:                             serviceRequestOntList.Add(serviceRequestOnt);
  237:                         }
  238:                         else
  239:                         {
  240:  
  241:                         }
  242:                     }
  243:                     else
  244:                     {
  245:  
  246:                     }
  247:                 }
  248:                 else
  249:                 {
  250:  
  251:                 }
  252:             }
  253:  
  254:             return serviceRequestOntList;
  255:         }
  256:  
  257:         ////////////////////////////////////////////////////////////////////////////    
  258:         ////////////////////////////////////////////////////////////////////////////    
  259:  
  260:         /// <summary>
  261:         ///
  262:         /// </summary>
  263:         public static Ia.Ftn.Cl.Models.ServiceRequestOnt ServiceRequestOntNotUpdatedInCustomerDepartmentDatabase(out Ia.Cl.Models.Result result)
  264:         {
  265:             Ia.Ftn.Cl.Models.ServiceRequestOnt serviceRequestOnt;
  266:             List<Ia.Ftn.Cl.Models.ServiceRequestOnt> serviceRequestOntList;
  267:             List<Ia.Ftn.Cl.Models.Access> accessList;
  268:  
  269:             result = new Ia.Cl.Models.Result();
  270:  
  271:             if (serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count == 0)
  272:             {
  273:                 accessList = Ia.Ftn.Cl.Models.Data.Access.AccessesWithProvisionedAndReadyOntsAndEmsOntsButMismatchedWithCustomerDepartmentDatabaseServiceRequestOntListList();
  274:  
  275:                 serviceRequestOntList = PrepareServiceRequestOntListFromAccessList(accessList);
  276:  
  277:                 serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ftn.Cl.Models.ServiceRequestOnt>(serviceRequestOntList);
  278:             }
  279:  
  280:             if (serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count > 0)
  281:             {
  282:                 serviceRequestOnt = serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Dequeue();
  283:  
  284:                 result.AddSuccess("(" + serviceRequestOnt.Name + "/" + serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count + ")");
  285:             }
  286:             else
  287:             {
  288:                 serviceRequestOnt = null;
  289:  
  290:                 result.AddSuccess("No mismatch in record values between FTN and billing database. ");
  291:             }
  292:  
  293:             return serviceRequestOnt;
  294:         }
  295:  
  296:         ////////////////////////////////////////////////////////////////////////////    
  297:  
  298:         /// <summary>
  299:         ///
  300:         /// </summary>
  301:         public static Tuple<string, string> ServiceRequestOntIdStartEndRangeManager(out Ia.Cl.Models.Result result)
  302:         {
  303:             string startAccessName, endAccessName;
  304:             Tuple<string, string> tuple;
  305:             List<string> ontListAccessIdList;
  306:             List<Tuple<string, string>> tupleList;
  307:  
  308:             result = new Ia.Cl.Models.Result();
  309:  
  310:             if (serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count == 0)
  311:             {
  312:                 ontListAccessIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.OntAccessIdList;
  313:                 ontAccessIdToOntAccessNameDictionary = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
  314:  
  315:                 tupleList = Ia.Cl.Models.Default.OptimizedStartEndRangeTupleList(ontListAccessIdList, LengthOfRequestOntIdRange);
  316:  
  317:                 serviceRequestAccessIdOptimizedStartEndRangeTupleQueue = new Queue<Tuple<string, string>>(tupleList);
  318:             }
  319:  
  320:             if (serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count > 0)
  321:             {
  322:                 tuple = serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Dequeue();
  323:  
  324:                 // debug
  325:                 //tuple = new Tuple<string, string>("104010301512009", "104010301512019");
  326:  
  327:                 startAccessName = ontAccessIdToOntAccessNameDictionary[tuple.Item1];
  328:                 endAccessName = ontAccessIdToOntAccessNameDictionary[tuple.Item2];
  329:  
  330:                 result.AddSuccess("(" + startAccessName + " - " + endAccessName + "/" + serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count + ")");
  331:             }
  332:             else
  333:             {
  334:                 tuple = null;
  335:  
  336:                 result.AddWarning("(0-0 0/0)");
  337:             }
  338:  
  339:             return tuple;
  340:         }
  341:  
  342:         ////////////////////////////////////////////////////////////////////////////    
  343:  
  344:         /// <summary>
  345:         ///
  346:         /// </summary>
  347:         public static string OracleSqlCommandSelectProperServiceRequestOntRecordList(Tuple<string, string> startEndRangeTuple)
  348:         {
  349:             return OracleSqlCommandSelectProperServiceRequestOntRecordList(startEndRangeTuple.Item1, startEndRangeTuple.Item2);
  350:         }
  351:  
  352:         ////////////////////////////////////////////////////////////////////////////    
  353:  
  354:         /// <summary>
  355:         ///
  356:         /// </summary>
  357:         public static string OracleSqlCommandSelectProperServiceRequestOntRecordList(string start, string end)
  358:         {
  359:             string sql;
  360:  
  361:             //if (startEndRangeTuple != null) sql = "select distinct DP_ID as Id, DP as Value from (select DP, DP_ID from FM_NET where (DP_ID >= " + startEndRangeTuple.Item1 + " and DP_ID <= " + startEndRangeTuple.Item2 + ") order by DP_ID asc) where (DP like '%/%/%' or DP like '% % %' or DP like '%.%.%')"; // and rownum <= " + Ia.Ftn.Cl.Model.Data.ServiceRequestOnt.LengthOfRequestOntIdRange;
  362:             //if (startEndRangeTuple != null) sql = "select distinct DP_ID as Id, DP_NAME as Value from FM_DP where (DP_ID >= " + startEndRangeTuple.Item1 + " and DP_ID <= " + startEndRangeTuple.Item2 + @") and (DP_NAME like '%/%/%' or DP_NAME like '% % %' or DP_NAME like '%.%.%') order by DP_ID asc";
  363:             //if (startEndRangeTuple != null) sql = "select distinct DP_ID as Id, DP_NAME as Value from FM_DP where (DP_ID >= " + startEndRangeTuple.Item1 + " and DP_ID <= " + startEndRangeTuple.Item2 + @") and (DP_NAME like '%/%/%' or DP_NAME like '% % %' or DP_NAME like '%.%.%' or DP_NAME like '% % %' or DP_NAME like '%.% %') order by DP_ID asc";
  364:             if (!string.IsNullOrEmpty(start) && !string.IsNullOrEmpty(end)) sql = @"select id, pon, ont, area_id as AreaSymbol, blook as Block, street, plot as PremisesOld, building as PremisesNew, paci, line_nos as PossibleNumberOfTd, ethernet_nos as PossibleNumberOfEthernet from ont_data where (id >= " + start + " and id <= " + end + @") order by id asc";
  365:             else sql = string.Empty;
  366:  
  367:             return sql;
  368:         }
  369:  
  370:         ////////////////////////////////////////////////////////////////////////////    
  371:  
  372:         /// <summary>
  373:         ///
  374:         /// </summary>
  375:         public static string OracleSqlCommandInsertServiceRequestOntRecord(Ia.Ftn.Cl.Models.ServiceRequestOnt serviceRequestOnt)
  376:         {
  377:             string sql;
  378:  
  379:             sql = @"insert into ONT_DATA(ID, PON, ONT, AREA_ID, BLOOK, STREET, PLOT, BUILDING, PACI, LINE_NOS, ETHERNET_NOS) values "
  380:                          + @"("
  381:                          + @"N&quote;" + serviceRequestOnt.Id + @"&quote;,"
  382:                          + @"N&quote;" + serviceRequestOnt.Pon + @"&quote;,"
  383:                          + @"N&quote;" + serviceRequestOnt.Ont + @"&quote;,"
  384:                          + @"N&quote;" + serviceRequestOnt.AreaSymbol + @"&quote;,"
  385:                          + @"N&quote;" + serviceRequestOnt.Block + @"&quote;,"
  386:                          + @"N&quote;" + serviceRequestOnt.Street + @"&quote;,"
  387:                          + @"N&quote;" + serviceRequestOnt.PremisesOld + @"&quote;,"
  388:                          + @"N&quote;" + serviceRequestOnt.PremisesNew + @"&quote;,"
  389:                          + @"N&quote;" + serviceRequestOnt.Paci + @"&quote;,"
  390:                          + @"N&quote;" + serviceRequestOnt.PossibleNumberOfTd + @"&quote;,"
  391:                          + @"N&quote;" + serviceRequestOnt.PossibleNumberOfEthernet + @"&quote;"
  392:                                                      + @")";
  393:  
  394:             sql = sql.Replace("'", "''");
  395:             sql = sql.Replace("&quote;", "'");
  396:  
  397:             return sql;
  398:         }
  399:  
  400:         ////////////////////////////////////////////////////////////////////////////    
  401:  
  402:         /// <summary>
  403:         ///
  404:         /// </summary>
  405:         public static string OracleSqlCommandUpdateServiceRequestOntRecord(Ia.Ftn.Cl.Models.ServiceRequestOnt serviceRequestOnt)
  406:         {
  407:             string sql;
  408:  
  409:             sql = @"update ONT_DATA set PON = N&quote;" + serviceRequestOnt.Pon + @"&quote;, "
  410:     + @"ONT = N&quote;" + serviceRequestOnt.Ont + @"&quote;, "
  411:     + @"AREA_ID = N&quote;" + serviceRequestOnt.AreaSymbol + @"&quote;, "
  412:     + @"BLOOK = N&quote;" + serviceRequestOnt.Block + @"&quote;, "
  413:     + @"STREET = N&quote;" + serviceRequestOnt.Street + @"&quote;, "
  414:     + @"PLOT = N&quote;" + serviceRequestOnt.PremisesOld + @"&quote;, "
  415:     + @"BUILDING = N&quote;" + serviceRequestOnt.PremisesNew + @"&quote;, "
  416:     + @"PACI = N&quote;" + serviceRequestOnt.Paci + @"&quote;, "
  417:     + @"LINE_NOS = N&quote;" + serviceRequestOnt.PossibleNumberOfTd + @"&quote;, "
  418:     + @"ETHERNET_NOS = N&quote;" + serviceRequestOnt.PossibleNumberOfEthernet + @"&quote;"
  419:     + @" where ID = N&quote;" + serviceRequestOnt.Id + @"&quote;";
  420:  
  421:             sql = sql.Replace("'", "''");
  422:             sql = sql.Replace("&quote;", "'");
  423:  
  424:             return sql;
  425:         }
  426:  
  427:         ////////////////////////////////////////////////////////////////////////////    
  428:  
  429:         /// <summary>
  430:         ///
  431:         /// </summary>
  432:         public static string OracleSqlCommandDeleteServiceRequestOntRecord(Ia.Ftn.Cl.Models.ServiceRequestOnt serviceRequestOnt)
  433:         {
  434:             string sql;
  435:  
  436:             sql = @"delete from ONT_DATA where ID = N&quote;" + serviceRequestOnt.Id + @"&quote;";
  437:  
  438:             sql = sql.Replace("'", "''");
  439:             sql = sql.Replace("&quote;", "'");
  440:  
  441:             return sql;
  442:         }
  443:  
  444:         ////////////////////////////////////////////////////////////////////////////    
  445:  
  446:         /// <summary>
  447:         ///
  448:         /// </summary>
  449:         public static string OracleSqlCommandDeleteServiceRequestOntRecord(string sqlWhereString)
  450:         {
  451:             string sql;
  452:  
  453:             sql = @"delete from ONT_DATA where " + sqlWhereString;
  454:  
  455:             return sql;
  456:         }
  457:  
  458:         ////////////////////////////////////////////////////////////////////////////
  459:  
  460:         /// <summary>
  461:         ///
  462:         /// </summary>
  463:         public static Ia.Cl.Models.Result UpdateForServiceRequestOntIdRangeWithOutputDataTable(DataTable dataTable, Tuple<string, string> startEndRangeTuple)
  464:         {
  465:             //bool isUpdated;
  466:             int pon, ont, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
  467:             string r, start, end, areaSymbol, accessName, serviceRequestOntId;
  468:             Ia.Cl.Models.Result result;
  469:             Ia.Ftn.Cl.Models.ServiceRequestOnt serviceRequestOnt, newServiceRequestOnt;
  470:             List<string> insertedItemIdList, newServiceRequestOntIdList;
  471:             List<Ia.Ftn.Cl.Models.ServiceRequestOnt> serviceRequestOntList;
  472:  
  473:             //isUpdated = false;
  474:             readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
  475:             r = string.Empty;
  476:             result = new Ia.Cl.Models.Result();
  477:             insertedItemIdList = new List<string>();
  478:             newServiceRequestOntIdList = new List<string>();
  479:  
  480:             if (dataTable != null)
  481:             {
  482:                 using (var db = new Ia.Ftn.Cl.Db())
  483:                 {
  484:                     readItemCount = dataTable.Rows.Count;
  485:  
  486:                     start = startEndRangeTuple.Item1;
  487:                     end = startEndRangeTuple.Item2;
  488:  
  489:                     serviceRequestOntList = Ia.Ftn.Cl.Models.Data.ServiceRequestOnt.ReadListWithinIdRange(start, end);
  490:                     existingItemCount = serviceRequestOntList.Count;
  491:  
  492:                     insertedItemIdList = new List<string>(dataTable.Rows.Count + 1);
  493:  
  494:                     foreach (DataRow dataRow in dataTable.Rows)
  495:                     {
  496:                         serviceRequestOntId = dataRow["Id"].ToString();
  497:  
  498:                         ont = int.Parse(dataRow["Ont"].ToString());
  499:                         pon = int.Parse(dataRow["Pon"].ToString());
  500:                         areaSymbol = dataRow["AreaSymbol"].ToString();
  501:  
  502:                         accessName = Ia.Ftn.Cl.Models.Business.Access.Name(areaSymbol, pon, ont);
  503:  
  504:                         serviceRequestOnt = (from sro in db.ServiceRequestOnts where sro.Id == serviceRequestOntId select sro).SingleOrDefault();
  505:  
  506:                         newServiceRequestOnt = new Ia.Ftn.Cl.Models.ServiceRequestOnt()
  507:                         {
  508:                             Id = serviceRequestOntId,
  509:  
  510:                             Pon = pon,
  511:                             Ont = ont,
  512:                             AreaSymbol = areaSymbol,
  513:                             Block = dataRow["Block"].ToString(),
  514:                             Street = dataRow["Street"].ToString(),
  515:                             PremisesOld = dataRow["PremisesOld"].ToString(),
  516:                             PremisesNew = dataRow["PremisesNew"].ToString(),
  517:                             Paci = dataRow["Paci"].ToString(),
  518:                             PossibleNumberOfTd = int.Parse(dataRow["PossibleNumberOfTd"].ToString()),
  519:                             PossibleNumberOfEthernet = int.Parse(dataRow["PossibleNumberOfEthernet"].ToString()),
  520:  
  521:                             Created = DateTime.UtcNow.AddHours(3),
  522:                             Updated = DateTime.UtcNow.AddHours(3),
  523:  
  524:                             Access = Ia.Ftn.Cl.Models.Business.ServiceRequestType.ExtractAccess(db, accessName)
  525:                         };
  526:  
  527:                         if (serviceRequestOnt == null)
  528:                         {
  529:                             insertedItemIdList.Add(serviceRequestOntId);
  530:  
  531:                             db.ServiceRequestOnts.Add(newServiceRequestOnt);
  532:  
  533:                             insertedItemCount++;
  534:                         }
  535:                         else
  536:                         {
  537:                             // below: copy values from newServiceRequestOnt to serviceRequestOnt
  538:  
  539:                             if (serviceRequestOnt.Update(newServiceRequestOnt))
  540:                             {
  541:                                 db.ServiceRequestOnts.Attach(serviceRequestOnt);
  542:                                 db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
  543:  
  544:                                 updatedItemCount++;
  545:                             }
  546:                         }
  547:  
  548:                         // below: this will enable the removal of SRT that don't have a valid SR
  549:                         newServiceRequestOntIdList.Add(serviceRequestOntId);
  550:                     }
  551:  
  552:                     db.SaveChanges();
  553:  
  554:                     // I will not remove ServiceRequestOnt information from billing database after it is inserted. Below code will remove ONTs that were OOS
  555:                     /*
  556:                     // below: this function will remove values that were not present in the reading
  557:                     if (serviceRequestOntList.Count > 0)
  558:                     {
  559:                         foreach (Ia.Ftn.Cl.Model.ServiceRequestOnt sro in serviceRequestOntList)
  560:                         {
  561:                             if (!newServiceRequestOntIdList.Contains(sro.Id))
  562:                             {
  563:                                 serviceRequestOnt = (from sro2 in db.ServiceRequestOnts where sro2.Id == sro.Id select sro2).SingleOrDefault();
  564: 
  565:                                 if (serviceRequestOnt != null)
  566:                                 {
  567:                                     db.ServiceRequestOnts.Remove(serviceRequestOnt);
  568:                                     deletedItemCount++;
  569:                                 }
  570:                             }
  571:                         }
  572: 
  573:                         db.SaveChanges();
  574:                     }
  575:                     */
  576:  
  577:                     /*
  578:                     // note that I had to create the object first (above) then assign a foreign key reference to it
  579:                     foreach (int i in insertedItemIdList)
  580:                     {
  581:                         newServiceRequestOnt = new Ia.Ftn.Cl.Model.ServiceRequestOnt();
  582: 
  583:                         serviceRequestOnt = (from sro in db.ServiceRequestOnts where sro.Id == i select sro).SingleOrDefault();
  584: 
  585:                         if (serviceRequestOnt != null)
  586:                         {
  587:                             newServiceRequestOnt.Copy(serviceRequestOnt);
  588: 
  589:                             //newServiceRequestOnt.ServiceRequest = (from q in db.ServiceRequests where q.Id == serviceRequestId select q).SingleOrDefault();
  590:                             newServiceRequestOnt.Access = Ia.Ftn.Cl.Model.Business.ServiceRequestType.ExtractAccess(db, serviceRequestOnt.Value);
  591: 
  592:                             if (serviceRequestOnt.Update(newServiceRequestOnt))
  593:                             {
  594:                                 db.ServiceRequestOnts.Attach(serviceRequestOnt);
  595:                                 db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
  596: 
  597:                                 updatedItemCount++;
  598:                             }
  599:                         }
  600:                     }
  601:                     */
  602:  
  603:                     db.SaveChanges();
  604:  
  605:                     //if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
  606:                     //else isUpdated = false;
  607:  
  608:                     result.AddSuccess("(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") ");
  609:                 }
  610:             }
  611:             else
  612:             {
  613:                 result.AddWarning("(dataTable == null/?/?) ");
  614:             }
  615:  
  616:             return result;
  617:         }
  618:  
  619:         ////////////////////////////////////////////////////////////////////////////
  620:  
  621:         /// <summary>
  622:         ///
  623:         /// </summary>
  624:         public static Ia.Ftn.Cl.Models.ServiceRequestOnt Read(string accessId)
  625:         {
  626:             Ia.Ftn.Cl.Models.ServiceRequestOnt serviceRequestOnt;
  627:  
  628:             using (var db = new Ia.Ftn.Cl.Db())
  629:             {
  630:                 serviceRequestOnt = (from sro in db.ServiceRequestOnts
  631:                                      where sro.Access.Id == accessId
  632:                                      select sro).SingleOrDefault();
  633:             }
  634:  
  635:             return serviceRequestOnt;
  636:         }
  637:  
  638:         ////////////////////////////////////////////////////////////////////////////
  639:  
  640:         /// <summary>
  641:         ///
  642:         /// </summary>
  643:         public static List<Ia.Ftn.Cl.Models.ServiceRequestOnt> ReadListWithinIdRange(string start, string end)
  644:         {
  645:             List<Ia.Ftn.Cl.Models.ServiceRequestOnt> serviceRequestOntList;
  646:  
  647:             using (var db = new Ia.Ftn.Cl.Db())
  648:             {
  649:                 serviceRequestOntList = (from sro in db.ServiceRequestOnts where sro.Id.CompareTo(start) >= 0 && sro.Id.CompareTo(end) <= 0 select sro).ToList();
  650:             }
  651:  
  652:             return serviceRequestOntList;
  653:         }
  654:  
  655:         /*
  656:         ////////////////////////////////////////////////////////////////////////////
  657: 
  658:         /// <summary>
  659:         ///
  660:         /// </summary>
  661:         public static void UpdateNullAccess(out Ia.Cl.Models.Result result)
  662:         {
  663:             int updatedItemCount;
  664:             Ia.Ftn.Cl.Model.ServiceRequestOnt updatedServiceRequestOnt;
  665:             List<Ia.Ftn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
  666: 
  667:             updatedItemCount = 0;
  668:             result = new Ia.Cl.Models.Result();
  669: 
  670:             using (var db = new Ia.Ftn.Cl.Model.Ftn())
  671:             {
  672:                 serviceRequestOntList = (from sro in db.ServiceRequestOnts where sro.Access == null select sro).ToList();
  673: 
  674:                 if (serviceRequestOntList != null && serviceRequestOntList.Count > 0)
  675:                 {
  676:                     foreach (Ia.Ftn.Cl.Model.ServiceRequestOnt serviceRequestOnt in serviceRequestOntList)
  677:                     {
  678:                         updatedServiceRequestOnt = (from q in db.ServiceRequestOnts where q.Id == serviceRequestOnt.Id select q).SingleOrDefault();
  679: 
  680:                         try
  681:                         {
  682:                             updatedServiceRequestOnt.Access = Ia.Ftn.Cl.Model.Business.ServiceRequestType.ExtractAccess(db, serviceRequestOnt.Value);
  683:                         }
  684:                         catch(Exception ex)
  685:                         {
  686: 
  687:                         }
  688: 
  689:                         if (updatedServiceRequestOnt.Access != null)
  690:                         {
  691:                             db.ServiceRequestOnts.Attach(updatedServiceRequestOnt);
  692:                             db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
  693: 
  694:                             updatedItemCount++;
  695:                         }
  696:                     }
  697: 
  698:                     db.SaveChanges();
  699: 
  700:                     result.AddSuccess("Number of updated records: " + updatedItemCount + ". ");
  701:                 }
  702:                 else
  703:                 {
  704:                     result.AddSuccess("No service request ONTs with Access = null. ");
  705:                 }
  706:             }
  707:         }
  708:         */
  709:  
  710:         ////////////////////////////////////////////////////////////////////////////
  711:  
  712:         /// <summary>
  713:         ///
  714:         /// </summary>
  715:         public static bool Delete(string id, out string result)
  716:         {
  717:             bool b;
  718:  
  719:             b = false;
  720:             result = string.Empty;
  721:  
  722:             using (var db = new Ia.Ftn.Cl.Db())
  723:             {
  724:                 var v = (from o in db.ServiceRequestOnts where o.Id == id select o).FirstOrDefault();
  725:  
  726:                 if (v != null)
  727:                 {
  728:                     db.ServiceRequestOnts.Remove(v);
  729:                     db.SaveChanges();
  730:  
  731:                     b = true;
  732:                 }
  733:                 else b = false;
  734:             }
  735:  
  736:             return b;
  737:         }
  738:  
  739:         ////////////////////////////////////////////////////////////////////////////
  740:  
  741:         /// <summary>
  742:         ///
  743:         /// </summary>
  744:         public static bool DeleteByAccessId(string accessId, out string result)
  745:         {
  746:             bool recordExisted;
  747:  
  748:             recordExisted = false;
  749:             result = string.Empty;
  750:  
  751:             using (var db = new Ia.Ftn.Cl.Db())
  752:             {
  753:                 // --delete from ServiceRequestOnts where Access_Id = '1040101010040004'
  754:                 var v = (from sro in db.ServiceRequestOnts
  755:                          where sro.Access.Id == accessId
  756:                          select sro).FirstOrDefault(); //.SingleOrDefault();
  757:  
  758:                 if (v != null)
  759:                 {
  760:                     db.ServiceRequestOnts.Remove(v);
  761:                     db.SaveChanges();
  762:  
  763:                     recordExisted = true;
  764:                 }
  765:                 else recordExisted = false;
  766:             }
  767:  
  768:             return recordExisted;
  769:         }
  770:  
  771:  
  772:         ////////////////////////////////////////////////////////////////////////////
  773:  
  774:         /// <summary>
  775:         ///
  776:         /// </summary>
  777:         public static List<string> AccessNamesThatRevertToAnUnexplainedErroneousStateInTheCustomerDepartmentDatabaseAndAreExemptFromSynchronizationProcessingList()
  778:         {
  779:             List<string> list = new List<string>() { "SHD.38.12", "SHD.45.2", "SHD.56.11", "HIT.75.15", "HIT.69.4", "HIT.69.3", "SHD.55.7", "SHD.41.8" };
  780:  
  781:             return list;
  782:         }
  783:  
  784:         ////////////////////////////////////////////////////////////////////////////    
  785:         ////////////////////////////////////////////////////////////////////////////    
  786:     }
  787:  
  788:     ////////////////////////////////////////////////////////////////////////////
  789:     ////////////////////////////////////////////////////////////////////////////
  790: }