private PurchaseOrderSearchData GetData()
{
PurchaseOrderSearchData data = new PurchaseOrderSearchData();
data.POCODE = this.txtPOCode.Text.Trim();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.PRCODE = this.txtPRCode.Text.Trim();
data.PURCHASETYPE = Convert.ToDouble(this.cmbPurchaseType.SelectedItem.Value);
data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedItem.Value);
data.DIVISION = Convert.ToDouble(this.cmbDivision.SelectedItem.Value);
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
return data;
}
public DataTable GetPDOrderList(PurchaseOrderSearchData data)
{
string whereString = "";
if (data.POCODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(POCODE) = '" + OracleDB.QRText(data.POCODE.Trim()).ToUpper() + "' ";
if (data.PRCODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(PRCODE) = '" + OracleDB.QRText(data.PRCODE.Trim()).ToUpper() + "' ";
if (data.DATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "DUEDATE >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "DUEDATE <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.PURCHASETYPE != 0)
whereString += (whereString == "" ? "" : "AND ") + "PURCHASETYPE = " + data.PURCHASETYPE.ToString() + " ";
if (data.DIVISION != 0)
whereString += (whereString == "" ? "" : "AND ") + "DIVISION = " + data.DIVISION.ToString() + " ";
if (data.PRODUCT != 0)
whereString += (whereString == "" ? "" : "AND ") + "PRODUCT = " + data.PRODUCT.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 * from ( SELECT ROWNUM NO, PO.LOID, PO.CODE AS POCODE, POI.DUEDATE, PO.ORDERDATE, PD.NAME AS PRODUCTNAME, POI.QTY AS POIQTY, UN.NAME AS UNITNAME, POI.PRICE, ((NVL(POI.QTY,0)*NVL(POI.PRICE,0))-NVL(POI.DISCOUNT,0)) AS NETPRICE, PR.CODE AS PRCODE, PRI.QTY AS PRIQTY, PR.PURCHASETYPE, PR.DIVISION, POI.PRODUCT, PR.LOID AS PRLOID, ";
sql += "CASE PO.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 PO.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 ";
sql += "FROM PDORDER PO INNER JOIN POITEM POI ON PO.LOID = POI.PDORDER ";
sql += "and poi.loid = (select min(loid) from poitem where pdorder = po.loid) ";
sql += "INNER JOIN PRITEM PRI ON PRI.LOID = POI.PRITEM ";
sql += "INNER JOIN PDREQUEST PR ON PR.LOID = PRI.PDREQUEST ";
sql += "LEFT JOIN PURCHASETYPE PT ON PT.LOID = PR.PURCHASETYPE ";
sql += "LEFT JOIN PRODUCT PD ON PD.LOID = POI.PRODUCT ";
sql += "LEFT JOIN UNIT UN ON UN.LOID = POI.UNIT ";
sql += "LEFT JOIN DIVISION DV ON PR.DIVISION = DV.LOID) ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY POCODE ";
DataTable dt = OracleDB.ExecListCmd(sql);
for (int i = 0; i < dt.Rows.Count; ++i)
{
dt.Rows[i]["NO"] = i + 1;
}
return dt;
}