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