1: using Microsoft.EntityFrameworkCore;
2: using System;
3: using System.Collections.Generic;
4: using System.Data;
5: using System.Linq;
6:
7: namespace Ia.Ngn.Cl.Model.Data
8: {
9: ////////////////////////////////////////////////////////////////////////////
10:
11: /// <summary publish="true">
12: /// Service Request Ont support class for Optical Fiber Network (OFN) data model.
13: /// </summary>
14: ///
15: /// <remarks>
16: /// Copyright © 2006-2018 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
17: ///
18: /// 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
19: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
20: ///
21: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
22: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
23: ///
24: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
25: ///
26: /// Copyright notice: This notice may not be removed or altered from any source distribution.
27: /// </remarks>
28: public class ServiceRequestOnt
29: {
30: private const int LengthOfRequestOntIdRange = 100;
31: private static Dictionary<string, string> ontAccessIdToOntAccessNameDictionary = new Dictionary<string, string>();
32: private static Queue<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntNotInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ngn.Cl.Model.ServiceRequestOnt>();
33: private static Queue<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ngn.Cl.Model.ServiceRequestOnt>();
34: private static Queue<Tuple<string, string>> serviceRequestAccessIdOptimizedStartEndRangeTupleQueue = new Queue<Tuple<string, string>>();
35:
36: ////////////////////////////////////////////////////////////////////////////
37:
38: /// <summary>
39: ///
40: /// </summary>
41: public ServiceRequestOnt() { }
42:
43: ////////////////////////////////////////////////////////////////////////////
44:
45: /// <summary>
46: ///
47: /// </summary>
48: public static bool ServiceRequestOntNotInCustomerDepartmentDatabaseQueueCountIsNotZero
49: {
50: get
51: {
52: return serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Count > 0;
53: }
54: }
55:
56: ////////////////////////////////////////////////////////////////////////////
57:
58: /// <summary>
59: ///
60: /// </summary>
61: public static List<string> IdList
62: {
63: get
64: {
65: List<string> list;
66:
67: using (var db = new Ia.Ngn.Cl.Model.Ngn())
68: {
69: list = (from a in db.ServiceRequestOnts select a.Id).ToList();
70: }
71:
72: return list;
73: }
74: }
75:
76: ////////////////////////////////////////////////////////////////////////////
77:
78: /// <summary>
79: ///
80: /// </summary>
81: public static List<Ia.Ngn.Cl.Model.ServiceRequestOnt> List
82: {
83: get
84: {
85: List<Ia.Ngn.Cl.Model.ServiceRequestOnt> list;
86:
87: using (var db = new Ia.Ngn.Cl.Model.Ngn())
88: {
89: list = (from a in db.ServiceRequestOnts select a).ToList();
90: }
91:
92: return list;
93: }
94: }
95:
96: ////////////////////////////////////////////////////////////////////////////
97: ////////////////////////////////////////////////////////////////////////////
98:
99: /// <summary>
100: ///
101: /// </summary>
102: public static Ia.Ngn.Cl.Model.ServiceRequestOnt ServiceRequestOntNotInCustomerDepartmentDatabase(out Ia.Cl.Model.Result result)
103: {
104: Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt;
105: List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
106: List<Ia.Ngn.Cl.Model.Access> accessList;
107:
108: result = new Ia.Cl.Model.Result();
109:
110: if (serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Count == 0)
111: {
112: accessList = Ia.Ngn.Cl.Model.Data.Access.ListOfAccessesWithProvisionedAndReadyOntsButDoNotExistInCustomerDepartmentDatabase();
113:
114: serviceRequestOntList = PrepareServiceRequestOntListFromAccessList(accessList);
115:
116: serviceRequestOntNotInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ngn.Cl.Model.ServiceRequestOnt>(serviceRequestOntList);
117: }
118:
119: if (serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Count > 0)
120: {
121: serviceRequestOnt = serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Dequeue();
122:
123: result.AddSuccess("(" + serviceRequestOnt.Name + "/" + serviceRequestOntNotInCustomerDepartmentDatabaseQueue.Count + ")");
124: }
125: else
126: {
127: serviceRequestOnt = null;
128:
129: result.AddSuccess("No missing records between OFN and billing database. ");
130: }
131:
132: return serviceRequestOnt;
133: }
134:
135: ////////////////////////////////////////////////////////////////////////////
136:
137: /// <summary>
138: ///
139: /// </summary>
140: public static List<Ia.Ngn.Cl.Model.ServiceRequestOnt> PrepareServiceRequestOntListFromAccessList(List<Ia.Ngn.Cl.Model.Access> accessList)
141: {
142: // Similar to Ia.Ngn.Cl.Model.Data.Access.AccessCapacityDictionary()
143:
144: bool familyExists;
145: int possibleNumberOfTd, possibleNumberOfEthernet;
146: string areaSymbol, accessOntFamilyTypeCapacityString;
147: Ia.Ngn.Cl.Model.Business.NetworkDesignDocument.Ont ont;
148: Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt;
149: List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
150:
151: serviceRequestOntList = new List<Ia.Ngn.Cl.Model.ServiceRequestOnt>();
152:
153: accessList = accessList.OrderByDescending(a => a.Created).ToList();
154:
155: foreach (Ia.Ngn.Cl.Model.Access access in accessList)
156: {
157: if (access.Onts != null && access.Onts.Count > 0)
158: {
159: if (access.Onts.First().FamilyType != Ia.Ngn.Cl.Model.Business.Nokia.Ont.FamilyType.Undefined)
160: {
161: accessOntFamilyTypeCapacityString = Ia.Ngn.Cl.Model.Data.Nokia.Ont.FamilyTypeStringFromId(access.Onts.FirstOrDefault().FamilyTypeId);
162:
163: possibleNumberOfTd = Ia.Ngn.Cl.Model.Business.Nokia.Ont.PossibleNumberOfTdForOntFamilyType(access.Onts.FirstOrDefault().FamilyTypeId);
164: possibleNumberOfEthernet = Ia.Ngn.Cl.Model.Business.Nokia.Ont.PossibleNumberOfHsiCardPortServiceConfigurationForOntFamilyType(access.Onts.FirstOrDefault().FamilyTypeId);
165:
166: accessOntFamilyTypeCapacityString += " (" + possibleNumberOfTd + ")";
167:
168: familyExists = true;
169: }
170: else
171: {
172: possibleNumberOfTd = 0;
173: possibleNumberOfEthernet = 0;
174:
175: accessOntFamilyTypeCapacityString = string.Empty;
176:
177: familyExists = false;
178: }
179: }
180: else if (access.EmsOnts != null && access.EmsOnts.Count > 0)
181: {
182: var familyType = access.EmsOnts.First().FamilyType;
183: var equipmentType = access.EmsOnts.First().EquipmentType;
184:
185: if (familyType != Ia.Ngn.Cl.Model.Business.Huawei.Ont.FamilyType.Undefined && equipmentType != null)
186: {
187: accessOntFamilyTypeCapacityString = familyType.ToString().ToUpper();
188:
189: possibleNumberOfTd = equipmentType.TelPorts;
190: possibleNumberOfEthernet = equipmentType.EthernetPorts;
191:
192: accessOntFamilyTypeCapacityString += " (" + possibleNumberOfTd + ")";
193:
194: familyExists = true;
195: }
196: else
197: {
198: possibleNumberOfTd = 0;
199: possibleNumberOfEthernet = 0;
200:
201: accessOntFamilyTypeCapacityString = string.Empty;
202:
203: familyExists = false;
204: }
205: }
206: else
207: {
208: possibleNumberOfTd = 0;
209: possibleNumberOfEthernet = 0;
210:
211: accessOntFamilyTypeCapacityString = string.Empty;
212:
213: familyExists = false;
214: }
215:
216: if (familyExists)
217: {
218: if (possibleNumberOfTd > 0 && possibleNumberOfEthernet > 0)
219: {
220: ont = (from o in Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntList
221: where o.Pon.PonGroup.Olt.Id == access.Olt && o.Pon.Number == access.Pon && o.Number == access.Ont
222: select o).SingleOrDefault();
223:
224: if (ont != null)
225: {
226: areaSymbol = ont.Pon.PonGroup.Symbol;
227:
228: var serviceRequestOntId = Ia.Ngn.Cl.Model.Business.ServiceRequestOnt.ServiceRequestOntId(access.Id);
229:
230: serviceRequestOnt = new Ia.Ngn.Cl.Model.ServiceRequestOnt()
231: {
232: Id = serviceRequestOntId,
233: Pon = access.Pon,
234: Ont = access.Ont,
235: Paci = access.Paci,
236: AreaSymbol = areaSymbol,
237: Block = access.Block,
238: Street = access.Street,
239: PremisesOld = access.PremisesOld,
240: PremisesNew = access.PremisesNew,
241:
242: PossibleNumberOfTd = possibleNumberOfTd,
243: PossibleNumberOfEthernet = possibleNumberOfEthernet
244: };
245:
246: serviceRequestOntList.Add(serviceRequestOnt);
247: }
248: else
249: {
250:
251: }
252: }
253: else
254: {
255:
256: }
257: }
258: else
259: {
260:
261: }
262: }
263:
264: return serviceRequestOntList;
265: }
266:
267: ////////////////////////////////////////////////////////////////////////////
268: ////////////////////////////////////////////////////////////////////////////
269:
270: /// <summary>
271: ///
272: /// </summary>
273: public static Ia.Ngn.Cl.Model.ServiceRequestOnt ServiceRequestOntNotUpdatedInCustomerDepartmentDatabase(out Ia.Cl.Model.Result result)
274: {
275: Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt;
276: List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
277: List<Ia.Ngn.Cl.Model.Access> accessList;
278:
279: result = new Ia.Cl.Model.Result();
280:
281: if (serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count == 0)
282: {
283: accessList = Ia.Ngn.Cl.Model.Data.Access.AccessesWithProvisionedAndReadyOntsAndEmsOntsButMismatchedWithCustomerDepartmentDatabaseServiceRequestOntListList();
284:
285: serviceRequestOntList = PrepareServiceRequestOntListFromAccessList(accessList);
286:
287: serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue = new Queue<Ia.Ngn.Cl.Model.ServiceRequestOnt>(serviceRequestOntList);
288: }
289:
290: if (serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count > 0)
291: {
292: serviceRequestOnt = serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Dequeue();
293:
294: result.AddSuccess("(" + serviceRequestOnt.Name + "/" + serviceRequestOntNotUpdatedInCustomerDepartmentDatabaseQueue.Count + ")");
295: }
296: else
297: {
298: serviceRequestOnt = null;
299:
300: result.AddSuccess("No mismatch in record values between OFN and billing database. ");
301: }
302:
303: return serviceRequestOnt;
304: }
305:
306: ////////////////////////////////////////////////////////////////////////////
307:
308: /// <summary>
309: ///
310: /// </summary>
311: public static Tuple<string, string> ServiceRequestOntIdStartEndRangeManager(out Ia.Cl.Model.Result result)
312: {
313: string startAccessName, endAccessName;
314: Tuple<string, string> tuple;
315: List<string> ontListAccessIdList;
316: List<Tuple<string, string>> tupleList;
317:
318: result = new Ia.Cl.Model.Result();
319:
320: if (serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count == 0)
321: {
322: ontListAccessIdList = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdList;
323: ontAccessIdToOntAccessNameDictionary = Ia.Ngn.Cl.Model.Data.NetworkDesignDocument.OntAccessIdToOntAccessNameDictionary;
324:
325: tupleList = Ia.Cl.Model.Default.OptimizedStartEndRangeTupleList(ontListAccessIdList, LengthOfRequestOntIdRange);
326:
327: serviceRequestAccessIdOptimizedStartEndRangeTupleQueue = new Queue<Tuple<string, string>>(tupleList);
328: }
329:
330: if (serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count > 0)
331: {
332: tuple = serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Dequeue();
333:
334: // debug
335: //tuple = new Tuple<string, string>("104010301512009", "104010301512019");
336:
337: startAccessName = ontAccessIdToOntAccessNameDictionary[tuple.Item1];
338: endAccessName = ontAccessIdToOntAccessNameDictionary[tuple.Item2];
339:
340: result.AddSuccess("(" + startAccessName + " - " + endAccessName + "/" + serviceRequestAccessIdOptimizedStartEndRangeTupleQueue.Count + ")");
341: }
342: else
343: {
344: tuple = null;
345:
346: result.AddWarning("(0-0 0/0)");
347: }
348:
349: return tuple;
350: }
351:
352: ////////////////////////////////////////////////////////////////////////////
353:
354: /// <summary>
355: ///
356: /// </summary>
357: public static string OracleSqlCommandSelectProperServiceRequestOntRecordList(Tuple<string, string> startEndRangeTuple)
358: {
359: return OracleSqlCommandSelectProperServiceRequestOntRecordList(startEndRangeTuple.Item1, startEndRangeTuple.Item2);
360: }
361:
362: ////////////////////////////////////////////////////////////////////////////
363:
364: /// <summary>
365: ///
366: /// </summary>
367: public static string OracleSqlCommandSelectProperServiceRequestOntRecordList(string start, string end)
368: {
369: string sql;
370:
371: //if (startEndRangeTuple != null) sql = "select distinct DP_ID as Id, DP as Value from (select DP, DP_ID from FM_NET where (DP_ID >= " + startEndRangeTuple.Item1 + " and DP_ID <= " + startEndRangeTuple.Item2 + ") order by DP_ID asc) where (DP like '%/%/%' or DP like '% % %' or DP like '%.%.%')"; // and rownum <= " + Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.LengthOfRequestOntIdRange;
372: //if (startEndRangeTuple != null) sql = "select distinct DP_ID as Id, DP_NAME as Value from FM_DP where (DP_ID >= " + startEndRangeTuple.Item1 + " and DP_ID <= " + startEndRangeTuple.Item2 + @") and (DP_NAME like '%/%/%' or DP_NAME like '% % %' or DP_NAME like '%.%.%') order by DP_ID asc";
373: //if (startEndRangeTuple != null) sql = "select distinct DP_ID as Id, DP_NAME as Value from FM_DP where (DP_ID >= " + startEndRangeTuple.Item1 + " and DP_ID <= " + startEndRangeTuple.Item2 + @") and (DP_NAME like '%/%/%' or DP_NAME like '% % %' or DP_NAME like '%.%.%' or DP_NAME like '% % %' or DP_NAME like '%.% %') order by DP_ID asc";
374: if (!string.IsNullOrEmpty(start) && !string.IsNullOrEmpty(end)) sql = @"select id, pon, ont, area_id as AreaSymbol, blook as Block, street, plot as PremisesOld, building as PremisesNew, paci, line_nos as PossibleNumberOfTd, ethernet_nos as PossibleNumberOfEthernet from ont_data where (id >= " + start + " and id <= " + end + @") order by id asc";
375: else sql = string.Empty;
376:
377: return sql;
378: }
379:
380: ////////////////////////////////////////////////////////////////////////////
381:
382: /// <summary>
383: ///
384: /// </summary>
385: public static string OracleSqlCommandInsertServiceRequestOntRecord(Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt)
386: {
387: string sql;
388:
389: sql = @"insert into ONT_DATA(ID, PON, ONT, AREA_ID, BLOOK, STREET, PLOT, BUILDING, PACI, LINE_NOS, ETHERNET_NOS) values "
390: + @"("
391: + @"N"e;" + serviceRequestOnt.Id + @""e;,"
392: + @"N"e;" + serviceRequestOnt.Pon + @""e;,"
393: + @"N"e;" + serviceRequestOnt.Ont + @""e;,"
394: + @"N"e;" + serviceRequestOnt.AreaSymbol + @""e;,"
395: + @"N"e;" + serviceRequestOnt.Block + @""e;,"
396: + @"N"e;" + serviceRequestOnt.Street + @""e;,"
397: + @"N"e;" + serviceRequestOnt.PremisesOld + @""e;,"
398: + @"N"e;" + serviceRequestOnt.PremisesNew + @""e;,"
399: + @"N"e;" + serviceRequestOnt.Paci + @""e;,"
400: + @"N"e;" + serviceRequestOnt.PossibleNumberOfTd + @""e;,"
401: + @"N"e;" + serviceRequestOnt.PossibleNumberOfEthernet + @""e;"
402: + @")";
403:
404: sql = sql.Replace("'", "''");
405: sql = sql.Replace(""e;", "'");
406:
407: return sql;
408: }
409:
410: ////////////////////////////////////////////////////////////////////////////
411:
412: /// <summary>
413: ///
414: /// </summary>
415: public static string OracleSqlCommandUpdateServiceRequestOntRecord(Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt)
416: {
417: string sql;
418:
419: sql = @"update ONT_DATA set PON = N"e;" + serviceRequestOnt.Pon + @""e;, "
420: + @"ONT = N"e;" + serviceRequestOnt.Ont + @""e;, "
421: + @"AREA_ID = N"e;" + serviceRequestOnt.AreaSymbol + @""e;, "
422: + @"BLOOK = N"e;" + serviceRequestOnt.Block + @""e;, "
423: + @"STREET = N"e;" + serviceRequestOnt.Street + @""e;, "
424: + @"PLOT = N"e;" + serviceRequestOnt.PremisesOld + @""e;, "
425: + @"BUILDING = N"e;" + serviceRequestOnt.PremisesNew + @""e;, "
426: + @"PACI = N"e;" + serviceRequestOnt.Paci + @""e;, "
427: + @"LINE_NOS = N"e;" + serviceRequestOnt.PossibleNumberOfTd + @""e;, "
428: + @"ETHERNET_NOS = N"e;" + serviceRequestOnt.PossibleNumberOfEthernet + @""e;"
429: + @" where ID = N"e;" + serviceRequestOnt.Id + @""e;";
430:
431: sql = sql.Replace("'", "''");
432: sql = sql.Replace(""e;", "'");
433:
434: return sql;
435: }
436:
437: ////////////////////////////////////////////////////////////////////////////
438:
439: /// <summary>
440: ///
441: /// </summary>
442: public static string OracleSqlCommandDeleteServiceRequestOntRecord(Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt)
443: {
444: string sql;
445:
446: sql = @"delete from ONT_DATA where ID = N"e;" + serviceRequestOnt.Id + @""e;";
447:
448: sql = sql.Replace("'", "''");
449: sql = sql.Replace(""e;", "'");
450:
451: return sql;
452: }
453:
454: ////////////////////////////////////////////////////////////////////////////
455:
456: /// <summary>
457: ///
458: /// </summary>
459: public static string OracleSqlCommandDeleteServiceRequestOntRecord(string sqlWhereString)
460: {
461: string sql;
462:
463: sql = @"delete from ONT_DATA where " + sqlWhereString;
464:
465: return sql;
466: }
467:
468: ////////////////////////////////////////////////////////////////////////////
469:
470: /// <summary>
471: ///
472: /// </summary>
473: public static Ia.Cl.Model.Result UpdateForServiceRequestOntIdRangeWithOutputDataTable(DataTable dataTable, Tuple<string, string> startEndRangeTuple)
474: {
475: //bool isUpdated;
476: int pon, ont, readItemCount, existingItemCount, insertedItemCount, updatedItemCount, deletedItemCount;
477: string r, start, end, areaSymbol, accessName, serviceRequestOntId;
478: Ia.Cl.Model.Result result;
479: Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt, newServiceRequestOnt;
480: List<string> insertedItemIdList, newServiceRequestOntIdList;
481: List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
482:
483: //isUpdated = false;
484: readItemCount = existingItemCount = insertedItemCount = updatedItemCount = deletedItemCount = 0;
485: r = string.Empty;
486: result = new Ia.Cl.Model.Result();
487: insertedItemIdList = new List<string>();
488: newServiceRequestOntIdList = new List<string>();
489:
490: if (dataTable != null)
491: {
492: using (var db = new Ia.Ngn.Cl.Model.Ngn())
493: {
494: readItemCount = dataTable.Rows.Count;
495:
496: start = startEndRangeTuple.Item1;
497: end = startEndRangeTuple.Item2;
498:
499: serviceRequestOntList = Ia.Ngn.Cl.Model.Data.ServiceRequestOnt.ReadListWithinIdRange(start, end);
500: existingItemCount = serviceRequestOntList.Count;
501:
502: insertedItemIdList = new List<string>(dataTable.Rows.Count + 1);
503:
504: foreach (DataRow dataRow in dataTable.Rows)
505: {
506: serviceRequestOntId = dataRow["Id"].ToString();
507:
508: ont = int.Parse(dataRow["Ont"].ToString());
509: pon = int.Parse(dataRow["Pon"].ToString());
510: areaSymbol = dataRow["AreaSymbol"].ToString();
511:
512: accessName = Ia.Ngn.Cl.Model.Business.Access.Name(areaSymbol, pon, ont);
513:
514: serviceRequestOnt = (from sro in db.ServiceRequestOnts where sro.Id == serviceRequestOntId select sro).SingleOrDefault();
515:
516: newServiceRequestOnt = new Ia.Ngn.Cl.Model.ServiceRequestOnt()
517: {
518: Id = serviceRequestOntId,
519:
520: Pon = pon,
521: Ont = ont,
522: AreaSymbol = areaSymbol,
523: Block = dataRow["Block"].ToString(),
524: Street = dataRow["Street"].ToString(),
525: PremisesOld = dataRow["PremisesOld"].ToString(),
526: PremisesNew = dataRow["PremisesNew"].ToString(),
527: Paci = dataRow["Paci"].ToString(),
528: PossibleNumberOfTd = int.Parse(dataRow["PossibleNumberOfTd"].ToString()),
529: PossibleNumberOfEthernet = int.Parse(dataRow["PossibleNumberOfEthernet"].ToString()),
530:
531: Created = DateTime.UtcNow.AddHours(3),
532: Updated = DateTime.UtcNow.AddHours(3),
533:
534: Access = Ia.Ngn.Cl.Model.Business.ServiceRequestType.ExtractAccess(db, accessName)
535: };
536:
537: if (serviceRequestOnt == null)
538: {
539: insertedItemIdList.Add(serviceRequestOntId);
540:
541: db.ServiceRequestOnts.Add(newServiceRequestOnt);
542:
543: insertedItemCount++;
544: }
545: else
546: {
547: // below: copy values from newServiceRequestOnt to serviceRequestOnt
548:
549: if (serviceRequestOnt.Update(newServiceRequestOnt))
550: {
551: db.ServiceRequestOnts.Attach(serviceRequestOnt);
552: db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
553:
554: updatedItemCount++;
555: }
556: }
557:
558: // below: this will enable the removal of SRT that don't have a valid SR
559: newServiceRequestOntIdList.Add(serviceRequestOntId);
560: }
561:
562: db.SaveChanges();
563:
564: // I will not remove ServiceRequestOnt information from billing database after it is inserted. Below code will remove ONTs that were OOS
565: /*
566: // below: this function will remove values that were not present in the reading
567: if (serviceRequestOntList.Count > 0)
568: {
569: foreach (Ia.Ngn.Cl.Model.ServiceRequestOnt sro in serviceRequestOntList)
570: {
571: if (!newServiceRequestOntIdList.Contains(sro.Id))
572: {
573: serviceRequestOnt = (from sro2 in db.ServiceRequestOnts where sro2.Id == sro.Id select sro2).SingleOrDefault();
574:
575: if (serviceRequestOnt != null)
576: {
577: db.ServiceRequestOnts.Remove(serviceRequestOnt);
578: deletedItemCount++;
579: }
580: }
581: }
582:
583: db.SaveChanges();
584: }
585: */
586:
587: /*
588: // note that I had to create the object first (above) then assign a foreign key reference to it
589: foreach (int i in insertedItemIdList)
590: {
591: newServiceRequestOnt = new Ia.Ngn.Cl.Model.ServiceRequestOnt();
592:
593: serviceRequestOnt = (from sro in db.ServiceRequestOnts where sro.Id == i select sro).SingleOrDefault();
594:
595: if (serviceRequestOnt != null)
596: {
597: newServiceRequestOnt.Copy(serviceRequestOnt);
598:
599: //newServiceRequestOnt.ServiceRequest = (from q in db.ServiceRequests where q.Id == serviceRequestId select q).SingleOrDefault();
600: newServiceRequestOnt.Access = Ia.Ngn.Cl.Model.Business.ServiceRequestType.ExtractAccess(db, serviceRequestOnt.Value);
601:
602: if (serviceRequestOnt.Update(newServiceRequestOnt))
603: {
604: db.ServiceRequestOnts.Attach(serviceRequestOnt);
605: db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
606:
607: updatedItemCount++;
608: }
609: }
610: }
611: */
612:
613: db.SaveChanges();
614:
615: //if (insertedItemCount != 0 || updatedItemCount != 0 || deletedItemCount != 0) isUpdated = true;
616: //else isUpdated = false;
617:
618: result.AddSuccess("(" + readItemCount + "/" + existingItemCount + "/" + insertedItemCount + "," + updatedItemCount + "," + deletedItemCount + ") ");
619: }
620: }
621: else
622: {
623: result.AddWarning("(dataTable == null/?/?) ");
624: }
625:
626: return result;
627: }
628:
629: ////////////////////////////////////////////////////////////////////////////
630:
631: /// <summary>
632: ///
633: /// </summary>
634: public static Ia.Ngn.Cl.Model.ServiceRequestOnt Read(string accessId)
635: {
636: Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt;
637:
638: using (var db = new Ia.Ngn.Cl.Model.Ngn())
639: {
640: serviceRequestOnt = (from sro in db.ServiceRequestOnts
641: where sro.Access.Id == accessId
642: select sro).SingleOrDefault();
643: }
644:
645: return serviceRequestOnt;
646: }
647:
648: ////////////////////////////////////////////////////////////////////////////
649:
650: /// <summary>
651: ///
652: /// </summary>
653: public static List<Ia.Ngn.Cl.Model.ServiceRequestOnt> ReadListWithinIdRange(string start, string end)
654: {
655: List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
656:
657: using (var db = new Ia.Ngn.Cl.Model.Ngn())
658: {
659: serviceRequestOntList = (from sro in db.ServiceRequestOnts where sro.Id.CompareTo(start) >= 0 && sro.Id.CompareTo(end) <= 0 select sro).ToList();
660: }
661:
662: return serviceRequestOntList;
663: }
664:
665: /*
666: ////////////////////////////////////////////////////////////////////////////
667:
668: /// <summary>
669: ///
670: /// </summary>
671: public static void UpdateNullAccess(out Ia.Cl.Model.Result result)
672: {
673: int updatedItemCount;
674: Ia.Ngn.Cl.Model.ServiceRequestOnt updatedServiceRequestOnt;
675: List<Ia.Ngn.Cl.Model.ServiceRequestOnt> serviceRequestOntList;
676:
677: updatedItemCount = 0;
678: result = new Ia.Cl.Model.Result();
679:
680: using (var db = new Ia.Ngn.Cl.Model.Ngn())
681: {
682: serviceRequestOntList = (from sro in db.ServiceRequestOnts where sro.Access == null select sro).ToList();
683:
684: if (serviceRequestOntList != null && serviceRequestOntList.Count > 0)
685: {
686: foreach (Ia.Ngn.Cl.Model.ServiceRequestOnt serviceRequestOnt in serviceRequestOntList)
687: {
688: updatedServiceRequestOnt = (from q in db.ServiceRequestOnts where q.Id == serviceRequestOnt.Id select q).SingleOrDefault();
689:
690: try
691: {
692: updatedServiceRequestOnt.Access = Ia.Ngn.Cl.Model.Business.ServiceRequestType.ExtractAccess(db, serviceRequestOnt.Value);
693: }
694: catch(Exception ex)
695: {
696:
697: }
698:
699: if (updatedServiceRequestOnt.Access != null)
700: {
701: db.ServiceRequestOnts.Attach(updatedServiceRequestOnt);
702: db.Entry(serviceRequestOnt).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
703:
704: updatedItemCount++;
705: }
706: }
707:
708: db.SaveChanges();
709:
710: result.AddSuccess("Number of updated records: " + updatedItemCount + ". ");
711: }
712: else
713: {
714: result.AddSuccess("No service request ONTs with Access = null. ");
715: }
716: }
717: }
718: */
719:
720: ////////////////////////////////////////////////////////////////////////////
721:
722: /// <summary>
723: ///
724: /// </summary>
725: public static bool Delete(string id, out string result)
726: {
727: bool b;
728:
729: b = false;
730: result = string.Empty;
731:
732: using (var db = new Ia.Ngn.Cl.Model.Ngn())
733: {
734: var v = (from o in db.ServiceRequestOnts where o.Id == id select o).FirstOrDefault();
735:
736: if (v != null)
737: {
738: db.ServiceRequestOnts.Remove(v);
739: db.SaveChanges();
740:
741: b = true;
742: }
743: else b = false;
744: }
745:
746: return b;
747: }
748:
749: ////////////////////////////////////////////////////////////////////////////
750:
751: /// <summary>
752: ///
753: /// </summary>
754: public static bool DeleteByAccessId(string accessId, out string result)
755: {
756: bool recordExisted;
757:
758: recordExisted = false;
759: result = string.Empty;
760:
761: using (var db = new Ia.Ngn.Cl.Model.Ngn())
762: {
763: // --delete from ServiceRequestOnts where Access_Id = '1040101010040004'
764: var v = (from sro in db.ServiceRequestOnts
765: where sro.Access.Id == accessId
766: select sro).FirstOrDefault(); //.SingleOrDefault();
767:
768: if (v != null)
769: {
770: db.ServiceRequestOnts.Remove(v);
771: db.SaveChanges();
772:
773: recordExisted = true;
774: }
775: else recordExisted = false;
776: }
777:
778: return recordExisted;
779: }
780:
781:
782: ////////////////////////////////////////////////////////////////////////////
783:
784: /// <summary>
785: ///
786: /// </summary>
787: public static List<string> AccessNamesThatRevertToAnUnexplainedErroneousStateInTheCustomerDepartmentDatabaseAndAreExemptFromSynchronizationProcessingList()
788: {
789: List<string> list = new List<string>() { "SHD.38.12", "SHD.45.2", "SHD.56.11", "HIT.75.15", "HIT.69.4", "HIT.69.3", "SHD.55.7", "SHD.41.8" };
790:
791: return list;
792: }
793:
794: ////////////////////////////////////////////////////////////////////////////
795: ////////////////////////////////////////////////////////////////////////////
796: }
797:
798: ////////////////////////////////////////////////////////////////////////////
799: ////////////////////////////////////////////////////////////////////////////
800: }