public static ArrayList GetEmpMultiFilterParams(ref string sqlStr, Hashtable hashTable, DBUtil dbUtil)
{
ArrayList listParms = new ArrayList();
UtilService utilService = new UtilService();
if (hashTable.Contains("@EmpName"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@EmpName");
listParms.Add(new SqlParameter("@EmpName", SqlDbType.NVarChar, 40));
}
if (hashTable.Contains("@IdCard"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@IdCard");
listParms.Add(new SqlParameter("@IdCard", SqlDbType.VarChar, 20));
}
if (hashTable.Contains("@DeptID"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@DeptID");
listParms.Add(new SqlParameter("@DeptID", SqlDbType.VarChar, 800));
}
if (hashTable.Contains("@EmpTypeID"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@EmpTypeID");
listParms.Add(new SqlParameter("@EmpTypeID", SqlDbType.VarChar, 800));
}
if (hashTable.Contains("@StatusKBN"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@StatusKBN");
listParms.Add(new SqlParameter("@StatusKBN", SqlDbType.VarChar, 800));
}
if (hashTable.Contains("@EntryTimeStart"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@EntryTimeStart");
listParms.Add(new SqlParameter("@EntryTimeStart", SqlDbType.DateTime));
}
if (hashTable.Contains("@EntryTimeEnd"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@EntryTimeEnd");
listParms.Add(new SqlParameter("@EntryTimeEnd", SqlDbType.DateTime));
}
if (hashTable.Contains("@LeaveTimeStart"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@LeaveTimeStart");
listParms.Add(new SqlParameter("@LeaveTimeStart", SqlDbType.DateTime));
}
if (hashTable.Contains("@LeaveTimeEnd"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@LeaveTimeEnd");
listParms.Add(new SqlParameter("@LeaveTimeEnd", SqlDbType.DateTime));
}
return listParms;
}
public List<Customer> searchCusByMultiFilter(Hashtable hashTable)
{
List<Customer> cusList = new List<Customer>();
string sqlStr = dbUtil.getSqlStatement("SQL_Cus_SearchByMultiFilter");
ArrayList listParms = new ArrayList();
UtilService utilService = new UtilService();
if (hashTable.Contains("@CusID"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@CusID");
listParms.Add(new SqlParameter("@CusID", SqlDbType.VarChar, 20));
}
if (hashTable.Contains("@CusName"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@CusName");
listParms.Add(new SqlParameter("@CusName", SqlDbType.NVarChar, 100));
}
if (hashTable.Contains("@CusCompName"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@CusCompName");
listParms.Add(new SqlParameter("@CusCompName", SqlDbType.NVarChar, 100));
}
if (hashTable.Contains("@TelNum"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@TelNum");
listParms.Add(new SqlParameter("@TelNum", SqlDbType.VarChar, 20));
}
if (hashTable.Contains("@CusTypeID"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@CusTypeID");
listParms.Add(new SqlParameter("@CusTypeID", SqlDbType.VarChar, 20));
}
SqlParameter[] sqlParms = (SqlParameter[])listParms.ToArray(typeof(SqlParameter));
foreach (SqlParameter parm in sqlParms)
{
parm.Value = hashTable[parm.ParameterName];
}
DataSet searchResult = null;
try
{
searchResult = DAO.DBAccess.ExecuteAdapter(DAO.DBAccess.SQLConnectionString, CommandType.Text, sqlStr, sqlParms);
foreach (DataTable dt in searchResult.Tables)
{
foreach (DataRow dr in dt.Rows)
{
Customer cus = new Customer();
cus.CustomId = dr["CusID"].ToString();
cus.Name = dr["CusName"].ToString();
cus.CompanyName = dr["CusCompName"].ToString();
cus.CusTypeId = dr["CusTypeID"].ToString();
cus.TelNum1 = dr["Tel1"].ToString();
cus.TelNum2 = dr["Tel2"].ToString();
cus.ZipCode = dr["ZipCode"].ToString();
cus.Address = dr["CusAddress"].ToString();
cus.RebateTypeId = dr["RebateTypeID"].ToString();
cusList.Add(cus);
}
}
}
catch (Exception ex)
{
throw;
}
return cusList;
}
public List<SaleParent> SearchSaleParentByMultiFilter(Hashtable hashTable)
{
List<SaleParent> saleParentList = new List<SaleParent>();
string sqlStr = dbUtil.getSqlStatement("SQL_Comd_SaleParent_SearchByMultiFilter");
ArrayList listParms = new ArrayList();
UtilService utilService = new UtilService();
if (hashTable.Contains("@SaleOrderID"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@SaleOrderID");
listParms.Add(new SqlParameter("@SaleOrderID", SqlDbType.NVarChar, 40));
}
if (hashTable.Contains("@CustomerID"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@CustomerID");
listParms.Add(new SqlParameter("@CustomerID", SqlDbType.VarChar, 20));
}
if (hashTable.Contains("@CustomerName"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@CustomerName");
listParms.Add(new SqlParameter("@CustomerName", SqlDbType.NVarChar, 100));
}
if (hashTable.Contains("@SaleEmpID"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@SaleEmpID");
listParms.Add(new SqlParameter("@SaleEmpID", SqlDbType.VarChar, 20));
}
if (hashTable.Contains("@SaleEmpName"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@SaleEmpName");
listParms.Add(new SqlParameter("@SaleEmpName", SqlDbType.NVarChar, 40));
}
if (hashTable.Contains("@ComdID"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@ComdID");
listParms.Add(new SqlParameter("@ComdID", SqlDbType.VarChar, 20));
}
if (hashTable.Contains("@ComdName"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@ComdName");
listParms.Add(new SqlParameter("@ComdName", SqlDbType.NVarChar, 100));
}
if (hashTable.Contains("@SaleDateStart"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@SaleDateStart");
listParms.Add(new SqlParameter("@SaleDateStart", SqlDbType.VarChar, 10));
}
if (hashTable.Contains("@SaleDateEnd"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@SaleDateEnd");
listParms.Add(new SqlParameter("@SaleDateEnd", SqlDbType.VarChar, 10));
}
if (hashTable.Contains("@StatusKBN"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@StatusKBN");
listParms.Add(new SqlParameter("@StatusKBN", SqlDbType.Int));
}
if (hashTable.Contains("@DeliveryFLG"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@DeliveryFLG");
listParms.Add(new SqlParameter("@DeliveryFLG", SqlDbType.Bit));
}
if (hashTable.Contains("@PaymentFLG"))
{
sqlStr = utilService.validSearchFilter(sqlStr, "@PaymentFLG");
listParms.Add(new SqlParameter("@PaymentFLG", SqlDbType.Bit));
}
SqlParameter[] sqlParms = (SqlParameter[])listParms.ToArray(typeof(SqlParameter));
foreach (SqlParameter parm in sqlParms)
{
parm.Value = hashTable[parm.ParameterName];
}
DataSet searchResult = null;
try
{
searchResult = DAO.DBAccess.ExecuteAdapter(DAO.DBAccess.SQLConnectionString, CommandType.Text, sqlStr, sqlParms);
foreach (DataTable dt in searchResult.Tables)
{
foreach (DataRow dr in dt.Rows)
{
SaleParent saleParent = new SaleParent();
saleParent.OrderId = dr["SaleOrderID"].ToString();
saleParent.CustomerID = dr["CustomerID"].ToString();
saleParent.CustomerName = dr["CustomerName"].ToString();
saleParent.SaleEmpID = dr["SaleEmpID"].ToString();
saleParent.SaleEmpName = dr["SaleEmpName"].ToString();
if (dr["SaleDate"].ToString() == string.Empty)
{
saleParent.SaleDate = DateTime.MinValue;
}
else
{
saleParent.SaleDate = DateTime.Parse(dr["SaleDate"].ToString());
}
saleParent.StatusKBN = (int)dr["StatusKBN"];
saleParent.DeliveryFLG = (bool)dr["DeliveryFLG"];
saleParent.PaymentFLG = (bool)dr["PaymentFLG"];
saleParent.Remark = dr["Remark"].ToString();
saleParent.ReceivingAddress = dr["ReceivingAddress"].ToString();
saleParentList.Add(saleParent);
}
}
}
catch (Exception ex)
{
throw;
}
return saleParentList;
}
