)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Skip Navigation LinksHome » Code Library » ServiceRequestOnt

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

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