<?php
require_once '../../../assets/excel/PHPExcel.php';
include('../../koneksi.php');

$thn=$_GET['thn'];
$kegiatan=$_GET['kg'];
$sub_kegiatan=$_GET['sub_kegiatan'];

$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(6);
$objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(6);
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(8);
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(6);
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(35);
$objPHPExcel->getSheet(0)->getColumnDimension('G')->setWidth(95);
$objPHPExcel->getSheet(0)->getColumnDimension('H')->setWidth(18);

$styleBold = array('font'  => array('bold'  => true));
$objPHPExcel->getSheet(0)->getStyle('A1:S1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A1:S1')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getRowDimension('1')->setRowHeight(25);
$objPHPExcel->getSheet(0)->setTitle('ANGGARAN');
$objPHPExcel->getSheet(0)
->setCellValue('A1', 'No')
->setCellValue('B1', 'KPA')
->setCellValue('C1', 'PPTK')
->setCellValue('D1', 'PPKOM')
->setCellValue('E1', 'PPBJ')
->setCellValue('F1', 'Rek')
->setCellValue('G1', 'Sub / Belanja')
->setCellValue('H1', 'Anggaran');

$query = "SELECT a.inisial,t.inisial,o.inisial,p.inisial,g.nama,
k.ra,k.rb,k.rc,k.rd,k.re,k.rf,k.rg,k.rh,k.sub_kegiatan,k.anggaran,
k.id,
k.kegiatan,k.kpa,k.pptk
FROM sub_kegiatan k
LEFT OUTER JOIN kpa a ON (a.id=k.kpa_id)
LEFT OUTER JOIN pptk t ON (t.id=k.pptk_id)
LEFT OUTER JOIN pejabat_pengadaan p ON (p.id=k.pejabat_pengadaan_id)
LEFT OUTER JOIN ppkom o ON (o.id=k.ppkom_id)
LEFT OUTER JOIN subkategori_kegiatan g ON (g.id=k.kategori_id)
WHERE k.thn='$thn'
".($sub_kegiatan == '' ? "" : "AND k.sub_kegiatan='$sub_kegiatan'")."
".($kegiatan == '' ? "" : "AND k.kegiatan='$kegiatan'")."
ORDER BY k.id DESC
";

$hasil = pg_query($query);
$baris = 2;
$a=1;
while ($r = pg_fetch_array($hasil))
{
    if(($r[5]=='') || ($r[5]==NULL)) {
    $rek='';
    } else {
    $rek=$r['kpa'].' '.$r['pptk'].' '.$r[5].' '.$r[6].' '.$r[7].' '.$r[8].' '.$r[9].' '.$r[10].' '.$r[11].' '.$r[12];
    }
	
	if(($r[13]=='') || ($r[13]==NULL)) {$sub='';} else {$sub=$r[13];}
    if(($r[16]=='') || ($r[16]==NULL)) {$bl='';} else {$bl=$r[16];}
    $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, $r[3])
    ->setCellValue('F'.$baris, $rek)
    ->setCellValue('G'.$baris, $sub."\n".$bl)
    ->setCellValueExplicit('H'.$baris, $r[14], PHPExcel_Cell_DataType::TYPE_NUMERIC); /*anggaran*/

    $objPHPExcel->getSheet(0)->getStyle('H'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	//$objPHPExcel->getSheet(0)->getStyle('P'.$baris)->getNumberFormat()->setFormatCode('@');
    $objPHPExcel->getSheet(0)->getStyle('A1:E'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('P1:S'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('A1:R'.$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="anggaran.xls"');
header('Cache-Control: max-age=0');

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

?>