#!/usr/bin/perl

$| = 1;
print "Content-type: text/html\n\n";

#configure DB connection info on about line 179


########################################
# important notes about using the script
########################################

# the script takes a table name on the first line
# then a list of fields (tab delimited) on the second.
# then each following line has a record (tab delimited) on it.

# a blank line will reset, and the script will again look for
# a table and field list.

# integer or unquoted fields must be named
# with an i_ at their start in order for the script to format
# the SQl correctly.

# EXISTING DATA IN ANY TABLE WILL BE ERASED BEFORE IMPORT!!!!!!!!!!!!!



# The file upload will not work with some web server configurations.
# If you'd like help troubleshooting this and have some control over
# your server, drop me an email at nathan@ncyoung.com
			
# If you use this script and like it, please let me know!
# If you re-distribute this script, please include the following credits:

#			Copywrite 2000 to Nathan Young - nathan@ncyoung.com



#the script uses the following libraries.
use DBI;
use CGI qw(:cgi-lib);

#parse incoming form info.
ReadParse(*incame);
$web = $incame{'CGI'};


$scripturl = $web->url;
$import_file = "./import.txt";

unless (($incame{'initial'})||($incame{'final'})){

	print qq!<H3 ALIGN="center">Use the button below to upload the file.</H3>!;	
	print $web->start_multipart_form(-method=>'post',-action => $scripturl);
	print "<CENTER><BR>";
	print $web->filefield(-name=>'file',
						-default=>'',
						-size=>20,
						-maxlength=>80);
	print $web->submit(-name => "initial", -value => "Upload");
	print "</FORM>";
	exit;
}


if ($file = $web->param("file")){

	open (OUTFILE,">$import_file");
	while ($bytesread=read($file,$buffer,1024)) {
		print OUTFILE $buffer;
	}
	close $file;
	close OUTFILE;
}


#open the import file
unless (open IN,$import_file){
	&header;
	print "Couldn't open the import file, $import_file";
	&footer;
	exit;
}

$line .= <IN>;
$line .= <IN>;
$line .= <IN>;

$test = chr(10);
if ($line =~ m/$test/s){
	$file_type = "unix";
	$sep = $test;
}
$test = chr(13);
if ($line =~ m/$test/s){
	$file_type = "mac";
	$sep = $test;
}
$test = chr(13).chr(10);
if ($line =~ m/$test/s){
	$file_type = "pc";
	$sep = $test;
}

print "file is a $file_type file.\n";
$save = $/;
$/ = $sep;

close IN;
open IN,$import_file;




if ($incame{'initial'}){

	#this is the flag that causes the first two lines
	#to be read as the table and field list type.
	$start_batch = 1;

	print qq!<TABLE BORDER="0">!;

	while ($line = <IN>){
	
		$line =~ s/\s*$//;
		

		if ($start_batch){
			
			print qq!</TABLE><TABLE BORDER="1" WIDTH="100%">!;
			$table = $line;
			$line = <IN>;
			$line =~ s/\s*$//;
			@field_list = split /\t/,$line;
			$line = <IN>;
			$line =~ s/\s*$//;
			$start_batch = "";
			
			print qq!<TR><TD><H3>New Set</TD></TR>!;
			print qq!<TR><TD><B>Table: $table</TD></TR>!;
			$temp = join "</TD><TD>",@field_list;
			print qq!<TR><TD>$temp</TD></TR>!;
			
	
		}
	
		if ($line eq ""){
			
			$start_batch = 1;
			
		}
	
		else {
		
		
			@value = split /\t/,$line;
		
			$temp = join "</TD><TD>",@value;
			print qq!<TR><TD>$temp</TD></TR>!;
			
		}	#end else
	}	#end while	
	
	print "</TABLE>";
	print "<BR><BR>";
	print qq!<FORM ACTION="$scripturl" METHOD="post">!;
	print "Use the button below to make the imports final <BR>";
	print qq!<INPUT TYPE="submit" NAME="final" VALUE="Make the import now"></FORM>!;
	&footer;
	close IN;
	exit;
	
}	#end if


if ($incame{'final'}){
	
	
	$database_name = "my_db";
	$user = "me";
	$pass = "my_pass";
	$database_host = "sql.mydomain.com";
	
	#connect to db
	$db_init_string = qq!dbi:mysql:$database_name:$database_host!;
	$dbh = DBI->connect("$db_init_string", $user, $pass)
			|| die "Can't connect to $database_name: $DBI::errstr";




	#this is the flag that causes the first two lines
	#to be read as the aid and ad type.
	$start_batch = 1;

	while ($line = <IN>){
	
		$line =~ s/\s*$//;
		
		#
		if ($start_batch){
			
			
			$table = $line;
			$line = <IN>;
			$line =~ s/\s*$//;
			@field_list = split /\t/,$line;
			$line = <IN>;
			$line =~ s/\s*$//;
			$start_batch = "";
			$sql = "delete from $table";
			$dbh->do($sql);
			print $dbh->errstr;
			
	
		}
	
		if ($line eq ""){
			
			$start_batch = 1;
		}
	
		else {
		
			%data = ();
			@value = split /\t/,$line;
		
			foreach $index (0..(@field_list-1)){
				if ($value[$index]){
					$value[$index] =~ s/'/&#39;/gs;
					$data{$field_list[$index]} = $value[$index];
				}
			}
			
			@fields = ();
			@values = ();
			
			foreach $key (keys %data){
				
				unless ($key =~ m/^i_/){
					$data{$key} = "\'".$data{$key}."\'";
				}
				push @fields,$key;
				push @values,$data{$key};
			}
			
			$temp = join ", ",@fields;
			$sql = "insert into $table ($temp) values ";
			$temp = join ", ",@values;
			$sql .= "($temp)";
			$dbh->do($sql);
			
			if ($dbh->errstr){
				print $dbh->errstr;
				print "<BR> $sql <BR>";
				&printhash(%data);
				$errors++;
			}
			else {
				print "Record imported<BR>";
				$count++;
			}
			
		}	#end else
	}	#end while
	
	unless ($errors){$errors = "0"}
	
	print "<CENTER>$count records imported.<BR>$errors errors.<BR><BR><B>Done.";

	close IN;
	unlink ($import_file);
}	#end if