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.Model.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.Model.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.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> DifferentOntNameAndStatisticalOntNameList(int siteId)

   66:         {

   67:             string address;

   68:             Ia.Ftn.Cl.Model.Business.ServiceAddress serviceAddress;

   69:             Ia.Ftn.Cl.Model.Access statisticalAccess;

   70:             Ia.Ftn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName serviceCustomerAddressAccessStatisticalAccessName;

   71:  

   72:             var level = string.Empty;

   73:  

   74:             var site = (from s in Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.SiteList

   75:                         where s.Id == siteId

   76:                         select s).SingleOrDefault();

   77:  

   78:  

   79:             var areaIdToNameArabicNameDictionary = Ia.Ftn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;

   80:  

   81:             var list = new List<Ia.Ftn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName>();

   82:  

   83:             using (var db = new Ia.Ftn.Cl.Model.Db())

   84:             {

   85:                 var siteRouterDomainList = (from r in Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.RouterList

   86:                                             where r.Site.Id == siteId

   87:                                             select r).SelectMany(z => z.DomainList).ToList();

   88:  

   89:                 var hashTable = Ia.Ftn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);

   90:  

   91:                 var serviceRequestServiceServiceAccessList = (from srs in db.ServiceRequestServices

   92:                                                               select new { srs.Service, srs.Access }).AsNoTracking().ToList();

   93:  

   94:                 serviceRequestServiceServiceAccessList = (from srs in serviceRequestServiceServiceAccessList

   95:                                                           where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString()))

   96:                                                           select srs).ToList();

   97:  

   98:                 var areaIdList = site.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.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName();

  109:  

  110:                     if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();

  111:                     else address = null;

  112:  

  113:                     serviceCustomerAddressAccessStatisticalAccessName.Service = srs.Service;

  114:                     serviceCustomerAddressAccessStatisticalAccessName.CustomerAddress = address;

  115:                     serviceCustomerAddressAccessStatisticalAccessName.Access = srs.Access;

  116:  

  117:                     if (srs.Access != null) serviceCustomerAddressAccessStatisticalAccessName.OntAddress = srs.Access.Address;

  118:  

  119:                     serviceAddress = Ia.Ftn.Cl.Model.Business.ServiceRequest.ServiceAddress(srs.Service, address, out level);

  120:  

  121:                     // below: this will skip Sabah Al-Salem area

  122:                     //if (l.CustomerAddress != null && !l.CustomerAddress.Contains("صباح"))

  123:                     //{

  124:                     statisticalAccess = null; // Ia.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.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> DifferentOntNameAndStatisticalOntNameList2(int siteId)

  181:         {

  182:             string address, level;

  183:             Hashtable hashTable; //, nokiaInitialInstallationListHashTable;

  184:             Ia.Ftn.Cl.Model.Business.NetworkDesignDocument.Site site;

  185:             Ia.Ftn.Cl.Model.Business.ServiceAddress serviceAddress;

  186:             Ia.Ftn.Cl.Model.Access statisticalAccess;

  187:             Ia.Ftn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName l;

  188:  

  189:             List<int> siteRouterDomainList, areaIdList;

  190:             List<Ia.Ftn.Cl.Model.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.Model.Data.NetworkDesignDocument.SiteList

  199:                     where a.Id == siteId

  200:                     select a).SingleOrDefault();

  201:  

  202:             var ontAccessIdToOntAccessNameDictionary = Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;

  203:  

  204:             areaIdToNameArabicNameDictionary = Ia.Ftn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;

  205:  

  206:             using (var db = new Ia.Ftn.Cl.Model.Db())

  207:             {

  208:                 siteRouterDomainList = (from r in Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.RouterList

  209:                                         where r.Site.Id == siteId

  210:                                         select r).SelectMany(z => z.DomainList).ToList();

  211:                 //siteRouterDomainList = new List<int> { 2538 };

  212:  

  213:                 //serviceRequestList = (from sr in db.ServiceRequests where siteRouterDomainList.Contains(sr.Number / 10000) || siteRouterDomainList.Contains(sr.Number / 1000) select sr).ToList();

  214:  

  215:                 hashTable = Ia.Ftn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);

  216:  

  217:                 var serviceRequestServiceList = (from srs in db.ServiceRequestServices

  218:                                                  where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString()))

  219:                                                  select new { srs.Service, AccessId = (srs.Access != null) ? srs.Access.Id : string.Empty }).ToList();

  220:  

  221:                 areaIdList = site.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.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName>();

  227:  

  228:                 foreach (var srs in serviceRequestServiceList)

  229:                 {

  230:                     l = new Ia.Ftn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName();

  231:  

  232:                     if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();

  233:                     else address = null;

  234:  

  235:                     l.Service = srs.Service;

  236:                     l.CustomerAddress = address;

  237:  

  238:                     if (ontAccessIdToOntAccessNameDictionary.ContainsKey(srs.AccessId)) l.AccessId = srs.AccessId;

  239:                     else l.Access = null;

  240:  

  241:                     if (!string.IsNullOrEmpty(srs.AccessId)) l.OntAddress = string.Empty;// srs.Access.Address;

  242:  

  243:                     //------------------

  244:                     serviceAddress = Ia.Ftn.Cl.Model.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.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();

  320:  

  321:             siteRouterDomainList = (from r in Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.RouterList where r.Site.Id == siteId select r).SelectMany(z => z.DomainList).ToList();

  322:  

  323:             areaIdToNameArabicNameDictionary = Ia.Ftn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;

  324:  

  325:             serviceList = Ia.Ftn.Cl.Model.Data.Service2.List(siteRouterDomainList);

  326:  

  327:             var serviceToAccessIdDictionary = Ia.Ftn.Cl.Model.Data.Service2.ServiceToAccessIdDictionary;

  328:  

  329:             var idToAccessDictionary = Ia.Ftn.Cl.Model.Data.Access.IdToAccessDictionary;

  330:  

  331:             var accessIdToOntDictionary = Ia.Ftn.Cl.Model.Data.Huawei.Ont.AccessIdToOntDictionary;

  332:  

  333:             var serviceToServiceRequestServiceDictionary = Ia.Ftn.Cl.Model.Data.ServiceRequestService.ServiceToServiceRequestServiceDictionary(siteRouterDomainList);

  334:  

  335:             var serviceToServiceRequestDictionary = Ia.Ftn.Cl.Model.Data.ServiceRequest.ServiceToServiceRequestDictionary(siteRouterDomainList);

  336:  

  337:  

  338:             s = "Service\tALIAS\tArea\tBlock\tStreet\tPremises Old\tPACI\tCustomer ID\tCustomer Name\tAddress\tType ID-1\tType ID-2\tType ID 4\tType ID 5";

  339:             sb.AppendLine(s);

  340:  

  341:             foreach (string u in serviceList)

  342:             {

  343:                 service = u;

  344:  

  345:                 if (serviceToAccessIdDictionary.ContainsKey(u))

  346:                 {

  347:                     accessId = serviceToAccessIdDictionary[u];

  348:  

  349:                     if (idToAccessDictionary.ContainsKey(accessId))

  350:                     {

  351:                         areaId = idToAccessDictionary[accessId].AreaId;

  352:                         block = idToAccessDictionary[accessId].Block;

  353:                         street = idToAccessDictionary[accessId].Street;

  354:                         premisesOld = idToAccessDictionary[accessId].PremisesOld;

  355:                         paci = idToAccessDictionary[accessId].Paci;

  356:  

  357:                         if (areaIdToNameArabicNameDictionary.ContainsKey(areaId)) area = areaIdToNameArabicNameDictionary[areaId];

  358:                         else area = string.Empty;

  359:  

  360:                         if (accessIdToOntDictionary.ContainsKey(accessId))

  361:                         {

  362:                             alias = accessIdToOntDictionary[accessId].ALIAS;

  363:                         }

  364:                         else

  365:                         {

  366:                             alias = string.Empty;

  367:                         }

  368:                     }

  369:                     else

  370:                     {

  371:                         block = string.Empty;

  372:                         street = string.Empty;

  373:                         premisesOld = string.Empty;

  374:                         paci = string.Empty;

  375:                         area = string.Empty;

  376:  

  377:                         alias = string.Empty;

  378:                     }

  379:                 }

  380:                 else

  381:                 {

  382:                     block = string.Empty;

  383:                     street = string.Empty;

  384:                     premisesOld = string.Empty;

  385:                     paci = string.Empty;

  386:                     area = string.Empty;

  387:  

  388:                     alias = string.Empty;

  389:                 }

  390:  

  391:  

  392:                 if (serviceToServiceRequestServiceDictionary.ContainsKey(u))

  393:                 {

  394:                     if (serviceToServiceRequestServiceDictionary[u].Provisioned)

  395:                     {

  396:                         if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.Count > 0)

  397:                         {

  398:                             //var bb = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault();

  399:  

  400:                             if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault() != null)

  401:                             {

  402:                                 customerId = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerId;

  403:                                 customerName = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerName;

  404:                                 customerAddress = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerAddress;

  405:  

  406:                                 if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes.Count > 0)

  407:                                 {

  408:                                     var vv = (from srt in serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes

  409:                                               group srt by new { srt.TypeId, srt.Value } into g

  410:                                               select new //Ia.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.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> ServicesWithinSiteWithRelevantServiceRequestTypeList(int siteId)

  537:         {

  538:             Ia.Ftn.Cl.Model.Business.NetworkDesignDocument.Site site;

  539:             List<int> siteKuwaitFtnAreaIdList;

  540:             List<Ia.Ftn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> list;

  541:             Dictionary<int, string> areaIdToSymbolDictionary;

  542:  

  543:             areaIdToSymbolDictionary = Ia.Ftn.Cl.Model.Data.Service.AreaIdToSymbolDictionary;

  544:  

  545:             site = (from s in Ia.Ftn.Cl.Model.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.Model.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.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue

  566:                         {

  567:                             Service = g.Key.Service,

  568:                             AreaSymbol = areaIdToSymbolDictionary[g.Key.AreaId],

  569:                             TypeId1Value = g.Max(u => u.TypeId == 1 ? u.Value : "?"),

  570:                             TypeId2Value = g.Max(u => u.TypeId == 2 ? u.Value : "?"),

  571:                             TypeId3Value = g.Max(u => u.TypeId == 3 ? u.Value : "?"),

  572:                             TypeId4Value = g.Max(u => u.TypeId == 4 ? u.Value : "?"),

  573:                             TypeId5Value = g.Max(u => u.TypeId == 5 ? u.Value : "?"),

  574:                         }).ToList();

  575:  

  576:             }

  577:  

  578:             return list;

  579:         }

  580:  

  581:         ////////////////////////////////////////////////////////////////////////////

  582:  

  583:         /// <summary>

  584:         ///

  585:         /// </summary>

  586:         public static Dictionary<string, int> 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.Model.Data.ServiceRequestService.ProvisionedServiceIdToAccessIdDictionary;

  595:                 accessIdToKuwaitFtnAreaIdDictionary = Ia.Ftn.Cl.Model.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.Model.Data.Service2.ServiceIdToAccessIdDictionary;

  638:                 accessIdToKuwaitFtnAreaIdDictionary = Ia.Ftn.Cl.Model.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.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();

  681:  

  682:             if (list.Count > 40 && list.Count < 100)

  683:             {

  684:                 sqlWhereString = string.Empty;

  685:  

  686:                 foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";

  687:                 sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);

  688:  

  689:                 sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Ont_Id"));

  690:                 sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Id"));

  691:  

  692:                 sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Ont_Id"));

  693:                 sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Id"));

  694:  

  695:                 sb.AppendLine("delete from Events where " + sqlWhereString.Replace("{variable}", "Ont_Id"));

  696:  

  697:                 sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Access_Id"));

  698:                 sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Id"));

  699:  

  700:                 sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "EmsOnt_Id"));

  701:                 sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "Id"));

  702:  

  703:                 sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));

  704:                 sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Id"));

  705:  

  706:                 sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));

  707:                 sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "Id"));

  708:  

  709:                 sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));

  710:                 sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));

  711:  

  712:                 sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Id"));

  713:                 sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Olt"));

  714:  

  715:                 // for testing

  716:                 //sb = sb.Replace("delete from","select * from");

  717:  

  718:                 using (var db = new Ia.Ftn.Cl.Model.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.Model.Business.Default.FixedLengthOfOntId;

  753:             fixedLengthOfAccessId = Ia.Ftn.Cl.Model.Business.Access.FixedLengthOfId;

  754:  

  755:             if (fixedLengthOfOntId > 10 && fixedLengthOfAccessId > 10)

  756:             {

  757:                 sb.AppendLine("delete from OntOntPots where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");

  758:                 sb.AppendLine("delete from OntServiceVoips where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");

  759:                 sb.AppendLine("delete from EmsOntSipInfoes where substring(Id,1, " + fixedLengthOfOntId + ") not like EmsOnt_Id");

  760:  

  761:                 sb.AppendLine("delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id");

  762:                 sb.AppendLine("delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id)");

  763:                 sb.AppendLine("delete from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id");

  764:  

  765:                 // for testing

  766:                 //sb = sb.Replace("delete from","select * from");

  767:  

  768:                 using (var db = new Ia.Ftn.Cl.Model.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.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();

  790:  

  791:             if (list.Count > 40 && list.Count < 100)

  792:             {

  793:                 if (list.Count > 0)

  794:                 {

  795:                     sqlWhereString = string.Empty;

  796:  

  797:                     foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";

  798:                     sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);

  799:  

  800:                     ontDataSql = Ia.Ftn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));

  801:  

  802:                     ontDetailsSql = Ia.Ftn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));

  803:                 }

  804:                 else

  805:                 {

  806:                     ontDataSql = ontDetailsSql = string.Empty;

  807:  

  808:                     throw new Exception("list.Count == 0");

  809:                 }

  810:             }

  811:             else

  812:             {

  813:                 throw new Exception("list.Count <= 40 || list.Count >= 100");

  814:             }

  815:         }

  816:  

  817:         ////////////////////////////////////////////////////////////////////////////

  818:  

  819:         /// <summary>

  820:         /// Compare OntList with ServiceRequestOntList to extract extra or wrong ONTs from the later

  821:         /// </summary>

  822:         private static List<string> ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList()

  823:         {

  824:             Ia.Ftn.Cl.Model.Business.NetworkDesignDocument.Ont ont;

  825:             List<string> list;

  826:             List<Ia.Ftn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;

  827:             Dictionary<string, Ia.Ftn.Cl.Model.Business.NetworkDesignDocument.Ont> ontAccessIdToOntDictionary;

  828:  

  829:             list = new List<string>();

  830:  

  831:             ontAccessIdToOntDictionary = Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntDictionary;

  832:  

  833:             serviceRequestOntList = Ia.Ftn.Cl.Model.Data.ServiceRequestOnt.List;

  834:  

  835:             foreach (Ia.Ftn.Cl.Model.ServiceRequestOnt sro in serviceRequestOntList)

  836:             {

  837:                 if (ontAccessIdToOntDictionary.ContainsKey(sro.Id))

  838:                 {

  839:                     ont = ontAccessIdToOntDictionary[sro.Id];

  840:  

  841:                     if (ont.Pon.PonGroup.Symbol == sro.AreaSymbol && ont.Pon.Number == sro.Pon && ont.Number == sro.Ont)

  842:                     {

  843:                     }

  844:                     else

  845:                     {

  846:                         //sb.AppendLine(" wrong:" + w.Id + "'");

  847:                         list.Add(sro.Id);

  848:                     }

  849:                 }

  850:                 else

  851:                 {

  852:                     //sb.AppendLine(" missing: " + w.Id + "'");

  853:                     list.Add(sro.Id);

  854:                 }

  855:             }

  856:  

  857:             return list;

  858:         }

  859:  

  860:         ////////////////////////////////////////////////////////////////////////////

  861:  

  862:         /// <summary>

  863:         ///

  864:         /// </summary>

  865:         public static void 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.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));

  889:  

  890:                     ontDataSql = Ia.Ftn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));

  891:                 }

  892:                 else

  893:                 {

  894:  

  895:                 }

  896:             }

  897:             else

  898:             {

  899:                 throw new Exception("list.Count >=2000");

  900:             }

  901:         }

  902:  

  903:         ////////////////////////////////////////////////////////////////////////////

  904:  

  905:         /// <summary>

  906:         ///

  907:         /// </summary>

  908:         private static void 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.Model.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.Model.Business.Huawei.Ont.EquipmentType equipmentType, int gatewayId)

  946:         {

  947:             int ft;

  948:             List<int> list;

  949:  

  950:             list = Ia.Ftn.Cl.Model.Data.Nokia.AgcfEndpoint.UsedFlatTermIdListForGatewayId(gatewayId);

  951:  

  952:             if (list.Count == 0) ft = 1; // this means agcfEndpoint does not exist for any number and the box is empty, and we should create agcfEndpoint at 1

  953:             else

  954:             {

  955:                 list = Ia.Cl.Models.Default.ExcludedNumberListFromNumberListWithinRange(list, equipmentType.TelPorts);

  956:  

  957:                 if (list.Count > 0) ft = list[0];

  958:                 else ft = Ia.Ftn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown; // something went wrong

  959:             }

  960:  

  961:             return ft;

  962:         }

  963:  

  964:         ////////////////////////////////////////////////////////////////////////////

  965:         ////////////////////////////////////////////////////////////////////////////

  966:  

  967:         /// <summary>

  968:         ///

  969:         /// </summary>

  970:         public static List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(List<string> oltSymbolList)

  971:         {

  972:             using (var db = new Ia.Ftn.Cl.Model.Db())

  973:             {

  974:                 var oltIdList = (from o in Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.OltList

  975:                                  where oltSymbolList.Contains(o.Symbol)

  976:                                  select o.Id).ToList();

  977:  

  978:                 var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntForOltSymbolListDictionary(oltSymbolList);

  979:  

  980:                 var list = (from a in db.Accesses

  981:                             join eo in db.EmsOnts on a.Id equals eo.Access.Id into eos

  982:                             from eo in eos.DefaultIfEmpty()

  983:                             join ed in db.EmsDevs on eo.DID equals ed.DID into eds

  984:                             from ed in eds.DefaultIfEmpty()

  985:                             where oltIdList.Contains(a.Olt)

  986:                             select new Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber

  987:                             {

  988:                                 AccessId = a.Id,

  989:                                 DevFnSnPnOntId = ed.DEV + "-" + eo.FN + "-" + eo.SN + "-" + eo.PN + "-" + eo.ONTID,

  990:                                 Vaprof = eo.VAPROF

  991:                             }).Distinct().ToList();

  992:  

  993:                 foreach (var u in list)

  994:                 {

  995:                     u.Ont = ontAccessIdToOntForOltSymbolListDictionary.ContainsKey(u.AccessId) ? ontAccessIdToOntForOltSymbolListDictionary[u.AccessId] : null;

  996:                 }

  997:  

  998:                 return list;

  999:             }

 1000:         }

 1001:  

 1002:         ////////////////////////////////////////////////////////////////////////////

 1003:  

 1004:         /// <summary>

 1005:         ///

 1006:         /// </summary>

 1007:         public static List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)

 1008:         {

 1009:             List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;

 1010:  

 1011:             var _list = AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(oltSymbolList);

 1012:  

 1013:             list = (from l in _list where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" select l).ToList();

 1014:  

 1015:             return list;

 1016:         }

 1017:  

 1018:         ////////////////////////////////////////////////////////////////////////////

 1019:  

 1020:         /// <summary>

 1021:         ///

 1022:         /// </summary>

 1023:         public static List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> DistinctNddOntWhereVaprofIsNotAluSipBAndIgnoreMduForOltSymbolListList(List<string> oltSymbolList)

 1024:         {

 1025:             // I will ignore MDU because they have to be converted to SIP manually by Huawei

 1026:  

 1027:             List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list, list2;

 1028:  

 1029:             var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);

 1030:  

 1031:             list = (from l in _list

 1032:                     where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" && l.EmsOntFamilyType != Ia.Ftn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu

 1033:                     select l).ToList();

 1034:  

 1035:             list2 = new List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber>(list.Count);

 1036:  

 1037:             list2 = (from l in list select new Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber { Ont = l.Ont }).Distinct().ToList();

 1038:  

 1039:             return list2;

 1040:         }

 1041:  

 1042:         ////////////////////////////////////////////////////////////////////////////

 1043:  

 1044:         /// <summary>

 1045:         ///

 1046:         /// </summary>

 1047:         public static List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> ServiceAndNddOntAndEmsOntFamilyTypeAndPortWhereAgcfEndpointPortIsNot0AndVaprofIsAluSipBAndSfuEmsOntSipInfoTelDoesNotExistOrMduEmsVagIsSipAndMduEmsVoipPstnUserPnDoesNotExistForOltSymbolListList(List<string> oltSymbolList)

 1048:         {

 1049:             List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;

 1050:  

 1051:             var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);

 1052:  

 1053:             list = (from l in _list

 1054:                     where l.Ont != null && l.Port != Ia.Ftn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown // meaning AgcfEndpoints is not null and Port = ep.FlatTermID

 1055:                     &&

 1056:                     (

 1057:                     l.EmsOntFamilyType == Ia.Ftn.Cl.Model.Business.Huawei.Ont.FamilyType.Sfu && l.Vaprof == "ALU-SIP-B-MS" && l.EmsOntSipInfoTel == Ia.Ftn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown

 1058:                     ||

 1059:                     l.EmsOntFamilyType == Ia.Ftn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn == Ia.Ftn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown

 1060:                     )

 1061:                     select l).ToList();

 1062:  

 1063:             return list;

 1064:         }

 1065:  

 1066:         ////////////////////////////////////////////////////////////////////////////

 1067:  

 1068:         /// <summary>

 1069:         ///

 1070:         /// </summary>

 1071:         public static List<string> AccessNameWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)

 1072:         {

 1073:             List<string> list;

 1074:  

 1075:             var 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.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(List<string> oltSymbolList)

 1088:         {

 1089:             using (var db = new Ia.Ftn.Cl.Model.Db())

 1090:             {

 1091:                 var oltIdList = (from o in Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.OltList

 1092:                                  where oltSymbolList.Contains(o.Symbol)

 1093:                                  select o.Id).ToList();

 1094:  

 1095:                 var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ftn.Cl.Model.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.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber

 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.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : eosi.TEL,

 1131:                                 EmsVoipPstnUserPn = evpu == null ? Ia.Ftn.Cl.Model.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.Model.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.Model.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.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofOntIpServicePortPrimaryPuidcpeProfileNumberWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)

 1156:         {

 1157:             List<Ia.Ftn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> 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.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10ForOltSymbolListList(List<string> oltSymbolList)

 1172:         {

 1173:             List<Ia.Ftn.Cl.Model.Business.ServiceNddOntAccessName> list;

 1174:  

 1175:             var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);

 1176:  

 1177:             list = (from l in list0

 1178:                     where l.PrimaryPuidcpeProfileNumber != Ia.Ftn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip

 1179:                     select new Ia.Ftn.Cl.Model.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.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)

 1190:         {

 1191:             List<Ia.Ftn.Cl.Model.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.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip

 1197:                     select new Ia.Ftn.Cl.Model.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.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)

 1208:         {

 1209:             List<Ia.Ftn.Cl.Model.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.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip

 1215:                     select new Ia.Ftn.Cl.Model.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.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBAndSfuEmsOntSipInfoTelExistsOrAndMduEmsOntVagIsSipAndMduEmsVoipPstnUserPnExistsForOltSymbolListList(List<string> oltSymbolList)

 1226:         {

 1227:             List<Ia.Ftn.Cl.Model.Business.ServiceNddOntAccessName> list;

 1228:  

 1229:             var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);

 1230:  

 1231:             list = (from l in list0

 1232:                     where

 1233:                     l.PrimaryPuidcpeProfileNumber != Ia.Ftn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip &&

 1234:                     (

 1235:                     l.EmsOntFamilyType == Ia.Ftn.Cl.Model.Business.Huawei.Ont.FamilyType.Sfu && l.Vaprof == "ALU-SIP-B-MS" && l.EmsOntSipInfoTel != Ia.Ftn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown

 1236:                     ||

 1237:                     l.EmsOntFamilyType == Ia.Ftn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn != Ia.Ftn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown

 1238:                     )

 1239:                     select new Ia.Ftn.Cl.Model.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.Model.Data.Default.OltIdToCountOfServiceDictionary();

 1252:  

 1253:             var nokiaRouterHuaweiAccessOltIdList = Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.NokiaRouterHuaweiAccessOltIdList;

 1254:  

 1255:             var nokiaRouterHuaweiAccessRouterList = (from o in Ia.Ftn.Cl.Model.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.Model.Data.Default.OltIdToCountOfServiceDictionary();

 1291:  

 1292:             var oltList = Ia.Ftn.Cl.Model.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.Model.Data.Default.OltIdToCountOfServiceDictionary();

 1319:  

 1320:             var oltList = Ia.Ftn.Cl.Model.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.Model.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.Model.Db())

 1378:             {

 1379: #if DEBUG

 1380:                 var dummyVarToDrawRefractorToBelow = Ia.Ftn.Cl.Model.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.Model.Db())

 1403:             {

 1404: #if DEBUG

 1405:                 var dummyVarToDrawRefractorToBelow = Ia.Ftn.Cl.Model.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.Model.Business.Nokia.Ont.EquipmentTypeList.Max(u => u.TelPorts);

 1427:                 var maxHuaweiTelPort = Ia.Ftn.Cl.Model.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.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration.AccessToPstnServiceListDictionary;

 1466:             _ = Ia.Ftn.Cl.Model.Data.ServiceRequest.AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary;

 1467:             _ = Ia.Ftn.Cl.Model.Data.ServiceRequest.DbNameToServiceListDictionary;

 1468:             _ = Ia.Ftn.Cl.Model.Data.Ims.NokiaAccessNameWithPbxList;

 1469:         }

 1470:  

 1471:         ////////////////////////////////////////////////////////////////////////////

 1472:         ////////////////////////////////////////////////////////////////////////////

 1473:     }

 1474: }