5 replies to this topic

[Brian-Bear]

Has anyone created a module in which a store admin can search for a customer by postcode or phone number.
I found this contribution by Hurl customer search on orders page would it be easy to add postcode and telephone number search to this.

Any help greatly appreciate. I think it could be a useful contribution.

Edited by Brian-Bear, 05 October 2010, 10:29.

[sublok]

Its a pretty easy fix... just open up:


/admin/customers.php

look for this sql query

$search = "where c.customers_lastname like '%" . $keywords . "%' or c.customers_firstname like '%" . $keywords . "%' or c.customers_email_address like '%" . $keywords . "%'";

and replace with this:

 $search = "where c.customers_lastname like '%" . $keywords . "%' or c.customers_firstname like '%" . $keywords . "%' or c.customers_email_address like '%" . $keywords . "%' or c.customers_telephone like '%" . $keywords . "%' or entry_postcode like '%" . $keywords . "%'";

thats is, now be able to search by phone or zip code

Cheers

Edited by sublok, 01 November 2010, 00:16.

[dkerfoot]

Thanks - this was very helpful!

I'd also like to be able to search comments. We use it for storing tracking numbers and product serial numbers and sometimes I need to hunt down a sale by one of these. Is this possible?

[midijay]

Hi

I was searching the forums to make another mod to customers.php, perhaps you could help. I've made a similar to change to the one above so that I can search by company name as most of our transactions are with companies. The let down of the the search results is that the tablet shows only the first/last name. I'd like to show the company name in the results.

I found the relevant parts of customers.php

              <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_LASTNAME; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_FIRSTNAME; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACCOUNT_CREATED; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACTION; ?>&nbsp;</td>
              </tr>

and modified to add a column heading as follows

              <tr class="dataTableHeadingRow">
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_LASTNAME; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_FIRSTNAME; ?></td>

                <td class="dataTableHeadingContent">Company Name</td>

                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACCOUNT_CREATED; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACTION; ?>&nbsp;</td>
              </tr>

and then about 40 rows further down my code, found the results

                <td class="dataTableContent"><?php echo $customers['customers_lastname']; ?></td>
                <td class="dataTableContent"><?php echo $customers['customers_firstname']; ?></td>
                <td class="dataTableContent" align="right"><?php echo tep_date_short($info['date_account_created']); ?></td>

so I added in this line in (naive) hope

                <td class="dataTableContent"><?php echo $address_book['entry_company']; ?></td>

but alas it does not work.

between the code for the table heading and tablet content there is a bunch of queries that (I assume) call the data to populate the tablet. So I recklessly added a.entry_company into the following query:

    $customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, a.entry_company, a.entry_country_id from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . $search . " order by c.customers_lastname, c.customers_firstname";

I have no idea if I am making the correct approach or not but my steps seem logical. Perhaps I've added to the wrong query or need different syntax to display the field? Any help appreciated, I'm assuming someone with just a passing familiarity with the coding conventions here will spot what needs to be done and help me along my way.

Thanks in advance!

[midijay]

I have answered my own question, just needed to change the code for displaying the field:

<td class="dataTableContent"><?php echo $customers['entry_company']; ?></td>

hey presto, life is easier

[kru]

If you want to limit your customer phone number or zip code search to people who have actually placed an order, another way to do it would be with a contrib I created:

http://addons.oscommerce.com/info/8281

It is geared towards an email and name search now but the fields you are after are in the orders table too, so you could just replace the customer_email_address and customer_name fields in the where clause in step 3 with customers_telephone and whichever one of the three postcode fields you want. Or you could add a couple more 'or' clauses in the 'where' clause and have it search on all four fields: email, name, phone and zip...

This contrib creates a new search box at the top of the Orders view in Admin. (Right above the Order ID search box.) One box, no matter how many different Orders fields you want to check for a given value. Just type the text string you want to search for and hit enter...