#!/usr/bin/perl

use strict;
use DBI;
use utilities;     # see below

my @cols = qw/
    Subject
    Body
    FromName
    FromAddress
    FromType
    ToName
    ToAddress
    ToType
    CCName
    CCAddress
    CCType
    BCCName
    BCCAddress
    BCCType
    BillingInformation
    Categories
    Importance
    Mileage
    Sensitivity
/;
chdir 'db';					# or wherever you've put the files
my %insert;
my $dbh = DBI->connect("DBI:mysql:database=jbr", "admin", "1263");
my $csv = DBI->connect("DBI:CSV:") or die "connect" . $DBI::errstr;
my $count = 0;
for (<*>) {
	print "\nstarting $_    . . . .     ";
    my $sth = $csv->prepare("SELECT * FROM $_") or die $DBI::errstr;
    $sth->execute() or die $DBI::errstr;
	my $n;
    while (my @ref = $sth->fetchrow_array) {
        @insert{@cols} = @ref;
        $n = InsertAndGetID($dbh, 'email', \%insert, 'ID');
    }
	print "done  - $n lines so far";
	$count = $n;
    $sth->finish();
}
$dbh->disconnect();
$csv->disconnect();

##</code><code>##

sub
InsertAndGetID
{

#---------------------------------------------------------------
# inserts an entry into a db and gets the auto_increment ID
#---------------------------------------------------------------

    my $dbh = shift;
    my $table = shift;
    my $Inserts = shift;
    my $IDCol = shift;

    $Inserts->{$IDCol} = 'NULL';
    
    $dbh->do("LOCK TABLES $table WRITE") or die $dbh->errstr;

    InsertMultipleValues($dbh,$table,$Inserts);

    $sth = $dbh->prepare("SELECT LAST_INSERT_ID() FROM $table") or die $dbh->errstr;
    $sth->execute or die $dbh->errstr;
    my @ary = $sth->fetchrow_array or die $dbh->errstr;

    $dbh->do("UNLOCK TABLES") or die $dbh->errstr;
    $sth->finish;

    return $ary[0];
}


sub
InsertMultipleValues
{

#---------------------------------------------------------------
# Inserts contents of a hashref into the db table specified
#---------------------------------------------------------------

    my $dbh = shift;
    my $table = shift;
    my $Inserts = shift;

    my @cols = keys %$Inserts;
    my @vals = @$Inserts{@cols};
    my $cols = join ',', @cols;
    my $places = '?,' x @vals;
    chop $places;

    my $sth = $dbh->prepare("INSERT INTO $table ($cols) VALUES ($places)") or die $dbh->errstr;
    $sth->execute(@vals) or die "$dbh->errstr : $table";
}