)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Home » Code Library » MinistryDatabase (Ia.Ftn.Cl.Models.Data)

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

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