712 lines
34 KiB
PHP
712 lines
34 KiB
PHP
<?php
|
|
|
|
require_once '../../assets/excel/PHPExcel.php';
|
|
|
|
// koneksi ke database
|
|
|
|
include("../koneksi.php");
|
|
$thn=$_GET['thn'];
|
|
$cari_verifikator=$_GET['cari_verifikator'];
|
|
$nama_verifikator=$_GET['nama_verifikator'];
|
|
$axx=strtoupper($_GET['a']);
|
|
$bxx=strtoupper($_GET['b']);
|
|
$cxx=strtoupper($_GET['c']);
|
|
$tm1=$_GET['m'];
|
|
$tm1=str_replace('/','-',$tm1);
|
|
$tt1=date('Y',strtotime($tm1));
|
|
$bb1=date('m',strtotime($tm1));
|
|
$hh1=date('d',strtotime($tm1));
|
|
|
|
$ts1=$_GET['s'];
|
|
$ts1=str_replace('/','-',$ts1);
|
|
$tt2=date('Y',strtotime($ts1));
|
|
$bb2=date('m',strtotime($ts1));
|
|
$hh2=date('d',strtotime($ts1));
|
|
|
|
$vr_pelunasan_id=$_GET['vr_pelunasan_id'];
|
|
|
|
|
|
|
|
|
|
$objPHPExcel = new PHPExcel();
|
|
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
|
|
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
|
|
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);
|
|
|
|
|
|
/*PROSES v1*/
|
|
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('A')->setWidth(5);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(35.86);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(6);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(6);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(11.43);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(23.57);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('G')->setWidth(38.57);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('H')->setWidth(40);
|
|
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('I')->setWidth(15.43);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('J')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('K')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('L')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('M')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('N')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('O')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('P')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('Q')->setWidth(16);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('R')->setWidth(16);
|
|
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('S')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('T')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('U')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('V')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('W')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('X')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('Y')->setWidth(13);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('Z')->setWidth(16);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('AA')->setWidth(16);
|
|
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('AB')->setWidth(20);
|
|
$objPHPExcel->getSheet(0)->getColumnDimension('AC')->setWidth(24.71);
|
|
|
|
|
|
$styleBold = array('font' => array('bold' => true));
|
|
$objPHPExcel->getSheet(0)->getStyle('A3:AC3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
|
|
$objPHPExcel->getSheet(0)->getStyle('A3:AC3')->applyFromArray($styleBold);
|
|
$objPHPExcel->getSheet(0)->getRowDimension('1')->setRowHeight(15);
|
|
$objPHPExcel->getSheet(0)->setTitle('LAP PROSES v1');
|
|
|
|
$objPHPExcel->getSheet(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
|
|
$objPHPExcel->getSheet(0)->setCellValue('A1', 'Tanggal Verif : '.$tm1.' s/d '.$ts1.' | '.$cari_verifikator);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:AC1')->getAlignment()->setWrapText(false);
|
|
|
|
|
|
$objPHPExcel->getSheet(0)
|
|
->setCellValue('A3', 'No')
|
|
->setCellValue('B3', 'Sub')
|
|
|
|
->setCellValue('C3', 'KPA')
|
|
->setCellValue('D3', 'PPTK')
|
|
|
|
->setCellValue('E3', 'Kd Panggil')
|
|
->setCellValue('F3', 'Rekening')
|
|
->setCellValue('G3', 'Nama Rekanan')
|
|
->setCellValue('H3', 'Kegiatan')
|
|
->setCellValue('I3', 'Nilai Kwitansi')
|
|
|
|
->setCellValue('J3', 'Kwitansi')
|
|
->setCellValue('K3', 'Faktur Penj')
|
|
->setCellValue('L3', 'Nota Dinas')
|
|
->setCellValue('M3', 'Faktur Pajak')
|
|
->setCellValue('N3', 'SSP PPN')
|
|
->setCellValue('O3', 'SSP PPH')
|
|
->setCellValue('P3', 'Ba HP Admin')
|
|
->setCellValue('Q3', 'Ba Serah Terima')
|
|
->setCellValue('R3', 'SP / SPK')
|
|
|
|
|
|
->setCellValue('S3', 'Penerimaan Honor')
|
|
->setCellValue('T3', 'Daftar Hadir')
|
|
->setCellValue('U3', 'TOR')
|
|
->setCellValue('V3', 'SK / SPT')
|
|
->setCellValue('W3', 'Pendukung')
|
|
->setCellValue('X3', 'Ba UM')
|
|
->setCellValue('Y3', 'Ba Ujicoba')
|
|
->setCellValue('Z3', 'Ba Pembayaran')
|
|
->setCellValue('AA3', 'Ba Kemajuan')
|
|
|
|
->setCellValue('AB3', 'No Kwi/SP/SPK')
|
|
->setCellValue('AC3', 'Periode Tgl SP/SPK');
|
|
|
|
|
|
|
|
$query = "SELECT
|
|
s.sub_kegiatan,k.inisial,t.inisial,s.kd_panggil,s.ra,s.rb,s.rc,s.rd,s.re,s.rf,s.rg,s.rh,
|
|
COALESCE(n.nama,p.cad1),s.kegiatan,p.kegiatan_pengadaan,l.nilai_pelunasan,
|
|
l.kwitansi,l.faktur_penjualan,l.nota_dinas,l.faktur_pajak,l.ssp_ppn,l.ssp_pph,l.ba_hp_admin,l.ba_st,l.sp_spk,
|
|
p.no_sp,p.tgl_mulai_sp,p.tgl_selesai_sp,honor,hadir,tor,spt,pendukung,ba_um,ba_ujicoba,ba_pembayaran,ba_kemajuan,
|
|
CASE WHEN r.st_termin='termin' THEN
|
|
l.data_ke
|
|
ELSE '0' END,r.st_termin,r.stum, l.tgl_terima_verif
|
|
FROM pekerjaan p
|
|
INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
|
|
INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
|
|
INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
|
|
LEFT OUTER JOIN rekanan n ON (n.id=p.rekanan_id)
|
|
INNER JOIN kpa k ON (k.id=s.kpa_id)
|
|
INNER JOIN pptk t ON (t.id=s.pptk_id)
|
|
INNER JOIN ppkom m ON (m.id=s.ppkom_id)
|
|
WHERE l.id IN ($vr_pelunasan_id)
|
|
";
|
|
|
|
$hasil = pg_query($query);
|
|
$baris = 4;
|
|
$a=1;
|
|
while ($r = pg_fetch_array($hasil))
|
|
{
|
|
$stum=$r['stum'];
|
|
//if ($r[37]=='0') {$trm='';} else {$trm=' - '.strtoupper(($r['st_termin'].' '.$r[37]));}
|
|
if ($r[37]=='0') {
|
|
if ($stum=='uang_muka') {$trm=' - UANG MUKA';} else {$trm='';}
|
|
|
|
} else {$trm=' - '.strtoupper(($r['st_termin'].' '.$r[37]));}
|
|
|
|
if (($r['tgl_mulai_sp']==null) || ($r['tgl_mulai_sp']=='1970-01-01 00:00:00')) {$tg='';} else {
|
|
$tg=date('d-m-Y',strtotime($r['tgl_mulai_sp'])).' s/d '.date('d-m-Y',strtotime($r['tgl_selesai_sp'])); }
|
|
|
|
$rek=$r['ra'].' '.$r['rb'].' '.$r['rc'].' '.$r['rd'].' '.$r['re'].' '.$r['rf'].' '.$r['rg'].' '.$r['rh'];
|
|
|
|
if ($r['kwitansi']=='t') {$ck1='v';} else {$ck1='';}
|
|
if ($r['faktur_penjualan']=='t') {$ck2='v';} else {$ck2='';}
|
|
if ($r['nota_dinas']=='t') {$ck3='v';} else {$ck3='';}
|
|
if ($r['faktur_pajak']=='t') {$ck4='v';} else {$ck4='';}
|
|
if ($r['ssp_ppn']=='t') {$ck5='v';} else {$ck5='';}
|
|
if ($r['ssp_pph']=='t') {$ck6='v';} else {$ck6='';}
|
|
if ($r['ba_hp_admin']=='t') {$ck7='v';} else {$ck7='';}
|
|
if ($r['ba_st']=='t') {$ck8='v';} else {$ck8='';}
|
|
if ($r['sp_spk']=='t') {$ck9='v';} else {$ck9='';}
|
|
|
|
if ($r['honor']=='t') {$ck10='v';} else {$ck10='';}
|
|
if ($r['hadir']=='t') {$ck11='v';} else {$ck11='';}
|
|
if ($r['tor']=='t') {$ck12='v';} else {$ck12='';}
|
|
if ($r['spt']=='t') {$ck13='v';} else {$ck13='';}
|
|
if ($r['pendukung']=='t') {$ck14='v';} else {$ck14='';}
|
|
if ($r['ba_um']=='t') {$ck15='v';} else {$ck15='';}
|
|
if ($r['ba_ujicoba']=='t') {$ck16='v';} else {$ck16='';}
|
|
if ($r['ba_pembayaran']=='t') {$ck17='v';} else {$ck17='';}
|
|
if ($r['ba_kemajuan']=='t') {$ck18='v';} else {$ck18='';}
|
|
|
|
$objPHPExcel->getSheet(0)
|
|
->setCellValue('A'.$baris, $a)
|
|
->setCellValue('B'.$baris, $r['sub_kegiatan'])
|
|
->setCellValue('C'.$baris, $r[1])
|
|
->setCellValue('D'.$baris, $r[2])
|
|
->setCellValue('E'.$baris, $r['kd_panggil'])
|
|
->setCellValue('F'.$baris, $rek)
|
|
->setCellValue('G'.$baris, $r[12])
|
|
->setCellValue('H'.$baris, strtoupper($r['kegiatan'].' - '.$r['kegiatan_pengadaan'].''.$trm.' - '.$r[12]))
|
|
->setCellValue('I'.$baris, $r['nilai_pelunasan'])
|
|
->setCellValue('J'.$baris, $ck1)
|
|
->setCellValue('K'.$baris, $ck2)
|
|
->setCellValue('L'.$baris, $ck3)
|
|
->setCellValue('M'.$baris, $ck4)
|
|
->setCellValue('N'.$baris, $ck5)
|
|
->setCellValue('O'.$baris, $ck6)
|
|
->setCellValue('P'.$baris, $ck7)
|
|
->setCellValue('Q'.$baris, $ck8)
|
|
->setCellValue('R'.$baris, $ck9)
|
|
|
|
->setCellValue('S'.$baris, $ck10)
|
|
->setCellValue('T'.$baris, $ck11)
|
|
->setCellValue('U'.$baris, $ck12)
|
|
->setCellValue('V'.$baris, $ck13)
|
|
->setCellValue('W'.$baris, $ck14)
|
|
->setCellValue('X'.$baris, $ck15)
|
|
->setCellValue('Y'.$baris, $ck16)
|
|
->setCellValue('Z'.$baris, $ck17)
|
|
->setCellValue('AA'.$baris, $ck18)
|
|
|
|
->setCellValue('AB'.$baris, $r['no_sp'])
|
|
->setCellValue('AC'.$baris, $tg);
|
|
|
|
|
|
$objPHPExcel->getSheet(0)->getStyle('A3:A'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
|
|
$objPHPExcel->getSheet(0)->getStyle('A1:AC'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
|
|
$objPHPExcel->getSheet(0)->getStyle('I'.$baris)->getNumberFormat()->setFormatCode('#,##0');
|
|
$objPHPExcel->getSheet(0)->getStyle('J'.$baris.':AC'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_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);
|
|
|
|
|
|
/* PROSES v2 =================================================================================================================================================== */
|
|
|
|
|
|
|
|
$objPHPExcel->createSheet();
|
|
$objPHPExcel->setActiveSheetIndex(1);
|
|
$objPHPExcel->getActiveSheet()->setTitle('LAP PROSES v2');
|
|
|
|
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('A')->setWidth(5);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('B')->setWidth(6);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('C')->setWidth(6);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('D')->setWidth(11);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('E')->setWidth(23.57);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('F')->setWidth(38.57);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('G')->setWidth(38);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('H')->setWidth(15.43);
|
|
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('I')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('J')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('K')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('L')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('M')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('N')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('O')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('P')->setWidth(16);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('Q')->setWidth(15);
|
|
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('R')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('S')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('T')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('U')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('V')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('W')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('X')->setWidth(13);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('Y')->setWidth(16);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('Z')->setWidth(15);
|
|
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('AA')->setWidth(20);
|
|
$objPHPExcel->getSheet(1)->getColumnDimension('AB')->setWidth(25);
|
|
|
|
|
|
$styleBold = array('font' => array('bold' => true));
|
|
$objPHPExcel->getSheet(1)->getStyle('A4:AB4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
|
|
$objPHPExcel->getSheet(1)->getStyle('A4:AB4')->applyFromArray($styleBold);
|
|
$objPHPExcel->getSheet(1)->getRowDimension('1')->setRowHeight(15);
|
|
|
|
$objPHPExcel->getSheet(1)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
|
|
$objPHPExcel->getSheet(1)->setCellValue('A1', 'Tanggal Verif : '.$tm1.' s/d '.$ts1.' | '.$cari_verifikator);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:AB1')->getAlignment()->setWrapText(false);
|
|
|
|
$objPHPExcel->getSheet(1)->getStyle('A3:AB3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A3:AB3')->getAlignment()->setWrapText(true);
|
|
$objPHPExcel->getSheet(1)->getStyle('A3:AB3')->applyFromArray($styleBold);
|
|
|
|
$objPHPExcel->getSheet(1)
|
|
->setCellValue('A3','No')
|
|
->setCellValue('B3', 'KPA')
|
|
->setCellValue('C3', 'PPTK')
|
|
->setCellValue('D3', 'Kd Panggil')
|
|
->setCellValue('E3', 'Rekening')
|
|
->setCellValue('F3', 'Nama Rekanan')
|
|
->setCellValue('G3', 'Kegiatan')
|
|
->setCellValue('H3', 'Nilai Kwitansi')
|
|
->setCellValue('I3', 'Kwitansi')
|
|
->setCellValue('J3', 'Faktur Penj')
|
|
->setCellValue('K3', 'Nota Dinas')
|
|
->setCellValue('L3', 'Faktur Pajak')
|
|
->setCellValue('M3', 'SSP PPN')
|
|
->setCellValue('N3', 'SSP PPH')
|
|
->setCellValue('O3', 'Ba HP Admin')
|
|
->setCellValue('P3', 'Ba Serah Terima')
|
|
->setCellValue('Q3', 'SP / SPK')
|
|
|
|
->setCellValue('R3', 'Penerimaan Honor')
|
|
->setCellValue('S3', 'Daftar Hadir')
|
|
->setCellValue('T3', 'TOR')
|
|
->setCellValue('U3', 'SK / SPT')
|
|
->setCellValue('V3', 'Pendukung')
|
|
->setCellValue('W3', 'Ba UM')
|
|
->setCellValue('X3', 'Ba Ujicoba')
|
|
->setCellValue('Y3', 'Ba Pembayaran')
|
|
->setCellValue('Z3', 'Ba Kemajuan')
|
|
|
|
->setCellValue('AA3', 'No Kwi/SP/SPK')
|
|
->setCellValue('AB3', 'Periode Tgl SP/SPK');
|
|
|
|
|
|
|
|
$query2 = "SELECT DISTINCT s.sub_kegiatan
|
|
FROM pekerjaan p
|
|
INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
|
|
INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
|
|
INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
|
|
INNER JOIN kpa a1 ON (s.kpa_id=a1.id)
|
|
INNER JOIN ppkom a2 ON (s.ppkom_id=a2.id)
|
|
INNER JOIN pptk a3 ON (s.pptk_id=a3.id)
|
|
WHERE l.id IN ($vr_pelunasan_id)
|
|
";
|
|
$hasil2 = pg_query($query2);
|
|
$baris2 = 4;
|
|
$a2=1;
|
|
while ($r2 = pg_fetch_array($hasil2))
|
|
{
|
|
$baris3 = $baris2+$a2;
|
|
|
|
$skg=$r2[0];
|
|
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris2.':AB'.$baris2)->getAlignment()->setWrapText(false);
|
|
$objPHPExcel->getSheet(1)->getStyle('A'.$baris2.':AB'.$baris2)->applyFromArray($styleBold);
|
|
$objPHPExcel->getSheet(1)->getStyle('A'.$baris2.':AB'.$baris2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
|
|
$objPHPExcel->getSheet(1)->setCellValue('A'.$baris2, $r2[0]);
|
|
|
|
|
|
|
|
|
|
|
|
$query3 = "SELECT
|
|
s.sub_kegiatan,k.inisial,t.inisial,s.kd_panggil,s.ra,s.rb,s.rc,s.rd,s.re,s.rf,s.rg,s.rh,
|
|
COALESCE(n.nama,p.cad1),s.kegiatan,p.kegiatan_pengadaan,l.nilai_pelunasan,
|
|
l.kwitansi,l.faktur_penjualan,l.nota_dinas,l.faktur_pajak,l.ssp_ppn,l.ssp_pph,l.ba_hp_admin,l.ba_st,l.sp_spk,
|
|
p.no_sp,p.tgl_mulai_sp,p.tgl_selesai_sp,hadir,tor,spt,pendukung,ba_um,ba_ujicoba,ba_pembayaran,ba_kemajuan,
|
|
CASE WHEN r.st_termin='termin' THEN
|
|
l.data_ke
|
|
ELSE '0' END,r.st_termin,l.tgl_terima_verif
|
|
FROM pekerjaan p
|
|
INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
|
|
INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
|
|
INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
|
|
LEFT OUTER JOIN rekanan n ON (n.id=p.rekanan_id)
|
|
INNER JOIN kpa k ON (k.id=s.kpa_id)
|
|
INNER JOIN pptk t ON (t.id=s.pptk_id)
|
|
INNER JOIN ppkom m ON (m.id=s.ppkom_id)
|
|
WHERE s.thn='$thn' AND l.status_berkas IN ('FIX','PROSES') AND l.tanggal_setor IS NULL
|
|
AND s.sub_kegiatan='$skg'
|
|
AND l.id IN ($vr_pelunasan_id)
|
|
";
|
|
$hasil3 = pg_query($query3);
|
|
$ab=1;
|
|
$a3=0;
|
|
while(($r3=pg_fetch_array($hasil3)) !== FALSE)
|
|
{
|
|
$baris4=$baris3+$a3;
|
|
$barisz=$baris3+$a3;
|
|
|
|
if ($r3[36]=='0') {$trm='';} else {$trm=' - '.strtoupper(($r3['st_termin'].' '.$r3[36]));}
|
|
|
|
if (($r3['tgl_mulai_sp']==null) || ($r3['tgl_mulai_sp']=='1970-01-01 00:00:00')) {$tg='';} else {
|
|
$tg=date('d-m-Y',strtotime($r3['tgl_mulai_sp'])).' s/d '.date('d-m-Y',strtotime($r3['tgl_selesai_sp'])); }
|
|
|
|
$rek=$r3['ra'].' '.$r3['rb'].' '.$r3['rc'].' '.$r3['rd'].' '.$r3['re'].' '.$r3['rf'].' '.$r3['rg'].' '.$r3['rh'];
|
|
|
|
if ($r3['kwitansi']=='t') {$ck1='v';} else {$ck1='';}
|
|
if ($r3['faktur_penjualan']=='t') {$ck2='v';} else {$ck2='';}
|
|
if ($r3['nota_dinas']=='t') {$ck3='v';} else {$ck3='';}
|
|
if ($r3['faktur_pajak']=='t') {$ck4='v';} else {$ck4='';}
|
|
if ($r3['ssp_ppn']=='t') {$ck5='v';} else {$ck5='';}
|
|
if ($r3['ssp_pph']=='t') {$ck6='v';} else {$ck6='';}
|
|
if ($r3['ba_hp_admin']=='t') {$ck7='v';} else {$ck7='';}
|
|
if ($r3['ba_st']=='t') {$ck8='v';} else {$ck8='';}
|
|
if ($r3['sp_spk']=='t') {$ck9='v';} else {$ck9='';}
|
|
|
|
if ($r3['honor']=='t') {$ck10='v';} else {$ck10='';}
|
|
if ($r3['hadir']=='t') {$ck11='v';} else {$ck11='';}
|
|
if ($r3['tor']=='t') {$ck12='v';} else {$ck12='';}
|
|
if ($r3['spt']=='t') {$ck13='v';} else {$ck13='';}
|
|
if ($r3['pendukung']=='t') {$ck14='v';} else {$ck14='';}
|
|
if ($r3['ba_um']=='t') {$ck15='v';} else {$ck15='';}
|
|
if ($r3['ba_ujicoba']=='t') {$ck16='v';} else {$ck16='';}
|
|
if ($r3['ba_pembayaran']=='t') {$ck17='v';} else {$ck17='';}
|
|
if ($r3['ba_kemajuan']=='t') {$ck18='v';} else {$ck18='';}
|
|
|
|
$objPHPExcel->getSheet(1)
|
|
->setCellValue('A'.$baris4, $ab)
|
|
->setCellValue('B'.$baris4, $r3[1])
|
|
->setCellValue('C'.$baris4, $r3[2])
|
|
->setCellValue('D'.$baris4, $r3['kd_panggil'])
|
|
->setCellValue('E'.$baris4, $rek)
|
|
->setCellValue('F'.$baris4, $r3[12])
|
|
->setCellValue('G'.$baris4, strtoupper($r3['kegiatan'].' - '.$r3['kegiatan_pengadaan'].''.$trm.' - '.$r3[12]))
|
|
->setCellValue('H'.$baris4, $r3['nilai_pelunasan'])
|
|
|
|
->setCellValue('I'.$baris4, $ck1)
|
|
->setCellValue('J'.$baris4, $ck2)
|
|
->setCellValue('K'.$baris4, $ck3)
|
|
->setCellValue('L'.$baris4, $ck4)
|
|
->setCellValue('M'.$baris4, $ck5)
|
|
->setCellValue('N'.$baris4, $ck6)
|
|
->setCellValue('O'.$baris4, $ck7)
|
|
->setCellValue('P'.$baris4, $ck8)
|
|
->setCellValue('Q'.$baris4, $ck9)
|
|
|
|
->setCellValue('R'.$baris4, $ck10)
|
|
->setCellValue('S'.$baris4, $ck11)
|
|
->setCellValue('T'.$baris4, $ck12)
|
|
->setCellValue('U'.$baris4, $ck13)
|
|
->setCellValue('V'.$baris4, $ck14)
|
|
->setCellValue('W'.$baris4, $ck15)
|
|
->setCellValue('X'.$baris4, $ck16)
|
|
->setCellValue('Y'.$baris4, $ck17)
|
|
->setCellValue('Z'.$baris4, $ck18)
|
|
|
|
->setCellValue('AA'.$baris4, $r3['no_sp'])
|
|
->setCellValue('AB'.$baris4, $tg);
|
|
|
|
$objPHPExcel->getSheet(1)->getStyle('A1:AB'.$baris4)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
|
|
$objPHPExcel->getSheet(1)->getStyle('I'.$baris4.':AB'.$baris4)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
|
|
$objPHPExcel->getSheet(1)->getStyle('H'.$baris4)->getNumberFormat()->setFormatCode('#,##0');
|
|
|
|
|
|
$barisz++;
|
|
$awal=$baris3;
|
|
$akhir=$baris4;
|
|
$objPHPExcel->getSheet(1)->setCellValue('H'.$barisz, '=SUM(H'.$awal.':H'.$akhir.')');
|
|
$objPHPExcel->getSheet(1)->getStyle('H'.$barisz)->getNumberFormat()->setFormatCode('#,##0');
|
|
|
|
$baris4=$baris4+$a3+1;
|
|
$a3++;
|
|
$ab++;
|
|
}
|
|
$objPHPExcel->getSheet(1)->getStyle('H'.$barisz)->applyFromArray($styleBold);
|
|
$baris2=$baris2+$a2+1+$a3;
|
|
$a++;
|
|
}
|
|
|
|
|
|
/* PROSES v3 =================================================================================================================================================== */
|
|
|
|
|
|
|
|
$objPHPExcel->createSheet();
|
|
$objPHPExcel->setActiveSheetIndex(2);
|
|
$objPHPExcel->getActiveSheet()->setTitle('LAP PROSES v3');
|
|
|
|
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('A')->setWidth(5);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('B')->setWidth(6);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('C')->setWidth(6);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('D')->setWidth(11);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('E')->setWidth(23.57);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('F')->setWidth(38.57);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('G')->setWidth(38.57);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('H')->setWidth(38);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('I')->setWidth(15.43);
|
|
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('J')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('K')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('L')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('M')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('N')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('O')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('P')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('Q')->setWidth(16);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('R')->setWidth(15);
|
|
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('S')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('T')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('U')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('V')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('W')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('X')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('Y')->setWidth(13);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('Z')->setWidth(16);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('AA')->setWidth(15);
|
|
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('AB')->setWidth(20);
|
|
$objPHPExcel->getSheet(2)->getColumnDimension('AC')->setWidth(25);
|
|
|
|
|
|
$styleBold = array('font' => array('bold' => true));
|
|
$objPHPExcel->getSheet(2)->getStyle('A4:AC4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
|
|
$objPHPExcel->getSheet(2)->getStyle('A4:AC4')->applyFromArray($styleBold);
|
|
$objPHPExcel->getSheet(2)->getRowDimension('1')->setRowHeight(15);
|
|
|
|
$objPHPExcel->getSheet(2)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
|
|
$objPHPExcel->getSheet(2)->setCellValue('A1', 'Tanggal Verif : '.$tm1.' s/d '.$ts1.' | '.$cari_verifikator);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A1:AC1')->getAlignment()->setWrapText(false);
|
|
|
|
$objPHPExcel->getSheet(2)->getStyle('A3:AC3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
|
|
$objPHPExcel->getActiveSheet()->getStyle('A3:AC3')->getAlignment()->setWrapText(true);
|
|
$objPHPExcel->getSheet(2)->getStyle('A3:AC3')->applyFromArray($styleBold);
|
|
|
|
$objPHPExcel->getSheet(2)
|
|
->setCellValue('A3','No')
|
|
->setCellValue('B3', 'KPA')
|
|
->setCellValue('C3', 'PPTK')
|
|
->setCellValue('D3', 'Kd Panggil')
|
|
->setCellValue('E3', 'Rekening')
|
|
|
|
->setCellValue('F3', 'Sub Kegiatan')
|
|
|
|
->setCellValue('G3', 'Nama Rekanan')
|
|
->setCellValue('H3', 'Kegiatan')
|
|
->setCellValue('I3', 'Nilai Kwitansi')
|
|
->setCellValue('J3', 'Kwitansi')
|
|
->setCellValue('K3', 'Faktur Penj')
|
|
->setCellValue('L3', 'Nota Dinas')
|
|
->setCellValue('M3', 'Faktur Pajak')
|
|
->setCellValue('N3', 'SSP PPN')
|
|
->setCellValue('O3', 'SSP PPH')
|
|
->setCellValue('P3', 'Ba HP Admin')
|
|
->setCellValue('Q3', 'Ba Serah Terima')
|
|
->setCellValue('R3', 'SP / SPK')
|
|
|
|
->setCellValue('S3', 'Penerimaan Honor')
|
|
->setCellValue('T3', 'Daftar Hadir')
|
|
->setCellValue('U3', 'TOR')
|
|
->setCellValue('V3', 'SK / SPT')
|
|
->setCellValue('W3', 'Pendukung')
|
|
->setCellValue('X3', 'Ba UM')
|
|
->setCellValue('Y3', 'Ba Ujicoba')
|
|
->setCellValue('Z3', 'Ba Pembayaran')
|
|
->setCellValue('AA3', 'Ba Kemajuan')
|
|
|
|
->setCellValue('AB3', 'No Kwi/SP/SPK')
|
|
->setCellValue('AC3', 'Periode Tgl SP/SPK');
|
|
|
|
|
|
|
|
$query2 = "SELECT DISTINCT s.sub_kegiatan
|
|
FROM pekerjaan p
|
|
INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
|
|
INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
|
|
INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
|
|
INNER JOIN kpa a1 ON (s.kpa_id=a1.id)
|
|
INNER JOIN ppkom a2 ON (s.ppkom_id=a2.id)
|
|
INNER JOIN pptk a3 ON (s.pptk_id=a3.id)
|
|
WHERE l.id IN ($vr_pelunasan_id)
|
|
";
|
|
$hasil2 = pg_query($query2);
|
|
$baris2 = 4;
|
|
$a2=1;
|
|
while ($r2 = pg_fetch_array($hasil2))
|
|
{
|
|
|
|
|
|
|
|
$baris3 = $baris2+$a2;
|
|
|
|
$skg=$r2[0];
|
|
$objPHPExcel->getActiveSheet()->getStyle('A'.$baris2.':AC'.$baris2)->getAlignment()->setWrapText(false);
|
|
$objPHPExcel->getSheet(2)->getStyle('A'.$baris2.':AC'.$baris2)->applyFromArray($styleBold);
|
|
$objPHPExcel->getSheet(2)->getStyle('A'.$baris2.':AC'.$baris2)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
|
|
$objPHPExcel->getSheet(2)->setCellValue('A'.$baris2, $r2[0]);
|
|
|
|
$query4 = "SELECT DISTINCT s.kegiatan
|
|
FROM pekerjaan p
|
|
INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
|
|
INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
|
|
INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
|
|
INNER JOIN kpa a1 ON (s.kpa_id=a1.id)
|
|
INNER JOIN ppkom a2 ON (s.ppkom_id=a2.id)
|
|
INNER JOIN pptk a3 ON (s.pptk_id=a3.id)
|
|
WHERE l.id IN ($vr_pelunasan_id)
|
|
";
|
|
$hasil4 = pg_query($query4);
|
|
$a4=0;
|
|
while ($r4 = pg_fetch_array($hasil4))
|
|
{
|
|
$kgz=$r4[0];
|
|
|
|
|
|
$query3 = "SELECT
|
|
s.sub_kegiatan,k.inisial,t.inisial,s.kd_panggil,s.ra,s.rb,s.rc,s.rd,s.re,s.rf,s.rg,s.rh,
|
|
COALESCE(n.nama,p.cad1),s.kegiatan,p.kegiatan_pengadaan,l.nilai_pelunasan,
|
|
l.kwitansi,l.faktur_penjualan,l.nota_dinas,l.faktur_pajak,l.ssp_ppn,l.ssp_pph,l.ba_hp_admin,l.ba_st,l.sp_spk,
|
|
p.no_sp,p.tgl_mulai_sp,p.tgl_selesai_sp,hadir,tor,spt,pendukung,ba_um,ba_ujicoba,ba_pembayaran,ba_kemajuan,
|
|
CASE WHEN r.st_termin='termin' THEN
|
|
l.data_ke
|
|
ELSE '0' END,r.st_termin,l.tgl_terima_verif
|
|
FROM pekerjaan p
|
|
INNER JOIN sub_kegiatan s ON (p.sub_kegiatan_id=s.id)
|
|
INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p.id)
|
|
INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
|
|
LEFT OUTER JOIN rekanan n ON (n.id=p.rekanan_id)
|
|
INNER JOIN kpa k ON (k.id=s.kpa_id)
|
|
INNER JOIN pptk t ON (t.id=s.pptk_id)
|
|
INNER JOIN ppkom m ON (m.id=s.ppkom_id)
|
|
WHERE s.thn='$thn' AND l.status_berkas IN ('FIX','PROSES') AND l.tanggal_setor IS NULL
|
|
AND s.sub_kegiatan='$skg' AND s.kegiatan='$kgz'
|
|
AND l.id IN ($vr_pelunasan_id)
|
|
";
|
|
$hasil3 = pg_query($query3);
|
|
$ab=1;
|
|
$a3=0;
|
|
while(($r3=pg_fetch_array($hasil3)) !== FALSE)
|
|
{
|
|
$baris4=$baris3+$a3+$a4;
|
|
$barisz=$baris3+$a3+$a4;
|
|
if ($r3[36]=='0') {$trm='';} else {$trm=' - '.strtoupper(($r3['st_termin'].' '.$r3[36]));}
|
|
|
|
if (($r3['tgl_mulai_sp']==null) || ($r3['tgl_mulai_sp']=='1970-01-01 00:00:00')) {$tg='';} else {
|
|
$tg=date('d-m-Y',strtotime($r3['tgl_mulai_sp'])).' s/d '.date('d-m-Y',strtotime($r3['tgl_selesai_sp'])); }
|
|
|
|
$rek=$r3['ra'].' '.$r3['rb'].' '.$r3['rc'].' '.$r3['rd'].' '.$r3['re'].' '.$r3['rf'].' '.$r3['rg'].' '.$r3['rh'];
|
|
|
|
if ($r3['kwitansi']=='t') {$ck1='v';} else {$ck1='';}
|
|
if ($r3['faktur_penjualan']=='t') {$ck2='v';} else {$ck2='';}
|
|
if ($r3['nota_dinas']=='t') {$ck3='v';} else {$ck3='';}
|
|
if ($r3['faktur_pajak']=='t') {$ck4='v';} else {$ck4='';}
|
|
if ($r3['ssp_ppn']=='t') {$ck5='v';} else {$ck5='';}
|
|
if ($r3['ssp_pph']=='t') {$ck6='v';} else {$ck6='';}
|
|
if ($r3['ba_hp_admin']=='t') {$ck7='v';} else {$ck7='';}
|
|
if ($r3['ba_st']=='t') {$ck8='v';} else {$ck8='';}
|
|
if ($r3['sp_spk']=='t') {$ck9='v';} else {$ck9='';}
|
|
|
|
if ($r3['honor']=='t') {$ck10='v';} else {$ck10='';}
|
|
if ($r3['hadir']=='t') {$ck11='v';} else {$ck11='';}
|
|
if ($r3['tor']=='t') {$ck12='v';} else {$ck12='';}
|
|
if ($r3['spt']=='t') {$ck13='v';} else {$ck13='';}
|
|
if ($r3['pendukung']=='t') {$ck14='v';} else {$ck14='';}
|
|
if ($r3['ba_um']=='t') {$ck15='v';} else {$ck15='';}
|
|
if ($r3['ba_ujicoba']=='t') {$ck16='v';} else {$ck16='';}
|
|
if ($r3['ba_pembayaran']=='t') {$ck17='v';} else {$ck17='';}
|
|
if ($r3['ba_kemajuan']=='t') {$ck18='v';} else {$ck18='';}
|
|
|
|
$objPHPExcel->getSheet(2)
|
|
->setCellValue('A'.$baris4, $ab)
|
|
->setCellValue('B'.$baris4, $r3[1])
|
|
->setCellValue('C'.$baris4, $r3[2])
|
|
->setCellValue('D'.$baris4, $r3['kd_panggil'])
|
|
->setCellValue('E'.$baris4, $rek)
|
|
->setCellValue('F'.$baris4, $r3['sub_kegiatan'])
|
|
->setCellValue('G'.$baris4, $r3[12])
|
|
->setCellValue('H'.$baris4, strtoupper($r3['kegiatan'].' - '.$r3['kegiatan_pengadaan'].''.$trm).' - '.$r3[12])
|
|
->setCellValue('I'.$baris4, $r3['nilai_pelunasan'])
|
|
|
|
->setCellValue('J'.$baris4, $ck1)
|
|
->setCellValue('K'.$baris4, $ck2)
|
|
->setCellValue('L'.$baris4, $ck3)
|
|
->setCellValue('M'.$baris4, $ck4)
|
|
->setCellValue('N'.$baris4, $ck5)
|
|
->setCellValue('O'.$baris4, $ck6)
|
|
->setCellValue('P'.$baris4, $ck7)
|
|
->setCellValue('Q'.$baris4, $ck8)
|
|
->setCellValue('R'.$baris4, $ck9)
|
|
|
|
->setCellValue('S'.$baris4, $ck10)
|
|
->setCellValue('T'.$baris4, $ck11)
|
|
->setCellValue('U'.$baris4, $ck12)
|
|
->setCellValue('V'.$baris4, $ck13)
|
|
->setCellValue('W'.$baris4, $ck14)
|
|
->setCellValue('X'.$baris4, $ck15)
|
|
->setCellValue('Y'.$baris4, $ck16)
|
|
->setCellValue('Z'.$baris4, $ck17)
|
|
->setCellValue('AA'.$baris4, $ck18)
|
|
|
|
->setCellValue('AB'.$baris4, $r3['no_sp'])
|
|
->setCellValue('AC'.$baris4, $tg);
|
|
|
|
$objPHPExcel->getSheet(2)->getStyle('A1:AC'.$baris4)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
|
|
$objPHPExcel->getSheet(2)->getStyle('J'.$baris4.':AC'.$baris4)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
|
|
$objPHPExcel->getSheet(2)->getStyle('I'.$baris4)->getNumberFormat()->setFormatCode('#,##0');
|
|
|
|
|
|
$barisz++;
|
|
$awal=$baris3;
|
|
$akhir=$baris4;
|
|
$objPHPExcel->getSheet(2)->setCellValue('F'.$barisz,$kgz);
|
|
$objPHPExcel->getSheet(2)->setCellValue('I'.$barisz, '=SUM(I'.$awal.':I'.$akhir.')');
|
|
$objPHPExcel->getSheet(2)->getStyle('I'.$barisz)->getNumberFormat()->setFormatCode('#,##0');
|
|
$a4++;
|
|
}
|
|
$baris4=$baris4+$a3+1+$a4;
|
|
$a3++;
|
|
$ab++;
|
|
}
|
|
$objPHPExcel->getSheet(2)->getStyle('I'.$barisz)->applyFromArray($styleBold);
|
|
$objPHPExcel->getSheet(2)->getStyle('F'.$barisz)->applyFromArray($styleBold);
|
|
$baris2=$baris2+$a2+1+$a3+$a4;
|
|
$a++;
|
|
}
|
|
|
|
|
|
header('Content-Type: application/vnd.ms-excel');
|
|
header('Content-Disposition: attachment;filename="lap_proses.xls"');
|
|
header('Cache-Control: max-age=0');
|
|
|
|
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
|
|
$objWriter->save('php://output');
|
|
exit;
|
|
|
|
?>
|
|
|
|
|