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