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