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

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 Optical Fiber Network (OFN) data model.

   1:  using Microsoft.EntityFrameworkCore;
   2:  using System;
   3:  using System.Collections;
   4:  using System.Collections.Generic;
   5:  using System.Data;
   6:  using System.Linq;
   7:  using System.Text;
   8:   
   9:  namespace Ia.Ngn.Cl.Model.Data
  10:  {
  11:      ////////////////////////////////////////////////////////////////////////////
  12:   
  13:      /// <summary publish="true">
  14:      /// Default support class for Optical Fiber Network (OFN) data model.
  15:      /// </summary>
  16:      /// 
  17:      /// <remarks> 
  18:      /// Copyright © 2006-2020 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
  19:      ///
  20:      /// 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
  21:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  22:      ///
  23:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  24:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  25:      /// 
  26:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  27:      /// 
  28:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  29:      /// </remarks> 
  30:      public class Default
  31:      {
  32:          private static StringBuilder logStringBuilder = new StringBuilder();
  33:   
  34:          ////////////////////////////////////////////////////////////////////////////
  35:   
  36:          /// <summary>
  37:          ///
  38:          /// </summary>
  39:          public Default() { }
  40:   
  41:          ////////////////////////////////////////////////////////////////////////////
  42:   
  43:          /// <summary>
  44:          ///
  45:          /// </summary>
  46:          public static List<string> NewOntEventAndAccessBySerialInNoteList()
  47:          {
  48:              List<string> list;
  49:   
  50:              list = new List<string>();
  51:   
  52:              var bList = Ia.Ngn.Cl.Model.Data.Event.NewOntList;
  53:   
  54:              //var aList = Ia.Ngn.Cl.Model.Data.Access.BySerialInNote();
  55:   
  56:   
  57:              return list;
  58:          }
  59:   
  60:          ////////////////////////////////////////////////////////////////////////////
  61:   
  62:          /// <summary>
  63:          ///
  64:          /// </summary>
  65:          public static List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> DifferentOntNameAndStatisticalOntNameList(int siteId)
  66:          {
  67:              string address;
  68:              Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
  69:              Ia.Ngn.Cl.Model.Access statisticalAccess;
  70:              Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName serviceCustomerAddressAccessStatisticalAccessName;
  71:   
  72:              var level = string.Empty;
  73:   
  74:              var site = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList
  75:                          where s.Id == siteId
  76:                          select s).SingleOrDefault();
  77:   
  78:   
  79:              var areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
  80:   
  81:              var list = new List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName>();
  82:   
  83:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
  84:              {
  85:                  var siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList
  86:                                              where r.Site.Id == siteId
  87:                                              select r).SelectMany(z => z.DomainList).ToList();
  88:   
  89:                  var hashTable = Ia.Ngn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);
  90:   
  91:                  var serviceRequestServiceServiceAccessList = (from srs in db.ServiceRequestServices
  92:                                                                select new { srs.Service, srs.Access }).AsNoTracking().ToList();
  93:   
  94:                  serviceRequestServiceServiceAccessList = (from srs in serviceRequestServiceServiceAccessList
  95:                                                            where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString()))
  96:                                                            select srs).ToList();
  97:   
  98:                  var areaIdList = site.KuwaitNgnAreas.Select(i => i.Id).ToList();
  99:   
 100:                  /*
 101:                  var accessList = (from a in db.Accesses
 102:                                    where areaIdList.Contains(a.AreaId)
 103:                                    select a).AsNoTracking().ToList();
 104:                  */
 105:   
 106:                  foreach (var srs in serviceRequestServiceServiceAccessList)
 107:                  {
 108:                      serviceCustomerAddressAccessStatisticalAccessName = new Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName();
 109:   
 110:                      if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();
 111:                      else address = null;
 112:   
 113:                      serviceCustomerAddressAccessStatisticalAccessName.Service = srs.Service;
 114:                      serviceCustomerAddressAccessStatisticalAccessName.CustomerAddress = address;
 115:                      serviceCustomerAddressAccessStatisticalAccessName.Access = srs.Access;
 116:   
 117:                      if (srs.Access != null) serviceCustomerAddressAccessStatisticalAccessName.OntAddress = srs.Access.Address;
 118:   
 119:                      serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(srs.Service, address, out level);
 120:   
 121:                      // below: this will skip Sabah Al-Salem area
 122:                      //if (l.CustomerAddress != null && !l.CustomerAddress.Contains("صباح"))
 123:                      //{
 124:                      statisticalAccess = null; // Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList);
 125:   
 126:                      if (statisticalAccess != null)
 127:                      {
 128:                          serviceCustomerAddressAccessStatisticalAccessName.Block = statisticalAccess.Block;
 129:                          serviceCustomerAddressAccessStatisticalAccessName.Street = statisticalAccess.Street;
 130:                          serviceCustomerAddressAccessStatisticalAccessName.PremisesOld = statisticalAccess.PremisesOld;
 131:                          serviceCustomerAddressAccessStatisticalAccessName.PremisesNew = statisticalAccess.PremisesNew;
 132:                          serviceCustomerAddressAccessStatisticalAccessName.KuwaitNgnAreaNameArabicName = areaIdToNameArabicNameDictionary[statisticalAccess.AreaId];
 133:   
 134:                          serviceCustomerAddressAccessStatisticalAccessName.StatisticalAccessName = statisticalAccess.Name;
 135:   
 136:                          /*
 137:                          if (nokiaInitialInstallationListHashTable.ContainsKey(statisticalAccess.Id))
 138:                          {
 139:                              l.Note = nokiaInitialInstallationListHashTable[statisticalAccess.Id].ToString();
 140:                          }
 141:                          */
 142:                          //else l.Note = "No: " + srs.Access.Id;
 143:                      }
 144:   
 145:                      if (serviceAddress.AreaId != 0)
 146:                      {
 147:                          serviceCustomerAddressAccessStatisticalAccessName.StatisticalAddress = serviceAddress.Address;
 148:   
 149:                          if (srs.Access != null) serviceCustomerAddressAccessStatisticalAccessName.AccessName = serviceCustomerAddressAccessStatisticalAccessName.Access.Name;
 150:   
 151:                          //if (l.OntName != null && l.OntName == l.StatisticalOntName) l.Note = Ia.Cl.Model.Default.YesNo(true);
 152:                          //else l.Note = Ia.Cl.Model.Default.YesNo(false);
 153:   
 154:                          //l.Note += " (" + level + ")";
 155:                      }
 156:                      else
 157:                      {
 158:                          //l.Note = Ia.Cl.Model.Default.YesNo(false);
 159:   
 160:                          //l.Note += " (AreaId zero)";
 161:                      }
 162:   
 163:                      list.Add(serviceCustomerAddressAccessStatisticalAccessName);
 164:                      //}
 165:                      //else
 166:                      //{
 167:   
 168:                      //}
 169:                  }
 170:              }
 171:   
 172:              return list;
 173:          }
 174:   
 175:          ////////////////////////////////////////////////////////////////////////////
 176:   
 177:          /// <summary>
 178:          ///
 179:          /// </summary>
 180:          public static List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> DifferentOntNameAndStatisticalOntNameList2(int siteId)
 181:          {
 182:              string address, level;
 183:              Hashtable hashTable; //, nokiaInitialInstallationListHashTable;
 184:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
 185:              Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
 186:              Ia.Ngn.Cl.Model.Access statisticalAccess;
 187:              Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName l;
 188:   
 189:              List<int> siteRouterDomainList, areaIdList;
 190:              List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> list;
 191:              //List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 192:              //List<Ia.Ngn.Cl.Model.Ui.Access> accessList;
 193:              Dictionary<int, string> areaIdToNameArabicNameDictionary;
 194:   
 195:              level = string.Empty;
 196:              areaIdList = new List<int>();
 197:   
 198:              site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList
 199:                      where a.Id == siteId
 200:                      select a).SingleOrDefault();
 201:   
 202:              var ontAccessIdToOntAccessNameDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
 203:   
 204:              areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
 205:   
 206:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 207:              {
 208:                  siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList
 209:                                          where r.Site.Id == siteId
 210:                                          select r).SelectMany(z => z.DomainList).ToList();
 211:                  //siteRouterDomainList = new List<int> { 2538 };
 212:   
 213:                  //serviceRequestList = (from sr in db.ServiceRequests where siteRouterDomainList.Contains(sr.Number / 10000) || siteRouterDomainList.Contains(sr.Number / 1000) select sr).ToList();
 214:   
 215:                  hashTable = Ia.Ngn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);
 216:   
 217:                  var serviceRequestServiceList = (from srs in db.ServiceRequestServices
 218:                                                   where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString()))
 219:                                                   select new { srs.Service, AccessId = (srs.Access != null) ? srs.Access.Id : string.Empty }).ToList();
 220:   
 221:                  areaIdList = site.KuwaitNgnAreas.Select(i => i.Id).ToList();
 222:                  //areaIdList = new List<int> { 60618 };
 223:   
 224:                  //accessList = null;// (from a in db.Accesses where areaIdList.Contains(a.AreaId) select a).ToList();
 225:   
 226:                  list = new List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName>();
 227:   
 228:                  foreach (var srs in serviceRequestServiceList)
 229:                  {
 230:                      l = new Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName();
 231:   
 232:                      if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();
 233:                      else address = null;
 234:   
 235:                      l.Service = srs.Service;
 236:                      l.CustomerAddress = address;
 237:   
 238:                      if (ontAccessIdToOntAccessNameDictionary.ContainsKey(srs.AccessId)) l.AccessId = srs.AccessId;
 239:                      else l.Access = null;
 240:   
 241:                      if (!string.IsNullOrEmpty(srs.AccessId)) l.OntAddress = string.Empty;// srs.Access.Address;
 242:   
 243:                      //------------------
 244:                      serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(srs.Service, address, out level);
 245:   
 246:                      statisticalAccess = null;// Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList);
 247:   
 248:                      if (statisticalAccess != null)
 249:                      {
 250:                          l.Block = statisticalAccess.Block;
 251:                          l.Street = statisticalAccess.Street;
 252:                          l.PremisesOld = statisticalAccess.PremisesOld;
 253:                          l.PremisesNew = statisticalAccess.PremisesNew;
 254:                          l.KuwaitNgnAreaNameArabicName = areaIdToNameArabicNameDictionary[statisticalAccess.AreaId];
 255:   
 256:                          l.StatisticalAccessName = string.Empty;// statisticalAccess.Name;
 257:   
 258:                          /*
 259:                          if (nokiaInitialInstallationListHashTable.ContainsKey(statisticalAccess.Id))
 260:                          {
 261:                              l.Note = nokiaInitialInstallationListHashTable[statisticalAccess.Id].ToString();
 262:                          }
 263:                          */
 264:                          //else l.Note = "No: " + srs.Access.Id;
 265:                      }
 266:   
 267:                      if (serviceAddress.AreaId != 0)
 268:                      {
 269:                          l.StatisticalAddress = string.Empty;// serviceAddress.Address;
 270:   
 271:                          if (!string.IsNullOrEmpty(srs.AccessId))
 272:                          {
 273:                              l.AccessName = string.Empty;// l.Access.Name;
 274:                          }
 275:   
 276:                          //if (l.OntName != null && l.OntName == l.StatisticalOntName) l.Note = Ia.Cl.Model.Default.YesNo(true);
 277:                          //else l.Note = Ia.Cl.Model.Default.YesNo(false);
 278:   
 279:                          //l.Note += " (" + level + ")";
 280:                      }
 281:                      else
 282:                      {
 283:                          //l.Note = Ia.Cl.Model.Default.YesNo(false);
 284:   
 285:                          //l.Note += " (AreaId zero)";
 286:                      }
 287:   
 288:                      list.Add(l);
 289:                  }
 290:              }
 291:   
 292:              return list;
 293:          }
 294:   
 295:          ////////////////////////////////////////////////////////////////////////////
 296:   
 297:          /// <summary>
 298:          ///
 299:          /// </summary>
 300:          public static void SiteSerivceAndEmsOntAndAccessAndServiceRequestAndServiceRequestTypeInformation(int siteId, out StringBuilder sb)
 301:          {
 302:              int areaId, customerId;
 303:              string s, accessId;
 304:              string service, alias, area, block, street, premisesOld, paci, customerName, customerAddress, typeId1, typeId2, typeId4, typeId5;
 305:   
 306:              List<int> siteRouterDomainList, areaIdList;
 307:              List<string> serviceList;
 308:              Dictionary<int, string> areaIdToNameArabicNameDictionary;
 309:   
 310:              sb = new StringBuilder();
 311:   
 312:              areaIdList = new List<int>();
 313:   
 314:              /*
 315:  Service2S    ONTs(EMS)    Accesses    Accesses    Accesses    Accesses    Accesses    Service Requests    Service Requests    Service Requests    Service Requests Types    Service Requests    Service Requests    Service Requests
 316:  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
 317:               */
 318:   
 319:              var site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();
 320:   
 321:              siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList where r.Site.Id == siteId select r).SelectMany(z => z.DomainList).ToList();
 322:   
 323:              areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
 324:   
 325:              serviceList = Ia.Ngn.Cl.Model.Data.Service2.List(siteRouterDomainList);
 326:   
 327:              var serviceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceToAccessIdDictionary;
 328:   
 329:              var idToAccessDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToAccessDictionary;
 330:   
 331:              var accessIdToOntDictionary = Ia.Ngn.Cl.Model.Data.Huawei.Ont.AccessIdToOntDictionary;
 332:   
 333:              var serviceToServiceRequestServiceDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ServiceToServiceRequestServiceDictionary(siteRouterDomainList);
 334:   
 335:              var serviceToServiceRequestDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToServiceRequestDictionary(siteRouterDomainList);
 336:   
 337:   
 338:              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";
 339:              sb.AppendLine(s);
 340:   
 341:              foreach (string u in serviceList)
 342:              {
 343:                  service = u;
 344:   
 345:                  if (serviceToAccessIdDictionary.ContainsKey(u))
 346:                  {
 347:                      accessId = serviceToAccessIdDictionary[u];
 348:   
 349:                      if (idToAccessDictionary.ContainsKey(accessId))
 350:                      {
 351:                          areaId = idToAccessDictionary[accessId].AreaId;
 352:                          block = idToAccessDictionary[accessId].Block;
 353:                          street = idToAccessDictionary[accessId].Street;
 354:                          premisesOld = idToAccessDictionary[accessId].PremisesOld;
 355:                          paci = idToAccessDictionary[accessId].Paci;
 356:   
 357:                          if (areaIdToNameArabicNameDictionary.ContainsKey(areaId)) area = areaIdToNameArabicNameDictionary[areaId];
 358:                          else area = string.Empty;
 359:   
 360:                          if (accessIdToOntDictionary.ContainsKey(accessId))
 361:                          {
 362:                              alias = accessIdToOntDictionary[accessId].ALIAS;
 363:                          }
 364:                          else
 365:                          {
 366:                              alias = string.Empty;
 367:                          }
 368:                      }
 369:                      else
 370:                      {
 371:                          block = string.Empty;
 372:                          street = string.Empty;
 373:                          premisesOld = string.Empty;
 374:                          paci = string.Empty;
 375:                          area = string.Empty;
 376:   
 377:                          alias = string.Empty;
 378:                      }
 379:                  }
 380:                  else
 381:                  {
 382:                      block = string.Empty;
 383:                      street = string.Empty;
 384:                      premisesOld = string.Empty;
 385:                      paci = string.Empty;
 386:                      area = string.Empty;
 387:   
 388:                      alias = string.Empty;
 389:                  }
 390:   
 391:   
 392:                  if (serviceToServiceRequestServiceDictionary.ContainsKey(u))
 393:                  {
 394:                      if (serviceToServiceRequestServiceDictionary[u].Provisioned)
 395:                      {
 396:                          if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.Count > 0)
 397:                          {
 398:                              //var bb = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault();
 399:   
 400:                              if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault() != null)
 401:                              {
 402:                                  customerId = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerId;
 403:                                  customerName = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerName;
 404:                                  customerAddress = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerAddress;
 405:   
 406:                                  if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes.Count > 0)
 407:                                  {
 408:                                      var vv = (from srt in serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes
 409:                                                group srt by new { srt.TypeId, srt.Value } into g
 410:                                                select new //Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 411:                                                {
 412:                                                    TypeId1Value = g.Max(v => v.TypeId == 1 ? v.Value : ""),
 413:                                                    TypeId2Value = g.Max(v => v.TypeId == 2 ? v.Value : ""),
 414:                                                    TypeId4Value = g.Max(v => v.TypeId == 4 ? v.Value : ""),
 415:                                                    TypeId5Value = g.Max(v => v.TypeId == 5 ? v.Value : ""),
 416:                                                }).ToList();
 417:   
 418:                                      typeId1 = string.Empty;
 419:                                      typeId2 = string.Empty;
 420:                                      typeId4 = string.Empty;
 421:                                      typeId5 = string.Empty;
 422:   
 423:                                      foreach (var w in vv)
 424:                                      {
 425:                                          if (!string.IsNullOrEmpty(w.TypeId1Value)) typeId1 = w.TypeId1Value;
 426:                                          if (!string.IsNullOrEmpty(w.TypeId2Value)) typeId2 = w.TypeId2Value;
 427:                                          if (!string.IsNullOrEmpty(w.TypeId4Value)) typeId4 = w.TypeId4Value;
 428:                                          if (!string.IsNullOrEmpty(w.TypeId5Value)) typeId5 = w.TypeId5Value;
 429:                                      }
 430:                                  }
 431:                                  else
 432:                                  {
 433:                                      customerId = 0;
 434:                                      customerName = string.Empty;
 435:                                      customerAddress = string.Empty;
 436:   
 437:                                      typeId1 = string.Empty;
 438:                                      typeId2 = string.Empty;
 439:                                      typeId4 = string.Empty;
 440:                                      typeId5 = string.Empty;
 441:                                  }
 442:                              }
 443:                              else
 444:                              {
 445:                                  customerId = 0;
 446:                                  customerName = string.Empty;
 447:                                  customerAddress = string.Empty;
 448:   
 449:                                  typeId1 = string.Empty;
 450:                                  typeId2 = string.Empty;
 451:                                  typeId4 = string.Empty;
 452:                                  typeId5 = string.Empty;
 453:                              }
 454:                          }
 455:                          else
 456:                          {
 457:                              customerId = 0;
 458:                              customerName = string.Empty;
 459:                              customerAddress = string.Empty;
 460:   
 461:                              typeId1 = string.Empty;
 462:                              typeId2 = string.Empty;
 463:                              typeId4 = string.Empty;
 464:                              typeId5 = string.Empty;
 465:                          }
 466:                      }
 467:                      else
 468:                      {
 469:                          customerId = 0;
 470:                          customerName = string.Empty;
 471:                          customerAddress = string.Empty;
 472:   
 473:                          typeId1 = string.Empty;
 474:                          typeId2 = string.Empty;
 475:                          typeId4 = string.Empty;
 476:                          typeId5 = string.Empty;
 477:                      }
 478:                  }
 479:                  else
 480:                  {
 481:                      if (serviceToServiceRequestDictionary.ContainsKey(u))
 482:                      {
 483:                          customerId = serviceToServiceRequestDictionary[u].CustomerId;
 484:                          customerName = serviceToServiceRequestDictionary[u].CustomerName;
 485:                          customerAddress = serviceToServiceRequestDictionary[u].CustomerAddress;
 486:   
 487:                          if (serviceToServiceRequestDictionary[u].ServiceRequestTypes.Count > 0)
 488:                          {
 489:                              var vv = (from srt in serviceToServiceRequestDictionary[u].ServiceRequestTypes
 490:                                        group srt by new { srt.TypeId, srt.Value } into g
 491:                                        select new //Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 492:                                        {
 493:                                            TypeId1Value = g.Max(v => v.TypeId == 1 ? v.Value : ""),
 494:                                            TypeId2Value = g.Max(v => v.TypeId == 2 ? v.Value : ""),
 495:                                            TypeId4Value = g.Max(v => v.TypeId == 4 ? v.Value : ""),
 496:                                            TypeId5Value = g.Max(v => v.TypeId == 5 ? v.Value : ""),
 497:                                        }).FirstOrDefault(); //.SingleOrDefault();
 498:   
 499:                              typeId1 = vv.TypeId1Value;
 500:                              typeId2 = vv.TypeId2Value;
 501:                              typeId4 = vv.TypeId4Value;
 502:                              typeId5 = vv.TypeId5Value;
 503:                          }
 504:                          else
 505:                          {
 506:                              typeId1 = string.Empty;
 507:                              typeId2 = string.Empty;
 508:                              typeId4 = string.Empty;
 509:                              typeId5 = string.Empty;
 510:                          }
 511:                      }
 512:                      else
 513:                      {
 514:                          customerId = 0;
 515:                          customerName = string.Empty;
 516:                          customerAddress = string.Empty;
 517:   
 518:                          typeId1 = string.Empty;
 519:                          typeId2 = string.Empty;
 520:                          typeId4 = string.Empty;
 521:                          typeId5 = string.Empty;
 522:                      }
 523:                  }
 524:   
 525:                  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 + "";
 526:                  sb.AppendLine(s);
 527:   
 528:              }
 529:          }
 530:   
 531:          ////////////////////////////////////////////////////////////////////////////
 532:   
 533:          /// <summary>
 534:          ///
 535:          /// </summary>
 536:          public static List<Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> ServicesWithinSiteWithRelevantServiceRequestTypeList(int siteId)
 537:          {
 538:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
 539:              List<int> siteKuwaitNgnAreaIdList;
 540:              List<Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> list;
 541:              Dictionary<int, string> areaIdToSymbolDictionary;
 542:   
 543:              areaIdToSymbolDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToSymbolDictionary;
 544:   
 545:              site = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where s.Id == siteId select s).SingleOrDefault();
 546:              siteKuwaitNgnAreaIdList = (from kna in site.KuwaitNgnAreas select kna.Id).ToList();
 547:   
 548:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 549:              {
 550:                  var serviceAreaIdTypeIdValue = (from srs in db.ServiceRequestServices
 551:                                                  join sr in db.ServiceRequests on srs.Id equals sr.ServiceRequestService.Id
 552:                                                  join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id
 553:                                                  where srs.Provisioned == true && siteKuwaitNgnAreaIdList.Contains(sr.AreaId)
 554:                                                  select new
 555:                                                  {
 556:                                                      Service = srs.Service,
 557:                                                      AreaId = sr.AreaId,
 558:                                                      TypeId = srt.TypeId,
 559:                                                      Value = srt.Value
 560:                                                  }
 561:                                      ).ToList();
 562:   
 563:                  list = (from a in serviceAreaIdTypeIdValue
 564:                          group a by new { a.Service, a.AreaId } into g
 565:                          select new Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 566:                          {
 567:                              Service = g.Key.Service,
 568:                              AreaSymbol = areaIdToSymbolDictionary[g.Key.AreaId],
 569:                              TypeId1Value = g.Max(u => u.TypeId == 1 ? u.Value : "?"),
 570:                              TypeId2Value = g.Max(u => u.TypeId == 2 ? u.Value : "?"),
 571:                              TypeId3Value = g.Max(u => u.TypeId == 3 ? u.Value : "?"),
 572:                              TypeId4Value = g.Max(u => u.TypeId == 4 ? u.Value : "?"),
 573:                              TypeId5Value = g.Max(u => u.TypeId == 5 ? u.Value : "?"),
 574:                          }).ToList();
 575:   
 576:              }
 577:   
 578:              return list;
 579:          }
 580:   
 581:          ////////////////////////////////////////////////////////////////////////////
 582:   
 583:          /// <summary>
 584:          ///
 585:          /// </summary>
 586:          public static Dictionary<string, int> ServiceRequestServiceServiceToKuwaitNgnAreaIdDictionary
 587:          {
 588:              get
 589:              {
 590:                  string accessId;
 591:                  Dictionary<string, string> serviceRequestServiceServiceToAccessIdDictionary;
 592:                  Dictionary<string, int> dictionary, accessIdToKuwaitNgnAreaIdDictionary;
 593:   
 594:                  serviceRequestServiceServiceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ProvisionedServiceIdToAccessIdDictionary;
 595:                  accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 596:   
 597:                  if (serviceRequestServiceServiceToAccessIdDictionary.Count > 0)
 598:                  {
 599:                      dictionary = new Dictionary<string, int>(serviceRequestServiceServiceToAccessIdDictionary.Count);
 600:   
 601:                      foreach (string s in serviceRequestServiceServiceToAccessIdDictionary.Keys)
 602:                      {
 603:                          accessId = serviceRequestServiceServiceToAccessIdDictionary[s].ToString();
 604:   
 605:                          if (accessId != null)
 606:                          {
 607:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
 608:                              {
 609:                                  dictionary[s] = accessIdToKuwaitNgnAreaIdDictionary[accessId];
 610:                              }
 611:                          }
 612:                          else dictionary[s] = 0;
 613:                      }
 614:                  }
 615:                  else
 616:                  {
 617:                      dictionary = new Dictionary<string, int>();
 618:                  }
 619:   
 620:                  return dictionary;
 621:              }
 622:          }
 623:   
 624:          ////////////////////////////////////////////////////////////////////////////
 625:   
 626:          /// <summary>
 627:          ///
 628:          /// </summary>
 629:          public static Dictionary<string, int> ServiceServiceToKuwaitNgnAreaIdDictionary
 630:          {
 631:              get
 632:              {
 633:                  string accessId;
 634:                  Dictionary<string, string> serviceServiceToAccessIdDictionary;
 635:                  Dictionary<string, int> dictionary, accessIdToKuwaitNgnAreaIdDictionary;
 636:   
 637:                  serviceServiceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceIdToAccessIdDictionary;
 638:                  accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 639:   
 640:                  if (serviceServiceToAccessIdDictionary.Count > 0)
 641:                  {
 642:                      dictionary = new Dictionary<string, int>(serviceServiceToAccessIdDictionary.Count);
 643:   
 644:                      foreach (string s in serviceServiceToAccessIdDictionary.Keys)
 645:                      {
 646:                          accessId = serviceServiceToAccessIdDictionary[s].ToString();
 647:   
 648:                          if (accessId != null)
 649:                          {
 650:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
 651:                              {
 652:                                  dictionary[s] = accessIdToKuwaitNgnAreaIdDictionary[accessId];
 653:                              }
 654:                          }
 655:                          else dictionary[s] = 0;
 656:                      }
 657:                  }
 658:                  else
 659:                  {
 660:                      dictionary = new Dictionary<string, int>();
 661:                  }
 662:   
 663:                  return dictionary;
 664:              }
 665:          }
 666:   
 667:          ////////////////////////////////////////////////////////////////////////////
 668:   
 669:          /// <summary>
 670:          ///
 671:          /// </summary>
 672:          public static void DeleteDatabaseRecordsThatReferenceNonExistantOltsInNdd()
 673:          {
 674:              string sqlWhereString;
 675:              StringBuilder sb;
 676:              List<int> list;
 677:   
 678:              sb = new StringBuilder();
 679:   
 680:              list = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();
 681:   
 682:              if (list.Count > 40 && list.Count < 100)
 683:              {
 684:                  sqlWhereString = string.Empty;
 685:   
 686:                  foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";
 687:                  sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);
 688:   
 689:                  sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 690:                  sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Id"));
 691:   
 692:                  sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 693:                  sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Id"));
 694:   
 695:                  sb.AppendLine("delete from Events where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 696:   
 697:                  sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 698:                  sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Id"));
 699:   
 700:                  sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "EmsOnt_Id"));
 701:                  sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "Id"));
 702:   
 703:                  sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 704:                  sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 705:   
 706:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));
 707:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "Id"));
 708:   
 709:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 710:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 711:   
 712:                  sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Id"));
 713:                  sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Olt"));
 714:   
 715:                  // for testing
 716:                  //sb = sb.Replace("delete from","select * from");
 717:   
 718:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 719:                  {
 720:                      db.Database.ExecuteSqlRaw(sb.ToString());
 721:                  }
 722:              }
 723:              else
 724:              {
 725:                  throw new Exception("list.Count <= 40 || list.Count >= 100");
 726:              }
 727:          }
 728:   
 729:          ////////////////////////////////////////////////////////////////////////////
 730:   
 731:          /// <summary>
 732:          ///
 733:          /// </summary>
 734:          public static void DeleteDatabaseRecordsThatReferenceAnUnrelatedForeignKeyBySimilarityOfId()
 735:          {
 736:              int fixedLengthOfOntId, fixedLengthOfAccessId;
 737:              StringBuilder sb;
 738:   
 739:              sb = new StringBuilder();
 740:   
 741:              /*
 742:      --delete from OntOntPots where substring(Id,1, 16) not like Ont_Id 
 743:      --delete from OntServiceVoips where substring(Id,1, 16) not like Ont_Id 
 744:      --delete from EmsOntSipInfoes where substring(Id,1, 16) not like EmsOnt_Id 
 745:      --delete from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id 
 746:  
 747:      --delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id
 748:      --delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id)
 749:      --delete from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id
 750:               */
 751:   
 752:              fixedLengthOfOntId = Ia.Ngn.Cl.Model.Business.Default.FixedLengthOfOntId;
 753:              fixedLengthOfAccessId = Ia.Ngn.Cl.Model.Business.Access.FixedLengthOfId;
 754:   
 755:              if (fixedLengthOfOntId > 10 && fixedLengthOfAccessId > 10)
 756:              {
 757:                  sb.AppendLine("delete from OntOntPots where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");
 758:                  sb.AppendLine("delete from OntServiceVoips where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");
 759:                  sb.AppendLine("delete from EmsOntSipInfoes where substring(Id,1, " + fixedLengthOfOntId + ") not like EmsOnt_Id");
 760:   
 761:                  sb.AppendLine("delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id");
 762:                  sb.AppendLine("delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id)");
 763:                  sb.AppendLine("delete from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id");
 764:   
 765:                  // for testing
 766:                  //sb = sb.Replace("delete from","select * from");
 767:   
 768:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 769:                  {
 770:                      db.Database.ExecuteSqlRaw(sb.ToString());
 771:                  }
 772:              }
 773:              else
 774:              {
 775:                  throw new Exception("Ia.Ngn.Cl.Model.Business.Default.FixedLengthOfOntId and/or Ia.Ngn.Cl.Model.Business.Access.FixedLengthOfId is not proper");
 776:              }
 777:          }
 778:   
 779:          ////////////////////////////////////////////////////////////////////////////
 780:   
 781:          /// <summary>
 782:          ///
 783:          /// </summary>
 784:          public static void ConstructDeleteSqlForOracleDatabaseRecordsThatReferenceNonExistantOltsInNdd(out string ontDataSql, out string ontDetailsSql)
 785:          {
 786:              string sqlWhereString;
 787:              List<int> list;
 788:   
 789:              list = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();
 790:   
 791:              if (list.Count > 40 && list.Count < 100)
 792:              {
 793:                  if (list.Count > 0)
 794:                  {
 795:                      sqlWhereString = string.Empty;
 796:   
 797:                      foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";
 798:                      sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);
 799:   
 800:                      ontDataSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));
 801:   
 802:                      ontDetailsSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));
 803:                  }
 804:                  else
 805:                  {
 806:                      ontDataSql = ontDetailsSql = string.Empty;
 807:   
 808:                      throw new Exception("list.Count == 0");
 809:                  }
 810:              }
 811:              else
 812:              {
 813:                  throw new Exception("list.Count <= 40 || list.Count >= 100");
 814:              }
 815:          }
 816:   
 817:          ////////////////////////////////////////////////////////////////////////////
 818:   
 819:          /// <summary>
 820:          /// Compare OntList with ServiceRequestOntList to extract extra or wrong ONTs from the later
 821:          /// </summary>
 822:          private static List<string> ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList()
 823:          {
 824:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont ont;
 825:              List<string> list;
 826:              List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
 827:              Dictionary<string, Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont> ontAccessIdToOntDictionary;
 828:   
 829:              list = new List<string>();
 830:   
 831:              ontAccessIdToOntDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntDictionary;
 832:   
 833:              serviceRequestOntList = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.List;
 834:   
 835:              foreach (Ia.Ngn.Cl.Model.ServiceRequestOnt sro in serviceRequestOntList)
 836:              {
 837:                  if (ontAccessIdToOntDictionary.ContainsKey(sro.Id))
 838:                  {
 839:                      ont = ontAccessIdToOntDictionary[sro.Id];
 840:   
 841:                      if (ont.Pon.PonGroup.Symbol == sro.AreaSymbol && ont.Pon.Number == sro.Pon && ont.Number == sro.Ont)
 842:                      {
 843:                      }
 844:                      else
 845:                      {
 846:                          //sb.AppendLine(" wrong:" + w.Id + "'");
 847:                          list.Add(sro.Id);
 848:                      }
 849:                  }
 850:                  else
 851:                  {
 852:                      //sb.AppendLine(" missing: " + w.Id + "'");
 853:                      list.Add(sro.Id);
 854:                  }
 855:              }
 856:   
 857:              return list;
 858:          }
 859:   
 860:          ////////////////////////////////////////////////////////////////////////////
 861:   
 862:          /// <summary>
 863:          ///
 864:          /// </summary>
 865:          public static void DeleteOfnDatabaseRecordsAndConstructDeleteSqlForOracleDatabaseRecordsThatReferenceNonExistantOntsInNdd(out string ontDataSql, out string ontDetailsSql)
 866:          {
 867:              string sqlWhereString;
 868:              StringBuilder sb;
 869:              List<string> list;
 870:   
 871:              sb = new StringBuilder();
 872:   
 873:              ontDataSql = ontDetailsSql = string.Empty;
 874:   
 875:              list = ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList();
 876:   
 877:              if (list.Count < 2000)
 878:              {
 879:                  if (list.Count > 0)
 880:                  {
 881:                      DeleteOfnDatabaseRecordsThatReferenceNonExistantOntsInNdd(list);
 882:   
 883:                      sqlWhereString = string.Empty;
 884:   
 885:                      foreach (string s in list) sqlWhereString += "{variable} = '" + s + "' or ";
 886:                      sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 4, 4);
 887:   
 888:                      ontDetailsSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));
 889:   
 890:                      ontDataSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));
 891:                  }
 892:                  else
 893:                  {
 894:   
 895:                  }
 896:              }
 897:              else
 898:              {
 899:                  throw new Exception("list.Count >=2000");
 900:              }
 901:          }
 902:   
 903:          ////////////////////////////////////////////////////////////////////////////
 904:   
 905:          /// <summary>
 906:          ///
 907:          /// </summary>
 908:          private static void DeleteOfnDatabaseRecordsThatReferenceNonExistantOntsInNdd(List<string> list)
 909:          {
 910:              string sqlWhereString;
 911:              StringBuilder sb;
 912:   
 913:              sb = new StringBuilder();
 914:   
 915:              if (list.Count > 0)
 916:              {
 917:                  sqlWhereString = string.Empty;
 918:   
 919:                  foreach (string s in list) sqlWhereString += "{variable} = '" + s + "' or ";
 920:                  sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 4, 4);
 921:   
 922:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));
 923:   
 924:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 925:   
 926:                  // for testing
 927:                  //sb = sb.Replace("delete from","select * from");
 928:   
 929:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 930:                  {
 931:                      db.Database.ExecuteSqlRaw(sb.ToString());
 932:                  }
 933:              }
 934:              else
 935:              {
 936:   
 937:              }
 938:          }
 939:   
 940:          ////////////////////////////////////////////////////////////////////////////
 941:   
 942:          /// <summary>
 943:          ///
 944:          /// </summary>
 945:          public static int NextVacantFlatTermIdForHuaweiEmsOntEquipmentTypeIdAndNokiaGatewayId(Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentType equipmentType, int gatewayId)
 946:          {
 947:              int ft;
 948:              List<int> list;
 949:   
 950:              list = Ia.Ngn.Cl.Model.Data.Nokia.AgcfEndpoint.UsedFlatTermIdListForGatewayId(gatewayId);
 951:   
 952:              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
 953:              else
 954:              {
 955:                  list = Ia.Cl.Model.Default.ExcludedNumberListFromNumberListWithinRange(list, equipmentType.TelPorts);
 956:   
 957:                  if (list.Count > 0) ft = list[0];
 958:                  else ft = Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown; // something went wrong
 959:              }
 960:   
 961:              return ft;
 962:          }
 963:   
 964:          ////////////////////////////////////////////////////////////////////////////
 965:          ////////////////////////////////////////////////////////////////////////////
 966:   
 967:          /// <summary>
 968:          ///
 969:          /// </summary>
 970:          public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(List<string> oltSymbolList)
 971:          {
 972:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 973:              {
 974:                  var oltIdList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
 975:                                   where oltSymbolList.Contains(o.Symbol)
 976:                                   select o.Id).ToList();
 977:   
 978:                  var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntForOltSymbolListDictionary(oltSymbolList);
 979:   
 980:                  var list = (from a in db.Accesses
 981:                              join eo in db.EmsOnts on a.Id equals eo.Access.Id into eos
 982:                              from eo in eos.DefaultIfEmpty()
 983:                              join ed in db.EmsDevs on eo.DID equals ed.DID into eds
 984:                              from ed in eds.DefaultIfEmpty()
 985:                              where oltIdList.Contains(a.Olt)
 986:                              select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber
 987:                              {
 988:                                  AccessId = a.Id,
 989:                                  DevFnSnPnOntId = ed.DEV + "-" + eo.FN + "-" + eo.SN + "-" + eo.PN + "-" + eo.ONTID,
 990:                                  Vaprof = eo.VAPROF
 991:                              }).Distinct().ToList();
 992:   
 993:                  foreach (var u in list)
 994:                  {
 995:                      u.Ont = ontAccessIdToOntForOltSymbolListDictionary.ContainsKey(u.AccessId) ? ontAccessIdToOntForOltSymbolListDictionary[u.AccessId] : null;
 996:                  }
 997:   
 998:                  return list;
 999:              }
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
        {
            List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
 
            var _list = AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(oltSymbolList);
 
            list = (from l in _list where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" select l).ToList();
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> DistinctNddOntWhereVaprofIsNotAluSipBAndIgnoreMduForOltSymbolListList(List<string> oltSymbolList)
        {
            // I will ignore MDU because they have to be converted to SIP manually by Huawei
 
            List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list, list2;
 
            var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
 
            list = (from l in _list
                    where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" && l.EmsOntFamilyType != Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu
                    select l).ToList();
 
            list2 = new List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber>(list.Count);
 
            list2 = (from l in list select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber { Ont = l.Ont }).Distinct().ToList();
 
            return list2;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> ServiceAndNddOntAndEmsOntFamilyTypeAndPortWhereAgcfEndpointPortIsNot0AndVaprofIsAluSipBAndSfuEmsOntSipInfoTelDoesNotExistOrMduEmsVagIsSipAndMduEmsVoipPstnUserPnDoesNotExistForOltSymbolListList(List<string> oltSymbolList)
        {
            List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
 
            var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
 
            list = (from l in _list
                    where l.Ont != null && l.Port != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown // meaning AgcfEndpoints is not null and Port = ep.FlatTermID
                    &&
                    (
                    l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Sfu && l.Vaprof == "ALU-SIP-B-MS" && l.EmsOntSipInfoTel == Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
                    ||
                    l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn == Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
                    )
                    select l).ToList();
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<string> AccessNameWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
        {
            List<string> list;
 
            var list0 = AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(oltSymbolList);
 
            list = (from l in list0 select l.Ont.Access.Name).ToList();
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(List<string> oltSymbolList)
        {
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                var oltIdList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
                                 where oltSymbolList.Contains(o.Symbol)
                                 select o.Id).ToList();
 
                var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntForOltSymbolListDictionary(oltSymbolList);
 
                var list = (from a in db.Accesses
                            join se in db.Service2 on a.Id equals se.Access.Id
                            join sp in db.SubParties on se.Service equals sp.DisplayName
 
                            join ep in db.AgcfEndpoints on sp.PartyId equals ep.Dn into eps
                            from ep in eps.DefaultIfEmpty()
 
                            join gr in db.AgcfGatewayRecords on ep.AgcfGatewayRecord.Id equals gr.Id into grs
                            from gr in grs.DefaultIfEmpty()
 
                            join eo in db.EmsOnts on a.Id equals eo.Access.Id into eos
                            from eo in eos.DefaultIfEmpty()
 
                            join ed in db.EmsDevs on eo.DID equals ed.DID into eds
                            from ed in eds.DefaultIfEmpty()
 
                            join eosi in db.EmsOntSipInfoes on eo.Id equals eosi.EmsOnt.Id into eosis
                            from eosi in eosis.DefaultIfEmpty()
 
                            join evpu in db.EmsVoipPstnUsers on eo.Id equals evpu.EmsOnt.Id into evpus
                            from evpu in evpus.DefaultIfEmpty()
 
                            join ev in db.EmsVags on eo.Id equals ev.EmsOnt.Id into evs
                            from ev in evs.DefaultIfEmpty()
 
                            where oltIdList.Contains(a.Olt) && ((eosi == null && evpu == null) || (eosi != null && eosi.SIPNAME.Contains(se.Service)) || (evpu != null && evpu.DN.Contains(se.Service) && ev != null))
 
                            select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber
                            {
                                AccessId = a.Id,
                                DevFnSnPnOntId = ed.DEV + "-" + eo.FN + "-" + eo.SN + "-" + eo.PN + "-" + eo.ONTID,
                                Vaprof = eo.VAPROF,
                                EquipmentId = eo.EQUIPMENTID,
                                EmsOntSipInfoTel = eosi == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : eosi.TEL,
                                EmsVoipPstnUserPn = evpu == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : evpu.PN,
                                EmsVagProtocolType = ev == null ? string.Empty : ev.PROTOCOLTYPE,
                                AgcfGatewayRecordIp = gr == null ? string.Empty : gr.IP1,
                                Service = se.Service,
                                Port = ep == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : ep.FlatTermID,
                                PrimaryPuidcpeProfileNumber = sp.PrimaryPUIDCPEProfileNumber
                            }).AsNoTracking().ToList();
 
                foreach (var u in list)
                {
                    u.Ont = ontAccessIdToOntForOltSymbolListDictionary.ContainsKey(u.AccessId) ? ontAccessIdToOntForOltSymbolListDictionary[u.AccessId] : null;
 
                    u.EmsOntFamilyType = (from e in Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentTypeList where e.SystemNameList.Contains(u.EquipmentId) select e.FamilyType).Single();
                }
 
                return list;
            }
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofOntIpServicePortPrimaryPuidcpeProfileNumberWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
        {
            List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
 
            var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
 
            list = (from l in list0 where l.Vaprof != "ALU-SIP-B-MS" select l).ToList();
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10ForOltSymbolListList(List<string> oltSymbolList)
        {
            List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
 
            var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
 
            list = (from l in list0
                    where l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
                    select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, AccessName = l.Ont.Access.Name }).ToList();
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)
        {
            List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
 
            var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
 
            list = (from l in list0
                    where l.Vaprof == "ALU-SIP-B-MS" && l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
                    select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, AccessName = l.Ont.Access.Name }).ToList();
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)
        {
            List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
 
            var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
 
            list = (from l in list0
                    where l.Vaprof == "ALU-SIP-B-MS" && l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
                    select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, Ont = l.Ont, AccessName = l.Ont.Access.Name }).ToList();
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBAndSfuEmsOntSipInfoTelExistsOrAndMduEmsOntVagIsSipAndMduEmsVoipPstnUserPnExistsForOltSymbolListList(List<string> oltSymbolList)
        {
            List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
 
            var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
 
            list = (from l in list0
                    where
                    l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip &&
                    (
                    l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Sfu && l.Vaprof == "ALU-SIP-B-MS" && l.EmsOntSipInfoTel != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
                    ||
                    l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
                    )
                    select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, Ont = l.Ont, AccessName = l.Ont.Access.Name }).ToList();
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<string, int> RouterNameToServiceCountInNokiaRouterHuaweiAccessOltDictionary()
        {
            var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
 
            var nokiaRouterHuaweiAccessOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaRouterHuaweiAccessOltIdList;
 
            var nokiaRouterHuaweiAccessRouterList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
                                                     where nokiaRouterHuaweiAccessOltIdList.Contains(o.Id)
                                                     select o.Odf.Router).Distinct().ToList();
 
 
            var dictionary = new Dictionary<string, int>();
 
            foreach (var r in nokiaRouterHuaweiAccessRouterList)
            {
                var nameType = r.Name + " (" + r.Type + ")";
 
                if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
 
                foreach (var o in r.Odfs.SelectMany(u => u.Olts))
                {
                    if (o.Odf.Router.Id == r.Id)
                    {
                        if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
                        {
                            dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
                        }
                    }
                }
            }
 
            return dictionary;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<string, int> NokiaOltToServiceCountDictionary()
        {
            var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
 
            var oltList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaOltList;
 
            var dictionary = new Dictionary<string, int>();
 
            foreach (var o in oltList)
            {
                var nameType = o.Name + " (" + o.EmsName + ")";
 
                if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
 
                if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
                {
                    dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
                }
            }
 
            return dictionary;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<string, int> HuaweiOltToServiceCountDictionary()
        {
            var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
 
            var oltList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltList;
 
            var dictionary = new Dictionary<string, int>();
 
            foreach (var o in oltList)
            {
                var nameType = o.Name + " (" + o.EmsName + ")";
 
                if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
 
                if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
                {
                    dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
                }
            }
 
            return dictionary;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<int, int> OltIdToCountOfServiceDictionary()
        {
            var dictionary = new Dictionary<int, int>();
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                /*
    select a.Olt, count(s.Service)
    from Service2 s
    left outer join Accesses a on a.Id = s.Access_Id
    where a.Olt is not null
    group by a.Olt
                 */
 
                dictionary = (from s in db.Service2
                              join a in db.Accesses on s.Access.Id equals a.Id
                              where a != null
                              group a.Olt by a.Olt into g
                              select new { OltId = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.OltId, t => t.ServiceCount);
            }
 
            return dictionary;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<int, int> MsanDidToMsanServiceCountDictionary()
        {
            var dictionary = new Dictionary<int, int>();
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
#if DEBUG
                var dummyVarToDrawRefractorToBelow = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn("0000000");
#endif
 
                dictionary = (from s in db.Service2
                              join evpu in db.EmsVoipPstnUsers on "+965" + s.Service equals evpu.DN
                              where evpu.DN.StartsWith("+965") // see: Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn()
                              group evpu.DID by evpu.DID into g
                              select new { Did = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.Did, t => t.ServiceCount);
            }
 
            return dictionary;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<int, int> MsanDidToEmsVoipPstnUsersMsanServiceCountDictionary()
        {
            var dictionary = new Dictionary<int, int>();
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
#if DEBUG
                var dummyVarToDrawRefractorToBelow = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn("0000000");
#endif
 
                dictionary = (from evpu in db.EmsVoipPstnUsers
                              where evpu.DN.StartsWith("+965") // see: Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn()
                              group evpu.DID by evpu.DID into g
                              select new { Did = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.Did, t => t.ServiceCount);
            }
 
            return dictionary;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static int MaximumOntTelPorts
        {
            get
            {
                var maxNokiaTelPort = Ia.Ngn.Cl.Model.Business.Nokia.Ont.EquipmentTypeList.Max(u => u.TelPorts);
                var maxHuaweiTelPort = Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentTypeList.Max(u => u.TelPorts);
 
                var maxTelPort = Math.Max(maxNokiaTelPort, maxHuaweiTelPort);
 
                return maxTelPort;
            }
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static StringBuilder Log()
        {
            return logStringBuilder;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static void Log(string line)
        {
            var s = DateTime.UtcNow.AddHours(3).ToString("yyyy-MM-dd HH:mm") + ": " + line;
 
            logStringBuilder.AppendLine(s);
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static void InitializeApplicationLists()
        {
            //_ = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntList; useless because this is not saved in SQL database where its accessable in other appliciations
            //_ = Ia.Ngn.Cl.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration.AccessToPstnServiceListDictionary;
            _ = Ia.Ngn.Cl.Model.Data.ServiceRequest.AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
            _ = Ia.Ngn.Cl.Model.Data.ServiceRequest.DbNameToServiceListDictionary;
            _ = Ia.Ngn.Cl.Model.Data.Ims.NokiaAccessNameWithPbxList;
        }
 
        ////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////
    }
}