public DataTable GetProductReceiveList(ProductReceiveListSearchData data)
{
string where = "WHERE PO.STATUS = 'AP' AND PO.ACTIVE = '1' AND PO.ORDERTYPE = 'PO' ";
if (data.CODE.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(PO.CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' ";
if (data.DATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(ORDERDATE, 'DDMMYYYY') >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(ORDERDATE, 'DDMMYYYY') <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.PRODUCT != 0)
where += (where == "" ? "" : "AND ") + "PRODUCT = " + data.PRODUCT.ToString() + " ";
if (data.SUPPLIER != 0)
where += (where == "" ? "" : "AND ") + "SUPPLIER = " + data.SUPPLIER.ToString() + " ";
string sql = "SELECT PO.LOID POLOID, POI.DUEDATE, PO.CODE POCODE, PO.ORDERDATE, ";
sql += "SP.SUPPLIERNAME, PD.NAME PRODUCTNAME, POI.QTY, POI.RECEIVEQTY, UN.NAME UNITNAME, ";
sql += "CASE WHEN POI.LOID IS NULL AND SII.LOID IS NULL THEN 'ÃÍà»Ô´ PO' ";
sql += "WHEN POI.LOID IS NOT NULL AND SII.LOID IS NULL THEN 'ÃÍÃѺÊÔ¹¤éÒ' ";
sql += "WHEN POI.LOID IS NOT NULL AND SII.STATUS = 'QC' THEN 'Ã͵ÃǨQC' ";
sql += "WHEN POI.LOID IS NOT NULL AND SII.STATUS = 'AP' THEN 'ÃÍÃѺà¢éÒ¤Åѧ' ";
sql += "WHEN POI.LOID IS NOT NULL AND SII.STATUS = 'FN' THEN 'ÃѺà¢éÒ¤ÅѧáÅéÇ' END STATUSPRODUCT ";
sql += "FROM POITEM POI INNER JOIN PDORDER PO ON POI.PDORDER = PO.LOID ";
sql += "INNER JOIN PRODUCT PD ON POI.PRODUCT = PD.LOID ";
sql += "INNER JOIN UNIT UN ON POI.UNIT = UN.LOID ";
sql += "INNER JOIN SUPPLIER SP ON PO.SUPPLIER = SP.LOID ";
sql += "LEFT JOIN STOCKINITEM SII ON SII.REFTABLE = 'POITEM' AND SII.REFLOID = POI.LOID ";
sql += where;
sql += "ORDER BY POI.DUEDATE, PO.CODE, PD.NAME ";
return OracleDB.ExecListCmd(sql);
}
private ProductReceiveListSearchData GetSearchData()
{
ProductReceiveListSearchData data = new ProductReceiveListSearchData();
data.CODE = this.txtCode.Text.Trim();
data.DATEFROM = this.dtpDateFrom.DateValue;
data.DATETO = this.dtpDateTo.DateValue;
data.PRODUCT = Convert.ToDouble(this.cmbProduct.SelectedItem.Value);
data.SUPPLIER = Convert.ToDouble(this.cmbSupplier.SelectedItem.Value);
return data;
}
