#!/usr/bin/perl
use DBI;

print "\nWelcome to the GnuLedger import utility. This utility is for 0.3.xx series\n";
print "versions of GnuLedger only.  Please respond to the following prompts to\n";
print "import your tables.\n\n";

print "MySQL user [root]: ";
$db_user = <STDIN>;
chomp($db_user);
if (!$db_user){
	$db_user = 'root';
}

print "$db_user" . "'s password: ";
$db_pass = <STDIN>;
chomp($db_pass);


print "Old financial database [Finance]: ";
$db_name = <STDIN>;
chomp($db_name);
if (!$db_name){
	$db_name = 'Finance';
}


print "New finanial database [New_Finance]: ";
$new_db_name = <STDIN>;
chomp($new_db_name);
if (!$new_db_name){
	$new_db_name = 'New_Finance';
}

print "New administrator [administrator]: ";
$adm_user = <STDIN>;
chomp($adm_user);
if (!$adm_user){
	$adm_user = 'administrator';
}

while ($adm_pass eq ''){
	print "Administrator password: ";
	$adm_pass = <STDIN>;
	chomp($adm_pass);
}
$adm_pass = crypt($adm_pass, ".1");


print "Please ignore any errors below...\n\n";
$old_db = DBI->connect("DBI:mysql:$db_name:localhost",
                                           $db_user,
                                           $db_pass);
$Query = "CREATE DATABASE $new_db_name";
$Cursor = $old_db -> prepare ($Query);
$Cursor -> execute;
$Cursor -> finish;

$new_db = DBI->connect("DBI:mysql:$new_db_name:localhost",
                                           $db_user,
                                           $db_pass);


$Query = "CREATE TABLE AccountList (name varchar(20), parent varchar(20), atype varchar(20), astlia char(1))";
$Cursor = $new_db -> prepare($Query);
$Cursor -> execute;
$Cursor -> finish;

$Query = "CREATE TABLE AccountTypes (type varchar(20))";
$Cursor = $new_db -> prepare($Query);
$Cursor -> execute;
$Cursor -> finish;

$Query = "CREATE TABLE userpriv (user varchar(30), pass varchar(50), adm char(1), rem char(1), ent char(1), edt char(1), accts blob)";
$Cursor = $new_db -> prepare($Query);
$Cursor -> execute;
$Cursor -> finish;


$Query = "SELECT user FROM userpriv WHERE user='$adm_user'";
$Cursor = $new_db -> prepare ($Query);
$Cursor -> execute;
$Response = $Cursor -> fetchrow;
$Cursor -> finish;

if ($Response ne "$adm_user"){
        $Query = "INSERT INTO userpriv (user, pass, adm, rem, ent, edt, accts) values ('$adm_user', '$adm_pass', 'Y', 'Y', 'Y', 'Y', 'ALL')";
        $Cursor = $new_db -> prepare($Query);
        $Cursor -> execute;
        $Cursor -> finish;
}


$Query = "SELECT type FROM AccountTypes WHERE type='Bank'";
$Cursor = $new_db -> prepare ($Query);
$Cursor -> execute;
$Response = $Cursor -> fetchrow;
$Cursor -> finish;

if ($Response ne 'Bank'){
        $Query = "INSERT INTO AccountTypes (type) values ('Bank')";
        $Cursor = $new_db -> prepare($Query);
        $Cursor -> execute;
        $Cursor -> finish;
}

$Query = "SELECT type FROM AccountTypes WHERE type='Credit_Card'";
$Cursor = $new_db -> prepare ($Query);
$Cursor -> execute;
$Response = $Cursor -> fetchrow;
$Cursor -> finish;

if ($Response ne 'Credit_Card'){

        $Query = "INSERT INTO AccountTypes (type) values ('Credit_Card')";
        $Cursor = $new_db -> prepare($Query);
        $Cursor -> execute;
        $Cursor -> finish;
}

$Query = "SELECT type FROM AccountTypes WHERE type='Expense'";
$Cursor = $new_db -> prepare ($Query);
$Cursor -> execute;
$Response = $Cursor -> fetchrow;
$Cursor -> finish;

if ($Response ne 'Expense'){

        $Query = "INSERT INTO AccountTypes (type) values ('Expense')";
        $Cursor = $new_db -> prepare($Query);
        $Cursor -> execute;
        $Cursor -> finish;
}

$Query = "SELECT type FROM AccountTypes WHERE type='Other_Asset'";
$Cursor = $new_db -> prepare ($Query);
$Cursor -> execute;
$Response = $Cursor -> fetchrow;
$Cursor -> finish;

if ($Response ne 'Other_Asset'){

        $Query = "INSERT INTO AccountTypes (type) values ('Other_Asset')";
        $Cursor = $new_db -> prepare($Query);
        $Cursor -> execute;
        $Cursor -> finish;
}

$Query = "SELECT type FROM AccountTypes WHERE type='Other_Liability'";
$Cursor = $new_db -> prepare ($Query);
$Cursor -> execute;
$Response = $Cursor -> fetchrow;
$Cursor -> finish;

if ($Response ne 'Other_Liability'){

        $Query = "INSERT INTO AccountTypes (type) values ('Other_Liability')";
        $Cursor = $new_db -> prepare($Query);
        $Cursor -> execute;
        $Cursor -> finish;
}


print "\n\n";
$import_table = '';
while ($import_table ne 'q'){

	print "Available tables:\n";
	print "-----------------\n\n";

	$Query = "SELECT name FROM AccountList";
	$Cursor = $old_db -> prepare ($Query);
	$Cursor -> execute;

	while ($table_name = $Cursor -> fetchrow){
		print "$table_name\n";
	}
	$Cursor -> finish;

	while ($import_table eq ''){
		print "Table to import [q to quit]: ";
		$import_table = <STDIN>;
		chomp($import_table);
	}

	if ($import_table ne 'q'){
		$Query = "CREATE TABLE $import_table (
					timestmp TIMESTAMP, 
					unid INTEGER, 
					tdate DATE, 
					transnum VARCHAR(100), 
					transfer VARCHAR(100), 
					description VARCHAR(100), 
					credit DECIMAL(9,2), 
					debit DECIMAL(9,2), 
					reconcile enum('Y', 'N'))";

		$Cursor = $new_db -> prepare ($Query);
		$Cursor -> execute;
		$Cursor -> finish;

		$Query = "SELECT atype, astlia FROM AccountList WHERE name='$import_table'";
		$Cursor = $old_db -> prepare ($Query);
		$Cursor -> execute;
		($atype, $astlia) = $Cursor -> fetchrow;
		$Cursor -> finish;

		$Query = "INSERT INTO AccountList (name, parent, atype, astlia) values ('$import_table', 'None', '$atype', '$astlia')";
		$Cursor = $new_db -> prepare ($Query);
		$Cursor -> execute;
		$Cursor -> finish;
		
		$Query = "SELECT timestmp, unid, tdate, transnum, transfer, description, credit, debit FROM $import_table ORDER BY timestmp ASC";
		$Cursor = $old_db -> prepare ($Query);
		$Cursor -> execute;
		while (@Field_Data = $Cursor -> fetchrow){

			($timestmp, $unid, $tdate, $transnum, $transfer, $description, $credit, $debit) = @Field_Data;
			my $Query = "INSERT INTO $import_table (timestmp, unid, tdate, transnum, transfer, description, credit, debit, reconcile) values ('$timestmp', '$unid', '$tdate', '$transnum', '$transfer', '$description', '$credit', '$debit', 'N')";
			$new_Cursor = $new_db -> prepare ($Query);
			$new_Cursor -> execute;
			$new_Cursor -> finish;
		}
		

	}
	$import_table = '';	
}



