)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Home » Code Library » Default (Ia.Ftn.Cl.Models.Data)

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

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