<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

namespace modules\extendedDatatable\code;

/**
 * Description of ExtendedDatatable
 *
 * @author maros
 */
abstract class ExtendedDatatable extends \app\classes\ModuleConfig {

    CONST COLUMN_TITLE = "title";
    CONST COLUMN_CONTROLLER = "controller";
    CONST COLUMN_VISIBLE = "visible";
    CONST COLUMN_ALIAS = "alias";
    CONST COLUMN_FILTER = "filter";
    CONST COLUMN_DISABLE_FILTER = "disable_filter";
    CONST COLUMN_TRANSLATE_FILTER_OPTIONS = "translate_filter_options";
    CONST COLUMN_TRANSLATE_FILTER_OPTIONS_PREFIX = "translate_filter_options_prefix";
    CONST COLUMN_SORTING_DISABLED = "sorting_disabled";
    CONST CONFIG_TABLES = "TABLES";
    CONST CONFIG_SQL = "SQL";
    CONST CONFIG_SHIFT = "SHIFT";
    CONST CONFIG_GROUP_BY = "GROUP_BY";
    CONST PROCESS_MODE_DATATABLE_API = "DATATABLE_API";
    CONST PROCESS_MODE_EXPORT_CSV = "export-csv";
    CONST PROCESS_MODE_EXPORT_XLSX = "export-xlsx";

    protected $tableConfig;
    protected $columns = array();
    protected $post;
    protected $columnIndexToName = array();
    protected $db;
    protected $visibleColumns;
    protected $columnsControllers;
    protected $t;
    protected $recordsFiltered;
    protected $recordsTotal;
    protected $data;
    protected $sqlFiltered;
    protected $bindArrayFiltered = array();
    protected $debug = false;
    protected $sqlNoLimit;
    protected $additionalResponseData = array();
    protected $finalBindArray = array();
    protected $finalSql;
    protected $finalTable;
    protected $processMode = self::PROCESS_MODE_DATATABLE_API;
    protected $baseSql;
    protected $dataCollection;
    protected $activeFilterColumns = array();

    public function __construct($class, $tableConfig) {
        parent::__construct($class);
        $this->t = \app\classes\Translator::getInstance();
        $this->db = \app\classes\PDODatabase::getInstance();
        $this->tableConfig = $tableConfig;
        $shift = intval($this->getIncConfig(self::CONFIG_SHIFT, false, $this->tableConfig)) - 1;
        $this->columnIndexToName = array($shift => null);
        foreach ($this->getIncConfig(false, false, $tableConfig) as $key => $val) {
            if (is_array($val) && !preg_match('/[A-Z]/', $key)) {
                $this->columns[$key] = $val;
                if ($val[self::COLUMN_VISIBLE]) {
                    $this->columnIndexToName[] = $key;
                }
            }
        }
    }

    public function getColumns() {
        return $this->columns;
    }

    /**
     * @return ColumnController
     */
    public function getColumnController($column) {
        if (key_exists($column, $this->columns)) {
            $columnConfig = $this->columns[$column];
            if (class_exists($columnConfig[self::COLUMN_CONTROLLER])) {
                $controller = new $columnConfig[self::COLUMN_CONTROLLER]($column, $columnConfig);
                return $controller;
            }
        }
    }

    public function printDatatableHeadFilters($params = array()) {
        foreach ($this->columns as $key => $column) {
            if ($column[self::COLUMN_VISIBLE]) {
                echo "<td>";
                if (class_exists($column[self::COLUMN_CONTROLLER])) {
                    $controller = new $column[self::COLUMN_CONTROLLER]($key, $column);
                    $controller->renderFilterInput($params = array());
                }
                echo "</td>";
            }
        }
    }

    public function prepareAjaxTableData($post) {
        $this->post = $post;

        if ($this->getPost("performAdditionalAction") === "export-csv") {
            $this->processMode = self::PROCESS_MODE_EXPORT_CSV;
        }

        if ($this->getPost("performAdditionalAction") === "export-xlsx") {
            $this->processMode = self::PROCESS_MODE_EXPORT_XLSX;
        }

        $sqlFilterConditions = array();
        $sqlHavingFilterConditions = array();
        $bindArray = array();
        $bindArrayTotal = array();
        $tables = $this->getIncConfig(self::CONFIG_TABLES, false, $this->tableConfig);
        foreach ($this->columns as $column => $config) {
            if ($this->getPost($column) !== NULL) {
                $havingCond = strpos($column, ".") === false;
                $table = $havingCond ? "" : substr($column, 0, strpos($column, "."));
                $inputName = $havingCond ? $column : substr($column, strpos($column, ".") + 1);
                $isUsingLikeConditions = false;
                try {
                    if (class_exists($config[self::COLUMN_CONTROLLER])) {
                        $controller = new $config[self::COLUMN_CONTROLLER]($column, $config);
                        $value = $controller->getDatatableSqlConditionValue($this->getPost($column));
                        $sqlCondition = $controller->getDatatableSqlCondition($column, $havingCond ? $inputName : $table . "_" . $inputName, $value);
                        $isUsingLikeConditions = $controller->isUsingLikeConditions();
                    }
                } catch (\Exception $ex) {
                    
                }

                if (strlen($sqlCondition) > 0 && (is_array($value) || strlen($value) > 0)) {
                    $this->activeFilterColumns[$column] = $config[self::COLUMN_TITLE];
                    if ($havingCond) {
                        $sqlHavingFilterConditions[] = $sqlCondition;
                    } else {
                        $sqlFilterConditions[] = $sqlCondition;
                    }
                    if (is_array($value)) {
                        for ($i = 0; $i < count($value); $i++) {
                            if ($value[$i] === NULL) {
                                continue;
                            }
                            $bindArray[($havingCond ? $inputName : $table . "_" . $inputName) . $i] = ($isUsingLikeConditions ? "%" : "") . $value[$i] . ($isUsingLikeConditions ? "%" : "");
                        }
                    } else {
                        $bindArray[$havingCond ? $inputName : $table . "_" . $inputName] = ($isUsingLikeConditions ? "%" : "") . $value . ($isUsingLikeConditions ? "%" : "");
                    }
                }
            }
        }

        $sqls = $this->getIncConfig(self::CONFIG_SQL, false, $this->tableConfig);

        $columns = array_keys($this->columns);

        foreach ($columns as $key => $val) {
            if (key_exists(self::COLUMN_ALIAS, $this->columns[$val]) && $this->columns[$val][self::COLUMN_ALIAS] !== false) {
                $columns[$key] = $this->columns[$val][self::COLUMN_ALIAS];
            }
        }

        $sqls[0] = "SELECT " . implode(", ", $columns) . " " . $sqls[0];

        if (isset($post["archiveEnabled"]) && $post["archiveEnabled"] === "enabled") {
            $sqlFilterConditions[] = $tables[0] . ".state=:ARCHIVED";
            $bindArrayTotal["ARCHIVED"] = \app\constants\State::ARCHIVED;
            $sqlTotalAdd = $tables[0] . ".state=:ARCHIVED";
        } else {
            $sqlFilterConditions[] = $tables[0] . ".state!=:DELETED";
            $bindArrayTotal["DELETED"] = \app\constants\State::DELETED;
            $sqlFilterConditions[] = $tables[0] . ".state!=:ARCHIVED";
            $bindArrayTotal["ARCHIVED"] = \app\constants\State::ARCHIVED;
            $sqlTotalAdd = $tables[0] . ".state!=:ARCHIVED AND " . $tables[0] . ".state!=:DELETED ";
        }

        $bindArray = $bindArray + $bindArrayTotal;
        $sql = implode(" ", $sqls);
        $this->baseSql = $sql;
        $sqlTotal = $sql;

        $sqlTotal .= (substr_count($sql, " FROM ") !== substr_count($sql, " WHERE ")) ? " WHERE " . $sqlTotalAdd : " AND (" . $sqlTotalAdd . ")";

        if (count($sqlFilterConditions) !== 0) {
            $sql .= (substr_count($sql, " FROM ") !== substr_count($sql, " WHERE ")) ? " WHERE " . implode(" AND ", $sqlFilterConditions) : " AND (" . implode(" AND ", $sqlFilterConditions) . ")";
        }

        if ($this->getFixedSqlCondition() !== "") {
            $sql .= " AND( " . $this->getFixedSqlCondition() . ") ";
            $sqlTotal .= " AND( " . $this->getFixedSqlCondition() . ") ";
        }

        $bindArray = $bindArray + $this->getFixedBindArray();
        $bindArrayTotal = $bindArrayTotal + $this->getFixedBindArray();

        $groupBy = $this->getIncConfig(self::CONFIG_GROUP_BY, false, $this->tableConfig);
        if ($groupBy !== false) {
            $sql .= " " . $groupBy;
            $sqlTotal .= " " . $groupBy;
        }



//        if ($groupBy === false) {
//            $sqlRecordsFiltered .= " GROUP BY " . $tables[0] . ".id ";
//        }

        if (count($sqlHavingFilterConditions) !== 0) {
            $addition = strpos($sql, " HAVING ") === false ? " HAVING " . implode(" AND ", $sqlHavingFilterConditions) : " AND (" . implode(" AND ", $sqlHavingFilterConditions) . ")";
            $sql .= $addition;
        }

        if ($this->getFixedHavingSqlCondition() !== "") {
            $addition = strpos($sql, " HAVING ") === false ? " HAVING " . $this->getFixedHavingSqlCondition() : " AND (" . $this->getFixedHavingSqlCondition() . ")";
            $sql .= $addition;
            $sqlTotal .= $addition;
        }

        $sqlRecordsFiltered = $sql;

        if (isset($post["order"]) && is_array($post["order"]) && isset($post["order"][0])) {
            if (isset($post["order"][0]["column"]) && isset($this->columnIndexToName[$post["order"][0]["column"]])) {
                $sql .= " ORDER BY " . $this->columnIndexToName[$post["order"][0]["column"]] . " ";
                $sql .= (isset($post["order"][0]["dir"]) && $post["order"][0]["dir"] === "desc") ? " DESC " : " ASC ";
            }
        }

        $this->sqlNoLimit = $sql;

        $sql .= " LIMIT " . intval($post["length"]) . " OFFSET " . intval($post["start"]);


        if ($this->debug) {
            echo $sql;
            var_dump($bindArray);
            echo "<br><br>";
            print_binded_sql($sql, $bindArray);
        }

        $this->sqlFiltered = $sqlRecordsFiltered;
        $this->bindArrayFiltered = $bindArray;

        $this->initRecordsTotal($sqlTotal, $bindArrayTotal);
        $this->initRecordsFiltered($sqlRecordsFiltered, $bindArray);

        $this->finalBindArray = $bindArray;
        $this->finalSql = $sql;
        $this->finalTable = $tables[0];
        $this->dataCollection = new \app\classes\PDOCollection($tables[0], "id", $sql, $bindArray);
        $this->data = $this->dataCollection->toArray();
    }

    protected function initRecordsTotal($sql, $bindArray) {
        $this->recordsTotal = $this->db->sqlCommand("SELECT " . substr($sql, 6), $bindArray);
        $this->recordsTotal = $this->db->getValue("SELECT FOUND_ROWS()");
    }

    protected function initRecordsFiltered(&$sql, &$bindArray) {
        $this->recordsFiltered = $this->db->sqlCommand("SELECT " . substr($sql, 6), $bindArray);
        $this->recordsFiltered = $this->db->getValue("SELECT FOUND_ROWS()");
    }

    public function getReponseData() {
        $this->data = array_values($this->data);
        array_walk($this->data, array($this, "processRow"));
        foreach ($this->columns as $key => $val) {
            if ($val[self::COLUMN_VISIBLE] === true) {
                $columnRealKey = isset($val[self::COLUMN_ALIAS]) && strpos($val[self::COLUMN_ALIAS], " AS ") !== false ? explode(" AS ", $val[self::COLUMN_ALIAS])[count(explode(" AS ", $val[self::COLUMN_ALIAS])) - 1] : substr($key, strpos($key, ".") + 1);
                $this->visibleColumns[] = $columnRealKey;
                if (class_exists($val[self::COLUMN_CONTROLLER])) {
                    $this->columnsControllers[$columnRealKey] = new $val[self::COLUMN_CONTROLLER]($key, $val);
                }
            }
        }
        array_walk($this->data, function($array, $key) {
            foreach ($array as $ikey => $val) {
                if (!in_array($ikey, $this->visibleColumns) && !is_numeric($ikey)) {
                    unset($array[$ikey]);
                } else if (isset($this->columnsControllers[$ikey])) {
                    $array[$ikey] = ($this->columnsControllers[$ikey])->getTableValue($val);
                }
            }
            $this->data[$key] = array_values($array);
        });

        if ($this->getPost("performAdditionalAction") === self::PROCESS_MODE_EXPORT_CSV) {
            $this->exportCsv();
        }

        if ($this->getPost("performAdditionalAction") === self::PROCESS_MODE_EXPORT_XLSX) {
            $this->exportXlsx();
        }

        if (strpos($this->getPost("performAdditionalAction"), "additionalAction") !== false) {
            if (method_exists(get_called_class(), $this->getPost("performAdditionalAction"))) {
                $method = $this->getPost("performAdditionalAction");
                $this->$method();
            }
        }

        return array(
            "data" => $this->data,
            "recordsTotal" => $this->recordsTotal,
            "recordsFiltered" => $this->recordsFiltered,
            "additional" => $this->additionalResponseData
        );
    }

    abstract function processRow($array, $key, $params = array());

    protected function getPost($key) {
        $key = str_replace(".", "_", $key);
        if (isset($this->post[$key]) && is_array($this->post[$key])) {
            return $this->post[$key];
        }
        if (isset($this->post[$key]) && strlen(trim($this->post[$key])) > 0) {
            return urldecode($this->post[$key]);
        }
        return NULL;
    }

    protected function getFixedSqlCondition() {
        return "";
    }

    protected function getFixedHavingSqlCondition() {
        return "";
    }

    protected function getFixedBindArray() {
        return array();
    }

    public function enableDebug() {
        $this->debug = true;
    }

    public function getCurrentProcessMode() {
        return $this->processMode;
    }

    public function getBaseTable() {
        return $this->finalTable;
    }

    public function getBaseSql() {
        return $this->baseSql;
    }

    protected function exportCsv() {
        $this->processMode = self::PROCESS_MODE_EXPORT_CSV;
        $output = array();

        $dataSwap = $this->data;
        $data = new \app\classes\PDOCollection($this->getIncConfig(self::CONFIG_TABLES, false, $this->tableConfig)[0], "id", $this->sqlNoLimit, $this->bindArrayFiltered);
        $this->data = $data->toArray();

        $this->data = array_values($this->data);
        array_walk($this->data, array($this, "processRow"));

        $head = array();

        foreach ($this->columns as $key => $val) {
            if ($val[self::COLUMN_VISIBLE] === true) {
                $columnRealKey = isset($val[self::COLUMN_ALIAS]) && strpos($val[self::COLUMN_ALIAS], " AS ") !== false ? explode(" AS ", $val[self::COLUMN_ALIAS])[count(explode(" AS ", $val[self::COLUMN_ALIAS])) - 1] : substr($key, strpos($key, ".") + 1);
                $this->visibleColumns[] = $columnRealKey;
                if (class_exists($val[self::COLUMN_CONTROLLER])) {
                    $this->columnsControllers[$columnRealKey] = new $val[self::COLUMN_CONTROLLER]($key, $val);
                    $head[] = $val[self::COLUMN_TITLE];
                }
            }
        }


        array_walk($this->data, function($array, $key) {
            foreach ($array as $ikey => $val) {
                if (!in_array($ikey, $this->visibleColumns) && !is_numeric($ikey)) {
                    unset($array[$ikey]);
                } else if (isset($this->columnsControllers[$ikey])) {
                    $array[$ikey] = strip_tags(($this->columnsControllers[$ikey])->getTableValue($val, true));
                } else {
                    unset($array[$ikey]);
                }
            }
            $this->data[$key] = array_values($array);
        });

        $output = array_merge(array($head), $this->data);
        $this->data = $dataSwap;

        $path = "uploads/exports/" . $this->getIncConfig(self::CONFIG_TABLES, false, $this->tableConfig)[0] . "-export-" . date("Y_m_d-H_i_s") . "-" . generateRandomString(8) . ".csv";
        $fp = fopen(DOCUMENT_ROOT . $path, 'w');
        fputs($fp, $bom = ( chr(0xEF) . chr(0xBB) . chr(0xBF) ));

        foreach ($output as $fields) {
            fputcsv($fp, $fields, ";");
        }

        fclose($fp);

        $this->additionalResponseData["export"] = "/" . $path;
        return $path;
    }

    protected function exportXlsx() {
        $csv = $this->exportCsv();
        $reader = new \PHPExcel_Reader_CSV();
        $sheet = new \PHPExcel();

        $reader->setDelimiter(";");
        $reader->setEnclosure('"');
        $reader->setSheetIndex(0);

        $objPHPExcel = $reader->load(DOCUMENT_ROOT . $csv);

        $headFill = array(
            'fill' => array(
                'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => 'BDBDBD')
            )
        );

        $headFill2 = array(
            'fill' => array(
                'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                'color' => array('rgb' => 'FAFAFA')
            )
        );

        $filterValues = array("Vygenerované" => array(date("d.m.Y H:i:s")));
        foreach ($this->activeFilterColumns as $key => $val) {
            $filterValues[$key] = array();
        }

        $indexes = array_flip($this->columnIndexToName);

        foreach ($this->data as $row) {
            foreach ($this->activeFilterColumns as $key => $val) {
                if (key_exists($key, $indexes)) {
                    $filterValues[$key][$row[$indexes[$key]]] = strip_tags($row[$indexes[$key]]);
                }
            }
        }

        $objPHPExcel->getActiveSheet()->getStyle("A1:" . $objPHPExcel->getActiveSheet()->getHighestColumn() . "1")->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle("A1:" . $objPHPExcel->getActiveSheet()->getHighestColumn() . "1")->applyFromArray($headFill);



        if (count($filterValues) > 0) {
            $objPHPExcel->getActiveSheet()->insertNewRowBefore(1, count($filterValues) + 1);
            $i = 1;
            foreach ($filterValues as $key => $val) {
                $objPHPExcel->getActiveSheet()->setCellValue("A" . $i, isset($this->activeFilterColumns[$key]) ? $this->activeFilterColumns[$key] : $key);
                $objPHPExcel->getActiveSheet()->setCellValue("B" . $i, implode(", ", $val));
                $objPHPExcel->getActiveSheet()->getStyle("A" . $i)->applyFromArray($headFill);
                $objPHPExcel->getActiveSheet()->getStyle("B" . $i)->applyFromArray($headFill2);
                $objPHPExcel->getActiveSheet()->getStyle("A" . $i)->getFont()->setBold(true);
                $i++;
            }
        }

        $maxWidth = 50;
        foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
            $objPHPExcel->setActiveSheetIndex($objPHPExcel->getIndex($worksheet));
            $sheet = $objPHPExcel->getActiveSheet();
            $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
//            $cellIterator->setIterateOnlyExistingCells(true);

            /** @var \PHPExcel_Cell $cell */
            foreach ($cellIterator as $cell) {
                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
                if ($sheet->getColumnDimension($cell->getColumn())->getWidth() > $maxWidth) {
                    $sheet->getColumnDimension($cell->getColumn())->setAutoSize(false);
                    $sheet->getColumnDimension($cell->getColumn())->setWidth($maxWidth);
                }
            }

            $sheet->calculateColumnWidths();

            /** @var \PHPExcel_Cell $cell */
            foreach ($cellIterator as $cell) {
                if ($sheet->getColumnDimension($cell->getColumn())->getWidth() > $maxWidth) {
                    $sheet->getColumnDimension($cell->getColumn())->setAutoSize(false);
                    $sheet->getColumnDimension($cell->getColumn())->setWidth($maxWidth);
                }
            }
        }

        $writer = new \PHPExcel_Writer_Excel2007($objPHPExcel);
        $path = str_replace(".csv", ".xlsx", $csv);
        $writer->save($path);

        $this->additionalResponseData["export"] = "/" . $path;
        return $path;
    }

    public function getColumnIndex($column) {
        
    }

}