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

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

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

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