Previous Entries

• Part 1: Introduction
• Part 2: The Core Process
• Part 3: Orders and Caveats

Quite some time ago (well over a year, in fact), I published a series of articles on Magento’s cart totals collection process (linked above), how it works, and how to customize it. The series provided a guide for introducing entirely new totals, but any intrepid developers trying out this procedure might have discovered a missing piece: While the previous articles offered a step-by-step for getting new totals into the cart and onto the final order, more effort is required for these totals to make it onto the order’s invoices and credit memos. If your order fulfillment process occurs within Magento, fancy custom totals can hardly be of any use without support for these components. Therefore, it’s about time to explore these final pieces.

Why Invoice and Credit Memo Collectors?

The chief complexities of the totals process we’ve looked at have been in the collector models that perform the necessary calculations while an order is still in the cart/checkout stage. Once a cart becomes an order, totals are just a series of numbers that need to be copied to that order.

One might expect the process to be just that simple – as simple as copying numbers – when an invoice or a credit memo is created. And if an order only ever produced a single invoice, this might be the case. Complexity enters in again, however, due to the ability to invoice or refund only a portion of an order. If I’m currently invoicing only 2 of the 5 items for an order, Magento needs to know how to calculate how much of each and every total should be part of the invoice. For the native shipping total, the entire amount is always included in the first invoice. For the tax total, however, the process is more complicated, requiring calculation to be done for specific invoiced items.

This level of complexity is starting to sound like what we faced in the cart process. And in a manner of speaking, that’s exactly what we have again – a “cart” being built toward the creation of an invoice or a credit memo rather than an order, and potentially involving different calculations. After all, we’re going to have to consider things like what amount has been invoiced previously. So you’ve probably guessed by this point what we’re dealing with: dedicated collector models just for this purpose.

A Familiar Process

Luckily, having peered under the hood of how to quote (i.e., cart) totals are declared and managed, we’re well equipped to understand invoice and credit memo totals because the process is much the same. Step one is to declare these totals and the models associated with them in configuration XML. Here are examples from the core:

    . . .
    
        
            
                . . .
                
                    sales/order_invoice_total_shipping
                    subtotal,discount
                    grand_total,tax
                
                . . .
            
        
        
            
                . . .
                
                    sales/order_creditmemo_total_shipping
                    subtotal,discount
                    grand_total,tax
                
                . . .
            
        
    
    . . .

And as with quotes and orders, the majority of totals will involve new fields to store the appropriate amounts on the invoice and credit memo tables, and in some cases, the invoice item and credit memo item tables as well. As an example, the fields “tax_amount” and “base_tax_amount” exist in the following tables:

• sales_flat_invoice
• sales_flat_invoice_item
• sales_flat_creditmemo
• sales_flat_creditmemo_item

The declared total models contain a “collect” method, just like their quote counterparts. The “fetch” method that was necessary for quote totals, however, isn’t present in this case. This is because the display process for invoice and credit memo totals cleaves much closer to that of orders than to the way things are handled in the cart. (I’ll delve into that shortly.)

A final note about the basic process of defining totals: We previously examined the use of the config node “fieldsets” to transfer amounts from quote to order. (The node “global/fieldsets/sales_convert_quote_address/shipping_amount/to_order” is responsible for a quote’s shipping total making it to the resulting order.) You won’t find the same procedure for order-to-invoice or order-to-credit-memo, though. This process will work; defining the node “global/fieldsets/sales_convert_order/shipping_amount/to_invoice” will indeed result in an order’s shipping amount being copied to every invoice created from it. This isn’t really what we want, though. To reiterate, the totals calculation for invoices and credit memos isn’t simply a matter of copying numbers; if I create three credit memos from the same order, I hardly want the order’s full tax amount refunded on each. (And doing so without a collector model still won’t successfully affect the credit memo’s grand total, as we’ll see.)

A note for clarity: Just as the collectTotals process is run each time we view a cart on the front-end, re-calculating all amounts with each change, the same is true when the creation of an invoice or credit memo is in progress. As the invoice is being prepared and updated (by the admin user changing quantities to be invoiced, for example), the collection process runs over and over to update the totals we see. It’s not a process that occurs only on the final creation of the invoice (or credit memo).

A Look at Displaying Totals

The block class Mage_Sales_Block_Order_Totals and its children, Mage_Sales_Block_Order_Invoice_Totals and Mage_Sales_Block_Order_Creditmemo_Totals, are responsible for the rendering of totals in order confirmation emails and the account section in the front-end. The related classes Mage_Adminhtml_Block_Sales_Order_Totals, Mage_Adminhtml_Block_Sales_Order_Invoice_Totals, and Mage_Adminhtml_Block_Sales_Order_Creditmemo_Totals take this job for the admin. Here is an example of a block declaration in a layout XML file:

<adminhtml_sales_order_view>
    . . .
    <reference name="left">
        <block type="adminhtml/sales_order_view_tabs" name="sales_order_tabs">
            <block type="adminhtml/sales_order_view_tab_info" name="order_tab_info" template="sales/order/view/tab/info.phtml">
                . . .
                <block type="adminhtml/sales_order_totals" name="order_totals" template="sales/order/totals.phtml">
                . . .
            </block>
        . . .
        </block>
    </reference>
    . . .
</adminhtml_sales_order_view>

In the previous entry “Orders and Caveats” in this series, I initially claimed that rewriting these blocks were required to successfully display custom totals at the order stage, only to backpedal on this thanks to some helpful advice from Vinai Kopp! Since the display of invoice and credit memo totals works the same way as orders, this provides us an excellent opportunity to go back and explore the better alternative.

Each of these blocks calls “initTotals” on any of its child blocks that have such a method. If we define such child blocks for custom totals, these classes can, in turn, grab their parent and call addTotal or addTotalBefore to modify the totals array, thus eliminating the need for block rewrites. Here’s an example of adding a custom total block to each parent within layout XML:

<adminhtml_sales_order_view>
    <reference name="order_totals">
        <block type="mymodule/sales_order_totals_mytotal" name="total_mytotal" />
    </reference>
</adminhtml_sales_order_view>

<adminhtml_sales_order_invoice_new>
    <reference name="invoice_totals">
        <block type="mymodule/sales_order_totals_mytotal" name="total_mytotal" />
    </reference>
</adminhtml_sales_order_invoice_new>

<adminhtml_sales_order_creditmemo_new>
    <reference name="creditmemo_totals">
        <block type="mymodule/sales_order_totals_mytotal" name="total_custom_mytotal" />
    </reference>
</adminhtml_sales_order_creditmemo_new>

And the example code for the block itself:

class Me_MyModule_Block_Sales_Order_Totals_Mytotal 
    extends Mage_Core_Block_Abstract
{
    public function getSource()
    {
        return $this->getParentBlock()->getSource();
    }

    /**
     * Add this total to parent
     */
    public function initTotals()
    {
        if ((float)$this->getSource()->getMyTotalAmount() == 0) {
            return $this;
        }
        $total = new Varien_Object(array(
            'code'  => 'mytotal',
            'field' => 'mytotal_amount',
            'value' => $this->getSource()->getMyTotalAmount(),
            'label' => $this->__('My Total')
        ));
        $this->getParentBlock()->addTotalBefore($total, 'shipping');
        return $this;
    }
}

The only cumbersome part is making sure to apply this update to all of the appropriate layout handles. Here is a list of relevant layout update handles in the front-end:

• sales_order_view
• sales_order_invoice
• sales_order_creditmemo
• sales_order_print
• sales_order_printinvoice
• sales_order_printcreditmemo
• sales_email_order_items
• sales_email_order_invoice_items
• sales_email_order_creditmemo_items
• sales_guest_view
• sales_guest_invoice
• sales_guest_creditmemo
• sales_guest_print
• sales_guest_printinvoice
• sales_guest_printcreditmemo

And here are the relevant handles in admin:

• adminhtml_sales_order_view
• adminhtml_sales_order_invoice_new
• adminhtml_sales_order_invoice_updateqty
• adminhtml_sales_order_invoice_view
• adminhtml_sales_order_creditmemo_new
• adminhtml_sales_order_creditmemo_updateqty
• adminhtml_sales_order_creditmemo_vie

A complete explanation of customizing these areas will be included in the comprehensive example that concludes this series.

A Simple Start

Beyond the simple declaration of invoice and credit memo totals, the important question is how their calculations differ from those in a quote. If you have an eye for building your own custom total, that’s the crux of the matter you’ll have to decide for yourself: How do you want the amount to be calculated when only a portion of an order is invoiced or refunded? Is your custom total fully payable immediately on the first invoice (like the native shipping total)? Does your custom discount need to be re-calculated for the entire order if one item is refunded (and the amount applied to the refund adjusted accordingly)?

These are the tough questions to work through, but at the very least the native total collectors give some insight into what data is available for your calculations and how to get at it. To start with, let’s assume a custom total is tracked only on the entire order, rather than on individual items, and that we have no need to split it up between multiple invoices or refunds. The native “collect” methods on Mage_Sales_Model_Order_Invoice_Total_Shipping and Mage_Sales_Model_Order_Creditmemo_Total_Shipping provides us an equivalent example and demonstrates some of the things we’ll need to deal with.

• “getOrder” can be called on both the invoice and credit memo models (passed as parameters to the respective “collect” methods) to retrieve totals or other information directly from the order.
• An order’s “getInvoiceCollection” and “getCreditMemosCollection” methods will be important for retrieving any past invoices or credit memos that have been created (and thus any amounts already invoiced/refunded). When looping through invoices, the convenient method “isCanceled” can be used to exclude invoices that are no longer valid.
• You’ll see frequent use of calls like “getShippingRefunded” on the order model. As handy as this is for finding out what amount of a total has already been invoiced/refunded, it relies not only on dedicated fields in the order table, but also the updates to these fields carried out in the invoice and credit memo models’ “register” methods that occur when these entities are finally saved. For custom totals, you can establish similar “total_name_invoiced”/”total_name_refunded” fields on the order table and use the “sales_order_invoice_register” event to update them, or simply fetch previous invoice or credit memo collections to obtain the data.
• There is no “_addAmount” method here as in the quote totals. Instead, amounts need to be set directly on the invoice or credit memo – not just the specific total amount, but the adjusted grand total as well.

Here’s an incredibly stripped down version of an invoice “collect” method for a hypothetical custom total:

    public function collect(Mage_Sales_Model_Order_Invoice $invoice)
    {
        $invoice->setMyTotalAmount(0);
        $invoice->setBaseMyTotalAmount(0);

        foreach ($invoice->getOrder()->getInvoiceCollection() as $prevInvoice) {
            if ($prevInvoice->getMyTotalAmount() && !$prevInvoice->isCanceled()) {
                return $this;
            }
        }

        $myTotalAmt = $invoice->getOrder()->getMyTotalAmount();
        $baseMyTotalAmt = $invoice->getOrder()->getBaseMyTotalAmount();

        $invoice->setMyTotalAmount($myTotalAmt);
        $invoice->setBaseMyTotalAmount($baseMyTotalAmt);

        $invoice->setGrandTotal($invoice->getGrandTotal()+$myTotalAmt);
        $invoice->setBaseGrandTotal($invoice->getBaseGrandTotal()+$baseMyTotalAmt);

        return $this;
    }

Digging Deeper

If your custom total does need to be tracked per item, or if you need to divide it between multiple invoices and credit memos, you likely have some more complex calculations to do in your models. If we look to some of the other native totals logic (such as in Mage_Sales_Model_Order_Invoice_Total_Tax and Mage_Sales_Model_Order_Creditmemo_Total_Tax), here are some additional useful details we can glean:

• Naturally, when looping through an invoice’s or credit memo’s items via the “getAllItems” method, the corresponding order item can be retrieved with “getOrderItem.”
• And speaking of order items: These models contain the handy method “isDummy”, providing a useful and straightforward check for items that should be ignored in any calculations. (These come into play when a single order line item is represented by both a parent and child record for a composite product. A configurable product’s child items are considered “dummy” items, while in the case of bundled products, it’s the parent item that is.)
• Both the invoice and credit memo models have an “isLast” method frequently used in totals collection. An invoice or credit memo is the “last” when the quantities being invoiced/refunded represent all of the remaining quantities on the order. Where an amount can be split over multiple invoices or refunds, it’s a best practice to use this check to calculate the last differently in order to safeguard against rounding errors or other unexpected calculation issues. (For example: “For any invoice that is not the last, invoice a percentage of the order total based on item quantities. For the last invoice, invoice remaining un-invoiced portion of the order total.)
• Note that a credit memo can be created in the context of a specific invoice, or merely in the context of the entire order. If the details of the specific invoice being refunded factor into total collection, the appropriate model can be retrieved with “getInvoice” on the credit memo.

NOTE: Use the “isLast” check with caution, as it is apparently unreliable in the case of composite products being invoiced/refunded. In my testing, I observed this “isLast” method resulting in a false negative when configurable products were present in the invoice. “isLast” on an invoice or credit memo model runs an identically named method on the relevant items, which compares the current quantity with the quantity left to invoice/refund. However, the aforementioned “isDummy” condition is applied in one case and not the other, leading to an inaccurate comparison. Luckily, the logic performed here is not difficult to duplicate, and you can perform the same basic check in your own collection logic with this issue corrected. (Look for an example in the final article in this series.)

In summary, the collection process for invoices and credit memos doesn’t differ fundamentally from that of quotes (at least in terms of technical implementation). Whether the calculations involved are simple or headache-inducing, the exploration touched on here should illuminate how Magento treats these components of the order lifecycle.

The example that comprises the last article in this series (linked below) has been updated with the inclusion of these long overdue final missing pieces.

Next entries

• Part 5: An Example

Recently, I posted about avoiding common coding problems. These practices can help to reduce debugging overall time. Even after common coding issues have been reduced, you are often still left with a need to debug more complex problems. These are the bugs that you either didn’t write, which are sometimes buried in the core of the framework or application on which you are working (in our case, Magento), or that stem from incorrect or incomplete logic. These types of problems can often require a variety of techniques to debug, especially when the bug is in the code you neither wrote nor have seen.

Example

The Problem

A client was having a problem where no product on a grouped product page was able to be added to the cart. All qty text boxes were hidden, and it was ultimately represented the same way an out of stock grouped product was represented.

Debugging Methodology

1. Start Simple
2. Check for common mistakes
3. Check the quickest things first
4. Isolate the specifics of the problem
5. Isolate the source of the problem
6. Determine the factors that could possibly impact the source of the problem
7. Create logs to determine which factor is the culprit
8. Fix the bug whether code, user, or data related

Solution

1. Capture the Current State

The first step before doing any debugging is to create a copy of the database. Sometimes, especially with complex eCommerce sites with multiple integrations, a problem can be difficult to isolate. If I accidentally “fix” the problem while debugging it, my efforts at fixing the problem long term can be delayed until the problem occurs again. It is also good practice to debug apart from production if the problem can be reproduced in a development environment.

2 & 3. Check for Quick Fixes & Common Mistakes

Once I had that backup, I start by checking things in the Magento admin like stock, qty, product status, product websites, product visibility, etc. of the parent grouped product and its child simple products. In this case, I determined that according to the admin, all of the products had satisfactory attribute values. There were at least a few products that were enabled, in the appropriate website, in stock, with a qty greater than the out of stock qty level, and attached correctly to the grouped product. Next, I went to the code that determines whether a product is eligible to be purchased. This code is somewhat spread out, but I determined with a fair degree of certainty that there should have been products eligible to purchase.

4. Isolate the Specifics

Because I had determined that this problem was most likely not caused by a simple mistake and I had checked the quickest things, I moved on to isolate the specifics of the problem. Since the quantity text boxes were hidden, I knew that whatever logic was causing the products to not be able to be added to the cart was associated with the grouped product page. I opened up lib/Zend/Db/Adapter/Abstract.php, the file that processes most of the actual SQL calls that Magento uses and their bindings before the query is sent to the DB, and added some log statements. I logged every query that was made along with its associated bindings. I then created a log before the products on the grouped product page were loaded and a log after they were loaded. By doing this, I was able to determine which queries were most likely responsible for determining whether the products should be eligible for addition to the cart.

A new tool from Zend, called Z-Ray, would have made this query logging process simpler since it automatically displays all queries when enabled. I highly recommend using this tool for debugging your applications since it makes your debugging information available to you without having to add any logging or enable profiling.

5. Isolate the Source

Next, I then looked for a query that looked like it was dealing with stock. With this query in hand, I went to the database and ran the query to see its results. There were no valid results from this query. I now had the specifics of the problem (no valid stock items being returned from the DB), so I started removing filters from the query until it would return rows. By using this method, I was able to determine that the problem was related to the stock status that was set on the grouped product itself. I had isolated the source of the problem.

6. Determine Factors Affecting the Source

I next investigated where in the code this stock status was set and what conditions were used to determine whether the stock status should be in stock or out of stock. I determined that the stock status was supposed to be “in stock” based on the conditions in the code where stock status was updated. According to the code, the conditions should have produced an “in stock” status, but the grouped product was nevertheless “out of stock.” This explained why I had not found anything wrong with the attributes of grouped and associated simple products when I had done my initial debugging in my “check the quickest things” first step. Since I knew that in this case, stock, which is stored on the simple product, was being updated dynamically through the Magento API, I looked at the logic that determined stock status from this stock information. Sure enough, there was a separate set of logic for generating stock status compared to if the stock item were updated through the product admin section. Since the stock status code was distinct for updating stock via the API and I was not able to reproduce the problem by saving the same stock levels on the product in the admin, I determined that I needed to either create sandbox API calls or log the live integration. Saving in the product actually was fixing the problem, which gave me clear evidence that I needed to find out what the API was doing differently.

7. Create Logs to Identify the Culprit

Because the budget was more of a constraint than the timeline, I opted to log data on the live integration and wait for the next time this was broken. I fixed the data temporarily by saving a few grouped products and set up logging statements that would allow me to see the API parameters, the stock items, the stock status, and the sql queries for each API call. By doing this, I was able to determine the exact API call/parameters used to allow me to create a non-API based sandbox call and step through the stock status update process in a debugger since I had determined the factors that impacted the source of the problem and logged their values. Since I was trying to identify why a grouped product’s stock status was false, I identified the array that was supposed to contain a list of the child simple products stock statuses keyed by their ids. The array that was returned for the child products statuses was an array with only a single key/value pair where the key was a 1 and the value was a seemingly random number.

By looking at the code that was generating the child product stock statuses, I was able to identify the problem. The query that was getting statuses from the database was switching the order of the keys and the values. The stock status, which was a 1, was being represented as the key and the key was being represented as the value. Since the stock status for each child product was 1, the resulting array was a key of 1 and a value of the final child product id. The children products’ stock statuses were then being used to update the parent stock status and since there was no valid child product stock status marked as in stock, the parent product was being marked as out of stock.

If you would like to view the relevant methods where I found this problem, you can look at Mage_CatalogInventory_Model_Resource_Stock_Status::saveProductStatus and Mage_Catalog_Model_Resource_Product_Status::getProductStatus in the else section under the $select and $rows variable assignment. I was working with EE 1.12.0.2 but the error likely exists in or around CE 1.7.0.2 as well.

8. Resolve the Issue

I was able to fix the problem with a simple rewrite of the Mage_Catalog_Model_Resource_Product_Status class by changing the order of the columns in the select in the getProductStatus() method.

Ultimately, by following the steps listed in the debugging methodology section above, I was able to identify the problem relatively quickly. The order of the steps I followed is important because at any point I may have identified the problem and not had to continue debugging the problem. I highly recommend using the above methodology or similar to arrive at a solution most quickly and ultimately save time, money, and frustration.

Internationalization is an increasingly important consideration for Magento merchants developers looking to expand market penetration and increase usability. A significant part of this effort is realized in the form of maintaining translations for multiple locales – quite the undertaking, in spite of Magento’s robust localization capabilities.

However, a journey of a thousand miles begins with a single step, and this initial step can be particularly daunting. What must be translated?

Ideally, every string ever used, be it backend or frontend, would be documented so that an exhaustive list is always available of material scheduled for translation. In practice, however, this is rarely the case – maybe the site or module wasn’t initially slated for an international market or the ROI was difficult to justify. Because of this, orphan strings with no record of their existence are very common and a barrier to internationalization.

Wouldn’t it be nice to have a mechanism to retroactively examine a site or module and perform a translation gap analysis?

Approach

One approach to ferreting out untranslated strings is to modify the translation tool itself to report untranslated strings as they are encountered. This is often expressed as a quick hack to the translation classes whereby strings are logged, then the changes reverted.

The basic idea is solid, but the execution is essentially a transient hack – requiring repeated discovery and implementation, and is prone to oversights.

After a recent internationalization-intensive project involving several countries and localizations, I attempted to formalize this approach into a robust module.

Translation path

When calling the familiar __() method (whether from a block, helper, controller, etc), Mage_Core_Model_Translate::translate() is invoked to do the actual heavy lifting.

Mage_Core_Model_Translate::translate()

/**
 * Translate
 *
 * @param array $args
 * @return string
 */
public function translate($args) {
    $text = array_shift($args);
    
    if (is_string($text) & amp; & amp;
    '' == $text || is_null($text) || is_bool($text) & amp; & amp; false === $text || is_object($text) & amp; & amp;
	'' == $text - & gt; getText()) {
		return '';
	}
    if ($text instanceof Mage_Core_Model_Translate_Expr) {
        $code = $text - & gt;
        getCode(self::SCOPE_SEPARATOR);
        $module = $text - & gt;
        getModule();
        $text = $text - & gt;
        getText();
        $translated = $this - & gt;
        _getTranslatedString($text, $code);
    } else {
        if (!empty($_REQUEST['theme'])) {
            $module = 'frontend/default/'.$_REQUEST['theme'];
        } else {
            $module = 'frontend/default/default';
        }
        $code = $module.self::SCOPE_SEPARATOR.$text;
        $translated = $this - & gt;
        _getTranslatedString($text, $code);
    }

    //array_unshift($args, $translated);
    //$result = @call_user_func_array('sprintf', $args);

    $result = @vsprintf($translated, $args);
    if ($result === false) {
        $result = $translated;
    }

    if ($this - & gt; _translateInline & amp; & amp; $this - & gt; getTranslateInline()) {
		if (strpos($result, '{{{') === false || strpos($result, '}}}') === false || strpos($result, '}}{{') === false) {
			$result = '{{{'.$result.
				'}}{{'.$translated.
				'}}{{'.$text.
				'}}{{'.$module.
				'}}}';
		}
    }

    return $result;
}

After doing some heuristics to determine exactly what module and code to use for context, Mage_Core_Model_Translate::_getTranslatedString() takes over.

Mage_Core_Model_Translate::_getTranslatedString()

/**
 * Return translated string from text.
 *
 * @param string $text
 * @param string $code
 * @return string
 */
protected function _getTranslatedString($text, $code)
{
    $translated = '';
    if (array_key_exists($code, $this->getData())) {
        $translated = $this->_data[$code];
    } 
    elseif (array_key_exists($text, $this->getData())) {
        $translated = $this->_data[$text];
    } 
    else {
        $translated = $text;
    }
    return $translated;
}

If a translation by either code or text exists, then it is returned – otherwise, the key (untranslated string) is returned.

Implementation

The Mage_Core_Model_Translate::_getTranslatedString() method offers a perfect opportunity to detect strings with no translation and record them.

String Detection

Unfortunately, there is no event present which can be observed to accomplish this detection, so the model must be rewritten. Using the lightest touch possible for such a fundamental model, the string is preprocessed before translation to determine if it is missing translations for interesting locales.

EW_UntranslatedStrings_Model_Core_Translate::_getTranslatedString()

/**
 * Evaluate translated text and code and determine
 * if they are untranslated.
 *
 * @param string $text
 * @param string $code
 */
protected function _checkTranslatedString($text, $code) {
    Varien_Profiler::start(__CLASS__ . '::' . __FUNCTION__);
    Varien_Profiler::start(EW_UntranslatedStrings_Helper_Data::PROFILER_KEY);
    
    //loop locale(s) and find gaps
    $untranslatedPhrases = array();
    foreach($this->_getLocalesToCheck() as $locale) {
        if(!Mage::helper('ew_untranslatedstrings')->isTranslated($text,$code,$locale)) {
            $untranslatedPhrases[] = array(
                'text' => $text,
                'code' => $code,
                'locale' => $locale
            );
        }
    }
    $this->_storeUntranslated($untranslatedPhrases);

    Varien_Profiler::stop(EW_UntranslatedStrings_Helper_Data::PROFILER_KEY);
    Varien_Profiler::stop(__CLASS__ . '::' . __FUNCTION__);
}

/**
 * Check for translation gap before returning
 *
 * @param string $text
 * @param string $code
 * @return string
 */
protected function _getTranslatedString($text, $code)
{
    if(Mage::helper('ew_untranslatedstrings')->isEnabled()) {
        $this->_checkTranslatedString($text, $code);
    }
    
    return parent::_getTranslatedString($text, $code);
}

This simple change allows the module to collect untranslated strings, which, for performance sake, are batched up and flushed to the database in a single query after the page is rendered. All that is required to perform this collection is to enable the module in the system configuration, and browse the site – a perfect scenario for stage sites during ongoing UAT.

Multiple Locales

If a merchant or developer is concerned about translation gaps for one locale, he is likely also concerned about several others. This is facilitated by the loop in _checkTranslatedString() which iterates over multiple locales which are ultimately retrieved from the system configuration.

To check the status of translations other than the currently selected locale, EW_UntranslatedStrings_Helper_Data::isTranslated() performs evaluations independent of current store configuration, aided by getTranslator() which provides ready-to-go translator models for any locale and store ID.

EW_UntranslatedStrings_Helper_Data::isTranslated() and getTranslator()

/**
 * Get translator prepared for given locale
 *
 * @param $locale
 * @param bool $allowMatchingKeyValuePairs - matching key / value pairs count as translations
 * @param null $storeIdContext
 * @param bool $forceRefresh
 * @return EW_UntranslatedStrings_Model_Core_Translate
 */
public function getTranslator($locale, $allowMatchingKeyValuePairs = null, $storeIdContext = null, $forceRefresh = false) {
    if(!isset($this->_translators[$locale])) {
        if(is_null($allowMatchingKeyValuePairs)) {
            // "allow" and "log" are opposite concepts
            $allowMatchingKeyValuePairs = !$this->logMatchingKeyValuePairs();
        }

        /* @var $translate EW_UntranslatedStrings_Model_Core_Translate */
        $translate = Mage::getModel('ew_untranslatedstrings/core_translate');
        $translate->setConfig(
            array(
                Mage_Core_Model_Translate::CONFIG_KEY_LOCALE => $locale
            )
        );
        $translate->setLocale($locale);
        $translate->setAllowLooseDevModuleMode(true); //prevent native dev mode differences
        $translate->setAllowMatchingKeyValuePairs($allowMatchingKeyValuePairs);
        if(!is_null($storeIdContext)) {
            $translate->setThemeContext($storeIdContext);
        }
        $translate->init(Mage_Core_Model_Design_Package::DEFAULT_AREA, $forceRefresh);

        $this->_translators[$locale] = $translate;
    }

    return $this->_translators[$locale];
}

/**
 * Does text/code have translation for given locale?
 *
 * @param $text
 * @param $code
 * @param $locale
 * @return bool
 */
public function isTranslated($text, $code, $locale) {
    /* @var $translate EW_UntranslatedStrings_Model_Core_Translate */
    $translate = $this->getTranslator($locale);

    return $translate->hasTranslation($text, $code);
}

While it requires a few more rewritten methods on the translator model, this feature allows a store owner or developer to quickly assemble a gap analysis of multiple locales at once.

Configurability

Although simple in concept, there are several optional tweaks that can make this feature much more useful for efficient string collection.

This module adds a new system configuration section which can be found at Advanced -> Developer -> Untranslated Strings.

These options allow several powerful options for customizability. For example

• Admin translation gaps can be ignored – useful to enable for all websites, but purposely omit admin strings.
• Matching translation key/value pairs can be either logged or ignored. This accommodates either of the following scenarios:
  • Complete translation gap analysis, where strings which are represented but not actually translated are logged – useful for merchant site evaluation.
  • String representation gap analysis, where only strings which are not represented at all are logged – useful for module developers who only wish to maintain a list of strings and are not concerned with actual translation.
• Translation code exclusion patterns which omit some strings from the log. Magento has many native translation gaps, and the translation status of some modules may be irrelevant to a given store. This allows final users to tweak the signal-to-noise ratio to ensure efficient use of resource when evaluating untranslated strings.
• Batch locale translation gap analysis. As mentioned before, this allows the evaluation of several locales at one time, greatly reducing the time required to collect an exhaustive list of strings.

Reports

Even the most robust untranslated string detection is useless if the results are not easily accessible and actionable.

To this end, there are two new reports which are now available at Reports -> Untranslated Strings in the main admin menu.

• Untranslated Strings Summary: this provides an overview of translation gaps by locale and store, and allows the ability to purge or truncate untranslated strings.
• Untranslated Strings Report: this provides a detailed view of untranslated strings and is filterable by locale, store, popularity, etc.

Additionally, the Untranslated Strings Report allows strings to be exported to CSV file. Combined with its filtering capabilities, this allows a merchant or developer to effectively manage the strings and provide them to translation teams, along with all important context.

Maintenance

Viewing the untranslated strings report as a sort of “to-do list”, the summary view allows merchants or developers to curate the remaining untranslated strings. In particular, the strings associated with a given locale and store can be truncated (individually or en masse), cleaning the slate and allowing a fresh set of strings to be collected.

More powerful, however, is the purge option. Exercising this option on a locale and store (or group of these, via the mass action functionality) causes the module to reevaluate the translation status of associated strings, removing any which are now considered translated according to the system configuration settings.

After adding translations to address gaps, this feature allows merchants and developers to cull strings which are no longer relevant, ensuring that the untranslated string list is always actionable.

Where to go from here (caveats)

While this module goes a long way to formalize a former “hacky” process, there is always room to improve. Some particular things to keep in mind:

• This module only assists with strings which are wrapped in translate calls – if strings are simply never translated, there is no way to detect them. On the same token, strings which are meant to be translated using admin store scopes (such as product attributes, CMS blocks, etc) are not evaluated.
• The module introduces a small to moderate performance overhead depending on the number of locales to be evaluated and the number of untranslated strings. Fortunately, this is only realized if the functionality is enabled in system configuration.
• As with Magento’s inline translation tool, the module works best if translation and block caches are disabled.
• Like the shoemaker’s wife, the module itself has a few translation gaps …

Where to get it

If you’d like to get your hands on this open source module and take the first step toward internationalization, you can find it on Github:

https://github.com/ericthehacker/magento-untranslatedstrings

As stated in the readme, it’s easily installed via modman. Use it wisely!

Interested in building an international e-commerce store or looking to upgrade the one you have? Let us know on our contact form!

Magento comes with a number of built-in reports covering areas like sales, taxes, customers, products, abandoned carts, and reviews. However many merchants have reporting needs beyond Magento has to offer. For some merchants, a third-party extension like Aheadworks’ Advanced Reports module will do the trick. However, if an existing module doesn’t fit the bill, then you’ll need to build a custom report. Traditionally, a Magento developer would build a report into Magento following the same pattern of Magento’s native reports. However, this approach can take a while and will often be cost-prohibitive.

To make building custom reports an easier task, Kalen Jordan built a module called Clean_SqlReports that allows you to take a MySQL query and automatically turn it into a Magento report. During the Magento Imagine 2014 Hackathon, Kalen worked with a number of others to enhance the module to add support for the Google Charts API, which means you can build some really visual reports. In this blog post, I’m going to demonstrate how to build a few different types of reports using this extension (some examples inspired by the module readme):

• Top 500 Customers by Lifetime Volume (Plain Table)
• Order Status (Pie Chart)
• Order Status by Month (Stacked Column Chart)

Module Setup

First, you will need to install the module: https://github.com/kalenjordan/custom-reports You can install it via modman, Composer, or download a ZIP of the module and copy it into your Magento installation. Once you have the module installed, log into the admin. Since this module runs MySQL code that is entered via the admin, someone with access to add/edit reports could wreak havoc on the site, if they knew the proper MySQL to enter (think TRUNCATE or DROP). To overcome this, go to “System > Permissions > Roles”. You should have at least one role for users that you don’t want to have access to add/edit reports.

Types of Reports

The module has two primary forms of reports:

• Plain Table – this report type is similar to what you get with Magento’s native reports. You won’t be able to do things like filter by date range and change how the report is grouped, but a business user can export the report to CSV or Excel and do additional filtering in a spreadsheet application.
• Google Charts API – this report type allows you to use nearly any of the Google Chart types. Check out these examples. Be aware that you need to write your MySQL query to return the data in the format required by be Google chart.

Report #1 – Top 500 Customers by Lifetime Volume

Now that you’ve setup the permissions for this extension, let’s create our first report. For this example, we’re going to grab the top 500 customers and display their lifetime order value, the total number of orders, and the last date they ordered. Go to “Reports > Special Reports”. Click “Add Report”. For the title, enter “Top 500 Customers by Lifetime Volume”. For the “SQL”, enter this query:

SELECT 
    -- Round to two decimal places and prepend with $
    CONCAT('

Now, save the report and run it by clicking on that row.

Report #2 – Order Status

Create a new report with the title of “Order Status”. Select “Pie Chart” from the “Output Type” field. Enter the following SQL:

SELECT 
    sales_order_status.label AS 'Status',
    COUNT(sales_flat_order.entity_id) AS 'Orders'
FROM sales_flat_order
LEFT JOIN sales_order_status ON sales_flat_order.status = sales_order_status.status
GROUP BY sales_flat_order.status
ORDER BY COUNT(sales_flat_order.entity_id) DESC

The “Chart Configuration” field needs to be a JSON object and corresponds to the “Configuration Options” section of the Pie Chart page. Enter this value into the “Chart Configuration” field:

{
    height: 900,
    width: 1100,
    title: 'Order Status',
}

Report #3 – Order Status by Month

Building a single MySQL query that groups data by months is a bit messy, as you have to “hard code” the columns you want have included in the report. In our example, we’re going to hard code the most common order statuses as columns in our select statement. While it is possible to dynamically create columns from statuses, that is outside the scope of this article. Create a new report with the title of “Average Products per Order by Month”, select the “Column Chart” Output Type and then enter this SQL:

SELECT
    -- "Year - Month"
    CONCAT(YEAR(sales_flat_order.created_at), ' - ', MONTHNAME(sales_flat_order.created_at)) AS 'Month',
    SUM(IF(`status` = 'canceled', 1, 0)) AS 'Canceled',
    SUM(IF(`status` = 'closed', 1, 0)) AS 'Closed',
    SUM(IF(`status` = 'complete', 1, 0)) AS 'Complete',
    -- Custom status
    SUM(IF(`status` = 'complete_partially_shipped', 1, 0)) AS 'Partially Shipped',
    SUM(IF(`status` = 'processing', 1, 0)) AS 'Processing',
    -- Custom status
    SUM(IF(`status` = 'shipped', 1, 0)) AS 'Shipped'
FROM sales_flat_order
GROUP BY MONTH(sales_flat_order.created_at)

Enter this value into the “Chart Configuration” field:

{
    isStacked: true, // Important, as this turns the column into a stacked chart
    height: 600,
    width: 1400,
    title: 'Order Status by Month',
    vAxis: {
        title: 'Number of orders'
    }
}

NOTE: Magento saves all records in the database using UTC, so when you run reports based on date, it will be reporting based on UTC, not your local timezone. Magento’s native reports account for this adjustment, but a raw MySQL query does not. However, there is a way to overcome this: use the DATE_ADD function to adjust for your time zone. Assuming you are in central time (and it is daylight savings time), you are UTC – 5. So replace the GROUP BY statement in the above query with this, and voilà, your report now accounts for the timezone difference:

GROUP BY MONTH(DATE_ADD(sales_flat_order.created_at, INTERVAL -5 HOURS))

Conclusion

Now that you’ve seen some of the things that you can do with the Clean_SqlReports module, go build some reports!

, FORMAT(SUM(sales_flat_order.`grand_total`), 2)) AS 'Lifetime Sales', COUNT(sales_flat_order.entity_id) AS 'Orders', customer_entity.email AS 'Email', MAX(sales_flat_order.created_at) AS 'Most Recent Order Date' FROM `customer_entity` LEFT JOIN sales_flat_order ON customer_entity.entity_id = sales_flat_order.customer_id GROUP BY customer_entity.entity_id ORDER BY SUM(sales_flat_order.`grand_total`) DESC LIMIT 500

Now, save the report and run it by clicking on that row.

Report #2 – Order Status

Create a new report with the title of “Order Status”. Select “Pie Chart” from the “Output Type” field. Enter the following SQL:

The “Chart Configuration” field needs to be a JSON object and corresponds to the “Configuration Options” section of the Pie Chart page. Enter this value into the “Chart Configuration” field:

Report #3 – Order Status by Month

Building a single MySQL query that groups data by months is a bit messy, as you have to “hard code” the columns you want have included in the report. In our example, we’re going to hard code the most common order statuses as columns in our select statement. While it is possible to dynamically create columns from statuses, that is outside the scope of this article. Create a new report with the title of “Average Products per Order by Month”, select the “Column Chart” Output Type and then enter this SQL:

Enter this value into the “Chart Configuration” field:

NOTE: Magento saves all records in the database using UTC, so when you run reports based on date, it will be reporting based on UTC, not your local timezone. Magento’s native reports account for this adjustment, but a raw MySQL query does not. However, there is a way to overcome this: use the DATE_ADD function to adjust for your time zone. Assuming you are in central time (and it is daylight savings time), you are UTC – 5. So replace the GROUP BY statement in the above query with this, and voilà, your report now accounts for the timezone difference:

Conclusion

Now that you’ve seen some of the things that you can do with the Clean_SqlReports module, go build some reports!