<?php

namespace App\Utils\OOXML;

/**
 * @see https://github.com/box/spout as an alternative
 *
 *
 * @see https://github.com/elidickinson/php-export-data/blob/master/php-export-data.class.php
 * @see http://officeopenxml.com/anatomyofOOXML-xlsx.php
 *
 * @see https://docs.microsoft.com/en-us/office/open-xml/structure-of-a-spreadsheetml-document
 * @see https://books.google.ro/books?id=J8lGvNBO_5MC&pg=PA178&lpg=PA178&dq=office+%22Workbook%22+%22DocumentProperties%22+%22ExcelWorkbook%22&source=bl&ots=ViKoiIFM59&sig=ACfU3U3UHEVTKn4QE9ostZDjhl4GyAQuhg&hl=en&sa=X&ved=2ahUKEwjrm9P_ucLoAhXp0aYKHfIlDFwQ6AEwAXoECAwQAQ#v=onepage&q&f=false
 * @see https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats
 *
 * @author Lucian Sirbu
 *
 */
class Writer
{
	protected $tempFolder = null;
	protected $rows = [];
	protected $properties = [];

	public function __construct() {
		global $CFG;

		$this->tempFolder = $CFG['paths']['temporary'];

        $this->properties['sheetTitle'] = "Sheet1";
	}

	/**
	 * Set a document property
	 */
	public function setProperty($property, $propValue)
	{
		$this->properties[$property] = $propValue;
	}

	/**
	 * Load all document data from a 2-dimensional array
	 * @param array $rows
	 */
	public function fromArray($rows)
	{
		$this->rows = $rows;
		return $this;
	}


	/**
	 * Convert a number to an excel-like column
	 * Converts '0' to 'A', '1' to 'B' etc
	 * @see http://stackoverflow.com/a/3302991/11301
	 */
	public function getCoordNameFromNumber($num) {
		$numeric = $num % 26;
		$letter = chr(65 + $numeric);
		$num2 = intval($num / 26);
		if ($num2 > 0) {
			return $this->getCoordNameFromNumber($num2 - 1) . $letter;
	    }
		return $letter;
	}

	/**
	 * Escape XML specific entities
	 * @param string $string
	 */
	function xml_entities($string) {
	    return strtr(
	        (string)$string,
	        [
	            "<" => "&lt;",
	            ">" => "&gt;",
	            '"' => "&quot;",
	            "'" => "&apos;",
	            "&" => "&amp;",
	        ]
	    );
	}


	/**
	 * Internal use, creates xl/worksheets/sheet1.xml. This is the file containing the actual raw data
	 * @param string $tempFolder
	 * @param string $sheet
	 * @param array $rows
	 */
	protected function create_worksheets_sheet($tempFolder, $sheet, $rows)
	{
		$file = sprintf("%s/xl/worksheets/{$sheet}.xml", $tempFolder);

		file_put_contents($file, <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
XML
);

		// TODO
		file_put_contents($file, sprintf('<dimension ref="A1:CV4"/>'), FILE_APPEND);

		file_put_contents($file, <<<XML
    <sheetViews>
        <sheetView tabSelected="1" topLeftCell="A1" workbookViewId="0">
            <selection activeCell="A1" sqref="A1"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15"/>
XML
, FILE_APPEND);

		if ($rows) {
			$columns = array_values($rows[0]);

			// write column data (widths)
			$out = "<cols>";
			$colIdx = 1;
			foreach($columns as $column) {
				$out.= sprintf('<col min="%s" max="%s" width="15.00" bestFit="1" customWidth="0"/>', $colIdx, $colIdx+1);
				$colIdx++;
			}
			$out.= "</cols>";
			file_put_contents($file, $out, FILE_APPEND);



			// write row data (values)
	 		$out = "<sheetData>";
	 		$rIdx = 0;
			foreach($rows as $row) {
				$rIdx++;
				$out.= sprintf('<row r="%s" spans="1:%s">', $rIdx, count($row));

				$cIdx = -1;
				foreach($row as $value) {
					$cIdx++;
					/*
					 * s is the style index??? wtf? styles are indexed here????
					 * 	$out.= sprintf('<c r="%s%s" s="100" t="inlineStr"><is><t>%s</t></is></c>',
					 *
					 * */
					$out.= sprintf('<c r="%s%s" t="inlineStr"><is><t>%s</t></is></c>',
							$this->getCoordNameFromNumber($cIdx),
							$rIdx,
							$this->xml_entities($value));
				}
				$out.= "</row>";


				// optimization: flush internal data to disk
				if(mt_rand(0, 100)<25) {
					file_put_contents($file, $out, FILE_APPEND);
					$out = "";
				}
			}

			$out.= "</sheetData>";
			file_put_contents($file, $out, FILE_APPEND);
			$out = "";
		}

		file_put_contents($file, <<<XML
    <sheetProtection formatCells="0" formatColumns="0" formatRows="0" insertColumns="0" insertRows="0" insertHyperlinks="0" deleteColumns="0" deleteRows="0" sort="0" autoFilter="0" pivotTables="0"/>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>
XML
, FILE_APPEND);
	}

	protected function create_docprops_core($tempFolder, $file)
	{
		$this->properties['createdAt'] = 	date("c");
		$this->properties['modifiedAt'] = 	date("c");

		file_put_contents(sprintf("%s/docProps/{$file}.xml", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <dc:creator>{$this->properties['creator']}</dc:creator>
    <cp:lastModifiedBy>{$this->properties['lastModifiedBy']}</cp:lastModifiedBy>
    <dcterms:created xsi:type="dcterms:W3CDTF">{$this->properties['createdAt']}</dcterms:created>
    <dcterms:modified xsi:type="dcterms:W3CDTF">{$this->properties['modifiedAt']}</dcterms:modified>
    <dc:title>{$this->properties['title']}</dc:title>
    <dc:description>{$this->properties['description']}</dc:description>
    <dc:subject>{$this->properties['subject']}</dc:subject>
    <cp:keywords></cp:keywords>
    <cp:category></cp:category>
</cp:coreProperties>
XML
);

	}


	/**
	 * Save the xlsx file to disk
	 * @param string $file
	 */
	public function save($file)
	{
		//

		$tempFolder = realpath($this->tempFolder)."/".uniqid()."/";

		mkdir($tempFolder);

		mkdir(sprintf("%s/_rels", $tempFolder));
		mkdir(sprintf("%s/docProps", $tempFolder));
		mkdir(sprintf("%s/xl", $tempFolder));
		mkdir(sprintf("%s/xl/_rels", $tempFolder));
		mkdir(sprintf("%s/xl/theme", $tempFolder));
		mkdir(sprintf("%s/xl/worksheets", $tempFolder));
		mkdir(sprintf("%s/xl/worksheets/_rels", $tempFolder));

		file_put_contents(sprintf("%s/_rels/.rels", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>
XML
);

		file_put_contents(sprintf("%s/docProps/app.xml", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Application>Microsoft Excel</Application><DocSecurity>0</DocSecurity><ScaleCrop>false</ScaleCrop><HeadingPairs><vt:vector size="2" baseType="variant"><vt:variant><vt:lpstr>Worksheets</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant></vt:vector></HeadingPairs><TitlesOfParts><vt:vector size="1" baseType="lpstr"><vt:lpstr>Sheet1</vt:lpstr></vt:vector></TitlesOfParts><Company>Microsoft Corporation</Company><Manager></Manager><LinksUpToDate>false</LinksUpToDate><SharedDoc>false</SharedDoc><HyperlinksChanged>false</HyperlinksChanged><AppVersion>12.0000</AppVersion></Properties>
XML
);

		$this->create_docprops_core($tempFolder, "core");

		file_put_contents(sprintf("%s/xl/_rels/workbook.xml.rels", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/><Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/><Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/></Relationships>
XML
);

		file_put_contents(sprintf("%s/xl/theme/theme1.xml", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office Theme">
    <a:themeElements>
        <a:clrScheme name="Office">
            <a:dk1>
                <a:sysClr val="windowText" lastClr="000000"/>
            </a:dk1>
            <a:lt1>
                <a:sysClr val="window" lastClr="FFFFFF"/>
            </a:lt1>
            <a:dk2>
                <a:srgbClr val="1F497D"/>
            </a:dk2>
            <a:lt2>
                <a:srgbClr val="EEECE1"/>
            </a:lt2>
            <a:accent1>
                <a:srgbClr val="4F81BD"/>
            </a:accent1>
            <a:accent2>
                <a:srgbClr val="C0504D"/>
            </a:accent2>
            <a:accent3>
                <a:srgbClr val="9BBB59"/>
            </a:accent3>
            <a:accent4>
                <a:srgbClr val="8064A2"/>
            </a:accent4>
            <a:accent5>
                <a:srgbClr val="4BACC6"/>
            </a:accent5>
            <a:accent6>
                <a:srgbClr val="F79646"/>
            </a:accent6>
            <a:hlink>
                <a:srgbClr val="0000FF"/>
            </a:hlink>
            <a:folHlink>
                <a:srgbClr val="800080"/>
            </a:folHlink>
        </a:clrScheme>
        <a:fontScheme name="Office">
            <a:majorFont>
                <a:latin typeface="Calibri"/>
                <a:ea typeface=""/>
                <a:cs typeface=""/>
            </a:majorFont>
            <a:minorFont>
                <a:latin typeface="Calibri"/>
                <a:ea typeface=""/>
                <a:cs typeface=""/>
            </a:minorFont>
        </a:fontScheme>
        <a:fmtScheme name="Office">
            <a:fillStyleLst>
                <a:solidFill>
                    <a:schemeClr val="phClr"/>
                </a:solidFill>
                <a:gradFill rotWithShape="1">
                    <a:gsLst>
                        <a:gs pos="0">
                            <a:schemeClr val="phClr">
                                <a:tint val="50000"/>
                                <a:satMod val="300000"/>
                            </a:schemeClr>
                        </a:gs>
                        <a:gs pos="35000">
                            <a:schemeClr val="phClr">
                                <a:tint val="37000"/>
                                <a:satMod val="300000"/>
                            </a:schemeClr>
                        </a:gs>
                        <a:gs pos="100000">
                            <a:schemeClr val="phClr">
                                <a:tint val="15000"/>
                                <a:satMod val="350000"/>
                            </a:schemeClr>
                        </a:gs>
                    </a:gsLst>
                    <a:lin ang="16200000" scaled="1"/>
                </a:gradFill>
                <a:gradFill rotWithShape="1">
                    <a:gsLst>
                        <a:gs pos="0">
                            <a:schemeClr val="phClr">
                                <a:shade val="51000"/>
                                <a:satMod val="130000"/>
                            </a:schemeClr>
                        </a:gs>
                        <a:gs pos="80000">
                            <a:schemeClr val="phClr">
                                <a:shade val="93000"/>
                                <a:satMod val="130000"/>
                            </a:schemeClr>
                        </a:gs>
                        <a:gs pos="100000">
                            <a:schemeClr val="phClr">
                                <a:shade val="94000"/>
                                <a:satMod val="135000"/>
                            </a:schemeClr>
                        </a:gs>
                    </a:gsLst>
                    <a:lin ang="16200000" scaled="0"/>
                </a:gradFill>
            </a:fillStyleLst>
            <a:lnStyleLst>
                <a:ln w="9525" cap="flat" cmpd="sng" algn="ctr">
                    <a:solidFill>
                        <a:schemeClr val="phClr">
                            <a:shade val="95000"/>
                            <a:satMod val="105000"/>
                        </a:schemeClr>
                    </a:solidFill>
                    <a:prstDash val="solid"/>
                </a:ln>
                <a:ln w="25400" cap="flat" cmpd="sng" algn="ctr">
                    <a:solidFill>
                        <a:schemeClr val="phClr"/>
                    </a:solidFill>
                    <a:prstDash val="solid"/>
                </a:ln>
                <a:ln w="38100" cap="flat" cmpd="sng" algn="ctr">
                    <a:solidFill>
                        <a:schemeClr val="phClr"/>
                    </a:solidFill>
                    <a:prstDash val="solid"/>
                </a:ln>
            </a:lnStyleLst>
            <a:effectStyleLst>
                <a:effectStyle>
                    <a:effectLst>
                        <a:outerShdw blurRad="40000" dist="20000" dir="5400000" rotWithShape="0">
                            <a:srgbClr val="000000">
                                <a:alpha val="38000"/>
                            </a:srgbClr>
                        </a:outerShdw>
                    </a:effectLst>
                </a:effectStyle>
                <a:effectStyle>
                    <a:effectLst>
                        <a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0">
                            <a:srgbClr val="000000">
                                <a:alpha val="35000"/>
                            </a:srgbClr>
                        </a:outerShdw>
                    </a:effectLst>
                </a:effectStyle>
                <a:effectStyle>
                    <a:effectLst>
                        <a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0">
                            <a:srgbClr val="000000">
                                <a:alpha val="35000"/>
                            </a:srgbClr>
                        </a:outerShdw>
                    </a:effectLst>
                    <a:scene3d>
                        <a:camera prst="orthographicFront">
                            <a:rot lat="0" lon="0" rev="0"/>
                        </a:camera>
                        <a:lightRig rig="threePt" dir="t">
                            <a:rot lat="0" lon="0" rev="1200000"/>
                        </a:lightRig>
                    </a:scene3d>
                    <a:sp3d>
                        <a:bevelT w="63500" h="25400"/>
                    </a:sp3d>
                </a:effectStyle>
            </a:effectStyleLst>
            <a:bgFillStyleLst>
                <a:solidFill>
                    <a:schemeClr val="phClr"/>
                </a:solidFill>
                <a:gradFill rotWithShape="1">
                    <a:gsLst>
                        <a:gs pos="0">
                            <a:schemeClr val="phClr">
                                <a:tint val="40000"/>
                                <a:satMod val="350000"/>
                            </a:schemeClr>
                        </a:gs>
                        <a:gs pos="40000">
                            <a:schemeClr val="phClr">
                                <a:tint val="45000"/>
                                <a:shade val="99000"/>
                                <a:satMod val="350000"/>
                            </a:schemeClr>
                        </a:gs>
                        <a:gs pos="100000">
                            <a:schemeClr val="phClr">
                                <a:shade val="20000"/>
                                <a:satMod val="255000"/>
                            </a:schemeClr>
                        </a:gs>
                    </a:gsLst>
                    <a:path path="circle">
                        <a:fillToRect l="50000" t="-80000" r="50000" b="180000"/>
                    </a:path>
                </a:gradFill>
                <a:gradFill rotWithShape="1">
                    <a:gsLst>
                        <a:gs pos="0">
                            <a:schemeClr val="phClr">
                                <a:tint val="80000"/>
                                <a:satMod val="300000"/>
                            </a:schemeClr>
                        </a:gs>
                        <a:gs pos="100000">
                            <a:schemeClr val="phClr">
                                <a:shade val="30000"/>
                                <a:satMod val="200000"/>
                            </a:schemeClr>
                        </a:gs>
                    </a:gsLst>
                    <a:path path="circle">
                        <a:fillToRect l="50000" t="50000" r="50000" b="50000"/>
                    </a:path>
                </a:gradFill>
            </a:bgFillStyleLst>
        </a:fmtScheme>
    </a:themeElements>
    <a:objectDefaults/>
    <a:extraClrSchemeLst/>
</a:theme>
XML
);

		file_put_contents(sprintf("%s/xl/worksheets/_rels/sheet1.xml.rels", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"/>
XML
);

		$this->create_worksheets_sheet($tempFolder, "sheet1", $this->rows);

		file_put_contents(sprintf("%s/xl/sharedStrings.xml", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="0" uniqueCount="0"></sst>
XML
);

		file_put_contents(sprintf("%s/xl/styles.xml", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xml:space="preserve" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <numFmts count="0"/>
    <fonts count="1">
        <font>
            <name val="Calibri"/>
            <sz val="11"/>
            <b val="0"/>
            <i val="0"/>
            <u val="none"/>
            <strike val="0"/>
            <color rgb="FF000000"/>
        </font>
    </fonts>
    <fills count="1">
        <fill>
            <patternFill patternType="none">
                <fgColor rgb="FFFFFFFF"/>
                <bgColor rgb="FF000000"/>
            </patternFill>
        </fill>
    </fills>
    <borders count="1">
        <border>
            <left style="thin">
                <color rgb="FFDDDDDD"/>
            </left>
            <right style="thin">
                <color rgb="FFDDDDDD"/>
            </right>
            <top style="thin">
                <color rgb="FFDDDDDD"/>
            </top>
            <bottom style="thin">
                <color rgb="FFDDDDDD"/>
            </bottom>
        </border>
    </borders>
    <cellStyleXfs count="1">
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
    </cellStyleXfs>
    <cellXfs count="1">
        <xf xfId="0" fontId="0" numFmtId="0" fillId="0" borderId="0" applyFont="0" applyNumberFormat="0" applyFill="0" applyBorder="0" applyAlignment="0">
            <alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" shrinkToFit="false"/>
        </xf>
    </cellXfs>
    <cellStyles count="1">
        <cellStyle name="Normal" xfId="0" builtinId="0"/>
    </cellStyles>
    <dxfs count="0"/>
    <tableStyles defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotTableStyle1"/>
</styleSheet>
XML
);

		file_put_contents(sprintf("%s/xl/workbook.xml", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xml:space="preserve" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>
    <workbookPr codeName="ThisWorkbook"/>
    <bookViews>
        <workbookView activeTab="0" autoFilterDateGrouping="1" firstSheet="0" minimized="0" showHorizontalScroll="1" showSheetTabs="1" showVerticalScroll="1" tabRatio="600" visibility="visible"/>
    </bookViews>
    <sheets>
        <sheet name="{$this->properties['sheetTitle']}" sheetId="1" r:id="rId4"/>
    </sheets>
    <definedNames/>
    <calcPr calcId="124519" calcMode="auto" fullCalcOnLoad="1"/>
</workbook>
XML
);

		file_put_contents(sprintf("%s/[Content_Types].xml", $tempFolder), <<<XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
    <Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>
    <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
    <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
    <Default Extension="xml" ContentType="application/xml"/>
    <Default Extension="vml" ContentType="application/vnd.openxmlformats-officedocument.vmlDrawing"/>
    <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
    <Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
    <Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
    <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
    <Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
</Types>
XML
);

		// create the zip file
		$cwd = getcwd();
		chdir($tempFolder);
		$output = [];
		$return_var = null;
		exec(sprintf("zip -9 -m -r %s *", escapeshellarg($tempFolder."output.zip")), $output, $return_var);
		chdir($cwd);

		rename($tempFolder."output.zip", $file);

		rmdir($tempFolder);
	}
}
