예제 #1
0
 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;
 }
예제 #2
0
        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;
        }