1: using System;
2: using System.Collections;
3: using System.Collections.Generic;
4: using System.Data;
5: using System.IO;
6: using System.Linq;
7: using System.Reflection;
8: using System.Text.RegularExpressions;
9: using System.Xml.Linq;
10:
11: namespace Ia.Ngn.Cl.Model.Data
12: {
13: ////////////////////////////////////////////////////////////////////////////
14:
15: /// <summary publish="true">
16: /// MinistryDatabase support class for Optical Fiber Network (OFN) data model.
17: /// </summary>
18: ///
19: /// <remarks>
20: /// Copyright © 2021-2022 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
21: ///
22: /// 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
23: /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
24: ///
25: /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
26: /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
27: ///
28: /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
29: ///
30: /// Copyright notice: This notice may not be removed or altered from any source distribution.
31: /// </remarks>
32: public class MinistryDatabase
33: {
34: private static XDocument xDocument;
35: private static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.Table> tableList;
36:
37: private static readonly object objectLock = new object();
38:
39: /// <summary/>
40: public static List<long> ExaminedServiceTransactionIdList = new List<long>();
41:
42: /// <summary/>
43: public static List<string> AddedServiceList { get; set; } = new List<string>();
44:
45: /// <summary/>
46: public static List<string> RemovedServiceList { get; set; } = new List<string>();
47:
48: /// <summary/>
49: public static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> ServiceStateThatCrossedThresholdList { get; set; } = new List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState>();
50:
51: /// <summary/>
52: public static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceTransaction> ServiceTransactionList { get; set; } = new List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceTransaction>();
53:
54: ////////////////////////////////////////////////////////////////////////////
55:
56: /// <summary>
57: ///
58: /// </summary>
59: public MinistryDatabase() { }
60:
61: ////////////////////////////////////////////////////////////////////////////
62:
63: /// <summary>
64: ///
65: /// </summary>
66: public static string AlterSessionOfCustomerDepartmentOracleDatabase
67: {
68: get
69: {
70: return @"alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS'";
71: }
72: }
73:
74: ////////////////////////////////////////////////////////////////////////////
75:
76: /// <summary>
77: ///
78: /// </summary>
79: public static string OracleSqlCommandToReadBalanceOfASingleServiceNumber(int serviceNumber)
80: {
81: return OracleSqlCommandToReadBalanceForRangeOfServiceNumbers(serviceNumber, serviceNumber);
82: }
83:
84: ////////////////////////////////////////////////////////////////////////////
85:
86: /// <summary>
87: ///
88: /// </summary>
89: public static string OracleSqlCommandToReadBalanceForRangeOfAHundredsSubdomain(Ia.Ngn.Cl.Model.Business.Service.HundredsSubdomain hundredsSubdomain)
90: {
91: return OracleSqlCommandToReadBalanceForRangeOfServiceNumbers(hundredsSubdomain.FirstServiceNumber, hundredsSubdomain.LastServiceNumber);
92: }
93:
94: ////////////////////////////////////////////////////////////////////////////
95:
96: /// <summary>
97: ///
98: /// </summary>
99: public static string OracleSqlCommandToReadBalanceForRangeOfServiceNumbers(int firstServiceNumber, int lastServiceNumber)
100: {
101: var sql = @"select distinct CSN.SRV_CAT_ID, C.CUST_CAT_ID, CSN.SRV_SER_NO, CSN.SRV_NO, CUST_SRV.CUR_SRV_BALANCE(CSN.SRV_CAT_ID, CSN.SRV_NO, CSN.SRV_SER_NO) BALANCE
102: from CUST_SRV_NOS CSN, CUSTOMERS C
103: where C.ACCOUNT_NO = CSN.ACCOUNT_NO
104: and CSN.SRV_CAT_ID = 3 and CSN.SRV_NO >= " + firstServiceNumber + @" and CSN.SRV_NO <= " + lastServiceNumber + @"
105: and (CSN.STATUS = 7001 or CSN.STATUS = 7002 or CSN.STATUS = 7003 or CSN.STATUS = 7004 or CSN.STATUS = 7008)
106: order by CSN.SRV_NO, CSN.SRV_SER_NO
107: ";
108: return sql;
109: }
110:
111: ////////////////////////////////////////////////////////////////////////////
112:
113: /// <summary>
114: ///
115: /// </summary>
116: public static string OracleSqlCommandToReturnPaymentTransactionOfThePreviousNDays(long latestTransactionId, int previousNDays)
117: {
118: var dateTime = DateTime.UtcNow.AddHours(3).AddDays(-previousNDays);
119:
120: // , CUST_SRV.CUR_SRV_BALANCE(SRV_CAT_ID ,SRV_NO ,SRV_SER_NO) BALANCE
121:
122: var sql = @"select SRV_NO, SRV_SER_NO, TRX_DATE, TRX_ID
123: from CUST_SRV_BALANCES
124: where SRV_CAT_ID = 3 and RECT_NO is not null and TRX_ID >= " + latestTransactionId + " and TRX_DATE >= to_date('" + dateTime.ToString("yyyy-MM-dd hh:mm:ss") + @"', 'yyyy-MM-dd hh24:mi:ss')
125: order by TRX_ID desc
126: ";
127:
128: return sql;
129: }
130:
131: ////////////////////////////////////////////////////////////////////////////
132:
133: /// <summary>
134: ///
135: /// </summary>
136: public static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> FilterServiceStateListByServicesWithLatestSerial(List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> serviceStateList)
137: {
138: var list = serviceStateList.GroupBy(t => t.Service).Select(g => g.OrderByDescending(t => t.Serial).First()).ToList();
139:
140: return list;
141: }
142:
143: ////////////////////////////////////////////////////////////////////////////
144:
145: /// <summary>
146: ///
147: /// </summary>
148: public static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> FilterServiceStateListByServicesThatCrossedAdministrativeDisconnectionBalanceThresholdList(List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> serviceStateList)
149: {
150: var list = new List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState>();
151:
152: foreach (var serviceState in serviceStateList)
153: {
154: if (Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceHadCrossedAdministrativeDisconnectionBalanceThreshold(serviceState.ServiceCategoryId, serviceState.CustomerCategoryId, serviceState.Balance))
155: {
156: list.Add(serviceState);
157: }
158: }
159:
160: return list;
161: }
162:
163: ////////////////////////////////////////////////////////////////////////////
164:
165: /// <summary>
166: ///
167: /// </summary>
168: public static void Update(Ia.Ngn.Cl.Model.Business.Service.HundredsSubdomain hundredsSubdomain, List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> serviceStateList, out List<string> addedServiceList, out List<string> removedServiceList)
169: {
170: addedServiceList = new List<string>();
171: removedServiceList = new List<string>();
172:
173: // add new
174: foreach (var serviceState in serviceStateList)
175: {
176: if (ServiceStateThatCrossedThresholdList.Any(u => u.ServiceCategoryId == serviceState.ServiceCategoryId
177: && u.CustomerCategoryId == serviceState.CustomerCategoryId
178: && u.Service == serviceState.Service
179: && u.Serial == serviceState.Serial
180: && u.HundredsSubdomainId == serviceState.HundredsSubdomainId))
181: {
182: }
183: else
184: {
185: ServiceStateThatCrossedThresholdList.Add(serviceState);
186:
187: addedServiceList.Add(serviceState.Service);
188: }
189: }
190:
191: // remove missing according to range
192: var list = (from s in ServiceStateThatCrossedThresholdList where s.HundredsSubdomainId == hundredsSubdomain.Id select s).ToList();
193:
194: foreach (var serviceState in list)
195: {
196: if (!serviceStateList.Any(u => u.ServiceCategoryId == serviceState.ServiceCategoryId
197: && u.CustomerCategoryId == serviceState.CustomerCategoryId
198: && u.Service == serviceState.Service
199: && u.Serial == serviceState.Serial
200: && u.HundredsSubdomainId == serviceState.HundredsSubdomainId))
201: {
202: ServiceStateThatCrossedThresholdList.Remove(serviceState);
203:
204: removedServiceList.Add(serviceState.Service);
205: }
206: else
207: {
208: }
209: }
210:
211: AddedServiceList = AddedServiceList.Union(addedServiceList).ToList();
212: AddedServiceList.Sort();
213:
214: RemovedServiceList = RemovedServiceList.Union(removedServiceList).ToList();
215: RemovedServiceList.Sort();
216: }
217:
218: ////////////////////////////////////////////////////////////////////////////
219:
220: /// <summary>
221: ///
222: /// </summary>
223: public static void Update(List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceTransaction> serviceTransactionList, out List<string> addedServiceTransactionList)
224: {
225: addedServiceTransactionList = new List<string>();
226:
227: // add new
228: foreach (var serviceTransaction in serviceTransactionList)
229: {
230: if (ServiceTransactionList.Any(u => u.Service == serviceTransaction.Service
231: && u.Serial == serviceTransaction.Serial
232: && u.TransactionId == serviceTransaction.TransactionId
233: && u.TransactionDateTime == serviceTransaction.TransactionDateTime))
234: {
235: }
236: else
237: {
238: ServiceTransactionList.Add(serviceTransaction);
239:
240: addedServiceTransactionList.Add(serviceTransaction.Service);
241: }
242: }
243: }
244:
245: ////////////////////////////////////////////////////////////////////////////
246: ////////////////////////////////////////////////////////////////////////////
247:
248: /// <summary>
249: ///
250: /// </summary>
251: public static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> ServiceStateListFromHundredsSubdomainListDataTable(Ia.Ngn.Cl.Model.Business.Service.HundredsSubdomain hundredsSubdomain, DataTable dataTable)
252: {
253: int serviceCategoryId, customerCategoryId, serviceNumber, serial;
254: float balance;
255: string service;
256:
257: var serviceStateList = new List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState>();
258:
259: if (dataTable != null)
260: {
261: foreach (DataRow dataRow in dataTable.Rows)
262: {
263: var srv_no = dataRow["SRV_NO"].ToString();
264:
265: if (int.TryParse(srv_no, out serviceNumber))
266: {
267: if (Ia.Ngn.Cl.Model.Business.Service.ServiceHasEightDigitsAndIsWithinAllowedDomainList(serviceNumber))
268: {
269: service = serviceNumber.ToString();
270:
271: serviceCategoryId = int.Parse(dataRow["SRV_CAT_ID"].ToString());
272: customerCategoryId = int.Parse(dataRow["CUST_CAT_ID"].ToString());
273: serial = int.Parse(dataRow["SRV_SER_NO"].ToString());
274: balance = float.Parse(dataRow["BALANCE"].ToString());
275:
276: var serviceState = new Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState(hundredsSubdomain, serviceCategoryId, customerCategoryId, service, serial, balance);
277:
278: serviceStateList.Add(serviceState);
279: }
280: else
281: {
282: throw new ArgumentOutOfRangeException("Service: " + serviceNumber + " is not a Kuwait landline number.");
283: }
284: }
285: else
286: {
287: throw new ArgumentOutOfRangeException("SRV_NO: " + srv_no + " from database is not in correct number format.");
288: }
289: }
290:
291: if (dataTable.Rows.Count != serviceStateList.Count)
292: {
293: throw new ArgumentOutOfRangeException("dataTable.Rows.Count: " + dataTable.Rows.Count + " and serviceStateList.Count: " + serviceStateList.Count + " are not equal.");
294: }
295: }
296:
297: return serviceStateList;
298: }
299:
300: ////////////////////////////////////////////////////////////////////////////
301:
302: /// <summary>
303: ///
304: /// </summary>
305: public static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceTransaction> ServiceSerialTransactionIdDateTimeListFromTransactionListDataTable(DataTable dataTable)
306: {
307: var list = new List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceTransaction>();
308:
309: if (dataTable != null)
310: {
311: foreach (DataRow dataRow in dataTable.Rows)
312: {
313: var srv_no = dataRow["SRV_NO"].ToString();
314:
315: if (int.TryParse(srv_no, out int serviceNumber))
316: {
317: if (Ia.Ngn.Cl.Model.Business.Service.ServiceHasEightDigitsAndIsWithinAllowedDomainList(serviceNumber))
318: {
319: var service = serviceNumber.ToString();
320: var serial = int.Parse(dataRow["SRV_SER_NO"].ToString());
321: var transactionId = long.Parse(dataRow["TRX_ID"].ToString());
322: var transactionDateTime = DateTime.Parse(dataRow["TRX_DATE"].ToString());
323:
324: list.Add(new Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceTransaction(service, serial, transactionId, transactionDateTime));
325: }
326: else
327: {
328: //throw new ArgumentOutOfRangeException("Service: " + serviceNumber + " is not an allowed number.");
329: }
330: }
331: else
332: {
333: throw new ArgumentOutOfRangeException("SRV_NO: " + srv_no + " from database is not in correct number format.");
334: }
335: }
336: }
337:
338: return list;
339: }
340:
341: ////////////////////////////////////////////////////////////////////////////
342:
343: /// <summary>
344: ///
345: /// </summary>
346: public static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.Table> TableList
347: {
348: get
349: {
350: if (tableList == null || tableList.Count == 0)
351: {
352: lock (objectLock)
353: {
354: tableList = Ia.Ngn.Cl.Model.Data.MinistryDatabase._TableList;
355: }
356: }
357:
358: return tableList;
359: }
360: }
361:
362: ////////////////////////////////////////////////////////////////////////////
363:
364: /// <summary>
365: ///
366: /// </summary>
367: private static List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.Table> _TableList
368: {
369: get
370: {
371: var list = new List<Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.Table>(1000);
372:
373: foreach (XElement x in XDocument.Element("ministryDatabase").Elements("tableList").Elements("table"))
374: {
375: var table = new Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.Table();
376:
377: // exclute Oracle database system tables:
378:
379: var owner = x.Attribute("owner").Value;
380:
381: if (!Ia.Ngn.Cl.Model.Business.Mdaa.MinistryDatabase.OracleSystemTableList.Contains(owner))
382: {
383: var name = x.Attribute("name").Value;
384:
385: table.Schema = owner;
386: table.Name = name;
387:
388: list.Add(table);
389: }
390: }
391:
392: return list;
393: }
394: }
395:
396: ////////////////////////////////////////////////////////////////////////////
397:
398: /// <summary>
399: ///
400: /// How to embed and access resources by using Visual C# http://support.microsoft.com/kb/319292/en-us
401: ///
402: /// 1. Change the "Build Action" property of your XML file from "Content" to "Embedded Resource".
403: /// 2. Add "using System.Reflection".
404: /// 3. Manifest resource stream will start with the project namespace, the location of XML file.
405: ///
406: /// </summary>
407: private static XDocument XDocument
408: {
409: get
410: {
411: if (xDocument == null)
412: {
413: lock (objectLock)
414: {
415: Assembly _assembly;
416: StreamReader streamReader;
417:
418: _assembly = Assembly.GetExecutingAssembly();
419: streamReader = new StreamReader(_assembly.GetManifestResourceStream("Ia.Ngn.Cl.model.data.mdaa.ministry-database.xml"));
420:
421: try
422: {
423: if (streamReader.Peek() != -1)
424: {
425: xDocument = System.Xml.Linq.XDocument.Load(streamReader);
426: }
427: }
428: catch (Exception)
429: {
430: }
431: finally
432: {
433: }
434: }
435: }
436:
437: return xDocument;
438: }
439: }
440:
441: ////////////////////////////////////////////////////////////////////////////
442: ////////////////////////////////////////////////////////////////////////////
443: }
444:
445: ////////////////////////////////////////////////////////////////////////////
446: ////////////////////////////////////////////////////////////////////////////
447: }