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