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