#!run it like this 'perl qb2oscommerce.pl'

use Win32::ODBC;
use Data::Dumper;

use DBI;

# Debug settings
my $debug = 1;
$Data::Dumper::Indent = 3;
my $count = 0;

# Mysql server settings
#my $mysqldsn = 'DBI:mysql:oscommerce:hostname';
#my $mysqldsn = 'DBI:mysql:oscommrce:localhost';
my $mysqldsn = 'dbi:mysql:oscommerce:localhost:3306';
my $db_user_name = '...';
my $db_password = '...';

# Show items that we dont have stock of?
my $show_no_stock = true;

# Quickbooks QODBC settings
$DSN = "QODBCDSN";

if (!($db = new Win32::ODBC($DSN))){
    print "Error connecting to $DSN\n";
    print "Error: " . Win32::ODBC::Error() . "\n";
    exit;
}

# Setup and execute query to list all Items from quickbooks.
$SqlStatement = "SELECT * FROM ItemInventory WHERE IsActive = 1 ORDER by FullName";
if ($db->Sql($SqlStatement)){
    print "SQL failed.\n";
    print "Error: " . $db->Error() . "\n";
    $db->Close();
    exit;
}

# Connect to the Mysql server.
my $dbh = DBI->connect($mysqldsn, $db_user_name, $db_password);

#if ($debug > 2) {
#    $dbh->do("DELETE FROM categories");
#    $dbh->do("DELETE FROM categories_description");
#    $dbh->do("DELETE FROM products");
#    $dbh->do("DELETE FROM products_description");
#    $dbh->do("DELETE FROM products_to_categories");
#}

# Loop through all Items
while ($db->FetchRow()){
    undef %Data;
    %Data = $db->DataHash();

    # Skip stuff we dont want on the web
    next if ($Data{'PurchaseDesc'} =~ /AMR/i);

    # Tell the user what we are doing
    print $Data{'Name'} . " ";
    
    # print debug messages if we are in debug mode;
    print Dumper(\%Data) . "\n" if $debug > 2;

    # If this is a category, then lets add/update the category
    # entry.

    $product_id = find_product_id($Data);

    if ($Data{'PurchaseDesc'} =~ /category/i) {
        print "Category ";

        # Was there any rows returned? (Does the category exist?)
        # If it does, setup an update query, otherwise setup an insert
        if (defined($product_id)) {
            $id = $product_id;
            $query = "UPDATE categories SET " .
                "date_added = '" . $Data{'TimeCreated'} . "', " .
                "sort_order = '1' " .
                "WHERE categories_id = '" . $id . "'";
            print "Updating Category: " . $query . "\n" if $debug > 1;
            print "Update\n";
        } else {
            $query = ("INSERT INTO categories " .
                      "(categories_id, date_added, sort_order) " . 
                      "VALUES " .
                      "(NULL, " .
                      "'" . $Data{'TimeCreated'} . "', '1')");
            print "Inserting Category: " . $query . "\n" if $debug > 1;
            print "New\n";
            $id = 'NULL';
        }

        # Do the insert/update query 
        $dbh->do($query);

        # If this was a new entry, get the mysql servers automatically
        # assigned number for the new record and setup the insert query
        # if if wasn't new, they setup and update query.
        if ($id eq 'NULL') {
            $id = $dbh->{'mysql_insertid'};
            $query = ("INSERT INTO categories_description " .
                      "(categories_id, categories_name) " .
                      "VALUES " .
                      "('" . $id . "', '" . 
                      $Data{'FullName'} . "')");
            print "Inserting Description: " . $query . "\n" if $debug > 1;
        } else {
            $query = "UPDATE categories_description SET " .
                "categories_name = '" . $Data{'FullName'} . "' " .
                "WHERE categories_id = '" . $id . "'";
            print "Updating Description: " . $query . "\n" if $debug > 1;
        }

        # Do the insert/update.
        $dbh->do($query);
    # If this isnt a category, then its an item, and needs to be added
    # to the products table.
    } else {
        print "Item " if $debug > 0;
        # Set the stock flag
        if ($Data{'QuantityOnHand'} <= 0) {
            if ($show_no_stock) {
                $status = 1;
            } else {
                $status = 0;
            }
        } else {
            $status = 1;
        }

        # Determine the Category id
        $query = ("SELECT categories_id from categories_description " .
                  "WHERE categories_name = '" . 
                  $Data{'ParentRefFullName'} . "'");
        my $sth = $dbh->prepare($query);
        $sth->execute();
        
        # Were there any rows returned? 
        if ($sth->rows > 0) {
            $ref = $sth->fetchrow_hashref();
            $cat_id = $$ref{'categories_id'};
        } else {
            $cat_id = 0;
        }

        # Try and work out the product id
        $products_id = find_product_id($Data);

        # Work out the manufacturers id
        if (defined($Data{'CustomFieldManufacturer'})) {
            $query = ("SELECT manufacturers_id " .
                      "FROM manufacturers " .
                      "WHERE manufacturers_name = '" . 
                      $Data{'CustomFieldManufacturer'} . "'");
            my $sth = $dbh->prepare($query);
            $sth->execute();
            if ($sth->rows > 0) {
                $ref = $sth->fetchrow_hashref();
                $manufacturers_id = $$ref{'manufacturers_id'};
            } else {
                $query = ("INSERT INTO manufacturers " .
                          "(manufacturers_id, manufacturers_name) " .
                          "VALUES " .
                          "(NULL, '" .
                          $Data{'CustomFieldManufacturer'} . "')");
                $dbh->do($query);
                $manufacturers_id = $dbh->{'mysql_insertid'};
            }
        } else {
            $manufacturers_id = 0;
        }

        # Work out the price
        if ($Data{'AmountIncludesVAT'} == 1) {
            $price = $Data{'SalesPrice'} - ($Data{'SalesPrice'} / 11);
        } else {
            $price = $Data{'SalesPrice'};
        }

        printf(" \$ %10.2f ", $price);

        # If quickbooks has data in the web products id, then use it
        # Rather than adding the product again
        if (defined($products_id)) {
            
            $query = ("SELECT * FROM specials WHERE products_id = '" . $products_id . "'");
            my $sth = $dbh->prepare($query);
            $sth->execute();
            if ($sth->rows > 0) {
                $priceupdate = "";
                print "Update - No Price\n";
            } else {
                $priceupdate = "products_price = '" . $price . "', ";
                print "Update - New Price\n";
            }

            # Add the product to the products table
            $query = "UPDATE products SET " .
                "products_quantity = '" . $Data{'QuantityOnHand'} . "', " .
                "products_model = '" . $Data{'Name'} . "', " .
                "products_date_added = '" . $Data{'TimeCreated'} . "', " .
                "products_status = '" . $status . "', " .
                "products_tax_class_id = '1', " .
                $priceupdate . 
                "manufacturers_id = '" . $manufacturers_id . "' " .
                "WHERE products_id = '" . $products_id . "'";
            $dbh->do($query);

            # Next, update the products description
            $query = "UPDATE products_description SET " .
                "products_name = '" . $Data{'Name'} . "', " .
                "products_description = '" . $Data{'SalesDesc'} . "' " .
                "WHERE products_id = '" . $products_id . "', " .
            $dbh->do($query);
            
            # Finally, update the products category association
            $query = "UPDATE products_to_categories SET " .
                "categories_id = '" . $cat_id . "' " .
                "WHERE products_id = '" . $products_id . "'";
            $dbh->do($query);
        } else {
            print "New\n";
            # Add the product to the products table
            $query = ("INSERT INTO products " .
                      "(products_id, products_quantity, products_model, products_price, products_date_added, products_status, products_tax_class_id, manufacturers_id) " .
                      "VALUES " .
                      "(NULL, '" . 
                      $Data{'QuantityOnHand'} . "', '" .
                      $Data{'Name'} . "', '" .
                      $price . "', '" .
                      $Data{'TimeCreated'} . "', '" .
                      $status . "','1','" .
                      $manufacturers_id . "')");
            $dbh->do($query);
            $id = $dbh->{'mysql_insertid'};
            
            # Next, add the products description
            $query = ("INSERT INTO products_description " .
                      "(products_id, products_name, products_description) " .
                      "VALUES " .
                      "('" . $id . "', '" .
                      $Data{'Name'} . "', '" .
                      $Data{'SalesDesc'} . "')");
#-----line 246            $dbh->do($query);
            
            # Finally, add the products category association
            $query = ("INSERT INTO products_to_categories " .
                      "(products_id, categories_id) " .
                      "VALUES " .
                      "('" . $id . "', '" . $cat_id . "')");
            $dbh->do($query);

            # Update quickbooks with the product id that was assigned
            push(@qb_update, ("UPDATE ItemInventory " .
                              "SET Barcode = '" . $id . "' " .
                              "WHERE ListID = '" . $Data{'ListID'} . "'"));

        }
    }
    $count++;
    last if (($debug > 2) && ($count > 10));
}

# Now do the quickbooks updates
#
# This doesn't work until Custom fields are supported in QODBC
#
#foreach $line (@qb_update) {
#    print $line . "\n";
#    if ($db->Sql($line)) {
#        print "SQL failed.\n";
#        print "Error: " . $db->Error() . "\n";
#        $db->Close();
#        exit;
#    }
#}

# Close the quickbooks link
$db->Close();
undef $db;
exit;

$SqlStatement = "SELECT * FROM ItemInventoryAssembly ORDER by FullName";
if ($db->Sql($SqlStatement)){
    print "SQL failed.\n";
    print "Error: " . $db->Error() . "\n";
    $db->Close();
    exit;
}

#my $dbh = DBI->connect($mysqldsn, $db_user_name, $db_password);

while($db->FetchRow()){
#$db->FetchRow();
    undef %Data;
    %Data = $db->DataHash();
#    print Dumper(\%Data) . "\n";
#    print $Data{'FullName'} . "\t" . 
#        $Data{'PurchaseCost'} . "\t" . 
#        $Data{'SalesPrice'} . "\n";
#    if (($Data{'ParentRefFullName'} eq 'WS') or
#        ($Data{'Name'} eq 'WS')) {
        print Dumper(\%Data);
    if ($Data{'PurchaseDesc'} =~ /category/i) {
        print $Data{'PurchaseDesc'} . "\n";
    } else {
        $query = ("INSERT INTO products (products_id, products_quantity, products_model, products_price, products_date_added, products_status, products_tax_class_id) VALUES(NULL, '" . 
                  $Data{'QuantityOnHand'} . "', '" .
                  $Data{'FullName'} . "', '" .
                  $Data{'SalesPrice'} . "', '" .
                  $Data{'TimeCreated'} . "', '" .
                  "1', '1')");
        print $query . "\n";
#        $dbh->do($query);
        }
#    }
}

$db->Close();

sub find_product_id {
    ($Data) = @_;
    my $sth;

    if (defined($Data{'CustomFieldWebProductId'})) {
        $products_id = $Data{'CustomFieldWebProductId'};
        print "ID: " . $products_id if $debug > 0;
    } else {
        if ($Data{'PurchaseDesc'} =~ /category/i) {
            $query = ("SELECT categories_id " .
                      "FROM categories_description " .
                      "WHERE categories_name = '" . $Data{'FullName'} . "'");
            $sth = $dbh->prepare($query);
            $sth->execute();
            
            undef $products_id;
            
            # Were there any rows returned?
            if ($sth->rows > 0) {
                $ref = $sth->fetchrow_hashref();
                $products_id = $$ref{'categories_id'};
                print "CID: "; # . $products_id if $debug > 0;
            }
            $sth->finish();
        } else {
            $query = ("SELECT products_id " .
                      "FROM products_description " .
                      "WHERE products_name = '" . $Data{'Name'} . "'");
            $sth = $dbh->prepare($query);
            $sth->execute();
            
            undef $products_id;
            
            # Were there any rows returned?
            if ($sth->rows > 0) {
                $ref = $sth->fetchrow_hashref();
                $products_id = $$ref{'products_id'};
                print "PID: "; # . $products_id if $debug > 0;
            }
            $sth->finish();
        }
    }

    return $products_id;
}