s4
view s4-blog.sh @ 471:1dacdf998892
Speed up new article listing
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Mon, 28 Aug 2017 22:51:45 +0859 |
| parents | 598f00f1b995 |
| children | 677597199031 |
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 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"`
655 ge=`gecos "$owner" | htmlescape`
656 title=`getvalbyid blog title $rowid | htmlescape`
657 h1="アクセス時刻"
658 link2board="<a href=\"?replyblog+$rowid\">$title</a>"
659 link2group="<a href=\"?grp+$grprowid\">$ge</a>"
660 _m4 -D_TITLE_="$h1" $layout/html.m4.html
661 echo "$h1" | html h1
662 echo "[$link2board]@$link2group" | html h2
663 warn=' class="warn"'
664 cat <<-EOF
665 <table class="b">
666 <tr><th>メンバー</th><th>uname</th><th>最終閲覧時刻</th></tr>
667 EOF
668 query <<-EOF |
669 WITH grpmem as (
670 SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos
671 FROM grp_mem
672 WHERE gname=(SELECT val FROM blog_s
673 WHERE id=(select id from blog where rowid=$blogid)
674 AND key='owner')
675 ), acctime AS (
676 SELECT user, max(time) atime
677 FROM tblaccesses
678 WHERE tbl='blog' AND tblrowid=$blogid
679 GROUP BY user
680 )
681 SELECT g.user,
682 (SELECT rowid FROM user u WHERE u.name=g.user),
683 hex(gecos),
684 atime
685 FROM grpmem g LEFT JOIN acctime t
686 ON g.user = t.user
687 GROUP BY g.user
688 ORDER BY atime DESC;
689 EOF
690 while IFS='|' read u uid hexge time; do
691 td=${time:+"<td>"} # If the variable time is set, td=<td>
692 td=${td:-"<td$warn>"} # else td=<td class="warn">
693 cat <<-EOF
694 <tr>
695 <td><a href="?home+$uid">`echo "$hexge"|unhexize|htmlescape`</a></td>
696 <td>`echo ${u%%@*}|htmlescape`</td>
697 $td${time:----}</td></tr>
698 EOF
699 done
700 cat <<-EOF
701 </table>
702 <p><a href="?replyblog+$rowid">[$title]に戻る</a></p>
703 </html>
704 EOF
705 }
706 lsmyfile() { # $1(optional)=SortBy
707 case "$1" in
708 ""|CTIME-DESC)
709 by="CTIME" ord="DESC" ;;
710 CTIME*) by="CTIME" ;;
711 FILE*) by="FILE" ;;
712 OWNER*) by="OWNER" ;;
713 TITLE*) by="TITLE" ;;
714 esac
715 case "$1" in
716 *DESC) ord="DESC" ;;
717 esac
718 case "$ord" in
719 DESC) lkod="" jord="降順" ;;
720 *) lkod="-DESC" jord="昇順" ;;
721 esac
722 sql="select m.val||'/'||m.rowid FILE,
723 coalesce(
724 case when (select name from user where name=bs.owner)
725 is not null
726 then (select val from user_s where name=bs.owner
727 and key='gecos')
728 when (select gname from grp where gname=bs.owner)
729 is not null
730 then (select val from grp_s where gname=bs.owner
731 and key='gecos')
732 else
733 null
734 end,
735 bs.owner
736 ) OWNER,
737 a_s.val CTIME,
738 ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
739 from (select rowid,id,val from article_m where id
740 in (select id from article where author='$user')
741 and type like 'file:%')
742 m left join article a on m.id=a.id
743 left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
744 left join (select id,
745 max(case key when 'owner' then val end) as owner,
746 max(case key when 'title' then val end) as title
747 from blog_s group by id)
748 bs on a.blogid=bs.id
749 left join blog b on bs.id=b.id
750 where m.val is not null order by $by $ord;"
751 err lshandoutbyauthor: sql=`echo "$sql"`
752 title="個人提出ファイル"
753 _m4 -D_TITLE_=$title $layout/html.m4.html
754 hra="<a href=\"?lsmyfile+"
755 hrb="<a href=\"?showattc+article_m+"
756 hrc="<a href=\"?replyblog+"
757 (echo '<table class="b">'
758 echo "$sql"|sq -html -header $db ) \
759 | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
760 -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
761 -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
762 | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
763 -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
764 echo '</table>'
765 }
766 searchart() {
767 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC
768 kwdgrp=""
769 authcond=""
770 if [ -z "$kwd" ]; then
771 echo "検索語を指定してください" | html p; return
772 fi
773 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
774 # Like '#1234', assume as artID
775 rowid=$((${kwd#\#} + 0)) # Force to be a number
776 kc="ar.rowid = $rowid"
777 else
778 for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes
779 if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
780 # '@<2016-10-10' -> ctime < '2016-10-10'
781 # '@>=2016-10-10' -> ctime >= '2016-10-10'
782 # '@2016-10-10' -> ctime GLOB '@2016-10-10'
783 k=${k#@}
784 case "$k" in
785 [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
786 *) op='GLOB'; ctime="${k##*[><= ]}*" ;;
787 esac
788 kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
789 # Not sure GROUP BY a.blogid is comfortable for searchers...?
790 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results
791 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
792 ctime=`date +%F`
793 kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'"
794 elif [ x"$k" = x"@week" ]; then
795 ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
796 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
797 elif [ x"$k" = x"@month" ]; then
798 ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
799 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
800 elif [ x"$k" = x"@year" ]; then
801 ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
802 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
803 else
804 kc=$kc${kc:+" AND "}"content LIKE '%$k%'"
805 fi
806 done
807 fi
808 kwd=`echo "$kwd"|htmlescape`
809 owner=`getpar owner`
810 owner=${owner:-$1}
811 echo "「$kwd」による検索結果" | html p
812 if [ -n "$owner" ]; then
813 cond="where key='owner' and val='$owner'"
814 if isuser $owner; then
815 echo "(`linkhome $owner` さんの記録からの検索)" | html p
816 else
817 linkhome $owner 1>&3
818 echo "(`linkhome $owner` グループからの検索)" | html p
819 fi
820 elif { author=`getpar author`; test -n "$author"; }; then
821 atptn=`sqlquotestr $author`
822 #kc="$kc${kc:+ AND }author=$atptn"
823 authcond="WHERE author=$atptn"
824 if isuser $author; then
825 echo "(`linkhome $author` さんの書き込みからの検索)" | html p
826 fi
827 fi
828 # article_s: id=article-id, key='text', val='TEXT'
829 # article: id=article-id, blogid=blogkd
830 # blog: id=blog-id, author=LeaderAuthor
831 # blog_s: id=blog-id, key='title', val='BLOG-TITLE'
832 # WANT: blog-ROWid,article-id,val(TEXT)
833 sql2="`sql4readableblogs` -- Extract user-readable blogs
834 -- 0.3sec
835 WITH artsm AS (
836 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
837 FROM article a
838 LEFT JOIN
839 (SELECT ars.id, ctime, text, coalesce(files, '') files
840 FROM (SELECT id,
841 max(CASE key WHEN 'ctime' THEN val END) ctime,
842 max(CASE key WHEN 'text' THEN val END) text
843 FROM article_s
844 GROUP BY id) ars
845 LEFT JOIN
846 (SELECT id, group_concat(val) files
847 FROM article_m
848 WHERE type LIKE 'file:%'
849 GROUP BY id) arm
850 ON ars.id=arm.id
851 ) ar
852 ON a.id=ar.id
853 ), ar AS (
854 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
855 FROM article a JOIN artsm ON a.id=artsm.id
856 $authcond
857 ), bl AS (
858 SELECT blg.rid, blg.*, blog_s.val TITLE
859 FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
860 )
861 SELECT bl.rid||'#'||ar.id '',
862 bl.title TITLE,
863 (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
864 substr(ctime, 0, 11) DATE,
865 substr(content, 0, 78) TEXT
866 FROM ar JOIN bl
867 ON ar.blogid=bl.id
868 WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond)
869 ORDER by DATE DESC, TITLE, ctime;"
870 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
871 href=\"?replyblog+\1\">VIEW</a></TD>,"
872 # echo "$sql2" > tmp/sql.out
873 result=$tmpd/result.$$
874 cat<<EOF
875 <table class="b searchart">
876 `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
877 </table>
878 EOF
879 if [ -s "$result" ]; then
880 found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT
881 one=${found%1}
882 echo "$found match${one:+es} found"
883 else
884 echo orz...
885 fi
886 }
887 listblog() (
888 # $1={user,group}
889 qow=`sqlquote $1`
890 cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
891 DT_CHLD=article:blogid
892 cgi_form searchart<<EOF
893 <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
894 `cgi_hidden owner $user`
895 EOF
896 dumptable html blog 'ctime title heading' "$cond"
897 )
899 blog_addentry() {
900 # $1=GRPname(if it is a group)
901 grprowid=$1
902 rowid=`getpar rowid`
903 ## err blog_addentry0: rowid=$rowid
904 if [ -n "$grprowid" ]; then
905 owner=`getgroupbyid $grprowid`
906 else
907 owner=`getpar owner`
908 fi
909 err blog-add: \$1=$1 rowid=$rowid owner=$owner
910 if isgroup "$owner"; then
911 groupmode=1 listing=$owner guide="[${owner}]" GF_OWNER=$owner
912 else
913 usermode=1 listing=$user guide="[個人]"
914 fi
916 if [ -n "`getpar title`" ]; then
917 if [ "$usermode" ]; then
918 err usermode: user=$user owner=$owner
919 if [ x"$user" != x"$owner" ]; then
920 echo "他人の日記は書けません" | html p
921 return 2
922 fi
923 elif [ "$groupmode" ]; then # if write to group log
924 grp=$owner #\`getpar grp\`
925 err ismember: $user $grp
926 if ! ismember "$user" "$grp"; then
927 echo "(話題作成はこのグループに加入してから)" | html p
928 return 3
929 fi
930 fi
931 par2table $formdir/blog.def
932 serial=`getpar serial`
933 ## err SERIAL: $serial ROWID=$rowid listing=$listing
934 id=""
935 if [ -n "$rowid" ]; then
936 # Here, id becomes NULL when removal of entries at par2table
937 id=`query "select rowid from blog where rowid=$rowid;"`
938 elif [ -n "$serial" ]; then
939 # If new blog leader created, traverse to its head.
940 id=`query "select rowid from blog where id='$serial';"`
941 ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
942 fi
943 if [ -n "$id" ]; then
944 ## If new aritcle is entered, JUMP to blog_reply
945 blog_reply $id
946 return
947 fi
948 fi
949 echo "${guide}新規話題作成" > $tmpd/title.$$
950 listblog $listing > $tmpd/listblog.$$
951 genform $formdir/blog.def \
952 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
953 -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
954 -D_DUMPHEAD_="これまでの蓄積" \
955 -D_FORM_="syscmd(\`cat')" \
956 -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
957 $layout/html.m4.html \
958 $layout/form+dump-whead.m4.html
959 }
961 blog_reply() { # Posting to blog article
962 rowid=$1
964 if [ -z "$rowid" ]; then
965 echo "表示する日記番号が未指定です。" | html p
966 return
967 fi
968 title=`getvalbyid blog title $rowid`
969 owner=`getvalbyid blog owner $rowid`
970 qowner=`sqlquotestr "$owner"`
971 if [ -z "$title" ]; then
972 echo "日記番号指定が無効です。" | html p
973 return
974 fi
975 blog_writable $rowid $user; rc=$?
976 if [ $rc = 0 ]; then
977 iswritable=true
978 else
979 iswritable=false
980 if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
981 isfrozen=true
982 frozen_class='frozen"'
983 frozen_flag=$FROZEN_TAG
984 fi
985 fi
986 if isuser "$owner"; then
987 subtitle="`gecos $owner` さんの話題"
988 else
989 grprowid=`query "select rowid from grp where gname=$qowner;"`
990 subtitle="グループ
991 <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$owner</a> での話題
992 `query \"SELECT printf('(チーム:%s)', val)\
993 FROM blog_s
994 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
995 AND key='team';
996 \"|htmlescape`"
997 memclass=`grp_getbodyclass "$owner"`
998 fi
1000 text=`getpar text`
1001 if [ -n "$text" ]; then
1002 if $iswritable; then
1003 par2table $formdir/article.def
1004 st=$?
1005 case $st in
1006 0|4)
1007 [ "$st" = "4" ] && act="書込削除"
1008 blog_notify_reply $rowid $user "$text" $act
1009 if [ -n "$grprowid" ]; then
1010 qgrp=$(sqlquote "$owner")
1011 dbsetbyid grp $owner wtime "`date '+%F %T'`"
1012 fi
1013 ;;
1014 esac
1015 else
1016 if $isfrozen; then
1017 title="$title(凍結板につき書き込み不可)"
1018 else
1019 title="$title(加入してないので書き込み不可)"
1020 fi
1021 fi
1022 fi
1023 def=$formdir/article.def
1024 echo "$title" > $tmpd/title.$$
1025 echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
1026 ${BLOG_SHOW:-blog_showentry} blog $rowid \
1027 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1028 -D_BODYCLASS_=general"${memclass:+ $memclass}" \
1029 -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
1030 -D_FORM_='' \
1031 -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
1032 $layout/html.m4.html $layout/form+dump-whead.m4.html
1033 }
1035 blog_reply_article() { # Direct link to article in some blog
1036 arid=${1:-0} # Already sanitized to digits
1037 brid=`query "SELECT rowid FROM blog WHERE \
1038 id=(SELECT blogid FROM article WHERE rowid=$arid);"`
1039 if [ -n "$brid" ]; then
1040 newurl="?replyblog+$brid#$arid"
1041 echo "Refresh: 0; $newurl"; echo
1042 exit 0
1043 else
1044 contenttype; echo
1045 echo "無効な記事番号です." | html p
1046 fi
1047 }