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

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

Access support class for Fixed Telecommunications Network (FTN) data model.

    1: using Microsoft.EntityFrameworkCore;
    2: using System;
    3: using System.Collections.Generic;
    4: using System.Linq;
    5: using System.Text;
    6:  
    7: namespace Ia.Ftn.Cl.Models.Data
    8: {
    9:     ////////////////////////////////////////////////////////////////////////////
   10:  
   11:     /// <summary publish="true">
   12:     /// Access support class for Fixed Telecommunications Network (FTN) data model.
   13:     /// </summary>
   14:     /// 
   15:     /// <remarks> 
   16:     /// Copyright © 2006-2021 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
   17:     /// </remarks> 
   18:     public class Access
   19:     {
   20:         ////////////////////////////////////////////////////////////////////////////
   21:  
   22:         /// <summary>
   23:         ///
   24:         /// </summary>
   25:         public Access() { }
   26:  
   27:         ////////////////////////////////////////////////////////////////////////////
   28:  
   29:         /// <summary>
   30:         ///
   31:         /// </summary>
   32:         public static void Create(Ia.Ftn.Cl.Models.Access newAccess, out Ia.Cl.Models.Result result)
   33:         {
   34:             Ia.Ftn.Cl.Models.Access access;
   35:  
   36:             result = new Ia.Cl.Models.Result();
   37:  
   38:             try
   39:             {
   40:                 using (var db = new Ia.Ftn.Cl.Db())
   41:                 {
   42:                     // check if it exists already
   43:                     access = (from a in db.Accesses where a.Id == newAccess.Id select a).Include(u => u.StaffIdentityUser).SingleOrDefault();
   44:  
   45:                     if (access == null)
   46:                     {
   47:                         newAccess.StaffIdentityUser = (from s in db.StaffIdentityUsers where s.Id == newAccess.StaffIdentityUser.Id select s).SingleOrDefault();
   48:  
   49:                         newAccess.Created = newAccess.Updated = DateTime.UtcNow.AddHours(3);
   50:  
   51:                         db.Accesses.Add(newAccess);
   52:                         db.SaveChanges();
   53:  
   54:                         result.AddSuccess("Access record " + newAccess.Name + " created. ");
   55:                     }
   56:                     else
   57:                     {
   58:                         result.AddError("Access record " + newAccess.Name + " already exists. ");
   59:                     }
   60:                 }
   61:             }
   62:             catch (Exception ex)
   63:             {
   64:                 result.AddError("Exception: Access record was not created: " + ex.Message);
   65:             }
   66:         }
   67:  
   68:         ////////////////////////////////////////////////////////////////////////////
   69:  
   70:         /// <summary>
   71:         ///
   72:         /// </summary>
   73:         public static Ia.Ftn.Cl.Models.Access Read(Db db, string id)
   74:         {
   75:             Ia.Ftn.Cl.Models.Access access;
   76:  
   77:             access = (from a in db.Accesses where a.Id == id select a).SingleOrDefault();
   78:  
   79:             return access;
   80:         }
   81:  
   82:         ////////////////////////////////////////////////////////////////////////////
   83:  
   84:         /// <summary>
   85:         ///
   86:         /// </summary>
   87:         public static Ia.Ftn.Cl.Models.Access Read(string id)
   88:         {
   89:             Ia.Ftn.Cl.Models.Access item;
   90:  
   91:             using (var db = new Ia.Ftn.Cl.Db())
   92:             {
   93:                 item = (from a in db.Accesses where a.Id == id select a).SingleOrDefault();
   94:             }
   95:  
   96:             return item;
   97:         }
   98:  
   99:         ////////////////////////////////////////////////////////////////////////////
  100:  
  101:         /// <summary>
  102:         ///
  103:         /// </summary>
  104:         public static Ia.Ftn.Cl.Models.Access Read(int oltId, int ponNumber, int ontNumber)
  105:         {
  106:             Ia.Ftn.Cl.Models.Access item;
  107:  
  108:             using (var db = new Ia.Ftn.Cl.Db())
  109:             {
  110:                 item = (from a in db.Accesses where a.Olt == oltId && a.Pon == ponNumber && a.Ont == ontNumber select a).SingleOrDefault();
  111:             }
  112:  
  113:             return item;
  114:         }
  115:  
  116:         ////////////////////////////////////////////////////////////////////////////
  117:  
  118:         /// <summary>
  119:         ///
  120:         /// </summary>
  121:         public static Ia.Ftn.Cl.Models.Access Read(Db db, int oltId, int ponNumber, int ontNumber)
  122:         {
  123:             return (from a in db.Accesses where a.Olt == oltId && a.Pon == ponNumber && a.Ont == ontNumber select a).SingleOrDefault();
  124:         }
  125:  
  126:         ////////////////////////////////////////////////////////////////////////////
  127:  
  128:         /// <summary>
  129:         ///
  130:         /// </summary>
  131:         public static Ia.Ftn.Cl.Models.Access ReadBySerialInNote(string serial)
  132:         {
  133:             List<Ia.Ftn.Cl.Models.Access> list;
  134:  
  135:             if (!string.IsNullOrEmpty(serial))
  136:             {
  137:                 using (var db = new Ia.Ftn.Cl.Db())
  138:                 {
  139:                     list = (from a in db.Accesses where a.Note.Contains(serial) select a).ToList();
  140:                 }
  141:             }
  142:             else list = new List<Ia.Ftn.Cl.Models.Access>();
  143:  
  144:             return list.FirstOrDefault();
  145:         }
  146:  
  147:         ////////////////////////////////////////////////////////////////////////////
  148:  
  149:         /// <summary>
  150:         ///
  151:         /// </summary>
  152:         public static List<Ia.Ftn.Cl.Models.Access> ReadListByPaci(string paci)
  153:         {
  154:             List<Ia.Ftn.Cl.Models.Access> list;
  155:  
  156:             if (!string.IsNullOrEmpty(paci))
  157:             {
  158:                 using (var db = new Ia.Ftn.Cl.Db())
  159:                 {
  160:                     list = (from a in db.Accesses where a.Paci == paci select a).AsNoTracking().ToList();
  161:                 }
  162:             }
  163:             else list = new List<Ia.Ftn.Cl.Models.Access>();
  164:  
  165:             return list;
  166:         }
  167:  
  168:         ////////////////////////////////////////////////////////////////////////////
  169:  
  170:         /// <summary>
  171:         ///
  172:         /// </summary>
  173:         public static bool PaciExists(string paci)
  174:         {
  175:             bool paciExists;
  176:  
  177:             if (!string.IsNullOrEmpty(paci))
  178:             {
  179:                 using (var db = new Ia.Ftn.Cl.Db())
  180:                 {
  181:                     paciExists = (from a in db.Accesses where a.Paci == paci select a).Any();
  182:                 }
  183:             }
  184:             else paciExists = false;
  185:  
  186:             return paciExists;
  187:         }
  188:  
  189:         ////////////////////////////////////////////////////////////////////////////
  190:  
  191:         /// <summary>
  192:         ///
  193:         /// </summary>
  194:         public static List<Ia.Ftn.Cl.Models.Access> ReadListByAreaId(int areaId)
  195:         {
  196:             List<Ia.Ftn.Cl.Models.Access> itemList;
  197:  
  198:             using (var db = new Ia.Ftn.Cl.Db())
  199:             {
  200:                 itemList = (from a in db.Accesses where a.AreaId == areaId select a).ToList();
  201:             }
  202:  
  203:             return itemList;
  204:         }
  205:  
  206:         ////////////////////////////////////////////////////////////////////////////
  207:  
  208:         /// <summary>
  209:         ///
  210:         /// </summary>
  211:         public static int AreaIdFromOltIdAndPon(int oltId, int ponNumber)
  212:         {
  213:             int areaId;
  214:  
  215:             using (var db = new Ia.Ftn.Cl.Db())
  216:             {
  217:                 areaId = (from a in db.Accesses where a.Olt == oltId && a.Pon == ponNumber select a.AreaId).FirstOrDefault();
  218:  
  219:                 if (areaId == 0)
  220:                 {
  221:                     areaId = (from a in db.Accesses where a.Olt == oltId select a.AreaId).FirstOrDefault();
  222:                 }
  223:             }
  224:  
  225:             return areaId;
  226:         }
  227:  
  228:         ////////////////////////////////////////////////////////////////////////////
  229:  
  230:         /// <summary>
  231:         ///
  232:         /// </summary>
  233:         public static List<Ia.Ftn.Cl.Models.Access> List()
  234:         {
  235:             List<Ia.Ftn.Cl.Models.Access> list;
  236:  
  237:             using (var db = new Ia.Ftn.Cl.Db())
  238:             {
  239:                 list = (from a in db.Accesses select a).Include(u => u.StaffIdentityUser).AsNoTracking().ToList();
  240:             }
  241:  
  242:             return list;
  243:         }
  244:  
  245:         ////////////////////////////////////////////////////////////////////////////
  246:  
  247:         /// <summary>
  248:         ///
  249:         /// </summary>
  250:         public static List<Ia.Ftn.Cl.Models.Access> ListWithOntsAndEmsOnts()
  251:         {
  252:             List<Ia.Ftn.Cl.Models.Access> list;
  253:  
  254:             using (var db = new Ia.Ftn.Cl.Db())
  255:             {
  256:                 list = (from a in db.Accesses select a).Include(u => u.Onts).Include(u => u.EmsOnts).ToList();
  257:             }
  258:  
  259:             return list;
  260:         }
  261:  
  262:         ////////////////////////////////////////////////////////////////////////////
  263:  
  264:         /// <summary>
  265:         ///
  266:         /// </summary>
  267:         public static List<Ia.Ftn.Cl.Models.Access> List(Ia.Ftn.Cl.Models.Business.NetworkDesignDocument.Olt olt)
  268:         {
  269:             List<Ia.Ftn.Cl.Models.Access> list;
  270:  
  271:             using (var db = new Ia.Ftn.Cl.Db())
  272:             {
  273:                 list = (from a in db.Accesses where a.Olt == olt.Id select a).ToList();
  274:             }
  275:  
  276:             return list;
  277:         }
  278:  
  279:         ////////////////////////////////////////////////////////////////////////////
  280:  
  281:         /// <summary>
  282:         ///
  283:         /// </summary>
  284:         public static List<Ia.Ftn.Cl.Models.Access> List(int oltId, int ponNumber)
  285:         {
  286:             List<Ia.Ftn.Cl.Models.Access> itemList;
  287:  
  288:             using (var db = new Ia.Ftn.Cl.Db())
  289:             {
  290:                 itemList = (from a in db.Accesses where a.Olt == oltId && a.Pon == ponNumber select a).Include(u => u.StaffIdentityUser).ToList();
  291:             }
  292:  
  293:             return itemList;
  294:         }
  295:  
  296:         ////////////////////////////////////////////////////////////////////////////
  297:  
  298:         /// <summary>
  299:         ///
  300:         /// </summary>
  301:         public static bool Update(Ia.Ftn.Cl.Models.Access updatedAccess, out string result)
  302:         {
  303:             bool b;
  304:             Ia.Ftn.Cl.Models.Access access;
  305:  
  306:             b = false;
  307:             result = string.Empty;
  308:  
  309:             using (var db = new Ia.Ftn.Cl.Db())
  310:             {
  311:                 access = (from a in db.Accesses where a.Id == updatedAccess.Id select a).Include(u => u.StaffIdentityUser).SingleOrDefault();
  312:  
  313:                 if (access.Update(updatedAccess))
  314:                 {
  315:                     access.StaffIdentityUser = (from s in db.StaffIdentityUsers where s.Id == access.StaffIdentityUser.Id select s).SingleOrDefault();
  316:  
  317:                     db.Accesses.Attach(access);
  318:                     db.Entry(access).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
  319:                 }
  320:  
  321:                 db.SaveChanges();
  322:  
  323:                 b = true;
  324:             }
  325:  
  326:             return b;
  327:         }
  328:  
  329:         ////////////////////////////////////////////////////////////////////////////
  330:  
  331:         /// <summary>
  332:         ///
  333:         /// </summary>
  334:         public static void Delete(string id, out Ia.Cl.Models.Result result)
  335:         {
  336:             result = new Ia.Cl.Models.Result();
  337:  
  338:             try
  339:             {
  340:                 using (var db = new Ia.Ftn.Cl.Db())
  341:                 {
  342:                     var v = (from a in db.Accesses where a.Id == id select a).FirstOrDefault();
  343:  
  344:                     if (v != null)
  345:                     {
  346:                         db.Accesses.Remove(v);
  347:                         db.SaveChanges();
  348:  
  349:                         result.AddSuccess("Access record deleted. ");
  350:                     }
  351:                     else
  352:                     {
  353:                         result.AddWarning("Access record does not exist. ");
  354:                     }
  355:  
  356:                 }
  357:             }
  358:             catch (Exception ex)
  359:             {
  360:                 result.AddError("Exception: Access record was not deleted: " + ex.Message);
  361:             }
  362:         }
  363:  
  364:         ////////////////////////////////////////////////////////////////////////////
  365:         ////////////////////////////////////////////////////////////////////////////
  366:  
  367:         /// <summary>
  368:         ///
  369:         /// </summary>
  370:         public static List<int> OntsNotInAccessList(List<Ia.Ftn.Cl.Models.Access> accessList)
  371:         {
  372:             // below: produce a list of ONTs between 1 and 32 that are not in ontList
  373:             List<int> ontNotInListArrayList = new List<int>(32);
  374:  
  375:             for (int i = 1; i <= 32; i++)
  376:             {
  377:                 if (accessList != null)
  378:                 {
  379:                     if ((from a in accessList where a.Ont == i select a).SingleOrDefault() == null)
  380:                     {
  381:                         ontNotInListArrayList.Add(i);
  382:                     }
  383:                     else
  384:                     {
  385:                     }
  386:                 }
  387:                 else
  388:                 {
  389:                     ontNotInListArrayList.Add(i);
  390:                 }
  391:             }
  392:  
  393:             return ontNotInListArrayList;
  394:         }
  395:  
  396:         ////////////////////////////////////////////////////////////////////////////
  397:  
  398:         /// <summary>
  399:         ///
  400:         /// </summary>
  401:         public static List<int> OltIdDistinctList
  402:         {
  403:             get
  404:             {
  405:                 List<int> list;
  406:  
  407:                 using (var db = new Ia.Ftn.Cl.Db())
  408:                 {
  409:                     list = (from o in db.Accesses select o.Olt).Distinct().OrderBy(u => u).ToList();
  410:                 }
  411:  
  412:                 return list;
  413:             }
  414:         }
  415:  
  416:         ////////////////////////////////////////////////////////////////////////////
  417:  
  418:         /// <summary>
  419:         ///
  420:         /// </summary>
  421:         public static Ia.Ftn.Cl.Models.Access ReadByAccessName(string accessName)
  422:         {
  423:             Ia.Ftn.Cl.Models.Access access;
  424:  
  425:             // below: this expects accessName in exact format like SUR.12.3
  426:  
  427:             Ia.Ftn.Cl.Models.Business.Access.ExtractOltIdAndPonNumberAndOntNumberFromOntName(accessName, out int oltId, out int pon, out int ont);
  428:  
  429:             using (var db = new Ia.Ftn.Cl.Db())
  430:             {
  431:                 access = (from a in db.Accesses where a.Olt == oltId && a.Pon == pon && a.Ont == ont select a).SingleOrDefault();
  432:             }
  433:  
  434:             return access;
  435:         }
  436:  
  437:         ////////////////////////////////////////////////////////////////////////////
  438:  
  439:         /// <summary>
  440:         ///
  441:         /// </summary>
  442:         public static List<Ia.Ftn.Cl.Models.Access> MduList()
  443:         {
  444:             List<Ia.Ftn.Cl.Models.Access> nokiaAccessList, huaweiAccessList, list;
  445:  
  446:             list = new List<Ia.Ftn.Cl.Models.Access>();
  447:  
  448:             using (var db = new Ia.Ftn.Cl.Db())
  449:             {
  450:                 nokiaAccessList = (from a in db.Accesses
  451:                                    join o in db.Onts on a equals o.Access
  452:                                    where o.FamilyTypeId == (int)Ia.Ftn.Cl.Models.Business.Nokia.Ont.FamilyType.Mdu
  453:                                    || o.FamilyTypeId == (int)Ia.Ftn.Cl.Models.Business.Nokia.Ont.FamilyType.Gmdu
  454:                                    || o.FamilyTypeId == (int)Ia.Ftn.Cl.Models.Business.Nokia.Ont.FamilyType.Sbu
  455:                                    || o.FamilyTypeId == (int)Ia.Ftn.Cl.Models.Business.Nokia.Ont.FamilyType.Soho
  456:                                    select a).ToList();
  457:  
  458:                 var systemNameListList = (from e in Ia.Ftn.Cl.Models.Business.Huawei.Ont.EquipmentTypeList
  459:                                           where e.FamilyType == Ia.Ftn.Cl.Models.Business.Huawei.Ont.FamilyType.Mdu
  460:                                           || e.FamilyType == Ia.Ftn.Cl.Models.Business.Huawei.Ont.FamilyType.Soho
  461:                                           select e.SystemNameList).ToList();
  462:  
  463:                 var equipmentTypeStringList = new List<string>();
  464:  
  465:                 foreach (var systemNameList in systemNameListList)
  466:                 {
  467:                     foreach (string systemName in systemNameList)
  468:                     {
  469:                         equipmentTypeStringList.Add(systemName);
  470:                     }
  471:                 }
  472:  
  473:                 equipmentTypeStringList = equipmentTypeStringList.Distinct().ToList();
  474:  
  475:                 huaweiAccessList = (from a in db.Accesses
  476:                                     join eo in db.EmsOnts on a equals eo.Access
  477:                                     where equipmentTypeStringList.Contains(eo.EQUIPMENTID)
  478:                                     select a).ToList();
  479:             }
  480:  
  481:             list.AddRange(nokiaAccessList);
  482:             list.AddRange(huaweiAccessList);
  483:  
  484:             return list;
  485:         }
  486:  
  487:         ////////////////////////////////////////////////////////////////////////////
  488:  
  489:         /// <summary>
  490:         ///
  491:         /// </summary>
  492:         public static List<string> ReadAccessNameListByKuwaitFtnAreaIdAndBlock(int kuwaitAreaId, string block)
  493:         {
  494:             Dictionary<string, string> ontAccessIdToOntAccessNameDictionary;
  495:             List<string> accessIdList, accessNameList;
  496:  
  497:             if (kuwaitAreaId > 0 && !string.IsNullOrEmpty(block))
  498:             {
  499:                 using (var db = new Ia.Ftn.Cl.Db())
  500:                 {
  501:                     accessIdList = (from a in db.Accesses where a.AreaId == kuwaitAreaId && a.Block == block select a.Id).ToList();
  502:                     ontAccessIdToOntAccessNameDictionary = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
  503:  
  504:                     if (accessIdList != null && accessIdList.Count > 0)
  505:                     {
  506:                         accessNameList = new List<string>(accessIdList.Count);
  507:  
  508:                         foreach (string accessId in accessIdList)
  509:                         {
  510:                             if (ontAccessIdToOntAccessNameDictionary.ContainsKey(accessId))
  511:                             {
  512:                                 accessNameList.Add(ontAccessIdToOntAccessNameDictionary[accessId]);
  513:                             }
  514:                         }
  515:                     }
  516:                     else
  517:                     {
  518:                         accessNameList = new List<string>();
  519:                     }
  520:                 }
  521:             }
  522:             else
  523:             {
  524:                 accessNameList = new List<string>();
  525:             }
  526:  
  527:             return accessNameList;
  528:         }
  529:  
  530:         ////////////////////////////////////////////////////////////////////////////
  531:  
  532:         /// <summary>
  533:         ///
  534:         /// </summary>
  535:         public static List<Ia.Ftn.Cl.Models.Access> ListByKuwaitFtnAreaId(int kuwaitAreaId)
  536:         {
  537:             List<Ia.Ftn.Cl.Models.Access> list;
  538:  
  539:             if (kuwaitAreaId > 0)
  540:             {
  541:                 using (var db = new Ia.Ftn.Cl.Db())
  542:                 {
  543:                     list = (from a in db.Accesses where a.AreaId == kuwaitAreaId select a).ToList();
  544:                 }
  545:             }
  546:             else
  547:             {
  548:                 list = new List<Ia.Ftn.Cl.Models.Access>();
  549:             }
  550:  
  551:             return list;
  552:         }
  553:  
  554:         ////////////////////////////////////////////////////////////////////////////
  555:  
  556:         /// <summary>
  557:         ///
  558:         /// </summary>
  559:         public static List<string> NameListByKuwaitFtnAreaId(int kuwaitAreaId)
  560:         {
  561:             List<string> list;
  562:  
  563:             if (kuwaitAreaId > 0)
  564:             {
  565:                 var ontAccessIdToOntAccessNameDictionary = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
  566:  
  567:                 using (var db = new Ia.Ftn.Cl.Db())
  568:                 {
  569:                     var accessIdList = (from a in db.Accesses
  570:                                         where a.AreaId == kuwaitAreaId
  571:                                         select a.Id).ToList();
  572:  
  573:                     list = new List<string>(accessIdList.Count);
  574:  
  575:                     foreach (string accessId in accessIdList)
  576:                     {
  577:                         if (ontAccessIdToOntAccessNameDictionary.ContainsKey(accessId))
  578:                         {
  579:                             list.Add(ontAccessIdToOntAccessNameDictionary[accessId]);
  580:                         }
  581:                     }
  582:                 }
  583:             }
  584:             else list = new List<string>();
  585:  
  586:             return list;
  587:         }
  588:  
  589:         ////////////////////////////////////////////////////////////////////////////
  590:  
  591:         /// <summary>
  592:         ///
  593:         /// </summary>
  594:         public static Dictionary<string, string> DistinctAccessKuwaitFtnAreaIdAndBlockDictionary
  595:         {
  596:             get
  597:             {
  598:                 string kuwaitFtnAreaIdString, blockString, kuwaitFtnAreaBlockValue, kuwaitFtnAreaNameArabicName;
  599:  
  600:                 var dictionary = new Dictionary<string, string>();
  601:  
  602:                 var kuwaitFtnAreaList = Ia.Ftn.Cl.Models.Data.Service.KuwaitFtnAreaList;
  603:  
  604:                 using (var db = new Ia.Ftn.Cl.Db())
  605:                 {
  606:                     var tempDictionary = (from a in db.Accesses
  607:                                           select new { a.AreaId, a.Block }).Distinct().OrderBy(u => u.AreaId).ThenBy(u => u.Block).ToDictionary(m => m.AreaId + "," + m.Block, m => m.AreaId + "," + m.Block);
  608:  
  609:                     if (tempDictionary != null && tempDictionary.Count > 0)
  610:                     {
  611:                         foreach (KeyValuePair<string, string> kvp in tempDictionary)
  612:                         {
  613:                             kuwaitFtnAreaIdString = kvp.Key.Split(',')[0].ToString();
  614:                             blockString = kvp.Key.Split(',')[1].ToString();
  615:  
  616:                             if (int.TryParse(kuwaitFtnAreaIdString, out int kuwaitFtnAreaId) && int.TryParse(blockString, out int block))
  617:                             {
  618:                                 kuwaitFtnAreaNameArabicName = (from kna in kuwaitFtnAreaList
  619:                                                                where kna.Id == kuwaitFtnAreaId
  620:                                                                select kna.NameArabicName).SingleOrDefault();
  621:  
  622:                                 kuwaitFtnAreaBlockValue = kuwaitFtnAreaNameArabicName + ", block " + block;
  623:  
  624:                                 dictionary.Add(kvp.Key, kuwaitFtnAreaBlockValue);
  625:                             }
  626:                             else
  627:                             {
  628:                             }
  629:                         }
  630:                     }
  631:                     else
  632:                     {
  633:                     }
  634:                 }
  635:  
  636:                 return dictionary;
  637:             }
  638:         }
  639:  
  640:         ////////////////////////////////////////////////////////////////////////////
  641:  
  642:         /// <summary>
  643:         ///
  644:         /// </summary>
  645:         public static Dictionary<int, string> DistinctAccessKuwaitFtnAreaIdToNameArabicNameDictionary
  646:         {
  647:             get
  648:             {
  649:                 Ia.Ftn.Cl.Models.Business.Service.KuwaitFtnArea kuwaitFtnArea;
  650:                 List<int> list;
  651:                 Dictionary<int, string> dictionary;
  652:  
  653:                 dictionary = new Dictionary<int, string>();
  654:                 list = new List<int>();
  655:  
  656:                 using (var db = new Ia.Ftn.Cl.Db())
  657:                 {
  658:                     list = (from a in db.Accesses select a.AreaId).Distinct().ToList();
  659:  
  660:                     if (list.Count > 0)
  661:                     {
  662:                         foreach (int i in list)
  663:                         {
  664:                             kuwaitFtnArea = Ia.Ftn.Cl.Models.Data.Service.KuwaitFtnAreaById(i);
  665:  
  666:                             if (kuwaitFtnArea != null)
  667:                             {
  668:                                 dictionary.Add(i, kuwaitFtnArea.NameArabicName);
  669:                             }
  670:                         }
  671:                     }
  672:                     else
  673:                     {
  674:                     }
  675:                 }
  676:  
  677:                 return dictionary;
  678:             }
  679:         }
  680:  
  681:         ////////////////////////////////////////////////////////////////////////////
  682:  
  683:         /// <summary>
  684:         ///
  685:         /// </summary>
  686:         public static Dictionary<string, int> IdToKuwaitFtnAreaIdDictionary
  687:         {
  688:             get
  689:             {
  690:                 Dictionary<string, int> dictionary;
  691:  
  692:                 using (var db = new Ia.Ftn.Cl.Db())
  693:                 {
  694:                     dictionary = (from a in db.Accesses select new { a.Id, a.AreaId }).AsNoTracking().ToDictionary(u => u.Id, u => u.AreaId);
  695:                 }
  696:  
  697:                 return dictionary;
  698:             }
  699:         }
  700:  
  701:         ////////////////////////////////////////////////////////////////////////////
  702:  
  703:         /// <summary>
  704:         ///
  705:         /// </summary>
  706:         public static Dictionary<string, string> IdToKuwaitFtnAreaSymbolDictionary
  707:         {
  708:             get
  709:             {
  710:                 Dictionary<string, int> accessIdToKuwaitFtnAreaIdDictionary;
  711:                 Dictionary<int, string> areaIdToSymbolDictionary;
  712:                 Dictionary<string, string> dictionary;
  713:  
  714:                 accessIdToKuwaitFtnAreaIdDictionary = Ia.Ftn.Cl.Models.Data.Access.IdToKuwaitFtnAreaIdDictionary;
  715:                 areaIdToSymbolDictionary = Ia.Ftn.Cl.Models.Data.Service.AreaIdToSymbolDictionary;
  716:  
  717:                 dictionary = new Dictionary<string, string>(accessIdToKuwaitFtnAreaIdDictionary.Count);
  718:  
  719:                 foreach (KeyValuePair<string, int> kvp in accessIdToKuwaitFtnAreaIdDictionary)
  720:                 {
  721:                     if (areaIdToSymbolDictionary.ContainsKey(kvp.Value)) dictionary[kvp.Key] = areaIdToSymbolDictionary[kvp.Value];
  722:                 }
  723:  
  724:                 return dictionary;
  725:             }
  726:         }
  727:  
  728:         ////////////////////////////////////////////////////////////////////////////
  729:  
  730:         /// <summary>
  731:         ///
  732:         /// </summary>
  733:         public static Dictionary<string, int> IdToOltIdDictionary
  734:         {
  735:             get
  736:             {
  737:                 Dictionary<string, int> dictionary;
  738:  
  739:                 using (var db = new Ia.Ftn.Cl.Db())
  740:                 {
  741:                     dictionary = (from a in db.Accesses select new { a.Id, a.Olt }).AsNoTracking().ToDictionary(u => u.Id, u => u.Olt);
  742:                 }
  743:  
  744:                 return dictionary;
  745:             }
  746:         }
  747:  
  748:         ////////////////////////////////////////////////////////////////////////////
  749:  
  750:         /// <summary>
  751:         ///
  752:         /// </summary>
  753:         public static Dictionary<string, Ia.Ftn.Cl.Models.Access> IdToAccessDictionary
  754:         {
  755:             get
  756:             {
  757:                 Dictionary<string, Ia.Ftn.Cl.Models.Access> dictionary;
  758:  
  759:                 using (var db = new Ia.Ftn.Cl.Db())
  760:                 {
  761:                     dictionary = (from a in db.Accesses select new { AccessId = a.Id, Access = a }).ToDictionary(u => u.AccessId, u => u.Access);
  762:                 }
  763:  
  764:                 return dictionary;
  765:             }
  766:         }
  767:  
  768:         ////////////////////////////////////////////////////////////////////////////
  769:  
  770:         /// <summary>
  771:         ///
  772:         /// </summary>
  773:         public static Dictionary<string, Ia.Ftn.Cl.Models.Access> IdToHuaweiAccessDictionary
  774:         {
  775:             get
  776:             {
  777:                 List<int> oltIdList;
  778:                 Dictionary<string, Ia.Ftn.Cl.Models.Access> dictionary;
  779:  
  780:                 oltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.HuaweiOltIdList();
  781:  
  782:                 using (var db = new Ia.Ftn.Cl.Db())
  783:                 {
  784:                     dictionary = (from a in db.Accesses where oltIdList.Contains(a.Olt) select new { AccessId = a.Id, Access = a }).ToDictionary(u => u.AccessId, u => u.Access);
  785:                 }
  786:  
  787:                 return dictionary;
  788:             }
  789:         }
  790:  
  791:         ////////////////////////////////////////////////////////////////////////////
  792:  
  793:         /// <summary>
  794:         ///
  795:         /// </summary>
  796:         public static List<string> IdList
  797:         {
  798:             get
  799:             {
  800:                 List<string> list;
  801:  
  802:                 using (var db = new Ia.Ftn.Cl.Db())
  803:                 {
  804:                     list = (from a in db.Accesses select a.Id).AsNoTracking().ToList();
  805:                 }
  806:  
  807:                 return list;
  808:             }
  809:         }
  810:  
  811:         ////////////////////////////////////////////////////////////////////////////
  812:  
  813:         /// <summary>
  814:         ///
  815:         /// </summary>
  816:         public static Dictionary<string, string> IdDictionary
  817:         {
  818:             get
  819:             {
  820:                 Dictionary<string, string> dictionary;
  821:  
  822:                 using (var db = new Ia.Ftn.Cl.Db())
  823:                 {
  824:                     dictionary = (from a in db.Accesses select new { a.Id, AccessId2 = a.Id }).ToDictionary(u => u.Id, u => u.AccessId2);
  825:                 }
  826:  
  827:                 return dictionary;
  828:             }
  829:         }
  830:  
  831:         ////////////////////////////////////////////////////////////////////////////
  832:  
  833:         /// <summary>
  834:         ///
  835:         /// </summary>
  836:         public static List<Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock> FamilyStatisticInKuwaitFtnAreaAndBlockTable
  837:         {
  838:             // FamilyType { Undefined = 0, Sfu = 1, Soho = 2, Mdu = 3 };
  839:  
  840:             get
  841:             {
  842:                 Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock item;
  843:                 List<Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock> list, sfu, soho, mdu;
  844:  
  845:                 list = new List<Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock>();
  846:  
  847:                 using (var db = new Ia.Ftn.Cl.Db())
  848:                 {
  849:                     sfu = (from a in db.Accesses join o in db.Onts on a.Id equals o.Access.Id where o.FamilyTypeId == 1 group a by new { a.AreaId, a.Block } into g select new Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock() { AreaId = g.Key.AreaId, Block = g.Key.Block, Sfu = g.Count() }).ToList();
  850:                     soho = (from a in db.Accesses join o in db.Onts on a.Id equals o.Access.Id where o.FamilyTypeId == 2 group a by new { a.AreaId, a.Block } into g select new Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock() { AreaId = g.Key.AreaId, Block = g.Key.Block, Soho = g.Count() }).ToList();
  851:                     mdu = (from a in db.Accesses join o in db.Onts on a.Id equals o.Access.Id where o.FamilyTypeId == 3 group a by new { a.AreaId, a.Block } into g select new Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock() { AreaId = g.Key.AreaId, Block = g.Key.Block, Mdu = g.Count() }).ToList();
  852:  
  853:                     foreach (Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock a in sfu) list.Add(new Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock { AreaId = a.AreaId, Block = a.Block, Sfu = a.Sfu });
  854:  
  855:                     foreach (Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock a in soho)
  856:                     {
  857:                         item = (from b in list where b.AreaId == a.AreaId && b.Block == a.Block select b).SingleOrDefault();
  858:  
  859:                         if (item != null) item.Soho = a.Soho;
  860:                         else list.Add(new Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock { AreaId = a.AreaId, Block = a.Block, Soho = a.Soho });
  861:                     }
  862:  
  863:                     foreach (Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock a in mdu)
  864:                     {
  865:                         item = (from b in list where b.AreaId == a.AreaId && b.Block == a.Block select b).SingleOrDefault();
  866:  
  867:                         if (item != null) item.Mdu = a.Mdu;
  868:                         else list.Add(new Ia.Ftn.Cl.Models.Ui.Maintenance.AccessFamilyTypeAreaBlock { AreaId = a.AreaId, Block = a.Block, Mdu = a.Mdu });
  869:                     }
  870:  
  871:                     list = list.OrderBy(u => u.AreaId).ThenBy(u => u.Block).ToList();
  872:  
  873:                     return list;
  874:                 }
  875:             }
  876:         }
  877:  
  878:         ////////////////////////////////////////////////////////////////////////////
  879:  
  880:         /// <summary>
  881:         ///
  882:         /// </summary>
  883:         public static List<Ia.Ftn.Cl.Models.Access> ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabase()
  884:         {
  885:             return ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabaseBySiteId(0);
  886:         }
  887:  
  888:         ////////////////////////////////////////////////////////////////////////////
  889:  
  890:         /// <summary>
  891:         ///
  892:         /// </summary>
  893:         public static List<Ia.Ftn.Cl.Models.Access> ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabaseBySiteId(int siteId)
  894:         {
  895:             Ia.Ftn.Cl.Models.Business.NetworkDesignDocument.Site site;
  896:             List<int> nokiaOltIdList, huaweiOltIdList;
  897:             List<Ia.Ftn.Cl.Models.Access> list, nokiaAccessList, huaweiAccessList;
  898:  
  899:             // this is related to AccessesWithProvisionedAndReadyOntsIdToKuwaitFtnAreaIdDictionary
  900:  
  901:             using (var db = new Ia.Ftn.Cl.Db())
  902:             {
  903:                 if (siteId == 0)
  904:                 {
  905:                     // if siteId is 0 we will return results for all sites
  906:  
  907:                     //nokiaOltIdList = Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.NokiaOltIdList();
  908:  
  909:                     // Nokia
  910:                     nokiaAccessList = (from a in db.Accesses
  911:                                            // join oid in nokiaOltIdList on a.Olt equals oid
  912:                                        join o in db.Onts on a equals o.Access
  913:                                        join osv in db.OntServiceVoips on o equals osv.Ont
  914:                                        join sro in db.ServiceRequestOnts on a equals sro.Access into gj
  915:                                        from subsro in gj.DefaultIfEmpty()
  916:                                        where o.StateId == (int)Ia.Ftn.Cl.Models.Business.Nokia.Ams.BellcoreState.IsNr
  917:                                        && o.Serial != null
  918:                                        && o.Serial != "ALCL00000000"
  919:                                        && o.FamilyTypeId != 0
  920:                                        && o.ActiveSoftware != null
  921:                                        //&& o.ActiveSoftware == o.PlannedSoftware
  922:                                        && osv.Ip != null
  923:                                        && subsro.Access.Id == null
  924:                                        select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).AsNoTracking().ToList();
  925:  
  926:                     // Huawei
  927:                     huaweiOltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.HuaweiOltIdList();
  928:  
  929:                     huaweiAccessList = (from a in db.Accesses
  930:                                         join o in db.EmsOnts on a equals o.Access
  931:                                         join sro in db.ServiceRequestOnts on a equals sro.Access into sros
  932:                                         from sro in sros.DefaultIfEmpty()
  933:                                         where o.EQUIPMENTID != string.Empty //.FamilyTypeId != 0
  934:                                         && huaweiOltIdList.Contains(a.Olt)
  935:                                         && sro.Access.Id == null
  936:                                         select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).AsNoTracking().ToList();
  937:  
  938:                     list = nokiaAccessList.Union(huaweiAccessList).ToList();
  939:                 }
  940:                 else
  941:                 {
  942:                     site = (from s in Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.SiteList where s.Id == siteId select s).SingleOrDefault();
  943:  
  944:                     nokiaOltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.NokiaOltIdList(siteId);
  945:  
  946:                     // Nokia
  947:                     nokiaAccessList = (from a in db.Accesses
  948:                                        join oid in nokiaOltIdList on a.Olt equals oid
  949:                                        join o in db.Onts on a equals o.Access
  950:                                        join osv in db.OntServiceVoips on o equals osv.Ont
  951:                                        join sro in db.ServiceRequestOnts on a equals sro.Access into sros
  952:                                        from sro in sros.DefaultIfEmpty()
  953:                                        where o.StateId == (int)Ia.Ftn.Cl.Models.Business.Nokia.Ams.BellcoreState.IsNr
  954:                                        && o.Serial != null
  955:                                        && o.Serial != "ALCL00000000"
  956:                                        && o.FamilyTypeId != 0
  957:                                        && o.ActiveSoftware != null
  958:                                        //&& o.ActiveSoftware == o.PlannedSoftware
  959:                                        && osv.Ip != null
  960:                                        && sro.Access.Id == null
  961:                                        select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).AsNoTracking().ToList();
  962:  
  963:                     // Huawei
  964:                     huaweiOltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.HuaweiOltIdList(siteId);
  965:  
  966:                     huaweiAccessList = (from a in db.Accesses
  967:                                         join o in db.EmsOnts on a equals o.Access
  968:                                         join sro in db.ServiceRequestOnts on a equals sro.Access into sros
  969:                                         from sro in sros.DefaultIfEmpty()
  970:                                         where o.EQUIPMENTID != string.Empty //.FamilyTypeId != 0
  971:                                         && huaweiOltIdList.Contains(a.Olt)
  972:                                         && sro.Access.Id == null
  973:                                         select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).AsNoTracking().ToList();
  974:  
  975:                     list = nokiaAccessList.Union(huaweiAccessList).ToList();
  976:                 }
  977:  
  978:                 // will exclude the following access name list
  979:                 var excludedAccessNameList = Ia.Ftn.Cl.Models.Data.ServiceRequestOnt.AccessNamesThatRevertToAnUnexplainedErroneousStateInTheCustomerDepartmentDatabaseAndAreExemptFromSynchronizationProcessingList();
  980:  
  981:                 list = (from l in list where !excludedAccessNameList.Contains(l.Name) select l).ToList();
  982:             }
  983:  
  984:             return list.OrderBy(u => u.Id).ToList();
  985:         }
  986:  
  987:         ////////////////////////////////////////////////////////////////////////////
  988:  
  989:         /// <summary>
  990:         ///
  991:         /// </summary>
  992:         public static List<Ia.Ftn.Cl.Models.Access> AccessesWithProvisionedAndReadyOntsAndEmsOntsButMismatchedWithCustomerDepartmentDatabaseServiceRequestOntListList()
  993:         {
  994:             List<Ia.Ftn.Cl.Models.Access> list;
  995:  
  996:             var accessCapacityDictionary = Ia.Ftn.Cl.Models.Data.Access.AccessCapacityDictionary();
  997:  
  998:             using (var db = new Ia.Ftn.Cl.Db())
  999:             {
 1000:                 // I will ignore area because its fixed with access id, which is shared by both Access and ServiceRequestOnt
 1001:  
 1002:                 var nokiaList = (from a in db.Accesses
 1003:                                  join o in db.Onts on a equals o.Access
 1004:                                  join osv in db.OntServiceVoips on o equals osv.Ont
 1005:                                  join sro in db.ServiceRequestOnts on a equals sro.Access
 1006:                                  select new
 1007:                                  {
 1008:                                      AccessId = a.Id,
 1009:  
 1010:                                      aBlock = a.Block,
 1011:                                      aStreet = a.Street,
 1012:                                      aPremisesOld = a.PremisesOld,
 1013:                                      aPremisesNew = a.PremisesNew,
 1014:                                      aPaci = a.Paci,
 1015:  
 1016:                                      sroBlock = sro.Block,
 1017:                                      sroStreet = sro.Street,
 1018:                                      sroPremisesOld = sro.PremisesOld,
 1019:                                      sroPremisesNew = sro.PremisesNew,
 1020:                                      sroPaci = sro.Paci,
 1021:                                      sroPossibleNumberOfTd = sro.PossibleNumberOfTd,
 1022:                                      sroPossibleNumberOfEthernet = sro.PossibleNumberOfEthernet
 1023:                                  }
 1024:                             ).AsNoTracking().ToList();
 1025:  
 1026:                 // Huawei
 1027:                 var huaweiOltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.HuaweiOltIdList();
 1028:  
 1029:                 var huaweiList = (from a in db.Accesses
 1030:                                   join o in db.EmsOnts on a equals o.Access
 1031:                                   join sro in db.ServiceRequestOnts on a equals sro.Access
 1032:                                   where huaweiOltIdList.Contains(a.Olt)
 1033:                                   select new
 1034:                                   {
 1035:                                       AccessId = a.Id,
 1036:  
 1037:                                       aBlock = a.Block,
 1038:                                       aStreet = a.Street,
 1039:                                       aPremisesOld = a.PremisesOld,
 1040:                                       aPremisesNew = a.PremisesNew,
 1041:                                       aPaci = a.Paci,
 1042:  
 1043:                                       sroBlock = sro.Block,
 1044:                                       sroStreet = sro.Street,
 1045:                                       sroPremisesOld = sro.PremisesOld,
 1046:                                       sroPremisesNew = sro.PremisesNew,
 1047:                                       sroPaci = sro.Paci,
 1048:                                       sroPossibleNumberOfTd = sro.PossibleNumberOfTd,
 1049:                                       sroPossibleNumberOfEthernet = sro.PossibleNumberOfEthernet
 1050:                                   }
 1051:                                  ).AsNoTracking().ToList();
 1052:  
 1053:                 var list0 = nokiaList.Union(huaweiList).ToList();
 1054:  
 1055:                 var mismatchedAccessIdlist = (from l in list0
 1056:                                               where accessCapacityDictionary.ContainsKey(l.AccessId) &&
 1057:                                               (
 1058:                                               l.sroBlock != l.aBlock && !string.IsNullOrEmpty(l.aBlock)
 1059:                                               || l.sroStreet != l.aStreet && !string.IsNullOrEmpty(l.aStreet)
 1060:                                               || l.sroPremisesOld != l.aPremisesOld && !string.IsNullOrEmpty(l.aPremisesOld)
 1061:                                               || l.sroPremisesNew != l.aPremisesNew && !string.IsNullOrEmpty(l.aPremisesNew)
 1062:                                               || l.sroPaci != l.aPaci && !string.IsNullOrEmpty(l.aPaci)
 1063:                                               || l.sroPossibleNumberOfTd != accessCapacityDictionary[l.AccessId].PossibleNumberOfTd
 1064:                                               || l.sroPossibleNumberOfEthernet != accessCapacityDictionary[l.AccessId].PossibleNumberOfEthernet
 1065:                                               )
 1066:                                               select l.AccessId).ToList();
 1067:  
 1068:                 list = (from a in db.Accesses
 1069:                         where mismatchedAccessIdlist.Contains(a.Id)
 1070:                         select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).ToList();
 1071:  
 1072:                 // will exclude the following access name list
 1073:                 var excludedAccessNameList = Ia.Ftn.Cl.Models.Data.ServiceRequestOnt.AccessNamesThatRevertToAnUnexplainedErroneousStateInTheCustomerDepartmentDatabaseAndAreExemptFromSynchronizationProcessingList();
 1074:  
 1075:                 list = (from l in list where !excludedAccessNameList.Contains(l.Name) select l).ToList();
 1076:             }
 1077:  
 1078:             // debugging
 1079:             // list = list.Where(u => u.Id == "1060204011646001").ToList();
 1080:  
 1081:             return list.OrderBy(u => u.Id).ToList();
 1082:         }
 1083:  
 1084:         ////////////////////////////////////////////////////////////////////////////
 1085:  
 1086:         /// <summary>
 1087:         ///
 1088:         /// </summary>
 1089:         public static List<Ia.Ftn.Cl.Models.Access> AccessesWithMismatchedInfomationInServiceRequestOntList()
 1090:         {
 1091:             List<Ia.Ftn.Cl.Models.Access> list;
 1092:             //Dictionary<int, string> areaIdToSymbolDictionary;
 1093:  
 1094:             //areaIdToSymbolDictionary = Ia.Ftn.Cl.Model.Data.Service.AreaIdToSymbolDictionary;
 1095:  
 1096:             using (var db = new Ia.Ftn.Cl.Db())
 1097:             {
 1098:                 // I will ignore area because its fixed with access id, which is shared by both Access and ServiceRequestOnt
 1099:  
 1100:                 list = (from a in db.Accesses
 1101:                         join o in db.Onts on a equals o.Access
 1102:                         join osv in db.OntServiceVoips on o equals osv.Ont
 1103:                         join sro in db.ServiceRequestOnts on a equals sro.Access
 1104:                         //join areaSymbol in areaIdToSymbolDictionary on a.AreaId equals areaSymbol.Key
 1105:                         //where sro.AreaSymbol != areaSymbol.Value
 1106:                         where (sro.Block != a.Block && !string.IsNullOrEmpty(a.Block))
 1107:                         || (sro.Street != a.Street && !string.IsNullOrEmpty(a.Street))
 1108:                         || (sro.PremisesOld != a.PremisesOld && !string.IsNullOrEmpty(a.PremisesOld))
 1109:                         || (sro.PremisesNew != a.PremisesNew && !string.IsNullOrEmpty(a.PremisesNew))
 1110:                         || (sro.Paci != a.Paci && !string.IsNullOrEmpty(a.Paci))
 1111:                         //|| (sro.PossibleNumberOfTd != a.PossibleNumberOfTd)
 1112:                         //|| (sro.PossibleNumberOfEthernet != a.PossibleNumberOfEthernet)
 1113:                         select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).ToList();
 1114:             }
 1115:  
 1116:             return list.OrderBy(u => u.Id).ToList();
 1117:         }
 1118:  
 1119:         ////////////////////////////////////////////////////////////////////////////    
 1120:  
 1121:         /// <summary>
 1122:         ///
 1123:         /// </summary>
 1124:         public static Dictionary<string, Ia.Ftn.Cl.Models.Business.AccessCapacity> AccessCapacityDictionary()
 1125:         {
 1126:             // Similar to Ia.Ftn.Cl.Model.Data.ServiceRequestOnt.PrepareServiceRequestOntListFromAccessList()
 1127:  
 1128:             bool familyExists;
 1129:             int possibleNumberOfTd, possibleNumberOfEthernet;
 1130:             string accessOntFamilyTypeCapacityString;
 1131:  
 1132:             var list = Ia.Ftn.Cl.Models.Data.Access.ListWithOntsAndEmsOnts();
 1133:  
 1134:             var dictionary = new Dictionary<string, Ia.Ftn.Cl.Models.Business.AccessCapacity>(list.Count);
 1135:  
 1136:             foreach (var access in list)
 1137:             {
 1138:                 if (access.Id == "1050102010044018")
 1139:                 {
 1140:  
 1141:                 }
 1142:  
 1143:                 if (access.Onts != null && access.Onts.Count > 0)
 1144:                 {
 1145:                     if (access.Onts.First().FamilyType != Ia.Ftn.Cl.Models.Business.Nokia.Ont.FamilyType.Undefined)
 1146:                     {
 1147:                         accessOntFamilyTypeCapacityString = Ia.Ftn.Cl.Models.Data.Nokia.Ont.FamilyTypeStringFromId(access.Onts.FirstOrDefault().FamilyTypeId);
 1148:  
 1149:                         possibleNumberOfTd = Ia.Ftn.Cl.Models.Business.Nokia.Ont.PossibleNumberOfTdForOntFamilyType(access.Onts.FirstOrDefault().FamilyTypeId);
 1150:                         possibleNumberOfEthernet = Ia.Ftn.Cl.Models.Business.Nokia.Ont.PossibleNumberOfHsiCardPortServiceConfigurationForOntFamilyType(access.Onts.FirstOrDefault().FamilyTypeId);
 1151:  
 1152:                         accessOntFamilyTypeCapacityString += " (" + possibleNumberOfTd + ")";
 1153:  
 1154:                         familyExists = true;
 1155:                     }
 1156:                     else
 1157:                     {
 1158:                         possibleNumberOfTd = 0;
 1159:                         possibleNumberOfEthernet = 0;
 1160:  
 1161:                         accessOntFamilyTypeCapacityString = string.Empty;
 1162:  
 1163:                         familyExists = false;
 1164:                     }
 1165:                 }
 1166:                 else if (access.EmsOnts != null && access.EmsOnts.Count > 0)
 1167:                 {
 1168:                     var familyType = access.EmsOnts.First().FamilyType;
 1169:                     var equipmentType = access.EmsOnts.First().EquipmentType;
 1170:  
 1171:                     if (familyType != Ia.Ftn.Cl.Models.Business.Huawei.Ont.FamilyType.Undefined && equipmentType != null)
 1172:                     {
 1173:                         accessOntFamilyTypeCapacityString = familyType.ToString().ToUpper();
 1174:  
 1175:                         possibleNumberOfTd = equipmentType.TelPorts;
 1176:                         possibleNumberOfEthernet = equipmentType.EthernetPorts;
 1177:  
 1178:                         accessOntFamilyTypeCapacityString += " (" + possibleNumberOfTd + ")";
 1179:  
 1180:                         familyExists = true;
 1181:                     }
 1182:                     else
 1183:                     {
 1184:                         possibleNumberOfTd = 0;
 1185:                         possibleNumberOfEthernet = 0;
 1186:  
 1187:                         accessOntFamilyTypeCapacityString = string.Empty;
 1188:  
 1189:                         familyExists = false;
 1190:                     }
 1191:                 }
 1192:                 else
 1193:                 {
 1194:                     possibleNumberOfTd = 0;
 1195:                     possibleNumberOfEthernet = 0;
 1196:  
 1197:                     accessOntFamilyTypeCapacityString = string.Empty;
 1198:  
 1199:                     familyExists = false;
 1200:                 }
 1201:  
 1202:                 if (familyExists)
 1203:                 {
 1204:                     dictionary[access.Id] = new Business.AccessCapacity
 1205:                     {
 1206:                         AccessId = access.Id,
 1207:                         PossibleNumberOfTd = possibleNumberOfTd,
 1208:                         PossibleNumberOfEthernet = possibleNumberOfEthernet,
 1209:                         FamilyTypeCapacityString = accessOntFamilyTypeCapacityString
 1210:                     };
 1211:                 }
 1212:                 else
 1213:                 {
 1214:  
 1215:                 }
 1216:             }
 1217:  
 1218:             return dictionary;
 1219:         }
 1220:  
 1221:         ////////////////////////////////////////////////////////////////////////////
 1222:  
 1223:         /// <summary>
 1224:         ///
 1225:         /// </summary>
 1226:         public static Dictionary<string, int> AccessWithProvisionedAndReadyOntIdToKuwaitFtnAreaIdDictionary
 1227:         {
 1228:             get
 1229:             {
 1230:                 Dictionary<string, int> dictionary, nokiaDictionary, huaweiDictionary;
 1231:                 List<int> huaweiOltIdList;//, nokiaOltIdList;
 1232:  
 1233:                 // this is related to ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabaseBySiteId()
 1234:                 // and to AccessWithProvisionedAndReadyOntsIdListByKuwaitFtnAreaId()
 1235:  
 1236:                 using (var db = new Ia.Ftn.Cl.Db())
 1237:                 {
 1238:                     //nokiaOltIdList = Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.NokiaOltIdList();
 1239:  
 1240:                     // Nokia
 1241:                     nokiaDictionary = (from a in db.Accesses
 1242:                                        join o in db.Onts on a equals o.Access
 1243:                                        join osv in db.OntServiceVoips on o equals osv.Ont
 1244:                                        join sro in db.ServiceRequestOnts on a equals sro.Access
 1245:                                        where o.StateId == (int)Ia.Ftn.Cl.Models.Business.Nokia.Ams.BellcoreState.IsNr // ?
 1246:                                        && o.Serial != null
 1247:                                        && o.Serial != "ALCL00000000"
 1248:                                        && o.FamilyTypeId != 0 // ?
 1249:                                        && o.ActiveSoftware != null
 1250:                                        //&& o.ActiveSoftware == o.PlannedSoftware
 1251:                                        && osv.Ip != null
 1252:                                        select new { a.Id, a.AreaId }).AsNoTracking().Distinct().ToDictionary(u => u.Id, u => u.AreaId);
 1253:  
 1254:                     // Huawei
 1255:                     huaweiOltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.HuaweiOltIdList();
 1256:  
 1257:                     huaweiDictionary = (from a in db.Accesses
 1258:                                         join o in db.EmsOnts on a equals o.Access
 1259:                                         join sro in db.ServiceRequestOnts on a equals sro.Access
 1260:                                         //where o.FamilyTypeId != 0
 1261:                                         where o.EQUIPMENTID != "--"
 1262:                                         && huaweiOltIdList.Contains(a.Olt)
 1263:                                         select new { a.Id, a.AreaId }).AsNoTracking().Distinct().ToDictionary(u => u.Id, u => u.AreaId);
 1264:  
 1265:                     dictionary = nokiaDictionary.Union(huaweiDictionary).ToDictionary(k => k.Key, v => v.Value);
 1266:                 }
 1267:  
 1268:                 return dictionary;
 1269:             }
 1270:         }
 1271:  
 1272:         ////////////////////////////////////////////////////////////////////////////
 1273:  
 1274:         /// <summary>
 1275:         ///
 1276:         /// </summary>
 1277:         public static List<string> AccessWithProvisionedAndReadyOntsAccessIdListByKuwaitFtnAreaId(int areaId)
 1278:         {
 1279:             List<string> list, nokiaList, huaweiList;
 1280:             List<int> huaweiOltIdList;//, nokiaOltIdList;
 1281:  
 1282:             // this is related to ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabaseBySiteId()
 1283:             // and to AccessWithProvisionedAndReadyOntIdToKuwaitFtnAreaIdDictionary
 1284:  
 1285:             using (var db = new Ia.Ftn.Cl.Db())
 1286:             {
 1287:                 //nokiaOltIdList = Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.NokiaOltIdList();
 1288:  
 1289:                 // Nokia
 1290:                 nokiaList = (from a in db.Accesses
 1291:                              join o in db.Onts on a equals o.Access
 1292:                              join osv in db.OntServiceVoips on o equals osv.Ont
 1293:                              join sro in db.ServiceRequestOnts on a equals sro.Access
 1294:                              where o.StateId == (int)Ia.Ftn.Cl.Models.Business.Nokia.Ams.BellcoreState.IsNr // ?
 1295:                              && o.Serial != null
 1296:                              && o.Serial != "ALCL00000000"
 1297:                              && o.FamilyTypeId != 0 // ?
 1298:                              && o.ActiveSoftware != null
 1299:                              //&& o.ActiveSoftware == o.PlannedSoftware
 1300:                              && osv.Ip != null
 1301:                              && a.AreaId == areaId
 1302:                              select a.Id).AsNoTracking().Distinct().ToList();
 1303:  
 1304:                 // Huawei
 1305:                 huaweiOltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.HuaweiOltIdList();
 1306:  
 1307:                 huaweiList = (from a in db.Accesses
 1308:                               join o in db.EmsOnts on a equals o.Access
 1309:                               join sro in db.ServiceRequestOnts on a equals sro.Access
 1310:                               //where o.FamilyTypeId != 0
 1311:                               where o.EQUIPMENTID != "--"
 1312:                               && huaweiOltIdList.Contains(a.Olt)
 1313:                               && a.AreaId == areaId
 1314:                               select a.Id).AsNoTracking().Distinct().ToList();
 1315:  
 1316:                 list = nokiaList.Union(huaweiList).ToList();
 1317:             }
 1318:  
 1319:             return list;
 1320:         }
 1321:  
 1322:         ////////////////////////////////////////////////////////////////////////////
 1323:  
 1324:         /// <summary>
 1325:         ///
 1326:         /// </summary>
 1327:         public static List<string> AccessNamesWithoutServicesByKuwaitFtnAreaId(int areaId)
 1328:         {
 1329:             int serviceCount;
 1330:             string accessName;
 1331:  
 1332:             var ll = new List<string>();
 1333:  
 1334:             var list = Ia.Ftn.Cl.Models.Data.Access.AccessWithProvisionedAndReadyOntsAccessIdListByKuwaitFtnAreaId(areaId);
 1335:  
 1336:             var accessIdToAccessNameDictionary = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
 1337:             var accessNameToSeviceCountDictionary = Ia.Ftn.Cl.Models.Data.Service2.AccessNameToSeviceCountDictionary();
 1338:  
 1339:             var dictionary = new Dictionary<string, List<string>>();
 1340:  
 1341:             dictionary["Unknown"] = new List<string>();
 1342:  
 1343:             foreach (var accessId in list)
 1344:             {
 1345:                 if (!string.IsNullOrEmpty(accessId))
 1346:                 {
 1347:                     if (accessIdToAccessNameDictionary.ContainsKey(accessId))
 1348:                     {
 1349:                         accessName = accessIdToAccessNameDictionary[accessId];
 1350:  
 1351:                         if (!string.IsNullOrEmpty(accessName))
 1352:                         {
 1353:                             if (accessNameToSeviceCountDictionary.ContainsKey(accessName))
 1354:                             {
 1355:                                 serviceCount = accessNameToSeviceCountDictionary[accessName];
 1356:  
 1357:                                 if (serviceCount == 0)
 1358:                                 {
 1359:                                     ll.Add(accessName);
 1360:                                 }
 1361:                                 else
 1362:                                 {
 1363:  
 1364:                                 }
 1365:                             }
 1366:                             else
 1367:                             {
 1368:  
 1369:                             }
 1370:                         }
 1371:                         else
 1372:                         {
 1373:  
 1374:                         }
 1375:                     }
 1376:                     else
 1377:                     {
 1378:  
 1379:                     }
 1380:                 }
 1381:                 else
 1382:                 {
 1383:                 }
 1384:             }
 1385:  
 1386:             ll.Sort();
 1387:  
 1388:             return ll;
 1389:         }
 1390:  
 1391:         ////////////////////////////////////////////////////////////////////////////
 1392:  
 1393:         /// <summary>
 1394:         ///
 1395:         /// </summary>
 1396:         public static List<Ia.Ftn.Cl.Models.Access> NokiaAccessList
 1397:         {
 1398:             get
 1399:             {
 1400:                 List<int> oltIdList;
 1401:                 List<Ia.Ftn.Cl.Models.Access> list;
 1402:  
 1403:                 oltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.NokiaOltIdList();
 1404:  
 1405:                 using (var db = new Ia.Ftn.Cl.Db())
 1406:                 {
 1407:                     list = (from a in db.Accesses where oltIdList.Contains(a.Olt) select a).ToList();
 1408:                 }
 1409:  
 1410:                 return list;
 1411:             }
 1412:         }
 1413:  
 1414:         ////////////////////////////////////////////////////////////////////////////
 1415:  
 1416:         /// <summary>
 1417:         ///
 1418:         /// </summary>
 1419:         public static List<Ia.Ftn.Cl.Models.Access> HuaweiAccessList
 1420:         {
 1421:             get
 1422:             {
 1423:                 List<int> oltIdList;
 1424:                 List<Ia.Ftn.Cl.Models.Access> list;
 1425:  
 1426:                 oltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.HuaweiOltIdList();
 1427:  
 1428:                 using (var db = new Ia.Ftn.Cl.Db())
 1429:                 {
 1430:                     list = (from a in db.Accesses where oltIdList.Contains(a.Olt) select a).ToList();
 1431:                 }
 1432:  
 1433:                 return list;
 1434:             }
 1435:         }
 1436:  
 1437:         ////////////////////////////////////////////////////////////////////////////
 1438:  
 1439:         /// <summary>
 1440:         ///
 1441:         /// </summary>
 1442:         public static List<Ia.Ftn.Cl.Models.Access> HuaweiAccessesWithNoEmsOntList
 1443:         {
 1444:             get
 1445:             {
 1446:                 List<int> huaweiOltIdList;
 1447:                 List<Ia.Ftn.Cl.Models.Access> list;
 1448:  
 1449:                 huaweiOltIdList = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.HuaweiOltIdList();
 1450:  
 1451:                 using (var db = new Ia.Ftn.Cl.Db())
 1452:                 {
 1453:                     list = (from a in db.Accesses
 1454:                             join o in db.EmsOnts on a equals o.Access into gj
 1455:                             from subsro in gj.DefaultIfEmpty()
 1456:                             where huaweiOltIdList.Contains(a.Olt) && subsro.Access.Id == null
 1457:                             select a).Distinct().ToList();
 1458:                 }
 1459:  
 1460:                 return list;
 1461:             }
 1462:         }
 1463:  
 1464:         ////////////////////////////////////////////////////////////////////////////
 1465:  
 1466:         /// <summary>
 1467:         ///
 1468:         /// </summary>
 1469:         public static Ia.Ftn.Cl.Models.Access ReadByIp(string ip)
 1470:         {
 1471:             // below: return the Access object of this ip
 1472:  
 1473:             Ia.Ftn.Cl.Models.Access item;
 1474:  
 1475:             using (var db = new Ia.Ftn.Cl.Db())
 1476:             {
 1477:                 item = (from a in db.Accesses
 1478:                         join o in db.Onts on a equals o.Access
 1479:                         join ov in db.OntServiceVoips on o equals ov.Ont
 1480:                         where ov.Ip == ip
 1481:                         select a).SingleOrDefault();
 1482:             }
 1483:  
 1484:             return item;
 1485:         }
 1486:  
 1487:         ////////////////////////////////////////////////////////////////////////////
 1488:  
 1489:         /// <summary>
 1490:         ///
 1491:         /// </summary>
 1492:         public static Ia.Ftn.Cl.Models.Ui.Access StatisticalAccess(Ia.Ftn.Cl.Models.Business.ServiceAddress serviceAddress, ref List<Ia.Ftn.Cl.Models.Ui.Access> accessList, out string note)
 1493:         {
 1494:             int numberOfTrials;
 1495:             Ia.Ftn.Cl.Models.Ui.Access statisticalAccess;
 1496:             List<Ia.Ftn.Cl.Models.Ui.Access> _accessList;
 1497:  
 1498:             numberOfTrials = 0;
 1499:  
 1500:             if (serviceAddress.AreaId != 0)
 1501:             {
 1502:                 numberOfTrials++;
 1503:  
 1504:                 _accessList = accessList.Where(u => u.AreaId == serviceAddress.AreaId).ToList();
 1505:  
 1506:                 if (!string.IsNullOrEmpty(serviceAddress.Block) && _accessList.Any(u => u.Block == serviceAddress.Block))
 1507:                 {
 1508:                     numberOfTrials++;
 1509:  
 1510:                     note = string.Empty;
 1511:  
 1512:                     _accessList = accessList.Where(u => u.AreaId == serviceAddress.AreaId && u.Block == serviceAddress.Block).ToList();
 1513:  
 1514:                     if (!string.IsNullOrEmpty(serviceAddress.Street))
 1515:                     {
 1516:                         numberOfTrials++;
 1517:  
 1518:                         statisticalAccess = StatisticalAccessBlockAndStreet(serviceAddress, serviceAddress.Street, ref accessList, ref note, ref numberOfTrials);
 1519:                     }
 1520:                     else
 1521:                     {
 1522:                         statisticalAccess = StatisticalAccessBlockAndNoStreet(serviceAddress, ref accessList, ref note, ref numberOfTrials);
 1523:                     }
 1524:                 }
 1525:                 else
 1526:                 {
 1527:                     numberOfTrials++;
 1528:  
 1529:                     statisticalAccess = null;
 1530:  
 1531:                     note = "serviceAddress.Block is empty or null";
 1532:                 }
 1533:             }
 1534:             else
 1535:             {
 1536:                 numberOfTrials++;
 1537:  
 1538:                 statisticalAccess = null;
 1539:  
 1540:                 note = "serviceAddress.AreaId is 0";
 1541:             }
 1542:  
 1543:             if (statisticalAccess != null) statisticalAccess.NumberOfTrials = numberOfTrials;
 1544:  
 1545:             return statisticalAccess;
 1546:         }
 1547:  
 1548:         //
 1549:         ////////////////////////////////////////////////////////////////////////////
 1550:  
 1551:         /// <summary>
 1552:         ///
 1553:         /// </summary>
 1554:         private static Ia.Ftn.Cl.Models.Ui.Access LevenshteinDistance(Ia.Ftn.Cl.Models.Business.ServiceAddress serviceAddress, ref List<Ia.Ftn.Cl.Models.Ui.Access> accessList)
 1555:         {
 1556:             string accessLevenshteinString, serviceAddressLevenshteinString, idOfKeyWithMinValue;
 1557:             Dictionary<string, int> dictionary;
 1558:             Ia.Ftn.Cl.Models.Ui.Access statisticalAccess;
 1559:  
 1560:             dictionary = new Dictionary<string, int>();
 1561:  
 1562:             serviceAddressLevenshteinString = serviceAddress.Block + ":" + serviceAddress.Street + ":" + serviceAddress.PremisesOld + ":" + serviceAddress.PremisesNew;
 1563:  
 1564:             foreach (var a in accessList)
 1565:             {
 1566:                 accessLevenshteinString = a.Block + ":" + a.Street + ":" + a.PremisesOld + ":" + a.PremisesNew;
 1567:  
 1568:                 dictionary[a.Id] = Ia.Cl.Models.Default.LevenshteinDistance(serviceAddressLevenshteinString, accessLevenshteinString);
 1569:             }
 1570:  
 1571:             var minValue = dictionary.Values.Min();
 1572:  
 1573:             idOfKeyWithMinValue = dictionary.FirstOrDefault(u => u.Value == minValue).Key;
 1574:  
 1575:             statisticalAccess = (from a in accessList where a.Id == idOfKeyWithMinValue select a).SingleOrDefault();
 1576:  
 1577:             return statisticalAccess;
 1578:         }
 1579:  
 1580:         ////////////////////////////////////////////////////////////////////////////
 1581:  
 1582:         /// <summary>
 1583:         ///
 1584:         /// </summary>
 1585:         private static Ia.Ftn.Cl.Models.Ui.Access StatisticalAccessBlockAndStreet(Ia.Ftn.Cl.Models.Business.ServiceAddress serviceAddress, string street, ref List<Ia.Ftn.Cl.Models.Ui.Access> accessList, ref string note, ref int numberOfTrials)
 1586:         {
 1587:             Ia.Ftn.Cl.Models.Ui.Access statisticalAccess;
 1588:             List<Ia.Ftn.Cl.Models.Ui.Access> _accessList;
 1589:  
 1590:             numberOfTrials++;
 1591:  
 1592:             _accessList = accessList.Where(u => u.AreaId == serviceAddress.AreaId && u.Block == serviceAddress.Block && u.Street == street).ToList();
 1593:  
 1594:             note = string.Empty;
 1595:  
 1596:             EvaluateAndCorrectPremisesOldAndPremisesNew(serviceAddress.PremisesOld, serviceAddress.PremisesNew, out string po, out string pn);
 1597:             serviceAddress.PremisesOld = po;
 1598:             serviceAddress.PremisesNew = pn;
 1599:  
 1600:             statisticalAccess = (from a in _accessList
 1601:                                  where a.AreaId == serviceAddress.AreaId
 1602:                                      && a.Block == serviceAddress.Block
 1603:                                      && a.Street == street
 1604:                                      && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
 1605:                                      && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
 1606:                                  select a).FirstOrDefault();
 1607:  
 1608:             if (statisticalAccess == null)
 1609:             {
 1610:                 numberOfTrials++;
 1611:  
 1612:                 statisticalAccess = (from a in _accessList
 1613:                                      where a.AreaId == serviceAddress.AreaId
 1614:                                          && a.Block == serviceAddress.Block
 1615:                                          && a.Street == street
 1616:                                          && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
 1617:                                      select a).FirstOrDefault();
 1618:  
 1619:                 if (statisticalAccess == null)
 1620:                 {
 1621:                     numberOfTrials++;
 1622:  
 1623:                     statisticalAccess = (from a in _accessList
 1624:                                          where a.AreaId == serviceAddress.AreaId
 1625:                                              && a.Block == serviceAddress.Block
 1626:                                              && a.Street == street
 1627:                                              && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
 1628:                                          select a).FirstOrDefault();
 1629:  
 1630:                     if (statisticalAccess == null)
 1631:                     {
 1632:                         numberOfTrials++;
 1633:  
 1634:                         statisticalAccess = (from a in _accessList
 1635:                                              where a.AreaId == serviceAddress.AreaId
 1636:                                                  && a.Block == serviceAddress.Block
 1637:                                                  && a.Street == street
 1638:                                                  && a.PremisesOld == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
 1639:                                              select a).FirstOrDefault();
 1640:  
 1641:                         if (statisticalAccess == null)
 1642:                         {
 1643:                             numberOfTrials++;
 1644:  
 1645:                             statisticalAccess = (from a in _accessList
 1646:                                                  where a.AreaId == serviceAddress.AreaId
 1647:                                                      && a.Block == serviceAddress.Block
 1648:                                                      && a.Street == street
 1649:                                                      && a.PremisesNew == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
 1650:                                                  select a).FirstOrDefault();
 1651:  
 1652:                             if (statisticalAccess == null)
 1653:                             {
 1654:                                 numberOfTrials++;
 1655:  
 1656:                                 statisticalAccess = (from a in _accessList
 1657:                                                      where a.AreaId == serviceAddress.AreaId
 1658:                                                          && a.Block == serviceAddress.Block
 1659:                                                          && a.Street == street
 1660:                                                          && a.PremisesNew == serviceAddress.Boulevard && !string.IsNullOrEmpty(serviceAddress.Boulevard)
 1661:                                                      select a).FirstOrDefault();
 1662:  
 1663:                                 if (statisticalAccess == null)
 1664:                                 {
 1665:                                     numberOfTrials++;
 1666:  
 1667:                                     statisticalAccess = (from a in _accessList
 1668:                                                          where a.AreaId == serviceAddress.AreaId
 1669:                                                              && a.Block == serviceAddress.Block
 1670:                                                              && a.Street == street
 1671:                                                              && a.PremisesOld == serviceAddress.Boulevard && !string.IsNullOrEmpty(serviceAddress.Boulevard)
 1672:                                                          select a).FirstOrDefault();
 1673:  
 1674:                                     if (statisticalAccess == null)
 1675:                                     {
 1676:                                         numberOfTrials++;
 1677:  
 1678:                                         statisticalAccess = (from a in _accessList
 1679:                                                              where a.AreaId == serviceAddress.AreaId
 1680:                                                                  && a.Block == serviceAddress.Block
 1681:                                                                  && a.Street == street
 1682:                                                                  && !string.IsNullOrEmpty(serviceAddress.PremisesOld) && !string.IsNullOrEmpty(a.PremisesOld) && a.PremisesOld.Contains(serviceAddress.PremisesOld)
 1683:                                                                  && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
 1684:                                                              select a).FirstOrDefault();
 1685:  
 1686:                                         if (statisticalAccess == null)
 1687:                                         {
 1688:                                             numberOfTrials++;
 1689:  
 1690:                                             statisticalAccess = (from a in _accessList
 1691:                                                                  where a.AreaId == serviceAddress.AreaId
 1692:                                                                      && a.Block == serviceAddress.Block
 1693:                                                                      && a.Street == street
 1694:                                                                      && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
 1695:                                                                      && !string.IsNullOrEmpty(serviceAddress.PremisesNew) && !string.IsNullOrEmpty(a.PremisesNew) && a.PremisesNew.Contains(serviceAddress.PremisesNew)
 1696:                                                                  select a).FirstOrDefault();
 1697:  
 1698:                                             if (statisticalAccess == null)
 1699:                                             {
 1700:                                                 numberOfTrials++;
 1701:  
 1702:                                                 statisticalAccess = (from a in _accessList
 1703:                                                                      where a.AreaId == serviceAddress.AreaId
 1704:                                                                          && a.Block == serviceAddress.Block
 1705:                                                                          && a.Street == street
 1706:                                                                          && !string.IsNullOrEmpty(serviceAddress.PremisesOld) && !string.IsNullOrEmpty(a.PremisesOld) && a.PremisesOld.Contains(serviceAddress.PremisesOld)
 1707:                                                                      select a).FirstOrDefault();
 1708:  
 1709:                                                 if (statisticalAccess == null)
 1710:                                                 {
 1711:                                                     numberOfTrials++;
 1712:  
 1713:                                                     statisticalAccess = (from a in _accessList
 1714:                                                                          where a.AreaId == serviceAddress.AreaId
 1715:                                                                              && a.Block == serviceAddress.Block
 1716:                                                                              && a.Street == street
 1717:                                                                              && !string.IsNullOrEmpty(serviceAddress.PremisesNew) && !string.IsNullOrEmpty(a.PremisesNew) && a.PremisesNew.Contains(serviceAddress.PremisesNew)
 1718:                                                                          select a).FirstOrDefault();
 1719:  
 1720:                                                     if (statisticalAccess == null)
 1721:                                                     {
 1722:                                                         numberOfTrials++;
 1723:  
 1724:  
 1725:                                                     }
 1726:                                                 }
 1727:                                             }
 1728:                                         }
 1729:                                     }
 1730:                                 }
 1731:                             }
 1732:                         }
 1733:                     }
 1734:                 }
 1735:             }
 1736:  
 1737:             return statisticalAccess;
 1738:         }
 1739:  
 1740:         ////////////////////////////////////////////////////////////////////////////
 1741:  
 1742:         /// <summary>
 1743:         ///
 1744:         /// </summary>
 1745:         private static Ia.Ftn.Cl.Models.Ui.Access StatisticalAccessBlockAndNoStreet(Ia.Ftn.Cl.Models.Business.ServiceAddress serviceAddress, ref List<Ia.Ftn.Cl.Models.Ui.Access> accessList, ref string note, ref int numberOfTrials)
 1746:         {
 1747:             Ia.Ftn.Cl.Models.Ui.Access statisticalAccess;
 1748:             List<Ia.Ftn.Cl.Models.Ui.Access> _accessList;
 1749:  
 1750:             numberOfTrials++;
 1751:  
 1752:             _accessList = accessList.Where(u => u.AreaId == serviceAddress.AreaId && u.Block == serviceAddress.Block).ToList();
 1753:  
 1754:             note = string.Empty;
 1755:  
 1756:             EvaluateAndCorrectPremisesOldAndPremisesNew(serviceAddress.PremisesOld, serviceAddress.PremisesNew, out string po, out string pn);
 1757:             serviceAddress.PremisesOld = po;
 1758:             serviceAddress.PremisesNew = pn;
 1759:  
 1760:             statisticalAccess = (from a in _accessList
 1761:                                  where a.AreaId == serviceAddress.AreaId
 1762:                                      && a.Block == serviceAddress.Block
 1763:                                      && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
 1764:                                      && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
 1765:                                  select a).FirstOrDefault();
 1766:  
 1767:             if (statisticalAccess == null)
 1768:             {
 1769:                 statisticalAccess = (from a in _accessList
 1770:                                      where a.AreaId == serviceAddress.AreaId
 1771:                                          && a.Block == serviceAddress.Block
 1772:                                          && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
 1773:                                      select a).FirstOrDefault();
 1774:  
 1775:                 if (statisticalAccess == null)
 1776:                 {
 1777:                     numberOfTrials++;
 1778:  
 1779:                     statisticalAccess = (from a in _accessList
 1780:                                          where a.AreaId == serviceAddress.AreaId
 1781:                                              && a.Block == serviceAddress.Block
 1782:                                              && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
 1783:                                          select a).FirstOrDefault();
 1784:  
 1785:                     if (statisticalAccess == null)
 1786:                     {
 1787:                         numberOfTrials++;
 1788:  
 1789:                         statisticalAccess = (from a in _accessList
 1790:                                              where a.AreaId == serviceAddress.AreaId
 1791:                                                  && a.Block == serviceAddress.Block
 1792:                                                  && a.PremisesOld == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
 1793:                                              select a).FirstOrDefault();
 1794:  
 1795:                         if (statisticalAccess == null)
 1796:                         {
 1797:                             numberOfTrials++;
 1798:  
 1799:                             statisticalAccess = (from a in _accessList
 1800:                                                  where a.AreaId == serviceAddress.AreaId
 1801:                                                      && a.Block == serviceAddress.Block
 1802:                                                      && a.PremisesNew == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
 1803:                                                  select a).FirstOrDefault();
 1804:  
 1805:                             if (statisticalAccess == null)
 1806:                             {
 1807:                                 numberOfTrials++;
 1808:  
 1809:                                 statisticalAccess = (from a in _accessList
 1810:                                                      where a.AreaId == serviceAddress.AreaId
 1811:                                                          && a.Block == serviceAddress.Block
 1812:                                                          && !string.IsNullOrEmpty(serviceAddress.PremisesOld) && !string.IsNullOrEmpty(a.PremisesOld) && a.PremisesOld.Contains(serviceAddress.PremisesOld)
 1813:                                                          && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
 1814:                                                      select a).FirstOrDefault();
 1815:  
 1816:                                 if (statisticalAccess == null)
 1817:                                 {
 1818:                                     numberOfTrials++;
 1819:  
 1820:                                     statisticalAccess = (from a in _accessList
 1821:                                                          where a.AreaId == serviceAddress.AreaId
 1822:                                                              && a.Block == serviceAddress.Block
 1823:                                                              && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
 1824:                                                              && !string.IsNullOrEmpty(serviceAddress.PremisesNew) && !string.IsNullOrEmpty(a.PremisesNew) && a.PremisesNew.Contains(serviceAddress.PremisesNew)
 1825:                                                          select a).FirstOrDefault();
 1826:  
 1827:                                     if (statisticalAccess == null)
 1828:                                     {
 1829:                                         numberOfTrials++;
 1830:  
 1831:                                         statisticalAccess = (from a in _accessList
 1832:                                                              where a.AreaId == serviceAddress.AreaId
 1833:                                                                  && a.Block == serviceAddress.Block
 1834:                                                                  && !string.IsNullOrEmpty(serviceAddress.PremisesOld) && !string.IsNullOrEmpty(a.PremisesOld) && a.PremisesOld.Contains(serviceAddress.PremisesOld)
 1835:                                                              select a).FirstOrDefault();
 1836:  
 1837:                                         if (statisticalAccess == null)
 1838:                                         {
 1839:                                             numberOfTrials++;
 1840:  
 1841:                                             statisticalAccess = (from a in _accessList
 1842:                                                                  where a.AreaId == serviceAddress.AreaId
 1843:                                                                      && a.Block == serviceAddress.Block
 1844:                                                                      && !string.IsNullOrEmpty(serviceAddress.PremisesNew) && !string.IsNullOrEmpty(a.PremisesNew) && a.PremisesNew.Contains(serviceAddress.PremisesNew)
 1845:                                                                  select a).FirstOrDefault();
 1846:  
 1847:                                             if (statisticalAccess == null)
 1848:                                             {
 1849:                                                 numberOfTrials++;
 1850:  
 1851:                                                 statisticalAccess = null;
 1852:  
 1853:                                                 note = "serviceAddress.Street is empty or null";
 1854:                                             }
 1855:                                         }
 1856:                                     }
 1857:                                 }
 1858:                             }
 1859:                         }
 1860:                     }
 1861:                 }
 1862:             }
 1863:  
 1864:             return statisticalAccess;
 1865:         }
 1866:  
 1867:         ////////////////////////////////////////////////////////////////////////////
 1868:  
 1869:         /// <summary>
 1870:         ///
 1871:         /// </summary>
 1872:         public static void EvaluateAndCorrectPremisesOldAndPremisesNew(string premisesOld, string premisesNew, out string newPremisesOld, out string newPremisesNew)
 1873:         {
 1874:             if (!string.IsNullOrEmpty(premisesOld) && !string.IsNullOrEmpty(premisesNew))
 1875:             {
 1876:                 if (int.TryParse(premisesOld, out int po) && int.TryParse(premisesNew, out int pn))
 1877:                 {
 1878:                     // if equal then one is invalid
 1879:                     if (po == pn)
 1880:                     {
 1881:                         if (po > 100)
 1882:                         {
 1883:                             newPremisesOld = po.ToString();
 1884:                             newPremisesNew = string.Empty;
 1885:                         }
 1886:                         else
 1887:                         {
 1888:                             newPremisesOld = string.Empty;
 1889:                             newPremisesNew = po.ToString();
 1890:                         }
 1891:                     }
 1892:                     else
 1893:                     {
 1894:                         if (pn > po)
 1895:                         {
 1896:                             newPremisesOld = pn.ToString();
 1897:                             newPremisesNew = po.ToString();
 1898:                         }
 1899:                         else
 1900:                         {
 1901:                             newPremisesOld = po.ToString();
 1902:                             newPremisesNew = pn.ToString();
 1903:                         }
 1904:                     }
 1905:                 }
 1906:                 else if (int.TryParse(premisesOld, out po) && !int.TryParse(premisesNew, out _))
 1907:                 {
 1908:                     // if equal then one is invalid
 1909:                     if (po.ToString() == premisesNew)
 1910:                     {
 1911:                         if (po > 100)
 1912:                         {
 1913:                             newPremisesOld = po.ToString();
 1914:                             newPremisesNew = string.Empty;
 1915:                         }
 1916:                         else
 1917:                         {
 1918:                             newPremisesOld = string.Empty;
 1919:                             newPremisesNew = po.ToString();
 1920:                         }
 1921:                     }
 1922:                     else
 1923:                     {
 1924:                         if (premisesNew.Length > po.ToString().Length)
 1925:                         {
 1926:                             newPremisesOld = premisesNew;
 1927:                             newPremisesNew = po.ToString();
 1928:                         }
 1929:                         else
 1930:                         {
 1931:                             newPremisesOld = po.ToString();
 1932:                             newPremisesNew = premisesNew;
 1933:                         }
 1934:                     }
 1935:                 }
 1936:                 else if (!int.TryParse(premisesOld, out po) && int.TryParse(premisesNew, out pn))
 1937:                 {
 1938:                     // if equal then one is invalid
 1939:                     if (premisesOld == pn.ToString())
 1940:                     {
 1941:                         if (premisesOld.Length >= 3)
 1942:                         {
 1943:                             newPremisesOld = premisesOld.ToString();
 1944:                             newPremisesNew = string.Empty;
 1945:                         }
 1946:                         else
 1947:                         {
 1948:                             newPremisesOld = string.Empty;
 1949:                             newPremisesNew = premisesOld;
 1950:                         }
 1951:                     }
 1952:                     else
 1953:                     {
 1954:                         if (pn.ToString().Length > premisesOld.Length)
 1955:                         {
 1956:                             newPremisesOld = pn.ToString();
 1957:                             newPremisesNew = premisesOld;
 1958:                         }
 1959:                         else
 1960:                         {
 1961:                             newPremisesOld = premisesOld;
 1962:                             newPremisesNew = pn.ToString();
 1963:                         }
 1964:                     }
 1965:                 }
 1966:                 else //if (!int.TryParse(premisesOld, out po) && !int.TryParse(premisesNew, out pn))
 1967:                 {
 1968:                     // if equal then one is invalid
 1969:                     if (premisesOld == premisesNew)
 1970:                     {
 1971:                         if (premisesOld.Length >= 3)
 1972:                         {
 1973:                             newPremisesOld = premisesOld;
 1974:                             newPremisesNew = string.Empty;
 1975:                         }
 1976:                         else
 1977:                         {
 1978:                             newPremisesOld = string.Empty;
 1979:                             newPremisesNew = premisesOld;
 1980:                         }
 1981:                     }
 1982:                     else
 1983:                     {
 1984:                         if (premisesNew.Length > premisesOld.Length)
 1985:                         {
 1986:                             newPremisesOld = premisesNew;
 1987:                             newPremisesNew = premisesOld;
 1988:                         }
 1989:                         else
 1990:                         {
 1991:                             newPremisesOld = premisesOld;
 1992:                             newPremisesNew = premisesNew;
 1993:                         }
 1994:                     }
 1995:                 }
 1996:             }
 1997:             else if (!string.IsNullOrEmpty(premisesOld) && string.IsNullOrEmpty(premisesNew))
 1998:             {
 1999:                 if (premisesOld.Length >= 3)
 2000:                 {
 2001:                     newPremisesOld = premisesOld;
 2002:                     newPremisesNew = string.Empty;
 2003:                 }
 2004:                 else
 2005:                 {
 2006:                     newPremisesOld = string.Empty;
 2007:                     newPremisesNew = premisesOld;
 2008:                 }
 2009:             }
 2010:             else if (string.IsNullOrEmpty(premisesOld) && !string.IsNullOrEmpty(premisesNew))
 2011:             {
 2012:                 if (premisesNew.Length < 3)
 2013:                 {
 2014:                     newPremisesOld = string.Empty;
 2015:                     newPremisesNew = premisesNew;
 2016:                 }
 2017:                 else
 2018:                 {
 2019:                     newPremisesOld = premisesNew;
 2020:                     newPremisesNew = string.Empty;
 2021:                 }
 2022:             }
 2023:             else //if (string.IsNullOrEmpty(premisesOld) && string.IsNullOrEmpty(premisesNew))
 2024:             {
 2025:                 newPremisesOld = string.Empty;
 2026:                 newPremisesNew = string.Empty;
 2027:             }
 2028:         }
 2029:  
 2030:         ////////////////////////////////////////////////////////////////////////////
 2031:         ////////////////////////////////////////////////////////////////////////////
 2032:  
 2033:         /// <summary>
 2034:         ///
 2035:         /// </summary>
 2036:         public static List<Ia.Ftn.Cl.Models.Access> AccessesWithNoServiceRequestOntsAndUpdatedWithinLastNDaysRandomNList(int pastDays, int take)
 2037:         {
 2038:             List<Ia.Ftn.Cl.Models.Access> list;
 2039:  
 2040:             var pastDateTime = DateTime.UtcNow.AddHours(3).AddDays(-pastDays);
 2041:  
 2042:             using (var db = new Ia.Ftn.Cl.Db())
 2043:             {
 2044:                 list = (from a in db.Accesses
 2045:                         join sro in db.ServiceRequestOnts on a.Id equals sro.Access.Id
 2046:                         into pg
 2047:                         from p in pg.DefaultIfEmpty()
 2048:                         where p == null && a.Updated >= pastDateTime
 2049:                         select a).AsNoTracking().ToList();
 2050:             }
 2051:  
 2052:             return list.PickRandom(take).ToList();
 2053:         }
 2054:  
 2055:         ////////////////////////////////////////////////////////////////////////////
 2056:  
 2057:         /// <summary>
 2058:         ///
 2059:         /// </summary>
 2060:         public static List<Ia.Ftn.Cl.Models.Access> AccessesWithNoServiceRequestOntsAndUpdatedTakeN(int take)
 2061:         {
 2062:             List<Ia.Ftn.Cl.Models.Access> list;
 2063:  
 2064:             using (var db = new Ia.Ftn.Cl.Db())
 2065:             {
 2066:                 list = (from a in db.Accesses
 2067:                         join sro in db.ServiceRequestOnts on a.Id equals sro.Access.Id
 2068:                         into pg
 2069:                         from p in pg.DefaultIfEmpty()
 2070:                         where p == null
 2071:                         select a).OrderByDescending(u => u.Updated).Take(take).ToList();
 2072:             }
 2073:  
 2074:             return list;
 2075:         }
 2076:  
 2077:         ////////////////////////////////////////////////////////////////////////////
 2078:  
 2079:         /// <summary>
 2080:         ///
 2081:         /// </summary>
 2082:         public static List<Ia.Ftn.Cl.Models.Access> AccessesWithNoServiceRequestOntsRandomNList(int take)
 2083:         {
 2084:             List<Ia.Ftn.Cl.Models.Access> list;
 2085:  
 2086:             using (var db = new Ia.Ftn.Cl.Db())
 2087:             {
 2088:                 list = (from a in db.Accesses
 2089:                         join sro in db.ServiceRequestOnts on a.Id equals sro.Access.Id
 2090:                         into pg
 2091:                         from p in pg.DefaultIfEmpty()
 2092:                         where p == null
 2093:                         select a).AsNoTracking().ToList();
 2094:             }
 2095:  
 2096:             return list.PickRandom(take).ToList();
 2097:         }
 2098:  
 2099:         ////////////////////////////////////////////////////////////////////////////
 2100:         ////////////////////////////////////////////////////////////////////////////
 2101:  
 2102:         /// <summary>
 2103:         ///
 2104:         /// </summary>
 2105:         public static string ToSimpleTextString(Ia.Ftn.Cl.Models.Access access)
 2106:         {
 2107:             StringBuilder sb;
 2108:             Ia.Ftn.Cl.Models.Business.NetworkDesignDocument.Vendor vendor;
 2109:  
 2110:             sb = new StringBuilder();
 2111:  
 2112:             vendor = Ia.Ftn.Cl.Models.Data.NetworkDesignDocument.AccessVendorByOntAccessId(access.Id);
 2113:             //vendor = (from o in Ia.Ftn.Cl.Model.Data.NetworkDesignDocument.OntList where o.Access.Id == access.Id select o.Pon.PonGroup.Olt.Odf.Vendor).SingleOrDefault();
 2114:  
 2115:             sb.AppendLine("Vendor: " + vendor.Name);
 2116:             sb.AppendLine("Name: " + access.Name);
 2117:             sb.AppendLine("Address: " + access.Address);
 2118:  
 2119:             if (!string.IsNullOrEmpty(access.Paci)) sb.AppendLine("Paci: " + access.Paci);
 2120:             if (!string.IsNullOrEmpty(access.Note)) sb.AppendLine("Note: " + access.Note);
 2121:  
 2122:             return sb.ToString();
 2123:         }
 2124:  
 2125:         ////////////////////////////////////////////////////////////////////////////
 2126:         ////////////////////////////////////////////////////////////////////////////
 2127:     }
 2128:  
 2129:     ////////////////////////////////////////////////////////////////////////////
 2130:     ////////////////////////////////////////////////////////////////////////////
 2131: }