Subversion Repositories VORC

Rev

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