<?php

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

// koneksi ke database
include('../koneksi.php');

$satker=$_GET['satker'];
$bln2=$_GET['bln2'];
$thn2=$_GET['thn2'];
$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);
//$objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(0)->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(25);
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(25);
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(25);
$objPHPExcel->getSheet(0)->getColumnDimension('G')->setWidth(25);
$objPHPExcel->getSheet(0)->getColumnDimension('H')->setWidth(30);

$styleBold = array('font'  => array('bold'  => true));
$objPHPExcel->getSheet(0)->getStyle('A1:H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A1:H1')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getRowDimension('1')->setRowHeight(30);
$objPHPExcel->getSheet(0)->setTitle('UNDANGAN MASUK');
$objPHPExcel->getSheet(0)
            ->setCellValue('A1', 'No')
            ->setCellValue('B1', 'Nama')
			->setCellValue('C1', 'Jabatan')
            ->setCellValue('D1', 'Tgl Acara')
            ->setCellValue('E1', 'Jam')
            ->setCellValue('F1', 'Ruangan')
            ->setCellValue('G1', 'Acara')
            ->setCellValue('H1', 'Terkirim');


						  $query = "SELECT DISTINCT w.nama,
                                        (SELECT j.nama FROM jabatan_pegawai j WHERE j.id=w.jabatan_pegawai_id),
                                        k.tgl_acara,p.waktu_mulai,p.waktu_selesai,
                                        (SELECT r.nama FROM data_ruangan r WHERE r.id=k.tempat),
                                        k.tujuan,p.st_terima,p.id,k.st_batal,k.und_kirim
                                    FROM riwayat_kegiatan k 
                                    INNER JOIN riwayat_peserta p ON (p.riwayat_kegiatan_id=k.id)
                                    INNER JOIN data_pegawai w ON (w.id=p.data_pegawai_id)
                                    WHERE k.st_persiapan='Ready'
                                    AND w.satuan_kerja='$satker'
                                    ".($thn2 == '' ? "" : "AND EXTRACT(YEAR FROM DATE(k.tgl_acara))='$thn2'")."
                                    ".($bln2 == '0' ? "" : "AND EXTRACT(MONTH FROM DATE(k.tgl_acara))='$bln2'")."
                                    ORDER BY k.und_kirim DESC NULLS LAST";

$hasil = pg_query($query);
$baris = 2;
$a=1;
while ($row = pg_fetch_array($hasil))
{
                                    	if ($row['und_kirim']==NULL) {$und_kirim='';} else {
                                		$und_kirim=date('d-m-Y H:i',strtotime($row['und_kirim']));
                                        }
    $objPHPExcel->getSheet(0)
            ->setCellValue('A'.$baris, $a)
            ->setCellValue('B'.$baris, $row[0])
    		->setCellValue('C'.$baris, $row[1])
			->setCellValue('D'.$baris, date('d-m-Y',strtotime($row['tgl_acara'])))
			->setCellValue('E'.$baris, date('H:i',strtotime($row['waktu_mulai'])).' - '.date('H:i',strtotime($row['waktu_selesai'])))
			->setCellValue('F'.$baris, $row[5])
			->setCellValue('G'.$baris, $row['tujuan'])
			->setCellValue('H'.$baris, $und_kirim);

	$objPHPExcel->getSheet(0)->getStyle('A1:A'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //$objPHPExcel->getSheet(0)->getStyle('F1:G'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('A1:J'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $a++;
    $baris++;
}
$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="laporan_undangan.xls"');
header('Cache-Control: max-age=0');

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

?>