SELECT NAME
,
taskname,
taskstartdate,
manager,
startdate,
completiondate,
enddate,
projectid,
project_relation_type,
departmentid,
requester,
DAY,
create_time,
taskid,
projecttypename,
projectnumber,
progress,
rapiddegree
FROM
(
SELECT NAME
,
taskname,
taskstartdate,
manager,
startdate,
completiondate,
enddate,
projectid,
project_relation_type,
departmentid,
requester,
DAY,
create_time,
taskid,
projecttypename,
projectnumber,
progress,
rapiddegree
FROM
(
SELECT MAX
( t1.NAME ) AS NAME,
CASE
WHEN MAX ( t3.end_time_ ) IS NOT NULL
AND MAX ( t3.name_ ) = '歸檔' THEN
'已結束' ELSE MAX ( t3.name_ )
END AS taskName,
MAX ( t3.start_time_ ) AS taskStartDate,
MAX ( t5.realname ) AS manager,
to_char( t1.start_date, 'yyyy-MM' ) AS startDate,
to_char( t1.completion_date, 'yyyy-MM-dd' ) AS completionDate,
CASE
WHEN t1.end_date IS NULL THEN
to_char( CURRENT_DATE, 'yyyy-MM' ) ELSE to_char( t1.end_date, 'yyyy-MM' )
END AS endDate,
t1.uuid AS projectId,
t1.project_relation_type,
MAX ( e.department_id ) AS departmentId,
MAX ( t10.NAME ) AS requester,
CURRENT_DATE - t1.start_date AS DAY,
MAX ( t1.create_time ) AS create_time,
MAX ( t3.id_ ) AS taskId,
MAX ( t13.NAME ) AS projectTypeName,
t1.project_number AS projectNumber,
MAX (
COALESCE ( t14.percentage, '0' )) AS progress,
MAX ( t1.rapid_degree ) AS rapidDegree
FROM
t_project t1
INNER JOIN act_hi_procinst t2 ON t2.business_key_ = t1.uuid
INNER JOIN act_hi_taskinst t3 ON t3.start_time_ IN (
SELECT MAX
( t4.start_time_ )
FROM
act_hi_taskinst t4
INNER JOIN t_user t5 ON t5.uuid = t4.assignee_
WHERE
t5.department = '59358597482a45bba4f049998474ab31'
GROUP BY
t4.proc_inst_id_
)
AND t3.proc_inst_id_ = t2.id_
LEFT JOIN t_user t5 ON t5.uuid = t1.manager
LEFT JOIN t_customer t10 ON t10.uuid = t1.requester
LEFT JOIN t_project_type t13 ON t13.uuid = t1.project_type
LEFT JOIN t_project_progress t14 ON t14.project_id = t1.uuid
AND t14.department_id = '59358597482a45bba4f049998474ab31'
AND (
t14.DATE IN ( SELECT MAX ( DATE ) FROM t_project_progress t15 WHERE t15.project_id = t1.uuid AND t15.department_id = '59358597482a45bba4f049998474ab31' ))
LEFT JOIN ( SELECT * FROM t_project_effect t16 WHERE t16.department_id = '59358597482a45bba4f049998474ab31' ) e ON e.project_id = t1.uuid
GROUP BY
t1.uuid
ORDER BY
t1.create_time DESC
) b
WHERE
1 = 1
AND b.taskName IN ( '所長接收并指派', '負責人接收', '專案實施', '一檢' ) UNION ALL
SELECT
*
FROM
(
SELECT MAX
( t1.NAME ) AS NAME,
CASE
WHEN MAX ( t3.end_time_ ) IS NOT NULL
AND MAX ( t3.name_ ) = '歸檔' THEN
'已結束' ELSE MAX ( t3.name_ )
END AS taskName,
MAX ( t3.start_time_ ) AS taskStartDate,
MAX ( t5.realname ) AS manager,
to_char( t1.start_date, 'yyyy-MM' ) AS startDate,
to_char( t1.completion_date, 'yyyy-MM-dd' ) AS completionDate,
CASE
WHEN t1.end_date IS NULL THEN
to_char( CURRENT_DATE, 'yyyy-MM' ) ELSE to_char( t1.end_date, 'yyyy-MM' )
END AS endDate,
t1.uuid AS projectId,
t1.project_relation_type,
MAX ( e.department_id ) AS departmentId,
MAX ( t10.NAME ) AS requester,
CURRENT_DATE - t1.start_date AS DAY,
MAX ( t1.create_time ) AS create_time,
MAX ( t3.id_ ) AS taskId,
MAX ( t13.NAME ) AS projectTypeName,
t1.project_number AS projectNumber,
MAX (
COALESCE ( t14.percentage, '0' )) AS progress,
MAX ( t1.rapid_degree ) AS rapidDegree
FROM
t_project t1
INNER JOIN act_hi_procinst t2 ON t2.business_key_ = t1.uuid
INNER JOIN act_hi_taskinst t3 ON t3.start_time_ IN (
SELECT MAX
( t4.start_time_ )
FROM
act_hi_taskinst t4
INNER JOIN t_user t5 ON t5.uuid = t4.assignee_
GROUP BY
t4.proc_inst_id_
)
AND t3.proc_inst_id_ = t2.id_
LEFT JOIN t_user t5 ON t5.uuid = t1.manager
LEFT JOIN t_customer t10 ON t10.uuid = t1.requester
LEFT JOIN t_project_type t13 ON t13.uuid = t1.project_type
LEFT JOIN t_project_progress t14 ON t14.project_id = t1.uuid
AND t14.department_id = '59358597482a45bba4f049998474ab31'
AND (
t14.DATE IN ( SELECT MAX ( DATE ) FROM t_project_progress t15 WHERE t15.project_id = t1.uuid AND t15.department_id = '59358597482a45bba4f049998474ab31' ))
LEFT JOIN ( SELECT * FROM t_project_effect t16 WHERE t16.department_id = '59358597482a45bba4f049998474ab31' ) e ON e.project_id = t1.uuid
GROUP BY
t1.uuid
ORDER BY
t1.create_time DESC
) b
WHERE
1 = 1
AND b.taskName NOT IN ( '所長接收并指派', '負責人接收', '專案實施', '一檢' )
) d
WHERE
1 = 1
AND d.departmentId = '59358597482a45bba4f049998474ab31'
AND d.taskStartDate = (
SELECT MAX
( f.taskStartDate )
FROM
(
SELECT NAME
,
taskname,
manager,
startdate,
completiondate,
enddate,
projectid,
project_relation_type,
departmentid,
requester,
DAY,
create_time,
taskid,
taskstartdate,
projecttypename,
projectnumber,
progress,
rapiddegree
FROM
(
SELECT MAX
( t1.NAME ) AS NAME,
CASE
WHEN MAX ( t3.end_time_ ) IS NOT NULL
AND MAX ( t3.name_ ) = '歸檔' THEN
'已結束' ELSE MAX ( t3.name_ )
END AS taskName,
MAX ( t5.realname ) AS manager,
to_char( t1.start_date, 'yyyy-MM' ) AS startDate,
to_char( t1.completion_date, 'yyyy-MM-dd' ) AS completionDate,
CASE
WHEN t1.end_date IS NULL THEN
to_char( CURRENT_DATE, 'yyyy-MM' ) ELSE to_char( t1.end_date, 'yyyy-MM' )
END AS endDate,
t1.uuid AS projectId,
t1.project_relation_type,
MAX ( e.department_id ) AS departmentId,
MAX ( t10.NAME ) AS requester,
CURRENT_DATE - t1.start_date AS DAY,
MAX ( t1.create_time ) AS create_time,
MAX ( t3.id_ ) AS taskId,
MAX ( t3.start_time_ ) AS taskStartDate,
MAX ( t13.NAME ) AS projectTypeName,
t1.project_number AS projectNumber,
MAX (
COALESCE ( t14.percentage, '0' )) AS progress,
MAX ( t1.rapid_degree ) AS rapidDegree
FROM
t_project t1
INNER JOIN act_hi_procinst t2 ON t2.business_key_ = t1.uuid
INNER JOIN act_hi_taskinst t3 ON t3.start_time_ IN (
SELECT MAX
( t4.start_time_ )
FROM
act_hi_taskinst t4
INNER JOIN t_user t5 ON t5.uuid = t4.assignee_
WHERE
t5.department = '59358597482a45bba4f049998474ab31'
GROUP BY
t4.proc_inst_id_
)
AND t3.proc_inst_id_ = t2.id_
LEFT JOIN t_user t5 ON t5.uuid = t1.manager
LEFT JOIN t_customer t10 ON t10.uuid = t1.requester
LEFT JOIN t_project_type t13 ON t13.uuid = t1.project_type
LEFT JOIN t_project_progress t14 ON t14.project_id = t1.uuid
AND t14.department_id = '59358597482a45bba4f049998474ab31'
AND (
t14.DATE IN ( SELECT MAX ( DATE ) FROM t_project_progress t15 WHERE t15.project_id = t1.uuid AND t15.department_id = '59358597482a45bba4f049998474ab31' ))
LEFT JOIN ( SELECT * FROM t_project_effect t16 WHERE t16.department_id = '59358597482a45bba4f049998474ab31' ) e ON e.project_id = t1.uuid
GROUP BY
t1.uuid
ORDER BY
t1.create_time DESC
) b
WHERE
b.taskName IN ( '所長接收并指派', '負責人接收', '專案實施', '一檢' ) UNION ALL
SELECT
*
FROM
(
SELECT MAX
( t1.NAME ) AS NAME,
CASE
WHEN MAX ( t3.end_time_ ) IS NOT NULL
AND MAX ( t3.name_ ) = '歸檔' THEN
'已結束' ELSE MAX ( t3.name_ )
END AS taskName,
MAX ( t5.realname ) AS manager,
to_char( t1.start_date, 'yyyy-MM-dd' ) AS startDate,
to_char( t1.completion_date, 'yyyy-MM-dd' ) AS completionDate,
CASE
WHEN t1.end_date IS NULL THEN
to_char( CURRENT_DATE, 'yyyy-MM' ) ELSE to_char( t1.end_date, 'yyyy-MM-dd' )
END AS endDate,
t1.uuid AS projectId,
t1.project_relation_type,
MAX ( e.department_id ) AS departmentId,
MAX ( t10.NAME ) AS requester,
CURRENT_DATE - t1.start_date AS DAY,
MAX ( t1.create_time ) AS create_time,
MAX ( t3.id_ ) AS taskId,
MAX ( t3.start_time_ ) AS taskStartDate,
MAX ( t13.NAME ) AS projectTypeName,
t1.project_number AS projectNumber,
MAX (
COALESCE ( t14.percentage, '0' )) AS progress,
MAX ( t1.rapid_degree ) AS rapidDegree
FROM
t_project t1
INNER JOIN act_hi_procinst t2 ON t2.business_key_ = t1.uuid
INNER JOIN act_hi_taskinst t3 ON t3.start_time_ IN (
SELECT MAX
( t4.start_time_ )
FROM
act_hi_taskinst t4
INNER JOIN t_user t5 ON t5.uuid = t4.assignee_
GROUP BY
t4.proc_inst_id_
)
AND t3.proc_inst_id_ = t2.id_
LEFT JOIN t_user t5 ON t5.uuid = t1.manager
LEFT JOIN t_customer t10 ON t10.uuid = t1.requester
LEFT JOIN t_project_type t13 ON t13.uuid = t1.project_type
LEFT JOIN t_project_progress t14 ON t14.project_id = t1.uuid
AND t14.department_id = '59358597482a45bba4f049998474ab31'
AND (
t14.DATE IN ( SELECT MAX ( DATE ) FROM t_project_progress t15 WHERE t15.project_id = t1.uuid AND t15.department_id = '59358597482a45bba4f049998474ab31' ))
LEFT JOIN ( SELECT department_id, project_id FROM t_project_effect t16 WHERE t16.department_id = '59358597482a45bba4f049998474ab31' ) e ON e.project_id = t1.uuid
GROUP BY
t1.uuid
ORDER BY
t1.create_time DESC
) b
WHERE
b.taskName NOT IN ( '所長接收并指派', '負責人接收', '專案實施', '一檢' )
) f
WHERE
f.departmentId = '59358597482a45bba4f049998474ab31'
AND d.projectId = f.projectId
)
-- LIMIT 10 offset 0;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/270063.html
標籤：PostgreSQL