195 lines
9.6 KiB
PHP
195 lines
9.6 KiB
PHP
<?php
|
|
header("Content-type: application/vnd-ms-excel");
|
|
header("Content-Disposition: attachment; filename=rekap_jumlah.xls");
|
|
include('../koneksi.php');
|
|
$thn=$_GET['thn'];
|
|
|
|
$d1=pg_query("SELECT status FROM set_pemb_pptk WHERE thn='$thn'");
|
|
while($r1=pg_fetch_array($d1)) {$status_pemb_pptk=$r1[0];}
|
|
|
|
if ($status_pemb_pptk=='t') {
|
|
$ketpembpptk='PEMBANTU PPTK';
|
|
} else {
|
|
$ketpembpptk='TIM VALIDASI';
|
|
}
|
|
|
|
$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)
|
|
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 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];
|
|
}
|
|
?>
|
|
|
|
<table border="1" style="font-family:Arial;font-size:12;">
|
|
<thead>
|
|
<tr>
|
|
<td align="center" width="200">PROSES PBJ <span style="color:red"><br><i class="mdi mdi-apps m-r-5"></i>PENGADAAN</span></td>
|
|
<td align="center" width="200">PROSES BA <span style="color:red"><br><i class="mdi mdi-apps m-r-5"></i>TIM BA</span></td>
|
|
<td align="center" width="200">PROSES VALIDASI <span style="color:red"><br><i class="mdi mdi-apps m-r-5"></i>TIM VALIDASI</span></td>
|
|
<td align="center" width="200">CEK KELENGKAPAN <span style="color:red"><br><i class="mdi mdi-apps m-r-5"></i><?php echo $ketpembpptk; ?></span></td>
|
|
<td align="center" width="200">SETOR VERIFIKASI <span style="color:red"><br><i class="mdi mdi-apps m-r-5"></i><?php echo $ketpembpptk; ?></span></td>
|
|
<td align="center" width="200">PROSES VERIFIKASI <span style="color:red"><br><i class="mdi mdi-apps m-r-5"></i>VERIFIKASI</span></td>
|
|
<td align="center" width="200">SETOR PELUNASAN <span style="color:red"><br><i class="mdi mdi-apps m-r-5"></i>VERIFIKASI</span></td>
|
|
<td align="center" width="200">PELUNASAN <span style="color:red"><br><i class="mdi mdi-apps m-r-5"></i>PERBENDAHARAAN</span></td>
|
|
<tr>
|
|
</thead>
|
|
<tbody>
|
|
<tr>
|
|
|
|
<td align="center"><?php echo $jml[0]; ?></td>
|
|
<td align="center"><?php echo $jml[7]; ?></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[7],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>
|