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