ryan.akbar.0107/sppd
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
sppd/app/verifikasi/exceltgl.php
ryan cc020ada92 add sppd to git repo
2025-10-09 13:13:09 +07:00

609 lines
31 KiB
PHP
Raw Permalink Blame History

<?php
require_once '../../assets/excel/PHPExcel.php';
// koneksi ke database
include("../koneksi.php");
$thn=$_GET['thn'];
$b=strtoupper($_GET['b']);
$c=strtoupper($_GET['c']);
$codot=strtoupper($_GET['codot']);
$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));
$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(9);
$objPHPExcel->getDefaultStyle()->getAlignment()->setWrapText(true);
//$objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(0)->getColumnDimension('A')->setWidth(5);
$objPHPExcel->getSheet(0)->getColumnDimension('B')->setWidth(9.29);
$objPHPExcel->getSheet(0)->getColumnDimension('C')->setWidth(12.57);
$objPHPExcel->getSheet(0)->getColumnDimension('D')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('E')->setWidth(26);
$objPHPExcel->getSheet(0)->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getSheet(0)->getColumnDimension('G')->setWidth(40);
$objPHPExcel->getSheet(0)->getColumnDimension('H')->setWidth(16.86);
$objPHPExcel->getSheet(0)->getColumnDimension('I')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('J')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('K')->setWidth(7);
$objPHPExcel->getSheet(0)->getColumnDimension('L')->setWidth(20.71);
$objPHPExcel->getSheet(0)->getColumnDimension('M')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('N')->setWidth(17);
$objPHPExcel->getSheet(0)->getColumnDimension('O')->setWidth(12);
$objPHPExcel->getSheet(0)->getColumnDimension('P')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('Q')->setWidth(17);
$objPHPExcel->getSheet(0)->getColumnDimension('R')->setWidth(12);
$objPHPExcel->getSheet(0)->getColumnDimension('S')->setWidth(26);
$objPHPExcel->getSheet(0)->getColumnDimension('T')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('U')->setWidth(12.86);
$objPHPExcel->getSheet(0)->getColumnDimension('V')->setWidth(11.14);
$objPHPExcel->getSheet(0)->getColumnDimension('W')->setWidth(9);
$objPHPExcel->getSheet(0)->getColumnDimension('X')->setWidth(20.71);
$objPHPExcel->getSheet(0)->getColumnDimension('Y')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('Z')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AA')->setWidth(7);
$objPHPExcel->getSheet(0)->getColumnDimension('AB')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AC')->setWidth(26);
$objPHPExcel->getSheet(0)->getColumnDimension('AD')->setWidth(26);
$objPHPExcel->getSheet(0)->getColumnDimension('AE')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AF')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AG')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AH')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AI')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AJ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AK')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AL')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AM')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AN')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AO')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AP')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AQ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AR')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('AS')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AT')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AU')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AV')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AW')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('AX')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('AY')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('AZ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BA')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BB')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BC')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BD')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BE')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BF')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BG')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BH')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BI')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BJ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BK')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('BL')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BM')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BN')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BO')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BP')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BQ')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('BR')->setWidth(20);
$objPHPExcel->getSheet(0)->getColumnDimension('BS')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BT')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BU')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BV')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('BW')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('BX')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('BY')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('BZ')->setWidth(13);
$objPHPExcel->getSheet(0)->getColumnDimension('CA')->setWidth(30);
$objPHPExcel->getSheet(0)->getColumnDimension('CB')->setWidth(30);
$styleBold = array('font' => array('bold' => true));
$objPHPExcel->getSheet(0)->getStyle('A3:CB3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A4:CB4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A5:CB5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A3:CB3')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getStyle('A4:CB4')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getStyle('A5:CB5')->applyFromArray($styleBold);
$objPHPExcel->getSheet(0)->getRowDimension('1')->setRowHeight(15);
$objPHPExcel->getSheet(0)->setTitle('LAP DATA SETOR');
$objPHPExcel->getSheet(0)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getSheet(0)->setCellValue('A1', 'Tanggal Setor : '.$tm1.' s/d '.$ts1);
$objPHPExcel->getActiveSheet()->getStyle('A1:CB1')->getAlignment()->setWrapText(false);
$objPHPExcel->getActiveSheet()->mergeCells('AE3:AY3');
$objPHPExcel->getActiveSheet()->mergeCells('AE4:AM4');
$objPHPExcel->getActiveSheet()->mergeCells('AN4:AY4');
$objPHPExcel->getSheet(0)->setCellValue('AE3', 'BERANGKAT');
$objPHPExcel->getSheet(0)->setCellValue('AE4', 'BIAYA');
$objPHPExcel->getSheet(0)->setCellValue('AN4', 'TRANSPORT');
$objPHPExcel->getActiveSheet()->mergeCells('AZ3:BV3');
$objPHPExcel->getActiveSheet()->mergeCells('AZ4:BF4');
$objPHPExcel->getActiveSheet()->mergeCells('BG4:BR4');
$objPHPExcel->getActiveSheet()->mergeCells('BS4:BV4');
$objPHPExcel->getSheet(0)->setCellValue('AZ3', 'PULANG');
$objPHPExcel->getSheet(0)->setCellValue('AZ4', 'BIAYA');
$objPHPExcel->getSheet(0)->setCellValue('BG4', 'TRANSPORT');
$objPHPExcel->getSheet(0)->setCellValue('BS4', 'PENGINAPAN');
$objPHPExcel->getActiveSheet()->mergeCells('A3:A5');
$objPHPExcel->getActiveSheet()->mergeCells('B3:B5');
$objPHPExcel->getActiveSheet()->mergeCells('C3:C5');
$objPHPExcel->getActiveSheet()->mergeCells('D3:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E3:E5');
$objPHPExcel->getActiveSheet()->mergeCells('F3:F5');
$objPHPExcel->getActiveSheet()->mergeCells('G3:G5');
$objPHPExcel->getActiveSheet()->mergeCells('H3:H5');
$objPHPExcel->getActiveSheet()->mergeCells('I3:I5');
$objPHPExcel->getActiveSheet()->mergeCells('J3:J5');
$objPHPExcel->getActiveSheet()->mergeCells('K3:K5');
$objPHPExcel->getActiveSheet()->mergeCells('L3:L5');
$objPHPExcel->getActiveSheet()->mergeCells('M3:M5');
$objPHPExcel->getActiveSheet()->mergeCells('N3:N5');
$objPHPExcel->getActiveSheet()->mergeCells('O3:O5');
$objPHPExcel->getActiveSheet()->mergeCells('P3:P5');
$objPHPExcel->getActiveSheet()->mergeCells('Q3:Q5');
$objPHPExcel->getActiveSheet()->mergeCells('R3:R5');
$objPHPExcel->getActiveSheet()->mergeCells('S3:S5');
$objPHPExcel->getActiveSheet()->mergeCells('T3:T5');
$objPHPExcel->getActiveSheet()->mergeCells('U3:U5');
$objPHPExcel->getActiveSheet()->mergeCells('V3:V5');
$objPHPExcel->getActiveSheet()->mergeCells('W3:W5');
$objPHPExcel->getActiveSheet()->mergeCells('X3:X5');
$objPHPExcel->getActiveSheet()->mergeCells('Y3:Y5');
$objPHPExcel->getActiveSheet()->mergeCells('Z3:Z5');
$objPHPExcel->getActiveSheet()->mergeCells('AA3:AA5');
$objPHPExcel->getActiveSheet()->mergeCells('AB3:AB5');
$objPHPExcel->getActiveSheet()->mergeCells('AC3:AC5');
$objPHPExcel->getActiveSheet()->mergeCells('AD3:AD5');
//$objPHPExcel->getSheet(0)->getStyle('AA')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
//$objPHPExcel->getActiveSheet()->setCellValueExplicit('AA14', ' ', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getSheet(0)
->setCellValue('A3', 'No')
->setCellValue('B3', 'No DPA')
->setCellValue('C3', 'Kd Panggil')
->setCellValue('D3', 'Sub')
->setCellValue('E3', 'Rekening')
->setCellValue('F3', 'Kd Sibaku')
->setCellValue('G3', 'Uraian')
->setCellValue('H3', 'Nilai Kwitansi')
->setCellValue('I3', 'PPN')
->setCellValue('J3', 'PPH')
->setCellValue('K3', 'Pasal')
->setCellValue('L3', 'No Kwi/SP/SPK')
->setCellValue('M3', 'No Faktur')
->setCellValue('N3', 'BAST(P)')
->setCellValue('O3', 'Tgl')
->setCellValue('P3', 'Nominal')
->setCellValue('Q3', 'BA HP Admin')
->setCellValue('R3', 'Tgl')
->setCellValue('S3', 'Periode Tgl SP/SPK')
->setCellValue('T3', 'Jam Setor')
->setCellValue('U3', 'Bulan Setor')
->setCellValue('V3', 'Tgl Dok')
->setCellValue('W3', 'St Buku')
->setCellValue('X3', 'No SPT')
->setCellValue('Y3', 'Berangkat')
->setCellValue('Z3', 'Kembali')
->setCellValue('AA3', 'Lama')
->setCellValue('AB3', 'Setor Berkas')
->setCellValue('AC3', 'Tujuan')
->setCellValue('AD3', 'NIP/NIPTT')
->setCellValue('AE5', 'Uang Harian')
->setCellValue('AF5', 'Representasi')
->setCellValue('AG5', 'Penginapan')
->setCellValue('AH5', 'Sewa Kendaraan')
->setCellValue('AI5', 'BBM')
->setCellValue('AJ5', 'Tol')
->setCellValue('AK5', 'Bantrans')
->setCellValue('AL5', 'Transportasi')
->setCellValue('AM5', 'PCR')
->setCellValue('AN5', 'Moda')
->setCellValue('AO5', 'Penyewa')
->setCellValue('AP5', 'Nopol')
->setCellValue('AQ5', 'Pesawat/KA')
->setCellValue('AR5', 'No Tiket')
->setCellValue('AS5', 'No Flight')
->setCellValue('AT5', 'Jam')
->setCellValue('AU5', 'Kode Booking')
->setCellValue('AV5', 'No Kursi')
->setCellValue('AW5', 'Tanggal')
->setCellValue('AX5', 'Asal')
->setCellValue('AY5', 'Tujuan')
->setCellValue('AZ5', 'Uang Harian')
->setCellValue('BA5', 'Representasi')
->setCellValue('BB5', 'Sewa Kendaraan')
->setCellValue('BC5', 'BBM')
->setCellValue('BD5', 'Tol')
->setCellValue('BE5', 'Bantrans')
->setCellValue('BF5', 'PCR')
->setCellValue('BG5', 'Moda')
->setCellValue('BH5', 'Penyewa')
->setCellValue('BI5', 'Nopol')
->setCellValue('BJ5', 'Pesawat/KA')
->setCellValue('BK5', 'No Tiket')
->setCellValue('BL5', 'No Flight')
->setCellValue('BM5', 'Jam')
->setCellValue('BN5', 'Kode Booking')
->setCellValue('BO5', 'No Kursi')
->setCellValue('BP5', 'Tanggal')
->setCellValue('BQ5', 'Asal')
->setCellValue('BR5', 'Tujuan')
->setCellValue('BS5', 'Nama Penginapan')
->setCellValue('BT5', 'Tarif/Hari')
->setCellValue('BU5', 'Lama')
->setCellValue('BV5', 'Total')
->setCellValue('BW5', 'Golongan')
->setCellValue('BX5', 'Satker')
->setCellValue('BY5', 'Penginput')
->setCellValue('BZ5', 'Tgl Lunas')
->setCellValue('CA5', 'Nama');
//$pecah=explode(',',$azx);
//foreach($pecah as $selected) {
$query="SELECT DISTINCT k2.no_angg,k2.kd_panggil,k2.sub_kegiatan,k2.ra,k2.rb,k2.rc,k2.rd,k2.re,k2.rf,k2.rg,k2.rh,k2.kd_sibaku,k2.kegiatan,
p2.kegiatan_pengadaan,l.tanggal_setor,
l.nilai_pelunasan,l.ppn,l.pph_21,l.pph_22,l.pph_23,l.pph_final,
p2.no_sp,l.no_faktur,p2.tgl_mulai_sp,p2.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,
COALESCE(p.nama,g.nama_non),
l.tanggal_dokumen,r.st_buku,
t.no_surat,t.tgl_berangkat,t.tgl_kembali,t.untuk,t.daerah_tujuan,x.nama,p.nipb,t.lama,g.id,p.golongan,p.ruang,k.pembuat,s.nama,l.tanggal_lunas
FROM sp_kwi k
INNER JOIN sp_rek u ON (k.sp_rek_id=u.id)
INNER JOIN sub_kegiatan k2 ON (k2.id=u.sub_kegiatan_id)
INNER JOIN sp_spt t ON (t.sp_kwi_id=k.id)
INNER JOIN sp_peg g ON (g.sp_spt_id=t.id)
LEFT OUTER JOIN data_pegawai p ON (g.data_pegawai_id=p.id)
LEFT OUTER JOIN satuan_kerja s ON (p.satuan_kerja=s.id)
INNER JOIN pekerjaan p2 ON (p2.id=g.pekerjaan_id)
INNER JOIN vr_realisasi r ON (r.pekerjaan_id=p2.id)
INNER JOIN vr_pelunasan l ON (l.vr_realisasi_id=r.id)
LEFT OUTER JOIN rekanan n ON (n.id=p2.rekanan_id)
LEFT OUTER JOIN jabatan_pegawai x ON (x.id=p.jabatan_pegawai_id)
WHERE g.tgl_setor_verif::DATE BETWEEN '$tt1-$bb1-$hh1' AND '$tt2-$bb2-$hh2'
".($c == 'ALL' ? "" : " AND k.sp_rek_id='$c'")."
".($codot == 'ALL' ? "AND g.status IS NOT NULL" : "AND g.status IS NULL")."
ORDER BY no_sp ASC
";
$hasil = pg_query($query);
$baris = 6;
$a=1;
while (($r= pg_fetch_array($hasil)) !== FALSE) {
$peg_satker=$r[46];
$pembuat=$r[45];
$sp_peg_id=$r[42];
$gol=$r['golongan'].'/'.$r['ruang'];
if ($gol=='-/-') {$golpeg='PTT-PK';} else {$golpeg=$gol;}
/*B-BIAYA*/
$bb = pg_query("SELECT * FROM sp_biaya WHERE sp_peg_id='$sp_peg_id' AND jenis='B'");
while (($rowbb= pg_fetch_array($bb)) !== FALSE) {
$uh_bb=$rowbb['uang_harian']*$rowbb['lama'];
$rep_bb=$rowbb['representasi']*$rowbb['lama'];
$penginapan_bb=$rowbb['penginapan_br']*$rowbb['lama_inap_br'];
$sewa_kendaraan_bb=$rowbb['sewa_kendaraan'];
$bbm_bb=$rowbb['bbm'];
$tol_bb=$rowbb['tol'];
$bantrans_bb=$rowbb['bantrans']*$rowbb['lama_bt'];
$trans_bb=$rowbb['trans_br'];
$pcr_bb=$rowbb['pcr'];
}
/*B-TRANSPORT*/
$bt = pg_query("SELECT * FROM sp_transportasi WHERE sp_peg_id='$sp_peg_id' AND jenis='B'");
while (($rowbt= pg_fetch_array($bt)) !== FALSE) {
$moda_bt=$rowbt['moda'];
$penyewa_bt=$rowbt['penyewa'];
$no_polisi_bt=$rowbt['no_polisi'];
$pesawat_ka_bt=$rowbt['pesawat_ka'];
$no_tiket_bt=$rowbt['no_tiket'];
$no_flight_bt=$rowbt['no_flight'];
$jam_bt=$rowbt['jam'];
$kode_booking_bt=$rowbt['kode_booking'];
$no_tempat_duduk_bt=$rowbt['no_tempat_duduk'];
if (($rowbt['tanggal']==null) || ($rowbt['tanggal']=='1970-01-01 00:00:00')) {$tanggal_bt='';} else {$tanggal_bt=date('d-m-Y',strtotime($rowbt['tanggal'])); }
$asal_bt=$rowbt['asal'];
$tujuan_bt=$rowbt['tujuan'];
}
/*P-BIAYA*/
$pb = pg_query("SELECT * FROM sp_biaya WHERE sp_peg_id='$sp_peg_id' AND jenis='P'");
while (($rowpb= pg_fetch_array($pb)) !== FALSE) {
$uh_pb=$rowpb['uang_harian']*$rowpb['lama'];
$rep_pb=$rowpb['representasi']*$rowpb['lama'];
$sewa_kendaraan_pb=$rowpb['sewa_kendaraan'];
$bbm_pb=$rowpb['bbm'];
$tol_pb=$rowpb['tol'];
$bantrans_pb=$rowpb['bantrans']*$rowpb['lama_bt'];
$pcr_pb=$rowpb['pcr'];
}
/*P-TRANSPORT*/
$pt = pg_query("SELECT * FROM sp_transportasi WHERE sp_peg_id='$sp_peg_id' AND jenis='P'");
while (($rowpt= pg_fetch_array($pt)) !== FALSE) {
$moda_pt=$rowpt['moda'];
$penyewa_pt=$rowpt['penyewa'];
$no_polisi_pt=$rowpt['no_polisi'];
$pesawat_ka_pt=$rowpt['pesawat_ka'];
$no_tiket_pt=$rowpt['no_tiket'];
$no_flight_pt=$rowpt['no_flight'];
$jam_pt=$rowpt['jam'];
$kode_booking_pt=$rowpt['kode_booking'];
$no_tempat_duduk_pt=$rowpt['no_tempat_duduk'];
if (($rowpt['tanggal']==null) || ($rowpt['tanggal']=='1970-01-01 00:00:00')) {$tanggal_pt='';} else {$tanggal_pt=date('d-m-Y',strtotime($rowpt['tanggal'])); }
$asal_pt=$rowpt['asal'];
$tujuan_pt=$rowpt['tujuan'];
}
/*PENGINAPAN*/
$pn = pg_query("SELECT id,sp_peg_id,nama_penginapan,penginapan,lama FROM sp_penginapan WHERE sp_peg_id='$sp_peg_id'");
while (($rowpn= pg_fetch_array($pn)) !== FALSE) {
$nama_pn=$rowpn['nama_penginapan'];
$tarif_pn=$rowpn['penginapan'];
$lama_pn=$rowpn['lama'];
}
if (($r['tgl_berangkat']==null) || ($r['tgl_berangkat']=='1970-01-01 00:00:00')) {
$tbrk="-";} else {
$tbrk=date('d-m-Y',strtotime($r['tgl_berangkat']));
}
if (($r['tgl_kembali']==null) || ($r['tgl_kembali']=='1970-01-01 00:00:00')) {
$tkbl="-";} else {
$tkbl=date('d-m-Y',strtotime($r['tgl_kembali']));
}
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'])); }
if (($r['tanggal_setor']=="01-01-1970") || ($r['tanggal_setor']==NULL) || ($r['tanggal_setor']=="")) {
$trl="-";} else {
$trl=date('d-m-Y',strtotime($r['tanggal_setor']));
}
if (($r['tanggal_lunas']=="01-01-1970") || ($r['tanggal_lunas']==NULL) || ($r['tanggal_lunas']=="")) {
$tlns="-";} else {
$tlns=date('d-m-Y',strtotime($r['tanggal_lunas']));
}
$rek=$r['ra'].' '.$r['rb'].' '.$r['rc'].' '.$r['rd'].' '.$r['re'].' '.$r['rf'].' '.$r['rg'].' '.$r['rh'];
if ((isset($r['pph_21'])) && ($r['pph_21']!=0)) {
$pph=$r['pph_21'];
$pasal='21';
} else
if ((isset($r['pph_22'])) && ($r['pph_22']!=0)) {
$pph=$r['pph_22'];
$pasal='22';
} else
if ((isset($r['pph_23'])) && ($r['pph_23']!=0)) {
$pph=$r['pph_23'];
$pasal='23';
} else
if ((isset($r['pph_final'])) && ($r['pph_final']!=0)) {
$pph=$r['pph_final'];
$pasal='FNL';
}
$objPHPExcel->getSheet(0)
->setCellValue('A'.$baris, $a)
->setCellValue('B'.$baris, $r[0])
->setCellValue('C'.$baris, $r[1])
->setCellValue('D'.$baris, $r[2])
->setCellValue('E'.$baris, $rek)
->setCellValue('F'.$baris, $r['kd_sibaku'])
->setCellValue('G'.$baris, strtoupper($r['kegiatan']).'##'.$r['untuk'].'##'.$r[31].'##'.$r['nama'])
->setCellValue('H'.$baris, $r['nilai_pelunasan'])
->setCellValue('I'.$baris, $r['ppn'])
->setCellValue('J'.$baris, $pph)
->setCellValue('K'.$baris, $pasal)
->setCellValue('L'.$baris, $r['no_sp'])
->setCellValue('M'.$baris, $r['no_faktur']);
/* BA Serah Terima */
$pekerjaan_id=$r['pekerjaan_id'];
$query_bast = "SELECT nomor_ba,tanggal_ba,nilai_ba FROM riwayat_ba WHERE pekerjaan_id='$pekerjaan_id' AND berita_acara_id=2 ORDER BY tanggal_ba ASC";
$hasil_bast = pg_query($query_bast);
$rows = pg_num_rows($hasil_bast);
if ($rows==0) {} else {
$baris_bast = $baris;
$z = 0;
while (($r_bast= pg_fetch_array($hasil_bast)) !== FALSE) {
$set=$baris_bast+$z;
$objPHPExcel->getSheet(0)
->setCellValue('N'.$set, $r_bast['nomor_ba'])
->setCellValue('O'.$set, date('d-m-Y',strtotime($r_bast['tanggal_ba'])))
->setCellValue('P'.$set, $r_bast['nilai_ba']);
$objPHPExcel->getSheet(0)->getStyle('P'.$set)->getNumberFormat()->setFormatCode('#,##0');
$z++;
}
$awal=$baris;
$akhir=$set;
$barisz=$set+1;
$objPHPExcel->getSheet(0)->setCellValue('P'.$barisz, '=SUM(P'.$awal.':P'.$akhir.')');
$objPHPExcel->getSheet(0)->getStyle('P'.$barisz)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('P'.$barisz)->applyFromArray($styleBold);
}
/* ====== end of BA Serah Terima ====== */
/* BA HP Admin */
$pekerjaan_id=$r['pekerjaan_id'];
$query_bahp = "SELECT nomor_ba,tanggal_ba,nilai_ba FROM riwayat_ba WHERE pekerjaan_id='$pekerjaan_id' AND berita_acara_id=1 ORDER BY tanggal_ba ASC";
$hasil_bahp = pg_query($query_bahp);
$baris_bahp = $baris;
$zhp = 0;
while (($r_bahp= pg_fetch_array($hasil_bahp)) !== FALSE) {
$sethp=$baris_bahp+$zhp;
$objPHPExcel->getSheet(0)
->setCellValue('Q'.$sethp, $r_bahp['nomor_ba'])
->setCellValue('R'.$sethp, date('d-m-Y',strtotime($r_bahp['tanggal_ba'])));
$zhp++;
}
/* ====== end of BA HP Admin ====== */
$objPHPExcel->getSheet(0)
->setCellValue('S'.$baris, $tg)
->setCellValue('T'.$baris, date('h:s',strtotime($r['tanggal_setor'])))
->setCellValue('U'.$baris, date('M-Y',strtotime($r['tanggal_setor'])))
->setCellValue('V'.$baris, date('d-m-Y',strtotime($r['tanggal_input'])))
->setCellValue('W'.$baris, $r['st_buku'])
->setCellValue('X'.$baris, $r['no_surat'])
->setCellValue('Y'.$baris, $tbrk)
->setCellValue('Z'.$baris, $tkbl)
->setCellValue('AA'.$baris, $r['lama'])
->setCellValue('AB'.$baris, date('d-m-Y',strtotime($r['tanggal_setor'])))
->setCellValue('AC'.$baris, $r['daerah_tujuan'])
->setCellValueExplicit('AD'.$baris, $r['nipb'], PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValue('AE'.$baris, $uh_bb)
->setCellValue('AF'.$baris, $rep_bb)
->setCellValue('AG'.$baris, $penginapan_bb)
->setCellValue('AH'.$baris, $sewa_kendaraan_bb)
->setCellValue('AI'.$baris, $bbm_bb)
->setCellValue('AJ'.$baris, $tol_bb)
->setCellValue('AK'.$baris, $bantrans_bb)
->setCellValue('AL'.$baris, $trans_bb)
->setCellValue('AM'.$baris, $pcr_bb)
->setCellValue('AN'.$baris, $moda_bt)
->setCellValue('AO'.$baris, $penyewa_bt)
->setCellValue('AP'.$baris, $no_polisi_bt)
->setCellValue('AQ'.$baris, $pesawat_ka_bt)
->setCellValueExplicit('AR'.$baris, $no_tiket_bt, PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValue('AS'.$baris, $no_flight_bt)
->setCellValue('AT'.$baris, $jam_bt)
->setCellValue('AU'.$baris, $kode_booking_bt)
->setCellValue('AV'.$baris, $no_tempat_duduk_bt)
->setCellValue('AW'.$baris, $tanggal_bt)
->setCellValue('AX'.$baris, $asal_bt)
->setCellValue('AY'.$baris, $tujuan_bt)
->setCellValue('AZ'.$baris, $uh_pb)
->setCellValue('BA'.$baris, $rep_pb)
->setCellValue('BB'.$baris, $sewa_kendaraan_pb)
->setCellValue('BC'.$baris, $bbm_pb)
->setCellValue('BD'.$baris, $tol_pb)
->setCellValue('BE'.$baris, $bantrans_pb)
->setCellValue('BF'.$baris, $pcr_pb)
->setCellValue('BG'.$baris, $moda_pt)
->setCellValue('BH'.$baris, $penyewa_pt)
->setCellValue('BI'.$baris, $no_polisi_pt)
->setCellValue('BJ'.$baris, $pesawat_ka_pt)
->setCellValueExplicit('BK'.$baris, $no_tiket_pt, PHPExcel_Cell_DataType::TYPE_STRING)
->setCellValue('BL'.$baris, $no_flight_pt)
->setCellValue('BM'.$baris, $jam_pt)
->setCellValue('BN'.$baris, $kode_booking_pt)
->setCellValue('BO'.$baris, $no_tempat_duduk_pt)
->setCellValue('BP'.$baris, $tanggal_pt)
->setCellValue('BQ'.$baris, $asal_pt)
->setCellValue('BR'.$baris, $tujuan_pt)
->setCellValue('BS'.$baris, $nama_pn)
->setCellValue('BT'.$baris, $tarif_pn)
->setCellValue('BU'.$baris, $lama_pn)
->setCellValue('BV'.$baris, $tarif_pn*$lama_pn)
->setCellValue('BW'.$baris, $golpeg)
->setCellValue('BX'.$baris, $peg_satker)
->setCellValue('BY'.$baris, $pembuat)
->setCellValue('BZ'.$baris, $tlns)
->setCellValue('CA'.$baris, $r[31]);
/*
$objPHPExcel->getSheet(0)->getStyle('A3:A'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A1:CA'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('H'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('I'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('J'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('AE6:AM'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('AZ6:BF'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('BT6:BV'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('AD'.$baris)->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(0)->getStyle('Y'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('Z'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('AA'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('AB'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('AN6:AY'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('BG6:BR'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('BU'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('BY'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('BZ'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); */
$a++;
if ($z > $zhp) {$ape=$z;} else {$ape=$zhp;}
$baris=$baris+$ape+1;
}
$objPHPExcel->getSheet(0)->getStyle('A3:A'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('A1:CA'.$baris)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('H6:H'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('I6:I'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('J6:J'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('AE6:AM'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('AZ6:BF'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('BT6:BV'.$baris)->getNumberFormat()->setFormatCode('#,##0');
$objPHPExcel->getSheet(0)->getStyle('AD6:AD'.$baris)->getNumberFormat()->setFormatCode('@');
$objPHPExcel->getSheet(0)->getStyle('Y6:Y'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('Z6:Z'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('AA6:AA'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('AB6:AB'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('AN6:AY'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('BG6:BR'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('BU6:BU'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('BY6:BY'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getSheet(0)->getStyle('BZ6:BZ'.$baris)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$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);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="lap_data_setor.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