^ """
\n
        SELECT acp.id_product, (IF(app.`valid_id_specific_price`=1 AND app.`is_specific`=1, 1, 0) + app.`has_no_specific` +  IF ((app.`from` = '0000-00-00 00:00:00' OR '2024-07-22 15:30:42' >= app.`from`) AND (app.`to` = '0000-00-00 00:00:00' OR '2024-07-22 15:30:42' <= app.`to`), 1, 0) +  IF (app.`id_shop` IN (0, 1), 2, 0) +  IF (app.`id_country` IN (0, 14), 4, 0) +  IF (app.`id_currency` IN (0, 1), 8, 0)) AS `score`\n
        FROM `ps_pm_advancedsearch_product_price_11` app\n
        LEFT JOIN `ps_pm_advancedsearch_cache_product_11` acp ON (app.`id_cache_product` = acp.`id_cache_product`)\n
        JOIN `ps_product_shop` ps ON (\n
            \n
            ps.id_shop IN (1)\n
            AND ps.`id_product` = acp.`id_product`\n
        )\n
JOIN `ps_pm_advancedsearch_cache_product_criterion_11` acpc3 ON ( acp.`id_cache_product` = acpc3.`id_cache_product` AND acpc3.`id_criterion` IN (517))\n
LEFT JOIN `ps_tax_rule` tr ON (ps.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = 14 AND tr.`id_state` = 0)\n
LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`)\n
LEFT JOIN `ps_group_reduction` grc ON (grc.`id_group`=1 AND ps.`id_category_default` = grc.`id_category`) WHERE ((app.`from` = '0000-00-00 00:00:00' OR '2024-07-22 15:30:42' >= app.`from`) AND (app.`to` = '0000-00-00 00:00:00' OR '2024-07-22 15:30:42' <= app.`to`)) AND  ((app.`valid_id_specific_price`=1 AND app.`is_specific`=1 AND app.`id_currency` IN (0, 1)) OR app.`has_no_specific`=1) AND ps.`active` = 1\n
 AND ps.`visibility` IN ("both", "catalog")\n
 AND EXISTS(\n
                    SELECT 1 FROM `ps_category_product` cp\n
                    JOIN `ps_category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` = 1)\n
                    WHERE cp.`id_product` = ps.`id_product`\n
                ) AND app.`id_country` IN (0, 14)  AND app.`id_group` IN (0, 1)  AND app.`id_shop` IN (0, 1)  ORDER BY score DESC, \n
            (\n
                app.`price_wt` -\n
                IF(\n
                    app.`reduction_type`='amount',\n
                    app.`reduction_amount` * IF(app.`reduction_tax`=1, 1, IF(t.`rate` > 0, ((t.`rate`/100) + 1), 1))  / IF(t.`rate` > 0, ((t.`rate`/100) + 1), 1),\n
                    app.`reduction_amount`\n
                )\n
            )\n
         * IF(grc.`reduction` > 0, 1 - grc.`reduction`, 1) ASC
"""