<?php

require_once '../../assets/excel/PHPExcel.php';

// koneksi ke database

include("../koneksi.php");

$thn=$_GET['thn'];
$axx=$_GET['axx'];
$bxx=$_GET['bxx'];
$cxx=$_GET['cxx'];
$axxx=$_GET['axxx'];
$tm1=$_GET['m'];
$tm1=str_replace('/','-',$tm1);
$tt1=date('Y',strtotime($tm1));
$bb1=date('m',strtotime($tm1));
$hh1=date('d',strtotime($tm1));

$ts1=$_GET['s'];
$ts1=str_replace('/','-',$ts1);
$tt2=date('Y',strtotime($ts1));
$bb2=date('m',strtotime($ts1));
$hh2=date('d',strtotime($ts1));


$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);
//$objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(0)->getColumnDimension('A')->setWidth(5);
/*$objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(9.29);*/
$objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(12.57);
/*$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(30);*/
$objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(26);
/*$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(12);*/
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(40);
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(16.86);
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('G')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('H')->setWidth(7);

$objPHPExcel->getSheet(0)->getColumnDimension('I')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('J')->setWidth(7);
$objPHPExcel->getSheet(0)->getColumnDimension('K')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('L')->setWidth(7);
$objPHPExcel->getSheet(0)->getColumnDimension('M')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('N')->setWidth(7);

$objPHPExcel->getSheet(0)->getColumnDimension('O')->setWidth(20.71);
$objPHPExcel->getSheet(0)->getColumnDimension('P')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('Q')->setWidth(17);

$objPHPExcel->getSheet(0)->getColumnDimension('S')->setWidth(24);
$objPHPExcel->getSheet(0)->getColumnDimension('T')->setWidth(12);

/*$objPHPExcel->getSheet(0)->getColumnDimension('O')->setWidth(12);
$objPHPExcel->getSheet(0)->getColumnDimension('P')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('Q')->setWidth(17);
$objPHPExcel->getSheet(0)->getColumnDimension('R')->setWidth(12);

$objPHPExcel->getSheet(0)->getColumnDimension('U')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('V')->setWidth(11.14);
$objPHPExcel->getSheet(0)->getColumnDimension('W')->setWidth(9);*/


$styleBold = array('font'  => array('bold'  => true));
$objPHPExcel->getSheet(0)->getStyle('A3:T3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A3:T3')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getRowDimension('1')->setRowHeight(15);
$objPHPExcel->getSheet(0)->setTitle('AMBIL DATA SETOR');

$objPHPExcel->getSheet(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getSheet(0)->setCellValue('A1', 'Tanggal Setor : '.$tm1.' s/d '.$ts1);
$objPHPExcel->getActiveSheet()->getStyle('A1:T1')->getAlignment()->setWrapText(false);


$objPHPExcel->getSheet(0)
            ->setCellValue('A3', 'No')
            /* ->setCellValue('B3', 'No DPA') */
            ->setCellValue('B3', 'Kd Panggil')
            /*->setCellValue('D3', 'Sub') */
            ->setCellValue('C3', 'Rekening')
            /*->setCellValue('F3', 'Kd Sibaku')*/
            ->setCellValue('D3', 'Uraian')
            ->setCellValue('E3', 'Nilai Kwitansi')
            ->setCellValue('F3', 'PPN')
            ->setCellValue('G3', 'PPH-21')
            ->setCellValue('H3', 'Pasal')
			->setCellValue('I3', 'PPH-22')
            ->setCellValue('J3', 'Pasal')
			->setCellValue('K3', 'PPH-23')
            ->setCellValue('L3', 'Pasal')
			->setCellValue('M3', 'PPH-Fnl')
            ->setCellValue('N3', 'Pasal')

            ->setCellValue('O3', 'No Kwi/SP/SPK') /*I*/
            ->setCellValue('P3', 'No Faktur') /*J*/
            ->setCellValue('Q3', 'Tgl Faktur') /*K*/
			->setCellValue('R3', 'Ket') /*L*/
			->setCellValue('S3', 'BA Denda') /*L*/
			->setCellValue('T3', 'Nilai Denda'); /*L*/


/*
						  $query = "SELECT s.no_angg,s.kd_panggil,s.sub_kegiatan,s.ra,s.rb,s.rc,s.rd,s.re,s.rf,s.rg,s.rh,s.kd_sibaku,s.kegiatan,
                                    p.kegiatan_pengadaan,l.tanggal_setor,
                                    l.nilai_pelunasan,l.ppn,l.pph_21,l.pph_22,l.pph_23,l.pph_final,
                                    p.no_sp,l.no_faktur,p.tgl_mulai_sp,p.tgl_selesai_sp,r.st_buku,r.st_termin,l.tanggal_input,l.id,r.pekerjaan_id,
                                    CASE WHEN r.st_termin='termin' THEN
                                    l.data_ke   
                                    ELSE '0' END,COALESCE(n.nama,p.cad1),l.tanggal_dokumen,r.st_buku,l.tanggal_faktur,l.ket,r.stum,
									(SELECT pg.nama_non FROM sp_peg pg WHERE pg.pekerjaan_id=p.id),st.untuk,st.tgl_berangkat,st.tgl_kembali
                                    FROM pekerjaan p
                                    INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
                                    INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
                                    INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
                                    LEFT OUTER JOIN rekanan n ON (n.id=p.rekanan_id)
                                    LEFT OUTER JOIN sp_peg sp ON (p.id=sp.pekerjaan_id)
									LEFT OUTER JOIN sp_spt st ON (sp.sp_spt_id=st.id)
                                    WHERE s.thn='$thn' AND l.status_berkas='FIX' AND l.tanggal_setor IS NOT NULL AND l.tanggal_lunas IS NULL
                                    ".($tm1 == null ? "" : "AND l.tanggal_setor::DATE BETWEEN '$tt1-$bb1-$hh1' AND '$tt2-$bb2-$hh2'")."
                                    ".($axx == '' ? "" : "AND s.kpa_id='$axx'")."
                                    ".($cxx == 'ALL' ? "" : "
                                        ".($cxx == 'FARMASI' ? "AND lower(s.sub_kegiatan) LIKE '%farmasi%'" : "AND lower(s.sub_kegiatan) NOT LIKE '%farmasi%'")."
                                        ")."
                                    ".($bxx == 'ALL' ? "" : "
                                        ".($bxx == 'NON UANG MUKA' ? "AND l.status_um='Non Uang Muka'" : "AND l.status_um='Uang Muka'")."
                                        ")."
                                    ".($axxx == 'ALL' ? "" : "
                                        ".($axxx == 'FUNGSIONAL' ? "AND LEFT(s.kegiatan,2) NOT IN ('**')" : "AND LEFT(s.kegiatan,2) IN ('**')")."
                                        ")."
                                    ";
*/

$query = "SELECT s.no_angg,s.kd_panggil,s.sub_kegiatan,s.ra,s.rb,s.rc,s.rd,s.re,s.rf,s.rg,s.rh,s.kd_sibaku,s.kegiatan,
                                    p.kegiatan_pengadaan,l.tanggal_setor,
                                    l.nilai_pelunasan,l.ppn,l.pph_21,l.pph_22,l.pph_23,l.pph_final,
                                    p.no_sp,l.no_faktur,p.tgl_mulai_sp,p.tgl_selesai_sp,r.st_buku,r.st_termin,l.tanggal_input,l.id,r.pekerjaan_id,
                                    CASE WHEN r.st_termin='termin' THEN
                                    l.data_ke   
                                    ELSE '0' END,COALESCE(n.nama,p.cad1),l.tanggal_dokumen,r.st_buku,l.tanggal_faktur,l.ket,r.stum,
									(SELECT pg.nama_non FROM sp_peg pg WHERE pg.pekerjaan_id=p.id),st.untuk,st.tgl_berangkat,st.tgl_kembali,rb.nomor_ba,rb.nilai_ba
                                    FROM pekerjaan p
                                    INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
                                    INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
                                    INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
                                    LEFT OUTER JOIN rekanan n ON (n.id=p.rekanan_id)
                                    LEFT OUTER JOIN sp_peg sp ON (p.id=sp.pekerjaan_id)
									LEFT OUTER JOIN sp_spt st ON (sp.sp_spt_id=st.id)
                                    LEFT OUTER JOIN (SELECT * FROM riwayat_ba WHERE berita_acara_id=8) rb ON (l.id=rb.vr_pelunasan_id)
                                    WHERE s.thn='$thn' AND l.status_berkas='FIX' AND l.tanggal_setor IS NOT NULL AND l.tanggal_lunas IS NULL
                                    ".($tm1 == null ? "" : "AND l.tanggal_setor::DATE BETWEEN '$tt1-$bb1-$hh1' AND '$tt2-$bb2-$hh2'")."
                                    ".($axx == '' ? "" : "AND s.kpa_id='$axx'")."
                                    ".($cxx == 'ALL' ? "" : "
                                        ".($cxx == 'FARMASI' ? "AND lower(s.sub_kegiatan) LIKE '%farmasi%'" : "AND lower(s.sub_kegiatan) NOT LIKE '%farmasi%'")."
                                        ")."
                                    ".($bxx == 'ALL' ? "" : "
                                        ".($bxx == 'NON UANG MUKA' ? "AND l.status_um='Non Uang Muka'" : "AND l.status_um='Uang Muka'")."
                                        ")."
                                    ".($axxx == 'ALL' ? "" : "
                                        ".($axxx == 'FUNGSIONAL' ? "AND LEFT(s.kegiatan,2) NOT IN ('**')" : "AND LEFT(s.kegiatan,2) IN ('**')")."
                                        ")."
                                    ";

$hasil = pg_query($query);
$baris = 4;
$a=1;
while ($r = pg_fetch_array($hasil))
{


if($r[31]=='') {$soporek=$r[37];} else {$soporek=$r[31];}
$stum=$r['stum'];

$ket1=strtoupper($r['ket']);
if ($ket1=='KURANG BAYAR') {$ket2=' ('.$ket1.')';} else {$ket2='';}

	//if ($r[30]=='0') {$trm='';} else {$trm=' - '.strtoupper(($r['st_termin'].' '.$r[30]));}
	if ($r[30]=='0') {
    	if ($stum=='uang_muka') {$trm=' - UANG MUKA';} else {$trm='';}
    	
    } else {$trm=' - '.strtoupper(($r['st_termin'].' '.$r[30]));}

    if (($r['tgl_mulai_sp']==null) || ($r['tgl_mulai_sp']=='1970-01-01 00:00:00')) {$tg='';} else {
                                    $tg=date('d-m-Y',strtotime($r['tgl_mulai_sp'])).' s/d '.date('d-m-Y',strtotime($r['tgl_selesai_sp'])); }

    if (($r['tanggal_setor']=="1970-01-01 00:00:00") || ($r['tanggal_setor']==NULL) || ($r['tanggal_setor']=="")) {
                                          $trl="-";} else {
                                          $trl=date('d-m-Y',strtotime($r['tanggal_setor'])); 
                                      }

    if (($r['tanggal_faktur']=="1970-01-01 00:00:00+07") || ($r['tanggal_faktur']==NULL) || ($r['tanggal_faktur']=="")) {
                                          $tfk="-";} else {
                                          $tfk=date('d-m-Y',strtotime($r['tanggal_faktur'])); 
                                      }
    $rek=$r['ra'].' '.$r['rb'].' '.$r['rc'].' '.$r['rd'].' '.$r['re'].' '.$r['rf'].' '.$r['rg'].' '.$r['rh'];

                                               if ((isset($r['pph_21'])) && ($r['pph_21']!=0)) {
                                                    $pph21=$r['pph_21'];
                                                    $pasal21='21';
                                                } else {$pph21=0;$pasal21='';}


                                                if ((isset($r['pph_22'])) && ($r['pph_22']!=0)) {
                                                    $pph22=$r['pph_22'];
                                                    $pasal22='22';
                                                } else {$pph22=0;$pasal22='';}

                                                if ((isset($r['pph_23'])) && ($r['pph_23']!=0)) {
                                                    $pph23=$r['pph_23'];
                                                    $pasal23='23';
                                                } else {$pph23=0;$pasal23='';}

                                                if ((isset($r['pph_final'])) && ($r['pph_final']!=0)) {
                                                    $pph_fnl=$r['pph_final'];
                                                    $pasal_fnl='FNL';
                                                } else {$pph_fnl=0;$pasal_fnl='';}

												if ((isset($r['ppn'])) && ($r['ppn']!=0)) {$ppn=$r['ppn'];} else {$ppn=0;} 

									$cek_sppd=substr($r['no_sp'],0,4);
                                    if ($cek_sppd=='SPPD') {
                                    	$drangka=$r['untuk'];$sbrk=$r['tgl_berangkat'];$skbl=$r['tgl_kembali'];
                                    	$ketel=strtoupper($r['kegiatan'].' dalam rangka '.$drangka);
                                    	$tglsppd=':::: Tanggal. '.date('d-m-Y',strtotime($sbrk)).' s/d '.date('d-m-Y',strtotime($skbl)); 
                                        $ketan=':::: a.n. '.$soporek;
                                        $keder=$ketel.' '.$tglsppd.' '.$ketan;
                                    } else {
                                    	$keder=strtoupper($r['kegiatan'].' - '.$r['kegiatan_pengadaan'].' - '.$soporek.''.$trm.''.$ket2);
                                    }
    $objPHPExcel->getSheet(0)
            ->setCellValue('A'.$baris, $a)
            /*->setCellValue('B'.$baris, $r[0])*/
			->setCellValue('B'.$baris, $r[1])
			/*->setCellValue('D'.$baris, $r[2])*/
			->setCellValue('C'.$baris, $rek)
			/*->setCellValue('F'.$baris, $r['kd_sibaku'])*/
    	
			->setCellValue('D'.$baris, $keder)
			->setCellValue('E'.$baris, $r['nilai_pelunasan'])
            ->setCellValue('F'.$baris, $ppn)
            ->setCellValue('G'.$baris, $pph21)
            ->setCellValue('H'.$baris, $pasal21)
    
    		->setCellValue('I'.$baris, $pph22)
            ->setCellValue('J'.$baris, $pasal22)
    		->setCellValue('K'.$baris, $pph23)
            ->setCellValue('L'.$baris, $pasal23)
    		->setCellValue('M'.$baris, $pph_fnl)
            ->setCellValue('N'.$baris, $pasal_fnl)
    
            ->setCellValue('O'.$baris, $r['no_sp'])
            ->setCellValue('P'.$baris, $r['no_faktur'])
            ->setCellValue('Q'.$baris, $tfk)
    		->setCellValue('R'.$baris, $r['ket'])
    		->setCellValue('S'.$baris, $r['nomor_ba'])
			->setCellValue('T'.$baris, $r['nilai_ba']);
            /*->setCellValue('O'.$baris, '')
            ->setCellValue('P'.$baris, '')
            ->setCellValue('Q'.$baris, '')
            ->setCellValue('R'.$baris, '')
            ->setCellValue('S'.$baris, $tg)
            ->setCellValue('T'.$baris, date('h:s',strtotime($r['tanggal_setor'])))
            ->setCellValue('U'.$baris, date('M-Y',strtotime($r['tanggal_setor'])))
            ->setCellValue('V'.$baris, date('d-m-Y',strtotime($r['tanggal_input'])))
            ->setCellValue('W'.$baris, $r['st_buku']);*/
/*
	$objPHPExcel->getSheet(0)->getStyle('A3:A'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('B3:B'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('C3:C'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('H3:H'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('J3:J'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('L3:L'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('N3:N'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('O3:O'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('Q3:Q'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('A1:R'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('E'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('F'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('G'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('I'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('K'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('M'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    */
    $a++;
    $baris++;
}
	$objPHPExcel->getSheet(0)->getStyle('A3:A'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('B3:B'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('C3:C'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('H3:H'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('J3:J'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('L3:L'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('N3:N'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('O3:O'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('Q3:Q'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('S3:S'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('A1:T'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('E3:E'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('F3:F'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('G3:G'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('I3:I'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('K3:K'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('M3:M'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('T3:T'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$akhir=$baris-1;
/*$objPHPExcel->getSheet(0)->setCellValue('I'.$baris, '=SUM(I2:I'.$akhir.')')->setCellValue('J'.$baris, '=SUM(J2:J'.$akhir.')');
$objPHPExcel->getSheet(0)->getStyle('I'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('J'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('I'.$baris.':J'.$baris)->applyFromArray($styleBold);*/
$objPHPExcel->setActiveSheetIndex(0);

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="ambil_data_setor.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

?>