--- a/s4-blog.sh	Sat Nov 26 10:14:06 2016 +0859
+++ b/s4-blog.sh	Sun Nov 27 10:15:15 2016 +0859
@@ -321,6 +321,37 @@
   # contenttype; echo
   CATCSV=1 lshandoutall "$1"
 }
+gethandoutcsv2() {
+  # contenttype; echo
+  SQL=$(cat<<-EOF)  gethandoutcsv "$1"
+	WITH this_blog_articles AS (
+	  SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime
+	  FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid
+	), text_or_file AS (
+	 SELECT bid, author, title, ctime, 'text' shu, count(val) cnt
+	 FROM this_blog_articles tba, article_s s
+	      ON tba.aid=s.id
+	 WHERE key='text'
+	 GROUP by bid, author
+	 	UNION
+	 SELECT bid, author, title, ctime, 'file' shu, count(val) cnt
+	 FROM this_blog_articles tba, article_m m
+	      ON tba.aid=m.id
+	 WHERE key='image'
+	 GROUP by bid, author
+	), count_list AS (
+	 SELECT author,
+	        substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit,
+	        cnt
+	 FROM text_or_file
+	)
+	 SELECT gecos "名前",
+		substr(author, 1, instr(author, '@')-1) "uname",
+		unit,
+		cnt "count"
+	 FROM count_list cl JOIN gecoses g ON cl.author=g.name;
+	EOF
+}
 lshandout_ulink_table() {
   # NO Args.  Read stdin as SQL
   echo '<table class="b td3rr td3evw">'
@@ -349,11 +380,12 @@
 	 	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
+		max(CASE key WHEN 'title' THEN val END) title,
+		max(CASE key WHEN 'ctime' THEN val END) ctime
  	 FROM blog NATURAL JOIN blog_s
  	 GROUP BY id
 	)
-	SELECT id, brid, title FROM blog_owner_mode
+	SELECT id, brid, title, ctime FROM blog_owner_mode
 	WHERE owner=$qowner AND mode LIKE '%report%';
 	 /* ↑これでレポート形式の blogid 一覧を得る */
 	EOF
@@ -379,15 +411,16 @@
     fi
     tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
   done
-  sql="SELECT * FROM $tbls;"
+  sql=${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>"
+    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>"
+    echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>"
   else
     contenttype "Application/CSV"
     printf ".mode csv\n.header ON\n" | query >/dev/null
@@ -418,7 +451,7 @@
 	    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',\
+	     substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\
 	     sum(case when c1.key is not null then 1 else 0 end)\
 		 as '[$title] コメント記入',\
 	     sum(case when c2.key is not null then 1 else 0 end)\