select `zap`.*, `zap_history`.`base_id` as `hBase`,
ROUND(
CASE
-- Нет скидки для запчастей, добавленных в течение 14 дней
WHEN DATEDIFF(CURRENT_DATE, zap.date_add) <= 14 THEN zap.price
-- Специальные случаи
WHEN zt.id_zap_type = -1 THEN zap.price
-- Если скидка отключена для запчасти
WHEN zap.disable_skidka THEN zap.price
-- Если задана скидка на запчасть
WHEN zap.skidka THEN (zap.price - zap.price / 100 * zap.skidka)
-- Стандартные скидки для грузовых
-- Если скидка на тип запчасти > 0, используем ее, но не более 300
WHEN model.is_truck = 1 AND zt.skidka_truck > 0 THEN
IF(
zap.price / 100 * zt.skidka_truck < 300,
(zap.price - zap.price / 100 * zt.skidka_truck),
(zap.price - 300)
)
-- Если скидка на категорию запчасти > 0, используем ее, но не более 300
WHEN model.is_truck = 1 AND ztc.skidka_truck > 0 THEN
IF(
zap.price / 100 * ztc.skidka_truck < 300,
(zap.price - zap.price / 100 * ztc.skidka_truck),
(zap.price - 300)
)
-- Стандартные скидки для легковых
-- Если скидка на тип запчасти > 0, используем ее, но не более 300
WHEN (model.is_truck = 0 OR model.is_truck IS NULL) AND zt.skidka > 0 THEN
IF(
zap.price / 100 * zt.skidka < 300,
(zap.price - zap.price / 100 * zt.skidka),
(zap.price - 300)
)
-- Если скидка на категорию запчасти > 0, используем ее, но не более 300
WHEN (model.is_truck = 0 OR model.is_truck IS NULL) AND ztc.skidka > 0 THEN
IF(
zap.price / 100 * ztc.skidka < 300,
(zap.price - zap.price / 100 * ztc.skidka),
(zap.price - 300)
)
-- Акционные условия для легковых запчастей (без ДВС и КПП)
WHEN (model.is_truck = 0 OR model.is_truck IS NULL)
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 15 AND 30 THEN
IF(
zap.price / 100 * 5 < 300,
(zap.price - zap.price / 100 * 5),
(zap.price - 300)
)
WHEN (model.is_truck = 0 OR model.is_truck IS NULL)
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 31 AND 180 THEN
IF(
zap.price / 100 * 10 < 300,
(zap.price - zap.price / 100 * 10),
(zap.price - 300)
)
WHEN (model.is_truck = 0 OR model.is_truck IS NULL)
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 181 AND 360 THEN
IF(
zap.price / 100 * 15 < 300,
(zap.price - zap.price / 100 * 15),
(zap.price - 300)
)
WHEN (model.is_truck = 0 OR model.is_truck IS NULL)
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 361 AND 1080 THEN
IF(
zap.price / 100 * 20 < 300,
(zap.price - zap.price / 100 * 20),
(zap.price - 300)
)
WHEN (model.is_truck = 0 OR model.is_truck IS NULL)
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) >= 1081 THEN
IF(
zap.price / 100 * 23 < 300,
(zap.price - zap.price / 100 * 23),
(zap.price - 300)
)
-- Акционные условия для легковых ДВС и КПП
WHEN (model.is_truck = 0 OR model.is_truck IS NULL)
AND zap.id_zap_type IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 31 AND 90 THEN
IF(
zap.price / 100 * 5 < 300,
(zap.price - zap.price / 100 * 5),
(zap.price - 300)
)
WHEN (model.is_truck = 0 OR model.is_truck IS NULL)
AND zap.id_zap_type IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 91 AND 540 THEN
IF(
zap.price / 100 * 10 < 300,
(zap.price - zap.price / 100 * 10),
(zap.price - 300)
)
WHEN (model.is_truck = 0 OR model.is_truck IS NULL)
AND zap.id_zap_type IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) >= 541 THEN
IF(
zap.price / 100 * 15 < 300,
(zap.price - zap.price / 100 * 15),
(zap.price - 300)
)
-- Акционные условия для грузовых запчастей (без ДВС и КПП)
WHEN model.is_truck = 1
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 15 AND 30 THEN
IF(
zap.price / 100 * 5 < 300,
(zap.price - zap.price / 100 * 5),
(zap.price - 300)
)
WHEN model.is_truck = 1
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 31 AND 180 THEN
IF(
zap.price / 100 * 10 < 300,
(zap.price - zap.price / 100 * 10),
(zap.price - 300)
)
WHEN model.is_truck = 1
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 181 AND 360 THEN
IF(
zap.price / 100 * 15 < 300,
(zap.price - zap.price / 100 * 15),
(zap.price - 300)
)
WHEN model.is_truck = 1
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 361 AND 720 THEN
IF(
zap.price / 100 * 20 < 300,
(zap.price - zap.price / 100 * 20),
(zap.price - 300)
)
WHEN model.is_truck = 1
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) BETWEEN 721 AND 1440 THEN
IF(
zap.price / 100 * 23 < 300,
(zap.price - zap.price / 100 * 23),
(zap.price - 300)
)
WHEN model.is_truck = 1
AND zap.id_zap_type NOT IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) >= 1441 THEN
IF(
zap.price / 100 * 25 < 300,
(zap.price - zap.price / 100 * 25),
(zap.price - 300)
)
-- Акционные условия для грузовых ДВС и КПП
WHEN model.is_truck = 1
AND zap.id_zap_type IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) <= 180 THEN
zap.price
WHEN model.is_truck = 1
AND zap.id_zap_type IN (338,605,606,607,608,609,610,1828,1829,1830)
AND DATEDIFF(CURRENT_DATE, zap.date_add) >= 181 THEN
IF(
zap.price / 100 * 10 < 300,
(zap.price - zap.price / 100 * 10),
(zap.price - 300)
)
-- Во всех остальных случаях — исходная цена
ELSE zap.price
END,
2
) as price_calculated
from `zap` inner join `zap_history` on `zap_history`.`id` = `zap`.`last_history_id` left join `model` on `zap`.`id_model` = `model`.`id_model` inner join `zap_type` as `zt` on `zap`.`id_zap_type` = `zt`.`id_zap_type` left join `zap_type_categorys` as `ztc` on `zt`.`id_zap_type_category` = `ztc`.`id_zap_type_category` left join `requests_zaps` as `rq` on `rq`.`id_zap` = `zap`.`id_zap` and `rq`.`status` <> 2 and `rq`.`status` <> 4 where `zap_history`.`action` in (?, ?, ?, ?) and `ztc`.`id_zap_type_category` = ? and (`rq`.`status` not in (?, ?) or `rq`.`status` is null) and `zap_history`.`base_id` in (select `id_base` from `base` where `to_export` = ?) and `zap`.`id_zap_type` <> ? order by CASE
WHEN zap.price IS NULL OR zap.price = '' THEN 1
WHEN CAST(REPLACE(zap.price, ',', '.') AS DECIMAL(10,2)) = 0 THEN 2
ELSE 0
END, `zap`.`id_zap` desc
Cannot modify header information - headers already sent by (output started at /home/aimby/aimSite/app/Http/Controllers/CatalogController.php:513)