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