1: using Microsoft.EntityFrameworkCore;
2: using System;
3: using System.Collections.Generic;
4: using System.Data;
5: using System.Linq;
6: using System.Text;
7:
8: namespace Ia.Ngn.Cl.Model.Data.Ericsson
9: {
10: ////////////////////////////////////////////////////////////////////////////
11:
12: /// <summary publish="true">
13: /// AXE Subscriber support class for Optical Fiber Network (OFN) data model.
14: /// </summary>
15: ///
16: /// <remarks>
17: /// Copyright © 2018-2020 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
18: ///
19: /// 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
20: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
21: ///
22: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
23: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
24: ///
25: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
26: ///
27: /// Copyright notice: This notice may not be removed or altered from any source distribution.
28: /// </remarks>
29: public class Subscriber
30: {
31: /// <summary/>
32: public Subscriber() { }
33:
34: ////////////////////////////////////////////////////////////////////////////
35:
36: /// <summary>
37: ///
38: /// </summary>
39: public static bool Update(Ia.Ngn.Cl.Model.Business.Ericsson.Axe.Response response, out Ia.Cl.Model.Result result)
40: {
41: bool wasUpdated;
42: int querySnb, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
43: string id, querySnbString, queryCommand;
44: Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber subscriber, newSubscriber;
45:
46: wasUpdated = false;
47: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
48: result = new Ia.Cl.Model.Result();
49:
50: queryCommand = response.CommandString;
51:
52: // SUSCP:SNB=25382234;
53: querySnbString = Ia.Cl.Model.Default.Match(queryCommand, @"SNB=(\d{8})");
54:
55: querySnb = int.Parse(querySnbString);
56:
57: readItemCount = 1; // response.Count;
58:
59: using (var db = new Ia.Ngn.Cl.Model.Ngn())
60: {
61: subscriber = (from s in db.AxeSubscribers where s.SNB == querySnb select s).SingleOrDefault();
62:
63: existingItemCount = (subscriber != null) ? 1 : 0;
64:
65: if (response.IsSuccess) //.Count >= 1)
66: {
67: id = Ia.Ngn.Cl.Model.Business.Ericsson.Subscriber.SubscriberId(querySnb);
68:
69: newSubscriber = new Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber()
70: {
71: Id = id,
72: SNB = response.Snb,
73: DEV = response.Dev,
74: DETY = response.Dety,
75: SUT = response.Sut,
76: SCL = response.Scl,
77: MIS = response.Mis,
78: COS = response.Cos,
79: Content = string.Empty// response.Content
80: };
81:
82: if (subscriber == null)
83: {
84: newSubscriber.Created = newSubscriber.Updated = DateTime.UtcNow.AddHours(3);
85:
86: db.AxeSubscribers.Add(newSubscriber);
87:
88: insertedItemCount++;
89: }
90: else
91: {
92: if (subscriber.Update(newSubscriber))
93: {
94: db.AxeSubscribers.Attach(subscriber);
95: db.Entry(subscriber).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
96:
97: updatedItemCount++;
98: }
99: }
100: }
101: else
102: {
103: // below: remove since reading was empty
104:
105: if (subscriber != null)
106: {
107: subscriber = (from s in db.AxeSubscribers where s.SNB == querySnb select s).SingleOrDefault();
108:
109: db.AxeSubscribers.Remove(subscriber);
110:
111: deletedItemCount++;
112: }
113: }
114:
115: db.SaveChanges();
116: }
117:
118: if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) wasUpdated = true;
119: else wasUpdated = false;
120:
121: result.AddSuccess("(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ")");
122:
123: return wasUpdated;
124: }
125:
126: ////////////////////////////////////////////////////////////////////////////
127:
128: /// <summary>
129: ///
130: /// </summary>
131: public static List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> List()
132: {
133: List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> list;
134:
135: using (var db = new Ia.Ngn.Cl.Model.Ngn())
136: {
137: list = (from s in db.AxeSubscribers select s).ToList();
138: }
139:
140: return list;
141: }
142:
143: ////////////////////////////////////////////////////////////////////////////
144:
145: /// <summary>
146: ///
147: /// </summary>
148: public static List<int> SnbList()
149: {
150: List<int> list;
151:
152: using (var db = new Ia.Ngn.Cl.Model.Ngn())
153: {
154: list = (from s in db.AxeSubscribers select s.SNB).ToList();
155: }
156:
157: return list;
158: }
159:
160: ////////////////////////////////////////////////////////////////////////////
161:
162: /// <summary>
163: ///
164: /// </summary>
165: public static List<int> SnbWhereSclHasIcs3AndSutNotNcList()
166: {
167: List<int> list;
168:
169: // select SNB from AxeSubscribers where SCL like '%ICS-3%' and SUT <> 'NC'
170:
171: using (var db = new Ia.Ngn.Cl.Model.Ngn())
172: {
173: list = (from s in db.AxeSubscribers
174: where s.SCL.Contains("ICS-3") && s.SUT != "NC"
175: select s.SNB).ToList();
176: }
177:
178: return list;
179: }
180:
181: ////////////////////////////////////////////////////////////////////////////
182:
183: /// <summary>
184: ///
185: /// </summary>
186: public static List<int> SnbWhereSclHasTbiOrTboOrBicAndSutNotNcList()
187: {
188: List<int> list;
189:
190: // select * from AxeSubscribers where SUT <> 'NC' and (scl like '%TBO%' or scl like '%TBI%' or scl like '%BIC%')
191:
192: using (var db = new Ia.Ngn.Cl.Model.Ngn())
193: {
194: list = (from s in db.AxeSubscribers
195: where (s.SCL.Contains("TBI") || s.SCL.Contains("TBO") || s.SCL.Contains("BIC")) && s.SUT != "NC"
196: select s.SNB).ToList();
197: }
198:
199: return list;
200: }
201:
202: ////////////////////////////////////////////////////////////////////////////
203:
204: /// <summary>
205: ///
206: /// </summary>
207: public static List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> List(List<string> serviceList)
208: {
209: List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> list;
210:
211: if (serviceList.Count > 0)
212: {
213: var sbnList = serviceList.ConvertAll(int.Parse);
214: var idList = new List<string>();
215:
216: foreach (var snb in sbnList)
217: {
218: idList.Add(Ia.Ngn.Cl.Model.Business.Ericsson.Subscriber.SubscriberId(snb));
219: }
220:
221: using (var db = new Ia.Ngn.Cl.Model.Ngn())
222: {
223: list = (from s in db.AxeSubscribers
224: where idList.Contains(s.Id)
225: select s).ToList();
226: }
227: }
228: else list = new List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber>();
229:
230: return list;
231: }
232:
233: ////////////////////////////////////////////////////////////////////////////
234:
235: /// <summary>
236: ///
237: /// </summary>
238: public static List<int> ServiceList(List<string> serviceList)
239: {
240: List<int> list, sbnList;
241:
242: if (serviceList.Count > 0)
243: {
244: sbnList = serviceList.ConvertAll(int.Parse);
245:
246: using (var db = new Ia.Ngn.Cl.Model.Ngn())
247: {
248: list = (from s in db.AxeSubscribers
249: where sbnList.Contains(s.SNB)
250: select s.SNB).ToList();
251: }
252: }
253: else list = new List<int>();
254:
255: return list;
256: }
257:
258: ////////////////////////////////////////////////////////////////////////////
259:
260: /// <summary>
261: ///
262: /// </summary>
263: public static List<Ia.Ngn.Cl.Model.Ui.Ericsson.Subscriber> ListWithoutContent()
264: {
265: List<Ia.Ngn.Cl.Model.Ui.Ericsson.Subscriber> list;
266:
267: // select SNB, DEV, DETY, SUT, SCL, MIS, COS from AxeSubscribers
268:
269: using (var db = new Ia.Ngn.Cl.Model.Ngn())
270: {
271: list = (from s in db.AxeSubscribers
272: select new Ia.Ngn.Cl.Model.Ui.Ericsson.Subscriber() { SNB = s.SNB, DEV = s.DEV, DETY = s.DETY, SUT = s.SUT, SCL = s.SCL, MIS = s.MIS, COS = s.COS }).ToList();
273:
274: return list;
275: }
276: }
277:
278: ////////////////////////////////////////////////////////////////////////////
279:
280: /// <summary>
281: ///
282: /// </summary>
283: public static List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> InServicePstnServiceList()
284: {
285: List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> list;
286:
287: // select * from AxeSubscribers a inner join Service2 s on a.SNB = s.Service where s.ServiceType = 2
288:
289: var serviceType = Ia.Ngn.Cl.Model.Business.Service.ServiceType.PstnService;
290:
291: using (var db = new Ia.Ngn.Cl.Model.Ngn())
292: {
293: list = (from s in db.AxeSubscribers
294: join se in db.Service2 on s.SNB.ToString() equals se.Service
295: where se.ServiceType == serviceType
296: select s).ToList();
297: }
298:
299: return list;
300: }
301:
302: ////////////////////////////////////////////////////////////////////////////
303:
304: /// <summary>
305: ///
306: /// </summary>
307: public static List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> InServiceImsServiceList()
308: {
309: List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> list;
310:
311: // select * from AxeSubscribers a inner join Service2 s on a.SNB = s.Service where s.ServiceType = 1
312:
313: var serviceType = Ia.Ngn.Cl.Model.Business.Service.ServiceType.ImsService;
314:
315: using (var db = new Ia.Ngn.Cl.Model.Ngn())
316: {
317: list = (from s in db.AxeSubscribers
318: join se in db.Service2 on s.SNB.ToString() equals se.Service
319: where se.ServiceType == serviceType
320: select s).ToList();
321: }
322:
323: return list;
324: }
325:
326: ////////////////////////////////////////////////////////////////////////////
327:
328: /// <summary>
329: ///
330: /// </summary>
331: public static List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> WithSclIcs3List()
332: {
333: List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> list;
334:
335: // select * from AxeSubscribers where SCL like '%ICS-3%'
336:
337: using (var db = new Ia.Ngn.Cl.Model.Ngn())
338: {
339: list = (from s in db.AxeSubscribers where s.SCL.Contains("ICS-3") select s).ToList();
340: }
341:
342: return list;
343: }
344:
345: ////////////////////////////////////////////////////////////////////////////
346:
347: /// <summary>
348: ///
349: /// </summary>
350: public static List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> InServiceImsServiceButHasNotSclIcs3List()
351: {
352: List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber> list;
353:
354: /*
355: * select * from AxeSubscribers [as]
356: * inner join Service2 s on [as].SNB = s.Service
357: * where s.ServiceType = 1 and [as].SCL not like '%ICS-3%'
358: */
359:
360: var serviceType = Ia.Ngn.Cl.Model.Business.Service.ServiceType.ImsService;
361:
362: using (var db = new Ia.Ngn.Cl.Model.Ngn())
363: {
364: list = (from s in db.AxeSubscribers
365: join se in db.Service2 on s.SNB.ToString() equals se.Service
366: where se.ServiceType == serviceType && !s.SCL.Contains("ICS-3")
367: select s).ToList();
368: }
369:
370: return list;
371: }
372:
373: ////////////////////////////////////////////////////////////////////////////
374:
375: /// <summary>
376: ///
377: /// </summary>
378: public static Dictionary<string, string> ServiceToRouteNameDictionary()
379: {
380: Dictionary<string, string> dictionary;
381:
382: dictionary = new Dictionary<string, string>();
383:
384: using (var db = new Ia.Ngn.Cl.Model.Ngn())
385: {
386: var list = (from s in db.AxeSubscribers select s).ToList();
387:
388: foreach (var l in list)
389: {
390: dictionary[l.SNB.ToString()] = Ia.Ngn.Cl.Model.Business.Ericsson.Subscriber.RouteName(l.SCL);
391: }
392: }
393:
394: return dictionary;
395: }
396:
397: ////////////////////////////////////////////////////////////////////////////
398:
399: /// <summary>
400: ///
401: /// </summary>
402: public static Dictionary<string, Ia.Ngn.Cl.Model.Business.Default.SwitchRoute> PstnServiceToSwitchRouteDictionary()
403: {
404: int icsRoute;
405: Dictionary<string, Ia.Ngn.Cl.Model.Business.Default.SwitchRoute> dictionary;
406:
407: dictionary = new Dictionary<string, Ia.Ngn.Cl.Model.Business.Default.SwitchRoute>();
408:
409: using (var db = new Ia.Ngn.Cl.Model.Ngn())
410: {
411: // I have to read even NC numbers because they will still have routing
412: var list = (from s in db.AxeSubscribers select new { s.SNB, s.SCL }).AsNoTracking().ToList();
413:
414: foreach (var l in list)
415: {
416: icsRoute = Ia.Ngn.Cl.Model.Business.Ericsson.Subscriber.IcsRouteFromSubscriberScl(l.SCL);
417:
418: dictionary[l.SNB.ToString()] = Ia.Ngn.Cl.Model.Business.Ericsson.Subscriber.IcsxRouteToSwitchRoute(icsRoute);
419: }
420: }
421:
422: return dictionary;
423: }
424:
425: ////////////////////////////////////////////////////////////////////////////
426:
427: /// <summary>
428: ///
429: /// </summary>
430: public static bool UpdateSnbSetSclToEmptyWhereSclHasIcs3AndSutNotNc()
431: {
432: var updatedItemCount = 0;
433: var wasUpdated = false;
434: var subscriberList = new List<Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber>();
435:
436: // select SNB from AxeSubscribers where SCL like '%ICS-3%' and SUT <> 'NC'
437:
438: using (var db = new Ia.Ngn.Cl.Model.Ngn())
439: {
440: subscriberList = (from s in db.AxeSubscribers
441: where s.SCL.Contains("ICS-3") && s.SUT != "NC"
442: select s).ToList();
443:
444: foreach (var subscriber in subscriberList)
445: {
446: subscriber.SCL = string.Empty;
447: subscriber.Updated = DateTime.UtcNow.AddHours(3);
448:
449: db.AxeSubscribers.Attach(subscriber);
450: db.Entry(subscriber).Property(u => u.SCL).IsModified = true;
451:
452:
453: updatedItemCount++;
454: }
455:
456: db.SaveChanges();
457: }
458:
459: if (updatedItemCount != 0) wasUpdated = true;
460: else wasUpdated = false;
461:
462: return wasUpdated;
463: }
464:
465: ////////////////////////////////////////////////////////////////////////////
466:
467: /// <summary>
468: ///
469: /// </summary>
470: public static List<string> ServiceNotWithinAxePstnDomainList()
471: {
472: var list = new List<string>();
473:
474: var axePstnDomainList = Ia.Ngn.Cl.Model.Data.Service.AxePstnDomainList;
475:
476: var snbList = Ia.Ngn.Cl.Model.Data.Ericsson.Subscriber.SnbList();
477:
478: var serviceList = snbList.ConvertAll(delegate (int i) { return i.ToString(); });
479:
480: foreach (var s in serviceList)
481: {
482: if (!axePstnDomainList.Any(u => s.StartsWith(u.ToString()))) list.Add(s);
483: }
484:
485: list.Sort();
486:
487: return list;
488: }
489:
490: ////////////////////////////////////////////////////////////////////////////
491: ////////////////////////////////////////////////////////////////////////////
492:
493: /// <summary>
494: ///
495: /// </summary>
496: public static string ToSimpleTextString(Ia.Ngn.Cl.Model.Ericsson.AxeSubscriber subscriber)
497: {
498: StringBuilder sb;
499:
500: sb = new StringBuilder();
501:
502: sb.AppendLine("SNB: " + subscriber.SNB);
503: sb.AppendLine("DEV: " + subscriber.DEV);
504: sb.AppendLine("DETY: " + subscriber.DETY);
505: sb.AppendLine("SUT: " + subscriber.SUT);
506: sb.AppendLine("SCL: " + subscriber.SCL);
507: sb.AppendLine("MIS: " + subscriber.MIS);
508: sb.AppendLine("COS: " + subscriber.COS);
509: //sb.AppendLine("Content: " + subscriber.Content);
510: sb.AppendLine("Route: " + Ia.Ngn.Cl.Model.Business.Ericsson.Subscriber.RouteName(subscriber.SCL));
511:
512: return sb.ToString();
513: }
514:
515: ////////////////////////////////////////////////////////////////////////////
516: ////////////////////////////////////////////////////////////////////////////
517: }
518:
519: ////////////////////////////////////////////////////////////////////////////
520: ////////////////////////////////////////////////////////////////////////////
521: }