<?php
class ModelSaleOrder extends Model {
  public function addOrder($data) {
    $store_name = $this->config->get('config_name');
    $store_url = $this->urlloja->link('', '', 'SSL', false);


    $this->load->model('setting/setting');

    $setting_info = $this->model_setting_setting->getSetting('setting', $data['store_id']);

    $this->load->model('localisation/country');

    $this->load->model('localisation/zone');

    $country_info = $this->model_localisation_country->getCountry($data['shipping_country_id']);

    if ($country_info) {
      $shipping_country = $country_info['name'];
      $shipping_address_format = $country_info['address_format'];
    } else {
      $shipping_country = '';
      $shipping_address_format = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
    }

    $zone_info = $this->model_localisation_zone->getZone($data['shipping_zone_id']);

    if ($zone_info) {
      $shipping_zone = $zone_info['name'];
    } else {
      $shipping_zone = '';
    }

    $country_info = $this->model_localisation_country->getCountry($data['payment_country_id']);

    if ($country_info) {
      $payment_country = $country_info['name'];
      $payment_address_format = $country_info['address_format'];
    } else {
      $payment_country = '';
      $payment_address_format = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
    }

    $zone_info = $this->model_localisation_zone->getZone($data['payment_zone_id']);

    if ($zone_info) {
      $payment_zone = $zone_info['name'];
    } else {
      $payment_zone = '';
    }

    $this->load->model('localisation/currency');

    $currency_info = $this->model_localisation_currency->getCurrencyByCode($this->config->get('config_currency'));

    if ($currency_info) {
      $currency_id = $currency_info['currency_id'];
      $currency_code = $currency_info['code'];
      $currency_value = $currency_info['value'];
    } else {
      $currency_id = 0;
      $currency_code = $this->config->get('config_currency');
      $currency_value = 1.00000;
    }


    $dataparts = explode('/', $data['data_nascimento']);
    if (sizeof($dataparts) == '3'){
      $data['data_nascimento'] = $dataparts[2]."-".$dataparts[1]."-".$dataparts[0];
    }else{
      $data['data_nascimento'] = '0000-00-00';
    }

    $data['firstname']          = ucwords($data['firstname']);
    $data['lastname']           = ucwords($data['lastname']);
    $data['email']              = strtr(strtolower($data['email']),"ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÜÚÞß","àáâãäåæçèéêëìíîïðñòóôõö÷øùüúþÿ");
    $data['telephone']          = preg_replace('/([^0-9])/i','',$data['telephone']);
    $data['fax']                = preg_replace('/([^0-9])/i','',$data['fax']);
    $data['cpf']                = preg_replace('/([^0-9])/i','',$data['cpf']);
    $data['rg']                 = preg_replace('/([^0-9])/i','',$data['rg']);

    if(isset($data['cnpj'])){
      $data['cnpj']               = preg_replace('/([^0-9])/i','',$data['cnpj']);
    }
    else{
      $data['cnpj'] = '';
    }

    if(isset($data['perfil'])){
      $data['perfil']             = $data['perfil'];
    }
    else{
      $data['perfil'] = '';
    }

    if(isset($data['contribuinte'])){
      $data['contribuinte']		= $data['contribuinte'];
    }
    else {
      $data['contribuinte'] = '';
    }

    if(isset($data['inscricao_estadual'])){
      $data['inscricao_estadual'] = $data['inscricao_estadual'];
    }
    else{
      $data['inscricao_estadual'] = '';
    }

    $this->db->query("INSERT INTO `" . DB_PREFIX . "order` SET store_id = '" . (int)$data['store_id'] . "', store_name = '" . $this->db->escape($store_name) . "',store_url = '" . $this->db->escape($store_url) . "', customer_id = '" . (int)$data['customer_id'] . "', customer_group_id = '" . (int)$data['customer_group_id'] . "', firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "', cpf = '" . $this->db->escape($data['cpf']) . "', cnpj = '" . $this->db->escape($data['cnpj']) . "', rg = '" . $this->db->escape($data['rg']) . "', razao_social = '" . $this->db->escape($data['razao_social']) . "', inscricao_estadual = '" . $this->db->escape($data['inscricao_estadual']) . "', data_nascimento = '" . $this->db->escape($data['data_nascimento']) . "', sexo = '" . $this->db->escape($data['sexo']) . "', payment_apelido = '" . $this->db->escape($data['payment_apelido']) . "', payment_firstname = '" . $this->db->escape($data['payment_firstname']) . "', payment_lastname = '" . $this->db->escape($data['payment_lastname']) . "', payment_company = '" . $this->db->escape($data['payment_company']) . "', payment_company_id = '" . $this->db->escape($data['payment_company_id']) . "', payment_address_1 = '" . $this->db->escape($data['payment_address_1']) . "', payment_numero = '" . $this->db->escape($data['payment_numero']) . "', payment_address_2 = '" . $this->db->escape($data['payment_address_2']) . "', payment_complemento = '" . $this->db->escape($data['payment_complemento']) . "', payment_city = '" . $this->db->escape($data['payment_city']) . "', payment_postcode = '" . $this->db->escape($data['payment_postcode']) . "', payment_country = '" . $this->db->escape($payment_country) . "', payment_country_id = '" . (int)$data['payment_country_id'] . "', payment_zone = '" . $this->db->escape($payment_zone) . "', payment_zone_id = '" . (int)$data['payment_zone_id'] . "', payment_address_format = '" . $this->db->escape($payment_address_format) . "', payment_method = '" . $this->db->escape($data['payment_method']) . "', payment_code = '" . $this->db->escape($data['payment_code']) . "', shipping_apelido = '" . $this->db->escape($data['shipping_apelido']) . "', shipping_firstname = '" . $this->db->escape($data['shipping_firstname']) . "', shipping_lastname = '" . $this->db->escape($data['shipping_lastname']) . "', shipping_company = '" . $this->db->escape($data['shipping_company']) . "', shipping_address_1 = '" . $this->db->escape($data['shipping_address_1']) . "', shipping_numero = '" . $this->db->escape($data['shipping_numero']) . "', shipping_address_2 = '" . $this->db->escape($data['shipping_address_2']) . "', shipping_complemento = '" . $this->db->escape($data['shipping_complemento']) . "', shipping_city = '" . $this->db->escape($data['shipping_city']) . "', shipping_postcode = '" . $this->db->escape($data['shipping_postcode']) . "', shipping_country = '" . $this->db->escape($shipping_country) . "', shipping_country_id = '" . (int)$data['shipping_country_id'] . "', shipping_zone = '" . $this->db->escape($shipping_zone) . "', shipping_zone_id = '" . (int)$data['shipping_zone_id'] . "', shipping_address_format = '" . $this->db->escape($shipping_address_format) . "', shipping_method = '" . $this->db->escape($data['shipping_method']) . "', shipping_code = '" . $this->db->escape($data['shipping_code']) . "', comment = '" . $this->db->escape($data['comment']) . "', order_status_id = '" . (int)$data['order_status_id'] . "', affiliate_id  = '" . (int)$data['affiliate_id'] . "', language_id = '" . (int)$this->config->get('config_language_id') . "', currency_id = '" . (int)$currency_id . "', currency_code = '" . $this->db->escape($currency_code) . "', currency_value = '" . (float)$currency_value . "', date_added = NOW(), date_modified = NOW()");

    $order_id = $this->db->getLastId();

    if (isset($data['order_product'])) {
      foreach ($data['order_product'] as $order_product) {
        $this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', reward = '" . (int)$order_product['reward'] . "'");

        $order_product_id = $this->db->getLastId();

        $this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = '" . (int)$order_product['product_id'] . "' AND subtract = '1'");

        if (isset($order_product['order_option'])) {
          foreach ($order_product['order_option'] as $order_option) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$order_option['product_option_id'] . "', product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "', name = '" . $this->db->escape($order_option['name']) . "', `value` = '" . $this->db->escape($order_option['value']) . "', `type` = '" . $this->db->escape($order_option['type']) . "'");

            $this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "' AND subtract = '1'");
          }
        }
      }
    }

    if (isset($data['order_voucher'])) {
      foreach ($data['order_voucher'] as $order_voucher) {
        $this->db->query("INSERT INTO " . DB_PREFIX . "order_voucher SET order_id = '" . (int)$order_id . "', voucher_id = '" . (int)$order_voucher['voucher_id'] . "', description = '" . $this->db->escape($order_voucher['description']) . "', code = '" . $this->db->escape($order_voucher['code']) . "', from_name = '" . $this->db->escape($order_voucher['from_name']) . "', from_email = '" . $this->db->escape($order_voucher['from_email']) . "', to_name = '" . $this->db->escape($order_voucher['to_name']) . "', to_email = '" . $this->db->escape($order_voucher['to_email']) . "', voucher_theme_id = '" . (int)$order_voucher['voucher_theme_id'] . "', message = '" . $this->db->escape($order_voucher['message']) . "', amount = '" . (float)$order_voucher['amount'] . "'");

        $this->db->query("UPDATE " . DB_PREFIX . "voucher SET order_id = '" . (int)$order_id . "' WHERE voucher_id = '" . (int)$order_voucher['voucher_id'] . "'");
      }
    }

    // Get the total
    $total = 0;

    if (isset($data['order_total'])) {
      foreach ($data['order_total'] as $order_total) {
        $this->db->query("INSERT INTO " . DB_PREFIX . "order_total SET order_id = '" . (int)$order_id . "', code = '" . $this->db->escape($order_total['code']) . "', title = '" . $this->db->escape($order_total['title']) . "', text = '" . $this->db->escape($order_total['text']) . "', `value` = '" . (float)$order_total['value'] . "', sort_order = '" . (int)$order_total['sort_order'] . "'");
      }

      $total += $order_total['value'];
    }

    // Affiliate
    $affiliate_id = 0;
    $commission = 0;

    if (!empty($this->request->post['affiliate_id'])) {
      $this->load->model('sale/affiliate');

      $affiliate_info = $this->model_sale_affiliate->getAffiliate($this->request->post['affiliate_id']);

      if ($affiliate_info) {
        $affiliate_id = $affiliate_info['affiliate_id'];
        $commission = ($total / 100) * $affiliate_info['commission'];
      }
    }

    // Update order total
    $this->db->query("UPDATE `" . DB_PREFIX . "order` SET total = '" . (float)$total . "', affiliate_id = '" . (int)$affiliate_id . "', commission = '" . (float)$commission . "' WHERE order_id = '" . (int)$order_id . "'");
  }

  public function editOrder($order_id, $data) {
    $this->load->model('localisation/country');

    $this->load->model('localisation/zone');

    $country_info = $this->model_localisation_country->getCountry($data['shipping_country_id']);

    if ($country_info) {
      $shipping_country = $country_info['name'];
      $shipping_address_format = $country_info['address_format'];
    } else {
      $shipping_country = '';
      $shipping_address_format = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
    }

    $zone_info = $this->model_localisation_zone->getZone($data['shipping_zone_id']);

    if ($zone_info) {
      $shipping_zone = $zone_info['name'];
    } else {
      $shipping_zone = '';
    }

    $country_info = $this->model_localisation_country->getCountry($data['payment_country_id']);

    if ($country_info) {
      $payment_country = $country_info['name'];
      $payment_address_format = $country_info['address_format'];
    } else {
      $payment_country = '';
      $payment_address_format = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
    }

    $zone_info = $this->model_localisation_zone->getZone($data['payment_zone_id']);

    if ($zone_info) {
      $payment_zone = $zone_info['name'];
    } else {
      $payment_zone = '';
    }

    // Restock products before subtracting the stock later on
    $order_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND order_id = '" . (int)$order_id . "'");

    if ($order_query->num_rows) {
      $product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");

      foreach($product_query->rows as $product) {
        $this->db->query("UPDATE `" . DB_PREFIX . "product` SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_id = '" . (int)$product['product_id'] . "' AND subtract = '1'");

        $option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$product['order_product_id'] . "'");

        foreach ($option_query->rows as $option) {
          $this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_option_value_id = '" . (int)$option['product_option_value_id'] . "' AND subtract = '1'");
        }
      }
    }


    $dataparts = explode('/', $data['data_nascimento']);
    if (sizeof($dataparts) == '3'){
      $data['data_nascimento'] = $dataparts[2]."-".$dataparts[1]."-".$dataparts[0];
    }else{
      $data['data_nascimento'] = '0000-00-00';
    }

    $data['firstname']          = ucwords($data['firstname']);
    $data['lastname']           = ucwords($data['lastname']);
    $data['email']              = strtr(strtolower($data['email']),"ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÜÚÞß","àáâãäåæçèéêëìíîïðñòóôõö÷øùüúþÿ");
    $data['telephone']          = preg_replace('/([^0-9])/i','',$data['telephone']);
    $data['fax']                = preg_replace('/([^0-9])/i','',$data['fax']);
    $data['cpf']                = preg_replace('/([^0-9])/i','',$data['cpf']);
    $data['rg']                 = preg_replace('/([^0-9])/i','',$data['rg']);

    if(isset($data['cnpj'])){
      $data['cnpj']               = preg_replace('/([^0-9])/i','',$data['cnpj']);
    }
    else{
      $data['cnpj'] = '';
    }

    if(isset($data['perfil'])){
      $data['perfil']             = $data['perfil'];
    }
    else{
      $data['perfil'] = '';
    }

    if(isset($data['contribuinte'])){
      $data['contribuinte']		= $data['contribuinte'];
    }
    else {
      $data['contribuinte'] = '';
    }

    if(isset($data['inscricao_estadual'])){
      $data['inscricao_estadual'] = $data['inscricao_estadual'];
    }
    else{
      $data['inscricao_estadual'] = '';
    }


    $this->db->query("UPDATE `" . DB_PREFIX . "order` SET firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "', cpf = '" . $this->db->escape($data['cpf']) . "', cnpj = '" . $this->db->escape($data['cnpj']) . "', rg = '" . $this->db->escape($data['rg']) . "', razao_social = '" . $this->db->escape($data['razao_social']) . "', inscricao_estadual = '" . $this->db->escape($data['inscricao_estadual']) . "', data_nascimento = '" . $this->db->escape($data['data_nascimento']) . "', sexo = '" . $this->db->escape($data['sexo']) . "', payment_apelido = '" . $this->db->escape($data['payment_apelido']) . "', payment_firstname = '" . $this->db->escape($data['payment_firstname']) . "', payment_lastname = '" . $this->db->escape($data['payment_lastname']) . "', payment_company = '" . $this->db->escape($data['payment_company']) . "', payment_company_id = '" . $this->db->escape($data['payment_company_id']) . "', payment_address_1 = '" . $this->db->escape($data['payment_address_1']) . "', payment_numero = '" . $this->db->escape($data['payment_numero']) . "', payment_address_2 = '" . $this->db->escape($data['payment_address_2']) . "', payment_complemento = '" . $this->db->escape($data['payment_complemento']) . "', payment_city = '" . $this->db->escape($data['payment_city']) . "', payment_postcode = '" . $this->db->escape($data['payment_postcode']) . "', payment_country = '" . $this->db->escape($payment_country) . "', payment_country_id = '" . (int)$data['payment_country_id'] . "', payment_zone = '" . $this->db->escape($payment_zone) . "', payment_zone_id = '" . (int)$data['payment_zone_id'] . "', payment_address_format = '" . $this->db->escape($payment_address_format) . "', payment_method = '" . $this->db->escape($data['payment_method']) . "', payment_code = '" . $this->db->escape($data['payment_code']) . "', shipping_apelido = '" . $this->db->escape($data['shipping_apelido']) . "', shipping_firstname = '" . $this->db->escape($data['shipping_firstname']) . "', shipping_lastname = '" . $this->db->escape($data['shipping_lastname']) . "',  shipping_company = '" . $this->db->escape($data['shipping_company']) . "', shipping_address_1 = '" . $this->db->escape($data['shipping_address_1']) . "', shipping_numero = '" . $this->db->escape($data['shipping_numero']) . "', shipping_address_2 = '" . $this->db->escape($data['shipping_address_2']) . "', shipping_complemento = '" . $this->db->escape($data['shipping_complemento']) . "', shipping_city = '" . $this->db->escape($data['shipping_city']) . "', shipping_postcode = '" . $this->db->escape($data['shipping_postcode']) . "', shipping_country = '" . $this->db->escape($shipping_country) . "', shipping_country_id = '" . (int)$data['shipping_country_id'] . "', shipping_zone = '" . $this->db->escape($shipping_zone) . "', shipping_zone_id = '" . (int)$data['shipping_zone_id'] . "', shipping_address_format = '" . $this->db->escape($shipping_address_format) . "', shipping_method = '" . $this->db->escape($data['shipping_method']) . "', shipping_code = '" . $this->db->escape($data['shipping_code']) . "', comment = '" . $this->db->escape($data['comment']) . "', order_status_id = '" . (int)$data['order_status_id'] . "', affiliate_id  = '" . (int)$data['affiliate_id'] . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");


    $this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "'");

    if (isset($data['order_product'])) {
      foreach ($data['order_product'] as $order_product) {
        $this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_product_id = '" . (int)$order_product['order_product_id'] . "', order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', reward = '" . (int)$order_product['reward'] . "'");

        $order_product_id = $this->db->getLastId();

        $this->db->query("UPDATE " . DB_PREFIX . "product SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_id = '" . (int)$order_product['product_id'] . "' AND subtract = '1'");

        if (isset($order_product['order_option'])) {
          foreach ($order_product['order_option'] as $order_option) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_option_id = '" . (int)$order_option['order_option_id'] . "', order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$order_option['product_option_id'] . "', product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "', name = '" . $this->db->escape($order_option['name']) . "', `value` = '" . $this->db->escape($order_option['value']) . "', `type` = '" . $this->db->escape($order_option['type']) . "'");


            $this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity - " . (int)$order_product['quantity'] . ") WHERE product_option_value_id = '" . (int)$order_option['product_option_value_id'] . "' AND subtract = '1'");
          }
        }
      }
    }

    $this->db->query("DELETE FROM " . DB_PREFIX . "order_voucher WHERE order_id = '" . (int)$order_id . "'");

    if (isset($data['order_voucher'])) {
      foreach ($data['order_voucher'] as $order_voucher) {
        $this->db->query("INSERT INTO " . DB_PREFIX . "order_voucher SET order_voucher_id = '" . (int)$order_voucher['order_voucher_id'] . "', order_id = '" . (int)$order_id . "', voucher_id = '" . (int)$order_voucher['voucher_id'] . "', description = '" . $this->db->escape($order_voucher['description']) . "', code = '" . $this->db->escape($order_voucher['code']) . "', from_name = '" . $this->db->escape($order_voucher['from_name']) . "', from_email = '" . $this->db->escape($order_voucher['from_email']) . "', to_name = '" . $this->db->escape($order_voucher['to_name']) . "', to_email = '" . $this->db->escape($order_voucher['to_email']) . "', voucher_theme_id = '" . (int)$order_voucher['voucher_theme_id'] . "', message = '" . $this->db->escape($order_voucher['message']) . "', amount = '" . (float)$order_voucher['amount'] . "'");

        $this->db->query("UPDATE " . DB_PREFIX . "voucher SET order_id = '" . (int)$order_id . "' WHERE voucher_id = '" . (int)$order_voucher['voucher_id'] . "'");
      }
    }

    // Get the total
    $total = 0;

    $this->db->query("DELETE FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "'");

    if (isset($data['order_total'])) {
      foreach ($data['order_total'] as $order_total) {
        $this->db->query("INSERT INTO " . DB_PREFIX . "order_total SET order_total_id = '" . (int)$order_total['order_total_id'] . "', order_id = '" . (int)$order_id . "', code = '" . $this->db->escape($order_total['code']) . "', title = '" . $this->db->escape($order_total['title']) . "', text = '" . $this->db->escape($order_total['text']) . "', `value` = '" . (float)$order_total['value'] . "', sort_order = '" . (int)$order_total['sort_order'] . "'");
      }

      $total += $order_total['value'];
    }

    // Affiliate
    $affiliate_id = 0;
    $commission = 0;

    if (!empty($this->request->post['affiliate_id'])) {
      $this->load->model('sale/affiliate');

      $affiliate_info = $this->model_sale_affiliate->getAffiliate($this->request->post['affiliate_id']);

      if ($affiliate_info) {
        $affiliate_id = $affiliate_info['affiliate_id'];
        $commission = ($total / 100) * $affiliate_info['commission'];
      }
    }

    $this->db->query("UPDATE `" . DB_PREFIX . "order` SET total = '" . (float)$total . "', affiliate_id = '" . (int)$affiliate_id . "', commission = '" . (float)$commission . "' WHERE order_id = '" . (int)$order_id . "'");
  }

  public function deleteOrder($order_id) {
    $order_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND order_id = '" . (int)$order_id . "'");

    if ($order_query->num_rows) {
      $product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");

      foreach($product_query->rows as $product) {
        $this->db->query("UPDATE `" . DB_PREFIX . "product` SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_id = '" . (int)$product['product_id'] . "' AND subtract = '1'");

        $option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$product['order_product_id'] . "'");

        foreach ($option_query->rows as $option) {
          $this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_option_value_id = '" . (int)$option['product_option_value_id'] . "' AND subtract = '1'");
        }
      }
    }

    $this->db->query("DELETE FROM `" . DB_PREFIX . "order` WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "order_voucher WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "order_history WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "order_fraud WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "customer_transaction WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "customer_reward WHERE order_id = '" . (int)$order_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "affiliate_transaction WHERE order_id = '" . (int)$order_id . "'");


    $this->load->model('futura/listapresente');
    $itens = $this->model_futura_listapresente->GetItensPedido($order_id);

    if($itens){
      $this->model_futura_listapresente->devolveEstoqueItem($itens);
    }


    $this->db->query("DELETE `or`, ort FROM " . DB_PREFIX . "order_recurring `or`, " . DB_PREFIX . "order_recurring_transaction ort WHERE order_id = '" . (int)$order_id . "' AND ort.order_recurring_id = `or`.order_recurring_id");
  }

  public function setOrderCustomer($order_id, $customer_data){

      if(!isset($customer_data['customer_id'])){
          $customer_data['customer_id'] = 0;
      }

      if(!isset($customer_data['customer_group_id'])){
          $customer_data['customer_group_id'] = 0;
      }

      if(!isset($customer_data['sexo'])){
          $customer_data['sexo'] = '';
      }

      if(!isset($customer_data['inscricao_estadual'])){
          $customer_data['inscricao_estadual'] = '';
      }

      if(strpos($customer_data['data_nascimento'], '/')){
          $dataparts = explode('/', $customer_data['data_nascimento']);
          if (sizeof($dataparts) == '3'){
              $customer_data['data_nascimento'] = $dataparts[2]."-".$dataparts[1]."-".$dataparts[0];
          } else {
              $customer_data['data_nascimento'] = '0000-00-00';
          }
      } elseif(!strpos($customer_data['data_nascimento'], '-')) {
          $customer_data['data_nascimento'] = '0000-00-00';
      }

      $customer_data['razao_social']       = ucwords($customer_data['razao_social']);
      $customer_data['firstname']          = ucwords($customer_data['firstname']);
      $customer_data['lastname']           = ucwords($customer_data['lastname']);
      $customer_data['email']              = strtr(strtolower($customer_data['email']),"ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÜÚÞß","àáâãäåæçèéêëìíîïðñòóôõö÷øùüúþÿ");
      $customer_data['telephone']          = preg_replace('/([^0-9])/i','',$customer_data['telephone']);
      $customer_data['fax']                = preg_replace('/([^0-9])/i','',$customer_data['fax']);
      $customer_data['cpf']                = preg_replace('/([^0-9])/i','',$customer_data['cpf']);
      $customer_data['rg']                 = preg_replace('/([^0-9])/i','',$customer_data['rg']);

      if(isset($customer_data['cnpj'])){
          $customer_data['cnpj'] = preg_replace('/([^0-9])/i','',$customer_data['cnpj']);
      } else {
          $customer_data['cnpj'] = '';
      }

      $this->db->query("UPDATE `" . DB_PREFIX . "order` SET customer_id = '" . $this->db->escape($customer_data['customer_id'])
          . "', customer_group_id = '" . $this->db->escape($customer_data['customer_group_id'])
          . "', firstname = '" . $this->db->escape($customer_data['firstname'])
          . "', lastname = '" . $this->db->escape($customer_data['lastname'])
          . "', email = '" . $this->db->escape($customer_data['email'])
          . "', telephone = '" . $this->db->escape($customer_data['telephone'])
          . "', fax = '" . $this->db->escape($customer_data['fax'])
          . "', cpf = '" . $this->db->escape($customer_data['cpf'])
          . "', cnpj = '" . $this->db->escape($customer_data['cnpj'])
          . "', rg = '" . $this->db->escape($customer_data['rg'])
          . "', razao_social = '" . $this->db->escape($customer_data['razao_social'])
          . "', inscricao_estadual = '" . $this->db->escape($customer_data['inscricao_estadual'])
          . "', data_nascimento = '" . $this->db->escape($customer_data['data_nascimento'])
          . "', sexo = '" . $this->db->escape($customer_data['sexo'])
          . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");
  }

  public function setOrderAddress($order_id, $address_data, $type_address){
      $this->load->type('tipocomum');

      switch($type_address){
          case Type_Address::Tp_Faturamento:
              $type = 'payment';
              break;

          case Type_Address::Tp_Entrega:
              $type = 'shipping';
              break;

          default : $type = 'payment';
      }

        if(!isset($address_data['address_id'])){
            $address_data['address_id'] = 0;
        }

        if(!isset($address_data['first_name'])){
            $address_data['first_name'] = '';
        }

        if(!isset($address_data['last_name'])){
            $address_data['last_name'] = '';
        }

        if(!isset($address_data['apelido'])){
            $address_data['apelido'] = '';
        }

        if(!isset($address_data['address_1'])){
            $address_data['address_1'] = '';
        }

        if(!isset($address_data['address_2'])){
            $address_data['address_2'] = '';
        }

        if(!isset($address_data['postcode'])){
            $address_data['postcode'] = '00000-000';
        }

        if(!isset($address_data['logradouro'])){
            $address_data['logradouro'] = '';
        }

        if(!isset($address_data['city'])){
            $address_data['city'] = '';
        }

        if(!isset($address_data['complemento'])){
            $address_data['complemento'] = '';
        }

        if(!isset($address_data['numero'])){
            $address_data['numero'] = 0;
        }

        if(!isset($address_data['zone'])){
            $address_data['zone'] = '';
        }

        if(!isset($address_data['country'])){
            $address_data['country'] = '';
        }

        if(!isset($address_data['address_format']) || trim($address_data['address_format']) == ''){
            $address_data['address_format'] = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
        }

        $address_data['firstname']          = ucwords($address_data['firstname']);
        $address_data['lastname']           = ucwords($address_data['lastname']);

        $sql = "UPDATE `" . DB_PREFIX . "order` SET "
            . $type . "_firstname = '" . $this->db->escape($address_data['firstname']) . "', "
            . $type . "_lastname = '" . $this->db->escape($address_data['lastname']) . "', "
            . $type . "_apelido = '" . $this->db->escape($address_data['apelido']) . "', "
            . $type . "_address_1 = '" . $this->db->escape($address_data['address_1']) . "', "
            . $type . "_address_2 = '" . $this->db->escape($address_data['address_2']) . "', "
            . $type . "_postcode = '" . $this->db->escape($address_data['postcode']) . "', "
            . $type . "_logradouro = '" . $this->db->escape($address_data['logradouro']) . "', "
            . $type . "_tipo_logradouro = '" . $this->db->escape($address_data['tipo_logradouro']) . "', "
            . $type . "_city = '" . $this->db->escape($address_data['city']) . "', "
            . $type . "_codigo_municipio = '" . $this->db->escape($address_data['codigo_municipio']) . "', "
            . $type . "_numero = '" . $this->db->escape($address_data['numero']) . "', "
            . $type . "_complemento = '" . $this->db->escape($address_data['complemento']) . "', "
            . $type . "_zone = '" . $this->db->escape($address_data['zone']) . "', "
            . $type . "_zone_id = '" . $this->db->escape($address_data['zone_id']) . "', "
            . $type . "_country = '" . $this->db->escape($address_data['country']) . "', "
            . $type . "_country_id = '" . $this->db->escape($address_data['country_id']) . "', "
            . $type . "_address_format = '" . $this->db->escape($address_data['address_format']) . "', "
            . "date_modified = NOW() ";

        if(isset($address_data[$type . '_method']) && $address_data[$type . '_method']){
            $sql .= ", " . $type . "_method = '" . $address_data[$type . '_method'] . "'";
        }

        $sql .= "WHERE order_id = '" . (int)$order_id . "'";
        $this->db->query($sql);
  }

  public function setOrderConfirmed($order_id) {
    $sql = "UPDATE `" . DB_PREFIX . "order` SET `nao_confirmado` = " . $this->db->escape(Type_DsimDnao::Tp_Nao) . " WHERE order_id = '" . (int)$order_id . "'";
    $this->db->query($sql);
  }

  public function getOrder($order_id) {

    $sql = "SELECT *, o.order_id, o.shipping_code as shipping_code, o.id_futura as order_id_futura, o.mkt_tipo, o.mkt_integracao_tipo, mo.mktOrderId,mo.origem_captura,"
    ." (SELECT CONCAT(c.firstname, ' ', c.lastname) FROM " . DB_PREFIX . "customer c WHERE c.customer_id = o.customer_id) AS customer "
    ." FROM `" . DB_PREFIX . "order` o "
    ." LEFT JOIN `oc_marketplace_order` mo ON (o.order_id = mo.order_id)"
    ." WHERE o.order_id = '" . (int)$order_id . "'";

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

    if ($order_query->num_rows) {
      $reward = 0;

      $order_product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");

      foreach ($order_product_query->rows as $product) {
        $reward += $product['reward'];
      }

      $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 = '';
      }

      if ($order_query->row['affiliate_id']) {
        $affiliate_id = $order_query->row['affiliate_id'];
      } else {
        $affiliate_id = 0;
      }

      $this->load->model('sale/affiliate');

      $affiliate_info = $this->model_sale_affiliate->getAffiliate($affiliate_id);

      if ($affiliate_info) {
        $affiliate_firstname = $affiliate_info['firstname'];
        $affiliate_lastname = $affiliate_info['lastname'];
      } else {
        $affiliate_firstname = '';
        $affiliate_lastname = '';
      }

      $this->load->model('localisation/language');

      $language_info = $this->model_localisation_language->getLanguage($order_query->row['language_id']);

      if ($language_info) {
        $language_code = $language_info['code'];
        $language_filename = $language_info['filename'];
        $language_directory = $language_info['directory'];
      } else {
        $language_code = '';
        $language_filename = '';
        $language_directory = '';
      }

      $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'],
        'id_futura'                => $order_query->row['id_futura'],
        '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'],
        'customer'                => $order_query->row['customer'],
        'customer_group_id'       => $order_query->row['customer_group_id'],
        'firstname'               => $order_query->row['firstname'],
        'lastname'                => $order_query->row['lastname'],
        'telephone'               => $order_query->row['telephone'],
        'fax'                     => $order_query->row['fax'],
        'sexo'                     => $order_query->row['sexo'],
        'data_nascimento'          => $order_query->row['data_nascimento'],
        'cpf'                      => $order_query->row['cpf'],
        'rg'                       => $order_query->row['rg'],
        'cnpj'                     => $order_query->row['cnpj'],
        'razao_social'             => $order_query->row['razao_social'],
        'inscricao_estadual'       => $order_query->row['inscricao_estadual'],
        '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_company_id'      => $order_query->row['payment_company_id'],
        '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'],
        'payment_option_code'     => $order_query->row['payment_option_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_code'           => $order_query->row['shipping_code'],
        'comment'                 => $order_query->row['comment'],
        'total'                   => $order_query->row['total'],
        'reward'                  => $reward,
        'order_status_id'         => $order_query->row['order_status_id'],
        'affiliate_id'            => $order_query->row['affiliate_id'],
        'affiliate_firstname'     => $affiliate_firstname,
        'affiliate_lastname'      => $affiliate_lastname,
        'commission'              => $order_query->row['commission'],
        'language_id'             => $order_query->row['language_id'],
        'language_code'           => $language_code,
        'language_filename'       => $language_filename,
        'language_directory'      => $language_directory,
        'currency_id'             => $order_query->row['currency_id'],
        'currency_code'           => $order_query->row['currency_code'],
        'currency_value'          => $order_query->row['currency_value'],
        'ip'                      => $order_query->row['ip'],
        'forwarded_ip'            => $order_query->row['forwarded_ip'],
        'user_agent'              => $order_query->row['user_agent'],
        'accept_language'         => $order_query->row['accept_language'],
        'date_added'              => $order_query->row['date_added'],
        'date_modified'           => $order_query->row['date_modified'],
        'mktOrderId'              => $order_query->row['mktOrderId'],
        'mkt_tipo'                => $order_query->row['mkt_tipo'],
        'mkt_integracao_tipo'     => $order_query->row['mkt_integracao_tipo'],
        'codigo_integracao_pagamento'  => $order_query->row['codigo_integracao_pagamento'],
        'origem_pedido'         => $order_query->row['origem_pedido'],
        'mercadolivre_stopsync' => $order_query->row['meli_stopSync'],
        'shipping_list_cost' => $order_query->row['shipping_list_cost'],
        'meli_pack_id'        => $order_query->row['meli_pack_id'],
        'tracking_code'       => $tracking_query_codes,
        'status_futuraserver' => $order_query->row['status_futuraserver'],
        'meli_shipping_id'    => $order_query->row['meli_shipping_id'],
        'celular'             => $order_query->row['celular'],
        'order_id_futura'     => $order_query->row['order_id_futura'],
        'origem_tracking_code' => $order_query->row['origem_tracking_code'],
        'antifraude_verificado'=> $order_query->row['antifraude_verificado'],
        'nao_confirmado'=> $order_query->row['nao_confirmado'],
        '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'],
        'mkt_origem_captura' => $order_query->row['origem_captura']
      );
    } else {
      return false;
    }
  }

  public function getOrders($data = array()) {

    $this->load->helper('ftr_geral');
    $this->load->type('tipocomum');

    $sql = "SELECT o.order_id, o.customer_id, o.id_futura,"
    ."CONCAT(o.firstname, ' ', o.lastname) AS customer,"
    ."(o.shipping_method) AS shipping_method, "
    ."(SELECT os.name FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS status, "
    ."(SELECT os.order_type_status FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS type_status, "
    ."o.total, (ot.value) as subtotal, o.currency_code, o.currency_value, o.date_added, o.date_modified, o.payment_method,"
    ."o.origem_pedido, o.mkt_tipo, o.mkt_integracao_tipo, o.antifraude_verificado, o.payment_code, o.payment_address_1, o.payment_address_2, "
    ."o.payment_postcode, o.shipping_address_1, o.shipping_address_2, o.shipping_postcode FROM `" . DB_PREFIX . "order` o "
    ."LEFT JOIN `" . DB_PREFIX . "order_total` ot ON (ot.order_id = o.order_id) and (ot.`code` = 'sub_total') ";

    if (!empty($data['filter_mkt_order_id'])) {
      $sql .= "LEFT JOIN `oc_marketplace_order` mlo ON (mlo.order_id = o.order_id)";
    }

    $sql .= " WHERE o.order_status_id > '0'";

    if (!empty($data['filter_order_id'])) {
      $sql .= " AND o.order_id = '" . (int)$data['filter_order_id'] . "'";
    }

    if (!empty($data['filter_customer'])) {
      $sql .= " AND CONCAT(o.firstname, ' ', o.lastname) LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
    }

    if (!empty($data['filter_order_payment_method'])) {
      $sql .= " AND o.payment_code = '".$this->db->escape($data['filter_order_payment_method'])."' ";
    }

    if (!empty($data['filter_order_shipping_method'])) {
      $sql .= " AND o.shipping_code LIKE '%".$this->db->escape($data['filter_order_shipping_method'])."%' ";
    }

    if (!empty($data['filter_date_added'])) {
      $sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape(ftr_geral::formatDate($data['filter_date_added'], 'en')) . "')";
    }

    if (!empty($data['filter_date_modified'])) {
      $sql .= " AND DATE(o.date_modified) = DATE('" . $this->db->escape(ftr_geral::formatDate($data['filter_date_modified'], 'en')) . "')";
    }

    if (!empty($data['filter_total'])) {
      $sql .= ftr_geral::getContentSqlValue(" AND CAST(o.total AS decimal(15,2)) ", $data['filter_total'], $data['filter_content_total']);
    }

    if (!empty($data['filter_subtotal'])) {
      $sql .= ftr_geral::getContentSqlValue(" AND CAST(ot.value AS decimal(15,2)) ", $data['filter_subtotal'], $data['filter_content_subtotal']);
    }

    if (!empty($data['filter_mkt_order_id'])) {
      $sql .= " AND mlo.mktOrderId like '%" . $data['filter_mkt_order_id'] . "%' OR o.meli_pack_id like '%".$data['filter_mkt_order_id']."%' ";
    }

    if (isset($data['filter_origem_pedido']) && !is_null($data['filter_origem_pedido'])) {
      $sql .= " AND o.origem_pedido = '" . (int)$data['filter_origem_pedido'] . "'";
    }

    if (isset($data['filter_integracao']) && !is_null($data['filter_integracao'])) {
      $sql .= " AND o.mkt_integracao_tipo = '" . (int)$data['filter_integracao'] . "'";
    }

    if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace'])) {
      $sql .= " AND o.mkt_tipo = '" . (int)$data['filter_marketplace'] . "'";
    }

    if (!empty($data['filter_lp']) && !is_null($data['filter_lp']) && $data['filter_lp']== Type_DsimDnao::Tp_Sim) {
      $sql .= " AND o.order_id in (select id_pedido from `futura_pedidos_itens_listas`)";
    }

    if (isset($data['filter_lp']) && !is_null($data['filter_lp']) && $data['filter_lp'] == Type_DsimDnao::Tp_Nao) {
      $sql .= " AND o.order_id not in (select id_pedido from `futura_pedidos_itens_listas`)";
    }

    if (isset($data['filter_order_process_status']) && $data['filter_order_process_status'] !== null) {
      $sql .= " AND o.proccess_atual = ".(int)$data['filter_order_process_status'];
    }

    $sort_data = array(
      'o.order_id',
      'customer',
      'status',
      'o.date_added',
      'o.date_modified',
      'o.total',
      'subtotal'
    );

    if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
      $sql .= " ORDER BY " . $data['sort'];
    } else {
      $sql .= " ORDER BY o.order_id";
    }

    if (isset($data['order']) && ($data['order'] == 'DESC')) {
      $sql .= " DESC";
    } else {
      $sql .= " ASC";
    }

    if (isset($data['start']) || isset($data['limit'])) {
      if ($data['start'] < 0) {
        $data['start'] = 0;
      }

      if ($data['limit'] < 1) {
        $data['limit'] = 20;
      }

      $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
    }

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

    return $query->rows;
  }

  public function getOrderOption($order_id, $order_option_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_option_id = '" . (int)$order_option_id . "'");

    return $query->row;
  }

  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 getOrderShippingReference($order_id) {

      $sql =    " SELECT quote_reference_code
                    FROM " . DB_PREFIX . "order_shipping
                    WHERE ORDER_ID = '" . (int)$order_id . "'
                      AND quote_reference_code IS NOT NULL
                GROUP BY quote_reference_code";

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

      return $query->rows;
  }

  public function getOrderVoucherByVoucherId($voucher_id) {
    $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_voucher` WHERE voucher_id = '" . (int)$voucher_id . "'");

    return $query->row;
  }
  /*TODO: remover daqui, migrado para dmodel */
  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 getTotalOrders($data = array()) {

    $this->load->helper('ftr_geral');
    $this->load->type('tipocomum');

    $sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` o LEFT JOIN `". DB_PREFIX ."order_total` ot ON (ot.order_id = o.order_id) and (ot.`code` = 'sub_total')";

    if (!empty($data['filter_mkt_order_id'])) {
      $sql .= "LEFT JOIN `oc_marketplace_order` mlo ON (mlo.order_id = o.order_id)";
    }

    if (isset($data['filter_order_status_id']) && !is_null($data['filter_order_status_id'])) {
      $sql .= " WHERE o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
    } else {
      $sql .= " WHERE o.order_status_id > '0'";
    }

    if (!empty($data['filter_order_payment_method'])) {
      $sql .= " AND o.payment_code = '".$this->db->escape($data['filter_order_payment_method'])."' ";
    }

    if (!empty($data['filter_order_shipping_method'])) {
      $sql .= " AND o.shipping_code LIKE '%".$this->db->escape($data['filter_order_shipping_method'])."%' ";
    }

    if (!empty($data['filter_order_id'])) {
      $sql .= " AND o.order_id = '" . (int)$data['filter_order_id'] . "'";
    }

    if (!empty($data['filter_customer'])) {
      $sql .= " AND CONCAT(o.firstname, ' ', o.lastname) LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
    }

    if (!empty($data['filter_date_added'])) {
      $sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
    }

    if (!empty($data['filter_date_modified'])) {
      $sql .= " AND DATE(o.date_modified) = DATE('" . $this->db->escape($data['filter_date_modified']) . "')";
    }

    if (!empty($data['filter_mkt_order_id'])) {
      $sql .= " AND mlo.mktOrderId like '%" . $data['filter_mkt_order_id'] . "%' OR o.meli_pack_id like '%".$data['filter_mkt_order_id']."%' ";
    }

    if (isset($data['filter_origem_pedido']) && !is_null($data['filter_origem_pedido'])) {
      $sql .= " AND o.origem_pedido = '" . (int)$data['filter_origem_pedido'] . "'";
    }

    if (isset($data['filter_integracao']) && !is_null($data['filter_integracao'])) {
      $sql .= " AND o.mkt_integracao_tipo = '" . (int)$data['filter_integracao'] . "'";
    }

    if (isset($data['filter_marketplace']) && !is_null($data['filter_marketplace'])) {
      $sql .= " AND o.mkt_tipo = '" . (int)$data['filter_marketplace'] . "'";
    }

    if (!empty($data['filter_lp']) && !is_null($data['filter_lp']) && $data['filter_lp']== Type_DsimDnao::Tp_Sim ) {
      $sql .= " AND o.order_id in (select id_pedido from `futura_pedidos_itens_listas`)";
    }

    if (isset($data['filter_lp']) && !is_null($data['filter_lp']) && $data['filter_lp'] == Type_DsimDnao::Tp_Nao) {
      $sql .= " AND o.order_id not in (select id_pedido from `futura_pedidos_itens_listas`)";
    }

    if (!empty($data['filter_total'])) {
      $sql .= ftr_geral::getContentSqlValue(" AND o.total ", $data['filter_total'], $data['filter_content_total']);
    }

    if (!empty($data['filter_subtotal'])) {
      $sql .= ftr_geral::getContentSqlValue(" AND ot.value ", $data['filter_subtotal'], $data['filter_content_subtotal']);
    }

    if (!empty($data['filter_date_added_between'])) {
        $sql .= " AND DATE(o.date_added) BETWEEN MAKEDATE(YEAR(current_timestamp), 1) AND DATE(NOW()) ";
    }

    if (isset($data['filter_order_process_status'])) {
      $sql .= " AND o.proccess_atual = ".(int)$data['filter_order_process_status'];
    }

    if (!empty($data['filter_status_futuraserver'])) {
      $sql .= " AND o.status_futuraserver IN (".Type_DashboardOrderStatus::TpPSt_Baixado.",".Type_DashboardOrderStatus::TpPSt_Faturado.") ";
    }

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

    return $query->row['total'];
  }

  public function getTotalOrdersByStoreId($store_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE store_id = '" . (int)$store_id . "'");

    return $query->row['total'];
  }

  public function getTotalOrdersByOrderStatusId($order_status_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$order_status_id . "' AND order_status_id > '0'");

    return $query->row['total'];
  }

  public function getTotalOrdersByLanguageId($language_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE language_id = '" . (int)$language_id . "' AND order_status_id > '0'");

    return $query->row['total'];
  }

  public function getTotalOrdersByCurrencyId($currency_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE currency_id = '" . (int)$currency_id . "' AND order_status_id > '0'");

    return $query->row['total'];
  }

  public function getTotalSales() {

    $sql = "SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE status_futuraserver IN (".Type_DashboardOrderStatus::TpPSt_Baixado.",".Type_DashboardOrderStatus::TpPSt_Faturado.")";

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

    return $query->row['total'];
  }

  public function getAverageSales() {
    $query = $this->db->query("SELECT AVG(total) AS ticket_medio FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" .(int)$this->config->get('config_complete_status_id')."'");

    return $query->row['ticket_medio'];
  }

  public function getAverageSalesLast12Months() {
      $query = $this->db->query("SELECT AVG(total) AS ticket_medio FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" .(int)$this->config->get('config_complete_status_id')."'
      AND date(date_added) between date_add(current_date, interval -1 year) and date(current_date)");

      return $query->row['ticket_medio'];
  }

  public function getTotalSalesByYear($year) {
    $query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE status_futuraserver IN (".Type_DashboardOrderStatus::TpPSt_Baixado.",".Type_DashboardOrderStatus::TpPSt_Faturado.") AND YEAR(date_added) = '" . (int)$year . "'");

    return $query->row['total'];
  }

  public function getOrderHistories($order_id, $start = 0, $limit = 10) {
      $sql_limit = '';

      if($start !== false){
          if ($start < 0) {
              $start = 0;
          }

          $sql_limit = 'LIMIT ' . (int)$start . ', ';
      }

      if($limit !== false){
          if ($limit < 1) {
              $limit = 10;
          }

          $sql_limit .= (int)$limit;
      }

      $sql = "SELECT
        oh.order_history_id,
        oh.date_added,
        os.name AS status,
        oh.comment,
        oh.notify_whatsapp,
        oh.message_whatsapp,
        oh.notify,
        oh.manual,
        os.order_type_status as type_status,
        os.order_status_id as order_status_id,
        ms.marketplace_order_status_id,
        ms.mktStatus,
        ms.tipo_origem,
        ms.tipo_situacao,
        ms.msg_erro
        FROM " . DB_PREFIX . "order_history oh
        LEFT JOIN " . DB_PREFIX . "order_status os ON oh.order_status_id = os.order_status_id
        LEFT JOIN oc_marketplace_order_status ms ON oh.order_history_id = ms.order_history_id
        WHERE oh.order_id = '" . (int)$order_id . "' AND os.language_id = '" . (int)$this->config->get('config_language_id') . "'
        ORDER BY oh.date_added DESC";

      if($sql_limit){
          $sql .= " " . $sql_limit;
      }

      $query = $this->db->query($sql);
      return $query->rows;
  }

  public function getTotalOrderHistories($order_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_history WHERE order_id = '" . (int)$order_id . "'");

    return $query->row['total'];
  }

  public function getTotalOrderHistoriesByOrderStatusId($order_status_id) {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_history WHERE order_status_id = '" . (int)$order_status_id . "'");

    return $query->row['total'];
  }

  // Schedule Module for Futura
  public function getScheduleFromFuturaParameters() {
    $sql = "SELECT pf.is_futura FROM parametros_efutura pf;";
    $result = $this->db->query($sql);
 
    return $result->row['is_futura'] > 0 ?? false;
  }

  public function getScheduleByOrderID($order_id) {
    $sql = "SELECT ost.date, ost.time, os.type, os.status, os.name 
            FROM " . DB_PREFIX . "order od 
            INNER JOIN " . DB_PREFIX . "schedule_times ost ON od.order_id = ost.order_id
            INNER JOIN " . DB_PREFIX . "schedule os ON os.schedule_id = ost.schedule_id
            WHERE od.order_id = " . (int)$order_id;
    $results = $this->db->query($sql);

    return $results->rows;
  }

  public function linkTicketCertificate($data) {
    $sql = "INSERT INTO ". DB_PREFIX . "ticket_certificate SET order_id = '" . (int)$data['order_id'] . "', 
    ticket_number = '" . $data['ticket_number'] . "', 
    ticket_type = '" . $data['ticket_type'] . "' ";
        
    try{
      $results = $this->db->query($sql);
    } catch(Exception $e){
      $results = 2601;
    }

    return $results;
  }

  public function unlinkTicketCertificate($data) {
    $sql = "DELETE FROM ". DB_PREFIX . "ticket_certificate WHERE ticket_number = '" . $data['ticket_number'] . "' ";
   
    $results = $this->db->query($sql);

    return $results;
  }

  public function hasLinkTicketCertificate($order_id) {
    $sql = "SELECT * FROM ". DB_PREFIX . "ticket_certificate WHERE order_id = '" . $order_id . "' "; 
    
    $results = $this->db->query($sql);

    return $results->rows;
  }

  public function getEmailsByProductsOrdered($products, $start, $end) {
    $implode = array();

    foreach ($products as $product_id) {
      $implode[] = "op.product_id = '" . $product_id . "'";
    }

    $limit = "";

    if(is_numeric($start) && is_numeric($end)){
      if ($start < 0) {
          $start = 0;
      }

      if ($end < 1) {
          $end = 20;
      }

      $limit = " LIMIT " . (int)$start . "," . (int)$end;
    }

    $query = $this->db->query("SELECT DISTINCT firstname, lastname, email FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (o.order_id = op.order_id) WHERE (" . implode(" OR ", $implode) . ") AND o.order_status_id <> '0' " . $limit);


    return $query->rows;
  }

  public function getTotalEmailsByProductsOrdered($products) {
    $implode = array();

    foreach ($products as $product_id) {
      $implode[] = "op.product_id = '" . $product_id . "'";
    }

    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (o.order_id = op.order_id) WHERE (" . implode(" OR ", $implode) . ") AND o.order_status_id <> '0'");

    return $query->row['total'];
  }

  public function getTotalItensByOrder($order_id){
    $query = $this->db->query("SELECT SUM(quantity) as qtditens FROM oc_order_product
                               WHERE order_id = " . $order_id);

    return $query->row['qtditens'];
  }

  public function getNewsletterByEmail(){

    $query = $this->db->query("SELECT DISTINCT email FROM ". DB_PREFIX ."newsletter");

    return $query->rows;

  }

  public function getNewsletter(){

      $query = $this->db->query("SELECT * FROM ". DB_PREFIX ."newsletter");

      return $query->rows;

  }

  public function getProductErrorMktPlace($orderid){
    $sql = "select case when op.product_id > 0 then true else false end as verifica from ".DB_PREFIX."order_product op where op.order_id = ".$orderid." and op.marketId is not null";
    $query = $this->db->query($sql);

    if($query->rows){
      $verifica = array();
      foreach ($query->rows as $key => $value) {
          $verifica[$key] = $value['verifica'];
      }
      return (count(array_filter($verifica)) == count($verifica)) ? true : false;
    }else{
      return true;
    }
  }

  public function getErrosReplicacao($orderid){
    $sql = "select replicacao_erro_message, replicacao_status_tipo from ".DB_PREFIX."order where order_id = ".$orderid." and replicacao_status_tipo=1 limit 1";
    $query = $this->db->query($sql);

    if($query->num_rows && $query->row['replicacao_status_tipo']==Type_ReplicacaoStatus::Tp_Erro){
      return array(
        "status_tipo" => $query->row['replicacao_status_tipo'],
        "message" => $query->row['replicacao_erro_message']
      );
    }else{
      return false;
    }
  }

  public function setOrderToRepli($orderid,$customer_id,$tipoOeracao='I'){
    $query = $this->db->query("SELECT id FROM change_efutura ORDER BY id LIMIT 1");

    if(!$query->num_rows || $query->row['id'] >= 0){
      $change_id = -1;
    }else{
      $change_id = --$query->row['id'];
    }


    $order_status = $this->db->query("SELECT * FROM oc_order_to_situation WHERE order_id = $orderid order by order_to_situation_id");

    if($order_status->num_rows){
      foreach($order_status->rows as $row){
        $change_id = --$change_id;
        $sql = "INSERT INTO `change_efutura` (`ID`, `TABLE_NAME`, `PK_FIELD`, `PK_VALUE`, `TYPE_OPERATION`, `DATE_TIME`, `ID_FUTURA`)
        VALUES ($change_id, 'oc_order_to_situation', 'order_to_situation_id',".$row['order_to_situation_id'].", '$tipoOeracao', NOW() , NULL)";
        $this->db->query($sql);
      }
    }

    $change_id = --$change_id;
    $this->db->query("INSERT INTO `change_efutura` (`ID`, `TABLE_NAME`, `PK_FIELD`, `PK_VALUE`, `TYPE_OPERATION`, `DATE_TIME`, `ID_FUTURA`)
            VALUES ($change_id, 'oc_order', 'order_id', $orderid, '$tipoOeracao', NOW() , NULL)");

    if ($customer_id){
        $change_id = --$change_id;
        $this->db->query("INSERT INTO `change_efutura` (`ID`, `TABLE_NAME`, `PK_FIELD`, `PK_VALUE`, `TYPE_OPERATION`, `DATE_TIME`, `ID_FUTURA`)
        VALUES ($change_id, 'oc_customer', 'customer_id', $customer_id, 'I', NOW() , NULL)");
    }

  }

  public function getProductOrderML($orderid){
    $sql = "select case when op.product_id > 0 then op.product_id else false end as verifica from ".DB_PREFIX."order_product op where op.order_id = ".$orderid."";
    $query = $this->db->query($sql);
    $retorno = array();
    if($query->rows){
      foreach ($query->rows as $key => $verifica) {
        foreach ($verifica as $key => $value) {
          if($value){
            $retorno[] = $value;
          }
        }
      }
    }
    return $retorno;
  }

  public function setProductOrderML($order_id, $product_id, $mercaid){

    $sql = "UPDATE ".DB_PREFIX."order_product SET product_id=".$product_id." WHERE order_id=".$order_id." AND marketId = '".$mercaid."'";
    $query = $this->db->query($sql);

  }

  public function hasOrderOnChangeEfutura($order_id){
      $query = $this->db->query("SELECT id FROM change_efutura WHERE PK_VALUE = {$order_id} AND PK_FIELD = 'order_id' AND TABLE_NAME = 'oc_order' ORDER BY id LIMIT 1");

      if($query->rows){
          return $query->rows;
      } else {
          return false;
      }
  }

  public function getOrderComissionTotal($order_id){

    $sql = "SELECT sum(mkt_comission) as mkt_comission from ".DB_PREFIX."order_product where order_id = ".$order_id;
    $query = $this->db->query($sql);
    if($query->row['mkt_comission'] > 0){
      return $query->row['mkt_comission'];
    }else{
      return false;
    }

  }

  public function getOrderTotalDateByHour($hour){

    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "' AND (DATE(date_added) = DATE(NOW()) AND HOUR(date_added) = '" . (int)$hour . "') GROUP BY HOUR(date_added) ORDER BY date_added ASC");
    return ($query->num_rows) ? (int) $query->row['total'] : 0 ;

  }

  public function getOrderTotalDateByWeek($date){

    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "' AND DATE(date_added) = '" . $this->db->escape($date) . "' GROUP BY DATE(date_added)");
    return ($query->num_rows) ? (int) $query->row['total'] : 0 ;

  }

  public function getOrderTotalDateByMonth($date){

    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "' AND (DATE(date_added) = '" . $this->db->escape($date) . "') GROUP BY DAY(date_added)");
    return ($query->num_rows) ? (int) $query->row['total'] : 0 ;

  }

  public function getOrderTotalDateByYear($year){

    $query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "' AND YEAR(date_added) = '" . date('Y') . "' AND MONTH(date_added) = '" . $year . "' GROUP BY MONTH(date_added)");
    return ($query->num_rows) ? (int) $query->row['total'] : 0 ;

  }

  public function getOrderTotalValueDateByHour($hour){
      $query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (DATE(date_added) = DATE(NOW()) AND HOUR(date_added) = '" . (int)$hour . "') GROUP BY HOUR(date_added) ORDER BY date_added ASC");
      return ($query->num_rows) ? (int) $query->row['total'] : 0 ;
  }

  public function getOrderTotalValueDateByWeek($date){
      $query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND DATE(date_added) = '" . $this->db->escape($date) . "' GROUP BY DATE(date_added)");
      return ($query->num_rows) ? (int) $query->row['total'] : 0 ;
  }

  public function getOrderTotalValueDateByYear($year){
      $query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND YEAR(date_added) = '" . date('Y') . "' AND MONTH(date_added) = '" . $year . "' GROUP BY MONTH(date_added)");
      return ($query->num_rows) ? (int) $query->row['total'] : 0 ;
  }

  public function getOrderTotalValueDateByMonth($date){
      $query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (DATE(date_added) = '" . $this->db->escape($date) . "') GROUP BY DAY(date_added)");
      return ($query->num_rows) ? (int) $query->row['total'] : 0 ;
  }

  public function getOrderValueDeviceByHour($hour){
    $sql = "SELECT o.tipo_dispositivo as device, SUM(o.total) as total_device from " . DB_PREFIX . "order o WHERE NOT o.tipo_dispositivo IS NULL AND o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (DATE(o.date_added) = DATE(NOW()) AND HOUR(o.date_added) = '" . (int)$hour . "') GROUP BY o.tipo_dispositivo, HOUR(o.date_added) ORDER BY o.date_added ASC";
    $query = $this->db->query($sql);

    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueDeviceByWeek($date){
    $sql = "SELECT o.tipo_dispositivo as device, SUM(o.total) as total_device from " . DB_PREFIX . "order o WHERE NOT o.tipo_dispositivo IS NULL AND o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (DATE(o.date_added) ='".$this->db->escape($date)."') GROUP BY o.tipo_dispositivo, DATE(o.date_added)";
    $query = $this->db->query($sql);

    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueDeviceByMonth($date){
    $sql = "SELECT o.tipo_dispositivo as device, SUM(o.total) as total_device from " . DB_PREFIX . "order o WHERE NOT o.tipo_dispositivo IS NULL AND o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (DATE(o.date_added) ='".$this->db->escape($date)."') GROUP BY o.tipo_dispositivo, DAY(o.date_added)";
    $query = $this->db->query($sql);

    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueDeviceByYear($year){
    $sql = "SELECT o.tipo_dispositivo as device, SUM(o.total) as total_device from " . DB_PREFIX . "order o WHERE NOT o.tipo_dispositivo IS NULL AND o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND YEAR(o.date_added) = '".date('Y')."' AND MONTH(o.date_added) = '".$year."' GROUP BY o.tipo_dispositivo, MONTH(o.date_added)";
    $query = $this->db->query($sql);

    return ($query->num_rows) ? $query->rows : false;
  }

  // MARKETPLACE
  public function getOrderValueMarketplaceByDay($date){
    $sql = "SELECT HOUR (o.date_added) as hora, o.mkt_tipo as marketplace, SUM(o.TOTAL) AS total_marketplace from " . DB_PREFIX . "order o WHERE NOT o.mkt_tipo = 0 AND o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND DATE(o.date_added) = DATE('".$date."') GROUP BY HOUR(o.date_added), o.mkt_tipo ORDER BY HOUR(o.date_added), o.mkt_tipo";
    $query = $this->db->query($sql);
    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueMarketplaceByWeek($date){
    $sql = "SELECT DAYOFWEEK (o.date_added)-1 as dia, o.mkt_tipo as marketplace, SUM(o.TOTAL) AS total_marketplace from " . DB_PREFIX . "order o WHERE NOT o.mkt_tipo = 0 AND o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (WEEK(o.date_added) = WEEK('".$date."')) AND (YEAR(o.date_added) = YEAR('".$date."')) GROUP BY DAYOFWEEK(o.date_added), o.mkt_tipo";
    $query = $this->db->query($sql);
    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueMarketplaceByMonth($date){
    $sql = "SELECT DAY(o.date_added) as dia, o.mkt_tipo as marketplace, SUM(o.TOTAL) AS total_marketplace from " . DB_PREFIX . "order o WHERE NOT o.mkt_tipo = 0 AND o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (MONTH(o.date_added) = MONTH('".$date."')) AND (YEAR(o.date_added) = YEAR('".$date."')) GROUP BY DAY(o.date_added), o.mkt_tipo";
    $query = $this->db->query($sql);
    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueMarketplaceByYear($year){
    $sql = "SELECT MONTH(o.date_added) as mes, o.mkt_tipo as marketplace, SUM(o.TOTAL) AS total_marketplace from " . DB_PREFIX . "order o WHERE NOT o.mkt_tipo = 0 AND o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND YEAR(o.date_added) = '".$year."' GROUP BY MONTH(o.date_added), o.mkt_tipo";
    $query = $this->db->query($sql);
    return ($query->num_rows) ? $query->rows : false;
  }

  // SALES CHANNEL
  public function getOrderValueSalesChannelByDay($date){
    $sql = "SELECT HOUR (o.date_added) as hora, o.origem_pedido as origem, SUM(o.TOTAL) AS total_origem from " . DB_PREFIX . "order o WHERE o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (o.date_added = '".$date."') GROUP BY HOUR(o.date_added), o.origem_pedido ORDER BY HOUR(o.date_added), o.origem_pedido";
    $query = $this->db->query($sql);
    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueSalesChannelByWeek($date){
    $sql = "SELECT DAYOFWEEK (o.date_added)-1 as dia, o.origem_pedido as origem, SUM(o.TOTAL) AS total_origem from " . DB_PREFIX . "order o WHERE o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (WEEK(o.date_added) = WEEK('".$date."')) AND (YEAR(o.date_added) = YEAR('".$date."')) GROUP BY DAYOFWEEK(o.date_added), o.origem_pedido";
    $query = $this->db->query($sql);
    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueSalesChannelByMonth($date){
    $sql = "SELECT DAY(o.date_added) as dia, o.origem_pedido as origem, SUM(o.TOTAL) AS total_origem from " . DB_PREFIX . "order o WHERE o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND (MONTH(o.date_added) = MONTH('".$date."')) AND (YEAR(o.date_added) = YEAR('".$date."')) GROUP BY DAY(o.date_added), o.origem_pedido";
    $query = $this->db->query($sql);
    return ($query->num_rows) ? $query->rows : false;
  }

  public function getOrderValueSalesChannelByYear($year){
    $sql = "SELECT MONTH(o.date_added) as mes, o.origem_pedido as origem, SUM(o.TOTAL) AS total_origem from " . DB_PREFIX . "order o WHERE o.status_futuraserver IN (".Type_PedidoStatus::TpPSt_Baixado.",".Type_PedidoStatus::TpPSt_Faturado.") AND YEAR(o.date_added) = '".$year."' GROUP BY MONTH(o.date_added), o.origem_pedido";
    $query = $this->db->query($sql);
    return ($query->num_rows) ? $query->rows : false;
  }

  public function getValueByCustomRange($start_date) {
    $query = $this->db->query("
        SELECT SUM(total) AS total
        FROM `" . DB_PREFIX . "order`
        WHERE status_futuraserver IN (" . Type_PedidoStatus::TpPSt_Baixado . "," . Type_PedidoStatus::TpPSt_Faturado . ") 
        AND DATE(date_added) = '" . $this->db->escape($start_date) . "'
    ");
    return $query->row['total'];
  }
  
  public function getOrderCodePagamento($order_id) {
    $row = $this->db->query("SELECT payment_code FROM " . DB_PREFIX . "order WHERE order_id = " . (int) $order_id)->row;
    return $row['payment_code'];
  }

  public function getOrderApp() {
    $row = $this->db->query("SELECT count(*) as total FROM " . DB_PREFIX . "order WHERE origem_pedido = " . (int)Type_OrigemSistema::Tp_App)->row;
    return $row['total'] > 0;
  }

  public function getOrderAntiFraude($order_id) {
    $query = $this->db->query("SELECT * FROM oc_antifraud_details WHERE order_id = " . (int)$order_id." order by 1 desc limit 1");
    return ($query->num_rows) ? $query->row : false;
  }

}
