/// <summary>
/// close the screen and return the selected extract as accepted record
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void AcceptExtract(System.Object sender, EventArgs e)
{
MExtractMasterRow ExtractMasterRow;
FResultTable = new MExtractMasterTable();
//MExtractMasterRow SelectedRow = null;
DataRow SelectedRow = null;
if (AllowMultipleSelect)
{
// multiple rows may have been selected: find all the ones where first column is ticked
foreach (DataRow Row in FDataTable.Rows)
{
if (Convert.ToBoolean(Row[FCheckedColumnName]) == true)
{
ExtractMasterRow = FResultTable.NewRowTyped();
ExtractMasterRow.ExtractId = (int)Row[MExtractMasterTable.GetExtractIdDBName()];
ExtractMasterRow.ExtractName = Row[MExtractMasterTable.GetExtractNameDBName()].ToString();
ExtractMasterRow.ExtractDesc = Row[MExtractMasterTable.GetExtractDescDBName()].ToString();
ExtractMasterRow.KeyCount = (int)Row[MExtractMasterTable.GetKeyCountDBName()];
ExtractMasterRow.CreatedBy = Row[MExtractMasterTable.GetCreatedByDBName()].ToString();
ExtractMasterRow.DateCreated = (DateTime)Row[MExtractMasterTable.GetDateCreatedDBName()];
FResultTable.Rows.Add(ExtractMasterRow);
}
}
}
else
{
// just one row can be selected
DataRowView[] SelectedGridRow = clbDetails.SelectedDataRowsAsDataRowView;
if (SelectedGridRow.Length >= 1)
{
SelectedRow = SelectedGridRow[0].Row;
ExtractMasterRow = FResultTable.NewRowTyped();
ExtractMasterRow.ExtractId = (int)SelectedRow[MExtractMasterTable.GetExtractIdDBName()];
ExtractMasterRow.ExtractName = SelectedRow[MExtractMasterTable.GetExtractNameDBName()].ToString();
ExtractMasterRow.ExtractDesc = SelectedRow[MExtractMasterTable.GetExtractDescDBName()].ToString();
ExtractMasterRow.KeyCount = (int)SelectedRow[MExtractMasterTable.GetKeyCountDBName()];
ExtractMasterRow.CreatedBy = SelectedRow[MExtractMasterTable.GetCreatedByDBName()].ToString();
ExtractMasterRow.DateCreated = (DateTime)SelectedRow[MExtractMasterTable.GetDateCreatedDBName()];
FResultTable.Rows.Add(ExtractMasterRow);
}
}
Close();
}
/// <summary>
/// Create grid and populate
/// </summary>
private void CreateGrid()
{
string CheckedMember = FCheckedColumnName;
string IdMember = MExtractMasterTable.GetExtractIdDBName();
string NameMember = MExtractMasterTable.GetExtractNameDBName();
string DescriptionMember = MExtractMasterTable.GetExtractDescDBName();
string DeletableMember = MExtractMasterTable.GetDeletableDBName();
string KeyCountMember = MExtractMasterTable.GetKeyCountDBName();
string CreatedByMember = MExtractMasterTable.GetCreatedByDBName();
string DateCreatedMember = MExtractMasterTable.GetDateCreatedDBName();
string ModifiedByMember = MExtractMasterTable.GetModifiedByDBName();
string DateModifiedMember = MExtractMasterTable.GetDateModifiedDBName();
FExtractMasterTable = TRemote.MPartner.Partner.WebConnectors.GetAllExtractHeaders();
if (FExtractMasterTable != null)
{
DataView view = new DataView(FExtractMasterTable);
FDataTable = view.ToTable(true,
new string[] { IdMember, NameMember, DescriptionMember, DeletableMember, KeyCountMember, CreatedByMember, DateCreatedMember,
ModifiedByMember, DateModifiedMember });
FDataTable.Columns.Add(new DataColumn(CheckedMember, typeof(bool)));
clbDetails.Columns.Clear();
if (FAllowMultipleSelect)
{
clbDetails.AddCheckBoxColumn("Select", FDataTable.Columns[FCheckedColumnName], 17, false);
}
else
{
clbDetails.AutoFindColumn = 0;
}
clbDetails.AddTextColumn("Extract Name", FDataTable.Columns[NameMember] /*, 200*/);
clbDetails.AddCheckBoxColumn("Deletable", FDataTable.Columns[DeletableMember] /*, 120*/);
clbDetails.AddTextColumn("Key Count", FDataTable.Columns[KeyCountMember] /*, 80*/);
clbDetails.AddTextColumn("Description", FDataTable.Columns[DescriptionMember] /*, 300*/);
clbDetails.DataBindGrid(FDataTable, NameMember, CheckedMember, NameMember, false, true, false);
clbDetails.SetCheckedStringList("");
}
this.clbDetails.ValueChanged += new System.EventHandler(this.UpdateRecordCount);
PrepareButtons();
UpdateRecordCount();
}
/// <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>
/// reload extract list when search button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void RefreshExtractList(System.Object sender, EventArgs e)
{
bool AllUsers = true;
String CreatedByUser = "";
String ModifiedByUser = "";
DateTime?DateCreatedFrom = null;
DateTime?DateCreatedTo = null;
DateTime?DateModifiedFrom = null;
DateTime?DateModifiedTo = null;
string CheckedMember = FCheckedColumnName;
string IdMember = MExtractMasterTable.GetExtractIdDBName();
string NameMember = MExtractMasterTable.GetExtractNameDBName();
string DescriptionMember = MExtractMasterTable.GetExtractDescDBName();
string DeletableMember = MExtractMasterTable.GetDeletableDBName();
string KeyCountMember = MExtractMasterTable.GetKeyCountDBName();
string CreatedByMember = MExtractMasterTable.GetCreatedByDBName();
string DateCreatedMember = MExtractMasterTable.GetDateCreatedDBName();
if (cmbUserCreated.GetSelectedString().Length > 0)
{
AllUsers = false;
CreatedByUser = cmbUserCreated.GetSelectedString();
}
if (cmbUserModified.GetSelectedString().Length > 0)
{
AllUsers = false;
ModifiedByUser = cmbUserModified.GetSelectedString();
}
if (dtpCreatedFrom.Text.Length > 0)
{
DateCreatedFrom = dtpCreatedFrom.Date;
}
if (dtpCreatedTo.Text.Length > 0)
{
DateCreatedTo = dtpCreatedTo.Date;
}
if (dtpModifiedFrom.Text.Length > 0)
{
DateModifiedFrom = dtpModifiedFrom.Date;
}
if (dtpModifiedTo.Text.Length > 0)
{
DateModifiedTo = dtpModifiedTo.Date;
}
FExtractMasterTable = TRemote.MPartner.Partner.WebConnectors.GetAllExtractHeaders(txtExtractName.Text,
txtExtractDesc.Text, AllUsers, CreatedByUser, ModifiedByUser, DateCreatedFrom, DateCreatedTo,
DateModifiedFrom, DateModifiedTo);
if (FExtractMasterTable != null)
{
DataView view = new DataView(FExtractMasterTable);
FDataTable = view.ToTable(true,
new string[] { IdMember, NameMember, DescriptionMember, DeletableMember, KeyCountMember, CreatedByMember, DateCreatedMember });
FDataTable.Columns.Add(new DataColumn(CheckedMember, typeof(bool)));
clbDetails.Columns.Clear();
if (FAllowMultipleSelect)
{
clbDetails.AddCheckBoxColumn("Select", FDataTable.Columns[FCheckedColumnName], 17, false);
}
clbDetails.AddTextColumn("Extract Name", FDataTable.Columns[NameMember], 200);
clbDetails.AddCheckBoxColumn("Deletable", FDataTable.Columns[DeletableMember], 120);
clbDetails.AddTextColumn("Key Count", FDataTable.Columns[KeyCountMember], 80);
clbDetails.AddTextColumn("Description", FDataTable.Columns[DescriptionMember], 300);
clbDetails.DataBindGrid(FDataTable, NameMember, CheckedMember, NameMember, false, true, false);
clbDetails.SetCheckedStringList("");
}
PrepareButtons();
clbDetails.Select();
}