/// <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)); }