/// <summary>
/// Used internally to build a SQL WHERE criteria from the AFindCriteria HashTable.
///
/// </summary>
/// <param name="ACriteriaData">HashTable containing non-empty Partner Find parameters</param>
/// <param name="AParametersArray">An array holding 1..n instantiated OdbcParameters
/// (including parameter Value)</param>
/// <returns>SQL WHERE criteria
/// </returns>
private static String BuildCustomWhereCriteria(DataTable ACriteriaData, out OdbcParameter[] AParametersArray)
{
String CustomWhereCriteria = "";
DataTable CriteriaDataTable;
DataRow CriteriaRow;
ArrayList InternalParameters;
CriteriaDataTable = ACriteriaData;
CriteriaRow = CriteriaDataTable.Rows[0];
InternalParameters = new ArrayList();
if (CriteriaRow["Ledger"].ToString().Length > 0)
{
// Searched DB Field: 'a_ledger_number_i'
new TDynamicSearchHelper(ATransactionTable.TableId,
ATransactionTable.ColumnLedgerNumberId, CriteriaRow, "Ledger", "",
ref CustomWhereCriteria, ref InternalParameters);
}
// Searched DB Field: 'a_batch_number_i'
if ((CriteriaRow["BatchNumber"] != null) && (CriteriaRow["BatchNumber"] != System.DBNull.Value))
{
// do manually otherwise 0 gets changed to a string and we get a crash
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
"PUB_" + TTypedDataTable.GetTableNameSQL(ATransactionTable.TableId) + "." + ATransactionTable.GetBatchNumberDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["BatchNumber"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_transaction_status_l'
if (!string.IsNullOrEmpty(CriteriaRow["TransactionStatus"].ToString()))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
"CAST (PUB_" + TTypedDataTable.GetTableNameSQL(
ATransactionTable.TableId) + "." + ATransactionTable.GetTransactionStatusDBName() + " as INT)");
OdbcParameter miParam = new OdbcParameter("", DbType.Boolean);
miParam.Value = (object)(CriteriaRow["TransactionStatus"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_batch_description_c'
if (!string.IsNullOrEmpty(CriteriaRow["BatchDescription"].ToString()))
{
CriteriaRow.Table.Columns.Add(new DataColumn("BatchDescriptionMatch"));
CriteriaRow["BatchDescriptionMatch"] = "CONTAINS";
new TDynamicSearchHelper(ABatchTable.TableId,
ABatchTable.ColumnBatchDescriptionId, CriteriaRow, "BatchDescription", "BatchDescriptionMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_journal_description_c'
if (CriteriaRow["JournalDescription"].ToString().Length > 0)
{
CriteriaRow.Table.Columns.Add(new DataColumn("JournalDescriptionMatch"));
CriteriaRow["JournalDescriptionMatch"] = "CONTAINS";
new TDynamicSearchHelper(AJournalTable.TableId,
AJournalTable.ColumnJournalDescriptionId, CriteriaRow, "JournalDescription", "JournalDescriptionMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_narrative_c'
if (CriteriaRow["Narrative"].ToString().Length > 0)
{
CriteriaRow.Table.Columns.Add(new DataColumn("NarrativeMatch"));
CriteriaRow["NarrativeMatch"] = "CONTAINS";
new TDynamicSearchHelper(ATransactionTable.TableId,
ATransactionTable.ColumnNarrativeId, CriteriaRow, "Narrative", "NarrativeMatch",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_cost_centre_code_c'
if (CriteriaRow["CostCentreCode"].ToString().Length > 0)
{
new TDynamicSearchHelper(ATransactionTable.TableId,
ATransactionTable.ColumnCostCentreCodeId, CriteriaRow, "CostCentreCode", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_account_code_c'
if (CriteriaRow["AccountCode"].ToString().Length > 0)
{
new TDynamicSearchHelper(ATransactionTable.TableId,
ATransactionTable.ColumnAccountCodeId, CriteriaRow, "AccountCode", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_transaction_date_d'
if ((CriteriaRow["From"] != System.DBNull.Value) && (CriteriaRow["To"] != System.DBNull.Value) &&
(CriteriaRow["From"] == CriteriaRow["To"]))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
ATransactionTable.GetTransactionDateDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["From"]);
InternalParameters.Add(miParam);
}
else
{
if (CriteriaRow["From"] != System.DBNull.Value)
{
CustomWhereCriteria = String.Format("{0} AND {1} >= ?", CustomWhereCriteria,
ATransactionTable.GetTransactionDateDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["From"]);
InternalParameters.Add(miParam);
}
if (CriteriaRow["To"] != System.DBNull.Value)
{
CustomWhereCriteria = String.Format("{0} AND {1} <= ?", CustomWhereCriteria,
ATransactionTable.GetTransactionDateDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["To"]);
InternalParameters.Add(miParam);
}
}
// Searched DB Field: 'a_amount_in_base_currency_n'
if ((CriteriaRow["MinAmount"].ToString().Length > 0) && (CriteriaRow["MaxAmount"].ToString().Length > 0) &&
(CriteriaRow["MinAmount"] == CriteriaRow["MaxAmount"]))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
ATransactionTable.GetAmountInBaseCurrencyDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MinAmount"]);
InternalParameters.Add(miParam);
}
else
{
if (CriteriaRow["MinAmount"].ToString().Length > 0)
{
CustomWhereCriteria = String.Format("{0} AND {1} >= ?", CustomWhereCriteria,
ATransactionTable.GetAmountInBaseCurrencyDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MinAmount"]);
InternalParameters.Add(miParam);
}
if (CriteriaRow["MaxAmount"].ToString().Length > 0)
{
CustomWhereCriteria = String.Format("{0} AND {1} <= ?", CustomWhereCriteria,
ATransactionTable.GetAmountInBaseCurrencyDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["MaxAmount"]);
InternalParameters.Add(miParam);
}
}
// TLogging.LogAtLevel(7, "CustomWhereCriteria: " + CustomWhereCriteria);
/* Convert ArrayList to a array of ODBCParameters
* seem to need to declare a type first
*/
AParametersArray = ((OdbcParameter[])(InternalParameters.ToArray(typeof(OdbcParameter))));
InternalParameters = null; // ensure this is GC'd
return(CustomWhereCriteria);
}