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