#!/usr/bin/perl use vars qw($dsn $username $password $dbh $query); use vars qw($my_mon $my_year); use vars qw($title); use vars qw($min_time $max_time $min_mon $max_mon $min_year $max_year ); use CGI; use CGI::Carp qw(fatalsToBrowser); use DBI; use Time::Local; require "/etc/icelog.conf"; my $cgi = new CGI; ($query) = $cgi->keywords; $dbh = DBI->connect($dsn, $username, $password) or die "Can't connect to $dsn: ". $DBI::errstr; if ( $cgi->param('customer') ) { @customers = ( $cgi->param('customer') ); } else { #everybody my $sth = $dbh->prepare('select distinct customer from icelog') or die $dbh->errstr; $sth->execute or die $sth->errstr; @customers = map { $_->[0] } @{$sth->fetchall_arrayref}; $sth->finish; } ($my_mon,$my_year) = ($cgi->param('mon'), $cgi->param('year')); my $sth = $dbh->prepare('select min(start), max(start) from icelog') or die $dbh->errstr; $sth->execute or die $sth->errstr; ( $min_time, $max_time ) = @{$sth->fetchrow_arrayref}; $sth->finish; ( $min_mon, $min_year ) = (localtime($min_time))[4,5]; $min_mon++; $min_year+=1900; ( $max_mon, $max_year ) = (localtime($max_time))[4,5]; $max_mon++; $max_year+=1900; my $title = 'icecast log'; if ( $my_mon && $my_year ) { $title .= " $my_mon/$my_year"; } else { $title .= ' (all)'; } print $cgi->header, < icecast log

$title

END for ( my($mon,$year) = ($min_mon, $min_year); $year < $max_year || ( $year == $max_year && $mon <= $max_mon); do { $mon++; if ( $mon == 13 ) { $year++; $mon-=12; } } ) { $cgi->param('year', $year); $cgi->param('mon', $mon); print '$mon/$year | ); } $cgi->param('year', ''); $cgi->param('mon', ''); print 'all); $cgi->param('year', $my_year); $cgi->param('mon', $my_mon); print < END foreach my $customer ( @customers ) { my $liveminutes = &getminutes($customer, 'Y', $my_mon, $my_year); my $archminutes = &getminutes($customer, 'N', $my_mon, $my_year); my $totminutes = $liveminutes + $archminutes; $cgi->param('customer', $customer); my $self_url = $cgi->self_url; print qq(); } print < END sub getminutes { my($customer, $liveflag, $mon, $year) = @_; my $statement = 'select sum(seconds) from icelog where customer = ? and liveflag = ?'; if ( $mon && $year ) { my $start = timelocal(0,0,0,1,$mon-1,$year-1900); $mon++; if ( $mon == 13 ) { $year++; $mon-=12; } my $end = timelocal(0,0,0,1,$mon-1,$year-1900) - 1; $statement .= " and start >= $start and start <= $end"; } #warn $statement; my $sth = $dbh->prepare($statement) or die $dbh->errstr; $sth->execute($customer, $liveflag) or die $sth->errstr; my $seconds = $sth->fetchrow_arrayref->[0]; $sth->finish; sprintf("%.3f", $seconds / 60); }
Cust#Minutes (live)Minutes (archived)Minutes (total)
$customer$liveminutes$archminutes$totminutes