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