Rev 36 | Blame | Compare with Previous | Last modification | View Log | RSS feed
## 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",0 => "Pending",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 screenmy $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 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><TABLE></TD></TR><TR><TD colspan=2> </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> </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 = ? unionselect id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? unionselect 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 shiftif ($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 leadlogit ($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-upmy $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 = ? unionselect concat('ANN-', id) from v_shift_announcer where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? unionselect 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;