#!/usr/bin/perl use DBI; use CGI; use locale; #Needed for the lc and ucfirst functions use strict; use Digest::MD5 qw(md5 md5_hex md5_base64); use POSIX; use URI::Escape; use mysql; use errorcapture; require 'common.pl'; my $myid = "events.html"; my $myversion = "2014.8.10.1.14716"; my ($errMsg, $focus); my $query = new CGI; my $errorobj = new errorcapture(); $errorobj->setFile($myid.$myversion); my ($dbh, $sth, $sql, $rows); my $sqlobj = new mysql(); $dbh = $sqlobj->MySqlLogon(); ################ #User Verification my %cookies = &getCookies; # store cookies in %cookies my $cookieID = $cookies{uid}; my $key = $cookies{key}; if ("events.html" eq "login.pl") { if ($cookieID && $key) { if (defined $dbh) { $sql = qq{ SELECT userKey FROM session WHERE userKey = ? AND cookieID = ? AND logoutTime >= now() }; eval { $rows = $dbh->selectrow_array($sql, {}, $key, $cookieID); }; if ($@) { $errorobj->writeerror($@, $sql); } if ($rows != 0){ print $query->redirect(-location => '/cgi-bin/datapages/welcome.pl'); $sqlobj->MySqlLogoff(); exit; } ################ } else { print $query->header(); $errorobj->writeerror($sqlobj->errorstring); $errorobj->webFatal("An error log has been started. A fatal program error occurred and halted the application."); } } } print $query->header(); my $search = substr($query->param('searchText'), 0, 50); my $regionKey = substr($query->param('regionKey'), 0, 3); my $sportKey = substr($query->param('sportKey'), 0, 4); my $showall = substr($query->param('showall'), 0, 1); my $start = substr($query->param('start'), 0, 4); my $rows = substr($query->param('rows'), 0, 4); my $errMsg1 = "Only text values A to Z, numbers 0 to 9, the underscore, period, space and hyphen are allowed"; my $errMsg2 = "An unrecoverable error has occured. Please search again."; if ($regionKey =~ /[^\d]/g) { $errMsg = $errMsg2; } elsif ($sportKey =~ /[^\d]/g) { $errMsg = $errMsg2; } elsif ($start =~ /[^\d]/g) { $errMsg = $errMsg2; } elsif ($rows =~ /[^\d]/g) { $errMsg = $errMsg2; } elsif ($showall =~ /[^\d]/g) { $errMsg = $errMsg2; } $sportKey = $cookies{sport} if ($sportKey eq "" && $cookies{usesport}); $regionKey = $cookies{region} if ($regionKey eq "" && $cookies{useregion}); my $queryLimit; if ($start) { $queryLimit = "LIMIT ".($start*10-10).",".($start*10); } my @queryCriteria = (); my ($fixsearch, $likesearch, $sql_clause); my ($searcharr, $regionarr, $sportarr); if (!$errMsg) { my ($fixsearch, $likesearch); if ($search) { $fixsearch = $dbh->quote( $search ); $likesearch = $dbh->quote("%".$search."%"); } if ( $fixsearch ) { push @queryCriteria, " (MATCH(es.eventName,es.eventDescription) AGAINST (".$fixsearch.") OR rg.regionName LIKE ".$likesearch.")"; } if ($sportKey) { push @queryCriteria, "es.sportKey = ".$sportKey; } if ($regionKey) { push @queryCriteria, "rg.regionKey = ".$regionKey; } if (!$showall) { push @queryCriteria, "TO_DAYS(es.eventEnd) - TO_DAYS(NOW()) >= 0"; } $sql_clause = join(" AND ", @queryCriteria); if ($sql_clause) { $sql_clause = "AND (".$sql_clause.")"; } $sql = qq{ SELECT es.eventID, es.eventName, es.eventDescription, rg.regionName, es.htmlLink, sp.sportName, DATE_FORMAT(es.eventStart, '%W, %M %d, %Y'), DATE_FORMAT(es.eventEnd, '%W, %M %d, %Y'), im.width, im.height, im.userID, im.actualName, im.name FROM events es LEFT JOIN sport sp ON sp.sportKey = es.sportKey LEFT JOIN reglocmapping rm ON rm.mappingKey = es.locMappingKey LEFT JOIN region rg ON rg.regionKey = rm.regionKey LEFT JOIN images im ON im.imageID = es.imageKey WHERE es.active = 1 $sql_clause ORDER BY es.eventEnd DESC, es.ordered $queryLimit }; eval { $sth = $dbh->prepare($sql); $sth->execute(); if (!$start) { $rows = $sth->rows; } $searcharr = $sth->fetchall_arrayref(); $sth->finish(); }; if ($@) { $errorobj->writeerror($@, $sql); } #WRITE THE SEARCH STRINGS TO A TABLE if ( $search ) { my $tempsql = $dbh->quote( $sql ); my $temprows = $dbh->quote( $rows ); my $tempstart = $dbh->quote( $start ); $sql = qq{ INSERT INTO searchSaved (searchText, searchScreen, searchDate, returnedRows, startRow) VALUES ($tempsql, 'EVENT SEARCH', NOW(), $temprows, $tempstart) }; eval { $dbh->do($sql); }; if ($@) { $errorobj->writeerror($@, $sql); } } $sql = qq{ SELECT sportKey, sportName FROM sport ORDER BY sportName }; eval { $sportarr = $dbh->selectall_arrayref($sql); }; if ($@) { $errorobj->writeerror($@, $sql); } $sql = qq{ SELECT regionKey, regionName FROM region ORDER BY regionName }; eval { $regionarr = $dbh->selectall_arrayref($sql); }; if ($@) { $errorobj->writeerror($@, $sql); } } print < Sports Events, Sports management, team websites, find players, teams and leagues
 
GO























Content is comming here as you probably can see.
}; my $i = 1; foreach my $row (@$searcharr) { my ($eventID, $eventName, $eventDescription, $location, $htmlLink, $sportName, $start, $end, $width, $height, $userID, $actualName, $altImgName) = @$row; $eventName = $query->escapeHTML( $eventName ); $eventDescription = $query->escapeHTML( $eventDescription ); print qq{ EOT ++$i; if ($i > 10) { last; } } $pageDisplay =~ s//imgbotPRE/g; $pageDisplay =~ s//imgbotNEX/g; print <
EVENT SEARCH
Looking for a event to play in
occurring
Want your event listed here, register for a free account with us and within minutes your event can be listed here too.

Search Results
EOT my $pageDisplay; if ($rows == 0) { print qq{ }; } else { print qq{ \n"; my $hirowcnt = nhimult(10, $rows) / 10; if ($hirowcnt > 1) { $pageDisplay = qq{ \n"; } else { print qq{\n}; } print qq{\n}; } print qq{
 
Sorry, no events found.
 
Events found: $rows
}; if (!$start) { $start=1; my $endRow = 10; $endRow = $rows if ($endRow > $rows); print qq{
Displaying Rows: 1-$endRow
}; } else { my $endRow = $start*10; $endRow = $rows if ($endRow > $rows); my $startrow = $start * 10 - 9; print qq{
Displaying Rows: $startrow-$endRow
}; } print "
Results Page: }; my $dispStart; #Display start is the number that starts the search results. ex. 1 2 3 4 5, or 3 4 5 6 7 if ($start <= 4) { $dispStart = 1; } else { $dispStart = $start - 4; if ($hirowcnt-$dispStart < 10) { $dispStart = $hirowcnt - 9; if ($dispStart < 1) { $dispStart = 1}; } } my $lstlen = 1; if ($start != 1) { my $pstart = $start-1; $pageDisplay .= qq{Previous  }; } for (my $y=$dispStart;$y<=$hirowcnt;++$y) { if ($y == $start) { $pageDisplay .= qq{$y  }; } else { $pageDisplay .= qq{$y  }; } ++$lstlen; if ($lstlen > 10) { last; } } if ($start != $hirowcnt) { my $pstart = $start+1; $pageDisplay .= qq{Next  }; } $pageDisplay .= qq{
}; my $tmpDisplay = $pageDisplay; $tmpDisplay =~ s//imgtopPRE/g; $tmpDisplay =~ s//imgtopNEX/g; print $tmpDisplay; print "

\n}; if ($actualName) { my $rowspan; if ($width < 250) { $width+=80; $rowspan = qq{ rowspan="2" width="$width"}; } else { $rowspan = qq{ colspan="3" width="100%"}; } print qq{ $altImgName\n}; } my $dateStart; if ($start || $end) { if ($start eq $end) { $dateStart = qq{When:$start
}; } elsif ($start && $end) { $dateStart = qq{When:$start - $end
}; } } $htmlLink = qq{More Details:$htmlLink\n} if ($htmlLink ne ""); if ($eventDescription !~ /\br\>/) { $eventDescription =~ s/\n/\/g; } else { $eventDescription =~ s/\n//g; } print <

$eventName

Location: $location
Sport: $sportName
$dateStart $eventDescription

$htmlLink



 $pageDisplay
Copyright © 2014 SpareSports Productions.
All Rights Reserved.
Team/Spare Search Version 7 Build 12.22
EOT $sqlobj->MySqlLogoff(); 1;