public function index(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('psb.index', ['data' => $data, 'dataFooter' => $dataFooter[0], 'awal' => $awal, 'ta' => $ta]);
}
public function scopeSekarang($query)
{
return $query->where('tahun_ajaran', \App\Ta::active()->first()->periode);
}
/**
* Remove the specified resource from storage.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function destroy(Ta $ta)
{
$ta->delete();
return redirect('/tahun-ajaran');
}
