شركة التطبيقات المتكاملة لتصميم النظم البرمجية الخاصة ش.ش.و.

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » Access

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

Access support class for Optical Fiber Network (OFN) 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.Ngn.Cl.Model.Data
   8:  {
   9:      ////////////////////////////////////////////////////////////////////////////
  10:   
  11:      /// <summary publish="true">
  12:      /// Access support class for Optical Fiber Network (OFN) 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.Ngn.Cl.Model.Access newAccess, out Ia.Cl.Model.Result result)
  43:          {
  44:              Ia.Ngn.Cl.Model.Access access;
  45:   
  46:              result = new Ia.Cl.Model.Result();
  47:   
  48:              try
  49:              {
  50:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
  51:                  {
  52:                      // check if it exists already
  53:                      access = (from a in db.Accesses where a.Id == newAccess.Id select a).SingleOrDefault();
  54:   
  55:                      if (access == null)
  56:                      {
  57:                          newAccess.Created = newAccess.Updated = DateTime.UtcNow.AddHours(3);
  58:   
  59:                          db.Accesses.Add(newAccess);
  60:                          db.SaveChanges();
  61:   
  62:                          result.AddSuccess("Access record " + newAccess.Name + " created. ");
  63:                      }
  64:                      else
  65:                      {
  66:                          result.AddError("Access record " + newAccess.Name + " already exists. ");
  67:                      }
  68:                  }
  69:              }
  70:              catch (Exception ex)
  71:              {
  72:                  result.AddError("Exception: Access record was not created: " + ex.Message);
  73:              }
  74:          }
  75:   
  76:          ////////////////////////////////////////////////////////////////////////////
  77:   
  78:          /// <summary>
  79:          ///
  80:          /// </summary>
  81:          public static Ia.Ngn.Cl.Model.Access Read(Ia.Ngn.Cl.Model.Ngn db, string id)
  82:          {
  83:              Ia.Ngn.Cl.Model.Access access;
  84:   
  85:              access = (from a in db.Accesses where a.Id == id select a).SingleOrDefault();
  86:   
  87:              return access;
  88:          }
  89:   
  90:          ////////////////////////////////////////////////////////////////////////////
  91:   
  92:          /// <summary>
  93:          ///
  94:          /// </summary>
  95:          public static Ia.Ngn.Cl.Model.Access Read(string id)
  96:          {
  97:              Ia.Ngn.Cl.Model.Access item;
  98:   
  99:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 100:              {
 101:                  item = (from a in db.Accesses where a.Id == id select a).SingleOrDefault();
 102:              }
 103:   
 104:              return item;
 105:          }
 106:   
 107:          ////////////////////////////////////////////////////////////////////////////
 108:   
 109:          /// <summary>
 110:          ///
 111:          /// </summary>
 112:          public static Ia.Ngn.Cl.Model.Access Read(int oltId, int ponNumber, int ontNumber)
 113:          {
 114:              Ia.Ngn.Cl.Model.Access item;
 115:   
 116:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 117:              {
 118:                  item = (from a in db.Accesses where a.Olt == oltId && a.Pon == ponNumber && a.Ont == ontNumber select a).SingleOrDefault();
 119:              }
 120:   
 121:              return item;
 122:          }
 123:   
 124:          ////////////////////////////////////////////////////////////////////////////
 125:   
 126:          /// <summary>
 127:          ///
 128:          /// </summary>
 129:          public static Ia.Ngn.Cl.Model.Access Read(Ia.Ngn.Cl.Model.Ngn db, int oltId, int ponNumber, int ontNumber)
 130:          {
 131:              return (from a in db.Accesses where a.Olt == oltId && a.Pon == ponNumber && a.Ont == ontNumber select a).SingleOrDefault();
 132:          }
 133:   
 134:          ////////////////////////////////////////////////////////////////////////////
 135:   
 136:          /// <summary>
 137:          ///
 138:          /// </summary>
 139:          public static Ia.Ngn.Cl.Model.Access ReadBySerialInNote(string serial)
 140:          {
 141:              List<Ia.Ngn.Cl.Model.Access> list;
 142:   
 143:              if (!string.IsNullOrEmpty(serial))
 144:              {
 145:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 146:                  {
 147:                      list = (from a in db.Accesses where a.Note.Contains(serial) select a).ToList();
 148:                  }
 149:              }
 150:              else list = new List<Ia.Ngn.Cl.Model.Access>();
 151:   
 152:              return list.FirstOrDefault();
 153:          }
 154:   
 155:          ////////////////////////////////////////////////////////////////////////////
 156:   
 157:          /// <summary>
 158:          ///
 159:          /// </summary>
 160:          public static List<Ia.Ngn.Cl.Model.Access> ReadListByPaci(string paci)
 161:          {
 162:              List<Ia.Ngn.Cl.Model.Access> list;
 163:   
 164:              if (!string.IsNullOrEmpty(paci))
 165:              {
 166:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 167:                  {
 168:                      list = (from a in db.Accesses where a.Paci == paci select a).AsNoTracking().ToList();
 169:                  }
 170:              }
 171:              else list = new List<Ia.Ngn.Cl.Model.Access>();
 172:   
 173:              return list;
 174:          }
 175:   
 176:          ////////////////////////////////////////////////////////////////////////////
 177:   
 178:          /// <summary>
 179:          ///
 180:          /// </summary>
 181:          public static bool PaciExists(string paci)
 182:          {
 183:              bool paciExists;
 184:   
 185:              if (!string.IsNullOrEmpty(paci))
 186:              {
 187:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 188:                  {
 189:                      paciExists = (from a in db.Accesses where a.Paci == paci select a).Any();
 190:                  }
 191:              }
 192:              else paciExists = false;
 193:   
 194:              return paciExists;
 195:          }
 196:   
 197:          ////////////////////////////////////////////////////////////////////////////
 198:   
 199:          /// <summary>
 200:          ///
 201:          /// </summary>
 202:          public static List<Ia.Ngn.Cl.Model.Access> ReadListByAreaId(int areaId)
 203:          {
 204:              List<Ia.Ngn.Cl.Model.Access> itemList;
 205:   
 206:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 207:              {
 208:                  itemList = (from a in db.Accesses where a.AreaId == areaId select a).ToList();
 209:              }
 210:   
 211:              return itemList;
 212:          }
 213:   
 214:          ////////////////////////////////////////////////////////////////////////////
 215:   
 216:          /// <summary>
 217:          ///
 218:          /// </summary>
 219:          public static int AreaIdFromOltIdAndPon(int oltId, int ponNumber)
 220:          {
 221:              int areaId;
 222:   
 223:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 224:              {
 225:                  areaId = (from a in db.Accesses where a.Olt == oltId && a.Pon == ponNumber select a.AreaId).FirstOrDefault();
 226:   
 227:                  if (areaId == 0)
 228:                  {
 229:                      areaId = (from a in db.Accesses where a.Olt == oltId select a.AreaId).FirstOrDefault();
 230:                  }
 231:              }
 232:   
 233:              return areaId;
 234:          }
 235:   
 236:          ////////////////////////////////////////////////////////////////////////////
 237:   
 238:          /// <summary>
 239:          ///
 240:          /// </summary>
 241:          public static List<Ia.Ngn.Cl.Model.Access> List()
 242:          {
 243:              List<Ia.Ngn.Cl.Model.Access> list;
 244:   
 245:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 246:              {
 247:                  list = (from a in db.Accesses select a).AsNoTracking().ToList();
 248:              }
 249:   
 250:              return list;
 251:          }
 252:   
 253:          ////////////////////////////////////////////////////////////////////////////
 254:   
 255:          /// <summary>
 256:          ///
 257:          /// </summary>
 258:          public static List<Ia.Ngn.Cl.Model.Access> ListWithOntsAndEmsOnts()
 259:          {
 260:              List<Ia.Ngn.Cl.Model.Access> list;
 261:   
 262:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 263:              {
 264:                  list = (from a in db.Accesses select a).Include(u => u.Onts).Include(u => u.EmsOnts).ToList();
 265:              }
 266:   
 267:              return list;
 268:          }
 269:   
 270:          ////////////////////////////////////////////////////////////////////////////
 271:   
 272:          /// <summary>
 273:          ///
 274:          /// </summary>
 275:          public static List<Ia.Ngn.Cl.Model.Access> List(Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Olt olt)
 276:          {
 277:              List<Ia.Ngn.Cl.Model.Access> list;
 278:   
 279:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 280:              {
 281:                  list = (from a in db.Accesses where a.Olt == olt.Id select a).ToList();
 282:              }
 283:   
 284:              return list;
 285:          }
 286:   
 287:          ////////////////////////////////////////////////////////////////////////////
 288:   
 289:          /// <summary>
 290:          ///
 291:          /// </summary>
 292:          public static List<Ia.Ngn.Cl.Model.Access> List(int oltId, int ponNumber)
 293:          {
 294:              List<Ia.Ngn.Cl.Model.Access> itemList;
 295:   
 296:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 297:              {
 298:                  itemList = (from a in db.Accesses where a.Olt == oltId && a.Pon == ponNumber select a).ToList();
 299:              }
 300:   
 301:              return itemList;
 302:          }
 303:   
 304:          ////////////////////////////////////////////////////////////////////////////
 305:   
 306:          /// <summary>
 307:          ///
 308:          /// </summary>
 309:          public static bool Update(Ia.Ngn.Cl.Model.Access updatedAccess, out string result)
 310:          {
 311:              bool b;
 312:              Ia.Ngn.Cl.Model.Access access;
 313:   
 314:              b = false;
 315:              result = string.Empty;
 316:   
 317:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 318:              {
 319:                  access = (from a in db.Accesses where a.Id == updatedAccess.Id select a).SingleOrDefault();
 320:   
 321:                  if (access.Update(updatedAccess))
 322:                  {
 323:                      db.Accesses.Attach(access);
 324:                      db.Entry(access).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
 325:                  }
 326:   
 327:                  db.SaveChanges();
 328:   
 329:                  b = true;
 330:              }
 331:   
 332:              return b;
 333:          }
 334:   
 335:          ////////////////////////////////////////////////////////////////////////////
 336:   
 337:          /// <summary>
 338:          ///
 339:          /// </summary>
 340:          public static void Delete(string id, out Ia.Cl.Model.Result result)
 341:          {
 342:              result = new Ia.Cl.Model.Result();
 343:   
 344:              try
 345:              {
 346:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 347:                  {
 348:                      var v = (from a in db.Accesses where a.Id == id select a).FirstOrDefault();
 349:   
 350:                      if (v != null)
 351:                      {
 352:                          db.Accesses.Remove(v);
 353:                          db.SaveChanges();
 354:   
 355:                          result.AddSuccess("Access record deleted. ");
 356:                      }
 357:                      else
 358:                      {
 359:                          result.AddWarning("Access record does not exist. ");
 360:                      }
 361:   
 362:                  }
 363:              }
 364:              catch (Exception ex)
 365:              {
 366:                  result.AddError("Exception: Access record was not deleted: " + ex.Message);
 367:              }
 368:          }
 369:   
 370:          ////////////////////////////////////////////////////////////////////////////
 371:          ////////////////////////////////////////////////////////////////////////////
 372:   
 373:          /// <summary>
 374:          ///
 375:          /// </summary>
 376:          public static List<int> OntsNotInAccessList(List<Ia.Ngn.Cl.Model.Access> accessList)
 377:          {
 378:              // below: produce a list of ONTs between 1 and 32 that are not in ontList
 379:              List<int> ontNotInListArrayList = new List<int>(32);
 380:   
 381:              for (int i = 1; i <= 32; i++)
 382:              {
 383:                  if (accessList != null)
 384:                  {
 385:                      if ((from a in accessList where a.Ont == i select a).SingleOrDefault() == null)
 386:                      {
 387:                          ontNotInListArrayList.Add(i);
 388:                      }
 389:                      else
 390:                      {
 391:                      }
 392:                  }
 393:                  else
 394:                  {
 395:                      ontNotInListArrayList.Add(i);
 396:                  }
 397:              }
 398:   
 399:              return ontNotInListArrayList;
 400:          }
 401:   
 402:          ////////////////////////////////////////////////////////////////////////////
 403:   
 404:          /// <summary>
 405:          ///
 406:          /// </summary>
 407:          public static List<int> OltIdDistinctList
 408:          {
 409:              get
 410:              {
 411:                  List<int> list;
 412:   
 413:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 414:                  {
 415:                      list = (from o in db.Accesses select o.Olt).Distinct().OrderBy(u => u).ToList();
 416:                  }
 417:   
 418:                  return list;
 419:              }
 420:          }
 421:   
 422:          ////////////////////////////////////////////////////////////////////////////
 423:   
 424:          /// <summary>
 425:          ///
 426:          /// </summary>
 427:          public static Ia.Ngn.Cl.Model.Access ReadByAccessName(string accessName)
 428:          {
 429:              Ia.Ngn.Cl.Model.Access access;
 430:   
 431:              // below: this expects accessName in exact format like SUR.12.3
 432:   
 433:              Ia.Ngn.Cl.Model.Business.Access.ExtractOltIdAndPonNumberAndOntNumberFromOntName(accessName, out int oltId, out int pon, out int ont);
 434:   
 435:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 436:              {
 437:                  access = (from a in db.Accesses where a.Olt == oltId && a.Pon == pon && a.Ont == ont select a).SingleOrDefault();
 438:              }
 439:   
 440:              return access;
 441:          }
 442:   
 443:          ////////////////////////////////////////////////////////////////////////////
 444:   
 445:          /// <summary>
 446:          ///
 447:          /// </summary>
 448:          public static List<Ia.Ngn.Cl.Model.Access> MduList()
 449:          {
 450:              List<Ia.Ngn.Cl.Model.Access> nokiaAccessList, huaweiAccessList, list;
 451:   
 452:              list = new List<Ia.Ngn.Cl.Model.Access>();
 453:   
 454:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 455:              {
 456:                  nokiaAccessList = (from a in db.Accesses
 457:                                     join o in db.Onts on a equals o.Access
 458:                                     where o.FamilyTypeId == (int)Ia.Ngn.Cl.Model.Business.Nokia.Ont.FamilyType.Mdu
 459:                                     || o.FamilyTypeId == (int)Ia.Ngn.Cl.Model.Business.Nokia.Ont.FamilyType.Gmdu
 460:                                     || o.FamilyTypeId == (int)Ia.Ngn.Cl.Model.Business.Nokia.Ont.FamilyType.Sbu
 461:                                     || o.FamilyTypeId == (int)Ia.Ngn.Cl.Model.Business.Nokia.Ont.FamilyType.Soho
 462:                                     select a).ToList();
 463:   
 464:                  var systemNameListList = (from e in Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentTypeList
 465:                                            where e.FamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu
 466:                                            || e.FamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Soho
 467:                                            select e.SystemNameList).ToList();
 468:   
 469:                  var equipmentTypeStringList = new List<string>();
 470:   
 471:                  foreach (var systemNameList in systemNameListList)
 472:                  {
 473:                      foreach (string systemName in systemNameList)
 474:                      {
 475:                          equipmentTypeStringList.Add(systemName);
 476:                      }
 477:                  }
 478:   
 479:                  equipmentTypeStringList = equipmentTypeStringList.Distinct().ToList();
 480:   
 481:                  huaweiAccessList = (from a in db.Accesses
 482:                                      join eo in db.EmsOnts on a equals eo.Access
 483:                                      where equipmentTypeStringList.Contains(eo.EQUIPMENTID)
 484:                                      select a).ToList();
 485:              }
 486:   
 487:              list.AddRange(nokiaAccessList);
 488:              list.AddRange(huaweiAccessList);
 489:   
 490:              return list;
 491:          }
 492:   
 493:          ////////////////////////////////////////////////////////////////////////////
 494:   
 495:          /// <summary>
 496:          ///
 497:          /// </summary>
 498:          public static List<string> ReadAccessNameListByKuwaitNgnAreaIdAndBlock(int kuwaitAreaId, string block)
 499:          {
 500:              Dictionary<string, string> ontAccessIdToOntAccessNameDictionary;
 501:              List<string> accessIdList, accessNameList;
 502:   
 503:              if (kuwaitAreaId > 0 && !string.IsNullOrEmpty(block))
 504:              {
 505:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 506:                  {
 507:                      accessIdList = (from a in db.Accesses where a.AreaId == kuwaitAreaId && a.Block == block select a.Id).ToList();
 508:                      ontAccessIdToOntAccessNameDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
 509:   
 510:                      if (accessIdList != null && accessIdList.Count > 0)
 511:                      {
 512:                          accessNameList = new List<string>(accessIdList.Count);
 513:   
 514:                          foreach (string accessId in accessIdList)
 515:                          {
 516:                              if (ontAccessIdToOntAccessNameDictionary.ContainsKey(accessId))
 517:                              {
 518:                                  accessNameList.Add(ontAccessIdToOntAccessNameDictionary[accessId]);
 519:                              }
 520:                          }
 521:                      }
 522:                      else
 523:                      {
 524:                          accessNameList = new List<string>();
 525:                      }
 526:                  }
 527:              }
 528:              else
 529:              {
 530:                  accessNameList = new List<string>();
 531:              }
 532:   
 533:              return accessNameList;
 534:          }
 535:   
 536:          ////////////////////////////////////////////////////////////////////////////
 537:   
 538:          /// <summary>
 539:          ///
 540:          /// </summary>
 541:          public static List<Ia.Ngn.Cl.Model.Access> ListByKuwaitNgnAreaId(int kuwaitAreaId)
 542:          {
 543:              List<Ia.Ngn.Cl.Model.Access> list;
 544:   
 545:              if (kuwaitAreaId > 0)
 546:              {
 547:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 548:                  {
 549:                      list = (from a in db.Accesses where a.AreaId == kuwaitAreaId select a).ToList();
 550:                  }
 551:              }
 552:              else
 553:              {
 554:                  list = new List<Ia.Ngn.Cl.Model.Access>();
 555:              }
 556:   
 557:              return list;
 558:          }
 559:   
 560:          ////////////////////////////////////////////////////////////////////////////
 561:   
 562:          /// <summary>
 563:          ///
 564:          /// </summary>
 565:          public static List<string> NameListByKuwaitNgnAreaId(int kuwaitAreaId)
 566:          {
 567:              List<string> list;
 568:   
 569:              if (kuwaitAreaId > 0)
 570:              {
 571:                  var ontAccessIdToOntAccessNameDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
 572:   
 573:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 574:                  {
 575:                      var accessIdList = (from a in db.Accesses
 576:                                          where a.AreaId == kuwaitAreaId
 577:                                          select a.Id).ToList();
 578:   
 579:                      list = new List<string>(accessIdList.Count);
 580:   
 581:                      foreach (string accessId in accessIdList)
 582:                      {
 583:                          if (ontAccessIdToOntAccessNameDictionary.ContainsKey(accessId))
 584:                          {
 585:                              list.Add(ontAccessIdToOntAccessNameDictionary[accessId]);
 586:                          }
 587:                      }
 588:                  }
 589:              }
 590:              else list = new List<string>();
 591:   
 592:              return list;
 593:          }
 594:   
 595:          ////////////////////////////////////////////////////////////////////////////
 596:   
 597:          /// <summary>
 598:          ///
 599:          /// </summary>
 600:          public static Dictionary<string, string> DistinctAccessKuwaitNgnAreaIdAndBlockDictionary
 601:          {
 602:              get
 603:              {
 604:                  string kuwaitOfnAreaIdString, blockString, kuwaitOfnAreaBlockValue, kuwaitOfnAreaNameArabicName;
 605:   
 606:                  var dictionary = new Dictionary<string, string>();
 607:   
 608:                  var kuwaitOfnAreaList = Ia.Ngn.Cl.Model.Data.Service.KuwaitOfnAreaList;
 609:   
 610:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 611:                  {
 612:                      var tempDictionary = (from a in db.Accesses
 613:                                            select new { a.AreaId, a.Block }).Distinct().OrderBy(u => u.AreaId).ThenBy(u => u.Block).ToDictionary(m => m.AreaId + "," + m.Block, m => m.AreaId + "," + m.Block);
 614:   
 615:                      if (tempDictionary != null && tempDictionary.Count > 0)
 616:                      {
 617:                          foreach (KeyValuePair<string, string> kvp in tempDictionary)
 618:                          {
 619:                              kuwaitOfnAreaIdString = kvp.Key.Split(',')[0].ToString();
 620:                              blockString = kvp.Key.Split(',')[1].ToString();
 621:   
 622:                              if (int.TryParse(kuwaitOfnAreaIdString, out int kuwaitOfnAreaId) && int.TryParse(blockString, out int block))
 623:                              {
 624:                                  kuwaitOfnAreaNameArabicName = (from kna in kuwaitOfnAreaList
 625:                                                                 where kna.Id == kuwaitOfnAreaId
 626:                                                                 select kna.NameArabicName).SingleOrDefault();
 627:   
 628:                                  kuwaitOfnAreaBlockValue = kuwaitOfnAreaNameArabicName + ", block " + block;
 629:   
 630:                                  dictionary.Add(kvp.Key, kuwaitOfnAreaBlockValue);
 631:                              }
 632:                              else
 633:                              {
 634:                              }
 635:                          }
 636:                      }
 637:                      else
 638:                      {
 639:                      }
 640:                  }
 641:   
 642:                  return dictionary;
 643:              }
 644:          }
 645:   
 646:          ////////////////////////////////////////////////////////////////////////////
 647:   
 648:          /// <summary>
 649:          ///
 650:          /// </summary>
 651:          public static Dictionary<int, string> DistinctAccessKuwaitNgnAreaIdToNameArabicNameDictionary
 652:          {
 653:              get
 654:              {
 655:                  Ia.Ngn.Cl.Model.Business.Service.KuwaitOfnArea kuwaitOfnArea;
 656:                  List<int> list;
 657:                  Dictionary<int, string> dictionary;
 658:   
 659:                  dictionary = new Dictionary<int, string>();
 660:                  list = new List<int>();
 661:   
 662:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 663:                  {
 664:                      list = (from a in db.Accesses select a.AreaId).Distinct().ToList();
 665:   
 666:                      if (list.Count > 0)
 667:                      {
 668:                          foreach (int i in list)
 669:                          {
 670:                              kuwaitOfnArea = Ia.Ngn.Cl.Model.Data.Service.KuwaitOfnAreaById(i);
 671:   
 672:                              if (kuwaitOfnArea != null)
 673:                              {
 674:                                  dictionary.Add(i, kuwaitOfnArea.NameArabicName);
 675:                              }
 676:                          }
 677:                      }
 678:                      else
 679:                      {
 680:                      }
 681:                  }
 682:   
 683:                  return dictionary;
 684:              }
 685:          }
 686:   
 687:          ////////////////////////////////////////////////////////////////////////////
 688:   
 689:          /// <summary>
 690:          ///
 691:          /// </summary>
 692:          public static Dictionary<string, int> IdToKuwaitNgnAreaIdDictionary
 693:          {
 694:              get
 695:              {
 696:                  Dictionary<string, int> dictionary;
 697:   
 698:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 699:                  {
 700:                      dictionary = (from a in db.Accesses select new { a.Id, a.AreaId }).AsNoTracking().ToDictionary(u => u.Id, u => u.AreaId);
 701:                  }
 702:   
 703:                  return dictionary;
 704:              }
 705:          }
 706:   
 707:          ////////////////////////////////////////////////////////////////////////////
 708:   
 709:          /// <summary>
 710:          ///
 711:          /// </summary>
 712:          public static Dictionary<string, string> IdToKuwaitNgnAreaSymbolDictionary
 713:          {
 714:              get
 715:              {
 716:                  Dictionary<string, int> accessIdToKuwaitNgnAreaIdDictionary;
 717:                  Dictionary<int, string> areaIdToSymbolDictionary;
 718:                  Dictionary<string, string> dictionary;
 719:   
 720:                  accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 721:                  areaIdToSymbolDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToSymbolDictionary;
 722:   
 723:                  dictionary = new Dictionary<string, string>(accessIdToKuwaitNgnAreaIdDictionary.Count);
 724:   
 725:                  foreach (KeyValuePair<string, int> kvp in accessIdToKuwaitNgnAreaIdDictionary)
 726:                  {
 727:                      if (areaIdToSymbolDictionary.ContainsKey(kvp.Value)) dictionary[kvp.Key] = areaIdToSymbolDictionary[kvp.Value];
 728:                  }
 729:   
 730:                  return dictionary;
 731:              }
 732:          }
 733:   
 734:          ////////////////////////////////////////////////////////////////////////////
 735:   
 736:          /// <summary>
 737:          ///
 738:          /// </summary>
 739:          public static Dictionary<string, int> IdToOltIdDictionary
 740:          {
 741:              get
 742:              {
 743:                  Dictionary<string, int> dictionary;
 744:   
 745:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 746:                  {
 747:                      dictionary = (from a in db.Accesses select new { a.Id, a.Olt }).AsNoTracking().ToDictionary(u => u.Id, u => u.Olt);
 748:                  }
 749:   
 750:                  return dictionary;
 751:              }
 752:          }
 753:   
 754:          ////////////////////////////////////////////////////////////////////////////
 755:   
 756:          /// <summary>
 757:          ///
 758:          /// </summary>
 759:          public static Dictionary<string, Ia.Ngn.Cl.Model.Access> IdToAccessDictionary
 760:          {
 761:              get
 762:              {
 763:                  Dictionary<string, Ia.Ngn.Cl.Model.Access> dictionary;
 764:   
 765:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 766:                  {
 767:                      dictionary = (from a in db.Accesses select new { AccessId = a.Id, Access = a }).ToDictionary(u => u.AccessId, u => u.Access);
 768:                  }
 769:   
 770:                  return dictionary;
 771:              }
 772:          }
 773:   
 774:          ////////////////////////////////////////////////////////////////////////////
 775:   
 776:          /// <summary>
 777:          ///
 778:          /// </summary>
 779:          public static Dictionary<string, Ia.Ngn.Cl.Model.Access> IdToHuaweiAccessDictionary
 780:          {
 781:              get
 782:              {
 783:                  List<int> oltIdList;
 784:                  Dictionary<string, Ia.Ngn.Cl.Model.Access> dictionary;
 785:   
 786:                  oltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltIdList();
 787:   
 788:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 789:                  {
 790:                      dictionary = (from a in db.Accesses where oltIdList.Contains(a.Olt) select new { AccessId = a.Id, Access = a }).ToDictionary(u => u.AccessId, u => u.Access);
 791:                  }
 792:   
 793:                  return dictionary;
 794:              }
 795:          }
 796:   
 797:          ////////////////////////////////////////////////////////////////////////////
 798:   
 799:          /// <summary>
 800:          ///
 801:          /// </summary>
 802:          public static List<string> IdList
 803:          {
 804:              get
 805:              {
 806:                  List<string> list;
 807:   
 808:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 809:                  {
 810:                      list = (from a in db.Accesses select a.Id).AsNoTracking().ToList();
 811:                  }
 812:   
 813:                  return list;
 814:              }
 815:          }
 816:   
 817:          ////////////////////////////////////////////////////////////////////////////
 818:   
 819:          /// <summary>
 820:          ///
 821:          /// </summary>
 822:          public static Dictionary<string, string> IdDictionary
 823:          {
 824:              get
 825:              {
 826:                  Dictionary<string, string> dictionary;
 827:   
 828:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 829:                  {
 830:                      dictionary = (from a in db.Accesses select new { a.Id, AccessId2 = a.Id }).ToDictionary(u => u.Id, u => u.AccessId2);
 831:                  }
 832:   
 833:                  return dictionary;
 834:              }
 835:          }
 836:   
 837:          ////////////////////////////////////////////////////////////////////////////
 838:   
 839:          /// <summary>
 840:          ///
 841:          /// </summary>
 842:          public static List<Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock> FamilyStatisticInKuwaitNgnAreaAndBlockTable
 843:          {
 844:              // FamilyType { Undefined = 0, Sfu = 1, Soho = 2, Mdu = 3 };
 845:   
 846:              get
 847:              {
 848:                  Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock item;
 849:                  List<Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock> list, sfu, soho, mdu;
 850:   
 851:                  list = new List<Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock>();
 852:   
 853:                  using (var db = new Ia.Ngn.Cl.Model.Ngn())
 854:                  {
 855:                      sfu = (from a in db.Accesses join o in db.Onts on a.Id equals o.Access.Id where o.FamilyTypeId == 1 group a by new { a.AreaId, a.Block } into g select new Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock() { AreaId = g.Key.AreaId, Block = g.Key.Block, Sfu = g.Count() }).ToList();
 856:                      soho = (from a in db.Accesses join o in db.Onts on a.Id equals o.Access.Id where o.FamilyTypeId == 2 group a by new { a.AreaId, a.Block } into g select new Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock() { AreaId = g.Key.AreaId, Block = g.Key.Block, Soho = g.Count() }).ToList();
 857:                      mdu = (from a in db.Accesses join o in db.Onts on a.Id equals o.Access.Id where o.FamilyTypeId == 3 group a by new { a.AreaId, a.Block } into g select new Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock() { AreaId = g.Key.AreaId, Block = g.Key.Block, Mdu = g.Count() }).ToList();
 858:   
 859:                      foreach (Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock a in sfu) list.Add(new Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock { AreaId = a.AreaId, Block = a.Block, Sfu = a.Sfu });
 860:   
 861:                      foreach (Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock a in soho)
 862:                      {
 863:                          item = (from b in list where b.AreaId == a.AreaId && b.Block == a.Block select b).SingleOrDefault();
 864:   
 865:                          if (item != null) item.Soho = a.Soho;
 866:                          else list.Add(new Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock { AreaId = a.AreaId, Block = a.Block, Soho = a.Soho });
 867:                      }
 868:   
 869:                      foreach (Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock a in mdu)
 870:                      {
 871:                          item = (from b in list where b.AreaId == a.AreaId && b.Block == a.Block select b).SingleOrDefault();
 872:   
 873:                          if (item != null) item.Mdu = a.Mdu;
 874:                          else list.Add(new Ia.Ngn.Cl.Model.Ui.Maintenance.AccessFamilyTypeAreaBlock { AreaId = a.AreaId, Block = a.Block, Mdu = a.Mdu });
 875:                      }
 876:   
 877:                      list = list.OrderBy(u => u.AreaId).ThenBy(u => u.Block).ToList();
 878:   
 879:                      return list;
 880:                  }
 881:              }
 882:          }
 883:   
 884:          ////////////////////////////////////////////////////////////////////////////
 885:   
 886:          /// <summary>
 887:          ///
 888:          /// </summary>
 889:          public static List<Ia.Ngn.Cl.Model.Access> ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabase()
 890:          {
 891:              return ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabaseBySiteId(0);
 892:          }
 893:   
 894:          ////////////////////////////////////////////////////////////////////////////
 895:   
 896:          /// <summary>
 897:          ///
 898:          /// </summary>
 899:          public static List<Ia.Ngn.Cl.Model.Access> ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabaseBySiteId(int siteId)
 900:          {
 901:              Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
 902:              List<int> nokiaOltIdList, huaweiOltIdList;
 903:              List<Ia.Ngn.Cl.Model.Access> list, nokiaAccessList, huaweiAccessList;
 904:   
 905:              // this is related to AccessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary
 906:   
 907:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 908:              {
 909:                  if (siteId == 0)
 910:                  {
 911:                      // if siteId is 0 we will return results for all sites
 912:   
 913:                      //nokiaOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaOltIdList();
 914:   
 915:                      // Nokia
 916:                      nokiaAccessList = (from a in db.Accesses
 917:                                             // join oid in nokiaOltIdList on a.Olt equals oid
 918:                                         join o in db.Onts on a equals o.Access
 919:                                         join osv in db.OntServiceVoips on o equals osv.Ont
 920:                                         join sro in db.ServiceRequestOnts on a equals sro.Access into gj
 921:                                         from subsro in gj.DefaultIfEmpty()
 922:                                         where o.StateId == (int)Ia.Ngn.Cl.Model.Business.Nokia.Ams.BellcoreState.IsNr
 923:                                         && o.Serial != null
 924:                                         && o.Serial != "ALCL00000000"
 925:                                         && o.FamilyTypeId != 0
 926:                                         && o.ActiveSoftware != null
 927:                                         //&& o.ActiveSoftware == o.PlannedSoftware
 928:                                         && osv.Ip != null
 929:                                         && subsro.Access.Id == null
 930:                                         select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).AsNoTracking().ToList();
 931:   
 932:                      // Huawei
 933:                      huaweiOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltIdList();
 934:   
 935:                      huaweiAccessList = (from a in db.Accesses
 936:                                          join o in db.EmsOnts on a equals o.Access
 937:                                          join sro in db.ServiceRequestOnts on a equals sro.Access into sros
 938:                                          from sro in sros.DefaultIfEmpty()
 939:                                          where o.EQUIPMENTID != string.Empty //.FamilyTypeId != 0
 940:                                          && huaweiOltIdList.Contains(a.Olt)
 941:                                          && sro.Access.Id == null
 942:                                          select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).AsNoTracking().ToList();
 943:   
 944:                      list = nokiaAccessList.Union(huaweiAccessList).ToList();
 945:                  }
 946:                  else
 947:                  {
 948:                      site = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where s.Id == siteId select s).SingleOrDefault();
 949:   
 950:                      nokiaOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaOltIdList(siteId);
 951:   
 952:                      // Nokia
 953:                      nokiaAccessList = (from a in db.Accesses
 954:                                         join oid in nokiaOltIdList on a.Olt equals oid
 955:                                         join o in db.Onts on a equals o.Access
 956:                                         join osv in db.OntServiceVoips on o equals osv.Ont
 957:                                         join sro in db.ServiceRequestOnts on a equals sro.Access into sros
 958:                                         from sro in sros.DefaultIfEmpty()
 959:                                         where o.StateId == (int)Ia.Ngn.Cl.Model.Business.Nokia.Ams.BellcoreState.IsNr
 960:                                         && o.Serial != null
 961:                                         && o.Serial != "ALCL00000000"
 962:                                         && o.FamilyTypeId != 0
 963:                                         && o.ActiveSoftware != null
 964:                                         //&& o.ActiveSoftware == o.PlannedSoftware
 965:                                         && osv.Ip != null
 966:                                         && sro.Access.Id == null
 967:                                         select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).AsNoTracking().ToList();
 968:   
 969:                      // Huawei
 970:                      huaweiOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltIdList(siteId);
 971:   
 972:                      huaweiAccessList = (from a in db.Accesses
 973:                                          join o in db.EmsOnts on a equals o.Access
 974:                                          join sro in db.ServiceRequestOnts on a equals sro.Access into sros
 975:                                          from sro in sros.DefaultIfEmpty()
 976:                                          where o.EQUIPMENTID != string.Empty //.FamilyTypeId != 0
 977:                                          && huaweiOltIdList.Contains(a.Olt)
 978:                                          && sro.Access.Id == null
 979:                                          select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).AsNoTracking().ToList();
 980:   
 981:                      list = nokiaAccessList.Union(huaweiAccessList).ToList();
 982:                  }
 983:   
 984:                  // will exclude the following access name list
 985:                  var excludedAccessNameList = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.AccessNamesThatRevertToAnUnexplainedErroneousStateInTheCustomerDepartmentDatabaseAndAreExemptFromSynchronizationProcessingList();
 986:   
 987:                  list = (from l in list where !excludedAccessNameList.Contains(l.Name) select l).ToList();
 988:              }
 989:   
 990:              return list.OrderBy(u => u.Id).ToList();
 991:          }
 992:   
 993:          ////////////////////////////////////////////////////////////////////////////
 994:   
 995:          /// <summary>
 996:          ///
 997:          /// </summary>
 998:          public static List<Ia.Ngn.Cl.Model.Access> AccessesWithProvisionedAndReadyOntsAndEmsOntsButMismatchedWithCustomerDepartmentDatabaseServiceRequestOntListList()
 999:          {
            List<Ia.Ngn.Cl.Model.Access> list;
 
            var accessCapacityDictionary = Ia.Ngn.Cl.Model.Data.Access.AccessCapacityDictionary();
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                // I will ignore area because its fixed with access id, which is shared by both Access and ServiceRequestOnt
 
                var nokiaList = (from a in db.Accesses
                                 join o in db.Onts on a equals o.Access
                                 join osv in db.OntServiceVoips on o equals osv.Ont
                                 join sro in db.ServiceRequestOnts on a equals sro.Access
                                 select new
                                 {
                                     AccessId = a.Id,
 
                                     aBlock = a.Block,
                                     aStreet = a.Street,
                                     aPremisesOld = a.PremisesOld,
                                     aPremisesNew = a.PremisesNew,
                                     aPaci = a.Paci,
 
                                     sroBlock = sro.Block,
                                     sroStreet = sro.Street,
                                     sroPremisesOld = sro.PremisesOld,
                                     sroPremisesNew = sro.PremisesNew,
                                     sroPaci = sro.Paci,
                                     sroPossibleNumberOfTd = sro.PossibleNumberOfTd,
                                     sroPossibleNumberOfEthernet = sro.PossibleNumberOfEthernet
                                 }
                            ).AsNoTracking().ToList();
 
                // Huawei
                var huaweiOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltIdList();
 
                var huaweiList = (from a in db.Accesses
                                  join o in db.EmsOnts on a equals o.Access
                                  join sro in db.ServiceRequestOnts on a equals sro.Access
                                  where huaweiOltIdList.Contains(a.Olt)
                                  select new
                                  {
                                      AccessId = a.Id,
 
                                      aBlock = a.Block,
                                      aStreet = a.Street,
                                      aPremisesOld = a.PremisesOld,
                                      aPremisesNew = a.PremisesNew,
                                      aPaci = a.Paci,
 
                                      sroBlock = sro.Block,
                                      sroStreet = sro.Street,
                                      sroPremisesOld = sro.PremisesOld,
                                      sroPremisesNew = sro.PremisesNew,
                                      sroPaci = sro.Paci,
                                      sroPossibleNumberOfTd = sro.PossibleNumberOfTd,
                                      sroPossibleNumberOfEthernet = sro.PossibleNumberOfEthernet
                                  }
                                 ).AsNoTracking().ToList();
 
                var list0 = nokiaList.Union(huaweiList).ToList();
 
                var mismatchedAccessIdlist = (from l in list0
                                              where accessCapacityDictionary.ContainsKey(l.AccessId) &&
                                              (
                                              l.sroBlock != l.aBlock && !string.IsNullOrEmpty(l.aBlock)
                                              || l.sroStreet != l.aStreet && !string.IsNullOrEmpty(l.aStreet)
                                              || l.sroPremisesOld != l.aPremisesOld && !string.IsNullOrEmpty(l.aPremisesOld)
                                              || l.sroPremisesNew != l.aPremisesNew && !string.IsNullOrEmpty(l.aPremisesNew)
                                              || l.sroPaci != l.aPaci && !string.IsNullOrEmpty(l.aPaci)
                                              || l.sroPossibleNumberOfTd != accessCapacityDictionary[l.AccessId].PossibleNumberOfTd
                                              || l.sroPossibleNumberOfEthernet != accessCapacityDictionary[l.AccessId].PossibleNumberOfEthernet
                                              )
                                              select l.AccessId).ToList();
 
                list = (from a in db.Accesses
                        where mismatchedAccessIdlist.Contains(a.Id)
                        select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).ToList();
 
                // will exclude the following access name list
                var excludedAccessNameList = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.AccessNamesThatRevertToAnUnexplainedErroneousStateInTheCustomerDepartmentDatabaseAndAreExemptFromSynchronizationProcessingList();
 
                list = (from l in list where !excludedAccessNameList.Contains(l.Name) select l).ToList();
            }
 
            // debugging
            // list = list.Where(u => u.Id == "1060204011646001").ToList();
 
            return list.OrderBy(u => u.Id).ToList();
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Access> AccessesWithMismatchedInfomationInServiceRequestOntList()
        {
            List<Ia.Ngn.Cl.Model.Access> list;
            //Dictionary<int, string> areaIdToSymbolDictionary;
 
            //areaIdToSymbolDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToSymbolDictionary;
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                // I will ignore area because its fixed with access id, which is shared by both Access and ServiceRequestOnt
 
                list = (from a in db.Accesses
                        join o in db.Onts on a equals o.Access
                        join osv in db.OntServiceVoips on o equals osv.Ont
                        join sro in db.ServiceRequestOnts on a equals sro.Access
                        //join areaSymbol in areaIdToSymbolDictionary on a.AreaId equals areaSymbol.Key
                        //where sro.AreaSymbol != areaSymbol.Value
                        where (sro.Block != a.Block && !string.IsNullOrEmpty(a.Block))
                        || (sro.Street != a.Street && !string.IsNullOrEmpty(a.Street))
                        || (sro.PremisesOld != a.PremisesOld && !string.IsNullOrEmpty(a.PremisesOld))
                        || (sro.PremisesNew != a.PremisesNew && !string.IsNullOrEmpty(a.PremisesNew))
                        || (sro.Paci != a.Paci && !string.IsNullOrEmpty(a.Paci))
                        //|| (sro.PossibleNumberOfTd != a.PossibleNumberOfTd)
                        //|| (sro.PossibleNumberOfEthernet != a.PossibleNumberOfEthernet)
                        select a).Distinct().Include(c => c.Onts).Include(c => c.EmsOnts).ToList();
            }
 
            return list.OrderBy(u => u.Id).ToList();
        }
 
        ////////////////////////////////////////////////////////////////////////////    
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<string, Ia.Ngn.Cl.Model.Business.AccessCapacity> AccessCapacityDictionary()
        {
            // Similar to Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.PrepareServiceRequestOntListFromAccessList()
 
            bool familyExists;
            int possibleNumberOfTd, possibleNumberOfEthernet;
            string accessOntFamilyTypeCapacityString;
 
            var list = Ia.Ngn.Cl.Model.Data.Access.ListWithOntsAndEmsOnts();
 
            var dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.Business.AccessCapacity>(list.Count);
 
            foreach (var access in list)
            {
                if (access.Id == "1050102010044018")
                {
 
                }
 
                if (access.Onts != null && access.Onts.Count > 0)
                {
                    if (access.Onts.First().FamilyType != Ia.Ngn.Cl.Model.Business.Nokia.Ont.FamilyType.Undefined)
                    {
                        accessOntFamilyTypeCapacityString = Ia.Ngn.Cl.Model.Data.Nokia.Ont.FamilyTypeStringFromId(access.Onts.FirstOrDefault().FamilyTypeId);
 
                        possibleNumberOfTd = Ia.Ngn.Cl.Model.Business.Nokia.Ont.PossibleNumberOfTdForOntFamilyType(access.Onts.FirstOrDefault().FamilyTypeId);
                        possibleNumberOfEthernet = Ia.Ngn.Cl.Model.Business.Nokia.Ont.PossibleNumberOfHsiCardPortServiceConfigurationForOntFamilyType(access.Onts.FirstOrDefault().FamilyTypeId);
 
                        accessOntFamilyTypeCapacityString += " (" + possibleNumberOfTd + ")";
 
                        familyExists = true;
                    }
                    else
                    {
                        possibleNumberOfTd = 0;
                        possibleNumberOfEthernet = 0;
 
                        accessOntFamilyTypeCapacityString = string.Empty;
 
                        familyExists = false;
                    }
                }
                else if (access.EmsOnts != null && access.EmsOnts.Count > 0)
                {
                    var familyType = access.EmsOnts.First().FamilyType;
                    var equipmentType = access.EmsOnts.First().EquipmentType;
 
                    if (familyType != Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Undefined && equipmentType != null)
                    {
                        accessOntFamilyTypeCapacityString = familyType.ToString().ToUpper();
 
                        possibleNumberOfTd = equipmentType.TelPorts;
                        possibleNumberOfEthernet = equipmentType.EthernetPorts;
 
                        accessOntFamilyTypeCapacityString += " (" + possibleNumberOfTd + ")";
 
                        familyExists = true;
                    }
                    else
                    {
                        possibleNumberOfTd = 0;
                        possibleNumberOfEthernet = 0;
 
                        accessOntFamilyTypeCapacityString = string.Empty;
 
                        familyExists = false;
                    }
                }
                else
                {
                    possibleNumberOfTd = 0;
                    possibleNumberOfEthernet = 0;
 
                    accessOntFamilyTypeCapacityString = string.Empty;
 
                    familyExists = false;
                }
 
                if (familyExists)
                {
                    dictionary[access.Id] = new Business.AccessCapacity
                    {
                        AccessId = access.Id,
                        PossibleNumberOfTd = possibleNumberOfTd,
                        PossibleNumberOfEthernet = possibleNumberOfEthernet,
                        FamilyTypeCapacityString = accessOntFamilyTypeCapacityString
                    };
                }
                else
                {
 
                }
            }
 
            return dictionary;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<string, int> AccessWithProvisionedAndReadyOntIdToKuwaitNgnAreaIdDictionary
        {
            get
            {
                Dictionary<string, int> dictionary, nokiaDictionary, huaweiDictionary;
                List<int> huaweiOltIdList;//, nokiaOltIdList;
 
                // this is related to ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabaseBySiteId()
                // and to AccessWithProvisionedAndReadyOntsIdListByKuwaitNgnAreaId()
 
                using (var db = new Ia.Ngn.Cl.Model.Ngn())
                {
                    //nokiaOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaOltIdList();
 
                    // Nokia
                    nokiaDictionary = (from a in db.Accesses
                                       join o in db.Onts on a equals o.Access
                                       join osv in db.OntServiceVoips on o equals osv.Ont
                                       join sro in db.ServiceRequestOnts on a equals sro.Access
                                       where o.StateId == (int)Ia.Ngn.Cl.Model.Business.Nokia.Ams.BellcoreState.IsNr // ?
                                       && o.Serial != null
                                       && o.Serial != "ALCL00000000"
                                       && o.FamilyTypeId != 0 // ?
                                       && o.ActiveSoftware != null
                                       //&& o.ActiveSoftware == o.PlannedSoftware
                                       && osv.Ip != null
                                       select new { a.Id, a.AreaId }).AsNoTracking().Distinct().ToDictionary(u => u.Id, u => u.AreaId);
 
                    // Huawei
                    huaweiOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltIdList();
 
                    huaweiDictionary = (from a in db.Accesses
                                        join o in db.EmsOnts on a equals o.Access
                                        join sro in db.ServiceRequestOnts on a equals sro.Access
                                        //where o.FamilyTypeId != 0
                                        where o.EQUIPMENTID != "--"
                                        && huaweiOltIdList.Contains(a.Olt)
                                        select new { a.Id, a.AreaId }).AsNoTracking().Distinct().ToDictionary(u => u.Id, u => u.AreaId);
 
                    dictionary = nokiaDictionary.Union(huaweiDictionary).ToDictionary(k => k.Key, v => v.Value);
                }
 
                return dictionary;
            }
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<string> AccessWithProvisionedAndReadyOntsAccessIdListByKuwaitNgnAreaId(int areaId)
        {
            List<string> list, nokiaList, huaweiList;
            List<int> huaweiOltIdList;//, nokiaOltIdList;
 
            // this is related to ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabaseBySiteId()
            // and to AccessWithProvisionedAndReadyOntIdToKuwaitNgnAreaIdDictionary
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                //nokiaOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaOltIdList();
 
                // Nokia
                nokiaList = (from a in db.Accesses
                             join o in db.Onts on a equals o.Access
                             join osv in db.OntServiceVoips on o equals osv.Ont
                             join sro in db.ServiceRequestOnts on a equals sro.Access
                             where o.StateId == (int)Ia.Ngn.Cl.Model.Business.Nokia.Ams.BellcoreState.IsNr // ?
                             && o.Serial != null
                             && o.Serial != "ALCL00000000"
                             && o.FamilyTypeId != 0 // ?
                             && o.ActiveSoftware != null
                             //&& o.ActiveSoftware == o.PlannedSoftware
                             && osv.Ip != null
                             && a.AreaId == areaId
                             select a.Id).AsNoTracking().Distinct().ToList();
 
                // Huawei
                huaweiOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltIdList();
 
                huaweiList = (from a in db.Accesses
                              join o in db.EmsOnts on a equals o.Access
                              join sro in db.ServiceRequestOnts on a equals sro.Access
                              //where o.FamilyTypeId != 0
                              where o.EQUIPMENTID != "--"
                              && huaweiOltIdList.Contains(a.Olt)
                              && a.AreaId == areaId
                              select a.Id).AsNoTracking().Distinct().ToList();
 
                list = nokiaList.Union(huaweiList).ToList();
            }
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<string> AccessNamesWithoutServicesByKuwaitNgnAreaId(int areaId)
        {
            int serviceCount;
            string accessName;
 
            var ll = new List<string>();
 
            var list = Ia.Ngn.Cl.Model.Data.Access.AccessWithProvisionedAndReadyOntsAccessIdListByKuwaitNgnAreaId(areaId);
 
            var accessIdToAccessNameDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
            var accessNameToSeviceCountDictionary = Ia.Ngn.Cl.Model.Data.Service2.AccessNameToSeviceCountDictionary();
 
            var dictionary = new Dictionary<string, List<string>>();
 
            dictionary["Unknown"] = new List<string>();
 
            foreach (var accessId in list)
            {
                if (!string.IsNullOrEmpty(accessId))
                {
                    if (accessIdToAccessNameDictionary.ContainsKey(accessId))
                    {
                        accessName = accessIdToAccessNameDictionary[accessId];
 
                        if (!string.IsNullOrEmpty(accessName))
                        {
                            if (accessNameToSeviceCountDictionary.ContainsKey(accessName))
                            {
                                serviceCount = accessNameToSeviceCountDictionary[accessName];
 
                                if (serviceCount == 0)
                                {
                                    ll.Add(accessName);
                                }
                                else
                                {
 
                                }
                            }
                            else
                            {
 
                            }
                        }
                        else
                        {
 
                        }
                    }
                    else
                    {
 
                    }
                }
                else
                {
                }
            }
 
            ll.Sort();
 
            return ll;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Access> NokiaAccessList
        {
            get
            {
                List<int> oltIdList;
                List<Ia.Ngn.Cl.Model.Access> list;
 
                oltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaOltIdList();
 
                using (var db = new Ia.Ngn.Cl.Model.Ngn())
                {
                    list = (from a in db.Accesses where oltIdList.Contains(a.Olt) select a).ToList();
                }
 
                return list;
            }
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Access> HuaweiAccessList
        {
            get
            {
                List<int> oltIdList;
                List<Ia.Ngn.Cl.Model.Access> list;
 
                oltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltIdList();
 
                using (var db = new Ia.Ngn.Cl.Model.Ngn())
                {
                    list = (from a in db.Accesses where oltIdList.Contains(a.Olt) select a).ToList();
                }
 
                return list;
            }
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Access> HuaweiAccessesWithNoEmsOntList
        {
            get
            {
                List<int> huaweiOltIdList;
                List<Ia.Ngn.Cl.Model.Access> list;
 
                huaweiOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltIdList();
 
                using (var db = new Ia.Ngn.Cl.Model.Ngn())
                {
                    list = (from a in db.Accesses
                            join o in db.EmsOnts on a equals o.Access into gj
                            from subsro in gj.DefaultIfEmpty()
                            where huaweiOltIdList.Contains(a.Olt) && subsro.Access.Id == null
                            select a).Distinct().ToList();
                }
 
                return list;
            }
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Ia.Ngn.Cl.Model.Access ReadByIp(string ip)
        {
            // below: return the Access object of this ip
 
            Ia.Ngn.Cl.Model.Access item;
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                item = (from a in db.Accesses
                        join o in db.Onts on a equals o.Access
                        join ov in db.OntServiceVoips on o equals ov.Ont
                        where ov.Ip == ip
                        select a).SingleOrDefault();
            }
 
            return item;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static Ia.Ngn.Cl.Model.Ui.Access StatisticalAccess(Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress, ref List<Ia.Ngn.Cl.Model.Ui.Access> accessList, out string note)
        {
            int numberOfTrials;
            Ia.Ngn.Cl.Model.Ui.Access statisticalAccess;
            List<Ia.Ngn.Cl.Model.Ui.Access> _accessList;
 
            numberOfTrials = 0;
 
            if (serviceAddress.AreaId != 0)
            {
                numberOfTrials++;
 
                _accessList = accessList.Where(u => u.AreaId == serviceAddress.AreaId).ToList();
 
                if (!string.IsNullOrEmpty(serviceAddress.Block) && _accessList.Any(u => u.Block == serviceAddress.Block))
                {
                    numberOfTrials++;
 
                    note = string.Empty;
 
                    _accessList = accessList.Where(u => u.AreaId == serviceAddress.AreaId && u.Block == serviceAddress.Block).ToList();
 
                    if (!string.IsNullOrEmpty(serviceAddress.Street))
                    {
                        numberOfTrials++;
 
                        statisticalAccess = StatisticalAccessBlockAndStreet(serviceAddress, serviceAddress.Street, ref accessList, ref note, ref numberOfTrials);
                    }
                    else
                    {
                        statisticalAccess = StatisticalAccessBlockAndNoStreet(serviceAddress, ref accessList, ref note, ref numberOfTrials);
                    }
                }
                else
                {
                    numberOfTrials++;
 
                    statisticalAccess = null;
 
                    note = "serviceAddress.Block is empty or null";
                }
            }
            else
            {
                numberOfTrials++;
 
                statisticalAccess = null;
 
                note = "serviceAddress.AreaId is 0";
            }
 
            if (statisticalAccess != null) statisticalAccess.NumberOfTrials = numberOfTrials;
 
            return statisticalAccess;
        }
 
        //
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        private static Ia.Ngn.Cl.Model.Ui.Access LevenshteinDistance(Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress, ref List<Ia.Ngn.Cl.Model.Ui.Access> accessList)
        {
            string accessLevenshteinString, serviceAddressLevenshteinString, idOfKeyWithMinValue;
            Dictionary<string, int> dictionary;
            Ia.Ngn.Cl.Model.Ui.Access statisticalAccess;
 
            dictionary = new Dictionary<string, int>();
 
            serviceAddressLevenshteinString = serviceAddress.Block + ":" + serviceAddress.Street + ":" + serviceAddress.PremisesOld + ":" + serviceAddress.PremisesNew;
 
            foreach (var a in accessList)
            {
                accessLevenshteinString = a.Block + ":" + a.Street + ":" + a.PremisesOld + ":" + a.PremisesNew;
 
                dictionary[a.Id] = Ia.Cl.Model.Default.LevenshteinDistance(serviceAddressLevenshteinString, accessLevenshteinString);
            }
 
            var minValue = dictionary.Values.Min();
 
            idOfKeyWithMinValue = dictionary.FirstOrDefault(u => u.Value == minValue).Key;
 
            statisticalAccess = (from a in accessList where a.Id == idOfKeyWithMinValue select a).SingleOrDefault();
 
            return statisticalAccess;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        private static Ia.Ngn.Cl.Model.Ui.Access StatisticalAccessBlockAndStreet(Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress, string street, ref List<Ia.Ngn.Cl.Model.Ui.Access> accessList, ref string note, ref int numberOfTrials)
        {
            Ia.Ngn.Cl.Model.Ui.Access statisticalAccess;
            List<Ia.Ngn.Cl.Model.Ui.Access> _accessList;
 
            numberOfTrials++;
 
            _accessList = accessList.Where(u => u.AreaId == serviceAddress.AreaId && u.Block == serviceAddress.Block && u.Street == street).ToList();
 
            note = string.Empty;
 
            EvaluateAndCorrectPremisesOldAndPremisesNew(serviceAddress.PremisesOld, serviceAddress.PremisesNew, out string po, out string pn);
            serviceAddress.PremisesOld = po;
            serviceAddress.PremisesNew = pn;
 
            statisticalAccess = (from a in _accessList
                                 where a.AreaId == serviceAddress.AreaId
                                     && a.Block == serviceAddress.Block
                                     && a.Street == street
                                     && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
                                     && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
                                 select a).FirstOrDefault();
 
            if (statisticalAccess == null)
            {
                numberOfTrials++;
 
                statisticalAccess = (from a in _accessList
                                     where a.AreaId == serviceAddress.AreaId
                                         && a.Block == serviceAddress.Block
                                         && a.Street == street
                                         && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
                                     select a).FirstOrDefault();
 
                if (statisticalAccess == null)
                {
                    numberOfTrials++;
 
                    statisticalAccess = (from a in _accessList
                                         where a.AreaId == serviceAddress.AreaId
                                             && a.Block == serviceAddress.Block
                                             && a.Street == street
                                             && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
                                         select a).FirstOrDefault();
 
                    if (statisticalAccess == null)
                    {
                        numberOfTrials++;
 
                        statisticalAccess = (from a in _accessList
                                             where a.AreaId == serviceAddress.AreaId
                                                 && a.Block == serviceAddress.Block
                                                 && a.Street == street
                                                 && a.PremisesOld == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
                                             select a).FirstOrDefault();
 
                        if (statisticalAccess == null)
                        {
                            numberOfTrials++;
 
                            statisticalAccess = (from a in _accessList
                                                 where a.AreaId == serviceAddress.AreaId
                                                     && a.Block == serviceAddress.Block
                                                     && a.Street == street
                                                     && a.PremisesNew == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
                                                 select a).FirstOrDefault();
 
                            if (statisticalAccess == null)
                            {
                                numberOfTrials++;
 
                                statisticalAccess = (from a in _accessList
                                                     where a.AreaId == serviceAddress.AreaId
                                                         && a.Block == serviceAddress.Block
                                                         && a.Street == street
                                                         && a.PremisesNew == serviceAddress.Boulevard && !string.IsNullOrEmpty(serviceAddress.Boulevard)
                                                     select a).FirstOrDefault();
 
                                if (statisticalAccess == null)
                                {
                                    numberOfTrials++;
 
                                    statisticalAccess = (from a in _accessList
                                                         where a.AreaId == serviceAddress.AreaId
                                                             && a.Block == serviceAddress.Block
                                                             && a.Street == street
                                                             && a.PremisesOld == serviceAddress.Boulevard && !string.IsNullOrEmpty(serviceAddress.Boulevard)
                                                         select a).FirstOrDefault();
 
                                    if (statisticalAccess == null)
                                    {
                                        numberOfTrials++;
 
                                        statisticalAccess = (from a in _accessList
                                                             where a.AreaId == serviceAddress.AreaId
                                                                 && a.Block == serviceAddress.Block
                                                                 && a.Street == street
                                                                 && !string.IsNullOrEmpty(serviceAddress.PremisesOld) && !string.IsNullOrEmpty(a.PremisesOld) && a.PremisesOld.Contains(serviceAddress.PremisesOld)
                                                                 && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
                                                             select a).FirstOrDefault();
 
                                        if (statisticalAccess == null)
                                        {
                                            numberOfTrials++;
 
                                            statisticalAccess = (from a in _accessList
                                                                 where a.AreaId == serviceAddress.AreaId
                                                                     && a.Block == serviceAddress.Block
                                                                     && a.Street == street
                                                                     && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
                                                                     && !string.IsNullOrEmpty(serviceAddress.PremisesNew) && !string.IsNullOrEmpty(a.PremisesNew) && a.PremisesNew.Contains(serviceAddress.PremisesNew)
                                                                 select a).FirstOrDefault();
 
                                            if (statisticalAccess == null)
                                            {
                                                numberOfTrials++;
 
                                                statisticalAccess = (from a in _accessList
                                                                     where a.AreaId == serviceAddress.AreaId
                                                                         && a.Block == serviceAddress.Block
                                                                         && a.Street == street
                                                                         && !string.IsNullOrEmpty(serviceAddress.PremisesOld) && !string.IsNullOrEmpty(a.PremisesOld) && a.PremisesOld.Contains(serviceAddress.PremisesOld)
                                                                     select a).FirstOrDefault();
 
                                                if (statisticalAccess == null)
                                                {
                                                    numberOfTrials++;
 
                                                    statisticalAccess = (from a in _accessList
                                                                         where a.AreaId == serviceAddress.AreaId
                                                                             && a.Block == serviceAddress.Block
                                                                             && a.Street == street
                                                                             && !string.IsNullOrEmpty(serviceAddress.PremisesNew) && !string.IsNullOrEmpty(a.PremisesNew) && a.PremisesNew.Contains(serviceAddress.PremisesNew)
                                                                         select a).FirstOrDefault();
 
                                                    if (statisticalAccess == null)
                                                    {
                                                        numberOfTrials++;
 
 
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
 
            return statisticalAccess;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        private static Ia.Ngn.Cl.Model.Ui.Access StatisticalAccessBlockAndNoStreet(Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress, ref List<Ia.Ngn.Cl.Model.Ui.Access> accessList, ref string note, ref int numberOfTrials)
        {
            Ia.Ngn.Cl.Model.Ui.Access statisticalAccess;
            List<Ia.Ngn.Cl.Model.Ui.Access> _accessList;
 
            numberOfTrials++;
 
            _accessList = accessList.Where(u => u.AreaId == serviceAddress.AreaId && u.Block == serviceAddress.Block).ToList();
 
            note = string.Empty;
 
            EvaluateAndCorrectPremisesOldAndPremisesNew(serviceAddress.PremisesOld, serviceAddress.PremisesNew, out string po, out string pn);
            serviceAddress.PremisesOld = po;
            serviceAddress.PremisesNew = pn;
 
            statisticalAccess = (from a in _accessList
                                 where a.AreaId == serviceAddress.AreaId
                                     && a.Block == serviceAddress.Block
                                     && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
                                     && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
                                 select a).FirstOrDefault();
 
            if (statisticalAccess == null)
            {
                statisticalAccess = (from a in _accessList
                                     where a.AreaId == serviceAddress.AreaId
                                         && a.Block == serviceAddress.Block
                                         && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
                                     select a).FirstOrDefault();
 
                if (statisticalAccess == null)
                {
                    numberOfTrials++;
 
                    statisticalAccess = (from a in _accessList
                                         where a.AreaId == serviceAddress.AreaId
                                             && a.Block == serviceAddress.Block
                                             && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
                                         select a).FirstOrDefault();
 
                    if (statisticalAccess == null)
                    {
                        numberOfTrials++;
 
                        statisticalAccess = (from a in _accessList
                                             where a.AreaId == serviceAddress.AreaId
                                                 && a.Block == serviceAddress.Block
                                                 && a.PremisesOld == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
                                             select a).FirstOrDefault();
 
                        if (statisticalAccess == null)
                        {
                            numberOfTrials++;
 
                            statisticalAccess = (from a in _accessList
                                                 where a.AreaId == serviceAddress.AreaId
                                                     && a.Block == serviceAddress.Block
                                                     && a.PremisesNew == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
                                                 select a).FirstOrDefault();
 
                            if (statisticalAccess == null)
                            {
                                numberOfTrials++;
 
                                statisticalAccess = (from a in _accessList
                                                     where a.AreaId == serviceAddress.AreaId
                                                         && a.Block == serviceAddress.Block
                                                         && !string.IsNullOrEmpty(serviceAddress.PremisesOld) && !string.IsNullOrEmpty(a.PremisesOld) && a.PremisesOld.Contains(serviceAddress.PremisesOld)
                                                         && a.PremisesNew == serviceAddress.PremisesNew && !string.IsNullOrEmpty(serviceAddress.PremisesNew)
                                                     select a).FirstOrDefault();
 
                                if (statisticalAccess == null)
                                {
                                    numberOfTrials++;
 
                                    statisticalAccess = (from a in _accessList
                                                         where a.AreaId == serviceAddress.AreaId
                                                             && a.Block == serviceAddress.Block
                                                             && a.PremisesOld == serviceAddress.PremisesOld && !string.IsNullOrEmpty(serviceAddress.PremisesOld)
                                                             && !string.IsNullOrEmpty(serviceAddress.PremisesNew) && !string.IsNullOrEmpty(a.PremisesNew) && a.PremisesNew.Contains(serviceAddress.PremisesNew)
                                                         select a).FirstOrDefault();
 
                                    if (statisticalAccess == null)
                                    {
                                        numberOfTrials++;
 
                                        statisticalAccess = (from a in _accessList
                                                             where a.AreaId == serviceAddress.AreaId
                                                                 && a.Block == serviceAddress.Block
                                                                 && !string.IsNullOrEmpty(serviceAddress.PremisesOld) && !string.IsNullOrEmpty(a.PremisesOld) && a.PremisesOld.Contains(serviceAddress.PremisesOld)
                                                             select a).FirstOrDefault();
 
                                        if (statisticalAccess == null)
                                        {
                                            numberOfTrials++;
 
                                            statisticalAccess = (from a in _accessList
                                                                 where a.AreaId == serviceAddress.AreaId
                                                                     && a.Block == serviceAddress.Block
                                                                     && !string.IsNullOrEmpty(serviceAddress.PremisesNew) && !string.IsNullOrEmpty(a.PremisesNew) && a.PremisesNew.Contains(serviceAddress.PremisesNew)
                                                                 select a).FirstOrDefault();
 
                                            if (statisticalAccess == null)
                                            {
                                                numberOfTrials++;
 
                                                statisticalAccess = null;
 
                                                note = "serviceAddress.Street is empty or null";
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
 
            return statisticalAccess;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static void EvaluateAndCorrectPremisesOldAndPremisesNew(string premisesOld, string premisesNew, out string newPremisesOld, out string newPremisesNew)
        {
            if (!string.IsNullOrEmpty(premisesOld) && !string.IsNullOrEmpty(premisesNew))
            {
                if (int.TryParse(premisesOld, out int po) && int.TryParse(premisesNew, out int pn))
                {
                    // if equal then one is invalid
                    if (po == pn)
                    {
                        if (po > 100)
                        {
                            newPremisesOld = po.ToString();
                            newPremisesNew = string.Empty;
                        }
                        else
                        {
                            newPremisesOld = string.Empty;
                            newPremisesNew = po.ToString();
                        }
                    }
                    else
                    {
                        if (pn > po)
                        {
                            newPremisesOld = pn.ToString();
                            newPremisesNew = po.ToString();
                        }
                        else
                        {
                            newPremisesOld = po.ToString();
                            newPremisesNew = pn.ToString();
                        }
                    }
                }
                else if (int.TryParse(premisesOld, out po) && !int.TryParse(premisesNew, out _))
                {
                    // if equal then one is invalid
                    if (po.ToString() == premisesNew)
                    {
                        if (po > 100)
                        {
                            newPremisesOld = po.ToString();
                            newPremisesNew = string.Empty;
                        }
                        else
                        {
                            newPremisesOld = string.Empty;
                            newPremisesNew = po.ToString();
                        }
                    }
                    else
                    {
                        if (premisesNew.Length > po.ToString().Length)
                        {
                            newPremisesOld = premisesNew;
                            newPremisesNew = po.ToString();
                        }
                        else
                        {
                            newPremisesOld = po.ToString();
                            newPremisesNew = premisesNew;
                        }
                    }
                }
                else if (!int.TryParse(premisesOld, out po) && int.TryParse(premisesNew, out pn))
                {
                    // if equal then one is invalid
                    if (premisesOld == pn.ToString())
                    {
                        if (premisesOld.Length >= 3)
                        {
                            newPremisesOld = premisesOld.ToString();
                            newPremisesNew = string.Empty;
                        }
                        else
                        {
                            newPremisesOld = string.Empty;
                            newPremisesNew = premisesOld;
                        }
                    }
                    else
                    {
                        if (pn.ToString().Length > premisesOld.Length)
                        {
                            newPremisesOld = pn.ToString();
                            newPremisesNew = premisesOld;
                        }
                        else
                        {
                            newPremisesOld = premisesOld;
                            newPremisesNew = pn.ToString();
                        }
                    }
                }
                else //if (!int.TryParse(premisesOld, out po) && !int.TryParse(premisesNew, out pn))
                {
                    // if equal then one is invalid
                    if (premisesOld == premisesNew)
                    {
                        if (premisesOld.Length >= 3)
                        {
                            newPremisesOld = premisesOld;
                            newPremisesNew = string.Empty;
                        }
                        else
                        {
                            newPremisesOld = string.Empty;
                            newPremisesNew = premisesOld;
                        }
                    }
                    else
                    {
                        if (premisesNew.Length > premisesOld.Length)
                        {
                            newPremisesOld = premisesNew;
                            newPremisesNew = premisesOld;
                        }
                        else
                        {
                            newPremisesOld = premisesOld;
                            newPremisesNew = premisesNew;
                        }
                    }
                }
            }
            else if (!string.IsNullOrEmpty(premisesOld) && string.IsNullOrEmpty(premisesNew))
            {
                if (premisesOld.Length >= 3)
                {
                    newPremisesOld = premisesOld;
                    newPremisesNew = string.Empty;
                }
                else
                {
                    newPremisesOld = string.Empty;
                    newPremisesNew = premisesOld;
                }
            }
            else if (string.IsNullOrEmpty(premisesOld) && !string.IsNullOrEmpty(premisesNew))
            {
                if (premisesNew.Length < 3)
                {
                    newPremisesOld = string.Empty;
                    newPremisesNew = premisesNew;
                }
                else
                {
                    newPremisesOld = premisesNew;
                    newPremisesNew = string.Empty;
                }
            }
            else //if (string.IsNullOrEmpty(premisesOld) && string.IsNullOrEmpty(premisesNew))
            {
                newPremisesOld = string.Empty;
                newPremisesNew = string.Empty;
            }
        }
 
        ////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Access> AccessesWithNoServiceRequestOntsAndUpdatedWithinLastNDaysRandomNList(int pastDays, int take)
        {
            List<Ia.Ngn.Cl.Model.Access> list;
 
            var pastDateTime = DateTime.UtcNow.AddHours(3).AddDays(-pastDays);
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                list = (from a in db.Accesses
                        join sro in db.ServiceRequestOnts on a.Id equals sro.Access.Id
                        into pg
                        from p in pg.DefaultIfEmpty()
                        where p == null && a.Updated >= pastDateTime
                        select a).AsNoTracking().ToList();
            }
 
            return list.PickRandom(take).ToList();
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Access> AccessesWithNoServiceRequestOntsAndUpdatedTakeN(int take)
        {
            List<Ia.Ngn.Cl.Model.Access> list;
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                list = (from a in db.Accesses
                        join sro in db.ServiceRequestOnts on a.Id equals sro.Access.Id
                        into pg
                        from p in pg.DefaultIfEmpty()
                        where p == null
                        select a).OrderByDescending(u => u.Updated).Take(take).ToList();
            }
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Access> AccessesWithNoServiceRequestOntsRandomNList(int take)
        {
            List<Ia.Ngn.Cl.Model.Access> list;
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                list = (from a in db.Accesses
                        join sro in db.ServiceRequestOnts on a.Id equals sro.Access.Id
                        into pg
                        from p in pg.DefaultIfEmpty()
                        where p == null
                        select a).AsNoTracking().ToList();
            }
 
            return list.PickRandom(take).ToList();
        }
 
        ////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static string ToSimpleTextString(Ia.Ngn.Cl.Model.Access access)
        {
            StringBuilder sb;
            Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor vendor;
 
            sb = new StringBuilder();
 
            vendor = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.AccessVendorByOntAccessId(access.Id);
            //vendor = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntList where o.Access.Id == access.Id select o.Pon.PonGroup.Olt.Odf.Vendor).SingleOrDefault();
 
            sb.AppendLine("Vendor: " + vendor.Name);
            sb.AppendLine("Name: " + access.Name);
            sb.AppendLine("Address: " + access.Address);
 
            if (!string.IsNullOrEmpty(access.Paci)) sb.AppendLine("Paci: " + access.Paci);
            if (!string.IsNullOrEmpty(access.Note)) sb.AppendLine("Note: " + access.Note);
 
            return sb.ToString();
        }
 
        ////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////
    }
 
    ////////////////////////////////////////////////////////////////////////////
    ////////////////////////////////////////////////////////////////////////////
}