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