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