s4
view s4-blog.sh @ 453:512cb413228b
Add help message of "|col1|col2|..."
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Mon, 14 Aug 2017 13:51:23 +0859 |
| parents | 30ab12e54324 |
| children | 354697d936c7 |
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 quizmodefile=$td/quiz; rm -f "$quizmodefile" # XXX: Global state
179 query<<-EOF |
180 SELECT coalesce((SELECT "yes" FROM blog
181 WHERE rowid=$rowid AND author='$user'),
182 ''),
183 max(CASE key WHEN 'ctime' THEN val END) ctime,
184 max(CASE key WHEN 'heading' THEN hex(val) END) heading,
185 CASE (SELECT val FROM $ts WHERE key="mode" AND id="$id")
186 WHEN 'report-closed' THEN 'レポート提出用(closed)'
187 WHEN 'report-open' THEN 'レポート提出用(open)'
188 WHEN 'quiz' THEN 'クイズ'
189 ELSE ''
190 END
191 FROM $ts WHERE id='$id' GROUP BY id;
192 EOF
193 { IFS='|' read edit ctime hexhead blogtype
194 cat<<-EOF
195 <tr><td>${edit:+$href }$ctime $blogtype $href2$href3 $href4</td></tr>
196 <tr class="preface${frozen_class:+ }$frozen_class">
197 <td>`echo "$hexhead"|unhexize|hreflink|minitbl`</td></tr>
198 </table>
199 EOF
200 case "$blogtype" in
201 "クイズ")
202 echo "クイズモードは本人と管理者の書き込みのみが表示されます。"
203 ;;
204 esac | html p 'class="warn"'
205 echo '<table class="blog_replies">'
206 if [ x"$blogtype" = x"クイズ" ]; then
207 if $isgroup; then
208 if ! isgrpowner "$user" "$blogowner"; then
209 qgrp=`sqlquote "$blogowner"`
210 cat<<-EOF > $quizmodefile
211 AND (author IN (SELECT user FROM grp_adm WHERE gname=$qgrp)
212 OR
213 author='$user')
214 EOF
215 fi
216 else # if user's blog
217 if [ x"$user" != x"$blogowner" ]; then
218 cat<<-EOF > $quizmodefile
219 AND author IN ('$blogowner', '$user')
220 EOF
221 fi
222 fi
223 fi
224 }
225 lkhome="<a href=\"$myname?home" lke='">'
226 lkedit="<a href=\"$myname?editart"
227 hlink="$myname?home" elink="$myname?editart"
228 catlink="$myname?showattc+article_m"
229 deficon="img/file-icon.png"
230 # 2016-08-15 Newer flag introduced
231 atime=`query "SELECT time FROM acclog
232 WHERE tbl='blog' AND tblrowid=$rowid AND user='$user';"`
233 iconcleaner=$tmpd/iconcleaner.$$
234 [ -s $quizmodefile ] && cond_qz=`cat $quizmodefile`
235 # *** DO NOT USE query(), use "sq $db" instead here ***
236 # because the next block in pipe line uses query() repeatedly.
237 sq $db<<EOF |
238 WITH a_s AS (
239 SELECT id,
240 max(CASE key WHEN 'ctime' THEN val END) TIME,
241 max(CASE key WHEN 'text' THEN val END) TEXT
242 FROM article_s
243 GROUP by id
244 )
245 SELECT a.id,
246 CASE author
247 WHEN '$user' THEN a.rowid||'+'||$rowid
248 ELSE ''
249 END edit,
250 CASE -- 「通知送信」ボタンの有無
251 WHEN '$notifyto' = '' THEN '' -- 不要モードならなし
252 WHEN '$user' = author THEN '' -- 筆者自身ならなし
253 ELSE "yes"
254 END notify,
255 (SELECT rowid FROM user WHERE name=author) user_rid,
256 coalesce((SELECT val FROM user_s
257 WHERE name=author AND key='gecos'),
258 author) uname,
259 (SELECT val FROM user_s WHERE name=author AND key='$iconcachekey')
260 icon,
261 a.rowid,
262 s.TIME,
263 CASE WHEN s.TIME > '$atime' THEN 'new' ELSE '' END newer,
264 hex(s.TEXT),
265 (SELECT group_concat(rowid||':'||length(bin)||':'||hex(val), ' ')
266 FROM article_m
267 WHERE id=a.id AND key='image') imxgids
268 FROM (select rowid,id,author from article
269 where blogid in
270 (select id from blog where rowid=$rowid)
271 $cond_qz) a
272 LEFT JOIN
273 a_s s
274 ON a.id=s.id;
275 EOF
276 while IFS='|' read id edit notify uid uname icon aid tm new hte imgids; do
277 cachefile="$td/$id.row.html"
278 stampfile="$td/$id.row.stamp"
279 editlink="${edit:+<a href="$elink+$edit">編集</a> }"
280 nt="<label style=\"font-size: 70%;\"><input type=\"checkbox\"\
281 name=\"notifyto\" value=\"$uid\">返信通知送信</label>"
283 # First, check the availability of user-icon.
284 # If not existent, clear and reset row cache by rm $stampfile
285 if [ ! -s "$icon" ]; then
286 rm -f "$stampfile"; unset stampfile
287 fi
288 if test -s "$stampfile" &&
289 test -s "$cachefile" &&
290 { ts=`cat "$stampfile"`; test -n "$ts"; } &&
291 test "$ts" '>' "$tm" && # Cache timestamp is newer
292 test "$stampfile" -nt "$icon"; then # UserIcon is older
293 : Nothing to do
294 else
295 { ######## New ROW creation begins here ######## >$cachefile
296 tdcls="__NEWCLS__repatt"
297 if [ -s "$icon" ]; then
298 icfn=`echo "$icon"|htmlescape`
299 picon="<p class=\"proficon\"><a href=\"$hlink+$uid\"><img src=\"$icfn\"></a></p>"
300 else
301 echo "DELETE FROM user_s WHERE key='$iconcachekey' AND
302 val=`sqlquotestr \"$icon\"`;" >> $iconcleaner
303 picon=""
304 fi
306 cat<<EOF
307 <tr id="$id">
308 <td class="$tdcls">${picon}__EDIT__<a href="#$aid">#$aid</a>
309 <a href="$hlink+$uid">$uname</a>
310 $tm
311 <__NOTIFY__></td>
312 EOF
313 echo -n "<td id=\"$aid\" class=\"repl\">"
314 echo "$hte"|unhexize|htmlescape|hreflink|minitbl
315 usecache='' tsfile=$td/$id.stamp
316 for i in $imgids; do
317 mrid=${i%%:*}; i=${i#*:}; sz=`size_h ${i%%:*}`
318 fn=`echo "${i#*:}"|unhexize`
319 fnb=$fn"(${sz})"
320 case "$fn" in
321 *.[Pp][Nn][Gg]|*.[Jj][Pp][Gg]|*.[Jj][Pp][Ee][Gg]|*.[GgTt][Ii][Ff])
322 # fmt=${fn##*.} # convert - jpg:- is slow...why
323 case "$fn" in
324 *.[Pp][Nn][Gg]) fmt=png ;;
325 *.[Gg][Ii][Ff]) fmt=gif ;;
326 *) fmt=jpeg ;;
327 esac
328 outfile=$td/$mrid-${fn%.*}.$fmt
329 #err fn=$fn outfile=$outfile
330 #err "usecache=$usecache `ls -l $outfile`"
331 #err tm=$tm
332 #err tsfile=$tsfile=`cat $tsfile`
333 if [ -s "$outfile" ] && # $outfile should be > 0
334 { [ "$usecache" ] || # And usecache flag is true, or...
335 { [ -s "$tsfile" ] && [ x"`cat $tsfile`" = x"$tm" ]
336 };}; then
337 usecache=1 # Set usecache flag on
338 cat<<-EOF
339 <a href="$catlink+$mrid"><img src="$outfile">
340 $fnb</a>
341 EOF
342 # !!NOTE!! Create row stamp ONLY WHEN imgcache is active
343 else
344 query "SELECT hex(bin) FROM article_m WHERE rowid=$mrid;" \
345 | unhexize \
346 | convert -define ${fmt}:size=100x100 -resize 100x100'>' \
347 - ${fmt}:- \
348 | tee "$outfile" \
349 | hexize \
350 | sed -e 's/\(..\)/%\1/g' \
351 -e "s|^|<a href=\"$catlink+$mrid\"><img src=\"data:image/$fmt,|" \
352 -e "s|\$|\">$fnb</a>|"
353 unset stampfile # img data stream is not suitable to cache
354 echo $tm > $tsfile
355 fi
356 ;;
357 *)
358 echo "<a href=\"$catlink+$mrid\"><img src=\"$deficon\">$fnb</a>"
359 ;;
360 esac
361 done
362 echo "</td></tr>"
363 } > "$cachefile" ######## New ROW Creation Ends here ########
364 test -n "$stampfile" && date "+%F %T" > $stampfile
365 fi
366 # Printing a cached row
367 sed -e "/^<td class=/s/__NEWCLS__/$new${new:+ }/" \
368 -e "/^<td class=/s,__EDIT__,$editlink," \
369 -e "/^<__NOTIFY__>/s,,${notify:+$nt}," \
370 $cachefile
371 done
373 help="=== コメントに使用できる特殊記法 ===
374 行頭に href=URL でURLへのリンク
375 行頭に iframe=URL でURL先を開く iframe
376 [[#記事番号]] でs4内の記事番号に飛ぶリンク
377 [[URL]] でURLへのリンク
378 [[URL|文字列]] でアンカー文字列を指定してのURLリンク
379 {{画像URL}} でインライン画像
380 {{画像URL|幅}} でピクセル幅を指定したインライン画像
381 {{{URL}}} でURL先を開く iframe
382 {{{URL|高さ}}} でピクセル高さを指定した iframe
383 ## 大見出し
384 ### 中見出し
385 #### 小見出し
386 |列1|列2|列3… と行頭から始まる縦棒区切り行を続けて表"
387 touchhelp="${touchpanel:+<p class=\"help\">$help</p>}"
388 # touchhelp="<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 r=$? # =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 r=$? # =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 r=$? # =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 lsmyfile() { # $1(optional)=SortBy
639 case "$1" in
640 ""|CTIME-DESC)
641 by="CTIME" ord="DESC" ;;
642 CTIME*) by="CTIME" ;;
643 FILE*) by="FILE" ;;
644 OWNER*) by="OWNER" ;;
645 TITLE*) by="TITLE" ;;
646 esac
647 case "$1" in
648 *DESC) ord="DESC" ;;
649 esac
650 case "$ord" in
651 DESC) lkod="" jord="降順" ;;
652 *) lkod="-DESC" jord="昇順" ;;
653 esac
654 sql="select m.val||'/'||m.rowid FILE,
655 coalesce(
656 case when (select name from user where name=bs.owner)
657 is not null
658 then (select val from user_s where name=bs.owner
659 and key='gecos')
660 when (select gname from grp where gname=bs.owner)
661 is not null
662 then (select val from grp_s where gname=bs.owner
663 and key='gecos')
664 else
665 null
666 end,
667 bs.owner
668 ) OWNER,
669 a_s.val CTIME,
670 ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
671 from (select rowid,id,val from article_m where id
672 in (select id from article where author='$user')
673 and type like 'file:%')
674 m left join article a on m.id=a.id
675 left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
676 left join (select id,
677 max(case key when 'owner' then val end) as owner,
678 max(case key when 'title' then val end) as title
679 from blog_s group by id)
680 bs on a.blogid=bs.id
681 left join blog b on bs.id=b.id
682 where m.val is not null order by $by $ord;"
683 err lshandoutbyauthor: sql=`echo "$sql"`
684 title="個人提出ファイル"
685 _m4 -D_TITLE_=$title $layout/html.m4.html
686 hra="<a href=\"?lsmyfile+"
687 hrb="<a href=\"?showattc+article_m+"
688 hrc="<a href=\"?replyblog+"
689 (echo '<table class="b">'
690 echo "$sql"|sq -html -header $db ) \
691 | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
692 -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
693 -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
694 | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
695 -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
696 echo '</table>'
697 }
698 searchart() {
699 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC
700 kwdgrp=""
701 authcond=""
702 if [ -z "$kwd" ]; then
703 echo "検索語を指定してください" | html p; return
704 fi
705 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
706 # Like '#1234', assume as artID
707 rowid=$((${kwd#\#} + 0)) # Force to be a number
708 kc="ar.rowid = $rowid"
709 else
710 for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes
711 if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
712 # '@<2016-10-10' -> ctime < '2016-10-10'
713 # '@>=2016-10-10' -> ctime >= '2016-10-10'
714 # '@2016-10-10' -> ctime GLOB '@2016-10-10'
715 k=${k#@}
716 case "$k" in
717 [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
718 *) op='GLOB'; ctime="${k##*[><= ]}*" ;;
719 esac
720 kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
721 # Not sure GROUP BY a.blogid is comfortable for searchers...?
722 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results
723 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
724 ctime=`date +%F`
725 kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'"
726 elif [ x"$k" = x"@week" ]; then
727 ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
728 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
729 elif [ x"$k" = x"@month" ]; then
730 ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
731 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
732 elif [ x"$k" = x"@year" ]; then
733 ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
734 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
735 else
736 kc=$kc${kc:+" AND "}"content LIKE '%$k%'"
737 fi
738 done
739 fi
740 kwd=`echo "$kwd"|htmlescape`
741 owner=`getpar owner`
742 owner=${owner:-$1}
743 echo "「$kwd」による検索結果" | html p
744 if [ -n "$owner" ]; then
745 cond="where key='owner' and val='$owner'"
746 if isuser $owner; then
747 echo "(`linkhome $owner` さんの記録からの検索)" | html p
748 else
749 linkhome $owner 1>&3
750 echo "(`linkhome $owner` グループからの検索)" | html p
751 fi
752 elif { author=`getpar author`; test -n "$author"; }; then
753 atptn=`sqlquotestr $author`
754 #kc="$kc${kc:+ AND }author=$atptn"
755 authcond="WHERE author=$atptn"
756 if isuser $author; then
757 echo "(`linkhome $author` さんの書き込みからの検索)" | html p
758 fi
759 fi
760 # article_s: id=article-id, key='text', val='TEXT'
761 # article: id=article-id, blogid=blogkd
762 # blog: id=blog-id, author=LeaderAuthor
763 # blog_s: id=blog-id, key='title', val='BLOG-TITLE'
764 # WANT: blog-ROWid,article-id,val(TEXT)
765 sql2="`sql4readableblogs` -- Extract user-readable blogs
766 -- 0.3sec
767 WITH artsm AS (
768 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
769 FROM article a
770 LEFT JOIN
771 (SELECT ars.id, ctime, text, coalesce(files, '') files
772 FROM (SELECT id,
773 max(CASE key WHEN 'ctime' THEN val END) ctime,
774 max(CASE key WHEN 'text' THEN val END) text
775 FROM article_s
776 GROUP BY id) ars
777 LEFT JOIN
778 (SELECT id, group_concat(val) files
779 FROM article_m
780 WHERE type LIKE 'file:%'
781 GROUP BY id) arm
782 ON ars.id=arm.id
783 ) ar
784 ON a.id=ar.id
785 ), ar AS (
786 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
787 FROM article a JOIN artsm ON a.id=artsm.id
788 $authcond
789 ), bl AS (
790 SELECT blg.rid, blg.*, blog_s.val TITLE
791 FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
792 )
793 SELECT bl.rid||'#'||ar.id '',
794 bl.title TITLE,
795 (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
796 substr(ctime, 0, 11) DATE,
797 substr(content, 0, 78) TEXT
798 FROM ar JOIN bl
799 ON ar.blogid=bl.id
800 WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond)
801 ORDER by DATE DESC, TITLE, ctime;"
802 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
803 href=\"?replyblog+\1\">VIEW</a></TD>,"
804 # echo "$sql2" > tmp/sql.out
805 result=$tmpd/result.$$
806 cat<<EOF
807 <table class="b searchart">
808 `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
809 </table>
810 EOF
811 if [ -s "$result" ]; then
812 found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT
813 one=${found%1}
814 echo "$found match${one:+es} found"
815 else
816 echo orz...
817 fi
818 }
819 listblog() (
820 # $1={user,group}
821 qow=`sqlquote $1`
822 cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
823 DT_CHLD=article:blogid
824 cgi_form searchart<<EOF
825 <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
826 `cgi_hidden owner $user`
827 EOF
828 dumptable html blog 'ctime title heading' "$cond"
829 )
831 blog_addentry() {
832 # $1=GRPname(if it is a group)
833 grprowid=$1
834 rowid=`getpar rowid`
835 ## err blog_addentry0: rowid=$rowid
836 if [ -n "$grprowid" ]; then
837 owner=`getgroupbyid $grprowid`
838 else
839 owner=`getpar owner`
840 fi
841 err blog-add: \$1=$1 rowid=$rowid owner=$owner
842 if isgroup "$owner"; then
843 groupmode=1 listing=$owner guide="[${owner}]" GF_OWNER=$owner
844 else
845 usermode=1 listing=$user guide="[個人]"
846 fi
848 if [ -n "`getpar title`" ]; then
849 if [ "$usermode" ]; then
850 err usermode: user=$user owner=$owner
851 if [ x"$user" != x"$owner" ]; then
852 echo "他人の日記は書けません" | html p
853 return 2
854 fi
855 elif [ "$groupmode" ]; then # if write to group log
856 grp=$owner #\`getpar grp\`
857 err ismember: $user $grp
858 if ! ismember "$user" "$grp"; then
859 echo "(話題作成はこのグループに加入してから)" | html p
860 return 3
861 fi
862 fi
863 par2table $formdir/blog.def
864 serial=`getpar serial`
865 ## err SERIAL: $serial ROWID=$rowid listing=$listing
866 id=""
867 if [ -n "$rowid" ]; then
868 # Here, id becomes NULL when removal of entries at par2table
869 id=`query "select rowid from blog where rowid=$rowid;"`
870 elif [ -n "$serial" ]; then
871 # If new blog leader created, traverse to its head.
872 id=`query "select rowid from blog where id='$serial';"`
873 ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
874 fi
875 if [ -n "$id" ]; then
876 ## If new aritcle is entered, JUMP to blog_reply
877 blog_reply $id
878 return
879 fi
880 fi
881 echo "${guide}新規話題作成" > $tmpd/title.$$
882 listblog $listing > $tmpd/listblog.$$
883 genform $formdir/blog.def \
884 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
885 -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
886 -D_DUMPHEAD_="これまでの蓄積" \
887 -D_FORM_="syscmd(\`cat')" \
888 -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
889 $layout/html.m4.html \
890 $layout/form+dump-whead.m4.html
891 }
893 blog_reply() { # Posting to blog article
894 rowid=$1
896 if [ -z "$rowid" ]; then
897 echo "表示する日記番号が未指定です。" | html p
898 return
899 fi
900 title=`getvalbyid blog title $rowid`
901 owner=`getvalbyid blog owner $rowid`
902 if [ -z "$title" ]; then
903 echo "日記番号指定が無効です。" | html p
904 return
905 fi
906 blog_writable $rowid $user; rc=$?
907 if [ $rc = 0 ]; then
908 iswritable=true
909 else
910 iswritable=false
911 if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
912 isfrozen=true
913 frozen_class='frozen"'
914 frozen_flag=$FROZEN_TAG
915 fi
916 fi
917 if isuser "$owner"; then
918 subtitle="`gecos $owner` さんの話題"
919 else
920 grprowid=`query "select rowid from grp where gname=\"$owner\";"`
921 subtitle="グループ
922 <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$owner</a> での話題
923 `query \"SELECT printf('(チーム:%s)', val)\
924 FROM blog_s
925 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
926 AND key='team';
927 \"|htmlescape`"
928 memclass=`grp_getbodyclass "$owner"`
929 fi
931 text=`getpar text`
932 if [ -n "$text" ]; then
933 if $iswritable; then
934 par2table $formdir/article.def
935 st=$?
936 case $st in
937 0|4)
938 [ "$st" = "4" ] && act="書込削除"
939 blog_notify_reply $rowid $user "$text" $act
940 if [ -n "$grprowid" ]; then
941 qgrp=$(sqlquote "$owner")
942 dbsetbyid grp $owner wtime "`date '+%F %T'`"
943 fi
944 ;;
945 esac
946 else
947 if $isfrozen; then
948 title="$title(凍結板につき書き込み不可)"
949 else
950 title="$title(加入してないので書き込み不可)"
951 fi
952 fi
953 fi
954 def=$formdir/article.def
955 echo "$title" > $tmpd/title.$$
956 echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
957 ${BLOG_SHOW:-blog_showentry} blog $rowid \
958 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
959 -D_BODYCLASS_=general"${memclass:+ $memclass}" \
960 -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
961 -D_FORM_='' \
962 -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
963 $layout/html.m4.html $layout/form+dump-whead.m4.html
964 }
966 blog_reply_article() { # Direct link to article in some blog
967 arid=${1:-0} # Already sanitized to digits
968 brid=`query "SELECT rowid FROM blog WHERE \
969 id=(SELECT blogid FROM article WHERE rowid=$arid);"`
970 if [ -n "$brid" ]; then
971 newurl="?replyblog+$brid#$arid"
972 echo "Refresh: 0; $newurl"; echo
973 exit 0
974 else
975 contenttype; echo
976 echo "無効な記事番号です." | html p
977 fi
978 }