salix/db/routines/vn/functions/ticket_getFreightCost.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`ticket_getFreightCost`(vTicketFk INT)
	RETURNS decimal(10,2)
	DETERMINISTIC
BEGIN
/**
 * Devuelve el coste de transportar un ticket.
 *
 * @vTicketFk Id del ticket a calcular
 */
	DECLARE deliveryPrice DECIMAL (10,2);
    
	IF (SELECT isVolumetric FROM ticket t JOIN zone z ON t.zoneFk = z.id 
			WHERE t.id = vTicketFk) THEN
		SELECT SUM(sv.freight) INTO deliveryPrice
			FROM vn.saleVolume sv
			WHERE sv.ticketFk = vTicketFk;
	ELSE
		SELECT SUM((t.zonePrice - t.zoneBonus) * ebv.ratio) INTO deliveryPrice
			FROM vn.ticket t
				LEFT JOIN expedition e ON e.ticketFk = t.id
				JOIN expeditionBoxVol ebv ON ebv.boxFk = e.freightItemFk
			WHERE t.id = vTicketFk;
		
	END IF;
	RETURN deliveryPrice;
END$$
DELIMITER ;