Subversion Repositories VORC

Rev

Rev 59 | Rev 65 | 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 
    }
63 bgadell 373 
    $output = {} unless ref $output eq "HASH";
7 - 374 
  }
375 
 
376 
  return $output;
377 
}
378 
 
50 bgadell 379 
sub convertTime {
380 
  my $time = shift || return;
381 
 
382 
  if ($time =~ / - /) {
383 
    return join " - ", map { convertTime ($_) } split / - /, $time;
384 
  }
57 bgadell 385 
 
386 
  $time =~ s/^(\d{1,2}:\d{2}):\d{2}$/$1/;
387 
 
50 bgadell 388 
  if ($ORCUSER->{timeformat} eq "24hr") {
389 
    if ($time =~ /^\d{1,2}:\d{2}$/) { return $time; }
390 
  } else {
391 
    my ($hr, $min) = split /:/, $time;
392 
    my $ampm = " am";
393 
    if ($hr >= 12) {
394 
      $hr -= 12 unless $hr == 12;
395 
      $ampm = " pm";
396 
    } elsif ($hr == 0) {
397 
      $hr = 12;
398 
    }
399 
    return $hr.":".$min.$ampm;
400 
  }
401 
}
402 
 
7 - 403 
sub getSchedule {
404 
  my $RCid = shift // return "ERROR: No RCid provided to getSchedule";
405 
  my $filter = shift // "";
53 bgadell 406 
  my $year = 1900 + (localtime)[5];
7 - 407 
 
408 
  my @whereclause;
46 - 409 
  if ($filter eq "all") {
53 bgadell 410 
  	push @whereclause, "date >= '$year-01-01'";
46 - 411 
  } else {
412 
  	push @whereclause, "date >= date(now())";
413 
  }
7 - 414 
#  if ($RCid ne $ORCUSER->{RCid}) {
415 
#    push @whereclause, "dept != 'PER'";
416 
#  }
417 
 
418 
  use DateTime;
25 - 419 
  my $dt = DateTime->today (time_zone => 'America/Los_Angeles');
7 - 420 
  $dt =~ s/T00\:00\:00$//;
25 - 421 
  my $now = DateTime->now (time_zone => 'America/Los_Angeles');
7 - 422 
 
423 
 
424 
  use HTML::Tiny;
425 
  my $h = HTML::Tiny->new( mode => 'html' );
426 
 
427 
  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";
428 
  my @shifts;
21 - 429 
  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
430 
                                          select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union
57 bgadell 431 
                                          select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ? union
432 
                                          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 - 433 
                           $where order by date, time");
57 bgadell 434 
  $sth->execute($RCid, $RCid, $RCid, $RCid);
13 - 435 
  my $hours = 0;
7 - 436 
  while (my $s = $sth->fetchrow_hashref) {
437 
    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};
438 
	  my $cutoff = DateTime->new(
439 
        year => $yyyy,
440 
        month => $mm,
441 
        day => $dd,
442 
        hour => 5,
443 
        minute => 0,
444 
        second => 0,
445 
        time_zone => 'America/Los_Angeles'
446 
    );
447 
 
448 
 
449 
  	if (!$s->{teams}) {
450 
  	  # it's a time-based shift
451 
  	  if ($s->{dept} eq "PER") {
452 
        if ($RCid eq $ORCUSER->{RCid}) {
453 
          # DROP
57 bgadell 454 
  	      $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 - 455 
  	    } else {
456 
  	      $s->{location} = "";
457 
  	      $s->{role} = "";
458 
  	    }
21 - 459 
      } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
7 - 460 
        # DROP
57 bgadell 461 
        my ($shiftORclass, $linkargs) = ("shift", "");
462 
        if ($s->{dept} eq "CLA") {
463 
          $shiftORclass = "class";
464 
          $linkargs = "&role=$s->{role}";
465 
        }
466 
	   		$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 - 467 
	   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
468 
   		    # NO SHOW
57 bgadell 469 
 	  	    $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 - 470 
 		    }
35 - 471 
        $hours += $s->{volhours} unless $s->{dept} eq "CLA";
7 - 472 
  		}
473 
 
21 - 474 
    } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
7 - 475 
      # it's a game shift
476 
      #DROP
477 
  		$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");
478 
   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
479 
 		    # NO SHOW
480 
        $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");
481 
      }
482 
      $hours += $s->{volhours};
483 
  	}
484 
  	$s->{role} =~ s/\-\d$//;
485 
 
9 - 486 
#  	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});
487 
#  	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 488 
    $s->{time} = convertTime $s->{time};
9 - 489 
  	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 - 490 
  }
491 
 
492 
  if (scalar @shifts) {
493 
    return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);
494 
  } else {
495 
    return $h->p ({ class=>"hint" }, "[nothing scheduled at the moment]");
496 
  }
497 
}
498 
 
29 - 499 
sub getRCid {
500 
  my $derbyname = shift;
501 
  ($derbyname) = $dbh->selectrow_array ("select RCid from official where derby_name = ?", undef, $derbyname);
502 
  return $derbyname;
503 
}
504 
 
2 - 505 
sub getSetting {
506 
	my $k = shift;
19 - 507 
	my ($value) = $dbh->selectrow_array ("select setting.value from setting where setting.key = ?", undef, $k);
29 - 508 
  return defined $value ? $value : undef;
2 - 509 
}
510 
 
511 
sub getUser {
7 - 512 
	my $ID = shift;
513 
 
514 
	my $sth;
515 
	if ($ID =~ /^\d+$/) {
58 bgadell 516 
	  $sth = $dbh->prepare("select * from v_official where RCid = ?");
7 - 517 
	} else {
58 bgadell 518 
	  $sth = $dbh->prepare("select * from v_official where email = ?");
7 - 519 
  }
520 
	$sth->execute($ID);
57 bgadell 521 
 
522 
	my $user = $sth->fetchrow_hashref;
523 
	map { $user->{$_} = "" unless $user->{$_} } keys %{$user};
524 
	return $user;
2 - 525 
}
526 
 
527 
sub getUserEmail {
528 
	my $RCid = shift;
529 
	my $sth = $dbh->prepare("select email from official where RCid = ?");
530 
	$sth->execute($RCid);
531 
	my ($email) = $sth->fetchrow_array();
532 
	return $email;
533 
}
534 
 
535 
sub getUserDerbyName {
536 
	my $RCid = shift;
537 
	my $sth = $dbh->prepare("select derby_name from official where RCid = ?");
538 
	$sth->execute($RCid);
539 
	my ($dname) = $sth->fetchrow_array();
540 
	return $dname;
541 
}
542 
 
543 
sub getYears {
50 bgadell 544 
	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");
545 
#	my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view");
2 - 546 
	$sth->execute();
547 
	my @years;
548 
	while (my ($y) =$sth->fetchrow_array()) { push @years, $y; }
549 
	return \@years;
550 
}
551 
 
552 
sub printRCHeader {
553 
	my $PAGE_TITLE = shift;
50 bgadell 554 
#	use CGI qw/start_html/;
7 - 555 
	use HTML::Tiny;
556 
  my $h = HTML::Tiny->new( mode => 'html' );
2 - 557 
 
7 - 558 
#  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 - 559 
  my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};
560 
  my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")."&nbsp;";
561 
  $logout .= url =~ /\/(index.pl)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/schedule/';" }, "Home")."&nbsp;";
562 
  $logout .= $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");
57 bgadell 563 
	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 - 564 
 
565 
  print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );
566 
 
567 
#<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title>
568 
#<link rel="stylesheet" type="text/css" href="/style.css">
569 
#</head>
570 
#<body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000">
571 
	print $h->div ({ class=>"sp0" }, [ $h->div ({ class=>"spLeft" },  $h->a ({ href=>"/schedule/" }, $h->img ({ src=>"/logo.jpg", width=>"75", height=>"75" }))),
572 
	                                   $h->div ({ class=>"spRight" }, [ $h->h1 (["vORC $PAGE_TITLE", $h->br]),
573 
	                                   $loggedinas,
574 
	                                   ])
575 
	                                 ]);
576 
#print<<rcheader;
577 
#  <TABLE>
578 
#	<TR class="nostripe">
579 
#		<TD align=right><img SRC="/logo.jpg"></TD>
580 
#		<TD align=center valign=middle><b><font size=+3>Officials' RollerCon<br>Schedule Manager<br>$PAGE_TITLE</FONT></b>
581 
#	<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>
582 
#	</TR>
2 - 583 
 
7 - 584 
#rcheader
2 - 585 
}
586 
 
587 
sub changeShift {
7 - 588 
	my ($change, $shift_id, $role, $user_id) = @_;
57 bgadell 589 
#warn join " - ", $change, $shift_id, $role, $user_id;
7 - 590 
	my $leadership_change = 0;
35 - 591 
#	my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
592 
	my $department;
593 
	if ($shift_id =~ /^\d+$/) {
594 
		$department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
595 
	} else {
596 
		$department = "CLA";
57 bgadell 597 
		if ($change eq "del") {
598 
		  ($shift_id, $role) = $dbh->selectrow_array ("select id, role from v_class_signup where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id);
599 
		} else {
600 
		  ($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);
601 
		}
602 
    $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 - 603 
	}
57 bgadell 604 
#	my $game_based = $role ? "game" : "shift";
605 
	my $game_based = $role =~ /^CLA-/ ? "class" : $role ? "game" : "shift";
7 - 606 
	my $sth;
2 - 607 
 
46 - 608 
	if ($change eq "add" or $change eq "override") {
7 - 609 
  	my $taken;
57 bgadell 610 
		if ($department eq "CLA") {
611 
  	  ($taken) = $shift_id ? 0 : 1;
612 
  	} elsif ($game_based eq "game") {
7 - 613 
  	  ($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);
614 
  	} else {
615 
  	  ($taken) = $dbh->selectrow_array ("select count(*) from shift where id = ? and isnull(assignee_id) = 0", undef, $shift_id);
616 
  	}
617 
  	if ($taken) {
35 - 618 
  	    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 - 619 
  	}
620 
  }
2 - 621 
 
7 - 622 
	if (lc ($user_id) ne lc ($ORCUSER->{RCid})) { # they're changing someone else's schedule...
35 - 623 
	  if ($ORCUSER->{department}->{$department} >= 2 or $ORCUSER->{access} >= 5 or $ORCUSER->{department}->{VCI} >= 2) {
624 
	    # the user making the change is either a lead in the dept, a sysadmin, or a VCI lead
7 - 625 
	    logit ($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $shift_id, $role, $user_id)");
626 
	    logit ($user_id, "Schedule was changed by $ORCUSER->{derby_name}. ($change, $shift_id, $role, $user_id)");
627 
	    $leadership_change = 1;
2 - 628 
	  } else {
7 - 629 
	    logit ($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $shift_id, $role, $user_id)");
630 
	    return "<br>Denied! You are not authorized to change someone else's schedule in this department ($department).<br>\n";
2 - 631 
	  }
7 - 632 
	} elsif ($ORCUSER->{department}->{$department} >= 3) {
633 
	  # Managers can sign up for as many shifts within their own department as they like...
634 
	  $leadership_change = 1;
2 - 635 
	}
636 
 
57 bgadell 637 
  if ($change eq "add") {
58 bgadell 638 
    if ($department eq "CLA" and !getUser($user_id)->{MVPid}) {
57 bgadell 639 
      return "<br>Denied! User ($user_id) does not have an MVP Pass!<br>\n";
640 
    } elsif ($department ne "CLA" and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {
641 
      return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n" unless $department eq "CMP";
642 
    }
7 - 643 
  }
644 
 
46 - 645 
  my $conflict = findConflict ($user_id, $shift_id, $game_based);
646 
  if ($change eq "add" and $conflict) {
647 
		return "<br>Denied! There is a conflict ($conflict) with that shift's time!<br>\n";
7 - 648 
  }
649 
 
57 bgadell 650 
  my $game_type;
651 
  if ($department ne "CLA") {
652 
   	($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);
653 
 
654 
   	if ($game_type =~ /^selected/ and !$leadership_change) {
655 
   	  return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n" unless $department eq "CMP";
656 
   	}
657 
 
658 
   	if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {
659 
   	  return "<br>Denied! Shift reserved for leadership staff!<br>\n";
660 
   	}
661 
  } else {
662 
    $game_type = "class";
663 
  }
664 
 
7 - 665 
 
29 - 666 
# 	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");
667 
	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$department);
668 
	$MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $MAXSHIFTS;
669 
	if ($game_type eq "lead" and $department eq "OFF") { $MAXSHIFTS = 99; }
670 
 
35 - 671 
  my $daily_count;
672 
  if ($department eq "CLA") {
673 
    # MVP Class Sign-up
674 
    $MAXSHIFTS = getSetting ("MAX_CLASS_SIGNUP");
57 bgadell 675 
	  ($daily_count) = $dbh->selectrow_array ("select count(*) from v_class_signup where RCid = ?", undef, $user_id);
676 
#	  ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);
35 - 677 
   	if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
678 
	    return "<br>Denied! You may only sign up for $MAXSHIFTS Classes!<br>\n";
679 
	  }
680 
  } else {
681 
   	$daily_count = signUpCount ('get', $user_id, $department);
682 
   	if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
683 
   		return "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";
684 
   	}
46 - 685 
#   	if ($change eq "add" and $game_based eq "game" and $department eq "OFF" and $game_type eq "full length") {
50 bgadell 686 
#    	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 - 687 
#  		if ($full_length_count >= 3) {
688 
#  			return "<br>Denied! You may only sign up to officiate 3 $game_type games (total)!<br>\n";
689 
#  		}
690 
#    }
35 - 691 
  }
692 
 
7 - 693 
 	my @DBARGS;
57 bgadell 694 
  if ($game_based eq "game" or $game_based eq "class") {
46 - 695 
  	if ($change eq "add" or $change eq "override") {
7 - 696 
  		$sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
697 
  	} elsif ($change eq "del") {
698 
  		$sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
699 
  	}
700 
  	@DBARGS = ($shift_id, $role, $user_id);
701 
  } else {
46 - 702 
  	if ($change eq "add" or $change eq "override") {
35 - 703 
  		$sth = $dbh->prepare("update shift set assignee_id = ? where id = ? and isnull(assignee_id) = 1");
7 - 704 
  		@DBARGS = ($user_id, $shift_id);
705 
  	} elsif ($change eq "del") {
706 
  		$sth = $dbh->prepare("update shift set assignee_id = null where id = ?");
707 
  		@DBARGS = ($shift_id);
708 
  	}
709 
  }
710 
 
711 
  print "<br>attempting to make DB changes...<br>";
712 
  if ($sth->execute (@DBARGS)) {
29 - 713 
  	$daily_count = signUpCount ($change, $user_id, $department) unless $leadership_change;
7 - 714 
  	logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");
46 - 715 
  	logit ($ORCUSER->{RCid}, "OVERRIDE: Shift ".ucfirst($change).": $shift_id -> $role") if $change eq "override";
57 bgadell 716 
  	if ($department eq "CLA") {
717 
  	  print "Success!...<br>You've signed up for $daily_count class(es) (you're currently allowed to sign up for $MAXSHIFTS).<br>\n";
718 
  	} else {
719 
  	  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";
720 
  	}
7 - 721 
  	return;
722 
  } else {
57 bgadell 723 
  	if ($department eq "CLA") {
724 
      return "<br><b>You did not get the class</b>, most likely because it filled up while you were looking.<br>\nERROR: ", $sth->errstr();
725 
  	} else {
726 
      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();
727 
    }
7 - 728 
  }
729 
}
730 
 
731 
sub modShiftTime {
732 
	my ($shift_id, $user_id, $diff) = @_;
733 
	my $ORCUSER = getUser (1);
2 - 734 
 
7 - 735 
	use Scalar::Util qw(looks_like_number);
736 
	if (!looks_like_number ($diff)) {
737 
	  print "<br>ERROR! The time adjustment ($diff) doesn't look like a number.<br>\n";
738 
  	return;
2 - 739 
	}
740 
 
7 - 741 
  my ($validate_assignee) = $dbh->selectrow_array ("select count(*) from v_shift where id = ? and RCid = ?", undef, $shift_id, $user_id);
742 
 	if (!$validate_assignee) {
743 
	  print "<br>ERROR! This shift is assigned to someone else.<br>\n";
744 
  	return;
745 
 	}
746 
 
747 
	my $department = getShiftDepartment ($shift_id);
748 
  if (convertDepartments ($ORCUSER->{department})->{$department} < 2 and $ORCUSER->{access} < 5) {
749 
	  print "<br>ERROR! You're not authorized to modify this shift's time.<br>\n";
750 
	  logit ($ORCUSER->{RCid}, "Unauthorized attempt to modify shift time. ($department, $shift_id)");
751 
  	return;
752 
 	}
753 
 
754 
  my $rows_changed;
755 
  print "<br>attempting to make DB changes...<br>";
756 
  if ($diff == 0) {
757 
	  $rows_changed = $dbh->do ("update shift set mod_time = null where id = ? and assignee_id = ?", undef, $shift_id, $user_id);
758 
  } else {
759 
	  $rows_changed = $dbh->do ("update shift set mod_time = ? where id = ? and assignee_id = ?", undef, $diff, $shift_id, $user_id);
760 
  }
761 
 
762 
 
763 
  if (!$rows_changed or $dbh->errstr) {
764 
  	print "ERROR: Nothing got updated".$dbh->errstr;
765 
  	logit (0, "ERROR modifying a shift time ($diff, $shift_id, $user_id):".$dbh->errstr);
766 
  } else {
767 
  	print "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)";
768 
  	logit ($ORCUSER->{RCid}, "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)");
769 
 
770 
  }
771 
  return;
2 - 772 
}
773 
 
774 
sub signUpCount {
775 
	my $action = shift;
776 
	my $id = shift;
19 - 777 
	my $dept = shift // "";
2 - 778 
 
7 - 779 
	if ($id eq $ORCUSER->{RCid}) {
2 - 780 
		if ($action eq 'add') {
19 - 781 
			if (signUpCount ('get', $id, $dept)) {
782 
				$dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
2 - 783 
			} else {
19 - 784 
				$dbh->do("replace into sign_up_count (date, RCid, department, sign_ups) values (curdate(), ?, ?, 1)", undef, $id, $dept);
2 - 785 
			}
786 
		} elsif ($action eq 'del') {
19 - 787 
			if (signUpCount ('get', $id, $dept)) {
788 
				$dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
2 - 789 
			}
790 
		}
791 
	}
792 
 
19 - 793 
	my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and department = ? and date = curdate()", undef, $id, $dept);
2 - 794 
 
795 
	return $R ? $R : '0';
796 
}
797 
 
798 
sub signUpEligible {
799 
	my $user = shift;
800 
	my $t = shift;
7 - 801 
	my $shifttype = shift // "game";
19 - 802 
	my $dept = $t->{dept} // "";
2 - 803 
 
19 - 804 
	my $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$dept);
805 
	$limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $limit;
806 
 
29 - 807 
	if ($t->{type} eq "lead" and $dept eq "OFF") { $limit = 99; }
808 
 
809 
	return 0 unless $limit > 0;
810 
 
19 - 811 
	my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";
812 
 
35 - 813 
	if ($shifttype eq "class") {
57 bgadell 814 
		($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 - 815 
		$t->{dept} = "CLA";
57 bgadell 816 
		$dept = "CLA";
35 - 817 
		$t->{type} = "open";
818 
	}
57 bgadell 819 
 
7 - 820 
	if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }
57 bgadell 821 
 
19 - 822 
	if (!exists $user->{$limitkey}) {
823 
		$user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);
2 - 824 
	}
825 
 
7 - 826 
	if ($shifttype eq "game") {
21 - 827 
#    if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{$limitkey} < $limit) {
46 - 828 
#		if ($t->{gtype} eq "full length" and $dept eq "OFF") {
50 bgadell 829 
#			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 - 830 
#			if ($full_length_count >= 3) {
831 
#				return 0;
832 
#			}
833 
#		}
21 - 834 
    if ($t->{signup} ne "selected" and $user->{$limitkey} < $limit) {
2 - 835 
			return 1;
836 
		} else {
837 
			return 0;
838 
		}
7 - 839 
	} else {
35 - 840 
    if ($dept eq "CLA") {
841 
      # MVP Class Sign-up
58 bgadell 842 
			return 0 unless $user->{MVPid};
35 - 843 
      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");
57 bgadell 844 
			my ($class_count) = $dbh->selectrow_array ("select count(*) from v_class_signup where RCid = ? and year(date) = year(now())", undef, $user->{RCid});
35 - 845 
			return 0 unless $class_count < $class_limit;
57 bgadell 846 
    } else {
847 
	    if ($user->{department}->{$t->{dept}} < 1) { return 0; }
848 
	  }
7 - 849 
	  if ($t->{type} eq "lead" and $user->{department}->{$t->{dept}} < 2) { return 0; }
850 
	  if ($t->{type} eq "manager" and $user->{department}->{$t->{dept}} < 3) { return 0; }
19 - 851 
    if ($t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {
2 - 852 
			return 1;
853 
		} else {
854 
			return 0;
855 
		}
856 
	}
857 
}
858 
 
859 
sub findConflict {
860 
  my $rcid = shift;
861 
  my $gid = shift;
7 - 862 
  my $type = shift // "";
863 
  my ($date, $start, $end, $conflicts);
2 - 864 
 
7 - 865 
  if ($type eq "game") {
866 
  # Are they already signed up for this game? (It's faster to check the two views one at a time...)
867 
#    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where substring_index(id, '-', 1) = ? and RCid = ?", undef, $gid, $rcid);
868 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where id = ? and RCid = ?", undef, $gid, $rcid);
46 - 869 
  	if ($conflicts) { return "OFF-".$gid; } # no need to keep looking...
7 - 870 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);
46 - 871 
  	if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...
7 - 872 
 
873 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);
57 bgadell 874 
  } elsif ($type eq "class")  {
875 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup where id = ? and RCid = ?", undef, $gid, $rcid);
876 
  	if ($conflicts) { return "CLA:".$gid; } # no need to keep looking...
877 
 
878 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_class where id = ?", undef, $gid);
879 
 
7 - 880 
  } elsif ($type eq "personal")  {
881 
    ($date, $start, $end) = @{ $gid };
882 
  } else {
883 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);
884 
  }
2 - 885 
 
886 
  # Are they signed up for any games that would conflict with this one?
7 - 887 
#  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 = ?");
888 
#  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 - 889 
 
46 - 890 
  ($conflicts) = $dbh->selectrow_array ("select * from (
57 bgadell 891 
    select concat(dept, '-', id) from v_shift          where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union
892 
    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 - 893 
    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
894 
    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 895 
    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 - 896 
  );
897 
 
2 - 898 
  return $conflicts;
899 
}
900 
 
901 
sub changeLeadShift {
902 
	my ($change, $lshift, $user_id) = @_;
903 
	my $ERRMSG;
904 
 
905 
	my $sth = $dbh->prepare("update lead_shift set assignee_id = ? where id = ?");
906 
 
907 
	print "<br>attempting to make DB changes...<br>";
908 
	if ($change eq "add") {
909 
		$sth->execute($user_id, $lshift)
910 
    	or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
911 
	} elsif ($change eq "del") {
912 
		$sth->execute('', $lshift)
913 
    	or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
914 
	}
915 
	if ($ERRMSG) {
916 
		print $ERRMSG;
917 
	} else {
918 
		logit($user_id, "Lead Shift ".ucfirst($change).": $lshift");
919 
  	print "Success.<br>";
920 
  }
921 
}
922 
 
923 
sub logit {
924 
	my $RCid = shift;
925 
	my $msg = shift;
926 
	my $sth = $dbh->prepare("insert into log (RCid, event) values (?, ?)");
927 
	$sth->execute($RCid, $msg);
928 
}
929 
 
57 bgadell 930 
sub sendNewUserEMail {
931 
	my $context = shift;
932 
	my $data = shift;
933 
	use RCMailer;
934 
  use HTML::Tiny;
935 
  my $h = HTML::Tiny->new( mode => 'html' );
936 
  my $depts = getDepartments (); # HashRef of the department TLAs -> Display Names...
937 
  my $AccessLevel = getAccessLevels;
938 
 
939 
	my $email = $data->{email};
940 
	my $subject = 'RollerCon VORC - New User';
941 
	my $body;
942 
	if ($context eq "New User") {
943 
    $subject .= " Request";
944 
    my $activationlink = url ()."?activate=".$data->{activation};
945 
	  $body = $h->p ("Greetings,");
946 
	  $body .= $h->p ("It appears as though you've registered a new account in RollerCon's VORC system with the following information:");
947 
	  $body .= $h->table ([
948 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Derby Name:",    $data->{derby_name})]),
949 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Real Name:",     $data->{real_name})]),
950 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Pronouns:",      $data->{pronouns})]),
951 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "TShirt Size:",   $data->{tshirt})]),
952 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Email Address:", $data->{email})]),
953 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Phone:",         $data->{phone})])
954 
	  ]);
955 
    $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:",
956 
      $h->a ({ HREF=>$activationlink }, "Activate my VORC Account!"), $h->br,
957 
      "Or you can copy/paste this into the 'Activation Code' box: ".$data->{activation}, $h->br,
958 
      "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.",
959 
      "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.",
960 
      "If you're new to using vORC, you may want to read this:",
961 
      $h->a ({ HREF=>"https://volunteers.rollercon.com/info.html" }, "VORC User Info"),
962 
      "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.",
963 
      $h->br,
964 
      "--RollerCon HQ".$h->br.'rollercon@gmail.com'.$h->br."rollercon.com");
965 
  } elsif ($context eq "Activate") {
966 
    $subject .= " Activated!";
967 
    my $tempDepartments = convertDepartments ($data->{department});
968 
    my $printableDepartments = join "\n", map { $depts->{$_}.": ".$AccessLevel->{$tempDepartments->{$_}} } sort keys %{$tempDepartments};
969 
    $body = "Greetings again,
970 
 
971 
You have been approved to volunteer at RollerCon in the following departments:
972 
 
973 
$printableDepartments
974 
 
975 
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.
976 
 
977 
https://volunteers.rollercon.com/schedule/
978 
 
979 
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?
980 
 
981 
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.
982 
 
983 
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.
984 
 
985 
If you're new to using vORC, you may want to read this:
986 
 
987 
https://volunteers.rollercon.com/info.html
988 
 
989 
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.
990 
 
991 
-RollerCon Management
992 
";
993 
  } else {
994 
    return;
995 
  }
996 
	# send the message
997 
	EmailUser ($email, $subject, $body);
998 
 
999 
}
1000 
 
1001 
 
2 - 1002 
1;