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

class Model_Invoice 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 getInvoiceList($ID = null, $requester_id = null, $department_id = null, $manager_id = null, $year)
	{
		$query = $this->db->select('a.*, group_concat(g.id separator "->") as description_id, group_concat(b.id separator "->") as detail_id, group_concat(g.type separator "->") as description_type, group_concat(g.description separator "->") as description, group_concat(b.remark separator "->") as description_remark, group_concat(b.year_end separator "->") as description_year_end, group_concat(b.amount separator "->") as description_amount, count(g.id) as total_description')
		->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.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('(select group_concat(mgr.manager_id) from sv__invoice_manager_approval mgr where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_id')
		->select('(select group_concat(user.name separator "->") from sv__invoice_manager_approval mgr join user on user.id = mgr.manager_id and user.is_delete = 0 where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_name')
		->select('(select group_concat(user.email separator "->") from sv__invoice_manager_approval mgr join user on user.id = mgr.manager_id and user.is_delete = 0 where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_email')
		->select('(select group_concat(mgr.approval_status separator "->") from sv__invoice_manager_approval mgr where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_status')
		->select('(select group_concat(mgr.approval_remark separator "->") from sv__invoice_manager_approval mgr where mgr.invoice_id = a.ID and mgr.is_delete = 0) as manager_remark')
		->join('sv__invoice_description b','b.invoice_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_invoice_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 and k.is_internal = 0','left')
		->join('customer l','l.id = a.client_id','left')
		->where('a.is_delete',0)
		->group_by('a.ID')
		->order_by('a.ID desc, a.job_id asc, a.inv_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(!empty($manager_id))
		{
			$this->db->where(
				'
				(select group_concat(mgr.manager_id) from sv__invoice_manager_approval mgr where mgr.invoice_id = a.ID and mgr.is_delete = 0 and manager_id = '.$manager_id.')
				OR
				a.requester_id = '.$manager_id.'
				'
			);
		}

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

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

	public function getInvoiceDescription($entity_id)
	{
		$query = $this->db->where('entity_id',$entity_id)
		->where('is_delete',0)
		->order_by('type asc, ID asc')
		->get('sv__ref_invoice_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('e.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, fax
				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, if(b.fax != "",b.fax, a.fax) as fax
				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, fax
				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, if(b.fax != "",b.fax, a.fax) as fax
				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 getInvoicePerson($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($invoice_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.invoice_id',$invoice_id)
		->update('sv__invoice_description a');

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

			return true;

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

	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__state b on b.id = a.state_id
				left join _ref__country c on c.id = a.country_id
				where a.id = '.$customerID.'
				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__state b on b.id = coa.state_id
				left join _ref__country c on c.id = coa.country_id
				where a.id = '.$customerID.'
			');
	
		return (!empty($query))?$query->result_array():null;
	}

	public function countDescriptionDetail($entity_id = 0, $invoice_id = 0)
	{
		$query = $this->db->select('a.ID, a.description, a.entity_id, b.invoice_id, b.remark, b.amount, count(b.ID) as total_description')
						->where('a.entity_id',$entity_id)
						->where('a.is_delete',0)
						->join('sv__invoice_description b', 'b.description_id = a.ID and b.is_delete = 0 and b.invoice_id = '.$invoice_id, 'left outer')
						->group_by('a.ID')
						->order_by('a.ID')
						->get('sv__ref_invoice_description a');
		
		return (!empty($query))?$query->result_array():null;
	}

	public function updateOrCreateNewMgrApproved($invoice_id, $manager_id = array())
	{
		if(empty($manager_id)) return false;
		
		$this->db->set('a.is_delete',1)
		->where_not_in('a.manager_id',$manager_id)
		->where('a.invoice_id',$invoice_id)
		->update('sv__invoice_manager_approval a');

		$mgr_stored = $this->db->select('a.*')
		->where('a.invoice_id',$invoice_id)
		->where('a.is_delete',0)
		->get('sv__invoice_manager_approval a')
		->result_array();

		if(!empty($mgr_stored))
		{
			$batch_insert = array();
			$update = array();
			$where = array();
			foreach ($manager_id as $mgr_key => $mgr) 
			{
				$bool = false;
				
				foreach ($mgr_stored as $key => $mgrs) 
				{
					if($mgrs['manager_id'] == $mgr)
					{
						$bool = true;
					}
				}

				if(!$bool)
				{
					$temp = array
					(
						'invoice_id'		=> $invoice_id,
						'manager_id'		=> $mgr,
						'approval_status'	=> 0,
						'approval_remark'	=> '',
						'createdBY'			=> $this->session->userdata('user')['id'],
					);
					array_push($batch_insert,$temp);
				}else{
					$temp = array
					(
						'approval_status'	=> 0,
						'approval_remark'	=> '',
						'updatedBY'			=> $this->session->userdata('user')['id'],
						'updatedTS'			=> date('Y-m-d H:i:s'),
					);
					array_push($update,$temp);

					$where_temp = array
					(
						'invoice_id'	=> $invoice_id,
						'manager_id'	=> $mgr,
					);
					array_push($where,$where_temp);
				}
			}
			if($batch_insert){
				$this->db->insert_batch('sv__invoice_manager_approval',$batch_insert);
			}else{
				foreach($update as $key => $row)
				{
					$this->db->update('sv__invoice_manager_approval',$row,$where[$key]);
				}
			}

			return true;

		} else {
			$batch_insert = array();
			foreach ($manager_id as $mgr_key => $mgr) 
			{
				$temp = array
				(
					'invoice_id'		=> $invoice_id,
					'manager_id'		=> $mgr,
					'approval_status'	=> 0,
					'approval_remark'	=> '',
					'createdBY'			=> $this->session->userdata('user')['id'],
				);
				array_push($batch_insert,$temp);
			}
			$this->db->insert_batch('sv__invoice_manager_approval',$batch_insert);return true;
		}
	}
}


?>