aboutsummaryrefslogtreecommitdiff
path: root/lib/raumzeit/CycleDataDB.php
diff options
context:
space:
mode:
Diffstat (limited to 'lib/raumzeit/CycleDataDB.php')
-rw-r--r--lib/raumzeit/CycleDataDB.php271
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);
+ }
+}
generated by cgit v1.0 at 2026-05-27 05:03:06 +0000