<?php

class DModelmarketplacemktpedido extends Model
{

    public function addOrder(MktPedido $order, $comission_value, $payment_code, $shipping_code, $addtionalFields = Array(), $addtionalValues = Array(), $shipping_name_as_method = false)
    {

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

        $addSql = "";
        if (!empty($addtionalFields)) {
            foreach ($addtionalFields as $key => $field) {
                $addSql .= $field . " = " . $addtionalValues[$key] . " ,";
            }
        }

        if (!empty($this->request->server['HTTP_X_FORWARDED_FOR'])) {
            $forwarded_ip = $this->request->server['HTTP_X_FORWARDED_FOR'];
        } elseif (!empty($this->request->server['HTTP_CLIENT_IP'])) {
            $forwarded_ip = $this->request->server['HTTP_CLIENT_IP'];
        } else {
            $forwarded_ip = '';
        }

        if (isset($this->request->server['HTTP_USER_AGENT'])) {
            $user_agent = $this->request->server['HTTP_USER_AGENT'];
        } else {
            $user_agent = '';
        }

        if (isset($this->request->server['HTTP_ACCEPT_LANGUAGE'])) {
            $accept_language = $this->request->server['HTTP_ACCEPT_LANGUAGE'];
        } else {
            $accept_language = '';
        }

        $isShippingToMatch = false;
        if($order->getMktIntegration() == Type_MarketPlace::Tp_Mercadolivre) {
            $meli_shipping_id = $order->getParam('meli_shipping_id');

            //frete a combinar MERCADO LIVRE
            $isShippingToMatch =
                ($meli_shipping_id == null || $meli_shipping_id != '') &&
                ($order->getBuyer()->getDocumento() == null || $order->getBuyer()->getDocumento() == '');
        }

        $customer_id = null;
        $customer_group_id = null;
        if(!$isShippingToMatch){
        $orderAddress = $this->getOrderAddress($order);

            $customer_info = $this->getOrderCustomer($order, $orderAddress);
            $customer_id = $customer_info['customer_id'];
            $customer_group_id = $customer_info['customer_group_id'];

            if ($shipping_name_as_method) {
                $shipping_method = !empty($order->getShippingName()) ? $order->getShippingName() : $this->getModuleName($shipping_code, 'shipping');
            } else {
                $shipping_method = empty($this->getModuleName($shipping_code, 'shipping')) ? $order->getShippingName() : $this->getModuleName($shipping_code, 'shipping');
            }
        } else {
            if ($shipping_name_as_method) {
                $shipping_method = !empty($order->getShippingName()) ? $order->getShippingName() : $this->getModuleName('freteacombinar', 'shipping');
            } else {
                $shipping_method = empty($this->getModuleName('freteacombinar', 'shipping')) ? $order->getShippingName() : $this->getModuleName('freteacombinar', 'shipping');
            }
        }

        $addSql .= ($order->getBuyer()->getTipoPessoa() == Type_Pessoa::TpPess_fisica) ? " cpf = '" . $this->db->escape($order->getBuyer()->getDocumento()) . "'," : '';
        $addSql .= ($order->getBuyer()->getTipoPessoa() == Type_Pessoa::TpPess_Juridica) ? " cnpj = '" . $this->db->escape($order->getBuyer()->getDocumento()) . "'," : '';

        // Se não possui dados do cliente e de endereço, é um pedido não confirmado, que será atualizado depois
        $naoConfirmado = ((mb_strtolower($order->getBuyer()->getFirstName(), 'UTF-8') == 'não') &&
            (($order->getBuyer()->getSendAddress()->getPostCode() == '00000000') ||
             (mb_strtolower($order->getBuyer()->getSendAddress()->getLogradouro(), 'UTF-8') == 'não informado'))) || $isShippingToMatch;

        if ($naoConfirmado) {
            $addSql .= " nao_confirmado = " . $this->db->escape(Type_DsimDnao::Tp_Sim) . ",";
        }

        if (!is_null($payment_code) && $payment_code ){
            $payment_caption = Type_MktPaymentForIntegrationUser::getNickFromValue($payment_code)['caption'];
            if (in_array($payment_code, Type_MktPaymentForIntegrationUser::getEnumListConst())){
                $config_name_integration_user = Type_MktPaymentForIntegrationUser::getNickFromValue($payment_code)['config'];
                $integration_user = $this->config->get($config_name_integration_user);
            } else {
                $integration_user = '';
            }
        } else {
            $integration_user = '';
            $payment_caption = null;
        }

        $payment_type = $payment_code;
        $payment_name = $this->getModuleName($payment_code, 'payment');
        $mktPaymentName = '';
        $mktPaymentType = '';
        if (!empty($order->getPayments())){
            $paymentTypes = [];
            foreach ($order->getPayments() as $payment){
                if (!in_array($payment->getCode(), $paymentTypes)){
                    $paymentTypes[] = $payment->getCode();
                }
            }

            if (count($paymentTypes) == 1) {
                $mktPaymentType = current($order->getPayments())->getCode();
                $payment_type .= "." . $mktPaymentType;

                switch ($order->getMktIntegration()) {
                    case Type_MarketPlace::Tp_Mercadolivre:
                        $mktPaymentName = Type_MercadoLivrePaymentMethod::getNickFromConstValue(current($order->getPayments())->getCode());
                        break;
                    case Type_MarketPlace::Tp_SkyHub:
                        $mktPaymentName = Type_SkyhubPaymentMethod::getNickFromConstValue(current($order->getPayments())->getCode());
                        break;
                }
            }
            $payment_name .= ($mktPaymentName ? " (" . $mktPaymentName . ")" : '');
        }

        $first_name = !$isShippingToMatch ? $order->getBuyer()->getFirstName() : '';
        $last_name = !$isShippingToMatch ? $order->getBuyer()->getLastName() : '';
        $email = !$isShippingToMatch ? $order->getBuyer()->getEmail() : '';
        $telephone = !$isShippingToMatch ? $order->getBuyer()->getPhone() : '';
        $celular = !$isShippingToMatch ? $order->getBuyer()->getCelular() : '';

       //Fiz assim atribuindo a variaveis pois quando for Frete a combinar ele nao pode passar os valores,
       //entao pra nao ficar fazendo if ternario no meio do SQL que ja ta zuado, eu fiz assim

       //Payment_address
       $payment_address_1         = !$isShippingToMatch ? $orderAddress['billing']['address_1'] : '';
       $payment_numero            = !$isShippingToMatch ? $orderAddress['billing']['numero'] : '';
       $payment_address_2         = !$isShippingToMatch ? $orderAddress['billing']['address_2'] : '';
       $payment_complemento       = !$isShippingToMatch ? $orderAddress['billing']['complemento'] : '';
       $payment_city              = !$isShippingToMatch ? $orderAddress['billing']['city'] : '';
       $payment_postcode          = !$isShippingToMatch ? $orderAddress['billing']['postcode'] : '';
       $payment_country           = !$isShippingToMatch ? $orderAddress['billing']['dadosPais']['name'] : '';
       $payment_country_id        = !$isShippingToMatch ? (int)$orderAddress['billing']['dadosPais']['country_id'] : 0;
       $payment_zone              = !$isShippingToMatch ? $orderAddress['billing']['uf'] : '';
       $payment_zone_id           = !$isShippingToMatch ? (int)$orderAddress['billing']['state_zone_id'] : 0;
       $payment_tipo_logradouro   = !$isShippingToMatch ?  $orderAddress['billing']['tipo_logradouro'] : '';
       $payment_logradouro        = !$isShippingToMatch ?  $orderAddress['billing']['logradouro'] : '';
       $payment_codigo_municipio  = !$isShippingToMatch ?  (int)$orderAddress['billing']['codigo_municipio'] : 0;

        //Shipping_address
        $shipping_address_1        = !$isShippingToMatch ? $orderAddress['shipping']['address_1'] : '';
        $shipping_numero           = !$isShippingToMatch ? $orderAddress['shipping']['numero'] : '';
        $shipping_address_2        = !$isShippingToMatch ? $orderAddress['shipping']['address_2'] : '';
        $shipping_complemento      = !$isShippingToMatch ? $orderAddress['shipping']['complemento'] : '';
        $shipping_city             = !$isShippingToMatch ? $orderAddress['shipping']['city'] : '';
        $shipping_postcode         = !$isShippingToMatch ? $orderAddress['shipping']['postcode'] : '';
        $shipping_country          = !$isShippingToMatch ? $orderAddress['shipping']['dadosPais']['name'] : '';
        $shipping_country_id       = !$isShippingToMatch ? (int)$orderAddress['shipping']['dadosPais']['country_id'] : 0;
        $shipping_zone             = !$isShippingToMatch ? $orderAddress['shipping']['uf'] : '';
        $shipping_zone_id          = !$isShippingToMatch ? (int)$orderAddress['shipping']['state_zone_id'] : 0;
        $shipping_tipo_logradouro  = !$isShippingToMatch ? $orderAddress['shipping']['tipo_logradouro'] : '';
        $shipping_logradouro       = !$isShippingToMatch ? $orderAddress['shipping']['logradouro'] : '';
        $shipping_codigo_municipio = !$isShippingToMatch ? (int)$orderAddress['shipping']['codigo_municipio'] : 0;


        $this->db->query(
            "INSERT INTO `oc_order` SET "
            . $addSql
            . " store_id                  = '" . (int)$this->config->get('config_store_id') . "',"
            . " store_name                = '" . $this->db->escape($this->config->get('config_name')) . "',"
            . " store_url                 = '" . $this->db->escape($this->urlloja->link('', '', 'SSL', false)) . "',"
            . " customer_id               = '" . (int)$customer_id . "',"
            . " customer_group_id         = '" . (int)$customer_group_id . "',"
            . " firstname                 = '" . $this->db->escape($first_name) . "',"
            . " lastname                  = '" . $this->db->escape($last_name) . "',"
            . " email                     = '" . $this->db->escape($email) . "',"
            . " telephone                 = '" . $this->db->escape($telephone) . "',"
            . " celular                   = '" . $this->db->escape($celular) . "',"
            . " fax                       = '',"

            . " payment_firstname         = '" . $this->db->escape($first_name) . "',"
            . " payment_lastname          = '" . $this->db->escape($last_name) . "',"
            . " payment_company           = '',"
            . " payment_company_id        = '',"
            . " payment_address_1         = '" . $this->db->escape($payment_address_1) . "',"
            . " payment_numero            = '" . $this->db->escape($payment_numero) . "',"
            . " payment_address_2         = '" . $this->db->escape($payment_address_2) . "',"
            . " payment_complemento       = '" . $this->db->escape($payment_complemento) . "',"
            . " payment_city              = '" . $this->db->escape($payment_city) . "',"
            . " payment_postcode          = '" . $this->db->escape($payment_postcode) . "',"
            . " payment_country           = '" . $this->db->escape($payment_country) . "',"
            . " payment_country_id        = '" . $payment_country_id . "',"
            . " payment_zone              = '" . $this->db->escape($payment_zone) . "',"
            . " payment_zone_id           = '" . $payment_zone_id . "',"
            . " payment_address_format    = '',"
            . " payment_tipo_logradouro   = '" . $this->db->escape($payment_tipo_logradouro) . "',"
            . " payment_logradouro        = '" . $this->db->escape($payment_logradouro) . "',"
            . " payment_codigo_municipio  = '" . $payment_codigo_municipio . "',"
            . " payment_code              = '" . $this->db->escape($payment_type) . "',"
            . " payment_method            = '" . $this->db->escape($payment_name) . "',"
            . " payment_caption           = '" . $this->db->escape($payment_caption) . "',"

            . " integracao_user           = '" . $this->db->escape($integration_user) . "',"

            . " shipping_firstname        = '" . $this->db->escape($first_name) . "',"
            . " shipping_lastname         = '" . $this->db->escape($last_name) . "',"
            . " shipping_company          = '',"
            . " shipping_address_1        = '" . $this->db->escape($shipping_address_1) . "',"
            . " shipping_numero           = '" . $this->db->escape($shipping_numero) . "',"
            . " shipping_address_2        = '" . $this->db->escape($shipping_address_2) . "',"
            . " shipping_complemento      = '" . $this->db->escape($shipping_complemento) . "',"
            . " shipping_city             = '" . $this->db->escape($shipping_city) . "',"
            . " shipping_postcode         = '" . $this->db->escape($shipping_postcode) . "',"
            . " shipping_country          = '" . $this->db->escape($shipping_country) . "',"
            . " shipping_country_id       = '" . $shipping_country_id . "',"
            . " shipping_zone             = '" . $this->db->escape($shipping_zone) . "',"
            . " shipping_zone_id          = '" . $shipping_zone_id . "',"
            . " shipping_address_format   = '',"
            . " shipping_tipo_logradouro  = '" . $this->db->escape($shipping_tipo_logradouro) . "',"
            . " shipping_logradouro       = '" . $this->db->escape($shipping_logradouro) . "',"
            . " shipping_codigo_municipio = '" . $shipping_codigo_municipio . "',"
            . " shipping_method           = '" . $this->db->escape($shipping_method) . "',"
            . " shipping_code             = '" . $this->db->escape($shipping_code . '.' . $shipping_method) . "',"

            . " total                     = '" . (float)$order->getTotal() . "',"
            . " affiliate_id              = 0,"
            . " commission                = 0,"
            . " language_id               = '" . (int)$this->config->get('config_language_id') . "',"
            . " currency_id               = '" . (int)$this->currency->getId() . "',"
            . " currency_code             = '" . $this->db->escape($this->currency->getCode()) . "',"
            . " currency_value            = '" . (float)$this->currency->getValue($this->currency->getCode()) . "',"
            . " ip                        = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "',"
            . " forwarded_ip              = '" . $this->db->escape($forwarded_ip) . "',"
            . " user_agent                = '" . $this->db->escape($user_agent) . "',"
            . " accept_language           = '" . $this->db->escape($accept_language) . "',"
            . " date_added                = NOW(),"
            . " date_modified             = NOW(),"
            . " origem_pedido             = " . Type_OrigemSistema::Tp_Marketplace . ","
            . " mkt_integracao_tipo       = '" . $this->db->escape($order->getMktIntegration()) . "',"
            . " mkt_tipo                  = '" . $this->db->escape($order->getMkTipo()) . "',"
            . " comment                   = '" . $this->db->escape($order->getComment()) . "',"
            . " mkt_comission_total       = '" . $this->db->escape($comission_value) . "'"
        );

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

        switch ($order->getMktIntegration()) {
            case Type_MarketPlace::Tp_Mercadolivre:
                if (in_array($mktPaymentType, [Type_MercadoLivrePaymentMethod::CreditCard])) {
                    foreach ($order->getPayments() as $payment){
                        $this->addPaymentCreditCard($order_id, $payment);
                    }
                }
                break;
            case Type_MarketPlace::Tp_SkyHub:
                if (in_array($mktPaymentType, [Type_SkyhubPaymentMethod::CreditCard])) {
                    foreach ($order->getPayments() as $payment){
                        $this->addPaymentCreditCard($order_id, $payment);
                    }
                }
                break;
        }

        return $order_id;
    }

    public function addPaymentCreditCard($order_id, MktPedidoPayment $payment)
    {
        $paymentinfo = array();
        $paymentinfo['tid'] = '';
        $paymentinfo['payment_id'] = $payment->getPaymentId();
        $paymentinfo['numero_autorizacao'] = $payment->getAuthorizationCode();
        $paymentinfo['cod_autorizacao'] = $payment->getAuthorizationCode();
        $paymentinfo['order_id'] = $order_id;
        $paymentinfo['valor_pagamento'] = $payment->getValue();
        $paymentinfo['data_pagamento'] = $payment->getDate() ?? $this->db->dataAtual();// $data->format("Y-m-d H:i:s");
        $paymentinfo['tipo_cartao'] = Type_Card::Tp_Credito;
        $paymentinfo['qtd_parcela'] = $payment->getParcels();
        $paymentinfo['cartao_bandeira_text'] = ucfirst($payment->getFlagText());

        $this->load->dmodel('payment/payment_info');
        $this->dmodel_payment_payment_info->addPaymentCreditCard($paymentinfo);
    }

    public function updateOrderCustomerName($orderInfo, MktPedido $order){
        $this->db->query(
            "UPDATE `oc_order` SET"
            . " firstname                 = '" . $this->db->escape($order->getBuyer()->getFirstName()) . "',"
            . " lastname                  = '" . $this->db->escape($order->getBuyer()->getLastName()) . "',"
            . " date_modified             = NOW()"
            . " where order_id =             " . $this->db->escape($orderInfo['order_id'])
        );
    }

    public function updateOrderProduct($orderInfo, MktPedido $order){

      foreach ($order->getItens() as $key => $product){
        $sql = " UPDATE oc_order_product SET price = ".$product->getPrice().",
                                             total = ".($product->getPrice() * $product->getQuantity())."
                  WHERE marketId = ".$product->getMktId()."
                    AND order_id   = ". $orderInfo['order_id'];
        $this->db->query($sql);
      }

    }

    public function updateOrder($orderInfo, MktPedido $order)
    {
        // Se não possui dados do cliente e de endereço, é um pedido não confirmado, que será atualizado depois
        if ((in_array(mb_strtolower($order->getBuyer()->getFirstName(), 'UTF-8'), array('não'))) &&
            (($order->getBuyer()->getSendAddress()->getPostCode() == '00000000') ||
             (mb_strtolower($order->getBuyer()->getSendAddress()->getLogradouro(), 'UTF-8') == 'não informado'))) {
            $naoConfirmado = $this->db->escape(Type_DsimDnao::Tp_Sim);
        } else {
            $naoConfirmado = $this->db->escape(Type_DsimDnao::Tp_Nao);
        }

        $orderAddress = $this->getOrderAddress($order);

        $this->db->query(
            "UPDATE `oc_order` SET"
            . " firstname                 = '" . $this->db->escape($order->getBuyer()->getFirstName()) . "',"
            . " lastname                  = '" . $this->db->escape($order->getBuyer()->getLastName()) . "',"
            . " email                     = '" . $this->db->escape($order->getBuyer()->getEmail()) . "',"
            . " telephone                 = '" . $this->db->escape($order->getBuyer()->getPhone()) . "',"
            . " celular                   = '" . $this->db->escape($order->getBuyer()->getCelular()) . "',"

            . " payment_firstname         = '" . $this->db->escape($order->getBuyer()->getFirstName()) . "',"
            . " payment_lastname          = '" . $this->db->escape($order->getBuyer()->getLastName()) . "',"
            . " payment_address_1         = '" . $this->db->escape($orderAddress['billing']['address_1']) . "',"
            . " payment_numero            = '" . $this->db->escape($orderAddress['billing']['numero']) . "',"
            . " payment_address_2         = '" . $this->db->escape($orderAddress['billing']['address_2']) . "',"
            . " payment_complemento       = '" . $this->db->escape($orderAddress['billing']['complemento']) . "',"
            . " payment_city              = '" . $this->db->escape($orderAddress['billing']['city']) . "',"
            . " payment_postcode          = '" . $this->db->escape($orderAddress['billing']['postcode']) . "',"
            . " payment_country           = '" . $this->db->escape($orderAddress['billing']['dadosPais']['name']) . "',"
            . " payment_country_id        = '" . (int)$orderAddress['billing']['dadosPais']['country_id'] . "',"
            . " payment_zone              = '" . $this->db->escape($orderAddress['billing']['uf']) . "',"
            . " payment_zone_id           = '" . (int)$orderAddress['billing']['state_zone_id'] . "',"
            . " payment_tipo_logradouro   = '" . $this->db->escape($orderAddress['billing']['tipo_logradouro']) . "',"
            . " payment_logradouro        = '" . $this->db->escape($orderAddress['billing']['logradouro']) . "',"
            . " payment_codigo_municipio  = '" . (int)$orderAddress['billing']['codigo_municipio'] . "',"

            . " shipping_firstname        = '" . $this->db->escape($order->getBuyer()->getFirstName()) . "',"
            . " shipping_lastname         = '" . $this->db->escape($order->getBuyer()->getLastName()) . "',"
            . " shipping_address_1        = '" . $this->db->escape($orderAddress['shipping']['address_1']) . "',"
            . " shipping_numero           = '" . $this->db->escape($orderAddress['shipping']['numero']) . "',"
            . " shipping_address_2        = '" . $this->db->escape($orderAddress['shipping']['address_2']) . "',"
            . " shipping_complemento      = '" . $this->db->escape($orderAddress['shipping']['complemento']) . "',"
            . " shipping_city             = '" . $this->db->escape($orderAddress['shipping']['city']) . "',"
            . " shipping_postcode         = '" . $this->db->escape($orderAddress['shipping']['postcode']) . "',"
            . " shipping_country          = '" . $this->db->escape($orderAddress['shipping']['dadosPais']['name']) . "',"
            . " shipping_country_id       = '" . (int)$orderAddress['shipping']['dadosPais']['country_id'] . "',"
            . " shipping_zone             = '" . $this->db->escape($orderAddress['shipping']['uf']) . "',"
            . " shipping_zone_id          = '" . (int)$orderAddress['shipping']['state_zone_id'] . "',"
            . " shipping_tipo_logradouro  = '" . $this->db->escape($orderAddress['shipping']['tipo_logradouro']) . "',"
            . " shipping_logradouro       = '" . $this->db->escape($orderAddress['shipping']['logradouro']) . "',"
            . " shipping_codigo_municipio = '" . (int)$orderAddress['shipping']['codigo_municipio'] . "',"

            . " total                     = '" . (float)$order->getTotal() . "',"
            . " date_modified             = NOW(),"
            . " nao_confirmado            = " . $naoConfirmado . ""

            . " where order_id =             " . $this->db->escape($orderInfo['order_id'])
        );
    }

    public function updateOrderAddress($orderInfo, MktPedido $order, $addtionalFields = Array(), $addtionalValues = Array())
    {
        $orderAddress = $this->getOrderAddress($order);

        $addSql = "";
        if (!empty($addtionalFields)) {
            foreach ($addtionalFields as $key => $field) {
                $addSql .= $field . " = " . $addtionalValues[$key] . " ,";
            }
        }

        $this->db->query(
            "UPDATE `oc_order` SET"
            . " payment_firstname         = '" . $this->db->escape($order->getBuyer()->getFirstName()) . "',"
            . " payment_lastname          = '" . $this->db->escape($order->getBuyer()->getLastName()) . "',"
            . " payment_address_1         = '" . $this->db->escape($orderAddress['billing']['address_1']) . "',"
            . " payment_numero            = '" . $this->db->escape($orderAddress['billing']['numero']) . "',"
            . " payment_address_2         = '" . $this->db->escape($orderAddress['billing']['address_2']) . "',"
            . " payment_complemento       = '" . $this->db->escape($orderAddress['billing']['complemento']) . "',"
            . " payment_city              = '" . $this->db->escape($orderAddress['billing']['city']) . "',"
            . " payment_postcode          = '" . $this->db->escape($orderAddress['billing']['postcode']) . "',"
            . " payment_country           = '" . $this->db->escape($orderAddress['billing']['dadosPais']['name']) . "',"
            . " payment_country_id        = '" . (int)$orderAddress['billing']['dadosPais']['country_id'] . "',"
            . " payment_zone              = '" . $this->db->escape($orderAddress['billing']['uf']) . "',"
            . " payment_zone_id           = '" . (int)$orderAddress['billing']['state_zone_id'] . "',"
            . " payment_tipo_logradouro   = '" . $this->db->escape($orderAddress['billing']['tipo_logradouro']) . "',"
            . " payment_logradouro        = '" . $this->db->escape($orderAddress['billing']['logradouro']) . "',"
            . " payment_codigo_municipio  = '" . (int)$orderAddress['billing']['codigo_municipio'] . "',"

            . " shipping_firstname        = '" . $this->db->escape($order->getBuyer()->getFirstName()) . "',"
            . " shipping_lastname         = '" . $this->db->escape($order->getBuyer()->getLastName()) . "',"
            . " shipping_address_1        = '" . $this->db->escape($orderAddress['shipping']['address_1']) . "',"
            . " shipping_numero           = '" . $this->db->escape($orderAddress['shipping']['numero']) . "',"
            . " shipping_address_2        = '" . $this->db->escape($orderAddress['shipping']['address_2']) . "',"
            . " shipping_complemento      = '" . $this->db->escape($orderAddress['shipping']['complemento']) . "',"
            . " shipping_city             = '" . $this->db->escape($orderAddress['shipping']['city']) . "',"
            . " shipping_postcode         = '" . $this->db->escape($orderAddress['shipping']['postcode']) . "',"
            . " shipping_country          = '" . $this->db->escape($orderAddress['shipping']['dadosPais']['name']) . "',"
            . " shipping_country_id       = '" . (int)$orderAddress['shipping']['dadosPais']['country_id'] . "',"
            . " shipping_zone             = '" . $this->db->escape($orderAddress['shipping']['uf']) . "',"
            . " shipping_zone_id          = '" . (int)$orderAddress['shipping']['state_zone_id'] . "',"
            . " shipping_tipo_logradouro  = '" . $this->db->escape($orderAddress['shipping']['tipo_logradouro']) . "',"
            . " shipping_logradouro       = '" . $this->db->escape($orderAddress['shipping']['logradouro']) . "',"
            . " shipping_codigo_municipio = '" . (int)$orderAddress['shipping']['codigo_municipio'] . "',"

            . $addSql
            . " date_modified             = NOW()"

            . " where order_id =             " . $this->db->escape($orderInfo['order_id'])
        );
    }

    public function updateOrderMktComission($order_id, $mkt_comission_value)
    {
        $this->db->query(
            "UPDATE `oc_order` SET"
            . " mkt_comission_total = " . $this->db->escape($mkt_comission_value)
            . " where order_id = " . $this->db->escape($order_id)
        );
    }

    public function getModuleName($code, $type)
    {
        if (!$code) return '';

        if ($this->language->has($type . '/' . $code)) {
            $this->language->load($type . '/' . $code);

            return $this->language->get('heading_title');
        } else {
            return '';
        }

    }

    public function getMktOrder($cod_mkt, $marketplace)
    {
        return $this->db->query(
            "SELECT
				mo.*, o.nao_confirmado, ot.`value` as total
			FROM `oc_marketplace_order` mo
			inner join oc_order o on (o.order_id = mo.order_id)
			left join oc_order_total ot on (ot.order_id = mo.order_id and ot.code = 'total')
			WHERE mo.mktOrderId = '" . $cod_mkt . "'"
            . " and o.mkt_integracao_tipo = " . $marketplace)->row;
    }

    public function getMktOrderStatus($order_id)
    {
        return $this->db->query(
            "Select *
      FROM oc_marketplace_order_status
      Where order_id = $order_id
      order by marketplace_order_status_id"
        )->rows;
    }

    public function getLastMktOrderStatus($order_id)
    {
        $sql = "Select *
      FROM oc_marketplace_order_status
      Where order_id = $order_id
      order by marketplace_order_status_id 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 inErrorMktOrderStatus($order_id, $situacaoErro, $typeMarketplace)
    {
        if (is_null($typeMarketplace)) {
            $typeMarketplace = Type_MarketPlace::Tp_Mercadolivre;
        }

        $sql = "SELECT mos.* FROM oc_marketplace_order_status mos
        INNER JOIN oc_marketplace_order mo
        ON (mos.order_id = mo.order_id) AND (mo.tipo_integracao = $typeMarketplace)
        WHERE mos.order_id = $order_id AND mos.tipo_situacao = $situacaoErro AND mos.msg_erro != '' AND NOT mos.msg_erro IS NULL
        ORDER BY mos.marketplace_order_status_id";

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

        if (count($err) > 0) {
            return true;
        } else {
            return false;
        }
    }

    public function isVinculedMktOrderStatus($order_history_id)
    {
        //verifica se existe um status vinculado ao histórico
        $reg = $this->db->query(
            "Select *
      FROM oc_marketplace_order_status
      Where order_history_id = $order_history_id"
        )->rows;
        if (count($reg) > 0) {
            return true;
        } else {
            return false;
        }
    }

    protected function addMktOrder($data)
    {
        $this->load->helper('Ftr_sql');
        $sqlInsert = Ftr_sql::generateInsert('oc_marketplace_order',
            ['order_id', 'feedback', 'mktOrderId', 'order_product_id', 'tipo_integracao', 'shipping_code', 'origem_captura']);

        $infos = array();
        $infos[] = $data['order_id'];
        $infos[] = isset($data['feedback']) ? $data['feedback'] : 0;
        $infos[] = $data['mktOrderId'];
        $infos[] = isset($data['order_product_id']) ? $data['order_product_id'] : null;
        $infos[] = $data['tipo_integracao'];
        $infos[] = isset($data['shipping_code']) ? $data['shipping_code'] : null;
        $infos[] = isset($data['origem_captura']) ? $data['origem_captura'] : null;

        $this->db->executeSql($sqlInsert, $infos);
    }

    public function updateOrderHistory($order_id, $order_status_id, $Comment)
    {
        $this->load->dmodel('proccess/order_to_situation');
        $this->dmodel_proccess_order_to_situation->updateHistory((int)$order_id, (int)$order_status_id);
        $this->db->query("INSERT INTO oc_order_history SET order_id = '" . (int)$order_id . "', order_status_id = '" . (int)$order_status_id . "', notify = '1', comment = '" . $Comment . "', date_added = current_timestamp(2)");
        $order_history_id = $this->db->getLastId();
        $this->db->query("UPDATE `oc_order` SET order_status_id = '" . (int)$order_status_id . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");

        return $order_history_id;
    }


    public function addOrderProduct($order_id, $product, $addtionalFields = Array(), $addtionalValues = Array())
    {

        $addSql = "";
        foreach ($addtionalFields as $key => $field) {
            $addSql .= $field . " = " . $addtionalValues[$key] . " ,";
        }

        $this->db->query("INSERT INTO oc_order_product SET"
            . " order_id = '" . (int)$order_id . "',"
            . $addSql
            . " product_id = '" . (int)$product['product_id'] . "',"
            . " 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 = 0,"
            . " marketId = '" . $this->db->escape($product['marketId']) . "',"
            . " mkt_comission='" . $this->db->escape($product['mkt_comission']) . "', "
            . " mkt_variation_id='" . $this->db->escape($product['mkt_variation_id']) . "' "
        );
        $order_product_id = $this->db->getLastId();

        return $order_product_id;
    }

    public function updateTipoStatusPedido($order_id, $mktStatus)
    {
        return $this->db->query("update oc_order set mkt_order_status = $mktStatus where order_id = $order_id");
    }

    public function addOrderTotal($order_id, $totals)
    {
        foreach ($totals as $total) {
            $this->db->query("INSERT INTO oc_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'] . "'");
        }

    }

    public function updateOrderTotal($order_id, $totals)
    {
        foreach ($totals as $total) {
            $this->db->query("UPDATE oc_order_total SET"
                . " text = '" . $this->db->escape($total['text']) . "',"
                . " title = '" . $this->db->escape($total['title']) . "',"
                . " `value` = '" . (float)$total['value'] . "'"
                . " where order_id = " . (int)$order_id
                . " and code = '" . $this->db->escape($total['code']) . "'"
            );
        }

    }

    public function getMktTotalOrder()
    {
        $this->load->type('tipocomum');
        //conta novos pedidos e pedidos atualizados
        $query = $this->db->query(
            "SELECT
          SUM(if(o.date_modified > o.date_added,1,0)) total_modified,
          SUM(if(o.date_added >= DATE(current_timestamp),1,0)) total_new
          from oc_order o
          WHERE  ((o.date_modified > o.date_added)
          OR (o.date_added >= DATE(CURRENT_TIMESTAMP)))
          and (mkt_integracao_tipo is not null)");

        $retorno = array(
            "total_new" => $query->row['total_new'],
            "total_modified" => $query->row['total_modified']
        );
        return $retorno;
    }


    public function addMktOrderStatus($data)
    {
        $this->load->helper('Ftr_sql');
        $this->load->type('tipocomum');
        try {
            $fieldList = array();
            $fields = array();

            $fieldList = array('order_id', 'order_history_id', 'mktStatus', 'mktStatus_envio', 'tipo_origem', 'tipo_situacao', 'msg_erro');

            $fields[] = (int)$data['order_id'];
            $fields[] = isset($data['order_history_id']) ? (int)$data['order_history_id'] : null;
            $fields[] = isset($data['mktStatus']) ? isset($data['mktStatus']['status'])? $data['mktStatus']['status'] :$data['mktStatus'] : null;
            $fields[] = isset($data['mktStatus_envio']) ? $data['mktStatus_envio'] : null;
            $fields[] = isset($data['tipo_origem']) ? $data['tipo_origem'] : null;
            $fields[] = isset($data['tipo_situacao']) ? $data['tipo_situacao'] : null;
            $fields[] = isset($data['msg_erro']) ? $data['msg_erro'] : null;

            $sqlInsert = Ftr_sql::generateInsert('oc_marketplace_order_status', $fieldList);
            $this->db->executeSql($sqlInsert, $fields);

        } catch (Exception $e) {
            throw new exception('Erro ao inserir registro de histórico do marketplace.' . $e->getMessage());
        }
    }


    public function updateMktOrderStatus($data)
    {
        $sql = "UPDATE oc_marketplace_order_status
                set order_id=" . $data['order_id'] . ",
                mktStatus='" . $data['mktStatus'] . "',
                tipo_origem=" . $data['tipo_origem'] . ",
                tipo_situacao=" . $data['tipo_situacao'] . ",
                msg_erro='" . $this->db->escape($data['msg_erro']) . "'" .
            " WHERE order_history_id=" . $data['order_history_id'];

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

    public function removeMktOrderStatus($order_history_id)
    {
        $this->db->query("DELETE from oc_marketplace_order_status where order_history_id=$order_history_id");
    }

    public function inStatusMkt($order_history_id, $marketplace)
    {

        $sql = "SELECT * FROM oc_order_history oh WHERE oh.order_status_id IN(";

        switch ($marketplace) {
            case Type_MarketPlace::Tp_SkyHub :
                $sql .= "SELECT `value` FROM oc_setting
                            WHERE `group` = 'skyhub' AND `key` like 'skyhub_order_status%'";
                break;
            case Type_MarketPlace::Tp_Mercadolivre :
                $sql .= "SELECT oc_status FROM oc_mercadolivre_status";
                break;
            case Type_MarketPlace::Tp_Galeriapage :
                $sql .= "SELECT `value` FROM oc_setting
                            WHERE `group` = 'galeriapage' AND `key` like 'gp_order_status%'";
                break;
            case Type_MarketPlace::Tp_Shopee:
                $sql .= "SELECT `value` FROM oc_setting
                            WHERE `group` = 'shopee' AND `key` like 'shopee_order_status%'";
                break;
        }

        $sql .= ") AND  oh.order_history_id = $order_history_id";

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

        if (count($retorno) > 0) {
            return true;
        } else {
            return false;
        }

    }

    public function existsMktOrderStatus($order_id, $mktStatus, $origem = false)
    {
        $filtroOrigem = ($origem === false ? '' : ' AND tipo_origem = ' . $origem);

        $sql = "SELECT *
                FROM oc_marketplace_order_status
                WHERE order_id = $order_id AND mktStatus = '$mktStatus'" . $filtroOrigem;
        $retorno = $this->db->query($sql)->rows;

        if (count($retorno) > 0) {
            return true;
        } else {
            return false;
        }
    }

    public function saveMarketplaceLog()
    {
        $this->load->dmodel('log/log');

        if (isset($this->marketplace->logArray)) {
            foreach ($this->marketplace->logArray as $log) {
                $this->dmodel_log_log->saveLogDB($log);
            }
        }

        $this->marketplace->logArray = array();
    }

    public function getOrderAddress($order)
    {
        $this->load->dmodel('localisation/zone');
        $this->load->dmodel('localisation/country');

        $shippingAddress = $order->getBuyer()->getSendAddress();
        $billingAddress = $order->getBuyer()->getBillingAddress();

        $adressesData = array('billing' => $billingAddress, 'shipping' => $shippingAddress);

        $data = array();

        foreach ($adressesData as $key => $addressData) {
            try {
                //Carregando informações padrões, caso na função de CEP não encontre os dados...
                $data[$key]['state_zone_id'] = $this->dmodel_localisation_zone->getZoneId($addressData->getPais(), $addressData->getUf());

                if (!$data[$key]['state_zone_id']) {
                    $data[$key]['state_zone_id'] = $this->config->get('config_zone_id');
                }
                $data[$key]['dadosPais'] = $this->dmodel_localisation_country->getCountryFromCode($addressData->getPais());

                if (!$data[$key]['dadosPais']) {
                    $data[$key]['dadosPais'] = $this->dmodel_localisation_country->getCountryFromCode(Type_PaisId::Tp_Brasil);
                }

                $data[$key]['city'] = $addressData->getCidade();
                $data[$key]['address_2'] = substr($addressData->getBairro(), 0, 60); //cortar nos 60caracteres o nome do bairro
                $data[$key]['tipo_logradouro'] = $addressData->getLogradouroTipo();
                $data[$key]['address_1'] = $addressData->getLogradouro();
                $data[$key]['logradouro'] = $addressData->getLogradouro();
                $data[$key]['numero'] = preg_replace("/[^0-9]/", "", $addressData->getNumero()); //retirar caracteres que nao sao numericos do NUMERO da residencia
                $data[$key]['postcode'] = $addressData->getPostCode();
                $data[$key]['complemento'] = $addressData->getComplemento();
                $data[$key]['uf'] = $addressData->getUf();
                $data[$key]['codigo_municipio'] = null;

                if ((utf8_strlen($addressData->getPostCode()) == 8) && ($addressData->getPostCode() != '00000000')) {
                    $result = $this->cep->getCep($addressData->getPostCode());
                    if ($result) {
                        $this->load->dmodel('localisation/zone');
                        if (!empty(trim($result['uf']))) {
                            $data[$key]['state_zone_id'] = $this->dmodel_localisation_zone->getZoneIdByUf($result['uf']);
                            $data[$key]['uf'] = $result['uf'];
                        }

                        if (!empty(trim($result['cidade']))) {
                            $data[$key]['city'] = ucwords(mb_strtolower($result['cidade']));
                        }

                        if (!empty(trim($result['logradouro_completo']))) {
                            $data[$key]['address_1'] = ucwords(mb_strtolower($result['logradouro_completo']));
                        }

                        if (!empty(trim($result['tipo_logradouro']))) {
                            $data[$key]['tipo_logradouro'] = ucwords(mb_strtolower($result['tipo_logradouro']));
                        }

                        if (!empty(trim($result['logradouro']))) {
                            $data[$key]['logradouro'] = ucwords(mb_strtolower($result['logradouro']));
                        }

                        if (!empty(trim($result['bairro']))) {
                            $data[$key]['address_2'] = ucwords(mb_strtolower($result['bairro']));
                        }

                        $data[$key]['codigo_municipio'] = $result['codigo_municipio'];
                    }
                }
            } catch (Exception $e) {
                $this->mercadolivre->setErrorML('Erro ao buscar cep pedido mercado livre: ' . $order->getMktId());
            }
        }

        return $data;
    }

    public function getOrderCustomerData($order, $orderAddress)
    {
        $genero = '';

        if (!is_null($order->getBuyer()->getGenero())) {
            if ($order->getBuyer()->getGenero() == Type_Genero::TpGen_Fem) {
                $genero = 'f';
            } elseif (($order->getBuyer()->getGenero() == Type_Genero::TpGen_Masc)) {
                $genero = 'm';
            }
        }

        $retorno = array(
            'email' => $order->getBuyer()->getEmail(),
            'firstname' => $order->getBuyer()->getFirstName(),
            'lastname' => $order->getBuyer()->getLastName(),
            'telephone' => $order->getBuyer()->getPhone(),
            'celular' => $order->getBuyer()->getCelular(),
            'cpf' => ($order->getBuyer()->getTipoPessoa() == Type_Pessoa::TpPess_fisica) ? $order->getBuyer()->getDocumento() : '',
            'cnpj' => ($order->getBuyer()->getTipoPessoa() == Type_Pessoa::TpPess_Juridica) ? $order->getBuyer()->getDocumento() : '',
            'razao_social' => $order->getBuyer()->getFirstName() . " " . $order->getBuyer()->getFirstName(),
            'mkt_integracao_tipo' => $order->getMktIntegration(),
            'mkt_tipo' => $order->getMkTipo(),
            'adresses' => $orderAddress,
            'mkt_buyer_id' => $order->getBuyer()->getMktBuyerId(),
            'genero' => $genero,
            'birth_day' => (is_null($order->getBuyer()->getDateBirth())) ? '' : $order->getBuyer()->getDateBirth()->format('Y-m-d'),
        );

        return $retorno;
    }

    public function getOrderCustomer($order, $orderAddress)
    {
        $this->load->dmodel('account/customer');

        $result = array();

        if (!empty($order->getBuyer()->getDocumento())) {
            $customerBD = $this->dmodel_account_customer->getTotalCustomersByCnpjCpf($order->getBuyer()->getDocumento());
        } elseif (!empty($order->getBuyer()->getEmail())) {
            $customerBD = $this->dmodel_account_customer->getCustomerByEmail($order->getBuyer()->getEmail());
        } else {
            $customerBD = false;
        }

        // Como o meli desce pedidos em aberto sem documento, verificaremos pelo mktBuyerId
        if (($order->getMktIntegration() == Type_MarketPlace::Tp_Mercadolivre) &&
            (!$customerBD) &&
            (!empty($order->getBuyer()->getMktBuyerId()))
              OR (($order->getMktIntegration() == Type_MarketPlace::Tp_Mercadolivre)
              and ($order->getBuyer()->getMktBuyerId() != $customerBD['mkt_buyer_id']))) {
            $customerBD = $this->dmodel_account_customer->getCustomerByMktBuyerId($order->getBuyer()->getMktBuyerId());

            // Se achou o cliente pelo buyer id mas o documento está diferente...
            // criar um novo cadastro com o documento que veio no pedido
            // pedido dever ser vinculado ao cadastro novo
            if ($customerBD && !empty($order->getBuyer()->getDocumento())) {
                $dataCustomer = $this->getOrderCustomerData($order, $orderAddress);
                $newCustomerId = $this->dmodel_account_customer->createOCCustomer($dataCustomer);
                $customerBD = $this->dmodel_account_customer->getCustomer($newCustomerId);
            }
        }

        if(($order->getMktIntegration() == Type_MarketPlace::Tp_Shopee) &&
          (!$customerBD) &&
          (!empty($order->getBuyer()->getNick()))
          OR (($order->getMktIntegration() == Type_MarketPlace::Tp_Shopee)
               and ($order->getBuyer()->getMktBuyerId() != $customerBD['mkt_buyer_id']))){

          //Devido posteriormente não usamos o ID do shopee ele busca o nick para que possa atualizar o ID caso não o tenha
          $customerBD = $this->dmodel_account_customer->getCustomerByMktNick($order->getBuyer()->getNick());
          //Caso não encontrado pelo Nick ele busca pelo ID
          if(!$customerBD){
            $customerBD = $this->dmodel_account_customer->getCustomerByMktBuyerId($order->getBuyer()->getMktBuyerId());
          }
          //Caso não encontrado por nenhuma das opções ele cadastra um novo.
          if ($customerBD && ((strrpos($customerBD['lastname'], $order->getBuyer()->getNick()) === false) AND ($order->getBuyer()->getMktBuyerId() != (int) $customerBD['mkt_buyer_id']))  ) {
            $dataCustomer = $this->getOrderCustomerData($order, $orderAddress);
            $newCustomerId = $this->dmodel_account_customer->createOCCustomer($dataCustomer);
            $customerBD = $this->dmodel_account_customer->getCustomer($newCustomerId);
          }
        }

        if ($customerBD) {
            $result = $customerBD;

            /*caso o cliente marketplace ja exista no ecommerce atualiza o cadastro salvando o buyer_id
              mkt_buyer_id usado hoje somente no envio de mensagem do mercado livre, caso seja necessario utilizar para outros marketplaces
              deve se alterar onde irá salvar essa informação. */
            if ((is_null($customerBD['mkt_buyer_id']) || empty($customerBD['mkt_buyer_id'])) && !empty($order->getBuyer()->getMktBuyerId())) {
                $this->dmodel_account_customer->updateMktBuyerId($order->getBuyer()->getMktBuyerId(), $customerBD['customer_id']);
            }
        } else {
            $dataCustomer = $this->getOrderCustomerData($order, $orderAddress);
            $result['customer_id'] = $this->dmodel_account_customer->createOCCustomer($dataCustomer);
            $result['customer_group_id'] = $this->config->get('config_customer_group_id');
        }

        $marketplaceStr = strtolower(Type_MarketPlace::getNickFromConstValue($order->getMktIntegration()));
        if ($this->config->get($marketplaceStr . '_table_price')) {
            $result['customer_group_id'] = $this->config->get($marketplaceStr . '_table_price');
        }

        return $result;
    }


    public function getMktBrinde($productID){
        $this->load->dmodel('catalog/product');
        $brinde = $this->dmodel_catalog_product->getProductBrinde($productID, !$this->config->get('config_stock_checkout'));
        $brindeMkt =  array();

        if($brinde){
            $brindeMkt = array(
                'product_id'        => $brinde['product_brind_id'],
                'name'              => $brinde['name'],
                'model'             => $brinde['model'],
                'quantity'          => 1,
                'price'             => 0.01,
                'total'             => 0.01,
                'marketId'          => "",
                'mkt_comission'     => 0,
                'mkt_variation_id'  => "",
                'brinde_product_id' => $brinde['product_id'],
                'brinde_tipo'       => Type_ProdutoBrinde::Tp_Brinde,
            );
        }

        return $brindeMkt;

    }
}