Categories
View Replies (8)

Resolved Export Virtuemart Orders

  1. loopy05
    loopy05
  2. RO CSVI
  3. Saturday, 07 April 2018
  4.  Subscribe via email
Hi,

I'm having a problem with the export, I'm pretty sure I just do not know to to setup the export correctly. I want to have all oders for a special period, e.g. for one month.
I have setup the fields for the export (CSVI_export_fields.jpg) and have set the "Group fields" in my template to "order_item_name". And I guess this my error.

When i have now 50 orders from the last month, and one product is included 20 times in these orders I only find this product in one exported line. In the rest of the orders the product is missing.

E.g, 2 orders (see attachment CSVI_orders.jpg)
In order BA4F063 one position is missing. The product with the name "Poloshirt Damen COMPANY" is not mentioned in this order because it has been ordered before in order VF26061. So how shall I group these orders correctly? If I do not group my orders, my export includes hundreds of lines though I only have maybe 10 positions in both orders.

Thanks,

CSVI_export_fields.jpg CSVI_orders.jpg
0
Responses (8)
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

The Group By is always a challenge because it takes a bit of puzzling to find out what the correct setting is.

have set the "Group fields" in my template to "order_item_name". And I guess this my error.
That is not a useful field for the group by as you will always get only 1 unique product name. I think the field to use for Group By is virtuemart_order_item_id. This is the unique ID for each ordered item.

Give that a try and see how it goes.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 1
loopy05
loopy05
Accepted Answer Pending Moderation
Hi,

thank you for your help. I changed the "Group fields" now to "virtuemart_order_item_id". Unfortunately my export includes now hundreds of lines. See attachment
CSVI_Export.csv
CSVI_Export.csv

One of these 2 orders (BA4F063) looks in the backend like this:
csvi_order_backend.JPG

I don't know what I'm doing wrong.

Thanks.
Attachments (2)
csvi_order_backend.JPG csvi_order_backend.JPG
CSVI_Export.csv
  1. more than a month ago
  2. RO CSVI
  3. # 2
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

Please post your debug log for me to check. Perhaps I can see which fields you need to group on.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 3
loopy05
loopy05
Accepted Answer Pending Moderation
Hi,

attached the debug log for the group field "virtuemart_order_item_id". As i said, this generates an export with hundreds of lines for 2 orders, which is not correct.
Thanks.

Warning: Spoiler Alert! [Toggle message] 

#

#<?php die('Forbidden.'); ?>

#Date: 2018-04-08 20:36:08 UTC

#Software: Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT



#Fields: date	time	line_nr	action	comment

2018-04-08	20:36:08	0	[DEBUG]	Clean up old logs. Found 25 logs and threshold is 25 logs

2018-04-08	20:36:08	0	[DEBUG]	Clean up old logs. Found 25 logs and threshold is 25 logs

2018-04-08	20:36:08	0	[DEBUG]	CSVI Pro Version: 7.5.1

2018-04-08	20:36:08	0	[DEBUG]	Joomla! version: 3.8.4

2018-04-08	20:36:08	0	[DEBUG]	PHP version: 5.6.35

2018-04-08	20:36:08	0	[DEBUG]	=========================

2018-04-08	20:36:08	0	[DEBUG]	action: export

2018-04-08	20:36:08	0	[DEBUG]	component: com_virtuemart

2018-04-08	20:36:08	0	[DEBUG]	operation: order

2018-04-08	20:36:08	0	[DEBUG]	override: 

2018-04-08	20:36:08	0	[DEBUG]	output_empty_file: Yes

2018-04-08	20:36:08	0	[DEBUG]	localpath: /var/www/vhosts/http://shop.haldrup.net/httpdocs

2018-04-08	20:36:08	0	[DEBUG]	ftphost: 

2018-04-08	20:36:08	0	[DEBUG]	ftpport: 

2018-04-08	20:36:08	0	[DEBUG]	sftp: No

2018-04-08	20:36:08	0	[DEBUG]	ftproot: 

2018-04-08	20:36:08	0	[DEBUG]	ftpfile: 

2018-04-08	20:36:08	0	[DEBUG]	export_email_subject: 

2018-04-08	20:36:08	0	[DEBUG]	export_email_body: 

2018-04-08	20:36:08	0	[DEBUG]	database_username: 

2018-04-08	20:36:08	0	[DEBUG]	database_host: 

2018-04-08	20:36:08	0	[DEBUG]	database_portno: 

2018-04-08	20:36:08	0	[DEBUG]	database_name: 

2018-04-08	20:36:08	0	[DEBUG]	database_table: 

2018-04-08	20:36:08	0	[DEBUG]	primary_key_source: Bestellnummer

2018-04-08	20:36:08	0	[DEBUG]	primary_key_target: id

2018-04-08	20:36:08	0	[DEBUG]	create_new_columns: Yes

2018-04-08	20:36:08	0	[DEBUG]	export_filename: 

2018-04-08	20:36:08	0	[DEBUG]	export_file: csv

2018-04-08	20:36:08	0	[DEBUG]	field_delimiter: |

2018-04-08	20:36:08	0	[DEBUG]	text_enclosure: 

2018-04-08	20:36:08	0	[DEBUG]	include_column_headers: Yes

2018-04-08	20:36:08	0	[DEBUG]	signature: No

2018-04-08	20:36:08	0	[DEBUG]	publish_state: Yes

2018-04-08	20:36:08	0	[DEBUG]	recordstart: 

2018-04-08	20:36:08	0	[DEBUG]	recordend: 

2018-04-08	20:36:08	0	[DEBUG]	export_date_format: d/m/Y H:i:s

2018-04-08	20:36:08	0	[DEBUG]	export_price_format_decimal: 2

2018-04-08	20:36:08	0	[DEBUG]	export_price_format_decsep: ,

2018-04-08	20:36:08	0	[DEBUG]	export_price_format_thousep: .

2018-04-08	20:36:08	0	[DEBUG]	header: 

2018-04-08	20:36:08	0	[DEBUG]	body: 

2018-04-08	20:36:08	0	[DEBUG]	footer: 

2018-04-08	20:36:08	0	[DEBUG]	language: de-DE

2018-04-08	20:36:08	0	[DEBUG]	splitorderline: No

2018-04-08	20:36:08	0	[DEBUG]	ordernostart: 

2018-04-08	20:36:08	0	[DEBUG]	ordernoend: 

2018-04-08	20:36:08	0	[DEBUG]	orderlist: 

2018-04-08	20:36:08	0	[DEBUG]	usedatefield: created_on

2018-04-08	20:36:08	0	[DEBUG]	orderdaterange: thismonth

2018-04-08	20:36:08	0	[DEBUG]	orderdatestart: 07-04-2018 12:50:36

2018-04-08	20:36:08	0	[DEBUG]	orderdateend: 

2018-04-08	20:36:08	0	[DEBUG]	ordermdatestart: 

2018-04-08	20:36:08	0	[DEBUG]	ordermdateend: 

2018-04-08	20:36:08	0	[DEBUG]	order_address: 

2018-04-08	20:36:08	0	[DEBUG]	orderpricestart: 

2018-04-08	20:36:08	0	[DEBUG]	orderpriceend: 

2018-04-08	20:36:08	0	[DEBUG]	groupbyfields: {"name":["virtuemart_order_item_id"]}

2018-04-08	20:36:08	0	[DEBUG]	sortfields: {"name":["order_number"]}

2018-04-08	20:36:08	0	[DEBUG]	use_system_limits: No

2018-04-08	20:36:08	0	[DEBUG]	max_execution_time: 

2018-04-08	20:36:08	0	[DEBUG]	memory_limit: 

2018-04-08	20:36:08	0	[DEBUG]	csvi_template_id: 6

2018-04-08	20:36:08	0	[DEBUG]	tags: 

2018-04-08	20:36:08	0	[DEBUG]	=========================

2018-04-08	20:36:08	0	[DEBUG]	Export field: Bestellnummer

2018-04-08	20:36:08	0	[DEBUG]	Export field: Produktbezeichnung

2018-04-08	20:36:08	0	[DEBUG]	Export field: Artikelnummer

2018-04-08	20:36:08	0	[DEBUG]	Export field: Nettopreis

2018-04-08	20:36:08	0	[DEBUG]	Export field: MWST

2018-04-08	20:36:08	0	[DEBUG]	Export field: Bruttopreis

2018-04-08	20:36:08	0	[DEBUG]	Export field: Menge

2018-04-08	20:36:08	0	[DEBUG]	Export field: P_Endsumme

2018-04-08	20:36:08	0	[DEBUG]	Export field: Gesamtpreis

2018-04-08	20:36:08	0	[DEBUG]	Export field: R_Vorname

2018-04-08	20:36:08	0	[DEBUG]	Export field: R_Nachname

2018-04-08	20:36:08	0	[DEBUG]	Export field: R_Email

2018-04-08	20:36:08	0	[DEBUG]	Export field: R_Telefon

2018-04-08	20:36:08	0	[DEBUG]	Export field: V_Vorname

2018-04-08	20:36:08	0	[DEBUG]	Export field: V_Nachname

2018-04-08	20:36:08	0	[DEBUG]	Export field: Stadt

2018-04-08	20:36:08	0	[DEBUG]	Export field: Versandadresse

2018-04-08	20:36:08	0	[DEBUG]	Export field: Login

2018-04-08	20:36:08	0	[DEBUG]	Export field: GTIN_Ikavios_Dummy1

2018-04-08	20:36:08	0	[DEBUG]	Export field: MPN_Ikavios_Dummy2

2018-04-08	20:36:08	0	[DEBUG]	Export query

SELECT `#__virtuemart_orders`.`virtuemart_order_id`,

`#__virtuemart_order_items`.`virtuemart_product_id`,

`#__virtuemart_order_items`.`product_attribute`,

`order_number`,

`order_item_name`,

`order_item_sku`,

`product_item_price`,

`product_tax`,

`product_final_price`,

`product_quantity`,

product_item_price*product_quantity AS product_price_total,

`order_total`,

`#__virtuemart_orders`.`virtuemart_user_id`,

`#__virtuemart_products`.`product_gtin`,

`#__virtuemart_products`.`product_mpn`

FROM `#__virtuemart_orders`

LEFT JOIN `#__virtuemart_order_items` ON `#__virtuemart_orders`.`virtuemart_order_id` = `#__virtuemart_order_items`.`virtuemart_order_id`

LEFT JOIN `#__virtuemart_order_userinfos` AS `user_info1` ON `#__virtuemart_orders`.`virtuemart_order_id` = `user_info1`.`virtuemart_order_id`

LEFT JOIN `#__virtuemart_orderstates` ON `#__virtuemart_orders`.`order_status` = `#__virtuemart_orderstates`.`order_status_code`

LEFT JOIN `#__virtuemart_product_manufacturers` ON `#__virtuemart_order_items`.`virtuemart_product_id` = `#__virtuemart_product_manufacturers`.`virtuemart_product_id`

LEFT JOIN `#__virtuemart_manufacturers` ON `#__virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` = `#__virtuemart_manufacturers`.`virtuemart_manufacturer_id`

LEFT JOIN `#__users` ON `#__users`.`id` = `user_info1`.`virtuemart_user_id`

LEFT JOIN `#__virtuemart_countries` ON `#__virtuemart_countries`.`virtuemart_country_id` = `user_info1`.`virtuemart_country_id`

LEFT JOIN `#__virtuemart_invoices` ON `#__virtuemart_orders`.`virtuemart_order_id` = `#__virtuemart_invoices`.`virtuemart_order_id`

LEFT JOIN `#__virtuemart_paymentmethods_de_de` ON `#__virtuemart_orders`.`virtuemart_paymentmethod_id` = `#__virtuemart_paymentmethods_de_de`.`virtuemart_paymentmethod_id`

LEFT JOIN `#__virtuemart_shipmentmethods` ON `#__virtuemart_orders`.`virtuemart_shipmentmethod_id` = `#__virtuemart_shipmentmethods`.`virtuemart_shipmentmethod_id`

LEFT JOIN `#__virtuemart_products` ON `#__virtuemart_products`.`product_sku` = `#__virtuemart_order_items`.`order_item_sku`

WHERE DATE(`#__virtuemart_orders`.`created_on`) >= DATE_SUB('2018-04-08', INTERVAL 08 DAY) AND DATE(`#__virtuemart_orders`.`created_on`) <= '2018-04-08'

ORDER BY `order_number`

2018-04-08	20:36:08	1	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	1	[QUERY]	 SELECT `first_name` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 61 AND `address_type` = 'BT'

2018-04-08	20:36:08	1	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	1	[QUERY]	 SELECT `last_name` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 61 AND `address_type` = 'BT'

2018-04-08	20:36:08	1	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	1	[QUERY]	 SELECT `email` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 61 AND `address_type` = 'BT'

2018-04-08	20:36:08	1	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	1	[QUERY]	 SELECT `phone_1` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 61 AND `address_type` = 'BT'

2018-04-08	20:36:08	1	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	1	[QUERY]	 SELECT `first_name` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 61 AND `address_type` = 'ST'

2018-04-08	20:36:08	1	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	1	[QUERY]	 SELECT `last_name` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 61 AND `address_type` = 'ST'

2018-04-08	20:36:08	1	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	1	[QUERY]	 SELECT `city` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 61 AND `address_type` = 'ST'

2018-04-08	20:36:08	1	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	1	[QUERY]	 SELECT `address_1` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 61 AND `address_type` = 'ST'

2018-04-08	20:36:08	115	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	115	[QUERY]	 SELECT `first_name` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 59 AND `address_type` = 'BT'

2018-04-08	20:36:08	115	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	115	[QUERY]	 SELECT `last_name` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 59 AND `address_type` = 'BT'

2018-04-08	20:36:08	115	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	115	[QUERY]	 SELECT `email` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 59 AND `address_type` = 'BT'

2018-04-08	20:36:08	115	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	115	[QUERY]	 SELECT `phone_1` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 59 AND `address_type` = 'BT'

2018-04-08	20:36:08	115	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	115	[QUERY]	 SELECT `first_name` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 59 AND `address_type` = 'ST'

2018-04-08	20:36:08	115	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	115	[QUERY]	 SELECT `last_name` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 59 AND `address_type` = 'ST'

2018-04-08	20:36:08	115	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	115	[QUERY]	 SELECT `city` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 59 AND `address_type` = 'ST'

2018-04-08	20:36:08	115	[DEBUG]	Find shipping field details

2018-04-08	20:36:08	115	[QUERY]	 SELECT `address_1` FROM `jos_virtuemart_order_userinfos` WHERE `virtuemart_order_id` = 59 AND `address_type` = 'ST'
  1. more than a month ago
  2. RO CSVI
  3. # 4
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

As i said, this generates an export with hundreds of lines for 2 orders, which is not correct.
For the database it is correct as it gives us what we ask :)

So I took the query and ran it local but I don't see duplicates :side: However I looked at the query and my suggestion to use the virtuemart_order_item_id I don't see it in the query. It also is not logical that you should see 100's of lines when grouping by the virtuemart_order_item_id because that ID only appears a few times. Perhaps your change isn't saved?

After you select the field and close the modal, you need to save the template again. Give that a try and let me know.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 5
loopy05
loopy05
Accepted Answer Pending Moderation
Hi,

in the previous post with my attached debug log, please check the following line:
2018-04-08	20:36:08	0	[DEBUG]	groupbyfields: {"name":["virtuemart_order_item_id"]}

This is the corresponding field, i guess?

Anyway, I checked the template again, checked the "Group Field" and saved both the field selection and the template. Unfortunately the output still stays the same.
CSVI_groupfield.jpg

kind regards,
Attachments (1)
CSVI_groupfield.jpg CSVI_groupfield.jpg
  1. more than a month ago
  2. RO CSVI
  3. # 6
rolandd
rolandd
Accepted Answer Pending Moderation
Hello,

Yes, you are right. I missed that part. I was checking the query where the group by is missing. Please do the following, add the field virtuemart_order_item_id to your list of template fields but set the published to No. This prevents the field from getting into your export file. Now the data should be grouped.

Let me know how that goes.
Kind regards,

RolandD

=========================
If you use our extensions, please post a rating and a review at the Joomla! Extension Directory
  1. more than a month ago
  2. RO CSVI
  3. # 7
loopy05
loopy05
Accepted Answer Pending Moderation
This looks good now :)
Thank you very much for your support.

Now I have to figure out the same for the xml outpout, triggered with a cronjob. If I have questions I will ask here in the forum.


regards,
Sven
  1. more than a month ago
  2. RO CSVI
  3. # 8
  • Page :
  • 1


There are no replies made for this post yet.
Be one of the first to reply to this post!