Merge branch 'main' into issue-3911issue-3911

1 files changed, 271 insertions, 0 deletions

diff --git a/lib/raumzeit/CycleDataDB.php b/lib/raumzeit/CycleDataDB.php
new file mode 100644
index 0000000..715df0b
--- /dev/null
+++ b/lib/raumzeit/CycleDataDB.php
@@ -0,0 +1,271 @@
+<?
+# Lifter002: TODO
+# Lifter003: TEST
+# Lifter007: TODO
+# Lifter010: TODO
+// +--------------------------------------------------------------------------+
+// This file is part of Stud.IP
+// CycleDataDB.php
+//
+// Datenbank-Abfragen für CycleData.php
+//
+// +--------------------------------------------------------------------------+
+// This program is free software; you can redistribute it and/or
+// modify it under the terms of the GNU General Public License
+// as published by the Free Software Foundation; either version 2
+// of the License, or any later version.
+// +--------------------------------------------------------------------------+
+// This program is distributed in the hope that it will be useful,
+// but WITHOUT ANY WARRANTY; without even the implied warranty of
+// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+// GNU General Public License for more details.
+// You should have received a copy of the GNU General Public License
+// along with this program; if not, write to the Free Software
+// Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
+// +--------------------------------------------------------------------------+
+
+
+/**
+ * CycleDataDB.php
+ *
+ *
+ * @author      Till Glöggler <tgloeggl@uos.de>
+ * @version     19. Oktober 2005
+ * @access      protected
+ * @package     raumzeit
+ */
+class CycleDataDB
+{
+    /**
+     * Returns sorted array of all dates belonging to the passed metadate,
+     * optionally filtered by start- and end-date
+     *
+     * @param  string  $metadate_id
+     * @param  integer $start
+     * @param  integer $end
+     *
+     * @return array
+     */
+    public static function getTermine($metadate_id, $start = 0, $end = 0)
+    {
+        if (($start != 0) || ($end != 0)) {
+            $query = "SELECT termine.*, r.resource_id, GROUP_CONCAT(DISTINCT trp.user_id) AS related_persons, GROUP_CONCAT(DISTINCT trg.statusgruppe_id) AS related_groups
+                      FROM termine
+                      LEFT JOIN termin_related_persons AS trp ON (termine.termin_id = trp.range_id)
+                      LEFT JOIN termin_related_groups AS trg ON (termine.termin_id = trg.termin_id)
+                      LEFT JOIN resource_bookings AS r ON (termine.termin_id = r.range_id)
+                      WHERE metadate_id = ? AND termine.date BETWEEN ? AND ?
+                      GROUP BY termine.termin_id
+                      ORDER BY NULL";
+            $parameters = [$metadate_id, $start, $end];
+        } else {
+            $query = "SELECT termine.*, r.resource_id, GROUP_CONCAT(DISTINCT trp.user_id) AS related_persons, GROUP_CONCAT(DISTINCT trg.statusgruppe_id) AS related_groups
+                      FROM termine
+                        LEFT JOIN termin_related_persons AS trp ON (termine.termin_id = trp.range_id)
+                        LEFT JOIN termin_related_groups AS trg ON (termine.termin_id = trg.termin_id)
+                        LEFT JOIN resource_bookings AS r ON (termine.termin_id = r.range_id)
+                      WHERE metadate_id = ?
+                      GROUP BY termine.termin_id
+                      ORDER BY NULL";
+            $parameters = [$metadate_id];
+        }
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute($parameters);
+
+        $ret = [];
+        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
+            $data = $row;
+            $data['related_persons'] = array_filter(explode(',', $data['related_persons']));
+            $data['related_groups'] = array_filter(explode(',', $data['related_groups']));
+            $ret[] = $data;
+        }
+
+        if (($start != 0) || ($end != 0)) {
+            $query = "SELECT ex_termine.*, GROUP_CONCAT(DISTINCT trp.user_id) AS related_persons, GROUP_CONCAT(DISTINCT trg.statusgruppe_id) AS related_groups
+                      FROM ex_termine
+                        LEFT JOIN termin_related_persons AS trp ON (ex_termine.termin_id = trp.range_id)
+                        LEFT JOIN termin_related_groups AS trg ON (ex_termine.termin_id = trg.termin_id)
+                      WHERE metadate_id = ? AND `date` BETWEEN ? AND ?
+                      GROUP BY ex_termine.termin_id
+                      ORDER BY NULL";
+            $parameters = [$metadate_id, $start, $end];
+        } else {
+            $query = "SELECT ex_termine.*, GROUP_CONCAT(DISTINCT trp.user_id) AS related_persons, GROUP_CONCAT(DISTINCT trg.statusgruppe_id) AS related_groups
+                      FROM ex_termine
+                        LEFT JOIN termin_related_persons AS trp ON (ex_termine.termin_id = trp.range_id)
+                        LEFT JOIN termin_related_groups AS trg ON (ex_termine.termin_id = trg.termin_id)
+                      WHERE metadate_id = ?
+                      GROUP BY ex_termine.termin_id
+                      ORDER BY NULL";
+            $parameters = [$metadate_id];
+        }
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute($parameters);
+
+        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
+            $zw = $row;
+            $zw['ex_termin'] = TRUE;
+            $zw['related_persons'] = array_filter(explode(',', $zw['related_persons']));
+            $zw['related_groups'] = array_filter(explode(',', $zw['related_groups']));
+            $ret[] = $zw;
+        }
+
+        if ($ret) {
+            usort($ret, 'CycleDataDB::sort_dates');
+            return $ret;
+        }
+
+        return FALSE;
+    }
+
+    public static function sort_dates($a, $b)
+    {
+        if ($a['date'] == $b['date']) return 0;
+        return ($a['date'] < $b['date']) ? -1 : 1;
+    }
+
+    /**
+     * Deletes all dates that are newer then the passed date for metadate
+     * with the passed id
+     *
+     * @param  string  $metadate_id
+     * @param  int  $timestamp
+     *
+     * @return int  number of deleted singledates
+     */
+    public static function deleteNewerSingleDates($metadate_id, $timestamp)
+    {
+        $count = 0;
+
+        $query = "SELECT termin_id
+                  FROM termine
+                  WHERE metadate_id = ? AND `date` > ?";
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute([$metadate_id, $timestamp]);
+        while ($termin_id = $statement->fetchColumn()) {
+            $termin = new SingleDate($termin_id);
+            $termin->delete();
+            unset($termin);
+
+            $count += 1;
+        }
+
+        $query = "DELETE FROM termine WHERE metadate_id = ? AND `date` > ?";
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute([$metadate_id, $timestamp]);
+
+        $query = "DELETE FROM ex_termine WHERE metadate_id = ? AND `date` > ?";
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute([$metadate_id, $timestamp]);
+
+        return $count;
+    }
+
+    /**
+     * Returns the list of booked rooms ordered by number of appearance
+     * in the metadate with the passed id
+     *
+     * @param  string  $metadate_id
+     * @param  integer $filterStart
+     * @param  integer $filterEnd
+     *
+     * @return array [resource_id, number_of_appearances]
+     */
+    public static function getPredominantRoomDB($metadate_id, $filterStart = 0, $filterEnd = 0)
+    {
+        if (($filterStart == 0) && ($filterEnd == 0)) {
+            $query = "SELECT resource_id, COUNT(resource_id) AS c
+                      FROM termine
+                      INNER JOIN resource_bookings ON (termin_id = resource_bookings.range_id)
+                      WHERE termine.metadate_id = ? AND resource_id != ''
+                      GROUP BY resource_id
+                      ORDER BY c DESC";
+            $parameters = [$metadate_id];
+        } else {
+            $query = "SELECT resource_id, COUNT(resource_id) AS c
+                      FROM termine
+                      INNER JOIN resource_bookings ON (termin_id = resource_bookings.range_id)
+                      WHERE termine.metadate_id = ? AND termine.date BETWEEN ? AND ?
+                      GROUP BY resource_id
+                      ORDER BY c DESC";
+            $parameters = [$metadate_id, $filterStart, $filterEnd];
+        }
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute($parameters);
+        return $statement->fetchGrouped(PDO::FETCH_COLUMN) ?: false;
+    }
+
+    /**
+     * Returns the list of freetext rooms ordered by number of appearance
+     * in the metadate with the passed id
+     *
+     * @param  [type]  $metadate_id
+     * @param  integer $filterStart
+     * @param  integer $filterEnd
+     *
+     * @return array [freetex, number_of_appearances]
+     */
+    public static function getFreeTextPredominantRoomDB($metadate_id, $filterStart = 0, $filterEnd = 0)
+    {
+        if (($filterStart == 0) && ($filterEnd == 0)) {
+            $query = "SELECT raum, COUNT(raum) AS c
+                      FROM termine
+                      LEFT JOIN resource_bookings ON (termin_id = resource_bookings.range_id)
+                      WHERE termine.metadate_id = ? AND resource_bookings.range_id IS NULL
+                      GROUP BY raum
+                      ORDER BY c DESC";
+            $parameters = [$metadate_id];
+        } else {
+            $query = "SELECT raum, COUNT(raum) AS c
+                      FROM termine
+                      LEFT JOIN resource_bookings ON (termin_id = resource_bookings.range_id)
+                      WHERE termine.metadate_id = ? AND resource_bookings.range_id IS NULL
+                        AND termine.date BETWEEN ? AND ?
+                      GROUP BY raum
+                      ORDER BY c DESC";
+            $parameters = [$metadate_id, $filterStart, $filterEnd];
+        }
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute($parameters);
+        return $statement->fetchGrouped(PDO::FETCH_COLUMN) ?: false;
+    }
+
+    /**
+     * returns the first date for a given metadate_id as array
+     *
+     * @param string $metadate_id
+     *
+     * @return array
+     */
+    public static function getFirstDate($metadate_id)
+    {
+        $query = "SELECT *
+                  FROM termine
+                  WHERE metadate_id = ?
+                  ORDER BY `date` ASC
+                  LIMIT 1";
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute([$metadate_id]);
+        return $statement->fetch(PDO::FETCH_ASSOC);
+    }
+
+
+    /**
+     * returns the last date for a given metadate_id as array
+     *
+     * @param string $metadate_id
+     *
+     * @return array
+     */
+    public static function getLastDate($metadate_id)
+    {
+        $query = "SELECT *
+                  FROM termine
+                  WHERE metadate_id = ?
+                  ORDER BY `date` DESC
+                  LIMIT 1";
+        $statement = DBManager::get()->prepare($query);
+        $statement->execute([$metadate_id]);
+        return $statement->fetch(PDO::FETCH_ASSOC);
+    }
+}