public DataTable GetPersonEfficiency(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 rw.Manager = rin.Manager and rin.created<rw.created) as RowNo, '日常' as source, r.name as name,r.Desc,date(r.startdate) as startedate,date(r.enddate) as enddate,rw.workload,rw .actualworkload ,'1' as type,s.name as allname, cast(round(rw.workload*1.0/rw.actualworkload*100,3) as varchar(20)) || '%' 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 where manager = dw.manager)+(select count(*) from troublework where manager = dw.manager) + count(*)+1 from deliverableswork d where dw.Manager = d.Manager and d.created<dw.created)as rowno, '交付物' as source,d.name as name,d.Desc,date(d.startedate) as startdate,date(d.enddate) as enddate,dw.workload,dw.actualworkload , '3' as type ,s.name as allname, cast(round(dw.workload*1.0/dw.actualworkload*100,3) as varchar(20)) || '%' 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 where manager = tw.manager)+ count(*)+1 from Troublework t where tw.Manager = t.Manager and t.created<tw.created)as rowno, '问题' as source,t.name as name ,t.Desc,date(t.startedate) as startdate,date(t.enddate) as enddate,tw.workload,tw.actualworkload,'2' as type ,s.name as allname, cast(round((tw.workload*1.0/tw.actualworkload),3)*100 as varchar(20)) || '%' 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 ") + //date(startedate)>=date(@staretdate) //and date(enddate)<=date(@enddate) //and finishstatus = @finishstatus @" union /*标题*/ select name as RowNo,'来源' as source,'名称' as name ,'描述' as desc,'开始' as startedate,'结束' as enddate,'工作量' as workload, '实际工作量' as actualworkload ,0 as type,name as allname,'效率系数' as efficiency,null as finishstatus from stakeholders s where s.pid=@pid and s.status =@status union /*签字行*/ select null as RowNo,null as source,null as name ,null as desc,null as startedate,null as enddate,null as workload,'平均系数:' as actualworkload ,-3 as type,name || '1' as allname, (select cast(round((ifnull((select sum(workload) from troublework where substr(s.id,1,36)=manager ),0)+ ifnull((select sum(workload) from routinework where substr(s.id,1,36)=manager ),0)+ ifnull((select sum(workload) from deliverableswork where substr(s.id,1,36)=manager ),0) )*1.0/(ifnull((select sum(actualworkload) from troublework where substr(s.id,1,36)=manager ),0)+ ifnull((select sum(actualworkload) from routinework where substr(s.id,1,36)=manager ),0)+ ifnull((select sum(actualworkload) from deliverableswork where substr(s.id,1,36)=manager ),0))*100.0,1) as varchar(20)) ||'%' ) as efficiency, null as finishstatus from stakeholders s where s.pid=@pid and s.status =@status ) order by allname,type,rowno "); DataTable dt = NHHelper.ExecuteDataTableNoRow(sql.ToString(), qlist); return(dt); }
/// <summary> /// 获取贡献率 /// </summary> /// <param name="PID"></param> /// <returns></returns> public DataTable GetMemberRate(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(@"with cte as ( /*内容*/ select * from ( /*日常*/ select * from (select (select count(*)+1 from routinework rin where rw.Manager = rin.Manager and rin.created<rw.created) as RowNo, '日常' as source, r.name as name,r.Desc,date(r.startdate) as startedate,date(r.enddate),r.workload,rw.workload as zhanbi,'1' as type,s.name as allname from routinework rw left join stakeholders s on substr(s.id,1,36) = rw.Manager left join routine r on rw.routineid = substr(r.id,1,36) where r.status = @status and s.pid =@pid and s.status =@status " + //日常 时间、完成情况 (Startedate != DateTime.MinValue?"and date(r.startdate)>=date(@StarteDate) ":"and 1=1 ") + (Enddate != DateTime.MinValue?"and date(r.enddate )<=date(@EndDate)":"and 1=1 ") + (FinishStatus != 0 ? (FinishStatus == 3 ? "and r.finishstatus=3 " : "and r.finishstatus!=3") : "and 1=1 ") + @" order by rw.manager,rw.created) union /*交付物*/ select * from( select (select (select count(*) from routinework where manager = dw.manager)+ (select count(*) from troublework where manager = dw.manager) + count(*)+1 from deliverableswork d where dw.Manager = d.Manager and d.created<dw.created)as rowno, '交付物' as source,d.name as name,d.Desc,date(d.startedate),date(d.enddate),d.workload,dw.workload as zhanbi,'3' as type ,s.name as allname from Deliverableswork dw left join stakeholders s on substr(s.id,1,36) = dw.Manager left join DeliverablesJBXX d on dw.JBXXid = substr(d.id,1,36) left join PNode p on substr(p.id,1,36) = d.nodeid left join NodeProgress pg on pg.nodeid = substr(p.id,1,36) where d.status = @status and s.pid =@pid and s.status =@status and pg.status = @status and p.status = 1 " + //交付物 时间、完成情况 (Startedate != DateTime.MinValue ? "and date(d.startedate)>=date(@StarteDate) " : "and 1=1 ") + (Enddate != DateTime.MinValue ? "and date(d.enddate)<=date(@EndDate) " : "and 1=1 ") + (FinishStatus != 0 ? (FinishStatus == 5?"and pg.ptype=5 ": "and pg.ptype!=5 ") : "and 1=1 ") + @"order by dw.manager,dw.created) union /*问题*/ select * from( select (select (select count(*) from routinework where manager = tw.manager)+ count(*)+1 from Troublework t where tw.Manager = t.Manager and t.created<tw.created)as rowno, '问题' as source,t.name as name ,t.Desc,date(t.startedate),date(t.enddate),t.workload,tw.workload as zhanbi,'2' as type ,s.name as allname from Troublework tw left join stakeholders s on substr(s.id,1,36) = tw.Manager left join Trouble t on tw.troubleid = substr(t.id,1,36) where t.status = 1 and s.pid =@pid and s.status =@status " + //问题 时间、完成情况 (Startedate != DateTime.MinValue ? "and date(t.startedate)>=date(@StarteDate) " : "and 1=1 ") + (Enddate != DateTime.MinValue ? "and date(t.enddate)<=date(@EndDate) " : "and 1=1 ") + (FinishStatus != 0 ? (FinishStatus == 5 ? "and t.handlestatus=3 " : "and t.handlestatus!=3 ") : "and 1=1 ") + @"order by tw.manager,tw.created) ) ) /*总工作量*/ select '总工作量' as RowNo, ( select sum(workload) from ( select sum(rw.workload) as workload from routinework rw left join stakeholders s on substr(s.id,1,36) = rw.Manager left join routine r on substr(r.id,1,36) = rw.routineid where s.PID =@pid and s.status=1 " + //日常 (Startedate != DateTime.MinValue ? "and date(r.startdate)>=date(@StarteDate) " : "and 1=1 ") + (Enddate != DateTime.MinValue ? "and date(r.enddate )<=date(@EndDate)" : "and 1=1 ") + (FinishStatus != 0 ? (FinishStatus == 5 ? "and r.finishstatus=5 " : "and r.finishstatus!=5 ") : "and 1=1 ") + @"union select sum(tw.workload) as workload from troublework tw left join stakeholders s on substr(s.id,1,36) = tw.Manager left join trouble t on substr(t.id,1,36) = tw.troubleid where s.PID =@pid and s.status=1 " + //问题 时间、完成情况 (Startedate != DateTime.MinValue ? "and date(t.startedate)>=date(@StarteDate) " : "and 1=1 ") + (Enddate != DateTime.MinValue ? "and date(t.enddate)<=date(@EndDate) " : "and 1=1 ") + (FinishStatus != 0 ? (FinishStatus == 5 ? "and t.handleresult=5 " : "and t.handleresult!=5 ") : "and 1=1 ") + @"union select sum(dw.workload) as workload from deliverableswork dw left join stakeholders s on substr(s.id,1,36) = dw.Manager left join DeliverablesJBXX d on dw.JBXXid = substr(d.id,1,36) left join PNode p on substr(p.id,1,36) = d.nodeid left join NodeProgress pg on pg.nodeid = substr(p.id,1,36) where s.PID =@pid and s.status=1 and pg.status = 1 and p.status = 1 " + //交付物 时间、完成情况 (Startedate != DateTime.MinValue ? "and date(d.startedate)>=date(@StarteDate) " : "and 1=1 ") + (Enddate != DateTime.MinValue ? "and date(d.enddate)<=date(@EndDate) " : "and 1=1 ") + (FinishStatus != 0 ? (FinishStatus == 5 ? "and pg.ptype=5 " : "and pg.ptype!=5 ") : "and 1=1 ") + @")) as Source, '天' as name,null as desc,null as startedate,null as enddate,null as workload,null as zhanbi,-2 as type,null as allname union /*人员*/ select name as RowNo,'总工作量' as Source ,ifnull((select sum(zhanbi) from cte c where c.allname =s.name group by c.allname ),0) as name,'天' as desc, '占比' as startedate, cast(round( (select sum(zhanbi) from cte c where c.allname =s.name group by c.allname )*1.0/ ( select sum(workload) from ( select sum(workload) as workload from routinework rw left join stakeholders s on substr(s.id,1,36) = rw.Manager where s.PID =@pid and s.status=1 union select sum(workload) as workload from troublework tw left join stakeholders s on substr(s.id,1,36) = tw.Manager where s.PID =@pid and s.status=1 union select sum(workload) as workload from deliverableswork dw left join stakeholders s on substr(s.id,1,36) = dw.Manager where s.PID =@pid and s.status=1 ) ),3 )*100 as varchar(20)) ||'%' as enddate,null as workload,null as zhanbi ,-1 as type,name as allname from stakeholders s where s.pid=@pid and s.status =@status union /*标题*/ select '编号' as RowNo,'来源' as source,'名称' as name ,'描述' as desc,'开始' as startrdate,'结束' as enddate,'工作量' as workload,'占比' as zhanbi ,0 as type,name as allname from stakeholders s where s.pid=@pid and s.status =@status union /*签字行*/ select null as RowNo,null as source,null as name ,null as desc,null as startrdate,null as enddate,null as workload,null as zhanbi ,-3 as type,name as allname from stakeholders s where s.pid=@pid and s.status =@status union select * from cte /*排序*/ order by allname,type"); DataTable dt = NHHelper.ExecuteDataTableNoRow(sql.ToString(), qlist); return(dt); }