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