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