Subversion Repositories VORC

Rev

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