s4
view s4-blog.sh @ 640:b7aeab02d242
Check the SQL output size in case of DB timeout
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Sun, 03 May 2020 19:06:59 +0900 |
| parents | 1c36cb1a1a0e |
| children | a1bcb043589e |
line source
1 #
2 type cgiinit >/dev/null 2>&1 || . ./s4-funcs.sh
4 # Global error flags
5 BLOG_NOTMEM=1
6 BLOG_FROZEN=2
7 FROZEN_TAG='<span class="frozen">[凍結]</span>'
9 blog_genform() {
10 #
11 t=$1
12 }
14 blog_writable() (
15 # $1=articleid $2=user
16 # Return: $?=0 - Writable
17 # =1 - NOT Writable because user is not a member
18 # =2 - NOT Writable because blog is frozen
19 blogowner=`getvalbyid blog owner "$1"`
20 state=`getvalbyid blog state "$1"`
21 rc=0
22 [ x"$blogowner" = x"$2" ] || isuser "$blogowner" || ismember "$2" "$blogowner" || rc=$((rc+$BLOG_NOTMEM))
23 [ "$state" = "frozen" ] && rc=$((rc+$BLOG_FROZEN))
24 return $rc
25 )
26 blog_readable() {
27 # $1=articleid $2=user
28 mode=`getgroupattr $grp regmode`
29 }
30 blog_getteam() {
31 # $1=rowid of blog
32 blogid="${1%%[!A-Z0-9a-z_]*}"
33 # team cannot get `getvalbyid blog team "$blogid"` because it's not
34 # defined in blog.def. Yes, it is Illegal USE!!
35 query "SELECT val FROM blog_s
36 WHERE id=(SELECT id FROM blog WHERE rowid=$blogid)
37 AND key='team';"
38 }
39 blog_notify_reply() (
40 # $1=blogid $2=ReplyingUser $3=WrittenText $4(optional)=Action
41 blogid="${1%%[!A-Z0-9a-z_]*}"
42 blogowner=`getvalbyid blog owner "$blogid"`
43 blogtitle=`getvalbyid blog title "$blogid"`
44 blogurl="$urlbase?replyblog+$blogid"
45 action=${4:-書き込み}
46 mode=`getvalbyid blog notify "$blogid"`
47 isgroup "$blogowner" && _isgroup=true || _isgroup=false
48 ### EXCEPT=`sqlquote "$user"` ## User should receive to feal some annoyance
49 case $mode in
50 admin)
51 if $_isgroup; then
52 emails=`getgroupadminmails $blogowner`
53 else
54 emails=`collectemail $blogowner`
55 fi
56 notifyto=`getpar notifyto`
57 if [ -n "$notifyto" ]; then
58 emails=$emails" `email4groupbyuid \"$blogowner\" $notifyto`"
59 fi
60 ;;
61 no) emails="" ;;
62 *) team=`blog_getteam "$blogid"`
63 # team cannot get by `getvalbyid blog team "$blogid"`
64 emails=`TEAM=$team collectemail $blogowner` ;;
65 esac
66 ## 2017-0210 Respond to the direct reply mark such as: >#1234
67 replymark=`echo "$3"|nkf -w -Z0|grep '^ *>#'`
68 authgecos=`gecos $2`
69 if [ -z "$4" -a -n "$replymark" ]; then
70 # If the action is new subscription($4="") and has ">#123" marks...
71 ids=`echo "$replymark"|sed 's/[^#0-9]*#\([0-9]*\)[^#0-9]*/\1 /g'`
72 ids=`echo $ids|tr -dc '[0-9 ]'|tr ' ' ','`
73 # -> 123,345,347
74 unames=`query "SELECT distinct author FROM article \
75 WHERE rowid in ($ids)\
76 AND blogid=(SELECT id FROM blog WHERE rowid=$blogid);"`
77 if [ -n "$unames" ]; then
78 e4g=$(if $_isgroup; then
79 email4group "$blogowner" $unames
80 else
81 for u in $unames; do
82 collectemail $u
83 done
84 fi)
85 emails=$emails" $e4g"
86 for e in $unames; do
87 g=`gecos $e`
88 whom=$whom"${whom:+,}${g:-$e}さん"
89 done
90 action="${whom}への返信"
91 fi
92 else
93 [ x"$2" = x"$blogowner" ] && return # If author=blogowner, unnecessary
94 fi
95 test -z "$emails" && return
96 err notify: user=$user Admins=`getgroupadmins $blogowner` Mode=$mode Emails="[$emails]"
97 MAIL_FROM=$noreply_from \
98 SMAIL_TO="`echo "$blogowner" | nkf -jM | tr -d '\n'` readers <$oreply>" \
99 smail "$emails" "${action}通知 $urlbase"<<EOF
100 [$blogtitle]板に${action}がありました。
101 ※※※このメイルには返信できません(返信は次のURLへ)※※※
102 場所: $blogurl (返信先)
103 所有: $blogowner
104 題目: $blogtitle
105 筆者: $authgecos
106 内容:
107 `echo "$3"|sed 's/^/> /'`
109 ※※このメイルに返信しても通知者には伝わりません。
110 ※※上記URLから${S4NAME:-s4}掲示板に書き込んでください。
111 EOF
112 )
114 blog_showentry() {
115 # $1=table $2=rowid
116 # if [ -n "$2" ]; then
117 # if [ -n "$imgcached" ]; then
118 # bstmpdir=$tmpdir/$imgcached/$thumbxy
119 # else
120 # bstmpdir=$tmpd
121 # # tmpd=`mktempd`
122 # # tmpfiles=$tmpfiles" $tmpd"
123 # fi
124 # fi
125 td=`getcachedir "article/$2"`
126 [ -d "$td" ] || mkdir -p $td
127 tbl=${1%%[!A-Z0-9a-z_]*} rowid=${2%%[!A-Z0-9a-z_]*}
128 err blow_showentry: rowid=$rowid, '$2'=$2 user=$user
129 ts=${tbl}_s tm=${tbl}_m
130 at=article as=article_s am=article_m
131 serial=$(($(date +%s)-1420038000))s$$
132 cannotread='<div class="relative"><img class="overlap" src="img/key.png" alt="(読み取り不可)"></div>'
133 blog_writable $rowid $user
134 rc=$?
135 if [ $rc = 0 ]; then
136 iswritable=true
137 ismem=true
138 else
139 iswritable=false
140 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ]; then
141 ismem=false
142 else
143 ismem=true
144 fi
145 fi
146 # This function grasps blog entry definiton directly.
147 # blog: id
148 # blog_s: title,ctime,heading
149 # blog_m: *article
151 blogowner=`getvalbyid blog owner "$2"`
152 isgroup "$blogowner" && isgroup=true || isgroup=false
153 isgrpadmin=false # Reversed later (*1)
154 test x"$user" = x"$blogowner" && isowner=true || isowner=false
156 # 2015-10-05 check readable
157 if ! $iswritable; then
158 # err blogowner=$blogowner
159 if $isgroup; then
160 regmode=`getgroupattr $blogowner regmode`
161 # err regmode=$regmode
162 if [ x"$regmode" = x"moderated" ]; then
163 # if ! ismember $user $blogowner; then
164 if ! $ismem; then
165 echo "加入してからどうぞ" | html p
166 return
167 fi
168 fi
169 fi
170 else # if writable
171 isgrpowner "$user" "$blogowner" && isgrpadmin=true # (*1)
172 fi
173 blog_notify=`getvalbyid blog notify "$rowid"`
174 blog_team=`blog_getteam "$rowid"`
175 blog_mode=`getvalbyid blog mode "$rowid"`
176 case "$blog_notify" in # "all", "admin" or "no" (or NULL)
177 admin) notifyto=adm ;;
178 *) notifyto="" ;;
179 esac
180 case $blog_mode in
181 *quiz*|*close*|*euquete*) f_exclusive=1 ;;
182 *) f_exclusive='' ;;
183 esac
185 # err "SELECT id from $tbl where rowid=$rowid"
186 id=`query "select id from $tbl where rowid=$rowid;"`
187 #err id=$id
188 #err "select val from $ts where key='title' and id='$id';"
190 #(1)Display root article
191 cat<<EOF
192 <form class="replyblog" action="$myname?replyblog+${rowid}#bottom" method="POST" enctype="multipart/form-data">
193 <table class="bloghead">
194 EOF
196 href="<a href=\"?editheading+$rowid\" accesskey=\"e\" title=\"E\"> 編集 </a>"
197 if $ismem; then
198 case $blog_mode in
199 *report*|*quiz*|*enquete*)
200 href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"L\"> 提出状況 </a>"
201 ;;
202 esac
203 if $isgrpadmin || $isowner; then
204 href3="(<a href=\"?gethandout+$rowid\" accesskey=\"f\" title=\"F\">ファイル取得</a>)"
205 fi
206 fi
207 href4='<a href="#bottom" accesskey="b" title="B"> 末尾へ</a>'
208 $isgrpadmin &&
209 href5="<a href=\"?blogseen+$rowid\" accesskey=\"s\" title=\"S\"> 読刻</a>"
210 quizmodefile=$tmpd/quiz; rm -f "$quizmodefile" # XXX: Global state
211 midfile=$tmpd/midfile
213 query<<-EOF > $midfile
214 SELECT coalesce((SELECT "yes" FROM blog
215 -- GrpAdmin CAN EDIT heading since 2019-08-15
216 WHERE '$isgrpadmin' = 'true'
217 OR (rowid=$rowid AND author='$user')),
218 ''),
219 max(CASE key WHEN 'ctime' THEN val END) ctime,
220 max(CASE key WHEN 'heading' THEN hex(val) END) heading,
221 CASE (SELECT val FROM $ts WHERE key="mode" AND id="$id")
222 WHEN 'report-closed' THEN 'レポート提出用(closed)'
223 WHEN 'report-open' THEN 'レポート提出用(open)'
224 WHEN 'quiz' THEN 'クイズ'
225 WHEN 'enquete' THEN '集計'
226 ELSE ''
227 END
228 FROM $ts WHERE id='$id' GROUP BY id;
229 EOF
230 if test -s $midfile && IFS='|' read edit ctime hexhead blogtype < $midfile
231 then
232 cat<<-EOF
233 <tr><td>${edit:+$href }$ctime $blogtype $href2$href3 $href4 $href5</td></tr>
234 <tr class="preface${frozen_class:+ }$frozen_class">
235 <td>`echo "$hexhead"|unhexize|htmlescape|hreflink|minitbl`</td></tr>
236 </table>
237 EOF
238 case "$blogtype" in
239 "クイズ"|"XXXX集計")
240 echo "${blogtype}モードは本人と管理者の書き込みのみが表示されます。"
241 ;;
242 esac | html p 'class="warn"'
243 echo '<table class="blog_replies"> <!-- blog:blog_showentry() main table -->'
244 if [ x"$blogtype" = x"クイズ" -o x"$blogtype" = x"XXXX集計" ]; then
245 if $isgroup; then
246 # Failsafe to query timeout
247 qgrp=`sqlquote "$blogowner"`
248 cat<<-EOF > $quizmodefile
249 AND (author IN (SELECT user FROM grp_adm WHERE gname=$qgrp)
250 OR
251 author='$user')
252 EOF
253 if isgrpowner "$user" "$blogowner"; then
254 : > $quizmodefile
255 fi
256 else # if user-blog
257 if [ x"$user" != x"$blogowner" ]; then
258 cat<<-EOF > $quizmodefile
259 AND author IN ('$blogowner', '$user')
260 EOF
261 fi
262 fi
263 fi
264 else # Cannot read SQL output
265 echo "時間をおいて繋いでください(Please visit later)." | html p
266 return
267 fi
268 lkhome="<a href=\"$myname?home" lke='">'
269 lkedit="<a href=\"$myname?editart"
270 hlink="$myname?home" elink="$myname?editart"
271 catlink="$myname?showattc+article_m"
272 deficon="img/file-icon.png"
273 # 2016-08-15 Newer flag introduced
274 atime=`query "SELECT time FROM acclog
275 WHERE tbl='blog' AND tblrowid=$rowid AND user='$user';"`
276 iconcleaner=$tmpd/iconcleaner.$$
277 [ -s $quizmodefile ] && cond_qz=`cat $quizmodefile`
278 # *** DO NOT USE query(), use "sq $db" instead here ***
279 # because the next block in pipe line uses query() repeatedly.
280 ###### TEST: 2020-04-23 Use intermediate file to shorten duration of db-lock
281 ###### sq $db<<EOF |
282 query <<EOF > $midfile
283 WITH a_s AS (
284 SELECT id,
285 max(CASE key WHEN 'ctime' THEN val END) TIME,
286 max(CASE key WHEN 'text' THEN val END) TEXT
287 FROM article_s
288 GROUP by id
289 )
290 SELECT a.id,
291 CASE author
292 WHEN '$user' THEN a.rowid||'+'||$rowid
293 ELSE ''
294 END edit,
295 CASE -- 「通知送信」ボタンの有無
296 WHEN '$notifyto' = '' THEN '' -- 不要モードならなし
297 WHEN '$user' = author THEN '' -- 筆者自身ならなし
298 ELSE "yes"
299 END notify,
300 (SELECT rowid FROM user WHERE name=author) user_rid,
301 author,
302 coalesce((SELECT val FROM user_s
303 WHERE name=author AND key='gecos'),
304 author) uname,
305 (SELECT val FROM user_s WHERE name=author AND key='$iconcachekey')
306 icon,
307 a.rowid,
308 s.TIME,
309 CASE WHEN s.TIME < '2019-05'
310 THEN printf('平成%d年%d月%d日%s',
311 substr(s.TIME, 1, 4)-1988,
312 substr(s.TIME, 6, 2),
313 substr(s.TIME, 9, 2),
314 substr(s.TIME, 12)
315 )
316 WHEN s.TIME < '2020'
317 THEN printf('令和元年%d月%d日%s',
318 substr(s.TIME, 6, 2),
319 substr(s.TIME, 9, 2),
320 substr(s.TIME, 12))
321 WHEN s.TIME < '2050'
322 THEN printf('令和%d年%d月%d日%s',
323 substr(s.TIME, 1, 4)-2018,
324 substr(s.TIME, 6, 2),
325 substr(s.TIME, 9, 2),
326 substr(s.TIME, 12))
327 ELSE s.TIME
328 END reki,
329 CASE WHEN s.TIME > '$atime' THEN 'new' ELSE '' END newer,
330 hex(s.TEXT),
331 CASE -- File Accessibility to attached file
332 WHEN '$f_exclusive' = '' THEN ''
333 WHEN '$isgrpadmin' = 'true' THEN ''
334 WHEN '$user' = author THEN ''
335 ELSE 'Unreadable'
336 END cannotread,
337 (SELECT group_concat(rowid||':'||length(bin)||':'||hex(val), ' ')
338 FROM article_m
339 WHERE id=a.id AND key='image') imxgids
340 FROM (select rowid,id,author from article
341 where blogid in
342 (select id from blog where rowid=$rowid)
343 $cond_qz) a
344 LEFT JOIN
345 a_s s
346 ON a.id=s.id;
347 EOF
348 cat $midfile |
349 while IFS='|' read id edit notify uid author uname icon aid \
350 tm reki new hte fa imgids
351 do
352 mf2=$tmpd/midfile2
353 cachefile="$td/$id.row.html"
354 stampfile="$td/$id.row.stamp"
355 editlink="${edit:+<a href="$elink+$edit">編集</a> }"
356 nt="<label style=\"font-size: 70%;\"><input type=\"checkbox\"\
357 name=\"notifyto\" value=\"$uid\">返信通知送信</label>"
358 # fa is file accessibility flag # err "----r=$aid fa=[$fa]----"
360 # First, check the availability of user-icon.
361 # If not existent, clear and reset row cache by rm $stampfile
362 if [ ! -s "$icon" ]; then
363 rm -f "$stampfile"; unset stampfile
364 fi
365 if test -s "$stampfile" &&
366 test -s "$cachefile" &&
367 { ts=`cat "$stampfile"`; test -n "$ts"; } &&
368 test "$ts" '>' "$tm" && # Cache timestamp is newer
369 test "$stampfile" -nt "$icon"; then # UserIcon is older
370 : Nothing to do
371 else
372 { ######## New ROW creation begins here ######## >$cachefile
373 tdcls="__NEWCLS__repatt"
374 if [ -s "$icon" ]; then
375 icfn=`echo "$icon"|htmlescape`
376 picon="<p class=\"proficon\"><a href=\"$hlink+$uid\" title=\"${author%@*}\"><img src=\"$icfn\"></a></p>"
377 else
378 echo "DELETE FROM user_s WHERE key='$iconcachekey' AND
379 val=`sqlquotestr \"$icon\"`;" >> $iconcleaner
380 picon=""
381 fi
383 cat<<EOF
384 <tr id="$id">
385 <td class="$tdcls">${picon}__EDIT__<a href="#$aid">#$aid</a>
386 <a href="$hlink+$uid" title="${author%@*}">$uname</a>
387 <span title="$tm">${reki:-$tm}</span>
388 <__NOTIFY__></td>
389 EOF
390 echo -n "<td id=\"$aid\" class=\"repl\">"
391 echo "$hte"|unhexize|htmlescape|hreflink|minitbl
392 usecache='' tsfile=$td/$id.stamp
393 for i in $imgids; do
394 mrid=${i%%:*}; i=${i#*:}; sz=`size_h ${i%%:*}`
395 fn=`echo "${i#*:}"|unhexize`
396 fnb=$fn"(${sz})"
397 case "$fn" in
398 *.[Pp][Nn][Gg]|*.[Jj][Pp][Gg]|*.[Jj][Pp][Ee][Gg]|*.[GgTt][Ii][Ff])
399 # fmt=${fn##*.} # convert - jpg:- is slow...why
400 case "$fn" in
401 *.[Pp][Nn][Gg]) fmt=png ;;
402 *.[Gg][Ii][Ff]) fmt=gif ;;
403 *) fmt=jpeg ;;
404 esac
405 outfile=$td/$mrid-${fn%.*}.$fmt
406 #err fn=$fn outfile=$outfile
407 #err "usecache=$usecache `ls -l $outfile`"
408 #err tm=$tm
409 #err tsfile=$tsfile=`cat $tsfile`
410 if [ -s "$outfile" ] && # $outfile should be > 0
411 { [ "$usecache" ] || # And usecache flag is true, or...
412 { [ -s "$tsfile" ] && [ x"`cat $tsfile`" = x"$tm" ]
413 };}; then
414 usecache=1 # Set usecache flag on
415 cat<<-EOF
416 <a href="$catlink+$mrid"><img src="$outfile">
417 $fnb</a>
418 EOF
419 # !!NOTE!! Create row stamp ONLY WHEN imgcache is active
420 else
421 query "SELECT hex(bin) FROM article_m WHERE rowid=$mrid;" \
422 > $mf2 # Stop query here 2020-04-23
423 cat $mf2| unhexize \
424 | convert -define ${fmt}:size=100x100 -resize 100x100'>' \
425 - ${fmt}:- \
426 | tee "$outfile" \
427 | hexize \
428 | sed -e 's/\(..\)/%\1/g' \
429 -e "s|^|<a href=\"$catlink+$mrid\"><img src=\"data:image/$fmt,|" \
430 -e "s|\$|\">$fnb</a>|"
431 unset stampfile # img data stream is not suitable to cache
432 echo $tm > $tsfile
433 fi
434 ;;
435 *)
436 echo "<__UNREADABLE__><a href=\"$catlink+$mrid\"><img src=\"$deficon\">$fnb</a>"
437 ;;
438 esac
439 done
440 echo "</td></tr>"
441 } > "$cachefile" ######## New ROW Creation Ends here ########
442 test -n "$stampfile" && date "+%F %T" > $stampfile
443 fi
444 # Printing a cached row
445 sed -e "/^<td class=/s/__NEWCLS__/$new${new:+ }/" \
446 -e "/^<td class=/s,__EDIT__,$editlink," \
447 -e "/^<__NOTIFY__>/s,,${notify:+$nt}," \
448 -e "/<__UNREADABLE__>/s,,${fa:+$cannotread}," \
449 $cachefile
450 done
452 help="=== コメントに使用できる特殊記法 ===
453 行頭に href=URL でURLへのリンク
454 行頭に iframe=URL でURL先を開く iframe
455 [[#記事番号]] でs4内の記事番号に飛ぶリンク
456 [[#検索キーワード]] でs4内の記事検索(記号はいくつか使えない)
457 [[URL]] でURLへのリンク、 [[URL|文字列]]でアンカー文字列指定
458 {{画像URL}} でインライン画像、 {{画像URL|幅}} でピクセル幅指定
459 {{{URL}}} でURL先を開く iframe、 {{{URL|高さ}}} ピクセル高さ指定
460 行頭: ## 大見出し, ### 中見出し, #### 小見出し
461 行末の2連続スペースで強制改行(<br>)
462 |*見出し列|列2|列3… と行頭から始まる縦棒区切り行を続けて表
463 ' *語群* ' で強調(両側の空白必要、** でもっと強調。*の代わりに _ でも可)
464 - [ ] と - [x] でチェックボックス"
465 touchhelp="${touchpanel:+<p class=\"help\">$help</p>}"
466 filehelp="《添付の注意》
467 $file_accept_help"
468 ntmode="通知モード=$blog_notify${blog_team:+ (team=$blog_team)}"
469 textform='<div class="fold">
470 <input type="checkbox" id="cmt" checked><label
471 accesskey="c" title="C" for="cmt">コメントする</label><div>
472 <table class="b">
473 <tr><td><textarea id="text" name="text" cols="72" rows="4" title="'"$help"'">
474 </textarea>'"$touchhelp</td></tr>
475 <tr><td>添付ファイル(${filesize_max_MB}以下):"'
476 <input type="file" name="image"'" $file_accept title=\"$filehelp\" multiple></td></tr>"'
477 </table>
478 <input type="submit" value="送信"'" class=\"$blog_notify\" title=\"$ntmode\""'>
479 <input type="reset" value="リセット"></div></div>
480 '
481 cat<<-EOF
482 </table> <!-- end of s4-blog:blog_showentry() main table -->
483 <p class="update_link"><a
484 href="?reload/$rowid" accesskey="r" title="R">再読込</a> / <a
485 href="#title" id="bottom" accesskey="t" title="T">先頭へ</a></p>
486 EOF
487 $iswritable && cat<<-EOF
488 <div class="blogcomment">
489 <input type="hidden" name="blogid" value="$id">
490 <input type="hidden" name="id" value="`genserial`">
491 <input type="hidden" name="stage" value="replyblog">
492 $textform
493 </div>
494 </form> <!-- End of s4-blog:blog_showentry() main form -->
495 EOF
496 # Clean up orphaned icon cache
497 [ -s $iconcleaner ] && query ".read '$iconcleaner'"
498 # Record access log
499 acclog blog $rowid
500 }
502 lshandout() {
503 # $1=rowid of blog
504 blog_writable $1 $user
505 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
506 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
507 echo "メンバー以外は利用できません。" | html p; return
508 fi
509 time=`getvalbyid blog ctime $1|colrm 11`
510 owner=`getvalbyid blog owner $1`
511 title=`getvalbyid blog title $1`
512 ge=`gecos $owner`
513 fh=$tmpd/formhead
514 echo "$time [$title]@${ge:-$owner}" > $fh
515 lshandoutsub $owner "$@" \
516 |_m4 -D_TITLE_="提出状況" \
517 -D_FORMHEAD_="syscmd(cat $fh)" \
518 -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \
519 $layout/html.m4.html $layout/form+dump-whead.m4.html
520 gn=`echo $owner|htmlescape`
521 echo "<p><a href=\"?lshandoutall+$1\">グループ $gn すべてのレポート板集計</a></p>"
522 }
523 gethandoutcsv() {
524 # contenttype; echo
525 CATCSV=1 lshandoutall "$1"
526 }
527 gethandoutcsv2() {
528 # contenttype; echo
529 SQL=$(cat<<-EOF
530 WITH this_blog_articles AS (
531 SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime
532 FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid
533 ), text_or_file AS (
534 SELECT bid, author, title, ctime, 'text' shu, count(val) cnt
535 FROM this_blog_articles tba, article_s s
536 ON tba.aid=s.id
537 WHERE key='text'
538 GROUP by bid, author
539 UNION
540 SELECT bid, author, title, ctime, 'file' shu, count(val) cnt
541 FROM this_blog_articles tba, article_m m
542 ON tba.aid=m.id
543 WHERE key='image'
544 GROUP by bid, author
545 ), count_list AS (
546 SELECT author,
547 substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit,
548 cnt
549 FROM text_or_file
550 )
551 SELECT gecos "名前",
552 substr(author, 1, instr(author, '@')-1) "uname",
553 unit,
554 cnt "post"
555 FROM count_list cl JOIN gecoses g ON cl.author=g.name;
556 EOF
557 ) gethandoutcsv "$1"
558 }
559 lshandout_ulink_table() {
560 # NO Args. Read stdin as SQL
561 echo '<table class="b td3rr td3evw">'
562 hrb="<a href=\"?home+"
563 # echo "$sql" | sq -header -html $db \ # Formerly, this is called via sq()
565 printf ".mode html\n.header ON\n" | query
566 cat | query \
567 | sed -e "s,\(<TR><TD>\)\([^ ]*\) \(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
568 echo '</table>'
569 printf ".mode list\n.header OFF\n" | query
570 }
571 lshandoutall() {
572 # $1=rowid of blog
573 blog_writable $1 $user
574 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
575 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
576 echo "メンバー以外は利用できません。" | html p; return
577 fi
578 rowid=$(($1 + 0))
579 owner=`getvalbyid blog owner $1`
580 qowner=`sqlquotestr "$owner"`
582 query<<-EOF
583 CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS
584 WITH blog_owner_mode AS (
585 SELECT id,
586 blog.rowid brid,
587 max(CASE key WHEN 'owner' THEN val END) owner,
588 max(CASE key WHEN 'mode' THEN val END) mode,
589 max(CASE key WHEN 'title' THEN val END) title,
590 max(CASE key WHEN 'ctime' THEN val END) ctime
591 FROM blog NATURAL JOIN blog_s
592 GROUP BY id
593 )
594 SELECT id, brid, title, ctime FROM blog_owner_mode
595 /* WHERE owner=$qowner AND mode LIKE '%report%'; */
596 WHERE owner=$qowner
597 AND
598 (mode LIKE '%report%' OR mode LIKE '%quiz%'
599 OR mode LIKE '%enquete%');
600 /* ↑これでレポート形式の blogid 一覧を得る */
601 EOF
602 if [ -z "$CATCSV" ]; then
603 _m4 -D_TITLE_="提出状況" $layout/html.m4.html
604 ge=`gecos "$owner"`
605 tbls=""
606 grptxt=`echo "${ge:-$owner}"|htmlescape`
607 echo "<h1>$grptxt 書き込み状況一覧</h1>"
608 fi
609 if [ -z "$SQL" ]; then
610 bridlist=`query "SELECT brid FROM report_type_blogs;"`
611 for brid in $bridlist; do # Skip this loop if $SQL set
612 brid=$(($brid + 0)) # Ensure to be a number
613 [ $brid = 0 ] && continue
614 time=`getvalbyid blog ctime $brid|colrm 11`
615 title=`getvalbyid blog title $brid|htmlescape`
616 state=`getvalbyid blog state $brid|htmlescape`
617 tt="handout_$brid"
618 [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen=""
619 if [ -z "$CATCSV" ]; then
620 echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a>$frozen</h2>"
621 lshandoutsub "$owner" $brid "$tt"
622 else
623 lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
624 fi
625 tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
626 done
627 fi
628 sql=${SQL:-"SELECT * FROM $tbls;"}
629 if [ -z "$CATCSV" ]; then
630 echo "<hr><h2>総合</h2>"
631 echo "$sql" | lshandout_ulink_table
632 echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">CSV</a>)</h2>"
633 printf ".mode csv\n.header ON\n" | query
634 echo '<pre class="list">'
635 echo "$sql" | query | sed 's/^"[0-9]* /"/'
636 echo "</pre>"
637 echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>"
638 else
639 contenttype "Application/CSV"
640 printf ".mode csv\n.header ON\n" | query >/dev/null
641 fn=report-count.csv
642 printf 'Content-Disposition: filename="%s"\n' "$fn"
643 outfile=$tmpd/out-$$.csv
644 echo "$sql" | query | sed 's/^"[0-9]* /"/' > $outfile
645 echo "Content-Length: " `cat $outfile | wc -c`; echo
647 cat $outfile
648 exit 0
649 fi
650 printf ".mode list\n.header OFF\n.separator |\n" | query
651 }
652 lshandoutsub() {
653 # $1=owner $2=rowid of blog &optional $3=temp_table name
654 qgname=`sqlquote "$1"`
655 if isgroup "$1"; then
656 sample="(select user from grp_mem where gname=$qgname)"
657 else
658 sample="(select distinct author as user from arts)"
659 echo "(集計は板への投稿者のみ)" | html p
660 fi
661 tmpname="${3:-handout_$2}"
662 sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS
663 with arts as (select id,author from article \
664 where blogid=(select id from blog where rowid=$2))\
665 select (select rowid from user where name=c0.user)||' '|| \
666 (select gecos from gecoses where name=c0.user) as 'メンバー',\
667 substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\
668 sum(case when c1.key is not null then 1 else 0 end)\
669 as '[$title] コメント記入',\
670 sum(case when c2.key is not null then 1 else 0 end)\
671 as '[$title] ファイルの提出'\
672 from $sample c0 \
673 left join (select id,author from arts) a\
674 on c0.user=a.author\
675 left join (select id,key from article_s where key='text') c1\
676 on a.id=c1.id left join (select id,key from article_m ) c2\
677 on c1.id=c2.id group by c0.user order by c0.user;\
678 \
679 SELECT * FROM $tmpname;"
680 # err ishandoutsub: sql="$sql"
681 echo "$sql" | lshandout_ulink_table
682 }
683 gethandout() {
684 # $1=rowid of blog
685 rid=`numericalize "$1"`
686 blog_writable $rid $user
687 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
688 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
689 contenttype; echo
690 echo "メンバー以外は利用できません。" | html p; return
691 fi
692 # Here, this blog is writable by $user
693 owner=`getvalbyid blog owner $1`
694 if [ x"$user" = x"$owner" ]; then
695 : OK
696 elif isgrpowner "$user" "$owner"; then
697 : OK
698 else
699 contenttype; echo
700 echo "板の所有者以外は利用できません。" | html p; return
701 fi
702 mode=`getvalbyid blog mode $1`
703 copy2csv=false
704 blogid=`getvalbyid blog id $1`
705 isgroup "$owner" && isgroup=true || isgroup=false
706 isgrpowner "$user" "$owner" && isgrpadmin=true || isgrpadmin=false
708 i=0
709 midfile=$tmpd/midfile
710 bd=$tmpd/archive.$$
711 mkdir $bd
712 case "$mode" in
713 *quiz*)
714 copy2csv=true ;;
715 *enquete*)
716 copy2csv=true
717 csvline=`getvalbyid blog heading $1 | grep "..*,." | head -1`
718 # Create CSV-base table for questionnaire
719 # If heading in blog_s has at least 1 CSV line,
720 # we take the line as column list.
721 # Otherwise we produce two column CSV as below:
722 # USER,ANSWER
723 query "DROP TABLE IF EXISTS tmp_q;"
724 if [ -n "$csvline" ]; then
725 query <<-EOF
726 CREATE TEMPORARY TABLE tmp_q("user", $csvline);
727 EOF
728 if [ $? != 0 ]; then
729 contenttype; echo
730 cat <<-EOF | html p; exit
731 掲示板のヘッダにあるCSV定義が不正でCSV出力できません。
732 $csvline
733 空白なしの項目名を半角カンマ区切りで1行で書いてください。
734 EOF
735 fi
736 else
737 query <<-EOF
738 CREATE TEMPORARY TABLE tmp_q(user text PRIMARY KEY, answer);
739 EOF
740 fi
741 esac
742 if $copy2csv; then
743 mkdir $bd/$rid
744 outcsv=$bd/$rid/migrate-$rid.csv
745 fullcsv=$bd/$rid/all-text-full-$rid.csv
746 sq "$db" <<-EOF | tr '|' ',' > $outcsv
747 SELECT author as "USER",
748 replace(val, x'0a', ',') as "${csvline:-ANSWER}"
749 FROM article a JOIN article_s s ON a.id=s.id
750 AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
751 AND s.key='text';
752 EOF
753 sq "$db" <<-EOF > $fullcsv
754 .mode csv
755 .head 1
756 SELECT author as "ユーザ",
757 (SELECT gecos FROM gecoses g WHERE author=g.name) as "表示名",
758 val as "テキスト"
759 FROM article a JOIN article_s s ON a.id=s.id
760 AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
761 AND s.key='text';
762 EOF
763 fi
764 query <<-EOF > $midfile # Using tempfile for quick db-unlock
765 SELECT a.rowid, a.id artid, a.author, hex(s.val)
766 FROM article a JOIN article_s s ON a.id=s.id
767 WHERE blogid=(SELECT id FROM blog WHERE rowid=$rid);
768 EOF
769 cat $midfile | while IFS='|' read rowid artid author text; do
770 isfilereadable $user article_s $rowid || continue
771 dir=`printf $bd/%d/%06d "$rid" "$rowid"`
772 mkdir -p $dir
773 echo "$author" > $dir/Author
774 echo "$text" | unhexize > $dir/Text
775 i=0
776 query "SELECT m.rowid, m.val FROM article_m m \
777 WHERE id='$artid' AND m.key IN ('image', 'document', 'binary');" \
778 | while IFS='|' read mrowid filename; do
779 i=$((i+1))
780 outfile=`printf "%s/%02d-%s" "$dir" $i "$filename"`
781 query "SELECT quote(bin) FROM article_m WHERE rowid=$mrowid;" \
782 | unhexize > $outfile
783 done
784 done
785 if [ ! -d $bd/$rid ]; then
786 contenttype; echo
787 echo "取得できるファイルがありませんでした。" | html p
788 return
789 fi
791 if $copy2csv; then
792 query <<-EOF > $bd/$rid/all-text-1stline-$rid.csv
793 .mode csv
794 .head 1
795 .import $outcsv tmp_q
796 SELECT * FROM tmp_q;
797 .mode list
798 .head 0
799 EOF
800 fi
801 err "BDLIST: `ls -l $bd`"
802 arcname=archive-$rid.tar.gz
803 (cd $bd
804 # query() CANNOT BE used in this subshell
805 tar zcf .archive.tar.gz $rid && mv .archive.tar.gz "$arcname"
806 err Creating tar archive "`ls -l "$arcname"`"
807 )
808 arcfile=$bd/$arcname
809 echo "Content-type: application/x-gzip"
810 echo "Content-Length: `cat $arcfile|wc -c`"
811 echo "Content-Disposition: filename=\"$arcname\""
812 echo
813 cat $arcfile
814 }
815 blogseen() { # $1 = blogid
816 blogid=${1%%[!0-9]*}
817 if [ -z "$blogid" ]; then
818 echo "Invalid blog id" | html p; exit
819 fi
820 blog_writable "$blogid" "$user"
821 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
822 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
823 echo "メンバー以外は利用できません。" | html p; return
824 fi
825 owner=`getvalbyid blog owner $rowid`
826 qowner=`sqlquotestr "$owner"`
827 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"`
828 ge=`gecos "$owner" | htmlescape`
829 title=`getvalbyid blog title $rowid | htmlescape`
830 h1="アクセス時刻"
831 link2board="<a href=\"?replyblog+$rowid\">$title</a>"
832 link2group="<a href=\"?grp+$grprowid\">$ge</a>"
833 _m4 -D_TITLE_="$h1" $layout/html.m4.html
834 echo "$h1" | html h1
835 echo "[$link2board]@$link2group" | html h2
836 warn=' class="warn"'
837 cat <<-EOF
838 <table class="b">
839 <tr><th>メンバー</th><th>uname</th><th>最終閲覧時刻</th></tr>
840 EOF
841 query <<-EOF |
842 WITH grpmem as (
843 SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos
844 FROM grp_mem
845 WHERE gname=(SELECT val FROM blog_s
846 WHERE id=(select id from blog where rowid=$blogid)
847 AND key='owner')
848 ), acctime AS (
849 SELECT user, max(time) atime
850 FROM tblaccesses
851 WHERE tbl='blog' AND tblrowid=$blogid
852 GROUP BY user
853 )
854 SELECT g.user,
855 (SELECT rowid FROM user u WHERE u.name=g.user),
856 hex(gecos),
857 atime
858 FROM grpmem g LEFT JOIN acctime t
859 ON g.user = t.user
860 GROUP BY g.user
861 ORDER BY atime DESC;
862 EOF
863 while IFS='|' read u uid hexge time; do
864 td=${time:+"<td>"} # If the variable time is set, td=<td>
865 td=${td:-"<td$warn>"} # else td=<td class="warn">
866 cat <<-EOF
867 <tr>
868 <td><a href="?home+$uid">`echo "$hexge"|unhexize|htmlescape`</a></td>
869 <td>`echo ${u%%@*}|htmlescape`</td>
870 $td${time:----}</td></tr>
871 EOF
872 done
873 cat <<-EOF
874 </table>
875 <p><a href="?replyblog+$rowid">[$title]に戻る</a></p>
876 </html>
877 EOF
878 }
879 lsmyfile() { # $1(optional)=SortBy
880 case "$1" in
881 ""|CTIME-DESC)
882 by="CTIME" ord="DESC" ;;
883 CTIME*) by="CTIME" ;;
884 FILE*) by="FILE" ;;
885 OWNER*) by="OWNER" ;;
886 TITLE*) by="TITLE" ;;
887 esac
888 case "$1" in
889 *DESC) ord="DESC" ;;
890 esac
891 case "$ord" in
892 DESC) lkod="" jord="降順" ;;
893 *) lkod="-DESC" jord="昇順" ;;
894 esac
895 sql="select m.val||'/'||m.rowid FILE,
896 coalesce(
897 case when (select name from user where name=bs.owner)
898 is not null
899 then (select val from user_s where name=bs.owner
900 and key='gecos')
901 when (select gname from grp where gname=bs.owner)
902 is not null
903 then (select val from grp_s where gname=bs.owner
904 and key='gecos')
905 else
906 null
907 end,
908 bs.owner
909 ) OWNER,
910 a_s.val CTIME,
911 ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
912 from (select rowid,id,val from article_m where id
913 in (select id from article where author='$user')
914 and type like 'file:%')
915 m left join article a on m.id=a.id
916 left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
917 left join (select id,
918 max(case key when 'owner' then val end) as owner,
919 max(case key when 'title' then val end) as title
920 from blog_s group by id)
921 bs on a.blogid=bs.id
922 left join blog b on bs.id=b.id
923 where m.val is not null order by $by $ord;"
924 err lshandoutbyauthor: sql=`echo "$sql"`
925 title="個人提出ファイル"
926 _m4 -D_TITLE_=$title $layout/html.m4.html
927 hra="<a href=\"?lsmyfile+"
928 hrb="<a href=\"?showattc+article_m+"
929 hrc="<a href=\"?replyblog+"
930 (echo '<table class="b">'
931 echo "$sql"|sq -html -header $db ) \
932 | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
933 -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
934 -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
935 | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
936 -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
937 echo '</table>'
938 }
939 searchart() {
940 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC
941 bloglist=`getpar bloglist|sed 's/[^0-9,]//g'`
942 kwdgrp=""
943 authcond=""
944 if [ -z "$kwd" ]; then
945 echo "検索語を指定してください" | html p; return
946 fi
947 if logstart "$searchlog"; then
948 { echo "kwd=$kwd"
949 test -n "$bloglist" && echo "bloglist=$bloglist"
950 } >> $searchlog
951 logend "$searchlog"
952 fi
953 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
954 # Like '#1234', assume as artID
955 rowid=$((${kwd#\#} + 0)) # Force to be a number
956 kc="ar.rowid = $rowid"
957 else
958 for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes
959 ctime=""
960 if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
961 # '@<2016-10-10' -> ctime < '2016-10-10'
962 # '@>=2016-10-10' -> ctime >= '2016-10-10'
963 # '@2016-10-10' -> ctime GLOB '@2016-10-10'
964 k=${k#@}
965 case "$k" in
966 [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
967 *) op='GLOB'; ctime="${k##*[><= ]}*" ;;
968 esac
969 kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
970 # Not sure GROUP BY a.blogid is comfortable for searchers...?
971 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results
972 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
973 ctime=`date +%F`
974 elif n=`expr x"$k" : 'x@\([0-9]*\)days*'` >/dev/null >&2; then
975 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
976 elif [ x"$k" = x"@week" ]; then
977 ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
978 elif n=`expr x"$k" : 'x@\([0-9]*\)weeks*'` >/dev/null >&2; then
979 n=$((n * 7))
980 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
981 elif [ x"$k" = x"@month" ]; then
982 ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
983 elif n=`expr x"$k" : 'x@\([0-9]*\)months*'` >/dev/null >&2; then
984 ctime=`query "SELECT datetime('now', 'localtime', '-$n month');"`
985 elif [ x"$k" = x"@year" ]; then
986 ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
987 elif n=`expr x"$k" : 'x@\([0-9]*\)years*'` >/dev/null >&2; then
988 ctime=`query "SELECT datetime('now', 'localtime', '-$n year');"`
989 fi
990 if [ -n "$ctime" ]; then
991 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
992 else
993 e=""
994 case "$k" in
995 *${likeesc}*) e="" ;; # Giving up char-escaping
996 *%*|*_*) k=`echo "$k"|sed "s/\([%_]\)/${likeesc}\1/g"`
997 e=" ESCAPE '$likeesc'" ;;
998 esac
999 kc=$kc${kc:+" AND "}"content LIKE '%$k%'$e"
1000 fi
1001 done
1002 fi
1003 kwd=`echo "$kwd"|htmlescape`
1004 owner=`getpar owner`
1005 owner=${owner:-$1}
1006 msg=""
1007 if [ -n "$owner" ]; then
1008 cond="where key='owner' and val='$owner'"
1009 if isuser $owner; then
1010 msg="(`linkhome $owner` さんの記録から)"
1011 else
1012 linkhome $owner 1>&3
1013 msg="(`linkhome $owner` グループから)"
1014 fi
1015 elif { author=`getpar author`; test -n "$author"; }; then
1016 atptn=`sqlquotestr $author`
1017 #kc="$kc${kc:+ AND }author=$atptn"
1018 authcond="WHERE author=$atptn"
1019 if isuser $author; then
1020 msg="(`linkhome $author` さんの書き込みから)"
1021 fi
1022 fi
1023 if [ -n "$bloglist" ]; then
1024 blogcond="AND bl.rid IN ($bloglist)"
1025 fi
1027 sf=`search_form "$search_form_args" "$kwd" | sed '1d;$d'` # rm <div></div>
1028 echo "$sf" | sed -e "/POST SENTENCE/s/.*/__PS__/" -e "/EOF/q" \
1029 | _m4 -D__PS__="による検索結果$msg"
1030 echo "(上記入力窓で再検索すると下記の掲示板のみに絞って再検索します)" \
1031 | html p 'class="small"'
1032 # article_s: id=article-id, key='text', val='TEXT'
1033 # article: id=article-id, blogid=blogkd
1034 # blog: id=blog-id, author=LeaderAuthor
1035 # blog_s: id=blog-id, key='title', val='BLOG-TITLE'
1036 # WANT: blog-ROWid,article-id,val(TEXT)
1037 sql2="`sql4readableblogs` -- Extract user-readable blogs
1038 -- 0.3sec
1039 WITH artsm AS (
1040 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
1041 FROM article a
1042 LEFT JOIN
1043 (SELECT ars.id, ctime, text, coalesce(files, '') files
1044 FROM (SELECT id,
1045 max(CASE key WHEN 'ctime' THEN val END) ctime,
1046 max(CASE key WHEN 'text' THEN val END) text
1047 FROM article_s
1048 GROUP BY id) ars
1049 LEFT JOIN
1050 (SELECT id, group_concat(val) files
1051 FROM article_m
1052 WHERE type LIKE 'file:%'
1053 GROUP BY id) arm
1054 ON ars.id=arm.id
1055 ) ar
1056 ON a.id=ar.id
1057 ), ar AS (
1058 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
1059 FROM article a JOIN artsm ON a.id=artsm.id
1060 $authcond
1061 ), bl AS (
1062 SELECT blg.rid, blg.*, blog_s.val TITLE
1063 FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
1064 )
1065 SELECT bl.rid||'#'||ar.id '',
1066 bl.title TITLE,
1067 (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
1068 substr(ctime, 0, 11) DATE,
1069 substr(content, 0, 78) TEXT
1070 FROM ar JOIN bl
1071 ON ar.blogid=bl.id
1072 WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) $blogcond
1073 ORDER by DATE DESC, TITLE, ctime;"
1074 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
1075 href=\"?replyblog+\1\">VIEW</a></TD>,"
1076 # echo "$sql2" > tmp/sql.out
1077 result=$tmpd/result.$$
1078 cat<<EOF
1079 <table class="b searchart">
1080 `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
1081 </table>
1082 EOF
1083 if [ -s "$result" ]; then
1084 found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT
1085 one=${found%1}
1086 echo "$found match${one:+es} found"
1087 # <a href="?replyblog+39#12345">VIEW</a>
1088 # -> 39,49,55, -> 39,49,55
1089 # -> <input type="hidden" name="bloglist" value="39,49,55">
1090 sed -n "/.*href=.*replyblog\+\([0-9][0-9]*\).*/s//\1/p" "$result" \
1091 | sort | uniq | tr '\n' ',' \
1092 | sed -e 's/,$//' \
1093 -e 's/^/<input type="hidden" name="bloglist" value="/' \
1094 -e 's/$/">/'
1095 else
1096 echo orz...
1097 fi
1098 echo "$sf" | sed "1,/-- EOF/d" # Close <form>
1099 }
1100 listblog() (
1101 # $1={user,group}
1102 qow=`sqlquote $1`
1103 cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
1104 cgi_form searchart<<EOF
1105 <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
1106 `cgi_hidden owner $user`
1107 EOF
1108 DT_CHLD=article:blogid DT_QOWNER=$qow \
1109 dumptable html blog 'ctime title heading' "$cond"
1110 )
1112 blog_addentry() {
1113 # $1=GRPname(if it is a group)
1114 grprowid=`numericalize $1`
1115 rowid=`getpar rowid`
1116 ## err blog_addentry0: rowid=$rowid
1117 if [ -n "$grprowid" ]; then
1118 owner=`getgroupbyid $grprowid`
1119 else
1120 owner=`getpar owner`
1121 fi
1122 err blog-add: \$1=$grprowid rowid=$rowid owner=$owner
1123 if isgroup "$owner"; then
1124 groupmode=1 listing=$owner guide="[${owner}]" GF_OWNER=$owner
1125 else
1126 usermode=1 listing=$user guide="[個人]"
1127 fi
1129 if [ -n "`getpar title`" ]; then
1130 if [ "$usermode" ]; then
1131 err usermode: user=$user owner=$owner
1132 if [ x"$user" != x"$owner" ]; then
1133 echo "他人の日記は書けません" | html p
1134 return 2
1135 fi
1136 elif [ "$groupmode" ]; then # if write to group log
1137 grp=$owner #\`getpar grp\`
1138 err ismember: $user $grp
1139 if ! ismember "$user" "$grp"; then
1140 echo "(話題作成はこのグループに加入してから)" | html p
1141 return 3
1142 fi
1143 fi
1144 par2table $formdir/blog.def
1145 serial=`getpar serial`
1146 ## err SERIAL: $serial ROWID=$rowid listing=$listing
1147 id=""
1148 if [ -n "$rowid" ]; then
1149 # Here, id becomes NULL when removal of entries at par2table
1150 id=`query "select rowid from blog where rowid=$rowid;"`
1151 elif [ -n "$serial" ]; then
1152 # If new blog leader created, traverse to its head.
1153 id=`query "select rowid from blog where id='$serial';"`
1154 ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
1155 fi
1156 if [ -n "$id" ]; then
1157 ## If new aritcle is entered, JUMP to blog_reply
1158 blog_reply $id
1159 return
1160 fi
1161 fi
1162 echo "${guide}新規話題作成" > $tmpd/title.$$
1163 listblog $listing > $tmpd/listblog.$$
1164 genform $formdir/blog.def \
1165 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1166 -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
1167 -D_DUMPHEAD_="これまでの蓄積" \
1168 -D_FORM_="syscmd(\`cat')" \
1169 -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
1170 $layout/html.m4.html \
1171 $layout/form+dump-whead.m4.html
1172 }
1174 blog_reply() { # Posting to blog article
1175 rowid=`numericalize $1` # Ensure (already purified in s4.cgi)
1177 if [ -z "$rowid" ]; then
1178 echo "表示する日記番号が未指定です。" | html p
1179 return
1180 fi
1181 title=`getvalbyid blog title $rowid`
1182 owner=`getvalbyid blog owner $rowid`
1183 qowner=`sqlquotestr "$owner"`
1184 if [ -z "$title" ]; then
1185 echo "日記番号指定が無効です。" | html p
1186 return
1187 fi
1188 blog_writable $rowid $user; rc=$?
1189 if [ $rc = 0 ]; then
1190 iswritable=true
1191 else
1192 iswritable=false
1193 if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
1194 isfrozen=true
1195 frozen_class='frozen"'
1196 frozen_flag=$FROZEN_TAG
1197 fi
1198 fi
1199 if isuser "$owner"; then
1200 subtitle="`gecos $owner` さんの話題"
1201 else
1202 grprowid=`query "select rowid from grp where gname=$qowner;"`
1203 subtitle="グループ
1204 <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$owner</a> での話題
1205 `query \"SELECT printf('(チーム:%s)', val)\
1206 FROM blog_s
1207 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
1208 AND key='team';
1209 \"|htmlescape`"
1210 memclass=`grp_getbodyclass "$owner"`
1211 fi
1213 text=`getpar text`
1214 if [ -n "$text" ]; then
1215 if $iswritable; then
1216 par2table $formdir/article.def
1217 st=$?
1218 case $st in
1219 0|4)
1220 [ "$st" = "4" ] && act="書込削除"
1221 blog_notify_reply $rowid $user "$text" $act
1222 if [ -n "$grprowid" ]; then
1223 qgrp=$(sqlquote "$owner")
1224 dbsetbyid grp $owner wtime "`date '+%F %T'`"
1225 else
1226 dbsetbyid user "$user" wtime "`date '+%F %T'`"
1227 fi
1228 ;;
1229 esac
1230 else
1231 if $isfrozen; then
1232 title="$title(凍結板につき書き込み不可)"
1233 else
1234 title="$title(加入してないので書き込み不可)"
1235 fi
1236 fi
1237 fi
1238 def=$formdir/article.def
1239 echo "$title" > $tmpd/title.$$
1240 echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
1241 ${BLOG_SHOW:-blog_showentry} blog $rowid \
1242 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1243 -D_BODYCLASS_=general"${memclass:+ $memclass}" \
1244 -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
1245 -D_FORM_='' \
1246 -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
1247 $layout/html.m4.html $layout/form+dump-whead.m4.html
1248 }
1250 blog_reply_article() { # Direct link to article in some blog
1251 arid=${1:-0} # Already sanitized to digits
1252 brid=`query "SELECT rowid FROM blog WHERE \
1253 id=(SELECT blogid FROM article WHERE rowid=$arid);"`
1254 if [ -n "$brid" ]; then
1255 newurl="?replyblog+$brid#$arid"
1256 echo "Refresh: 0; $newurl"; echo
1257 exit 0
1258 else
1259 contenttype; echo
1260 echo "無効な記事番号です." | html p
1261 fi
1262 }