private ReturnTesterSearchData GetData()
{
ReturnTesterSearchData data = new ReturnTesterSearchData();
data.SENDER = Appz.CurrentUserData.Warehouse;
data.CODE = this.txtCode.Text.Trim();
data.DATEFROM = this.dtpDateFrom.Value;
data.DATETO = this.dtpDateTo.Value;
if (this.cmbStatusFrom.SelectedValue != null)
data.STATUSFROM = this.cmbStatusFrom.SelectedValue.ToString();
if (this.cmbStatusTo.SelectedValue != null)
data.STATUSTO = this.cmbStatusTo.SelectedValue.ToString();
return data;
}
public DataTable GetReturnTesterList(ReturnTesterSearchData data)
{
string where = "";
if (data.CODE.Trim() != "")
where += (where == "" ? "" : " AND ") + "UPPER(CODE) LIKE '%" + data.CODE.Trim().ToUpper() + "%' ";
if (data.DATEFROM.Year != 1)
where += (where == "" ? "" : " AND ") + "TO_DATE(CREATEON) >= " + OracleDB.QRDate(data.DATEFROM) + " ";
if (data.DATETO.Year != 1)
where += (where == "" ? "" : " AND ") + "TO_DATE(CREATEON) <= " + OracleDB.QRDate(data.DATETO) + " ";
if (data.STATUSFROM.Trim() != "")
where += (where == "" ? "" : "AND ") + "RANK >= " + OracleDB.QRText(data.STATUSFROM.Trim()) + " ";
if (data.STATUSTO.Trim() != "")
where += (where == "" ? "" : "AND ") + "RANK <= " + OracleDB.QRText(data.STATUSTO.Trim()) + " ";
if (data.SENDER != 0)
where += (where == "" ? "" : " AND ") + "SENDER = " + data.SENDER.ToString() + " ";
string sql = "SELECT * FROM (SELECT 0 AS BOX, ST.LOID, ST.CODE, ST.CREATEON, ST.SENDER, W.NAME AS WAREHOUSENAME, ";
sql += "CASE ST.STATUS WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.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 AS STATUS, ";
sql += "CASE ST.STATUS WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN " + Constz.Requisition.Status.Approved.Rank + " ";
sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN " + Constz.Requisition.Status.Void.Rank + " ";
sql += "WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN " + Constz.Requisition.Status.Waiting.Rank + " ";
sql += "ELSE 0 END AS RANK, ";
sql += "COUNT(STI.LOID) AS TOTAL ";
sql += "FROM STOCKOUT ST INNER JOIN STOCKOUTITEM STI ON STI.STOCKOUT = ST.LOID ";
sql += " INNER JOIN WAREHOUSE W ON W.LOID = ST.RECEIVER ";
sql += "GROUP BY ST.LOID, ST.CODE, ST.CREATEON, ST.SENDER, W.NAME, ST.STATUS) A ";
sql += (where == "" ? "" : "WHERE " + where);
sql += "ORDER BY CODE, CREATEON, WAREHOUSENAME ";
return OracleDB.ExecListCmd(sql);
}
public DataTable GetReturnTesterList(ReturnTesterSearchData data)
{
return SearchDAL.GetReturnTesterList(data);
}