[locations/rebootcontrol] Inherit openingtimes for WOL/shutdown

The opening times schedule is now inherited to child locations, so it's
easy to toggle WOL or shutdown for individual rooms in a building, where
you only have to set the opening times once for the entire building.
As of now, WOL and shutdown settings are *not* inherited to child
locations, as I'm not sure if you always want to inherit those by
default.
Closes #3710

9 files changed, 285 insertions, 96 deletions

diff --git a/inc/database.inc.php b/inc/database.inc.php
index eddd4faf..3e8ee0f8 100644
--- a/inc/database.inc.php
+++ b/inc/database.inc.php
@@ -91,6 +91,19 @@ class Database
 	}
 
 	/**
+	 * Fetch two columns as key => value list.
+	 *
+	 * @return array|bool Associative array, first column is key, second column is value
+	 */
+	public static function queryKeyValueList($query, $args = array(), $ignoreError = null)
+	{
+		$res = self::simpleQuery($query, $args, $ignoreError);
+		if ($res === false)
+			return false;
+		return $res->fetchAll(PDO::FETCH_KEY_PAIR);
+	}
+
+	/**
 	 * Execute the given query and return the number of rows affected.
 	 * Mostly useful for UPDATEs or INSERTs
 	 *
diff --git a/modules-available/locations/inc/openingtimes.inc.php b/modules-available/locations/inc/openingtimes.inc.php
new file mode 100644
index 00000000..3417a213
--- /dev/null
+++ b/modules-available/locations/inc/openingtimes.inc.php
@@ -0,0 +1,28 @@
+<?php
+
+class OpeningTimes
+{
+
+	/**
+	 * Get opening times for given location.
+	 */
+	public static function forLocation(int $locationId)
+	{
+		static $openingTimesList = false;
+		if ($openingTimesList === false) {
+			$openingTimesList = Database::queryKeyValueList("SELECT locationid, openingtime FROM location
+				WHERE openingtime IS NOT NULL");
+		}
+		$chain = Location::getLocationRootChain($locationId);
+		$openingTimes = null;
+		foreach ($chain as $lid) {
+			if (isset($openingTimesList[$lid])) {
+				if (is_string($openingTimesList[$lid])) {
+					$openingTimesList[$lid] = json_decode($openingTimesList[$lid], true);
+				}
+				return $openingTimesList[$lid];
+			}
+		}
+		return null;
+	}
+}
\ No newline at end of file
diff --git a/modules-available/locations/install.inc.php b/modules-available/locations/install.inc.php
index c5fd9688..46a6544c 100644
--- a/modules-available/locations/install.inc.php
+++ b/modules-available/locations/install.inc.php
@@ -15,7 +15,7 @@ $res[] = tableCreate('location', '
 	`locationid` INT(11) NOT NULL AUTO_INCREMENT,
 	`parentlocationid` INT(11) NOT NULL,
 	`locationname` VARCHAR(100) NOT NULL,
-	`openingtime` BLOB,
+	`openingtime` BLOB DEFAULT NULL,
 	PRIMARY KEY (`locationid`),
 	KEY `locationname` (`locationname`),
 	KEY `parentlocationid` (`parentlocationid`)
@@ -40,7 +40,7 @@ $res[] = tableAddConstraint('setting_location', 'locationid', 'location', 'locat
 
 // 2020-07-14 Add openingtime column to location table, then migrate data and delete the column from locationinfo
 if (!tableHasColumn('location', 'openingtime')) {
-	if (Database::exec("ALTER TABLE location ADD openingtime BLOB") === false) {
+	if (Database::exec("ALTER TABLE location ADD openingtime BLOB DEFAULT NULL") === false) {
 		finalResponse(UPDATE_FAILED, 'Could not create openingtime column');
 	}
 	$res[] = UPDATE_DONE;
@@ -60,5 +60,8 @@ if (tableHasColumn('locationinfo_locationconfig', 'openingtime')) {
 	$res[] = UPDATE_DONE;
 }
 
+// 2021-03-19: Fix this. No idea how this came to be, maybe during dev only? But better be safe...
+Database::exec("UPDATE location SET openingtime = NULL WHERE openingtime = ''");
+
 // Create response for browser
 responseFromArray($res);
diff --git a/modules-available/locations/lang/de/template-tags.json b/modules-available/locations/lang/de/template-tags.json
index 79bdbac6..94b348d2 100644
--- a/modules-available/locations/lang/de/template-tags.json
+++ b/modules-available/locations/lang/de/template-tags.json
@@ -15,6 +15,7 @@
     "lang_endAddress": "Endadresse",
     "lang_expertMode": "Expertenmodus",
     "lang_fixMachineAssign": "Zuweisungen anzeigen\/aufheben",
+    "lang_inheritOpeningTimes": "Vom \u00fcbergeordneten Ort \u00fcbernehmen",
     "lang_ip": "IP-Adresse",
     "lang_listOfSubnets": "Liste der Subnetze",
     "lang_location": "Ort",
diff --git a/modules-available/locations/lang/en/template-tags.json b/modules-available/locations/lang/en/template-tags.json
index 5790902b..d60aa291 100644
--- a/modules-available/locations/lang/en/template-tags.json
+++ b/modules-available/locations/lang/en/template-tags.json
@@ -15,6 +15,7 @@
     "lang_endAddress": "End address",
     "lang_expertMode": "Expert mode",
     "lang_fixMachineAssign": "Fix or remove assignment",
+    "lang_inheritOpeningTimes": "Inherit from parent location",
     "lang_ip": "IP address",
     "lang_listOfSubnets": "List of subnets",
     "lang_location": "Location",
diff --git a/modules-available/locations/pages/details.inc.php b/modules-available/locations/pages/details.inc.php
index 19a89c88..356620d3 100644
--- a/modules-available/locations/pages/details.inc.php
+++ b/modules-available/locations/pages/details.inc.php
@@ -35,6 +35,7 @@ class SubPage
 
 	private static function updateOpeningTimes()
 	{
+		$otInherited = Request::post('openingtimes-inherited', false, 'bool');
 		$openingTimes = Request::post('openingtimes', Request::REQUIRED, 'string');
 		$locationid = Request::post('locationid', Request::REQUIRED, 'int');
 		$wol = Request::post('wol', false, 'bool');
@@ -45,10 +46,12 @@ class SubPage
 		User::assertPermission('location.edit.openingtimes', $locationid);
 
 		// Construct opening-times for database
-		if ($openingTimes !== '') {
+		if ($otInherited || $openingTimes === '') {
+			$openingTimes = null;
+		} else {
 			$openingTimes = json_decode($openingTimes, true);
 			if (!is_array($openingTimes)) {
-				$openingTimes = '';
+				$openingTimes = null;
 			} else {
 				$mangled = array();
 				foreach (array_keys($openingTimes) as $key) {
@@ -89,32 +92,8 @@ class SubPage
 			array('locationid' => $locationid, 'openingtime' => $openingTimes));
 
 		if (Module::isAvailable('rebootcontrol')) {
-			if ($wol || $sd) {
-				$options = array();
-
-				// Sanity checks
-				if ($woloffset > 15) {
-					$woloffset = 15;
-				} elseif ($woloffset < 0) {
-					$woloffset = 0;
-				}
-				if ($sdoffset > 15) {
-					$sdoffset = 15;
-				} elseif ($sdoffset < 0) {
-					$sdoffset = 0;
-				}
-
-				// Set options
-				$options['wol'] = $wol;
-				$options['wol-offset'] = $woloffset;
-				$options['sd'] = $sd;
-				$options['sd-offset'] = $sdoffset;
-
-				Scheduler::updateSchedule($locationid, $options, $openingTimes);
-
-			} else {
-				Scheduler::deleteSchedule($locationid);
-			}
+			// Set options
+			Scheduler::setLocationOptions($locationid, $wol, $sd, $woloffset, $sdoffset);
 		}
 	}
 
@@ -429,25 +408,29 @@ class SubPage
 	private static function ajaxOpeningTimes($id)
 	{
 		User::assertPermission('location.edit.openingtimes', $id);
-		$openTimes = Database::queryFirst("SELECT openingtime FROM `location` WHERE locationid = :id", array('id' => $id));
-		if ($openTimes !== false) {
+		$data = ['id' => $id];
+		$openTimes = Database::queryFirst("SELECT openingtime FROM `location`
+			WHERE locationid = :id", array('id' => $id));
+		if ($openTimes === false) {
+			Message::addError('invalid-location-id', $id);
+			return;
+		}
+		if ($openTimes['openingtime'] !== null) {
 			$openingTimes = json_decode($openTimes['openingtime'], true);
+		} else {
+			$openingTimes = OpeningTimes::forLocation($id);
+			$data['openingtimes_inherited'] = 'checked';
 		}
 		if (!isset($openingTimes) || !is_array($openingTimes)) {
 			$openingTimes = array();
 		}
-		$data = array('id' => $id);
 		$data['expertMode'] = !self::isSimpleMode($openingTimes);
 		$data['schedule_data'] = json_encode($openingTimes);
 
 		$rebootcontrol = Module::isAvailable('rebootcontrol');
 		$data['rebootcontrol'] = $rebootcontrol;
 		if ($rebootcontrol) {
-			$res = Database::queryFirst("SELECT action, nextexecution, options FROM `reboot_scheduler`
-				WHERE locationid = :id", ['id' => $id]);
-			if ($res !== false) {
-				$data['scheduler-options'] = json_decode($res['options'], true);
-			}
+			$data['scheduler-options'] = Scheduler::getLocationOptions($id);
 		}
 
 		echo Render::parse('ajax-opening-location', $data);
diff --git a/modules-available/locations/templates/ajax-opening-location.html b/modules-available/locations/templates/ajax-opening-location.html
index b1aea2e2..5c741857 100644
--- a/modules-available/locations/templates/ajax-opening-location.html
+++ b/modules-available/locations/templates/ajax-opening-location.html
@@ -1,5 +1,10 @@
 <div>
 	<h3>{{lang_openingTime}}</h3>
+	<div class="checkbox">
+		<input id="oi{{id}}" class="openingtimes-inherited"
+				 type="checkbox" name="openingtimes-inherited" value="1" {{openingtimes_inherited}}>
+		<label for="oi{{id}}">{{lang_inheritOpeningTimes}}</label>
+	</div>
 	{{^expertMode}}
 	<div class="simple-mode">
 
@@ -135,7 +140,7 @@
 	<div class="col-sm-8">
 		<div class="input-group">
 			<input disabled type="number" id="wol-offset-{{id}}" name="wol-offset" class="form-control"
-					 value="{{scheduler-options.wol-offset}}" placeholder="0" min="0" max="15">
+					 value="{{scheduler-options.wol-offset}}" placeholder="0" min="0" max="60">
 			<span class="input-group-addon slx-ga2">
 				<label for="wol-offset-{{id}}">{{lang_offsetEarly}}</label>
 			</span>
@@ -152,7 +157,7 @@
 	<div class="col-sm-8">
 		<div class="input-group">
 			<input disabled type="number" id="sd-offset-{{id}}" name="sd-offset" class="form-control"
-					 value="{{scheduler-options.sd-offset}}" placeholder="0" min="0" max="15">
+					 value="{{scheduler-options.sd-offset}}" placeholder="0" min="0" max="60">
 			<span class="input-group-addon slx-ga2">
 					<label for="sd-offset-{{id}}">{{lang_offsetLate}}</label>
 				</span>
@@ -204,6 +209,7 @@
 		$loc.find('.new-openingtime').click(function (e) {
 			e.preventDefault();
 			setTimepicker(newOpeningTime($loc, {}).find('.timepicker2'));
+			setInputEnabled();
 		});
 
 		$loc.find('.btn-show-expert').click(function (e) {
@@ -214,9 +220,16 @@
 			}
 			$loc.find('.simple-mode').remove();
 			$loc.find('.expert-mode').show();
+			setInputEnabled();
 		});
 
 		$loc.find('form').submit(validateOpeningTimes);
+		var setInputEnabled = function () {
+			$loc.find('.expert-mode input, .simple-mode input').prop('disabled', $inheritCb.is(':checked') ? 'disabled' : false);
+		};
+		var $inheritCb = $loc.find('.openingtimes-inherited');
+		setInputEnabled();
+		$inheritCb.change(setInputEnabled);
 	})();
 
 </script>
diff --git a/modules-available/rebootcontrol/hooks/cron.inc.php b/modules-available/rebootcontrol/hooks/cron.inc.php
index c1136c98..8f5c73a0 100644
--- a/modules-available/rebootcontrol/hooks/cron.inc.php
+++ b/modules-available/rebootcontrol/hooks/cron.inc.php
@@ -11,32 +11,7 @@ if (in_array((int)date('G'), [6, 7, 9, 12, 15]) && in_array(date('i'), ['00', '0
 }
 
 // CRON for Scheduler
-$now = time();
-$res = Database::simpleQuery("SELECT s.locationid, s.action, s.nextexecution, s.options, l.openingtime
-	FROM reboot_scheduler s
-	INNER JOIN location l USING (locationid)
-	WHERE s.nextexecution <= :now", ['now' => $now]);
-while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
-	$options = json_decode($row['options'], true);
-
-	// Calculate next_execution for the event.
-	Scheduler::updateSchedule($row['locationid'], $options, $row['openingtime']);
-
-	if ($row['nextexecution'] + 1200 < $now)
-		continue;
-
-	$machines = Database::queryAll("SELECT machineuuid, clientip, macaddr, locationid FROM machine
-		WHERE locationid = :locid", ['locid' => $row['locationid']]);
-	if ($row['action'] === Scheduler::SHUTDOWN) {
-		RebootControl::execute($machines, RebootControl::SHUTDOWN, 0);
-	} elseif ($row['action'] === Scheduler::WOL) {
-		RebootControl::wakeMachines($machines);
-	} elseif ($row['action'] === Scheduler::REBOOT) {
-		RebootControl::execute($machines, RebootControl::REBOOT, 0);
-	} else {
-		EventLog::warning("Invalid action '{$row['action']}' in schedule for location " . $row['locationid']);
-	}
-}
+Scheduler::cron();
 
 /*
  * Client reachability test -- can be disabled
diff --git a/modules-available/rebootcontrol/inc/scheduler.inc.php b/modules-available/rebootcontrol/inc/scheduler.inc.php
index 45aedcc1..292529fa 100644
--- a/modules-available/rebootcontrol/inc/scheduler.inc.php
+++ b/modules-available/rebootcontrol/inc/scheduler.inc.php
@@ -7,38 +7,23 @@ class Scheduler
 	const REBOOT = 'REBOOT';
 	const WOL = 'WOL';
 
-	public static function updateSchedule($locationid, $options, $openingTimes)
-	{
-		if (empty($openingTimes)) {
-			self::deleteSchedule($locationid);
-			return false;
-		}
-		$nextexec = self::calculateNext($options, $openingTimes);
-		if ($nextexec !== false) {
-			$json_options = json_encode($options);
-			Database::exec("INSERT INTO `reboot_scheduler` (locationid, action, nextexecution, options) 
-				VALUES (:lid, :act, :next, :opt)
-				ON DUPLICATE KEY UPDATE
-					action = VALUES(action), nextexecution = VALUES(nextexecution), options = VALUES(options)", [
-				'lid' => $locationid,
-				'act' => $nextexec['action'],
-				'next' => $nextexec['time'],
-				'opt' => $json_options,
-			]);
-		} else {
-			// All times are getting ignored because they are within 5 minutes of each other, delete possible db entries.
-			self::deleteSchedule($locationid);
-		}
-		return true;
-	}
-
-	public static function deleteSchedule($locationid)
+	/**
+	 * @param int $locationid ID of location to delete WOL/shutdown settings for
+	 */
+	public static function deleteSchedule(int $locationid)
 	{
 		Database::exec("DELETE FROM `reboot_scheduler` 
 			WHERE locationid = :lid", ['lid' => $locationid]);
 	}
 
-	private static function calculateTimestamp($now, $day, $time)
+	/**
+	 * Calculate next time the given time description is reached
+	 * @param int $now unix timestamp representing now
+	 * @param string $day Name of weekday
+	 * @param string $time Time, fi. 13:45
+	 * @return false|int unix timestamp in the future when we reach the given time
+	 */
+	private static function calculateTimestamp(int $now, string $day, string $time)
 	{
 		$ts = strtotime("$day $time");
 		if ($ts < $now) {
@@ -51,11 +36,14 @@ class Scheduler
 		return $ts;
 	}
 
-	private static function calculateNext($options, $openingTimes)
+	/**
+	 * Take WOL/SD options and opening times schedule, return next event.
+	 * @return array|false array with keys 'time' and 'action' false if no next event
+	 */
+	private static function calculateNext(array $options, array $openingTimes)
 	{
-		if (!$options['wol'] && !$options['sd'])
+		if ((!$options['wol'] && !$options['sd']) || empty($openingTimes))
 			return false;
-		$openingTimes = json_decode($openingTimes, true);
 		$now = time();
 		$events = [];
 		foreach ($openingTimes as $row) {
@@ -120,4 +108,188 @@ class Scheduler
 		return false;
 	}
 
+	/**
+	 * Check if any actions have to be taken. To be called periodically by cron.
+	 */
+	public static function cron()
+	{
+		$now = time();
+		$res = Database::simpleQuery("SELECT s.locationid, s.action, s.nextexecution, s.options
+			FROM reboot_scheduler s
+			WHERE s.nextexecution < :now AND s.nextexecution > 0", ['now' => $now]);
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			// Calculate next_execution for the event and update DB.
+			$options = json_decode($row['options'], true);
+			// Determine proper opening times by waling up tree
+			$openingTimes = OpeningTimes::forLocation($row['locationid']);
+			if ($openingTimes !== null) {
+				self::updateScheduleSingle($row['locationid'], $options, $openingTimes);
+			}
+			// Weird clock drift? Server offline for a while? Do nothing.
+			if ($row['nextexecution'] + 900 < $now)
+				continue;
+			self::executeCronForLocation($row['locationid'], $row['action']);
+		}
+	}
+
+	/**
+	 * Execute the given action for the given location.
+	 */
+	private static function executeCronForLocation(int $locationId, string $action)
+	{
+		$machines = Database::queryAll("SELECT machineuuid, clientip, macaddr, locationid FROM machine
+				WHERE locationid = :locid", ['locid' => $locationId]);
+		if (empty($machines))
+			return;
+		if ($action === Scheduler::SHUTDOWN) {
+			RebootControl::execute($machines, RebootControl::SHUTDOWN, 0);
+		} elseif ($action === Scheduler::WOL) {
+			RebootControl::wakeMachines($machines);
+		} elseif ($action === Scheduler::REBOOT) {
+			RebootControl::execute($machines, RebootControl::REBOOT, 0);
+		} else {
+			EventLog::warning("Invalid action '$action' in schedule for location " . $locationId);
+		}
+	}
+
+	/**
+	 * Get current settings for given location, or false if none.
+	 * @param int $id
+	 * @return false|array
+	 */
+	public static function getLocationOptions(int $id)
+	{
+		$res = Database::queryFirst("SELECT options FROM `reboot_scheduler`
+				WHERE locationid = :id", ['id' => $id]);
+		if ($res !== false) {
+			return json_decode($res['options'], true);
+		}
+		return false;
+	}
+
+	/**
+	 * Write new WOL/Shutdown options for given location.
+	 * @param int $locationId
+	 * @param bool $wol whether WOL is enabled
+	 * @param bool $sd whether Shutdown is enabled
+	 * @param int $wolOffset how many minutes prior to opening time the WOL should be triggered
+	 * @param int $sdOffset how many minutes after closing time a shutdown should be triggered
+	 */
+	public static function setLocationOptions(int $locationId, bool $wol, bool $sd, int $wolOffset, int $sdOffset)
+	{
+		$openingTimes = OpeningTimes::forLocation($locationId);
+		if (!$wol && !$sd) {
+			self::deleteSchedule($locationId);
+		} else {
+			// Sanity checks
+			if ($wolOffset > 60) {
+				$wolOffset = 60;
+			} elseif ($wolOffset < 0) {
+				$wolOffset = 0;
+			}
+			if ($sdOffset > 60) {
+				$sdOffset = 60;
+			} elseif ($sdOffset < 0) {
+				$sdOffset = 0;
+			}
+			$options = [
+				'wol' => $wol,
+				'sd' => $sd,
+				'wol-offset' => $wolOffset,
+				'sd-offset' => $sdOffset,
+			];
+			$json_options = json_encode($options);
+			// Write settings, reset schedule
+			Database::exec("INSERT INTO `reboot_scheduler` (locationid, action, nextexecution, options) 
+				VALUES (:lid, :act, :next, :opt)
+				ON DUPLICATE KEY UPDATE
+					action = VALUES(action), nextexecution = VALUES(nextexecution), options = VALUES(options)", [
+				'lid' => $locationId,
+				'act' => 'WOL',
+				'next' => 0,
+				'opt' => $json_options,
+			]);
+			// Write new timestamps for this location
+			if ($openingTimes !== null) {
+				self::updateScheduleSingle($locationId, $options, $openingTimes);
+			}
+		}
+		// In either case, refresh data for children as well
+		if ($openingTimes !== null) {
+			self::updateScheduleRecursive($locationId, $openingTimes);
+		}
+	}
+
+	/**
+	 * Write next WOL/shutdown action to DB, using given options and opening times.
+	 * @param int $locationid Location to store settings for
+	 * @param array $options Options for calculation (WOL/Shutdown enabled, offsets)
+	 * @param array $openingTimes Opening times to use
+	 */
+	private static function updateScheduleSingle(int $locationid, array $options, array $openingTimes)
+	{
+		if (!$options['wol'] && !$options['sd']) {
+			self::deleteSchedule($locationid);
+			return;
+		}
+		$nextexec = self::calculateNext($options, $openingTimes);
+		if ($nextexec === false) {
+			// Empty opening times, or all intervals seem to be < 5 minutes, disable.
+			$nextexec = [
+				'action' => 'WOL',
+				'time' => 0,
+			];
+		}
+		Database::exec("UPDATE reboot_scheduler
+			SET action = :act, nextexecution = :next
+			WHERE locationid = :lid", [
+			'lid' => $locationid,
+			'act' => $nextexec['action'],
+			'next' => $nextexec['time'],
+		]);
+	}
+
+	/**
+	 * Recurse into all child locations of the given location-id and re-calculate the next
+	 * WOL or shutdown event, based on the given opening times. Recursion stops at locations
+	 * that come with their own opening times.
+	 * @param int $parentId parent location to start recursion from. Not actually processed.
+	 * @param array $openingTimes Opening times to use for calculations
+	 */
+	private static function updateScheduleRecursive(int $parentId, array $openingTimes)
+	{
+		$list = Location::getLocationsAssoc();
+		if (!isset($list[$parentId]))
+			return;
+		$childIdList = $list[$parentId]['directchildren'];
+		if (empty($childIdList))
+			return;
+		$res = Database::simpleQuery("SELECT l.locationid, l.openingtime, rs.options
+			FROM location l
+			LEFT JOIN reboot_scheduler rs USING (locationid)
+			WHERE l.locationid IN (:list)", ['list' => $childIdList]);
+		$locationData = [];
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			$locationData[$row['locationid']] = $row;
+		}
+		// Handle all child locations
+		foreach ($childIdList as $locationId) {
+			if (!isset($locationData[$locationId]) || $locationData[$locationId]['openingtime'] !== null) {
+				continue; // Ignore entire sub-tree where new opening times are assigned
+			}
+			// This location doesn't have a new openingtimes schedule
+			// If any options are set for this location, update its schedule
+			if ($locationData[$locationId]['options'] !== null) {
+				$options = json_decode($locationData[$locationId]['options'], true);
+				if (!is_array($options)) {
+					trigger_error("Invalid options for lid:$locationId", E_USER_WARNING);
+				} else {
+					self::updateScheduleSingle($locationId, $options, $openingTimes);
+				}
+			}
+			// Either way, further walk down the tree
+			self::updateScheduleRecursive($locationId, $openingTimes);
+		}
+	}
+
 }
\ No newline at end of file