Mercurial > hg > egg-tcls
changeset 519:1cfa567f9500
quotedb: Improve statistics.
author | Matti Hamalainen <ccr@tnsp.org> |
---|---|
date | Sat, 02 May 2020 15:29:03 +0300 |
parents | 7fdc069840cc |
children | 0da68d583b38 |
files | config.quotedb.example quotedb.tcl |
diffstat | 2 files changed, 30 insertions(+), 10 deletions(-) [+] |
line wrap: on
line diff
--- a/config.quotedb.example Sat May 02 07:41:41 2020 +0300 +++ b/config.quotedb.example Sat May 02 15:29:03 2020 +0300 @@ -86,7 +86,8 @@ "quote_not_found" "@name@ #@1@ ei löydy, tai se ei kuulu käyttäjälle '@2@'." - "stats" "@name@-tilastot: @1@ quotea, @2@ annettua ääntä. Top @3@ lisääjät: @4@" + "stats" "@name@-tilastot: @1@ quotea, @2@ annettua ääntä." + "stats2" "Top-@1@ lisääjät: @2@ | Viimeisimmät vuodet: @4@ | Tuottoisimmat vuodet: @6@" "help" "@cmd@ <id> \002|\002 add <teksti> \002|\002 update <id> <teksti> \002|\002 del <id> \002|\002 find <parametrit> \002|\002 vote \[<id> \[1 | -1\]\] \002|\002 top <n> \002|\002 stats" @@ -124,7 +125,7 @@ set qdb_messages(kissadb_fmt_get) "kissa @2@ (#@1@)" set qdb_messages(kissadb_urlprefix) "" set qdb_messages(kissadb_name) "Kissa" -set qdb_messages(kissadb_stats) "Kissa-tilastot: @1@ kissajuttua, @2@ äänestettyä kissamaisuutta. Top @3@ lisääjät: @4@" +set qdb_messages(kissadb_stats) "Kissa-tilastot: @1@ kissajuttua, @2@ äänestettyä kissamaisuutta." set qdb_messages(kissadb_quote_does_not_exist) "Kissaa #@1@ ei ole olemassa." @@ -134,7 +135,7 @@ proc spede_msg_cmd {unick uhost uhand utext} { qdb_command "!spede" "spededb" $unick $uhost $uhand "" $utext 0 } set qdb_messages(spededb_urlprefix) "https://tnsp.org/spede/img/" set qdb_messages(spededb_name) "Spede" -set qdb_messages(spededb_stats) "Spede-tilastot: @1@ spedeä, @2@ äänestettyä spedeä. Top @3@ lisääjät: @4@" +set qdb_messages(spededb_stats) "Spede-tilastot: @1@ spedeä, @2@ äänestettyä spedeä." set qdb_messages(spededb_quote_does_not_exist) "Spedeä #@1@ ei ole." @@ -144,7 +145,7 @@ proc mn_msg_cmd {unick uhost uhand utext} { qdb_command "!mn" "mndb" $unick $uhost $uhand "" $utext 0 } set qdb_messages(mndb_urlprefix) "https://tnsp.org/mndb/img/" set qdb_messages(mndb_name) "Nykäsismi" -set qdb_messages(mndb_stats) "Nykäs-tilastot: @1@ nykäsismiä, @2@ äänestettyä. Top @3@ lisääjät: @4@" +set qdb_messages(mndb_stats) "Nykäs-tilastot: @1@ nykäsismiä, @2@ äänestettyä." set qdb_messages(mndb_quote_does_not_exist) "Nykäsismiä #@1@ ei ole." @@ -154,5 +155,5 @@ proc tuksu_msg_cmd {unick uhost uhand utext} { qdb_command "!tuksu" "tuksudb" $unick $uhost $uhand "" $utext 0 } set qdb_messages(tuksudb_urlprefix) "https://tnsp.org/tuksu/img/" set qdb_messages(tuksudb_name) "Tuksu" -set qdb_messages(tuksudb_stats) "Tuksu-tilastot: @1@ tuksuilua, @2@ äänestettyä. Top @3@ lisääjät: @4@" +set qdb_messages(tuksudb_stats) "Tuksu-tilastot: @1@ tuksuilua, @2@ äänestettyä." set qdb_messages(tuksudb_quote_does_not_exist) "Tuksuilua #@1@ ei ole."
--- 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 }