Subversion Repositories VORC

Rev

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

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