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