/// <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); }
/// <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> /// Print a receipt for each gift (one page for each donor) in the batch /// </summary> /// <param name="AGiftTDS"></param> public void PrintGiftBatchReceipts(GiftBatchTDS AGiftTDS) { AGiftBatchRow GiftBatchRow = AGiftTDS.AGiftBatch[0]; DataView GiftView = new DataView(AGiftTDS.AGift); //AGiftTDS.AGift.DefaultView.RowFilter GiftView.RowFilter = String.Format("{0}={1} and {2}={3}", AGiftTable.GetLedgerNumberDBName(), GiftBatchRow.LedgerNumber, AGiftTable.GetBatchNumberDBName(), GiftBatchRow.BatchNumber); String ReceiptedDonorsList = ""; List <Int32> ReceiptedGiftTransactions = new List <Int32>(); SortedList <Int64, AGiftTable> GiftsPerDonor = new SortedList <Int64, AGiftTable>(); foreach (DataRowView rv in GiftView) { AGiftRow GiftRow = (AGiftRow)rv.Row; bool ReceiptEachGift; String ReceiptLetterFrequency; bool EmailGiftStatement; bool AnonymousDonor; TRemote.MPartner.Partner.ServerLookups.WebConnectors.GetPartnerReceiptingInfo( GiftRow.DonorKey, out ReceiptEachGift, out ReceiptLetterFrequency, out EmailGiftStatement, out AnonymousDonor); if (ReceiptEachGift) { // I want to print a receipt for this gift, // but if there's already one queued for this donor, // I'll add this gift onto the existing receipt. if (!GiftsPerDonor.ContainsKey(GiftRow.DonorKey)) { GiftsPerDonor.Add(GiftRow.DonorKey, new AGiftTable()); } AGiftRow NewRow = GiftsPerDonor[GiftRow.DonorKey].NewRowTyped(); DataUtilities.CopyAllColumnValues(GiftRow, NewRow); GiftsPerDonor[GiftRow.DonorKey].Rows.Add(NewRow); } // if receipt required } // foreach gift String HtmlDoc = ""; OpenFileDialog DialogOpen = new OpenFileDialog(); if (Directory.Exists(TAppSettingsManager.GetValue("Formletters.Path"))) { DialogOpen.InitialDirectory = TAppSettingsManager.GetValue("Formletters.Path"); } DialogOpen.Filter = Catalog.GetString("HTML file (*.html)|*.html;*.htm"); DialogOpen.RestoreDirectory = true; DialogOpen.Title = Catalog.GetString("Select the template for the gift receipt"); if (DialogOpen.ShowDialog() != DialogResult.OK) { return; } string HTMLTemplateFilename = DialogOpen.FileName; foreach (Int64 DonorKey in GiftsPerDonor.Keys) { String DonorShortName; TPartnerClass DonorClass; TRemote.MPartner.Partner.ServerLookups.WebConnectors.GetPartnerShortName(DonorKey, out DonorShortName, out DonorClass); DonorShortName = Calculations.FormatShortName(DonorShortName, eShortNameFormat.eReverseShortname); string HtmlPage = TRemote.MFinance.Gift.WebConnectors.PrintGiftReceipt( GiftBatchRow.CurrencyCode, DonorShortName, DonorKey, DonorClass, GiftsPerDonor[DonorKey], HTMLTemplateFilename ); TFormLettersTools.AttachNextPage(ref HtmlDoc, HtmlPage); ReceiptedDonorsList += (DonorShortName + "\r\n"); foreach (AGiftRow GiftRow in GiftsPerDonor[DonorKey].Rows) { ReceiptedGiftTransactions.Add(GiftRow.GiftTransactionNumber); } } TFormLettersTools.CloseDocument(ref HtmlDoc); if (ReceiptedGiftTransactions.Count > 0) { TFrmReceiptControl.PreviewOrPrint(HtmlDoc); if (MessageBox.Show( Catalog.GetString( "Press OK if receipts to these recipients were printed correctly.\r\nThe gifts will be marked as receipted.\r\n") + ReceiptedDonorsList, Catalog.GetString("Receipt Printing"), MessageBoxButtons.OKCancel) == DialogResult.OK) { foreach (Int32 Trans in ReceiptedGiftTransactions) { TRemote.MFinance.Gift.WebConnectors.MarkReceiptsPrinted( GiftBatchRow.LedgerNumber, GiftBatchRow.BatchNumber, Trans); } } } }
/// <summary> /// update the transaction DateEntered from outside /// </summary> /// <param name="ABatchRow"></param> public void UpdateDateEntered(AGiftBatchRow ABatchRow) { Int32 ledgerNumber; Int32 batchNumber; DateTime batchEffectiveDate; if (ABatchRow.BatchStatus != MFinanceConstants.BATCH_UNPOSTED) { return; } ledgerNumber = ABatchRow.LedgerNumber; batchNumber = ABatchRow.BatchNumber; batchEffectiveDate = ABatchRow.GlEffectiveDate; DataView giftDataView = new DataView(FMainDS.AGift); giftDataView.RowFilter = String.Format("{0}={1} And {2}={3}", AGiftTable.GetLedgerNumberDBName(), ledgerNumber, AGiftTable.GetBatchNumberDBName(), batchNumber); DataView giftDetailDataView = new DataView(FMainDS.AGiftDetail); giftDetailDataView.RowFilter = String.Format("{0}={1} And {2}={3}", AGiftDetailTable.GetLedgerNumberDBName(), ledgerNumber, AGiftDetailTable.GetBatchNumberDBName(), batchNumber); ((TFrmGiftBatch)ParentForm).EnsureGiftDataPresent(ledgerNumber, batchNumber); if ((FPreviouslySelectedDetailRow != null) && (FBatchNumber == batchNumber)) { //Rows already active in transaction tab. Need to set current row as code below will not update currently selected row FGLEffectivePeriodHasChangedFlag = true; GetSelectedDetailRow().DateEntered = batchEffectiveDate; } TFrmGiftBatch ParentGiftBatchForm = (TFrmGiftBatch)ParentForm; ParentGiftBatchForm.Cursor = Cursors.WaitCursor; //Update all gift rows in this batch foreach (DataRowView dv in giftDataView) { AGiftRow giftRow = (AGiftRow)dv.Row; giftRow.DateEntered = batchEffectiveDate; } //Update all gift detail rows in this batch foreach (DataRowView dv in giftDetailDataView) { GiftBatchTDSAGiftDetailRow giftDetailRow = (GiftBatchTDSAGiftDetailRow)dv.Row; UpdateGiftDestinationOnDateChange(ref giftDetailRow, batchEffectiveDate); } ParentGiftBatchForm.Cursor = Cursors.Default; //If current row exists then refresh details if (FGLEffectivePeriodHasChangedFlag) { ShowDetails(); } }