Rev 7 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
## RollerCon support functions...use strict;use Exporter;use CGI qw/:standard :netscape/;use CGI::Cookie;use DBI;my $dsn = "DBI:mysql:database=rollerco_data;host=localhost;port=3306";my $dbh = DBI->connect($dsn, 'rollerco_www', 'www-data');our $ORCUSER;sub authDB {my $src = shift;my $id = shift;my $pass = shift;my $level = shift;my ($result, $encpass);my $sth = $dbh->prepare("select * from official where email = ?");$sth->execute($id);my $RCDBIDHASH = $sth->fetchrow_hashref();if ($src eq "form") {my $pwdhan = $dbh->prepare("select password(?)");$pwdhan->execute($pass);($encpass) = $pwdhan->fetchrow();} else {$encpass = $pass;}if (!$RCDBIDHASH) {$result->{ERRMSG} = "User-ID/Email Address not found!";$result->{cookie_string} = '';$result->{RCid} = '';logit(0, "Account not found: $id");$result->{authenticated} = 'false';} elsif ($RCDBIDHASH->{'password'} ne $encpass) {$result->{ERRMSG} = "Incorrect Password!";$result->{cookie_string} = '';$result->{RCid} = $RCDBIDHASH->{'RCid'};logit($RCDBIDHASH->{'RCid'}, "Incorrect Password");$result->{authenticated} = 'false';} elsif ($RCDBIDHASH->{'access'} < $level) {$result->{ERRMSG} = "Your account either needs to be activated, or doesn't have access to this page!";$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";$result->{RCid} = $RCDBIDHASH->{'RCid'};logit($RCDBIDHASH->{'RCid'}, "Insufficient Privileges");$result->{authenticated} = 'false';} else {$result->{ERRMSG} = '';$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";$result->{RCid} = $RCDBIDHASH->{'RCid'};logit($RCDBIDHASH->{'RCid'}, "Logged In") if $src eq "form";$result->{authenticated} = 'true';$ORCUSER=$RCDBIDHASH;}return $result;}sub authenticate { # Verifies the user has logged in or puts up a log in screen# Check to see if the user has already logged in (there should be cookies with their authentication)?my $MINLEVEL = shift || 1;# my $MINLEVEL = 1;my ($ERRMSG, $authenticated, %FORM);my $sth = $dbh->prepare("select * from official where email = '?'");my $query = new CGI;$FORM{'ID'} = $query->param('id') || '';$FORM{'PASS'} = $query->param('pass') || '';$FORM{'SUB'} = $query->param('login') || '';my $RCAUTH = $query->cookie('RCAUTH');if ($FORM{'SUB'}) {#a log in form was submitedif ($FORM{'SUB'} eq "Submit") {$authenticated = authDB('form', $FORM{'ID'}, $FORM{'PASS'}, $MINLEVEL);} elsif ($FORM{'SUB'} eq "New User") {# Print the new user form and exit}} elsif ($RCAUTH) {#We have an authenication cookie. Double-check itmy ($RCID, $RCPASS, $RCLVL) = split /&/, $RCAUTH;$authenticated = authDB('cookie', $RCID, $RCPASS, $MINLEVEL);} else {$authenticated->{authenticated} = 'false';}if ($authenticated->{authenticated} eq 'true') {return $authenticated->{cookie_string};}# If we get here, the user has failed authentication; throw up the log-in screen and die.my $RCAUTH_cookie = CGI::Cookie->new(-name=>'RCAUTH',-value=>"",-expires=>"now");if ($authenticated->{ERRMSG}) {$authenticated->{ERRMSG} = "<TR><TD colspan=2 align=center><font color=red><b>".$authenticated->{ERRMSG}."</b></font> </TD></TR>";# Log the failed access attempt} else {$authenticated->{ERRMSG} = "";# Since there was no ERRMSG, no need to log anything.}print header(-cookie=>$RCAUTH_cookie);printRCHeader("Please Sign In");print<<authpage;<form action="$ENV{REQUEST_URI}" method=POST name=Req id=Req><TR><TD colspan=2 align=center><b><font size=+2>Please Sign In</font></TD></TR><TR><TD colspan=2> </TD></TR>$authenticated->{ERRMSG}<TR><TD align=right><B>User ID:</TD><TD><INPUT type=text name=id></TD></TR><TR><TD align=right><B>Password:</TD><TD><INPUT type=password name=pass></TD></TR><TR><TD></TD><TD><INPUT type=submit name=login value=Submit></TD></TR><TR><TD colspan=2 align=center> </TD></TR><TR><TD colspan=2 align=center><A HREF="/schedule/manage_user.pl?submit=New%20User">[register as a new user]</A></TD></TR><TR><TD colspan=2 align=center><A HREF="/schedule/password_reset.pl">[reset your password]</A></TD></TR></TABLE></FORM><SCRIPT language="JavaScript"><!--function Login () {document.getElementById('Req').action = "$ENV{SCRIPT_NAME}";document.getElementById('Req').submit.click();return true;}//--></SCRIPT>authpage#foreach (keys %ENV) {# print "$_: $ENV{$_}<br>";#}# &JScript;exit;}sub getSetting {my $k = shift;my $sth = $dbh->prepare("select setting.value from setting where setting.key = ?");$sth->execute($k);return $sth->fetchrow_hashref()->{value};}sub getUser {my $EML = shift;my $sth = $dbh->prepare("select * from official where email = ?");$sth->execute($EML);return $sth->fetchrow_hashref();}sub getUserEmail {my $RCid = shift;my $sth = $dbh->prepare("select email from official where RCid = ?");$sth->execute($RCid);my ($email) = $sth->fetchrow_array();return $email;}sub getUserDerbyName {my $RCid = shift;my $sth = $dbh->prepare("select derby_name from official where RCid = ?");$sth->execute($RCid);my ($dname) = $sth->fetchrow_array();return $dname;}sub getYears {my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view union select year(now())");$sth->execute();my @years;while (my ($y) =$sth->fetchrow_array()) { push @years, $y; }return \@years;}sub printRCHeader {my $PAGE_TITLE = shift;my $loggedinas = $ORCUSER ? "Currently logged in as: $ORCUSER->{derby_name}" : "";print<<rcheader;<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title><link rel="stylesheet" type="text/css" href="/rollercon.css"></head><body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000"><TABLE><TR class="nostripe"><TD align=right><img SRC="/logo.jpg"></TD><TD align=center valign=middle><b><font size=+3>Officials' RollerCon<br>Schedule Manager<br>$PAGE_TITLE</FONT></b><p align=right><font size=-2>$loggedinas <a href='index.pl' onClick="document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/';return true;">[Log Out]</a></font></TD></TR>rcheader}sub changeShift {my ($change, $game_id, $role, $user_id) = @_;my $countbypass = 0;my $sth = $dbh->prepare("select type from game where id = ?");$sth->execute($game_id);my ($game_type) = $sth->fetchrow_array;if (lc($user_id) ne lc($ORCUSER->{RCid})) {if ($ORCUSER->{access} < 2) {print "<br>Denied! You are not authorized to change someone else's schedule.<br>\n";logit($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $game_id, $role, $user_id)");return;} else {logit($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $game_id, $role, $user_id)");$countbypass = 1;}}my $MAXSHIFTS = $game_type eq "clinic" ? getSetting("MAX_CLINIC_SIGNUPS") : $game_type eq "observation" ? getSetting("MAX_OBS_SIGNUPS") : getSetting("MAX_SHIFT_SIGNUP_PER_DAY");# my $MAXSHIFTS = getSetting("MAX_SHIFT_SIGNUP_PER_DAY");my $daily_count = signUpCount('get', $user_id, $game_type);if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$countbypass) {print "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";return;}if ($change eq "add" and ($daily_count < $MAXSHIFTS or $countbypass)) {$sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");} elsif ($change eq "del") {$sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");}print "<br>attempting to make DB changes...<br>";if ($sth->execute($game_id, $role, $user_id)) {$daily_count = signUpCount($change, $user_id, $game_type) unless $countbypass;logit($user_id, "Shift ".ucfirst($change).": $game_id -> $role");if ($game_type eq "clinic") {print "Success!...<br>You've signed up for $daily_count clinic shifts (you're currently allowed to sign up for $MAXSHIFTS total).<br>\n";} elsif ($game_type eq "observation") {print "Success!...<br>You've signed up for $daily_count clinic observation game (you're currently allowed to sign up for $MAXSHIFTS total).<br>\n";} else {print "Success!...<br>You've signed up for $daily_count challenge / scrimmage shifts today (you're currently allowed to sign up for $MAXSHIFTS per day).<br>\n";}} else {print "<br><b>You did not get the shift</b>, most likely because someone else took it while you were looking.<br>\nERROR: ", $sth->errstr();}}sub signUpCount {my $action = shift;my $id = shift;my $gtype = shift // "";if ($gtype ne "clinic" and $gtype ne "observation" and $id eq $ORCUSER->{RCid}) {if ($action eq 'add') {if (signUpCount('get', $id)) {$dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = $id");} else {$dbh->do("replace into sign_up_count values (curdate(), $id, 1)");}} elsif ($action eq 'del') {if (signUpCount('get', $id)) {$dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = $id");}}}my $get;if ($gtype eq "clinic") {$get = $dbh->prepare("select count(*) from v_shift where RCid = ? and gtype = 'clinic' and date >= '2019'");} elsif ($gtype eq "observation") {$get = $dbh->prepare("select count(*) from v_shift where RCid = ? and gtype = 'observation' and date >= '2019'");} else {$get = $dbh->prepare("select sign_ups from sign_up_count where RCid = ? and date = curdate()");}$get->execute($id);my ($R) = $get->fetchrow_array();return $R ? $R : '0';}sub signUpEligible {my $user = shift;my $t = shift;if (findConflict($user->{RCid}, $t->{id})) { return 0; }if (!exists $user->{sign_ups_today}) {$user->{sign_ups_today} = signUpCount('get', $user->{RCid});}if (!exists $user->{clinic_sign_ups}) {$user->{clinic_sign_ups} = signUpCount('get', $user->{RCid}, "clinic");}if (!exists $user->{obs_sign_ups}) {$user->{obs_sign_ups} = signUpCount('get', $user->{RCid}, "observation");}if ($t->{gtype} eq "clinic") {# Uncomment to open clinic games to everyone...return 1;#---------------------------------if ($user->{clinic_pass} and $user->{clinic_sign_ups} < getSetting("MAX_CLINIC_SIGNUPS")) {return 1;} else {return 0;}} elsif ($t->{gtype} eq "observation") {# Uncomment to open observation games to everyone...# return 1;#---------------------------------if ($user->{clinic_pass} and $user->{obs_sign_ups} < getSetting("MAX_OBS_SIGNUPS")) {return 1;} else {return 0;}} elsif ($t->{gtype} ne "selected staffing" and $t->{gtype} ne "short track" and $user->{sign_ups_today} < getSetting("MAX_SHIFT_SIGNUP_PER_DAY")) {return 1;} else {return 0;}}sub findConflict {my $rcid = shift;my $gid = shift;my $conflicts;# Are they already signed up for this game?my $sth0 = $dbh->prepare("select count(*) from v_shift where id = ? and RCid = ?");$sth0->execute($gid, $rcid);($conflicts) = $sth0->fetchrow_array;if ($conflicts) { return 1; }# Are they signed up for any games that would conflict with this one?# my $sth = $dbh->prepare("select count(*) from v_shift where id in (select id from game where date = (select date from game where id = ?) and ((end_time > (select time from game where id = ?) and end_time < (select end_time from game where id = ?)) or (time > (select time from game where id = ?) and time < (select end_time from game where id = ?)) or (time < (select time from game where id = ?) and end_time > (select end_time from game where id = ?)))) and RCid = ?");my $sth = $dbh->prepare("select count(*) from v_shift where id in (select id from game where date = (select date from game where id = ?) and ((time <= (select time from game where id = ?) and end_time > (select time from game where id = ?)) or (time > (select time from game where id = ?) and time < (select end_time from game where id = ?)))) and RCid = ?");# $sth->execute($gid, $gid, $gid, $gid, $gid, $gid, $gid, $rcid);$sth->execute($gid, $gid, $gid, $gid, $gid, $rcid);($conflicts) = $sth->fetchrow_array;return $conflicts;}sub changeLeadShift {my ($change, $lshift, $user_id) = @_;my $ERRMSG;my $sth = $dbh->prepare("update lead_shift set assignee_id = ? where id = ?");print "<br>attempting to make DB changes...<br>";if ($change eq "add") {$sth->execute($user_id, $lshift)or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";} elsif ($change eq "del") {$sth->execute('', $lshift)or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";}if ($ERRMSG) {print $ERRMSG;} else {logit($user_id, "Lead Shift ".ucfirst($change).": $lshift");print "Success.<br>";}}sub logit {my $RCid = shift;my $msg = shift;my $sth = $dbh->prepare("insert into log (RCid, event) values (?, ?)");$sth->execute($RCid, $msg);}1;