<?php

namespace App\Models;

use CodeIgniter\Model;

class DatatableModel extends Model
{
    protected $db;
    protected $session;

    public $table          = "student";
    public $select_column  = ["id", "user_id", "image", "enroll_num", "name", "father_name", "mother_name", "admission_session", "admission_type", "year", "course_id", "sub_course_id", "status", "form", "form_submited_date", "center_ref_no"];
    public $order_column   = [null, "user_id", "enroll_num", "name", "father_name", "mother_name", "admission_session", "admission_type", "course_id", "sub_course_id", "status"];

    public $select_re_reg_column = ["id", "user_id", "image", "enroll_num", "re_reg_date", "name", "father_name", "mother_name", "admission_session", "admission_type", "year", "course_id", "sub_course_id", "re_reg", "status", "form", "form_submited_date"];
    public $order_re_reg_column  = [null, "user_id", "enroll_num", "re_reg_date", "name", "father_name", "mother_name", "admission_session", "admission_type", "course_id", "sub_course_id", "re_reg", "status"];

    public function __construct()
    {
        parent::__construct();
        $this->db      = \Config\Database::connect();
        $this->session = \Config\Services::session();
    }

    public function make_query()
    {
        $builder = $this->db->table($this->table);
        $builder->select($this->select_column);
        if ($_POST["search"]["value"]) {
            $builder->where("id", $_POST["search"]["value"]);
        }
        if (isset($_POST["order"])) {
            $builder->orderBy($this->order_column[$_POST["order"]['0']['column']], $_POST["order"]['0']['dir']);
        } else {
            $builder->orderBy("id", "DESC");
        }
        return $builder;
    }

    public function make_data_table()
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        $user_name = $this->session->get('user_name');
        $center_id = get_team_lead_center($user_name, 'array');
        if (empty($center_id) === TRUE) {
            $center_id = '0';
        }
        $builder = $this->make_query();
        if ($_POST["length"] != -1) {
            $builder->where('calender_from', $_POST["year"]);
            if ($_POST["type"] == 'total') {
                if ($user_type == '0') {
                    $builder->where('user_id', $user_id);
                } elseif ($user_type == '2') {
                    $builder->whereIn('user_id', $center_id);
                }
            }
            $builder->limit($_POST["length"], $_POST["start"]);
        }
        $query = $builder->get();
        return $query->getResult();
    }

    public function get_all_data($year, $type)
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        if ($type == 'total') {
            if ($user_type == '1') {
                $query = "SELECT count(id) as total_record FROM student WHERE status='1' AND calender_from='$year'";
            } elseif ($user_type == '2') {
                $user_name = $this->session->get('user_name');
                $center_id = get_team_lead_center($user_name, 'string');
                if (empty($center_id) === TRUE) {
                    $center_id = '0';
                }
                $query = "SELECT count(id) as total_record FROM student WHERE status='1'  AND user_id IN($center_id) AND calender_from='$year'";
            } else {
                $query = "SELECT count(id) as total_record FROM student WHERE status='1' AND user_id='$user_id' AND calender_from='$year'";
            }
        }
        $query  = $this->db->query($query);
        $result = $query->getResult();
        if ($result) {
            return $result[0]->total_record;
        } else {
            return 0;
        }
    }

    public function get_filtered_data($year, $type)
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        if ($type == 'total') {
            if ($user_type == '1') {
                $query = "SELECT count(id) as total_record FROM student WHERE status='1' AND calender_from='$year'";
            } elseif ($user_type == '2') {
                $user_name = $this->session->get('user_name');
                $center_id = get_team_lead_center($user_name, 'string');
                if (empty($center_id) === TRUE) {
                    $center_id = '0';
                }
                $query = "SELECT count(id) as total_record FROM student WHERE status='1'  AND user_id IN($center_id) AND calender_from='$year'";
            } else {
                $query = "SELECT count(id) as total_record FROM student WHERE status='1' AND user_id='$user_id' AND calender_from='$year'";
            }
        }
        $query  = $this->db->query($query);
        $result = $query->getResult();
        if ($result) {
            return $result[0]->total_record;
        } else {
            return 0;
        }
    }

    public function make_re_reg_query()
    {
        $builder = $this->db->table('student');
        $builder->select($this->select_re_reg_column);
        if ($_POST["search"]["value"]) {
            $builder->where("id", $_POST["search"]["value"]);
        }
        if (isset($_POST["order"])) {
            $builder->orderBy($this->order_re_reg_column[$_POST["order"]['0']['column']], $_POST["order"]['0']['dir']);
        } else {
            $builder->orderBy("id", "DESC");
        }
        return $builder;
    }

    public function make_re_reg_data_table()
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        $user_name = $this->session->get('user_name');
        $center_id = get_team_lead_center($user_name, 'array');
        if (empty($center_id) === TRUE) {
            $center_id = '0';
        }
        $builder = $this->make_re_reg_query();
        if ($_POST["length"] != -1) {
            if ($_POST["year"] == CURRENT_SESSION) {
                $builder->where('re_reg_date >=', '2019-11-21');
            } else {
                $builder->where('re_reg_date <=', '2019-11-21');
            }
            if ($_POST["type"] == 'total') {
                if ($user_type == '0') {
                    $builder->where('user_id', $user_id);
                } elseif ($user_type == '2') {
                    $builder->whereIn('user_id', $center_id);
                }
            }
            $builder->where('re_reg <>', '');
            $builder->orderBy('re_reg_date', 'DESC');
            $builder->limit($_POST["length"], $_POST["start"]);
        }
        $query = $builder->get();
        return $query->getResult();
    }

    public function get_all_re_reg_data($year, $type)
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        $user_name = $this->session->get('user_name');
        if ($user_type == '2') {
            $center_id = get_team_lead_center($user_name, 'array');
        }
        if ($type == 'total') {
            $builder = $this->db->table('student');
            if ($year == CURRENT_SESSION) {
                $builder->where('re_reg_date >=', '2019-11-21');
            } else {
                $builder->where('re_reg_date <=', '2019-11-21');
            }
            if ($user_type == '0') {
                $builder->where('user_id', $user_id);
            } elseif ($user_type == '2') {
                $builder->whereIn('user_id', $center_id);
            }
            $builder->where('re_reg <>', '');
            $builder->orderBy('re_reg_date', 'DESC');
            $query  = $builder->get();
            $result = $query->getNumRows();
        }
        if ($result) {
            return $result;
        } else {
            return 0;
        }
    }

    public function get_re_reg_filtered_data($year, $type)
    {
        $user_id   = $this->session->get('user_id');
        $user_type = $this->session->get('user_type');
        $user_name = $this->session->get('user_name');
        if ($user_type == '2') {
            $center_id = get_team_lead_center($user_name, 'array');
        }
        if ($type == 'total') {
            $builder = $this->db->table('student');
            if ($year == CURRENT_SESSION) {
                $builder->where('re_reg_date >=', '2019-11-21');
            } else {
                $builder->where('re_reg_date <=', '2019-11-21');
            }
            if ($user_type == '0') {
                $builder->where('user_id', $user_id);
            } elseif ($user_type == '2') {
                $builder->whereIn('user_id', $center_id);
            }
            $builder->where('re_reg <>', '');
            $builder->orderBy('re_reg_date', 'DESC');
            $query  = $builder->get();
            $result = $query->getNumRows();
        }
        if ($result) {
            return $result;
        } else {
            return 0;
        }
    }
}