<?php

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

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


$kegiatan=$_GET['kg'];
$kpa=$_GET['kpa'];
$pptk=$_GET['pptk'];
$pgn=$_GET['pgn'];
$kom=$_GET['kom'];
$thn=$_GET['thn'];
$rh=$_GET['rh'];


$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(67);
$objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(18);
$objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(18);
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(18);
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(18);
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(18);
$objPHPExcel->getSheet(0)->getColumnDimension('G')->setWidth(18);
$objPHPExcel->getSheet(0)->getColumnDimension('H')->setWidth(18);
$objPHPExcel->getSheet(0)->getColumnDimension('I')->setWidth(5);
$objPHPExcel->getSheet(0)->getColumnDimension('J')->setWidth(8);

$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('LAPORAN REALISASI');
$objPHPExcel->getSheet(0)

->setCellValue('A1', 'Sub / Kegiatan')
->setCellValue('B1', 'Anggaran')
->setCellValue('C1', 'RL TW-1')
->setCellValue('D1', 'RL TW-2')
->setCellValue('E1', 'RL TW-3')
->setCellValue('F1', 'RL TW-4')
->setCellValue('G1', 'Total')
->setCellValue('H1', 'Sisa')
->setCellValue('I1', '%')
->setCellValue('J1', '% Sisa');

$ca=pg_query("SELECT anggaran FROM sub_kegiatan WHERE id=1436");
while($rca=pg_fetch_array($ca)) {$angg=$rca[0];}
                                
$cb=pg_query("SELECT COALESCE(SUM(a.nilai_pelunasan),0) FROM vr_pelunasan a
LEFT OUTER JOIN vr_realisasi r ON (r.id=a.vr_realisasi_id)
LEFT OUTER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
WHERE p.sub_kegiatan_id IN (1436,1437,1438)
AND EXTRACT(YEAR FROM DATE(a.tanggal_lunas))='$thn' 
AND EXTRACT(MONTH FROM DATE(a.tanggal_lunas)) BETWEEN '1' AND '3' AND a.tanggal_lunas IS NOT NULL
");
while($rcb=pg_fetch_array($cb)) {$l1=$rcb[0];}
                                
$cc=pg_query("SELECT COALESCE(SUM(a.nilai_pelunasan),0) FROM vr_pelunasan a
LEFT OUTER JOIN vr_realisasi r ON (r.id=a.vr_realisasi_id)
LEFT OUTER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
WHERE p.sub_kegiatan_id IN (1436,1437,1438)
AND EXTRACT(YEAR FROM DATE(a.tanggal_lunas))='$thn' 
AND EXTRACT(MONTH FROM DATE(a.tanggal_lunas)) BETWEEN '4' AND '6' AND a.tanggal_lunas IS NOT NULL");
while($rcc=pg_fetch_array($cc)) {$l2=$rcc[0];}
                                
$cd=pg_query("SELECT COALESCE(SUM(a.nilai_pelunasan),0) FROM vr_pelunasan a
LEFT OUTER JOIN vr_realisasi r ON (r.id=a.vr_realisasi_id)
LEFT OUTER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
WHERE p.sub_kegiatan_id IN (1436,1437,1438)
AND EXTRACT(YEAR FROM DATE(a.tanggal_lunas))='$thn' 
AND EXTRACT(MONTH FROM DATE(a.tanggal_lunas)) BETWEEN '7' AND '9' AND a.tanggal_lunas IS NOT NULL");
while($rcd=pg_fetch_array($cd)) {$l3=$rcd[0];}
                                
$ce=pg_query("SELECT COALESCE(SUM(a.nilai_pelunasan),0) FROM vr_pelunasan a
LEFT OUTER JOIN vr_realisasi r ON (r.id=a.vr_realisasi_id)
LEFT OUTER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
WHERE p.sub_kegiatan_id IN (1436,1437,1438)
AND EXTRACT(YEAR FROM DATE(a.tanggal_lunas))='$thn' 
AND EXTRACT(MONTH FROM DATE(a.tanggal_lunas)) BETWEEN '10' AND '12' AND a.tanggal_lunas IS NOT NULL");
while($rce=pg_fetch_array($ce)) {$l4=$rce[0];} 

/* ======================= */

$query = "SELECT a.inisial,o.inisial,t.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,
(SELECT COALESCE(SUM(a.nilai_pelunasan),0) FROM vr_pelunasan a
LEFT OUTER JOIN vr_realisasi r ON (r.id=a.vr_realisasi_id)
LEFT OUTER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
WHERE p.sub_kegiatan_id=k.id
AND EXTRACT(YEAR FROM DATE(a.tanggal_lunas))='$thn' 
AND EXTRACT(MONTH FROM DATE(a.tanggal_lunas)) BETWEEN '1' AND '3' AND a.tanggal_lunas IS NOT NULL
),
(SELECT COALESCE(SUM(a.nilai_pelunasan),0) FROM vr_pelunasan a
LEFT OUTER JOIN vr_realisasi r ON (r.id=a.vr_realisasi_id)
LEFT OUTER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
WHERE p.sub_kegiatan_id=k.id
AND EXTRACT(YEAR FROM DATE(a.tanggal_lunas))='$thn' 
AND EXTRACT(MONTH FROM DATE(a.tanggal_lunas)) BETWEEN '4' AND '6' AND a.tanggal_lunas IS NOT NULL
),
(SELECT COALESCE(SUM(a.nilai_pelunasan),0) FROM vr_pelunasan a
LEFT OUTER JOIN vr_realisasi r ON (r.id=a.vr_realisasi_id)
LEFT OUTER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
WHERE p.sub_kegiatan_id=k.id
AND EXTRACT(YEAR FROM DATE(a.tanggal_lunas))='$thn' 
AND EXTRACT(MONTH FROM DATE(a.tanggal_lunas)) BETWEEN '7' AND '9' AND a.tanggal_lunas IS NOT NULL
),
(SELECT COALESCE(SUM(a.nilai_pelunasan),0) FROM vr_pelunasan a
LEFT OUTER JOIN vr_realisasi r ON (r.id=a.vr_realisasi_id)
LEFT OUTER JOIN pekerjaan p ON (p.id=r.pekerjaan_id)
WHERE p.sub_kegiatan_id=k.id
AND EXTRACT(YEAR FROM DATE(a.tanggal_lunas))='$thn' 
AND EXTRACT(MONTH FROM DATE(a.tanggal_lunas)) BETWEEN '10' AND '12' AND a.tanggal_lunas IS NOT NULL
),k.id,k.kegiatan
                                    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' AND k.anggaran IS NOT NULL
                                    ".($pejabat_pengadaan_id == null ? "" : "AND k.pejabat_pengadaan_id='$pejabat_pengadaan_id'")."
                                    ".($kegiatan == null ? "" : "AND k.kegiatan='$kegiatan' AND k.rh='$rh'")."
                                    ".($kpa == null ? "" : "AND k.kpa_id='$kpa'")." 
                                    ".($pptk == null ? "" : "AND k.pptk_id='$pptk'")." 
                                    ".($pgn == null ? "" : "AND k.pejabat_pengadaan_id='$pgn'")." 
                                    ".($kom == null ? "" : "
                                        ".($kom == "ALL" ? "AND (k.ppkom_id IS NOT NULL AND o.inisial <> 'PPK')" : "
                                            ".($kom == "NON" ? "AND (k.ppkom_id IS NULL OR o.inisial = 'PPK')" : "AND k.ppkom_id='$kom'")."                                             
                                        ")." 
                                    ")."   
                                    ORDER BY k.id ASC";

$hasil = pg_query($query);
$baris = 2;
$a=1;
while ($r = pg_fetch_array($hasil))
{
if (($r['id']=='1436') || ($r['id']=='1437') || ($r['id']=='1438')) {
$p=@((($l1+$l2+$l3+$l4))/$angg);
$p1=@(($angg-($l1+$l2+$l3+$l4))/$angg);
$tot=$r[15]+$r[16]+$r[17]+$r[18];
$sisa=$angg-($l1+$l2+$l3+$l4);
} else {
$p=@((($r[15]+$r[16]+$r[17]+$r[18]))/$r[14]);
$p1=@(($r[14]-($r[15]+$r[16]+$r[17]+$r[18]))/$r[14]);
$tot=$r[15]+$r[16]+$r[17]+$r[18];
$sisa=$r[14]-($r[15]+$r[16]+$r[17]+$r[18]);
}
    $objPHPExcel->getSheet(0)
            ->setCellValue('A'.$baris, $r[13]."".PHP_EOL."".$r[20])
    		->setCellValueExplicit('B'.$baris, $r[14], PHPExcel_Cell_DataType::TYPE_NUMERIC) /*anggaran*/
    		->setCellValueExplicit('C'.$baris, $r[15], PHPExcel_Cell_DataType::TYPE_NUMERIC) 
    		->setCellValueExplicit('D'.$baris, $r[16], PHPExcel_Cell_DataType::TYPE_NUMERIC) 
    		->setCellValueExplicit('E'.$baris, $r[17], PHPExcel_Cell_DataType::TYPE_NUMERIC)
            ->setCellValueExplicit('F'.$baris, $r[18], PHPExcel_Cell_DataType::TYPE_NUMERIC)
            ->setCellValueExplicit('G'.$baris, $tot, PHPExcel_Cell_DataType::TYPE_NUMERIC)
            ->setCellValueExplicit('H'.$baris, $sisa, PHPExcel_Cell_DataType::TYPE_NUMERIC)
            ->setCellValue('I'.$baris, number_format($p*100, 0, '.', ''))
            ->setCellValue('J'.$baris, number_format($p1*100, 0, '.', ''));

    $objPHPExcel->getSheet(0)->getStyle('B'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('C'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('D'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('E'.$baris)->getNumberFormat()->setFormatCode('#,##0');
    $objPHPExcel->getSheet(0)->getStyle('F'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('G'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('H'.$baris)->getNumberFormat()->setFormatCode('#,##0');

	$objPHPExcel->getSheet(0)->getStyle('P'.$baris)->getNumberFormat()->setFormatCode('@');
    $objPHPExcel->getSheet(0)->getStyle('I1:J'.$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="laporan_realisasi.xls"');
header('Cache-Control: max-age=0');

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

?>