#!/usr/bin/perl -w
=head1 AUTHOR
me
=head1 DATE
10/04/2001
=head1 report.pl
reporting script
=cut
use strict;
use CGI;
use DBI;
my $dbh = DBI->connect('dbi:mysql:cheese', 'user', 'password', {RaiseError => 1 });
#---- Initialise variables and semi-constants
my $q = new CGI;
my $rep = $q->param('rep') || '';
my @shared = ('id', 'name', 'email');
my %sql = (
'new_rooms' => [ qq% select COLS from rooms, users where users.id = rooms.users_id %,
[@shared, 'title', 'description'],
'Report of room suggestions'
],
'winners' => [ qq% select COLS from users where is_winner = 'Y' %,
\@shared,
'Report of competition winners'
],
'all_users' => [ qq% select COLS from users %,
\@shared,
'Report of all users (winners or not)'
],
'opted_in' => [ qq% select COLS from users where optin = 'Y' %,
\@shared,
'Report of all users that have opted in for mail'
],
);
if (exists $sql{$rep}) {
do_report($sql{$rep});
} else {
show_buttons();
}
$dbh->disconnect;
exit;
=head1 do_report
parameters - reference to query and column array
returns - nowt
performs - sql query and formats output
=cut
sub do_report {
my $ref = shift;
my $f = 0;
my $cols = join ',', @{ $ref->[1] };
my $sql = $ref->[0];
$sql =~ s/COLS/$cols/;
my @tmp = map { "| $_ | " } @{ $ref->[1] };
my $html = $q->header;
$html .= qq( $ref->[2]
$ref->[2]
\n
\n@tmp
\n
);
my $sth = $dbh->prepare($sql);
$sth->execute;
while (my $ref = $sth->fetchrow_arrayref) {
$f = 1;
my @ary = map { "$_ | " } @$ref;
$html .= "@ary
\n";
}
if (!$f) {
$html .= '| NO ROWS FOUND |
';
}
$html .= '
';
$sth->finish;
print $html;
}
=head1 show_buttons
parameters - none
returns - nothing
performs - output of request page
=cut
sub show_buttons {
my $html = $q->header;
$html .= qq(
Cheese Game Stats
Report Options
);
print $html;
}