SELECT SQL_CALC_FOUND_ROWS products.product_id, descr1.product as product, companies.company as company_name, MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price, products.product_type, products.parent_product_id, CASE WHEN csc_product_currency_items.currency IS NULL THEN "D" ELSE csc_product_currency_items.currency END as currency, MIN(IF (prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount) / 100)) * _c.coefficient as currency_price FROM ?:products as products LEFT JOIN ?:product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'en' LEFT JOIN ?:product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN ?:companies AS companies ON companies.company_id = products.company_id INNER JOIN ?:products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN ?:categories ON ?:categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') AND cscart_categories.storefront_id IN (0, 2) LEFT JOIN ?:warehouses_sum_products_amount as war_sum_amount ON war_sum_amount.product_id = products.product_id LEFT JOIN ?:warehouses_destination_products_amount AS warehouses_destination_products_amount ON warehouses_destination_products_amount.product_id = products.product_id AND warehouses_destination_products_amount.destination_id = 9 AND warehouses_destination_products_amount.storefront_id = 0 LEFT JOIN ?:delivery_area_product_city as dapc ON products.product_id = dapc.product_id LEFT JOIN ?:delivery_area_cities as dac ON dapc.city_id = dac.city_id LEFT JOIN ?:csc_product_currency_items as csc_product_currency_items ON csc_product_currency_items.product_id=products.product_id AND csc_product_currency_items.company_id=0 LEFT JOIN ?:currencies as _c ON _c.currency_code = csc_product_currency_items.currency OR (_c.is_primary = "Y" AND (csc_product_currency_items.currency="D" OR csc_product_currency_items.currency IS NULL)) WHERE 1 AND ?:categories.category_id IN (1277, 1278, 1279, 1280, 1359, 1663, 1671, 1676, 1686, 1692, 1695, 1703, 1709, 1717, 1727, 1730, 1735, 1739, 1745, 1750, 1760, 1765, 1776, 1785, 1789, 1794, 1798, 1801, 1808, 1810, 1817, 1821, 1826, 1833, 1836, 1844, 1852, 1853, 1862, 1867, 1868, 1870, 1875, 1879, 1882, 1883, 1968, 4600, 5682, 1358, 1447, 1664, 1672, 1677, 1687, 1693, 1696, 1704, 1710, 1718, 1728, 1731, 1736, 1746, 1751, 1761, 1766, 1777, 1786, 1790, 1795, 1799, 1802, 1811, 1816, 1818, 1822, 1827, 1834, 1835, 1837, 1845, 1854, 1863, 1869, 1871, 1876, 1880, 1884, 1969, 4601, 5683, 6845, 1502, 1662, 1665, 1673, 1678, 1688, 1697, 1705, 1711, 1732, 1737, 1747, 1752, 1762, 1767, 1778, 1787, 1791, 1796, 1803, 1812, 1819, 1820, 1823, 1828, 1838, 1843, 1846, 1855, 1864, 1872, 1877, 1896, 4602, 6846, 1666, 1670, 1679, 1689, 1698, 1706, 1712, 1733, 1738, 1748, 1753, 1763, 1768, 1779, 1792, 1804, 1813, 1824, 1825, 1829, 1839, 1847, 1856, 1861, 1865, 1873, 1964, 4603, 4605, 6847, 1667, 1675, 1680, 1690, 1699, 1707, 1740, 1749, 1754, 1764, 1769, 1780, 1805, 1814, 1830, 1840, 1848, 1857, 1866, 1874, 1881, 1967, 4604, 4606, 5684, 6753, 1668, 1681, 1685, 1700, 1741, 1755, 1770, 1781, 1797, 1806, 1815, 1841, 1849, 1878, 1970, 4607, 5457, 5686, 6848, 1669, 1682, 1691, 1701, 1742, 1756, 1771, 1782, 1800, 1842, 1850, 4608, 5685, 1674, 1683, 1694, 1743, 1757, 1772, 1783, 1793, 4609, 1684, 1702, 1726, 1744, 1758, 1773, 4610, 1708, 1729, 1759, 1774, 1784, 4611, 1716, 1734, 1775, 1788, 4612, 1807, 1971, 4613, 5455, 5681, 4614, 1851, 4615, 3978, 4616, 3979, 4617, 3980, 4618, 4619, 4620, 4621, 4622, 4623, 4624, 4625, 4626, 4627, 4628, 4629, 4630, 4631, 4632, 4633, 4634, 4635, 4636, 4637, 4638, 4639, 4640, 4641, 4642, 4643, 4644, 4645, 4646, 4647, 4648, 4649, 4650, 4651, 4652) AND companies.status IN ('A') AND products.company_id IN (3, 4, 5, 6, 7, 8, 9, 10, 12, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 28, 29, 31, 32, 34, 0) AND ((CASE products.is_stock_split_by_warehouses WHEN 'Y' THEN warehouses_destination_products_amount.amount ELSE products.amount END) > 0 OR products.tracking = 'D') AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) AND products.parent_product_id = 0 AND products.product_type != 'D' GROUP BY products.product_id ORDER BY currency_price asc, products.product_id ASC LIMIT 150, 50
