<?php

class SupsysticTablesPro_Importer_Type_Xlsx implements SupsysticTablesPro_Importer_Interface
{
    /**
     * {@inheritdoc}
     */
    private $settings = array();
    private $replacedArr = array();
    private $tagsCounter = 0;
    private $imgsCounter = 0;

    public function validateDateTime($format)
    {
        return function($dateStr) use ($format) {
            $date = DateTime::createFromFormat($format, $dateStr);
            return $date && $date->format($format) === $dateStr;
        };
    }

    public function findTagsSaveContentReplaceKey($text) {
      if ($this->tagsCounter==100) return $text;
      $replaceName = '#REPLACETAG#'.$this->tagsCounter;

      $regex = '@<(\w+)\b.*?>.*?</\1>@si';
      $matches = array();
      preg_match($regex,$text,$matches);
      if (!empty($matches[0])) {
        $this->replacedArr[$replaceName] = $matches[0];
        return preg_replace($regex, $replaceName, $text, 1);
      } else {
        $this->tagsCounter = 100;
        return $text;
      }

      $this->tagsCounter = $this->tagsCounter + 1;
      return $text;
    }

    public function findImgsSaveContentReplaceKey($text) {
      if ($this->imgsCounter==100) return $text;
      $replaceName = '#REPLACEIMG#'.$this->imgsCounter;

      $regex = '@<img([\w\W]+?)>@si';
      $matches = array();
      preg_match($regex,$text,$matches);
      if (!empty($matches[0])) {
        $this->replacedArr[$replaceName] = $matches[0];
        return preg_replace($regex, $replaceName, $text, 1);
      } else {
        $this->imgsCounter = 100;
        return $text;
      }

      $this->imgsCounter = $this->imgsCounter + 1;
      return $text;
    }



    public function import($fileName)
    {
        $savedPrecision = ini_get('precision');

        $result         = array();
        $columnsWidth   = array();
        $mergedCells    = array();
        $conditionsData = array();

        $needCached = (filesize($fileName) > 200000);
        if ($needCached) {
            set_time_limit(1000);
            //PhpOffice\PhpSpreadsheet\Settings::setCache(PhpOffice\PhpSpreadsheet\Collection\CellsFactory::cache_to_sqlite3);
        }
        $startRow = 1;
        $reader   = PhpOffice\PhpSpreadsheet\IOFactory::createReader(PhpOffice\PhpSpreadsheet\IOFactory::identify($fileName));

        $chunkSize    = 500;
        $chunkFilter  = new chunkReadFilter();
        $totalRows    = 0;
        $totalColumns = 0;
        $rowNumber    = 0;
        $lastRow      = 0;
        $lastColumn   = 0;
        $sheetInfo    = $reader->listWorksheetInfo($fileName);
        $sheets       = array();
        foreach ($sheetInfo as $i => $data) {
            $sheets[$data['worksheetName']] = $data['totalRows'];
        }

				$this->settings['remove'] = ($this->settings['remove'] == 'true') ? true : false;

        $rawData     = $this->settings['raw'];
        $visibleData = $this->settings['visibleData'];
        $saved       = false;
        ini_set('precision', $savedPrecision);
        do {
            if ($startRow > 1) {
                if (sizeof($columnsWidth) == 0) {
                    $columnsWidth = $this->getColumnWidth($result[0]['cells']);
                }
                $this->settings['model']->updateTableData($this->settings['id'], array(
                    'rows' => $result
                ));
                unset($result, $workbook, $sheet, $rows);
                gc_collect_cycles();
                $result                   = array();
                $this->settings['remove'] = false;
                $saved                    = true;
            }
            $chunkFilter->setRows($startRow, $chunkSize);
            $reader->setReadFilter($chunkFilter);

            $reader->setReadDataOnly($rawData);
            if ($visibleData) {
                $reader->setReadDataOnly(false);
            }

            $workbook = $reader->load($fileName);
            $sheet    = $workbook->getActiveSheet();

            $importImage = isset($this->settings['uniq']) && !empty($this->settings['uniq']);
            if ($importImage) {
                $imgCoordinates = array();
                foreach ($sheet->getDrawingCollection() as $drawing) {
                    $string     = $drawing->getCoordinates();
                    $coordinate = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($string);
                    if ($drawing instanceof PhpOffice\PhpSpreadsheet\Worksheet\Drawing) {
                        $imgCoordinates[$string] = $this->media_sideload_image($drawing);
                    }
                }
                if (sizeof($imgCoordinates) == 0)
                    $importImage = false;
            }

            $highestRows = $sheets[$sheet->getTitle()];
            $stop        = $startRow + $chunkSize - 1;

            $rows         = $sheet->getRowIterator($startRow, $stop < $highestRows ? $stop : $highestRows);
            $mergedData   = $sheet->getMergeCells();
            $columnNumber = 0;

            if (!empty($mergedData)) { // find data for merge
                foreach ($mergedData as $md) {
                    $merge = array();
                    //$range = PhpOffice\PhpSpreadsheet\Cell\Cell::splitRange($md);
                    $range = PhpOffice\PhpSpreadsheet\Cell\Coordinate::splitRange($md);
                    $range = !empty($range[0]) ? $range[0] : $range;
                    if (!empty($range[0]) && !empty($range[1])) {
                        $startCoords   = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($range[0]); // start cell
                        $endCoords     = PhpOffice\PhpSpreadsheet\Cell\Coordinate::coordinateFromString($range[1]); // end cell
                        $startColIndex = PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($startCoords[0]);
                        $endColIndex   = PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($endCoords[0]);

                        $merge['row']     = ((int) $startCoords[1]) - 1;
                        $merge['col']     = $startColIndex - 1;
                        $merge['rowspan'] = $endCoords[1] - $startCoords[1] + 1;
                        $merge['colspan'] = $endColIndex - $startColIndex + 1;
                    }
                    array_push($mergedCells, $merge);
                }
            }
            $colVisibleArr = array();

            foreach ($rows as $i => $row) {

                $str = $i;

                $cells = $row->getCellIterator();
                $cells->setIterateOnlyExistingCells(false);
                $height             = $sheet->getRowDimension($row->getRowIndex())->getRowHeight();
                $height             = round($height * 1.333333); // pt to px
                $result[$rowNumber] = array(
                    'cells' => array(),
                    'height' => $height
                );



                $isCurrentRowVisibility = $sheet->getRowDimension($rowNumber + 1)->getVisible();
                foreach ($cells as $cell) {
                    $str = $str . ' ; ' . $cell->getValue();
                    $columnNameChars = $cell->getColumn();
                    $columnDimension = $sheet->getColumnDimension($columnNameChars);
                    $width           = $columnDimension->getWidth();
                    $width           = $width !== -1 ? round($width * 5.5) : 0;
                    $data            = $cell->getValue() === null ? '' : $cell->getValue();



                    //get values as visible in excel. setReadDataOnly must be set "bool" false
                    if ($visibleData) {
                        $data = (string) $cell->getFormattedValue();
                    }

                    $calculatedValue = '';
                    $cellStyle       = $cell->getStyle();
                    $cellFormat      = $cellStyle->getNumberFormat()->getFormatCode();
                    $formatType      = '';
                    $format          = '';
                    $reformat        = false;
                    $meta            = array();
                    $hidden          = false;


                    // Check merged cells
                    if (!empty($mergedCells)) {
                        $cellRowIndex = $cell->getRow() - 1;
                        //$cellColIndex = $cell->columnIndexFromString($cell->getColumn()) - 1;
                        $cellColIndex = PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($cell->getColumn()) - 1;

                        foreach ($mergedCells as $mc) {
                            $rowlen = $mc['row'] + $mc['rowspan'];
                            $collen = $mc['col'] + $mc['colspan'];
                            if ($cellRowIndex >= $mc['row'] && $cellRowIndex < $rowlen && $cellColIndex >= $mc['col'] && $cellColIndex < $collen) {
                                $hidden = true;
                            }
                            if ($cellColIndex >= $mc['col'] && $cellColIndex < $collen && $cellRowIndex >= $mc['row'] && $cellRowIndex < $rowlen) {
                                $hidden = true;
                            }
                        }
                    }
                    if ($importImage) {
                        $cellStrCoordinate = $cell->getCoordinate();
                        if (isset($imgCoordinates[$cellStrCoordinate])) {
                            $data = $imgCoordinates[$cellStrCoordinate];
                        }
                    }
                    if (!$visibleData) {
                        // Check format
                        if (preg_match('/%/', $cellFormat)) {
                            $formatType = 'percent';
                        } else if (preg_match('/[\$€£₴₽]+/Uu', $cellFormat)) {
                            $formatType = 'currency';
                        }
                        else if (is_numeric($cell->getValue()) && PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
                        //} else if (is_numeric($cell->getValue()) && !is_int($cell->getValue()) && $this->validateDateTime($cell->getValue())) {
                            $formatType = 'date';
                            $format     = 'YYYY-MM-DD hh:mm:ss';
                            $dataObject = PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($cell->getValue());
                            $data       = date_format($dataObject, 'Y-m-d h:m:s');
                            $reformat   = true;
                        }
                        else if (preg_match('/^[(\d{2}):]+$/', $cellFormat, $matches) && is_numeric($cell->getValue())) { // time format
                            $formatType  = 'time_duration';
                            $format      = 'HHmmss';
                            $reformat    = true;
                            $time_length = 4;
                            //fix for numbers for correct convert to time
                            if (strlen($data) < $time_length) {
                                $missing_zeros = $time_length - strlen($data);
                                for ($i = $missing_zeros; $i > 0; $i--) {
                                    $data = '0' . $data;
                                }
                            }
                        }
                    }
                    // Check formulas
                    if ($cell->isFormula()) {
                        try {
                            if (preg_match('/!/', $cell->getValue())) { // cell has link to another sheet
                                $data = $cell->getOldCalculatedValue();
                            } else {
                                $calculatedValue = $cell->getCalculatedValue();
                            }
                        }
                        catch (PhpOffice\PhpSpreadsheet\Calculation\Exception $e) {
                            $data = $cell->getOldCalculatedValue();
                        }
                        if ($data == '=') {
                            $data = $cell->getOldCalculatedValue();
                        }
                        if (strtoupper(substr($cell->getValue(), 0, 6)) == '=IMAGE') {
                            if (preg_match('/.*?(http.*?)["\']{1}/', $cell->getValue(), $match)) {
                                $imgSrc     = $match[1];
                                $formatType = 'image';
                                $width      = '';
                                $size       = getimagesize($imgSrc);
                                if ($size AND $size[0]) {
                                    $width = $size[0];
                                }
                                $data = "<img width='{$width}' src='{$imgSrc}'>";
                            }
                        }
                        //PLACE DATA HERE//
                        for ($i=0; $i < 30; $i++) {
                          $data = $this->findTagsSaveContentReplaceKey($data);
                        }
                        for ($i=0; $i < 30; $i++) {
                          $data = $this->findImgsSaveContentReplaceKey($data);
                        }
                        $data = str_ireplace(">", "@MORE@", $data);
                        $data = str_ireplace("<", "@LESS@", $data);
                        foreach ($this->replacedArr as $key => $val) {
                          $data = str_ireplace($key, $val, $data);
                        }
                        //error_log('IMPORT: '.$data);
                        //PLACE DATA HERE//
                    }

                    // Check links
                    if ($cell->hasHyperlink()) {
                        if (!$cell->isFormula()) {
                            $url = $cell->getHyperlink()->getUrl();

                            if (preg_match('/mailto:([A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}).*"(.*)"/i', $data, $match)) {
                                if (!empty($match[2])) {
                                    $data = $match[2];
                                } else if (!empty($match[1])) {
                                    $data = $match[1];
                                }
                            } else if (preg_match_all('/[\'|"](.*)[\'|"]/iU', $data, $match)) {
                                if (!empty($match[1]) && !empty($match[1][1])) {
                                    $data = $match[1][1];
                                } else {
                                    $data = $url;
                                }
                            }
                            $data = '=HYPERLINK("' . $url . '"; "' . $data . '")';
                        } else {
                            $url               = $cell->getHyperlink()->getUrl();
                            $cellFormattedData = (string) $cell->getFormattedValue();
                            $data              = '<a href="' . $url . '">' . $cellFormattedData . '</a>';
                        }
                        array_push($meta, 'unescapeHTML');
                    }

                    // Set correct empty value for cell - let it be here after check all variations to get cell data
                    if ($data) {
                        $lastColumn = max($lastColumn, $columnNumber);
                        $lastRow    = $totalRows;
                    }

                    // Cell formatting
                    $cellFill            = $cellStyle->getFill();
                    $cellFont            = $cellStyle->getFont();
                    $cellAlignment       = $cellStyle->getAlignment();
                    $horizontalAlignment = $cellAlignment->getHorizontal();
                    $verticalAlignment   = $cellAlignment->getVertical();
                    $verticalAlignment   = $verticalAlignment == 'center' ? 'middle' : $verticalAlignment;
                    $bgColor             = $this->getColor($cellFill, 'background');
                    $fontColor           = $this->getColor($cellFont);

                    if ($rowNumber == 0) {
                        $isCellVisible                   = $columnDimension->getVisible();
                        $colVisibleArr[$columnNameChars] = $isCellVisible;
                    }
                    if ($cellFill->getFillType() !== 'none') {
                        array_push($meta, 'bg-' . strtolower($bgColor));
                    }
                    if ($fontColor != '000000') {
                        array_push($meta, 'color-' . strtolower($fontColor));
                    }
                    if ($cellFont->getSize()) {
                        array_push($meta, 'fsize-' . $cellFont->getSize());
                    }
                    if ($cellFont->getName()) {
                        array_push($meta, 'ffamily-' . preg_replace('/\s+/', '_', $cellFont->getName()));
                    }
                    if ($cellFont->getBold()) {
                        array_push($meta, 'bold');
                    }
                    if ($cellFont->getItalic()) {
                        array_push($meta, 'italic');
                    }
                    if ($cellFont->getUnderline() != 'none') {
                        array_push($meta, 'underline');
                    }
                    if (!$cellAlignment->getWrapText()) {
                        array_push($meta, 'ww-h');
                    }
                    if ($horizontalAlignment !== 'general') {
                        array_push($meta, 'ht' . ucfirst($horizontalAlignment));
                    }
                    if (!$isCurrentRowVisibility || (isset($colVisibleArr[$columnNameChars]) && !$colVisibleArr[$columnNameChars])) {
                        array_push($meta, 'invisibleCell');
                    }
                    array_push($meta, 'ht' . ucfirst($verticalAlignment));

                    // check dropdown list
                    $source         = array();
                    $dataValidation = $cell->getDataValidation();

                    if ($dataValidation->getShowDropDown() && $dataValidation->getType() == 'list') {
                        array_push($meta, 'selectable');

                        $list = $dataValidation->getFormula1();
                        if (strpos($list, '$') === false) {
                            $source = explode(',', str_replace('"', '', $list));
                        } else {
                            $isSheet   = strpos($list, '!');
                            $listCells = $isSheet ? substr($list, $isSheet + 1) : $list;

                            try {
                                $sourceData = $sheet->rangeToArray(str_replace('$', '', $listCells), '', false, false);
                                foreach ($sourceData as $srcRow) {
                                    foreach ($srcRow as $srcCell) {
                                        if ($srcCell !== '') {
                                            array_push($source, $srcCell);
                                        }
                                    }
                                }
                            }
                            catch (PhpOffice\PhpSpreadsheet\Exception $e) {
                                array_push($source, 'error');
                            }
                        }
                    }

                    // Set formatted value in end to be sure that other cell values was already set
                    $formattedValue = !empty($calculatedValue) ? $calculatedValue : $data;
                    $formattedValue = strip_tags($formattedValue);

                    //error_log($str);

                    // Set all cell values anyway to prevent errors
                    array_push($result[$rowNumber]['cells'], array(
                        'data' => stripslashes($data),
                        'meta' => $meta,
                        'hidden' => $hidden,
                        'width' => strval(round($width)),
                        'calculatedValue' => $calculatedValue,
                        'formattedValue' => $formattedValue,
                        'formatType' => $formatType,
                        'format' => $format,
                        'reformat' => $reformat,
                        'source' => $source
                    ));
                    $columnNumber++;
                    //END OF THE CELL LOOP//
                    $this->replacedArr = array();
                    $this->imgsCounter = 0;
                    $this->tagsCounter = 0;
                    //END OF THE CELL LOOP//
                }
                $rowNumber++;
                $totalRows++;
                $totalColumns = max($totalColumns, $columnNumber);
                $columnNumber = 0;
            }

            // Check conditional formatting
            if (!empty($workbook->_supCondStyles)) {
                ksort($workbook->_supCondStyles);
                foreach ($workbook->_supCondStyles as $p => $condition) {
                    $condStyle = $condition['styles']->getStyle();
                    $condFill  = $condStyle->getFill();
                    $condFont  = $condStyle->getFont();
                    $condConditions = array_map(function($n)
                    {
                        preg_match('/^"(.*)"$/', $n, $matches);
                        return !empty($matches) ? $matches[1] : $n;
                    }, $condition['styles']->getConditions());
                    array_filter($condConditions);
                    $conditionsData[$p] = array(
                        'range' => $condition['range'],
                        'condition' => array(
                            'conditionType' => $condition['styles']->getConditionType(),
                            'operatorType' => $condition['styles']->getOperatorType(),
                            'text' => $condition['styles']->getText(),
                            'condition' => $condConditions,
                            'style' => array(
                                'bold' => $condFont->getBold() ? 'on' : '',
                                'italic' => $condFont->getItalic() ? 'on' : '',
                                'underline' => $condFont->getUnderline() ? 'on' : '',
                                'bg_color' => $this->getColor($condFill, 'condition_background'),
                                'color' => $this->getColor($condFont)
                            )
                        )
                    );
                }
            }

            $startRow += $chunkSize;
        } while ($startRow <= $highestRows);

        $reader      = null;
        $chunkFilter = null;
        unset($reader, $chunkFilter);
        gc_collect_cycles();

        $lastRow++; // +1 to include the last row
        $lastColumn++; // +1 to include the last column

        //Clear result from fully empty rows (if not saved)
        if (!$saved) {
            array_splice($result, $lastRow);
            foreach ($result as &$row) {
                array_splice($row['cells'], $lastColumn);
            }
        }
        if (sizeof($columnsWidth) == 0) {
            $columnsWidth = $this->getColumnWidth($result[0]['cells']);
        }

        if ($needCached) {
            global $wpdb;
            $wpdb->db_connect();
        }

        return array(
            'rows' => $result,
            'meta' => array(
                'mergedCells' => $mergedCells,
                'columnsWidth' => $columnsWidth
            ),
            'conditions' => $conditionsData,
            'remove' => $this->settings['remove'],
            'lastColumn' => ($saved && $totalColumns > $lastColumn ? $lastColumn : -1),
            'removeRows' => ($saved ? $totalRows - $lastRow : 0)
        );
    }

    public function media_sideload_image($drawing)
    {
        $title       = $this->settings['uniq'];
        $description = strtolower(substr(trim($drawing->getDescription()), 0, 20));
        if (empty($description))
            return '';
        $description = preg_replace('/[^a-z0-9-]/', '-', $description);
        $description = preg_replace('/-+/', "-", $description);
        $title .= '-' . $description;
        if (!function_exists('post_exists')) {
            require_once(ABSPATH . 'wp-admin/includes/post.php');
        }
        $id = post_exists($title);

        if (!$id) {
            try {
                if (!function_exists('media_handle_sideload')) {
                    require_once(ABSPATH . 'wp-admin/includes/media.php');
                    require_once(ABSPATH . 'wp-admin/includes/file.php');
                    require_once(ABSPATH . 'wp-admin/includes/image.php');
                }

                $imgFile       = $drawing->getPath();
                $extension     = $drawing->getExtension();
                $temp          = sys_get_temp_dir() . '/sss.' . $extension;
                $zipReader     = fopen($drawing->getPath(), 'r');
                $imageContents = '';
                while (!feof($zipReader)) {
                    $imageContents .= fread($zipReader, 1024);
                }
                fclose($zipReader);
                file_put_contents($temp, $imageContents);
                $id = media_handle_sideload(array(
                    'name' => $title . '.' . $extension,
                    'tmp_name' => $temp
                ), 0);
                if (is_wp_error($id)) {
                    @unlink($temp);
                }
            }
            catch (Exception $e) {
                $id = 0;
            }
        }
        if ($id) {
            $w   = $drawing->getWidth();
            $h   = $drawing->getHeight();
            /*if(empty($w)) $w = 0;
            if(empty($h)) $h = 0;
            return wp_get_attachment_image($id, ($w || $h ? array($w, $h) : 'full'));*/
            $img = wp_get_attachment_image_src($id, 'full');
            return '<img src="' . (is_array($img) ? $img[0] : '') . '"' . (empty($w) ? '' : ' width="' . $w . '"') . (empty($h) ? '' : ' height="' . $h . '"') . ' style="' . (empty($w) ? '' : 'width:' . $w . 'px;') . (empty($h) ? '' : 'height:' . $h . 'px') . '">';
        }

        return '';
    }

    protected function getColumnWidth($cells)
    {
        $width = array();
        foreach ($cells as $cell) {
            array_push($width, $cell['width']);
        }
        return $width;
    }

    protected function getColor($colorObj, $type = '')
    {
        switch ($type) {
            case 'background':
                $colorData = $colorObj->getStartColor();
                break;
            case 'condition_background':
                $colorData = $colorObj->getEndColor();
                break;
            default:
                $colorData = $colorObj->getColor();
                break;
        }
        $color = $colorData->getRGB();
        $color = strlen($color) == 4 ? $colorData->getARGB() : $color;
        $color = strlen($color) == 8 ? substr($color, 2) : $color;

        return $color;
    }

    /**
     * Returns column index for the cell.
     * @param string $columnName Column name (A, B, C, etc)
     * @return int Column index
     */
    protected function getColumnIndex($columnName)
    {
        return array_search(strtoupper($columnName), range('A', 'Z'), false);
    }

    /**
     * Returns row index of the cell.
     * @param string|int $rowName Row name
     * @return int Row index
     */
    protected function getRowIndex($rowName)
    {
        return (int) $rowName - 1;
    }

    /**
     * @param $settings
     */
    public function setSettings($settings)
    {
        foreach ($settings as $key => $value) {
            $this->settings[$key] = $value;
        }
    }
}

class chunkReadFilter implements PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
    private $_startRow = 0;
    private $_endRow = 0;

    public function setRows($startRow, $chunkSize)
    {
        $this->_startRow = $startRow;
        $this->_endRow   = $startRow + $chunkSize;
    }

    public function readCell($column, $row, $worksheetName = '')
    {
        if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
            return true;
        }
        return false;
    }
}
