SELECT COUNT(DISTINCT acp.`id_product`) as total
      FROM `ps_pm_advancedsearch_cache_product_1` acp JOIN `ps_product_shop` ps ON (ps.id_shop IN (1) AND ps.`id_product` = acp.`id_product`) LEFT JOIN `ps_product` p ON ( p.`id_product` = acp.`id_product`) LEFT JOIN `ps_tax_rule` tr ON (ps.`id_tax_rules_group` = tr.`id_tax_rules_group`
        AND tr.`id_country` = 8
        AND tr.`id_state` = 0)  LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)  LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 5) JOIN `ps_pm_advancedsearch_product_price_1` app ON ( acp.`id_cache_product` = app.`id_cache_product` AND ((app.`valid_id_specific_price`=1 AND app.`is_specific`=1) OR app.`is_specific`=0)  AND app.`id_shop` IN (0, 1) ) LEFT JOIN `ps_stock_available` sa1 ON ( sa1.`id_product` = acp.`id_product` AND sa1.id_shop IN (1)) JOIN (SELECT app.id_cache_product, MAX((app.is_specific +  IF ('2013-04-23 16:06:07' >= app.`from` AND '2013-04-23 16:06:07' <= app.`to`, 1, 0) +  IF (app.`id_group` = 0, 2, 0) +  IF (app.`id_country` = 8, 4, 0) +  IF (app.`id_currency` = 1, 8, 0) +  IF (app.`id_shop` = 0, 16, 0))) AS `score_max`FROM `ps_pm_advancedsearch_cache_product_1` acp  JOIN `ps_product_shop` ps ON (ps.id_shop IN (1) AND ps.`id_product` = acp.`id_product`) LEFT JOIN `ps_product` p ON ( p.`id_product` = acp.`id_product`) LEFT JOIN `ps_tax_rule` tr ON (ps.`id_tax_rules_group` = tr.`id_tax_rules_group`
        AND tr.`id_country` = 8
        AND tr.`id_state` = 0)  LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)  LEFT JOIN `ps_tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = 5) JOIN `ps_pm_advancedsearch_product_price_1` app ON ( acp.`id_cache_product` = app.`id_cache_product` AND ((app.`valid_id_specific_price`=1 AND app.`is_specific`=1) OR app.`is_specific`=0)  AND app.`id_shop` IN (0, 1) ) WHERE (  (
                app.`id_currency` IN (0, 1)
                AND app.`id_country` IN (0, 8) AND
                app.`id_group` IN (0, 0) AND
                ((app.`from` = '0000-00-00 00:00:00' OR '2013-04-23 16:06:07' >= app.`from`) AND (app.`to` = '0000-00-00 00:00:00' OR '2013-04-23 16:06:07' <= app.`to`))  AND app.`id_shop` IN (0, 1) )  ) AND ps.`active` = 1 AND (IF (sa1.`quantity` > 0, 1, IF (sa1.`out_of_stock` = 2, 0 = 1, sa1.`out_of_stock` = 1))) GROUP BY app.id_cache_product) AS specific_max_score  ON (specific_max_score.id_cache_product = app.id_cache_product AND specific_max_score.score_max = (app.is_specific +  IF ('2013-04-23 16:06:07' >= app.`from` AND '2013-04-23 16:06:07' <= app.`to`, 1, 0) +  IF (app.`id_group` = 0, 2, 0) +  IF (app.`id_country` = 8, 4, 0) +  IF (app.`id_currency` = 1, 8, 0) +  IF (app.`id_shop` = 0, 16, 0))) WHERE (  (
                /*AS4-PR-Start*/
                IF(app.`is_specific` = 1 AND app.`id_currency` IN (0, 1),
                  app.`price_wt`-(app.`reduction_amount`) >= FLOOR((0) / IF(t.`rate`,((t.`rate`/100)+1),1))
                  ,
                  app.`price_wt`-(app.`reduction_amount`) >= FLOOR((0) / IF(t.`rate`,((t.`rate`/100)+1),1))
                )

                AND
                IF(app.`is_specific` = 1 AND app.`id_currency` IN (0, 1),
                  app.`price_wt`-(app.`reduction_amount`) <= CEIL((50) / IF(t.`rate`,((t.`rate`/100)+1),1))
                  ,
                  app.`price_wt`-(app.`reduction_amount`) <= CEIL((50) / IF(t.`rate`,((t.`rate`/100)+1),1))
                )

                /*AS4-PR-End*/
                AND app.`id_country` IN (0, 8) AND
                app.`id_group` IN (0, 0) AND
                ((app.`from` = '0000-00-00 00:00:00' OR '2013-04-23 16:06:07' >= app.`from`) AND (app.`to` = '0000-00-00 00:00:00' OR '2013-04-23 16:06:07' <= app.`to`))  AND app.`id_shop` IN (0, 1) )  ) AND ps.`active` = 1 AND (IF (sa1.`quantity` > 0, 1, IF (sa1.`out_of_stock` = 2, 0 = 1, sa1.`out_of_stock` = 1))) LIMIT 1