#
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_getteam() {
  # $1=rowid of blog
  blogid="${1%%[!A-Z0-9a-z_]*}"
  # team cannot get `getvalbyid blog team "$blogid"` because it's not
  # defined in blog.def.  Yes, it is Illegal USE!!
  query "SELECT val FROM blog_s
		    WHERE id=(SELECT id FROM blog WHERE rowid=$blogid)
			  AND key='team';"
}
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=`blog_getteam "$blogid"`
       # team cannot get by `getvalbyid blog team "$blogid"`
       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]"
  quotedowner=`echo $blogowner | nkf -jM | tr -d '\n"'`
  MAIL_FROM=$noreply_from \
  SMAIL_TO="\"$quotedowner\" readers <$noreply>" \
	  smail "$emails" "${action}通知 $urlbase"<<EOF
[$blogtitle]板に${action}がありました。
※※※このメイルには返信できません(返信は次のURLへ)※※※
場所: $blogurl (返信先)
所有: $blogowner
題目: $blogtitle
筆者: $authgecos
内容:
`echo "$3"|sed 's/^/> /'`

※※このメイルに返信しても通知者には伝わりません。
※※上記URLから${S4NAME:-s4}掲示板に書き込んでください。
EOF
)

blog_showentry() {
  # $1=table $2=rowid $3(optional)=control-sequence
  # if [ -n "$2" ]; then
  #   if [ -n "$imgcached" ]; then
  #     bstmpdir=$tmpdir/$imgcached/$thumbxy
  #   else
  #     bstmpdir=$tmpd
  #     # tmpd=`mktempd`
  #     # tmpfiles=$tmpfiles" $tmpd"
  #   fi
  # fi
  control=$3
  td=`getcachedir "article/$2"`
  [ -d "$td" ] || mkdir -p $td
  tbl=${1%%[!A-Z0-9a-z_]*} rowid=${2%%[!A-Z0-9a-z_]*}
  err blog_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
  isgrpowner "$user" "$blogowner" && isgrpadmin=true

  # 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
  fi
  blog_notify=`getvalbyid blog notify "$rowid"`
  blog_team=`blog_getteam "$rowid"`
  blog_mode=`getvalbyid blog mode "$rowid"`
  case "$blog_notify" in # "all", "admin" or "no" (or NULL)
    admin)	notifyto=adm ;;
    *)		notifyto="" ;;
  esac
  case $blog_mode in
    *quiz*|*close*)
      f_exclusive=1
      if $isgroup; then
	qgrp=`sqlquote "$blogowner"`
	if $isgrpadmin; then
	  F_UNREADABLE="''"
	else
	  if [ x"$blog_mode" = x"quiz" ]; then
	    F_UNREADABLE="CASE
		WHEN author IN (SELECT user FROM grp_adm WHERE gname=$qgrp)
		THEN ''
		WHEN author = '$user'
		THEN ''
		ELSE 'Unreadable'
		END"
	  else
	    F_UNREADABLE="'Unreadable'"
	  fi
	fi
      else		# User blog
	if [ x"$blog_mode" = x"quiz" ]; then
	  F_UNREADABLE="CASE
		WHEN author = '$blogowner'
		THEN '' ELSE 'Unreadable'
		END"
	else
	  F_UNREADABLE="'Unreadable'"
	fi
      fi
      ;;
    *)	f_exclusive=''
	F_UNREADABLE="''"
	;;
  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=\"Shortcut: E${nl}Edit\"> 編集 </a>"
  if $ismem; then
    case $blog_mode in
      *report*|*quiz*|*enquete*)
	href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"Shortcut: L${nl}List Handouts\"> 提出状況 </a>"
	case "$isgrpadmin$blog_mode" in
	  false*closed*|false*quiz|false*enquete*) ;;
	  *)
	    href3="(<a href=\"?gethandout+$rowid\" accesskey=\"f\" title=\"Shortcut: F${nl}File Retrieval\">ファイル取得</a>)"
	    ;;
	esac
	;;
    esac
  fi
  href4="<a href=\"#bottom\" accesskey=\"b\" title=\"Shortcut: B${nl}to the Bottom\"> 末尾へ</a>"
  $isgrpadmin &&
      href5="<a href=\"?blogseen+$rowid\" accesskey=\"s\" title=\"Shortcut: S${nl}State of Accesses\"> 読刻</a>"
  quizmodefile=$tmpd/quiz; rm -f "$quizmodefile"	# XXX: Global state
  midfile=$tmpd/midfile

  query<<-EOF > $midfile
	SELECT coalesce((SELECT "yes" FROM blog
			-- GrpAdmin CAN EDIT heading since 2019-08-15
			 WHERE '$isgrpadmin' = 'true'
			       OR (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 'クイズ'
	       WHEN 'enquete' THEN '集計'
	       ELSE ''
	       END
	FROM $ts WHERE id='$id' GROUP BY id;
	EOF
  if test -s $midfile && IFS='|' read edit ctime hexhead blogtype < $midfile
  then
    cat<<-EOF
	<tr><td>${edit:+$href }$ctime $blogtype $href2${edit:+$href3} $href4 $href5</td></tr>
	<tr class="preface${frozen_class:+ }$frozen_class">
	 <td>`echo "$hexhead"|unhexize|htmlescape|hreflink|minitbl`</td></tr>
	</table>
	EOF
    case "$blogtype" in
      "クイズ"|"XXXX集計")
	echo "${blogtype}モードは本人と管理者の書き込みのみが表示されます。"
	;;
    esac | html p 'class="warn"'

    if [ x"$blogtype" = x"クイズ" -o x"$blogtype" = x"XXXX集計" ]; then
      if $isgroup; then
	# Failsafe to query timeout
	qgrp=`sqlquote "$blogowner"`
	cat<<-EOF > $quizmodefile
		AND  (author IN (SELECT user FROM grp_adm WHERE gname=$qgrp)
		      OR
		      author='$user')
		EOF
	if $isgrpadmin; then # 
	  : > $quizmodefile
	fi
      else		# if user-blog
	if [ x"$user" != x"$blogowner" ]; then
	  cat<<-EOF > $quizmodefile
		AND author IN ('$blogowner', '$user')
		EOF
	fi
      fi
    fi
  else		# Cannot read SQL output
    echo "時間をおいて繋いでください(Please visit later)." | html p
    return
  fi
  ## Parse control sequence
  nlimit=$listartlimit
  case "$control" in
    n:[Aa][Ll][Ll])
	unset nlimit ;;
    n:*)
	nlimit=${control##*:}
	nlimit=${nlimit%%[!A-Z0-9a-z_]*}
	;;
  esac
  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.
  ###### TEST: 2020-04-23 Use intermediate file to shorten duration of db-lock
  ###### sq $db<<EOF |
  query <<EOF > $midfile
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),

	$F_UNREADABLE 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
  if [ $? -ne 0 -a ! -s $midfile ]; then
    echo "時間をおいてください(Visit later please)." | html p
    return
  fi
  echo '<table class="blog_replies"> <!-- blog:blog_showentry() main table -->'
  # If, nLimit = 50
  # show article:1, hide(2, 3), show(4, ...)
  # Therefore hide 2 or more article when narts>53
  narts=`wc -l < $midfile`
  if [ -n "$nlimit" -a "$narts" -gt "$((nlimit+2))" ]; then
    newtop=`cat -n $midfile | grep "|new|" | head -1 | cut -f1`
    if [ -n "$newtop" ]; then
      afternew=$((narts-newtop+1))
      [ $afternew -gt $((nlimit+2)) ] && nlimit=$((afternew+0))
      err Newtop=$newtop lines=$narts afternew=$afternew nlim=$nilmit
    fi
  fi
  if [ $nlimit -lt $((narts-2)) ]; then
    n=0
    omitline=$td/omitline
    #CAT="tail -n $nlimit"
    CAT=cat
    limitedmsg="<span class=\"warn\">※最新${nlimit}件のみの表示※</span>"
    showalllink="<a title=\"Show All\" href=\"?replyblog+$rowid+n:all\">全件表示</a>"
    cat<<-EOF > $omitline
	<tr class="warn">
	<th>:<br>$limitedmsg<br>($((narts-$nlimit-1))件省略)<br>:</th>
	<th>$showalllink</td></th>
	EOF
  else
    CAT=cat
  fi
  # Start blog_replies table
  $CAT $midfile |
  while IFS='|' read id edit notify uid author uname icon aid \
	   tm reki new hte fa imgids
  do
    if [ -n "$omitline" ]; then
      n=$((n+1))
      if [ $n -eq 1 ]; then
	:
      elif [ $n -eq 2 ]; then
	cat $omitline
	continue
      elif [ $n -lt $((narts-nlimit+1)) ]; then
	continue
      fi
    fi
    mf2=$tmpd/midfile2
    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"; } &&
	     /bin/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
	cachestamp=$tmpd/cache.$$.stamp
	touch $cachestamp
	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%@*}">`echo $uname|htmlescape`</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;" \
		      > $mf2  # Stop query here 2020-04-23
		if cat $mf2 | unhexize \
		    | convert -define ${fmt}:size=100x100 -resize 100x100'>' \
			      - ${fmt}:- > $outfile
		then
		  cat "$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
		else	# Failed to convert
		  rm -f $outfile
		  echo "<a href=\"$catlink+$mrid\">$fnb</a>"
		fi
	      fi
	      ;;
	    *)
	      echo "<__UNREADABLE__><a href=\"$catlink+$mrid\"><img src=\"$deficon\">$fnb</a>"
	      ;;
	  esac
	done
	echo "</td></tr>"
      } > "$cachefile.$$"	######## New ROW Creation Ends here ########
      # Care about race condition
      if [ -z "$hte" -a -s $cachefile -a $cachefile -nt $cachestamp ]; then
	# If other process have created cache, give up to serve our file
	rm -f $cachefile.$$
      else
	mv -f $cachefile.$$ $cachefile
      fi
      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"
  ntmode="通知モード=$blog_notify${blog_team:+ (team=$blog_team)}"
  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 id="text" 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="送信"'" class=\"$blog_notify\" title=\"$ntmode\""'>
<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="Shortcut: R${nl}Reload">再読込</a> / <a
	 href="#title" id="bottom" accesskey="t"
	  title="Shortcut: T${nl}to the Top">先頭へ</a>
	 ${showalllink:+/ `echo $showalllink|sed 's/n:all/&\#bottom/'`$limitedmsg}</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 (numericalized in s4.cgi)
  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"`
  htmlowner=`echo ${ge:-$owner}|htmlescape`
  fh=$tmpd/formhead
  echo "$time [$title]@$htmlowner" > $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%'
	       OR mode LIKE '%enquete%');
	 /* ↑これでレポート形式の 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
    contenttype; echo
    echo "メンバー以外は利用できません。" | html p; return
  fi
  # Here, this blog is writable by $user
  mode=`getvalbyid blog mode $1`
  owner=`getvalbyid blog owner $1`
  blogauthor=`getvalbyid blog author $1`
  isopenblogauthor=false
  if [ x"$user" = x"$owner" ]; then
    : OK
  elif isgrpowner "$user" "$owner"; then
    : OK
  elif [ x"$blogauthor" = x"$user" ]; then
    # Non-admin Author of blog cannot do gethandout() in report-closed mode
    # for avoidance the risk of fake report-closed blog.
    case "$mode" in	# Only report-open can be handled by blog author
      *open*) isopenblogauthor=true ;;
    esac
  else
    contenttype; echo
    echo "グループ管理者のみ取得できます。" | html p; return
  fi
  copy2csv=false
  blogid=`getvalbyid blog id $1`
  isgroup "$owner" && isgroup=true || isgroup=false
  isgrpowner "$user" "$owner" && isgrpadmin=true || isgrpadmin=false
  
  i=0
  midfile=$tmpd/midfile
  bd=$tmpd/archive.$$
  mkdir $bd
  case "$mode" in
    *quiz*)
      copy2csv=true ;;
    *enquete*)
      copy2csv=true
      csvline=`getvalbyid blog heading $1 | grep "..*,." | head -1`
      # Create CSV-base table for questionnaire
      # If heading in blog_s has at least 1 CSV line,
      # we take the line as column list.
      # Otherwise we produce two column CSV as below:
      # USER,ANSWER
      query "DROP TABLE IF EXISTS tmp_q;"
      if [ -n "$csvline" ]; then
	query <<-EOF
	  CREATE TEMPORARY TABLE tmp_q("user", $csvline);
	EOF
	if [ $? != 0 ]; then
	  contenttype; echo
	  cat <<-EOF | html p; exit
		掲示板のヘッダにあるCSV定義が不正でCSV出力できません。
		$csvline
		空白なしの項目名を半角カンマ区切りで1行で書いてください。
		EOF
	fi
      else
	query <<-EOF
	  CREATE TEMPORARY TABLE tmp_q(user text PRIMARY KEY, answer);
	EOF
      fi
  esac
  if $copy2csv; then
    mkdir $bd/$rid
    outcsv=$bd/$rid/migrate-$rid.csv
    fullcsv=$bd/$rid/all-text-full-$rid.csv
    sq "$db" <<-EOF | tr '|' ',' > $outcsv
	SELECT author as "USER",
	       replace(val, x'0a', ',') as "${csvline:-ANSWER}"
	FROM article a JOIN article_s s ON a.id=s.id
	     	AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
	     	AND s.key='text';
	EOF
    sq "$db" <<-EOF > $fullcsv
	.mode csv
	.head 1
	SELECT author as "ユーザ",
	       (SELECT gecos FROM gecoses g WHERE author=g.name) as "表示名",
	       val as "テキスト"
	FROM   article a JOIN article_s s ON a.id=s.id
	       AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
	       AND s.key='text';
	EOF
  fi
  query <<-EOF > $midfile		# Using tempfile for quick db-unlock
	SELECT a.rowid, a.id artid, a.author, hex(s.val)
	FROM article a JOIN article_s s ON a.id=s.id
	WHERE blogid=(SELECT id FROM blog WHERE rowid=$rid);
	EOF
  cat $midfile | while IFS='|' read rowid artid author text; do
    $isgrpowner || $isopenblogauthor \
      || isfilereadable $user article_s $rowid || continue
    dir=`printf $bd/%d/%06d "$rid" "$rowid"`
    mkdir -p $dir
    echo "$author" > $dir/Author
    echo "$text" | unhexize > $dir/Text
    i=0
    query "SELECT m.rowid, m.val FROM article_m m \
	   WHERE id='$artid' AND m.key IN ('image', 'document', 'binary');" \
      | while IFS='|' read mrowid filename; do
          i=$((i+1))
          outfile=`printf "%s/%02d-%s" "$dir" $i "$filename"`
          query "SELECT quote(bin) FROM article_m WHERE rowid=$mrowid;" \
		| unhexize > $outfile
    done
  done
  if [ ! -d $bd/$rid ]; then
    contenttype; echo
    echo "取得できるファイルがありませんでした。" | html p
    return
  fi

  if $copy2csv; then
    query <<-EOF > $bd/$rid/all-text-1stline-$rid.csv
	.mode csv
	.head 1
	CREATE TEMPORARY TABLE IF NOT EXISTS tmp_q("user", "TEXT");
	.import $outcsv tmp_q
	SELECT * FROM tmp_q;
	.mode list
	.head 0
	EOF
  fi
  err "BDLIST: `ls -l $bd`"
  arcname=archive-$rid.tar.gz
  ### outstdout=true
  (cd $bd
   # query() CANNOT BE used in this subshell
   if [ "$outstdout" ]; then
     cat <<-EOF
	Content-type: application/x-gzip
	Content-Disposition: filename="$arcname"

	EOF
     tar zcf - $rid
     return
   else
     tar zcf .archive.tar.gz $rid && mv .archive.tar.gz "$arcname"
     err Creating tar archive "`ls -l "$arcname"`"
   fi
  )
  arcfile=$bd/$arcname
  echo "Content-type: application/x-gzip"
  echo "Content-Length: `cat $arcfile|wc -c`"
  echo "Content-Disposition: filename=\"$arcname\""
  echo
  cat $arcfile
}
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}
  grid=`getpar grid`
  msg=""
  if [ -n "$grid" ]; then
    grp=`getgroupbyid "$grid"`
    qgrp=`sqlquote "$grp"`
    cond="WHERE key='owner' AND val=$qgrp"
    msg="(`linkhome $grid` グループから)"
  elif [ -n "$owner" ]; then
    cond="where key='owner' and val='$owner'"
    msg="(`linkhome $owner` さんの記録から)"
  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||'+n:all#'||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=GRProwID(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
  htmlowner=`echo $owner|htmlescape`
  err blog-add: \$1=$grprowid rowid=$rowid owner=$owner
  if isgroup "$owner"; then
    if [ -z "$grprowid" ]; then
      qgrp=`sqlquote "$owner"`	# Inefficient...
      grprowid=`query "SELECT rowid FROM grp WHERE gname=$qgrp;"`
    fi
    groupmode=1 listing=$owner guide="[`linkhome $grprowid`]" 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
  # $1=rowid $2=control-sequence
  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`
  htmlowner=`echo $owner|htmlescape`
  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\">$htmlowner</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
      ## BEGIN: 2020-06-11 - Post Integrity Check.  Disable if it slows down..
      blogid=`getpar blogid | tr -c -d 'a-z0-9'`
      brid=`query "SELECT rowid FROM blog WHERE id='$blogid';"`
      if [ x"$rowid" != x"$brid" ]; then
	_id=`getpar id | tr -c -d 'a-z0-9'`
	_aid=`query "SELECT rowid FROM article WHERE id='$_id';"`
	if [ -z "$_aid" ]; then
	  echo "掲示板から書き込んで下さい。" | html p
	  return
	fi
      fi
      ## END:
      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" | htmlescape > $tmpd/title.$$
  echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
  ${BLOG_SHOW:-blog_showentry} blog $rowid "$2" \
      | _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
}