<?php
    class Order {
        protected $db;
        protected $utils;
        protected $session;

        public function __construct(Database $db, Utils $utils, Session $session, Product $product){
            $this->db = $db;
            $this->utils = $utils;
            $this->session = $session;
            $this->product = $product;
        }

        public function getOrders($data = array()){
            $orders = array();
            $query = "SELECT o.*, DATE_FORMAT(o.date_added, '%d.%m.%Y %H:%i') as 'date_added', (SELECT title FROM {$this->db->prefix}order_status WHERE id=o.status) AS 'status_text' FROM {$this->db->prefix}orders o LEFT JOIN {$this->db->prefix}users u ON u.id=o.user_id WHERE 1";
            
            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND o.status IN (".$data['status'].")";
                }
            }
            
            if(isset($data['in'])){
                if($data['in'] != ''){
                    $query .= " AND o.id IN (".implode(',', $data['in']).")";
                }
            }
            
            if(isset($data['payment_gateway'])){
                if($data['payment_gateway'] != '0'){
                    $query .= " AND payment_gateway = '".$data['payment_gateway']."'";
                }
            }
            
            if(isset($data['source'])){
                if($data['source'] != ''){
                    $query .= " AND source = '".$data['source']."'";
                }
            }

            if(isset($data['user_id']) && !empty($data['user_id'])){
                $query .= " AND o.user_id =".$data['user_id'];
            }

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(o.name LIKE '%$word%' OR o.surname LIKE '%$word%' OR o.email LIKE '%$word%' OR o.phone LIKE '%$word%' OR o.shipping_city LIKE '%$word%' OR o.shipping_town LIKE '%$word%' OR o.invoice_company LIKE '%$word%' OR o.order_id LIKE '%$word%' OR o.bank_order_id LIKE '%$word%' OR o.bank_auth_code LIKE '%$word%' OR bank_reference_code 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(o.date_added) BETWEEN '".$this->db->escape($data['date_start'])."' AND '".$this->db->escape($data['date_end'])."'";
            }
            
            if(isset($data['sort'])){
                $query .= " ORDER BY o." . $data['sort'];
            }else{
                $query .= " ORDER BY o.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) {
                foreach($stmt->rows as $row) {
                    $orders[] = $row;
                }
            }

            return $orders;
        }

        public function getTotalOrders($data = array()){
            $query = "SELECT COUNT(o.id) AS 'total' FROM {$this->db->prefix}orders o WHERE 1";
            
            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND o.status IN (".$data['status'].")";
                }
            }
            
            if(isset($data['in'])){
                if($data['in'] != ''){
                    $query .= " AND o.id IN (".implode(',', $data['in']).")";
                }
            }

            if(isset($data['payment_gateway'])){
                if($data['payment_gateway'] != '0'){
                    $query .= " AND payment_gateway = '".$data['payment_gateway']."'";
                }
            }

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(o.name LIKE '%$word%' OR o.surname LIKE '%$word%' OR o.email LIKE '%$word%' OR o.phone LIKE '%$word%' OR o.shipping_city LIKE '%$word%' OR o.shipping_town LIKE '%$word%' OR o.invoice_company LIKE '%$word%' OR o.order_id LIKE '%$word%' OR o.bank_order_id LIKE '%$word%' OR o.bank_auth_code LIKE '%$word%' OR bank_reference_code 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(o.date_added) BETWEEN '".$this->db->escape($data['date_start'])."' AND '".$this->db->escape($data['date_end'])."'";
            }
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0){
                return $stmt->row['total'];
            }

            return 0;
        }

        public function getOrderTrials($data = array()){
            $orders = array();
            $query = "SELECT o.*, DATE_FORMAT(o.date_added, '%d.%m.%Y %H:%i') as 'date_added',(SELECT title FROM {$this->db->prefix}order_status WHERE id=o.status) AS 'status_text' FROM {$this->db->prefix}orders_onhold o LEFT JOIN {$this->db->prefix}users u ON u.id=o.user_id WHERE 1";

            if(isset($data['user_id']) && !empty($data['user_id'])){
                $query .= " AND o.user_id =".$data['user_id'];
            }

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(o.name LIKE '%$word%' OR o.surname LIKE '%$word%' OR o.email LIKE '%$word%' OR o.phone LIKE '%$word%' OR o.shipping_city LIKE '%$word%' OR o.shipping_town LIKE '%$word%' OR o.invoice_company 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(o.date_added) BETWEEN '".$this->db->escape($data['date_start'])."' AND '".$this->db->escape($data['date_end'])."'";
            }
            
            if(isset($data['sort'])){
                $query .= " ORDER BY o." . $data['sort'];
            }else{
                $query .= " ORDER BY o.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) {
                foreach($stmt->rows as $row) {
                    $orders[] = $row;
                }
            }

            return $orders;
        }

        public function getTotalOrderTrials($data = array()){
            $query = "SELECT COUNT(o.id) AS 'total' FROM {$this->db->prefix}orders_onhold o WHERE 1";

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(o.name LIKE '%$word%' OR o.surname LIKE '%$word%' OR o.email LIKE '%$word%' OR o.phone LIKE '%$word%' OR o.shipping_city LIKE '%$word%' OR o.shipping_town LIKE '%$word%' OR o.invoice_company 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(o.date_added) BETWEEN '".$this->db->escape($data['date_start'])."' AND '".$this->db->escape($data['date_end'])."'";
            }
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0){
                return $stmt->row['total'];
            }

            return 0;
        }

        public function getOrderStatusList(){
            $order_status = array();
            $stmt = $this->db->query( "SELECT id, status, title, description FROM {$this->db->prefix}order_status ORDER BY id ASC" );
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row){ 
                    $item = array(
                        'id'           => $row['id'], 
                        'status'       => $row['status'],
                        'title'        => $row['title'],
                        'description'  => $row['description']
                    );
                    array_push($order_status, $item);
                }
            }
            return $order_status;
        }

        public function getShippingAgenciesList(){
            $agencies = array();
            $stmt = $this->db->query( "SELECT * FROM {$this->db->prefix}shipping_agencies ORDER BY id ASC" );
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row){
                    $agencies[] = $row;
                }
            }
            return $agencies;
        }

        public function getShippingAgency($id = 0) {
            $shipping_agency = array();
            $stmt = $this->db->query( "SELECT * FROM {$this->db->prefix}shipping_agencies WHERE id=:id", array(':id'=>(int)$id));
            if($stmt->num_rows > 0) {
                $shipping_agency = $stmt->row;
            }
            return $shipping_agency;
        }

        public function getShippingPrices($id = 0) {
            $shipping_prices = array();
            $stmt = $this->db->query( "SELECT * FROM {$this->db->prefix}shipping_prices WHERE shipping_agency_id=:id ORDER BY minimum ASC", array(':id'=>(int)$id));
            if($stmt->num_rows > 0) {
                $shipping_prices = $stmt->rows;
            }
            return $shipping_prices;
        }

        public function deleteShippingAgency($id = 0) {
            $this->db->query("DELETE FROM {$this->db->prefix}shipping_agencies WHERE id=:id", array(':id'=>(int)$id));
            $this->db->query("DELETE FROM {$this->db->prefix}shipping_prices WHERE shipping_agency_id=:id", array(':id'=>(int)$id));
        }

        public function getPaymentMethod($id) {
            $payment_type = "Bilinmiyor";
            $stmt = $this->db->query( "SELECT * FROM {$this->db->prefix}payment_types WHERE id=:id", array(':id'=>(int)$id));
            if($stmt->num_rows > 0) {
                $payment_type = $stmt->row['title'];
            }
            return $payment_type;
        }

        public function getOrderStatus($id) {
            $status = "Bilinmiyor";
            $stmt = $this->db->query( "SELECT * FROM {$this->db->prefix}order_status WHERE id=:id", array(':id'=>(int)$id));
            if($stmt->num_rows > 0) {
                $status = $stmt->row['title'];
            }
            return $status;
        }

        public function getOrder($id){
            $orders = array();
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}orders WHERE id=:id", array(':id'=>$id));
            if($stmt->num_rows > 0) {
                $orders = $stmt->row;
            }

            return $orders;
        }

        public function getOrderItems($order_id){
            $items = array();
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}order_items WHERE order_id=:order_id", array(':order_id'=>$order_id));
            if($stmt->num_rows > 0) {
                $items = $stmt->rows;
            }

            return $items;
        }


        public function addOrderHistory($user_id, $order_id, $message) {
            $this->db->query("INSERT INTO {$this->db->prefix}order_history (`message`, `user_id`, `order_id`, `date_added`) VALUES (:message, :user_id, :order_id, NOW())", array(':user_id'=>$user_id, ':order_id'=>$order_id, ':message'=>$message));
        }

        public function getOrderHistory($order_id) {
            $history = array();
            $query = "SELECT * FROM {$this->db->prefix}order_history WHERE order_id=:order_id ORDER BY id DESC";
            
            $stmt = $this->db->query($query, array(':order_id'=>$order_id));
            if($stmt->num_rows > 0) {
                $history = $stmt->rows;
            }

            return $history;
        }

        public function getTotalSales() {
            $total = 0;
            $stmt = $this->db->query("SELECT SUM(order_total) AS 'total' FROM {$this->db->prefix}orders WHERE status IN (1,3,4,5,6,7,8,11)");
            if($stmt->num_rows > 0) {
                $total = $stmt->row['total'];
            }

            return $total;
        }

        public function getTotalSalesToday() {
            $total = 0;
            $stmt = $this->db->query("SELECT SUM(order_total) AS 'total' FROM {$this->db->prefix}orders WHERE status IN (1,3,4,5,6,7,8,11) AND CAST(date_added AS DATE) = :date", array(':date'=>date('Y-m-d')));
            if($stmt->num_rows > 0) {
                $total = $stmt->row['total'];
            }

            return $total;
        }
        public function getTotalSalesMonth() {
            $total = 0;
            $stmt = $this->db->query("SELECT SUM(order_total) AS 'total' FROM {$this->db->prefix}orders WHERE status IN (1,3,4,5,6,7,8,11) AND MONTH(CAST(date_added AS DATE)) = MONTH(CURRENT_DATE()) AND YEAR(CAST(date_added AS DATE)) = YEAR(CURRENT_DATE())");
            if($stmt->num_rows > 0) {
                $total = $stmt->row['total'];
            }

            return $total;
        }

        public function getTotalOrdersToday() {
            $total = 0;
            $stmt = $this->db->query("SELECT COUNT(order_total) AS 'total' FROM {$this->db->prefix}orders WHERE status IN (1,3,4,5,6,7,8,11) AND CAST(date_added AS DATE) = :date", array(':date'=>date('Y-m-d')));
            if($stmt->num_rows > 0) {
                $total = $stmt->row['total'];
            }

            return $total;
        }
        public function getTotalOrdersMonth() {
            $total = 0;
            $stmt = $this->db->query("SELECT COUNT(order_total) AS 'total' FROM {$this->db->prefix}orders WHERE status IN (1,3,4,5,6,7,8,11) AND MONTH(CAST(date_added AS DATE)) = MONTH(CURRENT_DATE()) AND YEAR(CAST(date_added AS DATE)) = YEAR(CURRENT_DATE())");
            if($stmt->num_rows > 0) {
                $total = $stmt->row['total'];
            }

            return $total;
        }

        public function deleteOrder($id){
            $this->db->query("DELETE FROM {$this->db->prefix}orders WHERE id=:id", array(":id"=>$id));
            $this->db->query("DELETE FROM {$this->db->prefix}order_items WHERE order_id=:id", array(":id"=>$id));
        }
        

        public function getBanks() {
            $banks = array();
            $query = "SELECT * FROM {$this->db->prefix}banks ORDER BY id DESC";
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                $banks = $stmt->rows;
            }

            return $banks;
        }

        public function getBankAccount($id) {
            $bank_account = array();
            $query = "SELECT a.*, b.name AS 'bank_name' FROM {$this->db->prefix}bank_accounts a LEFT JOIN {$this->db->prefix}banks b ON b.id = a.bank_id WHERE a.id=:id";
            
            $stmt = $this->db->query($query, array(':id'=>$id));
            if($stmt->num_rows > 0) {
                $bank_account = $stmt->row;
            }

            return $bank_account;
        }

        public function getBankAccounts(){
            $bank_accounts = array();
            $stmt = $this->db->query( "SELECT a.id, a.status, a.owner, a.iban, a.branch, a.account_number, b.name AS 'bank_name', b.image AS 'bank_image' FROM {$this->db->prefix}bank_accounts a LEFT JOIN {$this->db->prefix}banks b ON b.id=a.bank_id" );
            if($stmt->num_rows > 0) {
                $bank_accounts = $stmt->rows;
            }
            return $bank_accounts;
        }

        public function deleteBankAccount($id){
            $this->db->query("DELETE FROM {$this->db->prefix}bank_accounts WHERE id=:id", array(":id"=>$id));
        }

        public function getPaymentNotifications($data = array()){
            $orders = array();
            $query = "SELECT * FROM {$this->db->prefix}remittance_notifications WHERE 1";
            
            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status =".$data['status'];
                }
            }
            
            if(isset($data['in'])){
                if($data['in'] != ''){
                    $query .= " AND id IN (".implode(',', $data['in']).")";
                }
            }

            if(isset($data['user_id']) && !empty($data['user_id'])){
                $query .= " AND user_id =".$data['user_id'];
            }

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(name LIKE '%$word%' OR phone LIKE '%$word%' OR order_id 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_created) 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) {
                foreach($stmt->rows as $row) {
                    $row['bank'] = $this->getBankAccount($row['bank_account_id']);
                    $orders[] = $row;
                }
            }

            return $orders;
        }

        public function getTotalPaymentNotifications($data = array()){
            $query = "SELECT COUNT(id) AS 'total' FROM {$this->db->prefix}remittance_notifications WHERE 1";
            
            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status IN (".$data['status'].")";
                }
            }
            
            if(isset($data['in'])){
                if($data['in'] != ''){
                    $query .= " AND id IN (".implode(',', $data['in']).")";
                }
            }

            if(isset($data['user_id']) && !empty($data['user_id'])){
                $query .= " AND user_id =".$data['user_id'];
            }

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(name LIKE '%$word%' OR phone LIKE '%$word%' OR order_id 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_created) BETWEEN '".$this->db->escape($data['date_start'])."' AND '".$this->db->escape($data['date_end'])."'";
            }
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0){
                return $stmt->row['total'];
            }

            return 0;
        }

        public function deletePaymentNotificaiton($id){
            $this->db->query("DELETE FROM {$this->db->prefix}remittance_notifications WHERE id=:id", array(":id"=>$id));
        }
    }
?>