<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Claim_model extends Ci_Model
{

    public function __construct()
    {
        parent::__construct();
    }
    
    function getclaims($company_id) {
        $sql = "SET SQL_BIG_SELECTS=1";
		$this->db->query($sql);

		$where = array(
            'admin_id'		=> $company_id,
        );
        

		$query = $this->db
            ->select("distinct c.claim_id, c.refno,c.name,c.date_added, 
                        (SELECT type FROM `claim_types` WHERE `ct_id` = c.ct_id) as claim_type, 
                        c.admin_id, 
                        c.status, 
                        (SELECT description FROM `status` WHERE `status_id` = c.status LIMIT 1) as status_desc
					",FALSE)
			->from("claims c")
			->where($where)
			->order_by('c.date_added DESC')
			->get();
			//echo $this->db->last_query();
			if(!$query) return false;
			if($query->num_rows()>0) return $query->result();
			else return false;
    }

    function getclaims_icam($company_id) {
        $sql = "SET SQL_BIG_SELECTS=1";
		$this->db->query($sql);

		$where = array(
            'icam'		=> $company_id,
        );
        

		$query = $this->db
            ->select("distinct c.claim_id, c.refno,c.name,c.date_added, 
                        (SELECT type FROM `claim_types` WHERE `ct_id` = c.ct_id) as claim_type, 
                        c.admin_id, 
                        c.status, 
						(SELECT description FROM `status` WHERE `status_id` = c.status LIMIT 1) as status_desc,
						(SELECT firstname FROM `admins` WHERE `admin_id` = c.admin_id) as distributor
					",FALSE)
			->from("claims c")
			->where($where)
			->order_by('date_added DESC')
			->get();
			//echo $this->db->last_query();
			if(!$query) return false;
			if($query->num_rows()>0) return $query->result();
			else return false;
    }

    function getclaims_fa($user_id) {
        $sql = "SET SQL_BIG_SELECTS=1";
		$this->db->query($sql);

		$where = array(
            'caf.fa_id'		=> $user_id,
        );
        

		$query = $this->db
            ->select("distinct c.claim_id, c.refno,c.name,DATE(c.date_added) AS date_added, caf.ticketno,
                        (SELECT type FROM `claim_types` WHERE `ct_id` = c.ct_id) as claim_type, 
                        c.admin_id, 
						c.status, 
						(SELECT DATE(date) FROM `claim_status_logs` where refno = c.refno and status_id = c.status ORDER BY cs_id DESC LIMIT 1) as icam_aprv_date,
						(SELECT description FROM `status` WHERE `status_id` = c.status LIMIT 1) as status_desc,
						(SELECT firstname FROM `admins` WHERE `admin_id` = c.icam) as icam,
						(SELECT firstname FROM `admins` WHERE `admin_id` = c.admin_id) as distributor
					",FALSE)
			->from("claims c")
			->join("claim_application_final AS caf","c.refno=caf.refno","left")
			->where($where)
			->where('c.status in (3,5,6)')
			->order_by('date_added DESC')
			->get();
			//  echo $this->db->last_query();
			if(!$query) return false;
			if($query->num_rows()>0) return $query->result();
			else return false;
    }


	function getclaims_appr($company_id) {
        $sql = "SET SQL_BIG_SELECTS=1";
		$this->db->query($sql);

		$where = array(
			'icam'		=> $company_id,
			'status'	=> 1
        );
        

		$query = $this->db
            ->select("distinct c.claim_id, c.refno,c.name,c.date_added, 
                        (SELECT type FROM `claim_types` WHERE `ct_id` = c.ct_id) as claim_type, 
                        c.admin_id, 
                        c.status, 
                        (SELECT description FROM `status` WHERE `status_id` = c.status LIMIT 1) as status_desc
					",FALSE)
			->from("claims c")
			->where($where)
			->order_by('c.date_added DESC')
			->get();
			//echo $this->db->last_query();
			if(!$query) return false;
			if($query->num_rows()>0) return $query->result();
			else return false;
    }

	
    function getdocus($refno) {
        $sql = "SET SQL_BIG_SELECTS=1";
		$this->db->query($sql);

		$where = array(
            'refno'		=> $refno,
        );
        

		$query = $this->db
            ->select("dt.id as id, dt.type as doc, cd.filename as filename
					",FALSE)
            ->from("claim_docus cd")
            ->join("doc_types AS dt","cd.doc_id=dt.id","left")
			->where($where)
			->order_by('dt.id ASC')
			->get();
			// echo $this->db->last_query();
			if(!$query) return false;
			if($query->num_rows()>0) return $query->result();
			else return false;
	}
	
    function getdocus_uploaded($refno) {
        $sql = "SET SQL_BIG_SELECTS=1";
		$this->db->query($sql);

		$where = array(
            'refno'		=> $refno,
        );
        

		$query = $this->db
            ->select("dt.id as id, dt.type as doc, CONCAT('http://xplus.ph/shell-claims/uploads/',filename) as filename, UPPER(SUBSTRING_INDEX(filename, '.', -1)) as ext
					",FALSE)
            ->from("claim_docus cd")
            ->join("doc_types AS dt","cd.doc_id=dt.id","left")
			->where($where)
			// ->where('filename != ""')
			->order_by('dt.id ASC')
			->get();
			//echo $this->db->last_query();
			if(!$query) return false;
			if($query->num_rows()>0) return $query->result();
			else return false;
    }

}
