#!/usr/bin/perl -w

push (@INC,"/home/master/applications/mcpl/public_html/cgi-bin");
require ("cgi-lib.pl");
require "header_footer.lib";

&ReadParse(\%input);

use DBI();

# Connect to the database.
my $dbh =
DBI->connect("DBI:mysql:database=yrbook;mysql_socket=/var/lib/mysql/mysql.sock",
                       "root", "mobob26",
                       {'RaiseError' => 1});


# we never reach this point if we wanted a redirect
print &PrintHeader;

print "<html>
<head>
<title>Yearbook Search | Monroe County Public Library, Indiana</title>
<style type=\"text/css\">
body\{
font-size: 10pt;
font-family: Verdana, Tahoma, \"DejaVu Sans\", sans-serif;
padding:0;
margin:0;
\}

table \{
font-size: 10pt;
\}

a\{
color: \#006699;
\}

a:hover\{
color: \#666666;
\}

\#page-wrapper\{
width: 1000px;
margin-left: auto;
margin-right: auto;
background-color: \#FFF;
border-left: 1px solid \#777;
-webkit-box-shadow: 6px 6px 6px \#000;
-moz-box-shadow: 6px 6px 6px \#000;
box-shadow: 6px 6px 6px \#000;
\}

\#content\{
text-align: center;
\}

\#content a\{
font-weight: bold;
\}

table\#yrbk\{
width:95%;
        border-collapse: collapse;
        margin-left:auto;
margin-right:auto;
\}

table\#yrbk tr.odd\{
        background-color:\#FFF;
\}

table\#yrbk tr.even\{
        background-color:\#E6E6E6;
\}

table\#yrbk th\{
        background-color: \#CCC;
text-align: center;
        padding: 3px 0 3px 0;
\}

table\#yrbk th,
table\#yrbk td\{
        border: 1px solid \#333;
        font-size: 10px;
        padding:4px;
\}

</style>


<script type=\"text/javascript\">
// Popup window code
function newPopup(url) {
popupWindow = window.open(
url,'popUpWindow','height=350,width=375,right=10,top=10,resizable=yes,scrollbars=no,toolbar=no,menubar=no,location=no,directories=no,status=yes')
}
</script>";




#This finds the searched for yearbook data.

if ($input{'isdata'} || $input{'records'} =~ /^0/) {



# Now retrieve data from the table.
#The new SELECT statement grabs from three tables. See A SECTION of the corresponding .sql file
#-----------------------------BEGIN A SECTION
my $sthcount = $dbh->prepare("SELECT p.lastname, p.firstname, p.middlename, p.maidenname, pisy.status, s.name, s.yearbook, y.yearincluded, y.page, y.url FROM person p, school s, yearandpage y, personinschoolyearbook pisy WHERE p.id=pisy.personid AND s.id=y.schoolid AND y.yearandpageid=pisy.yearandpageid AND lastname LIKE '%$input{'lastname'}%' AND firstname LIKE '%$input{'firstname'}%' AND middlename LIKE '%$input{'middlename'}%' AND maidenname LIKE '%$input{'maidenname'}%' AND name LIKE '%$input{'name'}%' ORDER BY p.lastname, p.firstname, p.middlename, p.maidenname, s.name, y.yearincluded");
#-----------------------------END A SECTION

$sthcount->execute();

my $refcount = $sthcount->rows;

# no search was entered
unless (($input{'lastname'}) || ($input{'firstname'})
     || ($input{'middlename'}) || ($input{'maidenname'}) || ($input{'name'})) {
        print "<CENTER><B>You need to enter a search term. Please <a href=\"https://mcpl.info/indiana/yearbook-index-search\" target=\"_parent\">try again</a>.</B></CENTER>\n";
        exit 0;
    }

unless ($refcount) {
        # no queries matched
                print "<p align=\"center\"><b>No yearbook entries matched your search query.  Please try a variant spelling or a
                new search.</b></p>\n";

            } else {

    $pages = sprintf "%1d\n", (($refcount / 50) + 0.99999);

if ($pages > 1) {
    print  "Pages: ";

    foreach $pagecount (0..($pages - 1)) {

     $records = 50 * $pagecount;

     $pagedisplay = $pagecount + 1;
     $recordscount = "$records";
     $next = 50;
     if ($pagecount =~ '^0$') {
         print "$pagedisplay ";
     } else {

     print "<a href=\"https://www.monroe.lib.in.us/cgi-bin/yrbksearch?records=$recordscount&lastname=$input{'lastname'}&firstname=$input{'firstname'}&middlename=$input{'middlename'}&maidenname=$input{'maidenname'}&name=$input{'name'}\">$pagedisplay</a> ";
 }
}

     print "<a href=\"https://www.monroe.lib.in.us/cgi-bin/yrbksearch?records=$next&lastname=$input{'lastname'}&firstname=$input{'firstname'}&middlename=$input{'middlename'}&maidenname=$input{'maidenname'}&name=$input{'name'}\">next</a> ";

}
}
#This SELECT grabs from all three tables, and includes the LIMIT 0, 50 at the end that was in the original code. See B SECTION of the corresponding .sql file
#---------------------------------------------BEGIN B SECTION
my $sth = $dbh->prepare("SELECT p.lastname, p.firstname, p.middlename, p.maidenname, pisy.status, s.name, s.yearbook, y.yearincluded, y.page, y.url FROM person p, school s, yearandpage y, personinschoolyearbook pisy WHERE p.id=pisy.personid AND s.id=y.schoolid AND y.yearandpageid=pisy.yearandpageid AND lastname LIKE '%$input{'lastname'}%' AND firstname LIKE '%$input{'firstname'}%' AND middlename LIKE '%$input{'middlename'}%' AND maidenname LIKE '%$input{'maidenname'}%' AND name LIKE '%$input{'name'}%' ORDER BY p.lastname, p.firstname, p.middlename, p.maidenname, s.name, y.yearincluded LIMIT 0, 50");
#---------------------------------------------END B SECTION
$sth->execute();

my $ref = $sth->fetchrow_hashref();
# no search was entered
unless (($input{'lastname'}) || ($input{'firstname'})
     || ($input{'middlename'}) || ($input{'maidenname'}) || ($input{'name'})) {
        print "<CENTER><B>You need to enter a search term. Please go back and
                try again.</B></CENTER>\n";
       exit 0;
}


unless ($ref) {
        # no queries matched
                print "";
  } else {
        print "<p align=\"center\" >
                <span class=\"bold\">These entries match your search criteria.</span></p>
<p align=\"center\"><a href=\"https://mcpl.info/indiana/yearbook-index-search\" target=\"_parent\">Search Again</a></p>
                                       <TABLE BORDER=0 cellspacing=3 align=\"center\" id=\"yrbk\">
                        <TR>
                        <TH>Last name</TH>
                        <TH>First name</TH>
                        <TH>Middle name</TH>
                        <th>Maiden name</th>
                        <TH>Status<br /><a href=\"JavaScript:newPopup('https://mcpl.info/yearbookkey.html\');\">Key to Abbreviations</a></TH>

                        <TH>School</TH>
                        <TH>Yearbook</TH>
                        <TH>Year</TH>
                        <TH>Page</TH>
                        </TR>\n";


$i = 0; #initialize a loop counter in order to label table rows as even or odd
                    while($ref){
                        $i++;
                        if($i%2 == 1){
                            print "<TR class=\"odd\">";
                        } else {
                            print "<TR class = \"even\">";
                        }
#This if statement makes it so if the data entry has a URL, it will become a hyperlink on the entry's lastname value
                        if ($ref->{'url'}) {
                            print "
                            <TD ALIGN=center><a href='$ref->{'url'}' target='_blank'>$ref->{'lastname'}</a></TD>
                            <TD ALIGN=center>$ref->{'firstname'}</TD>
                            <TD ALIGN=center>$ref->{'middlename'}</TD>
                            <TD ALIGN=center>$ref->{'maidenname'}</TD>
                            <TD ALIGN=center>$ref->{'status'}</TD>
                            <TD ALIGN=center>$ref->{'name'}</TD>
                            <TD ALIGN=center>$ref->{'yearbook'}</TD>
                            <TD ALIGN=center>$ref->{'yearincluded'}</TD>
                            <TD ALIGN=center>$ref->{'page'}</TD>
                            </TR>\n";
                            $ref = $sth->fetchrow_hashref();
                        }
                        else { print "
                            <TD ALIGN=center>$ref->{'lastname'}</TD>
                            <TD ALIGN=center>$ref->{'firstname'}</TD>
                            <TD ALIGN=center>$ref->{'middlename'}</TD>
                            <TD ALIGN=center>$ref->{'maidenname'}</TD>
                            <TD ALIGN=center>$ref->{'status'}</TD>
                            <TD ALIGN=center>$ref->{'name'}</TD>
                            <TD ALIGN=center>$ref->{'yearbook'}</TD>
                            <TD ALIGN=center>$ref->{'yearincluded'}</TD>
                            <TD ALIGN=center>$ref->{'page'}</TD>
                            </TR>\n";
                            $ref = $sth->fetchrow_hashref();
                        }
                    }


$sth->finish();
}




                print"</TABLE></CENTER>\n";
}

if ($input{'records'}) {
#---------------------------------------BEGIN A SECTION
my $sthcount = $dbh->prepare("SELECT p.lastname, p.firstname, p.middlename, p.maidenname, pisy.status, s.name, s.yearbook, y.yearincluded, y.page, y.url FROM person p, school s, yearandpage y, personinschoolyearbook pisy WHERE p.id=pisy.personid AND s.id=y.schoolid AND y.yearandpageid=pisy.yearandpageid AND lastname LIKE '%$input{'lastname'}%' AND firstname LIKE '%$input{'firstname'}%' AND middlename LIKE '%$input{'middlename'}%' AND maidenname LIKE '%$input{'maidenname'}%' AND name LIKE '%$input{'name'}%' ORDER BY p.lastname, p.firstname, p.middlename, p.maidenname, s.name, y.yearincluded");
#---------------------------------------END A SECTION

$sthcount->execute();

my $refcount = $sthcount->rows;
  $pages = sprintf "%1d\n", (($refcount / 50) + 0.99999);
     $prev = $input{'records'} - 50;

print  "Pages: <a href=\"https://www.monroe.lib.in.us/cgi-bin/yrbksearch?records=$prev&lastname=$input{'lastname'}&firstname=$input{'firstname'}&middlename=$input{'middlename'}&maidenname=$input{'maidenname'}&name=$input{'name'}\">prev</a> ";

foreach $pagecount (0..($pages -1)) {
$records = 50 * $pagecount;
$pagedisplay = $pagecount + 1;
$recordscount = "$records";
$next = $input{'records'} + 50;

    if ($input{'records'} =~ "^$records\$") {
         print "$pagedisplay ";
     } else {

     print "<a href=\"https://www.monroe.lib.in.us/cgi-bin/yrbksearch?records=$recordscount&lastname=$input{'lastname'}&firstname=$input{'firstname'}&middlename=$input{'middlename'}&maidenname=$input{'maidenname'}&name=$input{'name'}\">$pagedisplay</a> ";
 }
 }

    if ($input{'records'} =~ "^$records\$") {
         print "";
     } else {

     print "<a href=\"https://www.monroe.lib.in.us/cgi-bin/yrbksearch?records=$next&lastname=$input{'lastname'}&firstname=$input{'firstname'}&middlename=$input{'middlename'}&maidenname=$input{'maidenname'}&name=$input{'name'}\">next</a> ";
}
#Similar to the other select statements, but includes the LIMIT $input{'records'}, 50 found in the original code. See C SECTION of the corresponding .sql file
#--------------------------------------------BEGIN C SECTION
my $sth = $dbh->prepare("SELECT p.lastname, p.firstname, p.middlename, p.maidenname, pisy.status, s.name, s.yearbook, y.yearincluded, y.page, y.url FROM person p, school s, yearandpage y, personinschoolyearbook pisy WHERE p.id=pisy.personid AND s.id=y.schoolid AND y.yearandpageid=pisy.yearandpageid AND lastname LIKE '%$input{'lastname'}%' AND firstname LIKE '%$input{'firstname'}%' AND middlename LIKE '%$input{'middlename'}%' AND maidenname LIKE '%$input{'maidenname'}%' AND name LIKE '%$input{'name'}%' ORDER BY p.lastname, p.firstname, p.middlename, p.maidenname, s.name, y.yearincluded LIMIT $input{'records'}, 50");
#--------------------------------------------END C SECTION
$sth->execute();

my $ref = $sth->fetchrow_hashref();

        print "<p align=\"center\" >
                <span class=\"bold\">These entries match your search criteria.</span></p>
<p align=\"center\"><a href=\"https://mcpl.info/indiana/yearbook-index-search\" target=\"_parent\">Search Again</a></p>
                        <TABLE BORDER=0 cellspacing=3 align=\"center\" id=\"yrbk\">

                        <TR>
                        <TH>Last name</TH>
                        <TH>First name</TH>
                        <TH>Middle name</TH>
                        <th>Maiden name</th>
                        <TH>Status</TH>
                        <TH>School</TH>
                        <TH>Yearbook</TH>
                        <TH>Year</TH>
                        <TH>Page</TH>
                        </TR>\n";



$i = 0; #initialize a loop counter in order to label table rows as even or odd
                    while($ref){
                        $i++;
                        if($i%2 == 1){
                            print "<TR class=\"odd\">";
                        } else {
                            print "<TR class = \"even\">";
                        }
#This if statement makes it so if the data entry has a URL, it will become a hyperlink on the entry's lastname value
                        if ($ref->{'url'}) {
                            print "
                            <TD ALIGN=center><a href='$ref->{'url'}' target='_blank'>$ref->{'lastname'}</a></TD>
                            <TD ALIGN=center>$ref->{'firstname'}</TD>
                            <TD ALIGN=center>$ref->{'middlename'}</TD>
                            <TD ALIGN=center>$ref->{'maidenname'}</TD>
                            <TD ALIGN=center>$ref->{'status'}</TD>
                            <TD ALIGN=center>$ref->{'name'}</TD>
                            <TD ALIGN=center>$ref->{'yearbook'}</TD>
                            <TD ALIGN=center>$ref->{'yearincluded'}</TD>
                            <TD ALIGN=center>$ref->{'page'}</TD>
                            </TR>\n";
                            $ref = $sth->fetchrow_hashref();
                        }
                        else { print "
                            <TD ALIGN=center>$ref->{'lastname'}</TD>
                            <TD ALIGN=center>$ref->{'firstname'}</TD>
                            <TD ALIGN=center>$ref->{'middlename'}</TD>
                            <TD ALIGN=center>$ref->{'maidenname'}</TD>
                            <TD ALIGN=center>$ref->{'status'}</TD>
                            <TD ALIGN=center>$ref->{'name'}</TD>
                            <TD ALIGN=center>$ref->{'yearbook'}</TD>
                            <TD ALIGN=center>$ref->{'yearincluded'}</TD>
                            <TD ALIGN=center>$ref->{'page'}</TD>
                            </TR>\n";
                            $ref = $sth->fetchrow_hashref();
                        }
                    }



$sth->finish();



}
if ($input{'isdata'} || $input{'records'}) {
                print"</TABLE></CENTER>\n";
}


print <<EOCHUNK;


<FORM ACTION=https://www.monroe.lib.in.us/cgi-bin/yrbksearch METHOD=POST id="search_form">
<INPUT TYPE=hidden NAME=isdata VALUE=1>
<P>
<TABLE BORDER=0>
<TR>
<TD>
<TABLE BORDER=0>
<TR>
<TD ALIGN=right>Last name:</TD><TD><INPUT TYPE=text NAME=lastname size=25 maxlength=50></TD>
</TR>
<TR>
<TD ALIGN=right>First name:</TD><TD><INPUT TYPE=text NAME=firstname size=25 maxlength=50></TD>
</TR>
<TR>
<TD ALIGN=right>Middle name:</TD><TD><INPUT TYPE=text NAME=middlename size=25 maxlength=50></TD>
</TR>
<TR>
<TD ALIGN=right>Maiden name:</TD><TD><INPUT TYPE=text NAME=maidenname size=25 maxlength=50></TD>
</TR>
<TR>
<TD ALIGN=right>School:</TD><TD>
<select  name=name>
<option value=""> Any School/Yearbook
EOCHUNK

my $sth = $dbh->prepare("SELECT name, yearbook FROM school ORDER BY name");

$sth->execute();

while (my $ref = $sth->fetchrow_hashref()) {

print "
<option value=$ref->{'name'}> $ref->{'name'}/$ref->{'yearbook'}";

}

print <<EOCHUNK;
</TD>
</TR>
<TR>
<TD COLSPAN=2><CENTER><INPUT TYPE="SUBMIT" NAME="submitReport" VALUE="Search"> <INPUT TYPE="RESET"
VALUE="Reset Form"></CENTER></TD>
</TR>
</TABLE>
</TD>

<TD VALIGN="top">
<TABLE><TR>
        <TD>You may search any of the fields individually or in combination.</TD></TR>
        <TR>
        <TD>Type as much of the name as you know.  For example, the search term Jim would locate Jim, Jimmy,
        etc.</TD></TR>
        <TR>
        <TD>You may insert the percent sign ( % ) in place of individual unknown letters.  For example, the
        search term Sm%th would locate Smith, Smyth, etc.</TD></tr>
        <tr>
         <td><br /><a href=\"JavaScript:newPopup('https://mcpl.info/yearbookkey.html\');\">Key to Abbreviations</a></td>

        </TR></TABLE></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>
</FORM>

<div class="greendashborder">
<p>The index includes names of students who attended area middle and high schools in Monroe County
and who were included in their yearbook. See also <a href="http://ezproxy.monroe.lib.in.us/login?qurl=https%3A//ancestrylibrary.proquest.com/">Ancestry</a> and <a href="http://www.uhsbloomington.org/">Unionville High School\'s Memory page</a> with <a href="http://www.uhsbloomington.org/Jordannus.html">Jordannus Yearbooks</a> for more yearbook information and photos.  </p>

<h3>Guidelines for requesting copies of yearbook pages</h3>

<p>Due to the level of demand for this service we restrict the number of names requested at one time to four (4). </p>

<p>Please allow 2-4 weeks for delivery. </p>


<p>Send request to:</p>

<p>Indiana Room<br />
Monroe County Public Library<br />
303 E. Kirkwood Ave.<br />
Bloomington, IN 47408 <br />
</p>
or

<p>e-mail via <a href="https://mcpl.info/indiana/ask-indiana-room">Ask Indiana Room</a> </p>


<p>Be sure to include the name of the student, grade, school, and date along with your name and mailing address.</p>


</div>
EOCHUNK









# Disconnect from the database.
$dbh->disconnect();

exit 0;
