# This file will be used to test manipulation of text from 
# Oracle into files and hopefully back again

sub timer {

my ($argument1) = @_;
    system('cls'); # clear the screen
    $now = time();
    print "I am working with $argument1  data and will finish shortly.: \n";
    print "Time now: " . scalar localtime($now) . "\n";
    return 0;
   }


# Open the Log for any errors I may want output.
open LOG, 		">>h:\\test\\error.log" || die "Cannot open error Log: $! ";
print LOG 		"Error Log Started.\n";


# All File names to be put into variable for use in the script.
# I have used the direction pipes here because I will create 
# a seperate Filehandle if I need to open the file in another 
# direction.

$extract	=	">h:\\test\\oracle\\extracted_data.csv";
$edit_1		=	"h:\\test\\oracle\\extracted_data.csv";
$output_1	=	">h:\\test\\oracle\\german_lowercase_categories.csv";
$output_2	=	">h:\\test\\oracle\\Category.csv";
$output_3	=	">>h:\\test\\oracle\\Name_Category.csv";


# Open the Files.  
open(EXTRACT,	"$extract") 	|| die "Cannot open : $! ";
open(EDIT_1,	"$edit_1") 	|| die "Cannot open : $! ";
open(OUTPUT_1,	"$output_1")	|| die "Cannot open : $! ";
open(OUTPUT_2,	"$output_2")	|| die "Cannot open : $! ";
open(OUTPUT_3,	"$output_3")	|| die "Cannot open : $! ";


$country = "csr_de";
timer($country);
	# Open Oracle for GERMAN EXTRACTION
	use DBI;
	my	$dbh = DBI->connect('dbi:Oracle:host=lon;
					sid=sd;
					port=1521', 
	        		   	'',
					'',
					{RaiseError => 1,AutoCommit => 0}
				);

# Start SQL for ORACLE

my	$sth = $dbh->prepare('select circ_path_inst_id, type 
				from circ_path_inst'
				);
	$sth->execute();

print EXTRACT ",Inst ID, Category\n"; 
while (my (@table_name) = $sth->fetchrow_array()) {

	print EXTRACT ",$table_name[0], $table_name[1],\n";

}
	$sth->finish();

close(EXTRACT);

# Now loop through the Input file and parse.
# Variable to be used for counting through the array.
$a = 0;
while (defined($currentLine = <EDIT_1>)) 
	{
	if ($currentLine =~ /^,.*?,\s(.*[a-z].*),.*$/)  
		{
		$category[$a] 	= $1;
		print OUTPUT_1 	"$currentLine";
		++$a;
		}
	}

# Reset Loop variable
$a = undef;

# Sort Duplicate from the array and Count the Lower Case Categories.

undef %sort_hash;
	@sort_hash{@category} = ();
	@category = sort keys %sort_hash;    

# Search for the Categories that where found in the previous script.

my	$sti = $dbh->prepare('select circ_path_hum_id, type 
				from circ_path_inst
				where type = ?'
				);

#-----------------------------------------------------------------------#
# Start to print out to the file to create the headers.			#
# Commas have been used as this will be opened as a CSV file in Excel.	#
# If there was any likelyhood of Commas being in the data this would	#
# cause problems but there should not be.				#
#-----------------------------------------------------------------------#
	print OUTPUT_3 "Country, Path ID, Category\n"; 
	print OUTPUT_3 "Germany,,,\n"; 

foreach $cat (@category) 
	{
	$sti->execute($cat);
	while (my (@next) = $sti->fetchrow_array()) 
		{

		print OUTPUT_3 ",$next[0], $next[1],\n";
		}
	}

# Close the Connection the Database and the Query Handle.
	$sti->finish();
	$dbh->disconnect();
 
# Close the files opened earlier.
	close(EXTRACT);
	close(EDIT_1);
	close(OUTPUT_1);
	close(OUTPUT_2);
	close(OUTPUT_3);
exit 0;