Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

Needed - XML order export/feeder Rate Topic   - - - - -

 
  • ilookfab
  • Member
  • Members
  • Join Date: 20-Mar 06
  • 52 posts

Posted 10 June 2006 - 06:21 AM #1

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.

 
  • baballuci
  • Senior Member
  • Members
  • Join Date: 02-Mar 06
  • 969 posts

Posted 17 June 2006 - 10:20 AM #2

I'm working on this:

<?php
/**************
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: http://www.phpfreaks...rials/114/0.php
**************
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.
Charlie

 
  • TonyK
  • Member
  • Members
  • Join Date: 03-Mar 06
  • 1686 posts

Posted 17 June 2006 - 06:41 PM #3

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.
Pimpin' skins since v1.0

 
  • baballuci
  • Senior Member
  • Members
  • Join Date: 02-Mar 06
  • 969 posts

Posted 17 June 2006 - 09:43 PM #4

This is what i want

<?php
/**************
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: http://www.phpfreaks...rials/114/0.php
**************
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.
Charlie

 
  • baballuci
  • Senior Member
  • Members
  • Join Date: 02-Mar 06
  • 969 posts

Posted 18 June 2006 - 03:26 PM #5

Got it sorted. Thanks
Charlie

 
  • ilookfab
  • Member
  • Members
  • Join Date: 20-Mar 06
  • 52 posts

Posted 18 June 2006 - 08:54 PM #6

Got it sorted. Thanks

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.

 
  • honey2000
  • Member
  • Members
  • Join Date: 14-Apr 06
  • 34 posts

Posted 28 November 2007 - 10:51 AM #7

i cant use that.i use what you say.but my result file include just "(0) Records Found!"

1.3.5 version
www.xhediye.com

 
  • jobosales
  • Senior Member
  • Members
  • Join Date: 04-Nov 06
  • 3114 posts

Posted 29 November 2007 - 12:27 AM #8

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
CS-Cart 2.0.14 (testing)

 
  • DELTA9000
  • Senior Member
  • Authorized Reseller
  • Join Date: 17-Apr 07
  • 356 posts

Posted 29 November 2007 - 02:52 PM #9

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

 
  • jobosales
  • Senior Member
  • Members
  • Join Date: 04-Nov 06
  • 3114 posts

Posted 30 November 2007 - 04:55 AM #10

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
CS-Cart 2.0.14 (testing)

 
  • domfang
  • Junior Member
  • Members
  • Join Date: 06-Feb 10
  • 15 posts

Posted 08 October 2010 - 03:04 PM #11

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.
Swimwear Online

CS-Cart Currency exchange rate auto update script: $15.00

CS-Cart Order, order detail export in one file script: $29.00

Contact: dominic@swimwearplace.com