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