<?php

namespace App\Models;

use CodeIgniter\Model;

class GspModel extends Model
{

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

    public function get_courses(){
        $query = $this->db->query("SELECT * FROM course WHERE id LIKE '%GSP%' OR id LIKE '%GST%' group by id");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_courses_filter($semester, $level){
        $query = '';
        if($semester && $level){
            $query = $this->db->query("SELECT * FROM course WHERE (id LIKE '%GSP%' OR id LIKE '%GST%') AND semester = '$semester' AND level = '$level' group by id");
        }else if($semester){
            $query = $this->db->query("SELECT * FROM course WHERE (id LIKE '%GSP%' OR id LIKE '%GST%') AND semester = '$semester' group by id");
        }else if($level){
            $query = $this->db->query("SELECT * FROM course WHERE (id LIKE '%GSP%' OR id LIKE '%GST%') AND level = '$level' group by id");
        }else{
            $query = $this->db->query("SELECT * FROM course WHERE id LIKE '%GSP%' OR id LIKE '%GST%' group by id");
        }
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_programmes($course, $session){
        $query = $this->db->query("SELECT DISTINCT(st.awarded_programme), pr.title, pr.ccode FROM student_course_registration sc, student st, programme pr WHERE sc.session = '$session' AND sc.course_id = '$course' AND sc.student_id = st.student_id AND st.awarded_programme = pr.id;");
        $result = $query->getResult();

        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_programme_details($programme){
        $query = $this->db->query("SELECT p.title ptitle, d.title dtitle, f.title ftitle FROM programme p, department d, faculty f WHERE p.department_id = d.id AND d.faculty_id = f.id AND p.id = '$programme';");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_program_course($course, $session){
        $query = $this->db->query("SELECT DISTINCT(st.awarded_programme) FROM student_course_registration sc, student st WHERE sc.session = '$session' AND sc.course_id = '$course' AND sc.student_id = st.student_id;");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_faculty_name($id){
        $query = $this->db->query("SELECT fac.title FROM faculty fac, department dept WHERE fac.id = dept.faculty_id AND dept.id = $id");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

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

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

    public function get_course_allocation($session){
        $query = $this->db->query("SELECT id, title, credit_unit, semester, level FROM course WHERE id LIKE '%GSP%' OR id LIKE '%GST%' group by id;");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_previous_course_allocation($staff_no, $session){
        $query = $this->db->query("SELECT id, title, credit_unit, c.semester, level, session, 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;");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_uploaded_results($session){
        $query = $this->db->query("SELECT rs.id, rs.course_id, co.title, rs.level, rs.semester, rs.dept_id, rs.session, p.title AS programme_title, rs.submission_status, rs.created_at, rs.updated_at FROM result_instance rs, course co, programme p WHERE rs.course_id LIKE 'GSP%' AND rs.session = '$session' AND rs.course_id = co.id AND rs.programme_id = p.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, grade 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_current_result($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_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_program_details($programme){
		$query = $this->db->query("SELECT * FROM programme WHERE id = $programme");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

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

    /*public function get_allocated_programmes($course, $session){
        $builder = $this->db->table('course_allocation');
        $builder->select("*");
        $builder->where('course_id',$course);
        $builder->where('lecturer_id',$staff_no);
        $builder->where('session',$session);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }*/

    public function get_allocated_programmes($course, $session){
        $query = $this->db->query("SELECT DISTINCT s.awarded_programme, p.title FROM student_course_registration sr, student s, programme p WHERE sr.session = '$session' AND sr.course_id = '$course' AND sr.student_id = s.student_id AND s.awarded_programme = p.id;");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function check_attendance($course, $date){
        $query = $this->db->query("SELECT * FROM student_attendance WHERE course_id = '$course' AND date = '$date';");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }
    
    public function get_registered_students($programme, $course, $session){
        $query = $this->db->query("SELECT st.student_id, first_name, last_name, middle_name, registration_no, st.level, pf.phone FROM student st, profile pf, student_course_registration sc  WHERE st.awarded_programme IN ($programme) AND sc.course_id  = '$course' AND st.student_id = sc.student_id AND pf.id = st.profile_id AND session = '$session' ORDER BY registration_no");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function check_allocation_instance($staff_id, $session){
        $query = $this->db->query("SELECT * FROM course_allocation WHERE lecturer_id = '$staff_id' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return true;
        }else{
            return false;
        }
    }

    public function check_result_instance($id){
        $builder = $this->db->table('result_instance');
        $builder->select("*");
        $builder->where('id',$id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }
    
    public function get_allocation_detail($course_id, $lecturer_id, $session){
        $builder = $this->db->table('course_allocation');
        $builder->select("*");
        $builder->where('course_id',$course_id);
        $builder->where('lecturer_id',$lecturer_id);
        $builder->where('session',$session);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }
    
    public function get_allocation_details($course_id, $lecturer_id){
        $builder = $this->db->table('course_allocation');
        $builder->select("*");
        $builder->where('course_id',$course_id);
        $builder->where('lecturer_id',$lecturer_id);
        $result = $builder->get();
        if(count($result->getResultArray()) == 1){
            return $result->getRowArray();
        }else{
            return false;
        }
    }

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

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

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

    public function update_marks($result_instance_id, $reg_no, $ca_marks, $exam_marks, $total_marks, $grade){
        
        $this->db->query("UPDATE rps_exam_marks SET ca_marks = '$ca_marks', exam_marks = '$exam_marks', total_marks = '$total_marks', grade = '$grade' WHERE reg_no = '$reg_no' AND result_instance_id = '$result_instance_id'");

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

    public function update_result_instance($result_instance_id, $date_time){
        $this->db->query("UPDATE result_instance SET updated_at = '$date_time' WHERE id = '$result_instance_id'");

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

    public function submit_result_instance($result_instance_id){
        $this->db->query("UPDATE result_instance SET submission_status = 'Completed', eo_status = 'Pending', hod_status = 'Pending' WHERE id = '$result_instance_id'");

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

    public function delete_marks($result_instance_id){
        $this->db->query("DELETE FROM rps_exam_marks WHERE result_instance_id = '$result_instance_id'");

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


    public function delete_result_instance($result_instance_id){
        $this->db->query("DELETE FROM result_instance WHERE id = '$result_instance_id'");

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

    /*public function get_enrolled_student_list($staff_no, $course, $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, st.level FROM student st, profile pf, student_course_registration sc WHERE st.awarded_programme = $programme AND sc.course_id = 'CSC1201' AND st.student_id = sc.student_id AND pf.id = st.profile_id AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }*/

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

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

}