290 lines
15 KiB
PHP
290 lines
15 KiB
PHP
|
<?php
|
||
|
|
|
||
|
|
namespace App\Exports;
|
||
|
|
|
||
|
|
use App\Model\Audit\AuditFindingModel;
|
||
|
|
use Maatwebsite\Excel\Concerns\FromCollection;
|
||
|
|
use Maatwebsite\Excel\Concerns\Exportable;
|
||
|
|
use Maatwebsite\Excel\Concerns\WithHeadings;
|
||
|
|
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
|
||
|
|
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
|
||
|
|
use Maatwebsite\Excel\Concerns\WithEvents;
|
||
|
|
use Maatwebsite\Excel\Events\AfterSheet;
|
||
|
|
use Maatwebsite\Excel\Events\BeforeExport;
|
||
|
|
|
||
|
|
use Illuminate\Contracts\View\View;
|
||
|
|
use Maatwebsite\Excel\Concerns\FromView;
|
||
|
|
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
|
||
|
|
use PhpOffice\PhpSpreadsheet\Style\Border;
|
||
|
|
use PhpOffice\PhpSpreadsheet\Style\Alignment;
|
||
|
|
use Maatwebsite\Excel\Sheet;
|
||
|
|
|
||
|
|
class IcsExport implements WithColumnFormatting,FromView,WithEvents
|
||
|
|
{
|
||
|
|
public function __construct($t_mulai,$t_selesai,$persiapan,$jenis_cetak)
|
||
|
|
{
|
||
|
|
$this->t_mulai = $t_mulai;
|
||
|
|
$this->t_selesai = $t_selesai;
|
||
|
|
$this->persiapan = $persiapan;
|
||
|
|
$this->jenis_cetak = $jenis_cetak;
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
public function view(): View
|
||
|
|
{
|
||
|
|
// KCP
|
||
|
|
if ($this->jenis_cetak == 1) {
|
||
|
|
return view('export.xlsx_cetak_risk_register', [
|
||
|
|
't_mulai' => $this->t_mulai,
|
||
|
|
't_selesai' => $this->t_selesai,
|
||
|
|
'persiapan' => $this->persiapan
|
||
|
|
]);
|
||
|
|
}else{
|
||
|
|
return view('export.xlsx_cetak_formula', [
|
||
|
|
't_mulai' => $this->t_mulai,
|
||
|
|
't_selesai' => $this->t_selesai,
|
||
|
|
'persiapan' => $this->persiapan
|
||
|
|
]);
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
public function columnFormats(): array
|
||
|
|
{
|
||
|
|
return [
|
||
|
|
'Z' => Border::BORDER_MEDIUM
|
||
|
|
];
|
||
|
|
}
|
||
|
|
|
||
|
|
public function registerEvents(): array
|
||
|
|
{
|
||
|
|
|
||
|
|
return [
|
||
|
|
AfterSheet::class => function(AfterSheet $event) {
|
||
|
|
|
||
|
|
$t_mulai = date('Y-m-d',strtotime($this->t_mulai));
|
||
|
|
$t_selesai = date('Y-m-d',strtotime($this->t_selesai));
|
||
|
|
$persiapan = $this->persiapan;
|
||
|
|
|
||
|
|
$penugasan = $persiapan;
|
||
|
|
if ($penugasan == "semua") {
|
||
|
|
$t_mulai = date('Y-m-d',strtotime($t_mulai));
|
||
|
|
$t_selesai = date('Y-m-d',strtotime($t_selesai));
|
||
|
|
$filter_penugasan = "";
|
||
|
|
}else{
|
||
|
|
$date_penugasan = collect(\DB::select("SELECT audit_tittle, ma.start_date, ma.end_date FROM master_audit_job maj
|
||
|
|
join master_audit_plan ma on ma.id = maj.audit_plan_id
|
||
|
|
where maj.id = ".$penugasan))->first();
|
||
|
|
|
||
|
|
$t_mulai = $date_penugasan->start_date;
|
||
|
|
$t_selesai = $date_penugasan->end_date;
|
||
|
|
$filter_penugasan = " and audit_job_id = ".$penugasan;
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
////////////////////KCP
|
||
|
|
if ($this->jenis_cetak == 1) {
|
||
|
|
|
||
|
|
$d_sebab = collect(\DB::select("SELECT * FROM reff_aspek order by code"))->count();
|
||
|
|
$d_bidang = collect(\DB::select("SELECT DISTINCT ON (rsca.definition ) rsca.definition as bidang,ri.bidang_ics
|
||
|
|
FROM master_audit_finding maf
|
||
|
|
join reff_sub_class_audit rsca on rsca.id = maf.id_program
|
||
|
|
join reff_ics ri on ri.id = maf.ics where finding_date >= '".$t_mulai."' and finding_date <= '".$t_selesai."'".$filter_penugasan))->count();
|
||
|
|
|
||
|
|
$d_bidang_c = \DB::select("SELECT DISTINCT ON (rsca.definition ) rsca.definition as bidang,ri.bidang_ics
|
||
|
|
FROM master_audit_finding maf
|
||
|
|
join reff_sub_class_audit rsca on rsca.id = maf.id_program
|
||
|
|
join reff_ics ri on ri.id = maf.ics");
|
||
|
|
|
||
|
|
$count_isc = 0;
|
||
|
|
$count_data = 0;
|
||
|
|
|
||
|
|
foreach ($d_bidang_c as $db) {
|
||
|
|
|
||
|
|
|
||
|
|
$d_isc = \DB::select("SELECT DISTINCT ON(bidang_ics) rsca.definition as bidang,ri.bidang_ics
|
||
|
|
FROM master_audit_finding maf
|
||
|
|
join reff_sub_class_audit rsca on rsca.id = maf.id_program
|
||
|
|
join reff_ics ri on ri.id = maf.ics
|
||
|
|
where rsca.definition = '".$db->bidang."' and finding_date >= '".$t_mulai."' and finding_date <= '".$t_selesai."'".$filter_penugasan);
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
foreach ($d_isc as $di) {
|
||
|
|
$count_isc++;
|
||
|
|
|
||
|
|
$data = \DB::select("SELECT maf.* ,rsca.definition as bidang,ri.bidang_ics, ra.code as sebab_code
|
||
|
|
FROM master_audit_finding maf
|
||
|
|
join reff_sub_class_audit rsca on rsca.id = maf.id_program
|
||
|
|
join reff_ics ri on ri.id = maf.ics
|
||
|
|
join reff_aspek ra on ra.id = maf.jenis_sebab
|
||
|
|
where ri.bidang_ics = '".$di->bidang_ics."' and finding_date >= '".$t_mulai."' and finding_date <= '".$t_selesai."'".$filter_penugasan);
|
||
|
|
|
||
|
|
foreach ($data as $d) {
|
||
|
|
$count_data++;
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
$total_row = $d_sebab+$count_isc+$count_data+($d_bidang*3)+7+6;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getStyle('A7:W9')
|
||
|
|
->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
|
||
|
|
|
||
|
|
$styleArray = [
|
||
|
|
'borders' => [
|
||
|
|
'allBorders' => [
|
||
|
|
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
|
||
|
|
'color' => ['argb' => '000000'],
|
||
|
|
]
|
||
|
|
]
|
||
|
|
];
|
||
|
|
$event->sheet->getDelegate()->getStyle('A7:W'.$total_row)->applyFromArray($styleArray);
|
||
|
|
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('A')->setWidth(5.7);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('B')->setWidth(3);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('C')->setWidth(3);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('D')->setWidth(52);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('E')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('F')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('G')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('H')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('I')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('J')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('K')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('L')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('M')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('N')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('O')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('P')->setWidth(5);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('S')->setWidth(10);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('T')->setWidth(20);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('U')->setWidth(10);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('V')->setWidth(50);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('W')->setWidth(20);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getStyle('A1:W500')
|
||
|
|
->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getStyle('A1:W500')->getFont()->setSize(10);
|
||
|
|
$event->sheet->getDelegate()->getStyle('A7:W8')->getFont()->setBold(true);
|
||
|
|
$event->sheet->getDelegate()->getStyle('A1:W500')->getFont()->setName('Arial');
|
||
|
|
$event->sheet->getDelegate()->getStyle('D1:D500')->getAlignment()->setWrapText(true);
|
||
|
|
$event->sheet->getDelegate()->getStyle('V1:V500')->getAlignment()->setWrapText(true);
|
||
|
|
$event->sheet->getDelegate()->getStyle('T8')->getAlignment()->setWrapText(true);
|
||
|
|
$event->sheet->getDelegate()->getStyle('W7')->getAlignment()->setWrapText(true);
|
||
|
|
|
||
|
|
}
|
||
|
|
////////////////////FORMULA
|
||
|
|
else{
|
||
|
|
|
||
|
|
|
||
|
|
$d_init_bobot = \DB::select("SELECT DISTINCT ON (bobot.nama) rsca.definition as bidang,rsca.id as kode_bidang,ri.bidang_ics,bobot.*
|
||
|
|
FROM master_audit_finding maf
|
||
|
|
join reff_sub_class_audit rsca on rsca.id = maf.id_program
|
||
|
|
join reff_ics ri on ri.id = maf.ics
|
||
|
|
join bobot on bobot.id = rsca.id_bobot
|
||
|
|
where finding_date >= '".$t_mulai."' and finding_date <= '".$t_selesai."'".$filter_penugasan);
|
||
|
|
|
||
|
|
$d_bidang = \DB::select("SELECT DISTINCT ON (rsca.definition ) rsca.definition as bidang,rsca.id as kode_bidang,ri.bidang_ics
|
||
|
|
FROM master_audit_finding maf
|
||
|
|
join reff_sub_class_audit rsca on rsca.id = maf.id_program
|
||
|
|
join reff_ics ri on ri.id = maf.ics where finding_date >= '".$t_mulai."' and finding_date <= '".$t_selesai."'".$filter_penugasan);
|
||
|
|
|
||
|
|
$no = 1;
|
||
|
|
|
||
|
|
foreach($d_bidang as $db){
|
||
|
|
|
||
|
|
|
||
|
|
$d_isc = \DB::select("SELECT DISTINCT ON(bidang_ics) rsca.definition as bidang,ri.id as id_bidang_ics
|
||
|
|
FROM master_audit_finding maf
|
||
|
|
join reff_sub_class_audit rsca on rsca.id = maf.id_program
|
||
|
|
join reff_ics ri on ri.id = maf.ics
|
||
|
|
where rsca.id = '".$db->kode_bidang."' and finding_date >= '".$t_mulai."' and finding_date <= '".$t_selesai."' ".$filter_penugasan);
|
||
|
|
|
||
|
|
|
||
|
|
foreach ($d_isc as $di){
|
||
|
|
|
||
|
|
$data = \DB::select("SELECT maf.* ,rsca.id as kode_bidang,rsca.definition as bidang,ri.bidang_ics, ra.code as sebab_code
|
||
|
|
FROM master_audit_finding maf
|
||
|
|
join reff_sub_class_audit rsca on rsca.id = maf.id_program
|
||
|
|
join reff_ics ri on ri.id = maf.ics
|
||
|
|
join reff_aspek ra on ra.id = maf.jenis_sebab
|
||
|
|
where ri.id = '".$di->id_bidang_ics."' and finding_date >= '".$t_mulai."' and finding_date <= '".$t_selesai."'".$filter_penugasan);
|
||
|
|
|
||
|
|
|
||
|
|
foreach ($data as $d){
|
||
|
|
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$no++;
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
$total_row_formula = (count($d_init_bobot))+$no+35;
|
||
|
|
|
||
|
|
$headst = [
|
||
|
|
'borders' => [
|
||
|
|
'outline' => [
|
||
|
|
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
|
||
|
|
'color' => ['argb' => '000000'],
|
||
|
|
]
|
||
|
|
]
|
||
|
|
];
|
||
|
|
$event->sheet->getDelegate()->getStyle('A2:M3')->applyFromArray($headst);
|
||
|
|
$event->sheet->getDelegate()->getStyle('A5:M7')->applyFromArray($headst);
|
||
|
|
|
||
|
|
$styleArray = [
|
||
|
|
'borders' => [
|
||
|
|
'allBorders' => [
|
||
|
|
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
|
||
|
|
'color' => ['argb' => '000000'],
|
||
|
|
]
|
||
|
|
]
|
||
|
|
];
|
||
|
|
$event->sheet->getDelegate()->getStyle('A10:M'.$total_row_formula)->applyFromArray($styleArray);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('A')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('B')->setWidth(42);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('C')->setWidth(15);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('D')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('E')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('F')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('G')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('H')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('I')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('J')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('K')->setWidth(5);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('L')->setWidth(15);
|
||
|
|
$event->sheet->getDelegate()->getColumnDimension('M')->setWidth(15);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getStyle('A1:W500')
|
||
|
|
->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getStyle('A1:W500')->getFont()->setSize(10);
|
||
|
|
$event->sheet->getDelegate()->getStyle('A10:M12')->getFont()->setBold(true);
|
||
|
|
$event->sheet->getDelegate()->getStyle('A1:W500')->getFont()->setName('Arial');
|
||
|
|
$event->sheet->getDelegate()->getStyle('A10:M12')->getAlignment()->setWrapText(true);
|
||
|
|
$event->sheet->getDelegate()->getStyle('A10:W12')
|
||
|
|
->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getStyle('A2:W3')
|
||
|
|
->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->getStyle('A2:M3')->getFont()->setBold(true);
|
||
|
|
|
||
|
|
$event->sheet->getDelegate()->setShowGridlines(false);
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
];
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|