s4
view s4-blog.sh @ 493:d879d9d1acb7
Assume quiz board as report because we need handout-summary on quiz.
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Sat, 16 Jun 2018 13:25:55 +0900 |
| parents | 9f5b08b205bf |
| children | 59cd8278a5b5 |
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_notify_reply() (
31 # $1=blogid $2=ReplyingUser $3=WrittenText $4(optional)=Action
32 blogid="${1%%[!A-Z0-9a-z_]*}"
33 blogowner=`getvalbyid blog owner "$blogid"`
34 blogtitle=`getvalbyid blog title "$blogid"`
35 blogurl="$urlbase?replyblog+$blogid"
36 action=${4:-書き込み}
37 mode=`getvalbyid blog notify "$blogid"`
38 isgroup "$blogowner" && _isgroup=true || _isgroup=false
39 ### EXCEPT=`sqlquote "$user"` ## User should receive to feal some annoyance
40 case $mode in
41 admin)
42 if $_isgroup; then
43 emails=`getgroupadminmails $blogowner`
44 else
45 emails=`collectemail $blogowner`
46 fi
47 notifyto=`getpar notifyto`
48 if [ -n "$notifyto" ]; then
49 emails=$emails" `email4groupbyuid \"$blogowner\" $notifyto`"
50 fi
51 ;;
52 no) emails="" ;;
53 *) team=`query "SELECT val FROM blog_s
54 WHERE id=(SELECT id FROM blog WHERE rowid=$blogid)
55 AND key='team';"`
56 # team cannot get `getvalbyid blog team "$blogid"` because it's not
57 # defined in blog.def. Yes, it is Illegal USE!!
58 emails=`TEAM=$team collectemail $blogowner` ;;
59 esac
60 ## 2017-0210 Respond to the direct reply mark such as: >#1234
61 replymark=`echo "$3"|nkf -w -Z0|grep '^ *>#'`
62 authgecos=`gecos $2`
63 if [ -z "$4" -a -n "$replymark" ]; then
64 # If the action is new subscription($4="") and has ">#123" marks...
65 ids=`echo "$replymark"|sed 's/[^#0-9]*#\([0-9]*\)[^#0-9]*/\1 /g'`
66 ids=`echo $ids|tr -dc '[0-9 ]'|tr ' ' ','`
67 # -> 123,345,347
68 unames=`query "SELECT distinct author FROM article \
69 WHERE rowid in ($ids)\
70 AND blogid=(SELECT id FROM blog WHERE rowid=$blogid);"`
71 if [ -n "$unames" ]; then
72 e4g=$(if $_isgroup; then
73 email4group "$blogowner" $unames
74 else
75 for u in $unames; do
76 collectemail $u
77 done
78 fi)
79 emails=$emails" $e4g"
80 for e in $unames; do
81 g=`gecos $e`
82 whom=$whom"${whom:+,}${g:-$e}さん"
83 done
84 action="${whom}への返信"
85 fi
86 else
87 [ x"$2" = x"$blogowner" ] && return # If author=blogowner, unnecessary
88 fi
89 test -z "$emails" && return
90 err notify: user=$user Admins=`getgroupadmins $blogowner` Mode=$mode Emails="[$emails]"
91 SMAIL_TO="`echo "$blogowner" | nkf -jM | tr -d '\n'` readers <$admin>" \
92 smail "$emails" "${action}通知 $urlbase"<<EOF
93 [$blogtitle]板に${action}がありました。
94 ※このメイルに返信しても通知者には伝わりません(管理者宛になる)。
95 場所: $blogurl (返信先)
96 所有: $blogowner
97 題目: $blogtitle
98 筆者: $authgecos
99 内容:
100 `echo "$3"|sed 's/^/> /'`
101 EOF
102 )
104 blog_showentry() {
105 # $1=table $2=rowid
106 # if [ -n "$2" ]; then
107 # if [ -n "$imgcached" ]; then
108 # bstmpdir=$tmpdir/$imgcached/$thumbxy
109 # else
110 # bstmpdir=$tmpd
111 # # tmpd=`mktempd`
112 # # tmpfiles=$tmpfiles" $tmpd"
113 # fi
114 # fi
115 td=`getcachedir "article/$2"`
116 [ -d "$td" ] || mkdir -p $td
117 tbl=${1%%[!A-Z0-9a-z_]*} rowid=${2%%[!A-Z0-9a-z_]*}
118 err blow_showentry: rowid=$rowid, '$2'=$2 user=$user
119 ts=${tbl}_s tm=${tbl}_m
120 at=article as=article_s am=article_m
121 serial=$(($(date +%s)-1420038000))s$$
122 blog_writable $rowid $user
123 rc=$?
124 if [ $rc = 0 ]; then
125 iswritable=true
126 ismem=true
127 else
128 iswritable=false
129 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ]; then
130 ismem=false
131 else
132 ismem=true
133 fi
134 fi
135 # This function grasps blog entry definiton directly.
136 # blog: id
137 # blog_s: title,ctime,heading
138 # blog_m: *article
140 blogowner=`getvalbyid blog owner "$2"`
141 isgroup "$blogowner" && isgroup=true || isgroup=false
142 isgrpadmin=false # Reversed later (*1)
144 # 2015-10-05 check readable
145 if ! $iswritable; then
146 # err blogowner=$blogowner
147 if $isgroup; then
148 regmode=`getgroupattr $blogowner regmode`
149 # err regmode=$regmode
150 if [ x"$regmode" = x"moderated" ]; then
151 # if ! ismember $user $blogowner; then
152 if ! $ismem; then
153 echo "加入してからどうぞ" | html p
154 return
155 fi
156 fi
157 fi
158 else # if writable
159 isgrpowner "$user" "$blogowner" && isgrpadmin=true # (*1)
160 fi
161 case `getvalbyid blog notify "$2"` in # "all", "admin" or "no" (or NULL)
162 admin) notifyto=1 ;;
163 *) notifyto="" ;;
164 esac
166 # err "SELECT id from $tbl where rowid=$rowid"
167 id=`query "select id from $tbl where rowid=$rowid;"`
168 #err id=$id
169 #err "select val from $ts where key='title' and id='$id';"
172 #(1)Display root article
173 cat<<EOF
174 <form class="replyblog" action="$myname?replyblog+${rowid}#bottom" method="POST" enctype="multipart/form-data">
175 <table class="bloghead">
176 EOF
178 href="<a href=\"?editheading+$rowid\" accesskey=\"e\" title=\"E\"> 編集 </a>"
179 if $ismem; then
180 case `getvalbyid blog mode $rowid` in
181 *report*|*quiz*)
182 href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"L\"> 提出状況 </a>"
183 ;;
184 esac
185 href3="(<a href=\"?gethandout+$rowid\" accesskey=\"f\" title=\"F\">ファイル取得</a>)"
186 fi
187 href4='<a href="#bottom" accesskey="b" title="B"> 末尾へ</a>'
188 $isgrpadmin &&
189 href5="<a href=\"?blogseen+$rowid\" accesskey=\"s\" title=\"S\"> 読刻</a>"
190 quizmodefile=$td/quiz; rm -f "$quizmodefile" # XXX: Global state
192 query<<-EOF |
193 SELECT coalesce((SELECT "yes" FROM blog
194 WHERE rowid=$rowid AND author='$user'),
195 ''),
196 max(CASE key WHEN 'ctime' THEN val END) ctime,
197 max(CASE key WHEN 'heading' THEN hex(val) END) heading,
198 CASE (SELECT val FROM $ts WHERE key="mode" AND id="$id")
199 WHEN 'report-closed' THEN 'レポート提出用(closed)'
200 WHEN 'report-open' THEN 'レポート提出用(open)'
201 WHEN 'quiz' THEN 'クイズ'
202 ELSE ''
203 END
204 FROM $ts WHERE id='$id' GROUP BY id;
205 EOF
206 { IFS='|' read edit ctime hexhead blogtype
207 cat<<-EOF
208 <tr><td>${edit:+$href }$ctime $blogtype $href2$href3 $href4 $href5</td></tr>
209 <tr class="preface${frozen_class:+ }$frozen_class">
210 <td>`echo "$hexhead"|unhexize|hreflink|minitbl`</td></tr>
211 </table>
212 EOF
213 case "$blogtype" in
214 "クイズ")
215 echo "クイズモードは本人と管理者の書き込みのみが表示されます。"
216 ;;
217 esac | html p 'class="warn"'
218 echo '<table class="blog_replies">'
219 if [ x"$blogtype" = x"クイズ" ]; then
220 if $isgroup; then
221 if ! isgrpowner "$user" "$blogowner"; then
222 qgrp=`sqlquote "$blogowner"`
223 cat<<-EOF > $quizmodefile
224 AND (author IN (SELECT user FROM grp_adm WHERE gname=$qgrp)
225 OR
226 author='$user')
227 EOF
228 fi
229 else # if user's blog
230 if [ x"$user" != x"$blogowner" ]; then
231 cat<<-EOF > $quizmodefile
232 AND author IN ('$blogowner', '$user')
233 EOF
234 fi
235 fi
236 fi
237 }
238 lkhome="<a href=\"$myname?home" lke='">'
239 lkedit="<a href=\"$myname?editart"
240 hlink="$myname?home" elink="$myname?editart"
241 catlink="$myname?showattc+article_m"
242 deficon="img/file-icon.png"
243 # 2016-08-15 Newer flag introduced
244 atime=`query "SELECT time FROM acclog
245 WHERE tbl='blog' AND tblrowid=$rowid AND user='$user';"`
246 iconcleaner=$tmpd/iconcleaner.$$
247 [ -s $quizmodefile ] && cond_qz=`cat $quizmodefile`
248 # *** DO NOT USE query(), use "sq $db" instead here ***
249 # because the next block in pipe line uses query() repeatedly.
250 sq $db<<EOF |
251 WITH a_s AS (
252 SELECT id,
253 max(CASE key WHEN 'ctime' THEN val END) TIME,
254 max(CASE key WHEN 'text' THEN val END) TEXT
255 FROM article_s
256 GROUP by id
257 )
258 SELECT a.id,
259 CASE author
260 WHEN '$user' THEN a.rowid||'+'||$rowid
261 ELSE ''
262 END edit,
263 CASE -- 「通知送信」ボタンの有無
264 WHEN '$notifyto' = '' THEN '' -- 不要モードならなし
265 WHEN '$user' = author THEN '' -- 筆者自身ならなし
266 ELSE "yes"
267 END notify,
268 (SELECT rowid FROM user WHERE name=author) user_rid,
269 coalesce((SELECT val FROM user_s
270 WHERE name=author AND key='gecos'),
271 author) uname,
272 (SELECT val FROM user_s WHERE name=author AND key='$iconcachekey')
273 icon,
274 a.rowid,
275 s.TIME,
276 CASE WHEN s.TIME > '$atime' THEN 'new' ELSE '' END newer,
277 hex(s.TEXT),
278 (SELECT group_concat(rowid||':'||length(bin)||':'||hex(val), ' ')
279 FROM article_m
280 WHERE id=a.id AND key='image') imxgids
281 FROM (select rowid,id,author from article
282 where blogid in
283 (select id from blog where rowid=$rowid)
284 $cond_qz) a
285 LEFT JOIN
286 a_s s
287 ON a.id=s.id;
288 EOF
289 while IFS='|' read id edit notify uid uname icon aid tm new hte imgids; do
290 cachefile="$td/$id.row.html"
291 stampfile="$td/$id.row.stamp"
292 editlink="${edit:+<a href="$elink+$edit">編集</a> }"
293 nt="<label style=\"font-size: 70%;\"><input type=\"checkbox\"\
294 name=\"notifyto\" value=\"$uid\">返信通知送信</label>"
296 # First, check the availability of user-icon.
297 # If not existent, clear and reset row cache by rm $stampfile
298 if [ ! -s "$icon" ]; then
299 rm -f "$stampfile"; unset stampfile
300 fi
301 if test -s "$stampfile" &&
302 test -s "$cachefile" &&
303 { ts=`cat "$stampfile"`; test -n "$ts"; } &&
304 test "$ts" '>' "$tm" && # Cache timestamp is newer
305 test "$stampfile" -nt "$icon"; then # UserIcon is older
306 : Nothing to do
307 else
308 { ######## New ROW creation begins here ######## >$cachefile
309 tdcls="__NEWCLS__repatt"
310 if [ -s "$icon" ]; then
311 icfn=`echo "$icon"|htmlescape`
312 picon="<p class=\"proficon\"><a href=\"$hlink+$uid\"><img src=\"$icfn\"></a></p>"
313 else
314 echo "DELETE FROM user_s WHERE key='$iconcachekey' AND
315 val=`sqlquotestr \"$icon\"`;" >> $iconcleaner
316 picon=""
317 fi
319 cat<<EOF
320 <tr id="$id">
321 <td class="$tdcls">${picon}__EDIT__<a href="#$aid">#$aid</a>
322 <a href="$hlink+$uid">$uname</a>
323 $tm
324 <__NOTIFY__></td>
325 EOF
326 echo -n "<td id=\"$aid\" class=\"repl\">"
327 echo "$hte"|unhexize|htmlescape|hreflink|minitbl
328 usecache='' tsfile=$td/$id.stamp
329 for i in $imgids; do
330 mrid=${i%%:*}; i=${i#*:}; sz=`size_h ${i%%:*}`
331 fn=`echo "${i#*:}"|unhexize`
332 fnb=$fn"(${sz})"
333 case "$fn" in
334 *.[Pp][Nn][Gg]|*.[Jj][Pp][Gg]|*.[Jj][Pp][Ee][Gg]|*.[GgTt][Ii][Ff])
335 # fmt=${fn##*.} # convert - jpg:- is slow...why
336 case "$fn" in
337 *.[Pp][Nn][Gg]) fmt=png ;;
338 *.[Gg][Ii][Ff]) fmt=gif ;;
339 *) fmt=jpeg ;;
340 esac
341 outfile=$td/$mrid-${fn%.*}.$fmt
342 #err fn=$fn outfile=$outfile
343 #err "usecache=$usecache `ls -l $outfile`"
344 #err tm=$tm
345 #err tsfile=$tsfile=`cat $tsfile`
346 if [ -s "$outfile" ] && # $outfile should be > 0
347 { [ "$usecache" ] || # And usecache flag is true, or...
348 { [ -s "$tsfile" ] && [ x"`cat $tsfile`" = x"$tm" ]
349 };}; then
350 usecache=1 # Set usecache flag on
351 cat<<-EOF
352 <a href="$catlink+$mrid"><img src="$outfile">
353 $fnb</a>
354 EOF
355 # !!NOTE!! Create row stamp ONLY WHEN imgcache is active
356 else
357 query "SELECT hex(bin) FROM article_m WHERE rowid=$mrid;" \
358 | unhexize \
359 | convert -define ${fmt}:size=100x100 -resize 100x100'>' \
360 - ${fmt}:- \
361 | tee "$outfile" \
362 | hexize \
363 | sed -e 's/\(..\)/%\1/g' \
364 -e "s|^|<a href=\"$catlink+$mrid\"><img src=\"data:image/$fmt,|" \
365 -e "s|\$|\">$fnb</a>|"
366 unset stampfile # img data stream is not suitable to cache
367 echo $tm > $tsfile
368 fi
369 ;;
370 *)
371 echo "<a href=\"$catlink+$mrid\"><img src=\"$deficon\">$fnb</a>"
372 ;;
373 esac
374 done
375 echo "</td></tr>"
376 } > "$cachefile" ######## New ROW Creation Ends here ########
377 test -n "$stampfile" && date "+%F %T" > $stampfile
378 fi
379 # Printing a cached row
380 sed -e "/^<td class=/s/__NEWCLS__/$new${new:+ }/" \
381 -e "/^<td class=/s,__EDIT__,$editlink," \
382 -e "/^<__NOTIFY__>/s,,${notify:+$nt}," \
383 $cachefile
384 done
386 help="=== コメントに使用できる特殊記法 ===
387 行頭に href=URL でURLへのリンク
388 行頭に iframe=URL でURL先を開く iframe
389 [[#記事番号]] でs4内の記事番号に飛ぶリンク
390 [[#検索キーワード]] でs4内の記事検索(記号はいくつか使えない)
391 [[URL]] でURLへのリンク
392 [[URL|文字列]] でアンカー文字列を指定してのURLリンク
393 {{画像URL}} でインライン画像
394 {{画像URL|幅}} でピクセル幅を指定したインライン画像
395 {{{URL}}} でURL先を開く iframe
396 {{{URL|高さ}}} でピクセル高さを指定した iframe
397 ## 大見出し
398 ### 中見出し
399 #### 小見出し
400 |*見出し列|列2|列3… と行頭から始まる縦棒区切り行を続けて表
401 ' *語群* ' で強調(両側の空白必要、** でもっと強調。*の代わりに _ でも可)"
402 touchhelp="${touchpanel:+<p class=\"help\">$help</p>}"
403 textform='<div class="fold">
404 <input type="checkbox" id="cmt" checked><label
405 accesskey="c" title="C" for="cmt">コメントする</label><div>
406 <table class="b">
407 <tr><td><textarea name="text" cols="72" rows="4" title="'"$help"'">
408 </textarea>'"$touchhelp"'</td></tr>
409 <tr><td>添付ファイル:
410 <input type="file" name="image"'" $file_accept multiple></td></tr>"'
411 </table>
412 <input type="submit" value="送信">
413 <input type="reset" value="リセット"></div></div>
414 '
415 cat<<-EOF
416 </table> <!-- end of s4-blog:blog_showentry() main table -->
417 <p class="update_link"><a
418 href="?reload/$rowid" accesskey="r" title="R">再読込</a> / <a
419 href="#title" id="bottom" accesskey="t" title="T">先頭へ</a></p>
420 EOF
421 $iswritable && cat<<-EOF
422 <div class="blogcomment">
423 <input type="hidden" name="blogid" value="$id">
424 <input type="hidden" name="id" value="`genserial`">
425 <input type="hidden" name="stage" value="replyblog">
426 $textform
427 </div>
428 </form> <!-- End of s4-blog:blog_showentry() main form -->
429 EOF
430 # Clean up orphaned icon cache
431 [ -s $iconcleaner ] && query ".read '$iconcleaner'"
432 # Record access log
433 acclog blog $rowid
434 }
436 lshandout() {
437 # $1=rowid of blog
438 blog_writable $1 $user
439 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
440 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
441 echo "メンバー以外は利用できません。" | html p; return
442 fi
443 time=`getvalbyid blog ctime $1|colrm 11`
444 owner=`getvalbyid blog owner $1`
445 title=`getvalbyid blog title $1`
446 ge=`gecos $owner`
447 fh=$tmpd/formhead
448 echo "$time [$title]@${ge:-$owner}" > $fh
449 lshandoutsub $owner "$@" \
450 |_m4 -D_TITLE_="提出状況" \
451 -D_FORMHEAD_="syscmd(cat $fh)" \
452 -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \
453 $layout/html.m4.html $layout/form+dump-whead.m4.html
454 gn=`echo $owner|htmlescape`
455 echo "<p><a href=\"?lshandoutall+$1\">グループ $gn すべてのレポート板集計</a></p>"
456 }
457 gethandoutcsv() {
458 # contenttype; echo
459 CATCSV=1 lshandoutall "$1"
460 }
461 gethandoutcsv2() {
462 # contenttype; echo
463 SQL=$(cat<<-EOF) gethandoutcsv "$1"
464 WITH this_blog_articles AS (
465 SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime
466 FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid
467 ), text_or_file AS (
468 SELECT bid, author, title, ctime, 'text' shu, count(val) cnt
469 FROM this_blog_articles tba, article_s s
470 ON tba.aid=s.id
471 WHERE key='text'
472 GROUP by bid, author
473 UNION
474 SELECT bid, author, title, ctime, 'file' shu, count(val) cnt
475 FROM this_blog_articles tba, article_m m
476 ON tba.aid=m.id
477 WHERE key='image'
478 GROUP by bid, author
479 ), count_list AS (
480 SELECT author,
481 substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit,
482 cnt
483 FROM text_or_file
484 )
485 SELECT gecos "名前",
486 substr(author, 1, instr(author, '@')-1) "uname",
487 unit,
488 cnt "post"
489 FROM count_list cl JOIN gecoses g ON cl.author=g.name;
490 EOF
491 }
492 lshandout_ulink_table() {
493 # NO Args. Read stdin as SQL
494 echo '<table class="b td3rr td3evw">'
495 hrb="<a href=\"?home+"
496 # echo "$sql" | sq -header -html $db \ # Formerly, this is called via sq()
498 printf ".mode html\n.header ON\n" | query
499 cat | query \
500 | sed -e "s,\(<TR><TD>\)\([^ ]*\) \(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
501 echo '</table>'
502 printf ".mode list\n.header OFF\n" | query
503 }
504 lshandoutall() {
505 # $1=rowid of blog
506 blog_writable $1 $user
507 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
508 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
509 echo "メンバー以外は利用できません。" | html p; return
510 fi
511 rowid=$(($1 + 0))
512 owner=`getvalbyid blog owner $1`
513 qowner=`sqlquotestr "$owner"`
515 query<<-EOF
516 CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS
517 WITH blog_owner_mode AS (
518 SELECT id,
519 blog.rowid brid,
520 max(CASE key WHEN 'owner' THEN val END) owner,
521 max(CASE key WHEN 'mode' THEN val END) mode,
522 max(CASE key WHEN 'title' THEN val END) title,
523 max(CASE key WHEN 'ctime' THEN val END) ctime
524 FROM blog NATURAL JOIN blog_s
525 GROUP BY id
526 )
527 SELECT id, brid, title, ctime FROM blog_owner_mode
528 /* WHERE owner=$qowner AND mode LIKE '%report%'; */
529 WHERE owner=$qowner
530 AND
531 (mode LIKE '%report%' OR mode LIKE '%quiz%');
532 /* ↑これでレポート形式の blogid 一覧を得る */
533 EOF
534 if [ -z "$CATCSV" ]; then
535 _m4 -D_TITLE_="提出状況" $layout/html.m4.html
536 ge=`gecos "$owner"`
537 tbls=""
538 grptxt=`echo "${ge:-$owner}"|htmlescape`
539 echo "<h1>$grptxt 書き込み状況一覧</h1>"
540 fi
541 if [ -z "$SQL" ]; then
542 bridlist=`query "SELECT brid FROM report_type_blogs;"`
543 for brid in $bridlist; do # Skip this loop if $SQL set
544 brid=$(($brid + 0)) # Ensure to be a number
545 [ $brid = 0 ] && continue
546 time=`getvalbyid blog ctime $brid|colrm 11`
547 title=`getvalbyid blog title $brid|htmlescape`
548 state=`getvalbyid blog state $brid|htmlescape`
549 tt="handout_$brid"
550 [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen=""
551 if [ -z "$CATCSV" ]; then
552 echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a>$frozen</h2>"
553 lshandoutsub "$owner" $brid "$tt"
554 else
555 lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
556 fi
557 tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
558 done
559 fi
560 sql=${SQL:-"SELECT * FROM $tbls;"}
561 if [ -z "$CATCSV" ]; then
562 echo "<hr><h2>総合</h2>"
563 echo "$sql" | lshandout_ulink_table
564 echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">CSV</a>)</h2>"
565 printf ".mode csv\n.header ON\n" | query
566 echo '<pre class="list">'
567 echo "$sql" | query | sed 's/^"[0-9]* /"/'
568 echo "</pre>"
569 echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>"
570 else
571 contenttype "Application/CSV"
572 printf ".mode csv\n.header ON\n" | query >/dev/null
573 fn=report-count.csv
574 printf 'Content-Disposition: filename="%s"\n' "$fn"
575 outfile=$tmpd/out-$$.csv
576 echo "$sql" | query | sed 's/^"[0-9]* /"/' > $outfile
577 echo "Content-Length: " `cat $outfile | wc -c`; echo
579 cat $outfile
580 exit 0
581 fi
582 printf ".mode list\n.header OFF\n.separator |\n" | query
583 }
584 lshandoutsub() {
585 # $1=owner $2=rowid of blog &optional $3=temp_table name
586 qgname=`sqlquote "$1"`
587 if isgroup "$1"; then
588 sample="(select user from grp_mem where gname=$qgname)"
589 else
590 sample="(select distinct author as user from arts)"
591 echo "(集計は板への投稿者のみ)" | html p
592 fi
593 tmpname="${3:-handout_$2}"
594 sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS
595 with arts as (select id,author from article \
596 where blogid=(select id from blog where rowid=$2))\
597 select (select rowid from user where name=c0.user)||' '|| \
598 (select gecos from gecoses where name=c0.user) as 'メンバー',\
599 substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\
600 sum(case when c1.key is not null then 1 else 0 end)\
601 as '[$title] コメント記入',\
602 sum(case when c2.key is not null then 1 else 0 end)\
603 as '[$title] ファイルの提出'\
604 from $sample c0 \
605 left join (select id,author from arts) a\
606 on c0.user=a.author\
607 left join (select id,key from article_s where key='text') c1\
608 on a.id=c1.id left join (select id,key from article_m ) c2\
609 on c1.id=c2.id group by c0.user order by c0.user;\
610 \
611 SELECT * FROM $tmpname;"
612 err ishandoutsub: sql="$sql"
613 echo "$sql" | lshandout_ulink_table
614 }
615 gethandout() {
616 # $1=rowid of blog
617 blog_writable $1 $user
618 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
619 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
620 echo "メンバー以外は利用できません。" | html p; return
621 fi
622 i=0
623 bd=$tmpd/archive.$$
624 mkdir $bd
625 query "select m.rowid,author,m.val from article a join article_m m\
626 on a.id=m.id where blogid=(select id from blog where rowid=$1)\
627 and m.key in ('image', 'document', 'binary');" \
628 | while IFS='|' read rowid author filename; do
629 err isfilereadable $user article_m $rowid
630 isfilereadable $user article_m $rowid || continue
631 err ok
632 i=$((i+1))
633 dir=`printf $bd/%03d $i`
634 mkdir $dir
635 query "select quote(bin) from article_m where rowid=$rowid;" \
636 | unhexize > $dir/$filename
637 done
638 if [ ! -d $bd/001 ]; then
639 contenttype; echo
640 echo "取得できるファイルがありませんでした。" | html p
641 return
642 fi
643 (cd $bd
644 ## err cdto$bd; (pwd; ls -lFa) 1>&3
645 tar zcf .archive.tar.gz * && mv .archive.tar.gz archive.tar.gz
646 err Creating tar archive "`ls -l archive.tar.gz`"
647 )
648 arc=$bd/archive.tar.gz
649 echo "Content-type: application/x-gzip"
650 echo "Content-Length: `cat $arc|wc -c`"
651 echo "Content-Disposition: filename=\"archive.tar.gz\""
652 echo
653 cat $arc
654 }
655 blogseen() { # $1 = blogid
656 blogid=${1%%[!0-9]*}
657 if [ -z "$blogid" ]; then
658 echo "Invalid blog id" | html p; exit
659 fi
660 blog_writable "$blogid" "$user"
661 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
662 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
663 echo "メンバー以外は利用できません。" | html p; return
664 fi
665 owner=`getvalbyid blog owner $rowid`
666 qowner=`sqlquotestr "$owner"`
667 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"`
668 ge=`gecos "$owner" | htmlescape`
669 title=`getvalbyid blog title $rowid | htmlescape`
670 h1="アクセス時刻"
671 link2board="<a href=\"?replyblog+$rowid\">$title</a>"
672 link2group="<a href=\"?grp+$grprowid\">$ge</a>"
673 _m4 -D_TITLE_="$h1" $layout/html.m4.html
674 echo "$h1" | html h1
675 echo "[$link2board]@$link2group" | html h2
676 warn=' class="warn"'
677 cat <<-EOF
678 <table class="b">
679 <tr><th>メンバー</th><th>uname</th><th>最終閲覧時刻</th></tr>
680 EOF
681 query <<-EOF |
682 WITH grpmem as (
683 SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos
684 FROM grp_mem
685 WHERE gname=(SELECT val FROM blog_s
686 WHERE id=(select id from blog where rowid=$blogid)
687 AND key='owner')
688 ), acctime AS (
689 SELECT user, max(time) atime
690 FROM tblaccesses
691 WHERE tbl='blog' AND tblrowid=$blogid
692 GROUP BY user
693 )
694 SELECT g.user,
695 (SELECT rowid FROM user u WHERE u.name=g.user),
696 hex(gecos),
697 atime
698 FROM grpmem g LEFT JOIN acctime t
699 ON g.user = t.user
700 GROUP BY g.user
701 ORDER BY atime DESC;
702 EOF
703 while IFS='|' read u uid hexge time; do
704 td=${time:+"<td>"} # If the variable time is set, td=<td>
705 td=${td:-"<td$warn>"} # else td=<td class="warn">
706 cat <<-EOF
707 <tr>
708 <td><a href="?home+$uid">`echo "$hexge"|unhexize|htmlescape`</a></td>
709 <td>`echo ${u%%@*}|htmlescape`</td>
710 $td${time:----}</td></tr>
711 EOF
712 done
713 cat <<-EOF
714 </table>
715 <p><a href="?replyblog+$rowid">[$title]に戻る</a></p>
716 </html>
717 EOF
718 }
719 lsmyfile() { # $1(optional)=SortBy
720 case "$1" in
721 ""|CTIME-DESC)
722 by="CTIME" ord="DESC" ;;
723 CTIME*) by="CTIME" ;;
724 FILE*) by="FILE" ;;
725 OWNER*) by="OWNER" ;;
726 TITLE*) by="TITLE" ;;
727 esac
728 case "$1" in
729 *DESC) ord="DESC" ;;
730 esac
731 case "$ord" in
732 DESC) lkod="" jord="降順" ;;
733 *) lkod="-DESC" jord="昇順" ;;
734 esac
735 sql="select m.val||'/'||m.rowid FILE,
736 coalesce(
737 case when (select name from user where name=bs.owner)
738 is not null
739 then (select val from user_s where name=bs.owner
740 and key='gecos')
741 when (select gname from grp where gname=bs.owner)
742 is not null
743 then (select val from grp_s where gname=bs.owner
744 and key='gecos')
745 else
746 null
747 end,
748 bs.owner
749 ) OWNER,
750 a_s.val CTIME,
751 ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
752 from (select rowid,id,val from article_m where id
753 in (select id from article where author='$user')
754 and type like 'file:%')
755 m left join article a on m.id=a.id
756 left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
757 left join (select id,
758 max(case key when 'owner' then val end) as owner,
759 max(case key when 'title' then val end) as title
760 from blog_s group by id)
761 bs on a.blogid=bs.id
762 left join blog b on bs.id=b.id
763 where m.val is not null order by $by $ord;"
764 err lshandoutbyauthor: sql=`echo "$sql"`
765 title="個人提出ファイル"
766 _m4 -D_TITLE_=$title $layout/html.m4.html
767 hra="<a href=\"?lsmyfile+"
768 hrb="<a href=\"?showattc+article_m+"
769 hrc="<a href=\"?replyblog+"
770 (echo '<table class="b">'
771 echo "$sql"|sq -html -header $db ) \
772 | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
773 -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
774 -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
775 | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
776 -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
777 echo '</table>'
778 }
779 searchart() {
780 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC
781 kwdgrp=""
782 authcond=""
783 if [ -z "$kwd" ]; then
784 echo "検索語を指定してください" | html p; return
785 fi
786 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
787 # Like '#1234', assume as artID
788 rowid=$((${kwd#\#} + 0)) # Force to be a number
789 kc="ar.rowid = $rowid"
790 else
791 for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes
792 ctime=""
793 if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
794 # '@<2016-10-10' -> ctime < '2016-10-10'
795 # '@>=2016-10-10' -> ctime >= '2016-10-10'
796 # '@2016-10-10' -> ctime GLOB '@2016-10-10'
797 k=${k#@}
798 case "$k" in
799 [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
800 *) op='GLOB'; ctime="${k##*[><= ]}*" ;;
801 esac
802 kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
803 # Not sure GROUP BY a.blogid is comfortable for searchers...?
804 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results
805 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
806 ctime=`date +%F`
807 elif n=`expr x"$k" : 'x@\([0-9]*\)days*'` >/dev/null >&2; then
808 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
809 elif [ x"$k" = x"@week" ]; then
810 ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
811 elif n=`expr x"$k" : 'x@\([0-9]*\)weeks*'` >/dev/null >&2; then
812 n=$((n * 7))
813 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
814 elif [ x"$k" = x"@month" ]; then
815 ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
816 elif n=`expr x"$k" : 'x@\([0-9]*\)months*'` >/dev/null >&2; then
817 ctime=`query "SELECT datetime('now', 'localtime', '-$n month');"`
818 elif [ x"$k" = x"@year" ]; then
819 ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
820 elif n=`expr x"$k" : 'x@\([0-9]*\)years*'` >/dev/null >&2; then
821 ctime=`query "SELECT datetime('now', 'localtime', '-$n year');"`
822 fi
823 if [ -n "$ctime" ]; then
824 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
825 else
826 kc=$kc${kc:+" AND "}"content LIKE '%$k%'"
827 fi
828 done
829 fi
830 kwd=`echo "$kwd"|htmlescape`
831 owner=`getpar owner`
832 owner=${owner:-$1}
833 echo "「$kwd」による検索結果" | html p
834 if [ -n "$owner" ]; then
835 cond="where key='owner' and val='$owner'"
836 if isuser $owner; then
837 echo "(`linkhome $owner` さんの記録からの検索)" | html p
838 else
839 linkhome $owner 1>&3
840 echo "(`linkhome $owner` グループからの検索)" | html p
841 fi
842 elif { author=`getpar author`; test -n "$author"; }; then
843 atptn=`sqlquotestr $author`
844 #kc="$kc${kc:+ AND }author=$atptn"
845 authcond="WHERE author=$atptn"
846 if isuser $author; then
847 echo "(`linkhome $author` さんの書き込みからの検索)" | html p
848 fi
849 fi
850 # article_s: id=article-id, key='text', val='TEXT'
851 # article: id=article-id, blogid=blogkd
852 # blog: id=blog-id, author=LeaderAuthor
853 # blog_s: id=blog-id, key='title', val='BLOG-TITLE'
854 # WANT: blog-ROWid,article-id,val(TEXT)
855 sql2="`sql4readableblogs` -- Extract user-readable blogs
856 -- 0.3sec
857 WITH artsm AS (
858 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
859 FROM article a
860 LEFT JOIN
861 (SELECT ars.id, ctime, text, coalesce(files, '') files
862 FROM (SELECT id,
863 max(CASE key WHEN 'ctime' THEN val END) ctime,
864 max(CASE key WHEN 'text' THEN val END) text
865 FROM article_s
866 GROUP BY id) ars
867 LEFT JOIN
868 (SELECT id, group_concat(val) files
869 FROM article_m
870 WHERE type LIKE 'file:%'
871 GROUP BY id) arm
872 ON ars.id=arm.id
873 ) ar
874 ON a.id=ar.id
875 ), ar AS (
876 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
877 FROM article a JOIN artsm ON a.id=artsm.id
878 $authcond
879 ), bl AS (
880 SELECT blg.rid, blg.*, blog_s.val TITLE
881 FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
882 )
883 SELECT bl.rid||'#'||ar.id '',
884 bl.title TITLE,
885 (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
886 substr(ctime, 0, 11) DATE,
887 substr(content, 0, 78) TEXT
888 FROM ar JOIN bl
889 ON ar.blogid=bl.id
890 WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond)
891 ORDER by DATE DESC, TITLE, ctime;"
892 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
893 href=\"?replyblog+\1\">VIEW</a></TD>,"
894 # echo "$sql2" > tmp/sql.out
895 result=$tmpd/result.$$
896 cat<<EOF
897 <table class="b searchart">
898 `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
899 </table>
900 EOF
901 if [ -s "$result" ]; then
902 found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT
903 one=${found%1}
904 echo "$found match${one:+es} found"
905 else
906 echo orz...
907 fi
908 }
909 listblog() (
910 # $1={user,group}
911 qow=`sqlquote $1`
912 cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
913 DT_CHLD=article:blogid
914 cgi_form searchart<<EOF
915 <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
916 `cgi_hidden owner $user`
917 EOF
918 dumptable html blog 'ctime title heading' "$cond"
919 )
921 blog_addentry() {
922 # $1=GRPname(if it is a group)
923 grprowid=$1
924 rowid=`getpar rowid`
925 ## err blog_addentry0: rowid=$rowid
926 if [ -n "$grprowid" ]; then
927 owner=`getgroupbyid $grprowid`
928 else
929 owner=`getpar owner`
930 fi
931 err blog-add: \$1=$1 rowid=$rowid owner=$owner
932 if isgroup "$owner"; then
933 groupmode=1 listing=$owner guide="[${owner}]" GF_OWNER=$owner
934 else
935 usermode=1 listing=$user guide="[個人]"
936 fi
938 if [ -n "`getpar title`" ]; then
939 if [ "$usermode" ]; then
940 err usermode: user=$user owner=$owner
941 if [ x"$user" != x"$owner" ]; then
942 echo "他人の日記は書けません" | html p
943 return 2
944 fi
945 elif [ "$groupmode" ]; then # if write to group log
946 grp=$owner #\`getpar grp\`
947 err ismember: $user $grp
948 if ! ismember "$user" "$grp"; then
949 echo "(話題作成はこのグループに加入してから)" | html p
950 return 3
951 fi
952 fi
953 par2table $formdir/blog.def
954 serial=`getpar serial`
955 ## err SERIAL: $serial ROWID=$rowid listing=$listing
956 id=""
957 if [ -n "$rowid" ]; then
958 # Here, id becomes NULL when removal of entries at par2table
959 id=`query "select rowid from blog where rowid=$rowid;"`
960 elif [ -n "$serial" ]; then
961 # If new blog leader created, traverse to its head.
962 id=`query "select rowid from blog where id='$serial';"`
963 ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
964 fi
965 if [ -n "$id" ]; then
966 ## If new aritcle is entered, JUMP to blog_reply
967 blog_reply $id
968 return
969 fi
970 fi
971 echo "${guide}新規話題作成" > $tmpd/title.$$
972 listblog $listing > $tmpd/listblog.$$
973 genform $formdir/blog.def \
974 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
975 -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
976 -D_DUMPHEAD_="これまでの蓄積" \
977 -D_FORM_="syscmd(\`cat')" \
978 -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
979 $layout/html.m4.html \
980 $layout/form+dump-whead.m4.html
981 }
983 blog_reply() { # Posting to blog article
984 rowid=$1
986 if [ -z "$rowid" ]; then
987 echo "表示する日記番号が未指定です。" | html p
988 return
989 fi
990 title=`getvalbyid blog title $rowid`
991 owner=`getvalbyid blog owner $rowid`
992 qowner=`sqlquotestr "$owner"`
993 if [ -z "$title" ]; then
994 echo "日記番号指定が無効です。" | html p
995 return
996 fi
997 blog_writable $rowid $user; rc=$?
998 if [ $rc = 0 ]; then
999 iswritable=true
1000 else
1001 iswritable=false
1002 if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
1003 isfrozen=true
1004 frozen_class='frozen"'
1005 frozen_flag=$FROZEN_TAG
1006 fi
1007 fi
1008 if isuser "$owner"; then
1009 subtitle="`gecos $owner` さんの話題"
1010 else
1011 grprowid=`query "select rowid from grp where gname=$qowner;"`
1012 subtitle="グループ
1013 <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$owner</a> での話題
1014 `query \"SELECT printf('(チーム:%s)', val)\
1015 FROM blog_s
1016 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
1017 AND key='team';
1018 \"|htmlescape`"
1019 memclass=`grp_getbodyclass "$owner"`
1020 fi
1022 text=`getpar text`
1023 if [ -n "$text" ]; then
1024 if $iswritable; then
1025 par2table $formdir/article.def
1026 st=$?
1027 case $st in
1028 0|4)
1029 [ "$st" = "4" ] && act="書込削除"
1030 blog_notify_reply $rowid $user "$text" $act
1031 if [ -n "$grprowid" ]; then
1032 qgrp=$(sqlquote "$owner")
1033 dbsetbyid grp $owner wtime "`date '+%F %T'`"
1034 else
1035 dbsetbyid user "$user" wtime "`date '+%F %T'`"
1036 fi
1037 ;;
1038 esac
1039 else
1040 if $isfrozen; then
1041 title="$title(凍結板につき書き込み不可)"
1042 else
1043 title="$title(加入してないので書き込み不可)"
1044 fi
1045 fi
1046 fi
1047 def=$formdir/article.def
1048 echo "$title" > $tmpd/title.$$
1049 echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
1050 ${BLOG_SHOW:-blog_showentry} blog $rowid \
1051 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1052 -D_BODYCLASS_=general"${memclass:+ $memclass}" \
1053 -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
1054 -D_FORM_='' \
1055 -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
1056 $layout/html.m4.html $layout/form+dump-whead.m4.html
1057 }
1059 blog_reply_article() { # Direct link to article in some blog
1060 arid=${1:-0} # Already sanitized to digits
1061 brid=`query "SELECT rowid FROM blog WHERE \
1062 id=(SELECT blogid FROM article WHERE rowid=$arid);"`
1063 if [ -n "$brid" ]; then
1064 newurl="?replyblog+$brid#$arid"
1065 echo "Refresh: 0; $newurl"; echo
1066 exit 0
1067 else
1068 contenttype; echo
1069 echo "無効な記事番号です." | html p
1070 fi
1071 }