#
type cgiinit >/dev/null 2>&1 || . ./s4-funcs.sh

# Global error flags
BLOG_NOTMEM=1
BLOG_FROZEN=2
FROZEN_TAG='<span class="frozen">[凍結]</span>'

blog_genform() {
  #
  t=$1 
}

blog_writable() (
  # $1=articleid $2=user
  # Return: $?=0 - Writable
  # 	      =1 - NOT Writable because user is not a member
  # 	      =2 - NOT Writable because blog is frozen
  blogowner=`getvalbyid blog owner "$1"`
  state=`getvalbyid blog state "$1"`
  rc=0
  [ x"$blogowner" = x"$2" ] || isuser "$blogowner" || ismember "$2" "$blogowner" || rc=$((rc+$BLOG_NOTMEM))
  [ "$state" = "frozen" ] && rc=$((rc+$BLOG_FROZEN))
  return $rc
)
blog_readable() {
  # $1=articleid $2=user
  mode=`getgroupattr $grp regmode`
}
blog_notify_reply() (
  # $1=blogid $2=ReplyingUser $3=WrittenText $4(optional)=Action
  blogid="${1%%[!A-Z0-9a-z_]*}"
  blogowner=`getvalbyid blog owner "$blogid"`
  blogtitle=`getvalbyid blog title "$blogid"`
  blogurl="$urlbase?replyblog+$blogid"
  action=${4:-書き込み}
  mode=`getvalbyid blog notify "$blogid"`
  isgroup "$blogowner" && _isgroup=true || _isgroup=false
  ### EXCEPT=`sqlquote "$user"`	## User should receive to feal some annoyance
  case $mode in
    admin)
      if $_isgroup; then
	emails=`getgroupadminmails $blogowner`
      else
	emails=`collectemail $blogowner`
      fi
      notifyto=`getpar notifyto`
      if [ -n "$notifyto" ]; then
	emails=$emails" `email4groupbyuid \"$blogowner\" $notifyto`"
      fi
	;;
    no)		emails="" ;;
    *) team=`query "SELECT val FROM blog_s
		    WHERE id=(SELECT id FROM blog WHERE rowid=$blogid)
			   AND key='team';"`
       # team cannot get `getvalbyid blog team "$blogid"` because it's not
       # defined in blog.def.  Yes, it is Illegal USE!!
       emails=`TEAM=$team collectemail $blogowner` ;;
  esac
  ## 2017-0210 Respond to the direct reply mark such as: >#1234
  replymark=`echo "$3"|nkf -w -Z0|grep '^ *>#'`
  authgecos=`gecos $2`
  if [ -z "$4" -a -n "$replymark"  ]; then
    # If the action is new subscription($4="") and has ">#123" marks...
    ids=`echo "$replymark"|sed 's/[^#0-9]*#\([0-9]*\)[^#0-9]*/\1 /g'`
    ids=`echo $ids|tr -dc '[0-9 ]'|tr ' ' ','`
    # -> 123,345,347
    unames=`query "SELECT distinct author FROM article \
	WHERE rowid in ($ids)\
	AND blogid=(SELECT id FROM blog WHERE rowid=$blogid);"`
    if [ -n "$unames" ]; then
      e4g=$(if $_isgroup; then
	      email4group "$blogowner" $unames
	    else
	      for u in $unames; do
		collectemail $u
	      done
	    fi)
      emails=$emails" $e4g"
      for e in $unames; do
	g=`gecos $e`
	whom=$whom"${whom:+,}${g:-$e}さん"
      done
      action="${whom}への返信"
    fi
  else
    [ x"$2" = x"$blogowner" ] && return # If author=blogowner, unnecessary
  fi
  test -z "$emails" && return
  err notify: user=$user Admins=`getgroupadmins $blogowner` Mode=$mode Emails="[$emails]"
  SMAIL_TO="`echo "$blogowner" | nkf -jM | tr -d '\n'` readers <$admin>" \
	  smail "$emails" "${action}通知 $urlbase"<<EOF
[$blogtitle]板に${action}がありました。
※このメイルに返信しても通知者には伝わりません(管理者宛になる)。
場所: $blogurl (返信先)
所有: $blogowner
題目: $blogtitle
筆者: $authgecos
内容:
`echo "$3"|sed 's/^/> /'`
EOF
)

blog_showentry() {
  # $1=table $2=rowid
  # if [ -n "$2" ]; then
  #   if [ -n "$imgcached" ]; then
  #     bstmpdir=$tmpdir/$imgcached/$thumbxy
  #   else
  #     bstmpdir=$tmpd
  #     # tmpd=`mktempd`
  #     # tmpfiles=$tmpfiles" $tmpd"
  #   fi
  # fi
  td=`getcachedir "article/$2"`
  [ -d "$td" ] || mkdir -p $td
  tbl=${1%%[!A-Z0-9a-z_]*} rowid=${2%%[!A-Z0-9a-z_]*}
  err blow_showentry: rowid=$rowid, '$2'=$2 user=$user
  ts=${tbl}_s tm=${tbl}_m
  at=article as=article_s am=article_m
  serial=$(($(date +%s)-1420038000))s$$
  cannotread='<div class="relative"><img class="overlap" src="img/key.png" alt="(読み取り不可)"></div>'
  blog_writable $rowid $user
  rc=$?
  if [ $rc = 0 ]; then
    iswritable=true
    ismem=true
  else
    iswritable=false
    if [ $((rc & $BLOG_NOTMEM)) -gt 0 ]; then
      ismem=false
    else
      ismem=true
    fi
  fi
  # This function grasps blog entry definiton directly.
  # blog:   id
  # blog_s: title,ctime,heading
  # blog_m: *article

  blogowner=`getvalbyid blog owner "$2"`
  isgroup "$blogowner" && isgroup=true || isgroup=false
  isgrpadmin=false			# Reversed later (*1)

  # 2015-10-05 check readable
  if ! $iswritable; then
    # err blogowner=$blogowner
    if $isgroup; then
      regmode=`getgroupattr $blogowner regmode`
      # err regmode=$regmode
      if [ x"$regmode" = x"moderated" ]; then
	# if ! ismember $user $blogowner; then
	if ! $ismem; then
	  echo "加入してからどうぞ" | html p
	  return
	fi
      fi
    fi
  else	# if writable
    isgrpowner "$user" "$blogowner" && isgrpadmin=true	# (*1)
  fi
  case `getvalbyid blog notify "$rowid"` in # "all", "admin" or "no" (or NULL)
    admin)	notifyto=1 ;;
    *)		notifyto="" ;;
  esac
  case `getvalbyid blog mode "$rowid"` in
    *quiz*|*close*)	f_exclusive=1 ;;
    *)			f_exclusive='' ;;
  esac

  # err "SELECT id from $tbl where rowid=$rowid"
  id=`query "select id from $tbl where rowid=$rowid;"`
  #err id=$id
  #err "select val from $ts where key='title' and id='$id';"

  #(1)Display root article
  cat<<EOF
<form class="replyblog" action="$myname?replyblog+${rowid}#bottom" method="POST" enctype="multipart/form-data">
<table class="bloghead">
EOF

  href="<a href=\"?editheading+$rowid\" accesskey=\"e\" title=\"E\"> 編集 </a>"
  if $ismem; then
    case `getvalbyid blog mode $rowid` in
      *report*|*quiz*)
	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>'
  $isgrpadmin &&
      href5="<a href=\"?blogseen+$rowid\" accesskey=\"s\" title=\"S\"> 読刻</a>"
  quizmodefile=$td/quiz; rm -f "$quizmodefile"	# XXX: Global state

  query<<-EOF |
	SELECT coalesce((SELECT "yes" FROM blog
			 WHERE rowid=$rowid AND author='$user'),
			 ''),
	       max(CASE key WHEN 'ctime' THEN val END) ctime,
	       max(CASE key WHEN 'heading' THEN hex(val) END) heading,
	       CASE (SELECT val FROM $ts WHERE key="mode" AND id="$id")
	       WHEN 'report-closed' THEN 'レポート提出用(closed)'
	       WHEN 'report-open' THEN 'レポート提出用(open)'
	       WHEN 'quiz' THEN 'クイズ'
	       ELSE ''
	       END
	FROM $ts WHERE id='$id' GROUP BY id;
	EOF
  { IFS='|' read edit ctime hexhead blogtype
    cat<<-EOF
	<tr><td>${edit:+$href }$ctime $blogtype $href2$href3 $href4 $href5</td></tr>
	<tr class="preface${frozen_class:+ }$frozen_class">
	 <td>`echo "$hexhead"|unhexize|hreflink|minitbl`</td></tr>
	</table>
	EOF
    case "$blogtype" in
      "クイズ")
	echo "クイズモードは本人と管理者の書き込みのみが表示されます。"
	;;
    esac | html p 'class="warn"'
    echo '<table class="blog_replies">'
    if [ x"$blogtype" = x"クイズ" ]; then
      if $isgroup; then
	if ! isgrpowner "$user" "$blogowner"; then
	  qgrp=`sqlquote "$blogowner"`
	  cat<<-EOF > $quizmodefile
		AND  (author IN (SELECT user FROM grp_adm WHERE gname=$qgrp)
		      OR
		      author='$user')
		EOF
	fi
      else		# if user's blog
	if [ x"$user" != x"$blogowner" ]; then
	  cat<<-EOF > $quizmodefile
		AND author IN ('$blogowner', '$user')
		EOF
	fi
      fi
    fi
  }
  lkhome="<a href=\"$myname?home" lke='">'
  lkedit="<a href=\"$myname?editart"
  hlink="$myname?home" elink="$myname?editart"
  catlink="$myname?showattc+article_m"
  deficon="img/file-icon.png"
  # 2016-08-15 Newer flag introduced
  atime=`query "SELECT time FROM acclog
		WHERE tbl='blog' AND tblrowid=$rowid AND user='$user';"`
  iconcleaner=$tmpd/iconcleaner.$$
  [ -s $quizmodefile ] && cond_qz=`cat $quizmodefile`
  # *** DO NOT USE query(), use "sq $db" instead here ***
  # because the next block in pipe line uses query() repeatedly.
  sq $db<<EOF |
WITH a_s AS (
  SELECT id,
         max(CASE key WHEN 'ctime' THEN val END) TIME,
         max(CASE key WHEN 'text' THEN val END) TEXT
  FROM article_s
  GROUP by id
)
SELECT	a.id,
	CASE author
	WHEN '$user' THEN a.rowid||'+'||$rowid
	ELSE ''
	END edit,
	CASE -- 「通知送信」ボタンの有無
	WHEN '$notifyto' = '' THEN ''	-- 不要モードならなし
	WHEN '$user' = author THEN ''	-- 筆者自身ならなし
	ELSE "yes"
	END notify,
	(SELECT rowid FROM user WHERE name=author) user_rid,
	author,
	coalesce((SELECT val FROM user_s
			 WHERE name=author AND key='gecos'),
			author) uname,
	(SELECT val FROM user_s WHERE name=author AND key='$iconcachekey')
	icon,
	a.rowid,
	s.TIME,
	CASE WHEN s.TIME < '2019-05'
	     THEN printf('平成%d年%d月%d日%s',
			 substr(s.TIME, 1, 4)-1988,
			 substr(s.TIME, 6, 2),
			 substr(s.TIME, 9, 2),
			 substr(s.TIME, 12)
			 )
             WHEN s.TIME < '2020'
	     THEN printf('令和元年%d月%d日%s',
			 substr(s.TIME, 6, 2),
			 substr(s.TIME, 9, 2),
			 substr(s.TIME, 12))
             WHEN s.TIME < '2050'
	     THEN printf('令和%d年%d月%d日%s',
			 substr(s.TIME, 1, 4)-2018,
			 substr(s.TIME, 6, 2),
			 substr(s.TIME, 9, 2),
			 substr(s.TIME, 12))
             ELSE s.TIME
	     END reki,
        CASE WHEN s.TIME > '$atime' THEN 'new' ELSE '' END newer,
	hex(s.TEXT),
	CASE -- File Accessibility to attached file
	WHEN '$f_exclusive' = ''    THEN ''
	WHEN '$isgrpadmin' = 'true' THEN ''
	WHEN '$user' = author       THEN ''
	ELSE				 'Unreadable'
	END cannotread,
	(SELECT group_concat(rowid||':'||length(bin)||':'||hex(val), ' ')
	 FROM article_m
	 WHERE id=a.id AND key='image') imxgids
FROM (select rowid,id,author from article
      where blogid in
      (select id from blog where rowid=$rowid)
      $cond_qz) a
  LEFT JOIN
     a_s s
  ON a.id=s.id;
EOF
  while IFS='|' read id edit notify uid author uname icon aid \
	   tm reki new hte fa imgids
  do
    cachefile="$td/$id.row.html"
    stampfile="$td/$id.row.stamp"
    editlink="${edit:+<a href="$elink+$edit">編集</a> }"
    nt="<label style=\"font-size: 70%;\"><input type=\"checkbox\"\
	 name=\"notifyto\" value=\"$uid\">返信通知送信</label>"
    # fa is file accessibility flag # err "----r=$aid fa=[$fa]----"

    # First, check the availability of user-icon.
    # If not existent, clear and reset row cache by rm $stampfile
    if [ ! -s "$icon" ]; then
      rm -f "$stampfile"; unset stampfile
    fi
    if test -s "$stampfile" &&
	  test -s "$cachefile" &&
	  { ts=`cat "$stampfile"`; test -n "$ts"; } &&
	  test "$ts" '>' "$tm" &&		# Cache timestamp is newer
	  test "$stampfile" -nt "$icon"; then	# UserIcon is older
       : Nothing to do
    else
      {		######## New ROW creation begins here ######## >$cachefile
	tdcls="__NEWCLS__repatt"
	if [ -s "$icon" ]; then
	  icfn=`echo "$icon"|htmlescape`
	  picon="<p class=\"proficon\"><a href=\"$hlink+$uid\" title=\"${author%@*}\"><img src=\"$icfn\"></a></p>"
	else
	  echo "DELETE FROM user_s WHERE key='$iconcachekey' AND
	       val=`sqlquotestr \"$icon\"`;" >> $iconcleaner
	  picon=""
	fi

	cat<<EOF
<tr id="$id">
<td class="$tdcls">${picon}__EDIT__<a href="#$aid">#$aid</a>
<a href="$hlink+$uid" title="${author%@*}">$uname</a>
<span title="$tm">${reki:-$tm}</span>
<__NOTIFY__></td>
EOF
	echo -n "<td id=\"$aid\" class=\"repl\">"
	echo "$hte"|unhexize|htmlescape|hreflink|minitbl
	usecache='' tsfile=$td/$id.stamp
	for i in $imgids; do
	  mrid=${i%%:*}; i=${i#*:}; sz=`size_h ${i%%:*}`
	  fn=`echo "${i#*:}"|unhexize`
	  fnb=$fn"(${sz})"
	  case "$fn" in
	    *.[Pp][Nn][Gg]|*.[Jj][Pp][Gg]|*.[Jj][Pp][Ee][Gg]|*.[GgTt][Ii][Ff])
	      #  fmt=${fn##*.}	# convert - jpg:- is slow...why
	      case "$fn" in
		*.[Pp][Nn][Gg])	fmt=png ;;
		*.[Gg][Ii][Ff])	fmt=gif ;;
		*)		fmt=jpeg ;;
	      esac
	      outfile=$td/$mrid-${fn%.*}.$fmt
	      #err fn=$fn outfile=$outfile
	      #err "usecache=$usecache `ls -l $outfile`" 
	      #err tm=$tm
	      #err tsfile=$tsfile=`cat $tsfile`
	      if [ -s "$outfile" ] &&	# $outfile should be > 0
		     { [ "$usecache" ] ||	# And usecache flag is true, or...
			   { [ -s "$tsfile" ] && [ x"`cat $tsfile`" = x"$tm" ]
			   };}; then
		usecache=1		# Set usecache flag on
		cat<<-EOF
		<a href="$catlink+$mrid"><img src="$outfile">
		$fnb</a>
		EOF
		# !!NOTE!! Create row stamp ONLY WHEN imgcache is active
	      else
		query "SELECT hex(bin) FROM article_m WHERE rowid=$mrid;" \
		    | unhexize \
		    | convert -define ${fmt}:size=100x100 -resize 100x100'>' \
			      - ${fmt}:- \
		    | tee "$outfile" \
		    | hexize \
		    | sed -e 's/\(..\)/%\1/g' \
	    		  -e "s|^|<a href=\"$catlink+$mrid\"><img src=\"data:image/$fmt,|" \
			  -e "s|\$|\">$fnb</a>|"
		unset stampfile # img data stream is not suitable to cache
		echo $tm > $tsfile
	      fi
	      ;;
	    *)
	      echo "<__UNREADABLE__><a href=\"$catlink+$mrid\"><img src=\"$deficon\">$fnb</a>"
	      ;;
	  esac
	done
	echo "</td></tr>"
      } > "$cachefile"	######## New ROW Creation Ends here ########
      test -n "$stampfile" && date "+%F %T" > $stampfile
    fi
    # Printing a cached row
    sed -e "/^<td class=/s/__NEWCLS__/$new${new:+ }/" \
	-e "/^<td class=/s,__EDIT__,$editlink," \
	-e "/^<__NOTIFY__>/s,,${notify:+$nt}," \
	-e "/<__UNREADABLE__>/s,,${fa:+$cannotread}," \
	$cachefile
  done
  
  help="=== コメントに使用できる特殊記法 ===
行頭に href=URL でURLへのリンク
行頭に iframe=URL でURL先を開く iframe
[[#記事番号]] でs4内の記事番号に飛ぶリンク
[[#検索キーワード]] でs4内の記事検索(記号はいくつか使えない)
[[URL]] でURLへのリンク、        [[URL|文字列]]でアンカー文字列指定
{{画像URL}} でインライン画像、   {{画像URL|幅}} でピクセル幅指定
{{{URL}}} でURL先を開く iframe、 {{{URL|高さ}}} ピクセル高さ指定
行頭: ## 大見出し, ### 中見出し, #### 小見出し
行末の2連続スペースで強制改行(<br>)
|*見出し列|列2|列3…  と行頭から始まる縦棒区切り行を続けて表
' *語群* ' で強調(両側の空白必要、** でもっと強調。*の代わりに _ でも可)
- [ ] と - [x] でチェックボックス"
  touchhelp="${touchpanel:+<p class=\"help\">$help</p>}"
  filehelp="《添付の注意》
$file_accept_help"
  textform='<div class="fold">
<input type="checkbox" id="cmt" checked><label
 accesskey="c" title="C" for="cmt">コメントする</label><div>
<table class="b">
<tr><td><textarea name="text" cols="72" rows="4" title="'"$help"'">
</textarea>'"$touchhelp</td></tr>
<tr><td>添付ファイル(${filesize_max_MB}以下):"'
<input type="file" name="image"'" $file_accept title=\"$filehelp\" multiple></td></tr>"'
</table>
<input type="submit" value="送信">
<input type="reset" value="リセット"></div></div>
'
  cat<<-EOF
	</table> <!-- end of s4-blog:blog_showentry() main table -->
	<p class="update_link"><a
	 href="?reload/$rowid" accesskey="r" title="R">再読込</a> / <a
	 href="#title" id="bottom" accesskey="t" title="T">先頭へ</a></p>
	EOF
  $iswritable && cat<<-EOF
	<div class="blogcomment">
	<input type="hidden" name="blogid" value="$id">
	<input type="hidden" name="id" value="`genserial`">
	<input type="hidden" name="stage" value="replyblog">
	$textform
	</div>
	</form> <!-- End of s4-blog:blog_showentry() main form -->
	EOF
  # Clean up orphaned icon cache
  [ -s $iconcleaner ] && query ".read '$iconcleaner'"
  # Record access log
  acclog blog $rowid
}

lshandout() {
  # $1=rowid of blog
  blog_writable $1 $user
  rc=$?		# =0: writable, $BLOG_NOTMEM bit set => not member
  if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
    echo "メンバー以外は利用できません。" | html p; return
  fi
  time=`getvalbyid blog ctime $1|colrm 11`
  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_="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"
}
gethandoutcsv2() {
  # contenttype; echo
  SQL=$(cat<<-EOF
	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 "post"
	 FROM count_list cl JOIN gecoses g ON cl.author=g.name;
	EOF
     )  gethandoutcsv "$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
  blog_writable $1 $user
  rc=$?		# =0: writable, $BLOG_NOTMEM bit set => not member
  if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; 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,
		max(CASE key WHEN 'ctime' THEN val END) ctime
 	 FROM blog NATURAL JOIN blog_s
 	 GROUP BY id
	)
	SELECT id, brid, title, ctime FROM blog_owner_mode
	/* WHERE owner=$qowner AND mode LIKE '%report%'; */
	WHERE owner=$qowner
		AND
	      (mode LIKE '%report%' OR mode LIKE '%quiz%');
	 /* ↑これでレポート形式の blogid 一覧を得る */
	EOF
  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
  if [ -z "$SQL" ]; then
    bridlist=`query "SELECT brid FROM report_type_blogs;"`
    for brid in $bridlist; do	# Skip this loop if $SQL set
      brid=$(($brid + 0))		# Ensure to be a number
      [ $brid = 0 ] && continue
      time=`getvalbyid blog ctime $brid|colrm 11`
      title=`getvalbyid blog title $brid|htmlescape`
      state=`getvalbyid blog state $brid|htmlescape`
      tt="handout_$brid"
      [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen=""
      if [ -z "$CATCSV" ]; then
	echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a>$frozen</h2>"
	lshandoutsub "$owner" $brid "$tt"
      else
	lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
      fi
      tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
    done
  fi
  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>"
    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
    fn=report-count.csv
    printf 'Content-Disposition: filename="%s"\n' "$fn"
    outfile=$tmpd/out-$$.csv
    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 &optional $3=temp_table name
  qgname=`sqlquote "$1"`
  if isgroup "$1"; then
    sample="(select user from grp_mem where gname=$qgname)"
  else
    sample="(select distinct author as user from arts)"
    echo "(集計は板への投稿者のみ)" | html p
  fi
  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 'メンバー',\
	     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)\
		 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;\
	\
	SELECT * FROM $tmpname;"
    err ishandoutsub: sql="$sql"
    echo "$sql" | lshandout_ulink_table
}
gethandout() {
  # $1=rowid of blog
  rid=`numericalize "$1"`
  blog_writable $rid $user
  rc=$?		# =0: writable, $BLOG_NOTMEM bit set => not member
  if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
    echo "メンバー以外は利用できません。" | html p; return
  fi
  i=0
  bd=$tmpd/archive.$$
  mkdir $bd
  query "select m.rowid,author,m.val from article a join article_m m\
	 on a.id=m.id where blogid=(select id from blog where rowid=$rid)\
	 and m.key in ('image', 'document', 'binary');" \
      | while IFS='|' read rowid author filename; do
	  err isfilereadable $user article_m $rowid
	  isfilereadable $user article_m $rowid || continue
	  err ok
	  i=$((i+1))
	  dir=`printf $bd/%03d $i`
	  mkdir $dir
	  query "select quote(bin) from article_m where rowid=$rowid;" \
	      | unhexize > $dir/$filename
	done
  if [ ! -d $bd/001 ]; then
    contenttype; echo
    echo "取得できるファイルがありませんでした。" | html p
    return
  fi
  (cd $bd
   ## err cdto$bd; (pwd; ls -lFa) 1>&3
   tar zcf .archive.tar.gz * && mv .archive.tar.gz archive.tar.gz
   err Creating tar archive "`ls -l archive.tar.gz`"
  )
  arc=$bd/archive.tar.gz
  echo "Content-type: application/x-gzip"
  echo "Content-Length: `cat $arc|wc -c`"
  echo "Content-Disposition: filename=\"archive.tar.gz\""
  echo
  cat $arc
}
blogseen() {	# $1 = blogid
  blogid=${1%%[!0-9]*}
  if [ -z "$blogid" ]; then
    echo "Invalid blog id" | html p; exit
  fi
  blog_writable "$blogid" "$user"
  rc=$?		# =0: writable, $BLOG_NOTMEM bit set => not member
  if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
    echo "メンバー以外は利用できません。" | html p; return
  fi
  owner=`getvalbyid blog owner $rowid`
  qowner=`sqlquotestr "$owner"`
  grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"`
  ge=`gecos "$owner" | htmlescape`
  title=`getvalbyid blog title $rowid | htmlescape`
  h1="アクセス時刻"
  link2board="<a href=\"?replyblog+$rowid\">$title</a>"
  link2group="<a href=\"?grp+$grprowid\">$ge</a>"
  _m4 -D_TITLE_="$h1" $layout/html.m4.html
  echo "$h1" | html h1
  echo "[$link2board]@$link2group" | html h2
  warn=' class="warn"'
  cat <<-EOF
	<table class="b">
	<tr><th>メンバー</th><th>uname</th><th>最終閲覧時刻</th></tr>
	EOF
  query <<-EOF |
	WITH grpmem as (
	 SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos
	 FROM grp_mem
	 WHERE gname=(SELECT val FROM blog_s
	              WHERE id=(select id from blog where rowid=$blogid)
		      AND key='owner')
	), acctime AS (
	 SELECT user, max(time) atime
	 FROM tblaccesses
	 WHERE tbl='blog' AND tblrowid=$blogid
	 GROUP BY user
	)
	SELECT g.user,
	       (SELECT rowid FROM user u WHERE u.name=g.user),
               hex(gecos),
               atime
	FROM grpmem g LEFT JOIN acctime t
	ON   g.user = t.user
	GROUP BY g.user
	ORDER BY atime DESC;
	EOF
  while IFS='|' read u uid hexge time; do
    td=${time:+"<td>"}			# If the variable time is set, td=<td>
    td=${td:-"<td$warn>"}		# else td=<td class="warn">
    cat <<-EOF
	<tr>
	 <td><a href="?home+$uid">`echo "$hexge"|unhexize|htmlescape`</a></td>
	 <td>`echo ${u%%@*}|htmlescape`</td>
	 $td${time:----}</td></tr>
	EOF
  done
  cat <<-EOF
	</table>
	<p><a href="?replyblog+$rowid">[$title]に戻る</a></p>
	</html>
	EOF
}
lsmyfile() {	# $1(optional)=SortBy
  case "$1" in
    ""|CTIME-DESC)
      		by="CTIME"	ord="DESC" ;;
    CTIME*)	by="CTIME"	;;
    FILE*)	by="FILE"	;;
    OWNER*)	by="OWNER"	;;
    TITLE*)	by="TITLE"	;;
  esac
  case "$1" in
    *DESC)	ord="DESC" ;;
  esac
  case "$ord" in
    DESC)	lkod="" 	jord="降順" ;;
    *)		lkod="-DESC"	jord="昇順" ;;
  esac
  sql="select m.val||'/'||m.rowid FILE,
	      coalesce(
		case when (select name from user where name=bs.owner)
			 is not null
		     then (select val from user_s where name=bs.owner
				and key='gecos')
		     when (select gname from grp where gname=bs.owner)
			 is not null
		     then (select val from grp_s where gname=bs.owner
				and key='gecos')
		     else
			null
		 end,
		 bs.owner
		) OWNER,
	      a_s.val CTIME,
	      ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
	 from (select rowid,id,val from article_m where id
		 in (select id from article where author='$user')
		and type like 'file:%')
	m left join article a on m.id=a.id
	  left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
	  left join (select id,
			max(case key when 'owner' then val end) as owner,
		        max(case key when 'title' then val end) as title
			from blog_s group by id)
		     bs on a.blogid=bs.id
	  left join blog b on bs.id=b.id
	  where m.val is not null order by $by $ord;"
  err lshandoutbyauthor: sql=`echo "$sql"`
  title="個人提出ファイル"
  _m4 -D_TITLE_=$title $layout/html.m4.html
  hra="<a href=\"?lsmyfile+"
  hrb="<a href=\"?showattc+article_m+"
  hrc="<a href=\"?replyblog+"
  (echo '<table class="b">'
   echo "$sql"|sq -html -header $db ) \
      | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
	    -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
	    -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
      | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
	    -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
  echo '</table>'
}
searchart() {
  kwd=`getpar kwd|nkf -wZ1`		# Convert Zenkaku-SPC to ASCII-SPC
  bloglist=`getpar bloglist|sed 's/[^0-9,]//g'`
  kwdgrp=""
  authcond=""
  if [ -z "$kwd" ]; then
    echo "検索語を指定してください" | html p; return
  fi
  if logstart "$searchlog"; then
    { echo "kwd=$kwd"
      test -n "$bloglist" && echo "bloglist=$bloglist"
    } >> $searchlog
    logend "$searchlog"
  fi
  if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
    # Like '#1234', assume as artID
    rowid=$((${kwd#\#} + 0))	# Force to be a number
    kc="ar.rowid = $rowid"
  else
    for k in `echo "$kwd" | sed "s/'/''/g"`; do		# With wrap quotes
      ctime=""
      if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
	# '@<2016-10-10'	-> ctime < '2016-10-10'
	# '@>=2016-10-10'	-> ctime >= '2016-10-10'
	# '@2016-10-10'		-> ctime GLOB '@2016-10-10'
	k=${k#@}
	case "$k" in
	  [\<\>]*)	op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
	  *)	op='GLOB'; ctime="${k##*[><= ]}*" ;;
	esac
	kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
	# Not sure GROUP BY a.blogid is comfortable for searchers...?
	##### kwdgrp=" GROUP BY a.blogid"   ## Add this to lessen results
      elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
	ctime=`date +%F`
      elif n=`expr x"$k" : 'x@\([0-9]*\)days*'` >/dev/null >&2; then
	ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
      elif [ x"$k" = x"@week" ]; then
	ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
      elif n=`expr x"$k" : 'x@\([0-9]*\)weeks*'` >/dev/null >&2; then
	n=$((n * 7))
	ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
      elif [ x"$k" = x"@month" ]; then
	ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
      elif n=`expr x"$k" : 'x@\([0-9]*\)months*'` >/dev/null >&2; then
	ctime=`query "SELECT datetime('now', 'localtime', '-$n month');"`
      elif [ x"$k" = x"@year" ]; then
	ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
      elif n=`expr x"$k" : 'x@\([0-9]*\)years*'` >/dev/null >&2; then
	ctime=`query "SELECT datetime('now', 'localtime', '-$n year');"`
      fi
      if [ -n "$ctime" ]; then
	kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
      else
	e=""
	case "$k" in
	  *${likeesc}*) e="" ;;		# Giving up char-escaping
	  *%*|*_*) k=`echo "$k"|sed "s/\([%_]\)/${likeesc}\1/g"`
	       e=" ESCAPE '$likeesc'" ;;
	esac
	kc=$kc${kc:+" AND "}"content LIKE '%$k%'$e"
      fi
    done
  fi
  kwd=`echo "$kwd"|htmlescape`
  owner=`getpar owner`
  owner=${owner:-$1}
  msg=""
  if [ -n "$owner" ]; then
    cond="where key='owner' and val='$owner'"
    if isuser $owner; then
      msg="(`linkhome $owner` さんの記録から)"
    else
      linkhome $owner 1>&3
      msg="(`linkhome $owner` グループから)"
    fi
  elif { author=`getpar author`; test -n "$author"; }; then
    atptn=`sqlquotestr $author`
    #kc="$kc${kc:+ AND }author=$atptn"
    authcond="WHERE author=$atptn"
    if isuser $author; then
      msg="(`linkhome $author` さんの書き込みから)"
    fi
  fi
  if [ -n "$bloglist" ]; then
    blogcond="AND bl.rid IN ($bloglist)"
  fi
     
  sf=`search_form "$search_form_args" "$kwd" | sed '1d;$d'` # rm <div></div>
  echo "$sf" | sed -e "/POST SENTENCE/s/.*/__PS__/" -e "/EOF/q" \
    | _m4 -D__PS__="による検索結果$msg"
  echo "(上記入力窓で再検索すると下記の掲示板のみに絞って再検索します)" \
    | html p 'class="small"'
  # article_s:	id=article-id, key='text', val='TEXT'
  # article:	id=article-id, blogid=blogkd
  # blog:		id=blog-id, author=LeaderAuthor
  # blog_s:		id=blog-id, key='title', val='BLOG-TITLE'
  # WANT: blog-ROWid,article-id,val(TEXT)
  sql2="`sql4readableblogs`		-- Extract user-readable blogs
-- 0.3sec
WITH artsm AS (
 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
 FROM article a
   LEFT JOIN
     (SELECT ars.id, ctime, text, coalesce(files, '') files
      FROM (SELECT id,
		   max(CASE key WHEN 'ctime' THEN val END) ctime,
		   max(CASE key WHEN 'text' THEN val END) text
	    FROM   article_s
	    GROUP BY id) ars
	LEFT JOIN
	  (SELECT id, group_concat(val) files
	   FROM article_m
	   WHERE type LIKE 'file:%'
	   GROUP BY id) arm
	ON ars.id=arm.id
     ) ar
   ON a.id=ar.id
), ar AS (
 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
 FROM	article a JOIN artsm ON a.id=artsm.id
 $authcond
), bl AS (
 SELECT blg.rid, blg.*, blog_s.val TITLE
 FROM	readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
)
SELECT	bl.rid||'#'||ar.id '',
	bl.title TITLE,
	(SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
	substr(ctime, 0, 11) DATE,
	substr(content, 0, 78) TEXT
FROM ar JOIN bl
	ON ar.blogid=bl.id
WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) $blogcond
ORDER by DATE DESC, TITLE, ctime;"
  sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
 href=\"?replyblog+\1\">VIEW</a></TD>,"
  # echo "$sql2" > tmp/sql.out
  result=$tmpd/result.$$
  cat<<EOF
<table class="b searchart">
`sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
</table>
EOF
  if [ -s "$result" ]; then
    found=$((`grep "^<TR><TD>" $result | wc -l` + 0))	# Cast to INT
    one=${found%1}
    echo "$found match${one:+es} found"
    # <a href="?replyblog+39#12345">VIEW</a>
    # -> 39,49,55, -> 39,49,55
    # -> <input type="hidden" name="bloglist" value="39,49,55">
    sed -n "/.*href=.*replyblog\+\([0-9][0-9]*\).*/s//\1/p" "$result" \
      | sort | uniq | tr '\n' ',' \
      | sed -e 's/,$//' \
	    -e 's/^/<input type="hidden" name="bloglist" value="/' \
	    -e 's/$/">/'
  else
    echo orz...
  fi
  echo "$sf" | sed "1,/-- EOF/d"		# Close <form>
}
listblog() (
  # $1={user,group}
  qow=`sqlquote $1`
  cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
  cgi_form searchart<<EOF
<label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
`cgi_hidden owner $user`
EOF
  DT_CHLD=article:blogid DT_QOWNER=$qow \
	 dumptable html blog 'ctime title heading' "$cond"
)

blog_addentry() {
  # $1=GRPname(if it is a group)
  grprowid=`numericalize $1`
  rowid=`getpar rowid`
  ## err blog_addentry0: rowid=$rowid
  if [ -n "$grprowid" ]; then
    owner=`getgroupbyid $grprowid`
  else
    owner=`getpar owner`
  fi
  err blog-add: \$1=$grprowid rowid=$rowid owner=$owner
  if isgroup "$owner"; then
    groupmode=1 listing=$owner guide="[${owner}]" GF_OWNER=$owner
  else
    usermode=1 listing=$user guide="[個人]"
  fi

  if [ -n "`getpar title`" ]; then
    if [ "$usermode" ]; then
      err usermode: user=$user owner=$owner
      if [ x"$user" != x"$owner" ]; then
	echo "他人の日記は書けません" | html p
	return 2
      fi
    elif [ "$groupmode" ]; then	# if write to group log
      grp=$owner #\`getpar grp\`
      err ismember: $user $grp
      if ! ismember "$user" "$grp"; then
	echo "(話題作成はこのグループに加入してから)" | html p
	return 3
      fi
    fi
    par2table $formdir/blog.def
    serial=`getpar serial`
    ## err SERIAL: $serial ROWID=$rowid listing=$listing
    id=""
    if [ -n "$rowid" ]; then
      # Here, id becomes NULL when removal of entries at par2table
      id=`query "select rowid from blog where rowid=$rowid;"`
    elif [ -n "$serial" ]; then
      # If new blog leader created, traverse to its head.
      id=`query "select rowid from blog where id='$serial';"`
      ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
    fi
    if [ -n "$id" ]; then
      ## If new aritcle is entered, JUMP to blog_reply
      blog_reply $id
      return
    fi
  fi
  echo "${guide}新規話題作成"	> $tmpd/title.$$
  listblog $listing		> $tmpd/listblog.$$
  genform $formdir/blog.def \
      | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
	    -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
	    -D_DUMPHEAD_="これまでの蓄積" \
	    -D_FORM_="syscmd(\`cat')" \
	    -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
	    $layout/html.m4.html \
	    $layout/form+dump-whead.m4.html
}

blog_reply() {		# Posting to blog article
  rowid=`numericalize $1`	# Ensure (already purified in s4.cgi)

  if [ -z "$rowid" ]; then
    echo "表示する日記番号が未指定です。" | html p
    return
  fi
  title=`getvalbyid blog title $rowid`
  owner=`getvalbyid blog owner $rowid`
  qowner=`sqlquotestr "$owner"`
  if [ -z "$title" ]; then
    echo "日記番号指定が無効です。" | html p
    return
  fi
  blog_writable $rowid $user; rc=$?
  if [ $rc = 0 ]; then
    iswritable=true
  else
    iswritable=false
    if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
      isfrozen=true
      frozen_class='frozen"'
      frozen_flag=$FROZEN_TAG
    fi
  fi
  if isuser "$owner"; then
    subtitle="`gecos $owner` さんの話題"
  else
    grprowid=`query "select rowid from grp where gname=$qowner;"`
    subtitle="グループ
     <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$owner</a> での話題
    	`query \"SELECT printf('(チーム:%s)', val)\
		 FROM blog_s
		 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
		       AND key='team';
	 \"|htmlescape`"
    memclass=`grp_getbodyclass "$owner"`
  fi

  text=`getpar text`
  if [ -n "$text" ]; then
    if $iswritable; then
      par2table $formdir/article.def
      st=$?
      case $st in
	0|4)
	  [ "$st" = "4" ] && act="書込削除"
	  blog_notify_reply $rowid $user "$text" $act
	if [ -n "$grprowid" ]; then
	  qgrp=$(sqlquote "$owner")
	  dbsetbyid grp $owner wtime "`date '+%F %T'`"
	else
	  dbsetbyid user "$user" wtime "`date '+%F %T'`"
	fi
	;;
      esac
    else
      if $isfrozen; then
	title="$title(凍結板につき書き込み不可)"
      else
	title="$title(加入してないので書き込み不可)"
      fi
    fi
  fi
  def=$formdir/article.def
  echo "$title" > $tmpd/title.$$
  echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
  ${BLOG_SHOW:-blog_showentry} blog $rowid \
      | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
	    -D_BODYCLASS_=general"${memclass:+ $memclass}" \
	    -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
	    -D_FORM_='' \
	    -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
	    $layout/html.m4.html $layout/form+dump-whead.m4.html
}

blog_reply_article() {		# Direct link to article in some blog
  arid=${1:-0}			# Already sanitized to digits
  brid=`query "SELECT rowid FROM blog WHERE \
	       id=(SELECT blogid FROM article WHERE rowid=$arid);"`
  if [ -n "$brid" ]; then
    newurl="?replyblog+$brid#$arid"
    echo "Refresh: 0; $newurl"; echo
    exit 0
  else
    contenttype; echo
    echo "無効な記事番号です." | html p
  fi
}