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