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