public DataTable GetOrderList(ProductOrderSearchData whereData)
{
string whereString = "REQUISITIONTYPE = " + Constz.Requisition.RequisitionType.REQ07.ToString() + " ";
if (whereData.CODE.Trim() != "")
whereString += "AND CODE = '" + OracleDB.QRText(whereData.CODE.Trim()) + "' ";
if (whereData.DATEFROM.Year != 1)
whereString += "AND REQDATE >= " + OracleDB.QRDate(whereData.DATEFROM) + " ";
if (whereData.DATETO.Year != 1)
whereString += "AND REQDATE <= " + OracleDB.QRDate(whereData.DATETO) + " ";
if (whereData.PDNAME.Trim() != "0")
whereString += "AND PDLOID = '" + OracleDB.QRText(whereData.PDNAME.Trim()) + "' ";
if (whereData.STATUSFROM.Trim() != "")
whereString += "AND RANK >= " + OracleDB.QRText(whereData.STATUSFROM.Trim()) + " ";
if (whereData.STATUSTO.Trim() != "")
whereString += "AND RANK <= " + OracleDB.QRText(whereData.STATUSTO.Trim()) + " ";
string sql = "SELECT ROWNUM NO, A.* FROM (SELECT RQ.LOID, RQ.CODE, RQ.REQDATE, RQI.DUEDATE, RQ.REQUISITIONTYPE,";
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 STATUS, ";
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, PD.LOID AS PDLOID, PD.NAME AS PDNAME, RQI.QTY, UN.NAME AS UNIT, RQ.CREATEBY ";
sql += "FROM REQUISITION RQ LEFT JOIN (SELECT REQUISITION, MIN(LOID) LOID FROM REQUISITIONITEM GROUP BY REQUISITION) R ON RQ.LOID = R.REQUISITION ";
sql += "LEFT JOIN REQUISITIONITEM RQI ON RQI.LOID = R.LOID ";
sql += "LEFT JOIN UNIT UN ON RQI.UNIT = UN.LOID ";
sql += "LEFT JOIN PRODUCT PD ON RQI.PRODUCT = PD.LOID ) A ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY NO ";
return OracleDB.ExecListCmd(sql);
}
public DataTable GetRequisitionList(ProductOrderSearchData data)
{
DataTable dt= SearchDAL.GetOrderList(data);
for (int i = 0; i < dt.Rows.Count; ++i)
{
dt.Rows[i]["NO"] = (i + 1);
}
return dt;
}
private ProductOrderSearchData GetData()
{
ProductOrderSearchData data = new ProductOrderSearchData();
data.CODE = this.txtCode.Text.Trim();
data.PDNAME = this.cmbProductView.SelectedItem.Value;
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
return data;
}
public DataTable GetOrderList(ProductOrderSearchData whereData)
{
string whereString = "REQUISITIONTYPE = " + Constz.Requisition.RequisitionType.REQ06.ToString() + " ";
if (whereData.CODE.Trim() != "")
whereString += "AND CODE >= '" + OracleDB.QRText(whereData.CODE.Trim()) + "' ";
if (whereData.CODETO.Trim() != "")
whereString += "AND CODE <= '" + OracleDB.QRText(whereData.CODETO.Trim()) + "' ";
if (whereData.DATEFROM.Year != 1)
whereString += "AND RESERVEDATE >= " + OracleDB.QRDate(whereData.DATEFROM) + " ";
if (whereData.DATETO.Year != 1)
whereString += "AND RESERVEDATE <= " + OracleDB.QRDate(whereData.DATETO) + " ";
//if (whereData.PDNAME.Trim() != "0")
// whereString += "AND PDLOID = '" + OracleDB.QRText(whereData.PDNAME.Trim()) + "' ";
if (whereData.STATUSFROM.Trim() != "")
whereString += "AND RANK >= " + OracleDB.QRText(whereData.STATUSFROM.Trim()) + " ";
if (whereData.STATUSTO.Trim() != "")
whereString += "AND RANK <= " + OracleDB.QRText(whereData.STATUSTO.Trim()) + " ";
string sql = "SELECT ROWNUM NO, A.* FROM (SELECT RQ.LOID, RQ.CODE, RQ.RESERVEDATE, RQ.REQUISITIONTYPE, ";
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 STATUS, ";
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, RQ.CREATEBY, '" + Constz.ReadyMadeDepartment.Name + "' AS SELLER, RQ.TOTAL NETPRICE ";
sql += "FROM REQUISITION RQ ";
//sql += "LEFT JOIN (SELECT REQUISITION, SUM(GRANDTOT) NETPRICE ";
//sql += "FROM REQUISITIONITEM GROUP BY REQUISITION) R ON RQ.LOID = R.REQUISITION "
sql += ")A ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY CODE ";
return OracleDB.ExecListCmd(sql);
}
public DataTable GetRequisitionList(ProductOrderSearchData data)
{
return SearchDAL.GetOrderList(data);
}