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

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » Administration

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

Administration support class for Optical Fiber Network (OFN) data model.

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Data;
   4:  using System.Globalization;
   5:  using System.IO;
   6:  using System.Linq;
   7:  using System.Reflection;
   8:  using System.Web;
   9:  using System.Xml.Linq;
  10:   
  11:  namespace Ia.Ngn.Cl.Model.Data
  12:  {
  13:      ////////////////////////////////////////////////////////////////////////////
  14:   
  15:      /// <summary publish="true">
  16:      /// Administration support class for Optical Fiber Network (OFN) data model.
  17:      /// </summary>
  18:      /// 
  19:      /// <remarks> 
  20:      /// Copyright © 2006-2021 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
  21:      ///
  22:      /// 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
  23:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  24:      ///
  25:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  26:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  27:      /// 
  28:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  29:      /// 
  30:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  31:      /// </remarks> 
  32:      public class Administration
  33:      {
  34:          private static XDocument xDocument;
  35:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Framework> frameworkList;
  36:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Category> categoryList;
  37:          private static List<Ia.Ngn.Cl.Model.Business.Administration.StaffFramework> staffFrameworkList;
  38:          private static List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact> staffContactList;
  39:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea> statisticList;
  40:   
  41:          private static readonly object objectLock = new object();
  42:   
  43:          ////////////////////////////////////////////////////////////////////////////
  44:   
  45:          /// <summary>
  46:          ///
  47:          /// </summary>
  48:          public Administration() { }
  49:   
  50:          ////////////////////////////////////////////////////////////////////////////
  51:   
  52:          /// <summary>
  53:          ///
  54:          /// </summary>
  55:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Framework> FrameworkList
  56:          {
  57:              get
  58:              {
  59:                  if (frameworkList == null || frameworkList.Count == 0)
  60:                  {
  61:                      lock (objectLock)
  62:                      {
  63:                          frameworkList = Ia.Ngn.Cl.Model.Data.Administration._FrameworkList();
  64:                      }
  65:                  }
  66:   
  67:                  return frameworkList;
  68:              }
  69:          }
  70:   
  71:          ////////////////////////////////////////////////////////////////////////////
  72:   
  73:          /// <summary>
  74:          ///
  75:          /// </summary>
  76:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Framework> _FrameworkList()
  77:          {
  78:              int id, parentId;
  79:              string arabicName, coloredArabicName, siteNameList;
  80:              Ia.Ngn.Cl.Model.Business.Administration.Framework framework;
  81:   
  82:              var frameworkList = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>(Ia.Ngn.Cl.Model.Business.Administration.InitialFrameworkListLength); // needed to prevent errors
  83:   
  84:              var lightBackgroundColorList = Ia.Ngn.Cl.Model.Ui.Default.LightBackgroundColorList;
  85:   
  86:              foreach (XElement xe in XDocument.Element("administration").Element("frameworkList").Descendants("framework"))
  87:              {
  88:                  id = int.Parse(XmlBasedTwoDigitPerId(xe));
  89:                  parentId = Ia.Ngn.Cl.Model.Business.Administration.Framework.ParentId(id);
  90:                  arabicName = xe.Attribute("arabicName").Value;
  91:                  coloredArabicName = @"<span style=""color:" + lightBackgroundColorList[id % lightBackgroundColorList.Count] + @""">" + arabicName + "</span>";
  92:   
  93:                  framework = new Ia.Ngn.Cl.Model.Business.Administration.Framework();
  94:   
  95:                  framework.Id = id;
  96:                  framework.Level = xe.Ancestors().Count();
  97:                  framework.Guid = Guid.Parse(xe.Attribute("guid").Value);
  98:                  framework.Type = xe.Attribute("type").Value;
  99:                  framework.Name = xe.Attribute("name").Value;
 100:                  framework.ArabicName = arabicName;
 101:   
 102:                  if (xe.Attribute("phone") != null && !string.IsNullOrEmpty(xe.Attribute("phone").Value)) framework.Phone = xe.Attribute("phone").Value;
 103:                  else framework.Phone = string.Empty;
 104:   
 105:                  framework.ColoredArabicName = coloredArabicName;
 106:   
 107:                  if (xe.Attribute("siteNameList") != null && !string.IsNullOrEmpty(xe.Attribute("siteNameList").Value))
 108:                  {
 109:                      siteNameList = xe.Attribute("siteNameList").Value;
 110:                      framework.Sites = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where siteNameList.Contains(s.Name) select s).ToList();
 111:                  }
 112:                  else if (xe.Parent.Attribute("siteNameList") != null && !string.IsNullOrEmpty(xe.Parent.Attribute("siteNameList").Value))
 113:                  {
 114:                      siteNameList = xe.Parent.Attribute("siteNameList").Value;
 115:                      framework.Sites = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where siteNameList.Contains(s.Name) select s).ToList();
 116:                  }
 117:                  else framework.Sites = new List<Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site>();
 118:   
 119:                  frameworkList.Add(framework);
 120:              }
 121:   
 122:              // Parents, authorities and children
 123:              foreach (Ia.Ngn.Cl.Model.Business.Administration.Framework f in frameworkList)
 124:              {
 125:                  parentId = Ia.Ngn.Cl.Model.Business.Administration.Framework.ParentId(f.Id);
 126:   
 127:                  f.Parent = (from _f in frameworkList where _f.Id == parentId select _f).SingleOrDefault();
 128:   
 129:                  f.Children = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>();
 130:   
 131:                  if (f.Parent != null) f.Parent.Children.Add(f);
 132:              }
 133:   
 134:              // Children or self
 135:              foreach (var f in frameworkList)
 136:              {
 137:                  f.ChildrenOrSelf = (from _f in frameworkList where _f.Id == f.Id select _f).Union(f.Children).ToList();
 138:              }
 139:   
 140:              // Siblings
 141:              foreach (var f in frameworkList.ToList())
 142:              {
 143:                  //f.Siblings = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>();
 144:   
 145:                  f.Siblings = (from _f in frameworkList where _f.Parent == f.Parent && _f.Id != f.Id select _f).ToList();
 146:   
 147:                  f.SiblingsOrSelf = (from _f in frameworkList where _f.Id == f.Id select _f).Union(f.Siblings).ToList();
 148:              }
 149:   
 150:              // Descendants
 151:              foreach (var f in frameworkList.ToList())
 152:              {
 153:                  //f.Descendants = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>();
 154:   
 155:                  f.Descendants = (from _f in frameworkList
 156:                                   where _f.Id != f.Id && _f.Id > f.Id &&
 157:                                       (_f.Parent != null && (_f.Parent == f
 158:                                       || _f.Parent.Parent != null && (_f.Parent.Parent == f
 159:                                       || _f.Parent.Parent.Parent != null && (_f.Parent.Parent.Parent == f
 160:                                       || _f.Parent.Parent.Parent.Parent != null && _f.Parent.Parent.Parent.Parent == f)))
 161:                                       )
 162:                                   select _f).ToList();
 163:   
 164:                  f.DescendantsOrSelf = (from _f in frameworkList where _f.Id == f.Id select _f).Union(f.Descendants).ToList();
 165:              }
 166:   
 167:              // Ancestors
 168:              foreach (var f in frameworkList.ToList())
 169:              {
 170:                  //f.Ancestors = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>();
 171:   
 172:                  f.Ancestors = (from _f in frameworkList
 173:                                 where _f.Id != f.Id && _f.Id < f.Id &&
 174:                                     (f.Parent != null && (f.Parent == _f
 175:                                     || f.Parent.Parent != null && (f.Parent.Parent == _f
 176:                                     || f.Parent.Parent.Parent != null && (f.Parent.Parent.Parent == _f
 177:                                     || f.Parent.Parent.Parent.Parent != null && f.Parent.Parent.Parent.Parent == _f)))
 178:                                     )
 179:                                 select _f).ToList();
 180:   
 181:                  f.AncestorsOrSelf = (from _f in frameworkList where _f.Id == f.Id select _f).Union(f.Ancestors).ToList();
 182:              }
 183:   
 184:              return frameworkList.ToList();
 185:          }
 186:   
 187:          ////////////////////////////////////////////////////////////////////////////
 188:   
 189:          /// <summary>
 190:          ///
 191:          /// </summary>
 192:          private static string XmlBasedOneDigitPerId(XElement xeIn)
 193:          {
 194:              return XmlBasedId(xeIn, 1);
 195:          }
 196:   
 197:          ////////////////////////////////////////////////////////////////////////////
 198:   
 199:          /// <summary>
 200:          ///
 201:          /// </summary>
 202:          private static string XmlBasedTwoDigitPerId(XElement xeIn)
 203:          {
 204:              return XmlBasedId(xeIn, 2);
 205:          }
 206:   
 207:          ////////////////////////////////////////////////////////////////////////////
 208:   
 209:          /// <summary>
 210:          ///
 211:          /// </summary>
 212:          private static string XmlBasedId(XElement xe, int digit)
 213:          {
 214:              string id;
 215:   
 216:              id = string.Empty;
 217:   
 218:              while (xe.HasAttributes && xe.Attribute("id") != null)
 219:              {
 220:                  id = xe.Attribute("id").Value.PadLeft(digit, '0') + id;
 221:                  xe = xe.Parent;
 222:              }
 223:   
 224:              return id;
 225:          }
 226:   
 227:          ////////////////////////////////////////////////////////////////////////////
 228:   
 229:          /// <summary>
 230:          ///
 231:          /// </summary>
 232:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Category> CategoryList
 233:          {
 234:              get
 235:              {
 236:                  if (categoryList == null || categoryList.Count == 0)
 237:                  {
 238:                      lock (objectLock)
 239:                      {
 240:                          categoryList = Ia.Ngn.Cl.Model.Data.Administration._CategoryList;
 241:                      }
 242:                  }
 243:   
 244:                  return categoryList;
 245:              }
 246:          }
 247:   
 248:          ////////////////////////////////////////////////////////////////////////////
 249:   
 250:          /// <summary>
 251:          ///
 252:          /// </summary>
 253:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Category> _CategoryList
 254:          {
 255:              get
 256:              {
 257:                  Ia.Ngn.Cl.Model.Business.Administration.Category category;
 258:   
 259:                  var categoryList = new List<Ia.Ngn.Cl.Model.Business.Administration.Category>();
 260:   
 261:                  foreach (XElement xe in XDocument.Element("administration").Elements("category"))
 262:                  {
 263:                      category = new Ia.Ngn.Cl.Model.Business.Administration.Category()
 264:                      {
 265:                          Name = xe.Attribute("name").Value,
 266:                          Regex = xe.Attribute("regex").Value,
 267:                          Description = (xe.Attribute("description") != null) ? xe.Attribute("description").Value : string.Empty,
 268:                          Color = (xe.Attribute("color") != null) ? xe.Attribute("color").Value : string.Empty
 269:                      };
 270:   
 271:                      categoryList.Add(category);
 272:                  }
 273:   
 274:                  return categoryList.ToList();
 275:              }
 276:          }
 277:   
 278:          ////////////////////////////////////////////////////////////////////////////
 279:   
 280:          /// <summary>
 281:          ///
 282:          /// </summary>
 283:          public static List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact> StaffContactList
 284:          {
 285:              get
 286:              {
 287:                  if (staffContactList == null || staffContactList.Count == 0)
 288:                  {
 289:                      lock (objectLock)
 290:                      {
 291:                          staffContactList = Ia.Ngn.Cl.Model.Data.Administration._StaffContactList;
 292:                      }
 293:                  }
 294:   
 295:                  return staffContactList;
 296:              }
 297:          }
 298:   
 299:          ////////////////////////////////////////////////////////////////////////////
 300:   
 301:          /// <summary>
 302:          ///
 303:          /// </summary>
 304:          private static List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact> _StaffContactList
 305:          {
 306:              get
 307:              {
 308:                  var staffContactList = new List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact>();
 309:   
 310:                  foreach (var staff in Ia.Ngn.Cl.Model.Data.Staff.List)
 311:                  {
 312:                      if (staff.User != null && staff.User.Email != null)
 313:                      {
 314:                          var staffContact = new Ia.Ngn.Cl.Model.Business.Administration.StaffContact() { Staff = staff };
 315:   
 316:                          staffContactList.Add(staffContact);
 317:                      }
 318:                  }
 319:   
 320:                  foreach (var contact in Ia.Ngn.Cl.Model.Data.Contact.List)
 321:                  {
 322:                      if (contact.Email != null)
 323:                      {
 324:                          var staffContact = new Ia.Ngn.Cl.Model.Business.Administration.StaffContact() { Contact = contact };
 325:   
 326:                          staffContactList.Add(staffContact);
 327:                      }
 328:                  }
 329:   
 330:                  return staffContactList.ToList();
 331:              }
 332:          }
 333:   
 334:          ////////////////////////////////////////////////////////////////////////////
 335:   
 336:          /// <summary>
 337:          ///
 338:          /// </summary>
 339:          public static List<Ia.Ngn.Cl.Model.Business.Administration.StaffFramework> StaffFrameworkList
 340:          {
 341:              get
 342:              {
 343:                  if (staffFrameworkList == null || staffFrameworkList.Count == 0)
 344:                  {
 345:                      lock (objectLock)
 346:                      {
 347:                          staffFrameworkList = Ia.Ngn.Cl.Model.Data.Administration._StaffFrameworkList;
 348:                      }
 349:                  }
 350:   
 351:                  return staffFrameworkList;
 352:              }
 353:          }
 354:   
 355:          ////////////////////////////////////////////////////////////////////////////
 356:   
 357:          /// <summary>
 358:          ///
 359:          /// </summary>
 360:          private static List<Ia.Ngn.Cl.Model.Business.Administration.StaffFramework> _StaffFrameworkList
 361:          {
 362:              get
 363:              {
 364:                  var staffList = (from s in Ia.Ngn.Cl.Model.Data.Staff.List
 365:                                   select new Ia.Ngn.Cl.Model.Business.Administration.StaffFramework
 366:                                   {
 367:                                       Guid = s.UserId,
 368:                                       FrameworkId = s.Framework.Id,
 369:                                       IsStaff = true,
 370:                                       Name = s.FirstAndMiddleName
 371:                                   }
 372:                  ).ToList();
 373:   
 374:                  var frameworkList = (from f in Ia.Ngn.Cl.Model.Data.Administration.FrameworkList
 375:                                       select new Ia.Ngn.Cl.Model.Business.Administration.StaffFramework
 376:                                       {
 377:                                           Guid = f.Guid,
 378:                                           FrameworkId = f.Id,
 379:                                           IsFramework = true,
 380:                                           Name = f.FullyQualifiedArabicName //.ArabicName
 381:                                       }
 382:                  ).ToList();
 383:   
 384:                  staffFrameworkList = staffList.Union(frameworkList).ToList();
 385:   
 386:                  return staffFrameworkList.ToList();
 387:              }
 388:          }
 389:   
 390:          ////////////////////////////////////////////////////////////////////////////
 391:          ////////////////////////////////////////////////////////////////////////////
 392:   
 393:          /// <summary>
 394:          ///
 395:          /// </summary>
 396:          public static int ServiceRequestServicesWithNullAccessCount()
 397:          {
 398:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 399:              {
 400:                  return (from srs in db.ServiceRequestServices
 401:                          where srs.Access == null
 402:                          select srs.Id).Count();
 403:              }
 404:          }
 405:   
 406:          ////////////////////////////////////////////////////////////////////////////
 407:   
 408:          /// <summary>
 409:          ///
 410:          /// </summary>
 411:          public static int ServicesWithNullAccessCount()
 412:          {
 413:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 414:              {
 415:                  return (from s in db.Service2
 416:                          where s.ServiceType == Ia.Ngn.Cl.Model.Business.Service.ServiceType.ImsService && s.Access == null
 417:                          select s.Id).Count();
 418:              }
 419:          }
 420:   
 421:          ////////////////////////////////////////////////////////////////////////////
 422:   
 423:          /// <summary>
 424:          ///
 425:          /// </summary>
 426:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea> ServiceAccessStatisticWithinKuwaitNgnAreaCachedDaily
 427:          {
 428:              get
 429:              {
 430:                  if (statisticList == null || statisticList.Count == 0)
 431:                  {
 432:                      if (HttpContext.Current != null && HttpContext.Current.Application["statisticList" + DateTime.Now.Day] != null)
 433:                      {
 434:                          statisticList = HttpContext.Current.Application["statisticList" + DateTime.Now.Day] as List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea>;
 435:                      }
 436:                      else
 437:                      {
 438:                          lock (objectLock)
 439:                          {
 440:                              statisticList = null;
 441:                              statisticList = Ia.Ngn.Cl.Model.Data.Administration.ServiceAccessStatisticWithinKuwaitNgnArea();
 442:   
 443:                              if (HttpContext.Current != null) HttpContext.Current.Application["statisticList" + DateTime.Now.Day] = statisticList;
 444:                          }
 445:                      }
 446:                  }
 447:   
 448:                  return statisticList;
 449:              }
 450:          }
 451:   
 452:          ////////////////////////////////////////////////////////////////////////////
 453:   
 454:          /// <summary>
 455:          ///
 456:          /// </summary>
 457:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea> ServiceAccessStatisticWithinKuwaitNgnArea()
 458:          {
 459:              if (statisticList == null || statisticList.Count == 0)
 460:              {
 461:                  int fourDigitDomain, fiveDigitDomain;
 462:                  int totalService, totalNokiaService, totalHuaweiService, totalServiceRequestService, totalAccessCapacity, totalNokiaOnt, totalHuaweiOnt, totalNokiaAccess, totalHuaweiAccess, totalAccessReady, totalAccessUsed;
 463:                  string accessId;
 464:                  Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor vendor;
 465:                  Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea kuwaitAreaStatistic;
 466:                  Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Olt olt;
 467:   
 468:                  statisticList = new List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea>();
 469:                  statisticList.Clear();
 470:   
 471:                  totalService = totalNokiaService = totalHuaweiService = totalServiceRequestService = totalAccessCapacity = totalNokiaOnt = totalHuaweiOnt = totalNokiaAccess = totalHuaweiAccess = totalAccessReady = totalAccessUsed = totalService = totalServiceRequestService = 0;
 472:   
 473:                  //kuwaitNgnAreaList = new List<Ia.Ngn.Cl.Model.Business.Service.KuwaitNgnArea>(); // I need to clear it because I add an item in it below
 474:                  var kuwaitNgnAreaList = Ia.Ngn.Cl.Model.Data.Service.KuwaitOfnAreaList;
 475:   
 476:                  var ontAccessIdToSeviceListDictionary = Ia.Ngn.Cl.Model.Data.Service2.OntAccessIdToSeviceListDictionary();
 477:   
 478:                  var ontList = Ia.Ngn.Cl.Model.Data.Nokia.Ont.ListIncludeAccess();
 479:                  var emsOntList = Ia.Ngn.Cl.Model.Data.Huawei.Ont.ListIncludeAccess();
 480:                  var accessList = Ia.Ngn.Cl.Model.Data.Access.List();
 481:                  var accessIdToOltIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToOltIdDictionary;
 482:   
 483:                  var accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 484:   
 485:                  var serviceRequestServiceProvisionedServiceIdToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ProvisionedServiceIdToAccessIdDictionary;
 486:                  var serviceIdToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceIdToAccessIdDictionary;
 487:   
 488:                  var accessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.AccessWithProvisionedAndReadyOntIdToKuwaitNgnAreaIdDictionary;
 489:   
 490:                  var domainToRouterVendorDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.DomainToRouterVendorDictionary;
 491:   
 492:                  var unknown = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea("Unknown", "غير معرف");
 493:   
 494:                  //ontIdToOltIdDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntIdToOltIdDictionary;
 495:   
 496:                  // - ONT capacity, usage, # of services in an ONT, type of ONT
 497:                  // - Graphics
 498:                  /*
 499:                  update statistics
 500:      Vendor,
 501:      number perfix,
 502:      4 in ONT,
 503:      0 in ONT,
 504:      Acces: ONT used by subscribers
 505:      */
 506:   
 507:                  foreach (var kna in kuwaitNgnAreaList)
 508:                  {
 509:                      kuwaitAreaStatistic = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea();
 510:   
 511:                      kuwaitAreaStatistic.Name = kna.NameArabicName;
 512:   
 513:                      kuwaitAreaStatistic.Symbol = kna.Symbol;
 514:   
 515:                      kuwaitAreaStatistic.ServiceRequestService = 0;
 516:   
 517:                      kuwaitAreaStatistic.NokiaService = 0;
 518:                      kuwaitAreaStatistic.HuaweiService = 0;
 519:                      kuwaitAreaStatistic.Service = 0;
 520:   
 521:                      foreach (KeyValuePair<string, string> kvp in serviceIdToAccessIdDictionary)
 522:                      {
 523:                          accessId = kvp.Value;
 524:   
 525:                          if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
 526:                          {
 527:                              if (accessIdToKuwaitNgnAreaIdDictionary[accessId] == kna.Id)
 528:                              {
 529:                                  kuwaitAreaStatistic.Service++;
 530:   
 531:                                  fiveDigitDomain = int.Parse(kvp.Key.Substring(0, 5));
 532:   
 533:                                  if (domainToRouterVendorDictionary.ContainsKey(fiveDigitDomain)) vendor = domainToRouterVendorDictionary[fiveDigitDomain];
 534:                                  else
 535:                                  {
 536:                                      fourDigitDomain = int.Parse(kvp.Key.Substring(0, 4));
 537:   
 538:                                      if (domainToRouterVendorDictionary.ContainsKey(fourDigitDomain))
 539:                                      {
 540:                                          vendor = domainToRouterVendorDictionary[fourDigitDomain];
 541:                                      }
 542:                                      else vendor = null;
 543:                                  }
 544:   
 545:                                  if (vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Nokia) kuwaitAreaStatistic.NokiaService++;
 546:                                  else if (vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Huawei) kuwaitAreaStatistic.HuaweiService++;
 547:                                  else
 548:                                  {
 549:                                      unknown.Service++;
 550:   
 551:                                      //throw new Exception("Vendor is unknown");
 552:                                  }
 553:   
 554:                              }
 555:                          }
 556:                      }
 557:   
 558:                      kuwaitAreaStatistic.NokiaHuaweiService = (kuwaitAreaStatistic.NokiaService + kuwaitAreaStatistic.HuaweiService) + " (" + kuwaitAreaStatistic.NokiaService + "/" + kuwaitAreaStatistic.HuaweiService + ")";
 559:   
 560:   
 561:                      foreach (KeyValuePair<string, string> kvp in serviceRequestServiceProvisionedServiceIdToAccessIdDictionary)
 562:                      {
 563:                          if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(kvp.Value))
 564:                          {
 565:                              if (accessIdToKuwaitNgnAreaIdDictionary[kvp.Value] == kna.Id)
 566:                              {
 567:                                  kuwaitAreaStatistic.ServiceRequestService++;
 568:                              }
 569:                          }
 570:                      }
 571:   
 572:                      //statistic.SiteAccessCapacity = kna.Site.Routers.SelectMany(u => u.Odfs.SelectMany(y => y.Olts)).Count() * 1024;
 573:                      //statistic.SiteAccessCapacity = f.Sum(u => kna.Site.Routers.Any(u => u.Odfs.Any(y => y.Olts.Any(z => z.Id == u.Value)))) * 1024;
 574:                      //kuwaitAreaStatistic.AccessProvisioned = accessIdToKuwaitNgnAreaIdDictionary.Count(u => u.Value == kna.Id);
 575:                      //kuwaitAreaStatistic.AccessUtilized = 0;
 576:   
 577:                      // below: this does not include SSR accesses for area
 578:                      kuwaitAreaStatistic.AccessCapacity = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
 579:                                                            where o.Symbol == kna.Symbol
 580:                                                            select o).Sum(o => o.NumberOfPons * o.NumberOfOntsInPon);
 581:   
 582:                      kuwaitAreaStatistic.NokiaOnt = 0;
 583:                      kuwaitAreaStatistic.HuaweiOnt = 0;
 584:   
 585:                      foreach (var o in ontList)
 586:                      {
 587:                          if (o.Access != null)
 588:                          {
 589:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(o.Access.Id))
 590:                              {
 591:                                  if (accessIdToKuwaitNgnAreaIdDictionary[o.Access.Id] == kna.Id)
 592:                                  {
 593:                                      kuwaitAreaStatistic.NokiaOnt++;
 594:                                  }
 595:                              }
 596:                          }
 597:                          else
 598:                          {
 599:                          }
 600:                      }
 601:   
 602:                      foreach (var o in emsOntList)
 603:                      {
 604:                          if (o.Access != null)
 605:                          {
 606:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(o.Access.Id))
 607:                              {
 608:                                  if (accessIdToKuwaitNgnAreaIdDictionary[o.Access.Id] == kna.Id)
 609:                                  {
 610:                                      kuwaitAreaStatistic.HuaweiOnt++;
 611:                                  }
 612:                              }
 613:                          }
 614:                          else
 615:                          {
 616:                          }
 617:                      }
 618:   
 619:                      kuwaitAreaStatistic.NokiaHuaweiOnt = (kuwaitAreaStatistic.NokiaOnt + kuwaitAreaStatistic.HuaweiOnt) + " (" + kuwaitAreaStatistic.NokiaOnt + "/" + kuwaitAreaStatistic.HuaweiOnt + ")";
 620:   
 621:                      kuwaitAreaStatistic.NokiaAccess = 0;
 622:                      kuwaitAreaStatistic.HuaweiAccess = 0;
 623:   
 624:                      foreach (var a in accessList)
 625:                      {
 626:                          if (accessIdToKuwaitNgnAreaIdDictionary[a.Id] == kna.Id)
 627:                          {
 628:                              olt = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
 629:                                     where o.Id == accessIdToOltIdDictionary[a.Id]
 630:                                     select o).SingleOrDefault();
 631:   
 632:                              if (olt != null)
 633:                              {
 634:                                  // <vendor id="1" name="Nokia" shortName="No" ... />
 635:                                  if (olt.Odf.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Nokia) kuwaitAreaStatistic.NokiaAccess++;
 636:   
 637:                                  // <vendor id="2" name="Huawei" shortName="Hu" ... />
 638:                                  else if (olt.Odf.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Huawei) kuwaitAreaStatistic.HuaweiAccess++;
 639:                              }
 640:                              else
 641:                              {
 642:   
 643:                              }
 644:                          }
 645:                      }
 646:   
 647:                      kuwaitAreaStatistic.NokiaHuaweiAccess = (kuwaitAreaStatistic.NokiaAccess + kuwaitAreaStatistic.HuaweiAccess) + " (" + kuwaitAreaStatistic.NokiaAccess + "/" + kuwaitAreaStatistic.HuaweiAccess + ")";
 648:   
 649:   
 650:                      kuwaitAreaStatistic.AccessReady = 0;
 651:                      kuwaitAreaStatistic.AccessUsed = 0;
 652:   
 653:                      foreach (var a in accessList)
 654:                      {
 655:                          if (accessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary.ContainsKey(a.Id))
 656:                          {
 657:                              if (accessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary[a.Id] == kna.Id)
 658:                              {
 659:                                  kuwaitAreaStatistic.AccessReady++;
 660:   
 661:                                  if (ontAccessIdToSeviceListDictionary[a.Id].Count > 0) kuwaitAreaStatistic.AccessUsed++;
 662:                              }
 663:                          }
 664:                      }
 665:   
 666:                      kuwaitAreaStatistic.AccessReadyUsed = kuwaitAreaStatistic.AccessReady + "/" + kuwaitAreaStatistic.AccessUsed;
 667:   
 668:   
 669:                      // totals:
 670:                      totalService += kuwaitAreaStatistic.Service;
 671:   
 672:                      totalNokiaService += kuwaitAreaStatistic.NokiaService;
 673:                      totalHuaweiService += kuwaitAreaStatistic.HuaweiService;
 674:   
 675:                      totalServiceRequestService += kuwaitAreaStatistic.ServiceRequestService;
 676:   
 677:                      totalAccessCapacity += kuwaitAreaStatistic.AccessCapacity;
 678:   
 679:                      totalNokiaOnt += kuwaitAreaStatistic.NokiaOnt;
 680:                      totalHuaweiOnt += kuwaitAreaStatistic.HuaweiOnt;
 681:   
 682:                      totalNokiaAccess += kuwaitAreaStatistic.NokiaAccess;
 683:                      totalHuaweiAccess += kuwaitAreaStatistic.HuaweiAccess;
 684:   
 685:                      totalAccessReady += kuwaitAreaStatistic.AccessReady;
 686:                      totalAccessUsed += kuwaitAreaStatistic.AccessUsed;
 687:   
 688:                      statisticList.Add(kuwaitAreaStatistic);
 689:                  }
 690:   
 691:                  statisticList = statisticList.OrderBy(u => u.Name).ToList();
 692:   
 693:                  statisticList.Add(unknown);
 694:   
 695:                  // totals:
 696:                  kuwaitAreaStatistic = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea();
 697:   
 698:                  kuwaitAreaStatistic.Name = "Total";
 699:   
 700:                  kuwaitAreaStatistic.Service = totalService;
 701:                  kuwaitAreaStatistic.NokiaService = totalNokiaService;
 702:                  kuwaitAreaStatistic.HuaweiService = totalHuaweiService;
 703:                  kuwaitAreaStatistic.NokiaHuaweiService = (kuwaitAreaStatistic.NokiaService + kuwaitAreaStatistic.HuaweiService) + " (" + kuwaitAreaStatistic.NokiaService + "/" + kuwaitAreaStatistic.HuaweiService + ")";
 704:   
 705:   
 706:                  kuwaitAreaStatistic.ServiceRequestService = totalServiceRequestService;
 707:   
 708:                  kuwaitAreaStatistic.AccessCapacity = totalAccessCapacity;
 709:   
 710:                  kuwaitAreaStatistic.NokiaOnt = totalNokiaOnt;
 711:                  kuwaitAreaStatistic.HuaweiOnt = totalHuaweiOnt;
 712:                  kuwaitAreaStatistic.NokiaHuaweiOnt = (totalNokiaOnt + totalHuaweiOnt) + " (" + totalNokiaOnt + "/" + totalHuaweiOnt + ")";
 713:   
 714:                  kuwaitAreaStatistic.NokiaAccess = totalNokiaAccess;
 715:                  kuwaitAreaStatistic.HuaweiAccess = totalHuaweiAccess;
 716:                  kuwaitAreaStatistic.NokiaHuaweiAccess = (totalNokiaAccess + totalHuaweiAccess) + " (" + totalNokiaAccess + "/" + totalHuaweiAccess + ")";
 717:   
 718:                  kuwaitAreaStatistic.AccessReady = totalAccessReady;
 719:                  kuwaitAreaStatistic.AccessUsed = totalAccessUsed;
 720:                  kuwaitAreaStatistic.AccessReadyUsed = totalAccessReady + "/" + totalAccessUsed;
 721:   
 722:                  statisticList.Add(kuwaitAreaStatistic);
 723:              }
 724:   
 725:              return statisticList;
 726:          }
 727:   
 728:          ////////////////////////////////////////////////////////////////////////////
 729:   
 730:          /// <summary>
 731:          ///
 732:          /// </summary>
 733:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site> SiteStatistic()
 734:          {
 735:              int siteId;
 736:              int totalFiberAccessCapacity, totalFiberServiceCapacity, totalMsanServiceCapacity, totalFiberService, totalNokiaService, totalHuaweiService, totalService;
 737:              int totalMsanService, totalPstnService, totalEricssonService, totalSiemensService;
 738:              Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site siteStatistic;
 739:              List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site> siteStatisticList;
 740:   
 741:              siteStatisticList = new List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site>();
 742:   
 743:              totalFiberService = totalNokiaService = totalHuaweiService = totalFiberAccessCapacity = totalFiberServiceCapacity = totalMsanServiceCapacity = 0;
 744:              totalMsanService = totalPstnService = totalEricssonService = totalSiemensService = 0;
 745:              totalService = 0;
 746:   
 747:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 748:              {
 749:                  var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
 750:                  var oltList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList;
 751:   
 752:                  var msanSiteIdToDidListDictionary = Ia.Ngn.Cl.Model.Data.Huawei.Dev.MsanSiteIdToDidListDictionary();
 753:                  var msanSiteIdToCapacityDictionary = Ia.Ngn.Cl.Model.Data.Huawei.Dev.MsanSiteIdToCapacityDictionary();
 754:                  var msanDidToServiceCountDictionary = Ia.Ngn.Cl.Model.Data.Default.MsanDidToMsanServiceCountDictionary();
 755:   
 756:                  var pstnFiveDigitDomainToCountOfServicesDictionary = Ia.Ngn.Cl.Model.Data.Service2.PstnFiveDigitDomainToCountOfServicesDictionary();
 757:                  var pstnToFiveDigitDomainListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.PstnToFiveDigitDomainListDictionary();
 758:   
 759:                  var unknown = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site(); // ("Unknown", );
 760:                  unknown.NameArabicName = "Unknown (غير معرف)";
 761:                  unknown.Name = "Unknown";
 762:   
 763:                  foreach (var site in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList)
 764:                  {
 765:                      siteStatistic = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site();
 766:   
 767:                      siteId = site.Id;
 768:                      siteStatistic.Id = site.Id.ToString();
 769:                      siteStatistic.NameArabicName = site.NameArabicName;
 770:                      siteStatistic.Name = site.Name;
 771:   
 772:                      siteStatistic.KuwaitAreaNameListString = string.Join(", ", site.KuwaitNgnAreas.Select(u => u.ArabicName));
 773:   
 774:                      siteStatistic.PstnDomainListString = string.Join(", ", site.Pstns.SelectMany(u => u.DomainList).ToList());
 775:   
 776:                      siteStatistic.MsanDomainListString = string.Join(", ", site.Msans.SelectMany(u => u.DomainList).ToList());
 777:   
 778:                      siteStatistic.DomainListString = string.Join(", ", site.Routers.SelectMany(u => u.DomainList).ToList());
 779:   
 780:                      siteStatistic.SymbolListString = string.Join(", ", site.AreaSymbolList.ToList());
 781:   
 782:                      siteStatistic.FiberAccessCapacity = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
 783:                                                           where o.Odf.Router.Site.Id == siteId
 784:                                                           select o).Sum(o => o.NumberOfPons * o.NumberOfOntsInPon);
 785:   
 786:                      siteStatistic.FiberServiceCapacity = site.NumberOfPossibleServicesWithinDomainList;
 787:   
 788:   
 789:                      // Fiber
 790:                      foreach (var olt in site.Routers.SelectMany(u => u.Odfs.SelectMany(v => v.Olts)))
 791:                      {
 792:                          if (oltIdToCountOfServiceDictionary.ContainsKey(olt.Id))
 793:                          {
 794:                              if (olt.Odf.Router.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Nokia) siteStatistic.NokiaService += oltIdToCountOfServiceDictionary[olt.Id];
 795:                              else if (olt.Odf.Router.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Huawei) siteStatistic.HuaweiService += oltIdToCountOfServiceDictionary[olt.Id];
 796:                              else
 797:                              {
 798:                                  unknown.FiberService++;
 799:                              }
 800:                          }
 801:                      }
 802:   
 803:                      siteStatistic.FiberService = siteStatistic.NokiaService + siteStatistic.HuaweiService;
 804:                      siteStatistic.FiberServiceText = siteStatistic.FiberService + " (" + siteStatistic.NokiaService + "/" + siteStatistic.HuaweiService + ")";
 805:   
 806:   
 807:                      // MSAN
 808:                      if (!string.IsNullOrEmpty(siteStatistic.MsanDomainListString))
 809:                      {
 810:                          if (msanSiteIdToDidListDictionary.ContainsKey(siteId))
 811:                          {
 812:                              siteStatistic.MsanServiceCapacity = msanSiteIdToCapacityDictionary[siteId];
 813:   
 814:                              foreach (var msanDid in msanSiteIdToDidListDictionary[siteId])
 815:                              {
 816:                                  if (msanDidToServiceCountDictionary.ContainsKey(msanDid))
 817:                                  {
 818:                                      siteStatistic.MsanService += msanDidToServiceCountDictionary[msanDid];
 819:                                  }
 820:                                  else
 821:                                  {
 822:                                      unknown.MsanService++;
 823:                                  }
 824:                              }
 825:                          }
 826:   
 827:                          siteStatistic.MsanServiceText = siteStatistic.MsanService.ToString();
 828:                      }
 829:   
 830:                      siteStatistic.Service = siteStatistic.FiberService + siteStatistic.MsanService;
 831:                      siteStatistic.ServiceText = siteStatistic.Service.ToString();
 832:   
 833:                      // PSTN
 834:                      if (!string.IsNullOrEmpty(siteStatistic.PstnDomainListString))
 835:                      {
 836:                          foreach (var pstn in site.Pstns)
 837:                          {
 838:                              var list = pstnToFiveDigitDomainListDictionary[pstn];
 839:   
 840:                              foreach (var l in list)
 841:                              {
 842:                                  if (pstnFiveDigitDomainToCountOfServicesDictionary.ContainsKey(l.ToString()))
 843:                                  {
 844:                                      if (pstn.PstnExchangeType == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.PstnExchangeType.EricssonAxe) siteStatistic.EricssonPstnService += pstnFiveDigitDomainToCountOfServicesDictionary[l.ToString()];
 845:                                      else if (pstn.PstnExchangeType == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.PstnExchangeType.SiemensEwsd) siteStatistic.SiemensPstnService += pstnFiveDigitDomainToCountOfServicesDictionary[l.ToString()];
 846:                                      else
 847:                                      {
 848:                                          unknown.PstnService++;
 849:                                      }
 850:                                  }
 851:                                  else
 852:                                  {
 853:   
 854:                                  }
 855:                              }
 856:                          }
 857:   
 858:                          siteStatistic.PstnService = siteStatistic.EricssonPstnService + siteStatistic.SiemensPstnService;
 859:                          siteStatistic.EricssonSiemensPstnServiceText = siteStatistic.PstnService + " (" + siteStatistic.EricssonPstnService + "/" + siteStatistic.SiemensPstnService + ")";
 860:                      }
 861:   
 862:   
 863:                      // totals:
 864:                      totalFiberService += siteStatistic.FiberService;
 865:                      totalService += siteStatistic.Service;
 866:   
 867:                      totalNokiaService += siteStatistic.NokiaService;
 868:                      totalHuaweiService += siteStatistic.HuaweiService;
 869:   
 870:                      totalFiberAccessCapacity += siteStatistic.FiberAccessCapacity;
 871:                      totalFiberServiceCapacity += siteStatistic.FiberServiceCapacity;
 872:   
 873:                      totalMsanServiceCapacity += siteStatistic.MsanServiceCapacity;
 874:                      totalMsanService += siteStatistic.MsanService;
 875:   
 876:                      totalPstnService += siteStatistic.PstnService;
 877:   
 878:                      totalEricssonService += siteStatistic.EricssonPstnService;
 879:                      totalSiemensService += siteStatistic.SiemensPstnService;
 880:   
 881:                      siteStatisticList.Add(siteStatistic);
 882:                  }
 883:   
 884:                  // MSAN: collect count of numbers in DIDs *not* within site DIDs
 885:                  var didList = msanSiteIdToDidListDictionary.Values.SelectMany(x => x).ToList();
 886:   
 887:                  foreach (var d in msanDidToServiceCountDictionary)
 888:                  {
 889:                      if (!didList.Contains(d.Key)) unknown.MsanService += d.Value;
 890:                  }
 891:   
 892:                  siteStatisticList = siteStatisticList.OrderBy(u => u.Name).ToList();
 893:   
 894:                  siteStatisticList.Add(unknown);
 895:   
 896:                  // below: last totals row
 897:                  siteStatistic = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site();
 898:   
 899:                  siteStatistic.NameArabicName = "Total (مجموع)";
 900:                  siteStatistic.Name = "Total";
 901:   
 902:                  siteStatistic.FiberService = totalFiberService;
 903:                  siteStatistic.NokiaService = totalNokiaService;
 904:                  siteStatistic.HuaweiService = totalHuaweiService;
 905:                  siteStatistic.FiberServiceText = (siteStatistic.NokiaService + siteStatistic.HuaweiService) + " (" + siteStatistic.NokiaService + "/" + siteStatistic.HuaweiService + ")";
 906:   
 907:                  siteStatistic.FiberAccessCapacity = totalFiberAccessCapacity;
 908:                  siteStatistic.FiberServiceCapacity = totalFiberServiceCapacity;
 909:   
 910:                  siteStatistic.MsanService = totalMsanService;
 911:                  siteStatistic.MsanServiceCapacity = totalMsanServiceCapacity;
 912:                  siteStatistic.MsanServiceText = totalMsanService.ToString();
 913:   
 914:                  siteStatistic.PstnService = totalPstnService;
 915:                  siteStatistic.EricssonPstnService = totalEricssonService;
 916:                  siteStatistic.SiemensPstnService = totalSiemensService;
 917:                  siteStatistic.EricssonSiemensPstnServiceText = (siteStatistic.EricssonPstnService + siteStatistic.SiemensPstnService) + " (" + siteStatistic.EricssonPstnService + "/" + siteStatistic.SiemensPstnService + ")";
 918:   
 919:                  siteStatistic.Service = totalService;
 920:                  siteStatistic.ServiceText = siteStatistic.Service.ToString();
 921:   
 922:                  siteStatisticList.Add(siteStatistic);
 923:              }
 924:   
 925:              return siteStatisticList;
 926:          }
 927:   
 928:          ////////////////////////////////////////////////////////////////////////////
 929:   
 930:          /// <summary>
 931:          ///
 932:          /// </summary>
 933:          public class YearMonthProvisionedDeprovisionServiceProvisioningStatistic
 934:          {
 935:              public int Year { get; set; }
 936:   
 937:              public int Month { get; set; }
 938:   
 939:              public string MonthName
 940:              {
 941:                  get
 942:                  {
 943:                      return CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(this.Month);
 944:                  }
 945:              }
 946:   
 947:              public string YearMonth { get { return Year + "-" + Month.ToString().PadLeft(2, '0'); } }
 948:   
 949:              public int Provisioned { get; set; }
 950:   
 951:              public int Deprovisioned { get; set; }
 952:   
 953:              public int Total { get; set; }
 954:          }
 955:   
 956:          ////////////////////////////////////////////////////////////////////////////
 957:   
 958:          /// <summary>
 959:          ///
 960:          /// </summary>
 961:          public static List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic> ServiceProvisioningInImsSwitchAndAccessNetworkStatistic()
 962:          {
 963:              List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic> list;
 964:   
 965:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 966:              {
 967:                  var sqlServer = new Ia.Cl.Model.Db.SqlServer();
 968:   
 969:                  var dbtable = sqlServer.Select(@"
 970:  select
 971:  YEAR(sr.RequestDateTime) Year, MONTH(sr.RequestDateTime) Month,
 972:  SUM(case WHEN sr.ServiceId = 1 or sr.ServiceId = 129 or sr.ServiceId = 54 then 1 else 0 end) Provisioned,
 973:  SUM(case WHEN sr.ServiceId = 40 then 1 else 0 end) Deprovisioned 
 974:  --count(srs.Provisioned)
 975:  from ServiceRequestServices srs
 976:  left outer join ServiceRequests sr on srs.Id = sr.ServiceRequestService_Id
 977:  where (sr.ServiceId = 1 or sr.ServiceId = 129 or sr.ServiceId = 54 or sr.ServiceId = 40) 
 978:  group by YEAR(sr.RequestDateTime), MONTH(sr.RequestDateTime)
 979:  order by YEAR(sr.RequestDateTime), MONTH(sr.RequestDateTime)
 980:  ");
 981:   
 982:                  /*
 983:  -- <service id="1" arabicName="خط هاتف" />
 984:  -- <service id="129" arabicName="خط هاتف مع نداء آلي"/>
 985:  -- <service id="54" arabicName="اعادة تركيب" />
 986:  -- <service id="40" arabicName="رفع خط" />
 987:                  *
 988:                   */
 989:   
 990:                  // I tried every possible way to use LINQ but failed. As a last resort I used SqlServer directly
 991:   
 992:                  var total = 0;
 993:                  list = new List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic>();
 994:   
 995:                  foreach (DataRow row in dbtable.Rows)
 996:                  {
 997:                      var item = new YearMonthProvisionedDeprovisionServiceProvisioningStatistic();
 998:   
 999:                      item.Year = int.Parse(row["Year"].ToString());
                    item.Month = int.Parse(row["Month"].ToString());
                    item.Provisioned = int.Parse(row["Provisioned"].ToString());
                    item.Deprovisioned = int.Parse(row["Deprovisioned"].ToString());
 
                    total += item.Provisioned - item.Deprovisioned;
                    item.Total = total;
 
                    list.Add(item);
                }
            }
 
            return list;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic> ServiceProvisioningInNgnSwitchNetworkStatistic()
        {
            List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic> list;
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                var sqlServer = new Ia.Cl.Model.Db.SqlServer();
 
                var dbtable = sqlServer.Select(@"
select
YEAR(sr.RequestDateTime) Year, MONTH(sr.RequestDateTime) Month,
SUM(case WHEN sr.ServiceId = 1 or sr.ServiceId = 129 or sr.ServiceId = 54 then 1 else 0 end) Provisioned,
SUM(case WHEN sr.ServiceId = 40 then 1 else 0 end) Deprovisioned 
--count(srs.Provisioned)
from ServiceRequests sr
where (sr.ServiceId = 1 or sr.ServiceId = 129 or sr.ServiceId = 54 or sr.ServiceId = 40) 
group by YEAR(sr.RequestDateTime), MONTH(sr.RequestDateTime)
order by YEAR(sr.RequestDateTime), MONTH(sr.RequestDateTime)
");
 
                /*
-- <service id="1" arabicName="خط هاتف" />
-- <service id="129" arabicName="خط هاتف مع نداء آلي"/>
-- <service id="54" arabicName="اعادة تركيب" />
-- <service id="40" arabicName="رفع خط" />
                *
                 */
 
                // I tried every possible way to use LINQ but failed. As a last resort I used SqlServer directly
 
                var total = 0;
                list = new List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic>();
 
                foreach (DataRow row in dbtable.Rows)
                {
                    var item = new YearMonthProvisionedDeprovisionServiceProvisioningStatistic();
 
                    item.Year = int.Parse(row["Year"].ToString());
                    item.Month = int.Parse(row["Month"].ToString());
                    item.Provisioned = int.Parse(row["Provisioned"].ToString());
                    item.Deprovisioned = int.Parse(row["Deprovisioned"].ToString());
 
                    total += item.Provisioned - item.Deprovisioned;
                    item.Total = total;
 
                    list.Add(item);
                }
            }
 
            return list;
        }
 
        /*
        ////////////////////////////////////////////////////////////////////////////
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Business.Administration.Statistic> PhoneStatistic(string timePeriod)
        {
            List<Ia.Ngn.Cl.Model.Business.Administration.Statistic> phoneStatisticList;
            /*
            string s, where;
            DateTime from, to;
            DataTable dt;
            if (timePeriod != null)
            {
                from = DateTime.Parse(timePeriod);
                to = DateTime.Parse(timePeriod);
                to = to.AddMonths(1);
                where = " AND (sr.request_time >= '" + sqlserver.SmallDateTime(from) + "' AND sr.request_time < '" + sqlserver.SmallDateTime(to) + "') ";
            }
            else where = string.Empty;
             * /
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                phoneStatisticList = (from a in Ia.Ngn.Cl.Model.Data.Service.KuwaitNgnAreaList
                     group a by a.Id into grp
                     orderby grp.Key
                     select new Ia.Ngn.Cl.Model.Business.Administration.Statistic()
                     {
                         Id = grp.Key.ToString(),
                         Name = grp.SingleOrDefault().NameArabicName,
                         //ServiceRequests = (from sr in db.ServiceRequests where sr.AreaId == grp.Key && sr.ServiceRequestService != null select sr.Id).Count().ToString(),
                         ServiceRequestServices = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key select srs.Id).Count(),
                         Services = (from s in db.Service2s where s.ServiceType == Ia.Ngn.Cl.Model.Business.Service.ServiceType.NgnService && s.Access != null && s.Access.AreaId == grp.Key select s.Id).Count(),
                         InternationalCalling = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.InternationalCalling == true select srs.Id).Count(),
                         InternationalCallingUserControlled = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.InternationalCallingUserControlled == true select srs.Id).Count(),
                         CallWaiting = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.CallWaiting == true select srs.Id).Count(),
                         AlarmCall = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.AlarmCall == true select srs.Id).Count(),
                         CallBarring = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.CallBarring == true select srs.Id).Count(),
                         CallerId = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.CallerId == true select srs.Id).Count(),
                         CallForwarding = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.CallForwarding == true select srs.Id).Count(),
                         ConferenceCall = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.ConferenceCall == true select srs.Id).Count(),
                         ServiceSuspension = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.ServiceSuspension == true select srs.Id).Count()
                     }).ToList();
                /*
                if (dt != null)
                {
                    // below: the totals rows
                    dr = dt.NewRow();
                    dr["SRS_AccessIdNotNull"] = dt.Compute("SUM (SRS_AccessIdNotNull)", "").ToString();
                    dr["IMS_AccessIdNotNull"] = dt.Compute("SUM (IMS_AccessIdNotNull)", "").ToString();
                    dr["SRS_AccordingToAreaIdFromDomain"] = dt.Compute("SUM (SRS_AccordingToAreaIdFromDomain)", "").ToString();
                    dr["InternationalCalling"] = dt.Compute("SUM (InternationalCalling)", "").ToString();
                    dr["InternationalCallingUserControlled"] = dt.Compute("SUM (InternationalCallingUserControlled)", "").ToString();
                    dr["CallWaiting"] = dt.Compute("SUM (CallWaiting)", "").ToString();
                    dr["AlarmCall"] = dt.Compute("SUM (AlarmCall)", "").ToString();
                    dr["CallBarring"] = dt.Compute("SUM (CallBarring)", "").ToString();
                    dr["CallerId"] = dt.Compute("SUM (CallerId)", "").ToString();
                    dr["CallForwarding"] = dt.Compute("SUM (CallForwarding)", "").ToString();
                    dr["ConferenceCall"] = dt.Compute("SUM (ConferenceCall)", "").ToString();
                    dr["ServiceSuspension"] = dt.Compute("SUM (ServiceSuspension)", "").ToString();
                    dt.Rows.Add(dr);
                }
                 * /
            }
            return phoneStatisticList;
        }
        */
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Ui.Performance> StaffAndFrameworkPerformanceReport(int daysAgo)
        {
            DateTime startDateTime;
            List<Ia.Ngn.Cl.Model.Ui.Performance> performanceList;
 
            // after 2015-06-01 user report closer inserts a last historic report
            // I should designate last report as CLOSED and add it to resolution list to be accessed by HEAD only.
 
            // if daysAgo is 0 will make it 9999 days to cover all times
            startDateTime = DateTime.UtcNow.AddDays(daysAgo == 0 ? -9999 : -daysAgo);
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                var staffList = (from s in db.Staffs select s).ToList();
 
                var resolvedList = (from r in db.ReportHistories where r.Resolution == 1020 && r.Created > startDateTime group r.UserId by r.UserId into g select new { UserId = g.Key, Count = g.Count() }).ToDictionary(u => u.UserId, u => u.Count);
 
                var attemptedList = (from r in db.ReportHistories where r.Resolution != 1020 && r.Created > startDateTime group r.UserId by r.UserId into g select new { UserId = g.Key, Count = g.Count() }).ToDictionary(u => u.UserId, u => u.Count);
 
                var insertedList = (from r in db.Reports where r.Created > startDateTime group r.UserId by r.UserId into g select new { UserId = g.Key, Count = g.Count() }).ToDictionary(u => u.UserId, u => u.Count);
 
                var openList = (from r in db.Reports where r.Created > startDateTime group r.UserId by r.UserId into g select new { UserId = g.Key, Count = g.Count() }).ToDictionary(u => u.UserId, u => u.Count);
 
 
                var allowedToBeMigratedDomainList = Ia.Ngn.Cl.Model.Business.Service.AllowedToBeMigratedDomainList;
 
                var serviceRequestServiceList = (from srs in db.ServiceRequestServices
                                                 where srs.Updated > startDateTime
                                                 select new { srs.Service, srs.UserId }).ToList();
 
                var migratedList = (from srs in serviceRequestServiceList
                                    where allowedToBeMigratedDomainList.Any(u => srs.Service.StartsWith(u.ToString()))
                                    group srs.UserId by srs.UserId into g
                                    select new { UserId = g.Key, Count = g.Count() }).ToDictionary(u => u.UserId, u => u.Count);
 
 
                performanceList = new List<Ia.Ngn.Cl.Model.Ui.Performance>();
 
                foreach (var staff in staffList)
                {
                    var p = new Ia.Ngn.Cl.Model.Ui.Performance
                    {
                        UserId = staff.UserId,
                        FirstAndMiddleName = staff.FirstAndMiddleName,
                        Resolved = (resolvedList.ContainsKey(staff.UserId) ? resolvedList[staff.UserId] : 0),
                        Attempted = (attemptedList.ContainsKey(staff.UserId) ? attemptedList[staff.UserId] : 0),
                        Inserted = (insertedList.ContainsKey(staff.UserId) ? insertedList[staff.UserId] : 0),
                        Open = (openList.ContainsKey(staff.UserId) ? openList[staff.UserId] : 0),
                        AverageReportsPerDay = 0,
                        Migrated = (migratedList.ContainsKey(staff.UserId) ? migratedList[staff.UserId] : 0),
                    };
 
                    performanceList.Add(p);
                }
            }
 
            // below: exclude staff who did absolutly nothing: Resolved = Attempted = Inserted = 0
 
            performanceList = (from p in performanceList where (p.Inserted != 0 || p.Attempted != 0 || p.Resolved != 0) select p).ToList();
 
            foreach (var p in performanceList)
            {
                if (p.UserId == Guid.Empty) p.FirstAndMiddleName = "غير معرف";
                else { }
            }
 
            return performanceList;
        }
 
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        ///
        /// </summary>
        public static List<Ia.Ngn.Cl.Model.Ui.Performance> StatisticsOfResolvedAndAttemptedAndInsertedStaffReport2(Guid userId)
        {
            List<Ia.Ngn.Cl.Model.Ui.Performance> performanceList;
 
            // after 2015-06-01 user report closer inserts a last historic report
            // I should designate last report as CLOSED and add it to resolution list to be accessed by HEAD only.
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                performanceList =
                    (
                    from staff in
                        (from s in db.Staffs group s.UserId by s.UserId into g select new { UserId = g.Key, Count = g.Count() })
                    join resolved in
                        (from r in db.ReportHistories where r.Resolution == 1020 group r.UserId by r.UserId into g select new { UserId = g.Key, Count = g.Count() })
                    on staff.UserId equals resolved.UserId into resolved_gj
                    from re in resolved_gj.DefaultIfEmpty()
 
                    join attempted in
                        (from r in db.ReportHistories where r.Resolution != 1020 group r.UserId by r.UserId into g select new { UserId = g.Key, Count = g.Count() })
                    on staff.UserId equals attempted.UserId into attempted_gj
                    from at in attempted_gj.DefaultIfEmpty()
 
                    join inserted in
                        (from r in db.Reports group r.UserId by r.UserId into g select new { UserId = g.Key, Count = g.Count() })
                    on staff.UserId equals inserted.UserId into inserted_gj
                    from ins in inserted_gj.DefaultIfEmpty()
                        // below: the 20, 10, 1 give weight to the field
                    orderby re.Count descending, at.Count descending, ins.Count descending
                    select new Ia.Ngn.Cl.Model.Ui.Performance
                    {
                        UserId = staff.UserId,
                        FirstAndMiddleName = (from s in Ia.Ngn.Cl.Model.Data.Staff.List where s.UserId == staff.UserId select s.FirstAndMiddleName).SingleOrDefault(),
                        Resolved = (re == null ? 0 : re.Count),
                        Attempted = (at == null ? 0 : at.Count),
                        Inserted = (ins == null ? 0 : ins.Count),
                        AverageReportsPerDay = 0
                    }).ToList();
 
                /*
        select users.UserId, resolved.count,attempted.count,inserted.count from
        (
        (select count(*) as count, UserId from Users group by UserId) as users
        left outer join
        (select count(*) as count, rh.UserId from ReportHistories as rh where rh.Resolution = 1020 group by rh.UserId) as resolved
        on users.UserId = resolved.UserId
        left outer join
        (select count(*) as count, rh.UserId from ReportHistories as rh where rh.Resolution <> 1020 group by rh.UserId) as attempted
        on users.UserId = attempted.UserId
        left outer join
        (select count(*) as count, UserId from Reports group by UserId) as inserted
        on users.UserId = inserted.UserId
        )
        order by resolved.count*20+attempted.count*10+inserted.count desc
            */
            }
 
            return performanceList.ToList();
        }
 
        ////////////////////////////////////////////////////////////////////////////    
 
        /// <summary>
        ///
        /// </summary>
        public static Dictionary<string, int> DateTimesWithAvailableData()
        {
            Dictionary<string, int> dic;
 
 
            dic = new Dictionary<string, int>(100);
 
 
            using (var db = new Ia.Ngn.Cl.Model.Ngn())
            {
                //dic = (from q in db.ServiceRequests orderby q.RequestDateTime select q.RequestDateTime).Distinct().ToDictionary(r => r.CustomerName, r => r.Id);
 
                // dictionary = (from q in ReportXDocument.Elements("report").Elements("category") select new { Id = int.Parse(q.Attribute("id").Value), Name = q.Attribute("name").Value }).ToDictionary(r => r.Id, r => r.Name);
 
            }
 
            /*
        SELECT DISTINCT CONVERT(varchar(7), RequestDateTime, 102) AS date, COUNT(1) AS count
        FROM [Ia_Ngn].[dbo].[ServiceRequests]
        GROUP BY CONVERT(varchar(7), RequestDateTime, 102)
        ORDER BY date
             */
 
            return dic;
        }
 
        ////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////
 
        /// <summary>
        /// 
        /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
        /// 
        /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
        /// 2. Add "using System.Reflection".
        /// 3. Manifest resource stream will start with the project namespace, the location of XML file.
        /// 
        /// </summary>
 
        private static XDocument XDocument
        {
            get
            {
                if (xDocument == null)
                {
                    lock (objectLock)
                    {
                        Assembly _assembly;
                        StreamReader streamReader;
 
                        _assembly = Assembly.GetExecutingAssembly();
                        streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.administration.xml"));
 
                        try
                        {
                            if (streamReader.Peek() != -1) xDocument = System.Xml.Linq.XDocument.Load(streamReader);
                        }
                        catch (Exception)
                        {
                        }
                        finally
                        {
                        }
                    }
                }
 
                return xDocument;
            }
        }
 
        ////////////////////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////////////////////    
    }
 
    ////////////////////////////////////////////////////////////////////////////
    ////////////////////////////////////////////////////////////////////////////   
}