<link href="_lib/search.css" rel="stylesheet" type="text/css">
<link href="_lib/gradient.css" rel="stylesheet" type="text/css">
<a class="button" onclick="window.open('data:application/msexcel,'+document.getElementById('export').outerHTML.replace(/ /g, '%20'));">Export Data ke Excel</a>&nbsp;<a class="button" onclick="window.open('data:application/msword,'+document.getElementById('export').outerHTML.replace(/ /g, '%20'));">Export Data ke Word</a>
<?php
include 'config/koneksi.php';
include '_lib/fungsi_indotgl.php';	
include '_lib/currency.php';
$key = (isset($_GET['q'])?" AND b.nomor_perkara LIKE '%".$_GET['q']."%' ":"");
//menampilkan data
$posisi  = 0;
mysql_query('SET GLOBAL group_concat_max_len=4294967295');
$hasil = mysql_query("SELECT b.alur_perkara_id, b.nomor_perkara, b.jenis_perkara_text, c.pemohon_pk, c.putusan_pk,
@transaksi_terakhir:=(SELECT MAX(tanggal_transaksi) FROM perkara_biaya WHERE perkara_id = b.perkara_id ORDER BY tanggal_transaksi DESC) AS transaksi_terakhir,
@enam_bulan:=DATE_ADD(@transaksi_terakhir, INTERVAL 90 DAY) AS batas_akhir,
CONCAT((CASE WHEN(FLOOR(DATEDIFF(CURDATE(),@transaksi_terakhir)/30)) = 0 THEN '' ELSE (CONCAT(FLOOR(DATEDIFF(CURDATE(),@transaksi_terakhir)/30),' bulan ')) END),
(CASE WHEN(MOD(DATEDIFF(CURDATE(),@transaksi_terakhir),30)) = 0 THEN '' ELSE (CONCAT(MOD(DATEDIFF(CURDATE(),@transaksi_terakhir),30),' hari')) END)) AS total_bulan,
DATEDIFF(@enam_bulan,CURDATE()) AS total_batas,
(SUM(CASE WHEN a.jenis_transaksi = '1' THEN a.jumlah ELSE 0 END)) AS total_biaya_masuk,
(SUM(CASE WHEN a.jenis_transaksi = '-1' THEN a.jumlah ELSE 0 END)) AS total_biaya_keluar,
((SUM(CASE WHEN a.jenis_transaksi = '1' THEN a.jumlah ELSE 0 END))-(SUM(CASE WHEN a.jenis_transaksi = '-1' THEN a.jumlah ELSE 0 END))) AS sisa_biaya
FROM perkara_biaya a, perkara b, perkara_pk c WHERE b.proses_terakhir_id >= 570 AND b.proses_terakhir_id <= 590 AND a.tahapan_id = 40 AND (b.alur_perkara_id ='15' OR b.alur_perkara_id ='16') AND a.perkara_id = b.perkara_id AND b.perkara_id = c.perkara_id 	
AND (SELECT((SUM(CASE WHEN d.jenis_transaksi = '1' THEN d.jumlah ELSE 0 END))-(SUM(CASE WHEN d.jenis_transaksi = '-1' THEN d.jumlah ELSE 0 END))) AS sisa
FROM perkara_biaya d, perkara e, perkara_pk f WHERE b.perkara_id = d.perkara_id AND e.proses_terakhir_id >= 570 AND e.proses_terakhir_id <= 590 AND d.tahapan_id = 40 AND (e.alur_perkara_id ='15' OR e.alur_perkara_id ='16') AND d.perkara_id = e.perkara_id AND e.perkara_id = f.perkara_id 				  
					  GROUP BY d.perkara_id ORDER BY d.perkara_id DESC) <> 0
					  GROUP BY a.perkara_id ORDER BY a.perkara_id DESC") or die("Query failed with error: ".mysql_error());
					  

echo "<div id='export'>";
echo "<h2>Daftar Sisa Panjar Biaya Perkara Peninjauan Kembali</h2><br>";
echo "<table align=center class='biaya'><tr>

    <th width='3%' rowspan='2'>No.</th>
	<th width='12%' rowspan='2'>Nomor Perkara</th>
	<th width='11%' rowspan='2'>Jenis Perkara</th>
	<th width='18%' rowspan='2'>Nama Pemohon PK</th>
	<th width='11%' rowspan='2'>Tanggal Putusan PK</th>
	<th width='11%' rowspan='2'>Transaksi Terakhir</th>
	<th width='40%' colspan='4'>Biaya Perkara</th>";
echo "<tr>
	
	<th width='10%'>Masuk</th>
	<th width='10%'>Keluar</th>
	<th width='10%'>Sisa Panjar</th>";

	
$no = $posisi+1;
$totalperkara = mysql_num_rows($hasil);
echo "<p align=left>Total Perkara : <b>$totalperkara</b> Perkara</p><br>";
while($r= mysql_fetch_array($hasil)){
$tanggal1  = tgl_indo($r['putusan_pk']);
$tanggal2  = tgl_indo($r['transaksi_terakhir']);
$rp_sisa=formatcurrency($r['sisa_biaya'],'IDR');
$sisa=(($r['sisa_biaya']<0)?'<a>'.$rp_sisa.'</a>':$rp_sisa);
echo "<tr>
  <td align='center'>$no</td>
  <td align='center'>$r[nomor_perkara]</td>
  <td align='center'>$r[jenis_perkara_text] </td>
  <td>$r[pemohon_pk]</td>
  <td align='center'>$tanggal1</td> 
  <td align='center'>$tanggal2</td>  
  <td align='center'>".formatcurrency($r['total_biaya_masuk'],'IDR')."</td>
  <td align='center'>".formatcurrency($r['total_biaya_keluar'],'IDR')."</td>
  <td align='center'><b>$sisa</b></td></tr>";
  $no++;
}
echo "</table><br>";
?>