<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Blog;
use App\ProfileSekolah;
use App\RunningText;
use App\DataProgress;
use App\DataGuru;
use App\DataSiswa;
use App\Pages;
use App\Banner;
use App\Kegiatan;
use App\ManagementSekolah;
use App\Gallery;
use App\DataFile;
use App\DataSekolah;
use App\StrukturOrganisasi;
use Helper;
use Alert;
use App\BukuTamu;
use Carbon\Carbon;
use Illuminate\Database\QueryException;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\DB;
use Yajra\DataTables\Facades\DataTables;

class FrontEndController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public $periode = 2023;

    public function index()
    {
        set_time_limit(0);
        ini_set('memory_limit', '256M');
        $banner = Banner::where('status', '1')->orderBy('created_at', 'DESC')->get();
        $runningText = RunningText::where('status', '1')->orderBy('created_at', 'DESC')->get();
        $kegiatan = Kegiatan::where('status', '1')->orderBy('created_at', 'DESC')->get();
        $managementsekolah = ManagementSekolah::where('status', '1')->get();
        $gallery = Gallery::where('status', '1')->orderBy('created_at', 'DESC')->limit(8)->get();
        $blog = Blog::from('blogs as a')
                ->leftJoin('users as b', 'b.username', '=', 'a.author')
                ->select('a.*', 'b.name as nama_author', 'b.foto as foto_author')
                ->where('a.status', '1')
                ->orderBy('a.created_at', 'DESC')->get();
        $progress = DataProgress::where('sekolah_id', Helper::web_config('sekolah_id'))
                ->where('periode', Helper::periode())
                ->first();

        //Cek Folder install exists
        if(!is_dir("install/")){
            return view('pages.fe.home', compact('banner', 'runningText', 'kegiatan', 'managementsekolah', 'progress', 'blog', 'gallery'));
        }else{
            return view('pages.fe.check_installer');
        }
    }

    public function data_guru()
    {
        set_time_limit(0);
        ini_set('memory_limit', '256M');
        /* Guru */
        $data_guru_status_pns = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereIn('status_kepegawaian_id', [1,2,3,10,11])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_status_gtt = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereNotIn('status_kepegawaian_id', [1,2,3,10,11])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_guru_gol_1 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['golongan', 'LIKE', 'I/%']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_gol_2 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['golongan', 'LIKE', 'II/%']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_gol_3 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['golongan', 'LIKE', 'III/%']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_gol_4 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['golongan', 'LIKE', 'IV/%']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_guru_sertifikasi = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['sertifikasi', '!=', '-']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_non_sertifikasi = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['sertifikasi', '=', '-']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_guru_ijazah_s1 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['pendidikan_akhir', '=', 'S1']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_ijazah_non_s1 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['pendidikan_akhir', '=', 'Dibawah S1']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_ijazah_non_data = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['pendidikan_akhir', '=', '']])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_guru_kurang_30 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereraw('usia <= 30')->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_31_35 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [31, 35])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_36_40 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [36, 40])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_41_45 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [41, 45])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_46_50 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [46, 50])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_51_55 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [51, 55])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_lebih_55 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereraw('usia > 55')->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_guru_laki = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), 'jk_ptk' => 'L'])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_guru_perempuan = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), 'jk_ptk' => 'P'])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $daftar_guru = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        /* PTK */
        $data_ptk_status_pns = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereIn('status_kepegawaian_id', [1,2,3,10,11])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_status_ptt = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereNotIn('status_kepegawaian_id', [1,2,3,10,11])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_ptk_gol_1 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['golongan', 'LIKE', 'I/%']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_gol_2 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['golongan', 'LIKE', 'II/%']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_gol_3 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['golongan', 'LIKE', 'III/%']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_gol_4 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['golongan', 'LIKE', 'IV/%']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_ptk_sertifikasi = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['sertifikasi', '!=', '-']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_non_sertifikasi = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['sertifikasi', '=', '-']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_ptk_ijazah_s1 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['pendidikan_akhir', '=', 'S1']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_ijazah_non_s1 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['pendidikan_akhir', '=', 'Dibawah S1']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_ijazah_non_data = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), ['pendidikan_akhir', '=', '']])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_ptk_kurang_30 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereraw('usia <= 30')->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_31_35 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [31, 35])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_36_40 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [36, 40])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_41_45 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [41, 45])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_46_50 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [46, 50])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_51_55 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereBetween('usia', [51, 55])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_lebih_55 = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereraw('usia > 55')->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $data_ptk_laki = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), 'jk_ptk' => 'L'])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();
        $data_ptk_perempuan = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id'), 'jk_ptk' => 'P'])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        $daftar_ptk = DataGuru::where(['sekolah_id' => Helper::web_config('sekolah_id')])->whereNotIn('jenis_ptk_id', [3,4,5,6,12,13,14,15,16])->where('periode', $this->periode)->get();

        return view('pages.fe.data_guru', compact('data_guru_status_pns', 'data_guru_status_gtt', 'data_guru_gol_1', 'data_guru_gol_2', 'data_guru_gol_3', 'data_guru_gol_4', 'data_guru_sertifikasi', 'data_guru_non_sertifikasi', 'data_guru_ijazah_s1', 'data_guru_ijazah_non_s1', 'data_guru_ijazah_non_data', 'data_guru_kurang_30', 'data_guru_31_35', 'data_guru_36_40', 'data_guru_41_45', 'data_guru_46_50', 'data_guru_51_55', 'data_guru_lebih_55', 'data_guru_laki', 'data_guru_perempuan', 'daftar_guru','data_ptk_status_pns', 'data_ptk_status_ptt', 'data_ptk_gol_1', 'data_ptk_gol_2', 'data_ptk_gol_3', 'data_ptk_gol_4', 'data_ptk_sertifikasi', 'data_ptk_non_sertifikasi', 'data_ptk_ijazah_s1', 'data_ptk_ijazah_non_s1', 'data_ptk_ijazah_non_data', 'data_ptk_kurang_30', 'data_ptk_31_35', 'data_ptk_36_40', 'data_ptk_41_45', 'data_ptk_46_50', 'data_ptk_51_55', 'data_ptk_lebih_55', 'data_ptk_laki', 'data_ptk_perempuan', 'daftar_ptk'));
    }

    public function data_siswa()
    {
        set_time_limit(0);
        ini_set('memory_limit', '256M');
        /* Data Siswa */
        if(in_array(Helper::web_config('bentuk_pendidikan_id'), [1,2,3,4])){
            $siswa_tingkat = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                            ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                            ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.kelas as rombel', DB::raw('COUNT(b.kelas) as jumlah'))
                            ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                            ->whereIn('b.bentuk_pendidikan_id', [1,2,3,4])
                            ->where('b.periode', $this->periode)
                            ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.kelas')
                            ->orderBy('b.kelas', 'ASC')
                            ->get();
            $agama = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                    ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                    ->leftJoin('ODS_Situbondo.ref.agama as c', 'c.agama_id', 'b.agama_id')
                    ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.agama', DB::raw('COUNT(CASE WHEN b.agama_id = c.agama_id THEN 1 ELSE NULL END) as jumlah'))
                    ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('b.bentuk_pendidikan_id', [1,2,3,4])
                    ->where('b.periode', $this->periode)
                    ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.agama')
                    ->get();
            $daftarsiswa = DataSiswa::from('ODS_Dapen.dbo.siswa_view')
                    ->select('nama_siswa', 'jenis_kelamin', 'nisn', 'kelas')
                    ->where(['sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('bentuk_pendidikan_id', [1,2,3,4])
                    ->where('periode', $this->periode)
                    ->orderBy('kelas', 'ASC')
                    ->get();
            $jksiswa = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                    ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                    ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.jenis_kelamin', DB::raw('COUNT(b.jenis_kelamin) as jumlah'))
                    ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('b.bentuk_pendidikan_id', [1,2,3,4])
                    ->where('b.periode', $this->periode)
                    ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.jenis_kelamin')
                    ->get();
            $usiasiswa = DataSiswa::from('dbo.usiaSMP')
                        ->where(['sekolah_id' => Helper::web_config('sekolah_id')])
                        ->whereIn('bentuk_pendidikan_id', [1,2,3,4])
                        ->get();
        }elseif(in_array(Helper::web_config('bentuk_pendidikan_id'), [5])){
            $siswa_tingkat = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                            ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                            ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.kelas as rombel', DB::raw('COUNT(b.kelas) as jumlah'))
                            ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                            ->whereIn('b.bentuk_pendidikan_id', [5])
                            ->where('b.periode', $this->periode)
                            ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.kelas')
                            ->orderBy('b.kelas', 'ASC')
                            ->get();
            $agama = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                    ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                    ->leftJoin('ODS_Situbondo.ref.agama as c', 'c.agama_id', 'b.agama_id')
                    ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.agama', DB::raw('COUNT(CASE WHEN b.agama_id = c.agama_id THEN 1 ELSE NULL END) as jumlah'))
                    ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('b.bentuk_pendidikan_id', [5])
                    ->where('b.periode', $this->periode)
                    ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.agama')
                    ->get();
            $daftarsiswa = DataSiswa::from('ODS_Dapen.dbo.siswa_view')
                    ->select('nama_siswa', 'jenis_kelamin', 'nisn', 'kelas')
                    ->where(['sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('bentuk_pendidikan_id', [5])
                    ->where('periode', $this->periode)
                    ->orderBy('kelas', 'ASC')
                    ->get();
            $jksiswa = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                    ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                    ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.jenis_kelamin', DB::raw('COUNT(b.jenis_kelamin) as jumlah'))
                    ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('b.bentuk_pendidikan_id', [5])
                    ->where('b.periode', $this->periode)
                    ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.jenis_kelamin')
                    ->get();
            $usiasiswa = DataSiswa::from('dbo.usiaSMP')
                        ->where(['sekolah_id' => Helper::web_config('sekolah_id')])
                        ->whereIn('bentuk_pendidikan_id', [5])
                        ->get();
        }elseif(in_array(Helper::web_config('bentuk_pendidikan_id'), [6])){
            $siswa_tingkat = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                            ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                            ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.kelas as rombel', DB::raw('COUNT(b.kelas) as jumlah'))
                            ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                            ->whereIn('b.bentuk_pendidikan_id', [6])
                            ->where('b.periode', $this->periode)
                            ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.kelas')
                            ->orderBy('b.kelas', 'ASC')
                            ->get();
            $agama = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                    ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                    ->leftJoin('ODS_Situbondo.ref.agama as c', 'c.agama_id', 'b.agama_id')
                    ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.agama', DB::raw('COUNT(CASE WHEN b.agama_id = c.agama_id THEN 1 ELSE NULL END) as jumlah'))
                    ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('b.bentuk_pendidikan_id', [6])
                    ->where('b.periode', $this->periode)
                    ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.agama')
                    ->get();
            $daftarsiswa = DataSiswa::from('ODS_Dapen.dbo.siswa_view')
                    ->select('nama_siswa', 'jenis_kelamin', 'nisn', 'kelas')
                    ->where(['sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('bentuk_pendidikan_id', [6])
                    ->where('periode', $this->periode)
                    ->orderBy('kelas', 'ASC')
                    ->get();
            $jksiswa = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                    ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                    ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.jenis_kelamin', DB::raw('COUNT(b.jenis_kelamin) as jumlah'))
                    ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('b.bentuk_pendidikan_id', [6])
                    ->where('b.periode', $this->periode)
                    ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.jenis_kelamin')
                    ->get();
            $usiasiswa = DataSiswa::from('dbo.usiaSMP')
                        ->where(['sekolah_id' => Helper::web_config('sekolah_id')])
                        ->whereIn('bentuk_pendidikan_id', [6])
                        ->get();
        }elseif(in_array(Helper::web_config('bentuk_pendidikan_id'), [24])){
            $$siswa_tingkat = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                            ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                            ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.kelas as rombel', DB::raw('COUNT(b.kelas) as jumlah'))
                            ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                            ->whereIn('b.bentuk_pendidikan_id', [24])
                            ->where('b.periode', $this->periode)
                            ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.kelas')
                            ->orderBy('b.kelas', 'ASC')
                            ->get();
            $agama = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                    ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                    ->leftJoin('ODS_Situbondo.ref.agama as c', 'c.agama_id', 'b.agama_id')
                    ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.agama', DB::raw('COUNT(CASE WHEN b.agama_id = c.agama_id THEN 1 ELSE NULL END) as jumlah'))
                    ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('b.bentuk_pendidikan_id', [24])
                    ->where('b.periode', $this->periode)
                    ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.agama')
                    ->get();
            $daftarsiswa = DataSiswa::from('ODS_Dapen.dbo.siswa_view')
                    ->select('nama_siswa', 'jenis_kelamin', 'nisn', 'kelas')
                    ->where(['sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('bentuk_pendidikan_id', [24])
                    ->where('periode', $this->periode)
                    ->orderBy('kelas', 'ASC')
                    ->get();
            $jksiswa = DataSiswa::from('ODS_Dapen.dbo.sekolah_view as a')
                    ->leftJoin('ODS_Dapen.dbo.siswa_view as b', 'b.sekolah_id', 'a.sekolah_id')
                    ->select('a.nama as sekolah', 'a.bentuk_pendidikan_id', 'b.jenis_kelamin', DB::raw('COUNT(b.jenis_kelamin) as jumlah'))
                    ->where(['b.sekolah_id' => Helper::web_config('sekolah_id')])
                    ->whereIn('b.bentuk_pendidikan_id', [24])
                    ->where('b.periode', $this->periode)
                    ->groupBy('a.nama', 'a.bentuk_pendidikan_id', 'b.jenis_kelamin')
                    ->get();
            $usiasiswa = DataSiswa::from('dbo.usiaSMP')
                        ->where(['sekolah_id' => Helper::web_config('sekolah_id')])
                        ->whereIn('bentuk_pendidikan_id', [24])
                        ->get();
        }else{
            $siswa_tingkat = false;
            $agama = false;
            $daftarsiswa = false;
            $jksiswa = false;
            $usiasiswa = false;
        }
        /* Data Siswa Mengulang */
        /* Data Siswa Baru */
        /* Data Siswa Lulus */
        return view('pages.fe.data_siswa', compact('siswa_tingkat', 'agama', 'daftarsiswa', 'jksiswa', 'usiasiswa'));
    }

    public function visit($slug){
        $model = Pages::where('slug', 'pages/'.$slug)->first();
        return view('pages.fe.halaman', compact('model'));
    }

    public function blog($slug=null, $keyword=null)
    {
        if($slug == null){
            $data = Blog::from('blogs as a')
                    ->leftJoin('users as b', 'b.username', '=', 'a.author')
                    ->select('a.*', 'b.name as nama_author', 'b.foto as foto_author')
                    ->where('a.status', '1')
                    ->orderBy('a.created_at', 'DESC')
                    ->orderBy('a.updated_at', 'DESC')
                    ->paginate(3);
            $recent = Blog::from('blogs as a')
                    ->leftJoin('users as b', 'b.username', '=', 'a.author')
                    ->select('a.*', 'b.name as nama_author', 'b.foto as foto_author')
                    ->where('a.status', '1')
                    ->orderBy('a.created_at', 'DESC')
                    ->orderBy('a.updated_at', 'DESC')
                    ->limit(5)
                    ->get();
            return view('pages.fe.blogs', compact('data', 'recent'));
        }else{
            $data = Blog::from('blogs as a')
                    ->leftJoin('users as b', 'b.username', '=', 'a.author')
                    ->select('a.*', 'b.name as nama_author', 'b.foto as foto_author')
                    ->where(['a.status' => '1', 'a.slug' => $slug])
                    ->orderBy('a.created_at', 'DESC')
                    ->orderBy('a.updated_at', 'DESC')
                    ->first();
            $recent = Blog::from('blogs as a')
                    ->leftJoin('users as b', 'b.username', '=', 'a.author')
                    ->select('a.*', 'b.name as nama_author', 'b.foto as foto_author')
                    ->where('a.status', '1')
                    ->orderBy('a.created_at', 'DESC')
                    ->orderBy('a.updated_at', 'DESC')
                    ->limit(5)
                    ->get();
            return view('pages.fe.blog_detail', compact('data', 'recent'));
        }
    }

    public function blog_search(Request $request)
    {
        $data = Blog::from('blogs as a')
                ->leftJoin('users as b', 'b.username', '=', 'a.author')
                ->select('a.*', 'b.name as nama_author', 'b.foto as foto_author')
                ->when($request->keyword, function ($query) use ($request) {
                    $query->where('a.title', 'like', "%{$request->keyword}%")
                    ->orWhere('a.content', 'like', "%{$request->keyword}%");
                })
                ->orderBy('a.created_at', 'DESC')
                ->orderBy('a.updated_at', 'DESC')
                ->paginate(3);
        $recent = Blog::from('blogs as a')
                ->leftJoin('users as b', 'b.username', '=', 'a.author')
                ->select('a.*', 'b.name as nama_author', 'b.foto as foto_author')
                ->where('a.status', '1')
                ->orderBy('a.created_at', 'DESC')
                ->orderBy('a.updated_at', 'DESC')
                ->limit(5)
                ->get();
        return view('pages.fe.blogs', compact('data', 'recent'));
    }

    public function struktur()
    {
        $struktur = StrukturOrganisasi::where('status', '1')->orderBy('created_at', 'DESC')->first();
        return view('pages.fe.struktur-organisasi', compact('struktur'));
    }

    public function gallery()
    {
        $data = Gallery::where('status', '1')->orderBy('created_at', 'DESC')->get();
        return view('pages.fe.gallery', compact('data'));
    }

    public function download()
    {
        $data = DataFile::where('status', '1')->orderBy('created_at', 'DESC')->get();
        return view('pages.fe.download', compact('data'));
    }

    public function kontak()
    {
        $data = DataSekolah::where('sekolah_id', Helper::web_config('sekolah_id'))->first();
        return view('pages.fe.kontak', compact('data'));
    }

    public function bukutamu()
    {
        $tujuans = ManagementSekolah::where('status', '1')->get();
        return view('pages.fe.bukutamu', compact('tujuans'));
    }

    public function bukutamuJson(Request $request)
    {
        $data = BukuTamu::whereDate('created_at', Carbon::today()->toDateString())->orderBy('id', 'DESC')->get();
        return DataTables::of($data)
                ->addIndexColumn()
                ->editColumn('tujuan_id', function($row){
                    return $row->tujuan->nama.' - '.$row->tujuan->jabatan;
                })
                ->editColumn('created_at', function($row){
                    return \Helper::full_indo($row->created_at);
                })
                ->make(true);
    }

    public function bukutamuStore(Request $request)
    {
        try {
            if($request->email != NULL AND $request->no_hp != NULL){
                $this->validate($request, [
                    'nama' => 'required|string',
                    'email' => 'email',
                    'no_hp' => 'min:10',
                    'tujuan_id' => 'required',
                    'keperluan' => 'required'
                ],[
                    'required' => 'Field wajib diisi!',
                    'email' => 'Format email salah!',
                    'min' => 'Jumlah nomor telepon minimal 10 digit!',
                    'numeric' => 'Hanya angka yang diperbolehkan!',
                    'string' => 'Hanya teks yang diperbolehkan!',
                ]);
            }elseif($request->email != NULL AND $request->no_hp == NULL){
                $this->validate($request, [
                    'nama' => 'required|string',
                    'email' => 'email',
                    'tujuan_id' => 'required',
                    'keperluan' => 'required'
                ],[
                    'required' => 'Field wajib diisi!',
                    'email' => 'Format email salah!',
                    'numeric' => 'Hanya angka yang diperbolehkan!',
                    'string' => 'Hanya teks yang diperbolehkan!',
                ]);
            }elseif($request->email == NULL AND $request->no_hp != NULL){
                $this->validate($request, [
                    'nama' => 'required|string',
                    'no_hp' => 'min:10',
                    'tujuan_id' => 'required',
                    'keperluan' => 'required'
                ],[
                    'required' => 'Field wajib diisi!',
                    'min' => 'Jumlah nomor telepon minimal 10 digit!',
                    'numeric' => 'Hanya angka yang diperbolehkan!',
                    'string' => 'Hanya teks yang diperbolehkan!',
                ]);
            }else{
                $this->validate($request, [
                    'nama' => 'required|string',
                    'tujuan_id' => 'required',
                    'keperluan' => 'required'
                ],[
                    'required' => 'Field wajib diisi!',
                    'numeric' => 'Hanya angka yang diperbolehkan!',
                    'string' => 'Hanya teks yang diperbolehkan!',
                ]);
            }
            $bukutamu = new BukuTamu();
            $request->request->add(['created_at' => Carbon::now('Asia/Jakarta')]);
            $data = $request->except('_token');
            $bukutamu->create($data);
            return response()->json(['code' => Response::HTTP_OK, 'msg' => 'Berhasil menambah buku tamu!'], Response::HTTP_OK);
        } catch (QueryException $e) {
            return response()->json(['code' => Response::HTTP_UNPROCESSABLE_ENTITY, 'msg' => 'Gagal menambah buku tamu : '.$e->errorInfo[2]], Response::HTTP_UNPROCESSABLE_ENTITY);
        }
    }

    public function sendMail(Request $request)
    {
        //
    }

    public function install(Request $request)
    {
        //
    }

    public function passwordreset(){

        return view('pages.fe.passwordreset');
    }
}
