Subversion Repositories ORC

## RollerCon support functions...

use strict;

use cPanelUserConfig;

use Exporter;

use CGI qw/:standard :netscape/;

use CGI::Cookie;

use DBI;

use WebDB;

my $dbh = WebDB->connect ();

sub getRCDBH {

  return $dbh;

}

our $ORCUSER;

use constant {

    USER   => 1,

    LEAD   => 2,

    MANAGER  => 3,

    DIRECTOR  => 4,

    ADMIN  => 5

  };

sub getAccessLevels {

  my %AccessLevels = (

    -1 => "Locked",

    1 => "Volunteer",

    2 => "Lead",

    3 => "Manager",

    4 => "Director",

    5 => "SysAdmin"

  );

  return \%AccessLevels;

}

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;

	}

	my $tempDepartments = convertDepartments ($RCDBIDHASH->{department});

	my $MAXACCESS = scalar keys %{ $tempDepartments } ? max ($RCDBIDHASH->{'access'}, values %{ $tempDepartments }) : $RCDBIDHASH->{'access'};

	if (!$RCDBIDHASH->{'RCid'}) {

		$result->{ERRMSG} = "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 ($MAXACCESS < $level) {

	  if (getSetting ("MAINTENANCE")) {

	    $result->{ERRMSG} = "MAINTENANCE MODE: Logins are temporarily disabled.";

	  } else {

		  $result->{ERRMSG} = "Your account either needs to be activated, or doesn't have access to this page!";

  		logit($RCDBIDHASH->{'RCid'}, "Insufficient Privileges");

		}

		$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";

		$result->{RCid} = $RCDBIDHASH->{'RCid'};

		$result->{authenticated} = 'false';

	} else {

		$result->{ERRMSG} = '';

		$RCDBIDHASH->{department} = convertDepartments ($RCDBIDHASH->{department});

		$RCDBIDHASH->{'access'} = max ($RCDBIDHASH->{'access'}, values %{$RCDBIDHASH->{department}});

		$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";

		$result->{RCid} = $RCDBIDHASH->{'RCid'};

		logit($RCDBIDHASH->{'RCid'}, "Logged In") if $src eq "form";

		$dbh->do ("update official set last_login = CONVERT_TZ(now(), 'America/Chicago', 'America/Los_Angeles') where RCid = ?", undef, $RCDBIDHASH->{'RCid'}) if $src eq "form";

		$result->{authenticated} = 'true';

#		my @depts = map { s/-\d// } split /:/, $RCDBIDHASH->{department};

#		my @depts = split /:/, $RCDBIDHASH->{department};

		$ORCUSER=$RCDBIDHASH;

	}

	return $result;

}

sub max {

    my ($max, $next, @vars) = @_;

    return $max if not $next;

    return max( $max > $next ? $max : $next, @vars );

}

sub authenticate {									# Verifies the user has logged in or puts up a log in screen

	my $MAINTMODE = getSetting ("MAINTENANCE");

	my $MINLEVEL = $MAINTMODE ? $MAINTMODE : shift // 1;

	my ($ERRMSG, $authenticated, %FORM);

	my $sth = $dbh->prepare("select * from official where email = '?'");

	my $query = new CGI;

# Check to see if the user has already logged in (there should be cookies with their authentication)?

	my $RCAUTH = $query->cookie('RCAUTH');

	$FORM{'ID'} = WebDB::trim $query->param('id') || '';

	$FORM{'PASS'} = WebDB::trim $query->param('pass') || '';

	$FORM{'SUB'} = $query->param('login') || '';

	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>

		<TABLE>

		</TD></TR>

		<TR><TD colspan=2>&nbsp</TD></TR>

		$authenticated->{ERRMSG}

		<TR>

			<TD align=right><B>Email Address:</TD><TD><INPUT type=text id=login 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">

	<!--

	document.getElementById("login").focus();

	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 getShiftDepartment {

  my $shiftID = shift // "";

  my $dept;

  if ($shiftID =~ /^\d+$/) {

    ($dept) = $dbh->selectrow_array ("select dept from shift where id = ?", undef, $shiftID);

  } else {

    my ($id, $role) = split /-/, $shiftID;

    ($dept) = $dbh->selectrow_array ("select distinct department from staff_template where role like ?", undef, $role.'%');

  }

#  } elsif ($shiftID =~ /^\d+-ANN/) {

#    $dept = "ANN";

#  } else {

#    $dept = "OFF";

#  }

  return $dept;

}

sub getDepartments {

  my $RCid = shift // "";

  # If we get an RCid, return the list of departments and levels for that user.

  #   Otherwise (no parameter), return the list of departments with their display names.

	if ($RCid) {

  	my $sth = $dbh->prepare("select department from official where RCid = ?");

  	$sth->execute($RCid);

  	my ($dlist) = $sth->fetchrow;

  	return convertDepartments ($dlist);

	} else {

  	my %HASH;

  	my $sth = $dbh->prepare("select TLA, name from department");

  	$sth->execute();

  	while (my ($tla, $name) = $sth->fetchrow) {

  	  $HASH{$tla} = $name;

    }

    return \%HASH;

  }

}

sub convertDepartments {

  # For the department membership, converts the DB string back and forth to a hashref...

  my $input = shift // "";

  my $output;

  if (ref $input eq "HASH") {

    $output = join ":", map { $_."-".$input->{$_} } sort keys %{$input};

  } else {

  	foreach (split /:/, $input) {

  	  my ($tla, $level) = split /-/;

  	  $output->{$tla} = $level;

    }

  }

  return $output;

}

sub getSchedule {

  my $RCid = shift // return "ERROR: No RCid provided to getSchedule";

  my $filter = shift // "";

  my @whereclause;

  if ($filter eq "all") {

  	push @whereclause, "date >= '2022-01-01'";

  } else {

  	push @whereclause, "date >= date(now())";

  }

#  if ($RCid ne $ORCUSER->{RCid}) {

#    push @whereclause, "dept != 'PER'";

#  }

  use DateTime;

  my $dt = DateTime->today (time_zone => 'America/Los_Angeles');

  $dt =~ s/T00\:00\:00$//;

  my $now = DateTime->now (time_zone => 'America/Los_Angeles');

  use HTML::Tiny;

  my $h = HTML::Tiny->new( mode => 'html' );

  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";

  my @shifts;

  my $sth = $dbh->prepare("select * from (select id, date, dayofweek, track as location, time, role, teams, signup, 'OFF' as dept, volhours from v_shift_officiating where RCid = ? union

                                          select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union

                                          select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ?) temp

                           $where order by date, time");

  $sth->execute($RCid, $RCid, $RCid);

  my $hours = 0;

  while (my $s = $sth->fetchrow_hashref) {

    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};

	  my $cutoff = DateTime->new(

        year => $yyyy,

        month => $mm,

        day => $dd,

        hour => 5,

        minute => 0,

        second => 0,

        time_zone => 'America/Los_Angeles'

    );

  	if (!$s->{teams}) {

  	  # it's a time-based shift

  	  if ($s->{dept} eq "PER") {

        if ($RCid eq $ORCUSER->{RCid}) {

          # DROP

  	      $s->{buttons} = $h->button ({ onClick=>"if (confirm('Really? You want to delete this personal time?')==true) { window.open('manage_personal_time.pl?choice=Delete&id=$s->{id}','Confirm Change','resizable,height=260,width=370'); return false; }" }, "DEL")." ".$h->button ({ onClick=>"location.href='manage_personal_time.pl?choice=Update&id=$s->{id}'" }, "EDIT");

  	    } else {

  	      $s->{location} = "";

  	      $s->{role} = "";

  	    }

      } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {

        # DROP

  		  $s->{buttons} = $h->button ({ onClick=>"if (confirm('Really? You want to drop this shift?')==true) { window.open('make_shift_change.pl?change=del&id=$s->{id}','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "DROP");

	   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {

   		    # NO SHOW

 	  	    $s->{buttons} .= " ".$h->button ({ onClick=>"if (confirm('Really? They were a no show?')==true) { window.open('make_shift_change.pl?noshow=true&change=del&RCid=$RCid&id=$s->{id}','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "NO SHOW");

 		    }

        $hours += $s->{volhours} unless $s->{dept} eq "CLA";

  		}

    } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {

      # it's a game shift

      #DROP

  		$s->{buttons} = $h->button ({ onClick=>"if (confirm('Really? You want to drop this shift?')==true) { window.open('make_shift_change.pl?change=del&RCid=$RCid&id=$s->{id}&role=$s->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "DROP");

   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {

 		    # NO SHOW

        $s->{buttons} .= " ".$h->button ({ onClick=>"if (confirm('Really? They were a no show?')==true) { window.open('make_shift_change.pl?noshow=true&change=del&RCid=$RCid&id=$s->{id}&role=$s->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "NO SHOW");

      }

      $hours += $s->{volhours};

  	}

  	$s->{role} =~ s/\-\d$//;

#  	push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "nowrap highlighted" : "nowrap shaded" }, join '  ', $s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams}, $s->{buttons});

#  	push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "highlighted" : "shaded" }, join '  ', $s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams}, $s->{buttons});

  	push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "highlighted" : "shaded" }, $h->div ({ class=>"lisp0" }, [ $h->div ({ class=>"liLeft" }, join '  ', ($s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams})), $h->div ({ class=>"liRight" }, $s->{buttons}) ]));

  }

  if (scalar @shifts) {

    return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);

  } else {

    return $h->p ({ class=>"hint" }, "[nothing scheduled at the moment]");

  }

}

sub getRCid {

  my $derbyname = shift;

  ($derbyname) = $dbh->selectrow_array ("select RCid from official where derby_name = ?", undef, $derbyname);

  return $derbyname;

}

sub getSetting {

	my $k = shift;

	my ($value) = $dbh->selectrow_array ("select setting.value from setting where setting.key = ?", undef, $k);

  return defined $value ? $value : undef;

}

sub getUser {

	my $ID = shift;

	my $sth;

	if ($ID =~ /^\d+$/) {

	  $sth = $dbh->prepare("select * from official where RCid = ?");

	} else {

	  $sth = $dbh->prepare("select * from official where email = ?");

  }

	$sth->execute($ID);

	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())");

	my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view");

	$sth->execute();

	my @years;

	while (my ($y) =$sth->fetchrow_array()) { push @years, $y; }

	return \@years;

}

sub printRCHeader {

	my $PAGE_TITLE = shift;

	use CGI qw/start_html/;

	use HTML::Tiny;

  my $h = HTML::Tiny->new( mode => 'html' );

#  my $logout = $h->a ({ href=>"index.pl", onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/';return true;" }, "[Log Out]");

  my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};

  my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")." ";

  $logout .= url =~ /\/(index.pl)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/schedule/';" }, "Home")." ";

  $logout .= $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");

	my $loggedinas = $ORCUSER ? "Currently logged in as: ".$h->a ({ href=>"/schedule/manage_user.pl?submit=View&RCid=$ORCUSER->{RCid}" }, $ORCUSER->{derby_name}).$h->br.$logout : "";

  print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );

#<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title>

#<link rel="stylesheet" type="text/css" href="/style.css">

#</head>

#<body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000">

	print $h->div ({ class=>"sp0" }, [ $h->div ({ class=>"spLeft" },  $h->a ({ href=>"/schedule/" }, $h->img ({ src=>"/logo.jpg", width=>"75", height=>"75" }))),

	                                   $h->div ({ class=>"spRight" }, [ $h->h1 (["vORC $PAGE_TITLE", $h->br]),

	                                   $loggedinas,

	                                   ])

	                                 ]);

#print<<rcheader;

#  <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, $shift_id, $role, $user_id) = @_;

	my $leadership_change = 0;

#	my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);

	my $department;

	if ($shift_id =~ /^\d+$/) {

		$department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);

	} else {

		$department = "CLA";

		($shift_id) = $dbh->selectrow_array ("select min(id) from v_shift where date = ? and start_time = ? and location = ? and isnull(RCid) = 1", undef, split /\|/, $shift_id);

	}

	my $game_based = $role ? "game" : "shift";

	my $sth;

	if ($change eq "add" or $change eq "override") {

  	my $taken;

  	if ($game_based eq "game") {

  	  ($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);

		} elsif ($department eq "CLA") {

  	  ($taken) = $shift_id ? 0 : 1;

  	} else {

  	  ($taken) = $dbh->selectrow_array ("select count(*) from shift where id = ? and isnull(assignee_id) = 0", undef, $shift_id);

  	}

  	if ($taken) {

  	    return ($department eq "CLA") ? "<br>Denied! This class is already full ($shift_id).<br>\n" : "<br>Denied! This shift is already taken ($shift_id).<br>\n";

  	}

  }

	if (lc ($user_id) ne lc ($ORCUSER->{RCid})) { # they're changing someone else's schedule...

	  if ($ORCUSER->{department}->{$department} >= 2 or $ORCUSER->{access} >= 5 or $ORCUSER->{department}->{VCI} >= 2) {

	    # the user making the change is either a lead in the dept, a sysadmin, or a VCI lead

	    logit ($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $shift_id, $role, $user_id)");

	    logit ($user_id, "Schedule was changed by $ORCUSER->{derby_name}. ($change, $shift_id, $role, $user_id)");

	    $leadership_change = 1;

	  } else {

	    logit ($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $shift_id, $role, $user_id)");

	    return "<br>Denied! You are not authorized to change someone else's schedule in this department ($department).<br>\n";

	  }

	} elsif ($ORCUSER->{department}->{$department} >= 3) {

	  # Managers can sign up for as many shifts within their own department as they like...

	  $leadership_change = 1;

	}

  if ($change eq "add" and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {

		return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n" unless $department eq "CMP";

  }

  my $conflict = findConflict ($user_id, $shift_id, $game_based);

  if ($change eq "add" and $conflict) {

		return "<br>Denied! There is a conflict ($conflict) with that shift's time!<br>\n";

  }

 	my ($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);

 	if ($game_type =~ /^selected/ and !$leadership_change) {

 	  return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n" unless $department eq "CMP";

 	}

 	if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {

 	  return "<br>Denied! Shift reserved for leadership staff!<br>\n";

 	}

# 	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");

	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$department);

	$MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $MAXSHIFTS;

	if ($game_type eq "lead" and $department eq "OFF") { $MAXSHIFTS = 99; }

  my $daily_count;

  if ($department eq "CLA") {

    # MVP Class Sign-up

    $MAXSHIFTS = getSetting ("MAX_CLASS_SIGNUP");

	  ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);

   	if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {

	    return "<br>Denied! You may only sign up for $MAXSHIFTS Classes!<br>\n";

	  }

  } else {

   	$daily_count = signUpCount ('get', $user_id, $department);

   	if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {

   		return "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";

   	}

#   	if ($change eq "add" and $game_based eq "game" and $department eq "OFF" and $game_type eq "full length") {

#    	my ($full_length_count) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where RCid = ? and gtype = 'full length' and date > '2022-01-01'", undef, $user_id);

#  		if ($full_length_count >= 3) {

#  			return "<br>Denied! You may only sign up to officiate 3 $game_type games (total)!<br>\n";

#  		}

#    }

  }

 	my @DBARGS;

  if ($game_based eq "game") {

  	if ($change eq "add" or $change eq "override") {

  		$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= ?");

  	}

  	@DBARGS = ($shift_id, $role, $user_id);

  } else {

  	if ($change eq "add" or $change eq "override") {

  		$sth = $dbh->prepare("update shift set assignee_id = ? where id = ? and isnull(assignee_id) = 1");

  		@DBARGS = ($user_id, $shift_id);

  	} elsif ($change eq "del") {

  		$sth = $dbh->prepare("update shift set assignee_id = null where id = ?");

  		@DBARGS = ($shift_id);

  	}

  }

  print "<br>attempting to make DB changes...<br>";

  if ($sth->execute (@DBARGS)) {

  	$daily_count = signUpCount ($change, $user_id, $department) unless $leadership_change;

  	logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");

  	logit ($ORCUSER->{RCid}, "OVERRIDE: Shift ".ucfirst($change).": $shift_id -> $role") if $change eq "override";

  	print "Success!...<br>You've signed up for $daily_count shifts today (you're currently allowed to sign up for $MAXSHIFTS per day).<br>\n";

  	return;

  } else {

    return "<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 modShiftTime {

	my ($shift_id, $user_id, $diff) = @_;

	my $ORCUSER = getUser (1);

	use Scalar::Util qw(looks_like_number);

	if (!looks_like_number ($diff)) {

	  print "<br>ERROR! The time adjustment ($diff) doesn't look like a number.<br>\n";

  	return;

	}

  my ($validate_assignee) = $dbh->selectrow_array ("select count(*) from v_shift where id = ? and RCid = ?", undef, $shift_id, $user_id);

 	if (!$validate_assignee) {

	  print "<br>ERROR! This shift is assigned to someone else.<br>\n";

  	return;

 	}

	my $department = getShiftDepartment ($shift_id);

  if (convertDepartments ($ORCUSER->{department})->{$department} < 2 and $ORCUSER->{access} < 5) {

	  print "<br>ERROR! You're not authorized to modify this shift's time.<br>\n";

	  logit ($ORCUSER->{RCid}, "Unauthorized attempt to modify shift time. ($department, $shift_id)");

  	return;

 	}

  my $rows_changed;

  print "<br>attempting to make DB changes...<br>";

  if ($diff == 0) {

	  $rows_changed = $dbh->do ("update shift set mod_time = null where id = ? and assignee_id = ?", undef, $shift_id, $user_id);

  } else {

	  $rows_changed = $dbh->do ("update shift set mod_time = ? where id = ? and assignee_id = ?", undef, $diff, $shift_id, $user_id);

  }

  if (!$rows_changed or $dbh->errstr) {

  	print "ERROR: Nothing got updated".$dbh->errstr;

  	logit (0, "ERROR modifying a shift time ($diff, $shift_id, $user_id):".$dbh->errstr);

  } else {

  	print "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)";

  	logit ($ORCUSER->{RCid}, "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)");

  }

  return;

}

sub signUpCount {

	my $action = shift;

	my $id = shift;

	my $dept = shift // "";

	if ($id eq $ORCUSER->{RCid}) {

		if ($action eq 'add') {

			if (signUpCount ('get', $id, $dept)) {

				$dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);

			} else {

				$dbh->do("replace into sign_up_count (date, RCid, department, sign_ups) values (curdate(), ?, ?, 1)", undef, $id, $dept);

			}

		} elsif ($action eq 'del') {

			if (signUpCount ('get', $id, $dept)) {

				$dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);

			}

		}

	}

	my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and department = ? and date = curdate()", undef, $id, $dept);

	return $R ? $R : '0';

}

sub signUpEligible {

	my $user = shift;

	my $t = shift;

	my $shifttype = shift // "game";

	my $dept = $t->{dept} // "";

	my $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$dept);

	$limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $limit;

	if ($t->{type} eq "lead" and $dept eq "OFF") { $limit = 99; }

	return 0 unless $limit > 0;

	my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";

	if ($shifttype eq "class") {

		($t->{id}) = $dbh->selectrow_array ("select min(id) from v_shift where isnull(RCid) = 1 and dept = ? and date = ? and location = ? and start_time = ?", undef, "CLA", $t->{date}, $t->{location}, $t->{start_time});

		$t->{dept} = "CLA";

		$t->{type} = "open";

	}

	if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }

	if (!exists $user->{$limitkey}) {

		$user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);

	}

	if ($shifttype eq "game") {

#    if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{$limitkey} < $limit) {

#		if ($t->{gtype} eq "full length" and $dept eq "OFF") {

#			my ($full_length_count) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where RCid = ? and gtype = 'full length' and date > '2022-01-01'", undef, $user->{RCid});

#			if ($full_length_count >= 3) {

#				return 0;

#			}

#		}

    if ($t->{signup} ne "selected" and $user->{$limitkey} < $limit) {

			return 1;

		} else {

			return 0;

		}

	} else {

    if ($dept eq "CLA") {

      # MVP Class Sign-up

      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");

			my ($class_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user->{RCid});

			return 0 unless $class_count < $class_limit;

    }

	  if ($user->{department}->{$t->{dept}} < 1) { return 0; }

	  if ($t->{type} eq "lead" and $user->{department}->{$t->{dept}} < 2) { return 0; }

	  if ($t->{type} eq "manager" and $user->{department}->{$t->{dept}} < 3) { return 0; }

    if ($t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {

			return 1;

		} else {

			return 0;

		}

	}

}

sub findConflict {

  my $rcid = shift;

  my $gid = shift;

  my $type = shift // "";

  my ($date, $start, $end, $conflicts);

  if ($type eq "game") {

  # Are they already signed up for this game? (It's faster to check the two views one at a time...)

#    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where substring_index(id, '-', 1) = ? and RCid = ?", undef, $gid, $rcid);

    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where id = ? and RCid = ?", undef, $gid, $rcid);

  	if ($conflicts) { return "OFF-".$gid; } # no need to keep looking...

    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);

  	if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...

    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);

  } elsif ($type eq "personal")  {

    ($date, $start, $end) = @{ $gid };

  } else {

    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);

  }

  # Are they signed up for any games that would conflict with this one?

#  my $sth = $dbh->prepare("select count(*) from v_shift_admin_view 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 = ?");

#  my $sth = $dbh->prepare("select count(*) from v_shift_all where id in (select id from v_shift_all where date = (select date from v_shift_all where id = ?) and ((start_time <= (select start_time from v_shift_all where id = ?) and end_time > (select start_time from v_shift_all where id = ?)) or (start_time > (select start_time from v_shift_all where id = ?) and start_time < (select end_time from v_shift_all where id = ?)))) and RCid = ?");

  ($conflicts) = $dbh->selectrow_array ("select * from (

    select concat(dept, '-', id) from v_shift             where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union

    select concat('ANN-', id) from v_shift_announcer   where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union

    select concat('OFF-', id) from v_shift_officiating where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? ) alltables",

    undef, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid

  );

  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;