| 2 |
- |
1 |
## RollerCon support functions...
|
|
|
2 |
|
|
|
3 |
use strict;
|
| 9 |
- |
4 |
use cPanelUserConfig;
|
| 2 |
- |
5 |
use Exporter;
|
|
|
6 |
use CGI qw/:standard :netscape/;
|
|
|
7 |
use CGI::Cookie;
|
|
|
8 |
use DBI;
|
| 7 |
- |
9 |
use WebDB;
|
| 2 |
- |
10 |
|
| 7 |
- |
11 |
|
|
|
12 |
my $dbh = WebDB->connect ();
|
| 2 |
- |
13 |
our $ORCUSER;
|
| 7 |
- |
14 |
use constant {
|
|
|
15 |
USER => 1,
|
|
|
16 |
LEAD => 2,
|
|
|
17 |
MANAGER => 3,
|
|
|
18 |
DIRECTOR => 4,
|
|
|
19 |
ADMIN => 5
|
|
|
20 |
};
|
| 2 |
- |
21 |
|
| 7 |
- |
22 |
sub getAccessLevels {
|
|
|
23 |
my %AccessLevels = (
|
|
|
24 |
-1 => "Locked",
|
|
|
25 |
|
|
|
26 |
1 => "Volunteer",
|
|
|
27 |
2 => "Lead",
|
|
|
28 |
3 => "Manager",
|
|
|
29 |
4 => "Director",
|
|
|
30 |
5 => "SysAdmin"
|
|
|
31 |
);
|
|
|
32 |
return \%AccessLevels;
|
|
|
33 |
}
|
|
|
34 |
|
| 2 |
- |
35 |
sub authDB {
|
|
|
36 |
my $src = shift;
|
|
|
37 |
my $id = shift;
|
|
|
38 |
my $pass = shift;
|
|
|
39 |
my $level = shift;
|
|
|
40 |
my ($result, $encpass);
|
|
|
41 |
|
|
|
42 |
my $sth = $dbh->prepare("select * from official where email = ?");
|
|
|
43 |
$sth->execute($id);
|
|
|
44 |
my $RCDBIDHASH = $sth->fetchrow_hashref();
|
|
|
45 |
|
|
|
46 |
if ($src eq "form") {
|
|
|
47 |
my $pwdhan = $dbh->prepare("select password(?)");
|
|
|
48 |
$pwdhan->execute($pass);
|
|
|
49 |
($encpass) = $pwdhan->fetchrow();
|
|
|
50 |
} else {
|
|
|
51 |
$encpass = $pass;
|
|
|
52 |
}
|
|
|
53 |
|
| 9 |
- |
54 |
my $tempDepartments = convertDepartments ($RCDBIDHASH->{department});
|
|
|
55 |
my $MAXACCESS = scalar keys %{ $tempDepartments } ? max ($RCDBIDHASH->{'access'}, values %{ $tempDepartments }) : $RCDBIDHASH->{'access'};
|
|
|
56 |
|
| 2 |
- |
57 |
if (!$RCDBIDHASH) {
|
|
|
58 |
$result->{ERRMSG} = "User-ID/Email Address not found!";
|
|
|
59 |
$result->{cookie_string} = '';
|
|
|
60 |
$result->{RCid} = '';
|
|
|
61 |
logit(0, "Account not found: $id");
|
|
|
62 |
$result->{authenticated} = 'false';
|
|
|
63 |
} elsif ($RCDBIDHASH->{'password'} ne $encpass) {
|
|
|
64 |
$result->{ERRMSG} = "Incorrect Password!";
|
|
|
65 |
$result->{cookie_string} = '';
|
|
|
66 |
$result->{RCid} = $RCDBIDHASH->{'RCid'};
|
|
|
67 |
logit($RCDBIDHASH->{'RCid'}, "Incorrect Password");
|
|
|
68 |
$result->{authenticated} = 'false';
|
| 9 |
- |
69 |
} elsif ($MAXACCESS < $level) {
|
| 7 |
- |
70 |
if (getSetting ("MAINTENANCE")) {
|
|
|
71 |
$result->{ERRMSG} = "MAINTENANCE MODE: Logins are temporarily disabled.";
|
|
|
72 |
} else {
|
|
|
73 |
$result->{ERRMSG} = "Your account either needs to be activated, or doesn't have access to this page!";
|
|
|
74 |
logit($RCDBIDHASH->{'RCid'}, "Insufficient Privileges");
|
|
|
75 |
}
|
| 2 |
- |
76 |
$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";
|
|
|
77 |
$result->{RCid} = $RCDBIDHASH->{'RCid'};
|
|
|
78 |
$result->{authenticated} = 'false';
|
|
|
79 |
} else {
|
|
|
80 |
$result->{ERRMSG} = '';
|
| 7 |
- |
81 |
$RCDBIDHASH->{department} = convertDepartments ($RCDBIDHASH->{department});
|
|
|
82 |
$RCDBIDHASH->{'access'} = max ($RCDBIDHASH->{'access'}, values %{$RCDBIDHASH->{department}});
|
| 2 |
- |
83 |
$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";
|
|
|
84 |
$result->{RCid} = $RCDBIDHASH->{'RCid'};
|
|
|
85 |
logit($RCDBIDHASH->{'RCid'}, "Logged In") if $src eq "form";
|
| 25 |
- |
86 |
$dbh->do ("update official set last_login = CONVERT_TZ(now(), 'America/Chicago', 'America/Los_Angeles') where RCid = ?", undef, $RCDBIDHASH->{'RCid'}) if $src eq "form";
|
| 2 |
- |
87 |
$result->{authenticated} = 'true';
|
| 7 |
- |
88 |
# my @depts = map { s/-\d// } split /:/, $RCDBIDHASH->{department};
|
|
|
89 |
# my @depts = split /:/, $RCDBIDHASH->{department};
|
|
|
90 |
|
| 2 |
- |
91 |
$ORCUSER=$RCDBIDHASH;
|
|
|
92 |
}
|
|
|
93 |
return $result;
|
|
|
94 |
}
|
|
|
95 |
|
| 7 |
- |
96 |
sub max {
|
|
|
97 |
my ($max, $next, @vars) = @_;
|
|
|
98 |
return $max if not $next;
|
|
|
99 |
return max( $max > $next ? $max : $next, @vars );
|
|
|
100 |
}
|
|
|
101 |
|
| 2 |
- |
102 |
sub authenticate { # Verifies the user has logged in or puts up a log in screen
|
| 7 |
- |
103 |
my $MAINTMODE = getSetting ("MAINTENANCE");
|
|
|
104 |
my $MINLEVEL = $MAINTMODE ? $MAINTMODE : shift // 1;
|
|
|
105 |
|
| 2 |
- |
106 |
my ($ERRMSG, $authenticated, %FORM);
|
|
|
107 |
my $sth = $dbh->prepare("select * from official where email = '?'");
|
|
|
108 |
|
|
|
109 |
my $query = new CGI;
|
| 7 |
- |
110 |
# Check to see if the user has already logged in (there should be cookies with their authentication)?
|
|
|
111 |
my $RCAUTH = $query->cookie('RCAUTH');
|
| 2 |
- |
112 |
$FORM{'ID'} = $query->param('id') || '';
|
|
|
113 |
$FORM{'PASS'} = $query->param('pass') || '';
|
|
|
114 |
$FORM{'SUB'} = $query->param('login') || '';
|
|
|
115 |
|
|
|
116 |
if ($FORM{'SUB'}) {
|
|
|
117 |
#a log in form was submited
|
|
|
118 |
if ($FORM{'SUB'} eq "Submit") {
|
|
|
119 |
$authenticated = authDB('form', $FORM{'ID'}, $FORM{'PASS'}, $MINLEVEL);
|
|
|
120 |
} elsif ($FORM{'SUB'} eq "New User") {
|
|
|
121 |
# Print the new user form and exit
|
|
|
122 |
}
|
|
|
123 |
} elsif ($RCAUTH) {
|
|
|
124 |
#We have an authenication cookie. Double-check it
|
|
|
125 |
my ($RCID, $RCPASS, $RCLVL) = split /&/, $RCAUTH;
|
|
|
126 |
$authenticated = authDB('cookie', $RCID, $RCPASS, $MINLEVEL);
|
|
|
127 |
} else {
|
|
|
128 |
$authenticated->{authenticated} = 'false';
|
|
|
129 |
}
|
|
|
130 |
|
|
|
131 |
|
|
|
132 |
if ($authenticated->{authenticated} eq 'true') {
|
|
|
133 |
return $authenticated->{cookie_string};
|
|
|
134 |
}
|
|
|
135 |
|
|
|
136 |
|
|
|
137 |
|
|
|
138 |
# If we get here, the user has failed authentication; throw up the log-in screen and die.
|
|
|
139 |
|
|
|
140 |
my $RCAUTH_cookie = CGI::Cookie->new(-name=>'RCAUTH',-value=>"",-expires=>"now");
|
|
|
141 |
|
|
|
142 |
if ($authenticated->{ERRMSG}) {
|
|
|
143 |
$authenticated->{ERRMSG} = "<TR><TD colspan=2 align=center><font color=red><b>".$authenticated->{ERRMSG}."</b></font> </TD></TR>";
|
|
|
144 |
# Log the failed access attempt
|
|
|
145 |
} else {
|
|
|
146 |
$authenticated->{ERRMSG} = "";
|
|
|
147 |
# Since there was no ERRMSG, no need to log anything.
|
|
|
148 |
}
|
|
|
149 |
|
|
|
150 |
print header(-cookie=>$RCAUTH_cookie);
|
|
|
151 |
printRCHeader("Please Sign In");
|
|
|
152 |
print<<authpage;
|
|
|
153 |
<form action="$ENV{REQUEST_URI}" method=POST name=Req id=Req>
|
|
|
154 |
<TR><TD colspan=2 align=center><b><font size=+2>Please Sign In</font>
|
| 7 |
- |
155 |
<TABLE>
|
| 2 |
- |
156 |
</TD></TR>
|
|
|
157 |
<TR><TD colspan=2> </TD></TR>
|
|
|
158 |
$authenticated->{ERRMSG}
|
|
|
159 |
<TR>
|
| 7 |
- |
160 |
<TD align=right><B>User ID:</TD><TD><INPUT type=text id=login name=id></TD>
|
| 2 |
- |
161 |
</TR>
|
|
|
162 |
<TR>
|
|
|
163 |
<TD align=right><B>Password:</TD><TD><INPUT type=password name=pass></TD>
|
|
|
164 |
</TR>
|
|
|
165 |
<TR><TD></TD><TD><INPUT type=submit name=login value=Submit></TD></TR>
|
|
|
166 |
<TR><TD colspan=2 align=center> </TD></TR>
|
|
|
167 |
<TR><TD colspan=2 align=center><A HREF="/schedule/manage_user.pl?submit=New%20User">[register as a new user]</A></TD></TR>
|
|
|
168 |
<TR><TD colspan=2 align=center><A HREF="/schedule/password_reset.pl">[reset your password]</A></TD></TR>
|
|
|
169 |
</TABLE>
|
|
|
170 |
</FORM>
|
|
|
171 |
|
|
|
172 |
<SCRIPT language="JavaScript">
|
|
|
173 |
<!--
|
| 7 |
- |
174 |
document.getElementById("login").focus();
|
| 2 |
- |
175 |
|
|
|
176 |
function Login () {
|
|
|
177 |
document.getElementById('Req').action = "$ENV{SCRIPT_NAME}";
|
|
|
178 |
document.getElementById('Req').submit.click();
|
|
|
179 |
return true;
|
|
|
180 |
}
|
|
|
181 |
|
|
|
182 |
|
|
|
183 |
//-->
|
|
|
184 |
</SCRIPT>
|
|
|
185 |
|
|
|
186 |
authpage
|
|
|
187 |
|
|
|
188 |
#foreach (keys %ENV) {
|
|
|
189 |
# print "$_: $ENV{$_}<br>";
|
|
|
190 |
#}
|
|
|
191 |
# &JScript;
|
|
|
192 |
exit;
|
|
|
193 |
}
|
|
|
194 |
|
| 7 |
- |
195 |
sub getShiftDepartment {
|
|
|
196 |
my $shiftID = shift // "";
|
|
|
197 |
my $dept;
|
|
|
198 |
|
|
|
199 |
if ($shiftID =~ /^\d+$/) {
|
|
|
200 |
($dept) = $dbh->selectrow_array ("select dept from shift where id = ?", undef, $shiftID);
|
|
|
201 |
} elsif ($shiftID =~ /^\d+-ANN/) {
|
|
|
202 |
$dept = "ANN";
|
|
|
203 |
} else {
|
|
|
204 |
$dept = "OFF";
|
|
|
205 |
}
|
|
|
206 |
|
|
|
207 |
return $dept;
|
|
|
208 |
}
|
|
|
209 |
|
|
|
210 |
sub getDepartments {
|
|
|
211 |
my $RCid = shift // "";
|
|
|
212 |
# If we get an RCid, return the list of departments and levels for that user.
|
|
|
213 |
# Otherwise (no parameter), return the list of departments with their display names.
|
|
|
214 |
|
|
|
215 |
if ($RCid) {
|
|
|
216 |
my $sth = $dbh->prepare("select department from official where RCid = ?");
|
|
|
217 |
$sth->execute($RCid);
|
|
|
218 |
my ($dlist) = $sth->fetchrow;
|
|
|
219 |
return convertDepartments ($dlist);
|
|
|
220 |
} else {
|
|
|
221 |
my %HASH;
|
|
|
222 |
my $sth = $dbh->prepare("select TLA, name from department");
|
|
|
223 |
$sth->execute();
|
|
|
224 |
while (my ($tla, $name) = $sth->fetchrow) {
|
|
|
225 |
$HASH{$tla} = $name;
|
|
|
226 |
}
|
|
|
227 |
return \%HASH;
|
|
|
228 |
}
|
|
|
229 |
|
|
|
230 |
}
|
|
|
231 |
|
|
|
232 |
sub convertDepartments {
|
|
|
233 |
# For the department membership, converts the DB string back and forth to a hashref...
|
|
|
234 |
my $input = shift // "";
|
|
|
235 |
my $output;
|
|
|
236 |
|
|
|
237 |
if (ref $input eq "HASH") {
|
|
|
238 |
$output = join ":", map { $_."-".$input->{$_} } sort keys %{$input};
|
|
|
239 |
} else {
|
|
|
240 |
foreach (split /:/, $input) {
|
|
|
241 |
my ($tla, $level) = split /-/;
|
|
|
242 |
$output->{$tla} = $level;
|
|
|
243 |
}
|
|
|
244 |
}
|
|
|
245 |
|
|
|
246 |
return $output;
|
|
|
247 |
}
|
|
|
248 |
|
|
|
249 |
sub getSchedule {
|
|
|
250 |
my $RCid = shift // return "ERROR: No RCid provided to getSchedule";
|
|
|
251 |
my $filter = shift // "";
|
|
|
252 |
|
|
|
253 |
my @whereclause;
|
|
|
254 |
push @whereclause, "date >= date(now())" unless $filter eq "all";
|
|
|
255 |
# if ($RCid ne $ORCUSER->{RCid}) {
|
|
|
256 |
# push @whereclause, "dept != 'PER'";
|
|
|
257 |
# }
|
|
|
258 |
|
|
|
259 |
use DateTime;
|
| 25 |
- |
260 |
my $dt = DateTime->today (time_zone => 'America/Los_Angeles');
|
| 7 |
- |
261 |
$dt =~ s/T00\:00\:00$//;
|
| 25 |
- |
262 |
my $now = DateTime->now (time_zone => 'America/Los_Angeles');
|
| 7 |
- |
263 |
|
|
|
264 |
|
|
|
265 |
use HTML::Tiny;
|
|
|
266 |
my $h = HTML::Tiny->new( mode => 'html' );
|
|
|
267 |
|
|
|
268 |
my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";
|
|
|
269 |
my @shifts;
|
| 21 |
- |
270 |
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
|
|
|
271 |
select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union
|
|
|
272 |
select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ?) temp
|
| 7 |
- |
273 |
$where order by date, time");
|
|
|
274 |
$sth->execute($RCid, $RCid, $RCid);
|
| 13 |
- |
275 |
my $hours = 0;
|
| 7 |
- |
276 |
while (my $s = $sth->fetchrow_hashref) {
|
|
|
277 |
my ($yyyy, $mm, $dd) = split /\-/, $s->{date};
|
|
|
278 |
my $cutoff = DateTime->new(
|
|
|
279 |
year => $yyyy,
|
|
|
280 |
month => $mm,
|
|
|
281 |
day => $dd,
|
|
|
282 |
hour => 5,
|
|
|
283 |
minute => 0,
|
|
|
284 |
second => 0,
|
|
|
285 |
time_zone => 'America/Los_Angeles'
|
|
|
286 |
);
|
|
|
287 |
|
|
|
288 |
|
|
|
289 |
if (!$s->{teams}) {
|
|
|
290 |
# it's a time-based shift
|
|
|
291 |
if ($s->{dept} eq "PER") {
|
|
|
292 |
if ($RCid eq $ORCUSER->{RCid}) {
|
|
|
293 |
# DROP
|
|
|
294 |
$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")." ".$h->button ({ onClick=>"location.href='manage_personal_time.pl?choice=Update&id=$s->{id}'" }, "EDIT");
|
|
|
295 |
} else {
|
|
|
296 |
$s->{location} = "";
|
|
|
297 |
$s->{role} = "";
|
|
|
298 |
}
|
| 21 |
- |
299 |
} elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
|
| 7 |
- |
300 |
# DROP
|
|
|
301 |
$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");
|
|
|
302 |
if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
|
|
|
303 |
# NO SHOW
|
|
|
304 |
$s->{buttons} .= " ".$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");
|
|
|
305 |
}
|
|
|
306 |
$hours += $s->{volhours};
|
|
|
307 |
}
|
|
|
308 |
|
| 21 |
- |
309 |
} elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
|
| 7 |
- |
310 |
# it's a game shift
|
|
|
311 |
#DROP
|
|
|
312 |
$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");
|
|
|
313 |
if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
|
|
|
314 |
# NO SHOW
|
|
|
315 |
$s->{buttons} .= " ".$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");
|
|
|
316 |
}
|
|
|
317 |
$hours += $s->{volhours};
|
|
|
318 |
}
|
|
|
319 |
$s->{role} =~ s/\-\d$//;
|
|
|
320 |
|
| 9 |
- |
321 |
# push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "nowrap highlighted" : "nowrap shaded" }, join ' ', $s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams}, $s->{buttons});
|
|
|
322 |
# push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "highlighted" : "shaded" }, join ' ', $s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams}, $s->{buttons});
|
|
|
323 |
push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "highlighted" : "shaded" }, $h->div ({ class=>"lisp0" }, [ $h->div ({ class=>"liLeft" }, join ' ', ($s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams})), $h->div ({ class=>"liRight" }, $s->{buttons}) ]));
|
| 7 |
- |
324 |
}
|
|
|
325 |
|
|
|
326 |
if (scalar @shifts) {
|
|
|
327 |
return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);
|
|
|
328 |
} else {
|
|
|
329 |
return $h->p ({ class=>"hint" }, "[nothing scheduled at the moment]");
|
|
|
330 |
}
|
|
|
331 |
}
|
|
|
332 |
|
| 2 |
- |
333 |
sub getSetting {
|
|
|
334 |
my $k = shift;
|
| 19 |
- |
335 |
my ($value) = $dbh->selectrow_array ("select setting.value from setting where setting.key = ?", undef, $k);
|
|
|
336 |
return $value;
|
| 2 |
- |
337 |
}
|
|
|
338 |
|
|
|
339 |
sub getUser {
|
| 7 |
- |
340 |
my $ID = shift;
|
|
|
341 |
|
|
|
342 |
my $sth;
|
|
|
343 |
if ($ID =~ /^\d+$/) {
|
|
|
344 |
$sth = $dbh->prepare("select * from official where RCid = ?");
|
|
|
345 |
} else {
|
|
|
346 |
$sth = $dbh->prepare("select * from official where email = ?");
|
|
|
347 |
}
|
|
|
348 |
$sth->execute($ID);
|
|
|
349 |
return $sth->fetchrow_hashref;
|
| 2 |
- |
350 |
}
|
|
|
351 |
|
|
|
352 |
sub getUserEmail {
|
|
|
353 |
my $RCid = shift;
|
|
|
354 |
my $sth = $dbh->prepare("select email from official where RCid = ?");
|
|
|
355 |
$sth->execute($RCid);
|
|
|
356 |
my ($email) = $sth->fetchrow_array();
|
|
|
357 |
return $email;
|
|
|
358 |
}
|
|
|
359 |
|
|
|
360 |
sub getUserDerbyName {
|
|
|
361 |
my $RCid = shift;
|
|
|
362 |
my $sth = $dbh->prepare("select derby_name from official where RCid = ?");
|
|
|
363 |
$sth->execute($RCid);
|
|
|
364 |
my ($dname) = $sth->fetchrow_array();
|
|
|
365 |
return $dname;
|
|
|
366 |
}
|
|
|
367 |
|
|
|
368 |
sub getYears {
|
| 7 |
- |
369 |
# my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view union select year(now())");
|
|
|
370 |
my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view");
|
| 2 |
- |
371 |
$sth->execute();
|
|
|
372 |
my @years;
|
|
|
373 |
while (my ($y) =$sth->fetchrow_array()) { push @years, $y; }
|
|
|
374 |
return \@years;
|
|
|
375 |
}
|
|
|
376 |
|
|
|
377 |
sub printRCHeader {
|
|
|
378 |
my $PAGE_TITLE = shift;
|
| 7 |
- |
379 |
use CGI qw/start_html/;
|
|
|
380 |
use HTML::Tiny;
|
|
|
381 |
my $h = HTML::Tiny->new( mode => 'html' );
|
| 2 |
- |
382 |
|
| 7 |
- |
383 |
# 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 |
- |
384 |
my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};
|
|
|
385 |
my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")." ";
|
|
|
386 |
$logout .= url =~ /\/(index.pl)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/schedule/';" }, "Home")." ";
|
|
|
387 |
$logout .= $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");
|
|
|
388 |
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 : "";
|
| 7 |
- |
389 |
|
|
|
390 |
print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );
|
|
|
391 |
|
|
|
392 |
#<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title>
|
|
|
393 |
#<link rel="stylesheet" type="text/css" href="/style.css">
|
|
|
394 |
#</head>
|
|
|
395 |
#<body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000">
|
|
|
396 |
print $h->div ({ class=>"sp0" }, [ $h->div ({ class=>"spLeft" }, $h->a ({ href=>"/schedule/" }, $h->img ({ src=>"/logo.jpg", width=>"75", height=>"75" }))),
|
|
|
397 |
$h->div ({ class=>"spRight" }, [ $h->h1 (["vORC $PAGE_TITLE", $h->br]),
|
|
|
398 |
$loggedinas,
|
|
|
399 |
])
|
|
|
400 |
]);
|
|
|
401 |
#print<<rcheader;
|
|
|
402 |
# <TABLE>
|
|
|
403 |
# <TR class="nostripe">
|
|
|
404 |
# <TD align=right><img SRC="/logo.jpg"></TD>
|
|
|
405 |
# <TD align=center valign=middle><b><font size=+3>Officials' RollerCon<br>Schedule Manager<br>$PAGE_TITLE</FONT></b>
|
|
|
406 |
# <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>
|
|
|
407 |
# </TR>
|
| 2 |
- |
408 |
|
| 7 |
- |
409 |
#rcheader
|
| 2 |
- |
410 |
}
|
|
|
411 |
|
|
|
412 |
sub changeShift {
|
| 7 |
- |
413 |
my ($change, $shift_id, $role, $user_id) = @_;
|
|
|
414 |
my $leadership_change = 0;
|
|
|
415 |
my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
|
|
|
416 |
my $game_based = $role ? "game" : "shift";
|
|
|
417 |
my $sth;
|
| 2 |
- |
418 |
|
| 7 |
- |
419 |
if ($change eq "add") {
|
|
|
420 |
my $taken;
|
|
|
421 |
if ($game_based eq "game") {
|
|
|
422 |
($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);
|
|
|
423 |
} else {
|
|
|
424 |
($taken) = $dbh->selectrow_array ("select count(*) from shift where id = ? and isnull(assignee_id) = 0", undef, $shift_id);
|
|
|
425 |
}
|
|
|
426 |
if ($taken) {
|
|
|
427 |
return "<br>Denied! This shift is already taken ($shift_id).<br>\n";
|
|
|
428 |
}
|
|
|
429 |
}
|
| 2 |
- |
430 |
|
| 7 |
- |
431 |
if (lc ($user_id) ne lc ($ORCUSER->{RCid})) { # they're changing someone else's schedule...
|
|
|
432 |
if ($ORCUSER->{department}->{$department} >= 2 or $ORCUSER->{access} >= 5) {
|
|
|
433 |
# the user making the change is either a lead in the dept or a sysadmin
|
|
|
434 |
logit ($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $shift_id, $role, $user_id)");
|
|
|
435 |
logit ($user_id, "Schedule was changed by $ORCUSER->{derby_name}. ($change, $shift_id, $role, $user_id)");
|
|
|
436 |
$leadership_change = 1;
|
| 2 |
- |
437 |
} else {
|
| 7 |
- |
438 |
logit ($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $shift_id, $role, $user_id)");
|
|
|
439 |
return "<br>Denied! You are not authorized to change someone else's schedule in this department ($department).<br>\n";
|
| 2 |
- |
440 |
}
|
| 7 |
- |
441 |
} elsif ($ORCUSER->{department}->{$department} >= 3) {
|
|
|
442 |
# Managers can sign up for as many shifts within their own department as they like...
|
|
|
443 |
$leadership_change = 1;
|
| 2 |
- |
444 |
}
|
|
|
445 |
|
| 7 |
- |
446 |
if ($change eq "add" and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {
|
|
|
447 |
return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n";
|
|
|
448 |
}
|
|
|
449 |
|
|
|
450 |
if ($change eq "add" and findConflict ($user_id, $shift_id, $game_based)) {
|
|
|
451 |
return "<br>Denied! There is a conflict with that shift's time!<br>\n";
|
|
|
452 |
}
|
|
|
453 |
|
|
|
454 |
my ($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);
|
|
|
455 |
if ($game_type =~ /^selected/ and !$leadership_change) {
|
|
|
456 |
return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n";
|
|
|
457 |
}
|
|
|
458 |
|
|
|
459 |
if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {
|
|
|
460 |
return "<br>Denied! Shift reserved for leadership staff!<br>\n";
|
|
|
461 |
}
|
|
|
462 |
|
|
|
463 |
my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");
|
|
|
464 |
my $daily_count = signUpCount ('get', $user_id);
|
|
|
465 |
if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
|
|
|
466 |
return "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";
|
|
|
467 |
}
|
|
|
468 |
|
|
|
469 |
my @DBARGS;
|
|
|
470 |
if ($game_based eq "game") {
|
|
|
471 |
if ($change eq "add") {
|
|
|
472 |
$sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
|
|
|
473 |
} elsif ($change eq "del") {
|
|
|
474 |
$sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
|
|
|
475 |
}
|
|
|
476 |
@DBARGS = ($shift_id, $role, $user_id);
|
|
|
477 |
} else {
|
|
|
478 |
if ($change eq "add") {
|
|
|
479 |
$sth = $dbh->prepare("update shift set assignee_id = ? where id = ?");
|
|
|
480 |
@DBARGS = ($user_id, $shift_id);
|
|
|
481 |
} elsif ($change eq "del") {
|
|
|
482 |
$sth = $dbh->prepare("update shift set assignee_id = null where id = ?");
|
|
|
483 |
@DBARGS = ($shift_id);
|
|
|
484 |
}
|
|
|
485 |
}
|
|
|
486 |
|
|
|
487 |
print "<br>attempting to make DB changes...<br>";
|
|
|
488 |
if ($sth->execute (@DBARGS)) {
|
|
|
489 |
$daily_count = signUpCount ($change, $user_id) unless $leadership_change;
|
|
|
490 |
logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");
|
|
|
491 |
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";
|
|
|
492 |
return;
|
|
|
493 |
} else {
|
|
|
494 |
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();
|
|
|
495 |
}
|
|
|
496 |
}
|
|
|
497 |
|
|
|
498 |
sub modShiftTime {
|
|
|
499 |
my ($shift_id, $user_id, $diff) = @_;
|
|
|
500 |
my $ORCUSER = getUser (1);
|
| 2 |
- |
501 |
|
| 7 |
- |
502 |
use Scalar::Util qw(looks_like_number);
|
|
|
503 |
if (!looks_like_number ($diff)) {
|
|
|
504 |
print "<br>ERROR! The time adjustment ($diff) doesn't look like a number.<br>\n";
|
|
|
505 |
return;
|
| 2 |
- |
506 |
}
|
|
|
507 |
|
| 7 |
- |
508 |
my ($validate_assignee) = $dbh->selectrow_array ("select count(*) from v_shift where id = ? and RCid = ?", undef, $shift_id, $user_id);
|
|
|
509 |
if (!$validate_assignee) {
|
|
|
510 |
print "<br>ERROR! This shift is assigned to someone else.<br>\n";
|
|
|
511 |
return;
|
|
|
512 |
}
|
|
|
513 |
|
|
|
514 |
my $department = getShiftDepartment ($shift_id);
|
|
|
515 |
if (convertDepartments ($ORCUSER->{department})->{$department} < 2 and $ORCUSER->{access} < 5) {
|
|
|
516 |
print "<br>ERROR! You're not authorized to modify this shift's time.<br>\n";
|
|
|
517 |
logit ($ORCUSER->{RCid}, "Unauthorized attempt to modify shift time. ($department, $shift_id)");
|
|
|
518 |
return;
|
|
|
519 |
}
|
|
|
520 |
|
|
|
521 |
my $rows_changed;
|
|
|
522 |
print "<br>attempting to make DB changes...<br>";
|
|
|
523 |
if ($diff == 0) {
|
|
|
524 |
$rows_changed = $dbh->do ("update shift set mod_time = null where id = ? and assignee_id = ?", undef, $shift_id, $user_id);
|
|
|
525 |
} else {
|
|
|
526 |
$rows_changed = $dbh->do ("update shift set mod_time = ? where id = ? and assignee_id = ?", undef, $diff, $shift_id, $user_id);
|
|
|
527 |
}
|
|
|
528 |
|
|
|
529 |
|
|
|
530 |
if (!$rows_changed or $dbh->errstr) {
|
|
|
531 |
print "ERROR: Nothing got updated".$dbh->errstr;
|
|
|
532 |
logit (0, "ERROR modifying a shift time ($diff, $shift_id, $user_id):".$dbh->errstr);
|
|
|
533 |
} else {
|
|
|
534 |
print "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)";
|
|
|
535 |
logit ($ORCUSER->{RCid}, "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)");
|
|
|
536 |
|
|
|
537 |
}
|
|
|
538 |
return;
|
| 2 |
- |
539 |
}
|
|
|
540 |
|
|
|
541 |
sub signUpCount {
|
|
|
542 |
my $action = shift;
|
|
|
543 |
my $id = shift;
|
| 19 |
- |
544 |
my $dept = shift // "";
|
| 2 |
- |
545 |
|
| 7 |
- |
546 |
if ($id eq $ORCUSER->{RCid}) {
|
| 2 |
- |
547 |
if ($action eq 'add') {
|
| 19 |
- |
548 |
if (signUpCount ('get', $id, $dept)) {
|
|
|
549 |
$dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
|
| 2 |
- |
550 |
} else {
|
| 19 |
- |
551 |
$dbh->do("replace into sign_up_count (date, RCid, department, sign_ups) values (curdate(), ?, ?, 1)", undef, $id, $dept);
|
| 2 |
- |
552 |
}
|
|
|
553 |
} elsif ($action eq 'del') {
|
| 19 |
- |
554 |
if (signUpCount ('get', $id, $dept)) {
|
|
|
555 |
$dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);
|
| 2 |
- |
556 |
}
|
|
|
557 |
}
|
|
|
558 |
}
|
|
|
559 |
|
| 19 |
- |
560 |
my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and department = ? and date = curdate()", undef, $id, $dept);
|
| 2 |
- |
561 |
|
|
|
562 |
return $R ? $R : '0';
|
|
|
563 |
}
|
|
|
564 |
|
|
|
565 |
sub signUpEligible {
|
|
|
566 |
my $user = shift;
|
|
|
567 |
my $t = shift;
|
| 7 |
- |
568 |
my $shifttype = shift // "game";
|
| 19 |
- |
569 |
my $dept = $t->{dept} // "";
|
| 2 |
- |
570 |
|
| 19 |
- |
571 |
my $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$dept);
|
|
|
572 |
$limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $limit;
|
|
|
573 |
|
|
|
574 |
my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";
|
|
|
575 |
|
| 7 |
- |
576 |
if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }
|
|
|
577 |
|
| 19 |
- |
578 |
if (!exists $user->{$limitkey}) {
|
|
|
579 |
$user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);
|
| 2 |
- |
580 |
}
|
|
|
581 |
|
| 7 |
- |
582 |
if ($shifttype eq "game") {
|
| 21 |
- |
583 |
# if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{$limitkey} < $limit) {
|
|
|
584 |
if ($t->{signup} ne "selected" and $user->{$limitkey} < $limit) {
|
| 2 |
- |
585 |
return 1;
|
|
|
586 |
} else {
|
|
|
587 |
return 0;
|
|
|
588 |
}
|
| 7 |
- |
589 |
} else {
|
|
|
590 |
if ($user->{department}->{$t->{dept}} < 1) { return 0; }
|
|
|
591 |
if ($t->{type} eq "lead" and $user->{department}->{$t->{dept}} < 2) { return 0; }
|
|
|
592 |
if ($t->{type} eq "manager" and $user->{department}->{$t->{dept}} < 3) { return 0; }
|
| 19 |
- |
593 |
if ($t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {
|
| 2 |
- |
594 |
return 1;
|
|
|
595 |
} else {
|
|
|
596 |
return 0;
|
|
|
597 |
}
|
|
|
598 |
}
|
|
|
599 |
}
|
|
|
600 |
|
|
|
601 |
sub findConflict {
|
|
|
602 |
my $rcid = shift;
|
|
|
603 |
my $gid = shift;
|
| 7 |
- |
604 |
my $type = shift // "";
|
|
|
605 |
my ($date, $start, $end, $conflicts);
|
| 2 |
- |
606 |
|
| 7 |
- |
607 |
if ($type eq "game") {
|
|
|
608 |
# Are they already signed up for this game? (It's faster to check the two views one at a time...)
|
|
|
609 |
# ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where substring_index(id, '-', 1) = ? and RCid = ?", undef, $gid, $rcid);
|
|
|
610 |
($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where id = ? and RCid = ?", undef, $gid, $rcid);
|
|
|
611 |
if ($conflicts) { return 1; } # no need to keep looking...
|
|
|
612 |
($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);
|
|
|
613 |
if ($conflicts) { return 1; } # no need to keep looking...
|
|
|
614 |
|
|
|
615 |
($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);
|
|
|
616 |
} elsif ($type eq "personal") {
|
|
|
617 |
($date, $start, $end) = @{ $gid };
|
|
|
618 |
} else {
|
|
|
619 |
($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);
|
|
|
620 |
}
|
| 2 |
- |
621 |
|
|
|
622 |
# Are they signed up for any games that would conflict with this one?
|
| 7 |
- |
623 |
# 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 = ?");
|
|
|
624 |
# 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 |
- |
625 |
|
| 7 |
- |
626 |
($conflicts) = $dbh->selectrow_array ("select count(*) from (
|
|
|
627 |
select id from v_shift where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union
|
|
|
628 |
select id from v_shift_announcer where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union
|
|
|
629 |
select id from v_shift_officiating where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? ) alltables",
|
|
|
630 |
undef, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid
|
|
|
631 |
);
|
|
|
632 |
|
| 2 |
- |
633 |
return $conflicts;
|
|
|
634 |
}
|
|
|
635 |
|
|
|
636 |
sub changeLeadShift {
|
|
|
637 |
my ($change, $lshift, $user_id) = @_;
|
|
|
638 |
my $ERRMSG;
|
|
|
639 |
|
|
|
640 |
my $sth = $dbh->prepare("update lead_shift set assignee_id = ? where id = ?");
|
|
|
641 |
|
|
|
642 |
print "<br>attempting to make DB changes...<br>";
|
|
|
643 |
if ($change eq "add") {
|
|
|
644 |
$sth->execute($user_id, $lshift)
|
|
|
645 |
or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
|
|
|
646 |
} elsif ($change eq "del") {
|
|
|
647 |
$sth->execute('', $lshift)
|
|
|
648 |
or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
|
|
|
649 |
}
|
|
|
650 |
if ($ERRMSG) {
|
|
|
651 |
print $ERRMSG;
|
|
|
652 |
} else {
|
|
|
653 |
logit($user_id, "Lead Shift ".ucfirst($change).": $lshift");
|
|
|
654 |
print "Success.<br>";
|
|
|
655 |
}
|
|
|
656 |
}
|
|
|
657 |
|
|
|
658 |
sub logit {
|
|
|
659 |
my $RCid = shift;
|
|
|
660 |
my $msg = shift;
|
|
|
661 |
my $sth = $dbh->prepare("insert into log (RCid, event) values (?, ?)");
|
|
|
662 |
$sth->execute($RCid, $msg);
|
|
|
663 |
}
|
|
|
664 |
|
|
|
665 |
1;
|