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