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