WITH recent_entries AS (
    SELECT 
        kd_bangsal, 
        kode_brng,
        stok_akhir,
        tanggal,
        jam,
        ROW_NUMBER() OVER (PARTITION BY kd_bangsal ORDER BY tanggal DESC, jam DESC) AS rn
    FROM riwayat_barang_medis
    WHERE kode_brng = '000005941'
),
stok_akhir_cte AS (
    SELECT SUM(stok_akhir) as stok_akhir
    FROM recent_entries
    WHERE rn = 1
),
body as (
SELECT 
    b.kode_brng AS Kode_Barang, 
    b.nama_brng AS Nama_Barang, 
    b.kode_sat AS Satuan_Barang,
    c.h_beli AS Harga_Awal, 
    a.masuk AS Jumlah_Awal, 
    c.h_beli * a.masuk AS Total_Harta_Awal,
    '0' AS Tanggal_Beli, 
    '0' AS Harga_Masuk, 
    '0' AS Jumlah_Masuk, 
    '0' AS Total_Masuk,
    '0' AS Harga_Keluar, 
    '0' AS Jml_keluar, 
    '0' AS Ttl_keluar,
    '0' AS Harga_Akhir,
    '0' AS Jml_Akhir,
    '0' AS ttl_akhir,
    a.tanggal,a.jam
FROM riwayat_barang_medis a
INNER JOIN databarang b ON a.kode_brng = b.kode_brng
INNER JOIN opname c ON a.tanggal = c.tanggal AND a.kode_brng = c.kode_brng AND a.masuk = c.real
WHERE a.kode_brng = '000005941' AND a.posisi = 'Opname' AND a.status = 'Simpan'
UNION ALL
SELECT 
    b.kode_brng AS Kode_Barang, 
    b.nama_brng AS Nama_Barang, 
    b.kode_sat AS Satuan_Barang,
    '0' AS Harga_Awal, 
    '0' AS Jumlah_Awal, 
    '0' AS Total_Harta_Awal,
    a.tanggal AS Tanggal_Beli, 
    '0' AS Harga_Masuk, 
    a.masuk AS Jumlah_Masuk, 
    '0' AS Total_Masuk,
    '0' AS Harga_Keluar, 
    '0' AS Jml_keluar, 
    '0' AS Ttl_keluar,
    '0' AS Harga_Akhir,
    '0' AS Jml_Akhir,
    '0' AS ttl_akhir,
    a.tanggal,a.jam
FROM riwayat_barang_medis a
INNER JOIN databarang b ON a.kode_brng = b.kode_brng
WHERE a.kode_brng = '000005941' AND a.posisi = 'Penerimaan' AND a.status = 'Simpan')
SELECT body.Kode_Barang, body.Nama_Barang, body.satuan_barang, body.Harga_Awal, 
body.Jumlah_Awal, body.total_harta_awal, body.Tanggal_Beli, body.Harga_Masuk,
body.Jumlah_Masuk, body.Total_Masuk, body.Harga_Keluar, body.jml_keluar, body.Ttl_keluar,
body.harga_akhir, body.Jml_Akhir, body.ttl_akhir, stok_akhir_cte.stok_akhir,@stok_akhir := CASE WHEN body.Jumlah_Awal != 0 and body.Jumlah_Masuk != 0 THEN CASE WHEN body.Jumlah_Awal != 0 THEN LEAST(body.Jumlah_Awal, @stok_akhir) WHEN body.Jumlah_Masuk != 0 THEN
LEAST(body.Jumlah_Masuk, @stok_akhir) END ELSE 0 END AS jml_akhir_dinamis, @stok_akhir := @stok_akhir - jml_akhir_dinamis AS stok_sisa
FROM body CROSS JOIN stok_akhir_cte, (SELECT @stok_akhir := 100) AS vars  ORDER BY body.Tanggal DESC, body.Jam DESC; 

SELECT * from riwayat_barang_medis where kode_brng = '000005941' ORDER BY tanggal desc, jam desc


WITH recent_entries AS (
    SELECT 
        kd_bangsal, 
        kode_brng,
        stok_akhir,
        tanggal,
        jam,
        ROW_NUMBER() OVER (PARTITION BY kd_bangsal ORDER BY tanggal DESC, jam DESC) AS rn
    FROM riwayat_barang_medis
    WHERE kode_brng = '000005941'
),
stok_akhir_cte AS (
    SELECT SUM(stok_akhir) AS stok_akhir
    FROM recent_entries
    WHERE rn = 1
)
SELECT * FROM stok_akhir_cte;

WITH recent_entries AS (
    SELECT 
        kd_bangsal, 
        kode_brng,
        stok_akhir,
        tanggal,
        jam,
        ROW_NUMBER() OVER (PARTITION BY kd_bangsal ORDER BY tanggal DESC, jam DESC) AS rn
    FROM riwayat_barang_medis
    WHERE kode_brng = '000005941'
),
stok_akhir_cte AS (
    SELECT SUM(stok_akhir) AS stok_akhir
    FROM recent_entries
    WHERE rn = 1
)
SELECT 
    b.kode_brng AS Kode_Barang, 
    b.nama_brng AS Nama_Barang, 
    b.kode_sat AS Satuan_Barang,
    c.h_beli AS Harga_Awal, 
    a.masuk AS Jumlah_Awal, 
    c.h_beli * a.masuk AS Total_Harta_Awal,
    '0' AS Tanggal_Beli, 
    '0' AS Harga_Masuk, 
    '0' AS Jumlah_Masuk, 
    '0' AS Total_Masuk,
    '0' AS Harga_Keluar, 
    '0' AS Jml_keluar, 
    '0' AS Ttl_keluar,
    '0' AS Harga_Akhir,
    '0' AS Jml_Akhir,
    '0' AS ttl_akhir,
    stok.stok_akhir AS Stok_Akhir,  -- menambahkan kolom stok_akhir
    '0' AS Jml_Akhir  -- kolom Jml_Akhir diatur ke 0
FROM riwayat_barang_medis a
INNER JOIN databarang b ON a.kode_brng = b.kode_brng
INNER JOIN opname c ON a.tanggal = c.tanggal AND a.kode_brng = c.kode_brng AND a.masuk = c.real
CROSS JOIN stok_akhir_cte AS stok  -- memberi alias pada stok_akhir_cte
WHERE a.kode_brng = '000005941' AND a.posisi = 'Opname' AND a.status = 'Simpan'
UNION ALL
SELECT 
    b.kode_brng AS Kode_Barang, 
    b.nama_brng AS Nama_Barang, 
    b.kode_sat AS Satuan_Barang,
    '0' AS Harga_Awal, 
    '0' AS Jumlah_Awal, 
    '0' AS Total_Harta_Awal,
    a.tanggal AS Tanggal_Beli, 
    '0' AS Harga_Masuk, 
    a.masuk AS Jumlah_Masuk, 
    '0' AS Total_Masuk,
    '0' AS Harga_Keluar, 
    '0' AS Jml_keluar, 
    '0' AS Ttl_keluar,
    '0' AS Harga_Akhir,
    '0' AS Jml_Akhir,
    '0' AS ttl_akhir,
    stok.stok_akhir AS Stok_Akhir,  -- menambahkan kolom stok_akhir
    '0' AS Jml_Akhir  -- kolom Jml_Akhir diatur ke 0
FROM riwayat_barang_medis a
INNER JOIN databarang b ON a.kode_brng = b.kode_brng
CROSS JOIN stok_akhir_cte AS stok  -- memberi alias pada stok_akhir_cte
WHERE a.kode_brng = '000005941' AND a.posisi = 'Penerimaan' AND a.status = 'Simpan';


WITH recent_entries AS (
    SELECT 
        kd_bangsal, 
        kode_brng,
        stok_akhir,
        tanggal,
        jam,
        ROW_NUMBER() OVER (PARTITION BY kd_bangsal ORDER BY tanggal DESC, jam DESC) AS rn
    FROM riwayat_barang_medis
),
stok_akhir_cte AS (
    SELECT SUM(stok_akhir) as stok_akhir
    FROM recent_entries
    WHERE rn = 1
),
body as (
SELECT 
    b.kode_brng AS Kode_Barang, 
    b.nama_brng AS Nama_Barang, 
    b.kode_sat AS Satuan_Barang,
    c.h_beli AS Harga_Awal, 
    a.masuk AS Jumlah_Awal, 
    c.h_beli * a.masuk AS Total_Harta_Awal,
    '0' AS Tanggal_Beli, 
    '0' AS Harga_Masuk, 
    '0' AS Jumlah_Masuk, 
    '0' AS Total_Masuk,
    '0' AS Harga_Keluar, 
    '0' AS Jml_keluar, 
    '0' AS Ttl_keluar,
    '0' AS Harga_Akhir,
    '0' AS Jml_Akhir,
    '0' AS ttl_akhir,
    a.tanggal,a.jam
FROM riwayat_barang_medis a
INNER JOIN databarang b ON a.kode_brng = b.kode_brng
INNER JOIN opname c ON a.tanggal = c.tanggal AND a.kode_brng = c.kode_brng AND a.masuk = c.real
WHERE a.posisi = 'Opname' AND a.status = 'Simpan' ORDER BY a.tanggal asc, a.jam ASC 
UNION ALL
SELECT 
    b.kode_brng AS Kode_Barang, 
    b.nama_brng AS Nama_Barang, 
    b.kode_sat AS Satuan_Barang,
    '0' AS Harga_Awal, 
    '0' AS Jumlah_Awal, 
    '0' AS Total_Harta_Awal,
    a.tanggal AS Tanggal_Beli, 
    '0' AS Harga_Masuk, 
    a.masuk AS Jumlah_Masuk, 
    '0' AS Total_Masuk,
    '0' AS Harga_Keluar, 
    '0' AS Jml_keluar, 
    '0' AS Ttl_keluar,
    '0' AS Harga_Akhir,
    '0' AS Jml_Akhir,
    '0' AS ttl_akhir,
    a.tanggal,a.jam
FROM riwayat_barang_medis a
INNER JOIN databarang b ON a.kode_brng = b.kode_brng
WHERE a.posisi = 'Penerimaan' AND a.status = 'Simpan' ORDER BY a.tanggal asc, a.jam ASC)
SELECT body.Kode_Barang, body.Nama_Barang, body.satuan_barang, body.Harga_Awal, 
body.Jumlah_Awal, body.total_harta_awal, body.Tanggal_Beli, body.Harga_Masuk,
body.Jumlah_Masuk, body.Total_Masuk, body.Harga_Keluar, body.jml_keluar, body.Ttl_keluar,
body.harga_akhir, body.Jml_Akhir, body.ttl_akhir,stok_akhir_cte.stok_akhir
from body CROSS JOIN stok_akhir_cte where body.Nama_Barang like '%testing%'

select databarang.kode_brng,databarang.nama_brng,case when r.kode_brng is not null and detail_pemberian_obat.jml = '30' then '30' when r.kode_brng is not null and detail_pemberian_obat.jml = '60' then '60' 
when r.kode_brng is not null and detail_pemberian_obat.jml = '90' then '90'  when r.kode_brng is not null and detail_pemberian_obat.jml = '45' then '45' when r.kode_brng is not null and detail_pemberian_obat.jml = '15' then '15'
when detail_pemberian_obat.jml = '30' then '7' when detail_pemberian_obat.jml = '60' then '14' when detail_pemberian_obat.jml = '90' then '21' 
when detail_pemberian_obat.jml = '120' then '28' when detail_pemberian_obat.jml = '15' then '4' when detail_pemberian_obat.jml = '45' then '11' else detail_pemberian_obat.jml end as jml,
detail_pemberian_obat.biaya_obat,detail_pemberian_obat.embalase,databarang.kode_sat,
detail_pemberian_obat.tuslah,detail_pemberian_obat.total from
detail_pemberian_obat inner join databarang left join detail_obat_racikan r on detail_pemberian_obat.kode_brng=databarang.kode_brng and r.kode_brng=detail_pemberian_obat.kode_brng and 
detail_pemberian_obat.tgl_perawatan=r.tgl_perawatan and detail_pemberian_obat.jam=r.jam and detail_pemberian_obat.no_rawat=r.no_rawat 
where detail_pemberian_obat.tgl_perawatan='2024-10-05' and detail_pemberian_obat.jam='09:30:36' and detail_pemberian_obat.no_rawat='2024/10/05/000164'

SELECT databarang.kode_brng, databarang.nama_brng, CASE WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '30' THEN '30' WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '60' THEN '60'
WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '90' THEN '90' WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '45' THEN '45' WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '15' THEN '15'
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '2' THEN '1' WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '3' THEN '2'
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '4' THEN '2' WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '5' THEN '3'
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '6' THEN '4' WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '7' THEN '5'
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '8' THEN '6' WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '9' THEN '7'
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '2' THEN '1' WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '3' THEN '2'
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '4' THEN '2' WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '5' THEN '3'
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '6' THEN '4' WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '7' THEN '5'
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '8' THEN '6' WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '9' THEN '7'
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '2' THEN '1' WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '3' THEN '2'
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '4' THEN '2' WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '5' THEN '3'
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '6' THEN '4' WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '7' THEN '5'
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '8' THEN '6' WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '9' THEN '7'
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '2' THEN '1' WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '3' THEN '2'
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '4' THEN '2' WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '5' THEN '3' 
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '6' THEN '4' WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '7' THEN '5' 
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '8' THEN '6' WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '9' THEN '7' 
ELSE detail_pemberian_obat.jml END AS jml, detail_pemberian_obat.biaya_obat, detail_pemberian_obat.embalase, databarang.kode_sat,
detail_pemberian_obat.tuslah, CASE WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '30' THEN 30 * detail_pemberian_obat.biaya_obat WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '60' THEN 60 * detail_pemberian_obat.biaya_obat
WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '90' THEN 90 * detail_pemberian_obat.biaya_obat WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '45' THEN 45 * detail_pemberian_obat.biaya_obat WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '15' THEN 15 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '2' THEN 1 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '3' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '4' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '5' THEN 3 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '6' THEN 4 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '7' THEN 5 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '8' THEN 6 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '9' THEN 7 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '2' THEN 1 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '3' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '4' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '5' THEN 3 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '6' THEN 4 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '7' THEN 5 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '8' THEN 6 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '9' THEN 7 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '2' THEN 1 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '3' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '4' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '5' THEN 3 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '6' THEN 4 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '7' THEN 5 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '8' THEN 6 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '9' THEN 7 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '2' THEN 1 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '3' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '4' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '5' THEN 3 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '6' THEN 4 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '7' THEN 5 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '8' THEN 6 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '9' THEN 7 * detail_pemberian_obat.biaya_obat
ELSE detail_pemberian_obat.total end as total FROM detail_pemberian_obat INNER JOIN databarang ON detail_pemberian_obat.kode_brng = databarang.kode_brng LEFT JOIN detail_obat_racikan r 
ON detail_pemberian_obat.kode_brng = r.kode_brng AND detail_pemberian_obat.tgl_perawatan = r.tgl_perawatan AND detail_pemberian_obat.jam = r.jam AND detail_pemberian_obat.no_rawat = r.no_rawat
WHERE

SELECT databarang.kode_brng, databarang.nama_brng, CASE WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '30' THEN '30' WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '60' THEN '60'
WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '90' THEN '90' WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '45' THEN '45' WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '15' THEN '15'
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '2' THEN '1' WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '3' THEN '1'
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '4' THEN '2' WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '5' THEN '2'
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '6' THEN '2' WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '7' THEN '2'
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '8' THEN '2' WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '9' THEN '2'
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '2' THEN '1' WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '3' THEN '1'
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '4' THEN '2' WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '5' THEN '2'
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '6' THEN '2' WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '7' THEN '2'
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '8' THEN '2' WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '9' THEN '2'
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '2' THEN '1' WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '3' THEN '1'
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '4' THEN '2' WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '5' THEN '2'
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '6' THEN '2' WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '7' THEN '2'
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '8' THEN '2' WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '9' THEN '2'
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '2' THEN '1' WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '3' THEN '1'
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '4' THEN '2' WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '5' THEN '2'
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '6' THEN '2' WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '7' THEN '2'
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '8' THEN '2' WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '9' THEN '2'
WHEN detail_pemberian_obat.jml = '30' THEN '7' WHEN detail_pemberian_obat.jml = '60' THEN '14' WHEN detail_pemberian_obat.jml = '90' THEN '21' WHEN detail_pemberian_obat.jml = '120' THEN '28' WHEN detail_pemberian_obat.jml = '15' THEN '4'
WHEN detail_pemberian_obat.jml = '45' THEN '11' ELSE detail_pemberian_obat.jml END AS jml, detail_pemberian_obat.biaya_obat, detail_pemberian_obat.embalase, databarang.kode_sat,
detail_pemberian_obat.tuslah, CASE WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '30' THEN 30 * detail_pemberian_obat.biaya_obat WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '60' THEN 60 * detail_pemberian_obat.biaya_obat 
WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '90' THEN 90 * detail_pemberian_obat.biaya_obat WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '45' THEN 45 * detail_pemberian_obat.biaya_obat 
WHEN r.kode_brng IS NOT NULL AND detail_pemberian_obat.jml = '15' THEN 15 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '2' THEN 1 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '3' THEN 1 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '4' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '5' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '6' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '7' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '8' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003495' AND detail_pemberian_obat.jml = '9' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '2' THEN 1 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '3' THEN 1 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '4' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '5' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '6' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '7' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '8' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005258' AND detail_pemberian_obat.jml = '9' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '2' THEN 1 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '3' THEN 1 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '4' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '5' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '6' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '7' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '8' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000005264' AND detail_pemberian_obat.jml = '9' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '2' THEN 1 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '3' THEN 1 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '4' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '5' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '6' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '7' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '8' THEN 2 * detail_pemberian_obat.biaya_obat WHEN databarang.kode_brng = '000003177' AND detail_pemberian_obat.jml = '9' THEN 2 * detail_pemberian_obat.biaya_obat
WHEN detail_pemberian_obat.jml = '30' THEN 7 * detail_pemberian_obat.biaya_obat WHEN detail_pemberian_obat.jml = '60' THEN 14 * detail_pemberian_obat.biaya_obat WHEN detail_pemberian_obat.jml = '90' THEN 21 * detail_pemberian_obat.biaya_obat 
WHEN detail_pemberian_obat.jml = '120' THEN 28 * detail_pemberian_obat.biaya_obat WHEN detail_pemberian_obat.jml = '15' THEN 4 * detail_pemberian_obat.biaya_obat WHEN detail_pemberian_obat.jml = '45' THEN 11 * detail_pemberian_obat.biaya_obat 
ELSE detail_pemberian_obat.total end as total FROM detail_pemberian_obat INNER JOIN databarang ON detail_pemberian_obat.kode_brng = databarang.kode_brng LEFT JOIN detail_obat_racikan r 
ON detail_pemberian_obat.kode_brng = r.kode_brng AND detail_pemberian_obat.tgl_perawatan = r.tgl_perawatan AND detail_pemberian_obat.jam = r.jam AND detail_pemberian_obat.no_rawat = r.no_rawat
WHERE

"WITH recent_entries AS (SELECT kd_bangsal, kode_brng,stok_akhir,tanggal,jam,ROW_NUMBER() OVER (PARTITION BY kd_bangsal ORDER BY tanggal DESC, jam DESC) AS rn "+
"FROM riwayat_barang_medis), "+
"stok_akhir_cte AS (SELECT SUM(stok_akhir) as stok_akhir FROM recent_entries WHERE rn = 1 ), "+
"body as (SELECT b.kode_brng AS Kode_Barang, b.nama_brng AS Nama_Barang, b.kode_sat AS Satuan_Barang,c.h_beli AS Harga_Awal, "+
"a.masuk AS Jumlah_Awal, c.h_beli * a.masuk AS Total_Harta_Awal,'0' AS Tanggal_Beli, '0' AS Harga_Masuk, '0' AS Jumlah_Masuk, "+
"'0' AS Total_Masuk,'0' AS Harga_Keluar, '0' AS Jml_keluar, '0' AS Ttl_keluar,'0' AS Harga_Akhir,'0' AS Jml_Akhir, "+
"'0' AS ttl_akhir, a.tanggal,a.jam FROM riwayat_barang_medis a INNER JOIN databarang b ON a.kode_brng = b.kode_brng "+
"INNER JOIN opname c ON a.tanggal = c.tanggal AND a.kode_brng = c.kode_brng AND a.masuk = c.real "+
"WHERE a.posisi = 'Opname' AND a.status = 'Simpan' UNION ALL "+
"SELECT b.kode_brng AS Kode_Barang,b.nama_brng AS Nama_Barang, b.kode_sat AS Satuan_Barang,'0' AS Harga_Awal, '0' AS Jumlah_Awal, "+
"'0' AS Total_Harta_Awal,a.tanggal AS Tanggal_Beli, '0' AS Harga_Masuk, a.masuk AS Jumlah_Masuk, '0' AS Total_Masuk,'0' AS Harga_Keluar, "+
"'0' AS Jml_keluar, '0' AS Ttl_keluar, '0' AS Harga_Akhir, '0' AS Jml_Akhir, '0' AS ttl_akhir, a.tanggal,a.jam FROM riwayat_barang_medis a "+
"INNER JOIN databarang b ON a.kode_brng = b.kode_brng WHERE a.posisi IN ('Penerimaan','Hibah') AND a.status = 'Simpan') "+
"SELECT body.Kode_Barang, body.Nama_Barang, body.satuan_barang, body.Harga_Awal, body.Jumlah_Awal, body.total_harta_awal, body.Tanggal_Beli, body.Harga_Masuk, "+
"body.Jumlah_Masuk, body.Total_Masuk, body.Harga_Keluar, body.jml_keluar, body.Ttl_keluar,body.harga_akhir, body.Jml_Akhir, body.ttl_akhir "+
"from body CROSS JOIN stok_akhir_cte where body.Nama_Barang like ? and body.tanggal BETWEEN ? and ? ORDER BY body.tanggal asc, body.jam ASC");

SELECT * from riwayat_barang_medis where kode_brng = '000005941' ORDER BY jam DESC, tanggal DESC

