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