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

class Model_credit_note 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 getCreditNoteList($ID = null, $requester_id = null, $department_id = null, $year = null)
	{
		$query = $this->db->select('a.*, m.inv_form_no, group_concat(ifnull(g.id,sst.description_id) separator "->") as description_id, group_concat(g.description separator "->") as description, group_concat(b.remark separator "->") as description_remark, group_concat(ifnull(b.amount,sst.amount) separator "->") as description_amount, ')
		->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, j.name as inv_branch')
		->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__credit_note_description b','b.credit_note_id = a.ID and b.is_delete = 0','left')
		->join('sv__credit_note_description sst','sst.credit_note_id = a.ID and sst.description_id = "sst" and sst.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_credit_note_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('company__branch j','j.id = a.branch_id','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__invoice m','m.id = a.invoice_id','left')
		->where('a.is_delete',0)
		->group_by('a.ID')
		->order_by('a.ID desc, a.job_id asc, a.cn_form_no asc');

		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__credit_note a')
		->result_array();
		// die(debug($this->db->last_query()));
		return (!empty($query))?$query:null;
	}

	public function getCreditNoteDescription($entity_id)
	{
		$query = $this->db->where('entity_id',$entity_id)
		->where('is_delete',0)
		->order_by('ID asc')
		->get('sv__ref_credit_note_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 getClientContact($client_id, $client_contact = null)
	{
		if($client_contact) 
		{
			$query = $this->db->query('select id, name, "Main" as rep_name, phone, email
				from customer 
				where id = '.$client_id.' 
				and phone = "'.$client_contact.'" 
				union 
				select a.id, a.name, b.name as rep_name, if(b.phone_work != "", b.phone_work, b.phone_mobile) as phone, b.email
				from customer a 
				left join customer__contact b on b.customer_id = a.id and b.is_delete = 0 
				where a.id = '.$client_id.'
				and if(b.phone_work != "", b.phone_work, b.phone_mobile) = "'.$client_contact.'"
				');
		}else{
			$query = $this->db->query('select id, name, "Main" as rep_name, phone, email
				from customer 
				where id = '.$client_id.' 
				union 
				select a.id, a.name, b.name as rep_name, if(b.phone_work != "", b.phone_work, b.phone_mobile) as phone, if(b.email != "", b.email, a.email) as email
				from customer a 
				left join customer__contact b on b.customer_id = a.id and b.is_delete = 0 
				where a.id = '.$client_id.'
				');
		}

    	// debug($this->db->last_query());
		return (!empty($query))?$query->result_array():null;
	}

	public function getCreditNotePerson($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 updateOrCreateNew($credit_note_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.credit_note_id',$credit_note_id)
		->update('sv__credit_note_description a');

		$desc_stored = $this->db->select('a.*')
		->where('a.credit_note_id',$credit_note_id)
		->where('a.is_delete',0)
		->get('sv__credit_note_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
					(
						'credit_note_id'   		=> $credit_note_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
					(
						'credit_note_id'   		=> $credit_note_id,
						'description_id'    => $desc,
					);
					array_push($where,$where_temp);
				}
			}
			if($batch_insert){
				$this->db->insert_batch('sv__credit_note_description',$batch_insert);
			}else{
				foreach($update as $key => $row)
				{
					$this->db->update('sv__credit_note_description',$row,$where[$key]);
				}
			}

			return true;

		} else {
			$batch_insert = array();
			foreach ($desc_id as $desc_key => $desc) 
			{
				$temp = array
				(
					'credit_note_id'   		=> $credit_note_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__credit_note_description',$batch_insert);return true;
		}
	}

	public function getInvoiceDetail($jobID = null, $entity_id = null, $ID = null)
	{
		$query = $this->db->select('a.*, l.name as customerName, k.description as job_description, f.name as entity_name, j.name as inv_branch')
		->join('company__business_entity f','f.id = a.business_entity_id')
		->join('company__branch j','j.id = a.branch_id','left')
		->join($this->mgs_db.'.view__job k','k.ID = a.job_id','left')
		->join('customer l','l.id = a.client_id','left')
		->where('a.is_delete',0)
		->where('a.approval_status',1)
		->where('a.prepared_status',1)
		->group_by('a.ID')
		->order_by('a.ID desc, a.job_id asc, a.inv_form_no asc');

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

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

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

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

	public function getClientAddress($customerID = null)
	{
		$query = $this->db->query('
				select a.id as customer_id, a.name as customer_name, "Main" as type, concat(a.address1," ",a.address2," ",a.postcode," ",a.city," ",b.state," ",c.country) as address
				from customer a
				left join _ref__country b on b.id = a.state_id
				left join _ref__country c on b.id = a.country_id
				where a.id = '.$client_id.'
				union
				select a.id as customer_id, a.name as customer_name, "Other Address" as type, concat(coa.address1," ",coa.address2," ",coa.postcode," ",coa.city," ",b.state," ",c.country) as address
				from customer a
				left join customer__other_address coa on coa.customer_id = a.id and coa.is_delete = 0
				left join _ref__country b on b.id = coa.state_id
				left join _ref__country c on b.id = coa.country_id
				where a.id = '.$client_id.'
			');
	
		return (!empty($query))?$query->result_array():null;
	}
}


?>