/// <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>
/// 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));
}
/// <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>
/// 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>
/// get more details of the last gift of the partner
/// </summary>
/// <param name="APartnerKey"></param>
/// <param name="ALastGiftDate"></param>
/// <param name="ALastGiftAmount"></param>
/// <param name="ALastGiftGivenToPartnerKey"></param>
/// <param name="ALastGiftRecipientLedger"></param>
/// <param name="ALastGiftCurrencyCode"></param>
/// <param name="ALastGiftDisplayFormat"></param>
/// <param name="ALastGiftGivenToShortName"></param>
/// <param name="ALastGiftRecipientLedgerShortName"></param>
/// <param name="ARestrictedOrConfidentialGiftAccessDenied"></param>
/// <returns></returns>
public static Boolean GetLastGiftDetails(Int64 APartnerKey,
out DateTime ALastGiftDate,
out decimal ALastGiftAmount,
out Int64 ALastGiftGivenToPartnerKey,
out Int64 ALastGiftRecipientLedger,
out String ALastGiftCurrencyCode,
out String ALastGiftDisplayFormat,
out String ALastGiftGivenToShortName,
out String ALastGiftRecipientLedgerShortName,
out Boolean ARestrictedOrConfidentialGiftAccessDenied)
{
DataSet LastGiftDS;
AGiftDetailTable GiftDetailDT;
SGroupGiftTable GroupGiftDT;
SUserGroupTable UserGroupDT;
AGiftRow GiftDR;
AGiftBatchRow GiftBatchDR;
AGiftDetailRow GiftDetailDR;
ACurrencyRow CurrencyDR;
Int16 Counter;
Boolean AccessToGift = false;
DataRow[] FoundUserGroups;
ALastGiftAmount = 0;
ALastGiftCurrencyCode = "";
ALastGiftDisplayFormat = "";
ALastGiftDate = DateTime.MinValue;
ALastGiftGivenToPartnerKey = 0;
ALastGiftGivenToShortName = "";
ALastGiftRecipientLedger = 0;
ALastGiftRecipientLedgerShortName = "";
ARestrictedOrConfidentialGiftAccessDenied = false;
DateTime tmpLastGiftDate = ALastGiftDate;
decimal tmpLastGiftAmount = ALastGiftAmount;
Int64 tmpLastGiftGivenToPartnerKey = ALastGiftGivenToPartnerKey;
Int64 tmpLastGiftRecipientLedger = ALastGiftRecipientLedger;
String tmpLastGiftCurrencyCode = ALastGiftCurrencyCode;
String tmpLastGiftDisplayFormat = ALastGiftDisplayFormat;
String tmpLastGiftGivenToShortName = ALastGiftGivenToShortName;
String tmpLastGiftRecipientLedgerShortName = ALastGiftRecipientLedgerShortName;
Boolean tmpRestrictedOrConfidentialGiftAccessDenied = ARestrictedOrConfidentialGiftAccessDenied;
if ((!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapINQUIRE, AGiftTable.GetTableDBName())))
{
// User hasn't got access to a_gift Table in the DB
return(false);
}
// Set up temp DataSet
LastGiftDS = new DataSet("LastGiftDetails");
LastGiftDS.Tables.Add(new AGiftTable());
LastGiftDS.Tables.Add(new AGiftBatchTable());
LastGiftDS.Tables.Add(new AGiftDetailTable());
LastGiftDS.Tables.Add(new ACurrencyTable());
LastGiftDS.Tables.Add(new PPartnerTable());
TDBTransaction Transaction = null;
bool SubmissionOK = true;
// Important: The IsolationLevel here needs to correspond with the IsolationLevel in the
// Ict.Petra.Server.MPartner.Partner.UIConnectors.TPartnerEditUIConnector.LoadData Method
// as otherwise the attempt of taking-out of a DB Transaction here will lead to Bug #4167!
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref Transaction, ref SubmissionOK,
delegate
{
try
{
try
{
AGiftAccess.LoadViaPPartner(LastGiftDS, APartnerKey, null, Transaction,
StringHelper.InitStrArr(new String[] { "ORDER BY", AGiftTable.GetDateEnteredDBName() + " DESC" }), 0, 1);
}
catch (ESecurityDBTableAccessDeniedException)
{
// User hasn't got access to a_gift Table in the DB
return;
}
catch (Exception ex)
{
throw ex;
}
if (LastGiftDS.Tables[AGiftTable.GetTableName()].Rows.Count == 0)
{
// Partner hasn't given any Gift so far
return;
}
// Get the last gift
GiftDR = ((AGiftTable)LastGiftDS.Tables[AGiftTable.GetTableName()])[0];
if (GiftDR.Restricted)
{
AccessToGift = false;
GroupGiftDT = SGroupGiftAccess.LoadViaAGift(
GiftDR.LedgerNumber,
GiftDR.BatchNumber,
GiftDR.GiftTransactionNumber,
Transaction);
UserGroupDT = SUserGroupAccess.LoadViaSUser(UserInfo.GUserInfo.UserID, Transaction);
// Loop over all rows of GroupGiftDT
for (Counter = 0; Counter <= GroupGiftDT.Rows.Count - 1; Counter += 1)
{
// To be able to view a Gift, ReadAccess must be granted
if (GroupGiftDT[Counter].ReadAccess)
{
// Find out whether the user has a row in s_user_group with the
// GroupID of the GroupGift row
FoundUserGroups =
UserGroupDT.Select(SUserGroupTable.GetGroupIdDBName() + " = '" + GroupGiftDT[Counter].GroupId + "'");
if (FoundUserGroups.Length != 0)
{
// Access to gift can be granted
AccessToGift = true;
continue;
// don't evaluate further GroupGiftDT rows
}
}
}
}
else
{
AccessToGift = true;
}
if (AccessToGift)
{
tmpLastGiftDate = GiftDR.DateEntered;
// Console.WriteLine('GiftDR.LedgerNumber: ' + GiftDR.LedgerNumber.ToString + '; ' +
// 'GiftDR.BatchNumber: ' + GiftDR.BatchNumber.ToString);
// Load Gift Batch
AGiftBatchAccess.LoadByPrimaryKey(LastGiftDS, GiftDR.LedgerNumber, GiftDR.BatchNumber,
StringHelper.InitStrArr(new String[] { AGiftBatchTable.GetCurrencyCodeDBName() }), Transaction, null, 0, 0);
if (LastGiftDS.Tables[AGiftBatchTable.GetTableName()].Rows.Count != 0)
{
GiftBatchDR = ((AGiftBatchRow)LastGiftDS.Tables[AGiftBatchTable.GetTableName()].Rows[0]);
tmpLastGiftCurrencyCode = GiftBatchDR.CurrencyCode;
// Get Currency
ACurrencyAccess.LoadByPrimaryKey(LastGiftDS, GiftBatchDR.CurrencyCode, Transaction);
if (LastGiftDS.Tables[ACurrencyTable.GetTableName()].Rows.Count != 0)
{
CurrencyDR = (ACurrencyRow)(LastGiftDS.Tables[ACurrencyTable.GetTableName()].Rows[0]);
tmpLastGiftCurrencyCode = CurrencyDR.CurrencyCode;
tmpLastGiftDisplayFormat = CurrencyDR.DisplayFormat;
}
else
{
tmpLastGiftCurrencyCode = "";
tmpLastGiftDisplayFormat = "";
}
}
else
{
// missing Currency
tmpLastGiftCurrencyCode = "";
tmpLastGiftDisplayFormat = "";
}
// Load Gift Detail
AGiftDetailAccess.LoadViaAGift(LastGiftDS,
GiftDR.LedgerNumber,
GiftDR.BatchNumber,
GiftDR.GiftTransactionNumber,
StringHelper.InitStrArr(new String[] { AGiftDetailTable.GetGiftTransactionAmountDBName(),
AGiftDetailTable.GetRecipientKeyDBName(),
AGiftDetailTable.
GetRecipientLedgerNumberDBName(),
AGiftDetailTable.GetConfidentialGiftFlagDBName() }),
Transaction,
null,
0,
0);
GiftDetailDT = (AGiftDetailTable)LastGiftDS.Tables[AGiftDetailTable.GetTableName()];
if (GiftDetailDT.Rows.Count != 0)
{
if (GiftDR.LastDetailNumber > 1)
{
// Gift is a Split Gift
tmpLastGiftAmount = 0;
for (Counter = 0; Counter <= GiftDetailDT.Rows.Count - 1; Counter += 1)
{
GiftDetailDR = (AGiftDetailRow)GiftDetailDT.Rows[Counter];
// Check for confidential gift and whether the current user is allowed to see it
if (GiftDetailDR.ConfidentialGiftFlag)
{
if (!((UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE2)) ||
(UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE3))))
{
// User isn't allowed to see the gift
tmpRestrictedOrConfidentialGiftAccessDenied = true;
tmpLastGiftAmount = 0;
return;
}
}
tmpLastGiftAmount = tmpLastGiftAmount + GiftDetailDR.GiftTransactionAmount;
}
tmpLastGiftGivenToShortName = "";
tmpLastGiftRecipientLedgerShortName = "";
tmpLastGiftGivenToPartnerKey = -1;
tmpLastGiftRecipientLedger = -1;
}
else
{
// Gift isn't a Split Gift
GiftDetailDR = (AGiftDetailRow)GiftDetailDT.Rows[0];
// Check for confidential gift and whether the current user is allowed to see it
if (GiftDetailDR.ConfidentialGiftFlag)
{
if (!((UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE2)) ||
(UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE3))))
{
// User isn't allowed to see the gift
tmpRestrictedOrConfidentialGiftAccessDenied = true;
return;
}
}
tmpLastGiftAmount = GiftDetailDR.GiftTransactionAmount;
tmpLastGiftGivenToPartnerKey = GiftDetailDR.RecipientKey;
// Get Partner ShortName
PPartnerAccess.LoadByPrimaryKey(LastGiftDS, GiftDetailDR.RecipientKey,
StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }), Transaction, null, 0, 0);
if (LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Count != 0)
{
tmpLastGiftGivenToShortName =
((PPartnerRow)(LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows[0])).PartnerShortName;
}
else
{
// missing Partner
tmpLastGiftGivenToShortName = "";
}
// Get rid of last record because we are about to select again into the same DataTable...
LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Clear();
// Get Recipient Ledger
PPartnerAccess.LoadByPrimaryKey(LastGiftDS, GiftDetailDR.RecipientLedgerNumber,
StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }), Transaction, null, 0, 0);
if (LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Count != 0)
{
tmpLastGiftRecipientLedgerShortName =
((PPartnerRow)(LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows[0])).PartnerShortName;
}
else
{
// missing Ledger
tmpLastGiftRecipientLedgerShortName = "";
}
}
}
else
{
// missing Gift Detail
tmpLastGiftAmount = 0;
tmpLastGiftGivenToShortName = "";
tmpLastGiftRecipientLedgerShortName = "";
tmpLastGiftGivenToPartnerKey = -1;
tmpLastGiftRecipientLedger = -1;
}
}
else
{
// Gift is a restriced Gift and the current user isn't allowed to see it
tmpRestrictedOrConfidentialGiftAccessDenied = true;
}
}
finally
{
TLogging.LogAtLevel(7, "TGift.GetLastGiftDetails: committed own transaction.");
}
});
ALastGiftDate = tmpLastGiftDate;
ALastGiftAmount = tmpLastGiftAmount;
ALastGiftGivenToPartnerKey = tmpLastGiftGivenToPartnerKey;
ALastGiftRecipientLedger = tmpLastGiftRecipientLedger;
ALastGiftCurrencyCode = tmpLastGiftCurrencyCode;
ALastGiftDisplayFormat = tmpLastGiftDisplayFormat;
ALastGiftGivenToShortName = tmpLastGiftGivenToShortName;
ALastGiftRecipientLedgerShortName = tmpLastGiftRecipientLedgerShortName;
ARestrictedOrConfidentialGiftAccessDenied = tmpRestrictedOrConfidentialGiftAccessDenied;
return(AccessToGift);
}
