/// <summary>
/// 获取一栏数据总量
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public int GetWorkListDataCount(T_UseWorkerParam param)
{
sb?.Clear();
sb.Append(string.Format(@"SELECT COUNT(*) FROM dbo.T_UseWorker WHERE com_id={0} AND use_status={1} ", param.com_id, param.use_status));
if (!string.IsNullOrEmpty(param.jobName))
{
sb.Append(string.Format(@" AND trade_type LIKE '%@jobName%' "));
}
if (param.jobStartMoney > 0 && param.jobEndMoney > 0)
{
sb.Append(string.Format(" AND use_wage_start>={0} AND use_wage_end<={1} ", param.jobStartMoney, param.jobEndMoney));
}
else if (param.jobStartMoney > 0 && param.jobEndMoney <= 0)
{
sb.Append(string.Format(" AND use_wage_start>={0} ", param.jobStartMoney));
}
else if (param.jobStartMoney <= 0 && param.jobEndMoney > 0)
{
sb.Append(string.Format(" AND use_wage_end<={0} ", param.jobEndMoney));
}
if (!string.IsNullOrEmpty(param.jobName))
{
_param.Add("@jobName", param.jobName);
}
return(Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param))));
}
/// <summary>
/// 获取一栏数据总量
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public int GetWorkListDataCount(T_UseWorkerParam param)
{
if (null == param)
{
return(0);
}
return(DBSession.IT_UseWorkerDAL.GetWorkListDataCount(param));
}
/// <summary>
/// 获取查看用工一栏数据
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public List <T_UseIssueListModel> GetUseIssueListData(T_UseWorkerParam param)
{
if (null == param)
{
return(null);
}
return(DBSession.IT_UseWorkerDAL.GetUseIssueListData(param));
}
/// <summary>
/// 获取查看用工一栏数据
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public List <T_UseIssueListModel> GetUseIssueListData(T_UseWorkerParam param)
{
sb?.Clear();
sb.Append(string.Format(@"SELECT TOP {0} job.use_id,ISNULL(rel.subLen,0) AS 'subLen',com_id,code.CodeItemName as 'work_type_value',work_type,trade_type,work_city,use_wage_start,
use_wage_end,use_sex,use_age,use_edu,use_ageLimit,code1.ageLimit,use_major,
work_start,CONVERT(varchar(100),issue_start,23) AS 'issue_start',use_status,use_note FROM
(SELECT *, ROW_NUMBER() OVER(ORDER BY use_id) AS RowNumber FROM dbo.T_UseWorker WHERE com_id={1} AND use_status={2} ", param.rows, param.com_id, param.use_status));
if (!string.IsNullOrEmpty(param.jobName))
{
sb.Append(string.Format(@" AND trade_type LIKE '%@jobName%' "));
}
if (param.jobStartMoney > 0 && param.jobEndMoney > 0)
{
sb.Append(string.Format(" AND use_wage_start>={0} AND use_wage_end<={1} ", param.jobStartMoney, param.jobEndMoney));
}
else if (param.jobStartMoney > 0 && param.jobEndMoney <= 0)
{
sb.Append(string.Format(" AND use_wage_start>={0} ", param.jobStartMoney));
}
else if (param.jobStartMoney <= 0 && param.jobEndMoney > 0)
{
sb.Append(string.Format(" AND use_wage_end<={0} ", param.jobEndMoney));
}
sb.Append(string.Format(@") job INNER JOIN
(SELECT item1.*,item2.* FROM
(SELECT item_id,item_code FROM dbo.T_ItemCode WHERE item_code='JobPost') item1 INNER JOIN
(SELECT code_name AS CodeItemName,code_value AS CodeItemValue,item_id AS id FROM dbo.T_ItemCodeMenum) item2 ON item1.item_id = item2.id) code ON job.work_type=code.CodeItemValue LEFT JOIN
(SELECT use_id,subLen=COUNT(use_id) FROM dbo.T_JobResumeRelation WHERE com_id={0} GROUP BY use_id) rel ON job.use_id=rel.use_id INNER JOIN
(SELECT item1.*,item2.* FROM
(SELECT item_id,item_code FROM dbo.T_ItemCode WHERE item_code='WorkExperience') item1 INNER JOIN
(SELECT code_name AS ageLimit,code_value AS CodeItemValue,item_id AS id FROM dbo.T_ItemCodeMenum) item2 ON item1.item_id = item2.id) code1 ON job.use_ageLimit=code1.CodeItemValue WHERE job.RowNumber>{1} ", param.com_id, (param.page - 1) * param.rows));
if (!string.IsNullOrEmpty(param.jobName))
{
sb.Append(string.Format(@" AND code.CodeItemName LIKE '%@jobName%';"));
_param.Add("@jobName", param.jobName);
}
DataTable dt = SqlHelper.ExecuteDataTable(sb.ToString(), CommandType.Text, SqlHelper.GetParameters(_param));
return(HCQ2_Common.Data.DataTableHelper.DataTableToIList <T_UseIssueListModel>(dt));
}
public ActionResult InitJobTable()
{
string jobName = RequestHelper.GetStrByName("jobName");//行业，岗位
int page = RequestHelper.GetIntByName("page"),
rows = RequestHelper.GetIntByName("rows"),
com_id = RequestHelper.GetIntByName("com_id"),
jobStartMoney = RequestHelper.GetIntByName("jobStartMoney"),
jobEndMoney = RequestHelper.GetIntByName("jobEndMoney");
jobName = (!string.IsNullOrEmpty(jobName)) ? HttpUtility.UrlDecode(jobName) : "";
T_UseWorkerParam param = new T_UseWorkerParam {
com_id = com_id, jobName = jobName, jobStartMoney = jobStartMoney, jobEndMoney = jobEndMoney, page = page, rows = rows
};
List <T_UseWorkerModel> user = operateContext.bllSession.T_UseWorker.GetUseWorkListData(param);
TableModel tModel = new TableModel()
{
total = operateContext.bllSession.T_UseWorker.GetWorkListDataCount(param),
rows = user
};
return(Json(tModel, JsonRequestBehavior.AllowGet));
}
