#!/usr/bin/perl

use DBI;

require "/services/webpages/f/r/friendsofclearlake.com/cgi-bin/settings.pl";

&loadCookie;

$sectionTitle = "Welcome back";
$sectionDesc  = qq~
                <p>
                You are logged in as 
                ~;
$dbh          = DBI->connect($dsn, $user, $password);

if ($dbh) {
    &success;
    $dbh->disconnect;
    exit;
}
else {
    &unSuccess;
    exit;
}


sub success {
    if ($loggedIn) {
    print "Content-type: text/html\n\n";
    my $loginQuery = qq~SELECT * FROM userinfo where lower(email) = '$cuname' AND id = '$cuid'~;
    my $sth = $dbh->prepare_cached($loginQuery) || die "Cannot prepare query: $dbh->errstr";
    $sth->execute();
    while (my $row = $sth->fetchrow_hashref()) {
        $rid   = $row->{id};
        $fname = " $row->{fname}";
    }
    $sth->finish;
    $sectionTitle .= "$fname,";
    $sectionDesc  .= "$cuname.</p>";
    &pageHeader; 
    #SENATORS
    print qq~<p><b>Senator(s):</b></p>~;
    $senQuery = qq~
                Select
                    senator.fname,
                    senator.lname,
                    senator.district,
                    senator.party,
                    senator.home_county
                From
                    userinfo
                Inner Join user_has_sen ON userinfo.id = user_has_sen.user_id
                Inner Join senator ON user_has_sen.sen_id = senator.sen_id
                Where
                    userinfo.id = '$rid'
                ~;
    $bgcount = 0;
    my $sth = $dbh->prepare_cached($senQuery) || die "Cannot prepare query: $dbh->errstr";
    $sth->execute();
    $countSen = $sth->rows;
    if ($sth->rows > 0) {
        print qq~
        <p>You have selected $countSen Senator(s).</p>
        <table cellpadding="2" cellspacing="1" border="0" width="100%">
                <tr bgcolor="#999999">
                    <td valign="top" align="left" bgcolor="#ffffff">&nbsp;</td>
                    <td valign="top" align="left"><b>Name</b></td>
                    <td valign="top" align="middle"><b>District #</b></td>
                    <td valign="top" align="left"><b>Home County</b></td>
                    <td valign="top" align="middle"><b>Party</b></td>
                </tr>
        ~;
        
        while (my $sen = $sth->fetchrow_hashref()) {
            $sens++;
            $icon = lc($sen->{party});
            if (!$bgcount) {
                $bgcolor = qq~ bgcolor="#ffffff"~;
                $bgcount = 1;
            } else {
                $bgcolor = qq~ bgcolor="#eeeeee"~;
                $bgcount = 0;            
            }
            print qq~
                <tr$bgcolor>
                    <td valign="top" align="left">$sens</td>
                    <td valign="top" align="left">$sen->{fname} $sen->{lname}</td>
                    <td valign="top" align="middle">$sen->{district}</td>
                    <td valign="top" align="left">$sen->{home_county}</td>
                    <td valign="top" align="middle"><img src="/images/$icon~.qq~_logo.gif" width="20" height="20" alt="$sen->{party}"></td>
                </tr>~;
        }
        print "</table>";
    }
    else {
        print qq~You have not selected any Senator(s).</p>~;
    }
    $sth->finish;
    #REPRESENTATIVES 
    print qq~<p><b>Representative(s):</b></p>~;
    $repQuery = qq~
                Select
                    representative.fname,
                    representative.lname,
                    representative.district,
                    representative.party,
                    representative.home_county
                From
                    user_has_rep
                Inner Join representative ON user_has_rep.rep_id = representative.rep_id
                Inner Join userinfo ON user_has_rep.user_id = userinfo.id
                Where
                    userinfo.id = '$rid'
                ~;
    $bgcount = 0;
    my $sth = $dbh->prepare_cached($repQuery) || die "Cannot prepare query: $dbh->errstr";
    $sth->execute();
    $countRep = $sth->rows;
    if ($countRep > 0) {
        print qq~
        <p>You have selected $countRep Representative(s).</p>
        <table cellpadding="2" cellspacing="1" border="0" width="100%">
                <tr bgcolor="#999999">
                    <td valign="top" align="left" bgcolor="#ffffff">&nbsp;</td>
                    <td valign="top" align="left"><b>Name</b></td>
                    <td valign="top" align="middle"><b>District #</b></td>
                    <td valign="top" align="left"><b>Home County</b></td>
                    <td valign="top" align="middle"><b>Party</b></td>
                </tr>
        ~;
        while (my $rep = $sth->fetchrow_hashref()) {
            $reps++;
            $icon = lc($rep->{party});
            if (!$bgcount) {
                $bgcolor = qq~ bgcolor="#ffffff"~;
                $bgcount = 1;
            } else {
                $bgcolor = qq~ bgcolor="#eeeeee"~;
                $bgcount = 0;            
            }
            print qq~
                <tr$bgcolor>
                    <td valign="top" align="left">$reps</td>
                    <td valign="top" align="left">$rep->{fname} $rep->{lname}</td>
                    <td valign="top" align="middle">$rep->{district}</td>
                    <td valign="top" align="left">$rep->{home_county}</td>
                    <td valign="top" align="middle"><img src="/images/$icon~.qq~_logo.gif" width="20" height="20" alt="$rep->{party}"></td>
                </tr>~;
        }
        print "</table>";
    }
    else {
        print qq~You have not selected any Representative(s).</p>~;
    }
    $sth->finish;
    print qq~
    <p>&nbsp;</p>
    <p><a href="#top">Back to top</a>.</p>
    <p>&nbsp;</p>
    ~;
    &pageFooter;
    }
    else {
        print "Location: http://$hostName/login/\n\n";
    }
}

exit;