protected function response(Psb $psb, Request $request) { if ($request->ajax()) { return json_encode(['success' => true, 'jenjang' => $psb->jenjang, 'html' => view('psb._list', ['psbs' => Psb::where('jenjang', $psb->jenjang)->sekarang()->get(), 'jenjang' => $psb->jenjang])->render()]); } else { return redirect('/psb/admin'); } }
public function report(Request $request) { $ta = Ta::active()->first()->periode; $daftarPalingAwal = Psb::where('tahun_ajaran', $ta)->orderBy('tanggal_daftar', 'ASC')->first(); $awal = $daftarPalingAwal ? $daftarPalingAwal->tanggal_daftar : date('Y-m-d'); // params $bindings = ['start' => $request->get('start', $awal), 'stop' => $request->get('stop', date('Y-m-d')), 'ta' => $ta]; $query = "select jenjang j, tingkat t, count(psb.id) total, cs.jenis_kelamin jk,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 1 and tanggal_daftar between ':start' and ':stop' and intern = 0 and tahun_ajaran = ':ta') as daftar_extern,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 1 and tanggal_daftar between ':start' and ':stop' and intern = 1 and tahun_ajaran = ':ta') as daftar_intern,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 2 and tanggal_daftar between ':start' and ':stop' and intern = 0 and tahun_ajaran = ':ta') as bayar_extern,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 2 and tanggal_daftar between ':start' and ':stop' and intern = 1 and tahun_ajaran = ':ta') as bayar_intern,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 3 and tanggal_daftar between ':start' and ':stop' and intern = 0 and tahun_ajaran = ':ta') as isi_form_extern,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 3 and tanggal_daftar between ':start' and ':stop' and intern = 1 and tahun_ajaran = ':ta') as isi_form_intern,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 4 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as form_ok,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 5 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as berkas_ok,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 6 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as test_ok,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 7 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as wawancara_ok,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 8 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as wawancara_ortu_ok,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 9 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as tkd_ok,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 10 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as diterima,\n (select count(psb.id) from psb join calon_siswa cs1 on cs1.psb_id = psb.id where tingkat=t and cs1.jenis_kelamin=jk and status_progress >= 11 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as ditolak\n from psb join calon_siswa cs on psb.id = cs.psb_id where tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta' group by tingkat, cs.jenis_kelamin"; $queryFooter = "select count(id) total,\n (select count(id) from psb where status_progress >= 1 and tanggal_daftar between ':start' and ':stop' and intern = 0 and tahun_ajaran = ':ta') as daftar_extern,\n (select count(id) from psb where status_progress >= 1 and tanggal_daftar between ':start' and ':stop' and intern = 1 and tahun_ajaran = ':ta') as daftar_intern,\n (select count(id) from psb where status_progress >= 2 and tanggal_daftar between ':start' and ':stop' and intern = 0 and tahun_ajaran = ':ta') as bayar_extern,\n (select count(id) from psb where status_progress >= 2 and tanggal_daftar between ':start' and ':stop' and intern = 1 and tahun_ajaran = ':ta') as bayar_intern,\n (select count(id) from psb where status_progress >= 3 and tanggal_daftar between ':start' and ':stop' and intern = 0 and tahun_ajaran = ':ta') as isi_form_extern,\n (select count(id) from psb where status_progress >= 3 and tanggal_daftar between ':start' and ':stop' and intern = 1 and tahun_ajaran = ':ta') as isi_form_intern,\n (select count(id) from psb where status_progress >= 4 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as form_ok,\n (select count(id) from psb where status_progress >= 5 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as berkas_ok,\n (select count(id) from psb where status_progress >= 6 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as test_ok,\n (select count(id) from psb where status_progress >= 7 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as wawancara_ok,\n (select count(id) from psb where status_progress >= 8 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as wawancara_ortu_ok,\n (select count(id) from psb where status_progress >= 9 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as tkd_ok,\n (select count(id) from psb where status_progress >= 10 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as diterima,\n (select count(id) from psb where status_progress >= 11 and tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta') as ditolak\n from psb where tanggal_daftar between ':start' and ':stop' and tahun_ajaran = ':ta'"; // harusnya gini // $data = DB::select(DB::raw($query, [ // 'start' => $request->get('start','2015-12-21'), // 'stop' => $request->get('stop', date('Y-m-d')), // 'ta' => Ta::active()->first()->periode // ])); $data = DB::select(DB::raw(str_replace([':start', ':stop', ':ta'], [$request->get('start', $awal), $request->get('stop', date('Y-m-d')), $ta], $query))); // dd($data); $dataFooter = DB::select(DB::raw(str_replace([':start', ':stop', ':ta'], [$request->get('start', $awal), $request->get('stop', date('Y-m-d')), $ta], $queryFooter))); // dd($dataFooter[0]); return view('psb1.report', ['data' => $data, 'dataFooter' => $dataFooter[0], 'awal' => $awal, 'ta' => $ta]); }