Jump to content


Corporate Sponsors


Latest News: (loading..)

- - - - -

Export orders into csv

Started by dlan, Sep 04 2007, 10:05

99 replies to this topic

#21 serverguy

  • Community Member
  • 9 posts
  • Real Name:Jim Rogers
  • Location:Virginia, USA

Posted 06 December 2007, 01:42

View Postpeter_of_stirling, on Dec 3 2007, 11:37 PM, said:

Hi David,

I am still trying to make your efforts work.

I have even installed it on to another clean domain without any success!

Conversely, the latest contribution of export customers worked without me needing to do any changes apart from those recommended.

Admittedly, that requires far less filtering than the full extraction of orders.

I note that with that code it starts with if (!_POST['submit']) instead of if (!$submitted || $submitted !=1)
and concludes with :-
header("Content-Type: application/force-download\n");
header("Content-Disposition: attachment; filename=exportsname_" . date("Ymd") . ".txt");
header("Pragma: no-cache");
header("Expires: 0");
echo $csv_output;
die();
}
require(DIR_WS_INCLUDES . 'application_bottom.php');
//function main
?>

I have tried applying some of these different ideas to your code - but still without success.

Any further ideas as to why I can not make it work will be appreciated!!

Regards,
peter_of_stirling
I added this:
$submitted = $HTTP_POST_VARS['submitted'];
before:
if (!$submitted || $submitted != 1)
and now it processes, but the output is directly to the internet explorer screen. Along with that each order prints about 3 or 4 times into the window which makes for a big mess.

If I can't get it working it's on to searching for another.

#22 leveera

  • Community Member
  • 200 posts
  • Real Name:Sergei

Posted 17 December 2007, 09:01

Please advice, I'm looking for cont. to allow customers to get a copy of their orders in CSV. Is it a right one contrubution?

Thanks
Sergei

#23 bongo

  • Community Member
  • 147 posts
  • Real Name:Thomas Borge

Posted 01 January 2008, 15:20

I have installed the Export all data to csv file version 2.2 and this works just fine. Can export orders and change what i should export.

But i have one question about it, when i export it i get all in one row.
Sow if the customer has 5 products on their order i get this on one line.

Does anyone of you now how i can make the export make a new line for each product on the order?

like this
Ordernumber Productid
1 120020
1 120021
1 120022

Hope that someone can help me with this.

And also a happy new year to everyone

-Thomas

#24 razzer10

  • Community Member
  • 20 posts
  • Real Name:Kevin

Posted 26 January 2008, 22:16

Ok, now I tried installing this contrib and am having problems getting it to download to my desktop as well. I used the most recent script and installed all thepatches. I am using RC2, don't know if that could be the problem? Looks like it works though, doesn't do anything weird, just seems the generate csv is not working...

#25 janetgot

  • Community Member
  • 149 posts
  • Real Name:Janet
  • Gender:Female
  • Location:Boston, MA

Posted 28 January 2008, 14:44

Hi, I'm hoping to get this contribution to work, however, when I use it, it only exports the customer contact information, and none of the order data (products info) is saved to the csv file. Here is my exportorders.php file. Any ideas? 

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

  osCommerce, Open Source E-Commerce Solutions
  [url="http://www.oscommerce.com"]http://www.oscommerce.com[/url]

  Copyright © 2004 Oscommerce

  Use this module on your own risk. I will be updating a new one soon. This template is used to create
  the csv export for Ideal Computer Systems Accounting Software
*/

  define('FILENAME_EXPORTORDERS', 'exportorders.php');


require('includes/application_top.php'); 
require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_EXPORTORDERS);

// 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 HEADING_TITLE; ?></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><?php echo tep_draw_form('exportorders', FILENAME_EXPORTORDERS); ?>

                    <table border="0" style="font-family:tahoma;font-size:11px;" cellpadding="3">
                      <tr>
                        <td><?php echo INPUT_START; ?></td>
                        <td><!-- input name="start" size="5" value="<?php echo $start; ?>"> -->
                          <?php
    	                    $orders_list_query = tep_db_query("SELECT orders_id, date_purchased FROM orders ORDER BY orders_id");
   							$orders_list_array = array();
							$orders_list_array[] = array('id' => '', 'text' => '---');
   						    while ($orders_list = tep_db_fetch_array($orders_list_query)) {
   					        $orders_list_array[] = array('id' => $orders_list['orders_id'],
                                       'text' => $orders_list['orders_id']." - ".tep_date_short($orders_list['date_purchased']));
							}  

							echo '&nbsp;&nbsp;' . tep_draw_pull_down_menu('start', $orders_list_array, (isset($_GET['orders_id']) ? $_GET['orders_id'] : ''), 'size="1"') . '&nbsp;&nbsp;&nbsp;';

						?></td>
                      </tr>
                      <tr>
                        <td><?php echo INPUT_END; ?></td>
                        <td><!-- <input name="end" size="5" value="<?php echo $end; ?>"> -->
                          <?php 
						echo '&nbsp;&nbsp;' . tep_draw_pull_down_menu('end', $orders_list_array, (isset($_GET['orders_id']) ? $_GET['orders_id'] : ''), 'size="1"') . '&nbsp;&nbsp;&nbsp;';
						?></td>
                      </tr>
                      <tr>
                        <td>&nbsp;</td>
                        <td><input type="submit" value="<?php echo INPUT_VALID; ?>"></td>
                      </tr>
                    </table>
                    <input type="hidden" name="submitted" value="1">
                  </form></td>
              </tr>
              <tr>
                <td><?php echo INPUT_DESC; ?></td>
              </tr>
              <tr>
                <td>&nbsp;</td>
              </tr>
              <tr>
                <td>&nbsp;</td>
              </tr>
            </table></td>
        </tr>
      </table></td>
  </tr>
</table>
<!-- 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);
}

// generates csv file from $start order to $end order, inclusive
function generatecsv($start, $end)
{

// Patch dlan
// if both fields are empty we select all orders
if ($start=="" && $end=="") {
 $orders = tep_db_query("SELECT 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 
FROM orders ORDER BY orders_id"); 
// if $start is empty we select all orders up to $end
} else if($start=="" && $end!="") {
 $orders = tep_db_query("SELECT 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 
FROM orders WHERE orders_id <= $end ORDER BY orders_id"); 
// if $end is empty we select all orders from $start
} else if($start!="" && $end=="") {
 $orders = tep_db_query("SELECT 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 
FROM orders WHERE orders_id >= $start ORDER BY orders_id");
// if both fields are filed in we select orders betwenn $start and $end
} else {
 $orders = tep_db_query("SELECT 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 
FROM orders WHERE orders_id >= $start AND orders_id <= $end ORDER BY orders_id");
}
//patch

//$csv_output ="\n";
while ($row_orders = mysql_fetch_array($orders)) { //start one loop
 
$Orders_id = $row_orders["orders_id"];
$Date1 = $row_orders["date_purchased"];
//list($Date, $Time) = explode (' ',$Date1);
$Date = date('m/d/Y', 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"]);
$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 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 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
$orders_tax = tep_db_query("select value from 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 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 5  ------------------------------------//
//Orders_Shipping
$orders_shipping = tep_db_query("select title, value from 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 6  ------------------------------------//
//Orders_Residential Del Fee (Giftwrap)
$orders_residential_fee = tep_db_query("select value from 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
$orders_total = tep_db_query("select value from 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 8  ------------------------------------//
//Products COunt
$orders_count = tep_db_query("select count(products_quantity) as o_count from 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[0]; // 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 = "";
$Card_CVV_value = $row_orders["cvvnumber"];
$future1  = "";
$future2 = "";
$future3 = "";
$future4 = "";
$future5 = "";
$future6 = "";
$future7 = "";
$future8 = "";
$future9 = "";
// csv settings
$CSV_SEPARATOR = ",";
$CSV_NEWLINE = "\r\n";
$csv_output .= $Orders_id . "," ;
$csv_output .= $Date . "," ;
$csv_output .= $Time . "," ;
$csv_output .= $First_Name . "," ;
$csv_output .= $Last_Name . "," ;
$csv_output .= $Name_On_Card . "," ;
$csv_output .= $Company . "," ;
$csv_output .= $email . "," ;
$csv_output .= $Billing_Address_1 . "," ;
$csv_output .= $Billing_Address_2 . "," ;
$csv_output .= $Billing_City . "," ;
$csv_output .= $Billing_State . "," ;
$csv_output .= $Billing_Zip . "," ;
$csv_output .= $Billing_Country . "," ;
$csv_output .= $Billing_Phone . "," ;
$csv_output .= $ShipTo_First_Name . "," ;
$csv_output .= $ShipTo_Last_Name . "," ;
$csv_output .= $ShipTo_Name . "," ;
$csv_output .= $ShipTo_Company . "," ;
$csv_output .= $ShipTo_Address_1 . "," ;
$csv_output .= $ShipTo_Address_2 . "," ;
$csv_output .= $ShipTo_City . "," ;
$csv_output .= $ShipTo_State . "," ;
$csv_output .= $ShipTo_Zip . "," ;
$csv_output .= $ShipTo_Country . "," ;
$csv_output .= $ShipTo_Phone . "," ;
$csv_output .= $Card_Type . "," ;
$csv_output .= $Card_Number . "," ;
$csv_output .= $Exp_Date . "," ;
$csv_output .= $Bank_Name . "," ;
$csv_output .= $Gateway . "," ;
$csv_output .= $AVS_Code . "," ;
$csv_output .= $Transaction_ID . "," ;
$csv_output .= $Order_Special_Notes . "," ;
$csv_output .= $Comments . "," ;
$csv_output .= $Order_Subtotal . "," ;
$csv_output .= $Order_Tax . "," ;
$csv_output .= $Order_Insurance . "," ;
$csv_output .= $Tax_Exempt_Message . "," ;
$csv_output .= $Order_Shipping_Total . "," ;
$csv_output .= $Small_Order_Fee . "," ;
$csv_output .= $Discount_Rate . "," ;
$csv_output .= $Discount_Message . "," ;
$csv_output .= $CODAmount . "," ;
$csv_output .= $Order_Grand_Total . "," ;
$csv_output .= $Number_of_Items . "," ;
$csv_output .= $Shipping_Method . "," ;
$csv_output .= $Shipping_Weight . "," ;
$csv_output .= $Coupon_Code . "," ;
$csv_output .= $Order_security_msg . "," ;
$csv_output .= $Order_Surcharge_Amount . "," ;
$csv_output .= $Order_Surcharge_Something . "," ;
$csv_output .= $Affiliate_code . "," ;
$csv_output .= $Sentiment_message . "," ;
$csv_output .= $Checkout_form_type . "," ;
$csv_output .= $Card_CVV_value . "," ;
$csv_output .= $future1 . "," ;
$csv_output .= $future2 . "," ;
$csv_output .= $future3 . "," ;
$csv_output .= $future4 . "," ;
$csv_output .= $future5 . "," ;
$csv_output .= $future6 . "," ;
$csv_output .= $future7 . "," ;
$csv_output .= $future8 . "," ;
$csv_output .= $future9 ;
// --------------------    QUERIES 9  ------------------------------------//
//Get list of products ordered
$orders_products = tep_db_query("select products_model, products_price, products_quantity, products_name from orders_products
where orders_id = " . $Orders_id);

// While loop to list the item

while($row_orders_products = mysql_fetch_array($orders_products)) {
$csv_output .= "," . "BEGIN_ITEM". "," ;
$csv_output .= filter_text($row_orders_products[0]) . "," ;
$csv_output .= $row_orders_products[1] . "," ;
$csv_output .= $row_orders_products[2] . "," ;
$csv_output .= filter_text($row_orders_products[3]) . "," ;
$csv_output .= "END_ITEM";

} // end while loop for products

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

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

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

#26 stunter

  • Community Member
  • 1 posts
  • Real Name:Matt

Posted 29 January 2008, 02:10

View Postbongo, on Jan 1 2008, 10:20 AM, said:

I have installed the Export all data to csv file version 2.2 and this works just fine. Can export orders and change what i should export.

But i have one question about it, when i export it i get all in one row.
Sow if the customer has 5 products on their order i get this on one line.

Does anyone of you now how i can make the export make a new line for each product on the order?

like this
Ordernumber Productid
1 120020
1 120021
1 120022

Hope that someone can help me with this.

And also a happy new year to everyone

-Thomas


This is the exact issue that I have been struggling with. This contribution only seems to work if there is only one product ordered at a time, which doesn't help. Someone please help us with this. Thanks!

#27 eppie13

  • Community Member
  • 3 posts
  • Real Name:Esther

Posted 26 February 2008, 15:07

I would like to put the invoice number also in the csv file. I put this: $csv_output .= $Num_invoice . "," ; in the CSV settings but I think I also have to make a query? Can you help me out?

Kind regards,
Esther

#28 stubbsy

  • Community Member
  • 537 posts
  • Real Name:dave stubbs
  • Gender:Male
  • Location:Lyme Regis

Posted 28 February 2008, 12:24

Hi there,

I've been using the contribution for some time now, I use it every day to export data to upload to parcelforce to print dispatch labels.

I was wondering if it would be possible to have the option be able to export a string of orders, not neccessarily in order, eg 1,3,10,11,19,40 etc rather than a range.

Any ideas how this could be acheived?

Thanks

Dave

#29 dlan

  • Community Member
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 03 March 2008, 13:23

View Postoojacoboo, on Nov 30 2007, 09:51 PM, said:

I'm sorry, I don't see that statement in the exportorders.php file. Would you mind telling me what it says, or the line number? Maybe I have the wrong contribution file?

I see 9 Queries
- Order Comments
- Order Sub-Total
- Tax
- Insurance
- Shipping
- Giftwrap
- Order Total
- Product Count
- List of Products Ordered

I just don't see anything on here to filter out by Order Staus. I only want to export out Orders that have been paid for, via paypal, and OSC logs all orders even if the customer doesn't complete the order process...

Thanks!


Hi,

Sorry for the long reply I haven't been around for a while.
The orderby statement should be placed at the end of your sql query.
Here is a simple example :
SELECT * FROM tablenamegoeshere ORDERBY fieldnamegoeshere
So in the files, you have queries that are selecting the orders, simply add ORDERBY followed by the field you want to sort between quotes.

#30 dlan

  • Community Member
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 03 March 2008, 13:26

View Postleveera, on Dec 17 2007, 10:01 AM, said:

Please advice, I'm looking for cont. to allow customers to get a copy of their orders in CSV. Is it a right one contrubution?

Thanks
Sergei

Hi,

Not just as is but you can modify the contribution to get what you want. This would be like creating a little new contrib adding a customer field in order to select the customer name and then modifying the query so it selects only the orders of this selected customer.

#31 dlan

  • Community Member
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 03 March 2008, 13:38

View Postbongo, on Jan 1 2008, 04:20 PM, said:

I have installed the Export all data to csv file version 2.2 and this works just fine. Can export orders and change what i should export.

But i have one question about it, when i export it i get all in one row.
Sow if the customer has 5 products on their order i get this on one line.

Does anyone of you now how i can make the export make a new line for each product on the order?

like this
Ordernumber Productid
1 120020
1 120021
1 120022

Hope that someone can help me with this.

And also a happy new year to everyone

-Thomas


Hi,

Yes this is normal as this is exporting the total of the order.
If you want to get the product lines, you will need to modify the queries and add a join with the product table and possibly the customers table to get the product id, ...

#32 dlan

  • Community Member
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 03 March 2008, 13:42

View Postjanetgot, on Jan 28 2008, 03:44 PM, said:

Hi, I'm hoping to get this contribution to work, however, when I use it, it only exports the customer contact information, and none of the order data (products info) is saved to the csv file. Here is my exportorders.php file. Any ideas? 

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

  osCommerce, Open Source E-Commerce Solutions
  [url="http://www.oscommerce.com"]http://www.oscommerce.com[/url]

  Copyright © 2004 Oscommerce

  Use this module on your own risk. I will be updating a new one soon. This template is used to create
  the csv export for Ideal Computer Systems Accounting Software
*/

  define('FILENAME_EXPORTORDERS', 'exportorders.php');
require('includes/application_top.php'); 
require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_EXPORTORDERS);

// 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 HEADING_TITLE; ?></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><?php echo tep_draw_form('exportorders', FILENAME_EXPORTORDERS); ?>

                    <table border="0" style="font-family:tahoma;font-size:11px;" cellpadding="3">
                      <tr>
                        <td><?php echo INPUT_START; ?></td>
                        <td><!-- input name="start" size="5" value="<?php echo $start; ?>"> -->
                          <?php
    	                    $orders_list_query = tep_db_query("SELECT orders_id, date_purchased FROM orders ORDER BY orders_id");
   							$orders_list_array = array();
							$orders_list_array[] = array('id' => '', 'text' => '---');
   						    while ($orders_list = tep_db_fetch_array($orders_list_query)) {
   					        $orders_list_array[] = array('id' => $orders_list['orders_id'],
                                       'text' => $orders_list['orders_id']." - ".tep_date_short($orders_list['date_purchased']));
							}  

							echo '&nbsp;&nbsp;' . tep_draw_pull_down_menu('start', $orders_list_array, (isset($_GET['orders_id']) ? $_GET['orders_id'] : ''), 'size="1"') . '&nbsp;&nbsp;&nbsp;';

						?></td>
                      </tr>
                      <tr>
                        <td><?php echo INPUT_END; ?></td>
                        <td><!-- <input name="end" size="5" value="<?php echo $end; ?>"> -->
                          <?php 
						echo '&nbsp;&nbsp;' . tep_draw_pull_down_menu('end', $orders_list_array, (isset($_GET['orders_id']) ? $_GET['orders_id'] : ''), 'size="1"') . '&nbsp;&nbsp;&nbsp;';
						?></td>
                      </tr>
                      <tr>
                        <td>&nbsp;</td>
                        <td><input type="submit" value="<?php echo INPUT_VALID; ?>"></td>
                      </tr>
                    </table>
                    <input type="hidden" name="submitted" value="1">
                  </form></td>
              </tr>
              <tr>
                <td><?php echo INPUT_DESC; ?></td>
              </tr>
              <tr>
                <td>&nbsp;</td>
              </tr>
              <tr>
                <td>&nbsp;</td>
              </tr>
            </table></td>
        </tr>
      </table></td>
  </tr>
</table>
<!-- 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);
}

// generates csv file from $start order to $end order, inclusive
function generatecsv($start, $end)
{

// Patch dlan
// if both fields are empty we select all orders
if ($start=="" && $end=="") {
 $orders = tep_db_query("SELECT 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 
FROM orders ORDER BY orders_id"); 
// if $start is empty we select all orders up to $end
} else if($start=="" && $end!="") {
 $orders = tep_db_query("SELECT 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 
FROM orders WHERE orders_id <= $end ORDER BY orders_id"); 
// if $end is empty we select all orders from $start
} else if($start!="" && $end=="") {
 $orders = tep_db_query("SELECT 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 
FROM orders WHERE orders_id >= $start ORDER BY orders_id");
// if both fields are filed in we select orders betwenn $start and $end
} else {
 $orders = tep_db_query("SELECT 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 
FROM orders WHERE orders_id >= $start AND orders_id <= $end ORDER BY orders_id");
}
//patch

//$csv_output ="\n";
while ($row_orders = mysql_fetch_array($orders)) { //start one loop
 
$Orders_id = $row_orders["orders_id"];
$Date1 = $row_orders["date_purchased"];
//list($Date, $Time) = explode (' ',$Date1);
$Date = date('m/d/Y', 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"]);
$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 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 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
$orders_tax = tep_db_query("select value from 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 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 5  ------------------------------------//
//Orders_Shipping
$orders_shipping = tep_db_query("select title, value from 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 6  ------------------------------------//
//Orders_Residential Del Fee (Giftwrap)
$orders_residential_fee = tep_db_query("select value from 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
$orders_total = tep_db_query("select value from 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 8  ------------------------------------//
//Products COunt
$orders_count = tep_db_query("select count(products_quantity) as o_count from 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[0]; // 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 = "";
$Card_CVV_value = $row_orders["cvvnumber"];
$future1  = "";
$future2 = "";
$future3 = "";
$future4 = "";
$future5 = "";
$future6 = "";
$future7 = "";
$future8 = "";
$future9 = "";
// csv settings
$CSV_SEPARATOR = ",";
$CSV_NEWLINE = "\r\n";
$csv_output .= $Orders_id . "," ;
$csv_output .= $Date . "," ;
$csv_output .= $Time . "," ;
$csv_output .= $First_Name . "," ;
$csv_output .= $Last_Name . "," ;
$csv_output .= $Name_On_Card . "," ;
$csv_output .= $Company . "," ;
$csv_output .= $email . "," ;
$csv_output .= $Billing_Address_1 . "," ;
$csv_output .= $Billing_Address_2 . "," ;
$csv_output .= $Billing_City . "," ;
$csv_output .= $Billing_State . "," ;
$csv_output .= $Billing_Zip . "," ;
$csv_output .= $Billing_Country . "," ;
$csv_output .= $Billing_Phone . "," ;
$csv_output .= $ShipTo_First_Name . "," ;
$csv_output .= $ShipTo_Last_Name . "," ;
$csv_output .= $ShipTo_Name . "," ;
$csv_output .= $ShipTo_Company . "," ;
$csv_output .= $ShipTo_Address_1 . "," ;
$csv_output .= $ShipTo_Address_2 . "," ;
$csv_output .= $ShipTo_City . "," ;
$csv_output .= $ShipTo_State . "," ;
$csv_output .= $ShipTo_Zip . "," ;
$csv_output .= $ShipTo_Country . "," ;
$csv_output .= $ShipTo_Phone . "," ;
$csv_output .= $Card_Type . "," ;
$csv_output .= $Card_Number . "," ;
$csv_output .= $Exp_Date . "," ;
$csv_output .= $Bank_Name . "," ;
$csv_output .= $Gateway . "," ;
$csv_output .= $AVS_Code . "," ;
$csv_output .= $Transaction_ID . "," ;
$csv_output .= $Order_Special_Notes . "," ;
$csv_output .= $Comments . "," ;
$csv_output .= $Order_Subtotal . "," ;
$csv_output .= $Order_Tax . "," ;
$csv_output .= $Order_Insurance . "," ;
$csv_output .= $Tax_Exempt_Message . "," ;
$csv_output .= $Order_Shipping_Total . "," ;
$csv_output .= $Small_Order_Fee . "," ;
$csv_output .= $Discount_Rate . "," ;
$csv_output .= $Discount_Message . "," ;
$csv_output .= $CODAmount . "," ;
$csv_output .= $Order_Grand_Total . "," ;
$csv_output .= $Number_of_Items . "," ;
$csv_output .= $Shipping_Method . "," ;
$csv_output .= $Shipping_Weight . "," ;
$csv_output .= $Coupon_Code . "," ;
$csv_output .= $Order_security_msg . "," ;
$csv_output .= $Order_Surcharge_Amount . "," ;
$csv_output .= $Order_Surcharge_Something . "," ;
$csv_output .= $Affiliate_code . "," ;
$csv_output .= $Sentiment_message . "," ;
$csv_output .= $Checkout_form_type . "," ;
$csv_output .= $Card_CVV_value . "," ;
$csv_output .= $future1 . "," ;
$csv_output .= $future2 . "," ;
$csv_output .= $future3 . "," ;
$csv_output .= $future4 . "," ;
$csv_output .= $future5 . "," ;
$csv_output .= $future6 . "," ;
$csv_output .= $future7 . "," ;
$csv_output .= $future8 . "," ;
$csv_output .= $future9 ;
// --------------------    QUERIES 9  ------------------------------------//
//Get list of products ordered
$orders_products = tep_db_query("select products_model, products_price, products_quantity, products_name from orders_products
where orders_id = " . $Orders_id);

// While loop to list the item

while($row_orders_products = mysql_fetch_array($orders_products)) {
$csv_output .= "," . "BEGIN_ITEM". "," ;
$csv_output .= filter_text($row_orders_products[0]) . "," ;
$csv_output .= $row_orders_products[1] . "," ;
$csv_output .= $row_orders_products[2] . "," ;
$csv_output .= filter_text($row_orders_products[3]) . "," ;
$csv_output .= "END_ITEM";

} // end while loop for products

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

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

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

Hi,

If you want to export other data, you need to modify the queries within the file (SELECT ...) and play with SQL to get the data you need.

#33 dlan

  • Community Member
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 03 March 2008, 13:44

View Poststunter, on Jan 29 2008, 03:10 AM, said:

This is the exact issue that I have been struggling with. This contribution only seems to work if there is only one product ordered at a time, which doesn't help. Someone please help us with this. Thanks!


Hi,

those are two different things. If you want the detail of the order then yes, you need to modify the queries in order to extract the data you like. The way it is done so far is that it exports the total of the order so if you have two products within it, you will get only one line with the total amount for the two products.

#34 dlan

  • Community Member
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 03 March 2008, 13:47

View Posteppie13, on Feb 26 2008, 04:07 PM, said:

I would like to put the invoice number also in the csv file. I put this: $csv_output .= $Num_invoice . "," ; in the CSV settings but I think I also have to make a query? Can you help me out?

Kind regards,
Esther

Hi,

Yes you need to select the corresponding invoice number in the export orders query. I have not looked into it so I don't know if the invoice number is in the table we are querying here. If yes, simply add the name of the field within the query, if not then you might have to make a join.

#35 dlan

  • Community Member
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 03 March 2008, 13:53

View Poststubbsy, on Feb 28 2008, 01:24 PM, said:

Hi there,

I've been using the contribution for some time now, I use it every day to export data to upload to parcelforce to print dispatch labels.

I was wondering if it would be possible to have the option be able to export a string of orders, not neccessarily in order, eg 1,3,10,11,19,40 etc rather than a range.

Any ideas how this could be acheived?

Thanks

Dave

Hi,

sure thing, you can barely do whatever you like :-) You can change the queries that are in the exportorders.php file and even make another little contrib so it exports exactly what you want.

#36 dlan

  • Community Member
  • 16 posts
  • Real Name:dlan
  • Gender:Male
  • Location:France

Posted 03 March 2008, 14:10

View Postdlan, on Mar 3 2008, 02:23 PM, said:

Hi,

Sorry for the long reply I haven't been around for a while.
The orderby statement should be placed at the end of your sql query.
Here is a simple example :
SELECT * FROM tablenamegoeshere ORDERBY fieldnamegoeshere
So in the files, you have queries that are selecting the orders, simply add ORDERBY followed by the field you want to sort between quotes.

Oups ... no quotes sorry and it is being spellet ORDER BY (space between order and by) :-)

#37 lambro

  • Community Member
  • 32 posts
  • Real Name:Peter

Posted 03 March 2008, 23:34

I seem to be having problems with long numbers, for example telephone and credit card numbers. A phone number comes out as 2.09E+09 in the excel box, but when you high light it it reads 2086405970 in the formula bar (this is nearly correct, it is missing the leading 0).

#38 sps-merlin

  • Community Member
  • 19 posts
  • Real Name:Pete Gleadall

Posted 07 March 2008, 17:42

excellent contrib. i'm needing to alter the order of how things display in the exported .csv file. i have a pretty good idea of what i need to do in terms of the php and can join tables if i need to but i don't know where in the .php file i need to be looking for that aspect of the script. could anyone please advise? i'm using the latest downloadable version

#39 steven78

  • Community Member
  • 19 posts
  • Real Name:Steven Jobs

Posted 14 March 2008, 14:24

Who can help me? This script was working well; and after a change to another server this great contribution isn't working anymore.

I have RC1

I can use the contrib but after pressing 'Generate' nothing is happening; only the url gives: export_orders_csv.php?start=800&end=850&status=&submitted=1

Does anyone have the same problem?

#40 sps-merlin

  • Community Member
  • 19 posts
  • Real Name:Pete Gleadall

Posted 17 March 2008, 17:04

can anyone offer any help for altering the order in which the fields are displayed in .csv export. i would imagine this is easy enough but i don't know where in the php file to make the appropriate alterations
Back to Order Total Modules · Next Unread Topic →


  1. osCommerce Support Forum
  2. → osCommerce Online Merchant v2.x
  3. → Add-Ons
  4. → Order Total Modules
  5. Forum Rules