Rev 12 | Blame | Compare with Previous | Last modification | View Log | RSS feed
package PEEPS;## PEEPS support functions...use strict;use Exporter 'import';use CGI qw/param header start_html url/;use CGI::Cookie;use DBI;use WebDB;$SIG{__WARN__} = sub { warn sprintf("[%s] ", scalar localtime), @_ };$SIG{__DIE__} = sub { die sprintf("[%s] ", scalar localtime), @_ };our @EXPORT = qw( $ORCUSER $SYSTEM_EMAIL getRCDBH getAccessLevels authDB max authenticate canView getLeagueAffiliation getLeagueName getLeagues getShiftRef getShiftDepartment getClassID getDepartments convertDepartments convertTime getSchedule getRCid getSetting getUser getUserEmail getUserDerbyName getYears printRCHeader changeShift modShiftTime signUpCount signUpEligible findConflict changeLeadShift sendNewUserEMail sendUserMFAEMail logit orglogit isLeagueAdmin isPersonCovered isLeagueCovered isWFTDAMember remainingPolicyDays remainingOrgPolicyDays getPolicyByID getCoverageByID);my $dbh = WebDB::connect ("peeps");sub getRCDBH {return $dbh;}our $ORCUSER;our $SYSTEM_EMAIL = 'rollercon.vorc@gmail.com';use constant {NOONE => 0,USER => 1,VOLUNTEER => 1,LEAD => 2,MANAGER => 3,DIRECTOR => 4,SYSADMIN => 5,ADMIN => 5};sub getAccessLevels {my %AccessLevels = (-1 => "Locked",0 => "Pending",# 1 => "Volunteer",1 => "User",2 => "Lead",3 => "Manager",4 => "Director",5 => "SysAdmin");return \%AccessLevels;}sub authDB {my $src = shift;my $id = shift;my $pass = shift;my $level = shift;my $activationcode = shift // "";my $authentication = shift // "";my ($result, $authMatch, $sessionid);use CGI::Session;my $IDHASH;if ($src eq "form") {# check the username and password against the DB and return sessionid (if one exists) if valid.($authMatch, $sessionid) = $dbh->selectrow_array ("select 1, sessionid from authentication where username = ? and password = password(?)", undef, $id, $pass);if ($authMatch) {my $session = CGI::Session->new ("driver:mysql", $sessionid ? $sessionid : undef, { DataSource => WebDB::SessionDSN });$session->param ('is_logged_in', 1);$session->expire ('is_logged_in', '+30m');$session->flush;$sessionid = $session->id;$dbh->do ("update authentication set sessionid = ? where username = ?", undef, $sessionid, $id);%{$IDHASH} = (%{$dbh->selectrow_hashref ("select * from authentication where username = ?", undef, $id)},%{$dbh->selectrow_hashref ("select * from person where id = (select person_id from authentication where username = ?)", undef, $id)});} else {$result->{ERRMSG} = "Incorrect Password!";}} else {# check the sessionid against the DB to make sure it's the same user.($authMatch, $sessionid) = $dbh->selectrow_array ("select 1, sessionid from authentication where username = ? and sessionid = ?", undef, $id, $pass);if ($authMatch) {# the sessionid matches their DB entry, but we need to see if it's expired.my $session = CGI::Session->load ("driver:mysql", $sessionid, { DataSource => WebDB::SessionDSN });$sessionid = $session->id;if ($session->is_empty) {$result->{ERRMSG} = "Session Not Found!";$authMatch = 0;} elsif ($session->is_expired) {$result->{ERRMSG} = "Session Expired!";$authMatch = 0;} elsif (!$session->param ('is_logged_in')) {$result->{ERRMSG} = "Session Timed Out (>30 minutes idle)!";$authMatch = 0;} else {$session->expire ('is_logged_in', '+30m');$session->flush;}%{$IDHASH} = (%{$dbh->selectrow_hashref ("select * from authentication where username = ?", undef, $id)},%{$dbh->selectrow_hashref ("select * from person where id = (select person_id from authentication where username = ?)", undef, $id)});} else {$result->{ERRMSG} = "SECURITY ALERT: Bogus Session!";}}if ($authMatch) {# good login, but have we seen this browser before?my $query = new CGI;my $PEEPSMFACOOKIE = $query->cookie('PEEPS_MFA_UUID');my ($MFACHECK) = $dbh->selectrow_array ("select 1 from MFA where person_id = (select person_id from authentication where username = ?) and MFA_UUID = ?", undef, $id, $PEEPSMFACOOKIE);if (!$MFACHECK) {$result->{ERRMSG} = "MFA Check Required.";}}# my $tempDepartments = convertDepartments ($IDHASH->{department});# my $MAXACCESS = scalar keys %{ $tempDepartments } ? max ($IDHASH->{'access'}, values %{ $tempDepartments } ) : $IDHASH->{'access'};my $MAXACCESS = 1;my ($failed_attempts) = $dbh->selectrow_array ("select count(*) from log where person_id = ? and event = ? and timestampdiff(MINUTE, timestamp, now()) < ? and timestamp > (select timestamp from log where person_id = ? and event = ? order by timestamp desc limit 1)", undef, $IDHASH->{person_id}, "Incorrect Password", 30, $IDHASH->{person_id}, "Logged In");if (!$IDHASH->{'person_id'}) {$result->{ERRMSG} = "Username not found!";$result->{cookie_string} = '';$result->{person_id} = '';logit(0, "Account not found: $id");$result->{authenticated} = 'false';return $result;} elsif ($failed_attempts >= 3) {$result->{ERRMSG} = "Too Many Failed Login Attempts!<br>(Please wait 30 minutes before trying again.)";$result->{cookie_string} = '';$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "User Login Timeout");$result->{authenticated} = 'false';return $result;} elsif (!$authMatch) {$result->{cookie_string} = '';$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, $result->{ERRMSG});($failed_attempts) = $dbh->selectrow_array ("select count(*) from log where person_id = ? and event = ? and timestampdiff(MINUTE, timestamp, now()) < ? and timestamp > (select timestamp from log where person_id = ? and event = ? order by timestamp desc limit 1)", undef, $IDHASH->{person_id}, "Incorrect Password", 30, $IDHASH->{person_id}, "Logged In");if ($failed_attempts >=3) {$result->{ERRMSG} .= "<br>Too Many Failed Login Attempts!<br>(Please wait 30 minutes before trying again.)";logit($IDHASH->{'person_id'}, "Excessive Login Failures, 30 Minute Timeout");}$result->{authenticated} = 'false';return $result;} elsif ($IDHASH->{'locked'}) {$result->{ERRMSG} = "Account Locked!";$result->{cookie_string} = '';$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "Login attempted for Locked account.");$result->{authenticated} = 'false';return $result;} elsif ($IDHASH->{'activation'} ne "active") {# It's an inactive account...if ($activationcode eq "resend") {# warn "Resending activation code...";sendNewUserEMail ("New User", $IDHASH);$result->{ERRMSG} = "Activation code resent. Please check your email.";$result->{cookie_string} = "${id}&${sessionid}&0";$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "Activation code resent.");$result->{authenticated} = 'inactive';return $result;} elsif ($activationcode) {# They submitted an activation codeif ($activationcode eq $IDHASH->{'activation'}) {# ...and it was good.$dbh->do ("update authentication set activation = 'active', locked = 0, last_login = now() where person_id = ? and activation = ?", undef, $IDHASH->{'person_id'}, $activationcode);logit($IDHASH->{'person_id'}, "Activated their account and logged In");# sendNewUserEMail ("Activate", $IDHASH);$IDHASH->{'access'} = 1;$IDHASH->{'activation'} = "active";$MAXACCESS = max ($MAXACCESS, 1);} else {# ...but it wasn't good.$result->{ERRMSG} = "Activation failed, invalid code submitted.";$result->{cookie_string} = "${id}&${sessionid}&0";;$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "Activation failed, invalid code submitted.");$result->{authenticated} = 'inactive';return $result;}} else {# No activation code was submitted.$result->{ERRMSG} = "Inactive account! Please check your email for activation link/code." unless $result->{ERRMSG};$result->{cookie_string} = "${id}&${sessionid}&0";$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "Login attempted without activation code.");$result->{authenticated} = 'inactive';return $result;}} elsif ($result->{ERRMSG} eq "MFA Check Required.") {# Need to check MFA...if ($authentication eq "resend") {sendUserMFAEMail ($IDHASH);$result->{ERRMSG} .= "<br>Activation code resent. Please check your email.";$result->{cookie_string} = "${id}&${sessionid}&0";$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "MFA code resent.");$result->{authenticated} = 'needsMFA';return $result;} elsif ($authentication) {# They submitted an authentication codeif ($authentication =~ /^\d{6}$/ and $authentication eq $IDHASH->{'mfa'}) {# check to see how old it is...my ($code_age) = $dbh->selectrow_array ("select timestampdiff(MINUTE, mfa_timestamp, now()) from authentication where person_id = ? and mfa = ?", undef, $IDHASH->{'person_id'}, $IDHASH->{'mfa'}) // 99;if ($code_age > 10) {# ...but it was too old.$result->{ERRMSG} = "MFA Authentication failed, code is too old. Resending...";sendUserMFAEMail ($IDHASH);$result->{cookie_string} = "${id}&${sessionid}&0";$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "MFA Authentication failed, code is too old.");$result->{authenticated} = 'needsMFA';return $result;} else {# ...and it was good.use UUID::Tiny qw(create_UUID_as_string UUID_V4);$result->{MFA_UUID} = create_UUID_as_string(UUID_V4);$dbh->do ("insert into MFA (person_id, MFA_UUID) values (?, ?)", undef, $IDHASH->{'person_id'}, $result->{MFA_UUID});logit($IDHASH->{'person_id'}, "Authenticated with an MFA code.");$IDHASH->{'access'} = 1;$MAXACCESS = max ($MAXACCESS, 1);$result->{cookie_string} = "${id}&${sessionid}&".$MAXACCESS;$result->{authenticated} = 'confirmedMFA';return $result;}} else {# ...but it wasn't good.$result->{ERRMSG} = "MFA Authentication failed, invalid code submitted.";$result->{cookie_string} = "${id}&${sessionid}&0";$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "MFA Authentication failed, invalid code submitted.");$result->{authenticated} = 'needsMFA';return $result;}} else {# No activation code was submitted.sendUserMFAEMail ($IDHASH);$result->{ERRMSG} .= " Please check your email for activation code." unless $result->{ERRMSG};$result->{cookie_string} = "${id}&${sessionid}&0";$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "Login attempted from unrecognized location, MFA needed.");$result->{authenticated} = 'needsMFA';return $result;}}if ($MAXACCESS < $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!";logit($IDHASH->{'person_id'}, "Insufficient Privileges");}$result->{cookie_string} = "${id}&${sessionid}&$IDHASH->{'access'}";$result->{person_id} = $IDHASH->{'person_id'};$result->{authenticated} = 'false';} else {$result->{ERRMSG} = '';# $IDHASH->{department} = convertDepartments ($IDHASH->{department});# $IDHASH->{'access'} = max ($IDHASH->{'access'}, values %{$IDHASH->{department}});($IDHASH->{'SYSADMIN'}) = $dbh->selectrow_array ("select 1 from role where person_id = ? and member_org_id = (select id from organization where league_name = ?) and role = ?", undef, $IDHASH->{person_id}, "WFTDA Leadership", "System Admin");$IDHASH->{'access'} = $IDHASH->{'SYSADMIN'} ? 5 : 1;$result->{cookie_string} = "${id}&${sessionid}&$IDHASH->{'access'}";$result->{person_id} = $IDHASH->{'person_id'};logit($IDHASH->{'person_id'}, "Logged In") if $src eq "form";$dbh->do ("update authentication set last_login = now() where person_id = ?", undef, $IDHASH->{'person_id'}) if $src eq "form";$result->{authenticated} = 'true';$ORCUSER = $IDHASH;}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 screenmy $MAINTMODE = getSetting ("MAINTENANCE");my $MINLEVEL = $MAINTMODE ? $MAINTMODE : shift // 1;my ($ERRMSG, $authenticated, %FORM);my $sth = $dbh->prepare("select * from authentication where username = '?'");my $query = new CGI;# Check to see if the user has already logged in (there should be cookies with their authentication)?my $PEEPSAUTH = $query->cookie('PEEPSAUTH');$FORM{'ID'} = WebDB::trim $query->param('userid') || '';$FORM{'PASS'} = WebDB::trim $query->param('pass') || '';$FORM{'SUB'} = $query->param('login') || '';$FORM{'activate'} = WebDB::trim $query->param('activate') // '';$FORM{'authenticate'} = WebDB::trim $query->param('authenticate') // '';$FORM{'saveMFA'} = WebDB::trim $query->param('saveMFA') // '';if ($PEEPSAUTH) {# We have an authenication cookie. Double-check itmy ($PEEPSID, $SESSID, $LVL) = split /&/, $PEEPSAUTH;$authenticated = authDB('cookie', $PEEPSID, $SESSID, $MINLEVEL, $FORM{'activate'}, $FORM{'authenticate'});} elsif ($FORM{'SUB'}) {# a log in form was submitedif ($FORM{'SUB'} eq "Submit") {$authenticated = authDB('form', $FORM{'ID'}, $FORM{'PASS'}, $MINLEVEL, $FORM{'activate'}, $FORM{'authenticate'});} elsif ($FORM{'SUB'} eq "New User") {# Print the new user form and exit}} else {$authenticated->{authenticated} = 'false';}if ($authenticated->{authenticated} eq 'true') {use CGI::Session;my $session = CGI::Session->new ("driver:mysql", $ORCUSER->{sessionid}, { DataSource => WebDB::SessionDSN });# $session->expire ('~logged_in', '30m');# $session->flush;my $sessionid = $session->id ();# Limit how long users are allowed to stay logged in at once.# [there's no reason to limit PEEPS session length]# my ($session_length) = $dbh->selectrow_array ("select timestampdiff(MINUTE, last_login, now()) from authentication where person_id = ?", undef, $ORCUSER->{person_id});# if ($session_length > getSetting ("MAX_SESSION_MINUTES")) {# $ENV{'QUERY_STRING'} = "LOGOUT";# $authenticated->{ERRMSG} = "Maximum session time exceeded.<br>";# }if ($ENV{'QUERY_STRING'} eq "LOGOUT") {# warn "logging $ORCUSER->{derby_name} out...";$authenticated->{ERRMSG} .= "Logged Out.<br>";$authenticated->{cookie_string} = "";#$session->clear ("is_logged_in");#$session->flush;$authenticated->{authenticated} = 'false';$ENV{REQUEST_URI} =~ s/LOGOUT//;logit ($ORCUSER->{person_id}, "Logged Out");$dbh->do ("update authentication set last_active = now(), sessionid = null where person_id = ?", undef, $ORCUSER->{person_id});$dbh->do ("delete from sessions where sessions.id = ?", undef, $sessionid);$ORCUSER = "";} else {$dbh->do ("update authentication set last_active = now(), sessionid = ? where person_id = ?", undef, $sessionid, $ORCUSER->{person_id});return $authenticated->{cookie_string};}} elsif ($authenticated->{authenticated} eq "confirmedMFA") {# Set the MFA cookie and redirect the user to where they were going.my $PEEPSAUTH_cookie = CGI::Cookie->new(-name=>'PEEPSAUTH',-value=>$authenticated->{cookie_string});my $PEEPSMFA_cookie = $FORM{saveMFA} ?CGI::Cookie->new(-name=>'PEEPS_MFA_UUID',-value=>$authenticated->{MFA_UUID},-expires=>'+5y') :CGI::Cookie->new(-name=>'PEEPS_MFA_UUID',-value=>$authenticated->{MFA_UUID});$ENV{HTTPS} = 'ON' if $ENV{SERVER_NAME} =~ /^peeps/;my $destination = url ();print header(-cookie=>[$PEEPSAUTH_cookie, $PEEPSMFA_cookie]);printRCHeader("MFA Confirmed", $destination);print<<goforth;Your MFA Code has been confirmed. You may continue on your way.<br><br><a href="$destination">Continue.</a>goforthexit;}# If we get here, the user has failed authentication; throw up the log-in screen and die.my $PEEPSAUTH_cookie = CGI::Cookie->new(-name=>'PEEPSAUTH',-value=>$authenticated->{cookie_string});if ($authenticated->{ERRMSG}) {$authenticated->{ERRMSG} = "<TR><TD colspan=2 align=center><font color=red><b>".$authenticated->{ERRMSG}."</b></font> </TD></TR>";} else {$authenticated->{ERRMSG} = "";}print header(-cookie=>$PEEPSAUTH_cookie);printRCHeader("Sign In");print<<authpage;<form action="$ENV{REQUEST_URI}" method=POST name=Req id=Req><h2>Please Sign In</h2><TABLE>$authenticated->{ERRMSG}authpageprint<<firsttimer unless $query->cookie ("PEEPS_MFA_UUID");<TR><TD colspan=2>It looks like this might be your first visit to PEEPS.<br>If you had a login to MemberSuite, use the<br><A HREF="recoverAccount">[recover your account]</A> link with either your <br>registered email address or Member ID to find<br>and link your account.</td></tr><TR><TD colspan=2> </td></tr>firsttimerif ($ENV{'QUERY_STRING'} eq "LOGOUT") {print "<TR><TD colspan=2> </TD></TR>";print "<TR><TD colspan=2><button onClick=\"location.href='';\">Log In</button></TD></TR>";print "</TABLE></BODY></HTML>";exit;}if ($authenticated->{authenticated} eq "inactive") {print<<activationpage;<TR><TD colspan=2 align=center> </TD></TR><TR><TD align=right><B>Activation Code:</TD><TD><INPUT type=text id=activate name=activate></TD></TR><TR><TD></TD><TD><INPUT type=submit name=login value=Submit></TD></TR><TR><TD colspan=2 align=center> </TD></TR><TR><TD colspan=2 align=center><A HREF='' onClick='document.getElementById("activate").value="resend"; Req.submit(); return false;'>[Resend your activation email]</A></TD></TR><TR><TD colspan=2 align=center><A HREF='' onClick="location.href='?LOGOUT';">[Log Out]</A></TD></TR></TABLE></FORM>activationpage} elsif ($authenticated->{authenticated} eq "needsMFA") {print<<MFApage;<TR><TD colspan=2 align=center> </TD></TR><TR><TD align=right><B>Email Authentication Code:</TD><TD><INPUT type=text id=authenticate name=authenticate></TD></TR><TR><TD></TD><TD><INPUT type=submit name=login value=Submit></TD></TR><TR><TD colspan=2 align=center> </TD></TR><TR><TD colspan=2 align=center>Save this browser: <label class="switch"><input name="saveMFA" type="checkbox" value="1"><span class="slider round"></span></label></TD></TR><TR><TD colspan=2 align=center><A HREF='' onClick='document.getElementById("authenticate").value="resend"; Req.submit(); return false;'>[Send new authentication code]</A></TD></TR><TR><TD colspan=2 align=center><A HREF='' onClick="location.href='?LOGOUT';">[Log Out]</A></TD></TR></TABLE></FORM>MFApage} else {print<<authpage2;<TR><TD style="text-align: right;"><B>Username:</TD><TD><INPUT type=text id=login name=userid></TD></TR><TR><TD style="text-align: right;"><B>Password:</TD><TD><INPUT type=password name=pass></TD></TR><TR><TD></TD><TD style="text-align: right;"><input type=hidden name=activate id=activate value=$FORM{'activate'}><input type=hidden name=authenticate id=authenticate value=$FORM{'authenticate'}><INPUT type=submit name=login value=Submit></TD></TR><TR><TD colspan=2> </TD></TR><TR><TD colspan=2 align=center><A HREF="view_user?submit=New%20User">[register as a new user]</A></TD></TR><TR><TD colspan=2 align=center><A HREF="recoverAccount">[recover your account]</A></TD></TR></TABLE></FORM><SCRIPT language="JavaScript"><!--document.getElementById("login").focus();function Login () {document.getElementById('Req').action = "$ENV{SCRIPT_NAME}";document.getElementById('Req').submit.click();return true;}//--></SCRIPT>authpage2}#foreach (keys %ENV) {# print "$_: $ENV{$_}<br>";#}# &JScript;exit;}sub canView {my $A = shift // "";my $B = shift // "";# Is A a lead or higher of one of B's Depts? (or they're looking at themselves)# parameters should be a Hashref to the users' detailsreturn 1 if $A->{person_id} == $B->{person_id}; # It's the same person.my ($admin_check) = $dbh->selectrow_array ("select 1 from role where person_id = ? and member_org_id = ? and role = ?", undef, $A->{person_id}, 4276, "System Admin"); # 4276 is "WFTDA Leadership"return 1 if $admin_check or $A->{access} > 4; # viewer and target are the same person or it's a SysAdmin.my ($league_admin) = $dbh->selectrow_array ("select * from role where person_id = ? and member_org_id in (select distinct member_org_id from role where person_id = ?) and role = ?", undef, $A->{person_id}, $B->{person_id}, "League Admin");return 1 if $league_admin;return 0;}sub getShiftDepartment {my $shiftID = shift // "";my $dept;if ($shiftID =~ /^\d+$/) {($dept) = $dbh->selectrow_array ("select dept from shift where id = ?", undef, $shiftID);} else {my ($id, $role) = split /-/, $shiftID;if ($role =~ /^CLA/) {$dept = "CLA";} else {($dept) = $dbh->selectrow_array ("select distinct department from staff_template where role like ?", undef, $role.'%');}}# } elsif ($shiftID =~ /^\d+-ANN/) {# $dept = "ANN";# } else {# $dept = "OFF";# }return $dept;}sub getClassID {my $shift = shift // "";return unless $shift =~ /^\d+$/;my $shiftref = getShiftRef ($shift);my ($classid) = $dbh->selectrow_array ("select id from class where date = ? and start_time = ? and location = ?", undef, $shiftref->{date}, $shiftref->{start_time}, $shiftref->{location});return $classid unless !$classid;warn "ERROR: No class.id found for shift $shiftref->{id}";return "";}sub getShiftRef {my $shiftID = shift // "";return unless $shiftID =~ /^\d+$/;my ($shiftref) = $dbh->selectrow_hashref ("select * from shift where id = ?", undef, $shiftID);return $shiftref unless $shiftref->{id} != $shiftID;warn "ERROR: Couldn't find shift with ID [$shiftID]";return "";}sub getLeagueAffiliation {my $PEEPSid = shift // "";my $results;my $sth = $dbh->prepare ("select member_org_id, league_name, role, type from role join organization on member_org_id = organization.id left join person on person_id = person.id where person_id = ?");if ($PEEPSid =~ /^\d+$/) {$sth->execute ($PEEPSid);while (my ($orgid, $orgname, $role, $orgtype) = $sth->fetchrow_array ()) {push (@{$results->{$orgid}}, $role);}}return $results;}sub getLeagueName {my $id = shift // "";my ($name) = $dbh->selectrow_array ("select league_name from organization where id = ?", undef, $id);return $name;}sub getLeagues {my $exclude_id = shift // 0;return $dbh->selectall_arrayref ("select id, concat_ws(' - ', league_name, type, status) as league from organization where status in ('Active', 'Voluntary Inactive') and visible = 1 and id not in (select member_org_id from role where person_id = ?) order by league_name", undef, $exclude_id);}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;}$output = {} unless ref $output eq "HASH";}return $output;}sub getPolicyByID {my $pid = shift // "";(warn "ERROR: No PolicyID passed to getPolicyByID()" and return) unless $pid =~ /^\d+$/;my $policy = $dbh->selectrow_hashref ("select * from policy where id = ?", undef, $pid);(warn "ERROR: No Policy found with id: $pid" and return) unless $policy->{id} =~ /^\d+$/;return $policy;}sub getCoverageByID {my $pid = shift // "";my $person = shift // "";(warn "ERROR: No PolicyID passed to getCoverageByID()" and return) unless $pid =~ /^\d+$/;(warn "ERROR: No PersonID passed to getCoverageByID()" and return) unless $person =~ /^\d+$/;my $policy = $dbh->selectrow_hashref ("select * from coverage where id = ? and person_id = ?", undef, $pid, $person);(warn "ERROR: No Coverage found with id: $pid and person_id: $person" and return) unless $policy->{id} =~ /^\d+$/;return $policy;}sub convertTime {my $time = shift || return;if ($time =~ / - /) {return join " - ", map { convertTime ($_) } split / - /, $time;}$time =~ s/^(\d{1,2}:\d{2}):\d{2}$/$1/;$time =~ s/^0//;if ($ORCUSER->{timeformat} eq "24hr") {if ($time =~ /^\d{1,2}:\d{2}$/) { return $time; }} else {my ($hr, $min) = split /:/, $time;my $ampm = " am";if ($hr >= 12) {$hr -= 12 unless $hr == 12;$ampm = " pm";} elsif ($hr == 0) {$hr = 12;}return $hr.":".$min.$ampm;}}sub getSchedule {my $RCid = shift // return "ERROR: No RCid provided to getSchedule";my $filter = shift // "";my $output = shift // "";my $year = 1900 + (localtime)[5];my @whereclause;if ($filter eq "all") {push @whereclause, "year(date) >= year(now())";} elsif ($filter eq "prior") {push @whereclause, "year(date) < year(now())";} else {push @whereclause, "date >= date(now())";}# if ($RCid ne $ORCUSER->{RCid}) {# push @whereclause, "dept != 'PER'";# }use DateTime;my $dt = DateTime->today (time_zone => 'America/Los_Angeles');$dt =~ s/T00\:00\:00$//;my $now = DateTime->now (time_zone => 'America/Los_Angeles');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, signup, 'OFF' as dept, volhours from v_shift_officiating where RCid = ? unionselect id, date, dayofweek, track as location, time, role, teams, signup, 'ANN' as dept, volhours from v_shift_announcer where RCid = ? unionselect id, date, dayofweek, location, time, role, '' as teams, type as signup, dept, volhours from v_shift where RCid = ? unionselect id, date, dayofweek, location, time, role, name as teams, 'mvpclass' as signup, 'CLA' as dept, 0 as volhours from v_class_signup_new where RCid = ?) temp$where order by date, time");$sth->execute($RCid, $RCid, $RCid, $RCid);my $hours = 0;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} or $s->{dept} eq "CLA") {# it's a time-based shiftif ($s->{dept} eq "PER") {if ($RCid eq $ORCUSER->{RCid}) {# DROP$s->{buttons} = $h->button ({ onClick=>"event.stopPropagation(); if (confirm('Really? You want to delete this personal time?')==true) { location.href='personal_time?choice=Delete&id=$s->{id}'; return false; }" }, "DEL")." ".$h->button ({ onClick=>"event.stopPropagation(); location.href='personal_time?choice=Update&id=$s->{id}'" }, "EDIT");} else {$s->{location} = "";$s->{role} = "";}} elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^selected/ and $now < $cutoff) or ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5)) {# DROPmy ($shiftORclass, $linkargs) = ("shift", "");if ($s->{dept} eq "CLA") {$shiftORclass = "class";$linkargs = "&role=$s->{role}";$s->{role} = $s->{teams};$s->{teams} = "";}$s->{buttons} = $h->button ({ onClick=>"if (confirm('Really? You want to drop this $shiftORclass?')==true) { window.open('make_shift_change?change=del&RCid=$RCid&id=$s->{id}$linkargs','Confirm Class Change','resizable,height=260,width=370'); return false; }" }, "DROP");if ($ORCUSER->{department}->{$s->{dept}} >= 2 or $ORCUSER->{access} >= 5) {# NO SHOW$s->{buttons} .= " ".$h->button ({ onClick=>"if (confirm('Really? They were a no show?')==true) { window.open('make_shift_change?noshow=true&change=del&RCid=$RCid&id=$s->{id}$linkargs','Confirm Shift Change','resizable,height=260,width=370'); return false; }" }, "NO SHOW");}}# $hours += $s->{volhours} unless $s->{dept} eq "PER" or $s->{dept} eq "CLA";} elsif (($RCid == $ORCUSER->{RCid} and $s->{signup} !~ /^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?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} .= " ".$h->button ({ onClick=>"if (confirm('Really? They were a no show?')==true) { window.open('make_shift_change?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 ' ', $s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams}, $s->{buttons});# 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});$s->{time} = convertTime $s->{time};if ($s->{dept} eq "PER") {push @shifts, $h->li ({ onClick => "location.replace('personal_time?id=$s->{id}');", class=> $s->{date} eq $dt ? "highlighted" : "shaded" }, $h->div ({ class=>"lisp0" }, [ $h->div ({ class=>"liLeft" }, join ' ', ($s->{date}, $s->{dayofweek}, $s->{time}, $s->{location}, $s->{dept} eq "CLA" ? "MVP Class:" : getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams})), $h->div ({ class=>"liRight" }, $s->{buttons}) ]));} else {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}, $s->{dept} eq "CLA" ? "MVP Class:" : getDepartments()->{$s->{dept}}, $s->{role}, $s->{teams})), $h->div ({ class=>"liRight" }, $s->{buttons}) ]));}$hours += $s->{volhours} unless $s->{dept} eq "PER" or $s->{dept} eq "CLA";}if ($output eq "hours") {return $hours;}if (scalar @shifts) {return $h->ul ([ @shifts, $h->h5 ("Currently showing $hours hours of Volunteer Time.") ]);} elsif ($filter eq "prior") {return $h->p ({ class=>"hint" }, "[nothing to see here]");} else {return $h->p ({ class=>"hint" }, "[nothing scheduled at the moment]");}}sub getRCid {my $derbyname = shift;($derbyname) = $dbh->selectrow_array ("select RCid from official where derby_name = ?", undef, $derbyname);return $derbyname;}sub getSetting {my $k = shift;my ($value) = $dbh->selectrow_array ("select setting.value from setting where setting.key = ?", undef, $k);return defined $value ? $value : undef;}sub getUser {my $ID = shift;my $sth;if ($ID =~ /^\d+$/) {$sth = $dbh->prepare("select * from person where id = ?");} elsif ($ID =~ /@/) {$sth = $dbh->prepare("select * from person where email = ?");} else {$sth = $dbh->prepare("select * from person where id = (select person_id from authentication where username = ?)");}$sth->execute($ID);my $user = $sth->fetchrow_hashref;my $auth = $dbh->selectrow_hashref ("select * from authentication where person_id = ?", undef, $user->{id});map { $user->{$_} = "" unless $user->{$_} } keys %{$user};map { $user->{$_} = $auth->{$_} ? $auth->{$_} : "" } keys %{$auth};$user->{person_id} = $user->{id};return $user->{id} ? $user : "";}sub getUserEmail {my $RCid = shift;my $sth = $dbh->prepare("select email from official where RCid = ?");$sth->execute($RCid);my ($email) = $sth->fetchrow_array();return $email;}sub getUserDerbyName {my $RCid = shift;my $sth = $dbh->prepare("select derby_name from official where RCid = ?");$sth->execute($RCid);my ($dname) = $sth->fetchrow_array();return $dname;}sub getYears {my $sth = $dbh->prepare("select distinct year from (select distinct year(date) as year from shift union select distinct year(date) as year from game union select distinct year(date) as year from class union select year(now()) as year) years order by year");# 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;my $redirect = shift // "";# use CGI qw/start_html/;use HTML::Tiny;my $h = HTML::Tiny->new( mode => 'html' );$ENV{HTTPS} = 'ON' if $ENV{SERVER_NAME} =~ /^peeps/;# my $logout = $h->a ({ href=>"index", onClick=>"document.cookie = 'PEEPSAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/';return true;" }, "[Log Out]");my $referrer = param ("referrer") ? param ("referrer") : $ENV{HTTP_REFERER};my $logout = (!$referrer or $referrer eq url) ? "" : $h->button ({ onClick=>"window.location.href='$referrer';" }, "Back")." ";$logout .= url =~ /\/(index)?$/ ? "" : $h->button ({ onClick=>"window.location.href='/';" }, "Home")." ";# $logout .= $h->button ({ onClick=>"document.cookie = 'PEEPSAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/'; location.href='/';" }, "Log Out");$logout .= $h->button ({ onClick=>"location.href='?LOGOUT';" }, "Log Out");my $loggedinas = $ORCUSER ? "Currently logged in as: ".$h->a ({ href=>"/view_user?submit=View&person_id=$ORCUSER->{person_id}" }, $ORCUSER->{derby_name}).$h->br.$logout : "";# print start_html (-title=>"vORC - $PAGE_TITLE", -style => {'src' => "/style.css"} );my $ANALYTICS = <<MATOMO;var _mtm = window._mtm = window._mtm || [];_mtm.push({'mtm.startTime': (new Date().getTime()), 'event': 'mtm.Start'});(function() {var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];g.async=true; g.src='https://analytics.whump.org/js/container_to4NCtvM.js'; s.parentNode.insertBefore(g,s);})();MATOMOprint $h->open ("html");print $h->head ([$h->title ("PEEPS - $PAGE_TITLE"),$h->link ({ rel => "stylesheet",type => "text/css",href => "/style.css" }),$redirect ? $h->meta ({ 'http-equiv'=>"refresh", content=>"0; URL=".$redirect }) : "",# $h->script ($ANALYTICS)]);print $h->open ("body");# print $h->img ({referrerpolicy=>"no-referrer-when-downgrade", src=>"https://analytics.whump.org/matomo.php?idsite=2&rec=1", style=>"border:0", alt=>""});#<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=>"/" }, $h->img ({ src=>"/images/wftdapeeps-powerby-wftdainsurance-2.svg", width=>"400", height=>"75" }))),$h->div ({ class=>"spRight" }, [ $h->h1 (["$PAGE_TITLE", $h->br]),$loggedinas,])]);#print<<rcheader;# <TABLE># <TR class="nostripe"># <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># <p align=right><font size=-2>$loggedinas <a href='index' onClick="document.cookie = 'PEEPSAUTH=; expires=Thu, 01 Jan 1970 00:00:01 GMT; path=/';return true;">[Log Out]</a></font></TD># </TR>#rcheader}sub changeShift {my ($change, $shift_id, $role, $user_id) = @_;if ($shift_id =~ /(am|pm)/) {my ($td, $st, $tl) = split /\|/, $shift_id;my ($hr, $min, $ampm) = split /:|\s/, $st;if ($ampm eq "pm") { $hr += 12; }elsif ($ampm eq "am" and $hr == 12) { $hr = "00" }$st = $hr.":".$min;$shift_id = join "|", ($td, $st, $tl);} else {$shift_id =~ s/(\d+:\d+):00/$1/;}#warn join " - ", $change, $shift_id, $role, $user_id;my $leadership_change = 0;# my $department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);my $department;if ($shift_id =~ /^\d+$/) {$department = getShiftDepartment ($role ? $shift_id."-".$role : $shift_id);} else {$department = "CLA";if ($change eq "del") {($shift_id, $role) = $dbh->selectrow_array ("select id, role from v_class_signup_new where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id);} else {if ($change eq "override") {($shift_id, $role) = $dbh->selectrow_array ("select id, concat('CLA-', max(cast(substring_index(role, '-', -1) as UNSIGNED)) +1) as role from v_class_signup_new where date = ? and start_time = ? and location = ?", undef, split /\|/, $shift_id) unless $change ne "override";} else {($shift_id, $role) = $dbh->selectrow_array ("select id, concat('CLA-', max(cast(substring_index(role, '-', -1) as UNSIGNED)) +1) as role, count(role), capacity from v_class_signup_new where date = ? and start_time = ? and location = ? having capacity > count(role)", undef, split /\|/, $shift_id);}}$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 =~ /^CLA-/ ? "class" : $role ? "game" : "shift";my $sth;if ($change eq "add" or $change eq "override") {my $taken;if ($department eq "CLA") {($taken) = $shift_id ? 0 : 1;} elsif ($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 or assignee_id <> "")', undef, $shift_id);}if ($taken) {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";}}if (lc ($user_id) ne lc ($ORCUSER->{RCid})) { # they're changing someone else's schedule...if (($department eq "CLA" and $ORCUSER->{department}->{MVP} >= 2) or $ORCUSER->{department}->{$department} >= 2 or $ORCUSER->{access} >= 5 or $ORCUSER->{department}->{VCI} >= 2) {# the user making the change is either a lead in the dept, a sysadmin, or a VCI leadlogit ($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, $shift_id, $role, $user_id)");return "<br>Denied! You are not authorized to change someone else's schedule in this department ($department).<br>\n";}} elsif ($ORCUSER->{department}->{$department} >= 3 or $ORCUSER->{access} >= 5) {# Managers can sign up for as many shifts within their own department as they like...$leadership_change = 1;}if ($change eq "add") {if ($department eq "CLA" and !getUser($user_id)->{MVPid}) {return "<br>Denied! User ($user_id) does not have an MVP Pass!<br>\n";} elsif ($department ne "CLA" and getUser($user_id)->{department} and convertDepartments(getUser($user_id)->{department})->{$department} < 1) {return "<br>Denied! User ($user_id) is not a member of Department ($department)!<br>\n" unless $department eq "CMP";} elsif ($department eq "EMT" and getUser($user_id)->{emt_verified} == 0) {return "<br>Denied! User ($user_id) has not had their EMT status verified!<br>\n";}}my $conflict = findConflict ($user_id, $shift_id, $game_based);if ($change eq "add" and $conflict) {return "<br>Denied! There is a conflict ($conflict) with that shift's time!<br>\n";}my $game_type;if ($department ne "CLA") {($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" unless $department eq "CMP";}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";}} else {$game_type = "class";}# my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY");my $MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$department);$MAXSHIFTS = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $MAXSHIFTS;if ($game_type eq "lead" and $department eq "OFF") { $MAXSHIFTS = 99; }my $daily_count;if ($department eq "CLA") {# MVP Class Sign-up$MAXSHIFTS = getSetting ("MAX_CLASS_SIGNUP");($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);if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {return "<br>Denied! You may only sign up for $MAXSHIFTS Classes!<br>\n";}} else {$daily_count = signUpCount ('get', $user_id, $department);if ($change eq "add" and $daily_count >= $MAXSHIFTS and !$leadership_change) {return "<br>Denied! You may only sign up for $MAXSHIFTS $game_type shifts in one day!<br>\n";}if ($change eq "add" and $game_based eq "game" and ($department eq "OFF" or $department eq "ANN") and $game_type eq "full length" and !$leadership_change) {my $dept_table = $department eq 'OFF' ? "v_shift_officiating" : "v_shift_announcer";my ($full_length_count) = $dbh->selectrow_array ("select count(*) from $dept_table where RCid = ? and gtype = 'full length' and year(date) = year(now())", undef, $user_id);my $full_length_max = getSetting("MAX_FULL_LENGTH_SIGNUP_".$department);if ($full_length_count >= $full_length_max) {my $errormsg = "<br>Denied! You may only sign up to ".($department eq 'OFF' ? "officiate" : "announce")." $full_length_max $game_type game(s) (total)!<br>\n";return $errormsg;}}}my @DBARGS;if ($game_based eq "game" or $game_based eq "class") {if ($change eq "add" or $change eq "override") {$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" or $change eq "override") {$sth = $dbh->prepare("update shift set assignee_id = ? where id = ? and isnull(assignee_id) = 1");@DBARGS = ($user_id, $shift_id);} elsif ($change eq "del") {$sth = $dbh->prepare("update shift set assignee_id = null where id = ?");@DBARGS = ($shift_id);}}my $wb_act_code;if ($change eq "del" and $department eq "CLA") {($wb_act_code) = $dbh->selectrow_array ("select wb_ticket_act from assignment where Gid = ? and RCid = ? and role like ?", undef, $DBARGS[0], $DBARGS[2], 'CLA-%');}print "<br>attempting to make DB changes...<br>";if ($sth->execute (@DBARGS)) {$daily_count = signUpCount ($change, $user_id, $department) unless $leadership_change;logit ($user_id, "Shift ".ucfirst($change).": $shift_id -> $role");logit ($ORCUSER->{RCid}, "OVERRIDE: Shift ".ucfirst($change).": $shift_id -> $role") if $change eq "override";if ($department eq "CLA") {print "Success!...<br>You've signed up for $daily_count class(es) (you're currently allowed to sign up for $MAXSHIFTS).<br>\n";updateWRSTBND ($change, $wb_act_code, $DBARGS[0], $DBARGS[2]);} else {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 {if ($department eq "CLA") {return "<br><b>You did not get the class</b>, most likely because it filled up while you were looking.<br>\nERROR: ", $sth->errstr();} 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 updateWRSTBND {my ($change, $wb_act_code, $shift_id, $user_id) = @_;use REST::Client;use JSON;my $headers = { Authorization => getSetting ("WRSTBND_API_KEY") };my $client = REST::Client->new();$client->setHost('https://core.wrstbnd.io');my ($accountid) = $dbh->selectrow_array ("select wrstbnd_accountid from RCid_ticket_link left join ticket on MVPid = id where RCid = ? and year = year(now())", undef, $user_id);if ($change eq "add" or $change eq "override") {my ($classid) = $dbh->selectrow_array ("select wrstbnd_id from class where id = ?", undef, $shift_id);my $body = {"eventId" => "event_893C6u5olU","activeStatus" => "active","ticketTypeId" => $classid};my $json_body = encode_json $body;$client->POST('/rest/core/v1/ticket',$json_body,$headers);my $response = from_json($client->responseContent());my $activationCode = $response->{activationCode};my $api_key = getSetting ("WRSTBND_API_KEY");my @add_response = `/bin/curl --location --request POST 'https://core.wrstbnd.io/rest/core/v1/assign' --header 'Authorization: $api_key' --form accountid=$accountid --form ticketactcode=$activationCode --output /dev/null --silent --write-out '%{http_code}\n'`;my $add_response = $add_response[$#add_response];chomp $add_response;$dbh->do ("update assignment set wb_ticket_act = ? where Gid = ? and RCid = ? and role like ?", undef, $activationCode, $shift_id, $user_id, 'CLA-%') unless $add_response ne "200";return;} elsif ($change eq "del") {my $activationCode = $wb_act_code;my $api_key = getSetting ("WRSTBND_API_KEY");my $del_response = `/bin/curl --location --request DELETE 'https://core.wrstbnd.io/rest/core/v1/assign' --header 'Authorization: $api_key' --form accountid=$accountid --form ticketactcode=$activationCode --output /dev/null --silent --write-out '%{http_code}\n'`;}}sub modShiftTime {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;logit (0, "ERROR modifying a shift time ($diff, $shift_id, $user_id):".$dbh->errstr);} else {print "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)";logit ($ORCUSER->{RCid}, "SUCCESS: Shift $shift_id succesfully modified by $diff hour(s)");}return;}sub signUpCount {my $action = shift;my $id = shift;my $dept = shift // "";if ($id eq $ORCUSER->{RCid}) {if ($action eq 'add') {if (signUpCount ('get', $id, $dept)) {$dbh->do("update sign_up_count set sign_ups = sign_ups + 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);} else {$dbh->do("replace into sign_up_count (date, RCid, department, sign_ups) values (curdate(), ?, ?, 1)", undef, $id, $dept);}} elsif ($action eq 'del') {if (signUpCount ('get', $id, $dept)) {$dbh->do("update sign_up_count set sign_ups = sign_ups - 1 where date = curdate() and RCid = ? and department = ?", undef, $id, $dept);}}}my ($R) = $dbh->selectrow_array ("select sign_ups from sign_up_count where RCid = ? and department = ? and date = curdate()", undef, $id, $dept);return $R ? $R : '0';}sub signUpEligible {my $user = shift;my $t = shift;my $shifttype = shift // "game";my $dept = $t->{dept} // "";my $DEPTHASH = getDepartments ();if ($dept and !exists $DEPTHASH->{$dept}) {my %reverso = reverse %{$DEPTHASH};$dept = $reverso{$dept};}my $limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY_".$dept);$limit = getSetting ("MAX_SHIFT_SIGNUP_PER_DAY") unless defined $limit;if (lc $t->{type} eq "lead" and $dept eq "OFF") { $limit = 99; }return 0 unless $limit > 0;my $limitkey = $dept ? "sign_ups_today_".$dept : "sign_ups_today";if ($shifttype eq "class") {my $classid = $t->{id};$t->{start_time} =~ s/^(\d+:\d+):00$/$1/;($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";$dept = "CLA";$t->{type} = "open";}if (findConflict ($user->{RCid}, $t->{id}, $shifttype)) { return 0; }if (!exists $user->{$limitkey}) {$user->{$limitkey} = signUpCount('get', $user->{RCid}, $dept);}if ($shifttype eq "game") {# if ($t->{gtype} !~ /^selected/ and $t->{gtype} ne "short track" and $user->{$limitkey} < $limit) {if ($t->{gtype} eq "full length" and ($dept eq "OFF" or $dept eq "ANN")) {my $table = $dept eq "OFF" ? "v_shift_officiating" : "v_shift_announcer";my ($full_length_count) = $dbh->selectrow_array ("select count(*) from $table where RCid = ? and gtype = 'full length' and year(date) = year(now())", undef, $user->{RCid});if ($full_length_count >= getSetting ("MAX_FULL_LENGTH_SIGNUP_".$dept)) {return 0;}}if (lc $t->{signup} ne "selected" and $user->{$limitkey} < $limit) {return 1;} else {return 0;}} else {if ($dept eq "CLA") {# MVP Class Sign-upreturn 0 unless $user->{MVPid};my $class_limit = getSetting ("MAX_CLASS_SIGNUP");my ($class_count) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where RCid = ? and year(date) = year(now())", undef, $user->{RCid});return 0 unless $class_count < $class_limit;} else {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 "manager" and $user->{department}->{$dept} < 3) { return 0; }if ($dept eq "EMT" and $user->{emt_verified} == 0) { return 0; }if (lc $t->{type} !~ /^selected/ and $user->{$limitkey} < $limit) {return 1;} else {return 0;}}}sub findConflict {my $rcid = shift;my $gid = shift;my $type = shift // "";my ($date, $start, $end, $existing, $conflicts);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 "OFF-".$gid; } # 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 "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);} elsif ($type eq "class") {($conflicts) = $dbh->selectrow_array ("select count(*) from v_class_signup_new where id = ? and RCid = ?", undef, $gid, $rcid);if ($conflicts) { return "CLA:".$gid; } # no need to keep looking...($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") {($date, $start, $end, $existing) = @{ $gid };} else {($date, $start, $end) = $dbh->selectrow_array ("select distinct date, start_time, end_time from shift where id = ?", undef, $gid);}# Are they signed up for any games that would conflict with this one?# 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 = ?");($conflicts) = $dbh->selectrow_array ("select * from (select concat(dept, '-', id) as conflict from v_shift where date = ? and ((start_time <= ? and end_time > ?) or (start_time > ? and start_time < ?)) and RCid = ? unionselect 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 = ? unionselect 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 = ? unionselect 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 = ? ) alltableswhere 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);return $conflicts;}sub changeLeadShift {my ($change, $lshift, $user_id) = @_;my $ERRMSG;my $sth = $dbh->prepare("update lead_shift set assignee_id = ? where id = ?");print "<br>attempting to make DB changes...<br>";if ($change eq "add") {$sth->execute($user_id, $lshift)or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";} elsif ($change eq "del") {$sth->execute('', $lshift)or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";}if ($ERRMSG) {print $ERRMSG;} else {logit($user_id, "Lead Shift ".ucfirst($change).": $lshift");print "Success.<br>";}}sub logit {my $RCid = shift;my $msg = shift;my $sth = $dbh->prepare("insert into log (person_id, ip_address, event) values (?, ?, ?)");$sth->execute($RCid, $ENV{REMOTE_ADDR}, $msg);}sub orglogit {my $RCid = shift;my $org = shift;my $msg = shift;$dbh->do ("insert into organization_log (person_id, organization_id, ip_address, event) values (?, ?, ?, ?)", undef, $RCid, $org, $ENV{REMOTE_ADDR}, $msg);}sub sendUserMFAEMail {my $user = shift // return "ERROR [sendUserMFAEMail]: No user data sent to function.";use PEEPSMailer;use HTML::Tiny;my $h = HTML::Tiny->new( mode => 'html' );$ENV{HTTPS} = 'ON' if $ENV{SERVER_NAME} =~ /^peeps/;return "ERROR [sendUserMFAEMail]: No email address found for user" unless $user->{email};my $random_six_digit_number = 100000 + int(rand(900000));my $string_number = sprintf ("%06d", $random_six_digit_number);$dbh->do ("update authentication set mfa = ?, mfa_timestamp = now() where person_id = ?", undef, $string_number, $user->{person_id});my $subject = 'WFTDI PEEPS - Login MFA Verification Code';my $body = $h->p ({ style => "font-family: Verdana;" }, "Greetings,", "It appears you are trying to log into PEEPS from somewhere new. Here's a code to enter:");$body .= $h->p ({ style => "font-family: Verdana; font-size: larger; font-weight: bold;" }, $string_number);$body .= $h->p ({ style => "font-family: Verdana;" }, "Or click ".$h->a ({ href => url ()."?authenticate=".$string_number }, "this link").".");$body .= $h->p ({ style => "font-family: Verdana; font-size: smaller; font-style: italic;" }, "", "Sent by PEEPS Automated Emailer");EmailUser ($user->{email}, $subject, $body);}sub sendNewUserEMail {my $context = shift;my $data = shift;use PEEPSMailer;use HTML::Tiny;my $h = HTML::Tiny->new( mode => 'html' );my $depts = getDepartments (); # HashRef of the department TLAs -> Display Names...my $AccessLevel = getAccessLevels;my $email = $data->{email};my $subject = 'WFTDI PEEPS - New User';my $body;if ($context eq "New User") {$subject .= " Request";$ENV{HTTPS} = 'ON' if $ENV{SERVER_NAME} =~ /^peeps/;my $activationlink = url ()."?activate=".$data->{activation};$body = $h->p ("Greetings,");$body .= $h->p ("It appears as though you've registered a new account in WFTDI's PEEPS system with the following information:");$body .= $h->table ([$h->tr ([$h->td (" ", "Derby Name:", $data->{derby_name})]),$h->tr ([$h->td (" ", "Civil Name:", join (" ", $data->{name_first}, $data->{name_middle}, $data->{name_last}))]),# $h->tr ([$h->td (" ", "Pronouns:", $data->{pronouns})]),# $h->tr ([$h->td (" ", "TShirt Size:", $data->{tshirt})]),$h->tr ([$h->td (" ", "Email Address:", $data->{email})]),# $h->tr ([$h->td (" ", "Phone:", $data->{phone})])]);$body .= $h->p ("To validate that you've entered a real (and correct) email address (and that you're not a spam-bot), please click the following link:",$h->a ({ HREF=>$activationlink }, "Activate my PEEPS Account!"), $h->br,"Or you can copy/paste this into the 'Activation Code' box: ".$data->{activation}, $h->br,"Once activated, you'll be able to log in.","If you didn't make this request, well, you're still the only one who received this email, and you now have an account request. You should probably let us know that someone is messing with you.",$h->br,"--PEEPS Automated Emailer");} elsif ($context eq "Activate") {$subject .= " Activated!";$body = "Greetings again,Your PEEPS account has been actived.--PEEPS Automated Emailer";} else {return;}# send the messageEmailUser ($email, $subject, $body);}sub isPersonCovered {my $pid = shift // "";my $date = shift // "";return "" unless $pid =~ /^\d+$/;return "" unless !$date or $date =~ /^\d{4}-\d{2}-\d{2}$/;my $policy_id;if ($date) {($policy_id) = $dbh->selectrow_array ("select id from coverage where person_id = ? and datediff(start, ?) <= 1 and datediff(end, ?) >= 0 and isnull(coverage.terminated) = 1", undef, $pid, $date, $date);} else {($policy_id) = $dbh->selectrow_array ("select id from coverage where person_id = ? and datediff(start, now()) < 1 and datediff(end, now()) >= 0 and isnull(coverage.terminated) = 1", undef, $pid);}return $policy_id;}sub isLeagueCovered {my $pid = shift // "";my $date = shift // "";my $type = shift // "WFTDA General Liability Insurance";return "" unless $pid =~ /^\d+$/;return "" unless !$date or $date =~ /^\d{4}-\d{2}-\d{2}$/;my $policy_id;if ($date) {($policy_id) = $dbh->selectrow_array ("select id from org_coverage where policy_name = ? and organization_id = ? and datediff(start, ?) <= 1 and datediff(end, ?) >= 0 and isnull(org_coverage.terminated) = 1", undef, $type, $pid, $date, $date);} else {($policy_id) = $dbh->selectrow_array ("select id from org_coverage where policy_name = ? and organization_id = ? and datediff(start, now()) < 1 and datediff(end, now()) >= 0 and isnull(org_coverage.terminated) = 1", undef, $type, $pid);}return $policy_id;}sub isLeagueAdmin {my $person = shift // "";if (ref $person eq "HASH") {$person = $person->{person_id};}die "ERROR: function isLeagueAdmin(person_id) didn't receive proper argument" unless $person =~ /^\d+$/;my @array_of_leagues = map { $_->[0] } @{ $dbh->selectall_arrayref ("select member_org_id from role where person_id = ? and role = ?", undef, $person, "League Admin") };return scalar @array_of_leagues ? \@array_of_leagues : [];}sub isWFTDAMember {my $pid = shift // "";return "" unless $pid =~ /^\d+$/;my ($membership) = $dbh->selectrow_array ("select count(*) from organization where type = ? and status = ? and id in (select member_org_id from role where person_id = ?)", undef, "member league", "Active", $pid);return $membership;}sub remainingPolicyDays {my $person = shift // "";my $policy = shift // "";return "" unless $person =~ /^\d+$/;return "" unless $policy =~ /^\d+$/;my ($days_remaining) = $dbh->selectrow_array ("select datediff(end, now()) from coverage where id = ? and person_id = ?", undef, $policy, $person);return defined $days_remaining ? $days_remaining : "ERROR: Policy Not Found";}sub remainingOrgPolicyDays {my $league = shift // "";my $policy = shift // "";return "" unless $league =~ /^\d+$/;return "" unless $policy =~ /^\d+$/;my ($days_remaining) = $dbh->selectrow_array ("select datediff(end, now()) from org_coverage where id = ? and organization_id = ?", undef, $policy, $league);return defined $days_remaining ? $days_remaining : "ERROR: Policy Not Found";}1;