package SPYDB;
# By Meryll Larkin written January 2004
# Last update February 2006
# A custom module to be used with spy.cgi and stats.cgi
# To update current visitor Stats to the database
# To display a range of records to the stats.cgi page
use strict;
use DBI;
my $DB = '_db';
sub new {
my $class = shift;
my $self = {};
bless $self, $class;
$self-> _init;
return $self; # this is really database handle;
}
sub _init {
my $self = shift;
my $DATA_BASE = 'dbi:mysql:alwanza_site';
my $password = do 'pw.cgi';
my $user = do 'user.cgi';
my $dbh = DBI->connect ($DATA_BASE, $user, $password) ||
$self->_error ("can\'t connect to database: $!" . $DATA_BASE->errstr);
$self->{$DB} = $dbh;
return $self->{$DB};
}
sub set_visitor {
my ($self, @keydata) = @_;
my $i;
my $sql = qq|insert into all_stat (|;
for ($i = 0; $i <= $#keydata; $i+=2) {
$sql .= $keydata[$i] . ",";
}
chop $sql; # remove final comma
$sql .= ") values (";
for ($i = 1; $i <= ($#keydata+1); $i+=2) {
$sql .= '"' . $keydata[$i] . '",';
}
chop $sql; # remove final comma
$sql .= ")\;";
my $dbh = $self->{$DB};
my $sql_st = $dbh->prepare($sql) || $self->_error ("can\'t prepare $sql");
$sql_st->execute || $self->_error ("can\'t execute
" . $sql_st->errstr);
}
sub get_visitors {
my ($self, $old_order, $begin_range, $end_range) = @_;
my $selected_order = $old_order;
if ($old_order =~ /ASC$/) {
$selected_order =~ s/ASC/DESC/;
} elsif ($old_order =~ /DESC$/) {
$selected_order =~ s/DESC/ASC/;
}
if ( ($selected_order !~ /ASC\b$/) &&
($selected_order !~ /DESC\b$/)) {
$selected_order .= " DESC";
}
my ($record, $count, $user, $port, $wk_day, $day, $time,
$host, $platform, $url, $who, $cookie);
my $sql = qq|select * from all_stat where COUNT>$begin_range order by $selected_order\;|;
my $dbh = $self->{$DB};
my $sql_st = $dbh->prepare($sql) || $self->_error ("can\'t prepare $sql");
$sql_st->execute || $self->_error ("can\'t execute
" . $sql_st->errstr);
my $displayDay;
my $displayRecord;
my @displayRecord;
my @displayRecords;
while ($record = $sql_st->fetchrow_hashref){
$count = $record->{COUNT};
$user = $record->{REMOTE_ADDR};
next if ($user !~ /\w/);
$port = $record->{REMOTE_PORT};
$wk_day = $record->{WK_DAY};
$day = $record->{DAY};
next if ($day !~ /\w/);
$time = $record->{TIME};
$host = $record->{HTTP_HOST};
$platform = $record->{HTTP_USER_AGENT};
next if ($platform !~ /\w/);
$url = $record->{HTTP_REFERER};
$who = $record->{WHO};
$who = "\ \;" if (not defined $who);
$cookie = $record->{COOKIE};
$displayDay = "$wk_day $day $time";
$displayRecord = [$count,$displayDay,$user,$platform,$who,$cookie];
push (@displayRecords, $displayRecord);
}
return @displayRecords;
}
sub _error {
my $self = shift;
my $errorstring = shift;
$self->{_errorstring} = $errorstring;
return $self->{_errorstring};
}
1