s4
view s4-blog.sh @ 739:c892a3633baa
Annex-test: sessdb = main-sessdb
| author | HIROSE Yuuji <yuuji@gentei.org> |
|---|---|
| date | Thu, 04 Jun 2020 20:31:25 +0900 |
| parents | 19ee38a90e22 |
| children | 42cc0aeaa498 |
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_getteam() {
31 # $1=rowid of blog
32 blogid="${1%%[!A-Z0-9a-z_]*}"
33 # team cannot get `getvalbyid blog team "$blogid"` because it's not
34 # defined in blog.def. Yes, it is Illegal USE!!
35 query "SELECT val FROM blog_s
36 WHERE id=(SELECT id FROM blog WHERE rowid=$blogid)
37 AND key='team';"
38 }
39 blog_notify_reply() (
40 # $1=blogid $2=ReplyingUser $3=WrittenText $4(optional)=Action
41 blogid="${1%%[!A-Z0-9a-z_]*}"
42 blogowner=`getvalbyid blog owner "$blogid"`
43 blogtitle=`getvalbyid blog title "$blogid"`
44 blogurl="$urlbase?replyblog+$blogid"
45 action=${4:-書き込み}
46 mode=`getvalbyid blog notify "$blogid"`
47 isgroup "$blogowner" && _isgroup=true || _isgroup=false
48 ### EXCEPT=`sqlquote "$user"` ## User should receive to feal some annoyance
49 case $mode in
50 admin)
51 if $_isgroup; then
52 emails=`getgroupadminmails $blogowner`
53 else
54 emails=`collectemail $blogowner`
55 fi
56 notifyto=`getpar notifyto`
57 if [ -n "$notifyto" ]; then
58 emails=$emails" `email4groupbyuid \"$blogowner\" $notifyto`"
59 fi
60 ;;
61 no) emails="" ;;
62 *) team=`blog_getteam "$blogid"`
63 # team cannot get by `getvalbyid blog team "$blogid"`
64 emails=`TEAM=$team collectemail $blogowner` ;;
65 esac
66 ## 2017-0210 Respond to the direct reply mark such as: >#1234
67 replymark=`echo "$3"|nkf -w -Z0|grep '^ *>#'`
68 authgecos=`gecos $2`
69 if [ -z "$4" -a -n "$replymark" ]; then
70 # If the action is new subscription($4="") and has ">#123" marks...
71 ids=`echo "$replymark"|sed 's/[^#0-9]*#\([0-9]*\)[^#0-9]*/\1 /g'`
72 ids=`echo $ids|tr -dc '[0-9 ]'|tr ' ' ','`
73 # -> 123,345,347
74 unames=`query "SELECT distinct author FROM article \
75 WHERE rowid in ($ids)\
76 AND blogid=(SELECT id FROM blog WHERE rowid=$blogid);"`
77 if [ -n "$unames" ]; then
78 e4g=$(if $_isgroup; then
79 email4group "$blogowner" $unames
80 else
81 for u in $unames; do
82 collectemail $u
83 done
84 fi)
85 emails=$emails" $e4g"
86 for e in $unames; do
87 g=`gecos $e`
88 whom=$whom"${whom:+,}${g:-$e}さん"
89 done
90 action="${whom}への返信"
91 fi
92 else
93 [ x"$2" = x"$blogowner" ] && return # If author=blogowner, unnecessary
94 fi
95 test -z "$emails" && return
96 err notify: user=$user Admins=`getgroupadmins $blogowner` Mode=$mode Emails="[$emails]"
97 MAIL_FROM=$noreply_from \
98 SMAIL_TO="`echo "$blogowner" | nkf -jM | tr -d '\n'` readers <$noreply>" \
99 smail "$emails" "${action}通知 $urlbase"<<EOF
100 [$blogtitle]板に${action}がありました。
101 ※※※このメイルには返信できません(返信は次のURLへ)※※※
102 場所: $blogurl (返信先)
103 所有: $blogowner
104 題目: $blogtitle
105 筆者: $authgecos
106 内容:
107 `echo "$3"|sed 's/^/> /'`
109 ※※このメイルに返信しても通知者には伝わりません。
110 ※※上記URLから${S4NAME:-s4}掲示板に書き込んでください。
111 EOF
112 )
114 blog_showentry() {
115 # $1=table $2=rowid
116 # if [ -n "$2" ]; then
117 # if [ -n "$imgcached" ]; then
118 # bstmpdir=$tmpdir/$imgcached/$thumbxy
119 # else
120 # bstmpdir=$tmpd
121 # # tmpd=`mktempd`
122 # # tmpfiles=$tmpfiles" $tmpd"
123 # fi
124 # fi
125 td=`getcachedir "article/$2"`
126 [ -d "$td" ] || mkdir -p $td
127 tbl=${1%%[!A-Z0-9a-z_]*} rowid=${2%%[!A-Z0-9a-z_]*}
128 err blow_showentry: rowid=$rowid, '$2'=$2 user=$user
129 ts=${tbl}_s tm=${tbl}_m
130 at=article as=article_s am=article_m
131 serial=$(($(date +%s)-1420038000))s$$
132 cannotread='<div class="relative"><img class="overlap" src="img/key.png" alt="(読み取り不可)"></div>'
133 blog_writable $rowid $user
134 rc=$?
135 if [ $rc = 0 ]; then
136 iswritable=true
137 ismem=true
138 else
139 iswritable=false
140 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ]; then
141 ismem=false
142 else
143 ismem=true
144 fi
145 fi
146 # This function grasps blog entry definiton directly.
147 # blog: id
148 # blog_s: title,ctime,heading
149 # blog_m: *article
151 blogowner=`getvalbyid blog owner "$2"`
152 isgroup "$blogowner" && isgroup=true || isgroup=false
153 isgrpadmin=false
154 isgrpowner "$user" "$blogowner" && isgrpadmin=true
156 # 2015-10-05 check readable
157 if ! $iswritable; then
158 # err blogowner=$blogowner
159 if $isgroup; then
160 regmode=`getgroupattr $blogowner regmode`
161 # err regmode=$regmode
162 if [ x"$regmode" = x"moderated" ]; then
163 # if ! ismember $user $blogowner; then
164 if ! $ismem; then
165 echo "加入してからどうぞ" | html p
166 return
167 fi
168 fi
169 fi
170 fi
171 blog_notify=`getvalbyid blog notify "$rowid"`
172 blog_team=`blog_getteam "$rowid"`
173 blog_mode=`getvalbyid blog mode "$rowid"`
174 case "$blog_notify" in # "all", "admin" or "no" (or NULL)
175 admin) notifyto=adm ;;
176 *) notifyto="" ;;
177 esac
178 case $blog_mode in
179 *quiz*|*close*)
180 f_exclusive=1
181 if $isgroup; then
182 qgrp=`sqlquote "$blogowner"`
183 if $isgrpadmin; then
184 F_UNREADABLE="''"
185 else
186 if [ x"$blog_mode" = x"quiz" ]; then
187 F_UNREADABLE="CASE
188 WHEN author IN (SELECT user FROM grp_adm WHERE gname=$qgrp)
189 THEN ''
190 WHEN author = '$user'
191 THEN ''
192 ELSE 'Unreadable'
193 END"
194 else
195 F_UNREADABLE="'Unreadable'"
196 fi
197 fi
198 else # User blog
199 if [ x"$blog_mode" = x"quiz" ]; then
200 F_UNREADABLE="CASE
201 WHEN author = '$blogowner'
202 THEN '' ELSE 'Unreadable'
203 END"
204 else
205 F_UNREADABLE="'Unreadable'"
206 fi
207 fi
208 ;;
209 *) f_exclusive=''
210 F_UNREADABLE="''"
211 ;;
212 esac
214 # err "SELECT id from $tbl where rowid=$rowid"
215 id=`query "select id from $tbl where rowid=$rowid;"`
216 #err id=$id
217 #err "select val from $ts where key='title' and id='$id';"
219 #(1)Display root article
220 cat<<EOF
221 <form class="replyblog" action="$myname?replyblog+${rowid}#bottom" method="POST" enctype="multipart/form-data">
222 <table class="bloghead">
223 EOF
225 href="<a href=\"?editheading+$rowid\" accesskey=\"e\" title=\"Shortcut: E${nl}Edit\"> 編集 </a>"
226 if $ismem; then
227 case $blog_mode in
228 *report*|*quiz*|*enquete*)
229 href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"Shortcut: L${nl}List Handouts\"> 提出状況 </a>"
230 href3="(<a href=\"?gethandout+$rowid\" accesskey=\"f\" title=\"Shortcut: F${nl}File Retrieval\">ファイル取得</a>)"
231 ;;
232 esac
233 fi
234 href4="<a href=\"#bottom\" accesskey=\"b\" title=\"Shortcut: B${nl}to the Bottom\"> 末尾へ</a>"
235 $isgrpadmin &&
236 href5="<a href=\"?blogseen+$rowid\" accesskey=\"s\" title=\"Shortcut: S${nl}State of Accesses\"> 読刻</a>"
237 quizmodefile=$tmpd/quiz; rm -f "$quizmodefile" # XXX: Global state
238 midfile=$tmpd/midfile
240 query<<-EOF > $midfile
241 SELECT coalesce((SELECT "yes" FROM blog
242 -- GrpAdmin CAN EDIT heading since 2019-08-15
243 WHERE '$isgrpadmin' = 'true'
244 OR (rowid=$rowid AND author='$user')),
245 ''),
246 max(CASE key WHEN 'ctime' THEN val END) ctime,
247 max(CASE key WHEN 'heading' THEN hex(val) END) heading,
248 CASE (SELECT val FROM $ts WHERE key="mode" AND id="$id")
249 WHEN 'report-closed' THEN 'レポート提出用(closed)'
250 WHEN 'report-open' THEN 'レポート提出用(open)'
251 WHEN 'quiz' THEN 'クイズ'
252 WHEN 'enquete' THEN '集計'
253 ELSE ''
254 END
255 FROM $ts WHERE id='$id' GROUP BY id;
256 EOF
257 if test -s $midfile && IFS='|' read edit ctime hexhead blogtype < $midfile
258 then
259 cat<<-EOF
260 <tr><td>${edit:+$href }$ctime $blogtype $href2${edit:+$href3} $href4 $href5</td></tr>
261 <tr class="preface${frozen_class:+ }$frozen_class">
262 <td>`echo "$hexhead"|unhexize|htmlescape|hreflink|minitbl`</td></tr>
263 </table>
264 EOF
265 case "$blogtype" in
266 "クイズ"|"XXXX集計")
267 echo "${blogtype}モードは本人と管理者の書き込みのみが表示されます。"
268 ;;
269 esac | html p 'class="warn"'
270 echo '<table class="blog_replies"> <!-- blog:blog_showentry() main table -->'
271 if [ x"$blogtype" = x"クイズ" -o x"$blogtype" = x"XXXX集計" ]; then
272 if $isgroup; then
273 # Failsafe to query timeout
274 qgrp=`sqlquote "$blogowner"`
275 cat<<-EOF > $quizmodefile
276 AND (author IN (SELECT user FROM grp_adm WHERE gname=$qgrp)
277 OR
278 author='$user')
279 EOF
280 if $isgrpadmin; then #
281 : > $quizmodefile
282 fi
283 else # if user-blog
284 if [ x"$user" != x"$blogowner" ]; then
285 cat<<-EOF > $quizmodefile
286 AND author IN ('$blogowner', '$user')
287 EOF
288 fi
289 fi
290 fi
291 else # Cannot read SQL output
292 echo "時間をおいて繋いでください(Please visit later)." | html p
293 return
294 fi
295 lkhome="<a href=\"$myname?home" lke='">'
296 lkedit="<a href=\"$myname?editart"
297 hlink="$myname?home" elink="$myname?editart"
298 catlink="$myname?showattc+article_m"
299 deficon="img/file-icon.png"
300 # 2016-08-15 Newer flag introduced
301 atime=`query "SELECT time FROM acclog
302 WHERE tbl='blog' AND tblrowid=$rowid AND user='$user';"`
303 iconcleaner=$tmpd/iconcleaner.$$
304 [ -s $quizmodefile ] && cond_qz=`cat $quizmodefile`
305 # *** DO NOT USE query(), use "sq $db" instead here ***
306 # because the next block in pipe line uses query() repeatedly.
307 ###### TEST: 2020-04-23 Use intermediate file to shorten duration of db-lock
308 ###### sq $db<<EOF |
309 query <<EOF > $midfile
310 WITH a_s AS (
311 SELECT id,
312 max(CASE key WHEN 'ctime' THEN val END) TIME,
313 max(CASE key WHEN 'text' THEN val END) TEXT
314 FROM article_s
315 GROUP by id
316 )
317 SELECT a.id,
318 CASE author
319 WHEN '$user' THEN a.rowid||'+'||$rowid
320 ELSE ''
321 END edit,
322 CASE -- 「通知送信」ボタンの有無
323 WHEN '$notifyto' = '' THEN '' -- 不要モードならなし
324 WHEN '$user' = author THEN '' -- 筆者自身ならなし
325 ELSE "yes"
326 END notify,
327 (SELECT rowid FROM user WHERE name=author) user_rid,
328 author,
329 coalesce((SELECT val FROM user_s
330 WHERE name=author AND key='gecos'),
331 author) uname,
332 (SELECT val FROM user_s WHERE name=author AND key='$iconcachekey')
333 icon,
334 a.rowid,
335 s.TIME,
336 CASE WHEN s.TIME < '2019-05'
337 THEN printf('平成%d年%d月%d日%s',
338 substr(s.TIME, 1, 4)-1988,
339 substr(s.TIME, 6, 2),
340 substr(s.TIME, 9, 2),
341 substr(s.TIME, 12)
342 )
343 WHEN s.TIME < '2020'
344 THEN printf('令和元年%d月%d日%s',
345 substr(s.TIME, 6, 2),
346 substr(s.TIME, 9, 2),
347 substr(s.TIME, 12))
348 WHEN s.TIME < '2050'
349 THEN printf('令和%d年%d月%d日%s',
350 substr(s.TIME, 1, 4)-2018,
351 substr(s.TIME, 6, 2),
352 substr(s.TIME, 9, 2),
353 substr(s.TIME, 12))
354 ELSE s.TIME
355 END reki,
356 CASE WHEN s.TIME > '$atime' THEN 'new' ELSE '' END newer,
357 hex(s.TEXT),
359 $F_UNREADABLE cannotread,
361 (SELECT group_concat(rowid||':'||length(bin)||':'||hex(val), ' ')
362 FROM article_m
363 WHERE id=a.id AND key='image') imxgids
364 FROM (select rowid,id,author from article
365 where blogid in
366 (select id from blog where rowid=$rowid)
367 $cond_qz) a
368 LEFT JOIN
369 a_s s
370 ON a.id=s.id;
371 EOF
372 if [ $? -ne 0 -a ! -s $midfile ]; then
373 echo "時間をおいてください(Visit later please)." | html p
374 return
375 fi
376 cat $midfile |
377 while IFS='|' read id edit notify uid author uname icon aid \
378 tm reki new hte fa imgids
379 do
380 mf2=$tmpd/midfile2
381 cachefile="$td/$id.row.html"
382 stampfile="$td/$id.row.stamp"
383 editlink="${edit:+<a href="$elink+$edit">編集</a> }"
384 nt="<label style=\"font-size: 70%;\"><input type=\"checkbox\"\
385 name=\"notifyto\" value=\"$uid\">返信通知送信</label>"
386 # fa is file accessibility flag # err "----r=$aid fa=[$fa]----"
388 # First, check the availability of user-icon.
389 # If not existent, clear and reset row cache by rm $stampfile
390 if [ ! -s "$icon" ]; then
391 rm -f "$stampfile"; unset stampfile
392 fi
393 if test -s "$stampfile" &&
394 test -s "$cachefile" &&
395 { ts=`cat "$stampfile"`; test -n "$ts"; } &&
396 /bin/test "$ts" '>' "$tm" && # Cache timestamp is newer
397 test "$stampfile" -nt "$icon"; then # UserIcon is older
398 : Nothing to do
399 else
400 { ######## New ROW creation begins here ######## >$cachefile
401 cachestamp=$tmpd/cache.$$.stamp
402 touch $cachestamp
403 tdcls="__NEWCLS__repatt"
404 if [ -s "$icon" ]; then
405 icfn=`echo "$icon"|htmlescape`
406 picon="<p class=\"proficon\"><a href=\"$hlink+$uid\" title=\"${author%@*}\"><img src=\"$icfn\"></a></p>"
407 else
408 echo "DELETE FROM user_s WHERE key='$iconcachekey' AND
409 val=`sqlquotestr \"$icon\"`;" >> $iconcleaner
410 picon=""
411 fi
413 cat<<EOF
414 <tr id="$id">
415 <td class="$tdcls">${picon}__EDIT__<a href="#$aid">#$aid</a>
416 <a href="$hlink+$uid" title="${author%@*}">$uname</a>
417 <span title="$tm">${reki:-$tm}</span>
418 <__NOTIFY__></td>
419 EOF
420 echo -n "<td id=\"$aid\" class=\"repl\">"
421 echo "$hte"|unhexize|htmlescape|hreflink|minitbl
422 usecache='' tsfile=$td/$id.stamp
423 for i in $imgids; do
424 mrid=${i%%:*}; i=${i#*:}; sz=`size_h ${i%%:*}`
425 fn=`echo "${i#*:}"|unhexize`
426 fnb=$fn"(${sz})"
427 case "$fn" in
428 *.[Pp][Nn][Gg]|*.[Jj][Pp][Gg]|*.[Jj][Pp][Ee][Gg]|*.[GgTt][Ii][Ff])
429 # fmt=${fn##*.} # convert - jpg:- is slow...why
430 case "$fn" in
431 *.[Pp][Nn][Gg]) fmt=png ;;
432 *.[Gg][Ii][Ff]) fmt=gif ;;
433 *) fmt=jpeg ;;
434 esac
435 outfile=$td/$mrid-${fn%.*}.$fmt
436 #err fn=$fn outfile=$outfile
437 #err "usecache=$usecache `ls -l $outfile`"
438 #err tm=$tm
439 #err tsfile=$tsfile=`cat $tsfile`
440 if [ -s "$outfile" ] && # $outfile should be > 0
441 { [ "$usecache" ] || # And usecache flag is true, or...
442 { [ -s "$tsfile" ] && [ x"`cat $tsfile`" = x"$tm" ]
443 };}; then
444 usecache=1 # Set usecache flag on
445 cat<<-EOF
446 <a href="$catlink+$mrid"><img src="$outfile">
447 $fnb</a>
448 EOF
449 # !!NOTE!! Create row stamp ONLY WHEN imgcache is active
450 else
451 query "SELECT hex(bin) FROM article_m WHERE rowid=$mrid;" \
452 > $mf2 # Stop query here 2020-04-23
453 if cat $mf2 | unhexize \
454 | convert -define ${fmt}:size=100x100 -resize 100x100'>' \
455 - ${fmt}:- > $outfile
456 then
457 cat "$outfile" \
458 | hexize \
459 | sed -e 's/\(..\)/%\1/g' \
460 -e "s|^|<a href=\"$catlink+$mrid\"><img src=\"data:image/$fmt,|" \
461 -e "s|\$|\">$fnb</a>|"
462 unset stampfile # img data stream is not suitable to cache
463 echo $tm > $tsfile
464 else # Failed to convert
465 rm -f $outfile
466 echo "<a href=\"$catlink+$mrid\">$fnb</a>"
467 fi
468 fi
469 ;;
470 *)
471 echo "<__UNREADABLE__><a href=\"$catlink+$mrid\"><img src=\"$deficon\">$fnb</a>"
472 ;;
473 esac
474 done
475 echo "</td></tr>"
476 } > "$cachefile.$$" ######## New ROW Creation Ends here ########
477 # Care about race condition
478 if [ -s $cachefile -a $cachefile -nt $cachestamp ]; then
479 # If other process have created cache, give up to serve our file
480 rm -f $cachefile.$$
481 else
482 mv -f $cachefile.$$ $cachefile
483 fi
484 test -n "$stampfile" && date "+%F %T" > $stampfile
485 fi
486 # Printing a cached row
487 sed -e "/^<td class=/s/__NEWCLS__/$new${new:+ }/" \
488 -e "/^<td class=/s,__EDIT__,$editlink," \
489 -e "/^<__NOTIFY__>/s,,${notify:+$nt}," \
490 -e "/<__UNREADABLE__>/s,,${fa:+$cannotread}," \
491 $cachefile
492 done
494 help="=== コメントに使用できる特殊記法 ===
495 行頭に href=URL でURLへのリンク
496 行頭に iframe=URL でURL先を開く iframe
497 [[#記事番号]] でs4内の記事番号に飛ぶリンク
498 [[#検索キーワード]] でs4内の記事検索(記号はいくつか使えない)
499 [[URL]] でURLへのリンク、 [[URL|文字列]]でアンカー文字列指定
500 {{画像URL}} でインライン画像、 {{画像URL|幅}} でピクセル幅指定
501 {{{URL}}} でURL先を開く iframe、 {{{URL|高さ}}} ピクセル高さ指定
502 行頭: ## 大見出し, ### 中見出し, #### 小見出し
503 行末の2連続スペースで強制改行(<br>)
504 |*見出し列|列2|列3… と行頭から始まる縦棒区切り行を続けて表
505 ' *語群* ' で強調(両側の空白必要、** でもっと強調。*の代わりに _ でも可)
506 - [ ] と - [x] でチェックボックス"
507 touchhelp="${touchpanel:+<p class=\"help\">$help</p>}"
508 filehelp="《添付の注意》
509 $file_accept_help"
510 ntmode="通知モード=$blog_notify${blog_team:+ (team=$blog_team)}"
511 textform='<div class="fold">
512 <input type="checkbox" id="cmt" checked><label
513 accesskey="c" title="C" for="cmt">コメントする</label><div>
514 <table class="b">
515 <tr><td><textarea id="text" name="text" cols="72" rows="4" title="'"$help"'">
516 </textarea>'"$touchhelp</td></tr>
517 <tr><td>添付ファイル(${filesize_max_MB}以下):"'
518 <input type="file" name="image"'" $file_accept title=\"$filehelp\" multiple></td></tr>"'
519 </table>
520 <input type="submit" value="送信"'" class=\"$blog_notify\" title=\"$ntmode\""'>
521 <input type="reset" value="リセット"></div></div>
522 '
523 cat<<-EOF
524 </table> <!-- end of s4-blog:blog_showentry() main table -->
525 <p class="update_link"><a
526 href="?reload/$rowid" accesskey="r"
527 title="Shortcut: R${nl}Reload">再読込</a> / <a
528 href="#title" id="bottom" accesskey="t"
529 title="Shortcut: T${nl}to the Top">先頭へ</a></p>
530 EOF
531 $iswritable && cat<<-EOF
532 <div class="blogcomment">
533 <input type="hidden" name="blogid" value="$id">
534 <input type="hidden" name="id" value="`genserial`">
535 <input type="hidden" name="stage" value="replyblog">
536 $textform
537 </div>
538 </form> <!-- End of s4-blog:blog_showentry() main form -->
539 EOF
540 # Clean up orphaned icon cache
541 [ -s $iconcleaner ] && query ".read '$iconcleaner'"
542 # Record access log
543 acclog blog $rowid
544 }
546 lshandout() {
547 # $1=rowid of blog
548 blog_writable $1 $user
549 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
550 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
551 echo "メンバー以外は利用できません。" | html p; return
552 fi
553 time=`getvalbyid blog ctime $1|colrm 11`
554 owner=`getvalbyid blog owner $1`
555 title=`getvalbyid blog title $1`
556 ge=`gecos $owner`
557 fh=$tmpd/formhead
558 echo "$time [$title]@${ge:-$owner}" > $fh
559 lshandoutsub $owner "$@" \
560 |_m4 -D_TITLE_="提出状況" \
561 -D_FORMHEAD_="syscmd(cat $fh)" \
562 -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \
563 $layout/html.m4.html $layout/form+dump-whead.m4.html
564 gn=`echo $owner|htmlescape`
565 echo "<p><a href=\"?lshandoutall+$1\">グループ $gn すべてのレポート板集計</a></p>"
566 }
567 gethandoutcsv() {
568 # contenttype; echo
569 CATCSV=1 lshandoutall "$1"
570 }
571 gethandoutcsv2() {
572 # contenttype; echo
573 SQL=$(cat<<-EOF
574 WITH this_blog_articles AS (
575 SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime
576 FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid
577 ), text_or_file AS (
578 SELECT bid, author, title, ctime, 'text' shu, count(val) cnt
579 FROM this_blog_articles tba, article_s s
580 ON tba.aid=s.id
581 WHERE key='text'
582 GROUP by bid, author
583 UNION
584 SELECT bid, author, title, ctime, 'file' shu, count(val) cnt
585 FROM this_blog_articles tba, article_m m
586 ON tba.aid=m.id
587 WHERE key='image'
588 GROUP by bid, author
589 ), count_list AS (
590 SELECT author,
591 substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit,
592 cnt
593 FROM text_or_file
594 )
595 SELECT gecos "名前",
596 substr(author, 1, instr(author, '@')-1) "uname",
597 unit,
598 cnt "post"
599 FROM count_list cl JOIN gecoses g ON cl.author=g.name;
600 EOF
601 ) gethandoutcsv "$1"
602 }
603 lshandout_ulink_table() {
604 # NO Args. Read stdin as SQL
605 echo '<table class="b td3rr td3evw">'
606 hrb="<a href=\"?home+"
607 # echo "$sql" | sq -header -html $db \ # Formerly, this is called via sq()
609 printf ".mode html\n.header ON\n" | query
610 cat | query \
611 | sed -e "s,\(<TR><TD>\)\([^ ]*\) \(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,'
612 echo '</table>'
613 printf ".mode list\n.header OFF\n" | query
614 }
615 lshandoutall() {
616 # $1=rowid of blog
617 blog_writable $1 $user
618 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
619 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
620 echo "メンバー以外は利用できません。" | html p; return
621 fi
622 rowid=$(($1 + 0))
623 owner=`getvalbyid blog owner $1`
624 qowner=`sqlquotestr "$owner"`
626 query<<-EOF
627 CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS
628 WITH blog_owner_mode AS (
629 SELECT id,
630 blog.rowid brid,
631 max(CASE key WHEN 'owner' THEN val END) owner,
632 max(CASE key WHEN 'mode' THEN val END) mode,
633 max(CASE key WHEN 'title' THEN val END) title,
634 max(CASE key WHEN 'ctime' THEN val END) ctime
635 FROM blog NATURAL JOIN blog_s
636 GROUP BY id
637 )
638 SELECT id, brid, title, ctime FROM blog_owner_mode
639 /* WHERE owner=$qowner AND mode LIKE '%report%'; */
640 WHERE owner=$qowner
641 AND
642 (mode LIKE '%report%' OR mode LIKE '%quiz%'
643 OR mode LIKE '%enquete%');
644 /* ↑これでレポート形式の blogid 一覧を得る */
645 EOF
646 if [ -z "$CATCSV" ]; then
647 _m4 -D_TITLE_="提出状況" $layout/html.m4.html
648 ge=`gecos "$owner"`
649 tbls=""
650 grptxt=`echo "${ge:-$owner}"|htmlescape`
651 echo "<h1>$grptxt 書き込み状況一覧</h1>"
652 fi
653 if [ -z "$SQL" ]; then
654 bridlist=`query "SELECT brid FROM report_type_blogs;"`
655 for brid in $bridlist; do # Skip this loop if $SQL set
656 brid=$(($brid + 0)) # Ensure to be a number
657 [ $brid = 0 ] && continue
658 time=`getvalbyid blog ctime $brid|colrm 11`
659 title=`getvalbyid blog title $brid|htmlescape`
660 state=`getvalbyid blog state $brid|htmlescape`
661 tt="handout_$brid"
662 [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen=""
663 if [ -z "$CATCSV" ]; then
664 echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a>$frozen</h2>"
665 lshandoutsub "$owner" $brid "$tt"
666 else
667 lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table
668 fi
669 tbls="$tbls${tbls:+ NATURAL JOIN }$tt"
670 done
671 fi
672 sql=${SQL:-"SELECT * FROM $tbls;"}
673 if [ -z "$CATCSV" ]; then
674 echo "<hr><h2>総合</h2>"
675 echo "$sql" | lshandout_ulink_table
676 echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">CSV</a>)</h2>"
677 printf ".mode csv\n.header ON\n" | query
678 echo '<pre class="list">'
679 echo "$sql" | query | sed 's/^"[0-9]* /"/'
680 echo "</pre>"
681 echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>"
682 else
683 contenttype "Application/CSV"
684 printf ".mode csv\n.header ON\n" | query >/dev/null
685 fn=report-count.csv
686 printf 'Content-Disposition: filename="%s"\n' "$fn"
687 outfile=$tmpd/out-$$.csv
688 echo "$sql" | query | sed 's/^"[0-9]* /"/' > $outfile
689 echo "Content-Length: " `cat $outfile | wc -c`; echo
691 cat $outfile
692 exit 0
693 fi
694 printf ".mode list\n.header OFF\n.separator |\n" | query
695 }
696 lshandoutsub() {
697 # $1=owner $2=rowid of blog &optional $3=temp_table name
698 qgname=`sqlquote "$1"`
699 if isgroup "$1"; then
700 sample="(select user from grp_mem where gname=$qgname)"
701 else
702 sample="(select distinct author as user from arts)"
703 echo "(集計は板への投稿者のみ)" | html p
704 fi
705 tmpname="${3:-handout_$2}"
706 sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS
707 with arts as (select id,author from article \
708 where blogid=(select id from blog where rowid=$2))\
709 select (select rowid from user where name=c0.user)||' '|| \
710 (select gecos from gecoses where name=c0.user) as 'メンバー',\
711 substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\
712 sum(case when c1.key is not null then 1 else 0 end)\
713 as '[$title] コメント記入',\
714 sum(case when c2.key is not null then 1 else 0 end)\
715 as '[$title] ファイルの提出'\
716 from $sample c0 \
717 left join (select id,author from arts) a\
718 on c0.user=a.author\
719 left join (select id,key from article_s where key='text') c1\
720 on a.id=c1.id left join (select id,key from article_m ) c2\
721 on c1.id=c2.id group by c0.user order by c0.user;\
722 \
723 SELECT * FROM $tmpname;"
724 # err ishandoutsub: sql="$sql"
725 echo "$sql" | lshandout_ulink_table
726 }
727 gethandout() {
728 # $1=rowid of blog
729 rid=`numericalize "$1"`
730 blog_writable $rid $user
731 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
732 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
733 contenttype; echo
734 echo "メンバー以外は利用できません。" | html p; return
735 fi
736 # Here, this blog is writable by $user
737 owner=`getvalbyid blog owner $1`
738 if [ x"$user" = x"$owner" ]; then
739 : OK
740 elif isgrpowner "$user" "$owner"; then
741 : OK
742 else
743 contenttype; echo
744 echo "板の所有者以外は利用できません。" | html p; return
745 fi
746 mode=`getvalbyid blog mode $1`
747 copy2csv=false
748 blogid=`getvalbyid blog id $1`
749 isgroup "$owner" && isgroup=true || isgroup=false
750 isgrpowner "$user" "$owner" && isgrpadmin=true || isgrpadmin=false
752 i=0
753 midfile=$tmpd/midfile
754 bd=$tmpd/archive.$$
755 mkdir $bd
756 case "$mode" in
757 *quiz*)
758 copy2csv=true ;;
759 *enquete*)
760 copy2csv=true
761 csvline=`getvalbyid blog heading $1 | grep "..*,." | head -1`
762 # Create CSV-base table for questionnaire
763 # If heading in blog_s has at least 1 CSV line,
764 # we take the line as column list.
765 # Otherwise we produce two column CSV as below:
766 # USER,ANSWER
767 query "DROP TABLE IF EXISTS tmp_q;"
768 if [ -n "$csvline" ]; then
769 query <<-EOF
770 CREATE TEMPORARY TABLE tmp_q("user", $csvline);
771 EOF
772 if [ $? != 0 ]; then
773 contenttype; echo
774 cat <<-EOF | html p; exit
775 掲示板のヘッダにあるCSV定義が不正でCSV出力できません。
776 $csvline
777 空白なしの項目名を半角カンマ区切りで1行で書いてください。
778 EOF
779 fi
780 else
781 query <<-EOF
782 CREATE TEMPORARY TABLE tmp_q(user text PRIMARY KEY, answer);
783 EOF
784 fi
785 esac
786 if $copy2csv; then
787 mkdir $bd/$rid
788 outcsv=$bd/$rid/migrate-$rid.csv
789 fullcsv=$bd/$rid/all-text-full-$rid.csv
790 sq "$db" <<-EOF | tr '|' ',' > $outcsv
791 SELECT author as "USER",
792 replace(val, x'0a', ',') as "${csvline:-ANSWER}"
793 FROM article a JOIN article_s s ON a.id=s.id
794 AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
795 AND s.key='text';
796 EOF
797 sq "$db" <<-EOF > $fullcsv
798 .mode csv
799 .head 1
800 SELECT author as "ユーザ",
801 (SELECT gecos FROM gecoses g WHERE author=g.name) as "表示名",
802 val as "テキスト"
803 FROM article a JOIN article_s s ON a.id=s.id
804 AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
805 AND s.key='text';
806 EOF
807 fi
808 query <<-EOF > $midfile # Using tempfile for quick db-unlock
809 SELECT a.rowid, a.id artid, a.author, hex(s.val)
810 FROM article a JOIN article_s s ON a.id=s.id
811 WHERE blogid=(SELECT id FROM blog WHERE rowid=$rid);
812 EOF
813 cat $midfile | while IFS='|' read rowid artid author text; do
814 isfilereadable $user article_s $rowid || continue
815 dir=`printf $bd/%d/%06d "$rid" "$rowid"`
816 mkdir -p $dir
817 echo "$author" > $dir/Author
818 echo "$text" | unhexize > $dir/Text
819 i=0
820 query "SELECT m.rowid, m.val FROM article_m m \
821 WHERE id='$artid' AND m.key IN ('image', 'document', 'binary');" \
822 | while IFS='|' read mrowid filename; do
823 i=$((i+1))
824 outfile=`printf "%s/%02d-%s" "$dir" $i "$filename"`
825 query "SELECT quote(bin) FROM article_m WHERE rowid=$mrowid;" \
826 | unhexize > $outfile
827 done
828 done
829 if [ ! -d $bd/$rid ]; then
830 contenttype; echo
831 echo "取得できるファイルがありませんでした。" | html p
832 return
833 fi
835 if $copy2csv; then
836 query <<-EOF > $bd/$rid/all-text-1stline-$rid.csv
837 .mode csv
838 .head 1
839 .import $outcsv tmp_q
840 SELECT * FROM tmp_q;
841 .mode list
842 .head 0
843 EOF
844 fi
845 err "BDLIST: `ls -l $bd`"
846 arcname=archive-$rid.tar.gz
847 ### outstdout=true
848 (cd $bd
849 # query() CANNOT BE used in this subshell
850 if [ "$outstdout" ]; then
851 cat <<-EOF
852 Content-type: application/x-gzip
853 Content-Disposition: filename="$arcname"
855 EOF
856 tar zcf - $rid
857 return
858 else
859 tar zcf .archive.tar.gz $rid && mv .archive.tar.gz "$arcname"
860 err Creating tar archive "`ls -l "$arcname"`"
861 fi
862 )
863 arcfile=$bd/$arcname
864 echo "Content-type: application/x-gzip"
865 echo "Content-Length: `cat $arcfile|wc -c`"
866 echo "Content-Disposition: filename=\"$arcname\""
867 echo
868 cat $arcfile
869 }
870 blogseen() { # $1 = blogid
871 blogid=${1%%[!0-9]*}
872 if [ -z "$blogid" ]; then
873 echo "Invalid blog id" | html p; exit
874 fi
875 blog_writable "$blogid" "$user"
876 rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member
877 if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then
878 echo "メンバー以外は利用できません。" | html p; return
879 fi
880 owner=`getvalbyid blog owner $rowid`
881 qowner=`sqlquotestr "$owner"`
882 grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"`
883 ge=`gecos "$owner" | htmlescape`
884 title=`getvalbyid blog title $rowid | htmlescape`
885 h1="アクセス時刻"
886 link2board="<a href=\"?replyblog+$rowid\">$title</a>"
887 link2group="<a href=\"?grp+$grprowid\">$ge</a>"
888 _m4 -D_TITLE_="$h1" $layout/html.m4.html
889 echo "$h1" | html h1
890 echo "[$link2board]@$link2group" | html h2
891 warn=' class="warn"'
892 cat <<-EOF
893 <table class="b">
894 <tr><th>メンバー</th><th>uname</th><th>最終閲覧時刻</th></tr>
895 EOF
896 query <<-EOF |
897 WITH grpmem as (
898 SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos
899 FROM grp_mem
900 WHERE gname=(SELECT val FROM blog_s
901 WHERE id=(select id from blog where rowid=$blogid)
902 AND key='owner')
903 ), acctime AS (
904 SELECT user, max(time) atime
905 FROM tblaccesses
906 WHERE tbl='blog' AND tblrowid=$blogid
907 GROUP BY user
908 )
909 SELECT g.user,
910 (SELECT rowid FROM user u WHERE u.name=g.user),
911 hex(gecos),
912 atime
913 FROM grpmem g LEFT JOIN acctime t
914 ON g.user = t.user
915 GROUP BY g.user
916 ORDER BY atime DESC;
917 EOF
918 while IFS='|' read u uid hexge time; do
919 td=${time:+"<td>"} # If the variable time is set, td=<td>
920 td=${td:-"<td$warn>"} # else td=<td class="warn">
921 cat <<-EOF
922 <tr>
923 <td><a href="?home+$uid">`echo "$hexge"|unhexize|htmlescape`</a></td>
924 <td>`echo ${u%%@*}|htmlescape`</td>
925 $td${time:----}</td></tr>
926 EOF
927 done
928 cat <<-EOF
929 </table>
930 <p><a href="?replyblog+$rowid">[$title]に戻る</a></p>
931 </html>
932 EOF
933 }
934 lsmyfile() { # $1(optional)=SortBy
935 case "$1" in
936 ""|CTIME-DESC)
937 by="CTIME" ord="DESC" ;;
938 CTIME*) by="CTIME" ;;
939 FILE*) by="FILE" ;;
940 OWNER*) by="OWNER" ;;
941 TITLE*) by="TITLE" ;;
942 esac
943 case "$1" in
944 *DESC) ord="DESC" ;;
945 esac
946 case "$ord" in
947 DESC) lkod="" jord="降順" ;;
948 *) lkod="-DESC" jord="昇順" ;;
949 esac
950 sql="select m.val||'/'||m.rowid FILE,
951 coalesce(
952 case when (select name from user where name=bs.owner)
953 is not null
954 then (select val from user_s where name=bs.owner
955 and key='gecos')
956 when (select gname from grp where gname=bs.owner)
957 is not null
958 then (select val from grp_s where gname=bs.owner
959 and key='gecos')
960 else
961 null
962 end,
963 bs.owner
964 ) OWNER,
965 a_s.val CTIME,
966 ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE
967 from (select rowid,id,val from article_m where id
968 in (select id from article where author='$user')
969 and type like 'file:%')
970 m left join article a on m.id=a.id
971 left join article_s a_s on a.id=a_s.id and a_s.key='ctime'
972 left join (select id,
973 max(case key when 'owner' then val end) as owner,
974 max(case key when 'title' then val end) as title
975 from blog_s group by id)
976 bs on a.blogid=bs.id
977 left join blog b on bs.id=b.id
978 where m.val is not null order by $by $ord;"
979 err lshandoutbyauthor: sql=`echo "$sql"`
980 title="個人提出ファイル"
981 _m4 -D_TITLE_=$title $layout/html.m4.html
982 hra="<a href=\"?lsmyfile+"
983 hrb="<a href=\"?showattc+article_m+"
984 hrc="<a href=\"?replyblog+"
985 (echo '<table class="b">'
986 echo "$sql"|sq -html -header $db ) \
987 | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \
988 -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \
989 -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \
990 | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \
991 -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html
992 echo '</table>'
993 }
994 searchart() {
995 kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC
996 bloglist=`getpar bloglist|sed 's/[^0-9,]//g'`
997 kwdgrp=""
998 authcond=""
999 if [ -z "$kwd" ]; then
1000 echo "検索語を指定してください" | html p; return
1001 fi
1002 if logstart "$searchlog"; then
1003 { echo "kwd=$kwd"
1004 test -n "$bloglist" && echo "bloglist=$bloglist"
1005 } >> $searchlog
1006 logend "$searchlog"
1007 fi
1008 if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
1009 # Like '#1234', assume as artID
1010 rowid=$((${kwd#\#} + 0)) # Force to be a number
1011 kc="ar.rowid = $rowid"
1012 else
1013 for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes
1014 ctime=""
1015 if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then
1016 # '@<2016-10-10' -> ctime < '2016-10-10'
1017 # '@>=2016-10-10' -> ctime >= '2016-10-10'
1018 # '@2016-10-10' -> ctime GLOB '@2016-10-10'
1019 k=${k#@}
1020 case "$k" in
1021 [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;;
1022 *) op='GLOB'; ctime="${k##*[><= ]}*" ;;
1023 esac
1024 kc=$kc${kc:+" AND "}"ctime $op '${ctime}'"
1025 # Not sure GROUP BY a.blogid is comfortable for searchers...?
1026 ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results
1027 elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
1028 ctime=`date +%F`
1029 elif n=`expr x"$k" : 'x@\([0-9]*\)days*'` >/dev/null >&2; then
1030 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
1031 elif [ x"$k" = x"@week" ]; then
1032 ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"`
1033 elif n=`expr x"$k" : 'x@\([0-9]*\)weeks*'` >/dev/null >&2; then
1034 n=$((n * 7))
1035 ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"`
1036 elif [ x"$k" = x"@month" ]; then
1037 ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"`
1038 elif n=`expr x"$k" : 'x@\([0-9]*\)months*'` >/dev/null >&2; then
1039 ctime=`query "SELECT datetime('now', 'localtime', '-$n month');"`
1040 elif [ x"$k" = x"@year" ]; then
1041 ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"`
1042 elif n=`expr x"$k" : 'x@\([0-9]*\)years*'` >/dev/null >&2; then
1043 ctime=`query "SELECT datetime('now', 'localtime', '-$n year');"`
1044 fi
1045 if [ -n "$ctime" ]; then
1046 kc=$kc${kc:+" AND "}"ctime > '${ctime}'"
1047 else
1048 e=""
1049 case "$k" in
1050 *${likeesc}*) e="" ;; # Giving up char-escaping
1051 *%*|*_*) k=`echo "$k"|sed "s/\([%_]\)/${likeesc}\1/g"`
1052 e=" ESCAPE '$likeesc'" ;;
1053 esac
1054 kc=$kc${kc:+" AND "}"content LIKE '%$k%'$e"
1055 fi
1056 done
1057 fi
1058 kwd=`echo "$kwd"|htmlescape`
1059 owner=`getpar owner`
1060 owner=${owner:-$1}
1061 msg=""
1062 if [ -n "$owner" ]; then
1063 cond="where key='owner' and val='$owner'"
1064 if isuser $owner; then
1065 msg="(`linkhome $owner` さんの記録から)"
1066 else
1067 linkhome $owner 1>&3
1068 msg="(`linkhome $owner` グループから)"
1069 fi
1070 elif { author=`getpar author`; test -n "$author"; }; then
1071 atptn=`sqlquotestr $author`
1072 #kc="$kc${kc:+ AND }author=$atptn"
1073 authcond="WHERE author=$atptn"
1074 if isuser $author; then
1075 msg="(`linkhome $author` さんの書き込みから)"
1076 fi
1077 fi
1078 if [ -n "$bloglist" ]; then
1079 blogcond="AND bl.rid IN ($bloglist)"
1080 fi
1082 sf=`search_form "$search_form_args" "$kwd" | sed '1d;$d'` # rm <div></div>
1083 echo "$sf" | sed -e "/POST SENTENCE/s/.*/__PS__/" -e "/EOF/q" \
1084 | _m4 -D__PS__="による検索結果$msg"
1085 echo "(上記入力窓で再検索すると下記の掲示板のみに絞って再検索します)" \
1086 | html p 'class="small"'
1087 # article_s: id=article-id, key='text', val='TEXT'
1088 # article: id=article-id, blogid=blogkd
1089 # blog: id=blog-id, author=LeaderAuthor
1090 # blog_s: id=blog-id, key='title', val='BLOG-TITLE'
1091 # WANT: blog-ROWid,article-id,val(TEXT)
1092 sql2="`sql4readableblogs` -- Extract user-readable blogs
1093 -- 0.3sec
1094 WITH artsm AS (
1095 SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
1096 FROM article a
1097 LEFT JOIN
1098 (SELECT ars.id, ctime, text, coalesce(files, '') files
1099 FROM (SELECT id,
1100 max(CASE key WHEN 'ctime' THEN val END) ctime,
1101 max(CASE key WHEN 'text' THEN val END) text
1102 FROM article_s
1103 GROUP BY id) ars
1104 LEFT JOIN
1105 (SELECT id, group_concat(val) files
1106 FROM article_m
1107 WHERE type LIKE 'file:%'
1108 GROUP BY id) arm
1109 ON ars.id=arm.id
1110 ) ar
1111 ON a.id=ar.id
1112 ), ar AS (
1113 SELECT a.rowid, a.blogid, a.id, a.author, ctime, content
1114 FROM article a JOIN artsm ON a.id=artsm.id
1115 $authcond
1116 ), bl AS (
1117 SELECT blg.rid, blg.*, blog_s.val TITLE
1118 FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
1119 )
1120 SELECT bl.rid||'#'||ar.id '',
1121 bl.title TITLE,
1122 (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR,
1123 substr(ctime, 0, 11) DATE,
1124 substr(content, 0, 78) TEXT
1125 FROM ar JOIN bl
1126 ON ar.blogid=bl.id
1127 WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) $blogcond
1128 ORDER by DATE DESC, TITLE, ctime;"
1129 sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
1130 href=\"?replyblog+\1\">VIEW</a></TD>,"
1131 # echo "$sql2" > tmp/sql.out
1132 result=$tmpd/result.$$
1133 cat<<EOF
1134 <table class="b searchart">
1135 `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result`
1136 </table>
1137 EOF
1138 if [ -s "$result" ]; then
1139 found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT
1140 one=${found%1}
1141 echo "$found match${one:+es} found"
1142 # <a href="?replyblog+39#12345">VIEW</a>
1143 # -> 39,49,55, -> 39,49,55
1144 # -> <input type="hidden" name="bloglist" value="39,49,55">
1145 sed -n "/.*href=.*replyblog\+\([0-9][0-9]*\).*/s//\1/p" "$result" \
1146 | sort | uniq | tr '\n' ',' \
1147 | sed -e 's/,$//' \
1148 -e 's/^/<input type="hidden" name="bloglist" value="/' \
1149 -e 's/$/">/'
1150 else
1151 echo orz...
1152 fi
1153 echo "$sf" | sed "1,/-- EOF/d" # Close <form>
1154 }
1155 listblog() (
1156 # $1={user,group}
1157 qow=`sqlquote $1`
1158 cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc"
1159 cgi_form searchart<<EOF
1160 <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label>
1161 `cgi_hidden owner $user`
1162 EOF
1163 DT_CHLD=article:blogid DT_QOWNER=$qow \
1164 dumptable html blog 'ctime title heading' "$cond"
1165 )
1167 blog_addentry() {
1168 # $1=GRPname(if it is a group)
1169 grprowid=`numericalize $1`
1170 rowid=`getpar rowid`
1171 ## err blog_addentry0: rowid=$rowid
1172 if [ -n "$grprowid" ]; then
1173 owner=`getgroupbyid $grprowid`
1174 else
1175 owner=`getpar owner`
1176 fi
1177 err blog-add: \$1=$grprowid rowid=$rowid owner=$owner
1178 if isgroup "$owner"; then
1179 groupmode=1 listing=$owner guide="[${owner}]" GF_OWNER=$owner
1180 else
1181 usermode=1 listing=$user guide="[個人]"
1182 fi
1184 if [ -n "`getpar title`" ]; then
1185 if [ "$usermode" ]; then
1186 err usermode: user=$user owner=$owner
1187 if [ x"$user" != x"$owner" ]; then
1188 echo "他人の日記は書けません" | html p
1189 return 2
1190 fi
1191 elif [ "$groupmode" ]; then # if write to group log
1192 grp=$owner #\`getpar grp\`
1193 err ismember: $user $grp
1194 if ! ismember "$user" "$grp"; then
1195 echo "(話題作成はこのグループに加入してから)" | html p
1196 return 3
1197 fi
1198 fi
1199 par2table $formdir/blog.def
1200 serial=`getpar serial`
1201 ## err SERIAL: $serial ROWID=$rowid listing=$listing
1202 id=""
1203 if [ -n "$rowid" ]; then
1204 # Here, id becomes NULL when removal of entries at par2table
1205 id=`query "select rowid from blog where rowid=$rowid;"`
1206 elif [ -n "$serial" ]; then
1207 # If new blog leader created, traverse to its head.
1208 id=`query "select rowid from blog where id='$serial';"`
1209 ## err new-Leader: "select rowid from blog where id='$serial';" id=$id
1210 fi
1211 if [ -n "$id" ]; then
1212 ## If new aritcle is entered, JUMP to blog_reply
1213 blog_reply $id
1214 return
1215 fi
1216 fi
1217 echo "${guide}新規話題作成" > $tmpd/title.$$
1218 listblog $listing > $tmpd/listblog.$$
1219 genform $formdir/blog.def \
1220 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1221 -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \
1222 -D_DUMPHEAD_="これまでの蓄積" \
1223 -D_FORM_="syscmd(\`cat')" \
1224 -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \
1225 $layout/html.m4.html \
1226 $layout/form+dump-whead.m4.html
1227 }
1229 blog_reply() { # Posting to blog article
1230 rowid=`numericalize $1` # Ensure (already purified in s4.cgi)
1232 if [ -z "$rowid" ]; then
1233 echo "表示する日記番号が未指定です。" | html p
1234 return
1235 fi
1236 title=`getvalbyid blog title $rowid`
1237 owner=`getvalbyid blog owner $rowid`
1238 qowner=`sqlquotestr "$owner"`
1239 if [ -z "$title" ]; then
1240 echo "日記番号指定が無効です。" | html p
1241 return
1242 fi
1243 blog_writable $rowid $user; rc=$?
1244 if [ $rc = 0 ]; then
1245 iswritable=true
1246 else
1247 iswritable=false
1248 if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then
1249 isfrozen=true
1250 frozen_class='frozen"'
1251 frozen_flag=$FROZEN_TAG
1252 fi
1253 fi
1254 if isuser "$owner"; then
1255 subtitle="`gecos $owner` さんの話題"
1256 else
1257 grprowid=`query "select rowid from grp where gname=$qowner;"`
1258 subtitle="グループ
1259 <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$owner</a> での話題
1260 `query \"SELECT printf('(チーム:%s)', val)\
1261 FROM blog_s
1262 WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
1263 AND key='team';
1264 \"|htmlescape`"
1265 memclass=`grp_getbodyclass "$owner"`
1266 fi
1268 text=`getpar text`
1269 if [ -n "$text" ]; then
1270 if $iswritable; then
1271 par2table $formdir/article.def
1272 st=$?
1273 case $st in
1274 0|4)
1275 [ "$st" = "4" ] && act="書込削除"
1276 blog_notify_reply $rowid $user "$text" $act
1277 if [ -n "$grprowid" ]; then
1278 qgrp=$(sqlquote "$owner")
1279 dbsetbyid grp $owner wtime "`date '+%F %T'`"
1280 else
1281 dbsetbyid user "$user" wtime "`date '+%F %T'`"
1282 fi
1283 ;;
1284 esac
1285 else
1286 if $isfrozen; then
1287 title="$title(凍結板につき書き込み不可)"
1288 else
1289 title="$title(加入してないので書き込み不可)"
1290 fi
1291 fi
1292 fi
1293 def=$formdir/article.def
1294 echo "$title" > $tmpd/title.$$
1295 echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$
1296 ${BLOG_SHOW:-blog_showentry} blog $rowid \
1297 | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \
1298 -D_BODYCLASS_=general"${memclass:+ $memclass}" \
1299 -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \
1300 -D_FORM_='' \
1301 -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \
1302 $layout/html.m4.html $layout/form+dump-whead.m4.html
1303 }
1305 blog_reply_article() { # Direct link to article in some blog
1306 arid=${1:-0} # Already sanitized to digits
1307 brid=`query "SELECT rowid FROM blog WHERE \
1308 id=(SELECT blogid FROM article WHERE rowid=$arid);"`
1309 if [ -n "$brid" ]; then
1310 newurl="?replyblog+$brid#$arid"
1311 echo "Refresh: 0; $newurl"; echo
1312 exit 0
1313 else
1314 contenttype; echo
1315 echo "無効な記事番号です." | html p
1316 fi
1317 }