<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Model_alumni extends CI_Model {

	public function get_alumni_list($year=NULL)
	{
		$this->db->select('a.id, a.name as alumni_name, h.file_name, h.file_location, 
							(SELECT concat(d.address, ", ", d.postcode, " ", d.city, ", ", e.state, ", ", f.country) as address 
								FROM user__address d 
								LEFT OUTER JOIN _ref__state e ON e.id = d.state_id
								LEFT OUTER JOIN _ref__country f ON f.id = d.country_id 
								WHERE d.id IN (SELECT MAX(id) FROM user__address WHERE is_delete = 0 GROUP BY user_id) 
								AND d.user_id = a.user_id
								AND d.is_delete = 0) as address, 
							(SELECT contact_no FROM user__address WHERE id IN (SELECT MAX(id) FROM user__address WHERE is_delete = 0 GROUP BY user_id) AND user_id = a.user_id AND is_delete = 0) as contact_no,
							concat(g.email, ", ", g.email_secondary) as email,
							(SELECT birth_date FROM user__profiling WHERE id IN (SELECT MAX(id) FROM user__profiling WHERE is_delete = 0 GROUP BY user_id) AND user_id = a.user_id AND is_delete = 0) as birth_date,
							YEAR(a.date_termination) as year_resigned, 0 as curr_employer, 0 as status');
		$this->db->from('user__employment_detail a');
		$this->db->join('user g', 'g.id = a.user_id', 'left outer');
		$this->db->join('user__picture h', 'g.profile_picture_id = h.id', 'left');
		$this->db->where('a.is_delete', 1);

		if(!empty($year)){
			$this->db->where('YEAR(a.date_termination)', $year);
		}

		$query1     = $this->db->get();
    	$subQuery1  = $this->db->last_query();

    	$this->db->select('a.id, a.name as alumni_name, a.file_name, a.file_location, concat(a.address, ", ", a.postcode, " ", a.city, ", ", b.state, ", ", c.country) as address, a.contact_no as contact_no, a.email as email, a.dob as birth_date, a.year_resigned as year_resigned, a.curr_employer, 1 as status');
    	$this->db->from('alumni a');
    	$this->db->join('_ref__state b', 'b.id = a.state_id', 'left outer');
		$this->db->join('_ref__country c', 'c.id = a.country_id', 'left');
    	$this->db->where('a.is_delete', 0);

    	if(!empty($year)){
			$this->db->where('a.year_resigned', $year);
		}

    	$query2     = $this->db->get();
    	$subQuery2  = $this->db->last_query();

    	$query = $this->db->query("SELECT * FROM ($subQuery1 UNION $subQuery2) as unionTable ORDER BY alumni_name ASC");

    	// echo $this->db->last_query();

		return ($query->num_rows() > 0) ? $query->result_array() : null;
	}

	public function save_alumni($data)
	{
		$add_alumni = $this->db->insert('alumni',$data);

		if($add_alumni){
			return $this->db->insert_id();
		}else{
			return false;
		}
	}

	public function update_alumni($sets = array(), $where = array())
	{
		foreach($sets as $column => $value)
		{
			$this->db->set($column, $value);
		}

		foreach ($where as $column => $value) 
		{
			$this->db->where($column,$value);
		}

		$this->db->update('alumni');

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

	function get_alumni_details_byID($alumni_id)
	{
		$this->db->select('*');
		$this->db->where('id', $alumni_id);
		$this->db->where('is_delete', 0);

		$query = $this->db->get('alumni');

		return ($query->num_rows() > 0) ? $query->result_array()[0] : null;
	}

}

?>