/// <summary>
/// 收款列表
/// Created:20170327(ChengMengjia)
/// Updated:20170405(ChengMengjia) liuxx要求去除分页
/// </summary>
/// <param name="PID"></param>
/// <returns></returns>
public GridData GetSKList(int PageIndex, int PageSize, string PID)
{
//List<QueryField> qf = new List<QueryField>();
//StringBuilder sqlHead = new StringBuilder();
//sqlHead.Append(" select r.id,r.BatchNo,r.Ratio,r.FinishStatus,r.Amount,r.Condition,r.Remark,");
//sqlHead.Append(" strftime('%Y-%m-%d',r.InDate)InDate,d1.Name FinishStatusName ");
//StringBuilder sqlBody = new StringBuilder();
//sqlBody.Append(" from Receivables r ");
//sqlBody.Append(" left join DictItem d1 on d1.DictNo=" + (int)DictCategory.Receivables_FinshStatus + " and r.FinishStatus=d1.No ");
//sqlBody.Append(" where r.PID=@PID and r.status=1 order by r.updated desc,r.created asc");
//qf.Add(new QueryField() { Name = "PID", Type = QueryFieldType.String, Value = PID });
//GridData result = NHHelper.GetGridData(PageIndex, PageSize, sqlHead.ToString(), sqlBody.ToString(), qf);
//return result;
List <QueryField> qf = new List <QueryField>();
StringBuilder sql = new StringBuilder();
sql.Append(" select r.id,r.BatchNo,r.Ratio,r.FinishStatus,r.Amount,r.Condition,r.Remark,");
sql.Append(" strftime('%Y-%m-%d',r.InDate)InDate,d1.Name FinishStatusName ");
sql.Append(" from Receivables r ");
sql.Append(" left join DictItem d1 on d1.DictNo=" + (int)DictCategory.Receivables_FinshStatus + " and r.FinishStatus=d1.No ");
sql.Append(" where r.PID=@PID and r.status=1 order by r.CREATED");
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = PID
});
GridData result = new GridData();
result.data = NHHelper.ExecuteDataTable(sql.ToString(), qf);
return(result);
}
/// <summary>
/// 里程碑列表
/// Created:20170421(ChengMengjia)
/// </summary>
/// <param name="StartDate"></param>
/// <param name="EndDate"></param>
/// <param name="PID"></param>
/// <returns></returns>
public DataTable GetLCBList(string startDate, string endDate, string PID)
{
List <QueryField> qf = new List <QueryField>();
StringBuilder sql = new StringBuilder();
sql.Append(" select m.id,m.name,m.condition,m.remark,strftime('%Y-%m-%d',m.FinishDate)FinishDate,");
sql.Append(" m.FinishStatus,strftime('%Y-%m-%d',m.CREATED)CREATED,d.Name FinishStatusName ");
sql.Append(" from Milestones m left join DictItem d on d.DictNo=" + (int)DictCategory.Milestones_FinshStatus + " and m.FinishStatus=D.No ");
sql.Append(" where m.PID=@PID and m.status=1 ");
//开始日期
if (!string.IsNullOrEmpty(startDate))
{
sql.Append(" and date(m.FinishDate) >= date(@startDate)");
qf.Add(new QueryField()
{
Name = "startDate", Type = QueryFieldType.String, Value = DateTime.Parse(startDate).ToString("yyyy-MM-dd")
});
}
//结束日期
if (!string.IsNullOrEmpty(endDate))
{
sql.Append(" and (date(m.FinishDate) <= date(@endDate) or m.FinishDate is null )");
qf.Add(new QueryField()
{
Name = "endDate", Type = QueryFieldType.String, Value = DateTime.Parse(endDate).ToString("yyyy-MM-dd")
});
}
sql.Append(" order by m.updated desc,m.created asc");
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = PID
});
return(NHHelper.ExecuteDataTable(sql.ToString(), qf));
}
/// <summary>
/// 里程碑列表
/// Created:20170327(ChengMengjia)
/// Updated:20170405(ChengMengjia) liuxx要求去除分页
/// </summary>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="PID"></param>
/// <returns></returns>
public GridData GetLCBList(int PageIndex, int PageSize, string PID)
{
//List<QueryField> qf = new List<QueryField>();
//StringBuilder sqlHead = new StringBuilder();
//sqlHead.Append(" select m.id,m.name,m.condition,m.remark,strftime('%Y-%m-%d',m.FinishDate)FinishDate,");
//sqlHead.Append(" m.FinishStatus,strftime('%Y-%m-%d',m.CREATED)CREATED,d.Name FinishStatusName ");
//StringBuilder sqlBody = new StringBuilder();
//sqlBody.Append(" from Milestones m left join DictItem d on d.DictNo=" + (int)DictCategory.Milestones_FinshStatus + " and m.FinishStatus=D.No ");
//sqlBody.Append(" where m.PID=@PID and m.status=1 order by m.updated desc,m.created asc");
//qf.Add(new QueryField() { Name = "PID", Type = QueryFieldType.String, Value = PID });
//GridData result = NHHelper.GetGridData(PageIndex, PageSize, sqlHead.ToString(), sqlBody.ToString(), qf);
//return result;
List <QueryField> qf = new List <QueryField>();
StringBuilder sql = new StringBuilder();
sql.Append(" select m.id,m.name,m.condition,m.remark,strftime('%Y-%m-%d',m.FinishDate)FinishDate,");
sql.Append(" m.FinishStatus,strftime('%Y-%m-%d',m.CREATED)CREATED,d.Name FinishStatusName ");
sql.Append(" from Milestones m left join DictItem d on d.DictNo=" + (int)DictCategory.Milestones_FinshStatus + " and m.FinishStatus=D.No ");
sql.Append(" where m.PID=@PID and m.status=1 ");
sql.Append(" order by m.updated desc,m.created asc");
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = PID
});
GridData result = new GridData();
result.data = NHHelper.ExecuteDataTable(sql.ToString(), qf);
return(result);
}
/// <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:2017.04.21(ChengMengJia)
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="PID"></param>
/// <returns></returns>
public DataTable GetJFWList(string startDate, string endDate, string PID)
{
List <QueryField> qf = new List <QueryField>();
StringBuilder sql = new StringBuilder();
sql.Append(" select j.id,j.name,j.Desc,parent.Name NodeName,strftime('%Y-%m-%d',j.StarteDate)StarteDate,strftime('%Y-%m-%d',j.EndDate)EndDate,");
sql.Append(" j.Workload,j.Manager ");
sql.Append(" from DeliverablesJBXX j inner join PNode n on j.NodeID=substr(n.ID,1,36) and n.status=1");
sql.Append(" left join PNode parent on n.ParentID=substr(parent.ID,1,36) and parent.status=1");
sql.Append(" where n.PID=@PID and j.status=1 ");
//开始日期
if (!string.IsNullOrEmpty(startDate))
{
sql.Append(" and date(j.StarteDate) >= date(@startDate)");
qf.Add(new QueryField()
{
Name = "startDate", Type = QueryFieldType.String, Value = DateTime.Parse(startDate).ToString("yyyy-MM-dd")
});
}
//结束日期
if (!string.IsNullOrEmpty(endDate))
{
sql.Append(" and (date(j.EndDate) <= date(@endDate) or j.EndDate is null )");
qf.Add(new QueryField()
{
Name = "endDate", Type = QueryFieldType.String, Value = DateTime.Parse(endDate).ToString("yyyy-MM-dd")
});
}
sql.Append(" order by j.updated desc,j.created asc");
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = PID
});
return(NHHelper.ExecuteDataTable(sql.ToString(), qf));
}
/// <summary>
/// 获取分包合同报表
/// 2015/05/16(zhuguanjun)
/// </summary>
/// <param name="pids"></param>
/// <param name="dic"></param>
/// <returns></returns>
public DataTable GetSubcontract(List <string> pids, Dictionary <string, string> dic)
{
#region 查询条件
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
string PIDList = "";
if (pids != null && pids.Count() > 0)
{
foreach (var item in pids)
{
PIDList += "'" + item + "',";
}
PIDList = PIDList.TrimEnd(new char[] { ',' });
}
//string sqlstr1 = "";//分包字段
//string sqlstr2 = "";//项目字段
//if (dic!=null&&dic.Count>0)
//{
// foreach (var item in dic)
// {
// sqlstr1 += "s." + item.Key + ",";
// sqlstr2 += "null as " + item.Key + ",";
// }
// sqlstr1 =sqlstr1.TrimEnd(',');
// sqlstr2 = sqlstr2.TrimEnd(',');
//}
#endregion
StringBuilder sql = new StringBuilder();
//最外层
sql.Append(" select * from (");
//查询分包合同
sql.Append(" select s.ID as KeyFieldName,s.PID as ParentFieldName,s.B_Name,d.Name as SupplierName,");
sql.Append(" s.B_No,s.A_No,s.A_Name,s.CompanyName,s.Amount,s.SignDate,s.Desc");
sql.AppendFormat(@" from Subcontract s left join Project p on s.PID = p.ID
left join Supplier d on substr(d.ID,1,36) = s.CompanyName and d.Status=@Status
where s.Status = @Status and p.ID is not null");
//交合
sql.Append(" union");
//查询项目
sql.Append(" select distinct(p.ID) as KeyFieldName,p.ID as ParentFieldName,p.Name as B_Name,null as SupplierName");
sql.Append(" ,null as B_No,null as A_No,null as A_Name,null as CompanyName,null as Amount,null as SignDate,null as Desc");
sql.Append(@" from Project p left join Subcontract c on c.PID = p.ID
group by p.ID");
//最外层
sql.Append(" )");
sql.Append(" where ParentFieldName in (" + PIDList + ")");
//排序
sql.Append(" order by ParentFieldName, B_Name");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt);
}
/// <summary>
/// 获取供应商无版本号的ID和有版本号的Name(用于下拉框)
/// </summary>
/// <param name="qlist"></param>
/// <returns></returns>
public DataTable GetSupplierList(List <QueryField> qlist)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select substr(s.ID, 1,36) as ID,s.Name from Supplier s");
sql.Append(" where s.PID=@PID and s.status=@Status order by s.Name ");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt);
}
/// <summary>
/// 根据版本ID获得变更实体
/// 2017/04/18(zhuguanjun)
/// </summary>
/// <param name="qlist"></param>
/// <returns></returns>
public Change GetChange(List <QueryField> qlist)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select * from Change c");
sql.Append(" where Status=@status and substr(c.Id,1,37)||'1'=@CID");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt == null ? new Change() : JsonHelper.TableToEntity <Change>(dt));
}
/// <summary>
/// 项目问题查询
/// Created:2017.04.06(xuxb)
/// Updated:20170607(ChengMengjia)增加状态判断
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="key"></param>
/// <returns></returns>
public DataTable GetTroubleList(string PID, string startDate, string endDate, string key)
{
List <QueryField> qf = new List <QueryField>();
StringBuilder sql = new StringBuilder();
sql.Append(" select r.ID,r.Name,r.Desc,r.HandleResult,strftime('%Y-%m-%d',r.StarteDate) as StartDate, ");
sql.Append(" strftime('%Y-%m-%d',r.EndDate) as EndDate,d.Name as HandleStatus,d1.Name as Level, ");
//完成状态判断 参加PNode的Entity中FinishStatus说明
sql.Append(" case when r.HandleStatus=3 then 1 ");
sql.Append(" when r.EndDate<date('now') and (r.HandleStatus is null or r.HandleStatus<>3) then 3 ");
sql.Append(" when r.StarteDate>=date('now','+1 day') and (r.HandleStatus is null or r.HandleStatus<>3) then 0 else 2 end FinishType ");
sql.Append(" from Trouble r inner join PNode p on r.NodeID = substr(p.ID,1,36) and p.Status = 1");
sql.Append(" left join DictItem d on r.HandleStatus = d.No and d.DictNo = " + (int)CommonDLL.DictCategory.TroubleHandleStatus);
sql.Append(" left join DictItem d1 on r.Level = d1.No and d1.DictNo = " + (int)CommonDLL.DictCategory.TroubleLevel);
sql.Append(" where r.status = 1 and p.PID = @PID ");
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = PID
});
//开始日期
if (!string.IsNullOrEmpty(startDate))
{
sql.Append(" and date(r.StarteDate) >= date(@startDate)");
qf.Add(new QueryField()
{
Name = "startDate", Type = QueryFieldType.String, Value = DateTime.Parse(startDate).ToString("yyyy-MM-dd")
});
}
//结束日期
if (!string.IsNullOrEmpty(endDate))
{
sql.Append(" and date(r.EndDate) <= date(@endDate) )");
qf.Add(new QueryField()
{
Name = "endDate", Type = QueryFieldType.String, Value = DateTime.Parse(endDate).ToString("yyyy-MM-dd")
});
}
//关键字
if (!string.IsNullOrEmpty(key))
{
sql.Append(" and (r.Name like '%' || @key || '%' or r.Desc like '%' || @key || '%' or r.DealResult like '%' || @key || '%') ");
qf.Add(new QueryField()
{
Name = "key", Type = QueryFieldType.String, Value = key
});
}
sql.Append(" order by r.StarteDate Desc ");
return(NHHelper.ExecuteDataTable(sql.ToString(), qf));
}
/// <summary>
/// 获取干系人列表(无版本号的ID+有版本号的NAME)
/// </summary>
/// <param name="qf"></param>
/// <returns></returns>
public DataTable GetDataTable(List <QueryField> qf)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select s1.ID,s2.Name||'('||s2.CompanyName||')' as Name,s2.CompanyName || '-' || s2.Name as showName,s2.IsPublic from stakeholders s1, stakeholders s2");
sql.Append(" where substr(s1.ID, 38) = '1' and substr(s1.ID, 1, 37) = substr(s2.ID, 1, 37)");
sql.Append(" and s2.PID=@PID and s2.status=@Status order by s2.updated desc,s2.created desc");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qf);
return(dt);
}
/// <summary>
/// 根据版本ID获得变更附件列表
/// 2017/04/18(zhuguanjun)
/// </summary>
/// <param name="qlist"></param>
/// <returns></returns>
public DataTable GetFilesList(List <QueryField> qlist)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select * from ChangeFiles c");
sql.Append(" where c.Status=@status and c.ChangeID=@CID");
sql.Append(" order by c.CREATED");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt == null ? new DataTable() : dt);
}
/// <summary>
/// 获取(无版本号的ID+有版本号的NAME)
/// 2017/04/17(zhuguanjun)
/// </summary>
/// <param name="qf"></param>
/// <returns></returns>
public DataTable GetDataTable(List <QueryField> qf)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select c1.ID,c2.Name from Change c1, Change c2");
sql.Append(" where substr(c1.ID, 38) = '1' and substr(c1.ID, 1, 37) = substr(c2.ID, 1, 37)");
sql.Append(" and c2.PID=@PID and c2.status=@Status and c2.Type=@Type order by c2.created");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qf);
return(dt);
}
/// <summary>
/// 获取分包集合(无版本号的ID和有版本号的Name)
/// 2017/04/12(zhuguanjun)
/// </summary>
/// <param name="qlist"></param>
/// <returns></returns>
public DataTable GetContractList(List <QueryField> qf)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select c1.ID,c2.A_Name||'('||c2.A_No||')' as A,c2.B_Name as B from SubContract c1, SubContract c2");
sql.Append(" where substr(c1.ID, 38) = '1' and substr(c1.ID, 1, 37) = substr(c2.ID, 1, 37)");
sql.Append(" and c2.PID=@PID and c2.status=@Status order by c2.A_No ");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qf);
return(dt);
}
/// <summary>
/// 获取分包合同信息、附件信息、里程碑信息、付款信息
/// 2017/04/13
/// </summary>
/// <param name="SubID">分包合同主表ID(版本id)</param>
/// <param name="subContract">返回合同信息</param>
/// <param name="files">返回附件集合</param>
/// <param name="LCB">返回里程碑集合</param>
/// <param name="SKXX">返回付款信息集合</param>
public void GetSubContractAll(string SubID, out SubContract subContract, out DataTable files, out DataTable LCB, out DataTable SKXX)
{
StringBuilder sqlSub = new StringBuilder();
StringBuilder sqlFile = new StringBuilder();
StringBuilder sqlLCB = new StringBuilder();
StringBuilder sqlSKXX = new StringBuilder();
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
qlist.Add(new QueryField()
{
Name = "SubID", Type = QueryFieldType.String, Value = SubID
});
#region 分包合同
sqlSub.Append(" select * from SubContract s");
//sqlSub.Append(" where Status=@status and substr(s.Id,1,37)||'1'=@SubID");
sqlSub.Append(" where Status=@status and substr(s.Id,1,36)=@SubID");
sqlSub.Append(" order by s.CREATED");
DataTable Sub = NHHelper.ExecuteDataTable(sqlSub.ToString(), qlist);
subContract = Sub == null ? new SubContract() : JsonHelper.TableToEntity <SubContract>(Sub);
#endregion
#region 附件
sqlFile.Append(" select * from SubContractFiles");
sqlFile.Append(" where SubID=@SubID and Status=@Status");
sqlFile.Append(" order by s.CREATED");
files = NHHelper.ExecuteDataTable(sqlFile.ToString(), qlist);
#endregion
#region 里程碑
sqlLCB.Append(" select s.*,d1.Name as FinishStatusName from SubContractLCB s");
sqlLCB.Append(" left join DictItem d1 on s.FinishStatus = d1.No and d1.DictNo=" + (int)DictCategory.Milestones_FinshStatus);
sqlLCB.Append(" where s.SubID=@SubID and s.Status=@Status");
sqlLCB.Append(" order by s.CREATED");
LCB = NHHelper.ExecuteDataTable(sqlLCB.ToString(), qlist);
#endregion
#region 收款信息
//sqlSKXX.Append(" select s.*,d1.Name as FinishStatusName,d2.Name as BatchNoName from SubContractSKXX s");
sqlSKXX.Append(" select s.*,d1.Name as FinishStatusName from SubContractSKXX s");
sqlSKXX.Append(" left join DictItem d1 on s.FinishStatus = d1.No and d1.DictNo=" + (int)DictCategory.Receivables_FinshStatus);
//sqlSKXX.Append(" left join DictItem d2 on s.BatchNo = d2.No and d2.DictNo=" + (int)DictCategory.Receivables_BatchNo);
sqlSKXX.Append(" where SubID=@SubID and Status=@Status");
sqlSKXX.Append(" order by s.CREATED");
SKXX = NHHelper.ExecuteDataTable(sqlSKXX.ToString(), qlist);
#endregion
}
/// <summary>
/// 项目问题查询
/// Created:2017.04.21(ChengMengjia)
/// </summary>
/// <param name="PID"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="Status"></param>
/// <returns></returns>
public DataTable GetTroubleList(string PID, string startDate, string endDate, int?Status)
{
List <QueryField> qf = new List <QueryField>();
StringBuilder sql = new StringBuilder();
sql.Append(" select r.ID,r.Name,p.Name NodeName,r.Desc,r.HandleResult,s.Name HandleMan,strftime('%Y-%m-%d',r.HandleDate) as HandleDate, ");
sql.Append(" strftime('%Y-%m-%d',r.StarteDate) as StartDate,strftime('%Y-%m-%d',r.EndDate) as EndDate,d.Name as HandleStatus,d1.Name as Level from Trouble r ");
sql.Append(" inner join PNode p on r.NodeID = substr(p.ID,1,36) and p.Status = 1");
sql.Append(" left join Stakeholders s on r.HandleMan = substr(s.ID,1,36) ");
sql.Append(" left join DictItem d on r.HandleStatus = d.No and d.DictNo = " + (int)CommonDLL.DictCategory.TroubleHandleStatus);
sql.Append(" left join DictItem d1 on r.Level = d1.No and d1.DictNo = " + (int)CommonDLL.DictCategory.TroubleLevel);
sql.Append(" where r.status = 1 and p.PID = @PID ");
qf.Add(new QueryField()
{
Name = "PID", Type = QueryFieldType.String, Value = PID
});
//开始日期
if (!string.IsNullOrEmpty(startDate))
{
sql.Append(" and date(r.HandleDate) >= date(@startDate)");
qf.Add(new QueryField()
{
Name = "startDate", Type = QueryFieldType.String, Value = DateTime.Parse(startDate).ToString("yyyy-MM-dd")
});
}
//结束日期
if (!string.IsNullOrEmpty(endDate))
{
sql.Append(" and date(r.HandleDate) <= date(@endDate) )");
qf.Add(new QueryField()
{
Name = "endDate", Type = QueryFieldType.String, Value = DateTime.Parse(endDate).ToString("yyyy-MM-dd")
});
}
//解决状态
if (Status != null)
{
sql.Append(" and r.HandleStatus =@status ");
qf.Add(new QueryField()
{
Name = "status", Type = QueryFieldType.Numeric, Value = Status
});
}
sql.Append(" order by r.StarteDate Desc ");
return(NHHelper.ExecuteDataTable(sql.ToString(), qf));
}
public DataTable GetSupplierList(List <QueryField> qlist)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select * from Supplier");
sql.Append(" where Status=@Status and PID=@PID");
sql.Append(" order by CREATED");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
if (dt != null)
{
return(dt);
}
return(new DataTable());
}
/// <summary>
/// 获取wbs代码列表
/// 2017/05/04(zhuguanjun)
/// </summary>
/// <returns></returns>
public DataTable GetWBSCodeList(List <QueryField> qlist)
{
StringBuilder sql = new StringBuilder();
sql.Append(" select w.*,d2.Name as LengthName, d3.Name as BreakName,");
sql.Append(" case w.Orderr when " + (int)WBSCodeOrder.Upper + " then '" + EnumsHelper.GetDescription(WBSCodeOrder.Upper) + "'");
sql.Append(" when " + (int)WBSCodeOrder.Lower + " then '" + EnumsHelper.GetDescription(WBSCodeOrder.Lower) + "'");
sql.Append(" when " + (int)WBSCodeOrder.Number + " then '" + EnumsHelper.GetDescription(WBSCodeOrder.Number) + "' end as OrderName");
sql.Append(" from WBSCode w");
sql.Append(" left join DictItem d1 on d1.No = w.Orderr and d1.DictNo = " + (int)DictCategory.WBSCodeOrder);
sql.Append(" left join DictItem d2 on d2.No = w.Length and d2.DictNo = " + (int)DictCategory.WBSCodeLength);
sql.Append(" left join DictItem d3 on d3.No = w.Breakk and d3.DictNo = " + (int)DictCategory.WBSCodeBreak);
sql.Append(" where PID=@PID ");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt);
}
/// <summary>
/// 获取供应商报表
/// 2017/05/16
/// </summary>
/// <param name="pids"></param>
/// <param name="dic"></param>
/// <returns></returns>
public DataTable GetSupplier(List <string> pids, Dictionary <string, string> dic)
{
#region 查询条件
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
string PIDList = "";
if (pids != null && pids.Count() > 0)
{
foreach (var item in pids)
{
PIDList += "'" + item + "',";
}
PIDList = PIDList.TrimEnd(new char[] { ',' });
}
#endregion
StringBuilder sql = new StringBuilder();
//最外层
sql.Append(" select * from (");
//查询分包合同
sql.Append(" select s.ID as KeyFieldName,s.PID as ParentFieldName,s.Name,s.LegalMan,");
sql.Append(" s.Manager,s.Tel,s.Addr");
sql.AppendFormat(@" from Supplier s left join Project p on s.PID = p.ID
where s.Status = @Status and p.ID is not null");
//交合
sql.Append(" union");
//查询项目
sql.Append(" select distinct(p.ID) as KeyFieldName,p.ID as ParentFieldName,p.Name,null as LegalMan");
sql.Append(" ,null as Manager,null as Tel,null as Addr");
sql.Append(@" from Project p left join Supplier c on c.PID = p.ID
group by p.ID");
//最外层
sql.Append(" )");
sql.Append(" where ParentFieldName in (" + PIDList + ")");
//排序
sql.Append(" order by ParentFieldName,Name");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt);
}
/// <summary>
/// 获取里程碑集合
/// 2017/04/14(zhuguanjun)
/// </summary>
/// <param name="SubID"></param>
/// <returns></returns>
public DataTable GetLCBList(string SubID)
{
StringBuilder sqlLCB = new StringBuilder();
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
qlist.Add(new QueryField()
{
Name = "SubID", Type = QueryFieldType.String, Value = SubID
});
sqlLCB.Append(" select s.*,d1.Name as FinishStatusName from SubContractLCB s");
sqlLCB.Append(" left join DictItem d1 on s.FinishStatus = d1.No and d1.DictNo=" + (int)DictCategory.Milestones_FinshStatus);
sqlLCB.Append(" where s.SubID=@SubID and s.Status=@Status");
return(NHHelper.ExecuteDataTable(sqlLCB.ToString(), qlist));
}
/// <summary>
/// 获取收款报表信息
/// 2017/04/28(zhuguanjun)
/// </summary>
/// <param name="pids"></param>
/// <returns></returns>
public DataTable GetReceivables(List <string> pids)
{
#region 查询条件
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
string PIDList = "";
if (pids != null && pids.Count() > 0)
{
foreach (var item in pids)
{
PIDList += "'" + item + "',";
}
PIDList = PIDList.TrimEnd(new char[] { ',' });
}
#endregion
StringBuilder sql = new StringBuilder();
//最外层
sql.Append(" select * from (");
//查询收入
sql.AppendFormat(@" select e.ID as KeyFieldName,e.PID as ParentFieldName,e.BatchNo,e.Ratio,d.Name as FinishStatus,e.Amount,e.Condition,e.Remark,e.Indate from Receivables e
left join Project p on e.PID = P.ID
left join DictItem d on d.No = e.FinishStatus and d.DictNo ={0}
where e.Status = @Status and p.ID is not null", (int)DictCategory.Receivables_FinshStatus);
//交合
sql.Append(" union");
//查询项目
sql.Append(@" select distinct(p1.ID) as KeyFieldName,p1.ID as ParentFieldName,Name as BatchNo,null as Ratio,null as FinishStatus,null as Amount,null as Condition,null as Remark,null as Indate from Project p1
left join Receivables e1 on e1.PID = P1.ID
group by p1.ID");
//最外层
sql.Append(" )");
sql.Append(" where ParentFieldName in (" + PIDList + ")");
sql.Append(" order by ParentFieldName,BatchNo");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt);
}
/// <summary>
/// 获取收款信息集合
/// 2017/04/17(zhuguanjun)
/// </summary>
/// <param name="SubID"></param>
/// <returns></returns>
public DataTable GetSKXXList(string SubID)
{
StringBuilder sqlSKXX = new StringBuilder();
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
qlist.Add(new QueryField()
{
Name = "SubID", Type = QueryFieldType.String, Value = SubID
});
//sqlSKXX.Append(" select s.*,d1.Name as FinishStatusName,d2.Name as BatchNoName from SubContractSKXX s");
sqlSKXX.Append(" select s.*,d1.Name as FinishStatusName from SubContractSKXX s");
sqlSKXX.Append(" left join DictItem d1 on s.FinishStatus = d1.No and d1.DictNo=" + (int)DictCategory.Receivables_FinshStatus);
//sqlSKXX.Append(" left join DictItem d2 on s.BatchNo = d2.No and d2.DictNo=" + (int)DictCategory.Receivables_BatchNo);
sqlSKXX.Append(" where SubID=@SubID and Status=@Status");
return(NHHelper.ExecuteDataTable(sqlSKXX.ToString(), qlist));
}
/// <summary>
/// 获取成本分配
/// 2017/04/28(zhuguanjun)
/// </summary>
/// <param name="pids"></param>
/// <returns></returns>
public DataTable GetCost(List <string> pids)
{
#region 查询条件
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField()
{
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
string PIDList = "";
if (pids != null && pids.Count() > 0)
{
foreach (var item in pids)
{
PIDList += "'" + item + "',";
}
PIDList = PIDList.TrimEnd(new char[] { ',' });
}
#endregion
StringBuilder sql = new StringBuilder();
//最外层
sql.Append(" select * from (");
//查询收入
sql.AppendFormat(@" select e.ID as KeyFieldName,e.PID as ParentFieldName,e.Tag,e.Explanation,e.Total,e.Used,e.Transit,e.Remaining,e.Remark from Cost e
left join Project p on e.PID = P.ID
where e.Status = @Status and p.ID is not null");
//交合
sql.Append(" union");
//查询项目
sql.Append(@" select distinct(p1.ID) as KeyFieldName,p1.ID as ParentFieldName,Name as Tag,null as Explanation,null as Total,null as Used,null as Transit,null as Remaining,null as Remark from Project p1
left join Cost e1 on e1.PID = P1.ID
group by p1.ID");
//最外层
sql.Append(" )");
sql.Append(" where ParentFieldName in (" + PIDList + ")");
sql.Append(" order by ParentFieldName,Tag");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt);
}
public DataTable GetDefficutyDegree(string PID, DateTime Startedate, DateTime Enddate, int FinishStatus)
{
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField {
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
qlist.Add(new QueryField {
Name = "PID", Type = QueryFieldType.String, Value = PID
});
qlist.Add(new QueryField {
Name = "StarteDate", Type = QueryFieldType.DateTime, Value = Startedate
});
qlist.Add(new QueryField {
Name = "EndDate", Type = QueryFieldType.DateTime, Value = Enddate
});
qlist.Add(new QueryField {
Name = "FinishStatus", Type = QueryFieldType.Numeric, Value = FinishStatus
});
StringBuilder sql = new StringBuilder();
//sql.Append(@"
//select * from (
///*内容*/
//select * from (
///*日常*/
//select * from (select
//(select count(*)+1 from routinework rin where rin.created<rw.created) as RowNo,
//'日常' as source, r.name as name,r.Desc ,date(r.startdate) as startedate,date(r.enddate) as enddate,'1' as type,s.name as allname,
//round((r.enddate-r.startdate+1)*1.0/rw.actualworkload,1) as efficiency,r.finishstatus from routinework rw
//inner join stakeholders s on substr(s.id,1,36) = rw.Manager and r.status = @status
//inner join routine r on rw.routineid = substr(r.id,1,36) and s.status =@status
//where 1=1 and s.pid =@pid
//order by rw.manager,rw.created)
//union
///*交付物*/
//select * from(select
//(select (select count(*) from routinework )+(select count(*) from troublework )
//+ count(*)+1 from deliverableswork d where d.created<dw.created)as rowno,
//'交付物' as source,d.name as name,d.Desc,date(d.startedate) as startdate,date(d.enddate) as enddate,
//'3' as type ,s.name as allname,
//round((d.enddate-d.startedate+1)*1.0/dw.actualworkload,1) as efficiency,
//(case when pg.ptype = 5 then 3 else 2 end) as finishstatus from Deliverableswork dw
//inner join stakeholders s on substr(s.id,1,36) = dw.Manager and s.status =@status
//inner join DeliverablesJBXX d on dw.JBXXid = substr(d.id,1,36) and d.status = @status
//inner join PNode p on substr(p.id,1,36) = d.nodeid and p.status = @status
//inner join NodeProgress pg on pg.nodeid = substr(p.id,1,36) and pg.status = @status
//where 1=1 and s.pid =@pid
//)
//union
///*问题*/
//select * from(select
//(select (select count(*) from routinework )+ count(*)+1 from Troublework t where t.created<tw.created)as rowno,
//'问题' as source,t.name as name ,t.Desc,date(t.startedate) as startdate,date(t.enddate) as enddate,'2' as type ,s.name as allname,
//round((t.enddate-t.startedate+1)*1.0/tw.actualworkload,1) as efficiency,t.handlestatus as finishstatus from Troublework tw
//inner join stakeholders s on substr(s.id,1,36) = tw.Manager and s.status =@status
//inner join Trouble t on tw.troubleid = substr(t.id,1,36) and t.status = @status
//where 1=1 and s.pid =@pid
//order by tw.manager,tw.created)
//) where " +
// (Startedate != DateTime.MinValue ? "date(startedate)>=date(@StarteDate) " : "1=1 ") +
//(Enddate != DateTime.MinValue ? "and date(enddate)<=date(@EndDate) " : "and 1=1 ") +
//(FinishStatus != 0 ? (FinishStatus == 3 ? "and finishstatus=3 " : "and finishstatus!=3 ") : "and 1=1 ") +
//@"
//) order by type,rowno
//");
sql.Append(@"
with cte as (select
'日常' as source, t.name as name,t.Desc ,date(t.startdate) as startdate,date(t.enddate) as enddate,'1' as type,workload,
(select sum(tw.actualworkload) from routinework tw where substr(t.id,1,36)=tw.routineid) as actualworkload,
round(((julianday(enddate)-julianday(startdate)+1)*1.0/(select sum(tw.actualworkload) from routinework tw where substr(t.id,1,36)=tw.routineid)),1) as degree
from routine t
inner join pnode pn on t.nodeid=substr(pn.id,1,36)
inner join project p on pn.pid=substr(p.id,1,36) and p.id=@PID
where t.status=1
union
select
'问题' as source, t.name as name,t.Desc ,date(t.startedate) as startdate,date(t.enddate) as enddate,'2' as type,workload,
(select sum(tw.actualworkload) from troublework tw where substr(t.id,1,36)=tw.troubleid) as actualworkload ,
round(((julianday(enddate)-julianday(startedate)+1)*1.0/(select sum(tw.actualworkload) from troublework tw where substr(t.id,1,36)=tw.troubleid)*1.0),1) as degree
from trouble t
inner join pnode pn on t.nodeid=substr(pn.id,1,36)
inner join project p on pn.pid=substr(p.id,1,36) and p.id=@PID
where t.status=1
union
select
'交付物' as source, t.name as name,t.Desc ,date(t.startedate) as startdate,date(t.enddate) as enddate,'3' as type,workload,
(select sum(tw.actualworkload) from deliverableswork tw where substr(t.id,1,36)=tw.jbxxid) as actualworkload ,
round(((julianday(enddate)-julianday(startedate)+1)*1.0/(select sum(tw.actualworkload) from deliverableswork tw where substr(t.id,1,36)=tw.jbxxid)),1) as degree
from deliverablesjbxx t
inner join pnode pn on t.nodeid=substr(pn.id,1,36)
inner join project p on pn.pid=substr(p.id,1,36) and p.id=@PID
where t.status=1)
select * from(select null as source, null as name,null as Desc ,null as startdate,null as enddate,'4' as type,null as workload,
'平均系数' as actualworkload ,
round(sum(degree)/count(1),1) as degree from cte
union
select * from cte) order by type
");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
if (dt != null && dt.Rows.Count > 0)
{
dt.Rows[dt.Rows.Count - 1]["RowNo"] = "";
}
return(dt);
}
/// <summary>
/// 获取交付物信息(节点树形式)
/// 2017/04/20(zhuguanjun)
/// </summary>
/// <param name="qlist"></param>
/// <returns></returns>
public DataTable GetPlan(DateTime StarteDate, DateTime EndDate, int PType, string Manager, string PID)
{
List <QueryField> qlist = new List <QueryField>();
qlist.Add(new QueryField {
Name = "Status", Type = QueryFieldType.Numeric, Value = 1
});
qlist.Add(new QueryField {
Name = "PID", Type = QueryFieldType.String, Value = PID
});
qlist.Add(new QueryField()
{
Name = "Manager", Type = QueryFieldType.String, Value = Manager
});
qlist.Add(new QueryField()
{
Name = "StarteDate", Type = QueryFieldType.DateTime, Value = StarteDate
});
qlist.Add(new QueryField()
{
Name = "EndDate", Type = QueryFieldType.DateTime, Value = EndDate
});
qlist.Add(new QueryField()
{
Name = "PType", Type = QueryFieldType.Numeric, Value = PType
});
#region 2017/05/10 verson
//StringBuilder sql = new StringBuilder();
//sql.Append(" select d.ID,d.Name,(cast(d.Workload as varchar) || '(天)') as Workload,date(d.StarteDate) as StarteDate,date(d.EndDate) as EndDate,d.Manager");
//sql.Append(" ,d1.Name as Progress,substr(p.WBSNo,1,length(p.WBSNo)-1) as WBSNo from DeliverablesJBXX d");
//sql.Append(" left join NodeProgress n on n.Status=@Status and d.NodeID=n.NodeID");
//sql.Append(" left join DictItem d1 on n.PType = d1.No and d1.DictNo=" + (int)DictCategory.PlanFinishStatus);
//sql.Append(" left join PNode p on substr(p.ID, 1, 36) = d.NodeID and p.Status=@Status");
//sql.Append(" where d.Status=@Status");
//if (StarteDate != DateTime.MinValue)
//{
// qlist.Add(new QueryField() { Name = "StarteDate", Type = QueryFieldType.DateTime, Value = StarteDate });
// sql.Append(" and date(d.StarteDate)>=date(@StarteDate)");
//}
//if (EndDate != DateTime.MinValue)
//{
// qlist.Add(new QueryField() { Name = "EndDate", Type = QueryFieldType.DateTime, Value = EndDate });
// sql.Append(" and date(d.EndDate)<=date(@EndDate)");
//}
//if (PType > 0)
//{
// qlist.Add(new QueryField() { Name = "PType", Type = QueryFieldType.Numeric, Value = PType });
// sql.Append(" and n.PType=@PType");
//}
//if (!string.IsNullOrEmpty(Manager))
//{
// qlist.Add(new QueryField() { Name = "Manager", Type = QueryFieldType.String, Value = Manager });
// sql.Append(" and d.Manager=@Manager");
//}
//sql.Append(" order by d.CREATED");
//StringBuilder sql = new StringBuilder();
#endregion
#region 2017/05/27 verson(nodes have no minstartdate and maxenddate)
//StringBuilder sql = new StringBuilder();
////最外层
//sql.Append(" select * from (");
////查询交付物
//sql.Append(" select p.ID as KeyFieldName,p.ParentID as ParentFieldName,p.PID,d.Name,(cast(d.Workload as varchar) || '(天)') as Workload,date(d.StarteDate) as StarteDate,date(d.EndDate) as EndDate,d.Manager");
//sql.Append(" ,d1.Name as Progress,substr(p.WBSNo,1,length(p.WBSNo)-1) as WBSNo, p.CREATED from DeliverablesJBXX d");
//sql.Append(" left join NodeProgress n on n.Status=@Status and d.NodeID=n.NodeID");
//sql.Append(" left join DictItem d1 on n.PType = d1.No and d1.DictNo=" + (int)DictCategory.PlanFinishStatus);
//sql.Append(" left join PNode p on substr(p.ID, 1, 36) = d.NodeID");
//sql.Append(" where d.Status=@Status and p.Status=@Status and p.PType=1");
//if (StarteDate != DateTime.MinValue)
//{
// qlist.Add(new QueryField() { Name = "StarteDate", Type = QueryFieldType.DateTime, Value = StarteDate });
// sql.Append(" and date(d.StarteDate)>=date(@StarteDate)");
//}
//if (EndDate != DateTime.MinValue)
//{
// qlist.Add(new QueryField() { Name = "EndDate", Type = QueryFieldType.DateTime, Value = EndDate });
// sql.Append(" and date(d.EndDate)<=date(@EndDate)");
//}
//if (PType > 0)
//{
// qlist.Add(new QueryField() { Name = "PType", Type = QueryFieldType.Numeric, Value = PType });
// sql.Append(" and n.PType=@PType");
//}
//if (!string.IsNullOrEmpty(Manager))
//{
// qlist.Add(new QueryField() { Name = "Manager", Type = QueryFieldType.String, Value = Manager });
// sql.Append(" and d.Manager=@Manager");
//}
////sql.Append(" order by d.CREATED");
////交合
//sql.Append(" union");
////查询节点
//sql.Append(@" select distinct substr(p1.ID,1,36) as KeyFieldName,p1.ParentID as ParentFieldName,p1.PID,p1.Name,null as Workload,null as StartDate,null as EndDate,null as Manager,null as Progress, substr(WBSNo,1,length(WBSNo)-1) as WBSNo,p1.CREATED from PNode p1
// left join DeliverablesJBXX d1 on d1.NodeID = P1.ID
// where p1.Status = 1 and (p1.PType<>1 or p1.PType is null)");
////最外层
//sql.Append(" )");
//sql.Append(" where PID=@PID"); //如果显示所有项目则注释掉此行
//sql.Append(" order by PID,CREATED");
//DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
//return dt;
#endregion
StringBuilder sql = new StringBuilder();
//定义CTE(用于递归)
sql.Append(@" With CT
AS
(
select M.*, N.startedate, N.EndDate, N.name As N_name from pnode M left join deliverablesjbxx N on substr(M.id, 1, 36) = N.nodeid ");
if (PType > 0)
{
sql.Append(" inner join nodeprogress ng on ng.nodeid = substr(m.id,1,36) and ng.ptype=@PType");
}
if (!string.IsNullOrEmpty(Manager))
{
sql.Append(" inner join deliverableswork dw on dw.jbxxid = substr(N.id,1,36) and dw.manager=@Manager");
}
sql.Append(" where m.status = @Status and n.status = @Status ");
if (StarteDate != DateTime.MinValue)
{
sql.Append(" and date(N.StarteDate)>=date(@StarteDate)");
}
if (EndDate != DateTime.MinValue)
{
sql.Append(" and date(N.EndDate)<=date(@EndDate)");
}
sql.Append(@" union all
select M1.*, CT.startedate, ct.enddate, CT.N_name from CT inner join pnode M1
on CT.parentid = substr(M1.id, 1, 36)
where m1.status = @Status");
sql.Append(")");
//最外层
sql.Append(" select * from (");
//查询交付物
sql.Append(" select p.ID as KeyFieldName,p.ParentID as ParentFieldName,p.PID,d.Name,(cast(d.Workload as varchar) || '(天)') as Workload,date(d.StarteDate) as StarteDate,date(d.EndDate) as EndDate,null as Manager");
sql.Append(" ,d1.Name as Progress,p.WBSNo, p.CREATED from DeliverablesJBXX d");
sql.Append(" left join NodeProgress n on n.Status=@Status and d.NodeID=n.NodeID");
sql.Append(" left join DictItem d1 on n.PType = d1.No and d1.DictNo=" + (int)DictCategory.PlanFinishStatus);
sql.Append(" left join PNode p on substr(p.ID, 1, 36) = d.NodeID");
if (!string.IsNullOrEmpty(Manager))
{
sql.Append(" inner join deliverableswork dw on dw.jbxxid = substr(d.id,1,36) and dw.manager=@Manager");
}
sql.Append(" where d.Status=@Status and p.Status=@Status and p.PType=1");
if (StarteDate != DateTime.MinValue)
{
sql.Append(" and date(d.StarteDate)>=date(@StarteDate)");
}
if (EndDate != DateTime.MinValue)
{
sql.Append(" and date(d.EndDate)<=date(@EndDate)");
}
if (PType > 0)
{
sql.Append(" and n.PType=@PType");
}
//sql.Append(" order by d.CREATED");
//交合
sql.Append(" union");
//查询节点
sql.Append(@" select substr(p1.ID,1,36) as KeyFieldName,p1.ParentID as ParentFieldName,p1.PID,p1.Name,null as Workload,date(min(startedate)) As startedate,date(max(enddate)) as enddate ,null as Manager,null as Progress, WBSNo,p1.CREATED from ct p1
where p1.Status = @Status and (p1.PType=0 or p1.PType is null)
group by id");
//最外层
sql.Append(" )");
sql.Append(" where PID=@PID"); //如果显示所有项目则注释掉此行
sql.Append(" order by WBSNo,PID,CREATED");
DataTable dt = NHHelper.ExecuteDataTable(sql.ToString(), qlist);
return(dt);
}