Jump to content

SOLVED: Specific price error after update from 1.6.0.9 to 1.6.1.1


Recommended Posts

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? 

post-282359-0-16582400-1441635461_thumb.jpg

post-282359-0-97592500-1441635472_thumb.jpg

post-282359-0-18135400-1441635482_thumb.png

Edited by cerkoxxl (see edit history)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  

post-282359-0-70986100-1441743742_thumb.jpg

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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 by seba4leon (see edit history)
Link to comment
Share on other sites

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

 

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? 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Antakarana (see edit history)
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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 by satrick (see edit history)
  • Like 5
Link to comment
Share on other sites

 

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! :)

Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

 

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    :rolleyes:   :lol:   :D

Edited by logistics.gna (see edit history)
Link to comment
Share on other sites

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 by karthikcs (see edit history)
Link to comment
Share on other sites

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 ;)

Link to comment
Share on other sites

  • 1 month later...

 

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!!!

Link to comment
Share on other sites

  • 4 months later...

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

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

Link to comment
Share on other sites

  • 8 months later...

 

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

Link to comment
Share on other sites

  • 1 month later...

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 by saxcbr (see edit history)
  • Like 1
Link to comment
Share on other sites

  • 1 year later...

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

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...