/// <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>
/// 里程碑列表
/// 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:20170601(zhuguanjun)
/// Updated:20170607(ChengMengjia) 添加作为节点插入
/// </summary>
/// <param name="entity">问题实体</param>
/// <param name="listWork">责任人列表</param>
public virtual void AddTrouble(Trouble entity, PNode node, List <TroubleWork> listWork)
{
ISession s = NHHelper.GetCurrentSession();
try
{
s.BeginTransaction();
s.Save(entity);
if (node != null)
{
s.Save(node);
}
if (listWork != null)
{
foreach (TroubleWork item in listWork)
{
item.ID = Guid.NewGuid().ToString();
item.Status = 1;
item.CREATED = DateTime.Now;
item.TroubleID = entity.ID.Substring(0, 36);
s.Save(item);
}
}
UpdateProject(s);//更新项目时间
s.Transaction.Commit();
s.Close();
}
catch (Exception ex)
{
s.Transaction.Rollback();
s.Close();
throw new Exception("插入实体失败", ex);
}
}
/// <summary>
/// 返回分页数据
/// Created:20170330(ChengMengjia)
/// </summary>
/// <param name="QueryHead"></param>
/// <param name="QueryBody"></param>
/// <param name="qlist"></param>
/// <returns></returns>
public static GridData GetGridData(int PageIndex, int PageSize, string QueryHead, string QueryBody, List <QueryField> qlist)
{
GridData result = new GridData();
result.count = 0;
result.data = new DataTable();
string sql = "select count(1) n " + QueryBody;
DataSet ds = ExecuteDataset(sql, qlist);
if (ds != null && ds.Tables.Count > 0)
{
result.count = int.Parse(ds.Tables[0].Rows[0]["n"].ToString());
sql = QueryHead + QueryBody;
DataTable dt = NHHelper.ExecutePageDataTable(sql, qlist, PageSize, PageIndex);
if (dt != null)
{
dt.Columns.Add("RowNo");
int i = 1;
if (PageIndex >= 1)
{
i += (PageIndex - 1) * PageSize;
}
foreach (DataRow row in dt.Rows)
{
row["RowNo"] = i;
i++;
}
}
result.data = dt;
}
return(result);
}
/// <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>
/// 项目修改
/// Created:20170527(ChengMengjia)
/// </summary>
/// <param name="project"></param>
/// <param name="id"></param>
public void Update(Project entity, out string id)
{
id = string.Empty;
ISession s = NHHelper.GetCurrentSession();
try
{
s.BeginTransaction();
id = entity.ID;
entity.UPDATED = DateTime.Now;
entity.ProjectLastUpdate = DateTime.Now;
s.Update(entity);
#region 顶级节点
s.CreateSQLQuery("update PNode set Name=:name where PID=:pid and ParentID is null and Status=1; ")
.SetString("name", entity.Name).SetString("pid", entity.ID).ExecuteUpdate();
#endregion
s.Transaction.Commit();
s.Close();
}
catch (Exception ex)
{
s.Transaction.Rollback();
s.Close();
throw new Exception("保存实体失败", ex);
}
}
/// <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>
/// 收款列表
/// 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>
/// 交付物节点的添加
/// </summary>
/// <param name="entity"></param>
/// <param name="hisFlg"></param>
/// <param name="id"></param>
public virtual void AddDeliverables(PNode node, DeliverablesJBXX jbxx, NodeProgress progress, List <DeliverablesWork> listWork)
{
ISession s = NHHelper.GetCurrentSession();
try
{
s.BeginTransaction();
s.Save(node);
s.Save(jbxx);
s.Save(progress);
if (listWork != null)
{
foreach (DeliverablesWork entity in listWork)
{
entity.ID = Guid.NewGuid().ToString();
entity.Status = 1;
entity.CREATED = DateTime.Now;
entity.JBXXID = jbxx.ID.Substring(0, 36);
entity.Manager = entity.Manager.Substring(0, 36);
s.Save(entity);
}
}
UpdateProject(s);//更新项目时间
s.Transaction.Commit();
s.Close();
}
catch (Exception ex)
{
s.Transaction.Rollback();
s.Close();
throw new Exception("插入实体失败", ex);
}
}
/// <summary>
/// 周报发送记录新增
/// Created:20170509(ChengMengjia)
/// </summary>
/// <param name="project"></param>
/// <param name="node"></param>
/// <param name="id"></param>
public JsonResult AddPubInfo(PubInfo entity, List <PubInfoFiles> list)
{
JsonResult jsonreslut = new JsonResult();
ISession s = NHHelper.GetCurrentSession();
try
{
s.BeginTransaction();
s.Save(entity);
list.ForEach(t =>
{
s.Save(t);
});
UpdateProject(s);
s.Transaction.Commit();
s.Close();
jsonreslut.result = true;
jsonreslut.msg = "操作成功！";
jsonreslut.data = entity.ID;
}
catch (Exception ex)
{
LogHelper.WriteException(ex, LogType.DataAccessDLL);
jsonreslut.result = false;
jsonreslut.msg = ex.Message;
s.Transaction.Rollback();
s.Close();
}
return(jsonreslut);
}
/// <summary>
/// 更新付款信息和里程碑
/// Created：20170612(ChengMengjia)
/// </summary>
/// <param name="entity">日常工作实体</param>
/// <param name="listWork">负责人列表</param>
public void UpdateEntities(SubContractLCB newlcb, SubContractLCB oldlcb, SubContractSKXX newskxx, SubContractSKXX oldskxx)
{
ISession s = NHHelper.GetCurrentSession();
try
{
s.BeginTransaction();
if (newlcb != null)
{
s.Save(newlcb);
}
if (oldlcb != null)
{
s.Update(oldlcb);
}
if (newskxx != null)
{
s.Save(newskxx);
}
if (oldskxx != null)
{
s.Update(oldskxx);
}
UpdateProject(s);//更新项目时间
s.Transaction.Commit();
s.Close();
}
catch (Exception ex)
{
s.Transaction.Rollback();
s.Close();
throw new Exception("插入实体失败", ex);
}
}
/// <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>
/// 交付物节点的修改
/// </summary>
/// <param name="entity"></param>
/// <param name="hisFlg"></param>
/// <param name="id"></param>
public virtual void UpdatedDeliverables(DeliverablesJBXX new_jbxx, DeliverablesJBXX old_jbxx, PNode new_node, PNode old_node, List <DeliverablesWork> listWork)
{
ISession s = NHHelper.GetCurrentSession();
try
{
s.BeginTransaction();
s.Save(new_jbxx);
s.Save(new_node);
s.Update(old_jbxx);
s.Update(old_node);
if (listWork != null)
{
s.CreateQuery("delete from DeliverablesWork where JBXXID='" + new_jbxx.ID.Substring(0, 36) + "';").ExecuteUpdate();
foreach (DeliverablesWork entity in listWork)
{
entity.ID = Guid.NewGuid().ToString();
entity.CREATED = DateTime.Now;
entity.JBXXID = new_jbxx.ID.Substring(0, 36);
entity.Manager = entity.Manager.Substring(0, 36);
entity.Status = 1;
s.Save(entity);
}
}
UpdateProject(s);//更新项目时间
s.Transaction.Commit();
s.Close();
}
catch (Exception ex)
{
s.Transaction.Rollback();
s.Close();
throw new Exception("更新失败", ex);
}
}
/// <summary>
/// 获取周报收件人列表
/// Created:2017.04.11(ChengMengjia)
/// </summary>
/// <returns></returns>
public IList <dynamic> GetMemberList(string ProjectID, string IDs)
{
string sql = "select * from Stakeholders where PID=:PID and ID in (" + IDs + ")";
ISQLQuery query = NHHelper.GetCurrentSession().CreateSQLQuery(sql.ToString());
query.SetString("PID", ProjectID);
return(query.DynamicList());
}
/// <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获得变更实体
/// 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>
/// 获取供应商无版本号的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>
/// 项目问题查询
/// 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)
/// 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>
/// 根据版本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)
/// </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>
/// 获取带分页和编号的干系人列表集合
/// Updated:20170601(ChengMengjia) 干系人类别需要LeftJoin查询
/// </summary>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="qlist"></param>
/// <returns></returns>
public GridData GetGridData(int PageSize, int PageIndex, List <QueryField> qlist)
{
StringBuilder QueryHead = new StringBuilder();
StringBuilder QueryBody = new StringBuilder();
QueryHead.Append(" select s.*, s.companyname || '-' || s.name as truename, d1.Name as SendTypeName,d2.Name as TypeName");
QueryBody.Append(" from Stakeholders s left join DictItem d1 on s.SendType = d1.No and d1.DictNo=" + (int)DictCategory.SendType);
QueryBody.Append(" left join DictItem d2 on s.Type = d2.No and d2.DictNo=" + (int)DictCategory.StakehoderType);
QueryBody.Append(" where s.PID=@PID and s.status=@Status order by s.updated desc,s.created desc");
return(NHHelper.GetGridData(PageIndex, PageSize, QueryHead.ToString(), QueryBody.ToString(), qlist));
}
/// <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:20170328(ChengMengjia)
/// </summary>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="PID"></param>
/// <returns></returns>
public GridData GetJLPJList(int PageIndex, int PageSize, string PID)
{
List <QueryField> qf = new List <QueryField>();
string sqlHead = " select r.id,r.Name,r.Content,strftime('%Y-%m-%d',r.JudgeDate)JudgeDate ";
StringBuilder sqlBody = new StringBuilder();
sqlBody.Append(" from SupervisorJudge r ");
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
});
return(NHHelper.GetGridData(PageIndex, PageSize, sqlHead, sqlBody.ToString(), qf));
}
/// <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>
/// 获取带分页和编号的数据集合
/// </summary>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="qlist"></param>
/// <returns></returns>
public GridData GetGridData(int PageIndex, int PageSize, List <QueryField> qlist)
{
StringBuilder QueryHead = new StringBuilder();
StringBuilder QueryBody = new StringBuilder();
QueryHead.Append(" select r.*,d1.Name as LevelName,d2.Name as ProbabilityName,d3.Name as HandleTypeName,p1.Name as SourceName,p2.Name as DependencyName");
QueryBody.Append(" from Risk r left join DictItem d1 on r.Level = d1.No and d1.DictNo=" + (int)DictCategory.Level);
QueryBody.Append(" left join DictItem d2 on r.Probability = d2.No and d2.DictNo=" + (int)DictCategory.Probability);
QueryBody.Append(" left join DictItem d3 on r.HandleType = d3.No and d3.DictNo=" + (int)DictCategory.HandType);
QueryBody.Append(" left join PNode p1 on r.Source = p1.ID");
//QueryBody.Append(" left join PNode p2 on r.Dependency = p2.ID");
QueryBody.Append(" left join PNode p2 on r.Dependency = p2.ID");
QueryBody.Append(" where r.PID=@PID and r.status=@Status order by r.created");
return(NHHelper.GetGridData(PageIndex, PageSize, QueryHead.ToString(), QueryBody.ToString(), qlist));
}
/// <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);
}