From e90e6085dfd35ee101e43b0fa0f84a71a19f7208 Mon Sep 17 00:00:00 2001 From: Jan-Philipp Litza Date: Tue, 27 Dec 2011 13:22:43 +0100 Subject: fixed various bug in DB update scripts --- db/update.d/11_Room_and_NeededAngelTypes.php | 19 +++++++++++++++++++ 1 file changed, 19 insertions(+) (limited to 'db/update.d/11_Room_and_NeededAngelTypes.php') diff --git a/db/update.d/11_Room_and_NeededAngelTypes.php b/db/update.d/11_Room_and_NeededAngelTypes.php index f5b1c5b4..31cf7f06 100644 --- a/db/update.d/11_Room_and_NeededAngelTypes.php +++ b/db/update.d/11_Room_and_NeededAngelTypes.php @@ -29,5 +29,24 @@ if(sql_num_query("SHOW TABLES LIKE 'NeededAngelTypes'") === 0) { $applied = true; } + +if(sql_num_query("SELECT * FROM `ShiftEntry` WHERE `UID` = 0")) { + $data = sql_query(" + INSERT INTO `NeededAngelTypes` (`shift_id`, `angel_type_id`, `count`) + SELECT se.`SID`, se.`TID`, se.`count` FROM ( + SELECT `SID`, `TID`, COUNT(`TID`) AS `count` + FROM `ShiftEntry` + GROUP BY `SID`, `TID` + ) AS se + INNER JOIN `Shifts` AS s ON s.`SID` = se.`SID` + INNER JOIN `Room` AS r ON s.`RID` = r.`RID` + LEFT JOIN `NeededAngelTypes` AS nat ON (nat.`room_id` = r.`RID` AND nat.`angel_type_id` = se.`TID`) + WHERE nat.`count` IS NULL OR nat.`count` != se.`count` + "); + + sql_query("DELETE FROM `ShiftEntry` WHERE `UID` = 0 AND `Comment` IS NULL"); + + $applied = true; +} _add_index("Room", array("Name")); ?> -- cgit v1.2.3-54-g00ecf