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

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