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