Xtuple ERP Product Import into Ubercart

From wiki.kungfootek.net
Jump to: navigation, search

This Perlscript will read an Xtuple postgress database for characteristics assigned to items within Xtuple and import them into Ubercart with a product photo if one is provided in the ./pimages/. folder where $fname is the product $item_number.jpg. If you desire png or some other format, alter .jpg in the code to match the format your product images are in. This was written for a version of Xtuple ERP written in 2009, and may require some modification to work with current versions, but may also work for other ERP systems. This query is what reaches into the postgres DB for it's information.

$query="select charass_target_id, charass_value from charass where charass_target_type = 'I'";

In Xtuple set the characteristic to 'I' for all the items you would like to see imported into the Drupal Ubercart module. The code is commented. If you have any questions or would like some help you can reach me on my contact form on: my personal website.

print "\n\n Ubercart Product Import, Initializing...\n\n";

use DBI;
use DBD::mysql;
use Digest::MD5 qw(md5 md5_hex md5_base64);
use feature ':5.10';
use Time::Local;
use File::stat;

# dbh_my -> MySQL ; dbh_pg -> Postgres / Xtuple
my $dbh_my = DBI->connect("DBI:mysql:dbname=fm_drupal_6;host=<domain.tld>", "<user>", "<pass>", {'RaiseError' => 1});
my $dbh_pg = DBI->connect("DBI:Pg:dbname=FMI-3.2.2;host=<domain.tld>", "<user>", "<pass>", {'RaiseError' => 1});

# Get the nid and Vid
$query="select nid, vid from node order by nid asc";
my $sth_my = $dbh_my->prepare($query); $sth_my->execute();
while(my $ref = $sth_my->fetchrow_hashref())  { $nid="$ref->{'nid'}"; $vid="$ref->{'vid'}";  } 
		print "Nid and Vid $nid $vid\n"; ## Get the last NID number to begin auto increment. -- Don't need anymore.

$query="select charass_target_id, charass_value from charass where charass_target_type = 'I'";

	my $sth_pg = $dbh_pg->prepare($query);
#	print "$query\n\n";
	while(my $ref = $sth_pg->fetchrow_hashref()) 
		$nid++; $vid++;  
		print "$nid - $term - ";
		$term = $ref->{'charass_value'};		

$grabtheiteminfo="SELECT item_id, item_number, item_descrip1, item_descrip2, item_listprice, item_prodweight from item where item_id=$ref->{'charass_target_id'}";				

		my $sth_pg = $dbh_pg->prepare($grabtheiteminfo);
		$sth_pg->execute();  # extract product data from the Xtuple product tables
#		print "$grabtheiteminfo\n";
		while(my $ref = $sth_pg->fetchrow_hashref()) 
		{ if ( $ref->{'item_id'} !="" &&  $ref->{'item_id'} !=1 && $ref->{'item_listprice'} !=0) 
# Insert the product data into the drupal and ubercart tables.		
$hashish=md5_hex($ref->{item_number} . 0.000 . 0.000 . $ref->{item_listprice} . $ref->{item_prodweight} . 'oz' . 6 . 6 . 1 . 'in' . 1 . 1 .  1 . time());

## insert into NODE
$query="replace INTO `node` (`nid`, `vid`, `type`, `language`, `title`, `uid`, `status`, `created`, `changed`, `comment`, `promote`, `moderate`, `sticky`, `tnid`, `translate`) VALUES ($nid, $vid, 'product', '', '$ref->{item_number} -- $ref->{item_descrip1} - $ref->{item_descrip2}', 1, 1, ".time().", ".time().", 2, 0, 0, 0, 0, 0);";
#print "Node:\n$query\n\n";

## Insert into NODE Revisions
$query="replace INTO `node_revisions` (`nid`, `vid`, `uid`, `title`, `body`, `teaser`, `log`, `timestamp`, `format`) VALUES ($nid, $vid, 1, '$ref->{item_number} -- $ref->{item_descrip1} - $ref->{item_descrip2}', '$ref->{item_descrip1} - $ref->{item_descrip2} - $ref->{item_number}', '$ref->{item_descrip1} - $ref->{item_descrip2} - $ref->{item_number}', '', ".time().", 1)";
# print "Node Revisions:\n$query\n\n"; 

## Insert into UC_Products
$query="replace INTO `uc_products` (`vid`, `nid`, `model`, `list_price`, `cost`, `sell_price`, `weight`, `weight_units`, `length`, `width`, `height`, `length_units`, `pkg_qty`, `default_qty`, `unique_hash`, `ordering`, `shippable`) VALUES ($vid, $nid, '$ref->{item_number}', 0.000, 0.000, $ref->{item_listprice}, $ref->{item_prodweight}, 'oz', 6, 6, 1, 'in', 1, 1, '$hashish', 0, 1);";
#print "UC_Prodcuts:\n$query\n\n"; 

## Insert into Taxonomy
$query="replace into term_node set nid=$nid, vid=$vid, tid=$term";  
#print "Term_Node:\n$query\n\n"; 

## Place the photo.
$timestamp=time; 	$fname="$ref->{item_number}.jpg";

		 	if (-e "./pimages/$fname") 
				my $fsize = stat("./pimages/$fname")->size;
				print "$fname - $fsize\n";
$insert_into_files="replace INTO files (uid, filename, filepath, filemime, filesize, status, timestamp) VALUES (1, \"$fname\", \"sites/default/files/$fname\", \"image/jpeg\", \'$fsize\', 1, \'$timestamp\')";  
				my $sth_my2 = $dbh_my->prepare($insert_into_files); 
# 				print "$insert_into_files\n";
				$sth_my2->execute(); # Place the image into the content table.

				$lastid = $dbh_my->selectrow_array("SELECT LAST_INSERT_ID()");
$insert_into_content_field_image_cache="replace into content_field_image_cache (vid, nid, delta, field_image_cache_fid, field_image_cache_list, field_image_cache_data) values ('$vid', '$nid', 0, '$lastid', '1', 'a:2:{s:3:\"alt\"\;s:0:\"\"\;s:5:\"title\"\;s:0:\"\"\;}')";
				my $sth_my3 = $dbh_my->prepare($insert_into_content_field_image_cache); 
# 				print "$insert_into_content_field_image_cache\n";				
				$sth_my3->execute(); # Place the image into the content table.
				else  { print "$fname - NO IMAGE\n"; }
print "\n\n Don't forget to flush the Cache under \"Performance\"\n\n";