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

class Model_user_family extends CI_Model {
	function __construct() {
		parent::__construct();
    }

    function find($id)
    {
        $this->db->where('id',$id);
        $query = $this->db->get('user__family_member');
        return ($query->num_rows() > 0)?$query->result():null;
    }

    function fetchFamilyInfo($staff_id = null, $latest = false, $profiling_id = null)
    {

        //inner query
        $this->db->select('MAX(profiling_id)');
        $this->db->from('user__family_member ');
        if($staff_id) $this->db->where('user_id',$staff_id);
        $this->db->where('is_delete',0);
        $max = $this->db->get_compiled_select();

        //outer query
        $this->db->select('a.*,
                        b.relations,
                        c.level as level_academic,
                        d.action_efective_date,
                        e.type as action_type');

        if($staff_id) $this->db->where('a.user_id',$staff_id);
        if($latest) $this->db->where("a.profiling_id IN ($max)");
        if($profiling_id) $this->db->where('a.profiling_id',$profiling_id);

        $this->db->where('a.is_delete',0);
        $this->db->join('_ref__relationship b','b.id = a.relation');
        $this->db->join('_ref__academic c','c.id = a.academic_lavel','left');
        $this->db->join('user__profiling d','d.id = a.profiling_id','left');
        $this->db->join('_ref__action_type e','e.id = d.action_type_id','left');

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

        // if($this->session->userdata('user')['id'] == 304)
        // {
            // debug($this->db->last_query());
        //     die();
        // }
        
        return ($query->num_rows() > 0)?$query->result():null; 
    }

    function createFamily($input)
    {
    	$data = array(
            'profiling_id'          => $input['profiling_id'],
    		'user_id' 	            => $input['staff_id'],
    		'name' 		            => $input['name'],
    		'ic_no'		            => $input['ic_no'],
    		'relation'	            => $input['relation'],
            // 'birthdate'             => date('Y-m-d',strtotime($input['birthdate'])),
    		'birthdate'             => (!empty($input['birthdate']))?$input['birthdate']:null,            
    		'phone_no'	            => $input['phone_no'],
    		'email'		            => $input['email'],
    		'address' 	            => $input['address'],
    		'occupation'            => $input['occupation'],
            'oku_status'            => $input['oku_status'],
            'study_status'          => $input['study_status'],
            'academic_lavel'        => $input['academic_lavel'],
            'academic_institution'  => $input['academic_institution'],
    		'is_delete'             => 0,
    	);

    	$this->db->insert('user__family_member',$data);

    	return $this->db->insert_id();
    }

    public function editFamily($input,$family_id)
    {
        $data = array(
            'user_id'   => $input['staff_id'],
            'name'      => $input['name'],
            'ic_no'     => $input['ic_no'],
            'relation'  => $input['relation'],
            //'birthdate' => $input['birthdate'],
            'birthdate' => (!empty($input['birthdate']))?$input['birthdate']:null,
            'phone_no'  => $input['phone_no'],
            'email'     => $input['email'],
            'address'   => $input['address'],
            'occupation'=> $input['occupation'],
            'oku_status'            => $input['oku_status'],
            'study_status'          => $input['study_status'],
            'academic_lavel'        => $input['academic_lavel'],
            'academic_institution'  => $input['academic_institution'],
            'is_delete' => 0,
        );

        $this->db->set($data);
        $this->db->where('id',$family_id);
        $this->db->update('user__family_member');
    }

    public function removeFamily($input,$family_id)
    {
        $data = array(
            'is_delete' => 1,
        );

        $this->db->set($data);
        $this->db->where('id',$family_id);
        $this->db->update('user__family_member');

        return true;
    }
}