<?php

class ModelAccountOrder extends Model {
  public function getOrder($order_id) {
    $sql = "SELECT o.* FROM `" . DB_PREFIX . "order` o "
      . " WHERE o.order_id = '" . (int)$order_id . "' AND o.customer_id = '" . (int)$this->customer->getId() . "' AND o.order_status_id > '0'";

    $order_query = $this->db->query($sql);

    if ($order_query->num_rows) {
      $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['payment_country_id'] . "'");

      if ($country_query->num_rows) {
        $payment_iso_code_2 = $country_query->row['iso_code_2'];
        $payment_iso_code_3 = $country_query->row['iso_code_3'];
      } else {
        $payment_iso_code_2 = '';
        $payment_iso_code_3 = '';
      }

      $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['payment_zone_id'] . "'");

      if ($zone_query->num_rows) {
        $payment_zone_code = $zone_query->row['code'];
      } else {
        $payment_zone_code = '';
      }

      $country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['shipping_country_id'] . "'");

      if ($country_query->num_rows) {
        $shipping_iso_code_2 = $country_query->row['iso_code_2'];
        $shipping_iso_code_3 = $country_query->row['iso_code_3'];
      } else {
        $shipping_iso_code_2 = '';
        $shipping_iso_code_3 = '';
      }

      $zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['shipping_zone_id'] . "'");

      if ($zone_query->num_rows) {
        $shipping_zone_code = $zone_query->row['code'];
      } else {
        $shipping_zone_code = '';
      }

      $shipping_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_total` WHERE order_id = '" . (int)$order_id . "' AND code = 'shipping' ");

      if ($shipping_query->num_rows) {
        $shipping_value = $shipping_query->row['value'];
      } else {
        $shipping_value = '';
      }

      $tracking_query = $this->db->query("SELECT os.tracking_code FROM " . DB_PREFIX . "order_shipping os WHERE os.order_id = '" . (int)$order_id . "'");

      if($tracking_query->num_rows){
        $tracking_query_codes = $tracking_query->rows;
      }else{
        $tracking_query_codes = false;
      }


      return array(
        'order_id' => $order_query->row['order_id'],
        'store_id' => $order_query->row['store_id'],
        'store_name' => $order_query->row['store_name'],
        'store_url' => $order_query->row['store_url'],
        'customer_id' => $order_query->row['customer_id'],
        'firstname' => $order_query->row['firstname'],
        'lastname' => $order_query->row['lastname'],
        'telephone' => $order_query->row['telephone'],
        'fax' => $order_query->row['fax'],
        'email' => $order_query->row['email'],
        'payment_apelido' => $order_query->row['payment_apelido'],
        'payment_numero' => $order_query->row['payment_numero'],
        'payment_complemento' => $order_query->row['payment_complemento'],
        'payment_firstname' => $order_query->row['payment_firstname'],
        'payment_lastname' => $order_query->row['payment_lastname'],
        'payment_company' => $order_query->row['payment_company'],
        'payment_address_1' => $order_query->row['payment_address_1'],
        'payment_address_2' => $order_query->row['payment_address_2'],
        'payment_postcode' => $order_query->row['payment_postcode'],
        'payment_city' => $order_query->row['payment_city'],
        'payment_zone_id' => $order_query->row['payment_zone_id'],
        'payment_zone' => $order_query->row['payment_zone'],
        'payment_zone_code' => $payment_zone_code,
        'payment_country_id' => $order_query->row['payment_country_id'],
        'payment_country' => $order_query->row['payment_country'],
        'payment_iso_code_2' => $payment_iso_code_2,
        'payment_iso_code_3' => $payment_iso_code_3,
        'payment_address_format' => $order_query->row['payment_address_format'],
        'payment_method' => $order_query->row['payment_method'],
        'payment_code' => $order_query->row['payment_code'],
        'shipping_apelido' => $order_query->row['shipping_apelido'],
        'shipping_numero' => $order_query->row['shipping_numero'],
        'shipping_complemento' => $order_query->row['shipping_complemento'],
        'shipping_firstname' => $order_query->row['shipping_firstname'],
        'shipping_lastname' => $order_query->row['shipping_lastname'],
        'shipping_company' => $order_query->row['shipping_company'],
        'shipping_address_1' => $order_query->row['shipping_address_1'],
        'shipping_address_2' => $order_query->row['shipping_address_2'],
        'shipping_postcode' => $order_query->row['shipping_postcode'],
        'shipping_city' => $order_query->row['shipping_city'],
        'shipping_zone_id' => $order_query->row['shipping_zone_id'],
        'shipping_zone' => $order_query->row['shipping_zone'],
        'shipping_zone_code' => $shipping_zone_code,
        'shipping_country_id' => $order_query->row['shipping_country_id'],
        'shipping_country' => $order_query->row['shipping_country'],
        'shipping_iso_code_2' => $shipping_iso_code_2,
        'shipping_iso_code_3' => $shipping_iso_code_3,
        'shipping_address_format' => $order_query->row['shipping_address_format'],
        'shipping_method' => $order_query->row['shipping_method'],
        'shipping_value' => $shipping_value,
        'shipping_code' => $order_query->row['shipping_code'],
        'comment' => $order_query->row['comment'],
        'total' => $order_query->row['total'],
        'order_status_id' => $order_query->row['order_status_id'],
        'language_id' => $order_query->row['language_id'],
        'currency_id' => $order_query->row['currency_id'],
        'currency_code' => $order_query->row['currency_code'],
        'currency_value' => $order_query->row['currency_value'],
        'date_modified' => $order_query->row['date_modified'],
        'date_added' => $order_query->row['date_added'],
        'ip' => $order_query->row['ip'],
        'tracking_code' => $tracking_query_codes,
        'total_pontos' => $order_query->row['total_pontos'],
        'fidelity' => (int)$order_query->row['fidelity'],
        'origem_tracking_code' => $order_query->row['origem_tracking_code'],
        'pickup_address' => $order_query->row['pickup_address'],
        'pickup_number' => $order_query->row['pickup_number'],
        'pickup_neighborhood' => $order_query->row['pickup_neighborhood'],
        'pickup_complement' => $order_query->row['pickup_complement'],
        'pickup_city' => stripslashes($order_query->row['pickup_city']),
        'pickup_postcode' => $order_query->row['pickup_postcode'],
        'pickup_zone_id' => $order_query->row['pickup_zone_id'],
        'pickup_zone' => $order_query->row['pickup_zone'],
        'status_futuraserver' => $order_query->row['status_futuraserver'],
        'codigo_integracao_pagamento' => $order_query->row['codigo_integracao_pagamento'],
        'payment_option_code' => $order_query->row['payment_option_code'],
      );
    } else {
      return false;
    }
  }

  public function getSearchOrders($data, $date_value, $start = 0, $limit = 20) {
    if ($start < 0) {
        $start = 0;
    }

    if ($limit < 1) {
        $limit = 1;
    }

    $sql = "SELECT DISTINCT
              o.order_id,
              o.firstname,
              o.total_pontos,
              o.lastname,
              os.name as status,
              os.order_type_status as type_status,
              o.date_added, o.total,
              o.currency_code,
              o.currency_value,
              o.fidelity
                FROM oc_order_product p
                INNER JOIN oc_order o ON p.order_id = o.order_id
                LEFT JOIN oc_order_status os ON (o.order_status_id = os.order_status_id)
                WHERE (CAST(p.order_id AS CHAR) LIKE '%".$data."%'  
                OR p.`name` LIKE '%".$data."%'
                OR p.product_id LIKE '%".$data."%'
                OR p.model LIKE '%".$data."%')
                AND o.customer_id = '" . (int)$this->customer->getId() . "'
                AND o.order_status_id > '0'
                AND o.date_added >= '".$date_value."'
                AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' ";

                if(isset($data['not_status_futuraserver'])){
                  $sql.= " AND not(o.status_futuraserver = ".$data['not_status_futuraserver']. ")";
                }
        
                if(isset($data['not_origem_pedido'])){
                    $sql.= " AND not(o.origem_pedido = ".$data['not_origem_pedido']. ")";
                }
      
                $sql.= "
                ORDER BY
                CASE
                WHEN CAST(p.order_id AS CHAR) LIKE '%".$data."%' THEN 0
                ELSE 1
                END,
                CAST(p.order_id AS UNSIGNED) DESC,
                p.`name` ASC,
                p.product_id ASC,
                p.model ASC
                LIMIT ". (int)$start ." , ". (int)$limit ." ";

    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getSearchOrdersAll($data, $date_value) {
    $sql = "SELECT COUNT(DISTINCT o.order_id) as quantidade
                FROM oc_order_product p
                INNER JOIN oc_order o ON p.order_id = o.order_id
                LEFT JOIN oc_order_status os ON (o.order_status_id = os.order_status_id)
                WHERE (CAST(p.order_id AS CHAR) LIKE '%".$data."%'
                OR p.`name` LIKE '%".$data."%'
                OR p.product_id LIKE '%".$data."%'
                OR p.model LIKE '%".$data."%')
                AND o.customer_id = '" . (int)$this->customer->getId() . "'
                AND o.order_status_id > '0'
                AND o.date_added >= '".$date_value."'
                AND os.language_id = '" . (int)$this->config->get('config_language_id') . "'
                ORDER BY
                CASE
                WHEN CAST(p.order_id AS CHAR) LIKE '%".$data."%' THEN 0
                ELSE 1
                END,
                CAST(p.order_id AS UNSIGNED),   
                p.`name` ASC,
                p.product_id ASC,
                p.model ASC";

    $query = $this->db->query($sql);

    return $query->row;
  }

  public function getOrders($start = 0, $limit = 20, $data = array()) {
    if ($start < 0) {
      $start = 0;
    }

    if ($limit < 1) {
      $limit = 1;
    }

    $sql = "SELECT
                  o.order_id,
                  o.firstname,
                  o.total_pontos,
                  o.lastname,
                  os.name as status,
                  os.order_type_status as type_status,
                  o.date_added, o.total,
                  o.currency_code,
                  o.currency_value,
                  o.fidelity
              FROM `" . DB_PREFIX . "order` o
              LEFT JOIN " . DB_PREFIX . "order_status os ON (o.order_status_id = os.order_status_id)
              WHERE o.customer_id = '" . (int)$this->customer->getId() . "' AND o.order_status_id > '0' AND os.language_id = '" . (int)$this->config->get('config_language_id') . "'";

        if(isset($data['not_status_futuraserver'])){
            $sql.= " AND not(o.status_futuraserver = ".$data['not_status_futuraserver']. ")";
        }

        if(isset($data['not_origem_pedido'])){
            $sql.= " AND not(o.origem_pedido = ".$data['not_origem_pedido']. ")";
        }

        $sql.= "ORDER BY o.order_id DESC LIMIT " . (int)$start . "," . (int)$limit;

    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getImagesOrder($order_id) {
    $arrayImg = array();
    $sql = 'SELECT p.image, p.data_atualizacao_replicacao FROM ' . DB_PREFIX . 'order_product op '
      . 'INNER JOIN ' . DB_PREFIX . 'product p ON (op.product_id = p.product_id) '
      . ' WHERE op.order_id = ' . (int)$order_id;

    $query = $this->db->query($sql);

    if ($query->rows) {
      foreach ($query->rows as $key => $value) {
        $arrayImg[] = array(
          'image' => $value['image'],
          'data_atualizacao_replicacao' => $value['data_atualizacao_replicacao']
        );
      }
    }

    return $arrayImg;
  }

  public function getOrderComment($order_id) {
    $query = $this->db->query("SELECT comment FROM " . DB_PREFIX . "order WHERE order_id = '" . (int)$order_id . "' ");
    return $query->row['comment'];
  }

  public function getOrderOptions($order_id, $order_product_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product_id . "'");
    return $query->rows;
  }

  public function getOrderVouchers($order_id) {
    $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE order_id = '" . (int)$order_id . "'");
    return $query->rows;
  }

  public function getOrderTotals($order_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "' ORDER BY sort_order");
    return $query->rows;
  }

  public function getOrderTotalsByCode($order_id, $code) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "' and code = '" . $code . "' ORDER BY sort_order");
    return $query->row;
  }

  public function getOrderHistories($order_id) {
    $query = $this->db->query("SELECT date_added, os.name AS status, os.order_type_status AS type_status, oh.comment, oh.notify, os.order_status_id AS id FROM " . DB_PREFIX . "order_history oh LEFT JOIN " . DB_PREFIX . "order_status os ON oh.order_status_id = os.order_status_id WHERE oh.order_id = '" . (int)$order_id . "' AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY oh.date_added");

    return $query->rows;
  }

  public function getTotalOrders() {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE customer_id = '" . (int)$this->customer->getId() . "' AND order_status_id > '0'");
    return $query->row['total'];
  }

  public function getTotalOrderProductsByOrderId($order_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
    return $query->row['total'];
  }

  public function getTotalOrderVouchersByOrderId($order_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order_voucher` WHERE order_id = '" . (int)$order_id . "'");
    return $query->row['total'];
  }

}
