private ReturnRequestSearchData GetData() { ReturnRequestSearchData data = new ReturnRequestSearchData(); data.CODE = this.txtCode.Text.Trim(); data.MFGDATEFROM = this.ctlMFGDateFrom.DateValue; data.MFGDATETO = this.ctlMFGDateTo.DateValue; data.REQDATEFROM = this.ctlReqDateFrom.DateValue; data.REQDATETO = this.ctlReqDateTo.DateValue; data.PRODUCT = this.cmbProduct.SelectedItem.Value; data.LOTNO = this.txtLotNo.Text.Trim(); data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; return data; }
public DataTable GetReturnList(ReturnRequestSearchData data) { string whereString = ""; if (data.CODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "CODE = '" + OracleDB.QRText(data.CODE.Trim()) + "' "; if (data.REQDATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE >= " + OracleDB.QRDate(data.REQDATEFROM) + " "; if (data.REQDATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE <= " + OracleDB.QRDate(data.REQDATETO) + " "; if (data.MFGDATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "MFGDATE >= " + OracleDB.QRDate(data.MFGDATEFROM) + " "; if (data.MFGDATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "MFGDATE <= " + OracleDB.QRDate(data.MFGDATETO) + " "; if (data.PRODUCT.Trim() != "0") whereString += (whereString == "" ? "" : "AND ") + "PRODUCT = '" + OracleDB.QRText(data.PRODUCT.Trim()) + "' "; if (data.LOTNO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "LOTNO = '" + OracleDB.QRText(data.LOTNO.Trim()) + "' "; 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, RQ.LOID, RQ.CODE, RQ.REQDATE,RQ.REQUISITIONTYPE, VP.PDLOID PRODUCT, VP.PDNAME, VP.LOTNO,VP.BATCHSIZE,VP.BATCHSIZEUNITNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Rank + "' "; sql += "ELSE '' END AS RANK, VP.MFGDATE "; sql += "FROM REQUISITION RQ INNER JOIN V_PRODUCT_PDPRODUCT VP ON RQ.REFTABLE='PDPRODUCT' "; sql += "AND RQ.REFLOID=VP.PDPLOID "; sql += "INNER JOIN REQUISITIONTYPE RT ON RQ.REQUISITIONTYPE=RT.LOID AND RT.LOID=14) "; 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; }