<?php

class ControllerReportProductSalesForProductAttribute extends Controller {
    private $results;
    private $product_total;
    private $filter_data;

    public function index()
    {
        $this->load->language('report/product_sales_for_product_attribute');
        $this->document->setTitle($this->language->get('heading_title'));

        self::filterData();

        $config_language_id = (int)$this->config->get('config_language_id');
        $product_attributes = $this->db->query("select attribute_id,name from " . DB_PREFIX . "attribute_description WHERE language_id = $config_language_id");

        $data['product_attributes'] = array();
        if ($product_attributes->num_rows > 0) {
            $data['product_attributes'] = $product_attributes->rows;
        }

        $product_option_values = $this->db->query("select option_value_id,name from " . DB_PREFIX . "option_value_description WHERE language_id = $config_language_id");

        $data['product_options'] = array();
        if ($product_option_values->num_rows > 0) {
            $data['product_options'] = $product_option_values->rows;
        }

        $zones = $this->db->query("SELECT z.* 
			FROM ps_zone AS z 
			WHERE z.country_id = 215 
			AND z.`status` = 1"
        );

        $data['zones'] = array();
        if ($zones->num_rows > 0) {
            $data['zones'] = $zones->rows;
        }

        $this->load->model('catalog/category');
        $categories = $this->model_catalog_category->getSubCategories();

        if (isset($categories)) {
            $categories = $this->model_catalog_category->getSubCategories();
            $data['categories'] = $categories;
            foreach ($categories as $key => $value) {
                $data['categories'] += $this->model_catalog_category->getSubCategories($key);
            }
            unset($categories);
        }
        $data['products'] = array();
        $total_q = $total_t = 0;
        foreach ($this->results as $result) {
            $result_name = '';
            if (isset($result['name'])) {
                $result_name = $result['name'];
            }

            $result_option_value_name = '';
            if (isset($result['option_value_name'])) {
                $result_option_value_name = $result['option_value_name'];
            }
            $data['products'][] = array(

                'name' => $result_name,
                'option_value_name' => $result_option_value_name,
                'model' => $result['model'],

                'quantity' => $result['quantitys'],
                'total' => $this->currency->format($result['totals'], $this->config->get('config_currency'))
            );
            $total_q = $total_q + $result['quantitys'];
            $total_t = $total_t + $result['totals'];
        }

        $data['heading_title'] = $this->language->get('heading_title');
        $total_t = $this->currency->format($total_t, $this->config->get('config_currency'));
        $data['total_quantities'] = $total_q;
        $data['total_money'] = $total_t;

        $data['text_list'] = $this->language->get('text_list');
        $data['text_no_results'] = $this->language->get('text_no_results');
        $data['text_confirm'] = $this->language->get('text_confirm');
        $data['text_all_status'] = $this->language->get('text_all_status');

        $data['column_name'] = $this->language->get('column_name');
        $data['column_model'] = $this->language->get('column_model');
        $data['column_quantity'] = $this->language->get('column_quantity');
        $data['column_total'] = $this->language->get('column_total');

        $data['entry_date_start'] = $this->language->get('entry_date_start');
        $data['entry_date_end'] = $this->language->get('entry_date_end');
        $data['entry_status'] = $this->language->get('entry_status');
        $data['entry_product_model'] = $this->language->get('entry_product_model');
        $data['entry_sort_order'] = $this->language->get('entry_sort_order');

        $data['button_filter'] = $this->language->get('button_filter');

        $data['token'] = $this->session->data['token'];

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

        $data['order_statuses'] = $this->model_localisation_order_status->getOrderStatuses();

        $url = '';

        if (isset($this->request->get['filter_date_start'])) {
            $url .= '&filter_date_start=' . $this->request->get['filter_date_start'];
        }

        if (isset($this->request->get['filter_date_end'])) {
            $url .= '&filter_date_end=' . $this->request->get['filter_date_end'];
        }

        if (isset($this->request->get['filter_order_status_id'])) {
            $url .= '&filter_order_status_id=' . $this->request->get['filter_order_status_id'];
        }

        if (isset($this->request->get['filter_attribute'])) {
            $url .= '&filter_attribute=' . $this->request->get['filter_attribute'];
        }
        if (isset($this->request->get['filter_category'])) {
            $url .= '&filter_category=' . $this->request->get['filter_category'];
        }
        if (isset($this->request->get['filter_option'])) {
            $url .= '&filter_option=' . $this->request->get['filter_option'];
        }
        if (isset($this->request->get['filter_product_model'])) {
            $url .= '&filter_product_model=' . $this->request->get['filter_product_model'];
        }
        if (isset($this->request->get['filter_sort_order'])) {
            $url .= '&filter_sort_order=' . $this->request->get['filter_sort_order'];
        }

        if (isset($this->request->get['page'])) {
            $page = $this->request->get['page'];
        } else {
            $page = 1;
        }

        $pagination = new Pagination();
        $pagination->total = $this->product_total;
        $pagination->page = $page;
        $pagination->limit = $this->config->get('config_limit_admin');
        $pagination->url = $this->url->link('report/product_sales_for_product_attribute', 'token=' . $this->session->data['token'] . $url . '&page={page}', true);
        $data['breadcrumbs'] = array();

        $data['breadcrumbs'][] = array(
            'text' => $this->language->get('text_home'),
            'href' => $this->url->link('common/dashboard', 'token=' . $this->session->data['token'], true)
        );

        $data['breadcrumbs'][] = array(
            'text' => $this->language->get('heading_title'),
            'href' => $this->url->link('report/product_sales_for_product_attribute', 'token=' . $this->session->data['token'] . $url, true)
        );
        $data['pagination'] = $pagination->render();

        $data['results'] = sprintf($this->language->get('text_pagination'), ($this->product_total) ? (($page - 1) * $this->config->get('config_limit_admin')) + 1 : 0, ((($page - 1) * $this->config->get('config_limit_admin')) > ($this->product_total - $this->config->get('config_limit_admin'))) ? $this->product_total : ((($page - 1) * $this->config->get('config_limit_admin')) + $this->config->get('config_limit_admin')), $this->product_total, ceil($this->product_total / $this->config->get('config_limit_admin')));

        $data['filter_date_start'] = $this->filter_data['filter_date_start'];
        $data['filter_date_end'] = $this->filter_data['filter_date_end'];
        $data['filter_order_status_id'] = $this->filter_data['filter_order_status_id'];
        $data['filter_product_model'] = $this->filter_data['filter_product_model'];
        $data['filter_zone_id'] = $this->filter_data['filter_zone_id'];

        $filter_attribute_data = array();
        if (strpos($this->filter_data['filter_attribute'], ',') !== false) {
            $filter_attribute_data = explode(',', $this->filter_data['filter_attribute']);
        } else {
            $filter_attribute_data = array($this->filter_data['filter_attribute']);
        }

        $data['filter_attribute'] = $filter_attribute_data;

        $filter_option_data = array();
        if (strpos($this->filter_data['filter_option'], ',') !== false) {
            $filter_option_data = explode(',', $this->filter_data['filter_option']);
        } else {
            $filter_option_data = array($this->filter_data['filter_option']);
        }

        $data['filter_option'] = $filter_option_data;

        $filter_category_data = array();
        if (strpos($this->filter_data['filter_category'], ',') !== false) {
            $filter_category_data = explode(',', $this->filter_data['filter_category']);
        } else {
            $filter_category_data = array($this->filter_data['filter_category']);
        }

        $data['filter_category'] = $filter_category_data;

        $data['filter_sort_order'] = $this->filter_data['filter_sort_order'];

        $data['header'] = $this->load->controller('common/header');
        $data['column_left'] = $this->load->controller('common/column_left');
        $data['footer'] = $this->load->controller('common/footer');

        if (file_exists(DIR_LOCAL_TEMPLATE . 'report/product_sales_for_product_attribute.tpl')) {
            $this->response->setOutput($this->load->view(DIR_LOCAL_TEMPLATE . 'report/product_sales_for_product_attribute', $data));
        } else {
            $this->response->setOutput($this->load->view(DIR_TEMPLATE . 'report/product_sales_for_product_attribute', $data));
        }


    }

    public function excelDownload()
    {
        self::filterData();
        require_once DIR_SYSTEM . 'PHPExcel/Classes/PHPExcel.php';

        $exel = new PHPExcel();
        $exel->getActiveSheet()->setTitle("order-filter");
        $exel->getActiveSheet()->setCellValue('A1', "Name")->getColumnDimension('A')->setAutoSize(true);
        $exel->getActiveSheet()->setCellValue('B1', "Option Value Name")->getColumnDimension('B')->setAutoSize(true);
        $exel->getActiveSheet()->setCellValue('C1', "Model")->getColumnDimension('C')->setAutoSize(true);
        $exel->getActiveSheet()->setCellValue('D1', "Quantity")->getColumnDimension('D')->setAutoSize(true);
        $exel->getActiveSheet()->setCellValue('E1', "Total")->getColumnDimension('E')->setAutoSize(true);
        $exel->getActiveSheet()->setCellValue('F1', "Image")->getColumnDimension('F')->setAutoSize(true);
        $exel->getActiveSheet()->getRowDimension(1)->setRowHeight(27);
        $exel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true);
        $exel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $i = 2;
        foreach ($this->results as $item) {
            $exel->getActiveSheet()->getRowDimension($i)->setRowHeight(22);
            $exel->getActiveSheet()->setCellValue('A' . $i, $item['name'] ?? "-");
            $exel->getActiveSheet()->setCellValue('B' . $i, $item['option_value_name'] ?? "-");
            $exel->getActiveSheet()->setCellValue('C' . $i, $item['model'] ?? "-");
            $exel->getActiveSheet()->setCellValue('D' . $i, $item['quantitys'] ?? "-")
                ->getStyle('D'. $i)
                ->getAlignment()
                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);;
            $exel->getActiveSheet()->setCellValue('E' . $i, $item['totals'] ?? "-");
            $exel->getActiveSheet()
                ->setCellValue('F' . $i, "https://www.deercase.com/image/patterns/".strtoupper($item['model']).".png" ?? "-")
                ->getStyle('F'. $i)
                ->getAlignment()
                ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $i++;
        }
        $exel->setActiveSheetIndex(0);
        $file_name = $this->config->get('config_name') . '-' . @date("Y-m-d-His") . "-orders.xls";

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $file_name . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $objWriter = PHPExcel_IOFactory::createWriter($exel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }

    /*
     * @author Halit DOĞAN <halit44e44@gmail.com>
     * Gönderilen GET parametrelerini uygun biçimde filtreleyip gerekli variables atar.
     */
    private function filterData()
    {
        if (isset($this->request->get['filter_date_start'])) {
            $filter_date_start = $this->request->get['filter_date_start'];
        } else {
            $filter_date_start = '';
        }

        if (isset($this->request->get['filter_date_end'])) {
            $filter_date_end = $this->request->get['filter_date_end'];
        } else {
            $filter_date_end = '';
        }

        if (isset($this->request->get['filter_order_status_id'])) {
            $filter_order_status_id = $this->request->get['filter_order_status_id'];
        } else {
            $filter_order_status_id = 0;
        }

        if (isset($this->request->get['filter_product_model'])) {
            $filter_product_model = $this->request->get['filter_product_model'];
        } else {
            $filter_product_model = "";
        }

        if (isset($this->request->get['filter_zone_id'])) {
            $filter_zone_id = $this->request->get['filter_zone_id'];
        } else {
            $filter_zone_id = "";
        }

        if (isset($this->request->get['filter_attribute'])) {
            $filter_attribute = $this->request->get['filter_attribute'];
        } else {
            $filter_attribute = "";
        }

        if (isset($this->request->get['filter_category'])) {
            $filter_category = $this->request->get['filter_category'];
        } else {
            $filter_category = "";
        }

        if (isset($this->request->get['filter_option'])) {
            $filter_option = $this->request->get['filter_option'];
        } else {
            $filter_option = "";
        }

        if (isset($this->request->get['filter_sort_order'])) {
            $filter_sort_order = $this->request->get['filter_sort_order'];
        } else {
            $filter_sort_order = 0;
        }

        if (isset($this->request->get['page'])) {
            $page = $this->request->get['page'];
        } else {
            $page = 1;
        }

        $url = '';

        if (isset($this->request->get['filter_date_start'])) {
            $url .= '&filter_date_start=' . $this->request->get['filter_date_start'];
        }

        if (isset($this->request->get['filter_date_end'])) {
            $url .= '&filter_date_end=' . $this->request->get['filter_date_end'];
        }

        if (isset($this->request->get['filter_order_status_id'])) {
            $url .= '&filter_order_status_id=' . $this->request->get['filter_order_status_id'];
        }

        if (isset($this->request->get['filter_product_model'])) {
            $url .= '&filter_product_model=' . $this->request->get['filter_product_model'];
        }

        if (isset($this->request->get['filter_zone_id'])) {
            $url .= '&filter_zone_id=' . $this->request->get['filter_zone_id'];
        }

        if (isset($this->request->get['filter_sort_order'])) {
            $url .= '&filter_sort_order=' . $this->request->get['filter_sort_order'];
        }

        if (isset($this->request->get['filter_attribute'])) {
            $url .= '&filter_attribute=' . $this->request->get['filter_attribute'];
        }

        if (isset($this->request->get['filter_category'])) {
            $url .= '&filter_category=' . $this->request->get['filter_category'];
        }

        if (isset($this->request->get['filter_option'])) {
            $url .= '&filter_option=' . $this->request->get['filter_option'];
        }

        if (isset($this->request->get['page'])) {
            $url .= '&page=' . $this->request->get['page'];
        }

        $this->load->model('report/product');

        $this->filter_data = array(
            'filter_date_start' => $filter_date_start,
            'filter_date_end' => $filter_date_end,
            'filter_order_status_id' => $filter_order_status_id,
            'filter_product_model' => $filter_product_model,
            'filter_zone_id' => $filter_zone_id,
            'filter_attribute' => $filter_attribute,
            'filter_category' => $filter_category,
            'filter_option' => $filter_option,
            'filter_sort_order' => $filter_sort_order,
            'start' => ($page - 1) * $this->config->get('config_limit_admin'),
            'limit' => $this->config->get('config_limit_admin')
        );


        $sql = '';
        $sqlattribute = '';
        if (!empty($this->filter_data['filter_attribute'])) {
            $sqlattribute = "pa.attribute_id,ad.name, ";
        }
        if (!empty($this->filter_data['filter_option'])) {
            $sqlattribute .= "ovd.name as option_value_name, ";
        }

        $sql .= "
SELECT o.order_id, o.shipping_zone_id, op.product_id, op.model, op.quantity, o.total, o.order_status_id, o.date_added, " . $sqlattribute . "SUM(op.quantity) AS quantitys, SUM(op.price + (op.tax * op.quantity)) AS totals from " . DB_PREFIX . "order_product op 
LEFT JOIN " . DB_PREFIX . "order o ON (op.order_id = o.order_id) ";

        if (!empty($this->filter_data['filter_attribute'])) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_attribute pa ON (op.product_id = pa.product_id)  ";
            $sql .= " LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (ad.attribute_id = pa.attribute_id)  ";
        }

        if (!empty($this->filter_data['filter_category'])) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category ptc ON (op.product_id = ptc.product_id)";
        }

        if (!empty($this->filter_data['filter_option'])) {
            $sql .= " LEFT JOIN " . DB_PREFIX . "order_option oropt ON (op.order_product_id = oropt.order_product_id) ";
            $sql .= " LEFT JOIN " . DB_PREFIX . "product_option_value pov ON (oropt.product_option_value_id = pov.product_option_value_id) ";
            $sql .= " LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (pov.option_value_id = ovd.option_value_id) ";
        }

        if (!empty($this->filter_data['filter_order_status_id'])) {
            $sql .= " WHERE o.order_status_id = '" . (int)$this->filter_data['filter_order_status_id'] . "'";
        } else {
            $sql .= " WHERE o.order_status_id > '0'";
        }

        if (!empty($this->filter_data['filter_zone_id'])) {
            $sql .= " AND o.shipping_zone_id = '" . (int)$this->filter_data['filter_zone_id'] . "'";
        }

        if (!empty($this->filter_data['filter_attribute'])) {
            $sql .= " AND pa.attribute_id IN (" . $this->db->escape($this->filter_data['filter_attribute']) . ") AND ad.language_id='" . (int)$this->config->get('config_language_id') . "' ";
        }

        if (!empty($this->filter_data['filter_category'])) {
            $sql .= " AND ptc.category_id IN (" . $this->db->escape($this->filter_data['filter_category']) . ") ";
        }


        if (!empty($this->filter_data['filter_option'])) {
            $sql .= " AND pov.option_value_id IN (" . $this->db->escape($this->filter_data['filter_option']) . ") ";
        }

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

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

        if (isset($this->filter_data['filter_product_model']) && !empty($this->filter_data['filter_product_model'])) {
            $sql .= " AND op.model LIKE '%" . $this->db->escape($this->filter_data['filter_product_model']) . "%'";
        }

        $filter_attribute_attribute_id = "";
        if (!empty($this->filter_data['filter_attribute'])) {
            $filter_attribute_attribute_id = " ,pa.attribute_id ";
        }

        $sql .= " GROUP BY op.model" . $filter_attribute_attribute_id . " ORDER BY quantitys DESC";

        $this->results = $this->db->query($sql)->rows;
        $this->product_total = $this->db->query($sql)->num_rows;
    }
}