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

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » Default

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

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

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