1: using Microsoft.EntityFrameworkCore;
2: using System;
3: using System.Collections;
4: using System.Collections.Generic;
5: using System.Data;
6: using System.Linq;
7: using System.Text;
8:
9: namespace Ia.Ngn.Cl.Model.Data
10: {
11: ////////////////////////////////////////////////////////////////////////////
12:
13: /// <summary publish="true">
14: /// Default support class for Optical Fiber Network (OFN) data model.
15: /// </summary>
16: ///
17: /// <remarks>
18: /// Copyright © 2006-2020 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
19: ///
20: /// 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
21: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
22: ///
23: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
24: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
25: ///
26: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
27: ///
28: /// Copyright notice: This notice may not be removed or altered from any source distribution.
29: /// </remarks>
30: public class Default
31: {
32: private static StringBuilder logStringBuilder = new StringBuilder();
33:
34: ////////////////////////////////////////////////////////////////////////////
35:
36: /// <summary>
37: ///
38: /// </summary>
39: public Default() { }
40:
41: ////////////////////////////////////////////////////////////////////////////
42:
43: /// <summary>
44: ///
45: /// </summary>
46: public static List<string> NewOntEventAndAccessBySerialInNoteList()
47: {
48: List<string> list;
49:
50: list = new List<string>();
51:
52: var bList = Ia.Ngn.Cl.Model.Data.Event.NewOntList;
53:
54: //var aList = Ia.Ngn.Cl.Model.Data.Access.BySerialInNote();
55:
56:
57: return list;
58: }
59:
60: ////////////////////////////////////////////////////////////////////////////
61:
62: /// <summary>
63: ///
64: /// </summary>
65: public static List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> DifferentOntNameAndStatisticalOntNameList(int siteId)
66: {
67: string address;
68: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
69: Ia.Ngn.Cl.Model.Access statisticalAccess;
70: Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName serviceCustomerAddressAccessStatisticalAccessName;
71:
72: var level = string.Empty;
73:
74: var site = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList
75: where s.Id == siteId
76: select s).SingleOrDefault();
77:
78:
79: var areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
80:
81: var list = new List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName>();
82:
83: using (var db = new Ia.Ngn.Cl.Model.Ngn())
84: {
85: var siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList
86: where r.Site.Id == siteId
87: select r).SelectMany(z => z.DomainList).ToList();
88:
89: var hashTable = Ia.Ngn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);
90:
91: var serviceRequestServiceServiceAccessList = (from srs in db.ServiceRequestServices
92: select new { srs.Service, srs.Access }).AsNoTracking().ToList();
93:
94: serviceRequestServiceServiceAccessList = (from srs in serviceRequestServiceServiceAccessList
95: where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString()))
96: select srs).ToList();
97:
98: var areaIdList = site.KuwaitNgnAreas.Select(i => i.Id).ToList();
99:
100: /*
101: var accessList = (from a in db.Accesses
102: where areaIdList.Contains(a.AreaId)
103: select a).AsNoTracking().ToList();
104: */
105:
106: foreach (var srs in serviceRequestServiceServiceAccessList)
107: {
108: serviceCustomerAddressAccessStatisticalAccessName = new Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName();
109:
110: if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();
111: else address = null;
112:
113: serviceCustomerAddressAccessStatisticalAccessName.Service = srs.Service;
114: serviceCustomerAddressAccessStatisticalAccessName.CustomerAddress = address;
115: serviceCustomerAddressAccessStatisticalAccessName.Access = srs.Access;
116:
117: if (srs.Access != null) serviceCustomerAddressAccessStatisticalAccessName.OntAddress = srs.Access.Address;
118:
119: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(srs.Service, address, out level);
120:
121: // below: this will skip Sabah Al-Salem area
122: //if (l.CustomerAddress != null && !l.CustomerAddress.Contains("صباح"))
123: //{
124: statisticalAccess = null; // Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList);
125:
126: if (statisticalAccess != null)
127: {
128: serviceCustomerAddressAccessStatisticalAccessName.Block = statisticalAccess.Block;
129: serviceCustomerAddressAccessStatisticalAccessName.Street = statisticalAccess.Street;
130: serviceCustomerAddressAccessStatisticalAccessName.PremisesOld = statisticalAccess.PremisesOld;
131: serviceCustomerAddressAccessStatisticalAccessName.PremisesNew = statisticalAccess.PremisesNew;
132: serviceCustomerAddressAccessStatisticalAccessName.KuwaitNgnAreaNameArabicName = areaIdToNameArabicNameDictionary[statisticalAccess.AreaId];
133:
134: serviceCustomerAddressAccessStatisticalAccessName.StatisticalAccessName = statisticalAccess.Name;
135:
136: /*
137: if (nokiaInitialInstallationListHashTable.ContainsKey(statisticalAccess.Id))
138: {
139: l.Note = nokiaInitialInstallationListHashTable[statisticalAccess.Id].ToString();
140: }
141: */
142: //else l.Note = "No: " + srs.Access.Id;
143: }
144:
145: if (serviceAddress.AreaId != 0)
146: {
147: serviceCustomerAddressAccessStatisticalAccessName.StatisticalAddress = serviceAddress.Address;
148:
149: if (srs.Access != null) serviceCustomerAddressAccessStatisticalAccessName.AccessName = serviceCustomerAddressAccessStatisticalAccessName.Access.Name;
150:
151: //if (l.OntName != null && l.OntName == l.StatisticalOntName) l.Note = Ia.Cl.Model.Default.YesNo(true);
152: //else l.Note = Ia.Cl.Model.Default.YesNo(false);
153:
154: //l.Note += " (" + level + ")";
155: }
156: else
157: {
158: //l.Note = Ia.Cl.Model.Default.YesNo(false);
159:
160: //l.Note += " (AreaId zero)";
161: }
162:
163: list.Add(serviceCustomerAddressAccessStatisticalAccessName);
164: //}
165: //else
166: //{
167:
168: //}
169: }
170: }
171:
172: return list;
173: }
174:
175: ////////////////////////////////////////////////////////////////////////////
176:
177: /// <summary>
178: ///
179: /// </summary>
180: public static List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> DifferentOntNameAndStatisticalOntNameList2(int siteId)
181: {
182: string address, level;
183: Hashtable hashTable; //, nokiaInitialInstallationListHashTable;
184: Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
185: Ia.Ngn.Cl.Model.Business.ServiceAddress serviceAddress;
186: Ia.Ngn.Cl.Model.Access statisticalAccess;
187: Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName l;
188:
189: List<int> siteRouterDomainList, areaIdList;
190: List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName> list;
191: //List<Ia.Ngn.Cl.Model.ServiceRequest> serviceRequestList;
192: //List<Ia.Ngn.Cl.Model.Ui.Access> accessList;
193: Dictionary<int, string> areaIdToNameArabicNameDictionary;
194:
195: level = string.Empty;
196: areaIdList = new List<int>();
197:
198: site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList
199: where a.Id == siteId
200: select a).SingleOrDefault();
201:
202: var ontAccessIdToOntAccessNameDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
203:
204: areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
205:
206: using (var db = new Ia.Ngn.Cl.Model.Ngn())
207: {
208: siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList
209: where r.Site.Id == siteId
210: select r).SelectMany(z => z.DomainList).ToList();
211: //siteRouterDomainList = new List<int> { 2538 };
212:
213: //serviceRequestList = (from sr in db.ServiceRequests where siteRouterDomainList.Contains(sr.Number / 10000) || siteRouterDomainList.Contains(sr.Number / 1000) select sr).ToList();
214:
215: hashTable = Ia.Ngn.Cl.Model.Data.ServiceRequest.NumberToCustomerAddressHashtable(siteRouterDomainList);
216:
217: var serviceRequestServiceList = (from srs in db.ServiceRequestServices
218: where siteRouterDomainList.Any(u => srs.Service.StartsWith(u.ToString()))
219: select new { srs.Service, AccessId = (srs.Access != null) ? srs.Access.Id : string.Empty }).ToList();
220:
221: areaIdList = site.KuwaitNgnAreas.Select(i => i.Id).ToList();
222: //areaIdList = new List<int> { 60618 };
223:
224: //accessList = null;// (from a in db.Accesses where areaIdList.Contains(a.AreaId) select a).ToList();
225:
226: list = new List<Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName>();
227:
228: foreach (var srs in serviceRequestServiceList)
229: {
230: l = new Ia.Ngn.Cl.Model.Ui.ServiceCustomerAddressAccessStatisticalAccessName();
231:
232: if (hashTable[srs.Service] != null) address = hashTable[srs.Service].ToString();
233: else address = null;
234:
235: l.Service = srs.Service;
236: l.CustomerAddress = address;
237:
238: if (ontAccessIdToOntAccessNameDictionary.ContainsKey(srs.AccessId)) l.AccessId = srs.AccessId;
239: else l.Access = null;
240:
241: if (!string.IsNullOrEmpty(srs.AccessId)) l.OntAddress = string.Empty;// srs.Access.Address;
242:
243: //------------------
244: serviceAddress = Ia.Ngn.Cl.Model.Business.ServiceRequest.ServiceAddress(srs.Service, address, out level);
245:
246: statisticalAccess = null;// Ia.Ngn.Cl.Model.Data.Access.StatisticalAccess(serviceAddress, ref accessList);
247:
248: if (statisticalAccess != null)
249: {
250: l.Block = statisticalAccess.Block;
251: l.Street = statisticalAccess.Street;
252: l.PremisesOld = statisticalAccess.PremisesOld;
253: l.PremisesNew = statisticalAccess.PremisesNew;
254: l.KuwaitNgnAreaNameArabicName = areaIdToNameArabicNameDictionary[statisticalAccess.AreaId];
255:
256: l.StatisticalAccessName = string.Empty;// statisticalAccess.Name;
257:
258: /*
259: if (nokiaInitialInstallationListHashTable.ContainsKey(statisticalAccess.Id))
260: {
261: l.Note = nokiaInitialInstallationListHashTable[statisticalAccess.Id].ToString();
262: }
263: */
264: //else l.Note = "No: " + srs.Access.Id;
265: }
266:
267: if (serviceAddress.AreaId != 0)
268: {
269: l.StatisticalAddress = string.Empty;// serviceAddress.Address;
270:
271: if (!string.IsNullOrEmpty(srs.AccessId))
272: {
273: l.AccessName = string.Empty;// l.Access.Name;
274: }
275:
276: //if (l.OntName != null && l.OntName == l.StatisticalOntName) l.Note = Ia.Cl.Model.Default.YesNo(true);
277: //else l.Note = Ia.Cl.Model.Default.YesNo(false);
278:
279: //l.Note += " (" + level + ")";
280: }
281: else
282: {
283: //l.Note = Ia.Cl.Model.Default.YesNo(false);
284:
285: //l.Note += " (AreaId zero)";
286: }
287:
288: list.Add(l);
289: }
290: }
291:
292: return list;
293: }
294:
295: ////////////////////////////////////////////////////////////////////////////
296:
297: /// <summary>
298: ///
299: /// </summary>
300: public static void SiteSerivceAndEmsOntAndAccessAndServiceRequestAndServiceRequestTypeInformation(int siteId, out StringBuilder sb)
301: {
302: int areaId, customerId;
303: string s, accessId;
304: string service, alias, area, block, street, premisesOld, paci, customerName, customerAddress, typeId1, typeId2, typeId4, typeId5;
305:
306: List<int> siteRouterDomainList, areaIdList;
307: List<string> serviceList;
308: Dictionary<int, string> areaIdToNameArabicNameDictionary;
309:
310: sb = new StringBuilder();
311:
312: areaIdList = new List<int>();
313:
314: /*
315: Service2S ONTs(EMS) Accesses Accesses Accesses Accesses Accesses Service Requests Service Requests Service Requests Service Requests Types Service Requests Service Requests Service Requests
316: Service ALIAS Area Block Street Premises Old PACI Customer ID Customer Name Address Type ID-1 Type ID-2 Type ID 4 Type ID 5
317: */
318:
319: var site = (from a in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where a.Id == siteId select a).SingleOrDefault();
320:
321: siteRouterDomainList = (from r in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.RouterList where r.Site.Id == siteId select r).SelectMany(z => z.DomainList).ToList();
322:
323: areaIdToNameArabicNameDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToNameArabicNameDictionary;
324:
325: serviceList = Ia.Ngn.Cl.Model.Data.Service2.List(siteRouterDomainList);
326:
327: var serviceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceToAccessIdDictionary;
328:
329: var idToAccessDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToAccessDictionary;
330:
331: var accessIdToOntDictionary = Ia.Ngn.Cl.Model.Data.Huawei.Ont.AccessIdToOntDictionary;
332:
333: var serviceToServiceRequestServiceDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ServiceToServiceRequestServiceDictionary(siteRouterDomainList);
334:
335: var serviceToServiceRequestDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequest.ServiceToServiceRequestDictionary(siteRouterDomainList);
336:
337:
338: s = "Service\tALIAS\tArea\tBlock\tStreet\tPremises Old\tPACI\tCustomer ID\tCustomer Name\tAddress\tType ID-1\tType ID-2\tType ID 4\tType ID 5";
339: sb.AppendLine(s);
340:
341: foreach (string u in serviceList)
342: {
343: service = u;
344:
345: if (serviceToAccessIdDictionary.ContainsKey(u))
346: {
347: accessId = serviceToAccessIdDictionary[u];
348:
349: if (idToAccessDictionary.ContainsKey(accessId))
350: {
351: areaId = idToAccessDictionary[accessId].AreaId;
352: block = idToAccessDictionary[accessId].Block;
353: street = idToAccessDictionary[accessId].Street;
354: premisesOld = idToAccessDictionary[accessId].PremisesOld;
355: paci = idToAccessDictionary[accessId].Paci;
356:
357: if (areaIdToNameArabicNameDictionary.ContainsKey(areaId)) area = areaIdToNameArabicNameDictionary[areaId];
358: else area = string.Empty;
359:
360: if (accessIdToOntDictionary.ContainsKey(accessId))
361: {
362: alias = accessIdToOntDictionary[accessId].ALIAS;
363: }
364: else
365: {
366: alias = string.Empty;
367: }
368: }
369: else
370: {
371: block = string.Empty;
372: street = string.Empty;
373: premisesOld = string.Empty;
374: paci = string.Empty;
375: area = string.Empty;
376:
377: alias = string.Empty;
378: }
379: }
380: else
381: {
382: block = string.Empty;
383: street = string.Empty;
384: premisesOld = string.Empty;
385: paci = string.Empty;
386: area = string.Empty;
387:
388: alias = string.Empty;
389: }
390:
391:
392: if (serviceToServiceRequestServiceDictionary.ContainsKey(u))
393: {
394: if (serviceToServiceRequestServiceDictionary[u].Provisioned)
395: {
396: if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.Count > 0)
397: {
398: //var bb = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault();
399:
400: if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault() != null)
401: {
402: customerId = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerId;
403: customerName = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerName;
404: customerAddress = serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().CustomerAddress;
405:
406: if (serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes.Count > 0)
407: {
408: var vv = (from srt in serviceToServiceRequestServiceDictionary[u].ServiceRequests.OrderByDescending(y => y.Id).FirstOrDefault().ServiceRequestTypes
409: group srt by new { srt.TypeId, srt.Value } into g
410: select new //Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
411: {
412: TypeId1Value = g.Max(v => v.TypeId == 1 ? v.Value : ""),
413: TypeId2Value = g.Max(v => v.TypeId == 2 ? v.Value : ""),
414: TypeId4Value = g.Max(v => v.TypeId == 4 ? v.Value : ""),
415: TypeId5Value = g.Max(v => v.TypeId == 5 ? v.Value : ""),
416: }).ToList();
417:
418: typeId1 = string.Empty;
419: typeId2 = string.Empty;
420: typeId4 = string.Empty;
421: typeId5 = string.Empty;
422:
423: foreach (var w in vv)
424: {
425: if (!string.IsNullOrEmpty(w.TypeId1Value)) typeId1 = w.TypeId1Value;
426: if (!string.IsNullOrEmpty(w.TypeId2Value)) typeId2 = w.TypeId2Value;
427: if (!string.IsNullOrEmpty(w.TypeId4Value)) typeId4 = w.TypeId4Value;
428: if (!string.IsNullOrEmpty(w.TypeId5Value)) typeId5 = w.TypeId5Value;
429: }
430: }
431: else
432: {
433: customerId = 0;
434: customerName = string.Empty;
435: customerAddress = string.Empty;
436:
437: typeId1 = string.Empty;
438: typeId2 = string.Empty;
439: typeId4 = string.Empty;
440: typeId5 = string.Empty;
441: }
442: }
443: else
444: {
445: customerId = 0;
446: customerName = string.Empty;
447: customerAddress = string.Empty;
448:
449: typeId1 = string.Empty;
450: typeId2 = string.Empty;
451: typeId4 = string.Empty;
452: typeId5 = string.Empty;
453: }
454: }
455: else
456: {
457: customerId = 0;
458: customerName = string.Empty;
459: customerAddress = string.Empty;
460:
461: typeId1 = string.Empty;
462: typeId2 = string.Empty;
463: typeId4 = string.Empty;
464: typeId5 = string.Empty;
465: }
466: }
467: else
468: {
469: customerId = 0;
470: customerName = string.Empty;
471: customerAddress = string.Empty;
472:
473: typeId1 = string.Empty;
474: typeId2 = string.Empty;
475: typeId4 = string.Empty;
476: typeId5 = string.Empty;
477: }
478: }
479: else
480: {
481: if (serviceToServiceRequestDictionary.ContainsKey(u))
482: {
483: customerId = serviceToServiceRequestDictionary[u].CustomerId;
484: customerName = serviceToServiceRequestDictionary[u].CustomerName;
485: customerAddress = serviceToServiceRequestDictionary[u].CustomerAddress;
486:
487: if (serviceToServiceRequestDictionary[u].ServiceRequestTypes.Count > 0)
488: {
489: var vv = (from srt in serviceToServiceRequestDictionary[u].ServiceRequestTypes
490: group srt by new { srt.TypeId, srt.Value } into g
491: select new //Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
492: {
493: TypeId1Value = g.Max(v => v.TypeId == 1 ? v.Value : ""),
494: TypeId2Value = g.Max(v => v.TypeId == 2 ? v.Value : ""),
495: TypeId4Value = g.Max(v => v.TypeId == 4 ? v.Value : ""),
496: TypeId5Value = g.Max(v => v.TypeId == 5 ? v.Value : ""),
497: }).FirstOrDefault(); //.SingleOrDefault();
498:
499: typeId1 = vv.TypeId1Value;
500: typeId2 = vv.TypeId2Value;
501: typeId4 = vv.TypeId4Value;
502: typeId5 = vv.TypeId5Value;
503: }
504: else
505: {
506: typeId1 = string.Empty;
507: typeId2 = string.Empty;
508: typeId4 = string.Empty;
509: typeId5 = string.Empty;
510: }
511: }
512: else
513: {
514: customerId = 0;
515: customerName = string.Empty;
516: customerAddress = string.Empty;
517:
518: typeId1 = string.Empty;
519: typeId2 = string.Empty;
520: typeId4 = string.Empty;
521: typeId5 = string.Empty;
522: }
523: }
524:
525: s = service + "\t" + alias + "\t" + area + "\t" + block + "\t" + street + "\t" + premisesOld + "\t" + paci + "\t" + customerId + "\t" + customerName + "\t" + customerAddress + "\t" + typeId1 + "\t" + typeId2 + "\t" + typeId4 + "\t" + typeId5 + "";
526: sb.AppendLine(s);
527:
528: }
529: }
530:
531: ////////////////////////////////////////////////////////////////////////////
532:
533: /// <summary>
534: ///
535: /// </summary>
536: public static List<Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> ServicesWithinSiteWithRelevantServiceRequestTypeList(int siteId)
537: {
538: Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Site site;
539: List<int> siteKuwaitNgnAreaIdList;
540: List<Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue> list;
541: Dictionary<int, string> areaIdToSymbolDictionary;
542:
543: areaIdToSymbolDictionary = Ia.Ngn.Cl.Model.Data.Service.AreaIdToSymbolDictionary;
544:
545: site = (from s in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.SiteList where s.Id == siteId select s).SingleOrDefault();
546: siteKuwaitNgnAreaIdList = (from kna in site.KuwaitNgnAreas select kna.Id).ToList();
547:
548: using (var db = new Ia.Ngn.Cl.Model.Ngn())
549: {
550: var serviceAreaIdTypeIdValue = (from srs in db.ServiceRequestServices
551: join sr in db.ServiceRequests on srs.Id equals sr.ServiceRequestService.Id
552: join srt in db.ServiceRequestTypes on sr.Id equals srt.ServiceRequest.Id
553: where srs.Provisioned == true && siteKuwaitNgnAreaIdList.Contains(sr.AreaId)
554: select new
555: {
556: Service = srs.Service,
557: AreaId = sr.AreaId,
558: TypeId = srt.TypeId,
559: Value = srt.Value
560: }
561: ).ToList();
562:
563: list = (from a in serviceAreaIdTypeIdValue
564: group a by new { a.Service, a.AreaId } into g
565: select new Ia.Ngn.Cl.Model.Ui.ServiceAreaSymbolServiceRequestTypeIdServiceRequestTypeValue
566: {
567: Service = g.Key.Service,
568: AreaSymbol = areaIdToSymbolDictionary[g.Key.AreaId],
569: TypeId1Value = g.Max(u => u.TypeId == 1 ? u.Value : "?"),
570: TypeId2Value = g.Max(u => u.TypeId == 2 ? u.Value : "?"),
571: TypeId3Value = g.Max(u => u.TypeId == 3 ? u.Value : "?"),
572: TypeId4Value = g.Max(u => u.TypeId == 4 ? u.Value : "?"),
573: TypeId5Value = g.Max(u => u.TypeId == 5 ? u.Value : "?"),
574: }).ToList();
575:
576: }
577:
578: return list;
579: }
580:
581: ////////////////////////////////////////////////////////////////////////////
582:
583: /// <summary>
584: ///
585: /// </summary>
586: public static Dictionary<string, int> ServiceRequestServiceServiceToKuwaitNgnAreaIdDictionary
587: {
588: get
589: {
590: string accessId;
591: Dictionary<string, string> serviceRequestServiceServiceToAccessIdDictionary;
592: Dictionary<string, int> dictionary, accessIdToKuwaitNgnAreaIdDictionary;
593:
594: serviceRequestServiceServiceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.ServiceRequestService.ProvisionedServiceIdToAccessIdDictionary;
595: accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
596:
597: if (serviceRequestServiceServiceToAccessIdDictionary.Count > 0)
598: {
599: dictionary = new Dictionary<string, int>(serviceRequestServiceServiceToAccessIdDictionary.Count);
600:
601: foreach (string s in serviceRequestServiceServiceToAccessIdDictionary.Keys)
602: {
603: accessId = serviceRequestServiceServiceToAccessIdDictionary[s].ToString();
604:
605: if (accessId != null)
606: {
607: if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
608: {
609: dictionary[s] = accessIdToKuwaitNgnAreaIdDictionary[accessId];
610: }
611: }
612: else dictionary[s] = 0;
613: }
614: }
615: else
616: {
617: dictionary = new Dictionary<string, int>();
618: }
619:
620: return dictionary;
621: }
622: }
623:
624: ////////////////////////////////////////////////////////////////////////////
625:
626: /// <summary>
627: ///
628: /// </summary>
629: public static Dictionary<string, int> ServiceServiceToKuwaitNgnAreaIdDictionary
630: {
631: get
632: {
633: string accessId;
634: Dictionary<string, string> serviceServiceToAccessIdDictionary;
635: Dictionary<string, int> dictionary, accessIdToKuwaitNgnAreaIdDictionary;
636:
637: serviceServiceToAccessIdDictionary = Ia.Ngn.Cl.Model.Data.Service2.ServiceIdToAccessIdDictionary;
638: accessIdToKuwaitNgnAreaIdDictionary = Ia.Ngn.Cl.Model.Data.Access.IdToKuwaitNgnAreaIdDictionary;
639:
640: if (serviceServiceToAccessIdDictionary.Count > 0)
641: {
642: dictionary = new Dictionary<string, int>(serviceServiceToAccessIdDictionary.Count);
643:
644: foreach (string s in serviceServiceToAccessIdDictionary.Keys)
645: {
646: accessId = serviceServiceToAccessIdDictionary[s].ToString();
647:
648: if (accessId != null)
649: {
650: if (accessIdToKuwaitNgnAreaIdDictionary.ContainsKey(accessId))
651: {
652: dictionary[s] = accessIdToKuwaitNgnAreaIdDictionary[accessId];
653: }
654: }
655: else dictionary[s] = 0;
656: }
657: }
658: else
659: {
660: dictionary = new Dictionary<string, int>();
661: }
662:
663: return dictionary;
664: }
665: }
666:
667: ////////////////////////////////////////////////////////////////////////////
668:
669: /// <summary>
670: ///
671: /// </summary>
672: public static void DeleteDatabaseRecordsThatReferenceNonExistantOltsInNdd()
673: {
674: string sqlWhereString;
675: StringBuilder sb;
676: List<int> list;
677:
678: sb = new StringBuilder();
679:
680: list = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();
681:
682: if (list.Count > 40 && list.Count < 100)
683: {
684: sqlWhereString = string.Empty;
685:
686: foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";
687: sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);
688:
689: sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
690: sb.AppendLine("delete from OntServiceVoips where " + sqlWhereString.Replace("{variable}", "Id"));
691:
692: sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
693: sb.AppendLine("delete from OntOntPots where " + sqlWhereString.Replace("{variable}", "Id"));
694:
695: sb.AppendLine("delete from Events where " + sqlWhereString.Replace("{variable}", "Ont_Id"));
696:
697: sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
698: sb.AppendLine("delete from Onts where " + sqlWhereString.Replace("{variable}", "Id"));
699:
700: sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "EmsOnt_Id"));
701: sb.AppendLine("delete from EmsOntSipInfoes where " + sqlWhereString.Replace("{variable}", "Id"));
702:
703: sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
704: sb.AppendLine("delete from EmsOnts where " + sqlWhereString.Replace("{variable}", "Id"));
705:
706: sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));
707: sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "Id"));
708:
709: sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Access_Id"));
710: sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));
711:
712: sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Id"));
713: sb.AppendLine("delete from Accesses where " + sqlWhereString.Replace("{variable}", "Olt"));
714:
715: // for testing
716: //sb = sb.Replace("delete from","select * from");
717:
718: using (var db = new Ia.Ngn.Cl.Model.Ngn())
719: {
720: db.Database.ExecuteSqlRaw(sb.ToString());
721: }
722: }
723: else
724: {
725: throw new Exception("list.Count <= 40 || list.Count >= 100");
726: }
727: }
728:
729: ////////////////////////////////////////////////////////////////////////////
730:
731: /// <summary>
732: ///
733: /// </summary>
734: public static void DeleteDatabaseRecordsThatReferenceAnUnrelatedForeignKeyBySimilarityOfId()
735: {
736: int fixedLengthOfOntId, fixedLengthOfAccessId;
737: StringBuilder sb;
738:
739: sb = new StringBuilder();
740:
741: /*
742: --delete from OntOntPots where substring(Id,1, 16) not like Ont_Id
743: --delete from OntServiceVoips where substring(Id,1, 16) not like Ont_Id
744: --delete from EmsOntSipInfoes where substring(Id,1, 16) not like EmsOnt_Id
745: --delete from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id
746:
747: --delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id
748: --delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id)
749: --delete from ServiceRequestOnts where substring(Id,1, 16) not like Access_Id
750: */
751:
752: fixedLengthOfOntId = Ia.Ngn.Cl.Model.Business.Default.FixedLengthOfOntId;
753: fixedLengthOfAccessId = Ia.Ngn.Cl.Model.Business.Access.FixedLengthOfId;
754:
755: if (fixedLengthOfOntId > 10 && fixedLengthOfAccessId > 10)
756: {
757: sb.AppendLine("delete from OntOntPots where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");
758: sb.AppendLine("delete from OntServiceVoips where substring(Id,1, " + fixedLengthOfOntId + ") not like Ont_Id");
759: sb.AppendLine("delete from EmsOntSipInfoes where substring(Id,1, " + fixedLengthOfOntId + ") not like EmsOnt_Id");
760:
761: sb.AppendLine("delete from ServiceRequestOntDetails where substring(Id,1, 16) not like ServiceRequestOnt_Id");
762: sb.AppendLine("delete from ServiceRequestOntDetails where ServiceRequestOnt_Id in (select Id from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id)");
763: sb.AppendLine("delete from ServiceRequestOnts where substring(Id,1, " + fixedLengthOfAccessId + ") not like Access_Id");
764:
765: // for testing
766: //sb = sb.Replace("delete from","select * from");
767:
768: using (var db = new Ia.Ngn.Cl.Model.Ngn())
769: {
770: db.Database.ExecuteSqlRaw(sb.ToString());
771: }
772: }
773: else
774: {
775: throw new Exception("Ia.Ngn.Cl.Model.Business.Default.FixedLengthOfOntId and/or Ia.Ngn.Cl.Model.Business.Access.FixedLengthOfId is not proper");
776: }
777: }
778:
779: ////////////////////////////////////////////////////////////////////////////
780:
781: /// <summary>
782: ///
783: /// </summary>
784: public static void ConstructDeleteSqlForOracleDatabaseRecordsThatReferenceNonExistantOltsInNdd(out string ontDataSql, out string ontDetailsSql)
785: {
786: string sqlWhereString;
787: List<int> list;
788:
789: list = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList select o.Id).ToList();
790:
791: if (list.Count > 40 && list.Count < 100)
792: {
793: if (list.Count > 0)
794: {
795: sqlWhereString = string.Empty;
796:
797: foreach (int i in list) sqlWhereString += "{variable} not like '" + i + "%' and ";
798: sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 5, 5);
799:
800: ontDataSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));
801:
802: ontDetailsSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));
803: }
804: else
805: {
806: ontDataSql = ontDetailsSql = string.Empty;
807:
808: throw new Exception("list.Count == 0");
809: }
810: }
811: else
812: {
813: throw new Exception("list.Count <= 40 || list.Count >= 100");
814: }
815: }
816:
817: ////////////////////////////////////////////////////////////////////////////
818:
819: /// <summary>
820: /// Compare OntList with ServiceRequestOntList to extract extra or wrong ONTs from the later
821: /// </summary>
822: private static List<string> ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList()
823: {
824: Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont ont;
825: List<string> list;
826: List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
827: Dictionary<string, Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont> ontAccessIdToOntDictionary;
828:
829: list = new List<string>();
830:
831: ontAccessIdToOntDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntDictionary;
832:
833: serviceRequestOntList = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.List;
834:
835: foreach (Ia.Ngn.Cl.Model.ServiceRequestOnt sro in serviceRequestOntList)
836: {
837: if (ontAccessIdToOntDictionary.ContainsKey(sro.Id))
838: {
839: ont = ontAccessIdToOntDictionary[sro.Id];
840:
841: if (ont.Pon.PonGroup.Symbol == sro.AreaSymbol && ont.Pon.Number == sro.Pon && ont.Number == sro.Ont)
842: {
843: }
844: else
845: {
846: //sb.AppendLine(" wrong:" + w.Id + "'");
847: list.Add(sro.Id);
848: }
849: }
850: else
851: {
852: //sb.AppendLine(" missing: " + w.Id + "'");
853: list.Add(sro.Id);
854: }
855: }
856:
857: return list;
858: }
859:
860: ////////////////////////////////////////////////////////////////////////////
861:
862: /// <summary>
863: ///
864: /// </summary>
865: public static void DeleteOfnDatabaseRecordsAndConstructDeleteSqlForOracleDatabaseRecordsThatReferenceNonExistantOntsInNdd(out string ontDataSql, out string ontDetailsSql)
866: {
867: string sqlWhereString;
868: StringBuilder sb;
869: List<string> list;
870:
871: sb = new StringBuilder();
872:
873: ontDataSql = ontDetailsSql = string.Empty;
874:
875: list = ServiceRequestOntItemsThatDoNotExistInNetworkDesignDocumentOntListIdList();
876:
877: if (list.Count < 2000)
878: {
879: if (list.Count > 0)
880: {
881: DeleteOfnDatabaseRecordsThatReferenceNonExistantOntsInNdd(list);
882:
883: sqlWhereString = string.Empty;
884:
885: foreach (string s in list) sqlWhereString += "{variable} = '" + s + "' or ";
886: sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 4, 4);
887:
888: ontDetailsSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOntDetail.OracleSqlCommandDeleteServiceRequestOntDetailRecord(sqlWhereString.Replace("{variable}", "ID"));
889:
890: ontDataSql = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.OracleSqlCommandDeleteServiceRequestOntRecord(sqlWhereString.Replace("{variable}", "ID"));
891: }
892: else
893: {
894:
895: }
896: }
897: else
898: {
899: throw new Exception("list.Count >=2000");
900: }
901: }
902:
903: ////////////////////////////////////////////////////////////////////////////
904:
905: /// <summary>
906: ///
907: /// </summary>
908: private static void DeleteOfnDatabaseRecordsThatReferenceNonExistantOntsInNdd(List<string> list)
909: {
910: string sqlWhereString;
911: StringBuilder sb;
912:
913: sb = new StringBuilder();
914:
915: if (list.Count > 0)
916: {
917: sqlWhereString = string.Empty;
918:
919: foreach (string s in list) sqlWhereString += "{variable} = '" + s + "' or ";
920: sqlWhereString = sqlWhereString.Remove(sqlWhereString.Length - 4, 4);
921:
922: sb.AppendLine("delete from ServiceRequestOntDetails where " + sqlWhereString.Replace("{variable}", "ServiceRequestOnt_Id"));
923:
924: sb.AppendLine("delete from ServiceRequestOnts where " + sqlWhereString.Replace("{variable}", "Id"));
925:
926: // for testing
927: //sb = sb.Replace("delete from","select * from");
928:
929: using (var db = new Ia.Ngn.Cl.Model.Ngn())
930: {
931: db.Database.ExecuteSqlRaw(sb.ToString());
932: }
933: }
934: else
935: {
936:
937: }
938: }
939:
940: ////////////////////////////////////////////////////////////////////////////
941:
942: /// <summary>
943: ///
944: /// </summary>
945: public static int NextVacantFlatTermIdForHuaweiEmsOntEquipmentTypeIdAndNokiaGatewayId(Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentType equipmentType, int gatewayId)
946: {
947: int ft;
948: List<int> list;
949:
950: list = Ia.Ngn.Cl.Model.Data.Nokia.AgcfEndpoint.UsedFlatTermIdListForGatewayId(gatewayId);
951:
952: if (list.Count == 0) ft = 1; // this means agcfEndpoint does not exist for any number and the box is empty, and we should create agcfEndpoint at 1
953: else
954: {
955: list = Ia.Cl.Model.Default.ExcludedNumberListFromNumberListWithinRange(list, equipmentType.TelPorts);
956:
957: if (list.Count > 0) ft = list[0];
958: else ft = Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown; // something went wrong
959: }
960:
961: return ft;
962: }
963:
964: ////////////////////////////////////////////////////////////////////////////
965: ////////////////////////////////////////////////////////////////////////////
966:
967: /// <summary>
968: ///
969: /// </summary>
970: public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(List<string> oltSymbolList)
971: {
972: using (var db = new Ia.Ngn.Cl.Model.Ngn())
973: {
974: var oltIdList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
975: where oltSymbolList.Contains(o.Symbol)
976: select o.Id).ToList();
977:
978: var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntForOltSymbolListDictionary(oltSymbolList);
979:
980: var list = (from a in db.Accesses
981: join eo in db.EmsOnts on a.Id equals eo.Access.Id into eos
982: from eo in eos.DefaultIfEmpty()
983: join ed in db.EmsDevs on eo.DID equals ed.DID into eds
984: from ed in eds.DefaultIfEmpty()
985: where oltIdList.Contains(a.Olt)
986: select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber
987: {
988: AccessId = a.Id,
989: DevFnSnPnOntId = ed.DEV + "-" + eo.FN + "-" + eo.SN + "-" + eo.PN + "-" + eo.ONTID,
990: Vaprof = eo.VAPROF
991: }).Distinct().ToList();
992:
993: foreach (var u in list)
994: {
995: u.Ont = ontAccessIdToOntForOltSymbolListDictionary.ContainsKey(u.AccessId) ? ontAccessIdToOntForOltSymbolListDictionary[u.AccessId] : null;
996: }
997:
998: return list;
999: }
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
{
List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
var _list = AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(oltSymbolList);
list = (from l in _list where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" select l).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> DistinctNddOntWhereVaprofIsNotAluSipBAndIgnoreMduForOltSymbolListList(List<string> oltSymbolList)
{
// I will ignore MDU because they have to be converted to SIP manually by Huawei
List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list, list2;
var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
list = (from l in _list
where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" && l.EmsOntFamilyType != Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu
select l).ToList();
list2 = new List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber>(list.Count);
list2 = (from l in list select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber { Ont = l.Ont }).Distinct().ToList();
return list2;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> ServiceAndNddOntAndEmsOntFamilyTypeAndPortWhereAgcfEndpointPortIsNot0AndVaprofIsAluSipBAndSfuEmsOntSipInfoTelDoesNotExistOrMduEmsVagIsSipAndMduEmsVoipPstnUserPnDoesNotExistForOltSymbolListList(List<string> oltSymbolList)
{
List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
list = (from l in _list
where l.Ont != null && l.Port != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown // meaning AgcfEndpoints is not null and Port = ep.FlatTermID
&&
(
l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Sfu && l.Vaprof == "ALU-SIP-B-MS" && l.EmsOntSipInfoTel == Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
||
l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn == Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
)
select l).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<string> AccessNameWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
{
List<string> list;
var list0 = AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(oltSymbolList);
list = (from l in list0 select l.Ont.Access.Name).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(List<string> oltSymbolList)
{
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
var oltIdList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
where oltSymbolList.Contains(o.Symbol)
select o.Id).ToList();
var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntForOltSymbolListDictionary(oltSymbolList);
var list = (from a in db.Accesses
join se in db.Service2 on a.Id equals se.Access.Id
join sp in db.SubParties on se.Service equals sp.DisplayName
join ep in db.AgcfEndpoints on sp.PartyId equals ep.Dn into eps
from ep in eps.DefaultIfEmpty()
join gr in db.AgcfGatewayRecords on ep.AgcfGatewayRecord.Id equals gr.Id into grs
from gr in grs.DefaultIfEmpty()
join eo in db.EmsOnts on a.Id equals eo.Access.Id into eos
from eo in eos.DefaultIfEmpty()
join ed in db.EmsDevs on eo.DID equals ed.DID into eds
from ed in eds.DefaultIfEmpty()
join eosi in db.EmsOntSipInfoes on eo.Id equals eosi.EmsOnt.Id into eosis
from eosi in eosis.DefaultIfEmpty()
join evpu in db.EmsVoipPstnUsers on eo.Id equals evpu.EmsOnt.Id into evpus
from evpu in evpus.DefaultIfEmpty()
join ev in db.EmsVags on eo.Id equals ev.EmsOnt.Id into evs
from ev in evs.DefaultIfEmpty()
where oltIdList.Contains(a.Olt) && ((eosi == null && evpu == null) || (eosi != null && eosi.SIPNAME.Contains(se.Service)) || (evpu != null && evpu.DN.Contains(se.Service) && ev != null))
select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber
{
AccessId = a.Id,
DevFnSnPnOntId = ed.DEV + "-" + eo.FN + "-" + eo.SN + "-" + eo.PN + "-" + eo.ONTID,
Vaprof = eo.VAPROF,
EquipmentId = eo.EQUIPMENTID,
EmsOntSipInfoTel = eosi == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : eosi.TEL,
EmsVoipPstnUserPn = evpu == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : evpu.PN,
EmsVagProtocolType = ev == null ? string.Empty : ev.PROTOCOLTYPE,
AgcfGatewayRecordIp = gr == null ? string.Empty : gr.IP1,
Service = se.Service,
Port = ep == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : ep.FlatTermID,
PrimaryPuidcpeProfileNumber = sp.PrimaryPUIDCPEProfileNumber
}).AsNoTracking().ToList();
foreach (var u in list)
{
u.Ont = ontAccessIdToOntForOltSymbolListDictionary.ContainsKey(u.AccessId) ? ontAccessIdToOntForOltSymbolListDictionary[u.AccessId] : null;
u.EmsOntFamilyType = (from e in Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentTypeList where e.SystemNameList.Contains(u.EquipmentId) select e.FamilyType).Single();
}
return list;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofOntIpServicePortPrimaryPuidcpeProfileNumberWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
{
List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
list = (from l in list0 where l.Vaprof != "ALU-SIP-B-MS" select l).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10ForOltSymbolListList(List<string> oltSymbolList)
{
List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
list = (from l in list0
where l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, AccessName = l.Ont.Access.Name }).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)
{
List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
list = (from l in list0
where l.Vaprof == "ALU-SIP-B-MS" && l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, AccessName = l.Ont.Access.Name }).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)
{
List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
list = (from l in list0
where l.Vaprof == "ALU-SIP-B-MS" && l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, Ont = l.Ont, AccessName = l.Ont.Access.Name }).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBAndSfuEmsOntSipInfoTelExistsOrAndMduEmsOntVagIsSipAndMduEmsVoipPstnUserPnExistsForOltSymbolListList(List<string> oltSymbolList)
{
List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
list = (from l in list0
where
l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip &&
(
l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Sfu && l.Vaprof == "ALU-SIP-B-MS" && l.EmsOntSipInfoTel != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
||
l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
)
select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, Ont = l.Ont, AccessName = l.Ont.Access.Name }).ToList();
return list;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, int> RouterNameToServiceCountInNokiaRouterHuaweiAccessOltDictionary()
{
var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
var nokiaRouterHuaweiAccessOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaRouterHuaweiAccessOltIdList;
var nokiaRouterHuaweiAccessRouterList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
where nokiaRouterHuaweiAccessOltIdList.Contains(o.Id)
select o.Odf.Router).Distinct().ToList();
var dictionary = new Dictionary<string, int>();
foreach (var r in nokiaRouterHuaweiAccessRouterList)
{
var nameType = r.Name + " (" + r.Type + ")";
if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
foreach (var o in r.Odfs.SelectMany(u => u.Olts))
{
if (o.Odf.Router.Id == r.Id)
{
if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
{
dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
}
}
}
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, int> NokiaOltToServiceCountDictionary()
{
var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
var oltList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaOltList;
var dictionary = new Dictionary<string, int>();
foreach (var o in oltList)
{
var nameType = o.Name + " (" + o.EmsName + ")";
if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
{
dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
}
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<string, int> HuaweiOltToServiceCountDictionary()
{
var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
var oltList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltList;
var dictionary = new Dictionary<string, int>();
foreach (var o in oltList)
{
var nameType = o.Name + " (" + o.EmsName + ")";
if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
{
dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
}
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<int, int> OltIdToCountOfServiceDictionary()
{
var dictionary = new Dictionary<int, int>();
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
/*
select a.Olt, count(s.Service)
from Service2 s
left outer join Accesses a on a.Id = s.Access_Id
where a.Olt is not null
group by a.Olt
*/
dictionary = (from s in db.Service2
join a in db.Accesses on s.Access.Id equals a.Id
where a != null
group a.Olt by a.Olt into g
select new { OltId = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.OltId, t => t.ServiceCount);
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<int, int> MsanDidToMsanServiceCountDictionary()
{
var dictionary = new Dictionary<int, int>();
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
#if DEBUG
var dummyVarToDrawRefractorToBelow = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn("0000000");
#endif
dictionary = (from s in db.Service2
join evpu in db.EmsVoipPstnUsers on "+965" + s.Service equals evpu.DN
where evpu.DN.StartsWith("+965") // see: Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn()
group evpu.DID by evpu.DID into g
select new { Did = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.Did, t => t.ServiceCount);
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static Dictionary<int, int> MsanDidToEmsVoipPstnUsersMsanServiceCountDictionary()
{
var dictionary = new Dictionary<int, int>();
using (var db = new Ia.Ngn.Cl.Model.Ngn())
{
#if DEBUG
var dummyVarToDrawRefractorToBelow = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn("0000000");
#endif
dictionary = (from evpu in db.EmsVoipPstnUsers
where evpu.DN.StartsWith("+965") // see: Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn()
group evpu.DID by evpu.DID into g
select new { Did = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.Did, t => t.ServiceCount);
}
return dictionary;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static int MaximumOntTelPorts
{
get
{
var maxNokiaTelPort = Ia.Ngn.Cl.Model.Business.Nokia.Ont.EquipmentTypeList.Max(u => u.TelPorts);
var maxHuaweiTelPort = Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentTypeList.Max(u => u.TelPorts);
var maxTelPort = Math.Max(maxNokiaTelPort, maxHuaweiTelPort);
return maxTelPort;
}
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static StringBuilder Log()
{
return logStringBuilder;
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static void Log(string line)
{
var s = DateTime.UtcNow.AddHours(3).ToString("yyyy-MM-dd HH:mm") + ": " + line;
logStringBuilder.AppendLine(s);
}
////////////////////////////////////////////////////////////////////////////
/// <summary>
///
/// </summary>
public static void InitializeApplicationLists()
{
//_ = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntList; useless because this is not saved in SQL database where its accessable in other appliciations
//_ = Ia.Ngn.Cl.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration.AccessToPstnServiceListDictionary;
_ = Ia.Ngn.Cl.Model.Data.ServiceRequest.AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
_ = Ia.Ngn.Cl.Model.Data.ServiceRequest.DbNameToServiceListDictionary;
_ = Ia.Ngn.Cl.Model.Data.Ims.NokiaAccessNameWithPbxList;
}
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
}
}