Jump to content
jcall

All Customers Report

Recommended Posts

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.

 

Downloadable from http://www.oscommerce.com/community/contributions,3045 .

 

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 .

Edited by jcall

Share this post


Link to post
Share on other sites

2 questions before I install... does it have the capability of exporting as a .csv file? Also, does it show the customer's email address too?

 

-George

Share this post


Link to post
Share on other sites

2 questions before I install... does it have the capability of exporting as a .csv file? Also, does it show the customer's email address too?

 

-George

Share this post


Link to post
Share on other sites

No, I didn't build in the capability of exporting to CSV, but you really don't need it. You can simply copy and paste from what you see on the screen right into your spreadsheet application. The only thing missing are column headers, which I plan to add in a few days.

 

The email address isn't there, but you can add it as follows:

 

in all_customers.php, change this:

  $customers_query_raw = "select c.customers_id , c.customers_default_address_id, 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 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 order by a.entry_lastname ASC";

 

to this:

  $customers_query_raw = "select c.customers_id , c.customers_default_address_id, c.customers_email_address, 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 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 order by a.entry_lastname ASC";

 

Then, change this:

        <tr>
          <td class="dataTableContent"><?php echo $customers['entry_lastname'], ", " , $customers['entry_firstname']?></td> 
          <td class="dataTableContent"><?php echo $customers['entry_street_address']; ?></td> 
          <td class="dataTableContent"><?php echo $customers['entry_suburb']; ?></td> 
          <td class="dataTableContent"><?php echo $customers['entry_city']; ?></td> 
          <td class="dataTableContent"><?php echo $entry_state ?></td> 
          <td class="dataTableContent"><?php echo $customers['entry_postcode']; ?></td> 
          <td class="dataTableContent"><?php echo $country; ?></td> 
      </tr>
      <tr><td colspan="7"><hr></td></tr>

 

to this:

        <tr>
          <td class="dataTableContent"><?php echo $customers['entry_lastname'], ", " , $customers['entry_firstname']?></td> 
          <td class="dataTableContent"><?php echo $customers['customers_email_address']; ?></td> 
          <td class="dataTableContent"><?php echo $customers['entry_street_address']; ?></td> 
          <td class="dataTableContent"><?php echo $customers['entry_suburb']; ?></td> 
          <td class="dataTableContent"><?php echo $customers['entry_city']; ?></td> 
          <td class="dataTableContent"><?php echo $entry_state ?></td> 
          <td class="dataTableContent"><?php echo $customers['entry_postcode']; ?></td> 
          <td class="dataTableContent"><?php echo $country; ?></td> 
      </tr>
      <tr><td colspan="8"><hr></td></tr>

 

You should be good to go after that.

 

It also _looks_ better if your heading_title section looks like this (minor change):

 

      <tr>
       <td colspan="2"><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
         </tr>
       </table></td>
     </tr>
     <tr>

 

-jared

Share this post


Link to post
Share on other sites

Just add "customers_newsletter" in the same way customers_email_address was added in the example above.

 

-jared

Share this post


Link to post
Share on other sites

Hugo - - can you clarify your question, please? I don't understand.

 

-jared

Share this post


Link to post
Share on other sites

The heading title it does not appear.

Appear HEADING_TITLE.

So, i want to know where is this HEADING_TITLE?

This Title in customers.php work's, but in all_customers.php do not.

Share this post


Link to post
Share on other sites

It sounds like you didn't copy the language file into admin/includes/languages/english/all_customers.php.

 

-jared

Share this post


Link to post
Share on other sites

Hello everyone

 

I have customized this good contribution, and added customers ID with a link to edit the customer by clicking on the ID number, added the customers e-mail adress with a link to send her/him an e-mail by clicking on the e-mail adress and last, but not least added a table heading.

My all_customers.php

 

<?php
/*
?$Id: all_customers.php, v1.0 March 21, 2005 18:45:00

?osCommerce, Open Source E-Commerce Solutions
?http://www.oscommerce.com
?Copyright (c) 2002 - 2004 osCommerce

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

?require('includes/application_top.php');

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
?<tr>
? ?<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
? ?</table></td>
<!-- body_text //-->
? ?<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">
? ? ?<tr>
? ? ? ?<td colspan="2"><table border="0" width="100%" cellspacing="0" cellpadding="0">
? ? ? ? ?<tr>
? ? ? ? ? ?<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
? ? ? ? ?</tr>
? ? ? ?</table></td>
? ? ?</tr>
? ? ?<tr>
? ? ? ?<td colspan="6"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
? ? ?</tr>
? <tr class="dataTableHeadingRow">
?<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_ID; ?></td>
?<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NAME; ?></td>
?<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CONTACT; ?></td>
?<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_LOCATION; ?></td>
?<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_STATE; ?></td>
?<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NEWSLETTER; ?></td>
?</tr>
?<tr>
? ? <td colspan="6"><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
?</tr>
<?php 
? ?
?$customers_query_raw = "select c.customers_id, c.customers_gender, c.customers_default_address_id, c.customers_email_address, c.customers_telephone, c.customers_fax, c.customers_newsletter, a.address_book_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 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 order by a.entry_lastname ASC";
?$customers_query = tep_db_query($customers_query_raw); ?
?while ($customers = tep_db_fetch_array($customers_query)) {
? if ( tep_not_null($customers['customers_id']) ) {
? ?$entry_state = tep_get_zone_name($customers['entry_country_id'], $customers['entry_zone_id'], $customers['entry_state']);
? ?$country = tep_get_country_name($customers['entry_country_id']);
? if ($customers['customers_gender'] == 'm') {
? ?$gender = TEXT_GENDER_MALE;
? ?}
? else {
? ?$gender = TEXT_GENDER_FEMALE;
? ?}
? ?
? if ($customers['customers_newsletter'] < '1') {
? ?$newsletter = TEXT_NEWSLETTER_NO;
? ?}
? else {
? ?$newsletter = TEXT_NEWSLETTER_YES;
? ?}
? ??>
? ? ? ?<tr>
? ? ? ? ? <td class="dataTableContent" valign="top"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS . '?&cID=' . $customers['customers_id'] . '&action=edit') . '"' . ' target="blank">' . $customers['customers_id'] . '</a>'; ?></td>
? ? ?<td class="dataTableContent" valign="top"><?php echo $gender . '<br>' . $customers['entry_lastname'] . '<br>' . $customers['entry_firstname']?></td> 
? ? ? ? ? <td class="dataTableContent" valign="top"><?php echo '<a href="mailto:' . $customers['customers_email_address'] . '">' . $customers['customers_email_address'] . '</a><br>' . $customers['customers_telephone'] . '<br>' . $customers['customers_fax']; ?></td>
? ? ? ? ? <td class="dataTableContent" valign="top"><?php echo $customers['entry_postcode'], " " , $customers['entry_city'] . '<br>' . $customers['entry_street_address']; ?></td>
? ? ?<td class="dataTableContent" valign="top"><?php echo $country . '<br>' . '- ' . $entry_state; ?></td>
? ? ?<td class="dataTableContent" valign="top"><?php echo $newsletter; ?></td>
? ? ? </tr>
? ? ? <tr><td colspan="6"><hr></td></tr>
? ? ? <?php ? ?
? ? ?} else { }
?} ? ? ? ? ?
?>

<!-- body_text_eof //-->
?</tr>
</table>

<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
</body>
</html>

 

My admin/includes/languages/english/all_customers.php

 

<?php
/*
?$Id: customers.php,v 1.12 2002/01/12 18:46:27 hpdl Exp $

?osCommerce, Open Source E-Commerce Solutions
?http://www.oscommerce.com

?Copyright (c) 2002 osCommerce

?Released under the GNU General Public License
*/

define('HEADING_TITLE', 'All Customers');

define('TABLE_HEADING_ID', 'ID');
define('TABLE_HEADING_NAME', 'Name');
define('TABLE_HEADING_CONTACT', 'Contact');
define('TABLE_HEADING_LOCATION', 'Adress');
define('TABLE_HEADING_STATE', 'Country');
define('TABLE_HEADING_NEWSLETTER', 'Newsletter');

define('TEXT_GENDER_MALE', 'Mr');
define('TEXT_GENDER_FEMALE', 'Mrs');
define('TEXT_NEWSLETTER_YES', 'Newsletter');
define('TEXT_NEWSLETTER_NO', 'no Newsletter');
?>

 

My admin/includes/languages/german/all_customers.php

 

<?php
/*
?$Id: customers.php,v 1.12 2002/01/12 18:46:27 hpdl Exp $

?osCommerce, Open Source E-Commerce Solutions
?http://www.oscommerce.com

?Copyright (c) 2002 osCommerce

?Released under the GNU General Public License
*/

define('HEADING_TITLE', 'Alle Kunden');

define('TABLE_HEADING_ID', 'ID');
define('TABLE_HEADING_NAME', 'Name');
define('TABLE_HEADING_CONTACT', 'Kontakt');
define('TABLE_HEADING_LOCATION', 'Adresse');
define('TABLE_HEADING_STATE', 'Land');
define('TABLE_HEADING_NEWSLETTER', 'Newsletter');

define('TEXT_GENDER_MALE', 'Herr');
define('TEXT_GENDER_FEMALE', 'Frau');
define('TEXT_NEWSLETTER_YES', 'Newsletter');
define('TEXT_NEWSLETTER_NO', 'kein Newsletter');
?>

 

If you want it, simply replace the code of the original files with the code posted here.

 

BoF

Edited by BoF

Share this post


Link to post
Share on other sites

Great customization :thumbsup:

 

I like put in the top of customer table one filter. Example newsletter yes or not

 

It's Easy?

Share this post


Link to post
Share on other sites

Ulrich - great addition! You ought to release it as a an 1.2 or 2.0 update to this contribution.

 

Hugo - It wouldn't be that difficult to add that filter, but it'd be easier from UI perspective to simply do another report - - all customers that are subscribed to the newsletter. You could do that by adding c.

customers_newsletter to the beginning of the query, and then "where c.

customers_newsletter = 1" to the end of the query. Then it would only return customers who are subscribed to the newsletter.

 

-jared

Share this post


Link to post
Share on other sites

Hi,

 

Excellent contribution!

 

What I'd like to know is, if there's a way of adding the customers_info_date_account_created from table costomers_info to the report's output, maybe with a date filter.

 

Anyone?

Share this post


Link to post
Share on other sites

Can you tell me how to adjust the number generated per page as it is timing out.

Share this post


Link to post
Share on other sites

Anyone? Here is code:

 

<?php
/*
 $Id: all_customers.php, v1.0 March 21, 2005 18:45:00

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com
 Copyright (c) 2002 - 2004 osCommerce

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

 require('includes/application_top.php');

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
   <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
   </table></td>
<!-- body_text //-->
   <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

     <tr>
       <td colspan="2"><table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
           <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
         </tr>
       </table></td>
     </tr>
     <tr>
       <td><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
     </tr>
<?php

 $customers_query_raw = "select c.customers_id , c.customers_default_address_id, c.customers_email_address, 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 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 order by a.entry_lastname ASC";
 $customers_query = tep_db_query($customers_query_raw);
 while ($customers = tep_db_fetch_array($customers_query)) {
         if ( tep_not_null($customers['customers_id']) ) {
                 $entry_state = tep_get_zone_name($customers['entry_country_id'], $customers['entry_zone_id'], $customers['entry_state']);
                 $country = tep_get_country_name($customers['entry_country_id']);

                 ?>
                     <tr>
                               <td class="dataTableContent"><?php echo $customers['entry_lastname'], ", " , $customers['entry_firstname']?></td>
                               <td class="dataTableContent"><?php echo $customers['customers_email_address']; ?></td>
                               <td class="dataTableContent"><?php echo $customers['entry_street_address']; ?></td>
                               <td class="dataTableContent"><?php echo $customers['entry_suburb']; ?></td>
                               <td class="dataTableContent"><?php echo $customers['entry_city']; ?></td>
                               <td class="dataTableContent"><?php echo $entry_state ?></td>
                               <td class="dataTableContent"><?php echo $customers['entry_postcode']; ?></td>
                               <td class="dataTableContent"><?php echo $country; ?></td>
                           </tr>
                           <tr><td colspan="8"><hr></td></tr>
             <?php
     } else { }
 }
?>

<!-- body_text_eof //-->
 </tr>
</table>

<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
</body>
</html>

Share this post


Link to post
Share on other sites

Hi , I have just installed v2 all customers report, great contibution!!!

 

I just have a problem with exported to .csv the full complete list of customers in my database. It would seem to be exporting up the letter "C" and I have customers with the name of young.

 

Does anyone know a fix for this problem????

 

Many Thanks

 

Julian

 

julian@zenastoys.co.uk

http://www.zenastoys.co.uk

Share this post


Link to post
Share on other sites

Julian - I haven't worked with v2 yet (Thanks for the updates!!) but one easy workaround would be to simply copy and paste the customer info on the screen into Excel / OpenOffice and then save it from there as .csv

 

knokenet - shouldn't be too difficult, but I need to get to bed, so I'll have a look at it tomorrow if I can. Things have been pretty hectic the last few weeks.

 

Johnny - Unless Hugo added this in v2, there isn't any way to page through it. It's in the to-do list, though. I just have to figure out how to use the osC functions to do it.

 

-jared

Edited by jcall

Share this post


Link to post
Share on other sites

I have recently installed All Customers Report, but when I click on "Save CSV" I get the following header error message:

 

Warning: Cannot modify header information - headers already sent by (output started at /home/nubsnob/www/shop/admin/includes/languages/english/all_customers.php:29) in /home/nubsnob/www/shop/admin/all_customers.php on line 28

 

Warning: Cannot modify header information - headers already sent by (output started at /home/customer/www/shop/admin/includes/languages/english/all_customers.php:29) in /home/customer/www/shop/admin/all_customers.php on line 29

 

Warning: Cannot modify header information - headers already sent by (output started at /home/customer/www/shop/admin/includes/languages/english/all_customers.php:29) in /home/customer/www/shop/admin/all_customers.php on line 30

 

Warning: Cannot modify header information - headers already sent by (output started at /home/customer/www/shop/admin/includes/languages/english/all_customers.php:29) in /home/customer/www/shop/admin/all_customers.php on line 31

 

Warning: Cannot modify header information - headers already sent by (output started at /home/customer/www/shop/admin/includes/languages/english/all_customers.php:29) in /home/customer/www/shop/admin/all_customers.php on line 32

 

Warning: Cannot modify header information - headers already sent by (output started at /home/customer/www/shop/admin/includes/languages/english/all_customers.php:29) in /home/customer/www/shop/admin/all_customers.php on line 33

 

I checked all of my edits and recopied both all_customers.php files to the server, but am still getting the same error.

 

Can anyone help?

 

TIA


cdickson

Share this post


Link to post
Share on other sites

Hello,

 

Bravo for this nice contribution. Unfortunately for me ...everything words perfect exept the data. I mean by that the the link in the menu is ok, when I click on the link I have the columns, the titles, I can click on the titles to sort the data, but I don't have any data....strange to me..... (I of corse have clients).

 

Does it come form the database ? I am using a MS1 (LiLiBiKiNi.com). Can someone help me in this matter. Thank you very much.

 

Bye, smalto


oscommerce 2.2 ms1 with contrib - lilibikini.com - 2005

Share this post


Link to post
Share on other sites

Has anyone got a fix for the problem that smalto has?

 

I've got the exact same thing occuring.

 

Many thanks in advance.

:) Sadie

Share this post


Link to post
Share on other sites
I have recently installed All Customers Report, but when I click on "Save CSV" I get the following header error message:

I checked all of my edits and recopied both all_customers.php files to the server, but am still getting the same error.

 

Can anyone help?

 

TIA

 

I used output buffering to resolve this issue. To implement:

 

- Open admin/all_customers.php

- Above the first line of code require('includes/application_top.php'); (approx line 15) insert these two lines:

// Turn on output buffering
ob_start();


cdickson

Share this post


Link to post
Share on other sites

I have installed the version 2 of this Contributions on a Clean osCommerce MS2 2.2

 

When I'm going to All Customers page I get this error.

1064 - You have an error in your SQL syntax near 'ON a.entry_zone_id = z.zone_id JOIN countries co ON a.entry_country_id = co.coun' at line 1

 

The query:

 $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";

 

The strange thing is how exactly you have made the succesful installation???

 

If someone knows where is the error, please post here.

 

Thanks in advance and regards

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×