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