Untitled

mail@pastecode.io avatar
unknown
pgsql
8 months ago
9.1 kB
5
Indexable
Never
          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