<?php

final class DModelSaleOrder extends Model
{

    public function getTrackingEvent($tracking_code)
    {
        $sql = "SELECT * FROM " . DB_PREFIX . "order_tracking_event otv "
            . "INNER JOIN oc_order_tracking ot ON (otv.order_tracking_id = ot.order_tracking_id)"
            . "WHERE ot.tracking_code = '" . $tracking_code . "' ORDER BY otv.order_tracking_event_date DESC";

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

    public function getNfeOrder($order_id)
    {

        $sql = "SELECT * FROM " . DB_PREFIX . "order_nfe WHERE order_id = " . $order_id;
        $query = $this->db->query($sql);
        if ($query->rows) {
            return $query->row;
        }
        return false;

    }

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

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


    public function confirm($order_id, $order_status_id, $comment = '', $client_notify = null)
    {
        if ($this->config->get('listapresente_status') && $this->config->get('futuralistapresente_cod_status_completo') == $order_status_id) {

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

            $this->load->model('futura/listapresente');
            $lista = $this->model_futura_listapresente->getListaByPedido($order_id);
            if ($lista) {
                // Alterar o status dos itens quando o pedido for colocar como completo.
                $itens = $this->model_futura_listapresente->GetItensPedido($order_id);
                if ($itens) {
                    $this->model_futura_listapresente->updateStatusItemPedido($itens, $order_status_id);
                }

                $order_info = $this->getOrder($order_id);
                if ($order_info) {
                    $this->load->model('localisation/language');

                    $language_info = $this->model_localisation_language->getLanguage($order_info['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 = '';
                    }

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


                    // 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");

                    // Send out order confirmation mail
                    $language = new Language($language_directory);
                    $language->load($language_filename);
                    $language->load('mail/order');

                    $order_status_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_status WHERE order_status_id = '" . (int)$order_status_id . "' AND language_id = '" . (int)$order_info['language_id'] . "'");

                    if ($order_status_query->num_rows) {
                        $order_status = $order_status_query->row['name'];
                    } else {
                        $order_status = '';
                    }

                    $subject = html_entity_decode(sprintf($language->get('text_new_subject'), $order_info['store_name'], $order_id), ENT_QUOTES, 'UTF-8');

                    // HTML Mail
                    $template = new Template();

                    $template->data['title'] = sprintf($language->get('text_new_subject'), html_entity_decode($order_info['store_name'], ENT_QUOTES, 'UTF-8'), $order_id);

                    $template->data['text_greeting'] = sprintf($language->get('text_new_greeting'), html_entity_decode($order_info['store_name'], ENT_QUOTES, 'UTF-8'));
                    $template->data['text_link'] = $language->get('text_new_link');
                    $template->data['text_order_detail'] = $language->get('text_new_order_detail');
                    $template->data['text_instruction'] = $language->get('text_new_instruction');
                    $template->data['text_order_id'] = $language->get('text_new_order_id');
                    $template->data['text_date_added'] = $language->get('text_new_date_added');
                    $template->data['text_payment_method'] = $language->get('text_new_payment_method');
                    $template->data['text_shipping_method'] = $language->get('text_new_shipping_method');
                    $template->data['text_email'] = $language->get('text_new_email');
                    $template->data['text_telephone'] = $language->get('text_new_telephone');
                    $template->data['text_ip'] = $language->get('text_new_ip');
                    $template->data['text_payment_address'] = $language->get('text_new_payment_address');
                    $template->data['text_shipping_address'] = $language->get('text_new_shipping_address');
                    $template->data['text_product'] = $language->get('text_new_product');
                    $template->data['text_model'] = $language->get('text_new_model');
                    $template->data['text_quantity'] = $language->get('text_new_quantity');
                    $template->data['text_price'] = $language->get('text_new_price');
                    $template->data['text_total'] = $language->get('text_new_total');
                    $template->data['text_footer'] = $language->get('text_new_footer');
                    $template->data['text_powered'] = $language->get('text_new_powered');

                    $template->data['logo'] = $this->urlloja->link('', '', 'SSL', false) . 'image/' . $this->config->get('config_logo');
                    $template->data['store_name'] = $order_info['store_name'];
                    $template->data['store_url'] = $order_info['store_url'];
                    $template->data['customer_id'] = $order_info['customer_id'];
                    $template->data['link'] = $order_info['store_url'] . 'index.php?route=account/order/info&order_id=' . $order_id;

                    $template->data['order_id'] = $order_id;
                    $template->data['date_added'] = date($language->get('date_format_short'), strtotime($order_info['date_added']));
                    $template->data['payment_method'] = $order_info['payment_method'];
                    $template->data['shipping_method'] = $order_info['shipping_method'];
                    $template->data['email'] = $order_info['email'];
                    $template->data['telephone'] = $order_info['telephone'];
                    $template->data['ip'] = $order_info['ip'];

                    if ($comment) {
                        $template->data['comment'] = nl2br($comment);
                    } else {
                        $template->data['comment'] = '';
                    }

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

                    $find = array(
                        '{firstname}',
                        '{lastname}',
                        '{company}',
                        '{address_1}',
                        '{address_2}',
                        '{city}',
                        '{postcode}',
                        '{zone}',
                        '{zone_code}',
                        '{country}'
                    );

                    $replace = array(
                        'firstname' => $order_info['payment_firstname'],
                        'lastname' => $order_info['payment_lastname'],
                        'company' => $order_info['payment_company'],
                        'address_1' => $order_info['payment_address_1'],
                        'address_2' => $order_info['payment_address_2'],
                        'city' => $order_info['payment_city'],
                        'postcode' => $order_info['payment_postcode'],
                        'zone' => $order_info['payment_zone'],
                        'zone_code' => $order_info['payment_zone_code'],
                        'country' => $order_info['payment_country']
                    );

                    $template->data['payment_address'] = str_replace(array("\r\n", "\r", "\n"), '<br />', preg_replace(array("/\s\s+/", "/\r\r+/", "/\n\n+/"), '<br />', trim(str_replace($find, $replace, $format))));

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

                    $find = array(
                        '{firstname}',
                        '{lastname}',
                        '{company}',
                        '{address_1}',
                        '{address_2}',
                        '{city}',
                        '{postcode}',
                        '{zone}',
                        '{zone_code}',
                        '{country}'
                    );

                    $replace = array(
                        'firstname' => $order_info['shipping_firstname'],
                        'lastname' => $order_info['shipping_lastname'],
                        'company' => $order_info['shipping_company'],
                        'address_1' => $order_info['shipping_address_1'],
                        'address_2' => $order_info['shipping_address_2'],
                        'city' => $order_info['shipping_city'],
                        'postcode' => $order_info['shipping_postcode'],
                        'zone' => $order_info['shipping_zone'],
                        'zone_code' => $order_info['shipping_zone_code'],
                        'country' => $order_info['shipping_country']
                    );

                    $template->data['shipping_address'] = str_replace(array("\r\n", "\r", "\n"), '<br />', preg_replace(array("/\s\s+/", "/\r\r+/", "/\n\n+/"), '<br />', trim(str_replace($find, $replace, $format))));

                    // Products
                    $template->data['products'] = array();

                    foreach ($order_product_query->rows as $product) {
                        $template->data['products'][] = array(
                            'name' => $product['name'],
                            'model' => $product['model'],
                            'quantity' => $product['quantity'],
                            'price' => $this->currency->format($product['price'], $order_info['currency_code'], $order_info['currency_value']),
                            'total' => $this->currency->format($product['total'], $order_info['currency_code'], $order_info['currency_value'])
                        );
                    }


                    $template->data['totals'] = $order_total_query->rows;

                    // Text Mail
                    $text = sprintf($language->get('text_new_greeting'), html_entity_decode($order_info['store_name'], ENT_QUOTES, 'UTF-8')) . "\n\n";
                    $text .= $language->get('text_new_order_id') . ' ' . $order_id . "\n";
                    $text .= $language->get('text_new_date_added') . ' ' . date($language->get('date_format_short'), strtotime($order_info['date_added'])) . "\n";
                    $text .= $language->get('text_new_order_status') . ' ' . $order_status . "\n\n";

                    if ($comment && $client_notify) {
                        $text .= $language->get('text_new_instruction') . "\n\n";
                        $text .= $comment . "\n\n";
                    }

                    // Products
                    $text .= $language->get('text_new_products') . "\n";

                    foreach ($order_product_query->rows as $product) {
                        $text .= $product['quantity'] . 'x ' . $product['name'] . ' (' . $product['model'] . ') ' . html_entity_decode($this->currency->format($product['total'], $order_info['currency_code'], $order_info['currency_value']), ENT_NOQUOTES, 'UTF-8') . "\n";

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

                        foreach ($order_option_query->rows as $option) {
                            $text .= chr(9) . '-' . $option['name'] . ' ' . (utf8_strlen($option['value']) > 20 ? utf8_substr($option['value'], 0, 20) . '..' : $option['value']) . "\n";
                        }
                    }

                    $text .= "\n";

                    $text .= $language->get('text_new_order_total') . "\n";

                    foreach ($order_total_query->rows as $total) {
                        $text .= $total['title'] . ': ' . html_entity_decode($total['text'], ENT_NOQUOTES, 'UTF-8') . "\n";
                    }

                    $text .= "\n";

                    if ($order_info['customer_id']) {
                        $text .= $language->get('text_new_link') . "\n";
                        $text .= $order_info['store_url'] . 'index.php?route=account/order/info&order_id=' . $order_id . "\n\n";
                    }

                    // Comment
                    if ($order_info['comment']) {
                        $text .= $language->get('text_new_comment') . "\n\n";
                        $text .= $order_info['comment'] . "\n\n";
                    }

                    $text .= $language->get('text_new_footer') . "\n\n";

                    // Eviando email personalizado para o comprador do presente

                    if ($lista) {
                        $template->data['lista']['nome'] = $lista['nome'];
                        $template->data['lista']['data'] = implode("/", array_reverse(explode("-", $lista['evento'])));
                        $template->data['lista']['cliente'] = $lista['cliente'];
                        $template->data['lista']['frase'] = $lista['frase'];
                    } else {
                        $template->data['lista']['nome'] = '---';
                        $template->data['lista']['data'] = '---';
                        $template->data['lista']['cliente'] = '---';
                        $template->data['lista']['frase'] = '---';
                    }

                    $template->data['header_background_color'] = $this->config->get('wg24_header_bg_color');


                    // Eviando email para o dono da lista
                    // Buscando dados do dono da lista
                    $this->load->dmodel('account/customer');
                    $cliente = $this->dmodel_account_customer->getCustomer($lista['cliente_id']);

                    $template->data['customer_name'] = $order_info['firstname'] . ' ' . $order_info['lastname'];
                    $template->data['customer_email'] = $order_info['email'];
                    $template->data['customer_fone'] = $order_info['telephone'];

                    if (file_exists(DIR_TEMPLATE . $this->config->get('config_template') . '/template/mail/order_p_cli.tpl')) {
                        $html = $template->fetch($this->config->get('config_template') . '/template/mail/order_p_cli.tpl');
                    } else {
                        $html = $template->fetch('mail/order_p_cli.tpl');
                    }

                    $subject = $order_info['firstname'] . ' ' . $order_info['lastname'] . ' comprou um presente para você';

                    $mail = new Mail();
                    $mail->protocol = $this->config->get('config_mail_protocol');
                    $mail->parameter = $this->config->get('config_mail_parameter');
                    $mail->hostname = $this->config->get('config_smtp_host');
                    $mail->username = $this->config->get('config_smtp_username');
                    $mail->password = $this->config->get('config_smtp_password');
                    $mail->port = $this->config->get('config_smtp_port');
                    $mail->timeout = $this->config->get('config_smtp_timeout');
                    $mail->setTo($cliente['email']);
                    $mail->setFrom($this->config->get('config_email'));
                    $mail->setSender($order_info['store_name']);
                    $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
                    $mail->setHtml($html);
                    $mail->setText(html_entity_decode($text, ENT_QUOTES, 'UTF-8'));
                    $mail->send();
                }
            }
        }
    }

    public function getShippingVolumes($order_id)
    {
        $sql = "SELECT * FROM " . DB_PREFIX . "order_shipping_volume osv "
            . "WHERE osv.order_id = '" . $order_id . "'";

        $query = $this->db->query($sql);
        if ($query->rows) {
            return $query->rows;
        } else {
            return false;
        }
    }

    public function getShippingVolumeProducts($order_id)
    {
        $sql = "SELECT osvp.*, pd.name, op.price "
            . "FROM " . DB_PREFIX . "order_shipping_volume_product osvp "
            . "INNER JOIN " . DB_PREFIX . "order_shipping_volume osv on osvp.order_shipping_volume_id = osv.order_shipping_volume_id "
            . "INNER JOIN " . DB_PREFIX . "order o on osv.order_id = o.order_id "
            . "INNER JOIN " . DB_PREFIX . "order_product op on o.order_id = op.order_id and osvp.product_id = op.product_id "
            . "INNER JOIN " . DB_PREFIX . "product_description pd on osvp.product_id = pd.product_id "
            . "WHERE o.order_id = '" . $order_id . "'";

        $query = $this->db->query($sql);
        if ($query->rows) {
            return $query->rows;
        } else {
            return false;
        }
    }

    public function setOrderHistoryWhatsapp($data){
        $this->db->query("UPDATE oc_order_history 
                          SET message_whatsapp = '".$this->db->escape($data['message_whatsapp'])."', 
                              notify_whatsapp='".$this->db->escape($data['notify_whatsapp'])."'
                          WHERE order_history_id = ".$data['order_history_id']);
    }


}