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

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » Default

Public general use code classes and xml files that we've compiled and used over the years:

Default support class for Next Generation Network (NGN) data model.

   1:  using System;
   2:  using System.Data;
   3:  using System.Collections;
   4:  using System.Collections.Generic;
   5:  using System.Linq;
   6:  using System.Text;
   7:   
   8:  namespace Ia.Ngn.Cl.Model.Data
   9:  {
  10:      ////////////////////////////////////////////////////////////////////////////
  11:   
  12:      /// <summary publish="true">
  13:      /// Default support class for Next Generation Network (NGN) data model.
  14:      /// </summary>
  15:      /// 
  16:      /// <remarks> 
  17:      /// Copyright © 2006-2017 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 Default
  30:      {
  31:          ////////////////////////////////////////////////////////////////////////////
  32:   
  33:          /// <summary>
  34:          ///
  35:          /// </summary>
  36:          public Default() { }
  37:   
  38:          ////////////////////////////////////////////////////////////////////////////
  39:   
  40:          /// <summary>
  41:          ///
  42:          /// </summary>
  43:          public static List<string> NewOntEventAndAccessBySerialInNoteList()
  44:          {
  45:              List<string> list;
  46:   
  47:              list = new List<string>();
  48:   
  49:              var bList = Ia.Ngn.Cl.Model.Data.Event.ReadNewOntList;
  50:   
  51:              //var aList = Ia.Ngn.Cl.Model.Data.Access.BySerialInNote();
  52:   
  53:   
  54:              return list;
  55:          }
  56:   
  57:          ////////////////////////////////////////////////////////////////////////////
  58:   
  59:          /// <summary>
  60:          ///
  61:          /// </summary>
  62:          public static List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessOntNameStatisticalOntName> DifferentOntNameAndStatisticalOntNameList(int siteId)
  63:          {
  64:              string address, level;
  65:              Hashtable hashTable; //, nokiaInitialInstallationListHashTable;
  66:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
  67:              Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
  68:              Ia.Ngn.Cl.Model.Access statisticalAccess;
  69:              Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessOntNameStatisticalOntName l;
  70:   
  71:              List<int> siteRouterDomainList, areaIdList;
  72:              List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessOntNameStatisticalOntName> list;
  73:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
  74:              List<Ia.Ngn.Cl.Model.ServiceRequestService> serviceRequestServiceList;
  75:              List<Ia.Ngn.Cl.Model.Access> accessList;
  76:              Dictionary<int, string> areaIdToNameArabicNameDictionary;
  77:   
  78:              level = "";
  79:              areaIdList = new List<int>();
  80:   
  81:              site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();
  82:   
  83:   
  84:              areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
  85:   
  86:              //nokiaInitialInstallationListHashTable = new Hashtable(100000);
  87:   
  88:              /*
  89:              foreach (XElement xe in NokiaInitialInstallationListXDocument.Element("alInitialInstallationList").Elements("alInitialInstallation"))
  90:              {
  91:                  nokiaInitialInstallationListHashTable[xe.Attribute("accessId").Value] = xe.Value.Replace("|","\t");
  92:              }
  93:              */
  94:   
  95:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
  96:              {
  97:                  siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList where r.Site.Id == siteId select r).SelectMany(z => z.DomainList).ToList();
  98:   
  99:                  serviceRequestList = (from sr in db.ServiceRequests where siteRouterDomainList.Contains(sr.Number / 10000) || siteRouterDomainList.Contains(sr.Number / 1000) select sr).ToList();
 100:   
 101:                  hashTable = Ia.Ngn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);
 102:   
 103:                  serviceRequestServiceList = (from srs in db.ServiceRequestServices where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString())) select srs).ToList();
 104:   
 105:                  areaIdList = site.KuwaitNgnAreas.Select(i => i.Id).ToList();
 106:   
 107:                  accessList = (from a in db.Accesses join ai in areaIdList on a.AreaId equals ai select a).ToList();
 108:   
 109:                  list = new List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessOntNameStatisticalOntName>();
 110:   
 111:                  foreach (Ia.Ngn.Cl.Model.ServiceRequestService srs in serviceRequestServiceList)
 112:                  {
 113:                      l = new Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessOntNameStatisticalOntName();
 114:   
 115:                      if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();
 116:                      else address = null;
 117:   
 118:                      l.Service = srs.Service;
 119:                      l.CustomerAddress = address;
 120:                      l.Access = srs.Access;
 121:   
 122:                      if (srs.Access != null) l.OntAddress = srs.Access.Address;
 123:   
 124:                      serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.StatisticalServiceAddress(srs.Service, address, out level);
 125:   
 126:                      // below: this will skip Sabah Al-Salem area
 127:                      //if (l.CustomerAddress != null && !l.CustomerAddress.Contains("صباح"))
 128:                      //{
 129:                      statisticalAccess = Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList);
 130:   
 131:                      if (statisticalAccess != null)
 132:                      {
 133:                          l.Block = statisticalAccess.Block;
 134:                          l.Street = statisticalAccess.Street;
 135:                          l.PremisesOld = statisticalAccess.PremisesOld;
 136:                          l.PremisesNew = statisticalAccess.PremisesNew;
 137:                          l.KuwaitNgnAreaNameArabicName = areaIdToNameArabicNameDictionary[statisticalAccess.AreaId];
 138:   
 139:                          l.StatisticalOntName = statisticalAccess.Name;
 140:   
 141:                          /*
 142:                          if (nokiaInitialInstallationListHashTable.ContainsKey(statisticalAccess.Id))
 143:                          {
 144:                              l.Note = nokiaInitialInstallationListHashTable[statisticalAccess.Id].ToString();
 145:                          }
 146:                          */
 147:                          //else l.Note = "No: " + srs.Access.Id;
 148:                      }
 149:   
 150:                      if (serviceAddress.AreaId != 0)
 151:                      {
 152:                          l.StatisticalAddress = serviceAddress.Address;
 153:   
 154:                          if (srs.Access != null) l.OntName = l.Access.Name;
 155:   
 156:                          //if (l.OntName != null && l.OntName == l.StatisticalOntName) l.Note = Ia.Cl.Model.Default.YesNo(true);
 157:                          //else l.Note = Ia.Cl.Model.Default.YesNo(false);
 158:   
 159:                          //l.Note += " (" + level + ")";
 160:                      }
 161:                      else
 162:                      {
 163:                          //l.Note = Ia.Cl.Model.Default.YesNo(false);
 164:   
 165:                          //l.Note += " (AreaId zero)";
 166:                      }
 167:   
 168:                      list.Add(l);
 169:                      //}
 170:                      //else
 171:                      //{
 172:   
 173:                      //}
 174:                  }
 175:              }
 176:   
 177:              return list;
 178:          }
 179:   
 180:          ////////////////////////////////////////////////////////////////////////////
 181:   
 182:          /// <summary>
 183:          ///
 184:          /// </summary>
 185:          public static void SiteSerivceAndEmsOntAndAccessAndServiceRequestAndServiceRequestTypeInformation(int siteId, out StringBuilder sb)
 186:          {
 187:              int areaId, customerId;
 188:              string s, accessId;
 189:              string service, alias, area, block, street, premisesOld, paci, customerName, customerAddress, typeId1, typeId2, typeId4, typeId5;
 190:   
 191:              List<int> siteRouterDomainList, areaIdList;
 192:              List<string> serviceList;
 193:              Dictionary<int, string> areaIdToNameArabicNameDictionary;
 194:   
 195:              sb = new StringBuilder();
 196:   
 197:              areaIdList = new List<int>();
 198:   
 199:              /*
 200:  Service2S    ONTs(EMS)    Accesses    Accesses    Accesses    Accesses    Accesses    Service Requests    Service Requests    Service Requests    Service Requests Types    Service Requests    Service Requests    Service Requests
 201:  Service    ALIAS    Area    Block    Street    Premises Old    PACI    Customer ID    Customer Name    Address    Type ID-1    Type ID-2    Type ID 4    Type ID 5
 202:               */
 203:   
 204:              var site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();
 205:   
 206:              siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList where r.Site.Id == siteId select r).SelectMany(z => z.DomainList).ToList();
 207:   
 208:              areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
 209:   
 210:              serviceList = Ia.Ngn.Cl.Model.Data.Service2.List(siteRouterDomainList);
 211:   
 212:              var serviceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceToAccessIdDictionary;
 213:   
 214:              var idToAccessDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToAccessDictionary;
 215:   
 216:              var accessIdToOntDictionary = Ia.Ngn.Cl.Model.Data.Huawei.Ont.AccessIdToOntDictionary;
 217:   
 218:              var serviceToServiceRequestServiceDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ServiceToServiceRequestServiceDictionary(siteRouterDomainList);
 219:   
 220:              var serviceToServiceRequestDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToServiceRequestDictionary(siteRouterDomainList);
 221:   
 222:   
 223:              s = "Service\tALIAS\tArea\tBlock\tStreet\tPremises Old\tPACI\tCustomer ID\tCustomer Name\tAddress\tType ID-1\tType ID-2\tType ID 4\tType ID 5";
 224:              sb.AppendLine(s);
 225:   
 226:              foreach (string u in serviceList)
 227:              {
 228:                  service = u;
 229:   
 230:                  if (serviceToAccessIdDictionary.ContainsKey(u))
 231:                  {
 232:                      accessId = serviceToAccessIdDictionary[u];
 233:   
 234:                      if (idToAccessDictionary.ContainsKey(accessId))
 235:                      {
 236:                          areaId = idToAccessDictionary[accessId].AreaId;
 237:                          block = idToAccessDictionary[accessId].Block;
 238:                          street = idToAccessDictionary[accessId].Street;
 239:                          premisesOld = idToAccessDictionary[accessId].PremisesOld;
 240:                          paci = idToAccessDictionary[accessId].Paci;
 241:   
 242:                          if (areaIdToNameArabicNameDictionary.ContainsKey(areaId)) area = areaIdToNameArabicNameDictionary[areaId];
 243:                          else area = string.Empty;
 244:   
 245:                          if (accessIdToOntDictionary.ContainsKey(accessId))
 246:                          {
 247:                              alias = accessIdToOntDictionary[accessId].ALIAS;
 248:                          }
 249:                          else
 250:                          {
 251:                              alias = string.Empty;
 252:                          }
 253:                      }
 254:                      else
 255:                      {
 256:                          block = string.Empty;
 257:                          street = string.Empty;
 258:                          premisesOld = string.Empty;
 259:                          paci = string.Empty;
 260:                          area = string.Empty;
 261:   
 262:                          alias = string.Empty;
 263:                      }
 264:                  }
 265:                  else
 266:                  {
 267:                      block = string.Empty;
 268:                      street = string.Empty;
 269:                      premisesOld = string.Empty;
 270:                      paci = string.Empty;
 271:                      area = string.Empty;
 272:   
 273:                      alias = string.Empty;
 274:                  }
 275:   
 276:   
 277:                  if (serviceToServiceRequestServiceDictionary.ContainsKey(u))
 278:                  {
 279:                      if (serviceToServiceRequestServiceDictionary[u].Provisioned)
 280:                      {
 281:                          if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.Count > 0)
 282:                          {
 283:                              //var bb = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault();
 284:   
 285:                              if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault() != null)
 286:                              {
 287:                                  customerId = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerId;
 288:                                  customerName = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerName;
 289:                                  customerAddress = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerAddress;
 290:   
 291:                                  if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes.Count > 0)
 292:                                  {
 293:                                      var vv = (from srt in serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes
 294:                                                group srt by new { srt.TypeId, srt.Value } into g
 295:                                                select new //Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 296:                                                {
 297:                                                    TypeId1Value = g.Max(v => v.TypeId == 1 ? v.Value : ""),
 298:                                                    TypeId2Value = g.Max(v => v.TypeId == 2 ? v.Value : ""),
 299:                                                    TypeId4Value = g.Max(v => v.TypeId == 4 ? v.Value : ""),
 300:                                                    TypeId5Value = g.Max(v => v.TypeId == 5 ? v.Value : ""),
 301:                                                }).ToList();
 302:   
 303:                                      typeId1 = string.Empty;
 304:                                      typeId2 = string.Empty;
 305:                                      typeId4 = string.Empty;
 306:                                      typeId5 = string.Empty;
 307:   
 308:                                      foreach (var w in vv)
 309:                                      {
 310:                                          if (!string.IsNullOrEmpty(w.TypeId1Value)) typeId1 = w.TypeId1Value;
 311:                                          if (!string.IsNullOrEmpty(w.TypeId2Value)) typeId2 = w.TypeId2Value;
 312:                                          if (!string.IsNullOrEmpty(w.TypeId4Value)) typeId4 = w.TypeId4Value;
 313:                                          if (!string.IsNullOrEmpty(w.TypeId5Value)) typeId5 = w.TypeId5Value;
 314:                                      }
 315:                                  }
 316:                                  else
 317:                                  {
 318:                                      customerId = 0;
 319:                                      customerName = string.Empty;
 320:                                      customerAddress = string.Empty;
 321:   
 322:                                      typeId1 = string.Empty;
 323:                                      typeId2 = string.Empty;
 324:                                      typeId4 = string.Empty;
 325:                                      typeId5 = string.Empty;
 326:                                  }
 327:                              }
 328:                              else
 329:                              {
 330:                                  customerId = 0;
 331:                                  customerName = string.Empty;
 332:                                  customerAddress = string.Empty;
 333:   
 334:                                  typeId1 = string.Empty;
 335:                                  typeId2 = string.Empty;
 336:                                  typeId4 = string.Empty;
 337:                                  typeId5 = string.Empty;
 338:                              }
 339:                          }
 340:                          else
 341:                          {
 342:                              customerId = 0;
 343:                              customerName = string.Empty;
 344:                              customerAddress = string.Empty;
 345:   
 346:                              typeId1 = string.Empty;
 347:                              typeId2 = string.Empty;
 348:                              typeId4 = string.Empty;
 349:                              typeId5 = string.Empty;
 350:                          }
 351:                      }
 352:                      else
 353:                      {
 354:                          customerId = 0;
 355:                          customerName = string.Empty;
 356:                          customerAddress = string.Empty;
 357:   
 358:                          typeId1 = string.Empty;
 359:                          typeId2 = string.Empty;
 360:                          typeId4 = string.Empty;
 361:                          typeId5 = string.Empty;
 362:                      }
 363:                  }
 364:                  else
 365:                  {
 366:                      if (serviceToServiceRequestDictionary.ContainsKey(u))
 367:                      {
 368:                          customerId = serviceToServiceRequestDictionary[u].CustomerId;
 369:                          customerName = serviceToServiceRequestDictionary[u].CustomerName;
 370:                          customerAddress = serviceToServiceRequestDictionary[u].CustomerAddress;
 371:   
 372:                          if (serviceToServiceRequestDictionary[u].ServiceRequestTypes.Count > 0)
 373:                          {
 374:                              var vv = (from srt in serviceToServiceRequestDictionary[u].ServiceRequestTypes
 375:                                        group srt by new { srt.TypeId, srt.Value } into g
 376:                                        select new //Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 377:                                        {
 378:                                            TypeId1Value = g.Max(v => v.TypeId == 1 ? v.Value : ""),
 379:                                            TypeId2Value = g.Max(v => v.TypeId == 2 ? v.Value : ""),
 380:                                            TypeId4Value = g.Max(v => v.TypeId == 4 ? v.Value : ""),
 381:                                            TypeId5Value = g.Max(v => v.TypeId == 5 ? v.Value : ""),
 382:                                        }).FirstOrDefault(); //.SingleOrDefault();
 383:   
 384:                              typeId1 = vv.TypeId1Value;
 385:                              typeId2 = vv.TypeId2Value;
 386:                              typeId4 = vv.TypeId4Value;
 387:                              typeId5 = vv.TypeId5Value;
 388:                          }
 389:                          else
 390:                          {
 391:                              typeId1 = string.Empty;
 392:                              typeId2 = string.Empty;
 393:                              typeId4 = string.Empty;
 394:                              typeId5 = string.Empty;
 395:                          }
 396:                      }
 397:                      else
 398:                      {
 399:                          customerId = 0;
 400:                          customerName = string.Empty;
 401:                          customerAddress = string.Empty;
 402:   
 403:                          typeId1 = string.Empty;
 404:                          typeId2 = string.Empty;
 405:                          typeId4 = string.Empty;
 406:                          typeId5 = string.Empty;
 407:                      }
 408:                  }
 409:   
 410:                  s = service + "\t" + alias + "\t" + area + "\t" + block + "\t" + street + "\t" + premisesOld + "\t" + paci + "\t" + customerId + "\t" + customerName + "\t" + customerAddress + "\t" + typeId1 + "\t" + typeId2 + "\t" + typeId4 + "\t" + typeId5 + "";
 411:                  sb.AppendLine(s);
 412:   
 413:              }
 414:          }
 415:   
 416:          ////////////////////////////////////////////////////////////////////////////
 417:   
 418:          /// <summary>
 419:          ///
 420:          /// </summary>
 421:          public static List<Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> ServicesWithinSiteWithRelevantServiceRequestTypeList(int siteId)
 422:          {
 423:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
 424:              List<int> siteKuwaitNgnAreaIdList;
 425:              List<Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> list;
 426:              Dictionary<int, string> areaIdToSymbolDictionary;
 427:   
 428:              areaIdToSymbolDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToSymbolDictionary;
 429:   
 430:              site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();
 431:              siteKuwaitNgnAreaIdList = (from kna in site.KuwaitNgnAreas select kna.Id).ToList();
 432:   
 433:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 434:              {
 435:                  var serviceAreaIdTypeIdValue = (from srs in db.ServiceRequestServices
 436:                                                  join sr in db.ServiceRequests on srs.Id equals sr.ServiceRequestService.Id
 437:                                                  join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id
 438:                                                  where srs.Provisioned == true && siteKuwaitNgnAreaIdList.Contains(sr.AreaId)
 439:                                                  select new
 440:                                                  {
 441:                                                      Service = srs.Service,
 442:                                                      AreaId = sr.AreaId,
 443:                                                      TypeId = srt.TypeId,
 444:                                                      Value = srt.Value
 445:                                                  }
 446:                                      ).ToList();
 447:   
 448:                  list = (from a in serviceAreaIdTypeIdValue
 449:                          group a by new { a.Service, a.AreaId } into g
 450:                          select new Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 451:                          {
 452:                              Service = g.Key.Service,
 453:                              AreaSymbol = areaIdToSymbolDictionary[g.Key.AreaId],
 454:                              TypeId1Value = g.Max(u => u.TypeId == 1 ? u.Value : "?"),
 455:                              TypeId2Value = g.Max(u => u.TypeId == 2 ? u.Value : "?"),
 456:                              TypeId3Value = g.Max(u => u.TypeId == 3 ? u.Value : "?"),
 457:                              TypeId4Value = g.Max(u => u.TypeId == 4 ? u.Value : "?"),
 458:                              TypeId5Value = g.Max(u => u.TypeId == 5 ? u.Value : "?"),
 459:                          }).ToList();
 460:   
 461:              }
 462:   
 463:              return list;
 464:          }
 465:   
 466:          ////////////////////////////////////////////////////////////////////////////
 467:   
 468:          /// <summary>
 469:          ///
 470:          /// </summary>
 471:          public static Dictionary<string, int> ServiceRequestServiceServiceToKuwaitNgnAreaIdDictionary
 472:          {
 473:              get
 474:              {
 475:                  string accessId;
 476:                  Dictionary<string, string> serviceRequestServiceServiceToAccessIdDictionary;
 477:                  Dictionary<string, int> dictionary, accessIdToKuwaitNgnAreaIdDictionary;
 478:   
 479:                  serviceRequestServiceServiceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ServiceToAccessIdDictionary;
 480:                  accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 481:   
 482:                  if (serviceRequestServiceServiceToAccessIdDictionary.Count > 0)
 483:                  {
 484:                      dictionary = new Dictionary<string, int>(serviceRequestServiceServiceToAccessIdDictionary.Count);
 485:   
 486:                      foreach (string s in serviceRequestServiceServiceToAccessIdDictionary.Keys)
 487:                      {
 488:                          accessId = serviceRequestServiceServiceToAccessIdDictionary[s].ToString();
 489:   
 490:                          if (accessId != null)
 491:                          {
 492:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
 493:                              {
 494:                                  dictionary[s] = accessIdToKuwaitNgnAreaIdDictionary[accessId];
 495:                              }
 496:                          }
 497:                          else dictionary[s] = 0;
 498:                      }
 499:                  }
 500:                  else
 501:                  {
 502:                      dictionary = new Dictionary<string, int>();
 503:                  }
 504:   
 505:                  return dictionary;
 506:              }
 507:          }
 508:   
 509:          ////////////////////////////////////////////////////////////////////////////
 510:   
 511:          /// <summary>
 512:          ///
 513:          /// </summary>
 514:          public static Dictionary<string, int> ServiceServiceToKuwaitNgnAreaIdDictionary
 515:          {
 516:              get
 517:              {
 518:                  string accessId;
 519:                  Dictionary<string, string> serviceServiceToAccessIdDictionary;
 520:                  Dictionary<string, int> dictionary, accessIdToKuwaitNgnAreaIdDictionary;
 521:   
 522:                  serviceServiceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceIdToAccessIdDictionary;
 523:                  accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 524:   
 525:                  if (serviceServiceToAccessIdDictionary.Count > 0)
 526:                  {
 527:                      dictionary = new Dictionary<string, int>(serviceServiceToAccessIdDictionary.Count);
 528:   
 529:                      foreach (string s in serviceServiceToAccessIdDictionary.Keys)
 530:                      {
 531:                          accessId = serviceServiceToAccessIdDictionary[s].ToString();
 532:   
 533:                          if (accessId != null)
 534:                          {
 535:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
 536:                              {
 537:                                  dictionary[s] = accessIdToKuwaitNgnAreaIdDictionary[accessId];
 538:                              }
 539:                          }
 540:                          else dictionary[s] = 0;
 541:                      }
 542:                  }
 543:                  else
 544:                  {
 545:                      dictionary = new Dictionary<string, int>();
 546:                  }
 547:   
 548:                  return dictionary;
 549:              }
 550:          }
 551:   
 552:          ////////////////////////////////////////////////////////////////////////////
 553:   
 554:          /// <summary>
 555:          ///
 556:          /// </summary>
 557:          public static void DeleteDatabaseRecordsThatReferenceNonExistantOltsInNdd()
 558:          {
 559:              string sqlWhereString;
 560:              StringBuilder sb;
 561:              List<int> list;
 562:   
 563:              sb = new StringBuilder();
 564:   
 565:              list = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();
 566:   
 567:              if (list.Count > 40 && list.Count < 100)
 568:              {
 569:                  sqlWhereString = string.Empty;
 570:   
 571:                  foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";
 572:                  sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);
 573:   
 574:                  sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 575:                  sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Id"));
 576:   
 577:                  sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 578:                  sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Id"));
 579:   
 580:                  sb.AppendLine("delete from Events where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 581:   
 582:                  sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 583:                  sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Id"));
 584:   
 585:                  sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "EmsOnt_Id"));
 586:                  sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "Id"));
 587:   
 588:                  sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 589:                  sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 590:   
 591:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));
 592:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "Id"));
 593:   
 594:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 595:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 596:   
 597:                  sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Id"));
 598:                  sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Olt"));
 599:   
 600:                  // for testing
 601:                  //sb = sb.Replace("delete from","select * from");
 602:   
 603:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 604:                  {
 605:                      db.Database.ExecuteSqlCommand(sb.ToString());
 606:                  }
 607:              }
 608:              else
 609:              {
 610:                  throw new Exception("list.Count <= 40 || list.Count >= 100");
 611:              }
 612:          }
 613:   
 614:          ////////////////////////////////////////////////////////////////////////////
 615:   
 616:          /// <summary>
 617:          ///
 618:          /// </summary>
 619:          public static void DeleteDatabaseRecordsThatReferenceAnUnrelatedForeignKeyBySimilarityOfId()
 620:          {
 621:              int fixedLengthOfOntId, fixedLengthOfAccessId;
 622:              StringBuilder sb;
 623:   
 624:              sb = new StringBuilder();
 625:   
 626:              /*
 627:      --delete from OntOntPots where substring(Id,1, 16) not like Ont_Id 
 628:      --delete from OntServiceVoips where substring(Id,1, 16) not like Ont_Id 
 629:      --delete from EmsOntSipInfoes where substring(Id,1, 16) not like EmsOnt_Id 
 630:      --delete from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id 
 631:  
 632:      --delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id
 633:      --delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id)
 634:      --delete from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id
 635:               */
 636:   
 637:              fixedLengthOfOntId = Ia.Ngn.Cl.Model.Business.Default.FixedLengthOfOntId;
 638:              fixedLengthOfAccessId = Ia.Ngn.Cl.Model.Business.Access.FixedLengthOfId;
 639:   
 640:              if (fixedLengthOfOntId > 10 && fixedLengthOfAccessId > 10)
 641:              {
 642:                  sb.AppendLine("delete from OntOntPots where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");
 643:                  sb.AppendLine("delete from OntServiceVoips where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");
 644:                  sb.AppendLine("delete from EmsOntSipInfoes where substring(Id,1, " + fixedLengthOfOntId + ") not like EmsOnt_Id");
 645:   
 646:                  sb.AppendLine("delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id");
 647:                  sb.AppendLine("delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id)");
 648:                  sb.AppendLine("delete from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id");
 649:   
 650:                  // for testing
 651:                  //sb = sb.Replace("delete from","select * from");
 652:   
 653:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 654:                  {
 655:                      db.Database.ExecuteSqlCommand(sb.ToString());
 656:                  }
 657:              }
 658:              else
 659:              {
 660:                  throw new Exception("Ia.Ngn.Cl.Model.Business.Default.FixedLengthOfOntId and/or Ia.Ngn.Cl.Model.Business.Access.FixedLengthOfId is not proper");
 661:              }
 662:          }
 663:   
 664:          ////////////////////////////////////////////////////////////////////////////
 665:   
 666:          /// <summary>
 667:          ///
 668:          /// </summary>
 669:          public static void ConstructDeleteSqlForOracleDatabaseRecordsThatReferenceNonExistantOltsInNdd(out string ontDataSql, out string ontDetailsSql)
 670:          {
 671:              string sqlWhereString;
 672:              List<int> list;
 673:   
 674:              list = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();
 675:   
 676:              if (list.Count > 40 && list.Count < 100)
 677:              {
 678:                  if (list.Count > 0)
 679:                  {
 680:                      sqlWhereString = string.Empty;
 681:   
 682:                      foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";
 683:                      sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);
 684:   
 685:                      ontDataSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));
 686:   
 687:                      ontDetailsSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));
 688:                  }
 689:                  else
 690:                  {
 691:                      ontDataSql = ontDetailsSql = string.Empty;
 692:   
 693:                      throw new Exception("list.Count == 0");
 694:                  }
 695:              }
 696:              else
 697:              {
 698:                  throw new Exception("list.Count <= 40 || list.Count >= 100");
 699:              }
 700:          }
 701:   
 702:          ////////////////////////////////////////////////////////////////////////////
 703:   
 704:          /// <summary>
 705:          /// Compare OntList with ServiceRequestOntList to extract extra or wrong ONTs from the later
 706:          /// </summary>
 707:          private static List<string> ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList()
 708:          {
 709:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont ont;
 710:              List<string> list;
 711:              List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
 712:              Dictionary<string, Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont> ontAccessIdToOntDictionary;
 713:   
 714:              list = new List<string>();
 715:   
 716:              ontAccessIdToOntDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntDictionary;
 717:   
 718:              serviceRequestOntList = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.List;
 719:   
 720:              foreach (Ia.Ngn.Cl.Model.ServiceRequestOnt sro in serviceRequestOntList)
 721:              {
 722:                  if (ontAccessIdToOntDictionary.ContainsKey(sro.Id))
 723:                  {
 724:                      ont = ontAccessIdToOntDictionary[sro.Id];
 725:   
 726:                      if (ont.Pon.PonGroup.Symbol == sro.AreaSymbol && ont.Pon.Number == sro.Pon && ont.Number == sro.Ont)
 727:                      {
 728:                      }
 729:                      else
 730:                      {
 731:                          //sb.AppendLine(" wrong:" + w.Id + "'");
 732:                          list.Add(sro.Id);
 733:                      }
 734:                  }
 735:                  else
 736:                  {
 737:                      //sb.AppendLine(" missing: " + w.Id + "'");
 738:                      list.Add(sro.Id);
 739:                  }
 740:              }
 741:   
 742:              return list;
 743:          }
 744:   
 745:          ////////////////////////////////////////////////////////////////////////////
 746:   
 747:          /// <summary>
 748:          ///
 749:          /// </summary>
 750:          public static void DeleteOfnDatabaseRecordsAndConstructDeleteSqlForOracleDatabaseRecordsThatReferenceNonExistantOntsInNdd(out string ontDataSql, out string ontDetailsSql)
 751:          {
 752:              string sqlWhereString;
 753:              StringBuilder sb;
 754:              List<string> list;
 755:   
 756:              sb = new StringBuilder();
 757:   
 758:              ontDataSql = ontDetailsSql = string.Empty;
 759:   
 760:              list = ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList();
 761:   
 762:              if (list.Count < 2000)
 763:              {
 764:                  if (list.Count > 0)
 765:                  {
 766:                      DeleteOfnDatabaseRecordsThatReferenceNonExistantOntsInNdd(list);
 767:   
 768:                      sqlWhereString = string.Empty;
 769:   
 770:                      foreach (string s in list) sqlWhereString += "{variable} = '" + s + "' or ";
 771:                      sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 4, 4);
 772:   
 773:                      ontDetailsSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));
 774:   
 775:                      ontDataSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));
 776:                  }
 777:                  else
 778:                  {
 779:   
 780:                  }
 781:              }
 782:              else
 783:              {
 784:                  throw new Exception("list.Count >=2000");
 785:              }
 786:          }
 787:   
 788:          ////////////////////////////////////////////////////////////////////////////
 789:   
 790:          /// <summary>
 791:          ///
 792:          /// </summary>
 793:          private static void DeleteOfnDatabaseRecordsThatReferenceNonExistantOntsInNdd(List<string> list)
 794:          {
 795:              string sqlWhereString;
 796:              StringBuilder sb;
 797:   
 798:              sb = new StringBuilder();
 799:   
 800:              if (list.Count > 0)
 801:              {
 802:                  sqlWhereString = string.Empty;
 803:   
 804:                  foreach (string s in list) sqlWhereString += "{variable} = '" + s + "' or ";
 805:                  sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 4, 4);
 806:   
 807:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));
 808:   
 809:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 810:   
 811:                  // for testing
 812:                  //sb = sb.Replace("delete from","select * from");
 813:   
 814:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 815:                  {
 816:                      db.Database.ExecuteSqlCommand(sb.ToString());
 817:                  }
 818:              }
 819:              else
 820:              {
 821:   
 822:              }
 823:          }
 824:   
 825:          ////////////////////////////////////////////////////////////////////////////
 826:   
 827:          /// <summary>
 828:          ///
 829:          /// </summary>
 830:          public static int NextVacantFlatTermIdForHuaweiEmsOntEquipmentTypeIdAndNokiaGatewayId(Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentType equipmentType, int gatewayId)
 831:          {
 832:              int ft;
 833:              List<int> list;
 834:   
 835:              list = Ia.Ngn.Cl.Model.Data.Nokia.AgcfEndpoint.UsedFlatTermIdListForGatewayId(gatewayId);
 836:   
 837:              if (list.Count == 0) ft = 1; // this means agcfEndpoint does not exist for any number and the box is empty, and we should create agcfEndpoint at 1
 838:              else
 839:              {
 840:                  list = Ia.Cl.Model.Default.ExcludedNumberListFromNumberListWithinRange(list, Ia.Ngn.Cl.Model.Business.Huawei.Ems.PossibleNumberOfTdForOntEquipmentType(equipmentType));
 841:   
 842:                  if (list.Count > 0) ft = list[0];
 843:                  else ft = 0; // something went wrong
 844:              }
 845:   
 846:              return ft;
 847:          }
 848:   
 849:          ////////////////////////////////////////////////////////////////////////////
 850:          ////////////////////////////////////////////////////////////////////////////
 851:      }
 852:  }