Adding device stats

9 files changed, 98 insertions, 24 deletions

diff --git a/servo/templates/stats/devices.html b/servo/templates/stats/devices.html
new file mode 100644
index 0000000..079530a
--- /dev/null
+++ b/servo/templates/stats/devices.html
@@ -0,0 +1,40 @@
+{% extends "stats/index.html" %}
+{% load i18n %}
+
+{% block tabs %}
+    <li><a href="{% url 'stats-index' %}">{% trans "Technicians" %}</a></li>
+    <li><a href="{% url 'stats-locations' %}">{% trans "Locations" %}</a></li>
+    <li><a href="{% url 'stats-queues' %}">{% trans "Queues" %}</a></li>
+    <li><a href="{% url 'stats-repairs' %}">{% trans "Repairs" %}</a></li>
+    <li><a href="{% url 'stats-statuses' %}">{% trans "Statuses" %}</a></li>
+    <li><a href="{% url 'stats-sales' %}">{% trans "Sales" %}</a></li>
+    <li  class="active"><a href="{% url 'stats-devices' %}">{% trans "Devices" %}</a></li>
+{% endblock tabs %}
+
+{% block stats %}
+    <h2>{% trans "Device statistics" %}</h2>
+    <p>{% trans "Shows the number of orders and GSX repairs created for each device model within the given timeframe." %}</p>
+    <hr/>
+    <table class="table sortable">
+        <thead>
+            <tr>
+                <th>{% trans "Device" %}</th>
+                <th>{% trans "Service Orders" %}</th>
+                <th>{% trans "GSX Repairs" %}</th>
+            </tr>
+        </thead>
+        <tbody>
+        {% for r in results %}
+            <tr>
+                <td>{{ r.0 }}</td>
+                <td>{{ r.1 }}</td>
+                <td>{{ r.2 }}</td>
+            </tr>
+        {% empty %}
+            <tr>
+                <td colspan="3" class="empty muted">{% trans "No results found" %}</td>
+            </tr>
+        {% endfor %}
+        </tbody>
+    </table>
+{% endblock stats %}
diff --git a/servo/templates/stats/index.html b/servo/templates/stats/index.html
index 2edc57d..90c0ed9 100755
--- a/servo/templates/stats/index.html
+++ b/servo/templates/stats/index.html
@@ -11,6 +11,7 @@
   <li><a href="{% url 'stats-repairs' %}">{% trans "Repairs" %}</a></li>
   <li><a href="{% url 'stats-statuses' %}">{% trans "Statuses" %}</a></li>
   <li><a href="{% url 'stats-sales' %}">{% trans "Sales" %}</a></li>
+  <li><a href="{% url 'stats-devices' %}">{% trans "Devices" %}</a></li>
 {% endblock tabs %}
 </ul>
 
diff --git a/servo/templates/stats/locations.html b/servo/templates/stats/locations.html
index 576da8b..da4545e 100755
--- a/servo/templates/stats/locations.html
+++ b/servo/templates/stats/locations.html
@@ -8,6 +8,7 @@
     <li><a href="{% url 'stats-repairs' %}">{% trans "Repairs" %}</a></li>
     <li><a href="{% url 'stats-statuses' %}">{% trans "Statuses" %}</a></li>
     <li><a href="{% url 'stats-sales' %}">{% trans "Sales" %}</a></li>
+    <li><a href="{% url 'stats-devices' %}">{% trans "Devices" %}</a></li>
 {% endblock tabs %}
 
 {% block stats %}
diff --git a/servo/templates/stats/newstats.html b/servo/templates/stats/newstats.html
index 1900efc..325ab10 100644
--- a/servo/templates/stats/newstats.html
+++ b/servo/templates/stats/newstats.html
@@ -11,6 +11,7 @@
   <li class="active"><a href="{% url 'stats-repairs' %}">{% trans "Repairs" %}</a></li>
   <li><a href="{% url 'stats-statuses' %}">{% trans "Statuses" %}</a></li>
   <li><a href="{% url 'stats-sales' %}">{% trans "Sales" %}</a></li>
+  <li><a href="{% url 'stats-devices' %}">{% trans "Devices" %}</a></li>
 {% endblock tabs %}
 </ul>
 
diff --git a/servo/templates/stats/queues.html b/servo/templates/stats/queues.html
index eefec46..f4f493d 100755
--- a/servo/templates/stats/queues.html
+++ b/servo/templates/stats/queues.html
@@ -8,6 +8,7 @@
     <li><a href="{% url 'stats-repairs' %}">{% trans "Repairs" %}</a></li>
     <li><a href="{% url 'stats-statuses' %}">{% trans "Statuses" %}</a></li>
     <li><a href="{% url 'stats-sales' %}">{% trans "Sales" %}</a></li>
+    <li><a href="{% url 'stats-devices' %}">{% trans "Devices" %}</a></li>
 {% endblock tabs %}
 
 {% block stats %}
diff --git a/servo/templates/stats/sales.html b/servo/templates/stats/sales.html
index 186e897..e58b7b4 100755
--- a/servo/templates/stats/sales.html
+++ b/servo/templates/stats/sales.html
@@ -2,32 +2,33 @@
 {% load i18n %}
 
 {% block tabs %}
-<li><a href="{% url 'stats-index' %}">{% trans "Technicians" %}</a></li>
-<li><a href="{% url 'stats-locations' %}">{% trans "Locations" %}</a></li>
-<li><a href="{% url 'stats-queues' %}">{% trans "Queues" %}</a></li>
-<li><a href="{% url 'stats-repairs' %}">{% trans "Repairs" %}</a></li>
-<li><a href="{% url 'stats-statuses' %}">{% trans "Statuses" %}</a></li>
-<li class="active"><a href="{% url 'stats-sales' %}">{% trans "Sales" %}</a></li>
+    <li><a href="{% url 'stats-index' %}">{% trans "Technicians" %}</a></li>
+    <li><a href="{% url 'stats-locations' %}">{% trans "Locations" %}</a></li>
+    <li><a href="{% url 'stats-queues' %}">{% trans "Queues" %}</a></li>
+    <li><a href="{% url 'stats-repairs' %}">{% trans "Repairs" %}</a></li>
+    <li><a href="{% url 'stats-statuses' %}">{% trans "Statuses" %}</a></li>
+    <li class="active"><a href="{% url 'stats-sales' %}">{% trans "Sales" %}</a></li>
+    <li><a href="{% url 'stats-devices' %}">{% trans "Devices" %}</a></li>
 {% endblock tabs %}
 
 {% block stats %}
-<h2>{% trans "Sales" %}</h2>
-<p>{% trans "Shows you invoice totals per queue within the selected time period." %}</p>
-{% include "stats/plot_snippet.html" with url="/stats/data/sales/invoices/" %}
-<hr/>
-<h2>{% trans "Purchases" %}</h2>
-<p>{% trans "Shows you Purchase Order totals per queue within the selected time period." %}</p>
-{% include "stats/plot_snippet.html" with url="/stats/data/sales/purchases/" %}
-<hr/>
-<h2>{% trans "Service Parts" %}</h2>
-<p>{% trans "Shows you how many parts have been ordered for each labour tier." %}</p>
-<div class="row-fluid">
-  <div class="span12">
-    <div class="span10">
-      <div class="plot plot-bar" data-source="/stats/data/sales/parts/"></div>
-      <div class="legend-container"></div>
+    <h2>{% trans "Sales" %}</h2>
+    <p>{% trans "Shows you invoice totals per queue within the selected time period." %}</p>
+    {% include "stats/plot_snippet.html" with url="/stats/data/sales/invoices/" %}
+    <hr/>
+    <h2>{% trans "Purchases" %}</h2>
+    <p>{% trans "Shows you Purchase Order totals per queue within the selected time period." %}</p>
+    {% include "stats/plot_snippet.html" with url="/stats/data/sales/purchases/" %}
+    <hr/>
+    <h2>{% trans "Service Parts" %}</h2>
+    <p>{% trans "Shows you how many parts have been ordered for each labour tier." %}</p>
+    <div class="row-fluid">
+      <div class="span12">
+        <div class="span10">
+          <div class="plot plot-bar" data-source="/stats/data/sales/parts/"></div>
+          <div class="legend-container"></div>
+        </div>
+        <div class="span2"></div>
+      </div>
     </div>
-    <div class="span2"></div>
-  </div>
-</div>
 {% endblock stats %}
diff --git a/servo/templates/stats/statuses.html b/servo/templates/stats/statuses.html
index f4fb427..bfcf73e 100755
--- a/servo/templates/stats/statuses.html
+++ b/servo/templates/stats/statuses.html
@@ -8,6 +8,7 @@
     <li><a href="{% url 'stats-repairs' %}">{% trans "Repairs" %}</a></li>
     <li class="active"><a href="{% url 'stats-statuses' %}">{% trans "Statuses" %}</a></li>
     <li><a href="{% url 'stats-sales' %}">{% trans "Sales" %}</a></li>
+    <li><a href="{% url 'stats-devices' %}">{% trans "Devices" %}</a></li>
 {% endblock tabs %}
 
 {% block stats %}
diff --git a/servo/urls/stats.py b/servo/urls/stats.py
index d475e54..58c5ee3 100644
--- a/servo/urls/stats.py
+++ b/servo/urls/stats.py
@@ -13,4 +13,5 @@ urlpatterns = [
     url(r'^statuses/$', statuses, name="stats-statuses"),
     url(r'^data/(?P<query>[\w/\-]+)/$', data, name="stats-data"),
     url(r'^repairs/$', repairs, name="stats-repairs"),
+    url(r'^devices/$', devices, name="stats-devices"),
 ]
diff --git a/servo/views/stats.py b/servo/views/stats.py
index 0acf9f7..6a9f9a8 100644
--- a/servo/views/stats.py
+++ b/servo/views/stats.py
@@ -421,3 +421,30 @@ def repairs(request):
             totals['turnaround'] = ServoTimeDelta(totals['turnaround']/totals['dispatched'])
 
     return render(request, "stats/newstats.html", locals())
+
+
+def devices(request):
+    data = prep_view(request)
+    data['form'] = DeviceStatsForm()
+    start_date = data['initial']['start_date']
+    end_date = data['initial']['end_date']
+
+    if request.method == 'POST':
+        form = DeviceStatsForm(request.POST)
+        if form.is_valid():
+            start_date = form.cleaned_data['start_date']
+            end_date = form.cleaned_data['end_date']
+
+    cursor  = connection.cursor()
+    query = '''SELECT d.description device, count(o) AS orders, count(r) AS repairs 
+        FROM servo_device d, servo_order o, servo_repair r, servo_orderdevice od 
+        WHERE d.id = od.device_id 
+            AND o.id = od.order_id 
+            AND r.order_id = o.id
+            AND (o.created_at, o.created_at) OVERLAPS (%s, %s)
+        GROUP BY d.description''';
+    cursor.execute(query, [start_date, end_date])
+    data['results'] = cursor.fetchall()
+    data['title'] = _('Device statistics')
+
+    return render(request, "stats/devices.html", data)