/// <summary>
/// Procedure to execute a Find query. Although the full query results are retrieved from the DB and stored
/// internally in an object, data will be returned in 'pages' of data, each page holding a defined number
/// of records.
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters.</param>
/// <param name="ADetailedResults">Returns more (when true) or less (when false) columns.</param>
public void PerformSearch(DataTable ACriteriaData, bool ADetailedResults)
{
String CustomWhereCriteria;
Hashtable ColumnNameMapping;
OdbcParameter[] ParametersArray;
String FieldList;
String FromClause;
String WhereClause;
System.Text.StringBuilder sb;
DataRow CriteriaRow;
TLogging.LogAtLevel(7, "TPartnerFind.PerformSearch called.");
FPagedDataSetObject = new TPagedDataSet(new PartnerFindTDSSearchResultTable());
// Build WHERE criteria string based on AFindCriteria
CustomWhereCriteria = BuildCustomWhereCriteria(ACriteriaData, out ParametersArray);
//
// Set up find parameters
//
ColumnNameMapping = new Hashtable();
CriteriaRow = ACriteriaData.Rows[0];
// Create Field List
sb = new System.Text.StringBuilder();
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_partner_class_c", Environment.NewLine);
// short
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_partner_short_name_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_location.p_city_c", Environment.NewLine);
// short
if (ADetailedResults == true)
{
sb.AppendFormat("{0},{1}", "PUB.p_location.p_postal_code_c", Environment.NewLine);
}
if (ADetailedResults == true)
{
sb.AppendFormat("{0},{1}", "PUB.p_location.p_locality_c", Environment.NewLine);
}
sb.AppendFormat("{0},{1}", "PUB.p_location.p_street_name_c", Environment.NewLine);
// short
if (ADetailedResults == true)
{
sb.AppendFormat("{0},{1}", "PUB.p_location.p_address_3_c", Environment.NewLine);
}
if (ADetailedResults == true)
{
sb.AppendFormat("{0},{1}", "PUB.p_location.p_county_c", Environment.NewLine);
}
if (ADetailedResults == true)
{
sb.AppendFormat("{0},{1}", "PUB.p_location.p_country_code_c", Environment.NewLine);
}
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_partner_key_n", Environment.NewLine);
if ((ADetailedResults == true)
&& ((CriteriaRow["PartnerClass"].ToString() == "PERSON")
|| (CriteriaRow["PartnerClass"].ToString() == "*")))
{
sb.AppendFormat("{0},{1}", "PUB.p_person.p_family_key_n", Environment.NewLine);
}
sb.AppendFormat("{0},{1}", "PUB.p_partner_location.p_location_type_c", Environment.NewLine);
if (ADetailedResults == true)
{
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_previous_name_c", Environment.NewLine);
}
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_status_code_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_acquisition_code_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.s_date_created_d", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.s_created_by_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.s_modification_id_t", Environment.NewLine);
// short
sb.AppendFormat("{0},{1}", "PUB.p_location.p_location_key_i", Environment.NewLine);
// short
sb.AppendFormat("{0}{1}", "PUB.p_partner_location.p_site_key_n", Environment.NewLine);
// short
FieldList = sb.ToString();
// Create FROM From Clause
sb = new System.Text.StringBuilder();
System.Text.StringBuilder sbWhereClause = new System.Text.StringBuilder();
// Crude Optimisation
if ((CriteriaRow["locationKey"].ToString().Length > 0) || (CriteriaRow["PostCode"].ToString().Length > 0)
|| (CriteriaRow["Address1"].ToString().Length > 0) || (CriteriaRow["Address2"].ToString().Length > 0)
|| (CriteriaRow["Address3"].ToString().Length > 0) || (CriteriaRow["County"].ToString().Length > 0)
|| (CriteriaRow["Country"].ToString().Length > 0))
{
// If we are searching on p_location fields then
// essential that the FIRST table referenced is p_Location
// or we wait seconds for Progress
sb.AppendFormat("{0}{1}", "PUB.p_location, ", Environment.NewLine);
sb.AppendFormat("{0}{1}", "PUB.p_partner_location", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_partner", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_partner.p_partner_key_n = PUB.p_partner_location.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_person", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_person.p_partner_key_n = PUB.p_partner.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_family", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_family.p_partner_key_n = PUB.p_partner.p_partner_key_n", Environment.NewLine);
sbWhereClause.AppendFormat("{0}{1}", "PUB.p_location.p_location_key_i = PUB.p_partner_location.p_location_key_i", Environment.NewLine);
sbWhereClause.AppendFormat("{0}{1}", "AND PUB.p_location.p_site_key_n = PUB.p_partner_location.p_site_key_n", Environment.NewLine);
}
else
{
// normally p_partner is first table referenced
sb.AppendFormat("{0}{1}", "PUB.p_partner", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_partner_location", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_partner.p_partner_key_n = PUB.p_partner_location.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_person", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_person.p_partner_key_n = PUB.p_partner.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_family", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_family.p_partner_key_n = PUB.p_partner.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0}{1}", ", PUB.p_location", Environment.NewLine);
sbWhereClause.AppendFormat("{0}{1}", "PUB.p_partner_location.p_location_key_i = PUB.p_location.p_location_key_i", Environment.NewLine);
sbWhereClause.AppendFormat("{0}{1}", "AND PUB.p_location.p_site_key_n = PUB.p_partner_location.p_site_key_n", Environment.NewLine);
}
FromClause = sb.ToString();
WhereClause = CustomWhereCriteria;
if (WhereClause.StartsWith(" AND") == true)
{
WhereClause = WhereClause.Substring(4);
}
if (sbWhereClause.ToString().Length > 0)
{
WhereClause += " AND " + sbWhereClause.ToString();
}
FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters(FieldList,
FromClause,
WhereClause,
"PUB.p_partner.p_partner_short_name_c, PUB.p_partner.p_partner_class_c",
ColumnNameMapping,
ParametersArray);
string session = TSession.GetSessionID();
//
// Start the Find Thread
//
try
{
ThreadStart myThreadStart = delegate {
FPagedDataSetObject.ExecuteQuery(session);
};
FFindThread = new Thread(myThreadStart);
FFindThread.Name = "PartnerFindPerformSearch" + Guid.NewGuid().ToString();
FFindThread.Start();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Procedure to execute a Find query. Although the full
/// query results are retrieved from the DB and stored internally in an object,
/// data will be returned in 'pages' of data, each page holding a defined number
/// of records.
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters.</param>
public void PerformSearchByBankDetails(DataTable ACriteriaData)
{
String CustomWhereCriteria;
Hashtable ColumnNameMapping;
OdbcParameter[] ParametersArray;
String FieldList;
String FromClause;
String WhereClause;
System.Text.StringBuilder sb;
TLogging.LogAtLevel(7, "TPartnerFind.PerformSearchByBankDetails called.");
FPagedDataSetObject = new TPagedDataSet(new PartnerFindTDSSearchResultTable());
// Build WHERE criteria string based on AFindCriteria
CustomWhereCriteria = BuildCustomWhereCriteriaForBankDetails(ACriteriaData, out ParametersArray);
//
// Set up find parameters
//
ColumnNameMapping = new Hashtable();
// Create Field List
sb = new System.Text.StringBuilder();
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_partner_class_c", Environment.NewLine);
// short
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_partner_short_name_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_status_code_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.p_acquisition_code_c", Environment.NewLine);
// search by bank details
sb.AppendFormat("{0},{1}", "PUB.p_banking_details.p_banking_details_key_i", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_banking_details.p_account_name_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_banking_details.p_bank_account_number_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_banking_details.p_iban_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_banking_details.p_expiry_date_d", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_banking_details.p_comment_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_bank.p_bic_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_bank.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_bank.p_branch_name_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_bank.p_branch_code_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.s_date_created_d", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner.s_created_by_c", Environment.NewLine);
sb.AppendFormat("{0}{1}", "PUB.p_partner.s_modification_id_t", Environment.NewLine);
// short
FieldList = sb.ToString();
// Create FROM From Clause
sb = new System.Text.StringBuilder();
System.Text.StringBuilder sbWhereClause = new System.Text.StringBuilder();
sb.AppendFormat("{0},{1}", "PUB.p_banking_details", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_bank", Environment.NewLine);
sb.AppendFormat("{0}{1}", "PUB.p_partner_banking_details", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_partner", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_partner.p_partner_key_n = PUB.p_partner_banking_details.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_person", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_person.p_partner_key_n = PUB.p_partner.p_partner_key_n", Environment.NewLine);
sb.AppendFormat("{0}{1}", "LEFT OUTER JOIN PUB.p_family", Environment.NewLine);
sb.AppendFormat("{0}{1}", "ON PUB.p_family.p_partner_key_n = PUB.p_partner.p_partner_key_n", Environment.NewLine);
sbWhereClause.AppendFormat("{0}{1}",
"PUB.p_banking_details.p_banking_details_key_i = PUB.p_partner_banking_details.p_banking_details_key_i",
Environment.NewLine);
sbWhereClause.AppendFormat("{0}{1}", "AND PUB.p_bank.p_partner_key_n = PUB.p_banking_details.p_bank_key_n", Environment.NewLine);
FromClause = sb.ToString();
WhereClause = CustomWhereCriteria;
if (WhereClause.StartsWith(" AND") == true)
{
WhereClause = WhereClause.Substring(4);
}
if (sbWhereClause.ToString().Length > 0)
{
WhereClause += " AND " + sbWhereClause.ToString();
}
FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters(FieldList,
FromClause,
WhereClause,
"PUB.p_partner.p_partner_short_name_c, PUB.p_partner.p_partner_class_c",
ColumnNameMapping,
ParametersArray);
string session = TSession.GetSessionID();
//
// Start the Find Thread
//
try
{
ThreadStart myThreadStart = delegate {
FPagedDataSetObject.ExecuteQuery(session);
};
FFindThread = new Thread(myThreadStart);
FFindThread.Start();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Procedure to execute a Find query. Although the full
/// query results are retrieved from the DB and stored internally in an object,
/// data will be returned in 'pages' of data, each page holding a defined number
/// of records.
///
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters</param>
/// <returns>void</returns>
public void PerformSearch(DataTable ACriteriaData)
{
String CustomWhereCriteria;
Hashtable ColumnNameMapping;
OdbcParameter[] ParametersArray;
String FieldList;
String FromClause;
String WhereClause;
System.Text.StringBuilder sb;
TLogging.LogAtLevel(7, "TGiftDetailFind.PerformSearch called.");
FAsyncExecProgress = new TAsynchronousExecutionProgress();
FPagedDataSetObject = new TPagedDataSet(new GiftBatchTDSAGiftDetailTable());
/* Pass the TAsynchronousExecutionProgress object to FPagedDataSetObject so that it
* can update execution status */
FPagedDataSetObject.AsyncExecProgress = FAsyncExecProgress;
// Register Event Handler for the StopAsyncronousExecution event
FAsyncExecProgress.StopAsyncronousExecution += new System.EventHandler(this.StopSearch);
// Build WHERE criteria string based on AFindCriteria
CustomWhereCriteria = BuildCustomWhereCriteria(ACriteriaData, out ParametersArray);
//
// Set up find parameters
//
ColumnNameMapping = new Hashtable();
// Create Field List
sb = new System.Text.StringBuilder();
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_batch_number_i", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_gift_transaction_number_i", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_detail_number_i", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_confidential_gift_flag_l", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_gift_amount_n", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift.a_receipt_number_i", Environment.NewLine);
sb.AppendFormat("{0},{1}", "DonorPartner.p_partner_short_name_c DonorPartnerShortName", Environment.NewLine);
sb.AppendFormat("{0},{1}", "RecipientPartner.p_partner_short_name_c RecipientPartnerShortName", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_motivation_group_code_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_motivation_detail_code_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift.a_date_entered_d", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_cost_centre_code_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_gift_comment_one_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_gift_comment_two_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail.a_gift_comment_three_c", Environment.NewLine);
sb.AppendFormat("{0}{1}", "PUB.a_gift_batch.a_batch_status_c", Environment.NewLine);
// short
FieldList = sb.ToString();
// Create FROM From Clause
sb = new System.Text.StringBuilder();
System.Text.StringBuilder sbWhereClause = new System.Text.StringBuilder();
sb.AppendFormat("{0},{1}", "PUB.a_gift_detail", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_gift_batch", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.p_partner DonorPartner", Environment.NewLine);
sb.AppendFormat("{0}{1}", "PUB.p_partner RecipientPartner", Environment.NewLine);
sbWhereClause.AppendFormat("{0}{1}",
"DonorPartner.p_partner_key_n = PUB.a_gift.p_donor_key_n " +
"AND RecipientPartner.p_partner_key_n = PUB.a_gift_detail.p_recipient_key_n " +
"AND PUB.a_gift.a_ledger_number_i = PUB.a_gift_detail.a_ledger_number_i " +
"AND PUB.a_gift.a_batch_number_i = PUB.a_gift_detail.a_batch_number_i " +
"AND PUB.a_gift.a_gift_transaction_number_i = PUB.a_gift_detail.a_gift_transaction_number_i " +
"AND PUB.a_gift_batch.a_ledger_number_i = PUB.a_gift_detail.a_ledger_number_i " +
"AND PUB.a_gift_batch.a_batch_number_i = PUB.a_gift_detail.a_batch_number_i", Environment.NewLine);
FromClause = sb.ToString();
WhereClause = CustomWhereCriteria;
if (WhereClause.StartsWith(" AND") == true)
{
WhereClause = WhereClause.Substring(4);
}
if (sbWhereClause.ToString().Length > 0)
{
WhereClause += " AND " + sbWhereClause.ToString();
}
FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters(FieldList,
FromClause,
WhereClause,
"PUB.a_gift_detail.a_batch_number_i, PUB.a_gift_detail.a_gift_transaction_number_i, PUB.a_gift_detail.a_detail_number_i",
ColumnNameMapping,
ParametersArray);
//
// Start the Find Thread
//
try
{
FFindThread = new Thread(new ThreadStart(FPagedDataSetObject.ExecuteQuery));
FFindThread.Start();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// contructor
/// </summary>
/// <param name="ACriteriaData"></param>
public TPartnerLocationFindUIConnector(DataTable ACriteriaData) : base()
{
Hashtable ColumnNameMapping;
String CustomWhereCriteria;
ArrayList InternalParameters;
OdbcParameter miParam;
DataRow CriteriaRow;
FAsyncExecProgress = new TAsynchronousExecutionProgress();
FPagedDataSetObject = new TPagedDataSet(new PartnerFindTDSSearchResultTable());
FPagedDataSetObject.AsyncExecProgress = FAsyncExecProgress;
ColumnNameMapping = null;
// get the first and only row
CriteriaRow = ACriteriaData.Rows[0];
// used to help with strong typing of columns
InternalParameters = new ArrayList();
CustomWhereCriteria = "";
if (CriteriaRow["Addr1"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnLocalityId, CriteriaRow, "Addr1", "Addr1Match", ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["Street2"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnStreetNameId, CriteriaRow, "Street2", "Street2Match",
ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["Addr3"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnAddress3Id, CriteriaRow, "Addr3", "Addr3Match", ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["City"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnCityId, CriteriaRow, "City", "CityMatch", ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["PostCode"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnPostalCodeId, CriteriaRow, "PostCode", "PostCodeMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["County"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnCountyId, CriteriaRow, "County", "CountyMatch", ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["Country"].ToString().Length > 0)
{
new TDynamicSearchHelper(PLocationTable.TableId,
PLocationTable.ColumnCountryCodeId, CriteriaRow, "Country", "CountryMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
if (CriteriaRow["LocationKey"].ToString().Length > 0)
{
// DISREGARD ALL OTHER SEARCH CRITERIA!!!
CustomWhereCriteria = "";
InternalParameters = new ArrayList();
CustomWhereCriteria = String.Format("{0} AND PUB.{1}.{2} = ?", CustomWhereCriteria,
PLocationTable.GetTableDBName(), PLocationTable.GetLocationKeyDBName());
miParam = new OdbcParameter("", OdbcType.Decimal, 10);
miParam.Value = (object)CriteriaRow["LocationKey"];
InternalParameters = new ArrayList();
InternalParameters.Add(miParam);
}
Console.WriteLine("WHERE CLAUSE: " + CustomWhereCriteria);
FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters(
" p_city_c, p_postal_code_c, p_locality_c, p_street_name_c, p_address_3_c, p_county_c, p_country_code_c, p_location_key_i, p_site_key_n ",
"PUB_p_location ",
" p_location_key_i<>-1 " + CustomWhereCriteria + ' ',
"p_city_c ",
ColumnNameMapping,
((OdbcParameter[])(InternalParameters.ToArray(typeof(OdbcParameter)))));
// fields
// table
// where
// order by
// both empty for now
try
{
ThreadStart ThreadStartDelegate = new ThreadStart(FPagedDataSetObject.ExecuteQuery);
FFindThread = new Thread(ThreadStartDelegate);
FFindThread.Start();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Procedure to execute a Find query. Although the full query results are retrieved from the DB and stored
/// internally in an object, data will be returned in 'pages' of data, each page holding a defined number
/// of records.
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Find parameters.</param>
public void PerformSearch(DataTable ACriteriaData)
{
string PaymentNumberSQLPart;
FPagedDataSetObject = new TPagedDataSet(null);
DataRow CriteriaRow = PrepareDataRow(ACriteriaData);
Int32 ledgerNumber = (Int32)CriteriaRow["LedgerNumber"];
if (FSearchTransactions)
{
if (CommonTypes.ParseDBType(DBAccess.GDBAccessObj.DBType) == TDBType.SQLite)
{
// Fix for SQLite: it does not support the 'to_char' Function
PaymentNumberSQLPart = "PUB_a_ap_payment.a_payment_number_i as InvNum, ";
}
else
{
// whereas PostgreSQL does!
PaymentNumberSQLPart = "to_char(PUB_a_ap_payment.a_payment_number_i, '99999') as InvNum, ";
}
Int64 PartnerKey = Convert.ToInt64(CriteriaRow["PartnerKey"]);
String SqlQuery = "SELECT DISTINCT " +
"0 as ApDocumentId, " +
"PUB_a_ap_payment.a_payment_number_i as ApNum, " +
PaymentNumberSQLPart +
"true as CreditNote, " +
"'Payment' as Type, " +
"PUB_a_ap_payment.a_currency_code_c as Currency, " +
"PUB_a_ap_payment.a_amount_n as Amount, " +
"0 AS OutstandingAmount, " +
"'' as Status, " +
"0 as DiscountPercent, " +
"0 as DiscountDays, " +
"PUB_a_ap_payment.s_date_created_d as Date " +
" FROM PUB_a_ap_payment LEFT JOIN PUB_a_ap_document_payment on PUB_a_ap_payment.a_payment_number_i = PUB_a_ap_document_payment.a_payment_number_i"
+
" LEFT JOIN PUB_a_ap_document on PUB_a_ap_document_payment.a_ap_document_id_i = PUB_a_ap_document.a_ap_document_id_i\n"
+
" WHERE PUB_a_ap_document_payment.a_ledger_number_i=" + ledgerNumber +
" AND p_partner_key_n=" + PartnerKey +
"\n UNION\n" +
" SELECT " +
"a_ap_document_id_i as ApDocumentId, " +
"a_ap_number_i as ApNum, " +
"a_document_code_c as InvNum, " +
"a_credit_note_flag_l as CreditNote, " +
"'Invoice' as Type, " +
"a_currency_code_c AS Currency, " +
"a_total_amount_n as Amount, " +
"a_total_amount_n AS OutstandingAmount, " +
"a_document_status_c as Status, " +
"a_discount_percentage_n as DiscountPercent, " +
"a_discount_days_i as DiscountDays, " +
"a_date_issued_d as Date " +
"FROM PUB_a_ap_document " +
"WHERE a_ledger_number_i=" + ledgerNumber + " " +
"AND p_partner_key_n=" + PartnerKey + " " +
"ORDER BY Date DESC";
FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters(SqlQuery);
FPagedDataSetObject.FindParameters.FSearchName = "Transactions";
}
else
{
if (!FSearchSupplierOrInvoice)
{
String SqlQuery = "SELECT " +
"PUB_a_ap_document.a_ap_number_i AS ApNumber, " +
"PUB_a_ap_document.a_document_code_c AS DocumentCode, " +
"PUB_p_partner.p_partner_short_name_c AS PartnerShortName, " +
"PUB_a_ap_document.a_currency_code_c AS CurrencyCode, " +
"PUB_a_ap_document.a_total_amount_n AS TotalAmount, " +
"PUB_a_ap_document.a_total_amount_n AS OutstandingAmount, " +
"PUB_a_ap_document.a_document_status_c AS DocumentStatus, " +
"PUB_a_ap_document.a_date_issued_d AS DateIssued, " +
"PUB_a_ap_document.a_date_issued_d AS DateDue, " +
"PUB_a_ap_document.a_date_issued_d AS DateDiscountUntil, " +
"PUB_a_ap_document.a_credit_terms_i AS CreditTerms, " +
"PUB_a_ap_document.a_discount_percentage_n AS DiscountPercentage, " +
"PUB_a_ap_document.a_discount_days_i AS DiscountDays, " +
"'none' AS DiscountMsg, " +
"false AS Selected, " +
"PUB_a_ap_document.a_credit_note_flag_l AS CreditNoteFlag, " +
"PUB_a_ap_document.a_ap_document_id_i AS ApDocumentId " +
"FROM PUB_a_ap_document, PUB_a_ap_supplier, PUB_p_partner " +
"WHERE PUB_a_ap_document.a_ledger_number_i=" + ledgerNumber + " " +
"AND PUB_a_ap_document.a_document_status_c <> 'CANCELLED' " +
"AND PUB_a_ap_document.a_document_status_c <> 'PAID' " +
"AND PUB_a_ap_supplier.p_partner_key_n = PUB_p_partner.p_partner_key_n " +
"AND PUB_a_ap_document.p_partner_key_n = PUB_p_partner.p_partner_key_n " +
"ORDER BY PUB_a_ap_document.a_ap_number_i DESC";
FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters(SqlQuery);
FPagedDataSetObject.FindParameters.FSearchName = "Invoices";
}
else
{
String SqlQuery = "SELECT " +
"PUB_a_ap_supplier.p_partner_key_n AS PartnerKey, " +
"PUB_p_partner.p_partner_short_name_c AS PartnerShortName, " +
"PUB_a_ap_supplier.a_currency_code_c AS CurrencyCode, " +
"PUB_p_partner.p_status_code_c AS StatusCode " +
"FROM PUB_a_ap_supplier, PUB_p_partner " +
"WHERE ";
if (((String)CriteriaRow["SupplierId"]).Length > 0) // If the search box is empty, I'll not add this at all...
{
SqlQuery += String.Format("p_partner_short_name_c LIKE '{0}' AND ", (String)CriteriaRow["SupplierId"] + "%");
}
SqlQuery += "PUB_a_ap_supplier.p_partner_key_n = PUB_p_partner.p_partner_key_n " +
"ORDER BY PartnerShortName";
FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters(SqlQuery);
FPagedDataSetObject.FindParameters.FSearchName = "Suppliers";
}
}
string session = TSession.GetSessionID();
//
// Start the Find Thread
//
try
{
ThreadStart myThreadStart = delegate {
FPagedDataSetObject.ExecuteQuery(session);
};
FFindThread = new Thread(myThreadStart);
FFindThread.Name = "APFind" + Guid.NewGuid().ToString();
FFindThread.Start();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Procedure to execute a Find query. Although the full query results are retrieved from the DB and stored
/// internally in an object, data will be returned in 'pages' of data, each page holding a defined number
/// of records.
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Find parameters.</param>
public void PerformSearch(DataTable ACriteriaData)
{
String CustomWhereCriteria;
Hashtable ColumnNameMapping;
OdbcParameter[] ParametersArray;
String FieldList;
String FromClause;
String WhereClause;
System.Text.StringBuilder sb;
TLogging.LogAtLevel(7, "TGLTransactionFind.PerformSearch called.");
FPagedDataSetObject = new TPagedDataSet(new ATransactionTable());
// Build WHERE criteria string based on AFindCriteria
CustomWhereCriteria = BuildCustomWhereCriteria(ACriteriaData, out ParametersArray);
//
// Set up find parameters
//
ColumnNameMapping = new Hashtable();
// Create Field List
sb = new System.Text.StringBuilder();
sb.AppendFormat("{0},{1}", "PUB_a_transaction.*", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_journal.a_journal_description_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_batch.a_batch_description_c", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB.a_batch.a_batch_period_i", Environment.NewLine);
sb.AppendFormat("{0}{1}", "PUB.a_batch.a_batch_year_i", Environment.NewLine);
// short
FieldList = sb.ToString();
// Create FROM From Clause
sb = new System.Text.StringBuilder();
System.Text.StringBuilder sbWhereClause = new System.Text.StringBuilder();
sb.AppendFormat("{0},{1}", "PUB_a_transaction", Environment.NewLine);
sb.AppendFormat("{0},{1}", "PUB_a_journal", Environment.NewLine);
sb.AppendFormat("{0}{1}", "PUB_a_batch", Environment.NewLine);
sbWhereClause.AppendFormat("{0}{1}",
"PUB_a_transaction.a_ledger_number_i = PUB_a_batch.a_ledger_number_i " +
"AND PUB_a_transaction.a_batch_number_i = PUB_a_batch.a_batch_number_i " +
"AND PUB_a_transaction.a_ledger_number_i = PUB_a_journal.a_ledger_number_i " +
"AND PUB_a_transaction.a_batch_number_i = PUB_a_journal.a_batch_number_i " +
"AND PUB_a_transaction.a_journal_number_i = PUB_a_journal.a_journal_number_i", Environment.NewLine);
FromClause = sb.ToString();
WhereClause = CustomWhereCriteria;
if (WhereClause.StartsWith(" AND") == true)
{
WhereClause = WhereClause.Substring(4);
}
if (sbWhereClause.ToString().Length > 0)
{
WhereClause += " AND " + sbWhereClause.ToString();
}
FPagedDataSetObject.FindParameters = new TPagedDataSet.TAsyncFindParameters(FieldList,
FromClause,
WhereClause,
"PUB_a_transaction.a_batch_number_i, PUB_a_transaction.a_journal_number_i, PUB_a_transaction.a_transaction_number_i",
ColumnNameMapping,
ParametersArray);
string session = TSession.GetSessionID();
//
// Start the Find Thread
//
try
{
ThreadStart myThreadStart = delegate {
FPagedDataSetObject.ExecuteQuery(session);
};
FFindThread = new Thread(myThreadStart);
FFindThread.Name = "GLTransactionFind" + Guid.NewGuid().ToString();
FFindThread.Start();
}
catch (Exception)
{
throw;
}
}