九九九九九(提问者) 回复 重庆一棵草${if(roleId=\'1\',\"SELECT DISTINCT
tpi.id,
tpi.project_status,
tpi.labor_dispatch_type,
tpi.defend_status,
tpi.parent_or_sub,
tpi.product_type_level3_name,
tpi.need_stars,
tpi.create_time,
tpi.cid,
tpi.project_name,
tpi.address,
tpi.manager_code,
tpi.manager_name,
tpi.refuse_sys_reason,
sd.`name`,
t.praiseNum
FROM
project.t_project_info tpi
JOIN `fgbp-sca`.sys_dept sd ON sd.id = tpi.dept_id
AND tpi.del_flag = \'1\'
LEFT JOIN project.t_project_demand_person tpdp ON tpdp.project_id = tpi.id
AND tpdp.del_flag = \'1\'
#表扬数
LEFT JOIN ( SELECT
ta.project_id,COUNT(id)praiseNum
FROM
project.t_article ta
WHERE
ta.project_id IN ( SELECT DISTINCT id FROM project.t_project_info WHERE del_flag = \'1\' ) and ta.type=\'0\' and ta.del_flag=\'1\' and ta.`status`=\'2\' and ta.create_time >\'\"+create_time+\"\'
GROUP BY
ta.project_id ) t on t.project_id=tpi.id
GROUP BY
tpi.id,
tpi.cid,
tpi.project_name,
tpi.address,
tpi.manager_code,
tpi.manager_name, t.praiseNum ORDER BY tpi.update_time DESC\"
,if(roleId=\'2\',\"us,
tpi.labor_dispatch_type,
tpi.defend_status,
tpi.parent_or_sub,
tpi.product_type_level3_name,
tpi.need_stars,
tpi.create_time,
tpi.project_name,
tpi.address,
tpi.manager_code,
tpi.manager_name,
tpi.refuse_sys_reason,
sd.`name`,
SUM( tpdp.demand_num ) demand_num,
q.onlineNum,
SUM( tpdp.demand_num )-q.onlineNum unPostNum,
w.averDays,
e.clientNum,
r.netNum,
t.praiseNum,
u.articleNum,
i.taskNum,
p.inspectFinishRate,
p.inspectNum,
j.reportNum,
z.appraiseFinishRate,
z.appraiseNum,
x.interviewNum
FROM
project.t_project_info tpi
JOIN `fgbp-sca`.sys_dept sd ON sd.id = tpi.dept_id
AND tpi.id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\')
AND tpi.del_flag = \'1\'
LEFT JOIN project.t_project_demand_person tpdp ON tpdp.project_id = tpi.id
AND tpdp.del_flag = \'1\'
#在岗
LEFT JOIN (SELECT DISTINCT
tpi.cid,tpi.project_name,
COUNT( DISTINCT user_id ) onlineNum
FROM
project.t_project_info tpi
JOIN project.t_project_person tpp ON tpi.id = tpp.project_id
AND tpi.del_flag = \'1\'
AND tpp.del_flag = \'1\'
and tpp.person_type=\'3\'
AND tpi.id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\')
GROUP BY
tpi.cid,tpi.project_name) q on q.cid=tpi.cid
#转正天数
LEFT JOIN (SELECT
project_id,
CONVERT (
SUM(
TIMESTAMPDIFF( DAY, startServiceDate, confirmationDate ))/ COUNT( peopleNo ),
DECIMAL ( 10, 2 )) averDays
FROM
employee.`t_employee`
WHERE
peopleNo IN ( SELECT DISTINCT `code` FROM project.`t_project_person` WHERE project_id IN (select DISTINCT id from project.t_project_info where id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\')and del_flag=\'1\') and del_flag=\'1\') AND del_flag = \'1\' AND person_type = \'3\' AND `code` IS NOT NULL )
GROUP BY
project_id)w on w.project_id= tpi.id
LEFT JOIN (SELECT
q.id,
COUNT( DISTINCT su.user_id ) clientNum
FROM
`fgbp-sca`.`sys_user` su
RIGHT JOIN (
SELECT DISTINCT
a.dept_id,
a.id,
b.network_id
FROM
project.t_project_info a
LEFT JOIN project.`t_project_network` b ON a.id = b.project_id
AND b.del_flag = \'1\'
AND a.del_flag = \'1\'
WHERE
a.id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\')
AND b.network_id IS NOT NULL
) q ON q.network_id = su.dept_id and su.del_flag=\'0\' and su.create_by=\'\"+userId+\"\'
GROUP BY
q.id )e on e.id=tpi.id
#网点数
LEFT JOIN (
SELECT
COUNT( bank_id ) netNum ,e.projectId
FROM
(
SELECT DISTINCT
e.dept_id,
te.bank_id ,e.projectId
FROM
employee.t_employee te
JOIN (
SELECT DISTINCT
a.dept_id,
b.id ,a.id projectId
FROM
project.`t_project_info` a
JOIN project.`t_project_network` b ON a.id = b.project_id
AND a.id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\')
AND a.del_flag = \'1\'
AND b.del_flag = \'1\'
AND b.id IS NOT NULL
GROUP BY
a.dept_id,
b.id
) e ON e.id = te.bank_id
AND te.del_flag = \'1\'
AND te.person_type = \'3\'
GROUP BY
e.dept_id,
te.bank_id
) e
GROUP BY
e.projectId)r on r.projectId=tpi.id
#表扬数
LEFT JOIN (SELECT
ta.project_id,COUNT(id)praiseNum
FROM
project.t_article ta
WHERE
ta.project_id IN ( SELECT DISTINCT id FROM project.t_project_info WHERE manager_code =( SELECT DISTINCT `code` FROM project.t_project_person WHERE user_id = \'\"+userId+\"\' ) AND del_flag = \'1\' ) and ta.type=\'0\' and ta.del_flag=\'1\' and ta.`status`=\'2\'
GROUP BY
ta.project_id ) t on t.project_id=tpi.id
#网文数
LEFT JOIN (SELECT
COUNT( ta.id ) articleNum ,q.id
FROM
project.t_article ta
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\')AND del_flag = \'1\' ) q ON q.id = ta.project_id
AND ta.del_flag = \'1\'
GROUP BY
q.id )u on u.id=tpi.id
#任务数
LEFT JOIN(
SELECT
q.id,
COUNT( tt.id ) taskNum
FROM
project.t_task tt
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\') AND del_flag = \'1\' ) q ON q.id = tt.project_id
AND tt.del_flag = \'1\'
AND task_type IN ( 3, 4,14,15 )
AND task_status = \'1\'
GROUP BY
q.id
) i on i.id=tpi.id
#巡检完成率
LEFT JOIN (SELECT
q.id,
CONCAT( CONVERT (
(
COUNT(
IF
( sub_status = \'1\' OR sub_status = \'2\', 1, NULL ))/ COUNT(
ts.sub_id))* 100,
DECIMAL ( 10, 2 )),\'%\') inspectFinishRate,
COUNT(
IF
( sub_status = \'1\' OR sub_status = \'2\', 1, NULL )) inspectNum
FROM
project.t_subtask ts
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\') AND del_flag = \'1\' ) q on q.id=ts.project_id and ts.del_flag=\'1\' GROUP BY q.id
)p on p.id=tpi.id
#服务评价完成率
LEFT JOIN (SELECT
q.id,
CONCAT(CONVERT (
(
COUNT(
IF
( task_status = \'1\', 1, NULL ))/ COUNT(
IF
( task_status = \'1\' or task_status=\'0\', 1, NULL )))* 100,
DECIMAL ( 10, 2 )),\'%\') appraiseFinishRate
,COUNT(
IF
( task_status = \'1\', 1, NULL )) appraiseNum
FROM
project.t_emp_appraise tea
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\') AND del_flag = \'1\' ) q on q.id=tea.project_id and tea.del_flag=\'1\' GROUP BY q.id
)z on z.id=tpi.id
#交付报告数
LEFT JOIN(
SELECT
q.id,
COUNT( tt.id ) reportNum
FROM
project.t_task tt
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE id IN ( SELECT DISTINCT id FROM project.t_project_info where manager_code=(select DISTINCT `code` from project.t_project_person where user_id =\'\"+userId+\"\') and del_flag=\'1\') AND del_flag = \'1\' ) q ON q.id = tt.project_id
AND tt.del_flag = \'1\'
AND task_type IN ( 8, 9 )
AND task_status = \'1\'
GROUP BY
q.id
) j on j.id=tpi.id
#访谈数
LEFT JOIN
( SELECT
tri.project_id,
COUNT(tri.id) interviewNum
FROM
employee.`t_risk_interview` tri
WHERE
tri.project_id in (SELECT DISTINCT id
FROM project.t_project_info
WHERE manager_code = (
SELECT DISTINCT `code`
FROM project.t_project_person
WHERE user_id =\'\"+userId+\"\'
)
AND del_flag = \'1\' )
AND tri.del_flag = \'1\' and tri.state=\'1\'
GROUP BY
tri.project_id ) x on x.project_id=tpi.id
GROUP BY
tpi.id,
tpi.cid,
tpi.project_name,
tpi.address,
tpi.manager_code,
tpi.manager_name, q.onlineNum, w.averDays,e.clientNum,r.netNum,t.praiseNum,u.articleNum,i.taskNum,
p.inspectFinishRate,j.reportNum,z.appraiseFinishRate,x.interviewNum ORDER BY tpi.update_time DESC\"
,
\"SELECT DISTINCT
tpi.id,
tpi.cid,
tpi.project_status,
tpi.labor_dispatch_type,
tpi.defend_status,
tpi.parent_or_sub,
tpi.product_type_level3_name,
tpi.need_stars,
tpi.create_time,
tpi.project_name,
tpi.address,
tpi.manager_code,
tpi.manager_name,
tpi.refuse_sys_reason,
sd.`name`,
SUM( tpdp.demand_num ) demand_num,
q.onlineNum,
SUM( tpdp.demand_num )-q.onlineNum unPostNum,
w.averDays,
e.clientNum,
r.netNum,
t.praiseNum,
u.articleNum,
i.taskNum,
p.inspectFinishRate,
p.inspectNum,
j.reportNum,
z.appraiseFinishRate,
z.appraiseNum,
x.interviewNum
FROM
project.t_project_info tpi
JOIN `fgbp-sca`.sys_dept sd ON sd.id = tpi.dept_id
AND tpi.dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id )
AND tpi.del_flag = \'1\'
LEFT JOIN project.t_project_demand_person tpdp ON tpdp.project_id = tpi.id
AND tpdp.del_flag = \'1\'
#在岗
LEFT JOIN (SELECT DISTINCT
tpi.cid,tpi.project_name,
COUNT( DISTINCT user_id ) onlineNum
FROM
project.t_project_info tpi
JOIN project.t_project_person tpp ON tpi.id = tpp.project_id
AND tpi.del_flag = \'1\'
AND tpp.del_flag = \'1\'
and tpp.person_type=\'3\'
AND tpi.dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id )
GROUP BY
tpi.cid,tpi.project_name) q on q.cid=tpi.cid
#转正天数
LEFT JOIN (SELECT
project_id,
CONVERT (
SUM(
TIMESTAMPDIFF( DAY, startServiceDate, confirmationDate ))/ COUNT( peopleNo ),
DECIMAL ( 10, 2 )) averDays
FROM
employee.`t_employee`
WHERE
peopleNo IN ( SELECT DISTINCT `code` FROM project.`t_project_person` WHERE project_id IN (select DISTINCT id from project.t_project_info where dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id ) and del_flag=\'1\') AND del_flag = \'1\' AND person_type = \'3\' AND `code` IS NOT NULL )
GROUP BY
project_id)w on w.project_id= tpi.id
LEFT JOIN (SELECT
q.id,
COUNT( DISTINCT su.user_id ) clientNum
FROM
`fgbp-sca`.`sys_user` su
RIGHT JOIN (
SELECT DISTINCT
a.dept_id,
a.id,
b.network_id ,tpp.user_id
FROM
project.t_project_info a
LEFT JOIN project.`t_project_network` b ON a.id = b.project_id
AND b.del_flag = \'1\'
AND a.del_flag = \'1\'
LEFT JOIN project.t_project_person tpp on tpp.`code`=a.manager_code
WHERE
a.dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id )
AND b.network_id IS NOT NULL
) q ON q.network_id = su.dept_id WHERE su.del_flag=\'0\' and su.create_by=q.user_id
GROUP BY
q.id)e on e.id=tpi.id
#网点数
LEFT JOIN (
SELECT
COUNT( bank_id ) netNum ,e.projectId
FROM
(
SELECT DISTINCT
e.dept_id,
te.bank_id ,e.projectId
FROM
employee.t_employee te
JOIN (
SELECT DISTINCT
a.dept_id,
b.id ,a.id projectId
FROM
project.`t_project_info` a
JOIN project.`t_project_network` b ON a.id = b.project_id
AND a.dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id )
AND a.del_flag = \'1\'
AND b.del_flag = \'1\'
AND b.id IS NOT NULL
GROUP BY
a.dept_id,
b.id
) e ON e.id = te.bank_id
AND te.del_flag = \'1\'
AND te.person_type = \'3\'
GROUP BY
e.dept_id,
te.bank_id
) e
GROUP BY
e.projectId)r on r.projectId=tpi.id
#表扬数
LEFT JOIN (SELECT
ta.project_id,COUNT(id)praiseNum
FROM
project.t_article ta
WHERE
ta.project_id IN ( SELECT DISTINCT id FROM project.t_project_info WHERE dept_id=(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id ) AND del_flag = \'1\' ) and ta.type=\'0\' and ta.del_flag=\'1\' and ta.`status`=\'2\'
GROUP BY
ta.project_id) t on t.project_id=tpi.id
#网文数
LEFT JOIN (SELECT
COUNT( ta.id ) articleNum ,q.id
FROM
project.t_article ta
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id ) AND del_flag = \'1\' ) q ON q.id = ta.project_id
AND ta.del_flag = \'1\'
GROUP BY
q.id )u on u.id=tpi.id
#任务数
LEFT JOIN(
SELECT
q.id,
COUNT( tt.id ) taskNum
FROM
project.t_task tt
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id ) AND del_flag = \'1\' ) q ON q.id = tt.project_id
AND tt.del_flag = \'1\'
AND task_type IN ( 3, 4,14,15 )
AND task_status = \'1\'
GROUP BY
q.id
) i on i.id=tpi.id
#巡检完成率
LEFT JOIN (SELECT
q.id,
CONCAT( CONVERT (
(
COUNT(
IF
( sub_status = \'1\' OR sub_status = \'2\', 1, NULL ))/ COUNT(
ts.sub_id))* 100,
DECIMAL ( 10, 2 )),\'%\') inspectFinishRate,
COUNT(
IF
( sub_status = \'1\' OR sub_status = \'2\', 1, NULL )) inspectNum
FROM
project.t_subtask ts
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id ) AND del_flag = \'1\' ) q on q.id=ts.project_id and ts.del_flag=\'1\' GROUP BY q.id
)p on p.id=tpi.id
#服务评价完成率
LEFT JOIN (SELECT
q.id,
CONCAT(CONVERT (
(
COUNT(
IF
( task_status = \'1\', 1, NULL ))/ COUNT(
IF
( task_status = \'1\' or task_status=\'0\', 1, NULL )))* 100,
DECIMAL ( 10, 2 )),\'%\') appraiseFinishRate
,COUNT(
IF
( task_status = \'1\', 1, NULL ))appraiseNum
FROM
project.t_emp_appraise tea
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id ) AND del_flag = \'1\' ) q on q.id=tea.project_id and tea.del_flag=\'1\' GROUP BY q.id
)z on z.id=tpi.id
#任务数
LEFT JOIN(
SELECT
q.id,
COUNT( tt.id ) reportNum
FROM
project.t_task tt
JOIN ( SELECT DISTINCT id, dept_id FROM project.t_project_info WHERE dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id ) AND del_flag = \'1\' ) q ON q.id = tt.project_id
AND tt.del_flag = \'1\'
AND task_type IN ( 8,9 )
AND task_status = \'1\'
GROUP BY
q.id
) j on j.id=tpi.id
#访谈数
LEFT JOIN (SELECT
q.id,
COUNT(tri.id) interviewNum
FROM
employee.`t_risk_interview` tri
JOIN ( SELECT DISTINCT id, dept_id FROM project.`t_project_info` WHERE dept_id =(SELECT DISTINCT
a1.id
FROM
(
SELECT
t1.id
FROM
`fgbp-sca`.sys_dept t1
JOIN `fgbp-sca`.sys_dept_relation t2 ON ancestor = dept_id
WHERE
descendant = ( SELECT dept_id FROM `fgbp-sca`.sys_dept WHERE id =\'\"+deptId+\"\' )
) a1
JOIN ( SELECT DISTINCT id FROM `fgbp-sca`.sys_dept WHERE parent_id = \'EBE1EF50-E87A-41A9-881F-F023A50A4941\' AND del_flag = \'0\' ) a2 ON a1.id = a2.id ) AND del_flag = \'1\' ) q ON q.id = tri.project_id
AND tri.del_flag = \'1\' and tri.state=\'1\'
GROUP BY
q.id) x on x.id=tpi.id
GROUP BY
tpi.id,
tpi.cid,
tpi.project_name,
tpi.address,
tpi.manager_code,
tpi.manager_name, q.onlineNum, w.averDays,e.clientNum,r.netNum,t.praiseNum,u.articleNum,i.taskNum,
p.inspectFinishRate,j.reportNum,z.appraiseFinishRate,x.interviewNum ORDER BY tpi.update_time DESC\"))
}
1min目标场景问卷
立即参与
