From a75ed94bb4d8801149d1eff968bcd1d9163c3971 Mon Sep 17 00:00:00 2001 From: Adam Chlipala Date: Sun, 25 Feb 2007 23:57:45 +0000 Subject: [PATCH] Summary of recent support requests with response times --- header.mlt.in | 1 + qos.mlt | 29 +++++++++++++++++++++++++++++ qos.sig | 8 ++++++++ qos.sml | 45 +++++++++++++++++++++++++++++++++++++++++++++ request.sig | 3 ++- request.sml | 22 ++++++++++++++-------- requestH.sig | 3 ++- requestH.sml | 23 +++++++++++++++-------- support.sml | 4 ++-- tables.sql | 4 ++++ 10 files changed, 122 insertions(+), 20 deletions(-) create mode 100644 qos.mlt create mode 100644 qos.sig create mode 100644 qos.sml diff --git a/header.mlt.in b/header.mlt.in index b6cd42f..238d8d4 100644 --- a/header.mlt.in +++ b/header.mlt.in @@ -54,6 +54,7 @@ Miscellaneous
  • Review pending membership applications
  • Contact information directory
  • Polls
    • +
  • Support quality statistics
    • diff --git a/qos.mlt b/qos.mlt new file mode 100644 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 %> + +
    +Show me the entries from the last days. + +
    + + + +<% + +foreach qos in Qos.recent days do %> + + + + + + +<% end %> + +
    Kind Description User Placed Response Closed
    <% switch #kindUrl qos of SOME url => %><% end %><% Web.html (#kind qos) %><% switch #kindUrl qos of SOME _ => %><% end %><% switch #url qos of SOME url => %><% end %><% Web.html (#name qos) %><% switch #url qos of SOME _ => %><% end %><% Web.html (#uname qos) %><% #stamp qos %><% switch #pstamp qos of NONE => %>-<% | SOME stamp => stamp end %><% switch #cstamp qos of NONE => %>-<% | SOME stamp => stamp end %>
    + +<% @footer [] %> + + diff --git a/qos.sig b/qos.sig new file mode 100644 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 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 --- 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 --- 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 --- 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 --- 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 --- 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 --- 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); -- 2.20.1