)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Home » Code Library » MinistryDatabase (Ia.Ftn.Cl.Model.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.Model.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:     ///
   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.Ftn.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.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> ServiceStateThatCrossedThresholdList { get; set; } = new List<Ia.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState>();
   50:  
   51:         /// <summary/>
   52:         public static List<Ia.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceTransaction> ServiceTransactionList { get; set; } = new List<Ia.Ftn.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.Ftn.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.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> FilterServiceStateListByServicesWithLatestSerial(List<Ia.Ftn.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.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> FilterServiceStateListByServicesThatCrossedAdministrativeDisconnectionBalanceThresholdList(List<Ia.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> serviceStateList)
  149:         {
  150:             var list = new List<Ia.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState>();
  151:  
  152:             foreach (var serviceState in serviceStateList)
  153:             {
  154:                 if (Ia.Ftn.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.Ftn.Cl.Model.Business.Service.HundredsSubdomain hundredsSubdomain, List<Ia.Ftn.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.Ftn.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.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceState> ServiceStateListFromHundredsSubdomainListDataTable(Ia.Ftn.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.Ftn.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.Ftn.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.Ftn.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.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.ServiceTransaction> ServiceSerialTransactionIdDateTimeListFromTransactionListDataTable(DataTable dataTable)
  306:         {
  307:             var list = new List<Ia.Ftn.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.Ftn.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.Ftn.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.Ftn.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.Ftn.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.Ftn.Cl.Model.Business.Mdaa.MinistryDatabase.Table> _TableList
  368:         {
  369:             get
  370:             {
  371:                 var list = new List<Ia.Ftn.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.Ftn.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.Ftn.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.Ftn.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: }