#!/usr/bin/perl
# Script to convert Quickbooks text dump to a SQL dump file.
# Written By: webb (08/15/2002)

use strict;

## Configuration stuff whoop-ti-doo

my $qbfile = $ARGV[0];	# QuickBooks Dump File

## Lets Start this Show

## This Gets the information from the qbfile
if (($qbfile) && (-e $qbfile)) {

my @qbline = `cat $qbfile`;

## This will start the looping stuff and parse the file

for (my $i=0; $i<@qbline; $i++) {

 my $prepare;
 my $numb;
 chomp($qbline[$i]);
 $qbline[$i] =~ s/\r//g;
 $qbline[$i] =~ s/\n//g;
 my @qbinfo = split(/\t/, $qbline[$i]);

## This will make a table if the table name (first param of qbfile) has a !

 if ($qbinfo[0] =~ /!/) {
 
  $qbinfo[0] =~ s/!//;
  $qbinfo[0] =~ s/\ /_/g;
  $prepare = "\nCREATE TABLE IF NOT EXISTS \L$qbinfo[0]\E (\n\t";
  $prepare .= "numb bigint(20) NOT NULL autoincrement";
  my %stuff;
  for (my $j=1; $j<@qbinfo; $j++) {
  $qbinfo[$j] =~ s/\ /_/g;

## This stuff is needed in case the tablename happens to be a mysql function

   if ($qbinfo[$j] eq "DESC") {
    $qbinfo[$j] = "DESCRIPTION";
   }
   if ($qbinfo[$j] eq "LIMIT") {
    $qbinfo[$j] = "LIMITATION";
   }
   if ($qbinfo[$j] eq "LABEL") {
    $qbinfo[$j] = "LABELNAME";
   }
   if ($qbinfo[$j] eq "INDEX") {
    $qbinfo[$j] = "INDEXNAME";
   }
   if (!($qbinfo[$j] =~ /\D/)) {
    $qbinfo[$j] = "E$qbinfo[$j]"
   }
   $prepare .= ",\n\t";
   if ($stuff{$qbinfo[$j]}) {
    $numb = $stuff{$qbinfo[$j]} + 1;
    $prepare .= "\L$qbinfo[$j]\E$numb text";
   }
   else {
    $prepare .= "\L$qbinfo[$j]\E text";
   }
   $stuff{$qbinfo[$j]}++;
  }

  $prepare .= ",\n\tPRIMARY KEY (numb),\n\tUNIQUE numb (numb)\n)\;\n";

 }

## This will put the information into the table name if the first param doesn't have a !

 else {
  $qbinfo[0] =~ s/\ /_/g;
  $prepare = "INSERT INTO \L$qbinfo[0]\E VALUES ('0'"; 

  for (my $m=1; $m<@qbinfo; $m++) {
   $qbinfo[$m] =~ s/\'/\\\'/g;
   $qbinfo[$m] =~ s/\"//g;
   $prepare .= ", ";
   $prepare .= "\'$qbinfo[$m]\'";

  }

  $prepare .= " )\;";

 }

print "$prepare\n";

}

} else {
  print "Usage: $ENV{_} <inputfile>\ni.e. $ENV{_} mydata.txt > mydump.sql\n\n"
}


## Thus ends the script