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

<?php

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


class Route_ExportTableAjax extends RouteBase {

    /**
	 * @param $_GET['namespace'] = AclNamespace
	 * @param $_GET['format'] = 'csv' | 'html'
	 * @param $_GET['flds'] = csv - coma separated field names
	 * @param $_GET['sortCol'] = FieldName
	 * @param $_GET['sortDir'] = SortDir ('desc' | 'asc')
	 * @param $_GET['f_{$fieldName}'] = filter
	 * @param $_GET['sf_{$fieldName}'] = force filter
	 */
	function defaultAction() {
		$args = $_GET;
		$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();
		$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', '', $_GET));
		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', $_GET);
		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
		$csvFileName = "Tabela-" . $acl->getName() . "-" . date("Y-m-d_H_s");
		$labels = array();
		foreach ($toExportFields as $fieldName) {
			$labels[ $fieldName ] = $acl->getFieldLabel($fieldName);
		}
		$xw = new XMLWriter();
		$xw->openMemory();
		$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");
		{
			$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');
								$xw->writeAttribute("ss:Type", "String");
								$xw->text($item[$fieldName]);
								$xw->endElement();
							}
							$xw->endElement();
						}
						$xw->endElement();
					}
				}
				$xw->endElement();
			}
			$xw->endElement();
		}
		$xw->endElement();
		$xw->endDocument();

		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
		Lib::loadClass('Vendor_XLSXWriter');
		$csvFileName = "Tabela-" . $acl->getName() . "-" . date("Y-m-d_H_s");
		$labels = array();
		foreach ($toExportFields as $fieldName) {
			$labels[ $fieldName ] = $acl->getFieldLabel($fieldName);
		}
		$xlsx = new Vendor_XLSXWriter();
		$xlsx->writeSheetHeader('Sheet1', array_combine(
			array_values($labels),
			array_map(function ($label) { return 'string'; }, $labels)
		));
		foreach ($items as $item) {
			$row = array_map(function ($fieldName) use ($item) {
				return V::get($fieldName, '', $item);
			}, array_keys($labels));
			$xlsx->writeSheetRow('Sheet1', $row);
		}

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

}