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