Jump to content
jcall

All Customers Report

Recommended Posts

Modifications to 2.1a - much improved

  1. Shows all customers
  2. Save CSV does not show warning messages
  3. CSV file is properly named
  4. CSV file copes with commas in fields
  5. -
  6. Downloaded file has correct MIME type text/csv
  7. Removed two blank lines from start of CSV file
  8. Added header row to CSV file

I have just installed All Customers Report 2.1a and have had to make some changes to get it up and running.

In case these are useful to others I have detailed them here:

 

Additionally I shall attempt to post my changes as a file on http://www.oscommerce.com/community/contributions,3045

 

1) I noticed that not all the customers were appearing in the report (only 6 out of 31).

To solve this I changed the definition of $customers_query_raw in admin\all_customers.php (around line 117) to use a LEFT JOIN between TABLE_CUSTOMERS and TABLE_ZONES instead of a JOIN.

Original:

  $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";
 $customers_query = tep_db_query($customers_query_raw);

Mine:

$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 LEFT 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";
 $customers_query = tep_db_query($customers_query_raw);

 

2) The Save CSV button resulted in a page of warnings about header information not being able to be modified.

I solved this using the information provided by cdickson (http://forums.oscommerce.com/index.php?s=&showtopic=142447&view=findpost&p=702159)

 

3) The CSV file downloaded did not have a file extension, it was simply named 'Customer_list'

It was evident in the code within admin/all_customers.php that the filename was supposed to include the current date and time (around line 178) and was to end in .csv (around line 22). However the filename is terminated after 'Customer_list' because of the prence of at least one new line character introduced by the PHP code contained within the hidden input tag. This was solved by placing all the code to generate the filename on one line and moving the PHP comment:

Original:

				<td class="smallText" colspan="4"><form action="<?php echo $_SERVER['PHP_SELF']; ?>" method=post><input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'><input type='hidden' name='saveas' value='Customer_list <?php
				//suggested file name for csv, include year and month 
			echo date("Y" . "-" . "m" . "-" . "d" . "-" . "Hi"); 
			?>'><input type="submit" value="<?php echo TEXT_BUTTON_REPORT_SAVE;?>"></form>
			</td>

Mine:

				<td class="smallText" colspan="4"><form action="<?php echo $_SERVER['PHP_SELF']; ?>" method=post>
			<input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'>
			<?php //suggested file name for csv, include year and month ?>
			<input type='hidden' name='saveas' value='Customer_List_<?php echo date("Y" . "_" . "m" . "_" . "d" . "_" . "Hi"); ?>'><input type="submit" value="<?php echo TEXT_BUTTON_REPORT_SAVE;?>"></form>
			</td>

 

4) The CSV file does not properly cope with commas within fields, it throws them away to prevent misalignment of fields, however this makes it impossible to read the customer names as it is unclear where the surname ends and the forname(s) start. The CSV standard states that any field containing a comma should have double quotes around it (and also that fields containing double quotes should be surroud by double quotes and the original double quote should be repeated!) [http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm]. Basically the way the CSV file is currently put together breaks the CSV standard in several ways. It would be too complicated to look for fields containg characters which mean they must be surrounded by quotes, thankfully the standard allows you to surround all fields by double quotes and those that don't need to be there are ignored. Therefore I modified the mirror_out function detailed in the install.txt which is pasted into /admin/includes/functions/general.php to surround all fields by double quotes, not remove commas from fields, and replace any double quotes within a field with two sets of double quotes.

Original:

  // Produce CSV string for output
 function mirror_out ($field) {
global $csv_accum;
echo $field;
$field = strip_tags($field);
$field = ereg_replace (",","",$field);
if ($csv_accum=='') $csv_accum=$field; 
else 
{if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= $field;
	else $csv_accum .= "," . $field; };
return;
};

Mine:

  // Produce CSV string for output
 function mirror_out ($field) {
global $csv_accum;
echo $field;
$field = strip_tags($field);
$field = ereg_replace ("\"","\"\"",$field);
if ($csv_accum=='') $csv_accum= "\"" . $field . "\""; 
else 
{if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= "\"" . $field . "\"";
	else $csv_accum .= "," . "\"" . $field . "\""; };
return;
};

 

5) Item 4 threw up another problem in that the CSV file produced contained escaped double quotes rather than just double quotes.

This was solved by modifying admin/all_customers.php where the CSV file is provided to the browser for download (around line 34) to put the text through the stripslashes function:

Original:

  echo $csv_string;

Mine:

  echo stripslashes($csv_string);

 

6) I also noticed the MIME type of the downloaded file was given in admin/all_customers.php (around line 32) as Application/octet-stream which is incorrect.

The correct MIME type must be a text type rather than an application type and the best MIME type I could find was text/csv [http://www.shaftek.org/publications/drafts/mime-csv/draft-shafranovich-mime-csv-00.html] (the other option given is text/comma-separated-values)

Original:

  header("Content-Type: Application/octet-stream");

Mine:

  header("Content-Type: text/csv");

 

7) The resulting CSV file was almost correct now, however it contained two blank lines at the top of the file which appear in OpenOffice.org Calc/Excel. These two lines were caused by the call in admin/all_customers.php to the require function (around line 15). To solve this I moved the statement 'require('includes/application_top.php');' to below the block of code producing the CSV file, it ended up after the lines

  else echo "CSV string empty";
exit;
};

 

and before

?>

(i.e. around line 39 in the original file).

In doing so I moved the call to ob_start(); to be the first statement in the block relating to the CSV output, i.e.

// entry for bouncing csv string back as file
// Turn on output buffering
ob_start();

 

8) The CSV file did not contain a header row with the column names. To solve this i modified the tep_sort_order function detailed in the install.txt which is pasted into /admin/includes/functions/general.php to add the headings to the CSV data as each heading is displayed on screen.

Original:

// Sort Function 
 function tep_sort_order ($orderby, $sorted, $title, $order_criteria){
 if (!isset($orderby) or ($orderby == $order_criteria and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; 
$link = '<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, 'orderby=' . $order_criteria .'&sorted='. $to_sort) . '" class="headerLink">' . $title . '</a>';
 return $link;
 }

Mine:

// Sort Function 
 function tep_sort_order ($orderby, $sorted, $title, $order_criteria){
 global $csv_accum;
 if (!isset($orderby) or ($orderby == $order_criteria and $sorted == "ASC"))  $to_sort = "DESC"; else $to_sort = "ASC"; 

$field = strip_tags($title);
$field = ereg_replace ("\"","\"\"",$field);
if ($csv_accum=='') $csv_accum= "\"" . $field . "\""; 
else 
{if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= "\"" . $field . "\"";
else $csv_accum .= "," . "\"" . $field . "\""; };
$link = '<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, 'orderby=' . $order_criteria .'&sorted='. $to_sort) . '" class="headerLink">' . $title . '</a>';
  return $link;
 }

 

Has the problem with apostraphes (') in fields been solved yet? e.g. a name "O'Neil". Presumably when making up the string to go in the hidden text field each field could be put throw addslahes (since now everything should work out as I call stripslahes when outputting the file itself). If I get time I shall try this out/test it.

 

In conclusion All Customers is a great add on for OsCommerce, just version 2.1a contains a number of bugs which limit its usability.

Share this post


Link to post
Share on other sites
Has the problem with apostraphes (') in fields been solved yet? e.g. a name "O'Neil". Presumably when making up the string to go in the hidden text field each field could be put throw addslahes (since now everything should work out as I call stripslahes when outputting the file itself). If I get time I shall try this out/test it.

 

thanks for all those 2.1B update and clear explanations , we are improving this customer list :

 

2 problem :

 

- i got apostrophes in adresse and name and it make the csv be broken and finish to the first apostrophe :-(

- in the cvs , the field name are in english, can't we use the language define for the field so that they appears in the admin language?


MS2

Share this post


Link to post
Share on other sites

Hi all I'm getting this error message when running all_customers.php can any kind soul help me out.

 

1054 - Unknown column 'a.entry_zone_id' in 'on clause'

 

Thanks

 

Optimal

Share this post


Link to post
Share on other sites

Problem now resolved. just do the following.

 

 

This was fixed by member name:- jocg

 

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

Edited by Optimalkiller

Share this post


Link to post
Share on other sites
thanks for all those 2.1B update and clear explanations , we are improving this customer list :

 

2 problem :

 

- i got apostrophes in adresse and name and it make the csv be broken and finish to the first apostrophe :-(

- in the cvs , the field name are in english, can't we use the language define for the field so that they appears in the admin language?

Thanks for the feedback on the apostrophes issue. I think I have this sorted now.

 

In admin/all_customers.php you need to make the following changes:

 

Line 32:

echo stripslashes($csv_string);

needs to be changed to

echo html_entity_decode(stripslashes($csv_string), ENT_QUOTES);

Line 179

<input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'>

needs to be changed to

<input type='hidden' name='csv' value='<?php echo htmlentities($csv_accum, ENT_QUOTES); ?>'>

The technical explanation behind this is that the apostrophe in the csv_accum variable was closing the value='' attribute in the above input tag. Therefore when the csv form field was submitted only the data up to the first apostrophe in the file was transmitted. To overcome this I have used the htmlentities function to convert apostrophes into their html code '. When the CSV is outputted I call html_entity_decode to reverse this and convert the ' codes back to apostrophes. (Actually htmlentities converts more than just the apostrophes but since html_entity_decode completely reverses the conversion this does not matter, we are only interested in getting the apostrophes sorted.

 

As for the CSV headings always being in English: The headings written to the CSV are exactly the same as those displayed on screen in the report. It looks in the code as if the titles are hard coded. Perhaps the original devloper was hoping to make use of the language files but didn't get to implementing it in the report?

 //BOF HEADER  ?>
<tr class="dataTableHeadingRow">
<? /*<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td> */ ?>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, FULL_NAME, 'name');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, EMAIL, 'email');?></td>
<td class="dataTableHeadingContent"><?php
									  echo tep_sort_order ($orderby, $sorted, ADDRESS, 'address');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, CITY_NAME, 'city');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, STATE, 'state');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, POSTAL_CODE, 'pcode');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, CONTRY_NAME, 'country');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, TELEPHONE_NUMBER, 'telephone');

//EOF HEADER
?></td>

	   	 </tr>

Share this post


Link to post
Share on other sites
thanks for all those 2.1B update and clear explanations , we are improving this customer list :

 

2 problem :

 

- i got apostrophes in adresse and name and it make the csv be broken and finish to the first apostrophe :-(

- in the cvs , the field name are in english, can't we use the language define for the field so that they appears in the admin language?

Thanks for the feedback on the apostrophes issue. I think I have this sorted now.

 

In admin/all_customers.php you need to make the following changes:

 

Line 32:

echo stripslashes($csv_string);

needs to be changed to

echo html_entity_decode(stripslashes($csv_string), ENT_QUOTES);

Line 179

<input type='hidden' name='csv' value='<?php echo $csv_accum; ?>'>

needs to be changed to

<input type='hidden' name='csv' value='<?php echo htmlentities($csv_accum, ENT_QUOTES); ?>'>

The technical explanation behind this is that the apostrophe in the csv_accum variable was closing the value='' attribute in the above input tag. Therefore when the csv form field was submitted only the data up to the first apostrophe in the file was transmitted. To overcome this I have used the htmlentities function to convert apostrophes into their html code '. When the CSV is outputted I call html_entity_decode to reverse this and convert the ' codes back to apostrophes. (Actually htmlentities converts more than just the apostrophes but since html_entity_decode completely reverses the conversion this does not matter, we are only interested in getting the apostrophes sorted.

 

As for the CSV headings always being in English: The headings written to the CSV are exactly the same as those displayed on screen in the report. It looks in the code as if the titles are hard coded. Perhaps the original devloper was hoping to make use of the language files but didn't get to implementing it in the report?

 //BOF HEADER  ?>
<tr class="dataTableHeadingRow">
<? /*<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NUMBER; ?></td> */ ?>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, FULL_NAME, 'name');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, EMAIL, 'email');?></td>
<td class="dataTableHeadingContent"><?php
									  echo tep_sort_order ($orderby, $sorted, ADDRESS, 'address');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, CITY_NAME, 'city');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, STATE, 'state');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, POSTAL_CODE, 'pcode');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, CONTRY_NAME, 'country');?></td>
<td class="dataTableHeadingContent"><?php echo tep_sort_order ($orderby, $sorted, TELEPHONE_NUMBER, 'telephone');

//EOF HEADER
?></td>

	   	 </tr>

Share this post


Link to post
Share on other sites
It looks in the code as if the titles are hard coded. Perhaps the original devloper was hoping to make use of the language files but didn't get to implementing it in the report?

Actually looking more carefully at the source code the language file is used for the headings since the definition for ADDRESS is Street Address in the english language file and this is what gets displayed on screen and in the CSV. Perhaps the issue you are having is more to do with getting the correct lanague file to be used?

Share this post


Link to post
Share on other sites

New SQL Query

 

For those of you that have had SQL errors appearing (in particular invalid column id/syntax error around the join statements) or have noticed that customers without zones are not displayed try this revised SQL query I have put together:

 

In file admin/all_customers.php (around line 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 INNER JOIN " . TABLE_ADDRESS_BOOK . " a ON c.customers_default_address_id = a.address_book_id INNER JOIN " . TABLE_COUNTRIES . " co ON entry_country_id = co.countries_id LEFT OUTER JOIN ". TABLE_ZONES ." z ON a.entry_zone_id = z.zone_id ORDER BY $db_orderby $sorted";

The join syntax has been changed as follows:

JOIN > INNER JOIN

LEFT JOIN > LEFT OUTER JOIN

The LEFT OUTER JOIN to the TABLE_ZONES table is necessary to include those customers who do not have a zone.

Share this post


Link to post
Share on other sites
New SQL Query

 

For those of you that have had SQL errors appearing (in particular invalid column id/syntax error around the join statements) or have noticed that customers without zones are not displayed try this revised SQL query I have put together:

 

In file admin/all_customers.php (around line 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 INNER JOIN " . TABLE_ADDRESS_BOOK . " a ON c.customers_default_address_id = a.address_book_id INNER JOIN " . TABLE_COUNTRIES . " co ON entry_country_id = co.countries_id LEFT OUTER JOIN ". TABLE_ZONES ." z ON a.entry_zone_id = z.zone_id ORDER BY $db_orderby $sorted";

The join syntax has been changed as follows:

JOIN > INNER JOIN

LEFT JOIN > LEFT OUTER JOIN

The LEFT OUTER JOIN to the TABLE_ZONES table is necessary to include those customers who do not have a zone.

 

fmeetsoft, do you think we should make a new version wih your modification , is it an upgrade or only to do for shop owner who know they got customer without zone ?


MS2

Share this post


Link to post
Share on other sites

I installed All Customers Report version 2.1c and modified the request $customers_query_raw = "SELECT c.customers_id ... as previously noticed here.

 

I have the following error displayed :

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /icons/admin/includes/functions/database.php on line 107

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /icons/admin/includes/functions/database.php on line 99

 

Any idea ?

 

Thanks in advance,

Jacques

Share this post


Link to post
Share on other sites
When changing to the newest CUSTOMER_EXPORT.PHP file I get the following:

 

Warning: filesize() [function.filesize]: Stat failed for <table border=0 cellpadding=5 cellspacing=5 style='border: solid 1px #E0E0E0;' ><tr style='border: 0px;'><td colspan='13' align=left><strong>osCommerce</strong><br><strong>Customer List</stong><br>Jan 15 2007</td></tr><tr style='background:#c0c0c0;'><th>Firstname</th><th>Lastname</th><th>Company</th><th>Address Line 1</th><th>Address Line 2</th><th>City</th><th>Zip</th><th>County</th><th>Country</th><th>Phone</th><th>Email</th><th>Fax</th><th>Newsletter</th></tr> <tr nowrap bgcolor='#FFFFFF' style='border: solid 1px #E0E0E0;'><td>Test</td><td>User</td><td></td><td>123 Main Street</td><td></td><td>Anywhere</td><t in /home/root/public_html/catalog/admin/customer_export.php on line 106

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 109

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 110

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 111

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 112

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 113

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 116

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 120

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 121

 

Warning: Cannot modify header information - headers already sent by (output started at /home/root/public_html/catalog/admin/customer_export.php:106) in /home/root/public_html/catalog/admin/customer_export.php on line 122

osCommerce

Customer List

Jan 15 2007

Firstname Lastname Company Address Line 1 Address Line 2 City Zip County Country Phone Email Fax Newsletter

Followed by the address list, but it is displayed on the screen and not exported.

 

Anthony

 

I am having the same issue with the customers_export.php file. Any ideas?

 

Thanks!

Have a great day!

Heather

Share this post


Link to post
Share on other sites

Hi

 

I'm not able to see data even I have few records in it. Can anyone advice.

 

Thanks.

 

As I couldn't do print screen, there is the text captured shown below.

 

All Customers

Click title to sort +/-

Name Email Street Address City Province Postal Code Country Telephone No.

Share this post


Link to post
Share on other sites
I installed All Customers Report version 2.1c and modified the request $customers_query_raw = "SELECT c.customers_id ... as previously noticed here.

 

I have the following error displayed :

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /icons/admin/includes/functions/database.php on line 107

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /icons/admin/includes/functions/database.php on line 99

 

Any idea ?

 

Thanks in advance,

Jacques

 

I have that problem too ... anyone have a fix for it???

Share this post


Link to post
Share on other sites

Oh boy...whole site messed up now. Having the same problems as everyone else. New SQL query isn't helping out either. Anyone figure out any fixes?

Share this post


Link to post
Share on other sites
4) The CSV file does not properly cope with commas within fields, it throws them away to prevent misalignment of fields, however this makes it impossible to read the customer names as it is unclear where the surname ends and the forname(s) start. The CSV standard states that any field containing a comma should have double quotes around it (and also that fields containing double quotes should be surroud by double quotes and the original double quote should be repeated!) [http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm]. Basically the way the CSV file is currently put together breaks the CSV standard in several ways. It would be too complicated to look for fields containg characters which mean they must be surrounded by quotes, thankfully the standard allows you to surround all fields by double quotes and those that don't need to be there are ignored. Therefore I modified the mirror_out function detailed in the install.txt which is pasted into /admin/includes/functions/general.php to surround all fields by double quotes, not remove commas from fields, and replace any double quotes within a field with two sets of double quotes.

Original:

  // Produce CSV string for output
 function mirror_out ($field) {
global $csv_accum;
echo $field;
$field = strip_tags($field);
$field = ereg_replace (",","",$field);
if ($csv_accum=='') $csv_accum=$field; 
else 
{if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= $field;
	else $csv_accum .= "," . $field; };
return;
};

Mine:

  // Produce CSV string for output
 function mirror_out ($field) {
global $csv_accum;
echo $field;
$field = strip_tags($field);
$field = ereg_replace ("\"","\"\"",$field);
if ($csv_accum=='') $csv_accum= "\"" . $field . "\""; 
else 
{if (strrpos($csv_accum,chr(10)) == (strlen($csv_accum)-1)) $csv_accum .= "\"" . $field . "\"";
	else $csv_accum .= "," . "\"" . $field . "\""; };
return;
};

 

 

 

I keep on getting a timeout error because of this. Tried using the original and I still get a timeout error. Sometimes it will go through. I have over 25,000 customers!!!

 

What can solve this? Maybe showing 100 customers per page and only the CSV file show the total list? I dont know.

 

Does anyone have any idea how to solve this? This is the error code I get using the original code. I would like to use this new feature but with that one, not even half of my list shows since it takes to long to generate it.

 

Fatal error: Maximum execution time of 30 seconds exceeded in /home/premiere/public_html/admin/includes/functions/general.php on line 1330

 

 

PLEASE HELP!!

Share this post


Link to post
Share on other sites

Hi Guys,

 

I have an issue with downloading the file with Customers Export 1.5 contrib.

Everything works fine on FireFox, the save window opens OK when hitting Export button, but nothing happens when using IE7. I hit the Export button but no window opens.

 

Here is the header info for downloading the file:

 

header("Content-Type: application/force-download\n");

header("Content-disposition: attachment; filename=customers_export_" . date("Ymd") . ".txt");

header("Pragma: no-cache");

header("Expires: 0");

echo $contents;

die();

 

Any ideas?

 

thanks.

Share this post


Link to post
Share on other sites
I have that problem too ... anyone have a fix for it???

 

Me too...

Is there any fix for this ??

 

regards,

Share this post


Link to post
Share on other sites
Me too...

Is there any fix for this ??

 

regards,

 

 

ok, put after the main query:

$customers_query = tep_db_query($customers_query_raw);

 

...and delete de c.head from the main query.

Share this post


Link to post
Share on other sites

Guys i have tried to download and install this addon on my 2.2 osc shop but it still doesent work!!

 

I get a syntax error in admin under customers. Can you please specify the step 3 of installation which is very cery unclear what code to put and where to put it??

 

I am about to rip out my hair due to step 3 i have tried so many things and it still make me syntax errors!

 

Here is the code i use currently in the reports file!:

<?php
/*
 $Id: reports.php,v 1.5 2003/07/09 01:18:53 hpdl Exp $

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

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License
*/
?>
<!-- reports //-->
	  <tr>
		<td>
<?php
 $heading = array();
 $contents = array();

 $heading[] = array('text'  => BOX_HEADING_REPORTS,
				 'link'  => tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, 'selected_box=reports'));

 if ($selected_box == 'reports') {
$contents[] = array('text'  => '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_VIEWED . '</a><br>' .
							   '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_PURCHASED . '</a><br>' .
							   '<a href="' . tep_href_link(FILENAME_STATS_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TOTAL . '</a>');
							   '<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ALL_CUSTOMERS . '</a><br>' .
}

 $box = new box;
 echo $box->menuBox($heading, $contents);
?>
		</td>
	  </tr>
<!-- reports_eof //-->

 

This is what step 3 says:

 

STEP 3 (Add link in Admin)

===========================

Edit /admin/includes/boxes/reports.php. It's a small file. Find the section with lines that look like the following one, and add the following line (this reads easier with word wrap off):

'<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ALL_CUSTOMERS . '</a><br>' .

 

You can add the lines in any order you want, but make sure to follow the correct syntax. The finished section could look like this:

if ($selected_box == 'reports') {

$contents[] = array('text' => '<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_VIEWED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_VIEWED . '</a><br>' .

'<a href="' . tep_href_link(FILENAME_STATS_PRODUCTS_PURCHASED, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_PRODUCTS_PURCHASED . '</a><br>' .

'<a href="' . tep_href_link(FILENAME_ALL_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ALL_CUSTOMERS . '</a><br>' .

'<a href="' . tep_href_link(FILENAME_STATS_CUSTOMERS, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_REPORTS_ORDERS_TOTAL . '</a>');

}

 

What should I put and WHERE SHOULD I PUT IT???

 

Any feedback is very very mucg appreciated :)

Share this post


Link to post
Share on other sites

now i get this error inside the admin under all customers:

 

1054 - Unknown column 'a.entry_zone_id' in 'on clause'

 

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 customers c LEFT JOIN zones z ON a.entry_zone_id = z.zone_id JOIN countries co ON a.entry_country_id = co.countries_id LEFT JOIN 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

 

What should i do here?

 

As I udnerstand the all_customers file already has some of these as default so why it makes this error?

 

Also the link look like this: BOX_REPORTS_ALL_CUSTOMERS

 

what am i doing wrong guys?

Share this post


Link to post
Share on other sites

Ok now i see i reached the same problem as others in this thread i have the following error:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /mnt/w0509/d36/s07/b02be82e/ MY CATALOG/catalog/admin/includes/functions/database.php on line 107

 

AND

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /mnt/w0509/d36/s07/b02be82e/www/MYCATALOG/catalog/admin/includes/functions/database.php on line 99

 

Did any find a solution to this????

 

I have tried the sql update also - didnt work!

 

What is the solution to this? any know a contribution that can do the same job as this (have spent so many hours on this one now is enough!)???

Share this post


Link to post
Share on other sites
ok, put after the main query:

$customers_query = tep_db_query($customers_query_raw);

 

...and delete de c.head from the main query.

 

Please be a bit more specific please - which file is this in? Remember not everyone looking for help has your intimate knowledge of what is where



Don't put off till tomorrow things you can do today as tomorrow has a nasty habit of never coming and eternity is a long time to regret something you should have done today

 

Share this post


Link to post
Share on other sites

Great contribution. I added the "COMPANY" name to the file and translated to portuguese language. Everything works fine. Thanks a lot.

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

×