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 DESC
Editor is loading...