Resolved xml advanced export not working - 500 error

Hello,

I have VM 3.0.19, Joomla 3.6.5 and CSVI Pro 7.1.0.
I am creating an "order advanced" Virtuemart xml export, but I got a "500 error" as soon as I try to run the export.
I need the advanced order export because I need to have shipping details (name, address and so on). I filled the "layout" sheet cells according to http://csvimproved.com/support/export/204-virtuemart/930-advanced-order-xml-export but I can't understand if something is wrong.
I attach the log file.

The 500 error doesn't tell us the exact error or what has actually gone wrong. The actual error can be seen in your server error logs and you need to check and tell us the actual error to help you. You can also take a look at 500 Internal server error document which explains other causes for this error.

Furthermore, I need a field that is related to the enumeration of the items in the order. So, if an order contains 4 products, this field shall be 1 for first product, 2 for second one and so on. I did not find any field that do this. Could you help me?

Correct me if i am wrong but your explanation says that you are looking for product ordering in order export. Is it what you are looking for? If yes then there is no such field in VirtueMart database for orders.

Hi Tharuna, thanks for the answer. I will try to follow the procedure you suggested me about error 500 and I will let you know.
About the ordering of the product, I just need an enumeration of products, from 1 to number of different items ordered. I thought this information shall be retrieved somehow. In Vm, opening an order, you can see a column with # symbol and enumeration. Maybe it is possible to use it?

Thanks

Hello,
You can use product_quantity field for number of products ordered in an order. If you don't see this field try updating your available fields.

Actually meaning is different. Let's consider following order:
Product a --> quantity =4
Product b --> quantity = 1
Product c --> quantity = 1
Product d --> quantity = 2

Then I need a field that is:
1 for product a
2 for product b
3 for product c
4 for product d

So, a mere enumeration of references ordered.

Hello,
Ok, As i said earlier there is no direct field to get this. You need to create an override.sql file to add your own available field and create an override for your order export. In the export override file write some custom code to get your custom field value as per your need.

Hi Tharuna,
I have some info about error 500. Actually it is a 200 error, in the attached you can find what I get.

Regarding the procedure related to create override and adding custom field, I got the same problem for both:
/administrator/template/isis/html/com_csvi/ does not exist, as you can see in attached image. And I am using isis as template for administrator. So I cannot proceed further.

How is this possible?

Thank you

Hello,

I have some info about error 500. Actually it is a 200 error, in the attached you can find what I get.

That is the actual error. It looks you have first create an order export template and then changed it to order advanced by changing the operations in template settings. Create a new template for order advanced and you should not be seeing the 500 error.

Regarding the procedure related to create override and adding custom field, I got the same problem for both:
/administrator/template/isis/html/com_csvi/ does not exist, as you can see in attached image. And I am using isis as template for administrator. So I cannot proceed further.

How is this possible?

You need to create these folders and files yourself in the location explained in the above linked documents. As explained these are override files and you need to write your own custom code to get export working as per your needs.

Hi Tharuna,
your solution about error 200 worked - I created an order advanced export template from scratch, no more errors.

Relatively to export template override, I created manually the folders and now I can select it in the override dropdown menu in backend (as a suggestion, in the article you provided me about the override procedure I think you can just specify to create manually these folders).

What is not working yet is the custom field creation using override.sql file. I copied and pasted the code mentioned in the related article, I tried also to modify the ` symbols into ' symbols, but I got the error in the attached picture (in the bottom you can see override.sql file, in the top there is the error provided by CSVI Pro). So what is wrong with this query?

Hello,
There was a small typo in the documentation with table name and so the query is throwing an error. The table name with new CSVI version is `#__csvi_availablefields` and not `#__csvi_available_fields` . This has been corrected in the documentation. Please correct it in your override sql file too.

Hi Tharuna,
your solution worked.

However while I was trying to perform some test, trying also to add this new field manually in the database, I found another problem: the update of available fields is not working anymore. I got an error like the one in the picture. It occurs anytime I try to click on "Update the list" button.
I wonder why error is mentioning those "IVA" fields. Actually, all these are related to "calculation taxes and rules". I never mentioned or put such fields in override.sql file. So I think something is not working properly. I opened the database, no such fields in #__csvi_availablefields table. I tried also to uninstall CSVI Pro, manually delete tables, reinstall it. The same problem occurs. What can I do?

Before this, I tried to run "order advanced export" and I noticed that if customer chooses the shipping address equal to billing address, no field is exported. I mean, in the template field list I put "shipping_address", "shipping_zip", and so on (only shipping fields), but these are empty. They are filled only if shipping address is different from billing address. Is this your implementation of orderadvanced export, or again there is a problem with my configuration?

Just a little minor point: first I was able to set "Use CDATA tag" to "No" value, field by field. Later it was not possible anymore: if I try to set them to "No", as soon as I save its value is restored to "Yes". Do you know why this could happen?

Thank you

Sorry, I forgot to tell that I updated to VM 3.2.0.
I do not know if this information could be helpful.

Hello,

However while I was trying to perform some test, trying also to add this new field manually in the database, I found another problem: the update of available fields is not working anymore. I got an error like the one in the picture. It occurs anytime I try to click on "Update the list" button.
I wonder why error is mentioning those "IVA" fields. Actually, all these are related to "calculation taxes and rules". I never mentioned or put such fields in override.sql file. So I think something is not working properly. I opened the database, no such fields in #__csvi_availablefields table. I tried also to uninstall CSVI Pro, manually delete tables, reinstall it. The same problem occurs. What can I do?

Two things that look weird in your error image, one is the name of the field 'IVA-order-com-virtuemart-export' and other is the column name 'component_name_table'. There is no such column name in CSVI available fields table. You can try to empty your csvi_availablefields table manually from phpmyadmin and then try running the update of available fields. See if there is no such error after that.

Before this, I tried to run "order advanced export" and I noticed that if customer chooses the shipping address equal to billing address, no field is exported. I mean, in the template field list I put "shipping_address", "shipping_zip", and so on (only shipping fields), but these are empty. They are filled only if shipping address is different from billing address. Is this your implementation of orderadvanced export, or again there is a problem with my configuration?

It is not our implementation, it is how VirtueMart stores the data. If shipping address and billing address are different, it stores them as two different rows with address type as ST and BT and so you are seeing shipping data in export. If shipping and billing address are same VirtueMart stores it as BT address type and as single row. This should be the reason you are seeing your shipping address fields empty.

Just a little minor point: first I was able to set "Use CDATA tag" to "No" value, field by field. Later it was not possible anymore: if I try to set them to "No", as soon as I save its value is restored to "Yes". Do you know why this could happen?

Can you try to load the attached patch file and see if this issue is gone?
patch_cdata_2017_03_27.zip

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

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