Mercurial > hgrepos > hgweb.cgi > s4
changeset 937:7b3786b1eb4b draft
Summarize access count as well as post count
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Sun, 08 Aug 2021 11:28:37 +0900 |
parents | 789258aa857a |
children | 627a7fdf3e16 |
files | s4-funcs.sh |
diffstat | 1 files changed, 21 insertions(+), 3 deletions(-) [+] |
line wrap: on
line diff
--- a/s4-funcs.sh Thu Apr 29 15:43:33 2021 +0900 +++ b/s4-funcs.sh Sun Aug 08 11:28:37 2021 +0900 @@ -3129,7 +3129,7 @@ from=`getpar from`; to=`getpar to` from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">" to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">" - fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p><!-- $from - $to -->" + fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p><!-- $from - $to -->" # New entry sql="WITH mems AS ( SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g @@ -3157,6 +3157,21 @@ FROM mems m LEFT JOIN posts ON m.name=posts.author GROUP by m.rowid + ), user_view AS ( + SELECT user vuser,count(user) cnt + FROM tblaccesses + WHERE user IN ( + SELECT user FROM grp_mem + WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)) + AND tbl='blog' + AND tblrowid IN ( + SELECT rowid FROM blog + WHERE id IN ( + SELECT id FROM blog_s + WHERE key='owner' AND val=( + SELECT gname FROM grp WHERE rowid=$grid))) + AND time BETWEEN '${from:-0000}' AND '${to:-9999}' + GROUP BY user ORDER BY cnt ) SELECT CASE @@ -3168,10 +3183,13 @@ END || rowid || ',' || rtrim(substr(name, 1, instr(name, '@')), '@') || ',' || gecos NAME, - post POST, team _TEAM_ + post POST, + (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS, + team _TEAM_ FROM user_post up LEFT JOIN teams t ON up.name=t.user ORDER BY up.name;" + ## Want to count ACCESS with JOIN, but failed to produce user-unique rows ## err grpaction: "`echo \"$sql\"`" tf=$tmpd/title.$$ echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf @@ -3263,7 +3281,7 @@ `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label> </div> <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote -<table class="td2r thl"> +<table class="td2r td3r thl"> `sq $db -header -html "$sql"` </table> `cgi_hidden grp $grid`