ryan.akbar.0107/simak
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
simak/app/rekap_hutang/excel_jm1.php
ryan dc9b19e59f add simak to git repo
2025-10-10 09:25:05 +07:00

255 lines
16 KiB
PHP
Raw Permalink Blame History

<?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.user_nama IS NULL 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.user_nama IS NULL 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.user_nama IS NULL 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.user_nama IS NULL 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.user_nama IS NULL AND p.tgl_setor IS NULL AND l.tgl_terima_verif IS NOT NULL AND st_setor_verif IS NOT NULL" : "
".($key == 'telu' ? "AND p.user_nama IS NULL AND l.tgl_terima_verif IS NULL AND st_setor_verif IS NOT NULL AND l.tanggal_lunas IS NULL" : "
".($key == 'limo' ? "AND p.user_nama IS NULL 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.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;
?>
Reference in New Issue View Git Blame Copy Permalink