Magento 1 评论数据的导出导入

导出评论

首先新建一个sql文件export-reviews.sql：放到你的网站根目录下面

select
 catalog_product_entity.sku,
 review_detail.store_id,
 review.status_id,
 customer_entity.email,
 review.created_at,
 review_detail.title,
 REPLACE(REPLACE(review_detail.detail, '\r', ''), '\n', '<br>') as detail,
 review_detail.nickname,
 IFNULL(rov1.value, 0) as rating__1__value,
 IFNULL(rov2.value, 0) as rating__2__value,
 IFNULL(rov3.value, 0) as rating__3__value
from review
inner join review_detail on review.review_id=review_detail.review_id
left join customer_entity on review_detail.customer_id=customer_entity.entity_id
inner join catalog_product_entity on review.entity_pk_value=catalog_product_entity.entity_id
left join rating_option_vote rov1 on rov1.review_id=review.review_id and rov1.rating_id=1
left join rating_option_vote rov2 on rov2.review_id=review.review_id and rov2.rating_id=2
left join rating_option_vote rov3 on rov3.review_id=review.review_id and rov3.rating_id=3
order by sku, created_at;

登录SSH 执行mysql命令：

mysql watch_ser -u root --password=你的数据库密码 < /www/wwwroot/网站目录/export-reviews.sql | sed "s/\"/\"\"/;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/" > /www/wwwroot/网站目录/reviews.csv

执行成功会提示你：Warning: Using a password on the command line interface can be insecure.不必理会。此时根目录下面已经生成了reviews.csv.

导入评论

新建一个PHP文件import-reviews.php内容如下，放到你要导入的目标站点根目录下面的shell目录下:

<?php

require_once 'abstract.php';

class Mage_Shell_Import_Reviews extends Mage_Shell_Abstract
{
    public function run()
    {
        if (!$this->getArg('file')) {
            die('Usage: php -f review_import.php -- -file /path/to/file.csv');
        }

        $product = false;
        $ratings = $this->getRatings();
        $data = $this->getData();

        foreach ($data as $row) {
            if (!$product || $product->getSku() !== $row['sku']) {
                $product = Mage::getModel('catalog/product')->loadByAttribute('sku', $row['sku']);
            }

            if (!$product) {
                continue;
            }

            $store = Mage::getModel('core/store')->load($row['store_id']);
            $customer = Mage::getModel('customer/customer')
                ->setWebsiteId($store->getWebsiteId())
                ->loadByEmail($row['email']);

            Mage::getSingleton('customer/session')
                ->setCustomer($customer)
                ->setCustomerAsLoggedIn($customer);

            $review = Mage::getModel('review/review')
                ->setEntityPkValue($product->getId())
                ->setStatusId($row['status_id'])
                ->setTitle($row['title'])
                ->setDetail(str_replace('<br>', "\n", $row['detail']))
                ->setEntityId(1)
                ->setStoreId($row['store_id'])
                ->setStores(array($row['store_id']))
                ->setCustomerId($customer->getId())
                ->setNickname($row['nickname'])
                ->save();

            foreach ($ratings as $ratingId => $options) {
                $ratingValue = $row["rating__{$ratingId}__value"];

                if (!isset($options[$ratingValue])) {
                    continue;
                }

                Mage::getModel('rating/rating')
                    ->setRatingId($ratingId)
                    ->setReviewId($review->getId())
                    ->addOptionVote($options[$ratingValue], $product->getId());
            }

            $review->aggregate();
            $review->setCreatedAt($row['created_at']);
            $review->save();
        }
    }

    private function getData()
    {
        $csv = new Varien_File_Csv;
        $data = $csv->getData($this->getArg('file'));
        $headers = $data[0];
        unset($data[0]);
        foreach ($data as $key => $row) {
            $data[$key] = array_combine($headers, $row);
        }
        return array_values($data);
    }

    private function getRatings()
    {
        $ratings = [];
        $options = Mage::getModel('rating/rating_option')->getCollection();
        foreach ($options as $option) {
            $ratings[$option->getRatingId()][$option->getValue()] =
                $option->getOptionId();
        }
        return $ratings;
    }
}

$app = new Mage_Shell_Import_Reviews();
$app->run();

将之前生成的review数据csv也放到目标站点shell文件夹

执行导入前请务必保证导入的目标站里面存在review数据里面的产品SKU.

SSH执行命令：

php -f import-reviews.php -- -file reviews.csv