Resolved - Need help tweaking the Order Complete email content

[Dirty Butter]

I had added the macros {$product.name} and {$product.product_code} to my emails some time ago, and for most of them it works just fine. But it does NOT work for the Order Complete email.

If possible, I would like for the Subject to be - Order Complete {foreach from=$PRODUCTS item=product} - {$product.name}{/foreach}

And I'd like to include links to the items, so they can add a Review to the products.

<tbody>

<tr>

<td>

<p>

	 <strong>Items to review</strong></p>

</td>

</tr>

<tr>

<td>

{foreach from=$PRODUCTS item=product}



<p>

	 <a href="{$STORE_URL}/index.php?search[keywords]={$product.product_code}&amp;_a=category">{$product.name}</a></p>

{/foreach}

<p>

	 &nbsp;</p>

</td>

</tr>

</tbody>

What do I need to do to make this work?

[bsmither]

"[The macros {$product.name} and {$product.product_code} do] NOT work for the Order Complete email."

In the file order.class.php at about line 405, find:
case self::ORDER_COMPLETE:

Near the end if this case block, we see that the only data being submitted to the email template is order_summary, while order_inventory is not. Once data about the inventory gets sent over to the template, we can then work on the template.

[Dirty Butter]

Would it be possible to have it call _order_summary, instead of _order_history, the way the Order Confirmation email does? There is a status column there, too.

[bsmither]

Your reply doesn't make sense.

"Would it be possible to have [it] call _order_summary, instead of _order_history, the way the Order Confirmation email does? There is a status column there, too."

It? The Order Complete email? The Order Complete email is triggered when the order acquires the Complete status, and as I said above, uses _order_summary data.

None of the status processors uses data from _order_history (other than to make sure an order passed through status #2).

So what are you asking for, again? Please be more specific.

[Dirty Butter]

OK, as usual I don't know enough to ask a sensible question, so I'll try again.

This is what made me think the Order Complete email is getting data from _order_history:

case self::ORDER_COMPLETE:

// Check that we have not skipped processing if not already disabled

if (!$GLOBALS['config']->get('config','no_skip_processing_check') && ($GLOBALS['db']->select('CubeCart_order_history', array('status'), array('cart_order_id' => $order_id, 'status' => 2))) === false) {

There IS a status column on the order_summary table. All the data I would like to incorporate in the Order Complete email is in the Order Confirmation email that looks to me like it's using data from the order_summary table.

// Compose the Order Confirmation email to the customer

if ($this->_email_enabled && ($content = $mailer->loadContent('cart.order_confirmation', $order_summary['lang'])) !== false) {

$order_summary['link'] = $GLOBALS['storeURL'].'/index.php?_a=vieworder&cart_order_id='.$order_id;

$GLOBALS['smarty']->assign('DATA', $order_summary);

$GLOBALS['smarty']->assign('BILLING', $billing);

$GLOBALS['smarty']->assign('SHIPPING', $shipping);

$GLOBALS['smarty']->assign('TAXES', $vars['taxes']);

$GLOBALS['smarty']->assign('PRODUCTS', $vars['products']);

$mailer->sendEmail($this->_order_summary['email'], $content);

unset($content);

}

[bsmither]

"There IS a status column on the order_summary table."

Yes, it holds the 'last-known status' of the order.

That 'status' column and any other column in the CC_order_summary table is available as part of the array $this->_order_summary. This array is passed to the Mailer's loadContent() function, which in turn is assigned to the Smarty template variable 'DATA'.

That means, if you want the 'status' as found in the CC_order_summary table, use {$DATA.status} in the email template. That will give you the status number. To get the status name, use:

{$LANG['order_state]['name_'.$DATA.status]}

(I think.)

[Dirty Butter]

My 70 year old brain is doing the best it can to grasp all this, but I'm struggling. Sorry.

So back to the original question.... Is there any way to get the product name and product order code into the Order Complete email, so I can create a link to the Review section for each item they bought?

[bsmither]

Ah!

Since the case self::ORDER_COMPLETE section has no current means of giving to Smarty any array of Product data, we must add it. Here's how:

Just after:

$content = $mailer->loadContent('cart.order_complete'...

add:

$GLOBALS['smarty']->assign('PRODUCTS', $this->_order_inventory);

Notice that these statements are inside a test that asks if this order is digital-only.

Next, we need to make sure the email template iterates through $PRODUCTS.

Notice in the Order Confirmation email, the Smarty code for iteration, and while not necessary to be this way, is encapsulated in HTML comment tags:

<!--{foreach from=$PRODUCTS item=product}-->

<!--{/foreach}-->

You won't see them in the normal view of the editor. They are there in the source-code view of the editor.

So, for your Order Complete email, be sure to add the Smarty loop (again, I feel there is no need to be inside HTML comment tags) around $product.name, $product.code, and $product.product_id when building your group of links.

[Dirty Butter]

The product links on the email work perfectly, and thank you for your patience!

Here's what I ended up with, both to get the products listed in the Order Complete email to the customer, and to send a BCC of that email to me:

/* no need to send this email for digital only orders */

	 if(!$this->_skip_order_complete_email) {

	 $content = $mailer->loadContent('cart.order_complete', $order_summary['lang'], $this->_order_summary);

$mailer->ClearBCCs(); $mailer->AddBCC($this->_notifyAdmins());	

}

$GLOBALS['smarty']->assign('PRODUCTS', $this->_order_inventory);

break;

But now it doesn't look like the BCC is working, so something I did trying to combine both your fixes must have messed something up.

[bsmither]

That should work. Did you check your spam folder?

[Dirty Butter]

It wasn't a spam filter issue. I had used a test address on my own store domain, and it didn't work. BUT, when I used a different email address it did!! I was also able to add the

$GLOBALS['smarty']->assign('PRODUCTS', $this->_order_inventory);

to the Order Cancelled code and create a better email there, as well.

[bsmither]

Bravo!

[Dirty Butter]

You have been an invaluable teacher. I can't thank you enough for all your help.

[Dirty Butter]

I tried to make one more change for myself, based on your explanation of adding

$GLOBALS['smarty']->assign('PRODUCTS', $this->_order_inventory);

The item path is stored in _seo_urls as path, and the item_id is there, but the product code is not.

<a href="{$STORE_URL}/index.php?search[keywords]={$product.product_code}&amp;_a=category">{$product.name}</a>

Right now my Review product link goes to the item by searching for the product code, which takes them to the unopened item - http://dirtybutter.com/plushcatalog/index.php?_a=category&search[keywords]=01PA080908x07.

I would love to be able to use the actual link to the review tab - http://dirtybutter.com/plushcatalog/fisher-price-puffalump-baby-big-things-hot-pink-hippo-tusks-diaper.html#customer-reviews

This looks more complicated than giving Smarty the product data, but hopefully it's just because I still don't understand enough.

[bsmither]

I haven't tried this yet, so keep a backup.

In the file order.class.php, somewhere near line 680, find:

private function _getInventory($order_id = null, $force_db = false) {

About 12 lines down, find this:

if (($products = $GLOBALS['db']->select('CubeCart_order_inventory', false, array('cart_order_id' => $order_id))) !== false) {

Change that line to this:

if (($products = $GLOBALS['db']->select('CubeCart_order_inventory LEFT JOIN CubeCart_seo_urls ON product_id = item_id', 'CubeCart_order_inventory.*,path', "`cart_order_id` = $order_id AND `type` = 'prod'")) !== false) {

This will add the 'path' from the seo_urls table for that product into the _order_inventory array, of which we just had Smarty assign to $PRODUCTS, of which we just added a loop to the email template.

So, construct your URL such like: {$STORE_URL}/{$product.path}.html#customer-reviews

[bsmither]

Additionally, you said the product_code is not available. True, not in the CC_seo_urls table, but it is present in the $PRODUCTS array.

{STORE_URL}/?_a=product&product_id={$product.product_id}#customer-reviews

[Dirty Butter]

I did understand enough to guess that it would take a JOIN of some kind. But I'm sorry to say that it did not work, YET. I was not able to change my test order to complete, as I only got a blank page.

I don't use standard order id numbers, so I'll have to see if I can figure out what needs to be done from other instances of this mod to get this to work here.

From the error log:

File: [order.class.php] Line: [952] "SELECT CubeCart_order_inventory.*,path FROM CubeCart_order_inventory LEFT JOIN CubeCart_seo_urls ON product_id = item_id WHERE `cart_order_id` = 2012-PC-1988 AND `type` = 'prod' ;" - Unknown column 'PC' in 'where clause'

[Dirty Butter]

Maybe this would give you an idea of how to tweak it to match the mod:

cubecart.class.php about line 1928

/**

  * Orders

  */

private function _orders() {

  /* !Order history */

  $template = 'templates/content.orders.php';

  if ($GLOBALS['user']->is()) {

   $GLOBALS['gui']->addBreadcrumb($GLOBALS['language']->account['your_account'], 'index.php?_a=account');

   $GLOBALS['gui']->addBreadcrumb($GLOBALS['language']->account['your_orders'], currentPage(array('cart_order_id'), null, false));

   /* original 5.1.4 version - if (isset($_GET['cart_order_id']) && preg_match('#^[0-9]{6}-[0-9]{6}-[0-9]{4}$#i', trim($_GET['cart_order_id']))) { */

  //Sequential Order Number tweak for Lookup Order

   if (isset($_GET['cart_order_id'])){

//end Sequential Order Number tweak for Lookup Order

I appreciate all your patient help, and I expect that your code would normally work. I may be able to get the mod developer to tweak this for me if the solution is not obvious to you.

[Dirty Butter]

No, that may be part of the problem for me, but there's something else wrong, as I found a test order with the default order id system, and the products information came through as blank in the email to the customer when the order was marked complete.

Getting too tired to work on this any more for now, so I'll revisit this when I can.

[bsmither]

The reason for this:

`cart_order_id` = 2012-PC-1988 AND `type` = 'prod' ;" - Unknown column 'PC' in 'where clause'

is because this part:

"`cart_order_id` = $order_id AND `type` = 'prod'"

needs to be:

"`cart_order_id` = ". $GLOBALS['db']->sqlSafe($order_id, true). " AND `type` = 'prod'"

This will 'quote' the cart_order_id code. Unquoted, MySQL will try to evaluate the code:

2012-PC-1988 = 24 minus the contents of table column PC

120911-101535-1234 = 18142

That one slipped past. I said I hadn't tried it.

[Dirty Butter]

WHOOPIE!! That did it!

for order.class.php not long after // Returns a list of products from the order

if (($products = $GLOBALS['db']->select('CubeCart_order_inventory LEFT JOIN CubeCart_seo_urls ON product_id = item_id', 'CubeCart_order_inventory.*,path', "`cart_order_id` = ". $GLOBALS['db']->sqlSafe($order_id, true). " AND `type` = 'prod'")) !== false) {

with this in the Order Complete email as the link to the review for each item they bought

<td>

    {foreach from=$PRODUCTS item=product}

    <p>

	 <a href="{$STORE_URL}/{$product.path}.html#customer-reviews ">{$product.name}</a></p>

    {/foreach}

    <p>

	 &nbsp;</p>

   </td>

The default CC skins don't have a tab for the reviews, so leaving off the #customer-reviews should work on a stock skin.

I finally have an Order Complete email to the customers that does everything I've wanted. It lists the items they bought in the subject, gives the ship date and link to the tracking/customs number for the order, has links so they can easily review each item, and offers a link to subscribe to our newsletter.

If anyone can suggest something else that should be in this last chance to connect with the buyer, please elaborate.

[bsmither]

1. Link to your RSS feed, (as much as I despise them, links to your...) Facebook account and Twitter feed.

2. Links to the Popular, Featured, On Sale, products

3. (Even though not yours) Links to interesting and relevant books about plush animals and collecting on Amazon

4. Links to other (not your competition) resources, like collectibles forums, parts suppliers, ancillaries (e.g., display case makers)

5. Links to National and International Product Safety Advisory sites

Marketing! Marketing! Marketing!

[Dirty Butter]

Some very good suggestions there. Thanks again!

I imagine this thread will end up being helpful to several here on the forum.