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) . '&nbsp</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">&nbsp;' . $item['receipt_no'] . '</td>';
        echo '<td align="left">&nbsp;' . $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">&nbsp;' . $item['periode_pelaporan'] . '</td>';
        $output .= '<td align="left">' . $item['periode_awal_laporan'] . ' s.d ' . $item['periode_awal_laporan'] . '</td>';
        $output .= '<td align="center">&nbsp;' . $item['tgl_pelaporan'] . '</td>';
        $output .= '<td align="center">&nbsp;' . $item['jatuh_tempo'] . '</td>';
        $output .= '<td align="center">&nbsp;' . $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">&nbsp;' . $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;
}
Exemplo n.º 16
0
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">&nbsp;' . $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"> &nbsp; </td>
			<td align="center"> &nbsp; </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;
}
Exemplo n.º 23
0
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">&nbsp;' . $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>&nbsp;' . $item['registration_no'] . ' </a></td>
				<td>' . $item['wp_name'] . '</td>	
				<td>' . $item['description'] . '</td>	
				<td>&nbsp;' . $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">&nbsp;' . dateToString($item['tgl_bphtb']) . '</td>';
        echo '<td align="left">&nbsp;' . $item['order_no'] . '</td>';
        echo '<td align="left">&nbsp;' . $item['registration_no'] . '</td>';
        echo '<td align="left">' . strtoupper($item['wp_name']) . '</td>';
        echo '<td align="left">&nbsp;' . $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">&nbsp;' . $item['phone_no'] . '</td>';
        echo '<td align="left">&nbsp;' . $item['mobile_phone_no'] . '</td>';
        echo '<td align="left">&nbsp;' . $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">&nbsp;' . $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;
}