<?php
defined('BASEPATH') OR exit('No direct script access allowed');
defined('DIR_SEPARATOR') || define('DIR_SEPARATOR', '/');
defined('XUXO_VERSION') || define('XUXO_VERSION', '1.0.0');

class Testimonymain extends CI_Model {
	
	public function __construct() {
		parent::__construct();
		
	}
	
	/**
	* TESTIMONY
	*/
	public function getAllTestimonies($status = NULL, $countonly = FALSE, $search = NULL, $start = NULL, $length = NULL) {
		$sql = "SELECT
							testimony_main.ID,
							testimony_main.name,
							testimony_main.feature,
							testimony_main.origin,
							testimony_main.content,
							testimony_main.image,
							testimony_main.postDT,
							testimony_main.createdBy author,
							testimony_main.status
						FROM  testimony_main
						WHERE 1 = 1 ";
						if($status) {
							if(is_array($status) && !empty($status)) {
								$stsstr = NULL;
								foreach($status as $key=>$value) {
									$stsstr .= ($key) ? ", '".(strtoupper($value))."'" : "'".(strtoupper($value))."'";
								}
								$sql .= " AND  testimony_main.status IN (".$stsstr.") ";
							} elseif(is_string($status)) {
								$sql .= " AND  testimony_main.status = '".(strtoupper($status))."' ";
							}
						}
		$sql .= ($search) ? " AND UPPER(name) LIKE '%".(strtoupper(trim($search)))."%' " : NULL;
		$sql .= " ORDER BY name ASC";
		$sql .= ($start !== NULL && $length) ? " LIMIT ".$start.", ".$length : NULL;
		
		$q = $this->db->query($sql);
		$result = ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
		if($countonly) {
			return ($result) ? count($result) : 0;
		} else {
			return $result;
		}
	}
	
	public function getTestimony($id) {
		$sql = "SELECT
							ID,
							name,
							feature,
							origin,
							content,
							DATE_FORMAT(postDT, '%d/%m/%Y') postDT,
							image,
							status,
							createdBy author,
							DATE_FORMAT(createdDT, '%Y-%m-%d %H:%i:%s') createdDT,
							DATE_FORMAT(updatedDT, '%Y-%m-%d %H:%i:%s') updatedDT,
							IFNULL((SELECT penName FROM people, user WHERE user.peopleID = people.ID AND user.ID =  testimony_main.createdBy),'admin') createdBy,
							IFNULL((SELECT penName FROM people, user WHERE user.peopleID = people.ID AND user.ID =  testimony_main.updatedBy),'admin') updatedBy
						FROM  testimony_main
						WHERE  testimony_main.ID = ".$id."
						ORDER BY name ASC";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
		
	public function getTestimonyImpressions() {
		$sql = "SELECT
							tracker_main.id,
							tracker_main.path,
							tracker_main.param,
							tracker_main.href,
							tracker_main.ip,
							tracker_main.full,
							tracker_main.route,
							tracker_main.city,
							tracker_main.state,
							tracker_main.postcode,
							tracker_main.country_long,
							tracker_main.country_short,
							tracker_main.lat,
							tracker_main.long,
							tracker_main.org,
							DATE_FORMAT(tracker_main.datetime,'%Y-%m-%d %h:%i %p') datetime
						FROM tracker_main
						WHERE UPPER(path) = 'TESTIMONY/INDEX/'
						ORDER BY datetime DESC";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
	}
		
	public function savetestimony($data) {
		if(!$data['ID']) {
			$sql = "INSERT INTO  testimony_main (
								name, 
								content, 
								postDT,
								origin,
								feature,
								image, 	
								status, 
								createdDT, 
								updatedDT, 
								createdBy, 
								updatedBy
							) VALUES (
								'".(addslashes($data['name']))."',
								'".(addslashes($data['content']))."',
								".((isset($data['postDT']) && $data['postDT']) ? "STR_TO_DATE('".$data['postDT']."','%d/%m/%Y'), " : "NULL,")."		
								".((isset($data['origin']) && $data['origin']) ? "'".(addslashes($data['origin']))."', " : "NULL,")."						
								'".(strtoupper($data['feature']))."',
								".((isset($data['image']) && $data['image']) ? "'".(addslashes($data['image']))."' " : "NULL").",
								'".(strtoupper($data['status']))."',
								NOW(),
								NOW(),
								".$data['userid'].",
								".$data['userid']."
							)";
			return ($this->db->query($sql)) ? $this->db->insert_id() : 0;
		} else {
			$sql = "UPDATE  testimony_main
								SET
										name = '".(addslashes($data['name']))."',
										content = '".(addslashes($data['content']))."',		
										postDT = ".((isset($data['postDT']) && $data['postDT']) ? " STR_TO_DATE('".$data['postDT']."','%d/%m/%Y') " : "NULL").",							
										origin = ".((isset($data['origin']) && $data['origin']) ? "'".(addslashes($data['origin']))."' " : "NULL").",							
										image = ".((isset($data['image']) && $data['image']) ? "'".(addslashes($data['image']))."' " : "NULL").",									
										feature = '".(strtoupper($data['feature']))."',					
										status = '".(strtoupper($data['status']))."',
										updatedDT = NOW(),
										updatedBy = ".$data['userid']."
								WHERE ID = ".$data['ID'];
			return ($this->db->query($sql)) ? $data['ID'] : 0;
		}
	}
	
	public function ObjToArray($item) { return json_decode(json_encode($item),true); }
	
}































