<?php 
	defined('BASEPATH') or exit('No direct script access allowed');
	
	
	class Model_promo extends CI_model{
		
		public  $versi;
		private $user;
		private $api_key;
		private $_table = 'template_promo';
		
		function __construct()
		{
			parent::__construct();
			
			$this->api_key     = info()['api_key'];
			$this->versi       = info()['version'];
			// $this->pro         = info()['user_pass'];
			
			$this->user = info()['user_name'];
		}
		
		function getReport($params = array()){
			// print_r($params);
			$hasReportCustomer = $this->db->field_exists('id_konsumen', 'report');
			$hasReportDate = $this->db->field_exists('create_date', 'report');
			$reportSelect = '
			`report`.`message`,
			`report`.`id`,
			`report`.`id_kirim`,
			`report`.`target`,
			`report`.`status`';
			if ($hasReportCustomer) $reportSelect .= ',`report`.`id_konsumen`,`report_customer`.`nama` AS `customer_name`';
			if ($hasReportDate) $reportSelect .= ',`report`.`create_date`';
			if ($this->db->field_exists('state', 'report')) $reportSelect .= ',`report`.`state`';
			if ($this->db->field_exists('stateid', 'report')) $reportSelect .= ',`report`.`stateid`';
			$this->db->select($reportSelect);
			$this->db->from('report');
			if ($hasReportCustomer) $this->db->join('konsumen AS report_customer', 'report_customer.id = report.id_konsumen', 'left');
		 
			if(array_key_exists("where", $params)){
				foreach($params['where'] as $key => $val){ 
					$this->db->where($key, $val); 
				} 
			}  
			
			if(array_key_exists("search", $params))
			{
				if(!empty($params['search']['keywords'])){ 
					$this->db->group_start()->like('report.target', $params['search']['keywords'])->or_like('report.message', $params['search']['keywords']);
					if ($hasReportCustomer) $this->db->or_like('report_customer.nama', $params['search']['keywords']);
					$this->db->group_end();
				} 
				if(!empty($params['search']['status'])) {
					if ($params['search']['status'] === 'success') $this->db->where("(LOWER(report.status) IN ('1','2','3','sent','delivered','read','success','true','done','berhasil') OR LOWER(COALESCE(report.state,'')) IN ('1','2','3','sent','delivered','read'))", null, false);
					elseif ($params['search']['status'] === 'read') $this->db->where("(LOWER(report.status) IN ('3','read') OR LOWER(COALESCE(report.state,'')) IN ('3','read'))", null, false);
					elseif ($params['search']['status'] === 'delivered') $this->db->where("(LOWER(COALESCE(report.status,'')) IN ('2','delivered') OR LOWER(COALESCE(report.state,'')) IN ('2','delivered')) AND LOWER(COALESCE(report.status,'')) NOT IN ('3','read') AND LOWER(COALESCE(report.state,'')) NOT IN ('3','read')", null, false);
					elseif ($params['search']['status'] === 'failed') $this->db->where("LOWER(COALESCE(NULLIF(report.state,''),report.status)) IN ('0','failed','gagal','false','error','invalid','expired','url unreachable')", null, false);
					else $this->db->where("LOWER(COALESCE(NULLIF(report.state,''),report.status))", strtolower($params['search']['status']));
				}
				
			} 
			if(!empty($params['search']['sortBy'])){ 
				$this->db->order_by('`report`.`id`', $params['search']['sortBy']); 
			}
			if(array_key_exists("returnType",$params) && $params['returnType'] == 'count'){
				$result = $this->db->count_all_results(); 
				}else{ 
				if(array_key_exists("id", $params) || (array_key_exists("returnType", $params) && $params['returnType'] == 'single')){
					
					$this->db->order_by('report.id', 'DESC'); 
					$query = $this->db->get(); 
					$result = ($query->num_rows() > 0)?$query->row():FALSE; 
					
					}else{
					
					$this->db->order_by('report.id', 'DESC'); 
					if(array_key_exists("start",$params) && array_key_exists("limit",$params)){ 
						
						$this->db->limit($params['limit'],$params['start']); 
						}elseif(!array_key_exists("start",$params) && array_key_exists("limit",$params)){ 
						
						$this->db->limit($params['limit']); 
					}
					
					$query = $this->db->get(); 
					$result = ($query->num_rows() > 0)?$query->result():FALSE; 
				} 
			} 
			
			// Return fetched data 
			return $result; 
		}
		
		
		public function insert(array $data)
		{
			$query = $this->db->insert($this->_table, $data);
			
			if ($query == FALSE)
			return FALSE;
			else
			return TRUE;
		}
		
		public function insert_report(array $data)
		{
			$query = $this->db->insert('report', $data);
			
			if ($query == FALSE)
			return FALSE;
			else
			return TRUE;
		}
		
		public function update_device($data,$where)
		{
			$query = $this->db->where($where);
			$query = $this->db->update('device', $data);
			
			if ($query == FALSE)
			return FALSE;
			else
			return TRUE;
		}
		
		public function update_report($data,$where)
		{
			$query = $this->db->where($where);
			$query = $this->db->update('report', $data);
			
			if ($query == FALSE)
			return FALSE;
			else
			return TRUE;
		}
		
		public function update($key, $data)
		{
			$query = $this->db->where('id', $key);
			$query = $this->db->update($this->_table, $data);
			
			if ($query == FALSE)
			return FALSE;
			else
			return TRUE;
		}
		
		public function update_konsumen($data,$where)
		{
			$query = $this->db->where($where);
			$query = $this->db->update('konsumen', $data);
			
			if ($query == FALSE)
			return FALSE;
			else
			return TRUE;
		}
		
		
		public function delete($val = 0)
		{
			if ($this->cek_id($val) == 1) 
			{
				$this->db->where('id', $val)->delete($this->_table);
				return TRUE;
			}
			else
			{
				return FALSE;
			}
		}
		
		public function delete_report($val = 0, $valid = 0)
		{
			if ($this->cek_id_konsumen($valid) == 1) 
			{
				$query = $this->db->where('id',$valid);
				$query = $this->db->update('konsumen',['kunci'=>0]);
			}
			
			if ($this->cek_id_report($val) == 1) 
			{
				$this->db->where('id', $val)->delete('report');
				return TRUE;
			}
			else
			{
				return FALSE;
			}
			
		}
		
		
		public function get_data_edit($val_id)
		{
			$query = $this->db->where('id', $val_id);
			$query = $this->db->get($this->_table);
			$result = $query->row_array();
			return $result;
		}
		
		
		public function cek_nomor_bylabel($where)
		{
			$query = $this->db->select('id,nama,no_hp');
			$query = $this->db->where($where);
			$query = $this->db->order_by('nama', 'RANDOM');
			$query = $this->db->get('konsumen');
			$result = $query->row();
			return $result;
		}

		public function get_contact_for_promo($number)
		{
			return $this->db->select('id,nama,no_hp')
				->where('no_hp', $number)
				// 0 = siap, 2 = pernah terkirim, 3 = pengiriman pernah gagal.
				// Ketiganya tetap sah sebagai target promo berikutnya.
				->where_in('kunci', [0, 2, 3])
				->get('konsumen')
				->row();
		}
		
		public function get_label()
		{
			$query = $this->db->select('panggilan');
			$query = $this->db->where('kunci',0);
			$query = $this->db->or_where('kunci',3);
			$query = $this->db->group_by('panggilan');
			$query = $this->db->order_by('panggilan', 'ASC');
			$query = $this->db->get('konsumen');
			$result = $query->result();
			return $result;
		}
		
		public function total_label($where)
		{
			$query = $this->db->select('*');
			$query = $this->db->where($where);
			$query = $this->db->get('konsumen');
			$result = $query->num_rows();
			return $result;
		}

		public function get_target_contacts($level = 1)
		{
			$this->db->select('k.id,k.nama,k.no_hp,k.panggilan,k.jenis_member,m.title AS member_title');
			$this->db->from('konsumen k');
			$this->db->join('member m', 'm.id = k.jenis_member', 'left');
			$this->db->where_in('k.kunci', [0, 2, 3]);
			$this->db->where('k.no_hp !=', '');
			if (strpos((string) $level, 'member:') === 0) {
				$this->db->where('k.jenis_member', (int) substr($level, 7));
			}
			$this->db->order_by('k.nama', 'ASC');
			return $this->db->get()->result();
		}

		public function get_member_levels()
		{
			return $this->db->select('id,title')
				->where('status', 1)
				->order_by('id', 'ASC')
				->get('member')
				->result();
		}
		
		public function get_promo_byid($id =0)
		{
			$query = $this->db->select('id,title,deskripsi,jenis_pesan,url,filename,filetype');
			$query = $this->db->order_by('title', 'ASC');
			$query = $this->db->where('id',$id);
			$query = $this->db->get($this->_table);
			$result = $query->row();
			return $result;
		}
		
		public function get_promo()
		{
			$query = $this->db->select('id,title,jenis_pesan');
			$query = $this->db->order_by('title', 'ASC');
			$query = $this->db->get($this->_table);
			$result = $query->result();
			return $result;
		}

		public function get_template_cards()
		{
			return $this->db->select('id,title,deskripsi,jenis_pesan,active')
				->order_by('id', 'DESC')
				->get($this->_table)
				->result();
		}
		
		public function cek_id($val = 0)
		{
			$query = $this->db->select('id');
			$query = $this->db->where('id', $val);
			$query = $this->db->get($this->_table);
			$query = $query->num_rows();
			$int = (int)$query;
			return $int;
		}
		
		public function cek_id_report($val = 0)
		{
			$query = $this->db->select('id');
			$query = $this->db->where('id', $val);
			$query = $this->db->get('report');
			$query = $query->num_rows();
			$int = (int)$query;
			return $int;
		}
		
		public function cek_id_konsumen($val = 0)
		{
			$query = $this->db->select('id');
			$query = $this->db->where('id', $val);
			$query = $this->db->get('konsumen');
			$query = $query->num_rows();
			$int = (int)$query;
			return $int;
		}
	}																																																		
