<?php

final class DModelcheckoutorder extends Model
{

    public function getOrderItems($order_id)
    {
        $sql = "select
              op.*
            from oc_order_product op
            where op.order_id = $order_id
           ";
        $order_item_query = $this->db->query($sql);

        return $order_item_query->rows;
    }

    public function getOrder($order_id)
    {

        $sql = "SELECT *, (o.order_id) as order_id, o.shipping_code as order_shipping_code, o.shipping_method as order_shipping_method,
      DATE_FORMAT(o.data_nascimento,'%d/%m/%Y') AS data_nascimento_br,"
            . "(SELECT os.name FROM `" . DB_PREFIX . "order_status` os WHERE os.order_status_id = o.order_status_id AND os.language_id = o.language_id) AS order_status, "
            . "(SELECT c.celular FROM `" . DB_PREFIX . "customer` c WHERE c.customer_id = o.customer_id) AS celular, "
            . "(SELECT c.mkt_buyer_id FROM `" . DB_PREFIX . "customer` c WHERE c.customer_id = o.customer_id) AS mkt_buyer_id "
            . "FROM `" . DB_PREFIX . "order` o "
            . " left join oc_order_nfe onfe on (onfe.order_id = o.order_id)"
            . " left join oc_order_tracking ot on (ot.order_id = o.order_id)"
            . " 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) {
            $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 = '';
            }

            $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'],
                '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'],
                'tracking_code' => $tracking_query_codes,
                'fax' => $order_query->row['fax'],
                'data_nascimento' => $order_query->row['data_nascimento_br'],
                'sexo' => $order_query->row['sexo'],
                'cpf' => $order_query->row['cpf'],
                'rg' => $order_query->row['rg'],
                'celular' => $order_query->row['celular'],
                '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'],
                '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['order_shipping_method'],
                'shipping_code' => $order_query->row['order_shipping_code'],
                'comment' => $order_query->row['comment'],
                'total' => $order_query->row['total'],
                'order_status_id' => $order_query->row['order_status_id'],
                'order_status' => $order_query->row['order_status'],
                '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_modified' => $order_query->row['date_modified'],
                'date_added' => $order_query->row['date_added'],
                'origem_pedido' => $order_query->row['origem_pedido'],
                'mktOrderId' => $order_query->row['mktOrderId'],
                'mercadolivre_stopsync' => $order_query->row['meli_stopSync'],
                'meli_shipping_id' => $order_query->row['meli_shipping_id'],
                'meli_pack_id' => $order_query->row['meli_pack_id'],
                'mkt_buyer_id' => $order_query->row['mkt_buyer_id'],
                'md5_total' => md5($order_query->row['total']),
                'chave_acesso' => $order_query->row['chave_acesso'],
                'numero_nfe' => $order_query->row['numero_nfe'],
                'serie' => $order_query->row['serie'],
                'cfop' => $order_query->row['cfop'],
                'quantidade_volumes' => $order_query->row['quantidade_volumes'],
                'data_hora_emissao' => $order_query->row['data_hora_emissao'],
                'xml' => $order_query->row['xml'],
                'md5_order_id' => md5($order_query->row['order_id']),
                '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'],
            );
        } else {
            return false;
        }
    }

    public function getOrderStatus($order_status_id)
    {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_status WHERE order_status_id = '" . (int)$order_status_id . "'");
        return array(
            'descricao' => $query->row['name'],
            'tipo_status' => $query->row['order_type_status'],
            'order_status_id' => $query->row['order_status_id']
        );
    }

    public function notaFiscalEmitidaHistory($order_id)
    {
        $order_nfe = $this->config->get('select_order_status_nota_fiscal_emitida_id');
        if (!empty($order_nfe)) {
            $oc_order_info = $this->db->query("SELECT order_status_id FROM `" . DB_PREFIX . "order_history` WHERE order_id = '" . (int)$order_id . "' and order_status_id = '" . $order_nfe . "'")->row;
            return $oc_order_info['order_status_id'] != null;
        } else {
            //se n tiver configuração de nota fiscal retorna true...
            return false;
        }
    }

    public function update($order_id, $order_status_id, $comment = '', $skipOnDuplicate = false)
    {
        $order_info = $this->getOrder($order_id);

        if (($skipOnDuplicate) && ($order_status_id == $order_info['order_status_id'])) {
            return;
        }

        $client_notify = $this->ftrgeralregistry->getClienteNotify($order_status_id);

        if ($order_info && $order_info['order_status_id']) {
            // Ban IP
            $status = false;

            $this->load->dmodel('account/customer');

            if ($order_info['customer_id']) {

                $results = $this->dmodel_account_customer->getIps($order_info['customer_id']);

                foreach ($results as $result) {
                    if ($this->dmodel_account_customer->isBanIp($result['ip'])) {
                        $status = true;

                        break;
                    }
                }
            } else {
                $status = $this->dmodel_account_customer->isBanIp($order_info['ip']);
            }

            if ($status) {
                $order_status_id = $this->config->get('config_order_status_id');
            }

            $this->load->dmodel('proccess/order_to_situation');
            $this->dmodel_proccess_order_to_situation->updateHistory($order_id, $order_status_id);

            $this->db->query("UPDATE `" . DB_PREFIX . "order` SET order_status_id = '" . (int)$order_status_id . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");

            $this->db->query("INSERT INTO " . DB_PREFIX . "order_history SET order_id = '" . (int)$order_id . "', order_status_id = '" . (int)$order_status_id . "', notify = '" . (int)$client_notify . "', comment = '" . $this->db->escape($comment) . "', date_added = current_timestamp(2)");
        }
    }

    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 addOrder($data)
    {

        $this->load->helper('Ftr_sql');
        $this->load->helper('ftr_geral');

        //pega as informações do navegador a cada compra.
        $browser = new BrowserInfo();
        $data = array_merge($data, $browser->getDadosBrowser());

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

        $modulostconf = $this->futuracalcularst->getconfiguracoes();
        $calcular_st = (($this->customer->isLogged()) && ($this->customer->getTipo_pessoa() == 'J') && ($modulostconf) && ($modulostconf['futuracalcularst_status']) );

        $arrayOrder = array();

        $arrayFieldOrder = [
            'store_id', 'store_name', 'store_url', 'customer_id', 'customer_group_id', 'firstname',
            'lastname', 'email', 'telephone', 'fax', 'cpf', 'cnpj', 'rg', 'razao_social', 'inscricao_estadual', 'data_nascimento', 'sexo',
            'payment_apelido', 'payment_firstname', 'payment_lastname', 'payment_company', 'payment_company_id',
            'payment_address_1', 'payment_numero', 'payment_address_2', 'payment_complemento', 'payment_city', 'payment_postcode',
            'payment_country', 'payment_country_id', 'payment_zone', 'payment_zone_id', 'payment_address_format', 'payment_method',
            'payment_code', 'payment_logradouro', 'payment_tipo_logradouro', 'payment_codigo_municipio', 'shipping_apelido', 'shipping_firstname',
            'shipping_lastname', 'shipping_company', 'shipping_address_1', 'shipping_numero', 'shipping_address_2', 'shipping_complemento',
            'shipping_city', 'shipping_postcode', 'shipping_country', 'shipping_country_id', 'shipping_zone', 'shipping_zone_id',
            'shipping_address_format', 'shipping_method', 'shipping_code', 'shipping_logradouro', 'shipping_tipo_logradouro', 'shipping_codigo_municipio',
            'comment', 'total', 'affiliate_id', 'commission', 'language_id', 'currency_id', 'currency_code', 'currency_value', 'ip', 'forwarded_ip',
            'user_agent', 'accept_language', 'date_added', 'date_modified', 'origem_pedido', 'sistema_operacional', 'versao_sistema_operacional',
            'navegador', 'versao_navegador', 'celular', 'tipo_dispositivo', 'payment_option_code', 'shipping_simalation_days', 'total_pontos', 'fidelity',
            'total_weight', 'total_net_weight', 'pickup_address', 'pickup_number', 'pickup_neighborhood', 'pickup_complement', 'pickup_city',
            'pickup_postcode', 'pickup_zone_id', 'pickup_zone', 'feedback_order', 'feedback_order_whatsapp','shipping_list_cost'
        ];

        $dataAtual = $this->db->dataAtual();

        $arrayOrder[] = (int)$data['store_id'];
        $arrayOrder[] = $this->db->escape($data['store_name']);
        $arrayOrder[] = $this->db->escape($data['store_url']);
        $arrayOrder[] = (int)$data['customer_id'];
        $arrayOrder[] = (int)$data['customer_group_id'];
        $arrayOrder[] = $this->db->escape($data['firstname']);
        $arrayOrder[] = $this->db->escape($data['lastname']);
        $arrayOrder[] = $this->db->escape($data['email']);
        $arrayOrder[] = $this->db->escape($data['telephone']);
        $arrayOrder[] = $this->db->escape($data['fax']);
        $arrayOrder[] = $this->db->escape($data['cpf']);
        $arrayOrder[] = $this->db->escape($data['cnpj']);
        $arrayOrder[] = $this->db->escape($data['rg']);
        $arrayOrder[] = $this->db->escape($data['razao_social']);
        $arrayOrder[] = $this->db->escape($data['inscricao_estadual']);
        $arrayOrder[] = $this->db->escape($data['data_nascimento']);
        $arrayOrder[] = $this->db->escape($data['sexo']);
        $arrayOrder[] = $this->db->escape($data['payment_apelido']);
        $arrayOrder[] = $this->db->escape($data['payment_firstname']);
        $arrayOrder[] = $this->db->escape($data['payment_lastname']);
        $arrayOrder[] = $this->db->escape($data['payment_company']);
        $arrayOrder[] = $this->db->escape($data['payment_company_id']);
        $arrayOrder[] = $this->db->escape($data['payment_address_1']);
        $arrayOrder[] = $this->db->escape($data['payment_numero']);
        $arrayOrder[] = $this->db->escape($data['payment_address_2']);
        $arrayOrder[] = $this->db->escape($data['payment_complemento']);
        $arrayOrder[] = stripslashes($this->db->escape($data['payment_city']));
        $arrayOrder[] = $this->db->escape($data['payment_postcode']);
        $arrayOrder[] = $this->db->escape($data['payment_country']);
        $arrayOrder[] = $this->db->escape($data['payment_country_id']);
        $arrayOrder[] = $this->db->escape($data['payment_zone']);
        $arrayOrder[] = $this->db->escape($data['payment_zone_id']);
        $arrayOrder[] = $this->db->escape($data['payment_address_format']);
        $arrayOrder[] = $this->db->escape($data['payment_method']);
        $arrayOrder[] = $this->db->escape($data['payment_code']);
        $arrayOrder[] = $this->db->escape($data['payment_logradouro']);
        $arrayOrder[] = $this->db->escape($data['payment_tipo_logradouro']);
        $arrayOrder[] = $data['payment_codigo_municipio'];
        $arrayOrder[] = $this->db->escape($data['shipping_apelido']);
        $arrayOrder[] = $this->db->escape($data['shipping_firstname']);
        $arrayOrder[] = $this->db->escape($data['shipping_lastname']);
        $arrayOrder[] = $this->db->escape($data['shipping_company']);
        $arrayOrder[] = $this->db->escape($data['shipping_address_1']);
        $arrayOrder[] = $this->db->escape($data['shipping_numero']);
        $arrayOrder[] = $this->db->escape($data['shipping_address_2']);
        $arrayOrder[] = $this->db->escape($data['shipping_complemento']);
        $arrayOrder[] = stripslashes($this->db->escape($data['shipping_city']));
        $arrayOrder[] = $this->db->escape($data['shipping_postcode']);
        $arrayOrder[] = $this->db->escape($data['shipping_country']);
        $arrayOrder[] = $this->db->escape($data['shipping_country_id']);
        $arrayOrder[] = $this->db->escape($data['shipping_zone']);
        $arrayOrder[] = $this->db->escape($data['shipping_zone_id']);
        $arrayOrder[] = $this->db->escape($data['shipping_address_format']);
        $arrayOrder[] = stripslashes($this->db->escape($data['shipping_method']));
        $arrayOrder[] = stripslashes($this->db->escape( $data['shipping_code']));
        $arrayOrder[] = $this->db->escape($data['shipping_logradouro']);
        $arrayOrder[] = $this->db->escape($data['shipping_tipo_logradouro']);
        $arrayOrder[] = $data['shipping_codigo_municipio'];
        $arrayOrder[] = $this->db->escape($data['comment']);
        $arrayOrder[] = (float)$data['total'];
        $arrayOrder[] = (int)$data['affiliate_id'];
        $arrayOrder[] = (float)$data['commission'];
        $arrayOrder[] = (int)$data['language_id'];
        $arrayOrder[] = (int)$data['currency_id'];
        $arrayOrder[] = $this->db->escape($data['currency_code']);
        $arrayOrder[] = (float)$data['currency_value'];
        $arrayOrder[] = $this->db->escape($data['ip']);
        $arrayOrder[] = $this->db->escape($data['forwarded_ip']);
        $arrayOrder[] = $this->db->escape($data['user_agent']);
        $arrayOrder[] = $this->db->escape($data['accept_language']);
        $arrayOrder[] = $dataAtual;
        $arrayOrder[] = $dataAtual;
        $arrayOrder[] = $this->ftrgeralregistry->isApp() ? Type_OrigemSistema::Tp_App : Type_OrigemSistema::Tp_Ecommerce;
        $arrayOrder[] = $this->db->escape($data['sistema_operacional']);
        $arrayOrder[] = $this->db->escape($data['versao_sistema_operacional']);
        $arrayOrder[] = $this->db->escape($data['navegador']);
        $arrayOrder[] = $this->db->escape($data['versao_navegador']);
        $arrayOrder[] = $this->db->escape($data['celular']);
        $arrayOrder[] = $this->db->escape($data['tipo_dispositivo']);
        $arrayOrder[] = $this->db->escape($data['payment_option_code']);
        $arrayOrder[] = $this->db->escape($data['shipping_simalation_days']);
        $arrayOrder[] = (isset($data['fidelity'])) ? $data['total_pontos'] : 0;
        $arrayOrder[] = (isset($data['fidelity'])) ? $data['fidelity'] : 0;
        $arrayOrder[] = (isset($data['total_weight'])) ? (float)$data['total_weight'] : 0;
        $arrayOrder[] = (isset($data['total_net_weight'])) ? (float)$data['total_net_weight'] : 0;
        $arrayOrder[] = $this->db->escape($data['pickup_address'] ?? null);
        $arrayOrder[] = $this->db->escape($data['pickup_number'] ?? '0');
        $arrayOrder[] = $this->db->escape($data['pickup_neighborhood'] ?? null);
        $arrayOrder[] = $this->db->escape($data['pickup_complement'] ?? null);
        $arrayOrder[] = $this->db->escape($data['pickup_city'] ?? null);
        $arrayOrder[] = $this->db->escape($data['pickup_postcode'] ?? null);
        $arrayOrder[] = $this->db->escape($data['pickup_zone_id'] ?? null);
        $arrayOrder[] = $this->db->escape($data['pickup_zone'] ?? null);
        $arrayOrder[] = $this->db->escape($data['feedback_order'] ?? null);
        $arrayOrder[] = $this->db->escape($data['feedback_order_whatsapp'] ?? null);
        $arrayOrder[] = (float)$data['shipping_list_cost'] ?? null;

        if ($calcular_st && (!isset($data['fidelity']))) {
            $arrayFieldOrderSt = ['total_ipi_valor', 'total_st_valor', 'total_fcpst_valor', 'total_st_base', 'total_fcpst_base'];
            $arrayOrderSt = [(float)$data['totals_impostos']['total_ipi_valor'], (float)$data['totals_impostos']['total_st_valor'], (float)$data['totals_impostos']['total_fcpst_valor'], (float)$data['totals_impostos']['total_st_base'], (float)$data['totals_impostos']['total_fcpst_base']];
            $arrayFieldOrder = array_merge($arrayFieldOrder, $arrayFieldOrderSt);
            $arrayOrder = array_merge($arrayOrder, $arrayOrderSt);
        }

        $sqlOrderInsert = Ftr_sql::generateInsert('oc_order', $arrayFieldOrder);

        $this->db->executeSql($sqlOrderInsert, $arrayOrder);

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

        foreach ($data['products'] as $product) {

            $extraparans = '';

            if (isset($product['lista_escolar_id']) && $product['lista_escolar_id'] > 0) {
                $extraparans = " lista_escolar_id = '" . (int)$product['lista_escolar_id'] . "',"
                    . " lista_escolar_descricao = '" . $this->db->escape($product['lista_escolar_descricao']) . "',";
            }

            if (isset($product['brinde_product_id'])) {
                $extraparans .= " brinde_product_id = '" . (int)$product['brinde_product_id'] . "',";
            }

            if (isset($product['brinde_tipo'])) {
                $extraparans .= " brinde_tipo = '" . (int)$product['brinde_tipo'] . "',";
            }

            if (isset($data['fidelity'])) {
                $extraparans .= " pontos = '" . (int)$product['pontos'] . "', "
                    .  " total_pontos = '" . (int)$product['total_pontos'] . "', ";
            }else{
                $extraparans .=
                  " acrescimo_valor = '" . (float)$product['acrescimo_valor'] . "',"
                . " desconto_valor = '" . (float)$product['desconto_valor'] . "',"
                . " frete_valor = '" . (float)$product['frete_valor'] . "',";
            }

            if (isset($product['product_kit_id'])) {
                $extraparans .= " product_kit_id = '" . (int)$product['product_kit_id'] . "',";
            }

            if (isset($product['product_kit_qtd'])) {
                $extraparans .= " product_kit_qtd = '" . (int)$product['product_kit_qtd'] . "',";
            }

            if (isset($product['observation']) && !empty($product['observation'])) {
                $extraparans .= " observation = '" . $this->db->escape($product['observation']) . "',";
            }

            $order_product_sql = "INSERT INTO " . DB_PREFIX . "order_product SET"
                . " order_id = '" . (int)$order_id . "',"
                . " product_id = '" . (int)$product['product_id'] . "',"
                . $extraparans
                . " name = '" . $this->db->escape($product['name']) . "',"
                . " model = '" . $this->db->escape($product['model']) . "',"
                . " quantity = '" . (int)$product['quantity'] . "',"
                . " price = '" . (float)$product['price'] . "',"
                . " total = '" . (float)$product['total'] . "',"
                . " reward = '" . (int)$product['reward'] . "'";

            if ($calcular_st && (!isset($data['fidelity']))) {
                $order_product_sql .= ",ipi_valor = '" . (float)$product['float_ipi_valor'] . "',"
                    . " st_valor = '" . (float)$product['float_st_valor'] . "',"
                    . " fcpst_valor = '" . (float)$product['float_fcpst_valor'] . "',"
                    . " st_base = '" . (float)$product['float_st_base'] . "',"
                    . " fcpst_base = '" . (float)$product['float_fcpst_base'] . "',"
                    . " ipi_percentual = '" . (float)$product['float_ipi_percentual'] . "'";
            }

            $this->db->query($order_product_sql);

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

            foreach ($product['option'] as $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)$option['product_option_id'] . "', product_option_value_id = '" . (int)$option['product_option_value_id'] . "', name = '" . $this->db->escape($option['name']) . "', `value` = '" . $this->db->escape($option['value']) . "', `type` = '" . $this->db->escape($option['type']) . "'");
            }
        }

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

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

        if (isset($data['shipping_volumes_info']) && is_array($data['shipping_volumes_info'])) {
            $this->db->query(" DELETE from oc_order_shipping_volume where order_id=$order_id");
            foreach ($data['shipping_volumes_info'] as $volume) {
                $shippingVolumeId = $this->insertOrderShippingVolume($order_id, $volume);
                if (!empty($volume['products'])) {
                    $this->insertOrderShippingVolumeProduct($shippingVolumeId, $volume['products']);
                }
            }
            $this->setOrderShippingMD5($order_id, $data['products']);
        }

        if(isset($data['quote_reference_code']) && (trim($data['quote_reference_code']) != '')){
            $this->addOrderShipping($order_id,$data);
        }

        return $order_id;
    }

    public function addOrderShipping($order_id,$data=array()){
        $sql = "INSERT INTO ".DB_PREFIX."order_shipping SET order_id = '".$this->db->escape($order_id)."', quote_reference_code = '".$this->db->escape($data['quote_reference_code'])."' ";
        $this->db->query($sql);

    }

    public function addOrderHistory($order_id, $data, $callProccess = true)
    {
        $this->load->type('tipocomum');
        $this->load->helper('ftr_geral');
        $this->load->dmodel('sale/order');

        if ($callProccess) {
            $this->load->dmodel('proccess/order_to_situation');
            $this->dmodel_proccess_order_to_situation->updateHistory($order_id, (int)$data['order_status_id']);
        }

        if (!isset($data['client_notify'])) {
            $data['client_notify'] = $this->ftrgeralregistry->getClienteNotify((int)$data['order_status_id']);
        }

        if (!isset($data['comment'])) {
            $data['comment'] = '';
        }

        if ($this->config->get('listapresente_status')) {
            $this->load->model('futura/listapresente');

            // Verificar se os produtos comprados são de lista de presente
            $itens = $this->model_futura_listapresente->GetItensPedido($order_id);
            if ($itens) {
                $this->model_futura_listapresente->updateStatusItemPedido($itens, (int)$data['order_status_id']);
            }
        }

        $this->db->query("UPDATE `" . DB_PREFIX . "order` SET order_status_id = '" . (int)$data['order_status_id'] . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");

        if (isset($data['cometario_html']) && ($data['cometario_html'] == true)) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "order_history SET order_id = '" . (int)$order_id . "', order_status_id = '" . (int)$data['order_status_id']
              . "', notify = '" . (isset($data['client_notify']) ? (int)$data['client_notify'] : 0) . "', comment = '" . $this->db->escape(($data['comment']))
              . "', date_added = current_timestamp(2)" . ", manual ='" . (isset($data['manual']) ? (int)$data['manual'] : 0) . "'");
        } else {
            $this->db->query("INSERT INTO " . DB_PREFIX . "order_history SET order_id = '" . (int)$order_id . "', order_status_id = '" . (int)$data['order_status_id']
              . "', notify = '" . (isset($data['client_notify']) ? (int)$data['client_notify'] : 0) . "', comment = '" . $this->db->escape(strip_tags($data['comment']))
              . "', date_added = current_timestamp(2)" . ", manual ='" . (isset($data['manual']) ? (int)$data['manual'] : 0) . "'");
        }

        $order_history_id = $this->db->getLastId(); //Retorna o id da order_history, utilizado para atualizar o histórico do skyhub

        //        // Order Totals
        //        $order_total_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_total` WHERE order_id = '" . (int)$order_id . "' ORDER BY sort_order ASC");
        //
        //        foreach ($order_total_query->rows as $order_total) {
        //            $this->getOrderTotals($order_total['code']);
        //
        //            if (method_exists($this->{'model_total_' . $order_total['code']}, 'confirm')) {
        //                $this->{'model_total_' . $order_total['code']}->confirm($this->order_info, $order_total);
        //            }
        //        }

        return $order_history_id;
    }

    public function confirm($order_id, $order_status_id, $comment = '', $client_notify = null)
    {

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

        $order_info = $this->getOrder($order_id);

        if (is_null($client_notify)) {
            $client_notify = $this->ftrgeralregistry->getClienteNotify($order_status_id);
        }

        if ($order_info && !$order_info['order_status_id']) {


            if ($this->config->get('listapresente_status')) {
                $this->load->model('listapresente/listapresente');

                // Verificar se os produtos comprados sao de lista de presente
                if (isset($this->session->data['cartxitem'])) {
                    unset($this->session->data['cartxitem']['cliente']);
                    $this->model_listapresente_listapresente->addPedItensLista($this->session->data['cartxitem'], $order_id, $order_status_id);

                    $itens = $this->model_listapresente_listapresente->GetItensPedido($order_id);
                    if ($itens) {
                        $this->model_listapresente_listapresente->updateStatusItemPedido($itens, $order_status_id);
                    }

                    unset($this->session->data['cartxitem']);
                }
            }

            $this->load->dmodel('proccess/order_to_situation');
            $this->dmodel_proccess_order_to_situation->updateHistory($order_id, $order_status_id);

            $this->db->query("UPDATE `" . DB_PREFIX . "order` SET order_status_id = '" . (int)$order_status_id . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");

            $this->db->query("INSERT INTO " . DB_PREFIX . "order_history SET order_id = '" . (int)$order_id . "', order_status_id = '" . (int)$order_status_id . "', notify = '" . (int)$client_notify . "', comment = '" . $this->db->escape(($comment) ? $comment : '') . "', date_added = current_timestamp(2)");

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

            foreach ($order_product_query->rows as $order_product) {
                $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'");

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

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

            // Order Totals
            $order_total_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order_total` WHERE order_id = '" . (int)$order_id . "' ORDER BY sort_order ASC");

            foreach ($order_total_query->rows as $order_total) {
                $this->load->model('total/' . $order_total['code']);

                if (method_exists($this->{'model_total_' . $order_total['code']}, 'confirm')) {
                    $this->{'model_total_' . $order_total['code']}->confirm($order_info, $order_total);
                }
            }
        }
    }

    public function updateTotalOrder($id_order, $order_value)
    {
        $this->db->query("UPDATE `" . DB_PREFIX . "order` SET `total` = '" . $order_value . "' WHERE `order_id` = '" . (int) $id_order . "'");
        $this->db->query(
            "UPDATE `" . DB_PREFIX . "order_total`
                        SET
                         `value` = " . (float)$order_value . ",
                         `text` = '" . $this->currency->format($order_value) . "'
                        WHERE
                         `order_id` = '" . (int) $id_order . "' AND
                         `code` = 'total' "
        );
    }

    public function InsertOrderTotal($order_id, $dataTotal = array())
    {
        $this->db->query(
            "INSERT INTO `" . DB_PREFIX . "order_total`
          SET
            `order_id` = " . (int)$order_id . ",
            `code`  = '" . $dataTotal['module_code'] . "',
            `title` = '" . $dataTotal['module_title'] . "',
            `text`  = '" . $this->currency->format($dataTotal['value']) . "',
            `value` = " . (float)$dataTotal['value'] . ",
            `sort_order` = '" . $dataTotal['order'] . "'"
        );
    }

    public function getLastPaymentStatusHistory($order_id)
    {
        $sql = "select
            *
            from oc_order_payment_status_history
            where order_id = $order_id
            order by ordem desc limit 1
           ";
        $order_item_query = $this->db->query($sql);
        if ($order_item_query->num_rows > 0) {
            return $order_item_query->row;
        } else {
            return false;
        }
    }

    public function InsertPaymentStatusHistory($order_id, $status, $ordem)
    {
        $this->db->query("INSERT INTO oc_order_payment_status_history
    SET `order_id` = $order_id,`status` = $status,`ordem` = $ordem");
    }

    public function insertOrderShippingVolume($orderid, $datashipping)
    {
        $sql = "INSERT INTO oc_order_shipping_volume
        SET `order_id` =" . $orderid . "," .
            "`weight` =" . $datashipping['weight'] . "," .
            "`length` =" . $datashipping['lenght'] . "," .
            "`width` =" . $datashipping['width'] . "," .
            "`height` =" . $datashipping['height'] . "," .
            "`valor_total` =" . $datashipping['cost_of_goods'];

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

    public function insertOrderShippingVolumeProduct($volumeId, $productInfo)
    {
        foreach ($productInfo as $value) {
            $sql = "INSERT INTO oc_order_shipping_volume_product
        SET `order_shipping_volume_id` = " . $volumeId . "," .
                "`product_id` = " . $value->id . "," .
                "`quantity` = " . $value->quantity;
            $this->db->query($sql);
        }
    }

    function setOrderShippingMD5($order_id, $dataProdutos)
    {
        $md5 = '';
        $cep_origem = '';
        $order_info = $this->getOrder($order_id);
        $shipping_code = explode('.', $order_info['shipping_code']);
        $shipping_code = $shipping_code[0];

        switch (strtolower($shipping_code)) {
            case 'correios':
                $cep_origem = $this->config->get('correios_postcode');
                break;
            case 'intelipost':
                $cep_origem = $this->config->get('intelipost_cep_origem');
                break;
            case 'dlog':
                $cep_origem = $this->config->get('dlog_cep_origem');
                break;
            case 'melhorenvio':
                $cep_origem = $this->config->get('melhorenvio_postcode');
                break;
            case 'kangu':
                $cep_origem = $this->config->get('kangu_postcode');
                break;
        };
        foreach ($dataProdutos as $data) {
            $md5 = $md5 .
                '|ITEM:' . $data['id_futura'] .
                '|QTD:' . $data['quantity'] .
                '|WIDTH:' . number_format($data['width'], 2, ',', '') .
                '|HEIGHT:' . number_format($data['height'], 2, ',', '') .
                '|LENGTH:' . number_format($data['length'], 2, ',', '') .
                '|WEIGHT:' . number_format($data['weight_unit'], 2, ',', '') .
                '|WIDTHPROGRESS:' .   number_format($data['larg_progressiva'], 2, ',', '') .
                '|HEIGHTPROGRESS:' . number_format($data['alt_progressiva'], 2, ',', '') .
                '|LENGTHPROGRESS:' . number_format($data['comp_progressiva'], 2, ',', '') .
                '|CEPCLI:' . str_replace('-', '', $order_info['shipping_postcode']) .
                '|CEPEMP:' . str_replace('-', '', $cep_origem);
        };
        /*
        No server sempre faz o calculo considerando que o que vem do ecommerce é simulado,
        se algum dia a regra mudar, validar e adicionar a opção de gerar o md5 abaixo.
        Ver uTOtr_Frete, função StringMD5
        foreach ($dataVolumes as $data){
        $md5 = $md5.
        '|WIDTH:'.$data['width'].
        '|HEIGHT:'.$data['height'].
        '|LENGTH:'.$data['length'].
        '|WEIGHT:'.$data['weight'].
        '|TOTAL_PRICE:'.$data['valor_total'].
        '|CEPCLI:'.$data['cep_destino'].
        '|CEPEMP:'.$data['cep_origem'];
        }*/
        $md5 = md5($md5);
        $this->db->query("UPDATE `" . DB_PREFIX . "order` SET `shipping_info_md5` = '" . $md5 . "' WHERE `order_id` = '" . (int) $order_id . "'");
    }

    public function getOrdersByCustommer($customer_id, $data_ini, $data_fim)
    {
        $sql = " SELECT * FROM oc_order WHERE customer_id = " . (int)$customer_id;
        $sql .= " AND DATE(date_added) BETWEEN '" . $this->db->escape($data_ini) .  "' AND '" . $this->db->escape($data_fim) . "'";
        return $this->db->query($sql)->rows;
    }

    public function getOrdersByIP($ip, $data_ini, $data_fim)
    {
        $sql = " SELECT * FROM oc_order WHERE ip = '" . $ip . "'";
        $sql .= " AND DATE(date_added) BETWEEN '" . $this->db->escape($data_ini) .  "' AND '" . $this->db->escape($data_fim) . "'";
        return $this->db->query($sql)->rows;
    }


    public function getOrderProductKitItens($product_kit_id)
    {
        $itens_kit = array();
        if ($product_kit_id) {
            $this->load->dmodel('catalog/product');
            $this->load->dmodel('tool/image');

            $sql = "SELECT
        pki.product_kit_item_id,
        pki.product_kit_id,
        pki.quantity quantidade,
        pd.name,
        p.*
      FROM oc_product_kit_item pki
      INNER JOIN oc_order_product op ON op.product_kit_id = pki.product_id
      INNER JOIN oc_product p ON op.product_id = p.product_id
      INNER JOIN oc_product_description pd ON p.product_id = pd.product_id
      Where op.product_kit_id = $product_kit_id";

            $itens_kit_model = $this->db->query($sql)->rows;
            foreach ($itens_kit_model as $item_kit) {
                if ($item_kit['image']) {
                    $tumb_img_kit = $this->dmodel_tool_image->resize($item_kit['image'], $this->config->get('config_image_thumb_width'), $this->config->get('config_image_thumb_height'));
                } else {
                    $tumb_img_kit = $this->dmodel_tool_image->resize('data/sem-imagem.png', $this->config->get('config_image_thumb_width'), $this->config->get('config_image_thumb_height'));
                }
                $itens_kit[] = array(
                    'thumb'       => $tumb_img_kit,
                    'quantidade'  => $item_kit['quantidade'],
                    'descricao'   => $item_kit['name'],
                    'href'        => $this->url->link('product/product', 'product_id=' . $item_kit['product_id'])
                );
            }
        }
        return $itens_kit;
    }


    public function updateApportionment($order_id, $value, $type, $execute=true, $kitID=null, $order_itens=array()){
        $this->load->helper('Ftr_sql');

        if (!$order_itens){
            $order_itens = $this->getOrderItems($order_id);
        }

        if($kitID != null){
            /*Podem haver situações que seja necessário ratear valores apenas em determinado Kit, exemplo, comissão do marketplace*/
            $order_itens = array_filter($order_itens, function($v, $k) use ($kitID) {
                return $v['product_kit_id'] == $kitID;
            }, ARRAY_FILTER_USE_BOTH);
        }

        $subtotal =  array_sum(array_column($order_itens,'total'));
        $accValue = 0;
        $totalValue = 0;
        $inc = 0;

        switch ($type){
            case Type_Apportionment::Discount :
                $totalValue = array_sum(array_column($order_itens,'desconto_valor'))+$value;
                $field = 'desconto_valor';
            break;
            case Type_Apportionment::Increase :
                $totalValue = array_sum(array_column($order_itens,'acrescimo_valor'))+$value;
                $field = 'acrescimo_valor';
            break;
            case Type_Apportionment::Shipping :
                $totalValue = array_sum(array_column($order_itens,'frete_valor'))+$value;
                $field = 'frete_valor';
            break;
            case Type_Apportionment::MktCommission :
                $totalValue = array_sum(array_column($order_itens,'mkt_comission'))+$value;
                $field = 'mkt_comission';
            break;
        }

        $countItem = count($order_itens);
        foreach ($order_itens as $item){
            $inc += 1;
            $new_value = 0;
            $baseproduto  = (($item['quantity'] * $item['price']) * 100) / $subtotal;
            $new_value = (($baseproduto  / 100) * $value) + $item[$field];
            $new_value = round($new_value, 2, PHP_ROUND_HALF_DOWN);
            $accValue += $new_value;
            if (($accValue <> $totalValue) && ($countItem == $inc)) {
                $new_value += $totalValue - round($accValue, 2, PHP_ROUND_HALF_DOWN);
            }
            if ($execute){
                $sqlUpdParam = Ftr_sql::generateUpdate('oc_order_product', [$field], 'order_product_id = ' . $item['order_product_id']);
                $this->db->executeSql($sqlUpdParam, [$new_value]);
            }
            $result[] = array(
                'order_product_id' => $item['order_product_id'],
                'value' => $new_value
            );
        }
        return $result;
    }

    public function updateApportionmentAll($order_id, $dataValue=array()){
        $data[Type_Apportionment::Shipping] = $this->updateApportionment($order_id,$dataValue[Type_Apportionment::Shipping],Type_Apportionment::Shipping,false);
        $data[Type_Apportionment::Discount] = $this->updateApportionment($order_id,$dataValue[Type_Apportionment::Discount],Type_Apportionment::Discount,false);
        $data[Type_Apportionment::Increase] = $this->updateApportionment($order_id,$dataValue[Type_Apportionment::Increase],Type_Apportionment::Increase,false);

        if (count($data)>0){
            foreach ($data as $field => $fieldProducts){
                foreach ($fieldProducts as $product){
                    $arrayAux[$product['order_product_id']][$field] = array(
                        'field' => Type_Apportionment::getNickFromConstValue($field),
                        'value' => $product['value']
                    );
                }
            }

            foreach($arrayAux as $orderProductId => $orderProduct){
                $sqlUpdParam = Ftr_sql::generateUpdate('oc_order_product',
                [
                    $orderProduct[Type_Apportionment::Shipping]['field'],
                    $orderProduct[Type_Apportionment::Discount]['field'],
                    $orderProduct[Type_Apportionment::Increase]['field']
                ],
                'order_product_id = ' . $orderProductId);
                $this->db->executeSql($sqlUpdParam, [
                    $orderProduct[Type_Apportionment::Shipping]['value'],
                    $orderProduct[Type_Apportionment::Discount]['value'],
                    $orderProduct[Type_Apportionment::Increase]['value']
                ]);
            }
        }
    }

    public function getLastOrderId(){
        $sql = "SELECT coalesce(MAX(order_id), 0) as order_id FROM oc_order";
        return $this->db->query($sql)->row['order_id'];
    }

    public function getOrdersLimiterByCustommer($customer_id,$safety){

        $ini = new DateTime($this->db->dataAtual(), new DateTimeZone('America/Sao_Paulo'));
        $ini->modify('-'.$safety['safety_limiter_transaction_time'].' min');
        $fim = new DateTime($this->db->dataAtual(), new DateTimeZone('America/Sao_Paulo'));;


        $sql = "SELECT COUNT(*) as value
                  FROM oc_order
                 WHERE customer_id = {$customer_id}
                   AND date_added BETWEEN  '{$ini->format("Y-m-d H:i:s")}' AND '{$fim->format("Y-m-d H:i:s")}'";

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

    public function getOrdersLimiterByIP($customer_ip,$safety){
        $ini = new DateTime($this->db->dataAtual(), new DateTimeZone('America/Sao_Paulo'));
        $ini->modify('-'.$safety['safety_limiter_transaction_time'].' min');
        $fim = new DateTime($this->db->dataAtual(), new DateTimeZone('America/Sao_Paulo'));


        $sql = "SELECT COUNT(*) as value
                  FROM oc_order
                 WHERE ip = '{$customer_ip}'
                   AND date_added BETWEEN  '{$ini->format("Y-m-d H:i:s")}' AND '{$fim->format("Y-m-d H:i:s")}'";

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




}
