Subversion Repositories VORC

#!/usr/bin/perl

$ENV{SERVER_NAME} = "volunteers.rollercon.com";

warn "Server Name: ".$ENV{SERVER_NAME};

use strict;

use cPanelUserConfig;

use RollerCon;

use WebDB;

my $dbh = WebDB::connect ();

my $tmpdir = "/tmp/";

my $DEBUG = 0;

my $inputfile = shift // "";

if ($inputfile) {

	process_file ($inputfile);

} else {

	error ("No input file specified.");

}

$dbh->disconnect ();

sub process_file  {

	my $uploadedfile = shift or error ("No file passed to 'process_file()'");

	my @errors = ();

  my $mime_type;

  my $serve_url;

  logit (0, "Bulk Uploaded MVP Passes from file $uploadedfile");

  print "Processing file [$uploadedfile]...\n" if $DEBUG;

  open(INPUT, $uploadedfile) or error ("Could not open file [$uploadedfile]!");

  my @columnlabels = split /,/, <INPUT>;

  @columnlabels = map { s/[^\x00-\x7f]//g; WebDB::trim ($_) } @columnlabels;

  error ("File not read!") unless scalar @columnlabels;

#  pop @columnlabels;

#  my @AcceptableColumns = qw(event_name order_date ticket_type id first_name last_name email derby_name);

#  @columnlabels = @AcceptableColumns;

  my $fields = join ", ", qw(event_name order_date ticket_type id first_name last_name email derby_name);

  my $values = join ", ", map { '?' } 0..7;

  my $sth = $dbh->prepare ("replace into ticket ($fields) values ($values)");

  while (<INPUT>) {

    chomp;

    my @R = map { s/[^\x00-\x7f]//g; WebDB::trim ($_) } split /,/;

    my %mvpticket;

    @mvpticket{@columnlabels} = @R;

    delete_ticket($mvpticket{'Registration Number'}) and next unless $mvpticket{'Cancelled'} eq "FALSE"; #actually... if a ticket is cancelled, I should delete it and unmatch.

    # Fix date format for MySQL: 11/23/2022 15:49

    if ($mvpticket{'Order Date'} !~ /\d{4}-\d{2}-\d{2} \d{1,2}:\d{2}/) {

      my ($date, $time) = split / /, $mvpticket{'Order Date'};

      my ($m, $d, $y) = split /\//, $date;

      $mvpticket{'Order Date'} = "$y-$m-$d $time";

    }

    print "inserting: ", join (" ", @R), "\n" if $DEBUG;

    $sth->execute ( $mvpticket{'Event Name'}, $mvpticket{'Order Date'}, $mvpticket{'Ticket Type'}, $mvpticket{'Registration Number'}, $mvpticket{'Attendee First Name'}, $mvpticket{'Attendee Last Name'}, $mvpticket{'Attendee Email'}, $mvpticket{'Attendee Skate Name'} ) or error($sth->errstr);

  }

  print "DONE!\n\n" if $DEBUG;

  print "Updating RCid -> MVP Ticket links... " if $DEBUG;

  $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!\n\n" if $DEBUG;

}

sub delete_ticket {

  my $badticket = shift // error ("delete_ticket() called without a target ticket!");

  print "DELETING: ", $badticket, " (ticket cancelled)\n" if $DEBUG;

  $dbh->do ("delete from ticket where id = ?", undef, $badticket);

  $dbh->do ("delete from RCid_ticket_link where year = year(now()) and MVPid = ?", undef, $badticket);

}

sub error {

	my $msg = shift;

	logit (0, "Problem with Bulk MVP Pass upload: ".$msg);

	print "Error: $msg\n";

	exit (1);

}