meidu2021/daju/src-xcgl/main/java/com/xcgl/weixin/sql/WXDao_getTaskStati.sql

select CONCAT(cast(sum(todaydonetotal) as char),'/',cast(sum(todaytotal) as char)) today ,
CONCAT(cast(sum(weekdonetotal) as char),'/',cast(sum(weektotal) as char)) week ,
CONCAT(cast(sum(monthdonetotal) as char),'/',cast(sum(monthtotal) as char)) month 
from 
(select count(*) todaytotal,0 todaydonetotal ,0 weektotal,0 weekdonetotal,0 monthtotal,0 monthdonetotal
from p_projecttask task 
left join t_s_user user on task.owner = user.id
where left(planendtime,10) =:today
<#if openid ?exists && openid ?length gt 0>
and openid =:openid
</#if>
<#if projectid ?exists && projectid ?length gt 0>
and task.projectid =:projectid
</#if>
union all 
select 0 todaytotal,count(*) todaydonetotal ,0 weektotal,0 weekdonetotal,0 monthtotal,0 monthdonetotal
from p_projecttask task 
left join t_s_user user on task.owner = user.id
where left(planendtime,10) =:today
<#if openid ?exists && openid ?length gt 0>
and openid =:openid
</#if>
<#if projectid ?exists && projectid ?length gt 0>
and task.projectid =:projectid
</#if>
and task.taskstatus  in ('20','30')
union all
select  0 todaytotal,0 todaydonetotal ,count(*) weektotal,0 weekdonetotal,0 monthtotal,0 monthdonetotal 
from p_projecttask task 
left join t_s_user user on task.owner = user.id
where left(planendtime,10) >=:weekstart and left(planendtime,10) <:weekend
<#if openid ?exists && openid ?length gt 0>
and openid =:openid
</#if>
<#if projectid ?exists && projectid ?length gt 0>
and task.projectid =:projectid
</#if>
union all 
select 0 todaytotal,0 todaydonetotal ,0 weektotal,count(*) weekdonetotal,0 monthtotal,0 monthdonetotal 
from p_projecttask task 
left join t_s_user user on task.owner = user.id
where left(planendtime,10) >=:weekstart and left(planendtime,10) <:weekend
<#if openid ?exists && openid ?length gt 0>
and openid =:openid
</#if>
<#if projectid ?exists && projectid ?length gt 0>
and task.projectid =:projectid
</#if>
and task.taskstatus in ('20','30')
union all
select 0 todaytotal,0 todaydonetotal ,0 weektotal,0 weekdonetotal,count(*) monthtotal,0 monthdonetotal 
from p_projecttask task 
left join t_s_user user on task.owner = user.id
where left(planendtime,10) >=:monthstart and left(planendtime,10) <:monthend 
<#if openid ?exists && openid ?length gt 0>
and openid =:openid
</#if>
<#if projectid ?exists && projectid ?length gt 0>
and task.projectid =:projectid
</#if>
union all 
select 0 todaytotal,0 todaydonetotal ,0 weektotal,0 weekdonetotal,0 monthtotal,count(*) monthdonetotal 
from p_projecttask task 
left join t_s_user user on task.owner = user.id
where left(planendtime,10) >=:monthstart and left(planendtime,10) <:monthend 
<#if openid ?exists && openid ?length gt 0>
and openid =:openid
</#if>
<#if projectid ?exists && projectid ?length gt 0>
and task.projectid =:projectid
</#if>
and task.taskstatus in ('20','30')
) total