Lastwolf Posted May 13, 2016 Share Posted May 13, 2016 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. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 13, 2016 Share Posted May 13, 2016 I have done this a few times. I would be happy to help. Send me PM with your email address. Quote Link to comment Share on other sites More sharing options...
Eduard J Posted October 19, 2016 Share Posted October 19, 2016 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. Quote Link to comment Share on other sites More sharing options...
ayz1 Posted October 19, 2016 Share Posted October 19, 2016 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. Quote Link to comment Share on other sites More sharing options...
ayz1 Posted October 19, 2016 Share Posted October 19, 2016 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. Quote Link to comment Share on other sites More sharing options...
bsmither Posted October 19, 2016 Share Posted October 19, 2016 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; Quote Link to comment Share on other sites More sharing options...
Eduard J Posted October 21, 2016 Share Posted October 21, 2016 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 Quote Link to comment Share on other sites More sharing options...
ayz1 Posted October 21, 2016 Share Posted October 21, 2016 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. Quote Link to comment Share on other sites More sharing options...
Eduard J Posted October 28, 2016 Share Posted October 28, 2016 HI, Great it works just a treat. Is there any chance I only pull out 'Order Complete' orders instead of all of them pending, cancelled etc. Many thanks for your help, made my life much easier. Quote Link to comment Share on other sites More sharing options...
ayz1 Posted October 28, 2016 Share Posted October 28, 2016 Set to BETWEEN '2016-01-24' AND '2016-01-25' AND `status` = 3; Quote Link to comment Share on other sites More sharing options...
Eduard J Posted April 24, 2017 Share Posted April 24, 2017 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 ? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.