예제 #1
0
        public void T0_Consolidation()
        {
            // reset the database, so that there is no consolidated budget
            CommonNUnitFunctions.ResetDatabase();

            string budgetTestFile = TAppSettingsManager.GetValue("GiftBatch.file",
                                                                 CommonNUnitFunctions.rootPath + "/csharp/ICT/Testing/lib/MFinance/SampleData/BudgetImport-All.csv");

            int NumBudgetsUpdated;
            int NumFailedRows;
            TVerificationResultCollection VerificationResult;

            BudgetTDS ImportDS = new BudgetTDS();

            string ImportString = File.ReadAllText(budgetTestFile);

            // import budget from CSV
            decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets(
                FLedgerNumber,
                0,
                ImportString,
                budgetTestFile,
                new string[] { ",", "dmy", "American" },
                ref ImportDS,
                out NumBudgetsUpdated,
                out NumFailedRows,
                out VerificationResult);

            Assert.AreNotEqual(0, RowsImported, "expect to import several rows");

            CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
                                                                                "ImportBudgets has critical errors:");

            BudgetTDSAccess.SubmitChanges(ImportDS);

            // check for value in budget table
            string sqlQueryBudget =
                String.Format(
                    "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_period_number_i = 1 AND " +
                    "a_ledger_number_i = {3} AND a_revision_i = 0 AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'",
                    ABudgetPeriodTable.GetBudgetBaseDBName(),
                    ABudgetTable.GetTableDBName(),
                    ABudgetPeriodTable.GetTableDBName(),
                    FLedgerNumber);

            decimal budgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryBudget, IsolationLevel.ReadCommitted));

            Assert.AreEqual(250m, budgetValue, "problem with importing budget from CSV");

            // check for zero in glmperiod budget: that row does not even exist yet, so check that it does not exist
            string sqlQueryCheckEmptyConsolidatedBudget =
                String.Format(
                    "SELECT COUNT(*) FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND a_period_number_i = 1 AND " +
                    "a_ledger_number_i = {2} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'",
                    AGeneralLedgerMasterPeriodTable.GetTableDBName(),
                    AGeneralLedgerMasterTable.GetTableDBName(),
                    FLedgerNumber);

            Assert.AreEqual(0, DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryCheckEmptyConsolidatedBudget,
                                                                   IsolationLevel.ReadCommitted), "budget should not be consolidated yet");

            // consolidate the budget
            TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber);
            TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true);

            // check for correct value in glmperiod budget
            string sqlQueryConsolidatedBudget =
                String.Format(
                    "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_glm_sequence_i = {2}.a_glm_sequence_i AND a_period_number_i = 1 AND " +
                    "a_ledger_number_i = {3} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'",
                    AGeneralLedgerMasterPeriodTable.GetBudgetBaseDBName(),
                    AGeneralLedgerMasterPeriodTable.GetTableDBName(),
                    AGeneralLedgerMasterTable.GetTableDBName(),
                    FLedgerNumber);

            decimal consolidatedBudgetValue =
                Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted));

            Assert.AreEqual(250m, consolidatedBudgetValue, "budget should now be consolidated");

            // TODO: also check some summary account and cost centre for summed up budget values

            // check how reposting a budget works
            string sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 44 WHERE a_period_number_i = 1 AND " +
                                                   "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " +
                                                   "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')",
                                                   ABudgetPeriodTable.GetTableDBName(),
                                                   ABudgetPeriodTable.GetBudgetBaseDBName(),
                                                   ABudgetTable.GetTableDBName(),
                                                   FLedgerNumber);

            bool           SubmissionOK = true;
            TDBTransaction Transaction  = null;

            DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
                                                       delegate
            {
                DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction);
            });

            // post all budgets again
            TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber);
            TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true);

            consolidatedBudgetValue =
                Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted));
            Assert.AreEqual(44.0m, consolidatedBudgetValue, "budget should be consolidated with the new value");

            // post only a modified budget (testing UnPostBudget)
            sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 65 WHERE a_period_number_i = 1 AND " +
                                            "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " +
                                            "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')",
                                            ABudgetPeriodTable.GetTableDBName(),
                                            ABudgetPeriodTable.GetBudgetBaseDBName(),
                                            ABudgetTable.GetTableDBName(),
                                            FLedgerNumber);

            string sqlMarkBudgetForConsolidation = String.Format("UPDATE PUB_{0} SET {1} = false WHERE " +
                                                                 "a_ledger_number_i = {2} " +
                                                                 "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'",
                                                                 ABudgetTable.GetTableDBName(),
                                                                 ABudgetTable.GetBudgetStatusDBName(),
                                                                 FLedgerNumber);

            SubmissionOK = true;
            Transaction  = null;
            DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
                                                       delegate
            {
                DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction);
                DBAccess.GDBAccessObj.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction);
            });

            // post only modified budget again
            TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber);
            TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, false);

            consolidatedBudgetValue =
                Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted));
            Assert.AreEqual(65.0m, consolidatedBudgetValue, "budget should be consolidated with the new value, after UnPostBudget");

            // TODO: test forwarding periods. what happens to next year values, when there is no next year glm record yet?
        }
예제 #2
0
        /// <summary>
        /// export all GL Balances in the given year, towards the specified cost centres
        /// </summary>
        public static void ExportGLBalances(string AOutputPath,
                                            char ACSVSeparator,
                                            string ANewLine,
                                            Int32 ALedgerNumber,
                                            Int32 AFinancialYear,
                                            string ACostCentres,
                                            string AIgnoreAccounts)
        {
            string filename = Path.GetFullPath(Path.Combine(AOutputPath, "balance.csv"));

            Console.WriteLine("Writing file: " + filename);

            string sql =
                String.Format("SELECT GLM.{1} AS CostCentre, GLM.{0} AS Account, {2} AS StartBalance, GLMP.{3} AS EndBalance " +
                              "FROM PUB_{4} AS GLM, PUB_{10} AS A, PUB_{13} AS GLMP " +
                              "WHERE GLM.{5} = {6} AND {7} = {8} AND GLM.{1} IN ({9}) " +
                              "AND GLMP.{14} = GLM.{14} AND GLMP.{15} = 12 " +
                              "AND A.{5} = GLM.{5} AND A.{0} = GLM.{0} AND " +
                              "A.{11}=true AND NOT GLM.{0} IN ({12})" +
                              "ORDER BY GLM.{1}, GLM.{0}",
                              AGeneralLedgerMasterTable.GetAccountCodeDBName(),
                              AGeneralLedgerMasterTable.GetCostCentreCodeDBName(),
                              AGeneralLedgerMasterTable.GetStartBalanceBaseDBName(),
                              AGeneralLedgerMasterPeriodTable.GetActualBaseDBName(),
                              AGeneralLedgerMasterTable.GetTableDBName(),
                              AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
                              ALedgerNumber,
                              AGeneralLedgerMasterTable.GetYearDBName(),
                              AFinancialYear,
                              "'" + ACostCentres.Replace(",", "','") + "'",
                              AAccountTable.GetTableDBName(),
                              AAccountTable.GetPostingStatusDBName(),
                              "'" + AIgnoreAccounts.Replace(",", "','") + "'",
                              AGeneralLedgerMasterPeriodTable.GetTableDBName(),
                              AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName(),
                              AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName());

            TDBTransaction Transaction = new TDBTransaction();
            DataTable      balances    = null;

            DBAccess.ReadTransaction(ref Transaction,
                                     delegate
            {
                balances = Transaction.DataBaseObj.SelectDT(sql, "balances", Transaction);
            });

            StringBuilder sb = new StringBuilder();

            if (balances != null)
            {
                foreach (DataRow row in balances.Rows)
                {
                    sb.Append(StringHelper.StrMerge(
                                  new string[] {
                        row["CostCentre"].ToString(),
                        row["Account"].ToString(),
                        String.Format("{0:N}", Convert.ToDecimal(row["StartBalance"])),
                        String.Format("{0:N}", Convert.ToDecimal(row["EndBalance"]))
                    }, ACSVSeparator));

                    sb.Append(ANewLine);
                }
            }

            StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));

            sw.Write(sb.ToString());
            sw.Close();
        }
예제 #3
0
        public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber)
        {
            FBudgetTDS = new BudgetTDS();

            TDBTransaction Transaction = null;

            DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
                                                                      TEnforceIsolationLevel.eilMinimum,
                                                                      ref Transaction,
                                                                      delegate
            {
                ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction);

                string sqlLoadBudgetForThisAndNextYear =
                    string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)",
                                  ABudgetTable.GetTableDBName(),
                                  ABudgetTable.GetLedgerNumberDBName(),
                                  ABudgetTable.GetYearDBName());

                List <OdbcParameter> parameters = new List <OdbcParameter>();
                OdbcParameter param             = new OdbcParameter("ledgernumber", OdbcType.Int);
                param.Value = ALedgerNumber;
                parameters.Add(param);
                param       = new OdbcParameter("thisyear", OdbcType.Int);
                param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear;
                parameters.Add(param);
                param       = new OdbcParameter("nextyear", OdbcType.Int);
                param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1;
                parameters.Add(param);

                DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction,
                                             parameters.ToArray());

                string sqlLoadBudgetPeriodForThisAndNextYear =
                    string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " +
                                  "{2}=? AND ({3} = ? OR {3} = ?)",
                                  ABudgetPeriodTable.GetTableDBName(),
                                  ABudgetTable.GetTableDBName(),
                                  ABudgetTable.GetLedgerNumberDBName(),
                                  ABudgetTable.GetYearDBName());

                DBAccess.GDBAccessObj.Select(FBudgetTDS,
                                             sqlLoadBudgetPeriodForThisAndNextYear,
                                             FBudgetTDS.ABudgetPeriod.TableName,
                                             Transaction,
                                             parameters.ToArray());

                // Accept row changes here so that the Client gets 'unmodified' rows
                FBudgetTDS.AcceptChanges();

                GLPostingDS = new GLPostingTDS();
                AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
                AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
                ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
                ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction);

                // get the glm sequences for this year and next year
                for (int i = 0; i <= 1; i++)
                {
                    int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i;

                    AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false);

                    TemplateRow.LedgerNumber = ALedgerNumber;
                    TemplateRow.Year         = Year;

                    GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction));
                }

                string sqlLoadGlmperiodForThisAndNextYear =
                    string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " +
                                  "{2}=? AND ({3} = ? OR {3} = ?)",
                                  AGeneralLedgerMasterPeriodTable.GetTableDBName(),
                                  AGeneralLedgerMasterTable.GetTableDBName(),
                                  AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
                                  AGeneralLedgerMasterTable.GetYearDBName());

                DBAccess.GDBAccessObj.Select(GLPostingDS,
                                             sqlLoadGlmperiodForThisAndNextYear,
                                             GLPostingDS.AGeneralLedgerMasterPeriod.TableName,
                                             Transaction,
                                             parameters.ToArray());
            });

            GLPostingDS.AcceptChanges();

            return(true);
        }