<?php
    class TrafficSource {
        public function __construct(Database $db){
            $this->db = $db;
        }

        function getReferrers(){
            $stmt = $this->db->query( "SELECT DISTINCT referrer FROM {$this->db->prefix}referrer_logs");
            if($stmt->num_rows > 0) {
                return $stmt->rows;
            }
            return array();
        }

        function getRecord($id){
            $stmt = $this->db->query( "SELECT * FROM {$this->db->prefix}referrer_logs WHERE id = :id limit 1", array(':id' => $id));
            if($stmt->num_rows > 0) {
                return $stmt->row;
            }
            return false;
        }
        
        public function getRecords($data = array()) {
            $result = array();
            $query = "SELECT * FROM {$this->db->prefix}referrer_logs WHERE 1";
            
            if(isset($data['referrer']) && !empty($data['referrer'])){
                $query .= " AND referrer ='".$data['referrer']."'";
            }

            if(isset($data['search_keywords'])){
                $keywords = explode(' ', trim($data['search_keywords']));
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "((referrer LIKE '%$word%') OR (url LIKE '%$word%') OR (ip LIKE '%$word%'))";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }
            
            if(isset($data['date_start']) && !empty($data['date_start']) && isset($data['date_end']) && !empty($data['date_end'])){
                $query .= " AND DATE(date) BETWEEN '".$this->db->escape($data['date_start'])."' AND '".$this->db->escape($data['date_end'])."'";
            }
            
            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                $result = $stmt->rows;
            }
            return $result;
        }

        public function getTotalRecords($data = array()){
            $query = "SELECT COUNT(id) AS 'total' FROM {$this->db->prefix}referrer_logs WHERE 1";
            
            if(isset($data['referrer']) && !empty($data['referrer'])){
                $query .= " AND referrer ='".$data['referrer']."'";
            }
            
            if(isset($data['date_start']) && !empty($data['date_start']) && isset($data['date_end']) && !empty($data['date_end'])){
                $query .= " AND DATE(date) BETWEEN '".$this->db->escape($data['date_start'])."' AND '".$this->db->escape($data['date_end'])."'";
            }

            if(isset($data['search_keywords'])){
                $keywords = explode(' ', trim($data['search_keywords']));
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "((referrer LIKE '%$word%') OR (url LIKE '%$word%') OR (ip LIKE '%$word%'))";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0){
                $total_urls      = $stmt->row['total'];
            }else{
                $total_urls      = 0;
            }

            return $total_urls;
        }
    }