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