Resolved Duplicate items from advanced order export

Hello,

What is being duplicated? You can control what to group your data on by going to the Fields tab and add the fields you want to group the records on. For example if product_item_sku is duplicate, you add that to the Group by list. Usually you will add the fields you get duplicates of on this list.

Orderlines of some order are duplicate. What are the fields to group?
Please, look at png in attachment.

Thanks in advice.
Michele

Hello Michele,

What are the fields to group?

You know which fields you selected, so you would know which fields to group

You are telling me the order item SKU is duplicate, so you would group on that field. So in the Group By field select the order_item_sku and see how it goes.

I tried to group by sku, but result is very wrong.

I try to explain you the problem with very simply words.
Order #7318010 has really only one row in Virtuemart. Why does xml contain two identical rows? (please, see Cattura.png attachment)

Hello,

Why does xml contain two identical rows?

What you see is not always what is there You export only 4 fields but to retrieve those more fields are used.

Please post your debug log, so I can see what query is running and perhaps see which field you may need to group on.

Details

Date	2015-07-30 22:09:44 UTC

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

Date	Time	Line	Action	Comment

Processed 119 lines

2015-07-30	22:09:44	0	[DEBUG]	Clean up old logs. Found 25 logs and threshold is 25 logs

2015-07-30	22:09:44	0	[QUERY]	SELECT csvi_log_id FROM jos_csvi_logs ORDER BY csvi_log_id

2015-07-30	22:09:45	0	[DEBUG]	Clean up old logs. Found 25 logs and threshold is 25 logs

2015-07-30	22:09:45	0	[QUERY]	SELECT csvi_log_id FROM jos_csvi_logs ORDER BY csvi_log_id

2015-07-30	22:09:45	0	[DEBUG]	CSVI Pro Version: 6.0.1

2015-07-30	22:09:45	0	[DEBUG]	Joomla! version: 3.4.3

2015-07-30	22:09:45	0	[DEBUG]	PHP version: 5.4.33-2+deb.sury.org~lucid+1

2015-07-30	22:09:45	0	[DEBUG]	=========================

2015-07-30	22:09:45	0	[DEBUG]	action: export

2015-07-30	22:09:45	0	[DEBUG]	component: com_virtuemart

2015-07-30	22:09:45	0	[DEBUG]	operation: orderadvanced

2015-07-30	22:09:45	0	[DEBUG]	localpath: /var/www/[...]/tmp/

2015-07-30	22:09:45	0	[DEBUG]	ftphost:

2015-07-30	22:09:45	0	[DEBUG]	ftpport:

2015-07-30	22:09:45	0	[DEBUG]	ftproot:

2015-07-30	22:09:45	0	[DEBUG]	ftpfile:

2015-07-30	22:09:45	0	[DEBUG]	export_email_subject:

2015-07-30	22:09:45	0	[DEBUG]	export_email_body:

2015-07-30	22:09:45	0	[DEBUG]	export_filename: ordini.xml

2015-07-30	22:09:45	0	[DEBUG]	export_file: xml

2015-07-30	22:09:45	0	[DEBUG]	export_site: orderadvanced

2015-07-30	22:09:45	0	[DEBUG]	publish_state:

2015-07-30	22:09:45	0	[DEBUG]	recordstart:

2015-07-30	22:09:45	0	[DEBUG]	recordend:

2015-07-30	22:09:45	0	[DEBUG]	export_date_format: d/m/Y H:i:s

2015-07-30	22:09:45	0	[DEBUG]	export_price_format_decimal: 2

2015-07-30	22:09:45	0	[DEBUG]	export_price_format_decsep: .

2015-07-30	22:09:45	0	[DEBUG]	export_price_format_thousep:

2015-07-30	22:09:45	0	[DEBUG]	add_currency_to_price: No

2015-07-30	22:09:45	0	[DEBUG]	language: it-IT

2015-07-30	22:09:45	0	[DEBUG]	splitorderline: No

2015-07-30	22:09:45	0	[DEBUG]	ordernostart:

2015-07-30	22:09:45	0	[DEBUG]	ordernoend:

2015-07-30	22:09:45	0	[DEBUG]	orderlist:

2015-07-30	22:09:45	0	[DEBUG]	orderdaterange:

2015-07-30	22:09:45	0	[DEBUG]	orderdatestart:

2015-07-30	22:09:45	0	[DEBUG]	orderdateend:

2015-07-30	22:09:45	0	[DEBUG]	ordermdatestart:

2015-07-30	22:09:45	0	[DEBUG]	ordermdateend:

2015-07-30	22:09:45	0	[DEBUG]	1: C

2015-07-30	22:09:45	0	[DEBUG]	2: S

2015-07-30	22:09:45	0	[DEBUG]	order_address:

2015-07-30	22:09:45	0	[DEBUG]	orderpricestart:

2015-07-30	22:09:45	0	[DEBUG]	orderpriceend:

2015-07-30	22:09:45	0	[DEBUG]	header:

2015-07-30	22:09:45	0	[DEBUG]	order:

[company]

[first_name]

[last_name]

[address_1]

[zip]

[city]

[codice_fiscale]

[created_on]

[order_number]

[order_total]

[orderlines]

2015-07-30	22:09:45	0	[DEBUG]	orderline:

[order_item_sku]

[order_item_name]

[product_quantity]

[product_final_price]

2015-07-30	22:09:45	0	[DEBUG]	footer:

2015-07-30	22:09:45	0	[DEBUG]	groupbyfields: {"name":[]}

2015-07-30	22:09:45	0	[DEBUG]	sortfields:

2015-07-30	22:09:45	0	[DEBUG]	use_system_limits: No

2015-07-30	22:09:45	0	[DEBUG]	max_execution_time:

2015-07-30	22:09:45	0	[DEBUG]	memory_limit:

2015-07-30	22:09:45	0	[DEBUG]	=========================

2015-07-30	22:09:45	0	[DEBUG]	Export field: first_name

2015-07-30	22:09:45	0	[DEBUG]	Export field: address_1

2015-07-30	22:09:45	0	[DEBUG]	Export field: order_item_name

2015-07-30	22:09:45	0	[DEBUG]	Export field: codice_fiscale

2015-07-30	22:09:45	0	[DEBUG]	Export field: company

2015-07-30	22:09:45	0	[DEBUG]	Export field: created_on

2015-07-30	22:09:45	0	[DEBUG]	Export field: last_name

2015-07-30	22:09:45	0	[DEBUG]	Export field: order_item_sku

2015-07-30	22:09:45	0	[DEBUG]	Export field: order_number

2015-07-30	22:09:45	0	[DEBUG]	Export field: order_total

2015-07-30	22:09:45	0	[DEBUG]	Export field: product_final_price

2015-07-30	22:09:45	0	[DEBUG]	Export field: product_quantity

2015-07-30	22:09:45	0	[DEBUG]	Export field: zip

2015-07-30	22:09:45	0	[DEBUG]	Export field: city

2015-07-30	22:09:45	0	[DEBUG]	Export field: order_total

2015-07-30	22:09:45	0	[DEBUG]	Export query

SELECT `#__virtuemart_orders`.`virtuemart_order_id`,

`first_name`,

`address_1`,

`order_item_name`,

`CodFiscalePIVA`,

`company`,

`#__virtuemart_orders`.`created_on`,

`last_name`,

`order_item_sku`,

`order_number`,

`order_total`,

`product_final_price`,

`product_quantity`,

`zip`,

`city`

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_it_it` ON `#__virtuemart_orders`.`virtuemart_paymentmethod_id` = `#__virtuemart_paymentmethods_it_it`.`virtuemart_paymentmethod_id`

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

WHERE `#__virtuemart_orders`.`order_status` IN ('U','C','S')

Hello,

Group on the virtuemart_order_item_id and see if that solves your issue.

I think I have solved the bug. Items are really duplicate in some orders, then problem is on order, not on group items.

The problem was on select user_info1, when there are two address_type (BT and ST). Now, I fix adding

. ' AND user_info1.address_type = \'BT\''

at line 370 of
/administrator/components/com_csvi/addon/com_virtuemart/model/export/orderadvanced.php

I don't know if you want to fix it in other way.

See you
Michele

Hello,

Why does xml contain two identical rows?

What you see is not always what is there

In this case, what you see is not always what is there.

If you have multiple addresses you should set the option in the template called Order address type. No need to hack the core.

n this case, what you see is not always what is there.

Perfect! Bill address and delivery address are very common.

Thank you
Michele

Billing and shipping address are very common but it is less common to put them on 2 lines only if they differ.

Anyway, your export is running now

Billing and shipping address are very common but it is less common to put them on 2 lines only if they differ.

Following your "Advanced order XML export", csvi makes duplicate orderlines for some orders. Nobody puts anything!

Anyway, thanks for your work and for your time spent to support.

Following your "Advanced order XML export", csvi makes duplicate orderlines for some orders. Nobody puts anything!

You get duplicate lines when?

Anyway, thanks for your work and for your time spent to support.

Not a problem.

Following your "Advanced order XML export", csvi makes duplicate orderlines for some orders. Nobody puts anything!

You get duplicate lines when?

I get duplicate lines when the order has the shipping address different from the billing address.

Technically, in this case there are two user_info on database for the same order. One has address_type = 'BT', other has address_type = 'ST'.
Clearly, the JOIN with user_info produce duplicate orderlines because any orderline is joined with two user_info tuples.

Sorry for my english, but i wrote quickly.

Clearly, the JOIN with user_info produce duplicate orderlines because any orderline is joined with two user_info tuples.

That is why I said you should use the Order address type option in your template. Nothing wrong with the JOIN if you set it to Billing or Shipping.