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