djid_siput/app/Exports/MetadataProgramDetailExport.php

<?php
namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithTitle;
use App\Model\MetadataProgram;
use App\Model\TahunProgramDetail;
use App\Model\ItemProgram;
use Illuminate\Http\Request;
use DB;

class MetadataProgramDetailExport implements FromArray, WithTitle
{
    private $month;
    private $year;

    public function __construct(Request $request, object $tahunProgramDetail)
    {
        $this->request = $request;
        $this->tahunProgramDetail = $tahunProgramDetail;
    }

    public function array(): array
    {
        $values = [];

        if(!empty($this->tahunProgramDetail->templateProgramDetail) && !empty($this->tahunProgramDetail->templateProgramDetail->itemProgram)){
            $select = '';

            //header
            $header = [];
            $header[] = 'UPT';
            $header[] = 'Program';
            $header[] = 'Sub Program';
            $header[] = 'Target';
            
            $itemProgram = $this->tahunProgramDetail->templateProgramDetail->itemProgram->sortBy('order_by');

            $header = array_merge($header, $itemProgram->pluck('label')->toArray());
            $header[] = 'Tanggal Pelaporan';
            $values[] = $header;

            //column
            $column = [];
            $column[] = 'u_p_t_s.office_name as nama_upt';
            $column[] = 'template_program.nama as nama_program';
            $column[] = 'template_program_detail.nama as nama_sub_program';
            $column[] = DB::raw("(SELECT CONCAT(target,' ',satuan) as target from tahun_program_detail_upt WHERE id_tahun_program_detail = metadata_program.id_tb_tahun_program_detail AND id_upt = metadata_program.id_upt AND periode = metadata_program.periode LIMIT 1)");
            $column = array_merge($column, $itemProgram->pluck('kolom')->toArray());
            $column[] = 'metadata_program.created_at';
            
            //data
            $query = new MetadataProgram;
            $query = $query->select($column);
            $query = $query->leftJoin('u_p_t_s','metadata_program.id_upt','u_p_t_s.office_id');
            $query = $query->leftJoin('tahun_program_detail','tahun_program_detail.id','=','metadata_program.id_tb_tahun_program_detail');
            $query = $query->leftJoin('template_program','template_program.id','=','tahun_program_detail.id_template_program');
            $query = $query->leftJoin('template_program_detail','template_program_detail.id','=','tahun_program_detail.id_template_program_detail');
            $query = $query->leftJoin('tahun_program_detail_upt','tahun_program_detail.id','=','tahun_program_detail_upt.id_tahun_program_detail');
            $query = $query->where('id_tb_tahun_program_detail',$this->tahunProgramDetail->id);
            $query = $query->distinct('metadata_program.id');
            
            if(!empty($this->request->upt)){
                $query = $query->where('metadata_program.id_upt',$this->request->upt);
            }
            
            if (!empty($this->request->dari) && !empty($this->request->sampai)) {
                $query = $query->whereBetween('metadata_program.created_at', array($this->request->dari, $this->request->sampai));
            }            
            // dd($query->get()->toArray());
            $values[] = $query->get()->toArray();
        }
        
        return $values;
    }

    public function startCell(): string
    {
        return 'A1';
    }

    /**
     * @return string
     */
    public function title(): string
    {
        return !empty($this->tahunProgramDetail->templateProgramDetail)?$this->tahunProgramDetail->templateProgramDetail->nama:'-';
    }
}
?>