二向箔(提问者) 回复 Z4u3z1 select distinct a.requestid,
case when 状态=\"未提交\" then \"未提交\" when 是否逾期填报=\"逾期提交\" and (状态=\"待评价\" or 状态=\"已评价\") then \"逾期提交\" when 状态=\"待评价\" and 是否逾期填报 is null then \"按时提交\" end as 状态,
关键人id,关键人,公司id,公司名称,条线id,itimeture,itime,
case when 状态=\"待评价\" or 状态=\"未提交\" then \"未评价\" when 状态=\"已评价\" and (是否逾期评价=\"逾期评价\" or 是否逾期评价=\"逾期提交\") then \"逾期评价\" when 状态=\"已评价\" and 是否逾期评价 is null then \"按时评价\" end as 是否逾期填报,
case when 状态=\"待评价\" or 状态=\"已评价\" then \"未评价\" when 状态=\"已评价\" and 是否逾期评价,
关键人部门职位,gsgw,concat(\"(\",m.name,\"条线)\") as name
from (
select g.id,f.requestid,f.fqr as 关键人id,h.lastname as 关键人,f.szqy as 公司id,c.SUBCOMPANYNAME as 公司名称,f.sztx as 条线id,
f.fqrq as 发起日期
,concat(left(f.fqrq,4),QUARTER(f.fqrq)) as itimeture
,concat(left(f.fqrq,4),\"年 第\",case when QUARTER(f.fqrq)=1 then \"一\"
when QUARTER(f.fqrq)=2 then \"二\"
when QUARTER(f.fqrq)=3 then \"三\"
when QUARTER(f.fqrq)=4 then \"四\" end,\"季度\") as itime
,case when f.sfyqtb=1 and f.sfyqpj=1 then \"逾期提交\" else \"\" end as 是否逾期填报
,case when f.sfyqpj=1 then \"逾期评价\" when f.sfyqtb=1 and f.sfyqpj is null then \"逾期提交\" else \"\" end as 是否逾期评价
,case when f.zt=0 then \"未提交\" when f.zt=1 then \"待评价\" when f.zt=2 then \"已评价\" end as 状态,g.gjrbmjzw as 关键人部门职位
,concat(c.SUBCOMPANYNAME,\" \",g.gjrbmjzw) as gsgw
from formtable_main_776 f
left join uf_gjrgzjbtsqd g on (g.szqy=f.szqy and g.sztx=f.sztx)
left join hrmresource h on h.id=f.fqr
left join hrmsubcompany c on c.id=f.szqy
left join hrmdepartment d on d.id=h.DEPARTMENTID
)a left join
( select id,mainid,name,DISORDER from mode_selectitempagedetail where mainid=88 and CANCEL=0
)m on a.条线id=m.DISORDER
left join
(
select f.requestid,h.loginid as 关键人账号,h4.LOGINID as 对口部门经理账号, h5.LOGINID as 对口部门分管账号,h2.LOGINID as 企业总经理账号,h3.LOGINID as 企业分管领导账号
from formtable_main_776 f
left join hrmresource h on h.id=f.fqr
-- left join hrmsubcompany c on c.id=f.szqy
left join matrixtable_3 mc on mc.gs=f.szqy
left join hrmresource h2 on h2.id=mc.qyzjl
left join hrmresource h3 on h3.id=mc.jtgsfgld
left join hrmdepartment d on find_in_set(d.id,f.dkbm)
left join matrixtable_2 m2 on m2.id=d.id
left join hrmresource h4 on h4.id=m2.jzglbmfzr
left join hrmresource h5 on h5.id=m2.jzglbmfgld
)qx on a.requestid=qx.requestid
where 1=1
and case when \'${fine_username}\'
in (
select h.loginid from hrmrolemembers r
left join hrmresource h on h.id=r.RESOURCEID
where roleid=306) then 1=1
else( qx.关键人账号=\'${fine_username}\' or qx.对口部门经理账号=\'${fine_username}\' or qx.对口部门分管账号=\'${fine_username}\' or
qx.企业总经理账号=\'${fine_username}\' or qx.企业分管领导账号=\'${fine_username}\' ) end
${if(len(itime) ==0, \"\", \" AND a.itimeture in (\'\" + itime + \"\')\")}
${if(len(csgs) ==0, \"\", \" AND a.公司名称 in (\'\" + csgs + \"\')\")}
${if(len(tx) ==0, \"\", \" AND m.name in (\'\" + tx + \"\')\")}
${if(len(iname) ==0, \"\", \" AND a.关键人 in (\'\" + iname + \"\')\")}
order by a.id
${if(len(wcqk) ==0, \"\", if(wcqk=\'未提交\',\"and a.zt=\'0\' \",if(wcqk=\'逾期提交\',\"and (a.sfyqtb=\'1\' and a.zt<>\'0\')\",\" and (a.sfyqtb=\'0\' and a.zt<>\'0\')\")))}
${if(len(yqqk) ==0, \"\", if(yqqk=\'未评价\',\"and a.zt<>\'2\'\",if(yqqk=\'逾期评价\',\"and (a.sfyqpj=\'1\' and a.zt=\'2\')\",\" and (a.sfyqpj=\'0\' and a.zt=\'2\')\")))}
（wcqk=\'未提交\' 时：选择a.zt=\'0\'的数据
wcqk=\'逾期提交\'时：选择a.sfyqpj=\'1\' and a.zt=\'2\'
wcqk=\'按时提交\'时：选择a.sfyqtb=\'0\' and a.zt<>\'0\'
wcqk选\'未提交\'和\'逾期提交\'时：选择a.zt=\'0\' 并上（a.sfyqpj=\'1\' and a.zt=\'2\'）
以此类推组合，yqqk也是这样类推
yqqk=\'未评价\' 时：选择a.zt<>\'2\'的数据
yqqk=\'逾期评价\' 时：选择(a.sfyqpj=\'1\' and a.zt=\'2\')\'的数据
yqqk=\'按时提交\' 时：选择(a.sfyqpj=\'0\' and a.zt=\'2\')的数据
·····
）
1min目标场景问卷
立即参与
