[locations] Add machine count to each location

4 files changed, 227 insertions, 39 deletions

diff --git a/modules-available/locations/inc/location.inc.php b/modules-available/locations/inc/location.inc.php
index 1a01ff24..4bfe9f7a 100644
--- a/modules-available/locations/inc/location.inc.php
+++ b/modules-available/locations/inc/location.inc.php
@@ -5,7 +5,17 @@ class Location
 
 	private static $flatLocationCache = false;
 	private static $assocLocationCache = false;
-	
+	private static $treeCache = false;
+
+	private static function getTree()
+	{
+		if (self::$treeCache === false) {
+			self::$treeCache = self::queryLocations();
+			self::$treeCache = self::buildTree(self::$treeCache);
+		}
+		return self::$treeCache;
+	}
+
 	public static function queryLocations()
 	{
 		$res = Database::simpleQuery("SELECT locationid, parentlocationid, locationname FROM location");
@@ -24,28 +34,31 @@ class Location
 			return false;
 		return self::$assocLocationCache[$locationId]['locationname'];
 	}
-	
+
 	public static function getLocationsAssoc()
 	{
 		if (self::$assocLocationCache === false) {
-			$rows = self::queryLocations();
-			$rows = self::buildTree($rows);
+			$rows = self::getTree();
 			self::$assocLocationCache = self::flattenTreeAssoc($rows);
 		}
 		return self::$assocLocationCache;
 	}
-	
-	private static function flattenTreeAssoc($tree, $depth = 0)
+
+	private static function flattenTreeAssoc($tree, $parents = array(), $depth = 0)
 	{
+		if ($depth > 20) {
+			Util::traceError('Recursive location definition detected at ' . print_r($tree, true));
+		}
 		$output = array();
 		foreach ($tree as $node) {
 			$output[(int)$node['locationid']] = array(
 				'parentlocationid' => (int)$node['parentlocationid'],
+				'parents' => $parents,
 				'locationname' => $node['locationname'],
 				'depth' => $depth
 			);
 			if (!empty($node['children'])) {
-				$output += self::flattenTreeAssoc($node['children'], $depth + 1);
+				$output += self::flattenTreeAssoc($node['children'], array_merge($parents, array((int)$node['locationid'])), $depth + 1);
 			}
 		}
 		return $output;
@@ -54,8 +67,7 @@ class Location
 	public static function getLocations($default = 0, $excludeId = 0, $addNoParent = false)
 	{
 		if (self::$flatLocationCache === false) {
-			$rows = self::queryLocations();
-			$rows = self::buildTree($rows);
+			$rows = self::getTree();
 			$rows = self::flattenTree($rows);
 			self::$flatLocationCache = $rows;
 		} else {
@@ -109,6 +121,9 @@ class Location
 
 	private static function flattenTree($tree, $depth = 0)
 	{
+		if ($depth > 20) {
+			Util::traceError('Recursive location definition detected at ' . print_r($tree, true));
+		}
 		$output = array();
 		foreach ($tree as $node) {
 			$output[] = array(
@@ -137,7 +152,7 @@ class Location
 		}
 		return $ids;
 	}
-	
+
 	public static function getFromIp($ip)
 	{
 		$locationId = false;
@@ -147,11 +162,113 @@ class Location
 		while ($row = $net->fetch(PDO::FETCH_ASSOC)) {
 			$locations = self::getLocationsAssoc();
 			$id = (int)$row['locationid'];
-			if (!isset($locations[$id])) continue;
-			if ($locationId !== false && $locations[$id]['depth'] <= $locations[$locationId]['depth']) continue;
+			if (!isset($locations[$id]))
+				continue;
+			if ($locationId !== false && $locations[$id]['depth'] <= $locations[$locationId]['depth'])
+				continue;
 			$locationId = $id;
 		}
 		return $locationId;
 	}
 
+	/**
+	 * @return array list of subnets as numeric array
+	 */
+	public static function getSubnets()
+	{
+		$res = Database::simpleQuery("SELECT startaddr, endaddr, locationid FROM subnet");
+		$subnets = array();
+		while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+			settype($row['locationid'], 'int');
+			$subnets[] = $row;
+		}
+		return $subnets;
+	}
+
+	/**
+	 * @return array|bool assoc array mapping from locationid to subnets
+	 */
+	public static function getSubnetsByLocation(&$overlapSelf, &$overlapOther)
+	{
+		$locs = self::getLocationsAssoc();
+		$subnets = self::getSubnets();
+		// Find locations having nets overlapping with themselves if array was passed
+		if ($overlapSelf === true || $overlapOther === true) {
+			self::findOverlap($locs, $subnets, $overlapSelf, $overlapOther);
+		}
+		// Accumulate - copy up subnet definitions
+		foreach ($locs as &$loc) {
+			$loc['subnets'] = array();
+		}
+		unset($loc);
+		foreach ($subnets as $subnet) {
+			$lid = $subnet['locationid'];
+			while (isset($locs[$lid])) {
+				$locs[$lid]['subnets'][] = array(
+					'startaddr' => $subnet['startaddr'],
+					'endaddr' => $subnet['endaddr']
+				);
+				$lid = $locs[$lid]['parentlocationid'];
+			}
+		}
+		return $locs;
+	}
+
+	private static function findOverlap($locs, $subnets, &$overlapSelf, &$overlapOther)
+	{
+		if ($overlapSelf) {
+			$self = array();
+		}
+		if ($overlapOther) {
+			$other = array();
+		}
+		$cnt = count($subnets);
+		for ($i = 0; $i < $cnt; ++$i) {
+			for ($j = $i + 1; $j < $cnt; ++$j) {
+				if ($overlapSelf && $subnets[$i]['locationid'] === $subnets[$j]['locationid']
+					&& self::overlap($subnets[$i], $subnets[$j])
+				) {
+					$self[$subnets[$i]['locationid']] = $subnets[$i]['locationid'];
+				}
+				if ($overlapOther && $subnets[$i]['locationid'] !== $subnets[$j]['locationid']
+					&& self::overlap($subnets[$i], $subnets[$j])
+				) {
+					$a = min($subnets[$i]['locationid'], $subnets[$j]['locationid']);
+					$b = max($subnets[$i]['locationid'], $subnets[$j]['locationid']);
+					$other["$a|$b"] = array('lid1' => $subnets[$i]['locationid'], 'lid2' => $subnets[$j]['locationid']);
+				}
+			}
+		}
+		if ($overlapSelf) {
+			$overlapSelf = array();
+			foreach ($self as $entry) {
+				if (!isset($locs[$entry]))
+					continue;
+				$overlapSelf[]['locationname'] = $locs[$entry['locationid']]['locationname'];
+			}
+		}
+		if ($overlapOther) {
+			$overlapOther = array();
+			foreach ($other as $entry) {
+				if (!isset($locs[$entry['lid1']]) || !isset($locs[$entry['lid2']]))
+					continue;
+				if (in_array($entry['lid1'], $locs[$entry['lid2']]['parents']) || in_array($entry['lid2'], $locs[$entry['lid1']]['parents']))
+					continue;
+				if (isset($locs[$entry['lid1']])) {
+					$entry['name1'] = $locs[$entry['lid1']]['locationname'];
+				}
+				if (isset($locs[$entry['lid2']])) {
+					$entry['name2'] = $locs[$entry['lid2']]['locationname'];
+				}
+				$overlapOther[] = $entry;
+			}
+		}
+	}
+
+	private static function overlap($net1, $net2)
+	{
+		return ($net1['startaddr'] >= $net2['startaddr'] && $net1['startaddr'] <= $net2['endaddr'])
+		|| ($net1['endaddr'] >= $net2['startaddr'] && $net1['endaddr'] <= $net2['endaddr']);
+	}
+
 }
diff --git a/modules-available/locations/page.inc.php b/modules-available/locations/page.inc.php
index 8f76701a..9de5e521 100644
--- a/modules-available/locations/page.inc.php
+++ b/modules-available/locations/page.inc.php
@@ -257,11 +257,52 @@ class Page_Locations extends Page
 			}
 			Render::addTemplate('subnets', array('list' => $rows));
 		} elseif ($getAction === 'showlocations') {
-			$locs = Location::getLocations();
-			Render::addTemplate('locations', array('list' => $locs));
+			$this->showLocationList();
 		}
 	}
-	
+
+	private function showLocationList()
+	{
+		$overlapSelf = $overlapOther = true;
+		$subnets = Location::getSubnetsByLocation($overlapSelf, $overlapOther);
+		$locs = Location::getLocations();
+		$unassigned = false;
+		if (Module::get('statistics') !== false) {
+			foreach ($locs as &$location) {
+				$lid = (int)$location['locationid'];
+				if (!isset($subnets[$lid]))
+					continue;
+				$loc =& $subnets[$lid];
+				if (empty($loc['subnets'])) {
+					$query = "SELECT Count(*) AS cnt FROM machine WHERE locationid = :locationid";
+				} else {
+					$query = "SELECT Count(*) AS cnt FROM machine WHERE locationid = :locationid OR (locationid IS NULL AND (0";
+					foreach ($loc['subnets'] as $sub) {
+						$query .= ' OR INET_ATON(clientip) BETWEEN ' . $sub['startaddr'] . ' AND ' . $sub['endaddr'];
+					}
+					$query .= '))';
+				}
+				$ret = Database::queryFirst($query, array('locationid' => $lid));
+				$location['clientCount'] = $ret['cnt'];
+			}
+			$res = Database::queryFirst("SELECT Count(*) AS cnt FROM machine m"
+				. " LEFT JOIN subnet s ON (INET_ATON(m.clientip) BETWEEN s.startaddr AND s.endaddr)"
+				. " WHERE m.locationid IS NULL AND s.locationid IS NULL");
+			$unassigned = $res['cnt'];
+		}
+		unset($loc, $location);
+		Render::addTemplate('locations', array(
+			'list' => $locs,
+			'havestatistics' => Module::get('statistics') !== false,
+			'havebaseconfig' => Module::get('baseconfig') !== false,
+			'overlapSelf' => $overlapSelf,
+			'overlapOther' => $overlapOther,
+			'haveOverlapSelf' => !empty($overlapSelf),
+			'haveOverlapOther' => !empty($overlapOther),
+			'unassignedCount' => $unassigned
+		));
+	}
+
 	/*
 	 * Ajax
 	 */
@@ -300,31 +341,33 @@ class Page_Locations extends Page
 			'list' => $rows,
 			'parents' => Location::getLocations($loc['parentlocationid'], $locationId, true)
 		);
-		// if (moduleEnabled(DOZMOD) {
-		$lectures = Database::queryFirst('SELECT Count(*) AS cnt FROM sat.lecture l '
-			. ' INNER JOIN sat.lecture_x_location ll ON (l.lectureid = ll.lectureid AND ll.locationid = :lid)',
+		if (Module::get('dozmod') !== false) {
+			$lectures = Database::queryFirst('SELECT Count(*) AS cnt FROM sat.lecture l '
+				. ' INNER JOIN sat.lecture_x_location ll ON (l.lectureid = ll.lectureid AND ll.locationid = :lid)',
 				array('lid' => $locationId));
-		$data['lectures'] = $lectures['cnt'];
-		// }
+			$data['lectures'] = $lectures['cnt'];
+		}
 		// Get clients matching this location's subnet(s)
-		$mres = Database::simpleQuery("SELECT lastseen, logintime FROM machine"
-			. " INNER JOIN subnet ON (INET_ATON(machine.clientip) BETWEEN startaddr AND endaddr)"
-			. " WHERE subnet.locationid = :lid OR machine.locationid = :lid", array('lid' => $locationId));
 		$count = $online = $used = 0;
-		$DL = time() - 605;
-		while ($row = $mres->fetch(PDO::FETCH_ASSOC)) {
-			$count++;
-			if ($row['lastseen'] > $DL) {
-				$online++;
-				if ($row['logintime'] != 0) {
-					$used++;
+		if (Module::get('statistics') !== false) {
+			$mres = Database::simpleQuery("SELECT lastseen, logintime FROM machine"
+				. " INNER JOIN subnet ON (INET_ATON(machine.clientip) BETWEEN startaddr AND endaddr)"
+				. " WHERE subnet.locationid = :lid OR machine.locationid = :lid", array('lid' => $locationId));
+			$DL = time() - 605;
+			while ($row = $mres->fetch(PDO::FETCH_ASSOC)) {
+				$count++;
+				if ($row['lastseen'] > $DL) {
+					$online++;
+					if ($row['logintime'] != 0) {
+						$used++;
+					}
 				}
 			}
 		}
 		$data['machines'] = $count;
 		$data['machines_online'] = $online;
 		$data['machines_used'] = $used;
-		$data['used_percent'] = round(100 * $used / $online);
+		$data['used_percent'] = $online === 0 ? 0 : round(100 * $used / $online);
 		echo Render::parse('location-subnets', $data);
 	}
 	
diff --git a/modules-available/locations/templates/location-subnets.html b/modules-available/locations/templates/location-subnets.html
index 76b7442a..b0353416 100644
--- a/modules-available/locations/templates/location-subnets.html
+++ b/modules-available/locations/templates/location-subnets.html
@@ -1,4 +1,4 @@
-<div class="slx-well">
+<td class="slx-well">
 	<div class="slx-bold">{{lang_locationSettings}}</div>
 	<form method="post" action="?do=Locations">
 		<input type="hidden" name="token" value="{{token}}">
@@ -58,7 +58,7 @@
 				</td>
 				<td colspan="2" align="right">
 					<button type="submit" class="btn btn-primary">{{lang_save}}</button>
-				</div>
+				</td>
 			</tr>
 		</table>
 	</form>
diff --git a/modules-available/locations/templates/locations.html b/modules-available/locations/templates/locations.html
index 76c8f97c..e8b5d707 100644
--- a/modules-available/locations/templates/locations.html
+++ b/modules-available/locations/templates/locations.html
@@ -5,19 +5,37 @@
 	<h1>{{lang_locationsMainHeading}}</h1>
 	<table class="table table-condensed" style="margin-bottom:0px">
 		<tr>
-			<th>#</th>
 			<th width="100%">{{lang_locationName}}</th>
 			<th></th>
 		</tr>
 		{{#list}}
 			<tr>
-				<td>{{locationid}}</td>
-				<td><div style="display:inline-block;width:{{depth}}em"></div>{{locationname}}</td>
-				<td align="right">
-					<a class="btn btn-success btn-xs" onclick="slxOpenLocation(this, {{locationid}})"><span class="glyphicon glyphicon-edit"></span> {{lang_edit}}</a>
+				<td>
+					<div style="display:inline-block;width:{{depth}}em"></div>
+					<a href="#" onclick="slxOpenLocation(this, {{locationid}})">{{locationname}}<b class="caret"></b></a>
+				</td>
+				<td class="slx-nowrap" align="right">
+					{{#havestatistics}}
+					{{clientCount}}
+					<a class="btn btn-default btn-xs" href="?do=Statistics&amp;filter=location&amp;argument={{locationid}}"><span class="glyphicon glyphicon-eye-open"></span></a>
+					{{/havestatistics}}
+					{{#havebaseconfig}}
+					<a class="btn btn-success btn-xs" href="?do=baseconfig&amp;module=locations&amp;locationid={{locationid}}"><span class="glyphicon glyphicon-edit"></span> {{lang_editConfigVariables}}</a>
+					{{/havebaseconfig}}
 				</td>
 			</tr>
 		{{/list}}
+		{{#unassignedCount}}
+		<tr>
+			<td></td>
+			<td class="slx-nowrap" align="right">
+				{{lang_unassignedMachines}}: {{unassignedCount}}
+				<a class="btn btn-default btn-xs" href="?do=Statistics&amp;filter=location&amp;argument=0">
+					<span class="glyphicon glyphicon-eye-open"></span>
+				</a>
+			</td>
+		</tr>
+		{{/unassignedCount}}
 	</table>
 	<form method="post" action="?do=Locations">
 		<input type="hidden" name="token" value="{{token}}">
@@ -37,6 +55,16 @@
 		</table>
 	</form>
 </div>
+{{#overlapSelf}}
+<div class="alert alert-warning">
+	{{lang_locationSelfOverlap}}: <b>{{locationname}}</b>
+</div>
+{{/overlapSelf}}
+{{#overlapOther}}
+<div class="alert alert-danger">
+	{{lang_locationOtherOverlap}}: <b>{{name1}}</b> – <b>{{name2}}</b>
+</div>
+{{/overlapOther}}
 <script type="text/javascript"><!--
 var slxAddCounter = 0;
 var slxLastLocation = false;
@@ -70,7 +98,7 @@ function slxOpenLocation(e, lid) {
 		}
 		return;
 	}
-	var td = $('<td>').attr('colspan', '12').css('padding', '0px 0px 12px');
+	var td = $('<td>').attr('colspan', '2').css('padding', '0px 0px 12px');
 	var tr = $('<tr>').attr('id', 'location-details-' + lid);
 	tr.append(td);
 	$(e).closest('tr').addClass('active slx-bold').after(tr);