diff quotedb.tcl @ 519:1cfa567f9500

quotedb: Improve statistics.
author Matti Hamalainen <ccr@tnsp.org>
date Sat, 02 May 2020 15:29:03 +0300
parents 7fdc069840cc
children 3046ee0e574d
line wrap: on
line diff
--- a/quotedb.tcl	Sat May 02 07:41:41 2020 +0300
+++ b/quotedb.tcl	Sat May 02 15:29:03 2020 +0300
@@ -369,14 +369,33 @@
   set nmaxtop 5
 
   set usql "SELECT user,count(*) AS uquotes FROM ${utable} GROUP BY user ORDER BY uquotes DESC LIMIT ${nmaxtop}"
-  set ntop {}
-  set nindex 0
+  set ltopusers {}
+  set ntopusers 0
   qdb_handle eval $usql {
-    incr nindex
-    lappend ntop "${user} (${uquotes})"
+    incr ntopusers
+    lappend ltopusers "${user} (${uquotes})"
   }
 
-  qdb_msg $ucmd $utable $upublic $unick $uchan "stats" [list $nquotes $nvotes $nindex [join $ntop ", "]]
+  set nmaxtop 3
+
+  set usql "SELECT count(*) AS uquotes,CAST(strftime('%Y',utime,'unixepoch') AS integer) AS uyear FROM ${utable} GROUP BY uyear ORDER BY uyear DESC LIMIT ${nmaxtop}"
+  set llatestyears {}
+  set nlatestyears 0
+  qdb_handle eval $usql {
+    incr nlatestyears
+    lappend llatestyears "${uquotes} (${uyear})"
+  }
+
+  set usql "SELECT count(*) AS uquotes,CAST(strftime('%Y',utime,'unixepoch') AS integer) AS uyear FROM ${utable} GROUP BY uyear ORDER BY uquotes DESC LIMIT ${nmaxtop}"
+  set ltopeyears {}
+  set ntopyears 0
+  qdb_handle eval $usql {
+    incr ntopyears
+    lappend ltopeyears "${uquotes} (${uyear})"
+  }
+
+  qdb_msg $ucmd $utable $upublic $unick $uchan "stats" [list $nquotes $nvotes]
+  qdb_msg $ucmd $utable $upublic $unick $uchan "stats2" [list $ntopusers [join $ltopusers ", "] $nlatestyears [join $llatestyears ", "] $ntopyears [join $ltopeyears ", "]]
 
   return 0
 }