function view_html($param_arr)
{
$cetak_laporan = CCGetFromGet('cetak_laporan');
if ($cetak_laporan == 'view_excel') {
startExcel("laporan_wp_per_wilayah.xls");
}
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td valign="top">';
$output .= '<table class="grid-table" border="" cellspacing="0" cellpadding="0">
<tr>
<td class="HeaderLeft"></td>
<td class="th"><strong>Daftar WP - ' . $param_arr['kode_wilayah'] . '</strong></td>
<td class="HeaderRight"></td>
</tr>
</table>';
$output .= '<table class="report" cellspacing="0" cellpadding="3px" width="100%" border="1">
<tr>';
$output .= '<th>NO</th>';
$output .= '<th>NPWPD</th>';
$output .= '<th>MERK DAGANG</th>';
$output .= '<th>ALAMAT MERK DAGANG</th>';
$output .= '<th>KECAMATAN</th>';
$output .= '<th>KELURAHAN</th>';
$output .= '<th>AYAT PAJAK</th>';
$output .= '<th>STATUS WP</th>';
$output .= '</tr>';
$no = 1;
$dbConn = new clsDBConnSIKP();
$query = "select z.region_name as kecamatan,w.region_name as kelurahan,y.code as status_code,* FROM T_CUST_ACCOUNT a\r\n\t\tleft join p_vat_type_dtl x on x.p_vat_type_dtl_id = a.p_vat_type_dtl_id \r\n\t\tleft join p_account_status y on y.p_account_status_id = a.p_account_status_id\r\n\t\tleft join p_region z on z.p_region_id = a.brand_p_region_id_kec \r\n\t\tleft join p_region w on w.p_region_id = a.brand_p_region_id_kel \r\n\t\tWHERE f_get_wilayah(a.npwd) = '" . $param_arr['kode_wilayah'] . "'";
if ($param_arr['p_vat_type_id'] != '') {
$query .= "and a.p_vat_type_id = " . $param_arr['p_vat_type_id'];
}
if ($param_arr['p_account_status_id'] != 0) {
$query .= "and a.p_account_status_id = " . $param_arr['p_account_status_id'];
}
$query .= " order by x.vat_code, wp_name";
$dbConn->query($query);
while ($dbConn->next_record()) {
$item = array("t_piutang_pajak_penetapan_final_id" => $dbConn->f("t_piutang_pajak_penetapan_final_id"), "npwd" => $dbConn->f("npwd"), "company_brand" => $dbConn->f("company_brand"), "brand_address_name" => $dbConn->f("brand_address_name") . ' ' . $dbConn->f("brand_address_no"), "status_code" => $dbConn->f("status_code"), "kecamatan" => $dbConn->f("kecamatan"), "kelurahan" => $dbConn->f("kelurahan"), "ayat_pajak" => $dbConn->f("vat_code"));
$output .= '<tr>';
$output .= '<td align="center">' . $no++ . '</td>';
$output .= '<td align="left">' . $item['npwd'] . '</td>';
$output .= '<td align="left">' . $item['company_brand'] . '</td>';
$output .= '<td align="left">' . $item['brand_address_name'] . '</td>';
$output .= '<td align="left">' . $item['kecamatan'] . '</td>';
$output .= '<td align="left">' . $item['kelurahan'] . '</td>';
$output .= '<td align="left">' . $item['ayat_pajak'] . '</td>';
$output .= '<td align="left">' . $item['status_code'] . '</td>';
$output .= '</tr>';
}
$output .= '</table>';
if ($cetak_laporan == 'view_excel') {
echo $output;
exit;
}
return $output;
}
function print_excel($param_arr)
{
startExcel("laporan_harian_sptpd");
echo "<div><h3> LAPORAN PENCETAKAN HARIAN PENERIMAAN SPTPD </h3></div>";
echo "<div><b>Tanggal : " . dateToString($param_arr['date_start']) . " s.d " . dateToString($param_arr['date_end']) . "</b></div><br/>";
$dbConn = new clsDBConnSIKP();
if (empty($param_arr['p_vat_type_id'])) {
$param_arr['p_vat_type_id'] = 'null';
}
if (!empty($param_arr['p_vat_type_dtl_id'])) {
$query = "select *,to_char(start_period, 'DD-MM-YYYY') as start_period_formated,to_char(end_period, 'DD-MM-YYYY') as end_period_formated,to_char(tanggal, 'DD-MM-YYYY') as date_settle_formated from sikp.f_laporan_harian_sptpd2(1," . $param_arr['year_code'] . ",'" . $param_arr['date_start'] . "', '" . $param_arr['date_end'] . "'," . $param_arr['p_vat_type_dtl_id'] . ") ORDER BY tanggal, jenis ASC";
} else {
$query = "select *,to_char(start_period, 'DD-MM-YYYY') as start_period_formated,to_char(end_period, 'DD-MM-YYYY') as end_period_formated,to_char(tanggal, 'DD-MM-YYYY') as date_settle_formated from sikp.f_laporan_harian_sptpd2(" . $param_arr['p_vat_type_id'] . ",2001,'" . $param_arr['date_start'] . "', '" . $param_arr['date_end'] . "') ORDER BY trunc(tanggal),ayat_code_dtl, jenis ASC";
}
//echo $query;exit;
$dbConn->query($query);
$no = 1;
$jumlah_omzet = 0;
$jumlah_ketetapan = 0;
echo "<table border='1'>";
echo "<tr>\r\n\t\t<th>NO</th>\r\n\t\t<th>TANGGAL</th>\t\r\n\t\t<th>AYAT PAJAK</th>\r\n\t\t<th>NAMA</th>\r\n\t\t<th>ALAMAT</th>\r\n\t\t<th>NPWPD</th>\r\n\t\t<th>KOHIR</th>\r\n\t\t<th>MASA PAJAK</th>\r\n\t\t<th>JENIS</th>\r\n\t\t<th>OMZET</th>\r\n\t\t<th>KETETAPAN</th>\r\n\t\t<th>VOP</th>\r\n\t</tr>";
while ($dbConn->next_record()) {
$start_period = $dbConn->f("start_period_formated");
$end_period = $dbConn->f("end_period_formated");
$date_settle = $dbConn->f("date_settle_formated");
$items[] = $item = array('tanggal' => $date_settle, 'no_order' => $dbConn->f("no_order"), 'nama' => $dbConn->f("nama"), 'alamat' => $dbConn->f("alamat"), 'npwpd' => $dbConn->f("npwpd"), 'omzet' => $dbConn->f("omzet"), 'ketetapan' => $dbConn->f("ketetapan"), 'kohir' => $dbConn->f("kohir"), 'start_period' => $start_period, 'end_period' => $end_period, 'jenis_pajak' => $dbConn->f("jenis"), 'jenis_pajak_dtl' => $dbConn->f("jenis_dtl"), 'ayat_code' => $dbConn->f("ayat_code"), 'created_by' => $dbConn->f("created_by"), 'ayat_code_dtl' => $dbConn->f("ayat_code_dtl"));
if (!empty($param_arr['p_vat_type_dtl_id'])) {
//$pdf->RowMultiBorderWithHeight(array($no,$item['tanggal'],$item['no_order'],$item['nama'],$item['alamat'],$item['npwpd'], 2, ',', '.'),$item['kohir'],$item['start_period'].' - '.$item['end_period'],$item['jenis_pajak'],'Rp '.number_format($item['omzet'], 2, ',', '.'),'Rp '.number_format($item['ketetapan']),array('LB','LB','LB','LB','LB','LB','LB','LB','LB','LB','LBR'),6);
echo "<tr>\r\n\t\t\t\t<td>" . $no . "</td> \r\n\t\t\t\t<td>" . $item['tanggal'] . "</td>\r\n\t\t\t\t<td>" . $item['no_order'] . "</td>\r\n\t\t\t\t<td>" . $item['nama'] . "</td>\r\n\t\t\t\t<td>" . $item['alamat'] . "</td>\r\n\t\t\t\t<td>" . $item['npwpd'] . "</td>\r\n\t\t\t\t<td>" . $item['kohir'] . "</td>\r\n\t\t\t\t<td>" . $item['start_period'] . " s.d " . $item['end_period'] . "</td>\r\n\t\t\t\t<td>" . $item['jenis_pajak'] . "</td>\r\n\t\t\t\t<td align='right'>" . number_format($item['omzet'], 2, ',', '.') . "</td>\r\n\t\t\t\t<td align='right'>" . number_format($item['ketetapan'], 0, ',', '.') . "</td>\r\n\t\t\t\t<td>" . $item['created_by'] . "</td>\r\n\t\t\t</tr>";
} else {
//$pdf->RowMultiBorderWithHeight(array($no,$item['tanggal'],$item['ayat_code'].'.'.$item['ayat_code_dtl'],$item['nama'],$item['alamat'],$item['npwpd'],$item['kohir'],$item['start_period'].' - '.$item['end_period'],$item['jenis_pajak'],'Rp '.number_format($item['omzet'], 2, ',', '.'),'Rp '.number_format($item['ketetapan'], 2, ',', '.')),array('LB','LB','LB','LB','LB','LB','LB','LB','LB','LB','LBR'),6);
echo "<tr>\r\n\t\t\t\t<td>" . $no . "</td> \r\n\t\t\t\t<td>" . $item['tanggal'] . "</td>\r\n\t\t\t\t<td>" . $item['ayat_code'] . "." . $item['ayat_code_dtl'] . "</td>\r\n\t\t\t\t<td>" . $item['nama'] . "</td>\r\n\t\t\t\t<td>" . $item['alamat'] . "</td>\r\n\t\t\t\t<td>" . $item['npwpd'] . "</td>\r\n\t\t\t\t<td>" . $item['kohir'] . "</td>\r\n\t\t\t\t<td>" . $item['start_period'] . " s.d " . $item['end_period'] . "</td>\r\n\t\t\t\t<td>" . $item['jenis_pajak'] . "</td>\r\n\t\t\t\t<td align='right'>" . number_format($item['omzet'], 2, ',', '.') . "</td>\r\n\t\t\t\t<td align='right'>" . number_format($item['ketetapan'], 0, ',', '.') . "</td>\r\n\t\t\t\t<td>" . $item['created_by'] . "</td>\r\n\t\t\t</tr>";
}
$jumlah_omzet += $dbConn->f("omzet");
$jumlah_ketetapan += $dbConn->f("ketetapan");
$no++;
}
echo '<tr>
<td colspan="9" align="center"> <b>JUMLAH </b></td>
<td align="right"> <b>' . number_format($jumlah_omzet, 2, ",", ".") . ' </b></td>
<td align="right"> <b>' . number_format($jumlah_ketetapan, 2, ",", ".") . ' </b></td>
<td></td>
</tr>';
echo "</table>";
exit;
}
function print_excel($param_arr)
{
$dbConn = new clsDBConnSIKP();
$query = "select upper(vat_code)as vat_code from p_vat_type where p_vat_type_id = " . $param_arr['p_vat_type_id'];
$vat_code = '';
$dbConn->query($query);
while ($dbConn->next_record()) {
$vat_code = $dbConn->f("vat_code");
}
$query = "select 'SURAT_TEGURAN_'||sequence_no||'_'||replace('" . $vat_code . "',' ','_')||'_PERIODE_'||replace(code,' ','_') AS judul\r\n\t\tfrom t_debt_letter a\r\n\t\tleft join p_finance_period x on a.p_finance_period_id=x.p_finance_period_id\r\n\t\twhere t_customer_order_id=" . $param_arr['t_customer_order_id'];
$judul = '';
$dbConn->query($query);
while ($dbConn->next_record()) {
$judul = $dbConn->f("judul");
}
startExcel("LAPORAN_" . $judul . ".xls");
echo "<div><h3> DAFTAR SURAT TEGURAN PAJAK " . $vat_code . " </h3></div>";
echo "<div><b>Tanggal cetak : " . date('d-M-Y') . "</b></div><br/>";
$no = 1;
echo "<table border='1'>";
echo "<tr>\r\n\t\t<th>NO</th>\r\n\t\t<th>NPWPD</th>\t\r\n\t\t<th>NAMA</th>\r\n\t\t<th>ALAMAT</th>\r\n\t\t<th>JATUH TEMPO</th>\r\n\t\t<th>MASA PAJAK</th>\r\n\t\t<th>BESARNYA</th>\r\n\t\t<th>KETERANGAN</th>\r\n\t</tr>";
$query = "select * from f_debt_letter_list(" . $param_arr['t_customer_order_id'] . ") as a \r\n\t\t LEFT JOIN t_cust_account as b ON a.t_cust_account_id = b.t_cust_account_id\r\n\t\t WHERE a.p_vat_type_id = " . $param_arr['p_vat_type_id'] . " AND b.p_vat_type_dtl_id NOT IN (11, 15, 17, 21, 27, 30, 41, 42, 43)";
$dbConn->query($query);
while ($dbConn->next_record()) {
$items[] = $item = array('npwd' => $dbConn->f("npwd"), 'company_name' => $dbConn->f("company_name"), 'wp_name' => $dbConn->f("wp_name"), 'address_name' => $dbConn->f("address_name"), 'wp_address_name' => $dbConn->f("wp_address_name"), 'wp_address_no' => $dbConn->f("wp_address_no"), 'vat_code' => $dbConn->f("vat_code"), 'due_date' => $dbConn->f("due_date"), 'start_date' => $dbConn->f("start_date"), 'end_date' => $dbConn->f("end_date"), 'debt_amount' => $dbConn->f("debt_amount"), 'description' => $dbConn->f("description"));
if ($item['company_name'] == '-' || empty($item['company_name'])) {
$item['company_name'] = $item['wp_name'];
}
if ($item['address_name'] == '-' || empty($item['address_name'])) {
$item['address_name'] = $item['wp_address_name'] . ' ' . $item['wp_address_no'];
}
echo "<tr>\r\n\t\t\t<td>" . $no . "</td>\r\n\t\t\t<td>" . $item['npwd'] . "</td>\r\n\t\t\t<td>" . $item['company_name'] . "</td>\r\n\t\t\t<td>" . $item['address_name'] . "</td>\r\n\t\t\t<td>" . $item['due_date'] . "</td>\r\n\t\t\t<td>" . $item['start_date'] . " s.d. " . $item['end_date'] . "</td>\r\n\t\t\t<td align='right'>" . number_format($item['debt_amount'], 2, ',', '.') . "</td>\r\n\t\t\t<td>" . $item['description'] . "</td>\r\n\t\t</tr>";
$no++;
}
echo "</table>";
exit;
}
function GetCetakExcel($param_arr)
{
startExcel("rekap_skpdkb_jabatan.xls");
$output = '';
echo $output;
exit;
}
function GetCetakExcel($data, $user, $tgl_penerimaan)
{
startExcel("laporan_harian_versi_2.xls");
$output = '';
$output .= '<table>
<tr>';
$output = '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td valign="top">
<table class="grid-table" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="th" colspan=7 align="center"><strong>TARGET DAN REALISASI</strong></td>
</tr>
<tr>
<td class="th" colspan=7 align="center"><strong>SAMPAI TANGGAL ' . $tgl_penerimaan . '</strong></td>
</tr>
<tr>
</tr>
</table>
<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px">
<tr class="Caption">';
$output .= '<th>NO</th>';
$output .= '<th>JENIS PAJAK</th>';
$output .= '<th>TARGET TAHUNAN</th>';
$output .= '<th>REALISASI ' . $tgl_penerimaan . '</th>';
$output .= '<th>% REAL</th>';
$output .= '<th>SISA REALISASI</th>';
$output .= '<th>RATA-RATA PENERIMAAN PERBULAN</th>';
$output .= '</tr>';
$no = 1;
$total_target = 0;
$total_realisasi = 0;
$bulan = substr($tgl_penerimaan, 3, 2);
for ($i = 0; $i < count($data['nama_jns_pajak']); $i++) {
//print data
$query = "select sum(target_amt)as target \r\n\t\t\t\t\tfrom t_revenue_target_dtl where t_revenue_target_id = \r\n\t\t\t\t\t(select t_revenue_target_id from t_revenue_target\r\n\t\t\t\t\t where p_vat_type_id = " . $data['p_vat_type_id'][$i] . "\r\n\t\t\t\t\t\tand p_year_period_id =\r\n\t\t\t\t\t\t\t(select p_year_period_id from p_year_period \r\n\t\t\t\t\t\t\twhere to_date('" . $tgl_penerimaan . "','dd-mm-yyyy') BETWEEN start_date and end_date\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\t\t)";
//echo $query;exit;
$dbConn = new clsDBConnSIKP();
$dbConn->query($query);
$dbConn->next_record();
$target = $dbConn->f('target');
$output .= '<tr>';
$output .= '<td style="font-weight:bold;" align="left">' . $no . '</td>';
$output .= '<td style="font-weight:bold;" align="left">' . strtoupper($data["nama_jns_pajak"][$i]) . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($target, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data["realisasi"][$i], 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data["realisasi"][$i] / $target * 100, 2, ',', '.') . ' %</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($target - $data["realisasi"][$i], 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data["realisasi"][$i] / $bulan, 0, ',', '.') . '</td>';
$output .= '</tr>';
$no++;
$total_target += $target;
$total_realisasi += $data["realisasi"][$i];
}
$output .= '<tr>';
$output .= '<td style="font-weight:bold;" align="left"></td>';
$output .= '<td style="font-weight:bold;" align="left">JUMLAH</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_target, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi / $total_target * 100, 2, ',', '.') . ' %</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_target - $total_realisasi, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi / $bulan, 0, ',', '.') . '</td>';
$output .= '</tr>';
$output .= '</table></table>';
echo $output;
exit;
}
function PageCetak($data, $user, $tgl_penerimaan)
{
$excel = CCGetFromGet('excel');
if ($excel == 'T') {
startExcel("laporan_harian_per_ketetapan.xls");
}
$kabid = CCGetFromGet('kabid');
$bphtb_row = array();
$output = '';
$output .= '<table>
<tr>';
$output = '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td valign="top">
<table class="grid-table" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="HeaderLeft"></td>
<td class="th"><strong>LAPORAN HARIAN</strong></td>
<td class="HeaderRight"></td>
</tr>
</table>
<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px">
<tr class="Caption">';
$output .= '<th style="text-align:center;" rowspan=2>NO</th>';
$output .= '<th style="text-align:center;" rowspan=2>AYAT</th>';
$output .= '<th style="text-align:center;" rowspan=2>PAJAK/RETRIBUSI</th>';
$output .= '<th style="text-align:center;" colspan=5>JUMLAH HARI INI</th>';
$output .= '<th style="text-align:center;" colspan=5>JUMLAH S/D HARI YANG LALU</th>';
$output .= '<th style="text-align:center;" colspan=5>JUMLAH S/D HARI INI</th>';
$output .= '</tr>';
$output .= '<tr class="Caption">';
$output .= '<th style="text-align:center;">SPTPD</th>';
$output .= '<th style="text-align:center;">SKPDKB Jabatan</th>';
$output .= '<th style="text-align:center;">SKPDKB Pemeriksaan</th>';
$output .= '<th style="text-align:center;">Piutang</th>';
$output .= '<th style="text-align:center;">JUMLAH</th>';
$output .= '<th style="text-align:center;">SPTPD</th>';
$output .= '<th style="text-align:center;">SKPDKB Jabatan</th>';
$output .= '<th style="text-align:center;">SKPDKB Pemeriksaan</th>';
$output .= '<th style="text-align:center;">Piutang</th>';
$output .= '<th style="text-align:center;">JUMLAH</th>';
$output .= '<th style="text-align:center;">SPTPD</th>';
$output .= '<th style="text-align:center;">SKPDKB Jabatan</th>';
$output .= '<th style="text-align:center;">SKPDKB Pemeriksaan</th>';
$output .= '<th style="text-align:center;">Piutang</th>';
$output .= '<th style="text-align:center;">JUMLAH</th>';
$output .= '</tr>';
$no = 1;
$jumlahperjenis = array();
$jumlahtotal = 0;
$jumlahtemp = 0;
$jumlahperjenis_harilalu = array();
$jumlahtotal_harilalu = 0;
$jumlahtemp_harilalu = 0;
$jumlahperjenis_hariini = array();
$jumlahtotal_hariini = 0;
$jumlahtemp_hariini = 0;
$jml_transaksi_per_jenis_pajak = 0;
$jml_transaksi_semua_jenis_pajak = 0;
$jml_transaksi_sampai_kemarin_per_jenis_pajak = 0;
$jml_transaksi_sampai_kemarin_semua_jenis_pajak = 0;
$jml_transaksi_sampai_hari_ini_per_jenis_pajak = 0;
$jml_transaksi_sampai_hari_ini_semua_jenis_pajak = 0;
for ($i = 0; $i < count($data['nomor_ayat']); $i++) {
//print data
$output .= '<tr>';
$output .= '<td>
' . $no . '
</td>
<td>
' . $data["nomor_ayat"][$i] . '
</td>
<td>
P. ' . strtoupper($data["nama_ayat"][$i]) . '
</td>
<td align="right">
' . number_format($data["jml_sptpd_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_jabatan_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_pemeriksaan_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_piutang_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_sptpd_sd_hari_lalu"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_jabatan_sd_hari_lalu"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_pemeriksaan_sd_hari_lalu"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_piutang_sd_hari_lalu"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_sd_hari_lalu"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_sptpd_sd_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_jabatan_sd_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_pemeriksaan_sd_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_piutang_sd_hari_ini"][$i], 0, ',', '.') . '
</td>
<td align="right">
' . number_format($data["jml_sd_hari_ini"][$i], 0, ',', '.') . '
</td>';
$output .= '</tr>';
$no++;
//hitung jml_hari_ini sampai baris ini
$jumlahtemp += $data["jml_hari_ini"][$i];
$jumlahtotal += $data["jml_hari_ini"][$i];
$jumlahtemp_harilalu += $data["jml_sd_hari_lalu"][$i];
$jumlahtotal_harilalu += $data["jml_sd_hari_lalu"][$i];
$jumlahtemp_hariini += $data["jml_sd_hari_ini"][$i];
$jumlahtotal_hariini += $data["jml_sd_hari_ini"][$i];
$jml_transaksi_per_jenis_pajak += $data["jml_transaksi"][$i];
$jml_transaksi_semua_jenis_pajak += $data["jml_transaksi"][$i];
$jml_transaksi_sampai_kemarin_per_jenis_pajak += $data["jml_transaksi_sampai_kemarin"][$i];
$jml_transaksi_sampai_kemarin_semua_jenis_pajak += $data["jml_transaksi_sampai_kemarin"][$i];
$jml_transaksi_sampai_hari_ini_per_jenis_pajak += $data["jml_transaksi_sampai_hari_ini"][$i];
$jml_transaksi_sampai_hari_ini_semua_jenis_pajak += $data["jml_transaksi_sampai_hari_ini"][$i];
//cek apakah perlu bikin baris jumlah
//jika iya, simpan jumlahtemp ke jumlahperjenis, print jumlahtemp, reset jumlahtemp
$jenis = $data["nama_jns_pajak"][$i];
$jenissesudah = $data["nama_jns_pajak"][$i + 1];
if ($jenis != $jenissesudah) {
$jumlahperjenis[] = $jumlahtemp;
$jumlahperjenis_harilalu[] = $jumlahtemp_harilalu;
$jumlahperjenis_hariini[] = $jumlahtemp_hariini;
$output .= '<tr>';
$output .= '<td style="font-weight:bold;" align="center" colspan=3>' . strtoupper($data["nama_jns_pajak"][$i]) . '</td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($jumlahtemp, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($jumlahtemp_harilalu, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($jumlahtemp_hariini, 0, ',', '.') . '</td>';
$output .= '</tr>';
$jumlahtemp = 0;
$jumlahtemp_harilalu = 0;
$jumlahtemp_hariini = 0;
$jml_transaksi_per_jenis_pajak = 0;
$jml_transaksi_sampai_kemarin_per_jenis_pajak = 0;
$jml_transaksi_sampai_hari_ini_per_jenis_pajak = 0;
}
if ($i == count($data['nomor_ayat']) - 1) {
$output .= '<tr>';
$output .= '<td style="font-weight:bold;" align="center" colspan=3>JUMLAH TOTAL</td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($jumlahtotal, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($jumlahtotal_harilalu, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($jumlahtotal_hariini, 0, ',', '.') . '</td>';
$output .= '</tr>';
$jumlahtotal = 0;
$jumlahtotal_harilalu = 0;
$jumlahtotal_hariini = 0;
$jml_transaksi_per_jenis_pajak = 0;
$jml_transaksi_sampai_kemarin_per_jenis_pajak = 0;
$jml_transaksi_sampai_hari_ini_per_jenis_pajak = 0;
}
}
$output .= '</table></table>';
if ($excel == 'T') {
echo $output;
exit;
}
return $output;
}
function GetCetakHTML($param_arr)
{
$doAction = CCGetFromGet('doAction');
if ($doAction == 'view_excel') {
startExcel("laporan_pembayaran_wp_per_3_tahun_bulanan.xls");
}
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0" width="900">
<tr>
<td class="th" colspan = 9 ><strong>LAPORAN PEMBAYARAN WP PER 3 TAHUN BULANAN</strong></td>
</tr>
</table>';
$output .= '<h2>JENIS PAJAK : ' . $param_arr['vat_code'] . ' </h2>';
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
//$dbConn = new clsDBConnSIKP();
//$query="select year_code from p_year_period where p_year_period_id = ".$param_arr['p_year_period_id'];
//echo $query;exit;
$data = array();
//$dbConn->query($query);
//$dbConn->next_record();
$tahun = $param_arr['year_code'];
//$dbConn->close();
$output .= '<th align="center" >NO</th>';
$output .= '<th align="center" >AYAT PAJAK</th>';
$output .= '<th align="center" >NPWPD</th>';
$output .= '<th align="center" >NAMA MERK DAGANG</th>';
$output .= '<th align="center" >ALAMAT MERK DAGANG</th>';
$output .= '<th align="center" >TGL PENGUKUHAN</th>';
$output .= '<th align="center" >MASA PAJAK</th>';
$output .= '<th align="center" >' . ($tahun - 2) . '</th>';
$output .= '<th align="center" >' . ($tahun - 1) . '</th>';
$output .= '<th align="center" >' . $tahun . '</th>';
$output .= '</tr>';
if ($param_arr['p_vat_type_dtl_id'] == '') {
$param_arr['p_vat_type_dtl_id'] = 0;
}
$dbConn = new clsDBConnSIKP();
$query = "(select DISTINCT (c.npwd), c.t_cust_account_id,c.company_brand,regexp_replace(c.brand_address_name, '\r|\n', '', 'g')||' '||regexp_replace(c.brand_address_no, '\r|\n', '', 'g') as alamat,\r\n\t\t\tto_char(active_date,'dd-mm-yyyy') as active_date,\r\n\t\t\tc.p_vat_type_dtl_id, vat_code\r\n\t\t\tfrom t_cust_account c\r\n\t\t\tleft join p_vat_type_dtl d on d.p_vat_type_dtl_id=c.p_vat_type_dtl_id\r\n\t\t\twhere (case \r\n\t\t\t\t\t\twhen " . $param_arr['p_vat_type_dtl_id'] . " = 0 then true\r\n\t\t\t\t\t\twhen " . $param_arr['p_vat_type_dtl_id'] . " = 10 then c.p_vat_type_dtl_id in (10,9) \r\n\t\t\t\t\t\telse c.p_vat_type_dtl_id = " . $param_arr['p_vat_type_dtl_id'] . " \r\n\t\t\t\t\tend)\r\n\t\t\tand " . $param_arr['p_vat_type_id'] . " = c.p_vat_type_id \r\n\t\t\tand c.p_account_status_id = 1\r\n\t\t\t)\r\n\t\t\tunion\r\n\t\t\t(select DISTINCT (c.npwd),c.t_cust_account_id,c.company_brand,regexp_replace(c.brand_address_name, '\r|\n', '', 'g')||' '||regexp_replace(c.brand_address_no, '\r|\n', '', 'g') as alamat,\r\n\t\t\tto_char(active_date,'dd-mm-yyyy') as active_date,\r\n\t\t\tc.p_vat_type_dtl_id, vat_code\r\n\t\t\tfrom t_cust_account c\r\n\t\t\tleft join p_vat_type_dtl d on d.p_vat_type_dtl_id=c.p_vat_type_dtl_id\r\n\t\t\twhere (case \r\n\t\t\t\t\t\twhen " . $param_arr['p_vat_type_dtl_id'] . " = 0 then true\r\n\t\t\t\t\t\twhen " . $param_arr['p_vat_type_dtl_id'] . " = 10 then c.p_vat_type_dtl_id in (10,9) \r\n\t\t\t\t\t\telse c.p_vat_type_dtl_id = " . $param_arr['p_vat_type_dtl_id'] . " \r\n\t\t\t\t\tend)\r\n\t\t\tand " . $param_arr['p_vat_type_id'] . " = c.p_vat_type_id \r\n\t\t\tand c.p_account_status_id != 1\r\n\t\t\tand (\r\n\t\t\t\t\t(\r\n\t\t\t\t\t\tselect start_date\r\n\t\t\t\t\t\tfrom t_payment_receipt x\r\n\t\t\t\t\t\tleft join p_finance_period y on x.p_finance_period_id = y.p_finance_period_id\r\n\t\t\t\t\t\twhere x.t_cust_account_id = c.t_cust_account_id\r\n\t\t\t\t\t\tORDER BY y.start_date desc\r\n\t\t\t\t\t\tlimit 1\r\n\t\t\t\t\t) >= to_date('01-01-2014')\r\n\t\t\t\t)\r\n\t\t\t)\r\n\t\t\torder by p_vat_type_dtl_id,company_brand,npwd ";
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
asort($data);
/*echo "<pre>";
print_r($data);
echo "</pre>";
exit;*/
//echo $data[1]['t_cust_account_id'];
//exit;
$masa = array();
$masa[0] = '<td>Januari</td>';
/*$masa[1]= '<td>Februari</td>';
$masa[2]= '<td>Maret</td>';
$masa[3]= '<td>April</td>';
$masa[4]= '<td>Mei</td>';
$masa[5]= '<td>Juni</td>';
$masa[6]= '<td>Juli</td>';
$masa[7]= '<td>Agustus</td>';
$masa[8]= '<td>September</td>';
$masa[9]= '<td>Oktober</td>';
$masa[10]= '<td>November</td>';
$masa[11]= '<td>Desember</td>';*/
for ($k = 0; $k < count($data); $k++) {
$json_url = "http://localhost/mpd/services/pembayaran_wp_bulanan.php?p_finance_period_id=" . $param_arr['p_finance_period_id'] . "&t_cust_account_id=" . $data[$k]['t_cust_account_id'] . "&p_vat_type_dtl_id=" . $param_arr['p_vat_type_dtl_id'];
//echo $json_url;exit;
$json = file_get_contents($json_url);
$data_json = json_decode($json, TRUE);
/*echo "<pre>";
print_r($data_json);
echo "</pre>";
exit;*/
$arr_month = array();
$html = array();
foreach ($data_json as $tahun) {
$i = 0;
$j = 0;
foreach ($tahun['arr_data'] as $bulan) {
if ($j == 0) {
$no = '<tr><td>' . ($k + 1) . ' </td>';
$vat_code = '<td>' . $data[$k]['vat_code'] . '</td>';
$npwpd = '<td>' . $bulan['npwd'] . '</td>';
$company_brand = '<td>' . $data[$k]['company_brand'] . '</td>';
$alamat = '<td>' . $data[$k]['alamat'] . '</td>';
$active_date = '<td>' . $data[$k]['active_date'] . '</td>';
$masa = '<td>' . substr($bulan['code'], 0, -5) . '</td>';
$jumlah_bayar = '<td>' . number_format($bulan['pajak'], 2, ',', '.') . '</td>';
$html[$i] .= $no . $vat_code . $npwpd . $company_brand . $alamat . $active_date . $masa . $jumlah_bayar;
} else {
$html[$i] .= '<td>' . number_format($bulan['pajak'], 2, ',', '.') . '</td>';
if ($j == 2) {
$html[$i] .= '</tr>';
}
}
$j++;
$i++;
}
}
$output .= implode($html, '');
}
//echo '<script>';
//return $output;
//echo '</script>';
/*$j = 0;
$i = 0;
foreach ($data_json as $tahun){
//echo "<pre>";
//print_r($tahun['arr_data']);
//echo "</pre>";
//exit;
foreach ($tahun['arr_data'] as $bulan){
/*echo "<pre>";
print_r($bulan['code']);
echo "</pre>";
exit;*/
/* $output.='<tr><td align="center" >'.($i+1).'</td>';
$output.='<td align="left" >'.$bulan['npwd'].'</td>';
$output.='<td align="left" >'.$bulan['code'].'</td>';
$output.='<td align="right" >'.number_format($bulan['pajak'], 2, ',', '.').'</td>';
$output.='</tr>';
$i++;
}
}*/
if ($doAction == 'view_excel') {
echo $output;
exit;
} else {
return $output;
}
}
function GetCetakHTML($param_arr)
{
$doAction = CCGetFromGet('doAction');
if ($doAction == 'view_excel') {
startExcel("laporan_perkembangan_jumlah_wajib_pajak.xls");
}
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
if ($doAction != 'view_excel') {
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0" width="900">
<tr>
<td class="HeaderLeft"><img border="0" alt="" src="../Styles/sikp/Images/Spacer.gif"></td>
<td class="th"><strong>LAPORAN PEMBAYARAN PER JENIS DAN MASA PAJAK</strong></td>
<td class="HeaderRight"><img border="0" alt="" src="../Styles/sikp/Images/Spacer.gif"></td>
</tr>
</table>';
}
$output .= '<h2 style="color:black;" align="center">REKAPITULASI PEMBAYARAN PAJAK HOTEL, RESTORAN, HIBURAN, PARKIR, DAN PPJ</h2>';
if ($param_arr['npwpd_jabatan'] == 2) {
$output .= '<h2 style="color:black;" align="center">NPWPD JABATAN</h2>';
}
if ($param_arr['kode_wilayah'] == 'semua') {
$output .= '<h2 style="color:black;" align="center">KOTA BANDUNG</h2>';
}
if ($param_arr['kode_wilayah'] == 'lainnya') {
$output .= '<h2 style="color:black;" align="center">WILAYAH LAINNYA</h2>';
}
if ($param_arr['kode_wilayah'] != 'semua' && $param_arr['kode_wilayah'] != 'lainnya') {
$output .= '<h2 style="color:black;" align="center">' . $param_arr['kode_wilayah'] . '</h2>';
}
$output .= '<br>';
$tanggal = CCGetFromGet('date_end_laporan', '31-12-2014');
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th align="center" rowspan=2 >NO</th>';
$output .= '<th align="center" rowspan=2 >URAIAN JENIS PAJAK</th>';
$output .= '<th align="center" colspan=3 >JANUARI</th>';
$output .= '<th align="center" colspan=3 >FEBRUARI</th>';
$output .= '<th align="center" colspan=3 >MARET</th>';
$output .= '<th align="center" colspan=3 >APRIL</th>';
$output .= '<th align="center" colspan=3 >MEI</th>';
$output .= '<th align="center" colspan=3 >JUNI</th>';
$output .= '<th align="center" colspan=3 >JULI</th>';
$output .= '<th align="center" colspan=3 >AGUSTUS</th>';
$output .= '<th align="center" colspan=3 >SEPTEMBER</th>';
$output .= '<th align="center" colspan=3 >OKTOBER</th>';
$output .= '<th align="center" colspan=3 >NOVEMBER</th>';
$output .= '<th align="center" colspan=3 >DESEMBER</th>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >BAYAR</th>';
$output .= '<th align="center" >NILAI</th>';
$output .= '</tr>';
$dbConn = new clsDBConnSIKP();
$query = "select case when p_vat_type_dtl_id = 35 then 'RUMAH KOS' \r\n\t\t\t\t\t\twhen p_vat_type_dtl_id = 18 then 'DISKOTIK/KLUB MALAM' \r\n\t\t\t\t\t\twhen p_vat_type_dtl_id = 13 then 'PANTI PIJAT/SPA/REFLEKSI' \r\n\t\t\t\t\t\twhen p_vat_type_dtl_id = 31 then 'HIBURAN INSIDENTIL' \r\n\t\t\t\t\t\telse ayat_pajak end as ayat_pajak_2,* \r\n\t\t\tfrom \r\n\t\t\t(select upper(b.vat_code) as jenis_pajak ,a.vat_code as ayat_pajak,a.p_vat_type_dtl_id,a.p_vat_type_id\r\n\t\t\tfrom p_vat_type_dtl a\r\n\t\t\tleft join p_vat_type b on b.p_vat_type_id = a.p_vat_type_id\r\n\t\t\twhere a.p_vat_type_id in (1,2,3,4,5)\r\n\t\t\t\tand a.p_vat_type_dtl_id not in (9,20,44,15,41,12,17,21,42,43,27,30)\r\n\t\t\tORDER BY a.p_vat_type_id,a.code)";
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
$j = 0;
$total = array();
for ($bulan = 1; $bulan <= 12; $bulan++) {
$total[$bulan]['aktif'] = 0;
$total[$bulan]['bayar'] = 0;
$total[$bulan]['nilai'] = 0;
}
for ($i = 0; $i < count($data); $i++) {
if ($data[$i]['jenis_pajak'] != $data[$i - 1]['jenis_pajak']) {
$output .= '<tr><td align="center" ><b>' . ($j + 1) . '</b></td>';
$output .= '<td align="left" ><b>' . $data[$i]['jenis_pajak'] . '</b></td>';
for ($bulan = 1; $bulan <= 12; $bulan++) {
$data_per_bulan = getDataPerJenis($data[$i]['p_vat_type_id'], '01-' . str_pad($bulan, 2, "0", STR_PAD_LEFT) . '-' . $param_arr['year_code']);
$output .= '<td align="right" >' . $data_per_bulan['aktif'] . '</td>';
$output .= '<td align="right" >' . $data_per_bulan['bayar'] . '</td>';
$output .= '<td align="right" >' . number_format($data_per_bulan['nilai'], 2, ',', '.') . '</td>';
$total[$bulan]['aktif'] += $data_per_bulan['aktif'];
$total[$bulan]['bayar'] += $data_per_bulan['bayar'];
$total[$bulan]['nilai'] += $data_per_bulan['nilai'];
}
$output .= '</tr>';
$j++;
}
if ($data[$i]['jenis_pajak'] != 'PAJAK PARKIR' && $data[$i]['jenis_pajak'] != 'PAJAK PPJ') {
$output .= '<tr><td align="center" ></td>';
$output .= '<td align="left" >- ' . $data[$i]['ayat_pajak_2'] . '</td>';
//get data perbulan
for ($bulan = 1; $bulan <= 12; $bulan++) {
$data_per_bulan = getData($data[$i]['p_vat_type_dtl_id'], '01-' . str_pad($bulan, 2, "0", STR_PAD_LEFT) . '-' . $param_arr['year_code']);
$output .= '<td align="right" >' . $data_per_bulan['aktif'] . '</td>';
$output .= '<td align="right" >' . $data_per_bulan['bayar'] . '</td>';
$output .= '<td align="right" >' . number_format($data_per_bulan['nilai'], 2, ',', '.') . '</td>';
}
$output .= '</tr>';
}
}
$output .= '<tr><td align="center" ></td>';
$output .= '<td align="left" ><b>JUMLAH</b></td>';
for ($bulan = 1; $bulan <= 12; $bulan++) {
$output .= '<td align="right" >' . $total[$bulan]['aktif'] . '</td>';
$output .= '<td align="right" >' . $total[$bulan]['bayar'] . '</td>';
$output .= '<td align="right" >' . number_format($total[$bulan]['nilai'], 2, ',', '.') . '</td>';
}
$output .= '</tr>';
$output .= '</table>';
if ($doAction == 'view_excel') {
echo $output;
exit;
}
return $output;
}
function print_excel($param_arr)
{
startExcel("teller_report_" . date('Y-m-d'));
echo "<div><h3> LAPORAN PENERIMAAN BPHTB </h3></div>";
echo '<table>
<tr>
<td><b>TANGGAL</b></td>
<td><b>: ' . dateToString(date('Y-m-d'), true) . '</b></td>
</tr>
<tr>
<td><b>NAMA USER</b></td>
<td><b>: ' . $param_arr['uid'] . '</b></td>
</tr>
</table>
';
echo '<table border="1">';
echo '<tr>
<th>NO</th>
<th>NO TRANSAKSI</th>
<th>NOP</th>
<th>TGL</th>
<th>NAMA</th>
<th>ALAMAT</th>
<th>KELURAHAN</th>
<th>KECAMATAN</th>
<th>LUAS TANAH</th>
<th>LUAS BGN</th>
<th>NJOP (Rp)</th>
<th>TOTAL BAYAR (Rp)</th>
</tr>';
$dbConn = new clsDBConnSIKP();
$whereClause = " WHERE ";
$whereClause .= " to_char(a.payment_date, 'YYYY-mm-dd') = '" . date("Y-m-d") . "'";
$whereClause .= " AND a.p_cg_terminal_id = '" . $param_arr['uid'] . "'";
$query = "SELECT a.receipt_no, b.njop_pbb, to_char(a.payment_date, 'YYYY-MM-DD') AS payment_date,\r\n\t\t\t\t\tb.wp_name, b.wp_address_name, kelurahan.region_name AS kelurahan_name, kecamatan.region_name AS kecamatan_name, b.land_area, b.building_area, b.land_total_price, a.payment_amount \r\n\t\t\t\t\tFROM t_payment_receipt_bphtb AS a\r\n\t\t\tLEFT JOIN t_bphtb_registration AS b ON a.t_bphtb_registration_id = b.t_bphtb_registration_id\r\n\t\t\tLEFT JOIN p_region AS kelurahan ON b.wp_p_region_id_kel = kelurahan.p_region_id\r\n\t\t\tLEFT JOIN p_region AS kecamatan ON b.wp_p_region_id_kec = kecamatan.p_region_id";
$query .= $whereClause;
$query .= " ORDER BY a.receipt_no ASC";
$dbConn->query($query);
$total_nilai_penerimaan = 0;
$no = 1;
while ($dbConn->next_record()) {
$item = array('receipt_no' => $dbConn->f("receipt_no"), 'njop_pbb' => $dbConn->f("njop_pbb"), 'payment_date' => $dbConn->f("payment_date"), 'wp_name' => $dbConn->f("wp_name"), 'wp_address_name' => $dbConn->f("wp_address_name"), 'kelurahan_name' => $dbConn->f("kelurahan_name"), 'kecamatan_name' => $dbConn->f("kecamatan_name"), 'land_area' => $dbConn->f("land_area"), 'building_area' => $dbConn->f("building_area"), 'land_total_price' => $dbConn->f("land_total_price"), 'payment_amount' => $dbConn->f("payment_amount"));
echo '<tr>';
echo '<td align="center">' . $no . '</td>';
echo '<td align="left"> ' . $item['receipt_no'] . '</td>';
echo '<td align="left"> ' . $item['njop_pbb'] . '</td>';
echo '<td align="center">' . dateToString($item['payment_date']) . '</td>';
echo '<td align="left">' . trim(strtoupper($item['wp_name'])) . '</td>';
echo '<td align="left">' . $item['wp_address_name'] . '</td>';
echo '<td align="left">' . $item['kelurahan_name'] . '</td>';
echo '<td align="left">' . $item['kecamatan_name'] . '</td>';
echo '<td align="right">' . number_format($item['land_area'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['building_area'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['land_total_price'], 2, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['payment_amount'], 2, ",", ".") . '</td>';
echo '</tr>';
$total_nilai_penerimaan += $item['payment_amount'];
$no++;
}
echo '<tr>
<td align="center" colspan="11"> <b>TOTAL</b> </td>
<td align="right"><b>' . number_format($total_nilai_penerimaan, 2, ",", ".") . ' </b></td>
</tr>';
echo '</table>';
exit;
}
function GetCetakHTML($param_arr)
{
$doAction = CCGetFromGet('doAction');
if ($doAction == 'view_excel') {
startExcel("laporan_perkembangan_jumlah_wajib_pajak.xls");
}
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
if ($doAction != 'view_excel') {
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0" width="900">
<tr>
<td class="HeaderLeft"><img border="0" alt="" src="../Styles/sikp/Images/Spacer.gif"></td>
<td class="th"><strong>LAPORAN DETIL PEMBAYARAN PER JENIS DAN MASA PAJAK</strong></td>
<td class="HeaderRight"><img border="0" alt="" src="../Styles/sikp/Images/Spacer.gif"></td>
</tr>
</table>';
}
$output .= '<h2 style="color:black;" align="center">PEMBAYARAN PAJAK HOTEL, RESTORAN, HIBURAN, PARKIR, DAN PPJ</h2>';
if ($param_arr['npwpd_jabatan'] == 2) {
$output .= '<h2 style="color:black;" align="center">NPWPD JABATAN</h2>';
}
if ($param_arr['kode_wilayah'] == 'semua') {
$output .= '<h2 style="color:black;" align="center">KOTA BANDUNG</h2>';
}
if ($param_arr['kode_wilayah'] == 'lainnya') {
$output .= '<h2 style="color:black;" align="center">WILAYAH LAINNYA</h2>';
}
if ($param_arr['kode_wilayah'] != 'semua' && $param_arr['kode_wilayah'] != 'lainnya') {
$output .= '<h2 style="color:black;" align="center">' . $param_arr['kode_wilayah'] . '</h2>';
}
$output .= '<br>';
$tanggal = CCGetFromGet('date_end_laporan', '31-12-2014');
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th align="center">NO</th>';
$output .= '<th align="center">JENIS PAJAK</th>';
$output .= '<th align="center">URAIAN JENIS PAJAK</th>';
$output .= '<th align="center">NPWPD</th>';
$output .= '<th align="center">OBJEK PAJAK</th>';
$output .= '<th align="center">ALAMAT</th>';
$output .= '<th align="center">MASA PAJAK</th>';
$output .= '<th align="center">TOTAL BAYAR</th>';
$output .= '<th align="center">TANGGAL BAYAR</th>';
$output .= '<th align="center">WILAYAH</th>';
$output .= '</tr>';
$dbConn = new clsDBConnSIKP();
$query = "select \r\n\t\t\t\tf_get_wilayah(b.npwd) as wilayah,e.vat_code as jenis_pajak,\r\n\t\t\t\tf.vat_code as ayat_pajak,d.code as masa_pajak,\r\n\t\t\t\tto_char(payment_date,'dd-mm-yyyy') as tanggal_bayar,*\r\n\t\t\tfrom t_vat_setllement a\r\n\t\t\t\tleft join t_cust_account b on a.t_cust_account_id=b.t_cust_account_id\r\n\t\t\t\tleft join t_payment_receipt c on c.t_vat_setllement_id=a.t_vat_setllement_id\r\n\t\t\t\tLEFT JOIN p_finance_period d on d.p_finance_period_id=a.p_finance_period_id\r\n\t\t\t\tleft join p_vat_type e on e.p_vat_type_id = b.p_vat_type_id\r\n\t\t\t\tleft join p_vat_type_dtl f on f.p_vat_type_dtl_id = b.p_vat_type_dtl_id\r\n\t\t\twhere p_account_status_id = 1 \r\n\t\t\t\t--and trunc(last_satatus_date) <= (select end_date from p_finance_period where start_date = to_date('" . $start_date . "','dd-mm-yyyy'))\r\n\t\t\t\tand c.t_payment_receipt_id is not null\r\n\t\t\t\tand trunc(payment_date) between to_date('" . $param_arr['tgl_penerimaan'] . "','dd-mm-yyyy') and to_date('" . $param_arr['tgl_penerimaan_last'] . "','dd-mm-yyyy')\r\n\t\t\t\tand case when " . $param_arr['npwpd_jabatan'] . " = 1 then true else npwpd_jabatan='Y' end\r\n\t\t\t\tand case when '" . $param_arr['kode_wilayah'] . "' = 'semua' then true \r\n\t\t\t\t\twhen '" . $param_arr['kode_wilayah'] . "' = 'lainnya' then f_get_wilayah(b.npwd)='-' \r\n\t\t\t\t\telse '" . $param_arr['kode_wilayah'] . "' = f_get_wilayah(b.npwd) end\r\n\t\t\torder by wilayah, company_brand, b.npwd, d.start_date";
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
for ($i = 0; $i < count($data); $i++) {
$output .= '</tr>';
$output .= '<td align="left" >' . ($i + 1) . '</td>';
$output .= '<td align="left" >' . $data[$i]['jenis_pajak'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['ayat_pajak'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['npwd'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['company_brand'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['brand_address_name'] . ' ' . $data[$i]['brand_address_no'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['masa_pajak'] . '</td>';
$output .= '<td align="right" >' . number_format($data[$i]['payment_amount'], 2, ',', '.') . '</td>';
$output .= '<td align="left" >' . $data[$i]['tanggal_bayar'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['wilayah'] . '</td>';
$output .= '</tr>';
}
$output .= '</table>';
if ($doAction == 'view_excel') {
echo $output;
exit;
}
return $output;
}
function cetak_excel($param_arr)
{
$dbConn = new clsDBConnSIKP();
$whereCondition = "";
if ($param_arr['npwpd'] != "") {
$whereCondition .= " AND A .npwd = '" . $param_arr['npwpd'] . "' ";
}
$query = "SELECT\r\n\t\t\t\t\tnvl (nama, company_name) AS new_company_name,\r\n\t\t\t\t\tmasa_awal,\r\n\t\t\t\t\tmasa_akhir,\r\n\t\t\t\t\tdata_transaksi.*\r\n\t\t\t\tFROM\r\n\t\t\t\t\t(\r\n\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t*\r\n\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t\t\tC .npwd,\r\n\t\t\t\t\t\t\t\t\tA .t_vat_setllement_id,\r\n\t\t\t\t\t\t\t\t\tC .company_name,\r\n\t\t\t\t\t\t\t\t\tb.code AS periode_pelaporan,\r\n\t\t\t\t\t\t\t\t\tto_char(\r\n\t\t\t\t\t\t\t\t\t\tA .settlement_date,\r\n\t\t\t\t\t\t\t\t\t\t'DD-MON-YYYY'\r\n\t\t\t\t\t\t\t\t\t) tgl_pelaporan,\r\n\t\t\t\t\t\t\t\t\tA .total_trans_amount AS total_transaksi,\r\n\t\t\t\t\t\t\t\t\tA .total_vat_amount AS total_pajak,\r\n\t\t\t\t\t\t\t\t\tnvl (A .total_penalty_amount, 0) AS total_denda,\r\n\t\t\t\t\t\t\t\t\td.receipt_no AS kuitansi_pembayaran,\r\n\t\t\t\t\t\t\t\t\tto_char(\r\n\t\t\t\t\t\t\t\t\t\tpayment_date,\r\n\t\t\t\t\t\t\t\t\t\t'DD-MON-YYYY HH24:MI:SS'\r\n\t\t\t\t\t\t\t\t\t) tgl_pembayaran,\r\n\t\t\t\t\t\t\t\t\td.payment_amount,\r\n\t\t\t\t\t\t\t\t\tC .t_cust_account_id,\r\n\t\t\t\t\t\t\t\t\tb.p_finance_period_id,\r\n\t\t\t\t\t\t\t\t\tto_char(\r\n\t\t\t\t\t\t\t\t\t\tA .start_period,\r\n\t\t\t\t\t\t\t\t\t\t'DD-MON-YYYY'\r\n\t\t\t\t\t\t\t\t\t) AS periode_awal_laporan,\r\n\t\t\t\t\t\t\t\t\tto_char(A .end_period, 'DD-MON-YYYY') AS periode_akhir_laporan,\r\n\t\t\t\t\t\t\t\t\te.code AS type_code,\r\n\t\t\t\t\t\t\t\t\tnvl (\r\n\t\t\t\t\t\t\t\t\t\tA .debt_vat_amt,\r\n\t\t\t\t\t\t\t\t\t\tA .total_vat_amount\r\n\t\t\t\t\t\t\t\t\t) AS debt_vat_amt,\r\n\t\t\t\t\t\t\t\t\tnvl (A .db_increasing_charge, 0) AS db_increasing_charge,\r\n\t\t\t\t\t\t\t\t\tnvl (\r\n\t\t\t\t\t\t\t\t\t\tA .debt_vat_amt,\r\n\t\t\t\t\t\t\t\t\t\tA .total_vat_amount\r\n\t\t\t\t\t\t\t\t\t) + nvl (A .db_increasing_charge, 0) + nvl (A .db_interest_charge, 0) + nvl (A .total_penalty_amount, 0) AS total_hrs_bayar,\r\n\t\t\t\t\t\t\t\t\tnvl (A .db_increasing_charge, 0) AS kenaikan,\r\n\t\t\t\t\t\t\t\t\tnvl (A .db_interest_charge, 0) AS kenaikan1,\r\n\t\t\t\t\t\t\t\t\tA .p_vat_type_dtl_id,\r\n\t\t\t\t\t\t\t\t\tA .no_kohir,\r\n\t\t\t\t\t\t\t\t\tto_char(A .due_date, 'DD-MON-YYYY') AS jatuh_tempo,\r\n\t\t\t\t\t\t\t\t\tsettlement_date,\r\n\t\t\t\t\t\t\t\t\tb.start_date,\r\n\t\t\t\t\t\t\t\t\tA .is_settled\r\n\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t\tt_vat_setllement A,\r\n\t\t\t\t\t\t\t\t\tp_finance_period b,\r\n\t\t\t\t\t\t\t\t\tt_cust_account C,\r\n\t\t\t\t\t\t\t\t\tt_payment_receipt d,\r\n\t\t\t\t\t\t\t\t\tp_settlement_type e\r\n\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\t\tA .p_finance_period_id = b.p_finance_period_id\r\n\t\t\t\t\t\t\t\tAND A .t_cust_account_id = C .t_cust_account_id\r\n\t\t\t\t\t\t\t\tAND EXTRACT (YEAR FROM A .settlement_date) = '" . $param_arr['year_code'] . "'\r\n\t\t\t\t\t\t\t\tAND d.t_payment_receipt_id is null\r\n\t\t\t\t\t\t\t\t" . $whereCondition . "\r\n\t\t\t\t\t\t\t\tAND A .t_vat_setllement_id = d.t_vat_setllement_id (+)\r\n\t\t\t\t\t\t\t\tAND A .p_settlement_type_id = e.p_settlement_type_id\r\n\t\t\t\t\t\t\t) AS hasil\r\n\t\t\t\t\t\tLEFT JOIN p_vat_type_dtl x ON x.p_vat_type_dtl_id = hasil.p_vat_type_dtl_id\r\n\t\t\t\t\t) AS data_transaksi\r\n\t\t\t\tLEFT JOIN t_cust_acc_masa_jab masa_jab ON masa_jab.t_cust_account_id = data_transaksi.t_cust_account_id\r\n\t\t\t\tAND masa_awal <= settlement_date\r\n\t\t\t\tAND CASE\r\n\t\t\t\tWHEN masa_akhir IS NULL THEN\r\n\t\t\t\t\tTRUE\r\n\t\t\t\tWHEN masa_akhir >= settlement_date THEN\r\n\t\t\t\t\tmasa_akhir >= settlement_date\r\n\t\t\t\tEND\r\n\t\t\t\tWHERE\r\n\t\t\t\t\tdata_transaksi.p_vat_type_id = " . $param_arr['p_vat_type_id'] . "\r\n\t\t\t\tORDER BY\r\n\t\t\t\t\tnpwd,\r\n\t\t\t\t\tstart_date DESC,\r\n\t\t\t\t\tt_vat_setllement_id";
$dbConn->query($query);
$output = "";
startExcel("data_piutang_" . date("d_m_Y") . ".xls");
$output .= '<h2>DATA PIUTANG ' . strtoupper($param_arr['vat_code']) . ' TAHUN ' . $param_arr['year_code'] . '<h2/>';
$output .= '<table border="1" cellspacing="0" cellpadding="0" width="100%">';
$output .= '<tr>
<th rowspan="3" style="VERTICAL-ALIGN: middle; TEXT-ALIGN: center">NO</th>
<th rowspan="3" style="VERTICAL-ALIGN: middle; TEXT-ALIGN: center">NPWPD</th>
<th rowspan="3" style="VERTICAL-ALIGN: middle; TEXT-ALIGN: center">Nama Badan</th>
<th colspan="14" style="TEXT-ALIGN: center">Pelaporan Pajak</th>
<th colspan="3" style="TEXT-ALIGN: center"> Pembayaran</th>
</tr>
<tr>
<th rowspan="2"> Jenis Ketetapan</th>
<th rowspan="2"> Ayat Pajak </th>
<th rowspan="2"> Periode Pelaporan</th>
<th rowspan="2"> Periode Transaksi</th>
<th rowspan="2"> Tgl. Pelaporan</th>
<th rowspan="2"> Tgl.Jatuh Tempo </th>
<th rowspan="2"> No. Kohir </th>
<th rowspan="2"> Total Transaksi </th>
<th rowspan="2"> Total Pajak</th>
<th rowspan="2"> Pajak Terhutang</th>
<th colspan="2"> Sanksi Adm. </th>
<th rowspan="2"> Denda</th>
<th rowspan="2"> Total Harus Bayar</th>
<th rowspan="2"> No. Kwitansi</th>
<th rowspan="2"> Tgl. Pembayaran </th>
<th rowspan="2"> Nilai Pembayaran </th>
</tr>
<tr>
<th>25%</th>
<th>2%</th>
</tr>';
$total = 0;
$no = 1;
while ($dbConn->next_record()) {
$item = $dbConn->Record;
$output .= '<tr>';
$output .= '<td align="center">' . $no++ . '</td>';
$output .= '<td align="left">' . $item['npwd'] . '</td>';
$output .= '<td align="left">' . $item['company_name'] . '</td>';
$output .= '<td align="left">' . $item['type_code'] . '</td>';
$output .= '<td align="left">' . $item['vat_code'] . '</td>';
$output .= '<td align="left"> ' . $item['periode_pelaporan'] . '</td>';
$output .= '<td align="left">' . $item['periode_awal_laporan'] . ' s.d ' . $item['periode_awal_laporan'] . '</td>';
$output .= '<td align="center"> ' . $item['tgl_pelaporan'] . '</td>';
$output .= '<td align="center"> ' . $item['jatuh_tempo'] . '</td>';
$output .= '<td align="center"> ' . $item['no_kohir'] . '</td>';
$output .= '<td align="right">' . number_format($item['total_transaksi'], 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($item['total_pajak'], 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($item['debt_vat_amt'], 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($item['kenaikan'], 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($item['kenaikan1'], 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($item['total_denda'], 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($item['total_hrs_bayar'], 0, ",", ".") . '</td>';
$output .= '<td align="center">' . $item['kuitansi_pembayaran'] . '</td>';
$output .= '<td align="center"> ' . $item['tgl_pembayaran'] . '</td>';
$output .= '<td align="right">' . number_format($item['payment_amount'], 0, ",", ".") . '</td>';
$output .= '</tr>';
}
$output .= '</table>';
echo $output;
exit;
}
function CetakExcel($data, $param_arr)
{
startExcel("laporan_posisi_status_teguran");
$output = '';
$output .= '<h2>LAPORAN POSISI SURAT TEGURAN<h2/>';
$output .= '<h3>JENIS PAJAK : ' . $param_arr['jenis_pajak'] . '<br/>';
$output .= 'PERIODE PAJAK : ' . $param_arr['pajak_periode'] . '<br/>';
//$output .= 'JATUH TEMPO : '.strtoupper(dateToString($tgl_jatuh_tempo)).'</h3>';
$output .= '<table border="1" widht="100%">
<tr>';
$output .= '<th align="center" >NO</th>';
$output .= '<th align="center" >MERK DAGANG</th>';
$output .= '<th align="center" >ALAMAT MERK DAGANG</th>';
$output .= '<th align="center" >NPWPD</th>';
$output .= '<th align="center" >SURAT TEGURAN 1</th>';
$output .= '<th align="center" >SURAT TEGURAN 2</th>';
$output .= '<th align="center" >SURAT TEGURAN 3</th>';
$output .= '<th align="center" >PER TANGGAL ' . $tanggal . '</th>';
$output .= '<th align="center" >SETELAH TANGGAL ' . $tanggal . '</th>';
$output .= '</tr>';
$temp = $data[0];
$debt_amount = 0;
$pf = CCGetFromGet('p_finance_period_id');
$dbConn = new clsDBConnSIKP();
$query = "select f_cek_penerbitan_surat_teguran from \r\n\t\tf_cek_penerbitan_surat_teguran(" . $pf . ",1);";
$dbConn->query($query);
$dbConn->next_record();
$result = $dbConn->f('f_cek_penerbitan_surat_teguran');
if ($result == 1) {
$surat_teguran_1_desc = "Tidak Terbit";
} else {
$surat_teguran_1_desc = "Belum Terbit";
}
$query = "select f_cek_penerbitan_surat_teguran from \r\n\t\tf_cek_penerbitan_surat_teguran(" . $pf . ",2);";
$dbConn->query($query);
$dbConn->next_record();
$result = $dbConn->f('f_cek_penerbitan_surat_teguran');
if ($result == 1) {
$surat_teguran_2_desc = "Tidak Terbit";
} else {
$surat_teguran_2_desc = "Belum Terbit";
}
$query = "select f_cek_penerbitan_surat_teguran from \r\n\t\tf_cek_penerbitan_surat_teguran(" . $pf . ",3);";
$dbConn->query($query);
$dbConn->next_record();
$result = $dbConn->f('f_cek_penerbitan_surat_teguran');
if ($result == 1) {
$surat_teguran_3_desc = "Tidak Terbit";
} else {
$surat_teguran_3_desc = "Belum Terbit";
}
$dbConn->close();
$surat_teguran_1 = $surat_teguran_1_desc;
$surat_teguran_2 = $surat_teguran_2_desc;
$surat_teguran_3 = $surat_teguran_3_desc;
if ($temp['surat_teguran_1'] == '1') {
$surat_teguran_1 = 'Terbit(' . $temp['tgl_teg_1'] . ')';
}
if ($temp['surat_teguran_2'] == '1') {
$surat_teguran_2 = 'Terbit(' . $temp['tgl_teg_2'] . ')';
}
if ($temp['surat_teguran_3'] == '1') {
$surat_teguran_3 = 'Terbit(' . $temp['tgl_teg_3'] . ')';
}
$j = 0;
$sebelum = 'Belum Bayar';
$sesudah = 'Belum Bayar';
for ($i = 1; $i < count($data); $i++) {
if ($temp['npwpd'] == $data[$i]['npwpd']) {
if ($data[$i]['surat_teguran_1'] == '1') {
$surat_teguran_1 = 'Terbit (' . $data[$i]['tgl_teg_1'] . ')';
$debt_amount = 0;
}
if ($data[$i]['surat_teguran_2'] == '1') {
$surat_teguran_2 = 'Terbit (' . $data[$i]['tgl_teg_2'] . ')';
$debt_amount = 0;
}
if ($data[$i]['surat_teguran_3'] == '1') {
$debt_amount = $data[$i]['debt_amount'];
$surat_teguran_3 = 'Terbit (' . $data[$i]['tgl_teg_3'] . ') (Rp. ' . number_format($debt_amount, 2, ',', '.') . ')';
$debt_amount = 0;
}
} else {
$output .= '<tr>';
$output .= '<td align="center">' . ($j + 1) . '</td>';
$output .= '<td align="left">' . $temp['company_brand'] . '</td>';
$output .= '<td align="left">' . $temp['alamat_merk_dagang'] . '</td>';
$output .= '<td align="center">' . $temp['npwpd'] . '</td>';
$output .= '<td align="center">' . $surat_teguran_1 . '</td>';
$output .= '<td align="center">' . $surat_teguran_2 . '</td>';
$output .= '<td align="center">' . $surat_teguran_3 . '</td>';
$output .= '<td align="center">' . $data[$i - 1]['tgl_bayar'] . '</td>';
$output .= '<td align="center">' . $data[$i - 1]['tgl_bayar2'] . '</td>';
$output .= '</tr>';
$temp = $data[$i];
$surat_teguran_1 = $surat_teguran_1_desc;
$surat_teguran_2 = $surat_teguran_2_desc;
$surat_teguran_3 = $surat_teguran_3_desc;
if ($temp['surat_teguran_1'] == '1') {
$surat_teguran_1 = 'Terbit (' . $data[$i]['tgl_teg_1'] . ')';
$debt_amount = 0;
}
if ($temp['surat_teguran_2'] == '1') {
$surat_teguran_2 = 'Terbit (' . $data[$i]['tgl_teg_2'] . ')';
$debt_amount = 0;
}
if ($temp['surat_teguran_3'] == '1') {
$debt_amount = $data[$i]['debt_amount'];
$surat_teguran_3 = 'Terbit (' . $data[$i]['tgl_teg_3'] . ') (Rp. ' . number_format($debt_amount, 2, ',', '.') . ')';
$debt_amount = 0;
}
$j = $j + 1;
}
}
if ($j > 0) {
$output .= '<tr>';
$output .= '<td align="center">' . ($j + 1) . '</td>';
$output .= '<td align="left">' . $temp['company_brand'] . '</td>';
$output .= '<td align="left">' . $temp['alamat_merk_dagang'] . '</td>';
$output .= '<td align="center">' . $temp['npwpd'] . '</td>';
$output .= '<td align="center">' . $surat_teguran_1 . '</td>';
$output .= '<td align="center">' . $surat_teguran_2 . '</td>';
$output .= '<td align="center">' . $surat_teguran_3 . '</td>';
$output .= '<td align="center">' . $data[$i - 1]['tgl_bayar'] . '</td>';
$output .= '<td align="center">' . $data[$i - 1]['tgl_bayar2'] . '</td>';
$output .= '</tr>';
}
$output .= '</table>';
echo $output;
exit;
}
function GetCetakExcel($param_arr)
{
startExcel("rekap_skpdkb_jabatan.xls");
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0" width="900">
<tr>
<td class="th"><strong>LAPORAN REKAP SKPDKB</strong></td>
</tr>
</table>';
$output .= '<h2>JENIS PAJAK : ' . $param_arr['vat_code'] . ' </h2>';
$output .= '<h2>PERIODE PAJAK : ' . $param_arr['code'] . '</h2>';
$tanggal = CCGetFromGet('date_end_laporan', '31-12-2014');
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th align="center" >NO</th>';
$output .= '<th align="center" >NPWPD</th>';
$output .= '<th align="center" >NAMA</th>';
$output .= '<th align="center" >ALAMAT</th>';
$output .= '<th align="center" >JATUH TEMPO</th>';
$output .= '<th align="center" >MASA PAJAK</th>';
$output .= '<th align="center" >TGL TAP</th>';
$output .= '<th align="center" >POKOK (PAJAK TERHUTANG+KENAIKAN)</th>';
$output .= '<th align="center" >PAJAK TEHUTANG</th>';
$output .= '<th align="center" >KENAIKAN PAJAK TERHUTANG</th>';
$output .= '<th align="center" >DENDA</th>';
$output .= '<th align="center" >TOTAL HARUS DIBAYAR</th>';
$output .= '<th align="center" >STATUS BAYAR</th>';
$output .= '<th align="center" >TANGGAL BAYAR</th>';
$output .= '</tr>';
$dbConn = new clsDBConnSIKP();
$query = "select a.t_vat_setllement_id as set_id,a.npwd as npwpd ,to_char(start_period,'dd-mm-yyyy')||' s.d. '||to_char(end_period,'dd-mm-yyyy') as masa_pajak,\r\n\t\tto_char(due_date,'dd-mm-yyyy')as due_date_char, to_char(settlement_date,'dd-mm-yyyy') as tgl_tap,\r\n\t\t* from t_vat_setllement a\r\n\t\tleft join t_cust_account x on x.t_cust_account_id=a.t_cust_account_id\r\n\t\tleft join t_payment_receipt y on y.t_vat_setllement_id=a.t_vat_setllement_id\r\n\t\twhere p_settlement_type_id = 4 \r\n\t\tand a.p_finance_period_id in(\r\n\t\t\tselect p_finance_period_id \r\n\t\t\tfrom p_finance_period\r\n\t\t\twhere \r\n\t\t\t\tstart_date >= (select start_date from p_finance_period\r\n\t\t\t\t\twhere p_finance_period_id = " . $param_arr['p_finance_period_id'] . ") \r\n\t\t\t\tand end_date <= (select end_date from p_finance_period\r\n\t\t\t\t\twhere p_finance_period_id = " . $param_arr['p_finance_period_id1'] . ") \r\n\t\t)\r\n\t\tand a.p_vat_type_dtl_id in (select p_vat_type_dtl_id from p_vat_type_dtl where p_vat_type_id =" . $param_arr['p_vat_type_id'] . ")\r\n\t\tand a.p_vat_type_dtl_id not in (11, 15, 41, 12, 42, 43, 30, 17, 21, 27, 31)\r\n\t\tand x.p_account_status_id = 1";
if ($param_arr['status_bayar'] == 2) {
$query .= "and receipt_no is not null ORDER BY wp_name";
} else {
if ($param_arr['status_bayar'] == 3) {
$query .= "and receipt_no is null ORDER BY wp_name";
} else {
$query .= "ORDER BY wp_name, start_period";
}
}
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
for ($i = 0; $i < count($data); $i++) {
$output .= '<tr><td align="center" >' . ($i + 1) . '</td>';
$output .= '<td align="left" >' . $data[$i]['npwpd'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['wp_name'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['wp_address_name'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['due_date_char'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['masa_pajak'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['tgl_tap'] . '</td>';
$output .= '<td align="right" >' . number_format($data[$i]['total_vat_amount'], 2, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data[$i]['debt_vat_amt'], 2, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data[$i]['db_increasing_charge'] + $data[$i]['db_interest_charge'], 2, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data[$i]['total_penalty_amount'], 2, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data[$i]['total_penalty_amount'] + $data[$i]['db_increasing_charge'] + $data[$i]['db_interest_charge'] + $data[$i]['debt_vat_amt'], 2, ',', '.') . '</td>';
if ($data[$i]['payment_date'] == '') {
$output .= '<td align="left" >Belum Bayar</td>';
} else {
$output .= '<td align="left" >Sudah Bayar</td>';
}
$output .= '<td align="left" >' . $data[$i]['payment_date'] . '</td>';
$output .= '</tr>';
}
$output .= '</table>';
echo $output;
exit;
}
function GetCetakRekapHTML($param_arr)
{
$dbConn = new clsDBConnSIKP();
$query = "select p_settlement_type_id, code\n\t\t\tfrom p_settlement_type\n\t\t\twhere p_settlement_type_id = " . $param_arr['ketetapan'];
//echo $query;exit;
$data = array();
$dbConn->query($query);
$dbConn->next_record();
$ketetapan = $dbConn->Record;
$dbConn->close();
$doAction = CCGetFromGet('doAction');
if ($doAction == 'cetak_rekap_excel') {
startExcel("laporan_ketetapan_vs_realisasi_" . $param_arr['start_date'] . "_s.d._" . $param_arr['end_date'] . "_" . str_replace(' ', '_', $ketetapan["code"]) . ".xls");
}
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0" width="900">
<tr>
<td class="HeaderLeft"></td>
<td class="th"><strong>LAPORAN KETETAPAN VS REALISASI</strong></td>
<td class="HeaderRight"></td>
</tr>
</table>';
//$output .= '<h2>JENIS PAJAK : '.$param_arr['vat_code'].' </h2>';
$output .= '<h2>PERIODE PENETAPAN : ' . $param_arr['start_date'] . ' s.d. ' . $param_arr['end_date'] . '</h2>';
$output .= '<h2>JENIS KETETAPAN : ' . $ketetapan["code"] . '</h2>';
$tanggal = CCGetFromGet('date_end_laporan', '31-12-2014');
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th align="center" rowspan=2>NO</th>';
$output .= '<th align="center" rowspan=2>AYAT</th>';
$output .= '<th align="center" rowspan=2>PAJAK/RETRIBUSI</th>';
$output .= '<th align="center" colspan=2>KETETAPAN</th>';
$output .= '<th align="center" colspan=2>REALISASI</th>';
$output .= '<th align="center" colspan=2>SISA</th>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<th align="center" >Jumlah (Rp)</th>';
$output .= '<th align="center" >Jumlah SSPD</th>';
$output .= '<th align="center" >Jumlah (Rp)</th>';
$output .= '<th align="center" >Jumlah SSPD</th>';
$output .= '<th align="center" >Jumlah (Rp)</th>';
$output .= '<th align="center" >Jumlah SSPD</th>';
$output .= '</tr>';
$dbConn = new clsDBConnSIKP();
$query = "select * from v_p_vat_type_dtl_rep\r\n\t\t\tORDER BY nomor_ayat";
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
$jumlah = 0;
$jumlah_relisasi = 0;
$jumlah_sisa = 0;
$jumlah_per_ayat = 0;
$jumlah_relisasi_per_ayat = 0;
$jumlah_sisa_per_ayat = 0;
$jumlah_sspd = 0;
$jumlah_sspd_relisasi = 0;
$jumlah_sspd_sisa = 0;
$jumlah_sspd_per_ayat = 0;
$jumlah_sspd_relisasi_per_ayat = 0;
$jumlah_sspd_sisa_per_ayat = 0;
for ($i = 0; $i < count($data); $i++) {
$dbConn2 = new clsDBConnSIKP();
if ($param_arr['ketetapan'] == 7) {
$query2 = "select SUM (round(a.penalty_amount)) AS realisasi,\r\n\t\t\t\t\t\t\tcount(a.t_payment_receipt_id) as jumlah_sspd_realisasi,\r\n\t\t\t\t\t\t\tSUM (round(b.penalty_amt)) AS ketetapan,\r\n\t\t\t\t\t\t\tcount(e.t_vat_setllement_id) as jumlah_sspd_ketetapan\r\n\t\t\t\tfrom t_vat_setllement e\r\n\t\t\t\tleft join t_payment_receipt a on a.t_vat_setllement_id = e.t_vat_setllement_id\r\n\t\t\t\tleft join t_vat_penalty b on b.t_vat_setllement_id = e.t_vat_setllement_id\r\n\t\t\t\tleft join p_vat_type_dtl c on c.p_vat_type_dtl_id = e.p_vat_type_dtl_id\r\n\t\t\t\tleft join p_vat_type d on c.p_vat_type_id = d.p_vat_type_id\r\n\t\t\t\twhere p_settlement_type_id = " . $param_arr['ketetapan'] . " \r\n\t\t\t\tand trunc(settlement_date) between to_date('" . $param_arr['start_date'] . "','yyyy-mm-dd') \r\n\t\t\t\t\tand (to_date('" . $param_arr['end_date'] . "','yyyy-mm-dd'))\r\n\t\t\t\tand decode(c.p_vat_type_id,7,d.code||c.code,d.penalty_code) = " . $data[$i]['nomor_ayat'] . "\r\n\t\t\t\tand b.penalty_amt is not null";
if ($param_arr['p_vat_type_id'] != '') {
$query2 .= "and a.p_vat_type_dtl_id in (select p_vat_type_dtl_id \r\n\t\t\t\t\t\tfrom p_vat_type_dtl where p_vat_type_id =" . $param_arr['p_vat_type_id'] . ")";
}
if ($param_arr['status_bayar'] == 2) {
$query2 .= "and receipt_no is not null";
}
if ($param_arr['status_bayar'] == 3) {
$query2 .= "and receipt_no is null";
}
//echo $query2;exit;
} else {
$query2 = "select SUM (y.payment_vat_amount) AS realisasi,count(y.t_payment_receipt_id) as jumlah_sspd_realisasi,\r\n\t\t\t\t\t\t\tSUM (A .total_vat_amount) AS ketetapan,count(a.t_vat_setllement_id) as jumlah_sspd_ketetapan\r\n\t\t\t\tfrom t_vat_setllement a\r\n\t\t\t\tleft join t_payment_receipt y on y.t_vat_setllement_id=a.t_vat_setllement_id\r\n\t\t\t\tleft join t_cust_account x on x.t_cust_account_id=a.t_cust_account_id\r\n\t\t\t\twhere p_settlement_type_id = " . $param_arr['ketetapan'] . " \r\n\t\t\t\tand trunc(settlement_date) between to_date('" . $param_arr['start_date'] . "','yyyy-mm-dd') \r\n\t\t\t\t\tand (to_date('" . $param_arr['end_date'] . "','yyyy-mm-dd'))\r\n\t\t\t\tand " . $data[$i]['p_vat_type_dtl_id'] . "=a.p_vat_type_dtl_id ";
if ($param_arr['p_vat_type_id'] != '') {
$query2 .= "and a.p_vat_type_dtl_id in (select p_vat_type_dtl_id \r\n\t\t\t\t\t\tfrom p_vat_type_dtl where p_vat_type_id =" . $param_arr['p_vat_type_id'] . ")";
}
if ($param_arr['status_bayar'] == 2) {
$query2 .= "and receipt_no is not null";
}
if ($param_arr['status_bayar'] == 3) {
$query2 .= "and receipt_no is null";
}
}
//echo $query2;exit;
$data2 = array();
$dbConn2->query($query2);
while ($dbConn2->next_record()) {
$data2 = $dbConn2->Record;
}
$dbConn2->close();
//print_r($data2); exit;
$output .= '<tr ><td align="center" >' . ($i + 1) . '</td>';
$output .= '<td align="left" >' . $data[$i]['nomor_ayat'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['nama_ayat'] . '</td>';
$output .= '<td align="right" >' . number_format($data2['ketetapan'], 2, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data2['jumlah_sspd_ketetapan'], 0, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data2['realisasi'], 2, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data2['jumlah_sspd_realisasi'], 0, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data2['ketetapan'] - $data2['realisasi'], 2, ',', '.') . '</td>';
$output .= '<td align="right" >' . number_format($data2['jumlah_sspd_ketetapan'] - $data2['jumlah_sspd_realisasi'], 0, ',', '.') . '</td>';
$output .= '</tr>';
$jumlah += $data2['ketetapan'];
$jumlah_relisasi += $data2['realisasi'];
$jumlah_sisa += $data2['ketetapan'] - $data2['realisasi'];
$jumlah_sspd += $data2['jumlah_sspd_ketetapan'];
$jumlah_sspd_relisasi += $data2['jumlah_sspd_realisasi'];
$jumlah_sspd_sisa += $data2['jumlah_sspd_ketetapan'] - $data2['jumlah_sspd_realisasi'];
$jumlah_per_ayat += $data2['ketetapan'];
$jumlah_relisasi_per_ayat += $data2['realisasi'];
$jumlah_sisa_per_ayat += $data2['ketetapan'] - $data2['realisasi'];
$jumlah_sspd_per_ayat += $data2['jumlah_sspd_ketetapan'];
$jumlah_sspd_relisasi_per_ayat += $data2['jumlah_sspd_realisasi'];
$jumlah_sspd_sisa_per_ayat += $data2['jumlah_sspd_ketetapan'] - $data2['jumlah_sspd_realisasi'];
$ayat = $data[$i]["kode_jns_pjk"];
$ayatsesudah = $data[$i + 1]["kode_jns_pjk"];
if ($ayat != $ayatsesudah && count($data) > 1 || empty($data[$i + 1])) {
$output .= '<tr >';
$output .= '<td align="center" colspan=3><span style="font-weight:bold">' . $data[$i]['nama_jns_pajak'] . '</span></td>';
$output .= '<th align="right" >' . number_format($jumlah_per_ayat, 2, ',', '.') . '</th>';
$output .= '<th align="right" >' . number_format($jumlah_sspd_per_ayat, 0, ',', '.') . '</th>';
$output .= '<th align="right" >' . number_format($jumlah_relisasi_per_ayat, 2, ',', '.') . '</th>';
$output .= '<th align="right" >' . number_format($jumlah_sspd_relisasi_per_ayat, 0, ',', '.') . '</th>';
$output .= '<th align="right" >' . number_format($jumlah_sisa_per_ayat, 2, ',', '.') . '</th>';
$output .= '<th align="right" >' . number_format($jumlah_sspd_sisa_per_ayat, 0, ',', '.') . '</th>';
$output .= '</tr>';
$jumlah_per_ayat = 0;
$jumlah_relisasi_per_ayat = 0;
$jumlah_sisa_per_ayat = 0;
$jumlah_sspd_per_ayat = 0;
$jumlah_sspd_relisasi_per_ayat = 0;
$jumlah_sspd_sisa_per_ayat = 0;
}
}
$output .= '<tr><td align="center" colspan=3 ><span style="font-weight:bold">JUMLAH TOTAL</span></td>';
$output .= '<th align="right">' . number_format($jumlah, 2, ',', '.') . '</th>';
$output .= '<th align="right">' . number_format($jumlah_sspd, 0, ',', '.') . '</th>';
$output .= '<th align="right">' . number_format($jumlah_relisasi, 2, ',', '.') . '</th>';
$output .= '<th align="right">' . number_format($jumlah_sspd_relisasi, 0, ',', '.') . '</th>';
$output .= '<th align="right">' . number_format($jumlah_sisa, 2, ',', '.') . '</th>';
$output .= '<th align="right">' . number_format($jumlah_sspd_sisa, 0, ',', '.') . '</th>';
$output .= '</tr>';
$output .= '</table>';
if ($doAction == 'cetak_rekap_excel') {
echo $output;
exit;
}
return $output;
}
function CetakExcel($param_arr)
{
startExcel("laporan_history_potensi_piutang.xls");
$dbConn = new clsDBConnSIKP();
$query = "select a.t_vat_setllement_id as set_id,a.npwd as npwpd ,z.code as masa_pajak,\r\n\t\tto_char(due_date,'dd-mm-yyyy')as due_date_char, to_char(settlement_date,'dd-mm-yyyy') as tgl_tap,\r\n\t\tto_char(due_date,'dd-mm-yyyy') as tgl_jth_tempo, p.vat_code as ayat_pajak,q.vat_code as jenis_pajak,\r\n\t\t* from t_vat_setllement a\r\n\t\tleft join t_cust_account x on x.t_cust_account_id=a.t_cust_account_id\r\n\t\tleft join t_payment_receipt y on y.t_vat_setllement_id=a.t_vat_setllement_id\r\n\t\tleft join p_finance_period z on z.p_finance_period_id = a.p_finance_period_id\r\n\t\tleft join p_vat_type_dtl p on p.p_vat_type_dtl_id = a.p_vat_type_dtl_id\r\n\t\tleft join p_vat_type q on q.p_vat_type_id = p.p_vat_type_id\r\n\t\twhere p_settlement_type_id = " . $param_arr['ketetapan'] . " \r\n\t\tand a.p_finance_period_id in(\r\n\t\t\tselect p_finance_period_id \r\n\t\t\tfrom p_finance_period \r\n\t\t\twhere \r\n\t\t\t\tstart_date >= (select start_date from p_finance_period\r\n\t\t\t\t\twhere p_finance_period_id = " . $param_arr['p_finance_period_id'] . ") \r\n\t\t\t\tand end_date <= (select end_date from p_finance_period\r\n\t\t\t\t\twhere p_finance_period_id = " . $param_arr['p_finance_period_id1'] . ") \r\n\t\t)\r\n\t\tand a.p_vat_type_dtl_id not in (11, 15, 41, 12, 42, 43, 30, 17, 21, 27, 31)\r\n\t\tand x.p_account_status_id = 1";
if ($param_arr['p_vat_type_id'] != '') {
$query .= "and a.p_vat_type_dtl_id in (select p_vat_type_dtl_id \r\n\t\t\t\tfrom p_vat_type_dtl where p_vat_type_id =" . $param_arr['p_vat_type_id'] . ")";
}
if ($param_arr['status_bayar'] == 2) {
$query .= "and receipt_no is not null ORDER BY wp_name";
} else {
if ($param_arr['status_bayar'] == 3) {
$query .= "and receipt_no is null ORDER BY wp_name";
} else {
$query .= "ORDER BY q.p_vat_type_id, ayat_pajak, wp_name, start_period";
}
}
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
$output = '';
$output .= '<h2>LAPORAN HISTORY POTENSI PIUTANG<h2/>';
$output .= '<h2>PERIODE PAJAK : ' . $param_arr['code'] . ' s.d. ' . $param_arr['code1'] . '</h2>';
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th align="center" >NO</th>';
$output .= '<th align="center" >JENIS PAJAK</th>';
$output .= '<th align="center" >AYAT PAJAK</th>';
$output .= '<th align="center" >NAMA</th>';
$output .= '<th align="center" >NPWPD</th>';
$output .= '<th align="center" >MASA PAJAK</th>';
$output .= '<th align="center" >TGL TAP</th>';
$output .= '<th align="center" >TGL JTH TEMPO</th>';
$output .= '<th align="center" >TOTAL HARUS DIBAYAR</th>';
$output .= '<th align="center" >STATUS BAYAR</th>';
$output .= '<th align="center" >TANGGAL BAYAR</th>';
$output .= '</tr>';
$jumlah = 0;
for ($i = 0; $i < count($data); $i++) {
//$temp = ($data[$i]['total_penalty_amount']+$data[$i]['db_increasing_charge']+$data[$i]['db_interest_charge']+$data[$i]['debt_vat_amt']);
$temp = $data[$i]['total_vat_amount'] + $data[$i]['total_penalty_amount'];
$jumlah = $jumlah + $temp;
$output .= '<tr><td align="center" >' . ($i + 1) . '</td>';
$output .= '<td align="left" >' . $data[$i]['jenis_pajak'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['ayat_pajak'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['wp_name'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['npwpd'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['masa_pajak'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['tgl_tap'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['tgl_jth_tempo'] . '</td>';
$output .= '<td align="right" >' . number_format($temp, 2, ',', '.') . '</td>';
if ($data[$i]['payment_date'] == '') {
$output .= '<td align="left" >Belum Bayar</td>';
} else {
$output .= '<td align="left" >Sudah Bayar</td>';
}
$output .= '<td align="left" >' . $data[$i]['payment_date'] . '</td>';
$output .= '</tr>';
}
$output .= '<tr><td align="center" colspan=8 >Jumlah</td>';
$output .= '<td align="right">' . number_format($jumlah, 2, ',', '.') . '</td>';
$output .= '</tr>';
$output .= '</table>';
echo $output;
exit;
}
function GetCetakExcel($param_arr)
{
startExcel("history.xls");
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0" width="900">
<tr>
<td class="th"><strong>HISTORY</strong></td>
</tr>
</table>';
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th align="center" >NO</th>';
$output .= '<th align="center" >NPWPD</th>';
$output .= '<th align="center" >PERIODE</th>';
$output .= '<th align="center" >TANGAL TAP</th>';
$output .= '<th align="center" >Execute By</th>';
$output .= '<th align="center" >Modification Type</th>';
$output .= '<th align="center" >Modification Date</th>';
$output .= '<th align="center" >Reason</th>';
$output .= '</tr>';
$dbConn = new clsDBConnSIKP();
$query = "SELECT h.*, p.code, t.code as type_code\n\t\t\tFROM h_vat_setllement h\n\t\t\tLEFT JOIN p_finance_period p on p.p_finance_period_id = h.p_finance_period_id\n\t\t\tLEFT JOIN p_settlement_type t on t.p_settlement_type_id = h.p_settlement_type_id\n\t\t\tWHERE h.npwd LIKE '%" . $param_arr['s_keyword'] . "%'";
if ($param_arr['date_end_laporan'] != '') {
$query .= " and (trunc(modification_date) <= '" . $param_arr['date_end_laporan'] . "')";
}
if ($param_arr['date_start_laporan'] != '') {
$query .= " and (trunc(modification_date) >= '" . $param_arr['date_start_laporan'] . "')";
}
if ($param_arr['p_vat_type_id'] != '') {
$query .= " and (h.p_vat_type_dtl_id in (select p_vat_type_dtl_id from p_vat_type_dtl where p_vat_type_id = " . $param_arr['p_vat_type_id'] . "))";
}
$query .= " order by modification_date desc";
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
for ($i = 0; $i < count($data); $i++) {
$output .= '<tr><td align="center" >' . ($i + 1) . '</td>';
$output .= '<td align="left" >' . $data[$i]['npwd'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['code'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['type_code'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['settlement_date'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['modified_by'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['modification_type'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['alasan'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['payment_date'] . '</td>';
$output .= '</tr>';
}
$output .= '</table>';
echo $output;
exit;
}
function GetCetakExcel($param_arr)
{
startExcel("pendaftaran_wp.xls");
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
$output .= '<table>';
//$output .= '<tr><td class="th" align="center" colspan=7><h1><strong>KARTU LAPORAN</strong></td> </tr>';
//$output .= '<tr><td class="th" align="center" colspan=7><h1><strong>REKAPITULASI TARGET/SASARAN MUTU</strong></td> </tr>';
$output .= '<tr><td class="th" align="center" colspan=4><h1><strong>DAFTAR PENGUKUHAN NPWPD JABATAN</strong></td></tr>';
//$output .= '<tr><td class="th" align="center" colspan=4><h1><strong>PENDAFTARAN WAJIB PAJAK DAERAH</strong></td></tr>';
$output .= '</table></br>';
$output .= '<table>';
$output .= '<tr></tr>';
$output .= '<tr></tr>';
//$output .= '<tr><td colspan=2>JENIS PAJAK </td><td>: '.$param_arr['vat_code'].'</td></tr>';
$output .= '<tr><td colspan=2>TANGGAL </td><td>: ' . $param_arr['date_start_laporan'] . ' s.d. ' . $param_arr['date_end_laporan'] . ' </td></tr>';
//$output .= '<tr><td colspan=2>JENIS TARGET </td><td>: PENERBITAN NPWPD 7 HARI KERJA</td></tr>';
$output .= '<tr></tr>';
$output .= '</table></br>';
$tanggal = CCGetFromGet('date_end_laporan', '31-12-2014');
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th align="center" >NO</th>';
$output .= '<th align="center" >NAMA OBJEK PAJAK</th>';
$output .= '<th align="center" >ALAMAT OBJEK PAJAK</th>';
$output .= '<th align="center" >NPWPD</th>';
$output .= '</tr>';
$dbConn = new clsDBConnSIKP();
$query = "SELECT * \r\n\t\t\tFROM t_vat_registration a \r\n\t\t\tleft join p_vat_type_dtl b on a.p_vat_type_dtl_id=b.p_vat_type_dtl_id \r\n\t\t\tleft join t_customer_order c on a.t_customer_order_id = c. t_customer_order_id\r\n\t\t\twhere a.npwpd_jabatan = 'Y' and \r\n\t\t\t\ttrunc(a.creation_date) BETWEEN to_date('" . $param_arr['date_start_laporan'] . "','dd-mm-yyyy') \r\n\t\t\t\tand to_date('" . $param_arr['date_end_laporan'] . "','dd-mm-yyyy')\r\n\t\t\tand case when " . $param_arr['p_vat_type_id'] . "=0 then true\r\n\t\t\t\t\telse a.p_vat_type_dtl_id in (select p_vat_type_dtl_id from p_vat_type_dtl where p_vat_type_id =" . $param_arr['p_vat_type_id'] . ")\r\n\t\t\t\tend \r\n\t\t\tand case when " . $param_arr['nilai'] . "=0 then true\r\n\t\t\t\t\telse c.p_order_status_id = " . $param_arr['nilai'] . "\r\n\t\t\t\tend \r\n\t\t\torder by company_brand";
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
for ($i = 0; $i < count($data); $i++) {
$output .= '<tr><td align="center" >' . ($i + 1) . '</td>';
$output .= '<td align="left" >' . $data[$i]['company_brand'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['brand_address_name'] . ' ' . $data[$i]['brand_address_no'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['npwpd'] . '</td></tr>';
}
$output .= '</table>';
$output .= '<table width=100% border=0>';
$output .= '<tr></tr>';
$output .= '<tr></tr>';
$output .= '<tr >
<td width=50% align="center" colspan=3>
</td>
<td width=50% align="center">
KEPALA BIDANG
</td>
</tr>';
$output .= '<tr >
<td width=50% align="center" colspan=3>
</td>
<td width=50% align="center">
PAJAK PENDAFTARAN,
</td>
</tr>';
$output .= '<tr >
<td width=50% align="center" colspan=3>
</td>
<td width=50% align="center">
</td>
</tr>';
$output .= '<tr >
<td width=50% align="center" colspan=3>
</td>
<td width=50% align="center">
</td>
</tr>';
$output .= '<tr >
<td width=50% align="center" colspan=3>
</td>
<td width=50% align="center">
Drs. H. GUN GUN SUMARYANA
</td>
</tr>';
$output .= '<tr >
<td width=50% align="center" colspan=3>
</td>
<td width=50% align="center">
Pembina
</td>
</tr>';
$output .= '<tr >
<td width=50% align="center" colspan=3>
</td>
<td width=50% align="center">
NIP. 19700806 199101 1 001
</td>
</tr>';
$output .= '</table>';
echo $output;
exit;
}
}
$dbConn->close();
$total_per_bulan = array();
$total_per_bulan[12] = 0;
$total_per_bulan[1] = 0;
$total_per_bulan[2] = 0;
$total_per_bulan[3] = 0;
$total_per_bulan[4] = 0;
$total_per_bulan[5] = 0;
$total_per_bulan[6] = 0;
$total_per_bulan[7] = 0;
$total_per_bulan[8] = 0;
$total_per_bulan[9] = 0;
$total_per_bulan[10] = 0;
$total_per_bulan[11] = 0;
startExcel("laporan_penerimaan_pertahun_" . $data["tahun"][0]);
echo "<div><h3> LAPORAN PENERIMAAN PER TAHUN </h3></div>";
echo "<div><h3>TAHUN : " . $data["tahun"][0] . "</h3></div>";
echo '<table border="1" width="100%"> ';
echo '<tr>
<th rowspan="2">NO</th>
<th rowspan="2">Nama Perusahaan</th>
<th rowspan="2">Alamat</th>
<th rowspan="2">Tanggal Pengukuhan</th>
<th colspan="12">Realisasi dan Tanggal Bayar</th>
<th rowspan="2">Jumlah</th>
</tr>
';
echo '<tr>
<th> Desember <br/> ' . ($data["tahun"][0] - 1) . ' </th>
<th> Januari </th>
function print_excel($param_arr)
{
startExcel("laporan_penerimaan_bpthb");
echo "<div><h3> LAPORAN PENERIMAAN BPHTB </h3></div>";
echo "<div><h3>TANGGAL : " . dateToString($param_arr['date_start'], true) . " s/d " . dateToString($param_arr['date_end'], true) . "</h3></div>";
if ($param_arr['verificated_by'] != '0') {
echo '<div><h3>VERIFIKATOR : ' . $param_arr['verificated_by'] . '</h3></div>';
}
echo '<table border="1" width="100%"> ';
echo '<tr>
<th>NO</th>
<th>NO TRANSAKSI</th>
<th>JENIS TRANSAKSI</th>
<th>NOP</th>
<th>ALAMAT OBJEK PAJAK</th>
<th>KECAMATAN OBJEK PAJAK</th>
<th>KELURAHAN OBJEK PAJAK</th>
<th>TGL BAYAR</th>
<th>TGL DAFTAR</th>
<th>NAMA SUBJEK PAJAK</th>
<th>ALAMAT SUBJEK PAJAK</th>
<th>KELURAHAN SUBJEK PAJAK</th>
<th>KECAMATAN SUBJEK PAJAK</th>
<th>LUAS TNH</th>
<th>LUAS BGN</th>
<th>NJOP (Rp)</th>
<th>NILAI TRANSAKSI (Rp)</th>
<th>TOTAL BAYAR (Rp)</th>';
if ($param_arr['verificated_by'] == '0') {
echo '<th>VERIFIKATOR</th>';
}
echo '<th>DAFTAR ONLINE?</th>
</tr>
';
$dbConn = new clsDBConnSIKP();
$whereClause = '';
$criteria = array();
if (!empty($param_arr['date_start']) && !empty($param_arr['date_end'])) {
$criteria[] = " (trunc(a.payment_date) BETWEEN '" . $param_arr['date_start'] . "' AND '" . $param_arr['date_end'] . "') ";
} else {
if (!empty($param_arr['date_start']) && empty($param_arr['date_end'])) {
$criteria[] = " trunc(a.payment_date) >= '" . $param_arr['date_start'] . "' ";
} else {
if (empty($param_arr['date_start']) && !empty($param_arr['date_end'])) {
$criteria[] = " trunc(a.payment_date) <= '" . $param_arr['date_end'] . "' ";
}
}
}
if (!empty($param_arr['receipt_no'])) {
$criteria[] = " a.receipt_no ILIKE '%" . $param_arr['receipt_no'] . "%' ";
}
if (!empty($param_arr['njop_pbb'])) {
$criteria[] = " b.njop_pbb = '" . $param_arr['njop_pbb'] . "' ";
}
if (!empty($param_arr['wp_name'])) {
$criteria[] = " b.wp_name ILIKE '%" . $param_arr['wp_name'] . "%' ";
}
if (!empty($param_arr['p_region_id_kecamatan'])) {
$criteria[] = " b.object_p_region_id_kec = " . $param_arr['p_region_id_kecamatan'];
}
if (!empty($param_arr['p_region_id_kelurahan'])) {
$criteria[] = " b.object_p_region_id_kel = " . $param_arr['p_region_id_kelurahan'];
}
if ($param_arr['p_bphtb_legal_doc_type_id'] != 0) {
$criteria[] = " b.p_bphtb_legal_doc_type_id = " . $param_arr['p_bphtb_legal_doc_type_id'];
}
if ($param_arr['verificated_by'] != '0') {
$criteria[] = " b.verificated_by ilike '%" . $param_arr['verificated_by'] . "%' ";
}
$whereClause = join(" AND ", $criteria);
$query = "SELECT a.receipt_no, b.njop_pbb, to_char(a.payment_date, 'YYYY-MM-DD') AS payment_date, to_char(b.creation_date, 'YYYY-MM-DD') AS creation_date, b.t_ppat_id,\r\n\t\t\t\t\tb.wp_name, b.wp_address_name, kelurahan.region_name AS kelurahan_name, kecamatan.region_name AS kecamatan_name, b.land_area, b.building_area, b.land_total_price, a.payment_amount, b.verificated_by,\r\n\t\t\t\t\tmarket_price,building_total_price+land_total_price as njop, c.description,\r\n\t\t\t\t\tkelurahan_objek.region_name AS kelurahan_objek_name, kecamatan_objek.region_name AS kecamatan_objek_name,object_address_name\r\n\t\t\t\t\tFROM t_payment_receipt_bphtb AS a\r\n\t\t\tLEFT JOIN t_bphtb_registration AS b ON a.t_bphtb_registration_id = b.t_bphtb_registration_id\r\n\t\t\tLEFT JOIN p_region AS kelurahan ON b.wp_p_region_id_kel = kelurahan.p_region_id\r\n\t\t\tLEFT JOIN p_region AS kecamatan ON b.wp_p_region_id_kec = kecamatan.p_region_id\r\n\t\t\tLEFT JOIN p_bphtb_legal_doc_type c on c.p_bphtb_legal_doc_type_id = b.p_bphtb_legal_doc_type_id\r\n\t\t\tLEFT JOIN p_region AS kelurahan_objek ON b.object_p_region_id_kel = kelurahan_objek.p_region_id\r\n\t\t\tLEFT JOIN p_region AS kecamatan_objek ON b.object_p_region_id_kec = kecamatan_objek.p_region_id";
if (!empty($whereClause)) {
$query .= " WHERE " . $whereClause;
}
$query .= " ORDER BY a.receipt_no ASC";
//print_r($query);
//exit;
$dbConn->query($query);
$items = array();
$total_nilai_penerimaan = 0;
$no = 1;
while ($dbConn->next_record()) {
$items[] = $item = array('receipt_no' => $dbConn->f("receipt_no"), 'njop_pbb' => $dbConn->f("njop_pbb"), 'payment_date' => $dbConn->f("payment_date"), 'creation_date' => $dbConn->f("creation_date"), 'wp_name' => $dbConn->f("wp_name"), 'wp_address_name' => $dbConn->f("wp_address_name"), 'kelurahan_name' => $dbConn->f("kelurahan_name"), 'kecamatan_name' => $dbConn->f("kecamatan_name"), 'land_area' => $dbConn->f("land_area"), 'building_area' => $dbConn->f("building_area"), 'land_total_price' => $dbConn->f("land_total_price"), 'payment_amount' => $dbConn->f("payment_amount"), 'verificated_by' => $dbConn->f("verificated_by"), 'market_price' => $dbConn->f("market_price"), 'njop' => $dbConn->f("njop"), 'description' => $dbConn->f("description"), 'kelurahan_objek_name' => $dbConn->f("kelurahan_objek_name"), 'kecamatan_objek_name' => $dbConn->f("kecamatan_objek_name"), 'object_address_name' => $dbConn->f("object_address_name"), 't_ppat_id' => $dbConn->f("t_ppat_id"));
$status_daftar = empty($item['t_ppat_id']) ? "Tidak" : "Ya";
echo '<tr>';
echo '<td align="center">' . $no . '</td>';
echo '<td align="center">' . $item['receipt_no'] . '</td>';
echo '<td align="center">' . $item['description'] . '</td>';
echo '<td align="left"> ' . $item['njop_pbb'] . '</td>';
echo '<td align="left">' . $item['object_address_name'] . '</td>';
echo '<td align="left">' . $item['kelurahan_objek_name'] . '</td>';
echo '<td align="left">' . $item['kecamatan_objek_name'] . '</td>';
echo '<td align="center">' . dateToString($item['payment_date']) . '</td>';
echo '<td align="center">' . dateToString($item['creation_date']) . '</td>';
echo '<td align="left">' . trim(strtoupper($item['wp_name'])) . '</td>';
echo '<td align="left">' . $item['wp_address_name'] . '</td>';
echo '<td align="left">' . $item['kelurahan_name'] . '</td>';
echo '<td align="left">' . $item['kecamatan_name'] . '</td>';
echo '<td align="right">' . number_format($item['land_area'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['building_area'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['njop'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['market_price'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['payment_amount'], 0, ",", ".") . '</td>';
if ($param_arr['verificated_by'] == '0') {
echo '<td align="center">' . $item['verificated_by'] . '</td>';
}
echo '<td align="center">' . $status_daftar . '</td>';
echo '</tr>';
$total_nilai_penerimaan += $item['payment_amount'];
$no++;
}
echo '<tr>
<td colspan="17" align="center"> <b>TOTAL</b> </td>
<td align="right"><b>' . number_format($total_nilai_penerimaan, 0, ",", ".") . '</b></td>
<td align="center"> </td>
<td align="center"> </td>
</tr>';
echo '</table>';
exit;
}
function GetCetakHTML($param_arr)
{
$doAction = CCGetFromGet('doAction');
if ($doAction == 'view_excel') {
startExcel("laporan_perkembangan_jumlah_wajib_pajak.xls");
}
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
if ($doAction != 'view_excel') {
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0" width="900">
<tr>
<td class="HeaderLeft"><img border="0" alt="" src="../Styles/sikp/Images/Spacer.gif"></td>
<td class="th"><strong>LAPORAN PERKEMBANGAN JUMLAH WP</strong></td>
<td class="HeaderRight"><img border="0" alt="" src="../Styles/sikp/Images/Spacer.gif"></td>
</tr>
</table>';
}
$output .= '<h2 style="color:black;" align="center">LAPORAN PERKEMBANGAN JUMLAH WAJIB PAJAK</h2>';
$output .= '<h2 style="color:black;" align="center">HOTEL, RESTORAN, HIBURAN, PARKIR DAN PAJAK PENERANGAN JALAN</h2>';
$output .= '<br>';
$tanggal = CCGetFromGet('date_end_laporan', '31-12-2014');
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th align="center" rowspan=2 >NO</th>';
$output .= '<th align="center" rowspan=2 >URAIAN JENIS PAJAK</th>';
$output .= '<th align="center" colspan=2 >JUMLAH S.D. BULAN LALU</th>';
$output .= '<th align="center" colspan=2 >PEMUTAKHIRAN DATA BULAN INI</th>';
$output .= '<th align="center" colspan=2 >JUMLAH S.D. BULAN INI</th>';
$output .= '<th align="center" rowspan=2 >KETERANGAN</th>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >NON AKTIF</th>';
$output .= '<th align="center" >PENERBITAN NPWPD</th>';
$output .= '<th align="center" >PERUBAHAN STATUS NON AKTIF</th>';
$output .= '<th align="center" >AKTIF</th>';
$output .= '<th align="center" >NON AKTIF</th>';
$output .= '</tr>';
$dbConn = new clsDBConnSIKP();
/*$query="select upper(b.vat_code) as jenis_pajak ,a.vat_code as ayat_pajak,
(select count(*) from t_cust_account x
where case when ".$param_arr['npwpd_jabatan']." = 1 then true
else x.npwpd_jabatan = 'Y'
end
and x.p_vat_type_dtl_id = a.p_vat_type_dtl_id and x.p_account_status_id = 1 and
trunc(x.last_satatus_date) <= (select end_date from p_finance_period y where y.p_finance_period_id = ".$param_arr['p_finance_period_id'].")) as jumlah_aktif_sd_bulan_ini,
(select count(*) from t_cust_account x
where case when ".$param_arr['npwpd_jabatan']." = 1 then true
else x.npwpd_jabatan = 'Y'
end
and x.p_vat_type_dtl_id = a.p_vat_type_dtl_id and x.p_account_status_id != 1 and
trunc(x.last_satatus_date) <= (select end_date from p_finance_period y where y.p_finance_period_id = ".$param_arr['p_finance_period_id'].")) as jumlah_non_aktif_sd_bulan_ini,
(select count(*) from t_cust_account x
where case when ".$param_arr['npwpd_jabatan']." = 1 then true
else x.npwpd_jabatan = 'Y'
end
and x.p_vat_type_dtl_id = a.p_vat_type_dtl_id and x.p_account_status_id = 1 and
trunc(x.last_satatus_date) BETWEEN (select start_date from p_finance_period y where y.p_finance_period_id = ".$param_arr['p_finance_period_id'].")
and (select end_date from p_finance_period y where y.p_finance_period_id = ".$param_arr['p_finance_period_id'].")) as jumlah_aktif_bulan_ini,
(select count(*) from t_cust_account x
where case when ".$param_arr['npwpd_jabatan']." = 1 then true
else x.npwpd_jabatan = 'Y'
end
and x.p_vat_type_dtl_id = a.p_vat_type_dtl_id and x.p_account_status_id != 1 and
trunc(x.last_satatus_date) BETWEEN (select start_date from p_finance_period y where y.p_finance_period_id = ".$param_arr['p_finance_period_id'].")
and (select end_date from p_finance_period y where y.p_finance_period_id = ".$param_arr['p_finance_period_id'].")) as jumlah_non_aktif_bulan_ini,
(select count(*) from t_cust_account x
where case when ".$param_arr['npwpd_jabatan']." = 1 then true
else x.npwpd_jabatan = 'Y'
end
and x.p_vat_type_dtl_id = a.p_vat_type_dtl_id and x.p_account_status_id = 1 and
trunc(x.last_satatus_date) <= (select end_date from p_finance_period z where z.end_date =
(select start_date-1 from p_finance_period y where y.p_finance_period_id = ".$param_arr['p_finance_period_id']."))) as jumlah_aktif_sd_bulan_lalu,
(select count(*) from t_cust_account x
where case when ".$param_arr['npwpd_jabatan']." = 1 then true
else x.npwpd_jabatan = 'Y'
end
and x.p_vat_type_dtl_id = a.p_vat_type_dtl_id and x.p_account_status_id != 1 and
trunc(x.last_satatus_date) <= (select end_date from p_finance_period z where z.end_date =
(select start_date-1 from p_finance_period y where y.p_finance_period_id = ".$param_arr['p_finance_period_id']."))) as jumlah_non_aktif_sd_bulan_lalu
from p_vat_type_dtl a
left join p_vat_type b on b.p_vat_type_id = a.p_vat_type_id
where a.p_vat_type_id in (1,2,3,4,5)*/
/*ORDER BY a.p_vat_type_id,a.code"; */
$query = "select case when p_vat_type_dtl_id = 35 then 'RUMAH KOS' \r\n\t\t\t\t\t\twhen p_vat_type_dtl_id = 18 then 'DISKOTIK/KLUB MALAM' \r\n\t\t\t\t\t\twhen p_vat_type_dtl_id = 13 then 'PANTI PIJAT/SPA/REFLEKSI' \r\n\t\t\t\t\t\twhen p_vat_type_dtl_id = 31 then 'HIBURAN INSIDENTIL' \r\n\t\t\t\t\t\telse ayat_pajak end as ayat_pajak_2,* \r\n\t\t\tfrom \r\n\t\t\t(select upper(b.vat_code) as jenis_pajak ,a.vat_code as ayat_pajak,a.p_vat_type_dtl_id,\r\n\t\t\t(select count(*) from t_cust_account x \r\n\t\t\t\twhere case when " . $param_arr['npwpd_jabatan'] . " = 1 then true \r\n\t\t\t\t\t\t else x.npwpd_jabatan = 'Y'\r\n\t\t\t\t\t end\r\n\t\t\t\t\tand case when A.p_vat_type_dtl_id = 10 then x.p_vat_type_dtl_id in (9,10) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 18 then x.p_vat_type_dtl_id in (18,20) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 13 then x.p_vat_type_dtl_id in (13,44) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 31 then x.p_vat_type_dtl_id in (15,41,12,17,21,42,43,27,30,31) \r\n\t\t\t\t\t\t else A.p_vat_type_dtl_id = x.p_vat_type_dtl_id end\r\n\t\t\t\t\tand x.p_account_status_id = 1 and\r\n\t\t\t\t\t\t\ttrunc(x.last_satatus_date) <= (select end_date from p_finance_period y where y.p_finance_period_id = " . $param_arr['p_finance_period_id'] . ")) as jumlah_aktif_sd_bulan_ini,\r\n\r\n\t\t\t(select count(*) from t_cust_account x \r\n\t\t\t\twhere case when " . $param_arr['npwpd_jabatan'] . " = 1 then true \r\n\t\t\t\t\t\t else x.npwpd_jabatan = 'Y'\r\n\t\t\t\t\t end\r\n\t\t\t\t\tand case when A.p_vat_type_dtl_id = 10 then x.p_vat_type_dtl_id in (9,10) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 18 then x.p_vat_type_dtl_id in (18,20) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 13 then x.p_vat_type_dtl_id in (13,44) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 31 then x.p_vat_type_dtl_id in (15,41,12,17,21,42,43,27,30,31) \r\n\t\t\t\t\t\t else A.p_vat_type_dtl_id = x.p_vat_type_dtl_id end\r\n\t\t\t\t\tand x.p_account_status_id != 1 and\r\n\t\t\t\t\t\t\ttrunc(x.last_satatus_date) <= (select end_date from p_finance_period y where y.p_finance_period_id = " . $param_arr['p_finance_period_id'] . ")) as jumlah_non_aktif_sd_bulan_ini,\r\n\r\n\t\t\t(select count(*) from t_cust_account x \r\n\t\t\t\twhere case when " . $param_arr['npwpd_jabatan'] . " = 1 then true \r\n\t\t\t\t\t\t else x.npwpd_jabatan = 'Y'\r\n\t\t\t\t\t end\r\n\t\t\t\t\tand case when A.p_vat_type_dtl_id = 10 then x.p_vat_type_dtl_id in (9,10) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 18 then x.p_vat_type_dtl_id in (18,20) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 13 then x.p_vat_type_dtl_id in (13,44) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 31 then x.p_vat_type_dtl_id in (15,41,12,17,21,42,43,27,30,31) \r\n\t\t\t\t\t\t else A.p_vat_type_dtl_id = x.p_vat_type_dtl_id end\r\n\t\t\t\t\tand x.p_account_status_id = 1 and\r\n\t\t\t\t\t\t\ttrunc(x.last_satatus_date) BETWEEN (select start_date from p_finance_period y where y.p_finance_period_id = " . $param_arr['p_finance_period_id'] . ") \r\n\t\t\t\t\t\t\t\tand (select end_date from p_finance_period y where y.p_finance_period_id = " . $param_arr['p_finance_period_id'] . ")) as jumlah_aktif_bulan_ini,\r\n\r\n\t\t\t(select count(*) from t_cust_account x \r\n\t\t\t\twhere case when " . $param_arr['npwpd_jabatan'] . " = 1 then true \r\n\t\t\t\t\t\t else x.npwpd_jabatan = 'Y'\r\n\t\t\t\t\t end\r\n\t\t\t\t\tand case when A.p_vat_type_dtl_id = 10 then x.p_vat_type_dtl_id in (9,10) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 18 then x.p_vat_type_dtl_id in (18,20) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 13 then x.p_vat_type_dtl_id in (13,44) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 31 then x.p_vat_type_dtl_id in (15,41,12,17,21,42,43,27,30,31) \r\n\t\t\t\t\t\t else A.p_vat_type_dtl_id = x.p_vat_type_dtl_id end\r\n\t\t\t\t\tand x.p_account_status_id != 1 and\r\n\t\t\t\t\t\t\ttrunc(x.last_satatus_date) BETWEEN (select start_date from p_finance_period y where y.p_finance_period_id = " . $param_arr['p_finance_period_id'] . ") \r\n\t\t\t\t\t\t\t\tand (select end_date from p_finance_period y where y.p_finance_period_id = " . $param_arr['p_finance_period_id'] . ")) as jumlah_non_aktif_bulan_ini,\r\n\r\n\t\t\t(select count(*) from t_cust_account x \r\n\t\t\t\twhere case when " . $param_arr['npwpd_jabatan'] . " = 1 then true \r\n\t\t\t\t\t\t else x.npwpd_jabatan = 'Y'\r\n\t\t\t\t\t end\r\n\t\t\t\t\tand case when A.p_vat_type_dtl_id = 10 then x.p_vat_type_dtl_id in (9,10) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 18 then x.p_vat_type_dtl_id in (18,20) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 13 then x.p_vat_type_dtl_id in (13,44) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 31 then x.p_vat_type_dtl_id in (15,41,12,17,21,42,43,27,30,31) \r\n\t\t\t\t\t\t else A.p_vat_type_dtl_id = x.p_vat_type_dtl_id end\r\n\t\t\t\t\tand x.p_account_status_id = 1 and\r\n\t\t\t\t\t\t\ttrunc(x.last_satatus_date) <= (select end_date from p_finance_period z where z.end_date = \r\n\t\t\t\t\t\t\t\t(select start_date-1 from p_finance_period y where y.p_finance_period_id = " . $param_arr['p_finance_period_id'] . "))) as jumlah_aktif_sd_bulan_lalu,\r\n\r\n\t\t\t(select count(*) from t_cust_account x \r\n\t\t\t\twhere case when " . $param_arr['npwpd_jabatan'] . " = 1 then true \r\n\t\t\t\t\t\t else x.npwpd_jabatan = 'Y'\r\n\t\t\t\t\t end\r\n\t\t\t\t\tand case when A.p_vat_type_dtl_id = 10 then x.p_vat_type_dtl_id in (9,10) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 18 then x.p_vat_type_dtl_id in (18,20) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 13 then x.p_vat_type_dtl_id in (13,44) \r\n\t\t\t\t\t\t when A.p_vat_type_dtl_id = 31 then x.p_vat_type_dtl_id in (15,41,12,17,21,42,43,27,30,31) \r\n\t\t\t\t\t\t else A.p_vat_type_dtl_id = x.p_vat_type_dtl_id end\r\n\t\t\t\t\tand x.p_account_status_id != 1 and\r\n\t\t\t\t\t\t\ttrunc(x.last_satatus_date) <= (select end_date from p_finance_period z where z.end_date = \r\n\t\t\t\t\t\t\t\t(select start_date-1 from p_finance_period y where y.p_finance_period_id = " . $param_arr['p_finance_period_id'] . "))) as jumlah_non_aktif_sd_bulan_lalu\r\n\r\n\r\n\t\t\tfrom p_vat_type_dtl a\r\n\t\t\tleft join p_vat_type b on b.p_vat_type_id = a.p_vat_type_id\r\n\t\t\twhere a.p_vat_type_id in (1,2,3,4,5)\r\n\t\t\t\tand a.p_vat_type_dtl_id not in (9,20,44,15,41,12,17,21,42,43,27,30)\r\n\t\t\tORDER BY a.p_vat_type_id,a.code)";
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
$j = 0;
$total_aktif_sd_bulan_lalu = 0;
$total_non_aktif_sd_bulan_lalu = 0;
$total_aktif_bulan_ini = 0;
$total_non_aktif_bulan_ini = 0;
$total_aktif_sd_bulan_ini = 0;
$total_non_aktif_sd_bulan_ini = 0;
for ($i = 0; $i < count($data); $i++) {
if ($data[$i]['jenis_pajak'] != $data[$i - 1]['jenis_pajak']) {
$output .= '<tr><td align="center" ><b>' . ($j + 1) . '</b></td>';
$output .= '<td align="left" ><b>' . $data[$i]['jenis_pajak'] . '</b></td>';
if ($data[$i]['jenis_pajak'] == 'PAJAK PARKIR' || $data[$i]['jenis_pajak'] == 'PAJAK PPJ') {
$output .= '<td align="right" >' . $data[$i]['jumlah_aktif_sd_bulan_lalu'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_non_aktif_sd_bulan_lalu'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_aktif_bulan_ini'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_non_aktif_bulan_ini'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_aktif_sd_bulan_ini'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_non_aktif_sd_bulan_ini'] . '</td>';
$output .= '<td align="left" ></td>';
} else {
$output .= '<td align="left" ></td>';
$output .= '<td align="left" ></td>';
$output .= '<td align="left" ></td>';
$output .= '<td align="left" ></td>';
$output .= '<td align="left" ></td>';
$output .= '<td align="left" ></td>';
$output .= '<td align="left" ></td>';
}
$output .= '</tr>';
$j++;
}
if ($data[$i]['jenis_pajak'] != 'PAJAK PARKIR' && $data[$i]['jenis_pajak'] != 'PAJAK PPJ') {
$output .= '<tr><td align="center" ></td>';
$output .= '<td align="left" >- ' . $data[$i]['ayat_pajak_2'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_aktif_sd_bulan_lalu'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_non_aktif_sd_bulan_lalu'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_aktif_bulan_ini'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_non_aktif_bulan_ini'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_aktif_sd_bulan_ini'] . '</td>';
$output .= '<td align="right" >' . $data[$i]['jumlah_non_aktif_sd_bulan_ini'] . '</td>';
$output .= '<td align="left" ></td>';
$output .= '</tr>';
}
$total_aktif_sd_bulan_lalu = $total_aktif_sd_bulan_lalu + $data[$i]['jumlah_aktif_sd_bulan_lalu'];
$total_non_aktif_sd_bulan_lalu = $total_non_aktif_sd_bulan_lalu + $data[$i]['jumlah_non_aktif_sd_bulan_lalu'];
$total_aktif_bulan_ini = $total_aktif_bulan_ini + $data[$i]['jumlah_aktif_bulan_ini'];
$total_non_aktif_bulan_ini = $total_non_aktif_bulan_ini + $data[$i]['jumlah_non_aktif_bulan_ini'];
$total_aktif_sd_bulan_ini = $total_aktif_sd_bulan_ini + $data[$i]['jumlah_aktif_sd_bulan_ini'];
$total_non_aktif_sd_bulan_ini = $total_non_aktif_sd_bulan_ini + $data[$i]['jumlah_non_aktif_sd_bulan_ini'];
}
$output .= '<tr><td align="center" ></td>';
$output .= '<td align="left" ><b>JUMLAH</b></td>';
$output .= '<td align="right" >' . $total_aktif_sd_bulan_lalu . '</td>';
$output .= '<td align="right" >' . $total_non_aktif_sd_bulan_lalu . '</td>';
$output .= '<td align="right" >' . $total_aktif_bulan_ini . '</td>';
$output .= '<td align="right" >' . $total_non_aktif_bulan_ini . '</td>';
$output .= '<td align="right" >' . $total_aktif_sd_bulan_ini . '</td>';
$output .= '<td align="right" >' . $total_non_aktif_sd_bulan_ini . '</td>';
$output .= '<td align="left" ></td>';
$output .= '</tr>';
$output .= '</table>';
if ($doAction == 'view_excel') {
echo $output;
exit;
}
return $output;
}
function GetCetakExcel($p_rqst_type_id, $year_code, $year_period_id, $date_start, $date_end, $jenis_tahun, $rqst_type_code)
{
startExcel("LAPORAN_PENERIMAAN_PAJAK.xls");
$output = '';
$output .= '<table>
<tr>';
$output = '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td valign="top">
<table class="grid-table" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="th" colspan=6 align="center"><strong>LAPORAN PENERIMAAN GLOBAL PER TANGGAL PENERIMAAN</strong></td>
</tr>
<tr>
<td class="th"><strong></strong></td>
<td class="th"><strong>JENIS PAJAK</strong></td>
<td class="th"><strong>: ' . $rqst_type_code . '</strong></td>
</tr>
<tr>
<td class="th"><strong></strong></td>
<td class="th"><strong>TANGGAL</strong></td>
<td class="th"><strong>: ' . dateToString($date_start) . ' s/d ' . dateToString($date_end) . '</strong></td>
</tr>
<tr>
</tr>
</table>
<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px">
<tr class="Caption">';
$output .= '<th>NO</th>';
$output .= '<th>BULAN</th>';
$output .= '<th>BESARNYA</th>';
$output .= '<th>JUMLAH WP</th>';
$output .= '<th>JUMLAH SSPD</th>';
$output .= '<th>KETERANGAN</th>';
$output .= '</tr>';
$no = 1;
$jumlah = 0;
$jumlah_wp = 0;
$dbConn = new clsDBConnSIKP();
if ($jenis_tahun == 'pajak') {
$query = "select * from sikp.f_laporan_per_thn_pajak(" . $p_rqst_type_id . "," . $year_period_id . ",'" . $date_start . "', '" . $date_end . "')";
} else {
if ($jenis_tahun == 'bayar') {
$query = "select * from sikp.f_laporan_per_thn_bayar(" . $p_rqst_type_id . ",2013,'" . $date_start . "', '" . $date_end . "')";
}
}
$dbConn->query($query);
while ($dbConn->next_record()) {
$items[] = array('bulan_wp' => $dbConn->f("bulan_wp"), 'total_amount' => $dbConn->f("total_amount"), 'jumlah_wp' => $dbConn->f("jumlah_wp"));
$output .= '<tr>';
$output .= '<td style="font-weight:bold;" align="left">' . $no . '</td>';
$output .= '<td style="font-weight:bold;" align="left">' . strtoupper($dbConn->f("bulan_wp")) . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($dbConn->f("total_amount"), 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($dbConn->f("jumlah_wp"), 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '</tr>';
$jumlah += $dbConn->f("total_amount");
$jumlah_wp += $dbConn->f("jumlah_wp");
$no++;
}
$output .= '<tr>';
$output .= '<td style="font-weight:bold;" align="left"></td>';
$output .= '<td style="font-weight:bold;" align="left">JUMLAH</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($jumlah, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($jumlah_wp, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '<td style="font-weight:bold;" align="right"></td>';
$output .= '</tr>';
$output .= '</table></table>';
$output .= '<table>';
$output .= '<tr></tr>';
$output .= '<tr></tr>';
$output .= '<tr>';
$output .= '<td colspan=4 style="font-weight:bold;" align="right"></td>';
$output .= '<td colspan=2 style="font-weight:bold;" align="center">KEPALA SEKSI VERIFIKASI OTORISASI DAN</td>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<td colspan=4 style="font-weight:bold;" align="right"></td>';
$output .= '<td colspan=2 style="font-weight:bold;" align="center">PEMBUKUAN</td>';
$output .= '</tr>';
$output .= '<tr></tr>';
$output .= '<tr></tr>';
$output .= '<tr></tr>';
$output .= '<tr>';
$output .= '<td colspan=4 style="font-weight:bold;" align="right"></td>';
$output .= '<td colspan=2 style="font-weight:bold;" align="center">(Drs. H. Deden Saepulloh, MM.)</td>';
$output .= '</tr>';
$output .= '<tr>';
$output .= '<td colspan=4 style="font-weight:bold;" align="right"></td>';
$output .= '<td colspan=2 style="font-weight:bold;" align="center">(NIP 19681210 199010 001)</td>';
$output .= '</tr>';
$output .= '</table>';
echo $output;
exit;
}
function GetCetakExcel($data, $data2, $data3, $user, $tgl_penerimaan)
{
startExcel("laporan_target_vs_realisasi_perbandingan.xls");
$output = '';
$output .= '<table>
<tr>';
$output = '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td valign="top">
<table class="grid-table" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="th" colspan=11 align="center"><strong>TARGET DAN REALISASI PERBANDINGAN</strong></td>
</tr>
<tr>
<td class="th" colspan=11 align="center"><strong>SAMPAI TANGGAL ' . $tgl_penerimaan . '</strong></td>
</tr>
<tr>
</tr>
</table>
<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px">
<tr class="Caption">';
$tahun = substr($tgl_penerimaan, 6, 4);
$tgl_penerimaan2 = substr($tgl_penerimaan, 0, 6) . ($tahun - 1);
$tgl_penerimaan3 = substr($tgl_penerimaan, 0, 6) . ($tahun - 2);
$output .= '<th rowspan=2 style="text-align:center;">NO</th>';
$output .= '<th rowspan=2 style="text-align:center;">JENIS PAJAK</th>';
$output .= '<th colspan=3 style="text-align:center;">' . ($tahun - 2) . '</th>';
$output .= '<th colspan=3 style="text-align:center;">' . ($tahun - 1) . '</th>';
$output .= '<th colspan=3 style="text-align:center;">' . $tahun . '</th></tr>';
$output .= '<tr class="Caption"><th style="text-align:center;">TARGET</th>';
$output .= '<th style="text-align:center;">REALISASI</th>';
$output .= '<th style="text-align:center;">% REAL</th>';
$output .= '<th style="text-align:center;">TARGET</th>';
$output .= '<th style="text-align:center;">REALISASI</th>';
$output .= '<th style="text-align:center;">% REAL</th>';
$output .= '<th style="text-align:center;">TARGET</th>';
$output .= '<th style="text-align:center;">REALISASI</th>';
$output .= '<th style="text-align:center;">% REAL</th>';
$output .= '</tr>';
$no = 1;
$total_target = 0;
$total_realisasi = 0;
$total_target2 = 0;
$total_realisasi2 = 0;
$total_target3 = 0;
$total_realisasi3 = 0;
$bulan = substr($tgl_penerimaan, 3, 2);
for ($i = 0; $i < count($data['nama_jns_pajak']); $i++) {
//print data
$query = "select sum(target_amt)as target \r\n\t\t\t\t\tfrom t_revenue_target_dtl where t_revenue_target_id = \r\n\t\t\t\t\t(select t_revenue_target_id from t_revenue_target\r\n\t\t\t\t\t where p_vat_type_id = " . $data['p_vat_type_id'][$i] . "\r\n\t\t\t\t\t\tand p_year_period_id =\r\n\t\t\t\t\t\t\t(select p_year_period_id from p_year_period \r\n\t\t\t\t\t\t\twhere to_date('" . $tgl_penerimaan . "','dd-mm-yyyy') BETWEEN start_date and end_date\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\t\t)";
//echo $query;exit;
$dbConn = new clsDBConnSIKP();
$dbConn->query($query);
$dbConn->next_record();
$target = $dbConn->f('target');
$query = "select sum(target_amt)as target \r\n\t\t\t\t\tfrom t_revenue_target_dtl where t_revenue_target_id = \r\n\t\t\t\t\t(select t_revenue_target_id from t_revenue_target\r\n\t\t\t\t\t where p_vat_type_id = " . $data['p_vat_type_id'][$i] . "\r\n\t\t\t\t\t\tand p_year_period_id =\r\n\t\t\t\t\t\t\t(select p_year_period_id from p_year_period \r\n\t\t\t\t\t\t\twhere to_date('" . $tgl_penerimaan2 . "','dd-mm-yyyy') BETWEEN start_date and end_date\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\t\t)";
$dbConn->query($query);
$dbConn->next_record();
$target2 = $dbConn->f('target');
$query = "select sum(target_amt)as target \r\n\t\t\t\t\tfrom t_revenue_target_dtl where t_revenue_target_id = \r\n\t\t\t\t\t(select t_revenue_target_id from t_revenue_target\r\n\t\t\t\t\t where p_vat_type_id = " . $data['p_vat_type_id'][$i] . "\r\n\t\t\t\t\t\tand p_year_period_id =\r\n\t\t\t\t\t\t\t(select p_year_period_id from p_year_period \r\n\t\t\t\t\t\t\twhere to_date('" . $tgl_penerimaan3 . "','dd-mm-yyyy') BETWEEN start_date and end_date\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\t\t)";
$dbConn->query($query);
$dbConn->next_record();
$target3 = $dbConn->f('target');
$output .= '<tr>';
$output .= '<td style="font-weight:bold;" align="left">' . $no . '</td>';
$output .= '<td style="font-weight:bold;" align="left">' . strtoupper($data["nama_jns_pajak"][$i]) . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($target3, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data3["realisasi"][$i], 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data3["realisasi"][$i] / $target3 * 100, 2, ',', '.') . ' %</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($target2, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data2["realisasi"][$i], 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data2["realisasi"][$i] / $target2 * 100, 2, ',', '.') . ' %</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($target, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data["realisasi"][$i], 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($data["realisasi"][$i] / $target * 100, 2, ',', '.') . ' %</td>';
$output .= '</tr>';
$no++;
$total_target += $target;
$total_realisasi += $data["realisasi"][$i];
$total_target2 += $target2;
$total_realisasi2 += $data2["realisasi"][$i];
$total_target3 += $target3;
$total_realisasi3 += $data3["realisasi"][$i];
}
$output .= '<tr>';
$output .= '<td style="font-weight:bold;" align="left"></td>';
$output .= '<td style="font-weight:bold;" align="left">JUMLAH</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_target3, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi3, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi3 / $total_target3 * 100, 2, ',', '.') . ' %</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_target2, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi2, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi2 / $total_target2 * 100, 2, ',', '.') . ' %</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_target, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi, 0, ',', '.') . '</td>';
$output .= '<td style="font-weight:bold;" align="right">' . number_format($total_realisasi / $total_target * 100, 2, ',', '.') . ' %</td>';
$output .= '</tr>';
$output .= '</table></table>';
echo $output;
exit;
}
function PageCetak()
{
$data = array();
$dbConn = new clsDBConnSIKP();
$p_vat_type_id = CCGetFromGet("p_vat_type_id", "");
$p_year_period_id = CCGetFromGet("p_year_period_id", "");
$tgl_penerimaan = CCGetFromGet("tgl_penerimaan", "");
$i_flag_setoran = CCGetFromGet("i_flag_setoran", "");
$kode_bank = CCGetFromGet("kode_bank", "");
$query = "select b.payment_key, a.* \r\n\t\t\t\t\t\tfrom f_rep_bpps_mod_3({$p_vat_type_id}, {$p_year_period_id}, '{$tgl_penerimaan}',{$i_flag_setoran},'{$kode_bank}') a\r\n\t\t\t\t\t\tleft join t_vat_setllement b on b.t_vat_setllement_id=a.t_vat_setllement_id\r\n\t\t\t\t\t\torder by kode_jns_trans, kode_jns_pajak, kode_ayat";
//die($query);
$dbConn->query($query);
$tgl_penerimaan2 = $tgl_penerimaan;
$tgl_penerimaan = str_replace("'", "", $tgl_penerimaan);
$tahun = date("Y", strtotime($tgl_penerimaan));
while ($dbConn->next_record()) {
$data[] = array("kode_jns_trans" => $dbConn->f("kode_jns_trans"), "jns_trans" => $dbConn->f("jns_trans"), "kode_jns_pajak" => $dbConn->f("kode_jns_pajak"), "kode_ayat" => $dbConn->f("kode_ayat"), "jns_pajak" => $dbConn->f("jns_pajak"), "jns_ayat" => $dbConn->f("jns_ayat"), "nama_ayat" => $dbConn->f("nama_ayat"), "no_kohir" => $dbConn->f("no_kohir"), "wp_name" => $dbConn->f("wp_name"), "wp_address_name" => $dbConn->f("wp_address_name"), "wp_address_no" => $dbConn->f("wp_address_no"), "npwpd" => $dbConn->f("npwpd"), "jumlah_terima" => $dbConn->f("jumlah_terima"), "masa_pajak" => $dbConn->f("masa_pajak"), "kd_tap" => $dbConn->f("kd_tap"), "keterangan" => $dbConn->f("keterangan"), "payment_date" => $dbConn->f("payment_date"), "payment_key" => $dbConn->f("payment_key"), "brand_name" => $dbConn->f("brand_name"), "jam" => $dbConn->f("jam"));
}
$cetak = CCGetFromGet('cetak');
if ($cetak == 'excel') {
startExcel("laporan_bpps.xls");
}
$output = '';
$output .= '<table>
<tr>';
$output = '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td valign="top">
<table class="grid-table" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="HeaderLeft"></td>
<td class="th"><strong>LAPORAN BPPS</strong></td>
<td class="HeaderRight"></td>
</tr>
</table>
<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px">
<tr class="Caption">';
//NO. NO. AYAT NAMA AYAT NO. KOHIR NAMA WP NPWPD JUMLAH MASA PAJAK TGL TAP KET.
$output .= '<th style="text-align:center;">NO</th>';
$output .= '<th style="text-align:center;">NO. AYAT</th>';
$output .= '<th style="text-align:center;">NAMA AYAT</th>';
$output .= '<th style="text-align:center;">NO. KOHIR</th>';
$output .= '<th style="text-align:center;">NO. BAYAR</th>';
$output .= '<th style="text-align:center;">NAMA WP</th>';
$output .= '<th style="text-align:center;">MERK DAGANG</th>';
$output .= '<th style="text-align:center;">NPWPD</th>';
$output .= '<th style="text-align:center;">JUMLAH</th>';
$output .= '<th style="text-align:center;">MASA PAJAK</th>';
$output .= '<th style="text-align:center;">TGL TAP</th>';
$output .= '<th style="text-align:center;">KET.</th>';
$output .= '</tr>';
$no = 0;
$jumlahtemp = 0;
$total = 0;
foreach ($data as $item) {
$output .= '<tr>';
$output .= '<td>' . ($no + 1) . '</td>';
$output .= '<td>' . $item["kode_jns_pajak"] . ' ' . $item["kode_ayat"] . '</td>';
$output .= '<td>' . $item["nama_ayat"] . '</td>';
$output .= '<td>' . $item["no_kohir"] . '</td>';
$output .= '<td>' . $item["payment_key"] . '</td>';
$output .= '<td>' . $item["wp_name"] . '</td>';
$output .= '<td>' . $item["brand_name"] . '</td>';
$output .= '<td>' . $item["npwpd"] . '</td>';
$output .= '<td align="right">' . number_format($item["jumlah_terima"], 0, ',', '.') . '</td>';
$output .= '<td>' . $item["masa_pajak"] . '</td>';
$output .= '<td>' . $item["kd_tap"] . '</td>';
$output .= '<td>' . $item["keterangan"] . '</td>';
$output .= '</tr>';
$jumlahtemp += $item["jumlah_terima"];
$total += $item["jumlah_terima"];
$ayat = $item["kode_ayat"];
$ayatsesudah = $data[$no + 1]["kode_ayat"];
if ($ayat != $ayatsesudah && count($data) > 1 || empty($data[$no + 1])) {
$output .= '<tr>';
$output .= '<td colspan=8 align="center">' . "JUMLAH " . strtoupper($item["nama_ayat"]) . '</td>';
$output .= '<td align="right">' . number_format($jumlahtemp, 0, ',', '.') . '</td>';
$output .= '</tr>';
$jumlahtemp = 0;
}
$no++;
}
$output .= '<tr>';
$output .= '<td colspan=8 align="center">' . "TOTAL " . strtoupper($item["jns_pajak"]) . '</td>';
$output .= '<td align="right">' . number_format($total, 0, ',', '.') . '</td>';
$output .= '</tr>';
if ($cetak == 'excel') {
echo $output;
exit;
}
return $output;
}
function GetCetakExcel($param_arr)
{
startExcel("rekap_skpdkb_jabatan.xls");
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0">
<tr>
<td valign="top">';
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0" width="900">
<tr>
<td colspan=5 class="th"><strong>LAPORAN REKAP SURAT TEGURAN PERBULAN</strong></td>
</tr>
<tr>
</table>';
$output .= '<table id="table-piutang-detil" class="Grid" border="1" cellspacing="0" cellpadding="3px" width="100%">
<tr >';
$output .= '<th rowspan=2 align="center" >NO</th>';
$output .= '<th rowspan=2 align="center" >BULAN</th>';
$output .= '<th colspan=3 align="center" >JUMLAH TEGURAN KE</th></tr>';
$output .= '<tr><th align="center" >1</th>';
$output .= '<th align="center" >2</th>';
$output .= '<th align="center" >3</th>';
$output .= '</tr>';
$dbConn = new clsDBConnSIKP();
$query = "select t_debt_letter_id,t_customer_order_id,letter_date, sequence_no, code, \r\n\t\t\t\t\t(select count(*)\r\n\t\t\t\t\tfrom f_debt_letter_list(a.t_customer_order_id) x\r\n\t\t\t\t\tLEFT JOIN t_cust_account as y ON x.t_cust_account_id = y.t_cust_account_id\r\n\t\t\t\t\twhere y.p_vat_type_dtl_id NOT IN (11, 15, 17, 21, 27, 30, 41, 42, 43)) as jml\r\n\t\t\tfrom t_debt_letter a\r\n\t\t\tLEFT JOIN p_finance_period b on a.p_finance_period_id=b.p_finance_period_id\r\n\t\t\t where \r\n\t\t\t\ta.p_finance_period_id in (select p_finance_period_id \r\n\t\t\t\t\tfrom p_finance_period where p_year_period_id = " . $param_arr['p_year_period_id'] . ")\r\n\t\t\tORDER BY letter_date";
//echo $query;exit;
$data = array();
$dbConn->query($query);
while ($dbConn->next_record()) {
$data[] = $dbConn->Record;
}
$dbConn->close();
$no = 1;
for ($i = 0; $i < count($data); $i++) {
if ($data[$i]['code'] == $data[$i - 1]['code'] && $i != 0) {
$output .= '<td align="left" >' . $data[$i]['jml'] . '</td>';
} else {
$output .= '<tr><td align="center" >' . $no . '</td>';
$output .= '<td align="left" >' . $data[$i]['code'] . '</td>';
$output .= '<td align="left" >' . $data[$i]['jml'] . '</td>';
$no++;
}
}
$output .= '</table>';
echo $output;
exit;
}
function view_html($param_arr)
{
$cetak_laporan = CCGetFromGet('cetak_laporan');
if ($cetak_laporan == 'view_excel') {
startExcel("LAPORAN_PIUTANG_2002-2014.xls");
}
$output = '';
$output .= '<table id="table-piutang" class="grid-table-container" border="0" cellspacing="0" cellpadding="0" width="100%">
<tr>
<td valign="top">';
$output .= '<table class="grid-table" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="HeaderLeft"></td>
<td class="th"><strong>DAFTAR PIUTANG (2002-2014)</strong></td>
<td class="HeaderRight"></td>
</tr>
</table>';
$output .= '<table border=1 class="report" cellspacing="0" cellpadding="3px" width="100%">
<tr>';
$output .= '<th>NO</th>';
$output .= '<th>NPWPD</th>';
$output .= '<th>NAMA WP</th>';
$output .= '<th>MASA PAJAK</th>';
$output .= '<th>TGL TAP</th>';
$output .= '<th>NO KOHIR</th>';
$output .= '<th>BESARNYA (Rp)</th>';
$output .= '<th>REALISASI <br/>PIUTANG (Rp)</th>';
$output .= '<th>TGL BAYAR</th>';
$output .= '<th>SISA <br/>PIUTANG (Rp)</th>';
$output .= '<th>KETERANGAN</th>';
//$output.='<th>TAHUN</th>';
//$output.='<th>MODIF</th>';
$output .= '</tr>';
$no = 1;
$dbConn = new clsDBConnSIKP();
$query = "select sisa_piutang,\r\n\t\t\ta.*,to_char(a.tgl_tap,'dd-mm-yyyy') as tgl_tap_formated, to_char(a.tgl_bayar,'dd-mm-yyyy') as tgl_bayar_formated , b.wp_name, c.code as periode_bayar\r\n\t\t\tfrom t_piutang_pajak_penetapan_final_2 as a\r\n\t\t\tLEFT JOIN t_cust_account as b ON a.t_cust_account_id = b.t_cust_account_id\r\n\t\t\tLEFT JOIN p_finance_period as c ON a.p_finance_period_id = c.p_finance_period_id\r\n\t\t\tWHERE a.p_vat_type_id=" . $param_arr['p_vat_type_id'];
if ($param_arr['year_period_id'] != "") {
$query = $query . " and a.p_year_period_id = " . $param_arr['year_period_id'];
}
if ($param_arr['status_bayar'] != 0) {
if ($param_arr['status_bayar'] == 1) {
$query = $query . " and tgl_bayar is not null";
} else {
$query = $query . " and tgl_bayar is null";
}
}
if ($param_arr['tgl_penerimaan'] != "") {
$query = $query . " and trunc(tgl_bayar) >= to_date('" . $param_arr['tgl_penerimaan'] . "') ";
}
if ($param_arr['tgl_penerimaan_akhir'] != "") {
$query = $query . " and trunc(tgl_bayar) <= to_date('" . $param_arr['tgl_penerimaan_akhir'] . "') ";
}
$query = $query . " order by wp_name,p_finance_period_id, masa_pajak";
//echo $query; exit;
$dbConn->query($query);
$total_nilai = 0;
$total_realisasi = 0;
$total_sisa = 0;
while ($dbConn->next_record()) {
$item = array("t_piutang_pajak_penetapan_final_id" => $dbConn->f("t_piutang_pajak_penetapan_final_id"), "npwd" => $dbConn->f("npwd"), "wp_name" => $dbConn->f("wp_name"), "masa_pajak" => $dbConn->f("masa_pajak"), "periode_bayar" => $dbConn->f("periode_bayar"), "tgl_tap" => $dbConn->f("tgl_tap_formated"), "no_kohir" => $dbConn->f("no_kohir"), "realisasi_piutang" => $dbConn->f("realisasi_piutang"), "tgl_bayar" => $dbConn->f("tgl_bayar_formated"), "nilai_piutang" => $dbConn->f("nilai_piutang"), "sisa_piutang" => $dbConn->f("sisa_piutang"), "keterangan" => $dbConn->f("keterangan"), "p_year_period_id" => $dbConn->f("p_year_period_id"), "year_code" => $dbConn->f("year_code"));
$output .= '<tr>';
$output .= '<td align="center">' . $no++ . '</td>';
$output .= '<td align="left">' . $item['npwd'] . '</td>';
$output .= '<td align="left">' . $item['wp_name'] . '</td>';
$output .= '<td align="center">' . $item['masa_pajak'] . '</td>';
$output .= '<td align="left">' . $item['tgl_tap'] . '</td>';
$output .= '<td align="left">' . $item['no_kohir'] . '</td>';
$output .= '<td align="right">' . number_format($item['nilai_piutang'], 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($item['realisasi_piutang'], 0, ",", ".") . '</td>';
$output .= '<td align="center">' . $item['tgl_bayar'] . '</td>';
$output .= '<td align="right">' . number_format($item['sisa_piutang'], 0, ",", ".") . '</td>';
$output .= '<td align="left">' . $item['keterangan'] . '</td>';
//$output .= '<td align="center">'.$item['year_code'].'</td>';
//$output .= '<td align="center"><button class="btn_tambah" onclick="viewFormModifikasi('.$item['t_piutang_pajak_penetapan_final_id'].')">Ubah Data</button></td>';
$output .= '</tr>';
$total_nilai = $total_nilai + $item['nilai_piutang'];
$total_realisasi = $total_realisasi + $item['realisasi_piutang'];
$total_sisa = $total_sisa + $item['sisa_piutang'];
}
$output .= '<tr>';
$output .= '<td colspan=6 align="center">Total</td>';
$output .= '<td align="right">' . number_format($total_nilai, 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($total_realisasi, 0, ",", ".") . '</td>';
$output .= '<td align="center"></td>';
$output .= '<td align="right">' . number_format($total_sisa, 0, ",", ".") . '</td>';
$output .= '<td align="center"></td>';
$output .= '</tr>';
$output .= '</td></tr></table>';
$output .= '</table>';
if ($cetak_laporan == 'view_excel') {
echo $output;
exit;
}
return $output;
}
function print_excel($param_arr)
{
startExcel("cetak_piutang_2002_2012_" . str_replace(" ", "", $param_arr['vat_code']));
echo "<div><h3> PIUTANG PAJAK TAHUN 2002-2012 (" . strtoupper($param_arr['vat_code']) . ")</h3></div>";
//BESAR
$dbConn = new clsDBConnSIKP();
echo '
<br/>
<table border="1">
<tr>
<th>NO</th>
<th>NPWPD</th>
<th>NAMA WP</th>
<th>MASA PAJAK</th>
<th>BESARNYA (Rp)</th>
<th>REALISASI PIUTANG (Rp)</th>
<th>TGL BAYAR</th>
<th>SISA PIUTANG (Rp)</th>
<th>KETERANGAN</th>
</tr>';
$query = "select a.*,to_char(a.tgl_tap,'dd-mm-yyyy') as tgl_tap_formated, to_char(a.tgl_bayar,'dd-mm-yyyy') as tgl_bayar_formated , b.wp_name, c.p_finance_period_id, c.code as periode_bayar\r\n\t\t\tfrom t_piutang_pajak_penetapan_final as a\r\n\t\t\tLEFT JOIN t_cust_account as b ON a.t_cust_account_id = b.t_cust_account_id\r\n\t\t\tLEFT JOIN p_finance_period as c ON a.p_finance_period_id = c.p_finance_period_id\r\n\t\t\tWHERE a.p_vat_type_id=" . $param_arr['p_vat_type_id'] . "order by wp_name, c.start_date";
$dbConn->query($query);
$no = 1;
$output = '';
$total_piutang = 0;
$total_realisasi_piutang = 0;
$total_sisa_piutang = 0;
while ($dbConn->next_record()) {
$item = array("t_piutang_pajak_penetapan_final_id" => $dbConn->f("t_piutang_pajak_penetapan_final_id"), "npwd" => $dbConn->f("npwd"), "wp_name" => $dbConn->f("wp_name"), "masa_pajak" => $dbConn->f("masa_pajak"), "periode_bayar" => $dbConn->f("periode_bayar"), "tgl_tap" => $dbConn->f("tgl_tap_formated"), "no_kohir" => $dbConn->f("no_kohir"), "realisasi_piutang" => $dbConn->f("realisasi_piutang"), "tgl_bayar" => $dbConn->f("tgl_bayar_formated"), "nilai_piutang" => $dbConn->f("nilai_piutang"), "sisa_piutang" => $dbConn->f("sisa_piutang"), "keterangan" => $dbConn->f("keterangan"), "p_year_period_id" => $dbConn->f("p_year_period_id"), "year_code" => $dbConn->f("year_code"), "p_finance_period_id" => $dbConn->f("p_finance_period_id"));
$output .= '<tr>';
$output .= '<td align="center">' . $no++ . '</td>';
$output .= '<td align="left">' . $item['npwd'] . '</td>';
$output .= '<td align="left">' . $item['wp_name'] . '</td>';
//$output .= '<td align="left" style="color:#008000;">'.$item['p_finance_period_id'].'</td>';
$output .= '<td align="left"> ' . $item['periode_bayar'] . '</td>';
//$output .= '<td align="left">'.$item['tgl_tap'].'</td>';
//$output .= '<td align="left">'.$item['no_kohir'].'</td>';
$output .= '<td align="right">' . number_format($item['nilai_piutang'], 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($item['realisasi_piutang'], 0, ",", ".") . '</td>';
$output .= '<td align="center">' . $item['tgl_bayar'] . '</td>';
$output .= '<td align="right">' . number_format($item['sisa_piutang'], 0, ",", ".") . '</td>';
$output .= '<td align="left">' . $item['keterangan'] . '</td>';
//$output .= '<td align="center">'.$item['year_code'].'</td>';
//$output .= '<td align="center"><button class="btn_tambah" onclick="viewFormModifikasi('.$item['t_piutang_pajak_penetapan_final_id'].')">Ubah Data</button></td>';
$output .= '</tr>';
$total_piutang = $total_piutang + $item['nilai_piutang'];
$total_realisasi_piutang = $total_realisasi_piutang + $item['realisasi_piutang'];
$total_sisa_piutang = $total_sisa_piutang + $item['sisa_piutang'];
}
$output .= '<tr><td align="center" colspan=4>TOTAL</td>';
$output .= '<td align="right">' . number_format($total_piutang, 0, ",", ".") . '</td>';
$output .= '<td align="right">' . number_format($total_realisasi_piutang, 0, ",", ".") . '</td>';
$output .= '<td align="center"></td>';
$output .= '<td align="right">' . number_format($total_sisa_piutang, 0, ",", ".") . '</td>';
$output .= '<td align="left"></td></tr>';
$output .= '</table>';
$dbConn->close();
echo $output;
exit;
}
function print_excel($param_arr)
{
startExcel("daftar_nota_verifikasi_bphtb.xls");
$textFilter = '';
if (!empty($param_arr['filter_lap'])) {
if ($param_arr['filter_lap'] == 1) {
//sudah bayar
$textFilter = '(Sudah Bayar)';
}
if ($param_arr['filter_lap'] == 2) {
//belum bayar
$textFilter = 'YANG BELUM ADA KONFIRMASI LEBIH LANJUT OLEH PEMOHON';
}
if ($param_arr['filter_lap'] == 3) {
//belum bayar
$textFilter = '(Nihil)';
}
}
echo '<div><h3>LAPORAN VERIFIKASI BPHTB ' . $textFilter . '</h3></div>';
echo "<div><b>Tanggal : " . dateToString($param_arr['date_start'], "-") . " s.d " . dateToString($param_arr['date_end'], "-") . "</b></div>";
echo '<table border="1">';
echo '<tr>
<th>NO</th>
<th>TANGGAL</th>
<th>NO.REGISTRASI</th>
<th>NAMA WP</th>
<th>JENIS TRANSAKSI</th>
<th>NOP</th>
<th>LT/LB</th>
<th>HARGA TANAH / m2 (Rp)</th>
<th>HARGA BANGUNAN / m2 (Rp)</th>
<th>TOTAL NJOP (Rp)</th>
<th>HARGA PASAR / <br/> TRANSAKSI / <br/> LELANG </th>
<th>NILAI PAJAK <br/> YANG HARUS DIBAYAR (Rp)</th>
<th>PEMERIKSA</th>
</tr>';
$dbConn = new clsDBConnSIKP();
$whereClause = '';
if (!empty($param_arr['date_start']) && !empty($param_arr['date_end'])) {
$whereClause .= " AND (trunc(reg_bphtb.creation_date) BETWEEN '" . $param_arr['date_start'] . "'";
$whereClause .= " AND '" . $param_arr['date_end'] . "')";
} else {
if (!empty($param_arr['date_start']) && empty($param_arr['date_end'])) {
$whereClause .= " AND trunc(reg_bphtb.creation_date) >= '" . $param_arr['date_start'] . "'";
} else {
if (empty($param_arr['date_start']) && !empty($param_arr['date_end'])) {
$whereClause .= " AND trunc(reg_bphtb.creation_date) <= '" . $param_arr['date_end'] . "'";
}
}
}
if (!empty($param_arr['filter_lap'])) {
if ($param_arr['filter_lap'] == 1) {
//sudah bayar
$whereClause .= " AND (payment.receipt_no is not null or payment.receipt_no <> '') ";
$whereClause .= " AND ( bphtb_amt_final > 0 ) ";
}
if ($param_arr['filter_lap'] == 2) {
//belum bayar
$whereClause .= " AND ( payment.receipt_no is null or payment.receipt_no = '') ";
$whereClause .= " AND ( bphtb_amt_final > 0 ) ";
}
if ($param_arr['filter_lap'] == 3) {
//nihil
$whereClause .= " AND ( bphtb_amt_final < 1 ) ";
}
}
$query = "SELECT\r\n\t\t\t\tto_char(reg_bphtb.creation_date, 'YYYY-MM-DD') as creation_date,\r\n\t\t\t\tregistration_no,\r\n\t\t\t\twp_name,\r\n\t\t\t\treg_bphtb.p_bphtb_legal_doc_type_id,\r\n\t\t\t\tbphtb_doc.description,\r\n\t\t\t\tnjop_pbb,\r\n\t\t\t\tland_area,\r\n\t\t\t\tland_total_price,\r\n\t\t\t\tbuilding_area,\r\n\t\t\t\tbuilding_total_price,\r\n\t\t\t\tmarket_price,\r\n\t\t\t\tbphtb_amt_final,\r\n\t\t\t\tland_price_per_m,\r\n\t\t\t\treg_bphtb.verificated_by,\r\n\t\t\t\tbuilding_price_per_m\r\n\t\t\tFROM\r\n\t\t\t\tsikp.t_bphtb_registration reg_bphtb\r\n\t\t\tLEFT JOIN p_bphtb_legal_doc_type bphtb_doc on bphtb_doc.p_bphtb_legal_doc_type_id = reg_bphtb.p_bphtb_legal_doc_type_id\r\n\t\t\tLEFT JOIN t_customer_order cust_order ON cust_order.t_customer_order_id = reg_bphtb.t_customer_order_id \r\n\t\t\tLEFT JOIN t_payment_receipt_bphtb payment ON reg_bphtb.t_bphtb_registration_id = payment.t_bphtb_registration_id \r\n\t\t\tWHERE cust_order.p_order_status_id <> 1";
$query .= $whereClause;
$query .= " order by trunc(reg_bphtb.creation_date) ASC,upper(wp_name) ASC";
$dbConn->query($query);
$no = 1;
$jumlah = 0;
$jumlah = 0;
$total_nilai_pajak = 0;
$nilai_njop = 0;
while ($dbConn->next_record()) {
$items[] = $item = array('creation_date' => $dbConn->f("creation_date"), 'registration_no' => $dbConn->f("registration_no"), 'wp_name' => $dbConn->f("wp_name"), 'p_bphtb_legal_doc_type_id' => $dbConn->f("p_bphtb_legal_doc_type_id"), 'description' => $dbConn->f("description"), 'njop_pbb' => $dbConn->f("njop_pbb"), 'land_area' => $dbConn->f("land_area"), 'land_total_price' => $dbConn->f("land_total_price"), 'building_area' => $dbConn->f("building_area"), 'building_total_price' => $dbConn->f("building_total_price"), 'market_price' => $dbConn->f("market_price"), 'bphtb_amt_final' => $dbConn->f("bphtb_amt_final"), 'land_price_per_m' => $dbConn->f("land_price_per_m"), 'verificated_by' => $dbConn->f("verificated_by"), 'building_price_per_m' => $dbConn->f("building_price_per_m"));
$nilai_njop = $dbConn->f("building_total_price") + $dbConn->f("land_total_price");
echo '<tr>
<td>' . $no . '</td>
<td>' . dateToString($item['creation_date']) . '</td>
<td> ' . $item['registration_no'] . ' </a></td>
<td>' . $item['wp_name'] . '</td>
<td>' . $item['description'] . '</td>
<td> ' . $item['njop_pbb'] . '</td>
<td align="right">' . number_format($item['land_area'], 0, ",", ".") . " / " . number_format($item['building_area'], 0, ",", ".") . '</td>
<td align="right">' . number_format($item['land_price_per_m'], 0, ",", ".") . '</td>
<td align="right">' . number_format($item['building_price_per_m'], 0, ",", ".") . '</td>
<td align="right">' . number_format($nilai_njop, 0, ",", ".") . '</td>
<td align="right">' . number_format($item['market_price'], 0, ",", ".") . '</td>
<td align="right">' . number_format($item['bphtb_amt_final'], 0, ",", ".") . '</td>
<td>' . $item['verificated_by'] . '</td>
</tr>';
$jumlah += $dbConn->f("amount");
// $jumlah_wp+=$dbConn->f("jumlah_wp");
$total_nilai_pajak += $item['bphtb_amt_final'];
$no++;
}
echo '<tr>
<td colspan="11" align="center"><b> TOTAL </b></td>
<td align="right"><b>' . number_format($total_nilai_pajak, 2, ",", ".") . '</b></td>
</tr>';
echo '</table>';
exit;
}
function print_excel($param_arr)
{
startExcel("daftar_bphtb_" . date('Y-m-d'));
echo "<div><h3> LAPORAN DAFTAR BPHTB </h3></div>";
echo "<div><b>TANGGAL : " . dateToString($param_arr['date_start']) . " s/d " . dateToString($param_arr['date_end']) . "</b></div>";
echo '<table border="1">';
echo '<tr>
<th rowspan="2">NO</th>
<th rowspan="2">TANGGAL</th>
<th rowspan="2">NO ORDER</th>
<th rowspan="2">NO REGISTRASI</th>
<th rowspan="2">NAMA WAJIB PAJAK</th>
<th rowspan="2">NPWP</th>
<th rowspan="2">JENIS DOKUMEN</th>
<th colspan="4">ALAMAT WAJIB PAJAK</th>
<th rowspan="2">NO TELP</th>
<th rowspan="2">NO HP</th>
<th rowspan="2">NJOP PBB</th>
<th colspan="4">ALAMAT OBJEK PAJAK</th>
<th colspan="3">TANAH</th>
<th colspan="3">BANGUNAN</th>
<th rowspan="2">HARGA TRANSAKSI / <br> PASAR / <br> LELANG (Rp)</th>
<th rowspan="2">NPOP (Rp)</th>
<th rowspan="2">NPOP TKP (Rp)</th>
<th rowspan="2">NPOP KP (Rp)</th>
<th rowspan="2">BPTHTB AMOUNT (Rp)</th>
<th rowspan="2">BPTHTB DISKON (Rp)</th>
<th rowspan="2">BPTHTB TOTAL (Rp)</th>
<th rowspan="2">KETERANGAN</th>
<th rowspan="2">PEMERIKSA</th>
<th rowspan="2">NIP PEMERIKSA</th>
</tr>';
echo '
<tr>
<th>ALAMAT</th>
<th>RT/RW</th>
<th>KECAMATAN</th>
<th>KELURAHAN</th>
<th>ALAMAT</th>
<th>RT/RW</th>
<th>KECAMATAN</th>
<th>KELURAHAN</th>
<th>LUAS TANAH (m2)</th>
<th>HARGA PER METER (Rp)</th>
<th>HARGA TANAH (Rp)</th>
<th>LUAS BANGUNAN (m2)</th>
<th>HARGA PER METER (Rp)</th>
<th>HARGA BANGUNAN (Rp)</th>
</tr>
';
$dbConn = new clsDBConnSIKP();
$whereClause = " WHERE ";
if (!empty($param_arr['date_start']) && !empty($param_arr['date_end'])) {
$whereClause .= " (trunc(a.creation_date) BETWEEN '" . $param_arr['date_start'] . "'";
$whereClause .= " AND '" . $param_arr['date_end'] . "')";
} else {
if (!empty($param_arr['date_start']) && empty($param_arr['date_end'])) {
$whereClause .= " trunc(a.creation_date) >= '" . $param_arr['date_start'] . "'";
} else {
if (empty($param_arr['date_start']) && !empty($param_arr['date_end'])) {
$whereClause .= " trunc(a.creation_date) <= '" . $param_arr['date_end'] . "'";
}
}
}
$query = "SELECT a.*, to_char(a.creation_date,'yyyy-mm-dd') AS tgl_bphtb, b.order_no, kelurahan_wp.region_name AS kelurahan_wp_name, kecamatan_wp.region_name AS kecamatan_wp_name,\r\n\t\t\tkelurahan_op.region_name AS kelurahan_op_name, kecamatan_op.region_name AS kecamatan_op_name,\r\n\t\t\tdoc_type.description AS nama_dokumen\r\n\r\n\t\t\tFROM t_bphtb_registration AS a\r\n\t\t\tLEFT JOIN t_customer_order AS b ON a.t_customer_order_id = b.t_customer_order_id\r\n\t\t\tLEFT JOIN p_region AS kelurahan_wp ON a.wp_p_region_id_kel = kelurahan_wp.p_region_id\r\n\t\t\tLEFT JOIN p_region AS kecamatan_wp ON a.wp_p_region_id_kec = kecamatan_wp.p_region_id\r\n\t\t\tLEFT JOIN p_region AS kelurahan_op ON a.object_p_region_id_kel = kelurahan_op.p_region_id\r\n\t\t\tLEFT JOIN p_region AS kecamatan_op ON a.object_p_region_id_kec = kecamatan_op.p_region_id\r\n\t\t\tLEFT JOIN p_bphtb_legal_doc_type AS doc_type ON a.p_bphtb_legal_doc_type_id = doc_type.p_bphtb_legal_doc_type_id";
$query .= $whereClause;
$query .= " ORDER BY a.registration_no ASC";
$dbConn->query($query);
$no = 1;
while ($dbConn->next_record()) {
$item = array('tgl_bphtb' => $dbConn->f("tgl_bphtb"), 'order_no' => $dbConn->f("order_no"), 'registration_no' => $dbConn->f("registration_no"), 'wp_name' => $dbConn->f("wp_name"), 'npwp' => $dbConn->f("npwp"), 'wp_address_name' => $dbConn->f("wp_address_name"), 'wp_rt' => $dbConn->f("wp_rt"), 'wp_rw' => $dbConn->f("wp_rw"), 'kelurahan_wp_name' => $dbConn->f("kelurahan_wp_name"), 'kecamatan_wp_name' => $dbConn->f("kecamatan_wp_name"), 'phone_no' => $dbConn->f("phone_no"), 'mobile_phone_no' => $dbConn->f("mobile_phone_no"), 'njop_pbb' => $dbConn->f("njop_pbb"), 'object_address_name' => $dbConn->f("object_address_name"), 'object_rt' => $dbConn->f("object_rt"), 'object_rw' => $dbConn->f("object_rw"), 'kelurahan_op_name' => $dbConn->f("kelurahan_op_name"), 'kecamatan_op_name' => $dbConn->f("kecamatan_op_name"), 'nama_dokumen' => $dbConn->f("nama_dokumen"), 'land_area' => $dbConn->f("land_area"), 'land_price_per_m' => $dbConn->f("land_price_per_m"), 'land_total_price' => $dbConn->f("land_total_price"), 'building_area' => $dbConn->f("building_area"), 'building_price_per_m' => $dbConn->f("building_price_per_m"), 'building_total_price' => $dbConn->f("building_total_price"), 'market_price' => $dbConn->f("market_price"), 'npop' => $dbConn->f("npop"), 'npop_tkp' => $dbConn->f("npop_tkp"), 'npop_kp' => $dbConn->f("npop_kp"), 'bphtb_amt' => $dbConn->f("bphtb_amt"), 'bphtb_discount' => $dbConn->f("bphtb_discount"), 'bphtb_amt_final' => $dbConn->f("bphtb_amt_final"), 'description' => $dbConn->f("description"), 'creation_date' => $dbConn->f("creation_date"), 'created_by' => $dbConn->f("created_by"), 'updated_date' => $dbConn->f("updated_date"), 'updated_by' => $dbConn->f("updated_by"), 'verificated_by' => $dbConn->f("verificated_by"), 'verificated_nip' => $dbConn->f("verificated_nip"));
echo '<tr>';
echo '<td align="center">' . $no++ . '</td>';
echo '<td align="left"> ' . dateToString($item['tgl_bphtb']) . '</td>';
echo '<td align="left"> ' . $item['order_no'] . '</td>';
echo '<td align="left"> ' . $item['registration_no'] . '</td>';
echo '<td align="left">' . strtoupper($item['wp_name']) . '</td>';
echo '<td align="left"> ' . $item['npwp'] . '</td>';
echo '<td align="left">' . $item['nama_dokumen'] . '</td>';
echo '<td align="left">' . strtoupper($item['wp_address_name']) . '</td>';
echo '<td align="left">' . $item['wp_rt'] . ' / ' . $item['wp_rw'] . '</td>';
echo '<td align="left">' . $item['kecamatan_wp_name'] . '</td>';
echo '<td align="left">' . $item['kelurahan_wp_name'] . '</td>';
echo '<td align="left"> ' . $item['phone_no'] . '</td>';
echo '<td align="left"> ' . $item['mobile_phone_no'] . '</td>';
echo '<td align="left"> ' . $item['njop_pbb'] . '</td>';
echo '<td align="left">' . strtoupper($item['object_address_name']) . '</td>';
echo '<td align="left">' . $item['object_rt'] . ' / ' . $item['object_rw'] . '</td>';
echo '<td align="left">' . $item['kecamatan_op_name'] . '</td>';
echo '<td align="left">' . $item['kelurahan_op_name'] . '</td>';
echo '<td align="right">' . number_format($item['land_area'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['land_price_per_m'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['land_total_price'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['building_area'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['building_price_per_m'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['building_total_price'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['market_price'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['npop'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['npop_tkp'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['npop_kp'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['bphtb_amt'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['bphtb_discount'], 0, ",", ".") . '</td>';
echo '<td align="right">' . number_format($item['bphtb_amt_final'], 0, ",", ".") . '</td>';
echo '<td align="left">' . $item['description'] . '</td>';
echo '<td align="left">' . $item['verificated_by'] . '</td>';
echo '<td align="left"> ' . $item['verificated_nip'] . '</td>';
echo '</tr>';
}
echo '</table>';
exit;
}
function print_excel($param_arr)
{
startExcel("lapora_global_per_wp");
echo "<div><h3> Penerimaan Global Per WP</h3></div>";
echo "<div><b>Jenis Pajak : " . $param_arr['rqst_type_code'] . "</b></div>";
echo "<div><b>Tanggal : " . dateToString($param_arr['date_start']) . " s.d " . dateToString($param_arr['date_end']) . "</b></div><br/>";
$dbConn = new clsDBConnSIKP();
$query = "select a.*, f_get_wilayah(b.npwd) as kode_wilayah , to_char(b.active_date,'dd-mm-yyyy') as active_date,\r\n\t\t\tb.brand_address_name ||' '|| nvl(b.brand_address_no,'') as alamat_new\r\n\t\t\tfrom sikp.f_laporan_global_wp2(" . $param_arr['p_rqst_type_id'] . ",'" . $param_arr['date_start'] . "', '" . $param_arr['date_end'] . "') a\r\n\t\t\tleft join t_cust_account b on a.npwpd = b.npwd\r\n\t\t\twhere f_get_wilayah_id(b.npwd) = " . $param_arr['kode_wilayah'] . " \r\n\t\t\torder by jenis_pajak,TRIM(company_brand)";
$dbConn->query($query);
$no = 1;
$jumlah = 0;
echo '<table border="1">';
echo '<tr>
<th>NO</th>
<th>NAMA WP</th>
<th>ALAMAT</th>
<th>NPWPD</th>
<th>BESARNYA (Rp)</th>
<th>JML SSPD</th>
<th>NAMA AYAT</th>
<th>PENGUKUHAN</th>
<th>KETERANGAN</th>
</tr>';
while ($dbConn->next_record()) {
$items[] = array('nama_wp' => $dbConn->f("nama_wp"), 'alamat_new' => $dbConn->f("alamat_new"), 'npwpd' => $dbConn->f("npwpd"), 'amount' => $dbConn->f("amount"), 'tot_sspd' => $dbConn->f("tot_sspd"), 'kode_wilayah' => $dbConn->f("kode_wilayah"), 'active_date' => $dbConn->f("active_date"), 'jenis_pajak' => $dbConn->f("jenis_pajak"));
echo '<tr>';
echo '<td align="center">' . $no . '</td>';
echo '<td>' . $dbConn->f("nama_wp") . '</td>';
echo '<td>' . $dbConn->f("alamat_new") . '</td>';
echo '<td>' . $dbConn->f("npwpd") . '</td>';
echo '<td align="right">' . number_format($dbConn->f("amount"), 2, ",", ".") . '</td>';
echo '<td align="right">' . $dbConn->f("tot_sspd") . '</td>';
echo '<td>' . $dbConn->f('jenis_pajak') . '</td>';
echo '<td>' . $dbConn->f('active_date') . '</td>';
echo '<td>' . $dbConn->f('kode_wilayah') . '</td>';
echo '</tr>';
$jumlah += $dbConn->f("amount");
$jumlah_wp += $dbConn->f("tot_sspd");
$no++;
}
echo '<tr>
<td colspan="4" align="center"> <b> JUMLAH </b> </td>
<td align="right"><b>' . number_format($jumlah, 2, ",", ".") . '</b></td>
<td align="right"><b>' . $jumlah_wp . '</b></td>
</tr>';
echo '</table>';
exit;
}
function print_excel($param_arr)
{
$date_start = str_replace("'", "", $param_arr['tgl_penerimaan']);
$date_end = str_replace("'", "", $param_arr['tgl_penerimaan_last']);
$date_arr = explode("-", $date_start);
$year_date = $date_arr[2];
startExcel("laporan_realisasi_harian_dan_piutang");
echo "<div><h3> LAPORAN REALISASI HARIAN PER JENIS PAJAK</h3></div>";
echo "<b>TAHUN " . $year_date . "<b/> <br/>";
echo "<b>Tanggal Penerimaan : " . $date_start . " s.d " . $date_end . " </b><br/>";
echo '<table width="100%" border="1">';
echo '<tr>
<th>NO</th>
<th>NO.AYAT</th>
<th>NAMA AYAT</th>
<th>NO.KOHIR</th>
<th>NAMA WP</th>
<th>NPWPD</th>
<th>JUMLAH</th>
<th>MASA PAJAK</th>
<th>TGL TAP</th>
<th>TGL BAYAR</th>
</tr>';
$dbConn = new clsDBConnSIKP();
$query = '';
$jenis_laporan = $param_arr['jenis_laporan'];
$tgl_penerimaan = $param_arr['tgl_penerimaan'];
$p_year_period_id = $param_arr['p_year_period_id'];
$p_vat_type_id = $param_arr['p_vat_type_id'];
$i_flag_setoran = $param_arr['i_flag_setoran'];
$tgl_penerimaan_last = $param_arr['tgl_penerimaan_last'];
$date_start = str_replace("'", "", $tgl_penerimaan);
$year_date = DateTime::createFromFormat('d-m-Y', $date_start)->format('Y');
$border = $year_date - 1;
if ($jenis_laporan == 'all') {
$query = "select *,trunc(payment_date) from f_rep_bpps_piutang2new({$p_vat_type_id}, {$p_year_period_id}, {$tgl_penerimaan}, {$tgl_penerimaan_last}, {$i_flag_setoran}) order by kode_jns_trans, kode_jns_pajak, kode_ayat";
} else {
if ($jenis_laporan == 'piutang') {
$query = "select *,trunc(payment_date) \r\n\t\tfrom f_rep_bpps_piutang({$p_vat_type_id}, {$p_year_period_id}, {$tgl_penerimaan}, {$tgl_penerimaan_last}, {$i_flag_setoran}) rep\r\n\t\tWHERE\r\n\t\t(\tSUBSTRING(rep.masa_pajak,22,4) < {$year_date}\r\n\t\t\tAND \r\n\t\t\t(NOT (SUBSTRING(rep.masa_pajak,22,4) = {$border}\r\n\t\t\tAND SUBSTRING(rep.masa_pajak,19,2) = 12))\r\n\t\t)\r\n\t\tOR\r\n\t\t(\r\n\t\t\t(SUBSTRING(rep.masa_pajak,22,4) = {$year_date}\r\n\t\t\tAND SUBSTRING(rep.masa_pajak,19,2) = 12)\r\n\t\t)\r\n\t\tOR\r\n\t\t(\r\n\t\t\tSUBSTRING(rep.masa_pajak,22,4) > {$year_date}\r\n\t\t)\r\n\t\torder by kode_ayat, npwpd, masa_pajak";
} else {
if ($jenis_laporan == 'murni') {
$query = "select *,trunc(payment_date) \r\n\t\tfrom f_rep_bpps_piutang3new({$p_vat_type_id}, {$p_year_period_id}, {$tgl_penerimaan}, {$tgl_penerimaan_last}, {$i_flag_setoran}) rep\r\n\t\tWHERE\r\n\t\tEXTRACT (YEAR FROM rep.settlement_date) = {$year_date}\r\n\t\torder by kode_ayat, npwpd, masa_pajak";
}
}
}
$dbConn->query($query);
$tgl_penerimaan = str_replace("'", "", $tgl_penerimaan);
$tgl_penerimaan_last = str_replace("'", "", $tgl_penerimaan_last);
$tahun = date("Y", strtotime($tgl_penerimaan));
while ($dbConn->next_record()) {
$data[] = array("kode_jns_trans" => $dbConn->f("kode_jns_trans"), "jns_trans" => $dbConn->f("jns_trans"), "kode_jns_pajak" => $dbConn->f("kode_jns_pajak"), "kode_ayat" => $dbConn->f("kode_ayat"), "jns_pajak" => $dbConn->f("jns_pajak"), "jns_ayat" => $dbConn->f("jns_ayat"), "nama_ayat" => $dbConn->f("nama_ayat"), "no_kohir" => $dbConn->f("no_kohir"), "wp_name" => $dbConn->f("wp_name"), "wp_address_name" => $dbConn->f("wp_address_name"), "wp_address_no" => $dbConn->f("wp_address_no"), "npwpd" => $dbConn->f("npwpd"), "jumlah_terima" => $dbConn->f("jumlah_terima"), "masa_pajak" => $dbConn->f("masa_pajak"), "kd_tap" => $dbConn->f("kd_tap"), "keterangan" => $dbConn->f("keterangan"), "payment_date" => $dbConn->f("payment_date"), "jam" => $dbConn->f("jam"));
}
$dbConn->close();
$no = 1;
$jumlahtemp = 0;
$jumlahperayat = array();
$jumlahperwaktu = array();
$i = 0;
$total = 0;
foreach ($data as $item) {
echo '<tr>';
echo '<td align="center">' . $no . '</td>';
echo '<td align="left">' . $item["kode_jns_pajak"] . ' ' . $item["kode_ayat"] . '</td>';
echo '<td align="left">' . $item["nama_ayat"] . '</td>';
echo '<td align="left">' . $item["no_kohir"] . '</td>';
echo '<td align="left">' . $item["wp_name"] . '</td>';
echo '<td align="left">' . $item["npwpd"] . '</td>';
echo '<td align="right">' . number_format($item["jumlah_terima"], 0, ",", ".") . '</td>';
echo '<td align="left">' . $item["masa_pajak"] . '</td>';
echo '<td align="left">' . $item["kd_tap"] . '</td>';
echo '<td align="left">' . $item["payment_date"] . '</td>';
echo '</tr>';
$no++;
//hitung jumlahperayat sampai baris ini
$jumlahtemp += $item["jumlah_terima"];
$total += $item["jumlah_terima"];
//cek apakah perlu bikin baris jumlah
//jika iya, simpan jumlahtemp ke jumlahperayat, print jumlahtemp, reset jumlahtemp
$ayat = $item["kode_ayat"];
$ayatsesudah = $data[$i + 1]["kode_ayat"];
if ($ayat != $ayatsesudah && count($data) > 1 || empty($data[$i + 1])) {
$jumlahperayat[] = $jumlahtemp;
echo '<tr>
<td align="center" colspan="9"> <b> JUMLAH ' . strtoupper($item["jns_pajak"]) . " " . $item["nama_ayat"] . ' </b></td>
<td align="right"><b>' . number_format($jumlahtemp, 0, ",", ".") . '</b></td>
</tr>';
$jumlahtemp = 0;
}
$i++;
}
echo '<tr>
<td align="center" colspan="9"> <b>TOTAL ' . strtoupper($item["jns_pajak"]) . '</b></td>
<td align="right"><b>' . number_format($total, 0, ",", ".") . '</b></td>
</tr>';
echo '</table>';
exit;
}