/// <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 = null; if (AParameters["param_selection"].ToString() == "one partner") { return(AParameters["param_partnerkey"].ToString()); } List <string> PartnerKeys = new List <string>(); DataTable Partners = new DataTable(); DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction( IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, 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 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 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 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 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 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); }
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 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 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); }
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 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 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 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 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); }
/// <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(); }
/// <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 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 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); }
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); }