Blog

Delete orders in Magento using a shell script

In a recent post I’ve shown the steps required to delete orders in Magento using a couple of SQL queries. Since this is a common issue when switching from development to staging and finally to production ennvironments it would be handy to have a shell script that takes care of deleting orders in Magento by simply specifying a list of orders to delete, either by order id or order increment id.

One of the problems with installing additional extensions such as Delete Orders in this particular case  as explained in the previous post on deleting orders in Magento is that these extension will overwrite the sales order grid in most of the cases, thus causing conflicts in the code base.

This is why I’ve decided to write up a simply PHP shell script containing the required commands to delete orders in Magento. Although it’s pretty self-explanatory I’m planning to release an extension on Magento Connect. Meanwhile feel free to use the code provided below.

<?php

require '../app/Mage.php';

Mage::app('admin')->setUseSessionInUrl(false);

/**
 * whether $removeOrderIds are specified as order increment ids (e.g. 100000025)
 * or order ids (e.g. 25).
 */
$useOrderIncrementId = true;

/**
 * specify order ids to remove
 */
$removeOrderIds = array(
);

/**
 * Deletes order either by $orderIncrementId or $orderId.
 * @param int $orderId
 * @param boolean $isOrderIncrementId is order increment id
 * @return boolean
 */
function deleteOrder($orderId, $isOrderIncrementId = false) {
 try {

   $order = null;

   // load order either based on order increment id or order id
   if ($isOrderIncrementId) {
     $order = Mage::getModel('sales/order')->load($orderId, 'increment_id');
   } else {
     $order = Mage::getModel('sales/order')->load($orderId);
   }

   if (!$order->getId()) {
     echo 'Order #' . $orderId . ' does not exist (anymore)' . PHP_EOL;
     return;
   }

   // 1. call delete to trigger delete-CASCADE
   $order->delete();

   // 2. manual clean-up for deleted order
   // credits go to DeleteOrders extensions
   $resource = Mage::getSingleton('core/resource');
   $delete = $resource->getConnection('core_read');
   $orderTable = $resource->getTableName('sales_flat_order_grid');
   $invoiceTable = $resource->getTableName('sales_flat_invoice_grid');
   $shipmentTable = $resource->getTableName('sales_flat_shipment_grid');
   $creditMemoTable = $resource->getTableName('sales_flat_creditmemo_grid');
 
   $sql = "DELETE FROM " . $orderTable . " WHERE entity_id = " . $orderId . ";";
   $delete->query($sql);
   $sql = "DELETE FROM " . $invoiceTable . " WHERE order_id = " . $orderId . ";";
   $delete->query($sql);
   $sql = "DELETE FROM " . $shipmentTable . " WHERE order_id = " . $orderId . ";";
   $delete->query($sql);
   $sql = "DELETE FROM " . $creditMemoTable . " WHERE order_id = " . $orderId . ";";
   $delete->query($sql);

   // 3. we are done!
   echo 'Removed order #' . $orderId . PHP_EOL;

   return true;
 } catch (Exception $e) {
   echo "Failed to remove order #" . $orderId . ": "
   . $e->getMessage() . PHP_EOL;
 }

 return false;
}

/**
 * iterate over $removeOrderIds
 */
foreach ($removeOrderIds as $orderId) {
  deleteOrder($orderId, $useOrderIncrementId);
}

echo "DONE!" . PHP_EOL;

Please agree to all the terms and conditions before placing the order redirect issue in Paypal Express Checkout and Magento

So your customers are not able to complete orders when using Paypal Express Checkout in Magento one page checkout due to “misbehaving” sales agreement checkboxes.

Please agree to all the terms and conditions before placing the order.

The scenario at hand is as follows:

  1. Customers select PayPal Express Checkout as checkout method (yes, it’s not a payment method!)
  2. Customers are redirected to the PayPal payment page and select “Buy Now”
  3. Customers are redirected to Magento’s PayPal order review page (/paypal/order/review) to accept your sales agreements set (so far so good)
  4. Customers check all required agreement checkboxes displayed on the order review page and click “Buy now”
  5. But instead of being able to complete their order customers are redirected back to the order review page with a message “Please agree to all the terms and conditions before placing the order” that they need to accept all agreements in order to submit their order – thus ending in a order submission loop and being unable to complete their orders.

Check for required sales agreements

The solution to overcome this problem is rather easy: Check for required agreements via Sales / Agreements that might cause the PayPal order review step block valid order submission.

There’s a chance that some agreements is not rendered (correctly), thus disabling customers from accepting them and actually submitting their order.

A recent customer had the payment option electronic direct debit enabled, based on an third-party extension. Unfortuntalely, this extension did not render its additional required sales agreement correctly leading to the fact that customers were unable to select the corresponding required checkbox on the order review page ending in the above mentioned order submission loop.

Check for required sales agreements at order submission runtime

You can also check at runtime which agreements are required to be able to submit orders by inspecting the Paypal Express Checkout controller: Mage_Paypal_Controller_Express_Abstract. There you find the method placeOrderAction which initially checks for required agreements and their submission value. We can easily inject a debug message there to retrieve the required sales agreements at runtime. Below you find the corresponding code snippet. All we need to do is

Mage::log(implode(', ', $requiredAgreements));

which will write the required agreements to system.log. As always, make sure to enable debug logging in Magento.

public function placeOrderAction()
{
  try {
    $requiredAgreements = Mage::helper('checkout')-&gt;getRequiredAgreementIds();

    Mage::log(implode(', ', $requiredAgreements)); // inject debug logging

    if ($requiredAgreements) {
      $postedAgreements = array_keys($this-&gt;getRequest()-&gt;getPost('agreement', array()));
      if (array_diff($requiredAgreements, $postedAgreements)) {
        Mage::throwException(Mage::helper('paypal')-&gt;__('Please agree to all the terms and conditions before placing the order.'));
      }
    }
...

this will print your required agreement ids in system.log.

Make sure to revert any changes done to this Magento core file!

Customize surcharge price logic for Phoenix Cash on Delivery Magento extension

A recent customer had the requirement to calculate cash on delivery surcharges in relation to the shipping costs. For instance, let’s say that shipping to Germany costs EUR 8 whereas customers from Austria are charged only EUR 5, unless the total order reaches a certain amount resulting in free shipping.

Now the requirement in this case was that shipping costs and cash on delivery surcharges should in total be EUR 12 for shipments to Germany and EUR 8 for shipments to Austria. So the cash on delivery surcharge has to be calculated in relation to the shipping costs selected to match the desired total cash on delivery surcharge + shipping costs.

Using Phoenix Cash on Delivery extension enables us to set fixed or percentage surcharge values depending in inland and foreign shipments. But these settings do not contain special logic for shipping methods and their respective costs. The extension by default only allows us to disable certain shipping methods when using cash on delivery and limit the list of allowed countries, which in general is sufficient enough but not in our particular case.

So, using the vanilla version of the Phoenix Cash on delivery extension would in the example described above result in duplicate costs (shipping + cash on delivery surcharge) for customers during the checkout process, i.e. EUR 8 for shipping + EUR 12 cash on delivery surcharge for German customers, where in fact we only want to charge German customers EUR 12 in total when cash on delivery is selected as payment method and in total EUR 8 for Austrian customers.

Customize Phoenix Cash on Delivery extension

Having a look at the Phoenix Cash on Delivery extension quickly reveals that for our price logic to work we need to customize the price calculcation in Phoenix_CashOnDelivery_Model_CashOnDelivery and especially the functions getInlandCosts and getForeignCountryCosts.

First, copy app/code/community/Phoenix/CashonDelivery/Model/CashOnDelivery.php to app/code/local/Phoenix/CashonDelivery/Model/CashOnDelivery.php to overwrite the model class in the local code pool.

Note that this should be done using a custom module. The example here only serves as demonstration of the required code pieces.

Next, add the function getConditionalCosts in your local CashOnDelivery.php file:

public function getConditionalCost($baseCost) {
  $quote = Mage::getModel('checkout/session')->getQuote();
  if ($quote) {
    if ($quote->getShippingAddress()) {
      // check customer shipping address country
      $shippingData = $quote->getShippingAddress()->getData();

      if (isset($shippingData['country_id'])) {
        if ($shippingData['country_id'] === 'DE') {
          $baseCost; // do some calculation here
        } else if ($shippingData['country_id'] === 'AT') {
          if (floatval($quote->getGrandTotal()) < 70) {
            $baseCost = // do some calculation here
          }
        }
      }
    }
  }

  return floatval($baseCost);
}

And to actually integrate the custom logic by adding the required calls to getConditionalCost in getInlandCosts and getForeignCountryCosts:

public function getInlandCosts($address = null) {

  $inlandCost = $this->getConfigData('inlandcosts');
  $minInlandCost = $this->getConfigData('minimum_inlandcosts');

  if (is_object($address) && Mage::getStoreConfigFlag(self::XML_CONFIG_PATH_CASHONDELIVERY_COST_TYPE)) {
    $calcBase = $this->getConfigData('cost_calc_base');
    $inlandCost = ($address->getData($calcBase) / 100) * $inlandCost;
    if ($inlandCost < $minInlandCost) {
      $inlandCost = $minInlandCost;
    }
  }

  return $this->getConditionalCost($inlandCost);

  //return floatval($inlandCost);
}

public function getForeignCountryCosts($address = null) {
  $foreignCost = $this->getConfigData('foreigncountrycosts');
  $minForeignCost = $this->getConfigData('minimum_foreigncountrycosts');

  if (is_object($address) && Mage::getStoreConfigFlag(self::XML_CONFIG_PATH_CASHONDELIVERY_COST_TYPE)) {
    $calcBase = $this->getConfigData('cost_calc_base');
    $foreignCost = ($address->getData($calcBase) / 100) * $foreignCost;
    if ($foreignCost < $minForeignCost) {
      $foreignCost = $minForeignCost;
    }
  }

  return $this->getConditionalCost($foreignCost);

  //return floatval($foreignCost);
}

Possible optimizations

Note that with a custom module can easily administrate the price values using by getConditionalCost through the admin backend. As always use system.xml to add your backend options. In addition, make sure to rewrite the model through config.xml. But I’ll leave that to you as an additional excercise 😉

 

Fix “Unknown column SUM(IFNULL…” in Magento

This is just a quick fix post in case you are experiencing the exception “Unknown column ‘SUM((IFNULL” when trying to log in to the Magento admin backend. Below you find the full exception stack trace for reference:

a:5:{i:0;s:1073:"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'SUM((IFNULL(main_table.base_total_invoiced, 0) - IFNULL(main_table.base_tax_invoiced, 0) - IFNULL(main_table.base_shipping_invoiced, 0) - (IFNULL(main_table.base_total_refunded, 0) - IFNULL(ma' in 'field list', query was: SELECT `SUM((IFNULL(main_table.base_total_invoiced, 0) - IFNULL(main_table.base_tax_invoiced, 0) - IFNULL(main_table.base_shipping_invoiced, 0) - (IFNULL(main_table.base_total_refunded, 0) - IFNULL(main_table.base_tax_refunded, 0) - IFNULL(main_table.base_shipping_refunded, 0))) * main_table`.`base_to_global_rate)` AS `lifetime`, `AVG((IFNULL(main_table.base_total_invoiced, 0) - IFNULL(main_table.base_tax_invoiced, 0) - IFNULL(main_table.base_shipping_invoiced, 0) - (IFNULL(main_table.base_total_refunded, 0) - IFNULL(main_table.base_tax_refunded, 0) - IFNULL(main_table.base_shipping_refunded, 0))) * main_table`.`base_to_global_rate)` AS `average` FROM `sales_flat_order` AS `main_table` WHERE (main_table.status NOT IN('canceled')) AND (main_table.state NOT IN('new', 'pending_payment'))";i:1;s:4503:"#0 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `SUM((IF...', Array)
#4 lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `SUM((IF...', Array)
#5 lib/Zend/Db/Adapter/Abstract.php(737): Varien_Db_Adapter_Pdo_Mysql->query('SELECT `SUM((IF...', Array)
#6 lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll('SELECT `SUM((IF...', Array)
#7 app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll('SELECT `SUM((IF...', Array)
#8 lib/Varien/Data/Collection/Db.php(566): Mage_Core_Model_Resource_Db_Collection_Abstract->getData()
#9 app/code/core/Mage/Adminhtml/Block/Dashboard/Sales.php(65): Varien_Data_Collection_Db->load()
#10 app/code/core/Mage/Core/Block/Abstract.php(293): Mage_Adminhtml_Block_Dashboard_Sales->_prepareLayout()
#11 app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#12 app/code/core/Mage/Adminhtml/Block/Dashboard.php(54): Mage_Core_Model_Layout->createBlock('adminhtml/dashb...')
#13 app/code/core/Mage/Core/Block/Abstract.php(293): Mage_Adminhtml_Block_Dashboard->_prepareLayout()
#14 app/code/core/Mage/Core/Model/Layout.php(456): Mage_Core_Block_Abstract->setLayout(Object(Mage_Core_Model_Layout))
#15 app/code/core/Mage/Core/Model/Layout.php(472): Mage_Core_Model_Layout->createBlock('adminhtml/dashb...', 'dashboard')
#16 app/code/core/Mage/Core/Model/Layout.php(239): Mage_Core_Model_Layout->addBlock('adminhtml/dashb...', 'dashboard')
#17 app/code/core/Mage/Core/Model/Layout.php(205): Mage_Core_Model_Layout->_generateBlock(Object(Mage_Core_Model_Layout_Element), Object(Mage_Core_Model_Layout_Element))
#18 app/code/core/Mage/Core/Model/Layout.php(210): Mage_Core_Model_Layout->generateBlocks(Object(Mage_Core_Model_Layout_Element))
#19 app/code/core/Mage/Core/Controller/Varien/Action.php(344): Mage_Core_Model_Layout->generateBlocks()
#20 app/code/core/Mage/Core/Controller/Varien/Action.php(269): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()
#21 app/code/core/Mage/Adminhtml/Controller/Action.php(275): Mage_Core_Controller_Varien_Action->loadLayout(NULL, true, true)
#22 app/code/core/Mage/Adminhtml/controllers/DashboardController.php(40): Mage_Adminhtml_Controller_Action->loadLayout()
#23 app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_DashboardController->indexAction()
#24 app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
#25 app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#26 app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#27 app/Mage.php(684): Mage_Core_Model_App->run(Array)
#28 index.php(87): Mage::run('', 'store')
#29 {main}";s:3:"url";s:70:"/index.php/admin/dashboard/index/key/78e1f84097adc7e831f279f919f0d1a6/";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:5:"admin";}

Check Zend Library version

The solution in this case is pretty easy. Make sure that your Zend library conforms to your Magento version. It’s easiest to just remove the lib/Zend folder and use a fresh Zend library version corresponding to your Magento version. As always, make sure to clear var/cache afterwards. This has been tested in Magento CE 1.9+.

Delete orders in Magento

When developing and testing your Magento store you will potentially create a lot of test orders. In addition, when creating/testing extensions you might need to reset order-related entries, e.g. when experiencing SQL integrity constraint violations such as

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’, query was: INSERT INTO `sales_order_tax` (`order_id`, `code`, `title`, `percent`, `amount`, `priority`, `position`, `base_amount`, `process`, `base_real_amount`, `hidden`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)’

By default, Magento does not provide means to delete orders but merely to set their state to e.g. closed. Here’s a quick way how to delete orders in Magento. First, we’ll have a look at the SQL statements required to delete orders manually and second using a free extension.

Manually delete orders in Magento using SQL

/* 1. disable foreign checks */ 
SET FOREIGN_KEY_CHECKS=0; 

/* 2.1. truncate order-related tables, part 1 */
TRUNCATE `sales_flat_creditmemo`; 
TRUNCATE `sales_flat_creditmemo_comment`; 
TRUNCATE `sales_flat_creditmemo_grid`; 
TRUNCATE `sales_flat_creditmemo_item`; 
TRUNCATE `sales_flat_invoice`; 
TRUNCATE `sales_flat_invoice_comment`; 
TRUNCATE `sales_flat_invoice_grid`; 
TRUNCATE `sales_flat_invoice_item`; 
TRUNCATE `sales_flat_order`; 
TRUNCATE `sales_flat_order_address`; 
TRUNCATE `sales_flat_order_grid`; 
TRUNCATE `sales_flat_order_item`; 
TRUNCATE `sales_flat_order_payment`; 
TRUNCATE `sales_flat_order_status_history`; 
TRUNCATE `sales_flat_quote`; 
TRUNCATE `sales_flat_quote_address`; 
TRUNCATE `sales_flat_quote_address_item`; 
TRUNCATE `sales_flat_quote_item`; 
TRUNCATE `sales_flat_quote_item_option`; 
TRUNCATE `sales_flat_quote_payment`; 
TRUNCATE `sales_flat_quote_shipping_rate`; 
TRUNCATE `sales_flat_shipment`; 
TRUNCATE `sales_flat_shipment_comment`; 
TRUNCATE `sales_flat_shipment_grid`; 
TRUNCATE `sales_flat_shipment_item`; 
TRUNCATE `sales_flat_shipment_track`; 
TRUNCATE `sales_invoiced_aggregated`; 
TRUNCATE `sales_invoiced_aggregated_order`; 
TRUNCATE `sales_payment_transaction`;
TRUNCATE `sales_order_aggregated_created`; 
TRUNCATE `sales_order_tax`;
TRUNCATE `sales_order_tax_item`;

/* 2.2. truncate order-related tables, part 2*/
TRUNCATE `sendfriend_log`; 
TRUNCATE `tag`; 
TRUNCATE `tag_relation`; 
TRUNCATE `tag_summary`; 
TRUNCATE `wishlist`; 
TRUNCATE `log_quote`; 
TRUNCATE `report_event`; 

/* 3.1. reset auto-increment to 1 for truncated tables, part 1 */
ALTER TABLE `sales_flat_creditmemo` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1; 
ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1; 
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1; 
ALTER TABLE `sales_payment_transaction` AUTO_INCREMENT=1; 
ALTER TABLE `sales_order_aggregated_created` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_tax` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_tax_item` AUTO_INCREMENT=1;

/* 3.1. reset auto-increment to 1 for truncated tables, part 2 */
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1; 
ALTER TABLE `tag` AUTO_INCREMENT=1; 
ALTER TABLE `tag_relation` AUTO_INCREMENT=1; 
ALTER TABLE `tag_summary` AUTO_INCREMENT=1; 
ALTER TABLE `wishlist` AUTO_INCREMENT=1; 
ALTER TABLE `log_quote` AUTO_INCREMENT=1; 
ALTER TABLE `report_event` AUTO_INCREMENT=1; 

/* 4. finally re-activate foreign key check */
SET FOREIGN_KEY_CHECKS=1; 

Delete orders using Magento extension

There also exists a handy Magento extension for deleting orders: Delete Orders.

Delete Orders Magento extension

I’ve not used this extension myself so far so I can’t comment on the functionality but based on the reviews and comments it’s a good choice to go when you need a nicer user interface for deleting orders in Magento.

Fix Anaraky Google Dynamic Remarkting Tag extension

By default, Anaraky Google Dynamic Remarkting Tag (“Anaraky GDRT”) does not set correct page type “other“, as described in Google’s Dynamic Remarketing guidelines. Instead, it uses “siteview“. To overcome this behavior there are only 2 simple modifications required.

Required modifications for Anaraky Gdrt extension

First, in

app/code/community/Anaraky/Gdrt/Block/Script.php on line 51 replace siteview by other:

$params = array('ecomm_pagetype' => 'siteview');

becomes

$params = array('ecomm_pagetype' => 'other');

Second, in the same file on line 104 replace

$params = array( 'ecomm_pagetype' => 'siteview' );

with

$params = array( 'ecomm_pagetype' => 'other' );

Hint on using Anaraky custom page setup

Furthermore, when using the custom page setup options make sure to omit the trailing slash when only specifying module/controller setup, e.g. checkout/cart as shown below:

Anaraky Gdrt Magento Extension

Anaraky Google Dynamic Remarkting Tag does string comparison check without the trailing slash in the observer class:

foreach ($gdrtPages as $k =&gt; $v) {
  $v = rtrim($v, '/');
  if ($mName . '/' . $cName . '/' . $aName == $v ||
    $mName . '/' . $cName == $v)
  {
    $pageType = $k;
  }
}

Handling special prices and grouped/bundle products for “ecomm_totalvalue”

In case you run into problem when displaying special prices or handling grouped or bundle products in the ecomm_totalvalue field here is a quick fix to add this check. Add the following function in Script.php:

private function _getProductPrice($product) {

 $totalvalue = 0;

 // check if we are handling grouped products
 if($product->getTypeId() == 'grouped') {
 $groupedSimpleProducts = $product->getTypeInstance(true)->getAssociatedProducts($product);

 $groupedPrices = array();

 foreach($groupedSimpleProducts as $gSimpleProduct) { 
 $groupedPrices[] = $this->_getProductPrice($gSimpleProduct);
 }

 $totalvalue = min($groupedPrices);

 } else { // handle other product types
 $_price = Mage::helper('tax')->getPrice($product, $product->getPrice(), $inclTax);
 $_specialPrice = Mage::helper('tax')->getPrice($product, $product->getSpecialPrice(), $inclTax);
 $_finalPrice = Mage::helper('tax')->getPrice($product, $product->getFinalPrice(), $inclTax);

 if($_price == $_finalPrice) { // no special price
 $totalvalue = (float)$_price;
 } else { // get special price
 if((float)$_finalPrice > 0 && (float)$_finalPrice <= (float)$_price) {
 $totalvalue = (float)$_finalPrice;
 } else {
 $totalvalue = (float)$_price;
 }
 }
 } 

 return $totalvalue;
 }

Second, change the “product” case like so:


case 'product':
$product = Mage::registry('current_product');

$totalvalue = $this->_getProductPrice($product);

$params = array(
'ecomm_prodid' => $this->getEcommProdid($product),
'ecomm_pagetype' => 'product',
'ecomm_totalvalue' => (float)number_format($totalvalue, '2', '.', '')
);
unset($product);
break;

Don’t forget to flush the cache afterwards.

Set tier prices for product variants of configurable products in Magento

So you want to set tier prices for product variants of configurable products in Magento. To achieve this you create a configurable product and associate your simple product variants based on your pre-defined attributes. At this point you start setting tier prices for your associated product variants, i.e. your simple products thinking that Magento will use these values on the frontend. Well, unfortunately in fact Magento by default only uses the tier prices set for the configurable product itself which obviously results in wrong prices in the frontend.

Surcharge to the rescue?

Ok, well let’s then use surcharges for product variants as basis for tier price calculations of product variants. Unfortunately, this also will not work since price calculations of tier prices for product variants only take the parent configurable product into consideration.

A concrete example

Let’s back up a little and have a look at the problem at hand with a concrete example. Imagine you want to sell t-shirts in different colors (green, blue, orange). For each color you want to set different base prices and in addition different tier prices. Have a look at the following table for clarification:

T-shirt1 item3 items12 items24 items
green10876
orange111098
blue1211109

Prices are in EUR, e.g. 3 orange t-shirts cost 10 EUR each, whereas 3 blue t-shirts cost 11 EUR each. Again, by default Magento will use the tier price set for the product configurable product, thus ignoring tier prices set for product variants.

Magento extensions to use tier prices of product variants

This shortcoming of Magento’s default tier price handling for configurable products lead to the development of a handful of helpful extensions. One of the first ones on the market was Simple Configurable Producs (SCP), followed by the more advanced Better Configurable Products (BCP). Simple Configurable Products officially is not supported for the latest Magento 1.9.x series anymore. In fact, according to Magento Connect official support ended with Magento 1.5, although various people reported success with Magento 1.8 too.

Thus, you might want to use Better Configurable Products instead which is officially supported for the latest Magento EE and CE editions. Nevertheless, in case you want to save a little Configurable Products use Simple Price is cheaper but does the job just as well. No, I’m not affiliated with Configurable Products use Simple Price in any way. But after spending some time testing various extensions capable of using tier prices of product variants instead of the parent’s configurable product I’ve opted for Configurable Products use Simple Price for its price and functionality. Compared to Better Configurable Products Configurable Products use Simple Price is pretty simple with regards to the feature list.

To round up, in case you are just searching for a quick way to set tier prices for product variants of configurable products in Magento Configurable Products use Simple Price offers a quick and cost effective solution.

Grouped Products as alternative

Grouped products can also be used as alternative to the above mentioned extensions to correctly calculate and display tier prices in Magento. Of course, only if your product setup matches the required configuration for grouped products. For grouped products, tier prices are correctly calculated based on the values of the underlying simple products.

You can find a lot of information on the web concerning this issue in case you want to continue your search for alternative solutions. Hopefully, some day setting tier prices for configurable products correctly will become an integral part of Magento so we are not dependent on additional extensions for this (core) functionality.

Solve “Unable to find a sitemap to generate” in Magento

In case you receive the error message

Unable to find a sitemap to generate

when trying to generate a Google Sitemap for an existing entry in Magento make sure to check that the sitemap ID generated is set correctly, i.e. not equal to 0 (zero).

In this particular Magento 1.9.1.0 setup the ID for the first sitemap wrongly was set to 0 on generation via Catalog / Google Sitemap, thus breaking the sitemap generation process through the Generate option: magento-google-sitemap-id-0-unable-to-find-sitemap-to-generate

Correct Sitemap ID in Database

Manually setting the sitemap ID in the database to 1 for the first sitemap solved this problem:

Magento Google Sitemap ID 1 correct

 

Magento Google Sitemap generated successfully

 

 

Still, it’s strange that no error message was displayed when creating the first sitemap entry through Catalog / Google Sitemap.

Has anybody else experienced this behavior?

Installing Android Lollipop 5.1.1 on Samsung Galaxy S2 GT-i9100

The Samsung Galaxy S2 GT-i9100 is still one of the most popular smartphones today. Although, official Android upgrades have been discontinued for this model it is quite easy to install Android Lollipop 5.1 on Samsung Galaxy S2 GT-i9100 using Cyanogenmod 12.1. This post describes the steps needed to install Android Lollipop 5.1 based on an existing Cyanogenmod setup, e.g. CM 11. Additionally, it shows which Google Apps package (“gapps”, Google Play Services) is supported for this setup and guides you through the processing of setting it up correctly.

Backup data

Before flashing the Cyanogenmod 12.1 ROM on your Samsung Galaxy S2 make sure to backup for data. We are going to format /system and all caches, thus erasing your user data and previous settings. Also, in order to install Android Lollipop 5.1 using Cyanogenmod 12.1 on your Galaxy S2 your device needs to be rooted. Have a look at a previous post Installing Android Jelly Beans on HTC Desire Bravo which is still valid to root your Samsung Android device. In case you don’t have Cyanogenmod installed on your Samsung Galaxy S2 GT-i9100 have a look the official CM installation guide for the Samsung Galaxy S2 first before continuing.

Install Android Lollipop on Samsung Galaxy S2 GT-i9100

Once you’ve backed up your data download the latest Cyanogenmod 12.1 nightly for the Samsung Galaxy S2:

and save it to your SD-card. Then, download the latest TK GApps Pico Modular Package:

Make sure to choose the Pico Modular Package as the Samsung Galaxy S2 has some memory constraints that probably will lead to errors for other package types. Don’t worry, the Pico Modular Package has everything you need to use your favorit Google services, such as Google Play Services and Google Play Store but in addition does not install all of Google’s applications you might not need. Below you find a comparison chart of the different TK Modular Packages available to get an overview of the applications available in the various packages:

tk-apps-overview-table

Next, boot into Recovery mode (hold Home button, Volume up and Power simultaneously) of your existing CM 11 setup and execute the following steps:

Note: Make sure to backup your data first! Your settings and user data will be lost by executing the following steps!

  1. Wipe data/factory reset
  2. Wipe cache
  3. Mounts and storage => format /system
  4. Advanced => Wipe dalvik cache
  5. Install zip => Choose the CM 12.1 Nightly zip file you downloaded previously from your SD card
    1. do not reboot once finished
  6. Install zip => Choose the TK GApps zip file previously downloaded from your SD card
  7. Reboot

The first reboot will take some time, so don’t worry. After the reboot you now should have a working Android Lollipop 5.1 with compatible Google Apps on your Samsung Galaxy S2 GT-i9100! Simple, isn’t it?

Possible issues / hints

Do not use the Google Apps package officially provided by Cyanogenmod:

  • http://wiki.cyanogenmod.org/w/Google_Apps#Downloads

These will not work and you will end up with a message saying

Google Play Services, which some of your applications rely on, is not supported by your device. Please contact the manufacturer for assistence.

Also, you will possibly end up with a lot of crashes of Google Play Services and applications depending on this framework, like

“com.google.process.gapps” and Google Apps has stopped working

Instead, use the TK Pico Modular Package as explained above. In case you get an error message while trying to flash TK GApps Pico Modular Package saying

* Incompatible GApps currently installed

make sure to wipe data and format /system first as explained above. TK Gapps can only be installed on top of an existing installation of TK GApps. Thus, you must wipe your system partition before installing any TK GApps package.

In case you have additional notes feel free to leave a comment. Have fun with the latest Android Lollipop 5.1 based on Cyanogenmod 12.1 on your Samsung Galaxy S2 GT-i9100!

Run Magento Data Flow Profile from shell

Running Magento data flow profiles for importing/exporting data is quite resource intensive and can take a while to execute for larger junks of data. Although there exist alternative ways to import/export data in Magento sometimes existing data flow profiles can’t be replaced just yet. Below you find a shell script to run Magento data flow profiles from the shell based on the data flow profile id. You can execute this script using PHP or PHP-CI, thus being able to specify different resource limits for your PHP setups (e.g. time outs)

Create data flow profile

Go ahead and create a data flow profile as usual through Magento’s admin interface and take note of the profile id (13) in the URL: Magento data flow profile URL You will use this profile ID to run the data flow profile via the shell script provided below.

Run data flow profile via shell script

In order to execute the script specify the data flow profile id using the –profile switch, e.g. using PHP-CLI.


$ php5-5.4-cli ./run_data_flow_profile.php --profile 13

This will run the data flow profile with the ID 13.

Magento data flow profile shell script

Save the code below in a file called run_data_flow_profile.php in Magento’s shell folder. Use the command specified above to run the script from within your shell.

<?php

require_once 'abstract.php';

/**
 * @author Matthias Kerstner <matthias@kerstner.at>
 * @version 1.0.0
 */
class Mage_Shell_Run_Data_Flow_Profile extends Mage_Shell_Abstract {

  /** @var string this module's namespace */
  private static $_MODULE_NAMESPACE = 'kerstnerat_rundataflowprofileshell';

  /**
   * Logs $msg to logfile specified in configuration.
   * @param string $msg
   */
  private function logToFile($msg) {
    Mage::log($msg, null, self::$_MODULE_NAMESPACE . '.log');
  }

  /**
   * Run script based on shell arguments specified.
   */
  public function run() {
    try {
      if (!$this->getArg('profile')) {
        throw new Exception('Missing profile');
      }

      $profileId = (int) $this->getArg('profile');

      $this->logToFile('Profile started: ' . $profileId . ' at ' . date('Y-m-d H:i:s')
        . '...');

      $profile = Mage::getModel('dataflow/profile');
      $userModel = Mage::getModel('admin/user');
      $userModel->setUserId(0);

      Mage::getSingleton('admin/session')->setUser($userModel);
      $profile->load($profileId);

      if (!$profile->getId()) {
        $this->logToFile('error: ' . $profileId . ' - incorrect profile id');
        return;
      }

      Mage::register('current_convert_profile', $profile);
      $profile->run();

      $this->logToFile('Profile ended: ' . $profileId . ' at ' . date('Y-m-d H:i:s'));
    } catch (Exception $ex) {
      $this->logToFile($ex->getMessage());
      echo $this->usageHelp();
    }
  }

 /**
  * Retrieve Usage Help Message.
  */
 public function usageHelp() {
   return " 
Usage: php -f run_data_flow_profile.php --[options]
 
 --profile  Data Flow Profile ID
 help Show this help
 
  ID of Data Flow Profiles to run";
 }
}

$shell = new Mage_Shell_Run_Data_Flow_Profile();
$shell->run();

Compatibility

The script has been tested with Magento 1.8 and higher. If you happen to find a problem please leave a comment.

Setting up Varnish caching server for pd-admin with Apache and php-fcgi

Deploying Varnish caching server can significantly speed up web-based applications, as well as simple websites. Varnish supports different back-end systems, ranging from the popular Apache web server to the more efficient nginx. Especially when it comes to handling high traffic sites Varnish can bring a considerable uplift to the responsiveness by e.g. caching entire pages (i.e. full-page cache).

In order to improve the responsiveness of a couple of high traffic websites running on pd-admin (an advanced collection of tools to administrate web- and mail hosting on Linux-based servers) Varnish 4 was implemented. The following steps describe the process of setting up Varnish caching server for pd-admin with Apache and php-fcgi on a Debian 7 server.

Setup Varnish on Debian 7 64bit

Let’s have a quick look at the process of installing varnish on a Debian 7 64 bit machine. Basically, you need to do 5 things:

  1. Install https support for apt-get
  2. Add GPG key for apt-get
  3. Add the repository to apt-get packages source list
  4. Refresh package source list
  5. Install Varnish

Thus, in order to setup Varnish on Debian 7 64 bit execute the following commands:

  1. apt-get install apt-transport-https
  2. curl https://repo.varnish-cache.org/GPG-key.txt | apt-key add –
  3. echo “deb https://repo.varnish-cache.org/debian/ wheezy varnish-4.0” >> /etc/apt/sources.list.d/varnish-cache.list
  4. apt-get update
  5. apt-get install varnish

That’s it. Varnish should be installed on ready to be configured.

Configure Varnish through VCL – default.vcl

Now that Varnish is installed it’s time to configure it. This can be done by editing default.vcl – the default configuration file automatically created during the installation:

#
# This is an example VCL file for Varnish.
#
# It does not do anything by default, delegating control to the
# builtin VCL. The builtin VCL is called when there is no explicit
# return statement.
#
# See the VCL chapters in the Users Guide at https://www.varnish-cache.org/docs/
# and http://varnish-cache.org/trac/wiki/VCLExamples for more examples.

vcl 4.0;

backend default {
 .host = "127.0.0.1";
 .port = "80";
}

sub vcl_recv {
 # Happens before we check if we have this in cache already.
 #
 # Typically you clean up the request here, removing cookies you don't need,
 # rewriting the request, etc.
}

sub vcl_backend_response {
 # Happens after we have read the response headers from the backend.
 #
 # Here you clean the response headers, removing silly Set-Cookie headers
 # and other mistakes your backend does.
}

sub vcl_deliver {
 # Happens when we have all the pieces we need, and are about to send the
 # response to the client.
 #
 # You can do accounting or modifying the final object here.
}

As starting point have a look at the very handy Varnish 4.0 configuration template which works out of the box for

  • WordPress
  • Drupal (works decently for Drupal 7, depends on your modules obviously)
  • Joomla (WIP)
  • Fork CMS
  • OpenPhoto

and additional configuration setups like

  • Server-side URL rewriting
  • Clean error pages for debugging
  • Virtual Host implementations
  • Various header normalizations
  • Cookie manipulations
  • 301/302 redirects from within Varnish

Have a look at the Varnish configuration documentation to adjust it to your specific needs. In general, only few adaptions should be required using this template.

Set correct backend hostname to solve 403 forbidden error code

In case you get a 403 forbidden error make sure to set the correct backend hostname or IP in default.vcl:

backend server1 { # Define one backend
  .host = "USE_DOMAIN_SET_IN_HTTPD_CONF"; # IP or Hostname of backend
  ...
}

Have a look at pd-admin’s httpd.conf for the currently set hostname to be used by Varnish, or open up the web administration console and check the server name option. Also make sure to have set the X-Forwarded-For header (especially for Varnish 3):


req.http.x-forwarded-for = client.ip

That should solve the 403 forbidden error for cached domains.

Exclude Domains from Varnish Caching

Varnish by default caches all requests that are not excluded specifically. Thus, in case you want to exclude domains and simple URLs from being cached by Varnish you can specify them in vcl_recv() function like so:


if (req.http.host == "www.domain.com" && req.url == "/") {
return (pass);
}

This will redirect specific domains and/or URLs to the pass() function thus by-passing the caching mechanism.

Change Varnish port

Finally, you need to set Varnish to listen on the default http port (i.e. 80) and change Apache’s listener port to something different, e.g. 81 and set it as the Varnish backend port in default.vcl. Make sure to restart Apache and Varnish. That’s it!

How to solve “550 sorry, user unknown” problem when using pd-admin

Recently, a client reported problems when trying to send mail to certain domains hosted on their own web server. The web server in question was running pd-admin as administration tool set. Mail hosting was done on a separate dedicated mail server. The error message reported by the client was the infamous

550 sorry, user unknown

Checking SMTP through telnet

Having a quick look at the pd-admin (web server) and qmail configuration (mail server) did not reveal any errors in the setup. Thus, a quick telnet check for the email account in question was done to make sure the SMTP service was operating normally:

COMMAND:
telnet mx1.company.com 25
RESPONSE:
Connected to mx1.company.com.
Escape character is ‘^]’.
220 mx1.company.com ESMTP

COMMAND:
helo hi
RESPONSE:
250 mx1.company.com

COMMAND:
mail from: <your-email@gmail.com>
RESPONSE:
250 2.1.0 Ok

COMMAND:
rcpt to: <user.in.question@company.com>
RESPONSE:
550 5.1.1 <user.in.question@company.com>: Recipient address rejected: User unknown in virtual alias table

COMMAND:
quit
RESPONSE:
221 2.0.0 Bye

This check was done on the troubled web server itself. It seemed like that the MX record was pointing to the web server itself and not the mail server.

The solution

When creating customer accounts and setting up domains through pd-admin it automatically creates MX records for these domains too (although the mail server option was not selected during the domain setup on the web server). In case you want to get rid of these MX records

  • navigate to Customers

pd-admin-customers

  • select Overview and select the Domain option for the customer in question

pd-admin-customers-overview

  • then select the MX option

pd-admin-customers-mx

  • and finally deactivate the MX record

pd-admin-customers-mx-off That should solve it! Yes, a pretty simple solution 😉

Remote deployment script for Magento extensions using modman and rsync

In order to have an efficient way of deploying Magento extensions to (remote) Magento setups I’ve created a simple deployment script based on modman and rsync.

How it works

This script copies your Magento extension to the .modman directory of your destination Magento project and deploys it there using modman. In addition, it exludes files and folders (such as .git) so that only relevant files are copied. This script uses rsync to copy files to your (remote) Magento project.

For example:

  1. Let’s say you develop a Magento extension in /home/my_user/workspace/MyExtension
  2. Your development Magento setup is located at /var/www/magento-dev1/
  3. This script copies your Magento extension from /home/my_user/workspace/MyExtension to /var/www/magento-dev1/.modman/ and automatically deploys it using modman.

Benefits

Although you could do the copying alone using the modman configuration file modman does not offer the possibility to exclude certain files for the deployment process. In addition, this script uses rsync, thus giving you the possibility to remotely deploy your Magento extension.

Download script

Feel free to grab the script from Github. Also, in case you have suggestions for improvements don’t hesitate to drop a comment below.

Setting up MongoDB on Debian Wheezy 64Bit

Here are the steps required for setting up MongoDB on Debian Wheezy. This is the manual guide since the official MongoDB packages provided by Debian are a little dusty. Don’t worry, the setup process is pretty straight forward. Let’s get started!

Import public key for package management tool apk

First you need to import the public key for your package management tool. In this case apk:

sudo apt-key adv –keyserver keyserver.ubuntu.com –recv 7F0CEB10

Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --secret-keyring /tmp/tmp.YX8vkOfNgq --trustdb-name /etc/apt//trustdb.gpg --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-jessie-automatic.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-jessie-security-automatic.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-jessie-stable.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-squeeze-automatic.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-squeeze-stable.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-wheezy-automatic.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-wheezy-stable.gpg --keyserver keyserver.ubuntu.com --recv 7F0CEB10
gpg: requesting key 7F0CEB10 from hkp server keyserver.ubuntu.com
gpg: key 7F0CEB10: public key "Richard Kreuter <richard@10gen.com>" imported
gpg: no ultimately trusted keys found
gpg: Total number processed: 1
gpg: imported: 1 (RSA: 1)

Add MongoDB to package list

echo “deb http://repo.mongodb.org/apt/debian “$(lsb_release -sc)“/mongodb-org/3.0 main” | sudo tee /etc/apt/sources.list.d/mongodb-org-3.0.list

Note that this command requires lsb_release.  In case you haven’t installed it do so by issuing the following command:

sudo apt-get install lsb_release

Refresh local package list

In order to check for your new package entry reload your local package list:

apt-get update

...
Get:1 http://repo.mongodb.org wheezy/mongodb-org/3.0 Release.gpg [490 B]
Hit https://repo.varnish-cache.org wheezy/varnish-4.0 amd64 Packages
Get:2 http://repo.mongodb.org wheezy/mongodb-org/3.0 Release [2,017 B]
Get:3 http://repo.mongodb.org wheezy/mongodb-org/3.0/main amd64 Packages [4,113 B]
Fetched 6,620 B in 1s (3,471 B/s)
Reading package lists... Done

Install MongoDB

Now it’s time to actually install MongoDB – hurray! We are going to install the latest stable release. In case you want to install a specific release version simply specify the release number.

Install latest stable MongoDB

sudo apt-get install -y mongodb-org

Install specific MongoDB version

Replace VERSION-XYZ with your desired MongoDB version, e.g. 3.0.4-rc0:

sudo apt-get install -y mongodb-org=VERSION-XYZ mongodb-org-server=VERSION-XYZ mongodb-org-shell=VERSION-XYZ mongodb-org-mongos=VERSION-XYZ mongodb-org-tools=VERSION-XYZ

Check installation messages

Watch for the following installation messages to ensure that MongoDB was setup corectly:

Setting up mongodb-org-shell (3.0.3) ...
Setting up mongodb-org-server (3.0.3) ...
Adding system user `mongodb' (UID 111) ...
Adding new user `mongodb' (UID 111) with group `nogroup' ...
Not creating home directory `/home/mongodb'.
Adding group `mongodb' (GID 113) ...
Done.
Adding user `mongodb' to group `mongodb' ...
Adding user mongodb to group mongodb
Done.
[ ok ] Starting database: mongod.
Setting up mongodb-org-mongos (3.0.3) ...
Setting up mongodb-org-tools (3.0.3) ...
Setting up mongodb-org (3.0.3) ...

That’s it for the installation – Congratulations! By default, MongoDB listens on port 27017 for incoming requests. Please refer to /etc/mongod.conf for changing it, see section Configure MongoDB below

Run MongoDB

Once you’ve installed MongoDB here are the commands to control MongoDB.

Start MongoDB

sudo service mongod start

Verify status MongoDB

In order to verify the status of MongoDB have a look at log file located at

/var/log/mongodb/mongod.log

and watch out for the line

2015-06-14T10:21:05.027+0200 I NETWORK [initandlisten] waiting for connections on port 27017

Stop MongoDB

Like starting MongoDB use the service facilities to stop MongoDB:

sudo service mongod stop

Configure MongoDB

MongoDB can be configured using /etc/mongod.conf The file is pretty self-explanatory for the basic settings. Please refer to the Getting Started Guides for more information. Enjoy!

WordPress Simple Latest Posts Shortcode Plugin

Although there exist numerous WordPress plugins for integrating the latest posts into pages this new Simple Latest Posts Shortcode Plugin was created to specifically serve those people who just want to have a single line of shortcode in order to display the latest posts in their blog. Nothing more, nothing less.

And yes, there also will be a settings page for the plugin in the administration area of you WordPress just in case you want to customize the way the plugin behaves. This plugin is inspired by the Simple Random Posts Shortcode WordPress Plugin that I released earlier. Based on the great feedback via mail I’ve decided to add this Simple Latest Posts Shortcode Plugin too.

Demonstration

Below you see the Simple Latest Posts Shortcode Plugin in action. It is called with the shortcode

simple_latest_posts

without parameters: [simple_latest_posts]

Download

Feel free to download the Simple Latest Posts Shortcode Plugin via the WordPress plugin repository.