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 ;

0 ответы

Ответить

Хотите присоединиться к обсуждению?
Не стесняйтесь вносить свой вклад!

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *