--- a/s4-blog.sh	Sat Nov 26 09:37:13 2016 +0859
+++ b/s4-blog.sh	Sat Nov 26 09:38:11 2016 +0859
@@ -114,7 +114,11 @@
 
   href="<a href=\"?editheading+$rowid\" accesskey=\"e\" title=\"E\"> 編集 </a>"
   if $iswritable; then
-    href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"L\"> 提出状況 </a>"
+    case `getvalbyid blog mode $rowid` in
+      *report*)
+	href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"L\"> 提出状況 </a>"
+	;;
+    esac
     href3="(<a href=\"?gethandout+$rowid\" accesskey=\"f\" title=\"F\">ファイル取得</a>)"
   fi
   href4='<a href="#bottom" accesskey="b" title="B"> 末尾へ</a>'
@@ -303,41 +307,132 @@
   owner=`getvalbyid blog owner $1`
   title=`getvalbyid blog title $1`
   ge=`gecos $owner`
+  fh=$tmpd/formhead
+  echo "$time [$title]@${ge:-$owner}" > $fh
   lshandoutsub $owner "$@" \
       |_m4 -D_TITLE_="提出状況" \
-	   -D_FORMHEAD_="$time [$title]@${ge:-$owner}" \
+	   -D_FORMHEAD_="syscmd(cat $fh)" \
 	   -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \
 	   $layout/html.m4.html $layout/form+dump-whead.m4.html
+  gn=`echo $owner|htmlescape`
+  echo "<p><a href=\"?lshandoutall+$1\">グループ $gn すべてのレポート板集計</a></p>"
+}
+gethandoutcsv() {
+  # contenttype; echo
+  CATCSV=1 lshandoutall "$1"
+}
+lshandout_ulink_table() {
+  # NO Args.  Read stdin as SQL
+  echo '<table class="b td3rr td3evw">'
+  hrb="<a href=\"?home+"
+  # echo "$sql" | sq -header -html $db \  # Formerly, this is called via sq()
+
+  printf ".mode html\n.header ON\n" | query
+  cat | query \
+      | sed -e "s,\(<TR><TD>\)\([^	]*\)	\(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
+  echo '</table>'
+  printf ".mode list\n.header OFF\n" | query
+}
+lshandoutall() {
+  # $1=rowid of blog
+  if ! blog_writable $1 $user; then
+    echo "メンバー以外は利用できません。" | html p; return
+  fi
+  rowid=$(($1 + 0))
+  owner=`getvalbyid blog owner $1`
+  qowner=`sqlquotestr "$owner"`
+
+  query<<-EOF
+	CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS
+	WITH blog_owner_mode AS (
+	 SELECT id,
+	 	blog.rowid brid,
+ 	 	max(CASE key WHEN 'owner' THEN val END) owner,
+		max(CASE key WHEN 'mode'  THEN val END) mode,
+		max(CASE key WHEN 'title' THEN val END) title
+ 	 FROM blog NATURAL JOIN blog_s
+ 	 GROUP BY id
+	)
+	SELECT id, brid, title FROM blog_owner_mode
+	WHERE owner=$qowner AND mode LIKE '%report%';
+	 /* ↑これでレポート形式の blogid 一覧を得る */
+	EOF
+  bridlist=`query "SELECT brid FROM report_type_blogs;"`
+  if [ -z "$CATCSV" ]; then
+    _m4 -D_TITLE_="提出状況" $layout/html.m4.html
+    ge=`gecos "$owner"`
+    tbls=""
+    grptxt=`echo "${ge:-$owner}"|htmlescape`
+    echo "<h1>$grptxt 書き込み状況一覧</h1>"
+  fi
+  for brid in $bridlist; do
+    brid=$(($brid + 0))		# Ensure to be a number
+    [ $brid = 0 ] && continue
+    time=`getvalbyid blog ctime $brid|colrm 11`
+    title=`getvalbyid blog title $brid|htmlescape`
+    tt="handout_$brid"
+    if [ -z "$CATCSV" ]; then
+      echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a></h2>"
+      lshandoutsub "$owner" $brid "$tt"
+    else
+      lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
+    fi
+    tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
+  done
+  sql="SELECT * FROM $tbls;"
+  if [ -z "$CATCSV" ]; then
+    echo "<hr><h2>総合</h2>"
+    echo "$sql" | lshandout_ulink_table
+    echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">csv</a>)</h2>"
+    printf ".mode csv\n.header ON\n" | query
+    echo '<pre class="list">'
+    echo "$sql" | query | sed 's/^"[0-9]*	/"/'
+    echo "</pre>"
+  else
+    contenttype "Application/CSV"
+    printf ".mode csv\n.header ON\n" | query >/dev/null
+    fn=report-count.csv
+    printf 'Content-Disposition: filename="%s"\n' "$fn"
+    outfile=tmp/out-$$.csv
+    rm tmp/sql.out
+    echo "$sql" | query | sed 's/^"[0-9]*	/"/' > $outfile
+    echo "Content-Length: " `cat $outfile | wc -c`; echo
+    
+    cat $outfile
+    exit 0
+  fi
+  printf ".mode list\n.header OFF\n.separator |\n" | query
 }
 lshandoutsub() {
-  # $1=owner $2=rowid of blog
+  # $1=owner $2=rowid of blog &optional $3=temp_table name
+  qgname=`sqlquote "$1"`
   if isgroup $1; then
-    sample="(select user from grp_mem where gname='$1')"
+    sample="(select user from grp_mem where gname=$qgname)"
   else
     sample="(select distinct author as user from arts)"
     echo "(集計は板への投稿者のみ)" | html p
   fi
-  sql="with arts as (select id,author from article \
+  tmpname="${3:-handout_$2}"
+  sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS
+      with arts as (select id,author from article \
 	    where blogid=(select id from blog where rowid=$2))\
       select (select rowid from user where name=c0.user)||'	'|| \
 	      (select gecos from gecoses where name=c0.user) as 'メンバー',\
 	     (select substr(c0.user, 1, instr(c0.user, '@')-1)) 'uname',\
 	     sum(case when c1.key is not null then 1 else 0 end)\
-		 as 'コメント記入',\
+		 as '[$title] コメント記入',\
 	     sum(case when c2.key is not null then 1 else 0 end)\
-		 as 'ファイルの提出'\
+		 as '[$title] ファイルの提出'\
 	 from $sample c0 \
 	        left join (select id,author from arts) a\
 		on c0.user=a.author\
 	        left join (select id,key from article_s where key='text') c1\
 		on a.id=c1.id left join (select id,key from article_m ) c2\
-		on c1.id=c2.id group by c0.user order by c0.user;"
+		on c1.id=c2.id group by c0.user order by c0.user;\
+	\
+	SELECT * FROM $tmpname;"
     err ishandoutsub: sql="$sql"
-    echo '<table class="b td2r td3r">'
-    hrb="<a href=\"?home+"
-    echo "$sql" | sq -header -html $db \
-	| sed -e "s,\(<TR><TD>\)\([^	]*\)	\(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
-    echo '</table>'
+    echo "$sql" | lshandout_ulink_table
 }
 gethandout() {
   # $1=rowid of blog