Exemplo n.º 1
0
 /// <summary>
 /// The constructor creates a base batch and defines the batch parameters. There is only
 /// one batch to account. Use a new object to post another batch.
 /// </summary>
 /// <param name="ALedgerNumber">the ledger number</param>
 /// <param name="ABatchDescription">a batch description text</param>
 /// <param name="ADataBase"></param>
 public TCommonAccountingTool(int ALedgerNumber,
                              string ABatchDescription, TDataBase ADataBase = null)
 {
     FLedgerInfo = new TLedgerInfo(ALedgerNumber);
     FDataBase   = ADataBase;
     TCommonAccountingTool_(ABatchDescription);
 }
Exemplo n.º 2
0
        public static bool PeriodMonthEnd(
            Int32 ALedgerNumber,
            bool AInfoMode,
            out TVerificationResultCollection AVerificationResults)
        {
            try
            {
                TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
                Int32 PeriodClosing = ledgerInfo.CurrentPeriod;
                bool res = new TMonthEnd(ledgerInfo).RunMonthEnd(AInfoMode,
                    out AVerificationResults);

                if (!res && !AInfoMode)
                {
                    TDBTransaction Transaction = null;
                    AAccountingPeriodTable PeriodTbl = null;

                    DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadUncommitted,
                        TEnforceIsolationLevel.eilMinimum,
                        ref Transaction,
                        delegate
                        {
                            PeriodTbl = AAccountingPeriodAccess.LoadByPrimaryKey(ledgerInfo.LedgerNumber, PeriodClosing, Transaction);
                        });

                    if (PeriodTbl.Rows.Count > 0)
                    {
                        AVerificationResults.Add(
                            new TVerificationResult(
                                Catalog.GetString("Month End"),
                                String.Format(Catalog.GetString("The period {0} - {1} has been closed."),
                                    PeriodTbl[0].PeriodStartDate.ToShortDateString(), PeriodTbl[0].PeriodEndDate.ToShortDateString()),
                                TResultSeverity.Resv_Status));
                    }
                }

                return res;
            }
            catch (Exception e)
            {
                TLogging.Log("TPeriodIntervallConnector.TPeriodMonthEnd() throws " + e.ToString());
                AVerificationResults = new TVerificationResultCollection();
                AVerificationResults.Add(
                    new TVerificationResult(
                        Catalog.GetString("Month End"),
                        Catalog.GetString("Uncaught Exception: ") + e.Message,
                        TResultSeverity.Resv_Critical));


                return true;
            }
        }
Exemplo n.º 3
0
        /// <summary>
        /// ...
        /// </summary>
        /// <param name="ALedgerInfo"></param>
        public TGetAccountHierarchyDetailInfo(TLedgerInfo ALedgerInfo)
        {
            FledgerInfo = ALedgerInfo;

            TDBTransaction Transaction = null;

            DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                TEnforceIsolationLevel.eilMinimum,
                ref Transaction,
                delegate
                {
                    FAccountTable = AAccountHierarchyDetailAccess.LoadViaALedger(
                        FledgerInfo.LedgerNumber, Transaction);
                });
        }
Exemplo n.º 4
0
        /// <summary>
        /// ...
        /// </summary>
        /// <param name="ALedgerInfo"></param>
        public TGetAccountHierarchyDetailInfo(TLedgerInfo ALedgerInfo)
        {
            FledgerInfo = ALedgerInfo;

            TDBTransaction Transaction = null;

            DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                                                                      TEnforceIsolationLevel.eilMinimum,
                                                                      ref Transaction,
                                                                      delegate
            {
                FAccountTable = AAccountHierarchyDetailAccess.LoadViaALedger(
                    FledgerInfo.LedgerNumber, Transaction);
            });
        }
Exemplo n.º 5
0
        /// <summary>
        /// The constructor needs a ledgerinfo (for the ledger number)
        /// </summary>
        /// <param name="ledgerInfo"></param>
        public THandleAccountPropertyInfo(TLedgerInfo ledgerInfo)
        {
            bool NewTransaction = false;

            try
            {
                TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
                                                                                               TEnforceIsolationLevel.eilMinimum,
                                                                                               out NewTransaction);
                propertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ledgerInfo.LedgerNumber, transaction);
            }
            finally
            {
                if (NewTransaction)
                {
                    DBAccess.GDBAccessObj.CommitTransaction();
                }
            }
        }
Exemplo n.º 6
0
        public static bool TPeriodYearEnd(
            int ALedgerNum,
            bool AIsInInfoMode,
            out TVerificationResultCollection AVerificationResult)
        {
            bool NewTransaction;

            DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);

            try
            {
                TLedgerInfo LedgerInfo = new TLedgerInfo(ALedgerNum);
                bool res = new TYearEnd(LedgerInfo).RunYearEnd(AIsInInfoMode, out AVerificationResult);

                if (!res)
                {
                    String SuccessMsg = AIsInInfoMode ? "YearEnd check: No problems found." : "Success.";
                    AVerificationResult.Add(new TVerificationResult("Year End", SuccessMsg, "Success", TResultSeverity.Resv_Status));
                }

                if (NewTransaction)
                {
                    DBAccess.GDBAccessObj.CommitTransaction();
                }

                return res;
            }
            catch (Exception e)
            {
                TLogging.Log("TPeriodIntervalConnector.TPeriodYearEnd() throws " + e.ToString());
                AVerificationResult = new TVerificationResultCollection();
                AVerificationResult.Add(
                    new TVerificationResult(
                        Catalog.GetString("Year End"),
                        Catalog.GetString("Uncaught Exception: ") + e.Message,
                        TResultSeverity.Resv_Critical));

                DBAccess.GDBAccessObj.RollbackTransaction();

                return false;
            }
        }
Exemplo n.º 7
0
        /// <summary>
        /// ...
        /// </summary>
        /// <param name="ALedgerInfo"></param>
        public TGetAccountHierarchyDetailInfo(TLedgerInfo ALedgerInfo)
        {
            FledgerInfo = ALedgerInfo;

            bool NewTransaction = false;

            TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);

            try
            {
                FAccountTable = AAccountHierarchyDetailAccess.LoadViaALedger(
                    FledgerInfo.LedgerNumber, transaction);
            }
            finally
            {
                if (NewTransaction)
                {
                    DBAccess.GDBAccessObj.RollbackTransaction();
                }
            }
        }
Exemplo n.º 8
0
 /// <summary>
 /// </summary>
 /// <param name="ALedgerInfo"></param>
 public TMonthEnd(TLedgerInfo ALedgerInfo)
 {
     FledgerInfo = ALedgerInfo;
 }
Exemplo n.º 9
0
 /// <summary>
 /// The Constructor defines a first value of a specific accounting code too.
 /// </summary>
 /// <param name="ALedgerInfo"></param>
 /// <param name="AAccountCode"></param>
 public TAccountInfo(TLedgerInfo ALedgerInfo, string AAccountCode)
 {
     FLedgerInfo = ALedgerInfo;
     LoadData();
     AccountCode = AAccountCode;
 }
Exemplo n.º 10
0
        public static DataTable IncomeExpenseTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
        {
            /* Required columns:
             *   CostCentreCode
             *   CostCentreName
             *   AccountType
             *   AccountLevel
             *   HasChildren
             *   Breakdown
             *   ParentFooter
             *   AccountPath
             *   AccountCode
             *   AccountName
             *   AccountIsSummary
             *   YearStart
             *   Actual
             *   ActualYTD
             *   LastYearActual
             *   LastYearActualYtd
             *   LastYearEnd
             *   Budget
             *   BudgetYTD
             *   LastYearBudget
             *   BudgetWholeYear
             */


            /*
             *  Cost Centre Breakdown process, in English:
             *
             *  Find all the transactions for this period (and last month, last year) in glmp, sorting by Account, CostCentre
             *  For each account, re-calculate the summary accounts, generating parent records and AccountPath, using the given hierarchy
             *  Summarise to the required detail level by copying into new "breakdown" records:
             *      Headers and footers at a lower level are just copied,
             *      Accounts at the highest level must be made into header/footer pairs. The totals should be correct.
             *      all transactions at the required detail level or higher must be combined by CostCentreCode and listed within the appropriate level account.
             *
             *  The initial query and calculation of previous periods and budget figures is all the same; only the summarisation is different.
             */

            /*
             *  "Whole year breakdown by period" process, in English:
             *
             *  Find all the transactions for the whole year (to period 12) in glmp, sorting by CostCentre, Account
             *  For each account, summarise into 12 fields of summary accounts, generating parent records and AccountPath, using the given hierarchy
             *  Summarise to the required level of detail
             *  For each remaining posting account, create a "breakdown" record with 12 fields for the summation
             *  Remove all records that are not a summary or a breakdown
             */

            Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
            Int32 NumberOfAccountingPeriods = new TLedgerInfo(LedgerNumber).NumberOfAccountingPeriods;
            Int32 AccountingYear = AParameters["param_year_i"].ToInt32();
            Int32 ReportPeriodStart = AParameters["param_start_period_i"].ToInt32();
            Int32 ReportPeriodEnd = AParameters["param_end_period_i"].ToInt32();
            String HierarchyName = AParameters["param_account_hierarchy_c"].ToString();

            Boolean International = AParameters["param_currency"].ToString().StartsWith("Int");
            Decimal EffectiveExchangeRate = 1;
            Decimal LastYearExchangeRate = 1;

            if (International)
            {
                TCorporateExchangeRateCache ExchangeRateCache = new TCorporateExchangeRateCache();

                EffectiveExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
                    LedgerNumber,
                    AccountingYear,
                    ReportPeriodEnd,
                    -1);
                LastYearExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
                    LedgerNumber,
                    AccountingYear - 1,
                    ReportPeriodEnd,
                    -1);
            }

            //
            // Read different DB fields according to currency setting
            Boolean CostCentreBreakdown = AParameters["param_cost_centre_breakdown"].ToBool();
            Boolean WholeYearPeriodsBreakdown = AParameters["param_period_breakdown"].ToBool();

            List <String>SelectedCostCentres = GetCostCentreList(AParameters);

            DataTable FilteredResults = new DataTable();
            FilteredResults.TableName = "IncomeExpense";

            TDBTransaction ReadTrans = null;
            DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
                IsolationLevel.ReadCommitted,
                TEnforceIsolationLevel.eilMinimum,
                ref ReadTrans,
                delegate
                {
                    String YearFilter =                                         // Get last year's values too.
                                        " AND glm.a_year_i>=" + (AccountingYear - 1) +
                                        " AND glm.a_year_i<=" + AccountingYear;

                    String isThisYear = " Year=" + AccountingYear;
                    String isLastYear = " Year=" + (AccountingYear - 1);

                    String PeriodFilter = " AND glmp.a_period_number_i<=" + NumberOfAccountingPeriods; // I need the whole year to see "whole year budget".
                    String isEndPeriod = "Period=" + ReportPeriodEnd;
                    String isPrevPeriod = "Period=" + (ReportPeriodStart - 1);

                    String ActualYtdQuery = "SUM (CASE WHEN " + isThisYear + " AND " + isEndPeriod + " THEN ActualYTD ELSE 0 END) AS ActualYtd, ";
                    String PrevPeriodQuery = (ReportPeriodStart == 1) ?
                                             "SUM (CASE WHEN " + isThisYear + " THEN StartBalance ELSE 0 END) AS LastMonthYtd, "
                                             : "SUM (CASE WHEN " + isThisYear + " AND " + isPrevPeriod +
                                             " THEN ActualYTD ELSE 0 END) AS LastMonthYtd, ";
                    String LastYearActualYtdQuery = "SUM (CASE WHEN " + isLastYear + " AND " + isEndPeriod +
                                                    " THEN ActualYTD ELSE 0 END) AS LastYearActualYtd, ";
                    String LastYearPrevPeriodQuery = (ReportPeriodStart == 1) ?
                                                     "SUM (CASE WHEN " + isLastYear + " THEN StartBalance ELSE 0 END) AS LastYearLastMonthYtd, "
                                                     : "SUM (CASE WHEN " + isLastYear + " AND " + isPrevPeriod +
                                                     " THEN ActualYTD ELSE 0 END) AS LastYearLastMonthYtd, ";

                    String LastYearEndQuery = "SUM (CASE WHEN " + isLastYear + " THEN EndBalance ELSE 0 END) AS LastYearEnd, ";
                    String BudgetQuery = "SUM (CASE WHEN " + isThisYear + " AND Period>=" + ReportPeriodStart + " AND Period <= " + ReportPeriodEnd +
                                         " THEN Budget ELSE 0 END) AS Budget, ";
                    String BudgetYtdQuery = "SUM (CASE WHEN " + isThisYear + " AND Period<=" + ReportPeriodEnd +
                                            " THEN Budget ELSE 0 END) AS BudgetYTD, ";
                    String BudgetWholeYearQuery = "SUM (CASE WHEN " + isThisYear + " THEN Budget ELSE 0 END) AS WholeYearBudget, ";
                    String BudgetLastYearQuery = "SUM (CASE WHEN " + isLastYear + " THEN Budget ELSE 0 END) AS LastYearBudget, ";
                    String MonthlyBreakdownQuery =
                        "0.0 AS P1, 0.0 AS P2, 0.0 AS P3, 0.0 AS P4, 0.0 AS P5, 0.0 AS P6 , 0.0 AS P7, 0.0 AS P8, 0.0 AS P9, 0.0 AS P10, 0.0 AS P11, 0.0 AS P12 ";

                    String NoZeroesFilter =
                        "WHERE (LastMonthYtd != 0 OR ActualYtd != 0 OR Budget != 0 OR BudgetYTD != 0 OR WholeYearBudget != 0 OR LastYearBudget != 0 OR LastYearLastMonthYtd != 0 OR LastYearActualYtd != 0)";

                    if (WholeYearPeriodsBreakdown)
                    {
                        //TODO: Calendar vs Financial Date Handling - Check if this should use financial num periods and not assume 12
                        CostCentreBreakdown = false;                            // Hopefully the client will have ensured this is false anyway - I'm just asserting it!
                        MonthlyBreakdownQuery =
                            "SUM (CASE WHEN Period=1 THEN ActualYTD ELSE 0 END) AS P1, " +
                            "SUM (CASE WHEN Period=2 THEN ActualYTD ELSE 0 END) AS P2, " +
                            "SUM (CASE WHEN Period=3 THEN ActualYTD ELSE 0 END) AS P3, " +
                            "SUM (CASE WHEN Period=4 THEN ActualYTD ELSE 0 END) AS P4, " +
                            "SUM (CASE WHEN Period=5 THEN ActualYTD ELSE 0 END) AS P5, " +
                            "SUM (CASE WHEN Period=6 THEN ActualYTD ELSE 0 END) AS P6, " +
                            "SUM (CASE WHEN Period=7 THEN ActualYTD ELSE 0 END) AS P7, " +
                            "SUM (CASE WHEN Period=8 THEN ActualYTD ELSE 0 END) AS P8, " +
                            "SUM (CASE WHEN Period=9 THEN ActualYTD ELSE 0 END) AS P9, " +
                            "SUM (CASE WHEN Period=10 THEN ActualYTD ELSE 0 END) AS P10, " +
                            "SUM (CASE WHEN Period=11 THEN ActualYTD ELSE 0 END) AS P11, " +
                            "SUM (CASE WHEN Period=12 THEN ActualYTD ELSE 0 END) AS P12 "; // No comma because this is the last field!

                        ActualYtdQuery = "0 AS ActualYtd, ";
                        PrevPeriodQuery = "0.0 AS LastMonthYtd, ";
                        LastYearActualYtdQuery = "0 AS LastYearActualYtd, ";
                        LastYearPrevPeriodQuery = "0.0 AS LastYearLastMonthYtd, ";
                        LastYearEndQuery = "0.0 AS LastYearEnd, ";
                        BudgetQuery = "0.0 AS Budget,";
                        BudgetYtdQuery = "0.0 AS BudgetYTD,";
                        BudgetWholeYearQuery = "0.0 AS WholeYearBudget, ";
                        BudgetLastYearQuery = "0.0 AS LastYearBudget, ";

                        YearFilter = " AND glm.a_year_i=" + AccountingYear;
                        PeriodFilter = " AND glmp.a_period_number_i<=12";
                        NoZeroesFilter = "WHERE (P1<>0 OR P2<>0 OR P3<>0 OR P4<>0 OR P5<>0 OR P6<>0 " +
                                         "OR P7<>0 OR P8<>0 OR P9<>0 OR P10<>0 OR P11<>0 OR P12<>0) "; // No blank rows
                    }

                    TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);

                    //
                    // I can't use summary rows in GLM. Each Summary Cost Centre must be expressed as the sum of all the posting Cost Centres it represents.
                    // Accordingly, the query below is called for each Cost Centre, and the results appended into one table.

                    foreach (String ParentCC in SelectedCostCentres)
                    {
                        String[] Parts = ParentCC.Split(',');
                        String CostCentreFilter = GetReportingCostCentres(LedgerNumber, Parts[0], "");
                        CostCentreFilter = CostCentreFilter.Replace(",",
                            "','");                                                                          // SQL IN List items in single quotes
                        CostCentreFilter = " AND glm.a_cost_centre_code_c in ('" + CostCentreFilter + "') ";


                        String AllGlmp =                                                                     // This query fetches all the data I need from GLM and GLMP
                                         "(SELECT a_account.a_account_code_c AS AccountCode, a_account.a_account_type_c AS AccountType, " +
                                         "a_account.a_account_code_short_desc_c AS AccountName, " +
                                         "CASE a_account.a_account_type_c WHEN 'Income' THEN 1 WHEN 'Expense' THEN 2 END AS AccountTypeOrder, " +
                                         "a_account.a_debit_credit_indicator_l AS DebitCredit, " +
                                         "glm.a_year_i AS Year, " +
                                         "glm.a_start_balance_base_n AS StartBalance, " +
                                         "glm.a_closing_period_actual_base_n AS EndBalance, " +
                                         "glmp.a_period_number_i AS Period, " +
                                         "glmp.a_actual_base_n AS ActualYTD, " +
                                         "glmp.a_budget_base_n AS Budget " +
                                         "FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account " +
                                         "WHERE " +
                                         "glm.a_ledger_number_i=" + LedgerNumber + " " +
                                         YearFilter +
                                         PeriodFilter +
                                         " AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
                                         " AND a_account.a_account_code_c = glm.a_account_code_c" +
                                         " AND (a_account.a_account_type_c = 'Income' OR a_account.a_account_type_c = 'Expense') AND a_account.a_ledger_number_i = glm.a_ledger_number_i "
                                         +
                                         "AND a_account.a_posting_status_l = true " +
                                         CostCentreFilter +
                                         "AND (glmp.a_actual_base_n != 0 OR glmp.a_budget_base_n != 0) " +
                                         ") AS AllGlmp ";


                        String Summarised =                                                                  // This query reduces the result set from AllGlmp
                                            "(SELECT " +
                                            "AccountCode, AccountType, AccountName, DebitCredit, " +
                                            "SUM (CASE WHEN " + isThisYear + " THEN StartBalance ELSE 0 END) AS YearStart, " +
                                            PrevPeriodQuery +
                                            ActualYtdQuery +
                                            LastYearActualYtdQuery +
                                            LastYearPrevPeriodQuery +
                                            LastYearEndQuery +
                                            BudgetQuery +
                                            BudgetYtdQuery +
                                            BudgetWholeYearQuery +
                                            BudgetLastYearQuery +
                                            "AccountTypeOrder, " +
                                            MonthlyBreakdownQuery +
                                            "FROM " +
                                            AllGlmp +

                                            "GROUP BY AccountType, AccountCode, AccountName, DebitCredit, AccountTypeOrder " +
                                            ") AS Summarised ";


                        String Query = "SELECT " +                                                          // This query adds extra columns to Summarised

                                       " '" + Parts[0].Replace("'", "''") + "' AS CostCentreCode," +
                                       " '" + Parts[1].Replace("'",
                            "''") + "' AS CostCentreName," +

                                       "Summarised.*, " +
                                       "ActualYtd - LastMonthYtd AS Actual, " +
                                       "LastYearActualYtd - LastYearLastMonthYtd AS LastYearActual, " +
                                       "1 AS AccountLevel, false AS HasChildren, false AS ParentFooter, false AS AccountIsSummary, false AS Breakdown,'Path' AS AccountPath "
                                       +

                                       "FROM " +
                                       Summarised +
                                       NoZeroesFilter +
                                       "ORDER BY AccountTypeOrder, AccountCode ";

                        FilteredResults.Merge(DbAdapter.RunQuery(Query, "IncomeExpense", ReadTrans));

                        if (DbAdapter.IsCancelled)
                        {
                            return;
                        }
                    } // foreach ParentCC

                    if (CostCentreBreakdown) // I need to re-order the resulting table by Account:
                    {
                        FilteredResults.DefaultView.Sort = "AccountCode";
                        FilteredResults = FilteredResults.DefaultView.ToTable();
                    }

                    //
                    // I only have "posting accounts" - I need to add the summary accounts.
                    TLogging.Log(Catalog.GetString("Summarise to parent accounts.."), TLoggingType.ToStatusBar);
                    AAccountHierarchyDetailTable HierarchyTbl = AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(LedgerNumber,
                        HierarchyName,
                        ReadTrans);

                    HierarchyTbl.DefaultView.Sort = "a_reporting_account_code_c";   // These two sort orders

                    if (CostCentreBreakdown)                                        // Are required by AddTotalsToParentAccountRow, below.
                    {
                        FilteredResults.DefaultView.Sort = "AccountCode";
                    }
                    else
                    {
                        FilteredResults.DefaultView.Sort = "CostCentreCode, AccountCode";
                    }

                    Int32 PostingAccountRecords = FilteredResults.Rows.Count;

                    for (Int32 Idx = 0; Idx < PostingAccountRecords; Idx++)
                    {
                        if (DbAdapter.IsCancelled)
                        {
                            return;
                        }

                        DataRow Row = FilteredResults.Rows[Idx];

                        if (WholeYearPeriodsBreakdown) // The query gave me YTD values; I need monthly actuals.
                        {
                            for (Int32 i = NumberOfAccountingPeriods; i > 1; i--)
                            {
                                Row["P" + i] = Convert.ToDecimal(Row["P" + i]) - Convert.ToDecimal(Row["P" + (i - 1)]);
                            }

                            Row["P1"] = Convert.ToDecimal(Row["P1"]) - Convert.ToDecimal(Row["YearStart"]);
                        }

                        String CostCentreParam = (CostCentreBreakdown) ? "" : Row["CostCentreCode"].ToString();
                        String ParentAccountPath;
                        Int32 ParentAccountTypeOrder;

                        Int32 AccountLevel = AddTotalsToParentAccountRow(
                            FilteredResults,
                            HierarchyTbl,
                            LedgerNumber,
                            CostCentreParam,
                            Row["AccountCode"].ToString(),
                            Row,
                            CostCentreBreakdown,
                            (WholeYearPeriodsBreakdown) ? NumberOfAccountingPeriods : 0,
                            out ParentAccountPath,
                            out ParentAccountTypeOrder,
                            ReadTrans);
                        Row["AccountLevel"] = AccountLevel;
                        Row["AccountPath"] = ParentAccountPath + "~" + Row["AccountCode"];
                    }

                    //
                    // Now if I re-order the result, and hide any rows that are empty or too detailed, it should be what I need!

                    Int32 DetailLevel = AParameters["param_nesting_depth"].ToInt32();

                    if (CostCentreBreakdown)
                    {
                        TLogging.Log(Catalog.GetString("Get Cost Centre Breakdown.."), TLoggingType.ToStatusBar);

                        // I'm creating additional "breakdown" records for the per-CostCentre breakdown, and potentially removing
                        // some records that were summed into those "breakdown" records.
                        FilteredResults.DefaultView.Sort = "AccountType DESC, AccountPath ASC, CostCentreCode";
                        FilteredResults.DefaultView.RowFilter = "Breakdown=false";

                        // At this point I need to add together any transactions in more detailed levels, summarising them by Cost Centre,
                        // and listing them under the account to which they relate:
                        DataView SummaryView = new DataView(FilteredResults);
                        SummaryView.Sort = "AccountTypeOrder, AccountPath ASC, CostCentreCode";
                        SummaryView.RowFilter = "Breakdown=true";

                        DataRow AccumulatingRow = FilteredResults.NewRow(); // This temporary row is not part of the result set - it's just a line of temporary vars.

                        for (Int32 RowIdx = 0; RowIdx < FilteredResults.DefaultView.Count; RowIdx++)
                        {
                            if (DbAdapter.IsCancelled)
                            {
                                return;
                            }

                            DataRow DetailRow = FilteredResults.DefaultView[RowIdx].Row;
                            AddToCostCentreBreakdownSummary(SummaryView, DetailLevel, DetailRow);

                            //
                            // For posting accounts in "details" view, the cost centre breakdown rows will be presented after one or more rows with the same account.
                            // The last account row will become a header, below, and any other rows with the same account will be removed.
                            // So I need the values in those rows to accumulate into the last row.

                            AccumulateTotalsPerCostCentre(DetailRow, AccumulatingRow);
                        }

                        FilteredResults.DefaultView.Sort = "AccountTypeOrder, AccountPath ASC, Breakdown, CostCentreCode";
                    } // if (CostCentreBreakdown)
                    else
                    {
                        FilteredResults.DefaultView.Sort = "CostCentreCode, AccountTypeOrder, AccountPath ASC";
                    }

                    FilteredResults.DefaultView.RowFilter = "AccountLevel<=" + DetailLevel.ToString(); // Nothing too detailed
                    FilteredResults = FilteredResults.DefaultView.ToTable("IncomeExpense");

                    //
                    // Finally, to make the hierarchical report possible,
                    // I want to include a note to show whether a row has child rows,
                    // and if it does, I'll copy this row to a new row, below the children, marking the new row as "footer".

                    TLogging.Log(Catalog.GetString("Format data for reporting.."), TLoggingType.ToStatusBar);

                    for (Int32 RowIdx = 0; RowIdx < FilteredResults.Rows.Count - 1; RowIdx++)
                    {
                        if (DbAdapter.IsCancelled)
                        {
                            return;
                        }

                        Int32 ParentAccountLevel = Convert.ToInt32(FilteredResults.Rows[RowIdx]["AccountLevel"]);
                        Boolean HasChildren = (Convert.ToInt32(FilteredResults.Rows[RowIdx + 1]["AccountLevel"]) > ParentAccountLevel)
                                              || (Convert.ToBoolean(FilteredResults.Rows[RowIdx]["Breakdown"]) == false
                                                  && Convert.ToBoolean(FilteredResults.Rows[RowIdx + 1]["Breakdown"]) == true);
                        FilteredResults.Rows[RowIdx]["HasChildren"] = HasChildren;

                        if (HasChildren)
                        {
                            if (CostCentreBreakdown)
                            {
                                //
                                // Header and footer rows do not have Cost Centres -
                                // The Cost Centre fields were used for sorting, but they're misleading so I'll remove them here:
                                FilteredResults.Rows[RowIdx]["CostCentreCode"] = "";
                                FilteredResults.Rows[RowIdx]["CostCentreName"] = "";
                            }

                            Int32 NextSiblingPos = -1;

                            for (Int32 ChildIdx = RowIdx + 2; ChildIdx < FilteredResults.Rows.Count; ChildIdx++)
                            {
                                if ((Convert.ToInt32(FilteredResults.Rows[ChildIdx]["AccountLevel"]) <= ParentAccountLevel)
                                    && (Convert.ToBoolean(FilteredResults.Rows[ChildIdx]["Breakdown"]) == false)) // This row is not a child of mine
                                {                                                                                 // so I insert my footer before here.
                                    NextSiblingPos = ChildIdx;
                                    break;
                                }
                            }

                            DataRow FooterRow = FilteredResults.NewRow();
                            DataUtilities.CopyAllColumnValues(FilteredResults.Rows[RowIdx], FooterRow);
                            FooterRow["ParentFooter"] = true;
                            FooterRow["HasChildren"] = false;

                            if (NextSiblingPos > 0)
                            {
                                FilteredResults.Rows.InsertAt(FooterRow, NextSiblingPos);
                            }
                            else
                            {
                                FilteredResults.Rows.Add(FooterRow);
                            }
                        }
                    }

                    // For "Cost Centre Breakdown", the only transactions I want to see are the "breakdown" rows I've added.
                    // Everything else is removed unless it's a header or footer:

                    if (CostCentreBreakdown)
                    {
                        FilteredResults.DefaultView.RowFilter = "Breakdown=true OR HasChildren=true OR ParentFooter=true";
                        FilteredResults = FilteredResults.DefaultView.ToTable("IncomeExpense");
                    }

                    if (EffectiveExchangeRate != 1)
                    {
                        if (WholeYearPeriodsBreakdown)
                        {
                            foreach (DataRow Row in FilteredResults.Rows)
                            {
                                Row["p1"] = Convert.ToDecimal(Row["p1"]) * EffectiveExchangeRate;
                                Row["p2"] = Convert.ToDecimal(Row["p2"]) * EffectiveExchangeRate;
                                Row["p3"] = Convert.ToDecimal(Row["p3"]) * EffectiveExchangeRate;
                                Row["p4"] = Convert.ToDecimal(Row["p4"]) * EffectiveExchangeRate;
                                Row["p5"] = Convert.ToDecimal(Row["p5"]) * EffectiveExchangeRate;
                                Row["p6"] = Convert.ToDecimal(Row["p6"]) * EffectiveExchangeRate;
                                Row["p7"] = Convert.ToDecimal(Row["p7"]) * EffectiveExchangeRate;
                                Row["p8"] = Convert.ToDecimal(Row["p8"]) * EffectiveExchangeRate;
                                Row["p9"] = Convert.ToDecimal(Row["p9"]) * EffectiveExchangeRate;
                                Row["p10"] = Convert.ToDecimal(Row["p10"]) * EffectiveExchangeRate;
                                Row["p11"] = Convert.ToDecimal(Row["p11"]) * EffectiveExchangeRate;
                                Row["p12"] = Convert.ToDecimal(Row["p12"]) * EffectiveExchangeRate;
                            }
                        }
                        else
                        {
                            foreach (DataRow Row in FilteredResults.Rows)
                            {
                                Row["yearstart"] = Convert.ToDecimal(Row["yearstart"]) * EffectiveExchangeRate;
                                Row["lastmonthytd"] = Convert.ToDecimal(Row["lastmonthytd"]) * EffectiveExchangeRate;
                                Row["actualytd"] = Convert.ToDecimal(Row["actualytd"]) * EffectiveExchangeRate;
                                Row["LastYearlastmonthytd"] = Convert.ToDecimal(Row["LastYearlastmonthytd"]) * EffectiveExchangeRate;
                                Row["LastYearactualytd"] = Convert.ToDecimal(Row["LastYearactualytd"]) * EffectiveExchangeRate;
                                Row["LastYearEnd"] = Convert.ToDecimal(Row["LastYearEnd"]) * LastYearExchangeRate;
                                Row["budget"] = Convert.ToDecimal(Row["budget"]) * EffectiveExchangeRate;
                                Row["budgetytd"] = Convert.ToDecimal(Row["budgetytd"]) * EffectiveExchangeRate;
                                Row["wholeyearbudget"] = Convert.ToDecimal(Row["wholeyearbudget"]) * EffectiveExchangeRate;
                                Row["LastYearBudget"] = Convert.ToDecimal(Row["LastYearBudget"]) * LastYearExchangeRate;
                                Row["actual"] = Convert.ToDecimal(Row["actual"]) * EffectiveExchangeRate;
                                Row["LastYearactual"] = Convert.ToDecimal(Row["LastYearactual"]) * EffectiveExchangeRate;
                            }
                        }
                    }

                    TLogging.Log("", TLoggingType.ToStatusBar);
                }); // Get NewOrExisting AutoReadTransaction

            return FilteredResults;
        } // Income Expense Table
Exemplo n.º 11
0
 /// <summary>
 /// A TLedgerInfo-Oject is used internally.
 /// If you already have one, use this constructor to reduce the number of database requests.
 /// </summary>
 /// <param name="ALedgerInfo">The ledger-info object</param>
 /// <param name="ABatchDescription">the description text ...</param>
 public TCommonAccountingTool(TLedgerInfo ALedgerInfo, string ABatchDescription)
 {
     FLedgerInfo = ALedgerInfo;
     TCommonAccountingTool_(ABatchDescription);
 }
Exemplo n.º 12
0
 /// <summary></summary>
 /// <param name="ALedgerInfo"></param>
 public TArchive(TLedgerInfo ALedgerInfo)
 {
     FledgerInfo = ALedgerInfo;
 }
Exemplo n.º 13
0
        public void Test_07_ProcessStatus()
        {
            TLedgerInfo ledgerInfo = new TLedgerInfo(LedgerNumber);

            ledgerInfo.YearEndProcessStatus = (int)TYearEndProcessStatus.ACCOUNT_CLOSED_OUT;
            Assert.AreEqual((int)TYearEndProcessStatus.ACCOUNT_CLOSED_OUT, ledgerInfo.YearEndProcessStatus,
                "OK");
            ledgerInfo.YearEndProcessStatus = (int)TYearEndProcessStatus.GIFT_CLOSED_OUT;
            Assert.AreEqual((int)TYearEndProcessStatus.GIFT_CLOSED_OUT, ledgerInfo.YearEndProcessStatus,
                "OK");
        }
Exemplo n.º 14
0
        /// <summary>
        /// Run the revaluation and set the flag for the ledger
        /// </summary>
        public Boolean RunRevaluation()
        {
            try
            {
                TLedgerInfo ledger = new TLedgerInfo(F_LedgerNum);
                F_BaseCurrency = ledger.BaseCurrency;
                F_BaseCurrencyDigits = new TCurrencyInfo(F_BaseCurrency).digits;
                F_RevaluationAccCode = ledger.RevaluationAccount;
                F_FinancialYear = ledger.CurrentFinancialYear;
                F_AccountingPeriod = ledger.CurrentPeriod;

                if (!RunRevaluationIntern())
                {
                    return false;
                }

                if (F_resultSeverity != TResultSeverity.Resv_Critical)
                {
                    new TLedgerInitFlagHandler(F_LedgerNum,
                        TLedgerInitFlagEnum.Revaluation).Flag = true; // Set the REVALUATION flag on the Ledger. THIS FLAG IS NOW IGNORED.
                }
            }
            catch (EVerificationException terminate)
            {
                FVerificationCollection = terminate.ResultCollection();
            }
            return F_resultSeverity == TResultSeverity.Resv_Critical;
        }
Exemplo n.º 15
0
 /// <summary>
 /// This mininmal constructor defines the result collection for the error messages and
 /// Ledger Info to select the ledger ...
 /// </summary>
 /// <param name="ALedgerInfo"></param>
 public TAccountInfo(TLedgerInfo ALedgerInfo)
 {
     FLedgerInfo = ALedgerInfo;
     LoadData();
 }
Exemplo n.º 16
0
 /// <summary>
 /// The Constructor defines a first value of a specific accounting code too.
 /// </summary>
 /// <param name="ALedgerInfo"></param>
 /// <param name="AAccountCode"></param>
 public TAccountInfo(TLedgerInfo ALedgerInfo, string AAccountCode)
 {
     FLedgerInfo = ALedgerInfo;
     LoadData();
     AccountCode = AAccountCode;
 }
Exemplo n.º 17
0
        /// <summary>
        /// get the default bank account for this ledger
        /// </summary>
        /// <param name="ALedgerNumber"></param>
        public static string GetDefaultBankAccount(int ALedgerNumber)
        {
            string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
                SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());

            if (BankAccountCode.Length == 0)
            {
                TDBTransaction Transaction = null;

                DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                    TEnforceIsolationLevel.eilMinimum,
                    ref Transaction,
                    delegate
                    {
                        // use the first bank account
                        AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction);
                        accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
                                                                  MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
                                                                  AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";

                        if (accountProperties.DefaultView.Count > 0)
                        {
                            BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
                        }
                        else
                        {
                            string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
                                              "FROM a_gift_batch " +
                                              "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
                                              " AND a_gift_batch.a_batch_number_i = (" +
                                              "SELECT max(a_gift_batch.a_batch_number_i) " +
                                              "FROM a_gift_batch " +
                                              "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
                                              " AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";

                            DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", Transaction);

                            // use the Bank Account of the previous Gift Batch
                            if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
                            {
                                BankAccountCode = LatestAccountCode.Rows[0]["a_bank_account_code_c"].ToString();
                            }
                            // if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
                            else
                            {
                                AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, Transaction);

                                DataView dv = AccountTable.DefaultView;
                                dv.Sort = "a_account_code_c asc";
                                dv.RowFilter = "a_account_active_flag_l = true AND a_posting_status_l = true";
                                DataTable sortedDT = dv.ToTable();

                                TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
                                TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
                                List <string>children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);

                                foreach (DataRow account in sortedDT.Rows)
                                {
                                    // check if this account reports to the CASH account
                                    if (children.Contains(account["a_account_code_c"].ToString()))
                                    {
                                        BankAccountCode = account["a_account_code_c"].ToString();
                                        break;
                                    }
                                }
                            }
                        }
                    });
            }

            return BankAccountCode;
        }
Exemplo n.º 18
0
        /// <summary>
        /// The constructor needs a ledgerinfo (for the ledger number)
        /// </summary>
        /// <param name="ledgerInfo"></param>
        public THandleAccountPropertyInfo(TLedgerInfo ledgerInfo)
        {
            bool NewTransaction = false;

            try
            {
                TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
                    TEnforceIsolationLevel.eilMinimum,
                    out NewTransaction);
                propertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ledgerInfo.LedgerNumber, transaction);
            }
            finally
            {
                if (NewTransaction)
                {
                    DBAccess.GDBAccessObj.CommitTransaction();
                }
            }
        }
Exemplo n.º 19
0
 /// <summary>
 /// </summary>
 public TResetForwardPeriodBatches(TLedgerInfo ALedgerInfo, Int32 AOldYearNum)
 {
     FLedgerInfo = ALedgerInfo;
     FOldYearNum = AOldYearNum;
 }
Exemplo n.º 20
0
        /// <summary>
        /// get the default bank account for this ledger
        /// </summary>
        /// <param name="ALedgerNumber"></param>
        public static string GetDefaultBankAccount(int 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

            string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
                SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());

            if (BankAccountCode.Length == 0)
            {
                TDBTransaction readTransaction = null;

                try
                {
                    DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                                                                              TEnforceIsolationLevel.eilMinimum, ref readTransaction,
                                                                              delegate
                    {
                        // use the first bank account
                        AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction);

                        accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
                                                                  MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
                                                                  AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";

                        if (accountProperties.DefaultView.Count > 0)
                        {
                            BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
                        }
                        else
                        {
                            string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
                                              "FROM a_gift_batch " +
                                              "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
                                              " AND a_gift_batch.a_batch_number_i = (" +
                                              "SELECT max(a_gift_batch.a_batch_number_i) " +
                                              "FROM a_gift_batch " +
                                              "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
                                              " AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";

                            DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", readTransaction);

                            // use the Bank Account of the previous Gift Batch
                            if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
                            {
                                BankAccountCode = LatestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString();     //"a_bank_account_code_c"
                            }
                            // if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
                            else
                            {
                                AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction);

                                #region Validate Data

                                if ((AccountTable == null) || (AccountTable.Count == 0))
                                {
                                    throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
                                                                                                               "Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
                                                                                                           Utilities.GetMethodName(true),
                                                                                                           ALedgerNumber));
                                }

                                #endregion Validate Data

                                DataView dv  = AccountTable.DefaultView;
                                dv.Sort      = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
                                dv.RowFilter = String.Format("{0} = true AND {1} = true",
                                                             AAccountTable.GetAccountActiveFlagDBName(),
                                                             AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
                                DataTable sortedDT = dv.ToTable();

                                TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
                                TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
                                List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);

                                foreach (DataRow account in sortedDT.Rows)
                                {
                                    // check if this account reports to the CASH account
                                    if (children.Contains(account["a_account_code_c"].ToString()))
                                    {
                                        BankAccountCode = account["a_account_code_c"].ToString();
                                        break;
                                    }
                                }
                            }
                        }
                    });
                }
                catch (Exception ex)
                {
                    TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
                                               Utilities.GetMethodSignature(),
                                               Environment.NewLine,
                                               ex.Message));
                    throw ex;
                }
            }

            return(BankAccountCode);
        }
Exemplo n.º 21
0
        public static DataTable BalanceSheetTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
        {
            DataTable FilteredResults = null;

            /* Required columns:
             * Actual
             * LastYearActual
             * LastYearEnd
             */

            Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
            Int32 NumberOfAccountingPeriods = new TLedgerInfo(LedgerNumber).NumberOfAccountingPeriods;
            Int32 AccountingYear = AParameters["param_year_i"].ToInt32();
            Int32 ReportPeriodEnd = AParameters["param_end_period_i"].ToInt32();
            String HierarchyName = AParameters["param_account_hierarchy_c"].ToString();
            String RootCostCentre = AParameters["param_cost_centre_code"].ToString();

            Boolean International = AParameters["param_currency"].ToString().StartsWith("Int");
            Decimal EffectiveExchangeRate = 1;
            Decimal LastYearExchangeRate = 1;

            if (International)
            {
                TCorporateExchangeRateCache ExchangeRateCache = new TCorporateExchangeRateCache();

                EffectiveExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
                    LedgerNumber,
                    AccountingYear,
                    ReportPeriodEnd,
                    -1);

                LastYearExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
                    LedgerNumber,
                    AccountingYear - 1,
                    ReportPeriodEnd,
                    -1);
            }

            TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
            DataTable resultTable = null;
            TDBTransaction ReadTrans = null;
            DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                TEnforceIsolationLevel.eilMinimum,
                ref ReadTrans,
                delegate
                {
                    ACostCentreTable AllCostCentres = ACostCentreAccess.LoadViaALedger(LedgerNumber, ReadTrans);
                    AllCostCentres.DefaultView.Sort = ACostCentreTable.GetCostCentreToReportToDBName();
                    List <string>ReportingCostCentres = new List <string>();
                    GetReportingCostCentres(AllCostCentres, ReportingCostCentres, RootCostCentre);
                    String CostCentreList = StringHelper.StrMerge(ReportingCostCentres.ToArray(), ',');
                    CostCentreList = "'" + CostCentreList.Replace(",", "','") + "'";

                    String Summarised =                                                                  // This query gets the totals I need
                                        "(SELECT " +
                                        " glm.a_year_i AS Year," +
                                        " glmp.a_period_number_i AS Period," +
                                        " a_account.a_account_type_c AS AccountType," +
                                        " a_account.a_debit_credit_indicator_l AS DebitCredit," +
                                        " glm.a_account_code_c AS AccountCode," +
                                        " a_account.a_account_code_short_desc_c AS AccountName," +
                                        " sum(glmp.a_actual_base_n) AS Actual" +

                                        " FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account" +
                                        " WHERE glm.a_ledger_number_i=" + LedgerNumber +
                                        " AND glm.a_cost_centre_code_c in (" + CostCentreList + ") " +
                                        " AND glm.a_year_i IN (" + (AccountingYear - 1) + ", " + AccountingYear + ")" +
                                        " AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
                                        " AND glmp.a_period_number_i=" + ReportPeriodEnd +
                                        " AND a_account.a_account_code_c = glm.a_account_code_c" +
                                        " AND a_account.a_ledger_number_i = glm.a_ledger_number_i" +
                                        " AND a_account.a_posting_status_l = true" +
                                        " GROUP BY glm.a_year_i, glmp.a_period_number_i, a_account.a_account_type_c," +
                                        "   a_account.a_debit_credit_indicator_l, glm.a_account_code_c," +
                                        "   a_account.a_account_code_short_desc_c" +
                                        " ORDER BY glm.a_account_code_c) AS summarised"
                    ;

                    String Query =
                        "SELECT " +
                        "summarised.*," +
                        " 1 AS AccountLevel," +
                        " false AS HasChildren," +
                        " false AS ParentFooter," +
                        " 0 AS AccountTypeOrder," +
                        " false AS AccountIsSummary," +
                        " 'Path' AS AccountPath," +
                        " 0.0 AS ActualYTD," +
                        " 0.0 AS LastYearActual" +
                        " FROM " + Summarised;

                    resultTable = DbAdapter.RunQuery(Query, "BalanceSheet", ReadTrans);

                    DataView LastYear = new DataView(resultTable);
                    LastYear.Sort = "AccountCode";
                    LastYear.RowFilter = String.Format("Year={0}", AccountingYear - 1);

                    DataView ThisYear = new DataView(resultTable);
                    ThisYear.RowFilter = String.Format("Year={0}", AccountingYear);

                    //
                    // Some of these rows are from a year ago. I'll copy those into the current period "LastYear" fields.

                    TLogging.Log(Catalog.GetString("Get last year data.."), TLoggingType.ToStatusBar);

                    foreach (DataRowView rv in ThisYear)
                    {
                        if (DbAdapter.IsCancelled)
                        {
                            return;
                        }

                        DataRow Row = rv.Row;
                        Int32 RowIdx = LastYear.Find(
                            new Object[] {
                                Row["AccountCode"]
                            }
                            );

                        if (RowIdx >= 0)
                        {
                            DataRow LastYearRow = LastYear[RowIdx].Row;
                            Row["LastYearActual"] = LastYearExchangeRate * Convert.ToDecimal(LastYearRow["Actual"]);
                            LastYearRow.Delete();
                        }

                        Row["Actual"] = EffectiveExchangeRate * Convert.ToDecimal(Row["Actual"]);
                    }

                    //
                    // At the end of that process, there may be some remaining LastYearRows that don't have an equivalent entry in this year
                    // (because there's been no activity this year.)
                    // I'll tweak these so the Row appears as this year, but the amount appears as LastYear:

                    foreach (DataRowView rv in LastYear)
                    {
                        DataRow Row = rv.Row;
                        Row["Year"] = Convert.ToInt32(Row["Year"]) + 1;
                        Row["LastYearActual"] = LastYearExchangeRate * Convert.ToDecimal(Row["Actual"]);
                        Row["Actual"] = 0.0;
                    }

                    //
                    // So now I don't have to look at last year's rows:

                    FilteredResults = ThisYear.ToTable("BalanceSheet");

                    //
                    // I only have "posting accounts" - I need to add the summary accounts.

                    AAccountHierarchyDetailTable HierarchyTbl = AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(LedgerNumber,
                        HierarchyName,
                        ReadTrans);

                    HierarchyTbl.DefaultView.Sort = "a_reporting_account_code_c";  // These two sort orders
                    FilteredResults.DefaultView.Sort = "AccountCode";              // Are required by AddTotalsToParentAccountRow, below.

                    Int32 PostingAccountRecords = FilteredResults.Rows.Count;

                    TLogging.Log(Catalog.GetString("Summarise to parent accounts.."), TLoggingType.ToStatusBar);

                    for (Int32 Idx = 0; Idx < PostingAccountRecords; Idx++)
                    {
                        if (DbAdapter.IsCancelled)
                        {
                            return;
                        }

                        DataRow Row = FilteredResults.Rows[Idx];
                        String ParentAccountPath;
                        Int32 ParentAccountTypeOrder;
                        Int32 AccountLevel = AddTotalsToParentAccountRow(
                            FilteredResults,
                            HierarchyTbl,
                            LedgerNumber,
                            "", // No Cost Centres on Balance Sheet
                            Row["AccountCode"].ToString(),
                            Row,
                            false,
                            0,
                            out ParentAccountPath,
                            out ParentAccountTypeOrder,
                            ReadTrans);
                        Row["AccountLevel"] = AccountLevel;
                        Row["AccountPath"] = ParentAccountPath + Row["AccountCode"];
                    }
                }); // Get NewOrExisting AutoReadTransaction

            //
            // Now if I re-order the result by AccountPath, hide all the empty rows,
            // and rows that are too detailed, it should be what I need!

            Int32 DetailLevel = AParameters["param_nesting_depth"].ToInt32();
            String DepthFilter = " AND AccountLevel<=" + DetailLevel.ToString();
            FilteredResults.DefaultView.Sort = "AccountTypeOrder, AccountPath";

            FilteredResults.DefaultView.RowFilter = "(Actual <> 0 OR LastYearActual <> 0 )" + // Only non-zero rows
                                                    DepthFilter;    // Nothing too detailed

            FilteredResults = FilteredResults.DefaultView.ToTable("BalanceSheet");

            //
            // The income and expense accounts have been used to produce the balance of 'PL',
            // but now I don't want to see those details - only the total.

            FilteredResults.DefaultView.RowFilter = "AccountPath NOT LIKE '%-PL~%' OR AccountLevel < 3"; // Don't include Children of PL account
            FilteredResults = FilteredResults.DefaultView.ToTable("BalanceSheet");

            //
            // Finally, to make the hierarchical report possible,
            // I want to include a note to show whether a row has child rows,
            // and if it does, I'll copy this row to a new row, below the children, marking the new row as "footer".

            TLogging.Log(Catalog.GetString("Format data for reporting.."), TLoggingType.ToStatusBar);

            for (Int32 RowIdx = 0; RowIdx < FilteredResults.Rows.Count - 1; RowIdx++)
            {
                if (DbAdapter.IsCancelled)
                {
                    return FilteredResults;
                }

                Int32 ParentAccountLevel = Convert.ToInt32(FilteredResults.Rows[RowIdx]["AccountLevel"]);
                Boolean HasChildren = (Convert.ToInt32(FilteredResults.Rows[RowIdx + 1]["AccountLevel"]) > ParentAccountLevel);
                FilteredResults.Rows[RowIdx]["HasChildren"] = HasChildren;

                if (HasChildren)
                {
                    Int32 NextSiblingPos = -1;

                    for (Int32 ChildIdx = RowIdx + 2; ChildIdx < FilteredResults.Rows.Count; ChildIdx++)
                    {
                        if (Convert.ToInt32(FilteredResults.Rows[ChildIdx]["AccountLevel"]) <= ParentAccountLevel)  // This row is not a child of mine
                        {                                                                                           // so I insert my footer before here.
                            NextSiblingPos = ChildIdx;
                            break;
                        }
                    }

                    DataRow FooterRow = FilteredResults.NewRow();
                    DataUtilities.CopyAllColumnValues(FilteredResults.Rows[RowIdx], FooterRow);
                    FooterRow["ParentFooter"] = true;
                    FooterRow["HasChildren"] = false;

                    if (NextSiblingPos > 0)
                    {
                        FilteredResults.Rows.InsertAt(FooterRow, NextSiblingPos);
                    }
                    else
                    {
                        FilteredResults.Rows.Add(FooterRow);
                    }
                }
            } // for

            TLogging.Log("", TLoggingType.ToStatusBar);
            return FilteredResults;
        } // Balance Sheet Table
Exemplo n.º 22
0
 public RunMonthEndChecks(TLedgerInfo ALedgerInfo)
 {
     FledgerInfo = ALedgerInfo;
 }
Exemplo n.º 23
0
        /// <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();
                }
            }
        }
Exemplo n.º 24
0
        /// <summary>
        /// get the default bank account for this ledger
        /// </summary>
        /// <param name="ALedgerNumber"></param>
        public static string GetDefaultBankAccount(int ALedgerNumber)
        {
            string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
                SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());

            if (BankAccountCode.Length == 0)
            {
                TDBTransaction Transaction = null;

                DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                                                                          TEnforceIsolationLevel.eilMinimum,
                                                                          ref Transaction,
                                                                          delegate
                {
                    // use the first bank account
                    AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction);
                    accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
                                                              MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
                                                              AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";

                    if (accountProperties.DefaultView.Count > 0)
                    {
                        BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
                    }
                    else
                    {
                        string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
                                          "FROM a_gift_batch " +
                                          "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
                                          " AND a_gift_batch.a_batch_number_i = (" +
                                          "SELECT max(a_gift_batch.a_batch_number_i) " +
                                          "FROM a_gift_batch " +
                                          "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
                                          " AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";

                        DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", Transaction);

                        // use the Bank Account of the previous Gift Batch
                        if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
                        {
                            BankAccountCode = LatestAccountCode.Rows[0]["a_bank_account_code_c"].ToString();
                        }
                        // if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
                        else
                        {
                            AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, Transaction);

                            DataView dv        = AccountTable.DefaultView;
                            dv.Sort            = "a_account_code_c asc";
                            dv.RowFilter       = "a_account_active_flag_l = true AND a_posting_status_l = true";
                            DataTable sortedDT = dv.ToTable();

                            TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
                            TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
                            List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);

                            foreach (DataRow account in sortedDT.Rows)
                            {
                                // check if this account reports to the CASH account
                                if (children.Contains(account["a_account_code_c"].ToString()))
                                {
                                    BankAccountCode = account["a_account_code_c"].ToString();
                                    break;
                                }
                            }
                        }
                    }
                });
            }

            return(BankAccountCode);
        }
Exemplo n.º 25
0
        public void Test_2YearEnds()
        {
            intLedgerNumber = CommonNUnitFunctions.CreateNewLedger();
            CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\test-sql\\gl-test-year-end.sql", intLedgerNumber);
            TLedgerInfo LedgerInfo = new TLedgerInfo(intLedgerNumber);

            for (int countYear = 0; countYear < 2; countYear++)
            {
                TLogging.Log("preparing year number " + countYear.ToString());

                // accounting one gift
                string strAccountGift = "0200";
                string strAccountBank = "6200";
                TCommonAccountingTool commonAccountingTool =
                    new TCommonAccountingTool(intLedgerNumber, "NUNIT");
                commonAccountingTool.AddBaseCurrencyJournal();
                commonAccountingTool.JournalDescription = "Test Data accounts";
                commonAccountingTool.AddBaseCurrencyTransaction(
                    strAccountBank, "4301", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 100);
                commonAccountingTool.AddBaseCurrencyTransaction(
                    strAccountGift, "4301", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 100);
                Boolean PostedOk = commonAccountingTool.CloseSaveAndPost(); // returns true if posting seemed to work
                Assert.AreEqual(true, PostedOk, "Test batch can't be posted");

                bool blnLoop = true;

                while (blnLoop)
                {
//                  System.Windows.Forms.MessageBox.Show(LedgerInfo.CurrentPeriod.ToString(), "MonthEnd Period");

                    if (LedgerInfo.ProvisionalYearEndFlag)
                    {
                        blnLoop = false;
                    }
                    else
                    {
                        TVerificationResultCollection VerificationResult;
                        TPeriodIntervalConnector.PeriodMonthEnd(intLedgerNumber, false, out VerificationResult);
                        CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
                            "MonthEnd gave critical error at Period" + LedgerInfo.CurrentPeriod + ":\r\n");
                    }
                }

                TLogging.Log("Closing year number " + countYear.ToString());
                TReallocation reallocation = new TReallocation(LedgerInfo);
                TVerificationResultCollection verificationResult = new TVerificationResultCollection();
                reallocation.VerificationResultCollection = verificationResult;
                reallocation.IsInInfoMode = false;
//                Assert.AreEqual(1, reallocation.GetJobSize(), "Check 1 reallocation job is required"); // No job size is published by Reallocation
                reallocation.RunOperation();

                TYearEnd YearEndOperator = new TYearEnd(LedgerInfo);
                TGlmNewYearInit glmNewYearInit = new TGlmNewYearInit(LedgerInfo, countYear, YearEndOperator);
                glmNewYearInit.VerificationResultCollection = verificationResult;
                glmNewYearInit.IsInInfoMode = false;
//              Assert.Greater(glmNewYearInit.GetJobSize(), 0, "Check that NewYearInit has work to do"); // in this version, GetJobSize returns 0
                glmNewYearInit.RunOperation();
            }

            Assert.AreEqual(2, LedgerInfo.CurrentFinancialYear, "After YearEnd, Ledger is in year 2");

            TAccountPeriodInfo periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1);
            Assert.AreEqual(new DateTime(DateTime.Now.Year + 2,
                    1,
                    1), periodInfo.PeriodStartDate, "new Calendar should start with January 1st of next year");
        }
Exemplo n.º 26
0
        public void Test_09_TAccountPropertyHandler()
        {
            TLedgerInfo tHandleLedgerInfo = new TLedgerInfo(LedgerNumber);
            TAccountInfo tHandleAccountInfo = new TAccountInfo(tHandleLedgerInfo);

            tHandleAccountInfo.SetSpecialAccountCode(TAccountPropertyEnum.ICH_ACCT);
            Assert.IsTrue(tHandleAccountInfo.IsValid, "ICH_ACCT shall exist");
            Assert.AreEqual("8500", tHandleAccountInfo.AccountCode);
        }
Exemplo n.º 27
0
        public void Test_SwitchToNextMonth()
        {
            FLedgerNumber = CommonNUnitFunctions.CreateNewLedger();
            TLedgerInfo ledgerInfo = new TLedgerInfo(FLedgerNumber);
            int counter = 0;

            do
            {
                Int32 CurrentPeriod = ledgerInfo.CurrentPeriod;
                ++counter;
                Assert.Greater(20, counter, "Too many loops");

                // Set revaluation flag ...
                new TLedgerInitFlagHandler(FLedgerNumber,
                    TLedgerInitFlagEnum.Revaluation).Flag = true;

                // Run MonthEnd ...
                TVerificationResultCollection verificationResult;
                bool blnHasErrors = TPeriodIntervalConnector.PeriodMonthEnd(
                    FLedgerNumber, false, out verificationResult);

                if (!ledgerInfo.ProvisionalYearEndFlag)
                {
                    Assert.AreEqual(CurrentPeriod + 1,
                        ledgerInfo.CurrentPeriod, "Period increment");
                }

                Assert.IsFalse(blnHasErrors, "Month end without any error");
                System.Diagnostics.Debug.WriteLine("Counter: " + counter.ToString());
            } while (!ledgerInfo.ProvisionalYearEndFlag);
        }
Exemplo n.º 28
0
 /// <summary>
 /// The constructor creates a base batch and defines the batch parameters. There is only
 /// one batch to account. Use a new object to post another batch.
 /// </summary>
 /// <param name="ALedgerNumber">the ledger number</param>
 /// <param name="ABatchDescription">a batch description text</param>
 public TCommonAccountingTool(int ALedgerNumber,
                              string ABatchDescription)
 {
     FLedgerInfo = new TLedgerInfo(ALedgerNumber);
     TCommonAccountingTool_(ABatchDescription);
 }
Exemplo n.º 29
0
 /// <summary>
 /// </summary>
 /// <param name="ALedgerInfo"></param>
 public TYearEnd(TLedgerInfo ALedgerInfo)
 {
     FledgerInfo = ALedgerInfo;
 }
Exemplo n.º 30
0
        /// <summary>
        /// Populate ledger with gifts and invoices, post batches, close periods and years, according to FNumberOfClosedPeriods
        /// </summary>
        public static void PopulateData(string datadirectory, bool smallNumber = false)
        {
            int periodOverall = 0;
            int yearCounter = 0;
            int period = 1;
            int YearAD = DateTime.Today.Year - (FNumberOfClosedPeriods / 12);

            SampleDataGiftBatches.FLedgerNumber = FLedgerNumber;
            SampleDataAccountsPayable.FLedgerNumber = FLedgerNumber;
            SampleDataGiftBatches.LoadBatches(Path.Combine(datadirectory, "donations.csv"), smallNumber);
            SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD, smallNumber);

            while (periodOverall <= FNumberOfClosedPeriods)
            {
                TLogging.LogAtLevel(1, "working on year " + yearCounter.ToString() + " / period " + period.ToString());

                SampleDataGiftBatches.CreateGiftBatches(period);

                if (!SampleDataGiftBatches.PostBatches(yearCounter, period, (periodOverall == FNumberOfClosedPeriods) ? 1 : 0))
                {
                    throw new Exception("could not post gift batches");
                }

                if (!SampleDataAccountsPayable.PostAndPayInvoices(yearCounter, period, (periodOverall == FNumberOfClosedPeriods) ? 1 : 0))
                {
                    throw new Exception("could not post invoices");
                }

                TLedgerInfo LedgerInfo = new TLedgerInfo(FLedgerNumber);

                if (periodOverall < FNumberOfClosedPeriods)
                {
                    TAccountPeriodInfo AccountingPeriodInfo =
                        new TAccountPeriodInfo(FLedgerNumber, period);
                    TLogging.Log("closing period at " + AccountingPeriodInfo.PeriodEndDate.ToShortDateString());

                    // run month end
                    TMonthEnd MonthEndOperator = new TMonthEnd(LedgerInfo);
                    MonthEndOperator.SetNextPeriod();

                    if (period == 12)
                    {
                        TYearEnd YearEndOperator = new TYearEnd(LedgerInfo);
                        // run year end
                        TVerificationResultCollection verificationResult = new TVerificationResultCollection();
                        TReallocation reallocation = new TReallocation(LedgerInfo);
                        reallocation.VerificationResultCollection = verificationResult;
                        reallocation.IsInInfoMode = false;
                        reallocation.RunOperation();

                        TGlmNewYearInit glmNewYearInit = new TGlmNewYearInit(LedgerInfo, yearCounter, YearEndOperator);
                        glmNewYearInit.VerificationResultCollection = verificationResult;
                        glmNewYearInit.IsInInfoMode = false;
                        glmNewYearInit.RunOperation();

                        YearAD++;
                        yearCounter++;
                        SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD, smallNumber);
                        period = 0;
                    }
                }

                period++;
                periodOverall++;
            }
        }
Exemplo n.º 31
0
 /// <summary>
 /// A TLedgerInfo-Oject is used internally.
 /// If you already have one, use this constructor to reduce the number of database requests.
 /// </summary>
 /// <param name="ALedgerInfo">The ledger-info object</param>
 /// <param name="ABatchDescription">the description text ...</param>
 public TCommonAccountingTool(TLedgerInfo ALedgerInfo, string ABatchDescription)
 {
     FLedgerInfo = ALedgerInfo;
     TCommonAccountingTool_(ABatchDescription);
 }
Exemplo n.º 32
0
 /// <summary>
 /// This mininmal constructor defines the result collection for the error messages and
 /// Ledger Info to select the ledger ...
 /// </summary>
 /// <param name="ALedgerInfo"></param>
 public TAccountInfo(TLedgerInfo ALedgerInfo)
 {
     FLedgerInfo = ALedgerInfo;
     LoadData();
 }
Exemplo n.º 33
0
 /// <summary>
 ///
 /// </summary>
 public TReallocation(TLedgerInfo ALedgerInfo)
 {
     FledgerInfo = ALedgerInfo;
 }
Exemplo n.º 34
0
 /// <summary>
 /// A TLedgerInfo-Oject is used internally.
 /// If you already have one, use this constructor to reduce the number of database requests.
 /// </summary>
 /// <param name="ALedgerInfo">The ledger-info object</param>
 /// <param name="ABatchDescription">the description text ...</param>
 /// <param name="ADataBase"></param>
 public TCommonAccountingTool(TLedgerInfo ALedgerInfo, string ABatchDescription, TDataBase ADataBase = null)
 {
     FLedgerInfo = ALedgerInfo;
     FDataBase   = ADataBase;
     TCommonAccountingTool_(ABatchDescription);
 }
Exemplo n.º 35
0
        /// <summary>
        /// get the default bank account for this ledger
        /// </summary>
        /// <param name="ALedgerNumber"></param>
        public static string GetDefaultBankAccount(int 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

            string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
                SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());

            if (BankAccountCode.Length == 0)
            {
                TDBTransaction readTransaction = null;

                try
                {
                    DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                        TEnforceIsolationLevel.eilMinimum, ref readTransaction,
                        delegate
                        {
                            // use the first bank account
                            AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction);

                            accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
                                                                      MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
                                                                      AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";

                            if (accountProperties.DefaultView.Count > 0)
                            {
                                BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
                            }
                            else
                            {
                                string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
                                                  "FROM a_gift_batch " +
                                                  "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
                                                  " AND a_gift_batch.a_batch_number_i = (" +
                                                  "SELECT max(a_gift_batch.a_batch_number_i) " +
                                                  "FROM a_gift_batch " +
                                                  "WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
                                                  " AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";

                                DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", readTransaction);

                                // use the Bank Account of the previous Gift Batch
                                if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
                                {
                                    BankAccountCode = LatestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c"
                                }
                                // if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
                                else
                                {
                                    AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction);

                                    #region Validate Data

                                    if ((AccountTable == null) || (AccountTable.Count == 0))
                                    {
                                        throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
                                                    "Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
                                                Utilities.GetMethodName(true),
                                                ALedgerNumber));
                                    }

                                    #endregion Validate Data

                                    DataView dv = AccountTable.DefaultView;
                                    dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
                                    dv.RowFilter = String.Format("{0} = true AND {1} = true",
                                        AAccountTable.GetAccountActiveFlagDBName(),
                                        AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
                                    DataTable sortedDT = dv.ToTable();

                                    TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
                                    TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
                                    List <string>children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);

                                    foreach (DataRow account in sortedDT.Rows)
                                    {
                                        // check if this account reports to the CASH account
                                        if (children.Contains(account["a_account_code_c"].ToString()))
                                        {
                                            BankAccountCode = account["a_account_code_c"].ToString();
                                            break;
                                        }
                                    }
                                }
                            }
                        });
                }
                catch (Exception ex)
                {
                    TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
                            Utilities.GetMethodSignature(),
                            Environment.NewLine,
                            ex.Message));
                    throw ex;
                }
            }

            return BankAccountCode;
        }
Exemplo n.º 36
0
        public void Test_YearEnd()
        {
            intLedgerNumber = CommonNUnitFunctions.CreateNewLedger();

            TLedgerInfo LedgerInfo = new TLedgerInfo(intLedgerNumber);
            Assert.AreEqual(0, LedgerInfo.CurrentFinancialYear, "Before YearEnd, we should be in year 0");

            TAccountPeriodInfo periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1);
            Assert.AreEqual(new DateTime(DateTime.Now.Year,
                    1,
                    1), periodInfo.PeriodStartDate, "Calendar from base database should start with January 1st of this year");

            CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\test-sql\\gl-test-year-end.sql", intLedgerNumber);

            TCommonAccountingTool commonAccountingTool =
                new TCommonAccountingTool(intLedgerNumber, "NUNIT");
            commonAccountingTool.AddBaseCurrencyJournal();
            commonAccountingTool.JournalDescription = "Test Data accounts";
            string strAccountGift = "0200";
            string strAccountBank = "6200";
            string strAccountExpense = "4100";

            // Accounting of some gifts ...
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountBank, "4301", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 100);
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountBank, "4302", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 200);
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountBank, "4303", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 300);

            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountGift, "4301", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 100);
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountGift, "4302", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 200);
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountGift, "4303", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 300);


            // Accounting of some expenses ...

            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountExpense, "4301", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 150);
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountExpense, "4302", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 150);
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountExpense, "4303", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 200);

            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountBank, "4301", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 150);
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountBank, "4302", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 150);
            commonAccountingTool.AddBaseCurrencyTransaction(
                strAccountBank, "4303", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 200);

            commonAccountingTool.CloseSaveAndPost(); // returns true if posting seemed to work


            TVerificationResultCollection verificationResult = new TVerificationResultCollection();

            bool blnLoop = true;

            while (blnLoop)
            {
                if (LedgerInfo.ProvisionalYearEndFlag)
                {
                    blnLoop = false;
                }
                else
                {
                    TVerificationResultCollection VerificationResult;
                    TPeriodIntervalConnector.PeriodMonthEnd(intLedgerNumber, false, out VerificationResult);
                    CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
                        "Running MonthEnd gave critical error");
                }
            }

            // check before year end that income and expense accounts are not 0
            int intYear = 0;
            CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
                -50, 0, 50, 0, 100, 0);
            CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
                150, 0, 150, 0, 200, 0);
            CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
                100, 0, 200, 0, 300, 0);

            // test that we cannot post to period 12 anymore, all periods are closed?
            LedgerInfo = new TLedgerInfo(intLedgerNumber);
            Assert.AreEqual(true, LedgerInfo.ProvisionalYearEndFlag, "Provisional YearEnd flag should be set");
            Assert.AreEqual(TYearEndProcessStatus.RESET_STATUS,
                (TYearEndProcessStatus)LedgerInfo.YearEndProcessStatus,
                "YearEnd process status should be still on RESET");

            //
            // Reallocation is never called explicitly like this - it's not really appropriate
            // because I'm about to call it again as part of YearEnd, below.
            // But a tweak in the reallocation code means that it should now cope with being called twice.

            TReallocation reallocation = new TReallocation(LedgerInfo);
            reallocation.VerificationResultCollection = verificationResult;
            reallocation.IsInInfoMode = false;
            reallocation.RunOperation();

            // check amounts after reallocation
            CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
                -50, 0, 50, 0, 100, 0);
            CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
                0, -150, 0, -150, 0, -200);
            CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
                0, -100, 0, -200, 0, -300);

            // first run in info mode
            TPeriodIntervalConnector.PeriodYearEnd(intLedgerNumber, true, out verificationResult);
            CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(verificationResult,
                "YearEnd test should not have critical errors");

            // now run for real
            TPeriodIntervalConnector.PeriodYearEnd(intLedgerNumber, false, out verificationResult);
            CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(verificationResult,
                "YearEnd should not have critical errors");

            ++intYear;
            // check after year end that income and expense accounts are 0, bank account remains
            CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
                -50, 0, 50, 0, 100, 0);
            CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
                0, 0, 0, 0, 0, 0);
            CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
                0, 0, 0, 0, 0, 0);

            // also check the glm period records
            CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountBank,
                -50, 50, 100);
            CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountExpense,
                0, 0, 0);
            CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountGift,
                0, 0, 0);

            // 9700 is the account that the expenses and income from last year is moved to
            TGlmInfo glmInfo = new TGlmInfo(intLedgerNumber, intYear, "9700");
            glmInfo.Reset();
            Assert.IsTrue(glmInfo.MoveNext(), "9700 account not found");

            Assert.AreEqual(100, glmInfo.YtdActualBase);
            Assert.AreEqual(0, glmInfo.ClosingPeriodActualBase);

            LedgerInfo = new TLedgerInfo(intLedgerNumber);
            Assert.AreEqual(1, LedgerInfo.CurrentFinancialYear, "After YearEnd, we are in a new financial year");
            Assert.AreEqual(1, LedgerInfo.CurrentPeriod, "After YearEnd, we are in Period 1");
            Assert.AreEqual(false, LedgerInfo.ProvisionalYearEndFlag, "After YearEnd, ProvisionalYearEnd flag should not be set");
            Assert.AreEqual(TYearEndProcessStatus.RESET_STATUS,
                (TYearEndProcessStatus)LedgerInfo.YearEndProcessStatus,
                "after year end, year end process status should be RESET");

            periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1);
            Assert.AreEqual(new DateTime(DateTime.Now.Year + 1,
                    1,
                    1), periodInfo.PeriodStartDate, "new Calendar should start with January 1st of next year");
        }
Exemplo n.º 37
0
 /// <summary>
 /// The constructor creates a base batch and defines the batch parameters. There is only
 /// one batch to account. Use a new object to post another batch.
 /// </summary>
 /// <param name="ALedgerNumber">the ledger number</param>
 /// <param name="ABatchDescription">a batch description text</param>
 public TCommonAccountingTool(int ALedgerNumber,
     string ABatchDescription)
 {
     FLedgerInfo = new TLedgerInfo(ALedgerNumber);
     TCommonAccountingTool_(ABatchDescription);
 }
Exemplo n.º 38
0
 /// <summary>
 ///
 /// </summary>
 public TestGLPeriodicEndMonth()
 {
     FLedgerNumber = 43;
     FledgerInfo = new TLedgerInfo(FLedgerNumber);
 }
Exemplo n.º 39
0
 /// <summary>
 /// </summary>
 /// <param name="ALedgerInfo"></param>
 public TResetForwardPeriodICH(TLedgerInfo ALedgerInfo)
 {
     FLedgerInfo = ALedgerInfo;
 }