Subversion Repositories VORC

use WebDB;

use WebDB;

$SIG{__WARN__} = sub { warn sprintf("[%s] ", scalar localtime), @_ };

$SIG{__WARN__} = sub { warn sprintf("[%s] ", scalar localtime), @_ };

our @EXPORT = qw( $ORCUSER $SYSTEM_EMAIL getRCDBH getAccessLevels authDB max authenticate canView getShiftDepartment getClassID getDepartments convertDepartments convertTime getSchedule getRCid getSetting getUser getUserEmail getUserDerbyName getYears printRCHeader changeShift modShiftTime signUpCount signUpEligible findConflict changeLeadShift sendNewUserEMail logit validate_emt);

our @EXPORT = qw( $ORCUSER $SYSTEM_EMAIL getRCDBH getAccessLevels authDB max authenticate canView getShiftRef getShiftDepartment getClassID getDepartments convertDepartments convertTime getSchedule getRCid getSetting getUser getUserEmail getUserDerbyName getYears printRCHeader changeShift modShiftTime signUpCount signUpEligible findConflict changeLeadShift sendNewUserEMail logit validate_emt);

  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";

  my $where = scalar @whereclause ? "where ".join " and ", @whereclause : "";

  my @shifts;

  my @shifts;

  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

  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

                                          select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union

                                          select id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? union

                                          select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ? union

                                          select id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ? union

                           $where order by date, time");

                           $where order by date, time");

  $sth->execute($RCid, $RCid, $RCid, $RCid);

  $sth->execute($RCid, $RCid, $RCid, $RCid);

  my $hours = 0;

  my $hours = 0;

  while (my $s = $sth->fetchrow_hashref) {

  while (my $s = $sth->fetchrow_hashref) {

    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};

    my ($yyyy, $mm, $dd) = split /\-/, $s->{date};

  if ($shift_id =~ /^\d+$/) {

  if ($shift_id =~ /^\d+$/) {

    $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);

    $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);

  } else {

  } else {

    $department = "CLA";

    $department = "CLA";

    if ($change eq "del") {

    if ($change eq "del") {

    } else {

    } else {

      if ($change eq "override") {

      if ($change eq "override") {

      } else {

      } else {

      }

      }

    }

    }

    $role = "CLA-1" unless $role; # If no one has signed up for the class yet, the SQL above doesn't retrieve the first available 

    $role = "CLA-1" unless $role; # If no one has signed up for the class yet, the SQL above doesn't retrieve the first available 

  }

  }

# my $game_based = $role ? "game" : "shift";

# my $game_based = $role ? "game" : "shift";

  my $daily_count;

  my $daily_count;

  if ($department eq "CLA") {

  if ($department eq "CLA") {

    # MVP Class Sign-up

    # MVP Class Sign-up

    ($daily_count) = $dbh->selectrow_array ("select count(*) from v_class_signup where RCid = ? and year(date) = year(now())", undef, $user_id);

    ($daily_count) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where RCid = ? and year(date) = year(now())", undef, $user_id);

#   ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);

#   ($daily_count) = $dbh->selectrow_array ("select count(*) from v_shift where RCid = ? and dept = 'CLA'", undef, $user_id);

    if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {    

    if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {    

      return "<br>Denied! You may only sign up for $MAXSHIFTS Classes!<br>\n";

      return "<br>Denied! You may only sign up for $MAXSHIFTS Classes!<br>\n";

    }

    }

  return 0 unless $limit > 0;

  return 0 unless $limit > 0;

  my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";

  my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";

  if ($shifttype eq "class") {

  if ($shifttype eq "class") {

    ($t->{id}) = $dbh->selectrow_array ("select id from v_class where date = ? and location = ? and start_time = ?", undef, $t->{date}, $t->{location}, $t->{start_time});

    ($t->{id}) = $dbh->selectrow_array ("select id from v_class_new where date = ? and location = ? and start_time = ?", undef, $t->{date}, $t->{location}, $t->{start_time});

    $t->{dept} = "CLA";

    $t->{dept} = "CLA";

    $dept = "CLA";

    $dept = "CLA";

  } else {

  } else {

    if ($dept eq "CLA") {

    if ($dept eq "CLA") {

      # MVP Class Sign-up

      # MVP Class Sign-up

      return 0 unless $user->{MVPid};

      return 0 unless $user->{MVPid};

      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");

      my $class_limit = getSetting ("MAX_CLASS_SIGNUP");

      return 0 unless $class_count < $class_limit;

      return 0 unless $class_count < $class_limit;

    } else {

    } else {

      if ($user->{department}->{$dept} < 1) { return 0; }

      if ($user->{department}->{$dept} < 1) { return 0; }

    }

    }

    if (lc $t->{type} eq "lead" and $user->{department}->{$dept} < 2) { return 0; }

    if (lc $t->{type} eq "lead" and $user->{department}->{$dept} < 2) { return 0; }

    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);

    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_shift_announcer where id = ? and RCid = ?", undef, $gid, $rcid);

    if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...

    if ($conflicts) { return "ANN-".$gid; } # no need to keep looking...

    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);    

    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, time, end_time from game where id = ?", undef, $gid);    

    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup where id = ? and RCid = ?", undef, $gid, $rcid);

    ($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where id = ? and RCid = ?", undef, $gid, $rcid);

    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_class where id = ?", undef, $gid);

    ($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from v_class_new where id = ?", undef, $gid);

  } elsif ($type eq "personal")  {

  } elsif ($type eq "personal")  {

#  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 = ?");

#  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 = ?");

#  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 = ?");

#  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 = ?");

  ($conflicts) = $dbh->selectrow_array ("select * from (

  ($conflicts) = $dbh->selectrow_array ("select * from (

    select concat('CLA:', id) as conflict from v_class_signup      where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union

    select concat('CLA:', id) as conflict from v_class_signup_new  where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union

    select concat('ANN-', id) as conflict from v_shift_announcer   where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union

    select concat('ANN-', id) as conflict from v_shift_announcer   where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? union

    select concat('OFF-', id) as conflict from v_shift_officiating where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? ) alltables

    select concat('OFF-', id) as conflict from v_shift_officiating where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? ) alltables

    where conflict <> ?",

    where conflict <> ?",

    undef, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, "PER-".$existing

    undef, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, $date, $start, $start, $start, $end, $rcid, "PER-".$existing