/// <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(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnLedgerNumberId, CriteriaRow, "Ledger", "",
ref CustomWhereCriteria, ref InternalParameters);
}
// Searched DB Field: 'a_batch_number_i'
if ((CriteriaRow["Batch"] != null) && (CriteriaRow["Batch"] != 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(AGiftDetailTable.TableId) + "." + AGiftDetailTable.GetBatchNumberDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["Batch"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_gift_transaction_number_i'
if ((CriteriaRow["Transaction"] != null) && (CriteriaRow["Transaction"] != 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(AGiftDetailTable.TableId) + "." + AGiftDetailTable.GetGiftTransactionNumberDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["Transaction"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_receipt_number_i'
if ((CriteriaRow["Receipt"] != null) && (CriteriaRow["Receipt"] != 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(AGiftTable.TableId) + "." + AGiftTable.GetReceiptNumberDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.Int, 10);
miParam.Value = (object)(CriteriaRow["Receipt"]);
InternalParameters.Add(miParam);
}
// Searched DB Field: 'a_motivation_group_code_c'
if (CriteriaRow["MotivationGroup"].ToString().Length > 0)
{
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnMotivationGroupCodeId, CriteriaRow, "MotivationGroup", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_motivation_detail_code_c'
if (CriteriaRow["MotivationDetail"].ToString().Length > 0)
{
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnMotivationDetailCodeId, CriteriaRow, "MotivationDetail", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_gift_comment_one_c'
if (CriteriaRow["Comment1"].ToString().Length > 0)
{
CriteriaRow.Table.Columns.Add(new DataColumn("Comment1Match"));
CriteriaRow["Comment1Match"] = "CONTAINS";
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnGiftCommentOneId, CriteriaRow, "Comment1", "Comment1Match",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_donor_key_n'
if (((Int64)CriteriaRow["Donor"]) > 0)
{
new TDynamicSearchHelper(AGiftTable.TableId,
AGiftTable.ColumnDonorKeyId, CriteriaRow, "Donor", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'p_recipient_key_n'
if (((Int64)CriteriaRow["Recipient"]) > 0)
{
new TDynamicSearchHelper(AGiftDetailTable.TableId,
AGiftDetailTable.ColumnRecipientKeyId, CriteriaRow, "Recipient", "",
ref CustomWhereCriteria,
ref InternalParameters);
}
// Searched DB Field: 'a_date_entered_d'
if ((CriteriaRow["From"] != System.DBNull.Value) && (CriteriaRow["To"] != System.DBNull.Value) &&
(CriteriaRow["From"] == CriteriaRow["To"]))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
AGiftTable.GetDateEnteredDBName());
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,
AGiftTable.GetDateEnteredDBName());
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,
AGiftTable.GetDateEnteredDBName());
OdbcParameter miParam = new OdbcParameter("", OdbcType.DateTime, 10);
miParam.Value = (object)(CriteriaRow["To"]);
InternalParameters.Add(miParam);
}
}
// Searched DB Field: 'a_gift_amount_n'
if ((CriteriaRow["MinAmount"].ToString().Length > 0) && (CriteriaRow["MaxAmount"].ToString().Length > 0) &&
(CriteriaRow["MinAmount"] == CriteriaRow["MaxAmount"]))
{
CustomWhereCriteria = String.Format("{0} AND {1} = ?", CustomWhereCriteria,
AGiftDetailTable.GetGiftAmountDBName());
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,
AGiftDetailTable.GetGiftAmountDBName());
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,
AGiftDetailTable.GetGiftAmountDBName());
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);
}
