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