DELIMITER $$
CREATE FUNCTION calcula_desconto_cliente(p_cliente_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE v_dias INT;
DECLARE v_valor DECIMAL(10,2);
DECLARE v_desconto DECIMAL(5,2);
DECLARE v_total_com_desconto DECIMAL(10,2);
-- Busca o total de dias e o valor total do aluguel do cliente
SELECT
DATEDIFF(a.data_fim, a.data_inicio),
a.preco_total
INTO
v_dias, v_valor
FROM alugueis a
WHERE a.cliente_id = p_cliente_id
ORDER BY a.data_fim DESC
LIMIT 1;
-- Define o percentual de desconto com base nos dias de hospedagem
SET v_desconto = CASE
WHEN v_dias BETWEEN 4 AND 6 THEN 0.05
WHEN v_dias BETWEEN 7 AND 9 THEN 0.10
WHEN v_dias >= 10 THEN 0.15
ELSE 0
END;
-- Calcula o valor com desconto
SET v_total_com_desconto = ROUND(v_valor * (1 - v_desconto), 2);
RETURN v_total_com_desconto;
END$$
DELIMITER ;
-- como usar aplicando de cliente por ID
SELECT
c.nome AS NomeCliente,
a.preco_total AS ValorOriginal,
CONCAT(
CASE
WHEN DATEDIFF(a.data_fim, a.data_inicio) BETWEEN 4 AND 6 THEN 5
WHEN DATEDIFF(a.data_fim, a.data_inicio) BETWEEN 7 AND 9 THEN 10
WHEN DATEDIFF(a.data_fim, a.data_inicio) >= 10 THEN 15
ELSE 0
END, '%'
) AS PercentualDesconto,
calcula_desconto_cliente(a.cliente_id) AS ValorComDesconto
FROM alugueis a
JOIN clientes c ON a.cliente_id = c.cliente_id
WHERE a.cliente_id = 1;
-- como usar aplicando em lista de todos os clientes
SELECT
a.cliente_id,
c.nome AS NomeCliente,
a.preco_total AS ValorOriginal,
CONCAT(
ROUND(
CASE
WHEN DATEDIFF(a.data_fim, a.data_inicio) BETWEEN 4 AND 6 THEN 5
WHEN DATEDIFF(a.data_fim, a.data_inicio) BETWEEN 7 AND 9 THEN 10
WHEN DATEDIFF(a.data_fim, a.data_inicio) >= 10 THEN 15
ELSE 0
END, 0
), '%'
) AS PercentualDesconto,
calcula_desconto_cliente(a.cliente_id) AS ValorComDesconto
FROM alugueis a
JOIN clientes c ON a.cliente_id = c.cliente_id
ORDER BY a.cliente_id;
-- Usando o VIEW tornado a consulta mais rapida e dimanica e mais simples de entendimento
CREATE OR REPLACE VIEW vw_desconto_clientes AS
SELECT
a.cliente_id,
c.nome AS NomeCliente,
a.data_inicio,
a.data_fim,
DATEDIFF(a.data_fim, a.data_inicio) AS TotalDias,
CASE
WHEN DATEDIFF(a.data_fim, a.data_inicio) BETWEEN 4 AND 6 THEN '5%'
WHEN DATEDIFF(a.data_fim, a.data_inicio) BETWEEN 7 AND 9 THEN '10%'
WHEN DATEDIFF(a.data_fim, a.data_inicio) >= 10 THEN '15%'
ELSE '0%'
END AS PercentualDesconto,
a.preco_total AS ValorOriginal,
ROUND(a.preco_total *
(1 - CASE
WHEN DATEDIFF(a.data_fim, a.data_inicio) BETWEEN 4 AND 6 THEN 0.05
WHEN DATEDIFF(a.data_fim, a.data_inicio) BETWEEN 7 AND 9 THEN 0.10
WHEN DATEDIFF(a.data_fim, a.data_inicio) >= 10 THEN 0.15
ELSE 0
END), 2
) AS ValorComDesconto
FROM alugueis a
JOIN clientes c ON a.cliente_id = c.cliente_id;
-- Lista de todos os Todos os Clientes
SELECT * FROM vw_desconto_clientes;
-- Cliente especifico
SELECT * FROM vw_desconto_clientes WHERE cliente_id = 1000;