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