s4

changeset 937:7b3786b1eb4b

find changesets by author, revision, files, or words in the commit message
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 diff
     1.1 --- a/s4-funcs.sh	Thu Apr 29 15:43:33 2021 +0900
     1.2 +++ b/s4-funcs.sh	Sun Aug 08 11:28:37 2021 +0900
     1.3 @@ -3129,7 +3129,7 @@
     1.4    from=`getpar from`; to=`getpar to`
     1.5    from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"
     1.6    to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"
     1.7 -  fromtonote="<p title=\"Count the Number of Posts from-to\">POST集計: $from_input - $to_input</p><!-- $from - $to -->"
     1.8 +  fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p><!-- $from - $to -->"
     1.9    # New entry
    1.10    sql="WITH mems AS (
    1.11  	  SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g
    1.12 @@ -3157,6 +3157,21 @@
    1.13  	  FROM mems m LEFT JOIN posts
    1.14  	  ON m.name=posts.author
    1.15  	  GROUP by m.rowid
    1.16 +	), user_view AS (
    1.17 +	  SELECT user vuser,count(user) cnt
    1.18 +	  FROM tblaccesses
    1.19 +	  WHERE user IN (
    1.20 +		SELECT user FROM grp_mem
    1.21 +		WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))
    1.22 +	  AND tbl='blog'
    1.23 +	  AND tblrowid IN (
    1.24 +		    SELECT rowid FROM blog
    1.25 +		    WHERE id IN (
    1.26 +		    	  SELECT id FROM blog_s
    1.27 +			  WHERE key='owner' AND val=(
    1.28 +			  	SELECT gname FROM grp WHERE rowid=$grid)))
    1.29 +	  AND time BETWEEN '${from:-0000}' AND '${to:-9999}'
    1.30 +	  GROUP BY user ORDER BY cnt
    1.31  	)
    1.32  	SELECT
    1.33  	  CASE
    1.34 @@ -3168,10 +3183,13 @@
    1.35  	  END || rowid || ','
    1.36  	  || rtrim(substr(name, 1, instr(name, '@')), '@') || ','
    1.37  	  || gecos NAME,
    1.38 -	  post POST, team _TEAM_
    1.39 +	  post POST,
    1.40 +	  (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,
    1.41 + 	  team _TEAM_
    1.42  	FROM user_post up LEFT JOIN teams t
    1.43  	ON up.name=t.user
    1.44  	ORDER BY up.name;"
    1.45 +  ## Want to count ACCESS with JOIN, but failed to produce user-unique rows
    1.46    ## err grpaction: "`echo \"$sql\"`"
    1.47    tf=$tmpd/title.$$
    1.48    echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf
    1.49 @@ -3263,7 +3281,7 @@
    1.50  `cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>
    1.51  </div>
    1.52  <h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote
    1.53 -<table class="td2r thl">
    1.54 +<table class="td2r td3r thl">
    1.55  `sq $db -header -html "$sql"`
    1.56  </table>
    1.57  `cgi_hidden grp $grid`