s4
view s4-blog.sh @ 875:380167e9cfcf
Add link to generate csv file for Zoom(TM) pre-assigned rooms
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Wed, 02 Dec 2020 19:10:52 +0859 |
| parents | 1b0669c854cf |
| children | 85a20c87bf97 |
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="" ;; # 2020-0630 Omit email when heavy load...(XXX)
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*|"") # When blog_mode is "", fallback to 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>|<a href=\"?gethandout+$rowid+by_uname\" accesskey=\"u\" title=\"Shortcut: F${nl}File Retrieval by User\">著者順</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 行頭「* 」で箇条書、次の行頭空白で継続、行頭詰めると箇条書終わり
555 行頭「1. 」で番号付、2行目以降も「1. 」で勝手に番号増える、行頭詰めで終わり
556 [[#記事番号]] でs4内の記事番号に飛ぶリンク
557 [[#検索キーワード]] でs4内の記事検索(記号はいくつか使えない)
558 [[URL]] でURLへのリンク、 [[URL|文字列]]でアンカー文字列指定
559 {{画像URL}} でインライン画像、 {{画像URL|幅}} でピクセル幅指定
560 {{{URL}}} でURL先を開く iframe、 {{{URL|高さ}}} ピクセル高さ指定
561 行頭: ## 大見出し, ### 中見出し, #### 小見出し
562 行末の2連続スペースで強制改行(<br>)
563 |*見出し列|列2|列3… と行頭から始まる縦棒区切り行を続けて表
564 ' *語群* ' で強調(両側の空白必要、** でもっと強調。*の代わりに _ でも可)
565 - [ ] と - [x] でチェックボックス"
566 touchhelp="${touchpanel:+<p class=\"help\">$help</p>}"
567 filehelp="《添付の注意》
568 $file_accept_help"
569 ntmode="通知モード=$blog_notify${blog_team:+ (team=$blog_team)}"
570 textform=$(cat<<-EOF
571 <div class="fold">
572 <input type="checkbox" id="cmt" checked><label
573 accesskey="c" title="C" for="cmt">コメントする</label><div>
574 <table class="b">
575 <tr><td><textarea id="text" name="text" cols="72" rows="4" title="$help">
576 </textarea>$touchhelp</td></tr>
577 <tr><td>添付ファイル(${filesize_max_MB}以下):
578 `cgi_file image "" "$file_accept title=\"$filehelp\" multiple"`
579 </td></tr>
580 </table>
581 <input type="submit" value="送信" class="$blog_notify" title="$ntmode">
582 <input type="reset" value="リセット"></div></div>
583 EOF
584 )
585 cat<<-EOF
586 </table> <!-- end of s4-blog:blog_showentry() main table -->
587 <p class="update_link"><a
588 href="?reload/$rowid" accesskey="r"
589 title="Shortcut: R${nl}Reload">再読込</a> / <a
590 href="#title" id="bottom" accesskey="t"
591 title="Shortcut: T${nl}to the Top">先頭へ</a>
592 ${showalllink:+/ `echo $showalllink|sed 's/n:all/&\#bottom/'`$limitedmsg}</p>
593 EOF
594 $iswritable && cat<<-EOF
595 <div class="blogcomment">
596 <input type="hidden" name="blogid" value="$id">
597 <input type="hidden" name="id" value="`genserial`">
598 <input type="hidden" name="stage" value="replyblog">
599 $textform
600 </div>
601 </form> <!-- End of s4-blog:blog_showentry() main form -->
602 EOF
603 # Clean up orphaned icon cache
604 [ -s $iconcleaner ] && query ".read '$iconcleaner'"
605 # Record access log
606 acclog blog $rowid
607 }
609 lshandout() {
610 # $1=rowid of blog (numericalized in s4.cgi)
611 blog_writable $1 $user
612 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
613 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
614 echo "メンバー以外は利用できません。" | html p; return
615 fi
616 time=`getvalbyid blog ctime $1|colrm 11`
617 owner=`getvalbyid blog owner $1`
618 title=`getvalbyid blog title $1`
619 ge=`gecos "$owner"`
620 htmlowner=`echo ${ge:-$owner}|htmlescape`
621 fh=$tmpd/formhead
622 echo "$time [$title]@$htmlowner" > $fh
623 lshandoutsub "$owner" "$@" \
624 |_m4 -D_TITLE_="提出状況" \
625 -D_H1_="提出状況" \
626 -D_FORMHEAD_="syscmd(cat $fh)" \
627 -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \
628 $layout/html.m4.html $layout/form+dump-whead.m4.html
629 gn=`echo $owner|htmlescape`
630 echo "<p><a href=\"?lshandoutall+$1\">グループ $gn すべてのレポート板集計</a></p>"
631 }
632 gethandoutcsv() {
633 # contenttype; echo
634 CATCSV=1 lshandoutall "$1"
635 }
636 gethandoutcsv2() {
637 # contenttype; echo
638 SQL=$(cat<<-EOF
639 WITH this_blog_articles AS (
640 SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime
641 FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid
642 ), text_or_file AS (
643 SELECT bid, author, title, ctime, 'text' shu, count(val) cnt
644 FROM this_blog_articles tba, article_s s
645 ON tba.aid=s.id
646 WHERE key='text'
647 GROUP by bid, author
648 UNION
649 SELECT bid, author, title, ctime, 'file' shu, count(val) cnt
650 FROM this_blog_articles tba, article_m m
651 ON tba.aid=m.id
652 WHERE key='image'
653 GROUP by bid, author
654 ), count_list AS (
655 SELECT author,
656 substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit,
657 cnt
658 FROM text_or_file
659 )
660 SELECT gecos "名前",
661 substr(author, 1, instr(author, '@')-1) "uname",
662 unit,
663 cnt "post"
664 FROM count_list cl JOIN gecoses g ON cl.author=g.name;
665 EOF
666 ) gethandoutcsv "$1"
667 }
668 lshandout_ulink_table() {
669 # NO Args. Read stdin as SQL
670 echo '<table class="b td3rr td3evw">'
671 hrb="<a href=\"?home+"
672 # echo "$sql" | sq -header -html $db \ # Formerly, this is called via sq()
674 printf ".mode html\n.header ON\n" | query
675 cat | query \
676 | sed -e "s,\(<TR><TD>\)\([^ ]*\) \(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
677 echo '</table>'
678 printf ".mode list\n.header OFF\n" | query
679 }
680 lshandoutall() {
681 # $1=rowid of blog
682 blog_writable $1 $user
683 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
684 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
685 echo "メンバー以外は利用できません。" | html p; return
686 fi
687 rowid=$(($1 + 0))
688 owner=`getvalbyid blog owner $1`
689 qowner=`sqlquotestr "$owner"`
691 query<<-EOF
692 CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS
693 WITH blog_owner_mode AS (
694 SELECT id,
695 blog.rowid brid,
696 max(CASE key WHEN 'owner' THEN val END) owner,
697 max(CASE key WHEN 'mode' THEN val END) mode,
698 max(CASE key WHEN 'title' THEN val END) title,
699 max(CASE key WHEN 'ctime' THEN val END) ctime
700 FROM blog NATURAL JOIN blog_s
701 GROUP BY id
702 )
703 SELECT id, brid, title, ctime FROM blog_owner_mode
704 /* WHERE owner=$qowner AND mode LIKE '%report%'; */
705 WHERE owner=$qowner
706 AND
707 (mode LIKE '%report%' OR mode LIKE '%quiz%'
708 OR mode LIKE '%enquete%');
709 /* ↑これでレポート形式の blogid 一覧を得る */
710 EOF
711 if [ -z "$CATCSV" ]; then
712 _m4 -D_TITLE_="提出状況" $layout/html.m4.html
713 ge=`gecos "$owner"`
714 tbls=""
715 grptxt=`echo "${ge:-$owner}"|htmlescape`
716 echo "<h1>$grptxt 書き込み状況一覧</h1>"
717 fi
718 if [ -z "$SQL" ]; then
719 bridlist=`query "SELECT brid FROM report_type_blogs;"`
720 for brid in $bridlist; do # Skip this loop if $SQL set
721 brid=$(($brid + 0)) # Ensure to be a number
722 [ $brid = 0 ] && continue
723 time=`getvalbyid blog ctime $brid|colrm 11`
724 title=`getvalbyid blog title $brid|htmlescape`
725 state=`getvalbyid blog state $brid|htmlescape`
726 tt="handout_$brid"
727 [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen=""
728 if [ -z "$CATCSV" ]; then
729 echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a>$frozen</h2>"
730 lshandoutsub "$owner" $brid "$tt"
731 else
732 lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
733 fi
734 tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
735 done
736 fi
737 sql=${SQL:-"SELECT * FROM $tbls;"}
738 if [ -z "$CATCSV" ]; then
739 echo "<hr><h2>総合</h2>"
740 echo "$sql" | lshandout_ulink_table
741 echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">CSV</a>)</h2>"
742 printf ".mode csv\n.header ON\n" | query
743 echo '<pre class="list">'
744 echo "$sql" | query | sed 's/^"[0-9]* /"/'
745 echo "</pre>"
746 echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>"
747 else
748 contenttype "Application/CSV"
749 printf ".mode csv\n.header ON\n" | query >/dev/null
750 fn=report-count.csv
751 printf 'Content-Disposition: filename="%s"\n' "$fn"
752 outfile=$tmpd/out-$$.csv
753 echo "$sql" | query | sed 's/^"[0-9]* /"/' > $outfile
754 echo "Content-Length: " `cat $outfile | wc -c`; echo
756 cat $outfile
757 exit 0
758 fi
759 printf ".mode list\n.header OFF\n.separator |\n" | query
760 }
761 lshandoutsub() {
762 # $1=owner $2=rowid of blog &optional $3=temp_table name
763 qgname=`sqlquote "$1"`
764 if isgroup "$1"; then
765 sample="(select user from grp_mem where gname=$qgname)"
766 else
767 sample="(select distinct author as user from arts)"
768 echo "(集計は板への投稿者のみ)" | html p
769 fi
770 tmpname="${3:-handout_$2}"
771 sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS
772 with arts as (select id,author from article \
773 where blogid=(select id from blog where rowid=$2))\
774 select (select rowid from user where name=c0.user)||' '|| \
775 (select gecos from gecoses where name=c0.user) as 'メンバー',\
776 substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\
777 sum(case when c1.key is not null then 1 else 0 end)\
778 as '[$title] コメント記入',\
779 sum(case when c2.key is not null then 1 else 0 end)\
780 as '[$title] ファイルの提出'\
781 from $sample c0 \
782 left join (select id,author from arts) a\
783 on c0.user=a.author\
784 left join (select id,key from article_s where key='text') c1\
785 on a.id=c1.id left join (select id,key from article_m ) c2\
786 on c1.id=c2.id group by c0.user order by c0.user;\
787 \
788 SELECT * FROM $tmpname;"
789 # err ishandoutsub: sql="$sql"
790 echo "$sql" | lshandout_ulink_table
791 }
792 gethandout() {
793 # $1=rowid of blog
794 rid=`numericalize "$1"`
795 test x"$2" = x"by_uname" && by_uname="$2"
796 blog_writable $rid $user
797 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
798 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
799 contenttype; echo
800 echo "メンバー以外は利用できません。" | html p; return
801 fi
802 # Here, this blog is writable by $user
803 mode=`getvalbyid blog mode $1`
804 owner=`getvalbyid blog owner $1`
805 blogauthor=`getvalbyid blog author $1`
806 isopenblogauthor=false
807 if [ x"$user" = x"$owner" ]; then
808 : OK
809 elif isgrpowner "$user" "$owner"; then
810 : OK
811 elif [ x"$blogauthor" = x"$user" ]; then
812 # Non-admin Author of blog cannot do gethandout() in report-closed mode
813 # for avoidance the risk of fake report-closed blog.
814 case "$mode" in # Only report-open can be handled by blog author
815 *open*) isopenblogauthor=true ;;
816 esac
817 else
818 contenttype; echo
819 echo "グループ管理者のみ取得できます。" | html p; return
820 fi
821 copy2csv=false
822 blogid=`getvalbyid blog id $1`
823 isgroup "$owner" && isgroup=true || isgroup=false
824 isgrpowner "$user" "$owner" && isgrpadmin=true || isgrpadmin=false
826 i=0
827 midfile=$tmpd/midfile
828 bd=$tmpd/archive.$$
829 mkdir $bd
830 case "$mode" in
831 *quiz*)
832 copy2csv=true ;;
833 *enquete*)
834 copy2csv=true
835 csvline=`getvalbyid blog heading $1 | grep "..*,." | head -1`
836 # Create CSV-base table for questionnaire
837 # If heading in blog_s has at least 1 CSV line,
838 # we take the line as column list.
839 # Otherwise we produce two column CSV as below:
840 # USER,ANSWER
841 query "DROP TABLE IF EXISTS tmp_q;"
842 if [ -n "$csvline" ]; then
843 query <<-EOF
844 CREATE TEMPORARY TABLE tmp_q("user", $csvline);
845 EOF
846 if [ $? != 0 ]; then
847 contenttype; echo
848 cat <<-EOF | html p; exit
849 掲示板のヘッダにあるCSV定義が不正でCSV出力できません。
850 $csvline
851 空白なしの項目名を半角カンマ区切りで1行で書いてください。
852 EOF
853 fi
854 else
855 query <<-EOF
856 CREATE TEMPORARY TABLE tmp_q(user text PRIMARY KEY, answer);
857 EOF
858 fi
859 esac
860 if $copy2csv; then
861 mkdir $bd/$rid
862 outcsv=$bd/$rid/migrate-$rid.csv
863 fullcsv=$bd/$rid/all-text-full-$rid.csv
864 sq "$db" <<-EOF | tr '|' ',' > $outcsv
865 SELECT author as "USER",
866 replace(val, x'0a', ',') as "${csvline:-ANSWER}"
867 FROM article a JOIN article_s s ON a.id=s.id
868 AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
869 AND s.key='text';
870 EOF
871 sq "$db" <<-EOF > $fullcsv
872 .mode csv
873 .head 1
874 SELECT author as "ユーザ",
875 (SELECT gecos FROM gecoses g WHERE author=g.name) as "表示名",
876 val as "テキスト"
877 FROM article a JOIN article_s s ON a.id=s.id
878 AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
879 AND s.key='text';
880 EOF
881 fi
882 query <<-EOF > $midfile # Using tempfile for quick db-unlock
883 SELECT a.rowid, a.id artid, a.author, hex(s.val)
884 FROM article a JOIN article_s s ON a.id=s.id
885 WHERE blogid=(SELECT id FROM blog WHERE rowid=$rid)
886 ORDER BY a.rowid;
887 EOF
888 cat $midfile | while IFS='|' read rowid artid author text; do
889 $isgrpowner || $isopenblogauthor \
890 || isfilereadable $user article_s $rowid || continue
891 if [ "$by_uname" ]; then
892 dir=`printf $bd/%d/%s "$rid" "$author"`
893 else
894 dir=`printf $bd/%d/%06d "$rid" "$rowid"`
895 fi
896 txt=`printf %06d $rowid`.txt
897 test -d "$dir" || mkdir -p "$dir"
898 echo "$author" > "$dir"/Author.txt
899 echo "$text" | unhexize > "$dir/$txt"
900 i=0
901 query "SELECT m.rowid, m.val FROM article_m m \
902 WHERE id='$artid' AND m.key IN ('image', 'document', 'binary');" \
903 | while IFS='|' read mrowid filename; do
904 i=$((i+1))
905 if [ "$by_uname" ]; then
906 outfile=`printf "%s/%06d-%s" "$dir" $rowid "$filename"`
907 else
908 outfile=`printf "%s/%02d-%s" "$dir" $i "$filename"`
909 fi
910 query "SELECT quote(bin) FROM article_m WHERE rowid=$mrowid;" \
911 | unhexize > "$outfile"
912 done
913 done
914 if [ ! -d $bd/$rid ]; then
915 contenttype; echo
916 echo "取得できるファイルがありませんでした。" | html p
917 return
918 fi
920 if $copy2csv; then
921 query <<-EOF > $bd/$rid/all-text-1stline-$rid.csv
922 .mode csv
923 .head 1
924 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_q("user", "TEXT");
925 .import $outcsv tmp_q
926 SELECT * FROM tmp_q;
927 .mode list
928 .head 0
929 EOF
930 fi
931 err "BDLIST: `ls -l $bd`"
932 arcname=archive-$rid.tar.gz
933 ### outstdout=true
934 (cd $bd
935 # query() CANNOT BE used in this subshell
936 if [ "$outstdout" ]; then
937 cat <<-EOF
938 Content-type: application/x-gzip
939 Content-Disposition: filename="$arcname"
941 EOF
942 tar zcf - $rid
943 return
944 else
945 tar zcf .archive.tar.gz $rid && mv .archive.tar.gz "$arcname"
946 err Creating tar archive "`ls -l "$arcname"`"
947 fi
948 )
949 arcfile=$bd/$arcname
950 echo "Content-type: application/x-gzip"
951 echo "Content-Length: `cat $arcfile|wc -c`"
952 echo "Content-Disposition: filename=\"$arcname\""
953 echo
954 cat $arcfile
955 }
956 blogseen() { # $1 = blogid
957 blogid=${1%%[!0-9]*}
958 if [ -z "$blogid" ]; then
959 echo "Invalid blog id" | html p; exit
960 fi
961 blog_writable "$blogid" "$user"
962 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
963 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
964 echo "メンバー以外は利用できません。" | html p; return
965 fi
966 owner=`getvalbyid blog owner $rowid`
967 qowner=`sqlquotestr "$owner"`
968 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"`
969 ge=`gecos "$owner" | htmlescape`
970 title=`getvalbyid blog title $rowid | htmlescape`
971 h1="アクセス時刻"
972 link2board="<a href=\"?replyblog+$rowid\">$title</a>"
973 link2group="<a href=\"?grp+$grprowid\">$ge</a>"
974 _m4 -D_TITLE_="$h1" $layout/html.m4.html
975 echo "$h1" | html h1
976 echo "[$link2board]@$link2group" | html h2
977 warn=' class="warn"'
978 cat <<-EOF
979 <table class="b">
980 <tr><th>メンバー</th><th>uname</th><th>最終閲覧時刻</th></tr>
981 EOF
982 query <<-EOF |
983 WITH grpmem as (
984 SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos
985 FROM grp_mem
986 WHERE gname=(SELECT val FROM blog_s
987 WHERE id=(select id from blog where rowid=$blogid)
988 AND key='owner')
989 ), acctime AS (
990 SELECT user, max(time) atime
991 FROM tblaccesses
992 WHERE tbl='blog' AND tblrowid=$blogid
993 GROUP BY user
994 )
995 SELECT g.user,
996 (SELECT rowid FROM user u WHERE u.name=g.user),
997 hex(gecos),
998 atime
999 FROM grpmem g LEFT JOIN acctime t
1000 ON g.user = t.user
1001 GROUP BY g.user
1002 ORDER BY atime DESC;
1003 EOF
1004 while IFS='|' read u uid hexge time; do
1005 td=${time:+"<td>"} # If the variable time is set, td=<td>
1006 td=${td:-"<td$warn>"} # else td=<td class="warn">
1007 cat <<-EOF
1008 <tr>
1009 <td><a href="?home+$uid">`echo "$hexge"|unhexize|htmlescape`</a></td>
1010 <td>`echo ${u%%@*}|htmlescape`</td>
1011 $td${time:----}</td></tr>
1012 EOF
1013 done
1014 cat <<-EOF
1015 </table>
1016 <p><a href="?replyblog+$rowid">[$title]に戻る</a></p>
1017 </html>
1018 EOF
1019 }
1020 lsmyfile() { # $1(optional)=SortBy
1021 case "$1" in
1022 ""|CTIME-DESC)
1023 by="CTIME" ord="DESC" ;;
1024 CTIME*) by="CTIME" ;;
1025 FILE*) by="FILE" ;;
1026 OWNER*) by="OWNER" ;;
1027 TITLE*) by="TITLE" ;;
1028 esac
1029 case "$1" in
1030 *DESC) ord="DESC" ;;
1031 esac
1032 case "$ord" in
1033 DESC) lkod="" jord="降順" ;;
1034 *) lkod="-DESC" jord="昇順" ;;
1035 esac
1036 sql="select m.val||'/'||m.rowid FILE,
1037 coalesce(
1038 case when (select name from user where name=bs.owner)
1039 is not null
1040 then (select val from user_s where name=bs.owner
1041 and key='gecos')
1042 when (select gname from grp where gname=bs.owner)
1043 is not null
1044 then (select val from grp_s where gname=bs.owner
1045 and key='gecos')
1046 else
1047 null
1048 end,
1049 bs.owner
1050 ) OWNER,
1051 a_s.val CTIME,
1052 ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
1053 from (select rowid,id,val from article_m where id
1054 in (select id from article where author='$user')
1055 and type like 'file:%')
1056 m left join article a on m.id=a.id
1057 left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
1058 left join (select id,
1059 max(case key when 'owner' then val end) as owner,
1060 max(case key when 'title' then val end) as title
1061 from blog_s group by id)
1062 bs on a.blogid=bs.id
1063 left join blog b on bs.id=b.id
1064 where m.val is not null order by $by $ord;"
1065 err lshandoutbyauthor: sql=`echo "$sql"`
1066 title="個人提出ファイル"
1067 _m4 -D_TITLE_=$title $layout/html.m4.html
1068 hra="<a href=\"?lsmyfile+"
1069 hrb="<a href=\"?showattc+article_m+"
1070 hrc="<a href=\"?replyblog+"
1071 (echo '<table class="b">'
1072 echo "$sql"|sq -html -header $db ) \
1073 | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
1074 -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
1075 -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
1076 | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
1077 -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
1078 echo '</table>'
1079 }
1080 getteamcsv() {
1081 gid=`numericalize "$1"`
1082 grp=`getgroupbyid "$gid"`
1083 err gid=$gid grp=$grp
1084 if ! isgrpowner "$user" "$grp"; then
1085 contentytpe 'text/plain; charset="utf-8"'; echo
1086 echo "管理者メンバー以外は利用できません。" | html p; return
1087 fi
1088 fn="team-$gid.csv"
1089 csv="$tmpd/$fn"
1090 err csv=$csv
1091 # We can leave csv mode here because this scripts will exit soon
1092 query <<-EOF
1093 .mode csv
1094 .head 1
1095 .output $csv
1096 SELECT val "ルーム名を事前割り当て", user "メールアドレス"
1097 FROM grp_mem_m
1098 WHERE key='team'
1099 AND gname=(SELECT gname FROM grp WHERE rowid=$gid)
1100 ORDER BY val;
1101 EOF
1102 contenttype 'text/plain; charset="utf-8"'
1103 echo "Content-Disposition: filename=\"$fn\""
1104 echo "Content-Length: " `cat $csv | wc -c`; echo
1105 #echo "Content-Type: " ${type#file:}; echo
1106 cat $csv
1107 exit
1108 }
1109 searchart() {
1110 _m4 -D_TITLE_="検索結果" $layout/html.m4.html
1111 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC
1112 bloglist=`getpar bloglist|sed 's/[^0-9,]//g'`
1113 kwdgrp=""
1114 authcond=""
1115 if [ -z "$kwd" ]; then
1116 echo "検索語を指定してください" | html p; return
1117 fi
1118 if logstart "$searchlog"; then
1119 { echo "kwd=$kwd"
1120 test -n "$bloglist" && echo "bloglist=$bloglist"
1121 } >> $searchlog
1122 logend "$searchlog"
1123 fi
1124 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
1125 # Like '#1234', assume as artID
1126 rowid=$((${kwd#\#} + 0)) # Force to be a number
1127 kc="ar.rowid = $rowid"
1128 else
1129 for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes
1130 ctime=""
1131 if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
1132 # '@<2016-10-10' -> ctime < '2016-10-10'
1133 # '@>=2016-10-10' -> ctime >= '2016-10-10'
1134 # '@2016-10-10' -> ctime GLOB '@2016-10-10'
1135 k=${k#@}
1136 case "$k" in
1137 [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
1138 *) op='GLOB'; ctime="${k##*[><= ]}*" ;;
1139 esac
1140 kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
1141 # Not sure GROUP BY a.blogid is comfortable for searchers...?
1142 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results
1143 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
1144 ctime=`date +%F`
1145 elif n=`expr x"$k" : 'x@\([0-9]*\)days*'` >/dev/null >&2; then
1146 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
1147 elif [ x"$k" = x"@week" ]; then
1148 ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
1149 elif n=`expr x"$k" : 'x@\([0-9]*\)weeks*'` >/dev/null >&2; then
1150 n=$((n * 7))
1151 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
1152 elif [ x"$k" = x"@month" ]; then
1153 ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
1154 elif n=`expr x"$k" : 'x@\([0-9]*\)months*'` >/dev/null >&2; then
1155 ctime=`query "SELECT datetime('now', 'localtime', '-$n month');"`
1156 elif [ x"$k" = x"@year" ]; then
1157 ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
1158 elif n=`expr x"$k" : 'x@\([0-9]*\)years*'` >/dev/null >&2; then
1159 ctime=`query "SELECT datetime('now', 'localtime', '-$n year');"`
1160 fi
1161 if [ -n "$ctime" ]; then
1162 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
1163 else
1164 e=""
1165 case "$k" in
1166 *${likeesc}*) e="" ;; # Giving up char-escaping
1167 *%*|*_*) k=`echo "$k"|sed "s/\([%_]\)/${likeesc}\1/g"`
1168 e=" ESCAPE '$likeesc'" ;;
1169 esac
1170 kc=$kc${kc:+" AND "}"content LIKE '%$k%'$e"
1171 fi
1172 done
1173 fi
1174 kwd=`echo "$kwd"|htmlescape`
1175 owner=`getpar owner`
1176 owner=${owner:-$1}
1177 grid=`getpar grid`
1178 msg=""
1179 if [ -n "$grid" ]; then
1180 grp=`getgroupbyid "$grid"`
1181 qgrp=`sqlquote "$grp"`
1182 cond="WHERE key='owner' AND val=$qgrp"
1183 msg="(`linkhome $grid` グループから)"
1184 elif [ -n "$owner" ]; then
1185 cond="where key='owner' and val='$owner'"
1186 msg="(`linkhome $owner` さんの記録から)"
1187 elif { author=`getpar author`; test -n "$author"; }; then
1188 atptn=`sqlquotestr $author`
1189 #kc="$kc${kc:+ AND }author=$atptn"
1190 authcond="WHERE author=$atptn"
1191 if isuser $author; then
1192 msg="(`linkhome $author` さんの書き込みから)"
1193 fi
1194 fi
1195 if [ -n "$bloglist" ]; then
1196 blogcond="AND bl.rid IN ($bloglist)"
1197 fi
1199 sf=`search_form "$search_form_args" "$kwd" | sed '1d;$d'` # rm <div></div>
1200 echo "$sf" | sed -e "/POST SENTENCE/s/.*/__PS__/" -e "/EOF/q" \
1201 | _m4 -D__PS__="による検索結果$msg"
1202 echo "(上記入力窓で再検索すると下記の掲示板のみに絞って再検索します)" \
1203 | html p 'class="small"'
1204 # article_s: id=article-id, key='text', val='TEXT'
1205 # article: id=article-id, blogid=blogkd
1206 # blog: id=blog-id, author=LeaderAuthor
1207 # blog_s: id=blog-id, key='title', val='BLOG-TITLE'
1208 # WANT: blog-ROWid,article-id,val(TEXT)
1209 sql2="`sql4readableblogs` -- Extract user-readable blogs
1210 -- 0.3sec
1211 WITH artsm AS (
1212 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
1213 FROM article a
1214 LEFT JOIN
1215 (SELECT ars.id, ctime, text, coalesce(files, '') files
1216 FROM (SELECT id,
1217 max(CASE key WHEN 'ctime' THEN val END) ctime,
1218 max(CASE key WHEN 'text' THEN val END) text
1219 FROM article_s
1220 GROUP BY id) ars
1221 LEFT JOIN
1222 (SELECT id, group_concat(val) files
1223 FROM article_m
1224 WHERE type LIKE 'file:%'
1225 GROUP BY id) arm
1226 ON ars.id=arm.id
1227 ) ar
1228 ON a.id=ar.id
1229 ), ar AS (
1230 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
1231 FROM article a JOIN artsm ON a.id=artsm.id
1232 $authcond
1233 ), bl AS (
1234 SELECT blg.rid, blg.*, blog_s.val TITLE
1235 FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
1236 )
1237 SELECT bl.rid||'+n:all#'||ar.id '',
1238 bl.title TITLE,
1239 (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
1240 substr(ctime, 0, 11) DATE,
1241 substr(content, 0, 78) TEXT
1242 FROM ar JOIN bl
1243 ON ar.blogid=bl.id
1244 WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) $blogcond
1245 ORDER by DATE DESC, TITLE, ctime;"
1246 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
1247 href=\"?replyblog+\1\">VIEW</a></TD>,"
1248 # echo "$sql2" > tmp/sql.out
1249 result=$tmpd/result.$$
1250 cat<<EOF
1251 <table class="b searchart">
1252 `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
1253 </table>
1254 EOF
1255 if [ -s "$result" ]; then
1256 found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT
1257 one=${found%1}
1258 echo "$found match${one:+es} found"
1259 # <a href="?replyblog+39#12345">VIEW</a>
1260 # -> 39,49,55, -> 39,49,55
1261 # -> <input type="hidden" name="bloglist" value="39,49,55">
1262 sed -n "/.*href=.*replyblog\+\([0-9][0-9]*\).*/s//\1/p" "$result" \
1263 | sort | uniq | tr '\n' ',' \
1264 | sed -e 's/,$//' \
1265 -e 's/^/<input type="hidden" name="bloglist" value="/' \
1266 -e 's/$/">/'
1267 else
1268 echo orz...
1269 fi
1270 echo "$sf" | sed "1,/-- EOF/d" # Close <form>
1271 }
1272 listblog() (
1273 # $1={user,group}
1274 qow=`sqlquote "$1"`
1275 cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
1276 cgi_form searchart<<EOF
1277 <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
1278 `cgi_hidden owner $user`
1279 EOF
1280 DT_CHLD=article:blogid DT_QOWNER=$qow \
1281 dumptable html blog 'ctime title heading' "$cond"
1282 )
1284 blog_addentry() {
1285 # $1=GRProwID(if it is a group)
1286 grprowid=`numericalize $1`
1287 rowid=`getpar rowid`
1288 ## err blog_addentry0: rowid=$rowid
1289 if [ -n "$grprowid" ]; then
1290 owner=`getgroupbyid $grprowid`
1291 else
1292 owner=`getpar owner`
1293 fi
1294 htmlowner=`echo $owner|htmlescape`
1295 err blog-add: \$1=$grprowid rowid=$rowid owner=$owner
1296 if isgroup "$owner"; then
1297 if [ -z "$grprowid" ]; then
1298 qgrp=`sqlquote "$owner"` # Inefficient...
1299 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qgrp;"`
1300 fi
1301 groupmode=1 listing=$owner GF_OWNER=$owner
1302 titleguide="[$owner]" guide="[`linkhome $grprowid`]"
1303 GF_ARGS=$(mvteamform "$owner")
1304 else
1305 usermode=1 listing=$user guide="[個人]" titleguide=$guide
1306 fi
1308 title=`getpar title`
1309 if [ -n "$title" ]; then
1310 if [ "$usermode" ]; then
1311 err usermode: user=$user owner=$owner
1312 if [ x"$user" != x"$owner" ]; then
1313 echo "他人の日記は書けません" | html p
1314 return 2
1315 fi
1316 elif [ "$groupmode" ]; then # if write to group log
1317 grp=$owner #\`getpar grp\`
1318 err ismember: $user $grp
1319 if ! ismember "$user" "$grp"; then
1320 echo "(話題作成はこのグループに加入してから)" | html p
1321 return 3
1322 fi
1323 fi
1324 par2table $formdir/blog.def
1325 serial=`getpar serial`
1326 ## err SERIAL: $serial ROWID=$rowid listing=$listing
1327 id=""
1328 if [ -n "$rowid" ]; then
1329 # Here, id becomes NULL when removal of entries at par2table
1330 id=`query "select rowid from blog where rowid=$rowid;"`
1331 elif [ -n "$serial" ]; then
1332 # If new blog leader created, traverse to its head.
1333 id=`query "select rowid from blog where id='$serial';"`
1334 ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
1335 fi
1336 if [ -n "$id" ]; then
1337 ## If modifying existing blog, JUMP to blog_reply
1338 blog_reply $id
1339 return
1340 fi
1341 # Newly created blog comes here:
1342 mv2team=`getpar mv2team`
1343 if [ -n "$mv2team" -a -n "$groupmode" ]; then
1344 # For newly created BLOG, assign team-name if necessary and correct
1345 qmt=`sqlquote "$mv2team"`
1346 qowner=`sqlquote "$owner"`
1347 team=$(query "SELECT val FROM grp_mem_m
1348 WHERE key='team' AND val=$qmt AND gname=$qowner;")
1349 if [ -n "$team" ]; then # If it is valid team name
1350 qtt=`sqlquote "$title"`
1351 # We should acquire newly created blog id from title step by step
1352 thisblog=$(query \
1353 "SELECT id FROM blog_s
1354 WHERE id IN (SELECT id FROM blog_s
1355 WHERE key='owner' AND val=$qowner)
1356 AND key='title' AND val=$qtt;")
1357 if [ -n "$thisblog" ]; then
1358 query "REPLACE INTO blog_s(id, key, type, val)
1359 VALUES('$thisblog', 'team', 'string', $qmt);"
1360 fi
1361 fi
1362 fi
1363 fi
1364 echo "${titleguide}新規話題作成" > $tmpd/title.$$
1365 echo "${guide}新規話題作成" > $tmpd/h1.$$
1366 listblog "$listing" > $tmpd/listblog.$$
1367 genform $formdir/blog.def \
1368 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1369 -D_H1_="spaste(\`$tmpd/h1.$$')" \
1370 -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
1371 -D_DUMPHEAD_="これまでの蓄積" \
1372 -D_FORM_="syscmd(\`cat')" \
1373 -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
1374 $layout/html.m4.html \
1375 $layout/form+dump-whead.m4.html
1376 }
1378 blog_reply() { # Posting to blog article
1379 # $1=rowid $2=control-sequence
1380 rowid=`numericalize $1` # Ensure (already purified in s4.cgi)
1382 if [ -z "$rowid" ]; then
1383 echo "表示する日記番号が未指定です。" | html p
1384 return
1385 fi
1386 title=`getvalbyid blog title $rowid`
1387 owner=`getvalbyid blog owner $rowid`
1388 htmlowner=`echo $owner|htmlescape`
1389 qowner=`sqlquotestr "$owner"`
1390 if [ -z "$title" ]; then
1391 echo "日記番号指定が無効です。" | html p
1392 return
1393 fi
1394 err "blog_reply($$) Started: `gdate +%S.%03N` blogrowid=$rowid"
1395 blog_writable $rowid $user; rc=$?
1396 if [ $rc = 0 ]; then
1397 iswritable=true
1398 else
1399 iswritable=false
1400 if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
1401 isfrozen=true
1402 frozen_class='frozen"'
1403 frozen_flag=$FROZEN_TAG
1404 fi
1405 fi
1406 if isuser "$owner"; then
1407 subtitle="`gecos $owner` さんの話題"
1408 else
1409 grprowid=`query "select rowid from grp where gname=$qowner;"`
1410 subtitle="グループ
1411 <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$htmlowner</a> での話題
1412 `query \"SELECT printf('(チーム:%s)', val)\
1413 FROM blog_s
1414 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
1415 AND key='team';
1416 \"|htmlescape`"
1417 memclass=`grp_getbodyclass "$owner"`
1418 fi
1420 text=`getpar text`
1421 if [ -n "$text" ]; then
1422 if $iswritable; then
1423 ## BEGIN: 2020-06-11 - Post Integrity Check. Disable if it slows down..
1424 blogid=`getpar blogid | tr -c -d 'a-z0-9'`
1425 brid=`query "SELECT rowid FROM blog WHERE id='$blogid';"`
1426 if [ x"$rowid" != x"$brid" ]; then
1427 _id=`getpar id | tr -c -d 'a-z0-9'`
1428 _aid=`query "SELECT rowid FROM article WHERE id='$_id';"`
1429 if [ -z "$_aid" ]; then
1430 echo "掲示板から書き込んで下さい。" | html p
1431 return
1432 fi
1433 fi
1434 ## END:
1435 par2table $formdir/article.def
1436 st=$?
1437 err "blog_reply($$): POSTdone `gdate +%S.%03N` - st=$st title=$title owner=$owner user=$user, blogid=$blogid"
1438 case $st in
1439 0|4)
1440 [ "$st" = "4" ] && act="書込削除"
1441 blog_notify_reply $rowid $user "$text" $act
1442 if [ -n "$grprowid" ]; then
1443 qgrp=$(sqlquote "$owner")
1444 dbsetbyid grp "$owner" wtime "`date '+%F %T'`"
1445 else
1446 dbsetbyid user "$user" wtime "`date '+%F %T'`"
1447 fi
1448 ;;
1449 esac
1450 else
1451 if $isfrozen; then
1452 title="$title(凍結板につき書き込み不可)"
1453 else
1454 title="$title(加入してないので書き込み不可)"
1455 fi
1456 fi
1457 fi
1458 def=$formdir/article.def
1459 echo "$title" | htmlescape > $tmpd/title.$$
1460 echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
1461 ${BLOG_SHOW:-blog_showentry} blog $rowid "$2" \
1462 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1463 -D_H1_="spaste(\`$tmpd/title.$$')" \
1464 -D_BODYCLASS_=general"${memclass:+ $memclass}" \
1465 -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
1466 -D_FORM_='' \
1467 -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
1468 $layout/html.m4.html $layout/form+dump-whead.m4.html
1469 err "blog_reply($$) Finished: `gdate +%S.%03N` user=$user owner=[$owner] title=[$title]"
1470 }
1472 blog_reply_article() { # Direct link to article in some blog
1473 arid=${1:-0} # Already sanitized to digits
1474 brid=`query "SELECT rowid FROM blog WHERE \
1475 id=(SELECT blogid FROM article WHERE rowid=$arid);"`
1476 if [ -n "$brid" ]; then
1477 newurl="?replyblog+$brid#$arid"
1478 echo "Refresh: 0; $newurl"; echo
1479 exit 0
1480 else
1481 contenttype; echo
1482 echo "無効な記事番号です." | html p
1483 fi
1484 }