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

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:                              serviceRequestOnt = new Ia.Ngn.Cl.Model.ServiceRequestOnt()
 229:                              {
 230:                                  Id = access.Id,
 231:                                  Pon = access.Pon,
 232:                                  Ont = access.Ont,
 233:                                  Paci = access.Paci,
 234:                                  AreaSymbol = areaSymbol,
 235:                                  Block = access.Block,
 236:                                  Street = access.Street,
 237:                                  PremisesOld = access.PremisesOld,
 238:                                  PremisesNew = access.PremisesNew,
 239:   
 240:                                  PossibleNumberOfTd = possibleNumberOfTd,
 241:                                  PossibleNumberOfEthernet = possibleNumberOfEthernet
 242:                              };
 243:   
 244:                              serviceRequestOntList.Add(serviceRequestOnt);
 245:                          }
 246:                          else
 247:                          {
 248:   
 249:                          }
 250:                      }
 251:                      else
 252:                      {
 253:   
 254:                      }
 255:                  }
 256:                  else
 257:                  {
 258:   
 259:                  }
 260:              }
 261:   
 262:              return serviceRequestOntList;
 263:          }
 264:   
 265:          ////////////////////////////////////////////////////////////////////////////    
 266:          ////////////////////////////////////////////////////////////////////////////    
 267:   
 268:          /// <summary>
 269:          ///
 270:          /// </summary>
 271:          public static Ia.Ngn.Cl.Model.ServiceRequestOnt ServiceRequestOntNotUpdatedInCustomerDepartmentDatabase(out Ia.Cl.Model.Result result)
 272:          {
 273:              Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt;
 274:              List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
 275:              List<Ia.Ngn.Cl.Model.Access> accessList;
 276:   
 277:              result = new Ia.Cl.Model.Result();
 278:   
 279:              if (serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count == 0)
 280:              {
 281:                  accessList = Ia.Ngn.Cl.Model.Data.Access.AccessesWithProvisionedAndReadyOntsAndEmsOntsButMismatchedWithCustomerDepartmentDatabaseServiceRequestOntListList();
 282:   
 283:                  serviceRequestOntList = PrepareServiceRequestOntListFromAccessList(accessList);
 284:   
 285:                  serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ngn.Cl.Model.ServiceRequestOnt>(serviceRequestOntList);
 286:              }
 287:   
 288:              if (serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count > 0)
 289:              {
 290:                  serviceRequestOnt = serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Dequeue();
 291:   
 292:                  result.AddSuccess("(" + serviceRequestOnt.Name + "/" + serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count + ")");
 293:              }
 294:              else
 295:              {
 296:                  serviceRequestOnt = null;
 297:   
 298:                  result.AddSuccess("No mismatch in record values between OFN and billing database. ");
 299:              }
 300:   
 301:              return serviceRequestOnt;
 302:          }
 303:   
 304:          ////////////////////////////////////////////////////////////////////////////    
 305:   
 306:          /// <summary>
 307:          ///
 308:          /// </summary>
 309:          public static Tuple<string, string> ServiceRequestOntIdStartEndRangeManager(out Ia.Cl.Model.Result result)
 310:          {
 311:              string startAccessName, endAccessName;
 312:              Tuple<string, string> tuple;
 313:              List<string> ontListAccessIdList;
 314:              List<Tuple<string, string>> tupleList;
 315:   
 316:              result = new Ia.Cl.Model.Result();
 317:   
 318:              if (serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count == 0)
 319:              {
 320:                  ontListAccessIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdList;
 321:                  ontAccessIdToOntAccessNameDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
 322:   
 323:                  tupleList = Ia.Cl.Model.Default.OptimizedStartEndRangeTupleList(ontListAccessIdList, LengthOfRequestOntIdRange);
 324:   
 325:                  serviceRequestAccessIdOptimizedStartEndRangeTupleQueue = new Queue<Tuple<string, string>>(tupleList);
 326:              }
 327:   
 328:              if (serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count > 0)
 329:              {
 330:                  tuple = serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Dequeue();
 331:   
 332:                  // debug
 333:                  //tuple = new Tuple<string, string>("104010301512009", "104010301512019");
 334:   
 335:                  startAccessName = ontAccessIdToOntAccessNameDictionary[tuple.Item1];
 336:                  endAccessName = ontAccessIdToOntAccessNameDictionary[tuple.Item2];
 337:   
 338:                  result.AddSuccess("(" + startAccessName + " - " + endAccessName + "/" + serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count + ")");
 339:              }
 340:              else
 341:              {
 342:                  tuple = null;
 343:   
 344:                  result.AddWarning("(0-0 0/0)");
 345:              }
 346:   
 347:              return tuple;
 348:          }
 349:   
 350:          ////////////////////////////////////////////////////////////////////////////    
 351:   
 352:          /// <summary>
 353:          ///
 354:          /// </summary>
 355:          public static string OracleSqlCommandSelectProperServiceRequestOntRecordList(Tuple<string, string> startEndRangeTuple)
 356:          {
 357:              return OracleSqlCommandSelectProperServiceRequestOntRecordList(startEndRangeTuple.Item1, startEndRangeTuple.Item2);
 358:          }
 359:   
 360:          ////////////////////////////////////////////////////////////////////////////    
 361:   
 362:          /// <summary>
 363:          ///
 364:          /// </summary>
 365:          public static string OracleSqlCommandSelectProperServiceRequestOntRecordList(string start, string end)
 366:          {
 367:              string sql;
 368:   
 369:              //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;
 370:              //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";
 371:              //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";
 372:              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";
 373:              else sql = string.Empty;
 374:   
 375:              return sql;
 376:          }
 377:   
 378:          ////////////////////////////////////////////////////////////////////////////    
 379:   
 380:          /// <summary>
 381:          ///
 382:          /// </summary>
 383:          public static string OracleSqlCommandInsertServiceRequestOntRecord(Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt)
 384:          {
 385:              string sql;
 386:   
 387:              sql = @"insert into ONT_DATA(ID, PON, ONT, AREA_ID, BLOOK, STREET, PLOT, BUILDING, PACI, LINE_NOS, ETHERNET_NOS) values "
 388:                           + @"("
 389:                           + @"N&quote;" + serviceRequestOnt.Id + @"&quote;,"
 390:                           + @"N&quote;" + serviceRequestOnt.Pon + @"&quote;,"
 391:                           + @"N&quote;" + serviceRequestOnt.Ont + @"&quote;,"
 392:                           + @"N&quote;" + serviceRequestOnt.AreaSymbol + @"&quote;,"
 393:                           + @"N&quote;" + serviceRequestOnt.Block + @"&quote;,"
 394:                           + @"N&quote;" + serviceRequestOnt.Street + @"&quote;,"
 395:                           + @"N&quote;" + serviceRequestOnt.PremisesOld + @"&quote;,"
 396:                           + @"N&quote;" + serviceRequestOnt.PremisesNew + @"&quote;,"
 397:                           + @"N&quote;" + serviceRequestOnt.Paci + @"&quote;,"
 398:                           + @"N&quote;" + serviceRequestOnt.PossibleNumberOfTd + @"&quote;,"
 399:                           + @"N&quote;" + serviceRequestOnt.PossibleNumberOfEthernet + @"&quote;"
 400:                                                       + @")";
 401:   
 402:              sql = sql.Replace("'", "''");
 403:              sql = sql.Replace("&quote;", "'");
 404:   
 405:              return sql;
 406:          }
 407:   
 408:          ////////////////////////////////////////////////////////////////////////////    
 409:   
 410:          /// <summary>
 411:          ///
 412:          /// </summary>
 413:          public static string OracleSqlCommandUpdateServiceRequestOntRecord(Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt)
 414:          {
 415:              string sql;
 416:   
 417:              sql = @"update ONT_DATA set PON = N&quote;" + serviceRequestOnt.Pon + @"&quote;, "
 418:      + @"ONT = N&quote;" + serviceRequestOnt.Ont + @"&quote;, "
 419:      + @"AREA_ID = N&quote;" + serviceRequestOnt.AreaSymbol + @"&quote;, "
 420:      + @"BLOOK = N&quote;" + serviceRequestOnt.Block + @"&quote;, "
 421:      + @"STREET = N&quote;" + serviceRequestOnt.Street + @"&quote;, "
 422:      + @"PLOT = N&quote;" + serviceRequestOnt.PremisesOld + @"&quote;, "
 423:      + @"BUILDING = N&quote;" + serviceRequestOnt.PremisesNew + @"&quote;, "
 424:      + @"PACI = N&quote;" + serviceRequestOnt.Paci + @"&quote;, "
 425:      + @"LINE_NOS = N&quote;" + serviceRequestOnt.PossibleNumberOfTd + @"&quote;, "
 426:      + @"ETHERNET_NOS = N&quote;" + serviceRequestOnt.PossibleNumberOfEthernet + @"&quote;"
 427:      + @" where ID = N&quote;" + serviceRequestOnt.Id + @"&quote;";
 428:   
 429:              sql = sql.Replace("'", "''");
 430:              sql = sql.Replace("&quote;", "'");
 431:   
 432:              return sql;
 433:          }
 434:   
 435:          ////////////////////////////////////////////////////////////////////////////    
 436:   
 437:          /// <summary>
 438:          ///
 439:          /// </summary>
 440:          public static string OracleSqlCommandDeleteServiceRequestOntRecord(Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt)
 441:          {
 442:              string sql;
 443:   
 444:              sql = @"delete from ONT_DATA where ID = N&quote;" + serviceRequestOnt.Id + @"&quote;";
 445:   
 446:              sql = sql.Replace("'", "''");
 447:              sql = sql.Replace("&quote;", "'");
 448:   
 449:              return sql;
 450:          }
 451:   
 452:          ////////////////////////////////////////////////////////////////////////////    
 453:   
 454:          /// <summary>
 455:          ///
 456:          /// </summary>
 457:          public static string OracleSqlCommandDeleteServiceRequestOntRecord(string sqlWhereString)
 458:          {
 459:              string sql;
 460:   
 461:              sql = @"delete from ONT_DATA where " + sqlWhereString;
 462:   
 463:              return sql;
 464:          }
 465:   
 466:          ////////////////////////////////////////////////////////////////////////////
 467:   
 468:          /// <summary>
 469:          ///
 470:          /// </summary>
 471:          public static Ia.Cl.Model.Result UpdateForServiceRequestOntIdRangeWithOutputDataTable(DataTable dataTable, Tuple<string, string> startEndRangeTuple)
 472:          {
 473:              //bool isUpdated;
 474:              int pon, ont, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
 475:              string r, start, end, areaSymbol, accessName, serviceRequestOntId;
 476:              Ia.Cl.Model.Result result;
 477:              Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt, newServiceRequestOnt;
 478:              List<string> insertedItemIdList, newServiceRequestOntIdList;
 479:              List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
 480:   
 481:              //isUpdated = false;
 482:              readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
 483:              r = string.Empty;
 484:              result = new Ia.Cl.Model.Result();
 485:              insertedItemIdList = new List<string>();
 486:              newServiceRequestOntIdList = new List<string>();
 487:   
 488:              if (dataTable != null)
 489:              {
 490:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 491:                  {
 492:                      readItemCount = dataTable.Rows.Count;
 493:   
 494:                      start = startEndRangeTuple.Item1;
 495:                      end = startEndRangeTuple.Item2;
 496:   
 497:                      serviceRequestOntList = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.ReadListWithinIdRange(start, end);
 498:                      existingItemCount = serviceRequestOntList.Count;
 499:   
 500:                      insertedItemIdList = new List<string>(dataTable.Rows.Count + 1);
 501:   
 502:                      foreach (DataRow dataRow in dataTable.Rows)
 503:                      {
 504:                          serviceRequestOntId = dataRow["Id"].ToString();
 505:   
 506:                          ont = int.Parse(dataRow["Ont"].ToString());
 507:                          pon = int.Parse(dataRow["Pon"].ToString());
 508:                          areaSymbol = dataRow["AreaSymbol"].ToString();
 509:   
 510:                          accessName = Ia.Ngn.Cl.Model.Business.Access.Name(areaSymbol, pon, ont);
 511:   
 512:                          serviceRequestOnt = (from sro in db.ServiceRequestOnts where sro.Id == serviceRequestOntId select sro).SingleOrDefault();
 513:   
 514:                          newServiceRequestOnt = new Ia.Ngn.Cl.Model.ServiceRequestOnt()
 515:                          {
 516:                              Id = serviceRequestOntId,
 517:   
 518:                              Pon = pon,
 519:                              Ont = ont,
 520:                              AreaSymbol = areaSymbol,
 521:                              Block = dataRow["Block"].ToString(),
 522:                              Street = dataRow["Street"].ToString(),
 523:                              PremisesOld = dataRow["PremisesOld"].ToString(),
 524:                              PremisesNew = dataRow["PremisesNew"].ToString(),
 525:                              Paci = dataRow["Paci"].ToString(),
 526:                              PossibleNumberOfTd = int.Parse(dataRow["PossibleNumberOfTd"].ToString()),
 527:                              PossibleNumberOfEthernet = int.Parse(dataRow["PossibleNumberOfEthernet"].ToString()),
 528:   
 529:                              Created = DateTime.UtcNow.AddHours(3),
 530:                              Updated = DateTime.UtcNow.AddHours(3),
 531:   
 532:                              Access = Ia.Ngn.Cl.Model.Business.ServiceRequestType.ExtractAccess(db, accessName)
 533:                          };
 534:   
 535:                          if (serviceRequestOnt == null)
 536:                          {
 537:                              insertedItemIdList.Add(serviceRequestOntId);
 538:   
 539:                              db.ServiceRequestOnts.Add(newServiceRequestOnt);
 540:   
 541:                              insertedItemCount++;
 542:                          }
 543:                          else
 544:                          {
 545:                              // below: copy values from newServiceRequestOnt to serviceRequestOnt
 546:   
 547:                              if (serviceRequestOnt.Update(newServiceRequestOnt))
 548:                              {
 549:                                  db.ServiceRequestOnts.Attach(serviceRequestOnt);
 550:                                  db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
 551:   
 552:                                  updatedItemCount++;
 553:                              }
 554:                          }
 555:   
 556:                          // below: this will enable the removal of SRT that don't have a valid SR
 557:                          newServiceRequestOntIdList.Add(serviceRequestOntId);
 558:                      }
 559:   
 560:                      db.SaveChanges();
 561:   
 562:                      // I will not remove ServiceRequestOnt information from billing database after it is inserted. Below code will remove ONTs that were OOS
 563:                      /*
 564:                      // below: this function will remove values that were not present in the reading
 565:                      if (serviceRequestOntList.Count > 0)
 566:                      {
 567:                          foreach (Ia.Ngn.Cl.Model.ServiceRequestOnt sro in serviceRequestOntList)
 568:                          {
 569:                              if (!newServiceRequestOntIdList.Contains(sro.Id))
 570:                              {
 571:                                  serviceRequestOnt = (from sro2 in db.ServiceRequestOnts where sro2.Id == sro.Id select sro2).SingleOrDefault();
 572:  
 573:                                  if (serviceRequestOnt != null)
 574:                                  {
 575:                                      db.ServiceRequestOnts.Remove(serviceRequestOnt);
 576:                                      deletedItemCount++;
 577:                                  }
 578:                              }
 579:                          }
 580:  
 581:                          db.SaveChanges();
 582:                      }
 583:                      */
 584:   
 585:                      /*
 586:                      // note that I had to create the object first (above) then assign a foreign key reference to it
 587:                      foreach (int i in insertedItemIdList)
 588:                      {
 589:                          newServiceRequestOnt = new Ia.Ngn.Cl.Model.ServiceRequestOnt();
 590:  
 591:                          serviceRequestOnt = (from sro in db.ServiceRequestOnts where sro.Id == i select sro).SingleOrDefault();
 592:  
 593:                          if (serviceRequestOnt != null)
 594:                          {
 595:                              newServiceRequestOnt.Copy(serviceRequestOnt);
 596:  
 597:                              //newServiceRequestOnt.ServiceRequest = (from q in db.ServiceRequests where q.Id == serviceRequestId select q).SingleOrDefault();
 598:                              newServiceRequestOnt.Access = Ia.Ngn.Cl.Model.Business.ServiceRequestType.ExtractAccess(db, serviceRequestOnt.Value);
 599:  
 600:                              if (serviceRequestOnt.Update(newServiceRequestOnt))
 601:                              {
 602:                                  db.ServiceRequestOnts.Attach(serviceRequestOnt);
 603:                                  db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
 604:  
 605:                                  updatedItemCount++;
 606:                              }
 607:                          }
 608:                      }
 609:                      */
 610:   
 611:                      db.SaveChanges();
 612:   
 613:                      //if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
 614:                      //else isUpdated = false;
 615:   
 616:                      result.AddSuccess("(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") ");
 617:                  }
 618:              }
 619:              else
 620:              {
 621:                  result.AddWarning("(dataTable == null/?/?) ");
 622:              }
 623:   
 624:              return result;
 625:          }
 626:   
 627:          ////////////////////////////////////////////////////////////////////////////
 628:   
 629:          /// <summary>
 630:          ///
 631:          /// </summary>
 632:          public static Ia.Ngn.Cl.Model.ServiceRequestOnt Read(string accessId)
 633:          {
 634:              Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt;
 635:   
 636:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 637:              {
 638:                  serviceRequestOnt = (from sro in db.ServiceRequestOnts
 639:                                       where sro.Access.Id == accessId
 640:                                       select sro).SingleOrDefault();
 641:              }
 642:   
 643:              return serviceRequestOnt;
 644:          }
 645:   
 646:          ////////////////////////////////////////////////////////////////////////////
 647:   
 648:          /// <summary>
 649:          ///
 650:          /// </summary>
 651:          public static List<Ia.Ngn.Cl.Model.ServiceRequestOnt> ReadListWithinIdRange(string start, string end)
 652:          {
 653:              List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
 654:   
 655:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 656:              {
 657:                  serviceRequestOntList = (from sro in db.ServiceRequestOnts where sro.Id.CompareTo(start) >= 0 && sro.Id.CompareTo(end) <= 0 select sro).ToList();
 658:              }
 659:   
 660:              return serviceRequestOntList;
 661:          }
 662:   
 663:          /*
 664:          ////////////////////////////////////////////////////////////////////////////
 665:  
 666:          /// <summary>
 667:          ///
 668:          /// </summary>
 669:          public static void UpdateNullAccess(out Ia.Cl.Model.Result result)
 670:          {
 671:              int updatedItemCount;
 672:              Ia.Ngn.Cl.Model.ServiceRequestOnt updatedServiceRequestOnt;
 673:              List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
 674:  
 675:              updatedItemCount = 0;
 676:              result = new Ia.Cl.Model.Result();
 677:  
 678:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 679:              {
 680:                  serviceRequestOntList = (from sro in db.ServiceRequestOnts where sro.Access == null select sro).ToList();
 681:  
 682:                  if (serviceRequestOntList != null && serviceRequestOntList.Count > 0)
 683:                  {
 684:                      foreach (Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt in serviceRequestOntList)
 685:                      {
 686:                          updatedServiceRequestOnt = (from q in db.ServiceRequestOnts where q.Id == serviceRequestOnt.Id select q).SingleOrDefault();
 687:  
 688:                          try
 689:                          {
 690:                              updatedServiceRequestOnt.Access = Ia.Ngn.Cl.Model.Business.ServiceRequestType.ExtractAccess(db, serviceRequestOnt.Value);
 691:                          }
 692:                          catch(Exception ex)
 693:                          {
 694:  
 695:                          }
 696:  
 697:                          if (updatedServiceRequestOnt.Access != null)
 698:                          {
 699:                              db.ServiceRequestOnts.Attach(updatedServiceRequestOnt);
 700:                              db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
 701:  
 702:                              updatedItemCount++;
 703:                          }
 704:                      }
 705:  
 706:                      db.SaveChanges();
 707:  
 708:                      result.AddSuccess("Number of updated records: " + updatedItemCount + ". ");
 709:                  }
 710:                  else
 711:                  {
 712:                      result.AddSuccess("No service request ONTs with Access = null. ");
 713:                  }
 714:              }
 715:          }
 716:          */
 717:   
 718:          ////////////////////////////////////////////////////////////////////////////
 719:   
 720:          /// <summary>
 721:          ///
 722:          /// </summary>
 723:          public static bool Delete(string id, out string result)
 724:          {
 725:              bool b;
 726:   
 727:              b = false;
 728:              result = string.Empty;
 729:   
 730:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 731:              {
 732:                  var v = (from o in db.ServiceRequestOnts where o.Id == id select o).FirstOrDefault();
 733:   
 734:                  if (v != null)
 735:                  {
 736:                      db.ServiceRequestOnts.Remove(v);
 737:                      db.SaveChanges();
 738:   
 739:                      b = true;
 740:                  }
 741:                  else b = false;
 742:              }
 743:   
 744:              return b;
 745:          }
 746:   
 747:          ////////////////////////////////////////////////////////////////////////////
 748:   
 749:          /// <summary>
 750:          ///
 751:          /// </summary>
 752:          public static bool DeleteByAccessId(string accessId, out string result)
 753:          {
 754:              bool recordExisted;
 755:   
 756:              recordExisted = false;
 757:              result = string.Empty;
 758:   
 759:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 760:              {
 761:                  // --delete from ServiceRequestOnts where Access_Id = '1040101010040004'
 762:                  var v = (from sro in db.ServiceRequestOnts
 763:                           where sro.Access.Id == accessId
 764:                           select sro).FirstOrDefault(); //.SingleOrDefault();
 765:   
 766:                  if (v != null)
 767:                  {
 768:                      db.ServiceRequestOnts.Remove(v);
 769:                      db.SaveChanges();
 770:   
 771:                      recordExisted = true;
 772:                  }
 773:                  else recordExisted = false;
 774:              }
 775:   
 776:              return recordExisted;
 777:          }
 778:   
 779:   
 780:          ////////////////////////////////////////////////////////////////////////////
 781:   
 782:          /// <summary>
 783:          ///
 784:          /// </summary>
 785:          public static List<string> AccessNamesThatRevertToAnUnexplainedErroneousStateInTheCustomerDepartmentDatabaseAndAreExemptFromSynchronizationProcessingList()
 786:          {
 787:              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" };
 788:   
 789:              return list;
 790:          }
 791:   
 792:          ////////////////////////////////////////////////////////////////////////////    
 793:          ////////////////////////////////////////////////////////////////////////////    
 794:      }
 795:   
 796:      ////////////////////////////////////////////////////////////////////////////
 797:      ////////////////////////////////////////////////////////////////////////////
 798:  }