Subversion Repositories VORC

Rev

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