1.1 --- a/s4-blog.sh	Sat Nov 26 10:14:06 2016 +0859
     1.2 +++ b/s4-blog.sh	Sun Nov 27 10:15:15 2016 +0859
     1.3 @@ -321,6 +321,37 @@
     1.4    # contenttype; echo
     1.5    CATCSV=1 lshandoutall "$1"
     1.6  }
     1.7 +gethandoutcsv2() {
     1.8 +  # contenttype; echo
     1.9 +  SQL=$(cat<<-EOF)  gethandoutcsv "$1"
    1.10 +	WITH this_blog_articles AS (
    1.11 +	  SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime
    1.12 +	  FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid
    1.13 +	), text_or_file AS (
    1.14 +	 SELECT bid, author, title, ctime, 'text' shu, count(val) cnt
    1.15 +	 FROM this_blog_articles tba, article_s s
    1.16 +	      ON tba.aid=s.id
    1.17 +	 WHERE key='text'
    1.18 +	 GROUP by bid, author
    1.19 +	 	UNION
    1.20 +	 SELECT bid, author, title, ctime, 'file' shu, count(val) cnt
    1.21 +	 FROM this_blog_articles tba, article_m m
    1.22 +	      ON tba.aid=m.id
    1.23 +	 WHERE key='image'
    1.24 +	 GROUP by bid, author
    1.25 +	), count_list AS (
    1.26 +	 SELECT author,
    1.27 +	        substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit,
    1.28 +	        cnt
    1.29 +	 FROM text_or_file
    1.30 +	)
    1.31 +	 SELECT gecos "名前",
    1.32 +		substr(author, 1, instr(author, '@')-1) "uname",
    1.33 +		unit,
    1.34 +		cnt "count"
    1.35 +	 FROM count_list cl JOIN gecoses g ON cl.author=g.name;
    1.36 +	EOF
    1.37 +}
    1.38  lshandout_ulink_table() {
    1.39    # NO Args.  Read stdin as SQL
    1.40    echo '<table class="b td3rr td3evw">'
    1.41 @@ -349,11 +380,12 @@
    1.42  	 	blog.rowid brid,
    1.43   	 	max(CASE key WHEN 'owner' THEN val END) owner,
    1.44  		max(CASE key WHEN 'mode'  THEN val END) mode,
    1.45 -		max(CASE key WHEN 'title' THEN val END) title
    1.46 +		max(CASE key WHEN 'title' THEN val END) title,
    1.47 +		max(CASE key WHEN 'ctime' THEN val END) ctime
    1.48   	 FROM blog NATURAL JOIN blog_s
    1.49   	 GROUP BY id
    1.50  	)
    1.51 -	SELECT id, brid, title FROM blog_owner_mode
    1.52 +	SELECT id, brid, title, ctime FROM blog_owner_mode
    1.53  	WHERE owner=$qowner AND mode LIKE '%report%';
    1.54  	 /* ↑これでレポート形式の blogid 一覧を得る */
    1.55  	EOF
    1.56 @@ -379,15 +411,16 @@
    1.57      fi
    1.58      tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
    1.59    done
    1.60 -  sql="SELECT * FROM $tbls;"
    1.61 +  sql=${SQL:-"SELECT * FROM $tbls;"}
    1.62    if [ -z "$CATCSV" ]; then
    1.63      echo "<hr><h2>総合</h2>"
    1.64      echo "$sql" | lshandout_ulink_table
    1.65 -    echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">csv</a>)</h2>"
    1.66 +    echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">CSV</a>)</h2>"
    1.67      printf ".mode csv\n.header ON\n" | query
    1.68      echo '<pre class="list">'
    1.69      echo "$sql" | query | sed 's/^"[0-9]*	/"/'
    1.70      echo "</pre>"
    1.71 +    echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>"
    1.72    else
    1.73      contenttype "Application/CSV"
    1.74      printf ".mode csv\n.header ON\n" | query >/dev/null
    1.75 @@ -418,7 +451,7 @@
    1.76  	    where blogid=(select id from blog where rowid=$2))\
    1.77        select (select rowid from user where name=c0.user)||'	'|| \
    1.78  	      (select gecos from gecoses where name=c0.user) as 'メンバー',\
    1.79 -	     (select substr(c0.user, 1, instr(c0.user, '@')-1)) 'uname',\
    1.80 +	     substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\
    1.81  	     sum(case when c1.key is not null then 1 else 0 end)\
    1.82  		 as '[$title] コメント記入',\
    1.83  	     sum(case when c2.key is not null then 1 else 0 end)\

     2.1 --- a/s4.cgi	Sat Nov 26 10:14:06 2016 +0859
     2.2 +++ b/s4.cgi	Sun Nov 27 10:15:15 2016 +0859
     2.3 @@ -71,7 +71,7 @@
     2.4      echo "Refresh: 0; $newurl"; echo
     2.5      exit 0
     2.6      ;;
     2.7 -  lshandout|lshandoutall|gethandout|gethandoutcsv)
     2.8 +  lshandout|lshandoutall|gethandout|gethandoutcsv|gethandoutcsv2)
     2.9      case "$stage" in
    2.10        lshandout*) contenttype; echo ;;
    2.11      esac