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