private DataTable GetAccountListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName) { StringCollection FieldList = new StringCollection(); FieldList.Add(AAccountTable.GetLedgerNumberDBName()); FieldList.Add(AAccountTable.GetAccountCodeDBName()); FieldList.Add(AAccountTable.GetAccountTypeDBName()); FieldList.Add(AAccountTable.GetAccountCodeShortDescDBName()); FieldList.Add(AAccountTable.GetAccountActiveFlagDBName()); FieldList.Add(AAccountTable.GetPostingStatusDBName()); FieldList.Add(AAccountTable.GetForeignCurrencyFlagDBName()); FieldList.Add(AAccountTable.GetForeignCurrencyCodeDBName()); GLSetupTDS TempDS = new GLSetupTDS(); AAccountAccess.LoadViaALedger(TempDS, ALedgerNumber, FieldList, AReadTransaction); // load AAccountProperty and set the BankAccountFlag AAccountPropertyAccess.LoadViaALedger(TempDS, ALedgerNumber, AReadTransaction); foreach (AAccountPropertyRow accProp in TempDS.AAccountProperty.Rows) { if ((accProp.PropertyCode == MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT) && (accProp.PropertyValue == "true")) { TempDS.AAccount.DefaultView.RowFilter = String.Format("{0}='{1}'", AAccountTable.GetAccountCodeDBName(), accProp.AccountCode); GLSetupTDSAAccountRow acc = (GLSetupTDSAAccountRow)TempDS.AAccount.DefaultView[0].Row; acc.BankAccountFlag = true; TempDS.AAccount.DefaultView.RowFilter = ""; } } // not currently needed as an Account is only a Bank Account if it has a 'Bank Account' Account Property // load AAccountHierarchyDetails and check if this account reports to the CASH account /*AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(TempDS, * ALedgerNumber, * MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, * AReadTransaction); * * TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber); * TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo); * List <string>children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT); * * foreach (GLSetupTDSAAccountRow account in TempDS.AAccount.Rows) * { * if (children.Contains(account.AccountCode)) * { * account.CashAccountFlag = true; * } * }*/ return(TempDS.AAccount); }
/// <summary> /// Export the accounts /// </summary> public static void ExportAccounts(string AOutputPath, char ACSVSeparator, string ANewLine, Int32 ALedgerNumber, List <string> AAccounts) { string filename = Path.GetFullPath(Path.Combine(AOutputPath, "account.csv")); Console.WriteLine("Writing file: " + filename); // only export accounts that are actually used with these cost centres string sql = String.Format("SELECT {0}, {1}, {2} from PUB_{3} WHERE {4} = {5} AND " + " {6}=true ORDER BY {0}", AAccountTable.GetAccountCodeDBName(), AAccountTable.GetAccountCodeLongDescDBName(), AAccountTable.GetDebitCreditIndicatorDBName(), AAccountTable.GetTableDBName(), AAccountTable.GetLedgerNumberDBName(), ALedgerNumber, AAccountTable.GetPostingStatusDBName()); TDBTransaction Transaction = null; DataTable accounts = null; DBAccess.GDBAccessObj.BeginAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { accounts = DBAccess.GDBAccessObj.SelectDT(sql, "accounts", Transaction); }); StringBuilder sb = new StringBuilder(); if (accounts != null) { foreach (DataRow row in accounts.Rows) { if (AAccounts.Contains(row[0].ToString())) { sb.Append(StringHelper.StrMerge(new string[] { row[0].ToString(), row[1].ToString(), Convert.ToBoolean(row[2]) ? "Soll" : "Haben" }, ACSVSeparator)); sb.Append(ANewLine); } } } StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }
private void CheckIfRevaluationIsDone() { if (!FInfoMode) { return; } /* * I'm no longer looking at this flag, * since it can be set even though some accounts are left requiring revaluation. * See Mantis 0004059 * * if ((new TLedgerInitFlagHandler(FledgerInfo.LedgerNumber, * TLedgerInitFlagEnum.Revaluation).Flag)) * { * return; // Revaluation has been performed for the current period. * } */ TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { // TODO: could also check for the balance in this month of the foreign currency account. if all balances are zero, no revaluation is needed. string testForForeignKeyAccount = String.Format("SELECT COUNT(*) FROM PUB_a_account WHERE {0} = {1} and {2} = true", AAccountTable.GetLedgerNumberDBName(), FledgerInfo.LedgerNumber, AAccountTable.GetForeignCurrencyFlagDBName()); Int32 ForeignAccountCount = Convert.ToInt32(DBAccess.GDBAccessObj.ExecuteScalar(testForForeignKeyAccount, Transaction)); if (ForeignAccountCount > 0) { TVerificationResult tvr = new TVerificationResult( Catalog.GetString("Currency revaluation"), Catalog.GetString( "Before proceeding you may want to revalue the foreign currency accounts."), "", TPeriodEndErrorAndStatusCodes.PEEC_05.ToString(), TResultSeverity.Resv_Noncritical); // Error is non-critical - the user can choose to continue. FverificationResults.Add(tvr); } }); // Get NewOrExisting AutoReadTransaction }
private void CheckIfRevaluationIsDone() { if ((new TLedgerInitFlagHandler(FledgerInfo.LedgerNumber, TLedgerInitFlagEnum.Revaluation).Flag)) { return; // Revaluation has been performed for the current period. } bool NewTransaction = false; TDBTransaction transaction; try { transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); // TODO: could also check for the balance in this month of the foreign currency account. if all balances are zero, no revaluation is needed. string testForForeignKeyAccount = String.Format("SELECT COUNT(*) FROM PUB_a_account WHERE {0} = {1} and {2} = true", AAccountTable.GetLedgerNumberDBName(), FledgerInfo.LedgerNumber, AAccountTable.GetForeignCurrencyFlagDBName()); if (Convert.ToInt32(DBAccess.GDBAccessObj.ExecuteScalar(testForForeignKeyAccount, transaction)) != 0) { TVerificationResult tvr = new TVerificationResult( Catalog.GetString("Ledger revaluation"), Catalog.GetString("Please run a foreign currency revaluation first."), "", TPeriodEndErrorAndStatusCodes.PEEC_05.ToString(), TResultSeverity.Resv_Critical); // Error is critical but additional checks can still be done FverificationResults.Add(tvr); FHasCriticalErrors = true; } } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// export all GL Transactions in the given year, towards the specified cost centres /// </summary> public static void ExportGLTransactions(string AOutputPath, char ACSVSeparator, string ANewLine, Int32 ALedgerNumber, Int32 AFinancialYear, string ACostCentres, string AIgnoreAccounts, string AIgnoreReferences, ref List <string> ACostCentresInvolved, ref List <string> AAccountsInvolved) { string filename = Path.GetFullPath(Path.Combine(AOutputPath, "transaction.csv")); Console.WriteLine("Writing file: " + filename); TDBTransaction Transaction = new TDBTransaction(); ATransactionTable transactions = new ATransactionTable(); ATransAnalAttribTable TransAnalAttrib = new ATransAnalAttribTable(); ATransactionTable allTransactionsInJournal = new ATransactionTable(); AGiftBatchTable giftbatches = new AGiftBatchTable(); AAccountTable accounts = new AAccountTable(); DBAccess.ReadTransaction(ref Transaction, delegate { TDataBase db = Transaction.DataBaseObj; string sql = String.Format("SELECT T.*, B.{4} AS a_transaction_date_d " + "FROM PUB_{8} AS B, PUB_{7} AS T " + "WHERE B.{9} = {10} AND B.{15} = {16} AND B.{11}='{12}' " + "AND T.{9} = B.{9} AND T.{0} = B.{0} " + "AND T.{13} IN ({14}) " + "AND NOT T.{17} IN ({19}) " + "AND NOT T.{20} IN ({21}) " + "ORDER BY {0}, {1}, {2}", ATransactionTable.GetBatchNumberDBName(), ATransactionTable.GetJournalNumberDBName(), ATransactionTable.GetTransactionNumberDBName(), ATransactionTable.GetTransactionAmountDBName(), ABatchTable.GetDateEffectiveDBName(), ATransactionTable.GetNarrativeDBName(), ATransactionTable.GetReferenceDBName(), ATransactionTable.GetTableDBName(), ABatchTable.GetTableDBName(), ATransactionTable.GetLedgerNumberDBName(), ALedgerNumber, ABatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_POSTED, ATransactionTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'", ABatchTable.GetBatchYearDBName(), AFinancialYear, ATransactionTable.GetAccountCodeDBName(), ATransactionTable.GetDebitCreditIndicatorDBName(), "'" + AIgnoreAccounts.Replace(",", "','") + "'", ATransactionTable.GetReferenceDBName(), "'" + AIgnoreReferences.Replace(",", "','") + "'"); transactions = (ATransactionTable)db.SelectDT(transactions, sql, Transaction, null, 0, 0); // get the analysis attributes sql = String.Format("SELECT A.* from PUB_{1} AS B, PUB_{13} AS T, PUB_{0} AS A " + "WHERE B.{2} = {3} AND B.{4} = {5} AND B.{6}='{7}' " + "AND T.{2} = B.{2} AND T.{8} = B.{8} " + "AND T.{9} IN ({10}) " + "AND A.{2} = T.{2} AND A.{8} = T.{8} AND A.{11} = T.{11} AND A.{12} = T.{12}", ATransAnalAttribTable.GetTableDBName(), ABatchTable.GetTableDBName(), ABatchTable.GetLedgerNumberDBName(), ALedgerNumber, ABatchTable.GetBatchYearDBName(), AFinancialYear, ABatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_POSTED, ATransactionTable.GetBatchNumberDBName(), ATransactionTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'", ATransactionTable.GetJournalNumberDBName(), ATransactionTable.GetTransactionNumberDBName(), ATransactionTable.GetTableDBName(), ABatchTable.GetBatchYearDBName()); db.SelectDT(TransAnalAttrib, sql, Transaction, null, 0, 0); TransAnalAttrib.DefaultView.Sort = ATransAnalAttribTable.GetBatchNumberDBName() + "," + ATransAnalAttribTable.GetJournalNumberDBName() + "," + ATransAnalAttribTable.GetTransactionNumberDBName(); // get a list of all batches involved List <Int64> batches = new List <Int64>(); StringBuilder batchnumbers = new StringBuilder(); foreach (ATransactionRow r in transactions.Rows) { if (!batches.Contains(r.BatchNumber)) { batches.Add(r.BatchNumber); batchnumbers.Append(r.BatchNumber.ToString() + ","); } } // get the other transactions in the same journal for finding the opposite cc/acc involved // for performance reasons, get all transactions of the whole batch sql = String.Format("SELECT DISTINCT TJ.* " + "FROM PUB_{0} AS TJ " + "WHERE TJ.{1} = {2} AND TJ.{3} IN ({4})", ATransactionTable.GetTableDBName(), ATransactionTable.GetLedgerNumberDBName(), ALedgerNumber, ATransactionTable.GetBatchNumberDBName(), batchnumbers.ToString() + "-1"); allTransactionsInJournal = (ATransactionTable)db.SelectDT(allTransactionsInJournal, sql, Transaction, null, 0, 0); allTransactionsInJournal.DefaultView.Sort = ATransactionTable.GetBatchNumberDBName() + "," + ATransactionTable.GetJournalNumberDBName(); // get all names of gift batches sql = String.Format("SELECT * FROM PUB_{0} " + "WHERE {1} = {2} " + "AND {3} = {4}", AGiftBatchTable.GetTableDBName(), AGiftBatchTable.GetLedgerNumberDBName(), ALedgerNumber, AGiftBatchTable.GetBatchYearDBName(), AFinancialYear); db.SelectDT(giftbatches, sql, Transaction, null, 0, 0); giftbatches.DefaultView.Sort = AGiftBatchTable.GetBatchNumberDBName(); sql = String.Format("SELECT * FROM PUB_{0} " + "WHERE {1} = {2}", AAccountTable.GetTableDBName(), AAccountTable.GetLedgerNumberDBName(), ALedgerNumber); db.SelectDT(accounts, sql, Transaction, null, 0, 0); accounts.DefaultView.Sort = AAccountTable.GetAccountCodeDBName(); }); StringBuilder sb = new StringBuilder(); int rowCounter = 0; foreach (ATransactionRow row in transactions.Rows) { if (row.DebitCreditIndicator) { row.TransactionAmount *= -1.0m; } StringBuilder attributes = new StringBuilder(); DataRowView[] RelatedTransactions = allTransactionsInJournal.DefaultView.FindRows(new object[] { row.BatchNumber, row.JournalNumber }); ATransactionRow[] OtherTransactions = GetOtherTransactions(row, RelatedTransactions); string OtherCostCentres = string.Empty; string OtherAccountCodes = string.Empty; if (OtherTransactions.Length < 30) { foreach (ATransactionRow r in OtherTransactions) { OtherCostCentres = StringHelper.AddCSV(OtherCostCentres, r.CostCentreCode); OtherAccountCodes = StringHelper.AddCSV(OtherAccountCodes, r.AccountCode); } } if (!ACostCentresInvolved.Contains(row.CostCentreCode)) { ACostCentresInvolved.Add(row.CostCentreCode); } if (!AAccountsInvolved.Contains(row.AccountCode)) { AAccountsInvolved.Add(row.AccountCode); } // we are using gift batch for receiving payments string Narrative = row.Narrative; if (Narrative.StartsWith("GB - Gift Batch ") && row.Reference.StartsWith("GB")) { // find the account and set the account description into the narrative try { DataRowView[] acc = accounts.DefaultView.FindRows(row.AccountCode); Narrative = ((AAccountRow)acc[0].Row).AccountCodeLongDesc; } catch (Exception) { } try { DataRowView[] gb = giftbatches.DefaultView.FindRows(Convert.ToInt32(row.Reference.Substring(2))); Narrative += " " + ((AGiftBatchRow)gb[0].Row).BatchDescription; } catch (Exception) { } } sb.Append(StringHelper.StrMerge( new string[] { "B" + row.BatchNumber.ToString() + "_J" + row.JournalNumber.ToString() + "_T" + row.TransactionNumber.ToString(), row.CostCentreCode, row.AccountCode, row.TransactionDate.ToString("yyyyMMdd"), OtherCostCentres, OtherAccountCodes, Narrative, row.Reference, String.Format("{0:N}", row.TransactionAmount), attributes.ToString() }, ACSVSeparator)); sb.Append(ANewLine); rowCounter++; if (rowCounter % 500 == 0) { TLogging.Log("Processing transactions " + rowCounter.ToString()); } } TLogging.Log("Processing transactions " + rowCounter.ToString()); StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }