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

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