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