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