      
<?php
include '../../../config/koneksi.php';
session_start();
?>
<!DOCTYPE html>
<html>
   
   
    <head>
        <script src="ambildata.js"></script>
        <script src="efekscrol.js"></script>
        <title>Halaman OPD</title>

     
    </head>
    <body>
   
    <div class="content-wrapper">
   
            
        <div class ="wrapper">
        <div class="panel-body"  style ="overflow-x :auto ; overflow-y :auto ">
            
<button onclick="excelcetak()" class="fas fa-file-excel bg-green btn" style="border-radius: 14px;"> Excel</button>
 
<table width="100%"  id="example1s" class="table table-bordered table-striped" border="1">
<thead>
       <tr style="text-align:center ; background-color:lightgrey" >
       <th style="text-align:center ;vertical-align: middle;"  >No</th>
       <th style="text-align:center ;vertical-align: middle;"  >Kode </th>
       <th style="text-align:center ;vertical-align: middle;"   >SKPD</th>
       <th style="text-align:center ;vertical-align: middle;"   >Jumlah <br>Program</th>
       <th style="text-align:center ;vertical-align: middle;"   >Jumlah <br>Kegiatan</th>
       <th style="text-align:center ;vertical-align: middle;"   >Jumlah <br>Sub Kegiatan</th>
       <th style="text-align:center ;vertical-align: middle;"  >Pagu </th>
       </tr>
       </thead>
       <tbody>
<?php
$no=1;
$sqlskpd  = mysqli_query($koneksi, "SELECT 
 a.kode_sub_skpd,a.sub_skpd,jml.total,p.jp,k.jk,sk.jsk
  from((((( sub_skpd as a)
 left join
 (select kode_sub_skpd, count(distinct kode_program) as jp from renja group by kode_sub_skpd) as p on p.kode_sub_skpd=a.kode_sub_skpd)
 left join
 (select kode_sub_skpd, count(distinct kode_kegiatan) as jk from renja group by kode_sub_skpd) as k on k.kode_sub_skpd=a.kode_sub_skpd)
 left join
 (select kode_sub_skpd, count(distinct kode_sub_kegiatan) as jsk from renja group by kode_sub_skpd) as sk on sk.kode_sub_skpd=a.kode_sub_skpd)
 left join 
 (select kode_sub_skpd , sum(rincian) as total from renja group by kode_sub_skpd)as jml on jml.kode_sub_skpd=a.kode_sub_skpd)
  
    ");
foreach ($sqlskpd as $rowskpd) {
   ?>
   <tr>
        <td><?php echo($no++) ?></td>
       <td><?php echo $rowskpd['kode_sub_skpd']  ?></td>
       <td><?php echo $rowskpd['sub_skpd']  ?></td>
       <td style="text-align:right;vertical-align: middle;"><?php echo ($rowskpd['jp']) ?></td>
       <td style="text-align:right;vertical-align: middle;"><?php echo ($rowskpd['jk']) ?></td>
       <td style="text-align:right;vertical-align: middle;"><?php echo ($rowskpd['jsk']) ?></td>
       <td style="text-align:right;vertical-align: middle;"><?php echo str_replace('.',',',($rowskpd['total'])) ?></td>
</tr>
      <?php

$sqlsd  = mysqli_query($koneksi, "SELECT 
   kode_sub_skpd,sumber_dana, sum(rincian) as totrinc from renja where kode_sub_skpd='$rowskpd[kode_sub_skpd]' group by sumber_dana
    ");
foreach ($sqlsd as $rowsd) {


    ?>
    <tr>
        <td colspan="3"></td>
        <td colspan="3"><?php echo ($rowsd['sumber_dana']) ?></td>
        
 <td style="text-align:right ;vertical-align: middle;"  ><?php echo str_replace('.',',',($rowsd['totrinc'])) ?></td>
</tr>
    <?php
}
    ?>

   </tr>
   <tr style="background-color: gray;">
    <td colspan="7"></td>
</tr>
    <?php
}

    ?>
    <?php

$sqltot  = mysqli_query($koneksi, "SELECT '1', 
    count(distinct a.kode_sub_skpd) as jmlskpd,
 a.kode_sub_skpd,a.sub_skpd,jml.total,
 sum(p.jp) as pjp,
 k.jk,
 sk.jsk
  from((((( sub_skpd as a)
 left join
 (select '1', kode_sub_skpd, count(distinct (concat(kode_program,kode_sub_skpd))) as jp from renja group by kode_program) as p on a.kode_sub_skpd=p.kode_sub_skpd)
 left join
 (select kode_sub_skpd, count(distinct (concat(kode_kegiatan,kode_sub_skpd))) as jk from renja) as k on k.kode_sub_skpd=a.kode_sub_skpd)
 left join
 (select kode_sub_skpd, count(distinct (concat (kode_sub_kegiatan,kode_sub_skpd))) as jsk from renja) as sk on sk.kode_sub_skpd=a.kode_sub_skpd)
 left join 
 (select kode_sub_skpd , sum(rincian) as total from renja )as jml on jml.kode_sub_skpd=a.kode_sub_skpd)
  
    ");
foreach ($sqltot as $rowtot) {
   ?>
   <tr>
    <td></td>
          <td><strong>Total</strong></td>
       <td style="text-align:center; vertical-align: middle;"><strong>OPD</strong></td>
       <td style="text-align:right; vertical-align: middle;"><strong>Program</strong></td>
       <td style="text-align:right; vertical-align: middle;"><strong>Kegiatan</strong></td>
       <td style="text-align:right; vertical-align: middle;"><strong>Sub Kegiatan</strong></td>
       <td style="text-align:right; vertical-align: middle;"><strong>Total</strong></td>
   </tr>
   <tr>
    <td></td>
       <td><strong></strong></td>
       <td style="text-align:center; vertical-align: middle;"><strong><?php echo ($rowtot['jmlskpd']) ?></strong></td>
       <td style="text-align:right; vertical-align: middle;"><strong><?php echo ($rowtot['pjp']) ?></strong></td>
       <td style="text-align:right; vertical-align: middle;"><strong><?php echo ($rowtot['jk']) ?></strong></td>
       <td style="text-align:right; vertical-align: middle;"><strong><?php echo ($rowtot['jsk']) ?></strong></td>
       <td style="text-align:right; vertical-align: middle;"><strong><?php echo str_replace('.',',',($rowtot['total'])) ?></strong></td>
</tr>
<tr style="background-color: gray;"><td colspan="7"></td></tr>
 <tr>
        <td></td>
        <td colspan="5" style="text-align:right ;vertical-align: middle;"  ><strong>Sumber Dana</strong></td>
        <td colspan="1" style="text-align:right ;vertical-align: middle;"  ><strong>pagu</strong></td>
   </tr>
      <?php

$sqlsdt  = mysqli_query($koneksi, "SELECT 
   kode_sub_skpd,sumber_dana, sum(rincian) as totrinc from renja  group by sumber_dana
  
    ");
foreach ($sqlsdt as $rowsdt) {


    ?>
    <tr>
        <td></td>
        <td colspan="5" style="text-align:right ;vertical-align: middle;"  ><strong><?php echo ($rowsdt['sumber_dana']) ?></strong></td>
        <td colspan="1" style="text-align:right ;vertical-align: middle;"  ><strong><?php echo str_replace('.',',',($rowsdt['totrinc'])) ?></strong></td>
   </tr>
    <?php
}
}
    ?>

   </tr>
</tbody>
</table>
<?php

mysqli_close($koneksi);
?> 
  
        
   
          
         <script>
            $(function () {
                $("#example1").DataTable({
                    "responsive": true,
                    "lengthChange": true,
                    "autoWidth": true,
                    "buttons": ["copy", "csv", "excel", "pdf", "print"]
                }).buttons().container().appendTo('#example1_wrapper .col-md-6:eq(0)');
                $('#example2').DataTable({
                    "paging": true,
                    "lengthChange": false,
                    "searching": false,
                    "ordering": true,
                    "info": true,
                    "autoWidth": false,
                    "responsive": true,
                });
            });
</script>
 
</body>
</html>