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

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 Next Generation Network (NGN) data model.

   1:  using Ia.Cl.Model.Db;
   2:  using Ia.Ngn.Cl.Migrations;
   3:  using System;
   4:  using System.Collections.Generic;
   5:  using System.Configuration;
   6:  using System.Data;
   7:  using System.Diagnostics;
   8:  using System.Globalization;
   9:  using System.IO;
  10:  using System.Linq;
  11:  using System.Reflection;
  12:  using System.Security.Cryptography;
  13:  using System.Web;
  14:  using System.Web.UI.WebControls;
  15:  using System.Xml.Linq;
  16:   
  17:  namespace Ia.Ngn.Cl.Model.Data
  18:  {
  19:      ////////////////////////////////////////////////////////////////////////////
  20:   
  21:      /// <summary publish="true">
  22:      /// Administration support class for Next Generation Network (NGN) data model.
  23:      /// </summary>
  24:      /// 
  25:      /// <remarks> 
  26:      /// Copyright © 2006-2017 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
  27:      ///
  28:      /// 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
  29:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  30:      ///
  31:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  32:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  33:      /// 
  34:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  35:      /// 
  36:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  37:      /// </remarks> 
  38:      public class Administration
  39:      {
  40:          private static XDocument xDocument;
  41:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Framework> frameworkList;
  42:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Category> categoryList;
  43:          private static List<Ia.Ngn.Cl.Model.Business.Administration.StaffFramework> staffFrameworkList;
  44:          private static List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact> staffContactList;
  45:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea> statisticList;
  46:   
  47:          private static readonly object objectLock = new object();
  48:   
  49:          ////////////////////////////////////////////////////////////////////////////
  50:   
  51:          /// <summary>
  52:          ///
  53:          /// </summary>
  54:          public Administration() { }
  55:   
  56:          ////////////////////////////////////////////////////////////////////////////
  57:   
  58:          /// <summary>
  59:          ///
  60:          /// </summary>
  61:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Framework> FrameworkList
  62:          {
  63:              get
  64:              {
  65:                  lock (objectLock)
  66:                  {
  67:                      if (frameworkList == null || frameworkList.Count == 0) frameworkList = Ia.Ngn.Cl.Model.Data.Administration._FrameworkList;
  68:   
  69:                      return frameworkList;
  70:                  }
  71:              }
  72:          }
  73:   
  74:          ////////////////////////////////////////////////////////////////////////////
  75:   
  76:          /// <summary>
  77:          ///
  78:          /// </summary>
  79:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Framework> _FrameworkList
  80:          {
  81:              get
  82:              {
  83:                  int id, parentId;
  84:                  string arabicName, coloredArabicName, siteNameList;
  85:                  List<string> lightBackgroundColorList;
  86:                  Ia.Ngn.Cl.Model.Business.Administration.Framework framework;
  87:   
  88:                  frameworkList = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>(Ia.Ngn.Cl.Model.Business.Administration.InitialFrameworkListLength); // needed to prevent errors
  89:   
  90:                  lightBackgroundColorList = Ia.Ngn.Cl.Model.Ui.Default.LightBackgroundColorList;
  91:   
  92:                  foreach (XElement xe in XDocument.Element("administration").Element("frameworkList").Descendants("framework"))
  93:                  {
  94:                      id = int.Parse(XmlBasedTwoDigitPerId(xe));
  95:                      parentId = Ia.Ngn.Cl.Model.Business.Administration.Framework.ParentId(id);
  96:                      arabicName = xe.Attribute("arabicName").Value;
  97:                      coloredArabicName = @"<span style=""color:" + lightBackgroundColorList[id % lightBackgroundColorList.Count] + @""">" + arabicName + "</span>";
  98:   
  99:                      framework = new Ia.Ngn.Cl.Model.Business.Administration.Framework();
 100:   
 101:                      framework.Id = id;
 102:                      framework.Level = xe.Ancestors().Count();
 103:                      framework.Guid = Guid.Parse(xe.Attribute("guid").Value);
 104:                      framework.Type = xe.Attribute("type").Value;
 105:                      framework.Name = xe.Attribute("name").Value;
 106:                      framework.ArabicName = arabicName;
 107:                      framework.ColoredArabicName = coloredArabicName;
 108:   
 109:                      if (xe.Attribute("siteNameList") != null && !string.IsNullOrEmpty(xe.Attribute("siteNameList").Value))
 110:                      {
 111:                          siteNameList = xe.Attribute("siteNameList").Value;
 112:                          framework.Sites = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where siteNameList.Contains(s.Name) select s).ToList();
 113:                      }
 114:                      else if (xe.Parent.Attribute("siteNameList") != null && !string.IsNullOrEmpty(xe.Parent.Attribute("siteNameList").Value))
 115:                      {
 116:                          siteNameList = xe.Parent.Attribute("siteNameList").Value;
 117:                          framework.Sites = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where siteNameList.Contains(s.Name) select s).ToList();
 118:                      }
 119:                      else framework.Sites = new List<Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site>();
 120:   
 121:                      frameworkList.Add(framework);
 122:                  }
 123:   
 124:                  // Parents, authorities and children
 125:                  foreach (Ia.Ngn.Cl.Model.Business.Administration.Framework f in frameworkList)
 126:                  {
 127:                      parentId = Ia.Ngn.Cl.Model.Business.Administration.Framework.ParentId(f.Id);
 128:   
 129:                      /*
 130:  [InvalidOperationException: Sequence contains more than one element]
 131:  System.Linq.Enumerable.SingleOrDefault(IEnumerable`1 source) +348
 132:  Ia.Ngn.Cl.Model.Data.Administration.get__FrameworkList() in C:\Users\Jasem\Documents\Visual Studio 2019\Projects\Next Generation Network\cl\model\data\administration.cs:343                                 
 133:  */
 134:                      f.Parent = (from _f in frameworkList where _f.Id == parentId select _f).SingleOrDefault();
 135:   
 136:                      //HttpContext.Current.Response.Write("[" + f.ArabicName + "]" + "[" + ex.Message + "]" + "[count: " + frameworkList.Count + "]");
 137:   
 138:                      //f.Authorities = (from a in AuthorityList where a.AllowedFrameworkArabicName == f.ArabicName select a).ToList();
 139:   
 140:                      f.Children = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>();
 141:   
 142:                      if (f.Parent != null) f.Parent.Children.Add(f);
 143:                  }
 144:   
 145:                  // below: Siblings
 146:                  foreach (Ia.Ngn.Cl.Model.Business.Administration.Framework f in frameworkList.ToList())
 147:                  {
 148:                      f.Siblings = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>();
 149:   
 150:                      f.Siblings = (from _f in frameworkList where _f.Parent == f.Parent && _f.Id != f.Id select _f).ToList();
 151:                  }
 152:   
 153:                  // below: Descendants
 154:                  foreach (Ia.Ngn.Cl.Model.Business.Administration.Framework f in frameworkList.ToList())
 155:                  {
 156:                      f.Descendants = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>();
 157:   
 158:                      f.Descendants = (from _f in frameworkList
 159:                                       where _f.Id != f.Id && _f.Id > f.Id &&
 160:                                           (_f.Parent != null && (_f.Parent == f
 161:                                           || _f.Parent.Parent != null && (_f.Parent.Parent == f
 162:                                           || _f.Parent.Parent.Parent != null && (_f.Parent.Parent.Parent == f
 163:                                           || _f.Parent.Parent.Parent.Parent != null && _f.Parent.Parent.Parent.Parent == f)))
 164:                                           )
 165:                                       select _f).ToList();
 166:                  }
 167:   
 168:                  // below: Ancestors
 169:                  foreach (Ia.Ngn.Cl.Model.Business.Administration.Framework f in frameworkList.ToList())
 170:                  {
 171:                      f.Ancestors = new List<Ia.Ngn.Cl.Model.Business.Administration.Framework>();
 172:   
 173:                      f.Ancestors = (from _f in frameworkList
 174:                                     where _f.Id != f.Id && _f.Id < f.Id &&
 175:                                         (f.Parent != null && (f.Parent == _f
 176:                                         || f.Parent.Parent != null && (f.Parent.Parent == _f
 177:                                         || f.Parent.Parent.Parent != null && (f.Parent.Parent.Parent == _f
 178:                                         || f.Parent.Parent.Parent.Parent != null && f.Parent.Parent.Parent.Parent == _f)))
 179:                                         )
 180:                                     select _f).ToList();
 181:                  }
 182:   
 183:                  //list = (from q in list select q).OrderByDescending(c => c.IsHead).ThenBy(c => c.AdministrativeFrameworkId);
 184:   
 185:                  //frameworkList = (from q in frameworkList select q).OrderBy(c => c.Id); //.ThenBy(c => c.ParentId);
 186:   
 187:                  return frameworkList.ToList();
 188:              }
 189:          }
 190:   
 191:          ////////////////////////////////////////////////////////////////////////////
 192:   
 193:          /// <summary>
 194:          ///
 195:          /// </summary>
 196:          private static string XmlBasedOneDigitPerId(XElement xeIn)
 197:          {
 198:              return XmlBasedId(xeIn, 1);
 199:          }
 200:   
 201:          ////////////////////////////////////////////////////////////////////////////
 202:   
 203:          /// <summary>
 204:          ///
 205:          /// </summary>
 206:          private static string XmlBasedTwoDigitPerId(XElement xeIn)
 207:          {
 208:              return XmlBasedId(xeIn, 2);
 209:          }
 210:   
 211:          ////////////////////////////////////////////////////////////////////////////
 212:   
 213:          /// <summary>
 214:          ///
 215:          /// </summary>
 216:          private static string XmlBasedId(XElement xe, int digit)
 217:          {
 218:              string id;
 219:   
 220:              id = string.Empty;
 221:   
 222:              while (xe.HasAttributes && xe.Attribute("id") != null)
 223:              {
 224:                  id = xe.Attribute("id").Value.PadLeft(digit, '0') + id;
 225:                  xe = xe.Parent;
 226:              }
 227:   
 228:              return id;
 229:          }
 230:   
 231:          ////////////////////////////////////////////////////////////////////////////
 232:   
 233:          /// <summary>
 234:          ///
 235:          /// </summary>
 236:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Category> CategoryList
 237:          {
 238:              get
 239:              {
 240:                  lock (objectLock)
 241:                  {
 242:                      if (categoryList == null || categoryList.Count == 0) categoryList = Ia.Ngn.Cl.Model.Data.Administration._CategoryList;
 243:   
 244:                      return categoryList;
 245:                  }
 246:              }
 247:          }
 248:   
 249:          ////////////////////////////////////////////////////////////////////////////
 250:   
 251:          /// <summary>
 252:          ///
 253:          /// </summary>
 254:          private static List<Ia.Ngn.Cl.Model.Business.Administration.Category> _CategoryList
 255:          {
 256:              get
 257:              {
 258:                  Ia.Ngn.Cl.Model.Business.Administration.Category category;
 259:   
 260:                  categoryList = new List<Ia.Ngn.Cl.Model.Business.Administration.Category>();
 261:   
 262:                  foreach (XElement xe in XDocument.Element("administration").Elements("category"))
 263:                  {
 264:                      category = new Ia.Ngn.Cl.Model.Business.Administration.Category()
 265:                      {
 266:                          Name = xe.Attribute("name").Value,
 267:                          Regex = xe.Attribute("regex").Value,
 268:                          Description = (xe.Attribute("description") != null) ? xe.Attribute("description").Value : string.Empty,
 269:                          Color = (xe.Attribute("color") != null) ? xe.Attribute("color").Value : string.Empty
 270:                      };
 271:   
 272:                      categoryList.Add(category);
 273:                  }
 274:   
 275:                  return categoryList.ToList();
 276:              }
 277:          }
 278:   
 279:          ////////////////////////////////////////////////////////////////////////////
 280:   
 281:          /// <summary>
 282:          ///
 283:          /// </summary>
 284:          public static List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact> StaffContactList
 285:          {
 286:              get
 287:              {
 288:                  lock (objectLock)
 289:                  {
 290:                      if (staffContactList == null || staffContactList.Count == 0) staffContactList = Ia.Ngn.Cl.Model.Data.Administration._StaffContactList;
 291:   
 292:                      return staffContactList;
 293:                  }
 294:              }
 295:          }
 296:   
 297:          ////////////////////////////////////////////////////////////////////////////
 298:   
 299:          /// <summary>
 300:          ///
 301:          /// </summary>
 302:          private static List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact> _StaffContactList
 303:          {
 304:              get
 305:              {
 306:                  Ia.Ngn.Cl.Model.Business.Administration.StaffContact staffContact;
 307:                  List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact> staffContactList;
 308:   
 309:                  staffContactList = new List<Ia.Ngn.Cl.Model.Business.Administration.StaffContact>();
 310:   
 311:                  foreach (Ia.Ngn.Cl.Model.Staff staff in Ia.Ngn.Cl.Model.Data.Staff.List)
 312:                  {
 313:                      if (staff.User != null && staff.User.Email != null)
 314:                      {
 315:                          staffContact = new Ia.Ngn.Cl.Model.Business.Administration.StaffContact() { Staff = staff };
 316:   
 317:                          staffContactList.Add(staffContact);
 318:                      }
 319:                  }
 320:   
 321:                  foreach (Ia.Ngn.Cl.Model.Contact contact in Ia.Ngn.Cl.Model.Data.Contact.List)
 322:                  {
 323:                      if (contact.Email != null)
 324:                      {
 325:                          staffContact = new Ia.Ngn.Cl.Model.Business.Administration.StaffContact() { Contact = contact };
 326:   
 327:                          staffContactList.Add(staffContact);
 328:                      }
 329:                  }
 330:   
 331:                  return staffContactList.ToList();
 332:              }
 333:          }
 334:   
 335:          ////////////////////////////////////////////////////////////////////////////
 336:   
 337:          /// <summary>
 338:          ///
 339:          /// </summary>
 340:          public static List<Ia.Ngn.Cl.Model.Business.Administration.StaffFramework> StaffFrameworkList
 341:          {
 342:              get
 343:              {
 344:                  lock (objectLock)
 345:                  {
 346:                      if (staffFrameworkList == null || staffFrameworkList.Count == 0) staffFrameworkList = Ia.Ngn.Cl.Model.Data.Administration._StaffFrameworkList;
 347:   
 348:                      return staffFrameworkList;
 349:                  }
 350:              }
 351:          }
 352:   
 353:          ////////////////////////////////////////////////////////////////////////////
 354:   
 355:          /// <summary>
 356:          ///
 357:          /// </summary>
 358:          private static List<Ia.Ngn.Cl.Model.Business.Administration.StaffFramework> _StaffFrameworkList
 359:          {
 360:              get
 361:              {
 362:                  List<Ia.Ngn.Cl.Model.Business.Administration.StaffFramework> staffList, frameworkList;
 363:   
 364:                  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:                  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.GponService && 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, totalAccessReadyForService;
 463:                  Dictionary<string, int> accessIdToKuwaitNgnAreaIdDictionary, accessIdToOdfIdDictionary, accessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary;
 464:                  Dictionary<string, string> serviceRequestServiceProvisionedServiceIdToAccessIdDictionary, serviceIdToAccessIdDictionary;//, ontIdToAccessIdDictionary;
 465:                  Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor vendor;
 466:                  Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea kuwaitAreaStatistic;
 467:                  Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Olt olt;
 468:                  //List<Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont> nddOntList;
 469:                  List<Ia.Ngn.Cl.Model.Ont> ontList;
 470:                  List<Ia.Ngn.Cl.Model.Huawei.EmsOnt> emsOntList;
 471:                  List<Ia.Ngn.Cl.Model.Access> accessList;
 472:                  List<Ia.Ngn.Cl.Model.Business.Service.KuwaitNgnArea> kuwaitNgnAreaList;
 473:   
 474:                  statisticList = new List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea>();
 475:                  statisticList.Clear();
 476:   
 477:                  totalService = totalNokiaService = totalHuaweiService = totalServiceRequestService = totalAccessCapacity = totalNokiaOnt = totalHuaweiOnt = totalNokiaAccess = totalHuaweiAccess = totalAccessReadyForService = totalService = totalServiceRequestService = 0;
 478:   
 479:                  //kuwaitNgnAreaList = new List<Ia.Ngn.Cl.Model.Business.Service.KuwaitNgnArea>(); // I need to clear it because I add an item in it below
 480:                  kuwaitNgnAreaList = Ia.Ngn.Cl.Model.Data.Service.KuwaitNgnAreaList;
 481:   
 482:                  //nddOntList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntList;
 483:                  //ontIdToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Nokia.Ont.IdToAccessIdDictionary;
 484:   
 485:                  ontList = Ia.Ngn.Cl.Model.Data.Nokia.Ont.ListIncludeAccess();
 486:                  emsOntList = Ia.Ngn.Cl.Model.Data.Huawei.Ont.ListIncludeAccess();
 487:                  accessList = Ia.Ngn.Cl.Model.Data.Access.List();
 488:                  accessIdToOdfIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToOltIdDictionary;
 489:   
 490:                  //serviceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ProvisionedServiceIdToAccessIdDictionary;
 491:                  accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
 492:   
 493:                  serviceRequestServiceProvisionedServiceIdToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ProvisionedServiceIdToAccessIdDictionary;
 494:                  serviceIdToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceIdToAccessIdDictionary;
 495:   
 496:                  accessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.AccessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary;
 497:   
 498:                  var domainToRouterVendorDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.DomainToRouterVendorDictionary;
 499:   
 500:                  var unknown = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea("Unknown", "غير معرف");
 501:   
 502:                  //ontIdToOltIdDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntIdToOltIdDictionary;
 503:   
 504:                  // - ONT capacity, usage, # of services in an ONT, type of ONT
 505:                  // - Graphics
 506:                  /*
 507:                  update statistics
 508:      Vendor,
 509:      number perfix,
 510:      4 in ONT,
 511:      0 in ONT,
 512:      Acces: ONT used by subscribers
 513:      */
 514:   
 515:                  foreach (var kna in kuwaitNgnAreaList)
 516:                  {
 517:                      kuwaitAreaStatistic = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea();
 518:   
 519:                      kuwaitAreaStatistic.Name = kna.NameArabicName;
 520:   
 521:                      kuwaitAreaStatistic.Symbol = kna.Symbol;
 522:   
 523:                      kuwaitAreaStatistic.ServiceRequestService = 0;
 524:   
 525:                      kuwaitAreaStatistic.NokiaService = 0;
 526:                      kuwaitAreaStatistic.HuaweiService = 0;
 527:                      kuwaitAreaStatistic.Service = 0;
 528:   
 529:                      foreach (KeyValuePair<string, string> kvp in serviceIdToAccessIdDictionary)
 530:                      {
 531:                          if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(kvp.Value))
 532:                          {
 533:                              if (accessIdToKuwaitNgnAreaIdDictionary[kvp.Value] == kna.Id)
 534:                              {
 535:                                  kuwaitAreaStatistic.Service++;
 536:   
 537:                                  fiveDigitDomain = int.Parse(kvp.Key.Substring(0, 5));
 538:   
 539:                                  if (domainToRouterVendorDictionary.ContainsKey(fiveDigitDomain)) vendor = domainToRouterVendorDictionary[fiveDigitDomain];
 540:                                  else
 541:                                  {
 542:                                      fourDigitDomain = int.Parse(kvp.Key.Substring(0, 4));
 543:   
 544:                                      if (domainToRouterVendorDictionary.ContainsKey(fourDigitDomain))
 545:                                      {
 546:                                          vendor = domainToRouterVendorDictionary[fourDigitDomain];
 547:                                      }
 548:                                      else vendor = null;
 549:                                  }
 550:   
 551:                                  if (vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Nokia) kuwaitAreaStatistic.NokiaService++;
 552:                                  else if (vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Huawei) kuwaitAreaStatistic.HuaweiService++;
 553:                                  else
 554:                                  {
 555:                                      unknown.Service++;
 556:   
 557:                                      //throw new Exception("Vendor is unknown");
 558:                                  }
 559:   
 560:                              }
 561:                          }
 562:                      }
 563:   
 564:                      kuwaitAreaStatistic.NokiaHuaweiService = (kuwaitAreaStatistic.NokiaService + kuwaitAreaStatistic.HuaweiService) + " (" + kuwaitAreaStatistic.NokiaService + "/" + kuwaitAreaStatistic.HuaweiService + ")";
 565:   
 566:   
 567:                      foreach (KeyValuePair<string, string> kvp in serviceRequestServiceProvisionedServiceIdToAccessIdDictionary)
 568:                      {
 569:                          if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(kvp.Value))
 570:                          {
 571:                              if (accessIdToKuwaitNgnAreaIdDictionary[kvp.Value] == kna.Id)
 572:                              {
 573:                                  kuwaitAreaStatistic.ServiceRequestService++;
 574:                              }
 575:                          }
 576:                      }
 577:   
 578:                      //statistic.SiteAccessCapacity = kna.Site.Routers.SelectMany(u => u.Odfs.SelectMany(y => y.Olts)).Count() * 1024;
 579:                      //statistic.SiteAccessCapacity = f.Sum(u => kna.Site.Routers.Any(u => u.Odfs.Any(y => y.Olts.Any(z => z.Id == u.Value)))) * 1024;
 580:                      //kuwaitAreaStatistic.AccessProvisioned = accessIdToKuwaitNgnAreaIdDictionary.Count(u => u.Value == kna.Id);
 581:                      //kuwaitAreaStatistic.AccessUtilized = 0;
 582:   
 583:                      // below: this does not include SSR accesses for area
 584:                      kuwaitAreaStatistic.AccessCapacity = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList where o.Symbol == kna.Symbol select o).Sum(o => o.NumberOfPons * o.NumberOfOntsInPon); ;
 585:   
 586:                      kuwaitAreaStatistic.NokiaOnt = 0;
 587:                      kuwaitAreaStatistic.HuaweiOnt = 0;
 588:   
 589:                      foreach (var o in ontList)
 590:                      {
 591:                          if (o.Access != null)
 592:                          {
 593:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(o.Access.Id))
 594:                              {
 595:                                  if (accessIdToKuwaitNgnAreaIdDictionary[o.Access.Id] == kna.Id)
 596:                                  {
 597:                                      kuwaitAreaStatistic.NokiaOnt++;
 598:                                  }
 599:                              }
 600:                          }
 601:                          else
 602:                          {
 603:                          }
 604:                      }
 605:   
 606:                      foreach (var o in emsOntList)
 607:                      {
 608:                          if (o.Access != null)
 609:                          {
 610:                              if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(o.Access.Id))
 611:                              {
 612:                                  if (accessIdToKuwaitNgnAreaIdDictionary[o.Access.Id] == kna.Id)
 613:                                  {
 614:                                      kuwaitAreaStatistic.HuaweiOnt++;
 615:                                  }
 616:                              }
 617:                          }
 618:                          else
 619:                          {
 620:                          }
 621:                      }
 622:   
 623:                      kuwaitAreaStatistic.NokiaHuaweiOnt = (kuwaitAreaStatistic.NokiaOnt + kuwaitAreaStatistic.HuaweiOnt) + " (" + kuwaitAreaStatistic.NokiaOnt + "/" + kuwaitAreaStatistic.HuaweiOnt + ")";
 624:   
 625:                      kuwaitAreaStatistic.NokiaAccess = 0;
 626:                      kuwaitAreaStatistic.HuaweiAccess = 0;
 627:   
 628:                      foreach (var a in accessList)
 629:                      {
 630:                          if (accessIdToKuwaitNgnAreaIdDictionary[a.Id] == kna.Id)
 631:                          {
 632:                              olt = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList where o.Id == accessIdToOdfIdDictionary[a.Id] select o).SingleOrDefault();
 633:   
 634:                              if (olt != null)
 635:                              {
 636:                                  // <vendor id="1" name="Nokia" shortName="No" ... />
 637:                                  if (olt.Odf.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Nokia) kuwaitAreaStatistic.NokiaAccess++;
 638:   
 639:                                  // <vendor id="2" name="Huawei" shortName="Hu" ... />
 640:                                  else if (olt.Odf.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Huawei) kuwaitAreaStatistic.HuaweiAccess++;
 641:                              }
 642:                              else
 643:                              {
 644:   
 645:                              }
 646:                          }
 647:                      }
 648:   
 649:                      kuwaitAreaStatistic.NokiaHuaweiAccess = (kuwaitAreaStatistic.NokiaAccess + kuwaitAreaStatistic.HuaweiAccess) + " (" + kuwaitAreaStatistic.NokiaAccess + "/" + kuwaitAreaStatistic.HuaweiAccess + ")";
 650:   
 651:   
 652:                      kuwaitAreaStatistic.AccessReadyForService = 0;
 653:   
 654:                      foreach (var a in accessList)
 655:                      {
 656:                          if (accessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary.ContainsKey(a.Id))
 657:                          {
 658:                              if (accessesWithProvisionedAndReadyOntsIdToKuwaitNgnAreaIdDictionary[a.Id] == kna.Id)
 659:                              {
 660:                                  kuwaitAreaStatistic.AccessReadyForService++;
 661:                              }
 662:                          }
 663:                      }
 664:   
 665:                      // totals:
 666:                      totalService += kuwaitAreaStatistic.Service;
 667:   
 668:                      totalNokiaService += kuwaitAreaStatistic.NokiaService;
 669:                      totalHuaweiService += kuwaitAreaStatistic.HuaweiService;
 670:   
 671:                      totalServiceRequestService += kuwaitAreaStatistic.ServiceRequestService;
 672:   
 673:                      totalAccessCapacity += kuwaitAreaStatistic.AccessCapacity;
 674:   
 675:                      totalNokiaOnt += kuwaitAreaStatistic.NokiaOnt;
 676:                      totalHuaweiOnt += kuwaitAreaStatistic.HuaweiOnt;
 677:   
 678:                      totalNokiaAccess += kuwaitAreaStatistic.NokiaAccess;
 679:                      totalHuaweiAccess += kuwaitAreaStatistic.HuaweiAccess;
 680:   
 681:                      totalAccessReadyForService += kuwaitAreaStatistic.AccessReadyForService;
 682:   
 683:                      statisticList.Add(kuwaitAreaStatistic);
 684:                  }
 685:   
 686:                  statisticList.Add(unknown);
 687:   
 688:                  // totals:
 689:                  kuwaitAreaStatistic = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.KuwaitArea();
 690:   
 691:                  kuwaitAreaStatistic.Name = "Total";
 692:   
 693:                  kuwaitAreaStatistic.Service = totalService;
 694:                  kuwaitAreaStatistic.NokiaService = totalNokiaService;
 695:                  kuwaitAreaStatistic.HuaweiService = totalHuaweiService;
 696:                  kuwaitAreaStatistic.NokiaHuaweiService = (kuwaitAreaStatistic.NokiaService + kuwaitAreaStatistic.HuaweiService) + " (" + kuwaitAreaStatistic.NokiaService + "/" + kuwaitAreaStatistic.HuaweiService + ")";
 697:   
 698:   
 699:                  kuwaitAreaStatistic.ServiceRequestService = totalServiceRequestService;
 700:   
 701:                  kuwaitAreaStatistic.AccessCapacity = totalAccessCapacity;
 702:   
 703:                  kuwaitAreaStatistic.NokiaOnt = totalNokiaOnt;
 704:                  kuwaitAreaStatistic.HuaweiOnt = totalHuaweiOnt;
 705:                  kuwaitAreaStatistic.NokiaHuaweiOnt = (totalNokiaOnt + totalHuaweiOnt) + " (" + totalNokiaOnt + "/" + totalHuaweiOnt + ")";
 706:   
 707:                  kuwaitAreaStatistic.NokiaAccess = totalNokiaAccess;
 708:                  kuwaitAreaStatistic.HuaweiAccess = totalHuaweiAccess;
 709:                  kuwaitAreaStatistic.NokiaHuaweiAccess = (totalNokiaAccess + totalHuaweiAccess) + " (" + totalNokiaAccess + "/" + totalHuaweiAccess + ")";
 710:   
 711:                  kuwaitAreaStatistic.AccessReadyForService = totalAccessReadyForService;
 712:   
 713:                  statisticList.Add(kuwaitAreaStatistic);
 714:              }
 715:   
 716:              return statisticList;
 717:          }
 718:   
 719:          ////////////////////////////////////////////////////////////////////////////
 720:   
 721:          /// <summary>
 722:          ///
 723:          /// </summary>
 724:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site> SiteStatistic()
 725:          {
 726:              int siteId;
 727:              int totalAccessCapacity, totalServiceCapacity, totalService, totalNokiaService, totalHuaweiService;
 728:              int totalPstnService, totalEricssonService, totalSiemensService;
 729:              Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site siteStatistic;
 730:              List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site> siteStatisticList;
 731:   
 732:              siteStatisticList = new List<Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site>();
 733:   
 734:              totalService = totalNokiaService = totalHuaweiService = totalAccessCapacity = totalServiceCapacity = 0;
 735:              totalPstnService = totalEricssonService = totalSiemensService = 0;
 736:   
 737:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 738:              {
 739:                  var oltIdToCountOfServicesDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServicesDictionary();
 740:                  var oltList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList;
 741:   
 742:                  var pstnFiveDigitDomainToCountOfServicesDictionary = Ia.Ngn.Cl.Model.Data.Service2.PstnFiveDigitDomainToCountOfServicesDictionary();
 743:                  var pstnToFiveDigitDomainListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.PstnToFiveDigitDomainListDictionary();
 744:   
 745:                  var unknown = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site(); // ("Unknown", );
 746:                  unknown.NameArabicName = "Unknown (غير معرف)";
 747:                  unknown.Name = "Unknown";
 748:   
 749:                  foreach (var site in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList)
 750:                  {
 751:                      siteStatistic = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site();
 752:   
 753:                      siteId = site.Id;
 754:                      siteStatistic.Id = site.Id.ToString();
 755:                      siteStatistic.NameArabicName = site.NameArabicName;
 756:                      siteStatistic.Name = site.Name;
 757:   
 758:                      siteStatistic.KuwaitAreaNameListString = string.Join(", ", site.KuwaitNgnAreas.Select(u => u.ArabicName));
 759:   
 760:                      siteStatistic.PstnDomainListString = string.Join(", ", site.Pstns.SelectMany(u => u.DomainList).ToList());
 761:   
 762:                      siteStatistic.DomainListString = string.Join(", ", site.Routers.SelectMany(u => u.DomainList).ToList());
 763:   
 764:                      siteStatistic.SymbolListString = string.Join(", ", site.AreaSymbolList.ToList());
 765:   
 766:                      siteStatistic.AccessCapacity = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList where o.Odf.Router.Site.Id == siteId select o).Sum(o => o.NumberOfPons * o.NumberOfOntsInPon);
 767:   
 768:                      siteStatistic.ServiceCapacity = site.NumberOfPossibleServicesWithinDomainList;
 769:   
 770:                      foreach (var olt in site.Routers.SelectMany(u => u.Odfs.SelectMany(v => v.Olts)))
 771:                      {
 772:                          if (oltIdToCountOfServicesDictionary.ContainsKey(olt.Id))
 773:                          {
 774:                              if (olt.Odf.Router.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Nokia) siteStatistic.NokiaService += oltIdToCountOfServicesDictionary[olt.Id];
 775:                              else if (olt.Odf.Router.Vendor == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Vendor.Huawei) siteStatistic.HuaweiService += oltIdToCountOfServicesDictionary[olt.Id];
 776:                              else
 777:                              {
 778:                                  unknown.Service++;
 779:                              }
 780:                          }
 781:                      }
 782:   
 783:                      siteStatistic.Service = siteStatistic.NokiaService + siteStatistic.HuaweiService;
 784:                      siteStatistic.NokiaHuaweiService = siteStatistic.Service + " (" + siteStatistic.NokiaService + "/" + siteStatistic.HuaweiService + ")";
 785:   
 786:   
 787:                      foreach (var pstn in site.Pstns)
 788:                      {
 789:                          var list = pstnToFiveDigitDomainListDictionary[pstn];
 790:   
 791:                          foreach (var l in list)
 792:                          {
 793:                              if (pstnFiveDigitDomainToCountOfServicesDictionary.ContainsKey(l.ToString()))
 794:                              {
 795:                                  if (pstn.PstnExchangeType == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.PstnExchangeType.EricssonAxe) siteStatistic.EricssonService += pstnFiveDigitDomainToCountOfServicesDictionary[l.ToString()];
 796:                                  else if (pstn.PstnExchangeType == Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.PstnExchangeType.SiemensEwsd) siteStatistic.SiemensService += pstnFiveDigitDomainToCountOfServicesDictionary[l.ToString()];
 797:                                  else
 798:                                  {
 799:                                      unknown.Service++;
 800:                                  }
 801:                              }
 802:                              else
 803:                              {
 804:   
 805:                              }
 806:                          }
 807:                      }
 808:   
 809:                      siteStatistic.PstnService = siteStatistic.EricssonService + siteStatistic.SiemensService;
 810:                      siteStatistic.EricssonSiemensService = siteStatistic.PstnService + " (" + siteStatistic.EricssonService + "/" + siteStatistic.SiemensService + ")";
 811:   
 812:   
 813:                      // totals:
 814:                      totalService += siteStatistic.Service;
 815:   
 816:                      totalNokiaService += siteStatistic.NokiaService;
 817:                      totalHuaweiService += siteStatistic.HuaweiService;
 818:   
 819:                      totalAccessCapacity += siteStatistic.AccessCapacity;
 820:                      totalServiceCapacity += siteStatistic.ServiceCapacity;
 821:   
 822:   
 823:                      totalPstnService += siteStatistic.PstnService;
 824:   
 825:                      totalEricssonService += siteStatistic.EricssonService;
 826:                      totalSiemensService += siteStatistic.SiemensService;
 827:   
 828:                      siteStatisticList.Add(siteStatistic);
 829:                  }
 830:   
 831:                  siteStatisticList.Add(unknown);
 832:   
 833:                  // below: last totals row
 834:                  siteStatistic = new Ia.Ngn.Cl.Model.Business.Administration.Statistic.Site();
 835:   
 836:                  siteStatistic.NameArabicName = "Total (مجموع)";
 837:                  siteStatistic.Name = "Total";
 838:   
 839:                  siteStatistic.Service = totalService;
 840:                  siteStatistic.NokiaService = totalNokiaService;
 841:                  siteStatistic.HuaweiService = totalHuaweiService;
 842:                  siteStatistic.NokiaHuaweiService = (siteStatistic.NokiaService + siteStatistic.HuaweiService) + " (" + siteStatistic.NokiaService + "/" + siteStatistic.HuaweiService + ")";
 843:   
 844:                  siteStatistic.AccessCapacity = totalAccessCapacity;
 845:                  siteStatistic.ServiceCapacity = totalServiceCapacity;
 846:   
 847:                  siteStatistic.PstnService = totalPstnService;
 848:                  siteStatistic.EricssonService = totalEricssonService;
 849:                  siteStatistic.SiemensService = totalSiemensService;
 850:                  siteStatistic.EricssonSiemensService = (siteStatistic.EricssonService + siteStatistic.SiemensService) + " (" + siteStatistic.EricssonService + "/" + siteStatistic.SiemensService + ")";
 851:   
 852:                  siteStatisticList.Add(siteStatistic);
 853:              }
 854:   
 855:              return siteStatisticList;
 856:          }
 857:   
 858:          ////////////////////////////////////////////////////////////////////////////
 859:   
 860:          /// <summary>
 861:          ///
 862:          /// </summary>
 863:          public class YearMonthProvisionedDeprovisionServiceProvisioningStatistic
 864:          {
 865:              public int Year { get; set; }
 866:   
 867:              public int Month { get; set; }
 868:   
 869:              public string MonthName
 870:              {
 871:                  get
 872:                  {
 873:                      return CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(this.Month);
 874:                  }
 875:              }
 876:   
 877:              public string YearMonth { get { return Year + "-" + Month.ToString().PadLeft(2, '0'); } }
 878:   
 879:              public int Provisioned { get; set; }
 880:   
 881:              public int Deprovisioned { get; set; }
 882:          }
 883:   
 884:          ////////////////////////////////////////////////////////////////////////////
 885:   
 886:          /// <summary>
 887:          ///
 888:          /// </summary>
 889:          public static List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic> ServiceProvisioningInNgnSwitchAndGponNetworkStatistic()
 890:          {
 891:              List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic> list;
 892:   
 893:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 894:              {
 895:                  var sqlServer = new SqlServer();
 896:   
 897:                  var dbtable = sqlServer.Select(@"
 898:  select
 899:  YEAR(sr.RequestDateTime) Year, MONTH(sr.RequestDateTime) Month,
 900:  SUM(case WHEN sr.ServiceId = 1 or sr.ServiceId = 129 or sr.ServiceId = 54 then 1 else 0 end) Provisioned,
 901:  SUM(case WHEN sr.ServiceId = 40 then 1 else 0 end) Deprovisioned 
 902:  --count(srs.Provisioned)
 903:  from ServiceRequestServices srs
 904:  left outer join ServiceRequests sr on srs.Id = sr.ServiceRequestService_Id
 905:  where (sr.ServiceId = 1 or sr.ServiceId = 129 or sr.ServiceId = 54 or sr.ServiceId = 40) 
 906:  group by YEAR(sr.RequestDateTime), MONTH(sr.RequestDateTime)
 907:  order by YEAR(sr.RequestDateTime), MONTH(sr.RequestDateTime)
 908:  ");
 909:   
 910:                  /*
 911:  -- <service id="1" arabicName="خط هاتف" />
 912:  -- <service id="129" arabicName="خط هاتف مع نداء آلي"/>
 913:  -- <service id="54" arabicName="اعادة تركيب" />
 914:  -- <service id="40" arabicName="رفع خط" />
 915:                  *
 916:                   */
 917:   
 918:                  // I tried every possible way to use LINQ but failed. As a last resort I used SqlServer directly
 919:   
 920:                  list = new List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic>();
 921:   
 922:                  foreach (DataRow row in dbtable.Rows)
 923:                  {
 924:                      var item = new YearMonthProvisionedDeprovisionServiceProvisioningStatistic();
 925:   
 926:                      item.Year = int.Parse(row["Year"].ToString());
 927:                      item.Month = int.Parse(row["Month"].ToString());
 928:                      item.Provisioned = int.Parse(row["Provisioned"].ToString());
 929:                      item.Deprovisioned = int.Parse(row["Deprovisioned"].ToString());
 930:   
 931:                      list.Add(item);
 932:                  }
 933:              }
 934:   
 935:              return list;
 936:          }
 937:   
 938:          ////////////////////////////////////////////////////////////////////////////
 939:   
 940:          /// <summary>
 941:          ///
 942:          /// </summary>
 943:          public static List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic> ServiceProvisioningInNgnSwitchNetworkStatistic()
 944:          {
 945:              List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic> list;
 946:   
 947:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
 948:              {
 949:                  var sqlServer = new SqlServer();
 950:   
 951:                  var dbtable = sqlServer.Select(@"
 952:  select
 953:  YEAR(sr.RequestDateTime) Year, MONTH(sr.RequestDateTime) Month,
 954:  SUM(case WHEN sr.ServiceId = 1 or sr.ServiceId = 129 or sr.ServiceId = 54 then 1 else 0 end) Provisioned,
 955:  SUM(case WHEN sr.ServiceId = 40 then 1 else 0 end) Deprovisioned 
 956:  --count(srs.Provisioned)
 957:  from ServiceRequests sr
 958:  where (sr.ServiceId = 1 or sr.ServiceId = 129 or sr.ServiceId = 54 or sr.ServiceId = 40) 
 959:  group by YEAR(sr.RequestDateTime), MONTH(sr.RequestDateTime)
 960:  order by YEAR(sr.RequestDateTime), MONTH(sr.RequestDateTime)
 961:  ");
 962:   
 963:                  /*
 964:  -- <service id="1" arabicName="خط هاتف" />
 965:  -- <service id="129" arabicName="خط هاتف مع نداء آلي"/>
 966:  -- <service id="54" arabicName="اعادة تركيب" />
 967:  -- <service id="40" arabicName="رفع خط" />
 968:                  *
 969:                   */
 970:   
 971:                  // I tried every possible way to use LINQ but failed. As a last resort I used SqlServer directly
 972:   
 973:                  list = new List<YearMonthProvisionedDeprovisionServiceProvisioningStatistic>();
 974:   
 975:                  foreach (DataRow row in dbtable.Rows)
 976:                  {
 977:                      var item = new YearMonthProvisionedDeprovisionServiceProvisioningStatistic();
 978:   
 979:                      item.Year = int.Parse(row["Year"].ToString());
 980:                      item.Month = int.Parse(row["Month"].ToString());
 981:                      item.Provisioned = int.Parse(row["Provisioned"].ToString());
 982:                      item.Deprovisioned = int.Parse(row["Deprovisioned"].ToString());
 983:   
 984:                      list.Add(item);
 985:                  }
 986:              }
 987:   
 988:              return list;
 989:          }
 990:   
 991:          /*
 992:          ////////////////////////////////////////////////////////////////////////////
 993:  
 994:          /// <summary>
 995:          ///
 996:          /// </summary>
 997:          public static List<Ia.Ngn.Cl.Model.Business.Administration.Statistic> PhoneStatistic(string timePeriod)
 998:          {
 999:              List<Ia.Ngn.Cl.Model.Business.Administration.Statistic> phoneStatisticList;
1000:  
1001:              /*
1002:              string s, where;
1003:              DateTime from, to;
1004:              DataTable dt;
1005:  
1006:              if (timePeriod != null)
1007:              {
1008:                  from = DateTime.Parse(timePeriod);
1009:                  to = DateTime.Parse(timePeriod);
1010:                  to = to.AddMonths(1);
1011:  
1012:                  where = " AND (sr.request_time >= '" + sqlserver.SmallDateTime(from) + "' AND sr.request_time < '" + sqlserver.SmallDateTime(to) + "') ";
1013:              }
1014:              else where = string.Empty;
1015:               * /
1016:  
1017:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1018:              {
1019:                  phoneStatisticList = (from a in Ia.Ngn.Cl.Model.Data.Service.KuwaitNgnAreaList
1020:                       group a by a.Id into grp
1021:                       orderby grp.Key
1022:                       select new Ia.Ngn.Cl.Model.Business.Administration.Statistic()
1023:                       {
1024:                           Id = grp.Key.ToString(),
1025:                           Name = grp.SingleOrDefault().NameArabicName,
1026:                           //ServiceRequests = (from sr in db.ServiceRequests where sr.AreaId == grp.Key && sr.ServiceRequestService != null select sr.Id).Count().ToString(),
1027:                           ServiceRequestServices = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key select srs.Id).Count(),
1028:                           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(),
1029:                           InternationalCalling = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.InternationalCalling == true select srs.Id).Count(),
1030:                           InternationalCallingUserControlled = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.InternationalCallingUserControlled == true select srs.Id).Count(),
1031:                           CallWaiting = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.CallWaiting == true select srs.Id).Count(),
1032:  
1033:                           AlarmCall = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.AlarmCall == true select srs.Id).Count(),
1034:  
1035:                           CallBarring = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.CallBarring == true select srs.Id).Count(),
1036:                           CallerId = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.CallerId == true select srs.Id).Count(),
1037:                           CallForwarding = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.CallForwarding == true select srs.Id).Count(),
1038:                           ConferenceCall = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.ConferenceCall == true select srs.Id).Count(),
1039:                           ServiceSuspension = (from srs in db.ServiceRequestServices where srs.Access != null && srs.Access.AreaId == grp.Key && srs.ServiceSuspension == true select srs.Id).Count()
1040:                       }).ToList();
1041:  
1042:                  /*
1043:                  if (dt != null)
1044:                  {
1045:                      // below: the totals rows
1046:                      dr = dt.NewRow();
1047:  
1048:                      dr["SRS_AccessIdNotNull"] = dt.Compute("SUM (SRS_AccessIdNotNull)", "").ToString();
1049:                      dr["IMS_AccessIdNotNull"] = dt.Compute("SUM (IMS_AccessIdNotNull)", "").ToString();
1050:                      dr["SRS_AccordingToAreaIdFromDomain"] = dt.Compute("SUM (SRS_AccordingToAreaIdFromDomain)", "").ToString();
1051:  
1052:                      dr["InternationalCalling"] = dt.Compute("SUM (InternationalCalling)", "").ToString();
1053:                      dr["InternationalCallingUserControlled"] = dt.Compute("SUM (InternationalCallingUserControlled)", "").ToString();
1054:                      dr["CallWaiting"] = dt.Compute("SUM (CallWaiting)", "").ToString();
1055:                      dr["AlarmCall"] = dt.Compute("SUM (AlarmCall)", "").ToString();
1056:                      dr["CallBarring"] = dt.Compute("SUM (CallBarring)", "").ToString();
1057:                      dr["CallerId"] = dt.Compute("SUM (CallerId)", "").ToString();
1058:                      dr["CallForwarding"] = dt.Compute("SUM (CallForwarding)", "").ToString();
1059:                      dr["ConferenceCall"] = dt.Compute("SUM (ConferenceCall)", "").ToString();
1060:                      dr["ServiceSuspension"] = dt.Compute("SUM (ServiceSuspension)", "").ToString();
1061:  
1062:                      dt.Rows.Add(dr);
1063:                  }
1064:                   * /
1065:              }
1066:  
1067:              return phoneStatisticList;
1068:          }
1069:          */
1070:   
1071:          ////////////////////////////////////////////////////////////////////////////
1072:   
1073:          /// <summary>
1074:          ///
1075:          /// </summary>
1076:          public static List<Ia.Ngn.Cl.Model.Ui.Performance> StaffAndFrameworkPerformanceReport(int daysAgo)
1077:          {
1078:              DateTime startDateTime;
1079:              List<Ia.Ngn.Cl.Model.Ui.Performance> performanceList;
1080:   
1081:              // after 2015-06-01 user report closer inserts a last historic report
1082:              // I should designate last report as CLOSED and add it to resolution list to be accessed by HEAD only.
1083:   
1084:              // if daysAgo is 0 will make it 9999 days to cover all times
1085:              startDateTime = DateTime.UtcNow.AddDays(daysAgo == 0 ? -9999 : -daysAgo);
1086:   
1087:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1088:              {
1089:                  performanceList =
1090:                      (
1091:                      from staff in (from s in db.Staffs group s.UserId by s.UserId into g select new { UserId = g.Key, Count = g.Count() })
1092:   
1093:                      join resolved in (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() })
1094:                      on staff.UserId equals resolved.UserId into resolved_gj
1095:                      from re in resolved_gj.DefaultIfEmpty()
1096:   
1097:                      join attempted in (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() })
1098:                      on staff.UserId equals attempted.UserId into attempted_gj
1099:                      from at in attempted_gj.DefaultIfEmpty()
1100:   
1101:                      join inserted in
1102:                          (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() })
1103:                      on staff.UserId equals inserted.UserId into inserted_gj
1104:                      from ins in inserted_gj.DefaultIfEmpty()
1105:   
1106:                      join open in
1107:                          (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() })
1108:                      on staff.UserId equals open.UserId into open_gj
1109:                      from opn in open_gj.DefaultIfEmpty()
1110:   
1111:                          // below: the 20, 10, 1 give weight to the field
1112:                      orderby re.Count descending, at.Count descending, ins.Count descending
1113:   
1114:                      select new Ia.Ngn.Cl.Model.Ui.Performance
1115:                      {
1116:                          UserId = staff.UserId,
1117:                          Resolved = (re == null ? 0 : re.Count),
1118:                          Attempted = (at == null ? 0 : at.Count),
1119:                          Inserted = (ins == null ? 0 : ins.Count),
1120:                          Open = (ins == null ? 0 : opn.Count),
1121:                          AverageReportsPerDay = 0
1122:                      }).ToList();
1123:   
1124:                  /*
1125:          select users.UserId, resolved.count,attempted.count,inserted.count from
1126:          (
1127:          (select count(*) as count, UserId from Users group by UserId) as users
1128:          left outer join
1129:          (select count(*) as count, rh.UserId from ReportHistories as rh where rh.Resolution = 1020 group by rh.UserId) as resolved
1130:          on users.UserId = resolved.UserId
1131:          left outer join
1132:          (select count(*) as count, rh.UserId from ReportHistories as rh where rh.Resolution <> 1020 group by rh.UserId) as attempted
1133:          on users.UserId = attempted.UserId
1134:          left outer join
1135:          (select count(*) as count, UserId from Reports group by UserId) as inserted
1136:          on users.UserId = inserted.UserId
1137:          )
1138:          order by resolved.count*20+attempted.count*10+inserted.count desc
1139:              */
1140:              }
1141:   
1142:              // below: exclude staff who did absolutly nothing: Resolved = Attempted = Inserted = 0
1143:   
1144:              performanceList = (from p in performanceList where (p.Inserted != 0 || p.Attempted != 0 || p.Resolved != 0) select p).ToList();
1145:   
1146:              foreach (var p in performanceList)
1147:              {
1148:                  if (p.UserId == Guid.Empty) p.FirstAndMiddleName = "غير معرف";
1149:                  else
1150:                  {
1151:                      p.FirstAndMiddleName = (from s in Ia.Ngn.Cl.Model.Data.Staff.List where s.UserId == p.UserId select s.FirstAndMiddleName).SingleOrDefault();
1152:                  }
1153:              }
1154:   
1155:              return performanceList;
1156:          }
1157:   
1158:          ////////////////////////////////////////////////////////////////////////////
1159:   
1160:          /// <summary>
1161:          ///
1162:          /// </summary>
1163:          public static List<Ia.Ngn.Cl.Model.Ui.Performance> StatisticsOfResolvedAndAttemptedAndInsertedStaffReport2(Guid userId)
1164:          {
1165:              List<Ia.Ngn.Cl.Model.Ui.Performance> performanceList;
1166:   
1167:              // after 2015-06-01 user report closer inserts a last historic report
1168:              // I should designate last report as CLOSED and add it to resolution list to be accessed by HEAD only.
1169:   
1170:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1171:              {
1172:                  performanceList =
1173:                      (
1174:                      from staff in
1175:                          (from s in db.Staffs group s.UserId by s.UserId into g select new { UserId = g.Key, Count = g.Count() })
1176:                      join resolved in
1177:                          (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() })
1178:                      on staff.UserId equals resolved.UserId into resolved_gj
1179:                      from re in resolved_gj.DefaultIfEmpty()
1180:   
1181:                      join attempted in
1182:                          (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() })
1183:                      on staff.UserId equals attempted.UserId into attempted_gj
1184:                      from at in attempted_gj.DefaultIfEmpty()
1185:   
1186:                      join inserted in
1187:                          (from r in db.Reports group r.UserId by r.UserId into g select new { UserId = g.Key, Count = g.Count() })
1188:                      on staff.UserId equals inserted.UserId into inserted_gj
1189:                      from ins in inserted_gj.DefaultIfEmpty()
1190:                          // below: the 20, 10, 1 give weight to the field
1191:                      orderby re.Count descending, at.Count descending, ins.Count descending
1192:                      select new Ia.Ngn.Cl.Model.Ui.Performance
1193:                      {
1194:                          UserId = staff.UserId,
1195:                          FirstAndMiddleName = (from s in Ia.Ngn.Cl.Model.Data.Staff.List where s.UserId == staff.UserId select s.FirstAndMiddleName).SingleOrDefault(),
1196:                          Resolved = (re == null ? 0 : re.Count),
1197:                          Attempted = (at == null ? 0 : at.Count),
1198:                          Inserted = (ins == null ? 0 : ins.Count),
1199:                          AverageReportsPerDay = 0
1200:                      }).ToList();
1201:   
1202:                  /*
1203:          select users.UserId, resolved.count,attempted.count,inserted.count from
1204:          (
1205:          (select count(*) as count, UserId from Users group by UserId) as users
1206:          left outer join
1207:          (select count(*) as count, rh.UserId from ReportHistories as rh where rh.Resolution = 1020 group by rh.UserId) as resolved
1208:          on users.UserId = resolved.UserId
1209:          left outer join
1210:          (select count(*) as count, rh.UserId from ReportHistories as rh where rh.Resolution <> 1020 group by rh.UserId) as attempted
1211:          on users.UserId = attempted.UserId
1212:          left outer join
1213:          (select count(*) as count, UserId from Reports group by UserId) as inserted
1214:          on users.UserId = inserted.UserId
1215:          )
1216:          order by resolved.count*20+attempted.count*10+inserted.count desc
1217:              */
1218:              }
1219:   
1220:              return performanceList.ToList();
1221:          }
1222:   
1223:          ////////////////////////////////////////////////////////////////////////////
1224:   
1225:          /// <summary>
1226:          ///
1227:          /// </summary>
1228:          public DataTable CountOfActiveNumbersInArea()
1229:          {
1230:              return CountOfActiveNumbersInAreaByTimePeriod(null);
1231:          }
1232:   
1233:          ////////////////////////////////////////////////////////////////////////////
1234:   
1235:          /// <summary>
1236:          ///
1237:          /// </summary>
1238:          public static DataTable CountOfActiveNumbersInAreaByTimePeriod(string timePeriod)
1239:          {
1240:              string s, where;
1241:              DateTime from, to;
1242:              DataTable dt;
1243:   
1244:              if (timePeriod != null)
1245:              {
1246:                  from = DateTime.Parse(timePeriod);
1247:                  to = DateTime.Parse(timePeriod);
1248:                  to = to.AddMonths(1);
1249:   
1250:                  where = null; // " AND (sr.request_time >= '" + sqlserver.SmallDateTime(from) + "' AND sr.request_time < '" + sqlserver.SmallDateTime(to) + "') ";
1251:              }
1252:              else where = string.Empty;
1253:   
1254:              s = @"SELECT COUNT(1) AS count, f.area
1255:  FROM         ia_system AS s INNER JOIN
1256:                        ia_protocol AS p ON s.lceid = p.lceid AND s.lan = p.lan INNER JOIN
1257:                        ia_standard AS st ON st.ip = p.ip INNER JOIN
1258:                        ia_field AS f ON f.id = st.id LEFT OUTER JOIN
1259:                        ia_service_request_service AS srs ON srs.dn = s.dn LEFT OUTER JOIN
1260:                        ia_service_request AS sr ON sr.id = srs.ia_service_request_id
1261:  WHERE f.area != 0 " + where + @" GROUP BY f.area ";
1262:   
1263:              dt = null; // sqlserver.Select(s);
1264:   
1265:              return dt;
1266:          }
1267:   
1268:          ////////////////////////////////////////////////////////////////////////////    
1269:   
1270:          /// <summary>
1271:          ///
1272:          /// </summary>
1273:          public static Dictionary<string, int> DateTimesWithAvailableData()
1274:          {
1275:              Dictionary<string, int> dic;
1276:   
1277:   
1278:              dic = new Dictionary<string, int>(100);
1279:   
1280:   
1281:              using (var db = new Ia.Ngn.Cl.Model.Ngn())
1282:              {
1283:                  //dic = (from q in db.ServiceRequests orderby q.RequestDateTime select q.RequestDateTime).Distinct().ToDictionary(r => r.CustomerName, r => r.Id);
1284:   
1285:                  // 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);
1286:   
1287:              }
1288:   
1289:              /*
1290:          SELECT DISTINCT CONVERT(varchar(7), RequestDateTime, 102) AS date, COUNT(1) AS count
1291:          FROM [Ia_Ngn].[dbo].[ServiceRequests]
1292:          GROUP BY CONVERT(varchar(7), RequestDateTime, 102)
1293:          ORDER BY date
1294:               */
1295:   
1296:              return dic;
1297:          }
1298:   
1299:          ////////////////////////////////////////////////////////////////////////////
1300:          ////////////////////////////////////////////////////////////////////////////
1301:   
1302:          /// <summary>
1303:          /// 
1304:          /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
1305:          /// 
1306:          /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
1307:          /// 2. Add "using System.Reflection".
1308:          /// 3. See sample below.
1309:          /// 
1310:          /// </summary>
1311:   
1312:          private static XDocument XDocument
1313:          {
1314:              get
1315:              {
1316:                  Assembly _assembly;
1317:                  StreamReader streamReader;
1318:   
1319:                  if (xDocument == null)
1320:                  {
1321:                      _assembly = Assembly.GetExecutingAssembly();
1322:                      streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.administration.xml"));
1323:   
1324:                      try
1325:                      {
1326:                          if (streamReader.Peek() != -1) xDocument = System.Xml.Linq.XDocument.Load(streamReader);
1327:                      }
1328:                      catch (Exception)
1329:                      {
1330:                      }
1331:                      finally
1332:                      {
1333:                      }
1334:                  }
1335:   
1336:                  return xDocument;
1337:              }
1338:          }
1339:   
1340:          ////////////////////////////////////////////////////////////////////////////
1341:          ////////////////////////////////////////////////////////////////////////////    
1342:      }
1343:   
1344:      ////////////////////////////////////////////////////////////////////////////
1345:      ////////////////////////////////////////////////////////////////////////////   
1346:  }