<?php

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

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

$thn=$_GET['thn'];
$key=$_GET['key'];
$rk_sopo_id=$_GET['rk_sopo_id'];
$jenis=$_GET['jenis'];

if (($jenis=='kepala instalasi') || ($jenis=='kpa') || ($jenis=='admin utama') || ($jenis=='kabag keu') || ($jenis=='Verifikator') || ($jenis=='Kepala Perbendaharaan')) {$nb='';} else
if ($jenis=='pptk') {$bn='s.pptk_id';$nb='INNER JOIN pptk t ON (t.id=s.pptk_id)';} else
if ($jenis=='ppkom') {$bn='s.ppkom_id';$nb='INNER JOIN ppkom o ON (o.id=s.ppkom_id)';} else
if ($jenis=='pejabat') {$bn='s.pejabat_pengadaan_id';$nb='INNER JOIN pejabat_pengadaan g ON (g.id=s.pejabat_pengadaan_id)';} 

if ($key=='siji') {$title='PROSES VERIFIKASI';} else
if ($key=='loro') {$title='PROSES PELUNASAN';} else
if ($key=='telu') {$title='TERBAYAR';}

$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);

$objPHPExcel->getSheet(0)->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(8);
$objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(8);
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(8);
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(64);
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(64);
$objPHPExcel->getSheet(0)->getColumnDimension('G')->setWidth(15);

$objPHPExcel->getSheet(0)->getColumnDimension('H')->setWidth(23);
$objPHPExcel->getSheet(0)->getColumnDimension('I')->setWidth(15);
$objPHPExcel->getSheet(0)->getColumnDimension('J')->setWidth(21);
$objPHPExcel->getSheet(0)->getColumnDimension('K')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('L')->setWidth(12);


$styleBold = array('font'  => array('bold'  => true));
$objPHPExcel->getSheet(0)->getStyle('A2:L2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A2:L2')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getRowDimension('2')->setRowHeight(25);
$objPHPExcel->getSheet(0)->setTitle($title);

$objPHPExcel->getSheet(0)->getRowDimension('1')->setRowHeight(39);
$objPHPExcel->getSheet(0)->setCellValue('A1', $title);
$objPHPExcel->getSheet(0)->getStyle('A1:L1')->getAlignment()->setWrapText(false);
$objPHPExcel->getSheet(0)->getStyle('A1:L1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A1:L1')->applyFromArray($styleBold);

$objPHPExcel->getSheet(0)
            ->setCellValue('A2', 'No')
			->setCellValue('B2', 'KPA')
			->setCellValue('C2', 'PPTK')
			->setCellValue('D2', 'PPKOM')
			->setCellValue('E2', 'Sub Kg')
            ->setCellValue('F2', 'Kegiatan')
			->setCellValue('G2', 'Nilai')
			->setCellValue('H2', 'No SP/SPK/Kwi')
			->setCellValue('I2', 'Masa')
			->setCellValue('J2', 'Track')
			->setCellValue('K2', 'Progress')
			->setCellValue('L2', 'Tanggal');


									$query = "SELECT s.no_angg,s.kd_panggil,s.sub_kegiatan,s.ra,s.rb,s.rc,s.rd,s.re,s.rf,s.rg,s.rh,s.kd_sibaku,s.kegiatan,
                                    p.kegiatan_pengadaan,l.tanggal_setor,
                                    COALESCE(l.nilai_pelunasan,p.nilai_sp),l.ppn,l.pph_21,l.pph_22,l.pph_23,l.pph_final,
                                    p.no_sp,l.no_faktur,p.tgl_mulai_sp,p.tgl_selesai_sp,r.st_buku,r.st_termin,l.tanggal_input,l.id,r.pekerjaan_id,
                                    CASE WHEN r.st_termin='termin' THEN
                                    l.data_ke   
                                    ELSE '0' END,l.tanggal_lunas,COALESCE(n.nama,p.cad1),k.inisial,l.status_berkas,t.inisial,m.inisial,
                                    COALESCE(l.tanggal_lunas,
									COALESCE(l.tanggal_setor,
                                    COALESCE(l.tgl_terima_verif,
									COALESCE(l.tgl_setor_verif,
									COALESCE(l.tgl_setor_pptk,
                                    COALESCE(l.tanggal_input,
                                    COALESCE(p.tgl_setor,
                                   	COALESCE((SELECT a1.tanggal FROM pengerjaan_adminers a1 WHERE a1.id=MAX(a.id)),
                                        p.tgl_input)
                                    ))))))),
                                    l.nilai_pelunasan,p.nilai_sp,
                                    (SELECT a1.keterangan FROM pengerjaan_adminers a1 WHERE a1.id=MAX(a.id)),p.status,p.id,r.stum,l.st_setor_verif,l.tgl_terima_verif,l.tgl_setor_verif,l.tgl_setor_pptk,p.id as pek_id
                                    FROM pekerjaan p
                                    LEFT OUTER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
                                    LEFT OUTER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
                                    LEFT OUTER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
                                    LEFT OUTER JOIN rekanan n ON (n.id=p.rekanan_id)
                                    LEFT OUTER JOIN kpa k ON (k.id=s.kpa_id)
                                    LEFT OUTER JOIN pptk t ON (t.id=s.pptk_id)
                                    LEFT OUTER JOIN ppkom m ON (m.id=s.ppkom_id)
                                    LEFT OUTER JOIN pengerjaan_adminers a ON (a.pekerjaan_id=p.id)
                                    WHERE s.thn='$thn' AND COALESCE(l.st_hutang,'') = 'H' AND COALESCE(p.user_nama,'')<>'HUTANG TERMIN' AND p.status_setor IS NULL
                                    ".($key == '' ? "" : "
                                              ".($key == 'siji' ? "AND p.user_nama IS NOT NULL AND p.tgl_setor IS NULL AND l.tgl_terima_verif  IS NOT NULL AND l.tanggal_setor IS NULL AND l.tanggal_lunas IS NULL" : "
                                               	 ".($key == 'loro' ? "AND p.user_nama IS NOT NULL AND p.tgl_setor IS NULL AND l.tgl_terima_verif  IS NOT NULL AND l.tanggal_setor IS NOT NULL AND l.tanggal_lunas IS NULL" : "
                                                 	".($key == 'telu' ? "AND p.user_nama IS NOT NULL AND p.tgl_setor IS NULL AND l.tgl_terima_verif  IS NOT NULL AND l.tanggal_setor IS NOT NULL AND l.tanggal_lunas IS NOT NULL" : "
                                                    ")."
                                                 ")."
                                              ")."
                                    ")." 
                                    GROUP BY
                                    s.no_angg,s.kd_panggil,s.sub_kegiatan,s.ra,s.rb,s.rc,s.rd,s.re,s.rf,s.rg,s.rh,s.kd_sibaku,s.kegiatan,
                                    p.kegiatan_pengadaan,l.tanggal_setor,
                                    COALESCE(l.nilai_pelunasan,p.nilai_sp),l.ppn,l.pph_21,l.pph_22,l.pph_23,l.pph_final,
                                    p.no_sp,l.no_faktur,p.tgl_mulai_sp,p.tgl_selesai_sp,r.st_buku,r.st_termin,l.tanggal_input,l.id,r.pekerjaan_id,
                                    CASE WHEN r.st_termin='termin' THEN
                                    l.data_ke   
                                    ELSE '0' END,l.tanggal_lunas,COALESCE(n.nama,p.cad1),k.inisial,l.status_berkas,t.inisial,m.inisial,
                                    l.nilai_pelunasan,p.nilai_sp,p.id,p.status,r.stum,l.st_setor_verif,p.user_nama,l.tgl_terima_verif,l.tgl_setor_verif,l.tgl_setor_pptk,p.id
                                    ORDER BY p.id DESC
                                    ";

$hasil = pg_query($query);
$baris = 3;
$a=1;
while ($r = pg_fetch_array($hasil)) {
$vrprlid=$r[28];
$pekerjaan_id=$r['pek_id'];
$d2=pg_query("SELECT EXTRACT('Year' FROM tgl_input::TIMESTAMP) FROM pekerjaan WHERE id=$pekerjaan_id");
										while($r2=pg_fetch_array($d2)) {$thn_set=$r2[0];}

										$d1=pg_query("SELECT status FROM set_pemb_pptk WHERE thn='$thn_set'");
										while($r1=pg_fetch_array($d1)) {$status_pemb_pptk=$r1[0];}

										if ($status_pemb_pptk=='t') {
											$ketpembpptk='PEMBANTU PPTK';
										} else {
											$ketpembpptk='TIM VALIDASI PENGADAAN';
										}
if (($r['tanggal_lunas']==NULL) || ($r['tanggal_lunas']=='') || (date('Y-m-d',strtotime($r['tanggal_lunas']))=='1970-01-01')) {
                                            if (($r['tanggal_setor']==NULL) || ($r['tanggal_setor']=='') || (date('Y-m-d',strtotime($r['tanggal_setor']))=='1970-01-01')) {
                                            	if (($r['tgl_terima_verif']==NULL) || ($r['tgl_terima_verif']=='') || (date('Y-m-d',strtotime($r['tgl_terima_verif']))=='1970-01-01')) {
                                                	if (($r['tgl_setor_verif']==NULL) || ($r['tgl_setor_verif']=='') || (date('Y-m-d',strtotime($r['tgl_setor_verif']))=='1970-01-01')) {
                                                    	if (($r['tgl_setor_pptk']==NULL) || ($r['tgl_setor_pptk']=='') || (date('Y-m-d',strtotime($r['tgl_setor_pptk']))=='1970-01-01')) {
                                                        	if (($r['tanggal_input']==NULL) || ($r['tanggal_input']=='') || (date('Y-m-d',strtotime($r['tanggal_input']))=='1970-01-01')) {
                                                                if ($r[40]==NULL) {
                                         							$posisi='PENGADAAN';
                                       	 							$st_pbj='Penerbitan SP/SPK/Kwi';
                                       							} else {                	 
                                       	 							if (strtolower($r['status'])=='selesai adminers') {
                                            							$posisi='PENGADAAN';
                                         								$st_pbj='Selesai Admin';
                                         							} else 
                                         							if (strtolower($r[40])=='selesai') {
                                            							$posisi='PENGADAAN';
                                         								$st_pbj='Selesai Proses';
                                         							} else {
                                            							$posisi='PENGADAAN';
                                         								$st_pbj=$r[40];
                                         							}
                                       							}
                                       							$status=$st_pbj;
                                       							$nilai=$r[39];
                                                            } else {
                                                            	$status='PROSES VALIDASI';
                                            					$posisi='TIM VALIDASI';
                                            					$nilai=$r[38];
                                                            }
                                                        } else {
                                                        	//$status='PROSES PENGECEKAN KELENGKAPAN';
                                            				//$posisi='PEMBANTU PPTK';
                                                        	include("../pencarian/progress_npd.php");
                                            				$nilai=$r[38];
                                                        }
                                                    } else {
                                                    	$status='SETOR VERIFIKASI';
                                            			$posisi='PEMBANTU PPTK';
                                            			$nilai=$r[38];
                                                    }
                                                } else {
                                                	$status='PROSES VERIFIKASI';
                                            		$posisi='VERIFIKASI';
                                            		$nilai=$r[38];
                                                }
                                            } else {
                                                $status='SETOR SIAP PELUNASAN';
                                            	$posisi='VERIFIKASI';
                                            	$nilai=$r[38];
                                            }
                                        } else {
                                            $status='LUNAS';
                                    		$posisi='PERBENDAHARAAN';
                                        	$nilai=$r[38];
                                        }
                                    $stum=$r['stum'];
									if ($r[30]=='0') {
    									if ($stum=='uang_muka') {$trm='UANG MUKA';$sp=$r['no_sp']."\n".$trm;} else {$trm='';$sp=$r['no_sp'];}
    								} else {$trm=strtoupper(($r['st_termin'].' '.$r[30]));$sp=$r['no_sp']."\n".$trm;}
									$skg=strtoupper($r['sub_kegiatan']);
									$kg=strtoupper($r['kegiatan'].' - '.$r['kegiatan_pengadaan'].' - '.$r[32]);

if (($r['tgl_mulai_sp']==null) || ($r['tgl_mulai_sp']=='1970-01-01 00:00:00')) {$vv='';} else {
$vv=date('d-m-Y',strtotime($r['tgl_mulai_sp'])).' s/d '.date('d-m-Y',strtotime($r['tgl_selesai_sp']));
}
	$objPHPExcel->getSheet(0)
            ->setCellValue('A'.$baris, $a)
            ->setCellValue('B'.$baris, $r[33])        
            ->setCellValue('C'.$baris, $r[35])
            ->setCellValue('D'.$baris, $r[36])
            ->setCellValue('E'.$baris, $skg)
    		->setCellValue('F'.$baris, $kg)
            ->setCellValueExplicit('G'.$baris, $nilai, PHPExcel_Cell_DataType::TYPE_NUMERIC)
    		->setCellValueExplicit('H'.$baris, $sp, PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValue('I'.$baris, $vv)
            ->setCellValue('J'.$baris, $posisi)
            ->setCellValue('K'.$baris, strtoupper($status))
    		->setCellValue('L'.$baris, date('d-m-Y',strtotime($r[37])));

/*
    $objPHPExcel->getSheet(0)->getStyle('G'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('A2:D'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('H'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('J2:L'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('A2:L'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
*/
    $a++;
    $baris++;
}
	$objPHPExcel->getSheet(0)->getStyle('G2:G'.$baris)->getNumberFormat()->setFormatCode('#,##0');
	$objPHPExcel->getSheet(0)->getStyle('A2:D'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('H2:H'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$objPHPExcel->getSheet(0)->getStyle('J2:L'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objPHPExcel->getSheet(0)->getStyle('A2:L'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0);

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

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

?>