Subversion Repositories VORC

Rev

Rev 222 | 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 
 
208 - 15 
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);
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 
 
484 
sub getShiftRef {
485 
  my $shiftID = shift // "";
486 
  return unless $shiftID =~ /^\d+$/;
487 
 
488 
  my ($shiftref) = $dbh->selectrow_hashref ("select * from shift where id = ?", undef, $shiftID);
489 
  return $shiftref unless $shiftref->{id} != $shiftID;
490 
 
491 
  warn "ERROR: Couldn't find shift with ID [$shiftID]";
492 
  return "";
493 
}
494 
 
495 
sub getDepartments {
496 
  my $RCid = shift // "";
497 
  # If we get an RCid, return the list of departments and levels for that user.
498 
  #   Otherwise (no parameter), return the list of departments with their display names.
499 
 
200 - 500 
  if ($RCid) {
501 
    my $sth = $dbh->prepare("select department from official where RCid = ?");
502 
    $sth->execute($RCid);
503 
    my ($dlist) = $sth->fetchrow;
504 
    return convertDepartments ($dlist);
505 
  } else {
506 
    my %HASH;
507 
    my $sth = $dbh->prepare("select TLA, name from department");
508 
    $sth->execute();
509 
    while (my ($tla, $name) = $sth->fetchrow) {
510 
      $HASH{$tla} = $name;
149 - 511 
    }
512 
    return \%HASH;
513 
  }
514 
 
515 
}
516 
 
517 
sub convertDepartments {
518 
  # For the department membership, converts the DB string back and forth to a hashref...
519 
  my $input = shift // "";
520 
  my $output;
200 - 521 
 
149 - 522 
  if (ref $input eq "HASH") {
523 
    $output = join ":", map { $_."-".$input->{$_} } sort keys %{$input};
524 
  } else {
200 - 525 
    foreach (split /:/, $input) {
526 
      my ($tla, $level) = split /-/;
527 
      $output->{$tla} = $level;
149 - 528 
    }
529 
    $output = {} unless ref $output eq "HASH";
530 
  }
531 
 
532 
  return $output;
533 
}
534 
 
535 
sub convertTime {
536 
  my $time = shift || return;
537 
 
538 
  if ($time =~ / - /) {
539 
    return join " - ", map { convertTime ($_) } split / - /, $time;
540 
  }
541 
 
542 
  $time =~ s/^(\d{1,2}:\d{2}):\d{2}$/$1/;
543 
  $time =~ s/^0//;
200 - 544 
 
149 - 545 
  if ($ORCUSER->{timeformat} eq "24hr") {
546 
    if ($time =~ /^\d{1,2}:\d{2}$/) { return $time; }
547 
  } else {
548 
    my ($hr, $min) = split /:/, $time;
549 
    my $ampm = " am";
550 
    if ($hr >= 12) {
551 
      $hr -= 12 unless $hr == 12;
552 
      $ampm = " pm";
553 
    } elsif ($hr == 0) {
554 
      $hr = 12;
555 
    }
556 
    return $hr.":".$min.$ampm;
557 
  }
558 
}
559 
 
560 
sub getSchedule {
561 
  my $RCid = shift // return "ERROR: No RCid provided to getSchedule";
562 
  my $filter = shift // "";
180 - 563 
  my $output = shift // "";
149 - 564 
  my $year = 1900 + (localtime)[5];
565 
 
566 
  my @whereclause;
567 
  if ($filter eq "all") {
211 - 568 
    push @whereclause, "year(date) >= year(now())";
569 
  } elsif ($filter eq "prior") {
570 
    push @whereclause, "year(date) < year(now())";
149 - 571 
  } else {
200 - 572 
    push @whereclause, "date >= date(now())";
149 - 573 
  }
574 
#  if ($RCid ne $ORCUSER->{RCid}) {
575 
#    push @whereclause, "dept != 'PER'";
576 
#  }
577 
 
578 
  use DateTime;
579 
  my $dt = DateTime->today (time_zone => 'America/Los_Angeles');
580 
  $dt =~ s/T00\:00\:00$//;
581 
  my $now = DateTime->now (time_zone => 'America/Los_Angeles');
582 
 
200 - 583 
 
149 - 584 
  use HTML::Tiny;
585 
  my $h = HTML::Tiny->new( mode => 'html' );
586 
 
587 
  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";
588 
  my @shifts;
589 
  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
590 
                                          select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union
591 
                                          select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ? union
208 - 592 
                                          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 = ?) temp
149 - 593 
                           $where order by date, time");
594 
  $sth->execute($RCid, $RCid, $RCid, $RCid);
595 
  my $hours = 0;
596 
  while (my $s = $sth->fetchrow_hashref) {
597 
    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};
200 - 598 
    my $cutoff = DateTime->new(
149 - 599 
        year => $yyyy,
600 
        month => $mm,
601 
        day => $dd,
602 
        hour => 5,
603 
        minute => 0,
604 
        second => 0,
605 
        time_zone => 'America/Los_Angeles'
606 
    );
607 
 
608 
 
200 - 609 
    if (!$s->{teams} or $s->{dept} eq "CLA") {
610 
      # it's a time-based shift
611 
      if ($s->{dept} eq "PER") {
149 - 612 
        if ($RCid eq $ORCUSER->{RCid}) {
613 
          # DROP
200 - 614 
          $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");
615 
        } else {
616 
          $s->{location} = "";
617 
          $s->{role} = "";
618 
        }
149 - 619 
      } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
620 
        # DROP
621 
        my ($shiftORclass, $linkargs) = ("shift", "");
622 
        if ($s->{dept} eq "CLA") {
623 
          $shiftORclass = "class";
624 
          $linkargs = "&role=$s->{role}";
625 
          $s->{role} = $s->{teams};
626 
          $s->{teams} = "";
627 
        }
200 - 628 
        $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");
629 
        if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
630 
          # NO SHOW
631 
          $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");
632 
        }
149 - 633 
 
200 - 634 
      }
635 
#     $hours += $s->{volhours} unless $s->{dept} eq "PER" or $s->{dept} eq "CLA";
636 
 
149 - 637 
    } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
638 
      # it's a game shift
639 
      #DROP
200 - 640 
      $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");
641 
      if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
642 
        # NO SHOW
149 - 643 
        $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");
644 
      }
645 
#      $hours += $s->{volhours};
200 - 646 
    }
647 
    $s->{role} =~ s/\-\d+$//;
648 
 
649 
#   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});
650 
#   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 - 651 
    $s->{time} = convertTime $s->{time};
152 - 652 
    if ($s->{dept} eq "PER") {
200 - 653 
      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 - 654 
    } else {
200 - 655 
      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}) ]));
656 
    }
149 - 657 
    $hours += $s->{volhours} unless $s->{dept} eq "PER" or $s->{dept} eq "CLA";
658 
  }
200 - 659 
 
180 - 660 
  if ($output eq "hours") {
661 
    return $hours;
662 
  }
149 - 663 
 
664 
  if (scalar @shifts) {
665 
    return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);
213 - 666 
  } elsif ($filter eq "prior") {
667 
    return $h->p ({ class=>"hint" }, "[nothing to see here]");
149 - 668 
  } else {
669 
    return $h->p ({ class=>"hint" }, "[nothing scheduled at the moment]");
670 
  }
671 
}
672 
 
673 
sub getRCid {
674 
  my $derbyname = shift;
675 
  ($derbyname) = $dbh->selectrow_array ("select RCid from official where derby_name = ?", undef, $derbyname);
676 
  return $derbyname;
677 
}
678 
 
679 
sub getSetting {
200 - 680 
  my $k = shift;
681 
 
682 
  my ($value) = $dbh->selectrow_array ("select setting.value from setting where setting.key = ?", undef, $k);
149 - 683 
  return defined $value ? $value : undef;
684 
}
685 
 
686 
sub getUser {
200 - 687 
  my $ID = shift;
688 
 
689 
  my $sth;
690 
  if ($ID =~ /^\d+$/) {
691 
    $sth = $dbh->prepare("select * from v_official where RCid = ?");
692 
  } else {
693 
    $sth = $dbh->prepare("select * from v_official where email = ?");
149 - 694 
  }
200 - 695 
  $sth->execute($ID);
696 
 
697 
  my $user = $sth->fetchrow_hashref;
698 
  map { $user->{$_} = "" unless $user->{$_} } keys %{$user};
699 
  return $user->{RCid} ? $user : "";
149 - 700 
}
701 
 
702 
sub getUserEmail {
200 - 703 
  my $RCid = shift;
704 
  my $sth = $dbh->prepare("select email from official where RCid = ?");
705 
  $sth->execute($RCid);
706 
  my ($email) = $sth->fetchrow_array();
707 
  return $email;
149 - 708 
}
709 
 
710 
sub getUserDerbyName {
200 - 711 
  my $RCid = shift;
712 
  my $sth = $dbh->prepare("select derby_name from official where RCid = ?");
713 
  $sth->execute($RCid);
714 
  my ($dname) = $sth->fetchrow_array();
715 
  return $dname;
149 - 716 
}
717 
 
718 
sub getYears {
200 - 719 
  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");
720 
# my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view");
721 
  $sth->execute();
722 
  my @years;
723 
  while (my ($y) =$sth->fetchrow_array()) { push @years, $y; }
724 
  return \@years;
149 - 725 
}
726 
 
727 
sub printRCHeader {
200 - 728 
  my $PAGE_TITLE = shift;
729 
# use CGI qw/start_html/;
730 
  use HTML::Tiny;
149 - 731 
  my $h = HTML::Tiny->new( mode => 'html' );
732 
 
733 
#  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]");
734 
  my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};
735 
  my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")."&nbsp;";
736 
  $logout .= url =~ /\/(index.pl)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/schedule/';" }, "Home")."&nbsp;";
737 
#  $logout .= $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");
738 
  $logout .= $h->button ({ onClick=>"location.href='?LOGOUT';" }, "Log Out");
200 - 739 
  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 - 740 
 
200 - 741 
#  print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );
149 - 742 
 
200 - 743 
  my $ANALYTICS = <<MATOMO;
744 
  var _mtm = window._mtm = window._mtm || [];
745 
  _mtm.push({'mtm.startTime': (new Date().getTime()), 'event': 'mtm.Start'});
746 
  (function() {
747 
    var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];
748 
    g.async=true; g.src='https://analytics.whump.org/js/container_to4NCtvM.js'; s.parentNode.insertBefore(g,s);
749 
  })();
750 
MATOMO
751 
 
752 
  print $h->open ("html");
753 
  print $h->head ([$h->title ("vORC - $PAGE_TITLE"),
754 
                   $h->link  ({ rel  => "stylesheet",
755 
                                type => "text/css",
756 
                                href => "/style.css" }),
201 - 757 
#                   $h->script ($ANALYTICS)
200 - 758 
                  ]);
759 
  print $h->open ("body");
760 
#  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 - 761 
#<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title>
762 
#<link rel="stylesheet" type="text/css" href="/style.css">
763 
#</head>
764 
#<body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000">
200 - 765 
  print $h->div ({ class=>"sp0" }, [ $h->div ({ class=>"spLeft" },  $h->a ({ href=>"/schedule/" }, $h->img ({ src=>"/logo.jpg", width=>"75", height=>"75" }))),
766 
                                     $h->div ({ class=>"spRight" }, [ $h->h1 (["vORC $PAGE_TITLE", $h->br]),
767 
                                     $loggedinas,
768 
                                     ])
769 
                                   ]);
149 - 770 
#print<<rcheader;
771 
#  <TABLE>
200 - 772 
# <TR class="nostripe">
773 
#   <TD align=right><img SRC="/logo.jpg"></TD>
774 
#   <TD align=center valign=middle><b><font size=+3>Officials' RollerCon<br>Schedule Manager<br>$PAGE_TITLE</FONT></b>
775 
# <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>
776 
# </TR>
149 - 777 
 
778 
#rcheader
779 
}
780 
 
781 
sub changeShift {
200 - 782 
  my ($change, $shift_id, $role, $user_id) = @_;
149 - 783 
  if ($shift_id =~ /(am|pm)/) {
784 
    my ($td, $st, $tl) = split /\|/, $shift_id;
785 
    my ($hr, $min, $ampm) = split /:|\s/, $st;
786 
    if ($ampm eq "pm") { $hr += 12; }
787 
    elsif ($ampm eq "am" and $hr == 12) { $hr = "00" }
200 - 788 
 
149 - 789 
    $st = $hr.":".$min;
790 
    $shift_id = join "|", ($td, $st, $tl);
222 - 791 
  } else {
792 
    $shift_id =~ s/(\d+:\d+):00/$1/;
149 - 793 
  }
794 
#warn join " - ", $change, $shift_id, $role, $user_id;
200 - 795 
  my $leadership_change = 0;
796 
# my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
797 
  my $department;
798 
  if ($shift_id =~ /^\d+$/) {
799 
    $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
800 
  } else {
801 
    $department = "CLA";
802 
    if ($change eq "del") {
208 - 803 
      ($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 - 804 
    } else {
805 
      if ($change eq "override") {
208 - 806 
        ($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";
200 - 807 
      } else {
208 - 808 
        ($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);
200 - 809 
      }
810 
    }
149 - 811 
    $role = "CLA-1" unless $role; # If no one has signed up for the class yet, the SQL above doesn't retrieve the first available
812 
  }
200 - 813 
# my $game_based = $role ? "game" : "shift";
814 
  my $game_based = $role =~ /^CLA-/ ? "class" : $role ? "game" : "shift";
815 
  my $sth;
149 - 816 
 
200 - 817 
  if ($change eq "add" or $change eq "override") {
818 
    my $taken;
819 
    if ($department eq "CLA") {
820 
      ($taken) = $shift_id ? 0 : 1;
821 
    } elsif ($game_based eq "game") {
822 
      ($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);
823 
    } else {
824 
      ($taken) = $dbh->selectrow_array ('select count(*) from shift where id = ? and (isnull(assignee_id) = 0 or assignee_id <> "")', undef, $shift_id);
825 
    }
826 
    if ($taken) {
827 
      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";
828 
    }
829 
  }
830 
 
831 
  if (lc ($user_id) ne lc ($ORCUSER->{RCid})) { # they're changing someone else's schedule...
832 
    if (($department eq "CLA" and $ORCUSER->{department}->{MVP} >= 2) or $ORCUSER->{department}->{$department} >= 2 or $ORCUSER->{access} >= 5 or $ORCUSER->{department}->{VCI} >= 2) {
833 
      # the user making the change is either a lead in the dept, a sysadmin, or a VCI lead
834 
      logit ($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $shift_id, $role, $user_id)");
835 
      logit ($user_id, "Schedule was changed by $ORCUSER->{derby_name}. ($change, $shift_id, $role, $user_id)");
836 
      $leadership_change = 1;
837 
    } else {
838 
      logit ($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $shift_id, $role, $user_id)");
839 
      return "<br>Denied! You are not authorized to change someone else's schedule in this department ($department).<br>\n";
840 
    }
841 
  } elsif ($ORCUSER->{department}->{$department} >= 3 or $ORCUSER->{access} >= 5) {
842 
    # Managers can sign up for as many shifts within their own department as they like...
843 
    $leadership_change = 1;
844 
  }
845 
 
149 - 846 
  if ($change eq "add") {
847 
    if ($department eq "CLA" and !getUser($user_id)->{MVPid}) {
848 
      return "<br>Denied! User ($user_id) does not have an MVP Pass!<br>\n";
849 
    } elsif ($department ne "CLA" and getUser($user_id)->{department} and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {
850 
      return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n" unless $department eq "CMP";
222 - 851 
    } elsif ($department eq "EMT" and getUser($user_id)->{emt_verified} == 0) {
852 
      return "<br>Denied! User ($user_id) has not had their EMT status verified!<br>\n";
149 - 853 
    }
854 
  }
855 
 
856 
  my $conflict = findConflict ($user_id, $shift_id, $game_based);
857 
  if ($change eq "add" and $conflict) {
200 - 858 
    return "<br>Denied! There is a conflict ($conflict) with that shift's time!<br>\n";
149 - 859 
  }
860 
 
861 
  my $game_type;
862 
  if ($department ne "CLA") {
200 - 863 
    ($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);
864 
 
865 
    if ($game_type =~ /^selected/ and !$leadership_change) {
866 
      return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n" unless $department eq "CMP";
867 
    }
868 
 
869 
    if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {
870 
      return "<br>Denied! Shift reserved for leadership staff!<br>\n";
871 
    }
149 - 872 
  } else {
873 
    $game_type = "class";
874 
  }
875 
 
200 - 876 
 
877 
#   my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");
878 
  my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$department);
879 
  $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $MAXSHIFTS;
880 
  if ($game_type eq "lead" and $department eq "OFF") { $MAXSHIFTS = 99; }
881 
 
149 - 882 
  my $daily_count;
883 
  if ($department eq "CLA") {
884 
    # MVP Class Sign-up
885 
    $MAXSHIFTS = getSetting ("MAX_CLASS_SIGNUP");
208 - 886 
    ($daily_count) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where RCid = ? and year(date) = year(now())", undef, $user_id);
200 - 887 
#   ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);
888 
    if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
889 
      return "<br>Denied! You may only sign up for $MAXSHIFTS Classes!<br>\n";
890 
    }
149 - 891 
  } else {
200 - 892 
    $daily_count = signUpCount ('get', $user_id, $department);
893 
    if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
894 
      return "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";
149 - 895 
    }
200 - 896 
    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) {
897 
      my $dept_table = $department eq 'OFF' ? "v_shift_officiating" : "v_shift_announcer";
898 
      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);
899 
      my $full_length_max = getSetting("MAX_FULL_LENGTH_SIGNUP_".$department);
900 
      if ($full_length_count >= $full_length_max) {
901 
        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";
902 
        return $errormsg;
903 
      }
904 
    }
149 - 905 
  }
906 
 
200 - 907 
  my @DBARGS;
149 - 908 
  if ($game_based eq "game" or $game_based eq "class") {
200 - 909 
    if ($change eq "add" or $change eq "override") {
910 
      $sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
911 
    } elsif ($change eq "del") {
912 
      $sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
913 
    }
914 
    @DBARGS = ($shift_id, $role, $user_id);
149 - 915 
  } else {
200 - 916 
    if ($change eq "add" or $change eq "override") {
917 
      $sth = $dbh->prepare("update shift set assignee_id = ? where id = ? and isnull(assignee_id) = 1");
918 
      @DBARGS = ($user_id, $shift_id);
919 
    } elsif ($change eq "del") {
920 
      $sth = $dbh->prepare("update shift set assignee_id = null where id = ?");
921 
      @DBARGS = ($shift_id);
922 
    }
149 - 923 
  }
924 
 
175 - 925 
  my $wb_act_code;
926 
  if ($change eq "del" and $department eq "CLA") {
927 
    ($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-%');
928 
  }
929 
 
149 - 930 
  print "<br>attempting to make DB changes...<br>";
931 
  if ($sth->execute (@DBARGS)) {
200 - 932 
    $daily_count = signUpCount ($change, $user_id, $department) unless $leadership_change;
933 
    logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");
934 
    logit ($ORCUSER->{RCid}, "OVERRIDE: Shift ".ucfirst($change).": $shift_id -> $role") if $change eq "override";
935 
    if ($department eq "CLA") {
936 
      print "Success!...<br>You've signed up for $daily_count class(es) (you're currently allowed to sign up for $MAXSHIFTS).<br>\n";
937 
      updateWRSTBND ($change, $wb_act_code, $DBARGS[0], $DBARGS[2]);
938 
    } else {
939 
      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";
940 
    }
941 
    return;
149 - 942 
  } else {
200 - 943 
    if ($department eq "CLA") {
149 - 944 
      return "<br><b>You did not get the class</b>, most likely because it filled up while you were looking.<br>\nERROR: ", $sth->errstr();
200 - 945 
    } else {
149 - 946 
      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();
947 
    }
948 
  }
949 
}
950 
 
175 - 951 
sub updateWRSTBND {
952 
  my ($change, $wb_act_code, $shift_id, $user_id) = @_;
953 
  use REST::Client;
954 
  use JSON;
955 
  my $headers = { Authorization => '601037851507c624' };
956 
  my $client = REST::Client->new();
177 - 957 
  $client->setHost('https://core.wrstbnd.io');
200 - 958 
 
175 - 959 
  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);
960 
 
961 
  if ($change eq "add" or $change eq "override") {
962 
    my ($classid) = $dbh->selectrow_array ("select wrstbnd_id from class where id = ?", undef, $shift_id);
200 - 963 
 
175 - 964 
    my $body = {
235 - 965 
      "eventId"      => "event_893C6u5olU",
175 - 966 
      "activeStatus" => "active",
967 
      "ticketTypeId" => $classid
968 
    };
969 
    my $json_body = encode_json $body;
200 - 970 
 
175 - 971 
    $client->POST(
972 
      '/rest/core/v1/ticket',
973 
      $json_body,
974 
      $headers
975 
    );
976 
    my $response = from_json($client->responseContent());
200 - 977 
 
175 - 978 
    my $activationCode = $response->{activationCode};
979 
 
235 - 980 
    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'`;
981 
    my $add_response = $add_response[$#add_response];
982 
    chomp $add_response;
175 - 983 
 
235 - 984 
    $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 - 985 
 
986 
    return;
987 
  } elsif ($change eq "del") {
988 
    my $activationCode = $wb_act_code;
178 - 989 
    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'`;
175 - 990 
  }
991 
 
992 
}
993 
 
149 - 994 
sub modShiftTime {
200 - 995 
  my ($shift_id, $user_id, $diff) = @_;
996 
  my $ORCUSER = getUser (1);
997 
 
998 
  use Scalar::Util qw(looks_like_number);
999 
  if (!looks_like_number ($diff)) {
1000 
    print "<br>ERROR! The time adjustment ($diff) doesn't look like a number.<br>\n";
1001 
    return;
1002 
  }
1003 
 
149 - 1004 
  my ($validate_assignee) = $dbh->selectrow_array ("select count(*) from v_shift where id = ? and RCid = ?", undef, $shift_id, $user_id);
200 - 1005 
  if (!$validate_assignee) {
1006 
    print "<br>ERROR! This shift is assigned to someone else.<br>\n";
1007 
    return;
1008 
  }
149 - 1009 
 
200 - 1010 
  my $department = getShiftDepartment ($shift_id);
149 - 1011 
  if (convertDepartments ($ORCUSER->{department})->{$department} < 2 and $ORCUSER->{access} < 5) {
200 - 1012 
    print "<br>ERROR! You're not authorized to modify this shift's time.<br>\n";
1013 
    logit ($ORCUSER->{RCid}, "Unauthorized attempt to modify shift time. ($department, $shift_id)");
1014 
    return;
1015 
  }
1016 
 
149 - 1017 
  my $rows_changed;
1018 
  print "<br>attempting to make DB changes...<br>";
1019 
  if ($diff == 0) {
200 - 1020 
    $rows_changed = $dbh->do ("update shift set mod_time = null where id = ? and assignee_id = ?", undef, $shift_id, $user_id);
149 - 1021 
  } else {
200 - 1022 
    $rows_changed = $dbh->do ("update shift set mod_time = ? where id = ? and assignee_id = ?", undef, $diff, $shift_id, $user_id);
149 - 1023 
  }
1024 
 
1025 
 
1026 
  if (!$rows_changed or $dbh->errstr) {
200 - 1027 
    print "ERROR: Nothing got updated".$dbh->errstr;
1028 
    logit (0, "ERROR modifying a shift time ($diff, $shift_id, $user_id):".$dbh->errstr);
149 - 1029 
  } else {
200 - 1030 
    print "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)";
1031 
    logit ($ORCUSER->{RCid}, "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)");
1032 
 
149 - 1033 
  }
1034 
  return;
1035 
}
1036 
 
1037 
sub signUpCount {
200 - 1038 
  my $action = shift;
1039 
  my $id = shift;
1040 
  my $dept = shift // "";
1041 
 
1042 
  if ($id eq $ORCUSER->{RCid}) {
1043 
    if ($action eq 'add') {
1044 
      if (signUpCount ('get', $id, $dept)) {
1045 
        $dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
1046 
      } else {
1047 
        $dbh->do("replace into sign_up_count (date, RCid, department, sign_ups) values (curdate(), ?, ?, 1)", undef, $id, $dept);
1048 
      }
1049 
    } elsif ($action eq 'del') {
1050 
      if (signUpCount ('get', $id, $dept)) {
1051 
        $dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
1052 
      }
1053 
    }
1054 
  }
1055 
 
1056 
  my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and department = ? and date = curdate()", undef, $id, $dept);
1057 
 
1058 
  return $R ? $R : '0';
149 - 1059 
}
1060 
 
1061 
sub signUpEligible {
200 - 1062 
  my $user = shift;
1063 
  my $t = shift;
1064 
  my $shifttype = shift // "game";
1065 
  my $dept = $t->{dept} // "";
149 - 1066 
  my $DEPTHASH = getDepartments ();
1067 
  if ($dept and !exists $DEPTHASH->{$dept}) {
1068 
    my %reverso = reverse %{$DEPTHASH};
1069 
    $dept = $reverso{$dept};
1070 
  }
1071 
 
200 - 1072 
  my $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$dept);
1073 
  $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $limit;
149 - 1074 
 
200 - 1075 
  if (lc $t->{type} eq "lead" and $dept eq "OFF") { $limit = 99; }
1076 
 
1077 
  return 0 unless $limit > 0;
1078 
 
1079 
  my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";
1080 
 
1081 
  if ($shifttype eq "class") {
222 - 1082 
    my $classid = $t->{id};
1083 
    $t->{start_time} =~ s/^(\d+:\d+):00$/$1/;
208 - 1084 
    ($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 - 1085 
    $t->{dept} = "CLA";
1086 
    $dept = "CLA";
1087 
    $t->{type} = "open";
1088 
  }
1089 
 
1090 
  if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }
1091 
 
1092 
  if (!exists $user->{$limitkey}) {
1093 
    $user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);
1094 
  }
1095 
 
1096 
  if ($shifttype eq "game") {
149 - 1097 
#    if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{$limitkey} < $limit) {
200 - 1098 
    if ($t->{gtype} eq "full length" and ($dept eq "OFF" or $dept eq "ANN")) {
1099 
      my $table = $dept eq "OFF" ? "v_shift_officiating" : "v_shift_announcer";
1100 
      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});
1101 
      if ($full_length_count >= getSetting ("MAX_FULL_LENGTH_SIGNUP_".$dept)) {
1102 
        return 0;
1103 
      }
1104 
    }
149 - 1105 
    if (lc $t->{signup} ne "selected" and $user->{$limitkey} < $limit) {
200 - 1106 
      return 1;
1107 
    } else {
1108 
      return 0;
1109 
    }
1110 
  } else {
149 - 1111 
    if ($dept eq "CLA") {
1112 
      # MVP Class Sign-up
200 - 1113 
      return 0 unless $user->{MVPid};
149 - 1114 
      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");
208 - 1115 
      my ($class_count) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where RCid = ? and year(date) = year(now())", undef, $user->{RCid});
200 - 1116 
      return 0 unless $class_count < $class_limit;
149 - 1117 
    } else {
200 - 1118 
      if ($user->{department}->{$dept} < 1) { return 0; }
1119 
    }
1120 
    if (lc $t->{type} eq "lead" and $user->{department}->{$dept} < 2) { return 0; }
1121 
    if (lc $t->{type} eq "manager" and $user->{department}->{$dept} < 3) { return 0; }
1122 
    if ($dept eq "EMT" and $user->{emt_verified} == 0) { return 0; }
149 - 1123 
    if (lc $t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {
200 - 1124 
      return 1;
1125 
    } else {
1126 
      return 0;
1127 
    }
1128 
  }
149 - 1129 
}
1130 
 
1131 
sub findConflict {
1132 
  my $rcid = shift;
1133 
  my $gid = shift;
1134 
  my $type = shift // "";
152 - 1135 
  my ($date, $start, $end, $existing, $conflicts);
149 - 1136 
 
1137 
  if ($type eq "game") {
1138 
  # Are they already signed up for this game? (It's faster to check the two views one at a time...)
1139 
#    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where substring_index(id, '-', 1) = ? and RCid = ?", undef, $gid, $rcid);
1140 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where id = ? and RCid = ?", undef, $gid, $rcid);
200 - 1141 
    if ($conflicts) { return "OFF-".$gid; } # no need to keep looking...
149 - 1142 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);
200 - 1143 
    if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...
1144 
 
149 - 1145 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);
1146 
  } elsif ($type eq "class")  {
208 - 1147 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where id = ? and RCid = ?", undef, $gid, $rcid);
200 - 1148 
    if ($conflicts) { return "CLA:".$gid; } # no need to keep looking...
1149 
 
208 - 1150 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_class_new where id = ?", undef, $gid);
149 - 1151 
 
1152 
  } elsif ($type eq "personal")  {
152 - 1153 
    ($date, $start, $end, $existing) = @{ $gid };
149 - 1154 
  } else {
1155 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);
1156 
  }
1157 
 
1158 
  # Are they signed up for any games that would conflict with this one?
1159 
#  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 = ?");
1160 
#  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 = ?");
1161 
 
1162 
  ($conflicts) = $dbh->selectrow_array ("select * from (
152 - 1163 
    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 - 1164 
    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
152 - 1165 
    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
1166 
    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
1167 
    where conflict <> ?",
1168 
    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 - 1169 
  );
1170 
 
1171 
  return $conflicts;
1172 
}
1173 
 
1174 
sub changeLeadShift {
200 - 1175 
  my ($change, $lshift, $user_id) = @_;
1176 
  my $ERRMSG;
1177 
 
1178 
  my $sth = $dbh->prepare("update lead_shift set assignee_id = ? where id = ?");
1179 
 
1180 
  print "<br>attempting to make DB changes...<br>";
1181 
  if ($change eq "add") {
1182 
    $sth->execute($user_id, $lshift)
1183 
      or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
1184 
  } elsif ($change eq "del") {
1185 
    $sth->execute('', $lshift)
1186 
      or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
149 - 1187 
  }
200 - 1188 
  if ($ERRMSG) {
1189 
    print $ERRMSG;
1190 
  } else {
1191 
    logit($user_id, "Lead Shift ".ucfirst($change).": $lshift");
1192 
    print "Success.<br>";
1193 
  }
149 - 1194 
}
1195 
 
1196 
sub logit {
200 - 1197 
  my $RCid = shift;
1198 
  my $msg = shift;
1199 
  my $sth = $dbh->prepare("insert into log (RCid, event) values (?, ?)");
1200 
  $sth->execute($RCid, $msg);
149 - 1201 
}
1202 
 
1203 
sub sendNewUserEMail {
200 - 1204 
  my $context = shift;
1205 
  my $data = shift;
1206 
  use RCMailer;
149 - 1207 
  use HTML::Tiny;
1208 
  my $h = HTML::Tiny->new( mode => 'html' );
1209 
  my $depts = getDepartments (); # HashRef of the department TLAs -> Display Names...
1210 
  my $AccessLevel = getAccessLevels;
200 - 1211 
 
1212 
  my $email = $data->{email};
1213 
  my $subject = 'RollerCon VORC - New User';
1214 
  my $body;
1215 
  if ($context eq "New User") {
149 - 1216 
    $subject .= " Request";
1217 
    my $activationlink = url ()."?activate=".$data->{activation};
200 - 1218 
    $body = $h->p ("Greetings,");
1219 
    $body .= $h->p ("It appears as though you've registered a new account in RollerCon's VORC system with the following information:");
1220 
    $body .= $h->table ([
1221 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Derby Name:",    $data->{derby_name})]),
1222 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Full Name:",     $data->{real_name})]),
1223 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Pronouns:",      $data->{pronouns})]),
1224 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "TShirt Size:",   $data->{tshirt})]),
1225 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Email Address:", $data->{email})]),
1226 
      $h->tr ([$h->td ("&nbsp;&nbsp;", "Phone:",         $data->{phone})])
1227 
    ]);
149 - 1228 
    $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:",
1229 
      $h->a ({ HREF=>$activationlink }, "Activate my VORC Account!"), $h->br,
1230 
      "Or you can copy/paste this into the 'Activation Code' box: ".$data->{activation}, $h->br,
1231 
      "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.",
1232 
      "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.",
1233 
      "If you're new to using vORC, you may want to read this:",
1234 
      $h->a ({ HREF=>"https://volunteers.rollercon.com/info.html" }, "VORC User Info"),
1235 
      "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.",
1236 
      $h->br,
1237 
      "--RollerCon HQ".$h->br.'rollercon@gmail.com'.$h->br."rollercon.com");
1238 
  } elsif ($context eq "Activate") {
1239 
    $subject .= " Activated!";
1240 
    my $tempDepartments = convertDepartments ($data->{department});
1241 
    my $printableDepartments = join "\n", map { $depts->{$_}.": ".$AccessLevel->{$tempDepartments->{$_}} } sort keys %{$tempDepartments};
1242 
    $body = "Greetings again,
1243 
 
1244 
You have been approved to volunteer at RollerCon in the following departments:
1245 
 
1246 
$printableDepartments
1247 
 
1248 
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.
1249 
 
1250 
https://volunteers.rollercon.com/schedule/
1251 
 
1252 
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?
1253 
 
1254 
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.
1255 
 
1256 
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.
1257 
 
1258 
If you're new to using vORC, you may want to read this:
1259 
 
1260 
https://volunteers.rollercon.com/info.html
1261 
 
1262 
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.
1263 
 
1264 
-RollerCon Management
1265 
";
1266 
  } else {
1267 
    return;
1268 
  }
200 - 1269 
  # send the message
1270 
  EmailUser ($email, $subject, $body);
1271 
 
149 - 1272 
}
1273 
 
1274 
sub validate_emt {
1275 
  my $target = shift // "";
1276 
  my $change = shift // "";
1277 
 
1278 
  if (!$target or !$change) {
1279 
    warn "ERROR: validate_emt() called without a required parameter! target: $target, change: $change";
1280 
    return -1;
1281 
  }
1282 
 
1283 
  my $uservalidate = getUser $target;
1284 
  if (!exists $uservalidate->{RCid}) {
1285 
    warn "ERROR: validate_emt() called on a non-existant user! target: $target, change: $change";
1286 
    return -1;
1287 
  }
1288 
 
1289 
  if ($change eq "add") {
1290 
    if ($uservalidate->{emt_verified}) {
1291 
      warn "ERROR: validate_emt() called to add on a user already verified: $target, change: $change";
1292 
      return -1;
1293 
    } else {
216 - 1294 
      $dbh->do ("replace into emt_credential_verified (RCid, date, verified_by) values (?, date(now()), ?)", undef, $target, $ORCUSER->{RCid}) or warn $dbh->errstr;
149 - 1295 
      logit ($target, "EMT Credentials Verified");
1296 
      logit ($ORCUSER->{RCid}, "Verified EMT Credentials for $uservalidate->{derby_name} [$target]");
1297 
    }
1298 
  } elsif ($change eq "del") {
1299 
    if (!$uservalidate->{emt_verified}) {
1300 
      warn "ERROR: validate_emt() called to del on a user that isn't verified: $target, change: $change";
1301 
      return -1;
1302 
    } else {
222 - 1303 
      $dbh->do ("delete from emt_credential_verified where year(date) = year(now()) and RCid = ?", undef, $target) or warn $dbh->errstr;
149 - 1304 
      logit ($target, "EMT Credential Verification removed");
1305 
      logit ($ORCUSER->{RCid}, "Removed EMT Credential verification for $uservalidate->{derby_name} [$target]");
1306 
    }
1307 
  } else {
1308 
    warn "ERROR: validate_emt() called with a bad parameter! target: $target, change: $change";
1309 
    return -1;
1310 
  }
1311 
}
1312 
 
1313 
 
1314 
1;