256 lines
16 KiB
PHP
256 lines
16 KiB
PHP
<?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')) {$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 PBJ';} else
|
|
if ($key=='loro') {$title='SELESAI VALIDASI';} else
|
|
if ($key=='telu') {$title='SETOR VERIFIKASI';} else
|
|
if ($key=='papat') {$title='PROSES VERIFIKASI';} else
|
|
if ($key=='limo') {$title='PROSES PELUNASAN';} else
|
|
if ($key=='enem') {$title='TERBAYAR';} else
|
|
if ($key=='pitu') {$title='RED NOTICE';}
|
|
|
|
$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 == null ? "" : "
|
|
".($key == 'siji' ? "AND p.tgl_setor IS NULL AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NULL AND l.tanggal_lunas IS NULL" : "
|
|
".($key == 'loro' ? "AND p.tgl_setor IS NOT NULL AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NULL AND l.tanggal_lunas IS NULL" : "
|
|
".($key == 'papat' ? "AND p.tgl_setor IS NOT NULL AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL AND l.tanggal_setor IS NULL AND l.tanggal_lunas IS NULL" : "
|
|
".($key == 'enem' ? "AND p.tgl_setor IS NOT NULL AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL AND l.tanggal_setor IS NOT NULL AND l.tanggal_lunas IS NOT NULL" : "
|
|
".($key == 'pitu' ? "AND p.tgl_setor IS NULL AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL" : "
|
|
".($key == 'telu' ? "AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NOT NULL AND l.tanggal_lunas IS NULL" : "
|
|
".($key == 'limo' ? "AND p.tgl_setor IS NOT NULL AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL AND l.tanggal_setor IS NOT NULL AND l.tanggal_lunas IS 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.no_sp ASC,l.data_ke ASC
|
|
";
|
|
|
|
$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;
|
|
|
|
?>
|
|
|
|
|