Rev 222 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
package RollerCon;## RollerCon support functions...use strict;use cPanelUserConfig;use Exporter 'import';use CGI qw/param header start_html url/;use CGI::Cookie;use DBI;use WebDB;$SIG{__WARN__} = sub { warn sprintf("[%s] ", scalar localtime), @_ };$SIG{__DIE__} = sub { die sprintf("[%s] ", scalar localtime), @_ };our @EXPORT = qw( $ORCUSER $SYSTEM_EMAIL getRCDBH getAccessLevels authDB max authenticate canView getShiftRef getShiftDepartment getClassID getDepartments convertDepartments convertTime getSchedule getRCid getSetting getUser getUserEmail getUserDerbyName getYears printRCHeader changeShift modShiftTime signUpCount signUpEligible findConflict changeLeadShift sendNewUserEMail logit validate_emt);checkQueue (100); # without a number here, the queue functionality is disabled / bypassedmy $dbh = WebDB::connect ("vorc");sub getRCDBH {return $dbh;}our $ORCUSER;our $SYSTEM_EMAIL = 'rollercon.vorc@gmail.com';use constant {NOONE => 0,USER => 1,VOLUNTEER => 1,LEAD => 2,MANAGER => 3,DIRECTOR => 4,SYSADMIN => 5,ADMIN => 5};sub getAccessLevels {my %AccessLevels = (-1 => "Locked",0 => "Pending",# 1 => "Volunteer",1 => "User",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 $activationcode = 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';return $result;} elsif ($RCDBIDHASH->{'password'} ne $encpass) {$result->{ERRMSG} = "Incorrect Password!";$result->{cookie_string} = '';$result->{RCid} = $RCDBIDHASH->{'RCid'};logit($RCDBIDHASH->{'RCid'}, "Incorrect Password");$result->{authenticated} = 'false';return $result;} elsif ($RCDBIDHASH->{'activation'} ne "active") {# It's an inactive account...if ($activationcode eq "resend") {# warn "Resending activation code...";sendNewUserEMail ("New User", $RCDBIDHASH);$result->{ERRMSG} = "Activation code resent. Please check your email.";$result->{cookie_string} = "${id}&${encpass}&0";$result->{RCid} = $RCDBIDHASH->{'RCid'};logit($RCDBIDHASH->{'RCid'}, "Activation code resent.");$result->{authenticated} = 'inactive';return $result;} elsif ($activationcode) {# They sent an activation codeif ($activationcode eq $RCDBIDHASH->{'activation'}) {# ...and it was good.$dbh->do ("update official set activation = 'active', access = 1, last_login = now() where RCid = ? and activation = ?", undef, $RCDBIDHASH->{'RCid'}, $activationcode);logit($RCDBIDHASH->{'RCid'}, "Activated their account and logged In");# sendNewUserEMail ("Activate", $RCDBIDHASH);$RCDBIDHASH->{'access'} = 1;$RCDBIDHASH->{'activation'} = "active";$MAXACCESS = max ($MAXACCESS, 1);} else {# ...but it wasn't good.$result->{ERRMSG} = "Activation failed, invalid code submitted.";$result->{cookie_string} = "${id}&${encpass}&0";;$result->{RCid} = $RCDBIDHASH->{'RCid'};logit($RCDBIDHASH->{'RCid'}, "Activation failed, invalid code submitted.");$result->{authenticated} = 'inactive';return $result;}} else {# No activation code was submitted.$result->{ERRMSG} = "Inactive account! Please check your email for activation link/code." unless $result->{ERRMSG};$result->{cookie_string} = "${id}&${encpass}&0";$result->{RCid} = $RCDBIDHASH->{'RCid'};logit($RCDBIDHASH->{'RCid'}, "Login attempted without activation code.");$result->{authenticated} = 'inactive';return $result;}}if ($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 = now() where RCid = ?", undef, $RCDBIDHASH->{'RCid'}) if $src eq "form";$result->{authenticated} = 'true';$ORCUSER = $RCDBIDHASH;$ORCUSER->{MVPid} = getUser($ORCUSER->{RCid})->{MVPid};$ORCUSER->{emt_verified} = getUser($ORCUSER->{RCid})->{emt_verified};}return $result;}sub max {my ($max, $next, @vars) = @_;return $max if not $next;return max( $max > $next ? $max : $next, @vars );}sub inQueue {my $item = shift;my $array = shift;my $position = 1;foreach (@{$array}) {if ($item eq $_) {return $position;} else {$position++;}}return 0;}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');my $RCqueueID = CGI::cookie('RCQUEUEID') // WebDB::trim CGI::param('RCqueueID') // "";$FORM{'ID'} = WebDB::trim $query->param('userid') || '';$FORM{'PASS'} = WebDB::trim $query->param('pass') || '';$FORM{'SUB'} = $query->param('login') || '';$FORM{'activate'} = WebDB::trim $query->param('activate') // '';if ($RCAUTH) {# We have an authenication cookie. Double-check itmy ($RCID, $RCPASS, $RCLVL) = split /&/, $RCAUTH;$authenticated = authDB('cookie', $RCID, $RCPASS, $MINLEVEL, $FORM{'activate'});} elsif ($FORM{'SUB'}) {# a log in form was submitedif ($FORM{'SUB'} eq "Submit") {$authenticated = authDB('form', $FORM{'ID'}, $FORM{'PASS'}, $MINLEVEL, $FORM{'activate'});} elsif ($FORM{'SUB'} eq "New User") {# Print the new user form and exit}} else {$authenticated->{authenticated} = 'false';}if ($authenticated->{authenticated} eq 'true') {use Digest::MD5 qw/md5_hex/;my $sessionid = md5_hex ($ORCUSER->{email});# Limit how long users are allowed to stay logged in at once.my ($session_length) = $dbh->selectrow_array ("select timestampdiff(MINUTE, last_login, now()) from official where RCid = ?", undef, $ORCUSER->{RCid});if ($session_length > getSetting ("MAX_SESSION_MINUTES")) {$ENV{'QUERY_STRING'} = "LOGOUT";$authenticated->{ERRMSG} = "Maximum session time exceeded.<br>";}my $qdbh = WebDB::connect ("session");if ($ENV{'QUERY_STRING'} eq "LOGOUT") {# warn "logging $ORCUSER->{derby_name} out...";$authenticated->{ERRMSG} .= "Logged Out.<br>";$authenticated->{cookie_string} = "";$authenticated->{authenticated} = 'false';$ENV{REQUEST_URI} =~ s/LOGOUT//;logit ($ORCUSER->{RCid}, "Logged Out");$dbh->do ("update official set last_active = ? where RCid = ?", undef, undef, $ORCUSER->{RCid});$qdbh->do ("delete from session where sessionid = ?", undef, $sessionid);$ORCUSER = "";} else {$dbh->do ("update official set last_active = now() where RCid = ?", undef, $ORCUSER->{RCid});$qdbh->do ("replace into session (RCid, sessionid, timestamp, email) values (?, ?, now(), ?)", undef, $ORCUSER->{RCid}, $sessionid, $ORCUSER->{email});$qdbh->do ("delete from queue where queueid = ?", undef, $RCqueueID) if $RCqueueID;return $authenticated->{cookie_string};}$qdbh->disconnect;}# 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=>$authenticated->{cookie_string},-expires=>"+30m");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.}if ($RCqueueID) {my $RCQUEUE_cookie = CGI::Cookie->new(-name=>'RCQUEUEID',-value=>"",-expires=>"+0m");print header(-cookie=>[$RCAUTH_cookie,$RCQUEUE_cookie]);} else {print header(-cookie=>$RCAUTH_cookie);}printRCHeader("Please Sign In");print<<authpage;<form action="$ENV{REQUEST_URI}" method=POST name=Req id=Req><input type=hidden name=RCqueueID value=$RCqueueID><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}authpageif ($ENV{'QUERY_STRING'} eq "LOGOUT") {print "<TR><TD colspan=2> </TD></TR>";print "<TR><TD colspan=2><button onClick=\"location.href='';\">Log In</button></TD></TR>";print "</TABLE></BODY></HTML>";exit;}if ($authenticated->{authenticated} eq "inactive") {print<<activationpage;<TR><TD colspan=2 align=center> </TD></TR><TR><TD align=right><B>Activation Code:</TD><TD><INPUT type=text id=activate name=activate></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='' onClick='document.getElementById("activate").value="resend"; Req.submit(); return false;'>[Resend your activation email]</A></TD></TR><TR><TD colspan=2 align=center><A HREF='' onClick="location.href='?LOGOUT';">[Log Out]</A></TD></TR></TABLE></FORM>activationpage} else {print<<authpage2;<TR><TD align=right><B>Email Address:</TD><TD><INPUT type=text id=login name=userid></TD></TR><TR><TD align=right><B>Password:</TD><TD><INPUT type=password name=pass></TD></TR><TR><TD></TD><TD><input type=hidden name=activate id=activate value=$FORM{'activate'}><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/view_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>authpage2}#foreach (keys %ENV) {# print "$_: $ENV{$_}<br>";#}# &JScript;exit;}sub checkQueue {my $max_users = shift;return unless $max_users =~ /^\d+$/;return if $ENV{'QUERY_STRING'} eq "SKIPQUEUE";my $RCAUTH = CGI::cookie('RCAUTH') // "";my $qdbh = WebDB::connect ("session");if ($RCAUTH) {# If the user is already logged in, bypass the queue check.my ($email, $RCPASS, $RCLVL) = split /&/, $RCAUTH;my ($active) = $qdbh->selectrow_array ("select count(*) from session where email = ? and timestampdiff(minute, timestamp, now()) < 30", undef, $email);return if $active;}my ($active_users) = $qdbh->selectrow_array ("select count(*) from session where timestampdiff(minute, timestamp, now()) < 30");my ($current_wait) = $qdbh->selectrow_array ("select timestampdiff(minute, timestamp, now()) from queue where timestampdiff(minute, last_seen, now()) < 7 and (timestamp <> last_seen or timestampdiff(second, last_seen, now()) <= 60) limit 1");my @queued_users;push @queued_users, map { @{$_} } @{ $qdbh->selectall_arrayref ("select queueid from queue where timestampdiff(minute, last_seen, now()) < 7 and (timestamp <> last_seen or timestampdiff(second, last_seen, now()) <= 60) order by timestamp") };my $RCqueueID = CGI::cookie('RCQUEUEID') // WebDB::trim CGI::param('RCqueueID') // "";$RCqueueID = "" unless inQueue ($RCqueueID, \@queued_users);my $your_wait = 0;if ($active_users >= $max_users) {# We are at max users. People have to wait.if (!$RCqueueID) {use Digest::MD5 qw/md5_hex/;$RCqueueID = time () ."-". md5_hex (rand ());push @queued_users, $RCqueueID;$qdbh->do ("replace into queue (queueid, timestamp, last_seen) values (?, now(), now())", undef, $RCqueueID);} else {($your_wait) = $qdbh->selectrow_array ("select timestampdiff(minute, timestamp, now()) from queue where queueid = ?", undef, $RCqueueID);$qdbh->do ("update queue set last_seen = now() where queueid = ?", undef, $RCqueueID);}printQueuePage ($RCqueueID, "(".inQueue ($RCqueueID, \@queued_users)." of ".scalar @queued_users." users)", $current_wait - $your_wait);exit;} elsif (scalar @queued_users) {# There are users in queue...if (!$RCqueueID) {# If you're not already in queue, get in line.use Digest::MD5 qw/md5_hex/;$RCqueueID = time () ."-". md5_hex (rand ());push @queued_users, $RCqueueID;$qdbh->do ("replace into queue (queueid, timestamp, last_seen) values (?, now(), now())", undef, $RCqueueID);} else {($your_wait) = $qdbh->selectrow_array ("select timestampdiff(minute, timestamp, now()) from queue where queueid = ?", undef, $RCqueueID);$qdbh->do ("update queue set last_seen = now() where queueid = ?", undef, $RCqueueID);}my $queue_position = inQueue ($RCqueueID, \@queued_users);if ($queue_position > ($max_users - $active_users)) {# If you're not at the head of the line, continue to wait.printQueuePage ($RCqueueID, "($queue_position of ".scalar @queued_users." users)", $current_wait - $your_wait);exit;}}return;}sub printQueuePage {my $RCqueueID = shift;my $queue_position = shift;my $wait_time = shift;print header(-cookie=>CGI::Cookie->new(-name=>'RCQUEUEID',-value=>$RCqueueID,-expires=>"+5m"));printRCHeader("is Busy");print<<busy;<P><b><font size=+2>Sorry, we are full right now.</font></P><P>You are in queue $queue_position.</P><div><ul><li>Current wait time is about $wait_time minute(s).</li><li>This page will refresh every 30 seconds.</li><li>When it's your turn to log in, you'll see the username/password boxes.</li><li>If you don't log in within five [5] minutes, or if you leave this page, you will likely lose your place in line.</li><li>Please LOG OUT of VORC when you are done so that others can log in.</li></ul></div></BODY><SCRIPT language="JavaScript"><!--// Refresh the page after a delaysetTimeout(function(){location.replace(location.href);}, 30000); // 30000 milliseconds = 30 seconds//--></SCRIPT></HTML>busyreturn;}sub canView {my $A = shift // "";my $B = shift // "";# Is A a lead or higher of one of B's Depts? (or they're looking at themselves)# parameters should be a Hashref to the users' detailsreturn 1 if $A->{access} > 4 or $A->{RCid} == $B->{RCid}; # viewer and target are the same person or it's a SysAdmin.my $ADept = ref $A->{department} eq "HASH" ? $A->{department} : convertDepartments($A->{department});my $BDept = ref $B->{department} eq "HASH" ? $B->{department} : convertDepartments($B->{department});foreach (keys %{$BDept}) {if ($ADept->{$_} > 1) { # A is a Lead or higher of one of B's departmentsreturn 1;}}if ($ADept->{MVP} >= RollerCon::LEAD and $B->{MVPid}) {# MVP Volunteers can see user details for people with MVP Passesreturn 1;}return 0;}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;if ($role =~ /^CLA/) {$dept = "CLA";} else {($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 getClassID {my $shift = shift // "";return unless $shift =~ /^\d+$/;my $shiftref = getShiftRef ($shift);my ($classid) = $dbh->selectrow_array ("select id from class where date = ? and start_time = ? and location = ?", undef, $shiftref->{date}, $shiftref->{start_time}, $shiftref->{location});return $classid unless !$classid;warn "ERROR: No class.id found for shift $shiftref->{id}";return "";}sub getShiftRef {my $shiftID = shift // "";return unless $shiftID =~ /^\d+$/;my ($shiftref) = $dbh->selectrow_hashref ("select * from shift where id = ?", undef, $shiftID);return $shiftref unless $shiftref->{id} != $shiftID;warn "ERROR: Couldn't find shift with ID [$shiftID]";return "";}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;}$output = {} unless ref $output eq "HASH";}return $output;}sub convertTime {my $time = shift || return;if ($time =~ / - /) {return join " - ", map { convertTime ($_) } split / - /, $time;}$time =~ s/^(\d{1,2}:\d{2}):\d{2}$/$1/;$time =~ s/^0//;if ($ORCUSER->{timeformat} eq "24hr") {if ($time =~ /^\d{1,2}:\d{2}$/) { return $time; }} else {my ($hr, $min) = split /:/, $time;my $ampm = " am";if ($hr >= 12) {$hr -= 12 unless $hr == 12;$ampm = " pm";} elsif ($hr == 0) {$hr = 12;}return $hr.":".$min.$ampm;}}sub getSchedule {my $RCid = shift // return "ERROR: No RCid provided to getSchedule";my $filter = shift // "";my $output = shift // "";my $year = 1900 + (localtime)[5];my @whereclause;if ($filter eq "all") {push @whereclause, "year(date) >= year(now())";} elsif ($filter eq "prior") {push @whereclause, "year(date) < year(now())";} 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 = ? unionselect id, date, dayofweek, location, time, role, name as teams, 'mvpclass' as signup, 'CLA' as dept, 0 as volhours from v_class_signup_new where RCid = ?) temp$where order by date, time");$sth->execute($RCid, $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} or $s->{dept} eq "CLA") {# it's a time-based shiftif ($s->{dept} eq "PER") {if ($RCid eq $ORCUSER->{RCid}) {# DROP$s->{buttons} = $h->button ({ onClick=>"event.stopPropagation(); if (confirm('Really? You want to delete this personal time?')==true) { location.href='personal_time.pl?choice=Delete&id=$s->{id}'; return false; }" }, "DEL")." ".$h->button ({ onClick=>"event.stopPropagation(); location.href='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)) {# DROPmy ($shiftORclass, $linkargs) = ("shift", "");if ($s->{dept} eq "CLA") {$shiftORclass = "class";$linkargs = "&role=$s->{role}";$s->{role} = $s->{teams};$s->{teams} = "";}$s->{buttons} = $h->button ({ onClick=>"if (confirm('Really? You want to drop this $shiftORclass?')==true) { window.open('make_shift_change.pl?change=del&RCid=$RCid&id=$s->{id}$linkargs','Confirm Class 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}$linkargs','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "NO SHOW");}}# $hours += $s->{volhours} unless $s->{dept} eq "PER" or $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});$s->{time} = convertTime $s->{time};if ($s->{dept} eq "PER") {push @shifts, $h->li ({ onClick => "location.replace('personal_time.pl?id=$s->{id}');", class=> $s->{date} eq $dt ? "highlighted" : "shaded" }, $h->div ({ class=>"lisp0" }, [ $h->div ({ class=>"liLeft" }, join ' ', ($s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, $s->{dept} eq "CLA" ? "MVP Class:" : getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams})), $h->div ({ class=>"liRight" }, $s->{buttons}) ]));} else {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}, $s->{dept} eq "CLA" ? "MVP Class:" : getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams})), $h->div ({ class=>"liRight" }, $s->{buttons}) ]));}$hours += $s->{volhours} unless $s->{dept} eq "PER" or $s->{dept} eq "CLA";}if ($output eq "hours") {return $hours;}if (scalar @shifts) {return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);} elsif ($filter eq "prior") {return $h->p ({ class=>"hint" }, "[nothing to see here]");} 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 v_official where RCid = ?");} else {$sth = $dbh->prepare("select * from v_official where email = ?");}$sth->execute($ID);my $user = $sth->fetchrow_hashref;map { $user->{$_} = "" unless $user->{$_} } keys %{$user};return $user->{RCid} ? $user : "";}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 from (select distinct year(date) as year from shift union select distinct year(date) as year from game union select distinct year(date) as year from class union select year(now()) as year) years order by year");# 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");$logout .= $h->button ({ onClick=>"location.href='?LOGOUT';" }, "Log Out");my $loggedinas = $ORCUSER ? "Currently logged in as: ".$h->a ({ href=>"/schedule/view_user.pl?submit=View&RCid=$ORCUSER->{RCid}" }, $ORCUSER->{derby_name}).$h->br.$logout : "";# print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );my $ANALYTICS = <<MATOMO;var _mtm = window._mtm = window._mtm || [];_mtm.push({'mtm.startTime': (new Date().getTime()), 'event': 'mtm.Start'});(function() {var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];g.async=true; g.src='https://analytics.whump.org/js/container_to4NCtvM.js'; s.parentNode.insertBefore(g,s);})();MATOMOprint $h->open ("html");print $h->head ([$h->title ("vORC - $PAGE_TITLE"),$h->link ({ rel => "stylesheet",type => "text/css",href => "/style.css" }),# $h->script ($ANALYTICS)]);print $h->open ("body");# print $h->img ({referrerpolicy=>"no-referrer-when-downgrade", src=>"https://analytics.whump.org/matomo.php?idsite=2&rec=1", style=>"border:0", alt=>""});#<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) = @_;if ($shift_id =~ /(am|pm)/) {my ($td, $st, $tl) = split /\|/, $shift_id;my ($hr, $min, $ampm) = split /:|\s/, $st;if ($ampm eq "pm") { $hr += 12; }elsif ($ampm eq "am" and $hr == 12) { $hr = "00" }$st = $hr.":".$min;$shift_id = join "|", ($td, $st, $tl);} else {$shift_id =~ s/(\d+:\d+):00/$1/;}#warn join " - ", $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";if ($change eq "del") {($shift_id, $role) = $dbh->selectrow_array ("select id, role from v_class_signup_new where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id);} else {if ($change eq "override") {($shift_id, $role) = $dbh->selectrow_array ("select id, concat('CLA-', max(cast(substring_index(role, '-', -1) as UNSIGNED)) +1) as role from v_class_signup_new where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id) unless $change ne "override";} else {($shift_id, $role) = $dbh->selectrow_array ("select id, concat('CLA-', max(cast(substring_index(role, '-', -1) as UNSIGNED)) +1) as role, count(role), capacity from v_class_signup_new where date = ? and start_time = ? and location = ? having capacity > count(role)", undef, split /\|/, $shift_id);}}$role = "CLA-1" unless $role; # If no one has signed up for the class yet, the SQL above doesn't retrieve the first available}# my $game_based = $role ? "game" : "shift";my $game_based = $role =~ /^CLA-/ ? "class" : $role ? "game" : "shift";my $sth;if ($change eq "add" or $change eq "override") {my $taken;if ($department eq "CLA") {($taken) = $shift_id ? 0 : 1;} elsif ($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 or assignee_id <> "")', 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 (($department eq "CLA" and $ORCUSER->{department}->{MVP} >= 2) or $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 or $ORCUSER->{access} >= 5) {# Managers can sign up for as many shifts within their own department as they like...$leadership_change = 1;}if ($change eq "add") {if ($department eq "CLA" and !getUser($user_id)->{MVPid}) {return "<br>Denied! User ($user_id) does not have an MVP Pass!<br>\n";} elsif ($department ne "CLA" and getUser($user_id)->{department} 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";} elsif ($department eq "EMT" and getUser($user_id)->{emt_verified} == 0) {return "<br>Denied! User ($user_id) has not had their EMT status verified!<br>\n";}}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;if ($department ne "CLA") {($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";}} else {$game_type = "class";}# 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_class_signup_new where RCid = ? and year(date) = year(now())", undef, $user_id);# ($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" or $department eq "ANN") and $game_type eq "full length" and !$leadership_change) {my $dept_table = $department eq 'OFF' ? "v_shift_officiating" : "v_shift_announcer";my ($full_length_count) = $dbh->selectrow_array ("select count(*) from $dept_table where RCid = ? and gtype = 'full length' and year(date) = year(now())", undef, $user_id);my $full_length_max = getSetting("MAX_FULL_LENGTH_SIGNUP_".$department);if ($full_length_count >= $full_length_max) {my $errormsg = "<br>Denied! You may only sign up to ".($department eq 'OFF' ? "officiate" : "announce")." $full_length_max $game_type game(s) (total)!<br>\n";return $errormsg;}}}my @DBARGS;if ($game_based eq "game" or $game_based eq "class") {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);}}my $wb_act_code;if ($change eq "del" and $department eq "CLA") {($wb_act_code) = $dbh->selectrow_array ("select wb_ticket_act from assignment where Gid = ? and RCid = ? and role like ?", undef, $DBARGS[0], $DBARGS[2], 'CLA-%');}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";if ($department eq "CLA") {print "Success!...<br>You've signed up for $daily_count class(es) (you're currently allowed to sign up for $MAXSHIFTS).<br>\n";updateWRSTBND ($change, $wb_act_code, $DBARGS[0], $DBARGS[2]);} else {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 {if ($department eq "CLA") {return "<br><b>You did not get the class</b>, most likely because it filled up while you were looking.<br>\nERROR: ", $sth->errstr();} 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 updateWRSTBND {my ($change, $wb_act_code, $shift_id, $user_id) = @_;use REST::Client;use JSON;my $headers = { Authorization => '601037851507c624' };my $client = REST::Client->new();$client->setHost('https://core.wrstbnd.io');my ($accountid) = $dbh->selectrow_array ("select wrstbnd_accountid from RCid_ticket_link left join ticket on MVPid = id where RCid = ? and year = year(now())", undef, $user_id);if ($change eq "add" or $change eq "override") {my ($classid) = $dbh->selectrow_array ("select wrstbnd_id from class where id = ?", undef, $shift_id);my $body = {"eventId" => "event_893C6u5olU","activeStatus" => "active","ticketTypeId" => $classid};my $json_body = encode_json $body;$client->POST('/rest/core/v1/ticket',$json_body,$headers);my $response = from_json($client->responseContent());my $activationCode = $response->{activationCode};my @add_response = `/bin/curl --location --request POST 'https://core.wrstbnd.io/rest/core/v1/assign' --header 'Authorization: 601037851507c624' --form accountid=$accountid --form ticketactcode=$activationCode --output /dev/null --silent --write-out '%{http_code}\n'`;my $add_response = $add_response[$#add_response];chomp $add_response;$dbh->do ("update assignment set wb_ticket_act = ? where Gid = ? and RCid = ? and role like ?", undef, $activationCode, $shift_id, $user_id, 'CLA-%') unless $add_response ne "200";return;} elsif ($change eq "del") {my $activationCode = $wb_act_code;my $del_response = `/bin/curl --location --request DELETE 'https://core.wrstbnd.io/rest/core/v1/assign' --header 'Authorization: 601037851507c624' --form accountid=$accountid --form ticketactcode=$activationCode --output /dev/null --silent --write-out '%{http_code}\n'`;}}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 $DEPTHASH = getDepartments ();if ($dept and !exists $DEPTHASH->{$dept}) {my %reverso = reverse %{$DEPTHASH};$dept = $reverso{$dept};}my $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$dept);$limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $limit;if (lc $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") {my $classid = $t->{id};$t->{start_time} =~ s/^(\d+:\d+):00$/$1/;($t->{id}) = $dbh->selectrow_array ("select id from v_class_new where date = ? and location = ? and start_time = ?", undef, $t->{date}, $t->{location}, $t->{start_time});$t->{dept} = "CLA";$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" or $dept eq "ANN")) {my $table = $dept eq "OFF" ? "v_shift_officiating" : "v_shift_announcer";my ($full_length_count) = $dbh->selectrow_array ("select count(*) from $table where RCid = ? and gtype = 'full length' and year(date) = year(now())", undef, $user->{RCid});if ($full_length_count >= getSetting ("MAX_FULL_LENGTH_SIGNUP_".$dept)) {return 0;}}if (lc $t->{signup} ne "selected" and $user->{$limitkey} < $limit) {return 1;} else {return 0;}} else {if ($dept eq "CLA") {# MVP Class Sign-upreturn 0 unless $user->{MVPid};my $class_limit = getSetting ("MAX_CLASS_SIGNUP");my ($class_count) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where RCid = ? and year(date) = year(now())", undef, $user->{RCid});return 0 unless $class_count < $class_limit;} else {if ($user->{department}->{$dept} < 1) { return 0; }}if (lc $t->{type} eq "lead" and $user->{department}->{$dept} < 2) { return 0; }if (lc $t->{type} eq "manager" and $user->{department}->{$dept} < 3) { return 0; }if ($dept eq "EMT" and $user->{emt_verified} == 0) { return 0; }if (lc $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, $existing, $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 "class") {($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where id = ? and RCid = ?", undef, $gid, $rcid);if ($conflicts) { return "CLA:".$gid; } # no need to keep looking...($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_class_new where id = ?", undef, $gid);} elsif ($type eq "personal") {($date, $start, $end, $existing) = @{ $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) as conflict from v_shift where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? unionselect concat('CLA:', id) as conflict from v_class_signup_new where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? unionselect concat('ANN-', id) as conflict from v_shift_announcer where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? unionselect concat('OFF-', id) as conflict from v_shift_officiating where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? ) alltableswhere conflict <> ?",undef, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, "PER-".$existing);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);}sub sendNewUserEMail {my $context = shift;my $data = shift;use RCMailer;use HTML::Tiny;my $h = HTML::Tiny->new( mode => 'html' );my $depts = getDepartments (); # HashRef of the department TLAs -> Display Names...my $AccessLevel = getAccessLevels;my $email = $data->{email};my $subject = 'RollerCon VORC - New User';my $body;if ($context eq "New User") {$subject .= " Request";my $activationlink = url ()."?activate=".$data->{activation};$body = $h->p ("Greetings,");$body .= $h->p ("It appears as though you've registered a new account in RollerCon's VORC system with the following information:");$body .= $h->table ([$h->tr ([$h->td (" ", "Derby Name:", $data->{derby_name})]),$h->tr ([$h->td (" ", "Full Name:", $data->{real_name})]),$h->tr ([$h->td (" ", "Pronouns:", $data->{pronouns})]),$h->tr ([$h->td (" ", "TShirt Size:", $data->{tshirt})]),$h->tr ([$h->td (" ", "Email Address:", $data->{email})]),$h->tr ([$h->td (" ", "Phone:", $data->{phone})])]);$body .= $h->p ("To validate that you've entered a real (and correct) email address (and that you're not a spam-bot), please click the following link:",$h->a ({ HREF=>$activationlink }, "Activate my VORC Account!"), $h->br,"Or you can copy/paste this into the 'Activation Code' box: ".$data->{activation}, $h->br,"Once activated, you'll be able to log in. If you're looking to volunteer, some departments are automatically enabled. Others need to be manually reviewed and approved.","If you're looking to sign up for MVP Classes, your MVP Ticket needs to be confirmed. Once that happens, you'll receive another email.","If you're new to using vORC, you may want to read this:",$h->a ({ HREF=>"https://volunteers.rollercon.com/info.html" }, "VORC User Info"),"If you didn't make this request, well, you're still the only one who received this email, and you now have an account request. You should probably let us know that someone is messing with you.",$h->br,"--RollerCon HQ".$h->br.'rollercon@gmail.com'.$h->br."rollercon.com");} elsif ($context eq "Activate") {$subject .= " Activated!";my $tempDepartments = convertDepartments ($data->{department});my $printableDepartments = join "\n", map { $depts->{$_}.": ".$AccessLevel->{$tempDepartments->{$_}} } sort keys %{$tempDepartments};$body = "Greetings again,You have been approved to volunteer at RollerCon in the following departments:$printableDepartmentsYou may log into vORC and begin signing up for shifts. Please be considerate of others and don't hogger all of the shifts. If you do, we will find you and randomly drop your shifts.https://volunteers.rollercon.com/schedule/Please note that you are limited to signing up to a number of shifts per day. (Meaning, once you sign up for X shifts, you'll have to wait until tomorrow to sign up for more.) Please understand, while you are a nice, concientious, and good-looking person yourself, who knows how to share, there are others out there that will hogger up all of the shifts. As time goes by and we get closer to the event, we may lift the limit. Who knows?If you've already signed up for your daily limit of shifts, and another shift REALLY strikes your fancy, try dropping one of your shifts. That should allow you to pick up a different one.We'll be adding shifts over time, again to throttle how fast some people (not you, mind you) gobble up the shifts. Check back, maybe even daily.If you're new to using vORC, you may want to read this:https://volunteers.rollercon.com/info.htmlIf you didn't make this request, well, you're still the only one who received this email, and you now have an active account. You should probably let us know that someone is messing with you.-RollerCon Management";} else {return;}# send the messageEmailUser ($email, $subject, $body);}sub validate_emt {my $target = shift // "";my $change = shift // "";if (!$target or !$change) {warn "ERROR: validate_emt() called without a required parameter! target: $target, change: $change";return -1;}my $uservalidate = getUser $target;if (!exists $uservalidate->{RCid}) {warn "ERROR: validate_emt() called on a non-existant user! target: $target, change: $change";return -1;}if ($change eq "add") {if ($uservalidate->{emt_verified}) {warn "ERROR: validate_emt() called to add on a user already verified: $target, change: $change";return -1;} else {$dbh->do ("replace into emt_credential_verified (RCid, date, verified_by) values (?, date(now()), ?)", undef, $target, $ORCUSER->{RCid}) or warn $dbh->errstr;logit ($target, "EMT Credentials Verified");logit ($ORCUSER->{RCid}, "Verified EMT Credentials for $uservalidate->{derby_name} [$target]");}} elsif ($change eq "del") {if (!$uservalidate->{emt_verified}) {warn "ERROR: validate_emt() called to del on a user that isn't verified: $target, change: $change";return -1;} else {$dbh->do ("delete from emt_credential_verified where year(date) = year(now()) and RCid = ?", undef, $target) or warn $dbh->errstr;logit ($target, "EMT Credential Verification removed");logit ($ORCUSER->{RCid}, "Removed EMT Credential verification for $uservalidate->{derby_name} [$target]");}} else {warn "ERROR: validate_emt() called with a bad parameter! target: $target, change: $change";return -1;}}1;