Export invoice totals to .csv additional fields please

[Canvas101]

Hi,

I'm hoping someone can assit can help me out here.

I've been messing with the following module for most of the last 12 hrs but as my knowledge of programming is very limited and slow I've decided to post here to see if anyone can assit me further.

The mdule in question is the export order to .csv a link for download is attached below (http://www.zen-cart.com/index.php?ma...roducts_id=170)

As it currently stands this module only export 9 field to csv. What I require the module to do is export around 24 different field. The fields have been listed below;

[FONT=Calibri]orders_id[/FONT][FONT=Calibri]customer_company[/FONT][FONT=Calibri]customer_name[/FONT][FONT=Calibri]customer_street_address[/FONT][FONT=Calibri]customer_suburb[/FONT][FONT=Calibri]customer_city[/FONT][FONT=Calibri]customer_state[/FONT][FONT=Calibri]customer_postcode[/FONT][FONT=Calibri]customer_country[/FONT][FONT=Calibri]customer_telephone[/FONT][FONT=Calibri][/FONT][FONT=Calibri]order_id[/FONT][FONT=Calibri]delivery_name[/FONT][FONT=Calibri]delivery_street_address[/FONT][FONT=Calibri]delivery_suburb[/FONT][FONT=Calibri]delivery_city[/FONT][FONT=Calibri]delivery_state[/FONT][FONT=Calibri]delivery_postcode[/FONT][FONT=Calibri]delivery_contry[/FONT][FONT=Calibri]shipping_method[/FONT][FONT=Calibri]products_model[/FONT][FONT=Calibri]product_description[/FONT][FONT=Calibri]products_quanitity[/FONT][FONT=Calibri]order_total[/FONT]

I had a go at editing the files my self but havent really got far, basically what I need is a CSV download of the fields above.

Below you will be my basic attempts at modinf the main module file. You can see from the file that it seems logically to do

The high light green text below shows my attempts at starting this.

<?php
/**
* catalog/admin/stats_export_orders.php
*
* Export invoice data from Zen Cart to a CSV file
*
* @author Ahmed Chafik ajchafik@ksksks
* @copyright Cyberscraps 2006
* @version Define("Id: export.php,v 1 2006/06/21 21:24:53 Ahmed")
* @version 2.1 2009/12/09 torvista
* Released under the terms and conditions of the
* GNU General Public License (Version 2)
*
*/
require('includes/application_top.php');
require(DIR_WS_CLASSES . 'currencies.php');
$currencies = new currencies();
//This section outputs the CSV file
if( isset($_POST['download_csv']) ) {
Header('Content-type: application/csv');
Header("Content-disposition: attachment; filename=\"Payments".date('mdy-Hi').".csv\"");

$order = $db->execute("SELECT orders_id, customers_id, date_purchased, payment_method,
customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_state, customers_postcode, customer_country, customers_telephone, delivery_name, delivery_street_adress, delivery_suburb, delivery_city, delivery_state, delivery_postcode, delivery_county, shipping_method, order_total
FROM ". TABLE_ORDERS ."
WHERE downloaded='no'
ORDER BY orders_id ASC");

//includes tx_id echo TABLE_HEADING_INVOICE.','.TABLE_HEADING_DATE.','.TABLE_HEADING_PAYMENT.','.TABLE _HEADING_CUSTOMER.','.TABLE_HEADING_TAXID.','.TABLE_HEADING_TAX_CLASS. ','.TABLE_HEADING_TAX_RATE.','.TABLE_HEADING_EXCL.','.TABLE_HEADING_TAX.','.TABL E_HEADING_TOTAL."\n";
echo TABLE_HEADING_INVOICE.','.TABLE_HEADING_DATE.','.TABLE_HEADING_PAYMENT.','.TABLE _HEADING_CUSTOMER.','.TABLE_HEADING_TAX_CLASS. ','.TABLE_HEADING_TAX_RATE.','.TABLE_HEADING_EXCL.','.TABLE_HEADING_TAX.','.TABL E_HEADING_TOTAL."\n";
while( !$order->EOF ) {
list( $invoice, $customers_id, $date, $method, $name, $tax, $total ) = array_values($order->fields);
//These three lines added to get new field, tax_id from CUSTOMERS table
// $sql = "SELECT customers_taxid from " . TABLE_CUSTOMERS . " WHERE customers_id= " . $customers_id;
// $get_taxid = $db->Execute($sql);
// $taxid = $get_taxid->fields['customers_taxid'];
//This gets the tax class from the ORDERS_TOTAL table
$sql = "SELECT title from " . TABLE_ORDERS_TOTAL . " WHERE orders_id= " . $invoice . " AND class = 'ot_tax'";
$get_tax_class = $db->Execute($sql);
$tax_class = $get_tax_class->fields['title'];
//This next line from the original code apparently was to calculate the tax as a percentage!
//$get_tax = ((($total-5)/($total-5-$tax))-1)*100;
$tax_rate = ((($tax)/($total-$tax)))*100;
/** with tx_id echo "$invoice," .
zen_date_short($date).",\"$method\",\"$name\",\"$taxid\",\"$tax_class\",\"".
round(zen_display_tax_value($tax_rate, true),0).'%","'.
($total-$tax).'","'.
$tax.'","'.
$total."\"\n";
*/
echo "$invoice," .
zen_date_short($date).",\"$method\",\"$name\",\"$tax_class\",\"".
round(zen_display_tax_value($tax_rate, true),0).'%","'.
($total-$tax).'","'.
$tax.'","'.
$total."\"\n";
$order->MoveNext();
}
//This line flags the order as having been exported so it wont be exported next time
//$db->execute('UPDATE '. TABLE_ORDERS .' SET downloaded="yes" WHERE downloaded="no"');
//For repeated testing use this query to set the flags back to no:
//UPDATE ORDERS SET downloaded="no" WHERE downloaded="yes";
//and comment out the $db.. line above

exit;
}
?>
<!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">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript">
<!--
function init()
{
cssjsmenu('navbar');
if (document.getElementById)
{
var kill = document.getElementById('hoverJS');
kill.disabled = true;
}
}
// -->
</script>
</head>
<body onLoad="init()">
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<table border="0" width="100%" cellspacing="2" cellpadding="2">
<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td class="pageHeading"><?php echo HEADING_TITLE_EXPORT_ORDERS; ?></td>
<td class="pageHeading" align="right"><?php echo zen_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
</tr>
</table></td>
</tr>
<tr>
<td>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr>
<td valign="top">
<table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr class="dataTableHeadingRow">
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_INVOICE; ?></td>
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_DATE; ?></td>
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PAYMENT; ?></td>
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMER; ?></td>
<!--<td class="dataTableHeadingContent"><?php// echo TABLE_HEADING_TAXID; ?></td><!--extra line for tax_id-->
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_TAX_CLASS; ?></td>
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_TAX_RATE; ?></td><!--calculated from amounts-->
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_EXCL; ?></td>
<td class="dataTableHeadingContent"><?php echo TABLE_HEADING_TAX; ?></td>
<td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL; ?>&nbsp;</td>
</tr>
<?php
$query = "SELECT orders_id, customers_id, date_purchased, payment_method,
customers_name, order_tax, order_total
FROM ". TABLE_ORDERS ."
ORDER BY orders_id ASC";
$query = strtolower($query);

$order_pages = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $query, $rows);
$order = $db->execute($query);
while( !$order->EOF ) {
list( $invoice, $customers_id, $date, $method, $name, $tax, $total ) = array_values($order->fields);
//These three lines added to get new field, tax_id from CUSTOMERS table
// $sql = "SELECT customers_taxid from " . TABLE_CUSTOMERS . " WHERE customers_id= " . $customers_id;
// $get_taxid = $db->Execute($sql);
// $taxid = $get_taxid->fields['customers_taxid'];
//This gets the tax class from the ORDERS_TOTAL table
$sql = "SELECT title from " . TABLE_ORDERS_TOTAL . " WHERE orders_id= " . $invoice . " AND class = 'ot_tax'";
$get_tax_class = $db->Execute($sql);
$tax_class = $get_tax_class->fields['title'];
//This next line from the original code apparently was to calculate the tax as a percentage!
//$get_tax = ((($total-5)/($total-5-$tax))-1)*100;
$tax_rate = ((($tax)/($total-$tax)))*100;
?>
<tr class="dataTableRow" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)" onClick="window.open('<?php echo zen_href_link(FILENAME_ORDERS_INVOICE, 'oID=' . $invoice, 'NONSSL'); ?>')">
<td class="dataTableContent" align="right"><?php echo $invoice; ?>&nbsp;&nbsp;</td>
<td class="dataTableContent"><?php echo zen_date_short($date) ?></td>
<td class="dataTableContent"><?php echo $method ?></td>
<td class="dataTableContent"><?php echo $name ?></td>
<!--<td class="dataTableContent">--><?php//= $taxid ?><!--</td>extra line for tax_id-->
<td class="dataTableContent"><?php echo $tax_class ?></td>
<td class="dataTableContent"><?php echo round(zen_display_tax_value($tax_rate, true),0) ?>%</td>
<td class="dataTableContent"><?php echo $currencies->format($total-$tax); ?></td>
<td class="dataTableContent"><?php echo $currencies->format($tax); ?></td>
<td class="dataTableContent" align="right"><?php echo $currencies->format($total); ?>&nbsp;</td>
</tr>
<?php
$order->MoveNext();
}
if( !isset($invoice) ) {
?>
<tr class="dataTableRow">
<td class="dataTableContent" align="center" colspan="7"><?php echo ERROR_NO_ORDERS; ?></td>
</tr>
<?php
}
?>
</table></td>
</tr>
<tr>
<td>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr>
<td class="smallText" align="left"><?php echo $order_pages->display_count($rows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, $_GET['page'], TABLE_FOOTER_PAGE_COUNT); ?></td>
<td class="smallText" align="right"><?php echo $order_pages->display_links($rows, MAX_DISPLAY_SEARCH_RESULTS_REPORTS, MAX_DISPLAY_PAGE_LINKS, $_GET['page']); ?></td>
</tr>
<tr>
<td colspan="2" class="smallText" align="right">
<form name="download_csv" method="post" action="">
<input style="font-weight: bold" name="download_csv" type="submit" value="<?php echo EXPORT_BUTTON_TEXT; ?>" />
</form>
</td>
</tr>
</table>
</td>
</tr>
</table></td>
</tr>
</table>
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

[torvista]

There is a support thread for this which is hard to find,
http://www.zen-cart.com/forum/showthread.php?p=816553
I'll continue this there,
cheers
Steve