All Customers Report

All Customers Report v1.0 - March 22, 2005

written by Jared Call at client's suggestion
some code nicked and modified from /catalog/admin/customers.php
Released under the GNU General Public License


What this contrib does:
=========================

This contribution simply generates a list of all customers and their mailing addresses, alphabetized by last name (surname). Depending on several things, it may take several seconds to generate. The resulting data can be easily copied/pasted into spreadsheets for mailing list labels, importing into other customer management software, etc.

Disclaimer: This contrib has been designed for and tested with osCommerce 2.2 MS2. While it should easily work, perhaps with minor modifications, with other versions of osCommerce, it has not been tested as such.

If you find this contribution useful, please support the osCommerce project by becoming an osCommerce Community Sponsor. At the time of this writing, details of Community Sponsorship can be found at http://www.oscommerce.com/about/news,111 .

All Customers Report V 2_1d

02/22/2009 by Jack_mcs

Fixed code that displays the page heading so it displays the correct one.
Removed non-standard code from mysql call.
Added newsletter option along with the code to filter by it.
Added code to enter a start/stop date.
Changed code so the report is not ran until submit is clicked. Automatically running it creates problems with sites with a large number of customers.
Made minor changes to the code for formatting and displaying an error message.

This is a full package.

The support thread is at:
http://forums.oscommerce.com/index.php?showtopic=142447

SQL Query for MySQL 5

07/17/2007 by alexjudd

We had some problems with the original JOINs in the query in MySQL 5 so below is an updated SQL statement to replace the one around line 117 which does the same, but works on all MySQL versions we tested.

Regards

$customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_dob, c.customers_telephone, c.customers_gender, c.hear, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name 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 JOIN " . TABLE_COUNTRIES . " co ON a.entry_country_id = co.countries_id LEFT JOIN " . TABLE_ZONES . " z ON a.entry_zone_id = z.zone_id ORDER BY $db_orderby $sorted";

Version 2.1c

03/11/2007 by fleetsoft

Fixed issue with apostrophes terminating CSV output.
See http://forums.oscommerce.com/index.php?s=&showtopic=142447&view=findpost&p=1040510

2.1b - fixes to ver 2.1a

03/04/2007 by fleetsoft

Please see:
http://forums.oscommerce.com/index.php?s=&showtopic=142447&view=findpost&p=1036256
for details

More Bug Fixes

06/10/2006 by graphinex

I installed 2.1a and found the following problems:

1. I had to delete extra blanks from languages/english.php at EOF, they were causing header errors

2. I had to change function mirror out from admin/includes/functions/general.php to all_customers_mirror_out and rename the functions inside of all_customers.php

3. I am getting error:

Access Denied

No Right Permission Access
Please contact your Web Administrator to request
more access or if you found any problem.

and being forwarded to forbiden.php and when I figure out why I will post it.

SQL query

01/15/2006 by jocg

I have had several problems to get the Query in file admin/all_customers.php working fine, I tried and tried to edit it keeping the JOIN but nothing to do.
What work it out is this alternative query that replace the line number 117:

$customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM " . TABLE_CUSTOMERS . " c JOIN " . TABLE_ZONES . " z ON a.entry_zone_id = z.zone_id JOIN " . TABLE_COUNTRIES . " co ON a.entry_country_id = co.countries_id LEFT JOIN " . TABLE_ADDRESS_BOOK . " a ON c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id ORDER BY $db_orderby $sorted";

WITH:

$customers_query_raw = "SELECT c.customers_id , c.customers_default_address_id, c.customers_email_address, c.customers_fax, c.customers_telephone, a.entry_company, a.address_book_id, a.customers_id, a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb, a.entry_city, a.entry_state, a.entry_postcode, a.entry_country_id, a.entry_zone_id, z.zone_code, co.countries_name FROM " . TABLE_CUSTOMERS . " c, ". TABLE_ZONES ." z, " . TABLE_ADDRESS_BOOK . " a, " . TABLE_COUNTRIES . " co WHERE a.entry_zone_id = z.zone_id AND a.entry_country_id = co.countries_id AND c.customers_id = a.customers_id AND c.customers_default_address_id = a.address_book_id ORDER BY $db_orderby $sorted";

For the full packing see below !

2.1a

12/30/2005 by jcall

added french file from smalto
added GPL

No other changes

Full Package

File for the french version

09/07/2005 by smalto

Désolé, dans la contribution précédente le ficher français est manquant.

Just the file in french sind I didn't find it in the previous pack.
-----------------------------------
Voici le fichier en langue francaise.
This is the french traduction.

A placer dans admin/includes/french/
To put in admin/includes/french/

French version

07/08/2005 by n.mathon

Voici le fichier en langue francaise.
This is the french traduction.

A placer dans admin/includes/french/

ACR V2.0

06/24/2005 by Robert Goth

This is my first contribution so please be kind.
-Improved speed of query
-Added sort by category
-Allows user to export Customer list in CSV format.

I just wanted to thank Jared for putting his work. It made it a lot faster to produce this contribution by using your initial work

v1.1

04/26/2005 by Jared Call

changelog:
===========
v1.1: added customization instructions (Step 5)

All Customers Report

03/22/2005 by Jared Call

Forgot to include the support thread in the readme.

Support thread is http://forums.oscommerce.com/index.php?showtopic=142447

All Customers Report

03/22/2005 by Jared Call

All Customers Report v1.0 - March 22, 2005

written by Jared Call at client's suggestion
some code nicked and modified from /catalog/admin/customers.php
Released under the GNU General Public License


What this contrib does:
=========================

This contribution simply generates a list of all customers and their mailing addresses, alphabetized by last name (surname). Depending on several things, it may take several seconds to generate. The resulting data can be easily copied/pasted into spreadsheets for mailing list labels, importing into other customer management software, etc.

Disclaimer: This contrib has been designed for and tested with osCommerce 2.2 MS2. While it should easily work, perhaps with minor modifications, with other versions of osCommerce, it has not been tested as such.

If you find this contribution useful, please support the osCommerce project by becoming an osCommerce Community Sponsor. At the time of this writing, details of Community Sponsorship can be found at http://www.oscommerce.com/about/news,111 .