If anyone is interested to develop an order export module, which i think should be a standard in any cart with the purpose of providing information about the orders to a fulfillment company.
CSV is industry standard yet XML is what is becoming a new industry standard.
I would probably develop it myself but simply do not have any time recently. If anyone is interested please send me a message.
I’m working on this:
This PHP script Extracts MySQL table and downloads into an Excel Spreadsheet.
Script by Jeff Johns, for a full explanation and tutorial on this, see: [url]http://www.phpfreaks.com/tutorials/114/0.php[/url]
**************
CONFIGURATION:
YOUR DATABASE HOST = (ex. localhost)
USERNAME = username used to connect to host
PASSWORD = password used to connect to host
DB_NAME = your database name
TABLE_NAME = table in the database used for extraction
**************
To extract specific fields and not the whole table, simply replace
the * in the $select variable with the fields you want
**************/
define(db_host, "localhost");
define(db_user, "USER NAME");
define(db_pass, "PASSWORD");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "DATABASE NAME");
mysql_select_db(db_name);
/*************
Build query, call it, and find the number of fields
/*************/
$select = "SELECT * FROM cscart_orders, cscart_order_details, cscart_products where cscart_orders.order_id=cscart_order_details.order_id and cscart_order_details.product_id=cscart_products.product_id and status like '%C%'"; /*Only export completed orders*/
$export = mysql_query($select);
$fields = mysql_num_fields($export);
/************
Extract field names and write them to the $header variable
/***********/
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}
/***********
Extract all data, format it, and assign to the $data variable
/**********/
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
/************
Set the default message for zero records
/************/
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
/************
Set the automatic download section
/************/
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data"; ;
?>
This is still work in progress.
I'm not very good with MySQL queries, so the query doesn't yet show 'product name'. I keep getting an error.
If i get any further i'll let you know.
I wouldnt selec * from all 3 tables, thats a waste of resources.
Select only the data you need for the feed.
Let me know what you are trying to extract. I can help with sql.
This is what i want
This PHP script Extracts MySQL table and downloads into an Excel Spreadsheet.
Script by Jeff Johns, for a full explanation and tutorial on this, see: [url]http://www.phpfreaks.com/tutorials/114/0.php[/url]
**************
CONFIGURATION:
YOUR DATABASE HOST = (ex. localhost)
USERNAME = username used to connect to host
PASSWORD = password used to connect to host
DB_NAME = your database name
TABLE_NAME = table in the database used for extraction
**************
To extract specific fields and not the whole table, simply replace
the * in the $select variable with the fields you want
**************/
define(db_host, "localhost");
define(db_user, "USER NAME");
define(db_pass, "PASSWORD");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "DATABASE");
mysql_select_db(db_name);
/*************
Build query, call it, and find the number of fields
/*************/
$select = 'SELECT cscart_order_details.order_id, cscart_order_details.amount, cscart_products.product_code, cscart_product_prices.price, cscart_product_descriptions.product, cscart_orders.shipping_cost, cscart_orders.timestamp, cscart_orders.title, cscart_orders.firstname, cscart_orders.lastname, cscart_orders.b_address, cscart_orders.b_address_2, cscart_orders.b_city, cscart_orders.b_state, cscart_orders.b_zipcode, cscart_orders.phone, cscart_orders.tracking_number, cscart_orders.total, cscart_orders.subtotal FROM cscart_order_details, cscart_products, cscart_product_prices, cscart_product_descriptions, cscart_orders WHERE cscart_order_details.product_id=cscart_products.product_id AND cscart_order_details.product_id=cscart_product_prices.product_id AND cscart_order_details.product_id=cscart_product_descriptions.product_id AND cscart_order_details.order_id=cscart_orders.order_id AND cscart_orders.status=\'C\'';
$export = mysql_query($select);
$fields = mysql_num_fields($export);
/************
Extract field names and write them to the $header variable
/***********/
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}
/***********
Extract all data, format it, and assign to the $data variable
/**********/
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
/************
Set the default message for zero records
/************/
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
/************
Set the automatic download section
/************/
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data"; ;
?>
But it just prints to screen, it wont save as a file.
Got it sorted. Thanks
[quote name=‘baballuci’]Got it sorted. Thanks[/QUOTE]
Thank you for taking time to respond! We should write a mod i think, should we? Do you add the script to func.php in a form of a function and then call it from one of the tpl files?
Thank you.
i cant use that.i use what you say.but my result file include just “(0) Records Found!”
1.3.5 version
The code above is for a much earlier version of CS-Cart and there have been significant changes in the database since then. Have you tried to use the existing Order export available in version 1.3.5. It allows you to export either orders or order line items in standard CSV and semicolon- or tab-delimited formats.
Bob
jobosales
the demo of 1.3.5 export works like a charm out of the box - but can you or anyone tell me, are you able to configure the fields in the order export - for example off the top to have weight of the order added or any other field of choice?
Thanks
Unfortunately, you can only use the fields provided without doing some additional coding. Further, it appears that the weight is not stored in the same table (_orders) but that it might be stored in the “data” field of the _order_data table.
I would submit a FEATURE REQUEST (not a bug) in the Bug Tracker asking that the weight be made available in the order export. If there are other specific fields that you would also like exported in the order export, I would request them at the same time. If possible, concisely state the reason (e.g., "so we can prepare XXX from exported order data).
I have had good success when requesting that small additions be made to existing features - the developers want CS-Cart to be the very best available solution. It may not make it immediately but will likely be considered for the next spX update.
This is NOT, however, the place to ask for major changes or whole new subsystems (e.g., backorders). Put these types of requests in the “Wishlist & Future Requests” subforum as they will likely be part of a major release.
Bob
I have develop a mod to export order and order detail into a CSV file, but it is not in a XML format. But it should not be hard to convert to XML file.