Subversion Repositories ORC

Rev

Rev 7 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

#!/usr/bin/perl -w

use strict;
use lib "/home/rollerco/perl5/lib/perl5";
use RollerCon;
use CGI;
use CGI::Cookie;
use DBI;
use Email::Valid;

my ($FORM, $cookie_string, $ERRMSG);
my @ERRORS;
my $dsn = "DBI:mysql:database=rollerco_data;host=localhost;port=3306";
my $dbh = DBI->connect($dsn, 'rollerco_www', 'www-data');

my $query = new CGI;
$FORM->{'SUB'} = $query->param('submit') || '';
$FORM->{'RCid'} = $query->param('RCid') || '';
if ($FORM->{'SUB'} eq '') {
        if ($ENV{'REQUEST_URI'}) {
                my ($g, $keep) = split /\?/, $ENV{'REQUEST_URI'};
                if ($keep) {
                        foreach (split /&/, $keep) {
                                my ($k, $v) = split /=/;
                                $k =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
                                $v =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
                                $k eq "submit" ? $FORM->{'SUB'} = $v : $FORM->{$k} = $v;
                        }
                }
        }
}

my $goback;
if (!$ENV{HTTP_REFERER}) {
        $goback = "/schedule/";
} elsif ($ENV{HTTP_REFERER} !~ /manage_user.pl/) {
        $goback = $ENV{HTTP_REFERER};
} else {
        if ($FORM->{referer}) {
                $goback = $FORM->{referer};
        } else {
                $goback = "/schedule/"; 
        }
}

if ($FORM->{'SUB'} eq 'Save') {
        $FORM->{email}      = $query->param('email') || '';
        $FORM->{password}   = $query->param('password') || '';
        $FORM->{derby_name} = $query->param('derby_name') || '';
        $FORM->{real_name}  = $query->param('real_name') || '';
        $FORM->{phone}      = $query->param('phone') || '';
        $FORM->{level}      = $query->param('level') || '';
        $FORM->{type}       = $query->param('type') || '';
        $FORM->{RCid}         = $query->param('RCid') || '';
        $FORM->{access}                 = $query->param('access') || 0;
        if (defined $query->param('clinic_pass')) {
                $FORM->{clinic_pass} = 1;
        } else {
                $FORM->{clinic_pass} = 0;
        }
        
#       $FORM->{clinic_pass}                    = exists $query->param('clinic_pass') ? 1 : 0;
        
        if ($FORM->{RCid} eq "New") {
                if (!$FORM->{password})   { push @ERRORS, "Blank Password!"; }
                if (!$FORM->{real_name})  { push @ERRORS, "Blank Real Name!"; }
                if (!$FORM->{derby_name}) { $FORM->{derby_name} = $FORM->{real_name}; } # If they leave derby_name blank, use their real_name
                if (checkDupes('derby_name', $FORM->{derby_name})) { push @ERRORS, "Derby Name already in use. Pick a different one."; $FORM->{derby_name} = ""; }
                if (!$FORM->{level})      { $FORM->{level} = "B"; } # People keep leaving level blank.  Default 'em if they do.
                if (!$FORM->{type})       { $FORM->{type} = "official"; } # and now they left the other drop-down blank!!!
                if (!$FORM->{email})      { push @ERRORS, "Blank Email (User-ID)!"; } else {
                        $FORM->{email} =~ s/\s+//g; # make sure people aren't accidentally including spaces
                        $FORM->{email} = lc $FORM->{email}; # sometimes people capitalize their email addresses and that's annoying...
                        if (! Email::Valid->address(-address => $FORM->{email}, -mxcheck => 1, -tldcheck => 1)) { push @ERRORS, "Mal-formatted (or fake) Email Address!"; $FORM->{email} = ""; }
                }
                if (checkDupes('email', $FORM->{email})) { push @ERRORS, "Email Address already in use. Pick a different one."; $FORM->{email} = ""; }

                if (scalar @ERRORS) {
                        $ERRMSG = join "<br>", @ERRORS;
                        $FORM->{'SUB'} = 'New User';
                } else {
                        # We have a correctly formatted email address with a mail host record, go ahead and add the user
                        my $sth = $dbh->prepare("insert into official (email, password, derby_name, real_name, phone, level, type, access, clinic_pass) values (?, password(?), ?, ?, ?, ?, ?, ?, ?)");
                        
                        $sth->execute($FORM->{email}, $FORM->{password}, $FORM->{derby_name}, $FORM->{real_name}, $FORM->{phone}, $FORM->{level}, $FORM->{type}, 0, 0);
                        
                        $sth = $dbh->prepare("select RCid from official where email = ?");
                        $sth->execute($FORM->{email});
                        ($FORM->{RCid}) = $sth->fetchrow();
                        logit($FORM->{RCid}, "New User Registration");
                        sendEMail("New User", $FORM);
                        $cookie_string = authenticate(1);                       
                }
        } else {
                $cookie_string = authenticate(1);
                my ($EM, $PWD, $AL) = split /&/, $cookie_string;
                if (lc($EM) eq lc($FORM->{email})) { # They're editing their own record.
                        
                        # Don't let users change their own clinic_pass setting...
                  $FORM->{clinic_pass} = getUser($EM)->{clinic_pass};
#                 $FORM->{clinic_pass} = $RollerCon::ORCUSER->{clinic_pass};
                  
                        if ($FORM->{password}) { # They've possibly included an updated password.
                                my $sth = $dbh->prepare("replace into official (RCid, email, password, derby_name, real_name, phone, level, type, access, clinic_pass) values (?, ?, password(?), ?, ?, ?, ?, ?, ?, ?)");
                                $sth->execute($FORM->{RCid}, $EM, $FORM->{password}, $FORM->{derby_name}, $FORM->{real_name}, $FORM->{phone}, $FORM->{level}, $FORM->{type}, $FORM->{access}, $FORM->{clinic_pass})
                                        or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
                        } else { # No password was included, just keep the existing one.
                                my $sth = $dbh->prepare("replace into official (RCid, email, password, derby_name, real_name, phone, level, type, access, clinic_pass) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                                $sth->execute($FORM->{RCid}, $EM, $PWD, $FORM->{derby_name}, $FORM->{real_name}, $FORM->{phone}, $FORM->{level}, $FORM->{type}, $FORM->{access}, $FORM->{clinic_pass})
                                        or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
                        }
                        if ($ERRMSG) {
                                logit($FORM->{RCid}, "DB ERROR: Updating Self Details: $ERRMSG");
                        } else {
                                logit($FORM->{RCid}, "Updated User Details");
                        }
                } elsif ($AL > 1) { # A lead or higher is updating someone else's record
                        if ($FORM->{password}) {
                                my $sth = $dbh->prepare("replace into official (RCid, email, password, derby_name, real_name, phone, level, type, access, clinic_pass) values (?, ?, password(?), ?, ?, ?, ?, ?, ?, ?)");
                                $sth->execute($FORM->{RCid}, $FORM->{email}, $FORM->{password}, $FORM->{derby_name}, $FORM->{real_name}, $FORM->{phone}, $FORM->{level}, $FORM->{type}, $FORM->{access}, $FORM->{clinic_pass})
                                        or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
                        } else {
                                my $sth = $dbh->prepare("update official set email = ?, derby_name = ?, real_name = ?, phone = ?, level = ?, type = ?, access = ?, clinic_pass = ? where RCid = ?");
                                $sth->execute($FORM->{email}, $FORM->{derby_name}, $FORM->{real_name}, $FORM->{phone}, $FORM->{level}, $FORM->{type}, $FORM->{access}, $FORM->{clinic_pass}, $FORM->{RCid})
                                        or $ERRMSG = "ERROR: Can't execute SQL statement: ".$sth->errstr()."\n";
                        }
                        if ($ERRMSG) {
                                logit($FORM->{RCid}, "DB ERROR: Updating Someone Else: $ERRMSG");
                        } else {
                                logit($FORM->{RCid}, "Updated User Details (by ".getUser($EM)->{derby_name}.")");
                                logit(getUser($EM)->{RCid}, "Updated User Details: ".$FORM->{derby_name}." (".$FORM->{RCid}.")");
                        }
                } else {
                        $ERRMSG = "Attempting to update someone else's record, and you don't have permission to do that.";
                        logit($FORM->{RCid}, "FAIL: $EM doesn't have access to update $FORM->{email}'s record");
                }
        }
        $FORM->{password} = "*******";
        $FORM->{buttons}                = "<INPUT type=hidden name=RCid value=$FORM->{RCid}><INPUT TYPE=submit name=submit value=Edit>";
        my $checked = $FORM->{clinic_pass} ? "checked" : "";
        $FORM->{clinic_pass}    = "<INPUT type='checkbox' name='clinic_pass' $checked disabled readonly>";
        
}

if ($FORM->{'SUB'} eq 'Edit') {
        $cookie_string = authenticate(1);
        my ($EM, $PWD, $AL) = split /&/, $cookie_string;
        my $sth = $dbh->prepare("select * from official where RCid = ?");
        $sth->execute($FORM->{RCid});
        $FORM = $sth->fetchrow_hashref();
        if (lc($EM) eq lc($FORM->{email}) or $AL > 1) {
                if (lc($EM) eq lc($FORM->{email}) or $AL < $FORM->{access}) {
                        $FORM->{access}                 = "<INPUT TYPE=hidden NAME=access VALUE='$FORM->{access}'>$FORM->{access}";
                } else {
                        $FORM->{access}                 = "<SELECT NAME=access>".selectOptions($FORM->{access}, [-1..$AL])."</SELECT>";                 
                }
                my $checked = $FORM->{clinic_pass} ? "checked" : "";
                if ($AL > 2) {
                        $FORM->{clinic_pass} = "<INPUT type='checkbox' name='clinic_pass' value=$FORM->{clinic_pass} $checked>";
                } else {
                        $FORM->{clinic_pass} = "<INPUT type='checkbox' name='clinic_pass' $checked disabled readonly>";
                }
                $FORM->{email}      = "<INPUT TYPE=text NAME=email VALUE='$FORM->{email}' readonly>";
                $FORM->{password}   = "<INPUT TYPE=password NAME=password VALUE=''>";
                $FORM->{derby_name} = "<INPUT TYPE=text NAME=derby_name VALUE=\"$FORM->{derby_name}\">";
                $FORM->{real_name}  = "<INPUT TYPE=text NAME=real_name VALUE='$FORM->{real_name}'>";
                $FORM->{phone}      = "<INPUT TYPE=text NAME=phone VALUE='$FORM->{phone}'>";
                $FORM->{level}      = "<SELECT NAME=level>".selectOptions($FORM->{level}, [qw(AA A B C)])."</SELECT>";
                $FORM->{type}       = "<SELECT NAME=type>".selectOptions($FORM->{type}, [qw(official nso referee)])."</SELECT>";
                $FORM->{RCid}         = "<INPUT TYPE=hidden NAME=RCid VALUE='$FORM->{RCid}'>$FORM->{RCid}&nbsp;";
                $FORM->{buttons}                = "<INPUT TYPE=submit name=submit value=Save> <INPUT TYPE=reset value=Reset> <INPUT TYPE=submit name=submit value=Cancel>";
        } else {
                $ERRMSG = "Attempting to update someone else's record, and you don't have permission to do that.";
        }
        
} elsif ($FORM->{'SUB'} eq 'New User') {
        # Skip authentication
        $FORM->{email}      = "<INPUT TYPE=text NAME=email VALUE='$FORM->{email}'>";
        $FORM->{password}   = "<INPUT TYPE=password NAME=password VALUE=''>";
        $FORM->{derby_name} = "<INPUT TYPE=text NAME=derby_name VALUE='$FORM->{derby_name}'>";
        $FORM->{real_name}  = "<INPUT TYPE=text NAME=real_name VALUE='$FORM->{real_name}'>";
        $FORM->{phone}      = "<INPUT TYPE=text NAME=phone VALUE='$FORM->{phone}'>";
        $FORM->{level}      = "<SELECT NAME=level>".selectOptions($FORM->{level}, ["", qw(AA A B C)])."</SELECT>";
        $FORM->{type}       = "<SELECT NAME=type>".selectOptions($FORM->{type}, ["", qw(official nso referee)])."</SELECT>";
        $FORM->{RCid}         = "<INPUT TYPE=hidden NAME=RCid VALUE='New'>TBD&nbsp;";
        $FORM->{access}                 = "<INPUT TYPE=hidden NAME=access VALUE='0'>0";
        $FORM->{clinic_pass}    = "<INPUT type='checkbox' name='clinic_pass' disabled readonly>";
        $FORM->{buttons}                = "<INPUT TYPE=submit name=submit value=Save> <INPUT TYPE=reset value=Reset> <INPUT TYPE=submit name=submit value=Cancel>";
        $cookie_string = '';
} elsif ($FORM->{'SUB'} eq 'View' or $FORM->{'SUB'} eq 'Cancel' or $FORM->{'SUB'} eq '') {
        $cookie_string = authenticate(1);
        my ($EM, $PWD, $AL) = split /&/, $cookie_string;
        
        if ($FORM->{'SUB'} eq '') {
                my $sth = $dbh->prepare("select RCid from official where email = ?");
                $sth->execute($EM);
                ($FORM->{'RCid'}) = $sth->fetchrow;
        }
        
        # Check to make sure they're only looking up their own ID unless they're a lead or higher
        my $currentuser = getUser($EM);
        if ($currentuser->{RCid} ne $FORM->{RCid} and $AL < 2) {
          logit($currentuser->{RCid}, "SECURITY: $currentuser->{derby_name} attempted to view another user's ($FORM->{RCid}) info");
        $FORM->{email}      = "&nbsp;";
        $FORM->{password}   = "&nbsp;";
        $FORM->{derby_name} = "&nbsp;";
        $FORM->{real_name}  = "&nbsp;";
        $FORM->{phone}      = "&nbsp;";
        $FORM->{level}      = "&nbsp;";
        $FORM->{type}       = "&nbsp;";
        $FORM->{RCid}         = "&nbsp;";
        $FORM->{access}                 = "&nbsp;";
        $FORM->{clinic_pass}    = "&nbsp;";
        $FORM->{buttons}                = "&nbsp;";
        } else {
        my $sth = $dbh->prepare("select * from official where RCid = ?");
        $sth->execute($FORM->{'RCid'});
        $FORM = $sth->fetchrow_hashref();
        
        $FORM->{'password'} = "*******";
        my $checked = $FORM->{clinic_pass} ? "checked" : "";
        $FORM->{clinic_pass}    = "<INPUT type='checkbox' name='clinic_pass' value=$FORM->{clinic_pass} $checked disabled readonly>";
  }
  
        if (lc($EM) eq lc($FORM->{email}) || $AL > 1) {
                $FORM->{buttons} = "<INPUT TYPE=hidden name=RCid value=$FORM->{'RCid'}><INPUT TYPE=submit name=submit value=Edit>";
        } else {
                $FORM->{buttons} = "";
        }
} #else {
#       $cookie_string = authenticate(1);
#       $FORM->{email}      = "&nbsp;";
#       $FORM->{password}   = "&nbsp;";
#       $FORM->{derby_name} = "&nbsp;";
#       $FORM->{real_name}  = "&nbsp;";
#       $FORM->{phone}      = "&nbsp;";
#       $FORM->{level}      = "&nbsp;";
#       $FORM->{type}       = "&nbsp;";
#       $FORM->{RCid}         = "&nbsp;";
#       $FORM->{access}                 = "&nbsp;";
#       $FORM->{clinic_pass}    = "&nbsp;";
#       $FORM->{buttons}                = "&nbsp;";
#}


my $RCAUTH_cookie = CGI::Cookie->new(-name=>'RCAUTH',-value=>"$cookie_string",-expires=>"+30m");

print CGI::header(-cookie=>$RCAUTH_cookie);

foreach (keys %ENV) {
#       print "$_: $ENV{$_}\n<br>";
}

if ($ERRMSG) {
        $ERRMSG = "<TR><TD colspan=2><FONT color=red><B>".$ERRMSG."</B></FONT></TD></TR>";
} else {
        $ERRMSG = "";
}

printRCHeader("User Manager");
print<<body;
$ERRMSG
<form action="$ENV{SCRIPT_NAME}" method=POST name=Req>
<input type=hidden name=referer value="$goback">
        <TR><TD align=right colspan=2>&nbsp;</TD></TR>
        <TR>
                <TD align=right>User-ID / Email Address: </TD>
                <TD align=left>$FORM->{email}</TD>
        </TR>
        <TR>
                <TD align=right>Password: </TD>
                <TD align=left>$FORM->{password}</TD>
        </TR>
        <TR>
                <TD align=right>Derby Name: </TD>
                <TD align=left>$FORM->{derby_name}</TD>
        </TR>
        <TR>
                <TD align=right>Real Name: </TD>
                <TD align=left>$FORM->{real_name}</TD>
        </TR>
        <TR>
                <TD align=right>Phone: </TD>
                <TD align=left>$FORM->{phone}</TD>
        </TR>
        <TR>
                <TD align=right>Experience Level: </TD>
                <TD align=left>$FORM->{level}</TD>
        </TR>
        <TR>
                <TD align=right>type: </TD>
                <TD align=left>$FORM->{type}</TD>
        </TR>
        <TR>
                <TD align=right>Database ID: </TD>
                <TD align=left>$FORM->{RCid}</TD>
        </TR>
        <TR>
                <TD align=right>Access Level: </TD>
                <TD align=left>$FORM->{access}</TD>
        </TR>
        <TR>
                <TD align=right>Clinic Pass: </TD>
                <TD align=left>$FORM->{clinic_pass}</TD>
        </TR>
        <TR><TD colspan=2>&nbsp</TD></TR>
        <TR>
                <TD align=right><A HREF="/schedule/index.pl">[go home]</a>&nbsp<A HREF="$goback">[go back]</a></TD>
                <TD align=left>$FORM->{buttons}</TD>
        </TR>
</FORM>
</TABLE>

body


sub selectOptions {
        my $selectedOption = shift;
        my $options = shift;
        return join " ", map { $selectedOption eq $_ ?
                                                                                                        "<OPTION value='$_' selected>$_</OPTION>" :
                                                                                                        "<OPTION value='$_'>$_</OPTION>"
                                                                                        } @$options;
}

sub sendEMail {
        my $context = shift;
        my $data = shift;
        use RCMailer;

        my $email = $data->{email};
        my $subject = 'Officiating RollerCon Schedule Manager - New User Request';
        my $body = "Greetings,

It appears as though you've registered a new account to Officiate at RollerCon with the following information:

                Derby Name: $data->{derby_name} 
                Real Name:      $data->{real_name}
                Email Address: $data->{email}
                Phone: $data->{phone}
                Type: $data->{type}
                Level:  $data->{level}

Please be patient while our Admins are reviewing your account request.  Each user is manually approved to help ensure robots, spiders, and shift hoggers don't get in.

YOU WILL NOT BE ABLE TO LOG IN UNTIL YOU RECEIVE ANOTHER EMAIL STATING YOUR ACCOUNT REQUEST HAS BEEN APPROVED!

Once approved, you'll be able to log in and view the schedule and sign up for shifts.  Please be considerate of others and don't hogger all of the shifts.  If you do, we will find you and randomly drop your shifts.

http://officials.rollercon.com/schedule/

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.

-RollerCon Officiating Management
";
        # send the message
        EmailUser($email, $subject, $body);

}

sub checkDupes {
  my $field = shift;
  my $nametocheck = shift;
  my $han = $dbh->prepare("select RCid from official where $field = ?");
  $han->execute($nametocheck);
  my ($rcid) = $han->fetchrow();
  return $rcid;
}