WebSVN – ORC – Diff – /trunk/perl/RollerCon.pm

 use strict;
 use Exporter;
 use CGI qw/:standard :netscape/;
 use CGI::Cookie;
 use DBI;
+use WebDB;
-my $dsn = "DBI:mysql:database=rollerco_data;host=localhost;port=3306";
-my $dbh = DBI->connect($dsn, 'rollerco_www', 'www-data');
+my $dbh = WebDB->connect ();
+our $ORCUSER;
+use constant {
+    USER   => 1,
+    LEAD   => 2,
+    MANAGER  => 3,
+    DIRECTOR  => 4,
+    ADMIN  => 5
+  };
+sub getAccessLevels {
+  my %AccessLevels = (
+    -1 => "Locked",
+=> "Pending",
+=> "Volunteer",
+=> "Lead",
+=> "Manager",
+=> "Director",
+=> "SysAdmin"
+  );
+  return \%AccessLevels;
 our $ORCUSER;
 sub authDB {
 	my $src = shift;
 		$result->{ERRMSG} = "Incorrect Password!";
 		$result->{cookie_string} = '';
 		$result->{RCid} = $RCDBIDHASH->{'RCid'};
 		logit($RCDBIDHASH->{'RCid'}, "Incorrect Password");
 		$result->{authenticated} = 'false';
-	} elsif ($RCDBIDHASH->{'access'} < $level) {
+	} elsif (max ($RCDBIDHASH->{'access'}, values %{convertDepartments ($RCDBIDHASH->{department})}) < $level) {
+	  if (getSetting ("MAINTENANCE")) {
+	    $result->{ERRMSG} = "MAINTENANCE MODE: Logins are temporarily disabled.";
+	  } else {
-		$result->{ERRMSG} = "Your account either needs to be activated, or doesn't have access to this page!";
+		  $result->{ERRMSG} = "Your account either needs to be activated, or doesn't have access to this page!";
+  		logit($RCDBIDHASH->{'RCid'}, "Insufficient Privileges");
+		}
 		$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";
 		$result->{RCid} = $RCDBIDHASH->{'RCid'};
-		logit($RCDBIDHASH->{'RCid'}, "Insufficient Privileges");
 		$result->{authenticated} = 'false';
 	} else {
 		$result->{ERRMSG} = '';
+		$RCDBIDHASH->{department} = convertDepartments ($RCDBIDHASH->{department});
+		$RCDBIDHASH->{'access'} = max ($RCDBIDHASH->{'access'}, values %{$RCDBIDHASH->{department}});
 		$result->{cookie_string} = "${id}&${encpass}&$RCDBIDHASH->{'access'}";
 		$result->{RCid} = $RCDBIDHASH->{'RCid'};
 		logit($RCDBIDHASH->{'RCid'}, "Logged In") if $src eq "form";
+		$dbh->do ("update official set last_login = now() where RCid = ?", undef, $RCDBIDHASH->{'RCid'}) if $src eq "form";
 		$result->{authenticated} = 'true';
+#		my @depts = map { s/-\d// } split /:/, $RCDBIDHASH->{department};
+#		my @depts = split /:/, $RCDBIDHASH->{department};
 		$ORCUSER=$RCDBIDHASH;
+	}
 	return $result;
+}
+sub max {
+    my ($max, $next, @vars) = @_;
+    return $max if not $next;
+    return max( $max > $next ? $max : $next, @vars );
+}
-sub authenticate {									# Verifies the user has logged in or puts up a log in screen
-# Check to see if the user has already logged in (there should be cookies with their authentication)?
+sub authenticate {									# Verifies the user has logged in or puts up a log in screen
-	my $MINLEVEL = shift || 1;
+	my $MAINTMODE = getSetting ("MAINTENANCE");
+	my $MINLEVEL = $MAINTMODE ? $MAINTMODE : shift // 1;
 #	my $MINLEVEL = 1;
 	my ($ERRMSG, $authenticated, %FORM);
 	my $sth = $dbh->prepare("select * from official where email = '?'");
+	my $query = new CGI;
 # Check to see if the user has already logged in (there should be cookies with their authentication)?
-	my $query = new CGI;
+	my $RCAUTH = $query->cookie('RCAUTH');
 	$FORM{'ID'} = $query->param('id') || '';
-	$FORM{'PASS'} = $query->param('pass') || '';
-	$FORM{'SUB'} = $query->param('login') || '';
+	$FORM{'PASS'} = $query->param('pass') || '';
-	my $RCAUTH = $query->cookie('RCAUTH');
+	$FORM{'SUB'} = $query->param('login') || '';
 	if ($FORM{'SUB'}) {
 	print header(-cookie=>$RCAUTH_cookie);
 	printRCHeader("Please Sign In");
 	print<<authpage;
 	<form action="$ENV{REQUEST_URI}" method=POST name=Req id=Req>
 		<TR><TD colspan=2 align=center><b><font size=+2>Please Sign In</font>
+		<TABLE>
 		</TD></TR>
 		<TR><TD colspan=2>&nbsp</TD></TR>
 		$authenticated->{ERRMSG}
 		<TR>
-			<TD align=right><B>User ID:</TD><TD><INPUT type=text name=id></TD>
+			<TD align=right><B>User ID:</TD><TD><INPUT type=text id=login name=id></TD>
 		</TR>
 		<TR>
 			<TD align=right><B>Password:</TD><TD><INPUT type=password name=pass></TD>
 		</TR>
 		<TR><TD></TD><TD><INPUT type=submit name=login value=Submit></TD></TR>
 	</TABLE>
 	</FORM>
 	<SCRIPT language="JavaScript">
+	<!--
-	<!--
+	document.getElementById("login").focus();
 	function Login () {
 		document.getElementById('Req').action = "$ENV{SCRIPT_NAME}";
 #}
 #	&JScript;
 	exit;
+}
+sub getShiftDepartment {
+  my $shiftID = shift // "";
+  my $dept;
+  if ($shiftID =~ /^\d+$/) {
+    ($dept) = $dbh->selectrow_array ("select dept from shift where id = ?", undef, $shiftID);
+  } elsif ($shiftID =~ /^\d+-ANN/) {
+    $dept = "ANN";
+  } else {
+    $dept = "OFF";
+  }
+  return $dept;
+}
+sub getDepartments {
+  my $RCid = shift // "";
+  # If we get an RCid, return the list of departments and levels for that user.
+  #   Otherwise (no parameter), return the list of departments with their display names.
+	if ($RCid) {
+  	my $sth = $dbh->prepare("select department from official where RCid = ?");
+  	$sth->execute($RCid);
+  	my ($dlist) = $sth->fetchrow;
+  	return convertDepartments ($dlist);
+	} else {
+  	my %HASH;
+  	my $sth = $dbh->prepare("select TLA, name from department");
+  	$sth->execute();
+  	while (my ($tla, $name) = $sth->fetchrow) {
+  	  $HASH{$tla} = $name;
+    }
+    return \%HASH;
+  }
+}
+sub convertDepartments {
+  # For the department membership, converts the DB string back and forth to a hashref...
+  my $input = shift // "";
+  my $output;
+  if (ref $input eq "HASH") {
+    $output = join ":", map { $_."-".$input->{$_} } sort keys %{$input};
+  } else {
+  	foreach (split /:/, $input) {
+  	  my ($tla, $level) = split /-/;
+  	  $output->{$tla} = $level;
+    }
+  }
+  return $output;
+}
+sub getSchedule {
+  my $RCid = shift // return "ERROR: No RCid provided to getSchedule";
+  my $filter = shift // "";
+  my @whereclause;
+  push @whereclause, "date >= date(now())" unless $filter eq "all";
+#  if ($RCid ne $ORCUSER->{RCid}) {
+#    push @whereclause, "dept != 'PER'";
+#  }
+  use DateTime;
+  my $dt = DateTime->today;
+  $dt =~ s/T00\:00\:00$//;
+  my $now = DateTime->now;
+  use HTML::Tiny;
+  my $h = HTML::Tiny->new( mode => 'html' );
+  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";
+  my @shifts;
+  my $sth = $dbh->prepare("select * from (select id, date, dayofweek, track as location, time, role, teams, gtype, 'OFF' as dept, volhours from v_shift_officiating where RCid = ? union
+                                          select id, date, dayofweek, track as location, time, role, teams, gtype, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union
+                                          select id, date, dayofweek, location, time, role, '' as teams, type as gtype, dept, volhours from v_shift where RCid = ?) temp
+                           $where order by date, time");
+  $sth->execute($RCid, $RCid, $RCid);
+  my $hours;
+  while (my $s = $sth->fetchrow_hashref) {
+    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};
+	  my $cutoff = DateTime->new(
+        year => $yyyy,
+        month => $mm,
+        day => $dd,
+        hour => 5,
+        minute => 0,
+        second => 0,
+        time_zone => 'America/Los_Angeles'
+    );
+  	if (!$s->{teams}) {
+  	  # it's a time-based shift
+  	  if ($s->{dept} eq "PER") {
+        if ($RCid eq $ORCUSER->{RCid}) {
+          # DROP
+  	      $s->{buttons} = $h->button ({ onClick=>"if (confirm('Really? You want to delete this personal time?')==true) { window.open('manage_personal_time.pl?choice=Delete&id=$s->{id}','Confirm Change','resizable,height=260,width=370'); return false; }" }, "DEL")."&nbsp;".$h->button ({ onClick=>"location.href='manage_personal_time.pl?choice=Update&id=$s->{id}'" }, "EDIT");
+  	    } else {
+  	      $s->{location} = "";
+  	      $s->{role} = "";
+  	    }
+      } elsif (($RCid == $ORCUSER->{RCid} and $s->{gtype} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
+        # DROP
+  		  $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");
+	   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
+   		    # NO SHOW
+ 	  	    $s->{buttons} .= "&nbsp;".$h->button ({ onClick=>"if (confirm('Really? They were a no show?')==true) { window.open('make_shift_change.pl?noshow=true&change=del&RCid=$RCid&id=$s->{id}','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "NO SHOW");
+ 		    }
+        $hours += $s->{volhours};
+  		}
+    } elsif (($RCid == $ORCUSER->{RCid} and $s->{gtype} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {
+      # it's a game shift
+      #DROP
+  		$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");
+   		if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {
+ 		    # NO SHOW
+        $s->{buttons} .= "&nbsp;".$h->button ({ onClick=>"if (confirm('Really? They were a no show?')==true) { window.open('make_shift_change.pl?noshow=true&change=del&RCid=$RCid&id=$s->{id}&role=$s->{role}','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "NO SHOW");
+      }
+      $hours += $s->{volhours};
+  	}
+  	$s->{role} =~ s/\-\d$//;
+  	push @shifts, $h->li ({ class=> $s->{date} eq $dt ? "nowrap highlighted" : "nowrap shaded" }, join '&nbsp;&nbsp;', $s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams}, $s->{buttons});
+  }
+  if (scalar @shifts) {
+    return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);
+  } else {
+    return $h->p ({ class=>"hint" }, "[nothing scheduled at the moment]");
+  }
+}
 sub getSetting {
 	my $k = shift;
 	my $sth = $dbh->prepare("select setting.value from setting where setting.key = ?");
 	$sth->execute($k);
 	return $sth->fetchrow_hashref()->{value};
+}
+sub getUser {
+	my $ID = shift;
+	my $sth;
+	if ($ID =~ /^\d+$/) {
-sub getUser {
+	  $sth = $dbh->prepare("select * from official where RCid = ?");
+	} else {
-	my $EML = shift;
+	  $sth = $dbh->prepare("select * from official where email = ?");
-	my $sth = $dbh->prepare("select * from official where email = ?");
+  }
-	$sth->execute($EML);
+	$sth->execute($ID);
-	return $sth->fetchrow_hashref();
+	return $sth->fetchrow_hashref;
+}
 	my ($dname) = $sth->fetchrow_array();
 	return $dname;
+}
 sub getYears {
+#	my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view union select year(now())");
-	my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view union select year(now())");
+	my $sth = $dbh->prepare("select distinct year(date) from v_shift_admin_view");
 	$sth->execute();
 	my @years;
 	while (my ($y) =$sth->fetchrow_array()) { push @years, $y; }
 	return \@years;
+}
+sub printRCHeader {
+	my $PAGE_TITLE = shift;
+	use CGI qw/start_html/;
+	use HTML::Tiny;
+  my $h = HTML::Tiny->new( mode => 'html' );
-sub printRCHeader {
+#  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]");
-	my $PAGE_TITLE = shift;
+my $logout = $h->button ({ onClick=>"document.cookie = 'RCAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");
+	my $loggedinas = $ORCUSER ? "Currently logged in as: ".$h->a ({ href=>"/schedule/manage_user.pl?submit=View&RCid=$ORCUSER->{RCid}" }, $ORCUSER->{derby_name})." $logout" : "";
-	my $loggedinas = $ORCUSER ? "Currently logged in as: $ORCUSER->{derby_name}" : "";
+  print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );
-	print<<rcheader;
-<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title>
+#<html><head><title>Officials' RollerCon Schedule Manager - $PAGE_TITLE</title>
+#<link rel="stylesheet" type="text/css" href="/style.css">
+#</head>
+#<body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000">
+	print $h->div ({ class=>"sp0" }, [ $h->div ({ class=>"spLeft" },  $h->a ({ href=>"/schedule/" }, $h->img ({ src=>"/logo.jpg", width=>"75", height=>"75" }))),
+	                                   $h->div ({ class=>"spRight" }, [ $h->h1 (["vORC $PAGE_TITLE", $h->br]),
+	                                   $loggedinas,
-<link rel="stylesheet" type="text/css" href="/rollercon.css">
+	                                   ])
-</head>
+	                                 ]);
-<body text="#000000" bgcolor="#FFFFFF" link="#0000EE" vlink="#551A8B" alink="#FF0000">
+#print<<rcheader;
-<TABLE>
+#  <TABLE>
-	<TR class="nostripe">
+#	<TR class="nostripe">
-		<TD align=right><img SRC="/logo.jpg"></TD>
+#		<TD align=right><img SRC="/logo.jpg"></TD>
-		<TD align=center valign=middle><b><font size=+3>Officials' RollerCon<br>Schedule Manager<br>$PAGE_TITLE</FONT></b>
+#		<TD align=center valign=middle><b><font size=+3>Officials' RollerCon<br>Schedule Manager<br>$PAGE_TITLE</FONT></b>
-		<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>
+#	<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>
-	</TR>
+#	</TR>
-rcheader
+#rcheader
+}
+sub changeShift {
+	my ($change, $shift_id, $role, $user_id) = @_;
+	my $leadership_change = 0;
+	my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);
+	my $game_based = $role ? "game" : "shift";
+	my $sth;
+	if ($change eq "add") {
+  	my $taken;
+  	if ($game_based eq "game") {
+  	  ($taken) = $dbh->selectrow_array ("select count(*) from assignment where Gid = ? and role = ?", undef, $shift_id, $role);
+  	} else {
+  	  ($taken) = $dbh->selectrow_array ("select count(*) from shift where id = ? and isnull(assignee_id) = 0", undef, $shift_id);
-}
-sub changeShift {
-	my ($change, $game_id, $role, $user_id) = @_;
-	my $countbypass = 0;
+  	}
+  	if ($taken) {
-	my $sth = $dbh->prepare("select type from game where id = ?");
+  	    return "<br>Denied! This shift is already taken ($shift_id).<br>\n";
-	$sth->execute($game_id);
+  	}
+  }
-	my ($game_type) = $sth->fetchrow_array;
 	if (lc ($user_id) ne lc ($ORCUSER->{RCid})) { # they're changing someone else's schedule...
-	if (lc($user_id) ne lc($ORCUSER->{RCid})) {
+	  if ($ORCUSER->{department}->{$department} >= 2 or $ORCUSER->{access} >= 5) {
-	  if ($ORCUSER->{access} < 2) {
+	    # the user making the change is either a lead in the dept or a sysadmin
-	    print "<br>Denied! You are not authorized to change someone else's schedule.<br>\n";
+	    logit ($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $shift_id, $role, $user_id)");
+	    logit ($user_id, "Schedule was changed by $ORCUSER->{derby_name}. ($change, $shift_id, $role, $user_id)");
+	    $leadership_change = 1;
+	  } else {
-	    logit($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $game_id, $role, $user_id)");
+	    logit ($ORCUSER->{RCid}, "Unauthorized attempt to change someone else's schedule. ($change, $shift_id, $role, $user_id)");
-	    return;
+	    return "<br>Denied! You are not authorized to change someone else's schedule in this department ($department).<br>\n";
-	  } else {
+	  }
+	} elsif ($ORCUSER->{department}->{$department} >= 3) {
-	    logit($ORCUSER->{RCid}, "$ORCUSER->{derby_name} changed someone else's schedule. ($change, $game_id, $role, $user_id)");
+	  # Managers can sign up for as many shifts within their own department as they like...
-	    $countbypass = 1;
-	  }
+	  $leadership_change = 1;
 	}
-	my $MAXSHIFTS = $game_type eq "clinic" ? getSetting("MAX_CLINIC_SIGNUPS") : $game_type eq "observation" ? getSetting("MAX_OBS_SIGNUPS") : getSetting("MAX_SHIFT_SIGNUP_PER_DAY");
-#	my $MAXSHIFTS = getSetting("MAX_SHIFT_SIGNUP_PER_DAY");
+  if ($change eq "add" and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {
+		return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n";
+  }
-	my $daily_count = signUpCount('get', $user_id, $game_type);
+  if ($change eq "add" and findConflict ($user_id, $shift_id, $game_based)) {
+		return "<br>Denied! There is a conflict with that shift's time!<br>\n";
-	if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$countbypass) {
+  }
-		print "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";
-		return;
+ 	my ($game_type) = $dbh->selectrow_array ("select type from ".$game_based." where id = ?", undef, $shift_id);
+ 	if ($game_type =~ /^selected/ and !$leadership_change) {
-	}
+ 	  return "<br>Denied! Only leadership can make changes to 'selected staffing' shifts!<br>\n";
+ 	}
-	if ($change eq "add" and ($daily_count < $MAXSHIFTS or $countbypass)) {
-		$sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
+ 	if ($change eq "add" and $game_type eq "lead" and convertDepartments(getUser($user_id)->{department})->{$department} < 2 and $ORCUSER->{access} < 3) {
+ 	  return "<br>Denied! Shift reserved for leadership staff!<br>\n";
+ 	}
-	} elsif ($change eq "del") {
+ 	my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");
-		$sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
-	}
+ 	my $daily_count = signUpCount ('get', $user_id);
-	print "<br>attempting to make DB changes...<br>";
+ 	if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {
-	if ($sth->execute($game_id, $role, $user_id)) {
+ 		return "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";
-		$daily_count = signUpCount($change, $user_id, $game_type) unless $countbypass;
+ 	}
 		logit($user_id, "Shift ".ucfirst($change).": $game_id -> $role");
+ 	my @DBARGS;
-		if ($game_type eq "clinic") {
+  if ($game_based eq "game") {
+  	if ($change eq "add") {
+  		$sth = $dbh->prepare("insert into assignment (Gid, role, RCid) values (?, ?, ?)");
+  	} elsif ($change eq "del") {
+  		$sth = $dbh->prepare("delete from assignment where Gid = ? and role = ? and RCid= ?");
+  	}
+  	@DBARGS = ($shift_id, $role, $user_id);
+  } else {
+  	if ($change eq "add") {
+  		$sth = $dbh->prepare("update shift set assignee_id = ? where id = ?");
+  		@DBARGS = ($user_id, $shift_id);
+  	} elsif ($change eq "del") {
+  		$sth = $dbh->prepare("update shift set assignee_id = null where id = ?");
+  		@DBARGS = ($shift_id);
+  	}
+  }
-	 		print "Success!...<br>You've signed up for $daily_count clinic shifts (you're currently allowed to sign up for $MAXSHIFTS total).<br>\n";
+  print "<br>attempting to make DB changes...<br>";
+  if ($sth->execute (@DBARGS)) {
+  	$daily_count = signUpCount ($change, $user_id) unless $leadership_change;
+  	logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");
+  	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";
+  	return;
+  } else {
+    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();
+  }
+}
+sub modShiftTime {
-		} elsif ($game_type eq "observation") {
+	my ($shift_id, $user_id, $diff) = @_;
+	my $ORCUSER = getUser (1);
+	use Scalar::Util qw(looks_like_number);
+	if (!looks_like_number ($diff)) {
+	  print "<br>ERROR! The time adjustment ($diff) doesn't look like a number.<br>\n";
+  	return;
+	}
+  my ($validate_assignee) = $dbh->selectrow_array ("select count(*) from v_shift where id = ? and RCid = ?", undef, $shift_id, $user_id);
+ 	if (!$validate_assignee) {
+	  print "<br>ERROR! This shift is assigned to someone else.<br>\n";
+  	return;
+ 	}
+	my $department = getShiftDepartment ($shift_id);
+  if (convertDepartments ($ORCUSER->{department})->{$department} < 2 and $ORCUSER->{access} < 5) {
+	  print "<br>ERROR! You're not authorized to modify this shift's time.<br>\n";
+	  logit ($ORCUSER->{RCid}, "Unauthorized attempt to modify shift time. ($department, $shift_id)");
+  	return;
+ 	}
+  my $rows_changed;
+  print "<br>attempting to make DB changes...<br>";
+  if ($diff == 0) {
+	  $rows_changed = $dbh->do ("update shift set mod_time = null where id = ? and assignee_id = ?", undef, $shift_id, $user_id);
+  } else {
+	  $rows_changed = $dbh->do ("update shift set mod_time = ? where id = ? and assignee_id = ?", undef, $diff, $shift_id, $user_id);
+  }
+  if (!$rows_changed or $dbh->errstr) {
+  	print "ERROR: Nothing got updated".$dbh->errstr;
-	 		print "Success!...<br>You've signed up for $daily_count clinic observation game (you're currently allowed to sign up for $MAXSHIFTS total).<br>\n";
+  	logit (0, "ERROR modifying a shift time ($diff, $shift_id, $user_id):".$dbh->errstr);
-		} else {
+  } else {
-  		print "Success!...<br>You've signed up for $daily_count challenge / scrimmage shifts today (you're currently allowed to sign up for $MAXSHIFTS per day).<br>\n";
+  	print "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)";
   	logit ($ORCUSER->{RCid}, "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)");
 	} else {
-    print "<br><b>You did not get the shift</b>, most likely because someone else took it while you were looking.<br>\nERROR: ", $sth->errstr();
+  }
   return;
 }
 sub signUpCount {
 	my $action = shift;
 	my $id = shift;
 	my $gtype = shift // "";
-	if ($gtype ne "clinic" and $gtype ne "observation" and $id eq $ORCUSER->{RCid}) {
+	if ($id eq $ORCUSER->{RCid}) {
 		if ($action eq 'add') {
-			if (signUpCount('get', $id)) {
+			if (signUpCount ('get', $id)) {
-				$dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = $id");
+				$dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = ?", undef, $id);
-			} else {
-				$dbh->do("replace into sign_up_count values (curdate(), $id, 1)");
-			}
-		} elsif ($action eq 'del') {
-			if (signUpCount('get', $id)) {
-				$dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = $id");
-			}
+			} else {
-		}
-	}
-	my $get;
+				$dbh->do("replace into sign_up_count values (curdate(), ?, 1)", undef, $id);
 	if ($gtype eq "clinic") {
-		$get = $dbh->prepare("select count(*) from v_shift where RCid = ? and gtype = 'clinic' and date >= '2019'");
+		} elsif ($action eq 'del') {
-	} elsif ($gtype eq "observation") {
+			if (signUpCount ('get', $id)) {
-		$get = $dbh->prepare("select count(*) from v_shift where RCid = ? and gtype = 'observation' and date >= '2019'");
+				$dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = ?", undef, $id);
+			}
-	} else {
+		}
-		$get = $dbh->prepare("select sign_ups from sign_up_count where RCid = ? and date = curdate()");
+	}
-	$get->execute($id);
+	my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and date = curdate()", undef, $id);
 	my ($R) = $get->fetchrow_array();
-	return $R ? $R : '0';
-}
-sub signUpEligible {
-	my $user = shift;
-	my $t = shift;
+	return $R ? $R : '0';
-  if (findConflict($user->{RCid}, $t->{id})) { return 0; }
-	if (!exists $user->{sign_ups_today}) {
+}
 		$user->{sign_ups_today} = signUpCount('get', $user->{RCid});
 sub signUpEligible {
-	if (!exists $user->{clinic_sign_ups}) {
+	my $user = shift;
-		$user->{clinic_sign_ups} = signUpCount('get', $user->{RCid}, "clinic");
+	my $t = shift;
+	my $shifttype = shift // "game";
-	}
-	if (!exists $user->{obs_sign_ups}) {
+	if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }
-		$user->{obs_sign_ups} = signUpCount('get', $user->{RCid}, "observation");
-	}
+	if (!exists $user->{sign_ups_today}) {
-	if ($t->{gtype} eq "clinic") {
+		$user->{sign_ups_today} = signUpCount('get', $user->{RCid});
 	  # Uncomment to open clinic games to everyone...
      return 1;
-    #---------------------------------
+	if ($shifttype eq "game") {
-		if ($user->{clinic_pass} and $user->{clinic_sign_ups} < getSetting("MAX_CLINIC_SIGNUPS")) {
-			return 1;
-		} else {
-			return 0;
     if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{sign_ups_today} < getSetting("MAX_SHIFT_SIGNUP_PER_DAY")) {
-	} elsif ($t->{gtype} eq "observation") {
-	  # Uncomment to open observation games to everyone...
+			return 1;
-    # return 1;
+		} else {
-    #---------------------------------
+			return 0;
 		if ($user->{clinic_pass} and $user->{obs_sign_ups} < getSetting("MAX_OBS_SIGNUPS")) {
-			return 1;
+	} else {
+	  if ($user->{department}->{$t->{dept}} < 1) { return 0; }
+	  if ($t->{type} eq "lead" and $user->{department}->{$t->{dept}} < 2) { return 0; }
-		} else {
+	  if ($t->{type} eq "manager" and $user->{department}->{$t->{dept}} < 3) { return 0; }
+    if ($t->{type} !~ /^selected/ and $user->{sign_ups_today} < getSetting("MAX_SHIFT_SIGNUP_PER_DAY")) {
-			return 0;
+			return 1;
-		}
+		} else {
+			return 0;
-	} elsif ($t->{gtype} ne "selected staffing" and $t->{gtype} ne "short track" and $user->{sign_ups_today} < getSetting("MAX_SHIFT_SIGNUP_PER_DAY")) {
+		}
+	}
+}
-		return 1;
+sub findConflict {
+  my $rcid = shift;
+  my $gid = shift;
+  my $type = shift // "";
-	} else {
+  my ($date, $start, $end, $conflicts);
-		return 0;
-	}
+  if ($type eq "game") {
-}
+  # Are they already signed up for this game? (It's faster to check the two views one at a time...)
+#    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where substring_index(id, '-', 1) = ? and RCid = ?", undef, $gid, $rcid);
+    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_officiating where id = ? and RCid = ?", undef, $gid, $rcid);
+  	if ($conflicts) { return 1; } # no need to keep looking...
+    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);
+  	if ($conflicts) { return 1; } # no need to keep looking...
-sub findConflict {
+    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);
-  my $rcid = shift;
+  } elsif ($type eq "personal")  {
-  my $gid = shift;
+    ($date, $start, $end) = @{ $gid };
-  my $conflicts;
+  } else {

Subversion Repositories ORC

(root)/trunk/perl/RollerCon.pm – Rev 2 → 7