simak/app/rekap/tabel_jumlah.php

<?php 
include('../koneksi.php');
$thn=$_POST['thn_dpa'];

$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';
}
?>

<table class="table table-bordered table-striped table-condensed tabel_rekap" id="tabel_data_jumlah" style="font-size:12px">
<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>
<?php
$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];
}
?>
<tr>
<td align="center"><button class="btn btn-block btn-xs" id="siji"><?php echo $jml[0]; ?></button></td>
<td align="center"><button class="btn btn-block btn-xs" id="wolu"><?php echo $jml[7]; ?></button></td>
<td align="center"><button class="btn btn-block btn-xs" id="loro"><?php echo $jml[1]; ?></button></td>
<td align="center"><button class="btn btn-block btn-xs" id="telu"><?php echo $jml[2]; ?></button></td>
<td align="center"><button class="btn btn-block btn-xs" id="papat"><?php echo $jml[3]; ?></button></td>
<td align="center"><button class="btn btn-block btn-xs" id="limo"><?php echo $jml[4]; ?></button></td>
<td align="center"><button class="btn btn-block btn-xs" id="enem"><?php echo $jml[5]; ?></button></td>
<td align="center"><button class="btn btn-block btn-xs" id="pitu"><?php echo $jml[6]; ?></button></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>

<div class="row">
<div class="col-lg-6">
<button type="button" class="btn btn-default btn-block" id="excel_jml">EXCEL</button>
</div>
<div class="col-lg-6">
<button type="button" class="btn btn-default btn-block" id="grafik_jml"><i class="mdi mdi-chart-pie m-r-5"></i>GRAFIK</button>
</div>
</div>

<script>
$('#grafik_jml').click(function(){
var siji=$('#siji').html();
var loro=$('#loro').html();
var telu=$('#telu').html();
var papat=$('#papat').html();
var limo=$('#limo').html();
var enem=$('#enem').html();
var pitu=$('#pitu').html();
var wolu=$('#wolu').html();
$('#modal_chart').modal('show');
$('#modal_body_chart').html("");
$('#modal_head_chart').html("Grafik - Jumlah Pekerjaan");
	 $.ajax({
        type: "POST",
        url: "app/rekap/chart_jumlah.php",
        data: 'siji='+siji
     	+'&loro='+loro
     	+'&telu='+telu
     	+'&papat='+papat
     	+'&limo='+limo
     	+'&enem='+enem
     	+'&pitu='+pitu
     	+'&wolu='+wolu,
        success: function(data){
           $('#modal_body_chart').html(data);      
        }
     });
});

$('#excel_jml').click(function(){  
	var thn = $('#thn_dpa').val();
    var w = 600;
    var h = 300;
    var left = Number((screen.width/2)-(w/2));
    var tops = Number((screen.height/2)-(h/2));    
    window.open('app/rekap/excel_din.php?thn='+thn,'newwin','toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=yes, resizable=no, copyhistory=no, width='+w+', height='+h+', top='+tops+', left='+left);   
});  

$('#excel_pbj').click(function(){  
	var thn = $('#thn_dpa').val();
    var w = 600;
    var h = 300;
    var left = Number((screen.width/2)-(w/2));
    var tops = Number((screen.height/2)-(h/2));    
    window.open('app/rekap/excel_pbj.php?thn='+thn,'newwin','toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=yes, resizable=no, copyhistory=no, width='+w+', height='+h+', top='+tops+', left='+left);   
}); 

$('#siji').click(function(){
	var thn_dpa = $('#thn_dpa').val();
	var key = 'siji';
	$('#modal_jumlah').modal('show');
	$('#modal_body_jumlah').html('');
   	$('#modal_head_jumlah').html('<b>DATA PROSES PBJ</b>');
    $.ajax({
       type: "POST",
       url: "app/rekap/tabel_jm1.php",
       data: 'thn='+thn_dpa
       +'&key='+key,
       success: function(data){
          $('#modal_body_jumlah').html(data);
       }
    });
});

$('#loro').click(function(){
	var thn_dpa = $('#thn_dpa').val();
    var key = 'loro';
	$('#modal_jumlah').modal('show');
	$('#modal_body_jumlah').html('');
   	$('#modal_head_jumlah').html('<b>TIM VALIDASI PENGADAAN - PROSES VALIDASI</b>');
    $.ajax({
       type: "POST",
       url: "app/rekap/tabel_jm1.php",
       data: 'thn='+thn_dpa
       +'&key='+key,
       success: function(data){
          $('#modal_body_jumlah').html(data);
       }
    });
});

$('#telu').click(function(){
	var thn_dpa = $('#thn_dpa').val();
    var rk_sopo_id = $('#rk_sopo_id').val();
	var jenis = $('#rk_jenis').val();
    var key = 'telu';
	$('#modal_jumlah').modal('show');
	$('#modal_body_jumlah').html('');
   	$('#modal_head_jumlah').html('<b><?php echo $ketpembpptk; ?> - PROSES PENGECEKAN KELENGKAPAN</b>');
    $.ajax({
       type: "POST",
       url: "app/rekap/tabel_jm1.php",
       data: 'thn='+thn_dpa
       +'&rk_sopo_id='+rk_sopo_id
       +'&jenis='+jenis
       +'&key='+key,
       success: function(data){
          $('#modal_body_jumlah').html(data);
       }
    });
});

$('#papat').click(function(){
	var thn_dpa = $('#thn_dpa').val();
    var key = 'papat';
	$('#modal_jumlah').modal('show');
	$('#modal_body_jumlah').html('');
   	$('#modal_head_jumlah').html('<b><?php echo $ketpembpptk; ?> - SETOR VERIFIKASI</b>');
    $.ajax({
       type: "POST",
       url: "app/rekap/tabel_jm1.php",
       data: 'thn='+thn_dpa
       +'&key='+key,
       success: function(data){
          $('#modal_body_jumlah').html(data);
       }
    });
});

$('#limo').click(function(){
	var thn_dpa = $('#thn_dpa').val();
    var key = 'limo';
	$('#modal_jumlah').modal('show');
	$('#modal_body_jumlah').html('');
   	$('#modal_head_jumlah').html('<b>VERIFIKASI - PROSES VERIFIKASI</b>');
    $.ajax({
       type: "POST",
       url: "app/rekap/tabel_jm1.php",
       data: 'thn='+thn_dpa
       +'&key='+key,
       success: function(data){
          $('#modal_body_jumlah').html(data);
       }
    });
});

$('#enem').click(function(){
	var thn_dpa = $('#thn_dpa').val();
    var key = 'enem';
	$('#modal_jumlah').modal('show');
	$('#modal_body_jumlah').html('');
   	$('#modal_head_jumlah').html('<b>VERIFIKASI - SETOR SIAP PELUNASAN</b>');
    $.ajax({
       type: "POST",
       url: "app/rekap/tabel_jm1.php",
       data: 'thn='+thn_dpa
       +'&key='+key,
       success: function(data){
          $('#modal_body_jumlah').html(data);
       }
    });
});

$('#pitu').click(function(){
	var thn_dpa = $('#thn_dpa').val();
    var key = 'pitu';
	$('#modal_jumlah').modal('show');
	$('#modal_body_jumlah').html('');
   	$('#modal_head_jumlah').html('<b>PERBENDAHARAAN - LUNAS</b>');
    $.ajax({
       type: "POST",
       url: "app/rekap/tabel_jm1.php",
       data: 'thn='+thn_dpa
       +'&key='+key,
       success: function(data){
          $('#modal_body_jumlah').html(data);
       }
    });
});

$('#wolu').click(function(){
	var thn_dpa = $('#thn_dpa').val();
    var key = 'wolu';
	$('#modal_jumlah').modal('show');
	$('#modal_body_jumlah').html('');
   	$('#modal_head_jumlah').html('<b>TIM BA - PROSES BA</b>');
    $.ajax({
       type: "POST",
       url: "app/rekap/tabel_jm1.php",
       data: 'thn='+thn_dpa
       +'&key='+key,
       success: function(data){
          $('#modal_body_jumlah').html(data);
       }
    });
});

$('#modal_chart').on('off.bs.modal', function() {
    $('#chart').css('visibility', 'hidden');
});

$('#modal_chart').on('shown.bs.modal', function() {
    $('#chart').css('visibility', 'initial');
    $('#chart').highcharts().reflow();
});


</script>