<?php

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

// koneksi ke database

include("../koneksi.php");
$thn=$_GET['thn'];
$azx=$_GET['sp_peg_id'];
$b=strtoupper($_GET['b']);
$c=strtoupper($_GET['c']);
$codot=strtoupper($_GET['codot']);
$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('C')->setWidth(12.57);
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(26);
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getSheet(0)->getColumnDimension('G')->setWidth(40);
$objPHPExcel->getSheet(0)->getColumnDimension('H')->setWidth(16.86);
$objPHPExcel->getSheet(0)->getColumnDimension('I')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('J')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('K')->setWidth(7);
$objPHPExcel->getSheet(0)->getColumnDimension('L')->setWidth(20.71);
$objPHPExcel->getSheet(0)->getColumnDimension('M')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('N')->setWidth(17);
$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('S')->setWidth(26);
$objPHPExcel->getSheet(0)->getColumnDimension('T')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('U')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('V')->setWidth(11.14);
$objPHPExcel->getSheet(0)->getColumnDimension('W')->setWidth(9);

$objPHPExcel->getSheet(0)->getColumnDimension('X')->setWidth(20.71);
$objPHPExcel->getSheet(0)->getColumnDimension('Y')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('Z')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AA')->setWidth(7);
$objPHPExcel->getSheet(0)->getColumnDimension('AB')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AC')->setWidth(26);
$objPHPExcel->getSheet(0)->getColumnDimension('AD')->setWidth(26);

$objPHPExcel->getSheet(0)->getColumnDimension('AE')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AF')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AG')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AH')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AI')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AJ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AK')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AL')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AM')->setWidth(13);

$objPHPExcel->getSheet(0)->getColumnDimension('AN')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AO')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AP')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AQ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AR')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('AS')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AT')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AU')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AV')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AW')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AX')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('AY')->setWidth(20);

$objPHPExcel->getSheet(0)->getColumnDimension('AZ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BA')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BB')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BC')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BD')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BE')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BF')->setWidth(13);

$objPHPExcel->getSheet(0)->getColumnDimension('BG')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BH')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BI')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BJ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BK')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('BL')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BM')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BN')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BO')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BP')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BQ')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('BR')->setWidth(20);

$objPHPExcel->getSheet(0)->getColumnDimension('BS')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BT')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BU')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BV')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BW')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('BX')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('BY')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('BZ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('CA')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('CB')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('CC')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('CD')->setWidth(30);

$styleBold = array('font'  => array('bold'  => true));
$objPHPExcel->getSheet(0)->getStyle('A3:CD3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A4:CD4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A5:CD5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A3:CD3')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getStyle('A4:CD4')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getStyle('A5:CD5')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getRowDimension('1')->setRowHeight(15);
$objPHPExcel->getSheet(0)->setTitle('LAP 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:CD1')->getAlignment()->setWrapText(false);
$objPHPExcel->getActiveSheet()->mergeCells('AE3:AY3');
$objPHPExcel->getActiveSheet()->mergeCells('AE4:AM4');
$objPHPExcel->getActiveSheet()->mergeCells('AN4:AY4');
$objPHPExcel->getSheet(0)->setCellValue('AE3', 'BERANGKAT');
$objPHPExcel->getSheet(0)->setCellValue('AE4', 'BIAYA');
$objPHPExcel->getSheet(0)->setCellValue('AN4', 'TRANSPORT');

$objPHPExcel->getActiveSheet()->mergeCells('AZ3:BV3');
$objPHPExcel->getActiveSheet()->mergeCells('AZ4:BF4');
$objPHPExcel->getActiveSheet()->mergeCells('BG4:BR4');
$objPHPExcel->getActiveSheet()->mergeCells('BS4:BV4');
$objPHPExcel->getSheet(0)->setCellValue('AZ3', 'PULANG');
$objPHPExcel->getSheet(0)->setCellValue('AZ4', 'BIAYA');
$objPHPExcel->getSheet(0)->setCellValue('BG4', 'TRANSPORT');
$objPHPExcel->getSheet(0)->setCellValue('BS4', 'PENGINAPAN');

$objPHPExcel->getActiveSheet()->mergeCells('A3:A5');
$objPHPExcel->getActiveSheet()->mergeCells('B3:B5');
$objPHPExcel->getActiveSheet()->mergeCells('C3:C5');
$objPHPExcel->getActiveSheet()->mergeCells('D3:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E3:E5');
$objPHPExcel->getActiveSheet()->mergeCells('F3:F5');
$objPHPExcel->getActiveSheet()->mergeCells('G3:G5');
$objPHPExcel->getActiveSheet()->mergeCells('H3:H5');
$objPHPExcel->getActiveSheet()->mergeCells('I3:I5');
$objPHPExcel->getActiveSheet()->mergeCells('J3:J5');
$objPHPExcel->getActiveSheet()->mergeCells('K3:K5');
$objPHPExcel->getActiveSheet()->mergeCells('L3:L5');
$objPHPExcel->getActiveSheet()->mergeCells('M3:M5');
$objPHPExcel->getActiveSheet()->mergeCells('N3:N5');
$objPHPExcel->getActiveSheet()->mergeCells('O3:O5');
$objPHPExcel->getActiveSheet()->mergeCells('P3:P5');
$objPHPExcel->getActiveSheet()->mergeCells('Q3:Q5');
$objPHPExcel->getActiveSheet()->mergeCells('R3:R5');
$objPHPExcel->getActiveSheet()->mergeCells('S3:S5');
$objPHPExcel->getActiveSheet()->mergeCells('T3:T5');
$objPHPExcel->getActiveSheet()->mergeCells('U3:U5');
$objPHPExcel->getActiveSheet()->mergeCells('V3:V5');
$objPHPExcel->getActiveSheet()->mergeCells('W3:W5');
$objPHPExcel->getActiveSheet()->mergeCells('X3:X5');
$objPHPExcel->getActiveSheet()->mergeCells('Y3:Y5');
$objPHPExcel->getActiveSheet()->mergeCells('Z3:Z5');
$objPHPExcel->getActiveSheet()->mergeCells('AA3:AA5');
$objPHPExcel->getActiveSheet()->mergeCells('AB3:AB5');
$objPHPExcel->getActiveSheet()->mergeCells('AC3:AC5');
$objPHPExcel->getActiveSheet()->mergeCells('AD3:AD5');
//$objPHPExcel->getSheet(0)->getStyle('AA')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
//$objPHPExcel->getActiveSheet()->setCellValueExplicit('AA14', ' ',  PHPExcel_Cell_DataType::TYPE_STRING);

$objPHPExcel->getSheet(0)
            ->setCellValue('A3', 'No')
            ->setCellValue('B3', 'No DPA')
            ->setCellValue('C3', 'Kd Panggil')
            ->setCellValue('D3', 'Sub')
            ->setCellValue('E3', 'Rekening')
            ->setCellValue('F3', 'Kd Sibaku')
            ->setCellValue('G3', 'Uraian')
            ->setCellValue('H3', 'Nilai Kwitansi')
            ->setCellValue('I3', 'PPN')
            ->setCellValue('J3', 'PPH')
            ->setCellValue('K3', 'Pasal')
            ->setCellValue('L3', 'No Kwi/SP/SPK')
            ->setCellValue('M3', 'No Faktur')
            ->setCellValue('N3', 'BAST(P)')
            ->setCellValue('O3', 'Tgl')
            ->setCellValue('P3', 'Nominal')
            ->setCellValue('Q3', 'BA HP Admin')
            ->setCellValue('R3', 'Tgl')
            ->setCellValue('S3', 'Periode Tgl SP/SPK')
            ->setCellValue('T3', 'Jam Setor')
            ->setCellValue('U3', 'Bulan Setor')
            ->setCellValue('V3', 'Tgl Dok')
            ->setCellValue('W3', 'St Buku')

            ->setCellValue('X3', 'No SPT')
            ->setCellValue('Y3', 'Berangkat')
			->setCellValue('Z3', 'Kembali')
			->setCellValue('AA3', 'Lama')
			->setCellValue('AB3', 'Setor Berkas')
            ->setCellValue('AC3', 'Tujuan')
			->setCellValue('AD3', 'NIP/NIPTT')

			->setCellValue('AE5', 'Uang Harian')
			->setCellValue('AF5', 'Representasi')
			->setCellValue('AG5', 'Penginapan')
			->setCellValue('AH5', 'Sewa Kendaraan')
			->setCellValue('AI5', 'BBM')
			->setCellValue('AJ5', 'Tol')
			->setCellValue('AK5', 'Bantrans')
			->setCellValue('AL5', 'Transportasi')
			->setCellValue('AM5', 'PCR')

			->setCellValue('AN5', 'Moda')
			->setCellValue('AO5', 'Penyewa')
			->setCellValue('AP5', 'Nopol')
			->setCellValue('AQ5', 'Pesawat/KA')
			->setCellValue('AR5', 'No Tiket')
			->setCellValue('AS5', 'No Flight')
			->setCellValue('AT5', 'Jam')
			->setCellValue('AU5', 'Kode Booking')
			->setCellValue('AV5', 'No Kursi')
			->setCellValue('AW5', 'Tanggal')
			->setCellValue('AX5', 'Asal')
			->setCellValue('AY5', 'Tujuan')

			->setCellValue('AZ5', 'Uang Harian')
			->setCellValue('BA5', 'Representasi')
			->setCellValue('BB5', 'Sewa Kendaraan')
			->setCellValue('BC5', 'BBM')
			->setCellValue('BD5', 'Tol')
			->setCellValue('BE5', 'Bantrans')
			->setCellValue('BF5', 'PCR')

			->setCellValue('BG5', 'Moda')
			->setCellValue('BH5', 'Penyewa')
			->setCellValue('BI5', 'Nopol')
			->setCellValue('BJ5', 'Pesawat/KA')
			->setCellValue('BK5', 'No Tiket')
			->setCellValue('BL5', 'No Flight')
			->setCellValue('BM5', 'Jam')
			->setCellValue('BN5', 'Kode Booking')
			->setCellValue('BO5', 'No Kursi')
			->setCellValue('BP5', 'Tanggal')
			->setCellValue('BQ5', 'Asal')
			->setCellValue('BR5', 'Tujuan')

			->setCellValue('BS5', 'Nama Penginapan')
			->setCellValue('BT5', 'Tarif/Hari')
			->setCellValue('BU5', 'Lama')
			->setCellValue('BV5', 'Total')
			->setCellValue('BW5', 'Golongan')
			->setCellValue('BX5', 'Satker')
			->setCellValue('BY5', 'Penginput')
			->setCellValue('BZ5', 'Tgl Lunas')
			->setCellValue('CA5', 'Nama')

			->setCellValue('CB5', 'Nama NCR')
			->setCellValue('CC5', 'NIP NCR')
			->setCellValue('CD5', 'Jabatan NCR');

//$pecah=explode(',',$azx);
//foreach($pecah as $selected) {
$query="SELECT DISTINCT k2.no_angg,k2.kd_panggil,k2.sub_kegiatan,k2.ra,k2.rb,k2.rc,k2.rd,k2.re,k2.rf,k2.rg,k2.rh,k2.kd_sibaku,k2.kegiatan,
                                    p2.kegiatan_pengadaan,l.tanggal_setor,
                                    l.nilai_pelunasan,l.ppn,l.pph_21,l.pph_22,l.pph_23,l.pph_final,
                                    p2.no_sp,l.no_faktur,p2.tgl_mulai_sp,p2.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(p.nama,g.nama_non),
									l.tanggal_dokumen,r.st_buku,
                                    t.no_surat,t.tgl_berangkat,t.tgl_kembali,t.untuk,t.daerah_tujuan,x.nama,p.nipb,t.lama,g.id,p.golongan,p.ruang,k.pembuat,s.nama,l.tanggal_lunas,
                                    d.nama as nama_ncr,d.jabatan as jab_ncr,d.nip as nip_ncr
                                        FROM sp_kwi k
                                        INNER JOIN sp_rek u ON (k.sp_rek_id=u.id)
                                        INNER JOIN sub_kegiatan k2 ON (k2.id=u.sub_kegiatan_id)
                                        INNER JOIN sp_spt t ON (t.sp_kwi_id=k.id)
                                        INNER JOIN sp_peg g ON (g.sp_spt_id=t.id)
                                        LEFT OUTER JOIN sp_ttd d ON (g.id=d.sp_peg_id)
                                        LEFT OUTER JOIN data_pegawai p ON (g.data_pegawai_id=p.id)
                                        LEFT OUTER JOIN satuan_kerja s ON (p.satuan_kerja=s.id)
										INNER JOIN pekerjaan p2 ON (p2.id=g.pekerjaan_id)
										INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p2.id)
                                        INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
										LEFT OUTER JOIN rekanan n ON (n.id=p2.rekanan_id)
                                        LEFT OUTER JOIN jabatan_pegawai x ON (x.id=p.jabatan_pegawai_id)
                                        WHERE g.id IN ($azx)
                                        ORDER BY no_sp ASC
                                        ";
$hasil = pg_query($query);
$baris = 6;
$a=1;

while (($r= pg_fetch_array($hasil)) !== FALSE) {
$peg_satker=$r[46];
$pembuat=$r[45];

$sp_peg_id=$r[42];
$gol=$r['golongan'].'/'.$r['ruang'];
if ($gol=='-/-') {$golpeg='PTT-PK';} else {$golpeg=$gol;}
/*B-BIAYA*/
$bb = pg_query("SELECT * FROM sp_biaya WHERE sp_peg_id='$sp_peg_id' AND jenis='B'");
while (($rowbb= pg_fetch_array($bb)) !== FALSE) {
$uh_bb=$rowbb['uang_harian']*$rowbb['lama'];
$rep_bb=$rowbb['representasi']*$rowbb['lama'];
$penginapan_bb=$rowbb['penginapan_br']*$rowbb['lama_inap_br'];
$sewa_kendaraan_bb=$rowbb['sewa_kendaraan'];
$bbm_bb=$rowbb['bbm'];
$tol_bb=$rowbb['tol'];
$bantrans_bb=$rowbb['bantrans']*$rowbb['lama_bt'];
$trans_bb=$rowbb['trans_br'];
$pcr_bb=$rowbb['pcr'];
}

/*B-TRANSPORT*/
$bt = pg_query("SELECT * FROM sp_transportasi WHERE sp_peg_id='$sp_peg_id' AND jenis='B'");
while (($rowbt= pg_fetch_array($bt)) !== FALSE) {
$moda_bt=$rowbt['moda'];
$penyewa_bt=$rowbt['penyewa'];
$no_polisi_bt=$rowbt['no_polisi'];
$pesawat_ka_bt=$rowbt['pesawat_ka'];
$no_tiket_bt=$rowbt['no_tiket'];
$no_flight_bt=$rowbt['no_flight'];
$jam_bt=$rowbt['jam'];
$kode_booking_bt=$rowbt['kode_booking'];
$no_tempat_duduk_bt=$rowbt['no_tempat_duduk'];
if (($rowbt['tanggal']==null) || ($rowbt['tanggal']=='1970-01-01 00:00:00')) {$tanggal_bt='';} else {$tanggal_bt=date('d-m-Y',strtotime($rowbt['tanggal'])); }
$asal_bt=$rowbt['asal'];
$tujuan_bt=$rowbt['tujuan'];
}

/*P-BIAYA*/
$pb = pg_query("SELECT * FROM sp_biaya WHERE sp_peg_id='$sp_peg_id' AND jenis='P'");
while (($rowpb= pg_fetch_array($pb)) !== FALSE) {
$uh_pb=$rowpb['uang_harian']*$rowpb['lama'];
$rep_pb=$rowpb['representasi']*$rowpb['lama'];
$sewa_kendaraan_pb=$rowpb['sewa_kendaraan'];
$bbm_pb=$rowpb['bbm'];
$tol_pb=$rowpb['tol'];
$bantrans_pb=$rowpb['bantrans']*$rowpb['lama_bt'];
$pcr_pb=$rowpb['pcr'];
}

/*P-TRANSPORT*/
$pt = pg_query("SELECT * FROM sp_transportasi WHERE sp_peg_id='$sp_peg_id' AND jenis='P'");
while (($rowpt= pg_fetch_array($pt)) !== FALSE) {
$moda_pt=$rowpt['moda'];
$penyewa_pt=$rowpt['penyewa'];
$no_polisi_pt=$rowpt['no_polisi'];
$pesawat_ka_pt=$rowpt['pesawat_ka'];
$no_tiket_pt=$rowpt['no_tiket'];
$no_flight_pt=$rowpt['no_flight'];
$jam_pt=$rowpt['jam'];
$kode_booking_pt=$rowpt['kode_booking'];
$no_tempat_duduk_pt=$rowpt['no_tempat_duduk'];
if (($rowpt['tanggal']==null) || ($rowpt['tanggal']=='1970-01-01 00:00:00')) {$tanggal_pt='';} else {$tanggal_pt=date('d-m-Y',strtotime($rowpt['tanggal'])); }
$asal_pt=$rowpt['asal'];
$tujuan_pt=$rowpt['tujuan'];
}

/*PENGINAPAN*/
$pn = pg_query("SELECT id,sp_peg_id,nama_penginapan,penginapan,lama FROM sp_penginapan WHERE sp_peg_id='$sp_peg_id'");
while (($rowpn= pg_fetch_array($pn)) !== FALSE) {
$nama_pn=$rowpn['nama_penginapan'];
$tarif_pn=$rowpn['penginapan'];
$lama_pn=$rowpn['lama'];
}


if (($r['tgl_berangkat']==null) || ($r['tgl_berangkat']=='1970-01-01 00:00:00')) {
                                          $tbrk="-";} else {
                                          $tbrk=date('d-m-Y',strtotime($r['tgl_berangkat'])); 
}
if (($r['tgl_kembali']==null) || ($r['tgl_kembali']=='1970-01-01 00:00:00')) {
                                          $tkbl="-";} else {
                                          $tkbl=date('d-m-Y',strtotime($r['tgl_kembali'])); 
}
    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']=="01-01-1970") || ($r['tanggal_setor']==NULL) || ($r['tanggal_setor']=="")) {
                                          $trl="-";} else {
                                          $trl=date('d-m-Y',strtotime($r['tanggal_setor'])); 
                                      }
    if (($r['tanggal_lunas']=="01-01-1970") || ($r['tanggal_lunas']==NULL) || ($r['tanggal_lunas']=="")) {
                                          $tlns="-";} else {
                                          $tlns=date('d-m-Y',strtotime($r['tanggal_lunas'])); 
                                      }
    $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)) {
                                                    $pph=$r['pph_21'];
                                                    $pasal='21';
                                                } else
                                                if ((isset($r['pph_22'])) && ($r['pph_22']!=0)) {
                                                    $pph=$r['pph_22'];
                                                    $pasal='22';
                                                } else
                                                if ((isset($r['pph_23'])) && ($r['pph_23']!=0)) {
                                                    $pph=$r['pph_23'];
                                                    $pasal='23';
                                                } else
                                                if ((isset($r['pph_final'])) && ($r['pph_final']!=0)) {
                                                    $pph=$r['pph_final'];
                                                    $pasal='FNL';
                                                } 

    $objPHPExcel->getSheet(0)
            ->setCellValue('A'.$baris, $a)
            ->setCellValue('B'.$baris, $r[0])
            ->setCellValue('C'.$baris, $r[1])
            ->setCellValue('D'.$baris, $r[2])
            ->setCellValue('E'.$baris, $rek)
            ->setCellValue('F'.$baris, $r['kd_sibaku'])
            ->setCellValue('G'.$baris, strtoupper($r['kegiatan']).' - Dalam rangka '.$r['untuk'].' - a.n. '.$r[31].' - '.$r['nama'])
            ->setCellValue('H'.$baris, $r['nilai_pelunasan'])
            ->setCellValue('I'.$baris, $r['ppn'])
            ->setCellValue('J'.$baris, $pph)
            ->setCellValue('K'.$baris, $pasal)
            ->setCellValue('L'.$baris, $r['no_sp'])
            ->setCellValue('M'.$baris, $r['no_faktur']);

        /* BA Serah Terima */

        $pekerjaan_id=$r['pekerjaan_id'];
        $query_bast = "SELECT nomor_ba,tanggal_ba,nilai_ba FROM riwayat_ba WHERE pekerjaan_id='$pekerjaan_id' AND berita_acara_id=2 ORDER BY tanggal_ba ASC";
        $hasil_bast = pg_query($query_bast);
        $rows = pg_num_rows($hasil_bast);
        if ($rows==0) {} else {
        $baris_bast = $baris;
        $z = 0;
        while (($r_bast= pg_fetch_array($hasil_bast)) !== FALSE) {
            $set=$baris_bast+$z;
            $objPHPExcel->getSheet(0)
            ->setCellValue('N'.$set, $r_bast['nomor_ba'])
            ->setCellValue('O'.$set, date('d-m-Y',strtotime($r_bast['tanggal_ba'])))
            ->setCellValue('P'.$set, $r_bast['nilai_ba']);
            $objPHPExcel->getSheet(0)->getStyle('P'.$set)->getNumberFormat()->setFormatCode('#,##0');
            $z++;
            }

            $awal=$baris;
            $akhir=$set;
        	/*
            $barisz=$set+1;
            $objPHPExcel->getSheet(0)->setCellValue('P'.$barisz, '=SUM(P'.$awal.':P'.$akhir.')');
            $objPHPExcel->getSheet(0)->getStyle('P'.$barisz)->getNumberFormat()->setFormatCode('#,##0');
            $objPHPExcel->getSheet(0)->getStyle('P'.$barisz)->applyFromArray($styleBold);
            */
        }
        /* ====== end of BA Serah Terima ====== */

        /* BA HP Admin */

        $pekerjaan_id=$r['pekerjaan_id'];
        $query_bahp = "SELECT nomor_ba,tanggal_ba,nilai_ba FROM riwayat_ba WHERE pekerjaan_id='$pekerjaan_id' AND berita_acara_id=1 ORDER BY tanggal_ba ASC";
        $hasil_bahp = pg_query($query_bahp);
        $baris_bahp = $baris;
        $zhp = 0;
        while (($r_bahp= pg_fetch_array($hasil_bahp)) !== FALSE) {
            $sethp=$baris_bahp+$zhp;
            $objPHPExcel->getSheet(0)
            ->setCellValue('Q'.$sethp, $r_bahp['nomor_ba'])
            ->setCellValue('R'.$sethp, date('d-m-Y',strtotime($r_bahp['tanggal_ba'])));
            $zhp++;
            }
            
        /* ====== end of BA HP Admin ====== */

    $objPHPExcel->getSheet(0)
            ->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'])

            ->setCellValue('X'.$baris, $r['no_surat'])
            ->setCellValue('Y'.$baris, $tbrk)
    		->setCellValue('Z'.$baris, $tkbl)
    		->setCellValue('AA'.$baris, $r['lama'])
    		->setCellValue('AB'.$baris, date('d-m-Y',strtotime($r['tanggal_setor'])))
            ->setCellValue('AC'.$baris, $r['daerah_tujuan'])
			->setCellValueExplicit('AD'.$baris, $r['nipb'], PHPExcel_Cell_DataType::TYPE_STRING)

			->setCellValue('AE'.$baris, $uh_bb)
			->setCellValue('AF'.$baris, $rep_bb)
    		->setCellValue('AG'.$baris, $penginapan_bb)
			->setCellValue('AH'.$baris, $sewa_kendaraan_bb)
			->setCellValue('AI'.$baris, $bbm_bb)
			->setCellValue('AJ'.$baris, $tol_bb)
    		->setCellValue('AK'.$baris, $bantrans_bb)
			->setCellValue('AL'.$baris, $trans_bb)
			->setCellValue('AM'.$baris, $pcr_bb)

			->setCellValue('AN'.$baris, $moda_bt)
			->setCellValue('AO'.$baris, $penyewa_bt)
			->setCellValue('AP'.$baris, $no_polisi_bt)
			->setCellValue('AQ'.$baris, $pesawat_ka_bt)
			->setCellValueExplicit('AR'.$baris, $no_tiket_bt, PHPExcel_Cell_DataType::TYPE_STRING)
			->setCellValue('AS'.$baris, $no_flight_bt)
			->setCellValue('AT'.$baris, $jam_bt)
			->setCellValue('AU'.$baris, $kode_booking_bt)
			->setCellValue('AV'.$baris, $no_tempat_duduk_bt)
			->setCellValue('AW'.$baris, $tanggal_bt)
			->setCellValue('AX'.$baris, $asal_bt)
			->setCellValue('AY'.$baris, $tujuan_bt)

			->setCellValue('AZ'.$baris, $uh_pb)
			->setCellValue('BA'.$baris, $rep_pb)
			->setCellValue('BB'.$baris, $sewa_kendaraan_pb)
			->setCellValue('BC'.$baris, $bbm_pb)
			->setCellValue('BD'.$baris, $tol_pb)
			->setCellValue('BE'.$baris, $bantrans_pb)
			->setCellValue('BF'.$baris, $pcr_pb)

			->setCellValue('BG'.$baris, $moda_pt)
			->setCellValue('BH'.$baris, $penyewa_pt)
			->setCellValue('BI'.$baris, $no_polisi_pt)
			->setCellValue('BJ'.$baris, $pesawat_ka_pt)
			->setCellValueExplicit('BK'.$baris, $no_tiket_pt, PHPExcel_Cell_DataType::TYPE_STRING)
			->setCellValue('BL'.$baris, $no_flight_pt)
			->setCellValue('BM'.$baris, $jam_pt)
			->setCellValue('BN'.$baris, $kode_booking_pt)
			->setCellValue('BO'.$baris, $no_tempat_duduk_pt)
			->setCellValue('BP'.$baris, $tanggal_pt)
			->setCellValue('BQ'.$baris, $asal_pt)
			->setCellValue('BR'.$baris, $tujuan_pt)

			->setCellValue('BS'.$baris, $nama_pn)
			->setCellValue('BT'.$baris, $tarif_pn)
			->setCellValue('BU'.$baris, $lama_pn)
			->setCellValue('BV'.$baris, $tarif_pn*$lama_pn)
    		->setCellValue('BW'.$baris, $golpeg)
			->setCellValue('BX'.$baris, $peg_satker)
			->setCellValue('BY'.$baris, $pembuat)
    		->setCellValue('BZ'.$baris, $tlns)
    		->setCellValue('CA'.$baris, $r[31])

			->setCellValue('CB'.$baris, $r['nama_ncr'])
			->setCellValue('CC'.$baris, $r['nip_ncr'])
			->setCellValue('CD'.$baris, $r['jab_ncr']);

	/*
    $objPHPExcel->getSheet(0)->getStyle('A3:A'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('A1:CA'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('H'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('I'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('J'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('AE6:AM'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('AZ6:BF'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('BT6:BV'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('AD'.$baris)->getNumberFormat()->setFormatCode('@');
	$objPHPExcel->getSheet(0)->getStyle('Y'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('Z'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('AA'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('AB'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	$objPHPExcel->getSheet(0)->getStyle('AN6:AY'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('BG6:BR'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('BU'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('BY'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('BZ'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); */
    $a++;
    if ($z > $zhp) {$ape=$z;} else {$ape=$zhp;}
    //$baris=$baris+$ape+1;
	$baris=$baris+1;
}

$objPHPExcel->getSheet(0)->getStyle('A3:A'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('A1:CD'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('H6:H'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('I6:I'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('J6:J'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('AE6:AM'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('AZ6:BF'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('BT6:BV'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('AD6:AD'.$baris)->getNumberFormat()->setFormatCode('@');
	$objPHPExcel->getSheet(0)->getStyle('Y6:Y'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('Z6:Z'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('AA6:AA'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('AB6:AB'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	$objPHPExcel->getSheet(0)->getStyle('AN6:AY'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('BG6:BR'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('BU6:BU'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('BY6:BY'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('BZ6:BZ'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$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->getActiveSheet()->getColumnDimension('I')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setVisible(false);
$objPHPExcel->setActiveSheetIndex(0);

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

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

?>