Subversion Repositories VORC

Rev

Rev 96 | Rev 112 | 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
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 
 		    }
35 - 476 
        $hours += $s->{volhours} unless $s->{dept} eq "CLA";
7 - 477 
  		}
478 
 
21 - 479 
    } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
7 - 480 
      # it's a game shift
481 
      #DROP
482 
  		$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");
483 
   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
484 
 		    # NO SHOW
485 
        $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");
486 
      }
487 
      $hours += $s->{volhours};
488 
  	}
78 bgadell 489 
  	$s->{role} =~ s/\-\d+$//;
7 - 490 
 
9 - 491 
#  	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});
492 
#  	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 493 
    $s->{time} = convertTime $s->{time};
106 bgadell 494 
  	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}) ]));
7 - 495 
  }
496 
 
497 
  if (scalar @shifts) {
498 
    return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);
499 
  } else {
500 
    return $h->p ({ class=>"hint" }, "[nothing scheduled at the moment]");
501 
  }
502 
}
503 
 
29 - 504 
sub getRCid {
505 
  my $derbyname = shift;
506 
  ($derbyname) = $dbh->selectrow_array ("select RCid from official where derby_name = ?", undef, $derbyname);
507 
  return $derbyname;
508 
}
509 
 
2 - 510 
sub getSetting {
511 
	my $k = shift;
19 - 512 
	my ($value) = $dbh->selectrow_array ("select setting.value from setting where setting.key = ?", undef, $k);
29 - 513 
  return defined $value ? $value : undef;
2 - 514 
}
515 
 
516 
sub getUser {
7 - 517 
	my $ID = shift;
518 
 
519 
	my $sth;
520 
	if ($ID =~ /^\d+$/) {
58 bgadell 521 
	  $sth = $dbh->prepare("select * from v_official where RCid = ?");
7 - 522 
	} else {
58 bgadell 523 
	  $sth = $dbh->prepare("select * from v_official where email = ?");
7 - 524 
  }
525 
	$sth->execute($ID);
57 bgadell 526 
 
527 
	my $user = $sth->fetchrow_hashref;
528 
	map { $user->{$_} = "" unless $user->{$_} } keys %{$user};
65 bgadell 529 
	return $user->{RCid} ? $user : "";
2 - 530 
}
531 
 
532 
sub getUserEmail {
533 
	my $RCid = shift;
534 
	my $sth = $dbh->prepare("select email from official where RCid = ?");
535 
	$sth->execute($RCid);
536 
	my ($email) = $sth->fetchrow_array();
537 
	return $email;
538 
}
539 
 
540 
sub getUserDerbyName {
541 
	my $RCid = shift;
542 
	my $sth = $dbh->prepare("select derby_name from official where RCid = ?");
543 
	$sth->execute($RCid);
544 
	my ($dname) = $sth->fetchrow_array();
545 
	return $dname;
546 
}
547 
 
548 
sub getYears {
50 bgadell 549 
	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");
550 
#	my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view");
2 - 551 
	$sth->execute();
552 
	my @years;
553 
	while (my ($y) =$sth->fetchrow_array()) { push @years, $y; }
554 
	return \@years;
555 
}
556 
 
557 
sub printRCHeader {
558 
	my $PAGE_TITLE = shift;
50 bgadell 559 
#	use CGI qw/start_html/;
7 - 560 
	use HTML::Tiny;
561 
  my $h = HTML::Tiny->new( mode => 'html' );
2 - 562 
 
7 - 563 
#  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 - 564 
  my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};
565 
  my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")."&nbsp;";
566 
  $logout .= url =~ /\/(index.pl)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/schedule/';" }, "Home")."&nbsp;";
567 
  $logout .= $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");
57 bgadell 568 
	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 - 569 
 
570 
  print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );
571 
 
572 
#<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title>
573 
#<link rel="stylesheet" type="text/css" href="/style.css">
574 
#</head>
575 
#<body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000">
576 
	print $h->div ({ class=>"sp0" }, [ $h->div ({ class=>"spLeft" },  $h->a ({ href=>"/schedule/" }, $h->img ({ src=>"/logo.jpg", width=>"75", height=>"75" }))),
577 
	                                   $h->div ({ class=>"spRight" }, [ $h->h1 (["vORC $PAGE_TITLE", $h->br]),
578 
	                                   $loggedinas,
579 
	                                   ])
580 
	                                 ]);
581 
#print<<rcheader;
582 
#  <TABLE>
583 
#	<TR class="nostripe">
584 
#		<TD align=right><img SRC="/logo.jpg"></TD>
585 
#		<TD align=center valign=middle><b><font size=+3>Officials' RollerCon<br>Schedule Manager<br>$PAGE_TITLE</FONT></b>
586 
#	<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>
587 
#	</TR>
2 - 588 
 
7 - 589 
#rcheader
2 - 590 
}
591 
 
592 
sub changeShift {
7 - 593 
	my ($change, $shift_id, $role, $user_id) = @_;
78 bgadell 594 
  if ($shift_id =~ /(am|pm)/) {
595 
    my ($td, $st, $tl) = split /\|/, $shift_id;
596 
    my ($hr, $min, $ampm) = split /:|\s/, $st;
597 
    if ($ampm eq "pm") { $hr += 12; }
598 
    elsif ($ampm eq "am" and $hr == 12) { $hr = "00" }
599 
 
600 
    $st = $hr.":".$min;
601 
    $shift_id = join "|", ($td, $st, $tl);
602 
  }
57 bgadell 603 
#warn join " - ", $change, $shift_id, $role, $user_id;
7 - 604 
	my $leadership_change = 0;
35 - 605 
#	my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
606 
	my $department;
607 
	if ($shift_id =~ /^\d+$/) {
608 
		$department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
609 
	} else {
610 
		$department = "CLA";
57 bgadell 611 
		if ($change eq "del") {
612 
		  ($shift_id, $role) = $dbh->selectrow_array ("select id, role from v_class_signup where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id);
613 
		} else {
78 bgadell 614 
		  ($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 615 
		}
616 
    $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 - 617 
	}
57 bgadell 618 
#	my $game_based = $role ? "game" : "shift";
619 
	my $game_based = $role =~ /^CLA-/ ? "class" : $role ? "game" : "shift";
7 - 620 
	my $sth;
2 - 621 
 
46 - 622 
	if ($change eq "add" or $change eq "override") {
7 - 623 
  	my $taken;
57 bgadell 624 
		if ($department eq "CLA") {
625 
  	  ($taken) = $shift_id ? 0 : 1;
626 
  	} elsif ($game_based eq "game") {
7 - 627 
  	  ($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);
628 
  	} else {
629 
  	  ($taken) = $dbh->selectrow_array ("select count(*) from shift where id = ? and isnull(assignee_id) = 0", undef, $shift_id);
630 
  	}
631 
  	if ($taken) {
35 - 632 
  	    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 - 633 
  	}
634 
  }
2 - 635 
 
7 - 636 
	if (lc ($user_id) ne lc ($ORCUSER->{RCid})) { # they're changing someone else's schedule...
35 - 637 
	  if ($ORCUSER->{department}->{$department} >= 2 or $ORCUSER->{access} >= 5 or $ORCUSER->{department}->{VCI} >= 2) {
638 
	    # the user making the change is either a lead in the dept, a sysadmin, or a VCI lead
7 - 639 
	    logit ($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $shift_id, $role, $user_id)");
640 
	    logit ($user_id, "Schedule was changed by $ORCUSER->{derby_name}. ($change, $shift_id, $role, $user_id)");
641 
	    $leadership_change = 1;
2 - 642 
	  } else {
7 - 643 
	    logit ($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $shift_id, $role, $user_id)");
644 
	    return "<br>Denied! You are not authorized to change someone else's schedule in this department ($department).<br>\n";
2 - 645 
	  }
7 - 646 
	} elsif ($ORCUSER->{department}->{$department} >= 3) {
647 
	  # Managers can sign up for as many shifts within their own department as they like...
648 
	  $leadership_change = 1;
2 - 649 
	}
650 
 
57 bgadell 651 
  if ($change eq "add") {
58 bgadell 652 
    if ($department eq "CLA" and !getUser($user_id)->{MVPid}) {
57 bgadell 653 
      return "<br>Denied! User ($user_id) does not have an MVP Pass!<br>\n";
96 bgadell 654 
    } elsif ($department ne "CLA" and getUser($user_id)->{department} and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {
57 bgadell 655 
      return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n" unless $department eq "CMP";
656 
    }
7 - 657 
  }
658 
 
46 - 659 
  my $conflict = findConflict ($user_id, $shift_id, $game_based);
660 
  if ($change eq "add" and $conflict) {
661 
		return "<br>Denied! There is a conflict ($conflict) with that shift's time!<br>\n";
7 - 662 
  }
663 
 
57 bgadell 664 
  my $game_type;
665 
  if ($department ne "CLA") {
666 
   	($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);
667 
 
668 
   	if ($game_type =~ /^selected/ and !$leadership_change) {
669 
   	  return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n" unless $department eq "CMP";
670 
   	}
671 
 
672 
   	if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {
673 
   	  return "<br>Denied! Shift reserved for leadership staff!<br>\n";
674 
   	}
675 
  } else {
676 
    $game_type = "class";
677 
  }
678 
 
7 - 679 
 
29 - 680 
# 	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");
681 
	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$department);
682 
	$MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $MAXSHIFTS;
683 
	if ($game_type eq "lead" and $department eq "OFF") { $MAXSHIFTS = 99; }
684 
 
35 - 685 
  my $daily_count;
686 
  if ($department eq "CLA") {
687 
    # MVP Class Sign-up
688 
    $MAXSHIFTS = getSetting ("MAX_CLASS_SIGNUP");
57 bgadell 689 
	  ($daily_count) = $dbh->selectrow_array ("select count(*) from v_class_signup where RCid = ?", undef, $user_id);
690 
#	  ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);
35 - 691 
   	if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
692 
	    return "<br>Denied! You may only sign up for $MAXSHIFTS Classes!<br>\n";
693 
	  }
694 
  } else {
695 
   	$daily_count = signUpCount ('get', $user_id, $department);
696 
   	if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
697 
   		return "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";
698 
   	}
82 bgadell 699 
   	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 700 
    	my $dept_table = $department eq 'OFF' ? "v_shift_officiating" : "v_shift_announcer";
701 
    	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);
702 
  		my $full_length_max = getSetting("MAX_FULL_LENGTH_SIGNUP_".$department);
703 
  		if ($full_length_count >= $full_length_max) {
704 
  		  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";
705 
  			return $errormsg;
706 
  		}
707 
    }
35 - 708 
  }
709 
 
7 - 710 
 	my @DBARGS;
57 bgadell 711 
  if ($game_based eq "game" or $game_based eq "class") {
46 - 712 
  	if ($change eq "add" or $change eq "override") {
7 - 713 
  		$sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
714 
  	} elsif ($change eq "del") {
715 
  		$sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
716 
  	}
717 
  	@DBARGS = ($shift_id, $role, $user_id);
718 
  } else {
46 - 719 
  	if ($change eq "add" or $change eq "override") {
35 - 720 
  		$sth = $dbh->prepare("update shift set assignee_id = ? where id = ? and isnull(assignee_id) = 1");
7 - 721 
  		@DBARGS = ($user_id, $shift_id);
722 
  	} elsif ($change eq "del") {
723 
  		$sth = $dbh->prepare("update shift set assignee_id = null where id = ?");
724 
  		@DBARGS = ($shift_id);
725 
  	}
726 
  }
727 
 
728 
  print "<br>attempting to make DB changes...<br>";
729 
  if ($sth->execute (@DBARGS)) {
29 - 730 
  	$daily_count = signUpCount ($change, $user_id, $department) unless $leadership_change;
7 - 731 
  	logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");
46 - 732 
  	logit ($ORCUSER->{RCid}, "OVERRIDE: Shift ".ucfirst($change).": $shift_id -> $role") if $change eq "override";
57 bgadell 733 
  	if ($department eq "CLA") {
734 
  	  print "Success!...<br>You've signed up for $daily_count class(es) (you're currently allowed to sign up for $MAXSHIFTS).<br>\n";
735 
  	} else {
736 
  	  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";
737 
  	}
7 - 738 
  	return;
739 
  } else {
57 bgadell 740 
  	if ($department eq "CLA") {
741 
      return "<br><b>You did not get the class</b>, most likely because it filled up while you were looking.<br>\nERROR: ", $sth->errstr();
742 
  	} else {
743 
      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();
744 
    }
7 - 745 
  }
746 
}
747 
 
748 
sub modShiftTime {
749 
	my ($shift_id, $user_id, $diff) = @_;
750 
	my $ORCUSER = getUser (1);
2 - 751 
 
7 - 752 
	use Scalar::Util qw(looks_like_number);
753 
	if (!looks_like_number ($diff)) {
754 
	  print "<br>ERROR! The time adjustment ($diff) doesn't look like a number.<br>\n";
755 
  	return;
2 - 756 
	}
757 
 
7 - 758 
  my ($validate_assignee) = $dbh->selectrow_array ("select count(*) from v_shift where id = ? and RCid = ?", undef, $shift_id, $user_id);
759 
 	if (!$validate_assignee) {
760 
	  print "<br>ERROR! This shift is assigned to someone else.<br>\n";
761 
  	return;
762 
 	}
763 
 
764 
	my $department = getShiftDepartment ($shift_id);
765 
  if (convertDepartments ($ORCUSER->{department})->{$department} < 2 and $ORCUSER->{access} < 5) {
766 
	  print "<br>ERROR! You're not authorized to modify this shift's time.<br>\n";
767 
	  logit ($ORCUSER->{RCid}, "Unauthorized attempt to modify shift time. ($department, $shift_id)");
768 
  	return;
769 
 	}
770 
 
771 
  my $rows_changed;
772 
  print "<br>attempting to make DB changes...<br>";
773 
  if ($diff == 0) {
774 
	  $rows_changed = $dbh->do ("update shift set mod_time = null where id = ? and assignee_id = ?", undef, $shift_id, $user_id);
775 
  } else {
776 
	  $rows_changed = $dbh->do ("update shift set mod_time = ? where id = ? and assignee_id = ?", undef, $diff, $shift_id, $user_id);
777 
  }
778 
 
779 
 
780 
  if (!$rows_changed or $dbh->errstr) {
781 
  	print "ERROR: Nothing got updated".$dbh->errstr;
782 
  	logit (0, "ERROR modifying a shift time ($diff, $shift_id, $user_id):".$dbh->errstr);
783 
  } else {
784 
  	print "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)";
785 
  	logit ($ORCUSER->{RCid}, "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)");
786 
 
787 
  }
788 
  return;
2 - 789 
}
790 
 
791 
sub signUpCount {
792 
	my $action = shift;
793 
	my $id = shift;
19 - 794 
	my $dept = shift // "";
2 - 795 
 
7 - 796 
	if ($id eq $ORCUSER->{RCid}) {
2 - 797 
		if ($action eq 'add') {
19 - 798 
			if (signUpCount ('get', $id, $dept)) {
799 
				$dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
2 - 800 
			} else {
19 - 801 
				$dbh->do("replace into sign_up_count (date, RCid, department, sign_ups) values (curdate(), ?, ?, 1)", undef, $id, $dept);
2 - 802 
			}
803 
		} elsif ($action eq 'del') {
19 - 804 
			if (signUpCount ('get', $id, $dept)) {
805 
				$dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
2 - 806 
			}
807 
		}
808 
	}
809 
 
19 - 810 
	my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and department = ? and date = curdate()", undef, $id, $dept);
2 - 811 
 
812 
	return $R ? $R : '0';
813 
}
814 
 
815 
sub signUpEligible {
816 
	my $user = shift;
817 
	my $t = shift;
7 - 818 
	my $shifttype = shift // "game";
19 - 819 
	my $dept = $t->{dept} // "";
68 bgadell 820 
  my $DEPTHASH = getDepartments ();
821 
  if ($dept and !exists $DEPTHASH->{$dept}) {
822 
    my %reverso = reverse %{$DEPTHASH};
823 
    $dept = $reverso{$dept};
824 
  }
2 - 825 
 
19 - 826 
	my $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$dept);
827 
	$limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $limit;
828 
 
72 bgadell 829 
	if (lc $t->{type} eq "lead" and $dept eq "OFF") { $limit = 99; }
29 - 830 
 
831 
	return 0 unless $limit > 0;
832 
 
19 - 833 
	my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";
834 
 
35 - 835 
	if ($shifttype eq "class") {
57 bgadell 836 
		($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 - 837 
		$t->{dept} = "CLA";
57 bgadell 838 
		$dept = "CLA";
35 - 839 
		$t->{type} = "open";
840 
	}
57 bgadell 841 
 
7 - 842 
	if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }
57 bgadell 843 
 
19 - 844 
	if (!exists $user->{$limitkey}) {
845 
		$user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);
2 - 846 
	}
847 
 
7 - 848 
	if ($shifttype eq "game") {
21 - 849 
#    if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{$limitkey} < $limit) {
75 bgadell 850 
		if ($t->{gtype} eq "full length" and ($dept eq "OFF" or $dept eq "ANN")) {
851 
		  my $table = $dept eq "OFF" ? "v_shift_officiating" : "v_shift_announcer";
852 
			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});
853 
			if ($full_length_count >= getSetting ("MAX_FULL_LENGTH_SIGNUP_".$dept)) {
854 
				return 0;
855 
			}
856 
		}
72 bgadell 857 
    if (lc $t->{signup} ne "selected" and $user->{$limitkey} < $limit) {
2 - 858 
			return 1;
859 
		} else {
860 
			return 0;
861 
		}
7 - 862 
	} else {
35 - 863 
    if ($dept eq "CLA") {
864 
      # MVP Class Sign-up
58 bgadell 865 
			return 0 unless $user->{MVPid};
35 - 866 
      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");
57 bgadell 867 
			my ($class_count) = $dbh->selectrow_array ("select count(*) from v_class_signup where RCid = ? and year(date) = year(now())", undef, $user->{RCid});
35 - 868 
			return 0 unless $class_count < $class_limit;
57 bgadell 869 
    } else {
68 bgadell 870 
	    if ($user->{department}->{$dept} < 1) { return 0; }
57 bgadell 871 
	  }
72 bgadell 872 
	  if (lc $t->{type} eq "lead" and $user->{department}->{$dept} < 2) { return 0; }
873 
	  if (lc $t->{type} eq "manager" and $user->{department}->{$dept} < 3) { return 0; }
874 
    if (lc $t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {
2 - 875 
			return 1;
876 
		} else {
877 
			return 0;
878 
		}
879 
	}
880 
}
881 
 
882 
sub findConflict {
883 
  my $rcid = shift;
884 
  my $gid = shift;
7 - 885 
  my $type = shift // "";
886 
  my ($date, $start, $end, $conflicts);
2 - 887 
 
7 - 888 
  if ($type eq "game") {
889 
  # Are they already signed up for this game? (It's faster to check the two views one at a time...)
890 
#    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where substring_index(id, '-', 1) = ? and RCid = ?", undef, $gid, $rcid);
891 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where id = ? and RCid = ?", undef, $gid, $rcid);
46 - 892 
  	if ($conflicts) { return "OFF-".$gid; } # no need to keep looking...
7 - 893 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);
46 - 894 
  	if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...
7 - 895 
 
896 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);
57 bgadell 897 
  } elsif ($type eq "class")  {
898 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup where id = ? and RCid = ?", undef, $gid, $rcid);
899 
  	if ($conflicts) { return "CLA:".$gid; } # no need to keep looking...
900 
 
901 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_class where id = ?", undef, $gid);
902 
 
7 - 903 
  } elsif ($type eq "personal")  {
904 
    ($date, $start, $end) = @{ $gid };
905 
  } else {
906 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);
907 
  }
2 - 908 
 
909 
  # Are they signed up for any games that would conflict with this one?
7 - 910 
#  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 = ?");
911 
#  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 - 912 
 
46 - 913 
  ($conflicts) = $dbh->selectrow_array ("select * from (
57 bgadell 914 
    select concat(dept, '-', id) from v_shift          where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union
915 
    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 - 916 
    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
917 
    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 918 
    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 - 919 
  );
920 
 
2 - 921 
  return $conflicts;
922 
}
923 
 
924 
sub changeLeadShift {
925 
	my ($change, $lshift, $user_id) = @_;
926 
	my $ERRMSG;
927 
 
928 
	my $sth = $dbh->prepare("update lead_shift set assignee_id = ? where id = ?");
929 
 
930 
	print "<br>attempting to make DB changes...<br>";
931 
	if ($change eq "add") {
932 
		$sth->execute($user_id, $lshift)
933 
    	or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
934 
	} elsif ($change eq "del") {
935 
		$sth->execute('', $lshift)
936 
    	or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
937 
	}
938 
	if ($ERRMSG) {
939 
		print $ERRMSG;
940 
	} else {
941 
		logit($user_id, "Lead Shift ".ucfirst($change).": $lshift");
942 
  	print "Success.<br>";
943 
  }
944 
}
945 
 
946 
sub logit {
947 
	my $RCid = shift;
948 
	my $msg = shift;
949 
	my $sth = $dbh->prepare("insert into log (RCid, event) values (?, ?)");
950 
	$sth->execute($RCid, $msg);
951 
}
952 
 
57 bgadell 953 
sub sendNewUserEMail {
954 
	my $context = shift;
955 
	my $data = shift;
956 
	use RCMailer;
957 
  use HTML::Tiny;
958 
  my $h = HTML::Tiny->new( mode => 'html' );
959 
  my $depts = getDepartments (); # HashRef of the department TLAs -> Display Names...
960 
  my $AccessLevel = getAccessLevels;
961 
 
962 
	my $email = $data->{email};
963 
	my $subject = 'RollerCon VORC - New User';
964 
	my $body;
965 
	if ($context eq "New User") {
966 
    $subject .= " Request";
967 
    my $activationlink = url ()."?activate=".$data->{activation};
968 
	  $body = $h->p ("Greetings,");
969 
	  $body .= $h->p ("It appears as though you've registered a new account in RollerCon's VORC system with the following information:");
970 
	  $body .= $h->table ([
971 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Derby Name:",    $data->{derby_name})]),
972 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Real Name:",     $data->{real_name})]),
973 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Pronouns:",      $data->{pronouns})]),
974 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "TShirt Size:",   $data->{tshirt})]),
975 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Email Address:", $data->{email})]),
976 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Phone:",         $data->{phone})])
977 
	  ]);
978 
    $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:",
979 
      $h->a ({ HREF=>$activationlink }, "Activate my VORC Account!"), $h->br,
980 
      "Or you can copy/paste this into the 'Activation Code' box: ".$data->{activation}, $h->br,
981 
      "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.",
982 
      "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.",
983 
      "If you're new to using vORC, you may want to read this:",
984 
      $h->a ({ HREF=>"https://volunteers.rollercon.com/info.html" }, "VORC User Info"),
985 
      "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.",
986 
      $h->br,
987 
      "--RollerCon HQ".$h->br.'rollercon@gmail.com'.$h->br."rollercon.com");
988 
  } elsif ($context eq "Activate") {
989 
    $subject .= " Activated!";
990 
    my $tempDepartments = convertDepartments ($data->{department});
991 
    my $printableDepartments = join "\n", map { $depts->{$_}.": ".$AccessLevel->{$tempDepartments->{$_}} } sort keys %{$tempDepartments};
992 
    $body = "Greetings again,
993 
 
994 
You have been approved to volunteer at RollerCon in the following departments:
995 
 
996 
$printableDepartments
997 
 
998 
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.
999 
 
1000 
https://volunteers.rollercon.com/schedule/
1001 
 
1002 
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?
1003 
 
1004 
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.
1005 
 
1006 
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.
1007 
 
1008 
If you're new to using vORC, you may want to read this:
1009 
 
1010 
https://volunteers.rollercon.com/info.html
1011 
 
1012 
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.
1013 
 
1014 
-RollerCon Management
1015 
";
1016 
  } else {
1017 
    return;
1018 
  }
1019 
	# send the message
1020 
	EmailUser ($email, $subject, $body);
1021 
 
1022 
}
1023 
 
1024 
 
2 - 1025 
1;