Search Customer not searching address book

[keat]

Search customer only appears to search the customer table and not the address book.

Is there anything that will search both.

[bsmither]

The code will have to be edited to include the addressbook in the search.

[keat]

Is this something I could do with guidance. ?

I did consider that it might be looking up database table in common.search.php, but there's nothing obvious jumping out at me.

[bsmither]

Please step away from the idea that templates can fetch data from the database.

Let's look at /admin/sources/customers.index.inc.php. Near line 412, there is the construction of a WHERE clause that looks for the search term in the email, first, or last name. We can add to the array of column names with columns in the CubeCart_addressbook table.

Then, we need to include the addressbook table in the queries at line 434 and 439.

On the other hand, the suggestive search results are obtained by a query at:
/admin.php?_g=xml&type=user&q=Smither&function=search
which is processed in /classes/ajax.class.php, public static function search().

Conveniently, there is a case to fetch all the addresses for an already specified customer_id. We would have to create a new case to do an actual search query for columns in the addressbook.

[keat]

As a starting point, I thought i'd be clever and modify the select query so it was searching Cubecart_addressbook, afterall this table still contains columns indicated in the array. So I never touched the array.

But now it finds nothing at all.

I think this might be too complicated for my uneducated head.

Also the customer data is split accross two tables, addressbook holds address info, while customer holds email and company name details.

[bsmither]

The addressbook table does not contain an email address, unless you added one, but does contain a company name. The only common columns are 'first_name' and 'last_name'.

Hoever, in my research, I think there is a bug in the way the query is being built that results in the suggestive search never being able to work.

More later.

[keat]

I'd guessed that the array

$where = isset($_GET['q']) && !empty($_GET['q']) ? array('~'.(string)trim($_GET['q']) => array('email', "CONCAT(first_name ,' ',last_name)", "CONCAT(last_name ,' ',first_name)")) : false;

contained first_name & last_name same as addressbook, so I might at least be able to search on a name, but alas no results.

 

 

[bsmither]

My research shows that the query constructed to fetch results for the suggestive search is incorrect and the Database Class, select() method returns a false because of that error.

Which is to say, no matter how inventive you get, the bug will always spoil the query - no matter the query.

More later.

[bsmither]

I must have done something to my Database Class --- the online demo store works fine. More later.

[keat]

I think this is the now only thing holding me up from conducting some real life dummy orders etc, before I hand this over.

I want to duplicate (manually) about 50 orders taken from our live V3 site, so I can capture all sorts of different scenarios, taxes, shipping, products, product options etc.

The best way for me to do this, would be to log in to my V6 as the actual customer whom I'm copying, and see that everything on the V6 works out the same as the V3.

However, finding the customers is proving to be a challenge.

If CC won't do this out of the box, then I'm open to suggestions for plugins.

[keat]

I assume at the moment, the only way I have to search a customer based on his address or postcode is via an SQL query on the database.

Searching by name or company name isn't always fruitful, especially if say the customer calls himself ABC Company, but inputs it as A.B.C Company.

[bsmither]

For the CC6 admin Search -- Suggestive Search, I've had to completely rewrite the query so that it includes fields from the addressbook. I expected it to be very easy, but it seems the programmers have painted themselves into a corner, and fixing the situation will probably have to wait for CC7.

More later.

[keat]

So am I correct in thinking that when searching a customer, we can only search on first name, last name and email address.

$where = isset($_GET['q']) && !empty($_GET['q']) ? array('~'.(string)trim($_GET['q']) => array('email', "CONCAT(first_name ,' ',last_name)", "CONCAT(last_name ,' ',first_name)")) : false;

And then only from the customer table. ( i did try changing to the addressbook table, but recieved no results)

 

As i've already proven, I know just about nothing with regards to PHP,  but is it not just a matter of writing a script that goes along the lines 'check this table, check that table, combine the two and output the results'

I know thats probably far far easier said than done, but surely a fundamental part of any cart would be customer management.

We can search many customer fields on V3 and this is a dinosaur compared to V6

[bsmither]

Just an analysis: CC3 had all of the customer data in one table, and that was properly 'normalized' (a relational database technical term).

However, with the introduction of the possibility of multiple contact addresses and delivery addresses per customer (but only one billing address), an addressbook was needed. To 'normalize' that, a relationship exists between the primary key of the customer table and a foreign key of the addressbook table.

JOINing the tables in a query is elementary SQL. However, many methods in CubeCart's database class is not coded to allow for anything but the most straightforward of queries.

Try this edit. Again, it is for the Suggestive Search (the popup list that appears after typing three letters of what you are searching for).

In /classes/ajax.class.php, near line 78, find:
if (($results = $GLOBALS['db']->select('CubeCart_customer', false, array('~'.$search_string => array('last_name', 'first_name', 'email')), false, false, false, false)) !== false) {

Change to:
if (($results = $GLOBALS['db']->misc(sprintf('SELECT C.* FROM `%1$sCubeCart_customer` AS C LEFT JOIN %1$sCubeCart_addressbook AS A  ON C.customer_id = A.customer_id WHERE C.`last_name` LIKE %2$s OR C.`first_name` LIKE %2$s OR C.`email` LIKE %2$s OR A.`company_name` LIKE %2$s  ;', $GLOBALS['config']->get('config','dbprefix'), $GLOBALS['db']->sqlSafe('%'.$search_string.'%',true)))) !== false) {

This part:
A.`company_name` LIKE %2$s
can have other columns OR'd to it, such as:
A.`company_name` LIKE %2$s OR A.`line1` LIKE %2$s OR A.`postcode` LIKE %2$s

I will next examine the complete search routine -- that is, the routine used when the 'Go' button is clicked.

[keat]

Monsieur Smither, this has made searching for customers so much easier already.

The suggestive search in its actions is (i find) untidy, but far superior to what was availalable to me 10 minutes ago.

The sheer fact that I can now search on a few extra variables, makes this a much easier experience.

However, I am strugglimg to understand how something so fundamental slipped through the net.

 

Yet again I am indeed indebted

 

 

[havenswift-hosting]

On 18 February 2016 at 6:04 PM, keat said:

I want to duplicate (manually) about 50 orders taken from our live V3 site, so I can capture all sorts of different scenarios, taxes, shipping, products, product options etc.

The best way for me to do this, would be to log in to my V6 as the actual customer whom I'm copying, and see that everything on the V6 works out the same as the V3.

If CC won't do this out of the box, then I'm open to suggestions for plugins.

Another way of doing this would be to use our Enhanced Admin Order Entry Plugin (see new thread under Extensions) which greatly enhances the Order Entry process for a store admin including the search. Currently available from our website but will shortly be going up on the Marketplace.

Be aware if you are entering orders in on behalf of customers (however you do it) then you should be careful about what email address you use or better still remove the ability for your store to send emails - you don't want old customers receiving emails about "new" orders

Ian