<?php


class SupsysticTablesPro_Exporter_Type_Xls implements SupsysticTablesPro_Exporter_Interface
{

    /**
     * Exports specified data.
     * @param array $data
     * @return mixed
     */
    public function export(array $data)
    {
        $letters = range('A', 'Z');
		$prefix = array_merge(array(''), $letters);
        $countIndex = 0;
        $letterIndex = 0;
        $excel = new PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $excel->getActiveSheet();

		$meta = $data['table']->meta;
		if(!empty($meta['columnsWidth'])) {
			foreach($meta['columnsWidth'] as $i => $w) {
				$colWidth = round($w / 5.5);	// px to pt
				if($colWidth > 0) {
					$sheet->getColumnDimensionByColumn($i)->setWidth($colWidth);
				}
			}
		}
		$mergedCells = !empty($meta['mergedCells']) ? $meta['mergedCells'] : array();
		$values_only = $data['values_only'] ? true : false;
		$rowsCount = count($data['table']->rows);
		$invisibleRowArr = array();
		$uploadDir = wp_upload_dir();
		$imgUrl = $uploadDir['baseurl'];
		$imgDir = $uploadDir['basedir'];
		$tempFiles = array();

        foreach ($data['table']->rows as $rowIndex => $row) {
			if(!empty($row['height'])) {
				$rowHeight = round($row['height'] / 1.333333);	// px to pt
				if($rowHeight > 0) {
					$sheet->getRowDimension($rowIndex + 1)->setRowHeight($rowHeight);
				}
			}

			$realCellsInRowCount = 0;
			$invisibleCellsInRowCount = 0;
			foreach ($row['cells'] as $colIndex => $cell) {
				if($values_only && isset($cell['meta']) && !empty($cell['meta']) && in_array('hiddenCell', $cell['meta'])) continue;
				$cellChar = $prefix[$countIndex] . $letters[$letterIndex];
				$cellIndex = $prefix[$countIndex] . $letters[$letterIndex] . ($rowIndex + 1);
				$cellValue = $cell['data'];
				$is_link = '';
				$is_img = false;
				$is_remote_img = false;

                if (strpos($cellValue, '%3C') !== false) {
                    $cellValue = urldecode($cellValue);
                }

                if (strpos($cellValue, '&#') !== false) {
                    $cellValue = html_entity_decode($cellValue);
                }

                $cellValue = preg_replace('/<a href="#" class="delete-upload-file" title="(.*?)">(.*?)<\/a>/', '', $cellValue);

				if(preg_match('/^<a.*href="([^"]*)".*>(.*)<\/a>$/i', $cellValue, $matches)) {
					$is_link = $matches[1];
					$cellValue = $matches[2];
				}
				if(preg_match('/^<img.*src="([^"]*)".*>$/i', $cellValue, $matches)) {
					$imgPath = $matches[1];
					if(strpos($imgPath, $imgUrl) === 0) {
						$is_img = true;
						$imgPath = str_replace($imgUrl, $imgDir, $imgPath);
					} else {
						if(isset($tempFiles[$imgPath])) {
							$imgPath = $tempFiles[$imgPath];
							$is_remote_img = true;
						} else {
							try {
								$response = wp_remote_get($imgPath);
								if(!is_wp_error($response) && strpos(wp_remote_retrieve_header($response, 'content-type'), 'image') !==  false) {
									$file = wp_remote_retrieve_body($response);
									if(!empty($file)) {
										$temp = tempnam(sys_get_temp_dir(), 'sstimage');
										$tempFiles[$imgPath] = $temp;
										$handler = fopen($temp, 'w+');
										fwrite($handler, $file);
										fclose($handler);
										$imgPath = $temp;
										$is_remote_img = true;
									}
								}
							} catch (Exception $e) {}
						}
					}
					if($is_img || $is_remote_img) {
						if(preg_match('/^<img.*width="([^"]*)".*>$/i', $cellValue, $matches)) {
							$imgWidth = $matches[1];
						} else {
							$imgWidth = 0;
						}
						if(preg_match('/^<img.*height="([^"]*)".*>$/i', $cellValue, $matches)) {
							$imgHeight = $matches[1];
						} else {
							$imgHeight = 0;
						}
					}
				}

				if($values_only) {
					if(!empty($cell['formattedValue'])) {
						$cellValue = $cell['formattedValue'];
					} else if(isset($cell['calculatedValue'])) {
						$cellValue = $cell['calculatedValue'];
					}
				}
				if($mergedCells) {	// find data for merge
					if (is_array($mergedCells)) {
						foreach($mergedCells as $mc) {
							if($mc['row'] === $rowIndex && $mc['col'] === $colIndex) {
								$mRow = $mc['rowspan'];
								$mCol = $mc['colspan'] - 1;
								$mergeCountIndex = $countIndex;
								$mergeLetterIndex = $letterIndex + $mCol;
								if($mergeLetterIndex >= count($letters)) {
									$mergeCountIndex++;
									$mergeLetterIndex = $mergeLetterIndex - count($letters);
								}
								$toCol = $prefix[$mergeCountIndex] . $letters[$mergeLetterIndex];
								$toRow = $rowIndex + $mRow;
								$sheet->mergeCells($cellIndex.':'.$toCol.$toRow);		// coordinates for merge
							}
						}
					}
				}
				if($is_link) {
					$sheet->setHyperlink($cellIndex, new PhpOffice\PhpSpreadsheet\Cell\Hyperlink($is_link, $cellValue));
				}
				if($is_img || $is_remote_img) {
					$objDrawing = new PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
					$objDrawing->setPath($imgPath);
					$objDrawing->setResizeProportional(true);
					if($imgWidth > 0) $objDrawing->setWidth($imgWidth);
					if($imgHeight > 0) $objDrawing->setHeight($imgHeight);
					$objDrawing->setOffsetX(5);
					$objDrawing->setOffsetY(5);
					$objDrawing->setCoordinates($cellIndex);
					$h = $objDrawing->getHeight() - ($objDrawing->getHeight() * .25) + 10;
					$w = round($objDrawing->getWidth() / 5.5);
					if($h > $sheet->getRowDimension($rowIndex + 1)->getRowHeight()) {
						$sheet->getRowDimension($rowIndex + 1)->setRowHeight($h);
					}
					if($w > $sheet->getColumnDimensionByColumn($colIndex)->getWidth()) {
						$sheet->getColumnDimensionByColumn($colIndex)->setWidth($w);
					}
					$objDrawing->setWorksheet($sheet);
				} else {
					$sheet->setCellValue($cellIndex, $cellValue);
				}

				if ($sheet->getCell($cellIndex)->isFormula()) {
					$cellValue = str_replace(';', ',', $cellValue);
					$sheet->setCellValue($cellIndex, $cellValue);
				}

				$cellStyle = $sheet->getStyle($cellIndex);
				$cellAlignment = $cellStyle->getAlignment();
				$cellAlignment->setWrapText(true);
				if(isset($cell['meta']) && !empty($cell['meta'])) {
					$cellFill = $cellStyle->getFill();
					$cellFont = $cellStyle->getFont();

					foreach($cell['meta'] as $meta) {
						if(preg_match('/bg\-([0-9a-f]{6})/', $meta, $matches)) {
							$color = $this->getColor($matches[1]);
							$cellFill->setFillType(PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
							$cellFill->setStartColor($color);
						}
						if(preg_match('/color\-([0-9a-f]{6})/', $meta, $matches)) {
							$color = $this->getColor($matches[1]);
							$cellFont->setColor($color);
						}
						if(preg_match('/fsize\-([0-9]+)/', $meta, $matches)) {
							$cellFont->setSize($matches[1]);
						}
						if(preg_match('/ffamily\-([a-z_]+)/i', $meta, $matches)) {
							$cellFont->setName(str_replace('_', ' ', $matches[1]));
						}
						$cellAlignment->setWrapText(true);
						switch($meta) {
							case 'bold':
								$cellFont->setBold(true);
								break;
							case 'italic':
								$cellFont->setItalic(true);
								break;
							case 'underline':
								$cellFont->setUnderline(true);
								break;
							case 'ww-v':
								$cellAlignment->setWrapText(false);
								break;
							case 'ww-h':
								$cellAlignment->setWrapText(false);
								break;
							case 'htCenter':
								$cellAlignment->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
								break;
							case 'htLeft':
								$cellAlignment->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
								break;
							case 'htRight':
								$cellAlignment->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
								break;
							case 'htTop':
								$cellAlignment->setVertical(PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
								break;
							case 'htMiddle':
								$cellAlignment->setVertical(PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
								break;
							case 'htBottom':
								$cellAlignment->setVertical(PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_BOTTOM);
								break;
						}
					}
					// export seletable list
					if(!$values_only && ($cellIndex != 'A1' || $data['type'] != 'xls')
						 && in_array('selectable', $cell['meta']) && isset($cell['source']) && sizeof($cell['source']) > 0) {
						$dataValidation = $sheet->getCell($cellIndex)->getDataValidation();
						$dataValidation->setType( PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
						$dataValidation->setErrorStyle( PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
						$dataValidation->setAllowBlank(false);
						$dataValidation->setShowDropDown(true);
						$dataValidation->setFormula1('"'.str_replace('"', '', implode(',', $cell['source'])).'"');
					}
				}

				

				// check row to disable visibility
				if(isset($cell['invisibleCell']) && $cell['invisibleCell']) {
					$invisibleCellsInRowCount++;
					if(!isset($invisibleRowArr[$cellChar])) {
						$invisibleRowArr[$cellChar] = 1;
					} else {
						$invisibleRowArr[$cellChar]++;
					}
				}
				$realCellsInRowCount++;

				if($letters[$letterIndex] == 'Z') {
					$letterIndex = 0;
					$countIndex++;
				} else {
					$letterIndex++;
				}
			}

			// set invisibility row
			if($invisibleCellsInRowCount && $invisibleCellsInRowCount == $realCellsInRowCount) {
				$sheet->getRowDimension($rowIndex + 1)->setVisible(false);
			}
			$letterIndex = 0;
			$countIndex = 0;
		}
		// set invisibility column
		if(count($invisibleRowArr)) {
			foreach($invisibleRowArr as $colName => $iraCount) {
				if($iraCount == $rowsCount) {
					$sheet->getColumnDimension($colName)->setVisible(false);
				}
			}
		}
		// set conditional formatting (work only for xlsx - need fix)
		if($this->getWriterType() == 'Excel2007' && !empty($data['table']->conditionsData) && !empty($data['table']->conditionsData['conditions'])) {
			foreach ($data['table']->conditionsData['conditions'] as $cond) {
				$conditionalStyles = array();
				$objConditional = new PhpOffice\PhpSpreadsheet\Style\Conditional();
				$objConditional->setConditionType((string)$cond['condition']['conditionType']);
				$objConditional->setOperatorType((string)$cond['condition']['operatorType']);
				$objConditional->setConditions(array_map(function($n) {
					return is_numeric($n) ? $n : '"' . $n . '"';
				}, $cond['condition']['condition']));
				if((string)$cond['condition']['text'] != '') {
					$objConditional->setText((string)$cond['condition']['text']);
				}
				$condStyle = $objConditional->getStyle();
				$condFill = $condStyle->getFill();
				$condFont = $condStyle->getFont();

				foreach($cond['condition']['style'] as $n => $v) {
					switch ($n) {
						case 'bold':
							$condFont->setBold(true);
							break;
						case 'italic':
							$condFont->setItalic(true);
							break;
						case 'underline':
							$condFont->setUnderline(true);
							break;
						case 'bg_color':
							$condFill->setFillType(PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);
							$condFill->setEndColor($this->getColor($v));
							break;
						case 'color':
							$condFont->setColor($this->getColor($v));
							break;
					}
				}
				$objConditional->setStyle($condStyle);
				array_push($conditionalStyles, $objConditional);
				$sheet->getStyle($cond['range'])->setConditionalStyles($conditionalStyles);
			}
		}

		$writer = $this->getWriter($excel);

        try {
            $writer->save('php://output');
            $this->removeTempFiles($tempFiles);
        } catch (Exception $e) {
            $upload_dir = wp_upload_dir();
            if (!$upload_dir['error']) {
                $filename = trailingslashit($upload_dir['basedir']) . 'temp.xls';
                $writer->save($filename);
				readfile($filename);
				unlink($filename);
            }
            $this->removeTempFiles($tempFiles);
        }

    }
    public function removeTempFiles($tempFiles)
    {
    	foreach($tempFiles as $temp) {
			@unlink($temp);
		}
	}

	public function getWriter($excel)
	{
		return PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel, $this->getWriterType());
	}

	public function getWriterType()
	{
		return 'Xls';
	}

	public function getColor($color)
	{
		return new PhpOffice\PhpSpreadsheet\Style\Color('00' . $color);
	}
}
