New Product Variation Datatype

After creating several e-commerce websites in eZ publish we figured out that most of them lack certain e-commerce features realted to properly handling products in eZ Publish. This datatype should improve this situtation.

Functions relevant for price calculations.

eZDataType::productOptionInformation()

$optionData = $dataType->productOptionInformation( $attribute, $optionString, $item );

return array( 'id' => $option['option_id'],
'name' => $multioptionElement['name'],
'value' => $option['value'],
'additional_price' => $option['additional_price'] );

This structure should be changed. It could have but we should only keep that for legacy

array( 'id' => $option['option_id'],
'name' => $multioptionElement['name'],
'value' => $option['value'],
'price_quantifier' => $option['price_quantifier'], (a constant for fixed, percent, amount)
'price_factor' => $option['price_factor'] );

eZShopFunctions::convertAdditionalPrice( $currency, $optionData['additional_price'] );

Should be renamed and marked deprecated. eZShopFunctions::convertPrice( $currency, $price );

eZShopOperationCollection::addToBasket( $objectID, $optionList )

This function requires a lager rewrite.

We should drop the price from the ezproductcollection_item item table. Though we can keep it for legacy.

We should add a new functions to eZ that con compute the product price as a whole from the selected options and

eZProductCollectionItem::addOption( $optionData );

Calulates the price for the collection including all options.

eZProductCollectionItem:computePrice( ); parameters yet unknown

Variation Templates

A variation template will add pre defined attributes to product. They could be configured over an additional interface.

Variation Identifiers

Are used to group variations of product. It is intended that you might be able to to do comparsions or selection based on groups.

Examples:

Show me other Products that have a green color.
Show me products that costs less then $ 20 and have the size XL.

Fucntions that can be added later

Multi curreny support, through automatic conversion can work.

Notes

Translations can work through the regluar ez publish translations. Only Variation names can be translated.

Views

Product Template Creator

[ Product Definition ]

Template Name

Avialable Sets / Tempaltes

Name Identifier [ up / down ] (Predefined attribute)

Name Identifier [ up / down ] (Custom attribute)

Name	Identifier

Name Identifier [ up / down ] (Predefined attribute)

Name Identifier [ up / down ] (Custom attribute)

Product Attributes

Product Attribute Sets

Changing the identifiers name will be disabled, when adding it from a template.

When adding a Custom Variation a non template based Variation will be added.

Creates all Variations out of all possible combinations

Edit Product

[Base Attributes] [ Variations ] [ other ]

Configuration	Value
Product Template
Base product number
Base product price
Has variations
Manage Stock
Default sorting

Size / Weight /Volume	Price	Inventory

[Base Attributes] [ Variations ] [ other ]

Variations

SKU

Name

Description

Size / Weight / Volume

Price

Inventory

Internal Custom

Percent and Amount will also Allow negativ Values

Variation Attributes

Color	Size	Packing size	Hazard
			yes no

Database tables

There is a seperate SQL file attached for testing the conpect of storing data.

BEGIN;
DROP TABLE IF EXISTS `ezx_xrow_variation`;
CREATE TABLE `ezx_xrow_variation` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `price` int(10) unsigned NOT NULL COMMENT 'Foreign Key Multiprice data',
  `sku` varchar(45) NOT NULL,
  `sorting` varchar(45) NOT NULL,
  `stock` int(10) unsigned NOT NULL,
  `contentobject_id` int(10) unsigned NOT NULL,
  `version_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Variations Table';
DROP TABLE IF EXISTS `ezx_xrow_product`;
CREATE TABLE  `ezx_xrow_product` (
  `contentobject_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `version_id` int(10) unsigned NOT NULL,
  `base_price` varchar(45) NOT NULL,
  `base_product_number` varchar(45) NOT NULL,
  `has_variations` int(10) unsigned NOT NULL,
  `manage_stock` varchar(45) NOT NULL,
  PRIMARY KEY (`contentobject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS ezx_xrow_attribute;
CREATE TABLE  ezx_xrow_attribute (
  `contentobject_id` int(10) unsigned NOT NULL,
  `version` int(10) unsigned NOT NULL,
  `identifier` varchar(45) NOT NULL DEFAULT 'default',
  `data_text` text,
  `data_int` int(10) unsigned DEFAULT NULL,
  `data_float` float DEFAULT NULL,
  `variation_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`variation_id`,`version`,`contentobject_id`,`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ezx_xrow_attribute`;
CREATE TABLE `ezx_xrow_attribute` (
  `contentobject_id` int(10) unsigned NOT NULL,
  `version` int(10) unsigned NOT NULL,
  `identifier` varchar(45) NOT NULL DEFAULT 'default',
  `data_text` text,
  `data_int` int(10) unsigned DEFAULT NULL,
  `data_float` float DEFAULT NULL,
  `variation_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`variation_id`,`version`,`contentobject_id`,`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*FILLSOME DEMO DATA*/
INSERT INTO `ezx_xrow_attribute` (`contentobject_id`,`version`,`identifier`,`data_text`,`data_int`,`data_float`,`variation_id`) VALUES
 (1,1,'price',NULL,NULL,12,1),
 (1,1,'type','bla',NULL,NULL,1),
 (1,1,'price',NULL,NULL,13,2),
 (1,1,'type','bla',NULL,NULL,2),
 (1,1,'price',NULL,NULL,5,3),
 (1,1,'type','bla2',NULL,NULL,3);
INSERT INTO `ezx_xrow_product` (`contentobject_id`,`version_id`,`base_price`,`base_product_number`,`has_variations`,`manage_stock`) VALUES
 (1,1,'30','aaa',1,'1');
INSERT INTO `ezx_xrow_variation` (`id`,`price`,`sku`,`sorting`,`stock`,`contentobject_id`,`version_id`) VALUES
 (1,11,'asdasd','sddsada',23321,1,1),
 (2,12,'asdasdasd','asss',11,1,1),
 (3,14,'aaaaaaaaaaaaa','aaaaaaaaaaaa',222,1,1);
 
/* CREATING A TEMPORARY TABLE FOR SORTING RESULTS*/
DROP TABLE IF EXISTS test_sort;
CREATE TABLE test_sort (
  `id` int(11) UNIQUE NOT NULL default '0',
  `sort1` VARCHAR(11) default NULL,
  `sort2` FLOAT(11) default NULL
);

INSERT INTO `test_sort` ( id, `sort1` )
SELECT    variation_id, data_text
FROM      ezx_xrow_attribute a
WHERE     a.identifier = 'type';


UPDATE `test_sort` t, ezx_xrow_attribute a
    SET t.sort2=a.data_float
    WHERE a.identifier = 'price' AND
          a.variation_id = t.id;

/* GET SORTED VARIATIONS by 2 sort params */
SELECT DISTINCT t.* FROM ezx_xrow_attribute a, test_sort t WHERE a.variation_id=t.id ORDER BY t.sort1 asc, t.sort2 desc;

COMMIT;