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; }