/// <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>
/// First loads the extractTable needed. Goes throught all the Partners in the extract, If the partner already has the subscription, returns all those partners back to client. If the partner doesn't have the subscription, saves this
/// subscription to those partners.
/// </summary>
/// <returns>void</returns>
private void SubmitChangesInternal()
{
TDBTransaction SubmitChangesTransaction = null;
TSubmitChangesResult SubmissionResult = TSubmitChangesResult.scrNothingToBeSaved;
MExtractTable ExtractDT;
PSubscriptionTable SubscriptionTable;
PPartnerTable PartnerTable;
Int32 RowCounter;
Int32 PartnersInExtract;
StringCollection RequiredColumns;
StringCollection RequiredColumns2;
if (FInspectDT != null)
{
// Initialisations
FVerificationResult = new TVerificationResultCollection();
ExtractDT = new MExtractTable();
SubscriptionTable = new PSubscriptionTable();
FSubmissionDT = SubscriptionTable.Clone();
((TTypedDataTable)FSubmissionDT).InitVars();
PartnerTable = new PPartnerTable();
RequiredColumns = new StringCollection();
RequiredColumns.Add(MExtractTable.GetPartnerKeyDBName());
RequiredColumns2 = new StringCollection();
RequiredColumns2.Add(PPartnerTable.GetPartnerKeyDBName());
RequiredColumns2.Add(PPartnerTable.GetPartnerShortNameDBName());
RowCounter = 0;
// Set up asynchronous execution
TProgressTracker.SetCurrentState(FProgressID, "Checking Partners' Subscriptions...", 0.0m);
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref SubmitChangesTransaction,
ref SubmissionResult,
delegate
{
try
{
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: loading Subscriptions for ExtractID " + FExtractID.ToString() + "...");
ExtractDT = MExtractAccess.LoadViaMExtractMaster(FExtractID, RequiredColumns, SubmitChangesTransaction);
PartnersInExtract = ExtractDT.Rows.Count;
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: ExtractID has " + PartnersInExtract.ToString() + " Partners.");
// Go throught all the Partners in the extract
foreach (MExtractRow ExtractRow in ExtractDT.Rows)
{
RowCounter = RowCounter + 1;
// Calculate how much Partners we have checked. Let all Partners be a maximum of 70%.
TProgressTracker.SetCurrentState(
FProgressID,
string.Empty,
Convert.ToInt16((((double)RowCounter / (double)PartnersInExtract) * 100) * (MAX_PERCENTAGE_CHECKS / 100.0)));
TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: loadbyPrimaryKey");
SubscriptionTable = PSubscriptionAccess.LoadByPrimaryKey(
FInspectDT[0].PublicationCode,
ExtractRow.PartnerKey,
SubmitChangesTransaction);
// if the Partner does not yet have the subscription, add the subscription to this partner.
if (SubscriptionTable.Rows.Count == 0)
{
TLogging.LogAtLevel(
7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: will add Subscription to Partner with PartnerKey "
+
ExtractRow.PartnerKey.ToString());
FInspectDT[0].PartnerKey = ExtractRow.PartnerKey;
TLogging.LogAtLevel(7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: importing Row into FSubmissionDT...");
FSubmissionDT.ImportRow(FInspectDT[0]);
}
else
{
// The partner already has this Subscription: add the partner to the ResponseTable
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: won't add Subscription to Partner with PartnerKey " + ExtractRow.PartnerKey.ToString());
PartnerTable = PPartnerAccess.LoadByPrimaryKey(ExtractRow.PartnerKey, RequiredColumns2, SubmitChangesTransaction);
if (FResponseDT == null)
{
FResponseDT = PartnerTable.Clone();
}
FResponseDT.ImportRow(PartnerTable[0]);
}
}
TLogging.LogAtLevel(7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: Finished checking Partner's Subscriptions.");
if (FSubmissionDT.Rows.Count > 0)
{
// Submit the Partners with new Subscriptions to the PSubscription Table.
TProgressTracker.SetCurrentState(
FProgressID,
"Adding Subscriptions to " + FSubmissionDT.Rows.Count.ToString() + " Partners...",
MAX_PERCENTAGE_CHECKS);
// TLogging.LogAtLevel(7, "TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: " + FAsyncExecProgress.ProgressInformation);
PSubscriptionAccess.SubmitChanges((PSubscriptionTable)FSubmissionDT, SubmitChangesTransaction);
}
else
{
TLogging.LogAtLevel(
7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: no Subscriptions were added to Partners because all the Partners in the Extract already had this Subscription.");
}
SubmissionResult = TSubmitChangesResult.scrOK;
}
catch (Exception Exp)
{
TLogging.LogAtLevel(7,
"TExtractsAddSubscriptionsUIConnector.SubmitChangesInternal: Exception occured, Transaction will get ROLLED BACK. Exception: "
+
Exp.ToString());
SubmissionResult = TSubmitChangesResult.scrError;
FSubmitResult = SubmissionResult;
FSubmitException = Exp;
TProgressTracker.CancelJob(FProgressID);
return;
}
});
}
// if no values at response table, it needs to be created. If not creates, will raise exeption at client side.
if (FResponseDT == null)
{
FResponseDT = new DataTable();
}
TProgressTracker.FinishJob(FProgressID);
FSubmitResult = SubmissionResult;
}
