Mercurial > hgrepos > hgweb.cgi > s4
view s4-blog.sh @ 457:f06e2356cc70
pseudo content test2
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Wed, 16 Aug 2017 13:40:36 +0859 |
parents | 354697d936c7 |
children | d7b23ad0e2e8 |
line wrap: on
line source
# type cgiinit >/dev/null 2>&1 || . ./s4-funcs.sh # Global error flags BLOG_NOTMEM=1 BLOG_FROZEN=2 FROZEN_TAG='<span class="frozen">[凍結]</span>' blog_genform() { # t=$1 } blog_writable() ( # $1=articleid $2=user # Return: $?=0 - Writable # =1 - NOT Writable because user is not a member # =2 - NOT Writable because blog is frozen blogowner=`getvalbyid blog owner "$1"` state=`getvalbyid blog state "$1"` rc=0 [ x"$blogowner" = x"$2" ] || isuser "$blogowner" || ismember "$2" "$blogowner" || rc=$((rc+$BLOG_NOTMEM)) [ "$state" = "frozen" ] && rc=$((rc+$BLOG_FROZEN)) return $rc ) blog_readable() { # $1=articleid $2=user mode=`getgroupattr $grp regmode` } blog_notify_reply() ( # $1=blogid $2=ReplyingUser $3=WrittenText $4(optional)=Action blogid="${1%%[!A-Z0-9a-z_]*}" blogowner=`getvalbyid blog owner "$blogid"` blogtitle=`getvalbyid blog title "$blogid"` blogurl="$urlbase?replyblog+$blogid" action=${4:-書き込み} mode=`getvalbyid blog notify "$blogid"` ### EXCEPT=`sqlquote "$user"` ## User should receive to feal some annoyance case $mode in admin) if isgroup "$blogowner"; then emails=`getgroupadminmails $blogowner` else emails=`collectemail $blogowner` fi notifyto=`getpar notifyto` if [ -n "$notifyto" ]; then emails=$emails" `email4groupbyuid \"$blogowner\" $notifyto`" fi ;; no) emails="" ;; *) team=`query "SELECT val FROM blog_s WHERE id=(SELECT id FROM blog WHERE rowid=$blogid) AND key='team';"` # team cannot get `getvalbyid blog team "$blogid"` because it's not # defined in blog.def. Yes, it is Illegal USE!! emails=`TEAM=$team collectemail $blogowner` ;; esac ## 2017-0210 Respond to the direct reply mark such as: >#1234 replymark=`echo "$3"|nkf -w -Z0|grep '^ *>#'` authgecos=`gecos $2` if [ -z "$4" -a -n "$replymark" ]; then # If the action is new subscription($4="") and has ">#123" marks... ids=`echo "$replymark"|sed 's/[^#0-9]*#\([0-9]*\)[^#0-9]*/\1 /g'` ids=`echo $ids|tr -dc '[0-9 ]'|tr ' ' ','` # -> 123,345,347 unames=`query "SELECT distinct author FROM article \ WHERE rowid in ($ids)\ AND blogid=(SELECT id FROM blog WHERE rowid=$blogid);"` if [ -n "$unames" ]; then emails=$emails" `email4group \"$blogowner\" $unames`" for e in $unames; do g=`gecos $e` whom=$whom"${whom:+,}${g:-$e}さん" done action="${whom}への返信" fi else [ x"$2" = x"$blogowner" ] && return # If author=blogowner, unnecessary fi test -z "$emails" && return err notify: user=$user Admins=`getgroupadmins $blogowner` Mode=$mode Emails="[$emails]" SMAIL_TO="`echo "$blogowner" | nkf -jM | tr -d '\n'` readers <$admin>" \ smail "$emails" "${action}通知 $urlbase"<<EOF [$blogtitle]板に${action}がありました。 ※このメイルに返信しても通知者には伝わりません(管理者宛になる)。 場所: $blogurl (返信先) 所有: $blogowner 題目: $blogtitle 筆者: $authgecos 内容: `echo "$3"|sed 's/^/> /'` EOF ) blog_showentry() { # $1=table $2=rowid # if [ -n "$2" ]; then # if [ -n "$imgcached" ]; then # bstmpdir=$tmpdir/$imgcached/$thumbxy # else # bstmpdir=$tmpd # # tmpd=`mktempd` # # tmpfiles=$tmpfiles" $tmpd" # fi # fi td=`getcachedir "article/$2"` [ -d "$td" ] || mkdir -p $td tbl=${1%%[!A-Z0-9a-z_]*} rowid=${2%%[!A-Z0-9a-z_]*} err blow_showentry: rowid=$rowid, '$2'=$2 user=$user ts=${tbl}_s tm=${tbl}_m at=article as=article_s am=article_m serial=$(($(date +%s)-1420038000))s$$ blog_writable $rowid $user rc=$? if [ $rc = 0 ]; then iswritable=true ismem=true else iswritable=false if [ $((rc & $BLOG_NOTMEM)) -gt 0 ]; then ismem=false else ismem=true fi fi # This function grasps blog entry definiton directly. # blog: id # blog_s: title,ctime,heading # blog_m: *article blogowner=`getvalbyid blog owner "$2"` isgroup "$blogowner" && isgroup=true || isgroup=false # 2015-10-05 check readable if ! $iswritable; then # err blogowner=$blogowner if $isgroup; then regmode=`getgroupattr $blogowner regmode` # err regmode=$regmode if [ x"$regmode" = x"moderated" ]; then # if ! ismember $user $blogowner; then if ! $ismem; then echo "加入してからどうぞ" | html p return fi fi fi fi case `getvalbyid blog notify "$2"` in # "all", "admin" or "no" (or NULL) admin) notifyto=1 ;; *) notifyto="" ;; esac # err "SELECT id from $tbl where rowid=$rowid" id=`query "select id from $tbl where rowid=$rowid;"` #err id=$id #err "select val from $ts where key='title' and id='$id';" #(1)Display root article cat<<EOF <form class="replyblog" action="$myname?replyblog+${rowid}#bottom" method="POST" enctype="multipart/form-data"> <table class="bloghead"> EOF href="<a href=\"?editheading+$rowid\" accesskey=\"e\" title=\"E\"> 編集 </a>" if $ismem; then case `getvalbyid blog mode $rowid` in *report*) href2="<a href=\"?lshandout+$rowid\" accesskey=\"l\" title=\"L\"> 提出状況 </a>" ;; esac href3="(<a href=\"?gethandout+$rowid\" accesskey=\"f\" title=\"F\">ファイル取得</a>)" fi href4='<a href="#bottom" accesskey="b" title="B"> 末尾へ</a>' quizmodefile=$td/quiz; rm -f "$quizmodefile" # XXX: Global state query<<-EOF | SELECT coalesce((SELECT "yes" FROM blog WHERE rowid=$rowid AND author='$user'), ''), max(CASE key WHEN 'ctime' THEN val END) ctime, max(CASE key WHEN 'heading' THEN hex(val) END) heading, CASE (SELECT val FROM $ts WHERE key="mode" AND id="$id") WHEN 'report-closed' THEN 'レポート提出用(closed)' WHEN 'report-open' THEN 'レポート提出用(open)' WHEN 'quiz' THEN 'クイズ' ELSE '' END FROM $ts WHERE id='$id' GROUP BY id; EOF { IFS='|' read edit ctime hexhead blogtype cat<<-EOF <tr><td>${edit:+$href }$ctime $blogtype $href2$href3 $href4</td></tr> <tr class="preface${frozen_class:+ }$frozen_class"> <td>`echo "$hexhead"|unhexize|hreflink|minitbl`</td></tr> </table> EOF case "$blogtype" in "クイズ") echo "クイズモードは本人と管理者の書き込みのみが表示されます。" ;; esac | html p 'class="warn"' echo '<table class="blog_replies">' if [ x"$blogtype" = x"クイズ" ]; then if $isgroup; then if ! isgrpowner "$user" "$blogowner"; then qgrp=`sqlquote "$blogowner"` cat<<-EOF > $quizmodefile AND (author IN (SELECT user FROM grp_adm WHERE gname=$qgrp) OR author='$user') EOF fi else # if user's blog if [ x"$user" != x"$blogowner" ]; then cat<<-EOF > $quizmodefile AND author IN ('$blogowner', '$user') EOF fi fi fi } lkhome="<a href=\"$myname?home" lke='">' lkedit="<a href=\"$myname?editart" hlink="$myname?home" elink="$myname?editart" catlink="$myname?showattc+article_m" deficon="img/file-icon.png" # 2016-08-15 Newer flag introduced atime=`query "SELECT time FROM acclog WHERE tbl='blog' AND tblrowid=$rowid AND user='$user';"` iconcleaner=$tmpd/iconcleaner.$$ [ -s $quizmodefile ] && cond_qz=`cat $quizmodefile` # *** DO NOT USE query(), use "sq $db" instead here *** # because the next block in pipe line uses query() repeatedly. sq $db<<EOF | WITH a_s AS ( SELECT id, max(CASE key WHEN 'ctime' THEN val END) TIME, max(CASE key WHEN 'text' THEN val END) TEXT FROM article_s GROUP by id ) SELECT a.id, CASE author WHEN '$user' THEN a.rowid||'+'||$rowid ELSE '' END edit, CASE -- 「通知送信」ボタンの有無 WHEN '$notifyto' = '' THEN '' -- 不要モードならなし WHEN '$user' = author THEN '' -- 筆者自身ならなし ELSE "yes" END notify, (SELECT rowid FROM user WHERE name=author) user_rid, coalesce((SELECT val FROM user_s WHERE name=author AND key='gecos'), author) uname, (SELECT val FROM user_s WHERE name=author AND key='$iconcachekey') icon, a.rowid, s.TIME, CASE WHEN s.TIME > '$atime' THEN 'new' ELSE '' END newer, hex(s.TEXT), (SELECT group_concat(rowid||':'||length(bin)||':'||hex(val), ' ') FROM article_m WHERE id=a.id AND key='image') imxgids FROM (select rowid,id,author from article where blogid in (select id from blog where rowid=$rowid) $cond_qz) a LEFT JOIN a_s s ON a.id=s.id; EOF while IFS='|' read id edit notify uid uname icon aid tm new hte imgids; do cachefile="$td/$id.row.html" stampfile="$td/$id.row.stamp" editlink="${edit:+<a href="$elink+$edit">編集</a> }" nt="<label style=\"font-size: 70%;\"><input type=\"checkbox\"\ name=\"notifyto\" value=\"$uid\">返信通知送信</label>" # First, check the availability of user-icon. # If not existent, clear and reset row cache by rm $stampfile if [ ! -s "$icon" ]; then rm -f "$stampfile"; unset stampfile fi if test -s "$stampfile" && test -s "$cachefile" && { ts=`cat "$stampfile"`; test -n "$ts"; } && test "$ts" '>' "$tm" && # Cache timestamp is newer test "$stampfile" -nt "$icon"; then # UserIcon is older : Nothing to do else { ######## New ROW creation begins here ######## >$cachefile tdcls="__NEWCLS__repatt" if [ -s "$icon" ]; then icfn=`echo "$icon"|htmlescape` picon="<p class=\"proficon\"><a href=\"$hlink+$uid\"><img src=\"$icfn\"></a></p>" else echo "DELETE FROM user_s WHERE key='$iconcachekey' AND val=`sqlquotestr \"$icon\"`;" >> $iconcleaner picon="" fi cat<<EOF <tr id="$id"> <td class="$tdcls">${picon}__EDIT__<a href="#$aid">#$aid</a> <a href="$hlink+$uid">$uname</a> $tm <__NOTIFY__></td> EOF echo -n "<td id=\"$aid\" class=\"repl\">" echo "$hte"|unhexize|htmlescape|hreflink|minitbl usecache='' tsfile=$td/$id.stamp for i in $imgids; do mrid=${i%%:*}; i=${i#*:}; sz=`size_h ${i%%:*}` fn=`echo "${i#*:}"|unhexize` fnb=$fn"(${sz})" case "$fn" in *.[Pp][Nn][Gg]|*.[Jj][Pp][Gg]|*.[Jj][Pp][Ee][Gg]|*.[GgTt][Ii][Ff]) # fmt=${fn##*.} # convert - jpg:- is slow...why case "$fn" in *.[Pp][Nn][Gg]) fmt=png ;; *.[Gg][Ii][Ff]) fmt=gif ;; *) fmt=jpeg ;; esac outfile=$td/$mrid-${fn%.*}.$fmt #err fn=$fn outfile=$outfile #err "usecache=$usecache `ls -l $outfile`" #err tm=$tm #err tsfile=$tsfile=`cat $tsfile` if [ -s "$outfile" ] && # $outfile should be > 0 { [ "$usecache" ] || # And usecache flag is true, or... { [ -s "$tsfile" ] && [ x"`cat $tsfile`" = x"$tm" ] };}; then usecache=1 # Set usecache flag on cat<<-EOF <a href="$catlink+$mrid"><img src="$outfile"> $fnb</a> EOF # !!NOTE!! Create row stamp ONLY WHEN imgcache is active else query "SELECT hex(bin) FROM article_m WHERE rowid=$mrid;" \ | unhexize \ | convert -define ${fmt}:size=100x100 -resize 100x100'>' \ - ${fmt}:- \ | tee "$outfile" \ | hexize \ | sed -e 's/\(..\)/%\1/g' \ -e "s|^|<a href=\"$catlink+$mrid\"><img src=\"data:image/$fmt,|" \ -e "s|\$|\">$fnb</a>|" unset stampfile # img data stream is not suitable to cache echo $tm > $tsfile fi ;; *) echo "<a href=\"$catlink+$mrid\"><img src=\"$deficon\">$fnb</a>" ;; esac done echo "</td></tr>" } > "$cachefile" ######## New ROW Creation Ends here ######## test -n "$stampfile" && date "+%F %T" > $stampfile fi # Printing a cached row sed -e "/^<td class=/s/__NEWCLS__/$new${new:+ }/" \ -e "/^<td class=/s,__EDIT__,$editlink," \ -e "/^<__NOTIFY__>/s,,${notify:+$nt}," \ $cachefile done help="=== コメントに使用できる特殊記法 === 行頭に href=URL でURLへのリンク 行頭に iframe=URL でURL先を開く iframe [[#記事番号]] でs4内の記事番号に飛ぶリンク [[URL]] でURLへのリンク [[URL|文字列]] でアンカー文字列を指定してのURLリンク {{画像URL}} でインライン画像 {{画像URL|幅}} でピクセル幅を指定したインライン画像 {{{URL}}} でURL先を開く iframe {{{URL|高さ}}} でピクセル高さを指定した iframe ## 大見出し ### 中見出し #### 小見出し |列1|列2|列3… と行頭から始まる縦棒区切り行を続けて表" touchhelp="${touchpanel:+<p class=\"help\">$help</p>}" textform='<div class="fold"> <input type="checkbox" id="cmt" checked><label accesskey="c" title="C" for="cmt">コメントする</label><div> <table class="b"> <tr><td><textarea name="text" cols="72" rows="4" title="'"$help"'"> </textarea>'"$touchhelp"'</td></tr> <tr><td>添付ファイル: <input type="file" name="image"'" $file_accept multiple></td></tr>"' </table> <input type="submit" value="送信"> <input type="reset" value="リセット"></div></div> ' cat<<-EOF </table> <!-- end of s4-blog:blog_showentry() main table --> <p class="update_link"><a href="?reload/$rowid" accesskey="r" title="R">再読込</a> / <a href="#title" id="bottom" accesskey="t" title="T">先頭へ</a></p> EOF $iswritable && cat<<-EOF <div class="blogcomment"> <input type="hidden" name="blogid" value="$id"> <input type="hidden" name="id" value="`genserial`"> <input type="hidden" name="stage" value="replyblog"> $textform </div> </form> <!-- End of s4-blog:blog_showentry() main form --> EOF # Clean up orphaned icon cache [ -s $iconcleaner ] && query ".read '$iconcleaner'" # Record access log acclog blog $rowid } lshandout() { # $1=rowid of blog blog_writable $1 $user r=$? # =0: writable, $BLOG_NOTMEM bit set => not member if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then echo "メンバー以外は利用できません。" | html p; return fi time=`getvalbyid blog ctime $1|colrm 11` owner=`getvalbyid blog owner $1` title=`getvalbyid blog title $1` ge=`gecos $owner` fh=$tmpd/formhead echo "$time [$title]@${ge:-$owner}" > $fh lshandoutsub $owner "$@" \ |_m4 -D_TITLE_="提出状況" \ -D_FORMHEAD_="syscmd(cat $fh)" \ -D_FORM_="syscmd(cat)" -D_DUMPHEAD_= -D_DUMPTABLE_= \ $layout/html.m4.html $layout/form+dump-whead.m4.html gn=`echo $owner|htmlescape` echo "<p><a href=\"?lshandoutall+$1\">グループ $gn すべてのレポート板集計</a></p>" } gethandoutcsv() { # contenttype; echo CATCSV=1 lshandoutall "$1" } gethandoutcsv2() { # contenttype; echo SQL=$(cat<<-EOF) gethandoutcsv "$1" WITH this_blog_articles AS ( SELECT rtb.id bid, rtb.brid, a.id aid, author, title, ctime FROM report_type_blogs rtb JOIN article a ON rtb.id=a.blogid ), text_or_file AS ( SELECT bid, author, title, ctime, 'text' shu, count(val) cnt FROM this_blog_articles tba, article_s s ON tba.aid=s.id WHERE key='text' GROUP by bid, author UNION SELECT bid, author, title, ctime, 'file' shu, count(val) cnt FROM this_blog_articles tba, article_m m ON tba.aid=m.id WHERE key='image' GROUP by bid, author ), count_list AS ( SELECT author, substr(ctime, 1, 10)||upper(substr(shu, 1, 1)) unit, cnt FROM text_or_file ) SELECT gecos "名前", substr(author, 1, instr(author, '@')-1) "uname", unit, cnt "post" FROM count_list cl JOIN gecoses g ON cl.author=g.name; EOF } lshandout_ulink_table() { # NO Args. Read stdin as SQL echo '<table class="b td3rr td3evw">' hrb="<a href=\"?home+" # echo "$sql" | sq -header -html $db \ # Formerly, this is called via sq() printf ".mode html\n.header ON\n" | query cat | query \ | sed -e "s,\(<TR><TD>\)\([^ ]*\) \(.*\)</TD>,\1$hrb\2\">\3</TD>," -e 's,<TD>0</TD>,<TD class="warn">0</TD>,' echo '</table>' printf ".mode list\n.header OFF\n" | query } lshandoutall() { # $1=rowid of blog blog_writable $1 $user r=$? # =0: writable, $BLOG_NOTMEM bit set => not member if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then echo "メンバー以外は利用できません。" | html p; return fi rowid=$(($1 + 0)) owner=`getvalbyid blog owner $1` qowner=`sqlquotestr "$owner"` query<<-EOF CREATE TEMPORARY TABLE IF NOT EXISTS report_type_blogs AS WITH blog_owner_mode AS ( SELECT id, blog.rowid brid, max(CASE key WHEN 'owner' THEN val END) owner, max(CASE key WHEN 'mode' THEN val END) mode, max(CASE key WHEN 'title' THEN val END) title, max(CASE key WHEN 'ctime' THEN val END) ctime FROM blog NATURAL JOIN blog_s GROUP BY id ) SELECT id, brid, title, ctime FROM blog_owner_mode WHERE owner=$qowner AND mode LIKE '%report%'; /* ↑これでレポート形式の blogid 一覧を得る */ EOF if [ -z "$CATCSV" ]; then _m4 -D_TITLE_="提出状況" $layout/html.m4.html ge=`gecos "$owner"` tbls="" grptxt=`echo "${ge:-$owner}"|htmlescape` echo "<h1>$grptxt 書き込み状況一覧</h1>" fi if [ -z "$SQL" ]; then bridlist=`query "SELECT brid FROM report_type_blogs;"` for brid in $bridlist; do # Skip this loop if $SQL set brid=$(($brid + 0)) # Ensure to be a number [ $brid = 0 ] && continue time=`getvalbyid blog ctime $brid|colrm 11` title=`getvalbyid blog title $brid|htmlescape` state=`getvalbyid blog state $brid|htmlescape` tt="handout_$brid" [ "$state" = "frozen" ] && frozen=" $FROZEN_TAG" || frozen="" if [ -z "$CATCSV" ]; then echo "<h2>$time - <a href=\"?replyblog+$brid\">$title</a>$frozen</h2>" lshandoutsub "$owner" $brid "$tt" else lshandoutsub "$owner" $brid "$tt" >/dev/null # Only create temp.table fi tbls="$tbls${tbls:+ NATURAL JOIN }$tt" done fi sql=${SQL:-"SELECT * FROM $tbls;"} if [ -z "$CATCSV" ]; then echo "<hr><h2>総合</h2>" echo "$sql" | lshandout_ulink_table echo "<h2>総合(<a href=\"?gethandoutcsv+$rowid\">CSV</a>)</h2>" printf ".mode csv\n.header ON\n" | query echo '<pre class="list">' echo "$sql" | query | sed 's/^"[0-9]* /"/' echo "</pre>" echo "<pre><a href=\"?gethandoutcsv2+$rowid\">縦持ちCSV</a></pre>" else contenttype "Application/CSV" printf ".mode csv\n.header ON\n" | query >/dev/null fn=report-count.csv printf 'Content-Disposition: filename="%s"\n' "$fn" outfile=$tmpd/out-$$.csv echo "$sql" | query | sed 's/^"[0-9]* /"/' > $outfile echo "Content-Length: " `cat $outfile | wc -c`; echo cat $outfile exit 0 fi printf ".mode list\n.header OFF\n.separator |\n" | query } lshandoutsub() { # $1=owner $2=rowid of blog &optional $3=temp_table name qgname=`sqlquote "$1"` if isgroup "$1"; then sample="(select user from grp_mem where gname=$qgname)" else sample="(select distinct author as user from arts)" echo "(集計は板への投稿者のみ)" | html p fi tmpname="${3:-handout_$2}" sql="CREATE TEMPORARY TABLE IF NOT EXISTS $tmpname AS with arts as (select id,author from article \ where blogid=(select id from blog where rowid=$2))\ select (select rowid from user where name=c0.user)||' '|| \ (select gecos from gecoses where name=c0.user) as 'メンバー',\ substr(c0.user, 1, instr(c0.user, '@')-1) 'uname',\ sum(case when c1.key is not null then 1 else 0 end)\ as '[$title] コメント記入',\ sum(case when c2.key is not null then 1 else 0 end)\ as '[$title] ファイルの提出'\ from $sample c0 \ left join (select id,author from arts) a\ on c0.user=a.author\ left join (select id,key from article_s where key='text') c1\ on a.id=c1.id left join (select id,key from article_m ) c2\ on c1.id=c2.id group by c0.user order by c0.user;\ \ SELECT * FROM $tmpname;" err ishandoutsub: sql="$sql" echo "$sql" | lshandout_ulink_table } gethandout() { # $1=rowid of blog blog_writable $1 $user r=$? # =0: writable, $BLOG_NOTMEM bit set => not member if [ $((rc & $BLOG_NOTMEM)) -gt 0 ] ; then echo "メンバー以外は利用できません。" | html p; return fi i=0 bd=$tmpd/archive.$$ mkdir $bd query "select m.rowid,author,m.val from article a join article_m m\ on a.id=m.id where blogid=(select id from blog where rowid=$1)\ and m.key in ('image', 'document', 'binary');" \ | while IFS='|' read rowid author filename; do err isfilereadable $user article_m $rowid isfilereadable $user article_m $rowid || continue err ok i=$((i+1)) dir=`printf $bd/%03d $i` mkdir $dir query "select quote(bin) from article_m where rowid=$rowid;" \ | unhexize > $dir/$filename done if [ ! -d $bd/001 ]; then contenttype; echo echo "取得できるファイルがありませんでした。" | html p return fi (cd $bd ## err cdto$bd; (pwd; ls -lFa) 1>&3 tar zcf .archive.tar.gz * && mv .archive.tar.gz archive.tar.gz err Creating tar archive "`ls -l archive.tar.gz`" ) arc=$bd/archive.tar.gz echo "Content-type: application/x-gzip" echo "Content-Length: `cat $arc|wc -c`" echo "Content-Disposition: filename=\"archive.tar.gz\"" echo cat $arc } lsmyfile() { # $1(optional)=SortBy case "$1" in ""|CTIME-DESC) by="CTIME" ord="DESC" ;; CTIME*) by="CTIME" ;; FILE*) by="FILE" ;; OWNER*) by="OWNER" ;; TITLE*) by="TITLE" ;; esac case "$1" in *DESC) ord="DESC" ;; esac case "$ord" in DESC) lkod="" jord="降順" ;; *) lkod="-DESC" jord="昇順" ;; esac sql="select m.val||'/'||m.rowid FILE, coalesce( case when (select name from user where name=bs.owner) is not null then (select val from user_s where name=bs.owner and key='gecos') when (select gname from grp where gname=bs.owner) is not null then (select val from grp_s where gname=bs.owner and key='gecos') else null end, bs.owner ) OWNER, a_s.val CTIME, ',t,'||bs.title||':'||b.rowid||'#'||a.id TITLE from (select rowid,id,val from article_m where id in (select id from article where author='$user') and type like 'file:%') m left join article a on m.id=a.id left join article_s a_s on a.id=a_s.id and a_s.key='ctime' left join (select id, max(case key when 'owner' then val end) as owner, max(case key when 'title' then val end) as title from blog_s group by id) bs on a.blogid=bs.id left join blog b on bs.id=b.id where m.val is not null order by $by $ord;" err lshandoutbyauthor: sql=`echo "$sql"` title="個人提出ファイル" _m4 -D_TITLE_=$title $layout/html.m4.html hra="<a href=\"?lsmyfile+" hrb="<a href=\"?showattc+article_m+" hrc="<a href=\"?replyblog+" (echo '<table class="b">' echo "$sql"|sq -html -header $db ) \ | sed -e "s|\(<TR><TD>\)\([^/]*\)/\([0-9]*\)|\1$hrb\3\">\2</a>|" \ -e "s|,t,\(.*\):\([^<]*\)\(</TD>\)|$hrc\2\">\1</a>\3|" \ -e "s|\(<TH>\)\([A-Z]*\)\(</TH>\)|\1$hra\2$lkod\">\2</a>|" \ | _m4 -D_TITLE_=$title -D_FORM_="<p>($by$jord)</p>" \ -D_DUMPTABLE_="syscmd(cat)" $layout/form+dump.m4.html echo '</table>' } searchart() { kwd=`getpar kwd|nkf -wZ1` # Convert Zenkaku-SPC to ASCII-SPC kwdgrp="" authcond="" if [ -z "$kwd" ]; then echo "検索語を指定してください" | html p; return fi if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then # Like '#1234', assume as artID rowid=$((${kwd#\#} + 0)) # Force to be a number kc="ar.rowid = $rowid" else for k in `echo "$kwd" | sed "s/'/''/g"`; do # With wrap quotes if expr x"$k" : 'x@[><= ]*[1-9][][0-9]*-[][0-9:-]*$' >/dev/null >&2; then # '@<2016-10-10' -> ctime < '2016-10-10' # '@>=2016-10-10' -> ctime >= '2016-10-10' # '@2016-10-10' -> ctime GLOB '@2016-10-10' k=${k#@} case "$k" in [\<\>]*) op=${k%%[!<>=]*}; ctime=${k##*[><= ]} ;; *) op='GLOB'; ctime="${k##*[><= ]}*" ;; esac kc=$kc${kc:+" AND "}"ctime $op '${ctime}'" # Not sure GROUP BY a.blogid is comfortable for searchers...? ##### kwdgrp=" GROUP BY a.blogid" ## Add this to lessen results elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then ctime=`date +%F` kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'" elif [ x"$k" = x"@week" ]; then ctime=`query "SELECT datetime('now', 'localtime', '-7 days');"` kc=$kc${kc:+" AND "}"ctime > '${ctime}'" elif [ x"$k" = x"@month" ]; then ctime=`query "SELECT datetime('now', 'localtime', '-1 month');"` kc=$kc${kc:+" AND "}"ctime > '${ctime}'" elif [ x"$k" = x"@year" ]; then ctime=`query "SELECT datetime('now', 'localtime', '-1 year');"` kc=$kc${kc:+" AND "}"ctime > '${ctime}'" else kc=$kc${kc:+" AND "}"content LIKE '%$k%'" fi done fi kwd=`echo "$kwd"|htmlescape` owner=`getpar owner` owner=${owner:-$1} echo "「$kwd」による検索結果" | html p if [ -n "$owner" ]; then cond="where key='owner' and val='$owner'" if isuser $owner; then echo "(`linkhome $owner` さんの記録からの検索)" | html p else linkhome $owner 1>&3 echo "(`linkhome $owner` グループからの検索)" | html p fi elif { author=`getpar author`; test -n "$author"; }; then atptn=`sqlquotestr $author` #kc="$kc${kc:+ AND }author=$atptn" authcond="WHERE author=$atptn" if isuser $author; then echo "(`linkhome $author` さんの書き込みからの検索)" | html p fi fi # article_s: id=article-id, key='text', val='TEXT' # article: id=article-id, blogid=blogkd # blog: id=blog-id, author=LeaderAuthor # blog_s: id=blog-id, key='title', val='BLOG-TITLE' # WANT: blog-ROWid,article-id,val(TEXT) sql2="`sql4readableblogs` -- Extract user-readable blogs -- 0.3sec WITH artsm AS ( SELECT a.id,ctime, text || ' ' || coalesce(files, '') content FROM article a LEFT JOIN (SELECT ars.id, ctime, text, coalesce(files, '') files FROM (SELECT id, max(CASE key WHEN 'ctime' THEN val END) ctime, max(CASE key WHEN 'text' THEN val END) text FROM article_s GROUP BY id) ars LEFT JOIN (SELECT id, group_concat(val) files FROM article_m WHERE type LIKE 'file:%' GROUP BY id) arm ON ars.id=arm.id ) ar ON a.id=ar.id ), ar AS ( SELECT a.rowid, a.blogid, a.id, a.author, ctime, content FROM article a JOIN artsm ON a.id=artsm.id $authcond ), bl AS ( SELECT blg.rid, blg.*, blog_s.val TITLE FROM readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title' ) SELECT bl.rid||'#'||ar.id '', bl.title TITLE, (SELECT gecos FROM gecoses WHERE name=ar.author) AUTHOR, substr(ctime, 0, 11) DATE, substr(content, 0, 78) TEXT FROM ar JOIN bl ON ar.blogid=bl.id WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond) ORDER by DATE DESC, TITLE, ctime;" sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\ href=\"?replyblog+\1\">VIEW</a></TD>," # echo "$sql2" > tmp/sql.out result=$tmpd/result.$$ cat<<EOF <table class="b searchart"> `sq -header -html $db "$sql2"|sed "$sedopt"|tee $result` </table> EOF if [ -s "$result" ]; then found=$((`grep "^<TR><TD>" $result | wc -l` + 0)) # Cast to INT one=${found%1} echo "$found match${one:+es} found" else echo orz... fi } listblog() ( # $1={user,group} qow=`sqlquote $1` cond="where a.id in (select id from blog_s where key='owner' and val=$qow) order by ctime desc" DT_CHLD=article:blogid cgi_form searchart<<EOF <label>`cgi_text kwd`という語を含む記事をこの一覧から検索</label> `cgi_hidden owner $user` EOF dumptable html blog 'ctime title heading' "$cond" ) blog_addentry() { # $1=GRPname(if it is a group) grprowid=$1 rowid=`getpar rowid` ## err blog_addentry0: rowid=$rowid if [ -n "$grprowid" ]; then owner=`getgroupbyid $grprowid` else owner=`getpar owner` fi err blog-add: \$1=$1 rowid=$rowid owner=$owner if isgroup "$owner"; then groupmode=1 listing=$owner guide="[${owner}]" GF_OWNER=$owner else usermode=1 listing=$user guide="[個人]" fi if [ -n "`getpar title`" ]; then if [ "$usermode" ]; then err usermode: user=$user owner=$owner if [ x"$user" != x"$owner" ]; then echo "他人の日記は書けません" | html p return 2 fi elif [ "$groupmode" ]; then # if write to group log grp=$owner #\`getpar grp\` err ismember: $user $grp if ! ismember "$user" "$grp"; then echo "(話題作成はこのグループに加入してから)" | html p return 3 fi fi par2table $formdir/blog.def serial=`getpar serial` ## err SERIAL: $serial ROWID=$rowid listing=$listing id="" if [ -n "$rowid" ]; then # Here, id becomes NULL when removal of entries at par2table id=`query "select rowid from blog where rowid=$rowid;"` elif [ -n "$serial" ]; then # If new blog leader created, traverse to its head. id=`query "select rowid from blog where id='$serial';"` ## err new-Leader: "select rowid from blog where id='$serial';" id=$id fi if [ -n "$id" ]; then ## If new aritcle is entered, JUMP to blog_reply blog_reply $id return fi fi echo "${guide}新規話題作成" > $tmpd/title.$$ listblog $listing > $tmpd/listblog.$$ genform $formdir/blog.def \ | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \ -D_FORMHEAD_="序文は簡単に詳しくはコメントに" \ -D_DUMPHEAD_="これまでの蓄積" \ -D_FORM_="syscmd(\`cat')" \ -D_DUMPTABLE_="spaste(\`$tmpd/listblog.$$')" \ $layout/html.m4.html \ $layout/form+dump-whead.m4.html } blog_reply() { # Posting to blog article rowid=$1 if [ -z "$rowid" ]; then echo "表示する日記番号が未指定です。" | html p return fi title=`getvalbyid blog title $rowid` owner=`getvalbyid blog owner $rowid` if [ -z "$title" ]; then echo "日記番号指定が無効です。" | html p return fi blog_writable $rowid $user; rc=$? if [ $rc = 0 ]; then iswritable=true else iswritable=false if [ $((rc & $BLOG_FROZEN)) -gt 0 ]; then isfrozen=true frozen_class='frozen"' frozen_flag=$FROZEN_TAG fi fi if isuser "$owner"; then subtitle="`gecos $owner` さんの話題" else grprowid=`query "select rowid from grp where gname=\"$owner\";"` subtitle="グループ <a href=\"?grp+$grprowid\" accesskey=\"h\" title=\"H\">$owner</a> での話題 `query \"SELECT printf('(チーム:%s)', val)\ FROM blog_s WHERE id=(SELECT id FROM blog WHERE rowid=$rowid) AND key='team'; \"|htmlescape`" memclass=`grp_getbodyclass "$owner"` fi text=`getpar text` if [ -n "$text" ]; then if $iswritable; then par2table $formdir/article.def st=$? case $st in 0|4) [ "$st" = "4" ] && act="書込削除" blog_notify_reply $rowid $user "$text" $act if [ -n "$grprowid" ]; then qgrp=$(sqlquote "$owner") dbsetbyid grp $owner wtime "`date '+%F %T'`" fi ;; esac else if $isfrozen; then title="$title(凍結板につき書き込み不可)" else title="$title(加入してないので書き込み不可)" fi fi fi def=$formdir/article.def echo "$title" > $tmpd/title.$$ echo "$subtitle$frozen_flag" > $tmpd/subtitle.$$ ${BLOG_SHOW:-blog_showentry} blog $rowid \ | _m4 -D_TITLE_="spaste(\`$tmpd/title.$$')" \ -D_BODYCLASS_=general"${memclass:+ $memclass}" \ -D_FORMHEAD_="spaste(\`$tmpd/subtitle.$$')" \ -D_FORM_='' \ -D_DUMPTABLE_="syscmd(cat)" -D_DUMPHEAD_="" \ $layout/html.m4.html $layout/form+dump-whead.m4.html } blog_reply_article() { # Direct link to article in some blog arid=${1:-0} # Already sanitized to digits brid=`query "SELECT rowid FROM blog WHERE \ id=(SELECT blogid FROM article WHERE rowid=$arid);"` if [ -n "$brid" ]; then newurl="?replyblog+$brid#$arid" echo "Refresh: 0; $newurl"; echo exit 0 else contenttype; echo echo "無効な記事番号です." | html p fi }