<?php

namespace App\Models;

use CodeIgniter\Model;

class LevelCoordinatorModel extends Model
{

    public function verify_levelcoordinator($username){
        $builder = $this->db->table('level_coordinator');
        $builder->select("*");
        $builder->where('stfn',$username);
        $builder->orWhere('email',$username);
        $builder->Where('status','active');
        $result = $builder->get();
        if(count($result->getResultArray()) > 0){
            return $result->getRowArray();
        }else{
            return false;
        }
    }
    
    public function get_number_of_programmes_allocated($username){
        $builder = $this->db->table('level_coordinator');
        $builder->select("*");
        $builder->where('stfn',$username);
        $builder->orWhere('email',$username);
        $result = $builder->get();
        if(count($result->getResultArray()) > 0){
            return count($result->getResultArray());
        }else{
            return false;
        }
    }
    
    public function final_verify_levelcoordinator($username, $programme){
        $builder = $this->db->table('level_coordinator');
        $builder->select("*");
        $builder->where('email',$username);
        $builder->Where('programme_id',$programme);
        $builder->Where('status','active');
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }
    
    public function get_department_details($dept_id){
        $builder = $this->db->table('department');
        $builder->select("*");
        $builder->where('id',$dept_id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }
    
    public function get_faculty_details($id){
        $builder = $this->db->table('faculty');
        $builder->select("*");
        $builder->where('id',$id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }
    
    public function get_programmes_levels($username){
        $query = $this->db->query("SELECT * FROM level_coordinator WHERE email = '$username' OR stfn = '$username'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_staff_name($username){
        $builder = $this->db->table('staff');
        $builder->select("*");
        $builder->where('staff_id',$username);
        $builder->orWhere('email',$username);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function get_level_details($username){
        $builder = $this->db->table('level_coordinator');
        $builder->select("*");
        $builder->where('username',$username);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function get_programme_name($id){
        $builder = $this->db->table('programme');
        $builder->select("title");
        $builder->where('id',$id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function get_courses($department){
        $query = $this->db->query("SELECT * FROM course WHERE department = '$department'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_selected_courses($programme){
        $query = $this->db->query("SELECT * FROM course_selection WHERE selected_programme = '$programme' ORDER BY status");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function delete_selected_course($id){
        $builder = $this->db->table('course_selection');
        $res = $builder->where('code', $id)->delete();
        if($this->db->affectedRows() == 1){
            return true;
        }else{
            false;
        }
    }

    public function get_available_courses($programme){
        $query = $this->db->query("SELECT * FROM course where id NOT IN (SELECT code FROM course_selection WHERE selected_programme = $programme)");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_courses_by_programme($id){
        $query = $this->db->query("SELECT * FROM course WHERE programme_id = $id");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_students($programme, $level){
        $query = $this->db->query("SELECT st.student_id, first_name, last_name, middle_name, registration_no, st.level FROM student st, profile pf WHERE st.awarded_programme = $programme AND pf.id = st.profile_id AND st.level = '$level' ORDER BY registration_no");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    /*public function get_registered_students($programme, $level){
        $query = $this->db->query("SELECT st.student_id, first_name, last_name, middle_name, registration_no, st.level FROM student st, profile pf WHERE st.awarded_programme = $programme AND pf.id = st.profile_id AND st.level = '$level'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }*/
    public function count_all_students($programme, $level, $session){
        $query = $this->db->query("SELECT student.status, count(*) as num FROM `student`, tx WHERE awarded_programme = '$programme' AND student.profile_id = tx.profile_id AND tx.level = '$level' AND tx.status != 'Not Verified' AND tx.session = '$session' GROUP BY student.status");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_registered_students($programme, $level, $session){
        $query = $this->db->query("SELECT pf.*, st.student_id, tx.level, st.award_year, st.registration_no, pr.title FROM student st, profile pf, programme pr, tx WHERE st.awarded_programme = '$programme' AND pf.id = st.profile_id AND pf.id = tx.profile_id AND tx.level = '$level' AND st.awarded_programme = pr.id AND st.status = 'Student' AND tx.status != 'Not Verified' AND tx.session = '$session' ORDER BY st.registration_no");
        
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_transfered_students($programme, $level, $year){
        
        $query = $this->db->query("SELECT p.id, p.last_name, p.first_name, p.middle_name, s.registration_no, p.utme_no, s.level FROM profile p, student s, transfer t WHERE p.id = s.profile_id AND t.award_YEAR = $year AND s.award_year = $year AND t.awarded_level = $level AND s.awarded_programme = $programme AND t.awarded_programme = $programme AND p.utme_no = t.utme;");
        
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_student_courses($sid, $session){
        $query = $this->db->query("SELECT * FROM student_course_registration WHERE student_id = '$sid' AND session = '$session' ORDER BY semester ASC, course_id;");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_verify_grade($sid, $course, $session){
        $query = $this->db->query("SELECT rp.* FROM rps_exam_marks rp, result_instance rs, student st WHERE rs.course_id = '$course' AND rp.course_id = '$course' AND st.student_id = '$sid' AND rp.result_instance_id = rs.id AND st.registration_no = rp.reg_no AND rs.session = '$session';");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    /*public function get_student_courses($reg_no){
        $query = $this->db->query("SELECT * FROM student_course_registration WHERE student_id = (SELECT student_id FROM student WHERE registration_no = '$reg_no') ORDER BY semester ASC, course_id;");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_student_details($id){
        $query = $this->db->query("SELECT last_name, first_name, middle_name, gender, registration_no, level FROM profile p,  student s WHERE p.id = '$id' AND s.profile_id = '$id'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }*/

    public function get_student_details($id){
        $query = $this->db->query("SELECT p.*, s.student_id, s.level, s.award_year, s.registration_no FROM profile p,  student s WHERE p.id = '$id' AND s.profile_id = '$id'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_transfer_grades($id){
        $query = $this->db->query("SELECT * FROM transfer_grades WHERE profile_id = '$id'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_transfer_grade_status($id){
        $builder = $this->db->table('transfer_grades');
        $builder->select("*");
        $builder->where('profile_id',$id);
        $result = $builder->get();
        if(count($result->getResultArray()) > 0){
            return true;
        }else{
            return false;
        }
    }

    public function get_registration_status($id, $session){
        $builder = $this->db->table('student_course_registration');
        $builder->select("*");
        $builder->where('student_id',$id);
        $builder->where('Session',$session);
        $result = $builder->get();
        if(count($result->getResultArray()) > 0){
            return true;
        }else{
            return false;
        }
    }
    
    /*public function get_score_scheet_courses($programme, $session, $semester, $level){
        $query = $this->db->query("");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }*/

    public function get_submission_instance($programme, $session, $semester, $level, $ccode){
        $query = $this->db->query("SELECT DISTINCT(sc.course_id), co.title, co.level, ca.lecturer_id FROM student st, profile pf, student_course_registration sc, course co, course_allocation ca WHERE st.awarded_programme = $programme AND co.id = sc.course_id AND st.student_id = sc.student_id AND pf.id = st.profile_id AND sc.level = $level AND sc.semester = '$semester' AND sc.session = '$session' AND sc.course_id = ca.course_id AND ca.programmes_allocated_to LIKE '%$ccode%'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_submission_instance_result_($programme, $session, $semester, $level){
        $query = $this->db->query("SELECT  DISTINCT(course_id), co.title, co.level FROM student st, profile pf, student_course_registration sc, course co WHERE st.awarded_programme = $programme  AND co.id = sc.course_id AND st.student_id = sc.student_id AND pf.id = st.profile_id AND sc.level = $level AND sc.semester = '$semester' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
     public function get_student_registered_courses($programme, $session, $level){
        $query = $this->db->query("SELECT DISTINCT(course_id), co.title, co.level, sc.semester FROM student st, profile pf, student_course_registration sc, course co, tx t WHERE st.awarded_programme = $programme AND co.id = sc.course_id AND st.student_id = sc.student_id AND pf.id = st.profile_id AND pf.id = t.profile_id AND sc.level = $level AND t.level = $level AND sc.session = '$session';");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_student_registered_semester_courses($programme, $semester, $session, $level){
        $query = $this->db->query("SELECT DISTINCT(course_id), co.title, co.level, sc.semester FROM student st, profile pf, student_course_registration sc, course co, tx t WHERE st.awarded_programme = $programme AND co.id = sc.course_id AND st.student_id = sc.student_id AND pf.id = st.profile_id AND pf.id = t.profile_id AND sc.level = $level AND t.level = $level AND sc.semester = '$semester' AND sc.session = '$session';");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_enrolled_students($programme, $course, $level, $session){
        //$query = $this->db->query("SELECT last_name, first_name, middle_name, registration_no, level, programmes_allocated_to FROM course c,  course_allocation ca WHERE ca.lecturer_id = '$staff_no' AND ca.session = '$session' AND ca.course_id = c.id;");
        $query = $this->db->query("SELECT st.student_id, first_name, last_name, middle_name, registration_no, email, tx.level, pf.phone FROM student st, profile pf, student_course_registration sc, tx  WHERE st.awarded_programme = $programme AND sc.course_id  = '$course' AND st.student_id = sc.student_id AND pf.id = st.profile_id AND pf.id = tx.profile_id AND tx.level = $level AND tx.session = '$session' AND sc.session = '$session' ORDER BY registration_no");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_submission_instance_result($username, $session, $semester){
        $query = $this->db->query("SELECT * FROM result_instance WHERE programme_username = '$username' AND semester = '$semester' AND session = '$session' AND eo_status = 'Accepted'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_submitted_result($id, $username, $level, $session){
        $query = $this->db->query("SELECT * FROM result_instance WHERE programme_id = $id AND programme_username = '$username' AND level = '$level' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_programme_submitted_result($username, $semester, $session){
        $query = $this->db->query("SELECT * FROM result_instance WHERE programme_username = '$username' AND semester = '$semester' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_submission_detail($id){
        $query = $this->db->query("SELECT * FROM rps_exam_marks WHERE result_instance_id = '$id'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_mbbs_submitted_result_semester($username, $semester, $session){
        $query = $this->db->query("SELECT DISTINCT(dept_id) FROM result_instance WHERE programme_username = '$username' AND semester = '$semester' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_mbbs_submitted_result_semester_($dept_id, $username, $semester, $session){
        $query = $this->db->query("SELECT * FROM result_instance WHERE programme_username = '$username' AND semester = '$semester' AND session = '$session' AND dept_id = '$dept_id'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_submitted_result_semester($username, $semester, $session){
        $query = $this->db->query("SELECT * FROM result_instance WHERE programme_username = '$username' AND semester = '$semester' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_overall_courses($id, $username){
        $query = $this->db->query("SELECT * FROM result_instance WHERE programme_id = $id AND programme_username = '$username'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_submission_status__($course, $programme_username, $session){
        $query = $this->db->query("SELECT * FROM result_instance WHERE course_id = '$course' AND programme_username = '$programme_username' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return true;
        }else{
            return false;
        }
    }

    public function get_submission_status($course, $programme_username, $session){
        $builder = $this->db->table('result_instance');
        $builder->select("*");
        $builder->where('course_id',$course);
        $builder->where('programme_username',$programme_username);
        $builder->where('session',$session);
        $result = $builder->get();
        
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }
    
    public function get_mbbs_marks($id, $reg){
        $builder = $this->db->table('rps_exam_marks');
        $builder->select("*");
        $builder->where('result_instance_id',$id);
        $builder->where('reg_no',$reg);
        $result = $builder->get();
        
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function get_session($programme){
        $query = $this->db->query("SELECT * FROM result_instance");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_semester($programme, $session){
        $query = $this->db->query("SELECT DISTINCT(semester) FROM result_instance WHERE programme_username = '$programme' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_registered_courses($programme, $session){
        $query = $this->db->query("SELECT course_id FROM result_instance WHERE programme_id = $programme AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_score_sheet($id){
        $query = $this->db->query("SELECT * FROM rps_exam_marks WHERE result_instance_id = '$id'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_marks($id){
        $query = $this->db->query("SELECT first_name, last_name, middle_name, rp.reg_no, ca_marks, exam_marks, total_marks FROM profile pr, student st, rps_exam_marks rp WHERE rp.result_instance_id = '$id' AND rp.reg_no = st.registration_no AND pr.id = st.profile_id ORDER BY rp.reg_no; ");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_student_marks($reg_no, $id){
        $builder = $this->db->table('rps_exam_marks');
        $builder->select("*");
        $builder->where('reg_no',$reg_no);
        $builder->where('result_instance_id',$id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function get_course_detail_($id){
        $query = $this->db->query("SELECT co.id, title FROM course co, result_instance rs WHERE rs.id = '$id' AND rs.course_id = co.id; ");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_distinct_course_semester($id){
        $query = $this->db->query("SELECT DISTINCT(semester) FROM course WHERE id = '$id'; ");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_course_detail($id){
        $builder = $this->db->table('course');
        $builder->select("*");
        $builder->where('id',$id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function get_program_detail($id){
        $builder = $this->db->table('programme');
        $builder->select("*");
        $builder->where('id',$id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }


    public function get_total_credits($student_id){
        $builder = $this->db->table('student_course_registration');
        $builder->select("SUM(credit_units) AS total_credits");
        $builder->where('student_id',$student_id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function get_student_id($reg_no){
        $builder = $this->db->table('student');
        $builder->select("*");
        $builder->where('registration_no', $reg_no);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function get_students_profile($id){
        $builder = $this->db->table('profile');
        $builder->select("*");
        $builder->where('id', $id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

    public function update_credit_limit($username, $minimum, $maximum){
        
        $this->db->query("UPDATE level_coordinator SET minimum_credits = '$minimum', maximum_credits = '$maximum' WHERE username = '$username'");

        if($this->db->affectedRows() == 1){
            return true;
        }else{
            return false;
        }
    }
    
    public function save_transfer_grade($data){
        
        $builder = $this->db->table('transfer_grades');
        $res = $builder->insert($data);
        if($this->db->affectedRows() == 1){
            return true;
        }else{
            false;
        }
    }

    public function save_core_courses($data){
        
        $builder = $this->db->table('course_selection');
        $res = $builder->insert($data);
        if($this->db->affectedRows() == 1){
            return true;
        }else{
            false;
        }
    }

    public function save_elective_courses($data){
        
        $builder = $this->db->table('course_selection');
        $res = $builder->insert($data);
        if($this->db->affectedRows() == 1){
            return true;
        }else{
            false;
        }
    }

    public function update_password($uid, $pass){
        
        $this->db->query("UPDATE level_coordinator SET password = '$pass' WHERE username = '$uid'");

        if($this->db->affectedRows() == 1){
            return true;
        }else{
            return false;
        }
    }
    
    public function save_verify_result($reg_no, $instance, $ca, $exam, $total, $grade){
        
        $this->db->query("UPDATE rps_exam_marks SET ca_marks = '$ca', exam_marks = '$exam', total_marks = '$total', grade = '$grade' WHERE reg_no = '$reg_no' AND result_instance_id = '$instance'");

        if($this->db->affectedRows() == 1){
            return true;
        }else{
            return false;
        }
    }

}