public static GiftBatchTDS LoadMotivationDetails(Int32 ALedgerNumber) { GiftBatchTDS MainDS = new GiftBatchTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction); AMotivationGroupAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AMotivationDetailFeeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); }); // Accept row changes here so that the Client gets 'unmodified' rows MainDS.AcceptChanges(); // Remove all Tables that were not filled with data before remoting them. MainDS.RemoveEmptyTables(); return(MainDS); }
public static GiftBatchTDS LoadMotivationDetails(Int32 ALedgerNumber, string AMotivationGroupCode) { GiftBatchTDS MainDS = new GiftBatchTDS(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadMotivationDetails"); db.ReadTransaction( ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction); if (AMotivationGroupCode.Length > 0) { AMotivationGroupAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, AMotivationGroupCode, Transaction); AMotivationDetailAccess.LoadViaAMotivationGroup(MainDS, ALedgerNumber, AMotivationGroupCode, Transaction); } else { AMotivationGroupAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); } AMotivationDetailFeeAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction); }); // Accept row changes here so that the Client gets 'unmodified' rows MainDS.AcceptChanges(); // Remove all Tables that were not filled with data before remoting them. MainDS.RemoveEmptyTables(); return(MainDS); }
/// <summary> /// Sets TaxDeductiblePct and uses it to calculate the tax deductibility amounts for a Gift Detail /// </summary> /// <param name="AGiftDetail">Calculated amounts are added to this row</param> /// <param name="ADateEntered"></param> /// <param name="ATransaction"></param> public static void SetDefaultTaxDeductibilityData( ref AGiftDetailRow AGiftDetail, DateTime ADateEntered, TDBTransaction ATransaction) { bool FoundTaxDeductiblePct = false; // if the gift it tax deductible if (AGiftDetail.TaxDeductible) { AMotivationDetailTable Tbl = AMotivationDetailAccess.LoadByPrimaryKey( AGiftDetail.LedgerNumber, AGiftDetail.MotivationGroupCode, AGiftDetail.MotivationDetailCode, ATransaction); AMotivationDetailRow MotivationDetailRow; Boolean HasTaxDeductibleAccountCode = false; if (Tbl.Rows.Count > 0) { MotivationDetailRow = Tbl[0]; HasTaxDeductibleAccountCode = !string.IsNullOrEmpty(MotivationDetailRow.TaxDeductibleAccountCode); } // if the gift's motivation detail has a tax-deductible account if (HasTaxDeductibleAccountCode) { // default pct is 100 AGiftDetail.TaxDeductiblePct = 100; FoundTaxDeductiblePct = true; PPartnerTaxDeductiblePctTable PartnerTaxDeductiblePctTable = PPartnerTaxDeductiblePctAccess.LoadViaPPartner(AGiftDetail.RecipientKey, ATransaction); // search for tax deductible pct for recipient foreach (PPartnerTaxDeductiblePctRow Row in PartnerTaxDeductiblePctTable.Rows) { if (Row.DateValidFrom <= ADateEntered) { AGiftDetail.TaxDeductiblePct = Row.PercentageTaxDeductible; break; } } } } // if a tax deductible pct is set for the recipient if (FoundTaxDeductiblePct) { // calculate TaxDeductibleAmount and NonDeductibleAmount for all three currencies TaxDeductibility.UpdateTaxDeductibiltyAmounts(ref AGiftDetail); } // if gift is not tax deductible or motivation detail does not hace a tax deductible account if (!AGiftDetail.TaxDeductible || !FoundTaxDeductiblePct) { AGiftDetail.TaxDeductiblePct = 0; AGiftDetail.NonDeductibleAmount = AGiftDetail.GiftTransactionAmount; AGiftDetail.NonDeductibleAmountBase = AGiftDetail.GiftAmount; AGiftDetail.NonDeductibleAmountIntl = AGiftDetail.GiftAmountIntl; } }
public void SpeedTestLoadIntoTypedTable() { TDBTransaction ReadTransaction = null; DateTime before = DateTime.Now; DateTime after = DateTime.Now; GiftBatchTDS ds = new GiftBatchTDS(); DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction( IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref ReadTransaction, delegate { string sql = "SELECT PUB_a_gift_detail.*, false AS AlreadyMatched, PUB_a_gift_batch.a_batch_status_c AS BatchStatus " + "FROM PUB_a_gift_batch, PUB_a_gift_detail " + "WHERE PUB_a_gift_detail.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i"; before = DateTime.Now; DataTable untyped = DBAccess.GDBAccessObj.SelectDT(sql, "test", ReadTransaction); after = DateTime.Now; TLogging.Log(String.Format("loading all {0} gift details into an untyped table took {1} milliseconds", untyped.Rows.Count, (after.Subtract(before)).TotalMilliseconds)); GiftBatchTDSAGiftDetailTable typed = new GiftBatchTDSAGiftDetailTable(); before = DateTime.Now; DBAccess.GDBAccessObj.SelectDT(typed, sql, ReadTransaction, new OdbcParameter[0], 0, 0); after = DateTime.Now; TLogging.Log(String.Format("loading all {0} gift details into a typed table took {1} milliseconds", typed.Rows.Count, (after.Subtract(before)).TotalMilliseconds)); AMotivationDetailAccess.LoadAll(ds, ReadTransaction); before = DateTime.Now; DBAccess.GDBAccessObj.Select(ds, sql, ds.AGiftDetail.TableName, ReadTransaction); after = DateTime.Now; }); TLogging.Log(String.Format("loading all {0} gift details into a typed dataset took {1} milliseconds", ds.AGiftDetail.Rows.Count, (after.Subtract(before)).TotalMilliseconds)); before = DateTime.Now; GiftBatchTDS ds2 = new GiftBatchTDS(); ds2.Merge(ds.AGiftDetail); after = DateTime.Now; TLogging.Log(String.Format("merging typed table into other dataset took {0} milliseconds", (after.Subtract(before)).TotalMilliseconds)); }
public static bool SetDefaultMotivationDetail(Int32 ALedgerNumber, String AMotivationGroupCode, String AMotivationDetailCode, out TVerificationResultCollection AVerificationResult) { AVerificationResult = new TVerificationResultCollection(); GiftBatchTDS MainDS = new GiftBatchTDS(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("SetDefaultMotivationDetail"); if (!(AMotivationGroupCode == String.Empty && AMotivationDetailCode == String.Empty)) { bool validDetail = true; db.ReadTransaction( ref Transaction, delegate { // is this a valid motivation group and detail combination? AMotivationDetailAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, AMotivationGroupCode, AMotivationDetailCode, Transaction); if (MainDS.AMotivationDetail.Rows.Count == 0) { validDetail = false; } else if (MainDS.AMotivationDetail[0].MotivationStatus == false) { validDetail = false; } }); if (!validDetail) { AVerificationResult.Add(new TVerificationResult("error", "invalid or inactive motivation detail", TResultSeverity.Resv_Critical)); return(false); } } // Store System Default for this ledger return(new TSystemDefaults().SetSystemDefault("DEFAULTMOTIVATION" + ALedgerNumber.ToString(), AMotivationGroupCode + "::" + AMotivationDetailCode)); }
public static Boolean GetMotivationGroupAndDetailForPartner(Int64 APartnerKey, ref String AMotivationGroup, ref String AMotivationDetail) { Boolean PartnerKeyIsValid = false; if (APartnerKey != 0) { string motivationGroup = MFinanceConstants.MOTIVATION_GROUP_GIFT; string motivationDetail = AMotivationDetail; TDBTransaction readTransaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref readTransaction, delegate { PPartnerTable myPPartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, readTransaction); if (myPPartnerTable.Rows.Count == 1) { // partnerKey is valid PartnerKeyIsValid = true; PPartnerRow partnerRow = myPPartnerTable[0]; // Change motivationDetail if PartnerClass is UNIT if (partnerRow.PartnerClass.Equals(MPartnerConstants.PARTNERCLASS_UNIT)) { // AND KEY-MIN bool KeyMinFound = false; // first check if a specific motivation detail is linked to this partner AMotivationDetailTable MotivationDetailTable = AMotivationDetailAccess.LoadViaPPartner(APartnerKey, readTransaction); if ((MotivationDetailTable != null) && (MotivationDetailTable.Rows.Count > 0)) { foreach (AMotivationDetailRow Row in MotivationDetailTable.Rows) { if (Row.MotivationStatus) { motivationGroup = MotivationDetailTable[0].MotivationGroupCode; motivationDetail = MotivationDetailTable[0].MotivationDetailCode; KeyMinFound = true; break; // Go with the first entry found. } } } if (!KeyMinFound) { // Is this is a key min, or a field? PUnitTable pUnitTable = PUnitAccess.LoadByPrimaryKey(APartnerKey, readTransaction); if (pUnitTable.Rows.Count == 1) { PUnitRow unitRow = pUnitTable[0]; switch (unitRow.UnitTypeCode) { case MPartnerConstants.UNIT_TYPE_AREA: case MPartnerConstants.UNIT_TYPE_FUND: case MPartnerConstants.UNIT_TYPE_FIELD: motivationDetail = MFinanceConstants.GROUP_DETAIL_FIELD; break; case MPartnerConstants.UNIT_TYPE_KEYMIN: motivationDetail = MFinanceConstants.GROUP_DETAIL_KEY_MIN; break; case MPartnerConstants.UNIT_TYPE_COUNTRY: case MPartnerConstants.UNIT_TYPE_CONFERENCE: case MPartnerConstants.UNIT_TYPE_OTHER: case MPartnerConstants.UNIT_TYPE_ROOT: case MPartnerConstants.UNIT_TYPE_TEAM: case MPartnerConstants.UNIT_TYPE_WORKING_GROUP: default: motivationDetail = MFinanceConstants.GROUP_DETAIL_SUPPORT; break; } } } } else { motivationDetail = MFinanceConstants.GROUP_DETAIL_SUPPORT; } } }); AMotivationGroup = motivationGroup; AMotivationDetail = motivationDetail; } return(PartnerKeyIsValid); }
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); }
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> /// Adds a duplicate Gift Detail (or reversed duplicate GiftDetail) to Gift. /// </summary> /// <param name="AMainDS"></param> /// <param name="AGift"></param> /// <param name="AOldGiftDetail"></param> /// <param name="AReversal">True for reverse or false for straight duplicate</param> /// <param name="ATransaction"></param> /// <param name="AFunction"></param> /// <param name="ANewPct"></param> /// <param name="AAutoCompleteComments"></param> /// <param name="AReversalCommentOne"></param> /// <param name="AReversalCommentTwo"></param> /// <param name="AReversalCommentThree"></param> /// <param name="AReversalCommentOneType"></param> /// <param name="AReversalCommentTwoType"></param> /// <param name="AReversalCommentThreeType"></param> /// <param name="AUpdateTaxDeductiblePctRecipients"></param> /// <param name="AGeneralFixedGiftDestination"></param> /// <param name="AFixedGiftDestination"></param> private static void AddDuplicateGiftDetailToGift(ref GiftBatchTDS AMainDS, ref AGiftRow AGift, AGiftDetailRow AOldGiftDetail, bool AReversal, TDBTransaction ATransaction, GiftAdjustmentFunctionEnum AFunction, Decimal ANewPct, bool AAutoCompleteComments = false, string AReversalCommentOne = "", string AReversalCommentTwo = "", string AReversalCommentThree = "", string AReversalCommentOneType = "", string AReversalCommentTwoType = "", string AReversalCommentThreeType = "", List <string[]> AUpdateTaxDeductiblePctRecipients = null, bool AGeneralFixedGiftDestination = false, List <string> AFixedGiftDestination = null ) { bool TaxDeductiblePercentageEnabled = new TSystemDefaults(ATransaction.DataBaseObj).GetBooleanDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, false); AGiftDetailRow giftDetail = AMainDS.AGiftDetail.NewRowTyped(true); DataUtilities.CopyAllColumnValuesWithoutPK(AOldGiftDetail, giftDetail); giftDetail.DetailNumber = AGift.LastDetailNumber + 1; AGift.LastDetailNumber++; giftDetail.LedgerNumber = AGift.LedgerNumber; giftDetail.BatchNumber = AGift.BatchNumber; giftDetail.GiftTransactionNumber = AGift.GiftTransactionNumber; giftDetail.IchNumber = 0; decimal signum = (AReversal) ? -1 : 1; giftDetail.GiftTransactionAmount = signum * AOldGiftDetail.GiftTransactionAmount; giftDetail.GiftAmount = signum * AOldGiftDetail.GiftAmount; giftDetail.GiftAmountIntl = signum * AOldGiftDetail.GiftAmountIntl; if (TaxDeductiblePercentageEnabled) { if (!AReversal && AFunction.Equals(GiftAdjustmentFunctionEnum.TaxDeductiblePctAdjust)) { giftDetail.TaxDeductiblePct = ANewPct; TaxDeductibility.UpdateTaxDeductibiltyAmounts(ref giftDetail); } else if (!AReversal) { if (AUpdateTaxDeductiblePctRecipients != null) { string[] Result = AUpdateTaxDeductiblePctRecipients.Find(x => x[0] == giftDetail.RecipientKey.ToString()); // true if a new percentage is available and the user wants to use it if (Result != null) { giftDetail.TaxDeductiblePct = Convert.ToDecimal(Result[1]); TaxDeductibility.UpdateTaxDeductibiltyAmounts(ref giftDetail); } } } else { giftDetail.TaxDeductibleAmount = signum * AOldGiftDetail.TaxDeductibleAmount; giftDetail.TaxDeductibleAmountBase = signum * AOldGiftDetail.TaxDeductibleAmountBase; giftDetail.TaxDeductibleAmountIntl = signum * AOldGiftDetail.TaxDeductibleAmountIntl; giftDetail.NonDeductibleAmount = signum * AOldGiftDetail.NonDeductibleAmount; giftDetail.NonDeductibleAmountBase = signum * AOldGiftDetail.NonDeductibleAmountBase; giftDetail.NonDeductibleAmountIntl = signum * AOldGiftDetail.NonDeductibleAmountIntl; } } if (AAutoCompleteComments) // only used for tax deductible pct gift adjustments { AGiftRow OldGiftRow = (AGiftRow)AMainDS.AGift.Rows.Find( new object[] { AOldGiftDetail.LedgerNumber, AOldGiftDetail.BatchNumber, AOldGiftDetail.GiftTransactionNumber }); giftDetail.GiftCommentThree = Catalog.GetString("Original gift date: " + OldGiftRow.DateEntered.ToString("dd-MMM-yyyy")); giftDetail.CommentThreeType = "Both"; } else // user defined { giftDetail.GiftCommentOne = AReversalCommentOne; giftDetail.GiftCommentTwo = AReversalCommentTwo; giftDetail.GiftCommentThree = AReversalCommentThree; giftDetail.CommentOneType = AReversalCommentOneType; giftDetail.CommentTwoType = AReversalCommentTwoType; giftDetail.CommentThreeType = AReversalCommentThreeType; } // If reversal: mark the new gift as a reversal if (AReversal) { giftDetail.ModifiedDetail = true; //Identify the reversal source giftDetail.ModifiedDetailKey = "|" + AOldGiftDetail.BatchNumber.ToString() + "|" + AOldGiftDetail.GiftTransactionNumber.ToString() + "|" + AOldGiftDetail.DetailNumber.ToString(); } else { giftDetail.ModifiedDetail = false; // Make sure the motivation detail is still active. If not then we need a new one. AMotivationDetailTable MotivationDetailTable = AMotivationDetailAccess.LoadViaAMotivationGroup( giftDetail.LedgerNumber, giftDetail.MotivationGroupCode, ATransaction); DataRow CurrentMotivationDetail = MotivationDetailTable.Rows.Find( new object[] { giftDetail.LedgerNumber, giftDetail.MotivationGroupCode, giftDetail.MotivationDetailCode }); // Motivation detail has been made inactive (or doesn't exist) then use default if (!((MotivationDetailTable != null) && (MotivationDetailTable.Rows.Count > 0) && (CurrentMotivationDetail != null)) || !Convert.ToBoolean(CurrentMotivationDetail[AMotivationDetailTable.GetMotivationStatusDBName()])) { bool ActiveRowFound = false; // search for first alternative active detail that is part of the same group foreach (AMotivationDetailRow Row in MotivationDetailTable.Rows) { if ((Row.MotivationDetailCode != giftDetail.MotivationDetailCode) && Row.MotivationStatus) { ActiveRowFound = true; giftDetail.MotivationGroupCode = Row.MotivationGroupCode; giftDetail.MotivationDetailCode = Row.MotivationDetailCode; break; } } // if none found then use default group and detail if (!ActiveRowFound) { giftDetail.MotivationGroupCode = MFinanceConstants.MOTIVATION_GROUP_GIFT; giftDetail.MotivationDetailCode = MFinanceConstants.GROUP_DETAIL_SUPPORT; } } // if the gift destination should be fixed if ((AFunction.Equals(GiftAdjustmentFunctionEnum.TaxDeductiblePctAdjust) && AGeneralFixedGiftDestination) || ((AFixedGiftDestination != null) && (AFixedGiftDestination.Exists(x => x == giftDetail.RecipientKey.ToString())))) { giftDetail.FixedGiftDestination = true; } else { giftDetail.FixedGiftDestination = false; } } AMainDS.AGiftDetail.Rows.Add(giftDetail); }
public static bool MaintainSponsorshipRecurringGifts( Int32 ALedgerNumber, Int32 ABatchNumber, Int32 AGiftTransactionNumber, Int32 ADetailNumber, Int64 ARecipientKey, Int64 ADonorKey, String AMotivationGroupCode, String AMotivationDetailCode, decimal AGiftAmount, DateTime AStartDonations, DateTime?AEndDonations, out TVerificationResultCollection AVerificationResult) { AVerificationResult = new TVerificationResultCollection(); if (ADonorKey <= 0) { AVerificationResult.Add(new TVerificationResult("error", "Please specify the donor", "", "MaintainChildren.ErrMissingDonor", TResultSeverity.Resv_Critical)); } if (AGiftAmount <= 0) { AVerificationResult.Add(new TVerificationResult("error", "Please specify a valid amount", "", "MaintainChildren.ErrMissingAmount", TResultSeverity.Resv_Critical)); } if ((AMotivationDetailCode == "") || (AMotivationDetailCode == null)) { AVerificationResult.Add(new TVerificationResult("error", "Please specify the motivation", "", "MaintainChildren.ErrMissingMotivation", TResultSeverity.Resv_Critical)); } if (AVerificationResult.HasCriticalErrors) { return(false); } TDBTransaction Transaction = new TDBTransaction(); SponsorshipTDS MainDS = new SponsorshipTDS(); TDataBase DB = DBAccess.Connect("MaintainRecurringGifts"); bool MotivationExists = false; // load batches and their transactions based on their id / batch number DB.ReadTransaction(ref Transaction, delegate { // we overwrite the user input, since the user can't really send the right batch number on create ABatchNumber = GetRecurringGiftBatchForSponsorship(ALedgerNumber, Transaction); ARecurringGiftBatchAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ABatchNumber, Transaction); ARecurringGiftAccess.LoadViaARecurringGiftBatch(MainDS, ALedgerNumber, ABatchNumber, Transaction); MotivationExists = AMotivationDetailAccess.Exists(ALedgerNumber, AMotivationGroupCode, AMotivationDetailCode, Transaction); }); if (!MotivationExists) { AVerificationResult.Add(new TVerificationResult("error", "Please specify a valid motivation", "", "MaintainChildren.ErrMissingMotivation", TResultSeverity.Resv_Critical)); return(false); } // try to get a row with requested id, aka edit else make a new ARecurringGiftRow EditGiftRow = null; foreach (ARecurringGiftRow CheckGiftRow in MainDS.ARecurringGift.Rows) { if (CheckGiftRow.GiftTransactionNumber == AGiftTransactionNumber) { EditGiftRow = CheckGiftRow; break; } } // we did not find a Transaction in this Batch, so we create one if (EditGiftRow == null) { // TODO: we could look for a gift transaction of the same donor??? EditGiftRow = MainDS.ARecurringGift.NewRowTyped(true); EditGiftRow.DonorKey = ADonorKey; EditGiftRow.BatchNumber = ABatchNumber; EditGiftRow.LedgerNumber = ALedgerNumber; EditGiftRow.GiftTransactionNumber = MainDS.ARecurringGiftBatch[0].LastGiftNumber + 1; MainDS.ARecurringGiftBatch[0].LastGiftNumber++; MainDS.ARecurringGift.Rows.Add(EditGiftRow); } // load stuff based on the current edit row DB.ReadTransaction(ref Transaction, delegate { ARecurringGiftDetailAccess.LoadViaARecurringGift(MainDS, ALedgerNumber, ABatchNumber, EditGiftRow.GiftTransactionNumber, Transaction); }); DB.CloseDBConnection(); // try to get a row with requested id, aka edit else make a new ARecurringGiftDetailRow EditGiftDetailRow = null; foreach (ARecurringGiftDetailRow CheckGiftDetailRow in MainDS.ARecurringGiftDetail.Rows) { if (CheckGiftDetailRow.DetailNumber == ADetailNumber) { EditGiftDetailRow = CheckGiftDetailRow; break; } } // none found, make one if (EditGiftDetailRow == null) { EditGiftDetailRow = MainDS.ARecurringGiftDetail.NewRowTyped(true); EditGiftDetailRow.LedgerNumber = ALedgerNumber; EditGiftDetailRow.BatchNumber = ABatchNumber; EditGiftDetailRow.RecipientKey = ARecipientKey; EditGiftDetailRow.GiftTransactionNumber = EditGiftRow.GiftTransactionNumber; EditGiftDetailRow.DetailNumber = EditGiftRow.LastDetailNumber + 1; EditGiftRow.LastDetailNumber++; MainDS.ARecurringGiftDetail.Rows.Add(EditGiftDetailRow); } EditGiftRow.DonorKey = ADonorKey; EditGiftDetailRow.GiftAmount = AGiftAmount; EditGiftDetailRow.MotivationGroupCode = AMotivationGroupCode; EditGiftDetailRow.MotivationDetailCode = AMotivationDetailCode; EditGiftDetailRow.EndDonations = AEndDonations; EditGiftDetailRow.StartDonations = AStartDonations; try { SponsorshipTDSAccess.SubmitChanges(MainDS); return(true); } catch (Exception e) { TLogging.Log(e.ToString()); AVerificationResult.Add(new TVerificationResult("error", e.Message, TResultSeverity.Resv_Critical)); return(false); } }
public static Boolean GetMotivationGroupAndDetail(Int64 APartnerKey, ref String AMotivationGroup, ref String AMotivationDetail) { Boolean PartnerKeyIsValid = false; if (APartnerKey != 0) { string MotivationGroup = MFinanceConstants.MOTIVATION_GROUP_GIFT; string MotivationDetail = AMotivationDetail; TDBTransaction readTransaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref readTransaction, delegate { PPartnerTable myPPartnerTable = null; myPPartnerTable = PPartnerAccess.LoadByPrimaryKey(APartnerKey, readTransaction); if (myPPartnerTable.Rows.Count == 1) { // Entry for partnerKey is valid PartnerKeyIsValid = true; PPartnerRow partnerRow = null; partnerRow = (PPartnerRow)myPPartnerTable.Rows[0]; // Change motivationDetail if ColumnPartnerClass is UNIT if (partnerRow.PartnerClass.Equals(MPartnerConstants.PARTNERCLASS_UNIT)) { // AND KEY-MIN bool KeyMinFound = false; // first check if a motivation detail is linked to this potential key min AMotivationDetailTable MotivationDetailTable = null; MotivationDetailTable = AMotivationDetailAccess.LoadViaPPartner(APartnerKey, readTransaction); if ((MotivationDetailTable != null) && (MotivationDetailTable.Rows.Count > 0)) { foreach (AMotivationDetailRow Row in MotivationDetailTable.Rows) { if (Row.MotivationStatus) { MotivationGroup = MotivationDetailTable[0].MotivationGroupCode; MotivationDetail = MotivationDetailTable[0].MotivationDetailCode; KeyMinFound = true; break; } } } // second check to see if this is a key min if (!KeyMinFound) { PUnitTable pUnitTable = null; pUnitTable = PUnitAccess.LoadByPrimaryKey(APartnerKey, readTransaction); if (pUnitTable.Rows.Count == 1) { PUnitRow unitRow = null; unitRow = (PUnitRow)pUnitTable.Rows[0]; if (unitRow.UnitTypeCode.Equals(MPartnerConstants.UNIT_TYPE_KEYMIN)) { MotivationDetail = MFinanceConstants.GROUP_DETAIL_KEY_MIN; } else { MotivationDetail = TSystemDefaults.GetStringDefault(SharedConstants.SYSDEFAULT_DEFAULTFIELDMOTIVATION, MFinanceConstants.GROUP_DETAIL_FIELD); // if system default is empty then set to FIELD if (string.IsNullOrEmpty(MotivationDetail)) { MotivationDetail = MFinanceConstants.GROUP_DETAIL_FIELD; } } } } } else { MotivationDetail = MFinanceConstants.GROUP_DETAIL_SUPPORT; } } }); AMotivationGroup = MotivationGroup; AMotivationDetail = MotivationDetail; } return(PartnerKeyIsValid); }