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

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 Next Generation Network (NGN) data model.

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