Subversion Repositories VORC

Rev

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

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