// // GET: /Home/ public ActionResult Index() { // 首页显示 未出池的需求,未入池的需求,未完成的项目,未完成的下发 User user = this.GetSessionCurrentUser(); if (user == null) { return RedirectToAction("Login", "User", new { ReturnUrl = "/Home/Index" }); } // 根据用户UID, // (1)找到负责的系统,统计未出池的需求(未出池需求,超过三个月未出池需求,未入池需求,超过8天未入池需求) // (2)根据项目计划判断有无超期 // 若为管理员,则显示全部 HomeResult hr = new HomeResult(); if (user.IsAdmin) { hr.ReqLs = dbContext.Database.SqlQuery<HomeReq>("select t.SysID, count(1) as ReqNum, 0 as ReqAcptPerson from Reqs t where t.ReqStat = N'入池' group by t.SysID").ToList(); } else { hr.ReqLs = dbContext.Database.SqlQuery<HomeReq>("select t.SysID, count(1) as ReqNum, @p0 as ReqAcptPerson from Reqs t where t.ReqStat = N'入池' and t.SysID in (select rs.SysID from RetailSystems rs where rs.ReqPersonID = @p0) group by t.SysID", user.UID).ToList(); } // 统计计算未出池的需求总数 int reqLsSum = 0; foreach(HomeReq q in hr.ReqLs) { reqLsSum += q.ReqNum; } ViewBag.ReqLsSum = reqLsSum; ////////////////////////////////////////////////////////////////////// // 判断入池已超过三个月,但是没有出池的需求记录 if (user.IsAdmin) { hr.ReqDelayLS = dbContext.Database.SqlQuery<HomeReq>("select t.SysID, count(1) as ReqNum, 0 as ReqAcptPerson from Reqs t where t.ReqStat = N'入池' and t.AcptDate <= DATEADD(month,-3,GETDATE()) group by t.SysID").ToList(); } else { hr.ReqDelayLS = dbContext.Database.SqlQuery<HomeReq>("select t.SysID, count(1) as ReqNum, @p0 as ReqAcptPerson from Reqs t where t.ReqStat = N'入池' and t.AcptDate <= DATEADD(month,-3,GETDATE()) and t.SysID in (select rs.SysID from RetailSystems rs where rs.ReqPersonID = @p0) group by t.SysID", user.UID).ToList(); } // 统计计算三个月未出池的需求总数 int reqDelayLsSum = 0; foreach (HomeReq q in hr.ReqDelayLS) { reqDelayLsSum += q.ReqNum; } ViewBag.ReqDelayLsSum = reqDelayLsSum; ////////////////////////////////////////////////////////////////////// // 统计所有还没入池的记录(状态为「待评估」) if (user.IsAdmin) { hr.ReqInpoolLS = dbContext.Database.SqlQuery<HomeReq>("select t.SysID, count(1) as ReqNum, 0 as ReqAcptPerson from Reqs t where t.ReqStat = N'待评估' group by t.SysID").ToList(); } else { hr.ReqInpoolLS = dbContext.Database.SqlQuery<HomeReq>("select t.SysID, count(1) as ReqNum, @p0 as ReqAcptPerson from Reqs t where t.ReqStat = N'待评估' and t.SysID in (select rs.SysID from RetailSystems rs where rs.ReqPersonID = @p0) group by t.SysID", user.UID).ToList(); } // 统计计算所有未入池的需求总数 int reqInpoolLsSum = 0; foreach (HomeReq q in hr.ReqInpoolLS) { reqInpoolLsSum += q.ReqNum; } ViewBag.ReqInpoolLsSum = reqInpoolLsSum; ////////////////////////////////////////////////////////////////////// // 判断超过8天还没入池的记录(状态为「待评估」) if (user.IsAdmin) { hr.ReqInpoolDelayLS = dbContext.Database.SqlQuery<HomeReq>("select t.SysID, count(1) as ReqNum, 0 as ReqAcptPerson from Reqs t where t.ReqStat = N'待评估' and t.AcptDate <= DATEADD(day,-8,GETDATE()) group by t.SysID").ToList(); } else { hr.ReqInpoolDelayLS = dbContext.Database.SqlQuery<HomeReq>("select t.SysID, count(1) as ReqNum, @p0 as ReqAcptPerson from Reqs t where t.ReqStat = N'待评估' and t.AcptDate <= DATEADD(day,-8,GETDATE()) and t.SysID in (select rs.SysID from RetailSystems rs where rs.ReqPersonID = @p0) group by t.SysID", user.UID).ToList(); } // 统计计算超过8天未入池的需求总数 int reqInpoolDelayLsSum = 0; foreach (HomeReq q in hr.ReqInpoolDelayLS) { reqInpoolDelayLsSum += q.ReqNum; } ViewBag.ReqInpoolDelayLsSum = reqInpoolDelayLsSum; ////////////////////////////////////////////////////////////////////// // 筛选出各个阶段延期的项目(只统计项目状态为:进行中) // 首先获得所有有时间计划的项目列表,对没有时间计划的项目将不统计其延期的情况 List<ProjPlan> plans = dbContext.ProjPlans.ToList(); List<Proj> projs = dbContext.Projs.Where(p=>p.ProjStat=="进行中").ToList(); List<HomeProjDelay> delays = new List<HomeProjDelay>(); foreach (ProjPlan plan in plans) { Proj p = new Proj(); if (user.IsAdmin) { p = projs.Find(a => a.ProjID == plan.ProjID); } // 如果是非管理员登陆,显示自己的延期项目 else { p = projs.Find(a => a.ProjID == plan.ProjID && a.ReqAnalysisID == user.UID); } // 如果筛选出项目在项目计划列表中,那么判断时间是否延期 if (p != null) { // 判断各个阶段的时间是否延期 if (p.OutlineEndDate == null && plan.OutlineFinishDate <= DateTime.Now) { HomeProjDelay projDelay = new HomeProjDelay(); projDelay.ProjId = p.ProjID; projDelay.DelayDetail = "需求大纲结束编写"; delays.Add(projDelay); continue; } else if (p.ReviewAcptDate == null && plan.ReviewStartDate <= DateTime.Now) { HomeProjDelay projDelay = new HomeProjDelay(); projDelay.ProjId = p.ProjID; projDelay.DelayDetail = "业需开始评审"; delays.Add(projDelay); continue; } else if (p.ReqPublishDate == null && plan.ReviewFinishDate <= DateTime.Now) { HomeProjDelay projDelay = new HomeProjDelay(); projDelay.ProjId = p.ProjID; projDelay.DelayDetail = "业需结束评审"; delays.Add(projDelay); continue; } else if (p.TechFeasiReviewFinishDate == null && plan.TechFeasiReviewFinishDate <= DateTime.Now) { HomeProjDelay projDelay = new HomeProjDelay(); projDelay.ProjId = p.ProjID; projDelay.DelayDetail = "技术可行性分析报告评审结束"; delays.Add(projDelay); continue; } else if (p.SoftBudgetFinishDate == null && plan.SoftBudgetFinishDate <= DateTime.Now) { HomeProjDelay projDelay = new HomeProjDelay(); projDelay.ProjId = p.ProjID; projDelay.DelayDetail = "软件实施投入预算结束"; delays.Add(projDelay); continue; } else if (p.ImplementPlansFinishDate == null && plan.ImplementPlansFinishDate <= DateTime.Now) { HomeProjDelay projDelay = new HomeProjDelay(); projDelay.ProjId = p.ProjID; projDelay.DelayDetail = "实施方案结束"; delays.Add(projDelay); continue; } } } hr.ProjDetails = delays; ////////////////////////////////////////////////////////////////////// // 列出超过计划下发日期仍未下发的 string sql = "select distinct t.RlsNo, t.SecondRlsNo, t.PlanRlsDate from Reqs t where ((t.RlsNo is not null and t.RlsDate is null ) or (t.SecondRlsNo is not null and t.SecondRlsDate is null )) and t.PlanRlsDate < getdate()-1 and t.ReqStat=N'出池'"; if (!IsAdminNow()) { sql += " and t.ReqAcptPerson = " + user.UID; } hr.RlsDelayLS = dbContext.Database.SqlQuery<HomeRlsDelay>(sql).ToList(); return View(hr); }
/// <summary> /// 项目导入 /// </summary> /// <param name="worksheet"></param> /// <param name="fileName"></param> /// <returns></returns> private string ProjExcel(ExcelWorksheet worksheet, string fileName) { int rowStart = worksheet.Dimension.Start.Row; //工作区开始行号 int rowEnd = worksheet.Dimension.End.Row; //工作区结束行号 var ls = this.GetProjList(); for (int row = rowStart + 1; row <= rowEnd; row++) { // 第一列为空则结束 if (worksheet.Cells[row, 1] == null) break; // 第1列,项目名称 string projName = worksheet.Cells[row, 1].GetValue<string>(); // 项目名称 重复的不导入 if (ls.Find(a => a.ProjName == projName) != null) { continue; } // 第5列,需求分析师,转成UID string reqPerson = worksheet.Cells[row, 5].GetValue<string>(); var u = this.GetUserList().Find(a => a.Realname == reqPerson); int uid = u == null ? 0 : u.UID; Proj r = new Proj(); // 按列赋值 r.ProjName = projName; r.ProjNo = worksheet.Cells[row, 2].GetValue<string>(); r.HostDept = worksheet.Cells[row, 3].GetValue<string>(); r.ProjLevel = worksheet.Cells[row, 4].GetValue<string>(); r.ReqAnalysisID = uid; r.BusiPerson = worksheet.Cells[row, 6].GetValue<string>(); r.ProjManager = worksheet.Cells[row, 7].GetValue<string>(); r.Architect = worksheet.Cells[row, 8].GetValue<string>(); r.SurveyRemark = worksheet.Cells[row, 12].GetValue<string>(); r.OutlineWriter = worksheet.Cells[row, 13].GetValue<string>(); r.OutlineAuditPerson = worksheet.Cells[row, 16].GetValue<string>(); r.OutlineRemark = worksheet.Cells[row, 18].GetValue<string>(); r.ReqWriter = worksheet.Cells[row, 19].GetValue<string>(); r.ReqRemark = worksheet.Cells[row, 24].GetValue<string>(); r.RulesRemark = worksheet.Cells[row, 27].GetValue<string>(); r.CheckResult = worksheet.Cells[row, 30].GetValue<string>(); r.Remark = worksheet.Cells[row, 31].GetValue<string>(); //r.IsReqTrack = false; // 默认赋值false // 对于可能为空的日期单独处理 string ProAcptDate = worksheet.Cells[row, 9].GetValue<string>(); string SurveyGroupFoundDate = worksheet.Cells[row, 10].GetValue<string>(); string SurveyFinishDate = worksheet.Cells[row, 11].GetValue<string>(); string OutlineStartDate = worksheet.Cells[row, 14].GetValue<string>(); string OutlineEndDate = worksheet.Cells[row, 15].GetValue<string>(); string OutlinePublishDate = worksheet.Cells[row, 17].GetValue<string>(); string ReqStartDate = worksheet.Cells[row, 20].GetValue<string>(); string ReviewAcptDate = worksheet.Cells[row, 21].GetValue<string>(); string ReviewMeetingDate = worksheet.Cells[row, 22].GetValue<string>(); string ReqPublishDate = worksheet.Cells[row, 23].GetValue<string>(); string RulesStartDate = worksheet.Cells[row, 25].GetValue<string>(); string RulesPublishDate = worksheet.Cells[row, 26].GetValue<string>(); string ProjCheckAcptDate = worksheet.Cells[row, 28].GetValue<string>(); string ProjPublishDate = worksheet.Cells[row, 29].GetValue<string>(); if (!string.IsNullOrEmpty(ProAcptDate)) r.ProAcptDate = DateTime.Parse(worksheet.Cells[row, 9].GetValue<string>()); if (!string.IsNullOrEmpty(SurveyGroupFoundDate)) r.SurveyGroupFoundDate = DateTime.Parse(worksheet.Cells[row, 10].GetValue<string>()); if (!string.IsNullOrEmpty(SurveyFinishDate)) r.SurveyFinishDate = DateTime.Parse(worksheet.Cells[row, 11].GetValue<string>()); if (!string.IsNullOrEmpty(OutlineStartDate)) r.OutlineStartDate = DateTime.Parse(worksheet.Cells[row, 14].GetValue<string>()); if (!string.IsNullOrEmpty(OutlineEndDate)) r.OutlineEndDate = DateTime.Parse(worksheet.Cells[row, 15].GetValue<string>()); if (!string.IsNullOrEmpty(OutlinePublishDate)) r.OutlinePublishDate = DateTime.Parse(worksheet.Cells[row, 17].GetValue<string>()); if (!string.IsNullOrEmpty(ReqStartDate)) r.ReqStartDate = DateTime.Parse(worksheet.Cells[row, 20].GetValue<string>()); if (!string.IsNullOrEmpty(ReviewAcptDate)) r.ReviewAcptDate = DateTime.Parse(worksheet.Cells[row, 21].GetValue<string>()); if (!string.IsNullOrEmpty(ReviewMeetingDate)) r.ReviewMeetingDate = DateTime.Parse(worksheet.Cells[row, 22].GetValue<string>()); if (!string.IsNullOrEmpty(ReqPublishDate)) r.ReqPublishDate = DateTime.Parse(worksheet.Cells[row, 23].GetValue<string>()); if (!string.IsNullOrEmpty(RulesStartDate)) r.RulesStartDate = DateTime.Parse(worksheet.Cells[row, 25].GetValue<string>()); if (!string.IsNullOrEmpty(RulesPublishDate)) r.RulesPublishDate = DateTime.Parse(worksheet.Cells[row, 26].GetValue<string>()); if (!string.IsNullOrEmpty(ProjCheckAcptDate)) r.ProjCheckAcptDate = DateTime.Parse(worksheet.Cells[row, 28].GetValue<string>()); if (!string.IsNullOrEmpty(ProjPublishDate)) r.ProjPublishDate = DateTime.Parse(worksheet.Cells[row, 29].GetValue<string>()); dbContext.Projs.Add(r); } // 保存 int realNum = dbContext.SaveChanges(); // 导入完了要更新内存 this.Update(3); return string.Format("<p class='alert alert-success'>《{0}》处理成功!共{1}条数据,实际导入{2}条数据</p>", fileName, rowEnd - rowStart, realNum); }