#!/usr/local/bin/perl
#------------------------------------------------------
# MixiLogLoader Ver.0.03
# Copyright (C) DonaDona(KITAO Masato) 2004
#------------------------------------------------------
use strict;
use DBI;
use Jcode;
use Getopt::Std;
require WWW::Mixi;
# データベースファイル名
my $dbFile = 'mixidb.dat';
# Command Line Optionsの取得
getopts('u:p:anHSD');
my $loginUser = $Getopt::Std::opt_u;
my $loginPassword = $Getopt::Std::opt_p;
my $mode = {
NoAccess => $Getopt::Std::opt_n,
Analyse => $Getopt::Std::opt_a,
ShiftJis => $Getopt::Std::opt_S,
HTMLOutput => $Getopt::Std::opt_H,
Debug => $Getopt::Std::opt_D,
};
if(!$mode->{NoAccess}){
if($loginUser eq '' || $loginPassword eq ''){
die &showHelp();
}
}
# データベースファイルがなければ作るの。
if(!-f $dbFile){
&createDB($dbFile);
}
# アクセスしないモードでなければログ作成するの。
if(!$mode->{NoAccess}){
&updateDB($dbFile, $loginUser, $loginPassword);
}
# ログ解析
if($mode->{Analyse}){
&showAnalyse($dbFile, $mode->{ShiftJis}, $mode->{HTMLOutput});
}
exit(0);
# ヘルプの表示
sub showHelp(){
<< "EOD";
MixiLogLoader Copyright (C) DonaDona(KITAO Masato) 2004
Usage: mixilog.pl -u user\@mixi.jp -p password [-anHSD]
[Required Options]
-u: User's Mail Address
-p: User's Password
[Options]
-n: NoAccess Mode
-a: Analyse Mode
-H: HTML Output Mode
-S: ShiftJIS Mode (on Analyse Mode)
-D: Debug Mode
EOD
}
# データベースの存在を確認し、なければ新規で作成する
sub createDB(){
# パラメータの取得
my ($dbFileName) = @_;
if(!-f $dbFileName){
my $dbh = DBI->connect("dbi:SQLite:dbname=${dbFileName}","","");
my $sth = $dbh->prepare(
"CREATE TABLE mixilog(logdate,logtime,userid, username)"
);
$sth->execute();
$dbh->disconnect;
if($mode->{Debug}){
print qq([CreateDB] File\'${dbFileName}\'is created.\n);
}
}
}
# show_log.plにアクセスし、データベースを更新する
sub updateDB(){
# パラメータの取得
my ($dbFileName, $user, $password) = @_;
my $dbh = DBI->connect("dbi:SQLite:dbname=${dbFile}","","");
my $mixi = WWW::Mixi->new($user, $password);
$mixi->login;
my @items = $mixi->get_show_log();
my ($updateCount, $insertCount) = (0, 0);
foreach my $item(@items){
my ($logDate, $logTime) = split(' ', $item->{time});
my $userId = '';
if($item->{link} =~ /\?id=([0-9]+)/){
$userId = $1;
}
my $userName = $item->{name};
my @count = $dbh->selectrow_array(
"SELECT count(*) FROM mixilog WHERE logDate='${logDate}' and userid='${userId}'"
);
if($count[0] == 1){
my $sth = $dbh->prepare(
"UPDATE mixilog SET logtime = ? ,username = ? WHERE logDate=? and userid=?"
);
$sth->execute($logTime, $userName, $logDate, $userId);
$updateCount++;
}
else{
my $sth = $dbh->prepare(
"INSERT INTO mixilog(logdate,logtime,userid, username) VALUES(?,?,?,?)"
);
$sth->execute($logDate,$logTime,$userId,$userName);
$insertCount++;
}
}
$dbh->disconnect;
if($mode->{Debug}){
print qq([UpdateDB] New Log: ${insertCount}, Update Log: ${updateCount}\n);
}
}
# ログ解析した結果を表示する
sub showAnalyse(){
# パラメータの取得
my ($dbFileName, $sjisMode, $htmlMode) = @_;
my $dbh = DBI->connect("dbi:SQLite:dbname=${dbFile}","","");
my $sth = $dbh->prepare(&getLogAnalyseSQL());
my @row;
$sth->execute();
if($htmlMode){
my $charset = ($sjisMode ? 'Shift_JIS' : 'EUC-JP');
print qq(\n);
print qq(\n);
print qq(mixi Log Loader Result\n);
print qq(\n);
print qq();
print qq(
mixi Log Loader Result
\n);
print qq(\n);
print qq();
print qq(\n);
print qq(\n);
print qq(\n);
}
else{
print "----------------------------------------------------------------------\n";
print "Count: NickName : Graph\n";
print "----------------------------------------------------------------------\n";
while(@row = $sth->fetchrow_array) {
my ($userName, $userId, $accessCount, $latestDate, $latestTime, $firstDate) = @row;
printf ("%05d: ", $accessCount);
printf ("%-20s: ", ($sjisMode ? Jcode::convert($userName,'sjis','euc') : $userName));
for(my $i = 0; $i < ($accessCount / 10 - 1); $i++){
print ("+");
}
for(my $i = 0; $i < ($accessCount % 10); $i++){
print ("-");
}
print ("\n");
}
print "----------------------------------------------------------------------\n";
}
$dbh->disconnect;
}
# ログを解析する
sub getLogAnalyseSQL(){
<< "EOS";
SELECT DISTINCT
B.userName,
A.userId,
A.accessCount,
A.latestDate,
B.logTime latestTime,
A.firstDate
FROM (
SELECT t1.userid userId,
count(t1.userid) accessCount,
min(t1.logDate) firstDate,
max(t1.logDate) latestDate
FROM mixilog t1
GROUP BY userid
) A,
mixilog B
WHERE A.userId = B.userId
AND A.latestDate = B.logDate
ORDER BY A.accessCount DESC, A.latestDate DESC, A.firstDate DESC
EOS
}
