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

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