Magento direct sql query


Hello Friend's,

Here some usefully mysql query.

========================================================================

Number Orders - Sales - Avg Per Order

*********************************************************************************

AVG Order Value Per Day

SELECT DATE(`created_at`) AS 'Date', AVG(`grand_total`) AS 'AVG Order Value Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC

--------------------------------------------------------------------------------------

AVG Order Value Per Week

SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per Week' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC

--------------------------------------------------------------------------------------

AVG Order Value Per Month

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`), MONTH(`created_at`) 
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC

--------------------------------------------------------------------------------------

AVG Order Value Per year

SELECT YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per year' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Sale Per Day

SELECT DATE(`created_at`) AS 'Date',SUM(`grand_total`) AS 'Total Sale Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Sale Per Week

SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per Week' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC

--------------------------------------------------------------------------------------

Total sales per month

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`), MONTH(`created_at`) 
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Sale Per year

SELECT YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per year' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC

--------------------------------------------------------------------------------------

Specific Month of year  sale

EX. 
" MONTH(`created_at`) = '01' " = Number of month(05)
" YEAR(`created_at`) = '2016' " = Number of year(2013)

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total sale',`status` FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' AND MONTH(`created_at`) = '01' AND YEAR(`created_at`) = '2016'

--------------------------------------------------------------------------------------

Specific Month wise yearly sale

EX. 
" YEAR(`created_at`) = '2015' " = Number of year(2013)

SELECT MONTHNAME(`created_at`) AS 'Month', SUM(`grand_total`) AS 'Total Sale Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' AND YEAR(`created_at`) = '2015' GROUP BY MONTH(`created_at`) ORDER BY MONTH(`created_at`) ASC

--------------------------------------------------------------------------------------

Total Order Per Day

SELECT DATE(`created_at`) AS 'Date', COUNT(*) AS 'Total Order Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Order Per Week

SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Week' FROM `sales_flat_order` WHERE `store_id` = 2  GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Order Per Month

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY YEAR(`created_at`), MONTH(`created_at`) ORDER BY YEAR(`created_at`) DESC,MONTH(`created_at`) DESC

--------------------------------------------------------------------------------------

Total Order Per Year

SELECT YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Year' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC

*******************************************

Sales by New Customers (How many new and sales)

Consider created customers in current month customers as new customer

SELECT `sales_flat_order`.`customer_email`,`sales_flat_order`.`customer_id`,`customer_entity`.`created_at` AS 'Customer Created Date',COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` JOIN `customer_entity` ON `customer_entity`.`entity_id` = `sales_flat_order`.`customer_id` WHERE MONTH(`customer_entity`.`created_at`) = MONTH(NOW()) AND YEAR(`customer_entity`.`created_at`) = YEAR(NOW()) AND YEAR(`sales_flat_order`.`created_at`) = YEAR(NOW()) AND `sales_flat_order`.`store_id` = '2' GROUP BY `sales_flat_order`.`customer_email`

*********************************************************************************

Sales by Existing Customers

Consider not created customers in current month as existing customer

SELECT `sales_flat_order`.`customer_email`,`sales_flat_order`.`customer_id`,`customer_entity`.`created_at` AS 'Customer Created Date',`sales_flat_order`.`created_at` AS 'Order Created Date',COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` JOIN `customer_entity` ON `customer_entity`.`entity_id` = `sales_flat_order`.`customer_id` WHERE MONTH(`customer_entity`.`created_at`) != MONTH(NOW()) AND YEAR(`customer_entity`.`created_at`) != YEAR(NOW()) AND MONTH(`sales_flat_order`.`created_at`) = MONTH(NOW()) AND YEAR(`sales_flat_order`.`created_at`) = YEAR(NOW()) AND `sales_flat_order`.`store_id` = '2' GROUP BY `sales_flat_order`.`customer_email`

*********************************************************************************

Sales by Customers By Date

SELECT `customer_firstname`,`customer_lastname`,`customer_email`,`customer_id`, COUNT(*) AS 'Total Order Per Customer', DATE(`created_at`) AS 'Date' FROM `sales_flat_order` where `store_id` = 2 GROUP BY DATE(`created_at`), `customer_email` ORDER BY DATE(`created_at`) DESC

*********************************************************************************

Sales By State

SELECT `sales_flat_order_address`.`region`,count(*) FROM `sales_flat_order` JOIN `sales_flat_order_address` ON `sales_flat_order`.`entity_id` = `sales_flat_order_address`.`parent_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_address`.`address_type` = 'shipping' GROUP BY `sales_flat_order_address`.`region`

*********************************************************************************

Sales By Product

--------------------------------------------------------------------------------------

Total Sale for all product

SELECT `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order`.`status` = 'complete' GROUP BY `sales_flat_order_item`.`sku`

--------------------------------------------------------------------------------------

Total Sale for specific Product

SELECT `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete'

--------------------------------------------------------------------------------------

Total Sale for specific Product with specific Day

SELECT DATE(`sales_flat_order`.`created_at`) AS 'Date', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND DATE(`sales_flat_order`.`created_at`) = '2015-12-15'

--------------------------------------------------------------------------------------

Total Sale for specific Product with specific month period

SELECT YEAR(`sales_flat_order`.`created_at`) AS 'Year', MONTHNAME(`sales_flat_order`.`created_at`) AS 'Month', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND MONTH(`sales_flat_order`.`created_at`) = '10' AND YEAR(`sales_flat_order`.`created_at`) = '2014'

--------------------------------------------------------------------------------------

Total Sale for specific Product with specific year period

SELECT YEAR(`sales_flat_order`.`created_at`) AS 'Year', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND YEAR(`sales_flat_order`.`created_at`) = '2015'

*********************************************************************************

Sales by type of Customer


SELECT `customer_group`.`customer_group_code` AS 'Customer Role', COUNT(`sales_flat_order`.`customer_id`) AS 'Total Customer' FROM `sales_flat_order` JOIN `customer_group` ON `customer_group`.`customer_group_id` = `sales_flat_order`.`customer_group_id` where `sales_flat_order`.`store_id` = 2 GROUP BY `customer_group`.`customer_group_code`

--------------------------------------------------------------------------------------

Sales by specific type of Customer
EX. - 'Wholesale'

SELECT `sales_flat_order`.`entity_id`, `sales_flat_order`.`customer_id`,`sales_flat_order`.`customer_email`,`customer_group`.`customer_group_code` FROM `sales_flat_order`
JOIN `customer_group` ON `customer_group`.`customer_group_id` = `sales_flat_order`.`customer_group_id`
where `sales_flat_order`.`store_id` = 2 AND `customer_group`.`customer_group_code` = 'Wholesale'

*********************************************************************************

Sales by customer on specific period (Report that show how many times during year)

SELECT YEAR(`created_at`) AS 'Year',`customer_firstname`,`customer_lastname`,`customer_email` AS 'Customer Email',`customer_id`,COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` WHERE `store_id` = 2 AND YEAR(`created_at`) = '2015' GROUP BY `customer_email` ORDER BY COUNT(`customer_email`) DESC

*********************************************************************************

Birthdays per month (List Customers)

SELECT `customer_entity`.`email`,`customer_entity_varchar`.`value` FROM `customer_entity` JOIN `customer_entity_varchar` ON `customer_entity`.`entity_id` = `customer_entity_varchar`.`entity_id` WHERE `customer_entity_varchar`.`attribute_id` = 11 AND MONTH(`customer_entity_varchar`.`value`) = MONTH(NOW())

*********************************************************************************

Number of orders per customers

SELECT `customer_email` AS 'Customer Email',COUNT(`customer_email`) AS 'Total Order By Customer' FROM `sales_flat_order` WHERE `store_id` = 2  GROUP BY `customer_email` ORDER BY COUNT(`customer_email`) DESC


*********************************************************************************

Show all customer attribute data from Magento database

SELECT ce.*, ea.attribute_code, 
    CASE ea.backend_type 
       WHEN 'varchar' THEN ce_varchar.value
       WHEN 'int' THEN ce_int.value
       WHEN 'text' THEN ce_text.value
       WHEN 'decimal' THEN ce_decimal.value
       WHEN 'datetime' THEN ce_datetime.value
       ELSE NULL
    END AS value
  FROM customer_entity AS ce 
  LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
  LEFT JOIN customer_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
  LEFT JOIN customer_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
  LEFT JOIN customer_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
  LEFT JOIN customer_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
  LEFT JOIN customer_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'


*********************************************************************************

Update status form "fulfillment" to "complete" in sales order table

SELECT * FROM `sales_flat_order` where `store_id` = 2 AND `state` = 'complete' AND `status` = 'fulfillment'

UPDATE `sales_flat_order` SET `status` = 'complete' where `store_id` = 2 AND `state` = 'complete' AND `status` = 'fulfillment'

----------------------------------------------------------------------------------------

Update status form "fulfillment" to "complete" in sales order grid table

SELECT sg.* FROM `sales_flat_order_grid` as sg JOIN `sales_flat_order` as s ON s.`entity_id` = sg.`entity_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment'

UPDATE `sales_flat_order_grid` as sg JOIN `sales_flat_order` as s ON s.`entity_id` = sg.`entity_id` SET sg.`status` = 'complete' WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment'

----------------------------------------------------------------------------------------

Delete status "fulfillment" form sales order status history table

SELECT sh.* FROM `sales_flat_order_status_history` as sh JOIN `sales_flat_order` as s ON s.`entity_id` = sh.`parent_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment' AND sh.`status` = 'fulfillment' AND sh.`entity_name` = 'order'

DELETE sh.* FROM `sales_flat_order_status_history` as sh JOIN `sales_flat_order` as s ON s.`entity_id` = sh.`parent_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment' AND sh.`status` = 'fulfillment' AND sh.`entity_name` = 'order'


*********************************************************************************

Update order table date (day light saving)

UPDATE `sales_flat_order` 
SET `created_at`= ADDDATE(`created_at`, INTERVAL 60 MINUTE) 
WHERE `store_id`= 3 AND (created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2012-11-04 02:00:00' AND '2013-03-10 02:00:00'
OR created_at BETWEEN '2013-11-03 02:00:00' AND '2014-03-09 02:00:00'
OR created_at BETWEEN '2014-11-02 02:00:00' AND '2015-03-08 02:00:00'
OR created_at BETWEEN '2015-11-01 02:00:00' AND '2016-03-13 02:00:00');

----------------------------------------------------------------------------------------

Update order grid table date (day light saving)

UPDATE `sales_flat_order_grid` 
SET `created_at`= ADDDATE(`created_at`, INTERVAL 60 MINUTE) 
WHERE `store_id`= 3 AND (created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2012-11-04 02:00:00' AND '2013-03-10 02:00:00'
OR created_at BETWEEN '2013-11-03 02:00:00' AND '2014-03-09 02:00:00'
OR created_at BETWEEN '2014-11-02 02:00:00' AND '2015-03-08 02:00:00'
OR created_at BETWEEN '2015-11-01 02:00:00' AND '2016-03-13 02:00:00');


*********************************************************************************

Get all refund

SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`total_refunded`) AS 'Total Refund Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `total_refunded` != '' GROUP BY YEAR(`created_at`), MONTH(`created_at`) 
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC


Magento Techniques to Show configurable product options in custom product listing with price change


Displaying product options like colors, sizes, price… in the custom product list gives an incentive to the customer to select and purchase a product. It is likely not possible to achieve these options by installing default Magento alone. In this article, we will recommend some effective methods to show options for the configurable and product options in custom product list. Check them out!

1. Create Productoptions module with Magebug namespace (app/local/Magebug/Productoptions)
2.Create a file named app/etc/modules/Magebug_Productoptions.xml to activate the module:
Here is code :

<config>
  <modules>
    <Magebug_Productoptions>
      <active>true</active>
      <codePool>local</codePool>
    </Magebug_Productoptions>
  </modules>

</config>

3. Create a file named app/local/Magebug/Productoptions/ect/config.xml with the following content

<?xml version="1.0"?>
<config>
    <modules>
        <magebug_productoptions>
            <version>1.0.0</version>
        </magebug_productoptions>
    </modules>
    <frontend>
        <routers>
            <productoptions>
                <use>standard</use>
                <args>
                    <module>Magebug_Productoptions</module>
                    <frontName>productoptions</frontName>
                </args>
            </productoptions>
        </routers>
        <layout>
            <updates>
                <productoptions>
                    <file>productoptions.xml</file>
                </productoptions>
            </updates>
        </layout>
    </frontend>
    <global>
        <blocks>
            <productoptions>
                <class>Magebug_Productoptions_Block</class>
            </productoptions>
        </blocks>
    </global>
</config>

4. Create a file named app/local/Magebug/Productoptions/Block/Productoptions.php to overwrite the block of Mage_Catalog_Block_Product_List with the following content:

<?php
class Magebug_Productoptions_Block_Productoptions extends Mage_Catalog_Block_Product_List {

    public function getPriceJsonConfig($product) {
        $config = array();
        if (!$product->getTypeInstance(true)->hasOptions($product)) {
            return Mage::helper('core')->jsonEncode($config);
        }

        $_request = Mage::getSingleton('tax/calculation')->getDefaultRateRequest();
        $_request->setProductClassId($product->getTaxClassId());
        $defaultTax = Mage::getSingleton('tax/calculation')->getRate($_request);

        $_request = Mage::getSingleton('tax/calculation')->getRateRequest();
        $_request->setProductClassId($product->getTaxClassId());
        $currentTax = Mage::getSingleton('tax/calculation')->getRate($_request);

        $_regularPrice = $product->getPrice();
        $_finalPrice = $product->getFinalPrice();
        if ($product->getTypeId() == Mage_Catalog_Model_Product_Type::TYPE_BUNDLE) {
            $_priceInclTax = Mage::helper('tax')->getPrice($product, $_finalPrice, true, null, null, null, null, null, false);
            $_priceExclTax = Mage::helper('tax')->getPrice($product, $_finalPrice, false, null, null, null, null, null, false);
        } else {
            $_priceInclTax = Mage::helper('tax')->getPrice($product, $_finalPrice, true);
            $_priceExclTax = Mage::helper('tax')->getPrice($product, $_finalPrice);
        }
        $_tierPrices = array();
        $_tierPricesInclTax = array();
        foreach ($product->getTierPrice() as $tierPrice) {
            $_tierPrices[] = Mage::helper('core')->currency(
                    Mage::helper('tax')->getPrice($product, (float) $tierPrice['website_price'], false) - $_priceExclTax
                    , false, false);
            $_tierPricesInclTax[] = Mage::helper('core')->currency(
                    Mage::helper('tax')->getPrice($product, (float) $tierPrice['website_price'], true) - $_priceInclTax
                    , false, false);
        }
        $config = array(
            'productId' => $product->getId(),
            'priceFormat' => Mage::app()->getLocale()->getJsPriceFormat(),
            'includeTax' => Mage::helper('tax')->priceIncludesTax() ? 'true' : 'false',
            'showIncludeTax' => Mage::helper('tax')->displayPriceIncludingTax(),
            'showBothPrices' => Mage::helper('tax')->displayBothPrices(),
            'productPrice' => Mage::helper('core')->currency($_finalPrice, false, false),
            'productOldPrice' => Mage::helper('core')->currency($_regularPrice, false, false),
            'priceInclTax' => Mage::helper('core')->currency($_priceInclTax, false, false),
            'priceExclTax' => Mage::helper('core')->currency($_priceExclTax, false, false),
            /**
             * @var skipCalculate
             * @deprecated after 1.5.1.0
             */
            'skipCalculate' => ($_priceExclTax != $_priceInclTax ? 0 : 1),
            'defaultTax' => $defaultTax,
            'currentTax' => $currentTax,
            'idSuffix' => '_clone',
            'oldPlusDisposition' => 0,
            'plusDisposition' => 0,
            'plusDispositionTax' => 0,
            'oldMinusDisposition' => 0,
            'minusDisposition' => 0,
            'tierPrices' => $_tierPrices,
            'tierPricesInclTax' => $_tierPricesInclTax,
        );

        $responseObject = new Varien_Object();
        Mage::dispatchEvent('catalog_product_view_config', array('response_object' => $responseObject));
        if (is_array($responseObject->getAdditionalOptions())) {
            foreach ($responseObject->getAdditionalOptions() as $option => $value) {
                $config[$option] = $value;
            }
        }

        return Mage::helper('core')->jsonEncode($config);
    }

    public function getViewTypeConfigurableBlock($product) {
        $block = new Mage_Catalog_Block_Product_View_Type_Configurable();
        $block->setData('product', $product);
        return $block;
    }

    public function getProductId() {
        $productid = $this->getData('product_id');
        return explode(',', $productid);
    }
}

5. Create 2 javascript files named js/productoptions/product.js and js/productoptions/configurable.js

(Download attached
)


6. Create layout xml file and add the javascript file
Path :: app/design/frontend/default/default/layout/productoptions.xml

<?xml version="1.0"?>
<layout version="0.1.0">
    <productoptions_index_index>
        <reference name="head">
            <action method="addJs"><script>productoptions/product.js</script></action>
            <action method="addJs"><script>productoptions/configurable.js</script></action>
        </reference>
        <reference name="content">
            <block type="productoptions/productoptions" name="productoptions" template="productoptions/productoptions.phtml"/>
        </reference>
    </productoptions_index_index>
</layout>

7. Create phtml file and put below code
Path :: app/design/frontend/base/default/template/productoptions/productoptions.phtml

<?php
//Load product collection by product id
$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToSelect('*');
$collection->addAttributeToFilter('status',1);
if(!empty($collection)){
?>
<div class="category-product-main">
<ul>
<?php
foreach ($collection as $_product):
?>
<li>
<h3><a href="<?php echo $_product->getProductUrl();?>"><?php echo $_product->getName();?></a></h3>
<span>
<a href="<?php echo $_product->getProductUrl()?>" title="<?php echo $_product->getName()?>">
<img src="<?php echo Mage::helper('catalog/image')->init($_product, 'small_image')->resize(150, 150); ?>" alt="<?php echo $_product->getName()?>" />
</a>
</span>
<form action="<?php echo $this->helper('checkout/cart')->getAddUrl($_product) ?>" method="post" id="product_addtocart_form_<?php echo $_product->getId()?>" enctype="multipart/form-data">

<?php echo $this->getPriceHtml($_product, true) ?>

<?php
if($_product->isSaleable() && $_product->getTypeId() == 'configurable') {
$product = Mage::getModel('catalog/product')->load($_product->getId());
$block = $this->getViewTypeConfigurableBlock($product);
$_attributes = Mage::helper('core')->decorateArray($block->getAllowAttributes());
?>
<?php if (count($_attributes)): ?>
<dl>
<?php foreach($_attributes as $_attribute): ?>
<dt><label><?php echo $_attribute->getLabel() ?></label></dt>
<dd<?php if ($_attribute->decoratedIsLast){?> class="last"<?php }?>>
<div class="input-box">
<select name="super_attribute[<?php echo $_attribute->getAttributeId() ?>]" id="attribute<?php echo $_attribute->getAttributeId() ?>" class="required-entry super-attribute-select-<?php echo $_product->getId() ?>">
<option><?php echo $this->__('Choose an Option...') ?></option>
 </select>
 </div>
</dd>
<?php endforeach; ?>
</dl>
<script type="text/javascript">
var spConfig_<?php echo $_product->getId() ?> = new Product.Config(<?php echo $block->getJsonConfig() ?>, <?php echo $_product->getId() ?>, new Product.OptionsPrice(<?php echo $this->getPriceJsonConfig($product) ?>));
</script>
<?php endif;?>
<?php } ?>

<?php if($_product->isSaleable()): ?>
<div class="buy-add-to-cart">
<div class="qty-wrapper">
<label for="qty"><?php echo $this->__('Qty:') ?></label>
<input type="text" class="input-text qty" title="<?php echo $this->__('Qty') ?>" value="<?php echo ($this->getMinimalQty($_product)?$this->getMinimalQty($_product):1) ?>" maxlength="12" name="qty">
</div>
<div class="add-to-cart-buttons">
<button onclick="this.form.submit()" class="button btn-cart" title="<?php echo $this->__('Buy') ?>" type="button"><span><?php echo $this->__('Buy') ?></span></button>
</div>
</div>
<?php endif; ?>
</form>
</li>
<?php endforeach;?>
</ul>
</div>
<?php }?>

Now Flush Magento Cache and refresh page.


We hope my suggestion is useful enough for you to solve the product options issue. We highly appreciate your comment and continuous contribution to this techniques library.
You can download full source here Magebug_Productoptions

Magento create your own log files


Hi Friends,


Default Log files:

When developing your own module in Magento, it’s useful to pass variables and other messages out to a debug file so you can see how your module is working with its data. Magento comes with 2 built in log files, system.log & exception.log, both located in the var/log folder. To activate them, you must go to the Magento admin panel and under: System->Configuration->Developer->Log Settings, you will see a setting for enabling the logs.


system.log is used for general debugging and catches almost all log entries from Magento, including warning, debug and errors messages from both native and custom modules.

exception.log is reserved for exceptions only, for example when you are using try-catch statement.
To output to either the default system.log or the exception.log see the following code examples:

Mage::log('My log entry');
Mage::log('My log message: '.$myVariable);
Mage::log($myArray);
Mage::log($myObject);
Mage::logException($e);

Custom Log Files:

Going through the system.log can be a nightmare, with dozens of other modules outputting to the log it can be like finding the proverbial, needle in a haystack. So why not create a custom log for your module!, With Magento its so easy:

Mage::log('My log entry', null, 'mylogfilename.log');
//pass a variable
Mage::log('My Variable: '.$myVar, null, 'mylogfilename.log');

And that’s all there is to it. You can pass variable, arrays, messages to yourself, whatever you want. Just be careful when outputting native Magento objects, as they can be really really big. (Don’t ever try to output and entire product object!)

Custom Log Files with current year month and date:

If you want to save log file in your own separate folder with date here is code.

//Get today date
$todatedate = date('Y-m-d');

//Check log folder is created or not
$io = new Varien_Io_File();
$io->checkAndCreateFolder(Mage::getBaseDir('var').DS.'log'.DS.'YourfolderName'.DS.date('Y').DS.date('m'));

$logfilename = 'YourfolderName' . DS . date('Y') . DS . date('m') . DS . $todatedate . '_yourlogfilename.log';
Mage::log('testing log', null, $logfilename, true);

This file is store this path.
YourMagentoRootDirectory/var/log/YourfolderName/CurrentYear/CurrentMonth/Currentdate_yourlogfilename.log
EX. Magento/var/log/test/2014/05/2014-05-06_testing.log

Now Flush Magento Cache and refresh page

Hope this information is helpful to you :)

Magento Get attribute options by attribute code without load by product


Hi Friends,

Get attribute option label by attribute code without product load

Here is code:

$store_id = 1;
$attribute_code = 'brand';
$attribute = Mage::getModel('eav/config')->getAttribute('catalog_product', $attribute_code);

foreach ( $attribute->getSource()->getAllOptions(true, true) as $option )
{
    $product = Mage::getModel('catalog/product')
        ->setStoreId($store_id)
        ->setData($attribute_code,$option['value']);
    $option_label = $product->getAttributeText($attribute_code);

    echo "Option Is is ".$option['value']." And Option Label is ".$option_label."<br>";
}


I hope this helpful to you :)

Magento How To Display Product Reviews on Product Page With Pagination


Hi Friend's

Display Product review on product page in Review Tab with Pagination

First open your theme review.xml
Ex. app/design/frontend/[Your_Theme]/default/layout/review.xml

And Add this code

Code:

<catalog_product_view>
    <reference name="product.info">
        <block type="page/html_pager" name="product_review_list.toolbar" />
        <block type="core/template" name="product_review_list.count" template="review/product/view/count.phtml" />
        <block type="review/product_view_list" name="product.reviews" as="reviews" template="review/product/view/list.phtml" after="additional">
            <action method="addToParentGroup"><group>detailed_info</group></action>
            <action method="setTitle" translate="value"><value>Reviews</value></action>
        </block>
    </reference>

</catalog_product_view>

And also you have display review form after review listing see in image


Code:

<catalog_product_view>
    <reference name="product.info">
        <block type="page/html_pager" name="product_review_list.toolbar" />
        <block type="core/template" name="product_review_list.count" template="review/product/view/count.phtml" />
        <block type="review/product_view_list" name="product.reviews" as="reviews" template="review/product/view/list.phtml" after="additional">
            <action method="addToParentGroup"><group>detailed_info</group></action>
            <action method="setTitle" translate="value"><value>Reviews</value></action>
            <block type="review/form" name="product.review.form" as="review_form">
                <block type="page/html_wrapper" name="product.review.form.fields.before" as="form_fields_before" translate="label">
                    <label>Review Form Fields Before</label>
                    <action method="setMayBeInvisible"><value>1</value></action>
                </block>
            </block>
        </block>
    </reference>
</catalog_product_view>

Now Flush Magento Cache and refresh page

Hope this information is helpful to you :)

Magento Admin how to disable past days, current date in datepicker


Hi Friend's

Disable past and current date selection in admin form edit/add action

Open your form.php file and add this code

Code:

$nextdate = $fieldset->addField('next_date'.$counter, 'date',
    array(
        'name'      => 'upcoming_date',
        'label'     => 'Upcomng Date',
        'class'     => 'required-entry validate-date validate-date-range date-range-custom_theme-from',
        'required'  => true,
        'image'     => $this->getSkinUrl('images/grid-cal.gif'),
        'format'    => 'YYYY-MM-dd'
    )
);
$nextdate->setAfterElementHtml("<p>Date format is <strong style='color: #FF0000;'>YYYY</strong>/<strong style='color: #FF0000;'>MM</strong>/<strong style='color: #FF0000;'>DD</strong></p>
<script type=\"text/javascript\">
//<![CDATA[
Calendar.setup({
    inputField: 'upcoming_date',
        ifFormat: '%Y-%m-%d',
        showsTime: false,
        button: 'upcoming_date_trig',
        align: 'Bl',
        singleClick : true,
        disableFunc: function(date)  {
            var now = new Date();
            //If you want to disable current date so remove below comment
            //now.setDate(now.getDate() + 1);
           
            if(date.getFullYear()   <   now.getFullYear())  { return true; }
            if(date.getFullYear()   ==  now.getFullYear())  { if(date.getMonth()    <   now.getMonth()) { return true; } }
            if(date.getMonth()      ==  now.getMonth())     { if(date.getDate()     <   now.getDate())  { return true; } }
        }
});
//]]>
</script>");

Now Flush Magento Cache and refresh page

Hope this information is helpful to you :)