public ActionResult InitAskTable()
{
int page = Helper.ToInt(Request["page"]);
int rows = Helper.ToInt(Request["rows"]);
string unitID = Helper.ToString(Request["unitID"]);
string userName = Helper.ToString(Request["userName"]);
string dateStart = Helper.ToString(Request["dateStart"]);
string dateEnd = Helper.ToString(Request["dateEnd"]);
HCQ2_Model.SelectModel.T_AskManagerModel model = new HCQ2_Model.SelectModel.T_AskManagerModel()
{
unitID = unitID,
page = page,
rows = rows,
userName = userName,
dateStart = dateStart,
dateEnd = dateEnd
};
List <HCQ2_Model.ExtendsionModel.T_AskManagerModel> list =
operateContext.bllSession.T_AskManager.SelectAskManagerData(model);
HCQ2_Model.ViewModel.TableModel tModel = new TableModel()
{
total = operateContext.bllSession.T_AskManager.SelectCountAskUserData(model),
rows = list
};
return(Json(tModel, JsonRequestBehavior.AllowGet));
}
public int SelectCountData(HCQ2_Model.SelectModel.T_AskManagerModel model)
{
StringBuilder sb = new StringBuilder();
sb.Append(
"SELECT COUNT(*) FROM dbo.T_AskManager WHERE 1=1 ");
if (!string.IsNullOrEmpty(model.unitID))
{
sb.AppendLine(string.Format(" AND ask_unit LIKE '{0}%' ", model.unitID));
}
if (!string.IsNullOrEmpty(model.userName))
{
sb.Append(string.Format(" AND user_name LIKE '%{0}%' ", model.userName));
}
if (!string.IsNullOrEmpty(model.dateStart) && !string.IsNullOrEmpty(model.dateEnd))
{
sb.Append(string.Format(" AND ask_startDate>='{0}' AND ask_endDate<='{1}' ", model.dateStart,
model.dateEnd));
}
else if (!string.IsNullOrEmpty(model.dateStart))
{
sb.Append(string.Format(" AND ask_startDate>='{0}' ", model.dateStart));
}
else if (!string.IsNullOrEmpty(model.dateEnd))
{
sb.Append(string.Format(" AND ask_endDate<='{0}' ", model.dateEnd));
}
return(Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text)));
}
public List <T_AskManagerModel> SelectAskData(HCQ2_Model.SelectModel.T_AskManagerModel model)
{
if (model == null)
{
return(null);
}
StringBuilder sb = new StringBuilder();
sb.Append(string.Format(@" SELECT TOP {0} * FROM
(
SELECT TOP {1} ask.ask_id, ask.ask_unit, ask.user_name, ask.ask_startDate, ask.ask_endDate,
ask.ask_day, ask.ask_title, ask.ask_context, ask.ask_operUser, ask.ask_operDate,
code_type.*, code_status.* FROM
(SELECT * FROM dbo.T_AskManager WHERE 1 = 1 ", model.rows, model.page * model.rows));
if (!string.IsNullOrEmpty(model.unitID))
{
sb.AppendLine(string.Format(" AND ask_unit LIKE '{0}%' ", model.unitID));
}
if (!string.IsNullOrEmpty(model.userName))
{
sb.Append(string.Format(" AND user_name LIKE '%{0}%' ", model.userName));
}
if (!string.IsNullOrEmpty(model.dateStart) && !string.IsNullOrEmpty(model.dateEnd))
{
sb.Append(string.Format(" AND ask_startDate>='{0}' AND ask_endDate<='{1}' ", model.dateStart, model.dateEnd));
}
else if (!string.IsNullOrEmpty(model.dateStart))
{
sb.Append(string.Format(" AND ask_startDate>='{0}' ", model.dateStart));
}
else if (!string.IsNullOrEmpty(model.dateEnd))
{
sb.Append(string.Format(" AND ask_endDate<='{0}' ", model.dateEnd));
}
sb.Append(string.Format(@") ask INNER JOIN
(SELECT item2.ask_type, item2.ask_type_text FROM
(SELECT item_id, item_code FROM dbo.T_ItemCode WHERE item_code = 'QJTYPE') item1 INNER JOIN
(SELECT code_name AS ask_type_text, code_value AS ask_type, item_id AS id FROM dbo.T_ItemCodeMenum) item2 ON item1.item_id = item2.id) code_type
ON ask.ask_type = code_type.ask_type INNER JOIN
(SELECT item2.ask_status, item2.ask_status_text FROM
(SELECT item_id, item_code FROM dbo.T_ItemCode WHERE item_code = 'QJSTATUS') item1 INNER JOIN
(SELECT code_name AS ask_status_text, code_value AS ask_status, item_id AS id FROM dbo.T_ItemCodeMenum) item2 ON item1.item_id = item2.id) code_status
ON ask.ask_status = code_status.ask_status
ORDER BY ask.ask_id DESC
) w1"));
DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString(), System.Data.CommandType.Text);
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <T_AskManagerModel>(dt, true));
}
public int SelectCountAskUserData(HCQ2_Model.SelectModel.T_AskManagerModel model)
{
return(DBSession.IT_AskManagerDAL.SelectCountData(model));
}
public List <T_AskManagerModel> SelectAskManagerData(HCQ2_Model.SelectModel.T_AskManagerModel model)
{
return(DBSession.IT_AskManagerDAL.SelectAskData(model));
}
