DELIMITER $$
USE `transfashions`$$
DROP TRIGGER IF EXISTS `calc_user_profit`$$
CREATE
TRIGGER `calc_user_profit` BEFORE UPDATE ON `stock`
FOR EACH ROW BEGIN
UPDATE `result` SET `user_profit` = (SELECT `user_profit` FROM `userprofit` WHERE `range_id`=
(SELECT `id` FROM `range` WHERE (SELECT `price` FROM `stock` WHERE `art` = OLD.`art` LIMIT 1) BETWEEN `price_low` AND `price_high` LIMIT 1)
) WHERE `art` = OLD.`art`;
END;
$$
DELIMITER ;
DELIMITER $$
USE `transfashions`$$
DROP TRIGGER IF EXISTS `calc_total`$$
CREATE
TRIGGER `calc_total` AFTER UPDATE ON `stock`
FOR EACH ROW BEGIN
SET @roznica = 1;
SET @margin_add = 0;
SET @total = 0;
SET @transaction_fees = 0;
SET @btw = 0;
SET @profit = 0;
SET @margins = 0;
SET @price = 0;
SET @transfees_proc = 0;
SET @transfees_plus = 0;
SELECT `price` INTO @cost FROM `stock` WHERE `art` = OLD.`art`;
SELECT `user_profit` INTO @user_profit FROM `result` WHERE `art` = OLD.`art`;
SELECT `packing` INTO @packing FROM `company` WHERE `id` = 1;
SELECT `transfees_proc` INTO @transfees_proc FROM `company` WHERE `id` = 1;
SELECT `transfees_plus` INTO @transfees_plus FROM `company` WHERE `id` = 1;
SET @i=0;
WHILE @roznica <> 0 DO
SET @margin_add = @margin_add - @roznica;
SET @total = @cost + @margin_add + @packing;
SET @transaction_fees = (@total * @transfees_proc / 100) + @transfees_plus;
SET @btw = @total * (17.4 / 100);
SET @profit = @total - (@transaction_fees + @btw + @cost + @packing);
SET @margins = @profit / @cost * 100;
SET @roznica = ROUND(@total - (@cost + @packing + @transaction_fees + @btw + @user_profit), 3);
SET @i=@i+1; /* just iterations counter */
END WHILE;
UPDATE `result` SET `total` = @total WHERE `art` = OLD.`art`;
END;
$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `get_user_profit_by_price`$$
CREATE
PROCEDURE `transfashions`.`get_user_profit_by_price`(IN compid INT, IN priceval DECIMAL(12,2))/*, OUT uprofit DECIMAL(12,2)*/
BEGIN
/* INTO uprofit*/
SELECT `user_profit` FROM `userprofit` WHERE `company_id` = compid AND `range_id`=
(SELECT `id` FROM `range` WHERE priceval BETWEEN `price_low` AND `price_high` LIMIT 1) LIMIT 1;
END$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `get_total`$$
CREATE
PROCEDURE `transfashions`.`get_total`(IN price DECIMAL(12,2), IN packing DECIMAL(12,2), IN user_profit DECIMAL(12,2), IN transfees_proc DECIMAL(12,2), IN transfees_plus DECIMAL(12,2))
BEGIN
SET @roznica = 0;
SET @margin_add = 0;
SET @total = 0;
SET @transaction_fees = 0;
SET @btw = 0;
SET @profit = 0;
SET @margins = 0;
REPEAT
SET @margin_add = @margin_add - @roznica;
SET @total = price + @margin_add + packing;
SET @transaction_fees = (@total * transfees_proc / 100) + transfees_plus;
SET @btw = @total * (17.4 / 100);
SET @profit = @total - (@transaction_fees + @btw + price + packing);
SET @margins = @profit / price * 100;
SET @roznica = ROUND(@total - (price + packing + @transaction_fees + @btw + user_profit), 3);
UNTIL @roznica = 0
END REPEAT;
SELECT ROUND(@total,2) AS `total`;
END$$
DELIMITER ;
Ответить
Хотите присоединиться к обсуждению?Не стесняйтесь вносить свой вклад!