protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
//从请求中获取排序列
string orderString = hidOrderBy.Value.Trim();
//排序：默认为升序
string orderBy = "asc";
//要排序的字段，如果为空，默认为"RectApplyNo"
string orderByCol = (!string.IsNullOrEmpty(orderString)) ? orderString.Substring(0, orderString.Length - 2) : "RectApplyNo";
//降序时如果设置为降序
if (orderString.EndsWith("_d"))
{
//排序：降序
orderBy = "desc";
}
//从请求中获取当前页
//int pageIndex = Convert.ToInt32(txtToPage.Value);
////从请求中获取每页显示记录数
//int pageCount = Convert.ToInt32(txtShowPageCount.Value);
////跳过记录数
//int skipRecord = (pageIndex - 1) * pageCount;
RectPlanSearchModel searchModel = new RectPlanSearchModel();
//设置查询条件
//活动编号
searchModel.RectPlanNo = txtRectPlanNo.Value.Trim();
//主题
searchModel.Title = txtTitle.Value.Trim();
//开始时间
searchModel.StartDate = txtStartDate.Text.Trim();
searchModel.StartToDate = txtStartToDate.Text.Trim();
//负责人
string selectPrincipal = txtPrincipalID.Value;
searchModel.PrincipalID = selectPrincipal;
//招聘人数
searchModel.PersonCount = txtPersonCount.Value .Trim ();
//活动状态
searchModel.StatusID = Request.Form["ddlStatus"].ToString();
string ord = orderByCol + " " + orderBy;
int TotalCount = 0;
//查询数据
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(txtToPage.Value))
{
dt = RectPlanBus.SearchSpecialExport(searchModel, 1, 10000, ord, ref TotalCount);//查询数据
}
string[,] ht = {
{ "招聘计划编号", "RectPlanNo "},
{ "主题", "Title"},
{ "开始时间", "StartDate" },
{ "结束时间", "EndDate" },
{ "负责人", "PrincipalName"},
{ "招聘人数", "PersonCount"},
{ "已面试","ReviewStatus"},
{ "已录用","EmployStatus"},
{ "计划状态","StatusName"}
};
ExportExcel(dt, ht, "", "招聘计划列表");
}
public static DataTable SearchSpecialExport(RectPlanSearchModel model, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return RectPlanDBHelper.SearchRectExport(model, pageIndex, pageCount, ord, ref TotalCount);
}
public static DataTable SearchRectExport(RectPlanSearchModel model,int pageIndex,int pageCount,string ord, ref int TotalCount)
{
#region 查询语句
//查询SQL拼写
StringBuilder searchSql = new StringBuilder();
searchSql.AppendLine(" select * from ( SELECT A.ID AS ID ");
searchSql.AppendLine(" ,A.PlanNo AS RectPlanNo ");
searchSql.AppendLine(" ,A.Title AS Title ");
searchSql.AppendLine(" ,A.Status AS Status ");
searchSql.AppendLine(" ,A.Principal AS Principal ");
searchSql.AppendLine(" ,CONVERT(VARCHAR(10),A.StartDate,21) ");
searchSql.AppendLine(" AS StartDate ");
searchSql.AppendLine(" ,CONVERT(VARCHAR(10),A.EndDate,21) ");
searchSql.AppendLine(" AS EndDate ");
searchSql.AppendLine(" ,ISNULL(B.EmployeeName, '') AS PrincipalName ");
searchSql.AppendLine(" , CASE A.Status ");
searchSql.AppendLine(" WHEN '0' THEN '未完成' ");
searchSql.AppendLine(" WHEN '1' THEN '已完成' ");
searchSql.AppendLine(" ELSE '' ");
searchSql.AppendLine(" END AS StatusName ");
searchSql.AppendLine(" ,ISNULL(( ");
searchSql.AppendLine(" Select SUM(PersonCount) ");
searchSql.AppendLine(" FROM officedba.RectGoal C ");
searchSql.AppendLine(" WHERE C.CompanyCD = A.CompanyCD ");
searchSql.AppendLine(" AND C.PlanNo = A.PlanNo ");
searchSql.AppendLine(" ), 0) AS PersonCount ");
searchSql.AppendLine(" ,( ");
searchSql.AppendLine(" Select COUNT(ID) ");
searchSql.AppendLine(" FROM officedba.RectInterview E ");
searchSql.AppendLine(" WHERE E.CompanyCD=A.CompanyCD and E.PlanID = A.PlanNo ");
searchSql.AppendLine(" AND (E.InterviewResult = '1' or E.FinalResult is not null ) ");
searchSql.AppendLine(" ) AS ReviewStatus ");
searchSql.AppendLine(" ,( ");
searchSql.AppendLine(" Select COUNT(ID) ");
searchSql.AppendLine(" FROM officedba.RectInterview F ");
searchSql.AppendLine(" WHERE F.CompanyCD=A.CompanyCD and F.PlanID = A.PlanNo ");
searchSql.AppendLine(" AND F.FinalResult = '1' ");
searchSql.AppendLine(" ) AS EmployStatus ,isnull( Convert(varchar(100),A.ModifiedDate,23),'') AS ModifiedDate ");
searchSql.AppendLine(" FROM officedba.RectPlan A ");
searchSql.AppendLine(" left join officedba.EmployeeInfo B ");
searchSql.AppendLine(" on B.companyCD=A.companyCD AND A.Principal = B.ID ");
searchSql.AppendLine(" WHERE ");
searchSql.AppendLine(" A.CompanyCD = @CompanyCD ) x where 1=1 ");
#endregion
//定义查询的命令
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
//活动编号
if (!string.IsNullOrEmpty(model.RectPlanNo))
{
searchSql.AppendLine(" AND x.RectPlanNo LIKE '%' + @RectPlanNo + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@RectPlanNo", model.RectPlanNo));
}
//主题
if (!string.IsNullOrEmpty(model.Title))
{
searchSql.AppendLine(" AND x.Title LIKE '%' + @Title + '%' ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Title", model.Title));
}
//开始时间
if (!string.IsNullOrEmpty(model.StartDate))
{
searchSql.AppendLine(" AND x.StartDate >= @StartDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartDate", model.StartDate));
}
if (!string.IsNullOrEmpty(model.StartToDate))
{
searchSql.AppendLine(" AND x.StartDate <= @StartToDate ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StartToDate", model.StartToDate));
}
//负责人
if (!string.IsNullOrEmpty(model.PrincipalID))
{
searchSql.AppendLine(" AND x.Principal = @PrincipalID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PrincipalID", model.PrincipalID));
}
//招聘人数
if (!string.IsNullOrEmpty(model.PersonCount))
{
searchSql.AppendLine(" AND x.PersonCount= @PersonCount ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@PersonCount", model.PersonCount));
}
//活动状态
if (!string.IsNullOrEmpty(model.StatusID))
{
searchSql.AppendLine(" AND x.Status = @StatusID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StatusID", model.StatusID));
}
//指定命令的SQL文
comm.CommandText = searchSql.ToString();
//执行查询
//return SqlHelper.ExecuteSearch(comm);
return SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount); //执行查询
}
/// <summary>
/// 查询招聘活动信息
/// </summary>
/// <param name="model">查询条件</param>
/// <returns></returns>
public static DataTable SearchRectPlanInfo(RectPlanSearchModel model)
{
//获取登陆用户信息
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
//设置公司代码
model.CompanyCD = userInfo.CompanyCD;
return RectPlanDBHelper.SearchRectPlanInfo(model);
}