private PlanItemSearchData GetSearchData()
{
PlanItemSearchData data = new PlanItemSearchData();
data.PLAN = Convert.ToDouble(this.txtPlan.Text == "" ? "0" : this.txtPlan.Text);
data.PRODUCTGROUP = Convert.ToDouble(this.cmbProductGroup.SelectedItem.Value);
data.PRODUCTTYPE = Convert.ToDouble(this.cmbProductType.SelectedItem.Value);
data.PRODUCTNAME = this.txtProductKey.Text.Trim();
return data;
}
public DataTable GetPlanSaleItemList(PlanItemSearchData data)
{
string where = "";
if (data.PLAN != 0)
where += (where == "" ? "" : "AND ") + "PI.PLAN = " + data.PLAN.ToString() + " ";
if (data.PRODUCTGROUP != 0)
where += (where == "" ? "" : "AND ") + "P.PRODUCTGROUP = " + data.PRODUCTGROUP.ToString() + " ";
if (data.PRODUCTTYPE != 0)
where += (where == "" ? "" : "AND ") + "PG.PRODUCTTYPE = " + data.PRODUCTTYPE.ToString() + " ";
if (data.PRODUCTNAME != "")
where += (where == "" ? "" : "AND ") + "upper(P.NAME) LIKE '%" + data.PRODUCTNAME.ToUpper() + "%' ";
string sql = "SELECT PI.LOID, PI.PRODUCT, P.NAME PRODUCTNAME, P.PRODUCTGROUP, PG.NAME GROUPNAME, PT.NAME TYPENAME, ";
sql += "PI.M1, PI.M2, PI.M3, PI.M4, PI.M5, PI.M6, PI.M7, PI.M8, PI.M9, PI.M10, PI.M11, PI.M12, PI.STATUS ";
sql += "FROM PLANORDER PI INNER JOIN PRODUCT P ON P.LOID = PI.PRODUCT ";
sql += "INNER JOIN PRODUCTGROUP PG ON PG.LOID = P.PRODUCTGROUP ";
sql += "INNER JOIN PRODUCTTYPE PT ON PT.LOID = PG.PRODUCTTYPE ";
sql += (where == "" ? "" : "WHERE " + where) + "ORDER BY P.NAME ";
return OracleDB.ExecListCmd(sql);
}
public DataTable GetPlanMKItemList(PlanItemSearchData data)
{
return SaleDAL.GetPlanMKItemList(data);
}
public DataTable GetPlanMKItemList(PlanItemSearchData data)
{
string where = "";
if (data.PLAN != 0)
where += (where == "" ? "" : "AND ") + "PM.PLAN = " + data.PLAN.ToString() + " ";
string sql = "SELECT PM.LOID,PM.PLAN, PM.CUSTOMER,CASE WHEN PM.CUSTOMER = '-1' THEN 'Í×è¹æ' ELSE C.NAME END AS CUSTOMERNAME,PM.PERCENT, ";
sql += "PM.M1,PM.M2,PM.M3,PM.M4,PM.M5,PM.M6,PM.M7,PM.M8,PM.M9,PM.M10,PM.M11,PM.M12,PM.RANK,PM.STATUS ";
sql += "FROM PLANMARKET PM ";
sql += "LEFT JOIN CUSTOMER C ON PM.CUSTOMER = C.LOID ";
sql += (where == "" ? "" : "WHERE " + where) + "ORDER BY PM.RANK ";
return OracleDB.ExecListCmd(sql);
}