Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Export orders into csv


dlan

Recommended Posts

  • 2 months later...

Hi @@All,

 

the last posting is some month ago, but actual I'm also intersted how to export the "iso_code_3" from table "countries" into the csv-file.

 

I already tried to modifiy one of the queries in the exportorders.php to:

   // --------------------    QUERIES 0  ------------------------------------//
   //COUNTRIES nach Iso_Code_3 durchsuchen
$countries = tep_db_query("select countries_iso_code_3 from " . TABLE_COUNTRIES . "
where countries_name = " . $Land);
   //$row_orders_status_history = tep_db_fetch_array($comments);
while($row_countries = mysql_fetch_array($countries)) {
 // end //
$Land1 = filter_text($row_countries['countries_iso_code_3']);
}

 

"$Land" is defined in this line:

 

$Land = str_replace("(48 Contiguous Sta", "", $row_orders["delivery_country"]);

 

For my undersatanding it might be correct and should work, but whan I click to the export button I get the error:

 

1054 - Unknown column 'Deutschland' in 'where clause'

select countries_iso_code_3 from countries where countries_name = Deutschland

[TEP STOP]

 

Has anybody an idea, where I made the mistake?

 

Many thanks in advance

 

Best regards from Germany

Michael

Link to comment
Share on other sites

  • 4 months later...

I've added the export order function to osCommerce for integration withGolden Inventory System. I put the small php script in the folder "catalog" for creation a xml stream with last orders. Then, the inventory system connects to the php file and reads the xml stream. The program downloads the sales ordrers in the inventory database. It maybe MS SQL server Express Edition or MS Access database file. After that, I can create invoices, work orders and print different documents and reports in the desktop program.

 

?????

Edited by burt
remove quoted link
Link to comment
Share on other sites

  • 2 years later...
  • 3 months later...

New 2.3.4 install, same issue here

Just getting headers on csv file

 

Anyone got this to work yet?

 

I've just installed http://addons.oscommerce.com/info/8307/v,23

 

I was hoping this would solve my problems with this add on (empty csv files) but this version also just gives me an empty csv files (no errors) any idea's ?

Getting the Phoenix off the ground

Link to comment
Share on other sites

Fixed.

Find all instances of mysql_fetch_array

replace with tep_db_fetch_array  :thumbsup:

 

New 2.3.4 install, same issue here

Just getting headers on csv file

 

Anyone got this to work yet?

Getting the Phoenix off the ground

Link to comment
Share on other sites

  • 1 month later...

Hello, I tried this under 2.3.4 with an XML export and the CSV export. For XML I get nothing. For CSV, I get headers. I have modified the code various ways and no luck. Seems like it is not returning records from the database. Also the CSV with the dropdown (bad idea when you have 50K plus orders.

 

Any ideas on how to test? Tried ECHO and see nothing also. 2.3.4 working great otherwise.

 

I really need a way to pull out orders. I can't seem to find anything new on this. Does anyone need to upload their orders into another system?

 

Thanks,

 

Jeff

Link to comment
Share on other sites

This works for me. I changed the <?php require(DIR_WS_INCLUDES . 'header.php'); ?> to <?php require(DIR_WS_INCLUDES . 'template_top.php'); ?> and I removed the colum_left as well. I needed to for an add on I have.

 

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

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

  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 (!$_GET['submitted'])
{
?>
<!-- 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 . 'template_top.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">

      </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><form method="GET" action="<?php echo $PHP_SELF; ?>">
                    <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 '  ' . tep_draw_pull_down_menu('start', $orders_list_array, (isset($_GET['orders_id']) ? $_GET['orders_id'] : ''), 'size="1"') . '   ';

                        ?></td>
                      </tr>
                      <tr>
                        <td><?php echo INPUT_END; ?></td>
                        <td><!-- <input name="end" size="5" value="<?php echo $end; ?>"> -->
                          <?php
                        echo '  ' . tep_draw_pull_down_menu('end', $orders_list_array, (isset($_GET['orders_id']) ? $_GET['orders_id'] : ''), 'size="1"') . '   ';
                        ?></td>
                      </tr>
                      <tr>
                        <td> </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> </td>
              </tr>
              <tr>
                <td> </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($_GET['start'], $_GET['end']);
}

// generates csv file from $start order to $end order, inclusive
function generatecsv($start, $end)
{
//Placing columns names in first row
$delim =  ',' ;
$csv_output .= "Orders_id".$delim;
$csv_output .= "Date".$delim;
$csv_output .= "Time".$delim;
$csv_output .= "First_Name".$delim;
$csv_output .= "Last_Name".$delim;
$csv_output .= "Name_On_Card".$delim;
$csv_output .= "Company".$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 .= "Billing_Phone".$delim;
$csv_output .= "ShipTo_First_Name".$delim;
$csv_output .= "ShipTo_Last_Name".$delim;
$csv_output .= "ShipTo_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 .= "Card_Type".$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 .= "Comments".$delim;
$csv_output .= "Order_Subtotal".$delim;
$csv_output .= "Order_Tax".$delim;
$csv_output .= "Order_Insurance".$delim;
$csv_output .= "Tax_Exempt_Message".$delim;
$csv_output .= "Order_Shipping_Total".$delim;
//$csv_output .= "Small_Order_Fee".$delim;
//$csv_output .= "Discount_Rate".$delim;
//$csv_output .= "Discount_Message".$delim;
//$csv_output .= "CODAmount".$delim;
$csv_output .= "Order_Grand_Total".$delim;
$csv_output .= "Number_of_Items".$delim;
$csv_output .= "Shipping_Method".$delim;
$csv_output .= "Shipping_Weight".$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;
//$csv_output .= "Card_CVV_value".$delim;
//$csv_output .= "future1".$delim;
//$csv_output .= "future2".$delim;
//$csv_output .= "future3".$delim;
//$csv_output .= "future4".$delim;
//$csv_output .= "future5".$delim;
//$csv_output .= "future6".$delim;
//$csv_output .= "future7".$delim;
//$csv_output .= "future8".$delim;
//$csv_output .= "future9".$delim;
//$csv_output .= "Remarks".$delim;
//$csv_output .= "ProductId".$delim;
//$csv_output .= "Product_Price".$delim;
//$csv_output .= "Number".$delim;
//$csv_output .= "Product".$delim;
//$csv_output .= "Attribute".$delim;
//$csv_output .= "Attribute_Value".$delim;
$csv_output .= "\n";


//End Placing columns in first row
// 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, customers_id, 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, customers_id, 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, customers_id, 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, customers_id, 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 = tep_db_fetch_array($orders)) { //start one loop
 
$csv_output_ordersbefore = $csv_output;

$Orders_id = $row_orders["orders_id"];
$customers_id = $row_orders["customers_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 = tep_db_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 = tep_db_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 = tep_db_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 = tep_db_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 = tep_db_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 = tep_db_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 = tep_db_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 = tep_db_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, orders_products_id from orders_products
//where orders_id = " . $Orders_id);

// While loop to list the item


//$countproducts = 0;
//$csv_output_item = "";

//$csv_output_order = str_replace($csv_output_ordersbefore, "", $csv_output);

//while($row_orders_products = tep_db_fetch_array($orders_products)) {
    // loop through orders
    // More than one product per order, new line
    
//    if ($countproducts>0){
//        $csv_output .= "\n";
        
//        $csv_output .= $csv_output_order;
        
//        $csv_output_item = "";
//    }
    
//    $csv_output_item .= "," . "BEGIN_ITEM". "," ;
//    $csv_output_item .= ",";
//    $csv_output_item .= filter_text($row_orders_products[0]) . "," ;
//    $csv_output_item .= $row_orders_products[1] . "," ;
//    $csv_output_item .= $row_orders_products[2] . "," ;
//    $csv_output_item .= filter_text($row_orders_products[3]) . "," ;
//    $Products_id = $row_orders_products[4];
//
//    $orders_products_attributes = tep_db_query("select products_options, products_options_values from orders_products_attributes
//    where orders_id = " . $Orders_id . " and orders_products_id  = " . $Products_id);
//    
//    while($row_orders_products_attributes = tep_db_fetch_array($orders_products_attributes)) {
//        $csv_output_item .= filter_text($row_orders_products_attributes[0]) . "," ;
//        $csv_output_item .= filter_text($row_orders_products_attributes[1]) . "," ;
//    }

//    $csv_output_item .= "END_ITEM";
    
//    $csv_output .= $csv_output_item;
    
//    $countproducts += 1;

//} // 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
?>

Link to comment
Share on other sites

  • 10 months later...
On 30/09/2016 at 6:58 AM, ecommunlimited said:

This works for me. I changed the <?php require(DIR_WS_INCLUDES . 'header.php'); ?> to <?php require(DIR_WS_INCLUDES . 'template_top.php'); ?> and I removed the colum_left as well. I needed to for an add on I have.

 

 

There are clearly some errors in the original contribution. I found I was also getting blank CSVs until I used the code from ecommunlimited above.

However, I am trying to get it to export other fields. I am trying to get enough info into the CSV for it to work with Royal Mail Click & Drop, but I'm not 100% sure exactly what is needed yet. I tried uncommenting the fields below

 

$csv_output .= "Product_Id".$delim;
$csv_output .= "Product_Price".$delim;
$csv_output .= "Number".$delim;
$csv_output .= "Product".$delim;
$csv_output .= "Attribute".$delim;
$csv_output .= "Attribute_Value".$delim;

But am just getting the headers on the CSV, but no data in the columns below.

I have also tried to get the queries 9 section to work with no results. I notice that this section still uses mysql_fetch_array in two places instead of tep_db_fetch_array which has replaced it in the rest of the code. However, when I replace these in queries 9, I get

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2

select products_options, products_options_values from orders_products_attributes where orders_id = 2 and orders_products_id =

[TEP STOP]

Here's the code for queries 9

//Get list of products ordered
$orders_products = tep_db_query("select products_model, products_price, products_quantity, products_name, orders_products_id from orders_products
where orders_id = " . $Orders_id);
// While loop to list the item

$countproducts = 0;
$csv_output_item = "";
$csv_output_order = str_replace($csv_output_ordersbefore, "", $csv_output);
while($row_orders_products = mysql_fetch_array($orders_products)) {
    
// loop through orders
// More than one product per order, new line

if ($countproducts>0){
 $csv_output .= "\n";

 $csv_output .= $csv_output_order;

 $csv_output_item = "";
}

$csv_output_item .= "," . "BEGIN_ITEM". "," ;
$csv_output_item .= ",";
$csv_output_item .= filter_text($row_orders_products[0]) . ";" ;
$csv_output_item .= $row_orders_products[1] . ";" ;
$csv_output_item .= $row_orders_products[2] . ";" ;
$csv_output_item .= filter_text($row_orders_products[3]) . ";" ;
$Products_id = $row_orders_products[4];
$orders_products_attributes = tep_db_query("select products_options, products_options_values from orders_products_attributes
where orders_id = " . $Orders_id . " and orders_products_id  = " . $Products_id);

while($row_orders_products_attributes = mysql_fetch_array($orders_products_attributes)) {
 $csv_output_item .= filter_text($row_orders_products_attributes[0]) . "," ;
 $csv_output_item .= filter_text($row_orders_products_attributes[1]) . "," ;
}
$csv_output_item .= "END_ITEM";

$csv_output .= $csv_output_item;

$countproducts += 1;
}
// end while loop for products

Any help greatly appreciated.

Link to comment
Share on other sites

  • 7 months later...

Hi,
I need to export CSV file with total weight order but i don't know how do that. Osc 2.3.4 - my exportorders.php is:

 

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

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

  Copyright (c) 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_DPD', 'exportorders_dpd.php');


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

// Check if the form is submitted
if (!$_GET['submitted'])
{
require(DIR_WS_INCLUDES . 'template_top.php');
?>

    <table border="0" width="100%" cellspacing="0" cellpadding="2">
      <tr>
        <td width="100%"><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
               <td class="pageHeading"><?php echo "Export pliku do CSV" ?></td>
                <td class="pageHeading" align="left"></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 "Od numeru zamĂłwienia:"; ?></td>
<td><input name="start" size="5" value="<?php echo $start; ?>">
</tr>
<tr>
<td><?php echo "Do numeru zamĂłwienia:" ; ?></td>
<td><input name="end" size="5" value="<?php echo $end; ?>">
</tr>
<tr>
<td><?php echo "Status ZamĂłwienia"; ?></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' => 'Wszystkie zamĂłwienia')), $orders_statuses), $status); ?>
</tr>
	
<tr>
<td><?php echo "Sposób Wyświetlania:"; ?></td>
<td>
<select name="submitted">
	<option value="1">UtwĂłrz plik CSV</option>
	<option value="2">PokaĹĽ plik na ekranie</option>
</select>
</td></tr>

<tr>
<td>&nbsp;</td>
<td><input type="submit" value="<?php echo "Export pliku  DPD CSV"; ?>"></td>
</tr>
</table></form>
	</td></tr></table>
	</td></tr></table>
	</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($_GET['start'], $_GET['end']);
}

// generates csv file from $start order to $end order, inclusive
function generatecsv($start, $end)
{
//Placing columns names in first row
$delim =  '^' ;
//$csv_output .= "Orders_id".$delim;
//$csv_output .= "Date".$delim;
//$csv_output .= "Time".$delim;
//$csv_output .= "First_Name".$delim;
//$csv_output .= "Last_Name".$delim;
//$csv_output .= "Name_On_Card".$delim;
//$csv_output .= "Company".$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 .= "Billing_Phone".$delim;
$csv_output .= "Imie Nazwisko".$delim;
//$csv_output .= "Nazwisko".$delim;
//$csv_output .= "ShipTo_Name".$delim;
$csv_output .= "Nazwa Firmy".$delim;
$csv_output .= "Address_1".$delim;
//$csv_output .= "Address_2".$delim;
$csv_output .= "kod pocztowy".$delim;
$csv_output .= "Miejscowosc".$delim;
//$csv_output .= "ShipTo_State".$delim;

//$csv_output .= "ShipTo_Country".$delim;
$csv_output .= "telefon".$delim;
//$csv_output .= "Card_Type".$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 .= "Comments".$delim;
//$csv_output .= "Order_Subtotal".$delim;
//$csv_output .= "Order_Tax".$delim;
//$csv_output .= "Order_Insurance".$delim;
//$csv_output .= "Tax_Exempt_Message".$delim;
//$csv_output .= "Order_Shipping_Total".$delim;
//$csv_output .= "Small_Order_Fee".$delim;
//$csv_output .= "Discount_Rate".$delim;
//$csv_output .= "Discount_Message".$delim;
$csv_output .= "email".$delim;
//$csv_output .= "Order_Grand_Total".$delim;
//$csv_output .= "Number_of_Items".$delim;
//$csv_output .= "Sposob wysylki".$delim;
$csv_output .= "Numer Zamowienia".$delim;
$csv_output .= "Waga wysylki".$delim;
$csv_output .= "Waga wysylki2".$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;
//$csv_output .= "Card_CVV_value".$delim;
//$csv_output .= "future1".$delim;
//$csv_output .= "future2".$delim;
//$csv_output .= "future3".$delim;
//$csv_output .= "future4".$delim;
//$csv_output .= "future5".$delim;
//$csv_output .= "future6".$delim;
//$csv_output .= "future7".$delim;
//$csv_output .= "future8".$delim;
//$csv_output .= "future9".$delim;
//$csv_output .= "Remarks".$delim;
//$csv_output .= "ProductId".$delim;
//$csv_output .= "Product_Price".$delim;
//$csv_output .= "Number".$delim;
//$csv_output .= "Product".$delim;
//$csv_output .= "Attribute".$delim;
//$csv_output .= "Attribute_Value".$delim;
$csv_output .= "\n";


//End Placing columns in first row
// 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, customers_id, 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, customers_id, 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, customers_id, 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, customers_id, 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 = mysqli_fetch_array($orders)) { //start one loop
 
$csv_output_ordersbefore = $csv_output;

$Orders_id = $row_orders["orders_id"];
$customers_id = $row_orders["customers_id"];
$Date1 = $row_orders["date_purchased"];
//list($Date, $Time) = explode (' ',$Date1);
$Date = date('mdY', 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 = mysqli_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 = mysqli_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 = mysqli_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 = mysqli_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 = mysqli_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 = mysqli_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 = mysqli_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 = mysqli_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 .= "". "^" ;
$csv_output .= $ShipTo_First_Name . " " ;
$csv_output .= $ShipTo_Last_Name . "^" ;
$csv_output .= $ShipTo_Company . "^" ;
$csv_output .= $ShipTo_Address_1 . "^" ;
//$csv_output .= "PL" . "^" ;
$csv_output .= $ShipTo_Zip . "^" ;
$csv_output .= $ShipTo_City . "^" ;


//$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 .= $email . "^" ;
$csv_output .= $Orders_id . "^" ;
//$csv_output .= $Date . "^" ;
//$csv_output .= "2000^^N^N^N^N^N^N^N^N^N". "" ;
//$csv_output .= $ShipTo_Name . "," ;
//$csv_output .= $ShipTo_Company . "," ;
//$csv_output .= $ShipTo_Address_1 . "," ;
//$csv_output .= $ShipTo_Address_2 . "," ;
//$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 .= $total_weight . "^" ;
$csv_output .= $shipping_weight . "2^" ;
//$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, orders_products_id from orders_products
//where orders_id = " . $Orders_id);

// While loop to list the item


//$countproducts = 0;
//$csv_output_item = "";

//$csv_output_order = str_replace($csv_output_ordersbefore, "", $csv_output);

//while($row_orders_products = mysql_fetch_array($orders_products)) {
	// loop through orders
	// More than one product per order, new line
	
//	if ($countproducts>0){
//		$csv_output .= "\n";
		
//		$csv_output .= $csv_output_order;
		
//		$csv_output_item = "";
//	}
	
//	$csv_output_item .= "," . "BEGIN_ITEM". "," ;
//	$csv_output_item .= ",";
//	$csv_output_item .= filter_text($row_orders_products[0]) . "," ;
//	$csv_output_item .= $row_orders_products[1] . "," ;
//	$csv_output_item .= $row_orders_products[2] . "," ;
//	$csv_output_item .= filter_text($row_orders_products[3]) . "," ;
//	$Products_id = $row_orders_products[4];
//
//	$orders_products_attributes = tep_db_query("select products_options, products_options_values from orders_products_attributes
//	where orders_id = " . $Orders_id . " and orders_products_id  = " . $Products_id);
//	
//	while($row_orders_products_attributes = mysql_fetch_array($orders_products_attributes)) {
//		$csv_output_item .= filter_text($row_orders_products_attributes[0]) . "," ;
//		$csv_output_item .= filter_text($row_orders_products_attributes[1]) . "," ;
//	}

//	$csv_output_item .= "END_ITEM";
	
//	$csv_output .= $csv_output_item;
	
//	$countproducts += 1;

//} // 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
?>
Please help me :)
Link to comment
Share on other sites

1. add a field in table "orders" via phpMyAdmin called "weight" immediately after "payment_method", the field is DECIMAL (5,2) NOT NULL,
2. in catalog/checkout_process.php  after:
 

$shipping_modules = new shipping($shipping);

 add
 

$total_weight = $cart->show_weight();

same file - after:

'payment_method' => $order->info['payment_method'],

add
 

'weight' => $total_weight,

 in catalog/admin/includes/classes/order.php
in line 38 add 'weight'

 $order_query = tep_db_query("select customers_id, customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country, customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state, delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id, payment_method, weight, cc_type, cc_owner, cc_number, cc_expires, currency, currency_value, date_purchased, orders_status, last_modified from " . TABLE_ORDERS . " where orders_id = '" . (int)$order_id . "'");
   

same file line  56 add after:

'payment_method' => $order['payment_method'],

this:

'weight' => $order['weight'],

Aftert this "solution" i have in sql table weight and i can export to csv orders with weight.

In exportordes.php in line 199-217 added word weight:

// 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, customers_id, 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, weight
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, customers_id, 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, weight
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, customers_id, 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, weight
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, customers_id, 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, weight
FROM orders WHERE orders_id >= $start AND orders_id <= $end ORDER BY orders_id");
}
//patch

in line 261:

$weight = $row_orders ['weight'];

in line 428:

$csv_output .= $weight . "^" ;

It's work for me.

Link to comment
Share on other sites

  • 1 year later...

Hello everyone,

I hope this is the correct place to ask my question. I receive a new price list every week in an excel spreadsheet. I make the correct changes and then save it in an CSV format.I open the CSV Import and find the file and upload it then import it. when it's done it says it was successful but it does not show up on my website.  I am including a csv file. Could it be my file or the setting. I don't really know what the settings should be.

Thank you in advance for your help

Ray

May 23_ 2019 STOCKLIST.csv

settings1.JPG

settings2.JPG

Link to comment
Share on other sites

Have a look at Easy Populate...create an export file so you can see what data is needed.  Populate that with your spreadsheet information and then import it.   Once you get it formatted the way you need it, it should be pretty useful to use on a weekly basis.

Dan

Link to comment
Share on other sites

Sorry I can't offer you any guidance with that.  I use an older version but I believe there are more up-to-date versions available.  The admin side of osC hasn't changed much so most versions, if they work at all, should work with your version of osC.   Maybe someone who has installed it recently will chime in or maybe post that question in the Easy Populate support thread.

Dan

Link to comment
Share on other sites

First the attached Easypopulate used withinn last 3-4 months and known to work with Frozen.

The CSV file simply use your phpMyadmin to import the data and see if you have any issues. It's much simpler to use and will give you error report if you have any issues with the data format. As allways test on backup db first nt on live db, then if all is good do on live db.

oscom-easypopulate-BS3-Test V1.0.zip

 

Link to comment
Share on other sites

Thank you so much Zahid,

Should I remove CSV Import since I can't get it to work?

**Also with emojis on the upper right how do I do a like and a thank you? I am having a hard time figuring how to use a/or this forum** LOL

Thank you so MUCH to everyone for their help!

Link to comment
Share on other sites

24 minutes ago, ralgiere said:

Should I remove CSV Import since I can't get it to work?

That's up to you, but normaly I would advise if it's not working or you are unable to use it, then remove it as no point having code your not using.

25 minutes ago, ralgiere said:

how do I do a like and a thank you?

😂 Would not worry about it, you can only have one at anyone time on a thread.

image.png.b1cd50a984857217cb8bbde7283b144f.png

 

Link to comment
Share on other sites

Ray...did you have a look at Easy Populate?  I think once you set that up and create an excel spreadsheet to load in your data and reformat it, you'll have a file you can import directly into osC.

Dan

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...