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