Subversion Repositories VORC

Rev

Rev 252 | Rev 256 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
149 - 1 
package RollerCon;
2 
## RollerCon support functions...
3 
 
4 
use strict;
5 
use cPanelUserConfig;
6 
use Exporter 'import';
7 
use CGI qw/param header start_html url/;
8 
use CGI::Cookie;
9 
use DBI;
10 
use WebDB;
11 
 
12 
$SIG{__WARN__} = sub { warn sprintf("[%s] ", scalar localtime), @_ };
13 
$SIG{__DIE__}  = sub { die  sprintf("[%s] ", scalar localtime), @_ };
14 
 
252 - 15 
our @EXPORT = qw( $ORCUSER $SYSTEM_EMAIL getRCDBH getAccessLevels authDB max authenticate canView getShiftRef getShiftDepartment getClassID getSeminarID getDepartments convertDepartments convertTime getSchedule getRCid getSetting getUser getUserEmail getUserDerbyName getYears printRCHeader changeShift modShiftTime signUpCount signUpEligible findConflict changeLeadShift sendNewUserEMail logit validate_emt);
149 - 16 
 
162 - 17 
checkQueue (100); # without a number here, the queue functionality is disabled / bypassed
149 - 18 
 
153 - 19 
my $dbh = WebDB::connect ("vorc");
149 - 20 
sub getRCDBH {
21 
  return $dbh;
22 
}
23 
our $ORCUSER;
24 
our $SYSTEM_EMAIL = 'rollercon.vorc@gmail.com';
25 
use constant {
26 
    NOONE     => 0,
27 
    USER      => 1,
28 
    VOLUNTEER => 1,
29 
    LEAD      => 2,
30 
    MANAGER   => 3,
31 
    DIRECTOR  => 4,
32 
    SYSADMIN  => 5,
33 
    ADMIN     => 5
34 
  };
35 
 
36 
sub getAccessLevels {
37 
  my %AccessLevels = (
38 
    -1 => "Locked",
39 
 
40 
#    1 => "Volunteer",
41 
    1 => "User",
42 
    2 => "Lead",
43 
    3 => "Manager",
44 
    4 => "Director",
45 
    5 => "SysAdmin"
46 
  );
47 
  return \%AccessLevels;
48 
}
49 
 
50 
sub authDB {
200 - 51 
  my $src = shift;
52 
  my $id = shift;
53 
  my $pass = shift;
54 
  my $level = shift;
55 
  my $activationcode = shift // "";
56 
  my ($result, $encpass);
57 
 
58 
  my $sth = $dbh->prepare("select * from official where email = ?");
59 
  $sth->execute($id);
60 
  my $RCDBIDHASH = $sth->fetchrow_hashref();
61 
 
62 
  if ($src eq "form") {
63 
    my $pwdhan = $dbh->prepare("select password(?)");
64 
    $pwdhan->execute($pass);
65 
    ($encpass) = $pwdhan->fetchrow();
66 
  } else {
67 
    $encpass = $pass;
68 
  }
69 
 
70 
  my $tempDepartments = convertDepartments ($RCDBIDHASH->{department});
71 
  my $MAXACCESS = scalar keys %{ $tempDepartments } ? max ($RCDBIDHASH->{'access'}, values %{ $tempDepartments } ) : $RCDBIDHASH->{'access'};
72 
 
73 
  if (!$RCDBIDHASH->{'RCid'}) {
74 
    $result->{ERRMSG} = "Email Address not found!";
75 
    $result->{cookie_string} = '';
76 
    $result->{RCid} = '';
77 
    logit(0, "Account not found: $id");
78 
    $result->{authenticated} = 'false';
79 
    return $result;
80 
  } elsif ($RCDBIDHASH->{'password'} ne $encpass) {
81 
    $result->{ERRMSG} = "Incorrect Password!";
82 
    $result->{cookie_string} = '';
83 
    $result->{RCid} = $RCDBIDHASH->{'RCid'};
84 
    logit($RCDBIDHASH->{'RCid'}, "Incorrect Password");
85 
    $result->{authenticated} = 'false';
86 
    return $result;
149 - 87 
  } elsif ($RCDBIDHASH->{'activation'} ne "active") {
88 
    # It's an inactive account...
89 
    if ($activationcode eq "resend") {
90 
      # warn "Resending activation code...";
91 
      sendNewUserEMail ("New User", $RCDBIDHASH);
92 
      $result->{ERRMSG} = "Activation code resent. Please check your email.";
200 - 93 
      $result->{cookie_string} = "${id}&${encpass}&0";
94 
      $result->{RCid} = $RCDBIDHASH->{'RCid'};
95 
      logit($RCDBIDHASH->{'RCid'}, "Activation code resent.");
96 
      $result->{authenticated} = 'inactive';
97 
      return $result;
149 - 98 
    } elsif ($activationcode) {
99 
      # They sent an activation code
100 
      if ($activationcode eq $RCDBIDHASH->{'activation'}) {
101 
        # ...and it was good.
102 
        $dbh->do ("update official set activation = 'active', access = 1, last_login = now() where RCid = ? and activation = ?", undef, $RCDBIDHASH->{'RCid'}, $activationcode);
103 
        logit($RCDBIDHASH->{'RCid'}, "Activated their account and logged In");
104 
        # sendNewUserEMail ("Activate", $RCDBIDHASH);
105 
        $RCDBIDHASH->{'access'} = 1;
106 
        $RCDBIDHASH->{'activation'} = "active";
107 
        $MAXACCESS = max ($MAXACCESS, 1);
108 
      } else {
109 
        # ...but it wasn't good.
110 
        $result->{ERRMSG} = "Activation failed, invalid code submitted.";
200 - 111 
        $result->{cookie_string} = "${id}&${encpass}&0";;
112 
        $result->{RCid} = $RCDBIDHASH->{'RCid'};
149 - 113 
        logit($RCDBIDHASH->{'RCid'}, "Activation failed, invalid code submitted.");
200 - 114 
        $result->{authenticated} = 'inactive';
115 
        return $result;
149 - 116 
      }
117 
    } else {
118 
      # No activation code was submitted.
200 - 119 
      $result->{ERRMSG} = "Inactive account! Please check your email for activation link/code." unless $result->{ERRMSG};
120 
      $result->{cookie_string} = "${id}&${encpass}&0";
121 
      $result->{RCid} = $RCDBIDHASH->{'RCid'};
122 
      logit($RCDBIDHASH->{'RCid'}, "Login attempted without activation code.");
123 
      $result->{authenticated} = 'inactive';
124 
      return $result;
149 - 125 
    }
200 - 126 
  }
127 
 
128 
  if ($MAXACCESS < $level) {
129 
    if (getSetting ("MAINTENANCE")) {
130 
      $result->{ERRMSG} = "MAINTENANCE MODE: Logins are temporarily disabled.";
131 
    } else {
132 
      $result->{ERRMSG} = "Your account either needs to be activated, or doesn't have access to this page!";
133 
      logit($RCDBIDHASH->{'RCid'}, "Insufficient Privileges");
134 
    }
135 
    $result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";
136 
    $result->{RCid} = $RCDBIDHASH->{'RCid'};
137 
    $result->{authenticated} = 'false';
138 
  } else {
139 
    $result->{ERRMSG} = '';
140 
    $RCDBIDHASH->{department} = convertDepartments ($RCDBIDHASH->{department});
141 
    $RCDBIDHASH->{'access'} = max ($RCDBIDHASH->{'access'}, values %{$RCDBIDHASH->{department}});
142 
    $result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";
143 
    $result->{RCid} = $RCDBIDHASH->{'RCid'};
144 
    logit($RCDBIDHASH->{'RCid'}, "Logged In") if $src eq "form";
145 
    $dbh->do ("update official set last_login = now() where RCid = ?", undef, $RCDBIDHASH->{'RCid'}) if $src eq "form";
146 
    $result->{authenticated} = 'true';
147 
 
148 
    $ORCUSER = $RCDBIDHASH;
149 
    $ORCUSER->{MVPid} = getUser($ORCUSER->{RCid})->{MVPid};
150 
    $ORCUSER->{emt_verified} = getUser($ORCUSER->{RCid})->{emt_verified};
151 
  }
152 
  return $result;
149 - 153 
}
154 
 
155 
sub max {
156 
    my ($max, $next, @vars) = @_;
157 
    return $max if not $next;
158 
    return max( $max > $next ? $max : $next, @vars );
159 
}
160 
 
161 
sub inQueue {
200 - 162 
  my $item = shift;
163 
  my $array = shift;
164 
  my $position = 1;
165 
  foreach (@{$array}) {
166 
    if ($item eq $_) {
167 
      return $position;
168 
    } else {
169 
      $position++;
170 
    }
171 
  }
172 
  return 0;
149 - 173 
}
174 
 
175 
 
200 - 176 
sub authenticate {                  # Verifies the user has logged in or puts up a log in screen
177 
  my $MAINTMODE = getSetting ("MAINTENANCE");
178 
  my $MINLEVEL = $MAINTMODE ? $MAINTMODE : shift // 1;
179 
 
180 
  my ($ERRMSG, $authenticated, %FORM);
181 
  my $sth = $dbh->prepare("select * from official where email = '?'");
182 
 
183 
  my $query = new CGI;
149 - 184 
# Check to see if the user has already logged in (there should be cookies with their authentication)?
200 - 185 
  my $RCAUTH = $query->cookie('RCAUTH');
186 
  my $RCqueueID = CGI::cookie('RCQUEUEID') // WebDB::trim CGI::param('RCqueueID') // "";
187 
  $FORM{'ID'} = WebDB::trim $query->param('userid') || '';
188 
  $FORM{'PASS'} = WebDB::trim $query->param('pass') || '';
189 
  $FORM{'SUB'} = $query->param('login') || '';
190 
  $FORM{'activate'} = WebDB::trim $query->param('activate') // '';
191 
 
192 
  if ($RCAUTH) {
193 
    # We have an authenication cookie.  Double-check it
194 
    my ($RCID, $RCPASS, $RCLVL) = split /&/, $RCAUTH;
195 
    $authenticated = authDB('cookie', $RCID, $RCPASS, $MINLEVEL, $FORM{'activate'});
196 
  } elsif ($FORM{'SUB'}) {
197 
    # a log in form was submited
198 
    if ($FORM{'SUB'} eq "Submit") {
199 
      $authenticated = authDB('form', $FORM{'ID'}, $FORM{'PASS'}, $MINLEVEL, $FORM{'activate'});
200 
    } elsif ($FORM{'SUB'} eq "New User") {
201 
      # Print the new user form and exit
202 
    }
203 
  } else {
204 
    $authenticated->{authenticated} = 'false';
205 
  }
206 
 
207 
  if ($authenticated->{authenticated} eq 'true') {
149 - 208 
    use Digest::MD5 qw/md5_hex/;
209 
    my $sessionid = md5_hex ($ORCUSER->{email});
210 
 
211 
    # Limit how long users are allowed to stay logged in at once.
212 
    my ($session_length) = $dbh->selectrow_array ("select timestampdiff(MINUTE, last_login, now()) from official where RCid = ?", undef, $ORCUSER->{RCid});
213 
    if ($session_length > getSetting ("MAX_SESSION_MINUTES")) {
214 
      $ENV{'QUERY_STRING'} = "LOGOUT";
215 
      $authenticated->{ERRMSG} = "Maximum session time exceeded.<br>";
216 
    }
217 
 
153 - 218 
    my $qdbh = WebDB::connect ("session");
200 - 219 
    if ($ENV{'QUERY_STRING'} eq "LOGOUT") {
149 - 220 
      # warn "logging $ORCUSER->{derby_name} out...";
221 
      $authenticated->{ERRMSG} .= "Logged Out.<br>";
222 
      $authenticated->{cookie_string} = "";
223 
      $authenticated->{authenticated} = 'false';
224 
      $ENV{REQUEST_URI} =~ s/LOGOUT//;
225 
      logit ($ORCUSER->{RCid}, "Logged Out");
226 
      $dbh->do ("update official set last_active = ? where RCid = ?", undef, undef, $ORCUSER->{RCid});
200 - 227 
      $qdbh->do ("delete from session where sessionid = ?", undef, $sessionid);
149 - 228 
      $ORCUSER = "";
229 
    } else {
200 - 230 
      $dbh->do ("update official set last_active = now() where RCid = ?", undef, $ORCUSER->{RCid});
162 - 231 
      $qdbh->do ("replace into session (RCid, sessionid, timestamp, email) values (?, ?, now(), ?)", undef, $ORCUSER->{RCid}, $sessionid, $ORCUSER->{email});
153 - 232 
      $qdbh->do ("delete from queue where queueid = ?", undef, $RCqueueID) if $RCqueueID;
200 - 233 
      return $authenticated->{cookie_string};
234 
    }
153 - 235 
    $qdbh->disconnect;
200 - 236 
  }
237 
 
238 
 
149 - 239 
# If we get here, the user has failed authentication; throw up the log-in screen and die.
240 
 
200 - 241 
  my $RCAUTH_cookie = CGI::Cookie->new(-name=>'RCAUTH',-value=>$authenticated->{cookie_string},-expires=>"+30m");
149 - 242 
 
243 
  if ($authenticated->{ERRMSG}) {
200 - 244 
    $authenticated->{ERRMSG} = "<TR><TD colspan=2 align=center><font color=red><b>".$authenticated->{ERRMSG}."</b></font>&nbsp</TD></TR>";
245 
    # Log the failed access attempt
149 - 246 
  } else {
200 - 247 
    $authenticated->{ERRMSG} = "";
248 
    # Since there was no ERRMSG, no need to log anything.
149 - 249 
  }
250 
 
251 
  if ($RCqueueID) {
200 - 252 
    my $RCQUEUE_cookie = CGI::Cookie->new(-name=>'RCQUEUEID',-value=>"",-expires=>"+0m");
253 
    print header(-cookie=>[$RCAUTH_cookie,$RCQUEUE_cookie]);
149 - 254 
  } else {
200 - 255 
    print header(-cookie=>$RCAUTH_cookie);
256 
  }
257 
 
258 
  printRCHeader("Please Sign In");
259 
  print<<authpage;
260 
  <form action="$ENV{REQUEST_URI}" method=POST name=Req id=Req>
261 
  <input type=hidden name=RCqueueID value=$RCqueueID>
262 
    <TR><TD colspan=2 align=center><b><font size=+2>Please Sign In</font>
263 
    <TABLE>
264 
    </TD></TR>
265 
    <TR><TD colspan=2>&nbsp</TD></TR>
266 
    $authenticated->{ERRMSG}
149 - 267 
authpage
268 
 
269 
  if ($ENV{'QUERY_STRING'} eq "LOGOUT") {
270 
    print "<TR><TD colspan=2>&nbsp</TD></TR>";
271 
    print "<TR><TD colspan=2><button onClick=\"location.href='';\">Log In</button></TD></TR>";
272 
    print "</TABLE></BODY></HTML>";
273 
    exit;
274 
  }
275 
 
276 
  if ($authenticated->{authenticated} eq "inactive") {
200 - 277 
 
149 - 278 
    print<<activationpage;
279 
      <TR><TD colspan=2 align=center>&nbsp;</TD></TR>
280 
      <TR><TD align=right><B>Activation Code:</TD><TD><INPUT type=text id=activate name=activate></TD></TR>
281 
      <TR><TD></TD><TD><INPUT type=submit name=login value=Submit></TD></TR>
282 
      <TR><TD colspan=2 align=center>&nbsp;</TD></TR>
283 
      <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>
284 
      <TR><TD colspan=2 align=center><A HREF='' onClick="location.href='?LOGOUT';">[Log Out]</A></TD></TR>
285 
      </TABLE></FORM>
286 
activationpage
287 
 
288 
  } else {
200 - 289 
 
149 - 290 
    print<<authpage2;
200 - 291 
      <TR>
292 
        <TD align=right><B>Email Address:</TD><TD><INPUT type=text id=login name=userid></TD>
293 
      </TR>
294 
      <TR>
295 
        <TD align=right><B>Password:</TD><TD><INPUT type=password name=pass></TD>
296 
      </TR>
297 
      <TR><TD></TD><TD><input type=hidden name=activate id=activate value=$FORM{'activate'}><INPUT type=submit name=login value=Submit></TD></TR>
298 
      <TR><TD colspan=2 align=center>&nbsp;</TD></TR>
299 
      <TR><TD colspan=2 align=center><A HREF="/schedule/view_user.pl?submit=New%20User">[register as a new user]</A></TD></TR>
300 
      <TR><TD colspan=2 align=center><A HREF="/schedule/password_reset.pl">[reset your password]</A></TD></TR>
301 
    </TABLE>
302 
    </FORM>
303 
 
304 
    <SCRIPT language="JavaScript">
305 
    <!--
306 
    document.getElementById("login").focus();
307 
 
308 
    function Login () {
309 
      document.getElementById('Req').action = "$ENV{SCRIPT_NAME}";
310 
      document.getElementById('Req').submit.click();
311 
      return true;
312 
    }
313 
 
314 
    //-->
315 
    </SCRIPT>
316 
 
149 - 317 
authpage2
318 
  }
200 - 319 
 
149 - 320 
#foreach (keys %ENV) {
200 - 321 
# print "$_: $ENV{$_}<br>";
149 - 322 
#}
200 - 323 
# &JScript;
324 
  exit;
149 - 325 
}
326 
 
327 
sub checkQueue {
328 
  my $max_users = shift;
162 - 329 
 
149 - 330 
  return unless $max_users =~ /^\d+$/;
200 - 331 
 
332 
  return if $ENV{'QUERY_STRING'} eq "SKIPQUEUE";
333 
 
334 
  my $RCAUTH = CGI::cookie('RCAUTH') // "";
335 
 
336 
  my $qdbh = WebDB::connect ("session");
337 
 
338 
  if ($RCAUTH) {
339 
    # If the user is already logged in, bypass the queue check.
340 
    my ($email, $RCPASS, $RCLVL) = split /&/, $RCAUTH;
341 
    my ($active) = $qdbh->selectrow_array ("select count(*) from session where email = ? and timestampdiff(minute, timestamp, now()) < 30", undef, $email);
153 - 342 
    return if $active;
200 - 343 
  }
344 
 
345 
  my ($active_users) = $qdbh->selectrow_array ("select count(*) from session where timestampdiff(minute, timestamp, now()) < 30");
346 
  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");
347 
  my @queued_users;
163 - 348 
  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") };
200 - 349 
 
350 
  my $RCqueueID = CGI::cookie('RCQUEUEID') // WebDB::trim CGI::param('RCqueueID') // "";
162 - 351 
  $RCqueueID = "" unless inQueue ($RCqueueID, \@queued_users);
200 - 352 
 
353 
  my $your_wait = 0;
149 - 354 
  if ($active_users >= $max_users) {
355 
    # We are at max users. People have to wait.
356 
    if (!$RCqueueID) {
200 - 357 
      use Digest::MD5 qw/md5_hex/;
358 
      $RCqueueID = time () ."-". md5_hex (rand ());
359 
      push @queued_users, $RCqueueID;
360 
      $qdbh->do ("replace into queue (queueid, timestamp, last_seen) values (?, now(), now())", undef, $RCqueueID);
162 - 361 
    } else {
200 - 362 
      ($your_wait) = $qdbh->selectrow_array ("select timestampdiff(minute, timestamp, now()) from queue where queueid = ?", undef, $RCqueueID);
162 - 363 
      $qdbh->do ("update queue set last_seen = now() where queueid = ?", undef, $RCqueueID);
149 - 364 
    }
200 - 365 
 
163 - 366 
    printQueuePage ($RCqueueID, "(".inQueue ($RCqueueID, \@queued_users)." of ".scalar @queued_users." users)", $current_wait - $your_wait);
149 - 367 
    exit;
368 
 
369 
  } elsif (scalar @queued_users) {
370 
    # There are users in queue...
371 
    if (!$RCqueueID) {
372 
      # If you're not already in queue, get in line.
200 - 373 
      use Digest::MD5 qw/md5_hex/;
374 
      $RCqueueID = time () ."-". md5_hex (rand ());
375 
      push @queued_users, $RCqueueID;
376 
      $qdbh->do ("replace into queue (queueid, timestamp, last_seen) values (?, now(), now())", undef, $RCqueueID);
162 - 377 
    } else {
200 - 378 
      ($your_wait) = $qdbh->selectrow_array ("select timestampdiff(minute, timestamp, now()) from queue where queueid = ?", undef, $RCqueueID);
162 - 379 
      $qdbh->do ("update queue set last_seen = now() where queueid = ?", undef, $RCqueueID);
149 - 380 
    }
200 - 381 
 
382 
    my $queue_position = inQueue ($RCqueueID, \@queued_users);
149 - 383 
    if ($queue_position > ($max_users - $active_users)) {
384 
      # If you're not at the head of the line, continue to wait.
163 - 385 
      printQueuePage ($RCqueueID, "($queue_position of ".scalar @queued_users." users)", $current_wait - $your_wait);
149 - 386 
      exit;
387 
    }
388 
  }
200 - 389 
 
149 - 390 
  return;
391 
}
392 
 
393 
sub printQueuePage {
394 
  my $RCqueueID = shift;
395 
  my $queue_position = shift;
162 - 396 
  my $wait_time = shift;
149 - 397 
 
398 
  print header(-cookie=>CGI::Cookie->new(-name=>'RCQUEUEID',-value=>$RCqueueID,-expires=>"+5m"));
399 
  printRCHeader("is Busy");
400 
  print<<busy;
401 
    <P><b><font size=+2>Sorry, we are full right now.</font></P>
402 
    <P>You are in queue $queue_position.</P>
403 
    <div><ul>
200 - 404 
  <li>Current wait time is about $wait_time minute(s).</li>
153 - 405 
    <li>This page will refresh every 30 seconds.</li>
149 - 406 
    <li>When it's your turn to log in, you'll see the username/password boxes.</li>
407 
    <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>
164 - 408 
    <li>Please LOG OUT of VORC when you are done so that others can log in.</li>
149 - 409 
    </ul></div>
410 
    </BODY>
411 
    <SCRIPT language="JavaScript">
200 - 412 
    <!--
153 - 413 
    // Refresh the page after a delay
149 - 414 
      setTimeout(function(){
415 
        location.replace(location.href);
153 - 416 
      }, 30000); // 30000 milliseconds = 30 seconds
149 - 417 
    //-->
418 
    </SCRIPT>
419 
    </HTML>
420 
busy
421 
  return;
422 
}
423 
 
424 
sub canView {
200 - 425 
  my $A = shift // "";
426 
  my $B = shift // "";
427 
  # Is A a lead or higher of one of B's Depts? (or they're looking at themselves)
428 
  # parameters should be a Hashref to the users' details
429 
 
430 
  return 1 if $A->{access} > 4 or $A->{RCid} == $B->{RCid}; # viewer and target are the same person or it's a SysAdmin.
431 
 
432 
  my $ADept = ref $A->{department} eq "HASH" ? $A->{department} : convertDepartments($A->{department});
433 
  my $BDept = ref $B->{department} eq "HASH" ? $B->{department} : convertDepartments($B->{department});
434 
 
435 
  foreach (keys %{$BDept}) {
436 
    if ($ADept->{$_} > 1) { # A is a Lead or higher of one of B's departments
437 
      return 1;
438 
    }
439 
  }
440 
 
441 
  if ($ADept->{MVP} >= RollerCon::LEAD and $B->{MVPid}) {
442 
    # MVP Volunteers can see user details for people with MVP Passes
443 
    return 1;
444 
  }
445 
 
446 
  return 0;
149 - 447 
}
448 
 
449 
sub getShiftDepartment {
450 
  my $shiftID = shift // "";
451 
  my $dept;
452 
 
453 
  if ($shiftID =~ /^\d+$/) {
454 
    ($dept) = $dbh->selectrow_array ("select dept from shift where id = ?", undef, $shiftID);
455 
  } else {
456 
    my ($id, $role) = split /-/, $shiftID;
457 
    if ($role =~ /^CLA/) {
458 
      $dept = "CLA";
459 
    } else {
460 
      ($dept) = $dbh->selectrow_array ("select distinct department from staff_template where role like ?", undef, $role.'%');
461 
    }
462 
  }
463 
#  } elsif ($shiftID =~ /^\d+-ANN/) {
464 
#    $dept = "ANN";
465 
#  } else {
466 
#    $dept = "OFF";
467 
#  }
468 
 
469 
  return $dept;
470 
}
471 
 
472 
sub getClassID {
473 
  my $shift = shift // "";
474 
  return unless $shift =~ /^\d+$/;
475 
 
476 
  my $shiftref = getShiftRef ($shift);
477 
  my ($classid) = $dbh->selectrow_array ("select id from class where date = ? and start_time = ? and location = ?", undef, $shiftref->{date}, $shiftref->{start_time}, $shiftref->{location});
478 
  return $classid unless !$classid;
479 
 
480 
  warn "ERROR: No class.id found for shift $shiftref->{id}";
481 
  return "";
482 
}
483 
 
252 - 484 
sub getSeminarID {
485 
  my $shift = shift // "";
486 
  return unless $shift =~ /^\d+$/;
487 
 
488 
  my $shiftref = getShiftRef ($shift);
489 
  my ($classid) = $dbh->selectrow_array ("select id from seminar where date = ? and start_time = ? and location = ?", undef, $shiftref->{date}, $shiftref->{start_time}, $shiftref->{location});
490 
  return $classid unless !$classid;
491 
 
492 
  warn "ERROR: No seminar.id found for shift $shiftref->{id}";
493 
  return "";
494 
}
495 
 
149 - 496 
sub getShiftRef {
497 
  my $shiftID = shift // "";
498 
  return unless $shiftID =~ /^\d+$/;
499 
 
500 
  my ($shiftref) = $dbh->selectrow_hashref ("select * from shift where id = ?", undef, $shiftID);
501 
  return $shiftref unless $shiftref->{id} != $shiftID;
502 
 
503 
  warn "ERROR: Couldn't find shift with ID [$shiftID]";
504 
  return "";
505 
}
506 
 
507 
sub getDepartments {
508 
  my $RCid = shift // "";
509 
  # If we get an RCid, return the list of departments and levels for that user.
510 
  #   Otherwise (no parameter), return the list of departments with their display names.
511 
 
200 - 512 
  if ($RCid) {
513 
    my $sth = $dbh->prepare("select department from official where RCid = ?");
514 
    $sth->execute($RCid);
515 
    my ($dlist) = $sth->fetchrow;
516 
    return convertDepartments ($dlist);
517 
  } else {
518 
    my %HASH;
519 
    my $sth = $dbh->prepare("select TLA, name from department");
520 
    $sth->execute();
521 
    while (my ($tla, $name) = $sth->fetchrow) {
522 
      $HASH{$tla} = $name;
149 - 523 
    }
524 
    return \%HASH;
525 
  }
526 
 
527 
}
528 
 
529 
sub convertDepartments {
530 
  # For the department membership, converts the DB string back and forth to a hashref...
531 
  my $input = shift // "";
532 
  my $output;
200 - 533 
 
149 - 534 
  if (ref $input eq "HASH") {
535 
    $output = join ":", map { $_."-".$input->{$_} } sort keys %{$input};
536 
  } else {
200 - 537 
    foreach (split /:/, $input) {
538 
      my ($tla, $level) = split /-/;
539 
      $output->{$tla} = $level;
149 - 540 
    }
541 
    $output = {} unless ref $output eq "HASH";
542 
  }
543 
 
544 
  return $output;
545 
}
546 
 
547 
sub convertTime {
548 
  my $time = shift || return;
549 
 
550 
  if ($time =~ / - /) {
551 
    return join " - ", map { convertTime ($_) } split / - /, $time;
552 
  }
553 
 
554 
  $time =~ s/^(\d{1,2}:\d{2}):\d{2}$/$1/;
555 
  $time =~ s/^0//;
200 - 556 
 
149 - 557 
  if ($ORCUSER->{timeformat} eq "24hr") {
558 
    if ($time =~ /^\d{1,2}:\d{2}$/) { return $time; }
559 
  } else {
560 
    my ($hr, $min) = split /:/, $time;
561 
    my $ampm = " am";
562 
    if ($hr >= 12) {
563 
      $hr -= 12 unless $hr == 12;
564 
      $ampm = " pm";
565 
    } elsif ($hr == 0) {
566 
      $hr = 12;
567 
    }
568 
    return $hr.":".$min.$ampm;
569 
  }
570 
}
571 
 
572 
sub getSchedule {
573 
  my $RCid = shift // return "ERROR: No RCid provided to getSchedule";
574 
  my $filter = shift // "";
180 - 575 
  my $output = shift // "";
149 - 576 
  my $year = 1900 + (localtime)[5];
577 
 
578 
  my @whereclause;
579 
  if ($filter eq "all") {
211 - 580 
    push @whereclause, "year(date) >= year(now())";
581 
  } elsif ($filter eq "prior") {
582 
    push @whereclause, "year(date) < year(now())";
149 - 583 
  } else {
200 - 584 
    push @whereclause, "date >= date(now())";
149 - 585 
  }
586 
#  if ($RCid ne $ORCUSER->{RCid}) {
587 
#    push @whereclause, "dept != 'PER'";
588 
#  }
589 
 
590 
  use DateTime;
591 
  my $dt = DateTime->today (time_zone => 'America/Los_Angeles');
592 
  $dt =~ s/T00\:00\:00$//;
593 
  my $now = DateTime->now (time_zone => 'America/Los_Angeles');
594 
 
200 - 595 
 
149 - 596 
  use HTML::Tiny;
597 
  my $h = HTML::Tiny->new( mode => 'html' );
598 
 
599 
  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";
600 
  my @shifts;
601 
  my $sth = $dbh->prepare("select * from (select id, date, dayofweek, track as location, time, role, teams, signup, 'OFF' as dept, volhours from v_shift_officiating where RCid = ? union
602 
                                          select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union
603 
                                          select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ? union
252 - 604 
                                          select 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 = ? union
605 
                                          select id, date, dayofweek, location, time, role, name as teams, 'seminar' as signup, 'SEM' as dept, 0 as volhours from v_seminar_signup_new where RCid = ?) temp
149 - 606 
                           $where order by date, time");
252 - 607 
  $sth->execute($RCid, $RCid, $RCid, $RCid, $RCid);
149 - 608 
  my $hours = 0;
609 
  while (my $s = $sth->fetchrow_hashref) {
610 
    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};
200 - 611 
    my $cutoff = DateTime->new(
149 - 612 
        year => $yyyy,
613 
        month => $mm,
614 
        day => $dd,
615 
        hour => 5,
616 
        minute => 0,
617 
        second => 0,
618 
        time_zone => 'America/Los_Angeles'
619 
    );
620 
 
621 
 
252 - 622 
    if (!$s->{teams} or $s->{dept} eq "CLA" or $s->{dept} eq "SEM") {
200 - 623 
      # it's a time-based shift
624 
      if ($s->{dept} eq "PER") {
149 - 625 
        if ($RCid eq $ORCUSER->{RCid}) {
626 
          # DROP
200 - 627 
          $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")."&nbsp;".$h->button ({ onClick=>"event.stopPropagation(); location.href='personal_time.pl?choice=Update&id=$s->{id}'" }, "EDIT");
628 
        } else {
629 
          $s->{location} = "";
630 
          $s->{role} = "";
631 
        }
149 - 632 
      } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
633 
        # DROP
634 
        my ($shiftORclass, $linkargs) = ("shift", "");
635 
        if ($s->{dept} eq "CLA") {
636 
          $shiftORclass = "class";
637 
          $linkargs = "&role=$s->{role}";
638 
          $s->{role} = $s->{teams};
639 
          $s->{teams} = "";
252 - 640 
        } elsif ($s->{dept} eq "SEM") {
641 
          $shiftORclass = "seminar";
642 
          $linkargs = "&role=$s->{role}";
643 
          $s->{role} = $s->{teams};
644 
          $s->{teams} = "";
149 - 645 
        }
200 - 646 
        $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");
647 
        if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
648 
          # NO SHOW
649 
          $s->{buttons} .= "&nbsp;".$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");
650 
        }
149 - 651 
 
200 - 652 
      }
653 
#     $hours += $s->{volhours} unless $s->{dept} eq "PER" or $s->{dept} eq "CLA";
654 
 
149 - 655 
    } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
656 
      # it's a game shift
657 
      #DROP
200 - 658 
      $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");
659 
      if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
660 
        # NO SHOW
149 - 661 
        $s->{buttons} .= "&nbsp;".$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");
662 
      }
663 
#      $hours += $s->{volhours};
200 - 664 
    }
665 
    $s->{role} =~ s/\-\d+$//;
666 
 
667 
#   push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "nowrap highlighted" : "nowrap shaded" }, join '&nbsp;&nbsp;', $s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams}, $s->{buttons});
668 
#   push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "highlighted" : "shaded" }, join '&nbsp;&nbsp;', $s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams}, $s->{buttons});
149 - 669 
    $s->{time} = convertTime $s->{time};
152 - 670 
    if ($s->{dept} eq "PER") {
200 - 671 
      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 '&nbsp;&nbsp;', ($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}) ]));
152 - 672 
    } else {
200 - 673 
      push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "highlighted" : "shaded" }, $h->div ({ class=>"lisp0" }, [ $h->div ({ class=>"liLeft" }, join '&nbsp;&nbsp;', ($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}) ]));
674 
    }
149 - 675 
    $hours += $s->{volhours} unless $s->{dept} eq "PER" or $s->{dept} eq "CLA";
676 
  }
200 - 677 
 
180 - 678 
  if ($output eq "hours") {
679 
    return $hours;
680 
  }
149 - 681 
 
682 
  if (scalar @shifts) {
683 
    return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);
213 - 684 
  } elsif ($filter eq "prior") {
685 
    return $h->p ({ class=>"hint" }, "[nothing to see here]");
149 - 686 
  } else {
687 
    return $h->p ({ class=>"hint" }, "[nothing scheduled at the moment]");
688 
  }
689 
}
690 
 
691 
sub getRCid {
692 
  my $derbyname = shift;
693 
  ($derbyname) = $dbh->selectrow_array ("select RCid from official where derby_name = ?", undef, $derbyname);
694 
  return $derbyname;
695 
}
696 
 
697 
sub getSetting {
200 - 698 
  my $k = shift;
699 
 
700 
  my ($value) = $dbh->selectrow_array ("select setting.value from setting where setting.key = ?", undef, $k);
149 - 701 
  return defined $value ? $value : undef;
702 
}
703 
 
704 
sub getUser {
200 - 705 
  my $ID = shift;
706 
 
707 
  my $sth;
708 
  if ($ID =~ /^\d+$/) {
709 
    $sth = $dbh->prepare("select * from v_official where RCid = ?");
710 
  } else {
711 
    $sth = $dbh->prepare("select * from v_official where email = ?");
149 - 712 
  }
200 - 713 
  $sth->execute($ID);
714 
 
715 
  my $user = $sth->fetchrow_hashref;
716 
  map { $user->{$_} = "" unless $user->{$_} } keys %{$user};
717 
  return $user->{RCid} ? $user : "";
149 - 718 
}
719 
 
720 
sub getUserEmail {
200 - 721 
  my $RCid = shift;
722 
  my $sth = $dbh->prepare("select email from official where RCid = ?");
723 
  $sth->execute($RCid);
724 
  my ($email) = $sth->fetchrow_array();
725 
  return $email;
149 - 726 
}
727 
 
728 
sub getUserDerbyName {
200 - 729 
  my $RCid = shift;
730 
  my $sth = $dbh->prepare("select derby_name from official where RCid = ?");
731 
  $sth->execute($RCid);
732 
  my ($dname) = $sth->fetchrow_array();
733 
  return $dname;
149 - 734 
}
735 
 
736 
sub getYears {
200 - 737 
  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");
738 
# my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view");
739 
  $sth->execute();
740 
  my @years;
741 
  while (my ($y) =$sth->fetchrow_array()) { push @years, $y; }
742 
  return \@years;
149 - 743 
}
744 
 
745 
sub printRCHeader {
200 - 746 
  my $PAGE_TITLE = shift;
747 
# use CGI qw/start_html/;
748 
  use HTML::Tiny;
149 - 749 
  my $h = HTML::Tiny->new( mode => 'html' );
750 
 
751 
#  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]");
752 
  my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};
753 
  my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")."&nbsp;";
754 
  $logout .= url =~ /\/(index.pl)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/schedule/';" }, "Home")."&nbsp;";
755 
#  $logout .= $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");
756 
  $logout .= $h->button ({ onClick=>"location.href='?LOGOUT';" }, "Log Out");
200 - 757 
  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 : "";
149 - 758 
 
200 - 759 
#  print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );
149 - 760 
 
200 - 761 
  my $ANALYTICS = <<MATOMO;
762 
  var _mtm = window._mtm = window._mtm || [];
763 
  _mtm.push({'mtm.startTime': (new Date().getTime()), 'event': 'mtm.Start'});
764 
  (function() {
765 
    var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];
766 
    g.async=true; g.src='https://analytics.whump.org/js/container_to4NCtvM.js'; s.parentNode.insertBefore(g,s);
767 
  })();
768 
MATOMO
769 
 
770 
  print $h->open ("html");
771 
  print $h->head ([$h->title ("vORC - $PAGE_TITLE"),
772 
                   $h->link  ({ rel  => "stylesheet",
773 
                                type => "text/css",
774 
                                href => "/style.css" }),
201 - 775 
#                   $h->script ($ANALYTICS)
200 - 776 
                  ]);
777 
  print $h->open ("body");
778 
#  print $h->img ({referrerpolicy=>"no-referrer-when-downgrade", src=>"https://analytics.whump.org/matomo.php?idsite=2&amp;rec=1", style=>"border:0", alt=>""});
149 - 779 
#<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title>
780 
#<link rel="stylesheet" type="text/css" href="/style.css">
781 
#</head>
782 
#<body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000">
200 - 783 
  print $h->div ({ class=>"sp0" }, [ $h->div ({ class=>"spLeft" },  $h->a ({ href=>"/schedule/" }, $h->img ({ src=>"/logo.jpg", width=>"75", height=>"75" }))),
784 
                                     $h->div ({ class=>"spRight" }, [ $h->h1 (["vORC $PAGE_TITLE", $h->br]),
785 
                                     $loggedinas,
786 
                                     ])
787 
                                   ]);
149 - 788 
#print<<rcheader;
789 
#  <TABLE>
200 - 790 
# <TR class="nostripe">
791 
#   <TD align=right><img SRC="/logo.jpg"></TD>
792 
#   <TD align=center valign=middle><b><font size=+3>Officials' RollerCon<br>Schedule Manager<br>$PAGE_TITLE</FONT></b>
793 
# <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>
794 
# </TR>
149 - 795 
 
796 
#rcheader
797 
}
798 
 
799 
sub changeShift {
200 - 800 
  my ($change, $shift_id, $role, $user_id) = @_;
149 - 801 
  if ($shift_id =~ /(am|pm)/) {
252 - 802 
    my $s = $shift_id =~ /^SEM/ ? 1 : 0;
803 
    $shift_id =~ s/^SEM\|//;
149 - 804 
    my ($td, $st, $tl) = split /\|/, $shift_id;
805 
    my ($hr, $min, $ampm) = split /:|\s/, $st;
806 
    if ($ampm eq "pm") { $hr += 12; }
807 
    elsif ($ampm eq "am" and $hr == 12) { $hr = "00" }
200 - 808 
 
149 - 809 
    $st = $hr.":".$min;
810 
    $shift_id = join "|", ($td, $st, $tl);
252 - 811 
    $shift_id = "SEM|".$shift_id if $s;
222 - 812 
  } else {
813 
    $shift_id =~ s/(\d+:\d+):00/$1/;
149 - 814 
  }
815 
#warn join " - ", $change, $shift_id, $role, $user_id;
200 - 816 
  my $leadership_change = 0;
817 
# my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
818 
  my $department;
819 
  if ($shift_id =~ /^\d+$/) {
820 
    $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
821 
  } else {
252 - 822 
    if ($shift_id =~ /^SEM/) {
823 
      $department = "SEM";
824 
      $shift_id =~ s/^SEM\|//;
825 
      if ($change eq "del") {
826 
        ($shift_id, $role) = $dbh->selectrow_array ("select id, role from v_seminar_signup_new where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id);
827 
      } else {
828 
        if ($change eq "override") {
829 
          ($shift_id, $role) = $dbh->selectrow_array ("select id, concat('SEM-', max(cast(substring_index(role, '-', -1) as UNSIGNED)) +1) as role from v_seminar_signup_new where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id) unless $change ne "override";
830 
        } else {
831 
          ($shift_id, $role) = $dbh->selectrow_array ("select id, concat('SEM-', max(cast(substring_index(role, '-', -1) as UNSIGNED)) +1) as role, count(role), capacity from v_seminar_signup_new where date = ? and start_time = ? and location = ? having capacity > count(role)", undef, split /\|/, $shift_id);
832 
        }
833 
      }
834 
      $role = "SEM-1" unless $role; # If no one has signed up for the class yet, the SQL above doesn't retrieve the first available
200 - 835 
    } else {
252 - 836 
      $department = "CLA";
837 
      if ($change eq "del") {
838 
        ($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);
200 - 839 
      } else {
252 - 840 
        if ($change eq "override") {
841 
          ($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";
842 
        } else {
843 
          ($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);
844 
        }
200 - 845 
      }
252 - 846 
      $role = "CLA-1" unless $role; # If no one has signed up for the class yet, the SQL above doesn't retrieve the first available
200 - 847 
    }
149 - 848 
  }
200 - 849 
# my $game_based = $role ? "game" : "shift";
252 - 850 
  my $game_based = $role =~ /^CLA-/ ? "class" : ($role =~ /^SEM-/) ? "seminar" : $role ? "game" : "shift";
200 - 851 
  my $sth;
149 - 852 
 
200 - 853 
  if ($change eq "add" or $change eq "override") {
854 
    my $taken;
252 - 855 
    if ($department eq "CLA" or $department eq "SEM") {
200 - 856 
      ($taken) = $shift_id ? 0 : 1;
857 
    } elsif ($game_based eq "game") {
858 
      ($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);
859 
    } else {
860 
      ($taken) = $dbh->selectrow_array ('select count(*) from shift where id = ? and (isnull(assignee_id) = 0 or assignee_id <> "")', undef, $shift_id);
861 
    }
862 
    if ($taken) {
252 - 863 
      return ($department eq "CLA") ? "<br>Denied! This class is already full ($shift_id).<br>\n" : ($department eq "SEM") ? "<br>Denied! This seminar is already full ($shift_id).<br>\n" : "<br>Denied! This shift is already taken ($shift_id).<br>\n";
200 - 864 
    }
865 
  }
866 
 
867 
  if (lc ($user_id) ne lc ($ORCUSER->{RCid})) { # they're changing someone else's schedule...
868 
    if (($department eq "CLA" and $ORCUSER->{department}->{MVP} >= 2) or $ORCUSER->{department}->{$department} >= 2 or $ORCUSER->{access} >= 5 or $ORCUSER->{department}->{VCI} >= 2) {
869 
      # the user making the change is either a lead in the dept, a sysadmin, or a VCI lead
870 
      logit ($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $shift_id, $role, $user_id)");
871 
      logit ($user_id, "Schedule was changed by $ORCUSER->{derby_name}. ($change, $shift_id, $role, $user_id)");
872 
      $leadership_change = 1;
873 
    } else {
874 
      logit ($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $shift_id, $role, $user_id)");
875 
      return "<br>Denied! You are not authorized to change someone else's schedule in this department ($department).<br>\n";
876 
    }
877 
  } elsif ($ORCUSER->{department}->{$department} >= 3 or $ORCUSER->{access} >= 5) {
878 
    # Managers can sign up for as many shifts within their own department as they like...
879 
    $leadership_change = 1;
880 
  }
881 
 
149 - 882 
  if ($change eq "add") {
883 
    if ($department eq "CLA" and !getUser($user_id)->{MVPid}) {
884 
      return "<br>Denied! User ($user_id) does not have an MVP Pass!<br>\n";
252 - 885 
    } elsif ($department ne "CLA" and $department ne "SEM" and getUser($user_id)->{department} and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {
149 - 886 
      return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n" unless $department eq "CMP";
222 - 887 
    } elsif ($department eq "EMT" and getUser($user_id)->{emt_verified} == 0) {
888 
      return "<br>Denied! User ($user_id) has not had their EMT status verified!<br>\n";
149 - 889 
    }
890 
  }
891 
 
892 
  my $conflict = findConflict ($user_id, $shift_id, $game_based);
893 
  if ($change eq "add" and $conflict) {
200 - 894 
    return "<br>Denied! There is a conflict ($conflict) with that shift's time!<br>\n";
149 - 895 
  }
896 
 
897 
  my $game_type;
252 - 898 
  if ($department ne "CLA" and $department ne "SEM") {
200 - 899 
    ($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);
900 
 
901 
    if ($game_type =~ /^selected/ and !$leadership_change) {
902 
      return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n" unless $department eq "CMP";
903 
    }
904 
 
905 
    if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {
906 
      return "<br>Denied! Shift reserved for leadership staff!<br>\n";
907 
    }
149 - 908 
  } else {
252 - 909 
    $game_type = $department eq "CLA" ? "class" : "seminar";
149 - 910 
  }
911 
 
200 - 912 
 
913 
#   my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");
914 
  my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$department);
915 
  $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $MAXSHIFTS;
916 
  if ($game_type eq "lead" and $department eq "OFF") { $MAXSHIFTS = 99; }
917 
 
149 - 918 
  my $daily_count;
919 
  if ($department eq "CLA") {
920 
    # MVP Class Sign-up
921 
    $MAXSHIFTS = getSetting ("MAX_CLASS_SIGNUP");
208 - 922 
    ($daily_count) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where RCid = ? and year(date) = year(now())", undef, $user_id);
200 - 923 
#   ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);
924 
    if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
925 
      return "<br>Denied! You may only sign up for $MAXSHIFTS Classes!<br>\n";
926 
    }
149 - 927 
  } else {
200 - 928 
    $daily_count = signUpCount ('get', $user_id, $department);
929 
    if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
930 
      return "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";
149 - 931 
    }
200 - 932 
    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) {
933 
      my $dept_table = $department eq 'OFF' ? "v_shift_officiating" : "v_shift_announcer";
934 
      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);
935 
      my $full_length_max = getSetting("MAX_FULL_LENGTH_SIGNUP_".$department);
936 
      if ($full_length_count >= $full_length_max) {
937 
        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";
938 
        return $errormsg;
939 
      }
940 
    }
149 - 941 
  }
942 
 
200 - 943 
  my @DBARGS;
252 - 944 
  if ($game_based eq "game" or $game_based eq "class" or $game_based eq "seminar") {
200 - 945 
    if ($change eq "add" or $change eq "override") {
946 
      $sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
947 
    } elsif ($change eq "del") {
948 
      $sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
949 
    }
950 
    @DBARGS = ($shift_id, $role, $user_id);
149 - 951 
  } else {
200 - 952 
    if ($change eq "add" or $change eq "override") {
953 
      $sth = $dbh->prepare("update shift set assignee_id = ? where id = ? and isnull(assignee_id) = 1");
954 
      @DBARGS = ($user_id, $shift_id);
955 
    } elsif ($change eq "del") {
956 
      $sth = $dbh->prepare("update shift set assignee_id = null where id = ?");
957 
      @DBARGS = ($shift_id);
958 
    }
149 - 959 
  }
960 
 
175 - 961 
  my $wb_act_code;
962 
  if ($change eq "del" and $department eq "CLA") {
963 
    ($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-%');
964 
  }
965 
 
149 - 966 
  print "<br>attempting to make DB changes...<br>";
967 
  if ($sth->execute (@DBARGS)) {
200 - 968 
    $daily_count = signUpCount ($change, $user_id, $department) unless $leadership_change;
969 
    logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");
970 
    logit ($ORCUSER->{RCid}, "OVERRIDE: Shift ".ucfirst($change).": $shift_id -> $role") if $change eq "override";
971 
    if ($department eq "CLA") {
972 
      print "Success!...<br>You've signed up for $daily_count class(es) (you're currently allowed to sign up for $MAXSHIFTS).<br>\n";
252 - 973 
#      updateWRSTBND ($change, $wb_act_code, $DBARGS[0], $DBARGS[2]);
974 
    } elsif ($department eq "CLA") {
975 
      print "Success!...<br>You've signed up for $daily_count seminar(s) (you're currently allowed to sign up for $MAXSHIFTS).<br>\n";
200 - 976 
    } else {
977 
      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";
978 
    }
979 
    return;
149 - 980 
  } else {
200 - 981 
    if ($department eq "CLA") {
149 - 982 
      return "<br><b>You did not get the class</b>, most likely because it filled up while you were looking.<br>\nERROR: ", $sth->errstr();
252 - 983 
    } elsif ($department eq "SEM") {
984 
      return "<br><b>You did not get the seminar</b>, most likely because it filled up while you were looking.<br>\nERROR: ", $sth->errstr();
200 - 985 
    } else {
149 - 986 
      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();
987 
    }
988 
  }
989 
}
990 
 
175 - 991 
sub updateWRSTBND {
992 
  my ($change, $wb_act_code, $shift_id, $user_id) = @_;
993 
  use REST::Client;
994 
  use JSON;
236 - 995 
  my $headers = { Authorization => getSetting ("WRSTBND_API_KEY") };
175 - 996 
  my $client = REST::Client->new();
177 - 997 
  $client->setHost('https://core.wrstbnd.io');
200 - 998 
 
175 - 999 
  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);
1000 
 
1001 
  if ($change eq "add" or $change eq "override") {
1002 
    my ($classid) = $dbh->selectrow_array ("select wrstbnd_id from class where id = ?", undef, $shift_id);
200 - 1003 
 
175 - 1004 
    my $body = {
235 - 1005 
      "eventId"      => "event_893C6u5olU",
175 - 1006 
      "activeStatus" => "active",
1007 
      "ticketTypeId" => $classid
1008 
    };
1009 
    my $json_body = encode_json $body;
200 - 1010 
 
175 - 1011 
    $client->POST(
1012 
      '/rest/core/v1/ticket',
1013 
      $json_body,
1014 
      $headers
1015 
    );
1016 
    my $response = from_json($client->responseContent());
200 - 1017 
 
175 - 1018 
    my $activationCode = $response->{activationCode};
1019 
 
236 - 1020 
    my $api_key = getSetting ("WRSTBND_API_KEY");
1021 
    my @add_response = `/bin/curl --location --request POST 'https://core.wrstbnd.io/rest/core/v1/assign' --header 'Authorization: $api_key' --form accountid=$accountid --form ticketactcode=$activationCode --output /dev/null --silent --write-out '%{http_code}\n'`;
235 - 1022 
    my $add_response = $add_response[$#add_response];
1023 
    chomp $add_response;
175 - 1024 
 
235 - 1025 
    $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";
175 - 1026 
 
1027 
    return;
1028 
  } elsif ($change eq "del") {
1029 
    my $activationCode = $wb_act_code;
236 - 1030 
    my $api_key = getSetting ("WRSTBND_API_KEY");
1031 
    my $del_response = `/bin/curl --location --request DELETE 'https://core.wrstbnd.io/rest/core/v1/assign' --header 'Authorization: $api_key' --form accountid=$accountid --form ticketactcode=$activationCode --output /dev/null --silent --write-out '%{http_code}\n'`;
175 - 1032 
  }
1033 
 
1034 
}
1035 
 
149 - 1036 
sub modShiftTime {
200 - 1037 
  my ($shift_id, $user_id, $diff) = @_;
1038 
  my $ORCUSER = getUser (1);
1039 
 
1040 
  use Scalar::Util qw(looks_like_number);
1041 
  if (!looks_like_number ($diff)) {
1042 
    print "<br>ERROR! The time adjustment ($diff) doesn't look like a number.<br>\n";
1043 
    return;
1044 
  }
1045 
 
149 - 1046 
  my ($validate_assignee) = $dbh->selectrow_array ("select count(*) from v_shift where id = ? and RCid = ?", undef, $shift_id, $user_id);
200 - 1047 
  if (!$validate_assignee) {
1048 
    print "<br>ERROR! This shift is assigned to someone else.<br>\n";
1049 
    return;
1050 
  }
149 - 1051 
 
200 - 1052 
  my $department = getShiftDepartment ($shift_id);
149 - 1053 
  if (convertDepartments ($ORCUSER->{department})->{$department} < 2 and $ORCUSER->{access} < 5) {
200 - 1054 
    print "<br>ERROR! You're not authorized to modify this shift's time.<br>\n";
1055 
    logit ($ORCUSER->{RCid}, "Unauthorized attempt to modify shift time. ($department, $shift_id)");
1056 
    return;
1057 
  }
1058 
 
149 - 1059 
  my $rows_changed;
1060 
  print "<br>attempting to make DB changes...<br>";
1061 
  if ($diff == 0) {
200 - 1062 
    $rows_changed = $dbh->do ("update shift set mod_time = null where id = ? and assignee_id = ?", undef, $shift_id, $user_id);
149 - 1063 
  } else {
200 - 1064 
    $rows_changed = $dbh->do ("update shift set mod_time = ? where id = ? and assignee_id = ?", undef, $diff, $shift_id, $user_id);
149 - 1065 
  }
1066 
 
1067 
 
1068 
  if (!$rows_changed or $dbh->errstr) {
200 - 1069 
    print "ERROR: Nothing got updated".$dbh->errstr;
1070 
    logit (0, "ERROR modifying a shift time ($diff, $shift_id, $user_id):".$dbh->errstr);
149 - 1071 
  } else {
200 - 1072 
    print "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)";
1073 
    logit ($ORCUSER->{RCid}, "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)");
1074 
 
149 - 1075 
  }
1076 
  return;
1077 
}
1078 
 
1079 
sub signUpCount {
200 - 1080 
  my $action = shift;
1081 
  my $id = shift;
1082 
  my $dept = shift // "";
1083 
 
1084 
  if ($id eq $ORCUSER->{RCid}) {
1085 
    if ($action eq 'add') {
1086 
      if (signUpCount ('get', $id, $dept)) {
1087 
        $dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
1088 
      } else {
1089 
        $dbh->do("replace into sign_up_count (date, RCid, department, sign_ups) values (curdate(), ?, ?, 1)", undef, $id, $dept);
1090 
      }
1091 
    } elsif ($action eq 'del') {
1092 
      if (signUpCount ('get', $id, $dept)) {
1093 
        $dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
1094 
      }
1095 
    }
1096 
  }
1097 
 
1098 
  my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and department = ? and date = curdate()", undef, $id, $dept);
1099 
 
1100 
  return $R ? $R : '0';
149 - 1101 
}
1102 
 
1103 
sub signUpEligible {
200 - 1104 
  my $user = shift;
1105 
  my $t = shift;
1106 
  my $shifttype = shift // "game";
1107 
  my $dept = $t->{dept} // "";
149 - 1108 
  my $DEPTHASH = getDepartments ();
1109 
  if ($dept and !exists $DEPTHASH->{$dept}) {
1110 
    my %reverso = reverse %{$DEPTHASH};
1111 
    $dept = $reverso{$dept};
1112 
  }
1113 
 
200 - 1114 
  my $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$dept);
1115 
  $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $limit;
149 - 1116 
 
200 - 1117 
  if (lc $t->{type} eq "lead" and $dept eq "OFF") { $limit = 99; }
1118 
 
1119 
  return 0 unless $limit > 0;
1120 
 
1121 
  my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";
1122 
 
1123 
  if ($shifttype eq "class") {
222 - 1124 
    my $classid = $t->{id};
1125 
    $t->{start_time} =~ s/^(\d+:\d+):00$/$1/;
208 - 1126 
    ($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});
200 - 1127 
    $t->{dept} = "CLA";
1128 
    $dept = "CLA";
1129 
    $t->{type} = "open";
252 - 1130 
  } elsif ($shifttype eq "seminar") {
1131 
    my $classid = $t->{id};
1132 
    $t->{start_time} =~ s/^(\d+:\d+):00$/$1/;
1133 
    ($t->{id}) = $dbh->selectrow_array ("select id from v_seminar_new where date = ? and location = ? and start_time = ?", undef, $t->{date}, $t->{location}, $t->{start_time});
1134 
    $t->{dept} = "SEM";
1135 
    $dept = "SEM";
1136 
    $t->{type} = "open";
200 - 1137 
  }
252 - 1138 
 
200 - 1139 
  if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }
1140 
 
1141 
  if (!exists $user->{$limitkey}) {
1142 
    $user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);
1143 
  }
1144 
 
1145 
  if ($shifttype eq "game") {
149 - 1146 
#    if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{$limitkey} < $limit) {
200 - 1147 
    if ($t->{gtype} eq "full length" and ($dept eq "OFF" or $dept eq "ANN")) {
1148 
      my $table = $dept eq "OFF" ? "v_shift_officiating" : "v_shift_announcer";
1149 
      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});
1150 
      if ($full_length_count >= getSetting ("MAX_FULL_LENGTH_SIGNUP_".$dept)) {
1151 
        return 0;
1152 
      }
1153 
    }
149 - 1154 
    if (lc $t->{signup} ne "selected" and $user->{$limitkey} < $limit) {
200 - 1155 
      return 1;
1156 
    } else {
1157 
      return 0;
1158 
    }
1159 
  } else {
149 - 1160 
    if ($dept eq "CLA") {
1161 
      # MVP Class Sign-up
200 - 1162 
      return 0 unless $user->{MVPid};
149 - 1163 
      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");
208 - 1164 
      my ($class_count) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where RCid = ? and year(date) = year(now())", undef, $user->{RCid});
200 - 1165 
      return 0 unless $class_count < $class_limit;
252 - 1166 
    } elsif ($dept eq "SEM") {
1167 
      # Nothing to check for Seminars...
149 - 1168 
    } else {
200 - 1169 
      if ($user->{department}->{$dept} < 1) { return 0; }
1170 
    }
1171 
    if (lc $t->{type} eq "lead" and $user->{department}->{$dept} < 2) { return 0; }
1172 
    if (lc $t->{type} eq "manager" and $user->{department}->{$dept} < 3) { return 0; }
1173 
    if ($dept eq "EMT" and $user->{emt_verified} == 0) { return 0; }
149 - 1174 
    if (lc $t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {
200 - 1175 
      return 1;
1176 
    } else {
1177 
      return 0;
1178 
    }
1179 
  }
149 - 1180 
}
1181 
 
1182 
sub findConflict {
1183 
  my $rcid = shift;
1184 
  my $gid = shift;
1185 
  my $type = shift // "";
152 - 1186 
  my ($date, $start, $end, $existing, $conflicts);
149 - 1187 
 
1188 
  if ($type eq "game") {
1189 
  # Are they already signed up for this game? (It's faster to check the two views one at a time...)
1190 
#    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where substring_index(id, '-', 1) = ? and RCid = ?", undef, $gid, $rcid);
1191 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where id = ? and RCid = ?", undef, $gid, $rcid);
200 - 1192 
    if ($conflicts) { return "OFF-".$gid; } # no need to keep looking...
149 - 1193 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);
200 - 1194 
    if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...
1195 
 
149 - 1196 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);
1197 
  } elsif ($type eq "class")  {
208 - 1198 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where id = ? and RCid = ?", undef, $gid, $rcid);
200 - 1199 
    if ($conflicts) { return "CLA:".$gid; } # no need to keep looking...
1200 
 
208 - 1201 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_class_new where id = ?", undef, $gid);
149 - 1202 
 
252 - 1203 
  } elsif ($type eq "seminar")  {
1204 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_seminar_signup_new where id = ? and RCid = ?", undef, $gid, $rcid);
1205 
    if ($conflicts) { return "SEM:".$gid; } # no need to keep looking...
1206 
 
1207 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_seminar_new where id = ?", undef, $gid);
1208 
 
149 - 1209 
  } elsif ($type eq "personal")  {
152 - 1210 
    ($date, $start, $end, $existing) = @{ $gid };
149 - 1211 
  } else {
1212 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);
1213 
  }
1214 
 
1215 
  # Are they signed up for any games that would conflict with this one?
1216 
#  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 = ?");
1217 
#  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 = ?");
1218 
 
1219 
  ($conflicts) = $dbh->selectrow_array ("select * from (
152 - 1220 
    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 = ? union
208 - 1221 
    select 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 = ? union
255 - 1222 
    select concat('SEM:', id) as conflict from v_seminar_signup_new  where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union
152 - 1223 
    select 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 = ? union
1224 
    select 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 = ? ) alltables
1225 
    where conflict <> ?",
1226 
    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
149 - 1227 
  );
1228 
 
1229 
  return $conflicts;
1230 
}
1231 
 
1232 
sub changeLeadShift {
200 - 1233 
  my ($change, $lshift, $user_id) = @_;
1234 
  my $ERRMSG;
1235 
 
1236 
  my $sth = $dbh->prepare("update lead_shift set assignee_id = ? where id = ?");
1237 
 
1238 
  print "<br>attempting to make DB changes...<br>";
1239 
  if ($change eq "add") {
1240 
    $sth->execute($user_id, $lshift)
1241 
      or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
1242 
  } elsif ($change eq "del") {
1243 
    $sth->execute('', $lshift)
1244 
      or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
149 - 1245 
  }
200 - 1246 
  if ($ERRMSG) {
1247 
    print $ERRMSG;
1248 
  } else {
1249 
    logit($user_id, "Lead Shift ".ucfirst($change).": $lshift");
1250 
    print "Success.<br>";
1251 
  }
149 - 1252 
}
1253 
 
1254 
sub logit {
200 - 1255 
  my $RCid = shift;
1256 
  my $msg = shift;
1257 
  my $sth = $dbh->prepare("insert into log (RCid, event) values (?, ?)");
1258 
  $sth->execute($RCid, $msg);
149 - 1259 
}
1260 
 
1261 
sub sendNewUserEMail {
200 - 1262 
  my $context = shift;
1263 
  my $data = shift;
1264 
  use RCMailer;
149 - 1265 
  use HTML::Tiny;
1266 
  my $h = HTML::Tiny->new( mode => 'html' );
1267 
  my $depts = getDepartments (); # HashRef of the department TLAs -> Display Names...
1268 
  my $AccessLevel = getAccessLevels;
200 - 1269 
 
1270 
  my $email = $data->{email};
1271 
  my $subject = 'RollerCon VORC - New User';
1272 
  my $body;
1273 
  if ($context eq "New User") {
149 - 1274 
    $subject .= " Request";
1275 
    my $activationlink = url ()."?activate=".$data->{activation};
200 - 1276 
    $body = $h->p ("Greetings,");
1277 
    $body .= $h->p ("It appears as though you've registered a new account in RollerCon's VORC system with the following information:");
1278 
    $body .= $h->table ([
1279 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Derby Name:",    $data->{derby_name})]),
1280 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Full Name:",     $data->{real_name})]),
1281 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Pronouns:",      $data->{pronouns})]),
1282 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "TShirt Size:",   $data->{tshirt})]),
1283 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Email Address:", $data->{email})]),
1284 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Phone:",         $data->{phone})])
1285 
    ]);
149 - 1286 
    $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:",
1287 
      $h->a ({ HREF=>$activationlink }, "Activate my VORC Account!"), $h->br,
1288 
      "Or you can copy/paste this into the 'Activation Code' box: ".$data->{activation}, $h->br,
1289 
      "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.",
1290 
      "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.",
1291 
      "If you're new to using vORC, you may want to read this:",
1292 
      $h->a ({ HREF=>"https://volunteers.rollercon.com/info.html" }, "VORC User Info"),
1293 
      "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.",
1294 
      $h->br,
1295 
      "--RollerCon HQ".$h->br.'rollercon@gmail.com'.$h->br."rollercon.com");
1296 
  } elsif ($context eq "Activate") {
1297 
    $subject .= " Activated!";
1298 
    my $tempDepartments = convertDepartments ($data->{department});
1299 
    my $printableDepartments = join "\n", map { $depts->{$_}.": ".$AccessLevel->{$tempDepartments->{$_}} } sort keys %{$tempDepartments};
1300 
    $body = "Greetings again,
1301 
 
1302 
You have been approved to volunteer at RollerCon in the following departments:
1303 
 
1304 
$printableDepartments
1305 
 
1306 
You 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.
1307 
 
1308 
https://volunteers.rollercon.com/schedule/
1309 
 
1310 
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?
1311 
 
1312 
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.
1313 
 
1314 
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.
1315 
 
1316 
If you're new to using vORC, you may want to read this:
1317 
 
1318 
https://volunteers.rollercon.com/info.html
1319 
 
1320 
If 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.
1321 
 
1322 
-RollerCon Management
1323 
";
1324 
  } else {
1325 
    return;
1326 
  }
200 - 1327 
  # send the message
1328 
  EmailUser ($email, $subject, $body);
1329 
 
149 - 1330 
}
1331 
 
1332 
sub validate_emt {
1333 
  my $target = shift // "";
1334 
  my $change = shift // "";
1335 
 
1336 
  if (!$target or !$change) {
1337 
    warn "ERROR: validate_emt() called without a required parameter! target: $target, change: $change";
1338 
    return -1;
1339 
  }
1340 
 
1341 
  my $uservalidate = getUser $target;
1342 
  if (!exists $uservalidate->{RCid}) {
1343 
    warn "ERROR: validate_emt() called on a non-existant user! target: $target, change: $change";
1344 
    return -1;
1345 
  }
1346 
 
1347 
  if ($change eq "add") {
1348 
    if ($uservalidate->{emt_verified}) {
1349 
      warn "ERROR: validate_emt() called to add on a user already verified: $target, change: $change";
1350 
      return -1;
1351 
    } else {
216 - 1352 
      $dbh->do ("replace into emt_credential_verified (RCid, date, verified_by) values (?, date(now()), ?)", undef, $target, $ORCUSER->{RCid}) or warn $dbh->errstr;
149 - 1353 
      logit ($target, "EMT Credentials Verified");
1354 
      logit ($ORCUSER->{RCid}, "Verified EMT Credentials for $uservalidate->{derby_name} [$target]");
1355 
    }
1356 
  } elsif ($change eq "del") {
1357 
    if (!$uservalidate->{emt_verified}) {
1358 
      warn "ERROR: validate_emt() called to del on a user that isn't verified: $target, change: $change";
1359 
      return -1;
1360 
    } else {
222 - 1361 
      $dbh->do ("delete from emt_credential_verified where year(date) = year(now()) and RCid = ?", undef, $target) or warn $dbh->errstr;
149 - 1362 
      logit ($target, "EMT Credential Verification removed");
1363 
      logit ($ORCUSER->{RCid}, "Removed EMT Credential verification for $uservalidate->{derby_name} [$target]");
1364 
    }
1365 
  } else {
1366 
    warn "ERROR: validate_emt() called with a bad parameter! target: $target, change: $change";
1367 
    return -1;
1368 
  }
1369 
}
1370 
 
1371 
 
1372 
1;