#!/usr/bin/perl -w

#####################################################################
# exh_import.plx
# By Brad Smithart (AKA Impossible Robot)
#
# $Revision: 1.1 $
#
# Description:
# Imports Exhibitor information from tab-delimited text
# file into exh_search.cgi database tables.
# Import file contains header row and data rows consisting of
# a 'company' column followed by various optional columns
# and any number of 'booth' columns.
# 
#####################################################################

# Load Modules
use strict;
use DBI;
use Getopt::Std;

my (%opts, @insert_fields);

getopts('cwfh', \%opts);

# Get arguments
unless (@ARGV > 1) # Check for correct number of arguments
{
	my $program;
	($program = $0) =~ s!.*[/\\]!!; # Could use File::Basename
	print <<"EOP";

Usage: $program [options] [input file] [database name]
         Imports exhibitor data into exhibit search
         database from tab-delimited text file.
       Options:
         -c Force case conversion (to title-case)
         -w Import URL data ('showurl' and 'web' fields)
         -f Import data using column headers for fields
         -h Expanded help
       (-w and -f options cannot be used together.)
EOP
	print <<"EOP" if $opts{'h'};

       Sample Import File Structures:
       ==============================
        Without '-w':
        +-------+-----+-----+-----+
        |company|booth|booth|booth|
        +-------+-----+-----+-----+
        |ABC Co.|1001 |2001 |     |
        +-------+-----+-----+-----+
        |DEF Co.|1002 |     |     |
        +-------+-----+-----+-----+

        With '-w':
        +-------+-------+-------------+-----+-----+-----+
        |company|showurl|web          |booth|booth|booth|
        +-------+-------+-------------+-----+-----+-----+
        |ABC Co.|1      |www.abcco.com|1001 |2001 |     |
        +-------+-------+-------------+-----+-----+-----+
        |DEF Co.|1      |www.defco.com|1002 |     |     |
        +-------+-------+-------------+-----+-----+-----+
		
        With '-f':
        Uses column header names for field names; 'booth'
        fields must be in final columns.
EOP
	exit;
}

die "\n**Error: '-w' and '-f' options cannot be used together.\n" 
	if ($opts{'w'} && $opts{'f'});

my ($input_file, $database) = @ARGV; # Assign arguments to variables

# Define Variables
my $company_count = 0;

# Database Variables
my $dsn = "DBI:mysql:$database:localhost";
my $username = 'username';

my $enc_passwd = q{(<&%S<W=O>F0`}; # Not encrypted, just obscured
my $passwd = unpack("u", $enc_passwd);

my $exhibit_table = "exhibitor";
my $booth_table = "booth";

# Connect to database
my $dbh = DBI->connect($dsn, $username, $passwd, { RaiseError => 1 });

# Print program identifier
my $opt_text  = $opts{'c'} ? '(with case conversion)' : '';
   $opt_text .= $opts{'w'} ? '(with URL import)' : '';
   $opt_text .= $opts{'f'} ? '(using field names from header)' : '';
   
print "Exhibitor Import $opt_text\n";

# Open file
open(INPUT, "$input_file")
	or die "Couldn't open import file: $input_file\n";

my $header = <INPUT>;

if ($opts{'f'})
{
	# Validate header 
	$header =~ /^company/
		or die "Invalid data - first column must be 'company'\n";
	$header =~ /\tbooth/
		or die "Invalid data - must contain 'booth' column\n";
	# Remove booth fields, then get insert fields
	my ($fields) = split(/\tbooth/, $header);
	@insert_fields = split(/\t/, $fields);
	# I should probably also validate insert fields against database
}
else
{
	@insert_fields = qw( company );
	push(@insert_fields, qw( showurl web )) if $opts{'w'};
}

# Setup queries
my $sth_LastID = $dbh->prepare('SELECT LAST_INSERT_ID()');

my $insert_fields = join(',', @insert_fields);
my $placeholders = join(',', map {'?'} @insert_fields);

my $query_InsExh = <<"EOQ";
    INSERT INTO $exhibit_table ( $insert_fields )
    VALUES ( $placeholders )
EOQ
my $sth_InsExh = $dbh->prepare($query_InsExh);

my $query_InsBth = <<"EOQ";
    INSERT INTO $booth_table ( booth_number, exhibitor_id )
    VALUES ( ?, ? )
EOQ
my $sth_InsBth = $dbh->prepare($query_InsBth);


while (<INPUT>)
{
	my (%data, @booths, @values);
	s/\"//g; # Remove all quotes
	(@data{@insert_fields}, @booths) = split(/\t/, $_);
	if (exists $data{'web'} && $data{'web'})
	{
		$data{'web'} = 'http://'.$data{'web'}
			unless $data{'web'} =~ /^http\:/;
	}
	
	# Convert each word to ucfirst case
	$data{'company'} =~ s/(\w+)/\u\L$1/g if $opts{'c'};
	
	$sth_InsExh->execute( @data{@insert_fields} ) && $company_count++;
	$sth_LastID->execute;
	my ($exhibitor_id) = $sth_LastID->fetchrow_array;
	foreach my $booth (@booths)
	{
		if ($booth =~ /\w+/)
		{
			$sth_InsBth->execute($booth, $exhibitor_id);
		}
	}
}

# Disconnect from database
$sth_LastID->finish;
$dbh->disconnect;
print "----------\n$company_count companies inserted\n";

exit;