<?php 
class ModelExtensionModuleExcelportReview extends ModelExtensionModuleExcelport
{
    public function importXLSReviews($file, $importLimit, $addAsNew = false)
    {
        $this->language->load('extension/module/excelport');
        if (!is_numeric($importLimit) || $importLimit < 10 || $importLimit > 800) throw new Exception($this->language->get('excelport_import_limit_invalid'));

        $progress = $this->getProgress();
        $progress['importedCount'] = !empty($progress['importedCount']) ? $progress['importedCount'] : 0;
        $progress['done'] = false;

        // Create new PHPExcel object
        require_once(IMODULE_ROOT.'system/library/vendor/isenselabs/excelport/phpexcel/PHPExcel.php');
        require_once(IMODULE_ROOT.'system/library/vendor/isenselabs/excelport/phpexcel/CustomReadFilter.php');
        $chunkFilter = new CustomReadFilter(array('Reviews' => array('A', ($progress['importedCount'] + 2), 'AM', (($progress['importedCount'] + $importLimit) + 1)), 'reviews' => array('A', ($progress['importedCount'] + 2), 'AM', (($progress['importedCount'] + $importLimit) + 1))), true); 

        $madeImports = false;
        $objReader = new PHPExcel_Reader_Excel2007();
        $objReader->setReadFilter($chunkFilter);
        $objReader->setReadDataOnly(true);
        $objReader->setLoadSheetsOnly(array("Reviews", "reviews"));
        $objPHPExcel = $objReader->load($file);
        $progress['importingFile'] = substr($file, strripos($file, '/') + 1);
        $reviewSheet = 0;

        $reviewSheetObj = $objPHPExcel->setActiveSheetIndex($reviewSheet);

        $progress['all'] = -1;
        $this->setProgress($progress);

        $review_map = array(
            'review_id'     => 0,
            'product_id'    => 1,
            'customer_id'   => 2,
            'author'        => 3,
            'text'          => 4,
            'rating'        => 5,
            'status'        => 6,
            'date_added'    => 7,
            'date_modified' => 8
        );

        $source = array(0, 2 + ($progress['importedCount']));

        do {
            $this->custom_set_time_limit();
            $review_author = strval($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['author']) . ($source[1]))->getValue());

            if (!empty($review_author)) {
                $review_id            = (int)trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['review_id']) . ($source[1]))->getValue());
                $review_product_id    = (int)trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['product_id']) . ($source[1]))->getValue());
                $review_customer_id   = (int)trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['customer_id']) . ($source[1]))->getValue());
                // $review_author        = trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['author']) . ($source[1]))->getValue());
                $review_text          = trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['text']) . ($source[1]))->getValue());
                $review_rating        = (int)trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['rating']) . ($source[1]))->getValue());
                $review_status        = trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['status']) . ($source[1]))->getValue());
                $review_status        = $review_status == 'Enabled' ? 1 : 0;
                $review_date_added    = trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['date_added']) . ($source[1]))->getValue());
                $review_date_modified = trim($reviewSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $review_map['date_modified']) . ($source[1]))->getValue());

                $review = array(
                    'review_id'     => $review_id,
                    'product_id'    => $review_product_id,
                    'customer_id'   => $review_customer_id,
                    'author'        => $review_author,
                    'text'          => $review_text,
                    'rating'        => $review_rating,
                    'status'        => $review_status,
                    'date_added'    => $review_date_added,
                    'date_modified' => $review_date_modified
                );

                // Extras
                foreach ($this->extraGeneralFields['Reviews'] as $extra) {
                    if (!empty($extra['name']) && !empty($extra['column_light'])) {
                        $review[$extra['name']] = $reviewSheetObj->getCell($extra['column_light'] . $source[1])->getValue(); 
                    }
                }

                if (!$addAsNew) {
                    $exists = false;
                    $existsQuery = $this->db->query("SELECT review_id FROM " . DB_PREFIX . "review WHERE review_id = " . $review_id);

                    $exists = $existsQuery->num_rows > 0;

                    if ($exists) {
                        $this->editReview($review_id, $review);
                    } else {
                        $this->addReview($review_id, $review);
                    }
                } else {
                    $this->addReview('', $review);
                }

                $progress['current']++;
                $progress['importedCount']++;
                $madeImports = true;
                $this->setProgress($progress);
            }

            $source[1] += 1;

        } while (!empty($review_author));

        $progress['done'] = true;
        if (!$madeImports) {
            $progress['importedCount'] = 0;
            array_shift($this->session->data['uploaded_files']);
        }

        $this->setProgress($progress);
    }

    public function exportXLSReviews($destinationFolder = '', $exportLimit = 500, $exportFilters = array())
    {
        $this->language->load('extension/module/excelport');
        $this->folderCheck($destinationFolder);

        $progress = $this->getProgress();
        $progress['done'] = false;

        //=== Preparation
        $language       = $this->config->get('config_language_id');
        $fileTemplate   = IMODULE_ROOT . 'system/library/vendor/isenselabs/excelport/excelport/template_review.xlsx';
        $filename       = 'reviews_excelport_' . str_replace('/', '_', substr(HTTP_CATALOG, 7, strlen(HTTP_CATALOG) - 8)) . '_' . date("Y-m-d_H-i-s") . '_' . $progress['current'];
        $outputFilename = $filename . '.xlsx';
        $outputFilepath = $destinationFolder . '/' . $filename . '.xlsx';

        if (!empty($progress['populateAll'])) {
            $all = $this->db->query($this->getQuery($exportFilters, true));

            $progress['all'] = $all->num_rows ? (int)$all->row['count'] : 0;
            unset($progress['populateAll']);
            $this->setProgress($progress);
        }

        $this->setData('Reviews', $destinationFolder, $language);

        require_once(IMODULE_ROOT.'system/library/vendor/isenselabs/excelport/phpexcel/PHPExcel.php');

        //=== Sheets Mapping
        // Sheets index in $fileTemplate
        $reviewSheet = 0; // ds
        $metaSheet   = 1; // ms
        $dsInsertPos = array(0,2); // start col 0, row 2

        // Main fields ($reviewSheet)
        $generals = array(
            'review_id'     => 0,
            'product_id'    => 1,
            'customer_id'   => 2,
            'author'        => 3,
            'text'          => 4,
            'rating'        => 5,
            'status'        => 6,
            'date_added'    => 7,
            'date_modified' => 8
        );

        // Extra fields
        $extras = array();
        foreach ($this->extraGeneralFields['Reviews'] as $extra) {
            if (!empty($extra['name']) && !empty($extra['column_light'])) {
                $extras[$extra['name']] = $extra['column_light'];
            }
        }

        $dataValidations = array(
            array(
                'type' => 'list',
                'field' => $generals['status'],
                'data' => array(0,2,0,3),  // start col 0, row 2; end col 0, row 3
                'range' => '',
            )
        );

        //=== Template object
        $objPHPExcel = PHPExcel_IOFactory::load($fileTemplate);

        // Set document properties
        $objPHPExcel->getProperties()
                    ->setCreator($this->user->getUserName())
                    ->setLastModifiedBy($this->user->getUserName())
                    ->setTitle($filename)
                    ->setSubject($filename)
                    ->setDescription("Backup for Office 2007 and later, generated using PHPExcel and ExcelPort.")
                    ->setKeywords("office 2007 2010 2013 xlsx openxml php phpexcel excelport")
                    ->setCategory("Backup");

        $objPHPExcel->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        $metaSheetObj = $objPHPExcel->setActiveSheetIndex($metaSheet);

        // Get db data
        $this->db->query("SET SESSION group_concat_max_len = 1000000;");
        $reviewsQuery = $this->db->query($this->getQuery($exportFilters) . " ORDER BY r.review_id ASC LIMIT ". $progress['current'] . ", " . $exportLimit);

        $reviewSheetObj = $objPHPExcel->setActiveSheetIndex($reviewSheet);
        foreach ($this->extraGeneralFields['Reviews'] as $extra) {
            if (!empty($extra['title']) && !empty($extra['column_light'])) {
                $reviewSheetObj->setCellValueExplicit($extra['column_light'] . '1', $extra['title'], PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }

        if ($reviewsQuery->num_rows > 0) {
            foreach ($reviewsQuery->rows as $row) {
                $this->getData('Reviews', $row);

                // Prepare data
                $row['status'] = empty($row['status']) ? 'Disabled' : 'Enabled';
                
                // Extras
                foreach ($extras as $name => $position) {
                    $reviewSheetObj->setCellValueExplicit($position . ($dsInsertPos[1]), empty($row[$name]) ? '' : $row[$name], PHPExcel_Cell_DataType::TYPE_STRING);
                }

                // General
                foreach ($generals as $name => $position) {
                    $reviewSheetObj->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($dsInsertPos[0] + $position) . ($dsInsertPos[1]), empty($row[$name]) && $row[$name] !== '0' ? '' : $row[$name], PHPExcel_Cell_DataType::TYPE_STRING);
                }

                // Data validations
                foreach ($dataValidations as $dataValidationIndex => $dataValidation) {
                    if (isset($dataValidations[$dataValidationIndex]['count']) && $dataValidations[$dataValidationIndex]['count'] == 0) continue;
                    $dataValidations[$dataValidationIndex]['range'] = PHPExcel_Cell::stringFromColumnIndex($dsInsertPos[0] + $dataValidation['field']) . ($dsInsertPos[1]);
                    if (empty($dataValidations[$dataValidationIndex]['root'])) $dataValidations[$dataValidationIndex]['root'] = PHPExcel_Cell::stringFromColumnIndex($dsInsertPos[0] + $dataValidation['field']) . ($dsInsertPos[1]);
                }

                $dsInsertPos[1] = $dsInsertPos[1] + 1;
                $progress['current']++;
                $progress['memory_get_usage'] = round(memory_get_usage(true)/(1024*1024));
                $progress['percent'] = 100 / ($reviewsQuery->num_rows / $progress['current']);
                
                $this->setProgress($progress);
            }

            foreach ($dataValidations as $dataValidationIndex => $dataValidation) {
                if (isset($dataValidations[$dataValidationIndex]['count']) && $dataValidations[$dataValidationIndex]['count'] == 0) continue;
                if ($dataValidations[$dataValidationIndex]['range'] != $dataValidations[$dataValidationIndex]['root']) {
                    $dataValidations[$dataValidationIndex]['range'] = $dataValidations[$dataValidationIndex]['root'] . ':' . $dataValidations[$dataValidationIndex]['range'];
                }
            }

            //Apply data validation for:
            // Generals
            foreach ($dataValidations as $dataValidation) {
                $range = trim($dataValidation['range']);
                if (isset($dataValidation['count']) && $dataValidation['count'] == 0) continue;
                if ($dataValidation['type'] == 'list' && !empty($dataValidation['root']) && !empty($range)) {
                    $objValidation = $reviewSheetObj->getCell($dataValidation['root'])->getDataValidation();
                    $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
                    $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
                    $objValidation->setAllowBlank(false);
                    $objValidation->setShowInputMessage(true);
                    $objValidation->setShowErrorMessage(true);
                    $objValidation->setShowDropDown(true);
                    $objValidation->setErrorTitle('Input error');
                    $objValidation->setError('Value is not in list.');
                    $objValidation->setPromptTitle('Pick from list');
                    $objValidation->setPrompt('Please pick a value from the drop-down list.');
                    $objValidation->setFormula1($metaSheetObj->getTitle() . '!$' . PHPExcel_Cell::stringFromColumnIndex($dataValidation['data'][0]) . '$' . ($dataValidation['data'][1]) . ':$' . PHPExcel_Cell::stringFromColumnIndex($dataValidation['data'][2]) . '$' . ($dataValidation['data'][3]));
                    $reviewSheetObj->setDataValidation($range, $objValidation);
                }
            }
            
            unset($objValidation);
        } else {
            $progress['done'] = true;
        }

        $this->session->data['generated_file'] = $outputFilepath;
        $this->session->data['generated_files'][] = $outputFilename;
        $this->setProgress($progress);

        try {
            $this->custom_set_time_limit();

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $objWriter->setPreCalculateFormulas(false);
            $objWriter->save($outputFilepath);

            $progress['done'] = true;
        } catch (Exception $e) {
            $progress['message'] = $e->getMessage();
            $progress['error'] = true;
            $progress['done'] = false;
            $this->setProgress($progress);
        }

        $objPHPExcel->disconnectWorksheets();
        unset($metaSheetObj);
        unset($objWriter);
        unset($reviewSheetObj);
        unset($objPHPExcel);
        
        $progress['done'] = true;
        $this->setProgress($progress);
        
        return true;
    }

    public function getQuery($filters = array(), $count = false)
    {
        if (empty($filters) || !in_array($filters['Conjunction'], array('AND', 'OR'))) $filters['Conjunction'] = 'OR';

        $wheres     = array();
        $conditions = $this->conditions;

        foreach ($filters as $i => $filter) {
            if (is_array($filter)) {

                $condition = str_replace(array('{FIELD_NAME}', '{WORD}'), array($conditions['Reviews'][$filter['Field']]['field_name'], stripos($conditions['Reviews'][$filter['Field']]['type'], 'number') !== FALSE ? (int)$this->db->escape($filter['Value']) : $this->db->escape($filter['Value'])), $this->operations[$filter['Condition']]['operation']);
                if (!in_array($condition, $wheres)) $wheres[] = $condition;
            }
        }

        $select = $count ? "COUNT(*)" : "*";
        $query = ($count ? "SELECT COUNT(*) as count FROM (" : "") . "SELECT " . $select . " FROM " . DB_PREFIX . "review r " . (!empty($wheres) ? " WHERE (" . implode(" " . $filters['Conjunction'] . " ", $wheres) . ")" : "") . " GROUP BY r.review_id" . ($count ? ") as count_table" : "");

        return $query;
    }

    public function addReview($review_id = '', $data = array())
    {
        $this->db->query("INSERT INTO " . DB_PREFIX . "review SET " .
            (!empty($review_id) ? "review_id = '" . (int)$review_id . "', " : "") . "
            product_id    = '" . (int)$data['product_id'] . "',
            customer_id   = '" . (int)$data['customer_id'] . "',
            author        = '" . $this->db->escape($data['author']) . "',
            text          = '" . $this->db->escape($data['text']) . "',
            rating        = '" . (int)$data['rating'] . "',
            status        = '" . (int)$data['status'] . "',
            date_added    = " . (!empty($data['date_added']) ? "'" . $this->db->escape($data['date_added']) . "'" : "NOW()") . ",
            date_modified = " . (!empty($data['date_date_modified']) ? "'" . $this->db->escape($data['date_modified']) . "'" : "NOW()")
        );

        // Extras
        foreach ($this->extraGeneralFields['Reviews'] as $extra) {
            if (!empty($extra['eval_add'])) {
                eval($extra['eval_add']);
            }
        }
    }

    public function editReview($review_id, $data)
    {
        $this->db->query("UPDATE " . DB_PREFIX . "review SET
            product_id    = '" . (int)$data['product_id'] . "',
            customer_id   = '" . (int)$data['customer_id'] . "',
            author        = '" . $this->db->escape($data['author']) . "',
            text          = '" . $this->db->escape($data['text']) . "',
            rating        = '" . (int)$data['rating'] . "',
            status        = '" . (int)$data['status'] . "',
            date_added    = " . (!empty($data['date_added']) ? "'" . $this->db->escape($data['date_added']) . "'" : "NOW()") . ",
            date_modified = " . (!empty($data['date_modified']) ? "'" . $this->db->escape($data['date_modified']) . "'" : "NOW()") . "
        WHERE review_id='" . (int)$review_id . "'");

        // Extras
        foreach ($this->extraGeneralFields['Reviews'] as $extra) {
            if (!empty($extra['eval_edit'])) {
                eval($extra['eval_edit']);
            }
        }
    }

    public function deleteReviews()
    {
        $this->load->model('catalog/review');

        $ids = $this->db->query("SELECT review_id FROM " . DB_PREFIX . "review");

        foreach ($ids->rows as $row) {
            $this->model_catalog_review->deleteReview($row['review_id']);
        }
    }
}