public DataTable GetPlanList(PlanSearchData data) { DataTable dt = PlanInvDAL.GetPlanList(data); double rowIndex = 1; foreach (DataRow dRow in dt.Rows) { dRow["ORDERNO"] = rowIndex; rowIndex += 1; } return dt; }
private PlanSearchData GetData() { PlanSearchData data = new PlanSearchData(); data.YEARFROM = this.txtYearFrom.Text.Trim(); data.YEARTO = this.txtYearTo.Text.Trim(); data.CREATEFROM = ctlCreateFrom.DateValue; data.CREATETO = ctlCreateTo.DateValue; data.CONFIRMFROM = ctlConfirmFrom.DateValue; data.CONFIRMTO = ctlConfirmTo.DateValue; data.STATUSFROM = this.cmbStatusFrom.SelectedItem.Value; data.STATUSTO = this.cmbStatusTo.SelectedItem.Value; return data; }
public DataTable GetPlanList(PlanSearchData data) { string sql = "SELECT * FROM (SELECT 0 ORDERNO, LOID, CREATEBY, CREATEON, CODE, PLANTYPE, CONFIRMDATE, YEAR, "; sql += "CASE STATUS WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Name + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Name + "' "; sql += "ELSE '' END AS STATUSNAME, "; sql += "CASE STATUS WHEN '" + Constz.Requisition.Status.Approved.Code + "' THEN '" + Constz.Requisition.Status.Approved.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Waiting.Code + "' THEN '" + Constz.Requisition.Status.Waiting.Rank + "' "; sql += "WHEN '" + Constz.Requisition.Status.Void.Code + "' THEN '" + Constz.Requisition.Status.Void.Rank + "' "; sql += "ELSE '0' END AS RANK "; sql += "FROM PLAN) A "; string where = "PLANTYPE = '" + Constz.PlanType.WH + "' "; if (data.YEARFROM != "") where += (where == "" ? "" : "AND ") + "YEAR >= '" + data.YEARFROM + "' "; if (data.YEARTO != "") where += (where == "" ? "" : "AND ") + "YEAR <= '" + data.YEARFROM + "' "; if (data.CREATEFROM.Year != 1) where += (where == "" ? "" : "AND ") + "TO_CHAR(CREATEON, 'YYYYMMDD') >= TO_CHAR(" + OracleDB.QRDate(data.CREATEFROM) + ", 'YYYYMMDD') "; if (data.CREATETO.Year != 1) where += (where == "" ? "" : "AND ") + "TO_CHAR(CREATEON, 'YYYYMMDD') <= TO_CHAR(" + OracleDB.QRDate(data.CREATETO) + ", 'YYYYMMDD') "; if (data.CONFIRMFROM.Year != 1) where += (where == "" ? "" : "AND ") + "TO_CHAR(CONFIRMDATE, 'YYYYMMDD') >= TO_CHAR(" + OracleDB.QRDate(data.CONFIRMFROM) + ", 'YYYYMMDD') "; if (data.CONFIRMTO.Year != 1) where += (where == "" ? "" : "AND ") + "TO_CHAR(CONFIRMDATE, 'DD/MM/YYYY') <= TO_CHAR(" + OracleDB.QRDate(data.CONFIRMTO) + ", 'YYYYMMDD') "; if (data.STATUSFROM != "") where += (where == "" ? "" : "AND ") + "RANK >= '" + data.STATUSFROM + "' "; if (data.STATUSTO != "") where += (where == "" ? "" : "AND ") + "RANK <= '" + data.STATUSTO + "' "; sql += (where == "" ? "" : "WHERE " + where) + " ORDER BY CODE "; return OracleDB.ExecListCmd(sql); }