public static Int32 CreateGLBatch(BankImportTDS AMainDS, Int32 ALedgerNumber, Int32 AStatementKey, Int32 AGLBatchNumber, out TVerificationResultCollection AVerificationResult) { AMainDS.AEpTransaction.DefaultView.RowFilter = String.Format("{0}={1}", AEpTransactionTable.GetStatementKeyDBName(), AStatementKey); AMainDS.AEpStatement.DefaultView.RowFilter = String.Format("{0}={1}", AEpStatementTable.GetStatementKeyDBName(), AStatementKey); AEpStatementRow stmt = (AEpStatementRow)AMainDS.AEpStatement.DefaultView[0].Row; AVerificationResult = null; Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber; TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); if (!TFinancialYear.IsValidPostingPeriod(ALedgerNumber, stmt.Date, out DateEffectivePeriodNumber, out DateEffectiveYearNumber, Transaction)) { string msg = String.Format(Catalog.GetString("Cannot create a GL batch for date {0} since it is not in an open period of the ledger."), stmt.Date.ToShortDateString()); TLogging.Log(msg); AVerificationResult = new TVerificationResultCollection(); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating GL Batch"), msg, TResultSeverity.Resv_Critical)); DBAccess.GDBAccessObj.RollbackTransaction(); return(-1); } Int32 BatchYear, BatchPeriod; // if DateEffective is outside the range of open periods, use the most fitting date DateTime DateEffective = stmt.Date; TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref DateEffective, out BatchYear, out BatchPeriod, Transaction, true); ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); DBAccess.GDBAccessObj.RollbackTransaction(); GLBatchTDS GLDS = TGLTransactionWebConnector.CreateABatch(ALedgerNumber); ABatchRow glbatchRow = GLDS.ABatch[0]; glbatchRow.BatchPeriod = BatchPeriod; glbatchRow.DateEffective = DateEffective; glbatchRow.BatchDescription = String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString()); decimal HashTotal = 0.0M; decimal DebitTotal = 0.0M; decimal CreditTotal = 0.0M; // TODO: support several journals // TODO: support several currencies, support other currencies than the base currency AJournalRow gljournalRow = GLDS.AJournal.NewRowTyped(); gljournalRow.LedgerNumber = glbatchRow.LedgerNumber; gljournalRow.BatchNumber = glbatchRow.BatchNumber; gljournalRow.JournalNumber = glbatchRow.LastJournal + 1; gljournalRow.TransactionCurrency = LedgerTable[0].BaseCurrency; glbatchRow.LastJournal++; gljournalRow.JournalPeriod = glbatchRow.BatchPeriod; gljournalRow.DateEffective = glbatchRow.DateEffective; gljournalRow.JournalDescription = glbatchRow.BatchDescription; gljournalRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString(); gljournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString(); gljournalRow.ExchangeRateToBase = 1.0m; GLDS.AJournal.Rows.Add(gljournalRow); foreach (DataRowView dv in AMainDS.AEpTransaction.DefaultView) { AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row; DataView v = AMainDS.AEpMatch.DefaultView; v.RowFilter = AEpMatchTable.GetActionDBName() + " = '" + MFinanceConstants.BANK_STMT_STATUS_MATCHED_GL + "' and " + AEpMatchTable.GetMatchTextDBName() + " = '" + transactionRow.MatchText + "'"; if (v.Count > 0) { AEpMatchRow match = (AEpMatchRow)v[0].Row; ATransactionRow trans = GLDS.ATransaction.NewRowTyped(); trans.LedgerNumber = glbatchRow.LedgerNumber; trans.BatchNumber = glbatchRow.BatchNumber; trans.JournalNumber = gljournalRow.JournalNumber; trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1; trans.AccountCode = match.AccountCode; trans.CostCentreCode = match.CostCentreCode; trans.Reference = match.Reference; trans.Narrative = match.Narrative; trans.TransactionDate = transactionRow.DateEffective; if (transactionRow.TransactionAmount < 0) { trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount; trans.TransactionAmount = -1 * transactionRow.TransactionAmount; trans.DebitCreditIndicator = true; DebitTotal += trans.AmountInBaseCurrency; } else { trans.AmountInBaseCurrency = transactionRow.TransactionAmount; trans.TransactionAmount = transactionRow.TransactionAmount; trans.DebitCreditIndicator = false; CreditTotal += trans.AmountInBaseCurrency; } GLDS.ATransaction.Rows.Add(trans); gljournalRow.LastTransactionNumber++; // add one transaction for the bank as well trans = GLDS.ATransaction.NewRowTyped(); trans.LedgerNumber = glbatchRow.LedgerNumber; trans.BatchNumber = glbatchRow.BatchNumber; trans.JournalNumber = gljournalRow.JournalNumber; trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1; trans.AccountCode = stmt.BankAccountCode; trans.CostCentreCode = TLedgerInfo.GetStandardCostCentre(ALedgerNumber); trans.Reference = match.Reference; trans.Narrative = match.Narrative; trans.TransactionDate = transactionRow.DateEffective; if (transactionRow.TransactionAmount < 0) { trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount; trans.TransactionAmount = -1 * transactionRow.TransactionAmount; trans.DebitCreditIndicator = false; CreditTotal += trans.AmountInBaseCurrency; } else { trans.AmountInBaseCurrency = transactionRow.TransactionAmount; trans.TransactionAmount = transactionRow.TransactionAmount; trans.DebitCreditIndicator = true; DebitTotal += trans.AmountInBaseCurrency; } GLDS.ATransaction.Rows.Add(trans); gljournalRow.LastTransactionNumber++; } } gljournalRow.JournalDebitTotal = DebitTotal; gljournalRow.JournalCreditTotal = CreditTotal; glbatchRow.BatchDebitTotal = DebitTotal; glbatchRow.BatchCreditTotal = CreditTotal; glbatchRow.BatchControlTotal = HashTotal; TVerificationResultCollection VerificationResult; TSubmitChangesResult result = TGLTransactionWebConnector.SaveGLBatchTDS(ref GLDS, out VerificationResult); if (result == TSubmitChangesResult.scrOK) { return(glbatchRow.BatchNumber); } TLogging.Log("Problems storing GL Batch"); return(-1); }
public static Int32 CreateGiftBatch( Int32 ALedgerNumber, Int32 AStatementKey, Int32 AGiftBatchNumber, out TVerificationResultCollection AVerificationResult) { BankImportTDS MainDS = GetBankStatementTransactionsAndMatches(AStatementKey, ALedgerNumber); string MyClientID = DomainManager.GClientID.ToString(); TProgressTracker.InitProgressTracker(MyClientID, Catalog.GetString("Creating gift batch"), MainDS.AEpTransaction.DefaultView.Count + 10); AVerificationResult = new TVerificationResultCollection(); MainDS.AEpTransaction.DefaultView.RowFilter = String.Format("{0}={1}", AEpTransactionTable.GetStatementKeyDBName(), AStatementKey); MainDS.AEpStatement.DefaultView.RowFilter = String.Format("{0}={1}", AEpStatementTable.GetStatementKeyDBName(), AStatementKey); AEpStatementRow stmt = (AEpStatementRow)MainDS.AEpStatement.DefaultView[0].Row; // TODO: optional: use the preselected gift batch, AGiftBatchNumber Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber; DateTime BatchDateEffective = stmt.Date; TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); if (!TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref BatchDateEffective, out DateEffectiveYearNumber, out DateEffectivePeriodNumber, Transaction, true)) { // just use the latest possible date string msg = String.Format(Catalog.GetString("Date {0} is not in an open period of the ledger, using date {1} instead for the gift batch."), stmt.Date.ToShortDateString(), BatchDateEffective.ToShortDateString()); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Info)); } ACostCentreAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); MainDS.AEpMatch.DefaultView.Sort = AEpMatchTable.GetActionDBName() + ", " + AEpMatchTable.GetMatchTextDBName(); if (MainDS.AEpTransaction.DefaultView.Count == 0) { AVerificationResult.Add(new TVerificationResult( Catalog.GetString("Creating Gift Batch"), String.Format(Catalog.GetString("There are no transactions for statement #{0}."), AStatementKey), TResultSeverity.Resv_Info)); return(-1); } foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView) { AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row; DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] { MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT, transactionRow.MatchText }); if (matches.Length > 0) { AEpMatchRow match = (AEpMatchRow)matches[0].Row; if (match.IsDonorKeyNull() || (match.DonorKey == 0)) { string msg = String.Format(Catalog.GetString("Cannot create a gift for transaction {0} since there is no valid donor."), transactionRow.Description); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Critical)); DBAccess.GDBAccessObj.RollbackTransaction(); return(-1); } } } string MatchedGiftReference = stmt.Filename; if (!stmt.IsBankAccountKeyNull()) { string sqlGetBankSortCode = "SELECT bank.p_branch_code_c " + "FROM PUB_p_banking_details details, PUB_p_bank bank " + "WHERE details.p_banking_details_key_i = ?" + "AND details.p_bank_key_n = bank.p_partner_key_n"; OdbcParameter param = new OdbcParameter("detailkey", OdbcType.Int); param.Value = stmt.BankAccountKey; PBankTable bankTable = new PBankTable(); DBAccess.GDBAccessObj.SelectDT(bankTable, sqlGetBankSortCode, Transaction, new OdbcParameter[] { param }, 0, 0); MatchedGiftReference = bankTable[0].BranchCode + " " + stmt.Date.Day.ToString(); } DBAccess.GDBAccessObj.RollbackTransaction(); GiftBatchTDS GiftDS = TGiftTransactionWebConnector.CreateAGiftBatch( ALedgerNumber, BatchDateEffective, String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString())); AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0]; giftbatchRow.BankAccountCode = stmt.BankAccountCode; decimal HashTotal = 0.0M; MainDS.AEpTransaction.DefaultView.Sort = AEpTransactionTable.GetNumberOnPaperStatementDBName(); MainDS.AEpMatch.DefaultView.RowFilter = String.Empty; MainDS.AEpMatch.DefaultView.Sort = AEpMatchTable.GetActionDBName() + ", " + AEpMatchTable.GetMatchTextDBName(); int counter = 5; foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView) { TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("Preparing the gifts"), counter++); AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row; DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] { MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT, transactionRow.MatchText }); if (matches.Length > 0) { AEpMatchRow match = (AEpMatchRow)matches[0].Row; AGiftRow gift = GiftDS.AGift.NewRowTyped(); gift.LedgerNumber = giftbatchRow.LedgerNumber; gift.BatchNumber = giftbatchRow.BatchNumber; gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1; gift.DonorKey = match.DonorKey; gift.DateEntered = transactionRow.DateEffective; gift.Reference = MatchedGiftReference; GiftDS.AGift.Rows.Add(gift); giftbatchRow.LastGiftNumber++; foreach (DataRowView r in matches) { match = (AEpMatchRow)r.Row; AGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped(); detail.LedgerNumber = gift.LedgerNumber; detail.BatchNumber = gift.BatchNumber; detail.GiftTransactionNumber = gift.GiftTransactionNumber; detail.DetailNumber = gift.LastDetailNumber + 1; gift.LastDetailNumber++; detail.GiftTransactionAmount = match.GiftTransactionAmount; detail.GiftAmount = match.GiftTransactionAmount; HashTotal += match.GiftTransactionAmount; detail.MotivationGroupCode = match.MotivationGroupCode; detail.MotivationDetailCode = match.MotivationDetailCode; // do not use the description in comment one, because that could show up on the gift receipt??? // detail.GiftCommentOne = transactionRow.Description; detail.CommentOneType = MFinanceConstants.GIFT_COMMENT_TYPE_BOTH; detail.CostCentreCode = match.CostCentreCode; detail.RecipientKey = match.RecipientKey; detail.RecipientLedgerNumber = match.RecipientLedgerNumber; AMotivationDetailRow motivation = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find( new object[] { ALedgerNumber, detail.MotivationGroupCode, detail.MotivationDetailCode }); if (motivation == null) { AVerificationResult.Add(new TVerificationResult( String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description), String.Format(Catalog.GetString("Cannot find motivation group '{0}' and motivation detail '{1}'"), detail.MotivationGroupCode, detail.MotivationDetailCode), TResultSeverity.Resv_Critical)); } if (detail.CostCentreCode.Length == 0) { // try to retrieve the current costcentre for this recipient if (detail.RecipientKey != 0) { detail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(detail.RecipientKey); detail.CostCentreCode = TGiftTransactionWebConnector.IdentifyPartnerCostCentre(detail.LedgerNumber, detail.RecipientLedgerNumber); } else { if (motivation != null) { detail.CostCentreCode = motivation.CostCentreCode; } } } // check for active cost centre ACostCentreRow costcentre = (ACostCentreRow)MainDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, detail.CostCentreCode }); if ((costcentre == null) || !costcentre.CostCentreActiveFlag) { AVerificationResult.Add(new TVerificationResult( String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description), Catalog.GetString("Invalid or inactive cost centre"), TResultSeverity.Resv_Critical)); } GiftDS.AGiftDetail.Rows.Add(detail); } } } TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("Submit to database"), counter++); if (AVerificationResult.HasCriticalErrors) { return(-1); } giftbatchRow.HashTotal = HashTotal; giftbatchRow.BatchTotal = HashTotal; // do not overwrite the parameter, because there might be the hint for a different gift batch date TVerificationResultCollection VerificationResultSubmitChanges; TSubmitChangesResult result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS, out VerificationResultSubmitChanges); TProgressTracker.FinishJob(MyClientID); if (result == TSubmitChangesResult.scrOK) { return(giftbatchRow.BatchNumber); } return(-1); }
/// <summary> /// dump unmatched gifts or other transactions to a HTML table for printing /// </summary> private static string PrintHTML( AEpStatementRow ACurrentStatement, DataView AEpTransactions, AEpMatchTable AMatches, string ATitle, string ALetterTemplateFilename) { if ((ALetterTemplateFilename.Length == 0) || !File.Exists(ALetterTemplateFilename)) { OpenFileDialog DialogOpen = new OpenFileDialog(); DialogOpen.Filter = "Report template (*.html)|*.html"; DialogOpen.RestoreDirectory = true; DialogOpen.Title = "Open Report Template"; if (DialogOpen.ShowDialog() == DialogResult.OK) { ALetterTemplateFilename = DialogOpen.FileName; } } // message body from HTML template StreamReader reader = new StreamReader(ALetterTemplateFilename); string msg = reader.ReadToEnd(); reader.Close(); msg = msg.Replace("#TITLE", ATitle); msg = msg.Replace("#PRINTDATE", DateTime.Now.ToShortDateString()); if (!ACurrentStatement.IsIdFromBankNull()) { msg = msg.Replace("#STATEMENTNR", ACurrentStatement.IdFromBank); } if (!ACurrentStatement.IsStartBalanceNull()) { msg = msg.Replace("#STARTBALANCE", String.Format("{0:N}", ACurrentStatement.StartBalance)); } if (!ACurrentStatement.IsEndBalanceNull()) { msg = msg.Replace("#ENDBALANCE", String.Format("{0:N}", ACurrentStatement.EndBalance)); } // recognise detail lines automatically string RowTemplate; msg = TPrinterHtml.GetTableRow(msg, "#NRONSTATEMENT", out RowTemplate); string rowTexts = ""; BankImportTDSAEpTransactionRow row = null; AEpTransactions.Sort = BankImportTDSAEpTransactionTable.GetNumberOnPaperStatementDBName(); Decimal Sum = 0.0m; Int32 NumberPrinted = 0; DataView MatchesByMatchText = new DataView(AMatches, string.Empty, AEpMatchTable.GetMatchTextDBName(), DataViewRowState.CurrentRows); string thinLine = "<font size=\"-3\">-------------------------------------------------------------------------<br/></font>"; foreach (DataRowView rv in AEpTransactions) { row = (BankImportTDSAEpTransactionRow)rv.Row; string rowToPrint = RowTemplate; // short description, remove all SEPA stuff string ShortDescription = RemoveSEPAText(row.Description); rowToPrint = rowToPrint.Replace("#NAME", row.AccountName); rowToPrint = rowToPrint.Replace("#DESCRIPTION", row.Description); rowToPrint = rowToPrint.Replace("#SHORTDESCRIPTION", ShortDescription); string RecipientDescription = string.Empty; DataRowView[] matches = MatchesByMatchText.FindRows(row.MatchText); AEpMatchRow match = null; foreach (DataRowView rvMatch in matches) { match = (AEpMatchRow)rvMatch.Row; if (RecipientDescription.Length > 0) { RecipientDescription += "<br/>"; } if (!match.IsRecipientKeyNull() && (match.RecipientKey > 0)) { RecipientDescription += match.RecipientKey.ToString() + " "; } RecipientDescription += match.RecipientShortName; } if (RecipientDescription.Trim().Length > 0) { rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTIONUNMATCHED", string.Empty); rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTION", "<br/>" + thinLine + RecipientDescription); } else { // extra space for unmatched gifts rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTIONUNMATCHED", "<br/><br/>"); rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTION", string.Empty); } if ((match != null) && !match.IsDonorKeyNull() && (match.DonorKey > 0)) { string DonorDescription = "<br/>" + thinLine + match.DonorKey.ToString() + " " + match.DonorShortName; rowToPrint = rowToPrint.Replace("#DONORDESCRIPTION", DonorDescription); rowToPrint = rowToPrint.Replace("#DONORKEY", StringHelper.PartnerKeyToStr(match.DonorKey)); rowToPrint = rowToPrint.Replace("#DONORNAMEORDESCRIPTION", match.DonorShortName); } else { rowToPrint = rowToPrint.Replace("#DONORDESCRIPTION", string.Empty); rowToPrint = rowToPrint.Replace("#DONORKEY", string.Empty); rowToPrint = rowToPrint.Replace("#DONORNAMEORDESCRIPTION", row.AccountName); } rowTexts += rowToPrint. Replace("#NRONSTATEMENT", row.NumberOnPaperStatement.ToString()). Replace("#AMOUNT", String.Format("{0:C}", row.TransactionAmount)). Replace("#IBANANDBIC", row.IsIbanNull() ? string.Empty : "<br/>" + row.Iban + "<br/>" + row.Bic). Replace("#IBAN", row.Iban). Replace("#BIC", row.Bic). Replace("#ACCOUNTNUMBER", row.BankAccountNumber). Replace("#BANKSORTCODE", row.BranchCode); Sum += Convert.ToDecimal(row.TransactionAmount); NumberPrinted++; } Sum = Math.Round(Sum, 2); msg = msg.Replace("#ROWTEMPLATE", rowTexts); msg = msg.Replace("#TOTALAMOUNT", String.Format("{0:C}", Sum)); msg = msg.Replace("#TOTALNUMBER", NumberPrinted.ToString()); return msg; }
public static BankImportTDS GetBankStatementTransactionsAndMatches(Int32 AStatementKey, Int32 ALedgerNumber) { TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); BankImportTDS ResultDataset = new BankImportTDS(); string MyClientID = DomainManager.GClientID.ToString(); TProgressTracker.InitProgressTracker(MyClientID, Catalog.GetString("Load Bank Statement"), 100.0m); TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("loading statement"), 0); try { AEpStatementAccess.LoadByPrimaryKey(ResultDataset, AStatementKey, Transaction); if (ResultDataset.AEpStatement[0].BankAccountCode.Length == 0) { throw new Exception("Loading Bank Statement: Bank Account must not be empty"); } ACostCentreAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction); AMotivationDetailAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction); AEpTransactionAccess.LoadViaAEpStatement(ResultDataset, AStatementKey, Transaction); BankImportTDS TempDataset = new BankImportTDS(); AEpTransactionAccess.LoadViaAEpStatement(TempDataset, AStatementKey, Transaction); AEpMatchAccess.LoadViaALedger(TempDataset, ResultDataset.AEpStatement[0].LedgerNumber, Transaction); // load all bankingdetails and partner shortnames related to this statement string sqlLoadPartnerByBankAccount = "SELECT DISTINCT p.p_partner_key_n AS PartnerKey, " + "p.p_partner_short_name_c AS ShortName, " + "t.p_branch_code_c AS BranchCode, " + "t.a_bank_account_number_c AS BankAccountNumber " + "FROM PUB_a_ep_transaction t, PUB_p_banking_details bd, PUB_p_bank b, PUB_p_partner_banking_details pbd, PUB_p_partner p " + "WHERE t.a_statement_key_i = " + AStatementKey.ToString() + " " + "AND bd.p_bank_account_number_c = t.a_bank_account_number_c " + "AND b.p_partner_key_n = bd.p_bank_key_n " + "AND b.p_branch_code_c = t.p_branch_code_c " + "AND pbd.p_banking_details_key_i = bd.p_banking_details_key_i " + "AND p.p_partner_key_n = pbd.p_partner_key_n"; DataTable PartnerByBankAccount = DBAccess.GDBAccessObj.SelectDT(sqlLoadPartnerByBankAccount, "partnerByBankAccount", Transaction); PartnerByBankAccount.DefaultView.Sort = "BranchCode, BankAccountNumber"; // get all recipients that have been merged string sqlGetMergedRecipients = string.Format( "SELECT DISTINCT p.p_partner_key_n AS PartnerKey, p.p_status_code_c AS StatusCode FROM PUB_a_ep_match m, PUB_p_partner p " + "WHERE (m.p_recipient_key_n = p.p_partner_key_n OR m.p_donor_key_n = p.p_partner_key_n) AND p.p_status_code_c = '{0}'", MPartnerConstants.PARTNERSTATUS_MERGED); DataTable MergedPartners = DBAccess.GDBAccessObj.SelectDT(sqlGetMergedRecipients, "mergedPartners", Transaction); MergedPartners.DefaultView.Sort = "PartnerKey"; DBAccess.GDBAccessObj.RollbackTransaction(); string BankAccountCode = ResultDataset.AEpStatement[0].BankAccountCode; TempDataset.AEpMatch.DefaultView.Sort = AEpMatchTable.GetMatchTextDBName(); SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>(); int count = 0; // load the matches or create new matches foreach (BankImportTDSAEpTransactionRow row in ResultDataset.AEpTransaction.Rows) { TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("finding matches") + " " + count + "/" + ResultDataset.AEpTransaction.Rows.Count.ToString(), 10.0m + (count * 80.0m / ResultDataset.AEpTransaction.Rows.Count)); count++; BankImportTDSAEpTransactionRow tempTransactionRow = (BankImportTDSAEpTransactionRow)TempDataset.AEpTransaction.Rows.Find( new object[] { row.StatementKey, row.Order, row.DetailKey }); // find a match with the same match text, or create a new one if (row.IsMatchTextNull() || (row.MatchText.Length == 0) || !row.MatchText.StartsWith(BankAccountCode)) { row.MatchText = TBankImportMatching.CalculateMatchText(BankAccountCode, row); tempTransactionRow.MatchText = row.MatchText; } DataRowView[] matches = TempDataset.AEpMatch.DefaultView.FindRows(row.MatchText); if (matches.Length > 0) { Decimal sum = 0.0m; // update the recent date foreach (DataRowView rv in matches) { AEpMatchRow r = (AEpMatchRow)rv.Row; sum += r.GiftTransactionAmount; // check if the recipient key is still valid. could be that they have married, and merged into another family record if ((r.RecipientKey != 0) && (MergedPartners.DefaultView.FindRows(r.RecipientKey).Length > 0)) { TLogging.LogAtLevel(1, "partner has been merged: " + r.RecipientKey.ToString()); r.RecipientKey = 0; r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; } // check if the donor key is still valid. could be that they have married, and merged into another family record if ((r.DonorKey != 0) && (MergedPartners.DefaultView.FindRows(r.DonorKey).Length > 0)) { TLogging.LogAtLevel(1, "partner has been merged: " + r.DonorKey.ToString()); r.DonorKey = 0; r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; } if (r.RecentMatch < row.DateEffective) { r.RecentMatch = row.DateEffective; } // do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time row.MatchAction = r.Action; if (r.IsDonorKeyNull() || (r.DonorKey <= 0)) { FindDonorByAccountNumber(r, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber); } } if (sum != row.TransactionAmount) { TLogging.Log("we should drop this match since the total is wrong: " + row.Description); row.MatchAction = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; foreach (DataRowView rv in matches) { AEpMatchRow r = (AEpMatchRow)rv.Row; r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; } } } else if (!MatchesToAddLater.ContainsKey(row.MatchText)) { // create new match AEpMatchRow tempRow = TempDataset.AEpMatch.NewRowTyped(true); tempRow.EpMatchKey = (TempDataset.AEpMatch.Count + MatchesToAddLater.Count + 1) * -1; tempRow.Detail = 0; tempRow.MatchText = row.MatchText; tempRow.LedgerNumber = ALedgerNumber; tempRow.GiftTransactionAmount = row.TransactionAmount; tempRow.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED; FindDonorByAccountNumber(tempRow, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber); #if disabled // fuzzy search for the partner. only return if unique result string sql = "SELECT p_partner_key_n, p_partner_short_name_c FROM p_partner WHERE p_partner_short_name_c LIKE '{0}%' OR p_partner_short_name_c LIKE '{1}%'"; string[] names = row.AccountName.Split(new char[] { ' ' }); if (names.Length > 1) { string optionShortName1 = names[0] + ", " + names[1]; string optionShortName2 = names[1] + ", " + names[0]; DataTable partner = DBAccess.GDBAccessObj.SelectDT(String.Format(sql, optionShortName1, optionShortName2), "partner", Transaction); if (partner.Rows.Count == 1) { tempRow.DonorKey = Convert.ToInt64(partner.Rows[0][0]); } } #endif MatchesToAddLater.Add(tempRow.MatchText, tempRow); // do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time row.MatchAction = tempRow.Action; } } // for speed reasons, add the new rows after clearing the sort on the view TempDataset.AEpMatch.DefaultView.Sort = string.Empty; foreach (AEpMatchRow m in MatchesToAddLater.Values) { TempDataset.AEpMatch.Rows.Add(m); } TProgressTracker.SetCurrentState(MyClientID, Catalog.GetString("save matches"), 90.0m); TempDataset.ThrowAwayAfterSubmitChanges = true; // only store a_ep_transactions and a_ep_matches, but without additional typed fields (ie MatchAction) BankImportTDSAccess.SubmitChanges(TempDataset.GetChangesTyped(true)); } catch (Exception e) { TLogging.Log(e.GetType().ToString() + " in BankImport, GetBankStatementTransactionsAndMatches; " + e.Message); TLogging.Log(e.StackTrace); DBAccess.GDBAccessObj.RollbackTransaction(); throw; } // drop all matches that do not occur on this statement ResultDataset.AEpMatch.Clear(); // reloading is faster than deleting all matches that are not needed string sqlLoadMatchesOfStatement = "SELECT DISTINCT m.* FROM PUB_a_ep_match m, PUB_a_ep_transaction t WHERE t.a_statement_key_i = ? AND m.a_ledger_number_i = ? AND m.a_match_text_c = t.a_match_text_c"; OdbcParameter param = new OdbcParameter("statementkey", OdbcType.Int); param.Value = AStatementKey; OdbcParameter paramLedgerNumber = new OdbcParameter("ledgerNumber", OdbcType.Int); paramLedgerNumber.Value = ALedgerNumber; DBAccess.GDBAccessObj.SelectDT(ResultDataset.AEpMatch, sqlLoadMatchesOfStatement, null, new OdbcParameter[] { param, paramLedgerNumber }, -1, -1); // update the custom field for cost centre name for each match foreach (BankImportTDSAEpMatchRow row in ResultDataset.AEpMatch.Rows) { ACostCentreRow ccRow = (ACostCentreRow)ResultDataset.ACostCentre.Rows.Find(new object[] { row.LedgerNumber, row.CostCentreCode }); if (ccRow != null) { row.CostCentreName = ccRow.CostCentreName; } } // remove all rows that we do not need on the client side ResultDataset.AGiftDetail.Clear(); ResultDataset.AMotivationDetail.Clear(); ResultDataset.ACostCentre.Clear(); ResultDataset.AcceptChanges(); if (TLogging.DebugLevel > 0) { int CountMatched = 0; foreach (BankImportTDSAEpTransactionRow transaction in ResultDataset.AEpTransaction.Rows) { if (!transaction.IsMatchActionNull() && (transaction.MatchAction != MFinanceConstants.BANK_STMT_STATUS_UNMATCHED)) { CountMatched++; } } TLogging.Log( "Loading bank statement: matched: " + CountMatched.ToString() + " of " + ResultDataset.AEpTransaction.Rows.Count.ToString()); } TProgressTracker.FinishJob(MyClientID); return(ResultDataset); }
/// <summary> /// store historic Gift matches /// </summary> private static void StoreCurrentMatches(BankImportTDS AMatchDS, string ABankAccountCode) { DataView GiftDetailView = new DataView( AMatchDS.AGiftDetail, string.Empty, BankImportTDSAGiftDetailTable.GetGiftTransactionNumberDBName() + "," + BankImportTDSAGiftDetailTable.GetDetailNumberDBName(), DataViewRowState.CurrentRows); SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>(); List <string> MatchesToDelete = new List <string>(); foreach (BankImportTDSAEpTransactionRow tr in AMatchDS.AEpTransaction.Rows) { // create a match text which uniquely identifies this transaction string MatchText = CalculateMatchText(ABankAccountCode, tr); // delete existing matches MatchesToDelete.Add(MatchText); if (tr.MatchAction != MFinanceConstants.BANK_STMT_STATUS_MATCHED) { continue; } // get the gift details assigned to this transaction StringCollection GiftDetailNumbers = StringHelper.GetCSVList(tr.GiftDetailNumbers, ",", false); foreach (string strDetailNumber in GiftDetailNumbers) { DataRowView[] FilteredGiftDetails = GiftDetailView.FindRows( new object[] { tr.GiftTransactionNumber, Convert.ToInt32(strDetailNumber) }); // add new matches // do not assign tr.EpMatchKey, because we cannot delete the old matches then CreateNewMatches( AMatchDS, (BankImportTDSAGiftDetailRow)FilteredGiftDetails[0].Row, MatchText, MatchesToAddLater); } } DataView MatchesByText = new DataView( AMatchDS.AEpMatch, string.Empty, AEpMatchTable.GetMatchTextDBName(), DataViewRowState.CurrentRows); foreach (string MatchToDelete in MatchesToDelete) { DataRowView[] MatchesToDeleteRv = MatchesByText.FindRows(MatchToDelete); foreach (DataRowView rv in MatchesToDeleteRv) { rv.Row.Delete(); } } MatchesByText.Sort = string.Empty; MatchesByText.RowFilter = string.Empty; // for speed reasons, add the new rows at the end foreach (AEpMatchRow m in MatchesToAddLater.Values) { AMatchDS.AEpMatch.Rows.Add(m); } AMatchDS.PBankingDetails.Clear(); AMatchDS.AGiftDetail.Clear(); AMatchDS.AGift.Clear(); AMatchDS.ThrowAwayAfterSubmitChanges = true; if (TLogging.DebugLevel > 0) { TLogging.Log("before submitchanges"); } BankImportTDSAccess.SubmitChanges(AMatchDS); if (TLogging.DebugLevel > 0) { TLogging.Log("after submitchanges"); } }