<?php
class ModelExtensionModuleExcelportorderstatus extends ModelExtensionModuleExcelport
{
    public function importXLSOrderStatuses($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("OrderStatuses", "orderstatuses"));
        $objPHPExcel = $objReader->load($file);
        $progress['importingFile'] = substr($file, strripos($file, '/') + 1);
        
        $orderStatusSheet = 0;
        $orderStatusSheetObj = $objPHPExcel->setActiveSheetIndex($orderStatusSheet);
        
        $progress['all'] = -1; //(int)(($orderStatusSheetObj->getHighestRow() - 2)/$this->productSize);
        $this->setProgress($progress);
        
        $orderStatus_map = array(
            'order_status_id' => 0,
            'language_id'     => 1,
            'name'            => 2
        );

        $source = array(0,2);

        do {
            $this->custom_set_time_limit();
            $orderStatus_name = strval($orderStatusSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $orderStatus_map['name']) . ($source[1]))->getValue());
            if (!empty($orderStatus_name)) {
                $order_status_id = (int)$orderStatusSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $orderStatus_map['order_status_id']) . ($source[1]))->getValue();
                $language_id = $orderStatusSheetObj->getCell(PHPExcel_Cell::stringFromColumnIndex($source[0] + $orderStatus_map['language_id']) . ($source[1]))->getValue();

                $order_status = array(
                    'language_id' => $language_id,
                    'name'        => $orderStatus_name
                );
                
                // Extras
                foreach ($this->extraGeneralFields['OrderStatuses'] as $extra) {
                    if (!empty($extra['name']) && !empty($extra['column_light'])) {
                        $order_status[$extra['name']] = $orderStatusSheetObj->getCell($extra['column_light'] . $source[1])->getValue();    
                    }
                }

                if (!$addAsNew) {
                    $exists = false;
                    $existsQuery = $this->db->query("SELECT order_status_id FROM " . DB_PREFIX . "order_status WHERE order_status_id = ". (int)$order_status_id . " AND language_id = '" . (int)$language_id . "'");
                    
                    $exists = $existsQuery->num_rows > 0;
                            
                    if ($exists) {
                        $this->editOrderStatuses($order_status_id, $order_status, $allLanguages);
                    } else {
                        $this->addOrderStatuses($order_status_id, $order_status, $allLanguages);
                    }
                } else {
                    $this->addOrderStatuses('', $order_status, $allLanguages);
                }
                
                $progress['current']++;
                $madeImports = true;
                $this->setProgress($progress);
            }
            $source[1] += 1;
        } while (!empty($orderStatus_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 exportXLSOrderStatuses($language, $destinationFolder = '', $exportLimit = 800, $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_order_status.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->getQuery($export_filters, $language, true));
            $progress['all'] = $all->num_rows ? (int)$all->row['count'] : 0;
            unset($progress['populateAll']);
            $this->setProgress($progress);
        }
        
        $this->setData('OrderStatuses', $destinationFolder, $language);

        $orderStatusSheet = 0;
        $orderStatusMetaSheet = 1;

        $target = array(0,2);

        $this->load->model('localisation/language');
        $languageQuery = $this->model_localisation_language->getLanguage($this->config->get('config_language_id'));
        
        $name = 'order_statuses_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);
        
        $orderStatusSheetObj = $objPHPExcel->setActiveSheetIndex($orderStatusSheet);
        $metaSheetObj = $objPHPExcel->setActiveSheetIndex($orderStatusMetaSheet);

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

        $order_statuses_generals = array(
            'order_status_id' => 0,
            'language_id'     => 1,
            'name'            => 2
        );

        $this->db->query("SET SESSION group_concat_max_len = 1000000;");
        
        $order_statuses_result = $this->db->query($this->getQuery($export_filters, $language) . " ORDER BY os.order_status_id LIMIT ". $progress['current'] . ", " . $exportLimit);
        
        foreach ($this->extraGeneralFields['OrderStatuses'] as $extra) {
            if (!empty($extra['title']) && !empty($extra['column_light'])) {
                $orderStatusSheetObj->setCellValueExplicit($extra['column_light'] . '1', $extra['title'], PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }

        if ($order_statuses_result->num_rows > 0) {
            foreach ($order_statuses_result->rows as $row) {
                
                $this->getData('OrderStatuses', $row);
                
                // Add data
                // Extras
                foreach ($extras as $name => $position) {
                    $orderStatusSheetObj->setCellValueExplicit($position . ($target[1]), empty($row[$name]) ? '' : $row[$name], PHPExcel_Cell_DataType::TYPE_STRING);
                }
                // General
                foreach ($order_statuses_generals as $name => $position) {
                    $orderStatusSheetObj->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($target[0] + $position) . ($target[1]), empty($row[$name]) && $row[$name] !== '0' ? '' : $row[$name], PHPExcel_Cell_DataType::TYPE_STRING);
                }

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

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

        $join_rules = array();
        $joins = array();
        $wheres = array();
        
        foreach ($filters as $i => $filter) {
            if (is_array($filter)) {
                if (!array_key_exists($this->conditions['OrderStatuses'][$filter['Field']]['join_table'], $joins) && array_key_exists($this->conditions['OrderStatuses'][$filter['Field']]['join_table'], $join_rules)) {
                    $joins[$this->conditions['OrderStatuses'][$filter['Field']]['join_table']] = $join_rules[$this->conditions['OrderStatuses'][$filter['Field']]['join_table']];
                }
                $condition = str_replace(array('{FIELD_NAME}', '{WORD}'), array($this->conditions['OrderStatuses'][$filter['Field']]['field_name'], stripos($this->conditions['OrderStatuses'][$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 . "order_status os " . implode(" ", $joins) . " WHERE os.language_id = " . (int)$language_id . (!empty($wheres) ? " AND (" . implode(" " . $filters['Conjunction'] . " ", $wheres) . ")" : "") . " GROUP BY os.order_status_id" . ($count ? ") as count_table" : "");

        return $query;
    }

    public function addOrderStatuses($order_status_id = '', $data = array(), $allLanguages = array())
    {
        $order_status_id = trim($order_status_id);
        $new_query = (!empty($order_status_id) ? " order_status_id = '" . (int)$order_status_id . "', " : "");
        
        $language_ids = array();
        foreach ($allLanguages as $language) {
            $language_ids[] = $language['language_id']; 
        }
        
        $this->db->query("DELETE FROM " . DB_PREFIX . "order_status WHERE order_status_id = '" . (int)$order_status_id . "' AND language_id NOT IN (" . implode(',', $language_ids) . ")");

        $this->db->query("INSERT INTO " . DB_PREFIX . "order_status SET " . $new_query . " language_id = '" . (int)$data['language_id'] . "', name = '" . $this->db->escape($data['name']) . "'");
        
        // Extras
        foreach ($this->extraGeneralFields['OrderStatuses'] as $extra) {
            if (!empty($extra['eval_add'])) {
                eval($extra['eval_add']);
            }
        }
    }

    public function editOrderStatuses($order_status_id, $data, $allLanguages)
    {
        $order_status_id = trim($order_status_id);

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

        $this->db->query("DELETE FROM " . DB_PREFIX . "order_status WHERE order_status_id = '" . (int)$order_status_id . "' AND language_id NOT IN (" . implode(',', $language_ids) . ")");
        $this->db->query("UPDATE " . DB_PREFIX . "order_status SET name = '" . $this->db->escape($data['name']) . "' WHERE order_status_id = '" . (int)$order_status_id . "' AND language_id = '" . (int)$data['language_id'] . "'");
        
        // Extras
        foreach ($this->extraGeneralFields['OrderStatuses'] as $extra) {
            if (!empty($extra['eval_edit'])) {
                eval($extra['eval_edit']);
            }
        }
    }

    public function deleteOrderStatuses()
    {
        $this->load->model('localisation/order_status');
        
        $ids = $this->db->query("SELECT order_status_id FROM " . DB_PREFIX . "order_status");
        
        foreach ($ids->rows as $row) {
            $this->model_localisation_order_status->deleteOrderStatus($row['order_status_id']);  
        }
    }
}