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