)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Skip Navigation Links

Public general use code classes and xml files that we've compiled and used over the years:

MinistryDatabase support class for Optical Fiber Network (OFN) data model.

    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: }