[statistics_reporting] Query rewrite: Use iterative approach

Several simple test cases yielded incorrect results, also
sessions partially overlapping with the selected hours
were not handled properly. Iterative processing of logged events
instead of single humongous unmaintainable SQL query.

3 files changed, 86 insertions, 44 deletions

diff --git a/modules-available/statistics_reporting/templates/table-client.html b/modules-available/statistics_reporting/templates/table-client.html
index 59153e01..a23c2f89 100644
--- a/modules-available/statistics_reporting/templates/table-client.html
+++ b/modules-available/statistics_reporting/templates/table-client.html
@@ -1,29 +1,43 @@
-<table id="table-perclient" class="table table-condensed table-striped stupidtable">
+<table id="table-perclient" class="table table-condensed table-striped table-nowrap stupidtable">
 	<thead>
 	<tr>
-		<th data-sort="string" class="text-left col-md-4">{{lang_hostname}}</th>
-		<th data-sort="string" class="text-left col_location">{{lang_location}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_totalTime">{{lang_totalTime}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_medianSessionLength">{{lang_medianSessionLength}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_longSessions">{{lang_longSessions}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_shortSessions">{{lang_shortSessions}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_totalOffTime">{{lang_totalOffTime}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_lastLogout">{{lang_lastLogout}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_lastStart">{{lang_lastStart}}</th>
+		<th data-sort="string">{{lang_hostname}}</th>
+		<th data-sort="string" class="col_locationname">{{lang_location}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalTime">{{lang_totalTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_medianSessionLength">{{lang_medianSessionLength}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_longSessions">{{lang_longSessions}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_shortSessions">{{lang_shortSessions}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalOffTime">{{lang_totalOffTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalOffTime">(%)</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalStandbyTime">{{lang_totalStandbyTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalStandbyTime">(%)</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalSessionTime">{{lang_totalSessionTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalSessionTime">(%)</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalIdleTime">{{lang_totalIdleTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalIdleTime">(%)</th>
+		<th data-sort="int" data-sort-default="desc" class="col_lastLogout">{{lang_lastLogout}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_lastStart">{{lang_lastStart}}</th>
 	</tr>
 	</thead>
 	<tbody>
 	{{#data}}
 	<tr>
 		<td class="text-left">{{hostname}}</td>
-		<td class="text-left col_location"><a class="locationLink" href="#" data-lid="{{locationId}}">{{location}}</a></td>
-		<td data-sort-value="{{totalTime}}" class="text-left col_totalTime">{{totalTime_s}}</td>
-		<td data-sort-value="{{medianSessionLength}}" class="text-left col_medianSessionLength">{{medianSessionLength_s}}</td>
-		<td class="text-left col_longSessions">{{longSessions}}</td>
-		<td class="text-left col_shortSessions">{{shortSessions}}</td>
-		<td data-sort-value="{{totalOffTime}}" class="text-left col_totalOffTime">{{totalOffTime_s}}</td>
-		<td data-sort-value="{{lastLogout}}" class="text-left col_lastLogout">{{lastLogout_s}}</td>
-		<td data-sort-value="{{lastStart}}" class="text-left col_lastStart">{{lastStart_s}}</td>
+		<td class="col_locationname"><a class="locationLink" href="#" data-lid="{{locationid}}">{{locationname}}</a></td>
+		<td data-sort-value="{{totalTime}}" class="col_totalTime">{{totalTime_s}}</td>
+		<td data-sort-value="{{medianSessionLength}}" class="col_medianSessionLength">{{medianSessionLength_s}}</td>
+		<td class="col_longSessions">{{longSessions}}</td>
+		<td class="col_shortSessions">{{shortSessions}}</td>
+		<td data-sort-value="{{totalOffTime}}" class="text-right col_totalOffTime">{{totalOffTime_s}}</td>
+		<td class="col_totalOffTime p-addon">{{totalOffTime_p}}&thinsp;%</td>
+		<td data-sort-value="{{totalStandbyTime}}" class="text-right col_totalStandbyTime">{{totalStandbyTime_s}}</td>
+		<td class="col_totalStandbyTime p-addon">{{totalStandbyTime_p}}&thinsp;%</td>
+		<td data-sort-value="{{totalSessionTime}}" class="text-right col_totalSessionTime">{{totalSessionTime_s}}</td>
+		<td class="col_totalSessionTime p-addon">{{totalSessionTime_p}}&thinsp;%</td>
+		<td data-sort-value="{{totalIdleTime}}" class="text-right col_totalIdleTime">{{totalIdleTime_s}}</td>
+		<td class="col_totalIdleTime p-addon">{{totalIdleTime_p}}&thinsp;%</td>
+		<td data-sort-value="{{lastLogout}}" class="col_lastLogout">{{lastLogout_s}}</td>
+		<td data-sort-value="{{lastStart}}" class="col_lastStart">{{lastStart_s}}</td>
 	</tr>
 	{{/data}}
 	</tbody>
diff --git a/modules-available/statistics_reporting/templates/table-location.html b/modules-available/statistics_reporting/templates/table-location.html
index a0867208..4e470d1e 100644
--- a/modules-available/statistics_reporting/templates/table-location.html
+++ b/modules-available/statistics_reporting/templates/table-location.html
@@ -1,23 +1,37 @@
-<table id="table-perlocation" class="table table-condensed table-striped stupidtable">
+<table id="table-perlocation" class="table table-condensed table-striped table-nowrap stupidtable">
 	<thead>
 	<tr>
-		<th data-sort="string" class="text-left col-md-2">{{lang_location}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_totalTime">{{lang_totalTime}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_medianSessionLength">{{lang_medianSessionLength}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_longSessions">{{lang_longSessions}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_shortSessions">{{lang_shortSessions}}</th>
-		<th data-sort="int" data-sort-default="desc" class="text-left col_totalOffTime">{{lang_totalOffTime}}</th>
+		<th data-sort="string">{{lang_location}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalTime">{{lang_totalTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_medianSessionLength">{{lang_medianSessionLength}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_longSessions">{{lang_longSessions}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_shortSessions">{{lang_shortSessions}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalOffTime">{{lang_totalOffTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalOffTime">(%)</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalStandbyTime">{{lang_totalStandbyTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalStandbyTime">(%)</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalSessionTime">{{lang_totalSessionTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalSessionTime">(%)</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalIdleTime">{{lang_totalIdleTime}}</th>
+		<th data-sort="int" data-sort-default="desc" class="col_totalIdleTime">(%)</th>
 	</tr>
 	</thead>
 	<tbody>
 	{{#data}}
 	<tr{{#highlight}} class="info"{{/highlight}}>
-		<td class="locationName text-left">{{location}}</td>
-		<td data-sort-value="{{totalTime}}" class="text-left col_totalTime">{{totalTime_s}}</td>
-		<td data-sort-value="{{medianSessionLength}}" class="text-left col_medianSessionLength">{{medianSessionLength_s}}</td>
-		<td class="text-left col_longSessions">{{longSessions}}</td>
-		<td class="text-left col_shortSessions">{{shortSessions}}</td>
-		<td data-sort-value="{{totalOffTime}}" class="text-left col_totalOffTime">{{totalOffTime_s}}</td>
+		<td data-sort-value="{{parentlocations}}" class="locationName text-left">{{locationname}}</td>
+		<td data-sort-value="{{totalTime}}" class="text-right col_totalTime">{{totalTime_s}}</td>
+		<td data-sort-value="{{medianSessionLength}}" class="col_medianSessionLength">{{medianSessionLength_s}}</td>
+		<td class="text-right col_longSessions">{{longSessions}}</td>
+		<td class="text-right col_shortSessions">{{shortSessions}}</td>
+		<td data-sort-value="{{totalOffTime}}" class="text-right col_totalOffTime">{{totalOffTime_s}}</td>
+		<td class="col_totalOffTime p-addon">{{totalOffTime_p}}&thinsp;%</td>
+		<td data-sort-value="{{totalStandbyTime}}" class="text-right col_totalStandbyTime">{{totalStandbyTime_s}}</td>
+		<td class="col_totalStandbyTime p-addon">{{totalStandbyTime_p}}&thinsp;%</td>
+		<td data-sort-value="{{totalSessionTime}}" class="text-right col_totalSessionTime">{{totalSessionTime_s}}</td>
+		<td class="col_totalSessionTime p-addon">{{totalSessionTime_p}}&thinsp;%</td>
+		<td data-sort-value="{{totalIdleTime}}" class="text-right col_totalIdleTime">{{totalIdleTime_s}}</td>
+		<td class="col_totalIdleTime p-addon">{{totalIdleTime_p}}&thinsp;%</td>
 	</tr>
 	{{/data}}
 	</tbody>
diff --git a/modules-available/statistics_reporting/templates/table-total.html b/modules-available/statistics_reporting/templates/table-total.html
index 8d5d7571..83078714 100644
--- a/modules-available/statistics_reporting/templates/table-total.html
+++ b/modules-available/statistics_reporting/templates/table-total.html
@@ -1,22 +1,36 @@
-<table id="table-total" class="table table-condensed table-striped stupidtable">
+<table id="table-total" class="table table-condensed table-striped table-nowrap stupidtable">
 	<thead>
 	<tr>
-		<th class="text-left col-md-2"></th>
-		<th class="text-left col_totalTime">{{lang_totalTime}}</th>
-		<th class="text-left col_medianSessionLength">{{lang_medianSessionLength}}</th>
-		<th class="text-left col_longSessions">{{lang_longSessions}}</th>
-		<th class="text-left col_shortSessions">{{lang_shortSessions}}</th>
-		<th class="text-left col_totalOffTime">{{lang_totalOffTime}}</th>
+		<th></th>
+		<th class="col_totalTime">{{lang_totalTime}}</th>
+		<th class="col_medianSessionLength">{{lang_medianSessionLength}}</th>
+		<th class="col_longSessions">{{lang_longSessions}}</th>
+		<th class="col_shortSessions">{{lang_shortSessions}}</th>
+		<th class="col_totalOffTime">{{lang_totalOffTime}}</th>
+		<th class="col_totalOffTime">(%)</th>
+		<th class="col_totalStandbyTime">{{lang_totalStandbyTime}}</th>
+		<th class="col_totalStandbyTime">(%)</th>
+		<th class="col_totalSessionTime">{{lang_totalSessionTime}}</th>
+		<th class="col_totalSessionTime">(%)</th>
+		<th class="col_totalIdleTime">{{lang_totalIdleTime}}</th>
+		<th class="col_totalIdleTime">(%)</th>
 	</tr>
 	</thead>
 	<tbody>
 	<tr>
-		<th class="text-left">{{lang_total}}</th>
-		<td class="text-left col_totalTime">{{data.totalTime_s}}</td>
-		<td class="text-left col_medianSessionLength">{{data.medianSessionLength_s}}</td>
-		<td class="text-left col_longSessions">{{data.longSessions}}</td>
-		<td class="text-left col_shortSessions">{{data.shortSessions}}</td>
-		<td class="text-left col_totalOffTime">{{data.totalOffTime_s}}</td>
+		<td>{{lang_total}}</td>
+		<td class="col_totalTime">{{data.totalTime_s}}</td>
+		<td class="col_medianSessionLength">{{data.medianSessionLength_s}}</td>
+		<td class="col_longSessions">{{data.longSessions}}</td>
+		<td class="col_shortSessions">{{data.shortSessions}}</td>
+		<td class="text-right col_totalOffTime">{{data.totalOffTime_s}}</td>
+		<td class="col_totalOffTime p-addon">{{data.totalOffTime_p}}&thinsp;%</td>
+		<td class="text-right col_totalStandbyTime">{{data.totalStandbyTime_s}}</td>
+		<td class="col_totalStandbyTime p-addon">{{data.totalStandbyTime_p}}&thinsp;%</td>
+		<td class="text-right col_totalSessionTime">{{data.totalSessionTime_s}}</td>
+		<td class="col_totalSessionTime p-addon">{{data.totalSessionTime_p}}&thinsp;%</td>
+		<td class="text-right col_totalIdleTime">{{data.totalIdleTime_s}}</td>
+		<td class="col_totalIdleTime p-addon">{{data.totalIdleTime_p}}&thinsp;%</td>
 	</tr>
 	</tbody>
 </table>