private Boolean RunRevaluationIntern() { TDBTransaction Transaction = null; AGeneralLedgerMasterTable GlmTable = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow glmTemplate = (AGeneralLedgerMasterRow)GlmTable.NewRowTyped(false); glmTemplate.LedgerNumber = F_LedgerNum; glmTemplate.Year = F_FinancialYear; for (Int32 i = 0; i < F_ForeignAccount.Length; i++) { glmTemplate.AccountCode = F_ForeignAccount[i]; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { GlmTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(glmTemplate, Transaction); }); if (GlmTable.Rows.Count > 0) { RevaluateAccount(GlmTable, F_ExchangeRate[i], F_ForeignAccount[i]); } } return(CloseRevaluationAccountingBatch()); }
/// <summary> /// Post a budget /// </summary> private static void PostBudget(int ALedgerNumber, ABudgetRow ABudgetRow, List <ABudgetPeriodRow> ABudgetPeriodRows) { //gb5300.p string AccountCode = ABudgetRow.AccountCode; string CostCentreList = ABudgetRow.CostCentreCode; /* posting CC and parents */ //Populate list of affected Cost Centres CostCentreParentsList(ALedgerNumber, ref CostCentreList); //Locate the row for the current account AAccountRow AccountRow = (AAccountRow)GLPostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, AccountCode }); GLPostingDS.AGeneralLedgerMaster.DefaultView.Sort = String.Format("{0},{1},{2},{3}", AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName(), AGeneralLedgerMasterTable.GetAccountCodeDBName(), AGeneralLedgerMasterTable.GetCostCentreCodeDBName()); /* calculate values for budgets and store them in a temp table; uses lb_budget */ ProcessAccountParent( ALedgerNumber, AccountCode, AccountRow.DebitCreditIndicator, CostCentreList, ABudgetRow, ABudgetPeriodRows); }
/// <summary> /// /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="AAccountCode"></param> /// <param name="ACurrentFinancialYear">Number of the year after the the installation of the software</param> public TGet_GLM_Info(int ALedgerNumber, string AAccountCode, int ACurrentFinancialYear) { bool NewTransaction = false; try { TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.Year = ACurrentFinancialYear; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// Loads only GLM Data selected by Ledger Number, Year and Account Code ... /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ACurrentFinancialYear"></param> /// <param name="AAccountCode"></param> public TGlmInfo(int ALedgerNumber, int ACurrentFinancialYear, string AAccountCode) { bool NewTransaction = false; try { TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.Year = ACurrentFinancialYear; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); TLogging.LogAtLevel(1, "TGlmInfo(" + ALedgerNumber + ", " + ACurrentFinancialYear + ", " + AAccountCode + ") has loaded " + FGLMTbl.Rows.Count + " Rows."); iPtr = -1; } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// THIS METHOD DOES NOT RETURN USEFUL VALUES because the Year is not specified. /// It is only called from tests, and those tests pass, /// because there's no previous financial year in the database, /// because the data returned by this method is from the earliest year in the ledger. /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="AAccountCode"></param> /// <param name="ACostCentreCode"></param> public TGet_GLM_Info(int ALedgerNumber, string AAccountCode, string ACostCentreCode) { bool NewTransaction = false; try { TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.CostCentreCode = ACostCentreCode; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); if (FGLMTbl.Rows.Count == 0) { // String msg = TLogging.StackTraceToText(new StackTrace(true)); String msg = ""; TLogging.Log(String.Format("ERROR: No TGet_GLM_Info row found for ({0}, {1}).", ACostCentreCode, AAccountCode, msg)); } } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// Loads only GLM Data selected by Ledger Number, Year and Account Code ... /// </summary> public TGlmInfo(int ALedgerNumber, int ACurrentFinancialYear, string AAccountCode, TDataBase ADataBase = null) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TGlmInfo", ADataBase); db.ReadTransaction( ref transaction, delegate { FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.Year = ACurrentFinancialYear; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); TLogging.LogAtLevel(1, "TGlmInfo(" + ALedgerNumber + ", " + ACurrentFinancialYear + ", " + AAccountCode + ") has loaded " + FGLMTbl.Rows.Count + " Rows."); iPtr = -1; }); if (ADataBase == null) { db.CloseDBConnection(); } }
/// <summary> /// THIS METHOD DOES NOT RETURN USEFUL VALUES because the Year is not specified. /// It is only called from tests, and those tests pass, /// because there's no previous financial year in the database, /// because the data returned by this method is from the earliest year in the ledger. /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="AAccountCode"></param> /// <param name="ACostCentreCode"></param> /// <param name="ADataBase"></param> public TGet_GLM_Info(int ALedgerNumber, string AAccountCode, string ACostCentreCode, TDataBase ADataBase = null) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TGet_GLM_Info", ADataBase); db.ReadTransaction(ref transaction, delegate { FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.CostCentreCode = ACostCentreCode; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); if (FGLMTbl.Rows.Count == 0) { // String msg = TLogging.StackTraceToText(new StackTrace(true)); String msg = ""; TLogging.Log(String.Format("ERROR: No TGet_GLM_Info row found for ({0}, {1}).", ACostCentreCode, AAccountCode, msg)); } }); if (ADataBase == null) { db.CloseDBConnection(); } }
public static int GetGLMSequenceForBudget(int ALedgerNumber, string AAccountCode, string ACostCentreCode, int AYear) { int retVal; AGeneralLedgerMasterTable GeneralLedgerMasterTable = null; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { GeneralLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByUniqueKey(ALedgerNumber, AYear, AAccountCode, ACostCentreCode, Transaction); }); if (GeneralLedgerMasterTable.Count > 0) { retVal = (int)GeneralLedgerMasterTable.Rows[0].ItemArray[0]; } else { retVal = -1; } return(retVal); }
public static int GetGLMSequenceForBudget(int ALedgerNumber, string AAccountCode, string ACostCentreCode, int AYear) { int retVal; AGeneralLedgerMasterTable GeneralLedgerMasterTable = null; TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetGLMSequenceForBudget"); db.ReadTransaction( ref Transaction, delegate { GeneralLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByUniqueKey(ALedgerNumber, AYear, AAccountCode, ACostCentreCode, Transaction); }); if (GeneralLedgerMasterTable.Count > 0) { retVal = (int)GeneralLedgerMasterTable.Rows[0].ItemArray[0]; } else { retVal = -1; } return(retVal); }
/// <summary> /// Loads <b>all</b> GLMP data for the selected year /// </summary> public void LoadByYear(Int32 AYear) { bool NewTransaction = false; try { TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); AGeneralLedgerMasterTable GLMTemplateTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = GLMTemplateTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = FLedgerNumber; GLMTemplateRow.Year = AYear; FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadViaAGeneralLedgerMasterTemplate(GLMTemplateRow, transaction); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// Returns true if it seems to be OK. private Boolean RunRevaluationIntern() { TDBTransaction Transaction = null; AGeneralLedgerMasterTable GlmTable = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow glmTemplate = (AGeneralLedgerMasterRow)GlmTable.NewRowTyped(false); Boolean transactionsWereCreated = false; glmTemplate.LedgerNumber = F_LedgerNum; glmTemplate.Year = F_FinancialYear; for (Int32 i = 0; i < F_ForeignAccount.Length; i++) { glmTemplate.AccountCode = F_ForeignAccount[i]; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { GlmTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(glmTemplate, Transaction); }); if (GlmTable.Rows.Count > 0) { transactionsWereCreated |= RevaluateAccount(GlmTable, F_ExchangeRate[i], F_ForeignAccount[i]); } } Boolean batchPostedOK = true; if (transactionsWereCreated) { batchPostedOK = CloseRevaluationAccountingBatch(); } if (batchPostedOK) { if (!transactionsWereCreated) // If no transactions were needed, I'll just advise the user: { FVerificationCollection.Add(new TVerificationResult( "Post Forex Batch", "Exchange rates are unchanged - no revaluation was required.", TResultSeverity.Resv_Status)); } for (Int32 i = 0; i < F_ForeignAccount.Length; i++) { TLedgerInitFlag.RemoveFlagComponent(F_LedgerNum, MFinanceConstants.LEDGER_INIT_FLAG_REVAL, F_ForeignAccount[i]); } } else { FVerificationCollection.Add(new TVerificationResult( "Post Forex Batch", "The Revaluation Batch could not be posted.", TResultSeverity.Resv_Critical)); } return(batchPostedOK); }
private void SetupGrid() { grdDetails.Columns.Clear(); grdDetails.AddTextColumn(Catalog.GetString("Currency Code"), FMainDS.AAccount.ColumnForeignCurrencyCode); grdDetails.AddTextColumn(Catalog.GetString("Account Code"), FMainDS.AAccount.ColumnAccountCode); grdDetails.AddTextColumn(Catalog.GetString("Description"), FMainDS.AAccount.ColumnAccountCodeShortDesc); grdDetails.AddCurrencyColumn(Catalog.GetString("YTD Total"), FForeignCurrencyAccountsDT.Columns[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()]); grdDetails.AddCurrencyColumn(Catalog.GetString("YTD Total (BASE)"), FForeignCurrencyAccountsDT.Columns[AGeneralLedgerMasterTable.GetYtdActualBaseDBName()]); }
private void RunRevaluationIntern() { Boolean NewTransaction; TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); AAccountTable accountTable = new AAccountTable(); AAccountRow accountTemplate = (AAccountRow)accountTable.NewRowTyped(false); accountTemplate.LedgerNumber = F_LedgerNum; accountTemplate.AccountActiveFlag = true; accountTemplate.ForeignCurrencyFlag = true; accountTable = AAccountAccess.LoadUsingTemplate(accountTemplate, DBTransaction); AGeneralLedgerMasterTable glmTable = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow glmTemplate = (AGeneralLedgerMasterRow)glmTable.NewRowTyped(false); glmTemplate.LedgerNumber = F_LedgerNum; glmTemplate.Year = F_FinancialYear; glmTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(glmTemplate, DBTransaction); if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } if (accountTable.Rows.Count == 0) // not using any foreign accounts? { return; } for (int iCnt = 0; iCnt < accountTable.Rows.Count; ++iCnt) { AAccountRow accountRow = (AAccountRow)accountTable[iCnt]; for (int kCnt = 0; kCnt < F_CurrencyCode.Length; ++kCnt) { // AForeignCurrency[] and ANewExchangeRate[] shall support a value // for this account resp. for the currency of the account if (accountRow.ForeignCurrencyCode.Equals(F_CurrencyCode[kCnt])) { glmTable.DefaultView.RowFilter = "a_account_code_c = '" + accountRow.AccountCode + "'"; if (glmTable.DefaultView.Count > 0) { RevaluateAccount(glmTable.DefaultView, F_ExchangeRate[kCnt]); } } } } CloseRevaluationAccountingBatch(); }
private DataTable LoadTable(int ALedgerNumber, int AYear, TDBTransaction ATransaction) { AGeneralLedgerMasterTable typedTable = new AGeneralLedgerMasterTable(); string strSQL = "SELECT * FROM PUB_a_general_ledger_master" + " WHERE a_ledger_number_i = " + ALedgerNumber + " AND a_year_i = " + AYear + " ORDER BY a_account_code_c,a_cost_centre_code_c"; DBAccess.GDBAccessObj.SelectDT(typedTable, strSQL, ATransaction); return typedTable; }
public static void GetForeignCurrencyAccountActuals(ref DataTable AForeignCurrencyAccounts, Int32 ALedgerNumber, Int32 AYear) { DataTable ForeignCurrencyAccounts = AForeignCurrencyAccounts; TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetForeignCurrencyAccountActuals"); db.ReadTransaction(ref Transaction, delegate { AGeneralLedgerMasterTable glmTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = glmTbl.NewRowTyped(false); foreach (DataRow ForeignCurrencyAccountRow in ForeignCurrencyAccounts.Rows) { GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.Year = AYear; GLMTemplateRow.AccountCode = ForeignCurrencyAccountRow[AAccountTable.GetAccountCodeDBName()].ToString(); glmTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, Transaction); Decimal YtdActual = 0; Decimal YtdActualForeign = 0; if (glmTbl != null) { // // I need to sum up all the GLM entries for this account: foreach (AGeneralLedgerMasterRow glmRow in glmTbl.Rows) { if (!glmRow.IsYtdActualForeignNull()) { YtdActualForeign += glmRow.YtdActualForeign; } YtdActual += glmRow.YtdActualBase; } } ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualBaseDBName()] = YtdActual; ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = YtdActualForeign; } }); db.CloseDBConnection(); }
/// <summary> /// Unpost a budget /// </summary> /// <param name="ABudgetRow"></param> /// <param name="ALedgerNumber"></param> /// <returns>true if it seemed to go OK</returns> private static bool UnPostBudget(ABudgetRow ABudgetRow, int ALedgerNumber) { /* post the negative budget, which will result in an empty a_glm_period.budget */ // get the current budget value for each GLM Period, and unpost that budget GLPostingDS.AGeneralLedgerMaster.DefaultView.Sort = String.Format("{0},{1},{2},{3}", AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName(), AGeneralLedgerMasterTable.GetAccountCodeDBName(), AGeneralLedgerMasterTable.GetCostCentreCodeDBName()); int glmIndex = GLPostingDS.AGeneralLedgerMaster.DefaultView.Find( new object[] { ALedgerNumber, ABudgetRow.Year, ABudgetRow.AccountCode, ABudgetRow.CostCentreCode }); if (glmIndex != -1) { AGeneralLedgerMasterRow glmRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.DefaultView[glmIndex].Row; List <ABudgetPeriodRow> budgetPeriods = new List <ABudgetPeriodRow>(); for (int Period = 1; Period <= GLPostingDS.ALedger[0].NumberOfAccountingPeriods; Period++) { AGeneralLedgerMasterPeriodRow GeneralLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)GLPostingDS.AGeneralLedgerMasterPeriod.Rows.Find( new object[] { glmRow.GlmSequence, Period }); ABudgetPeriodRow budgetPeriodRow = FBudgetTDS.ABudgetPeriod.NewRowTyped(true); budgetPeriodRow.PeriodNumber = Period; budgetPeriodRow.BudgetSequence = ABudgetRow.BudgetSequence; // use negative amount for unposting budgetPeriodRow.BudgetBase = -1 * GeneralLedgerMasterPeriodRow.BudgetBase; // do not add to the budgetperiod table, but to our local list budgetPeriods.Add(budgetPeriodRow); } PostBudget(ALedgerNumber, ABudgetRow, budgetPeriods); } ABudgetRow.BudgetStatus = false; //i.e. unposted return(true); }
public static void GetForeignCurrencyAccountActuals(ref DataTable AForeignCurrencyAccounts, Int32 ALedgerNumber, Int32 AYear) { //string ReturnValue = ""; DataTable ForeignCurrencyAccounts = AForeignCurrencyAccounts.Clone(); ForeignCurrencyAccounts.Merge(AForeignCurrencyAccounts); string CostCentreCode = "[" + ALedgerNumber + "]"; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.BeginAutoReadTransaction(ref Transaction, delegate { foreach (DataRow ForeignCurrencyAccountRow in ForeignCurrencyAccounts.Rows) { AGeneralLedgerMasterTable Table = AGeneralLedgerMasterAccess.LoadByUniqueKey( ALedgerNumber, AYear, ForeignCurrencyAccountRow[AAccountTable.GetAccountCodeDBName()].ToString(), CostCentreCode, Transaction); if ((Table != null) && (Table.Rows.Count > 0)) { AGeneralLedgerMasterRow Row = Table[0]; if (Row.IsYtdActualForeignNull()) { ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = 0; } else { ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = Row.YtdActualForeign; } ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualBaseDBName()] = Row.YtdActualBase; } else { ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = 0; ForeignCurrencyAccountRow[AGeneralLedgerMasterTable.GetYtdActualBaseDBName()] = 0; } } }); AForeignCurrencyAccounts = ForeignCurrencyAccounts; }
/// <summary> /// Loads <b>all</b> GLMP data for the selected year /// </summary> public void LoadByYear(Int32 AYear) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadByYear", FDataBase); db.ReadTransaction(ref transaction, delegate { AGeneralLedgerMasterTable GLMTemplateTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = GLMTemplateTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = FLedgerNumber; GLMTemplateRow.Year = AYear; FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadViaAGeneralLedgerMasterTemplate(GLMTemplateRow, transaction); }); if (FDataBase == null) { db.CloseDBConnection(); } }
/// <summary> /// /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="AAccountCode"></param> /// <param name="ACurrentFinancialYear">Number of the year after the the installation of the software</param> /// <param name="ADataBase"></param> public TGet_GLM_Info(int ALedgerNumber, string AAccountCode, int ACurrentFinancialYear, TDataBase ADataBase = null) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TGet_GLM_Info", ADataBase); db.ReadTransaction(ref transaction, delegate { FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.Year = ACurrentFinancialYear; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); }); if (ADataBase == null) { db.CloseDBConnection(); } }
/// <summary> /// </summary> public TGlmNewYearInit(TLedgerInfo ALedgerInfo, int AYear, TYearEnd AYearEndOperator) { FOldYearNum = AYear; FNewYearNum = FOldYearNum + 1; FLedgerInfo = ALedgerInfo; FYearEndOperator = AYearEndOperator; FLedgerAccountingPeriods = FLedgerInfo.NumberOfAccountingPeriods; // Don't call these properties in a loop, FLedgerFwdPeriods = FLedgerInfo.NumberFwdPostingPeriods; // as they reload the row from the DB! bool NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); try { DataTable GlmTble = LoadTable(FLedgerInfo.LedgerNumber, FOldYearNum, Transaction); FGlmPostingFrom.Merge(GlmTble); GlmTble = LoadTable(FLedgerInfo.LedgerNumber, FNewYearNum, Transaction); GlmTDS.AGeneralLedgerMaster.Merge(GlmTble); GlmTDS.AGeneralLedgerMaster.DefaultView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName(); DataTable GlmpTbl = GetGlmpRows(FOldYearNum, Transaction, FLedgerAccountingPeriods); FGlmpFrom.Merge(GlmpTbl); FGlmpFrom.DefaultView.Sort = "a_glm_sequence_i,a_period_number_i"; GlmpTbl = GetGlmpRows(FNewYearNum, Transaction, 0); GlmTDS.AGeneralLedgerMasterPeriod.Merge(GlmpTbl); GlmTDS.AGeneralLedgerMasterPeriod.DefaultView.Sort = "a_glm_sequence_i,a_period_number_i"; } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// Post a budget /// </summary> private static void PostBudget(int ALedgerNumber, ABudgetRow ABudgetRow, List <ABudgetPeriodRow> ABudgetPeriodRows) { GLPostingDS.AGeneralLedgerMaster.DefaultView.Sort = String.Format("{0},{1},{2},{3}", AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName(), AGeneralLedgerMasterTable.GetAccountCodeDBName(), AGeneralLedgerMasterTable.GetCostCentreCodeDBName()); int glmRowIndex = GLPostingDS.AGeneralLedgerMaster.DefaultView.Find(new object[] { ALedgerNumber, ABudgetRow.Year, ABudgetRow.AccountCode, ABudgetRow.CostCentreCode }); if (glmRowIndex == -1) { TGLPosting.CreateGLMYear(ref GLPostingDS, ALedgerNumber, ABudgetRow.Year, ABudgetRow.AccountCode, ABudgetRow.CostCentreCode); glmRowIndex = GLPostingDS.AGeneralLedgerMaster.DefaultView.Find(new object[] { ALedgerNumber, ABudgetRow.Year, ABudgetRow.AccountCode, ABudgetRow.CostCentreCode }); } int GLMSequence = ((AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.DefaultView[glmRowIndex].Row).GlmSequence; /* Update totals for the General Ledger Master period record. */ foreach (ABudgetPeriodRow BPR in ABudgetPeriodRows) { AddBudgetValue(GLMSequence, BPR.PeriodNumber, BPR.BudgetBase); } }
/// <summary> /// Get the actual amount /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="AGLMSeqThisYear"></param> /// <param name="AGLMSeqNextYear"></param> /// <param name="APeriodNumber"></param> /// <param name="ANumberAccountingPeriods"></param> /// <param name="ACurrentFinancialYear"></param> /// <param name="AThisYear"></param> /// <param name="AYTD"></param> /// <param name="ABalSheetForwardPeriods"></param> /// <param name="ACurrencySelect"></param> /// <returns></returns> private static decimal GetActualInternal(int ALedgerNumber, int AGLMSeqThisYear, int AGLMSeqNextYear, int APeriodNumber, int ANumberAccountingPeriods, int ACurrentFinancialYear, int AThisYear, bool AYTD, bool ABalSheetForwardPeriods, string ACurrencySelect) { decimal retVal = 0; decimal currencyAmount = 0; bool incExpAccountFwdPeriod = false; //DEFINE BUFFER a_glm_period FOR a_general_ledger_master_period. //DEFINE BUFFER a_glm FOR a_general_ledger_master. //DEFINE BUFFER buf_account FOR a_account. if (AGLMSeqThisYear == -1) { return(retVal); } bool newTransaction = false; TDataBase db = DBAccess.Connect("GetActualInternal"); TDBTransaction dBTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out newTransaction); AGeneralLedgerMasterTable generalLedgerMasterTable = null; AGeneralLedgerMasterRow generalLedgerMasterRow = null; AGeneralLedgerMasterPeriodTable generalLedgerMasterPeriodTable = null; AGeneralLedgerMasterPeriodRow generalLedgerMasterPeriodRow = null; AAccountTable AccountTable = null; AAccountRow AccountRow = null; try { if (APeriodNumber == 0) /* start balance */ { generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction); generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0]; switch (ACurrencySelect) { case MFinanceConstants.CURRENCY_BASE: currencyAmount = generalLedgerMasterRow.StartBalanceBase; break; case MFinanceConstants.CURRENCY_INTERNATIONAL: currencyAmount = generalLedgerMasterRow.StartBalanceIntl; break; default: currencyAmount = generalLedgerMasterRow.StartBalanceForeign; break; } } else if (APeriodNumber > ANumberAccountingPeriods) /* forwarding periods only exist for the current financial year */ { if (ACurrentFinancialYear == AThisYear) { generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear, APeriodNumber, dBTransaction); generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0]; } else { generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqNextYear, (APeriodNumber - ANumberAccountingPeriods), dBTransaction); generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0]; } } else /* normal period */ { generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear, APeriodNumber, dBTransaction); generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0]; } if (generalLedgerMasterPeriodRow != null) { switch (ACurrencySelect) { case MFinanceConstants.CURRENCY_BASE: currencyAmount = generalLedgerMasterPeriodRow.ActualBase; break; case MFinanceConstants.CURRENCY_INTERNATIONAL: currencyAmount = generalLedgerMasterPeriodRow.ActualIntl; break; default: currencyAmount = generalLedgerMasterPeriodRow.ActualForeign; break; } } if ((APeriodNumber > ANumberAccountingPeriods) && (ACurrentFinancialYear == AThisYear)) { generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction); generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0]; AccountTable = AAccountAccess.LoadByPrimaryKey(ALedgerNumber, generalLedgerMasterRow.AccountCode, dBTransaction); AccountRow = (AAccountRow)AccountTable.Rows[0]; if ((AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) || (AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_EXPENSE.ToUpper()) && !ABalSheetForwardPeriods) { incExpAccountFwdPeriod = true; currencyAmount -= GetActualInternal(ALedgerNumber, AGLMSeqThisYear, AGLMSeqNextYear, ANumberAccountingPeriods, ANumberAccountingPeriods, ACurrentFinancialYear, AThisYear, true, ABalSheetForwardPeriods, ACurrencySelect); } } if (!AYTD) { if (!((APeriodNumber == (ANumberAccountingPeriods + 1)) && incExpAccountFwdPeriod) && !((APeriodNumber == (ANumberAccountingPeriods + 1)) && (ACurrentFinancialYear > AThisYear))) { /* if it is an income expense acount, and we are in period 13, nothing needs to be subtracted, * because that was done in correcting the amount in the block above; * if we are in a previous year, in period 13, don't worry about subtracting. * * THIS IS CLEARLY INCORRECT - THE CONDITION ABOVE APPLIES *ONLY* IN THE FIRST FORWARDING PERIOD, NOT IN EVERY FORWARDING PERIOD. * IF THE METHOD IS ONLY CALLED FROM AUTOGENERATE BUDGETS, THIS IS PROBABLY INCONSEQUENTIAL. */ currencyAmount -= GetActualInternal(ALedgerNumber, AGLMSeqThisYear, AGLMSeqNextYear, (APeriodNumber - 1), ANumberAccountingPeriods, ACurrentFinancialYear, AThisYear, true, ABalSheetForwardPeriods, ACurrencySelect); } } retVal = currencyAmount; } finally { if (newTransaction) { dBTransaction.Rollback(); } } return(retVal); }
} // Run Revaluation private Boolean RevaluateAccount(AGeneralLedgerMasterTable AGlmTbl, decimal AExchangeRate, string ACurrencyCode) { Boolean transactionsWereCreated = false; foreach (AGeneralLedgerMasterRow glmRow in AGlmTbl.Rows) { AGeneralLedgerMasterPeriodTable glmpTbl = null; TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref transaction, delegate { glmpTbl = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(glmRow.GlmSequence, F_AccountingPeriod, transaction); }); try { if (glmpTbl.Rows.Count == 0) { continue; // I really don't expect this, but if it does happen, this will prevent a crash! } AGeneralLedgerMasterPeriodRow glmpRow = glmpTbl[0]; // // If ActualForeign has not been set, I can't allow the ORM to even attempt to access them: // (If ActualForeign is NULL, that's probably a fault, but this has occured in historical data.) if (glmpRow.IsActualBaseNull() || glmpRow.IsActualForeignNull()) { continue; } decimal delta = AccountDelta(glmpRow.ActualBase, glmpRow.ActualForeign, AExchangeRate, F_BaseCurrencyDigits); if (delta != 0) { // Now we have the relevant Cost Centre ... RevaluateCostCentre(glmRow.AccountCode, glmRow.CostCentreCode, delta, AExchangeRate, ACurrencyCode); transactionsWereCreated = true; } else { string strMessage = String.Format( Catalog.GetString("The account {1}:{0} does not require revaluation."), glmRow.AccountCode, glmRow.CostCentreCode, AExchangeRate); FVerificationCollection.Add(new TVerificationResult( strStatusContent, strMessage, TResultSeverity.Resv_Status)); TLedgerInitFlag.RemoveFlagComponent(F_LedgerNum, MFinanceConstants.LEDGER_INIT_FLAG_REVAL, glmRow.AccountCode); } } catch (EVerificationException terminate) { FVerificationCollection = terminate.ResultCollection(); } } // foreach return(transactionsWereCreated); }
/// <summary> /// THIS METHOD DOES NOT RETURN USEFUL VALUES because the Year is not specified. /// It is only called from tests, and those tests pass, /// because there's no previous financial year in the database, /// because the data returned by this method is from the earliest year in the ledger. /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="AAccountCode"></param> /// <param name="ACostCentreCode"></param> public TGet_GLM_Info(int ALedgerNumber, string AAccountCode, string ACostCentreCode) { bool NewTransaction = false; try { TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.CostCentreCode = ACostCentreCode; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); if (FGLMTbl.Rows.Count == 0) { TLogging.Log(String.Format("\nERROR: No TGet_GLM_Info row found for ({0}, {1}).\n", AAccountCode, ACostCentreCode)); } } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
public void T0_Consolidation() { // reset the database, so that there is no consolidated budget CommonNUnitFunctions.ResetDatabase(); string budgetTestFile = TAppSettingsManager.GetValue("GiftBatch.file", CommonNUnitFunctions.rootPath + "/csharp/ICT/Testing/lib/MFinance/SampleData/BudgetImport-All.csv"); int NumBudgetsUpdated; int NumFailedRows; TVerificationResultCollection VerificationResult; BudgetTDS ImportDS = new BudgetTDS(); string ImportString = File.ReadAllText(budgetTestFile); // import budget from CSV decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets( FLedgerNumber, 0, ImportString, budgetTestFile, new string[] { ",", "dmy", "American" }, ref ImportDS, out NumBudgetsUpdated, out NumFailedRows, out VerificationResult); Assert.AreNotEqual(0, RowsImported, "expect to import several rows"); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult, "ImportBudgets has critical errors:"); BudgetTDSAccess.SubmitChanges(ImportDS); // check for value in budget table string sqlQueryBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_revision_i = 0 AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), ABudgetPeriodTable.GetTableDBName(), FLedgerNumber); decimal budgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(250m, budgetValue, "problem with importing budget from CSV"); // check for zero in glmperiod budget: that row does not even exist yet, so check that it does not exist string sqlQueryCheckEmptyConsolidatedBudget = String.Format( "SELECT COUNT(*) FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {2} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); Assert.AreEqual(0, DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryCheckEmptyConsolidatedBudget, IsolationLevel.ReadCommitted), "budget should not be consolidated yet"); // consolidate the budget TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); // check for correct value in glmperiod budget string sqlQueryConsolidatedBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_glm_sequence_i = {2}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetBudgetBaseDBName(), AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); decimal consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(250m, consolidatedBudgetValue, "budget should now be consolidated"); // TODO: also check some summary account and cost centre for summed up budget values // check how reposting a budget works string sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 44 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); bool SubmissionOK = true; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction); }); // post all budgets again TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(44.0m, consolidatedBudgetValue, "budget should be consolidated with the new value"); // post only a modified budget (testing UnPostBudget) sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 65 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); string sqlMarkBudgetForConsolidation = String.Format("UPDATE PUB_{0} SET {1} = false WHERE " + "a_ledger_number_i = {2} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetTable.GetTableDBName(), ABudgetTable.GetBudgetStatusDBName(), FLedgerNumber); SubmissionOK = true; Transaction = null; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction); DBAccess.GDBAccessObj.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction); }); // post only modified budget again TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, false); consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(65.0m, consolidatedBudgetValue, "budget should be consolidated with the new value, after UnPostBudget"); // TODO: test forwarding periods. what happens to next year values, when there is no next year glm record yet? }
private void RevaluateAccount(AGeneralLedgerMasterTable AGlmTbl, decimal AExchangeRate, string ACurrencyCode) { foreach (AGeneralLedgerMasterRow glmRow in AGlmTbl.Rows) { AGeneralLedgerMasterPeriodTable glmpTbl = null; TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref transaction, delegate { glmpTbl = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(glmRow.GlmSequence, F_AccountingPeriod, transaction); }); try { if (glmpTbl.Rows.Count == 0) { continue; // I really don't expect this, but if it does happen, this will prevent a crash! } AGeneralLedgerMasterPeriodRow glmpRow = glmpTbl[0]; // // If ActualForeign has not been set, I can't allow the ORM to even attempt to access them: // (If ActualForeign is NULL, that's probably a fault, but this has occured in historical data.) if (glmpRow.IsActualBaseNull() || glmpRow.IsActualForeignNull()) { continue; } decimal delta = AccountDelta(glmpRow.ActualBase, glmpRow.ActualForeign, AExchangeRate, F_BaseCurrencyDigits); if (delta != 0) { // Now we have the relevant Cost Centre ... RevaluateCostCentre(glmRow.AccountCode, glmRow.CostCentreCode, delta, AExchangeRate, ACurrencyCode); } else { string strMessage = String.Format( Catalog.GetString("The account {1}:{0} does not require revaluation."), glmRow.AccountCode, glmRow.CostCentreCode, AExchangeRate); FVerificationCollection.Add(new TVerificationResult( strStatusContent, strMessage, TResultSeverity.Resv_Status)); } } catch (EVerificationException terminate) { FVerificationCollection = terminate.ResultCollection(); } } // foreach }
private Boolean RunRevaluationIntern() { TDBTransaction Transaction = null; AGeneralLedgerMasterTable GlmTable = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow glmTemplate = (AGeneralLedgerMasterRow)GlmTable.NewRowTyped(false); glmTemplate.LedgerNumber = F_LedgerNum; glmTemplate.Year = F_FinancialYear; for (Int32 i = 0; i < F_ForeignAccount.Length; i++) { glmTemplate.AccountCode = F_ForeignAccount[i]; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { GlmTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(glmTemplate, Transaction); }); if (GlmTable.Rows.Count > 0) { RevaluateAccount(GlmTable, F_ExchangeRate[i], F_ForeignAccount[i]); } } return CloseRevaluationAccountingBatch(); }
/// <summary> /// Loads only GLM Data selected by Ledger Number, Year and Account Code ... /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ACurrentFinancialYear"></param> /// <param name="AAccountCode"></param> public TGlmInfo(int ALedgerNumber, int ACurrentFinancialYear, string AAccountCode) { bool NewTransaction = false; try { TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); FGLMTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = FGLMTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.AccountCode = AAccountCode; GLMTemplateRow.Year = ACurrentFinancialYear; FGLMTbl = AGeneralLedgerMasterAccess.LoadUsingTemplate(GLMTemplateRow, transaction); iPtr = -1; } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
private void LoadAll(Int32 ALedgerNumber, Int32 AYear) { bool NewTransaction = false; try { TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); AGeneralLedgerMasterTable GLMTemplateTbl = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow GLMTemplateRow = GLMTemplateTbl.NewRowTyped(false); GLMTemplateRow.LedgerNumber = ALedgerNumber; GLMTemplateRow.Year = AYear; FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadViaAGeneralLedgerMasterTemplate(GLMTemplateRow, transaction); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
public static TSubmitChangesResult SaveLedgerSettings( Int32 ALedgerNumber, DateTime ACalendarStartDate, ref GLSetupTDS AInspectDS) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (AInspectDS == null) { return TSubmitChangesResult.scrNothingToBeSaved; } #endregion Validate Arguments ALedgerTable LedgerTable; ALedgerRow LedgerRow; AAccountingPeriodTable AccountingPeriodTable; AAccountingPeriodRow AccountingPeriodRow; AAccountingPeriodTable NewAccountingPeriodTable; AAccountingPeriodRow NewAccountingPeriodRow; AGeneralLedgerMasterTable GLMTable; AGeneralLedgerMasterRow GLMRow; AGeneralLedgerMasterPeriodTable GLMPeriodTable; AGeneralLedgerMasterPeriodTable TempGLMPeriodTable; AGeneralLedgerMasterPeriodTable NewGLMPeriodTable; AGeneralLedgerMasterPeriodRow GLMPeriodRow; AGeneralLedgerMasterPeriodRow TempGLMPeriodRow; AGeneralLedgerMasterPeriodRow NewGLMPeriodRow; int CurrentNumberPeriods; int NewNumberPeriods; int CurrentNumberFwdPostingPeriods; int NewNumberFwdPostingPeriods; int CurrentLastFwdPeriod; int NewLastFwdPeriod; int Period; Boolean ExtendFwdPeriods = false; DateTime PeriodStartDate; DateTime CurrentCalendarStartDate; Boolean CreateCalendar = false; TDBTransaction Transaction = null; bool SubmissionOK = false; GLSetupTDS InspectDS = AInspectDS; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, TEnforceIsolationLevel.eilMinimum, ref Transaction, ref SubmissionOK, delegate { // load ledger row currently saved in database so it can be used for comparison with modified data LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); #region Validate Data if ((LedgerTable == null) || (LedgerTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data LedgerRow = (ALedgerRow)LedgerTable.Rows[0]; if (InspectDS.ALedger != null) { // initialize variables for accounting periods and forward periods CurrentNumberPeriods = LedgerRow.NumberOfAccountingPeriods; NewNumberPeriods = ((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods; CurrentNumberFwdPostingPeriods = LedgerRow.NumberFwdPostingPeriods; NewNumberFwdPostingPeriods = ((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberFwdPostingPeriods; // retrieve currently saved calendar start date (start date of financial year) AAccountingPeriodTable CalendarTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, 1, Transaction); CurrentCalendarStartDate = DateTime.MinValue; if (CalendarTable.Count > 0) { CurrentCalendarStartDate = ((AAccountingPeriodRow)CalendarTable.Rows[0]).PeriodStartDate; } // update accounting periods (calendar): // this only needs to be done if the calendar mode is changed // or if calendar mode is monthly and the start date has changed // or if not monthly and number of periods has changed if (((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode != LedgerRow.CalendarMode) { CreateCalendar = true; } else if (((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode && (ACalendarStartDate != CurrentCalendarStartDate)) { CreateCalendar = true; } else if (!((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode && (NewNumberPeriods != CurrentNumberPeriods)) { CreateCalendar = true; } if (!CreateCalendar && (NewNumberFwdPostingPeriods < CurrentNumberFwdPostingPeriods)) { CreateCalendar = true; } if (!CreateCalendar && (NewNumberFwdPostingPeriods > CurrentNumberFwdPostingPeriods)) { // in this case only extend the periods (as there may already be existing transactions) ExtendFwdPeriods = true; } // now perform the actual update of accounting periods (calendar) if (CreateCalendar) { // first make sure all accounting period records are deleted if (AAccountingPeriodAccess.CountViaALedger(ALedgerNumber, Transaction) > 0) { AAccountingPeriodTable TemplateTable = new AAccountingPeriodTable(); AAccountingPeriodRow TemplateRow = TemplateTable.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; AAccountingPeriodAccess.DeleteUsingTemplate(TemplateRow, null, Transaction); } // now create all accounting period records according to monthly calendar mode // (at the same time create forwarding periods. If number of forwarding periods also // changes with this saving method then this will be dealt with further down in the code) NewAccountingPeriodTable = new AAccountingPeriodTable(); PeriodStartDate = ACalendarStartDate; for (Period = 1; Period <= NewNumberPeriods; Period++) { NewAccountingPeriodRow = NewAccountingPeriodTable.NewRowTyped(); NewAccountingPeriodRow.LedgerNumber = ALedgerNumber; NewAccountingPeriodRow.AccountingPeriodNumber = Period; NewAccountingPeriodRow.PeriodStartDate = PeriodStartDate; //TODO: Calendar vs Financial Date Handling - Check for current ledger number of periods if ((((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods == 13) && (Period == 12)) { // in case of 12 periods the second last period represents the last month except for the very last day NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate.AddMonths(1).AddDays(-2); } else if ((((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods == 13) && (Period == 13)) { // in case of 13 periods the last period just represents the very last day of the financial year NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate; } else { NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate.AddMonths(1).AddDays(-1); } NewAccountingPeriodRow.AccountingPeriodDesc = PeriodStartDate.ToString("MMMM"); NewAccountingPeriodTable.Rows.Add(NewAccountingPeriodRow); PeriodStartDate = NewAccountingPeriodRow.PeriodEndDate.AddDays(1); } AAccountingPeriodAccess.SubmitChanges(NewAccountingPeriodTable, Transaction); TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing( TCacheableFinanceTablesEnum.AccountingPeriodList.ToString()); CurrentNumberPeriods = NewNumberPeriods; } // check if any new forwarding periods need to be created if (CreateCalendar || ExtendFwdPeriods) { // now create new forwarding posting periods (if at all needed) NewAccountingPeriodTable = new AAccountingPeriodTable(); // if calendar was created then there are no forward periods yet if (CreateCalendar) { Period = CurrentNumberPeriods + 1; } else { Period = CurrentNumberPeriods + CurrentNumberFwdPostingPeriods + 1; } while (Period <= NewNumberPeriods + NewNumberFwdPostingPeriods) { AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, Period - CurrentNumberPeriods, Transaction); AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0]; NewAccountingPeriodRow = NewAccountingPeriodTable.NewRowTyped(); NewAccountingPeriodRow.LedgerNumber = ALedgerNumber; NewAccountingPeriodRow.AccountingPeriodNumber = Period; NewAccountingPeriodRow.AccountingPeriodDesc = AccountingPeriodRow.AccountingPeriodDesc; NewAccountingPeriodRow.PeriodStartDate = AccountingPeriodRow.PeriodStartDate.AddYears(1); NewAccountingPeriodRow.PeriodEndDate = AccountingPeriodRow.PeriodEndDate.AddYears(1); NewAccountingPeriodTable.Rows.Add(NewAccountingPeriodRow); Period++; } AAccountingPeriodAccess.SubmitChanges(NewAccountingPeriodTable, Transaction); TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing( TCacheableFinanceTablesEnum.AccountingPeriodList.ToString()); // also create new general ledger master periods with balances CurrentLastFwdPeriod = LedgerRow.NumberOfAccountingPeriods + CurrentNumberFwdPostingPeriods; NewLastFwdPeriod = LedgerRow.NumberOfAccountingPeriods + NewNumberFwdPostingPeriods; // TODO: the following 2 lines would need to replace the 2 lines above if not all possible forward periods are created initially //CurrentLastFwdPeriod = LedgerRow.CurrentPeriod + CurrentNumberFwdPostingPeriods; //NewLastFwdPeriod = LedgerRow.CurrentPeriod + NewNumberFwdPostingPeriods; GLMTable = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow template = GLMTable.NewRowTyped(false); template.LedgerNumber = ALedgerNumber; template.Year = LedgerRow.CurrentFinancialYear; // find all general ledger master records of the current financial year for given ledger GLMTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(template, Transaction); NewGLMPeriodTable = new AGeneralLedgerMasterPeriodTable(); foreach (DataRow Row in GLMTable.Rows) { // for each of the general ledger master records of the current financial year set the // new, extended forwarding glm period records (most likely they will not exist yet // but if they do then update values) GLMRow = (AGeneralLedgerMasterRow)Row; GLMPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(GLMRow.GlmSequence, CurrentLastFwdPeriod, Transaction); if (GLMPeriodTable.Count > 0) { GLMPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodTable.Rows[0]; for (Period = CurrentLastFwdPeriod + 1; Period <= NewLastFwdPeriod; Period++) { if (AGeneralLedgerMasterPeriodAccess.Exists(GLMPeriodRow.GlmSequence, Period, Transaction)) { // if the record already exists then just change values TempGLMPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(GLMPeriodRow.GlmSequence, Period, Transaction); TempGLMPeriodRow = (AGeneralLedgerMasterPeriodRow)TempGLMPeriodTable.Rows[0]; TempGLMPeriodRow.ActualBase = GLMPeriodRow.ActualBase; TempGLMPeriodRow.ActualIntl = GLMPeriodRow.ActualIntl; if (!GLMPeriodRow.IsActualForeignNull()) { TempGLMPeriodRow.ActualForeign = GLMPeriodRow.ActualForeign; } else { TempGLMPeriodRow.SetActualForeignNull(); } NewGLMPeriodTable.Merge(TempGLMPeriodTable, true); } else { // add new row since it does not exist yet NewGLMPeriodRow = NewGLMPeriodTable.NewRowTyped(); NewGLMPeriodRow.GlmSequence = GLMPeriodRow.GlmSequence; NewGLMPeriodRow.PeriodNumber = Period; NewGLMPeriodRow.ActualBase = GLMPeriodRow.ActualBase; NewGLMPeriodRow.ActualIntl = GLMPeriodRow.ActualIntl; if (!GLMPeriodRow.IsActualForeignNull()) { NewGLMPeriodRow.ActualForeign = GLMPeriodRow.ActualForeign; } else { NewGLMPeriodRow.SetActualForeignNull(); } NewGLMPeriodTable.Rows.Add(NewGLMPeriodRow); } } // remove periods if the number of periods + forwarding periods has been reduced int NumberOfExistingPeriods = LedgerRow.NumberOfAccountingPeriods + LedgerRow.NumberFwdPostingPeriods; while ((NewNumberPeriods + NewNumberFwdPostingPeriods) < NumberOfExistingPeriods) { AGeneralLedgerMasterPeriodAccess.DeleteByPrimaryKey(GLMPeriodRow.GlmSequence, NumberOfExistingPeriods, Transaction); NumberOfExistingPeriods--; } } } // just one SubmitChanges for all records needed AGeneralLedgerMasterPeriodAccess.SubmitChanges(NewGLMPeriodTable, Transaction); } } // update a_ledger_init_flag records for: // suspense account flag: "SUSP-ACCT" // budget flag: "BUDGET" // base currency: "CURRENCY" // international currency: "INTL-CURRENCY" (this is a new flag for OpenPetra) // current period (start of ledger date): CURRENT-PERIOD // calendar settings: CAL AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_SUSP_ACC, LedgerRow.SuspenseAccountFlag, Transaction); AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_BUDGET, LedgerRow.BudgetControlFlag, Transaction); AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CURRENCY, !LedgerRow.IsBaseCurrencyNull(), Transaction); AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_INTL_CURRENCY, !LedgerRow.IsIntlCurrencyNull(), Transaction); AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CURRENT_PERIOD, !LedgerRow.IsCurrentPeriodNull(), Transaction); AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CAL, !LedgerRow.IsNumberOfAccountingPeriodsNull(), Transaction); GLSetupTDSAccess.SubmitChanges(InspectDS); SubmissionOK = true; }); } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } return TSubmitChangesResult.scrOK; }
public static bool ZeroForeignCurrencyBalances(Int32 ALedgerNumber, Int32 AYear, string[] AAccountCode) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } else if (AAccountCode.Length == 0) { return true; } #endregion Validate Arguments TDBTransaction Transaction = null; bool SubmissionOK = false; DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { foreach (string AccountCode in AAccountCode) { // "SELECT a_general_ledger_master.* " + // "FROM a_general_ledger_master, a_cost_centre " + // "WHERE a_general_ledger_master.a_ledger_number_i = " + ALedgerNumber + // " AND a_general_ledger_master.a_account_code_c = '" + AccountCode + "'" + // " AND a_general_ledger_master.a_year_i = " + AYear + // " AND a_cost_centre.a_cost_centre_code_c = a_general_ledger_master.a_cost_centre_code_c" + // " AND a_cost_centre.a_ledger_number_i = " + ALedgerNumber + // " AND a_cost_centre.a_posting_cost_centre_flag_l = true"; string Query = String.Format("SELECT {0}.* " + "FROM {0}, {1} " + "WHERE {0}.{2} = {3}" + " AND {0}.{4} = '{5}'" + " AND {0}.{6} = {7}" + " AND {1}.{8} = {0}.{9}" + " AND {1}.{10} = {3}" + " AND {1}.{11} = true", AGeneralLedgerMasterTable.GetTableDBName(), ACostCentreTable.GetTableDBName(), AGeneralLedgerMasterTable.GetLedgerNumberDBName(), ALedgerNumber, AGeneralLedgerMasterTable.GetAccountCodeDBName(), AccountCode, AGeneralLedgerMasterTable.GetYearDBName(), AYear, ACostCentreTable.GetCostCentreCodeDBName(), AGeneralLedgerMasterTable.GetCostCentreCodeDBName(), ACostCentreTable.GetLedgerNumberDBName(), ACostCentreTable.GetPostingCostCentreFlagDBName()); AGeneralLedgerMasterTable GeneralLedgerMasterTable = new AGeneralLedgerMasterTable(); DBAccess.GDBAccessObj.SelectDT(GeneralLedgerMasterTable, Query, Transaction); foreach (DataRow Row in GeneralLedgerMasterTable.Rows) { Row[AGeneralLedgerMasterTable.GetYtdActualForeignDBName()] = 0; Row[AGeneralLedgerMasterTable.GetStartBalanceForeignDBName()] = 0; AGeneralLedgerMasterPeriodTable GeneralLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadViaAGeneralLedgerMaster((int)Row[AGeneralLedgerMasterTable.GetGlmSequenceDBName() ], Transaction); foreach (AGeneralLedgerMasterPeriodRow GeneralLedgerMasterPeriodRow in GeneralLedgerMasterPeriodTable.Rows) { GeneralLedgerMasterPeriodRow.ActualForeign = 0; } AGeneralLedgerMasterPeriodAccess.SubmitChanges(GeneralLedgerMasterPeriodTable, Transaction); } AGeneralLedgerMasterAccess.SubmitChanges(GeneralLedgerMasterTable, Transaction); } SubmissionOK = true; }); return SubmissionOK; }
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber) { FBudgetTDS = new BudgetTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction); string sqlLoadBudgetForThisAndNextYear = string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)", ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); List <OdbcParameter> parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("thisyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear; parameters.Add(param); param = new OdbcParameter("nextyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1; parameters.Add(param); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction, parameters.ToArray()); string sqlLoadBudgetPeriodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", ABudgetPeriodTable.GetTableDBName(), ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetPeriodForThisAndNextYear, FBudgetTDS.ABudgetPeriod.TableName, Transaction, parameters.ToArray()); // Accept row changes here so that the Client gets 'unmodified' rows FBudgetTDS.AcceptChanges(); GLPostingDS = new GLPostingTDS(); AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction); // get the glm sequences for this year and next year for (int i = 0; i <= 1; i++) { int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i; AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.Year = Year; GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction)); } string sqlLoadGlmperiodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(GLPostingDS, sqlLoadGlmperiodForThisAndNextYear, GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray()); }); GLPostingDS.AcceptChanges(); return(true); }
public static GLSetupTDS LoadAccountHierarchies(Int32 ALedgerNumber) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } #endregion Validate Arguments GLSetupTDS MainDS = new GLSetupTDS(); // create template for AGeneralLedgerMaster TCacheable CachePopulator = new TCacheable(); System.Type TypeofTable = null; TDBTransaction Transaction = null; try { ALedgerTable ledger = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails, "", false, ALedgerNumber, out TypeofTable); #region Validate Data if ((ledger == null) || (ledger.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data int year = ledger[0].CurrentFinancialYear; string costCentreCode = "[" + ALedgerNumber + "]"; AGeneralLedgerMasterRow template = new AGeneralLedgerMasterTable().NewRowTyped(false); template.LedgerNumber = ALedgerNumber; template.Year = year; template.CostCentreCode = costCentreCode; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction); ACurrencyAccess.LoadAll(MainDS, Transaction); AAccountHierarchyAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AAccountHierarchyDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AAccountAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AAccountPropertyAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AAnalysisTypeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AAnalysisAttributeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AFreeformAnalysisAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AFeesReceivableAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AFeesPayableAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AGeneralLedgerMasterAccess.LoadUsingTemplate(MainDS, template, Transaction); ASuspenseAccountAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); }); // set Account BankAccountFlag if there exists a property foreach (AAccountPropertyRow accProp in MainDS.AAccountProperty.Rows) { if ((accProp.PropertyCode == MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT) && (accProp.PropertyValue == "true")) { MainDS.AAccount.DefaultView.RowFilter = String.Format("{0}='{1}'", AAccountTable.GetAccountCodeDBName(), accProp.AccountCode); GLSetupTDSAAccountRow acc = (GLSetupTDSAAccountRow)MainDS.AAccount.DefaultView[0].Row; acc.BankAccountFlag = true; MainDS.AAccount.DefaultView.RowFilter = ""; } } // set Account SuspenseAccountFlag if there exists a property foreach (ASuspenseAccountRow suspenseAccountRow in MainDS.ASuspenseAccount.Rows) { GLSetupTDSAAccountRow AccountRow = (GLSetupTDSAAccountRow)MainDS.AAccount.Rows.Find(new object[] { ALedgerNumber, suspenseAccountRow.SuspenseAccountCode }); AccountRow.SuspenseAccountFlag = true; } // Don't include any AnalysisType for which there are no values set MainDS.AFreeformAnalysis.DefaultView.Sort = AFreeformAnalysisTable.GetAnalysisTypeCodeDBName(); // "a_analysis_type_code_c"; foreach (AAnalysisTypeRow typeRow in MainDS.AAnalysisType.Rows) { Int32 Idx = MainDS.AFreeformAnalysis.DefaultView.Find(typeRow.AnalysisTypeCode); if (Idx < 0) { typeRow.Delete(); } } // add the YTD Actuals to each account foreach (AGeneralLedgerMasterRow generalLedgerMasterRow in MainDS.AGeneralLedgerMaster.Rows) { GLSetupTDSAAccountRow AccountRow = (GLSetupTDSAAccountRow)MainDS.AAccount.Rows.Find(new object[] { ALedgerNumber, generalLedgerMasterRow.AccountCode }); AccountRow.YtdActualBase = generalLedgerMasterRow.YtdActualBase; if (AccountRow.ForeignCurrencyFlag) { AccountRow.YtdActualForeign = generalLedgerMasterRow.YtdActualForeign; } } // Accept row changes here so that the Client gets 'unmodified' rows MainDS.AcceptChanges(); // Remove all Tables that were not filled with data before remoting them. MainDS.RemoveEmptyTables(); } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } return MainDS; }
/// <summary> /// export all GL Balances in the given year, towards the specified cost centres /// </summary> public static void ExportGLBalances(string AOutputPath, char ACSVSeparator, string ANewLine, Int32 ALedgerNumber, Int32 AFinancialYear, string ACostCentres, string AIgnoreAccounts) { string filename = Path.GetFullPath(Path.Combine(AOutputPath, "balance.csv")); Console.WriteLine("Writing file: " + filename); string sql = String.Format("SELECT GLM.{1} AS CostCentre, GLM.{0} AS Account, {2} AS StartBalance, GLMP.{3} AS EndBalance " + "FROM PUB_{4} AS GLM, PUB_{10} AS A, PUB_{13} AS GLMP " + "WHERE GLM.{5} = {6} AND {7} = {8} AND GLM.{1} IN ({9}) " + "AND GLMP.{14} = GLM.{14} AND GLMP.{15} = 12 " + "AND A.{5} = GLM.{5} AND A.{0} = GLM.{0} AND " + "A.{11}=true AND NOT GLM.{0} IN ({12})" + "ORDER BY GLM.{1}, GLM.{0}", AGeneralLedgerMasterTable.GetAccountCodeDBName(), AGeneralLedgerMasterTable.GetCostCentreCodeDBName(), AGeneralLedgerMasterTable.GetStartBalanceBaseDBName(), AGeneralLedgerMasterPeriodTable.GetActualBaseDBName(), AGeneralLedgerMasterTable.GetTableDBName(), AGeneralLedgerMasterTable.GetLedgerNumberDBName(), ALedgerNumber, AGeneralLedgerMasterTable.GetYearDBName(), AFinancialYear, "'" + ACostCentres.Replace(",", "','") + "'", AAccountTable.GetTableDBName(), AAccountTable.GetPostingStatusDBName(), "'" + AIgnoreAccounts.Replace(",", "','") + "'", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName(), AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName()); TDBTransaction Transaction = new TDBTransaction(); DataTable balances = null; DBAccess.ReadTransaction(ref Transaction, delegate { balances = Transaction.DataBaseObj.SelectDT(sql, "balances", Transaction); }); StringBuilder sb = new StringBuilder(); if (balances != null) { foreach (DataRow row in balances.Rows) { sb.Append(StringHelper.StrMerge( new string[] { row["CostCentre"].ToString(), row["Account"].ToString(), String.Format("{0:N}", Convert.ToDecimal(row["StartBalance"])), String.Format("{0:N}", Convert.ToDecimal(row["EndBalance"])) }, ACSVSeparator)); sb.Append(ANewLine); } } StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }
/// <summary> /// Run the revaluation and set the flag for the ledger /// Returns true if a Reval batch was posted. /// </summary> public Boolean RunRevaluation(out Int32 glBatchNumber) { glBatchNumber = -1; try { TLedgerInfo ledger = new TLedgerInfo(F_LedgerNum, FDataBase); F_BaseCurrency = ledger.BaseCurrency; F_BaseCurrencyDigits = new TCurrencyInfo(F_BaseCurrency).digits; F_RevaluationAccCode = ledger.RevaluationAccount; F_FinancialYear = ledger.CurrentFinancialYear; F_AccountingPeriod = ledger.CurrentPeriod; TDBTransaction Transaction = new TDBTransaction(); FDataBase = DBAccess.Connect("RunRevaluation", FDataBase); AGeneralLedgerMasterTable GlmTable = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow glmTemplate = (AGeneralLedgerMasterRow)GlmTable.NewRowTyped(false); Boolean transactionsWereCreated = false; glmTemplate.LedgerNumber = F_LedgerNum; glmTemplate.Year = F_FinancialYear; for (Int32 i = 0; i < F_ForeignAccount.Length; i++) { glmTemplate.AccountCode = F_ForeignAccount[i]; FDataBase.ReadTransaction( ref Transaction, delegate { GlmTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(glmTemplate, Transaction); }); if (GlmTable.Rows.Count > 0) { transactionsWereCreated |= RevaluateAccount(GlmTable, F_ExchangeRate[i], F_ForeignCurrency[i]); } } Boolean batchPostedOK = true; if (transactionsWereCreated) { batchPostedOK = CloseRevaluationAccountingBatch(out glBatchNumber); } if (batchPostedOK) { if (!transactionsWereCreated) // If no transactions were needed, I'll just advise the user: { FVerificationCollection.Add(new TVerificationResult( "Post Forex Batch", "Exchange rates are unchanged - no revaluation was required.", TResultSeverity.Resv_Status)); } TLedgerInitFlag flag = new TLedgerInitFlag(F_LedgerNum, "", FDataBase); for (Int32 i = 0; i < F_ForeignAccount.Length; i++) { flag.RemoveFlagComponent(MFinanceConstants.LEDGER_INIT_FLAG_REVAL, F_ForeignAccount[i]); } } else { FVerificationCollection.Add(new TVerificationResult( "Post Forex Batch", "The Revaluation Batch could not be posted.", TResultSeverity.Resv_Critical)); } return(batchPostedOK); } catch (EVerificationException terminate) { FVerificationCollection = terminate.ResultCollection(); return(false); } } // Run Revaluation