private PDReserveSearchData GetData() { PDReserveSearchData data = new PDReserveSearchData(); data.CODE = this.txtCode.Text.Trim(); data.DATEFROM = this.ctlDateFrom.DateValue; data.DATETO = this.ctlDateTo.DateValue; data.PRODUCTNAME = this.txtProductName.Text.Trim(); data.LOTNO = this.txtLotNo.Text.Trim(); data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; data.REFWAREHOUSE = Convert.ToDouble(this.cmbWarehouse.SelectedItem.Value); return data; }
public DataTable GetPDRequestList(PDReserveSearchData data) { string whereString = ""; if (data.CODE.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "CODE = '" + OracleDB.QRText(data.CODE.Trim()) + "' "; if (data.DATEFROM.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE >= " + OracleDB.QRDate(data.DATEFROM) + " "; if (data.DATETO.Year != 1) whereString += (whereString == "" ? "" : "AND ") + "REQDATE <= " + OracleDB.QRDate(data.DATETO) + " "; if (data.PRODUCTNAME.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "PDNAME LIKE '%" + OracleDB.QRText(data.PRODUCTNAME.Trim()) + "%' "; if (data.LOTNO.Trim() != "") whereString += (whereString == "" ? "" : "AND ") + "LOTNO = '" + OracleDB.QRText(data.LOTNO.Trim()) + "' "; if (data.REFWAREHOUSE != 0) whereString += (whereString == "" ? "" : "AND ") + "REFWAREHOUSE = '" + OracleDB.QRText(data.REFWAREHOUSE.ToString()) + "' "; 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 ROWNUM NO,A.* from (SELECT RQ.LOID, RQ.CODE, RQ.REQDATE,RQ.REQUISITIONTYPE, VP.PDNAME, RQ.REFWAREHOUSE,VP.LOTNO,VP.BATCHSIZE,VP.BATCHSIZEUNITNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.DoWaiting.Code + "' THEN '" + Constz.Requisition.Status.DoWaiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.SendWareHouse.Code + "' THEN '" + Constz.Requisition.Status.SendWareHouse.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE RQ.STATUS WHEN '" + Constz.Requisition.Status.DoWaiting.Code + "' THEN '" + Constz.Requisition.Status.DoWaiting.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.SendWareHouse.Code + "' THEN '" + Constz.Requisition.Status.SendWareHouse.Rank + "' "; sql += "ELSE '' END AS RANK "; 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=8) A "; sql += (whereString == "" ? "" : "WHERE " + whereString); DataTable dt = OracleDB.ExecListCmd(sql); //for (int i = 0; i < dt.Rows.Count; ++i) //{ // dt.Rows[i]["NO"] = i + 1; //} return dt; }