Resolved xml advanced export not working - 500 error

Hello,
Glad to hear that your issues are solved. Would be great if you can give us a review on Joomla extension directory.

Hi Tharuna,
Your solution worked! It is unbelievable.. I was thinking about the strangest reasons for this.. but no, solution was (let's say) simple!
I did not consider that option, that was my fault.

Thank you very much for your patience and time!

Bye!

Hello,
Thank you for sending the requested details. Yes as you said when there are two different address for billing and shipping you see duplicates in export. To avoid this in your template settings on options tab, look for Order address type filter and set it to billing or shipping address. Run the export.

Ok thanks.
I have just sent to you what you requested!

Hello,
MySQL dump is a backup file you can generate from your phpmyadmin to collect the data from your Virtuemart tables. Using this file i will import the VirtueMart data into my database and with the requested template XML i can run the orderadvanced export here to check.

Just a question.. what are you meaning with "dump" of VM tables?

Hello,
I may need to look into your data you have in VirtueMart tables. Can you send me mysql dump of your VirtueMart tables as email to tharuna@csvimproved.com? Also post your export template XML file. To get template XML file go to CSVI maintenance page, Select CSVI Pro on first option and Backup template on second option. Select your template and click continue. Download the XML file and post it.

Hi Tharuna,
I tried both and it is not working.
Especially grouping by order_number, I lose some products in the export!

Hello,
Your are exporting only shipping address fields as per your debug log so i think grouping by correct field should fix the issue. What if you try to group only by virtuemart_order_id field? If not try only order_number field, see if it helps.

Hi Roland,
I attach 2 logs: first one is related to export with standard, normal orderadvanced.php. Second one is related to export with my custom override of orderadvanced.php.
The issue occurs with both.
Please consider that the solution shall suit especially the override export, since I need this.

Thank you!

Hello,

Can you post the debug log with the queries that are being executed? I will have a look at what is happening.

Hi,
unfortunately this is not working. I tried to group by virtuemart_product_id, by virtuemart_order_id, combination of both, also order_item_sku and combination of some shipping fields, but I got always the same problem.
What I don't understand is why I shall find duplicates - I mean, you wrote queries in the php native orderadvanced file, I have only two orders, and duplicates occur only in a certain condition (shipping address different from billing address).
Could you help me solving this? I really am becoming crazy behind this.

Hello,
Please have a look at Group by and Sort by field document to know how to set group by fields so you can avoid duplicate of rows in your export. You may probably need to set group by field to virtuemart_product_id in your export template.

Hi Tharuna,
your hint was fine and now my custom override is working.
Anyway I noticed a strange behaviour, that occurs even with "standard" orderadvanced.php file. If a shipment address different from billing address exists, the products in the order are repeated twice in the xm exported file. So if the order contains 3 products, you will get 6 rows (prod.1, prod.2 and prod.3, then again prod.1, prod.2 and prod.3). If billing and shipment address are the same, this does not happen.
Do you know why this is happening?

Thanks

Hello,
You need to write your custom code in your override file orderadvanced.php. Your custom code has to be one of the case statement for switch statement which comes after the filters (date start, date range, sku products and so on). The order_item_sku is a direct field and they are exported directly without any custom processing on them so they are not seen in the file. The case statement is for extra processing on fields and that is where your custom code goes by adding a case statement for your custom field like case 'order_row'. Hope it is clear.

Hi Tharuna,
even this trial was not good. Anyway, I think I will try to delete the whole database and subdomain (luckily it is a test subdomain), as soon as I have time.

I need a little hint about the new field I need to add for the current order element (mere enumeration of products in the same order, if you remember). You told me to create an override of orderadvanced.php. But I cannot find the section, in original override.php file, where the single order is processed. I mean, in this file a lot of fields are mentioned (all the shipping ones for example), but I can't find the single product of an order. No order_item_sku, no "for" cycle about products in an order.
I find just:

// Add an orderline
$this->addExportContent($this->exportclass->Orderline());

followed by a "for" cycle about fields in the template, with relative sql queries, as well as all the filters set in backend (date start, date range, sku products and so on).
Can you help me understanding which file shall I find to get this part, so that I can add my custom code? I am not sure override of orderadvanced.php can help me.

Thank you

Hello,
Running the below query in override.sql file doesn't show me any duplicate error and the update available fields works fine. The file override.sql is created at location administrator/templates/isis/html/com_csvi/com_virtuemart/install/override.sql. Hope this is how you have setup your override file.

INSERT IGNORE INTO `#__csvi_availablefields` (`csvi_name`, `component_name`, `component_table`, `component`) VALUES

('order_row', 'order_row', 'orderadvanced', 'com_virtuemart');

Let us give another try cleaning #__csvi_availablefields table. Can you run the below query in your phpmyadmin? Select your database, copy the below query and paste it in SQL tab and click Go. The query will delete the #__csvi_availablefields table first and then creates a new one. After that try to update your available fields. Don't forget to replace #_ with your own table prefix before running the query.

DROP TABLE IF EXISTS `#__csvi_availablefields`;

CREATE TABLE IF NOT EXISTS `#__csvi_availablefields` (

	`csvi_availablefield_id` INT(11) NOT NULL AUTO_INCREMENT,

	`csvi_name` VARCHAR(255) NOT NULL,

	`component_name` VARCHAR(55) NOT NULL,

	`component_table` VARCHAR(55) NOT NULL,

	`component` VARCHAR(55) NOT NULL,

	`action` VARCHAR(6) NOT NULL,

	`isprimary` TINYINT(1) NOT NULL DEFAULT '0',

	PRIMARY KEY (`csvi_availablefield_id`),

	UNIQUE INDEX `component_name_table` (`component_name`, `component_table`, `component`, `action`)

) CHARSET=utf8 COMMENT='Available fields for CSVI';

Hi ,
You can find the file in a previous answer. Please check what I attached in post #54036 - it is the part below. It is just an image actually.

Hello,
Your override fields are not showing in the dropdown list because your update available fields is not working. CSVI fetches any new fields from database tables or override files with update of available fields. Can you post your override.sql file? I will run it here and check on the duplicate entry error.

Hi Tharuna,
Patch is working.
About implementation, I was referring to the fact that current implementation is not covering the case when the 2 addresses are the same. I mean, if ST address is empty, just take BT infos. Anyway, I did it manually in the override of the orderadvanced.
But even if my new field is included in available fields, I cannot select it in the dropdown menu of fields for my custom export template. So if I cannot test if my override with that field is working. How can I get it?

About the last issue regarding this weird fields and table names.. I tried to do as you suggested but I get again the same error. It is like something has been conpromised.

Thank you