se/SE/se-lib/Route/ExportTableAjax.php

<?php

Lib::loadClass('RouteBase');
Lib::loadClass('Core_AclHelper');
Lib::loadClass('Response');


class Route_ExportTableAjax extends RouteBase {

    /**
	 * @param $_REQUEST['namespace'] = AclNamespace
	 * @param $_REQUEST['format'] = 'csv' | 'html'
	 * @param $_REQUEST['flds'] = csv - coma separated field names
	 * @param $_REQUEST['sortCol'] = FieldName
	 * @param $_REQUEST['sortDir'] = SortDir ('desc' | 'asc')
	 * @param $_REQUEST['f_{$fieldName}'] = filter
	 * @param $_REQUEST['sf_{$fieldName}'] = force filter
	 * @param $_POST['backRefNS'] - back ref filter
	 * @param $_POST['backRefPK']
	 * @param $_POST['backRefField']
	 * @param $_POST['childRefNS'] - child ref filter
	 * @param $_POST['childRefPK']
	 */
	function defaultAction() {
		if (!empty($_POST)) {
			$backRefFilter = [
				'namespace' => V::get('backRefNS', '', $_POST),
				'primaryKey' => V::get('backRefPK', '', $_POST),
				'fieldName' => V::get('backRefField', '', $_POST),
			];
			$childRefFilter = [
				'namespace' => V::get('childRefNS', '', $_POST),
				'primaryKey' => V::get('childRefPK', '', $_POST),
			];

			return $this->executeExport(
				$_REQUEST // `f_%` may be sent by GET
				, (!empty($backRefFilter['namespace']) && !empty($backRefFilter['primaryKey'])) ? $backRefFilter : null
				, (!empty($childRefFilter['namespace']) && !empty($childRefFilter['primaryKey'])) ? $childRefFilter : null
			);
		}
		$this->executeExport($_GET);
	}

	function executeExport($args, $backRefFilter = null, $childRefFilter = null) {
		$namespace = V::get('namespace', '', $args, 'word');
		if (!$namespace) throw new HttpException("Bad Request - missing namespace", 400);
		$acl = Core_AclHelper::getAclByNamespace($namespace);

		$exportLimit = 10000;
		$params = array();

		if ($backRefFilter) {
			$params['__backRef'] = $backRefFilter;
			DBG::log($params, 'array', '$params __backRef');
		}
		if ($childRefFilter) {
			$params['__childRef'] = $childRefFilter;
			DBG::log($params, 'array', '$params __childRef');
		}

		$params['limit'] = $exportLimit;
		// $params['limitstart'] = 0;
		$params['order_by'] = V::get('sortCol', '', $args);
		$params['order_dir'] = V::get('sortDir', '', $args);
		$params['cols'] = array($acl->getPrimaryKeyField());
		$toExportFields = explode(',', V::get('flds', '', $args));
		$toExportFields = array_filter($toExportFields, [ 'V', 'filterNotEmpty' ]);
		if (empty($toExportFields)) throw new Exception("Nie wybrano żandych pól do exportu.");
		$allowedExportFieldList = Core_AclHelper::getExportFieldList($acl);
		foreach ($toExportFields as $fieldName) {
			if ($fieldName == $acl->getPrimaryKeyField()) continue;
			if (!in_array($fieldName, $allowedExportFieldList)) throw new Exception("Brak uprawnień do exportu pola '{$fieldName}'");
			$params['cols'][] = $fieldName;
		}

		foreach ($args as $k => $v) {
			if (strlen($k) > 3 && substr($k, 0, 2) == 'f_' && strlen($v) > 0) {// filter prefix
				$params[$k] = $v;
			}
			else if (strlen($k) > 4 && substr($k, 0, 3) == 'sf_' && strlen($v) > 0) {// special filter prefix
				$params[$k] = $v;
			}
		}
		try {
			$queryFeatures = $acl->buildQuery($params);
			$total = $queryFeatures->getTotal();
			$listItems = $queryFeatures->getItems();
			$primaryKeyField = $acl->getPrimaryKeyField();
			$items = []; foreach ($listItems as $item) $items[ $item[$primaryKeyField] ] = $item;
		} catch (Exception $e) {
			DBG::log($e);
			throw $e;
		}

		$format = V::get('format', 'html', $args);
		switch ($format) {
			case 'html': return $this->_exportToHTML($acl, $items, $toExportFields, $format);
			case 'xls': return $this->_exportToXLS($acl, $items, $toExportFields, $format);
			case 'xlsx': return $this->_exportToXLSX($acl, $items, $toExportFields, $format);
			case 'csv_cp1250': return $this->_exportToCSV($acl, $items, $toExportFields, $format);
			case 'csv':  return $this->_exportToCSV($acl, $items, $toExportFields, $format);
		}
		die("Nieobsługiwany format danych.");
	}

	function _exportToHTML($acl, $items, $toExportFields, $format = 'html') {
		$labels = array();
		foreach ($toExportFields as $fieldName) {
			$labels[ $fieldName ] = $acl->getFieldLabel($fieldName);
		}
		UI::gora();
		echo UI::h('table', ['class'=>'table table-bordered table-hover'], [
			UI::h('thead', [], [
				UI::h('tr', [], array_map(function ($label) {
					return UI::h('th', [], $label);
				}, $labels))
			]),
			UI::h('tbody', [], array_map(function ($item) use($labels) {
				return UI::h('tr', [], array_map(function ($fieldName) use ($item) {
					return UI::h('td', [], V::get($fieldName, '', $item));
				}, array_keys($labels)));
			}, $items)),
		]);
		UI::dol();
	}
	function _exportToCSV($acl, $items, $toExportFields, $format = 'csv') {
		$csvFileName = "Tabela-" . $acl->getName() . "-" . date("Y-m-d_H_s");
		$csvSeparator = ';';
		$labels = array();
		foreach ($toExportFields as $fieldName) {
			$labels[ $fieldName ] = $acl->getFieldLabel($fieldName);
		}
		$csvHeader = implode($csvSeparator, array_map(function ($label) use ($item) {
			return '"' . addslashes($label) . '"';
		}, array_values($labels)));
		$csvRows = implode("\r\n", array_map(function ($item) use ($labels, $csvSeparator) {
			return implode($csvSeparator, array_map(function ($fieldName) use ($item) {
				return '"' . addslashes(V::get($fieldName, '', $item)) . '"';
			}, array_keys($labels)));
		}, $items));

		header('Content-Type: text/csv; charset=utf-8');
		header("Content-Disposition: attachment; filename={$csvFileName}.csv");
		switch ($format) {
			case 'csv': echo $csvHeader . "\n" . $csvRows; exit;
			case 'csv_cp1250': echo iconv('utf-8', 'Windows-1250//IGNORE', $csvHeader) . "\r\n" . iconv('utf-8', 'Windows-1250//IGNORE', $csvRows); exit;
			die("Nieobsługiwane kodowanie danych csv.");
		}
	}
	function _exportToXLS($acl, $items, $toExportFields, $format = 'xls') {
		// https://en.wikipedia.org/wiki/Microsoft_Excel#XML_Spreadsheet

		$DBG_OUTPUT = 0;

		$csvFileName = "Tabela-" . $acl->getName() . "-" . date("Y-m-d_H_s");
		$labels = array();
		foreach ($toExportFields as $fieldName) {
			$label = $acl->getFieldLabel($fieldName);
			$labels[ $fieldName ] = ($label) ? $label : $fieldName;
		}

		if ($DBG_OUTPUT) {
			$items = array_slice($items, 0, 10);
			DBG::nicePrint($acl, '$acl');
			DBG::nicePrint($labels, '$labels');
			DBG::nicePrint($toExportFields, '$toExportFields');
		}

		$xw = new XMLWriter();
		$xw->openMemory();
		if ($DBG_OUTPUT) $xw->setIndent(true); //set the indentation to true (if false all the xml will be written on one line)
		$xw->startDocument("1.0");
		$xw->startElement("Workbook");
		$xw->writeAttribute("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
		$xw->writeAttribute("xmlns:o", "urn:schemas-microsoft-com:office:office");
		$xw->writeAttribute("xmlns:x", "urn:schemas-microsoft-com:office:excel");
		$xw->writeAttribute("xmlns:ss", "urn:schemas-microsoft-com:office:spreadsheet");
		$xw->writeAttribute("xmlns:html", "http://www.w3.org/TR/REC-html40");
		{
			// TODO: format date 'Y-m-d' - not working in LibreOffice
			// <Styles>
			// 	<Style ss:ID="p5FieldType_Date">
			// 		<NumberFormat ss:Format="yyyy\-mm\-dd"/>
			// 	</Style>
			// 	<Style ss:ID="p5FieldType_Date2">
			// 		<!-- <NumberFormat ss:Format="YYYY\-MM\-DD"/> -->
			// 		<NumberFormat ss:Format="YYYY\-MM\-DD"/>
			// 	</Style>
			// 	<Style ss:ID="p5FieldType_Date3">
			// 		<!-- <NumberFormat ss:Format="YYYY\-MM\-DD"/> -->
			// 		<DateFormat ss:Format="YYYY\-MM\-DD"/>
			// 	</Style>
			// </Styles>

			$xw->startElement("Worksheet");
			$xw->writeAttribute("ss:Name", "Sheet1");
			{
				$xw->startElement("Table");
				$xw->writeAttribute("ss:ExpandedColumnCount", "2");
				$xw->writeAttribute("ss:ExpandedRowCount", "2");
				$xw->writeAttribute("x:FullColumns", "1");
				$xw->writeAttribute("x:FullRows", "1");
				{
					$xw->startElement("Row");
					foreach ($labels as $fieldName => $label) {
						$xw->startElement('Cell');
						{
							$xw->startElement('Data');
							$xw->writeAttribute("ss:Type", "String");
							$xw->text(str_replace([ '<br>', '<br/>' ], "\n", $label));
							$xw->endElement();
						}
						$xw->endElement();
					}
					$xw->endElement();
					foreach ($items as $item) {
						$xw->startElement("Row");
						foreach ($labels as $fieldName => $label) {
							$xw->startElement('Cell');
							{
								$xw->startElement('Data');

								// ss:Type:
								// - <xsd:enumeration value="Number" />
								// - <xsd:enumeration value="DateTime" />
								// - <xsd:enumeration value="Boolean" />
								// - <xsd:enumeration value="String" />
								// - <xsd:enumeration value="Error" />

								$xsdType = $acl->getXsdFieldType($fieldName);
								switch ($xsdType) {
									case 'xsd:double':
									{
										if (!empty($item[$fieldName])) {
											$xw->writeAttribute("ss:Type", "Number");
										} else {
											$xw->writeAttribute("ss:Type", "String");
										}
										break;
									}
									// case 'xsd:date':
									// {
									// 	if (!empty($item[$fieldName])) {
									// 		$xw->writeAttribute("ss:Type", "Date");
									// 	} else {
									// 		$xw->writeAttribute("ss:Type", "String");
									// 	}
									// 	break;
									// }
									default: $xw->writeAttribute("ss:Type", "String");
								}
								$xw->text($item[$fieldName]);
								$xw->endElement();
							}
							$xw->endElement();
						}
						$xw->endElement();
					}
				}
				$xw->endElement();
			}
			$xw->endElement();
		}
		$xw->endElement();
		$xw->endDocument();

		if (!$DBG_OUTPUT) {
			header("Content-Type: text/xml; charset=utf-8");
			header("Content-Disposition: attachment; filename={$csvFileName}.xls");
		}
		echo $xw->outputMemory();
	}
	function _exportToXLSX($acl, $items, $toExportFields, $format = 'xlsx') {
		// https://github.com/mk-j/PHP_XLSXWriter

		$DBG_OUTPUT = 0;
		if ($DBG_OUTPUT) {
			$items = array_slice($items, 0, 10);
			DBG::nicePrint($acl, '$acl');
		}

		Lib::loadClass('Vendor_XLSXWriter');
		$csvFileName = "Tabela-" . $acl->getName() . "-" . date("Y-m-d_H_s");
		$labels = array();
		$columnTypes = array();
		// string	@
		// integer	0
		// date		YYYY-MM-DD
		// datetime	YYYY-MM-DD HH:MM:SS
		// time		HH:MM:SS
		// price	#,##0.00
		// dollar	[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00
		// euro		#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]
		foreach ($toExportFields as $fieldName) {
			$label = $acl->getFieldLabel($fieldName);
			$labels[ $fieldName ] = ($label) ? $label : $fieldName;

			$typeXLSL = 'string';
			$xsdType = $acl->getXsdFieldType($fieldName);
			switch ($xsdType) {
				case 'xsd:double': $typeXLSL = 'price'; break;
				case 'xsd:date': $typeXLSL = 'date'; break;
				case 'xsd:dateTime': $typeXLSL = 'datetime'; break;
			}
			$columnTypes[ $fieldName ] = $typeXLSL;
		}
		$xlsx = new Vendor_XLSXWriter();
		$xlsx->writeSheetHeader('Sheet1', array_combine(
			array_values($labels),
			// array_map(function ($label) { return 'string'; }, $labels)
			array_values($columnTypes)
		));
		foreach ($items as $item) {
			$row = array_map(function ($fieldName) use ($item) {
				return V::get($fieldName, '', $item);
			}, array_keys($labels));
			$xlsx->writeSheetRow('Sheet1', $row);
		}

		if (!$DBG_OUTPUT) {
			header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");
			header("Content-Disposition: attachment; filename={$csvFileName}.xlsx");
		}
		$xlsx->writeToStdOut();
	}

}