From 8fa66377a1c97d42d83d91ef0821f37f1b885617 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Tue, 6 Dec 2016 16:21:56 +0100
Subject: [statistics/locations/..] Cache calculated locationid of machine in
 machine table

We now have three columns for the locationid:
- subnetlocationid
  Updated whenever the clientip of a client changes on bootup, or when
  locations/subnets are edited in the locations module.
- fixedlocationid
  Takes the role of the old locationid field - set when a machine is
  placed in a room via roomplanner, set to NULL otherwise.
- locationid
  Set to fixedlocationid if it's not NULL, to subnetlocationid otherwise.
  The field is updated by a BEFORE UPDATE trigger.
---
 modules-available/roomplanner/page.inc.php | 7 +++----
 1 file changed, 3 insertions(+), 4 deletions(-)

(limited to 'modules-available/roomplanner/page.inc.php')

diff --git a/modules-available/roomplanner/page.inc.php b/modules-available/roomplanner/page.inc.php
index 65025c5f..c03b69d0 100644
--- a/modules-available/roomplanner/page.inc.php
+++ b/modules-available/roomplanner/page.inc.php
@@ -191,14 +191,14 @@ class Page_Roomplanner extends Page
 				'gridCol' => $computer['gridCol'],
 				'itemlook' => $computer['itemlook']]);
 
-			Database::exec('UPDATE machine SET position = :position, locationid = :locationid WHERE machineuuid = :muuid',
+			Database::exec('UPDATE machine SET position = :position, fixedlocationid = :locationid WHERE machineuuid = :muuid',
 				['locationid' => $this->locationid, 'muuid' => $computer['muuid'], 'position' => $position]);
 		}
 
 		$toDelete = array_diff($oldUuids, $newUuids);
 
 		foreach ($toDelete as $d) {
-			Database::exec("UPDATE machine SET position = '', locationid = NULL WHERE machineuuid = :uuid", ['uuid' => $d]);
+			Database::exec("UPDATE machine SET position = '', fixedlocationid = NULL WHERE machineuuid = :uuid", ['uuid' => $d]);
 		}
 	}
 
@@ -256,8 +256,7 @@ class Page_Roomplanner extends Page
 	protected function getPotentialMachines()
 	{
 		$result = Database::simpleQuery('SELECT machineuuid, macaddr, clientip, hostname '
-			. 'FROM machine INNER JOIN subnet ON (INET_ATON(clientip) BETWEEN startaddr AND endaddr) '
-			. 'WHERE subnet.locationid = :locationid', ['locationid' => $this->locationid]);
+			. 'FROM machine WHERE locationid = :locationid', ['locationid' => $this->locationid]);
 
 		$machines = [];
 
-- 
cgit v1.2.3-55-g7522