<?php

namespace App\Models;

use CodeIgniter\Model;

class Absensi_model extends Model
{
    var $table = 'absensi';
    public function __construct()
    {
        parent::__construct();
    }
    public function get_all_rombel($tahunpelajaran, $iduser)
    {
        if ($iduser) {
            $query = $this->db->table('rombel')
                ->select('rombel.*, user.nama as nama_walas, 
        (SELECT SUM(sakit) FROM absensi WHERE absensi.id_siswa IN (SELECT id_siswa FROM rombel_siswa WHERE rombel_siswa.id_rombel = rombel.id)) as total_sakit,
        (SELECT SUM(ijin) FROM absensi WHERE absensi.id_siswa IN (SELECT id_siswa FROM rombel_siswa WHERE rombel_siswa.id_rombel = rombel.id)) as total_ijin,
        (SELECT SUM(alfa) FROM absensi WHERE absensi.id_siswa IN (SELECT id_siswa FROM rombel_siswa WHERE rombel_siswa.id_rombel = rombel.id)) as total_alfa')
                ->join('user', 'user.id = rombel.walikelas')
                ->where('rombel.tahunpelajaran', $tahunpelajaran)
                ->where('rombel.walikelas', $iduser)
                ->get();
        } else {
            $query = $this->db->table('rombel')
                ->select('rombel.*, user.nama as nama_walas, 
    (SELECT SUM(sakit) FROM absensi WHERE absensi.id_siswa IN (SELECT id_siswa FROM rombel_siswa WHERE rombel_siswa.id_rombel = rombel.id)) as total_sakit,
    (SELECT SUM(ijin) FROM absensi WHERE absensi.id_siswa IN (SELECT id_siswa FROM rombel_siswa WHERE rombel_siswa.id_rombel = rombel.id)) as total_ijin,
    (SELECT SUM(alfa) FROM absensi WHERE absensi.id_siswa IN (SELECT id_siswa FROM rombel_siswa WHERE rombel_siswa.id_rombel = rombel.id)) as total_alfa')
                ->join('user', 'user.id = rombel.walikelas')
                ->where('rombel.tahunpelajaran', $tahunpelajaran)
                ->get();
        }
        return $query->getResult();
    }
    public function get_siswa($idrombel, $tahunpelajaran)
    {
        $query = $this->db->table('siswa')
            ->select('siswa.id as id_siswa, siswa.nama,rombel.nama_rombel,absensi.id as id_absensi,absensi.sakit,absensi.ijin,absensi.alfa,absensi.catatan')
            ->join('rombel_siswa', 'rombel_siswa.id_siswa = siswa.id')
            ->join('rombel', 'rombel.id=rombel_siswa.id_rombel')
            ->join('absensi', "absensi.id_siswa=siswa.id and absensi.tahunpelajaran='$tahunpelajaran'", 'left')
            ->where('rombel.id', $idrombel)
            ->orderBy('siswa.nama')
            ->get();
        return $query->getResult();
    }
    public function get_all_absensi()
    {
        $query = $this->db->table($this->table)
            ->get();
        return $query->getResult();
    }
    public function get_where_absensi($where)
    {
        $query = $this->db->table('absensi')
            ->where($where)
            ->get();
        return $query->getResult();
    }
    public function get_where_tujuan($where)
    {
        $query = $this->db->table('tujuan')
            ->where($where)
            ->get();
        return $query->getResult();
    }
    public function cek_where($where)
    {
        $query = $this->db->table($this->table)
            ->where($where)
            ->get();
        return $query->getRow();
    }





    public function get_guru()
    {
        $query = $this->db->table('user')
            ->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 absensi_add($data)
    {
        $query = $this->db->table($this->table)->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(['absensi' => $data], ['id' => 1]);
        return $update;
    }

    public function absensi_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));
    }
}
