/// <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>
/// 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>
/// Used internally to build a SQL WHERE criteria from the AFindCriteria HashTable.
///
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters</param>
/// <param name="AParametersArray">An array holding 1..n instantiated OdbcParameters
/// (including parameter Value)</param>
/// <returns>SQL WHERE criteria
/// </returns>
private static String BuildCustomWhereCriteria(DataTable ACriteriaData, out OdbcParameter[] AParametersArray)
{
String CustomWhereCriteria = "";
DataTable CriteriaDataTable;
DataRow CriteriaRow;
ArrayList InternalParameters;
CriteriaDataTable = ACriteriaData;
CriteriaRow = CriteriaDataTable.Rows[0];
InternalParameters = new ArrayList();
if (CriteriaRow["Ledger"].ToString().Length > 0)
{
// Searched DB Field: 'a_ledger_number_i'
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnLedgerNumberId, CriteriaRow, "Ledger", "",
ref CustomWhereCriteria, ref InternalParameters);
}
// Searched DB Field: 'a_batch_number_i'
if ((CriteriaRow["Batch"] != null) && (CriteriaRow["Batch"] != System.DBNull.Value))
{
// do manually otherwise 0 gets changed to a string and we get a crash
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
"PUB_" + TTypedDataTable.GetTableNameSQL(AGiftDetailTable.TableId) + "." + AGiftDetailTable.GetBatchNumberDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["Batch"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_gift_transaction_number_i'
if ((CriteriaRow["Transaction"] != null) && (CriteriaRow["Transaction"] != System.DBNull.Value))
{
// do manually otherwise 0 gets changed to a string and we get a crash
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
"PUB_" + TTypedDataTable.GetTableNameSQL(AGiftDetailTable.TableId) + "." + AGiftDetailTable.GetGiftTransactionNumberDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["Transaction"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_receipt_number_i'
if ((CriteriaRow["Receipt"] != null) && (CriteriaRow["Receipt"] != System.DBNull.Value))
{
// do manually otherwise 0 gets changed to a string and we get a crash
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
"PUB_" + TTypedDataTable.GetTableNameSQL(AGiftTable.TableId) + "." + AGiftTable.GetReceiptNumberDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["Receipt"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_motivation_group_code_c'
if (CriteriaRow["MotivationGroup"].ToString().Length > 0)
{
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnMotivationGroupCodeId, CriteriaRow, "MotivationGroup", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_motivation_detail_code_c'
if (CriteriaRow["MotivationDetail"].ToString().Length > 0)
{
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnMotivationDetailCodeId, CriteriaRow, "MotivationDetail", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_gift_comment_one_c'
if (CriteriaRow["Comment1"].ToString().Length > 0)
{
CriteriaRow.Table.Columns.Add(new DataColumn("Comment1Match"));
CriteriaRow["Comment1Match"] = "CONTAINS";
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnGiftCommentOneId, CriteriaRow, "Comment1", "Comment1Match",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_donor_key_n'
if (((Int64)CriteriaRow["Donor"]) > 0)
{
new TDynamicSearchHelper(AGiftTable.TableId,
AGiftTable.ColumnDonorKeyId, CriteriaRow, "Donor", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_recipient_key_n'
if (((Int64)CriteriaRow["Recipient"]) > 0)
{
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnRecipientKeyId, CriteriaRow, "Recipient", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_date_entered_d'
if ((CriteriaRow["From"] != System.DBNull.Value) && (CriteriaRow["To"] != System.DBNull.Value) &&
(CriteriaRow["From"] == CriteriaRow["To"]))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
AGiftTable.GetDateEnteredDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["From"]);
InternalParameters.Add(miParam);
}
else
{
if (CriteriaRow["From"] != System.DBNull.Value)
{
CustomWhereCriteria = String.Format("{0} AND {1} >= ?", CustomWhereCriteria,
AGiftTable.GetDateEnteredDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["From"]);
InternalParameters.Add(miParam);
}
if (CriteriaRow["To"] != System.DBNull.Value)
{
CustomWhereCriteria = String.Format("{0} AND {1} <= ?", CustomWhereCriteria,
AGiftTable.GetDateEnteredDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["To"]);
InternalParameters.Add(miParam);
}
}
// Searched DB Field: 'a_gift_amount_n'
if ((CriteriaRow["MinAmount"].ToString().Length > 0) && (CriteriaRow["MaxAmount"].ToString().Length > 0) &&
(CriteriaRow["MinAmount"] == CriteriaRow["MaxAmount"]))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
AGiftDetailTable.GetGiftAmountDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MinAmount"]);
InternalParameters.Add(miParam);
}
else
{
if (CriteriaRow["MinAmount"].ToString().Length > 0)
{
CustomWhereCriteria = String.Format("{0} AND {1} >= ?", CustomWhereCriteria,
AGiftDetailTable.GetGiftAmountDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MinAmount"]);
InternalParameters.Add(miParam);
}
if (CriteriaRow["MaxAmount"].ToString().Length > 0)
{
CustomWhereCriteria = String.Format("{0} AND {1} <= ?", CustomWhereCriteria,
AGiftDetailTable.GetGiftAmountDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MaxAmount"]);
InternalParameters.Add(miParam);
}
}
// TLogging.LogAtLevel(7, "CustomWhereCriteria: " + CustomWhereCriteria);
/* Convert ArrayList to a array of ODBCParameters
* seem to need to declare a type first
*/
AParametersArray = ((OdbcParameter[])(InternalParameters.ToArray(typeof(OdbcParameter))));
InternalParameters = null; // ensure this is GC'd
return(CustomWhereCriteria);
}
/// <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));
}