Summary of recent support requests with response times

diff --git a/header.mlt.in b/header.mlt.in
index b6cd42f..238d8d4 100644 (file)
--- a/header.mlt.in
+++ b/header.mlt.in
@@ -54,6 +54,7 @@ Miscellaneous<br />
 <li> <a href="apps">Review pending membership applications</a></li>
 <li> <a href="dir">Contact information directory</a></li>
 <li> <a href="poll">Polls</a></li>
+<li> <a href="qos">Support quality statistics</a></li>
 </div>
 
 <div class="sbgroup">

diff --git a/qos.mlt b/qos.mlt
new file mode 100644 (file)
index 0000000..cc0cfaa
--- /dev/null
+++ b/qos.mlt
@@ -0,0 +1,29 @@
+<% @header [("title", ["Support quality statistics"])];
+
+val days = case $"days" of
+                 "" => 7
+               | days => Web.stoi days %>
+
+<form method="post">
+Show me the entries from the last <input name="days" size="7" value="<% days %>"> days.
+<input type="submit" value="Show">
+</form>
+
+<table>
+<tr><td><b>Kind</b></td> <td><b>Description</b></td> <td><b>User</b></td> <td><b>Placed</b></td> <td><b>Response</b></td> <td><b>Closed</b></td></tr>
+<%
+
+foreach qos in Qos.recent days do %>
+       <tr> <td><% switch #kindUrl qos of SOME url => %><a href="<% url %>"><% end %><% Web.html (#kind qos) %><% switch #kindUrl qos of SOME _ => %></a><% end %></td>
+       <td><% switch #url qos of SOME url => %><a href="<% url %>"><% end %><% Web.html (#name qos) %><% switch #url qos of SOME _ => %></a><% end %></td>
+       <td><a href="user?id=<% #usr qos %>"><% Web.html (#uname qos) %></a></td>
+       <td><% #stamp qos %></td>
+       <td><% switch #pstamp qos of NONE => %>-<% | SOME stamp => stamp end %></td>
+       <td><% switch #cstamp qos of NONE => %>-<% | SOME stamp => stamp end %></td> </tr>
+<% end %>
+
+</table>
+
+<% @footer [] %>
+
+

diff --git a/qos.sig b/qos.sig
new file mode 100644 (file)
index 0000000..d617aec
--- /dev/null
+++ b/qos.sig
@@ -0,0 +1,8 @@
+signature QOS = sig
+
+    type entry = { kind : string, kindUrl : string option, name : string, url : string option, usr : int, uname : string,
+                  stamp : Init.C.timestamp, pstamp : Init.C.timestamp option, cstamp : Init.C.timestamp option}
+
+    val recent : int -> entry list
+
+end

diff --git a/qos.sml b/qos.sml
new file mode 100644 (file)
index 0000000..4d06332
--- /dev/null
+++ b/qos.sml
@@ -0,0 +1,45 @@
+structure Qos :> QOS = struct
+
+open Util Sql Init
+
+type entry = { kind : string, kindUrl : string option, name : string, url : string option, usr : int, uname : string,
+              stamp : C.timestamp, pstamp : C.timestamp option, cstamp : C.timestamp option}
+
+fun mkEntryRow [kind, kindUrl, name, url, usr, uname, stamp, pstamp, cstamp] =
+    {kind = C.stringFromSql kind, kindUrl = if C.isNull kindUrl then NONE else SOME (C.stringFromSql kindUrl),
+     name = C.stringFromSql name, url = if C.isNull url then NONE else SOME (C.stringFromSql url),
+     usr = C.intFromSql usr, uname = C.stringFromSql uname, stamp = C.timestampFromSql stamp,
+     pstamp = if C.isNull pstamp then NONE else SOME (C.timestampFromSql pstamp),
+     cstamp = if C.isNull cstamp then NONE else SOME (C.timestampFromSql cstamp)}
+  | mkEntryRow row = rowError ("QOS", row)
+
+fun recent days =
+    let
+       val usr = Init.getUserId ()
+       val db = getDb ()
+    in
+       C.map db mkEntryRow ($`SELECT SupCategory.name, 'issue?cat=' || SupCategory.id, title,
+                                 'issue?cat=' || SupCategory.id || '&id=' || SupIssue.id, usr, WebUser.name,
+                                stamp, COALESCE(pstamp, cstamp), cstamp
+                                FROM SupIssue JOIN SupCategory ON SupCategory.id = cat
+                                   JOIN WebUser ON WebUser.id = usr
+                                WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
+                                  AND (NOT priv OR usr = ^(C.intToSql usr)
+                                    OR (SELECT COUNT(*) FROM Membership WHERE Membership.usr = ^(C.intToSql usr)
+                                       AND (Membership.grp = 0 OR Membership.grp = SupCategory.grp)) > 0)
+                              UNION SELECT 'APT package', NULL, data, NULL, usr, name, stamp, cstamp, cstamp
+                                FROM Apt JOIN WebUser ON WebUser.id = usr
+                                WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
+                              UNION SELECT 'Domain', NULL, data, NULL, usr, name, stamp, cstamp, cstamp
+                                FROM Domain JOIN WebUser ON WebUser.id = usr
+                                WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
+                              UNION SELECT 'Mailing list', NULL, data, NULL, usr, name, stamp, cstamp, cstamp
+                                FROM MailingList JOIN WebUser ON WebUser.id = usr
+                                WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
+                              UNION SELECT 'Security', NULL, data, NULL, usr, name, stamp, cstamp, cstamp
+                                FROM Sec JOIN WebUser ON WebUser.id = usr
+                                WHERE stamp >= CURRENT_TIMESTAMP - interval '^(C.intToSql days) DAYS'
+                              ORDER BY stamp DESC`)
+    end
+
+end

diff --git a/request.sig b/request.sig
index 9d9a272..fe4800e 100644 (file)
--- a/request.sig
+++ b/request.sig
@@ -15,7 +15,8 @@ sig
           | INSTALLED
           | REJECTED
 
-    type request = { id : int, usr : int, data : string, msg : string, status : status, stamp : Init.C.timestamp }
+    type request = { id : int, usr : int, data : string, msg : string, status : status,
+                    stamp : Init.C.timestamp, cstamp : Init.C.timestamp option }
 
     val statusFromInt : int -> status
 

diff --git a/request.sml b/request.sml
index ed48c2a..8e3dbc5 100644 (file)
--- a/request.sml
+++ b/request.sml
@@ -11,7 +11,8 @@ datatype status =
        | INSTALLED
        | REJECTED
 
-type request = { id : int, usr : int, data : string, msg : string, status : status, stamp : C.timestamp }
+type request = { id : int, usr : int, data : string, msg : string, status : status,
+                stamp : C.timestamp, cstamp : C.timestamp option }
 
 val statusFromInt =
     fn 0 => NEW
@@ -27,9 +28,10 @@ val statusToInt =
 fun statusFromSql v = statusFromInt (C.intFromSql v)
 fun statusToSql s = C.intToSql (statusToInt s)
 
-fun mkRow [id, usr, data, msg, status, stamp] =
+fun mkRow [id, usr, data, msg, status, stamp, cstamp] =
     {id = C.intFromSql id, usr = C.intFromSql usr, data = C.stringFromSql data,
-     msg = C.stringFromSql msg, status = statusFromSql status, stamp = C.timestampFromSql stamp}
+     msg = C.stringFromSql msg, status = statusFromSql status, stamp = C.timestampFromSql stamp,
+     cstamp = if C.isNull cstamp then NONE else SOME (C.timestampFromSql cstamp)}
   | mkRow r = rowError ("APT request", r)
 
 fun add (usr, data, msg) =
@@ -37,9 +39,9 @@ fun add (usr, data, msg) =
        val db = getDb ()
        val id = nextSeq (db, seq)
     in
-       C.dml db ($`INSERT INTO ^table (id, usr, data, msg, status, stamp)
+       C.dml db ($`INSERT INTO ^table (id, usr, data, msg, status, stamp, cstamp)
                    VALUES (^(C.intToSql id), ^(C.intToSql usr), ^(C.stringToSql data), ^(C.stringToSql msg),
-                           0, CURRENT_TIMESTAMP)`);
+                           0, CURRENT_TIMESTAMP, NULL)`);
        id
     end
 
@@ -47,6 +49,10 @@ fun modify (req : request) =
     let
        val db = getDb ()
     in
+       if #status req <> NEW then
+           ignore (C.dml db ($`UPDATE ^table SET cstamp = CURRENT_TIMESTAMP WHERE id = ^(C.intToSql (#id req))`))
+       else
+           ();
        ignore (C.dml db ($`UPDATE ^table SET
                            usr = ^(C.intToSql (#usr req)), data = ^(C.stringToSql (#data req)),
                               msg = ^(C.stringToSql (#msg req)), status = ^(statusToSql (#status req))
@@ -57,7 +63,7 @@ fun delete id =
     ignore (C.dml (getDb ()) ($`DELETE FROM ^table WHERE id = ^(C.intToSql id)`))
 
 fun lookup id =
-    case C.oneOrNoRows (getDb ()) ($`SELECT id, usr, data, msg, status, stamp
+    case C.oneOrNoRows (getDb ()) ($`SELECT id, usr, data, msg, status, stamp, cstamp
                                     FROM ^table
                                     WHERE id = ^(C.intToSql id)`) of
        SOME row => mkRow row
@@ -67,12 +73,12 @@ fun mkRow' (name :: rest) = (C.stringFromSql name, mkRow rest)
   | mkRow' r = rowError ("Apt.request'", r)
 
 fun list () =
-    C.map (getDb ()) mkRow' ($`SELECT name, ^table.id, usr, data, msg, status, stamp
+    C.map (getDb ()) mkRow' ($`SELECT name, ^table.id, usr, data, msg, status, stamp, cstamp
                               FROM ^table JOIN WebUser ON usr = WebUser.id
                               ORDER BY stamp DESC`)
 
 fun listOpen () =
-    C.map (getDb ()) mkRow' ($`SELECT name, ^table.id, usr, data, msg, status, stamp
+    C.map (getDb ()) mkRow' ($`SELECT name, ^table.id, usr, data, msg, status, stamp, cstamp
                               FROM ^table JOIN WebUser ON usr = WebUser.id
                               WHERE status = 0
                               ORDER BY stamp DESC`)

diff --git a/requestH.sig b/requestH.sig
index 1ff14f3..77e6c9e 100644 (file)
--- a/requestH.sig
+++ b/requestH.sig
@@ -15,7 +15,8 @@ sig
           | INSTALLED
           | REJECTED
 
-    type request = { id : int, usr : int, node : int, data : string, msg : string, status : status, stamp : Init.C.timestamp }
+    type request = { id : int, usr : int, node : int, data : string, msg : string, status : status,
+                    stamp : Init.C.timestamp, cstamp : Init.C.timestamp option }
 
     val statusFromInt : int -> status
 

diff --git a/requestH.sml b/requestH.sml
index f6bb4af..dd4e423 100644 (file)
--- a/requestH.sml
+++ b/requestH.sml
@@ -11,7 +11,8 @@ datatype status =
        | INSTALLED
        | REJECTED
 
-type request = { id : int, usr : int, node : int, data : string, msg : string, status : status, stamp : C.timestamp }
+type request = { id : int, usr : int, node : int, data : string, msg : string, status : status,
+                stamp : C.timestamp, cstamp : C.timestamp option }
 
 val statusFromInt =
     fn 0 => NEW
@@ -27,10 +28,11 @@ val statusToInt =
 fun statusFromSql v = statusFromInt (C.intFromSql v)
 fun statusToSql s = C.intToSql (statusToInt s)
 
-fun mkRow [id, usr, node, data, msg, status, stamp] =
+fun mkRow [id, usr, node, data, msg, status, stamp, cstamp] =
     {id = C.intFromSql id, usr = C.intFromSql usr, node = C.intFromSql node,
      data = C.stringFromSql data,
-     msg = C.stringFromSql msg, status = statusFromSql status, stamp = C.timestampFromSql stamp}
+     msg = C.stringFromSql msg, status = statusFromSql status, stamp = C.timestampFromSql stamp,
+     cstamp = if C.isNull cstamp then NONE else SOME (C.timestampFromSql cstamp)}
   | mkRow r = rowError ("APT request", r)
 
 fun add {usr, node, data, msg} =
@@ -38,9 +40,9 @@ fun add {usr, node, data, msg} =
        val db = getDb ()
        val id = nextSeq (db, seq)
     in
-       C.dml db ($`INSERT INTO ^table (id, usr, node, data, msg, status, stamp)
+       C.dml db ($`INSERT INTO ^table (id, usr, node, data, msg, status, stamp, cstamp)
                    VALUES (^(C.intToSql id), ^(C.intToSql usr), ^(C.intToSql node), ^(C.stringToSql data), ^(C.stringToSql msg),
-                           0, CURRENT_TIMESTAMP)`);
+                           0, CURRENT_TIMESTAMP, NULL)`);
        id
     end
 
@@ -48,6 +50,11 @@ fun modify (req : request) =
     let
        val db = getDb ()
     in
+       if #status req <> NEW then
+           ignore (C.dml db ($`UPDATE ^table SET cstamp = CURRENT_TIMESTAMP WHERE id = ^(C.intToSql (#id req))`))
+       else
+           ();
+
        ignore (C.dml db ($`UPDATE ^table SET
                            usr = ^(C.intToSql (#usr req)), data = ^(C.stringToSql (#data req)),
                               node = ^(C.intToSql (#node req)),
@@ -59,7 +66,7 @@ fun delete id =
     ignore (C.dml (getDb ()) ($`DELETE FROM ^table WHERE id = ^(C.intToSql id)`))
 
 fun lookup id =
-    case C.oneOrNoRows (getDb ()) ($`SELECT id, usr, node, data, msg, status, stamp
+    case C.oneOrNoRows (getDb ()) ($`SELECT id, usr, node, data, msg, status, stamp, cstamp
                                     FROM ^table
                                     WHERE id = ^(C.intToSql id)`) of
        SOME row => mkRow row
@@ -69,12 +76,12 @@ fun mkRow' (name :: rest) = (C.stringFromSql name, mkRow rest)
   | mkRow' r = rowError ("Apt.request'", r)
 
 fun list () =
-    C.map (getDb ()) mkRow' ($`SELECT name, ^table.id, usr, node, data, msg, status, stamp
+    C.map (getDb ()) mkRow' ($`SELECT name, ^table.id, usr, node, data, msg, status, stamp, cstamp
                               FROM ^table JOIN WebUser ON usr = WebUser.id
                               ORDER BY stamp DESC`)
 
 fun listOpen () =
-    C.map (getDb ()) mkRow' ($`SELECT name, ^table.id, usr, node, data, msg, status, stamp
+    C.map (getDb ()) mkRow' ($`SELECT name, ^table.id, usr, node, data, msg, status, stamp, cstamp
                               FROM ^table JOIN WebUser ON usr = WebUser.id
                               WHERE status = 0
                               ORDER BY stamp DESC`)

diff --git a/support.sml b/support.sml
index b82175b..c5895bf 100644 (file)
--- a/support.sml
+++ b/support.sml
@@ -157,8 +157,8 @@ fun modIssue (iss : issue) =
        val db = getDb ()
     in
        case #status iss of
-           PENDING => ignore (C.dml db ($`UPDATE SupIssue SET pstamp = CURRENT_TIMESTAMP`))
-         | CLOSED => ignore (C.dml db ($`UPDATE SupIssue SET cstamp = CURRENT_TIMESTAMP`))
+           PENDING => ignore (C.dml db ($`UPDATE SupIssue SET pstamp = CURRENT_TIMESTAMP WHERE id = ^(C.intToSql (#id iss))`))
+         | CLOSED => ignore (C.dml db ($`UPDATE SupIssue SET cstamp = CURRENT_TIMESTAMP WHERE id = ^(C.intToSql (#id iss))`))
          | _ => ();
        ignore (C.dml db ($`UPDATE SupIssue SET
                            usr = ^(C.intToSql (#usr iss)), cat = ^(C.intToSql (#cat iss)),

diff --git a/tables.sql b/tables.sql
index 682f44d..ea3633c 100644 (file)
--- a/tables.sql
+++ b/tables.sql
@@ -227,6 +227,7 @@ CREATE TABLE Apt(
        msg TEXT NOT NULL,
        status INTEGER NOT NULL,
        stamp TIMESTAMP NOT NULL,
+       cstamp TIMESTAMP,
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
        FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);
 
@@ -239,6 +240,7 @@ CREATE TABLE Domain(
        msg TEXT NOT NULL,
        status INTEGER NOT NULL,
        stamp TIMESTAMP NOT NULL,
+       cstamp TIMESTAMP,
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
 CREATE SEQUENCE DomainSeq START 1;
@@ -250,6 +252,7 @@ CREATE TABLE MailingList(
        msg TEXT NOT NULL,
        status INTEGER NOT NULL,
        stamp TIMESTAMP NOT NULL,
+       cstamp TIMESTAMP,
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE);
 
 CREATE SEQUENCE MailingListSeq START 1;
@@ -306,6 +309,7 @@ CREATE TABLE Sec(
        msg TEXT NOT NULL,
        status INTEGER NOT NULL,
        stamp TIMESTAMP NOT NULL,
+       cstamp TIMESTAMP,
        FOREIGN KEY (usr) REFERENCES WebUser(id) ON DELETE CASCADE,
        FOREIGN KEY (node) REFERENCES WebNode(id) ON DELETE CASCADE);