X-Cart forums - Customizing orders export excel format

X-Cart forums (https://forum.x-cart.com/index.php)

- Changing design (https://forum.x-cart.com/forumdisplay.php?f=51)

- - Customizing orders export excel format (https://forum.x-cart.com/showthread.php?t=42555)

Customizing orders export excel format

Hi there,

Really need some help here.

What I'm trying to do is to change the output format looks of the orders export excel csv file. But after hours of struggle, I'm unable to figure out which files to go after. It was supposed to be skin1/main/orders_export.tpl, but it doesn't seem to work for me, as whatever changes I made to it, there's absolutely No effect on the output. Actually I tried to detete this file, and the export still worked the same way.

There must be something wrong here. But I'm just not sure. Not smart enough to be a programmer.... :(

Please please any advice would be greatly appreciated!

Re: Customizing orders export excel format

Please describe what kind of changes you need for export

Re: Customizing orders export excel format

Ok, here's what I want to achieve:

currently I'm having the built in default export scripts, which output these fields:
!ORDERID !ITEMID !PRODUCTID !PRODUCTCODE !PRODUCT !PRICE !AMOUNT !PROVIDER !OPTION_CLASS !OPTION_VALUE !EXTRA_DATA

I don't want some of these field, for example, the EXTRA_DATA field. At the same time, I want to add a few more fields, for example PRODUCT_OPTIONS (which is actually the human readable version of EXTRA_DATA if I'm right.

Any advice would be appreciated Victor. Thanks in advance.

Re: Customizing orders export excel format

There is no template to do so.
You should edit include/import_orders.php

Extra data is a serialized representation of product options.

find following lines

PHP Code:


		
			
# Export extra fields
            $ef = func_query("SELECT khash, value FROM $sql_tbl[order_extras] WHERE orderid = '$id'");
            if (!empty($ef)) {
                foreach ($ef as $v) {
                    $row['extra_field'][] = $v['khash'];
                    $row['extra_value'][] = $v['value'];
                }
            }
            unset($ef);

and comment it like this

PHP Code:


		
			
# Export extra fields
/*            $ef = func_query("SELECT khash, value FROM $sql_tbl[order_extras] WHERE orderid = '$id'");
            if (!empty($ef)) {
                foreach ($ef as $v) {
                    $row['extra_field'][] = $v['khash'];
                    $row['extra_value'][] = $v['value'];
                }
            }
            unset($ef); */

to disable extra data export

To change extra data export format you need not to comment but modify the lines above. It can be done like this

PHP Code:


		
			
# Export extra fields
  $ef = func_query("SELECT khash, value FROM $sql_tbl[order_extras] WHERE orderid = '$id'");
            if (!empty($ef)) {
                foreach ($ef as $v) {
                    $row['extra_field'] .= $v['khash'].':'.$v['value'].' ';
}
            }
            unset($ef);

But it is necessary to disable serialization of this array
change the lines above in the same file

PHP Code:


		
			
"extra_field"            => array(
                "array"        => true),

PHP Code:


		
			
"extra_field"            => array(  ),

Re: Customizing orders export excel format

Hey Victor,

What if I needed to add a custom column to exporting orders for Worldship? I specifically need to add a column with the value 'shipper' and the header 'Billing shipping charges to' so that I can properly map all the required fields in Worldship.

Re: Customizing orders export excel format

Victor,

Great! You're the man! I'll implement it and post the results for others having the same concerns.

Re: Customizing orders export excel format

I find the default code in import_order_items.php is wrong.

[code]
1. in import_order_items.php:
"option_value" => array(
"array" => true),
should be instead:
"product_class_option" => array(
"array" => true)
if you want to export the human readable product options in order details.

2. if you want to get data from order_details, do the following:
find
if ($single_mode || AREA_TYPE == 'A') {
$row = func_query_first("SELECT * FROM $sql_tbl[order_details] WHERE itemid = '$id'");
} else {
$row = func_query_first("SELECT * FROM $sql_tbl[order_details] WHERE itemid = '$id' AND $sql_tbl[order_details].provider = '$login'");
}

replace with
if ($single_mode || AREA_TYPE == 'A') {
$row = func_query_first("SELECT * FROM $sql_tbl[order_details] WHERE itemid = '$id'");
$myorderid = $row['orderid'];
$row2 = func_query_first("SELECT * FROM $sql_tbl[orders] WHERE orderid = '$myorderid'");
} else {
$row = func_query_first("SELECT * FROM $sql_tbl[order_details] WHERE itemid = '$id' AND $sql_tbl[order_details].provider = '$login'");
$myorderid = $row['orderid'];
$row2 = func_query_first("SELECT $sql_tbl[orders].* FROM $sql_tbl[orders], $sql_tbl[order_details] WHERE $sql_tbl[orders].orderid = '$id' AND $sql_tbl[orders].orderid = $sql_tbl[order_details].orderid AND $sql_tbl[order_details].provider = '$login'");
}
[code]

And if you want to add or eliminate more export fields, just add or delete more elements in $import_specification['ORDER_ITEMS'].

At the end, I'm not a program guy. So my code may look a little junky. But it did what I want to achieve. Hopefully it helps those with the same concern.

Many thanks to Victor.

Re: Customizing orders export excel format

would anyone be able to give me any pointers on this to export my products so it just lists the sku and the wholesale price?

Re: Customizing orders export excel format

I'm not sure if this helps in this particular situation but I've done something similar with adding a date of purchase column and category of product purchased: http://forum.x-cart.com/showthread.php?t=64163&highlight=order_items