<?php

namespace App\Models;

use CodeIgniter\Model;

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

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

	public function get_faculty_name($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_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_courses_filter($department, $semester){
	    $query = '';
	    if($semester){
		    $query = $this->db->query("SELECT * FROM course WHERE department = '$department' AND semester = '$semester'");
	    }else{
		    $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_course_allocation($dept_id, $session){
		$query = $this->db->query("SELECT ca.id, course_id, title, semester, level, lec.name, programmes_allocated_to, session FROM course_allocation ca, staff lec, course c WHERE ca.dept_id = $dept_id AND ca.lecturer_id = lec.id 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_allocated($course_id, $session){
		$query = $this->db->query("SELECT * FROM course_allocation WHERE course_id = '$course_id' AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
	}

	public function check_allocation($staff_id, $course_id, $session){
		$builder = $this->db->table('course_allocation');
		$builder->select("*");
		$builder->where('lecturer_id',$staff_id);
		$builder->where('course_id',$course_id);
		$builder->where('session',$session);
		$result = $builder->get();
		if(count($result->getResultArray()) == 1){
			return $result->getRowArray();
		}else{
			return false;
		}
	}
	
	public function get_programmes($department){
		$query = $this->db->query("SELECT * FROM programme WHERE department_id = '$department'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
	}
	
	public function get_faculty_staffs($faculty){
		$query = $this->db->query("SELECT * FROM staff WHERE faculty = '$faculty' ORDER BY salaryscale DESC");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

	public function get_staffs($department){
		$query = $this->db->query("SELECT * FROM staff WHERE department = '$department' ORDER BY salaryscale DESC");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

	public function get_staff($department){
		$query = $this->db->query("SELECT * FROM staff WHERE department = '$department' AND id NOT IN (SELECT staff_id FROM exam_officer)");
		$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_level_coord($department){
		$query = $this->db->query("SELECT staff.name, level_coordinator.id, level_coordinator.staff_id, level_coordinator.email, level_coordinator.username, level_coordinator.dept_id, level_coordinator.programme_id, level_coordinator.session_started, level_coordinator.current_level, level_coordinator.created_at FROM staff, level_coordinator WHERE staff.id = level_coordinator.staff_id AND level_coordinator.dept_id = '$department' AND level_coordinator.status = 'active'");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

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

	public function check_level_coord($programme, $level, $session){
		$query = $this->db->query("SELECT * FROM level_coordinator WHERE programme_id = '$programme' AND status = 'active' AND current_level = '$level' AND session_started = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return true;
        }else{
            return false;
        }
	}

	/*public function delete_level_coord($id){
    	$this->db->table('level_coordinator')->update(['status' => 'deleted'], ['id'=> $id]);
    	if($this->db->affectedRows() == 1){
    		return true;
    	}else{
    		return false;
    	}
    }*/
    
    public function delete_level_coord($id){
    	$this->db->query("DELETE FROM level_coordinator WHERE id = $id");

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

    
    public function delete_course_allocation($id){
    	$this->db->query("DELETE FROM course_allocation WHERE id = $id");

        if($this->db->affectedRows() == 1){
            return true;
        }else{
            return false;
        }
    }
	 
	public function get_exam_officers($department){
		$query = $this->db->query("SELECT staff.name, exam_officer.id, exam_officer.staff_id, exam_officer.email, exam_officer.dept_id, exam_officer.programme_id, exam_officer.session, exam_officer.created_at FROM staff, exam_officer WHERE staff.id = exam_officer.staff_id AND exam_officer.dept_id = '$department' AND exam_officer.deleted = '0'");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

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

	public function check_exam_officer($programme, $session){
		$query = $this->db->query("SELECT * FROM exam_officer WHERE programme_id IN ($programme) AND session = '$session'");
        $result = $query->getResult();
        
        if(count($result) > 0){
            return true;
        }else{
            return false;
        }
	}

	public function delete_exam_officer($id){
    	$this->db->query("DELETE FROM exam_officer WHERE id = $id");
    	if($this->db->affectedRows() == 1){
    		return true;
    	}else{
    		return false;
    	}
    }

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

	public function get_students($programme, $level){
		$query = $this->db->query("SELECT * FROM student WHERE awarded_programme = $programme AND level = '$level' AND status = 'Student'");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

	public function get_all_students($programme){
		$query = $this->db->query("SELECT * FROM student WHERE status = 'Student' AND awarded_programme IN (SELECT id FROM programme WHERE department_id = $programme)");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

	public function count_all_students($programme){
		$query = $this->db->query("SELECT status, count(*) as num FROM `student` WHERE awarded_programme IN (SELECT id FROM programme WHERE department_id = $programme) GROUP BY status");
		$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' ORDER BY s.registration_no");
		$result = $query->getResult();
		
		if(count($result) > 0){
			return $result;
		}else{
			return false;
		}
	}

    public function get_registered_students($session, $programme, $level, $gender){
    	$query = '';
    	
    	if($gender == 'All'){
            $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 tx.session = '$session' AND st.awarded_programme = pr.id AND st.status = 'Student' ORDER BY st.registration_no");
    	}else{
    		$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 tx.session = '$session' AND st.awarded_programme = pr.id AND st.status = 'Student' AND pf.gender = '$gender' ORDER BY st.registration_no");
    	}
        $result = $query->getResult();
        
        if(count($result) > 0){
            return $result;
        }else{
            return false;
        }
    }

    public function get_students_by_stat($dept, $stat){
    	$query = '';
        
        $query = $this->db->query("SELECT pf.*, st.student_id, st.level, st.award_year, st.registration_no, pr.title FROM student st, profile pf, programme pr WHERE st.awarded_programme IN (SELECT id FROM programme WHERE department_id = '$dept') AND st.awarded_programme = pr.id AND pf.id = st.profile_id AND st.status = '$stat' ORDER BY st.registration_no");
        $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_details_by_ccode($code){
		$query = $this->db->query("SELECT * FROM programme WHERE ccode = '$code'");
		$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_programs_allocated($id){
		$builder = $this->db->table('course_allocation');
		$builder->select("*");
		$builder->where('id',$id);
		$result = $builder->get();
		if(count($result->getResultArray()) == 1){
			return $result->getRowArray();
		}else{
			return false;
		}
	}

	public function save_allocation($id, $data, $action){
        $builder = $this->db->table('course_allocation');
        if($action == 'save'){
        	$res = $builder->insert($data);
    	}else{
        	$res = $builder->update($data, ['id'=> $id]);
    	}
        if($this->db->affectedRows() == 1){
            return true;
        }else{
            return false;
        }
    }

	public function edit_allocation($id, $data){
    	$this->db->table('course_allocation')->update($data, ['id'=> $id]);
    	if($this->db->affectedRows() == 1){
    		return true;
    	}else{
    		return false;
    	}
	}

    public function get_uploaded_results($id, $semester, $session){
        $query = $this->db->query("SELECT rs.*, 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.dept_id = $id OR (lecturer_id LIKE '%GSP%' AND rs.programme_id IN (SELECT id FROM programme WHERE department_id = $id))) AND rs.semester = '$semester' 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_submitted_result_semester($id, $semester, $session){
		$query = $this->db->query("SELECT * FROM result_instance WHERE dept_id = $id 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_programme_submitted_result($programme, $semester, $session){
		$query = $this->db->query("SELECT * FROM result_instance WHERE programme_id = $programme 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 update_password($uid, $pass){
        
        $this->db->query("UPDATE department SET password = '$pass' WHERE id = '$uid'");

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

    public function get_semester($pro, $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;
		}
	}

}