109 lines
5.7 KiB
PHP
109 lines
5.7 KiB
PHP
<?php
|
|
header("Content-type: application/vnd-ms-excel");
|
|
header("Content-Disposition: attachment; filename=rekap_jumlah.xls");
|
|
include('../koneksi.php');
|
|
$thn=$_GET['thn'];
|
|
|
|
|
|
$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 COALESCE(p.user_nama,'')<>'HUTANG TERMIN'
|
|
AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NULL
|
|
AND l.tanggal_lunas IS NULL AND p.status_setor 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 COALESCE(p.user_nama,'')<>'HUTANG TERMIN'
|
|
AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NULL AND l.tanggal_lunas IS NULL AND p.status_setor 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 COALESCE(p.user_nama,'')<>'HUTANG TERMIN'
|
|
AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NOT NULL AND l.tanggal_lunas IS NULL AND p.status_setor IS 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 COALESCE(p.user_nama,'')<>'HUTANG TERMIN'
|
|
AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT 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 p.tgl_setor IS NOT NULL AND COALESCE(l.st_hutang,'') = 'H' AND COALESCE(p.user_nama,'')<>'HUTANG TERMIN'
|
|
AND l.tgl_terima_verif IS NOT NULL AND st_setor_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 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 p.tgl_setor IS NOT NULL AND COALESCE(l.st_hutang,'') = 'H' AND COALESCE(p.user_nama,'')<>'HUTANG TERMIN'
|
|
AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL AND l.tanggal_setor IS NOT NULL AND l.tanggal_lunas IS NOT NULL AND p.status_setor IS NULL
|
|
) UNION
|
|
(
|
|
SELECT 7 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 COALESCE(p.user_nama,'')<>'HUTANG TERMIN'
|
|
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];
|
|
}
|
|
?>
|
|
|
|
<table border="1" style="font-family:Arial;font-size:12;">
|
|
<thead>
|
|
<tr>
|
|
<td align="center" width="200">PROSES PBJ <span style="color:#3282B8"><br><i class="mdi mdi-apps m-r-5"></i>PENGADAAN</span></td>
|
|
<td align="center" width="200">SELESAI ADMIN <span style="color:#3282B8"><br><i class="mdi mdi-apps m-r-5"></i>PENGADAAN</span></td>
|
|
<td align="center" width="200">SETOR VERIFIKASI <span style="color:#3282B8"><br><i class="mdi mdi-apps m-r-5"></i>PEMBANTU PPTK</span></td>
|
|
<td align="center" width="200">PROSES VERIFIKASI <span style="color:#3282B8"><br><i class="mdi mdi-apps m-r-5"></i>VERIFIKASI</span></td>
|
|
<td align="center" width="200">PROSES PELUNASAN <span style="color:#3282B8"><br><i class="mdi mdi-apps m-r-5"></i>PERBENDAHARAAN</span></td>
|
|
<td align="center" width="200">TERBAYAR <span style="color:#3282B8"><br><i class="mdi mdi-apps m-r-5"></i>PERBENDAHARAAN</span></td>
|
|
<td align="center" width="280"><span style="color:red"><i class="mdi mdi-alert-circle-outline m-r-5"></i>BELUM DIVALIDASI<br>SUDAH DIVERIFIKASI </span></td>
|
|
<tr>
|
|
</thead>
|
|
<tbody>
|
|
<tr>
|
|
|
|
<td align="center"><?php echo $jml[0]; ?></td>
|
|
<td align="center"><?php echo $jml[1]; ?></td>
|
|
<td align="center"><?php echo $jml[2]; ?></td>
|
|
<td align="center"><?php echo $jml[3]; ?></td>
|
|
<td align="center"><?php echo $jml[4]; ?></td>
|
|
<td align="center"><?php echo $jml[5]; ?></td>
|
|
<td align="center"><?php echo $jml[6]; ?></td>
|
|
</tr>
|
|
<tr>
|
|
<td align="center"><?php echo number_format($total[0],0,',','.'); ?></td>
|
|
<td align="center"><?php echo number_format($total[1],0,',','.'); ?></td>
|
|
<td align="center"><?php echo number_format($total[2],0,',','.'); ?></td>
|
|
<td align="center"><?php echo number_format($total[3],0,',','.'); ?></td>
|
|
<td align="center"><?php echo number_format($total[4],0,',','.'); ?></td>
|
|
<td align="center"><?php echo number_format($total[5],0,',','.'); ?></td>
|
|
<td align="center"><?php echo number_format($total[6],0,',','.'); ?></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|