Subversion Repositories VORC

Rev

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