SOLVED: Specific price error after update from 1.6.0.9 to 1.6.1.1

[cerovic]

Hello.

I made update from 1.6.0.9 to 1.6.1.1. . I made test update and everything was OK. Then I made real update and everything looked like OK, but I recognized one problem with specific prices.

I can see all specific prices in administration in product page ,see attachement – administration. However the specific price is there, it has no affect to price. So if I have product with price 10  € and have 50% diccount, the final price of product is 10€ , however I can see the specific price with 50% discount on product administration.  

Old shop on 1.6.0.9 and new shop 1.6.1.1. are also in attachement.

 

Can anybody have any idea what could be wrong? 

Edited September 22, 2015 by cerkoxxl (see edit history)

[bluekachina]

Same problem?

https://www.prestashop.com/forums/topic/467035-princing-problem-after-upgrading-to-1611/

[musicmaster]

There is one thing that the backoffice doesn't show: the shop id. Specific prices can be shop specific. That is a possible source of problems.

 

The way to go is to create a new specific price (I assume that that does work) and then compare that in the database with the upgrade specifics that don't work. 

 

You can also use Triple Edit instead of Phpmyadmin to have a better look at how it is implemented in the database. Saving changes on specific prices in Triple Edit requires buying a module, but just having a look at the data is free.

[sagenterprises]

We are also facing this error. ... that specific Prices are not working for any of the products... (Specific prices reflect in back-end, but does'nt actually work in front end, which is creating much of the concerns.... Please help)

[cerovic]

There is one thing that the backoffice doesn't show: the shop id. Specific prices can be shop specific. That is a possible source of problems.

 

The way to go is to create a new specific price (I assume that that does work) and then compare that in the database with the upgrade specifics that don't work. 

 

You can also use Triple Edit instead of Phpmyadmin to have a better look at how it is implemented in the database. Saving changes on specific prices in Triple Edit requires buying a module, but just having a look at the data is free.

Thanks musicmaster.

Generally, you were right. It is probably problem of Shop ID, since I have just 1 shop (no multishop) with ID 1 an in specific price table is all specific prices assigned to sho ID 0, see attachment. 

However I made new specific price (the last one in attachement) and the system also assigned this specific price to shop ID 0. So something has gone wrong here after update to 1.6.1.1.  

[cerovic]

To tel the true, I looked at the database of old shop (1.6.0.9) and there were also in column is_shop all 0, so it is same that in 1.6.1.1 and in 1.6.0.9 it worked and after update to 1.6.1.1 not. 

[musicmaster]

Shop id =0 usually means "all shops". So that doesn't have to be a problem.

 

You didn't answer the question about making a new discount:

 - if you make a new discount in 1.6.1.1: does that work?

 - if it works: can you find any difference in the database with the old one that don't work?

[cerovic]

No,also new discount does not work. And it has no difference in specific_price table.

I compared also old table (from 1.6.0.9) to new one (from 1.6.1.1) and no difference. 

[bluekachina]

This looks like the same problem. Though it should be fixed on 1.6.1.1
http://forge.prestashop.com/browse/PSCSX-6430

[cerovic]

I just asking my self one think. When I open product in administration, I can see that for example product ID 1 has specific price with 10%. I see it in Price tab. So the system know to assign this discount to correct product. However it do not decrease the price, and it do not show in front office.

 

Also all product are assigned to Prices-Drop category see http://www.sportove-volanty.sk/sk/prices-drop .

 

So it is confusing, that system know, that it is product in sale, it know to assign the discount in BO, but it do not decrease the price in BO and FO.  

[seba4leon]

I have the same problem in my multi-store shop.  In non multi-store shop  specific prices are OK.

[seba4leon]

the solution:

ALTER TABLE `ps_specific_price` ENGINE=MyISAM;

ALTER IGNORE TABLE `ps_specific_price` ADD UNIQUE KEY `id_product_2` (`id_cart`,`id_product`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`id_customer`,`id_product_attribute`,`from_quantity`,`id_specific_price_rule`,`from`,`to`);

 

in my shop this works

Edited September 12, 2015 by seba4leon (see edit history)

[cerovic]

Thank you seba4leon. I do not belive to Good but Good bless you  .

 

This works. I do know everithing but, what this code means. What was wrong. I compared table before application your code and after, and I can not see any different value. I just see that before the storage engine of table was InnoDB and after it is MyISAM. This was the reason? 

[cerovic]

Seba4leon: since it works on single shop and does not in mustistore, do you have "0" in shop_id? 

[seba4leon]

there is problem with index "id_product_2" to this table, you can show

SHOW INDEXES FROM `ps_specific_price`  

 

Myisam is only to work correctly alter table command

 

Alter ignore table deletes duplicated keys in the table.

Edited September 14, 2015 by seba4leon (see edit history)

[harshagarwal5]

Hi I upgraded from 1.6.0.9 to 1.6.1.1... all things are working fine expect that the shop is not showing final selling price, it is showing the tax exclusive price.. the final selling price shown but only at the time of checkout.. but it is not showing in product_list or product pages.. kindly help urgently..

 

 

there is problem with index "id_product_2" to this table, you can show

SHOW INDEXES FROM `ps_specific_price`  

 

Myisam is only to work correctly alter table command

 

Alter ignore table deletes duplicated keys in the table.

[cerovic]

can you sent a link to you shop? 

 

Hi I upgraded from 1.6.0.9 to 1.6.1.1... all things are working fine expect that the shop is not showing final selling price, it is showing the tax exclusive price.. the final selling price shown but only at the time of checkout.. but it is not showing in product_list or product pages.. kindly help urgently..

can you send a link to your shop

[Antakarana]

Hi!

 

I'm not sure if this is the same problem that I have... I'm using Ps 1.6.1.1

 

Some of our products have general price rules, those price rules are applied to the whole products from the same manufacturer.

 

For example: If the product is from Manufacturer XXX then it must have  a 15% discount ....

 

However some specifc products must not have this general price rules, so to avoid the using of the general price rule we just createed an empty specific price rule... and that was enought.

 

For example: if the product is from Manufacturer XXX then it should have the 15% discount, however by applying an "empty" specific price rule then the product will have no discounts.

 

 

Unfortunately since we have update to PS 1.6.1.1 we can not apply empty specific price rules (we get an error), so the producs always have the general price rules applied to the manufacturer.

 

Is there any way to fix that?

 

Thanks

Edited September 17, 2015 by Antakarana (see edit history)

[cerovic]

The only idea I have is to completely cancel the general price rule and makes the one. You do not have  ad this rules to all product from one manufactor. You can directly select product assigned to this discount. 

 

Hi!

 

I'm not sure if this is the same problem that I have... I'm using Ps 1.6.1.1

 

Some of our products have general price rules, those price rules are applied to the whole products from the same manufacturer.

 

For example: If the product is from Manufacturer XXX then it must have  a 15% discount ....

 

However some specifc products must not have this general price rules, so to avoid the using of the general price rule we just createed an empty specific price rule... and that was enought.

 

For example: if the product is from Manufacturer XXX then it should have the 15% discount, however by applying an "empty" specific price rule then the product will have no discounts.

 

 

Unfortunately since we have update to PS 1.6.1.1 we can not apply empty specific price rules (we get an error), so the producs always have the general price rules applied to the manufacturer.

 

Is there any way to fix that?

 

Thanks

[Antakarana]

The only idea I have is to completely cancel the general price rule and makes the one. You do not have  ad this rules to all product from one manufactor. You can directly select product assigned to this discount. 

thanks for your answer... however I'm not sure if I have correctly understood your suggestions.

As far as I see you are suggesting to cancel all the "general manufacturer price rules" and then apply a custom discount for each product from the same manufacturer...

Well that is is not a good choice for us, because we have about 1300 products and some manufacturers have more than 100 products... so obviously it is too much work to manually change each discount one by one.

We need to find a simple way to avoid the application of the general price rules on some specific products... Just like we wer doing on PS1.6.0.9 by simply adding an empty "specific price" to the product.

[cerovic]

thanks for your answer... however I'm not sure if I have correctly understood your suggestions.

As far as I see you are suggesting to cancel all the "general manufacturer price rules" and then apply a custom discount for each product from the same manufacturer...

Well that is is not a good choice for us, because we have about 1300 products and some manufacturers have more than 100 products... so obviously it is too much work to manually change each discount one by one.

We need to find a simple way to avoid the application of the general price rules on some specific products... Just like we wer doing on PS1.6.0.9 by simply adding an empty "specific price" to the product.

So, sorry. No advice.

[cobrax13]

the solution:

ALTER TABLE `ps_specific_price` ENGINE=MyISAM;

ALTER IGNORE TABLE `ps_specific_price` ADD UNIQUE KEY `id_product_2` (`id_cart`,`id_product`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`id_customer`,`id_product_attribute`,`from_quantity`,`id_specific_price_rule`,`from`,`to`);

 

in my shop this works

 

Hello I have this problem in my prestashop 1.6.1.2, I update it from 1.6.0.9 and now i don`t see the specific prices. I have execute the SQL sentence but it's do nothing. Please I need your help.

Thanks

[satrick]

I totaly agree with cobrax13. I upgrated from 1.6.1.1 to 1.6.1.2  and that sql solution doesn't work at all. It say, that id_product_2 unique key already exist. 

[musicmaster]

If it already exists you should drop it first

ALTER TABLE `ps_specific_price` DROP KEY `id_product_2`;

[satrick]

 

If it already exists you should drop it first

ALTER TABLE `ps_specific_price` DROP KEY `id_product_2`;

 

I know, already did that, but with same result after creating it after that again

[musicmaster]

Of course 1.6.1.2 is a different story. Look in the install\upgrade\sql\1.6.1.2.sql file and you will see that in that version index2 is as follows:

ALTER TABLE `ps_specific_price` ADD UNIQUE KEY `id_product_2` (`id_product`,`id_product_attribute`,`id_customer`,`id_cart`,`from`,`to`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`from_quantity`,`id_specific_price_rule`);

 

Yet I don't know whether this really makes a difference. These are the same fields, just in a different order. But you can try it.

[satrick]

 

Of course 1.6.1.2 is a different story. Look in the install\upgrade\sql\1.6.1.2.sql file and you will see that in that version index2 is as follows:

ALTER TABLE `ps_specific_price` ADD UNIQUE KEY `id_product_2` (`id_product`,`id_product_attribute`,`id_customer`,`id_cart`,`from`,`to`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`from_quantity`,`id_specific_price_rule`);

 

Yet I don't know whether this really makes a difference. These are the same fields, just in a different order. But you can try it.

 

 

Tried, no progress

btw.. to avoid unnecessary questions.. i am always erasing my cache, removing product specific price (discount) and then creating it once again

[satrick]

ok i compared all major files (folders: core, classes,...) in problematic eshop on 1.6.1.2 with fresh instalation of 1.6.1.2 where discounts are working and there are no difference. So problem has to be in database somewhere

[musicmaster]

I still am puzzled about what error you see. Do you still see a "key exists" error after you drop that index? That would be very strange and require direct investigation of what happens in the database.

 

You can compare your databases too. Export the databases of two shops and compare the sql files with a tool like WinMerge... It is a rather messy work as most differences are just different dates.

[satrick]

I still am puzzled about what error you see. Do you still see a "key exists" error after you drop that index? That would be very strange and require direct investigation of what happens in the database.

 

You can compare your databases too. Export the databases of two shops and compare the sql files with a tool like WinMerge... It is a rather messy work as most differences are just different dates.

 

That is misunderstanding. Update 1.6.1.2 already gave me id_product_2 key so I successfully deleted it and created it once again. There was no problem with deleting key id_product_2. But if you are really desperate as me, you can simply rollback old version of specificprice.php from classes and then everything is working

[satrick]

Ok I have discovered that problem is in getSpecificPrice function in classes/specificprice.php 

 

You have to replace SQL select. So:

 

Not working select:

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price`

WHERE

                `id_shop` '.self::formatIntInQuery(0, $id_shop).' AND

                `id_currency` '.self::formatIntInQuery(0, $id_currency).' AND

                `id_country` '.self::formatIntInQuery(0, $id_country).' AND

                `id_group` '.self::formatIntInQuery(0, $id_group).' '.$query_extra.'

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

working select:

           $now = date('Y-m-d H:i:00');

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price` USE INDEX (id_product_2)

WHERE `id_product` IN (0, '.(int)$id_product.')

AND `id_product_attribute` IN (0, '.(int)$id_product_attribute.')

AND `id_shop` IN (0, '.(int)$id_shop.')

AND `id_currency` IN (0, '.(int)$id_currency.')

AND `id_country` IN (0, '.(int)$id_country.')

AND `id_group` IN (0, '.(int)$id_group.')

AND `id_customer` IN (0, '.(int)$id_customer.')

AND

(

(`from` = \'0000-00-00 00:00:00\' OR \''.$now.'\' >= `from`)

AND

(`to` = \'0000-00-00 00:00:00\' OR \''.$now.'\' <= `to`)

)

AND id_cart IN (0, '.(int)$id_cart.')

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

Edited November 1, 2015 by satrick (see edit history)

[cobrax13]

Perfect. Now with this change my web is running well. Thanks for your help

[indycz]

Excellent! Change sql helped me too!

[koconborz]

 

Ok I have discovered that problem is in getSpecificPrice function in classes/specificprice.php 

 

You have to replace SQL select. So:

 

Not working select:

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price`

WHERE

                `id_shop` '.self::formatIntInQuery(0, $id_shop).' AND

                `id_currency` '.self::formatIntInQuery(0, $id_currency).' AND

                `id_country` '.self::formatIntInQuery(0, $id_country).' AND

                `id_group` '.self::formatIntInQuery(0, $id_group).' '.$query_extra.'

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

working select:

           $now = date('Y-m-d H:i:00');

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price` USE INDEX (id_product_2)

WHERE `id_product` IN (0, '.(int)$id_product.')

AND `id_product_attribute` IN (0, '.(int)$id_product_attribute.')

AND `id_shop` IN (0, '.(int)$id_shop.')

AND `id_currency` IN (0, '.(int)$id_currency.')

AND `id_country` IN (0, '.(int)$id_country.')

AND `id_group` IN (0, '.(int)$id_group.')

AND `id_customer` IN (0, '.(int)$id_customer.')

AND

(

(`from` = \'0000-00-00 00:00:00\' OR \''.$now.'\' >= `from`)

AND

(`to` = \'0000-00-00 00:00:00\' OR \''.$now.'\' <= `to`)

)

AND id_cart IN (0, '.(int)$id_cart.')

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

Thank you!

This solution solved my problem!

[moscariellop]

 

Ok I have discovered that problem is in getSpecificPrice function in classes/specificprice.php 

 

You have to replace SQL select. So:

 

Not working select:

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price`

WHERE

                `id_shop` '.self::formatIntInQuery(0, $id_shop).' AND

                `id_currency` '.self::formatIntInQuery(0, $id_currency).' AND

                `id_country` '.self::formatIntInQuery(0, $id_country).' AND

                `id_group` '.self::formatIntInQuery(0, $id_group).' '.$query_extra.'

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

working select:

           $now = date('Y-m-d H:i:00');

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price` USE INDEX (id_product_2)

WHERE `id_product` IN (0, '.(int)$id_product.')

AND `id_product_attribute` IN (0, '.(int)$id_product_attribute.')

AND `id_shop` IN (0, '.(int)$id_shop.')

AND `id_currency` IN (0, '.(int)$id_currency.')

AND `id_country` IN (0, '.(int)$id_country.')

AND `id_group` IN (0, '.(int)$id_group.')

AND `id_customer` IN (0, '.(int)$id_customer.')

AND

(

(`from` = \'0000-00-00 00:00:00\' OR \''.$now.'\' >= `from`)

AND

(`to` = \'0000-00-00 00:00:00\' OR \''.$now.'\' <= `to`)

)

AND id_cart IN (0, '.(int)$id_cart.')

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

 

It works also for me. I had that iussue after upgrading from 1.6.0.9 to 1.6.1.2

[PEPPE83]

 

Ok I have discovered that problem is in getSpecificPrice function in classes/specificprice.php 

 

You have to replace SQL select. So:

 

Not working select:

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price`

WHERE

                `id_shop` '.self::formatIntInQuery(0, $id_shop).' AND

                `id_currency` '.self::formatIntInQuery(0, $id_currency).' AND

                `id_country` '.self::formatIntInQuery(0, $id_country).' AND

                `id_group` '.self::formatIntInQuery(0, $id_group).' '.$query_extra.'

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

working select:

           $now = date('Y-m-d H:i:00');

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price` USE INDEX (id_product_2)

WHERE `id_product` IN (0, '.(int)$id_product.')

AND `id_product_attribute` IN (0, '.(int)$id_product_attribute.')

AND `id_shop` IN (0, '.(int)$id_shop.')

AND `id_currency` IN (0, '.(int)$id_currency.')

AND `id_country` IN (0, '.(int)$id_country.')

AND `id_group` IN (0, '.(int)$id_group.')

AND `id_customer` IN (0, '.(int)$id_customer.')

AND

(

(`from` = \'0000-00-00 00:00:00\' OR \''.$now.'\' >= `from`)

AND

(`to` = \'0000-00-00 00:00:00\' OR \''.$now.'\' <= `to`)

)

AND id_cart IN (0, '.(int)$id_cart.')

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

 

Thank you!!!!! this is the solution for my problem!

 

I get the problem after upgrade my shop from 1.6.0.14 to 1.6.1.2

[logistics.gna]

 

Ok I have discovered that problem is in getSpecificPrice function in classes/specificprice.php 

 

You have to replace SQL select. So:

 

Not working select:

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price`

WHERE

                `id_shop` '.self::formatIntInQuery(0, $id_shop).' AND

                `id_currency` '.self::formatIntInQuery(0, $id_currency).' AND

                `id_country` '.self::formatIntInQuery(0, $id_country).' AND

                `id_group` '.self::formatIntInQuery(0, $id_group).' '.$query_extra.'

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

working select:

           $now = date('Y-m-d H:i:00');

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price` USE INDEX (id_product_2)

WHERE `id_product` IN (0, '.(int)$id_product.')

AND `id_product_attribute` IN (0, '.(int)$id_product_attribute.')

AND `id_shop` IN (0, '.(int)$id_shop.')

AND `id_currency` IN (0, '.(int)$id_currency.')

AND `id_country` IN (0, '.(int)$id_country.')

AND `id_group` IN (0, '.(int)$id_group.')

AND `id_customer` IN (0, '.(int)$id_customer.')

AND

(

(`from` = \'0000-00-00 00:00:00\' OR \''.$now.'\' >= `from`)

AND

(`to` = \'0000-00-00 00:00:00\' OR \''.$now.'\' <= `to`)

)

AND id_cart IN (0, '.(int)$id_cart.')

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

 

Mismo problema desde una versión anterior y también funciona 1.5. → 1.6.1         

Edited November 19, 2015 by logistics.gna (see edit history)

[satrick]

thank you guys

[totallighting.sk]

Thx! Great jobe done, it`s working on PS 1612!

[karthikcs]

I'm using version 1.6.1.2.  it worked after running following SQL queries and modifying the classes/SpecificPrice.php

 

• ALTER TABLE `ps_specific_price` ENGINE=MyISAM;
• ALTER TABLE `ps_specific_price` DROP KEY `id_product_2`;
• ALTER IGNORE TABLE `ps_specific_price` ADD UNIQUE KEY `id_product_2` (`id_cart`,`id_product`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`id_customer`,`id_product_attribute`,`from_quantity`,`id_specific_price_rule`,`from`,`to`);

Edited November 25, 2015 by karthikcs (see edit history)

[satrick]

I'm using version 1.6.1.2.  Even after running 

 

• ALTER TABLE `ps_specific_price` ENGINE=MyISAM;
• ALTER TABLE `ps_specific_price` DROP KEY `id_product_2`;
• ALTER IGNORE TABLE `ps_specific_price` ADD UNIQUE KEY `id_product_2` (`id_cart`,`id_product`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`id_customer`,`id_product_attribute`,`from_quantity`,`id_specific_price_rule`,`from`,`to`);

 

I'm facing the same issue, FO doesn't show specific prices (discounts) 

 

Apply my fix and everything will be alright

[karlangas77]

 

Ok I have discovered that problem is in getSpecificPrice function in classes/specificprice.php 

 

You have to replace SQL select. So:

 

Not working select:

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price`

WHERE

                `id_shop` '.self::formatIntInQuery(0, $id_shop).' AND

                `id_currency` '.self::formatIntInQuery(0, $id_currency).' AND

                `id_country` '.self::formatIntInQuery(0, $id_country).' AND

                `id_group` '.self::formatIntInQuery(0, $id_group).' '.$query_extra.'

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

working select:

           $now = date('Y-m-d H:i:00');

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price` USE INDEX (id_product_2)

WHERE `id_product` IN (0, '.(int)$id_product.')

AND `id_product_attribute` IN (0, '.(int)$id_product_attribute.')

AND `id_shop` IN (0, '.(int)$id_shop.')

AND `id_currency` IN (0, '.(int)$id_currency.')

AND `id_country` IN (0, '.(int)$id_country.')

AND `id_group` IN (0, '.(int)$id_group.')

AND `id_customer` IN (0, '.(int)$id_customer.')

AND

(

(`from` = \'0000-00-00 00:00:00\' OR \''.$now.'\' >= `from`)

AND

(`to` = \'0000-00-00 00:00:00\' OR \''.$now.'\' <= `to`)

)

AND id_cart IN (0, '.(int)$id_cart.')

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

 

wooo thanks thanks thanks !!!!!  

works for me in PS 1.6.1.2

happy new year for everyone!!!

[Bonifero]

Mismo problema desde una versión anterior y también funciona 1.5. → 1.6.1         

Hola logistics.gna tengo este problema pero no se bien donde debo tocar para hacer el cambio este que plantean, Estoy con la version 1.6.0.6. Me puedes decir como lo has hecho? Gracias

[vrobador]

 

Ok I have discovered that problem is in getSpecificPrice function in classes/specificprice.php 

 

You have to replace SQL select. So:

 

Not working select:

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price`

WHERE

                `id_shop` '.self::formatIntInQuery(0, $id_shop).' AND

                `id_currency` '.self::formatIntInQuery(0, $id_currency).' AND

                `id_country` '.self::formatIntInQuery(0, $id_country).' AND

                `id_group` '.self::formatIntInQuery(0, $id_group).' '.$query_extra.'

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

working select:

           $now = date('Y-m-d H:i:00');

            $query = '

SELECT *, '.SpecificPrice::_getScoreQuery($id_product, $id_shop, $id_currency, $id_country, $id_group, $id_customer).'

FROM `'._DB_PREFIX_.'specific_price` USE INDEX (id_product_2)

WHERE `id_product` IN (0, '.(int)$id_product.')

AND `id_product_attribute` IN (0, '.(int)$id_product_attribute.')

AND `id_shop` IN (0, '.(int)$id_shop.')

AND `id_currency` IN (0, '.(int)$id_currency.')

AND `id_country` IN (0, '.(int)$id_country.')

AND `id_group` IN (0, '.(int)$id_group.')

AND `id_customer` IN (0, '.(int)$id_customer.')

AND

(

(`from` = \'0000-00-00 00:00:00\' OR \''.$now.'\' >= `from`)

AND

(`to` = \'0000-00-00 00:00:00\' OR \''.$now.'\' <= `to`)

)

AND id_cart IN (0, '.(int)$id_cart.')

AND IF(`from_quantity` > 1, `from_quantity`, 0) <= ';

 

 

This solution is perfect for PS 1.6.1.2

[saxcbr]

Unfortunately this (working) solution it's only a workaround of the real problem. 

 

The real problem is in function filterOutField at line 212 in classes/specificprice.php 

    protected static function filterOutField($field_name, $field_value, $threshold = 1000)
    {
        $name = Db::getInstance()->escape($field_name, false, true);
        $query_extra = 'AND `'.$name.'` = 0 ';
        if ($field_value == 0 || array_key_exists($field_name, self::$_no_specific_values)) {
            return $query_extra;
        }
...
...
...

That function is called to add the extra_query value and there's a limit imposed by default value $threshold = 1000, so if you have more that 1000 products with offer these are not showed. 

 

You need to change $threshold default limit to a nr > of the nr of specials to solve the problem without changing more code!

 

This problem affect also PS 1.7.0.6 !!!

Edited March 17, 2017 by saxcbr (see edit history)

[sannet]

After updating to Presta 1.6.1.19 from 1.6.1.5, i couldn't add any specific prices.

I solved this problem by inserting a new field in ps_specific_price table after reduction_type:

Name                   Type                    Null    Default
reduction_tax    tinyint(1)            No       1