Subversion Repositories VORC

Rev

Rev 53 | Rev 58 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 53 Rev 57
Line 7... Line 7...
7 
use CGI qw/param header start_html url/;
 7 
use CGI qw/param header start_html url/;
8 
use CGI::Cookie;
 8 
use CGI::Cookie;
9 
use DBI;
 9 
use DBI;
10 
use WebDB;
 10 
use WebDB;
Line 11... Line 11...
11 
 
 11 
 
Line 12... Line 12...
12 
our @EXPORT = qw( $ORCUSER getRCDBH getAccessLevels authDB max authenticate getShiftDepartment getDepartments convertDepartments convertTime getSchedule getRCid getSetting getUser getUserEmail getUserDerbyName getYears printRCHeader changeShift modShiftTime signUpCount signUpEligible findConflict changeLeadShift logit );
 12 
our @EXPORT = qw( $ORCUSER 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 );
13 
 
 13 
 
14 
my $dbh = WebDB->connect ();
 14 
my $dbh = WebDB->connect ();
15 
sub getRCDBH {
 15 
sub getRCDBH {
16 
  return $dbh;
 16 
  return $dbh;
17 
}
 17 
}
- 
 
 18 
our $ORCUSER;
18 
our $ORCUSER;
 19 
use constant {
- 
 
 20 
    NOONE     => 0,
19 
use constant {
 21 
    USER      => 1,
20 
    USER   => 1,
 22 
    VOLUNTEER => 1,
21 
    LEAD   => 2,
 23 
    LEAD      => 2,
- 
 
 24 
    MANAGER   => 3,
22 
    MANAGER  => 3,
 25 
    DIRECTOR  => 4,
23 
    DIRECTOR  => 4,
 26 
    SYSADMIN  => 5,
Line 24... Line 27...
24 
    ADMIN  => 5
 27 
    ADMIN     => 5
25 
  };
 28 
  };
26 
 
 29 
 
Line 40... Line 43...
40 
sub authDB {
 43 
sub authDB {
41 
	my $src = shift;
 44 
	my $src = shift;
42 
	my $id = shift;
 45 
	my $id = shift;
43 
	my $pass = shift;
 46 
	my $pass = shift;
44 
	my $level = shift;
 47 
	my $level = shift;
- 
 
 48 
	my $activationcode = shift // "";
45 
	my ($result, $encpass);
 49 
	my ($result, $encpass);
Line 46... Line 50...
46
50
47 
	my $sth = $dbh->prepare("select * from official where email = ?");
 51 
	my $sth = $dbh->prepare("select * from official where email = ?");
48 
	$sth->execute($id);
 52 
	$sth->execute($id);
Line 55... Line 59...
55 
	} else {
 59 
	} else {
56 
		$encpass = $pass;
60 
		$encpass = $pass;
57 
	}
 61 
	}
Line 58... Line 62...
58
62
59 
	my $tempDepartments = convertDepartments ($RCDBIDHASH->{department});
 63 
	my $tempDepartments = convertDepartments ($RCDBIDHASH->{department});
Line 60... Line 64...
60 
	my $MAXACCESS = scalar keys %{ $tempDepartments } ? max ($RCDBIDHASH->{'access'}, values %{ $tempDepartments }) : $RCDBIDHASH->{'access'};
 64 
	my $MAXACCESS = scalar keys %{ $tempDepartments } ? max ($RCDBIDHASH->{'access'}, values %{ $tempDepartments } ) : $RCDBIDHASH->{'access'};
61
65
62 
	if (!$RCDBIDHASH->{'RCid'}) {
 66 
	if (!$RCDBIDHASH->{'RCid'}) {
63 
		$result->{ERRMSG} = "Email Address not found!";
 67 
		$result->{ERRMSG} = "Email Address not found!";
64 
		$result->{cookie_string} = '';
 68 
		$result->{cookie_string} = '';
65 
		$result->{RCid} = '';
 69 
		$result->{RCid} = '';
- 
 
 70 
		logit(0, "Account not found: $id");
66 
		logit(0, "Account not found: $id");
 71 
		$result->{authenticated} = 'false';
67 
		$result->{authenticated} = 'false';
 72 
		return $result;
68 
	} elsif ($RCDBIDHASH->{'password'} ne $encpass) {
 73 
	} elsif ($RCDBIDHASH->{'password'} ne $encpass) {
69 
		$result->{ERRMSG} = "Incorrect Password!";
 74 
		$result->{ERRMSG} = "Incorrect Password!";
70 
		$result->{cookie_string} = '';
 75 
		$result->{cookie_string} = '';
71 
		$result->{RCid} = $RCDBIDHASH->{'RCid'};
 76 
		$result->{RCid} = $RCDBIDHASH->{'RCid'};
- 
 
 77 
		logit($RCDBIDHASH->{'RCid'}, "Incorrect Password");
- 
 
 78 
		$result->{authenticated} = 'false';
- 
 
 79 
		return $result;
- 
 
 80 
  } elsif ($RCDBIDHASH->{'activation'} ne "active") {
- 
 
 81 
    # It's an inactive account...
- 
 
 82 
    if ($activationcode eq "resend") {
- 
 
 83 
      # warn "Resending activation code...";
- 
 
 84 
      sendNewUserEMail ("New User", $RCDBIDHASH);
- 
 
 85 
      $result->{ERRMSG} = "Activation code resent. Please check your email.";
- 
 
 86 
  		$result->{cookie_string} = "${id}&${encpass}&0";
- 
 
 87 
  		$result->{RCid} = $RCDBIDHASH->{'RCid'};
- 
 
 88 
  		logit($RCDBIDHASH->{'RCid'}, "Activation code resent.");
- 
 
 89 
  		$result->{authenticated} = 'inactive';
- 
 
 90 
  		return $result;
- 
 
 91 
    } elsif ($activationcode) {
- 
 
 92 
      # They sent an activation code
- 
 
 93 
      if ($activationcode eq $RCDBIDHASH->{'activation'}) {
- 
 
 94 
        # ...and it was good.
- 
 
 95 
        $dbh->do ("update official set activation = 'active', access = 1 where RCid = ? and activation = ?", undef, $RCDBIDHASH->{'RCid'}, $activationcode);
- 
 
 96 
        logit($RCDBIDHASH->{'RCid'}, "Activated their account and logged In");
- 
 
 97 
        # sendNewUserEMail ("Activate", $RCDBIDHASH);
- 
 
 98 
        $RCDBIDHASH->{'access'} = 1;
- 
 
 99 
        $RCDBIDHASH->{'activation'} = "active";
- 
 
 100 
        $MAXACCESS = max ($MAXACCESS, 1);
- 
 
 101 
      } else {
- 
 
 102 
        # ...but it wasn't good.
- 
 
 103 
        $result->{ERRMSG} = "Activation failed, invalid code submitted.";
- 
 
 104 
    		$result->{cookie_string} = "${id}&${encpass}&0";;
- 
 
 105 
    		$result->{RCid} = $RCDBIDHASH->{'RCid'};
- 
 
 106 
        logit($RCDBIDHASH->{'RCid'}, "Activation failed, invalid code submitted.");
- 
 
 107 
    		$result->{authenticated} = 'inactive';
- 
 
 108 
  	  	return $result;
- 
 
 109 
      }
- 
 
 110 
    } else {
- 
 
 111 
      # No activation code was submitted.
- 
 
 112 
  		$result->{ERRMSG} = "Inactive account! Please check your email for activation link/code." unless $result->{ERRMSG};
- 
 
 113 
  		$result->{cookie_string} = "${id}&${encpass}&0";
- 
 
 114 
  		$result->{RCid} = $RCDBIDHASH->{'RCid'};
- 
 
 115 
  		logit($RCDBIDHASH->{'RCid'}, "Login attempted without activation code.");
- 
 
 116 
  		$result->{authenticated} = 'inactive';
- 
 
 117 
  		return $result;
- 
 
 118 
    }
72 
		logit($RCDBIDHASH->{'RCid'}, "Incorrect Password");
 119 
	}
73 
		$result->{authenticated} = 'false';
 120
74 
	} elsif ($MAXACCESS < $level) {
 121 
	if ($MAXACCESS < $level) {
75 
	  if (getSetting ("MAINTENANCE")) {
 122 
	  if (getSetting ("MAINTENANCE")) {
76 
	    $result->{ERRMSG} = "MAINTENANCE MODE: Logins are temporarily disabled.";
 123 
	    $result->{ERRMSG} = "MAINTENANCE MODE: Logins are temporarily disabled.";
77 
	  } else {
 124 
	  } else {
Line 86... Line 133...
86 
		$RCDBIDHASH->{department} = convertDepartments ($RCDBIDHASH->{department});
 133 
		$RCDBIDHASH->{department} = convertDepartments ($RCDBIDHASH->{department});
87 
		$RCDBIDHASH->{'access'} = max ($RCDBIDHASH->{'access'}, values %{$RCDBIDHASH->{department}});
 134 
		$RCDBIDHASH->{'access'} = max ($RCDBIDHASH->{'access'}, values %{$RCDBIDHASH->{department}});
88 
		$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";
 135 
		$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";
89 
		$result->{RCid} = $RCDBIDHASH->{'RCid'};
 136 
		$result->{RCid} = $RCDBIDHASH->{'RCid'};
90 
		logit($RCDBIDHASH->{'RCid'}, "Logged In") if $src eq "form";
 137 
		logit($RCDBIDHASH->{'RCid'}, "Logged In") if $src eq "form";
91 
		$dbh->do ("update official set last_login = CONVERT_TZ(now(), 'America/Chicago', 'America/Los_Angeles') where RCid = ?", undef, $RCDBIDHASH->{'RCid'}) if $src eq "form";
 138 
#		$dbh->do ("update official set last_login = CONVERT_TZ(now(), 'America/Chicago', 'America/Los_Angeles') where RCid = ?", undef, $RCDBIDHASH->{'RCid'}) if $src eq "form";
- 
 
 139 
		$dbh->do ("update official set last_login = now() where RCid = ?", undef, $RCDBIDHASH->{'RCid'}) if $src eq "form";
92 
		$result->{authenticated} = 'true';
 140 
		$result->{authenticated} = 'true';
93 
#		my @depts = map { s/-\d// } split /:/, $RCDBIDHASH->{department};
 141 
#		my @depts = map { s/-\d// } split /:/, $RCDBIDHASH->{department};
94 
#		my @depts = split /:/, $RCDBIDHASH->{department};
 142 
#		my @depts = split /:/, $RCDBIDHASH->{department};
Line 95... Line 143...
95
143
Line 112... Line 160...
112 
	my $sth = $dbh->prepare("select * from official where email = '?'");
 160 
	my $sth = $dbh->prepare("select * from official where email = '?'");
Line 113... Line 161...
113
161
114 
	my $query = new CGI;
 162 
	my $query = new CGI;
115 
# Check to see if the user has already logged in (there should be cookies with their authentication)?
 163 
# Check to see if the user has already logged in (there should be cookies with their authentication)?
116 
	my $RCAUTH = $query->cookie('RCAUTH');
 164 
	my $RCAUTH = $query->cookie('RCAUTH');
117 
	$FORM{'ID'} = WebDB::trim $query->param('id') || '';
 165 
	$FORM{'ID'} = WebDB::trim $query->param('userid') || '';
118 
	$FORM{'PASS'} = WebDB::trim $query->param('pass') || '';
 166 
	$FORM{'PASS'} = WebDB::trim $query->param('pass') || '';
- 
 
 167 
	$FORM{'SUB'} = $query->param('login') || '';
Line -... Line 168...
- 
 
 168 
	$FORM{'activate'} = WebDB::trim $query->param('activate') // '';
- 
 
 169
- 
 
 170 
	if ($RCAUTH) {
- 
 
 171 
		#We have an authenication cookie.  Double-check it
119 
	$FORM{'SUB'} = $query->param('login') || '';
 172 
		my ($RCID, $RCPASS, $RCLVL) = split /&/, $RCAUTH;
120
173 
		$authenticated = authDB('cookie', $RCID, $RCPASS, $MINLEVEL, $FORM{'activate'});
121 
	if ($FORM{'SUB'}) {
 174 
	} elsif ($FORM{'SUB'}) {
122 
		#a log in form was submited
 175 
		#a log in form was submited
123 
		if ($FORM{'SUB'} eq "Submit") {
 176 
		if ($FORM{'SUB'} eq "Submit") {
124 
			$authenticated = authDB('form', $FORM{'ID'}, $FORM{'PASS'}, $MINLEVEL);
 177 
			$authenticated = authDB('form', $FORM{'ID'}, $FORM{'PASS'}, $MINLEVEL, $FORM{'activate'});
125 
		} elsif ($FORM{'SUB'} eq "New User") {
 178 
		} elsif ($FORM{'SUB'} eq "New User") {
126 
			# Print the new user form and exit
 - 
 
127 
		}
 - 
 
128 
	} elsif ($RCAUTH) {
 - 
 
129 
		#We have an authenication cookie.  Double-check it
 - 
 
130 
		my ($RCID, $RCPASS, $RCLVL) = split /&/, $RCAUTH;
 179 
			# Print the new user form and exit
131 
		$authenticated = authDB('cookie', $RCID, $RCPASS, $MINLEVEL);
 180 
		}
132 
	} else {
 181 
	} else {
Line 140... Line 189...
140
189
Line 141... Line 190...
141
190
Line 142... Line 191...
142 
 
 191 
 
143 
# If we get here, the user has failed authentication; throw up the log-in screen and die.
 192 
# If we get here, the user has failed authentication; throw up the log-in screen and die.
144 
 
 193 
 
145 
	my $RCAUTH_cookie = CGI::Cookie->new(-name=>'RCAUTH',-value=>"",-expires=>"now");
 194 
	my $RCAUTH_cookie = CGI::Cookie->new(-name=>'RCAUTH',-value=>$authenticated->{cookie_string},-expires=>"+30m");
146 
 
 195 
 
147 
if ($authenticated->{ERRMSG}) {
 196 
  if ($authenticated->{ERRMSG}) {
148 
	$authenticated->{ERRMSG} = "<TR><TD colspan=2 align=center><font color=red><b>".$authenticated->{ERRMSG}."</b></font>&nbsp</TD></TR>";
 197 
  	$authenticated->{ERRMSG} = "<TR><TD colspan=2 align=center><font color=red><b>".$authenticated->{ERRMSG}."</b></font>&nbsp</TD></TR>";
Line 149... Line 198...
149 
	# Log the failed access attempt
 198 
  	# Log the failed access attempt
150 
} else {
 199 
  } else {
151 
	$authenticated->{ERRMSG} = "";
 200 
  	$authenticated->{ERRMSG} = "";
152 
	# Since there was no ERRMSG, no need to log anything.
 201 
  	# Since there was no ERRMSG, no need to log anything.
153 
}
 202 
  }
154 
 
 203 
 
155 
	print header(-cookie=>$RCAUTH_cookie);
 204 
	print header(-cookie=>$RCAUTH_cookie);
156 
	printRCHeader("Please Sign In");
 205 
	printRCHeader("Please Sign In");
157 
	print<<authpage;
206 
	print<<authpage;
158 
	<form action="$ENV{REQUEST_URI}" method=POST name=Req id=Req>
 - 
 
159 
		<TR><TD colspan=2 align=center><b><font size=+2>Please Sign In</font>
 - 
 
160 
		<TABLE>
 - 
 
161 
		</TD></TR>
 - 
 
162 
		<TR><TD colspan=2>&nbsp</TD></TR>
 - 
 
163 
		$authenticated->{ERRMSG}
 - 
 
164 
		<TR>
 - 
 
165 
			<TD align=right><B>Email Address:</TD><TD><INPUT type=text id=login name=id></TD>
 - 
 
166 
		</TR>
 - 
 
167 
		<TR>
 - 
 
168 
			<TD align=right><B>Password:</TD><TD><INPUT type=password name=pass></TD>
 - 
 
169 
		</TR>
 207 
	<form action="$ENV{REQUEST_URI}" method=POST name=Req id=Req>
170 
		<TR><TD></TD><TD><INPUT type=submit name=login value=Submit></TD></TR>
 - 
 
171 
		<TR><TD colspan=2 align=center>&nbsp;</TD></TR>
 - 
 
172 
		<TR><TD colspan=2 align=center><A HREF="/schedule/manage_user.pl?submit=New%20User">[register as a new user]</A></TD></TR>
 - 
 
173 
		<TR><TD colspan=2 align=center><A HREF="/schedule/password_reset.pl">[reset your password]</A></TD></TR>
 - 
 
174 
	</TABLE>
 - 
 
175 
	</FORM>
 - 
 
176 
 
 - 
 
177 
	<SCRIPT language="JavaScript">
 - 
 
178 
	<!--
 - 
 
179 
	document.getElementById("login").focus();
 - 
 
Line -... Line 208...
- 
 
 208 
		<TR><TD colspan=2 align=center><b><font size=+2>Please Sign In</font>
Line -... Line 209...
- 
 
 209 
		<TABLE>
- 
 
 210 
		</TD></TR>
- 
 
 211 
		<TR><TD colspan=2>&nbsp</TD></TR>
- 
 
 212 
		$authenticated->{ERRMSG}
- 
 
 213 
authpage
- 
 
 214 
 
- 
 
 215 
  if ($authenticated->{authenticated} eq "inactive") {
180
216 
 
- 
 
 217 
    print<<activationpage;
- 
 
 218 
      <TR><TD colspan=2 align=center>&nbsp;</TD></TR>
181 
	function Login () {
 219 
      <TR><TD align=right><B>Activation Code:</TD><TD><INPUT type=text id=activate name=activate></TD></TR>
Line -... Line 220...
- 
 
 220 
      <TR><TD></TD><TD><INPUT type=submit name=login value=Submit></TD></TR>
- 
 
 221 
      <TR><TD colspan=2 align=center>&nbsp;</TD></TR>
- 
 
 222 
      <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>
- 
 
 223 
      <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>
- 
 
 224 
      </TABLE></FORM>
- 
 
 225 
activationpage
- 
 
 226
- 
 
 227 
  } else {
- 
 
 228 
 
- 
 
 229 
    print<<authpage2;
- 
 
 230 
  		<TR>
- 
 
 231 
  			<TD align=right><B>Email Address:</TD><TD><INPUT type=text id=login name=userid></TD>
- 
 
 232 
  		</TR>
- 
 
 233 
  		<TR>
- 
 
 234 
  			<TD align=right><B>Password:</TD><TD><INPUT type=password name=pass></TD>
- 
 
 235 
  		</TR>
- 
 
 236 
  		<TR><TD></TD><TD><input type=hidden name=activate id=activate value=$FORM{'activate'}><INPUT type=submit name=login value=Submit></TD></TR>
- 
 
 237 
  		<TR><TD colspan=2 align=center>&nbsp;</TD></TR>
- 
 
 238 
  		<TR><TD colspan=2 align=center><A HREF="/schedule/view_user.pl?submit=New%20User">[register as a new user]</A></TD></TR>
- 
 
 239 
  		<TR><TD colspan=2 align=center><A HREF="/schedule/password_reset.pl">[reset your password]</A></TD></TR>
- 
 
 240 
  	</TABLE>
- 
 
 241 
  	</FORM>
- 
 
 242 
 
- 
 
 243 
  	<SCRIPT language="JavaScript">
- 
 
 244 
  	<!--
- 
 
 245 
  	document.getElementById("login").focus();
- 
 
 246
- 
 
 247 
  	function Login () {
182 
		document.getElementById('Req').action = "$ENV{SCRIPT_NAME}";
 248 
  		document.getElementById('Req').action = "$ENV{SCRIPT_NAME}";
- 
 
 249 
  		document.getElementById('Req').submit.click();
Line 183... Line 250...
183 
		document.getElementById('Req').submit.click();
 250 
  		return true;
184 
		return true;
 251 
  	}
185 
	}
 252 
 
186 
 
 253 
 
187 
 
 254 
  	//-->
188 
	//-->
 255 
  	</SCRIPT>
Line -... Line 256...
- 
 
 256 
 
- 
 
 257 
authpage2
- 
 
 258 
}
- 
 
 259 
 
- 
 
 260 
#foreach (keys %ENV) {
- 
 
 261 
#	print "$_: $ENV{$_}<br>";
- 
 
 262 
#}
- 
 
 263 
#	&JScript;
- 
 
 264 
	exit;
- 
 
 265 
}
- 
 
 266 
 
- 
 
 267 
sub canView {
- 
 
 268 
	my $A = shift // "";
- 
 
 269 
	my $B = shift // "";
- 
 
 270 
	# Is A a lead or higher of one of B's Depts? (or they're looking at themselves)
- 
 
 271 
	# parameters should be a Hashref to the users' details
- 
 
 272
- 
 
 273 
	return 1 if $A->{access} > 4 or $A->{RCid} == $B->{RCid}; # viewer and target are the same person or it's a SysAdmin.
- 
 
 274 
 
- 
 
 275 
	my $ADept = ref $A->{department} eq "HASH" ? $A->{department} : convertDepartments $A->{department};
- 
 
 276 
	my $BDept = ref $B->{department} eq "HASH" ? $B->{department} : convertDepartments $B->{department};
- 
 
 277
- 
 
 278 
	foreach (keys %{$BDept}) {
- 
 
 279 
		if ($ADept->{$_} > 1) { # A is a Lead or higher of one of B's departments
- 
 
 280 
			return 1;
189 
	</SCRIPT>
 281 
		}
190 
 
 282 
	}
191 
authpage
 283
Line 192... Line 284...
192 
 
 284 
	if ($ADept->{MVP} >= RollerCon::VOLUNTEER and $B->{mvp_pass} = 1) {
193 
#foreach (keys %ENV) {
 285 
	  # MVP Volunteers can see user details for people with MVP Passes
194 
#	print "$_: $ENV{$_}<br>";
 286 
	  return 1;
195 
#}
 287 
	}
- 
 
 288
- 
 
 289 
	return 0;
- 
 
 290 
}
196 
#	&JScript;
 291 
 
- 
 
 292 
sub getShiftDepartment {
197 
	exit;
 293 
  my $shiftID = shift // "";
198 
}
 294 
  my $dept;
199 
 
 295
200 
sub getShiftDepartment {
 296 
  if ($shiftID =~ /^\d+$/) {
201 
  my $shiftID = shift // "";
 297 
    ($dept) = $dbh->selectrow_array ("select dept from shift where id = ?", undef, $shiftID);
202 
  my $dept;
 298 
  } else {
Line 203... Line 299...
203
299 
    my ($id, $role) = split /-/, $shiftID;
204 
  if ($shiftID =~ /^\d+$/) {
 300 
    if ($role =~ /^CLA/) {
Line -... Line 301...
- 
 
 301 
      $dept = "CLA";
- 
 
 302 
    } else {
- 
 
 303 
      ($dept) = $dbh->selectrow_array ("select distinct department from staff_template where role like ?", undef, $role.'%');
- 
 
 304 
    }
- 
 
 305 
  }
- 
 
 306 
#  } elsif ($shiftID =~ /^\d+-ANN/) {
- 
 
 307 
#    $dept = "ANN";
- 
 
 308 
#  } else {
- 
 
 309 
#    $dept = "OFF";
- 
 
 310 
#  }
- 
 
 311
- 
 
 312 
  return $dept;
- 
 
 313 
}
- 
 
 314 
 
- 
 
 315 
sub getClassID {
- 
 
 316 
  my $shift = shift // "";
- 
 
 317 
  return unless $shift =~ /^\d+$/;
- 
 
 318
- 
 
 319 
  my $shiftref = getShiftRef ($shift);
- 
 
 320 
  my ($classid) = $dbh->selectrow_array ("select id from class where date = ? and start_time = ? and location = ?", undef, $shiftref->{date}, $shiftref->{start_time}, $shiftref->{location});
- 
 
 321 
  return $classid unless !$classid;
- 
 
 322
- 
 
 323 
  warn "ERROR: No class.id found for shift $shiftref->{id}";
205 
    ($dept) = $dbh->selectrow_array ("select dept from shift where id = ?", undef, $shiftID);
 324 
  return "";
206 
  } else {
 325 
}
207 
    my ($id, $role) = split /-/, $shiftID;
 326 
 
208 
    ($dept) = $dbh->selectrow_array ("select distinct department from staff_template where role like ?", undef, $role.'%');
 327 
sub getShiftRef {
Line 259... Line 378...
259 
  my $time = shift || return;
 378 
  my $time = shift || return;
Line 260... Line 379...
260
379
261 
  if ($time =~ / - /) {
 380 
  if ($time =~ / - /) {
262 
    return join " - ", map { convertTime ($_) } split / - /, $time;
 381 
    return join " - ", map { convertTime ($_) } split / - /, $time;
263 
  }
 382 
  }
- 
 
 383
- 
 
 384 
  $time =~ s/^(\d{1,2}:\d{2}):\d{2}$/$1/;
264
385
265 
  if ($ORCUSER->{timeformat} eq "24hr") {
 386 
  if ($ORCUSER->{timeformat} eq "24hr") {
266 
    if ($time =~ /^\d{1,2}:\d{2}$/) { return $time; }
387 
    if ($time =~ /^\d{1,2}:\d{2}$/) { return $time; }
267 
  } else {
 388 
  } else {
268 
    my ($hr, $min) = split /:/, $time;
 389 
    my ($hr, $min) = split /:/, $time;
Line 303... Line 424...
303
424
304 
  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";
 425 
  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";
305 
  my @shifts;
 426 
  my @shifts;
306 
  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
 427 
  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
307 
                                          select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union
 428 
                                          select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union
- 
 
 429 
                                          select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ? union
308 
                                          select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ?) temp
 430 
                                          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
309 
                           $where order by date, time");
 431 
                           $where order by date, time");
310 
  $sth->execute($RCid, $RCid, $RCid);
 432 
  $sth->execute($RCid, $RCid, $RCid, $RCid);
311 
  my $hours = 0;
 433 
  my $hours = 0;
312 
  while (my $s = $sth->fetchrow_hashref) {
 434 
  while (my $s = $sth->fetchrow_hashref) {
313 
    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};
 435 
    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};
314 
	  my $cutoff = DateTime->new(
 436 
	  my $cutoff = DateTime->new(
Line 325... Line 447...
325 
  	if (!$s->{teams}) {
 447 
  	if (!$s->{teams}) {
326 
  	  # it's a time-based shift
 448 
  	  # it's a time-based shift
327 
  	  if ($s->{dept} eq "PER") {
 449 
  	  if ($s->{dept} eq "PER") {
328 
        if ($RCid eq $ORCUSER->{RCid}) {
 450 
        if ($RCid eq $ORCUSER->{RCid}) {
329 
          # DROP
 451 
          # DROP
330 
  	      $s->{buttons} = $h->button ({ onClick=>"if (confirm('Really? You want to delete this personal time?')==true) { window.open('manage_personal_time.pl?choice=Delete&id=$s->{id}','Confirm Change','resizable,height=260,width=370'); return false; }" }, "DEL")."&nbsp;".$h->button ({ onClick=>"location.href='manage_personal_time.pl?choice=Update&id=$s->{id}'" }, "EDIT");
 452 
  	      $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");
331 
  	    } else {
 453 
  	    } else {
332 
  	      $s->{location} = "";
 454 
  	      $s->{location} = "";
333 
  	      $s->{role} = "";
 455 
  	      $s->{role} = "";
334 
  	    }
 456 
  	    }
335 
      } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
 457 
      } elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
336 
        # DROP
 458 
        # DROP
- 
 
 459 
        my ($shiftORclass, $linkargs) = ("shift", "");
- 
 
 460 
        if ($s->{dept} eq "CLA") {
- 
 
 461 
          $shiftORclass = "class";
- 
 
 462 
          $linkargs = "&role=$s->{role}";
- 
 
 463 
        }
337 
  		  $s->{buttons} = $h->button ({ onClick=>"if (confirm('Really? You want to drop this shift?')==true) { window.open('make_shift_change.pl?change=del&id=$s->{id}','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "DROP");
 464 
	   		$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");
338 
	   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
 465 
	   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
339 
   		    # NO SHOW
 466 
   		    # NO SHOW
340 
 	  	    $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}','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "NO SHOW");
 467 
 	  	    $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");
341 
 		    }
 468 
 		    }
342 
        $hours += $s->{volhours} unless $s->{dept} eq "CLA";
 469 
        $hours += $s->{volhours} unless $s->{dept} eq "CLA";
343 
  		}
 470 
  		}
Line 344... Line 471...
344 
 
 471 
 
Line 387... Line 514...
387 
	  $sth = $dbh->prepare("select * from official where RCid = ?");
 514 
	  $sth = $dbh->prepare("select * from official where RCid = ?");
388 
	} else {
 515 
	} else {
389 
	  $sth = $dbh->prepare("select * from official where email = ?");
 516 
	  $sth = $dbh->prepare("select * from official where email = ?");
390 
  }
 517 
  }
391 
	$sth->execute($ID);
 518 
	$sth->execute($ID);
- 
 
 519
392 
	return $sth->fetchrow_hashref;
 520 
	my $user = $sth->fetchrow_hashref;
- 
 
 521 
	map { $user->{$_} = "" unless $user->{$_} } keys %{$user};
- 
 
 522 
	return $user;
393 
}
 523 
}
Line 394... Line 524...
394 
 
 524 
 
395 
sub getUserEmail {
 525 
sub getUserEmail {
396 
	my $RCid = shift;
 526 
	my $RCid = shift;
Line 426... Line 556...
426 
#  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]");
 556 
#  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]");
427 
  my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};
 557 
  my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};
428 
  my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")."&nbsp;";
 558 
  my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")."&nbsp;";
429 
  $logout .= url =~ /\/(index.pl)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/schedule/';" }, "Home")."&nbsp;";
 559 
  $logout .= url =~ /\/(index.pl)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/schedule/';" }, "Home")."&nbsp;";
430 
  $logout .= $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");
 560 
  $logout .= $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");
431 
	my $loggedinas = $ORCUSER ? "Currently logged in as: ".$h->a ({ href=>"/schedule/manage_user.pl?submit=View&RCid=$ORCUSER->{RCid}" }, $ORCUSER->{derby_name}).$h->br.$logout : "";
 561 
	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 : "";
Line 432... Line 562...
432
562
Line 433... Line 563...
433 
  print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );
 563 
  print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );
434
564
Line 452... Line 582...
452 
#rcheader
 582 
#rcheader
453 
}
 583 
}
Line 454... Line 584...
454 
 
 584 
 
455 
sub changeShift {
 585 
sub changeShift {
- 
 
 586 
	my ($change, $shift_id, $role, $user_id) = @_;
456 
	my ($change, $shift_id, $role, $user_id) = @_;
 587 
#warn join " - ", $change, $shift_id, $role, $user_id;
457 
	my $leadership_change = 0;
 588 
	my $leadership_change = 0;
458 
#	my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
 589 
#	my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
459 
	my $department;
 590 
	my $department;
460 
	if ($shift_id =~ /^\d+$/) {
 591 
	if ($shift_id =~ /^\d+$/) {
461 
		$department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
 592 
		$department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
462 
	} else {
 593 
	} else {
- 
 
 594 
		$department = "CLA";
- 
 
 595 
		if ($change eq "del") {
- 
 
 596 
		  ($shift_id, $role) = $dbh->selectrow_array ("select id, role from v_class_signup where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id);
463 
		$department = "CLA";
 597 
		} else {
- 
 
 598 
		  ($shift_id, $role) = $dbh->selectrow_array ("select id, concat('CLA-', max(substring_index(role, '-', -1)) +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);
- 
 
 599 
		}
464 
		($shift_id) = $dbh->selectrow_array ("select min(id) from v_shift where date = ? and start_time = ? and location = ? and isnull(RCid) = 1", undef, split /\|/, $shift_id);
 600 
    $role = "CLA-1" unless $role; # If no one has signed up for the class yet, the SQL above doesn't retrieve the first available
465 
	}
 601 
	}
- 
 
 602 
#	my $game_based = $role ? "game" : "shift";
466 
	my $game_based = $role ? "game" : "shift";
 603 
	my $game_based = $role =~ /^CLA-/ ? "class" : $role ? "game" : "shift";
Line 467... Line 604...
467 
	my $sth;
 604 
	my $sth;
468
605
469 
	if ($change eq "add" or $change eq "override") {
 - 
 
470 
  	my $taken;
 - 
 
471 
  	if ($game_based eq "game") {
 606 
	if ($change eq "add" or $change eq "override") {
472 
  	  ($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);
 607 
  	my $taken;
- 
 
 608 
		if ($department eq "CLA") {
- 
 
 609 
  	  ($taken) = $shift_id ? 0 : 1;
473 
		} elsif ($department eq "CLA") {
 610 
  	} elsif ($game_based eq "game") {
474 
  	  ($taken) = $shift_id ? 0 : 1;
 611 
  	  ($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);
475 
  	} else {
 612 
  	} else {
476 
  	  ($taken) = $dbh->selectrow_array ("select count(*) from shift where id = ? and isnull(assignee_id) = 0", undef, $shift_id);
 613 
  	  ($taken) = $dbh->selectrow_array ("select count(*) from shift where id = ? and isnull(assignee_id) = 0", undef, $shift_id);
477 
  	}
 614 
  	}
Line 493... Line 630...
493 
	} elsif ($ORCUSER->{department}->{$department} >= 3) {
 630 
	} elsif ($ORCUSER->{department}->{$department} >= 3) {
494 
	  # Managers can sign up for as many shifts within their own department as they like...
 631 
	  # Managers can sign up for as many shifts within their own department as they like...
495 
	  $leadership_change = 1;
 632 
	  $leadership_change = 1;
496 
	}
 633 
	}
Line -... Line 634...
- 
 
 634
- 
 
 635 
  if ($change eq "add") {
- 
 
 636 
    if ($department eq "CLA" and getUser($user_id)->{mvp_pass} < 1) {
497
637 
      return "<br>Denied! User ($user_id) does not have an MVP Pass!<br>\n";
498 
  if ($change eq "add" and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {
 638 
    } elsif ($department ne "CLA" and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {
- 
 
 639 
      return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n" unless $department eq "CMP";
499 
		return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n" unless $department eq "CMP";
 640 
    }
Line 500... Line 641...
500 
  }
 641 
  }
501
642
502 
  my $conflict = findConflict ($user_id, $shift_id, $game_based);
 643 
  my $conflict = findConflict ($user_id, $shift_id, $game_based);
503 
  if ($change eq "add" and $conflict) {
 644 
  if ($change eq "add" and $conflict) {
Line -... Line 645...
- 
 
 645 
		return "<br>Denied! There is a conflict ($conflict) with that shift's time!<br>\n";
- 
 
 646 
  }
504 
		return "<br>Denied! There is a conflict ($conflict) with that shift's time!<br>\n";
 647
- 
 
 648 
  my $game_type;
505 
  }
 649 
  if ($department ne "CLA") {
506
650 
   	($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);
507 
 	my ($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);
 651
508 
 	if ($game_type =~ /^selected/ and !$leadership_change) {
 652 
   	if ($game_type =~ /^selected/ and !$leadership_change) {
509 
 	  return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n" unless $department eq "CMP";
 653 
   	  return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n" unless $department eq "CMP";
510 
 	}
 654 
   	}
- 
 
 655
- 
 
 656 
   	if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {
- 
 
 657 
   	  return "<br>Denied! Shift reserved for leadership staff!<br>\n";
511
658 
   	}
- 
 
 659 
  } else {
Line 512... Line 660...
512 
 	if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {
 660 
    $game_type = "class";
513 
 	  return "<br>Denied! Shift reserved for leadership staff!<br>\n";
 661 
  }
514 
 	}
 662
515 
 
 663 
 
Line 516... Line 664...
516 
# 	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");
 664 
# 	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");
517 
	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$department);
 665 
	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$department);
518 
	$MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $MAXSHIFTS;
 666 
	$MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $MAXSHIFTS;
519 
	if ($game_type eq "lead" and $department eq "OFF") { $MAXSHIFTS = 99; }
 667 
	if ($game_type eq "lead" and $department eq "OFF") { $MAXSHIFTS = 99; }
- 
 
 668 
 
520 
 
 669 
  my $daily_count;
521 
  my $daily_count;
 670 
  if ($department eq "CLA") {
522 
  if ($department eq "CLA") {
 671 
    # MVP Class Sign-up
523 
    # MVP Class Sign-up
 672 
    $MAXSHIFTS = getSetting ("MAX_CLASS_SIGNUP");
524 
    $MAXSHIFTS = getSetting ("MAX_CLASS_SIGNUP");
 673 
	  ($daily_count) = $dbh->selectrow_array ("select count(*) from v_class_signup where RCid = ?", undef, $user_id);
525 
	  ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);
 674 
#	  ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);
Line 538... Line 687...
538 
#  		}
687 
#  		}
539 
#    }
 688 
#    }
540 
  }
 689 
  }
Line 541... Line 690...
541
690
542 
 	my @DBARGS;
 691 
 	my @DBARGS;
543 
  if ($game_based eq "game") {
 692 
  if ($game_based eq "game" or $game_based eq "class") {
544 
  	if ($change eq "add" or $change eq "override") {
 693 
  	if ($change eq "add" or $change eq "override") {
545 
  		$sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
 694 
  		$sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
546 
  	} elsif ($change eq "del") {
 695 
  	} elsif ($change eq "del") {
547 
  		$sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
 696 
  		$sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
Line 560... Line 709...
560 
  print "<br>attempting to make DB changes...<br>";
 709 
  print "<br>attempting to make DB changes...<br>";
561 
  if ($sth->execute (@DBARGS)) {
 710 
  if ($sth->execute (@DBARGS)) {
562 
  	$daily_count = signUpCount ($change, $user_id, $department) unless $leadership_change;
 711 
  	$daily_count = signUpCount ($change, $user_id, $department) unless $leadership_change;
563 
  	logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");
 712 
  	logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");
564 
  	logit ($ORCUSER->{RCid}, "OVERRIDE: Shift ".ucfirst($change).": $shift_id -> $role") if $change eq "override";
 713 
  	logit ($ORCUSER->{RCid}, "OVERRIDE: Shift ".ucfirst($change).": $shift_id -> $role") if $change eq "override";
- 
 
 714 
  	if ($department eq "CLA") {
- 
 
 715 
  	  print "Success!...<br>You've signed up for $daily_count class(es) (you're currently allowed to sign up for $MAXSHIFTS).<br>\n";
- 
 
 716 
  	} else {
565 
  	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";
 717 
  	  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";
- 
 
 718 
  	}
566 
  	return;
 719 
  	return;
567 
  } else {
 720 
  } else {
- 
 
 721 
  	if ($department eq "CLA") {
- 
 
 722 
      return "<br><b>You did not get the class</b>, most likely because it filled up while you were looking.<br>\nERROR: ", $sth->errstr();
- 
 
 723 
  	} else {
568 
    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();
 724 
      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();
- 
 
 725 
    }
569 
  }
 726 
  }
570 
}
 727 
}
Line 571... Line 728...
571 
 
 728 
 
572 
sub modShiftTime {
 729 
sub modShiftTime {
Line 650... Line 807...
650 
	return 0 unless $limit > 0;
 807 
	return 0 unless $limit > 0;
Line 651... Line 808...
651
808
Line 652... Line 809...
652 
	my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";
 809 
	my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";
653
810
654 
	if ($shifttype eq "class") {
 811 
	if ($shifttype eq "class") {
- 
 
 812 
		($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});
655 
		($t->{id}) = $dbh->selectrow_array ("select min(id) from v_shift where isnull(RCid) = 1 and dept = ? and date = ? and location = ? and start_time = ?", undef, "CLA", $t->{date}, $t->{location}, $t->{start_time});
 813 
		$t->{dept} = "CLA";
656 
		$t->{dept} = "CLA";
 814 
		$dept = "CLA";
657 
		$t->{type} = "open";
 815 
		$t->{type} = "open";
658 
	}
 816 
	}
659
817
660 
	if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }
 818 
	if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }
661 
 
 819
662 
	if (!exists $user->{$limitkey}) {
 820 
	if (!exists $user->{$limitkey}) {
Line 663... Line 821...
663 
		$user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);
 821 
		$user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);
Line 677... Line 835...
677 
			return 0;
 835 
			return 0;
678 
		}
 836 
		}
679 
	} else {
 837 
	} else {
680 
    if ($dept eq "CLA") {
 838 
    if ($dept eq "CLA") {
681 
      # MVP Class Sign-up
 839 
      # MVP Class Sign-up
- 
 
 840 
			return 0 unless $user->{mvp_pass};
682 
      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");
 841 
      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");
683 
			my ($class_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user->{RCid});
 842 
			my ($class_count) = $dbh->selectrow_array ("select count(*) from v_class_signup where RCid = ? and year(date) = year(now())", undef, $user->{RCid});
684 
			return 0 unless $class_count < $class_limit;
 843 
			return 0 unless $class_count < $class_limit;
685 
    }
 844 
    } else {
686 
	  if ($user->{department}->{$t->{dept}} < 1) { return 0; }
 845 
	    if ($user->{department}->{$t->{dept}} < 1) { return 0; }
- 
 
 846 
	  }
687 
	  if ($t->{type} eq "lead" and $user->{department}->{$t->{dept}} < 2) { return 0; }
 847 
	  if ($t->{type} eq "lead" and $user->{department}->{$t->{dept}} < 2) { return 0; }
688 
	  if ($t->{type} eq "manager" and $user->{department}->{$t->{dept}} < 3) { return 0; }
 848 
	  if ($t->{type} eq "manager" and $user->{department}->{$t->{dept}} < 3) { return 0; }
689 
    if ($t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {
 849 
    if ($t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {
690 
			return 1;
 850 
			return 1;
691 
		} else {
 851 
		} else {
Line 707... Line 867...
707 
  	if ($conflicts) { return "OFF-".$gid; } # no need to keep looking...
 867 
  	if ($conflicts) { return "OFF-".$gid; } # no need to keep looking...
708 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);
 868 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);
709 
  	if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...
 869 
  	if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...
Line 710... Line 870...
710
870
- 
 
 871 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);
- 
 
 872 
  } elsif ($type eq "class")  {
- 
 
 873 
    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup where id = ? and RCid = ?", undef, $gid, $rcid);
- 
 
 874 
  	if ($conflicts) { return "CLA:".$gid; } # no need to keep looking...
- 
 
 875
- 
 
 876 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_class where id = ?", undef, $gid);
711 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);
877
712 
  } elsif ($type eq "personal")  {
 878 
  } elsif ($type eq "personal")  {
713 
    ($date, $start, $end) = @{ $gid };
 879 
    ($date, $start, $end) = @{ $gid };
714 
  } else {
 880 
  } else {
715 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);
881 
    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);
Line 718... Line 884...
718 
  # Are they signed up for any games that would conflict with this one?
 884 
  # Are they signed up for any games that would conflict with this one?
719 
#  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 = ?");
 885 
#  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 = ?");
720 
#  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 = ?");
 886 
#  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 = ?");
Line 721... Line 887...
721
887
722 
  ($conflicts) = $dbh->selectrow_array ("select * from (
 888 
  ($conflicts) = $dbh->selectrow_array ("select * from (
- 
 
 889 
    select concat(dept, '-', id) from v_shift          where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union
723 
    select concat(dept, '-', id) from v_shift             where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union
 890 
    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
724 
    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
 891 
    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
725 
    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",
 892 
    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",
726 
    undef, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid
 893 
    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
Line 727... Line 894...
727 
  );
 894 
  );
728
895
Line 756... Line 923...
756 
	my $msg = shift;
 923 
	my $msg = shift;
757 
	my $sth = $dbh->prepare("insert into log (RCid, event) values (?, ?)");
 924 
	my $sth = $dbh->prepare("insert into log (RCid, event) values (?, ?)");
758 
	$sth->execute($RCid, $msg);
 925 
	$sth->execute($RCid, $msg);
759 
}
 926 
}
Line -... Line 927...
- 
 
 927 
 
- 
 
 928 
sub sendNewUserEMail {
- 
 
 929 
	my $context = shift;
- 
 
 930 
	my $data = shift;
- 
 
 931 
	use RCMailer;
- 
 
 932 
  use HTML::Tiny;
- 
 
 933 
  my $h = HTML::Tiny->new( mode => 'html' );
- 
 
 934 
  my $depts = getDepartments (); # HashRef of the department TLAs -> Display Names...
- 
 
 935 
  my $AccessLevel = getAccessLevels;
- 
 
 936 
 
- 
 
 937 
	my $email = $data->{email};
- 
 
 938 
	my $subject = 'RollerCon VORC - New User';
- 
 
 939 
	my $body;
- 
 
 940 
	if ($context eq "New User") {
- 
 
 941 
    $subject .= " Request";
- 
 
 942 
    my $activationlink = url ()."?activate=".$data->{activation};
- 
 
 943 
	  $body = $h->p ("Greetings,");
- 
 
 944 
	  $body .= $h->p ("It appears as though you've registered a new account in RollerCon's VORC system with the following information:");
- 
 
 945 
	  $body .= $h->table ([
- 
 
 946 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Derby Name:",    $data->{derby_name})]),
- 
 
 947 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Real Name:",     $data->{real_name})]),
- 
 
 948 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Pronouns:",      $data->{pronouns})]),
- 
 
 949 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "TShirt Size:",   $data->{tshirt})]),
- 
 
 950 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Email Address:", $data->{email})]),
- 
 
 951 
	    $h->tr ([$h->td ("&nbsp;&nbsp;", "Phone:",         $data->{phone})])
- 
 
 952 
	  ]);
- 
 
 953 
    $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:",
- 
 
 954 
      $h->a ({ HREF=>$activationlink }, "Activate my VORC Account!"), $h->br,
- 
 
 955 
      "Or you can copy/paste this into the 'Activation Code' box: ".$data->{activation}, $h->br,
- 
 
 956 
      "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.",
- 
 
 957 
      "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.",
- 
 
 958 
      "If you're new to using vORC, you may want to read this:",
- 
 
 959 
      $h->a ({ HREF=>"https://volunteers.rollercon.com/info.html" }, "VORC User Info"),
- 
 
 960 
      "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.",
- 
 
 961 
      $h->br,
- 
 
 962 
      "--RollerCon HQ".$h->br.'rollercon@gmail.com'.$h->br."rollercon.com");
- 
 
 963 
  } elsif ($context eq "Activate") {
- 
 
 964 
    $subject .= " Activated!";
- 
 
 965 
    my $tempDepartments = convertDepartments ($data->{department});
- 
 
 966 
    my $printableDepartments = join "\n", map { $depts->{$_}.": ".$AccessLevel->{$tempDepartments->{$_}} } sort keys %{$tempDepartments};
- 
 
 967 
    $body = "Greetings again,
- 
 
 968 
 
- 
 
 969 
You have been approved to volunteer at RollerCon in the following departments:
- 
 
 970 
 
- 
 
 971 
$printableDepartments
- 
 
 972 
 
- 
 
 973 
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.
- 
 
 974 
 
- 
 
 975 
https://volunteers.rollercon.com/schedule/
- 
 
 976 
 
- 
 
 977 
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?
- 
 
 978 
 
- 
 
 979 
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.
- 
 
 980 
 
- 
 
 981 
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.
- 
 
 982 
 
- 
 
 983 
If you're new to using vORC, you may want to read this:
- 
 
 984 
 
- 
 
 985 
https://volunteers.rollercon.com/info.html
- 
 
 986 
 
- 
 
 987 
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.
- 
 
 988 
 
- 
 
 989 
-RollerCon Management
- 
 
 990 
";
- 
 
 991 
  } else {
- 
 
 992 
    return;
- 
 
 993 
  }
- 
 
 994 
	# send the message
- 
 
 995 
	EmailUser ($email, $subject, $body);
- 
 
 996 
 
- 
 
 997 
}
- 
 
 998 
 
760 
 
 999