<?php 
class ModelExtensionModuleExcelportDownload extends ModelExtensionModuleExcelport
{
    public function importXLSDownloads($language, $allLanguages, $file, $importLimit = 100, $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'));

        $default_language = $this->config->get('config_language_id');
        $this->config->set('config_language_id', $language);

        $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('Downloads' => array('A', ($progress['importedCount'] + 2), 'AM', (($progress['importedCount'] + $importLimit) + 1)), 'downloads' => 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("Downloads", "downloads"));
        $objPHPExcel = $objReader->load($file);
        $progress['importingFile'] = substr($file, strripos($file, '/') + 1);
        $downloadSheet = 0;

        $downloadSheetObj = $objPHPExcel->setActiveSheetIndex($downloadSheet);

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

        $download_map = array(
            'download_id' => 0,
            'name'        => 1,
            'filename'    => 2,
            'mask'        => 3,
            'date_added'  => 4
        );

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

        do {
            $this->custom_set_time_limit();
            $download_name = strval($downloadSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $download_map['name']) . ($source[1]))->getValue());

            if (!empty($download_name)) {
                $download_id         = (int)trim($downloadSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $download_map['download_id']) . ($source[1]))->getValue());
                $download_filename   = trim($downloadSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $download_map['filename']) . ($source[1]))->getValue());
                $download_mask       = trim($downloadSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $download_map['mask']) . ($source[1]))->getValue());
                $download_date_added = trim($downloadSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $download_map['date_added']) . ($source[1]))->getValue());

                $download = array(
                    'download_id' => $download_id,
                    'download_description' => array(
                        $language => array(
                            'name' => $download_name
                        )
                    ),
                    'filename'    => $download_filename,
                    'mask'        => $download_mask,
                    'date_added'  => $download_date_added
                );

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

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

                    $exists = $existsQuery->num_rows > 0;

                    if ($exists) {
                        $this->editDownload($download_id, $download, $allLanguages);
                    } else {
                        $this->addDownload($download_id, $download, $allLanguages);
                    }
                } else {
                    $this->addDownload('', $download, $allLanguages);
                }

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

            $source[1] += 1;

        } while (!empty($download_name));

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

        $this->setProgress($progress);
        $this->config->set('config_language_id', $default_language);
    }

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

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

        //=== Preparation
        $this->load->model('localisation/language');
        $languageQuery  = $this->model_localisation_language->getLanguage($language);
        
        $fileTemplate   = IMODULE_ROOT . 'system/library/vendor/isenselabs/excelport/excelport/template_download.xlsx';
        $filename       = 'downloads_excelport_' . $languageQuery['code'] . '_' . 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, $language, true));

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

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

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

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

        // Main fields ($downloadSheet)
        $generals = array(
            'download_id'   => 0,
            'name'          => 1,
            'filename'      => 2,
            'mask'          => 3,
            'date_added'    => 4
        );

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

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

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

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

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

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

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

                $dsInsertPos[1] = $dsInsertPos[1] + 1;
                $progress['current']++;
                $progress['memory_get_usage'] = round(memory_get_usage(true)/(1024*1024));
                $progress['percent'] = 100 / ($downloadsQuery->num_rows / $progress['current']);
                
                $this->setProgress($progress);
            }
        } 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($objWriter);
        unset($downloadSheetObj);
        unset($objPHPExcel);
        
        $progress['done'] = true;
        $this->setProgress($progress);
        
        return true;
    }

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

        $join_rules = array(
            'download_description' => "LEFT JOIN " . DB_PREFIX . "download_description dd ON (d.download_id = dd.download_id AND dd.language_id = '" . $language . "')"
        );

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

        foreach ($filters as $i => $filter) {
            if (is_array($filter)) {
                if (!array_key_exists($conditions['Downloads'][$filter['Field']]['join_table'], $joins) && array_key_exists($conditions['Downloads'][$filter['Field']]['join_table'], $join_rules)) {
                    $joins[$conditions['Downloads'][$filter['Field']]['join_table']] = $join_rules[$conditions['Downloads'][$filter['Field']]['join_table']];
                }

                $condition = str_replace(array('{FIELD_NAME}', '{WORD}'), array($conditions['Downloads'][$filter['Field']]['field_name'], stripos($conditions['Downloads'][$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 . "download d " . implode(" ", $joins) . " " . (!empty($wheres) ? " WHERE (" . implode(" " . $filters['Conjunction'] . " ", $wheres) . ")" : "") . " GROUP BY d.download_id" . ($count ? ") as count_table" : "");

        return $query;
    }

    public function addDownload($download_id = '', $data = array(), $allLanguages = array())
    {
        $this->addDownloadLanguages($data, $allLanguages);

        $queryDownloadId = !empty($download_id) ? "download_id = '" . (int)$download_id . "', " : "";
        $queryDateAdded = ", date_added = " . (!empty($data['date_added']) ? "'" . $this->db->escape($data['date_added']) . "'" : "NOW()");

        $this->db->query("INSERT INTO " . DB_PREFIX . "download SET " . $queryDownloadId . " filename = '" . $this->db->escape($data['filename']) . "', mask = '" . $this->db->escape($data['mask']) . "' " . $queryDateAdded);

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

        $language_ids = array();
        foreach ($allLanguages as $language) {
            $language_ids[] = $language['language_id']; 
        }

        $this->db->query("DELETE FROM " . DB_PREFIX . "download_description WHERE download_id = '" . (int)$download_id . "' AND language_id NOT IN (" . implode(',', $language_ids) . ")");

        foreach ($data['download_description'] as $language_id => $value) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "download_description SET download_id = '" . (int)$download_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "' ON DUPLICATE KEY UPDATE name = '" . $this->db->escape($value['name']) . "'");
        }

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

        $this->cache->delete('download');
    }

    public function editDownload($download_id, $data, $allLanguages)
    {
        $queryDateAdded = ", date_added = " . (!empty($data['date_added']) ? "'" . $this->db->escape($data['date_added']) . "'" : "NOW()");

        $this->db->query("UPDATE " . DB_PREFIX . "download SET filename = '" . $this->db->escape($data['filename']) . "', mask = '" . $this->db->escape($data['mask']) . "'" . $queryDateAdded .  " WHERE download_id='" . (int)$download_id . "'");

        $language_ids = array();
        foreach ($allLanguages as $language) {
            $language_ids[] = $language['language_id']; 
        }

        $this->db->query("DELETE FROM " . DB_PREFIX . "download_description WHERE download_id = '" . (int)$download_id . "' AND language_id NOT IN (" . implode(',', $language_ids) . ")");

        foreach ($data['download_description'] as $language_id => $value) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "download_description SET download_id = '" . (int)$download_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "' ON DUPLICATE KEY UPDATE name = '" . $this->db->escape($value['name']) . "'");
        }

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

        $this->cache->delete('download');
    }
    
    public function addDownloadLanguages(&$data, $allLanguages)
    {
        // Add Downloads Languages
        if (!empty($data['download_description'])) {
            $language_entries = array_keys($data['download_description']);
            foreach ($allLanguages as $language) {
                if (!in_array($language['language_id'], $language_entries)) {
                    $data['download_description'][$language['language_id']] = array(
                        'name' => $data['download_description'][$language_entries[0]]['name']
                    );
                }
            }
        }
    }

    public function deleteDownloads()
    {
        $this->load->model('catalog/download');

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

        foreach ($ids->rows as $row) {
            $this->model_catalog_download->deleteDownload($row['download_id']);
        }
    }
}