s4
view s4-blog.sh @ 900:1fa8b4440f8f
Update button changed to ajax
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Sat, 02 Jan 2021 19:13:27 +0900 |
| parents | 411ce55c0dae |
| children | 78bed4445148 |
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}Reload">
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|htmlescape`
766 state=`getvalbyid blog state $brid|htmlescape`
767 tt="handout_$brid"
768 [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen=""
769 if [ -z "$CATCSV" ]; then
770 echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a>$frozen</h2>"
771 lshandoutsub "$owner" $brid "$tt"
772 else
773 lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
774 fi
775 tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
776 done
777 fi
778 sql=${SQL:-"SELECT * FROM $tbls;"}
779 if [ -z "$CATCSV" ]; then
780 echo "<hr><h2>総合</h2>"
781 echo "$sql" | lshandout_ulink_table
782 echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">CSV</a>)</h2>"
783 printf ".mode csv\n.header ON\n" | query
784 echo '<pre class="list">'
785 echo "$sql" | query | sed 's/^"[0-9]* /"/'
786 echo "</pre>"
787 echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>"
788 else
789 contenttype "Application/CSV"
790 printf ".mode csv\n.header ON\n" | query >/dev/null
791 fn=report-count.csv
792 printf 'Content-Disposition: filename="%s"\n' "$fn"
793 outfile=$tmpd/out-$$.csv
794 echo "$sql" | query | sed 's/^"[0-9]* /"/' > $outfile
795 echo "Content-Length: " `cat $outfile | wc -c`; echo
797 cat $outfile
798 exit 0
799 fi
800 printf ".mode list\n.header OFF\n.separator |\n" | query
801 }
802 lshandoutsub() {
803 # $1=owner $2=rowid of blog &optional $3=temp_table name
804 qgname=`sqlquote "$1"`
805 if isgroup "$1"; then
806 sample="(select user from grp_mem where gname=$qgname)"
807 else
808 sample="(select distinct author as user from arts)"
809 echo "(集計は板への投稿者のみ)" | html p
810 fi
811 tmpname="${3:-handout_$2}"
812 sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS
813 with arts as (select id,author from article \
814 where blogid=(select id from blog where rowid=$2))\
815 select (select rowid from user where name=c0.user)||' '|| \
816 (select gecos from gecoses where name=c0.user) as 'メンバー',\
817 substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\
818 sum(case when c1.key is not null then 1 else 0 end)\
819 as '[$title] コメント記入',\
820 sum(case when c2.key is not null then 1 else 0 end)\
821 as '[$title] ファイルの提出'\
822 from $sample c0 \
823 left join (select id,author from arts) a\
824 on c0.user=a.author\
825 left join (select id,key from article_s where key='text') c1\
826 on a.id=c1.id left join (select id,key from article_m ) c2\
827 on c1.id=c2.id group by c0.user order by c0.user;\
828 \
829 SELECT * FROM $tmpname;"
830 # err ishandoutsub: sql="$sql"
831 echo "$sql" | lshandout_ulink_table
832 }
833 gethandout() {
834 # $1=rowid of blog
835 rid=`numericalize "$1"`
836 test x"$2" = x"by_uname" && by_uname="$2"
837 blog_writable $rid $user
838 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
839 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
840 contenttype; echo
841 echo "メンバー以外は利用できません。" | html p; return
842 fi
843 # Here, this blog is writable by $user
844 mode=`getvalbyid blog mode $1`
845 owner=`getvalbyid blog owner $1`
846 blogauthor=`getvalbyid blog author $1`
847 isopenblogauthor=false
848 if [ x"$user" = x"$owner" ]; then
849 : OK
850 elif isgrpowner "$user" "$owner"; then
851 : OK
852 elif [ x"$blogauthor" = x"$user" ]; then
853 # Non-admin Author of blog cannot do gethandout() in report-closed mode
854 # for avoidance the risk of fake report-closed blog.
855 case "$mode" in # Only report-open can be handled by blog author
856 *open*) isopenblogauthor=true ;;
857 esac
858 else
859 contenttype; echo
860 echo "グループ管理者のみ取得できます。" | html p; return
861 fi
862 copy2csv=false
863 blogid=`getvalbyid blog id $1`
864 isgroup "$owner" && isgroup=true || isgroup=false
865 isgrpowner "$user" "$owner" && isgrpadmin=true || isgrpadmin=false
867 i=0
868 midfile=$tmpd/midfile
869 bd=$tmpd/archive.$$
870 mkdir $bd
871 case "$mode" in
872 *quiz*)
873 copy2csv=true ;;
874 *enquete*)
875 copy2csv=true
876 csvline=`getvalbyid blog heading $1 | grep "..*,." | head -1`
877 # Create CSV-base table for questionnaire
878 # If heading in blog_s has at least 1 CSV line,
879 # we take the line as column list.
880 # Otherwise we produce two column CSV as below:
881 # USER,ANSWER
882 query "DROP TABLE IF EXISTS tmp_q;"
883 if [ -n "$csvline" ]; then
884 query <<-EOF
885 CREATE TEMPORARY TABLE tmp_q("user", $csvline);
886 EOF
887 if [ $? != 0 ]; then
888 contenttype; echo
889 cat <<-EOF | html p; exit
890 掲示板のヘッダにあるCSV定義が不正でCSV出力できません。
891 $csvline
892 空白なしの項目名を半角カンマ区切りで1行で書いてください。
893 EOF
894 fi
895 else
896 query <<-EOF
897 CREATE TEMPORARY TABLE tmp_q(user text PRIMARY KEY, answer);
898 EOF
899 fi
900 esac
901 if $copy2csv; then
902 mkdir $bd/$rid
903 outcsv=$bd/$rid/migrate-$rid.csv
904 fullcsv=$bd/$rid/all-text-full-$rid.csv
905 sq "$db" <<-EOF | tr '|' ',' > $outcsv
906 SELECT author as "USER",
907 replace(val, x'0a', ',') as "${csvline:-ANSWER}"
908 FROM article a JOIN article_s s ON a.id=s.id
909 AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
910 AND s.key='text';
911 EOF
912 sq "$db" <<-EOF > $fullcsv
913 .mode csv
914 .head 1
915 SELECT author as "ユーザ",
916 (SELECT gecos FROM gecoses g WHERE author=g.name) as "表示名",
917 val as "テキスト"
918 FROM article a JOIN article_s s ON a.id=s.id
919 AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
920 AND s.key='text';
921 EOF
922 fi
923 query <<-EOF > $midfile # Using tempfile for quick db-unlock
924 SELECT a.rowid, a.id artid, a.author, hex(s.val)
925 FROM article a JOIN article_s s ON a.id=s.id
926 WHERE blogid=(SELECT id FROM blog WHERE rowid=$rid)
927 ORDER BY a.rowid;
928 EOF
929 cat $midfile | while IFS='|' read rowid artid author text; do
930 $isgrpowner || $isopenblogauthor \
931 || isfilereadable $user article_s $rowid || continue
932 if [ "$by_uname" ]; then
933 dir=`printf $bd/%d/%s "$rid" "$author"`
934 else
935 dir=`printf $bd/%d/%06d "$rid" "$rowid"`
936 fi
937 txt=`printf %06d $rowid`.txt
938 test -d "$dir" || mkdir -p "$dir"
939 echo "$author" > "$dir"/Author.txt
940 echo "$text" | unhexize > "$dir/$txt"
941 i=0
942 query "SELECT m.rowid, m.val FROM article_m m \
943 WHERE id='$artid' AND m.key IN ('image', 'document', 'binary');" \
944 | while IFS='|' read mrowid filename; do
945 i=$((i+1))
946 if [ "$by_uname" ]; then
947 outfile=`printf "%s/%06d-%s" "$dir" $rowid "$filename"`
948 else
949 outfile=`printf "%s/%02d-%s" "$dir" $i "$filename"`
950 fi
951 query "SELECT quote(bin) FROM article_m WHERE rowid=$mrowid;" \
952 | unhexize > "$outfile"
953 done
954 done
955 if [ ! -d $bd/$rid ]; then
956 contenttype; echo
957 echo "取得できるファイルがありませんでした。" | html p
958 return
959 fi
961 if $copy2csv; then
962 query <<-EOF > $bd/$rid/all-text-1stline-$rid.csv
963 .mode csv
964 .head 1
965 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_q("user", "TEXT");
966 .import $outcsv tmp_q
967 SELECT * FROM tmp_q;
968 .mode list
969 .head 0
970 EOF
971 fi
972 err "BDLIST: `ls -l $bd`"
973 arcname=archive-$rid.tar.gz
974 ### outstdout=true
975 (cd $bd
976 # query() CANNOT BE used in this subshell
977 if [ "$outstdout" ]; then
978 cat <<-EOF
979 Content-type: application/x-gzip
980 Content-Disposition: filename="$arcname"
982 EOF
983 tar zcf - $rid
984 return
985 else
986 tar zcf .archive.tar.gz $rid && mv .archive.tar.gz "$arcname"
987 err Creating tar archive "`ls -l "$arcname"`"
988 fi
989 )
990 arcfile=$bd/$arcname
991 echo "Content-type: application/x-gzip"
992 echo "Content-Length: `cat $arcfile|wc -c`"
993 echo "Content-Disposition: filename=\"$arcname\""
994 echo
995 cat $arcfile
996 }
997 blogseen() { # $1 = blogid
998 blogid=${1%%[!0-9]*}
999 if [ -z "$blogid" ]; then
1000 echo "Invalid blog id" | html p; exit
1001 fi
1002 blog_writable "$blogid" "$user"
1003 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
1004 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
1005 echo "メンバー以外は利用できません。" | html p; return
1006 fi
1007 owner=`getvalbyid blog owner $rowid`
1008 qowner=`sqlquotestr "$owner"`
1009 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"`
1010 ge=`gecos "$owner" | htmlescape`
1011 title=`getvalbyid blog title $rowid | htmlescape`
1012 h1="アクセス時刻"
1013 link2board="<a href=\"?replyblog+$rowid\">$title</a>"
1014 link2group="<a href=\"?grp+$grprowid\">$ge</a>"
1015 _m4 -D_TITLE_="$h1" $layout/html.m4.html
1016 echo "$h1" | html h1
1017 echo "[$link2board]@$link2group" | html h2
1018 warn=' class="warn"'
1019 cat <<-EOF
1020 <table class="b">
1021 <tr><th>メンバー</th><th>uname</th><th>最終閲覧時刻</th></tr>
1022 EOF
1023 query <<-EOF |
1024 WITH grpmem as (
1025 SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos
1026 FROM grp_mem
1027 WHERE gname=(SELECT val FROM blog_s
1028 WHERE id=(select id from blog where rowid=$blogid)
1029 AND key='owner')
1030 ), acctime AS (
1031 SELECT user, max(time) atime
1032 FROM tblaccesses
1033 WHERE tbl='blog' AND tblrowid=$blogid
1034 GROUP BY user
1035 )
1036 SELECT g.user,
1037 (SELECT rowid FROM user u WHERE u.name=g.user),
1038 hex(gecos),
1039 atime
1040 FROM grpmem g LEFT JOIN acctime t
1041 ON g.user = t.user
1042 GROUP BY g.user
1043 ORDER BY atime DESC;
1044 EOF
1045 while IFS='|' read u uid hexge time; do
1046 td=${time:+"<td>"} # If the variable time is set, td=<td>
1047 td=${td:-"<td$warn>"} # else td=<td class="warn">
1048 cat <<-EOF
1049 <tr>
1050 <td><a href="?home+$uid">`echo "$hexge"|unhexize|htmlescape`</a></td>
1051 <td>`echo ${u%%@*}|htmlescape`</td>
1052 $td${time:----}</td></tr>
1053 EOF
1054 done
1055 cat <<-EOF
1056 </table>
1057 <p><a href="?replyblog+$rowid">[$title]に戻る</a></p>
1058 </html>
1059 EOF
1060 }
1061 lsmyfile() { # $1(optional)=SortBy
1062 case "$1" in
1063 ""|CTIME-DESC)
1064 by="CTIME" ord="DESC" ;;
1065 CTIME*) by="CTIME" ;;
1066 FILE*) by="FILE" ;;
1067 OWNER*) by="OWNER" ;;
1068 TITLE*) by="TITLE" ;;
1069 esac
1070 case "$1" in
1071 *DESC) ord="DESC" ;;
1072 esac
1073 case "$ord" in
1074 DESC) lkod="" jord="降順" ;;
1075 *) lkod="-DESC" jord="昇順" ;;
1076 esac
1077 sql="select m.val||'/'||m.rowid FILE,
1078 coalesce(
1079 case when (select name from user where name=bs.owner)
1080 is not null
1081 then (select val from user_s where name=bs.owner
1082 and key='gecos')
1083 when (select gname from grp where gname=bs.owner)
1084 is not null
1085 then (select val from grp_s where gname=bs.owner
1086 and key='gecos')
1087 else
1088 null
1089 end,
1090 bs.owner
1091 ) OWNER,
1092 a_s.val CTIME,
1093 ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
1094 from (select rowid,id,val from article_m where id
1095 in (select id from article where author='$user')
1096 and type like 'file:%')
1097 m left join article a on m.id=a.id
1098 left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
1099 left join (select id,
1100 max(case key when 'owner' then val end) as owner,
1101 max(case key when 'title' then val end) as title
1102 from blog_s group by id)
1103 bs on a.blogid=bs.id
1104 left join blog b on bs.id=b.id
1105 where m.val is not null order by $by $ord;"
1106 err lshandoutbyauthor: sql=`echo "$sql"`
1107 title="個人提出ファイル"
1108 _m4 -D_TITLE_=$title $layout/html.m4.html
1109 hra="<a href=\"?lsmyfile+"
1110 hrb="<a href=\"?showattc+article_m+"
1111 hrc="<a href=\"?replyblog+"
1112 (echo '<table class="b">'
1113 echo "$sql"|sq -html -header $db ) \
1114 | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
1115 -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
1116 -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
1117 | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
1118 -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
1119 echo '</table>'
1120 }
1121 getteamcsv() {
1122 gid=`numericalize "$1"`
1123 grp=`getgroupbyid "$gid"`
1124 err gid=$gid grp=$grp
1125 if ! isgrpowner "$user" "$grp"; then
1126 contentytpe 'text/plain; charset="utf-8"'; echo
1127 echo "管理者メンバー以外は利用できません。" | html p; return
1128 fi
1129 fn="team-$gid.csv"
1130 csv="$tmpd/$fn"
1131 err csv=$csv
1132 # We can leave csv mode here because this scripts will exit soon
1133 query <<-EOF
1134 .mode csv
1135 .head 1
1136 .output $csv
1137 SELECT val "ルーム名を事前割り当て", user "メールアドレス"
1138 FROM grp_mem_m
1139 WHERE key='team'
1140 AND gname=(SELECT gname FROM grp WHERE rowid=$gid)
1141 ORDER BY val;
1142 EOF
1143 contenttype 'text/plain; charset="utf-8"'
1144 echo "Content-Disposition: filename=\"$fn\""
1145 echo "Content-Length: " `cat $csv | wc -c`; echo
1146 #echo "Content-Type: " ${type#file:}; echo
1147 cat $csv
1148 exit
1149 }
1150 searchart() {
1151 _m4 -D_TITLE_="検索結果" $layout/html.m4.html
1152 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC
1153 bloglist=`getpar bloglist|sed 's/[^0-9,]//g'`
1154 kwdgrp=""
1155 authcond=""
1156 if [ -z "$kwd" ]; then
1157 echo "検索語を指定してください" | html p; return
1158 fi
1159 if logstart "$searchlog"; then
1160 { echo "kwd=$kwd"
1161 test -n "$bloglist" && echo "bloglist=$bloglist"
1162 } >> $searchlog
1163 logend "$searchlog"
1164 fi
1165 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
1166 # Like '#1234', assume as artID
1167 rowid=$((${kwd#\#} + 0)) # Force to be a number
1168 kc="ar.rowid = $rowid"
1169 else
1170 for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes
1171 ctime=""
1172 if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
1173 # '@<2016-10-10' -> ctime < '2016-10-10'
1174 # '@>=2016-10-10' -> ctime >= '2016-10-10'
1175 # '@2016-10-10' -> ctime GLOB '@2016-10-10'
1176 k=${k#@}
1177 case "$k" in
1178 [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
1179 *) op='GLOB'; ctime="${k##*[><= ]}*" ;;
1180 esac
1181 kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
1182 # Not sure GROUP BY a.blogid is comfortable for searchers...?
1183 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results
1184 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
1185 ctime=`date +%F`
1186 elif n=`expr x"$k" : 'x@\([0-9]*\)days*'` >/dev/null >&2; then
1187 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
1188 elif [ x"$k" = x"@week" ]; then
1189 ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
1190 elif n=`expr x"$k" : 'x@\([0-9]*\)weeks*'` >/dev/null >&2; then
1191 n=$((n * 7))
1192 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
1193 elif [ x"$k" = x"@month" ]; then
1194 ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
1195 elif n=`expr x"$k" : 'x@\([0-9]*\)months*'` >/dev/null >&2; then
1196 ctime=`query "SELECT datetime('now', 'localtime', '-$n month');"`
1197 elif [ x"$k" = x"@year" ]; then
1198 ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
1199 elif n=`expr x"$k" : 'x@\([0-9]*\)years*'` >/dev/null >&2; then
1200 ctime=`query "SELECT datetime('now', 'localtime', '-$n year');"`
1201 fi
1202 if [ -n "$ctime" ]; then
1203 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
1204 else
1205 e=""
1206 case "$k" in
1207 *${likeesc}*) e="" ;; # Giving up char-escaping
1208 *%*|*_*) k=`echo "$k"|sed "s/\([%_]\)/${likeesc}\1/g"`
1209 e=" ESCAPE '$likeesc'" ;;
1210 esac
1211 kc=$kc${kc:+" AND "}"content LIKE '%$k%'$e"
1212 fi
1213 done
1214 fi
1215 kwd=`echo "$kwd"|htmlescape`
1216 owner=`getpar owner`
1217 owner=${owner:-$1}
1218 grid=`getpar grid`
1219 msg=""
1220 if [ -n "$grid" ]; then
1221 grp=`getgroupbyid "$grid"`
1222 qgrp=`sqlquote "$grp"`
1223 cond="WHERE key='owner' AND val=$qgrp"
1224 msg="(`linkhome $grid` グループから)"
1225 elif [ -n "$owner" ]; then
1226 cond="where key='owner' and val='$owner'"
1227 msg="(`linkhome $owner` さんの記録から)"
1228 elif { author=`getpar author`; test -n "$author"; }; then
1229 atptn=`sqlquotestr $author`
1230 #kc="$kc${kc:+ AND }author=$atptn"
1231 authcond="WHERE author=$atptn"
1232 if isuser $author; then
1233 msg="(`linkhome $author` さんの書き込みから)"
1234 fi
1235 fi
1236 if [ -n "$bloglist" ]; then
1237 blogcond="AND bl.rid IN ($bloglist)"
1238 fi
1240 sf=`search_form "$search_form_args" "$kwd" | sed '1d;$d'` # rm <div></div>
1241 echo "$sf" | sed -e "/POST SENTENCE/s/.*/__PS__/" -e "/EOF/q" \
1242 | _m4 -D__PS__="による検索結果$msg"
1243 echo "(上記入力窓で再検索すると下記の掲示板のみに絞って再検索します)" \
1244 | html p 'class="small"'
1245 # article_s: id=article-id, key='text', val='TEXT'
1246 # article: id=article-id, blogid=blogkd
1247 # blog: id=blog-id, author=LeaderAuthor
1248 # blog_s: id=blog-id, key='title', val='BLOG-TITLE'
1249 # WANT: blog-ROWid,article-id,val(TEXT)
1250 sql2="`sql4readableblogs` -- Extract user-readable blogs
1251 -- 0.3sec
1252 WITH artsm AS (
1253 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
1254 FROM article a
1255 LEFT JOIN
1256 (SELECT ars.id, ctime, text, coalesce(files, '') files
1257 FROM (SELECT id,
1258 max(CASE key WHEN 'ctime' THEN val END) ctime,
1259 max(CASE key WHEN 'text' THEN val END) text
1260 FROM article_s
1261 GROUP BY id) ars
1262 LEFT JOIN
1263 (SELECT id, group_concat(val) files
1264 FROM article_m
1265 WHERE type LIKE 'file:%'
1266 GROUP BY id) arm
1267 ON ars.id=arm.id
1268 ) ar
1269 ON a.id=ar.id
1270 ), ar AS (
1271 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
1272 FROM article a JOIN artsm ON a.id=artsm.id
1273 $authcond
1274 ), bl AS (
1275 SELECT blg.rid, blg.*, blog_s.val TITLE
1276 FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
1277 )
1278 SELECT bl.rid||'+n:all#'||ar.id '',
1279 bl.title TITLE,
1280 (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
1281 substr(ctime, 0, 11) DATE,
1282 substr(content, 0, 78) TEXT
1283 FROM ar JOIN bl
1284 ON ar.blogid=bl.id
1285 WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) $blogcond
1286 ORDER by DATE DESC, TITLE, ctime;"
1287 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
1288 href=\"?replyblog+\1\">VIEW</a></TD>,"
1289 # echo "$sql2" > tmp/sql.out
1290 result=$tmpd/result.$$
1291 cat<<EOF
1292 <table class="b searchart">
1293 `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
1294 </table>
1295 EOF
1296 if [ -s "$result" ]; then
1297 found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT
1298 one=${found%1}
1299 echo "$found match${one:+es} found"
1300 # <a href="?replyblog+39#12345">VIEW</a>
1301 # -> 39,49,55, -> 39,49,55
1302 # -> <input type="hidden" name="bloglist" value="39,49,55">
1303 sed -n "/.*href=.*replyblog\+\([0-9][0-9]*\).*/s//\1/p" "$result" \
1304 | sort | uniq | tr '\n' ',' \
1305 | sed -e 's/,$//' \
1306 -e 's/^/<input type="hidden" name="bloglist" value="/' \
1307 -e 's/$/">/'
1308 else
1309 echo orz...
1310 fi
1311 echo "$sf" | sed "1,/-- EOF/d" # Close <form>
1312 }
1313 listblog() (
1314 # $1={user,group}
1315 qow=`sqlquote "$1"`
1316 cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
1317 cgi_form searchart<<EOF
1318 <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
1319 `cgi_hidden owner $user`
1320 EOF
1321 DT_CHLD=article:blogid DT_QOWNER=$qow \
1322 dumptable html blog 'ctime title heading' "$cond"
1323 )
1325 blog_setval() {
1326 # $1=GRProwID $2=key $3=value
1327 # RETURN VALUE(JSON):
1328 # {code: EXIT_CODE, message: MESSAGE}
1329 # This function will be called via ajax control of fetch() suite,
1330 # so we need to return JSON text string and exit directly.
1331 rid=`numericalize $1`
1332 blogowner=`getvalbyid blog owner "$rid"`
1333 contenttype "application/json; charset=utf-8"; echo
1334 if [ -z "$blogowner" ]; then
1335 msg="不当な掲示板です"; code=1
1336 elif ! isgroup "$blogowner"; then
1337 msg="グループのみの操作です"; code=2
1338 elif ! isgrpowner "$user" "$blogowner"; then
1339 msg="グループ管理者のみの操作です"; code=3
1340 else # With full permission
1341 blogid=`query "SELECT id FROM blog WHERE rowid=$rid;"`
1342 dbsetbyid blog "$blogid" "$2" "$3"
1343 code=0
1344 fi
1345 # echo "{\"code\": $code, \"message\": \"foo\"}"; exit
1346 newval=`getvalbyid blog "$2" "$1"`
1347 alert="${msg:+, \"alert\": \"$msg\"}"
1348 json=$(cat <<-EOF
1349 {"code": $code, "$2": "`echo "$newval"|sed 's/"/\\\\"/g'`"$alert}
1350 EOF
1351 )
1352 err blog_setval: returning JSON: "$json"
1353 echo "$json"
1354 exit
1355 }
1357 blog_setfrozen() {
1358 # $1=GRProwID $2=val={ "frozen" | "" }
1359 err blog_setfrozen: getvalbyid-blog-$1=`getvalbyid blog state "$1"`
1360 case `getvalbyid blog state "$1"` in
1361 [Ff][Rr]*) newval="" ;;
1362 *) newval="frozen" ;;
1363 esac
1364 blog_setval "$1" state $newval
1365 }
1367 blog_addentry() {
1368 # $1=GRProwID(if it is a group)
1369 grprowid=`numericalize $1`
1370 rowid=`getpar rowid`
1371 ## err blog_addentry0: rowid=$rowid
1372 if [ -n "$grprowid" ]; then
1373 owner=`getgroupbyid $grprowid`
1374 else
1375 owner=`getpar owner`
1376 fi
1377 htmlowner=`echo $owner|htmlescape`
1378 err blog-add: \$1=$grprowid rowid=$rowid owner=$owner
1379 if isgroup "$owner"; then
1380 if [ -z "$grprowid" ]; then
1381 qgrp=`sqlquote "$owner"` # Inefficient...
1382 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qgrp;"`
1383 fi
1384 groupmode=1 listing=$owner GF_OWNER=$owner
1385 titleguide="[$owner]" guide="[`linkhome $grprowid`]"
1386 GF_ARGS=$(mvteamform "$owner")
1387 else
1388 usermode=1 listing=$user guide="[個人]" titleguide=$guide
1389 fi
1391 title=`getpar title`
1392 if [ -n "$title" ]; then
1393 if [ "$usermode" ]; then
1394 err usermode: user=$user owner=$owner
1395 if [ x"$user" != x"$owner" ]; then
1396 echo "他人の日記は書けません" | html p
1397 return 2
1398 fi
1399 elif [ "$groupmode" ]; then # if write to group log
1400 grp=$owner #\`getpar grp\`
1401 err ismember: $user $grp
1402 if ! ismember "$user" "$grp"; then
1403 echo "(話題作成はこのグループに加入してから)" | html p
1404 return 3
1405 fi
1406 fi
1407 par2table $formdir/blog.def
1408 serial=`getpar serial`
1409 ## err SERIAL: $serial ROWID=$rowid listing=$listing
1410 id=""
1411 if [ -n "$rowid" ]; then
1412 # Here, id becomes NULL when removal of entries at par2table
1413 id=`query "select rowid from blog where rowid=$rowid;"`
1414 elif [ -n "$serial" ]; then
1415 # If new blog leader created, traverse to its head.
1416 id=`query "select rowid from blog where id='$serial';"`
1417 ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
1418 fi
1419 if [ -n "$id" ]; then
1420 ## If modifying existing blog, JUMP to blog_reply
1421 blog_reply $id
1422 return
1423 fi
1424 # Newly created blog comes here:
1425 mv2team=`getpar mv2team`
1426 if [ -n "$mv2team" -a -n "$groupmode" ]; then
1427 # For newly created BLOG, assign team-name if necessary and correct
1428 qmt=`sqlquote "$mv2team"`
1429 qowner=`sqlquote "$owner"`
1430 team=$(query "SELECT val FROM grp_mem_m
1431 WHERE key='team' AND val=$qmt AND gname=$qowner;")
1432 if [ -n "$team" ]; then # If it is valid team name
1433 qtt=`sqlquote "$title"`
1434 # We should acquire newly created blog id from title step by step
1435 thisblog=$(query \
1436 "SELECT id FROM blog_s
1437 WHERE id IN (SELECT id FROM blog_s
1438 WHERE key='owner' AND val=$qowner)
1439 AND key='title' AND val=$qtt;")
1440 if [ -n "$thisblog" ]; then
1441 query "REPLACE INTO blog_s(id, key, type, val)
1442 VALUES('$thisblog', 'team', 'string', $qmt);"
1443 fi
1444 fi
1445 fi
1446 fi
1447 echo "${titleguide}新規話題作成" > $tmpd/title.$$
1448 echo "${guide}新規話題作成" > $tmpd/h1.$$
1449 listblog "$listing" > $tmpd/listblog.$$
1450 genform $formdir/blog.def \
1451 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1452 -D_H1_="spaste(\`$tmpd/h1.$$')" \
1453 -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
1454 -D_DUMPHEAD_="これまでの蓄積" \
1455 -D_FORM_="syscmd(\`cat')" \
1456 -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
1457 $layout/html.m4.html \
1458 $layout/form+dump-whead.m4.html
1459 }
1461 blog_reply() { # Posting to blog article
1462 # $1=rowid $2=control-sequence
1463 rowid=`numericalize $1` # Ensure (already purified in s4.cgi)
1465 if [ -z "$rowid" ]; then
1466 echo "表示する日記番号が未指定です。" | html p
1467 return
1468 fi
1469 title=`getvalbyid blog title $rowid`
1470 owner=`getvalbyid blog owner $rowid`
1471 htmlowner=`echo $owner|htmlescape`
1472 qowner=`sqlquotestr "$owner"`
1473 if [ -z "$title" ]; then
1474 echo "日記番号指定が無効です。" | html p
1475 return
1476 fi
1477 err "blog_reply Started: `gdate +%S.%03N` blogrowid=$rowid"
1478 blog_writable $rowid $user; rc=$?
1479 if [ $rc = 0 ]; then
1480 iswritable=true
1481 else
1482 iswritable=false
1483 if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
1484 isfrozen=true
1485 frozen_class='frozen"'
1486 frozen_flag=$FROZEN_TAG
1487 fi
1488 fi
1489 if isuser "$owner"; then
1490 subtitle="`gecos $owner` さんの話題"
1491 else
1492 grprowid=`query "select rowid from grp where gname=$qowner;"`
1493 subtitle="グループ
1494 <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$htmlowner</a> での話題
1495 `query \"SELECT printf('(チーム:%s)', val)\
1496 FROM blog_s
1497 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
1498 AND key='team';
1499 \"|htmlescape`"
1500 memclass=`grp_getbodyclass "$owner"`
1501 fi
1503 text=`getpar text`
1504 if [ -n "$text" ]; then
1505 if $iswritable; then
1506 ## BEGIN: 2020-06-11 - Post Integrity Check. Disable if it slows down..
1507 blogid=`getpar blogid | tr -c -d 'a-z0-9'`
1508 brid=`query "SELECT rowid FROM blog WHERE id='$blogid';"`
1509 if [ x"$rowid" != x"$brid" ]; then
1510 _id=`getpar id | tr -c -d 'a-z0-9'`
1511 _aid=`query "SELECT rowid FROM article WHERE id='$_id';"`
1512 if [ -z "$_aid" ]; then
1513 echo "掲示板から書き込んで下さい。" | html p
1514 return
1515 fi
1516 fi
1517 ## END:
1518 par2table $formdir/article.def
1519 st=$?
1520 err "blog_reply: POSTdone `gdate +%S.%03N` - st=$st title=$title owner=$owner user=$user, blogid=$blogid"
1521 case $st in
1522 0|4)
1523 [ "$st" = "4" ] && act="書込削除"
1524 blog_notify_reply $rowid $user "$text" $act
1525 if [ -n "$grprowid" ]; then
1526 qgrp=$(sqlquote "$owner")
1527 dbsetbyid grp "$owner" wtime "`date '+%F %T'`"
1528 else
1529 dbsetbyid user "$user" wtime "`date '+%F %T'`"
1530 fi
1531 ;;
1532 esac
1533 else
1534 if $isfrozen; then
1535 title="$title(凍結板につき書き込み不可)"
1536 else
1537 title="$title(加入してないので書き込み不可)"
1538 fi
1539 fi
1540 fi
1541 def=$formdir/article.def
1542 echo "$title" | htmlescape > $tmpd/title.$$
1543 echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
1544 ${BLOG_SHOW:-blog_showentry} blog $rowid "$2" \
1545 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1546 -D_H1_="spaste(\`$tmpd/title.$$')" \
1547 -D_BODYCLASS_=general"${memclass:+ $memclass}" \
1548 -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
1549 -D_FORM_='' \
1550 -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
1551 $layout/html.m4.html $layout/form+dump-whead.m4.html
1552 err "blog_reply Finished: `gdate +%S.%03N` user=$user owner=[$owner] title=[$title]"
1553 }
1555 blog_fetch() {
1556 contenttype "text/plain; charset=utf-8"; echo
1557 err blog_fetch: blog "$@"
1558 blog_reply "$@"
1559 # blog_showentry blog "$@"
1560 }
1562 blog_reply_article() { # Direct link to article in some blog
1563 arid=${1:-0} # Already sanitized to digits
1564 brid=`query "SELECT rowid FROM blog WHERE \
1565 id=(SELECT blogid FROM article WHERE rowid=$arid);"`
1566 if [ -n "$brid" ]; then
1567 newurl="?replyblog+$brid#$arid"
1568 echo "Refresh: 0; $newurl"; echo
1569 exit 0
1570 else
1571 contenttype; echo
1572 echo "無効な記事番号です." | html p
1573 fi
1574 }