<?php

namespace App\Models;

use CodeIgniter\Model;

class ExamOfficerModel extends Model
{

    public function verify_examofficer($email){
        $query = $this->db->query("SELECT lc.staff_id stfn, lc.name, lc.gsm, eo.* FROM staff lc, exam_officer eo WHERE lc.id = eo.staff_id AND eo.email = '$email'");
        $result = $query->getResultArray();
        if(count($result) == 1){
            return $result;
        }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_all_courses(){
		$query = $this->db->query("SELECT * FROM course");
        $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_program_ccode($ccode){
		$query = $this->db->query("SELECT * FROM programme WHERE id = '$ccode'");
		$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_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' ORDER BY registration_no");
        $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');");
        $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_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_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_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 edit_submission($data, $id){
    	$this->db->table('result_instance')->update($data, ['id'=> $id]);
    	if($this->db->affectedRows() > 0){
    		return true;
    	}else{
    		return $this->db->error();
    	}
	}

    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_submitted_result_semester($id, $username, $semester, $session){
		$query = $this->db->query("SELECT * FROM result_instance WHERE programme_id = $id AND programme_username = '$username' AND semester = '$semester' AND session = '$session'");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}
	
	public function get_submitted_results_($id, $semester, $session, $dept_id){
		$query = $this->db->query("SELECT * FROM result_instance WHERE programme_id = $id 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_results($id, $semester, $session, $dept_id){
		$query = $this->db->query("SELECT * FROM result_instance WHERE semester = '$semester' AND session = '$session' AND (dept_id='$dept_id' OR (programme_id = '$id' AND lecturer_id LIKE '%GSP%'))");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

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

	public function get_course_details($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_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_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_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_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_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 update_password($uid, $pass){
        
        $this->db->query("UPDATE exam_officer SET password = '$pass' WHERE id = '$uid'");

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

}