dataRekap

 public function exportBiaya2BV2(Request $request)
    {
        $datas = $this->datasRekap();
        $datas = $this->_filterDataRekapV2($request, $datas);
        $datas = $datas->get();

        return Excel::download(new DataRekapBiayaProduksi2B($datas), 'Rekap_Biaya_Produksi_II_B.xlsx');
    }

    private function datasRekap()
    {
        // fv = field view
        $bu = DB::table('badan_usaha as bu')
            ->select(
                'bu.id',
                'bu.singkat_bu'
            )
            ->whereNull('bu.deleted_at');

        $tp = DB::table('tim_prod as tp')
            ->select(
                'tp.id',
                'tp.singkat_tim_p'
            );
        $tpd = DB::table('tim_prod_details as tpd')
            ->select(
                'tpd.id',
                'b.singkat_bu as singkat_bu_tpd'
            )
            ->leftJoin('badan_usaha as b', 'tpd.id_bu', '=', 'b.id');

        $prov =  DB::table('ms_provinsi as prov')
            ->select(
                'prov.id',
                'prov.nama_singkat as nama_singkat_provinsi'
            );

        // jns_class_r
        $jc =  DB::table('ms_type_class1 as jc')
            ->select(
                'jc.id',
                'jc.kode'
            );

        $pst = DB::table('uji_peserta as pst')
            ->select(
                'pst.id_jadwal'
            )
            ->selectRaw('COUNT(*) as jumlah_peserta')
            ->groupBy('pst.id_jadwal')
            ->whereNull('pst.deleted_at');

        $datas = DB::table('uji_jadwal as a')
            ->select(
                'a.id',
                'a.id_pjk3_uji',
                'a.id_tim_prod',
                'a.id_tim_prod_dtl',
                'a.id_prov',
                'a.id_jenis_usaha',
                'a.jns_kgt',
                'a.ttd_sk_at', // fv = 7
                'a.no_kgt', // fv = 4

                // relation $bu (badan_usaha table)
                'bu.id as bu_id',
                'bu.singkat_bu', // fv = 1

                // relation $tp (tim_prod table)\
                'tp.singkat_tim_p', // fv = 2

                'tpd.singkat_bu_tpd', // fv = 3

                'prov.nama_singkat_provinsi', // fv 5

                'jc.kode', // fv 6

                'pst.jumlah_peserta', // fv 8

                'b.id_jadwal',
                'b.tipe_biaya'
            )
            ->selectRaw("max(case when b.uraian = 'Adm Srtf' then b.jml_biaya_real else 0 end) as by_adm_srtf") // fv = 9
            ->selectRaw("max(case when b.uraian = 'Adm Ijin' then b.jml_biaya_real else 0 end) as by_adm_ijin") // fv = 10
            ->selectRaw("max(case when b.uraian = 'Adm P3S_M' then b.jml_biaya_real else 0 end) as by_adm_p3sm") // fv = 11
            ->selectRaw("max(case when b.uraian = 'Adm Organisasi' then b.jml_biaya_real else 0 end) as by_adm_org") // fv = 12
            ->selectRaw("max(case when b.uraian = 'Sistim Aplikasi' then b.jml_biaya_real else 0 end) as by_sistem_app") // fv = 13
            ->selectRaw("max(case when b.uraian = 'TUK' then b.jml_biaya_real else 0 end) as by_tuk") // fv = 14
            ->selectRaw("max(case when b.uraian = 'Adm Lain_Lain' then b.jml_biaya_real else 0 end) as by_adm_lain") // fv = 15
            ->leftJoinSub($bu, 'bu', function ($join) {
                $join->on('a.id_pjk3_uji', '=', 'bu.id');
            })
            ->leftJoinSub($tp, 'tp', function ($join) {
                $join->on('a.id_tim_prod', '=', 'tp.id');
            })
            ->leftJoinSub($tpd, 'tpd', function ($join) {
                $join->on('a.id_tim_prod_dtl', '=', 'tpd.id');
            })
            ->leftJoinSub($prov, 'prov', function ($join) {
                $join->on('a.id_prov', '=', 'prov.id');
            })
            ->leftJoinSub($jc, 'jc', function ($join) {
                $join->on('a.jns_kgt', '=', 'jc.id');
            })
            ->leftJoinSub($pst, 'pst', function ($join) {
                $join->on('a.id', '=', 'pst.id_jadwal');
            })
            ->join('uji_biaya_produksi as b', 'a.id', '=', 'b.id_jadwal')
            ->oldest('a.ttd_sk_at')
            ->whereNull('a.deleted_at')
            ->groupBy('a.id');

        return $datas;
    }

    private function _filterDataRekapV2($request, $datas)
    {
        if ($request->f_awal_sk) {
            $f_awal_sk = $request->f_awal_sk;
            $f_akhir_sk = $request->f_akhir_sk;
            if (empty($request->f_akhir_sk)) {
                $datas->whereDate('a.ttd_sk_at', '>=', Carbon::createFromFormat('d/m/Y', $f_awal_sk));
            } else {
                $tgl_awal = Carbon::createFromFormat('d/m/Y', $f_awal_sk)->toDateString();
                $tgl_akhir = Carbon::createFromFormat('d/m/Y', $f_akhir_sk)->toDateString();
                $datas->whereRaw("DATE(a.ttd_sk_at) BETWEEN '$tgl_awal' and '$tgl_akhir' ");
            }
        } else {
            $f_awal_sk = Carbon::now();
            $datas->whereDate('a.ttd_sk_at', $f_awal_sk);
        }

        if ($request->f_akhir_sk) {
            $f_akhir_sk = $request->f_akhir_sk;
            $datas->whereDate('a.ttd_sk_at', '<=', Carbon::createFromFormat('d/m/Y', $f_akhir_sk));
        } else {
            $f_akhir_sk = Carbon::now();
            $datas->whereDate('a.ttd_sk_at', $f_akhir_sk);
        }

        if ($request->f_jenis_usaha) {
            $datas->where('a.id_jenis_usaha', $request->f_jenis_usaha);
        } else {
            $datas->where('a.id_jenis_usaha', 17);
        }

        if ($request->f_lsp) {
            $datas->where('a.id_pjk3_uji', $request->f_lsp);
        }

        if ($request->f_tim_prod != "null") {
            if ($request->f_tim_prod) {
                $datas->where('a.id_tim_prod', $request->f_tim_prod);
            }
        }

        return $datas;
    }