'H'
AND l.tanggal_input IS NULL AND l.tanggal_lunas IS NULL AND p.status_setor IS NULL
AND b.pekerjaan_id IS NULL AND p.status <> 'Selesai Adminers'
) UNION
(
SELECT 2 AS urut,COUNT(*),SUM(p.nilai_sp) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tanggal_input IS NOT NULL AND l.tgl_setor_pptk IS NULL AND l.tgl_setor_verif IS NULL AND l.tgl_terima_verif IS NULL AND l.tanggal_setor IS NULL AND l.tanggal_lunas IS NULL AND p.status_setor IS NULL
) UNION
(
SELECT 3 AS urut,COUNT(*),SUM(p.nilai_sp) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tanggal_input IS NOT NULL AND l.tgl_setor_pptk IS NOT NULL AND l.tgl_setor_verif IS NULL AND l.tgl_terima_verif IS NULL AND l.tanggal_setor IS NULL AND l.tanggal_lunas IS NULL AND p.status_setor IS NULL
) UNION
(
SELECT 4 AS urut,COUNT(*),SUM(p.nilai_sp) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tanggal_input IS NOT NULL AND l.tgl_setor_pptk IS NOT NULL AND l.tgl_setor_verif IS NOT NULL AND l.tgl_terima_verif IS NULL AND l.tanggal_setor IS NULL AND l.tanggal_lunas IS NULL AND p.status_setor IS NULL
) UNION
(
SELECT 5 AS urut,COUNT(*),SUM(l.nilai_pelunasan) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tanggal_input IS NOT NULL AND l.tgl_setor_pptk IS NOT NULL AND l.tgl_setor_verif IS NOT NULL AND l.tgl_terima_verif IS NOT NULL AND l.tanggal_setor IS NULL AND l.tanggal_lunas IS NULL AND p.status_setor IS NULL
) UNION
(
SELECT 6 AS urut,COUNT(*),SUM(l.nilai_pelunasan) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tanggal_input IS NOT NULL AND l.tgl_setor_pptk IS NOT NULL AND l.tgl_setor_verif IS NOT NULL AND l.tgl_terima_verif IS NOT NULL AND l.tanggal_setor IS NOT NULL AND l.tanggal_lunas IS NULL AND p.status_setor IS NULL
) UNION
(
SELECT 7 AS urut,COUNT(*),SUM(l.nilai_pelunasan) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tanggal_lunas IS NOT NULL AND p.status_setor IS NULL
) UNION
(
SELECT 8 AS urut,COUNT(*),SUM(p.nilai_sp) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
LEFT OUTER JOIN
(SELECT b1.pekerjaan_id,b1.st_termin FROM riwayat_ba b1
INNER JOIN pekerjaan p1 ON (p1.id=b1.pekerjaan_id)
INNER JOIN sub_kegiatan s1 ON (s1.id=p1.sub_kegiatan_id)
WHERE s1.thn='$thn'
GROUP BY b1.pekerjaan_id,b1.st_termin)
b ON (p.id=b.pekerjaan_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tanggal_input IS NULL AND l.tanggal_lunas IS NULL AND p.status_setor IS NULL
AND (b.pekerjaan_id IS NOT NULL OR (b.pekerjaan_id IS NULL AND p.status = 'Selesai Adminers'))
)
ORDER BY urut ASC
");
/*
$d=pg_query("(
SELECT 1 AS urut,COUNT(*),SUM(p.nilai_sp) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND p.tgl_setor IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NULL
) UNION
(
SELECT 2 AS urut,COUNT(*),SUM(p.nilai_sp) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND p.tgl_setor IS NOT NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NULL
) UNION
(
SELECT 3 AS urut,COUNT(*),SUM(l.nilai_pelunasan) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NOT NULL
) UNION
(
SELECT 4 AS urut,COUNT(*),SUM(l.nilai_pelunasan) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND p.tgl_setor IS NOT NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL AND l.tanggal_setor IS NULL
) UNION
(
SELECT 5 AS urut,COUNT(*),SUM(l.nilai_pelunasan) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND p.tgl_setor IS NOT NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL AND l.tanggal_setor IS NOT NULL
) UNION
(
SELECT 6 AS urut,COUNT(*),SUM(COALESCE(l.nilai_pelunasan,p.nilai_sp)) FROM pekerjaan p
INNER JOIN sub_kegiatan s ON (s.id=p.sub_kegiatan_id)
LEFT OUTER JOIN vr_realisasi r ON (p.id=r.pekerjaan_id)
LEFT OUTER JOIN vr_pelunasan l ON (r.id=l.vr_realisasi_id)
WHERE s.thn='$thn' AND p.user_nama IS NULL AND p.tgl_setor IS NULL AND COALESCE(l.st_hutang,'') <> 'H'
AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL
)
ORDER BY urut ASC
");
*/
while($r=pg_fetch_array($d)) {
$urut[]=$r[0];
$jml[]=$r[1];
$total[]=$r[2];
}
?>