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

Modules, Add-ons and custom code that's more than just a quick hack or Mod.
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

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

Post by Martin »

Small bug that affected the coupon code related order results...

Assuming you've used the code fixes previously posted:

Find:

Code: Select all

			if(isset($_REQUEST['couponCode']) && trim($_REQUEST['couponCode']) != '') {
				$extraFields = 'DISTINCT(co.ordcouporderid), ';
				$extraJoins = sprintf("INNER JOIN [|PREFIX|]order_coupons co ON (co.ordcouporderid=o.orderid AND co.ordcouponcode='%s')", $GLOBALS['ISC_CLASS_DB']->Quote($_REQUEST['couponCode']));
			}

//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

			// Return an array containing details about orders.
			$query = sprintf("
				SELECT %s DISTINCT o.orderid, o.*, c.*, s.statusdesc AS ordstatustext, CONCAT(custconfirstname, ' ', custconlastname) AS custname,
.. Replace with:

Code: Select all

//MOD Search using SKU or product name
			if(isset($_REQUEST['couponCode']) && trim($_REQUEST['couponCode']) != '') {
				$extraFields = ' DISTINCT(co.ordcouporderid) ';
				$extraJoins = sprintf("INNER JOIN [|PREFIX|]order_coupons co ON (co.ordcouporderid=o.orderid AND co.ordcouponcode='%s')", $GLOBALS['ISC_CLASS_DB']->Quote($_REQUEST['couponCode']));
			}
			else {
				$extraFields = ' DISTINCT (o.orderid) ';
			}

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

			// Return an array containing details about orders.
			$query = sprintf("
				SELECT %s, o.*, c.*, s.statusdesc AS ordstatustext, CONCAT(custconfirstname, ' ', custconlastname) AS custname,
The reason for the problem before was that you cannot have two DISTINCT() calls in a single MySQL query...

The above code moves the o.orderid field to an else conditional and allows the use of DISTINCT just once
grantg
Posts: 112
Joined: Thu Oct 08, 2009 11:01 am

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

Post by grantg »

Hey Folks

Just wondering if any of you brave folks fancy tackling this mod to work with ISC V6.X?

Thank you please ;)
Cupar Garden Centre: CS-Cart Professional 2.2.5 (Converted from ISC 6.0.14 Ultimate)
Online Garden Centre: ISC 6.0.14 Professional
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

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

Post by Martin »

Version 6.x (tested on 6.1.1)
Pretty sure this version is the same as the 5.x (once all mods resolved including fix above)... Is working fine on my 6.1.1 system

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

Find:

Code: Select all

			if(isset($_REQUEST['couponCode']) && trim($_REQUEST['couponCode']) != '') {
				$extraFields = 'DISTINCT(co.ordcouporderid), ';
				$extraJoins = sprintf("INNER JOIN [|PREFIX|]order_coupons co ON (co.ordcouporderid=o.orderid AND co.ordcouponcode='%s')", $GLOBALS['ISC_CLASS_DB']->Quote($_REQUEST['couponCode']));
			}
Replace with:

Code: Select all

//MOD Search using SKU or product name

			if(isset($_REQUEST['couponCode']) && trim($_REQUEST['couponCode']) != '') {
				$extraFields = ' DISTINCT(co.ordcouporderid) ';
				$extraJoins = sprintf("INNER JOIN [|PREFIX|]order_coupons co ON (co.ordcouporderid=o.orderid AND co.ordcouponcode='%s')", $GLOBALS['ISC_CLASS_DB']->Quote($_REQUEST['couponCode']));
			}
			else {
				$extraFields = ' DISTINCT (o.orderid) ';
			}

 	
			$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 ordbillzip      LIKE '%".$search_query."%'
					OR ordbillcountry  LIKE '%".$search_query."%'
				) ";
Replace with:

Code: Select all

					OR ordbillzip      LIKE '%".$search_query."%'
					OR ordbillcountry  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
grantg
Posts: 112
Joined: Thu Oct 08, 2009 11:01 am

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

Post by grantg »

Hi Martin

Many thanks for your assistance - I am on ISC 6.1.1, however, after applying this fix (I used everything from your last post, assuming this includes all previous fixes?), but I receive a blank Orders page - it does say Page X of XYZ so the system is obviously picking up that there are XYZ number of orders - the search yields no results - perhaps just a slight error, a missing bracket or semi-colon?

Thanks :)
Cupar Garden Centre: CS-Cart Professional 2.2.5 (Converted from ISC 6.0.14 Ultimate)
Online Garden Centre: ISC 6.0.14 Professional
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

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

Post by Martin »

grantg wrote:Many thanks for your assistance - I am on ISC 6.1.1, however, after applying this fix (I used everything from your last post, assuming this includes all previous fixes?), but I receive a blank Orders page - it does say Page X of XYZ so the system is obviously picking up that there are XYZ number of orders - the search yields no results - perhaps just a slight error, a missing bracket or semi-colon?
The system log should give us some clues as to where the problem lies...

If you could check that, copy the relevant lines (and details) and attach the affected file (most like class.orders.php) as a zip I can see what may have gone awry...
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

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

Post by Martin »

grantg wrote:Many thanks for your assistance - I am on ISC 6.1.1, however, after applying this fix (I used everything from your last post, assuming this includes all previous fixes?), but I receive a blank Orders page - it does say Page X of XYZ so the system is obviously picking up that there are XYZ number of orders - the search yields no results - perhaps just a slight error, a missing bracket or semi-colon?
The system log should give us some clues as to where the problem lies...

If you could check that, copy the relevant lines (and details) and attach the affected file (most like class.orders.php) as a zip I can see what may have gone awry...
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

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

Post by Martin »

See the attached for my own pre-modified version of the /admin/includes/classes/class.orders.php file
Attachments
class.orders.php.zip
6.1.1 - pre-modified
(20.63 KiB) Downloaded 610 times
grantg
Posts: 112
Joined: Thu Oct 08, 2009 11:01 am

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

Post by grantg »

Sorry it took so long to get a reply up here, not been on any Interspire foru for a while cos the situation is so bleak...

Thanks for your last post/attachment, that got things working great, must of been something I was doing wrong.

:D
Cupar Garden Centre: CS-Cart Professional 2.2.5 (Converted from ISC 6.0.14 Ultimate)
Online Garden Centre: ISC 6.0.14 Professional
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

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

Post by Martin »

Glad that sorted it :)
Martin
Site Admin
Site Admin
Posts: 1854
Joined: Wed Jun 17, 2009 6:30 pm
Location: South Yorkshire UK
Contact:

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

Post by Martin »

Found a bug in the code and in my fix..

Turns out that you can't search properly using a custom date range for a few reasons..

1. This code is incorrect:

Code: Select all

					if(isset($to_stamp)) {
						$queryWhere .= " AND (orddate <= '".(int)$to_stamp."' OR (
							SELECT opt.orderprodid
							FROM [|PREFIX|]order_products opt
							WHERE o.orderid=opt.orderorderid AND opt.ordprodeventdate <='".(int)$to_stamp."'
						))";
					}
					if(isset($to_stamp)) {
						$queryWhere .= " AND orddate <='".(int)$from_stamp."'";
					}
... should be...

Code: Select all

					if(isset($to_stamp)) {
						$queryWhere .= " AND (orddate <= '".(int)$to_stamp."' OR (
							SELECT opt.orderprodid
							FROM [|PREFIX|]order_products opt
							WHERE o.orderid=opt.orderorderid AND opt.ordprodeventdate <='".(int)$to_stamp."'
						))";
					}
					if(isset($to_stamp)) {
						$queryWhere .= " AND orddate <='".(int)$to_stamp."'";
					}

Also, the code I added in causes a few issues so you need to amend things further like so:

Find:

Code: Select all

						$queryWhere .= " AND (orddate >= '".(int)$from_stamp."' OR (
							SELECT opf.orderprodid
							FROM [|PREFIX|]order_products opf
							WHERE o.orderid=opf.orderorderid AND opf.ordprodeventdate >='".(int)$from_stamp."'
						))";
Replace with:

Code: Select all

/*						
						$queryWhere .= " AND (orddate >= '".(int)$from_stamp."' OR (
							SELECT opf.orderprodid
							FROM [|PREFIX|]order_products opf
							WHERE o.orderid=opf.orderorderid AND opf.ordprodeventdate >='".(int)$from_stamp."'
						))";
*/						
						$queryWhere .= " AND (orddate >= '".(int)$from_stamp."' OR sp.ordprodeventdate >='".(int)$from_stamp."')";
Find:

Code: Select all

						$queryWhere .= " AND (orddate <= '".(int)$to_stamp."' OR (
							SELECT opt.orderprodid
							FROM [|PREFIX|]order_products opt
							WHERE o.orderid=opt.orderorderid AND opt.ordprodeventdate <='".(int)$to_stamp."'
						))";
Replace with:

Code: Select all

/*						
						$queryWhere .= " AND (orddate <= '".(int)$to_stamp."' OR (
							SELECT opt.orderprodid
							FROM [|PREFIX|]order_products opt
							WHERE o.orderid=opt.orderorderid AND opt.ordprodeventdate <='".(int)$to_stamp."'
						))";
*/
						$queryWhere .= " AND (orddate <= '".(int)$to_stamp."' OR sp.ordprodeventdate <='".(int)$to_stamp."')";



There's probably some other bits that break it but this should help with the one issue above.
Post Reply