Author Topic: Ordering products in orders by SKU in backend  (Read 424 times)

mhilliard

  • Beginner
  • *
  • Posts: 7
Ordering products in orders by SKU in backend
« on: October 05, 2017, 18:39:31 pm »
We've been struggling to override the default listing of products in each order so they listed by SKU and not the sequence they were added.

I think it has something to with this file (models/orders.php) but could be wrong. Our client pulls all their inventory by SKU number in order and it would make processing/shipping a lot easier for them. Does anyone have insight to what line of code needs to change or is aware of a component that will make the columns sortable by heading on the backend?

We're working on VM 3.2.4 and Joomla 3.8.0

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2640
  • VirtueMart Version: 3.2.8
Re: Ordering products in orders by SKU in backend
« Reply #1 on: October 05, 2017, 19:43:04 pm »
You could adjust the model to do this:-

in the order.php


<<modified to remove bold tags in code snippet>>
Code: [Select]
// 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,
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`
ORDER BY order_item_sku ASC';



Tested on vm 3.2.4
However you will need to reapply this every time you update VM

It does not allow selection of columns though.


regards
A

Joomla 3.8.3
php 5.6 + php 7

mhilliard

  • Beginner
  • *
  • Posts: 7
Re: Ordering products in orders by SKU in backend
« Reply #2 on: October 05, 2017, 23:42:16 pm »
That works! However the last line ( ORDER BY order_item_sku ASC'; ) threw an error with the ... tags. Pulling those out had everything ordered by SKU on the backend. Thanks.

Here's the code that did the job:


Code: [Select]
// 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,
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`

ORDER BY order_item_sku ASC';


AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2640
  • VirtueMart Version: 3.2.8
Re: Ordering products in orders by SKU in backend
« Reply #3 on: October 06, 2017, 09:29:11 am »
Drat - the tags were designed to highlight the modification you should make
regards
A

Joomla 3.8.3
php 5.6 + php 7

mhilliard

  • Beginner
  • *
  • Posts: 7
Re: Ordering products in orders by SKU in backend
« Reply #4 on: November 02, 2017, 19:37:24 pm »
Well, this is weird. It worked properly a month ago, but we've since migrated the site and reinstalled VM (currently running vm3.2.4 and Joomla 3.8.1) during the process, and now this patch won't work.

It's possible we applied the patch to a vm3.2.2 version of the site earlier... can you think of any reason that would change in vm3.2.4 for administrator/components/com_virtuemart/models/orders.php?

AH

  • Global Moderator
  • Sr. Member
  • *
  • Posts: 2640
  • VirtueMart Version: 3.2.8
Re: Ordering products in orders by SKU in backend
« Reply #5 on: November 04, 2017, 20:11:46 pm »
The patch should still work but you could try adding the `s

Code: [Select]

// 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,
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`
ORDER BY `order_item_sku` ASC';

regards
A

Joomla 3.8.3
php 5.6 + php 7