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