/// <summary>
/// 变更升版验证
/// </summary>
/// <param name="Data"></param>
/// <returns></returns>
public JsonResult ValidateUpVersion(string Data)
{
var data = JsonHelper.ToObject(Data);
var plan = this.GetEntityByID(data.GetValue("ID"));
if (plan == null)
{
throw new Formula.Exceptions.BusinessException("没有找到指定对的培训计划，不能进行变更操作");
}
if (plan.FlowPhase != "End" || String.IsNullOrEmpty(plan.RelateID))
{
throw new Formula.Exceptions.BusinessException("不能对没有审批完成的培训计划进行变更操作");
}
string FlowVersionNumberStart = "1";
if (!string.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings["FlowVersionNumberStart"]))
{
FlowVersionNumberStart = System.Configuration.ConfigurationManager.AppSettings["FlowVersionNumberStart"];
}
string sql = string.Format("select ID,VersionNumber,FlowPhase from {0} where 1=1 and RelateID='{1}'", "T_Trainmanagement_Departmenttrainplan", plan.RelateID);
sql += " order by convert(int,VersionNumber) desc";
var dt = HRSQLDB.ExecuteDataTable(sql);
var versions = string.Join(",", dt.AsEnumerable().Select(c => c["VersionNumber"].ToString()));
return(Json(new
{
ID = plan.ID,
RelateID = plan.RelateID,
VersionNumber = versions[0],
Versions = versions
}));
}
protected override void OnFlowEnd(T_Qualification_PostTotal entity, Workflow.Logic.Domain.S_WF_InsTaskExec taskExec, Workflow.Logic.Domain.S_WF_InsDefRouting routing)
{
if (entity == null)
{
return;
}
var sql = string.Format("select * from T_Qualification_Postqualificationmanagement where T_Qualification_PostTotalID ='{0}'", entity.ID);
var dt = HRSQLDB.ExecuteDataTable(sql);
var dicList = FormulaHelper.DataTableToListDic(dt);
foreach (var item in dicList)
{
var userid = item.GetValue("Users");
var quali = BusinessEntities.Set <S_Qualification_Postqualificationmanagement>().FirstOrDefault(p => p.Users == userid);
if (quali == null)
{
quali = new S_Qualification_Postqualificationmanagement();
quali.ID = FormulaHelper.CreateGuid();
BusinessEntities.Set <S_Qualification_Postqualificationmanagement>().Add(quali);
}
UpdateEntity <S_Qualification_Postqualificationmanagement>(quali, item);
}
BusinessEntities.SaveChanges();
sql = string.Format("update T_Qualification_Postqualificationmanagement set IsApprove='True' where T_Qualification_PostTotalID ='{0}'", entity.ID);
HRSQLDB.ExecuteNonQuery(sql);
}
// GET: /AutoForm/Recruitment_Schoolrecruitmentneedsum/
public ActionResult Recruitment_SchoolrecruitmentneedSummaryList()
{
var year = DateTime.Now.Year;
var sql = @"SELECT Dept, DeptName FROM S_E_Recruitment_DeptRequirementReport where [Year] = " + year + @" GROUP BY Dept, DeptName";
var dt = HRSQLDB.ExecuteDataTable(sql);
ViewBag.deptDataTable = dt;
return(View());
}
public JsonResult GetPostInfoList(QueryBuilder qb)
{
var id = GetQueryString("ID");
var sql = "";
var oldSql = "";
if (!string.IsNullOrEmpty(id))
{
sql = string.Format(@"select *from T_Qualification_Postqualificationmanagement where T_Qualification_PostTotalID='{0}' ", id);
}
else
{
//自动查询出当前系统未提交审批的资质变更
sql = string.Format(@"select *from T_Qualification_Postqualificationmanagement where isnull(IsApprove,'False')='False'");
}
DataTable dt = HRSQLDB.ExecuteDataTable(sql, qb);
string[] UserIDs = dt.AsEnumerable().Select(d => d.Field <string>("Users")).ToArray();
//根据查询出来的人员查找现在系统已经存在的资质信息，用于页面列表展示变更颜色
sql = string.Format(@"select * from S_Qualification_Postqualificationmanagement where Users in ('{0}')", string.Join(",", UserIDs).Replace(",", "','"));
DataTable qualifiDt = HRSQLDB.ExecuteDataTable(sql);
//将已存在的人员资质写入到资质审批信息中
foreach (DataRow item in dt.Rows)
{
var rowData = qualifiDt.Select(" Users = '" + item["Users"].ToString() + "'");
if (rowData.Length == 0)
{
continue;
}
foreach (DataColumn columns in qualifiDt.Columns)
{
if (!dt.Columns.Contains("Old_" + columns.ColumnName))
{
dt.Columns.Add("Old_" + columns.ColumnName);
}
item["Old_" + columns.ColumnName] = rowData[0][columns.ColumnName];
}
}
GridData gd = new GridData(dt);
gd.total = qb.TotolCount;
return(Json(gd));
}
public JsonResult GetSchoolrecruitmentneedsumList(QueryBuilder qb)
{
var year = DateTime.Now.Year;
//专业
var sql = "SELECT Major FROM S_E_Recruitment_DeptRequirementReport where Year = " + year + @" group by major";
var dt = HRSQLDB.ExecuteDataTable(sql);
//部门
var deptsql = "select Dept from S_E_Recruitment_DeptRequirementReport where Year =" + year + " group by Dept";
var deptdt = HRSQLDB.ExecuteDataTable(deptsql);
var sumsql = "select Dept,Major,sum(Number) as Number from S_E_Recruitment_DeptRequirementReport where Year =" + year + " group by Dept,Major";
var sumdt = HRSQLDB.ExecuteDataTable(sumsql);
foreach (DataRow dr in deptdt.Rows)
{
if (!dt.Columns.Contains(dr["Dept"].ToString()))
{
dt.Columns.Add(dr["Dept"].ToString());
}
}
var result = new List <Dictionary <string, object> >();
var json = JsonHelper.ToJson(dt);
result = JsonHelper.ToList(json);
foreach (var item in result)
{
foreach (DataRow dr in deptdt.Rows)
{
var dept = dr["Dept"].ToString();
var Number = sumdt.Compute("sum(Number)", " Major='" + item.GetValue("Major") + "' and Dept='" + dept + "' ");
item.SetValue(dept, Number == DBNull.Value ? 0 : Number);
}
}
var dataGrid = new GridData(result);
dataGrid.total = qb.TotolCount;
return(Json(dataGrid));
}
public JsonResult GetSociaDynamicList(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_SZRCXQJH A " + linkSql + " GROUP BY Dept,DeptName";
var deptDt = HRSQLDB.ExecuteDataTable(deptSql);
var sumSql = @"SELECT count(1) as Num, Dept, IsPass FROM S_E_Recruitment_Socialrecruitmentemployment A " + linkSql + " GROUP BY Dept,IsPass";
var sumDt = HRSQLDB.ExecuteDataTable(sumSql);
var needSql = @"SELECT sum(Number) as Num, Dept FROM S_E_Recruitment_DeptRequirementReport_SZRCXQJH A " + linkSql + " GROUP BY Dept";
var needDt = HRSQLDB.ExecuteDataTable(needSql);
var entrySql = @"select count(1) as Num, A.Dept from S_E_Recruitment_Socialrecruitmentemployment A " + linkSql +
@"and (select count(1) as num from T_Employee B where B.Interview = A.ID) != 0 GROUP BY A.dept";
var entryDt = HRSQLDB.ExecuteDataTable(entrySql);
deptDt.Columns.Add("Pass"); //面试通过人数
deptDt.Columns.Add("All"); //面试总人数
deptDt.Columns.Add("PassRate"); //面试通过率
deptDt.Columns.Add("Need"); //招聘需求人数
deptDt.Columns.Add("Entry"); //入职人数
deptDt.Columns.Add("EntryRate"); //入职率
deptDt.Columns.Add("FinishRate"); //招聘完成率
var result = new List <Dictionary <string, object> >();
var json = JsonHelper.ToJson(deptDt);
result = JsonHelper.ToList(json);
foreach (var item in result)
{
var passNumber = sumDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "'" + @" and IsPass = '******' "); //面试通过人数
var allNumber = sumDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "'"); //面试总人数
var pass = passNumber == DBNull.Value ? 0 : passNumber;
var all = allNumber == DBNull.Value ? 0 : allNumber;
double passRate = Convert.ToInt32(all) != 0 ? Convert.ToDouble(pass) / Convert.ToDouble(all) * 100 : 0; //面试通过率=面试审批通过人数/面试审批总人数
var needNumber = needDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "'"); //招聘需求人数
var entryNumber = entryDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "'"); //入职人数
var entry = entryNumber == DBNull.Value ? 0 : entryNumber;
var need = needNumber == DBNull.Value ? 0 : needNumber;
double entryRate = Convert.ToInt32(pass) != 0 ? Convert.ToDouble(entry) / Convert.ToDouble(pass) * 100 : 0; //入职率=入职人数/面试审批通过人数
double finishRate = Convert.ToInt32(need) != 0 ? Convert.ToDouble(entry) / Convert.ToDouble(need) * 100 : 0; //招聘完成率=入职人数/招聘计划人数
item.SetValue("Pass", pass);
item.SetValue("All", all);
item.SetValue("PassRate", passRate.ToString("0.00"));
item.SetValue("Need", need);
item.SetValue("Entry", entry);
item.SetValue("EntryRate", entryRate.ToString("0.00"));
item.SetValue("FinishRate", finishRate.ToString("0.00"));
}
var dataGrid = new GridData(result);
dataGrid.total = qb.TotolCount;
return(Json(dataGrid));
}
public JsonResult GetUserInfo()
{
var UserID = GetQueryString("UserID");
var Employee = BusinessEntities.Set <T_Employee>().Where(c => c.UserID == UserID).FirstOrDefault();
if (Employee == null)
{
throw new BusinessException("员工不存在！");
}
var dic = new Dictionary <string, string>();
dic.SetValue("Number", Employee.Code);
dic.SetValue("ApplyDept", Employee.DeptID);
dic.SetValue("ApplyDeptName", Employee.DeptIDName);
dic.SetValue("Counterfeitdecoy", Employee.Name);
var Vacationdays = "0";
var sql = "select sum(Actualdays) as Actualdays from T_AttendanceLeaveApply where FlowPhase='End' and year(StartDate)=year(getdate()) and ApplyUser='******'";
var dt = HRSQLDB.ExecuteDataTable(sql);
if (dt.Rows.Count > 0)
{
Vacationdays = dt.Rows[0]["Actualdays"].ToString();
}
dic.SetValue("Vacationdays", Vacationdays);
dic.SetValue("Workingyears", Employee.Lengthofservice);
var ApplyUser = UserID;
var ApplyDept = Employee.DeptID;
sql = "select Character from S_A_Org where ID='" + ApplyDept + "'";
var BasesqlDB = SQLHelper.CreateSqlHelper(ConnEnum.Base);
var orgDt = BasesqlDB.ExecuteDataTable(sql);
dic.SetValue("IsPrincipal", "0");
var Character = orgDt.Rows[0]["Character"].ToString();
if (Character == "二级部门")
{//如果当前人的党群部门是二级部门则送所长，如果当前人是所长送部门正职
var RoleSql = "select UserID from S_A_Role a inner join S_A__OrgRoleUser b on a.ID=b.RoleID where a.Code='所级正职领导' and b.orgID='" + ApplyDept + "' ";
var RoleDt = BasesqlDB.ExecuteDataTable(RoleSql);
if (RoleDt.Rows.Count > 0)
{
var userID = RoleDt.Rows[0]["UserID"].ToString();
if (userID != ApplyUser)
{
dic.SetValue("Director", userID);
}
RoleSql = @"select * from S_A_Role a inner join S_A__OrgRoleUser b on a.ID=b.RoleID
inner join (select ParentID from S_A_Org where ID='" + ApplyDept + @"' ) c on c.ParentID=b.orgID
where a.Code='部门正职' ";
RoleDt = BasesqlDB.ExecuteDataTable(RoleSql);
if (RoleDt.Rows.Count > 0)
{
dic.SetValue("Principal", RoleDt.Rows[0]["UserID"].ToString());
}
}
}
else
{
var RoleSql = "select UserID from S_A_Role a inner join S_A__OrgRoleUser b on a.ID=b.RoleID where a.Code='部门正职' and b.orgID='" + ApplyDept + "' ";
var RoleDt = BasesqlDB.ExecuteDataTable(RoleSql);
if (RoleDt.Rows.Count > 0)
{
var userID = RoleDt.Rows[0]["UserID"].ToString();
if (userID != ApplyUser)
{
dic.SetValue("Principal", userID);
}
else
{
RoleSql = "select UserID from S_A_Role a inner join S_A__OrgRoleUser b on a.ID=b.RoleID where a.Code='上级主管领导' and b.orgID='" + ApplyDept + "' ";
RoleDt = BasesqlDB.ExecuteDataTable(RoleSql);
if (RoleDt.Rows.Count > 0)
{
userID = RoleDt.Rows[0]["UserID"].ToString();
dic.SetValue("Principal", userID);
dic.SetValue("IsPrincipal", "1");
}
}
}
}
return(Json(dic));
}
public JsonResult GetSchoolInterviewFinishRateList(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 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);
var needSql = @"SELECT sum(Number) as Num, major, dept FROM S_E_Recruitment_DeptRequirementReport A " + linkSql + " GROUP BY major, dept";
var needDt = HRSQLDB.ExecuteDataTable(needSql);
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() + "Entry");
deptDt.Columns.Add(dr["Major"].ToString() + "Need");
}
}
deptDt.Columns.Add("FinishRate"); //招聘完成率
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 entryNumber = entryDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' and Major='" + major + "' ");
var needNumber = needDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' and Major='" + major + "' ");
item.SetValue(major + "Entry", entryNumber == DBNull.Value ? 0 : entryNumber);
item.SetValue(major + "Need", needNumber == DBNull.Value ? 0 : needNumber);
}
var entryDeptNumber = entryDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' ");
var needDeptNumber = needDt.Compute("sum(Num)", " Dept='" + item.GetValue("Dept") + "' ");
var entryDept = entryDeptNumber == DBNull.Value ? 0 : entryDeptNumber;
var needDept = needDeptNumber == DBNull.Value ? 0 : needDeptNumber;
double finishRate = Convert.ToInt32(needDept) != 0 ? Convert.ToDouble(entryDept) / Convert.ToDouble(needDept) * 100 : 0; //招聘完成率=入职人数/招聘计划人数
item.SetValue("FinishRate", finishRate.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));
}