# HG changeset patch
# User HIROSE Yuuji <yuuji@gentei.org>
# Date 1628389717 -32400
# Node ID 7b3786b1eb4b72fa475071a11e99457b72b188ee
# Parent  789258aa857a16884300222e9319975b10c54bcb
Summarize access count as well as post count

diff -r 789258aa857a -r 7b3786b1eb4b s4-funcs.sh
--- 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`