<?php 
class ModelExtensionModuleExcelportfilter extends ModelExtensionModuleExcelport
{
    public function importXLSFilters($language, $allLanguages, $file, $addAsNew = false)
    {
        $this->language->load('extension/module/excelport');

        $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;

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

        // Create new PHPExcel object
        $madeImports = false;
        $objReader = new PHPExcel_Reader_Excel2007();
        $objReader->setReadDataOnly(true);
        $objReader->setLoadSheetsOnly(array("Filters", "Filter Groups", "filters", "filter groups"));
        $objPHPExcel = $objReader->load($file);
        $progress['importingFile'] = substr($file, strripos($file, '/') + 1);
        $filtersSheet = 0;
        $filterGroupsSheet = 1;

        $filtersSheetObj = $objPHPExcel->setActiveSheetIndex($filtersSheet);
        $filterGroupsSheetObj = $objPHPExcel->setActiveSheetIndex($filterGroupsSheet);

        $progress['all'] = -1; //(int)(($filtersSheetObj->getHighestRow() - 2)/$this->productSize);
        $this->setProgress($progress);

        $filter_map = array(
            'filter_id'       => 0,
            'name'            => 1,
            'filter_group_id' => 2,
            'sort_order'      => 3
        );

        $filter_group_map = array(
            'filter_group_id' => 0,
            'name'            => 1,
            'sort_order'      => 2
        );

        $source1 = array(0,2);
        $source2 = array(0,2);

        do {
            $this->custom_set_time_limit();
            $filter_group_name = strval($filterGroupsSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source2[0] + $filter_group_map['name']) . ($source2[1]))->getValue());
            if (!empty($filter_group_name)) {
                $filter_group_id = $filterGroupsSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source2[0] + $filter_group_map['filter_group_id']) . ($source2[1]))->getValue();
                $filter_sort_order = (int)str_replace(' ', '', $filterGroupsSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source2[0] + $filter_group_map['sort_order']) . ($source2[1]))->getValue());
                
                $filter_group = array(
                    'filter_group_description' => array(
                        $language => array(
                            'name' => $filter_group_name
                        )
                    ),
                    'sort_order' => $filter_sort_order
                );
                
                // Extras
                foreach ($this->extraGeneralFields['FilterGroups'] as $extra) {
                    if (!empty($extra['name']) && !empty($extra['column_light'])) {
                        $filter_group[$extra['name']] = $filterGroupsSheetObj->getCell($extra['column_light'] . $source2[1])->getValue(); 
                    }
                }
                
                if (!empty($filter_group_id)) {
                    $exists = false;
                    $existsQuery = $this->db->query("SELECT filter_group_id FROM " . DB_PREFIX . "filter_group WHERE filter_group_id = ".$filter_group_id);
                    
                    $exists = $existsQuery->num_rows > 0;
                            
                    if ($exists) {
                        $this->editFilterGroup($filter_group_id, $filter_group, $allLanguages);
                    } else {
                        $this->addFilterGroup($filter_group_id, $filter_group, $allLanguages);
                    }
                } else {
                    $this->addFilterGroup('', $filter_group, $allLanguages);
                }
                
                $progress['current']++;
                $madeImports = true;
                $this->setProgress($progress);
            }
            $source2[1] += 1;
        } while (!empty($filter_group_name));

        do {
            $this->custom_set_time_limit();
            $filter_name = strval($filtersSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source1[0] + $filter_map['name']) . ($source1[1]))->getValue());
            if (!empty($filter_name)) {
                $filter_id = (int)$filtersSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source1[0] + $filter_map['filter_id']) . ($source1[1]))->getValue();
                $filter_group_id = $filtersSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source1[0] + $filter_map['filter_group_id']) . ($source1[1]))->getValue();
                $filter_sort_order = (int)str_replace(' ', '', $filtersSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source1[0] + $filter_map['sort_order']) . ($source1[1]))->getValue());
                
                $filter = array(
                    'filter_description' => array(
                        $language => array(
                            'name' => $filter_name
                        )
                    ),
                    'filter_group_id' => $filter_group_id,
                    'sort_order' => $filter_sort_order
                );
                
                // Extras
                foreach ($this->extraGeneralFields['Filters'] as $extra) {
                    if (!empty($extra['name']) && !empty($extra['column_light'])) {
                        $filter[$extra['name']] = $filtersSheetObj->getCell($extra['column_light'] . $source1[1])->getValue();    
                    }
                }
                
                if (!$addAsNew) {
                    $exists = false;
                    $existsQuery = $this->db->query("SELECT filter_id FROM " . DB_PREFIX . "filter WHERE filter_id = ".$filter_id);
                    
                    $exists = $existsQuery->num_rows > 0;
                            
                    if ($exists) {
                        $this->editFilter($filter_id, $filter, $allLanguages);
                    } else {
                        $this->addFilter($filter_id, $filter, $allLanguages);
                    }
                } else {
                    $this->addFilter('', $filter, $allLanguages);
                }
                
                $progress['current']++;
                $madeImports = true;
                $this->setProgress($progress);
            }
            $source1[1] += 1;
        } while (!empty($filter_name));
        
        $progress['done'] = true;
        $progress['importedCount'] = 0;
        array_shift($this->session->data['uploaded_files']);
        
        $this->setProgress($progress);
        
        $this->config->set('config_language_id', $default_language);
    }

    public function exportXLSFilters($language, $destinationFolder = '', $export_filters = array())
    {
        $this->language->load('extension/module/excelport');
        $this->folderCheck($destinationFolder);

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

        $file = IMODULE_ROOT . 'system/library/vendor/isenselabs/excelport/excelport/template_filter.xlsx';

        $default_language = $this->config->get('config_language_id');
        $this->config->set('config_language_id', $language);
        require_once(IMODULE_ROOT.'system/library/vendor/isenselabs/excelport/phpexcel/PHPExcel.php');

        if (!empty($progress['populateAll'])) {
            $all = $this->db->query($this->getFiltersQuery($export_filters, $language, true));
            $progress['all'] = $all->num_rows ? (int)$all->row['count'] : 0;
            unset($progress['populateAll']);
            $this->setProgress($progress);
        }

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

        $filtersSheet = 0;
        $filterGroupsSheet = 1;

        $filter_map = array(
            'filter_id'       => 0,
            'name'            => 1,
            'filter_group_id' => 2,
            'sort_order'      => 3
        );

        $filter_group_map = array(
            'filter_group_id' => 0,
            'name'            => 1,
            'sort_order'      => 2
        );

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

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

        $filters_target = array(0,2);

        $this->load->model('localisation/language');
        $languageQuery = $this->model_localisation_language->getLanguage($this->config->get('config_language_id'));

        $name = 'filters_excelport_' . $languageQuery['code'] . '_' . str_replace('/', '_', substr(HTTP_CATALOG, 7, strlen(HTTP_CATALOG) - 8)) . '_' . date("Y-m-d_H-i-s") . '_' . $progress['current'];
        $resultName = $name . '.xlsx';
        $result = $destinationFolder . '/' . $name . '.xlsx';

        $objPHPExcel = PHPExcel_IOFactory::load($file);

        // Set document properties
        $objPHPExcel->getProperties()
                    ->setCreator($this->user->getUserName())
                    ->setLastModifiedBy($this->user->getUserName())
                    ->setTitle($name)
                    ->setSubject($name)
                    ->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);

        $filtersSheetObj = $objPHPExcel->setActiveSheetIndex($filtersSheet);
        $filterGroupsSheetObj = $objPHPExcel->setActiveSheetIndex($filterGroupsSheet);

        $extra_select = "";

        $this->db->query("SET SESSION group_concat_max_len = 1000000;");

        $filters_result = $this->db->query($this->getFiltersQuery($export_filters, $language) . " ORDER BY f.filter_id");

        foreach ($this->extraGeneralFields['Filters'] as $extra) {
            if (!empty($extra['title']) && !empty($extra['column_light'])) {
                $filtersSheetObj->setCellValueExplicit($extra['column_light'] . '1', $extra['title'], PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }

        if ($filters_result->num_rows > 0) {
            foreach ($filters_result->rows as $myFiltersIndex => $filter_row) {

                $this->getData('Filters', $filter_row);
                $filter_row['sort_order'] = empty($filter_row['sort_order']) ? '0' : $filter_row['sort_order'];
                if (empty($filter_row['name'])) $filter_row['name'] = '-';

                // Add data
                // Extras
                foreach ($extras as $name => $position) {
                    $filtersSheetObj->setCellValueExplicit($position . ($filters_target[1]), empty($filter_row[$name]) ? '' : $filter_row[$name], PHPExcel_Cell_DataType::TYPE_STRING);
                }
                // General
                foreach ($filter_map as $name => $position) {
                    $filtersSheetObj->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($filters_target[0] + $position) . ($filters_target[1]), empty($filter_row[$name]) && $filter_row[$name] !== '0' ? '' : $filter_row[$name], PHPExcel_Cell_DataType::TYPE_STRING);
                }

                $filters_target[1] = $filters_target[1] + 1;
                $progress['current']++;
                $progress['memory_get_usage'] = round(memory_get_usage(true)/(1024*1024));
                $progress['percent'] = 100 / ($filters_result->num_rows / $progress['current']);

                $this->setProgress($progress);
            }
        } else {
            $progress['done'] = true;
        }

        $filter_groups_target = array(0,2);
        $filter_groups_result = $this->db->query("SELECT * FROM " . DB_PREFIX . "filter_group fg LEFT JOIN " . DB_PREFIX . "filter_group_description fgd ON (fg.filter_group_id = fgd.filter_group_id AND fgd.language_id = '" . $language . "') GROUP BY fg.filter_group_id ORDER BY fg.filter_group_id");

        foreach ($this->extraGeneralFields['FilterGroups'] as $extra) {
            if (!empty($extra['title']) && !empty($extra['column_light'])) {
                $filtersSheetObj->setCellValueExplicit($extra['column_light'] . '1', $extra['title'], PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }

        $all = $this->db->query("SELECT DISTINCT fg.filter_group_id, COUNT(*) AS total FROM " . DB_PREFIX . "filter_group fg");
        $progress['all'] = (int)$all->row['total'];
        $progress['current'] = 0;

        if ($filter_groups_result->num_rows > 0) {
            foreach ($filter_groups_result->rows as $myFilterGroupsIndex => $filter_group_row) {

                $this->getData('FilterGroups', $filter_group_row);
                $filter_group_row['sort_order'] = empty($filter_group_row['sort_order']) ? '0' : $filter_group_row['sort_order'];
                if (empty($filter_group_row['name'])) $filter_group_row['name'] = '-';

                // Add data
                // Extras
                foreach ($extras_groups as $name => $position) {
                    $filterGroupsSheetObj->setCellValueExplicit($position . ($filter_groups_target[1]), empty($filter_group_row[$name]) ? '' : $filter_group_row[$name], PHPExcel_Cell_DataType::TYPE_STRING);
                }
                // General
                foreach ($filter_group_map as $name => $position) {
                    $filterGroupsSheetObj->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($filter_groups_target[0] + $position) . ($filter_groups_target[1]), empty($filter_group_row[$name]) && $filter_group_row[$name] !== '0' ? '' : $filter_group_row[$name], PHPExcel_Cell_DataType::TYPE_STRING);
                }

                $filter_groups_target[1] = $filter_groups_target[1] + 1;
                $progress['current']++;
                $progress['memory_get_usage'] = round(memory_get_usage(true)/(1024*1024));
                $progress['percent'] = 100 / ($filter_groups_result->num_rows / $progress['current']);

                $this->setProgress($progress);
            }
        } else {
            $progress['done'] = true;
        }

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

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

        try {
            $this->custom_set_time_limit();

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

            $objWriter->save($result);

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

        $progress['done'] = true;
        $this->setProgress($progress);

        return true;
    }

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

        $join_rules = array(
            'filter_group' => "LEFT JOIN " . DB_PREFIX . "filter_group fg ON (f.filter_group_id = fg.filter_group_id)",
            'filter_description' => "LEFT JOIN " . DB_PREFIX . "filter_description fd ON (f.filter_id = fd.filter_id AND fd.language_id = '" . $language . "')",
            'filter_group_description' => "LEFT JOIN " . DB_PREFIX . "filter_group_description fgd ON (fg.filter_group_id = fgd.filter_group_id AND fgd.language_id = '" . $language . "')"
        );

        $joins = array();
        $joins['filter_group'] = $join_rules['filter_group'];
        $joins['filter_description'] = $join_rules['filter_description'];
        $joins['filter_group_description'] = $join_rules['filter_group_description'];

        $wheres = array();

        foreach ($filters as $i => $filter) {
            if (is_array($filter)) {
                if (!array_key_exists($this->conditions['Filters'][$filter['Field']]['join_table'], $joins) && array_key_exists($this->conditions['Filters'][$filter['Field']]['join_table'], $join_rules)) {
                    $joins[$this->conditions['Filters'][$filter['Field']]['join_table']] = $join_rules[$this->conditions['Filters'][$filter['Field']]['join_table']];
                }
                $condition = str_replace(array('{FIELD_NAME}', '{WORD}'), array($this->conditions['Filters'][$filter['Field']]['field_name'], stripos($this->conditions['Filters'][$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(*)" : "*, fd.name AS name, f.*";

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

        return $query;
    }

    public function addFilterGroup($filter_group_id = '', $data = array(), $allLanguages = array())
    {
        $this->addFilterGroupLanguages($data, $allLanguages);
        
        $filter_group_id = trim($filter_group_id);
        
        $this->db->query("INSERT INTO " . DB_PREFIX . "filter_group SET ".(!empty($filter_group_id) ? "filter_group_id = '" . (int)trim($filter_group_id) . "', " : "")."sort_order = '" . (int)$data['sort_order'] . "'");
        
        $filter_group_id = $this->db->getLastId();
        
        foreach ($data['filter_group_description'] as $language_id => $value) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "filter_group_description SET filter_group_id = '" . (int)$filter_group_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "'");
        }
        
        // Extras
        foreach ($this->extraGeneralFields['FilterGroups'] as $extra) {
            if (!empty($extra['eval_add'])) {
                eval($extra['eval_add']);
            }
        }
    }

    public function editFilterGroup($filter_group_id, $data, &$languages)
    {
        $this->db->query("UPDATE " . DB_PREFIX . "filter_group SET sort_order = '" . (int)$data['sort_order'] . "' WHERE filter_group_id = '" . (int)$filter_group_id . "'");
        
        $language_ids = array();
        foreach ($languages as $language) {
            $language_ids[] = $language['language_id']; 
        }
        
        $this->db->query("DELETE FROM " . DB_PREFIX . "filter_group_description WHERE filter_group_id = '" . (int)$filter_group_id . "' AND language_id NOT IN (" . implode(',', $language_ids) . ")");

        foreach ($data['filter_group_description'] as $language_id => $value) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "filter_group_description SET filter_group_id = '" . (int)$filter_group_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['FilterGroups'] as $extra) {
            if (!empty($extra['eval_edit'])) {
                eval($extra['eval_edit']);
            }
        }
    }

    public function addFilterGroupLanguages(&$data, $allLanguages) {
        // Add Filter Group Description Languages
        if (!empty($data['filter_group_description'])) {
            $entered_keys = array_keys($data['filter_group_description']);
            foreach ($allLanguages as $language) {
                if (!in_array($language['language_id'], $entered_keys)) {
                    $data['filter_group_description'][$language['language_id']] = array(
                        'name' => $data['filter_group_description'][$entered_keys[0]]['name']
                    );
                }
            }
        }
    }

    public function addFilter($filter_id = '', $data = array(), $allLanguages = array()) {
        $this->addFilterLanguages($data, $allLanguages);
        
        $filter_id = trim($filter_id);
        
        $this->db->query("INSERT INTO " . DB_PREFIX . "filter SET ".(!empty($filter_id) ? "filter_id = '" . (int)trim($filter_id) . "', " : "")."filter_group_id = '" . (int)$data['filter_group_id'] . "', sort_order = '" . (int)$data['sort_order'] . "'");
        
        $filter_id = $this->db->getLastId();
        
        $language_ids = array();
        foreach ($allLanguages as $language) {
            $language_ids[] = $language['language_id']; 
        }
        
        $this->db->query("DELETE FROM " . DB_PREFIX . "filter_description WHERE filter_id = '" . (int)$filter_id . "' AND language_id NOT IN (" . implode(',', $language_ids) . ")");
        
        foreach ($data['filter_description'] as $language_id => $value) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "filter_description SET filter_id = '" . (int)$filter_id . "', language_id = '" . (int)$language_id . "', filter_group_id = '" . (int)$data['filter_group_id'] . "', name = '" . $this->db->escape($value['name']) . "' ON DUPLICATE KEY UPDATE name = '" . $this->db->escape($value['name']) . "'");
        }
        
        // Extras
        foreach ($this->extraGeneralFields['Filters'] as $extra) {
            if (!empty($extra['eval_add'])) {
                eval($extra['eval_add']);
            }
        }
    }

    public function editFilter($filter_id, $data, &$languages) {
        $this->db->query("UPDATE " . DB_PREFIX . "filter SET filter_group_id = '" . (int)$data['filter_group_id'] . "', sort_order = '" . (int)$data['sort_order'] . "' WHERE filter_id = '" . (int)$filter_id . "'");
        
        $language_ids = array();
        foreach ($languages as $language) {
            $language_ids[] = $language['language_id']; 
        }
        
        $this->db->query("DELETE FROM " . DB_PREFIX . "filter_description WHERE filter_id = '" . (int)$filter_id . "' AND language_id NOT IN (" . implode(',', $language_ids) . ")");

        foreach ($data['filter_description'] as $language_id => $value) {
            $this->db->query("INSERT INTO " . DB_PREFIX . "filter_description SET filter_id = '" . (int)$filter_id . "', language_id = '" . (int)$language_id . "', filter_group_id = '" . (int)$data['filter_group_id'] . "', name = '" . $this->db->escape($value['name']) . "' ON DUPLICATE KEY UPDATE name = '" . $this->db->escape($value['name']) . "'");
        }
        
        // Extras
        foreach ($this->extraGeneralFields['Filters'] as $extra) {
            if (!empty($extra['eval_edit'])) {
                eval($extra['eval_edit']);
            }
        }
    }

    public function addFilterLanguages(&$data, $allLanguages) {
        // Add Filter Description Languages
        if (!empty($data['filter_description'])) {
            $entered_keys = array_keys($data['filter_description']);
            foreach ($allLanguages as $language) {
                if (!in_array($language['language_id'], $entered_keys)) {
                    $data['filter_description'][$language['language_id']] = array(
                        'name' => $data['filter_description'][$entered_keys[0]]['name']
                    );
                }
            }
        }
    }

    /**
     * Delete Filters and FilterGroups
     */
    public function deleteFilters()
    {
        $this->load->model('catalog/filter');
        
        $ids = $this->db->query("SELECT filter_group_id FROM " . DB_PREFIX . "filter_group");
        
        foreach ($ids->rows as $row) {
            $this->model_catalog_filter->deleteFilter($row['filter_group_id']);  
        }
    }
}