Subversion Repositories ORC

## 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 submited

		if ($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 it

		my ($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>&nbsp</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>&nbsp</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>&nbsp;</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;