public JsonResult GetSchoolInterviewEntryRateList(QueryBuilder qb)
{
var year = DateTime.Now.Year.ToString();
string yearQuery = this.Request["year"] ?? "";
if (!string.IsNullOrEmpty(yearQuery))
{
year = yearQuery;
}
var linkSql = " where A.Year = '" + year + "'";
//部门：提过需求的所有的部门
var deptSql = @"SELECT Dept, DeptName FROM S_E_Recruitment_DeptRequirementReport A " + linkSql + " GROUP BY Dept,DeptName";
var deptDt = HRSQLDB.ExecuteDataTable(deptSql);
//专业：所有的枚举
var majorDt = GetMajors();
var sumSql = @"SELECT count(1) as Num, Major, Dept, IsPass FROM S_E_Recruitment_Schoolrecruitmentinterview A " + linkSql + " GROUP BY Major, Dept,IsPass";
var sumDt = HRSQLDB.ExecuteDataTable(sumSql);
var entrySql = @"select count(1) as Num, A.Dept,A.Major from S_E_Recruitment_Schoolrecruitmentinterview A " + linkSql +
@"and (select count(1) as num from T_Employee B where B.Interview = A.ID) != 0 GROUP BY A.dept, A.major";
var entryDt = HRSQLDB.ExecuteDataTable(entrySql);
foreach (DataRow dr in majorDt.Rows)
{
if (!deptDt.Columns.Contains(dr["Major"].ToString()))
{
deptDt.Columns.Add(dr["Major"].ToString());
deptDt.Columns.Add(dr["Major"].ToString() + "Pass");
deptDt.Columns.Add(dr["Major"].ToString() + "Entry");
}
}
deptDt.Columns.Add("EntryRate"); //入职率
var result = new List <Dictionary <string, object> >();
var json = JsonHelper.ToJson(deptDt);
result = JsonHelper.ToList(json);
foreach (var item in result)
{
foreach (DataRow dr in majorDt.Rows)
{
var major = dr["Major"].ToString();
var passNumber = sumDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' and Major='" + major + "' " + @" and IsPass = '******' ");
var entryNumber = entryDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' and Major='" + major + "' ");
var pass = passNumber == DBNull.Value ? 0 : passNumber;
var entry = entryNumber == DBNull.Value ? 0 : entryNumber;
item.SetValue(major + "Pass", pass);
item.SetValue(major + "Entry", entry);
}
var deptPassNumber = sumDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + @"' and IsPass = '******' ");
var entryPassNumber = entryDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' ");
var deptPass = deptPassNumber == DBNull.Value ? 0 : deptPassNumber;
var entryPass = entryPassNumber == DBNull.Value ? 0 : entryPassNumber;
double entryRate = Convert.ToInt32(deptPass) != 0 ? entryRate = Convert.ToDouble(entryPass) / Convert.ToDouble(deptPass) * 100 : 0; //入职率=入职人数/面试审批通过人数
item.SetValue("EntryRate", entryRate.ToString("0.00"));
}
var dataGrid = new GridData(result);
dataGrid.total = qb.TotolCount;
return(Json(dataGrid));
}
/// <summary>
/// 获取校园招聘面试通过率
/// </summary>
/// <returns></returns>
public JsonResult GetSchoolInterviewPassRate(QueryBuilder qb)
{
var year = DateTime.Now.Year.ToString();
string yearQuery = this.Request["year"] ?? "";
if (!string.IsNullOrEmpty(yearQuery))
{
year = yearQuery;
}
var linkSql = " where [Year] = '" + year + "'";
//部门：提过需求的所有的部门
var deptSql = @"SELECT Dept, DeptName FROM S_E_Recruitment_DeptRequirementReport " + linkSql + " GROUP BY Dept,DeptName";
var deptDt = HRSQLDB.ExecuteDataTable(deptSql);
//专业：所有的枚举
var majorDt = GetMajors();
//校招面试
var sumSql = @"SELECT count(1) as Num, Major, Dept, IsPass FROM S_E_Recruitment_Schoolrecruitmentinterview " + linkSql + " GROUP BY Major, Dept,IsPass";
var sumDt = HRSQLDB.ExecuteDataTable(sumSql);
foreach (DataRow dr in majorDt.Rows)
{
if (!deptDt.Columns.Contains(dr["Major"].ToString()))
{
deptDt.Columns.Add(dr["Major"].ToString());
deptDt.Columns.Add(dr["Major"].ToString() + "Pass");
deptDt.Columns.Add(dr["Major"].ToString() + "All");
}
}
deptDt.Columns.Add("PassRate"); //面试通过率
var result = new List <Dictionary <string, object> >();
var json = JsonHelper.ToJson(deptDt);
result = JsonHelper.ToList(json);
foreach (var item in result)
{
double deptPass = 0;
double deptAll = 0;
foreach (DataRow dr in majorDt.Rows)
{
var major = dr["Major"].ToString();
var passNumber = sumDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' and Major='" + major + "' " + @" and IsPass = '******' ");
var allNumber = sumDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' and Major='" + major + "' ");
var pass = passNumber == DBNull.Value ? 0 : passNumber;
var all = allNumber == DBNull.Value ? 0 : allNumber;
deptPass += Convert.ToInt32(pass);
deptAll += Convert.ToInt32(all);
item.SetValue(major + "Pass", pass);
item.SetValue(major + "All", all);
}
double rate = 0;
if (deptAll != 0)
{
rate = (deptPass / deptAll) * 100;
}
item.SetValue("PassRate", rate.ToString("0.00"));
}
var dataGrid = new GridData(result);
dataGrid.total = qb.TotolCount;
return(Json(dataGrid));
}
