User Tools

Site Tools


php:kompost:moantsschichtplan

[PHP] Monats Schicht Plan

SELECT
	@month_date := DATE(CONCAT_WS('-', YEAR(select_month), MONTH(select_month), day_of_month)) AS month_date,
	UNIX_TIMESTAMP(@month_date) AS unix_data,
	my_data.name,
	IF(@month_date BETWEEN holiday_start AND holiday_end, 'X', '')    AS is_in_holiday
FROM
	(
		-- [Variablen]
		-- Ein Datum, dass den Monat und das Jahr vorgibt. 
		-- Kann ein beliebiger Tag im entsprechenden Monat sein
		SELECT FROM_UNIXTIME({$selectDate}) AS select_month
		-- [/Variablen]
	) AS my_vars,
   (
	   -- [Tagesnummern]
	   -- Zahlen von 1 bis 32
	   SELECT
		   @day_of_month := @day_of_month +1 AS day_of_month
	   FROM
		   (SELECT @day_of_month :=0) AS vars,
		   -- [VirtualRows]
		   -- 32 Zeilen um die Tageszeilen zu erstellen (4*4*2)
		   (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d1,
		   (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d2,
		   (SELECT 1 UNION SELECT 2) AS d3
		   -- [/VirtualRows]
	   -- [/Tagesnummern]
   ) AS days_of_month,
	(
		-- [Daten]
		-- Simulieren der DB-Daten
		SELECT 'Hans' AS name, DATE('2012-01-05') AS holiday_start, DATE('2012-01-15') AS holiday_end 
		UNION SELECT 'Paul', DATE('2012-01-25'), DATE('2012-02-05')
		-- [/Daten]
	) AS my_data   
WHERE
 day_of_month <= DAY(LAST_DAY(select_month))
ORDER BY
	name,
	month_date
<?php
include('connect.php');
var_dump(__FILE__);
//Als Beispiel mal der Janaur 2012
$selectDate = mktime(0,0,0, 1, 1, 2012);
 
$sql = <<<SQL
SELECT
	@month_date := DATE(CONCAT_WS('-', YEAR(select_month), MONTH(select_month), day_of_month)) AS month_date,
	UNIX_TIMESTAMP(@month_date) AS unix_data,
	my_data.name,
	IF(@month_date BETWEEN holiday_start AND holiday_end, 'X', '')    AS is_in_holiday
FROM
	(
		SELECT FROM_UNIXTIME({$selectDate}) AS select_month
	) AS my_vars,
   (
	   SELECT
		   @day_of_month := @day_of_month +1 AS day_of_month
	   FROM
		   (SELECT @day_of_month :=0) AS vars,
		   (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d1,
		   (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS d2,
		   (SELECT 1 UNION SELECT 2) AS d3
   ) AS days_of_month,
	(
		-- [Daten]
		-- Simulieren der DB-Daten
		SELECT 'Hans' AS name, DATE('2012-01-05') AS holiday_start, DATE('2012-01-15') AS holiday_end 
		UNION SELECT 'Paul', DATE('2012-01-25'), DATE('2012-02-05')
		-- [/Daten]
	) AS my_data   
WHERE
 day_of_month <= DAY(LAST_DAY(select_month))
ORDER BY
	name,
	month_date
SQL;
 
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)){
	$unix_data[] = $row['unix_data'];
	$data[$row['name']][] = $row['is_in_holiday'];
}
 
//Tabellenpattern
$tableHtml = <<<HTML
<table>
%s
</table>
HTML;
$trHtml = <<<HTML
	<tr>
%s
	</tr>
HTML;
$tdHtml = <<<HTML
		<%1\$s><%2\$s></%1\$s>
HTML;
 
$month_date = array_unique($unix_data);
 
$tds = array(sprintf($tdHtml, 'th', ''));
foreach($month_date AS $value) $tds[] = sprintf($tdHtml, 'th', date('d. M', $value));
 
$trs[] = sprintf($trHtml, implode("\n", $tds));
 
foreach($data as $rowHeader => $values){
	$tds = array();
	$tds[] = sprintf($tdHtml, 'th', $rowHeader);
	foreach($values as $value)         $tds[] = sprintf($tdHtml, 'td', $value);
	$trs[] = sprintf($trHtml, implode("\n", $tds));
}
printf($tableHtml, implode("\n", $trs));
 
?>
php/kompost/moantsschichtplan.txt · Last modified: 11.12.2013 11:20:48 (external edit)