VM 2.0.26a/d
Joomla 2.5.17
Scenario:
Run the VM2 Migration tool with "everything" selected".
Issue:
The child product SKU attributes are not ported from vm_product_attribute and vm_product_attribute_sku
For large VM1 shops, these tables could hold a significant amount of data and would be very time consuming/error prone to recreate in VM2.
Solution:
A new function portVm1SkuAttributes() has been created and included in helpers/migrator.php
Call function from migrateAllInOne() after line 208 as $result = $this->portVm1SkuAttributes()
Note: This function has been tested where a child product has only one sku attribute. i.e selectname1 and selectoption1 are used; the others are blank
It looks for commonality in sku attributes (removes capitalisation and reorders options alpha-numerically) and
will reuse an an existing entry in virtuemart_customs rather than add a new one.
In VM2, sku attributes are implemented as stockable plugins and hence, a check that the plugin exists and is enabled is performed.
install.sql is also modified to include new entry " sku_attributes` longblob, " in Table `#__virtuemart_migration_oldtonew_ids`
Affected files:
administrator/components/com_virtuemart/helpers/migrator.php line 208 and 2167
administrator/components/com_virturemart/install/install.sql line 424
code snip for portVm1SkuAttributes()
/**
*
* Ports the product attributes configured as selectable SKU child products from the parent product
*
* @author M. Barry
*/
function portVm1SkuAttributes(){
if($this->_stop || (microtime(true)-$this->starttime) >= ($this->maxScriptTime)){
return;
}
vmInfo('Migration: processing sku attributes ..... ');
$alreadyKnownIds = $this->getMigrationProgress('sku_attributes');
$alreadyProcessed = count($alreadyKnownIds);
$completionStatus = false;
$i = 0;
// Check if stockable plugin exists and is enabled, we also need the plugin 'extension_id' for later on
$q = 'SELECT `extension_id`, `enabled` FROM `#__extensions` WHERE `#__extensions`.`element` = "stockable"';
$this->_db->setQuery($q);
$res = $this->_db->loadAssocList();
$extension = $res[0];
if (empty($extension)) {
vmWarn('portVm1SkuAttributes : virtuemart stockable extension does not exist');
return;
}
else if($extension['enabled']=='0') {
vmWarn('portVm1SkuAttributes : virtuemart stockable extension exists but is not enabled, must be enabled first');
return;
}
//Select parent product from the attribute_sku table
$q = 'SELECT * FROM `#__vm_product_attribute_sku` WHERE `#__vm_product_attribute_sku`.`attribute_name` <> ""';
$this->_db->setQuery($q);
$productIds = $this->_db->loadAssocList();
$maxItems = count($productIds);
foreach ($productIds as $productId) {
if(array_key_exists($productId['product_id'],$alreadyKnownIds)) {
continue;
}
//Remove Case from to improve consistency in table entry and string searches/sorts/comparisions
$productId['attribute_name'] = ucfirst(strtolower($productId['attribute_name']));
//vmInfo('portVm1SkuAttributes : Parent ID :'.$productId['product_id']);
// Converting Attributes from parent product to customfields Stockable Plugin variant
// Select VM1 SKU Product attribute and values for each child product assigned to a parent product
// Assumes only one SKU product attribute per product
$q = 'SELECT `pa`.`attribute_value`,`pa`.`attribute_name`,`pa`.`product_id`,`pa`.`attribute_id`
FROM `#__vm_product` AS `p`
LEFT JOIN `#__vm_product` AS `cp` ON `cp`.`product_parent_id` = `p`.`product_id`
LEFT JOIN `#__vm_product_attribute` AS `pa` ON `pa`.`product_id` = `cp`.`product_id`
WHERE `p`.`product_id` = "'.$productId['product_id'].'"';
$this->_db->setQuery($q);
$productAttributes = $this->_db->loadAssocList();
if (!empty($productAttributes)) {
//alpha_numeric sort $productAttributes based on 'attribute_value' to provide commonality in order of attribute values.
// Products entered randomly and hence, attributes associated with products will also be presented in random order
if (!function_exists('cmp_')) {
function cmp_ ($a, $b) {
return strnatcmp ( $a['attribute_value'] , $b['attribute_value'] );
}
}
uasort($productAttributes, 'cmp_');
// Create the custom_params for the stockable plugin
$selectoptions1 = '';
$selectoptions1_ = '';
foreach ($productAttributes as $childAttrib) {
if($childAttrib['attribute_name'] == NULL) break;
//Remove Case from to improve consistency in table entry and string searches/sorts/comparisions
$childAttrib['attribute_name'] = ucfirst(strtolower($childAttrib['attribute_name']));
if($childAttrib['attribute_name'] != $productId['attribute_name']) {
vmError('portVm1SkuAttributes : Child ID :'.$childAttrib['product_id'].' attibute : '.$childAttrib['attribute_name'].'!= Parent ID :'.$productId['attribute_name']);
break;
}
if($selectoptions1 == '') {
$selectoptions1 = $selectoptions1_cmp = $childAttrib['attribute_value'];
}
else {
$selectoptions1 .= "\r\n".$childAttrib['attribute_value'];
$selectoptions1_cmp .= '\r\n'.$childAttrib['attribute_value'];
}
//vmInfo('portVm1SkuAttributes : Child ID :'.$childAttrib['product_id'].' attibute : '.$childAttrib['attribute_name'].'= '.$childAttrib['attribute_value']);
}
// Use custom_params to determine if a custom has been used before.
// A comparision based on attribute_name does not work since this field is automatically prepended to make it unique
//
$customParams_cmp = 'selectname1='.'""'.$productId['attribute_name'].'""'.'|'.'selectname2=""""'.'|'.'selectname3=""""'.'|'.'selectname4=""""'.'|';
$customParams_cmp .= 'selectoptions1='.'""'.addslashes(str_replace('/','\\/',$selectoptions1_cmp)).'""'.'|'.'selectoptions2=""""'.'|'.'selectoptions3=""""'.'|'.'selectoptions4=""""'.'|';
$q = 'SELECT `virtuemart_custom_id` FROM `#__virtuemart_customs` as c WHERE c.`field_type` ="E" AND c.`custom_params` ="'.$customParams_cmp.'"';
$this->_db->setQuery($q);
$res = $this->_db->loadResult();
$attrib = array();
$newId = NULL;
// Check if specific customParams already exists, if not create a new entry in database
if(!$res) {
$customModel = VmModel::getModel('Custom');
$attrib['custom_title'] = $productId['attribute_name'];
$attrib['show_title'] = '0';
$attrib['custom_element'] = 'stockable';
$attrib['custom_jplugin_id'] = $extension['extension_id'];
$attrib['custom_value'] = 'stockable';
$attrib['field_type'] = 'E';
$attrib['is_list'] = '0';
$attrib['is_hidden'] = '0';
$attrib['is_cart_attribute'] = '1';
$attrib['params'] = array('selectname1'=>$productId['attribute_name'],'selectname2'=>'','selectname3'=>'','selectname4'=>'',
'selectoptions1'=>$selectoptions1,'selectoptions2'=>'','selectoptions3'=>'','selectoptions4'=>'');
$newCustomId = $customModel->store($attrib);
}
// Store the customfield details with the parent product in the product_customfields Table
$data = array();
if($newCustomId) {
$productIdMapping = $this->getMigrationProgress('products');
$newParentProductId = $productIdMapping[$productId['product_id']];
$field = array();
$field['virtuemart_custom_id'] = $newCustomId;
$field['custom_value'] = 'stockable';
$field['admin_only'] = '0';
$field['ordering'] = '1';
$field['published'] = '1';
$data['field'][0] = $field;
$childList = array();
foreach ($productAttributes as $childAttrib) {
if($childAttrib['attribute_name'] == NULL) continue;
$newChildProductId = $productIdMapping[$childAttrib['product_id']];
$childList[$newChildProductId]['is_variant'] = '1';
$childList[$newChildProductId]['selectoptions1'] = $childAttrib['attribute_value'];
$childList[$newChildProductId]['custom_price'] = '';
}
if(!empty($childList)) {
$data['custom_param'][0]['child'] = $childList;
if (!class_exists ('VirtueMartModelCustomfields')) {
require(JPATH_VM_ADMINISTRATOR . DS . 'models' . DS . 'customfields.php');
}
VirtueMartModelCustomfields::storeProductCustomfields('product', $data, $newParentProductId);
}
}
}
$alreadyKnownIds[$productId['product_id']] = (int) $newParentProductId;
$i++;
if((microtime(true)-$this->starttime) >= ($this->maxScriptTime)){
break;
}
}
$this->storeMigrationProgress('sku_attributes',$alreadyKnownIds);
vmInfo('Migration: '.$i.' sku attributes processed this run, already processed '.$alreadyProcessed.' of '.$maxItems);
if(($alreadyProcessed+$i) == $maxItems) $completionStatus = true;
return $completionStatus;
}
Since posting the code for portVm1SkuAttributes() , I noticed that the child products would inherent the sku of the parent product. After a hunt around on the web I found this site http://cocoate.com/vm2/product-attributes-custom-fields which details exactly how I wanted the selection of child products to behave in VM2. The behaviour described is exactly how they currently behave in VM1 using the sku attribute. Based on this, I have updated the code to build generic child variant customs.
/**
*
* Ports the product attributes configured as selectable SKU child products from the parent product
* Generic Child Variant
*
* @author M. Barry
*/
function portVm1SkuAttributes(){
if($this->_stop || (microtime(true)-$this->starttime) >= ($this->maxScriptTime)){
return false;
}
vmInfo('Migration: processing sku attributes ..... ');
$alreadyKnownIds = $this->getMigrationProgress('sku_attributes');
$alreadyProcessed = count($alreadyKnownIds);
$completionStatus = false;
$i = 0;
//Select parent product from the attribute_sku table
$q = 'SELECT * FROM `#__vm_product_attribute_sku` WHERE `#__vm_product_attribute_sku`.`attribute_name` <> ""';
$this->_db->setQuery($q);
$productIds = $this->_db->loadAssocList();
$maxItems = count($productIds);
foreach ($productIds as $productId) {
if(array_key_exists($productId['product_id'],$alreadyKnownIds)) {
continue;
}
//Remove Case from to improve consistency in table entry and string searches/sorts/comparisions
$productId['attribute_name'] = ucfirst(strtolower($productId['attribute_name']));
$q = 'SELECT `virtuemart_custom_id` FROM `#__virtuemart_customs` as c WHERE c.`field_type` ="A" AND c.`custom_title` ="'.$productId['attribute_name'].'"';
$this->_db->setQuery($q);
$res = $this->_db->loadResult();
$attrib = array();
$newId = NULL;
// Check if specific customParams already exists, if not create a new entry in database
if(!$res) {
$customModel = VmModel::getModel('Custom');
$attrib['custom_title'] = $productId['attribute_name'];
$attrib['show_title'] = '1';
$attrib['custom_element'] = '0';
$attrib['custom_jplugin_id'] = '0';
$attrib['custom_value'] = '';
$attrib['field_type'] = 'A';
$attrib['is_list'] = '0';
$attrib['is_hidden'] = '0';
$attrib['is_cart_attribute'] = '1';
$customId = $customModel->store($attrib);
}
else {
$customId = $res;
}
// Converting Attributes from parent product to generic child variant
// Select VM1 SKU Product attribute and values for each child product assigned to a parent product
// Assumes only one SKU product attribute per product
// for each child product, select product_name and append $childAttrib['attribute_value'] to the product name
$q = 'SELECT `p`.`product_name`,`pa`.`attribute_value`,`pa`.`attribute_name`,`pa`.`product_id`,`pa`.`attribute_id`
FROM `#__vm_product` AS `p`
LEFT JOIN `#__vm_product` AS `cp` ON `cp`.`product_parent_id` = `p`.`product_id`
LEFT JOIN `#__vm_product_attribute` AS `pa` ON `pa`.`product_id` = `cp`.`product_id`
WHERE `p`.`product_id` = "'.$productId['product_id'].'"';
$this->_db->setQuery($q);
$productAttributes = $this->_db->loadAssocList();
if($productId['product_id']=='910') {
$found = true;
}
if (!empty($productAttributes)) {
//alpha_numeric sort $productAttributes based on 'attribute_value' to provide commonality in order of attribute values.
// Products entered randomly and hence, attributes associated with products will also be presented in random order
if (!function_exists('cmp_')) {
function cmp_ ($a, $b) {
return strnatcmp ( $a['attribute_value'] , $b['attribute_value'] );
}
}
uasort($productAttributes, 'cmp_');
foreach ($productAttributes as $childAttrib) {
if($childAttrib['attribute_name'] == NULL) break;
//Remove Case from to improve consistency in table entry and string searches/sorts/comparisions
$childAttrib['attribute_name'] = ucfirst(strtolower($childAttrib['attribute_name']));
if($childAttrib['attribute_name'] != $productId['attribute_name']) {
vmError('portVm1SkuAttributes : Child ID :'.$childAttrib['product_id'].' attibute : '.$childAttrib['attribute_name'].'!= Parent ID :'.$productId['attribute_name']);
break;
}
// append $childAttrib['attribute_value'] to Product 'product_name' to ensure product name is selectable based on the generic child variant attribute
$childAttrib['product_name'] .= ' - '.$childAttrib['attribute_value'];
// UPDATE child product product_name in #__virtuemart_products_language Table
$q = 'UPDATE `#__virtuemart_products_'.VMLANG.'` SET `product_name`="'.$childAttrib['product_name'].'" WHERE `virtuemart_product_id` = "'.$childAttrib['product_id'].'"';
$this->_db->setQuery($q);
$this->_db->query();
}
// Store the customfield details with the parent product in the product_customfields Table
$data = array();
if($customId) {
$productIdMapping = $this->getMigrationProgress('products');
$newParentProductId = $productIdMapping[$productId['product_id']];
$field = array();
$field['virtuemart_custom_id'] = $customId;
$field['custom_value'] = 'product_sku';
$field['admin_only'] = '0';
$field['ordering'] = '1';
$field['published'] = '1';
$field['custom_param'] = 'withParent="0"|parentOrderable="0"|';
$data['field'][0] = $field;
if (!class_exists ('VirtueMartModelCustomfields')) {
require(JPATH_VM_ADMINISTRATOR . DS . 'models' . DS . 'customfields.php');
}
VirtueMartModelCustomfields::storeProductCustomfields('product', $data, $newParentProductId);
}
$alreadyKnownIds[$productId['product_id']] = (int) $newParentProductId;
$i++;
if((microtime(true)-$this->starttime) >= ($this->maxScriptTime)){
$this->_stop = true;
break;
}
}
}
$this->storeMigrationProgress('sku_attributes',$alreadyKnownIds);
vmInfo('Migration: '.$i.' sku attributes processed this run, already processed '.$alreadyProcessed.' of '.$maxItems);
if(($alreadyProcessed+$i) == $maxItems) $completionStatus = true;
return $completionStatus;
}
What about if vm_product_attribute_sku (VM1) is not used, but attribute column from _vm_product table used instead for customizing child positions?