s4
view s4-blog.sh @ 495:59cd8278a5b5
Trailing 2 consecutive SPC will be translated to <br>
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Tue, 26 Jun 2018 07:55:41 +0900 |
| parents | d879d9d1acb7 |
| children | 5119c19d15b5 |
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へのリンク、 [[URL|文字列]]でアンカー文字列指定
392 {{画像URL}} でインライン画像、 {{画像URL|幅}} でピクセル幅指定
393 {{{URL}}} でURL先を開く iframe、 {{{URL|高さ}}} ピクセル高さ指定
394 行頭: ## 大見出し, ### 中見出し, #### 小見出し
395 行末の2連続スペースで強制改行(<br>)
396 |*見出し列|列2|列3… と行頭から始まる縦棒区切り行を続けて表
397 ' *語群* ' で強調(両側の空白必要、** でもっと強調。*の代わりに _ でも可)"
398 touchhelp="${touchpanel:+<p class=\"help\">$help</p>}"
399 textform='<div class="fold">
400 <input type="checkbox" id="cmt" checked><label
401 accesskey="c" title="C" for="cmt">コメントする</label><div>
402 <table class="b">
403 <tr><td><textarea name="text" cols="72" rows="4" title="'"$help"'">
404 </textarea>'"$touchhelp"'</td></tr>
405 <tr><td>添付ファイル:
406 <input type="file" name="image"'" $file_accept multiple></td></tr>"'
407 </table>
408 <input type="submit" value="送信">
409 <input type="reset" value="リセット"></div></div>
410 '
411 cat<<-EOF
412 </table> <!-- end of s4-blog:blog_showentry() main table -->
413 <p class="update_link"><a
414 href="?reload/$rowid" accesskey="r" title="R">再読込</a> / <a
415 href="#title" id="bottom" accesskey="t" title="T">先頭へ</a></p>
416 EOF
417 $iswritable && cat<<-EOF
418 <div class="blogcomment">
419 <input type="hidden" name="blogid" value="$id">
420 <input type="hidden" name="id" value="`genserial`">
421 <input type="hidden" name="stage" value="replyblog">
422 $textform
423 </div>
424 </form> <!-- End of s4-blog:blog_showentry() main form -->
425 EOF
426 # Clean up orphaned icon cache
427 [ -s $iconcleaner ] && query ".read '$iconcleaner'"
428 # Record access log
429 acclog blog $rowid
430 }
432 lshandout() {
433 # $1=rowid of blog
434 blog_writable $1 $user
435 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
436 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
437 echo "メンバー以外は利用できません。" | html p; return
438 fi
439 time=`getvalbyid blog ctime $1|colrm 11`
440 owner=`getvalbyid blog owner $1`
441 title=`getvalbyid blog title $1`
442 ge=`gecos $owner`
443 fh=$tmpd/formhead
444 echo "$time [$title]@${ge:-$owner}" > $fh
445 lshandoutsub $owner "$@" \
446 |_m4 -D_TITLE_="提出状況" \
447 -D_FORMHEAD_="syscmd(cat $fh)" \
448 -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \
449 $layout/html.m4.html $layout/form+dump-whead.m4.html
450 gn=`echo $owner|htmlescape`
451 echo "<p><a href=\"?lshandoutall+$1\">グループ $gn すべてのレポート板集計</a></p>"
452 }
453 gethandoutcsv() {
454 # contenttype; echo
455 CATCSV=1 lshandoutall "$1"
456 }
457 gethandoutcsv2() {
458 # contenttype; echo
459 SQL=$(cat<<-EOF) gethandoutcsv "$1"
460 WITH this_blog_articles AS (
461 SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime
462 FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid
463 ), text_or_file AS (
464 SELECT bid, author, title, ctime, 'text' shu, count(val) cnt
465 FROM this_blog_articles tba, article_s s
466 ON tba.aid=s.id
467 WHERE key='text'
468 GROUP by bid, author
469 UNION
470 SELECT bid, author, title, ctime, 'file' shu, count(val) cnt
471 FROM this_blog_articles tba, article_m m
472 ON tba.aid=m.id
473 WHERE key='image'
474 GROUP by bid, author
475 ), count_list AS (
476 SELECT author,
477 substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit,
478 cnt
479 FROM text_or_file
480 )
481 SELECT gecos "名前",
482 substr(author, 1, instr(author, '@')-1) "uname",
483 unit,
484 cnt "post"
485 FROM count_list cl JOIN gecoses g ON cl.author=g.name;
486 EOF
487 }
488 lshandout_ulink_table() {
489 # NO Args. Read stdin as SQL
490 echo '<table class="b td3rr td3evw">'
491 hrb="<a href=\"?home+"
492 # echo "$sql" | sq -header -html $db \ # Formerly, this is called via sq()
494 printf ".mode html\n.header ON\n" | query
495 cat | query \
496 | sed -e "s,\(<TR><TD>\)\([^ ]*\) \(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
497 echo '</table>'
498 printf ".mode list\n.header OFF\n" | query
499 }
500 lshandoutall() {
501 # $1=rowid of blog
502 blog_writable $1 $user
503 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
504 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
505 echo "メンバー以外は利用できません。" | html p; return
506 fi
507 rowid=$(($1 + 0))
508 owner=`getvalbyid blog owner $1`
509 qowner=`sqlquotestr "$owner"`
511 query<<-EOF
512 CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS
513 WITH blog_owner_mode AS (
514 SELECT id,
515 blog.rowid brid,
516 max(CASE key WHEN 'owner' THEN val END) owner,
517 max(CASE key WHEN 'mode' THEN val END) mode,
518 max(CASE key WHEN 'title' THEN val END) title,
519 max(CASE key WHEN 'ctime' THEN val END) ctime
520 FROM blog NATURAL JOIN blog_s
521 GROUP BY id
522 )
523 SELECT id, brid, title, ctime FROM blog_owner_mode
524 /* WHERE owner=$qowner AND mode LIKE '%report%'; */
525 WHERE owner=$qowner
526 AND
527 (mode LIKE '%report%' OR mode LIKE '%quiz%');
528 /* ↑これでレポート形式の blogid 一覧を得る */
529 EOF
530 if [ -z "$CATCSV" ]; then
531 _m4 -D_TITLE_="提出状況" $layout/html.m4.html
532 ge=`gecos "$owner"`
533 tbls=""
534 grptxt=`echo "${ge:-$owner}"|htmlescape`
535 echo "<h1>$grptxt 書き込み状況一覧</h1>"
536 fi
537 if [ -z "$SQL" ]; then
538 bridlist=`query "SELECT brid FROM report_type_blogs;"`
539 for brid in $bridlist; do # Skip this loop if $SQL set
540 brid=$(($brid + 0)) # Ensure to be a number
541 [ $brid = 0 ] && continue
542 time=`getvalbyid blog ctime $brid|colrm 11`
543 title=`getvalbyid blog title $brid|htmlescape`
544 state=`getvalbyid blog state $brid|htmlescape`
545 tt="handout_$brid"
546 [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen=""
547 if [ -z "$CATCSV" ]; then
548 echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a>$frozen</h2>"
549 lshandoutsub "$owner" $brid "$tt"
550 else
551 lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
552 fi
553 tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
554 done
555 fi
556 sql=${SQL:-"SELECT * FROM $tbls;"}
557 if [ -z "$CATCSV" ]; then
558 echo "<hr><h2>総合</h2>"
559 echo "$sql" | lshandout_ulink_table
560 echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">CSV</a>)</h2>"
561 printf ".mode csv\n.header ON\n" | query
562 echo '<pre class="list">'
563 echo "$sql" | query | sed 's/^"[0-9]* /"/'
564 echo "</pre>"
565 echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>"
566 else
567 contenttype "Application/CSV"
568 printf ".mode csv\n.header ON\n" | query >/dev/null
569 fn=report-count.csv
570 printf 'Content-Disposition: filename="%s"\n' "$fn"
571 outfile=$tmpd/out-$$.csv
572 echo "$sql" | query | sed 's/^"[0-9]* /"/' > $outfile
573 echo "Content-Length: " `cat $outfile | wc -c`; echo
575 cat $outfile
576 exit 0
577 fi
578 printf ".mode list\n.header OFF\n.separator |\n" | query
579 }
580 lshandoutsub() {
581 # $1=owner $2=rowid of blog &optional $3=temp_table name
582 qgname=`sqlquote "$1"`
583 if isgroup "$1"; then
584 sample="(select user from grp_mem where gname=$qgname)"
585 else
586 sample="(select distinct author as user from arts)"
587 echo "(集計は板への投稿者のみ)" | html p
588 fi
589 tmpname="${3:-handout_$2}"
590 sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS
591 with arts as (select id,author from article \
592 where blogid=(select id from blog where rowid=$2))\
593 select (select rowid from user where name=c0.user)||' '|| \
594 (select gecos from gecoses where name=c0.user) as 'メンバー',\
595 substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\
596 sum(case when c1.key is not null then 1 else 0 end)\
597 as '[$title] コメント記入',\
598 sum(case when c2.key is not null then 1 else 0 end)\
599 as '[$title] ファイルの提出'\
600 from $sample c0 \
601 left join (select id,author from arts) a\
602 on c0.user=a.author\
603 left join (select id,key from article_s where key='text') c1\
604 on a.id=c1.id left join (select id,key from article_m ) c2\
605 on c1.id=c2.id group by c0.user order by c0.user;\
606 \
607 SELECT * FROM $tmpname;"
608 err ishandoutsub: sql="$sql"
609 echo "$sql" | lshandout_ulink_table
610 }
611 gethandout() {
612 # $1=rowid of blog
613 blog_writable $1 $user
614 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
615 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
616 echo "メンバー以外は利用できません。" | html p; return
617 fi
618 i=0
619 bd=$tmpd/archive.$$
620 mkdir $bd
621 query "select m.rowid,author,m.val from article a join article_m m\
622 on a.id=m.id where blogid=(select id from blog where rowid=$1)\
623 and m.key in ('image', 'document', 'binary');" \
624 | while IFS='|' read rowid author filename; do
625 err isfilereadable $user article_m $rowid
626 isfilereadable $user article_m $rowid || continue
627 err ok
628 i=$((i+1))
629 dir=`printf $bd/%03d $i`
630 mkdir $dir
631 query "select quote(bin) from article_m where rowid=$rowid;" \
632 | unhexize > $dir/$filename
633 done
634 if [ ! -d $bd/001 ]; then
635 contenttype; echo
636 echo "取得できるファイルがありませんでした。" | html p
637 return
638 fi
639 (cd $bd
640 ## err cdto$bd; (pwd; ls -lFa) 1>&3
641 tar zcf .archive.tar.gz * && mv .archive.tar.gz archive.tar.gz
642 err Creating tar archive "`ls -l archive.tar.gz`"
643 )
644 arc=$bd/archive.tar.gz
645 echo "Content-type: application/x-gzip"
646 echo "Content-Length: `cat $arc|wc -c`"
647 echo "Content-Disposition: filename=\"archive.tar.gz\""
648 echo
649 cat $arc
650 }
651 blogseen() { # $1 = blogid
652 blogid=${1%%[!0-9]*}
653 if [ -z "$blogid" ]; then
654 echo "Invalid blog id" | html p; exit
655 fi
656 blog_writable "$blogid" "$user"
657 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
658 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
659 echo "メンバー以外は利用できません。" | html p; return
660 fi
661 owner=`getvalbyid blog owner $rowid`
662 qowner=`sqlquotestr "$owner"`
663 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"`
664 ge=`gecos "$owner" | htmlescape`
665 title=`getvalbyid blog title $rowid | htmlescape`
666 h1="アクセス時刻"
667 link2board="<a href=\"?replyblog+$rowid\">$title</a>"
668 link2group="<a href=\"?grp+$grprowid\">$ge</a>"
669 _m4 -D_TITLE_="$h1" $layout/html.m4.html
670 echo "$h1" | html h1
671 echo "[$link2board]@$link2group" | html h2
672 warn=' class="warn"'
673 cat <<-EOF
674 <table class="b">
675 <tr><th>メンバー</th><th>uname</th><th>最終閲覧時刻</th></tr>
676 EOF
677 query <<-EOF |
678 WITH grpmem as (
679 SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos
680 FROM grp_mem
681 WHERE gname=(SELECT val FROM blog_s
682 WHERE id=(select id from blog where rowid=$blogid)
683 AND key='owner')
684 ), acctime AS (
685 SELECT user, max(time) atime
686 FROM tblaccesses
687 WHERE tbl='blog' AND tblrowid=$blogid
688 GROUP BY user
689 )
690 SELECT g.user,
691 (SELECT rowid FROM user u WHERE u.name=g.user),
692 hex(gecos),
693 atime
694 FROM grpmem g LEFT JOIN acctime t
695 ON g.user = t.user
696 GROUP BY g.user
697 ORDER BY atime DESC;
698 EOF
699 while IFS='|' read u uid hexge time; do
700 td=${time:+"<td>"} # If the variable time is set, td=<td>
701 td=${td:-"<td$warn>"} # else td=<td class="warn">
702 cat <<-EOF
703 <tr>
704 <td><a href="?home+$uid">`echo "$hexge"|unhexize|htmlescape`</a></td>
705 <td>`echo ${u%%@*}|htmlescape`</td>
706 $td${time:----}</td></tr>
707 EOF
708 done
709 cat <<-EOF
710 </table>
711 <p><a href="?replyblog+$rowid">[$title]に戻る</a></p>
712 </html>
713 EOF
714 }
715 lsmyfile() { # $1(optional)=SortBy
716 case "$1" in
717 ""|CTIME-DESC)
718 by="CTIME" ord="DESC" ;;
719 CTIME*) by="CTIME" ;;
720 FILE*) by="FILE" ;;
721 OWNER*) by="OWNER" ;;
722 TITLE*) by="TITLE" ;;
723 esac
724 case "$1" in
725 *DESC) ord="DESC" ;;
726 esac
727 case "$ord" in
728 DESC) lkod="" jord="降順" ;;
729 *) lkod="-DESC" jord="昇順" ;;
730 esac
731 sql="select m.val||'/'||m.rowid FILE,
732 coalesce(
733 case when (select name from user where name=bs.owner)
734 is not null
735 then (select val from user_s where name=bs.owner
736 and key='gecos')
737 when (select gname from grp where gname=bs.owner)
738 is not null
739 then (select val from grp_s where gname=bs.owner
740 and key='gecos')
741 else
742 null
743 end,
744 bs.owner
745 ) OWNER,
746 a_s.val CTIME,
747 ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
748 from (select rowid,id,val from article_m where id
749 in (select id from article where author='$user')
750 and type like 'file:%')
751 m left join article a on m.id=a.id
752 left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
753 left join (select id,
754 max(case key when 'owner' then val end) as owner,
755 max(case key when 'title' then val end) as title
756 from blog_s group by id)
757 bs on a.blogid=bs.id
758 left join blog b on bs.id=b.id
759 where m.val is not null order by $by $ord;"
760 err lshandoutbyauthor: sql=`echo "$sql"`
761 title="個人提出ファイル"
762 _m4 -D_TITLE_=$title $layout/html.m4.html
763 hra="<a href=\"?lsmyfile+"
764 hrb="<a href=\"?showattc+article_m+"
765 hrc="<a href=\"?replyblog+"
766 (echo '<table class="b">'
767 echo "$sql"|sq -html -header $db ) \
768 | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
769 -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
770 -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
771 | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
772 -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
773 echo '</table>'
774 }
775 searchart() {
776 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC
777 kwdgrp=""
778 authcond=""
779 if [ -z "$kwd" ]; then
780 echo "検索語を指定してください" | html p; return
781 fi
782 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
783 # Like '#1234', assume as artID
784 rowid=$((${kwd#\#} + 0)) # Force to be a number
785 kc="ar.rowid = $rowid"
786 else
787 for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes
788 ctime=""
789 if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
790 # '@<2016-10-10' -> ctime < '2016-10-10'
791 # '@>=2016-10-10' -> ctime >= '2016-10-10'
792 # '@2016-10-10' -> ctime GLOB '@2016-10-10'
793 k=${k#@}
794 case "$k" in
795 [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
796 *) op='GLOB'; ctime="${k##*[><= ]}*" ;;
797 esac
798 kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
799 # Not sure GROUP BY a.blogid is comfortable for searchers...?
800 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results
801 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
802 ctime=`date +%F`
803 elif n=`expr x"$k" : 'x@\([0-9]*\)days*'` >/dev/null >&2; then
804 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
805 elif [ x"$k" = x"@week" ]; then
806 ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
807 elif n=`expr x"$k" : 'x@\([0-9]*\)weeks*'` >/dev/null >&2; then
808 n=$((n * 7))
809 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
810 elif [ x"$k" = x"@month" ]; then
811 ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
812 elif n=`expr x"$k" : 'x@\([0-9]*\)months*'` >/dev/null >&2; then
813 ctime=`query "SELECT datetime('now', 'localtime', '-$n month');"`
814 elif [ x"$k" = x"@year" ]; then
815 ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
816 elif n=`expr x"$k" : 'x@\([0-9]*\)years*'` >/dev/null >&2; then
817 ctime=`query "SELECT datetime('now', 'localtime', '-$n year');"`
818 fi
819 if [ -n "$ctime" ]; then
820 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
821 else
822 kc=$kc${kc:+" AND "}"content LIKE '%$k%'"
823 fi
824 done
825 fi
826 kwd=`echo "$kwd"|htmlescape`
827 owner=`getpar owner`
828 owner=${owner:-$1}
829 echo "「$kwd」による検索結果" | html p
830 if [ -n "$owner" ]; then
831 cond="where key='owner' and val='$owner'"
832 if isuser $owner; then
833 echo "(`linkhome $owner` さんの記録からの検索)" | html p
834 else
835 linkhome $owner 1>&3
836 echo "(`linkhome $owner` グループからの検索)" | html p
837 fi
838 elif { author=`getpar author`; test -n "$author"; }; then
839 atptn=`sqlquotestr $author`
840 #kc="$kc${kc:+ AND }author=$atptn"
841 authcond="WHERE author=$atptn"
842 if isuser $author; then
843 echo "(`linkhome $author` さんの書き込みからの検索)" | html p
844 fi
845 fi
846 # article_s: id=article-id, key='text', val='TEXT'
847 # article: id=article-id, blogid=blogkd
848 # blog: id=blog-id, author=LeaderAuthor
849 # blog_s: id=blog-id, key='title', val='BLOG-TITLE'
850 # WANT: blog-ROWid,article-id,val(TEXT)
851 sql2="`sql4readableblogs` -- Extract user-readable blogs
852 -- 0.3sec
853 WITH artsm AS (
854 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
855 FROM article a
856 LEFT JOIN
857 (SELECT ars.id, ctime, text, coalesce(files, '') files
858 FROM (SELECT id,
859 max(CASE key WHEN 'ctime' THEN val END) ctime,
860 max(CASE key WHEN 'text' THEN val END) text
861 FROM article_s
862 GROUP BY id) ars
863 LEFT JOIN
864 (SELECT id, group_concat(val) files
865 FROM article_m
866 WHERE type LIKE 'file:%'
867 GROUP BY id) arm
868 ON ars.id=arm.id
869 ) ar
870 ON a.id=ar.id
871 ), ar AS (
872 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
873 FROM article a JOIN artsm ON a.id=artsm.id
874 $authcond
875 ), bl AS (
876 SELECT blg.rid, blg.*, blog_s.val TITLE
877 FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
878 )
879 SELECT bl.rid||'#'||ar.id '',
880 bl.title TITLE,
881 (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
882 substr(ctime, 0, 11) DATE,
883 substr(content, 0, 78) TEXT
884 FROM ar JOIN bl
885 ON ar.blogid=bl.id
886 WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond)
887 ORDER by DATE DESC, TITLE, ctime;"
888 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
889 href=\"?replyblog+\1\">VIEW</a></TD>,"
890 # echo "$sql2" > tmp/sql.out
891 result=$tmpd/result.$$
892 cat<<EOF
893 <table class="b searchart">
894 `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
895 </table>
896 EOF
897 if [ -s "$result" ]; then
898 found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT
899 one=${found%1}
900 echo "$found match${one:+es} found"
901 else
902 echo orz...
903 fi
904 }
905 listblog() (
906 # $1={user,group}
907 qow=`sqlquote $1`
908 cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
909 DT_CHLD=article:blogid
910 cgi_form searchart<<EOF
911 <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
912 `cgi_hidden owner $user`
913 EOF
914 dumptable html blog 'ctime title heading' "$cond"
915 )
917 blog_addentry() {
918 # $1=GRPname(if it is a group)
919 grprowid=$1
920 rowid=`getpar rowid`
921 ## err blog_addentry0: rowid=$rowid
922 if [ -n "$grprowid" ]; then
923 owner=`getgroupbyid $grprowid`
924 else
925 owner=`getpar owner`
926 fi
927 err blog-add: \$1=$1 rowid=$rowid owner=$owner
928 if isgroup "$owner"; then
929 groupmode=1 listing=$owner guide="[${owner}]" GF_OWNER=$owner
930 else
931 usermode=1 listing=$user guide="[個人]"
932 fi
934 if [ -n "`getpar title`" ]; then
935 if [ "$usermode" ]; then
936 err usermode: user=$user owner=$owner
937 if [ x"$user" != x"$owner" ]; then
938 echo "他人の日記は書けません" | html p
939 return 2
940 fi
941 elif [ "$groupmode" ]; then # if write to group log
942 grp=$owner #\`getpar grp\`
943 err ismember: $user $grp
944 if ! ismember "$user" "$grp"; then
945 echo "(話題作成はこのグループに加入してから)" | html p
946 return 3
947 fi
948 fi
949 par2table $formdir/blog.def
950 serial=`getpar serial`
951 ## err SERIAL: $serial ROWID=$rowid listing=$listing
952 id=""
953 if [ -n "$rowid" ]; then
954 # Here, id becomes NULL when removal of entries at par2table
955 id=`query "select rowid from blog where rowid=$rowid;"`
956 elif [ -n "$serial" ]; then
957 # If new blog leader created, traverse to its head.
958 id=`query "select rowid from blog where id='$serial';"`
959 ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
960 fi
961 if [ -n "$id" ]; then
962 ## If new aritcle is entered, JUMP to blog_reply
963 blog_reply $id
964 return
965 fi
966 fi
967 echo "${guide}新規話題作成" > $tmpd/title.$$
968 listblog $listing > $tmpd/listblog.$$
969 genform $formdir/blog.def \
970 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
971 -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
972 -D_DUMPHEAD_="これまでの蓄積" \
973 -D_FORM_="syscmd(\`cat')" \
974 -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
975 $layout/html.m4.html \
976 $layout/form+dump-whead.m4.html
977 }
979 blog_reply() { # Posting to blog article
980 rowid=$1
982 if [ -z "$rowid" ]; then
983 echo "表示する日記番号が未指定です。" | html p
984 return
985 fi
986 title=`getvalbyid blog title $rowid`
987 owner=`getvalbyid blog owner $rowid`
988 qowner=`sqlquotestr "$owner"`
989 if [ -z "$title" ]; then
990 echo "日記番号指定が無効です。" | html p
991 return
992 fi
993 blog_writable $rowid $user; rc=$?
994 if [ $rc = 0 ]; then
995 iswritable=true
996 else
997 iswritable=false
998 if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
999 isfrozen=true
1000 frozen_class='frozen"'
1001 frozen_flag=$FROZEN_TAG
1002 fi
1003 fi
1004 if isuser "$owner"; then
1005 subtitle="`gecos $owner` さんの話題"
1006 else
1007 grprowid=`query "select rowid from grp where gname=$qowner;"`
1008 subtitle="グループ
1009 <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$owner</a> での話題
1010 `query \"SELECT printf('(チーム:%s)', val)\
1011 FROM blog_s
1012 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
1013 AND key='team';
1014 \"|htmlescape`"
1015 memclass=`grp_getbodyclass "$owner"`
1016 fi
1018 text=`getpar text`
1019 if [ -n "$text" ]; then
1020 if $iswritable; then
1021 par2table $formdir/article.def
1022 st=$?
1023 case $st in
1024 0|4)
1025 [ "$st" = "4" ] && act="書込削除"
1026 blog_notify_reply $rowid $user "$text" $act
1027 if [ -n "$grprowid" ]; then
1028 qgrp=$(sqlquote "$owner")
1029 dbsetbyid grp $owner wtime "`date '+%F %T'`"
1030 else
1031 dbsetbyid user "$user" wtime "`date '+%F %T'`"
1032 fi
1033 ;;
1034 esac
1035 else
1036 if $isfrozen; then
1037 title="$title(凍結板につき書き込み不可)"
1038 else
1039 title="$title(加入してないので書き込み不可)"
1040 fi
1041 fi
1042 fi
1043 def=$formdir/article.def
1044 echo "$title" > $tmpd/title.$$
1045 echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
1046 ${BLOG_SHOW:-blog_showentry} blog $rowid \
1047 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1048 -D_BODYCLASS_=general"${memclass:+ $memclass}" \
1049 -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
1050 -D_FORM_='' \
1051 -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
1052 $layout/html.m4.html $layout/form+dump-whead.m4.html
1053 }
1055 blog_reply_article() { # Direct link to article in some blog
1056 arid=${1:-0} # Already sanitized to digits
1057 brid=`query "SELECT rowid FROM blog WHERE \
1058 id=(SELECT blogid FROM article WHERE rowid=$arid);"`
1059 if [ -n "$brid" ]; then
1060 newurl="?replyblog+$brid#$arid"
1061 echo "Refresh: 0; $newurl"; echo
1062 exit 0
1063 else
1064 contenttype; echo
1065 echo "無効な記事番号です." | html p
1066 fi
1067 }