private void SearchData()
{
SearchFlow flow = new SearchFlow();
PopupStockoutSearchData data = new PopupStockoutSearchData();
data.CODEFROM = this.txtCodeFrom.Text.Trim();
data.CODETO = this.txtCodeTo.Text.Trim();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.PRODUCTNAME = this.txtProductName.Text.Trim();
this.grvReserve.DataSource = flow.GetProductReturnList(data);
this.grvReserve.DataBind();
}
public static DataTable GetProductReturnList(PopupStockoutSearchData data)
{
string where = "STATUS = 'AP' AND LOID NOT IN (SELECT REFLOID FROM PDRETURN WHERE REFTABLE = 'STOCKOUT') ";
if (data.CODEFROM.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) >= '" + OracleDB.QRText(data.CODEFROM.Trim()).ToUpper() + "' ";
if (data.CODETO.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(CODE) <= '" + OracleDB.QRText(data.CODETO.Trim()).ToUpper() + "' ";
if (data.DATEFROM.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(CREATEON, 'DDMMYYYY') >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
where += (where == "" ? "" : "AND ") + "TO_DATE(CREATEON, 'DDMMYYYY') <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.PRODUCTNAME.Trim() != "")
where += (where == "" ? "" : "AND ") + "UPPER(PRODUCTNAME) = '" + OracleDB.QRText(data.PRODUCTNAME.Trim()).ToUpper() + "' ";
string sql = "SELECT A.* FROM (SELECT ST.LOID,ST.CODE,ST.CREATEON,ST.STATUS,STI.LOID STICODE,STI.PRODUCT,PD.NAME PRODUCTNAME,STI.QTY,STI.UNIT,U.NAME UNITNAME FROM STOCKOUT ST ";
sql += "INNER JOIN (SELECT MIN(LOID) LOID,STOCKOUT FROM STOCKOUTITEM GROUP BY STOCKOUT)A ON ST.DOCTYPE IN (9,10) AND ST.LOID = A.STOCKOUT ";
sql += "INNER JOIN STOCKOUTITEM STI ON A.LOID = STI.LOID INNER JOIN PRODUCT PD ON STI.PRODUCT = PD.LOID ";
sql += "INNER JOIN UNIT U ON STI.UNIT = U.LOID)A ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY LOID ";
return OracleDB.ExecListCmd(sql);
}
public DataTable GetProductReturnList(PopupStockoutSearchData data)
{
return SearchDAL.GetProductReturnList(data);
}
