yuuji@0: # yuuji@17: type cgiinit >/dev/null 2>&1 || . ./s4-funcs.sh yuuji@0: yuuji@397: # Global error flags yuuji@397: BLOG_NOTMEM=1 yuuji@397: BLOG_FROZEN=2 yuuji@399: FROZEN_TAG='[凍結]' yuuji@397: yuuji@0: blog_genform() { yuuji@0: # yuuji@0: t=$1 yuuji@0: } yuuji@0: yuuji@0: blog_writable() ( yuuji@0: # $1=articleid $2=user yuuji@397: # Return: $?=0 - Writable yuuji@397: # =1 - NOT Writable because user is not a member yuuji@397: # =2 - NOT Writable because blog is frozen yuuji@0: blogowner=`getvalbyid blog owner "$1"` yuuji@397: state=`getvalbyid blog state "$1"` yuuji@397: rc=0 yuuji@397: [ x"$blogowner" = x"$2" ] || isuser "$blogowner" || ismember "$2" "$blogowner" || rc=$((rc+$BLOG_NOTMEM)) yuuji@397: [ "$state" = "frozen" ] && rc=$((rc+$BLOG_FROZEN)) yuuji@397: return $rc yuuji@0: ) yuuji@138: blog_readable() { yuuji@138: # $1=articleid $2=user yuuji@768: mode=`getgroupattr "$grp" regmode` yuuji@138: } yuuji@587: blog_getteam() { yuuji@587: # $1=rowid of blog yuuji@587: blogid="${1%%[!A-Z0-9a-z_]*}" yuuji@587: # team cannot get `getvalbyid blog team "$blogid"` because it's not yuuji@587: # defined in blog.def. Yes, it is Illegal USE!! yuuji@587: query "SELECT val FROM blog_s yuuji@587: WHERE id=(SELECT id FROM blog WHERE rowid=$blogid) yuuji@587: AND key='team';" yuuji@587: } yuuji@50: blog_notify_reply() ( yuuji@222: # $1=blogid $2=ReplyingUser $3=WrittenText $4(optional)=Action yuuji@411: blogid="${1%%[!A-Z0-9a-z_]*}" yuuji@411: blogowner=`getvalbyid blog owner "$blogid"` yuuji@411: blogtitle=`getvalbyid blog title "$blogid"` yuuji@411: blogurl="$urlbase?replyblog+$blogid" yuuji@80: action=${4:-書き込み} yuuji@411: mode=`getvalbyid blog notify "$blogid"` yuuji@473: isgroup "$blogowner" && _isgroup=true || _isgroup=false yuuji@238: ### EXCEPT=`sqlquote "$user"` ## User should receive to feal some annoyance yuuji@50: case $mode in yuuji@48: admin) yuuji@473: if $_isgroup; then yuuji@768: emails=`getgroupadminmails "$blogowner"` yuuji@48: else yuuji@768: emails=`collectemail "$blogowner"` yuuji@48: fi yuuji@170: notifyto=`getpar notifyto` yuuji@191: if [ -n "$notifyto" ]; then yuuji@170: emails=$emails" `email4groupbyuid \"$blogowner\" $notifyto`" yuuji@170: fi yuuji@48: ;; yuuji@849: no|"") emails="" ;; # 2020-0630 Omit email when heavy load...(XXX) yuuji@587: *) team=`blog_getteam "$blogid"` yuuji@587: # team cannot get by `getvalbyid blog team "$blogid"` yuuji@768: emails=`TEAM=$team collectemail "$blogowner"` ;; yuuji@48: esac yuuji@410: ## 2017-0210 Respond to the direct reply mark such as: >#1234 yuuji@413: replymark=`echo "$3"|nkf -w -Z0|grep '^ *>#'` yuuji@410: authgecos=`gecos $2` yuuji@878: ## 2020-1209 If the first line begins with '## ', use it as Subject yuuji@878: firstline=`echo "$3"|head -1|nkf -w -Z0` yuuji@878: if [ -z "$4" ]; then yuuji@878: if [ -n "$replymark" ]; then yuuji@878: # If the action is new subscription($4="") and has ">#123" marks... yuuji@878: ids=`echo "$replymark"|sed 's/[^#0-9]*#\([0-9]*\)[^#0-9]*/\1 /g'` yuuji@878: ids=`echo $ids|tr -dc '[0-9 ]'|tr ' ' ','` yuuji@878: # -> 123,345,347 yuuji@878: unames=`query "SELECT distinct author FROM article \ yuuji@410: WHERE rowid in ($ids)\ yuuji@411: AND blogid=(SELECT id FROM blog WHERE rowid=$blogid);"` yuuji@878: if [ -n "$unames" ]; then yuuji@878: e4g=$(if $_isgroup; then yuuji@878: email4group "$blogowner" $unames yuuji@878: else yuuji@878: for u in $unames; do yuuji@878: collectemail $u yuuji@878: done yuuji@878: fi) yuuji@878: emails=$emails" $e4g" yuuji@878: for e in $unames; do yuuji@878: g=`gecos $e` yuuji@878: whom=$whom"${whom:+,}${g:-$e}さん" yuuji@878: done yuuji@878: action="${whom}への返信" yuuji@878: fi yuuji@878: elif echo "$firstline" | grep -q "^## "; then yuuji@878: subject=${firstline#\#\# } yuuji@411: fi yuuji@430: else yuuji@878: # This else block is not symmetry, check later(2020-1209) yuuji@430: [ x"$2" = x"$blogowner" ] && return # If author=blogowner, unnecessary yuuji@410: fi yuuji@410: test -z "$emails" && return yuuji@878: sj=${subject:-${action}通知} yuuji@768: err notify: user=$user Admins=`getgroupadmins "$blogowner"` Mode=$mode Emails="[$emails]" yuuji@749: quotedowner=`echo $blogowner | nkf -jM | tr -d '\n"'` yuuji@615: MAIL_FROM=$noreply_from \ yuuji@749: SMAIL_TO="\"$quotedowner\" readers <$noreply>" \ yuuji@878: smail "$emails" "$sj $urlbase"< yuuji@154: yuuji@154: EOF yuuji@154: yuuji@702: href=" 編集 " yuuji@397: if $ismem; then yuuji@592: case $blog_mode in yuuji@593: *report*|*quiz*|*enquete*) yuuji@702: href2=" 提出状況 " yuuji@774: case "$isgrpadmin$blog_mode" in yuuji@774: false*closed*|false*quiz|false*enquete*) ;; yuuji@774: *) yuuji@866: href3="(ファイル取得[記事順|著者順])" yuuji@774: ;; yuuji@774: esac yuuji@369: ;; yuuji@369: esac yuuji@154: fi yuuji@702: href4=" 末尾へ" yuuji@465: $isgrpadmin && yuuji@702: href5=" 読刻" yuuji@638: quizmodefile=$tmpd/quiz; rm -f "$quizmodefile" # XXX: Global state yuuji@640: midfile=$tmpd/midfile yuuji@154: yuuji@640: query<<-EOF > $midfile yuuji@293: SELECT coalesce((SELECT "yes" FROM blog yuuji@585: -- GrpAdmin CAN EDIT heading since 2019-08-15 yuuji@585: WHERE '$isgrpadmin' = 'true' yuuji@585: OR (rowid=$rowid AND author='$user')), yuuji@293: ''), yuuji@293: max(CASE key WHEN 'ctime' THEN val END) ctime, yuuji@293: max(CASE key WHEN 'heading' THEN hex(val) END) heading, yuuji@293: CASE (SELECT val FROM $ts WHERE key="mode" AND id="$id") yuuji@293: WHEN 'report-closed' THEN 'レポート提出用(closed)' yuuji@293: WHEN 'report-open' THEN 'レポート提出用(open)' yuuji@442: WHEN 'quiz' THEN 'クイズ' yuuji@592: WHEN 'enquete' THEN '集計' yuuji@293: ELSE '' yuuji@293: END yuuji@293: FROM $ts WHERE id='$id' GROUP BY id; yuuji@293: EOF yuuji@640: if test -s $midfile && IFS='|' read edit ctime hexhead blogtype < $midfile yuuji@640: then yuuji@293: cat<<-EOF yuuji@648: yuuji@397: yuuji@632: yuuji@293:
${edit:+$href }$ctime $blogtype $href2${edit:+$href3} $href4 $href5
`echo "$hexhead"|unhexize|htmlescape|hreflink|minitbl`
yuuji@293: EOF yuuji@446: case "$blogtype" in yuuji@594: "クイズ"|"XXXX集計") yuuji@592: echo "${blogtype}モードは本人と管理者の書き込みのみが表示されます。" yuuji@446: ;; yuuji@446: esac | html p 'class="warn"' yuuji@801: yuuji@594: if [ x"$blogtype" = x"クイズ" -o x"$blogtype" = x"XXXX集計" ]; then yuuji@443: if $isgroup; then yuuji@640: # Failsafe to query timeout yuuji@640: qgrp=`sqlquote "$blogowner"` yuuji@640: cat<<-EOF > $quizmodefile yuuji@443: AND (author IN (SELECT user FROM grp_adm WHERE gname=$qgrp) yuuji@443: OR yuuji@443: author='$user') yuuji@443: EOF yuuji@720: if $isgrpadmin; then # yuuji@640: : > $quizmodefile yuuji@443: fi yuuji@640: else # if user-blog yuuji@446: if [ x"$user" != x"$blogowner" ]; then yuuji@446: cat<<-EOF > $quizmodefile yuuji@446: AND author IN ('$blogowner', '$user') yuuji@446: EOF yuuji@446: fi yuuji@443: fi yuuji@443: fi yuuji@640: else # Cannot read SQL output yuuji@640: echo "時間をおいて繋いでください(Please visit later)." | html p yuuji@640: return yuuji@640: fi yuuji@801: ## Parse control sequence yuuji@801: nlimit=$listartlimit yuuji@801: case "$control" in yuuji@801: n:[Aa][Ll][Ll]) yuuji@801: unset nlimit ;; yuuji@801: n:*) yuuji@801: nlimit=${control##*:} yuuji@801: nlimit=${nlimit%%[!A-Z0-9a-z_]*} yuuji@801: ;; yuuji@801: esac yuuji@154: lkhome=" $midfile yuuji@154: WITH a_s AS ( yuuji@154: SELECT id, yuuji@154: max(CASE key WHEN 'ctime' THEN val END) TIME, yuuji@154: max(CASE key WHEN 'text' THEN val END) TEXT yuuji@154: FROM article_s yuuji@154: GROUP by id yuuji@154: ) yuuji@154: SELECT a.id, yuuji@154: CASE author yuuji@154: WHEN '$user' THEN a.rowid||'+'||$rowid yuuji@154: ELSE '' yuuji@154: END edit, yuuji@170: CASE -- 「通知送信」ボタンの有無 yuuji@170: WHEN '$notifyto' = '' THEN '' -- 不要モードならなし yuuji@170: WHEN '$user' = author THEN '' -- 筆者自身ならなし yuuji@170: ELSE "yes" yuuji@170: END notify, yuuji@154: (SELECT rowid FROM user WHERE name=author) user_rid, yuuji@570: author, yuuji@154: coalesce((SELECT val FROM user_s yuuji@154: WHERE name=author AND key='gecos'), yuuji@154: author) uname, yuuji@322: (SELECT val FROM user_s WHERE name=author AND key='$iconcachekey') yuuji@322: icon, yuuji@183: a.rowid, yuuji@154: s.TIME, yuuji@571: CASE WHEN s.TIME < '2019-05' yuuji@572: THEN printf('平成%d年%d月%d日%s', yuuji@571: substr(s.TIME, 1, 4)-1988, yuuji@571: substr(s.TIME, 6, 2), yuuji@572: substr(s.TIME, 9, 2), yuuji@572: substr(s.TIME, 12) yuuji@572: ) yuuji@571: WHEN s.TIME < '2020' yuuji@574: THEN printf('令和元年%d月%d日%s', yuuji@571: substr(s.TIME, 6, 2), yuuji@572: substr(s.TIME, 9, 2), yuuji@572: substr(s.TIME, 12)) yuuji@571: WHEN s.TIME < '2050' yuuji@574: THEN printf('令和%d年%d月%d日%s', yuuji@571: substr(s.TIME, 1, 4)-2018, yuuji@571: substr(s.TIME, 6, 2), yuuji@572: substr(s.TIME, 9, 2), yuuji@572: substr(s.TIME, 12)) yuuji@571: ELSE s.TIME yuuji@571: END reki, yuuji@286: CASE WHEN s.TIME > '$atime' THEN 'new' ELSE '' END newer, yuuji@154: hex(s.TEXT), yuuji@695: yuuji@695: $F_UNREADABLE cannotread, yuuji@695: yuuji@154: (SELECT group_concat(rowid||':'||length(bin)||':'||hex(val), ' ') yuuji@154: FROM article_m yuuji@154: WHERE id=a.id AND key='image') imxgids yuuji@443: FROM (select rowid,id,author from article yuuji@443: where blogid in yuuji@443: (select id from blog where rowid=$rowid) yuuji@443: $cond_qz) a yuuji@154: LEFT JOIN yuuji@154: a_s s yuuji@154: ON a.id=s.id; yuuji@154: EOF yuuji@643: if [ $? -ne 0 -a ! -s $midfile ]; then yuuji@642: echo "時間をおいてください(Visit later please)." | html p yuuji@642: return yuuji@642: fi yuuji@803: echo '' yuuji@807: # If, nLimit = 50 yuuji@807: # show article:1, hide(2, 3), show(4, ...) yuuji@807: # Therefore hide 2 or more article when narts>53 yuuji@803: narts=`wc -l < $midfile` yuuji@807: if [ -n "$nlimit" -a "$narts" -gt "$((nlimit+2))" ]; then yuuji@803: newtop=`cat -n $midfile | grep "|new|" | head -1 | cut -f1` yuuji@803: if [ -n "$newtop" ]; then yuuji@803: afternew=$((narts-newtop+1)) yuuji@807: [ $afternew -gt $((nlimit+2)) ] && nlimit=$((afternew+0)) yuuji@803: err Newtop=$newtop lines=$narts afternew=$afternew nlim=$nilmit yuuji@803: fi yuuji@804: fi yuuji@807: if [ $nlimit -lt $((narts-2)) ]; then yuuji@807: n=0 yuuji@807: omitline=$td/omitline yuuji@807: #CAT="tail -n $nlimit" yuuji@807: CAT=cat yuuji@803: limitedmsg="※最新${nlimit}件のみの表示※" yuuji@801: showalllink="全件表示" yuuji@807: cat<<-EOF > $omitline yuuji@807: yuuji@807: yuuji@807: yuuji@803: EOF yuuji@801: else yuuji@801: CAT=cat yuuji@801: fi yuuji@801: # Start blog_replies table yuuji@801: $CAT $midfile | yuuji@571: while IFS='|' read id edit notify uid author uname icon aid \ yuuji@571: tm reki new hte fa imgids yuuji@557: do yuuji@807: if [ -n "$omitline" ]; then yuuji@807: n=$((n+1)) yuuji@807: if [ $n -eq 1 ]; then yuuji@807: : yuuji@807: elif [ $n -eq 2 ]; then yuuji@807: cat $omitline yuuji@807: continue yuuji@807: elif [ $n -lt $((narts-nlimit+1)) ]; then yuuji@807: continue yuuji@807: fi yuuji@807: fi yuuji@629: mf2=$tmpd/midfile2 yuuji@407: cachefile="$td/$id.row.html" yuuji@407: stampfile="$td/$id.row.stamp" yuuji@408: editlink="${edit:+編集 }" yuuji@409: nt="" yuuji@559: # fa is file accessibility flag # err "----r=$aid fa=[$fa]----" yuuji@406: yuuji@406: # First, check the availability of user-icon. yuuji@406: # If not existent, clear and reset row cache by rm $stampfile yuuji@407: if [ ! -s "$icon" ]; then yuuji@407: rm -f "$stampfile"; unset stampfile yuuji@406: fi yuuji@403: if test -s "$stampfile" && yuuji@403: test -s "$cachefile" && yuuji@403: { ts=`cat "$stampfile"`; test -n "$ts"; } && yuuji@707: /bin/test "$ts" '>' "$tm" && # Cache timestamp is newer yuuji@422: test "$stampfile" -nt "$icon"; then # UserIcon is older yuuji@422: : Nothing to do yuuji@403: else yuuji@403: { ######## New ROW creation begins here ######## >$cachefile yuuji@641: cachestamp=$tmpd/cache.$$.stamp yuuji@641: touch $cachestamp yuuji@403: tdcls="__NEWCLS__repatt" yuuji@407: if [ -s "$icon" ]; then yuuji@407: icfn=`echo "$icon"|htmlescape` yuuji@570: picon="

" yuuji@407: else yuuji@407: echo "DELETE FROM user_s WHERE key='$iconcachekey' AND yuuji@407: val=`sqlquotestr \"$icon\"`;" >> $iconcleaner yuuji@407: picon="" yuuji@407: fi yuuji@406: yuuji@403: cat< yuuji@420: yuuji@154: EOF yuuji@403: echo -n "" yuuji@641: } > "$cachefile.$$" ######## New ROW Creation Ends here ######## yuuji@641: # Care about race condition yuuji@800: if [ -z "$hte" -a -s $cachefile -a $cachefile -nt $cachestamp ]; then yuuji@641: # If other process have created cache, give up to serve our file yuuji@641: rm -f $cachefile.$$ yuuji@641: else yuuji@641: mv -f $cachefile.$$ $cachefile yuuji@641: fi yuuji@406: test -n "$stampfile" && date "+%F %T" > $stampfile yuuji@403: fi yuuji@403: # Printing a cached row yuuji@409: sed -e "/^
:
$limitedmsg
($((narts-$nlimit-1))件省略)
:
$showalllink${picon}__EDIT__#$aid yuuji@749: `echo $uname|htmlescape` yuuji@571: ${reki:-$tm} yuuji@409: <__NOTIFY__>" yuuji@403: echo "$hte"|unhexize|htmlescape|hreflink|minitbl yuuji@403: usecache='' tsfile=$td/$id.stamp yuuji@403: for i in $imgids; do yuuji@403: mrid=${i%%:*}; i=${i#*:}; sz=`size_h ${i%%:*}` yuuji@403: fn=`echo "${i#*:}"|unhexize` yuuji@403: fnb=$fn"(${sz})" yuuji@263: case "$fn" in yuuji@407: *.[Pp][Nn][Gg]|*.[Jj][Pp][Gg]|*.[Jj][Pp][Ee][Gg]|*.[GgTt][Ii][Ff]) yuuji@403: # fmt=${fn##*.} # convert - jpg:- is slow...why yuuji@403: case "$fn" in yuuji@403: *.[Pp][Nn][Gg]) fmt=png ;; yuuji@403: *.[Gg][Ii][Ff]) fmt=gif ;; yuuji@403: *) fmt=jpeg ;; yuuji@403: esac yuuji@403: outfile=$td/$mrid-${fn%.*}.$fmt yuuji@403: #err fn=$fn outfile=$outfile yuuji@403: #err "usecache=$usecache `ls -l $outfile`" yuuji@403: #err tm=$tm yuuji@403: #err tsfile=$tsfile=`cat $tsfile` yuuji@403: if [ -s "$outfile" ] && # $outfile should be > 0 yuuji@403: { [ "$usecache" ] || # And usecache flag is true, or... yuuji@403: { [ -s "$tsfile" ] && [ x"`cat $tsfile`" = x"$tm" ] yuuji@403: };}; then yuuji@403: usecache=1 # Set usecache flag on yuuji@403: cat<<-EOF yuuji@264: yuuji@264: $fnb yuuji@264: EOF yuuji@404: # !!NOTE!! Create row stamp ONLY WHEN imgcache is active yuuji@403: else yuuji@403: query "SELECT hex(bin) FROM article_m WHERE rowid=$mrid;" \ yuuji@629: > $mf2 # Stop query here 2020-04-23 yuuji@641: if cat $mf2 | unhexize \ yuuji@403: | convert -define ${fmt}:size=100x100 -resize 100x100'>' \ yuuji@641: - ${fmt}:- > $outfile yuuji@641: then yuuji@641: cat "$outfile" \ yuuji@403: | hexize \ yuuji@403: | sed -e 's/\(..\)/%\1/g' \ yuuji@403: -e "s|^|$fnb|" yuuji@641: unset stampfile # img data stream is not suitable to cache yuuji@641: echo $tm > $tsfile yuuji@641: else # Failed to convert yuuji@641: rm -f $outfile yuuji@641: echo "$fnb" yuuji@641: fi yuuji@403: fi yuuji@403: ;; yuuji@403: *) yuuji@557: echo "<__UNREADABLE__>$fnb" yuuji@403: ;; yuuji@403: esac yuuji@403: done yuuji@403: echo "
/s,,${notify:+$nt}," \ yuuji@559: -e "/<__UNREADABLE__>/s,,${fa:+$cannotread}," \ yuuji@408: $cachefile yuuji@263: done yuuji@452: yuuji@843: help="=== コメントに使用できる特殊記法(記号は全て半角) === yuuji@452: 行頭に href=URL でURLへのリンク yuuji@452: 行頭に iframe=URL でURL先を開く iframe yuuji@843: 行頭「* 」で箇条書、次の行頭空白で継続、行頭詰めると箇条書終わり yuuji@843: 行頭「1. 」で番号付、2行目以降も「1. 」で勝手に番号増える、行頭詰めで終わり yuuji@452: [[#記事番号]] でs4内の記事番号に飛ぶリンク yuuji@479: [[#検索キーワード]] でs4内の記事検索(記号はいくつか使えない) yuuji@495: [[URL]] でURLへのリンク、 [[URL|文字列]]でアンカー文字列指定 yuuji@495: {{画像URL}} でインライン画像、 {{画像URL|幅}} でピクセル幅指定 yuuji@495: {{{URL}}} でURL先を開く iframe、 {{{URL|高さ}}} ピクセル高さ指定 yuuji@495: 行頭: ## 大見出し, ### 中見出し, #### 小見出し yuuji@495: 行末の2連続スペースで強制改行(
) yuuji@487: |*見出し列|列2|列3… と行頭から始まる縦棒区切り行を続けて表 yuuji@503: ' *語群* ' で強調(両側の空白必要、** でもっと強調。*の代わりに _ でも可) yuuji@503: - [ ] と - [x] でチェックボックス" yuuji@452: touchhelp="${touchpanel:+

$help

}" yuuji@513: filehelp="《添付の注意》 yuuji@513: $file_accept_help" yuuji@878: ntmode="通知モード=$blog_notify${blog_team:+ (team=$blog_team)} yuuji@878: 記事の1行目を「## 」(半角シャープシャープ空白=大見出し) yuuji@884: にするとそれより後ろの部分がSubject(件名)になります。 yuuji@878: If the first line begins with "## ", sent it as Subject of email." yuuji@837: textform=$(cat<<-EOF yuuji@837:
yuuji@341:
yuuji@193: yuuji@837: yuuji@837: yuuji@154:
$touchhelp
添付ファイル(${filesize_max_MB}以下): yuuji@837: `cgi_file image "" "$file_accept title=\"$filehelp\" multiple"` yuuji@837:
yuuji@837: yuuji@193:
yuuji@837: EOF yuuji@837: ) yuuji@219: cat<<-EOF yuuji@239:
yuuji@219: yuuji@219: EOF yuuji@219: $iswritable && cat<<-EOF yuuji@219:
yuuji@239: yuuji@268: yuuji@239: yuuji@239: $textform yuuji@239:
yuuji@239: yuuji@239: EOF yuuji@333: # Clean up orphaned icon cache yuuji@406: [ -s $iconcleaner ] && query ".read '$iconcleaner'" yuuji@154: # Record access log yuuji@154: acclog blog $rowid yuuji@154: } yuuji@154: yuuji@0: lshandout() { yuuji@749: # $1=rowid of blog (numericalized in s4.cgi) yuuji@401: blog_writable $1 $user yuuji@461: rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member yuuji@401: if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then yuuji@64: echo "メンバー以外は利用できません。" | html p; return yuuji@64: fi yuuji@0: time=`getvalbyid blog ctime $1|colrm 11` yuuji@0: owner=`getvalbyid blog owner $1` yuuji@0: title=`getvalbyid blog title $1` yuuji@749: ge=`gecos "$owner"` yuuji@749: htmlowner=`echo ${ge:-$owner}|htmlescape` yuuji@369: fh=$tmpd/formhead yuuji@749: echo "$time [$title]@$htmlowner" > $fh yuuji@749: lshandoutsub "$owner" "$@" \ yuuji@189: |_m4 -D_TITLE_="提出状況" \ yuuji@838: -D_H1_="提出状況" \ yuuji@369: -D_FORMHEAD_="syscmd(cat $fh)" \ yuuji@189: -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \ yuuji@189: $layout/html.m4.html $layout/form+dump-whead.m4.html yuuji@369: gn=`echo $owner|htmlescape` yuuji@369: echo "

グループ $gn すべてのレポート板集計

" yuuji@369: } yuuji@369: gethandoutcsv() { yuuji@369: # contenttype; echo yuuji@369: CATCSV=1 lshandoutall "$1" yuuji@369: } yuuji@373: gethandoutcsv2() { yuuji@373: # contenttype; echo yuuji@557: SQL=$(cat<<-EOF yuuji@373: WITH this_blog_articles AS ( yuuji@373: SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime yuuji@373: FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid yuuji@373: ), text_or_file AS ( yuuji@373: SELECT bid, author, title, ctime, 'text' shu, count(val) cnt yuuji@373: FROM this_blog_articles tba, article_s s yuuji@373: ON tba.aid=s.id yuuji@373: WHERE key='text' yuuji@373: GROUP by bid, author yuuji@373: UNION yuuji@373: SELECT bid, author, title, ctime, 'file' shu, count(val) cnt yuuji@373: FROM this_blog_articles tba, article_m m yuuji@373: ON tba.aid=m.id yuuji@373: WHERE key='image' yuuji@373: GROUP by bid, author yuuji@373: ), count_list AS ( yuuji@373: SELECT author, yuuji@373: substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit, yuuji@373: cnt yuuji@373: FROM text_or_file yuuji@373: ) yuuji@373: SELECT gecos "名前", yuuji@373: substr(author, 1, instr(author, '@')-1) "uname", yuuji@373: unit, yuuji@374: cnt "post" yuuji@373: FROM count_list cl JOIN gecoses g ON cl.author=g.name; yuuji@373: EOF yuuji@557: ) gethandoutcsv "$1" yuuji@373: } yuuji@369: lshandout_ulink_table() { yuuji@369: # NO Args. Read stdin as SQL yuuji@369: echo '' yuuji@369: hrb=",\1$hrb\2\">\3," -e 's,,,' yuuji@369: echo '
\)\([^ ]*\) \(.*\)00
' yuuji@369: printf ".mode list\n.header OFF\n" | query yuuji@369: } yuuji@369: lshandoutall() { yuuji@369: # $1=rowid of blog yuuji@402: blog_writable $1 $user yuuji@461: rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member yuuji@402: if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then yuuji@369: echo "メンバー以外は利用できません。" | html p; return yuuji@369: fi yuuji@369: rowid=$(($1 + 0)) yuuji@369: owner=`getvalbyid blog owner $1` yuuji@369: qowner=`sqlquotestr "$owner"` yuuji@369: yuuji@369: query<<-EOF yuuji@369: CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS yuuji@369: WITH blog_owner_mode AS ( yuuji@369: SELECT id, yuuji@369: blog.rowid brid, yuuji@369: max(CASE key WHEN 'owner' THEN val END) owner, yuuji@369: max(CASE key WHEN 'mode' THEN val END) mode, yuuji@373: max(CASE key WHEN 'title' THEN val END) title, yuuji@373: max(CASE key WHEN 'ctime' THEN val END) ctime yuuji@369: FROM blog NATURAL JOIN blog_s yuuji@369: GROUP BY id yuuji@369: ) yuuji@373: SELECT id, brid, title, ctime FROM blog_owner_mode yuuji@493: /* WHERE owner=$qowner AND mode LIKE '%report%'; */ yuuji@493: WHERE owner=$qowner yuuji@493: AND yuuji@592: (mode LIKE '%report%' OR mode LIKE '%quiz%' yuuji@592: OR mode LIKE '%enquete%'); yuuji@369: /* ↑これでレポート形式の blogid 一覧を得る */ yuuji@369: EOF yuuji@369: if [ -z "$CATCSV" ]; then yuuji@369: _m4 -D_TITLE_="提出状況" $layout/html.m4.html yuuji@369: ge=`gecos "$owner"` yuuji@369: tbls="" yuuji@369: grptxt=`echo "${ge:-$owner}"|htmlescape` yuuji@369: echo "

$grptxt 書き込み状況一覧

" yuuji@369: fi yuuji@375: if [ -z "$SQL" ]; then yuuji@375: bridlist=`query "SELECT brid FROM report_type_blogs;"` yuuji@375: for brid in $bridlist; do # Skip this loop if $SQL set yuuji@375: brid=$(($brid + 0)) # Ensure to be a number yuuji@375: [ $brid = 0 ] && continue yuuji@375: time=`getvalbyid blog ctime $brid|colrm 11` yuuji@375: title=`getvalbyid blog title $brid|htmlescape` yuuji@399: state=`getvalbyid blog state $brid|htmlescape` yuuji@375: tt="handout_$brid" yuuji@399: [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen="" yuuji@375: if [ -z "$CATCSV" ]; then yuuji@399: echo "

$time - $title$frozen

" yuuji@375: lshandoutsub "$owner" $brid "$tt" yuuji@375: else yuuji@375: lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table yuuji@375: fi yuuji@375: tbls="$tbls${tbls:+ NATURAL JOIN }$tt" yuuji@375: done yuuji@375: fi yuuji@373: sql=${SQL:-"SELECT * FROM $tbls;"} yuuji@369: if [ -z "$CATCSV" ]; then yuuji@369: echo "

総合

" yuuji@369: echo "$sql" | lshandout_ulink_table yuuji@373: echo "

総合(CSV)

" yuuji@369: printf ".mode csv\n.header ON\n" | query yuuji@369: echo '
'
yuuji@369:     echo "$sql" | query | sed 's/^"[0-9]*	/"/'
yuuji@369:     echo "
" yuuji@373: echo "
縦持ちCSV
" yuuji@369: else yuuji@369: contenttype "Application/CSV" yuuji@369: printf ".mode csv\n.header ON\n" | query >/dev/null yuuji@369: fn=report-count.csv yuuji@369: printf 'Content-Disposition: filename="%s"\n' "$fn" yuuji@379: outfile=$tmpd/out-$$.csv yuuji@369: echo "$sql" | query | sed 's/^"[0-9]* /"/' > $outfile yuuji@369: echo "Content-Length: " `cat $outfile | wc -c`; echo yuuji@369: yuuji@369: cat $outfile yuuji@369: exit 0 yuuji@369: fi yuuji@369: printf ".mode list\n.header OFF\n.separator |\n" | query yuuji@0: } yuuji@0: lshandoutsub() { yuuji@369: # $1=owner $2=rowid of blog &optional $3=temp_table name yuuji@369: qgname=`sqlquote "$1"` yuuji@436: if isgroup "$1"; then yuuji@369: sample="(select user from grp_mem where gname=$qgname)" yuuji@2: else yuuji@2: sample="(select distinct author as user from arts)" yuuji@67: echo "(集計は板への投稿者のみ)" | html p yuuji@2: fi yuuji@369: tmpname="${3:-handout_$2}" yuuji@369: sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS yuuji@369: with arts as (select id,author from article \ yuuji@0: where blogid=(select id from blog where rowid=$2))\ yuuji@0: select (select rowid from user where name=c0.user)||' '|| \ yuuji@366: (select gecos from gecoses where name=c0.user) as 'メンバー',\ yuuji@373: substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\ yuuji@0: sum(case when c1.key is not null then 1 else 0 end)\ yuuji@369: as '[$title] コメント記入',\ yuuji@0: sum(case when c2.key is not null then 1 else 0 end)\ yuuji@369: as '[$title] ファイルの提出'\ yuuji@2: from $sample c0 \ yuuji@0: left join (select id,author from arts) a\ yuuji@0: on c0.user=a.author\ yuuji@0: left join (select id,key from article_s where key='text') c1\ yuuji@0: on a.id=c1.id left join (select id,key from article_m ) c2\ yuuji@369: on c1.id=c2.id group by c0.user order by c0.user;\ yuuji@369: \ yuuji@369: SELECT * FROM $tmpname;" yuuji@597: # err ishandoutsub: sql="$sql" yuuji@369: echo "$sql" | lshandout_ulink_table yuuji@0: } yuuji@2: gethandout() { yuuji@2: # $1=rowid of blog yuuji@579: rid=`numericalize "$1"` yuuji@866: test x"$2" = x"by_uname" && by_uname="$2" yuuji@579: blog_writable $rid $user yuuji@461: rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member yuuji@402: if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then yuuji@592: contenttype; echo yuuji@64: echo "メンバー以外は利用できません。" | html p; return yuuji@64: fi yuuji@592: # Here, this blog is writable by $user yuuji@774: mode=`getvalbyid blog mode $1` yuuji@592: owner=`getvalbyid blog owner $1` yuuji@774: blogauthor=`getvalbyid blog author $1` yuuji@774: isopenblogauthor=false yuuji@592: if [ x"$user" = x"$owner" ]; then yuuji@592: : OK yuuji@592: elif isgrpowner "$user" "$owner"; then yuuji@592: : OK yuuji@774: elif [ x"$blogauthor" = x"$user" ]; then yuuji@774: # Non-admin Author of blog cannot do gethandout() in report-closed mode yuuji@774: # for avoidance the risk of fake report-closed blog. yuuji@774: case "$mode" in # Only report-open can be handled by blog author yuuji@774: *open*) isopenblogauthor=true ;; yuuji@774: esac yuuji@592: else yuuji@592: contenttype; echo yuuji@774: echo "グループ管理者のみ取得できます。" | html p; return yuuji@592: fi yuuji@592: copy2csv=false yuuji@592: blogid=`getvalbyid blog id $1` yuuji@592: isgroup "$owner" && isgroup=true || isgroup=false yuuji@592: isgrpowner "$user" "$owner" && isgrpadmin=true || isgrpadmin=false yuuji@592: yuuji@2: i=0 yuuji@629: midfile=$tmpd/midfile yuuji@2: bd=$tmpd/archive.$$ yuuji@2: mkdir $bd yuuji@592: case "$mode" in yuuji@616: *quiz*) yuuji@616: copy2csv=true ;; yuuji@592: *enquete*) yuuji@592: copy2csv=true yuuji@592: csvline=`getvalbyid blog heading $1 | grep "..*,." | head -1` yuuji@592: # Create CSV-base table for questionnaire yuuji@592: # If heading in blog_s has at least 1 CSV line, yuuji@592: # we take the line as column list. yuuji@592: # Otherwise we produce two column CSV as below: yuuji@592: # USER,ANSWER yuuji@595: query "DROP TABLE IF EXISTS tmp_q;" yuuji@592: if [ -n "$csvline" ]; then yuuji@595: query <<-EOF yuuji@595: CREATE TEMPORARY TABLE tmp_q("user", $csvline); yuuji@595: EOF yuuji@595: if [ $? != 0 ]; then yuuji@595: contenttype; echo yuuji@595: cat <<-EOF | html p; exit yuuji@595: 掲示板のヘッダにあるCSV定義が不正でCSV出力できません。 yuuji@595: $csvline yuuji@595: 空白なしの項目名を半角カンマ区切りで1行で書いてください。 yuuji@595: EOF yuuji@595: fi yuuji@592: else yuuji@592: query <<-EOF yuuji@592: CREATE TEMPORARY TABLE tmp_q(user text PRIMARY KEY, answer); yuuji@592: EOF yuuji@592: fi yuuji@592: esac yuuji@595: if $copy2csv; then yuuji@595: mkdir $bd/$rid yuuji@595: outcsv=$bd/$rid/migrate-$rid.csv yuuji@616: fullcsv=$bd/$rid/all-text-full-$rid.csv yuuji@595: sq "$db" <<-EOF | tr '|' ',' > $outcsv yuuji@595: SELECT author as "USER", yuuji@595: replace(val, x'0a', ',') as "${csvline:-ANSWER}" yuuji@595: FROM article a JOIN article_s s ON a.id=s.id yuuji@595: AND blogid=(SELECT id FROM blog WHERE rowid=$rid) yuuji@595: AND s.key='text'; yuuji@595: EOF yuuji@616: sq "$db" <<-EOF > $fullcsv yuuji@616: .mode csv yuuji@616: .head 1 yuuji@616: SELECT author as "ユーザ", yuuji@616: (SELECT gecos FROM gecoses g WHERE author=g.name) as "表示名", yuuji@616: val as "テキスト" yuuji@616: FROM article a JOIN article_s s ON a.id=s.id yuuji@616: AND blogid=(SELECT id FROM blog WHERE rowid=$rid) yuuji@616: AND s.key='text'; yuuji@616: EOF yuuji@595: fi yuuji@629: query <<-EOF > $midfile # Using tempfile for quick db-unlock yuuji@616: SELECT a.rowid, a.id artid, a.author, hex(s.val) yuuji@592: FROM article a JOIN article_s s ON a.id=s.id yuuji@866: WHERE blogid=(SELECT id FROM blog WHERE rowid=$rid) yuuji@866: ORDER BY a.rowid; yuuji@592: EOF yuuji@629: cat $midfile | while IFS='|' read rowid artid author text; do yuuji@774: $isgrpowner || $isopenblogauthor \ yuuji@774: || isfilereadable $user article_s $rowid || continue yuuji@866: if [ "$by_uname" ]; then yuuji@866: dir=`printf $bd/%d/%s "$rid" "$author"` yuuji@866: else yuuji@866: dir=`printf $bd/%d/%06d "$rid" "$rowid"` yuuji@866: fi yuuji@866: txt=`printf %06d $rowid`.txt yuuji@867: test -d "$dir" || mkdir -p "$dir" yuuji@867: echo "$author" > "$dir"/Author.txt yuuji@867: echo "$text" | unhexize > "$dir/$txt" yuuji@592: i=0 yuuji@592: query "SELECT m.rowid, m.val FROM article_m m \ yuuji@592: WHERE id='$artid' AND m.key IN ('image', 'document', 'binary');" \ yuuji@592: | while IFS='|' read mrowid filename; do yuuji@620: i=$((i+1)) yuuji@866: if [ "$by_uname" ]; then yuuji@866: outfile=`printf "%s/%06d-%s" "$dir" $rowid "$filename"` yuuji@866: else yuuji@866: outfile=`printf "%s/%02d-%s" "$dir" $i "$filename"` yuuji@866: fi yuuji@592: query "SELECT quote(bin) FROM article_m WHERE rowid=$mrowid;" \ yuuji@866: | unhexize > "$outfile" yuuji@592: done yuuji@592: done yuuji@592: if [ ! -d $bd/$rid ]; then yuuji@2: contenttype; echo yuuji@67: echo "取得できるファイルがありませんでした。" | html p yuuji@2: return yuuji@2: fi yuuji@592: yuuji@592: if $copy2csv; then yuuji@616: query <<-EOF > $bd/$rid/all-text-1stline-$rid.csv yuuji@592: .mode csv yuuji@592: .head 1 yuuji@766: CREATE TEMPORARY TABLE IF NOT EXISTS tmp_q("user", "TEXT"); yuuji@595: .import $outcsv tmp_q yuuji@592: SELECT * FROM tmp_q; yuuji@592: .mode list yuuji@592: .head 0 yuuji@592: EOF yuuji@592: fi yuuji@592: err "BDLIST: `ls -l $bd`" yuuji@592: arcname=archive-$rid.tar.gz yuuji@658: ### outstdout=true yuuji@2: (cd $bd yuuji@592: # query() CANNOT BE used in this subshell yuuji@658: if [ "$outstdout" ]; then yuuji@658: cat <<-EOF yuuji@658: Content-type: application/x-gzip yuuji@658: Content-Disposition: filename="$arcname" yuuji@658: yuuji@658: EOF yuuji@658: tar zcf - $rid yuuji@658: return yuuji@658: else yuuji@658: tar zcf .archive.tar.gz $rid && mv .archive.tar.gz "$arcname" yuuji@658: err Creating tar archive "`ls -l "$arcname"`" yuuji@658: fi yuuji@2: ) yuuji@592: arcfile=$bd/$arcname yuuji@2: echo "Content-type: application/x-gzip" yuuji@592: echo "Content-Length: `cat $arcfile|wc -c`" yuuji@592: echo "Content-Disposition: filename=\"$arcname\"" yuuji@2: echo yuuji@592: cat $arcfile yuuji@2: } yuuji@462: blogseen() { # $1 = blogid yuuji@462: blogid=${1%%[!0-9]*} yuuji@462: if [ -z "$blogid" ]; then yuuji@462: echo "Invalid blog id" | html p; exit yuuji@462: fi yuuji@462: blog_writable "$blogid" "$user" yuuji@462: rc=$? # =0: writable, $BLOG_NOTMEM bit set => not member yuuji@462: if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then yuuji@462: echo "メンバー以外は利用できません。" | html p; return yuuji@462: fi yuuji@462: owner=`getvalbyid blog owner $rowid` yuuji@467: qowner=`sqlquotestr "$owner"` yuuji@467: grprowid=`query "SELECT rowid FROM grp WHERE gname=$qowner;"` yuuji@467: ge=`gecos "$owner" | htmlescape` yuuji@467: title=`getvalbyid blog title $rowid | htmlescape` yuuji@467: h1="アクセス時刻" yuuji@467: link2board="$title" yuuji@467: link2group="$ge" yuuji@467: _m4 -D_TITLE_="$h1" $layout/html.m4.html yuuji@467: echo "$h1" | html h1 yuuji@467: echo "[$link2board]@$link2group" | html h2 yuuji@462: warn=' class="warn"' yuuji@462: cat <<-EOF yuuji@464: yuuji@462: yuuji@462: EOF yuuji@462: query <<-EOF | yuuji@462: WITH grpmem as ( yuuji@462: SELECT user, (SELECT gecos FROM gecoses WHERE name=user) gecos yuuji@462: FROM grp_mem yuuji@462: WHERE gname=(SELECT val FROM blog_s yuuji@463: WHERE id=(select id from blog where rowid=$blogid) yuuji@462: AND key='owner') yuuji@462: ), acctime AS ( yuuji@462: SELECT user, max(time) atime yuuji@462: FROM tblaccesses yuuji@463: WHERE tbl='blog' AND tblrowid=$blogid yuuji@462: GROUP BY user yuuji@462: ) yuuji@462: SELECT g.user, yuuji@462: (SELECT rowid FROM user u WHERE u.name=g.user), yuuji@462: hex(gecos), yuuji@462: atime yuuji@462: FROM grpmem g LEFT JOIN acctime t yuuji@462: ON g.user = t.user yuuji@470: GROUP BY g.user yuuji@462: ORDER BY atime DESC; yuuji@462: EOF yuuji@462: while IFS='|' read u uid hexge time; do yuuji@462: td=${time:+" yuuji@462: yuuji@462: yuuji@462: $td${time:----} yuuji@462: EOF yuuji@462: done yuuji@462: cat <<-EOF yuuji@462:
メンバーuname最終閲覧時刻
"} # If the variable time is set, td= yuuji@462: td=${td:-""} # else td= yuuji@462: cat <<-EOF yuuji@462:
`echo "$hexge"|unhexize|htmlescape``echo ${u%%@*}|htmlescape`
yuuji@462:

[$title]に戻る

yuuji@462: yuuji@462: EOF yuuji@462: } yuuji@80: lsmyfile() { # $1(optional)=SortBy yuuji@80: case "$1" in yuuji@80: ""|CTIME-DESC) yuuji@80: by="CTIME" ord="DESC" ;; yuuji@80: CTIME*) by="CTIME" ;; yuuji@80: FILE*) by="FILE" ;; yuuji@80: OWNER*) by="OWNER" ;; yuuji@80: TITLE*) by="TITLE" ;; yuuji@80: esac yuuji@80: case "$1" in yuuji@80: *DESC) ord="DESC" ;; yuuji@80: esac yuuji@80: case "$ord" in yuuji@80: DESC) lkod="" jord="降順" ;; yuuji@80: *) lkod="-DESC" jord="昇順" ;; yuuji@80: esac yuuji@80: sql="select m.val||'/'||m.rowid FILE, yuuji@80: coalesce( yuuji@80: case when (select name from user where name=bs.owner) yuuji@80: is not null yuuji@80: then (select val from user_s where name=bs.owner yuuji@80: and key='gecos') yuuji@80: when (select gname from grp where gname=bs.owner) yuuji@80: is not null yuuji@80: then (select val from grp_s where gname=bs.owner yuuji@80: and key='gecos') yuuji@80: else yuuji@80: null yuuji@80: end, yuuji@80: bs.owner yuuji@80: ) OWNER, yuuji@80: a_s.val CTIME, yuuji@80: ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE yuuji@80: from (select rowid,id,val from article_m where id yuuji@80: in (select id from article where author='$user') yuuji@80: and type like 'file:%') yuuji@80: m left join article a on m.id=a.id yuuji@80: left join article_s a_s on a.id=a_s.id and a_s.key='ctime' yuuji@80: left join (select id, yuuji@80: max(case key when 'owner' then val end) as owner, yuuji@80: max(case key when 'title' then val end) as title yuuji@80: from blog_s group by id) yuuji@80: bs on a.blogid=bs.id yuuji@80: left join blog b on bs.id=b.id yuuji@80: where m.val is not null order by $by $ord;" yuuji@80: err lshandoutbyauthor: sql=`echo "$sql"` yuuji@80: title="個人提出ファイル" yuuji@189: _m4 -D_TITLE_=$title $layout/html.m4.html yuuji@80: hra="' yuuji@80: echo "$sql"|sq -html -header $db ) \ yuuji@80: | sed -e "s|\(\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2|" \ yuuji@80: -e "s|,t,\(.*\):\([^<]*\)\(\)|$hrc\2\">\1\3|" \ yuuji@80: -e "s|\(\)\([A-Z]*\)\(\)|\1$hra\2$lkod\">\2|" \ yuuji@189: | _m4 -D_TITLE_=$title -D_FORM_="

($by$jord)

" \ yuuji@189: -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html yuuji@80: echo '' yuuji@80: } yuuji@875: getteamcsv() { yuuji@875: gid=`numericalize "$1"` yuuji@875: grp=`getgroupbyid "$gid"` yuuji@875: err gid=$gid grp=$grp yuuji@875: if ! isgrpowner "$user" "$grp"; then yuuji@875: contentytpe 'text/plain; charset="utf-8"'; echo yuuji@875: echo "管理者メンバー以外は利用できません。" | html p; return yuuji@875: fi yuuji@875: fn="team-$gid.csv" yuuji@875: csv="$tmpd/$fn" yuuji@875: err csv=$csv yuuji@875: # We can leave csv mode here because this scripts will exit soon yuuji@875: query <<-EOF yuuji@875: .mode csv yuuji@875: .head 1 yuuji@875: .output $csv yuuji@875: SELECT val "ルーム名を事前割り当て", user "メールアドレス" yuuji@875: FROM grp_mem_m yuuji@875: WHERE key='team' yuuji@875: AND gname=(SELECT gname FROM grp WHERE rowid=$gid) yuuji@875: ORDER BY val; yuuji@875: EOF yuuji@875: contenttype 'text/plain; charset="utf-8"' yuuji@875: echo "Content-Disposition: filename=\"$fn\"" yuuji@875: echo "Content-Length: " `cat $csv | wc -c`; echo yuuji@875: #echo "Content-Type: " ${type#file:}; echo yuuji@875: cat $csv yuuji@875: exit yuuji@875: } yuuji@78: searchart() { yuuji@862: _m4 -D_TITLE_="検索結果" $layout/html.m4.html yuuji@301: kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC yuuji@510: bloglist=`getpar bloglist|sed 's/[^0-9,]//g'` yuuji@301: kwdgrp="" yuuji@337: authcond="" yuuji@78: if [ -z "$kwd" ]; then yuuji@78: echo "検索語を指定してください" | html p; return yuuji@78: fi yuuji@510: if logstart "$searchlog"; then yuuji@510: { echo "kwd=$kwd" yuuji@510: test -n "$bloglist" && echo "bloglist=$bloglist" yuuji@510: } >> $searchlog yuuji@510: logend "$searchlog" yuuji@510: fi yuuji@301: if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then yuuji@301: # Like '#1234', assume as artID yuuji@301: rowid=$((${kwd#\#} + 0)) # Force to be a number yuuji@337: kc="ar.rowid = $rowid" yuuji@301: else yuuji@344: for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes yuuji@488: ctime="" yuuji@344: if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then yuuji@344: # '@<2016-10-10' -> ctime < '2016-10-10' yuuji@344: # '@>=2016-10-10' -> ctime >= '2016-10-10' yuuji@344: # '@2016-10-10' -> ctime GLOB '@2016-10-10' yuuji@344: k=${k#@} yuuji@344: case "$k" in yuuji@344: [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;; yuuji@344: *) op='GLOB'; ctime="${k##*[><= ]}*" ;; yuuji@344: esac yuuji@344: kc=$kc${kc:+" AND "}"ctime $op '${ctime}'" yuuji@301: # Not sure GROUP BY a.blogid is comfortable for searchers...? yuuji@331: ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results yuuji@304: elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then yuuji@304: ctime=`date +%F` yuuji@488: elif n=`expr x"$k" : 'x@\([0-9]*\)days*'` >/dev/null >&2; then yuuji@488: ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"` yuuji@344: elif [ x"$k" = x"@week" ]; then yuuji@344: ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"` yuuji@488: elif n=`expr x"$k" : 'x@\([0-9]*\)weeks*'` >/dev/null >&2; then yuuji@488: n=$((n * 7)) yuuji@488: ctime=`query "SELECT datetime('now', 'localtime', '-$n days');"` yuuji@352: elif [ x"$k" = x"@month" ]; then yuuji@352: ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"` yuuji@488: elif n=`expr x"$k" : 'x@\([0-9]*\)months*'` >/dev/null >&2; then yuuji@488: ctime=`query "SELECT datetime('now', 'localtime', '-$n month');"` yuuji@352: elif [ x"$k" = x"@year" ]; then yuuji@352: ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"` yuuji@488: elif n=`expr x"$k" : 'x@\([0-9]*\)years*'` >/dev/null >&2; then yuuji@488: ctime=`query "SELECT datetime('now', 'localtime', '-$n year');"` yuuji@488: fi yuuji@488: if [ -n "$ctime" ]; then yuuji@352: kc=$kc${kc:+" AND "}"ctime > '${ctime}'" yuuji@301: else yuuji@510: e="" yuuji@510: case "$k" in yuuji@510: *${likeesc}*) e="" ;; # Giving up char-escaping yuuji@510: *%*|*_*) k=`echo "$k"|sed "s/\([%_]\)/${likeesc}\1/g"` yuuji@510: e=" ESCAPE '$likeesc'" ;; yuuji@510: esac yuuji@510: kc=$kc${kc:+" AND "}"content LIKE '%$k%'$e" yuuji@301: fi yuuji@301: done yuuji@301: fi yuuji@165: kwd=`echo "$kwd"|htmlescape` yuuji@78: owner=`getpar owner` yuuji@78: owner=${owner:-$1} yuuji@747: grid=`getpar grid` yuuji@508: msg="" yuuji@747: if [ -n "$grid" ]; then yuuji@747: grp=`getgroupbyid "$grid"` yuuji@747: qgrp=`sqlquote "$grp"` yuuji@747: cond="WHERE key='owner' AND val=$qgrp" yuuji@747: msg="(`linkhome $grid` グループから)" yuuji@747: elif [ -n "$owner" ]; then yuuji@78: cond="where key='owner' and val='$owner'" yuuji@747: msg="(`linkhome $owner` さんの記録から)" yuuji@331: elif { author=`getpar author`; test -n "$author"; }; then yuuji@331: atptn=`sqlquotestr $author` yuuji@337: #kc="$kc${kc:+ AND }author=$atptn" yuuji@337: authcond="WHERE author=$atptn" yuuji@331: if isuser $author; then yuuji@509: msg="(`linkhome $author` さんの書き込みから)" yuuji@331: fi yuuji@78: fi yuuji@509: if [ -n "$bloglist" ]; then yuuji@509: blogcond="AND bl.rid IN ($bloglist)" yuuji@509: fi yuuji@509: yuuji@509: sf=`search_form "$search_form_args" "$kwd" | sed '1d;$d'` # rm
yuuji@509: echo "$sf" | sed -e "/POST SENTENCE/s/.*/__PS__/" -e "/EOF/q" \ yuuji@509: | _m4 -D__PS__="による検索結果$msg" yuuji@509: echo "(上記入力窓で再検索すると下記の掲示板のみに絞って再検索します)" \ yuuji@509: | html p 'class="small"' yuuji@78: # article_s: id=article-id, key='text', val='TEXT' yuuji@78: # article: id=article-id, blogid=blogkd yuuji@78: # blog: id=blog-id, author=LeaderAuthor yuuji@78: # blog_s: id=blog-id, key='title', val='BLOG-TITLE' yuuji@78: # WANT: blog-ROWid,article-id,val(TEXT) yuuji@337: sql2="`sql4readableblogs` -- Extract user-readable blogs yuuji@337: -- 0.3sec yuuji@337: WITH artsm AS ( yuuji@337: SELECT a.id,ctime, text || ' ' || coalesce(files, '') content yuuji@337: FROM article a yuuji@337: LEFT JOIN yuuji@337: (SELECT ars.id, ctime, text, coalesce(files, '') files yuuji@337: FROM (SELECT id, yuuji@337: max(CASE key WHEN 'ctime' THEN val END) ctime, yuuji@337: max(CASE key WHEN 'text' THEN val END) text yuuji@337: FROM article_s yuuji@337: GROUP BY id) ars yuuji@337: LEFT JOIN yuuji@337: (SELECT id, group_concat(val) files yuuji@337: FROM article_m yuuji@337: WHERE type LIKE 'file:%' yuuji@337: GROUP BY id) arm yuuji@337: ON ars.id=arm.id yuuji@337: ) ar yuuji@337: ON a.id=ar.id yuuji@337: ), ar AS ( yuuji@338: SELECT a.rowid, a.blogid, a.id, a.author, ctime, content yuuji@338: FROM article a JOIN artsm ON a.id=artsm.id yuuji@337: $authcond yuuji@337: ), bl AS ( yuuji@337: SELECT blg.rid, blg.*, blog_s.val TITLE yuuji@337: FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title' yuuji@337: ) yuuji@819: SELECT bl.rid||'+n:all#'||ar.id '', yuuji@337: bl.title TITLE, yuuji@338: (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR, yuuji@337: substr(ctime, 0, 11) DATE, yuuji@337: substr(content, 0, 78) TEXT yuuji@337: FROM ar JOIN bl yuuji@337: ON ar.blogid=bl.id yuuji@509: WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) $blogcond yuuji@337: ORDER by DATE DESC, TITLE, ctime;" yuuji@78: sedopt="s,\([^<]*\),VIEW," yuuji@337: # echo "$sql2" > tmp/sql.out yuuji@352: result=$tmpd/result.$$ yuuji@78: cat< yuuji@352: `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result` yuuji@78: yuuji@78: EOF yuuji@352: if [ -s "$result" ]; then yuuji@356: found=$((`grep "^" $result | wc -l` + 0)) # Cast to INT yuuji@352: one=${found%1} yuuji@352: echo "$found match${one:+es} found" yuuji@509: # VIEW yuuji@509: # -> 39,49,55, -> 39,49,55 yuuji@509: # -> yuuji@509: sed -n "/.*href=.*replyblog\+\([0-9][0-9]*\).*/s//\1/p" "$result" \ yuuji@509: | sort | uniq | tr '\n' ',' \ yuuji@509: | sed -e 's/,$//' \ yuuji@509: -e 's/^//' yuuji@352: else yuuji@352: echo orz... yuuji@352: fi yuuji@509: echo "$sf" | sed "1,/-- EOF/d" # Close
yuuji@78: } yuuji@0: listblog() ( yuuji@67: # $1={user,group} yuuji@749: qow=`sqlquote "$1"` yuuji@67: cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc" yuuji@0: cgi_form searchart<`cgi_text kwd`という語を含む記事をこの一覧から検索 yuuji@0: `cgi_hidden owner $user` yuuji@0: EOF yuuji@573: DT_CHLD=article:blogid DT_QOWNER=$qow \ yuuji@573: dumptable html blog 'ctime title heading' "$cond" yuuji@0: ) yuuji@0: yuuji@0: blog_addentry() { yuuji@749: # $1=GRProwID(if it is a group) yuuji@579: grprowid=`numericalize $1` yuuji@0: rowid=`getpar rowid` yuuji@356: ## err blog_addentry0: rowid=$rowid yuuji@80: if [ -n "$grprowid" ]; then yuuji@80: owner=`getgroupbyid $grprowid` yuuji@80: else yuuji@80: owner=`getpar owner` yuuji@80: fi yuuji@749: htmlowner=`echo $owner|htmlescape` yuuji@579: err blog-add: \$1=$grprowid rowid=$rowid owner=$owner yuuji@436: if isgroup "$owner"; then yuuji@749: if [ -z "$grprowid" ]; then yuuji@749: qgrp=`sqlquote "$owner"` # Inefficient... yuuji@749: grprowid=`query "SELECT rowid FROM grp WHERE gname=$qgrp;"` yuuji@749: fi yuuji@838: groupmode=1 listing=$owner GF_OWNER=$owner yuuji@838: titleguide="[$owner]" guide="[`linkhome $grprowid`]" yuuji@860: GF_ARGS=$(mvteamform "$owner") yuuji@80: else yuuji@838: usermode=1 listing=$user guide="[個人]" titleguide=$guide yuuji@80: fi yuuji@80: yuuji@860: title=`getpar title` yuuji@860: if [ -n "$title" ]; then yuuji@80: if [ "$usermode" ]; then yuuji@122: err usermode: user=$user owner=$owner yuuji@0: if [ x"$user" != x"$owner" ]; then yuuji@67: echo "他人の日記は書けません" | html p yuuji@80: return 2 yuuji@0: fi yuuji@80: elif [ "$groupmode" ]; then # if write to group log yuuji@0: grp=$owner #\`getpar grp\` yuuji@0: err ismember: $user $grp yuuji@0: if ! ismember "$user" "$grp"; then yuuji@67: echo "(話題作成はこのグループに加入してから)" | html p yuuji@80: return 3 yuuji@0: fi yuuji@0: fi yuuji@0: par2table $formdir/blog.def yuuji@0: serial=`getpar serial` yuuji@356: ## err SERIAL: $serial ROWID=$rowid listing=$listing yuuji@0: id="" yuuji@0: if [ -n "$rowid" ]; then yuuji@0: # Here, id becomes NULL when removal of entries at par2table yuuji@0: id=`query "select rowid from blog where rowid=$rowid;"` yuuji@0: elif [ -n "$serial" ]; then yuuji@0: # If new blog leader created, traverse to its head. yuuji@0: id=`query "select rowid from blog where id='$serial';"` yuuji@356: ## err new-Leader: "select rowid from blog where id='$serial';" id=$id yuuji@0: fi yuuji@0: if [ -n "$id" ]; then yuuji@860: ## If modifying existing blog, JUMP to blog_reply yuuji@0: blog_reply $id yuuji@0: return yuuji@0: fi yuuji@860: # Newly created blog comes here: yuuji@860: mv2team=`getpar mv2team` yuuji@860: if [ -n "$mv2team" -a -n "$groupmode" ]; then yuuji@860: # For newly created BLOG, assign team-name if necessary and correct yuuji@860: qmt=`sqlquote "$mv2team"` yuuji@860: qowner=`sqlquote "$owner"` yuuji@860: team=$(query "SELECT val FROM grp_mem_m yuuji@860: WHERE key='team' AND val=$qmt AND gname=$qowner;") yuuji@860: if [ -n "$team" ]; then # If it is valid team name yuuji@860: qtt=`sqlquote "$title"` yuuji@860: # We should acquire newly created blog id from title step by step yuuji@860: thisblog=$(query \ yuuji@860: "SELECT id FROM blog_s yuuji@860: WHERE id IN (SELECT id FROM blog_s yuuji@860: WHERE key='owner' AND val=$qowner) yuuji@860: AND key='title' AND val=$qtt;") yuuji@860: if [ -n "$thisblog" ]; then yuuji@860: query "REPLACE INTO blog_s(id, key, type, val) yuuji@860: VALUES('$thisblog', 'team', 'string', $qmt);" yuuji@860: fi yuuji@860: fi yuuji@860: fi yuuji@0: fi yuuji@838: echo "${titleguide}新規話題作成" > $tmpd/title.$$ yuuji@838: echo "${guide}新規話題作成" > $tmpd/h1.$$ yuuji@749: listblog "$listing" > $tmpd/listblog.$$ yuuji@81: genform $formdir/blog.def \ yuuji@189: | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \ yuuji@838: -D_H1_="spaste(\`$tmpd/h1.$$')" \ yuuji@189: -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \ yuuji@189: -D_DUMPHEAD_="これまでの蓄積" \ yuuji@189: -D_FORM_="syscmd(\`cat')" \ yuuji@189: -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \ yuuji@189: $layout/html.m4.html \ yuuji@189: $layout/form+dump-whead.m4.html yuuji@0: } yuuji@0: yuuji@379: blog_reply() { # Posting to blog article yuuji@801: # $1=rowid $2=control-sequence yuuji@579: rowid=`numericalize $1` # Ensure (already purified in s4.cgi) yuuji@0: yuuji@0: if [ -z "$rowid" ]; then yuuji@67: echo "表示する日記番号が未指定です。" | html p yuuji@0: return yuuji@0: fi yuuji@0: title=`getvalbyid blog title $rowid` yuuji@0: owner=`getvalbyid blog owner $rowid` yuuji@749: htmlowner=`echo $owner|htmlescape` yuuji@466: qowner=`sqlquotestr "$owner"` yuuji@397: if [ -z "$title" ]; then yuuji@397: echo "日記番号指定が無効です。" | html p yuuji@397: return yuuji@397: fi yuuji@885: err "blog_reply Started: `gdate +%S.%03N` blogrowid=$rowid" yuuji@397: blog_writable $rowid $user; rc=$? yuuji@397: if [ $rc = 0 ]; then yuuji@397: iswritable=true yuuji@397: else yuuji@397: iswritable=false yuuji@397: if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then yuuji@397: isfrozen=true yuuji@397: frozen_class='frozen"' yuuji@399: frozen_flag=$FROZEN_TAG yuuji@397: fi yuuji@397: fi yuuji@0: if isuser "$owner"; then yuuji@0: subtitle="`gecos $owner` さんの話題" yuuji@0: else yuuji@466: grprowid=`query "select rowid from grp where gname=$qowner;"` yuuji@341: subtitle="グループ yuuji@749: $htmlowner での話題 yuuji@222: `query \"SELECT printf('(チーム:%s)', val)\ yuuji@222: FROM blog_s yuuji@222: WHERE id=(SELECT id FROM blog WHERE rowid=$rowid) yuuji@222: AND key='team'; yuuji@222: \"|htmlescape`" yuuji@288: memclass=`grp_getbodyclass "$owner"` yuuji@0: fi yuuji@0: yuuji@0: text=`getpar text` yuuji@0: if [ -n "$text" ]; then yuuji@397: if $iswritable; then yuuji@782: ## BEGIN: 2020-06-11 - Post Integrity Check. Disable if it slows down.. yuuji@782: blogid=`getpar blogid | tr -c -d 'a-z0-9'` yuuji@782: brid=`query "SELECT rowid FROM blog WHERE id='$blogid';"` yuuji@782: if [ x"$rowid" != x"$brid" ]; then yuuji@782: _id=`getpar id | tr -c -d 'a-z0-9'` yuuji@782: _aid=`query "SELECT rowid FROM article WHERE id='$_id';"` yuuji@782: if [ -z "$_aid" ]; then yuuji@782: echo "掲示板から書き込んで下さい。" | html p yuuji@782: return yuuji@782: fi yuuji@782: fi yuuji@782: ## END: yuuji@80: par2table $formdir/article.def yuuji@356: st=$? yuuji@885: err "blog_reply: POSTdone `gdate +%S.%03N` - st=$st title=$title owner=$owner user=$user, blogid=$blogid" yuuji@80: case $st in yuuji@80: 0|4) yuuji@80: [ "$st" = "4" ] && act="書込削除" yuuji@386: blog_notify_reply $rowid $user "$text" $act yuuji@386: if [ -n "$grprowid" ]; then yuuji@386: qgrp=$(sqlquote "$owner") yuuji@749: dbsetbyid grp "$owner" wtime "`date '+%F %T'`" yuuji@474: else yuuji@474: dbsetbyid user "$user" wtime "`date '+%F %T'`" yuuji@386: fi yuuji@386: ;; yuuji@80: esac yuuji@0: else yuuji@397: if $isfrozen; then yuuji@397: title="$title(凍結板につき書き込み不可)" yuuji@397: else yuuji@397: title="$title(加入してないので書き込み不可)" yuuji@397: fi yuuji@0: fi yuuji@0: fi yuuji@0: def=$formdir/article.def yuuji@749: echo "$title" | htmlescape > $tmpd/title.$$ yuuji@397: echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$ yuuji@801: ${BLOG_SHOW:-blog_showentry} blog $rowid "$2" \ yuuji@259: | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \ yuuji@838: -D_H1_="spaste(\`$tmpd/title.$$')" \ yuuji@259: -D_BODYCLASS_=general"${memclass:+ $memclass}" \ yuuji@189: -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \ yuuji@189: -D_FORM_='' \ yuuji@189: -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \ yuuji@189: $layout/html.m4.html $layout/form+dump-whead.m4.html yuuji@885: err "blog_reply Finished: `gdate +%S.%03N` user=$user owner=[$owner] title=[$title]" yuuji@0: } yuuji@451: yuuji@451: blog_reply_article() { # Direct link to article in some blog yuuji@451: arid=${1:-0} # Already sanitized to digits yuuji@451: brid=`query "SELECT rowid FROM blog WHERE \ yuuji@451: id=(SELECT blogid FROM article WHERE rowid=$arid);"` yuuji@451: if [ -n "$brid" ]; then yuuji@451: newurl="?replyblog+$brid#$arid" yuuji@451: echo "Refresh: 0; $newurl"; echo yuuji@451: exit 0 yuuji@451: else yuuji@451: contenttype; echo yuuji@451: echo "無効な記事番号です." | html p yuuji@451: fi yuuji@451: }