gistfile1.txt

<?php
/**
 * Magento Enterprise Edition
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Magento Enterprise Edition License
 * that is bundled with this package in the file LICENSE_EE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.magentocommerce.com/license/enterprise-edition
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to [email protected] so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade Magento to newer
 * versions in the future. If you wish to customize Magento for your
 * needs please refer to http://www.magentocommerce.com for more information.
 *
 * @category    Ai
 * @package     Ai_Migration
 * @copyright   Copyright (c) 2010 Magento Inc. (http://www.magentocommerce.com)
 * @license     http://www.magentocommerce.com/license/enterprise-edition
 */

class Ai_Migration_Model_Feed_Handler_Product_Urlpath extends Ai_Migration_Model_Feed_Handler
{
    protected $_entityTypeCode  = Mage_Catalog_Model_Product::ENTITY;

    public function afterComplete()
    {
        $this->_prepareProductUrlPath();
        $this->_index();
    }

    /**
     * Build url_path for product.
     *
     * @return Ai_Migration_Model_Feed_Handler_Product_Urlpath
     */
    protected function _prepareProductUrlPath()
    {
        /** @var $urlKeyAttribute Mage_Eav_Model_Entity_Attribute */
        $urlKeyAttribute = $this->_getAttribute('url_key');
        /** @var $urlPathAttribute Mage_Eav_Model_Entity_Attribute */
        $urlPathAttribute = $this->_getAttribute('url_path');

        $resource = $this->_getResource();
        /** @var $read Varien_Db_Adapter_Pdo_Mysql */
        $read     = $resource->getReadConnection();
        /** @var $write Varien_Db_Adapter_Pdo_Mysql */
        $write    = $this->_writeAdapter;
        /** @var $helper Mage_Catalog_Helper_Product */
        $helper   = Mage::helper('catalog/product');

        $linkTable              = $this->_prepareFeedTableName('entity_link');
        $categoryTable          = $resource->getTable('catalog/category');
        $productTable           = $resource->getTable('catalog/product');
        $fields                 = array('entity_id', 'entity_type_id', 'attribute_id', 'store_id', 'value');
        /**
         * Prepare main url_path column for products
         * Example: product-1.html
         */
        $urlPath = sprintf("CONCAT(%s, %s)", $read->quoteIdentifier('pv.value'), $read->quote($helper->getProductUrlSuffix()));
        $select = $read->select()
            ->from(array('p' => $productTable), array(
                'entity_id',
                'entity_type_id' => new Zend_Db_Expr($urlPathAttribute->getEntityTypeId()),
                'attribute_id'   => new Zend_Db_Expr($urlPathAttribute->getId()),
                'store_id'       => new Zend_Db_Expr(Mage_Core_Model_App::ADMIN_STORE_ID),
                'value' => new Zend_Db_Expr($urlPath)
            ))
            ->joinInner(array('pv' => $urlPathAttribute->getBackendTable()),
                implode(' AND ', array(
                    $read->quoteIdentifier('pv.attribute_id') . ' = ' . $urlKeyAttribute->getId(),
                    $read->quoteIdentifier('pv.entity_id') . ' = ' . $read->quoteIdentifier('p.entity_id')
                )),
                array()
            );

        $insert = $resource->insertFromSelect($select, $urlPathAttribute->getBackendTable(), $fields, Ai_Migration_Model_Resource_Db::INSERT_ON_DUPLICATE, array('value'));
        $write->query($insert);
        sleep(1);

        return $this;
    }

    /**
     * Build url_path for product which assigned to the categories.
     *
     * @return Ai_Migration_Model_Feed_Handler_Product_Urlpath
     */
    protected function _index()
    {
        /** @var $urlPathAttribute Mage_Eav_Model_Entity_Attribute */
        $urlPathAttribute = $this->_getAttribute('url_path');
        /** @var $categoryUrlPathAttribute Mage_Eav_Model_Entity_Attribute */
        $categoryUrlPathAttribute = Mage::getModel('eav/config')->getAttribute(Mage_Catalog_Model_Category::ENTITY, 'url_path');

        $resource = $this->_getResource();
        /** @var $read Varien_Db_Adapter_Pdo_Mysql */
        $read     = $resource->getReadConnection();
        /** @var $write Varien_Db_Adapter_Pdo_Mysql */
        $write    = $this->_writeAdapter;
        /** @var $helper Mage_Catalog_Helper_Category */
        $helper   = Mage::helper('catalog/category');

        $categoryProductTable = $resource->getTable('catalog/category_product');
        $productTable = $resource->getTable('catalog/product');
        $urlPathTable         = $urlPathAttribute->getBackendTable();
        $categoryUrlPathTable = $categoryUrlPathAttribute->getBackendTable();
        $coreUrlRewriteTable  = $resource->getTable('core/url_rewrite');

        $pathValue = sprintf("CONCAT(SUBSTRING_INDEX(%s, %s, 1), '/')",
            $read->quoteIdentifier('cv.value'), $read->quote($helper->getCategoryUrlSuffix()));
        $subSelect = $read->select()
            ->from(array('pv' => $urlPathTable), array(
                'path' => new Zend_Db_Expr($pathValue),
                //'pv.value',
                'CONCAT(cpe.sku, ".html") as value',
                'pv.entity_id',
            	'cpe.sku',
                'ccp.category_id'
            ))
            ->joinInner(array('ccp' => $categoryProductTable),
                $read->quoteIdentifier('ccp.product_id') . ' = ' . $read->quoteIdentifier('pv.entity_id') . ' AND ccp.category_id != ' . (int)Mage::app()->getDefaultStoreView()->getRootCategoryId(),
                array()
            )
            ->joinInner(array('cpe' => $productTable),
                $read->quoteIdentifier('cpe.entity_id') . ' = ' . $read->quoteIdentifier('pv.entity_id'),
                array()
            )
            ->joinLeft(array('cv' => $categoryUrlPathTable),
                implode(' AND ', array(
                    $read->quoteIdentifier('cv.entity_id') . ' = ' . $read->quoteIdentifier('ccp.category_id'),
                    $read->quoteIdentifier('cv.attribute_id') . ' = ' . $categoryUrlPathAttribute->getId()
                )),
                array()
            )
            ->where($read->quoteIdentifier('pv.attribute_id') . ' = ?', (int)$urlPathAttribute->getId())
            ->group('path')
            ->group('cv.value');

        $requestPathValue = sprintf('%s)',
            $read->quoteIdentifier('value'));
        $targetPathValue  = sprintf("CONCAT('catalog/product/view/id/', %s, '/category/', %s)",
            $read->quoteIdentifier('entity_id'), $read->quoteIdentifier('category_id'));
        $idPathValue      = sprintf("CONCAT('product', '/', %s, '/', %s)",
            $read->quoteIdentifier('entity_id'), $read->quoteIdentifier('category_id'));

        $fields = array('store_id', 'category_id', 'product_id', 'id_path', 'request_path', 'target_path', 'is_system');
        foreach (Mage::app()->getStores() as $store) {
            $select = $read->select()
                ->from(array('sub' => new Zend_Db_Expr(sprintf('(%s)', $subSelect))), array(
                    'store_id'     => new Zend_Db_Expr($store->getId()),
                    'category_id'  => 'category_id',
                    'product_id'   => 'entity_id',
                    'id_path'      => new Zend_Db_Expr($idPathValue),
                    'request_path' => new Zend_Db_Expr(str_replace('US    ', 'used-', $requestPathValue)),
                    'target_path'  => new Zend_Db_Expr($targetPathValue),
                    'is_system'    => new Zend_Db_Expr(1)
                ));

            $insert = $resource->insertFromSelect($select, $coreUrlRewriteTable, $fields, Ai_Migration_Model_Resource_Db::INSERT_ON_DUPLICATE);
            try {
                $write->query($insert);
                sleep(1);
            } catch(Exception $e) {
                Mage::log('Caught exception on INSERT: '.$e->getMessage());
                /* continue execution as we can safely ignore this
                 *  E.g. Caught exception on INSERT: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'product/692/113-1-1' for key 'UNQ_PATH'
                 * */
            }
        }

        return $this;
    }
}