Rev 2 | Rev 9 | 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;use WebDB;my $dbh = WebDB->connect ();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;}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 (max ($RCDBIDHASH->{'access'}, values %{convertDepartments ($RCDBIDHASH->{department})}) < $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 = now() 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'} = $query->param('id') || '';$FORM{'PASS'} = $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>User ID:</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);} 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;push @whereclause, "date >= date(now())" unless $filter eq "all";# if ($RCid ne $ORCUSER->{RCid}) {# push @whereclause, "dept != 'PER'";# }use DateTime;my $dt = DateTime->today;$dt =~ s/T00\:00\:00$//;my $now = DateTime->now;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, gtype, 'OFF' as dept, volhours from v_shift_officiating where RCid = ? unionselect id, date, dayofweek, track as location, time, role, teams, gtype, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? unionselect id, date, dayofweek, location, time, role, '' as teams, type as gtype, dept, volhours from v_shift where RCid = ?) temp$where order by date, time");$sth->execute($RCid, $RCid, $RCid);my $hours;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->{gtype} !~ /^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};}} elsif (($RCid == $ORCUSER->{RCid} and $s->{gtype} !~ /^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});}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 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 $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 $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})." $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 $game_based = $role ? "game" : "shift";my $sth;if ($change eq "add") {my $taken;if ($game_based eq "game") {($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);} else {($taken) = $dbh->selectrow_array ("select count(*) from shift where id = ? and isnull(assignee_id) = 0", undef, $shift_id);}if ($taken) {return "<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) {# the user making the change is either a lead in the dept or a sysadminlogit ($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";}if ($change eq "add" and findConflict ($user_id, $shift_id, $game_based)) {return "<br>Denied! There is a 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";}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 $daily_count = signUpCount ('get', $user_id);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";}my @DBARGS;if ($game_based eq "game") {if ($change eq "add") {$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") {$sth = $dbh->prepare("update shift set assignee_id = ? where id = ?");@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) unless $leadership_change;logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");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 $gtype = shift // "";if ($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 = ?", undef, $id);} else {$dbh->do("replace into sign_up_count values (curdate(), ?, 1)", undef, $id);}} 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 = ?", undef, $id);}}}my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and date = curdate()", undef, $id);return $R ? $R : '0';}sub signUpEligible {my $user = shift;my $t = shift;my $shifttype = shift // "game";if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }if (!exists $user->{sign_ups_today}) {$user->{sign_ups_today} = signUpCount('get', $user->{RCid});}if ($shifttype eq "game") {if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{sign_ups_today} < getSetting("MAX_SHIFT_SIGNUP_PER_DAY")) {return 1;} else {return 0;}} else {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->{sign_ups_today} < getSetting("MAX_SHIFT_SIGNUP_PER_DAY")) {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 1; } # 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 1; } # 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 count(*) from (select id from v_shift where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? unionselect id from v_shift_announcer where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? unionselect 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;