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