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

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 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 Next Generation Network (NGN) 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 partial class Default
  31:      {
  32:          ////////////////////////////////////////////////////////////////////////////
  33:   
  34:          /// <summary>
  35:          ///
  36:          /// </summary>
  37:          public Default() { }
  38:   
  39:          ////////////////////////////////////////////////////////////////////////////
  40:   
  41:          /// <summary>
  42:          ///
  43:          /// </summary>
  44:          public static List<string> NewOntEventAndAccessBySerialInNoteList()
  45:          {
  46:              List<string> list;
  47:   
  48:              list = new List<string>();
  49:   
  50:              var bList = Ia.Ngn.Cl.Model.Data.Event.ReadNewOntList;
  51:   
  52:              //var aList = Ia.Ngn.Cl.Model.Data.Access.BySerialInNote();
  53:   
  54:   
  55:              return list;
  56:          }
  57:   
  58:          ////////////////////////////////////////////////////////////////////////////
  59:   
  60:          /// <summary>
  61:          ///
  62:          /// </summary>
  63:          public static List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> DifferentOntNameAndStatisticalOntNameList(int siteId)
  64:          {
  65:              string address, level;
  66:              Hashtable hashTable; //, nokiaInitialInstallationListHashTable;
  67:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
  68:              Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
  69:              Ia.Ngn.Cl.Model.Access statisticalAccess;
  70:              Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName l;
  71:   
  72:              List<int> siteRouterDomainList, areaIdList;
  73:              List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> list;
  74:              List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
  75:              List<Ia.Ngn.Cl.Model.ServiceRequestService> serviceRequestServiceList;
  76:              List<Ia.Ngn.Cl.Model.Access> accessList;
  77:              Dictionary<int, string> areaIdToNameArabicNameDictionary;
  78:   
  79:              level = string.Empty;
  80:              areaIdList = new List<int>();
  81:   
  82:              site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();
  83:   
  84:   
  85:              areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
  86:   
  87:              //nokiaInitialInstallationListHashTable = new Hashtable(100000);
  88:   
  89:              /*
  90:              foreach (XElement xe in NokiaInitialInstallationListXDocument.Element("alInitialInstallationList").Elements("alInitialInstallation"))
  91:              {
  92:                  nokiaInitialInstallationListHashTable[xe.Attribute("accessId").Value] = xe.Value.Replace("|","\t");
  93:              }
  94:              */
  95:   
  96:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
  97:              {
  98:                  siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList 
  99:                                          where r.Site.Id == siteId 
 100:                                          select r).SelectMany(z => z.DomainList).ToList();
 101:   
 102:                  serviceRequestList = (from sr in db.ServiceRequests 
 103:                                        where siteRouterDomainList.Contains(sr.Number / 10000) || siteRouterDomainList.Contains(sr.Number / 1000) 
 104:                                        select sr).ToList();
 105:   
 106:                  hashTable = Ia.Ngn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);
 107:   
 108:                  serviceRequestServiceList = (from srs in db.ServiceRequestServices
 109:                                               where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString()))
 110:                                               select srs).Include(u => u.Access).ToList();
 111:   
 112:                  areaIdList = site.KuwaitNgnAreas.Select(i => i.Id).ToList();
 113:   
 114:                  accessList = (from a in db.Accesses 
 115:                                where areaIdList.Contains(a.AreaId) 
 116:                                select a).ToList();
 117:   
 118:                  list = new List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName>();
 119:   
 120:                  foreach (Ia.Ngn.Cl.Model.ServiceRequestService srs in serviceRequestServiceList)
 121:                  {
 122:                      l = new Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName();
 123:   
 124:                      if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();
 125:                      else address = null;
 126:   
 127:                      l.Service = srs.Service;
 128:                      l.CustomerAddress = address;
 129:                      l.Access = srs.Access;
 130:   
 131:                      if (srs.Access != null) l.OntAddress = srs.Access.Address;
 132:   
 133:                      serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(srs.Service, address, out level);
 134:   
 135:                      // below: this will skip Sabah Al-Salem area
 136:                      //if (l.CustomerAddress != null && !l.CustomerAddress.Contains("صباح"))
 137:                      //{
 138:                      statisticalAccess = null; // Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList);
 139:   
 140:                      if (statisticalAccess != null)
 141:                      {
 142:                          l.Block = statisticalAccess.Block;
 143:                          l.Street = statisticalAccess.Street;
 144:                          l.PremisesOld = statisticalAccess.PremisesOld;
 145:                          l.PremisesNew = statisticalAccess.PremisesNew;
 146:                          l.KuwaitNgnAreaNameArabicName = areaIdToNameArabicNameDictionary[statisticalAccess.AreaId];
 147:   
 148:                          l.StatisticalAccessName = statisticalAccess.Name;
 149:   
 150:                          /*
 151:                          if (nokiaInitialInstallationListHashTable.ContainsKey(statisticalAccess.Id))
 152:                          {
 153:                              l.Note = nokiaInitialInstallationListHashTable[statisticalAccess.Id].ToString();
 154:                          }
 155:                          */
 156:                          //else l.Note = "No: " + srs.Access.Id;
 157:                      }
 158:   
 159:                      if (serviceAddress.AreaId != 0)
 160:                      {
 161:                          l.StatisticalAddress = serviceAddress.Address;
 162:   
 163:                          if (srs.Access != null) l.AccessName = l.Access.Name;
 164:   
 165:                          //if (l.OntName != null && l.OntName == l.StatisticalOntName) l.Note = Ia.Cl.Model.Default.YesNo(true);
 166:                          //else l.Note = Ia.Cl.Model.Default.YesNo(false);
 167:   
 168:                          //l.Note += " (" + level + ")";
 169:                      }
 170:                      else
 171:                      {
 172:                          //l.Note = Ia.Cl.Model.Default.YesNo(false);
 173:   
 174:                          //l.Note += " (AreaId zero)";
 175:                      }
 176:   
 177:                      list.Add(l);
 178:                      //}
 179:                      //else
 180:                      //{
 181:   
 182:                      //}
 183:                  }
 184:              }
 185:   
 186:              return list;
 187:          }
 188:   
 189:          ////////////////////////////////////////////////////////////////////////////
 190:   
 191:          /// <summary>
 192:          ///
 193:          /// </summary>
 194:          public static List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> DifferentOntNameAndStatisticalOntNameList2(int siteId)
 195:          {
 196:              string address, level;
 197:              Hashtable hashTable; //, nokiaInitialInstallationListHashTable;
 198:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
 199:              Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
 200:              Ia.Ngn.Cl.Model.Access statisticalAccess;
 201:              Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName l;
 202:   
 203:              List<int> siteRouterDomainList, areaIdList;
 204:              List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> list;
 205:              //List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
 206:              //List<Ia.Ngn.Cl.Model.Ui.Access> accessList;
 207:              Dictionary<int, string> areaIdToNameArabicNameDictionary;
 208:   
 209:              level = string.Empty;
 210:              areaIdList = new List<int>();
 211:   
 212:              site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList 
 213:                      where a.Id == siteId 
 214:                      select a).SingleOrDefault();
 215:   
 216:              var ontAccessIdToOntAccessNameDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
 217:   
 218:              areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
 219:   
 220:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 221:              {
 222:                  siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList 
 223:                                          where r.Site.Id == siteId 
 224:                                          select r).SelectMany(z => z.DomainList).ToList();
 225:                  //siteRouterDomainList = new List<int> { 2538 };
 226:   
 227:                  //serviceRequestList = (from sr in db.ServiceRequests where siteRouterDomainList.Contains(sr.Number / 10000) || siteRouterDomainList.Contains(sr.Number / 1000) select sr).ToList();
 228:   
 229:                  hashTable = Ia.Ngn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);
 230:   
 231:                  var serviceRequestServiceList = (from srs in db.ServiceRequestServices
 232:                                                   where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString()))
 233:                                                   select new { srs.Service, AccessId = (srs.Access != null) ? srs.Access.Id : string.Empty }).ToList();
 234:   
 235:                  areaIdList = site.KuwaitNgnAreas.Select(i => i.Id).ToList();
 236:                  //areaIdList = new List<int> { 60618 };
 237:   
 238:                  //accessList = null;// (from a in db.Accesses where areaIdList.Contains(a.AreaId) select a).ToList();
 239:   
 240:                  list = new List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName>();
 241:   
 242:                  foreach (var srs in serviceRequestServiceList)
 243:                  {
 244:                      l = new Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName();
 245:   
 246:                      if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();
 247:                      else address = null;
 248:   
 249:                      l.Service = srs.Service;
 250:                      l.CustomerAddress = address;
 251:   
 252:                      if (ontAccessIdToOntAccessNameDictionary.ContainsKey(srs.AccessId)) l.AccessId = srs.AccessId;
 253:                      else l.Access = null;
 254:   
 255:                      if (!string.IsNullOrEmpty(srs.AccessId)) l.OntAddress = string.Empty;// srs.Access.Address;
 256:   
 257:                      //------------------
 258:                      serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(srs.Service, address, out level);
 259:   
 260:                      statisticalAccess = null;// Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList);
 261:   
 262:                      if (statisticalAccess != null)
 263:                      {
 264:                          l.Block = statisticalAccess.Block;
 265:                          l.Street = statisticalAccess.Street;
 266:                          l.PremisesOld = statisticalAccess.PremisesOld;
 267:                          l.PremisesNew = statisticalAccess.PremisesNew;
 268:                          l.KuwaitNgnAreaNameArabicName = areaIdToNameArabicNameDictionary[statisticalAccess.AreaId];
 269:   
 270:                          l.StatisticalAccessName = string.Empty;// statisticalAccess.Name;
 271:   
 272:                          /*
 273:                          if (nokiaInitialInstallationListHashTable.ContainsKey(statisticalAccess.Id))
 274:                          {
 275:                              l.Note = nokiaInitialInstallationListHashTable[statisticalAccess.Id].ToString();
 276:                          }
 277:                          */
 278:                          //else l.Note = "No: " + srs.Access.Id;
 279:                      }
 280:   
 281:                      if (serviceAddress.AreaId != 0)
 282:                      {
 283:                          l.StatisticalAddress = string.Empty;// serviceAddress.Address;
 284:   
 285:                          if (!string.IsNullOrEmpty(srs.AccessId))
 286:                          {
 287:                              l.AccessName = string.Empty;// l.Access.Name;
 288:                          }
 289:   
 290:                          //if (l.OntName != null && l.OntName == l.StatisticalOntName) l.Note = Ia.Cl.Model.Default.YesNo(true);
 291:                          //else l.Note = Ia.Cl.Model.Default.YesNo(false);
 292:   
 293:                          //l.Note += " (" + level + ")";
 294:                      }
 295:                      else
 296:                      {
 297:                          //l.Note = Ia.Cl.Model.Default.YesNo(false);
 298:   
 299:                          //l.Note += " (AreaId zero)";
 300:                      }
 301:   
 302:                      list.Add(l);
 303:                  }
 304:              }
 305:   
 306:              return list;
 307:          }
 308:   
 309:          ////////////////////////////////////////////////////////////////////////////
 310:   
 311:          /// <summary>
 312:          ///
 313:          /// </summary>
 314:          public static void SiteSerivceAndEmsOntAndAccessAndServiceRequestAndServiceRequestTypeInformation(int siteId, out StringBuilder sb)
 315:          {
 316:              int areaId, customerId;
 317:              string s, accessId;
 318:              string service, alias, area, block, street, premisesOld, paci, customerName, customerAddress, typeId1, typeId2, typeId4, typeId5;
 319:   
 320:              List<int> siteRouterDomainList, areaIdList;
 321:              List<string> serviceList;
 322:              Dictionary<int, string> areaIdToNameArabicNameDictionary;
 323:   
 324:              sb = new StringBuilder();
 325:   
 326:              areaIdList = new List<int>();
 327:   
 328:              /*
 329:  Service2S    ONTs(EMS)    Accesses    Accesses    Accesses    Accesses    Accesses    Service Requests    Service Requests    Service Requests    Service Requests Types    Service Requests    Service Requests    Service Requests
 330:  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
 331:               */
 332:   
 333:              var site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();
 334:   
 335:              siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList where r.Site.Id == siteId select r).SelectMany(z => z.DomainList).ToList();
 336:   
 337:              areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
 338:   
 339:              serviceList = Ia.Ngn.Cl.Model.Data.Service2.List(siteRouterDomainList);
 340:   
 341:              var serviceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceToAccessIdDictionary;
 342:   
 343:              var idToAccessDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToAccessDictionary;
 344:   
 345:              var accessIdToOntDictionary = Ia.Ngn.Cl.Model.Data.Huawei.Ont.AccessIdToOntDictionary;
 346:   
 347:              var serviceToServiceRequestServiceDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ServiceToServiceRequestServiceDictionary(siteRouterDomainList);
 348:   
 349:              var serviceToServiceRequestDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToServiceRequestDictionary(siteRouterDomainList);
 350:   
 351:   
 352:              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";
 353:              sb.AppendLine(s);
 354:   
 355:              foreach (string u in serviceList)
 356:              {
 357:                  service = u;
 358:   
 359:                  if (serviceToAccessIdDictionary.ContainsKey(u))
 360:                  {
 361:                      accessId = serviceToAccessIdDictionary[u];
 362:   
 363:                      if (idToAccessDictionary.ContainsKey(accessId))
 364:                      {
 365:                          areaId = idToAccessDictionary[accessId].AreaId;
 366:                          block = idToAccessDictionary[accessId].Block;
 367:                          street = idToAccessDictionary[accessId].Street;
 368:                          premisesOld = idToAccessDictionary[accessId].PremisesOld;
 369:                          paci = idToAccessDictionary[accessId].Paci;
 370:   
 371:                          if (areaIdToNameArabicNameDictionary.ContainsKey(areaId)) area = areaIdToNameArabicNameDictionary[areaId];
 372:                          else area = string.Empty;
 373:   
 374:                          if (accessIdToOntDictionary.ContainsKey(accessId))
 375:                          {
 376:                              alias = accessIdToOntDictionary[accessId].ALIAS;
 377:                          }
 378:                          else
 379:                          {
 380:                              alias = string.Empty;
 381:                          }
 382:                      }
 383:                      else
 384:                      {
 385:                          block = string.Empty;
 386:                          street = string.Empty;
 387:                          premisesOld = string.Empty;
 388:                          paci = string.Empty;
 389:                          area = string.Empty;
 390:   
 391:                          alias = string.Empty;
 392:                      }
 393:                  }
 394:                  else
 395:                  {
 396:                      block = string.Empty;
 397:                      street = string.Empty;
 398:                      premisesOld = string.Empty;
 399:                      paci = string.Empty;
 400:                      area = string.Empty;
 401:   
 402:                      alias = string.Empty;
 403:                  }
 404:   
 405:   
 406:                  if (serviceToServiceRequestServiceDictionary.ContainsKey(u))
 407:                  {
 408:                      if (serviceToServiceRequestServiceDictionary[u].Provisioned)
 409:                      {
 410:                          if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.Count > 0)
 411:                          {
 412:                              //var bb = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault();
 413:   
 414:                              if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault() != null)
 415:                              {
 416:                                  customerId = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerId;
 417:                                  customerName = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerName;
 418:                                  customerAddress = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerAddress;
 419:   
 420:                                  if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes.Count > 0)
 421:                                  {
 422:                                      var vv = (from srt in serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes
 423:                                                group srt by new { srt.TypeId, srt.Value } into g
 424:                                                select new //Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 425:                                                {
 426:                                                    TypeId1Value = g.Max(v => v.TypeId == 1 ? v.Value : ""),
 427:                                                    TypeId2Value = g.Max(v => v.TypeId == 2 ? v.Value : ""),
 428:                                                    TypeId4Value = g.Max(v => v.TypeId == 4 ? v.Value : ""),
 429:                                                    TypeId5Value = g.Max(v => v.TypeId == 5 ? v.Value : ""),
 430:                                                }).ToList();
 431:   
 432:                                      typeId1 = string.Empty;
 433:                                      typeId2 = string.Empty;
 434:                                      typeId4 = string.Empty;
 435:                                      typeId5 = string.Empty;
 436:   
 437:                                      foreach (var w in vv)
 438:                                      {
 439:                                          if (!string.IsNullOrEmpty(w.TypeId1Value)) typeId1 = w.TypeId1Value;
 440:                                          if (!string.IsNullOrEmpty(w.TypeId2Value)) typeId2 = w.TypeId2Value;
 441:                                          if (!string.IsNullOrEmpty(w.TypeId4Value)) typeId4 = w.TypeId4Value;
 442:                                          if (!string.IsNullOrEmpty(w.TypeId5Value)) typeId5 = w.TypeId5Value;
 443:                                      }
 444:                                  }
 445:                                  else
 446:                                  {
 447:                                      customerId = 0;
 448:                                      customerName = string.Empty;
 449:                                      customerAddress = string.Empty;
 450:   
 451:                                      typeId1 = string.Empty;
 452:                                      typeId2 = string.Empty;
 453:                                      typeId4 = string.Empty;
 454:                                      typeId5 = string.Empty;
 455:                                  }
 456:                              }
 457:                              else
 458:                              {
 459:                                  customerId = 0;
 460:                                  customerName = string.Empty;
 461:                                  customerAddress = string.Empty;
 462:   
 463:                                  typeId1 = string.Empty;
 464:                                  typeId2 = string.Empty;
 465:                                  typeId4 = string.Empty;
 466:                                  typeId5 = string.Empty;
 467:                              }
 468:                          }
 469:                          else
 470:                          {
 471:                              customerId = 0;
 472:                              customerName = string.Empty;
 473:                              customerAddress = string.Empty;
 474:   
 475:                              typeId1 = string.Empty;
 476:                              typeId2 = string.Empty;
 477:                              typeId4 = string.Empty;
 478:                              typeId5 = string.Empty;
 479:                          }
 480:                      }
 481:                      else
 482:                      {
 483:                          customerId = 0;
 484:                          customerName = string.Empty;
 485:                          customerAddress = string.Empty;
 486:   
 487:                          typeId1 = string.Empty;
 488:                          typeId2 = string.Empty;
 489:                          typeId4 = string.Empty;
 490:                          typeId5 = string.Empty;
 491:                      }
 492:                  }
 493:                  else
 494:                  {
 495:                      if (serviceToServiceRequestDictionary.ContainsKey(u))
 496:                      {
 497:                          customerId = serviceToServiceRequestDictionary[u].CustomerId;
 498:                          customerName = serviceToServiceRequestDictionary[u].CustomerName;
 499:                          customerAddress = serviceToServiceRequestDictionary[u].CustomerAddress;
 500:   
 501:                          if (serviceToServiceRequestDictionary[u].ServiceRequestTypes.Count > 0)
 502:                          {
 503:                              var vv = (from srt in serviceToServiceRequestDictionary[u].ServiceRequestTypes
 504:                                        group srt by new { srt.TypeId, srt.Value } into g
 505:                                        select new //Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 506:                                        {
 507:                                            TypeId1Value = g.Max(v => v.TypeId == 1 ? v.Value : ""),
 508:                                            TypeId2Value = g.Max(v => v.TypeId == 2 ? v.Value : ""),
 509:                                            TypeId4Value = g.Max(v => v.TypeId == 4 ? v.Value : ""),
 510:                                            TypeId5Value = g.Max(v => v.TypeId == 5 ? v.Value : ""),
 511:                                        }).FirstOrDefault(); //.SingleOrDefault();
 512:   
 513:                              typeId1 = vv.TypeId1Value;
 514:                              typeId2 = vv.TypeId2Value;
 515:                              typeId4 = vv.TypeId4Value;
 516:                              typeId5 = vv.TypeId5Value;
 517:                          }
 518:                          else
 519:                          {
 520:                              typeId1 = string.Empty;
 521:                              typeId2 = string.Empty;
 522:                              typeId4 = string.Empty;
 523:                              typeId5 = string.Empty;
 524:                          }
 525:                      }
 526:                      else
 527:                      {
 528:                          customerId = 0;
 529:                          customerName = string.Empty;
 530:                          customerAddress = string.Empty;
 531:   
 532:                          typeId1 = string.Empty;
 533:                          typeId2 = string.Empty;
 534:                          typeId4 = string.Empty;
 535:                          typeId5 = string.Empty;
 536:                      }
 537:                  }
 538:   
 539:                  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 + "";
 540:                  sb.AppendLine(s);
 541:   
 542:              }
 543:          }
 544:   
 545:          ////////////////////////////////////////////////////////////////////////////
 546:   
 547:          /// <summary>
 548:          ///
 549:          /// </summary>
 550:          public static List<Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> ServicesWithinSiteWithRelevantServiceRequestTypeList(int siteId)
 551:          {
 552:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
 553:              List<int> siteKuwaitNgnAreaIdList;
 554:              List<Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> list;
 555:              Dictionary<int, string> areaIdToSymbolDictionary;
 556:   
 557:              areaIdToSymbolDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToSymbolDictionary;
 558:   
 559:              site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();
 560:              siteKuwaitNgnAreaIdList = (from kna in site.KuwaitNgnAreas select kna.Id).ToList();
 561:   
 562:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 563:              {
 564:                  var serviceAreaIdTypeIdValue = (from srs in db.ServiceRequestServices
 565:                                                  join sr in db.ServiceRequests on srs.Id equals sr.ServiceRequestService.Id
 566:                                                  join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id
 567:                                                  where srs.Provisioned == true && siteKuwaitNgnAreaIdList.Contains(sr.AreaId)
 568:                                                  select new
 569:                                                  {
 570:                                                      Service = srs.Service,
 571:                                                      AreaId = sr.AreaId,
 572:                                                      TypeId = srt.TypeId,
 573:                                                      Value = srt.Value
 574:                                                  }
 575:                                      ).ToList();
 576:   
 577:                  list = (from a in serviceAreaIdTypeIdValue
 578:                          group a by new { a.Service, a.AreaId } into g
 579:                          select new Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
 580:                          {
 581:                              Service = g.Key.Service,
 582:                              AreaSymbol = areaIdToSymbolDictionary[g.Key.AreaId],
 583:                              TypeId1Value = g.Max(u => u.TypeId == 1 ? u.Value : "?"),
 584:                              TypeId2Value = g.Max(u => u.TypeId == 2 ? u.Value : "?"),
 585:                              TypeId3Value = g.Max(u => u.TypeId == 3 ? u.Value : "?"),
 586:                              TypeId4Value = g.Max(u => u.TypeId == 4 ? u.Value : "?"),
 587:                              TypeId5Value = g.Max(u => u.TypeId == 5 ? u.Value : "?"),
 588:                          }).ToList();
 589:   
 590:              }
 591:   
 592:              return list;
 593:          }
 594:   
 595:          ////////////////////////////////////////////////////////////////////////////
 596:   
 597:          /// <summary>
 598:          ///
 599:          /// </summary>
 600:          public static Dictionary<string, int> ServiceRequestServiceServiceToKuwaitNgnAreaIdDictionary
 601:          {
 602:              get
 603:              {
 604:                  string accessId;
 605:                  Dictionary<string, string> serviceRequestServiceServiceToAccessIdDictionary;
 606:                  Dictionary<string, int> dictionary, accessIdToKuwaitNgnAreaIdDictionary;
 607:   
 608:                  serviceRequestServiceServiceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ProvisionedServiceIdToAccessIdDictionary;
 609:                  accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 610:   
 611:                  if (serviceRequestServiceServiceToAccessIdDictionary.Count > 0)
 612:                  {
 613:                      dictionary = new Dictionary<string, int>(serviceRequestServiceServiceToAccessIdDictionary.Count);
 614:   
 615:                      foreach (string s in serviceRequestServiceServiceToAccessIdDictionary.Keys)
 616:                      {
 617:                          accessId = serviceRequestServiceServiceToAccessIdDictionary[s].ToString();
 618:   
 619:                          if (accessId != null)
 620:                          {
 621:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
 622:                              {
 623:                                  dictionary[s] = accessIdToKuwaitNgnAreaIdDictionary[accessId];
 624:                              }
 625:                          }
 626:                          else dictionary[s] = 0;
 627:                      }
 628:                  }
 629:                  else
 630:                  {
 631:                      dictionary = new Dictionary<string, int>();
 632:                  }
 633:   
 634:                  return dictionary;
 635:              }
 636:          }
 637:   
 638:          ////////////////////////////////////////////////////////////////////////////
 639:   
 640:          /// <summary>
 641:          ///
 642:          /// </summary>
 643:          public static Dictionary<string, int> ServiceServiceToKuwaitNgnAreaIdDictionary
 644:          {
 645:              get
 646:              {
 647:                  string accessId;
 648:                  Dictionary<string, string> serviceServiceToAccessIdDictionary;
 649:                  Dictionary<string, int> dictionary, accessIdToKuwaitNgnAreaIdDictionary;
 650:   
 651:                  serviceServiceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceIdToAccessIdDictionary;
 652:                  accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 653:   
 654:                  if (serviceServiceToAccessIdDictionary.Count > 0)
 655:                  {
 656:                      dictionary = new Dictionary<string, int>(serviceServiceToAccessIdDictionary.Count);
 657:   
 658:                      foreach (string s in serviceServiceToAccessIdDictionary.Keys)
 659:                      {
 660:                          accessId = serviceServiceToAccessIdDictionary[s].ToString();
 661:   
 662:                          if (accessId != null)
 663:                          {
 664:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
 665:                              {
 666:                                  dictionary[s] = accessIdToKuwaitNgnAreaIdDictionary[accessId];
 667:                              }
 668:                          }
 669:                          else dictionary[s] = 0;
 670:                      }
 671:                  }
 672:                  else
 673:                  {
 674:                      dictionary = new Dictionary<string, int>();
 675:                  }
 676:   
 677:                  return dictionary;
 678:              }
 679:          }
 680:   
 681:          ////////////////////////////////////////////////////////////////////////////
 682:   
 683:          /// <summary>
 684:          ///
 685:          /// </summary>
 686:          public static void DeleteDatabaseRecordsThatReferenceNonExistantOltsInNdd()
 687:          {
 688:              string sqlWhereString;
 689:              StringBuilder sb;
 690:              List<int> list;
 691:   
 692:              sb = new StringBuilder();
 693:   
 694:              list = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();
 695:   
 696:              if (list.Count > 40 && list.Count < 100)
 697:              {
 698:                  sqlWhereString = string.Empty;
 699:   
 700:                  foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";
 701:                  sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);
 702:   
 703:                  sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 704:                  sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Id"));
 705:   
 706:                  sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 707:                  sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Id"));
 708:   
 709:                  sb.AppendLine("delete from Events where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
 710:   
 711:                  sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 712:                  sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Id"));
 713:   
 714:                  sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "EmsOnt_Id"));
 715:                  sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "Id"));
 716:   
 717:                  sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 718:                  sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 719:   
 720:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));
 721:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "Id"));
 722:   
 723:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
 724:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 725:   
 726:                  sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Id"));
 727:                  sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Olt"));
 728:   
 729:                  // for testing
 730:                  //sb = sb.Replace("delete from","select * from");
 731:   
 732:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 733:                  {
 734:                      db.Database.ExecuteSqlRaw(sb.ToString());
 735:                  }
 736:              }
 737:              else
 738:              {
 739:                  throw new Exception("list.Count <= 40 || list.Count >= 100");
 740:              }
 741:          }
 742:   
 743:          ////////////////////////////////////////////////////////////////////////////
 744:   
 745:          /// <summary>
 746:          ///
 747:          /// </summary>
 748:          public static void DeleteDatabaseRecordsThatReferenceAnUnrelatedForeignKeyBySimilarityOfId()
 749:          {
 750:              int fixedLengthOfOntId, fixedLengthOfAccessId;
 751:              StringBuilder sb;
 752:   
 753:              sb = new StringBuilder();
 754:   
 755:              /*
 756:      --delete from OntOntPots where substring(Id,1, 16) not like Ont_Id 
 757:      --delete from OntServiceVoips where substring(Id,1, 16) not like Ont_Id 
 758:      --delete from EmsOntSipInfoes where substring(Id,1, 16) not like EmsOnt_Id 
 759:      --delete from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id 
 760:  
 761:      --delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id
 762:      --delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id)
 763:      --delete from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id
 764:               */
 765:   
 766:              fixedLengthOfOntId = Ia.Ngn.Cl.Model.Business.Default.FixedLengthOfOntId;
 767:              fixedLengthOfAccessId = Ia.Ngn.Cl.Model.Business.Access.FixedLengthOfId;
 768:   
 769:              if (fixedLengthOfOntId > 10 && fixedLengthOfAccessId > 10)
 770:              {
 771:                  sb.AppendLine("delete from OntOntPots where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");
 772:                  sb.AppendLine("delete from OntServiceVoips where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");
 773:                  sb.AppendLine("delete from EmsOntSipInfoes where substring(Id,1, " + fixedLengthOfOntId + ") not like EmsOnt_Id");
 774:   
 775:                  sb.AppendLine("delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id");
 776:                  sb.AppendLine("delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id)");
 777:                  sb.AppendLine("delete from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id");
 778:   
 779:                  // for testing
 780:                  //sb = sb.Replace("delete from","select * from");
 781:   
 782:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 783:                  {
 784:                      db.Database.ExecuteSqlRaw(sb.ToString());
 785:                  }
 786:              }
 787:              else
 788:              {
 789:                  throw new Exception("Ia.Ngn.Cl.Model.Business.Default.FixedLengthOfOntId and/or Ia.Ngn.Cl.Model.Business.Access.FixedLengthOfId is not proper");
 790:              }
 791:          }
 792:   
 793:          ////////////////////////////////////////////////////////////////////////////
 794:   
 795:          /// <summary>
 796:          ///
 797:          /// </summary>
 798:          public static void ConstructDeleteSqlForOracleDatabaseRecordsThatReferenceNonExistantOltsInNdd(out string ontDataSql, out string ontDetailsSql)
 799:          {
 800:              string sqlWhereString;
 801:              List<int> list;
 802:   
 803:              list = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();
 804:   
 805:              if (list.Count > 40 && list.Count < 100)
 806:              {
 807:                  if (list.Count > 0)
 808:                  {
 809:                      sqlWhereString = string.Empty;
 810:   
 811:                      foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";
 812:                      sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);
 813:   
 814:                      ontDataSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));
 815:   
 816:                      ontDetailsSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));
 817:                  }
 818:                  else
 819:                  {
 820:                      ontDataSql = ontDetailsSql = string.Empty;
 821:   
 822:                      throw new Exception("list.Count == 0");
 823:                  }
 824:              }
 825:              else
 826:              {
 827:                  throw new Exception("list.Count <= 40 || list.Count >= 100");
 828:              }
 829:          }
 830:   
 831:          ////////////////////////////////////////////////////////////////////////////
 832:   
 833:          /// <summary>
 834:          /// Compare OntList with ServiceRequestOntList to extract extra or wrong ONTs from the later
 835:          /// </summary>
 836:          private static List<string> ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList()
 837:          {
 838:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont ont;
 839:              List<string> list;
 840:              List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
 841:              Dictionary<string, Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont> ontAccessIdToOntDictionary;
 842:   
 843:              list = new List<string>();
 844:   
 845:              ontAccessIdToOntDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntDictionary;
 846:   
 847:              serviceRequestOntList = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.List;
 848:   
 849:              foreach (Ia.Ngn.Cl.Model.ServiceRequestOnt sro in serviceRequestOntList)
 850:              {
 851:                  if (ontAccessIdToOntDictionary.ContainsKey(sro.Id))
 852:                  {
 853:                      ont = ontAccessIdToOntDictionary[sro.Id];
 854:   
 855:                      if (ont.Pon.PonGroup.Symbol == sro.AreaSymbol && ont.Pon.Number == sro.Pon && ont.Number == sro.Ont)
 856:                      {
 857:                      }
 858:                      else
 859:                      {
 860:                          //sb.AppendLine(" wrong:" + w.Id + "'");
 861:                          list.Add(sro.Id);
 862:                      }
 863:                  }
 864:                  else
 865:                  {
 866:                      //sb.AppendLine(" missing: " + w.Id + "'");
 867:                      list.Add(sro.Id);
 868:                  }
 869:              }
 870:   
 871:              return list;
 872:          }
 873:   
 874:          ////////////////////////////////////////////////////////////////////////////
 875:   
 876:          /// <summary>
 877:          ///
 878:          /// </summary>
 879:          public static void DeleteOfnDatabaseRecordsAndConstructDeleteSqlForOracleDatabaseRecordsThatReferenceNonExistantOntsInNdd(out string ontDataSql, out string ontDetailsSql)
 880:          {
 881:              string sqlWhereString;
 882:              StringBuilder sb;
 883:              List<string> list;
 884:   
 885:              sb = new StringBuilder();
 886:   
 887:              ontDataSql = ontDetailsSql = string.Empty;
 888:   
 889:              list = ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList();
 890:   
 891:              if (list.Count < 2000)
 892:              {
 893:                  if (list.Count > 0)
 894:                  {
 895:                      DeleteOfnDatabaseRecordsThatReferenceNonExistantOntsInNdd(list);
 896:   
 897:                      sqlWhereString = string.Empty;
 898:   
 899:                      foreach (string s in list) sqlWhereString += "{variable} = '" + s + "' or ";
 900:                      sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 4, 4);
 901:   
 902:                      ontDetailsSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));
 903:   
 904:                      ontDataSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));
 905:                  }
 906:                  else
 907:                  {
 908:   
 909:                  }
 910:              }
 911:              else
 912:              {
 913:                  throw new Exception("list.Count >=2000");
 914:              }
 915:          }
 916:   
 917:          ////////////////////////////////////////////////////////////////////////////
 918:   
 919:          /// <summary>
 920:          ///
 921:          /// </summary>
 922:          private static void DeleteOfnDatabaseRecordsThatReferenceNonExistantOntsInNdd(List<string> list)
 923:          {
 924:              string sqlWhereString;
 925:              StringBuilder sb;
 926:   
 927:              sb = new StringBuilder();
 928:   
 929:              if (list.Count > 0)
 930:              {
 931:                  sqlWhereString = string.Empty;
 932:   
 933:                  foreach (string s in list) sqlWhereString += "{variable} = '" + s + "' or ";
 934:                  sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 4, 4);
 935:   
 936:                  sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));
 937:   
 938:                  sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));
 939:   
 940:                  // for testing
 941:                  //sb = sb.Replace("delete from","select * from");
 942:   
 943:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 944:                  {
 945:                      db.Database.ExecuteSqlRaw(sb.ToString());
 946:                  }
 947:              }
 948:              else
 949:              {
 950:   
 951:              }
 952:          }
 953:   
 954:          ////////////////////////////////////////////////////////////////////////////
 955:   
 956:          /// <summary>
 957:          ///
 958:          /// </summary>
 959:          public static int NextVacantFlatTermIdForHuaweiEmsOntEquipmentTypeIdAndNokiaGatewayId(Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentType equipmentType, int gatewayId)
 960:          {
 961:              int ft;
 962:              List<int> list;
 963:   
 964:              list = Ia.Ngn.Cl.Model.Data.Nokia.AgcfEndpoint.UsedFlatTermIdListForGatewayId(gatewayId);
 965:   
 966:              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
 967:              else
 968:              {
 969:                  list = Ia.Cl.Model.Default.ExcludedNumberListFromNumberListWithinRange(list, equipmentType.TelPorts);
 970:   
 971:                  if (list.Count > 0) ft = list[0];
 972:                  else ft = Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown; // something went wrong
 973:              }
 974:   
 975:              return ft;
 976:          }
 977:   
 978:          ////////////////////////////////////////////////////////////////////////////
 979:          ////////////////////////////////////////////////////////////////////////////
 980:   
 981:          /// <summary>
 982:          ///
 983:          /// </summary>
 984:          public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(List<string> oltSymbolList)
 985:          {
 986:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 987:              {
 988:                  var oltIdList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList where oltSymbolList.Contains(o.Symbol) select o.Id).ToList();
 989:   
 990:                  var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntForOltSymbolListDictionary(oltSymbolList);
 991:   
 992:                  var list = (from a in db.Accesses
 993:                              join eo in db.EmsOnts on a.Id equals eo.Access.Id into eos
 994:                              from eo in eos.DefaultIfEmpty()
 995:                              join ed in db.EmsDevs on eo.DID equals ed.DID into eds
 996:                              from ed in eds.DefaultIfEmpty()
 997:                              where oltIdList.Contains(a.Olt)
 998:                              select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber
 999:                              {
1000:                                  AccessId = a.Id,
1001:                                  DevFnSnPnOntId = ed.DEV + "-" + eo.FN + "-" + eo.SN + "-" + eo.PN + "-" + eo.ONTID,
1002:                                  Vaprof = eo.VAPROF
1003:                              }).Distinct().ToList();
1004:   
1005:                  foreach (var u in list)
1006:                  {
1007:                      u.Ont = ontAccessIdToOntForOltSymbolListDictionary.ContainsKey(u.AccessId) ? ontAccessIdToOntForOltSymbolListDictionary[u.AccessId] : null;
1008:                  }
1009:   
1010:                  return list;
1011:              }
1012:          }
1013:   
1014:          ////////////////////////////////////////////////////////////////////////////
1015:   
1016:          /// <summary>
1017:          ///
1018:          /// </summary>
1019:          public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
1020:          {
1021:              List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
1022:   
1023:              var _list = AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(oltSymbolList);
1024:   
1025:              list = (from l in _list where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" select l).ToList();
1026:   
1027:              return list;
1028:          }
1029:   
1030:          ////////////////////////////////////////////////////////////////////////////
1031:   
1032:          /// <summary>
1033:          ///
1034:          /// </summary>
1035:          public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> DistinctNddOntWhereVaprofIsNotAluSipBAndIgnoreMduForOltSymbolListList(List<string> oltSymbolList)
1036:          {
1037:              // I will ignore MDU because they have to be converted to SIP manually by Huawei
1038:   
1039:              List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list, list2;
1040:   
1041:              var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1042:   
1043:              list = (from l in _list
1044:                      where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" && l.EmsOntFamilyType != Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu
1045:                      select l).ToList();
1046:   
1047:              list2 = new List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber>(list.Count);
1048:   
1049:              list2 = (from l in list select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber { Ont = l.Ont }).Distinct().ToList();
1050:   
1051:              return list2;
1052:          }
1053:   
1054:          ////////////////////////////////////////////////////////////////////////////
1055:   
1056:          /// <summary>
1057:          ///
1058:          /// </summary>
1059:          public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> ServiceAndNddOntAndEmsOntFamilyTypeAndPortWhereAgcfEndpointPortIsNot0AndVaprofIsAluSipBAndSfuEmsOntSipInfoTelDoesNotExistOrMduEmsVagIsSipAndMduEmsVoipPstnUserPnDoesNotExistForOltSymbolListList(List<string> oltSymbolList)
1060:          {
1061:              List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
1062:   
1063:              var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1064:   
1065:              list = (from l in _list
1066:                      where l.Ont != null && l.Port != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown // meaning AgcfEndpoints is not null and Port = ep.FlatTermID
1067:                      &&
1068:                      (
1069:                      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
1070:                      ||
1071:                      l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn == Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
1072:                      )
1073:                      select l).ToList();
1074:   
1075:              return list;
1076:          }
1077:   
1078:          ////////////////////////////////////////////////////////////////////////////
1079:   
1080:          /// <summary>
1081:          ///
1082:          /// </summary>
1083:          public static List<string> AccessNameWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
1084:          {
1085:              List<string> list;
1086:   
1087:              var _list = AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(oltSymbolList);
1088:   
1089:              list = (from l in _list select l.Ont.Access.Name).ToList();
1090:   
1091:              return list;
1092:          }
1093:   
1094:          ////////////////////////////////////////////////////////////////////////////
1095:   
1096:          /// <summary>
1097:          ///
1098:          /// </summary>
1099:          public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(List<string> oltSymbolList)
1100:          {
1101:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1102:              {
1103:                  var oltIdList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList where oltSymbolList.Contains(o.Symbol) select o.Id).ToList();
1104:   
1105:                  var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntForOltSymbolListDictionary(oltSymbolList);
1106:   
1107:                  var list = (from a in db.Accesses
1108:                              join se in db.Service2 on a.Id equals se.Access.Id
1109:                              join sp in db.SubParties on se.Service equals sp.DisplayName
1110:   
1111:                              join ep in db.AgcfEndpoints on sp.PartyId equals ep.Dn into eps
1112:                              from ep in eps.DefaultIfEmpty()
1113:   
1114:                              join gr in db.AgcfGatewayRecords on ep.AgcfGatewayRecord.Id equals gr.Id into grs
1115:                              from gr in grs.DefaultIfEmpty()
1116:   
1117:                              join eo in db.EmsOnts on a.Id equals eo.Access.Id into eos
1118:                              from eo in eos.DefaultIfEmpty()
1119:   
1120:                              join ed in db.EmsDevs on eo.DID equals ed.DID into eds
1121:                              from ed in eds.DefaultIfEmpty()
1122:   
1123:                              join eosi in db.EmsOntSipInfoes on eo.Id equals eosi.EmsOnt.Id into eosis
1124:                              from eosi in eosis.DefaultIfEmpty()
1125:   
1126:                              join evpu in db.EmsVoipPstnUsers on eo.Id equals evpu.EmsOnt.Id into evpus
1127:                              from evpu in evpus.DefaultIfEmpty()
1128:   
1129:                              join ev in db.EmsVags on eo.Id equals ev.EmsOnt.Id into evs
1130:                              from ev in evs.DefaultIfEmpty()
1131:   
1132:                              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))
1133:   
1134:                              select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber
1135:                              {
1136:                                  AccessId = a.Id,
1137:                                  DevFnSnPnOntId = ed.DEV + "-" + eo.FN + "-" + eo.SN + "-" + eo.PN + "-" + eo.ONTID,
1138:                                  Vaprof = eo.VAPROF,
1139:                                  EquipmentId = eo.EQUIPMENTID,
1140:                                  EmsOntSipInfoTel = eosi == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : eosi.TEL,
1141:                                  EmsVoipPstnUserPn = evpu == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : evpu.PN,
1142:                                  EmsVagProtocolType = ev == null ? string.Empty : ev.PROTOCOLTYPE,
1143:                                  AgcfGatewayRecordIp = gr == null ? string.Empty : gr.IP1,
1144:                                  Service = se.Service,
1145:                                  Port = ep == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : ep.FlatTermID,
1146:                                  PrimaryPuidcpeProfileNumber = sp.PrimaryPUIDCPEProfileNumber
1147:                              }).ToList();
1148:   
1149:                  foreach (var u in list)
1150:                  {
1151:                      u.Ont = ontAccessIdToOntForOltSymbolListDictionary.ContainsKey(u.AccessId) ? ontAccessIdToOntForOltSymbolListDictionary[u.AccessId] : null;
1152:   
1153:                      u.EmsOntFamilyType = (from e in Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentTypeList where e.SystemNameList.Contains(u.EquipmentId) select e.FamilyType).Single();
1154:                  }
1155:   
1156:                  return list;
1157:              }
1158:          }
1159:   
1160:          ////////////////////////////////////////////////////////////////////////////
1161:   
1162:          /// <summary>
1163:          ///
1164:          /// </summary>
1165:          public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofOntIpServicePortPrimaryPuidcpeProfileNumberWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
1166:          {
1167:              List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
1168:   
1169:              var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1170:   
1171:              list = (from l in _list where l.Vaprof != "ALU-SIP-B-MS" select l).ToList();
1172:   
1173:              return list;
1174:          }
1175:   
1176:          ////////////////////////////////////////////////////////////////////////////
1177:   
1178:          /// <summary>
1179:          ///
1180:          /// </summary>
1181:          public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10ForOltSymbolListList(List<string> oltSymbolList)
1182:          {
1183:              List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
1184:   
1185:              var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1186:   
1187:              list = (from l in _list 
1188:                      where l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip 
1189:                      select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, AccessName = l.Ont.Access.Name }).ToList();
1190:   
1191:              return list;
1192:          }
1193:   
1194:          ////////////////////////////////////////////////////////////////////////////
1195:   
1196:          /// <summary>
1197:          ///
1198:          /// </summary>
1199:          public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)
1200:          {
1201:              List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
1202:   
1203:              var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1204:   
1205:              list = (from l in _list
1206:                      where l.Vaprof == "ALU-SIP-B-MS" && l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
1207:                      select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, AccessName = l.Ont.Access.Name }).ToList();
1208:   
1209:              return list;
1210:          }
1211:   
1212:          ////////////////////////////////////////////////////////////////////////////
1213:   
1214:          /// <summary>
1215:          ///
1216:          /// </summary>
1217:          public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)
1218:          {
1219:              List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
1220:   
1221:              var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1222:   
1223:              list = (from l in _list
1224:                      where l.Vaprof == "ALU-SIP-B-MS" && l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
1225:                      select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, Ont = l.Ont, AccessName = l.Ont.Access.Name }).ToList();
1226:   
1227:              return list;
1228:          }
1229:   
1230:          ////////////////////////////////////////////////////////////////////////////
1231:   
1232:          /// <summary>
1233:          ///
1234:          /// </summary>
1235:          public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBAndSfuEmsOntSipInfoTelExistsOrAndMduEmsOntVagIsSipAndMduEmsVoipPstnUserPnExistsForOltSymbolListList(List<string> oltSymbolList)
1236:          {
1237:              List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
1238:   
1239:              var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1240:   
1241:              list = (from l in _list
1242:                      where
1243:                      l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip &&
1244:                      (
1245:                      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
1246:                      ||
1247:                      l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
1248:                      )
1249:                      select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, Ont = l.Ont, AccessName = l.Ont.Access.Name }).ToList();
1250:   
1251:              return list;
1252:          }
1253:   
1254:          ////////////////////////////////////////////////////////////////////////////
1255:   
1256:          /// <summary>
1257:          ///
1258:          /// </summary>
1259:          public static Dictionary<int, int> OltIdToCountOfServicesDictionary()
1260:          {
1261:              var dictionary = new Dictionary<int, int>();
1262:   
1263:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1264:              {
1265:                  /*
1266:  select a.Olt, count(s.Service)
1267:  from Service2 s
1268:  left outer join Accesses a on a.Id = s.Access_Id
1269:  where a.Olt is not null
1270:  group by a.Olt
1271:                   */
1272:   
1273:                  dictionary = (from s in db.Service2
1274:                                join a in db.Accesses on s.Access.Id equals a.Id
1275:                                where a != null
1276:                                group a.Olt by a.Olt into g
1277:                                select new { OltId = g.Key, ServiceCount = g.Count() }).ToDictionary(t => t.OltId, t => t.ServiceCount);
1278:              }
1279:   
1280:              return dictionary;
1281:          }
1282:   
1283:          ////////////////////////////////////////////////////////////////////////////
1284:   
1285:          /// <summary>
1286:          ///
1287:          /// </summary>
1288:          public static void InitializeApplicationLists()
1289:          {
1290:              //_ = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntList; useless because this is not saved in SQL database where its accessable in other appliciations
1291:              _ = Ia.Ngn.Cl.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration.AccessToPstnServiceListDictionary;
1292:              _ = Ia.Ngn.Cl.Model.Data.ServiceRequest.AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
1293:              _ = Ia.Ngn.Cl.Model.Data.ServiceRequest.DbNameToServiceListDictionary;
1294:              _ = Ia.Ngn.Cl.Model.Data.Ims.NokiaAccessNameWithPbxList;
1295:          }
1296:   
1297:          ////////////////////////////////////////////////////////////////////////////
1298:   
1299:          /// <summary>
1300:          ///
1301:          /// </summary>
1302:          public static string AccessIdFromSwitchInformation(string service)
1303:          {
1304:              string accessId;
1305:   
1306:              if (!string.IsNullOrEmpty(service))
1307:              {
1308:                  var serviceType = Ia.Ngn.Cl.Model.Business.Service.ServiceType.GponService;
1309:                  var serviceId = Ia.Ngn.Cl.Model.Business.Service.ServiceToServiceId(service, serviceType);
1310:   
1311:                  var router = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterFromService(service);
1312:   
1313:                  if (router != null)
1314:                  {
1315:                      if (router.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Nokia || router.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Huawei)
1316:                      {
1317:                          using (var db = new Ia.Ngn.Cl.Model.Ngn())
1318:                          {
1319:                              if (router.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Nokia)
1320:                              {
1321:                                  var prividUser = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.PrividUser(service);
1322:   
1323:                                  // attempt to find access of No-No non-SIP SFU/MDU
1324:                                  accessId = (from a in db.Accesses
1325:                                              join o in db.Onts on a.Id equals o.Access.Id
1326:                                              join osv in db.OntServiceVoips on o.Id equals osv.Ont.Id
1327:                                              join gr in db.AgcfGatewayRecords on osv.Ip equals gr.IP1
1328:                                              join ep in db.AgcfEndpoints on gr.Id equals ep.AgcfGatewayRecord.Id
1329:                                              where ep.PrividUser == prividUser
1330:                                              select a.Id).FirstOrDefault(); //.SingleOrDefault();
1331:   
1332:                                  if (string.IsNullOrEmpty(accessId))
1333:                                  {
1334:                                      var impi = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Impi(service);
1335:                                      var impuAid = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.ImpuAid(service);
1336:   
1337:                                      // attempt to find access of No-Hu or Hu-Hu SIP SFU
1338:                                      accessId = (from a in db.Accesses
1339:                                                  join eo in db.EmsOnts on a.Id equals eo.Access.Id
1340:                                                  join eosi in db.EmsOntSipInfoes on eo.Id equals eosi.EmsOnt.Id
1341:                                                  where eosi.SIPNAME == impi || eosi.SIPUSERNAME == impuAid
1342:                                                  select a.Id).FirstOrDefault(); //.SingleOrDefault();
1343:   
1344:                                      if (string.IsNullOrEmpty(accessId))
1345:                                      {
1346:                                          var dn = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.ServiceWithCountryCode(service);
1347:   
1348:                                          // attempt to find access of No-Hu or Hu-Hu SIP MDU
1349:                                          accessId = (from a in db.Accesses
1350:                                                      join eo in db.EmsOnts on a.Id equals eo.Access.Id
1351:                                                      join evpu in db.EmsVoipPstnUsers on eo.Id equals evpu.EmsOnt.Id
1352:                                                      where evpu.DN == dn
1353:                                                      select a.Id).FirstOrDefault(); //.SingleOrDefault();
1354:   
1355:                                          if (string.IsNullOrEmpty(accessId))
1356:                                          {
1357:   
1358:                                          }
1359:                                          else
1360:                                          {
1361:   
1362:                                          }
1363:                                      }
1364:                                      else
1365:                                      {
1366:   
1367:                                      }
1368:                                  }
1369:                                  else
1370:                                  {
1371:   
1372:                                  }
1373:                              }
1374:                              else //if(router.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Huawei)
1375:                              {
1376:                                  var impi = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Impi(service);
1377:                                  var impuAid = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.ImpuAid(service);
1378:   
1379:                                  // attempt to find access of No-Hu or Hu-Hu SIP SFU
1380:                                  accessId = (from a in db.Accesses
1381:                                              join eo in db.EmsOnts on a.Id equals eo.Access.Id
1382:                                              join eosi in db.EmsOntSipInfoes on eo.Id equals eosi.EmsOnt.Id
1383:                                              where eosi.SIPNAME == impi || eosi.SIPUSERNAME == impuAid
1384:                                              select a.Id).FirstOrDefault(); //.SingleOrDefault();
1385:   
1386:                                  if (string.IsNullOrEmpty(accessId))
1387:                                  {
1388:                                      var dn = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.ServiceWithCountryCode(service);
1389:   
1390:                                      // attempt to find access of No-Hu or Hu-Hu SIP MDU
1391:                                      accessId = (from a in db.Accesses
1392:                                                  join eo in db.EmsOnts on a.Id equals eo.Access.Id
1393:                                                  join evpu in db.EmsVoipPstnUsers on eo.Id equals evpu.EmsOnt.Id
1394:                                                  where evpu.DN == dn
1395:                                                  select a.Id).FirstOrDefault(); //.SingleOrDefault();
1396:   
1397:                                      if (string.IsNullOrEmpty(accessId))
1398:                                      {
1399:                                          var pui = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.ImpuSipDomain(service);
1400:   
1401:                                          // attempt to find access of Hu-No non-SIP SFU/MDU
1402:                                          accessId = (from a in db.Accesses
1403:                                                      join o in db.Onts on a.Id equals o.Access.Id
1404:                                                      join osv in db.OntServiceVoips on o.Id equals osv.Ont.Id
1405:                                                      join m in db.Mgws on osv.Ip equals m.RA1.Replace(":" + Ia.Ngn.Cl.Model.Business.Huawei.SoftX.RemotePort, "")
1406:                                                      join asb in db.Asbrs on m.Id equals asb.Mgw.Id
1407:                                                      where asb.PUI == pui
1408:                                                      select a.Id).FirstOrDefault(); //.SingleOrDefault();
1409:                                      }
1410:                                      else
1411:                                      {
1412:   
1413:                                      }
1414:                                  }
1415:                                  else
1416:                                  {
1417:   
1418:                                  }
1419:                              }
1420:                          }
1421:                      }
1422:                      else
1423:                      {
1424:                          accessId = string.Empty;
1425:                      }
1426:                  }
1427:                  else
1428:                  {
1429:                      accessId = string.Empty;
1430:                  }
1431:              }
1432:              else
1433:              {
1434:                  accessId = string.Empty;
1435:              }
1436:   
1437:              return accessId;
1438:          }
1439:   
1440:          ////////////////////////////////////////////////////////////////////////////
1441:          ////////////////////////////////////////////////////////////////////////////
1442:      }
1443:  }