private StockoutBasketSearchData GetData() { StockoutBasketSearchData data = new StockoutBasketSearchData(); data.CODE = this.txtBasketCode.Text.Trim(); data.DATEFROM = this.ctlDateFrom.DateValue; data.DATETO = this.ctlDateTo.DateValue; data.PDNAME = this.txtBasketName.Text.Trim(); data.CREATEBY = this.txtCreateBy.Text.Trim(); data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; return data; }
public DataTable GetBasketList(StockoutBasketSearchData data) { string whereString = ""; if (data.CODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' "; if (data.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CHECKDATE >= " + OracleDB.QRDate(data.DATEFROM) + " "; if (data.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "CHECKDATE <= " + OracleDB.QRDate(data.DATETO) + " "; if (data.PDNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(PDNAME) LIKE '%" + data.PDNAME.Trim().ToUpper() + "%' "; if (data.CREATEBY.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "UPPER(CREATEBY) LIKE '%" + data.CREATEBY.Trim().ToUpper() + "%' "; if (data.STATUSFROM.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK >= " + OracleDB.QRText(data.STATUSFROM.Trim()) + " "; if (data.STATUSTO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "RANK <= " + OracleDB.QRText(data.STATUSTO.Trim()) + " "; string sql = "select * from ( SELECT ROWNUM NO, BS.LOID, BS.CODE, BS.CHECKDATE, BS.CREATEBY, PD.NAME PDNAME, BS.QTY, UN.NAME UNITNAME, BS.STOCKINDATE, "; sql += "CASE BS.STATUS WHEN '" + Constz.Basket.Status.Waiting.Code + "' THEN '" + Constz.Basket.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Basket.Status.Approved.Code + "' THEN '" + Constz.Basket.Status.Approved.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE BS.TYPE WHEN '" + Constz.Basket.Type.New.Code + "' THEN '" + Constz.Basket.Type.New.Name + "' "; sql += "WHEN '" + Constz.Basket.Type.Return.Code + "' THEN '" + Constz.Basket.Type.Return.Name + "' "; sql += "ELSE '' END AS TYPENAME, "; sql += "CASE BS.STATUS WHEN '" + Constz.Basket.Status.Waiting.Code + "' THEN '" + Constz.Basket.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.Basket.Status.Approved.Code + "' THEN '" + Constz.Basket.Status.Approved.Rank + "' "; sql += "ELSE '' END AS RANK "; sql += "FROM BASKET BS INNER JOIN PRODUCT PD ON BS.PRODUCT = PD.LOID "; sql += "INNER JOIN UNIT UN ON BS.UNIT = UN.LOID "; sql += "LEFT JOIN WAREHOUSE WA ON BS.WAREHOUSE = WA.LOID) "; sql += (whereString == "" ? "" : "WHERE " + whereString); sql += "ORDER BY CODE "; DataTable dt = OracleDB.ExecListCmd(sql); for (int i = 0; i < dt.Rows.Count; ++i) { dt.Rows[i]["NO"] = i + 1; } return dt; }