Send New Product Review Notification Email

January 13, 2015  |  1 Comments  |  by Raj (MagePsycho)  |  Latest, Magento

This is probably a feature which store owner might be looking for.
As of now store owner has to check for new product reviews from Magento backend in frequent manner.

Won’t it be better if admin gets a instant email whenever a new product review is posted? So that whatever action needs to be carried out with the pending review can be done quickly. You don’t need to buy expensive extension in order to get job done. Here I will be sharing a simple trick to achieve this.

1. Crate a skeleton module(for example: MagePsycho_Reviewnotifier) and register the event: review_save_after
File: app/code/local/MagePsycho/Reviewnotifier/etc/config.xml

<frontend>
    ...
    <events>
        <review_save_after>
            <observers>
                <magePsycho_reviewnotifier_review_save_after>
                    <type>singleton</type>
                    <class>magePsycho_reviewnotifier/observer</class>
                    <method>reviewSaveAfter</method>
                </magePsycho_reviewnotifier_review_save_after>
            </observers>
        </review_save_after>
    </events>
    ...
</frontend>

2. Implement the observer model
File: app/code/local/MagePsycho/Reviewnotifier/Model/Observer.php

<?php

/**
 * Observer Model for Review Notifier
 * 
 * @author MagePsycho<magepsycho@gmail.com>
 * @package MagePsycho_Reviewnotifier
 * Class MagePsycho_Reviewnotifier_Model_Observer
 */
class MagePsycho_Reviewnotifier_Model_Observer
{
    /**
     * Send notification email when product review is posted
     *
     * @param Varien_Event_Observer $observer
     */
    public function reviewSaveAfter(Varien_Event_Observer $observer)
    {
        $review = $observer->object;
        if ($review) {
            $emails     = array('info@yourstore.com', 'email1@yourstore.com'); #Edit admin emails
            try {
                //@todo - use configurable email templates
                $this->_sendNotificationEmail($emails, $review);
            } catch (Exception $e) {
                Mage::logException($e);
            }
        } else {
            Mage::log('ERROR::UNABLE TO LOAD REVIEW');
        }
    }

    protected function _sendNotificationEmail($emails, $review)
    {
        if (count($emails)) {
            $product        = Mage::getModel('catalog/product')->load($review->getEntityPkValue());
            $starRatings    = array_values($review->getRatings());

            $body = 'New product review has been posted!<br /><br />';
            $body .= 'Customer Name: ' . $review->getNickname() . '<br />';
            $body .= 'Product: ' . sprintf('<a href="%s" target="_blank">%s</a>', $product->getProductUrl(), $product->getName()) . '<br />';
            $body .= 'Star Rating: ' . (isset($starRatings[0]) ? $starRatings[0] : 'N/A') . '<br />';
            $body .= 'Review Title: ' . $review->getTitle() . '<br />';
            $body .= 'Review Description: ' . $review->getDetail() . '<br />';

            foreach ($emails as $toEmail) {
                $mail = Mage::getModel('core/email');
                $mail->setToName('YourStore Admin');
                $mail->setToEmail($toEmail);
                $mail->setBody($body);
                $mail->setSubject('YourStore: New Product Review');
                $mail->setFromEmail('donotreply@yourstore.com');
                $mail->setFromName("YourStore");
                $mail->setType('html');

                try {
                    $mail->send();
                }
                catch (Exception $e) {
                    Mage::logException($e);
                }
            }
        }
    }
}

Please leave your comments if it works for you or if you have any queries.

How to fix the issue: Product images missing in backend but not in frontend

June 22, 2012  |  54 Comments  |  by Raj (MagePsycho)  |  Latest, Magento

Introduction

Recently i have to fix a strange issue for one of my client. Issue was that product images were missing from ‘Images’ tab of product edit form (as depicted below), though they were displaying fine in the frontend.

Missing Product Images In Backend

After going through the table relationship for catalog product images (as shown below), it was found that required rows were missing in table ‘catalog_product_entity_media_gallery’. Note that table ‘catalog_product_entity_media_gallery_value’ is not necessary unless you want to sort or label the product images.

Schema For Product Images

By inspecting the html code of Images tab of product edit form via firebug, it was seen that ‘value’ attribute had empty json:

<input type="hidden" value="[]" name="product[media_gallery][images]" id="media_gallery_content_save">

Further Debugging:
Using template path hints extension for admin: Easy Template Path Hints, it was found that the responsible template and block class were: ‘app/design/adminhtml/default/default/template/catalog/product/helper/gallery.phtml’ and ‘Mage_Adminhtml_Block_Catalog_Product_Helper_Form_Gallery_Content’ respectively.

Looking at the block class and .phtml code it was found that Input element has empty json because of Mage_Adminhtml_Block_Catalog_Product_Helper_Form_Gallery_Content::getImagesJson() method.

When populated the images from table ‘catalog_product_entity_varchar’ to ‘catalog_product_entity_media_gallery’ for those products, products images were seen at the backend(refer to the code below for more info).
Here goes the steps for fixing:

Steps

1> Prepare CSV file(update_missing_images.csv) with only one field: sku and upload to the root of Magento installation.
Note: This will contain the sku of those products whose images are missing at the backend.

2> Create a file: update_missing_images.php and upload to the root of magento installation and paste the following code:

<?php
/**
 * @author		MagePsycho <info@magepsycho.com>
 * @website		http://www.magepsycho.com
 * @category	Export / Import
 */
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

set_time_limit(0);
ini_set('memory_limit','1024M');

/***************** UTILITY FUNCTIONS ********************/
function _log($message, $file = 'update_missing_images.log'){
	Mage::log($message, null, $file);
}

function _getIndex($field) {
	global $fields;
	$result = array_search($field, $fields);
	if($result === false){
		$result = -1;
	}
	return $result;
}

function _getConnection($type = 'core_read'){
	return Mage::getSingleton('core/resource')->getConnection($type);
}

function _getTableName($tableName){
	return Mage::getSingleton('core/resource')->getTableName($tableName);
}

function _getAttributeId($attribute_code = 'price'){
	$connection = _getConnection('core_read');
	$sql = "SELECT attribute_id
				FROM " . _getTableName('eav_attribute') . "
			WHERE
				entity_type_id = ?
				AND attribute_code = ?";
	$entity_type_id = _getEntityTypeId();
	return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
}

function _getEntityTypeId($entity_type_code = 'catalog_product'){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
	return $connection->fetchOne($sql, array($entity_type_code));
}

function _getIdFromSku($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
	return $connection->fetchOne($sql, array($sku));
}

function _checkIfSkuExists($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . "	WHERE sku = ?";
	$count		= $connection->fetchOne($sql, array($sku));
	if($count > 0){
		return true;
	}else{
		return false;
	}
}

function _checkIfRowExists($productId, $attributeId, $value){
	$tableName  = _getTableName('catalog_product_entity_media_gallery');
	$connection = _getConnection('core_read');
	$sql		= "SELECT COUNT(*) AS count_no FROM " . _getTableName($tableName) . " WHERE entity_id = ? AND attribute_id = ?  AND value = ?";
	$count		= $connection->fetchOne($sql, array($productId, $attributeId, $value));
	if($count > 0){
		return true;
	}else{
		return false;
	}
}

function _insertRow($productId, $attributeId, $value){
	$connection				= _getConnection('core_write');
	$tableName				= _getTableName('catalog_product_entity_media_gallery');

	$sql = "INSERT INTO " . $tableName . " (attribute_id, entity_id, value) VALUES (?, ?, ?)";
	$connection->query($sql, array($attributeId, $productId, $value));
}

function _updateMissingImages($count, $productId, $data){
	$connection				= _getConnection('core_read');
	$smallImageId			= _getAttributeId('small_image');
	$imageId				= _getAttributeId('image');
	$thumbnailId			= _getAttributeId('thumbnail');
	$mediaGalleryId			= _getAttributeId('media_gallery');

	//getting small, base, thumbnail images from catalog_product_entity_varchar for a product
	$sql	= "SELECT * FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE attribute_id IN (?, ?, ?) AND entity_id = ? AND `value` != 'no_selection'";
	$rows	= $connection->fetchAll($sql, array($imageId, $smallImageId, $thumbnailId, $productId));
	if(!empty($rows)){
		foreach($rows as $_image){
			//check if that images exist in catalog_product_entity_media_gallery table or not
			if(!_checkIfRowExists($productId, $mediaGalleryId, $_image['value'])){
				//insert that image in catalog_product_entity_media_gallery if it doesn't exist
				_insertRow($productId, $mediaGalleryId, $_image['value']);
				/* Output / Logs */
				$missingImageUpdates = $count . '> Updated:: $productId=' . $productId . ', $image=' . $_image['value'];
				echo $missingImageUpdates.'<br />';
				_log($missingImageUpdates);
			}
		}
		$separator = str_repeat('=', 100);
		_log($separator);
		echo $separator . '<br />';
	}
}
/***************** UTILITY FUNCTIONS ********************/

$messages			= array();
$csv				= new Varien_File_Csv();
$data				= $csv->getData('update_missing_images.csv'); //path to csv
$fields				= array_shift($data);
#print_r($fields); print_r($data); exit;

$message = '<hr />';
$count   = 1;
foreach($data as $_data){
	$sku									= isset($_data[_getIndex('sku')]) ? trim($_data[_getIndex('sku')]) : '';
	if(_checkIfSkuExists($sku)){
		try{
			$productId = _getIdFromSku($sku);
			_updateMissingImages($count, $productId, $_data);
			$message .= $count . '> Success:: While Updating Images of Sku (' . $sku . '). <br />';

		}catch(Exception $e){
			$message .=  $count .'> Error:: While Upating Images of Sku (' . $sku . ') => '.$e->getMessage().'<br />';
		}
	}else{
		$message .=  $count .'> Error:: Product with Sku (' . $sku . ') does\'t exist.<br />';
	}
	$count++;
}
echo $message;

3> Open your browser and run the following url:
http://your-magento-url/update_missing_images.php

4> That’s all. Try to browse the Images tab, now you will see those missing images.

Hope this helps somebody!

Magento EAV structure & role of eav_attribute’s backend_type field

April 8, 2012  |  3 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction:

Magento database is based on EAV(Entity Attribute Value) architecture. And it uses this architecture especially for categories, products, customers & customer addresses.
Before Magento version 1.4.x it used EAV structure for orders as well but thereafter order’s eav structure has been converted to flat structure.

From the defination of EAV:

Entity(E):

Entity actually refers to data item.
In Magento:

catalog_category_entity
catalog_product_entity
customer_entity
customer_address_entity

are the entity tables for categories, products, customers & customer addresses respectively.

Attribute(A):

Attribute refers to the different attributes of an Entity.
In Magento, attributes related to an entity are stored in a single table: eav_attribute but differentiated by field: entity_type_id.

We can easily find all the attributes related to an entity using the following SQL query(For Example: product):

SELECT 
  attribute_code,
  attribute_id,
  backend_type 
FROM
  eav_attribute 
WHERE entity_type_id = 
  (SELECT 
    entity_type_id 
  FROM
    eav_entity_type 
  WHERE entity_type_code = 'catalog_product')

Which gives the following results:

+----------------------------+--------------+--------------+
| attribute_code             | attribute_id | backend_type |
+----------------------------+--------------+--------------+
| activation_information     |          496 | text         |
| color                      |          272 | int          |
| color_code                 |          950 | varchar      |
| computer_manufacturers     |          510 | int          |
| contrast_ratio             |          875 | int          |
| cost                       |          100 | decimal      |
| country_orgin              |          507 | text         |
| cpu_speed                  |          877 | int          |
| created_at                 |          930 | static       |
| custom_design              |          571 | varchar      |
| custom_design_from         |          572 | datetime     |
| custom_design_to           |          573 | datetime     |
| custom_layout_update       |          531 | text         |
| description                |           97 | text         |
| dimension                  |          494 | text         |
| enable_googlecheckout      |          903 | int          |
| finish                     |          509 | text         |
| gallery                    |          271 | varchar      |
| gender                     |          501 | int          |
| gift_message_available     |          562 | varchar      |
| harddrive_speed            |          878 | varchar      |
| hardrive                   |          499 | text         |
| has_options                |          838 | static       |
| image                      |          106 | varchar      |
| image_label                |          879 | varchar      |
| in_depth                   |          492 | text         |
| is_imported                |          949 | int          |
| is_recurring               |          933 | int          |
| links_exist                |          947 | int          |
| links_purchased_separately |          904 | int          |
| links_title                |          906 | varchar      |
| manufacturer               |          102 | int          |
| max_resolution             |          873 | varchar      |
| media_gallery              |          703 | varchar      |
| megapixels                 |          513 | int          |
| memory                     |          498 | text         |
| meta_description           |          105 | varchar      |
| meta_keyword               |          104 | text         |
| meta_title                 |          103 | varchar      |
| minimal_price              |          503 | decimal      |
| model                      |          495 | text         |
| name                       |           96 | varchar      |
| news_from_date             |          704 | datetime     |
| news_to_date               |          705 | datetime     |
| old_id                     |          110 | int          |
| options_container          |          836 | varchar      |
| package_id                 |          951 | int          |
| page_layout                |          929 | varchar      |
| price                      |           99 | decimal      |
| price_type                 |          859 | int          |
| price_view                 |          862 | int          |
| processor                  |          497 | text         |
| ram_size                   |          874 | varchar      |
| recurring_profile          |          934 | text         |
| required_options           |          837 | static       |
| response_time              |          876 | varchar      |
| room                       |          508 | int          |
| samples_title              |          905 | varchar      |
| screensize                 |          500 | text         |
| shape                      |          476 | text         |
| shipment_type              |          863 | int          |
| shipping_qty               |          952 | int          |
| shirt_size                 |          525 | int          |
| shoe_size                  |          502 | int          |
| shoe_type                  |          107 | int          |
| short_description          |          506 | text         |
| sku                        |           98 | static       |
| sku_type                   |          860 | int          |
| small_image                |          109 | varchar      |
| small_image_label          |          880 | varchar      |
| special_from_date          |          568 | datetime     |
| special_price              |          567 | decimal      |
| special_to_date            |          569 | datetime     |
| status                     |          273 | int          |
| tax_class_id               |          274 | int          |
| thumbnail                  |          493 | varchar      |
| thumbnail_label            |          881 | varchar      |
| tier_price                 |          270 | decimal      |
| updated_at                 |          931 | static       |
| url_key                    |          481 | varchar      |
| url_path                   |          570 | varchar      |
| visibility                 |          526 | int          |
| weight                     |          101 | decimal      |
| weight_type                |          861 | int          |
+----------------------------+--------------+--------------+

Similarly you can find attributes for other entities just by using following values for entity_type_code:

catalog_category
customer
customer_address

for categories, customers & customer address respectively.

Note: You can get the related values for entity_type_code from table: eav_entity_type.

Value(V):

Value refers to the actual value of the attribute of the entity.
In Magento attribute values of an entity (for example: product) are stored in catalog_product_entity_[backend_type] tables.
Where [backend_type] refers to the value of field: backend_type (except value ‘static’) of table: eav_attribute.

Following SQL is used to find all the backend types related to a product entity:

SELECT DISTINCT 
  backend_type 
FROM
  eav_attribute 
WHERE entity_type_id = 
  (SELECT 
    entity_type_id 
  FROM
    eav_entity_type 
  WHERE entity_type_code = 'catalog_product')

Which results:

+--------------+
| backend_type |
+--------------+
| text         |
| int          |
| varchar      |
| decimal      |
| static       |
| datetime     |
+--------------+

So tables:

catalog_product_entity_text 
catalog_product_entity_int 
catalog_product_entity_varchar 
catalog_product_entity_decimal
catalog_product_entity_datetime

are used for storing values for an attributes of related backend_type.

Note: You must be wondering about backend_type = static. For an attribute with the static backend_type, values are stored as a column directly in the entity table. For example: sku is an attribute of backend_type = static and it’s values are stored in the entity table itself: catalog_product_entity under field: sku.

That’s all about the definition of EAV relating to Magento.
Hopefully now you are able to differentiate which attribute values goes where (in which table) based on the backend_type.

Thanks for reading & Happy E-Commerce!!

Playing with Attribute Set in Magento

November 18, 2011  |  6 Comments  |  by Raj (MagePsycho)  |  Latest, Magento

Introduction

An attribute set is a collection of attributes which can be created/edited from Catalog -> Manage Attribute Sets in backend.

Some useful snippets regarding Attribute Set.

1> Getting Attribute Set Details

$attributeSetModel = Mage::getModel('eav/entity_attribute_set');
$attributeSetId	   = 9; // or $_product->getAttributeSetId(); if you are using product model
$attributeSetModel->load($attributeSetId);
print_r($attributeSetModel->getData());

2> Getting Attribute Set Id by Name

$entityTypeId = Mage::getModel('eav/entity')
				->setType('catalog_product')
				->getTypeId();
$attributeSetName	= 'Default';
$attributeSetId		= Mage::getModel('eav/entity_attribute_set')
					->getCollection()
					->setEntityTypeFilter($entityTypeId)
					->addFieldToFilter('attribute_set_name', $attributeSetName)
					->getFirstItem()
					->getAttributeSetId();
echo $attributeSetId;

Note that the following code doesn’t work

$attributeSetId = Mage::getModel('eav/entity_attribute_set')
->load($attributeSetName, 'attribute_set_name')
->getAttributeSetId();

Because if you refer to eav_attribute_set table then you can see there are lots of rows with same attribute set name(for example: Default)

3> Getting all Attribute Sets by Entity Type(catalog_product)

$entityTypeId = Mage::getModel('eav/entity')
				->setType('catalog_product')
				->getTypeId();
$attributeSetCollection = Mage::getModel('eav/entity_attribute_set')
				->getCollection()
				->setEntityTypeFilter($entityTypeId);
foreach($attributeSetCollection as $_attributeSet){
	print_r($_attributeSet->getData());
}

Alternatively, you can also use:

$attributeSets = Mage::getModel('catalog/product_attribute_set_api')->items();
foreach($attributeSets as $_attributeSet){
	print_r($_attributeSet);
}

4> Getting all attributes in an Attribute Set

$attributes = Mage::getModel('catalog/product_attribute_api')->items($attributeSetId);
foreach($attributes as $_attribute){
	print_r($_attribute);
}

5> Filtering Products by Attribute Set

$productCollection = Mage::getModel('catalog/product')
					->getCollection()
					->addAttributeToSelect('*')
					->addFieldToFilter('attribute_set_id', $attributeSetId);
foreach($productCollection as $_product){
	print_r($_product->getData());
}

Hope you will find these snippets useful/helpful.
Please do share if you have any useful code snippets regarding Attribute Set.

Thanks for reading!

Magento utility function: How to easily create select box for drop-down attributes?

November 13, 2011  |  12 Comments  |  by Raj (MagePsycho)  |  Latest, Magento

From now onward i will be sharing custom utility functions on regular basis which will make your programming life easier to some extent at least in case of Magento 🙂

Here i am going to share an utility function which will help you to create select box for drop-down attributes(manufacturer, color, size etc.) of a product.

Utility Function:

function getSelectBox($attributeCode, $label = '', $defaultSelect = null, $extraParams = null){
	$options			= array();
	$product			= Mage::getModel('catalog/product');
	$attribute			= $product->getResource()->getAttribute($attributeCode);
	if($attribute->usesSource()){
		$options = $attribute->getSource()->getAllOptions(false);
		array_unshift($options, array('label' => $label, 'value' => ''));
	}

	$select = Mage::app()->getLayout()->createBlock('core/html_select')
			->setName($attributeCode)
			->setId($attributeCode)
			->setTitle($label)
			->setValue($defaultSelect)
			->setExtraParams($extraParams)
			->setOptions($options);
	return $select->getHtml();
}

Now you can easily draw select box for any drop-down attribute (for example: manufacturer) by simply calling the above function as:

<?php echo getSelectBox('manufacturer', 'Select Manufacturer'); ?>

Output:

Select Box - Manufacturer

Note: You can also pass default value to be selected, extra parameter like css class, javascript event handlers etc via function arguments. Or you can customize the function easily as per your requirements.

You can define the above function in your Helper class and can call from anywhere within your application in order to create select box for any drop-down attributes of product.

Happy Coding!!