/// load transactions from a CSV file into the currently selected batch private void CreateBatchFromCSVFile(string ADataFilename, XmlNode ARootNode, AJournalRow ARefJournalRow, Int32 AFirstTransactionRow, string ADefaultCostCentre, out DateTime ALatestTransactionDate) { StreamReader dataFile = new StreamReader(ADataFilename, System.Text.Encoding.Default); XmlNode ColumnsNode = TXMLParser.GetChild(ARootNode, "Columns"); string Separator = TXMLParser.GetAttribute(ARootNode, "Separator"); string DateFormat = TXMLParser.GetAttribute(ARootNode, "DateFormat"); string ThousandsSeparator = TXMLParser.GetAttribute(ARootNode, "ThousandsSeparator"); string DecimalSeparator = TXMLParser.GetAttribute(ARootNode, "DecimalSeparator"); Int32 lineCounter; // read headers for (lineCounter = 0; lineCounter < AFirstTransactionRow - 1; lineCounter++) { dataFile.ReadLine(); } decimal sumDebits = 0.0M; decimal sumCredits = 0.0M; ALatestTransactionDate = DateTime.MinValue; do { string line = dataFile.ReadLine(); lineCounter++; GLBatchTDSATransactionRow NewTransaction = FMainDS.ATransaction.NewRowTyped(true); FMyForm.GetTransactionsControl().NewRowManual(ref NewTransaction, ARefJournalRow); FMainDS.ATransaction.Rows.Add(NewTransaction); foreach (XmlNode ColumnNode in ColumnsNode.ChildNodes) { string Value = StringHelper.GetNextCSV(ref line, Separator); string UseAs = TXMLParser.GetAttribute(ColumnNode, "UseAs"); if (UseAs.ToLower() == "reference") { NewTransaction.Reference = Value; } else if (UseAs.ToLower() == "narrative") { NewTransaction.Narrative = Value; } else if (UseAs.ToLower() == "dateeffective") { NewTransaction.SetTransactionDateNull(); if (Value.Trim().ToString().Length > 0) { try { NewTransaction.TransactionDate = XmlConvert.ToDateTime(Value, DateFormat); if (NewTransaction.TransactionDate > ALatestTransactionDate) { ALatestTransactionDate = NewTransaction.TransactionDate; } } catch (Exception exp) { MessageBox.Show(Catalog.GetString("Problem with date in row " + lineCounter.ToString() + " Error: " + exp.Message)); } } } else if (UseAs.ToLower() == "account") { if (Value.Length > 0) { if (Value.Contains(" ")) { // cut off currency code; should have been defined in the data description file, for the whole batch Value = Value.Substring(0, Value.IndexOf(" ") - 1); } Value = Value.Replace(ThousandsSeparator, ""); Value = Value.Replace(DecimalSeparator, "."); NewTransaction.TransactionAmount = Convert.ToDecimal(Value, System.Globalization.CultureInfo.InvariantCulture); NewTransaction.CostCentreCode = ADefaultCostCentre; NewTransaction.AccountCode = ColumnNode.Name; NewTransaction.DebitCreditIndicator = true; if (TXMLParser.HasAttribute(ColumnNode, "CreditDebit") && (TXMLParser.GetAttribute(ColumnNode, "CreditDebit").ToLower() == "credit")) { NewTransaction.DebitCreditIndicator = false; } if (NewTransaction.TransactionAmount < 0) { NewTransaction.TransactionAmount *= -1.0M; NewTransaction.DebitCreditIndicator = !NewTransaction.DebitCreditIndicator; } if (TXMLParser.HasAttribute(ColumnNode, "AccountCode")) { NewTransaction.AccountCode = TXMLParser.GetAttribute(ColumnNode, "AccountCode"); } if (NewTransaction.DebitCreditIndicator) { sumDebits += NewTransaction.TransactionAmount; } else if (!NewTransaction.DebitCreditIndicator) { sumCredits += NewTransaction.TransactionAmount; } } } } if (!NewTransaction.IsTransactionDateNull()) { NewTransaction.AmountInBaseCurrency = GLRoutines.CurrencyMultiply(NewTransaction.TransactionAmount, TExchangeRateCache.GetDailyExchangeRate( ARefJournalRow.TransactionCurrency, FMainDS.ALedger[0].BaseCurrency, NewTransaction.TransactionDate, false)); // // The International currency calculation is changed to "Base -> International", because it's likely // we won't have a "Transaction -> International" conversion rate defined. // NewTransaction.AmountInIntlCurrency = GLRoutines.CurrencyMultiply(NewTransaction.AmountInBaseCurrency, TExchangeRateCache.GetDailyExchangeRate( FMainDS.ALedger[0].BaseCurrency, FMainDS.ALedger[0].IntlCurrency, NewTransaction.TransactionDate, false)); } } while (!dataFile.EndOfStream); // create a balancing transaction; not sure if this is needed at all??? if (Convert.ToDecimal(sumCredits - sumDebits) != 0) { GLBatchTDSATransactionRow BalancingTransaction = FMainDS.ATransaction.NewRowTyped(true); FMyForm.GetTransactionsControl().NewRowManual(ref BalancingTransaction, ARefJournalRow); FMainDS.ATransaction.Rows.Add(BalancingTransaction); BalancingTransaction.TransactionDate = ALatestTransactionDate; BalancingTransaction.DebitCreditIndicator = true; BalancingTransaction.TransactionAmount = sumCredits - sumDebits; if (BalancingTransaction.TransactionAmount < 0) { BalancingTransaction.TransactionAmount *= -1; BalancingTransaction.DebitCreditIndicator = !BalancingTransaction.DebitCreditIndicator; } if (BalancingTransaction.DebitCreditIndicator) { sumDebits += BalancingTransaction.TransactionAmount; } else { sumCredits += BalancingTransaction.TransactionAmount; } BalancingTransaction.AmountInIntlCurrency = GLRoutines.CurrencyMultiply(BalancingTransaction.TransactionAmount, TExchangeRateCache.GetDailyExchangeRate( ARefJournalRow.TransactionCurrency, FMainDS.ALedger[0].IntlCurrency, BalancingTransaction.TransactionDate, false)); BalancingTransaction.AmountInBaseCurrency = GLRoutines.CurrencyMultiply(BalancingTransaction.TransactionAmount, TExchangeRateCache.GetDailyExchangeRate( ARefJournalRow.TransactionCurrency, FMainDS.ALedger[0].BaseCurrency, BalancingTransaction.TransactionDate, false)); BalancingTransaction.Narrative = Catalog.GetString("Automatically generated balancing transaction"); BalancingTransaction.CostCentreCode = TXMLParser.GetAttribute(ARootNode, "CashCostCentre"); BalancingTransaction.AccountCode = TXMLParser.GetAttribute(ARootNode, "CashAccount"); } ARefJournalRow.JournalCreditTotal = sumCredits; ARefJournalRow.JournalDebitTotal = sumDebits; ARefJournalRow.JournalDescription = Path.GetFileNameWithoutExtension(ADataFilename); ABatchRow RefBatch = (ABatchRow)FMainDS.ABatch.Rows[FMainDS.ABatch.Rows.Count - 1]; RefBatch.BatchCreditTotal = sumCredits; RefBatch.BatchDebitTotal = sumDebits; // todo RefBatch.BatchControlTotal = sumCredits - sumDebits; // csv ! }
private ATransactionRow AddATransaction(string AAccount, string ACostCenter, string ANarrativeMessage, string AReferenceMessage, bool AIsDebit, decimal AAmountBaseCurrency, decimal AAmountForeignCurrency, bool ATransActionIsInForeign) { if (!blnReadyForTransaction) { EVerificationException terminate = new EVerificationException( Catalog.GetString("You have to add a journal before you can add a transaction!")); terminate.Context = "Common Accounting"; terminate.ErrorCode = "GL.CAT.06"; throw terminate; } if (AAccount.Trim().Length == 0) { throw new Exception("Empty Account Code in transaction with Cost Centre " + ACostCenter); } if (FForeignJournal) { if (ATransActionIsInForeign) { TAccountInfo accountCheck = new TAccountInfo(FLedgerInfo.LedgerNumber, AAccount); if (accountCheck.IsValid) { if (accountCheck.ForeignCurrencyFlag) { if (!accountCheck.ForeignCurrencyCode.Equals(this.FForeignCurrencyInfo.CurrencyCode)) { // This is a difficult error situation. Someone wants to account // JYP-Currencies on a GBP-account in an EUR ledger. string strMessage = Catalog.GetString("The ledger is defined in {0}, the account {1} is defined in " + "{2} and you want to account something in {3}?"); strMessage = String.Format(strMessage, FLedgerInfo.BaseCurrency, AAccount, accountCheck.ForeignCurrencyCode, FForeignCurrencyInfo.CurrencyCode); EVerificationException terminate = new EVerificationException(strMessage); terminate.Context = "Common Accounting"; terminate.ErrorCode = "GL.CAT.07"; throw terminate; } } } } } ATransactionRow transRow = null; transRow = FBatchTDS.ATransaction.NewRowTyped(); transRow.LedgerNumber = FJournalRow.LedgerNumber; transRow.BatchNumber = FJournalRow.BatchNumber; transRow.JournalNumber = FJournalRow.JournalNumber; transRow.TransactionNumber = ++FJournalRow.LastTransactionNumber; transRow.AccountCode = AAccount; transRow.CostCentreCode = ACostCenter; transRow.Narrative = ANarrativeMessage; transRow.Reference = AReferenceMessage; transRow.DebitCreditIndicator = AIsDebit; transRow.AmountInBaseCurrency = AAmountBaseCurrency; transRow.TransactionAmount = (ATransActionIsInForeign) ? AAmountForeignCurrency : AAmountBaseCurrency; // // The International currency calculation is changed to "Base -> International", because it's likely // we won't have a "Transaction -> International" conversion rate defined. // transRow.AmountInIntlCurrency = GLRoutines.CurrencyMultiply(transRow.AmountInBaseCurrency, TExchangeRateTools.GetDailyExchangeRate( FLedgerInfo.BaseCurrency, FLedgerInfo.InternationalCurrency, transRow.TransactionDate)); transRow.TransactionDate = FBatchRow.DateEffective; FBatchTDS.ATransaction.Rows.Add(transRow); if (AIsDebit) { FJournalRow.JournalDebitTotal += AAmountBaseCurrency; } else { FJournalRow.JournalCreditTotal += AAmountBaseCurrency; } return(transRow); }