--- a/s4-funcs.sh	Fri Jun 23 23:11:31 2017 +0859
+++ b/s4-funcs.sh	Fri Jun 23 23:23:40 2017 +0859
@@ -2389,7 +2389,7 @@
   fi
   # New entry
   sql="select 	/* Ahh, ugly SQL, I wanna fix... */
-	 case
+	 case	/* YES, can be REMOVED if it works until 2017-07-01 */
 	 when (select user from grp_adm where
 		 gname=(select gname from grp where rowid=$grid)
 		 and user=a.name) is not null
@@ -2413,6 +2413,42 @@
 	 WHERE name in (select user from grp_mem where
 		 gname=(select gname from grp where rowid=$grid))
 	 ORDER by a.gecos;"
+  sql="WITH mems AS (
+	  SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g
+	  ON gm.user=g.name
+	  WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
+	), posts AS (
+  	  SELECT author, count(author) post
+	  FROM article NATURAL JOIN article_s
+  	  WHERE blogid IN (SELECT id FROM blog_s
+                   WHERE key='owner'
+                   AND val=(SELECT gname FROM grp WHERE rowid=$grid))
+  		   AND key='text'
+	  GROUP BY author
+	), teams AS (
+	  SELECT user, group_concat(val, ', ') team
+	  FROM grp_mem_m
+	  WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
+	  AND key='team'
+	  GROUP BY user
+	), user_post AS (
+	  SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST
+	  FROM mems m LEFT JOIN posts
+	  ON m.name=posts.author
+	  GROUP by m.rowid
+	)
+	SELECT
+	  CASE
+	  WHEN (SELECT user FROM grp_adm
+	        WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
+		AND user=up.name) IS NOT NULL
+	      then 'k'
+	  ELSE ''
+	  END || rowid || ',' || gecos NAME,
+	  post POST, team TEAM
+	FROM user_post up LEFT JOIN teams t
+	ON up.name=t.user
+	ORDER BY gecos;"
   ## err grpaction: "`echo \"$sql\"`"
   tf=$tmpd/title.$$
   echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf