Resolved Import Record for one product product prices

Hello Marc,

Please also read the documentation Import VirtueMart 2 prices.

You will need to use the Price import and not the product import for this.

The publish up and publish down fields are actually not required but if you use them, CSVI will use them also to find this specific price.

Do I need to import for each price a record ?

Correct, you need to because each price is unique.

Hi Roland,

Joomla! 3.4.3
CSVI Pro 6.1.0

Hello Roland,

I’ve tried to import one price, import process was successful, but price value wasn’t updated. I’ve also tried to import two prices for one product :

In both cases, the import process was successful, but the data’s wasn’t updated.

What Am I doing wrong ?

Please find attached the Debug file or the import process of two prices.

Please find also attached the Template settings (21082015 - EDIT TEMPLATE 1 to 3)).

In order to be sure, that you understand, what I would like to achieve, let me explain it again.

As you know, in the VM Product edit form, you can define several prices.

For one product I have defined 2 prices (please see attached the Product settings (21082015 - PRODUCT EDIT 1 & 2)) :

1. one without “product_price_publish_up” and “product_price_publish_down” dates,
2. one with a “product_price_publish_up” = 2015-08-21 and “product_price_publish_down” = 2015-08-22.

I would like to update the two prices with the following datas :
[table]
[tr]
[td]Article ID[/td]
[td]shopper_group_name[/td]
[td]selling price[/td]
[td]product_currency[/td]
[td]price_quantity_start[/td]
[td]price_quantity_end[/td]
[td]Action du[/td]
[td]Action au[/td]
[/tr]
[tr]
[td]10192605[/td]
[td]0[/td]
[td]13.26625[/td]
[td]27[/td]
[td]0[/td]
[td]0[/td]
[td] [/td]
[td] [/td]
[/tr]
[tr]
[td]10192605[/td]
[td]0[/td]
[td]11.26625[/td]
[td]27[/td]
[td]0[/td]
[td]0[/td]
[td]2015-08-21 [/td]
[td]2015-08-22[/td]
[/tr]
[/table]

The 2 Records which belong to Product ID 362 are in the virtuemart_product_prices table :

`jos_virtuemart_product_prices` (`virtuemart_product_price_id`, `virtuemart_product_id`, `virtuemart_shoppergroup_id`, `product_price`, `override`, `product_override_price`, `product_tax_id`, `product_discount_id`, `product_currency`, `product_price_publish_up`, `product_price_publish_down`, `price_quantity_start`, `price_quantity_end`, `created_on`, `created_by`, `modified_on`, `modified_by`, `locked_on`, `locked_by`) VALUES
(274, 362, 0, '3.266250', 0, '0.00000', 2, 0, 27, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 0, 0, '2015-08-20 09:27:17', 516, '2015-08-21 07:01:01', 516, '0000-00-00 00:00:00', 0),
(435, 362, 0, '2.266250', 0, '0.00000', 2, 0, 27, '2015-08-21 00:00:00', '2015-08-28 00:00:00', 0, 0, '2015-08-20 15:36:44', 516, '2015-08-20 15:38:19', 516, '0000-00-00 00:00:00', 0);

Thanks in advance for your suggestions.

Cheers,

Marc

Hello Marc,

First issue I spot is the shopper_group_name, this cannot be 0 unless you actually have a shopper group called 0. If you don't have any shopper_group_name you can replace the field with the virtuemart_shoppergroup_id field and keep the 0 value.

This is CSVI looking for a price:

SELECT `virtuemart_product_price_id` FROM `jos_virtuemart_product_prices` WHERE `virtuemart_product_id` = 362 AND (`virtuemart_shoppergroup_id` = 0 OR `virtuemart_shoppergroup_id` IS NULL) AND (`product_currency` = 27 OR `product_currency` IS NULL) AND `price_quantity_start` = 0 AND `price_quantity_end` = 0 AND (`product_price_publish_up` = '0000-00-00 00:00:00' OR `product_price_publish_up` IS NULL) AND (`product_price_publish_down` = '0000-00-00 00:00:00' OR `product_price_publish_down` IS NULL)

this will find the first price you posted. This is then updated by CSVI

UPDATE `jos_virtuemart_product_prices` SET `virtuemart_product_id`='362',`virtuemart_shoppergroup_id`='0',`product_price`='3.266250',`override`='0',`product_override_price`='0.00000',`product_tax_id`='2',`product_discount_id`='0',`product_currency`='27',`product_price_publish_up`='0000-00-00 00:00:00',`product_price_publish_down`='0000-00-00 00:00:00',`price_quantity_start`='0',`price_quantity_end`='0',`created_on`='2015-08-20 09:27:17',`created_by`='516',`modified_on`='2015-08-21 06:58:52',`modified_by`='516',`locked_on`='0000-00-00 00:00:00',`locked_by`='0' WHERE `virtuemart_product_price_id`='274'

Notice the ID is 274 and matches the ID you posted.

If you want to change the price, you need to use the field product_price_new with the new price. So I think if you change the product_price to product_price_new field, the update should be OK.

Hi Roland,
Thank you for your explanations.

First issue I spot is the shopper_group_name, this cannot be 0 unless you actually have a shopper group called 0. If you don't have any shopper_group_name you can replace the field with the virtuemart_shoppergroup_id field and keep the 0 value.

I have defined the correct shopper group.

If you want to change the price, you need to use the field product_price_new with the new price. So I think if you change the product_price to product_price_new field, the update should be OK.

I have changed the field name to product_price_new.

I was able to update the first record (please see previous post -> product_price_new = 13.26625), but the second one has not been updated (please see previous post -> product_price_new = 11.26625).

Records to Import / update :

Article ID;shopper_group_name;product_price_new;product_currency;price_quantity_start;price_quantity_end;Action du;Action au

10192605;2;13.26625;27;0;0;;;

10192605;2;11.26625;27;0;0;2015-08-21;2015-08-22

Do you have a clue, why the second record wasn't updated ?

My question is the following :

The webshop will be feed on a regular basis with data delivered by a data provider.

1. In general, we will have only one price per article. So it will be easy to handle if we want to update the price,


2. Sometimes we will have to add a second price (discount) for the same article to apply over a defined time period (“product_price_publish_up” ["Action du" field from the import file] and “product_price_publish_down” ["Action au"] dates),


3. Sometimes, we will have to update the second price (if we already have a "second price" (discount price) defined for a specific article) and its “product_price_publish_up” ["Action du" field from the import file] and “product_price_publish_down” ["Action au"] dates.

What should be the best way to meet my needs ? Should the data provider deliver a file with the prices to update ("normal" price + discount price (if discount price already exists)).

And if there's no "second price" (discount price) existing for the same article,deliver a file with the discount price to add ?

Thanks a lot in advance for your tips.

Cheers,

Marc

Hello Marc,

Do you have a clue, why the second record wasn't updated ?

I need to have the new debug log because the old one wasn't using the product_price_new field.

What should be the best way to meet my needs ?

Let's look at your 3 requirements, the first one is nullified by the second requirement. If you sometimes have discounted prices, you always have more than 1 price per product. So this will need to be taken as a start point. This way you know you always need to use the Price import.

For the price import it is clear which fields are needed and as long as you always fill these fields correctly, you can add/update existing prices. Does that make sense?

Hey Roland,

Thanks a lot for your reply.

It make certainly sens Roland ;-)

I have made again a test.

Results :

Line Action Result Message
1 Updated Success Product price updated
2 Updated Success Product price updated

Both records should have been updated with the content of the import file :

[CODE]Article ID;shopper_group_name;product_price_new;product_currency;price_quantity_start;price_quantity_end;Action du;Action au
10192605;2;18.26625;27;0;0;;;
10192605;2;14.26625;27;0;0;2015-09-07;2015-09-13[/CODE]

In fact both records should have been updated with the new "normal" price and the new discount price + publish up and down dates.

The normal VM price was originally "16.26625" -> the new price should have been "18.26625"
The discount VM price was originally "11.26625" -> the new price should have been "14.26625"
The “product_price_publish_up” ["Action du" field from the import file] and “product_price_publish_down” ["Action au"] was originally "31/08/15" and "06/09/15" -> the new date should have been "07/09/2015" and "13/09/2015"

SQL DUMP from the 2 records in the jos_virtuemart_product_prices table :

[CODE]
(437, 362, 0, '11.266250', 0, '0.00000', 0, 0, 27, '2015-08-31 00:00:00', '2015-09-06 00:00:00', 0, 0, '2015-08-25 11:09:17', 516, '2015-08-25 11:18:52', 516, '0000-00-00 00:00:00', 0),
(438, 362, 0, '16.266250', 0, '0.00000', 1, 0, 27, '0000-00-00 00:00:00', '0000-00-00 00:00:00', 0, 0, '2015-08-25 11:11:28', 0, '2015-08-25 11:19:39', 516, '0000-00-00 00:00:00', 0);
[/CODE]

Perhaps the debug log file attached will help !

Cheers,

Marc

Hello Marc,

The “product_price_publish_up” ["Action du" field from the import file] and “product_price_publish_down” ["Action au"] was originally "31/08/15" and "06/09/15" -> the new date should have been "07/09/2015" and "13/09/2015"

This is where the issue is. CSVI can't update it this way. You need to specify the old date so CSVI can find the price. Look at this line:

2015-08-25	11:19:39	2	[DEBUG]	Finding a product_price_id

2015-08-25	11:19:39	2	[QUERY]	SELECT `virtuemart_product_price_id` FROM `jos_virtuemart_product_prices` WHERE `virtuemart_product_id` = 362 AND (`virtuemart_shoppergroup_id` = 0 OR `virtuemart_shoppergroup_id` IS NULL) AND (`product_currency` = 27 OR `product_currency` IS NULL) AND `price_quantity_start` = 0 AND `price_quantity_end` = 0 AND (`product_price_publish_up` = '2015-09-06 22:00:00' OR `product_price_publish_up` IS NULL) AND (`product_price_publish_down` = '2015-09-12 22:00:00' OR `product_price_publish_down` IS NULL)

this is where CSVI is trying to find the price stored in the database. To be able to find it, it requires the old publish dates.

Updating the publish dates of a discounted price is not possible. The only way to do that would be to delete the old discounted price and add a new discounted price using the price_delete field. This still requires the data of the old discounted price to be able to delete it. You could just use the product_sku and product_price to delete them and after that add the prices.

Hey Roland,
Ok understood now, thank you I'm trying to do it and I'll keep you posted. Marc

Hi Roland,

As told before I would like to update the second price of my product (discount price).

Please see attached doc PRODUCT INFORMATION INITAL.JPEG which shows the initial product information, where you can see the 2 defined prices.

As explained before, the first price as no publish up and publish down values. The second price as publish up and publish down values.

You told me that

Updating the publish dates of a discounted price is not possible. The only way to do that would be to delete the old discounted price and add a new discounted price using the price_delete field. This still requires the data of the old discounted price to be able to delete it. You could just use the product_sku and product_price to delete them and after that add the prices.

So I've created a template with the following template fields :

[table]
[tr]
[td]Template[/td]
[td]Field name[/td]
[td]Your field[/td]
[td]Default value[/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]product_sku[/td]
[td]virtuemart_product_id[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]virtuemart_shoppergroup_id[/td]
[td]virtuemart_shoppergroup_id[/td]
[td]2[/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]product_price[/td]
[td]product_price[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]product_tax_id[/td]
[td]skip[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]override[/td]
[td]skip[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]product_override_price[/td]
[td]skip[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]product_discount_id[/td]
[td]skip[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]product_currency[/td]
[td]skip[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]product_price_publish_up[/td]
[td]product_price_publish_up[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]product_price_publish_down[/td]
[td]product_price_publish_down[/td]
[td][/td]
[/tr]
[tr]
[td]Delete Discount Price[/td]
[td]price_delete[/td]
[td]product_price_publish_down[/td]
[td]price_delete[/td]
[/tr]
[/table]

Ps: please see attached file TEMPLATE FIELDS.txt, if some fields are hidden.

The file to import was the following :

[CODE]
virtuemart_product_id;virtuemart_shoppergroup_id;product_price;override;product_override_price;product_tax_id;product_discount_id;product_currency;product_price_publish_up;product_price_publish_down;price_delete
10192605;2;8.26625;;;;;;2015-08-31 00:00:00;2015-09-06 00:00:00;Y
[/CODE]

I launched the import process. A record was successfully deleted. But the wrong one.

Please see attached doc PRODUCT INFORMATION AFTER IMPORT DELETE.JPEG which shows the product information, where you can see that the second (which should have been deleted) still exists.

I would appreciate if you could help me.

Cheers,

Marc

Hello Marc,

The fields you mentioned in Your Field are not being used with your import. This is only for XML or if you use the combine plugin (see the Help button for more details).

Let's see, CSVI looks for the price

SELECT `virtuemart_product_price_id` FROM `jos_virtuemart_product_prices` WHERE `virtuemart_product_id` = 362 AND (`virtuemart_shoppergroup_id` = 2 OR `virtuemart_shoppergroup_id` IS NULL) AND (`product_currency` = 27 OR `product_currency` IS NULL) AND `price_quantity_start` = 0 AND `price_quantity_end` = 0 AND (`product_price_publish_up` = '2015-08-30 22:00:00' OR `product_price_publish_up` IS NULL) AND (`product_price_publish_down` = '2015-09-05 22:00:00' OR `product_price_publish_down` IS NULL)

So it looks like it found the first price because the publish dates did not match the dates of the second price but the NULL value matched the first price. The 2-hour difference is caused by the timezone calculation. If you set the timestamp to 02:00:00, I think it will delete the correct price.

This is something to think about, whether or not a timezone check should be done on this. It is done when inserting the date/time, so it should be the same result when deleting the same value. How did you import the publish up and down dates or were they set via VirtueMart?

Hi Roland,

Thank you for you reply. Joomla timezone is set to Paris which is correct. I have setted up the publish up and publish down values in virtuemart. And I have created the Import file in order to test.

Regarding the 2 hour difference, this is strange. Dates in the jos_virtuemart_product_prices :

439, 362, 2, '8.266250', 0, '0.00000', 1, 0, 27, '2015-08-31 00:00:00', '2015-09-06 00:00:00', 0, 0, '2015-08-25 14:27:29', 516, '2015-08-25 16:35:28', 516, '0000-00-00 00:00:00', 0);

and dates in the file I'm importing to delete the record have the same values :

virtuemart_product_id;virtuemart_shoppergroup_id;product_price;override;product_override_price;product_tax_id;product_discount_id;product_currency;product_price_publish_up;product_price_publish_down;price_delete
10192605;2;8.26625;;;;;;2015-08-31 00:00:00;2015-09-06 00:00:00;Y

I just checked the server time which is correct.

Where did the time calculation take place, in CSVI ?

Cheers,

Marc

Hello Marc,

The calculation takes place on the price import routine. Attached is a modified version, that doesn't do the timezone calculation. You can load the patch file and the times should no longer be changed.

Hi Roland,

Thank you very much, it works now ! I was able to delete the appropriate discount price, now I will import the new discount price. I'll keep you posted.

Cheers,

Marc

Hi Roland,

As told before, I was able to delete the discount price.

Now I would like to add a discount price + publish up and publish down dates to my existing product.

In your previous answer, you told me :

Updating the publish dates of a discounted price is not possible. The only way to do that would be to delete the old discounted price ... using the price_delete field.

I have done that and it works

... and add a new discounted ...

Since I need to insert among others the publish up and publish down dates, I have created a Import Template with the following fields :

[table]
[tr]
[td]Template[/td]
[td]Field name[/td]
[td]Your field[/td]
[td]Default value[/td]
[/tr]
[tr]
[td]Import Discount Price[/td]
[td]product_sku[/td]
[td]virtuemart_product_id[/td]
[td][/td]
[/tr]
[tr]
[td]Import Discount Price[/td]
[td]virtuemart_shoppergroup_id[/td]
[td]virtuemart_shoppergroup_id[/td]
[td][/td]
[/tr]
[tr]
[td]Import Discount Price[/td]
[td]product_price[/td]
[td]product_price[/td]
[td][/td]
[/tr]
[tr]
[td]Import Discount Price[/td]
[td]product_tax_id[/td]
[td]product_tax_id[/td]
[td][/td]
[/tr]
[tr]
[td]Import Discount Price[/td]
[td]product_price_publish_up[/td]
[td]product_price_publish_up[/td]
[td][/td]
[/tr]
[tr]
[td]Import Discount Price[/td]
[td]product_price_publish_down[/td]
[td]product_price_publish_down[/td]
[td][/td]
[/tr]
[/table]

And created the following test import discount file :

[CODE]
virtuemart_product_id;virtuemart_shoppergroup_id;product_price;product_tax_id;product_price_publish_up;product_price_publish_down
10192605;2;8.26625;2;2015-08-31 00:00:00;2015-09-06 00:00:00
[/CODE]

Data was correctely imported, but discount price not added to the product.

I have read carefully your Import VirtueMart 2 prices tutorial. I could find how to update or to delete an existing price, but there's nothing explaining how to add for example a discount price to an existing product.

I would appreciate, if you could explain me how to.

Thanks in advance.

Cheers,

Hello Marc,

Your setup looks fine but without the debug log I can't see what is happening, so you will need to post the debug log.

The only thing you may want to change as it is confusing is that your file has the virtuemart_product_id field while it is the product_sku field. Not related to the import but seems inconsistent to me.