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

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