export orders into CSV problem seeking solution

sw0857 · April 9, 2008

Hello All,

Recently I've encounter one problem using "export orders into csv" module. What i found is the file "exportorders.php" does export through TABLE ORDERS and this results purchase order # with multiple items purchased to be displayed once only. Example like po# 25 suppose to have sku#001 and 002 purchased, but the exported csv only contains po#25 & sku#001 in row 1 instead of po#25, sku#001 on row1 & po#25, sku#002 on row2.

I have also looked into TABLE ORDERS_PRODUCTS and found it has repeat po# if multiple sku is purchased. Below is what I have for the exportorders.php code, any help or suggestion will be greatly appreaciated.

steve

<?php
/*
 $Id: exportorders.php,v 1.1 April 21, 2006 Harris Ahmed (Original Contribution)  $

 Edited and enhanced by Nico Maurer 12-12-2006

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

 Copyright (c) 2004 Oscommerce

 Installation and Use: Put the file in the Admin Folder and Call the script: www.yourstore.com/catalog/admin/export_orders_csv.php
 In the Start field put the first oder no. to export,
 in the end field the last order no. (or choose a big number for export of all orders up to the current date). In the status field
 put the number of the order status to export (the status numbers may vary from shop to shop). For my store status 1 means "processing", which
 is the status orders need to be exported for my store.
 Still very hard coded but it works for my ressource planning software.

 In order to make a link in your reports section of admin do the following, after uploading the file

1.	Insert this line in admin/includes/filenames.php: Under Definitions

  // Export orders to CSV
	  define('FILENAME_EXPORT_ORDERS_CSV', 'export_orders_csv.php');
	  // End Export orders to csv

2.	Insert this line in admin/includes/languages/english.php: under definitions

 // EXPORT_ORDERS_CSV
	 define('BOX_EXPORT_ORDERS_CSV', 'Export my orders to CSV');
	 // End EXPORT_ORDERS_CSV

3.	In admin/includes/boxes/reports.php, change this (or whichever line is NOT last in the menu):

 // add export orders to csv
	 '<a href="' . tep_href_link(FILENAME_EXPORT_ORDERS_CSV, '', 'NONSSL') . '" class="menuBoxContentLink">' . BOX_EXPORT_ORDERS_CSV . '</a><br>' .
	 // end export orders to csv


*/

require('includes/application_top.php');

// Check if the form is submitted

if (!$submitted || $submitted != 1)
{
?>

<!-- header_eof //-->

<!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">
</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="2">
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td class="pageHeading"><?php echo "Export Order" ?></td>
		<td class="pageHeading" align="right"></td>
	  </tr>
	</table>
 </td>
  </tr>
  <!-- first ends // -->
  <tr>
	<td>

<table border="0" style="font-family:tahoma;font-size:11px;" width="100%" cellspacing="2" cellpadding="2">
<tr>
<td>

<form method="GET" action="<?php echo $PHP_SELF; ?>">
<table border="0" style="font-family:tahoma;font-size:11px;" cellpadding="3">
<tr>
<td><?php echo "Start Date :"; ?></td>
<td><input name="start" size="15" value="<?php echo $start; ?>" > (yyyymmdd)</tr>
<tr>
<td><?php echo "End Date :"; ?></td>
<td><input name="end" size="15" value="<?php echo $end; ?>"> 
(yyyymmdd)</tr>
<tr>
<td><?php echo "Order Status:"; ?></td>
<?php
 $orders_statuses = array();
 $orders_status_array = array();
 $orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . (int)$languages_id . "'");
 while ($orders_status = tep_db_fetch_array($orders_status_query)) {
$orders_statuses[] = array('id' => $orders_status['orders_status_id'],
						   'text' => $orders_status['orders_status_name']);
$orders_status_array[$orders_status['orders_status_id']] = $orders_status['orders_status_name'];
 }
?>
<td><?php echo tep_draw_pull_down_menu('status', array_merge(array(array('id' => '', 'text' => 'All Orders')), $orders_statuses), $status); ?>
</tr>
<tr>
<td> </td>
<td><input type="submit" value="<?php echo "Create CSV"; ?>"></td>
</tr>
</table>
<input type="hidden" name="submitted" value="1">
</form>

</td>
</tr>
</table>

</td>
</tr>
</table>

</td></tr>
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?><?php
}
// submitted so generate csv if the form is submitted
else
{
generatecsv($start, $end, $status);
}

// generates csv file from $start order to $end order, inclusive
function generatecsv($start, $end, $status)
{
$delim = ',';

$orders = tep_db_query("select customers_id, orders_id, date_purchased, customers_name
, cc_owner, customers_company, customers_email_address,
billing_street_address, billing_city, billing_state, billing_postcode,
billing_country, customers_telephone, delivery_name,
delivery_company, delivery_street_address, delivery_city, delivery_state,
delivery_postcode, delivery_country, cc_type, cc_number, cc_expires, payment_method, orders_status
from " . TABLE_ORDERS . " where 1 " . ($start ? "and date_purchased >= $start " : "") . ($end ? "and date_purchased <= $end " : "") . ($status ? "and orders_status = $status " : "") . "order by orders_id");



//	$csv_output .= "OrderProductsID".$delim;
$csv_output .= "OrderID".$delim;
$csv_output .= "OrderDate".$delim;
$csv_output .= "CustomerNo".$delim;
$csv_output .= "Company".$delim;
$csv_output .= "Phone".$delim;
$csv_output .= "LastName".$delim;
$csv_output .= "FirstName".$delim;
$csv_output .= "Email".$delim;
$csv_output .= "Address1".$delim;
$csv_output .= "Address2".$delim;
$csv_output .= "City".$delim;
$csv_output .= "State".$delim;
$csv_output .= "Zip".$delim;
$csv_output .= "Country".$delim;
$csv_output .= "Recipient".$delim;
$csv_output .= "ShipAddress1".$delim;
$csv_output .= "ShipAddress2".$delim;
$csv_output .= "ShipCity".$delim;
$csv_output .= "ShipState".$delim;
$csv_output .= "ShipZip".$delim;
$csv_output .= "ShipCountry".$delim;
$csv_output .= "ShipPhone".$delim;
$csv_output .= "ItemQty".$delim;
$csv_output .= "Weight".$delim;
$csv_output .= "SubTotal".$delim;
$csv_output .= "Shipping".$delim;
$csv_output .= "ShipMethod".$delim;
$csv_output .= "PaymentMethod".$delim;
$csv_output .= "CreditCardType".$delim;
$csv_output .= "Status".$delim;
$csv_output .= "ItemNo".$delim;
$csv_output .= "ModelNo".$delim;
$csv_output .= "ItemQty".$delim;
$csv_output .= "ItemWeight".$delim;
$csv_output .= "ItemAttribute".$delim;
$csv_output .= "ItemPrice".$delim;
$csv_output .= "ItemTotal".$delim;
$csv_output .= "TotalShipQty".$delim;
$csv_output .= "Comments".$delim;
$csv_output .= "\n";



while ($row_orders = mysql_fetch_array($orders)) { //start one loop

$Orders_id = $row_orders["orders_id"];
$orders_status = $row_orders["orders_status"];
$customers_id = $row_orders["customers_id"];
$customers_gender = $row_orders["customers_gender"];
$Date1 = $row_orders["date_purchased"];
//list($Date, $Time) = explode (' ',$Date1);
$Date = date('Y.m.d', strtotime($Date1));
$Time= date('H:i:s', strtotime($Date1));
$Name_On_Card1 = $row_orders["customers_name"];
$Name_On_Card = filter_text($Name_On_Card1);// order changed
list($First_Name,$Last_Name) = explode(', ',$Name_On_Card1); // order changed
$Company = filter_text($row_orders["customers_company"]);
$email = filter_text($row_orders["customers_email_address"]);
$payment = filter_text($row_orders["payment_method"]);
$Billing_Address_1 = filter_text($row_orders["billing_street_address"]);
$Billing_Address_2 = "";
$Billing_City = filter_text($row_orders["billing_city"]);
$Billing_State = filter_text($row_orders["billing_state"]);
$Billing_Zip = filter_text($row_orders["billing_postcode"]);
$Billing_Country = str_replace("(48 Contiguous Sta", "", $row_orders["billing_country"]);
$Billing_Phone = filter_text($row_orders["customers_telephone"]);
$ShipTo_Name1 = $row_orders["delivery_name"];
$ShipTo_Name = filter_text($ShipTo_Name1); // order changed
list($ShipTo_First_Name,$ShipTo_Last_Name) = explode(', ',$ShipTo_Name1); // order changed
$ShipTo_Company = filter_text($row_orders["delivery_company"]);
$ShipTo_Address_1 = filter_text($row_orders["delivery_street_address"]);
$ShipTo_Address_2 = "";
$ShipTo_City = filter_text($row_orders["delivery_city"]);
$ShipTo_State = filter_text($row_orders["delivery_state"]);
$ShipTo_Zip = filter_text($row_orders["delivery_postcode"]);
$ShipTo_Country = str_replace("(48 Contiguous Sta", "", $row_orders["delivery_country"]);
$ShipTo_Phone = "";
$Card_Type = $row_orders["cc_type"];
$Card_Number = $row_orders["cc_number"];
$Exp_Date = $row_orders["cc_expires"];
$Bank_Name = "";
$Gateway  = "";
$AVS_Code = "";
$Transaction_ID = "";
$Order_Special_Notes = "";
// --------------------	QUERIES 1  ------------------------------------//
//Orders_status_history for comments
$orders_status_history = tep_db_query("select comments from " . TABLE_ORDERS_STATUS_HISTORY . "
where orders_id = " . $Orders_id);
//$row_orders_status_history = tep_db_fetch_array($comments);
while($row_orders_status_history = mysql_fetch_array($orders_status_history)) {
// end //

$Comments = filter_text($row_orders_status_history["comments"]);

}
// --------------------	QUERIES 2  ------------------------------------//
//Orders_subtotal
$orders_subtotal = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_subtotal' and orders_id = " . $Orders_id);
//$row_orders_subtotal = tep_db_fetch_array($orders_subtotal);
while($row_orders_subtotal = mysql_fetch_array($orders_subtotal)) {
// end //
$Order_Subtotal = filter_text($row_orders_subtotal["value"]);
}
// --------------------	QUERIES 3  ------------------------------------//
//Orders_tax
$Order_Tax = '0';
$orders_tax = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_tax' and orders_id = " . $Orders_id);
//$row_orders_tax = tep_db_fetch_array($orders_tax);
while($row_orders_tax = mysql_fetch_array($orders_tax)) {
// end //
$Order_Tax = filter_text($row_orders_tax["value"]);
}
// --------------------	QUERIES 4  ------------------------------------//
//Orders_Insurance
$orders_insurance = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_insurance' and orders_id = " . $Orders_id);
//$row_orders_insurance = tep_db_fetch_array($orders_insurance);
while($row_orders_insurance = mysql_fetch_array($orders_insurance)) {
// end //
$Order_Insurance = filter_text($row_orders_insurance["value"]);
}
$Tax_Exempt_Message = "";
// --------------------	QUERIES 5a  ------------------------------------//
//Orders_Shipping Versandkosten
$orders_shipping = tep_db_query("select title, value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_shipping' and orders_id = " . $Orders_id);
//$row_orders_shipping = tep_db_fetch_array($orders_shipping);
while($row_orders_shipping = mysql_fetch_array($orders_shipping)) {
// end //
$Order_Shipping_Total = $row_orders_shipping["value"];
$Shipping_Method = filter_text($row_orders_shipping["title"]); // Shipping method from query 5
}
// --------------------	QUERIES 5b  ------------------------------------//
//Orders_Shipping_Nachnahme
unset($nn_gebuehr);
$orders_shipping_nn = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_cod_fee' and orders_id = " . $Orders_id);
//$row_orders_shipping_nn = tep_db_fetch_array($orders_shipping_nn);
while($row_orders_shipping_nn = mysql_fetch_array($orders_shipping_nn)) {
// end //
$nn_gebuehr = $row_orders_shipping_nn["value"];

}
// --------------------	QUERIES 5c  ------------------------------------//
//Orders_Shipping_Minderwert bei Auslandsaufträgen
unset($minderwert);
$orders_shipping_minderwert = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_loworderfee' and orders_id = " . $Orders_id);
//$row_orders_shipping_minderwert = tep_db_fetch_array($orders_shipping_minderwert);
while($row_orders_shipping_minderwert = mysql_fetch_array($orders_shipping_minderwert)) {
// end //
$minderwert = $row_orders_shipping_minderwert["value"];

}
// --------------------	QUERIES 5d  ------------------------------------//
//Orders_Coupon Rabatt bei Couponeinsatz
unset($coupon);
$orders_coupon = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_discount_coupon' and orders_id = " . $Orders_id);
//$row_orders_coupon = tep_db_fetch_array($orders_coupon);
while($row_orders_coupon = mysql_fetch_array($orders_coupon)) {
// end //
$coupon = $row_orders_coupon["value"];

}

// --------------------	QUERIES 6  ------------------------------------//
//Orders_Residential Del Fee (Giftwrap)
$orders_residential_fee = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_giftwrap' and orders_id = " . $Orders_id);
//$row_orders_residential_fee = tep_db_fetch_array($orders_residential_fee);
while($row_orders_residential_fee = mysql_fetch_array($orders_residential_fee)) {
// end //
$Small_Order_Fee = $row_orders_residential_fee["value"];
}
////////////////////////////////////
$Discount_Rate = "";
$Discount_Message  = "";
$CODAmount  = "";
// --------------------	QUERIES 7  ------------------------------------//
//Orders_Total Gesamtbetrag der Bestellung wird noch nicht gebraucht
$orders_total = tep_db_query("select value from " . TABLE_ORDERS_TOTAL . "
where class = 'ot_total' and orders_id = " . $Orders_id);
//$row_orders_total = tep_db_fetch_array($orders_total);
while($row_orders_total = mysql_fetch_array($orders_total)) {
// end //
$Order_Grand_Total = $row_orders_total["value"];
}
// --------------------	QUERIES 9  ------------------------------------//
//Kundendaten wie Name, Faxnummer und Refferer
$customers = tep_db_query("select customers_gender, customers_firstname, customers_lastname, customers_fax from " . TABLE_CUSTOMERS . "
where customers_id = " . $customers_id);
//$row_customers = tep_db_fetch_array($customers);
while($row_customers = mysql_fetch_array($customers)) {
// end //
$fax = $row_customers["customers_fax"];
$gender = $row_customers["customers_gender"];
$kvorname = $row_customers["customers_firstname"];
$knachname = $row_customers["customers_lastname"];
}

// --------------------	QUERIES 10  ------------------------------------//
//Products COunt
$orders_count = tep_db_query("select count(products_quantity) as o_count from " . TABLE_ORDERS_PRODUCTS . "
where orders_id = " . $Orders_id);
//$row_orders_total = tep_db_fetch_array($orders_total);
while($row_orders_count = mysql_fetch_array($orders_count)) {
// end //
$Number_of_Items = $row_orders_count[10]; // used array to show the number of items ordered
}
//
$Shipping_Weight = "";
$Coupon_Code = "";
$Order_security_msg = "";
$Order_Surcharge_Amount = "";
$Order_Surcharge_Something = "";
$Affiliate_code = "";
$Sentiment_message = "";
$Checkout_form_type = "";
$future1  = "";
$future2 = "";
$future3 = "";
$future4 = "";
$future5 = "";
$future6 = "";
$future7 = "";
$future8 = "";
$future9 = "";
// csv settings
$CSV_SEPARATOR = ";";
$CSV_NEWLINE = "\r\n";

$csv_output .= $Orders_id . $delim;
//$csv_output .= $orders_products_id . $delim;
$csv_output .= $Date . $delim;
//$csv_output .= $Time . $delim;
$csv_output .= $customers_id . $delim;
$csv_output .= $Company . $delim;
$csv_output .= $Billing_Phone . $delim;
//$csv_output .= $gender . $delim;
$csv_output .= $knachname . $delim;
$csv_output .= $kvorname . $delim;
$csv_output .= $email . $delim;
$csv_output .= $Billing_Address_1 . $delim;
$csv_output .= $Billing_Address_2 . $delim;
$csv_output .= $Billing_City . $delim;
$csv_output .= $Billing_State . $delim;
$csv_output .= $Billing_Zip . $delim;
$csv_output .= $Billing_Country . $delim;
$csv_output .= $ShipTo_Name . $delim;
//$csv_output .= $fax . $delim;
//$csv_output .= $ShipTo_First_Name . $delim;
//$csv_output .= $ShipTo_Last_Name . $delim;
//$csv_output .= $ShipTo_Company . $delim;
$csv_output .= $ShipTo_Address_1 . $delim;
$csv_output .= $ShipTo_Address_2 . $delim;
$csv_output .= $ShipTo_City . $delim;
$csv_output .= $ShipTo_State . $delim;
$csv_output .= $ShipTo_Zip . $delim;
$csv_output .= $ShipTo_Country . $delim;
$csv_output .= $ShipTo_Phone . $delim;

$csv_output .= $Number_of_Items . $delim;
$csv_output .= $Shipping_Weight . $delim;
$csv_output .= $Order_Subtotal . $delim;
$csv_output .= $Order_Shipping_Total . $delim;
$csv_output .= $Shipping_Method . $delim;
$csv_output .= $payment . $delim;
$csv_output .= $Card_Type . $delim;
$csv_output .= $orders_status . $delim;

$csv_output .= $products_id . $delim;
$csv_output .= $products_model . $delim;
$csv_output .= $products_quantity . $delim;
$csv_output .= $item_weight . $delim;
$csv_output .= $item_attribute . $delim;
$csv_output .= $item_Price . $delim;
$csv_output .= $Order_Grand_Total . $delim;
$csv_output .= $total_quantity . $delim;
$csv_output .= $Comments . $delim;

//$csv_output .= $Card_Number . $delim;
//$csv_output .= $Exp_Date . $delim;
//$csv_output .= $Bank_Name . $delim;
//$csv_output .= $Gateway . $delim;
//$csv_output .= $AVS_Code . $delim;
//$csv_output .= $Transaction_ID . $delim;
//$csv_output .= $Order_Special_Notes . $delim;
//$csv_output .= $Order_Tax . $delim;
//$csv_output .= $Order_Insurance . $delim;
//$csv_output .= $Tax_Exempt_Message . $delim;
//$csv_output .= $nn_gebuehr . $delim;
//$csv_output .= $minderwert . $delim;
//$csv_output .= $coupon . $delim;
//$csv_output .= $Small_Order_Fee . $delim;
//$csv_output .= $Discount_Rate . $delim;
//$csv_output .= $Discount_Message . $delim;
//$csv_output .= $CODAmount . $delim;
//$csv_output .= $Coupon_Code . $delim;
//$csv_output .= $Order_security_msg . $delim;
//$csv_output .= $Order_Surcharge_Amount . $delim;
//$csv_output .= $Order_Surcharge_Something . $delim;
//$csv_output .= $Affiliate_code . $delim;
//$csv_output .= $Sentiment_message . $delim;
//$csv_output .= $Checkout_form_type . $delim;

// --------------------	QUERIES 9  ------------------------------------//
//Get list of products ordered
$orders_products = tep_db_query("select products_model, products_price, products_quantity, products_name, products_id, orders_products_id from " . TABLE_ORDERS_PRODUCTS . "
where orders_id = " . $Orders_id);

// While loop to list the item

while($row_orders_products = mysql_fetch_array($orders_products)) {
//$csv_output .= filter_text($row_orders_products[0]) . $delim;
//$csv_output .= $row_orders_products[1] . $delim;
//$csv_output .= $row_orders_products[2] . $delim;
//$csv_output .= filter_text($row_orders_products[3]) . $delim;

$products_model = $row_orders_products["products_model"];
$products_id = $row_orders_products["products_id"];
$products_quantity = $row_orders_products["products_quantity"];
$products_price = $row_orders_products["products_price"];
//$orders_products_id = $row_orders_products["orders_products_id"];


} // end while loop for products

// --------------------------------------------------------------------------//
$csv_output .= "\n";
} // while loop main first


/*
If you want to see the output in the browser you can replace 
the text inside  //BOF OUTPUT - //EOF OUTPUT
with :
echo $csv_output;
exit;
*/

//BOF OUTPUT
header("Content-Type: application/force-download\n");
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
header("Content-Disposition: attachment; filename=orders_" . date("dmY") . ".csv");
print $csv_output;
exit;
//EOF OUTPUT
}//function main

function filter_text($text) {
$filter_array = array(",","\r","\n","\t");
return str_replace($filter_array,"",$text);
} // function for the filter
?>

export orders into CSV problem seeking solution

Recommended Posts

sw0857

Archived

Browse

Activity