Explore
Snippets

$10

```php
<?php

$sql = <<<___SQL
SELECT
	IFNULL(tblarbetsstalle.forvaltning, "[ Värde saknas ]") AS Forvaltning,
	tblgeonamn.GeoNamn,
	tblarbomr.ArbOmrNamn,
	tblperiod.periodnamn AS Sokperiod,
	COUNT(*) AS Antal
FROM tblplats
LEFT JOIN tblarbetsstalle ON (tblarbetsstalle.orgnr = tblplats.orgnr AND tblarbetsstalle.arbetsstallenamn = tblplats.arbetsstallenamn)
LEFT JOIN tblarbomr ON (tblarbomr.ArbOmrID = tblplats.ArbOmrID)
LEFT JOIN tblgeonamn ON (tblgeonamn.GeoID = tblplats.GeoID)
LEFT JOIN tblperiod ON (tblperiod.PeriodID = tblplats.PeriodID)
___SQL;

$arrWhere = array();
$arrWhere[] = 'tblplats.Inaktiv = 0';
if($params['startdatum']($_POST['startdatum'])) {
	$startdatum = date('Y-m-d', strtotime($_POST['startdatum']));
	$arrWhere[] = 'tblplats.startdatum >= "'.$startdatum.'"';
}
if($params['slutdatum']($_POST['slutdatum'])) {
	$slutdatum = date('Y-m-d', strtotime($_POST['slutdatum']));
	$arrWhere[] = 'tblplats.slutdatum <= "'.$slutdatum.'"';
}
if(!empty($_POST['grupp'])) {
	$arrWhere[] = 'tblplats.GruppID IN ("'.implode('","', $params['grupp']($_POST['grupp'])).'")';
}
if(!empty($arrWhere)) {
	$sql .= ' WHERE ' . implode(' AND ', $arrWhere) . ' ';
}

$sql .= <<<___SQL
GROUP BY
	tblarbetsstalle.forvaltning,
	tblplats.ArbOmrID,
	tblplats.GeoID,
	tblplats.PeriodID
___SQL;

$stmt = $db->query($sql);

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);


if($_POST['submit'] == 'Generera rapport') {
	$result = Helper::recursiveEncode($result, Helper::ENC_ENTITY);
}

if($_POST['submit'] == 'Generera CSV') {
	$result = Helper::recursiveEncode($result, Helper::ENC_UTF8);

	header('Expires: 0');
	header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
	header('Content-Type: text/csv');
	header('Content-Disposition: attachment; filename=rapport_platser_forvaltningar_'.date('Ymd-His').'.csv');
	header('Content-Transfer-Encoding: binary');

	$fd = fopen('php://output', 'w');

	fputs($fd, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) )); // UTF-8 BOM

	fputcsv($fd, array_keys($result[0]), ';');
	foreach($result as $row) {
		fputcsv($fd, $row, ';');
	}
	fclose($fd);

	exit;
}


if($_POST['submit'] == 'Generera Excel') {
	header('Content-Type: application/vnd-ms-excel');
	header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
	header('Content-Transfer-Encoding: binary');
	header('Content-Disposition: attachment; filename=platser-forvaltningar-'.date('Ymd_His').'.xls');
	header('Pragma: public');
	header('Expires: 0');

	?>
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-15">
	<meta name="ProgId" content="Excel.Sheet">
	<style id="arbetsstallen_Styles" type="text/css">
		.nowrap {
			white-space: nowrap;
		}
	</style>
</head>
<body>

<table border="1" bordercolor="#d3d3d3" cellpadding="5" cellspacing="0">
	<tr class="header">
		<th>Frvaltning</th>
		<th>Geografiskt omrde</th>
		<th>Arbestomrde</th>
		<th>Skperiod</th>
		<th>Antal platser</th>
	</tr>
<?php
foreach($result as $row) {
	//var_dump($row);
	?>
	<tr>
		<td class="nowrap" x:str><?=Helper::entityEncode($row['Forvaltning'])?></td>
		<td class="nowrap" x:str><?=Helper::entityEncode($row['GeoNamn'])?></td>
		<td class="nowrap" x:str><?=Helper::entityEncode($row['ArbOmrNamn'])?></td>
		<td class="nowrap" x:str><?=Helper::entityEncode($row['Sokperiod'])?></td>
		<td class="nowrap" x:num><?=$row['Antal']?></td>
	</tr>
	<?php
}
?>
</table>

</body>
</html>
	<?php
	exit;
}
```