[SOLVED] Orders Model, include product SKUs in orders

[EvanGR]

[SOLVED this... info on my followup reply]

Hello,

I am trying to print the product SKUs for each order, in the Orders page.

I have tweaked the Orders model (\administrator\components\com_virtuemart\models\orders.php)

... EDIT: See followup reply on how I solved this issue.

[EvanGR]

Ok, I solved this...

EDIT: Updated solution fixing some bugs...
These are the changes I did in Orders model file.
\administrator\components\com_virtuemart\models\orders.php

#1 Add a new method to get order items for an order (it's a clone of getOrder(), but getting only the order items, for better performance)

Code Select Expand

public function getOrderItems($virtuemart_order_id){

//sanitize id
$virtuemart_order_id = (int)$virtuemart_order_id;
$db = JFactory::getDBO();
$order = array();

// Get the order items
$q = 'SELECT virtuemart_order_item_id, product_quantity, order_item_name, order_item_sku, i.virtuemart_product_id, product_item_price, product_final_price, product_basePriceWithTax, product_discountedPriceWithoutTax, product_priceWithoutTax, product_subtotal_with_tax, product_subtotal_discount, product_tax, product_attribute, order_status, paid,
intnotes, virtuemart_category_id
FROM #__virtuemart_order_items i
LEFT JOIN #__virtuemart_products p
ON p.virtuemart_product_id = i.virtuemart_product_id
LEFT JOIN #__virtuemart_product_categories c
ON p.virtuemart_product_id = c.virtuemart_product_id
WHERE `virtuemart_order_id`="'.$virtuemart_order_id.'" group by `virtuemart_order_item_id`';

$orderBy = VmConfig::get('order_item_ordering','virtuemart_order_item_id');
        if (!empty ( $orderBy)) {
        $orderingDir = VmConfig::get('order_item_ordering_dir','ASC');
            $q .= ' ORDER BY `'.$orderBy.'` ' . $orderingDir;
        }
//group by `virtuemart_order_id`'; Why ever we added this, it makes trouble, only one order item is shown then.
// without group by we get the product 3 times, when it is in 3 categories and similar, so we need a group by
//lets try group by `virtuemart_order_item_id`
$db->setQuery($q);
$order['items'] = $db->loadObjectList();

$customfieldModel = VmModel::getModel('customfields');
$pModel = VmModel::getModel('product');
foreach($order['items'] as $p=>$item){

$ids = array();

$product = $pModel->getProduct($item->virtuemart_product_id, true, false, false);
if($product){
$pvar = get_object_vars($product);

foreach ( $pvar as $k => $v) {
if (!isset($item->{$k}) and strpos ($k, '_') !== 0 and property_exists($product, $k)) {
$item->{$k} = $v;
}
}
}

$item->paid = floatval($item->paid);
$order['items'][$p] = $item;
}

return $order;
}


#2 Update getOrdersListQuery() to always LEFT JOIN the virtuemart_order_items table

Code Select Expand


private function getOrdersListQuery($byOrderItem = false) {
$q = ' FROM #__virtuemart_orders as o
LEFT JOIN #__virtuemart_order_userinfos as u
ON u.virtuemart_order_id = o.virtuemart_order_id AND u.address_type="BT"
LEFT JOIN #__virtuemart_order_userinfos as st
ON st.virtuemart_order_id = o.virtuemart_order_id AND st.address_type="ST" ';
$q .= ' LEFT JOIN #__virtuemart_order_items as oi
ON o.virtuemart_order_id = oi.virtuemart_order_id '; // amivag: we need this to be able to search on product item SKUs

if($byOrderItem){
// $q .= ' LEFT JOIN #__virtuemart_order_items as oi
// ON o.virtuemart_order_id = oi.virtuemart_order_id ';
}
$q .= ' LEFT JOIN #__virtuemart_paymentmethods_'.VmConfig::$vmlang.' as pm
ON o.virtuemart_paymentmethod_id = pm.virtuemart_paymentmethod_id
LEFT JOIN #__virtuemart_shipmentmethods_'.VmConfig::$vmlang.' as sm
ON o.virtuemart_shipmentmethod_id = sm.virtuemart_shipmentmethod_id';
return $q;
}


#3 Update getOrdersList() to include product items in orders (and also search for product SKUs in Orders search)

Code Select Expand


if ($this->search){
$db = JFactory::getDBO();
$this->search = '"%' . $db->escape( $this->search, true ) . '%"' ;
$this->search = str_replace(' ','%',$this->search);

$searchFields = array();
$searchFields[] = 'u.first_name';
//$searchFields[] = 'u.middle_name';
$searchFields[] = 'u.last_name';
$searchFields[] = 'o.order_number';
$searchFields[] = 'u.company';
$searchFields[] = 'u.email';
$searchFields[] = 'u.phone_1';
$searchFields[] = 'u.address_1';
$searchFields[] = 'u.city';
$searchFields[] = 'u.zip';
//quorvia added  ST data searches and virtuemart_order_id and order total
$searchFields[] = 'o.virtuemart_order_id';
$searchFields[] = 'round(o.order_total,2)';
$searchFields[] = 'u.customer_note';
$searchFields[] = 'o.order_note';
$searchFields[] = 'st.last_name';
$searchFields[] = 'st.company';
$searchFields[] = 'st.city';
$searchFields[] = 'st.zip';
$searchFields[] = 'oi.order_item_sku'; // amivag: also search in product SKUs
$where[] = implode (' LIKE '.$this->search.' OR ', $searchFields) . ' LIKE '.$this->search.' ';
//$where[] = ' ( u.first_name LIKE '.$search.' OR u.middle_name LIKE '.$search.' OR u.last_name LIKE '.$search.' OR `order_number` LIKE '.$search.')';



...

Code Select Expand


// AMIVAG: Make orders unique
$groupBy = " GROUP BY o.virtuemart_order_id "; // AMIVAG: Remove duplicate orders...
$ordersMayContainDuplicates = $this->exeSortSearchListQuery(0,$select,$from,$whereString,$groupBy,$ordering);
$this->_data = $ordersMayContainDuplicates; // GROUP BY takes care of duplicates


if($this->_data){
foreach($this->_data as $k=>$d){
$this->_data[$k]->order_name = htmlspecialchars(strip_tags(htmlspecialchars_decode($d->order_name)));
// AMIVAG: Get details for each order, including product items
$orderDetails = $this->getOrderItems($this->_data[$k]->virtuemart_order_id);
$orderItems = $orderDetails["items"];
//AMIVAG: Include products in each Order
$this->_data[$k]->product_skus = [];
$this->_data[$k]->product_names = [];
foreach($orderItems as $item) {
$this->_data[$k]->product_skus[] = $item->order_item_sku;
$this->_data[$k]->product_names[] = $item->order_item_name;
}
}
}


[AH]

Why are you doing this?

[EvanGR]

To include the product SKUs for each order, in the Orders list.

It makes it easy for our employees to quickly find the products attached to an order, and even with a browser search (Ctrl+F) to find the orders on the page, by searching a product SKU.

I also went ahead and tweaked the Order search field above, to also search for product SKUs. Now we can enter a product SKU and find out which Orders it has been included in.

It will save a TON of time to match incoming products (from manufacturers), to the customers that placed the order, and ship them.

[pinochico]

hmmm, our customers have in one order more then 50 products == that is one order on the one page in admin

[AH]

Pinochio- agreed

This would seriously slow down many stores!!!

[EvanGR]

I don't see any noticeable slowdown in my case.
Our orders usually have 1-4 products on average.

I understand that using getOrder() brings in a lot more data than I need, but it was the quick+dirty way.

This hack will really boost productivity of our employees.

EDIT: Perhaps I could clone getOrder() method, to getOrderItems(), and only retrieve the product SKUs. This should speed it up.

[AH]

EvanGR

1-4 products is a very small value per order

I am not saying that it will be a problem in your case - just that this is not a valid core solution

In a simple scenario

Displaying 100 orders
with average of 20 order lines per order

- 2000 lines to scroll through to set order status - or view other relevant order list information.

Again - I am not saying you are wrong in what you are doing as it works for you and your particular business process

I would suggest that might have created a plugin for such a scenario, this would not get overwritten by core updates

[EvanGR]

Yes, I am only posting this here for anyone that may be interested. As well as for me for future reference.

I am not suggesting core changes!

By the way, the UI issues are very simple to solve. If there are many SKUs per order, with just a few lines of CSS/Javascript, you can place product SKUs in a hidden container on each table, and toggle the view with a button.
(I have already tweaked the admin UI to make it more compact and with more info per page, compared to the default Isis template.)


I would love to be able to do this core hack as a plugin instead. I have only modified orders.php (model).
Would it take a lot more effort to do it via a plugin?
Where should I start reading about how to implement it?

Thanks!

[pinochico]

you can place product SKUs in a hidden container on each table, and toggle the view with a button

it good idea

Better will be plugin, which use some trigger from admin view for order list and add your view to existing or override.
But I don't know which or if exist or if is the right way (only my idea)


I'm also curious

[AH]

Creating a new plugin to get the data for each orderline gives you a degree of flexibility in what you get and how that is displayed.

The plugin would certainly be a lot more effort.

But then you would have a registered plugin in which you write your own events - which could then be called from many places :-)

You then just register your plugin in the override where you want to use it and the trigger events are ready to be called

I wanted a lot of control over my plugin so did not want it to sit as one of the core VM plugins

So i created a new plugin that sat in its own plugin "group"
Group
plugins/mynewgroup

Plugin in the new group
pluginplugins/mynewgroup/mynewplugin/

Plugin code etc.
pluginplugins/mynewgroup/mynewplugin/mynewplugin.php
pluginplugins/mynewgroup/mynewplugin/mynewplugin.xml


Do some stuff at the beginning of the plugin to register some vm stuff  and then set the class

Code Select Expand

class plgMynewPluginMynewPlugin extends vmPlugin {


Create your events along with whatever functions you require.

e.g.

Code Select Expand

public function plgOnDisplaySomeExtraOrderDetails($virtuemart_order_id) {


Register your plugin in any views you want the trigger events to be available.

Code Select Expand

$myplugactive = JPluginHelper::importPlugin('mynewgroup', 'mynewplugin');
$dispatcher = JEventDispatcher::getInstance();



Then trigger the event where you want some form of result:


<td>
   <?php
      if(isset($myplugactive)){
         $dispatcher->trigger('plgOnDisplaySomeExtraOrderDetails', $order->virtuemart_order_id );
      }
   ?>
</td>



Of course this will trigger the event once for ever order in an order list view which may be very heavy in terms of processing, but I have something very similar working with no adverse effects when displaying 100 orders with multiline detail.

Plus you now have your own plugin in which you can create many functions that may be of use to you in many VM views

In the images below you can see an adjusted order list with up to 2 extra links per order created by a new plugin and new plugintrigger

The first display some order data in a new screen via a new link
And the second displays a list of items from the order in a popup (image 2)

[pinochico]

to AH:

thanks for explanation.

The basic result is - need own plugin witn own trigger and call this trigger as dispatcher for own plugin in right place in CUSTOM OVERRIDE!!! view in my template for admin order list, isn't?
Yes, this idea a had in my head, but not sure

[EvanGR]

Super awesome!
AH, thanks for this amazing explanation!

[AH]

I realised I had really missed something very basic in your requirement  and had missed a simple "option"

I do also understand that this does not fully meet your requirement but wanted to show you as this might make it to core with a few extra tweaks in the way the search is originally requested

I also went ahead and tweaked the Order search field above, to also search for product SKUs. Now we can enter a product SKU and find out which Orders it has been included in.

This is the lowest impact option with no new SQL code - as it uses existing search capability in the order model:

In models orders:-

public function getOrdersList($uid = 0, $noLimit = false) {

Change the search query
A couple of tweaks will get you there:

Set the byorderitem to true


//      $from = $this->getOrdersListQuery($byOrderItem);
      $from = $this->getOrdersListQuery(true);


And include the order_item_sku in the searched fields


      if ($this->search){
         $db = JFactory::getDBO();
         $this->search = '"%' . $db->escape( $this->search, true ) . '%"' ;
         $this->search = str_replace(' ','%',$this->search);

         $searchFields = array();
         $searchFields[] = 'oi.order_item_sku';
         $searchFields[] = 'u.first_name';
         //$searchFields[] = 'u.middle_name';

[sirius]

Hi all,

thanks AH for for this really practical tip