Yaslaw.Info

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

======[PHP] Monats Schicht Plan======
<code 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
	(
		-- [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
</code>

<code php>
<?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));

?></code>
Yaslaw.Info

User Tools

Site Tools

Page Tools
