1 files changed, 34 insertions, 19 deletions
diff --git a/html/index.php b/html/index.php
index e0158e1..38f4f3d 100644
--- a/html/index.php
+++ b/html/index.php
@@ -1,27 +1,42 @@
 <?php
 
-$db = new SQLite3('/srv/http/vhosts/eckner.net/colocation.eckner.net/backend/sqlite.db');
+include "db.php";
 
-$columns = array('name', 'running', 'ping', 'power', 'last_update');
+$result = $db -> query(
+  'SELECT machines.id as machine, MIN(states.id) as min_state' .
+  ' FROM machines' .
+  ' JOIN "values" ON machines.id = "values".machine_id' .
+  ' JOIN states ON "values".state_id = states.id' .
+  ' WHERE "values".value IS NOT NULL' .
+  ' GROUP BY machines.id'
+);
 
-$result = $db -> query('SELECT machines.' . implode(', machines.', $columns) . ' FROM machines;');
+$result = $db -> query(
+//  'SELECT machines.name as machine, GROUP_CONCAT(states.name || \': \' || "values".value, \',\') as state, last_update' .
+  'SELECT machines.name as machine, GROUP_CONCAT(states.name || \': \' || IFNULL("values".value, \'NULL\'), \',\') as state, machines.last_update' .
+  ' FROM machines' .
+  ' JOIN "values" ON machines.id = "values".machine_id' .
+  ' JOIN states ON "values".state_id = states.id' .
+  ' GROUP BY machines.id'
+);
 
-echo '<html>' . "\n";
-echo '<body>' . "\n";
-echo '<table>' . "\n";
-echo '  <tr>' . "\n";
-foreach ($columns as $column) {
-  echo '    <th>' . $column . '</th>' . "\n";
-}
-echo '  </tr>' . "\n";
+?>
+<html><body><table>
+<tr>
+  <th>machine</th>
+  <th>state</th>
+  <th>last update</th>
+</tr>
+<?php
 
 while ($row = $result -> fetchArray()) {
-  echo '  <tr>' . "\n";
-  foreach ($columns as $column) {
-    echo '    <td>' . $row[$column] . '</td>' . "\n";
-  }
-  echo '  </tr>' . "\n";
+?>
+<tr>
+  <td><?php echo $row['machine']; ?></td>
+  <td><?php echo $row['state']; ?></td>
+  <td><?php echo $row['last_update']; ?></td>
+</tr>
+<?php
 }
-echo '</table>' . "\n";
-echo '</body>' . "\n";
-echo '</html>' . "\n";
+?>
+</table></body></html>