Subversion Repositories VORC

Rev

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

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