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