Symfony Profiler

WITH
  kombinasi AS (
    SELECT
      s.sumber_dana,
      p.kategori_plant
    FROM
      (
        SELECT
          DISTINCT kategori_plant
        FROM
          v_cleansing_pk_vendor
      ) p CROSS
      JOIN (
        SELECT
          UNNEST(
            ARRAY[ 'investasi', 'eksploitasi' ]
          ) AS sumber_dana
      ) s
  ),
  agregasi AS (
    SELECT
      t.sumber_dana,
      t.kategori_plant,
      -- COUNT(DISTINCT t.no_pr) FILTER (WHERE t.tahun_anggaran = '2026') AS total_pr,
      -- COUNT(DISTINCT t.no_pk) FILTER (WHERE t.tahun_anggaran = '2026') AS total_pk,
      -- COUNT(DISTINCT t.no_pp) FILTER (WHERE t.tahun_anggaran = '2026') AS total_pp,
      COUNT(DISTINCT t.no_pr) FILTER (
        WHERE
          t.tahun_anggaran = '2026'
          AND t.status_pr IN ('approve', 'submit')
      ) AS total_pr,
      COUNT(DISTINCT t.no_pk) FILTER (
        WHERE
          t.tahun_anggaran = '2026'
          AND t.status_pk IN (
            'draft', 'assign', 'pengadaan', 'submit'
          )
      ) AS total_pk,
      COUNT(DISTINCT t.no_pp) FILTER (
        WHERE
          t.tahun_anggaran = '2026'
          AND t.status_pp_ IN ('draft', 'laksanakan', 'finish')
      ) AS total_pp,
      COUNT(DISTINCT t.no_sppbj) FILTER (
        WHERE
          t.tahun_anggaran = '2026'
          AND t.kategori_tahap = 'Selesai (pemilihan penyedia)'
          AND t.no_sppbj IS NOT NULL
          AND t.no_sppbj != ''
      ) AS total_sppbj,
      COUNT(
        DISTINCT NULLIF(
          UPPER(
            TRIM(t.vendor_pemenang)
          ),
          ''
        )
      ) FILTER (
        WHERE
          t.tahun_anggaran = '2026'
          AND t.kategori_tahap = 'Selesai (pemilihan penyedia)'
      ) AS total_vendor_pemenang,
      SUM(
        CASE WHEN t.tahun_anggaran = '2026' THEN NULLIF(
          NULLIF(
            btrim(t.total_nilai_hasil :: text),
            ''
          ),
          '-'
        ):: numeric ELSE 0 END
      ) AS total_nilai,
      SUM(
        CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)'
        AND t.tahun_anggaran = '2026' THEN NULLIF(
          NULLIF(
            btrim(t.nilai_akhir_hasil :: text),
            ''
          ),
          '-'
        ):: numeric ELSE 0 END
      ) AS total_nilai_akhir,
      SUM(
        CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)'
        AND t.tahun_anggaran = '2026' THEN NULLIF(
          NULLIF(
            btrim(t.anggaran_hasil :: text),
            ''
          ),
          '-'
        ):: numeric ELSE 0 END
      ) AS total_anggaran,
      SUM(
        CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)'
        AND t.tahun_anggaran = '2026' THEN NULLIF(
          NULLIF(
            btrim(t.hps_hasil :: text),
            ''
          ),
          '-'
        ):: numeric ELSE 0 END
      ) AS total_hps,
      SUM(
        CASE WHEN t.kategori_tahap = 'Tahap penyusunan hps'
        AND t.tahun_anggaran = '2026' THEN NULLIF(
          NULLIF(
            btrim(t.total_nilai_hasil :: text),
            ''
          ),
          '-'
        ):: numeric ELSE 0 END
      ) AS nilai_penyusunan_hps,
      SUM(
        CASE WHEN t.kategori_tahap IN (
          'Dalam proses (pemilihan penyedia)',
          'Selesai (pemilihan penyedia)'
        )
        AND t.tahun_anggaran = '2026' THEN NULLIF(
          NULLIF(
            btrim(t.total_nilai_hasil :: text),
            ''
          ),
          '-'
        ):: numeric ELSE 0 END
      ) AS nilai_pemilihan_penyedia,
      COUNT(DISTINCT t.no_pk) FILTER (
        WHERE
          t.kategori_tahap = 'Tahap pembuatan paket'
          AND t.tahun_anggaran = '2026'
      ) AS tahap_pembuatan_paket,
      COUNT(DISTINCT t.no_pk) FILTER (
        WHERE
          t.kategori_tahap = 'Tahap penyusunan hps'
          AND t.tahun_anggaran = '2026'
      ) AS tahap_penyusunan_hps,
      COUNT(DISTINCT t.no_pk) FILTER (
        WHERE
          t.kategori_tahap = 'Dalam proses (pemilihan penyedia)'
          AND t.tahun_anggaran = '2026'
      ) AS dalam_proses,
      COUNT(DISTINCT t.no_pk) FILTER (
        WHERE
          t.kategori_tahap = 'Selesai (pemilihan penyedia)'
          AND t.tahun_anggaran = '2026'
      ) AS selesai
    FROM
      v_cleansing_pk_vendor t
    WHERE
      t.tahun_anggaran IS NOT NULL
      AND t.sumber_dana IS NOT NULL
    GROUP BY
      t.sumber_dana,
      t.kategori_plant
  ),
  hari_kerja AS (
    SELECT
      sumber_dana,
      kategori_plant,
      COUNT(*) AS total_hari_kerja
    FROM
      (
        SELECT
          t.sumber_dana,
          t.kategori_plant,
          generate_series(
            t.tgl_submit_ke_pengadaan :: date,
            CURRENT_DATE, INTERVAL '1 day'
          ):: date AS tanggal
        FROM
          v_cleansing_pk_vendor t
        WHERE
          t.kategori_tahap = 'Dalam proses (pemilihan penyedia)'
      ) tanggalan
      LEFT JOIN m_libur l ON tanggalan.tanggal = l.tanggal :: date
    WHERE
      EXTRACT(
        DOW
        FROM
          tanggalan.tanggal
      ) NOT IN (0, 6)
      AND l.tanggal IS NULL
    GROUP BY
      sumber_dana,
      kategori_plant
  )
SELECT
  k.sumber_dana,
  k.kategori_plant,
  COALESCE(a.total_vendor_pemenang, 0) AS total_vendor_pemenang,
  COALESCE(a.total_pk, 0) AS total_pk,
  COALESCE(a.total_pr, 0) AS total_pr,
  COALESCE(a.total_pp, 0) AS total_pp,
  COALESCE(a.total_nilai, 0) AS total_nilai,
  COALESCE(a.total_nilai_akhir, 0) AS total_nilai_akhir,
  COALESCE(a.total_anggaran, 0) AS total_anggaran,
  COALESCE(a.total_hps, 0) AS total_hps,
  COALESCE(a.nilai_penyusunan_hps, 0) AS nilai_penyusunan_hps,
  COALESCE(a.nilai_pemilihan_penyedia, 0) AS nilai_pemilihan_penyedia,
  COALESCE(a.tahap_pembuatan_paket, 0) AS tahap_pembuatan_paket,
  COALESCE(a.tahap_penyusunan_hps, 0) AS tahap_penyusunan_hps,
  COALESCE(a.dalam_proses, 0) AS dalam_proses,
  COALESCE(a.selesai, 0) AS selesai,
  COALESCE(h.total_hari_kerja, 0) AS total_hari_kerja
FROM
  kombinasi k
  LEFT JOIN agregasi a ON a.sumber_dana = k.sumber_dana
  AND a.kategori_plant = k.kategori_plant
  LEFT JOIN hari_kerja h ON h.sumber_dana = k.sumber_dana
  AND h.kategori_plant = k.kategori_plant
ORDER BY
  k.sumber_dana,
  k.kategori_plant;

WITH 
    kombinasi AS (
        SELECT
            s.sumber_dana,
            p.kategori_plant
        FROM
            (SELECT DISTINCT kategori_plant FROM v_cleansing_pk_vendor ) p 
        CROSS JOIN
            (SELECT UNNEST(ARRAY['investasi', 'eksploitasi']) AS sumber_dana) s
    ),

    agregasi AS (
        SELECT
            t.sumber_dana,
            t.kategori_plant,

            -- COUNT(DISTINCT t.no_pr) FILTER (WHERE t.tahun_anggaran = '2026') AS total_pr,
            -- COUNT(DISTINCT t.no_pk) FILTER (WHERE t.tahun_anggaran = '2026') AS total_pk,
            -- COUNT(DISTINCT t.no_pp) FILTER (WHERE t.tahun_anggaran = '2026') AS total_pp,
            COUNT(DISTINCT t.no_pr) FILTER (WHERE t.tahun_anggaran = '2026' AND t.status_pr IN ('approve','submit')) AS total_pr,
            COUNT(DISTINCT t.no_pk) FILTER (WHERE t.tahun_anggaran = '2026'  AND t.status_pk IN ('draft','assign','pengadaan','submit')) AS total_pk,
            COUNT(DISTINCT t.no_pp) FILTER (WHERE t.tahun_anggaran = '2026' AND t.status_pp_ IN ('draft','laksanakan','finish') ) AS total_pp,
            COUNT(DISTINCT t.no_sppbj) FILTER (WHERE t.tahun_anggaran = '2026' AND t.kategori_tahap = 'Selesai (pemilihan penyedia)' AND t.no_sppbj IS NOT NULL AND t.no_sppbj != '') AS total_sppbj,
            COUNT(DISTINCT NULLIF(UPPER(TRIM(t.vendor_pemenang)), '')) FILTER (WHERE t.tahun_anggaran = '2026' AND t.kategori_tahap = 'Selesai (pemilihan penyedia)') AS total_vendor_pemenang,
            
            SUM(CASE WHEN t.tahun_anggaran = '2026'
                     THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric 
                     ELSE 0 END) AS total_nilai,

            SUM(CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)' 
                     AND t.tahun_anggaran = '2026'
                     THEN NULLIF(NULLIF(btrim(t.nilai_akhir_hasil::text), ''), '-')::numeric 
                     ELSE 0 END) AS total_nilai_akhir,

            SUM(CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)' 
                     AND  t.tahun_anggaran = '2026'
                     THEN NULLIF(NULLIF(btrim(t.anggaran_hasil::text), ''), '-')::numeric 
                     ELSE 0 END) AS total_anggaran,

            SUM(CASE WHEN t.kategori_tahap = 'Selesai (pemilihan penyedia)'
                     AND t.tahun_anggaran = '2026'
                     THEN NULLIF(NULLIF(btrim(t.hps_hasil::text), ''), '-')::numeric 
                     ELSE 0 END) AS total_hps,

            SUM(CASE WHEN t.kategori_tahap = 'Tahap penyusunan hps'
                     AND t.tahun_anggaran = '2026'
                     THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric 
                     ELSE 0 END) AS nilai_penyusunan_hps,

            SUM(CASE WHEN t.kategori_tahap IN ('Dalam proses (pemilihan penyedia)', 'Selesai (pemilihan penyedia)')
                     AND t.tahun_anggaran = '2026'
                     THEN NULLIF(NULLIF(btrim(t.total_nilai_hasil::text), ''), '-')::numeric 
                     ELSE 0 END) AS nilai_pemilihan_penyedia,

            COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Tahap pembuatan paket' AND t.tahun_anggaran = '2026') AS tahap_pembuatan_paket,
            COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Tahap penyusunan hps' AND t.tahun_anggaran = '2026') AS tahap_penyusunan_hps,
            COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Dalam proses (pemilihan penyedia)' AND t.tahun_anggaran = '2026') AS dalam_proses,
            COUNT(DISTINCT t.no_pk) FILTER (WHERE t.kategori_tahap = 'Selesai (pemilihan penyedia)' AND t.tahun_anggaran = '2026') AS selesai

        FROM v_cleansing_pk_vendor t
        WHERE t.tahun_anggaran IS NOT NULL 
          AND t.sumber_dana IS NOT NULL
        GROUP BY t.sumber_dana, t.kategori_plant
    ),

    hari_kerja AS (
        SELECT
            sumber_dana,
            kategori_plant,
            COUNT(*) AS total_hari_kerja
        FROM (
            SELECT
                t.sumber_dana,
                t.kategori_plant,
                generate_series(
                    t.tgl_submit_ke_pengadaan::date,
                    CURRENT_DATE,
                    INTERVAL '1 day'
                )::date AS tanggal
            FROM v_cleansing_pk_vendor t 
            WHERE t.kategori_tahap = 'Dalam proses (pemilihan penyedia)'
        ) tanggalan
        LEFT JOIN m_libur l ON tanggalan.tanggal = l.tanggal::date
        WHERE EXTRACT(DOW FROM tanggalan.tanggal) NOT IN (0, 6)
          AND l.tanggal IS NULL
        GROUP BY sumber_dana, kategori_plant
    )

SELECT
    k.sumber_dana,
    k.kategori_plant,
    COALESCE(a.total_vendor_pemenang, 0) AS total_vendor_pemenang,
    COALESCE(a.total_pk, 0) AS total_pk,
    COALESCE(a.total_pr, 0) AS total_pr,
    COALESCE(a.total_pp, 0) AS total_pp,
    COALESCE(a.total_nilai, 0) AS total_nilai,
    COALESCE(a.total_nilai_akhir, 0) AS total_nilai_akhir,
    COALESCE(a.total_anggaran, 0) AS total_anggaran,
    COALESCE(a.total_hps, 0) AS total_hps,
    COALESCE(a.nilai_penyusunan_hps, 0) AS nilai_penyusunan_hps,
    COALESCE(a.nilai_pemilihan_penyedia, 0) AS nilai_pemilihan_penyedia,
    COALESCE(a.tahap_pembuatan_paket, 0) AS tahap_pembuatan_paket,
    COALESCE(a.tahap_penyusunan_hps, 0) AS tahap_penyusunan_hps,
    COALESCE(a.dalam_proses, 0) AS dalam_proses,
    COALESCE(a.selesai, 0) AS selesai,
    COALESCE(h.total_hari_kerja, 0) AS total_hari_kerja
FROM kombinasi k
LEFT JOIN agregasi a 
    ON a.sumber_dana = k.sumber_dana 
   AND a.kategori_plant = k.kategori_plant
LEFT JOIN hari_kerja h
    ON h.sumber_dana = k.sumber_dana
   AND h.kategori_plant = k.kategori_plant
ORDER BY k.sumber_dana, k.kategori_plant;;

"COMMIT"

"COMMIT";

SELECT
  t0_.id AS id_0,
  t0_.head_1 AS head_1_1,
  t0_.head_2 AS head_2_2,
  t0_.isi AS isi_3,
  t0_.file AS file_4,
  t0_.created_at AS created_at_5,
  t0_.updated_at AS updated_at_6,
  t0_.created_by AS created_by_7,
  t0_.header_id AS header_id_8,
  t0_.tahun_anggaran AS tahun_anggaran_9,
  t0_.periode_upload AS periode_upload_10
FROM
  t_raw_data t0_
WHERE
  t0_.updated_at IS NOT NULL
ORDER BY
  t0_.updated_at DESC
LIMIT
  1

SELECT t0_.id AS id_0, t0_.head_1 AS head_1_1, t0_.head_2 AS head_2_2, t0_.isi AS isi_3, t0_.file AS file_4, t0_.created_at AS created_at_5, t0_.updated_at AS updated_at_6, t0_.created_by AS created_by_7, t0_.header_id AS header_id_8, t0_.tahun_anggaran AS tahun_anggaran_9, t0_.periode_upload AS periode_upload_10 FROM t_raw_data t0_ WHERE t0_.updated_at IS NOT NULL ORDER BY t0_.updated_at DESC LIMIT 1;

SELECT
  p0_.id AS id_0,
  p0_.module AS module_1,
  p0_.url AS url_2,
  p0_.icon AS icon_3,
  p1_.id AS id_4,
  p2_.is_allowed AS is_allowed_5
FROM
  pti_module p0_
  INNER JOIN pti_module_action p1_ ON p0_.id = p1_.module_id
  INNER JOIN pti_rule p2_ ON p1_.id = p2_.module_action_id
  INNER JOIN pti_group p3_ ON p2_.groups_id = p3_.id
WHERE
  p0_.parent = ?
  AND p1_.action = ?
  AND p3_.credential = ?
ORDER BY
  p0_.id ASC

SELECT p0_.id AS id_0, p0_.module AS module_1, p0_.url AS url_2, p0_.icon AS icon_3, p1_.id AS id_4, p2_.is_allowed AS is_allowed_5 FROM pti_module p0_ INNER JOIN pti_module_action p1_ ON p0_.id = p1_.module_id INNER JOIN pti_rule p2_ ON p1_.id = p2_.module_action_id INNER JOIN pti_group p3_ ON p2_.groups_id = p3_.id WHERE p0_.parent = 5 AND p1_.action = 'index' AND p3_.credential = 'ROLE_ADMINISTRATOR' ORDER BY p0_.id ASC;

SELECT
  p0_.id AS id_0,
  p0_.module AS module_1,
  p0_.icon AS icon_2,
  p1_.id AS id_3,
  p2_.is_allowed AS is_allowed_4
FROM
  pti_module p0_
  INNER JOIN pti_module_action p1_ ON p0_.id = p1_.module_id
  INNER JOIN pti_rule p2_ ON p1_.id = p2_.module_action_id
  INNER JOIN pti_group p3_ ON p2_.groups_id = p3_.id
WHERE
  p0_.parent = 0
  AND p1_.action = ?
  AND p3_.credential = ?
ORDER BY
  p0_.id ASC

SELECT p0_.id AS id_0, p0_.module AS module_1, p0_.icon AS icon_2, p1_.id AS id_3, p2_.is_allowed AS is_allowed_4 FROM pti_module p0_ INNER JOIN pti_module_action p1_ ON p0_.id = p1_.module_id INNER JOIN pti_rule p2_ ON p1_.id = p2_.module_action_id INNER JOIN pti_group p3_ ON p2_.groups_id = p3_.id WHERE p0_.parent = 0 AND p1_.action = 'index' AND p3_.credential = 'ROLE_ADMINISTRATOR' ORDER BY p0_.id ASC;

SELECT
  t0.id AS id_1,
  t0.reff_name AS reff_name_2,
  t0.reff_id AS reff_id_3,
  t0.deskripsi AS deskripsi_4,
  t0.created_by AS created_by_5,
  t0.created_at AS created_at_6,
  t0.updated_at AS updated_at_7,
  t0.other_attr AS other_attr_8,
  t0.div_id AS div_id_9
FROM
  t_log t0
ORDER BY
  t0.reff_id DESC
LIMIT
  5

SELECT t0.id AS id_1, t0.reff_name AS reff_name_2, t0.reff_id AS reff_id_3, t0.deskripsi AS deskripsi_4, t0.created_by AS created_by_5, t0.created_at AS created_at_6, t0.updated_at AS updated_at_7, t0.other_attr AS other_attr_8, t0.div_id AS div_id_9 FROM t_log t0 ORDER BY t0.reff_id DESC LIMIT 5;

SELECT
  t0.id AS id_1,
  t0.ip AS ip_2,
  t0.referrer AS referrer_3,
  t0.user_agent AS user_agent_4,
  t0.query_string AS query_string_5,
  t0.utm_source AS utm_source_6,
  t0.utm_medium AS utm_medium_7,
  t0.utm_campaign AS utm_campaign_8,
  t0.utm_term AS utm_term_9,
  t0.utm_content AS utm_content_10,
  t0.loan_term AS loan_term_11,
  t0.rep_apr AS rep_apr_12,
  t0.created AS created_13,
  t0.lifetime_id AS lifetime_id_14
FROM
  SESSION t0
WHERE
  t0.id = ?

SELECT t0.id AS id_1, t0.ip AS ip_2, t0.referrer AS referrer_3, t0.user_agent AS user_agent_4, t0.query_string AS query_string_5, t0.utm_source AS utm_source_6, t0.utm_medium AS utm_medium_7, t0.utm_campaign AS utm_campaign_8, t0.utm_term AS utm_term_9, t0.utm_content AS utm_content_10, t0.loan_term AS loan_term_11, t0.rep_apr AS rep_apr_12, t0.created AS created_13, t0.lifetime_id AS lifetime_id_14 FROM SESSION t0 WHERE t0.id = '695126';

SELECT
  t0.id AS id_3,
  t0.username AS username_4,
  t0.name AS name_5,
  t0.password AS password_6,
  t0.roles AS roles_7,
  t0.is_active AS is_active_8,
  t0.type,
  t1.email AS email_9,
  t1.telp AS telp_10,
  t1.login AS login_11,
  t1.last_login AS last_login_12,
  t1.user_agent AS user_agent_13,
  t1.div_akses AS div_akses_14,
  t1.kota_id AS kota_id_15,
  t1.div_id AS div_id_16
FROM
  kmj_user t0
  LEFT JOIN m_user t1 ON t0.id = t1.id
  LEFT JOIN kmj_default_user t2 ON t0.id = t2.id
WHERE
  t0.id = ?

SELECT t0.id AS id_3, t0.username AS username_4, t0.name AS name_5, t0.password AS password_6, t0.roles AS roles_7, t0.is_active AS is_active_8, t0.type, t1.email AS email_9, t1.telp AS telp_10, t1.login AS login_11, t1.last_login AS last_login_12, t1.user_agent AS user_agent_13, t1.div_akses AS div_akses_14, t1.kota_id AS kota_id_15, t1.div_id AS div_id_16 FROM kmj_user t0 LEFT JOIN m_user t1 ON t0.id = t1.id LEFT JOIN kmj_default_user t2 ON t0.id = t2.id WHERE t0.id = '1ed35617-b8d3-68ca-9889-992233484e41';

INSERT INTO user_activity (
  id, created_at, name, route, path, method,
  description, person_id
)
VALUES
  (?, ?, ?, ?, ?, ?, ?, ?)

INSERT INTO user_activity (id, created_at, name, route, path, method, description, person_id) VALUES (30771, '2026-04-09 13:21:46', 'dash_nilai_paket_pengadaan', 'dash_nilai_paket_pengadaan', '/backend/dashboard/dash_nilai_paket_pengadaan', 'GET', 'dash_nilai_paket_pengadaan', '1ed35617-b8d3-68ca-9889-992233484e41');

INSERT INTO tracking_page_view (id, url, created, session_id)
VALUES
  (?, ?, ?, ?)

INSERT INTO tracking_page_view (id, url, created, session_id) VALUES (7173385, 'https://dashboard-sgn.appdev.web.id/backend/dashboard/dash_nilai_paket_pengadaan?p=backend%2Fdashboard%2Fdash_nilai_paket_pengadaan', '2026-04-09 13:21:46', 695126);

"START TRANSACTION"

"START TRANSACTION";

SELECT
  NEXTVAL('user_activity_id_seq')

SELECT NEXTVAL('user_activity_id_seq');

SELECT
  NEXTVAL('tracking_page_view_id_seq')

SELECT NEXTVAL('tracking_page_view_id_seq');