VM1 to VM2: Porting of Sku Attributes for child products missing

[mbarry]

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;
   }
   

[mbarry]

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.

Code Select Expand


/**
*
* 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;
}



[Maxim Pishnyak]

What about if vm_product_attribute_sku (VM1) is not used, but attribute column from _vm_product table used instead for customizing child positions?