Jump to content

Export Orders


Lastwolf

Recommended Posts

I need to be able to export orders, I've tried for like 2 days but extensions melt my brain. I had a mod that did it for CC4 and CC5, but when I try to patch it into cc6 I get many pretty errors about cross site forgery (much fun)

Basically, I need to be able to extract all the order information in a csv file as it's easier for me to handle/ print labels that way.
 

 

I can provide the working cc5 model if it helps in anyway.

 

Link to comment
Share on other sites

  • 5 months later...

I need a little help with final touch on mysql query, I managed to get report as i need but I'm unable to work out how to add date range for the report, tried so many varieties and still no luck, any help would be really appreciated. Here is so far what I've done : 

SELECT order_date,
       shop1_CubeCart_order_inventory.cart_order_id AS 'Order ID',
       first_name AS 'Name',
       last_name AS 'Surname',
       email,
       shop1_CubeCart_order_inventory.name AS 'Product Name',
        quantity,
        price,
        discount,
        shipping
FROM shop1_CubeCart_order_summary 
INNER JOIN shop1_CubeCart_order_inventory
ON shop1_CubeCart_order_inventory.cart_order_id = shop1_CubeCart_order_summary.cart_order_id;

Also tried by order ID as it is actually a date and time when order was placed but with same error. 
 

Link to comment
Share on other sites

Looks like you need a WHERE clause in there e.g. WHERE `order_date` BETWEEN 1420070401 AND 1422576001

I've been looking at something similar myself but because cubecart uses unix timestamps and not real dates to store the date information it's causing me a bit of a headache. Only way I've managed to get anything to work is to use the timestamps e.g. 1420070401  and  1422576001. You can create the timestamps at http://www.unixtimestamp.com

There must be a way to use real dates but not found it yet. Hopefully someone with a bit more knowledge will be along with a better solution or a one that works if this isn't any help.

 

 

 

 

 

Link to comment
Share on other sites

Just had another look at this and something like SELECT FROM_UNIXTIME(`order_date`) FROM `CubeCart_order_summary`; does convert the timestamps to date/time values so wondering if worth creating a new column and converting the timestamps to real dates to see if it is easier to work with.

Just tried this and cretaed a new column called time and something like SELECT * FROM `CubeCart_order_summary` WHERE `time` BETWEEN '2016-02-17 11:25:10' AND '2016-02-18 11:25:10'; works.

Link to comment
Share on other sites

When creating an exporter, you have the power of PHP to make calculations for you.

$from = mktime(false,false,false,month_int,day_int,year_int); // replace with actual integers
$to = mktime(23,59,59,month_int,day_int,year_int); // replace with actual integers

$where_string = "WHERE `order_date` BETWEEN ".$from." AND ".$to;

Link to comment
Share on other sites

On 19/10/2016 at 3:13 PM, ayz1 said:

Just had another look at this and something like SELECT FROM_UNIXTIME(`order_date`) FROM `CubeCart_order_summary`; does convert the timestamps to date/time values so wondering if worth creating a new column and converting the timestamps to real dates to see if it is easier to work with.

Just tried this and cretaed a new column called time and something like SELECT * FROM `CubeCart_order_summary` WHERE `time` BETWEEN '2016-02-17 11:25:10' AND '2016-02-18 11:25:10'; works.

Hi,

Thanks for update, not sure where to update above, is there any chance you can update it to my query above so I see where " FROM_UNIXTIME" goes same for date range. Not very good at databases :)

Link to comment
Share on other sites

Please backup your database or preferably test on a copy.

Run this query once.

ALTER TABLE ` shop1_ CubeCart_order_summary` ADD `time` DATE NULL;

Then run these two queries each time (Change dates as appropriate)

UPDATE ` shop1_ CubeCart_order_summary` SET `time`= FROM_UNIXTIME(`order_date`);


SELECT order_date,
       shop1_ CubeCart_order_inventory.cart_order_id AS 'Order ID',
       first_name AS 'Name',
       last_name AS 'Surname',
       email,
       shop1_ CubeCart_order_inventory.name AS 'Product Name',
        quantity,
        price,
        discount,
        shipping,
        time
FROM shop1_ CubeCart_order_summary
INNER JOIN shop1_ CubeCart_order_inventory
ON shop1_ CubeCart_order_inventory.cart_order_id = shop1_ CubeCart_order_summary.cart_order_id WHERE `time` BETWEEN '2016-01-24' AND '2016-01-25';

I'm no expert so please test on a backup if possible.

Link to comment
Share on other sites

  • 5 months later...

Hi,
For some reason this queries don't work anymore :

For 1st one to run i get error message Duplicate column name 'time' (ALTER TABLE `shop1_CubeCart_order_summary` ADD `time` DATE NULL;)
than for 2nd part it says table does not exist, not sure what that could be, any idea ?

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