示例#1
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();
        }