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;
}