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: }
1000: }
1001:
1002: ////////////////////////////////////////////////////////////////////////////
1003:
1004: /// <summary>
1005: ///
1006: /// </summary>
1007: public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
1008: {
1009: List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
1010:
1011: var _list = AccessIdNddOntDevFnSnPnOntIdVaprofForOltSymbolListList(oltSymbolList);
1012:
1013: list = (from l in _list where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" select l).ToList();
1014:
1015: return list;
1016: }
1017:
1018: ////////////////////////////////////////////////////////////////////////////
1019:
1020: /// <summary>
1021: ///
1022: /// </summary>
1023: public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> DistinctNddOntWhereVaprofIsNotAluSipBAndIgnoreMduForOltSymbolListList(List<string> oltSymbolList)
1024: {
1025: // I will ignore MDU because they have to be converted to SIP manually by Huawei
1026:
1027: List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list, list2;
1028:
1029: var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1030:
1031: list = (from l in _list
1032: where l.Ont != null && l.Vaprof != "ALU-SIP-B-MS" && l.EmsOntFamilyType != Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu
1033: select l).ToList();
1034:
1035: list2 = new List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber>(list.Count);
1036:
1037: list2 = (from l in list select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber { Ont = l.Ont }).Distinct().ToList();
1038:
1039: return list2;
1040: }
1041:
1042: ////////////////////////////////////////////////////////////////////////////
1043:
1044: /// <summary>
1045: ///
1046: /// </summary>
1047: public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> ServiceAndNddOntAndEmsOntFamilyTypeAndPortWhereAgcfEndpointPortIsNot0AndVaprofIsAluSipBAndSfuEmsOntSipInfoTelDoesNotExistOrMduEmsVagIsSipAndMduEmsVoipPstnUserPnDoesNotExistForOltSymbolListList(List<string> oltSymbolList)
1048: {
1049: List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
1050:
1051: var _list = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1052:
1053: list = (from l in _list
1054: where l.Ont != null && l.Port != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown // meaning AgcfEndpoints is not null and Port = ep.FlatTermID
1055: &&
1056: (
1057: 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
1058: ||
1059: l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn == Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
1060: )
1061: select l).ToList();
1062:
1063: return list;
1064: }
1065:
1066: ////////////////////////////////////////////////////////////////////////////
1067:
1068: /// <summary>
1069: ///
1070: /// </summary>
1071: public static List<string> AccessNameWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
1072: {
1073: List<string> list;
1074:
1075: var list0 = AccessIdNddOntDevFnSnPnOntIdVaprofWhereVaprofIsNotAluSipBForOltSymbolListList(oltSymbolList);
1076:
1077: list = (from l in list0 select l.Ont.Access.Name).ToList();
1078:
1079: return list;
1080: }
1081:
1082: ////////////////////////////////////////////////////////////////////////////
1083:
1084: /// <summary>
1085: ///
1086: /// </summary>
1087: public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(List<string> oltSymbolList)
1088: {
1089: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1090: {
1091: var oltIdList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
1092: where oltSymbolList.Contains(o.Symbol)
1093: select o.Id).ToList();
1094:
1095: var ontAccessIdToOntForOltSymbolListDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntForOltSymbolListDictionary(oltSymbolList);
1096:
1097: var list = (from a in db.Accesses
1098: join se in db.Service2 on a.Id equals se.Access.Id
1099: join sp in db.SubParties on se.Service equals sp.DisplayName
1100:
1101: join ep in db.AgcfEndpoints on sp.PartyId equals ep.Dn into eps
1102: from ep in eps.DefaultIfEmpty()
1103:
1104: join gr in db.AgcfGatewayRecords on ep.AgcfGatewayRecord.Id equals gr.Id into grs
1105: from gr in grs.DefaultIfEmpty()
1106:
1107: join eo in db.EmsOnts on a.Id equals eo.Access.Id into eos
1108: from eo in eos.DefaultIfEmpty()
1109:
1110: join ed in db.EmsDevs on eo.DID equals ed.DID into eds
1111: from ed in eds.DefaultIfEmpty()
1112:
1113: join eosi in db.EmsOntSipInfoes on eo.Id equals eosi.EmsOnt.Id into eosis
1114: from eosi in eosis.DefaultIfEmpty()
1115:
1116: join evpu in db.EmsVoipPstnUsers on eo.Id equals evpu.EmsOnt.Id into evpus
1117: from evpu in evpus.DefaultIfEmpty()
1118:
1119: join ev in db.EmsVags on eo.Id equals ev.EmsOnt.Id into evs
1120: from ev in evs.DefaultIfEmpty()
1121:
1122: 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))
1123:
1124: select new Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber
1125: {
1126: AccessId = a.Id,
1127: DevFnSnPnOntId = ed.DEV + "-" + eo.FN + "-" + eo.SN + "-" + eo.PN + "-" + eo.ONTID,
1128: Vaprof = eo.VAPROF,
1129: EquipmentId = eo.EQUIPMENTID,
1130: EmsOntSipInfoTel = eosi == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : eosi.TEL,
1131: EmsVoipPstnUserPn = evpu == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : evpu.PN,
1132: EmsVagProtocolType = ev == null ? string.Empty : ev.PROTOCOLTYPE,
1133: AgcfGatewayRecordIp = gr == null ? string.Empty : gr.IP1,
1134: Service = se.Service,
1135: Port = ep == null ? Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown : ep.FlatTermID,
1136: PrimaryPuidcpeProfileNumber = sp.PrimaryPUIDCPEProfileNumber
1137: }).AsNoTracking().ToList();
1138:
1139: foreach (var u in list)
1140: {
1141: u.Ont = ontAccessIdToOntForOltSymbolListDictionary.ContainsKey(u.AccessId) ? ontAccessIdToOntForOltSymbolListDictionary[u.AccessId] : null;
1142:
1143: u.EmsOntFamilyType = (from e in Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentTypeList where e.SystemNameList.Contains(u.EquipmentId) select e.FamilyType).Single();
1144: }
1145:
1146: return list;
1147: }
1148: }
1149:
1150: ////////////////////////////////////////////////////////////////////////////
1151:
1152: /// <summary>
1153: ///
1154: /// </summary>
1155: public static List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> AccessIdNddOntDevFnSnPnOntIdVaprofOntIpServicePortPrimaryPuidcpeProfileNumberWhereVaprofIsNotAluSipBForOltSymbolListList(List<string> oltSymbolList)
1156: {
1157: List<Ia.Ngn.Cl.Model.Business.AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumber> list;
1158:
1159: var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1160:
1161: list = (from l in list0 where l.Vaprof != "ALU-SIP-B-MS" select l).ToList();
1162:
1163: return list;
1164: }
1165:
1166: ////////////////////////////////////////////////////////////////////////////
1167:
1168: /// <summary>
1169: ///
1170: /// </summary>
1171: public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10ForOltSymbolListList(List<string> oltSymbolList)
1172: {
1173: List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
1174:
1175: var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1176:
1177: list = (from l in list0
1178: where l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
1179: select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, AccessName = l.Ont.Access.Name }).ToList();
1180:
1181: return list;
1182: }
1183:
1184: ////////////////////////////////////////////////////////////////////////////
1185:
1186: /// <summary>
1187: ///
1188: /// </summary>
1189: public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndAccessNameWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)
1190: {
1191: List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
1192:
1193: var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1194:
1195: list = (from l in list0
1196: where l.Vaprof == "ALU-SIP-B-MS" && l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
1197: select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, AccessName = l.Ont.Access.Name }).ToList();
1198:
1199: return list;
1200: }
1201:
1202: ////////////////////////////////////////////////////////////////////////////
1203:
1204: /// <summary>
1205: ///
1206: /// </summary>
1207: public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBForOltSymbolListList(List<string> oltSymbolList)
1208: {
1209: List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
1210:
1211: var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1212:
1213: list = (from l in list0
1214: where l.Vaprof == "ALU-SIP-B-MS" && l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip
1215: select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, Ont = l.Ont, AccessName = l.Ont.Access.Name }).ToList();
1216:
1217: return list;
1218: }
1219:
1220: ////////////////////////////////////////////////////////////////////////////
1221:
1222: /// <summary>
1223: ///
1224: /// </summary>
1225: public static List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> ServiceAndNddOntWherePrimaryPuidcpeProfileNumberIsNot10AndEmsOntVaprofIsAluSipBAndSfuEmsOntSipInfoTelExistsOrAndMduEmsOntVagIsSipAndMduEmsVoipPstnUserPnExistsForOltSymbolListList(List<string> oltSymbolList)
1226: {
1227: List<Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName> list;
1228:
1229: var list0 = AccessIdNddOntEmsOntiIdDevFnSnPnVaprofEquipmentIdFamilTypeSipInfoTelVoipPstnUserPnVagProtocolTypeServicePortAgcfGatewayRecordIpSubpartyPuidceProfileNumberForOltSymbolListList(oltSymbolList);
1230:
1231: list = (from l in list0
1232: where
1233: l.PrimaryPuidcpeProfileNumber != Ia.Ngn.Cl.Model.Business.Nokia.Ims.PrimaryPUIDCPEProfileNumberForSip &&
1234: (
1235: 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
1236: ||
1237: l.EmsOntFamilyType == Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Mdu && l.EmsVagProtocolType == "SIP" && l.EmsVoipPstnUserPn != Ia.Ngn.Cl.Model.Business.Default.PortUndefinedOrInvalidOrUnknown
1238: )
1239: select new Ia.Ngn.Cl.Model.Business.ServiceNddOntAccessName { Service = l.Service, Ont = l.Ont, AccessName = l.Ont.Access.Name }).ToList();
1240:
1241: return list;
1242: }
1243:
1244: ////////////////////////////////////////////////////////////////////////////
1245:
1246: /// <summary>
1247: ///
1248: /// </summary>
1249: public static Dictionary<string, int> RouterNameToServiceCountInNokiaRouterHuaweiAccessOltDictionary()
1250: {
1251: var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
1252:
1253: var nokiaRouterHuaweiAccessOltIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaRouterHuaweiAccessOltIdList;
1254:
1255: var nokiaRouterHuaweiAccessRouterList = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OltList
1256: where nokiaRouterHuaweiAccessOltIdList.Contains(o.Id)
1257: select o.Odf.Router).Distinct().ToList();
1258:
1259:
1260: var dictionary = new Dictionary<string, int>();
1261:
1262: foreach (var r in nokiaRouterHuaweiAccessRouterList)
1263: {
1264: var nameType = r.Name + " (" + r.Type + ")";
1265:
1266: if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
1267:
1268: foreach (var o in r.Odfs.SelectMany(u => u.Olts))
1269: {
1270: if (o.Odf.Router.Id == r.Id)
1271: {
1272: if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
1273: {
1274: dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
1275: }
1276: }
1277: }
1278: }
1279:
1280: return dictionary;
1281: }
1282:
1283: ////////////////////////////////////////////////////////////////////////////
1284:
1285: /// <summary>
1286: ///
1287: /// </summary>
1288: public static Dictionary<string, int> NokiaOltToServiceCountDictionary()
1289: {
1290: var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
1291:
1292: var oltList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.NokiaOltList;
1293:
1294: var dictionary = new Dictionary<string, int>();
1295:
1296: foreach (var o in oltList)
1297: {
1298: var nameType = o.Name + " (" + o.EmsName + ")";
1299:
1300: if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
1301:
1302: if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
1303: {
1304: dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
1305: }
1306: }
1307:
1308: return dictionary;
1309: }
1310:
1311: ////////////////////////////////////////////////////////////////////////////
1312:
1313: /// <summary>
1314: ///
1315: /// </summary>
1316: public static Dictionary<string, int> HuaweiOltToServiceCountDictionary()
1317: {
1318: var oltIdToCountOfServiceDictionary = Ia.Ngn.Cl.Model.Data.Default.OltIdToCountOfServiceDictionary();
1319:
1320: var oltList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.HuaweiOltList;
1321:
1322: var dictionary = new Dictionary<string, int>();
1323:
1324: foreach (var o in oltList)
1325: {
1326: var nameType = o.Name + " (" + o.EmsName + ")";
1327:
1328: if (!dictionary.ContainsKey(nameType)) dictionary[nameType] = 0;
1329:
1330: if (oltIdToCountOfServiceDictionary.ContainsKey(o.Id))
1331: {
1332: dictionary[nameType] += oltIdToCountOfServiceDictionary[o.Id];
1333: }
1334: }
1335:
1336: return dictionary;
1337: }
1338:
1339: ////////////////////////////////////////////////////////////////////////////
1340:
1341: /// <summary>
1342: ///
1343: /// </summary>
1344: public static Dictionary<int, int> OltIdToCountOfServiceDictionary()
1345: {
1346: var dictionary = new Dictionary<int, int>();
1347:
1348: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1349: {
1350: /*
1351: select a.Olt, count(s.Service)
1352: from Service2 s
1353: left outer join Accesses a on a.Id = s.Access_Id
1354: where a.Olt is not null
1355: group by a.Olt
1356: */
1357:
1358: dictionary = (from s in db.Service2
1359: join a in db.Accesses on s.Access.Id equals a.Id
1360: where a != null
1361: group a.Olt by a.Olt into g
1362: select new { OltId = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.OltId, t => t.ServiceCount);
1363: }
1364:
1365: return dictionary;
1366: }
1367:
1368: ////////////////////////////////////////////////////////////////////////////
1369:
1370: /// <summary>
1371: ///
1372: /// </summary>
1373: public static Dictionary<int, int> MsanDidToMsanServiceCountDictionary()
1374: {
1375: var dictionary = new Dictionary<int, int>();
1376:
1377: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1378: {
1379: #if DEBUG
1380: var dummyVarToDrawRefractorToBelow = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn("0000000");
1381: #endif
1382:
1383: dictionary = (from s in db.Service2
1384: join evpu in db.EmsVoipPstnUsers on "+965" + s.Service equals evpu.DN
1385: where evpu.DN.StartsWith("+965") // see: Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn()
1386: group evpu.DID by evpu.DID into g
1387: select new { Did = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.Did, t => t.ServiceCount);
1388: }
1389:
1390: return dictionary;
1391: }
1392:
1393: ////////////////////////////////////////////////////////////////////////////
1394:
1395: /// <summary>
1396: ///
1397: /// </summary>
1398: public static Dictionary<int, int> MsanDidToEmsVoipPstnUsersMsanServiceCountDictionary()
1399: {
1400: var dictionary = new Dictionary<int, int>();
1401:
1402: using (var db = new Ia.Ngn.Cl.Model.Ngn())
1403: {
1404: #if DEBUG
1405: var dummyVarToDrawRefractorToBelow = Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn("0000000");
1406: #endif
1407:
1408: dictionary = (from evpu in db.EmsVoipPstnUsers
1409: where evpu.DN.StartsWith("+965") // see: Ia.Ngn.Cl.Model.Business.NumberFormatConverter.Dn()
1410: group evpu.DID by evpu.DID into g
1411: select new { Did = g.Key, ServiceCount = g.Count() }).AsNoTracking().ToDictionary(t => t.Did, t => t.ServiceCount);
1412: }
1413:
1414: return dictionary;
1415: }
1416:
1417: ////////////////////////////////////////////////////////////////////////////
1418:
1419: /// <summary>
1420: ///
1421: /// </summary>
1422: public static int MaximumOntTelPorts
1423: {
1424: get
1425: {
1426: var maxNokiaTelPort = Ia.Ngn.Cl.Model.Business.Nokia.Ont.EquipmentTypeList.Max(u => u.TelPorts);
1427: var maxHuaweiTelPort = Ia.Ngn.Cl.Model.Business.Huawei.Ont.EquipmentTypeList.Max(u => u.TelPorts);
1428:
1429: var maxTelPort = Math.Max(maxNokiaTelPort, maxHuaweiTelPort);
1430:
1431: return maxTelPort;
1432: }
1433: }
1434:
1435: ////////////////////////////////////////////////////////////////////////////
1436:
1437: /// <summary>
1438: ///
1439: /// </summary>
1440: public static StringBuilder Log()
1441: {
1442: return logStringBuilder;
1443: }
1444:
1445: ////////////////////////////////////////////////////////////////////////////
1446:
1447: /// <summary>
1448: ///
1449: /// </summary>
1450: public static void Log(string line)
1451: {
1452: var s = DateTime.UtcNow.AddHours(3).ToString("yyyy-MM-dd HH:mm") + ": " + line;
1453:
1454: logStringBuilder.AppendLine(s);
1455: }
1456:
1457: ////////////////////////////////////////////////////////////////////////////
1458:
1459: /// <summary>
1460: ///
1461: /// </summary>
1462: public static void InitializeApplicationLists()
1463: {
1464: //_ = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntList; useless because this is not saved in SQL database where its accessable in other appliciations
1465: //_ = Ia.Ngn.Cl.Model.Data.Huawei.GponPhaseIiHomeConnectionAndMigration.AccessToPstnServiceListDictionary;
1466: _ = Ia.Ngn.Cl.Model.Data.ServiceRequest.AccessNameToServiceListWithinAllowedToBeMigratedOltDictionary;
1467: _ = Ia.Ngn.Cl.Model.Data.ServiceRequest.DbNameToServiceListDictionary;
1468: _ = Ia.Ngn.Cl.Model.Data.Ims.NokiaAccessNameWithPbxList;
1469: }
1470:
1471: ////////////////////////////////////////////////////////////////////////////
1472: ////////////////////////////////////////////////////////////////////////////
1473: }
1474: }