public static DataSet GetReportDataSet(String AReportType, Dictionary <String, TVariant> AParameters)
{
TLogging.SetStatusBarProcedure(WriteToStatusBar);
DataSet ResultSet = null;
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
switch (AReportType)
{
/* Financial Development */
case "GiftsOverMinimum":
ResultSet = TFinanceReportingWebConnector.GiftsOverMinimum(AParameters, FDbAdapter);
break;
default:
TLogging.Log("GetDataSetThread unknown ReportType: " + AReportType);
break;
}
FDbAdapter.CloseConnection();
if (FDbAdapter.IsCancelled)
{
ResultSet = null;
}
return(ResultSet);
}
public static DataSet GetOneYearMonthGivingDataSet(Dictionary <String, TVariant> AParameters)
{
FDbAdapter = new TReportingDbAdapter(false);
TLogging.SetStatusBarProcedure(WriteToStatusBar);
DataSet ReturnDataSet = new DataSet();
// get recipients
DataTable Recipients = TFinanceReportingWebConnector.RecipientGiftStatementRecipientTable(AParameters, FDbAdapter);
if (FDbAdapter.IsCancelled || (Recipients == null))
{
return(null);
}
DataTable Donors = new DataTable("Donors");
foreach (DataRow Row in Recipients.Rows)
{
// get donor information for each recipient
Donors.Merge(TFinanceReportingWebConnector.OneYearMonthGivingDonorTable(AParameters, (Int64)Row["RecipientKey"], FDbAdapter));
if (FDbAdapter.IsCancelled)
{
return(null);
}
}
ReturnDataSet.Tables.Add(Recipients);
ReturnDataSet.Tables.Add(Donors);
return((FDbAdapter.IsCancelled) ? null : ReturnDataSet);
}
public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv)
{
GLReportingTDS MainDs = new GLReportingTDS();
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction();
FDbAdapter = new TReportingDbAdapter();
while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "")
{
String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", "");
String[] part = Tbl.Split('/');
// MainDs.Tables[part[0]].Merge(FDbAdapter.RunQuery(part[1], part[0], Transaction));
MainDs.Merge(FDbAdapter.RunQuery(part[1], part[0], Transaction));
}
DBAccess.GDBAccessObj.RollbackTransaction();
if (FDbAdapter.IsCancelled)
{
return(null);
}
return(MainDs);
}
public static SReportTemplateTable GetTemplateVariants(String AReportType, String AAuthor, Boolean ADefaultOnly = false)
{
SReportTemplateTable Tbl = new SReportTemplateTable();
SReportTemplateTable Ret = new SReportTemplateTable();
TDBTransaction Transaction = null;
TDataBase dbConnection = new TDataBase();
try
{
dbConnection = TReportingDbAdapter.EstablishDBConnection(true, "GetTemplateVariants");
LoadTemplatesFromBackupFile(AReportType, dbConnection);
dbConnection.BeginAutoReadTransaction(
ref Transaction,
delegate
{
SReportTemplateRow TemplateRow = Tbl.NewRowTyped(false);
TemplateRow.ReportType = AReportType;
Tbl = SReportTemplateAccess.LoadUsingTemplate(TemplateRow, Transaction);
});
String filter = String.Format("(s_author_c ='{0}' OR s_private_l=false)", AAuthor);
if (ADefaultOnly)
{
filter += " AND (s_default_l=true OR s_private_default_l=true)";
}
Tbl.DefaultView.RowFilter = filter;
if (Tbl.DefaultView.Count > 0)
{
Tbl.DefaultView.Sort =
(ADefaultOnly) ? "s_private_default_l DESC, s_default_l DESC" :
"s_readonly_l DESC, s_default_l DESC, s_private_default_l DESC";
}
else // Something went wrong, but I'll try not to return empty-handed.
{
Tbl.DefaultView.RowFilter = "";
}
Ret.Merge(Tbl.DefaultView.ToTable());
Ret.AcceptChanges();
}
finally
{
dbConnection.CloseDBConnection();
}
return(Ret);
}
public static DataTable GetReportDataTable(String AReportType, Dictionary <String, TVariant> AParameters)
{
FDbAdapter = new TReportingDbAdapter();
TLogging.SetStatusBarProcedure(WriteToStatusBar);
DataTable ResultTbl = null;
switch (AReportType)
{
case "BalanceSheet":
ResultTbl = TFinanceReportingWebConnector.BalanceSheetTable(AParameters, FDbAdapter);
break;
case "HOSA":
ResultTbl = TFinanceReportingWebConnector.HosaGiftsTable(AParameters, FDbAdapter);
break;
case "Stewardship":
ResultTbl = TFinanceReportingWebConnector.StewardshipTable(AParameters, FDbAdapter);
break;
case "IncomeExpense":
ResultTbl = TFinanceReportingWebConnector.IncomeExpenseTable(AParameters, FDbAdapter);
break;
case "AFO":
ResultTbl = TFinanceReportingWebConnector.AFOTable(AParameters, FDbAdapter);
break;
case "Executive Summary":
ResultTbl = TFinanceReportingWebConnector.ExecutiveSummaryTable(AParameters, FDbAdapter);
break;
case "TrialBalance":
ResultTbl = TFinanceReportingWebConnector.TrialBalanceTable(AParameters, FDbAdapter);
break;
case "GiftBatchDetail":
ResultTbl = TFinanceReportingWebConnector.GiftBatchDetailTable(AParameters, FDbAdapter);
break;
case "RecipientTaxDeductPct":
ResultTbl = TFinanceReportingWebConnector.RecipientTaxDeductPctTable(AParameters, FDbAdapter);
break;
default:
TLogging.Log("GetDatatableThread unknown ReportType: " + AReportType);
break;
}
return((FDbAdapter.IsCancelled) ? null : ResultTbl);
}
/// <summary>
/// Reuturns a DataTable called ContactInformation
/// </summary>
/// <param name="APartnerKeys"></param>
/// <param name="ADbAdapter"></param>
/// <param name="AIncludeMobile"></param>
/// <param name="AIncludeAlternateTelephone"></param>
/// <param name="AIncludeURL"></param>
/// <returns></returns>
public static DataTable GetPrimaryPhoneFax(List <string> APartnerKeys, TReportingDbAdapter ADbAdapter,
Boolean AIncludeMobile = false, Boolean AIncludeAlternateTelephone = false, Boolean AIncludeURL = false)
{
DataTable ReturnTable = new DataTable("ContactInformation");
ReturnTable.Columns.Add("PartnerKey");
foreach (string PartnerKey in APartnerKeys)
{
DataRow dr = ReturnTable.NewRow();
dr[0] = PartnerKey;
ReturnTable.Rows.Add(dr);
}
AddPrimaryPhoneEmailFaxToTable(ReturnTable, 0, ADbAdapter, AIncludeMobile, AIncludeAlternateTelephone, AIncludeURL);
return(ReturnTable);
}
public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv)
{
TDBTransaction Transaction = null;
GLReportingTDS MainDs = new GLReportingTDS();
try
{
FDbAdapter = new TReportingDbAdapter(false);
Transaction = FDbAdapter.FPrivateDatabaseObj.BeginTransaction(
ATransactionName: "FastReports Report GetReportingDataSet DB Transaction");
while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "")
{
String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", "");
String[] part = Tbl.Split('/');
DataTable NewTbl = FDbAdapter.RunQuery(part[1], part[0], Transaction);
MainDs.Merge(NewTbl);
}
if (FDbAdapter.IsCancelled)
{
return(null);
}
}
catch (Exception Exc)
{
MainDs = null;
TLogging.Log("TReportingWebConnector.GetReportingDataSet encountered an Exception: " + Exc.ToString());
throw;
}
finally
{
FDbAdapter.FPrivateDatabaseObj.RollbackTransaction();
FDbAdapter.CloseConnection();
}
return(MainDs);
}
public static SReportTemplateTable GetTemplateById(int TemplateId)
{
TDataBase dbConnection = null;
TDBTransaction Transaction = null;
SReportTemplateTable TemplateTable = null;
try
{
dbConnection = TReportingDbAdapter.EstablishDBConnection(true, "GetTemplateById");
dbConnection.BeginAutoReadTransaction(
ref Transaction,
delegate
{
TemplateTable = SReportTemplateAccess.LoadByPrimaryKey(TemplateId, Transaction);
});
}
finally
{
dbConnection.CloseDBConnection();
}
return(TemplateTable);
}
public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv)
{
TDBTransaction Transaction = null;
GLReportingTDS MainDs = new GLReportingTDS();
try
{
Transaction = DBAccess.GDBAccessObj.BeginTransaction();
FDbAdapter = new TReportingDbAdapter();
while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "")
{
String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", "");
String[] part = Tbl.Split('/');
DataTable NewTbl = FDbAdapter.RunQuery(part[1], part[0], Transaction);
MainDs.Merge(NewTbl);
}
if (FDbAdapter.IsCancelled)
{
return(null);
}
}
catch (Exception)
{
MainDs = null;
throw;
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(MainDs);
}
public static DataTable RecipientTaxDeductPctTable(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.BeginAutoReadTransaction(ref Transaction,
delegate
{
DateTime CurrentDate = DateTime.Today;
string RecipientSelection = AParameters["param_recipient_selection"].ToString();
string Query =
"SELECT DISTINCT p_partner_tax_deductible_pct.p_partner_key_n, p_partner_tax_deductible_pct.p_date_valid_from_d, " +
"p_partner_tax_deductible_pct.p_percentage_tax_deductible_n, p_partner.p_partner_short_name_c, " +
"p_partner_gift_destination.p_field_key_n, um_unit_structure.um_parent_unit_key_n " +
"FROM p_partner_tax_deductible_pct " +
"LEFT JOIN p_partner " +
"ON p_partner.p_partner_key_n = p_partner_tax_deductible_pct.p_partner_key_n " +
"LEFT JOIN p_partner_gift_destination " +
"ON CASE WHEN p_partner.p_partner_class_c = 'FAMILY' " +
"THEN p_partner.p_partner_key_n = p_partner_gift_destination.p_partner_key_n " +
"AND p_partner_gift_destination.p_date_effective_d <= '" + CurrentDate + "' " +
"AND (p_partner_gift_destination.p_date_expires_d IS NULL " +
"OR (p_partner_gift_destination.p_date_expires_d >= '" + CurrentDate + "' " +
"AND p_partner_gift_destination.p_date_effective_d <> p_partner_gift_destination.p_date_expires_d)) END " +
"LEFT JOIN um_unit_structure " +
"ON CASE WHEN p_partner.p_partner_class_c = 'UNIT' " +
"THEN NOT EXISTS (SELECT * FROM p_partner_type " +
"WHERE p_partner_type.p_partner_key_n = p_partner_tax_deductible_pct.p_partner_key_n " +
"AND p_partner_type.p_type_code_c = 'LEDGER') " +
"AND um_unit_structure.um_child_unit_key_n = p_partner_tax_deductible_pct.p_partner_key_n " +
"AND um_unit_structure.um_child_unit_key_n <> um_unit_structure.um_parent_unit_key_n END";
if (RecipientSelection == "one_partner")
{
Query += " WHERE p_partner_tax_deductible_pct.p_partner_key_n = " + AParameters["param_recipient_key"].ToInt64();
}
else if (RecipientSelection == "Extract")
{
// recipient must be part of extract
Query += " WHERE EXISTS(SELECT * FROM m_extract, m_extract_master";
if (!AParameters["param_chkPrintAllExtract"].ToBool())
{
Query += ", a_gift_detail, a_gift_batch";
}
Query += " WHERE p_partner_tax_deductible_pct.p_partner_key_n = m_extract.p_partner_key_n " +
"AND m_extract.m_extract_id_i = m_extract_master.m_extract_id_i " +
"AND m_extract_master.m_extract_name_c = '" + AParameters["param_extract_name"] + "'";
if (!AParameters["param_chkPrintAllExtract"].ToBool())
{
// recipient must have a posted gift
Query += " AND a_gift_detail.a_ledger_number_i = " + AParameters["param_ledger_number_i"] +
" AND a_gift_detail.p_recipient_key_n = m_extract.p_partner_key_n " +
"AND a_gift_batch.a_ledger_number_i = " + AParameters["param_ledger_number_i"] +
" AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i " +
"AND a_gift_batch.a_batch_status_c = 'Posted'";
}
Query += ")";
}
Results = DBAccess.GDBAccessObj.SelectDT(Query, "Results", Transaction);
});
return(Results);
}
public static DataTable OneYearMonthGivingDonorTable(Dictionary <String, TVariant> AParameters,
Int64 ARecipientKey,
TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string Currency = AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n";
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
//TODO: Calendar vs Financial Date Handling - Check if this should use financial year start/end in all places below
string Query = "SELECT DISTINCT" +
" gift.p_donor_key_n AS DonorKey," +
" DonorPartner.p_partner_short_name_c AS DonorName," +
" DonorPartner.p_partner_class_c AS DonorClass," +
" detail.p_recipient_key_n AS RecipientKey," +
" SUM (detail." + Currency + ") AS GiftAmountTotal," +
" COUNT (detail." + Currency + ") AS TotalCount," +
" PUB_a_gift_batch.a_currency_code_c AS GiftCurrency," +
" SUM (CASE WHEN UPPER(DonorPartner.p_partner_class_c) = 'CHURCH' THEN detail." + Currency +
" ELSE 0 END) AS TotalChurches," +
" SUM (CASE WHEN UPPER(DonorPartner.p_partner_class_c) = 'PERSON' OR " +
" UPPER(DonorPartner.p_partner_class_c) = 'FAMILY' THEN detail." + Currency +
" ELSE 0 END) AS TotalIndividuals," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-01-01'" +
" AND '" + AParameters["param_year"] + "-01-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftJanuary," +
" SUM (CASE WHEN gift.a_date_entered_d >= '" + AParameters["param_year"] + "-02-01'" +
" AND gift.a_date_entered_d < '" + AParameters["param_year"] + "-03-01'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftFebruary," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-03-01'" +
" AND '" + AParameters["param_year"] + "-03-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftMarch," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-04-01'" +
" AND '" + AParameters["param_year"] + "-04-30'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftApril," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-05-01'" +
" AND '" + AParameters["param_year"] + "-05-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftMay," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-06-01'" +
" AND '" + AParameters["param_year"] + "-06-30'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftJune," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-07-01'" +
" AND '" + AParameters["param_year"] + "-07-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftJuly," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-08-01'" +
" AND '" + AParameters["param_year"] + "-08-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftAugust," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-09-01'" +
" AND '" + AParameters["param_year"] + "-09-30'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftSeptember," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-10-01'" +
" AND '" + AParameters["param_year"] + "-10-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftOctober," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-11-01'" +
" AND '" + AParameters["param_year"] + "-11-30'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftNovember," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-12-01'" +
" AND '" + AParameters["param_year"] + "-12-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftDecember" +
" FROM" +
" PUB_a_gift as gift," +
" PUB_a_gift_detail as detail," +
" PUB_a_gift_batch," +
" PUB_p_partner AS DonorPartner" +
" WHERE" +
" detail.a_ledger_number_i = gift.a_ledger_number_i" +
" AND detail.p_recipient_key_n = " + ARecipientKey +
" AND PUB_a_gift_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_gift_batch.a_batch_number_i = gift.a_batch_number_i" +
" AND PUB_a_gift_batch.a_ledger_number_i = " + LedgerNumber +
" AND gift.a_date_entered_d BETWEEN '" + AParameters["param_from_date"].ToDate().ToString("yyyy-MM-dd") +
"' AND '" + AParameters["param_to_date"].ToDate().ToString(
"yyyy-MM-dd") + "'" +
" AND DonorPartner.p_partner_key_n = gift.p_donor_key_n" +
" AND gift.a_ledger_number_i = " + LedgerNumber +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i" +
" AND detail.a_modified_detail_l = false" +
" GROUP BY DonorPartner.p_partner_key_n, gift.p_donor_key_n, detail.p_recipient_key_n, DonorPartner.p_partner_short_name_c, DonorPartner.p_partner_class_c, PUB_a_gift_batch.a_currency_code_c"
+
" ORDER BY gift.p_donor_key_n";
Results = DbAdapter.RunQuery(Query, "Donors", Transaction);
});
return(Results);
}
public static DataTable RecipientGiftStatementDonorAddressesTable(Int64 ADonorKey, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
// create new datatable
DataTable Results = new DataTable();
Results.Columns.Add("DonorKey", typeof(Int64));
DBAccess.GDBAccessObj.BeginAutoReadTransaction(
ref Transaction,
delegate
{
// get best address for the partner
PPartnerLocationTable PartnerLocationDT = PPartnerLocationAccess.LoadViaPPartner(ADonorKey, Transaction);
TLocationPK BestAddress = Calculations.DetermineBestAddress(PartnerLocationDT);
string QueryLocation = "SELECT" +
" PUB_p_location.p_locality_c AS Locality," +
" PUB_p_location.p_street_name_c," +
" PUB_p_location.p_address_3_c," +
" PUB_p_location.p_postal_code_c," +
" PUB_p_location.p_city_c," +
" PUB_p_location.p_county_c," +
" PUB_p_location.p_country_code_c," +
" PUB_p_country.p_address_order_i" +
" FROM" +
" PUB_p_location" +
" LEFT JOIN PUB_p_country" +
" ON PUB_p_country.p_country_code_c = PUB_p_location.p_country_code_c" +
" WHERE" +
" PUB_p_location.p_site_key_n = " + BestAddress.SiteKey +
" AND PUB_p_location.p_location_key_i = " + BestAddress.LocationKey;
Results.Merge(DbAdapter.RunQuery(QueryLocation, "DonorAddresses", Transaction));
if (Results.Rows.Count == 0)
{
DataRow NewRow = Results.NewRow();
NewRow["Locality"] = "UNKNOWN";
Results.Rows.Add(NewRow);
}
Results.Rows[0]["DonorKey"] = ADonorKey;
});
return(Results);
}
public static DataTable GiftBatchDetailTable(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
int BatchNumber = AParameters["param_batch_number_i"].ToInt32();
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.BeginAutoReadTransaction(
ref Transaction,
delegate
{
String CurrentDate = DateTime.Today.ToString(
"yyyy-MM-dd");
string Query =
"SELECT DISTINCT a_gift_batch.a_batch_description_c, a_gift_batch.a_batch_status_c, a_gift_batch.a_gift_type_c, a_gift_batch.a_gl_effective_date_d, "
+
"a_gift_batch.a_bank_cost_centre_c, a_gift_batch.a_bank_account_code_c, a_gift_batch.a_currency_code_c, a_gift_batch.a_hash_total_n, a_gift_batch.a_batch_total_n, "
+
"a_gift_detail.a_gift_transaction_number_i, a_gift_detail.a_detail_number_i, a_gift_detail.a_confidential_gift_flag_l, " +
"a_gift_detail.p_recipient_key_n, a_gift_detail.a_gift_amount_n, a_gift_detail.a_gift_amount_intl_n, a_gift_detail.a_gift_transaction_amount_n, "
+
"a_gift_detail.a_motivation_group_code_c, a_gift_detail.a_motivation_detail_code_c, a_gift_detail.a_recipient_ledger_number_n, "
+
"a_gift_detail.a_gift_comment_one_c, a_gift_detail.a_gift_comment_two_c, a_gift_detail.a_gift_comment_three_c, a_gift_detail.a_tax_deductible_pct_n, "
+
"a_gift.p_donor_key_n AS DonorKey, a_gift.a_reference_c AS GiftReference, a_gift.a_method_of_giving_code_c, a_gift.a_method_of_payment_code_c, "
+
"a_gift.a_receipt_letter_code_c, a_gift.a_date_entered_d, a_gift.a_first_time_gift_l, a_gift.a_receipt_number_i, " +
"Donor.p_partner_class_c AS DonorClass, Donor.p_partner_short_name_c AS DonorShortName, Donor.p_receipt_letter_frequency_c, Donor.p_receipt_each_gift_l, "
+
"Recipient.p_partner_class_c AS RecipientClass, Recipient.p_partner_short_name_c AS RecipientShortName, " +
"a_gift_detail.p_mailing_code_c AS MailingCode, " +
"a_gift_detail.a_charge_flag_l AS ChargeFlag, "
+
// true if donor has a valid Ex-Worker special type
"CASE WHEN EXISTS (SELECT p_partner_type.* FROM p_partner_type WHERE " +
"p_partner_type.p_partner_key_n = a_gift.p_donor_key_n" +
" AND (p_partner_type.p_valid_from_d IS null OR p_partner_type.p_valid_from_d <= '" + CurrentDate + "')" +
" AND (p_partner_type.p_valid_until_d IS null OR p_partner_type.p_valid_until_d >= '" + CurrentDate + "')" +
" AND p_partner_type.p_type_code_c LIKE '" +
TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_EXWORKERSPECIALTYPE, "EX-WORKER") + "%'" +
") THEN True ELSE False END AS EXWORKER, " +
// true if the gift is restricted for the user
"CASE WHEN EXISTS (SELECT s_user_group.* FROM s_user_group " +
"WHERE a_gift.a_restricted_l IS true" +
" AND NOT EXISTS (SELECT s_group_gift.s_read_access_l FROM s_group_gift, s_user_group " +
"WHERE s_group_gift.s_read_access_l" +
" AND s_group_gift.a_ledger_number_i = " + LedgerNumber +
" AND s_group_gift.a_batch_number_i = " + BatchNumber +
" AND s_group_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" +
" AND s_user_group.s_user_id_c = '" + UserInfo.GUserInfo.UserID + "'" +
" AND s_user_group.s_group_id_c = s_group_gift.s_group_id_c" +
" AND s_user_group.s_unit_key_n = s_group_gift.s_group_unit_key_n)" +
") THEN False ELSE True END AS ReadAccess " +
"FROM a_gift_batch, a_gift_detail, a_gift, p_partner AS Donor, p_partner AS Recipient " +
"WHERE a_gift_batch.a_ledger_number_i = " + LedgerNumber + " AND a_gift_batch.a_batch_number_i = " + BatchNumber +
" AND a_gift.a_ledger_number_i = " + LedgerNumber + " AND a_gift.a_batch_number_i = " + BatchNumber +
" AND a_gift_detail.a_ledger_number_i = " + LedgerNumber + " AND a_gift_detail.a_batch_number_i = " +
BatchNumber +
" AND a_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i " +
" AND Donor.p_partner_key_n = a_gift.p_donor_key_n" +
" AND Recipient.p_partner_key_n = a_gift_detail.p_recipient_key_n";
Results = DbAdapter.RunQuery(Query, "Results", Transaction);
});
return(Results);
}
public static DataTable SYBUNTTable(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string Currency = AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n";
bool Extract = AParameters["param_extract"].ToBool();
int MinimumAmount = AParameters["param_minimum_amount"].ToInt32();
string GiftsInRange = AParameters["param_gifts_in_range"].ToString();
string NoGiftsInRange = AParameters["param_nogifts_in_range"].ToString();
// create new datatable
DataTable Results = new DataTable();
DataTable ReturnTable = new DataTable();
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
string Query = "SELECT" +
" PUB_p_partner.p_partner_key_n AS PartnerKey," +
" PUB_p_partner.p_partner_short_name_c AS PartnerName," +
" PUB_p_partner.p_partner_class_c AS PartnerClass," +
" gift.a_date_entered_d AS LastGiftDate," +
" detail.a_motivation_group_code_c AS MotivationGroup," +
" detail.a_motivation_detail_code_c AS MotivationDetail," +
" detail." + Currency + " AS LastGiftAmount," +
" detail.a_ledger_number_i AS Ledger," +
" detail.a_batch_number_i AS Batch," +
" detail.a_gift_transaction_number_i AS GiftTransaction," +
" detail.a_detail_number_i AS Detail" +
" FROM" +
" PUB_a_gift as gift," +
" PUB_a_gift_detail as detail," +
" PUB_a_gift_batch," +
" PUB_p_partner";
if (Extract)
{
Query +=
", PUB_m_extract," +
" PUB_m_extract_master" +
" WHERE" +
" gift.p_donor_key_n = PUB_m_extract.p_partner_key_n" +
" AND PUB_m_extract.m_extract_id_i = PUB_m_extract_master.m_extract_id_i" +
" AND PUB_m_extract_master.m_extract_name_c = '" + AParameters["param_extract_name"].ToString() + "'" + // {param_extract_name}" +
" AND";
}
else
{
Query += " WHERE";
}
Query +=
" detail.a_ledger_number_i = " + LedgerNumber +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i" +
" AND (SELECT SUM(detail2." + Currency + ") FROM PUB_a_gift_detail AS detail2" +
" WHERE detail2.a_ledger_number_i = detail.a_ledger_number_i" +
" AND detail2.a_batch_number_i = detail.a_batch_number_i" +
" AND detail2.a_gift_transaction_number_i = detail.a_gift_transaction_number_i)" + " >= " + MinimumAmount.ToString();
if (!string.IsNullOrEmpty(GiftsInRange))
{
string[] GiftsInRangeArray = GiftsInRange.Split(',');
foreach (string Range in GiftsInRangeArray)
{
Query += " AND (gift.a_date_entered_d BETWEEN '" + Range.Substring(0, 10) + "' AND '" + Range.Substring(13,
10) + "') AND";
}
}
Query += " gift.a_ledger_number_i = " + LedgerNumber +
" AND PUB_p_partner.p_partner_key_n = gift.p_donor_key_n" +
" AND PUB_a_gift_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_gift_batch.a_batch_number_i = gift.a_batch_number_i" +
" AND PUB_a_gift_batch.a_ledger_number_i = " + LedgerNumber +
" AND NOT EXISTS (SELECT *" +
" FROM PUB_a_gift, PUB_a_gift_detail, PUB_a_gift_batch" +
" WHERE";
if (!string.IsNullOrEmpty(NoGiftsInRange))
{
string[] NoGiftsInRangeArray = NoGiftsInRange.Split(',');
foreach (string Range in NoGiftsInRangeArray)
{
Query += " (PUB_a_gift.a_date_entered_d BETWEEN '" + Range.Substring(0, 10) + "' AND '" + Range.Substring(13,
10) + "') AND";
}
}
Query += " PUB_a_gift.a_ledger_number_i = " + LedgerNumber +
" AND PUB_a_gift.p_donor_key_n = PUB_p_partner.p_partner_key_n" +
" AND PUB_a_gift_detail.a_ledger_number_i = " + LedgerNumber +
" AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift.a_batch_number_i" +
" AND PUB_a_gift_detail.a_gift_transaction_number_i = PUB_a_gift.a_gift_transaction_number_i" +
" AND (SELECT SUM(detail3." + Currency + ") FROM PUB_a_gift_detail AS detail3" +
" WHERE detail3.a_ledger_number_i = PUB_a_gift_detail.a_ledger_number_i" +
" AND detail3.a_batch_number_i = PUB_a_gift_detail.a_batch_number_i" +
" AND detail3.a_gift_transaction_number_i = PUB_a_gift_detail.a_gift_transaction_number_i)" + " >= " +
MinimumAmount.ToString() +
" AND PUB_a_gift_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_gift_batch.a_batch_number_i = PUB_a_gift.a_batch_number_i" +
" AND PUB_a_gift_batch.a_ledger_number_i = " + LedgerNumber +
")" +
" ORDER BY PartnerKey, LastGiftDate DESC";
Results = DbAdapter.RunQuery(Query, "Results", Transaction);
if (DbAdapter.IsCancelled)
{
Results = null;
return;
}
ReturnTable = Results.Clone();
ReturnTable.Columns.Add("Number", typeof(Int32));
int i = 0;
// Only keep the first gift for each donor (it will be the newest)
// Get the total number of gifts for a donor
while (i < Results.Rows.Count)
{
int NumberDetails = Results.Select("PartnerKey = '" + Results.Rows[i]["PartnerKey"] + "'").Length;
int NumberGifts = Results.Select("PartnerKey = '" + Results.Rows[i]["PartnerKey"] + "' AND Detail = '1'").Length;
decimal Amount = 0;
DataRow[] Gifts =
Results.Select("PartnerKey = '" + Results.Rows[i]["PartnerKey"] + "' AND Ledger = '" + Results.Rows[i]["Ledger"] +
"' AND Batch = '" + Results.Rows[i]["Batch"] + "' AND GiftTransaction = '" + Results.Rows[i]["GiftTransaction"] + "'");
foreach (DataRow Gift in Gifts)
{
Amount += Convert.ToInt32(Gift["LastGiftAmount"]);
}
DataRow NewRow = ReturnTable.NewRow();
NewRow["PartnerKey"] = Results.Rows[i]["PartnerKey"];
NewRow["PartnerName"] = Results.Rows[i]["PartnerName"];
NewRow["PartnerClass"] = Results.Rows[i]["PartnerClass"];
NewRow["LastGiftDate"] = Results.Rows[i]["LastGiftDate"];
NewRow["MotivationGroup"] = Results.Rows[i]["MotivationGroup"];
NewRow["MotivationDetail"] = Results.Rows[i]["MotivationDetail"];
NewRow["LastGiftAmount"] = Amount;
NewRow["Number"] = NumberGifts;
ReturnTable.Rows.Add(NewRow);
i += NumberDetails;
}
});
return(ReturnTable);
}
public static DataTable RecipientGiftStatementDonorTable(Dictionary <String, TVariant> AParameters,
Int64 ARecipientKey,
TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string ReportType = AParameters["param_report_type"].ToString();
string Currency = "";
if ((ReportType == "List") || (ReportType == "Email"))
{
Currency = "a_gift_transaction_amount_n";
}
else
{
Currency = AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n";
}
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
string Query = "SELECT" +
" gift.a_date_entered_d AS GiftDate," +
" gift.p_donor_key_n AS DonorKey," +
" CASE WHEN DonorPartner.p_partner_short_name_c NOT LIKE ''" +
" THEN DonorPartner.p_partner_short_name_c" +
" ELSE '" + Catalog.GetString("Unknown Donor") + "' END AS DonorName," +
" DonorPartner.p_partner_class_c AS DonorClass," +
" detail.p_recipient_key_n AS RecipientKey," +
" detail.a_motivation_detail_code_c AS MotivationCode," +
" detail.a_confidential_gift_flag_l AS Confidential," +
" detail." + Currency + " AS GiftAmount," +
" gift.a_receipt_number_i AS Receipt," +
" PUB_a_gift_batch.a_currency_code_c AS GiftCurrency," +
" RecipientLedgerPartner.p_partner_short_name_c AS GiftField," +
" CASE WHEN" +
" (UPPER(detail.a_comment_one_type_c) = 'RECIPIENT' OR UPPER(detail.a_comment_one_type_c) = 'BOTH')" +
" AND '" + ReportType + "' = 'Complete'" +
" THEN detail.a_gift_comment_one_c" +
" ELSE ''" +
" END AS CommentOne," +
" CASE WHEN" +
" UPPER(detail.a_comment_one_type_c) = 'RECIPIENT' OR UPPER(detail.a_comment_one_type_c) = 'BOTH'" +
" AND '" + ReportType + "' = 'Complete'" +
" THEN detail.a_gift_comment_two_c" +
" ELSE ''" +
" END AS CommentTwo," +
" CASE WHEN" +
" UPPER(detail.a_comment_one_type_c) = 'RECIPIENT' OR UPPER(detail.a_comment_one_type_c) = 'BOTH'" +
" AND '" + ReportType + "' = 'Complete'" +
" THEN detail.a_gift_comment_three_c" +
" ELSE ''" +
" END AS CommentThree" +
" FROM" +
" PUB_a_gift as gift," +
" PUB_a_gift_detail as detail," +
" PUB_a_gift_batch," +
" PUB_p_partner AS DonorPartner," +
" PUB_p_partner AS RecipientLedgerPartner" +
" WHERE" +
" detail.a_ledger_number_i = gift.a_ledger_number_i" +
" AND detail.p_recipient_key_n = " + ARecipientKey +
" AND PUB_a_gift_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_gift_batch.a_batch_number_i = gift.a_batch_number_i" +
" AND PUB_a_gift_batch.a_ledger_number_i = " + LedgerNumber +
" AND gift.a_date_entered_d BETWEEN '" + AParameters["param_from_date"].ToDate().ToString("yyyy-MM-dd") +
"' AND '" + AParameters["param_to_date"].ToDate().ToString("yyyy-MM-dd") + "'" +
" AND DonorPartner.p_partner_key_n = gift.p_donor_key_n" +
" AND RecipientLedgerPartner.p_partner_key_n = detail.a_recipient_ledger_number_n" +
" AND gift.a_ledger_number_i = " + LedgerNumber +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i";
if ((ReportType == "Complete") || (ReportType == "Gifts Only"))
{
Query += " ORDER BY gift.a_date_entered_d";
}
else if (ReportType == "Donors Only")
{
Query += " ORDER BY DonorPartner.p_partner_short_name_c";
}
Results = DbAdapter.RunQuery(Query, "Donors", Transaction);
});
return(Results);
}
public static DataSet APPaymentReport(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string NumFrom = "";
string NumTo = "";
string DateFrom = "";
string DateTo = "";
if (AParameters["param_payment_date_from_i"].ToString() != String.Empty)
{
DateFrom = " AND a_payment_date_d >= '" + AParameters["param_payment_date_from_i"].ToDate().ToString("yyyy-MM-dd") + "' ";
}
if (AParameters["param_payment_date_to_i"].ToString() != String.Empty)
{
DateTo = " AND a_payment_date_d <= '" + AParameters["param_payment_date_to_i"].ToDate().ToString("yyyy-MM-dd") + "' ";
}
if (AParameters["param_payment_num_from_i"].ToString() != String.Empty)
{
NumFrom = " AND a_ap_document_payment.a_payment_number_i >= " + AParameters["param_payment_num_from_i"].ToInt() + " ";
}
if (AParameters["param_payment_num_to_i"].ToString() != String.Empty)
{
NumTo = " AND a_ap_document_payment.a_payment_number_i <= " + AParameters["param_payment_num_to_i"].ToInt() + " ";
}
DataSet ReturnDataSet = new DataSet();
// create new datatable
DataTable Payments = new DataTable();
DataTable Suppliers = new DataTable();
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String Query =
@"SELECT
a_ap_document.p_partner_key_n,
a_ap_document_payment.a_payment_number_i,
a_ap_document.a_ap_number_i AS ApNumber,
a_ap_document.a_ap_document_id_i AS ApDocumentId,
a_ap_document.a_document_code_c AS DocCode,
a_ap_document.a_reference_c AS DocRef,
a_ap_document.a_credit_note_flag_l AS DocCreditNote,
a_ap_document.a_date_issued_d AS DocDate,
a_ap_document_payment.a_amount_n AS DocumentPaymentTotal,
a_ap_document_detail.a_amount_n AS TotalAmountInvoice,
a_payment_date_d,
a_ap_payment.a_bank_account_c AS PaymentBAcc,
a_account_code_c
FROM a_ap_document
JOIN a_ap_document_payment ON a_ap_document_payment.a_ap_document_id_i = a_ap_document.a_ap_document_id_i
JOIN a_ap_document_detail ON a_ap_document_detail.a_ap_document_id_i = a_ap_document.a_ap_document_id_i
LEFT JOIN a_ap_payment ON a_ap_payment.a_payment_number_i=a_ap_document_payment.a_payment_number_i AND a_ap_payment.a_ledger_number_i=a_ap_document_payment.a_ledger_number_i
WHERE a_ap_document.a_ledger_number_i = "
+
LedgerNumber + @"
AND a_ap_document_payment.a_ledger_number_i = " + LedgerNumber +
" " +
DateFrom + " " + DateTo + " " + NumFrom + " " + NumTo + " " +
@"
ORDER BY a_ap_document.p_partner_key_n";
Payments = DbAdapter.RunQuery(Query, "Payments", Transaction);
List <string> partners = new List <string>();
foreach (DataRow dr in Payments.Rows)
{
partners.Add(dr[0].ToString());
}
String partnerstring = String.Join(",",
partners);
if (partnerstring == String.Empty)
{
partnerstring = "0";
}
Query =
@"SELECT DISTINCT p_partner.p_partner_key_n, p_partner_short_name_c,a_currency_code_c FROM p_partner
JOIN a_ap_supplier ON p_partner.p_partner_key_n = a_ap_supplier.p_partner_key_n WHERE p_partner.p_partner_key_n IN("
+
partnerstring + ")";
Suppliers = DbAdapter.RunQuery(Query, "Suppliers", Transaction);
});
ReturnDataSet.Tables.Add(Payments);
ReturnDataSet.Tables.Add(Suppliers);
return(ReturnDataSet);
}
public static DataSet APAgedSupplierList(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string DateSelection = AParameters["param_date_selection"].ToDate().ToString("yyyy-MM-dd");
DataSet ReturnDataSet = new DataSet();
// create new datatable
DataTable APAgedSupplierListTable = new DataTable();
DataTable documents = new DataTable();
DataTable currencies = new DataTable();
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String Query =
@"SELECT
p_partner.p_partner_key_n,
a_ap_document.a_ap_document_id_i AS DocId,
a_ap_document.a_total_amount_n AS APAmount,
a_ap_document.a_ap_number_i AS APNumber,
a_ap_document.a_credit_note_flag_l AS IsCredit,
a_ap_document.a_date_issued_d AS IssueDate,
a_ap_document.a_credit_terms_i AS CreditTerms,
a_ap_document.a_document_code_c AS DocumentCode,
a_ap_document.a_reference_c AS Reference,
a_ap_document.a_discount_days_i AS DiscountDays,
a_ap_supplier.a_currency_code_c,
a_ap_document.a_document_status_c
FROM a_ap_document
JOIN a_ap_supplier ON a_ap_document.p_partner_key_n = a_ap_supplier.p_partner_key_n
JOIN p_partner ON p_partner.p_partner_key_n = a_ap_document.p_partner_key_n
WHERE a_ap_document.a_ledger_number_i = "
+
LedgerNumber + @"
AND a_ap_document.a_date_entered_d <= '" + DateSelection +
@"'
AND a_ap_document.a_document_status_c <> 'CANCELLED'
AND a_ap_document.a_document_status_c <> 'PAID'";
documents = DbAdapter.RunQuery(Query, "documents", Transaction);
List <string> partners = new List <string>();
foreach (DataRow dr in documents.Rows)
{
partners.Add(dr[0].ToString());
}
String partnerstring = String.Join(",",
partners);
if (partnerstring == String.Empty)
{
partnerstring = "0";
}
Query =
@"SELECT DISTINCT p_partner.p_partner_key_n, p_partner_short_name_c,a_currency_code_c FROM p_partner
JOIN a_ap_supplier ON p_partner.p_partner_key_n = a_ap_supplier.p_partner_key_n WHERE p_partner.p_partner_key_n IN("
+
partnerstring + ")";
APAgedSupplierListTable = DbAdapter.RunQuery(Query, "APAgedSupplierList", Transaction);
});
DataTable NewDocuments = documents.Copy();
//adding new columns
String[] newCol =
{
"OverdueP", "Overdue", "Due", "DueP", "DuePP"
};
foreach (String col in newCol)
{
NewDocuments.Columns.Add(col, typeof(decimal));
}
int counter = -1;
foreach (DataRow row in documents.Rows)
{
counter++;
DateTime Paramdate = AParameters["param_date_selection"].ToDate();
DateTime DueDate = ((DateTime)row["issuedate"]).AddDays(Double.Parse(row["creditterms"].ToString()));
//Overdue30+
DateTime MyDate = Paramdate.AddDays(-30);
if (DueDate <= MyDate)
{
NewDocuments.Rows[counter]["OverdueP"] = row["apamount"];
continue;
}
//Overdue
if ((DueDate >= MyDate) && (DueDate < Paramdate))
{
NewDocuments.Rows[counter]["Overdue"] = row["apamount"];
continue;
}
//Due
MyDate = Paramdate.AddDays(30);
if ((DueDate >= Paramdate) && (DueDate < MyDate))
{
NewDocuments.Rows[counter]["Due"] = row["apamount"];
continue;
}
//Due30p
DateTime MyBigDate = MyDate.AddDays(30);
if ((DueDate >= MyDate) && (DueDate < MyBigDate))
{
NewDocuments.Rows[counter]["DueP"] = row["apamount"];
continue;
}
//Due60p
if (DueDate >= MyBigDate)
{
NewDocuments.Rows[counter]["DuePP"] = row["apamount"];
continue;
}
NewDocuments.Rows[counter]["apamount"] = "ERROR";
counter++;
}
//get all the diffrent currencies into a table
currencies = NewDocuments.DefaultView.ToTable("currencies", true, "a_currency_code_c");
ReturnDataSet.Tables.Add(currencies);
ReturnDataSet.Tables.Add(APAgedSupplierListTable);
ReturnDataSet.Tables.Add(NewDocuments);
return(ReturnDataSet);
}
/// <summary>
/// Adds the primary Phone Email and Fax to a DataTable
/// </summary>
/// <param name="ADataTable"></param>
/// <param name="APartnerKeyColumn"></param>
/// <param name="ADbAdapter"></param>
/// <param name="AIncludeMobile"></param>
/// <param name="AIncludeAlternateTelephone"></param>
/// <param name="AIncludeURL"></param>
public static void AddPrimaryPhoneEmailFaxToTable(DataTable ADataTable, int APartnerKeyColumn, TReportingDbAdapter ADbAdapter,
Boolean AIncludeMobile = false, Boolean AIncludeAlternateTelephone = false, Boolean AIncludeURL = false)
{
TDBTransaction Transaction = new TDBTransaction();
String SelectMobile = "";
String SelectAlternateTelephone = "";
String SelectURL = "";
int IdxMobile = 0;
int IdxAlternateTelephone = 0;
int IdxURL = 0;
int NextIdx = 4;
List <string> partnerlist = new List <string>();
foreach (DataRow dr in ADataTable.Rows)
{
partnerlist.Add(dr[APartnerKeyColumn].ToString());
}
if (partnerlist.Count == 0)
{
partnerlist.Add("-1");
}
if (AIncludeMobile)
{
SelectMobile =
@", (
SELECT '+' || (SELECT p_internat_telephone_code_i FROM p_country WHERE p_country_code_c = pattribute.p_value_country_c)|| ' ' || p_value_c
FROM p_partner_attribute AS pattribute
JOIN p_partner_attribute_type ON p_partner_attribute_type.p_attribute_type_c = pattribute.p_attribute_type_c
WHERE pattribute.p_partner_key_n = partner.p_partner_key_n AND p_current_l AND p_category_code_c = 'Phone' AND pattribute.p_attribute_type_c = 'Mobile Phone' LIMIT 1
) AS Mobile";
}
if (AIncludeAlternateTelephone)
{
SelectAlternateTelephone =
@", (
SELECT '+' || (SELECT p_internat_telephone_code_i FROM p_country WHERE p_country_code_c = pattribute.p_value_country_c)|| ' ' || p_value_c
FROM p_partner_attribute AS pattribute
JOIN p_partner_attribute_type ON p_partner_attribute_type.p_attribute_type_c = pattribute.p_attribute_type_c
WHERE pattribute.p_partner_key_n = partner.p_partner_key_n AND NOT p_primary_l AND p_current_l AND p_category_code_c = 'Phone' AND pattribute.p_attribute_type_c = 'Phone' LIMIT 1
) AS AlternateTelephone";
}
if (AIncludeURL)
{
SelectURL =
@", (
SELECT p_value_c
FROM p_partner_attribute AS pattribute
JOIN p_partner_attribute_type ON p_partner_attribute_type.p_attribute_type_c = pattribute.p_attribute_type_c
WHERE pattribute.p_partner_key_n = partner.p_partner_key_n AND p_category_code_c = 'Digital Media' AND pattribute.p_attribute_type_c = 'Web Site' LIMIT 1
) AS URL";
}
DataTable PhoneFaxMailDT = new DataTable();
string Query =
@"SELECT
p_partner_key_n AS partner_key,
(
SELECT '+' || (SELECT p_internat_telephone_code_i FROM p_country WHERE p_country_code_c = pattribute.p_value_country_c)|| ' ' || p_value_c
FROM p_partner_attribute AS pattribute
JOIN p_partner_attribute_type ON p_partner_attribute_type.p_attribute_type_c = pattribute.p_attribute_type_c
WHERE pattribute.p_partner_key_n = partner.p_partner_key_n AND p_primary_l AND p_category_code_c = 'Phone' AND pattribute.p_attribute_type_c != 'Fax' LIMIT 1
) AS Primary_Phone,
(
SELECT p_value_c
FROM p_partner_attribute AS pattribute
JOIN p_partner_attribute_type ON p_partner_attribute_type.p_attribute_type_c = pattribute.p_attribute_type_c
WHERE pattribute.p_partner_key_n = partner.p_partner_key_n AND p_primary_l AND p_category_code_c = 'E-Mail' LIMIT 1
) AS Primary_Email,
(
SELECT '+' || (SELECT p_internat_telephone_code_i FROM p_country WHERE p_country_code_c = pattribute.p_value_country_c)|| ' ' || p_value_c
FROM p_partner_attribute AS pattribute
JOIN p_partner_attribute_type ON p_partner_attribute_type.p_attribute_type_c = pattribute.p_attribute_type_c
WHERE pattribute.p_partner_key_n = partner.p_partner_key_n AND p_current_l AND p_category_code_c = 'Phone' AND pattribute.p_attribute_type_c = 'Fax' LIMIT 1
) AS Fax"
+
SelectMobile
+
SelectAlternateTelephone
+
SelectURL
+
@" FROM p_partner AS partner
WHERE p_partner_key_n IN("
+
String.Join(",", partnerlist) + ")";
ADbAdapter.FPrivateDatabaseObj.ReadTransaction(
ref Transaction,
delegate
{
PhoneFaxMailDT = ADbAdapter.RunQuery(Query, "PhoneFaxMail", Transaction);
});
ADataTable.Columns.Add("Primary_Phone");
ADataTable.Columns.Add("Primary_Email");
ADataTable.Columns.Add("Fax");
if (AIncludeMobile)
{
ADataTable.Columns.Add("Mobile");
IdxMobile = NextIdx;
NextIdx++;
}
if (AIncludeAlternateTelephone)
{
ADataTable.Columns.Add("Alternate_Telephone");
IdxAlternateTelephone = NextIdx;
NextIdx++;
}
if (AIncludeURL)
{
ADataTable.Columns.Add("URL");
IdxURL = NextIdx;
NextIdx++;
}
DataView dv = ADataTable.DefaultView;
foreach (DataRow dr in PhoneFaxMailDT.Rows)
{
dv.RowFilter = ADataTable.Columns[APartnerKeyColumn].ColumnName + " = " + dr[0].ToString();
dv[0]["Primary_Phone"] = dr[1];
dv[0]["Primary_Email"] = dr[2];
dv[0]["Fax"] = dr[3];
if (AIncludeMobile)
{
dv[0]["Mobile"] = dr[IdxMobile];
}
if (AIncludeAlternateTelephone)
{
dv[0]["Alternate_Telephone"] = dr[IdxAlternateTelephone];
}
if (AIncludeURL)
{
dv[0]["URL"] = dr[IdxURL];
}
}
dv.RowFilter = String.Empty;
ADataTable = dv.ToTable();
}
public static DataSet GetRecipientGiftStatementDataSet(Dictionary <String, TVariant> AParameters)
{
string ReportType = AParameters["param_report_type"].ToString();
FDbAdapter = new TReportingDbAdapter(false);
TLogging.SetStatusBarProcedure(WriteToStatusBar);
DataSet ReturnDataSet = new DataSet();
// get recipients
DataTable Recipients = TFinanceReportingWebConnector.RecipientGiftStatementRecipientTable(AParameters, FDbAdapter);
if (FDbAdapter.IsCancelled || (Recipients == null))
{
return(null);
}
DataTable RecipientTotals = new DataTable("RecipientTotals");
RecipientTotals.Columns.Add("PreviousYearTotal", typeof(decimal));
RecipientTotals.Columns.Add("CurrentYearTotal", typeof(decimal));
DataTable Donors = new DataTable("Donors");
foreach (DataRow Row in Recipients.Rows)
{
if (ReportType == "Complete")
{
// get year totals for recipient
RecipientTotals.Merge(TFinanceReportingWebConnector.RecipientGiftStatementTotalsTable(AParameters, (Int64)Row["RecipientKey"],
FDbAdapter));
}
// get donor information for each recipient
Donors.Merge(TFinanceReportingWebConnector.RecipientGiftStatementDonorTable(AParameters, (Int64)Row["RecipientKey"], FDbAdapter));
if (FDbAdapter.IsCancelled)
{
return(null);
}
}
DataView View = new DataView(Donors);
DataTable DistinctDonors = new DataTable();
if (View.Count > 0)
{
DistinctDonors = View.ToTable(true, "DonorKey");
}
DataTable DonorAddresses = new DataTable("DonorAddresses");
if ((ReportType == "Complete") || (ReportType == "Donors Only"))
{
foreach (DataRow Row in DistinctDonors.Rows)
{
// get best address for each distinct donor
DonorAddresses.Merge(TFinanceReportingWebConnector.RecipientGiftStatementDonorAddressesTable(Convert.ToInt64(Row["DonorKey"]),
FDbAdapter));
if (FDbAdapter.IsCancelled)
{
return(null);
}
}
}
else
{
DonorAddresses.Merge(DistinctDonors);
}
// We only want distinct donors for this report (i.e. no more than one per recipient)
if (ReportType == "Donors Only")
{
if (View.Count > 0)
{
DistinctDonors = View.ToTable(true, "DonorKey", "DonorName", "RecipientKey");
Donors.Clear();
Donors.Merge(DistinctDonors);
}
else // I should return an empty table with just columns, to keep the client happy:
{
DistinctDonors = new DataTable();
DistinctDonors.Columns.Add("DonorKey", typeof(Int64));
DistinctDonors.Columns.Add("DonorName", typeof(String));
DistinctDonors.Columns.Add("RecipientKey", typeof(Int64));
}
}
ReturnDataSet.Tables.Add(Recipients);
ReturnDataSet.Tables.Add(RecipientTotals);
ReturnDataSet.Tables.Add(Donors);
ReturnDataSet.Tables.Add(DonorAddresses);
return((FDbAdapter.IsCancelled) ? null : ReturnDataSet);
}
/// <summary>
///
/// </summary>
/// <param name="ADataTable"></param>
/// <param name="APartnerKeyColumn"></param>
/// <param name="AParameters"></param>
/// <param name="ADateKey"></param>
/// <param name="ADbAdapter"></param>
public static void AddFieldNameToTable(DataTable ADataTable,
int APartnerKeyColumn,
Dictionary <String, TVariant> AParameters,
String ADateKey,
TReportingDbAdapter ADbAdapter)
{
TDBTransaction Transaction = new TDBTransaction();
DateTime CurrentDate = DateTime.Today;
if (AParameters.ContainsKey(ADateKey))
{
CurrentDate = AParameters[ADateKey].ToDate();
}
List <string> partnerlist = new List <string>();
foreach (DataRow dr in ADataTable.Rows)
{
partnerlist.Add("(" + dr[APartnerKeyColumn].ToString() + ")");
}
if (partnerlist.Count == 0)
{
partnerlist.Add("(-1)");
}
DataTable PartnerAndField = new DataTable();
string Query = @"
WITH partnertable AS (VALUES " +
String.Join(",",
partnerlist) +
@"),
persontable AS
(SELECT
p_partner_key_n AS Partner_Key,
(SELECT p_partner_short_name_c FROM p_partner WHERE p_partner_key_n = staff.pm_receiving_field_n) AS Field_Name
FROM
pm_staff_data AS staff
WHERE
staff.p_partner_key_n IN((SELECT * FROM partnertable))
AND pm_start_of_commitment_d <= '"
+
CurrentDate.ToString("yyyy-MM-dd") + @"'
AND(pm_end_of_commitment_d >= '" +
CurrentDate.ToString(
"yyyy-MM-dd") +
@"' OR pm_end_of_commitment_d IS NULL)
)
SELECT
partner.p_partner_key_n,
CASE WHEN partner.p_partner_class_c = 'PERSON' THEN string_agg(Field_Name, ',')
ELSE CASE WHEN partner.p_partner_class_c = 'FAMILY' THEN(
SELECT(SELECT p_partner_short_name_c FROM p_partner WHERE p_partner_key_n = p_field_key_n)
FROM p_partner_gift_destination
WHERE p_partner_key_n = partner.p_partner_key_n
AND p_date_effective_d <= '"
+
CurrentDate.ToString("yyyy-MM-dd") + "' AND (p_date_expires_d >= '" + CurrentDate.ToString("yyyy-MM-dd") +
@"
' OR p_date_expires_d = NULL))
ELSE CASE WHEN partner.p_partner_class_c = 'UNIT' THEN
CASE WHEN(SELECT u_unit_type_code_c FROM p_unit WHERE p_unit.p_partner_key_n = partner.p_partner_key_n) IN('A', 'F', 'D')
THEN partner.p_partner_short_name_c
ELSE(
SELECT(SELECT p_partner_short_name_c FROM p_partner WHERE p_partner_key_n = um_parent_unit_key_n)
FROM um_unit_structure
WHERE um_child_unit_key_n = partner.p_partner_key_n
)
END
ELSE ''
END END END AS Field_Name
FROM persontable
RIGHT JOIN p_partner AS partner ON persontable.Partner_Key = partner.p_partner_key_n
WHERE partner.p_partner_key_n IN((SELECT * FROM partnertable))
GROUP BY partner.p_partner_key_n";
ADbAdapter.FPrivateDatabaseObj.ReadTransaction(
ref Transaction,
delegate
{
PartnerAndField = ADbAdapter.RunQuery(Query, "PartnerAndField", Transaction);
});
ADataTable.Columns.Add("Field", typeof(string));
DataView dv = ADataTable.DefaultView;
foreach (DataRow dr in PartnerAndField.Rows)
{
dv.RowFilter = ADataTable.Columns[APartnerKeyColumn].ColumnName + " = " + dr[0].ToString();
dv[0]["Field"] = dr[1];
}
dv.RowFilter = String.Empty;
ADataTable = dv.ToTable();
}
public static DataSet GiftsOverMinimum(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
String StartDate = AParameters["param_start_date"].ToDate().ToString("yyyy-MM-dd");
String EndDate = AParameters["param_end_date"].ToDate().ToString("yyyy-MM-dd");
String MinimumAmount = AParameters["param_minimum_amount"].ToString();
String CurrencyField = (AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n");
String DonorExclude = "";
String MotivationQuery = "";
TDBTransaction Transaction = null;
DataTable Gifts = new DataTable();
DataTable Donors = new DataTable();
DataTable Contacts = new DataTable();
DataSet Results = new DataSet();
#if DEBUG
foreach (String key in AParameters.Keys)
{
TLogging.Log(key + " => " + AParameters[key].ToString());
}
#endif
if (AParameters["param_exclude_anonymous_donors"].ToBool())
{
DonorExclude += "AND Donor.p_anonymous_donor_l = 0 ";
}
if (AParameters["param_exclude_no_solicitations"].ToBool())
{
DonorExclude += "AND Donor.p_no_solicitations_l = 0 ";
}
if (!AParameters["param_all_motivation_groups"].ToBool())
{
MotivationQuery += String.Format("AND a_gift_detail.a_motivation_group_code_c IN ({0}) ",
AParameters["param_motivation_group_quotes"]);
}
if (!AParameters["param_all_motivation_details"].ToBool())
{
MotivationQuery += String.Format("AND (a_gift_detail.a_motivation_group_code_c, a_gift_detail.a_motivation_detail_code_c) IN ({0}) ",
AParameters["param_motivation_group_detail_pairs"]);
}
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String Query =
@"
WITH Details AS (
SELECT
a_gift.p_donor_key_n AS DonorKey
-- We need to join to Donor to check p_anonymous_donor_l and p_no_solicitations_l for the query, so we may as well pull Name etc.
-- at the same time, to avoid doing a second Donor query later. But we'll consolidate this duplicated data into another DataTable
-- to return it to the client.
, Donor.p_partner_short_name_c AS DonorName
, Donor.p_partner_class_c AS DonorClass
, Donor.p_receipt_letter_frequency_c AS ReceiptFrequency
, a_gift.a_date_entered_d AS GiftDate
, a_gift_detail.a_confidential_gift_flag_l AS Confidential
, a_gift_detail.a_motivation_group_code_c AS MotivationGroup
, a_gift_detail.a_motivation_detail_code_c AS MotivationDetail
, a_motivation_group.a_motivation_group_description_c AS MotivationGroupDescription
, a_motivation_detail.a_motivation_detail_desc_c AS MotivationDetailDescription
, a_gift_detail.p_recipient_key_n AS RecipientKey
, a_gift_detail."
+
CurrencyField + @" AS GiftAmount
, sum(a_gift_detail." + CurrencyField +
@") OVER (PARTITION BY a_gift.p_donor_key_n) AS TotalAmount
FROM
a_gift
INNER JOIN
p_partner AS Donor
ON
(Donor.p_partner_key_n = a_gift.p_donor_key_n)
INNER JOIN
a_gift_detail
USING
(a_ledger_number_i, a_batch_number_i, a_gift_transaction_number_i)
INNER JOIN
a_motivation_group
USING
(a_ledger_number_i, a_motivation_group_code_c)
INNER JOIN
a_motivation_detail
USING
(a_ledger_number_i, a_motivation_group_code_c, a_motivation_detail_code_c)
WHERE
a_gift.a_ledger_number_i = "
+
LedgerNumber + @"
AND a_gift.a_date_entered_d BETWEEN '" + StartDate + "' AND '" +
EndDate +
@"'
-- I hope a_dont_report_l gets converted to a_report_l to avoid this horrible double negative:
AND a_motivation_detail.a_dont_report_l = False
"
+
MotivationQuery + DonorExclude +
@"
-- For OM Germany, exclude donors 99000000 and 27002909 (SHKI and anonymous UNBEKANNT)
AND NOT ((a_gift.a_ledger_number_i = 27 OR a_gift.a_ledger_number_i = 90 OR a_gift.a_ledger_number_i = 99)
AND (a_gift.p_donor_key_n = 99000000 OR a_gift.p_donor_key_n = 27002909))
)
SELECT
Details.*
, Recipient.p_partner_short_name_c AS RecipientName
FROM
Details
INNER JOIN
p_partner AS Recipient
ON
(Recipient.p_partner_key_n = Details.RecipientKey)
WHERE
TotalAmount >= "
+
MinimumAmount +
@"
ORDER BY
Details.DonorName
;
";
#if DEBUG
TLogging.Log(Query);
#endif
Gifts = DbAdapter.RunQuery(Query, "GiftsOverMinimum", Transaction);
#if DEBUG
TLogging.Log("Query finished");
#endif
// Get the donors' addresses
const int DONOR_KEY = 0;
const int DONOR_ADDR = 5;
const int DONOR_POSTCODE = 6;
const int DONOR_PHONE = 7;
const int DONOR_EMAIL = 8;
Donors = Gifts.DefaultView.ToTable("Donors", true, "DonorKey", "DonorName", "DonorClass", "ReceiptFrequency", "TotalAmount");
Donors.Columns.Add("Address", typeof(String));
Donors.Columns.Add("PostalCode", typeof(String));
Donors.Columns.Add("Phone", typeof(String));
Donors.Columns.Add("Email", typeof(String));
// Having copied the distinct names and totals from Gifts to Donors, we no longer need to pass their duplicated data back to the client
foreach (String col in new String[] { "DonorName", "DonorClass", "ReceiptFrequency", "TotalAmount" })
{
Gifts.Columns.Remove(col);
}
PLocationTable Address;
String Country, EmailAddress, PhoneNumber, FaxNumber;
List <String> DonorList = new List <string>();
#if DEBUG
TLogging.Log("Processing addresses");
#endif
foreach (DataRow Donor in Donors.Rows)
{
DonorList.Add(Donor[DONOR_KEY].ToString());
if (TAddressTools.GetBestAddress((Int64)Donor[DONOR_KEY], out Address, out Country, Transaction))
{
Donor[DONOR_ADDR] =
Calculations.DetermineLocationString(Address[0], Calculations.TPartnerLocationFormatEnum.plfCommaSeparated);
Donor[DONOR_POSTCODE] = Address[0]["p_postal_code_c"];
}
else
{
Donor[DONOR_ADDR] = "";
Donor[DONOR_POSTCODE] = "";
}
TContactDetailsAggregate.GetPrimaryEmailAndPrimaryPhoneAndFax((Int64)Donor[DONOR_KEY], out PhoneNumber, out EmailAddress,
out FaxNumber);
Donor[DONOR_PHONE] = PhoneNumber;
Donor[DONOR_EMAIL] = EmailAddress;
}
if (DonorList.Count == 0)
{
DonorList.Add("null");
}
#if DEBUG
TLogging.Log(
"Addresses finished");
#endif
// Get the most recent contacts with each donor
Query =
@"
WITH Contacts AS (
SELECT
row_number() OVER (PARTITION BY p_partner_contact.p_partner_key_n ORDER BY s_contact_date_d DESC, s_contact_time_i DESC) AS RowID
, p_partner_contact.p_partner_key_n AS DonorKey
, p_contact_log.p_contactor_c AS Contactor
, p_contact_log.s_contact_date_d AS ContactDate
, p_contact_log.s_contact_time_i AS ContactTime
, p_contact_log.s_contact_time_i * '1 second'::interval AS Time
, p_contact_log.p_contact_code_c AS ContactCode
, p_contact_log.p_contact_comment_c AS Comment
FROM
p_partner_contact
INNER JOIN
p_contact_log
USING
(p_contact_log_id_i)
WHERE
p_partner_key_n in ("
+
String.Join(",",
DonorList) +
@")
ORDER BY
DonorKey,
RowID
)
SELECT
*
FROM
Contacts
WHERE
Contacts.RowID <= "
+
AParameters["param_max_contacts"] + @";
";
#if DEBUG
TLogging.Log(Query);
#endif
Contacts = DbAdapter.RunQuery(Query, "Contacts", Transaction);
#if DEBUG
TLogging.Log("Query finished");
#endif
if (DbAdapter.IsCancelled)
{
Results = null;
return;
}
}); // GetNewOrExistingAutoReadTransaction
Results.Tables.Add(Gifts);
Results.Tables.Add(Donors);
Results.Tables.Add(Contacts);
return(Results);
}
/// <summary>
/// Returns the Partner Keys for UCPartnerSelection as a comma seperated list.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="DbAdapter"></param>
/// <returns></returns>
public static String GetPartnerKeysAsString(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = new TDBTransaction();
if (AParameters["param_selection"].ToString() == "one partner")
{
return(AParameters["param_partnerkey"].ToString());
}
List <string> PartnerKeys = new List <string>();
DataTable Partners = new DataTable();
DbAdapter.FPrivateDatabaseObj.ReadTransaction(
ref Transaction,
delegate
{
string Query = "";
if (AParameters["param_selection"].ToString() == "an extract")
{
Query =
"SELECT p_partner_key_n FROM m_extract WHERE m_extract_id_i = (SELECT m_extract_id_i FROM m_extract_master WHERE m_extract_name_c = '"
+
AParameters["param_extract"].ToString().Replace("'", "''") + "')";
}
else if (AParameters["param_selection"].ToString() == "all current staff")
{
string date = AParameters["param_currentstaffdate"].ToDate().ToString("yyyy-MM-dd");
Query = "SELECT p_partner_key_n FROM pm_staff_data WHERE pm_start_of_commitment_d <= '" + date +
"' AND (pm_end_of_commitment_d >= '" + date + "' OR pm_end_of_commitment_d IS NULL)";
}
if (Query != "")
{
Partners = DbAdapter.RunQuery(Query, "Partners", Transaction);
}
else
{
Partners.Columns.Add("partnerkey");
Partners.Rows.Add(new object[] { 0 });
}
});
if (Partners.Rows.Count == 0)
{
return("-1");
}
else
{
foreach (DataRow dr in Partners.Rows)
{
PartnerKeys.Add(dr[0].ToString());
}
return(String.Join(",", PartnerKeys));
}
}
public static DataTable APCurrentPayable(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string DateSelection = AParameters["param_payment_date"].ToDate().ToString("yyyy-MM-dd");
// create new datatable
DataTable APCurrentPayable = new DataTable();
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String Query =
@"SELECT
p_partner.p_partner_key_n,
p_partner_short_name_c,
a_ap_document.a_ap_document_id_i AS DocId,
a_ap_document.a_total_amount_n AS APAmount,
a_ap_document.a_ap_number_i AS APNumber,
a_ap_document.a_credit_note_flag_l AS IsCredit,
a_ap_document.a_date_issued_d AS IssueDate,
a_ap_document.a_credit_terms_i AS CreditTerms,
a_ap_document.a_document_code_c AS DocumentCode,
a_ap_document.a_reference_c AS Reference,
a_ap_document.a_discount_days_i AS DiscountDays,
a_ap_supplier.a_currency_code_c,
a_ap_document.a_document_status_c,
a_exchange_rate_to_base_n
FROM a_ap_document
JOIN a_ap_supplier ON a_ap_document.p_partner_key_n = a_ap_supplier.p_partner_key_n
JOIN p_partner ON p_partner.p_partner_key_n = a_ap_document.p_partner_key_n
WHERE a_ap_document.a_ledger_number_i = "
+
LedgerNumber + @"
AND a_ap_document.a_date_entered_d <= '" + DateSelection +
@"'
AND a_ap_document.a_document_status_c <> 'CANCELLED'
AND a_ap_document.a_document_status_c <> 'PAID'";
APCurrentPayable = DbAdapter.RunQuery(Query, "APCurrentPayable", Transaction);
});
return(APCurrentPayable);
}
public static DataTable RecipientGiftStatementRecipientTable(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string RecipientSelection = AParameters["param_recipient"].ToString();
string OrderBy = AParameters["param_order_by_name"].ToString();
/*
* string ReportType = string.Empty;
*
* if (AParameters.ContainsKey("param_report_type"))
* {
* ReportType = AParameters["param_report_type"].ToString();
* }
*/
DateTime CurrentDate = DateTime.Today;
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.BeginAutoReadTransaction(
ref Transaction,
delegate
{
string Query = "SELECT DISTINCT" +
" Recipient.p_partner_key_n AS RecipientKey," +
" Recipient.p_partner_short_name_c AS RecipientName," +
" Recipient.p_partner_class_c AS RecipientClass," +
" CASE WHEN EXISTS (SELECT 1 FROM PUB_p_partner WHERE PUB_p_partner.p_partner_key_n = PUB_p_partner_gift_destination.p_field_key_n"
+
" OR PUB_p_partner.p_partner_key_n = um_unit_structure.um_child_unit_key_n)" +
" THEN PUB_p_partner.p_partner_short_name_c " +
" ELSE 'UNKNOWN'" +
" END AS FieldName," +
" CASE WHEN EXISTS (SELECT 1 FROM PUB_p_partner WHERE PUB_p_partner.p_partner_key_n = PUB_p_partner_gift_destination.p_field_key_n"
+
" OR PUB_p_partner.p_partner_key_n = um_unit_structure.um_child_unit_key_n)" +
" THEN PUB_p_partner.p_partner_key_n " +
" ELSE 0" +
" END AS FieldKey" +
" FROM" +
" PUB_a_gift as gift, " +
" PUB_a_gift_detail AS detail," +
" PUB_a_gift_batch," +
" PUB_p_partner AS Recipient" +
" LEFT JOIN PUB_p_partner_gift_destination" +
" ON Recipient.p_partner_class_c = 'FAMILY'" +
" AND PUB_p_partner_gift_destination.p_partner_key_n = Recipient.p_partner_key_n" +
" AND PUB_p_partner_gift_destination.p_date_effective_d <= '" + CurrentDate.ToString("yyyy-MM-dd") + "'" +
" AND (PUB_p_partner_gift_destination.p_date_expires_d IS NULL" +
" OR (PUB_p_partner_gift_destination.p_date_expires_d >= '" + CurrentDate.ToString(
"yyyy-MM-dd") + "'" +
" AND PUB_p_partner_gift_destination.p_date_effective_d <> PUB_p_partner_gift_destination.p_date_expires_d))" +
" LEFT JOIN um_unit_structure" +
" ON Recipient.p_partner_class_c = 'UNIT'" +
" AND um_unit_structure.um_child_unit_key_n = Recipient.p_partner_key_n" +
" LEFT JOIN PUB_p_partner" +
" ON (PUB_p_partner.p_partner_key_n = PUB_p_partner_gift_destination.p_field_key_n" +
" AND EXISTS (SELECT * FROM PUB_p_partner_gift_destination WHERE PUB_p_partner_gift_destination.p_partner_key_n = Recipient.p_partner_key_n))"
+
" OR (PUB_p_partner.p_partner_key_n = um_unit_structure.um_parent_unit_key_n" +
" AND EXISTS (SELECT * FROM um_unit_structure WHERE um_unit_structure.um_child_unit_key_n = Recipient.p_partner_key_n))";
if (RecipientSelection == "Extract")
{
Query += ", PUB_m_extract," +
" PUB_m_extract_master";
}
Query += " WHERE";
if (RecipientSelection == "Extract")
{
Query += " detail.p_recipient_key_n = PUB_m_extract.p_partner_key_n" +
" AND PUB_m_extract.m_extract_id_i = PUB_m_extract_master.m_extract_id_i" +
" AND PUB_m_extract_master.m_extract_name_c = '" + AParameters["param_extract_name"].ToString() + "'" +
" AND";
}
Query += " detail.a_ledger_number_i = gift.a_ledger_number_i" +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i" +
" AND gift.a_date_entered_d BETWEEN '" + AParameters["param_from_date"].ToDate().ToString("yyyy-MM-dd") +
"' AND '" + AParameters["param_to_date"].ToDate().ToString("yyyy-MM-dd") + "'" +
" AND gift.a_ledger_number_i = " + LedgerNumber +
" AND PUB_a_gift_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_gift_batch.a_batch_number_i = gift.a_batch_number_i" +
" AND PUB_a_gift_batch.a_ledger_number_i = " + LedgerNumber +
" AND Recipient.p_partner_key_n = detail.p_recipient_key_n";
if (RecipientSelection == "One Recipient")
{
Query += " AND detail.p_recipient_key_n = " + AParameters["param_recipientkey"].ToInt64();
}
if (OrderBy == "RecipientField")
{
Query += " ORDER BY FieldName, RecipientKey";
}
else if (OrderBy == "RecipientKey")
{
Query += " ORDER BY RecipientKey";
}
else if (OrderBy == "RecipientName")
{
Query += " ORDER BY RecipientName";
}
Results = DbAdapter.RunQuery(Query, "Recipients", Transaction);
});
return(Results);
}
public static DataSet APAccountDetail(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
TLedgerInfo LedgerInfo = new TLedgerInfo(LedgerNumber);
string LedgerCurrency = LedgerInfo.BaseCurrency;
string NumFrom = "";
string NumTo = "";
string DateFrom = "";
string DateTo = "";
if (AParameters["param_from_date"].ToString() != String.Empty)
{
DateFrom = " AND a_transaction.a_transaction_date_d >= '" + AParameters["param_from_date"].ToDate().ToString("yyyy-MM-dd") + "' ";
}
if (AParameters["param_to_date"].ToString() != String.Empty)
{
DateTo = " AND a_transaction.a_transaction_date_d <= '" + AParameters["param_to_date"].ToDate().ToString("yyyy-MM-dd") + "' ";
}
if (AParameters["param_account_from"].ToString() != String.Empty)
{
NumFrom = " AND a_transaction.a_account_code_c >= '" + AParameters["param_account_from"].ToString() + "' ";
}
if (AParameters["param_account_to"].ToString() != String.Empty)
{
NumTo = " AND a_transaction.a_account_code_c <= '" + AParameters["param_account_to"].ToString() + "' ";
}
DataSet ReturnDataSet = new DataSet();
// create new datatable
DataTable Accounts = new DataTable();
DataTable Details = new DataTable();
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String Query =
@"SELECT DISTINCT
a_transaction.a_account_code_c AS AccountCode,
a_cost_centre.a_cost_centre_code_c,
a_cost_centre_name_c,
a_account_code_long_desc_c,
a_account_code_short_desc_c,
CASE WHEN a_foreign_currency_flag_l THEN a_foreign_currency_code_c ELSE '"
+
LedgerCurrency +
@"' END AS currency
FROM a_transaction
LEFT JOIN a_cost_centre ON a_cost_centre.a_cost_centre_code_c = a_transaction.a_cost_centre_code_c
JOIN a_account ON a_transaction.a_ledger_number_i=a_account.a_ledger_number_i AND a_transaction.a_account_code_c=a_account.a_account_code_c
WHERE
a_transaction.a_ledger_number_i = "
+
LedgerNumber +
@"
AND a_narrative_c LIKE 'AP%'
AND a_transaction.a_transaction_status_l = true
AND NOT (a_transaction.a_system_generated_l = true
AND a_transaction.a_narrative_c LIKE 'Year end re-allocation%')
"
+
DateFrom + DateTo + NumFrom + NumTo;
Accounts = DbAdapter.RunQuery(Query,
"Accounts",
Transaction);
Query =
@"SELECT
a_transaction.a_account_code_c AS AccountCode,
a_transaction.a_transaction_date_d AS Date,
a_transaction.a_amount_in_base_currency_n AS Amount,
a_transaction.a_debit_credit_indicator_l,
a_transaction.a_narrative_c AS Detail,
a_transaction.a_reference_c AS ReferenceNumber,
a_transaction.a_batch_number_i AS BatchNumber,
a_transaction.a_cost_centre_code_c AS CostCentreCode
FROM a_transaction
WHERE
a_transaction.a_ledger_number_i = "
+
LedgerNumber +
@"
AND a_transaction.a_transaction_status_l = true
AND NOT (a_transaction.a_system_generated_l = true
AND a_transaction.a_narrative_c LIKE 'Year end re-allocation%')
AND a_narrative_c LIKE 'AP%'"
+
DateFrom + DateTo + NumFrom + NumTo + "ORDER BY a_cost_centre_code_c,a_account_code_c";
Details = DbAdapter.RunQuery(Query, "Details", Transaction);
DataView tempView = Details.DefaultView;
tempView.RowFilter = "detail LIKE 'AP Payment:%' AND detail LIKE '% AP: %'";
List <string> invoicesList = new List <string>();
foreach (DataRow row in tempView.ToTable().Rows)
{
invoicesList.AddRange(row["detail"].ToString().Split(new char[] { ' ' },
4)[3].Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries));
}
if (invoicesList.Count != 0)
{
DataTable Invoices =
DbAdapter.RunQuery(
"SELECT a_ap_number_i, a_document_code_c, a_document_code_c || ' (' || a_reference_c || ')' AS newref FROM a_ap_document WHERE a_ap_number_i IN("
+
String.Join(",", invoicesList) + ")", "Invoices", Transaction);
Invoices.PrimaryKey = new DataColumn[] { Invoices.Columns["a_ap_number_i"] };
for (int i = 0; i < Details.Rows.Count; i++)
{
if (Details.Rows[i]["detail"].ToString().Split(':')[0].ToString() == "AP Payment")
{
string detailString = Details.Rows[i]["detail"].ToString().Split(new char[] { ' ' }, 4)[3];
//If it contains a "-" it is a Payment for a supplier
if (!detailString.Contains("-"))
{
string[] InvoiceNumbers = detailString.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries);
List <string> tempList = new List <string>();
foreach (string num in InvoiceNumbers)
{
int IntNum;
if (int.TryParse(num, out IntNum))
{
string Reference = Invoices.Rows.Find(IntNum)["newref"].ToString();
if (Reference == String.Empty)
{
tempList.Add(Catalog.GetString("AP: ") + Invoices.Rows.Find(IntNum)["a_document_code_c"].ToString());
}
else
{
tempList.Add(Catalog.GetString("AP: ") + Reference);
}
}
}
Details.Rows[i]["referencenumber"] = String.Join("; ", tempList);
}
}
}
}
});
ReturnDataSet.Tables.Add(Accounts);
ReturnDataSet.Tables.Add(Details);
return(ReturnDataSet);
}
public static DataTable RecipientGiftStatementTotalsTable(Dictionary <String, TVariant> AParameters,
Int64 ARecipientKey,
TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
int CurrentYear = AParameters["param_from_date"].ToDate().Year;
string Currency = AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n";
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
//TODO: Calendar vs Financial Date Handling - Check if this should use financial year start/end in all places below
string Query = "SELECT " +
" GiftDetail.p_recipient_key_n AS RecipientKey," +
" SUM (" +
" CASE WHEN" +
" Gift.a_date_entered_d >= '" + new DateTime(CurrentYear - 1, 1, 1).ToString("yyyy-MM-dd") + "'" +
" AND Gift.a_date_entered_d <= '" + new DateTime(CurrentYear - 1, 12, 31).ToString("yyyy-MM-dd") + "'" +
" THEN GiftDetail." + Currency +
" ELSE 0" +
" END) AS PreviousYearTotal," +
" SUM (" +
" CASE WHEN" +
" Gift.a_date_entered_d >= '" + new DateTime(CurrentYear, 1, 1).ToString("yyyy-MM-dd") + "'" +
" AND Gift.a_date_entered_d <= '" + AParameters["param_to_date"].ToDate().ToString("yyyy-MM-dd") + "'" +
" THEN GiftDetail." + Currency +
" ELSE 0" +
" END) AS CurrentYearTotal" +
" FROM" +
" PUB_a_gift AS Gift, " +
" PUB_a_gift_detail AS GiftDetail," +
" PUB_a_gift_batch AS GiftBatch" +
" WHERE" +
" GiftDetail.a_ledger_number_i = " + LedgerNumber +
" AND GiftDetail.p_recipient_key_n = " + ARecipientKey +
" AND Gift.a_ledger_number_i = " + LedgerNumber +
" AND Gift.a_batch_number_i = GiftDetail.a_batch_number_i" +
" AND Gift.a_gift_transaction_number_i = GiftDetail.a_gift_transaction_number_i" +
" AND ((Gift.a_date_entered_d >= '" + new DateTime(CurrentYear - 1, 1, 1).ToString("yyyy-MM-dd") + "'" +
" AND Gift.a_date_entered_d <= '" + new DateTime(CurrentYear - 1, 12, 31).ToString("yyyy-MM-dd") + "')" +
" OR (Gift.a_date_entered_d >= '" + new DateTime(CurrentYear, 1, 1).ToString("yyyy-MM-dd") + "'" +
" AND Gift.a_date_entered_d <= '" + AParameters["param_to_date"].ToDate().ToString("yyyy-MM-dd") + "'))" +
" AND GiftBatch.a_ledger_number_i = " + LedgerNumber +
" AND GiftBatch.a_batch_number_i = Gift.a_batch_number_i" +
" AND GiftBatch.a_batch_status_c = 'Posted'" +
" GROUP BY GiftDetail.p_recipient_key_n";
Results = DbAdapter.RunQuery(Query, "RecipientTotals", Transaction);
});
return(Results);
}
public static DataTable GetReportDataTable(String AReportType, Dictionary <String, TVariant> AParameters)
{
TLogging.SetStatusBarProcedure(WriteToStatusBar);
DataTable ResultTbl = null;
switch (AReportType)
{
/* GL Reports */
case "BalanceSheet":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.BalanceSheetTable(AParameters, FDbAdapter);
break;
case "FieldGifts":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.KeyMinGiftsTable(AParameters, FDbAdapter);
break;
case "HOSA":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.HosaGiftsTable(AParameters, FDbAdapter);
break;
case "Stewardship":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.StewardshipTable(AParameters, FDbAdapter);
break;
case "Fees":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.FeesTable(AParameters, FDbAdapter);
break;
case "StewardshipForPeriod":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.StewardshipForPeriodTable(AParameters, FDbAdapter);
break;
case "IncomeExpense":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.IncomeExpenseTable(AParameters, FDbAdapter);
break;
case "AFO":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.AFOTable(AParameters, FDbAdapter);
break;
case "Executive Summary":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.ExecutiveSummaryTable(AParameters, FDbAdapter);
break;
case "TrialBalance":
FDbAdapter = new TReportingDbAdapter(false);
ResultTbl = TFinanceReportingWebConnector.TrialBalanceTable(AParameters, FDbAdapter);
break;
case "SurplusDeficit":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.SurplusDeficitTable(AParameters, FDbAdapter);
break;
/* Gift Reports */
case "GiftBatchDetail":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.GiftBatchDetailTable(AParameters, FDbAdapter);
break;
case "RecipientTaxDeductPct":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.RecipientTaxDeductPctTable(AParameters, FDbAdapter);
break;
case "FieldLeaderGiftSummary":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.FieldLeaderGiftSummary(AParameters, FDbAdapter);
break;
case "TotalGiftsThroughField":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.TotalGiftsThroughField(AParameters, FDbAdapter);
break;
case "MotivationResponse":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.MotivationResponse(AParameters, FDbAdapter);
break;
case "DonorGiftsToField":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.DonorGiftsToField(AParameters, FDbAdapter);
break;
case "GiftDestination":
FDbAdapter = new TReportingDbAdapter(false);
ResultTbl = TFinanceReportingWebConnector.GiftDestination(AParameters, FDbAdapter);
break;
case "TotalForRecipients":
FDbAdapter = new TReportingDbAdapter(false);
ResultTbl = TFinanceReportingWebConnector.TotalForRecipients(AParameters, FDbAdapter);
break;
/* Financial Development */
case "SYBUNT":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.SYBUNTTable(AParameters, FDbAdapter);
break;
case "GiftsOverMinimum":
FDbAdapter = new TReportingDbAdapter(true); // Uses a separate DB Connection.
ResultTbl = TFinanceReportingWebConnector.GiftsOverMinimum(AParameters, FDbAdapter);
break;
default:
TLogging.Log("GetDatatableThread unknown ReportType: " + AReportType);
break;
}
if (FDbAdapter != null)
{
FDbAdapter.CloseConnection();
if (FDbAdapter.IsCancelled)
{
ResultTbl = null;
}
}
return(ResultTbl);
}
public static DataTable GiftsOverMinimum(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
DataTable Results = new DataTable();
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String Query = "SELECT * FROM a_gift_detail WHERE a_batch_number_i > 9800 AND a_gift_transaction_number_i = 1";
Results = DbAdapter.RunQuery(Query, "GiftsOverMinimum", Transaction);
if (DbAdapter.IsCancelled)
{
Results = null;
return;
}
}); // GetNewOrExistingAutoReadTransaction
return(Results);
}