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