| 2 |
- |
1 |
#!/usr/bin/perl
|
|
|
2 |
|
|
|
3 |
use strict;
|
|
|
4 |
use PEEPS;
|
|
|
5 |
use WebDB;
|
|
|
6 |
|
|
|
7 |
my $dbh = WebDB::connect ();
|
|
|
8 |
my $DEBUG = 1;
|
|
|
9 |
|
|
|
10 |
my $inputfile = shift // "";
|
|
|
11 |
|
|
|
12 |
if ($inputfile) {
|
|
|
13 |
process_file ($inputfile);
|
|
|
14 |
} else {
|
|
|
15 |
error ("No input file specified.");
|
|
|
16 |
}
|
|
|
17 |
|
|
|
18 |
$dbh->disconnect ();
|
|
|
19 |
|
|
|
20 |
|
|
|
21 |
|
|
|
22 |
sub process_file {
|
|
|
23 |
my $uploadedfile = shift or error ("No file passed to 'process_file()'");
|
|
|
24 |
my @errors = ();
|
|
|
25 |
|
|
|
26 |
logit (0, "Bulk Uploaded PEEPS User Affiliations from file $uploadedfile");
|
|
|
27 |
|
|
|
28 |
print "Processing file [$uploadedfile]...\n" if $DEBUG;
|
|
|
29 |
open(INPUT, $uploadedfile) or error ("Could not open file [$uploadedfile]!");
|
|
|
30 |
|
|
|
31 |
my @columnlabels = split /,/, <INPUT>;
|
|
|
32 |
@columnlabels = map { s/[^\x00-\x7f]//g; WebDB::trim ($_) } @columnlabels;
|
|
|
33 |
error ("File not read!") unless scalar @columnlabels;
|
|
|
34 |
# pop @columnlabels;
|
|
|
35 |
|
|
|
36 |
# my @AcceptableColumns = qw(event_name order_date ticket_type id first_name last_name email derby_name);
|
|
|
37 |
# @columnlabels = @AcceptableColumns;
|
|
|
38 |
|
|
|
39 |
my $fields = join ", ", @columnlabels;
|
|
|
40 |
my $values = join ", ", map { '?' } 0..$#columnlabels;
|
|
|
41 |
my $sth = $dbh->prepare ("replace into role ($fields) values ($values)");
|
|
|
42 |
|
|
|
43 |
while (<INPUT>) {
|
|
|
44 |
chomp;
|
|
|
45 |
|
|
|
46 |
my @R = map { s/[^\x00-\x7f]//g; WebDB::trim ($_) } split /,/;
|
|
|
47 |
|
|
|
48 |
if (scalar @R != scalar @columnlabels) {
|
|
|
49 |
# there's an extra field, likely from commas in the member_org_name...
|
|
|
50 |
my $extra = scalar @R - scalar @columnlabels;
|
|
|
51 |
# print "fixing a derby name with $extra comma(s)...: @R\n";
|
|
|
52 |
$R[2] = join ", ", @R[2..2+$extra];
|
|
|
53 |
splice @R, 3, $extra;
|
|
|
54 |
$R[2] =~ s/"//g;
|
|
|
55 |
# print "now it's @R\n";
|
|
|
56 |
# exit;
|
|
|
57 |
}
|
|
|
58 |
|
|
|
59 |
for my $i (0..$#R) {
|
|
|
60 |
$R[$i] = undef unless $R[$i];
|
|
|
61 |
}
|
|
|
62 |
|
|
|
63 |
($R[2]) = $dbh->selectrow_array ("select id from organization where league_name = ?", undef, $R[2]);
|
|
|
64 |
next unless $R[2];
|
|
|
65 |
|
|
|
66 |
foreach my $role (split /\|/, $R[1]) {
|
|
|
67 |
print "inserting: ", join (" ", $R[0], $role, $R[2]), "\n" if $DEBUG;
|
|
|
68 |
$sth->execute ( $R[0], $role, $R[2] ) or error($sth->errstr);
|
|
|
69 |
}
|
|
|
70 |
}
|
|
|
71 |
print "DONE!\n\n" if $DEBUG;
|
|
|
72 |
}
|
|
|
73 |
|
|
|
74 |
|
|
|
75 |
sub error {
|
|
|
76 |
my $msg = shift;
|
|
|
77 |
logit (0, "Problem with Bulk MVP Pass upload: ".$msg);
|
|
|
78 |
print "Error: $msg\n";
|
|
|
79 |
exit (1);
|
|
|
80 |
}
|
|
|
81 |
|