/// <summary> /// 加载责任人列表 /// 2017/06/06(zhuguanjun) /// </summary> /// <param name="TroubleID"></param> /// <returns></returns> public DataTable GetTroubleWorkList(string TroubleID) { List <QueryField> qf = new List <QueryField>(); qf.Add(new QueryField() { Name = "TROUBLEID", Type = QueryFieldType.String, Value = TroubleID }); StringBuilder sql = new StringBuilder(); sql.Append(" SELECT r.*,s.Name as ManagerName FROM TROUBLEWORK r"); sql.Append(" LEFT JOIN STAKEHOLDERS s ON r.Manager= substr(s.Id,1,36) and s.status=1 "); sql.Append(" WHERE r.TROUBLEID =@TROUBLEID "); DataSet ds = NHHelper.ExecuteDataset(sql.ToString(), qf); if (ds != null && ds.Tables.Count > 0) { return(ds.Tables[0]); } else { return(new DataTable()); } }
/// <summary> /// 通过NodeID获取附件列表 /// Created:20170612 (ChengMengjia) /// </summary> /// <param name="NodeID"></param> /// <param name="type"></param> /// <returns></returns> public List <TroubleFiles> GetFilesByNodeID(string NodeID, int?type) { List <QueryField> qf = new List <QueryField>(); StringBuilder sql = new StringBuilder(); sql.Append(" SELECT a.* FROM TroubleFiles a"); sql.Append(" LEFT JOIN Trouble b ON a.TroubleID= substr(b.ID,1,36) and b.Status=1 "); sql.Append(" WHERE a.Status =1 and b.NodeID=@NodeID "); if (type != null) { sql.Append(" and a.Type=@Type "); qf.Add(new QueryField() { Name = "Type", Type = QueryFieldType.Numeric, Value = type }); } qf.Add(new QueryField() { Name = "NodeID", Type = QueryFieldType.String, Value = NodeID.Substring(0, 36) }); DataSet ds = NHHelper.ExecuteDataset(sql.ToString(), qf); if (ds != null && ds.Tables.Count > 0) { return(JsonHelper.TableToList <TroubleFiles>(ds.Tables[0])); } else { return(new List <TroubleFiles>()); } }
/// <summary> /// 点击干系人列表加载干系人信息和干系人沟通方式列表 /// </summary> /// <param name="PID"></param> /// <param name="stakeholders">干系人实体</param> /// <param name="communication">沟通方式集合</param> public void GetCommunicationMatix(List <QueryField> qf, out Stakeholders stakeholders, out List <CommunicationFXFA> FXFAlist) { stakeholders = new Stakeholders(); FXFAlist = new List <CommunicationFXFA>(); #region 干系人 StringBuilder sql = new StringBuilder(); sql.Append(" select * from stakeholders s where s.PID=@PID and substr(s.Id,1,37)||'1'=@Id and s.Status=@Status"); sql.Append(" order by s.UPDATED desc , s.CREATED desc"); DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qf); stakeholders = dt == null ? new Stakeholders() : JsonHelper.TableToEntity <Stakeholders>(dt); #endregion #region 沟通方式列表 StringBuilder sql2 = new StringBuilder(); sql2.Append(" select fxfa.*,c.Name as CNAME from communicationFXFA fxfa left join communication c on c.Id = fxfa.CID"); sql2.Append(" where fxfa.SID=@ID and c.PID=@PID and c.Status=@Status and fxfa.Status=@Status"); sql2.Append(" order by fxfa.CREATED"); DataSet ds2 = NHHelper.ExecuteDataset(sql2.ToString(), qf); var dt2 = new DataTable(); if (ds2 != null && ds2.Tables.Count > 0) { dt2 = ds2.Tables[0]; } FXFAlist = JsonHelper.TableToList <CommunicationFXFA>(dt2).ToList(); #endregion }
/// <summary> /// 项目成果取得 /// Created:20170328(xuxb) /// </summary> /// <param name="PID"></param> /// <returns></returns> public DataTable GetProjectResult(string PID) { List <QueryField> qf = new List <QueryField>(); StringBuilder sql = new StringBuilder(); sql.Append(" select case when sum(d.Workload) is null then 0 else sum(d.Workload) end as TotalWork, "); sql.Append(" case when sum(d.WorkLoad * (np.PType - 1)/4) is null then 0 else sum(d.WorkLoad * (np.PType - 1)/4) end as CompleteWork "); sql.Append(" from PNode p "); sql.Append(" left join DeliverablesJBXX d on substr(p.ID,1,36) = d.NodeID and d.Status = 1 "); sql.Append(" left join NodeProgress np on substr(p.ID,1,36) = np.NodeID and np.Status = 1 "); sql.Append(" where p.PID=@PID and p.status=1"); qf.Add(new QueryField() { Name = "PID", Type = QueryFieldType.String, Value = PID }); return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0]); }
/// <summary> /// 项目风险取得 /// Created:20170329(xuxb) /// </summary> /// <param name="PID"></param> /// <returns></returns> public DataTable GetProjectRisk(string PID) { List <QueryField> qf = new List <QueryField>(); StringBuilder sql = new StringBuilder(); sql.Append(" select IFNULL(riskFind,0) as riskFind,IFNULL(riskAssess,0) as riskAssess,"); sql.Append(" IFNULL(riskHandle,0) as riskHandle from ( "); sql.Append(" select sum( case when FindDate is null then 0 else 1 end) as riskFind, "); sql.Append(" sum( case when AssessDate is null then 0 else 1 end) as riskAssess, "); sql.Append(" sum( case when HandleDate is null then 0 else 1 end) as riskHandle "); sql.Append(" from Risk r "); sql.Append(" where r.PID=@PID and r.status=1)"); qf.Add(new QueryField() { Name = "PID", Type = QueryFieldType.String, Value = PID }); return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0]); }
/// <summary> /// 项目问题取得 /// Created:20170329(xuxb) /// </summary> /// <param name="PID"></param> /// <returns></returns> public DataTable GetProjectTrouble(string PID) { List <QueryField> qf = new List <QueryField>(); StringBuilder sql = new StringBuilder(); sql.Append(" select IFNULL(TroubleTotal,0) as TroubleTotal,IFNULL(TroubleHandle,0) as TroubleHandle,"); sql.Append(" IFNULL(TroubleLeave,0) as TroubleLeave,IFNULL(TroubleRest,0) as TroubleRest from ("); sql.Append(" select count(1) as TroubleTotal, "); sql.Append(" sum(case when HandleStatus = 3 then 1 else 0 end) as TroubleHandle, "); sql.Append(" sum( case when HandleStatus <> 3 then 1 else 0 end) as TroubleLeave, "); sql.Append(" sum( case when HandleStatus <> 3 and EndDate < date('now') then 1 else 0 end) as TroubleRest "); sql.Append(" from Trouble t "); sql.Append(" inner join PNode p on substr(p.ID,1,36) = t.NodeId and p.status = 1 "); sql.Append(" where p.PID=@PID and t.status=1)"); qf.Add(new QueryField() { Name = "PID", Type = QueryFieldType.String, Value = PID }); return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0]); }
/// <summary> /// 根据项目ID取得项目结点ID /// Created:2017.04.07(Xuxb) /// </summary> /// <param name="projectId"></param> /// <returns></returns> public string GetNodeIdByProjectId(string projectId) { List <QueryField> qf = new List <QueryField>(); StringBuilder sql = new StringBuilder(); sql.Append(" select ID from PNode where ParentID is null and PID = @PID "); qf.Add(new QueryField() { Name = "PID", Type = QueryFieldType.String, Value = projectId }); DataSet ds = NHHelper.ExecuteDataset(sql.ToString(), qf); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0].Rows[0]["ID"].ToString()); } else { return(string.Empty); } }
/// <summary> /// 项目近期工作和问题取得 /// Created:20170329(xuxb) /// updated:20170706(zhugj)近期不显示已完成的 /// </summary> /// <param name="PID"></param> /// <returns></returns> public DataTable GetProjectLastWorkList(string PID, int days) { List <QueryField> qf = new List <QueryField>(); StringBuilder sql = new StringBuilder(); sql.Append(" select r.ID,r.Name,r.Desc,'日常工作' as WorkType from Routine r "); sql.Append(" inner join PNode p on substr(p.ID,1,36) = r.NodeId and p.status = 1 "); sql.Append(" where r.StartDate < date('now','+' || @Days || ' day') and r.status = 1 and p.PID=@PID "); sql.Append(" and r.FinishStatus != 3"); sql.Append(" union all "); sql.Append(" select t.ID,t.Name,t.Desc,'项目问题' as WorkType from Trouble t "); sql.Append(" inner join PNode p on substr(p.ID,1,36) = t.NodeId and p.status = 1 "); sql.Append(" where t.StarteDate < date('now','+' || @Days || ' day') and t.status = 1 and p.PID=@PID"); sql.Append(" and t.HandleStatus != 3"); qf.Add(new QueryField() { Name = "PID", Type = QueryFieldType.String, Value = PID }); qf.Add(new QueryField() { Name = "Days", Type = QueryFieldType.Numeric, Value = days }); return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0]); }
/// <summary> /// 预警内容取得 /// Created:20170414(xuxb) /// </summary> /// <param name="PID"></param> /// <param name="openFlg"></param> /// <param name="projectUpdateDays"></param> /// <param name="publishUpdateDays"></param> /// <returns></returns> public DataTable GetProjectWarnning(string PID, bool[] openFlg, int projectUpdateDays, int publishUpdateDays) { List <QueryField> qf = new List <QueryField>(); StringBuilder sql = new StringBuilder(); //项目更新预警 sql.Append(" select '' Id,'项目更新预警' as WarnningName, @Days1 || '天内没有更新项目' as WarnningContent from Project "); sql.Append(" where IFNULL(ProjectLastUpdate,date('2017-01-01')) < date('now','-' || @Days1 || ' day') "); sql.Append(" and ID=@PID "); //项目发布更新预警 //TODO //项目预警 if (openFlg[0]) { sql.Append(" union all "); sql.Append(" select '' Id,'项目预警' as WarnningName, Tag || '的成本超出预算' as WarnningContent from Cost where IFNULL(Remaining,0) < 0 "); sql.Append(" and status = 1 and PID=@PID "); } //交付物预警条件 //期限内没有完成工作量 if (openFlg[1]) { sql.Append(" union all "); sql.Append(" select p.ID Id,'项目交付物预警' as WarnningName, d.Name || '在期限内没有完成工作量' as WarnningContent from DeliverablesJBXX d "); sql.Append(" inner join PNode p on substr(p.ID,1,36) = d.NodeID "); sql.Append(" left join NodeProgress n on d.NodeID = n.NodeID "); sql.Append(" where IFNULL(d.EndDate,date('2017-01-01')) < date('now') and IFNULL(n.PType,0) < 4 "); sql.Append(" and d.status = 1 and n.status = 1 and p.status = 1 and p.PID=@PID "); } //时间过去2/3时,工作量没有完成时提醒 if (openFlg[2]) { sql.Append(" union all "); sql.Append(" select p.ID Id,'项目交付物预警' as WarnningName, d.Name || '的时间过去2/3,但工作量没有完成' as WarnningContent from DeliverablesJBXX d "); sql.Append(" inner join PNode p on substr(p.ID,1,36) = d.NodeID "); sql.Append(" left join NodeProgress n on d.NodeID = n.NodeID "); sql.Append(" where (julianday(strftime('%Y-%m-%d','now'))-julianday(IFNULL(d.StarteDate,date('2017-01-01'))))/"); sql.Append("(julianday(IFNULL(d.EndDate,strftime('%Y-%m-%d','now')))-julianday(IFNULL(d.StarteDate,date('2017-01-01'))))"); sql.Append(" > 2/3 and IFNULL(n.PType,0) < 4 "); sql.Append(" and d.status = 1 and n.status = 1 and p.status = 1 and p.PID=@PID "); } //问题处理预警 if (openFlg[3]) { sql.Append(" union all "); sql.Append(" select d.ID Id,'项目问题预警' as WarnningName, '问题【' || d.Name || '】在期限内没有解决' as WarnningContent from Trouble d "); sql.Append(" inner join PNode p on substr(p.ID,1,36) = d.NodeID "); sql.Append(" where IFNULL(d.EndDate,date('2017-01-01')) < date('now') and d.HandleStatus <> 3"); sql.Append(" and d.status = 1 and p.status = 1 and p.PID=@PID "); } qf.Add(new QueryField() { Name = "PID", Type = QueryFieldType.String, Value = PID }); qf.Add(new QueryField() { Name = "Days1", Type = QueryFieldType.Numeric, Value = projectUpdateDays }); return(NHHelper.ExecuteDataset(sql.ToString(), qf).Tables[0]); }