<?php

class Orders_model extends CI_Model {

    function get_settings() {
        $this->db->select('inv_currency');
        return $this->db->get('setting')->row();
    }

    function get_total_sales_amount() {
        $this->db->select('count(orders.id) as total_count,sum(bills_orders.discount_remainder) as total_amount');
        $this->db->join('bills_orders', 'bills_orders.id_orders = orders.id');
        return $this->db->get_where('orders', array('orders.type' => 0, 'bills_orders.paid' => 1))->row();
    }

    function get_amount_by_payment_types() {
        $this->db->select("SUM(CASE WHEN orders.payment_method = '0' THEN bills_orders.discount_remainder ELSE 0 END) AS cash");
        $this->db->select("SUM(CASE WHEN orders.payment_method = '1' THEN bills_orders.discount_remainder ELSE 0 END) AS knet");
        $this->db->select("SUM(CASE WHEN orders.payment_method = '2' THEN bills_orders.discount_remainder ELSE 0 END) AS visa");
        $this->db->select("SUM(CASE WHEN orders.payment_method = '3' THEN bills_orders.discount_remainder ELSE 0 END) AS link");
        $this->db->join('bills_orders', 'bills_orders.id_orders = orders.id');
        return $this->db->get_where('orders', array('orders.type' => 0, 'bills_orders.paid' => 1))->row();
    }

    function get_best_selling_products() {
        $this->db->select('bills_orders.name_order,count(bills_orders.quantity) as total_count,sum(bills_orders.discount_remainder) as total_amount');
        $this->db->join('bills_orders', 'bills_orders.id_orders = orders.id');
        $this->db->group_by('bills_orders.id_producer,bills_orders.name_order');
        $this->db->order_by('total_count', 'DESC');
        return $this->db->get_where('orders', array('orders.type' => 0, 'bills_orders.paid' => 1))->result();
    }

    function get_clients() {
        $this->db->select('DISTINCT bills_user.id,bills_user.username as name,bills_user.phone,bills_user.email', FALSE);
        $this->db->join('bills_orders', 'bills_orders.id_orders = orders.id');
        $this->db->join('bills_user', 'bills_user.id = bills_orders.id_user');
        return $this->db->get_where('orders', array('orders.type' => 0, 'bills_orders.paid' => 1))->result();
    }

    function get_order_ajax($length, $start, $seach_val, $call_center, $filter, $type, $order_status, $order_col, $order_dir) {

        $this->db->from('bills_orders');
        if ($order_col == -1 || $order_col == 0) {
            $this->db->order_by('orders.timestamp', 'desc');
        } else if ($order_col == 1) {
            $this->db->order_by('orders.timestamp', $order_dir);
        } else if ($order_col == 2) {
            $this->db->order_by('id_orders', $order_dir);
        } else if ($order_col == 3) {
            $this->db->order_by('bills_user.username', $order_dir);
        } else if ($order_col == 4) {
            $this->db->order_by('total_price', $order_dir);
        } //else if ($order_col == 6) {
        // $this->db->order_by('created_name', $order_dir);
        //}


        if ($length != -1 && $length) {
            $this->db->limit($length, $start);
        }
        $this->db->select('bills_orders.*,sum(bills_orders.discount_remainder) as total_price,user.name as created_name,orders.order_status,orders.order_status,orders.delivery_type,'
                . 'orders.payment_method,bills_user.username,bills_user.phone,bills_user.phone_code,bills_user.email,orders.delivery_type,orders.expected_delivery_time,orders.timestamp,addresses.region');
        $this->db->group_by('id_orders');

        if (isset($filter->start_expiry) && $filter->start_expiry) {
            $date_arr = explode(' - ', $filter->start_expiry);
            $from = date('Y-m-d', strtotime($date_arr[0]));
            $to = date('Y-m-d', strtotime($date_arr[1]));
            $this->db->where(array('orders.timestamp >=' => $from, 'orders.timestamp <=' => $to));
        }
        if (isset($filter->products) && !empty($filter->products)) {
            $this->db->where_in('bills_orders.name_order', $filter->products);
        }
        if (isset($filter->username) && !empty($filter->username)) {
            $this->db->where_in('bills_orders.name', $filter->username);
        }
        if (isset($filter->order_status) && !empty($filter->order_status)) {
            $this->db->where_in('orders.order_status', $filter->order_status);
        }
//        else if (empty($filter)) {
//            $this->db->where('orders.order_status', 0);
//        }
        if (isset($filter->admins) && !empty($filter->admins)) {
            $this->db->where_in('orders.created_by', $filter->admins);
        }
        if (isset($filter->payment_status) && $filter->payment_status != 0) {
            $this->db->where_in('bills_orders.paid', $filter->payment_status);
        }
        if ($seach_val) {
            $payment_method = array($this->lang->line('cash'), 'Knit', 'Visa/Master', $this->lang->line('link'));
            $this->db->group_start();
            $this->db->like('bills_user.username', $seach_val);
            $this->db->or_like('bills_user.phone', $seach_val);
            $this->db->or_like('bills_user.email', $seach_val);
            $this->db->or_like('orders.id', $seach_val);
            $this->db->or_like('user.name', $seach_val);
            $this->db->or_like('addresses.region', $seach_val);
            if (array_search($seach_val, $payment_method) !== FALSE) {
                $this->db->or_like('orders.payment_method', array_search($seach_val, $payment_method));
            }
            $this->db->group_end();
        }

        $this->db->join('orders', 'orders.id=bills_orders.id_orders');
        $this->db->join('user', 'user.id=orders.created_by', 'left');
        $this->db->join('bills_user', 'bills_user.id=bills_orders.id_user', 'left');
        $this->db->join('addresses', 'addresses.id = orders.address_id', 'left');
        if ($type) {
            $this->db->where('type', 1);
        } else {
            $this->db->where('type', 0);
            if ($call_center) {
                $this->db->where('call_center', 1);
            } else {
                $this->db->group_start();
                $this->db->group_start();
                $this->db->where('call_center', 1);
                $this->db->where('order_status !=', 0);
                $this->db->where('order_status !=', 5);
                $this->db->group_end();
                $this->db->or_where('call_center', 0);
                $this->db->group_end();
            }
            if ($order_status != -1) {
                $this->db->where('order_status', $order_status);
            }
        }
        if ($length == 0) {
            return $this->db->get()->num_rows();
        }
        return $this->db->get()->result_array();
        // echo $this->db->last_query();die;
    }

    function get_order_details($order_id) {

        $this->db->select('bills_user.username as client_name,bills_user.phone_code,bills_user.phone,sum(bills_orders.discount_remainder) as total_amount,orders.id,orders.payment_details,orders.payment_time');
        $this->db->join('bills_orders', 'bills_orders.id_orders = orders.id');
        $this->db->join('bills_user', 'bills_user.id = bills_orders.id_user');
        $this->db->group_by('bills_orders.id_orders');
        $order = $this->db->get_where('orders', array('orders.id' => $order_id))->row();
        if ($order) {
            if ($order->payment_details) {
                $tries = explode('/', $order->payment_details);

                $success_payment = $tries[count($tries) - 1];
                $pay_details = json_decode($success_payment);
                $order->ref_id = $pay_details->REFERENCE_ID;
                $order->trans_id = $pay_details->TRANSACTION_ID;
                $order->payment_id = $pay_details->PAYMENT_ID;
                $order->payment_status = $pay_details->PAYMENT_STATUS;
                $order->payment_gateway = 'Knet';
            }
            return $order;
        }
        return FALSE;
    }

    function get_payment_data($filter) {
        $where = '';
        if ($filter) {
//            print_R($filter);
//            die;
            if ($filter['inv_num']) {
                $where .= ' AND id=' . $filter['inv_num'];
            }
            if (isset($filter['from']) && isset($filter['to'])) {
                $where .= ' AND payment_time >= "' . $filter['from'] . '" AND payment_time <="' . $filter['to'] . '"';
            }
        }
        $pay = $this->db->query("SELECT `id`, `timestamp`, `payment_details`, `payment_method`, `payment_time`, `TRACKING_ID`, 'invoice' as type"
                        . " FROM `orders` "
                        . "WHERE `TRACKING_ID` != '' $where "
                        . "UNION "
                        . "SELECT `id`, `created_at` as `timestamp`, `payment_details`, `payment_method`, `payment_time`, `TRACKING_ID`, 'pay_order' as type"
                        . " FROM `payment_orders` "
                        . "WHERE `TRACKING_ID` != '' $where "
                        . "ORDER BY `payment_time` DESC")->result();

        $payments = array();
        foreach ($pay as $order) {
            if ($filter && $filter['type'] == 0 && $order->type == 'pay_order') {
                continue;
            } else if ($filter && $filter['type'] == 1 && $order->type == 'invoice') {
                continue;
            }
            $tries = explode('/', $order->payment_details);
            if (isset($tries[0]))
                unset($tries[0]);
            $arr = array('id' => $order->id, 'order_added' => $order->timestamp,
                'payment_method' => $order->payment_method, 'payment_time' => $order->payment_time, 'type' => $order->type, 'g_tracking_id' => $order->TRACKING_ID);
            if (($order->payment_details == '')) {
                if (!$filter || (($filter['status'] == -1 || $filter['status'] == 1) &&
                        ($filter['trans_id'] == '') &&
                        ($filter['ref_id'] == '') &&
                        ($filter['amount'] == '') &&
                        ($filter['track_id'] == '' || $filter['track_id'] == $order->TRACKING_ID))) {
                    $payments[] = $arr;
                }
            }
            foreach ($tries as $try) {
                if ($try) {
                    $arr['payment_data'] = json_decode($try);
                    if (!$filter || (($filter['status'] == -1 || ($filter['status'] == 0 && $arr['payment_data']->STATUS == 'successful') ||
                            ($filter['status'] == 1 && $arr['payment_data']->STATUS == 'failed')) &&
                            ($filter['trans_id'] == '' || ($filter['trans_id'] && $filter['trans_id'] == $arr['payment_data']->TRANSACTION_ID)) &&
                            ($filter['ref_id'] == '' || ($filter['ref_id'] && $filter['ref_id'] == $arr['payment_data']->REFERENCE_ID)) &&
                            ($filter['track_id'] == '' || ($filter['track_id'] && $filter['track_id'] == $arr['payment_data']->TRACKING_ID)) &&
                            ($filter['amount'] == '' || ($filter['amount'] && $filter['amount'] == $arr['payment_data']->AMOUNT)))) {
                        $payments[] = $arr;
                    }
                }
            }
        }
        return $payments;
    }

    function get_product_additions($product_id) {
        $adds = $this->db->get_where('product_additions', array('product_id' => $product_id))->result();
        $col = $this->session->userdata('language') == 'english' ? 'name_en' : 'name_ar';
        foreach ($adds as $add) {
            $add->options_ids = $add->options;
            $options = explode(',', $add->options);
            $this->db->select('name_en,name_ar');
            $this->db->where_in('id', $options);
            $options_query = $this->db->get('additions')->result();
            $options_names = array();
            foreach ($options_query as $row) {
                $options_names[] = $row->$col;
            }
            $add->options = implode(',', $options_names);
        }
        return $adds;
    }

    function get_order_products($order_id) {
        $this->db->select('name_order,bills_orders.start_date,bills_orders.expiry_date,payment,discount,quantity,bills_orders.comment,'
                . 'bills_producer.name as prod_name_en,bills_producer.arabic_name as prod_name_ar,discount,discount_remainder,'
                . 'bills_orders.item_price,bills_orders.*');
        $this->db->join('bills_producer', 'bills_producer.id = bills_orders.id_producer');
        $query = $this->db->get_where('bills_orders', array('bills_orders.id_orders' => $order_id));
        $producer = $query->result();
        foreach ($producer as $order_product) {
            $order_product->additions = new stdClass();
            $order_product->additions_arr = array();
            $order_product->additions = '';
            $additions_qu = $this->db->get_where('order_products_additions', array('order_product_id' => $order_product->id))->result();
            $price = 0;
            foreach ($additions_qu as $add) {
                $order_product->additions_arr[] = array('product_addition_id' => $add->product_addition_id, 'choice' => $add->choice_id);
                $options = explode(',', $add->choice_id);

                foreach ($options as $option) {
                    $selected = $this->db->get_where('additions', array('id' => $option))->row();

                    $order_product->additions .= $this->session->userdata('language') == 'english' ? $selected->name_en : $selected->name_ar;
                    $order_product->additions .= ' -- ' . $selected->price . ',';
                    $price += $selected->price;
                }
                $order_product->additions = substr_replace($order_product->additions, "", -1);
                $order_product->additions .= '<br/>';
            }
            $order_product->addition_price = $price;
        }
        return $producer;
    }

}
