private StockoutBasketSearchData GetData()
{
StockoutBasketSearchData data = new StockoutBasketSearchData();
data.CODE = this.txtBasketCode.Text.Trim();
data.DATEFROM = this.ctlDateFrom.DateValue;
data.DATETO = this.ctlDateTo.DateValue;
data.PDNAME = this.txtBasketName.Text.Trim();
data.CREATEBY = this.txtCreateBy.Text.Trim();
data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value;
data.STATUSTO = this.cmbStatusTo.SelectedItem.Value;
return data;
}
public DataTable GetBasketList(StockoutBasketSearchData data)
{
string whereString = "";
if (data.CODE.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' ";
if (data.DATEFROM.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CHECKDATE >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
whereString += (whereString == "" ? "" : "AND ") + "CHECKDATE <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.PDNAME.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(PDNAME) LIKE '%" + data.PDNAME.Trim().ToUpper() + "%' ";
if (data.CREATEBY.Trim() != "")
whereString += (whereString == "" ? "" : "AND ") + "UPPER(CREATEBY) LIKE '%" + data.CREATEBY.Trim().ToUpper() + "%' ";
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, BS.LOID, BS.CODE, BS.CHECKDATE, BS.CREATEBY, PD.NAME PDNAME, BS.QTY, UN.NAME UNITNAME, BS.STOCKINDATE, ";
sql += "CASE BS.STATUS WHEN '" + Constz.Basket.Status.Waiting.Code + "' THEN '" + Constz.Basket.Status.Waiting.Name + "' ";
sql += "WHEN '" + Constz.Basket.Status.Approved.Code + "' THEN '" + Constz.Basket.Status.Approved.Name + "' ";
sql += "ELSE '' END AS STATUSNAME, ";
sql += "CASE BS.TYPE WHEN '" + Constz.Basket.Type.New.Code + "' THEN '" + Constz.Basket.Type.New.Name + "' ";
sql += "WHEN '" + Constz.Basket.Type.Return.Code + "' THEN '" + Constz.Basket.Type.Return.Name + "' ";
sql += "ELSE '' END AS TYPENAME, ";
sql += "CASE BS.STATUS WHEN '" + Constz.Basket.Status.Waiting.Code + "' THEN '" + Constz.Basket.Status.Waiting.Rank + "' ";
sql += "WHEN '" + Constz.Basket.Status.Approved.Code + "' THEN '" + Constz.Basket.Status.Approved.Rank + "' ";
sql += "ELSE '' END AS RANK ";
sql += "FROM BASKET BS INNER JOIN PRODUCT PD ON BS.PRODUCT = PD.LOID ";
sql += "INNER JOIN UNIT UN ON BS.UNIT = UN.LOID ";
sql += "LEFT JOIN WAREHOUSE WA ON BS.WAREHOUSE = WA.LOID) ";
sql += (whereString == "" ? "" : "WHERE " + whereString);
sql += "ORDER BY CODE ";
DataTable dt = OracleDB.ExecListCmd(sql);
for (int i = 0; i < dt.Rows.Count; ++i)
{
dt.Rows[i]["NO"] = i + 1;
}
return dt;
}
