News:

You may pay someone to create your store, or you visit our seminar and become a professional yourself with the silver certification

Main Menu

VM1 to VM2: Port userfield_values is missing

Started by mbarry, January 16, 2014, 06:47:06 AM

Previous topic - Next topic

mbarry

VM 2.0.26a/d
Joomla 2.5.17

Scenario:
      Run the VM2 Migration tool with "everything" selected".
Issue:
      The table virtuemart_userfield_values is not populated with the vm_userfield_values
      In my case I had a number of fields that I use for identifying how the the new user found the website and is linked to a userfield that is visible during registration.
     
Solution:
      A new function portVm1UserfieldValues() has been created and included in helpers/migrator.php
     
      Call function from migrateAllInOne() after line 208 as $result = $this->portVm1UserfieldValues()

      install.sql is also modified to include new entry " `userfield_values` longblob, "  in Table `#__virtuemart_migration_oldtonew_ids`

Note: table #__virtuemart_userfield_values is not purged/re-installed as part to DB Tools "Remove Virtuemart Tables" and "Install or if necessary update tables" so there is the side effect of this only updating on the first attempt to Migrate the data.

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 portVm1UserfieldValues()

   /**
    *
    * Port VM1 Userfield Values
    * Assumes UserFields have already been ported across via JUpgrade
    *
    *
    * @author M. Barry
    */
   public function portVm1UserfieldValues() {
   
      if($this->_stop || (microtime(true)-$this->starttime) >= ($this->maxScriptTime)){
         return;
      }
      
      vmInfo('Migration: processing UserfieldValues ..... ');
      $alreadyKnownIds = $this->getMigrationProgress('userfield_values');
      $alreadyProcessed = count($alreadyKnownIds);
      $completionStatus = true;
      $i = 0;
      
      // get values to process
      $q = 'SELECT * FROM `#__vm_userfield_values` WHERE 1';
      $this->_db->setQuery($q);
      $userfieldValues= $this->_db->loadAssocList();
      $maxItems = count($userfieldValues);
   
      if (empty($userfieldValues)) {
         vmWarn('portVm1UserfieldValues : No VM1 userfield values exist');
         return;
      }
   
      foreach ($userfieldValues as $userfieldValue) {
            
         if(array_key_exists($userfieldValue['fieldvalueid'],$alreadyKnownIds)) {
            continue;
         }

         // get the name of the userfield for which the userfield_value applies 
         $q = 'SELECT `u`.`name` FROM `#__vm_userfield` AS `u` JOIN `#__vm_userfield_values` AS `uv` ON `uv`.`fieldid` = `u`.`fieldid` WHERE `u`.`fieldid` = "'.$userfieldValue['fieldid'].'"';           
         $this->_db->setQuery($q);
         $fieldName = $this->_db->loadResult();
               
         if(empty($fieldName)) {
            vmError('portVm1UserfieldValues : fieldId does not exist for field ID '.$userfieldValue['fieldid'].' in Table #__vm_userfield_values');
            continue;
         }
         
         // search for the same userfield name in the VM2 table and if exists return the virtuemart_userfield_id
         $q = 'SELECT `vu`.`virtuemart_userfield_id` FROM `#__virtuemart_userfields` AS `vu` JOIN `#__vm_userfield` AS `u` ON `vu`.`name` = `u`.`name` WHERE `u`.`name` = "'.$fieldName.'"';
         $this->_db->setQuery($q);
         $userfieldId = $this->_db->loadResult();
            
         if(empty($userfieldId)) {
            vmError('portVm1UserfieldValues : field name'.$fieldName.' does not exist in Table #__virtuemart_userfields');
            continue;
         }
         
         // Check if userfield_value has already been loaded into VM2 tables
         // need to select on both virtuemart_userfield_id AND fieldtitle to ensure a unique return value
         $q = 'SELECT * FROM `#__virtuemart_userfield_values` WHERE `#__virtuemart_userfield_values`.`virtuemart_userfield_id` = "'.$userfieldId.'" AND `#__virtuemart_userfield_values`.`fieldtitle`="'.$userfieldValue['fieldtitle'].'"';
         $this->_db->setQuery($q);
         $values = $this->_db->loadAssocList();
         if(empty($values)) {
                        
            $data['virtuemart_userfield_id'] = $userfieldId;
            $data['fieldtitle'] = "'".$userfieldValue['fieldtitle']."'";
            $data['fieldvalue'] = "'".$userfieldValue['fieldvalue']."'";
            $data['sys'] = $userfieldValue['sys'];
            $data['ordering'] = $userfieldValue['ordering'];
            
            $date = JFactory::getDate();
            $today = $date->toMySQL();
            $user = JFactory::getUser();
            
            $data['created_on'] = "'".$today."'";
            $data['created_by'] = $user->id;
            $data['modified_on'] = "'".$today."'";
            $data['modified_by'] = $user->id;
            $data['locked_on'] = "'0000-00-00 00:00:00'";
            $data['locked_by'] = 0;
            
            // no simple model exists to store userfield values directly
            // there is a model for "userfields" which could be adapted but too complex as is
            // so insert row into table directly         
            $q = 'INSERT INTO `#__virtuemart_userfield_values` (
                  `virtuemart_userfield_value_id`,
                  `virtuemart_userfield_id`,
                  `fieldtitle`,
                  `fieldvalue`,
                  `sys`,
                  `ordering`,
                  `created_on`,
                  `created_by`,
                  `modified_on`,
                  `modified_by`,
                  `locked_on`,
                  `locked_by`
               ) VALUES (
                  NULL,
                  '.$data['virtuemart_userfield_id'].',
                  '.$data['fieldtitle'].',
                  '.$data['fieldvalue'].',
                  '.$data['sys'].',
                  '.$data['ordering'].',
                  '.$data['created_on'].',
                  '.$data['created_by'].',
                  '.$data['modified_on'].', 
                  '.$data['modified_by'].',
                  '.$data['locked_on'].',
                  '.$data['locked_by'].');';
            
            $this->_db->setQuery($q);
            if(!$this->_db->query()){
               vmError('portVm1UserfieldValues : Unable to insert userfield value '.$data['fieldtitle']);
               continue;
            }

            // store result to avoid processing again
            $q = 'SELECT `virtuemart_userfield_value_id` FROM `#__virtuemart_userfield_values` WHERE `#__virtuemart_userfield_values`.`virtuemart_userfield_id` = "'.$userfieldId.'" AND `#__virtuemart_userfield_values`.`fieldtitle`="'.$userfieldValue['fieldtitle'].'"';
            $this->_db->setQuery($q);
            $virtuemartUserfieldValueId = $this->_db->loadResult();
            
            $alreadyKnownIds[$userfieldValue['fieldvalueid']] = (int) $virtuemartUserfieldValueId;      
         }
            
         $i++;
         if((microtime(true)-$this->starttime) >= ($this->maxScriptTime)){
            break;
         }
      }
      $this->storeMigrationProgress('userfield_values',$alreadyKnownIds);
      vmInfo('Migration: '.$i.' userfield_values processed this run, already processed '.$alreadyProcessed.' of '.$maxItems);
      if(($alreadyProcessed+$i) == $maxItems) $completionStatus = true;
      return $completionStatus;
   }