)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Skip Navigation LinksHome » Code Library » Administration

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

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

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