#!/usr/bin/perl -w

use DBI;
use CGI::Pretty qw( :html3 );
use CGI qw/:standard *table *Tr *td/;

# Variables

$user = 'user';
$passwd = 'password';
my $dbh = DBI->connect( 'DBI:mysql:Music;host=localhost',$user,$passwd ) || die "Could not connect to database:  $DBI::errstr";
if(param('LGIO') or param('LM')){&process;};

        print header;
        @tables = ('lgio','lm');
        foreach $tbl (@tables){
                @lartist = '';
                @ltitle = '';
                @lalbum = '';
                @lyear = '';
                @lgenre = '';
                if($tbl eq 'lgio'){
                        $header = "Let's Get It On\!";
                        $arr = 'lgio';
                }
                if($tbl eq 'lm'){
                        $header = "Love Making Music";
                }
                my $sth = $dbh->prepare("select * from $tbl order by votes desc limit 6");
                $sth->execute();
                $sth->bind_columns( undef, \$artist, \$title, \$album, \$year, \$genre, \$votes );
                print h4({-align=>center},"$header",
                start_table( {-cellspacing=>5, -border=>1}, start_Tr ),
                                th( [(''),b('1'), b('2'), b('3'), b('4'), b('5')] ));
                        while($sth->fetch()){
                                        @lartist = (@lartist,$artist);
                                        @ltitle = (@ltitle,$title);
                                        @lalbum = (@lalbum,$album);
                                        @lyear = (@lyear,$year);
                                        @lgenre = (@lgenre,$genre);
                        }
                        print Tr( td( ['Artist',$lartist[1],$lartist[2],$lartist[3],$lartist[4],$lartist[5]] ) ),
                                Tr( td( ['Title',$ltitle[1],$ltitle[2],$ltitle[3],$ltitle[4],$ltitle[5]] ) ),
                                Tr( td( ['Album',$lalbum[1],$lalbum[2],$lalbum[3],$lalbum[4],$lalbum[5]] ) ),
                                Tr( td( ['Year',$lyear[1],$lyear[2],$lyear[3],$lyear[4],$lyear[5]] ) ),
                                Tr( td( ['Genre',$lgenre[1],$lgenre[2],$lgenre[3],$lgenre[4],$lgenre[5]] ) ),
                end_table,
                hr;
        }


########################## FORM

        print start_form,
                h4({-align=>center},start_table(start_Tr),
                        td(['Artist','Title','Album','Year','Genre']),
                Tr,
                        td([textfield(-name=>'artist', -size=>10),textfield(-name=>'title'),textfield(-name=>'album', -size=>10),textfield(-name=>'year', -size=>5),textfield(-name=>'genre', -size=>7)]),
                Tr,
                        th([submit(-value=>'Makes Me "Anxious"',-name=>'LGIO'),submit(-value=>'Favorite Love Making Song',-name=>'LM'),submit(-value=>'Favorite Song',-name=>'FAV'),defaults('Clear It Out')]),
                end_table),
        end_form,
hr;


############################ PROCESS
sub process {
# Variables

$sartist = param('artist');
$stitle = param('title');
$salbum = param('album');
$syear = param('year');
$sgenre = param('genre');

if (param('LGIO')) {
        $table = 'lgio';
}
if (param('LM')) {
        $table = 'lm';
}

print header;
$sth = $dbh->prepare("select * from $table where artist = \"$sartist\" and title = \"$stitle\"");
$sth->execute;
my ($artist, $title, $album, $year, $genre, $votes);
$sth->bind_columns( undef, \$artist, \$title, \$album, \$year, \$genre, \$votes );
$sth->fetch();
if (!$votes or $votes =~ /null/i or $voted eq ''){ $votes=1;}
if (!$album){ $album = 'Unknown';}
if (!$genre){ $genre = 'Unknown';}
if (!$year){ $year = 2007;}
if (!$salbum){ $salbum = $album;}
if (!$sgenre){ $sgenre = $album;}
if (!$syear){ $syear = $year;}
$votes++;
$svotes = $votes;
#if(!$svotes){$svotes=1;}
$dbh->do("delete from $table where artist = '$sartist' and title = '$stitle'");
$dbh->do("insert into $table values ('$sartist','$stitle','$salbum',$syear,'$sgenre',$svotes)");
print "insert into $table values (\'$sartist\',\'$stitle\',\'$salbum\',$syear,\'$sgenre\',$svotes)\;\n";
$sth->finish();
$dbh->disconnect();
print redirect({-location=>'tmp.cgi'},-method=>'post');
}