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