Untitled
unknown
pgsql
2 years ago
9.1 kB
8
Indexable
SELECT
foo.*,
to_char(foo.start_date_time_execution, 'yyyy-mm-dd hh24:mi') AS start_date_time_execution,
to_char(foo.end_date_time_execution, 'yyyy-mm-dd hh24:mi') AS end_date_time_execution,
type_planning_situation(foo.end_date_time_execution, foo.states_category) AS planning_situation,
fc_amount_working_days(
ptm.last_log_in_phase_timestamp,
CASE WHEN foo.states_category IN ('FIN', 'REM')
THEN ptm.last_work_item_status_log
ELSE CURRENT_TIMESTAMP
END,
'N'
) AS amount_working_days,
alr.primary_day_number,
alr.secondary_day_number,
alr.tertiary_day_number
FROM(
SELECT
wok.id AS work_item_id,
wok.name,
wok.priority,
wok."itemTypeId" AS item_type_id,
wok.board_origin_id AS board_origin_id,
wok.person_id,
boa.name AS board_origin_name,
tea.id AS workspace_origin_id,
tea.name AS workspace_origin_name,
sht.id AS share_task_id,
sht."boardId" AS board_id,
its.column_id,
col.name AS column_name,
sht.state_id,
sg1.position AS group_position,
so1.position AS work_item_position,
wok."fatherId" AS father_id,
wf1.name AS father_work_item_name,
wf1."itemTypeId" AS father_item_type_id,
wif.name AS father_item_type_name,
wif.icon AS father_item_type_icon,
wif."backgroundColor" AS father_item_type_background_color,
if1.column_id AS father_column_id,
sf1.id AS share_task_father_id,
sf1."boardId" AS share_task_father_board_id,
wf1."fatherId" AS grand_father_id,
wf2.name AS grand_father_work_item_name,
wf2."itemTypeId" AS grand_father_item_type_id,
wig."name" AS grand_father_item_type_name,
wig."backgroundColor" AS grand_father_item_type_background_color,
wig.icon AS grand_father_item_type_icon,
if2.column_id AS grand_father_column_id,
sf2.id AS share_task_grand_father_id,
sf2."boardId" AS grand_father_board_id,
ito.states_category,
sht.end_date_time_execution,
sht.start_date_time_execution,
sht.directory_id,
COALESCE(lgs.last_update, to_date('dd/mm/yyyy', '01/01/1900')) as last_update
FROM
work_items wok
INNER JOIN share_tasks sht
ON wok.id = sht."taskId"
INNER JOIN items_types_states its
ON sht.state_id = its.state_id
AND wok."itemTypeId" = its.item_type_id
AND sht."boardId" = its.board_id
LEFT JOIN items_types_states_order ito
ON sht.state_id = ito.state_id
AND sht."boardId" = ito.board_id
AND wok."itemTypeId" = ito.item_type_id
INNER JOIN work_item_types wit
ON wok."itemTypeId" = wit.id
INNER JOIN boards boa
ON boa.id = wok.board_origin_id
INNER JOIN teams tea
ON tea.id = boa.workspace_id
LEFT JOIN work_items wf1
ON wok."fatherId" = wf1.id
AND wf1.deleted = false
LEFT JOIN work_item_types wif
ON wf1."itemTypeId" = wif.id
LEFT JOIN share_tasks sf1
ON wf1.id = sf1."taskId"
AND sf1.deleted = false
AND sf1."boardId" = (
SELECT
"boardId"
FROM
share_tasks
WHERE "taskId" = sf1."taskId"
ORDER BY
CASE WHEN "boardId" = sht."boardId"
THEN 0
ELSE "boardId"
END ASC
LIMIT 1
)
LEFT JOIN items_types_states if1
ON sf1.state_id = if1.state_id
AND wf1."itemTypeId" = if1.item_type_id
AND sf1."boardId" = if1.board_id
LEFT JOIN subitems_order so1
ON sf1.id = so1.share_task_father_id
AND sht."taskId" = so1.work_item_id
LEFT JOIN subitems_grp_itp_order sg1
ON sf1.id = sg1.share_task_father_id
AND wok."itemTypeId" = sg1.item_type_id
LEFT JOIN work_items wf2
ON wf1."fatherId" = wf2.id
AND wf2.deleted = false
LEFT JOIN work_item_types wig
ON wf2."itemTypeId" = wig.id
LEFT JOIN share_tasks sf2
ON wf2.id = sf2."taskId"
AND sf2.deleted = false
AND sf2."boardId" = (
SELECT
"boardId"
FROM
share_tasks
WHERE "taskId" = sf2."taskId"
ORDER BY
CASE WHEN "boardId" = sht."boardId"
THEN 0
ELSE "boardId"
END ASC
LIMIT 1
)
LEFT JOIN items_types_states if2
ON sf2.state_id = if2.state_id
AND wf2."itemTypeId" = if2.item_type_id
AND sf2."boardId" = if2.board_id
LEFT JOIN work_items wf3
ON wf2."fatherId" = wf3.id
AND wf3.deleted = false
LEFT JOIN share_tasks sf3
ON wf3.id = sf3."taskId"
AND sf3.deleted = false
AND sf3."boardId" = (
SELECT
"boardId"
FROM
share_tasks
WHERE "taskId" = sf3."taskId"
ORDER BY
CASE WHEN "boardId" = sht."boardId"
THEN 0
ELSE "boardId"
END ASC
LIMIT 1
)
LEFT JOIN items_types_states if3
ON sf3.state_id = if3.state_id
AND wf3."itemTypeId" = if3.item_type_id
AND sf3."boardId" = if3.board_id
LEFT JOIN columns col
ON its.column_id = col.id
AND col.deleted = false
LEFT JOIN (
SELECT
lgs."workItemId",
MAX(created_at) AS last_update
FROM
logs lgs
GROUP BY
lgs."workItemId"
) lgs
ON lgs."workItemId" = wok.id
WHERE wok.deleted = false
AND sht.deleted = false
AND wit.deleted = false
AND wok.record_type = 'WIT'
AND sht.situation = 'REL'
AND wf3.id IS NULL
AND sht."boardId" = 195
AND sht.id NOT IN (
SELECT
sht.id
FROM
work_items wok
INNER JOIN share_tasks sht
ON wok.id = sht."taskId"
INNER JOIN work_items wf1
ON wok."fatherId" = wf1.id
INNER JOIN work_items wf2
ON wf1."fatherId" = wf2.id
INNER JOIN work_items wf3
ON wf2."fatherId" = wf3.id
WHERE wf1.deleted = false
AND wf2.deleted = false
AND wf3.deleted = false
AND wok.deleted = false
AND wok."fatherId" IS NOT NULL
AND sht.deleted = false
AND sht."boardId" = 195
)
AND sht.id IN(
SELECT
sch.id
FROM
share_tasks sft
INNER JOIN work_items wft
ON sft."taskId" = wft.id
INNER JOIN work_items wch
ON wft.id = wch."fatherId"
INNER JOIN share_tasks sch
ON sch."taskId" = wch.id
WHERE sft.id IN (42073)
AND (
sft."boardId" = 195 OR
sht."boardId" = 195
)
UNION ALL
SELECT
sgc.id
FROM
share_tasks sft
INNER JOIN work_items wft
ON sft."taskId" = wft.id
INNER JOIN work_items wch
ON wft.id = wch."fatherId"
INNER JOIN work_items wgc
ON wch.id = wgc."fatherId"
INNER JOIN share_tasks sgc
ON sgc."taskId" = wgc.id
WHERE sft.id IN (42073)
UNION ALL
SELECT
sht.id
FROM
share_tasks sht
WHERE sht.id IN (42073)
AND sht.deleted = false
)
LIMIT 15000
) foo
LEFT JOIN oce_column_alert_rules alr
ON foo.column_id = alr.column_id
LEFT JOIN oce_processing_phase_time_sht ptm
ON foo.share_task_id = ptm.share_task_id
ORDER BY
foo.last_update DESCEditor is loading...