From e90e6085dfd35ee101e43b0fa0f84a71a19f7208 Mon Sep 17 00:00:00 2001
From: Jan-Philipp Litza <janphilipp@litza.de>
Date: Tue, 27 Dec 2011 13:22:43 +0100
Subject: fixed various bug in DB update scripts

---
 db/update.d/01_faq.php                       |  3 ++-
 db/update.d/06_Shifts.php                    |  2 ++
 db/update.d/11_Room_and_NeededAngelTypes.php | 19 +++++++++++++++++++
 3 files changed, 23 insertions(+), 1 deletion(-)

(limited to 'db')

diff --git a/db/update.d/01_faq.php b/db/update.d/01_faq.php
index 92a7376d..1e32f1ad 100644
--- a/db/update.d/01_faq.php
+++ b/db/update.d/01_faq.php
@@ -7,7 +7,8 @@ if(sql_num_query("DESCRIBE `FAQ` `Sprache`") === 0) {
     while($row = mysql_fetch_assoc($res)) {
         $question = explode('<br>', $row['Frage'], 2);
         $answer = explode('<br>', $row['Antwort'], 2);
-        sql_query("INSERT INTO `FAQ` (`Frage`, `Antwort`, `Sprache`) VALUES ('" . sql_escape(trim($question[1])) . "', '" . sql_escape(trim($answer[1])) . "', 'en')");
+        if(count($question) == 2 && count($answer) == 2)
+            sql_query("INSERT INTO `FAQ` (`Frage`, `Antwort`, `Sprache`) VALUES ('" . sql_escape(trim($question[1])) . "', '" . sql_escape(trim($answer[1])) . "', 'en')");
         sql_query("UPDATE `FAQ` SET `Frage` = '" . sql_escape(trim($question[0])) . "', `Antwort` = '" . sql_escape(trim($answer[0])) . "', `Sprache` = 'de' WHERE `FID` = " . $row['FID']);
     }
 
diff --git a/db/update.d/06_Shifts.php b/db/update.d/06_Shifts.php
index 721d73bc..4aeb59b1 100644
--- a/db/update.d/06_Shifts.php
+++ b/db/update.d/06_Shifts.php
@@ -7,6 +7,8 @@ if(sql_num_query("SHOW COLUMNS FROM `Shifts` LIKE 'Date_'") == 2) {
             sql_query("ALTER TABLE `Shifts` DROP `Len`");
         }
     }
+    _datetime_to_int("Shifts", "DateS");
+    _datetime_to_int("Shifts", "DateE");
     sql_query("ALTER TABLE `Shifts` CHANGE `DateS` `start` INT NOT NULL, CHANGE `DateE` `end` INT NOT NULL");
 
     $applied = true;
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