<?php
class ControllerCatalogExcel extends Controller{

    public function index()
    {

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

        $data['heading_title'] = 'Excel İle Ürün Yükle';

        $this->document->setTitle($data['heading_title']);

        $data['breadcrumbs'][] = array(
            'href' => 'dd',
            'text' => $data['heading_title'],
        );

        if (($this->request->server['REQUEST_METHOD'] == 'POST') ) {

            $path = DIR_UPLOAD . "XLS/" . $this->request->files['excel_file']['name'];
            $file_path = DIR_UPLOAD . "XLS";
            if (!is_dir($file_path)) {
                mkdir($file_path, 0777, true);
            }
            if(!isset($this->session->data['file_path'])){
                $upload = move_uploaded_file($this->request->files['excel_file']['tmp_name'], $path);
                $this->session->data['file_path'] = $file_path . '/' . $this->request->files['excel_file']['name'];
            }

            $this->excelRead();

        }

        $data['error_warning'] = '';
        $data['success'] = '';


        $data['action'] = $this->url->link('catalog/excel', 'token=' . $this->session->data['token'], true);


        $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 .'catalog/excel.tpl')){
            $this->response->setOutput($this->load->view( DIR_LOCAL_TEMPLATE .'catalog/excel', $data));
        }else{
            $this->response->setOutput($this->load->view(DIR_TEMPLATE . 'catalog/excel', $data));
        }

    }

    public function excelRead()
    {

        unset($this->session->data['excel_file']);
        header('Content-Type: text/html; charset=utf-8');
        set_time_limit(0);

        require_once DIR_SYSTEM . 'PHPExcel/Classes/PHPExcel.php';

        $file_path = $this->session->data['file_path'];
        //$file_path = DIR_IMAGE . 'repository/DeerCase Ürün Excel.xlsx';
        $flag = 0;
        $valid = false;
        $types = array('Excel2007', 'Excel5');
        foreach ($types as $type) {
            $objReader = PHPExcel_IOFactory::createReader($type);
            if ($objReader->canRead($file_path)) {
                $valid = true;
                break;
            }
        }

        if(!isset($this->session->data['excel_file'])){
            $objPHPExcel = new PHPExcel();
            $objReader->setReadDataOnly(true);
            $objPHPExcel = $objReader->load($file_path);
            $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
            $array_data = array();
            foreach ($rowIterator as $row) {
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false);
                $rowIndex = $row->getRowIndex();
                $columnIndex = 0;
                //if (!$flag) {
                foreach ($cellIterator as $cell) {
                    $this->session->data['excel_file'][$rowIndex - 1][$columnIndex++] = $cell->getCalculatedValue();
                }
                $flag++;
                //}
            }

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

        $add_product_count = 0;
        $edit_product_count = 0;
        foreach ($this->session->data['excel_file'] as $k_row => $product_item){ //var_dump($product_item); die();

            if($product_item[7] == ''){
                //var_dump($k_row); die();
            }

            // 0 => Ürün Adı
            // 1 => Ürün Kodu Model
            // 2 => Ürün Fiyat - Price
            // 3 => Ürün İndirimli Fiyat - Special
            // 4 => Ürün Adeti - Quantity
            // 5 => 2. Kategori - Category
            // 6 => 3. Kategori - Category
            // 7 => Ana Kategori - Category
            // 8 => Koleksiyon - Attribute
            // 9 => Koleksiyon - Renk - Attribute
            // 10 => Ürün Tipi - Product Type
            // 11 => Ürün Deseni - Desen
            // 12 => Ürün Meta Title - Prdouct Description
            // 13 => Ürün Meta Description - Prdouct Description

            if($product_item[0] != ''){
                $product_name = $product_item[0];
                $product_meta_title = $product_item[12];
                $product_meta_description = $product_item[13];

                $product_description = array();
                $product_description[1] = array(
                    'name' => $product_name,
                    'description' => $product_name,
                    'meta_title' => $product_meta_title,
                    'meta_description' => $product_meta_description,
                    'tag' => '',
                    'meta_keyword' => '',
                );


                $model = $this->seflink($product_item[1]);
                $quantity = $product_item[4];
                $price = $product_item[2];
                $special = $product_item[3];

                $category_1 = $this->findCategoryIdByName($product_item[7] . ' Telefon Kılıfı', 0);
                $category_2 = $this->findCategoryIdByName($product_item[5] . ' Telefon Kılıfı', $category_1);
                $category_3 = $this->findCategoryIdByName($product_item[6], $category_2);

                $product_category = array($category_1, $category_2, $category_3);

                $manufacturer = $product_item[7];
                $manufacturer_id = $this->findManufacturerIdByName($manufacturer);

                $koleksiyon = $this->findAttributeIDByName($product_item[8], 11);
                $renk = $this->findAttributeIDByName($product_item[9], 13, $product_item[8]);

                $product_attribute = array();
                $product_attribute_description = array();
                $product_attribute_description[1] = array(
                    'text' => ''
                );
                $product_attribute[] = array(
                    'attribute_id' => $koleksiyon,
                    'product_attribute_description' => $product_attribute_description
                );

                $product_attribute_description = array();
                $product_attribute_description[1] = array(
                    'text' => ''
                );
                $product_attribute[] = array(
                    'attribute_id' => $renk,
                    'product_attribute_description' => $product_attribute_description
                );


                if($product_item[10] == 'Özel')
                    $product_type = 1;
                else
                    $product_type = 0;

                $model2_data = array(
                    'model' => $model,
                    'koleksiyon' => $product_item[8],
                    'renk' => $product_item[9],
                    'category' => $product_item[6],
                );

                $model2 = $this->createModel2($model2_data);


                $product_special = array();
                if($special){
                    $product_special[] = array(
                        'price' => $special,
                        'customer_group_id' => 1
                    );
                }

                $image_path = '';
                switch ($product_item[8]){
                    case 'Simli Sulu': $image_path = 'aquarium'; break;
                    case 'Neon': $image_path = 'neon'; break;
                    case 'Şeffaf': $image_path = 'seffaf'; break;
                    case 'Renkli Silikon': $image_path = 'silicon'; break;
                }

                $product_image = array();
                $image = '';
                $images = $this->getImg($image_path, $model, $product_item[7], $product_item[5], $product_item[9]);

                if(isset($images[0])){
                    $image = $images[0];
                    unset($images[0]);
                    foreach ($images as $i_key => $img){
                        $product_image[] = array(
                            'image' => $img,
                            'sort_order' => $i_key,
                        );
                    }
                }

                $product_data = array(
                    'model' => $model,
                    'model2' => $model2,
                    'quantity' => $quantity,
                    'manufacturer_id' => $manufacturer_id,
                    'image' => $image,
                    'price' => $price,
                    'sku' => '',
                    'product_type' => '1',
                    'minimum' => '1',
                    'subtract' => '1',
                    'stock_status_id' => '1',
                    'date_available' => '',
                    'shipping' => '1',
                    'currency_id' => '1',
                    'points' => '',
                    'weight' => '',
                    'weight_class_id' => '',
                    'length' => '',
                    'width' => '',
                    'height' => '',
                    'length_class_id' => '',
                    'tax_class_id' => 1,
                    'status' => 1,
                    'sort_order' => 0,
                    'product_special' => $product_special,
                    'product_description' => $product_description,
                    'product_category' => $product_category,
                    'product_attribute' => $product_attribute,
                    'product_image' => $product_image,
                );



                $ask = $this->db->query("SELECT * FROM ps_product WHERE model2 = '". $this->db->escape($model2) ."' ");
                if(!isset($ask->row['product_id'])){
                    if($image){
                        $add_product_count++;
                        $this->product->addProduct($product_data);
                    }
                }else{
                    $edit_product_count++;
                }
            }

        }


        echo $add_product_count . ' Yeni Ürün Eklendi' . '<br>';
        echo $edit_product_count . ' Ürün güncellendi' . '<br>';



    }

    private function koleksiyonRenkEslestirmesi($koleksiyon, $renk){

        $return = '';
        switch ($renk){
            //Neon
            case 'Violet' : $return = 'Violet'; break;
            case 'Yeşil' : $return = 'Green'; break;
            case 'Turuncu' : $return = 'Orange'; break;
            //Aqua
            case 'Pembe' : $return = 'Red'; break;
            case 'Siyah' : $return = 'Black'; break;
            case 'Gri' : $return = 'Silver'; break;
            //Silicon
            case 'Siyah' : $return = 'Black'; break;
            case 'Pembe' : $return = 'Pink'; break;
            case 'Kırmızı' : $return = 'Red'; break;
            case 'Sarı' : $return = 'Yellow'; break;
            //Şeffaf
            case 'Siyah' : $return = 'Black'; break;
        }

    }

    private function findManufacturerIdByName($manufacturer_name)
    {
        $ask = $this->db->query("SELECT * FROM ps_manufacturer WHERE name = '". $this->db->escape($manufacturer_name) ."' ");
        if(isset($ask->row['manufacturer_id'])){
            return $ask->row['manufacturer_id'];
        }

    }

    public function createModel2($data)
    {

        $koleksiyon = '';
        $renk = '';

        switch ($data['koleksiyon']){
            case 'Neon': $koleksiyon = 'neon'; break;
            case 'Simli Sulu': $koleksiyon = 'aquarium'; break;
            case 'Renkli Silikon': $koleksiyon = 'silicon'; break;
            case 'Şeffaf': $koleksiyon = 'seffaf'; break;
        }

        switch ($data['renk']){
            case 'Violet': $renk = 'violet'; break;
            case 'Turuncu': $renk = 'orange'; break;
            case 'Yeşil': $renk = 'green'; break;
            case 'Pembe': $renk = 'pink'; break;
            case 'Siyah': $renk = 'black'; break;
            case 'Gri': $renk = 'silver'; break;
            case 'Saydam': $renk = 'saydam'; break;
            case 'Sarı': $renk = 'yellow'; break;
        }

        $alt_category = str_replace(' ', '_', mb_convert_case($data['category'], MB_CASE_LOWER , "UTF-8"));

        return $data['model'] . '-' . $alt_category . '-' . $koleksiyon . '-' . $renk . '-kilif';

    }

    public function findAttributeIDByName($attribute_name, $attribute_group_id, $koleksiyon = '')
    {

        //Koleksiyonların içerisindeki renkleri ayırabilmek için attribute_key alanını yaptık. neon-red, auq-red gibi
        $attr_coll = '';
        if($koleksiyon){
            switch ($koleksiyon){
                case 'Neon': $attr_coll = 'neon-'; break;
                case 'Simli Sulu': $attr_coll = 'aqua-'; break;
                case 'Renkli Silikon': $attr_coll = 'silicon-'; break;
                case 'Şeffaf': $attr_coll = 'seffaf-'; break;
            }
            switch ($attribute_name){
                case 'Violet': $attr_coll .= 'violet'; break;
                case 'Yeşil': $attr_coll .= 'green'; break;
                case 'Turuncu': $attr_coll .= 'orange'; break;
                case 'Pembe': $attr_coll .= 'pink'; break;
                case 'Siyah': $attr_coll .= 'black'; break;
                case 'Gri': $attr_coll .= 'silver'; break;
                case 'Saydam': $attr_coll .= 'saydam'; break;
                case 'Kırmızı': $attr_coll .= 'red'; break;
                case 'Sarı': $attr_coll .= 'yellow'; break;
            }
        }

        $ask = $this->db->query("SELECT * FROM ps_attribute a 
        LEFT JOIN ps_attribute_description ad ON(a.attribute_id = ad.attribute_id) 
        WHERE ad.name = '". $this->db->escape($attribute_name) ."' AND ad.language_id = '1' AND a.attribute_group_id = '". (int)$attribute_group_id ."' AND attribute_key = '". $this->db->escape($attr_coll) ."' ");
        if(isset($ask->row['attribute_id'])){
            return $ask->row['attribute_id'];
        }else{
            $attribute_description[1] = array(
                'name' => $attribute_name
            );

            $attribute_data = array(
                'attribute_group_id' => $attribute_group_id,
                'sort_order' => 0,
                'attribute_key' => $attr_coll,
                'attribute_description' => $attribute_description
            );
            echo $attr_coll . '<br>';
            return $this->addAttribute($attribute_data);
        }



    }

    public function addAttribute($data) {

        $this->db->query("INSERT INTO ps_attribute SET 
            attribute_group_id = '" . (int)$data['attribute_group_id'] . "', 
            attribute_key = '" . $data['attribute_key'] . "', 
            sort_order = '" . (int)$data['sort_order'] . "' ");

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

        foreach ($data['attribute_description'] as $language_id => $value) {
            $this->db->query("INSERT INTO ps_attribute_description SET 
            attribute_id = '" . (int)$attribute_id . "', 
            language_id = '" . (int)$language_id . "', 
            name = '" . $this->db->escape($value['name']) . "'");
        }

        return $attribute_id;
    }

    public function findCategoryIdByName($category_name, $parent_id)
    {
        //gelen kategori adına göre kategori ID döner, Yoksa açar.

        $ask = $this->db->query("SELECT * FROM ps_category_description WHERE name = '". $this->db->escape($category_name) ."' AND language_id = '1' ");
        if(isset($ask->row['category_id'])){
            return $ask->row['category_id'];
        }else{
            $category_description[1] = array(
                'name' => $category_name,
            );

            $category_data = array(
                'parent_id' => $parent_id,
                'top' => 1,
                'column' => 0,
                'sort_order' => 0,
                'status' => 1,
                'keyword' => $this->seflink($category_name),
                'category_description' => $category_description
            );

            return $this->addCategory($category_data);
        }

    }

    public function addCategory($data)
    {
        $this->db->query("INSERT INTO ps_category SET 
        parent_id = '" . (int)$data['parent_id'] . "', 
        `top` = '" . (isset($data['top']) ? (int)$data['top'] : 0) . "', 
        `column` = '" . (int)$data['column'] . "', 
        sort_order = '" . (int)$data['sort_order'] . "', 
        status = '" . (int)$data['status'] . "', 
        date_modified = NOW(), 
        date_added = NOW() ");

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

        if (isset($data['category_description'])) {
            foreach ($data['category_description'] as $language_id => $value) {
                $this->db->query("INSERT INTO ps_category_description SET 
                category_id = '" . (int)$category_id . "', 
                language_id = '" . (int)$language_id . "', 
                name = '" . $this->db->escape($value['name']) . "' ");
            }
        }


        $level = 0;
        $query = $this->db->query("SELECT * FROM ps_category_path WHERE category_id = '" . (int)$data['parent_id'] . "' ORDER BY `level` ASC");
        foreach ($query->rows as $result) {
            $this->db->query("INSERT INTO ps_category_path SET category_id = '" . (int)$category_id . "', path_id = '" . (int)$result['path_id'] . "', level = '" . (int)$level . "'");
            $level++;
        }

        $this->db->query("INSERT INTO ps_category_path SET category_id = '" . (int)$category_id . "', path_id = '" . (int)$category_id . "', level = '" . (int)$level . "'");

        $this->db->query("INSERT INTO ps_category_to_store SET category_id = '" . (int)$category_id . "', store_id = '0' ");

        $this->db->query("INSERT INTO ps_url_alias SET query = 'category_id=" . (int)$category_id . "', keyword = '" . $this->db->escape($data['keyword']) . "', language_id = '1' ");

        return $category_id;

    }

    function seflink($text)
    {
        $find = array('Ç', 'Ş', 'Ğ', 'Ü', 'İ', 'Ö', 'ç', 'ş', 'ğ', 'ü', 'ö', 'ı', '+', '#');
        $replace = array('c', 's', 'g', 'u', 'i', 'o', 'c', 's', 'g', 'u', 'o', 'i', 'plus', 'sharp');
        $text = strtolower(str_replace($find, $replace, $text));
        $text = preg_replace("@[^A-Za-z0-9\-_\.\+]@i", ' ', $text);
        $text = trim(preg_replace('/\s+/', ' ', $text));
        $text = str_replace(' ', '-', $text);
        return $text;
    }

    private function getImg($koleksiyon, $model, $main_category, $alt_category, $color)
    {

        //Koleksiyonların içerisindeki renkleri ayırabilmek için attribute_key alanını yaptık. neon-red, auq-red gibi
        $attr_coll = '';
        if($koleksiyon){
            switch ($koleksiyon){
                case 'neon': $attr_coll = 'NEON_'; break;
                case 'aquarium': $attr_coll = 'AQUARIUM_'; break;
                case 'silicon': $attr_coll = 'SILICON_'; break;
                case 'seffaf': $attr_coll = 'SEFFAF_'; break;
            }
            switch ($color){
                case 'Violet': $attr_coll .= 'VIOLET'; break;
                case 'Yeşil': $attr_coll .= 'GREEN'; break;
                case 'Turuncu': $attr_coll .= 'ORANGE'; break;
                case 'Pembe': ( ($koleksiyon == 'silicon') ? $attr_coll .= 'PINK' : $attr_coll .= 'RED'); break;
                case 'Siyah': $attr_coll .= 'BLACK'; break;
                case 'Gri': $attr_coll .= 'SILVER'; break;
                case 'Saydam': $attr_coll .= 'SAYDAM'; break;
                case 'Kırmızı': $attr_coll .= 'RED'; break;
                case 'Sarı': $attr_coll .= 'YELLOW'; break;
            }
        }



        $file_path = DIR_IMAGE . 'repository/';

        $model = mb_convert_case($model, MB_CASE_UPPER , "UTF-8");
        $koleksiyon = mb_convert_case($koleksiyon, MB_CASE_UPPER , "UTF-8");

        $model_prefix = substr($model, 0,2);

        $main_category = mb_convert_case($main_category, MB_CASE_UPPER , "UTF-8");
        $alt_category = str_replace(' ', '_', mb_convert_case($alt_category, MB_CASE_UPPER , "UTF-8"));
        $color = str_replace(' ', '_', mb_convert_case($color, MB_CASE_UPPER , "UTF-8"));

        $images = array();
        if($model_prefix == 'NE'){
            $image_1 = $file_path . 'neon/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-1.jpg';
            $image_2 = $file_path . 'neon/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-1.jpg';
            $image_3 = $file_path . 'neon/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-1.jpg';
            if(is_file($image_1)){
                $images[] = 'repository/' . 'neon/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-1.jpg';
            }else{
                echo $image_1 . ' Bulunamadı! <br>';
            }
            if(is_file($image_2)){
                $images[] = 'repository/' . 'neon/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-2.jpg';
            }else{
                echo $image_2 . ' Bulunamadı! <br>';
            }
            if(is_file($image_3)){
                $images[] = 'repository/' . 'neon/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-3.jpg';
            }else{
                echo $image_3 . ' Bulunamadı! <br>';
            }
        }else if($model_prefix == 'AQ'){
            $image_1 = $file_path . 'aquarium/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-1.jpg';
            $image_2 = $file_path . 'aquarium/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-2.jpg';
            $image_3 = $file_path . 'aquarium/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-3.jpg';
            if(is_file($image_1)){
                $images[] = 'repository/' . 'aquarium/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-1.jpg';
            }else{
                echo $image_1 . '<br>';
            }
            if(is_file($image_2)){
                $images[] = 'repository/' . 'aquarium/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-2.jpg';
            }else{
                echo $image_2 . '<br>';
            }
            if(is_file($image_3)){
                $images[] = 'repository/' . 'aquarium/' . $model .'/' . $model . '-' . $main_category . '-' . $alt_category . '-' . $koleksiyon . '-' . $attr_coll . '-3.jpg';
            }else{
                echo $image_3 . '<br>';
            }
        }

        return $images;

    }
}
