<?php

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

	public function import($fileName)
	{
		// PHPExcel is modifying the php.ini precision setting in its calculation engine. We need to override this.
		// http://stackoverflow.com/questions/30107011/phpexcel-unexpected-float-behavior
		$savedPrecision = ini_get('precision');

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

		$needCached = (filesize($fileName) > 200000);
		if ($needCached) {
			set_time_limit(1000);
			PHPExcel_Settings::setCacheStorageMethod(PHPExcel_CachedObjectStorageFactory::cache_to_sqlite3);
		}
		$startRow = 1;
		$reader = PHPExcel_IOFactory::createReader(PHPExcel_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'];
		}

		$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), $this->settings['remove']);
    			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();

			$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 = PHPExcel_Cell::splitRange($md);
					$range = !empty($range[0]) ? $range[0] : $range;
					if (!empty($range[0]) && !empty($range[1])) {
						$startCoords = PHPExcel_Cell::coordinateFromString($range[0]);	// start cell
						$endCoords = PHPExcel_Cell::coordinateFromString($range[1]);	// end cell
						$startColIndex = PHPExcel_Cell::columnIndexFromString($startCoords[0]);
						$endColIndex = PHPExcel_Cell::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 $row) {

				$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) {
					$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;
						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(!$visibleData){
						// Check format
						if (preg_match('/%/', $cellFormat)) {
							$formatType = 'percent';
						} else if (preg_match('/[\$€£₴₽]+/Uu', $cellFormat)) {
							$formatType = 'currency';
						} else if (PHPExcel_Shared_Date::isDateTime($cell) && is_numeric($cell->getValue())) {
							$formatType = 'date';
							$format = 'YYYY-MM-DD hh:mm:ss';
							$dataObject = PHPExcel_Shared_Date::ExcelToPHPObject($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 {
							PHPExcel_Calculation::getInstance($workbook)->calculateFormula($cell->getValue(), null, $cell);
							if (preg_match('/!/', $cell->getValue())) { // cell has link to another sheet
								$data = $cell->getOldCalculatedValue();
							} else {
								$calculatedValue = $cell->getCalculatedValue();
							}
						} catch (PHPExcel_Calculation_Exception $e) {
							$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}'>";
							}
						}
					}

					// Check links
					if ($cell->hasHyperlink()) {
						array_push($meta, 'unescapeHTML');
						/*$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 = '<a href="' . $url . '">' . $data . '</a>';*/
					}

					// 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  (PHPExcel_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);

					// 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++;
				}
				$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)
		);
	}

	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 PHPExcel_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;
	}
}
