<?php

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

// koneksi ke database

include("../koneksi.php");
$thn=$_GET['thn'];
$bulan=$_GET['bulan'];
$a=strtoupper($_GET['a']);
$c=strtoupper($_GET['c']);
$nama=strtolower($_GET['nama']);
$untuk=strtolower($_GET['untuk']);
$cek_jenis_user=$_GET['cek_jenis_user'];
$cek_satuan_kerja=$_GET['cek_satuan_kerja'];


$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(10);
$objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(23);
$objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(17);
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(64);
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(26);
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(16);

$styleBold = array('font'  => array('bold'  => true));
$objPHPExcel->getSheet(0)->getStyle('A3:CB3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A3:CB3')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getRowDimension('1')->setRowHeight(15);
$objPHPExcel->getSheet(0)->setTitle('LAPORAN SPPD');
/*
$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('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->getSheet(0)
            ->setCellValue('A3', 'Tahun')
            ->setCellValue('B3', 'Nama')
            ->setCellValue('C3', 'Tujuan')
            ->setCellValue('D3', 'Dalam Rangka')
            ->setCellValue('E3', 'Tanggal')
            ->setCellValue('F3', 'Status');

$query="SELECT 
                                    k.no_bukti,
                                    COALESCE(p.nama,g.nama_non),
                                    s.nama,
                                    t.daerah_tujuan,
                                    t.tgl_berangkat,
                                    t.tgl_kembali,
                                    t.untuk,
                                    t.jenis_bayar,
                                    k.id,
                                        g.tgl_setor_berkas,
                                        k.ambulance,
                                        (row_number() OVER (ORDER BY k.id,g.id)),
                                        k.pembuat,
                                        k.tanggal,
                                        g.pekerjaan_id,
                                        g.tgl_setor_verif,
                                        g.id,
                                        t.kota_dari,
                                        k.thn
                                        FROM sp_kwi k
                                        LEFT OUTER JOIN sp_spt t ON (t.sp_kwi_id=k.id)
                                        LEFT OUTER 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)
                                        WHERE k.id IS NOT NULL AND k.thn='$thn'
                                        ".($nama == '' ? "" : " AND LOWER(COALESCE(p.nama,g.nama_non)) LIKE '%$nama%'")."
                                        ORDER BY k.id DESC,g.id DESC";
$hasil = pg_query($query);
$baris = 4;
$a=1;
while ($r= pg_fetch_array($hasil)) {
if (($r['kota_dari']==NULL) || ($r['kota_dari']=='MALANG')) {
  $tujuan=strtoupper($r['daerah_tujuan']);
} else {
  $tujuan=strtoupper($r['kota_dari']).' ke '.strtoupper($r['daerah_tujuan']);
}

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

    $objPHPExcel->getSheet(0)
            ->setCellValue('A'.$baris, $r['thn'])
            ->setCellValue('B'.$baris, $r[1])
            ->setCellValue('C'.$baris, $tujuan)
            ->setCellValue('D'.$baris, $r['untuk'])
            ->setCellValue('E'.$baris, $tanggal)
            ->setCellValue('F'.$baris, $r['jenis_bayar']);

    $objPHPExcel->getSheet(0)->getStyle('A3:A'.$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('E3:E'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('F3:F'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

	$objPHPExcel->getSheet(0)->getStyle('A3:A'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('B3:B'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('C3:C'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('D3:D'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('E3:E'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('F3:F'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);


    $a++;
	$baris++;
}

$objPHPExcel->setActiveSheetIndex(0);

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

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

?>