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: }