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

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