[jasonatennui]

I'm in the process of developing a module to run an XML import of products from a point of sale system.

I've got all of the technical junk out of the way (unpacking the ZIP archive, importing the XML, parsing the XML into individual product information bits), but I'm really new to LemonStand and I'm not sure the preferred method of saving products to the database.

Here are my goals:

• Import new products into the database[/*]
  
• Update existing products in the database[/*]
  
• Delete old products from the database[/*]

The POS system provides an XML dump of new/updated products and products to be removed, as well as categories to be added/updated/removed, and a dump of product-category relationships.

What I'm wondering is this: how do I save the parsed product info, categories, and product-category associations into the DB?

My first instinct was to invoke a Shop_Product() object and save them like that:

$db_product = new Shop_Product();
$db_product->name = $name;
$db_product->description = $ldesc;
$db_product->short_description = $sdesc;
$db_product->url_name = $url;
$db_product->price = $price;
$db_product->sku = $sku;
$db_product->weight = $weight;
$db_product->width = $width;
$db_product->height = $height;
$db_product->depth = $depth;
$db_product->meta_description = $meta_description;
$db_product->meta_keywords = $meta_keywords;
$db_product->enabled = $enabled;
$db_product->save();

However, that resulted in the following error:

Quote

Error applying catalog price rules: MySQL error executing query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '),(3,2,),(3,3,),(3,4,)' at line 1

Also, due to my lack of familiarity with ActiveRecord, I'm not sure if that will update existing products (I imagine it won't unless I check for the SKU or something similar first).

And, of course, the categories are sort of confusing to me. I looked at the documentation, but I couldn't find a "save product" example. I could probably hack this together, but I figured I'd ask about best practices first. I don't want to break anything. :)

Thanks in advance!

[Aleksey]

Hi!

Thank you for the detailed description. It helps a lot! :-)

When it comes to processing multiple products it is more efficient to work with SQL queries instead of Active Record. Active Record could take much memory and it works slower.

We have a good example of the product import feature - the CSV import. Please look to the /modules/shop/models/shop_productcsvimportmodel.php script. It contains the Shop_ProductCsvImportModel class which imports data from a CSV file. It contains many lines of code, but you don't really need much of the code.

You only need to get the idea of how this class creates and updates products. First, this class inherits the Db_ActiveRecord class (through another class, but this does not matter). It adds some useful methods for working with direct SQL queries the class. You can create a similar class and repeat some parts of the code in it.

There are two methods defined in the class - create_product() and update_product_fields(). These methods accept arrays of product field values (field_name=>field_values) and execute CREATE or UPDATE queries using the following calls:

$this->sql_insert('shop_products', $product_fields);

and

$this->sql_update('shop_products', $product_fields, 'id='.$existing_product_id);

There are also some service code around - setting the current user ID and create/update time and so on. I believe you will get the point. Also you will find the set_product_categories() method in this class, which assigns different categories to products.

Please let me know if you need help with it.

Thank you

[jasonatennui]

Thanks, Aleksey! That worked properly.

However, it got me thinking: how feasible would it be to simply extend the Ship_ProductCsvImportModel class to support XML imports?

I need to support product grouping, images, categories, pretty much everything. It's tough to track back through the code because there are no docblocks (feature request!), so I feel like I'm going to be reinventing the wheel if I don't stop and ask for directions. :)

If it's possible to extend the CSV Import class, I'd love to collaborate with you and/or other LS developers to come up with the most effective way to do so.

If not, any direction at all is much appreciated. :)

[Aleksey]

Hi, Jason!

In general I agree with you. We need to make the product importing feature more flexible and allow other developers to write custom import options. But this is a large-scale task and we have no time for it now, unfortunately.

I have a suggestion. What if you will be converting your XML document into a CSV file? Then you will not need developing any importing code. Instead, you can develop a simple script for generating a CSV file, which will be calling the standard CSV import function internally. What do you think?

Thank you

[jasonatennui]

Aleksey-

That's definitely a possibility. If I wanted to run the CSV import with a cron job, would that be possible? Or would it need to be done using the back-end tools each time?

[Aleksey]

Jason,

If your script can fetch the XML document from somewhere and prepare the CSV file, than you can do everything automatically. Cron jobs are supported by the engine, so there will be no issues with this. Please prepare a CSV document generator and let me know when you finish. Oh, regarding images. It will not be convenient for you to generate a ZIP archive containing images. I'm going to implement an image directory support in the product CSV import feature, so it will be possible to specify a directory where your images are placed, and the script will use this directory as an image source instead of unpacking a ZIP archive. And you can use this feature for your need as well.

Let me know when you need this.

Thanks

[jasonatennui]

This all looks perfect! Quick question about CSV imports that I couldn't find in your documentation: how do I define a sub-category in the CSV? Is that even possible in LemonStand?

Also, with images, can I specify thumbnails? Or should I just upload the full-size image?

[jasonatennui]

Oh, and speaking of images, the export from the POS is a zip file with images. All the images are at root level, and if the presence of an XML file isn't a big deal, we might not need any new directory support. :)

[Aleksey]

Hi, Jason!

Quote

How do I define a sub-category in the CSV? Is that even possible in LemonStand?

This feature is planned but not implemented yet. If it is required for your product, I can move the task to the top of our to-do list. Please let me know.

Thanks

[jasonatennui]

Aleksey-

No rush on the subcategories. I think that can be styled up to look like subcategories easily enough.

I have the CSV file ready, but I think I might need a hand figuring out a few things. I'm getting a few errors on images that I don't entirely understand, so I'd love your input on that.

Also, when I tried to save my ICF file during testing, what actually saved was an HTML page which loads the "file not found" error page. Just an FYI.

Thanks for all your help!

[Aleksey]

Hi, Jason!

I attached a PHP file which demonstrates how you can import products using PHP. The most important method in the demo class is import(). Other methods are required by the LemonStand's CSV import feature.

Please note that columns are configured using indexes:

$columns = array(
  array('name'),
  ...
);

This means that the first (zero) column in the CSV file contains the product name. LemonStand allows assigning multiple product fields to a single column in the CSV file, so we need to use arrays here. In the PHP file you will find a list of all product fields.

Quote

I'm getting a few errors on images that I don't entirely understand, so I'd love your input on that.

I need more information. :-)

Quote

Also, when I tried to save my ICF file during testing, what actually saved was an HTML page which loads the "file not found" error page.

It should be something server-specific. Where do you test - on your local machine? Can I take a look? Maybe you can create a temporary account for me? Please send it to aleksey [at] lemonstandapp.com

Thank you

[jasonatennui]

Thanks for the sample script!

Regarding the images, it's giving path errors — all of the lines in the CSV file are set up the same, but some of them are giving nonsensical errors citing the path /SDAJ or something equally confusing.

Finally, I have some questions about product grouping, but it might be easier to go over this in email since it requires showing you samples with client data in them.

Is that alright? You can reach me at answers [at] ennuidesign.com

I'll have an account for you soon.

Thanks for everything!

[jkudish]

Hi,

I am currently evaluating whether LemonStand will fit the needs of an upcoming project for a client. One of the main features that will be required is an advanced import feature. I stumbled upon this post with my research. I was curious to see Aleksey's example php file above, but I can't see the link
Would you mind pointing me to the link?

Cheers,
Joey

[Eric]

jkudish, on 25 March 2011 - 11:57 AM, said:

Hi,

I am currently evaluating whether LemonStand will fit the needs of an upcoming project for a client. One of the main features that will be required is an advanced import feature. I stumbled upon this post with my research. I was curious to see Aleksey's example php file above, but I can't see the link
Would you mind pointing me to the link?

Cheers,
Joey

Reattached.

Attached File(s)

•  custom_import.php (3.17K)
  Number of downloads: 10

[David Wren]

Apologies for re-opening an old thread, but I am trying to setup a module to run the CSV import (in the script attached to the thread) from a cron job and am having a bit of trouble.

Problem is, the module is being run whenever the site loads a page (the page times out as the script takes a while).

How can I defer it to only run when requested (via cron) and not on every page (so the site doesn't crash!)?

As the script is a little old now, I was wondering if it's out of date - maybe there is a newer way of doing things? Or is there a problem with the way I have set things up?

I have a file / folder structure like this:

modules
-- productimport
---- classes
------ productimport_module.php
---- updates
------ version.dat

Here is the code from productimport_module.php:

<?
  class ProductImport extends Core_ModuleBase
  {
		/**
		 * Creates the module information object
		 * @return Core_ModuleInfo
		 */
		protected function createModuleInfo()
		{
			return new Core_ModuleInfo(
				'Custom Import',
				'Imports data from a CSV file, can be automated with a cron job',
				'David Wren' );
		}

	public function csvImportDbColumnPresented(&$matches, $column_db_name)
	{
  	return true;
	}
	
	public function csvImportGetCsvFileHandle()
	{
  	$handle = @fopen('/users/elf/temporary/products.csv', "r");
  	if (!$handle)
    	throw new Phpr_ApplicationException('Unable to open the CSV file');
    	
  	return $handle;
	}
	
	public function csvImportBoolValue($value)
	{
  	$value = mb_strtolower($value);

  	if ($value == 1 || $value == 'enabled' || $value == 'y' || $value == 'yes' || $value == 'active')
    	return true;
    	
  	return false;
	}

	public function csvImportFloatValue($value)
	{
  	$value = str_replace(' ', '', $value);
  	$value = str_replace(',', '', $value);

  	return $value;
	}
	
	public function csvImportNumericValue($value)
	{
  	$value = str_replace(' ', '', $value);
  	$value = round(str_replace(',', '', $value));

  	return $value;
	}
   
	public function import()
	{
  	$import_model = new Shop_ProductCsvImportModel();
  	$import_model->auto_create_categories = true;
  	$import_model->import_product_images = true;
  	$import_model->auto_manufacturers = true;
  	$import_model->update_existing_sku = true;
  	
  	$import_model->auto_tax_classes = false;
  	$import_model->tax_class = Shop_TaxClass::create()->find_by_name('VAT');
  	
  	$import_model->product_type = Shop_ProductType::create()->find_by_name('Goods');
  	
  	// If product images are stored in a directory on the server,
  	// use the next line
  	$import_model->images_directory_path = '/users/elf/temporary/images/';
 	
  	// If images are stored in a ZIP archive, use the following code
  	//$file = Db_File::create()->fromFile('/users/elf/temporary/images.zip');
  	//$file->master_object_class = get_class($import_model);
  	//$file->field = 'images_file';
  	//$import_model->images_file->add($file, 'tmp_session');
  	
  	// Import products
  	
  	$data_model = new Shop_Product();
  	$delimeter = ',';
  	$first_row_titles = true; // The first row in the CSV contains column titles
  	
  	$columns = array(
    	array('id'),
    	array('name'),
    	array('url_name'),
    	array('manufacturer_link'),
    	array('price'),
    	array('cost'),
    	array('enabled'),
    	array('sku'),
    	array('weight'),
    	array('width'),
    	array('height'),
    	array('depth'),
    	array('track_inventory'),
    	array('in_stock'),
    	array('hide_if_out_of_stock'),
    	array('categories'),
    	array('csv_related_sku')
  	);  	
  	
  	$import_result = $import_model->import_csv_data($data_model, 'tmp_session', $columns, $this, $delimeter, $first_row_titles);

  	return $import_result;
	}
  }
 
  $obj = new ProductImport();
  print_r($obj->import());

?>