Query Stok Detail

select json_agg(foo) from (
    SELECT
        kode_item,
        nama_item,
        satuan,
        wac,
        SUM ( awal ) as awal,
        SUM ( awal_rupiah ) as awal_rupiah,
        SUM (masuk) as masuk,
        SUM (masuk_rupiah) as masuk_rupiah,
        SUM ( keluar ) as keluar,
        SUM ( keluar_rupiah ) as keluar_rupiah,
        SUM (adj_plus) as adj_plus,
        SUM (adj_plus_rupiah) as adj_plus_rupiah,
        SUM (adj_minus) as adj_minus,
        SUM (adj_minus_rupiah) as adj_minus_rupiah,
        SUM ( akhir ) as akhir,
        SUM ( akhir_rupiah ) as akhir_rupiah
FROM
        (
            SELECT
            item.kode_item,
            item.nama_item,
            (
            SELECT
                nama_satuan
            FROM
                item_satuan
                INNER JOIN satuan ON satuan.id_satuan = item_satuan.id_satuan
            WHERE
                item_satuan.id_item = item.id_item
                AND parent_id_item_satuan IS NULL
            ) AS satuan,
            item_gudang_setup.min_stok,
            item_gudang_setup.max_stok,
            item.wac,
            (
            SELECT
                jumlah_setelah_transaksi
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi < '2020-06-01 00:00:00'
            ORDER BY
                item_transaksi.waktu_transaksi DESC
                LIMIT 1
            ) AS awal,
            (
            SELECT
                jumlah_setelah_transaksi * harga_wac
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi < '2020-06-01 00:00:00'
            ORDER BY
                item_transaksi.waktu_transaksi DESC
                LIMIT 1
            ) AS awal_rupiah,
            (
            SELECT COALESCE
                ( SUM ( jumlah_transaksi ), 0 )
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                AND '2020-07-16 23:59:59'
                AND item_transaksi.id_jenis_item_transaksi IN ( 1, 12, 4, 39, 3 )
                AND item_transaksi.is_delete = FALSE
            ) AS masuk,
            (
            SELECT SUM
                ( transaksi )
            FROM
                (
                    (
                    SELECT COALESCE
                        (
                        CASE

                                WHEN item_transaksi.id_jenis_item_transaksi = 1 THEN
                                COALESCE ( SUM ( jumlah_transaksi * harga_transaksi ), 0 ) ELSE COALESCE ( SUM ( jumlah_transaksi * harga_wac ), 0 )
                            END,
                            0
                        ) AS transaksi
                    FROM
                        daftar_item_transaksi
                        INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
                    WHERE
                        daftar_item_transaksi.id_item = item.id_item
                        AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                        AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                        AND '2020-07-16 23:59:59'
                        AND item_transaksi.id_jenis_item_transaksi IN ( 1, 12, 4, 39, 3 )
                        AND item_transaksi.is_delete = FALSE
                    GROUP BY
                        item_transaksi.id_jenis_item_transaksi
                    )
                ) foo
            ) AS masuk_rupiah,
            (
            SELECT COALESCE
                ( SUM ( jumlah_transaksi ), 0 )
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                AND '2020-07-16 23:59:59'
                AND item_transaksi.id_jenis_item_transaksi IN ( 2, 10, 13, 15, 6 )
                AND item_transaksi.is_delete = FALSE
            ) AS keluar,
            (
            SELECT SUM
                ( transaksi )
            FROM
                (
                    (
                    SELECT COALESCE
                        (
                        CASE

                                WHEN item_transaksi.id_jenis_item_transaksi IN ( 6, 15 ) THEN
                                COALESCE ( SUM ( jumlah_transaksi * harga_transaksi ), 0 ) ELSE COALESCE ( SUM ( jumlah_transaksi * harga_wac ), 0 )
                            END,
                            0
                        ) AS transaksi
                    FROM
                        daftar_item_transaksi
                        INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
                    WHERE
                        daftar_item_transaksi.id_item = item.id_item
                        AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                        AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                        AND '2020-07-16 23:59:59'
                        AND item_transaksi.id_jenis_item_transaksi IN ( 2, 10, 13, 15, 6 )
                        AND item_transaksi.is_delete = FALSE
                    GROUP BY
                        item_transaksi.id_jenis_item_transaksi
                    )
                ) foo
            ) AS keluar_rupiah,
            (
            SELECT COALESCE
                ( SUM ( jumlah_transaksi ), 0 )
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                AND '2020-07-16 23:59:59'
                AND item_transaksi.id_jenis_item_transaksi IN ( 23 )
                AND item_transaksi.is_delete = FALSE
            ) AS adj_plus,
            (
            SELECT COALESCE
                ( SUM ( jumlah_transaksi * harga_transaksi ), 0 )
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                AND '2020-07-16 23:59:59'
                AND item_transaksi.id_jenis_item_transaksi IN ( 23 )
                AND item_transaksi.is_delete = FALSE
            GROUP BY
                item_transaksi.id_jenis_item_transaksi
            ) AS adj_plus_rupiah,
            (
            SELECT COALESCE
                ( SUM ( jumlah_transaksi ), 0 )
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                AND '2020-07-16 23:59:59'
                AND item_transaksi.id_jenis_item_transaksi IN ( 37 )
                AND item_transaksi.is_delete = FALSE
            ) AS adj_minus,
            (
            SELECT COALESCE
                ( SUM ( jumlah_transaksi * harga_transaksi ), 0 )
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                AND '2020-07-16 23:59:59'
                AND item_transaksi.id_jenis_item_transaksi IN ( 37 )
                AND item_transaksi.is_delete = FALSE
            GROUP BY
                item_transaksi.id_jenis_item_transaksi
            ) AS adj_minus_rupiah,
            (
            SELECT
                jumlah_setelah_transaksi
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                AND '2020-07-16 23:59:59'
            ORDER BY
                item_transaksi.waktu_transaksi DESC
                LIMIT 1
            ) AS akhir,
            (
            SELECT
                jumlah_setelah_transaksi * harga_wac
            FROM
                daftar_item_transaksi
                INNER JOIN item_transaksi ON item_transaksi.id_item_transaksi = daftar_item_transaksi.id_item_transaksi
            WHERE
                daftar_item_transaksi.id_item = item.id_item
                AND daftar_item_transaksi.id_item_gudang_setup = item_gudang_setup.id_item_gudang_setup
                AND item_transaksi.waktu_transaksi BETWEEN '2020-06-01 00:00:00'
                AND '2020-07-16 23:59:59'
            ORDER BY
                item_transaksi.waktu_transaksi DESC
                LIMIT 1
            ) AS akhir_rupiah
        FROM
            item_gudang_setup
            INNER JOIN item ON item.id_item = item_gudang_setup.id_item
        WHERE
            id_gudang IN (78)
        )
data_gabung
GROUP BY
data_gabung.nama_item,
data_gabung.kode_item,
data_gabung.satuan,
data_gabung.wac
) foo