| Server IP : 88.222.222.201 / Your IP : 216.73.217.80 Web Server : LiteSpeed System : Linux id-dci-web1986.main-hosting.eu 5.14.0-611.26.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Thu Jan 29 05:24:47 EST 2026 x86_64 User : u686484674 ( 686484674) PHP Version : 8.0.30 Disable Function : system, exec, shell_exec, passthru, mysql_list_dbs, ini_alter, dl, symlink, link, chgrp, leak, popen, apache_child_terminate, virtual, mb_send_mail MySQL : OFF | cURL : ON | WGET : ON | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : /home/u686484674/domains/idikotabandung.com/public_html/IDI/superadmin/ |
Upload File : |
<?php include "koneksi.php";
// cek apakah yang mengakses halaman ini sudah login
// if($_SESSION['level']<>"pengurus"){
// header("location:location:../login.php");
// }
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<meta name="description" content="">
<meta name="author" content="">
<head>
<title>IDI | List Kategori</title>
<!-- Custom fonts for this template-->
<link href="vendor/fontawesome-free/css/all.min.css" rel="stylesheet" type="text/css">
<link href="https://fonts.googleapis.com/css?family=Nunito:200,200i,300,300i,400,400i,600,600i,700,700i,800,800i,900,900i" rel="stylesheet">
<!-- Custom styles for this template-->
<link href="css/sb-admin-2.min.css" rel="stylesheet">
<!-- Custom styles for this page -->
<link href="vendor/datatables/dataTables.bootstrap4.min.css" rel="stylesheet">
<style>
body{
background: #dedede;
font-family: 'Helvetica', arial, sans-serif;
font-size: 10px;
}
.table th,
.table td {
padding: 5px 5px;
font-size:12px;
}
.tab-content{
font-size: 10px;
}
#home th,td{
font-size: 9px;
}
#home td{
font-size: 9px;
}
.left{
background: #dedede;
font-family: 'Helvetica', arial, sans-serif;
font-size: 10px;
width: 100%;
height: 130px;
margin-left: 5px;
font-size:10px;
background:white;
padding: 10px;
margin-bottom: 10px;
float: left;
margin-top: 20px;
}
}
</style>
<!-- bootstrap cdn -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-eOJMYsd53ii+scO/bJGFsiCZc+5NDVN2yr8+0RDqr0Ql0h+rP48ckxlpbzKgwra6" crossorigin="anonymous">
</head>
<body id="page-top">
<body>
<!-- Page Wrapper -->
<div id="wrapper">
<!-- Content Wrapper -->
<div id="content-wrapper" class="d-flex flex-column">
<!-- Main Content -->
<div id="content">
<!-- Begin Page Content -->
<div class="container-fluid">
<!-- DataTales Example -->
<div class="card shadow mb-4">
<div class="card-header py-3">
<h6 class="m-0 font-weight-bold text-primary">Data Keuangan</h6>
<?php
//$koneksi = mysqli_connect("localhost","bhaktiag_sales","cleosa2020","bhaktiag_sales");
//$koneksi = mysqli_connect("localhost","root","","agdsales");
?>
<center><h4>Data Pengajuan Rekomendasi<br> Anggota Dan Non Anggota IDI Kota Bandung</h4></center>
<br><br>
- Data laporan Rekom yang sudah Finish. <br>
- Data Rekom Kehilangan, Mutasi dan pengurus Tidak Muncul.<br>
- Data Pencarian Bisa per Periode Waktu dan Bisa Pencarian per kelompok Anggota, Non Anggota dan Anggota Baru.<br>
</p>
<br>
<i class='fa fa-file-excel-o'> EXPORT Ke EXCEL Lap Anggota & Non Anggota : </i>
<a target="_blank" href="expdata_angv2.php"> Lap Keuangan Rekom Anggota & Non Anggota </a>
</br> </br>
<i class='fa fa-file-excel-o'> EXPORT Ke EXCEL Lap Anggota Baru : </i>
<a target="_blank" href="expdata_ang4.php">Export Ke EXCEL Lap Registrasi Anggota Baru</a>
<body>
<br><br>
<center><a href='index.php' class="btn btn-info btn-sm"><span class="glyphicon glyphicon-home"> </span> Home</a></center>
<!-- Tab panes -->
<div class="tab-content">
<div role="tabpanel" class="tab-pane active" id="home">
<br>
<br>
<br>
<?php
$tglBayar2 = date('Y-m-d');
$tglBayar = date('2022-02-01');
$bulanlalu=date('Y-m-d', strtotime('-1 month', strtotime( $tglBayar )));
?>
<div class="container mt-4">
<!-- form filter data berdasarkan range tanggal -->
<form action="lap_rekomgabung2.php" method="get">
<div class="row g-3 align-items-center">
<div class="col-auto">
<label class="col-form-label">Periode</label>
</div>
<div class="col-auto">
<input type="date" class="form-control" name="dari" value ="<?php echo $bulanlalu ?>" required>
</div>
<div class="col-auto">
-
</div>
<div class="col-auto">
<input type="date" class="form-control" name="ke" value ="<?php echo $tglBayar2 ?>" required>
</div>
<div class="col-auto">
<input type="radio" name="anggota" value="semua" checked> Semua
<input type="radio" name="anggota" value=""> Anggota
<input type="radio" name="anggota" value="non"> Non Anggota
<input type="radio" name="anggota" value="AB"> Anggota Baru
</div>
<div class="col-auto">
<button class="btn btn-primary" type="submit">Cari</button>
</div>
</div>
</form>
</p> </p> </p>
</p> </p> </p>
</div> </div> </div> </div> </div>
<div class="left">
<div class="tab-content">
<div role="tabpanel" class="tab-pane active" id="home">
<?php
//jika tanggal dari dan tanggal ke ada maka
if(isset($_GET['dari']) && isset($_GET['ke'])){
$dari = isset($_GET['dari']) ? $_GET['dari'] : null;
$sampai = isset($_GET['ke']) ? $_GET['ke'] : null;
$anggota= isset($_GET['anggota']) ? $_GET['anggota'] : null;
// $dari=$_GET['dari'];
// $sampai=$_GET['ke'];
if($anggota=='semua'){
$sql = "
SELECT h_registrasi.nmlead,h_registrasi.noreg,
IFNULL( h_registrasi.nmlead, 'SUB TOTAL' ) AS nmlead,
IFNULL( b_inv3.tgl_bayar, 'Belum byr' ) AS sales_tim,
(h_registrasi.npa) AS npa,(b_inv3.stat_ang) AS stat_ang,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_dr_umum', b_inv3.bayar, 0)) AS bln_Umum,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_dr_spesialis', b_inv3.bayar, 0)) AS bln_Spesialist,
SUM(IF (b_inv3.perihal = 'rekomendasi_PPDS', b_inv3.bayar,0)) AS bln_PPDS,
SUM(IF (b_inv3.perihal = 'rekomendasi_PPDS_I', b_inv3.bayar,0)) AS bln_PPDSI,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_PPDS_I', b_inv3.bayar, 0)) AS bln_SIPPPDSI,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_PPDS_II', b_inv3.bayar, 0)) AS bln_SIPPPDSII,
SUM(IF (b_inv3.perihal = 'rekomendasi_fellowship', b_inv3.bayar, 0)) AS bln_fellowship,
SUM(IF (b_inv3.perihal = 'rekomendasi_COGS', b_inv3.bayar, 0)) AS bln_COG,
SUM(IF (b_inv3.perihal = 'rekomendasi_PKHI', b_inv3.bayar, 0)) AS bln_PKHI,
SUM(IF (b_inv3.perihal = 'rekomendasi_UKDI', b_inv3.bayar, 0)) AS bln_UKDI,
SUM(IF (b_inv3.perihal = 'rekomendasi_internsip', b_inv3.bayar, 0)) AS bln_Internsip,
SUM( b_inv3.biuran) AS bln_Pembinaan,
SUM(IF (b_inv3.perihal = 'anggota_baru', b_inv3.kta, 0)) AS bln_kta,
SUM(IF (b_inv3.perihal = 'anggota_baru', b_inv3.pgd, 0)) AS bln_Gedung,
COUNT( b_inv3.id_tagihan) AS jml_trx,
SUM( b_inv3.nominal ) AS total_trx
FROM h_registrasi JOIN b_inv3 ON h_registrasi.noreg=b_inv3.noreg
where h_registrasi.status2='finish'
AND h_registrasi.verifikasi1='valid'
AND h_registrasi.verifikasi2='valid'
AND h_registrasi.verifikasi3='valid'
and h_registrasi.perihal <>'p2kb'
and h_registrasi.noreko<>''
AND h_registrasi.perihal<>'kehilangan'
AND h_registrasi.perihal<>'mutasi'
AND h_registrasi.pengurus<>'1'
AND tgl_bayar between '$dari' and '$sampai'
GROUP BY h_registrasi.noreg
WITH ROLLUP
";
$query = mysqli_query($konek, $sql);
$result = mysqli_fetch_all($query,MYSQLI_ASSOC);
//jika pencarian data tidak ditemukan
if(mysqli_num_rows($query)==0){
echo "<font color=red><blink> Pencarian data dari Tanggal: $dari sampai Tanggal : $sampai,tidak ditemukan!</blink></font>";
}else{
echo '<html>
<head>
<title>Pivot Table Dengan PHP dan MySQL</title>
<style>
body {font-family:"open sans", "segoe ui", tahoma, arial}
table {border-collapse: collapse}
.total td {background-color: #f5f5f5 !important;}
.right{text-align: right}
table tr:nth-child(odd) td {
background-color: #fbfbfb;
border-bottom: 1px solid #efefef;
border-top: 1px solid #ececec;
}
table th {
color: #616161;
margin: 0;
padding: 10px 10px;
border: 1px solid #e4e4e4;
text-align: center;
font-size: 14px;
text-transform: uppercase;
background: #efefef;
}
table td {
border-right: 1px solid #ececec;
border-left: 1px solid #ececec;
padding: 7px 15px;
color: #676767;
font-size: 14px;
}
table td:nth-child(n+3) {
text-align: right;
}
</style>
</head>
<body>';
$header_row1 = $header_row2 = '';
foreach ($result[0] as $key => $val)
{
if (strpos($key, 'bln_') !== false)
{
$bln = explode('_', $key);
$header_row1 .= '<th colspan="1">' . $bln[1] . '</th>';
$header_row2 .= '<th>Nilai</th>
';
}
}
echo '
<br>
<table>
<table class="table table-bordered">
<thead>
<tr>
<th rowspan="2">No</th>
<th rowspan="2">Nama</th>
<th rowspan="2">Noreg</th>
<th rowspan="2">Tanggal_Bayar</th>
<th rowspan="2">NPA</th>
<th rowspan="2">Stat</th>
'
. $header_row1 .
'
<th rowspan="2">Total jumlah</th>
<th rowspan="2">Total Nominal</th>
</tr>
</thead>
<tbody id="posTable">';
$no = 1;
foreach ($result as $array)
{
$class = '';
$print_no = $no;
$noreg = $array['noreg'];
if ($array['nmlead'] == 'SUB TOTAL') {
$class = ' class="total"';
$print_no = '';
} else {
$no++;
}
echo '<tr'.$class.'>
<td>' . $print_no . '</td>';
foreach ($array as $key => $val)
{
if ($key !== 'nmlead' && $key !== 'sales_tim'&& $key !== 'noreg'&& $key !== 'npa'&&
$key !== 'verifikasi1'&&
$key !== 'verifikasi2'&&
$key !== 'verifikasi3'&&
$key !== 'perihal'&&
$key !== 'noreko'&&
$key !== 'pengurus'&&
$key !== 'stat_ang' )
{
$val = number_format($val, '0', ',', '.');
}
echo '<td>' . $val . '</td>';
}
?>
<td> <a href='cetak_rekom2.php?noreg=<?php echo $noreg ?>'> cek</a></td>
<?php
echo '</tr>';
}
echo '
</tbody>
</table>
</body>
</html>';
}
}elseif ($anggota=='non'||$anggota==''){
$sql = "
SELECT h_registrasi.nmlead,h_registrasi.noreg,
IFNULL( h_registrasi.nmlead, 'SUB TOTAL' ) AS nmlead,
IFNULL( b_inv3.tgl_bayar, 'Belum byr' ) AS sales_tim,
(h_registrasi.npa) AS npa,(b_inv3.stat_ang) AS stat_ang,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_dr_umum', b_inv3.bayar, 0)) AS bln_Umum,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_dr_spesialis', b_inv3.bayar, 0)) AS bln_Spesialist,
SUM(IF (b_inv3.perihal = 'rekomendasi_PPDS', b_inv3.bayar,0)) AS bln_PPDS,
SUM(IF (b_inv3.perihal = 'rekomendasi_PPDS_I', b_inv3.bayar,0)) AS bln_PPDSI,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_PPDS_I', b_inv3.bayar, 0)) AS bln_SIPPPDSI,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_PPDS_II', b_inv3.bayar, 0)) AS bln_SIPPPDSII,
SUM(IF (b_inv3.perihal = 'rekomendasi_fellowship', b_inv3.bayar, 0)) AS bln_fellowship,
SUM(IF (b_inv3.perihal = 'rekomendasi_COGS', b_inv3.bayar, 0)) AS bln_COG,
SUM(IF (b_inv3.perihal = 'rekomendasi_PKHI', b_inv3.bayar, 0)) AS bln_PKHI,
SUM(IF (b_inv3.perihal = 'rekomendasi_UKDI', b_inv3.bayar, 0)) AS bln_UKDI,
SUM(IF (b_inv3.perihal = 'rekomendasi_internsip', b_inv3.bayar, 0)) AS bln_Internsip,
SUM( b_inv3.biuran) AS bln_iuran,
SUM( b_inv3.pembinaan) AS bln_Pembinaan,
SUM(IF (b_inv3.perihal = 'anggota_baru', b_inv3.kta, 0)) AS bln_kta,
SUM(IF (b_inv3.perihal = 'anggota_baru', b_inv3.pgd, 0)) AS bln_Gedung,
COUNT( b_inv3.id_tagihan) AS jml_trx,
SUM( b_inv3.nominal ) AS total_trx
FROM h_registrasi JOIN b_inv3 ON h_registrasi.noreg=b_inv3.noreg
where h_registrasi.status2='finish'
AND h_registrasi.verifikasi1='valid'
AND h_registrasi.verifikasi2='valid'
AND h_registrasi.verifikasi3='valid'
and h_registrasi.perihal <>'p2kb'
and h_registrasi.noreko<>''
AND h_registrasi.perihal<>'kehilangan'
AND h_registrasi.perihal<>'mutasi'
AND h_registrasi.pengurus<>'1'
AND tgl_bayar between '$dari' and '$sampai' AND h_registrasi.stat_ang='$anggota'
GROUP BY h_registrasi.noreg
WITH ROLLUP
";
$query = mysqli_query($konek, $sql);
$result = mysqli_fetch_all($query,MYSQLI_ASSOC);
//jika pencarian data tidak ditemukan
if(mysqli_num_rows($query)==0){
echo "<font color=red><blink> Pencarian data dari Tanggal: $dari sampai Tanggal : $sampai,tidak ditemukan!</blink></font>";
}else{
echo '<html>
<head>
<title>Pivot Table Dengan PHP dan MySQL</title>
<style>
body {font-family:"open sans", "segoe ui", tahoma, arial}
table {border-collapse: collapse}
.total td {background-color: #f5f5f5 !important;}
.right{text-align: right}
table tr:nth-child(odd) td {
background-color: #fbfbfb;
border-bottom: 1px solid #efefef;
border-top: 1px solid #ececec;
}
table th {
color: #616161;
margin: 0;
padding: 10px 10px;
border: 1px solid #e4e4e4;
text-align: center;
font-size: 14px;
text-transform: uppercase;
background: #efefef;
}
table td {
border-right: 1px solid #ececec;
border-left: 1px solid #ececec;
padding: 7px 15px;
color: #676767;
font-size: 14px;
}
table td:nth-child(n+3) {
text-align: right;
}
</style>
</head>
<body>';
$header_row1 = $header_row2 = '';
foreach ($result[0] as $key => $val)
{
if (strpos($key, 'bln_') !== false)
{
$bln = explode('_', $key);
$header_row1 .= '<th colspan="1">' . $bln[1] . '</th>';
$header_row2 .= '<th>Nilai</th>
';
}
}
echo '
<br>
<table>
<table class="table table-bordered">
<thead>
<tr>
<th rowspan="2">No</th>
<th rowspan="2">Nama</th>
<th rowspan="2">Noreg</th>
<th rowspan="2">Tanggal_Bayar</th>
<th rowspan="2">NPA</th>
<th rowspan="2">Stat</th>
'
. $header_row1 .
'
<th rowspan="2">Total jumlah</th>
<th rowspan="2">Total Nominal</th>
</tr>
</thead>
<tbody id="posTable">';
$no = 1;
foreach ($result as $array)
{
$class = '';
$print_no = $no;
$noreg = $array['noreg'];
if ($array['nmlead'] == 'SUB TOTAL') {
$class = ' class="total"';
$print_no = '';
} else {
$no++;
}
echo '<tr'.$class.'>
<td>' . $print_no . '</td>';
foreach ($array as $key => $val)
{
if ($key !== 'nmlead' && $key !== 'sales_tim'&& $key !== 'noreg'&& $key !== 'npa'&&
$key !== 'verifikasi1'&&
$key !== 'verifikasi2'&&
$key !== 'verifikasi3'&&
$key !== 'perihal'&&
$key !== 'noreko'&&
$key !== 'pengurus'&&
$key !== 'stat_ang'
)
{
$val = number_format($val, '0', ',', '.');
}
echo '<td>' . $val . '</td>';
}
?>
<td> <a href='cetak_ang2.php?noreg=<?php echo $noreg ?>'> cek</a></td>
<?php
echo '</tr>';
}
echo '
</tbody>
</table>
</body>
</html>';
}
?>
<?php
}elseif ($anggota=='AB'){
$sql = "
SELECT h_regang.nmlead,h_regang.noreg,
IFNULL( h_regang.nmlead, 'SUB TOTAL' ) AS nmlead,
IFNULL( b_inv3.tgl_bayar, 'Belum byr' ) AS sales_tim,
(h_regang.npa) AS npa,(b_inv3.stat_ang) AS stat_ang,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_dr_umum', b_inv3.bayar, 0)) AS bln_Umum,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_dr_spesialis', b_inv3.bayar, 0)) AS bln_Spesialist,
SUM(IF (b_inv3.perihal = 'rekomendasi_PPDS', b_inv3.bayar,0)) AS bln_PPDS,
SUM(IF (b_inv3.perihal = 'rekomendasi_PPDS_I', b_inv3.bayar,0)) AS bln_PPDSI,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_PPDS_I', b_inv3.bayar, 0)) AS bln_SIPPPDSI,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_PPDS_II', b_inv3.bayar, 0)) AS bln_SIPPPDSII,
SUM(IF (b_inv3.perihal = 'rekomendasi_fellowship', b_inv3.bayar, 0)) AS bln_fellowship,
SUM(IF (b_inv3.perihal = 'rekomendasi_COGS', b_inv3.bayar, 0)) AS bln_COG,
SUM(IF (b_inv3.perihal = 'rekomendasi_PKHI', b_inv3.bayar, 0)) AS bln_PKHI,
SUM(IF (b_inv3.perihal = 'rekomendasi_UKDI', b_inv3.bayar, 0)) AS bln_UKDI,
SUM(IF (b_inv3.perihal = 'rekomendasi_internsip', b_inv3.bayar, 0)) AS bln_Internsip,
SUM( b_inv3.biuran) AS bln_iuran,
SUM( b_inv3.pembinaan) AS bln_Pembinaan,
SUM(IF (b_inv3.perihal = 'anggota_baru', b_inv3.kta, 0)) AS bln_kta,
SUM(IF (b_inv3.perihal = 'anggota_baru', b_inv3.pgd, 0)) AS bln_Gedung,
COUNT( b_inv3.id_tagihan) AS jml_trx,
SUM( b_inv3.nominal ) AS total_trx
FROM h_regang LEFT JOIN b_inv3 ON h_regang.noreg=b_inv3.noreg where
h_regang.status2='finish'
AND b_inv3.tgl_bayar between '$dari' and '$sampai'
GROUP BY h_regang.noreg
WITH ROLLUP
";
$query = mysqli_query($konek, $sql);
$result = mysqli_fetch_all($query,MYSQLI_ASSOC);
//jika pencarian data tidak ditemukan
if(mysqli_num_rows($query)==0){
echo "<font color=red><blink> Pencarian data dari Tanggal: $dari sampai Tanggal : $sampai,tidak ditemukan!</blink></font>";
}else{
echo '<html>
<head>
<title>Pivot Table Dengan PHP dan MySQL</title>
<style>
body {font-family:"open sans", "segoe ui", tahoma, arial}
table {border-collapse: collapse}
.total td {background-color: #f5f5f5 !important;}
.right{text-align: right}
table tr:nth-child(odd) td {
background-color: #fbfbfb;
border-bottom: 1px solid #efefef;
border-top: 1px solid #ececec;
}
table th {
color: #616161;
margin: 0;
padding: 10px 10px;
border: 1px solid #e4e4e4;
text-align: center;
font-size: 14px;
text-transform: uppercase;
background: #efefef;
}
table td {
border-right: 1px solid #ececec;
border-left: 1px solid #ececec;
padding: 7px 15px;
color: #676767;
font-size: 14px;
}
table td:nth-child(n+3) {
text-align: right;
}
</style>
</head>
<body>';
$header_row1 = $header_row2 = '';
foreach ($result[0] as $key => $val)
{
if (strpos($key, 'bln_') !== false)
{
$bln = explode('_', $key);
$header_row1 .= '<th colspan="1">' . $bln[1] . '</th>';
$header_row2 .= '<th>Nilai</th>
';
}
}
echo '
<br>
<table>
<table class="table table-bordered">
<thead>
<tr>
<th rowspan="2">No</th>
<th rowspan="2">Nama</th>
<th rowspan="2">Noreg</th>
<th rowspan="2">Tanggal_Bayar</th>
<th rowspan="2">NPA</th>
<th rowspan="2">Stat</th>
'
. $header_row1 .
'
<th rowspan="2">Total jumlah</th>
<th rowspan="2">Total Nominal</th>
</tr>
</thead>
<tbody id="posTable">';
$no = 1;
foreach ($result as $array)
{
$class = '';
$print_no = $no;
$noreg = $array['noreg'];
if ($array['nmlead'] == 'SUB TOTAL') {
$class = ' class="total"';
$print_no = '';
} else {
$no++;
}
echo '<tr'.$class.'>
<td>' . $print_no . '</td>';
foreach ($array as $key => $val)
{
if ($key !== 'nmlead' && $key !== 'sales_tim'&& $key !== 'noreg'&& $key !== 'npa'&& $key !== 'stat_ang' )
{
$val = number_format($val, '0', ',', '.');
}
echo '<td>' . $val . '</td>';
}
?>
<td> <a href='cetak_ang2.php?noreg=<?php echo $noreg ?>'> cek</a></td>
<?php
echo '</tr>';
}
echo '
</tbody>
</table>
</body>
</html>';
}
}
?>
<?php
}else{
//jika tidak ada tanggal dari dan tanggal ke maka tampilkan seluruh data
$sql = "
SELECT h_registrasi.nmlead,h_registrasi.noreg,
IFNULL( h_registrasi.nmlead, 'SUB TOTAL' ) AS nmlead,
IFNULL( b_inv3.tgl_bayar, 'Belum byr' ) AS sales_tim,
(h_registrasi.npa) AS npa,(b_inv3.stat_ang) AS stat_ang,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_dr_umum', b_inv3.bayar, 0)) AS bln_Umum,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_dr_spesialis', b_inv3.bayar, 0)) AS bln_Spesialist,
SUM(IF (b_inv3.perihal = 'rekomendasi_PPDS', b_inv3.bayar,0)) AS bln_PPDS,
SUM(IF (b_inv3.perihal = 'rekomendasi_PPDS_I', b_inv3.bayar,0)) AS bln_PPDSI,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_PPDS_I', b_inv3.bayar, 0)) AS bln_SIPPPDSI,
SUM(IF (b_inv3.perihal = 'rekomendasi_SIP_PPDS_II', b_inv3.bayar, 0)) AS bln_SIPPPDSII,
SUM(IF (b_inv3.perihal = 'rekomendasi_fellowship', b_inv3.bayar, 0)) AS bln_fellowship,
SUM(IF (b_inv3.perihal = 'rekomendasi_COGS', b_inv3.bayar, 0)) AS bln_COG,
SUM(IF (b_inv3.perihal = 'rekomendasi_PKHI', b_inv3.bayar, 0)) AS bln_PKHI,
SUM(IF (b_inv3.perihal = 'rekomendasi_UKDI', b_inv3.bayar, 0)) AS bln_UKDI,
SUM(IF (b_inv3.perihal = 'rekomendasi_internsip', b_inv3.bayar, 0)) AS bln_Internsip,
SUM( b_inv3.biuran) AS bln_iuran,
SUM( b_inv3.pembinaan) AS bln_Pembinaan,
SUM(IF (b_inv3.perihal = 'anggota_baru', b_inv3.kta, 0)) AS bln_kta,
SUM(IF (b_inv3.perihal = 'anggota_baru', b_inv3.pgd, 0)) AS bln_Gedung,
COUNT( b_inv3.id_tagihan) AS jml_trx,
SUM( b_inv3.nominal ) AS total_trx
FROM h_registrasi JOIN b_inv3 ON h_registrasi.noreg=b_inv3.noreg
where h_registrasi.status2='finish'
AND h_registrasi.verifikasi1='valid'
AND h_registrasi.verifikasi2='valid'
AND h_registrasi.verifikasi3='valid'
and h_registrasi.perihal <>'p2kb'
and h_registrasi.noreko<>''
AND h_registrasi.perihal<>'kehilangan'
AND h_registrasi.perihal<>'mutasi'
AND h_registrasi.pengurus<>'1'
GROUP BY h_registrasi.noreg
WITH ROLLUP
";
$query = mysqli_query($konek, $sql);
$result = mysqli_fetch_all($query,MYSQLI_ASSOC);
echo '<html>
<head>
<title>Pivot Table Dengan PHP dan MySQL</title>
<style>
body {font-family:"open sans", "segoe ui", tahoma, arial}
table {border-collapse: collapse}
.total td {background-color: #f5f5f5 !important;}
.right{text-align: right}
table tr:nth-child(odd) td {
background-color: #fbfbfb;
border-bottom: 1px solid #efefef;
border-top: 1px solid #ececec;
}
table th {
color: #616161;
margin: 0;
padding: 10px 10px;
border: 1px solid #e4e4e4;
text-align: center;
font-size: 14px;
text-transform: uppercase;
background: #efefef;
}
table td {
border-right: 1px solid #ececec;
border-left: 1px solid #ececec;
padding: 7px 15px;
color: #676767;
font-size: 14px;
}
table td:nth-child(n+3) {
text-align: right;
}
</style>
</head>
<body>';
$header_row1 = $header_row2 = '';
foreach ($result[0] as $key => $val)
{
if (strpos($key, 'bln_') !== false)
{
$bln = explode('_', $key);
$header_row1 .= '<th colspan="1">' . $bln[1] . '</th>';
$header_row2 .= '<th>Nilai</th>
';
}
}
echo '
<br>
<table>
<table class="table table-bordered">
<thead>
<tr>
<th rowspan="2">No</th>
<th rowspan="2">Nama</th>
<th rowspan="2">Noreg</th>
<th rowspan="2">Tanggal Bayar</th>
<th rowspan="2">NPA</th>
<th rowspan="2">Stat</th>
'
. $header_row1 .
'
<th rowspan="2">Total jumlah</th>
<th rowspan="2">Total Nominal</th>
</tr>
</thead>
<tbody id="posTable">';
$no = 1;
foreach ($result as $array)
{
$class = '';
$print_no = $no;
$noreg = $array['noreg'];
if ($array['nmlead'] == 'SUB TOTAL') {
$class = ' class="total"';
$print_no = '';
} else {
$no++;
}
echo '<tr'.$class.'>
<td>' . $print_no . '</td>';
foreach ($array as $key => $val)
{
if ($key !== 'nmlead' && $key !== 'sales_tim'&& $key !== 'noreg'&& $key !== 'npa'&& $key !== 'stat_ang' )
{
$val = number_format($val, '0', ',', '.');
}
echo '<td>' . $val . '</td>';
}
?>
<td> <a href='cetak_ang2.php?noreg=<?php echo $noreg ?>'> Cek</a></td>
<?php
echo '</tr>';
}
echo '
</tbody>
</table>
</body>
</html>';
}
?>
</div>
</div>
</div>
</div>
</body>
</html>
</div>
<!-- proses edit data -->
</div>
<!--Script Javascript-->
<script>
$(document).ready(function(){
$("#posSearch").on("keyup", function() {
var value = $(this).val().toLowerCase();
$("#posTable tr").filter(function() {
$(this).toggle($(this).text().toLowerCase().indexOf(value) > -1)
});
});
});
</script>
</div>
</div>
</div>
</div>
</body>
</html>