[MOD] Admin>Orders>Search : On product Name or SKU

[Martin]

This mod allows you to search your orders using:

• Product SKU (full or part of it)
• Product name (full or part of it)

It does add some extra load to the SQL calls so you will want to be careful about stressing the system too much with multiple searches but it does make searches for things like "who bought which product when" a possibility.


Version 5.0.6 - 5.5.x (tested)

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

Find:

Code: Select all

			// Return an array containing details about orders.
			$query = sprintf("

Before, Add:

Code: Select all

//MOD Search using SKU or product name 		
			$extraJoins .= " LEFT JOIN [|PREFIX|]order_products sp ON (o.orderid=sp.orderorderid)";
//MOD END Search using SKU or product name

Find:

Code: Select all

SELECT %so.*, c.*, s.statusdesc AS ordstatustext, CONCAT(custconfirstname, ' ', custconlastname) AS custname,

Replace with

Code: Select all

SELECT %s DISTINCT o.orderid, o.*, c.*, s.statusdesc AS ordstatustext, CONCAT(custconfirstname, ' ', custconlastname) AS custname,

Find:

Code: Select all

$countQuery = "SELECT COUNT(o.orderid) FROM [|PREFIX|]orders o";

Replace with

Code: Select all

$countQuery = "SELECT COUNT(DISTINCT o.orderid) FROM [|PREFIX|]orders o";

Find:

Code: Select all

					OR ordshipzip      LIKE '%".$search_query."%'
					OR ordshipcountry  LIKE '%".$search_query."%'
				) ";

Replace with:

Code: Select all

					OR ordshipzip      LIKE '%".$search_query."%'
					OR ordshipcountry  LIKE '%".$search_query."%'
				";
//MOD Search using SKU or product name 				
//				) ";
				
				$queryWhere .= "
					OR ordprodsku      LIKE '%".$search_query."%'
					OR ordprodname     LIKE '%".$search_query."%'
				) "; 
			
//MOD END Search using SKU or product name

[Tony Barnes]

Gold star for this one mate, one I'd meant to have a proper look into many times, but never got round to!!

[Martin]

Version 1.0.2 fixes multiple copies of the same order.

Note: Full modification has been updated to include this fix.

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

Find:

Code: Select all

SELECT %so.*, c.*, s.statusdesc AS ordstatustext, CONCAT(custconfirstname, ' ', custconlastname) AS custname,

Replace with

Code: Select all

SELECT %s DISTINCT o.orderid, o.*, c.*, s.statusdesc AS ordstatustext, CONCAT(custconfirstname, ' ', custconlastname) AS custname,

Find:

Code: Select all

$countQuery = "SELECT COUNT(o.orderid) FROM [|PREFIX|]orders o";

Replace with

Code: Select all

$countQuery = "SELECT COUNT(DISTINCT o.orderid) FROM [|PREFIX|]orders o";

[Tony Barnes]

Funnily I was just looking at orders from a single customer and it was pulling up lots of repeats, glad this was fixed so quickly!

[Martin]

Funnily I was just looking at orders from a single customer and it was pulling up lots of repeats, glad this was fixed so quickly!

Sorry... I was forgetting this was an Interspire product... I'll add some more bugs in now to help attenuate the shock...

[CharlieFoxtrot]

I'll add some more bugs in now to help attenuate the shock...

Then you can sit back, relax, and ignore everyone for 6 months.

[Tony Barnes]

lol, if you can get it so that it randomly doesn't work on occassion, just a weird error message pop up, that would be great

[CharlieFoxtrot]

lol, if you can get it so that it randomly doesn't work on occassion, just a weird error message pop up, that would be great

Must be a generic message. Something that gives no clue as to cause of the error. "Something went wrong."

(And make sure it's one of those errors that never shows up in the logs.)

[Tony Barnes]

lol!

[grantg]

Tried to apply this to 6.0.14 - the original code is the same, however, no extra functionality is added to the search when this hack is applied (ie. you cannot search for products ordered, only customers details as default). Shame
I tell a lie...the ordshipzip step is not the same, as the shipping calls have been removed from class.orders.php due to this single/multiple shipments malarky.

Now, I suspect, if I hunt down where these calls are made, I can edit that file...soon find out!