/// <summary> /// Find the latest gift that was given by a donor /// </summary> /// <param name="AParameters">report parameter list</param> /// <param name="AResults">result list</param> /// <returns>DataTable with row for last gift by donor</returns> public static DataTable SelectLatestGiftRow(TParameterList AParameters, TResultList AResults) { Int64 DonorKey = AParameters.Get("PartnerKey").ToInt64(); String StrSql = "SELECT DISTINCT " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetLedgerNumberDBName() + ", " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetBatchNumberDBName() + ", " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetGiftTransactionNumberDBName() + ", " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName(); StrSql = StrSql + " FROM " + AGiftTable.GetTableDBName() + " WHERE " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDonorKeyDBName() + " = " + DonorKey.ToString() + " ORDER BY " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " DESC"; TDBTransaction Transaction = new TDBTransaction(); DataTable tempTbl = new DataTable(); DBAccess.ReadTransaction( ref Transaction, delegate { tempTbl = Transaction.DataBaseObj.SelectDT(StrSql, "result", Transaction); }); DataTable resultTbl = tempTbl.Clone(); resultTbl.Clear(); if (tempTbl.Rows.Count > 0) { resultTbl.Rows.Add((object[])tempTbl.Rows[0].ItemArray.Clone()); } return(resultTbl); }
/// <summary> /// Select the last Gift and motivation details of the gifts that were given within the time period from one partner. /// </summary> /// <param name="ADonorKey">Partner key of the donor</param> /// <param name="ALedgerNumber">Ledger number</param> /// <param name="AStartDate">Start date of the period</param> /// <param name="AEndDate">End date of the period</param> /// <param name="ACurrency">Currency: Base or International</param> /// <returns>True if a gift was found; otherwise false</returns> private bool SelectLastGift(Int64 ADonorKey, Int64 ALedgerNumber, DateTime AStartDate, DateTime AEndDate, String ACurrency) { String StrSql = "SELECT " + AGiftTable.GetDateEnteredDBName() + ", " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetMotivationGroupCodeDBName() + ", " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetMotivationDetailCodeDBName(); if (ACurrency == "Base") { StrSql = StrSql + ", " + AGiftDetailTable.GetGiftAmountDBName() + " AS CurrentAmount"; } else { StrSql = StrSql + ", " + AGiftDetailTable.GetGiftAmountIntlDBName() + " AS CurrentAmount"; } StrSql = StrSql + " FROM " + AGiftTable.GetTableDBName() + " , " + AGiftDetailTable.GetTableDBName() + ", " + AGiftBatchTable.GetTableDBName() + " WHERE " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDonorKeyDBName() + " = " + ADonorKey.ToString() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetBatchNumberDBName() + " = " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetBatchNumberDBName() + " AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchNumberDBName() + " = " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetBatchNumberDBName() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetGiftTransactionNumberDBName() + " = " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetGiftTransactionNumberDBName() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " BETWEEN '" + AStartDate.ToString( "yyyy-MM-dd") + "' AND '" + AEndDate.ToString("yyyy-MM-dd") + "'" + " AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'Posted'" + " ORDER BY " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " DESC LIMIT 1"; DataTable Table = situation.GetDatabaseConnection().SelectDT(StrSql, "table", situation.GetDatabaseConnection().Transaction, new OdbcParameter[] { }); if (Table.Rows.Count > 0) { DateTime DateEntered = (DateTime)Table.Rows[0][AGiftTable.GetDateEnteredDBName()]; decimal CurrentAmount = Convert.ToDecimal(Table.Rows[0]["CurrentAmount"]); String MotivationDetail = (String)Table.Rows[0][AGiftDetailTable.GetMotivationDetailCodeDBName()]; String MotivationGroup = (String)Table.Rows[0][AGiftDetailTable.GetMotivationGroupCodeDBName()]; situation.GetParameters().Add("LastGiftDate", new TVariant(DateEntered)); situation.GetParameters().Add("LastGiftAmount", new TVariant(CurrentAmount)); situation.GetParameters().Add("MotivationDetail", new TVariant(MotivationDetail)); situation.GetParameters().Add("MotivationGroup", new TVariant(MotivationGroup)); return(true); } return(false); }
private bool DonorIsAlreadyLoaded(Int64 ADonorKey, Int32 AGiftNumber) { //Check for Donor in loaded gift batches DataView giftDV = new DataView(FMainDS.AGift); giftDV.RowFilter = string.Format("{0}={1} And Not ({2}={3} And {4}={5})", AGiftTable.GetDonorKeyDBName(), ADonorKey, AGiftTable.GetBatchNumberDBName(), FBatchNumber, AGiftTable.GetGiftTransactionNumberDBName(), AGiftNumber); return(giftDV != null && giftDV.Count > 0); }
/// <summary> /// there are several gift batches that might fit this bank statement. find the right one! /// simple matching; no split gifts, bank account number fits and amount fits /// </summary> private static int FindGiftBatch(BankImportTDS AMainDS, AEpStatementRow AStmt) { SortedList <Int32, Int32> MatchedGiftBatches = new SortedList <int, int>(); // create the dataview only after loading, otherwise loading is much slower DataView GiftDetailByAmountAndDonor = new DataView(AMainDS.AGiftDetail, string.Empty, AGiftDetailTable.GetGiftAmountDBName() + "," + BankImportTDSAGiftDetailTable.GetDonorKeyDBName(), DataViewRowState.CurrentRows); DataView GiftByAmountAndDonor = new DataView(AMainDS.AGift, string.Empty, BankImportTDSAGiftTable.GetTotalAmountDBName() + "," + AGiftTable.GetDonorKeyDBName(), DataViewRowState.CurrentRows); AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetBankSortCodeDBName() + "," + BankImportTDSPBankingDetailsTable.GetBankAccountNumberDBName(); foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows) { // find the donor for this transaction, by his bank account number Int64 DonorKey = GetDonorByBankAccountNumber(AMainDS, transaction.BranchCode, transaction.BankAccountNumber); if (transaction.BankAccountNumber.Length == 0) { // useful for NUnit testing for csv import: partnerkey in description try { DonorKey = Convert.ToInt64(transaction.Description); } catch (Exception) { DonorKey = -1; } } BankImportTDSAGiftDetailRow detailrow = null; if (DonorKey != -1) { DataRowView[] giftDetails = GiftDetailByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey }); if (giftDetails.Length == 1) { // found a possible match detailrow = (BankImportTDSAGiftDetailRow)giftDetails[0].Row; } else { // check if we can find a gift with several gift details, that would match this transaction amount DataRowView[] gifts = GiftByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey }); if (gifts.Length >= 1) { AGiftRow gift = (AGiftRow)gifts[0].Row; detailrow = (BankImportTDSAGiftDetailRow)AMainDS.AGiftDetail.Rows.Find(new object[] { gift.LedgerNumber, gift.BatchNumber, gift.GiftTransactionNumber, 1 }); } } } if (detailrow != null) { if (MatchedGiftBatches.ContainsKey(detailrow.BatchNumber)) { MatchedGiftBatches[detailrow.BatchNumber]++; } else { MatchedGiftBatches.Add(detailrow.BatchNumber, 1); } } } int SelectedGiftBatch = -1; int maxMatches = 0; foreach (int GiftBatchNumber in MatchedGiftBatches.Keys) { if (MatchedGiftBatches[GiftBatchNumber] > maxMatches) { maxMatches = MatchedGiftBatches[GiftBatchNumber]; SelectedGiftBatch = GiftBatchNumber; } } if ((SelectedGiftBatch != -1) && ((AMainDS.AEpTransaction.Rows.Count > 2) && (MatchedGiftBatches[SelectedGiftBatch] < AMainDS.AEpTransaction.Rows.Count / 2))) { TLogging.Log( "cannot find enough gifts that look the same, for statement " + AStmt.Filename + ". CountMatches for batch " + SelectedGiftBatch.ToString() + ": " + MatchedGiftBatches[SelectedGiftBatch].ToString()); SelectedGiftBatch = -1; } return(SelectedGiftBatch); }
/// <summary> /// This report considers gifts given between the two specified dates, and can include all gifts or, if /// selected, those to a particular motivation, motivation detail or recipient. For the defined set of gifts /// and its total value, the donors are sorted into a list, starting with those who gave most, and showing /// the percentage that their gifts contributed to the total received (for this motivation or recipient, if /// specified) and the cumulative percentage, moving down the list starting with the top donor. /// </summary> /// <param name="ATotalAmount">Pre calculated value of the total gifts given with these parameters</param> /// <param name="ATopXPercent">Upper limit of the percentage to show in the report</param> /// <param name="ABottomXPercent">Lower limit of the percentage to show in the report</param> /// <param name="AExtract">true to use only partners from an extract</param> /// <param name="AExtractName">extract name</param> /// <param name="AStartDate">Start date of the gifts given</param> /// <param name="AEndDate">End date of the gifts given</param> /// <param name="ARecipientKey">Partner key of a specific recipient. If 0 then use all recipients</param> /// <param name="AMotivationGroup">Limit gifts to this motivation group. If % use all motivation groups</param> /// <param name="AMotivationDetail">Limit gifts to this motivation detail. If % use all motivation details</param> /// <returns></returns> private bool MakeTopDonor(decimal ATotalAmount, decimal ATopXPercent, decimal ABottomXPercent, bool AExtract, String AExtractName, DateTime AStartDate, DateTime AEndDate, Int64 ARecipientKey, String AMotivationGroup, String AMotivationDetail) { Int64 LedgerNumber = situation.GetParameters().Get("param_ledger_number_i").ToInt64(); String CurrencyType = situation.GetParameters().Get("param_currency").ToString(); StringBuilder SqlString = new StringBuilder(); SqlString.Append("SELECT DISTINCT "); SqlString.Append("gift.p_donor_key_n AS DonorKey, "); SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerShortNameDBName() + " AS ShortName, "); SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerClassDBName() + " AS PartnerClass, "); if (CurrencyType == "Base") { SqlString.Append("SUM(detail." + AGiftDetailTable.GetGiftAmountDBName() + ") AS Amount "); } else { SqlString.Append("SUM(detail." + AGiftDetailTable.GetGiftAmountIntlDBName() + ") AS Amount "); } SqlString.Append( " FROM " + AGiftTable.GetTableDBName() + " as gift, " + AGiftDetailTable.GetTableDBName() + " as detail, " + PPartnerTable.GetTableDBName() + ", " + AGiftBatchTable.GetTableDBName() + " "); if (AExtract) { SqlString.Append(", " + MExtractTable.GetTableDBName() + ", " + MExtractMasterTable.GetTableDBName()); SqlString.Append( " WHERE gift." + AGiftTable.GetDonorKeyDBName() + " = " + MExtractTable.GetTableDBName() + "." + MExtractTable.GetPartnerKeyDBName()); SqlString.Append( " AND " + MExtractTable.GetTableDBName() + "." + MExtractTable.GetExtractIdDBName() + " = " + MExtractMasterTable.GetTableDBName() + "." + MExtractMasterTable.GetExtractIdDBName()); SqlString.Append(" AND " + MExtractMasterTable.GetTableDBName() + "." + MExtractMasterTable.GetExtractNameDBName() + " = '"); SqlString.Append(AExtractName); SqlString.Append("' AND "); } else { SqlString.Append(" WHERE "); } SqlString.Append(" detail." + AGiftDetailTable.GetLedgerNumberDBName() + " = gift." + AGiftTable.GetLedgerNumberDBName()); SqlString.Append(" AND detail." + AGiftDetailTable.GetBatchNumberDBName() + " = gift." + AGiftTable.GetBatchNumberDBName()); SqlString.Append( " AND detail." + AGiftDetailTable.GetGiftTransactionNumberDBName() + " = gift." + AGiftTable.GetGiftTransactionNumberDBName()); SqlString.Append(" AND gift." + AGiftTable.GetDateEnteredDBName() + " BETWEEN '"); SqlString.Append(AStartDate.ToString("yyyy-MM-dd")); SqlString.Append("' AND '"); SqlString.Append(AEndDate.ToString("yyyy-MM-dd")); SqlString.Append("' AND gift." + AGiftTable.GetLedgerNumberDBName() + " = "); SqlString.Append(LedgerNumber.ToString()); SqlString.Append(" AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetLedgerNumberDBName() + " = "); SqlString.Append(LedgerNumber.ToString()); SqlString.Append( " AND " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchNumberDBName() + " = gift." + AGiftTable.GetBatchNumberDBName()); SqlString.Append(" AND ( " + AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'Posted' OR "); SqlString.Append(AGiftBatchTable.GetTableDBName() + "." + AGiftBatchTable.GetBatchStatusDBName() + " = 'posted' ) "); SqlString.Append( " AND " + PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerKeyDBName() + " = gift." + AGiftTable.GetDonorKeyDBName()); if (ARecipientKey != 0) { SqlString.Append(" AND detail." + AGiftDetailTable.GetRecipientKeyDBName() + " = "); SqlString.Append(ARecipientKey.ToString()); } if (AMotivationGroup != "%") { SqlString.Append(" AND detail." + AGiftDetailTable.GetMotivationGroupCodeDBName() + " LIKE '"); SqlString.Append(AMotivationGroup); SqlString.Append("' "); } if (AMotivationDetail != "%") { SqlString.Append(" AND detail." + AGiftDetailTable.GetMotivationDetailCodeDBName() + " LIKE '"); SqlString.Append(AMotivationDetail); SqlString.Append("' "); } SqlString.Append(" GROUP BY gift." + AGiftTable.GetDonorKeyDBName() + ", "); SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerShortNameDBName() + ", "); SqlString.Append(PPartnerTable.GetTableDBName() + "." + PPartnerTable.GetPartnerClassDBName()); SqlString.Append(" ORDER BY Amount DESC"); DataTable Table = situation.GetDatabaseConnection().SelectDT(SqlString.ToString(), "table", situation.GetDatabaseConnection().Transaction, new OdbcParameter[] { }); decimal CummulativeAmount = 0; decimal TopAmount = ATotalAmount * ATopXPercent / 100; decimal BottomAmount = ATotalAmount * ABottomXPercent / 100; int NumColumns = 7; int ChildRow = 1; situation.GetResults().Clear(); for (int Counter = 0; Counter < Table.Rows.Count; ++Counter) { decimal CurrentAmount = Convert.ToDecimal(Table.Rows[Counter]["Amount"]); if (CurrentAmount < 0) { continue; } if ((CummulativeAmount <= TopAmount) && (CummulativeAmount >= BottomAmount)) { Int64 DonorKey = Convert.ToInt64(Table.Rows[Counter]["DonorKey"]); String ShortName = (String)Table.Rows[Counter]["ShortName"]; String PartnerClass = (String)Table.Rows[Counter]["PartnerClass"]; CummulativeAmount += CurrentAmount; // Transfer to results TVariant[] Header = new TVariant[NumColumns]; TVariant[] Description = { new TVariant(), new TVariant() }; TVariant[] Columns = new TVariant[NumColumns]; for (int Counter2 = 0; Counter2 < NumColumns; ++Counter2) { Header[Counter2] = new TVariant(); Columns[Counter2] = new TVariant(); } StringBuilder PartnerAddress = new StringBuilder(); PPartnerLocationRow AddressRow; if (Ict.Petra.Server.MReporting.MPartner.TRptUserFunctionsPartner.GetPartnerBestAddressRow(DonorKey, situation, out AddressRow)) { PLocationTable LocationTable = PLocationAccess.LoadByPrimaryKey(AddressRow.SiteKey, AddressRow.LocationKey, situation.GetDatabaseConnection().Transaction); if (LocationTable.Rows.Count > 0) { PLocationRow LocationRow = (PLocationRow)LocationTable.Rows[0]; PartnerAddress.Append(LocationRow.Locality); if (LocationRow.Locality.Length > 0) { PartnerAddress.Append(", "); } PartnerAddress.Append(LocationRow.StreetName); if (PartnerAddress.Length > 0) { PartnerAddress.Append(", "); } PartnerAddress.Append(LocationRow.Address3); if (PartnerAddress.Length > 0) { PartnerAddress.Append(", "); } PartnerAddress.Append(LocationRow.PostalCode); PartnerAddress.Append(" "); PartnerAddress.Append(LocationRow.City); if (LocationRow.County.Length > 0) { PartnerAddress.Append(", "); PartnerAddress.Append(LocationRow.County); } PartnerAddress.Append(", "); PartnerAddress.Append(LocationRow.CountryCode); } } Columns[0] = new TVariant(DonorKey.ToString("0000000000")); Columns[1] = new TVariant(PartnerClass); Columns[2] = new TVariant(ShortName); Columns[3] = new TVariant(CurrentAmount, "-#,##0.00;#,##0.00"); Columns[4] = new TVariant((CurrentAmount * 100 / ATotalAmount), "-#,##0.00;#,##0.00"); Columns[5] = new TVariant((CummulativeAmount * 100 / ATotalAmount), "-#,##0.00;#,##0.00"); Columns[6] = new TVariant(PartnerAddress.ToString()); situation.GetResults().AddRow(0, ChildRow++, true, 2, "", "", false, Header, Description, Columns); } else { CummulativeAmount += CurrentAmount; } } return(true); }
/// <summary> /// Calculate the gift amount for this year and the previous two years given from this donor to this recipient. /// </summary> /// <param name="ALastGiftDate">defines the last year of the calculation</param> /// <param name="ADonorKey">Partner Key of the donor</param> /// <param name="ALedgerNumber">The ledger number</param> /// <param name="ARecipientKey">Partner Key of the gift recipient</param> /// <param name="AMotivationDetail">A matching string for the gift motivation detail</param> /// <param name="AMotivationGroup">A matching string for the gift motivation group.</param> /// <param name="ABaseCurrency">Defines if we sum up the base currency or international currency</param> private void CalculatePrviousYearsGift(DateTime ALastGiftDate, Int64 ADonorKey, int ALedgerNumber, Int64 ARecipientKey, String AMotivationDetail, String AMotivationGroup, bool ABaseCurrency) { DateTime SelectionEndDate = new DateTime(ALastGiftDate.Year, 12, 31); DateTime SelectionStartDate = new DateTime(ALastGiftDate.Year - 2, 1, 1); String StrSql = "SELECT " + AGiftTable.GetDateEnteredDBName(); if (ABaseCurrency) { StrSql = StrSql + ", " + AGiftDetailTable.GetGiftAmountDBName() + " AS CurrentAmount"; } else { StrSql = StrSql + ", " + AGiftDetailTable.GetGiftAmountIntlDBName() + " AS CurrentAmount"; } StrSql = StrSql + " FROM " + AGiftTable.GetTableDBName() + " , " + AGiftDetailTable.GetTableDBName() + " WHERE " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDonorKeyDBName() + " = " + ADonorKey.ToString() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetBatchNumberDBName() + " = " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetBatchNumberDBName() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetGiftTransactionNumberDBName() + " = " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetGiftTransactionNumberDBName() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " BETWEEN '" + SelectionStartDate.ToString( "yyyy-MM-dd") + "' AND '" + SelectionEndDate.ToString("yyyy-MM-dd") + "'"; if (ARecipientKey != 0) { StrSql = StrSql + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetRecipientKeyDBName() + " = " + ARecipientKey.ToString(); } if (AMotivationDetail != "%") { StrSql = StrSql + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetMotivationDetailCodeDBName() + " LIKE '" + AMotivationDetail + "'"; } if (AMotivationGroup != "%") { StrSql = StrSql + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetMotivationGroupCodeDBName() + " LIKE '" + AMotivationGroup + "'"; } DataTable Table = situation.GetDatabaseConnection().SelectDT(StrSql, "table", situation.GetDatabaseConnection().Transaction, new OdbcParameter[] { }); decimal TotalYear_0 = 0.0M; decimal TotalYear_1 = 0.0M; decimal TotalYear_2 = 0.0M; foreach (DataRow Row in Table.Rows) { DateTime DateEntered = (DateTime)Row[AGiftTable.GetDateEnteredDBName()]; decimal CurrentAmount = Convert.ToDecimal(Row["CurrentAmount"]); if (DateEntered.Year == ALastGiftDate.Year) { TotalYear_0 += CurrentAmount; } else if (DateEntered.Year == ALastGiftDate.Year - 1) { TotalYear_1 += CurrentAmount; } else if (DateEntered.Year == ALastGiftDate.Year - 2) { TotalYear_2 += CurrentAmount; } } situation.GetParameters().Add("TotalYear_0", new TVariant(TotalYear_0)); situation.GetParameters().Add("TotalYear_1", new TVariant(TotalYear_1)); situation.GetParameters().Add("TotalYear_2", new TVariant(TotalYear_2)); }
/// <summary> /// Checks if the donor is a lapsed donor according to the parameters supplied /// </summary> /// <param name="ADonorKey">Partner Key of the donor</param> /// <param name="ARecipientKey">Partner Key of the gift recipient</param> /// <param name="AStartDate">Date of the first gift</param> /// <param name="AEndDate">Date until the gift must occure regularly</param> /// <param name="AFrequency">How often the gift must come</param> /// <param name="ATolerance">How much tolerance (in days) the gift can vary</param> /// <param name="ALedgerNumber">The ledger number</param> /// <param name="AMotivationGroup">A matching string for the gift motivation group.</param> /// <param name="AMotivationDetail">A matching string for the gift motivation detail</param> /// <param name="AIgnoreBetween">True: If this donor gave a gift in between the frequency pattern, then return false</param> /// <returns>True if donor is still active</returns> private bool IsLapsedDonor(Int64 ADonorKey, Int64 ARecipientKey, DateTime AStartDate, DateTime AEndDate, String AFrequency, int ATolerance, int ALedgerNumber, String AMotivationGroup, String AMotivationDetail, bool AIgnoreBetween) { bool ReturnValue = false; bool FirstTime = true; DateTime StartDate = AEndDate.AddDays(-ATolerance); DateTime EndDate = AEndDate.AddDays(ATolerance); int YearFrequency; int MonthFrequency; int DayFrequency; GetTimeFrequency(AFrequency, out YearFrequency, out MonthFrequency, out DayFrequency); DataTable Table; String StrSql = "SELECT " + AGiftTable.GetDateEnteredDBName() + " FROM " + AGiftTable.GetTableDBName() + " , " + AGiftDetailTable.GetTableDBName() + " WHERE " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDonorKeyDBName() + " = " + ADonorKey.ToString() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetBatchNumberDBName() + " = " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetBatchNumberDBName() + " AND " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetGiftTransactionNumberDBName() + " = " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetGiftTransactionNumberDBName(); if (ARecipientKey != 0) { StrSql = StrSql + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetRecipientKeyDBName() + " = " + ARecipientKey.ToString(); } if (AMotivationDetail != "%") { StrSql = StrSql + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetMotivationDetailCodeDBName() + " LIKE '" + AMotivationDetail + "'"; } if (AMotivationGroup != "%") { StrSql = StrSql + " AND " + AGiftDetailTable.GetTableDBName() + "." + AGiftDetailTable.GetMotivationGroupCodeDBName() + " LIKE '" + AMotivationGroup + "'"; } StrSql = StrSql + " ORDER BY " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " DESC "; Table = situation.GetDatabaseConnection().SelectDT(StrSql, "table", situation.GetDatabaseConnection().Transaction, new OdbcParameter[] { }); foreach (DataRow Row in Table.Rows) { DateTime DateEntered = (DateTime)Row[AGiftTable.GetDateEnteredDBName()]; // Ok they gave a gift during this period but check that it was the last gift. if (FirstTime) { if (DateEntered > EndDate) { break; } FirstTime = false; } // If the date is not within the date range then // check the flag to see if gifts are allowed between gifts. // otherwise ignore. if (DateEntered > EndDate) { if (AIgnoreBetween) { break; } continue; } // If we are passed the start date and have not found a gift then this // donor does not have the right pattern of giving */ if (DateEntered < StartDate) { break; } // we have found a gift and it is within the dates we are interested in. // now go back to the next set of dates. StartDate = StartDate.AddDays(-DayFrequency); StartDate = StartDate.AddMonths(-MonthFrequency); StartDate = StartDate.AddYears(-YearFrequency); EndDate = EndDate.AddDays(-DayFrequency); EndDate = EndDate.AddMonths(-MonthFrequency); EndDate = EndDate.AddYears(-YearFrequency); // we are now beyond the first gift date so stop. if (EndDate < AStartDate) { ReturnValue = true; break; } } if (!ReturnValue) { // clear this row, we don't want to display it // set all parameters of this row to NULL situation.GetParameters().Add("DONTDISPLAYROW", new TVariant(true)); } else { // show this row situation.GetParameters().Add("DONTDISPLAYROW", new TVariant(false), -1, -1, null, null, ReportingConsts.CALCULATIONPARAMETERS); String Currency = situation.GetParameters().Get("param_currency").ToString(); bool BaseCurrency = false; if (Currency == "Base") { BaseCurrency = true; } CalculatePrviousYearsGift(AEndDate, ADonorKey, ALedgerNumber, ARecipientKey, AMotivationDetail, AMotivationGroup, BaseCurrency); } return(ReturnValue); }
/// <summary> /// Main method to post a specified batch /// </summary> /// <param name="ACurrentBatchRow">The batch row to post</param> /// <param name="APostingAlreadyConfirmed">True means ask user if they want to post</param> /// <param name="AWarnOfInactiveValues">True means user is warned if inactive values exist</param> /// <param name="ADonorZeroIsValid"></param> /// <param name="ARecipientZeroIsValid"></param> /// <returns>True if the batch was successfully posted</returns> public bool PostBatch(AGiftBatchRow ACurrentBatchRow, bool APostingAlreadyConfirmed = false, bool AWarnOfInactiveValues = true, bool ADonorZeroIsValid = false, bool ARecipientZeroIsValid = false) { //This assumes that all gift data etc is loaded into the batch before arriving here bool RetVal = false; if ((ACurrentBatchRow == null) || (ACurrentBatchRow.BatchStatus != MFinanceConstants.BATCH_UNPOSTED)) { return(RetVal); } FSelectedBatchNumber = ACurrentBatchRow.BatchNumber; TVerificationResultCollection Verifications; try { //Make sure that all control data is in dataset FMyForm.GetControlDataForPosting(); GiftBatchTDSAGiftDetailTable batchGiftDetails = new GiftBatchTDSAGiftDetailTable(); DataView batchGiftDetailsDV = new DataView(FMainDS.AGiftDetail); batchGiftDetailsDV.RowFilter = string.Format("{0}={1}", AGiftDetailTable.GetBatchNumberDBName(), FSelectedBatchNumber); batchGiftDetailsDV.Sort = string.Format("{0} ASC, {1} ASC, {2} ASC", AGiftDetailTable.GetBatchNumberDBName(), AGiftDetailTable.GetGiftTransactionNumberDBName(), AGiftDetailTable.GetDetailNumberDBName()); foreach (DataRowView drv in batchGiftDetailsDV) { GiftBatchTDSAGiftDetailRow gBRow = (GiftBatchTDSAGiftDetailRow)drv.Row; batchGiftDetails.Rows.Add((object[])gBRow.ItemArray.Clone()); } bool CancelledDueToExWorkerOrAnonDonor; // save first, then post if (!FMyForm.SaveChangesForPosting(batchGiftDetails, out CancelledDueToExWorkerOrAnonDonor)) { FMyForm.Cursor = Cursors.Default; if (!CancelledDueToExWorkerOrAnonDonor) { // saving failed, therefore do not try to post MessageBox.Show(Catalog.GetString("The batch was not posted due to problems during saving; ") + Environment.NewLine + Catalog.GetString("Please first correct and save the batch, and then post it!")); } return(RetVal); } } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } //Check for missing international exchange rate bool IsTransactionInIntlCurrency = false; FMyForm.WarnAboutMissingIntlExchangeRate = true; if (FMyForm.InternationalCurrencyExchangeRate(ACurrentBatchRow, out IsTransactionInIntlCurrency, true) == 0) { return(RetVal); } //Check for zero Donors or Recipients if (!ADonorZeroIsValid) { DataView batchGiftDV = new DataView(FMainDS.AGift); batchGiftDV.RowFilter = string.Format("{0}={1} And {2}=0", AGiftTable.GetBatchNumberDBName(), FSelectedBatchNumber, AGiftTable.GetDonorKeyDBName()); int numDonorZeros = batchGiftDV.Count; if (numDonorZeros > 0) { string messageListOfOffendingGifts = String.Format(Catalog.GetString( "Gift Batch {0} contains {1} gift detail(s) with Donor 0000000. Please fix before posting!{2}{2}"), FSelectedBatchNumber, numDonorZeros, Environment.NewLine); string listOfOffendingRows = string.Empty; listOfOffendingRows += "Gift" + Environment.NewLine; listOfOffendingRows += "------------"; foreach (DataRowView drv in batchGiftDV) { AGiftRow giftRow = (AGiftRow)drv.Row; listOfOffendingRows += String.Format("{0}{1:0000}", Environment.NewLine, giftRow.GiftTransactionNumber); } TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); extendedMessageBox.ShowDialog((messageListOfOffendingGifts + listOfOffendingRows), Catalog.GetString("Post Batch Error"), string.Empty, TFrmExtendedMessageBox.TButtons.embbOK, TFrmExtendedMessageBox.TIcon.embiWarning); return(RetVal); } } if (!ARecipientZeroIsValid) { DataView batchGiftDetailsDV = new DataView(FMainDS.AGiftDetail); batchGiftDetailsDV.RowFilter = string.Format("{0}={1} And {2}=0", AGiftDetailTable.GetBatchNumberDBName(), FSelectedBatchNumber, AGiftDetailTable.GetRecipientKeyDBName()); int numRecipientZeros = batchGiftDetailsDV.Count; if (numRecipientZeros > 0) { string messageListOfOffendingGifts = String.Format(Catalog.GetString( "Gift Batch {0} contains {1} gift detail(s) with Recipient 0000000. Please fix before posting!{2}{2}"), FSelectedBatchNumber, numRecipientZeros, Environment.NewLine); string listOfOffendingRows = string.Empty; listOfOffendingRows += "Gift Detail" + Environment.NewLine; listOfOffendingRows += "-------------------"; foreach (DataRowView drv in batchGiftDetailsDV) { AGiftDetailRow giftDetailRow = (AGiftDetailRow)drv.Row; listOfOffendingRows += String.Format("{0}{1:0000} {2:00}", Environment.NewLine, giftDetailRow.GiftTransactionNumber, giftDetailRow.DetailNumber); } TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); extendedMessageBox.ShowDialog((messageListOfOffendingGifts + listOfOffendingRows), Catalog.GetString("Post Batch Error"), string.Empty, TFrmExtendedMessageBox.TButtons.embbOK, TFrmExtendedMessageBox.TIcon.embiWarning); return(RetVal); } } //Check for inactive KeyMinistries DataTable GiftsWithInactiveKeyMinistries; bool ModifiedDetails = false; if (AWarnOfInactiveValues && TRemote.MFinance.Gift.WebConnectors.InactiveKeyMinistriesFoundInBatch(FLedgerNumber, FSelectedBatchNumber, out GiftsWithInactiveKeyMinistries, false)) { int numInactiveValues = GiftsWithInactiveKeyMinistries.Rows.Count; string messageNonModifiedBatch = String.Format(Catalog.GetString("Gift Batch {0} contains {1} inactive key ministries. Please fix before posting!{2}{2}"), FSelectedBatchNumber, numInactiveValues, Environment.NewLine); string messageModifiedBatch = String.Format(Catalog.GetString( "Reversal/Adjustment Gift Batch {0} contains {1} inactive key ministries. Do you still want to post?{2}{2}"), FSelectedBatchNumber, numInactiveValues, Environment.NewLine); string listOfOffendingRows = string.Empty; listOfOffendingRows += "Gift Detail Recipient KeyMinistry" + Environment.NewLine; listOfOffendingRows += "-------------------------------------------------------------------------------"; foreach (DataRow dr in GiftsWithInactiveKeyMinistries.Rows) { listOfOffendingRows += String.Format("{0}{1:0000} {2:00} {3:00000000000} {4}", Environment.NewLine, dr[0], dr[1], dr[2], dr[3]); bool isModified = Convert.ToBoolean(dr[4]); if (isModified) { ModifiedDetails = true; } } TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); if (ModifiedDetails) { if (extendedMessageBox.ShowDialog((messageModifiedBatch + listOfOffendingRows), Catalog.GetString("Post Batch"), string.Empty, TFrmExtendedMessageBox.TButtons.embbYesNo, TFrmExtendedMessageBox.TIcon.embiWarning) == TFrmExtendedMessageBox.TResult.embrYes) { APostingAlreadyConfirmed = true; } else { return(RetVal); } } else { extendedMessageBox.ShowDialog((messageNonModifiedBatch + listOfOffendingRows), Catalog.GetString("Post Batch Error"), string.Empty, TFrmExtendedMessageBox.TButtons.embbOK, TFrmExtendedMessageBox.TIcon.embiWarning); return(RetVal); } } // ask if the user really wants to post the batch if (!APostingAlreadyConfirmed && (MessageBox.Show(String.Format(Catalog.GetString("Do you really want to post gift batch {0}?"), FSelectedBatchNumber), Catalog.GetString("Confirm posting of Gift Batch"), MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)) { return(RetVal); } Verifications = new TVerificationResultCollection(); try { FPostingInProgress = true; Thread postingThread = new Thread(() => PostGiftBatch(out Verifications)); using (TProgressDialog dialog = new TProgressDialog(postingThread)) { dialog.ShowDialog(); } if (!TVerificationHelper.IsNullOrOnlyNonCritical(Verifications)) { TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); StringBuilder errorMessages = new StringBuilder(); int counter = 0; errorMessages.AppendLine(Catalog.GetString("________________________Gift Posting Errors________________________")); errorMessages.AppendLine(); foreach (TVerificationResult verif in Verifications) { counter++; errorMessages.AppendLine(counter.ToString("000") + " - " + verif.ResultText); errorMessages.AppendLine(); } extendedMessageBox.ShowDialog(errorMessages.ToString(), Catalog.GetString("Post Batch Error"), string.Empty, TFrmExtendedMessageBox.TButtons.embbOK, TFrmExtendedMessageBox.TIcon.embiWarning); } else { MessageBox.Show(Catalog.GetString("The batch has been posted successfully!")); RetVal = true; } } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } finally { FPostingInProgress = false; } return(RetVal); }
/// <summary> /// auto populate new gift recipient info using the donor's last gift /// </summary> /// <param name="ADonorKey"></param> /// <param name="APartnerShortName"></param> /// <param name="AGiftTransactionNumber"></param> private void AutoPopulateGiftDetail(Int64 ADonorKey, String APartnerShortName, Int32 AGiftTransactionNumber) { FAutoPopulatingGiftInProcess = true; bool IsSplitGift = false; DateTime LatestUnpostedGiftDateEntered = new DateTime(1900, 1, 1); try { //Check for Donor in loaded gift batches // and record most recent date entered AGiftTable DonorRecentGiftsTable = new AGiftTable(); GiftBatchTDSAGiftDetailTable GiftDetailTable = new GiftBatchTDSAGiftDetailTable(); AGiftRow MostRecentLoadedGiftForDonorRow = null; DataView giftDV = new DataView(FMainDS.AGift); giftDV.RowStateFilter = DataViewRowState.CurrentRows; giftDV.RowFilter = string.Format("{0}={1} And Not ({2}={3} And {4}={5})", AGiftTable.GetDonorKeyDBName(), ADonorKey, AGiftTable.GetBatchNumberDBName(), FBatchNumber, AGiftTable.GetGiftTransactionNumberDBName(), AGiftTransactionNumber); giftDV.Sort = String.Format("{0} DESC, {1} DESC", AGiftTable.GetDateEnteredDBName(), AGiftTable.GetGiftTransactionNumberDBName()); if (giftDV.Count > 0) { //Take first row = most recent date entered value MostRecentLoadedGiftForDonorRow = (AGiftRow)giftDV[0].Row; LatestUnpostedGiftDateEntered = MostRecentLoadedGiftForDonorRow.DateEntered; DonorRecentGiftsTable.Rows.Add((object[])MostRecentLoadedGiftForDonorRow.ItemArray.Clone()); } //Check for even more recent saved gifts on server (i.e. not necessarily loaded) GiftDetailTable = TRemote.MFinance.Gift.WebConnectors.LoadDonorLastPostedGift(ADonorKey, FLedgerNumber, LatestUnpostedGiftDateEntered); if (((GiftDetailTable != null) && (GiftDetailTable.Count > 0))) { //UnLoaded/Saved gift from donor is more recent IsSplitGift = (GiftDetailTable.Count > 1); } else if (MostRecentLoadedGiftForDonorRow != null) { //Loaded/unsaved gift from donor is more recent DataView giftDetailDV = new DataView(FMainDS.AGiftDetail); giftDetailDV.RowStateFilter = DataViewRowState.CurrentRows; giftDetailDV.RowFilter = string.Format("{0}={1} And {2}={3}", AGiftDetailTable.GetBatchNumberDBName(), MostRecentLoadedGiftForDonorRow.BatchNumber, AGiftDetailTable.GetGiftTransactionNumberDBName(), MostRecentLoadedGiftForDonorRow.GiftTransactionNumber); foreach (DataRowView drv in giftDetailDV) { GiftBatchTDSAGiftDetailRow gDR = (GiftBatchTDSAGiftDetailRow)drv.Row; GiftDetailTable.Rows.Add((object[])gDR.ItemArray.Clone()); } IsSplitGift = (GiftDetailTable.Count > 1); } else { //nothing to autocopy return; } // if the last gift was a split gift (multiple details) then ask the user if they would like this new gift to also be split if (IsSplitGift) { GiftDetailTable.DefaultView.Sort = GiftBatchTDSAGiftDetailTable.GetDetailNumberDBName() + " ASC"; string Message = string.Format(Catalog.GetString( "The last gift from this donor was a split gift.{0}{0}Here are the details:{0}"), "\n"); int DetailNumber = 1; foreach (DataRowView dvr in GiftDetailTable.DefaultView) { GiftBatchTDSAGiftDetailRow Row = (GiftBatchTDSAGiftDetailRow)dvr.Row; Message += DetailNumber + ") "; if (Row.RecipientKey > 0) { Message += string.Format(Catalog.GetString("Recipient: {0} ({1}); Motivation Group: {2}; Motivation Detail: {3}; Amount: {4}"), Row.RecipientDescription, Row.RecipientKey, Row.MotivationGroupCode, Row.MotivationDetailCode, StringHelper.FormatUsingCurrencyCode(Row.GiftTransactionAmount, GetBatchRow().CurrencyCode) + " " + FBatchRow.CurrencyCode) + "\n"; } DetailNumber++; } Message += "\n" + Catalog.GetString("Do you want to create the same split gift again?"); if (!(MessageBox.Show(Message, Catalog.GetString( "Create Split Gift"), MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)) { if (cmbDetailMethodOfGivingCode.CanFocus) { cmbDetailMethodOfGivingCode.Focus(); } else if (txtDetailReference.CanFocus) { txtDetailReference.Focus(); } return; } } this.Cursor = Cursors.WaitCursor; GiftBatchTDSAGiftDetailRow giftDetailRow = (GiftBatchTDSAGiftDetailRow)GiftDetailTable.DefaultView[0].Row; // Handle first row, which is FPreviouslySelectedDetailRow txtDetailDonorKey.Text = String.Format("{0:0000000000}", ADonorKey); txtDetailRecipientKey.Text = String.Format("{0:0000000000}", giftDetailRow.RecipientKey); cmbDetailMotivationGroupCode.SetSelectedString(giftDetailRow.MotivationGroupCode); txtDetailMotivationDetailCode.Text = giftDetailRow.MotivationDetailCode; cmbMotivationDetailCode.SetSelectedString(giftDetailRow.MotivationDetailCode); chkDetailConfidentialGiftFlag.Checked = giftDetailRow.ConfidentialGiftFlag; // Change #5481 chkDetailChargeFlag.Checked = true; cmbDetailMethodOfPaymentCode.SetSelectedString(FBatchMethodOfPayment, -1); cmbDetailMethodOfGivingCode.SetSelectedString(giftDetailRow.MethodOfGivingCode, -1); //Handle mailing code if (FSETAutoCopyIncludeMailingCodeFlag) { cmbDetailMailingCode.SetSelectedString(giftDetailRow.MailingCode, -1); } else { cmbDetailMailingCode.SelectedIndex = -1; } //Copy the comments and comment types if required if (FSETAutoCopyIncludeCommentsFlag) { txtDetailGiftCommentOne.Text = giftDetailRow.GiftCommentOne; cmbDetailCommentOneType.SetSelectedString(giftDetailRow.CommentOneType); txtDetailGiftCommentTwo.Text = giftDetailRow.GiftCommentTwo; cmbDetailCommentTwoType.SetSelectedString(giftDetailRow.CommentTwoType); txtDetailGiftCommentThree.Text = giftDetailRow.GiftCommentThree; cmbDetailCommentThreeType.SetSelectedString(giftDetailRow.CommentThreeType); } //Handle tax fields on current row if (FSETUseTaxDeductiblePercentageFlag) { bool taxDeductible = (giftDetailRow.IsTaxDeductibleNull() ? true : giftDetailRow.TaxDeductible); giftDetailRow.TaxDeductible = taxDeductible; try { FPetraUtilsObject.SuppressChangeDetection = true; chkDetailTaxDeductible.Checked = taxDeductible; EnableTaxDeductibilityPct(taxDeductible); } finally { FPetraUtilsObject.SuppressChangeDetection = false; } if (!IsSplitGift) { //Most commonly not a split gift (?) if (!taxDeductible) { txtDeductiblePercentage.NumberValueDecimal = 0.0m; } txtTaxDeductAmount.NumberValueDecimal = 0.0m; txtNonDeductAmount.NumberValueDecimal = 0.0m; } else { if (taxDeductible) { //In case the tax percentage has changed or null values in amount fields ReconcileTaxDeductibleAmounts(giftDetailRow); } else { //Changing this will update the unbound amount textboxes txtDeductiblePercentage.NumberValueDecimal = 0.0m; } } } //Process values that are not bound to a control giftDetailRow.ReceiptPrinted = false; giftDetailRow.ReceiptNumber = 0; //Now process other gift details if they exist if (IsSplitGift) { //Only copy amount to first row if copying split gifts txtDetailGiftTransactionAmount.NumberValueDecimal = giftDetailRow.GiftTransactionAmount; // clear previous validation errors. // otherwise we get an error if the user has changed the control immediately after changing the donor key. FPetraUtilsObject.VerificationResultCollection.Clear(); bool SelectEndRow = (FBatchRow.LastGiftNumber == FPreviouslySelectedDetailRow.GiftTransactionNumber); //Just retain other details to add giftDetailRow.Delete(); GiftDetailTable.AcceptChanges(); foreach (DataRowView drv in GiftDetailTable.DefaultView) { GiftBatchTDSAGiftDetailRow detailRow = (GiftBatchTDSAGiftDetailRow)drv.Row; //______________________ //Update basic field values detailRow.LedgerNumber = FLedgerNumber; detailRow.BatchNumber = FBatchNumber; detailRow.GiftTransactionNumber = AGiftTransactionNumber; detailRow.DetailNumber = ++FGift.LastDetailNumber; detailRow.DonorName = APartnerShortName; detailRow.DonorClass = FPreviouslySelectedDetailRow.DonorClass; detailRow.DateEntered = FGift.DateEntered; detailRow.MethodOfPaymentCode = FPreviouslySelectedDetailRow.MethodOfPaymentCode; detailRow.ReceiptPrinted = false; detailRow.ReceiptNumber = 0; // Change #5481 detailRow.ChargeFlag = true; if (!FSETAutoCopyIncludeMailingCodeFlag) { detailRow.MailingCode = string.Empty; } //______________________ //process recipient details to get most recent data Int64 partnerKey = detailRow.RecipientKey; string partnerShortName = string.Empty; TPartnerClass partnerClass; bool recipientIsValid = true; if (TServerLookup.TMPartner.GetPartnerShortName(partnerKey, out partnerShortName, out partnerClass)) { detailRow.RecipientDescription = partnerShortName; detailRow.RecipientClass = partnerClass.ToString(); if (partnerClass == TPartnerClass.FAMILY) { detailRow.RecipientLedgerNumber = TRemote.MFinance.Gift.WebConnectors.GetRecipientFundNumber(partnerKey, FGift.DateEntered); detailRow.RecipientField = detailRow.RecipientLedgerNumber; detailRow.RecipientKeyMinistry = string.Empty; } else { //Class - UNIT Int64 field; string keyMinName; recipientIsValid = TFinanceControls.GetRecipientKeyMinData(partnerKey, out field, out keyMinName); detailRow.RecipientLedgerNumber = field; detailRow.RecipientField = field; detailRow.RecipientKeyMinistry = keyMinName; if (!recipientIsValid) { string msg = String.Format(Catalog.GetString( "Gift: {0}, Detail: {1} has a recipient: '{2}-{3}' that is an inactive Key Ministry!"), AGiftTransactionNumber, detailRow.DetailNumber, partnerKey, keyMinName); MessageBox.Show(msg, Catalog.GetString( "Copying Previous Split Gift"), MessageBoxButtons.OK, MessageBoxIcon.Warning); } } } else { recipientIsValid = false; string msg = String.Format(Catalog.GetString("Gift: {0}, Detail: {1} has an invalid Recipient key: '{2}'!"), AGiftTransactionNumber, detailRow.DetailNumber, partnerKey); MessageBox.Show(msg, Catalog.GetString("Copying Previous Split Gift"), MessageBoxButtons.OK, MessageBoxIcon.Warning); } //______________________ //Process motivation if (string.IsNullOrEmpty(detailRow.MotivationGroupCode)) { detailRow.MotivationGroupCode = string.Empty; string msg = String.Format(Catalog.GetString("Gift: {0}, Detail: {1} has no Motivation Group!"), AGiftTransactionNumber, detailRow.DetailNumber); MessageBox.Show(msg, Catalog.GetString("Copying Previous Split Gift"), MessageBoxButtons.OK, MessageBoxIcon.Warning); } else if (string.IsNullOrEmpty(detailRow.MotivationDetailCode)) { detailRow.MotivationDetailCode = string.Empty; string msg = String.Format(Catalog.GetString("Gift: {0}, Detail: {1} has no Motivation Detail!"), AGiftTransactionNumber, detailRow.DetailNumber); MessageBox.Show(msg, Catalog.GetString("Copying Previous Split Gift"), MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { AMotivationDetailRow motivationDetailRow = null; string motivationGroup = detailRow.MotivationGroupCode; string motivationDetail = detailRow.MotivationDetailCode; motivationDetailRow = (AMotivationDetailRow)FMainDS.AMotivationDetail.Rows.Find( new object[] { FLedgerNumber, motivationGroup, motivationDetail }); if (motivationDetailRow != null) { if (partnerKey > 0) { bool partnerIsMissingLink = false; detailRow.CostCentreCode = TRemote.MFinance.Gift.WebConnectors.RetrieveCostCentreCodeForRecipient(FLedgerNumber, partnerKey, detailRow.RecipientLedgerNumber, detailRow.DateEntered, motivationGroup, motivationDetail, out partnerIsMissingLink); } else { if (motivationGroup != MFinanceConstants.MOTIVATION_GROUP_GIFT) { detailRow.RecipientDescription = motivationGroup; } else { detailRow.RecipientDescription = string.Empty; } detailRow.CostCentreCode = motivationDetailRow.CostCentreCode; } detailRow.AccountCode = motivationDetailRow.AccountCode; if (FSETUseTaxDeductiblePercentageFlag && string.IsNullOrEmpty(motivationDetailRow.TaxDeductibleAccountCode)) { detailRow.TaxDeductibleAccountCode = string.Empty; string msg = String.Format(Catalog.GetString( "Gift: {0}, Detail: {1} has Motivation Detail: {2} which has no Tax Deductible Account!" + "This can be added in Finance / Setup / Motivation Details.{3}{3}" + "Unless this is changed it will be impossible to assign a Tax Deductible Percentage to this gift."), AGiftTransactionNumber, detailRow.DetailNumber, motivationDetail, Environment.NewLine); MessageBox.Show(msg, Catalog.GetString( "Copying Previous Split Gift"), MessageBoxButtons.OK, MessageBoxIcon.Warning); } else { detailRow.TaxDeductibleAccountCode = motivationDetailRow.TaxDeductibleAccountCode; } } else { string msg = String.Format(Catalog.GetString( "Gift: {0}, Detail: {1} has Motivation Group and Detail codes ('{2} : {3}') not found in the database!"), AGiftTransactionNumber, detailRow.DetailNumber, motivationGroup, motivationDetail); MessageBox.Show(msg, Catalog.GetString("Copying Previous Split Gift"), MessageBoxButtons.OK, MessageBoxIcon.Warning); detailRow.TaxDeductible = false; } } //______________________ //Handle tax fields detailRow.TaxDeductiblePct = RetrieveTaxDeductiblePct((recipientIsValid ? detailRow.RecipientKey : 0), detailRow.TaxDeductible); AGiftDetailRow giftDetails = (AGiftDetailRow)detailRow; TaxDeductibility.UpdateTaxDeductibiltyAmounts(ref giftDetails); //______________________ //Process comments if (!FSETAutoCopyIncludeCommentsFlag) { detailRow.SetCommentOneTypeNull(); detailRow.SetCommentTwoTypeNull(); detailRow.SetCommentThreeTypeNull(); detailRow.SetGiftCommentOneNull(); detailRow.SetGiftCommentTwoNull(); detailRow.SetGiftCommentThreeNull(); } detailRow.AcceptChanges(); detailRow.SetAdded(); } //Add in the new records (latter two arguments put in to parallel recurring form) FMainDS.AGiftDetail.Merge(GiftDetailTable, false, MissingSchemaAction.Ignore); int indexOfLatestRow = FMainDS.AGiftDetail.Rows.Count - 1; //Select last row added if (SelectEndRow) { grdDetails.SelectRowInGrid(grdDetails.Rows.Count - 1); } else if (!SelectDetailRowByDataTableIndex(indexOfLatestRow)) { if (!FFilterAndFindObject.IsActiveFilterEqualToBase) { MessageBox.Show( MCommonResourcestrings.StrNewRecordIsFiltered, MCommonResourcestrings.StrAddNewRecordTitle, MessageBoxButtons.OK, MessageBoxIcon.Information); FFilterAndFindObject.FilterPanelControls.ClearAllDiscretionaryFilters(); if (FFilterAndFindObject.FilterFindPanel.ShowApplyFilterButton != TUcoFilterAndFind.FilterContext.None) { FFilterAndFindObject.ApplyFilter(); } SelectDetailRowByDataTableIndex(indexOfLatestRow); } } ClearKeyMinistries(); cmbMotivationDetailCode.Clear(); mniRecipientHistory.Enabled = false; btnDeleteAll.Enabled = btnDelete.Enabled; UpdateRecordNumberDisplay(); FLastDonor = -1; } else { txtDetailDonorKey.FocusTextBoxPartAfterFindScreenCloses = false; txtDetailGiftTransactionAmount.Focus(); } FPetraUtilsObject.SetChangedFlag(); } finally { this.Cursor = Cursors.Default; FAutoPopulatingGiftInProcess = false; } }
/// <summary> /// export all posted invoices for conference and seminar participants in this year /// </summary> public static void Export(string AOutputPath, char ACSVSeparator, string ANewLine, Int32 ALedgerNumber, Int32 AFinancialYear, string ACostCentres) { string filename = Path.GetFullPath(Path.Combine(AOutputPath, "participants.csv")); Console.WriteLine("Writing file: " + filename); TDBTransaction Transaction = new TDBTransaction(); AGiftDetailTable giftdetails = new AGiftDetailTable(); AGiftTable gifts = new AGiftTable(); AGiftBatchTable batches = new AGiftBatchTable(); PPersonTable persons = new PPersonTable(); DBAccess.ReadTransaction(ref Transaction, delegate { TDataBase db = Transaction.DataBaseObj; // all gift details towards a costcentre that needs to be exported string sql = String.Format("SELECT DISTINCT D.* " + "FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D " + "WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " + "AND G.{3} = B.{3} AND G.{9} = B.{9} " + "AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " + "AND D.{11} IN ({12}) " + "AND NOT D.{13} = '{14}'", AGiftBatchTable.GetTableDBName(), AGiftTable.GetTableDBName(), AGiftDetailTable.GetTableDBName(), AGiftBatchTable.GetLedgerNumberDBName(), ALedgerNumber, AGiftBatchTable.GetBatchYearDBName(), AFinancialYear, AGiftBatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_POSTED, AGiftBatchTable.GetBatchNumberDBName(), AGiftTable.GetGiftTransactionNumberDBName(), AGiftDetailTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'", AGiftDetailTable.GetMotivationGroupCodeDBName(), "GIFT"); db.SelectDT(giftdetails, sql, Transaction, null, 0, 0); sql = sql.Replace("SELECT DISTINCT D.*", "SELECT DISTINCT G.*"); db.SelectDT(gifts, sql, Transaction, null, 0, 0); gifts.DefaultView.Sort = AGiftTable.GetBatchNumberDBName() + "," + AGiftTable.GetGiftTransactionNumberDBName(); sql = sql.Replace("SELECT DISTINCT G.*", "SELECT DISTINCT B.*"); db.SelectDT(batches, sql, Transaction, null, 0, 0); batches.DefaultView.Sort = AGiftTable.GetBatchNumberDBName(); sql = String.Format("SELECT DISTINCT P.* " + "FROM PUB_{0} AS B, PUB_{1} AS G, PUB_{2} AS D, PUB.{15} AS P " + "WHERE B.{3} = {4} AND B.{5} = {6} AND B.{7}='{8}' " + "AND G.{3} = B.{3} AND G.{9} = B.{9} " + "AND D.{3} = G.{3} AND D.{9} = G.{9} AND D.{10} = G.{10} " + "AND D.{11} IN ({12}) " + "AND NOT D.{13} = '{14}' " + "AND P.{16} = G.{17}", AGiftBatchTable.GetTableDBName(), AGiftTable.GetTableDBName(), AGiftDetailTable.GetTableDBName(), AGiftBatchTable.GetLedgerNumberDBName(), ALedgerNumber, AGiftBatchTable.GetBatchYearDBName(), AFinancialYear, AGiftBatchTable.GetBatchStatusDBName(), MFinanceConstants.BATCH_POSTED, AGiftBatchTable.GetBatchNumberDBName(), AGiftTable.GetGiftTransactionNumberDBName(), AGiftDetailTable.GetCostCentreCodeDBName(), "'" + ACostCentres.Replace(",", "','") + "'", AGiftDetailTable.GetMotivationGroupCodeDBName(), "GIFT", PPersonTable.GetTableDBName(), PPersonTable.GetPartnerKeyDBName(), AGiftTable.GetDonorKeyDBName()); db.SelectDT(persons, sql, Transaction, null, 0, 0); persons.DefaultView.Sort = PPersonTable.GetPartnerKeyDBName(); }); StringBuilder sb = new StringBuilder(); foreach (AGiftDetailRow detail in giftdetails.Rows) { AGiftRow gift = (AGiftRow)gifts.DefaultView.FindRows(new object[] { detail.BatchNumber, detail.GiftTransactionNumber })[0].Row; AGiftBatchRow batch = (AGiftBatchRow)batches.DefaultView.FindRows(detail.BatchNumber)[0].Row; DataRowView[] personList = persons.DefaultView.FindRows(gift.DonorKey); PPersonRow person = (personList.Length > 0 ? (PPersonRow)personList[0].Row : null); sb.Append(StringHelper.StrMerge( new string[] { "GB" + detail.BatchNumber.ToString() + "_G" + detail.GiftTransactionNumber.ToString() + "_D" + detail.DetailNumber.ToString(), String.Format("{0:N}", detail.GiftTransactionAmount), batch.GlEffectiveDate.ToString("yyyyMMdd"), gift.DonorKey.ToString(), person != null ? (person.DateOfBirth.HasValue ? person.DateOfBirth.Value.ToString("yyyyMMdd") : string.Empty) : string.Empty, detail.CostCentreCode, batch.BatchDescription, detail.GiftCommentOne, detail.GiftCommentTwo }, ACSVSeparator)); sb.Append(ANewLine); } StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252)); sw.Write(sb.ToString()); sw.Close(); }
/// <summary> /// Main method to post a specified batch /// </summary> /// <param name="ACurrentBatchRow">The batch row to post</param> /// <param name="ARefreshGUIAfterPosting">Will be set to true if the GUI should be updated. Can be true even if Posting fails /// <param name="AWarnOfInactiveValues">True means user is warned if inactive values exist</param> /// <param name="ADonorZeroIsValid"></param> /// <param name="ARecipientZeroIsValid"></param> /// <param name="APostingAlreadyConfirmed">True means ask user if they want to post</param> /// if the server gets a SerializableTransactionException</param> /// <returns>True if the batch was successfully posted</returns> public bool PostBatch(AGiftBatchRow ACurrentBatchRow, out bool ARefreshGUIAfterPosting, bool AWarnOfInactiveValues = true, bool ADonorZeroIsValid = false, bool ARecipientZeroIsValid = false, bool APostingAlreadyConfirmed = false) { ARefreshGUIAfterPosting = false; if ((ACurrentBatchRow == null) || (ACurrentBatchRow.BatchStatus != MFinanceConstants.BATCH_UNPOSTED)) { return(false); } FSelectedBatchNumber = ACurrentBatchRow.BatchNumber; //Make sure that all control data is in dataset FMyForm.GetLatestControlData(); //Copy all batch data to new table GiftBatchTDSAGiftDetailTable BatchGiftDetails = new GiftBatchTDSAGiftDetailTable(); DataView BatchGiftDetailsDV = new DataView(FMainDS.AGiftDetail); BatchGiftDetailsDV.RowFilter = string.Format("{0}={1}", AGiftDetailTable.GetBatchNumberDBName(), FSelectedBatchNumber); BatchGiftDetailsDV.Sort = string.Format("{0} ASC, {1} ASC, {2} ASC", AGiftDetailTable.GetBatchNumberDBName(), AGiftDetailTable.GetGiftTransactionNumberDBName(), AGiftDetailTable.GetDetailNumberDBName()); foreach (DataRowView drv in BatchGiftDetailsDV) { GiftBatchTDSAGiftDetailRow gBRow = (GiftBatchTDSAGiftDetailRow)drv.Row; BatchGiftDetails.Rows.Add((object[])gBRow.ItemArray.Clone()); } //Save and check for inactive values and ex-workers and anonymous gifts if (FPetraUtilsObject.HasChanges) { //Keep this conditional check separate from the one above so that it only gets called // when necessary and doesn't result in the executon of the same method if (!FMyForm.SaveChangesForPosting(BatchGiftDetails)) { return(false); } else { APostingAlreadyConfirmed = true; } } else { //This has to be called here because if there are no changes then the DataSavingValidating // method which calls the method below, will not run. if (!FMyForm.GetBatchControl().AllowInactiveFieldValues(ref APostingAlreadyConfirmed, TExtraGiftBatchChecks.GiftBatchAction.POSTING) || FMyForm.GiftHasExWorkerOrAnon(BatchGiftDetails) ) { return(false); } } //Check hash total validity if ((ACurrentBatchRow.HashTotal != 0) && (ACurrentBatchRow.BatchTotal != ACurrentBatchRow.HashTotal)) { MessageBox.Show(String.Format(Catalog.GetString( "The gift batch total ({0}) for batch {1} does not equal the hash total ({2})!"), StringHelper.FormatUsingCurrencyCode(ACurrentBatchRow.BatchTotal, ACurrentBatchRow.CurrencyCode), ACurrentBatchRow.BatchNumber, StringHelper.FormatUsingCurrencyCode(ACurrentBatchRow.HashTotal, ACurrentBatchRow.CurrencyCode)), "Post Gift Batch"); return(false); } //Check for missing international exchange rate bool IsTransactionInIntlCurrency = false; FMyForm.WarnAboutMissingIntlExchangeRate = true; if (FMyForm.InternationalCurrencyExchangeRate(ACurrentBatchRow, out IsTransactionInIntlCurrency, true) == 0) { return(false); } //Check for zero Donors or Recipients if (!ADonorZeroIsValid) { DataView batchGiftDV = new DataView(FMainDS.AGift); batchGiftDV.RowFilter = string.Format("{0}={1} And {2}=0", AGiftTable.GetBatchNumberDBName(), FSelectedBatchNumber, AGiftTable.GetDonorKeyDBName()); int numDonorZeros = batchGiftDV.Count; if (numDonorZeros > 0) { string messageListOfOffendingGifts = String.Format(Catalog.GetString( "Gift Batch {0} contains {1} gift detail(s) with Donor 0000000. Please fix before posting!{2}{2}"), FSelectedBatchNumber, numDonorZeros, Environment.NewLine); string listOfOffendingRows = string.Empty; listOfOffendingRows += "Gift" + Environment.NewLine; listOfOffendingRows += "------------"; foreach (DataRowView drv in batchGiftDV) { AGiftRow giftRow = (AGiftRow)drv.Row; listOfOffendingRows += String.Format("{0}{1:0000}", Environment.NewLine, giftRow.GiftTransactionNumber); } TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); extendedMessageBox.ShowDialog((messageListOfOffendingGifts + listOfOffendingRows), Catalog.GetString("Post Batch Error"), string.Empty, TFrmExtendedMessageBox.TButtons.embbOK, TFrmExtendedMessageBox.TIcon.embiWarning); return(false); } } if (!ARecipientZeroIsValid) { DataView batchGiftDetailsDV = new DataView(FMainDS.AGiftDetail); batchGiftDetailsDV.RowFilter = string.Format("{0}={1} And {2}=0", AGiftDetailTable.GetBatchNumberDBName(), FSelectedBatchNumber, AGiftDetailTable.GetRecipientKeyDBName()); int numRecipientZeros = batchGiftDetailsDV.Count; if (numRecipientZeros > 0) { string messageListOfOffendingGifts = String.Format(Catalog.GetString( "Gift Batch {0} contains {1} gift detail(s) with Recipient 0000000. Please fix before posting!{2}{2}"), FSelectedBatchNumber, numRecipientZeros, Environment.NewLine); string listOfOffendingRows = string.Empty; listOfOffendingRows += "Gift Detail" + Environment.NewLine; listOfOffendingRows += "-------------------"; foreach (DataRowView drv in batchGiftDetailsDV) { AGiftDetailRow giftDetailRow = (AGiftDetailRow)drv.Row; listOfOffendingRows += String.Format("{0}{1:0000} {2:00}", Environment.NewLine, giftDetailRow.GiftTransactionNumber, giftDetailRow.DetailNumber); } TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); extendedMessageBox.ShowDialog((messageListOfOffendingGifts + listOfOffendingRows), Catalog.GetString("Post Batch Error"), string.Empty, TFrmExtendedMessageBox.TButtons.embbOK, TFrmExtendedMessageBox.TIcon.embiWarning); return(false); } } //Check for inactive KeyMinistries DataTable GiftsWithInactiveKeyMinistries; bool ModifiedDetails = false; if (AWarnOfInactiveValues && TRemote.MFinance.Gift.WebConnectors.InactiveKeyMinistriesFoundInBatch(FLedgerNumber, FSelectedBatchNumber, out GiftsWithInactiveKeyMinistries, false)) { int numInactiveValues = GiftsWithInactiveKeyMinistries.Rows.Count; string messageNonModifiedBatch = String.Format(Catalog.GetString("Gift Batch {0} contains {1} inactive key ministries. Please fix before posting!{2}{2}"), FSelectedBatchNumber, numInactiveValues, Environment.NewLine); string messageModifiedBatch = String.Format(Catalog.GetString( "Reversal/Adjustment Gift Batch {0} contains {1} inactive key ministries. Do you still want to post?{2}{2}"), FSelectedBatchNumber, numInactiveValues, Environment.NewLine); string listOfOffendingRows = string.Empty; listOfOffendingRows += "Gift Detail Recipient KeyMinistry" + Environment.NewLine; listOfOffendingRows += "-------------------------------------------------------------------------------"; foreach (DataRow dr in GiftsWithInactiveKeyMinistries.Rows) { listOfOffendingRows += String.Format("{0}{1:0000} {2:00} {3:00000000000} {4}", Environment.NewLine, dr[0], dr[1], dr[2], dr[3]); bool isModified = Convert.ToBoolean(dr[4]); if (isModified) { ModifiedDetails = true; } } TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); if (ModifiedDetails) { if (extendedMessageBox.ShowDialog((messageModifiedBatch + listOfOffendingRows), Catalog.GetString("Post Batch"), string.Empty, TFrmExtendedMessageBox.TButtons.embbYesNo, TFrmExtendedMessageBox.TIcon.embiWarning) == TFrmExtendedMessageBox.TResult.embrYes) { APostingAlreadyConfirmed = true; } else { return(false); } } else { extendedMessageBox.ShowDialog((messageNonModifiedBatch + listOfOffendingRows), Catalog.GetString("Post Batch Error"), string.Empty, TFrmExtendedMessageBox.TButtons.embbOK, TFrmExtendedMessageBox.TIcon.embiWarning); return(false); } } // ask if the user really wants to post the batch if (!APostingAlreadyConfirmed && (MessageBox.Show(String.Format(Catalog.GetString("Do you really want to post gift batch {0}?"), FSelectedBatchNumber), Catalog.GetString("Confirm posting of Gift Batch"), MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)) { return(false); } TVerificationResultCollection Verifications = new TVerificationResultCollection(); try { FPostingInProgress = true; Thread postingThread = new Thread(() => PostGiftBatch(out Verifications)); postingThread.SetApartmentState(ApartmentState.STA); using (TProgressDialog dialog = new TProgressDialog(postingThread)) { dialog.ShowDialog(); } if (TVerificationHelper.ResultsContainErrorCode(Verifications, PetraErrorCodes.ERR_DB_SERIALIZATION_EXCEPTION)) { TConcurrentServerTransactions.ShowTransactionSerializationExceptionDialog(); ARefreshGUIAfterPosting = true; return(false); } else if (!TVerificationHelper.IsNullOrOnlyNonCritical(Verifications)) { TFrmExtendedMessageBox extendedMessageBox = new TFrmExtendedMessageBox(FMyForm); StringBuilder errorMessages = new StringBuilder(); int counter = 0; errorMessages.AppendLine(Catalog.GetString("________________________Gift Posting Errors________________________")); errorMessages.AppendLine(); foreach (TVerificationResult verif in Verifications) { counter++; errorMessages.AppendLine(counter.ToString("000") + " - " + verif.ResultText); errorMessages.AppendLine(); } extendedMessageBox.ShowDialog(errorMessages.ToString(), Catalog.GetString("Post Batch Error"), string.Empty, TFrmExtendedMessageBox.TButtons.embbOK, TFrmExtendedMessageBox.TIcon.embiWarning); } else { MessageBox.Show(Catalog.GetString("The batch has been posted successfully!")); ARefreshGUIAfterPosting = true; } } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } finally { FPostingInProgress = false; } return(true); }