<?php
class ControllerToolHopishop extends Controller {
    public function index() {
        set_time_limit(0);

        require_once DIR_SYSTEM . 'PHPExcel/Classes/PHPExcel.php';
        $this->load->model('catalog/product');

        $selected_categories = (isset($this->request->get['selected_categories'])) ? $this->request->get['selected_categories'] : null;

        $products = "SELECT * FROM product_for_hopishop";
        
        if($selected_categories!==null) {
            $products.= " WHERE category_id IN  (" . $selected_categories .")";
        }
        
        $products = $this->db->query($products)->rows;

        $img_prefix = ($this->request->server['HTTPS']) ? $this->config->get('config_ssl') . 'image/' : $this->config->get('config_url') . 'image/';
        
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1','Barkod')
            ->setCellValue('B1','Ürün Kodu')
            ->setCellValue('C1','Marka')
            ->setCellValue('D1','Kategori Kodu')
            ->setCellValue('E1','Ürün Adı')
            ->setCellValue('F1','Açıklama')
            ->setCellValue('G1','Satış Fiyatı (KDV Dahil)')
            ->setCellValue('H1','İndirimli Satış Fiyatı (KDV Dahil)')
            ->setCellValue('I1','Ürün Stok Adedi')
            ->setCellValue('J1','KDV Oranı')
            ->setCellValue('K1','Sevkiyat Süresi (Gün)')
            ->setCellValue('L1','Beden')
            ->setCellValue('M1','Renk')
            ->setCellValue('N1','Cinsiyet')
            ->setCellValue('O1','Görsel İsmi 1 (jpg)')
            ->setCellValue('P1','Görsel İsmi 2 (jpg)')
            ->setCellValue('Q1','Görsel İsmi 3 (jpg)')
            ->setCellValue('R1','Görsel İsmi 4 (jpg)')
            ->setCellValue('S1','Görsel İsmi 5 (jpg)');


        $file_name = $this->config->get('config_name').'-HopiShop-'.@date("Y-m-d").".xls";

        $row_i = 2;
        foreach ($products as $product) {

            $product_special = $this->db->query("SELECT price from  ". DB_PREFIX ."product_special
                WHERE product_id = ". $product['product_id'] ."
                AND date_start < NOW()
                AND date_end > NOW()
                LIMIT 1
            ")->row;

            $product_special = ($product_special) ? number_format($this->tax->calculate($product_special['price'], $product['tax_class_id'], $this->config->get('config_tax')), 2) : "";

            $product_attrs = $this->model_catalog_product->getProductAttributes($product['product_id']);
            $material = (isset($product_attrs[0]['attribute'][0])) ? $product_attrs[0]['attribute'][0]['name'] : "";
            $color = (isset($product_attrs[1]['attribute'][0])) ? $product_attrs[1]['attribute'][0]['name'] : "";
            $price =  number_format($this->tax->calculate($product['price'], $product['tax_class_id'], $this->config->get('config_tax')), 2);

            if(strpos($color, $material)!==false) { // renkte materyal adı varsa kaldırır.
                $color = str_replace($material, '', $color);
            }

            if(strpos($color, 'Kordon')!==false) { // renkten kordon yazısı varsa kaldırır.
                $color = str_replace('Kordon', '', $color);
            }

            if(strpos($color, 'Pudra')!==false) { // Hopi Shop'ta 'Pudra Pembe' rengi olmadığı için 'Pudra Pembe' rengi varsa 'Pembe' ye çevirir.
                $color = str_replace('Pudra', '', $color);
            }

            $search  = array("ç","i","ı","ğ","ö","ş","ü");
            $replace = array("Ç","İ","I","Ğ","Ö","Ş","Ü");
            $color   = str_replace($search,$replace,$color);
            $color   = mb_strtoupper($color ,'UTF8'); // renkleri büyük harfe çevirir.
        
            $color = trim($color);

            $phone_model = $this->model_catalog_product->getCategoryByProductId($product['product_id']);
            
            $category_code = "";
            if(strpos($phone_model[0]['name'], 'Telefon Kılıfı')>0) {
                $category_code = 293; // Telefon Kılıfı Kategori Kodu: 293
            } else if(strpos($phone_model[0]['name'], 'Saat Kordonları')>0) {
                $category_code = 290; // Akıllı Saat Kategori Kodu: 290
            }


            $phone_model = str_replace(' Telefon Kılıfı', '', $phone_model[1]['name']);
            $images =  $this->model_catalog_product->getProductImages($product['product_id']);
            $tax_rate = "";
            if($product['tax_class_id'] == '1') {
                $tax_rate = '%18';
            } else if($product['tax_class_id'] == '2') {
                $tax_rate = '%8';
            }

            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'. $row_i, $product['product_id']) 
                ->setCellValue('B'. $row_i, $product['model']) 
                ->setCellValue('C'. $row_i, $product['manufacturer_name']) 
                ->setCellValue('D'. $row_i, $category_code) 
                ->setCellValue('E'. $row_i, $product['name']) 
                ->setCellValue('F'. $row_i, $product['description']) 
                ->setCellValueExplicit('G'. $row_i, $price)
                ->setCellValueExplicit('H'. $row_i, $product_special)
                ->setCellValue('I'. $row_i, $product['quantity'])
                ->setCellValue('J'. $row_i, $tax_rate)
                ->setCellValue('K'. $row_i, 3) 
                ->setCellValue('L'. $row_i, "Standart")
                ->setCellValue('M'. $row_i, $color)
                ->setCellValue('N'. $row_i, "unisex")
                ->setCellValue('O'. $row_i, (isset($product['image'])) ? $img_prefix . $product['image'] : "")
                ->setCellValue('P'. $row_i, (isset($images[0]['image'])) ? $img_prefix.$images[0]['image'] : "")
                ->setCellValue('Q'. $row_i, (isset($images[1]['image'])) ? $img_prefix.$images[1]['image'] : "")
                ->setCellValue('R'. $row_i, (isset($images[2]['image'])) ? $img_prefix.$images[2]['image'] : "")
                ->setCellValue('S'. $row_i, (isset($images[3]['image'])) ? $img_prefix.$images[3]['image'] : "");

            $row_i++;

        }


        $objPHPExcel->getActiveSheet()->setTitle('Simple');

        $objPHPExcel->setActiveSheetIndex(0);

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$file_name.'"');
        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($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
}