Scribd
Upload
13 views3 pages

Cetjl3-Union All

Uploaded by

Ahmad Faikar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
13 views3 pages

Cetjl3-Union All

Uploaded by

Ahmad Faikar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

if ($cbg !

= 'semua') {
$query = "
SELECT
kd_brg,
nm_brg,
harga,
satuan,
SUM(totmasuk) AS totmasuk,
SUM(totkeluar) AS totkeluar,
SUM(transmasuk) AS transmasuk,
SUM(transkeluar) AS transkeluar,
SUM(nontransmasuk) AS nontransmasuk,
SUM(nontranskeluar) AS nontranskeluar
FROM (
SELECT
a.kd_brg,
a.nm_brg,
a.harga,
a.satuan,
SUM(b.masuk) AS totmasuk,
SUM(b.keluar) AS totkeluar,
0 AS transmasuk,
0 AS transkeluar,
0 AS nontransmasuk,
0 AS nontranskeluar,
(SELECT akhir FROM rwy_stock WHERE kode=a.kd_brg AND tgl<'$hr1'
ORDER BY tgl DESC LIMIT 1) AS akhirs
FROM stock a, rwy_stock b
WHERE b.kode LIKE '$cbg%'
AND a.kd_brg = b.kode
$piljen
AND (b.tgl BETWEEN '$hr1' AND '$hr2')
GROUP BY a.kd_brg

UNION ALL

SELECT
a.kd_brg,
a.nm_brg,
a.harga,
a.satuan,
0 AS totmasuk,
0 AS totkeluar,
SUM(b.masuk) AS transmasuk,
SUM(b.keluar) AS transkeluar,
0 AS nontransmasuk,
0 AS nontranskeluar,
NULL AS akhirs
FROM stock a, rwy_stock b
WHERE b.kode LIKE '$cbg%'
AND a.kd_brg = b.kode
$piljen
AND (uraian LIKE 'Penjualan Obat Bebas%' OR uraian LIKE 'Penjualan
Obat Resep%' OR uraian LIKE 'Penjualan u/Treatment Paket%')
AND (b.tgl BETWEEN '$hr1' AND '$hr2')
GROUP BY a.kd_brg

UNION ALL
SELECT
a.kd_brg,
a.nm_brg,
a.harga,
a.satuan,
0 AS totmasuk,
0 AS totkeluar,
0 AS transmasuk,
0 AS transkeluar,
SUM(b.masuk) AS nontransmasuk,
SUM(b.keluar) AS nontranskeluar,
NULL AS akhirs
FROM stock a, rwy_stock b
WHERE b.kode LIKE '$cbg%'
AND a.kd_brg = b.kode
$piljen
AND (uraian NOT LIKE 'Penjualan Obat Bebas%' AND uraian NOT LIKE
'Penjualan Obat Resep%' AND uraian NOT LIKE 'Penjualan u/Treatment Paket%')
AND (b.tgl BETWEEN '$hr1' AND '$hr2')
GROUP BY a.kd_brg
) AS q
GROUP BY kd_brg, nm_brg, harga, satuan
";
} else {
$query = "
SELECT
kd_brg,
nm_brg,
harga,
satuan,
SUM(totmasuk) AS totmasuk,
SUM(totkeluar) AS totkeluar,
SUM(transmasuk) AS transmasuk,
SUM(transkeluar) AS transkeluar,
SUM(nontransmasuk) AS nontransmasuk,
SUM(nontranskeluar) AS nontranskeluar
FROM (
SELECT
a.kd_brg,
a.nm_brg,
a.harga,
a.satuan,
SUM(b.masuk) AS totmasuk,
SUM(b.keluar) AS totkeluar,
0 AS transmasuk,
0 AS transkeluar,
0 AS nontransmasuk,
0 AS nontranskeluar,
(SELECT akhir FROM rwy_stock WHERE kode=a.kd_brg AND tgl<'$hr1'
ORDER BY tgl DESC LIMIT 1) AS akhirs
FROM stock a, rwy_stock b
WHERE a.kd_brg = b.kode
$piljen
AND (b.tgl BETWEEN '$hr1' AND '$hr2')
GROUP BY a.kd_brg

UNION ALL

SELECT
a.kd_brg,
a.nm_brg,
a.harga,
a.satuan,
0 AS totmasuk,
0 AS totkeluar,
SUM(b.masuk) AS transmasuk,
SUM(b.keluar) AS transkeluar,
0 AS nontransmasuk,
0 AS nontranskeluar,
NULL AS akhirs
FROM stock a, rwy_stock b
WHERE a.kd_brg = b.kode
$piljen
AND (uraian LIKE 'Penjualan Obat Bebas%' OR uraian LIKE 'Penjualan
Obat Resep%' OR uraian LIKE 'Penjualan u/Treatment Paket%')
AND (b.tgl BETWEEN '$hr1' AND '$hr2')
GROUP BY a.kd_brg

UNION ALL

SELECT
a.kd_brg,
a.nm_brg,
a.harga,
a.satuan,
0 AS totmasuk,
0 AS totkeluar,
0 AS transmasuk,
0 AS transkeluar,
SUM(b.masuk) AS nontransmasuk,
SUM(b.keluar) AS nontranskeluar,
NULL AS akhirs
FROM stock a, rwy_stock b
WHERE a.kd_brg = b.kode
$piljen
AND (uraian NOT LIKE 'Penjualan Obat Bebas%' AND uraian NOT LIKE
'Penjualan Obat Resep%' AND uraian NOT LIKE 'Penjualan u/Treatment Paket%')
AND (b.tgl BETWEEN '$hr1' AND '$hr2')
GROUP BY a.kd_brg
) AS q
GROUP BY kd_brg, nm_brg, harga, satuan
";
}

You might also like