Subversion Repositories VORC

Rev

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