Search for Products Ordered

[grantg]

Hello all.

Here's the situation:

Two stores, one on 5.5.2 and one on 5.5.4.

After 5.0.6, the functionality to search for order number, customer name and products ordered was removed in favour of search functionality for order number and customer name.

I would like to be able to search for the products ordered, so that when a large back-order comes into stock, I can then search for "Product ABC" and find all the customers with completed and pending orders.

Is this possible, and if so, how? Alternatively, if this is a major hack, please drop me a line if you have some time for hiring.

Thanks
Grant

[Tony Barnes]

If you're wanting to just do this as you go along a "dirty" way to do it is grab the product id of the product (mouse over the edit and check the value), then just plug this into your address bar:

YOUR_SITE. com/admin/index.php?ToDo=customOrderSearch&productId=XXX

replacing XXX with the product id. You will get a couple of PHP errors thrown up because strictly speaking it should be using a search id for custom stuff like this, but it saves bothering to have to set them up for each and every product.

However, if you do have common ones that you will use regularly, set them up as a custom view, and then dump the &productId=XXX bit into your SQL database at the end of the relevant searchvars column in isc_custom_searches

[grantg]

Hey Tony

I have tried this out and it seems to do the job, but...

Where you have hundreds of product ranges and several computer-illiterate staff, this won't be easy.

I am pretty sure in 5.0.6 you could search for Customer Name, Address or Products - now this appears to be limited to Customer Name and Address only.

I was expecting some php/SQL to be in place where the search is done on one of the database table fields, where I could add a clause to the statement to have the search function search for the products too.

Is this possible? Can anyone modify the search feature to do this for a beer or six?

[Tony Barnes]

Ok, hunting back to compare admin/includes/classes/class.order.php version 4.0.3 with 5.5.4, I've found this line goes away:

OR op.ordprodname LIKE '%".$search_query."%'

at about line 990.

From the context I think it is adding ordprodname from op (isc_orders_products) to the search. I've tried just adding it back in, but it fails. Maybe it needs explaining to look at that table when referring to it that way, don't know, but it's a direction for you to hunt in

[noticz]

I know this is an old post, just added this to ISC so figured I would share.


in: admin/includes/classes/class.orders.php

find (around line 1100 or so):

Code: Select all

 				$countQuery .= " LEFT JOIN [|PREFIX|]customers c ON (o.ordcustid=c.customerid)";

And add this under the line:

Code: Select all

				// mod - for searching inside product names
				$queryWhere .= " OR (
					SELECT sp.orderprodid
					FROM [|PREFIX|]order_products sp
					WHERE sp.ordprodname LIKE '%".$search_query."%' AND sp.orderorderid=o.orderid
					LIMIT 1
				)";