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