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