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