Symfony Profiler

WITH
  kombinasi AS (
    SELECT
      s.sumber_dana,
      p.plant
    FROM
      (
        SELECT
          DISTINCT 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.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' AND t.kategori_tahap = 'Selesai (pemilihan penyedia)') 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.kategori_tahap = 'Selesai (pemilihan penyedia)'
          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.plant
  ),
  hari_kerja AS (
    SELECT
      sumber_dana,
      plant,
      COUNT(*) AS total_hari_kerja
    FROM
      (
        SELECT
          t.sumber_dana,
          t.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,
      plant
  )
SELECT
  k.sumber_dana,
  k.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.plant = k.plant
  LEFT JOIN hari_kerja h ON h.sumber_dana = k.sumber_dana
  AND h.plant = k.plant
ORDER BY
  k.sumber_dana,
  k.plant;

WITH 
    kombinasi AS (
        SELECT
            s.sumber_dana,
            p.plant
        FROM
            (SELECT DISTINCT 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.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' AND t.kategori_tahap = 'Selesai (pemilihan penyedia)') 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.kategori_tahap = 'Selesai (pemilihan penyedia)'  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.plant
    ),

    hari_kerja AS (
        SELECT
            sumber_dana,
            plant,
            COUNT(*) AS total_hari_kerja
        FROM (
            SELECT
                t.sumber_dana,
                t.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, plant
    )

SELECT
    k.sumber_dana,
    k.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.plant = k.plant
LEFT JOIN hari_kerja h
    ON h.sumber_dana = k.sumber_dana
   AND h.plant = k.plant
ORDER BY k.sumber_dana, k.plant;;

"COMMIT"

"COMMIT";

SELECT
  t0.id AS id_1,
  t0.kode AS kode_2,
  t0.kategori AS kategori_3,
  t0.nama AS nama_4,
  t0.regional AS regional_5
FROM
  m_plant t0
WHERE
  t0.nama = ?
LIMIT
  1

SELECT t0.id AS id_1, t0.kode AS kode_2, t0.kategori AS kategori_3, t0.nama AS nama_4, t0.regional AS regional_5 FROM m_plant t0 WHERE t0.nama = 'HO MKSO TEBU' LIMIT 1;

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 = '1f09ce3c-07e4-66d0-807a-9511b5b4e666';

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 = '1007085';

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 (30787, '2026-06-10 17:07:48', 'dashboard', 'dashboard_table_realisasi', '/backend/dashboard/table_realisasi', 'POST', 'table_realisasi', '1f09ce3c-07e4-66d0-807a-9511b5b4e666');

SELECT
  NEXTVAL('user_activity_id_seq')

SELECT NEXTVAL('user_activity_id_seq');

"START TRANSACTION"

"START TRANSACTION";

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

INSERT INTO tracking_page_view (id, url, created, session_id) VALUES (13595643, 'https://dashboard-sgn.appdev.web.id/backend/dashboard/table_realisasi?p=backend%2Fdashboard%2Ftable_realisasi', '2026-06-10 17:07:49', 1007085);

SELECT
  NEXTVAL('tracking_page_view_id_seq')

SELECT NEXTVAL('tracking_page_view_id_seq');