public DataTable GetProductPurchaseList(ProductPurchaseListSearchData data) { //string where = "WHERE PR.STATUS = 'AP' AND PRI.ACTIVE = '1' AND PR.ORDERTYPE = 'PO' "; //if (data.CODE.Trim() != "") // where += (where == "" ? "" : "AND ") + "UPPER(PR.CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' "; //if (data.DATEFROM.Year != 1) // where += (where == "" ? "" : "AND ") + "TO_DATE(REQUESTDATE, 'DDMMYYYY') >= " + OracleDB.QRDate(data.DATEFROM) + " "; //if (data.DATETO.Year != 1) // where += (where == "" ? "" : "AND ") + "TO_DATE(REQUESTDATE, 'DDMMYYYY') <= " + OracleDB.QRDate(data.DATETO) + " "; //if (data.PURCHASETYPE != 0) // where += (where == "" ? "" : "AND ") + "PURCHASETYPE = " + data.PURCHASETYPE.ToString() + " "; //if (data.PRODUCT != 0) // where += (where == "" ? "" : "AND ") + "PRODUCT = " + data.PRODUCT.ToString() + " "; //string sql = "SELECT PRI.LOID, PR.LOID PRLOID, PR.CODE PRCODE, PR.REQUESTDATE, PD.NAME PRODUCTNAME, PRI.PRODUCT, PRI.QTY, UN.NAME UNITNAME, PRI.UNIT, PRI.OLDPRICE, PRI.URGENT, PRI.CURPRICE, PRI.MINPRICE, PT.NAME PURCHASETYPENAME, PRI.DUEDATE, PR.STATUS, "; //sql += "CASE PR.STATUS WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' "; //sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Name + "' "; //sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Name + "' "; //sql += "WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; //sql += "ELSE '' END STATUSNAME "; //sql += "FROM PRITEM PRI INNER JOIN PDREQUEST PR ON PRI.PDREQUEST = PR.LOID "; //sql += "INNER JOIN PRODUCT PD ON PRI.PRODUCT = PD.LOID "; //sql += "INNER JOIN UNIT UN ON PRI.UNIT = UN.LOID "; //sql += "INNER JOIN PURCHASETYPE PT ON PR.PURCHASETYPE = PT.LOID "; //sql += where; //sql += "ORDER BY PR.REQUESTDATE, PR.CODE "; string where = "WHERE VP.STATUS = '" + Constz.Requisition.Status.Approved.Code + "' AND VP.ACTIVE = '" + Constz.ActiveStatus.Active + "' AND VP.ORDERTYPE = '" + Constz.OrderType.PO.Code + "' AND VP.QTY>0 "; if (data.CODE.Trim() != "") where += (where == "" ? "" : "AND ") + "UPPER(VP.PRCODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' "; if (data.DATEFROM.Year != 1) where += (where == "" ? "" : "AND ") + "TO_DATE(REQUESTDATE, 'DDMMYYYY') >= " + OracleDB.QRDate(data.DATEFROM) + " "; if (data.DATETO.Year != 1) where += (where == "" ? "" : "AND ") + "TO_DATE(REQUESTDATE, 'DDMMYYYY') <= " + OracleDB.QRDate(data.DATETO) + " "; if (data.PURCHASETYPE != 0) where += (where == "" ? "" : "AND ") + "VP.PTLOID = '" + data.PURCHASETYPE.ToString() + "' "; if (data.PRODUCT != 0) where += (where == "" ? "" : "AND ") + "PRODUCT = " + data.PRODUCT.ToString() + " "; string sql = "SELECT VP.PRITEM LOID,VP.PDREQUEST PRLOID,VP.PRCODE PRCODE,VP.REQUEStDate,VP.PDNAME PRODUCTNAME,VP.PRODUCT,VP.PRQTY QTY,VP.UNAME UNITNAME,VP.UNIT,VP.OLDPRICE,VP.URGENT, "; sql += "VP.CURPRICE,VP.MINPRICE,VP.PURCHASETYPE PURCHASETYPENAME,VP.DUEDATE,VP.STATUS, "; sql += "CASE VP.STATUS WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Finish.Code + "' THEN '" + Constz.Requisition.Status.Finish.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "ELSE '' END STATUSNAME "; sql += "FROM V_PRODUCT_PO_POPUP_LIST VP "; sql += where; sql += "ORDER BY VP.REQUESTDATE, VP.PRCODE "; return OracleDB.ExecListCmd(sql); }
private ProductPurchaseListSearchData GetSearchData() { ProductPurchaseListSearchData data = new ProductPurchaseListSearchData(); data.CODE = this.txtCode.Text.Trim(); data.DATEFROM = this.dtpDateFrom.DateValue; data.DATETO = this.dtpDateTo.DateValue; data.PURCHASETYPE = Convert.ToDouble(this.cmbPurchaseType.SelectedItem.Value); data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedItem.Value); return data; }