<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Dashboard_Model extends MY_Model {

    function __construct() {
        parent::__construct();
  
    }

    public function get_message_list($type) {

        $this->db->select('MR.*, M.*');
        $this->db->from('message_relationships AS MR');
        $this->db->join('messages AS M', 'M.id = MR.message_id', 'left');

        if ($type == 'draft') {
            $this->db->where('MR.status', 1);
            $this->db->where('MR.is_draft', 1);
            $this->db->where('MR.owner_id', logged_in_user_id());
            $this->db->where('MR.sender_id', logged_in_user_id());
        }
        if ($type == 'inbox') {
            $this->db->where('MR.status', 1);
            $this->db->where('MR.owner_id', logged_in_user_id());
            $this->db->where('MR.receiver_id', logged_in_user_id());
        }
        if ($type == 'new') {
            $this->db->where('MR.status', 1);
            $this->db->where('MR.owner_id', logged_in_user_id());
            $this->db->where('MR.is_read', 0);
            $this->db->where('MR.receiver_id', logged_in_user_id());
        }
        if ($type == 'trash') {
            $this->db->where('MR.status', 1);
            $this->db->where('MR.is_trash', 1);
            $this->db->where('MR.owner_id', logged_in_user_id());
        }
        if ($type == 'sent') {
            $this->db->where('MR.status', 1);
            $this->db->where('MR.is_draft', 0);
            $this->db->where('MR.is_trash', 0);
            $this->db->where('MR.sender_id', logged_in_user_id());
            $this->db->where('MR.owner_id', logged_in_user_id());
        }

        return $this->db->get()->result();
    }

    public function get_user_by_role($school_id = null) {

        $this->db->select('COUNT(U.role_id) AS total_user, R.name');
        $this->db->from('users AS U');
        $this->db->join('roles AS R', 'R.id = U.role_id', 'left');
        $this->db->group_by('U.role_id');
        $this->db->where('U.status', 1);
        if ($school_id) {
            $this->db->where('U.school_id', $school_id);
        }
        return $this->db->get()->result();
    }

    public function get_student_by_class($school_id = null) {

        $this->db->select('COUNT(E.student_id) AS total_student, C.name AS class_name');
        $this->db->from('enrollments AS E');
        $this->db->join('classes AS C', 'C.id = E.class_id', 'left');
        $this->db->group_by('E.class_id');
        $this->db->where('E.status', 1);
        $this->db->where('E.status', 1);
        if ($school_id) {
            $this->db->where('E.school_id', $school_id);
        }
        return $this->db->get()->result();
    }

    public function get_total_student($school_id = null) {

        if ($this->session->userdata('role_id') == STUDENT) {
            
            $class_id = $this->session->userdata('class_id');
            $school = $this->get_single('schools', array('id' => $school_id));
            
            $this->db->select('COUNT(E.id) AS total_student');
            $this->db->from('enrollments AS E');
            if ($school_id) {
                $this->db->where('E.school_id', $school_id);
            }
            if ($class_id) {
                $this->db->where('E.class_id', $class_id);
            }
            if ($school) {
                $this->db->where('E.academic_year_id', $school->academic_year_id);
            }
            

        }else if ($this->session->userdata('role_id') == GUARDIAN) {
            
            $this->db->select('COUNT(S.id) AS total_student');
            $this->db->from('students AS S');
            $this->db->where('S.status', 1);
            if ($school_id) {
                $this->db->where('S.school_id', $school_id);
            }
            $this->db->where('S.guardian_id',  $this->session->userdata('profile_id'));
          
            
        } else {            
          
            $this->db->select('COUNT(S.id) AS total_student');
            $this->db->from('students AS S');
            $this->db->where('S.status', 1);
            if ($school_id) {
                $this->db->where('S.school_id', $school_id);
            }
        }

        return $this->db->get()->row()->total_student;
    }

    public function get_total_guardian($school_id = null) {

        if ($this->session->userdata('role_id') == STUDENT) {

            $profile_id = $this->session->userdata('profile_id');
            $student = $this->get_single('students', array('id' => $profile_id));

            $this->db->select('COUNT(G.id) AS total_guardian');
            $this->db->from('guardians AS G');
            $this->db->where('G.id', $student->guardian_id);
           
        } else {

            $this->db->select('COUNT(G.id) AS total_guardian');
            $this->db->from('guardians AS G');
        }

         $this->db->where('G.status', 1);
        if ($school_id) {
            $this->db->where('G.school_id', $school_id);
        }
        return $this->db->get()->row()->total_guardian;
    }

    public function get_total_class($school_id = null) {

        $this->db->select('COUNT(C.id) AS total_class');
        $this->db->from('classes AS C');
        $this->db->where('C.status', 1);
        if ($school_id) {
            $this->db->where('C.school_id', $school_id);
        }
        return $this->db->get()->row()->total_class;
    }

    public function get_total_teacher($school_id = null) {

        $this->db->select('COUNT(T.id) AS total_teacher');
        $this->db->from('teachers AS T');
        $this->db->where('T.status', 1);
        if ($school_id) {
            $this->db->where('T.school_id', $school_id);
        }
        return $this->db->get()->row()->total_teacher;
    }

    public function get_total_employee($school_id = null) {

        $this->db->select('COUNT(E.id) AS total_employee');
        $this->db->from('employees AS E');
        $this->db->where('E.status', 1);
        if ($school_id) {
            $this->db->where('E.school_id', $school_id);
        }
        return $this->db->get()->row()->total_employee;
    }

    public function get_total_expenditure($school_id = null) {

        $this->db->select('SUM(E.amount) AS total_expenditure');
        $this->db->from('expenditures AS E');
        if ($school_id) {
            $this->db->where('E.school_id', $school_id);
        }
        return $this->db->get()->row()->total_expenditure;
    }

    public function get_total_income($school_id = null) {
             
        $this->db->select('SUM(T.amount) AS total_income');
        $this->db->from('transactions AS T');

        if ($school_id) {
            $this->db->where('T.school_id', $school_id);
        }
        return $this->db->get()->row()->total_income;
    }
    
    
    
    
    public function get_search_student($school_id, $keyword){
        
        $school = $this->get_school_by_id($school_id);
      
        if(empty($school)){
            return;
        }        
        $this->db->select('S.id, S.name, S.photo, S.dob, E.roll_no, C.name AS class_name, SE.name AS section, AY.session_year');
        $this->db->from('enrollments AS E');
        $this->db->join('classes AS C', 'C.id = E.class_id', 'left');
        $this->db->join('sections AS SE', 'SE.id = E.section_id', 'left');
        $this->db->join('academic_years AS AY', 'AY.id = E.academic_year_id', 'left');
        $this->db->join('students AS S', 'S.id = E.student_id', 'left');
        
        if ($school_id) {
            $this->db->where('E.school_id', $school_id);
        }
        
        $this->db->where('E.academic_year_id', $school->academic_year_id);
        
        $this->db->like('S.name', $keyword);
        $this->db->or_like('S.email', $keyword, 'after');
        $this->db->or_like('S.phone', $keyword, 'after');
        $this->db->or_like('S.present_address', $keyword, 'after');
        $this->db->or_like('S.permanent_address', $keyword, 'after');
        $this->db->or_like('C.name', $keyword, 'after');
        $this->db->or_like('SE.name', $keyword, 'after');
        
       return $this->db->get()->result();
        
    }
    
    public function get_search_guardian($school_id, $keyword){
        
        $this->db->select('G.*');
        $this->db->from('guardians AS G');
        $this->db->where('G.status', 1);
        if ($school_id) {
            $this->db->where('G.school_id', $school_id);
        }
        
        $this->db->like('G.name', $keyword);
        $this->db->or_like('G.email', $keyword, 'after');
        $this->db->or_like('G.phone', $keyword, 'after');
        $this->db->or_like('G.profession', $keyword, 'after');
        $this->db->or_like('G.present_address', $keyword, 'after');
        $this->db->or_like('G.permanent_address', $keyword, 'after');
        
       return $this->db->get()->result();
       
    }
    
    public function get_search_teacher($school_id, $keyword){
        
        $this->db->select('T.*, D.title AS department');
        $this->db->from('teachers AS T');
        $this->db->join('departments AS D', 'D.id = T.department_id', 'left');
        $this->db->where('T.status', 1);
        if ($school_id) {
            $this->db->where('T.school_id', $school_id);
        }
        
        $this->db->like('T.name', $keyword);
        $this->db->or_like('T.email', $keyword, 'after');
        $this->db->or_like('T.phone', $keyword, 'after');
        $this->db->or_like('T.present_address', $keyword, 'after');
        $this->db->or_like('T.permanent_address', $keyword, 'after');
        $this->db->or_like('D.title', $keyword, 'after');
        
       return $this->db->get()->result();
       
    }
    
    public function get_search_employee($school_id, $keyword){
     
        $this->db->select('E.*, D.name AS designation');
        $this->db->from('employees AS E');
        $this->db->join('designations AS D', 'D.id = E.designation_id', 'left');
        
        $this->db->where('E.status', 1);
        if ($school_id) {
            $this->db->where('E.school_id', $school_id);
        }
        
        $this->db->like('E.name', $keyword);
        $this->db->or_like('D.name', $keyword);
        $this->db->or_like('E.email', $keyword, 'after');
        $this->db->or_like('E.phone', $keyword, 'after');
        $this->db->or_like('E.present_address', $keyword, 'after');
        $this->db->or_like('E.permanent_address', $keyword, 'after');
        
       return $this->db->get()->result();
    }

}
