1.1 --- a/examples/common/default/default.css	Sat Nov 26 09:37:13 2016 +0859
     1.2 +++ b/examples/common/default/default.css	Sat Nov 26 09:38:11 2016 +0859
     1.3 @@ -20,10 +20,21 @@
     1.4  div.topmenu ul a {text-decoration: none;}
     1.5  
     1.6  ldiv.topmenu + h1 {clear: both; margin-top: 3em;}
     1.7 +pre.list {
     1.8 +    overflow: auto; width: 96%; height: 40%; background: white;
     1.9 +    border: 2px inset;
    1.10 +}
    1.11 +p.copyright {
    1.12 +    border-top: 2px inset #555; text-align: right;
    1.13 +    font-size: 50%;
    1.14 +}
    1.15 +p.copyright a {text-decoration: none;}
    1.16  
    1.17  td {padding-left: 0.5ex; padding-right: 0.5ex;}
    1.18  table.td2r td:nth-child(2) {text-align: right;}
    1.19  table.td3r td:nth-child(3) {text-align: right;}
    1.20 +table.td3rr td:nth-child(n+3) {text-align: right;}
    1.21 +table.td3evw th:nth-child(2n+4) {background: white;}
    1.22  
    1.23  table.form, table.b, table.b tr, table.b td, table.b th {
    1.24    border: 1px solid black; border-collapse: collapse;
    1.25 @@ -142,7 +153,7 @@
    1.26  }
    1.27  div.foldtabs > div {
    1.28      position: absolute; top: 2.5em; opacity: 0.0; background: pink;
    1.29 -    margin: 2px; scroll: auto;
    1.30 +    margin: 2px; overflow: auto;
    1.31  }
    1.32  div.foldtabs input[type="radio"] {display: none;}
    1.33  div.foldtabs input[type="radio"]:checked + label + div {

     2.1 --- a/s4-blog.sh	Sat Nov 26 09:37:13 2016 +0859
     2.2 +++ b/s4-blog.sh	Sat Nov 26 09:38:11 2016 +0859
     2.3 @@ -114,7 +114,11 @@
     2.4  
     2.5    href="<a href=\"?editheading+$rowid\" accesskey=\"e\" title=\"E\"> 編集 </a>"
     2.6    if $iswritable; then
     2.7 -    href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"L\"> 提出状況 </a>"
     2.8 +    case `getvalbyid blog mode $rowid` in
     2.9 +      *report*)
    2.10 +	href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"L\"> 提出状況 </a>"
    2.11 +	;;
    2.12 +    esac
    2.13      href3="(<a href=\"?gethandout+$rowid\" accesskey=\"f\" title=\"F\">ファイル取得</a>)"
    2.14    fi
    2.15    href4='<a href="#bottom" accesskey="b" title="B"> 末尾へ</a>'
    2.16 @@ -303,41 +307,132 @@
    2.17    owner=`getvalbyid blog owner $1`
    2.18    title=`getvalbyid blog title $1`
    2.19    ge=`gecos $owner`
    2.20 +  fh=$tmpd/formhead
    2.21 +  echo "$time [$title]@${ge:-$owner}" > $fh
    2.22    lshandoutsub $owner "$@" \
    2.23        |_m4 -D_TITLE_="提出状況" \
    2.24 -	   -D_FORMHEAD_="$time [$title]@${ge:-$owner}" \
    2.25 +	   -D_FORMHEAD_="syscmd(cat $fh)" \
    2.26  	   -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \
    2.27  	   $layout/html.m4.html $layout/form+dump-whead.m4.html
    2.28 +  gn=`echo $owner|htmlescape`
    2.29 +  echo "<p><a href=\"?lshandoutall+$1\">グループ $gn すべてのレポート板集計</a></p>"
    2.30 +}
    2.31 +gethandoutcsv() {
    2.32 +  # contenttype; echo
    2.33 +  CATCSV=1 lshandoutall "$1"
    2.34 +}
    2.35 +lshandout_ulink_table() {
    2.36 +  # NO Args.  Read stdin as SQL
    2.37 +  echo '<table class="b td3rr td3evw">'
    2.38 +  hrb="<a href=\"?home+"
    2.39 +  # echo "$sql" | sq -header -html $db \  # Formerly, this is called via sq()
    2.40 +
    2.41 +  printf ".mode html\n.header ON\n" | query
    2.42 +  cat | query \
    2.43 +      | sed -e "s,\(<TR><TD>\)\([^	]*\)	\(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
    2.44 +  echo '</table>'
    2.45 +  printf ".mode list\n.header OFF\n" | query
    2.46 +}
    2.47 +lshandoutall() {
    2.48 +  # $1=rowid of blog
    2.49 +  if ! blog_writable $1 $user; then
    2.50 +    echo "メンバー以外は利用できません。" | html p; return
    2.51 +  fi
    2.52 +  rowid=$(($1 + 0))
    2.53 +  owner=`getvalbyid blog owner $1`
    2.54 +  qowner=`sqlquotestr "$owner"`
    2.55 +
    2.56 +  query<<-EOF
    2.57 +	CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS
    2.58 +	WITH blog_owner_mode AS (
    2.59 +	 SELECT id,
    2.60 +	 	blog.rowid brid,
    2.61 + 	 	max(CASE key WHEN 'owner' THEN val END) owner,
    2.62 +		max(CASE key WHEN 'mode'  THEN val END) mode,
    2.63 +		max(CASE key WHEN 'title' THEN val END) title
    2.64 + 	 FROM blog NATURAL JOIN blog_s
    2.65 + 	 GROUP BY id
    2.66 +	)
    2.67 +	SELECT id, brid, title FROM blog_owner_mode
    2.68 +	WHERE owner=$qowner AND mode LIKE '%report%';
    2.69 +	 /* ↑これでレポート形式の blogid 一覧を得る */
    2.70 +	EOF
    2.71 +  bridlist=`query "SELECT brid FROM report_type_blogs;"`
    2.72 +  if [ -z "$CATCSV" ]; then
    2.73 +    _m4 -D_TITLE_="提出状況" $layout/html.m4.html
    2.74 +    ge=`gecos "$owner"`
    2.75 +    tbls=""
    2.76 +    grptxt=`echo "${ge:-$owner}"|htmlescape`
    2.77 +    echo "<h1>$grptxt 書き込み状況一覧</h1>"
    2.78 +  fi
    2.79 +  for brid in $bridlist; do
    2.80 +    brid=$(($brid + 0))		# Ensure to be a number
    2.81 +    [ $brid = 0 ] && continue
    2.82 +    time=`getvalbyid blog ctime $brid|colrm 11`
    2.83 +    title=`getvalbyid blog title $brid|htmlescape`
    2.84 +    tt="handout_$brid"
    2.85 +    if [ -z "$CATCSV" ]; then
    2.86 +      echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a></h2>"
    2.87 +      lshandoutsub "$owner" $brid "$tt"
    2.88 +    else
    2.89 +      lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
    2.90 +    fi
    2.91 +    tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
    2.92 +  done
    2.93 +  sql="SELECT * FROM $tbls;"
    2.94 +  if [ -z "$CATCSV" ]; then
    2.95 +    echo "<hr><h2>総合</h2>"
    2.96 +    echo "$sql" | lshandout_ulink_table
    2.97 +    echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">csv</a>)</h2>"
    2.98 +    printf ".mode csv\n.header ON\n" | query
    2.99 +    echo '<pre class="list">'
   2.100 +    echo "$sql" | query | sed 's/^"[0-9]*	/"/'
   2.101 +    echo "</pre>"
   2.102 +  else
   2.103 +    contenttype "Application/CSV"
   2.104 +    printf ".mode csv\n.header ON\n" | query >/dev/null
   2.105 +    fn=report-count.csv
   2.106 +    printf 'Content-Disposition: filename="%s"\n' "$fn"
   2.107 +    outfile=tmp/out-$$.csv
   2.108 +    rm tmp/sql.out
   2.109 +    echo "$sql" | query | sed 's/^"[0-9]*	/"/' > $outfile
   2.110 +    echo "Content-Length: " `cat $outfile | wc -c`; echo
   2.111 +    
   2.112 +    cat $outfile
   2.113 +    exit 0
   2.114 +  fi
   2.115 +  printf ".mode list\n.header OFF\n.separator |\n" | query
   2.116  }
   2.117  lshandoutsub() {
   2.118 -  # $1=owner $2=rowid of blog
   2.119 +  # $1=owner $2=rowid of blog &optional $3=temp_table name
   2.120 +  qgname=`sqlquote "$1"`
   2.121    if isgroup $1; then
   2.122 -    sample="(select user from grp_mem where gname='$1')"
   2.123 +    sample="(select user from grp_mem where gname=$qgname)"
   2.124    else
   2.125      sample="(select distinct author as user from arts)"
   2.126      echo "(集計は板への投稿者のみ)" | html p
   2.127    fi
   2.128 -  sql="with arts as (select id,author from article \
   2.129 +  tmpname="${3:-handout_$2}"
   2.130 +  sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS
   2.131 +      with arts as (select id,author from article \
   2.132  	    where blogid=(select id from blog where rowid=$2))\
   2.133        select (select rowid from user where name=c0.user)||'	'|| \
   2.134  	      (select gecos from gecoses where name=c0.user) as 'メンバー',\
   2.135  	     (select substr(c0.user, 1, instr(c0.user, '@')-1)) 'uname',\
   2.136  	     sum(case when c1.key is not null then 1 else 0 end)\
   2.137 -		 as 'コメント記入',\
   2.138 +		 as '[$title] コメント記入',\
   2.139  	     sum(case when c2.key is not null then 1 else 0 end)\
   2.140 -		 as 'ファイルの提出'\
   2.141 +		 as '[$title] ファイルの提出'\
   2.142  	 from $sample c0 \
   2.143  	        left join (select id,author from arts) a\
   2.144  		on c0.user=a.author\
   2.145  	        left join (select id,key from article_s where key='text') c1\
   2.146  		on a.id=c1.id left join (select id,key from article_m ) c2\
   2.147 -		on c1.id=c2.id group by c0.user order by c0.user;"
   2.148 +		on c1.id=c2.id group by c0.user order by c0.user;\
   2.149 +	\
   2.150 +	SELECT * FROM $tmpname;"
   2.151      err ishandoutsub: sql="$sql"
   2.152 -    echo '<table class="b td2r td3r">'
   2.153 -    hrb="<a href=\"?home+"
   2.154 -    echo "$sql" | sq -header -html $db \
   2.155 -	| sed -e "s,\(<TR><TD>\)\([^	]*\)	\(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
   2.156 -    echo '</table>'
   2.157 +    echo "$sql" | lshandout_ulink_table
   2.158  }
   2.159  gethandout() {
   2.160    # $1=rowid of blog

     3.1 --- a/s4.cgi	Sat Nov 26 09:37:13 2016 +0859
     3.2 +++ b/s4.cgi	Sat Nov 26 09:38:11 2016 +0859
     3.3 @@ -71,10 +71,10 @@
     3.4      echo "Refresh: 0; $newurl"; echo
     3.5      exit 0
     3.6      ;;
     3.7 -  lshandout|gethandout)
     3.8 -    if [ x"$stage" = x"lshandout" ]; then
     3.9 -      contenttype; echo
    3.10 -    fi
    3.11 +  lshandout|lshandoutall|gethandout|gethandoutcsv)
    3.12 +    case "$stage" in
    3.13 +      lshandout*) contenttype; echo ;;
    3.14 +    esac
    3.15      rowid=${2%%[!A-Z0-9a-z_]*}
    3.16      if [ -z "$rowid" ]; then
    3.17        echo "話題番号が未指定です。" | html p