Subversion Repositories VORC

Rev

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

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