Author Topic: [SOLVED] Orders Model, include product SKUs in orders  (Read 1087 times)

EvanGR

  • Jr. Member
  • **
  • Posts: 429
[SOLVED] Orders Model, include product SKUs in orders
« on: March 16, 2021, 12:48:22 pm »
[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

  • Jr. Member
  • **
  • Posts: 429
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #1 on: March 16, 2021, 13:44:16 pm »
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]
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]
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]
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]
// 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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3450
  • VirtueMart Version: 3.8.9
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #2 on: March 16, 2021, 14:39:18 pm »
Why are you doing this?

Regards
A

Joomla 3.9.26
php 7.4

EvanGR

  • Jr. Member
  • **
  • Posts: 429
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #3 on: March 16, 2021, 15:01:34 pm »
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

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1126
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #4 on: March 16, 2021, 15:54:42 pm »
hmmm, our customers have in one order more then 50 products == that is one order on the one page in admin :D
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3450
  • VirtueMart Version: 3.8.9
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #5 on: March 16, 2021, 17:21:00 pm »
Pinochio- agreed

This would seriously slow down many stores!!!
Regards
A

Joomla 3.9.26
php 7.4

EvanGR

  • Jr. Member
  • **
  • Posts: 429
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #6 on: March 17, 2021, 08:34:43 am »
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

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3450
  • VirtueMart Version: 3.8.9
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #7 on: March 17, 2021, 09:49:49 am »
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
Regards
A

Joomla 3.9.26
php 7.4

EvanGR

  • Jr. Member
  • **
  • Posts: 429
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #8 on: March 17, 2021, 10:26:36 am »
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

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1126
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #9 on: March 17, 2021, 11:34:31 am »
Quote
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
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3450
  • VirtueMart Version: 3.8.9
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #10 on: March 17, 2021, 12:02:55 pm »
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]
class plgMynewPluginMynewPlugin extends vmPlugin {

Create your events along with whatever functions you require.

e.g.

Code: [Select]
public function plgOnDisplaySomeExtraOrderDetails($virtuemart_order_id) {

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

Code: [Select]
$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)
Regards
A

Joomla 3.9.26
php 7.4

pinochico

  • 3rd party VirtueMart Developer
  • Full Member
  • *
  • Posts: 1126
    • MiniJoomla
  • Skype Name: support-easysoftware
  • VirtueMart Version: 3
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #11 on: March 17, 2021, 13:09:54 pm »
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 :)
www.minijoomla.org  - new portal for Joomla!, Virtuemart and other extensions
XML Easy Feeder - feeds from products, orders and database table
Virtuemart Email Manager - customs email templates

EvanGR

  • Jr. Member
  • **
  • Posts: 429
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #12 on: March 17, 2021, 14:38:24 pm »
Super awesome!
AH, thanks for this amazing explanation!

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 3450
  • VirtueMart Version: 3.8.9
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #13 on: March 18, 2021, 10:59:33 am »
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


Quote
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';

Regards
A

Joomla 3.9.26
php 7.4

sirius

  • Jr. Member
  • **
  • Posts: 104
    • OCTeam
  • VirtueMart Version: 3.8.6
Re: [SOLVED] Orders Model, include product SKUs in orders
« Reply #14 on: March 19, 2021, 16:04:17 pm »
Hi all,

thanks AH for for this really practical tip

J3.9.26 | PHP7.4.18 + APC + Opcode
VM Prod : 3.8.6 | VM Test : 3.8.9