Subversion Repositories VORC

#!/usr/bin/perl

# Redirect error messages to a log of my choosing. (it's annoying to filter for errors in the shared env)
my $error_log_path = $ENV{SERVER_NAME} eq "volunteers.rollercon.com" ? "/home3/rollerco/logs/" : "/tmp/";
close STDERR;
open STDERR, '>>', $error_log_path.'vorc_error.log' or warn "Failed to open redirected logfile ($0): $!";
#warn "Redirecting errors to ${error_log_path}vorc_error.log";

use strict;
use cPanelUserConfig;
use RollerCon;
use HTML::Tiny;
use CGI qw/param header start_html url uploadInfo/;
my $h = HTML::Tiny->new( mode => 'html' );

my $cookie_string = authenticate (5) || die;
my ($EML, $PWD, $LVL) = split /&/, $cookie_string;
my $ID = getUser ($EML);
my $RCAUTH_cookie = cookie (-name=>'RCAUTH',-value=>"$cookie_string",-expires=>"+30m");

my $tmpdir = "/tmp/";


print header (-cookie=>$RCAUTH_cookie),
                        start_html (-title => "Bulk Upload vORC Data - MVP Passes", -style => {'src' => "/style.css"} );

if (defined (param ("uploadedfile"))) {
        process_form ();
} else {
        display_upload_form ();
}

print end_html ();

use CGI qw(:standard escape escapeHTML);

sub display_upload_form  {
  print $h->div ("Upload a CSV File of MVP Passholders...");
  print $h->div ("These should be the columns: (event_name, order_date, ticket_type, id, first_name, last_name, email, derby_name)");
        print start_multipart_form (-action => url ()),
#                   "Upload File: ", br (),
      $h->input ({
        name => "uploadedfile",
        class => "inputfile",
        type => "file",
        id   => "file",
        size => 60
      }) . $h->label ({ for=>"file", class=>"top" }, $h->span ("Choose File...")),
        br (), br (),
                    submit (-name => "choice", -value => "Submit"), $h->input ({ type=>"button", value => "Cancel" , onClick=>"history.back(); return false;" }),
                    end_form ();
                    printJavascript ();
}

sub printJavascript {
    print<<JSCRIPT;
  
<SCRIPT language="JavaScript">
<!--

var inputs = document.querySelectorAll( '.inputfile' );
Array.prototype.forEach.call( inputs, function( input )
{
        var label        = input.nextElementSibling,
                labelVal = label.innerHTML;

        input.addEventListener( 'change', function( e )
        {
                var fileName = e.target.value.split( '\\\\' ).pop();
                if( fileName )
                        label.querySelector( 'span' ).innerHTML = fileName;
                else
                        label.innerHTML = labelVal;
        });
});

//-->
</SCRIPT>


JSCRIPT
}

sub process_form  {
  use WebDB;
  my $dbh = WebDB::connect ();
  
        my $uploadedfile = param ("uploadedfile");
        my @errors = ();
  my $mime_type;
  my $serve_url;
  
  logit ($ID->{RCid}, "Bulk Uploaded MVP Passes from file $uploadedfile");

  push (@errors, "Please specify a file") if $uploadedfile eq "";
  $mime_type = uploadInfo ($uploadedfile)->{'Content-Type'};
  if ($mime_type ne "text/csv") {
    push @errors, "Expecting a CSV file, but received a '$mime_type'.";
  }
  if (@errors)   {
        print p ("The following errors occurred:");
        print ul (li (\@errors));
        print p ("Please click your Browser's Back button to\n"
                                   . "return to the previous page and correct the problem.");
        return;
  }      # Form was okay;
  
  print $h->div ("Processing file...");
  my $fh = upload ('uploadedfile');

  my @columnlabels = split /,/, <$fh>;
  @columnlabels = map { s/[^\x00-\x7f]//g; WebDB::trim ($_) } @columnlabels;
  pop @columnlabels;
    
  my $idtrue = 0;
  if ($columnlabels[0] eq "id") {
    $idtrue = 1;
    shift @columnlabels;
  }
  
  my @AcceptableColumns = qw(event_name order_date ticket_type id first_name last_name email derby_name);
  @columnlabels = @AcceptableColumns;

  my $fields = join ", ", @columnlabels;
  my $values = join ", ", map { '?' } 0..$#columnlabels;
  my $sth = $dbh->prepare ("replace into ticket ($fields) values ($values)");
  
  while (<$fh>) {
    chomp;
    my @R = map { s/[^\x00-\x7f]//g; WebDB::trim ($_) } split /,/;
    shift @R if $idtrue;
    
    # Fix date format for MySQL: 11/23/2022 15:49
    if ($R[1] !~ /\d{4}-\d{2}-\d{2} \d{1,2}:\d{2}/) {
      my ($date, $time) = split / /, $R[1];
      my ($m, $d, $y) = split /\//, $date;
      $R[1] = "$y-$m-$d $time";
    }
    
    pop @R if scalar @R > scalar @AcceptableColumns;
    push @R, "" if (scalar @R < scalar @columnlabels and eof);
    print "inserting: ", join (" ", @R), $h->br;
    $sth->execute (map { defined $_ ? $_ : undef } @R) or print $sth->errstr;
  }
  print "DONE!", $h->br, $h->br;
  
  print "Updating RCid -> MVP Ticket links... ";
  $dbh->do ("replace into RCid_ticket_link select official.RCid, id, year(now()) from official join v_ticket on official.email = v_ticket.email and official.real_name = v_ticket.full_name");
  print "DONE!", $h->br, $h->br;
  
        $dbh->disconnect ();     # Image was stored into database successfully.  Present confirmation
        
        # Display link to main page so user can upload another image
        print $h->hr, $h->a ({ href => url }, "[Upload another file]"), $h->a ({ href => "/schedule/" }, "[Home]");
}


sub error {
        my $msg = shift;
        print p (escapeHTML ("Error: $msg")), end_html ();
        exit (0);
}