<?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->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(18);
$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(15);
$objPHPExcel->getSheet(0)->getColumnDimension('L')->setWidth(15);
$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(17);
$objPHPExcel->getSheet(0)->getColumnDimension('Q')->setWidth(10);
$objPHPExcel->getSheet(0)->getColumnDimension('R')->setWidth(10);
$objPHPExcel->getSheet(0)->getColumnDimension('S')->setWidth(14);
$objPHPExcel->getSheet(0)->getColumnDimension('T')->setWidth(17);
$objPHPExcel->getSheet(0)->getColumnDimension('U')->setWidth(18);

$styleBold = array('font'  => array('bold'  => true));

$objPHPExcel->getSheet(0)->getStyle('A3:CA5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A3:CB3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A4:CB4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A5:CB5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A3:CB3')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getStyle('A4:CB4')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getStyle('A5:CB5')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->setTitle('SPPDWEB Dalam Daerah');
$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:CB1')->getAlignment()->setWrapText(false);

$objPHPExcel->getActiveSheet()->mergeCells('A3:A5');
$objPHPExcel->getSheet(0)->setCellValue('A3', 'No');
$objPHPExcel->getActiveSheet()->mergeCells('B3:B5');
$objPHPExcel->getSheet(0)->setCellValue('B3', 'No. TBK');
$objPHPExcel->getActiveSheet()->mergeCells('C3:C5');
$objPHPExcel->getSheet(0)->setCellValue('C3', 'Tanggal');
$objPHPExcel->getActiveSheet()->mergeCells('D3:D5');
$objPHPExcel->getSheet(0)->setCellValue('D3', 'Nama Lengkap');
$objPHPExcel->getActiveSheet()->mergeCells('E3:E5');
$objPHPExcel->getSheet(0)->setCellValue('E3', 'NIP');
$objPHPExcel->getActiveSheet()->mergeCells('F3:F5');
$objPHPExcel->getSheet(0)->setCellValue('F3', 'Keperluan');
$objPHPExcel->getActiveSheet()->mergeCells('G3:G5');
$objPHPExcel->getSheet(0)->setCellValue('G3', 'Jumlah Dibayarkan');
$objPHPExcel->getActiveSheet()->mergeCells('H3:H5');
$objPHPExcel->getSheet(0)->setCellValue('H3', 'Gol.Peg');
$objPHPExcel->getActiveSheet()->mergeCells('I3:I5');
$objPHPExcel->getSheet(0)->setCellValue('I3', 'Tujuan');
$objPHPExcel->getActiveSheet()->mergeCells('J3:J5');
$objPHPExcel->getSheet(0)->setCellValue('J3', 'Kota');
$objPHPExcel->getActiveSheet()->mergeCells('K3:M3');
$objPHPExcel->getActiveSheet()->mergeCells('K4:L4');
$objPHPExcel->getSheet(0)->setCellValue('K3', 'SPPD');
$objPHPExcel->getSheet(0)->setCellValue('K4', 'Tanggal');
$objPHPExcel->getActiveSheet()->mergeCells('M4:M5');
$objPHPExcel->getSheet(0)->setCellValue('M4', 'Lama Hari');
$objPHPExcel->getActiveSheet()->mergeCells('N3:U3');
$objPHPExcel->getSheet(0)->setCellValue('N3', 'Rincian Biaya');
$objPHPExcel->getActiveSheet()->mergeCells('N4:O4');
$objPHPExcel->getSheet(0)->setCellValue('N4', 'Uang Harian');
$objPHPExcel->getActiveSheet()->mergeCells('P4:P5');
$objPHPExcel->getSheet(0)->setCellValue('P4', 'Bantuan Transport');
$objPHPExcel->getActiveSheet()->mergeCells('Q4:Q5');
$objPHPExcel->getSheet(0)->setCellValue('Q4', 'BBM');
$objPHPExcel->getActiveSheet()->mergeCells('R4:R5');
$objPHPExcel->getSheet(0)->setCellValue('R4', 'TOL');
$objPHPExcel->getActiveSheet()->mergeCells('S4:S5');
$objPHPExcel->getSheet(0)->setCellValue('S4', 'Representasi');
$objPHPExcel->getActiveSheet()->mergeCells('T4:T5');
$objPHPExcel->getSheet(0)->setCellValue('T4', 'Sewa Kendaraan');
$objPHPExcel->getActiveSheet()->mergeCells('U4:U5');
$objPHPExcel->getSheet(0)->setCellValue('U4', 'Jumlah');

$objPHPExcel->getSheet(0)->setCellValue('K5', 'Berangkat');
$objPHPExcel->getSheet(0)->setCellValue('L5', 'Kembali');
$objPHPExcel->getSheet(0)->setCellValue('N5', 'Per Hari');
$objPHPExcel->getSheet(0)->setCellValue('O5', 'Total');


$query="(
SELECT DISTINCT
CONCAT(l.no_kwi_bend,'-',l.kode_perben) as no_tbk,
l.tanggal_lunas, 
COALESCE(p.nama,g.nama_non),
COALESCE(p.nipb,g.nip_non),
t.untuk,l.nilai_pelunasan,
CASE WHEN (p.golongan IS NULL OR p.golongan='-') THEN '-' ELSE CONCAT(p.golongan,'/',p.ruang) END as golongan,
t.instansi_tujuan,t.daerah_tujuan,
t.tgl_berangkat,t.tgl_kembali,spb.lama,
spb.uang_harian as uang_harian,
spb.uang_harian*spb.lama as total_uang_harian,
spb.bantrans*spb.lama_bt as bantrans,
spb.bbm,
spb.tol,
spb.representasi*spb.lama as representasi,
spb.sewa_kendaraan 
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 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)
LEFT OUTER JOIN (SELECT * FROM sp_biaya WHERE jenis='P') spb ON (g.id=spb.sp_peg_id)
WHERE g.tgl_setor_verif::DATE BETWEEN '$tt1-$bb1-$hh1' AND '$tt2-$bb2-$hh2' AND g.status IS NOT NULL AND u.jenis_sppd='DALAM'
ORDER BY l.tanggal_lunas ASC
) UNION
(
SELECT DISTINCT
CONCAT(l.no_kwi_bend,'-',l.kode_perben) as no_tbk,
l.tanggal_lunas, 
COALESCE(p.nama,g.nama_non),
COALESCE(p.nipb,g.nip_non),
t.untuk,l.nilai_pelunasan,
CASE WHEN (p.golongan IS NULL OR p.golongan='-') THEN '-' ELSE CONCAT(p.golongan,'/',p.ruang) END as golongan,
t.instansi_tujuan,t.daerah_tujuan,
t.tgl_berangkat,t.tgl_kembali,spb.lama,
spb.uang_harian as uang_harian,
spb.uang_harian*spb.lama as total_uang_harian,
spb.bantrans*spb.lama_bt as bantrans,
spb.bbm,
spb.tol,
spb.representasi*spb.lama as representasi,
spb.sewa_kendaraan 
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 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)
LEFT OUTER JOIN (SELECT * FROM sp_biaya WHERE jenis='P') spb ON (g.id=spb.sp_peg_id)
WHERE g.tgl_setor_verif::DATE BETWEEN '$tt1-$bb1-$hh1' AND '$tt2-$bb2-$hh2' AND g.status IS NOT NULL AND t.area='DIKLAT' AND u.jenis_sppd='DALAM'
ORDER BY l.tanggal_lunas ASC
)
";
$hasil = pg_query($query);
$baris = 6;
$a=1;

while (($r= pg_fetch_array($hasil)) !== FALSE) {
$objPHPExcel->getSheet(0)
->setCellValue('A'.$baris, $a)
->setCellValue('B'.$baris, $r[0])
->setCellValue('C'.$baris, date('d-m-Y',strtotime($r[1])))
->setCellValue('D'.$baris, $r[2])
->setCellValueExplicit('E'.$baris, $r[3], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValue('F'.$baris, $r[4])
->setCellValue('G'.$baris, $r[5])
->setCellValue('H'.$baris, $r[6])
->setCellValue('I'.$baris, $r[7])
->setCellValue('J'.$baris, $r[8])
->setCellValue('K'.$baris, date('d-m-Y',strtotime($r[9])))
->setCellValue('L'.$baris, date('d-m-Y',strtotime($r[10])))
->setCellValue('M'.$baris, $r[11])
->setCellValue('N'.$baris, $r[12])
->setCellValue('O'.$baris, $r[13])
->setCellValue('P'.$baris, $r[14])
->setCellValue('Q'.$baris, $r[15])
->setCellValue('R'.$baris, $r[16])
->setCellValue('S'.$baris, $r[17])
->setCellValue('T'.$baris, $r[18])
->setCellValue('U'.$baris, '=SUM(O'.$baris.':T'.$baris.')');
$a++;
$baris++;
}

$objPHPExcel->getSheet(0)->getStyle('E6:E'.$baris)->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(0)->getStyle('G6:G'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('N6:U'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->setActiveSheetIndex(0);


/* -------------------------------------------------------------------------------------------------------- */

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('SPPDWEB Luar Daerah');


$objPHPExcel->getSheet(1)->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getSheet(1)->getColumnDimension('B')->setWidth(9.29);
$objPHPExcel->getSheet(1)->getColumnDimension('C')->setWidth(12.57);
$objPHPExcel->getSheet(1)->getColumnDimension('D')->setWidth(30);
$objPHPExcel->getSheet(1)->getColumnDimension('E')->setWidth(26);
$objPHPExcel->getSheet(1)->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getSheet(1)->getColumnDimension('G')->setWidth(18);
$objPHPExcel->getSheet(1)->getColumnDimension('H')->setWidth(16.86);
$objPHPExcel->getSheet(1)->getColumnDimension('I')->setWidth(12.86);
$objPHPExcel->getSheet(1)->getColumnDimension('J')->setWidth(12.86);
$objPHPExcel->getSheet(1)->getColumnDimension('K')->setWidth(15);
$objPHPExcel->getSheet(1)->getColumnDimension('L')->setWidth(15);
$objPHPExcel->getSheet(1)->getColumnDimension('M')->setWidth(12.86);
$objPHPExcel->getSheet(1)->getColumnDimension('N')->setWidth(17);
$objPHPExcel->getSheet(1)->getColumnDimension('O')->setWidth(12);
$objPHPExcel->getSheet(1)->getColumnDimension('P')->setWidth(17);
$objPHPExcel->getSheet(1)->getColumnDimension('Q')->setWidth(10);
$objPHPExcel->getSheet(1)->getColumnDimension('R')->setWidth(10);
$objPHPExcel->getSheet(1)->getColumnDimension('S')->setWidth(14);
$objPHPExcel->getSheet(1)->getColumnDimension('T')->setWidth(17);
$objPHPExcel->getSheet(1)->getColumnDimension('U')->setWidth(18);

$styleBold = array('font'  => array('bold'  => true));
$objPHPExcel->getSheet(1)->getStyle('A3:CA5')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getSheet(1)->getStyle('A3:CB3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(1)->getStyle('A4:CB4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(1)->getStyle('A5:CB5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(1)->getStyle('A3:CB3')->applyFromArray($styleBold);
$objPHPExcel->getSheet(1)->getStyle('A4:CB4')->applyFromArray($styleBold);
$objPHPExcel->getSheet(1)->getStyle('A5:CB5')->applyFromArray($styleBold);

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

$objPHPExcel->getActiveSheet()->mergeCells('A3:A5');
$objPHPExcel->getSheet(1)->setCellValue('A3', 'No');

$objPHPExcel->getActiveSheet()->mergeCells('B3:E3');
$objPHPExcel->getSheet(1)->setCellValue('B3', 'Surat Perintah Tugas');

$objPHPExcel->getActiveSheet()->mergeCells('B4:B5');
$objPHPExcel->getSheet(1)->setCellValue('B4', 'Nama Lengkap');

$objPHPExcel->getActiveSheet()->mergeCells('C4:C5');
$objPHPExcel->getSheet(1)->setCellValue('C4', 'NIP');

$objPHPExcel->getActiveSheet()->mergeCells('D4:D5');
$objPHPExcel->getSheet(1)->setCellValue('D4', 'Dalam Rangka');

$objPHPExcel->getActiveSheet()->mergeCells('E4:E5');
$objPHPExcel->getSheet(1)->setCellValue('E4', 'Bidang/Unit');

$objPHPExcel->getActiveSheet()->mergeCells('F3:K3');
$objPHPExcel->getSheet(1)->setCellValue('F3', 'SPPD');

$objPHPExcel->getActiveSheet()->mergeCells('F4:F5');
$objPHPExcel->getSheet(1)->setCellValue('F4', 'Gol.Peg');

$objPHPExcel->getActiveSheet()->mergeCells('G4:G5');
$objPHPExcel->getSheet(1)->setCellValue('G4', 'Daerah Tujuan');

$objPHPExcel->getActiveSheet()->mergeCells('H4:H5');
$objPHPExcel->getSheet(1)->setCellValue('H4', 'Instansi');

$objPHPExcel->getActiveSheet()->mergeCells('I4:J4');
$objPHPExcel->getSheet(1)->setCellValue('I4', 'Tanggal');

$objPHPExcel->getSheet(1)->setCellValue('I5', 'Berangkat');
$objPHPExcel->getSheet(1)->setCellValue('J5', 'Kembali');

$objPHPExcel->getActiveSheet()->mergeCells('K4:K5');
$objPHPExcel->getSheet(1)->setCellValue('K4', 'Lama Hari');

$objPHPExcel->getActiveSheet()->mergeCells('L3:U3');
$objPHPExcel->getSheet(1)->setCellValue('L3', 'Tanda Bukti/Kwitansi');

$objPHPExcel->getActiveSheet()->mergeCells('L4:L5');
$objPHPExcel->getSheet(1)->setCellValue('L4', 'No. Bukti');

$objPHPExcel->getActiveSheet()->mergeCells('M4:M5');
$objPHPExcel->getSheet(1)->setCellValue('M4', 'Tanggal Bukti');

$objPHPExcel->getActiveSheet()->mergeCells('N4:N5');
$objPHPExcel->getSheet(1)->setCellValue('N4', 'Jumlah Dibayarkan');

$objPHPExcel->getActiveSheet()->mergeCells('O4:U4');
$objPHPExcel->getSheet(1)->setCellValue('O4', 'Rincian Biaya');

$objPHPExcel->getSheet(1)->setCellValue('O5', 'Uang Harian');
$objPHPExcel->getSheet(1)->setCellValue('P5', 'Biaya Transport');
$objPHPExcel->getSheet(1)->setCellValue('Q5', 'BBM');
$objPHPExcel->getSheet(1)->setCellValue('R5', 'TOL');
$objPHPExcel->getSheet(1)->setCellValue('S5', 'Biaya Penginapan');
$objPHPExcel->getSheet(1)->setCellValue('T5', 'Uang Representasi');
$objPHPExcel->getSheet(1)->setCellValue('U5', 'Sewa Kendaraan');

$objPHPExcel->getActiveSheet()->mergeCells('V3:AT3');
$objPHPExcel->getSheet(1)->setCellValue('V3', 'Data Transportasi/Akomodasi');

$objPHPExcel->getActiveSheet()->mergeCells('V4:Z4');
$objPHPExcel->getSheet(1)->setCellValue('V4', 'Penginapan');
$objPHPExcel->getSheet(1)->setCellValue('V5', 'Nama');
$objPHPExcel->getSheet(1)->setCellValue('W5', 'Alamat');
$objPHPExcel->getSheet(1)->setCellValue('X5', 'No Kamar');
$objPHPExcel->getSheet(1)->setCellValue('Y5', 'Check-in');
$objPHPExcel->getSheet(1)->setCellValue('Z5', 'Check-out');

$objPHPExcel->getActiveSheet()->mergeCells('AA4:AJ4');
$objPHPExcel->getSheet(1)->setCellValue('AA4', 'Berangkat');

$objPHPExcel->getSheet(1)->setCellValue('AA5', 'Pswt/KA');
$objPHPExcel->getSheet(1)->setCellValue('AB5', 'Maskapai');
$objPHPExcel->getSheet(1)->setCellValue('AC5', 'Nomor Tiket');
$objPHPExcel->getSheet(1)->setCellValue('AD5', 'Nomor Flight');
$objPHPExcel->getSheet(1)->setCellValue('AE5', 'Jam');
$objPHPExcel->getSheet(1)->setCellValue('AF5', 'No. Tmpt Duduk');
$objPHPExcel->getSheet(1)->setCellValue('AG5', 'Tanggal');
$objPHPExcel->getSheet(1)->setCellValue('AH5', 'Asal');
$objPHPExcel->getSheet(1)->setCellValue('AI5', 'Tujuan');
$objPHPExcel->getSheet(1)->setCellValue('AJ5', 'Harga');

$objPHPExcel->getActiveSheet()->mergeCells('AK4:AT4');
$objPHPExcel->getSheet(1)->setCellValue('AK4', 'Kembali');

$objPHPExcel->getSheet(1)->setCellValue('AK5', 'Pswt/KA');
$objPHPExcel->getSheet(1)->setCellValue('AL5', 'Maskapai');
$objPHPExcel->getSheet(1)->setCellValue('AM5', 'Nomor Tiket');
$objPHPExcel->getSheet(1)->setCellValue('AN5', 'Nomor Flight');
$objPHPExcel->getSheet(1)->setCellValue('AO5', 'Jam');
$objPHPExcel->getSheet(1)->setCellValue('AP5', 'No. Tmpt Duduk');
$objPHPExcel->getSheet(1)->setCellValue('AQ5', 'Tanggal');
$objPHPExcel->getSheet(1)->setCellValue('AR5', 'Asal');
$objPHPExcel->getSheet(1)->setCellValue('AS5', 'Tujuan');
$objPHPExcel->getSheet(1)->setCellValue('AT5', 'Harga');


$query1="(
SELECT DISTINCT 
COALESCE(p.nama,g.nama_non),
COALESCE(p.nipb,g.nip_non),
t.untuk,s.nama,
CASE WHEN (p.golongan IS NULL OR p.golongan='-') THEN '-' ELSE CONCAT(p.golongan,'/',p.ruang) END as golongan,
t.daerah_tujuan,t.instansi_tujuan,t.tgl_berangkat,t.tgl_kembali,spb.lama,
CONCAT(l.no_kwi_bend,'-',l.kode_perben) as no_bukti,
l.tanggal_lunas,l.nilai_pelunasan,
spb.uang_harian*spb.lama as uang_harian,
spb.bantrans*spb.lama_bt as biaya_transport,
spb.bbm,
spb.tol,
COALESCE(SUM(lspp.penginapan*lspp.lama),spp.penginapan*spp.lama) as biaya_penginapan,
spb.representasi*spb.lama as uang_repesentasi,
spb.sewa_kendaraan,
COALESCE(STRING_AGG(lspp.nama_penginapan,','),spp.nama_penginapan) as penginapan,
sptb.pesawat_ka,sptb.no_tiket,sptb.no_flight,sptb.jam,sptb.no_tempat_duduk,sptb.tanggal,sptb.asal,sptb.tujuan,sptb.harga,
sptp.pesawat_ka,sptp.no_tiket,sptp.no_flight,sptp.jam,sptp.no_tempat_duduk,sptp.tanggal,sptp.asal,sptp.tujuan,sptp.harga,
sptb.penyewa as maskapaib,sptp.penyewa as maskapaip,
lspp.alamat_penginapan,lspp.email_penginapan,lspp.nomor_kamar,lspp.tgl_cekin,lspp.tgl_cekout
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 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)
LEFT OUTER JOIN (SELECT * FROM sp_biaya WHERE jenis='P') spb ON (g.id=spb.sp_peg_id)
LEFT OUTER JOIN (SELECT * FROM sp_penginapan) spp ON (g.id=spp.sp_peg_id)
LEFT OUTER JOIN (SELECT * FROM list_sp_penginapan) lspp ON (g.id=lspp.sp_peg_id)
LEFT OUTER JOIN (SELECT * FROM sp_transportasi WHERE jenis='B') sptb ON (g.id=sptb.sp_peg_id)
LEFT OUTER JOIN (SELECT * FROM sp_transportasi WHERE jenis='P') sptp ON (g.id=sptp.sp_peg_id)
WHERE g.tgl_setor_verif::DATE BETWEEN '$tt1-$bb1-$hh1' AND '$tt2-$bb2-$hh2' AND g.status IS NOT NULL AND u.jenis_sppd='LUAR'
GROUP BY
COALESCE(p.nama,g.nama_non),
COALESCE(p.nipb,g.nip_non),
t.untuk,s.nama,p.golongan,p.ruang,t.daerah_tujuan,t.instansi_tujuan,t.tgl_berangkat,t.tgl_kembali,spb.lama,
l.no_kwi_bend,l.kode_perben,l.tanggal_lunas,l.nilai_pelunasan,
spb.uang_harian*spb.lama,
spb.bantrans*spb.lama_bt,
spb.bbm,
spb.tol,
spb.representasi,spb.lama,
spb.sewa_kendaraan,
spp.penginapan,spp.lama,
spp.nama_penginapan,
sptb.pesawat_ka,sptb.no_tiket,sptb.no_flight,sptb.jam,sptb.no_tempat_duduk,sptb.tanggal,sptb.asal,sptb.tujuan,sptb.harga,
sptp.pesawat_ka,sptp.no_tiket,sptp.no_flight,sptp.jam,sptp.no_tempat_duduk,sptp.tanggal,sptp.asal,sptp.tujuan,sptp.harga,
sptb.penyewa,sptp.penyewa,
lspp.alamat_penginapan,lspp.email_penginapan,lspp.nomor_kamar,lspp.tgl_cekin,lspp.tgl_cekout
ORDER BY l.tanggal_lunas ASC
) UNION 
(
SELECT DISTINCT 
COALESCE(p.nama,g.nama_non),
COALESCE(p.nipb,g.nip_non),
t.untuk,s.nama,
CASE WHEN (p.golongan IS NULL OR p.golongan='-') THEN '-' ELSE CONCAT(p.golongan,'/',p.ruang) END as golongan,
t.daerah_tujuan,t.instansi_tujuan,t.tgl_berangkat,t.tgl_kembali,spb.lama,
CONCAT(l.no_kwi_bend,'-',l.kode_perben) as no_bukti,
l.tanggal_lunas,l.nilai_pelunasan,
spb.uang_harian*spb.lama as uang_harian,
spb.bantrans*spb.lama_bt as biaya_transport,
spb.bbm,
spb.tol,
COALESCE(SUM(lspp.penginapan*lspp.lama),spp.penginapan*spp.lama) as biaya_penginapan,
spb.representasi*spb.lama as uang_repesentasi,
spb.sewa_kendaraan,
COALESCE(STRING_AGG(lspp.nama_penginapan,','),spp.nama_penginapan) as penginapan,
sptb.pesawat_ka,sptb.no_tiket,sptb.no_flight,sptb.jam,sptb.no_tempat_duduk,sptb.tanggal,sptb.asal,sptb.tujuan,sptb.harga,
sptp.pesawat_ka,sptp.no_tiket,sptp.no_flight,sptp.jam,sptp.no_tempat_duduk,sptp.tanggal,sptp.asal,sptp.tujuan,sptp.harga,
sptb.penyewa as maskapaib,sptp.penyewa as maskapaip,
lspp.alamat_penginapan,lspp.email_penginapan,lspp.nomor_kamar,lspp.tgl_cekin,lspp.tgl_cekout
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 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)
LEFT OUTER JOIN (SELECT * FROM sp_biaya WHERE jenis='P') spb ON (g.id=spb.sp_peg_id)
LEFT OUTER JOIN (SELECT * FROM sp_penginapan) spp ON (g.id=spp.sp_peg_id)
LEFT OUTER JOIN (SELECT * FROM list_sp_penginapan) lspp ON (g.id=lspp.sp_peg_id)
LEFT OUTER JOIN (SELECT * FROM sp_transportasi WHERE jenis='B') sptb ON (g.id=sptb.sp_peg_id)
LEFT OUTER JOIN (SELECT * FROM sp_transportasi WHERE jenis='P') sptp ON (g.id=sptp.sp_peg_id)
WHERE g.tgl_setor_verif::DATE BETWEEN '$tt1-$bb1-$hh1' AND '$tt2-$bb2-$hh2' AND g.status IS NOT NULL AND t.area='DIKLAT' AND u.jenis_sppd='LUAR'
GROUP BY
COALESCE(p.nama,g.nama_non),
COALESCE(p.nipb,g.nip_non),
t.untuk,s.nama,p.golongan,p.ruang,t.daerah_tujuan,t.instansi_tujuan,t.tgl_berangkat,t.tgl_kembali,spb.lama,
l.no_kwi_bend,l.kode_perben,l.tanggal_lunas,l.nilai_pelunasan,
spb.uang_harian*spb.lama,
spb.bantrans*spb.lama_bt,
spb.bbm,
spb.tol,
spb.representasi,spb.lama,
spb.sewa_kendaraan,
spp.penginapan,spp.lama,
spp.nama_penginapan,
sptb.pesawat_ka,sptb.no_tiket,sptb.no_flight,sptb.jam,sptb.no_tempat_duduk,sptb.tanggal,sptb.asal,sptb.tujuan,sptb.harga,
sptp.pesawat_ka,sptp.no_tiket,sptp.no_flight,sptp.jam,sptp.no_tempat_duduk,sptp.tanggal,sptp.asal,sptp.tujuan,sptp.harga,
sptb.penyewa,sptp.penyewa,
lspp.alamat_penginapan,lspp.email_penginapan,lspp.nomor_kamar,lspp.tgl_cekin,lspp.tgl_cekout
ORDER BY l.tanggal_lunas ASC
)";
$hasil1 = pg_query($query1);
$baris1 = 6;
$a1=1;

while (($r1= pg_fetch_array($hasil1)) !== FALSE) {

if (($r1[26]==null) || ($r1[26]=='1970-01-01 00:00:00')) {$tg1='';} else {$tg1=date('d-m-Y',strtotime($r1[26]));}
if (($r1[35]==null) || ($r1[35]=='1970-01-01 00:00:00')) {$tg2='';} else {$tg2=date('d-m-Y',strtotime($r1[35]));}

if (($r1['tgl_cekin']==null) || ($r1['tgl_cekin']=='1970-01-01 00:00:00')) {$ti='';} else {$ti=date('d-m-Y',strtotime($r1['tgl_cekin']));}
if (($r1['tgl_cekout']==null) || ($r1['tgl_cekout']=='1970-01-01 00:00:00')) {$to='';} else {$to=date('d-m-Y',strtotime($r1['tgl_cekout']));}

$objPHPExcel->getSheet(1)
->setCellValue('A'.$baris1, $a1)
->setCellValue('B'.$baris1, $r1[0])
->setCellValueExplicit('C'.$baris1, $r1[1], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValue('D'.$baris1, $r1[2])
->setCellValue('E'.$baris1, $r1[3])
->setCellValue('F'.$baris1, $r1[4])
->setCellValue('G'.$baris1, $r1[5])
->setCellValue('H'.$baris1, $r1[6])
->setCellValue('I'.$baris1, date('d-m-Y',strtotime($r1[7])))
->setCellValue('J'.$baris1, date('d-m-Y',strtotime($r1[8])))
->setCellValue('K'.$baris1, $r1[9])
->setCellValue('L'.$baris1, $r1[10])
->setCellValue('M'.$baris1, date('d-m-Y',strtotime($r1[11])))
->setCellValue('N'.$baris1, $r1[12])
->setCellValue('O'.$baris1, $r1[13])
->setCellValue('P'.$baris1, $r1[14])
->setCellValue('Q'.$baris1, $r1[15])
->setCellValue('R'.$baris1, $r1[16])
->setCellValue('S'.$baris1, $r1[17])
->setCellValue('T'.$baris1, $r1[18])
->setCellValue('U'.$baris1, $r1[19])

->setCellValue('V'.$baris1, $r1[20])
->setCellValue('W'.$baris1, $r1['alamat_penginapan'])
->setCellValue('X'.$baris1, $r1['nomor_kamar'])
->setCellValue('Y'.$baris1, $ti)
->setCellValue('Z'.$baris1, $to)
->setCellValue('AA'.$baris1, $r1[21])
->setCellValue('AB'.$baris1, $r1['maskapaib'])
->setCellValueExplicit('AC'.$baris1, $r1[22], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValue('AD'.$baris1, $r1[23])
->setCellValue('AE'.$baris1, $r1[24])
->setCellValue('AF'.$baris1, $r1[25])
->setCellValue('AG'.$baris1, $tg1)
->setCellValue('AH'.$baris1, $r1[27])
->setCellValue('AI'.$baris1, $r1[28])
->setCellValue('AJ'.$baris1, $r1[29])

->setCellValue('AK'.$baris1, $r1[30])
->setCellValue('AL'.$baris1, $r1['maskapaip'])
->setCellValueExplicit('AM'.$baris1, $r1[31], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValue('AN'.$baris1, $r1[32])
->setCellValue('AO'.$baris1, $r1[33])
->setCellValue('AP'.$baris1, $r1[34])
->setCellValue('AQ'.$baris1, $tg2)
->setCellValue('AR'.$baris1, $r1[36])
->setCellValue('AS'.$baris1, $r1[37])
->setCellValue('AT'.$baris1, $r1[38]);
$a1++;
$baris1++;
}

$objPHPExcel->getSheet(1)->getStyle('C6:C'.$baris1)->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(1)->getStyle('N6:U'.$baris1)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(1)->getStyle('AA6:AF'.$baris1)->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(1)->getStyle('AJ6:AJ'.$baris1)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(1)->getStyle('AK6:AP'.$baris1)->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(1)->getStyle('AT6:AT'.$baris1)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(1)->getStyle('AC6:AC'.$baris1)->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(1)->getStyle('AM6:AM'.$baris1)->getNumberFormat()->setFormatCode('@');


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

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

?>