megafilter.sql

Opencart

SELECT 
   MIN(`price_tmp`) AS `p_min`, 
   MAX(`price_tmp`) AS `p_max`
FROM 
  (
    SELECT 
         CASE WHEN cena1 IS NOT NULL THEN cena1 WHEN opt IS NOT NULL THEN opt ELSE price_tmp END AS price_tmp 
    FROM 
      (
        SELECT 
        
          IFNULL(
            (
              SELECT 
                CASE WHEN sk.cena1 IS NOT NULL THEN sk.cena1 WHEN p.opt IS NOT NULL THEN p.opt ELSE price END AS price 
              FROM 
                `oc_product_special` AS `ps` 
                INNER JOIN oc_skidki sk ON ps.product_id = sk.product_id 
                AND sk.customer1c_id = 68823 
              WHERE 
                `ps`.`product_id` = `p`.`product_id` 
                AND `ps`.`customer_group_id` = '1' 
                AND(
                  (
                    `ps`.`date_start` = '0000-00-00' 
                    OR `ps`.`date_start` < NOW()
                  ) 
                  AND(
                    `ps`.`date_end` = '0000-00-00' 
                    OR `ps`.`date_end` > NOW()
                  )
                ) 
              ORDER BY 
                `ps`.`priority` ASC, 
                `ps`.`price` ASC 
              LIMIT 
                1
            ), IFNULL(
              (
                SELECT 
                  CASE WHEN sk.cena1 IS NOT NULL THEN sk.cena1 WHEN p.opt IS NOT NULL THEN p.opt ELSE p.price END AS price 
                FROM 
                  `oc_product_discount` AS `pd2` 
                  INNER JOIN oc_skidki sk ON pd2.product_id = sk.product_id 
                  AND sk.customer1c_id = 68823 
                WHERE 
                  `pd2`.`product_id` = `p`.`product_id` 
                  AND `pd2`.`customer_group_id` = '1' 
                  AND `pd2`.`quantity` >= '1' 
                  AND(
                    (
                      `pd2`.`date_start` = '0000-00-00' 
                      OR `pd2`.`date_start` < NOW()
                    ) 
                    AND(
                      `pd2`.`date_end` = '0000-00-00' 
                      OR `pd2`.`date_end` > NOW()
                    )
                  ) 
                ORDER BY 
                  `pd2`.`priority` ASC, 
                  `pd2`.`price` ASC 
                LIMIT 
                  1
              ), `p`.`price`
            )
          ) AS `price_tmp`,sk.cena1,p.opt
          
            
        FROM 
          `oc_product` AS `p` 
          INNER JOIN `oc_product_description` AS `pd` ON `pd`.`product_id` = `p`.`product_id` 
          AND `pd`.`language_id` = 3 
          INNER JOIN `oc_product_to_store` AS `p2s` ON `p2s`.`product_id` = `p`.`product_id` 
          AND `p2s`.`store_id` = 0 
          INNER JOIN `oc_product_to_category` AS `p2c` ON `p2c`.`product_id` = `p`.`product_id` 
          INNER JOIN `oc_skidki` sk ON `p`.`product_id` = sk.product_id 
          AND sk.customer1c_id = 68823 
        WHERE 
          `p`.`status` = '1' 
          AND `p`.`date_available` <= NOW() 
          AND `p2c`.`category_id` IN(1)
      ) AS `tmp`
  ) AS `tmp`;