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

class Model_petty_cash_advance extends CI_Model {

	function __construct()
	{
		parent::__construct();
		if(ENVIRONMENT == 'production'){
			$this->erp_db = "spserp";
			$this->mgs_db = "mgsdb";
		} else if(ENVIRONMENT == 'development'){
			$this->erp_db = "spserp_dev";
			$this->mgs_db = "mgsdb_dev";
		}
	}

	public function getPettyCashAdvanceList($ID = null, $requester_id = null, $department_id = null, $year = null)
	{
		$query = $this->db->select('a.*, group_concat(g.id separator "->") as description_id, group_concat(g.description separator "->") as description, group_concat(b.remark separator "->") as description_remark, group_concat(b.amount separator "->") as description_amount')
		->select('(select group_concat(user_id separator "->") from sv__petty_cash_advance_member where petty_cash_advance_id = a.ID and is_delete = 0 group by a.ID) as member_id, (select group_concat(department_id separator "->") from sv__petty_cash_advance_member where petty_cash_advance_id = a.ID and is_delete = 0 group by a.ID) as member_department_id')
		->select('m.ID as photoID, m.photo_title, m.file_location')
		->select('l.name as customerName, k.description as job_description')
		->select('d.name as dept_name, e.name as branchName, f.name as entity_name')
		->select('c.staff_id as requester_staff_id, c.name as requester_name, c.email as requester_email')
		->select('h.staff_id as approved_by_staff_id, h.name as approved_by_name, h.email as approved_by_email')
		->select('i.staff_id as prepared_by_staff_id, i.name as prepared_by_name, i.email as prepared_by_email')
		->join('sv__petty_cash_advance_description b','b.petty_cash_advance_id = a.ID and b.is_delete = 0','left')
		->join('user c','c.id = a.requester_id and c.is_delete = 0')
		->join('company__department d','d.id = a.department_id')
		->join('company__branch e','e.id = d.branch_id')
		->join('company__business_entity f','f.id = a.business_entity_id','left')
		->join('sv__ref_petty_cash_advance_description g','g.id = b.description_id','left')
		->join('user h','h.id = a.approvedBY and h.is_delete = 0','left')
		->join('user i','i.id = a.preparedBY and i.is_delete = 0','left')
		->join($this->mgs_db.'.view__job k','k.ID = a.job_id','left')
		->join('customer l','l.id = a.client_id','left')
		->join('sv__petty_cash_advance_upload m','m.petty_cash_advance_id = a.ID and m.is_delete = 0','left')
		->where('a.is_delete',0)
		->group_by('a.ID')
		->order_by('a.ID desc');

		if($ID)
		{
			$this->db->where('a.ID',$ID);
		}

		if(!empty($requester_id))
		{
			$this->db->where('a.requester_id',$requester_id);
            // $this->db->or_where('a.createdBY',$requester_id);
		}

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

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

		$query = $this->db->get('sv__petty_cash_advance a')
		->result_array();
		// die(debug($query));
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getCashAdvanceListing($ID)
	{
		$query = $this->db->select('a.*, group_concat(g.id separator "->") as description_id, group_concat(g.description separator "->") as description, group_concat(b.remark separator "->") as description_remark, group_concat(b.base_amount separator "->") as base_amount, group_concat(b.sst_amount separator "->") as sst_amount, group_concat(b.total separator "->") as total')
		->select('d.name as dept_name, e.name as branchName, f.name as entity_name')
		->select('c.staff_id as requester_staff_id, c.name as requester_name, c.email as requester_email')
		->select('h.staff_id as approved_by_staff_id, h.name as approved_by_name, h.email as approved_by_email')
		->select('i.staff_id as prepared_by_staff_id, i.name as prepared_by_name, i.email as prepared_by_email')
		->select('m.staff_id as listing_approved_by_staff_id, m.name as listing_approved_by_name, m.email as listing_approved_by_email')
		->select('n.staff_id as listing_received_by_staff_id, n.name as listing_received_by_name, n.email as listing_received_by_email')
		->select('l.name as customerName, k.description as job_description')
		->join('sv__petty_cash_advance_listing b','b.petty_cash_advance_id = a.ID and b.is_delete = 0','left')
		->join('user c','c.id = a.requester_id and c.is_delete = 0')
		->join('company__department d','d.id = a.department_id')
		->join('company__branch e','e.id = d.branch_id')
		->join('company__business_entity f','f.id = a.business_entity_id')
		->join('sv__ref_petty_cash_advance_description g','g.id = b.description_id','left')
		->join('user h','h.id = a.approvedBY and h.is_delete = 0','left')
		->join('user i','i.id = a.preparedBY and i.is_delete = 0','left')
		->join($this->mgs_db.'.view__job k','k.ID = a.job_id','left')
		->join('customer l','l.id = a.client_id','left')
		->join('user m','m.id = a.listing_approvedBY and h.is_delete = 0','left')
		->join('user n','n.id = a.listing_receivedBY and n.is_delete = 0','left')
		->where('a.is_delete',0)
		->group_by('a.ID')
		->order_by('a.ID desc');

		if($ID)
		{
			$this->db->where('a.ID',$ID);
		}

		if(!empty($requester_id))
		{
			$this->db->where('a.requester_id',$requester_id);
            // $this->db->or_where('a.createdBY',$requester_id);
		}

		$query = $this->db->get('sv__petty_cash_advance a')
		->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getPettyCashAdvanceDescription($type = null)
	{
		if($type != 'null' && $type != null) $this->db->where('type',$type);
		$query = $this->db->select('*')
			->where('is_delete',0)
			->order_by('type asc, ID asc')
			->get('sv__ref_petty_cash_advance_description');
			
		return (!empty($query))?$query->result_array():null;
	}

	public function getMgsJobByPIC($user_id)
	{
		$query = $this->db->select('a.id, a.staff_id, a.name, concat(b.name, " ", "[", c.name, "]") as department, d.name as business_entity, e.ID as job_id, e.description as job_description, e.customerName')
		->join('company__department b','b.id = a.department_id')
		->join('company__branch c','c.id = b.branch_id')
		->join('company__business_entity d','d.id = a.business_entity_id')
		->join($this->mgs_db.'.job__pic f','f.staff_id = a.id and f.is_delete = 0','left')
		->join($this->mgs_db.'.view__job e','f.job_id = e.ID')
		->where('a.id',$user_id)
		->where('is_internal',0)
		->group_by('a.id, e.ID')
		->order_by('a.department_id asc, a.business_entity_id asc, a.employee_grade desc, a.id asc')
		->get('user__employment_detail a');
		
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query->result_array():null;
	}

	public function getJobDetail($job_id)
	{
		$query = $this->db->where('ID',$job_id)
		->get($this->mgs_db.'.view__job');

		return (!empty($query))?$query->result_array():null;
	}

	public function getPettyCashAdvancePerson($person, $dept_id = null)
	{
		if($person == 'requester')
		{
			$this->db->distinct()->select('a.id, a.name');
			if($dept_id)
			{
				$this->db->where('a.department_id',$dept_id);
			}
		}

		if($person == 'approver')
		{
			$this->db->distinct()->select('a.id, a.name')->where('a.employee_grade >=',31);
			if($dept_id)
			{
				$this->db->where('a.department_id',$dept_id);
			}
		}

		if($person == 'preparer')
		{
			$this->db->distinct()->select('a.id, a.name')->where('a.division_id',8);
		}


		$query = $this->db->where('a.is_delete',0)
		->order_by('a.employee_grade desc, a.department_id asc, a.id asc')
		->get('user__employment_detail a');

		return (!empty($query))?$query->result_array():null;
	}

	public function updateOrCreateNewDescription($petty_cash_advance_id, $desc_id = array(), $desc_detail = array(), $desc_amount = array())
	{
		if(empty($desc_id)) return false;
		
		$this->db->set('a.is_delete',1)
		->where_not_in('a.description_id',$desc_id)
		->where('a.petty_cash_advance_id',$petty_cash_advance_id)
		->update('sv__petty_cash_advance_description a');

		$desc_stored = $this->db->select('a.*')
		->where('a.petty_cash_advance_id',$petty_cash_advance_id)
		->where('a.is_delete',0)
		->get('sv__petty_cash_advance_description a')
		->result_array();

		if(!empty($desc_stored))
		{
			$batch_insert 	= array();
			$update 		= array();
			$where 			= array();
			foreach ($desc_id as $desc_key => $desc) 
			{
				$bool = false;
				
				foreach ($desc_stored as $key => $descs) 
				{
					if($descs['description_id'] == $desc)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'petty_cash_advance_id'   	=> $petty_cash_advance_id,
						'description_id'    		=> $desc,
						'remark'     				=> $desc_detail[$desc_key],
						'amount'    				=> $desc_amount[$desc_key],
						'createdBY'    				=> $this->session->userdata('user')['id'],
					);
					array_push($batch_insert,$temp);
				}else{
					$temp = array
					(
						'remark'     	=> $desc_detail[$desc_key],
						'amount'    	=> $desc_amount[$desc_key],
						'updatedBY'    	=> $this->session->userdata('user')['id'],
						'updatedTS'    	=> date('Y-m-d H:i:s'),
					);
					array_push($update,$temp);

					$where_temp = array
					(
						'petty_cash_advance_id'   	=> $petty_cash_advance_id,
						'description_id'    		=> $desc,
					);
					array_push($where,$where_temp);
				}
			}
			if($batch_insert){
				$this->db->insert_batch('sv__petty_cash_advance_description',$batch_insert);
			}else{
				foreach($update as $key => $row)
				{
					$this->db->update('sv__petty_cash_advance_description',$row,$where[$key]);
				}
			}

			return true;

		} else {
			$batch_insert = array();
			foreach ($desc_id as $desc_key => $desc) 
			{
				$temp = array
				(
					'petty_cash_advance_id'   	=> $petty_cash_advance_id,
					'description_id'    		=> $desc,
					'remark'     				=> $desc_detail[$desc_key],
					'amount'    				=> $desc_amount[$desc_key],
					'createdBY'    				=> $this->session->userdata('user')['id'],
				);
				array_push($batch_insert,$temp);
			}
			$this->db->insert_batch('sv__petty_cash_advance_description',$batch_insert);return true;
		}
	}

	public function updateOrCreateNewMember($petty_cash_advance_id, $member_id = array(), $department_id = array())
	{
		if(empty($member_id)) return false;
		
		$this->db->set('a.is_delete',1)
		->where_not_in('a.user_id',$member_id)
		->where('a.petty_cash_advance_id',$petty_cash_advance_id)
		->update('sv__petty_cash_advance_member a');

		$mid_stored = $this->db->select('a.*')
		->where('a.petty_cash_advance_id',$petty_cash_advance_id)
		->where('a.is_delete',0)
		->get('sv__petty_cash_advance_member a')
		->result_array();


		if(!empty($mid_stored))
		{
			$batch_insert = array();
			$update = array();
			$where = array();
			foreach ($member_id as $mid_key => $mid) 
			{
				$bool = false;
				
				foreach ($mid_stored as $key => $mids) 
				{
					if($mids['user_id'] == $mid)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'petty_cash_advance_id'	=> $petty_cash_advance_id,
						'user_id'				=> $mid,
						'department_id'			=> $department_id[$mid_key],
						'createdBY'    			=> $this->session->userdata('user')['id'],
					);
					array_push($batch_insert,$temp);
				}else{
					$temp = array
					(
						'department_id'	=> $department_id[$mid_key],
						'updatedBY'    	=> $this->session->userdata('user')['id'],
						'updatedTS'    	=> date('Y-m-d H:i:s'),
					);
					array_push($update,$temp);

					$where_temp = array
					(
						'petty_cash_advance_id'		=> $petty_cash_advance_id,
						'user_id'					=> $mid,
					);
					array_push($where,$where_temp);
				}
			}
			if($batch_insert){
				$this->db->insert_batch('sv__petty_cash_advance_member',$batch_insert);
			}else{
				foreach($update as $key => $row)
				{
					$this->db->update('sv__petty_cash_advance_member',$row,$where[$key]);
				}
			}

			return true;

		} else {
			$batch_insert = array();
			foreach ($member_id as $mid_key => $mid) 
			{
				$temp = array
				(
					'petty_cash_advance_id'		=> $petty_cash_advance_id,
					'user_id'    				=> $mid,
					'department_id'				=> $department_id[$mid_key],
					'createdBY'    				=> $this->session->userdata('user')['id'],
				);
				array_push($batch_insert,$temp);
			}
			$this->db->insert_batch('sv__petty_cash_advance_member',$batch_insert);return true;
		}
	}

	public function updateOrCreateNewListing($petty_cash_advance_id, $desc_id = array(), $desc_detail = array(), $desc_amount = array(), $desc_sst = array(), $desc_total = array())
	{
		if(empty($desc_id)) return false;
		
		$this->db->set('a.is_delete',1)
		->where_not_in('a.description_id',$desc_id)
		->where('a.petty_cash_advance_id',$petty_cash_advance_id)
		->update('sv__petty_cash_advance_listing a');

		$desc_stored = $this->db->select('a.*')
		->where('a.petty_cash_advance_id',$petty_cash_advance_id)
		->where('a.is_delete',0)
		->get('sv__petty_cash_advance_listing a')
		->result_array();

		if(!empty($desc_stored))
		{
			$batch_insert 	= array();
			$update 		= array();
			$where 			= array();
			foreach ($desc_id as $desc_key => $desc) 
			{
				$bool = false;
				
				foreach ($desc_stored as $key => $descs) 
				{
					if($descs['description_id'] == $desc)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'petty_cash_advance_id'   	=> $petty_cash_advance_id,
						'description_id'    		=> $desc,
						'remark'     				=> $desc_detail[$desc_key],
						'base_amount'   			=> $desc_amount[$desc_key],
						'sst_amount'				=> $desc_sst[$desc_key],
						'total'						=> $desc_total[$desc_key],
						'createdBY'    				=> $this->session->userdata('user')['id'],
					);
					array_push($batch_insert,$temp);
				}else{
					$temp = array
					(
						'remark'     	=> $desc_detail[$desc_key],
						'base_amount'   => $desc_amount[$desc_key],
						'sst_amount'	=> $desc_sst[$desc_key],
						'total'			=> $desc_total[$desc_key],
						'updatedBY'    	=> $this->session->userdata('user')['id'],
						'updatedTS'    	=> date('Y-m-d H:i:s'),
					);
					array_push($update,$temp);

					$where_temp = array
					(
						'petty_cash_advance_id'   	=> $petty_cash_advance_id,
						'description_id'    		=> $desc,
					);
					array_push($where,$where_temp);
				}
			}
			if($batch_insert){
				$this->db->insert_batch('sv__petty_cash_advance_listing',$batch_insert);
			}else{
				foreach($update as $key => $row)
				{
					$this->db->update('sv__petty_cash_advance_listing',$row,$where[$key]);
				}
			}

			return true;

		} else {
			$batch_insert = array();
			foreach ($desc_id as $desc_key => $desc) 
			{
				$temp = array
				(
					'petty_cash_advance_id'   	=> $petty_cash_advance_id,
					'description_id'    		=> $desc,
					'remark'     				=> $desc_detail[$desc_key],
					'base_amount'   			=> $desc_amount[$desc_key],
					'sst_amount'				=> $desc_sst[$desc_key],
					'total'						=> $desc_total[$desc_key],
					'createdBY'    				=> $this->session->userdata('user')['id'],
				);
				array_push($batch_insert,$temp);
			}
			$this->db->insert_batch('sv__petty_cash_advance_listing',$batch_insert);return true;
		}
	}

	function getMemberDeptID($member_id = array())
	{
		$query = $this->db->select('a.id, a.name, a.department_id')
						->where_in('a.id',$member_id)
						->get('user__employment_detail a');
		
		return (!empty($query))?$query->result_array():null;
	}

	function getCashAdvanceEmail($filters = array())
	{
		if(!empty($filters)){
			foreach ($filters as $filter) {
				$this->db->where($filter);
			}
		}

		$query = $this->db->select('a.*, b.name as requester_name, b.email, c.primary_email, c.secondary_email')
				->join('user b','b.id = a.requester_id','left')
				->join('user__profiling c','c.user_id = a.requester_id AND c.action_efective_date <= NOW() AND c.is_delete = 0 AND c.id = (SELECT id FROM user__profiling WHERE user_id = a.requester_id AND action_efective_date <= NOW() AND is_delete = 0 ORDER BY action_efective_date desc, created_at desc, id desc limit 1)','left')
				->where('a.is_delete',0)
				->get('sv__petty_cash_advance a');
		
		return (!empty($query))?$query->result_array():null;
	}
}


?>