人気ランキング ブロック

create table node_ranking_new (
nid integer primary key,
path varchar(50),
title text,
count integer,
rank integer
) ENGINE=InnoDB;

create table node_ranking_old (
nid integer primary key,
path varchar(50),
title text,
count integer,
rank integer
) ENGINE=InnoDB;

<?xml version="1.0" encoding="UTF-8" ?>
<config>
    <drupal>
        <database>drupaldb</database>
        <username>fooqux</username>
        <password>abracadabra</password>
    </drupal>
</config>

#!/usr/bin/ruby -Ku

require 'mysql'
require 'rexml/document'

#-------------------------------------------------------------------------------

limit    = 20                       # TOP 20
interval = 7                        # 過去何日分を集計する？
node     = '^node/[[:digit:]]+$'    # 集計するノードのパターン（正規表現）

filter = [                          # 集計から除外するIPアドレス（"'と'"で囲む）
    "'192.168.100.200'",
    "'192.168.100.201'",
    "'192.168.100.202'"
]

#-------------------------------------------------------------------------------

summary = [
    "set character set utf8",

    "start transaction",

    "delete from node_ranking_old",

    "insert into node_ranking_old select * from node_ranking_new",

    "delete from node_ranking_new",

    "set @rank=0",

    "insert into node_ranking_new (nid, path, title, count, rank)
     select tmp2.nid, tmp2.path, node.title, tmp2.count, tmp2.rank from node,
     (select replace(path, 'node/', '') nid, path, count(*) count, (@rank:=@rank+1) rank from
     (select path from accesslog
     where hostname not in (#{filter.join(",")})
     and path regexp '#{node}'
     and timestamp between unix_timestamp(subdate(curdate(), #{interval})) and  unix_timestamp(curdate())
     group by path,hostname) as tmp1
     group by path
     order by count desc
     limit 0, #{limit}) as tmp2
     where node.nid = tmp2.nid",

    "commit"
]

#-------------------------------------------------------------------------------

sio = open(File.dirname(File.expand_path(__FILE__)) + '/../config.xml')
doc = REXML::Document.new(sio.readlines.to_s)
cfg = doc.elements['config/drupal']

database = cfg.elements['database'].text
username = cfg.elements['username'].text
password = cfg.elements['password'].text

mysql = Mysql::new('localhost', username, password, database)
summary.each { |sql|
    mysql.query(sql)
}

#node_ranking td.rank {                 /* 順位 */
    text-align: right;
    white-space: nowrap;
}

#node_ranking td.fluct {                /* 変動 */
    padding: 0px 5px;
    text-align: center;
}

#node_ranking td.entry {                /* 記事 */
    padding-bottom: 3px;
    text-align: left;
}

#node_ranking .notdown {
    color: darkorange;
}

#node_ranking .down {
    color: cornflowerblue;
}

<?php

global $base_url;
global $active_db;

$sql =<<< SQL
    select new.*,
        case when new.rank = old.rank then "→"
             when new.rank < old.rank then "↑"
             when new.rank > old.rank then "↓"
             when old.rank is null then "↑"
    end fluctuate
    from node_ranking_new new left outer join node_ranking_old old
    on new.path = old.path
    order by new.rank;
SQL;

$result = mysql_query($sql, $active_db);

echo '<table id="node_ranking">';
while ($row = mysql_fetch_assoc($result)) {
    $color = $row['fluctuate'] == "↓" ? "down": "notdown";
    $title = htmlentities($row['title'], ENT_QUOTES, 'UTF-8');
echo <<< RANKING
    <tr>
        <td class="rank">${row['rank']}.</td>
        <td class="fluct ${color}">${row['fluctuate']}</td>
        <td class="entry"><a href="${base_url}/${row['path']}">${title}</a></td>
    </tr>
RANKING;
}
echo '</table>';

?>