CREATE PROCEDURE ssp_Transacoes_InsereTransacao_ClienteNovo
(
	@ClienteID NUMERIC,
	@EmailPrimario VARCHAR(400)
)
AS
	SET NOCOUNT OFF;


-- START THE TRANSACTION
BEGIN TRANSACTION


DECLARE
	@EmailID NUMERIC,
	@TaxaTransacao money


--   SELECT THE EMAILID OF THE CUSTOMER
SELECT @EmailID = EmailID FROM tbEmails WHERE ClienteID = @ClienteID


-- INSERT THE NEW TRANSACTION TO CUSTOMER
INSERT INTO tbTransacoes(
		TransacaoID,
		ClienteID,
		ClienteTransacao,
		EmailID,
		DebitoCredito,
		TipoTransacao,
		StatusTransacao,
		TipoPagamento,
		ValorTransacaoBruto,
		TaxaTransacao,
		ValorTransacaoLiquido,
		Descricao,
		IP
	)  (
		SELECT
			TransacaoID,
			@ClienteID AS ClienteTransacao,
			T.ClienteID,
			(SELECT EmailID FROM tbEmails E WHERE (E.ClienteID =  T.ClienteID) AND (Habilitado = 1) AND (Ativo = 1) AND (Principal = 1) ) AS EmailID,
			'C' AS DebitoCredito,
			TipoTransacao,
			1 AS StatusTransacao,
			TipoPagamento,
			T.ValorTransacaoBruto * (-1),
			TaxaTransacao, -- HERE I WANT USE MY STORED PROCEDURE
			ValorTransacaoLiquido * (-1), -- HERE I WILL USE MY STORED PROCEDURE AGAIN ((T.ValorTransacaoBruto * (-1) - TaxaTransacao)
			Descricao,
			IP
		FROM tbTransacoes T
		WHERE EmailInexistente = @EmailPrimario
	)

-- NOW, UPDATE THE TRANSACTION TO CUSTOMER EMAIL
UPDATE tbTransacoes SET
		EmailInexistente = NULL,
		ClienteTransacao = @ClienteID,
		EmailID = @EmailID
	WHERE
		(EmailInexistente = @EmailPrimario)

-- UPDATE NOW THE FEE OF THE RECEIVED TRANSACTION
UPDATE tbTransacoes SET
		EmailInexistente = NULL,
		ClienteTransacao = @ClienteID,
		EmailID = @EmailID
	WHERE
		(EmailInexistente = @EmailPrimario)






IF @@ERROR = 0
	BEGIN
		COMMIT TRANSACTION
	END
ELSE
	BEGIN
		ROLLBACK TRANSACTION
		RETURN
	END
GO

CREATE PROCEDURE ssp_Transacoes_CalculaTaxa
(
	@ClienteID numeric,
	@ValorTransacaoBruto money,
	@Taxa money OUTPUT
)
AS
	SET NOCOUNT OFF;


-- START THE TRANSACTION
BEGIN TRANSACTION


DECLARE
	@BaseCalculo money, -- PERCENT TO FEE
	@TaxaFixa money -- FIX FEE BY TRANSACTION


-- SELECT THE FEE
SELECT @BaseCalculo = PorcentagemTaxa
FROM tbExtratoMensal
WHERE ClienteID = @ClienteID
IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRANSACTION
		RETURN
	END


-- SET A VALUE TO THE FIX FEE FOR EACH TRANSACTION
SET @TaxaFixa = 0.95


-- GET THE FEE
SELECT @Taxa = (@ValorTransacaoBruto * @BaseCalculo) + @TaxaFixa
IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRANSACTION
		RETURN
	END


-- VERIFY IF THE FEE VALUE IS BIGGER THAN THE TRANSACTION AMOUNT
IF @Taxa > @ValorTransacaoBruto		SELECT @Taxa = @ValorTransacaoBruto
IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRANSACTION
		RETURN
	END



IF @@ERROR = 0
	BEGIN
		COMMIT TRANSACTION
		RETURN
	END
ELSE
	BEGIN
		ROLLBACK TRANSACTION
		RETURN
	END
GO