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