<?php

namespace App\Models;

use CodeIgniter\Model;

class Ekstrakulikuler_model extends Model
{
    var $table = 'ekstrakulikuler';
    public function __construct()
    {
        parent::__construct();
    }

    public function get_all_ekstrakulikuler($tahunpelajaran, $iduser)
    {
        if ($iduser) {
            $builder = $this->db->table($this->table);
            $builder->select('ekstrakulikuler.*, user.nama');
            $builder->select("(SELECT COUNT(*) FROM penilaian_eskul WHERE penilaian_eskul.tahunpelajaran='$tahunpelajaran' AND penilaian_eskul.id_eskul = ekstrakulikuler.id AND penilaian_eskul.id_siswa IN (SELECT id FROM siswa)) as anggota");
            $builder->select("(SELECT COUNT(*) FROM penilaian_eskul WHERE penilaian_eskul.tahunpelajaran='$tahunpelajaran' AND penilaian_eskul.id_eskul = ekstrakulikuler.id AND penilaian_eskul.predikat != '') as jumlah_dinilai");
            $builder->select("(SELECT COUNT(*) FROM penilaian_eskul WHERE penilaian_eskul.tahunpelajaran='$tahunpelajaran' AND penilaian_eskul.id_eskul = ekstrakulikuler.id) as jumlah_belum");
            $builder->join('user', 'ekstrakulikuler.pembina = user.id');
            $builder->where('ekstrakulikuler.pembina', $iduser);
            //$builder->where('penilaian_eskul.tahunpelajaran', $tahunpelajaran);
            $query = $builder->get();
        } else {
            $builder = $this->db->table($this->table);
            $builder->select('ekstrakulikuler.*, user.nama');
            $builder->select("(SELECT COUNT(*) FROM penilaian_eskul WHERE penilaian_eskul.tahunpelajaran='$tahunpelajaran' AND penilaian_eskul.id_eskul = ekstrakulikuler.id AND penilaian_eskul.id_siswa IN (SELECT id FROM siswa)) as anggota");
            $builder->select("(SELECT COUNT(*) FROM penilaian_eskul WHERE penilaian_eskul.tahunpelajaran='$tahunpelajaran' AND penilaian_eskul.id_eskul = ekstrakulikuler.id AND penilaian_eskul.predikat != '') as jumlah_dinilai");
            $builder->select("(SELECT COUNT(*) FROM penilaian_eskul WHERE penilaian_eskul.tahunpelajaran='$tahunpelajaran' AND penilaian_eskul.id_eskul = ekstrakulikuler.id) as jumlah_belum");
            $builder->join('user', 'ekstrakulikuler.pembina = user.id');
            //$builder->where('penilaian_eskul.tahunpelajaran', $tahunpelajaran);
            $query = $builder->get();
        }
        return $query->getResult();
    }
    public function get_siswa($tahunpelajaran = null)
    {

        $query = $this->db->table('siswa')
            ->select('siswa.id, siswa.nama, siswa.nis,rombel.nama_rombel as rombel')
            ->join('rombel_siswa', 'rombel_siswa.id_siswa=siswa.id')
            ->join('rombel', "rombel.id=rombel_siswa.id_rombel")
            ->groupBy('siswa.id')
            ->get();

        return $query->getResult();
    }
    public function get_anggota($ideskul, $tahunpelajaran)
    {
        $query = $this->db->table('penilaian_eskul')
            ->select('penilaian_eskul.id as id_penilaian,siswa.nama,penilaian_eskul.predikat,penilaian_eskul.deskripsi,rombel.nama_rombel as rombel')
            ->join('siswa', 'siswa.id=penilaian_eskul.id_siswa')
            ->join('rombel_siswa', 'rombel_siswa.id_siswa=penilaian_eskul.id_siswa')
            ->join('rombel', "rombel.id=rombel_siswa.id_rombel and rombel.tahunpelajaran='$tahunpelajaran'")
            ->where('penilaian_eskul.tahunpelajaran', $tahunpelajaran)
            ->where('penilaian_eskul.id_eskul', $ideskul)
            ->get();
        return $query->getResult();
    }

    public function get_wali()
    {
        $query = $this->db->table('users')
            ->where('role', 'guru')
            ->get();
        return $query->getResult();
    }

    public function get_by_id($id)
    {
        $query =  $this->db->table($this->table)
            ->where('id', $id)
            ->get();
        return $query->getRow();
    }
    public function get_where_anggota($where)
    {
        $query =  $this->db->table('penilaian_eskul')
            ->where($where)
            ->get();
        return $query->getRow();
    }
    public function ekstrakulikuler_add($data)
    {
        $query = $this->db->table($this->table)->insert($data);
        return $this->db->insertID();
    }
    public function ekstrakulikuler_addanggota($data)
    {
        $query = $this->db->table('eskul_siswa')->insert($data);
        return $this->db->insertID();
    }
    public function ekstrakulikuler_addpenilaian($data)
    {
        $query = $this->db->table('penilaian_eskul')->insert($data);
        return $this->db->insertID();
    }
    public function update_status($where)
    {

        $cekdata = $this->db->table($this->table)
            ->update(['status' => 'tidak'], $where);
        return $cekdata;
    }
    public function update_setting($data)
    {
        $update = $this->db->table('settings')
            ->update(['ekstrakulikuler' => $data], ['id' => 1]);
        return $update;
    }

    public function ekstrakulikuler_update($where, $data)
    {
        $this->db->table($this->table)->update($data, $where);
        return $this->db->affectedRows();
    }

    public function delete_by_id($id)
    {
        $this->db->table($this->table)->delete(array('id' => $id));
    }
    public function delete_anggota($id)
    {
        $this->db->table('penilaian_eskul')->delete(array('id' => $id));
    }
}
