1.1 --- a/s4-funcs.sh	Fri Feb 19 18:03:37 2016 +0859
     1.2 +++ b/s4-funcs.sh	Fri Feb 19 22:00:01 2016 +0859
     1.3 @@ -1342,6 +1342,49 @@
     1.4    echo "グループ $grp"|m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html
     1.5    showgroup "$grid"
     1.6  }
     1.7 +listnewblogsql() { # $1=user
     1.8 +  cat<<EOF
     1.9 +with article_ctime as (
    1.10 +  select id,blogid,author,val ctime
    1.11 +  from article join article_s s using(id)
    1.12 +  where s.key='ctime'
    1.13 +), blog_title_owner as (
    1.14 +  select blog.rowid, id,
    1.15 +        max(case key when 'title' then val end) title,
    1.16 +        max(case key when 'owner' then val end) owner
    1.17 + from blog, blog_s using(id) group by id
    1.18 +), acclog_user as (
    1.19 +  select * from acclog where user='$user'
    1.20 +), blogall as (
    1.21 + select b.id, b.rowid, b.owner, b.title, ctime, ac.author
    1.22 + from blog_title_owner b, article_ctime ac
    1.23 +      on b.id=ac.blogid
    1.24 +)
    1.25 + select rowid ID, 
    1.26 +        sum(case when l.time is null then 1
    1.27 +                 when l.time < ctime then 1
    1.28 +                 else 0
    1.29 +            end) "新着",
    1.30 + count(id) "総数", ctime, title,
    1.31 + owner gecos -- (select gecos from gecoses where name=bl.owner) gecos
    1.32 + from blogall bl left join acclog_user l
    1.33 +      on bl.rowid=l.tblrowid
    1.34 + where case when (select name from user where owner=name) is not null
    1.35 +            then 1 /* user's diary is always readable */
    1.36 +            when (select val from grp_s
    1.37 +                  where gname=owner and key='regmode') = 'moderated'
    1.38 +                 and -- moderated group and nonmember can't read, not counting
    1.39 +                 (select user from grp_mem
    1.40 +                  where gname=owner and user='$user') is null
    1.41 +            then 0
    1.42 +            else 1
    1.43 +       end
    1.44 + group by bl.id
    1.45 + having "新着" > 0 order by "新着" desc, bl.id
    1.46 + limit 10;
    1.47 +EOF
    1.48 +}
    1.49 +
    1.50  showhome() {
    1.51    # $1=userRowIdToShow
    1.52    err showhome \$1=$1
    1.53 @@ -1399,9 +1442,15 @@
    1.54    
    1.55    if [ x"$user" = x"$uname" ]; then
    1.56      # Display NEWS
    1.57 -    cond="where 新着 > 0 order by 新着 desc,ctime desc limit 10"
    1.58 -    new10=`DT_CHLD=article:blogid \
    1.59 +    if false; then
    1.60 +      cond="where 新着 > 0 order by 新着 desc,ctime desc limit 10"
    1.61 +      new10=`DT_CHLD=article:blogid \
    1.62  	  DT_VIEW=replyblog dumptable html blog "ctime title gecos" "$cond"`
    1.63 +    else
    1.64 +      # 2016-02-19 Counting NEWS without using dumptable.
    1.65 +      sql=`listnewblogsql "$user"`
    1.66 +      new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`
    1.67 +    fi
    1.68      cont=`echo "$new10"|grep "^<TR>"|wc -l`
    1.69      cont=$((cont-1))
    1.70      err newcount=$cont
    1.71 @@ -2128,7 +2177,7 @@
    1.72      scols="$scols${scols:+, }b.$as"
    1.73    done
    1.74  #case author when '$user' then a.rowid else '---' end as ID,
    1.75 -  sql="select \
    1.76 +  sql=${DT_SQL:-"select \
    1.77  a.rowid as ID,\
    1.78  $cnt\
    1.79  $scols from $2 a left join\
    1.80 @@ -2136,8 +2185,8 @@
    1.81  	max(case key when 'owner'
    1.82  	 then coalesce((select val from user_s us
    1.83  		 where us.key='gecos' and us.name=c.val), val) end) as gecos
    1.84 - from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"
    1.85 -err SQL=`echo "$sql"`
    1.86 + from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}
    1.87 +  err SQL=`echo "$sql"`
    1.88    cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)</TD>,\1$elink$dvlink</TD>,"
    1.89  <div class="dumptable">
    1.90  <table class="b$dt_class">