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