/****** Object:  StoredProcedure [dbo].[P_CG_FND_VERBA_NOTA_CREDITO_GRUPO_APURACAO]    Script Date: 04/08/2014 09:57:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_CG_FND_VERBA_NOTA_CREDITO_GRUPO_APURACAO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_CG_FND_VERBA_NOTA_CREDITO_GRUPO_APURACAO]
GO
--EXEC P_CG_FND_VERBA_NOTA_CREDITO_GRUPO_APURACAO 4
CREATE PROCEDURE P_CG_FND_VERBA_NOTA_CREDITO_GRUPO_APURACAO (@CD_CTR_GRUPO INT)
AS

SET NOCOUNT ON

DECLARE @CD_EMP INT, @CD_FILIAL INT
DECLARE @QTDE_PAR_ITENS INT

CREATE TABLE #RsParametro(CD_CTR_GRUPO INT,
						  ORDEM	INT,
						  VLR_RESSARCIMENTO MONEY,
						  CD_PAR INT,
						  QT_ITEM MONEY,
						  VLR_ITEM MONEY,
						  PERC_DESC_ITEM MONEY,
						  TP_PARAMETRO MONEY,
						  DT_INI DATETIME,
						  DT_FIM DATETIME)

CREATE TABLE #RsParametroFilial(CD_CTR_GRUPO INT,
								ORDEM	INT,
								CD_EMP INT,
								CD_FILIAL INT,
								CD_PAR INT,
								QT_ITEM MONEY,
								VLR_ITEM MONEY,
								PERC_DESC_ITEM MONEY,					  
								DT_INI DATETIME,
								DT_FIM DATETIME,
								VLR_RESSARCIMENTO MONEY)

CREATE TABLE #RsVendasItens (CD_EMP INT,
							CD_FILIAL INT,
							CD_VD INT,
							CD_PROD INT,
							CD_IT INT,
							QT_IT MONEY,
							VLR_IT MONEY,
							TX_DESC MONEY)

CREATE TABLE #QtdeParItens (ORDEM INT,
							QTDE MONEY)

--Buscando parametro para realizar o processamento
INSERT INTO 
	#RsParametro
EXEC 
	P_CG_FND_VERBA_NOTA_CREDITO_GRUPO_PARAMETRO @CD_CTR_GRUPO;

--Quantidade de itens do paramentro para 
--deixar apenas vendas com a mesma configuracao
INSERT INTO
	#QtdeParItens
SELECT
	ORDEM,
	MAX(QT_ITEM)
FROM
	#RsParametro
GROUP BY
	ORDEM

SET @QTDE_PAR_ITENS = (SELECT COUNT(*) FROM #QtdeParItens)


--Montando o grupo de parametros e as filiais
INSERT INTO 
	#RsParametroFilial
SELECT
	P.CD_CTR_GRUPO,
	P.ORDEM,
	F.CD_EMP,
	F.CD_FILIAL,
	P.CD_PAR,
	P.QT_ITEM,
	P.VLR_ITEM,
	P.PERC_DESC_ITEM,
	P.DT_INI,
	P.DT_FIM,
	p.VLR_RESSARCIMENTO
FROM
	#RsParametro P 
	INNER JOIN CG_FND_VERBA_NOTA_CREDITO_GRUPO_PRC_FILIAL F ON
		P.CD_CTR_GRUPO = F.CD_CTR_GRUPO

	--Processando vendas filial a filial
DECLARE 
	ProcMovApuraFiliais 
CURSOR FAST_FORWARD FOR 
SELECT DISTINCT
	p.CD_EMP,
	p.CD_FILIAL
FROM 
	#RsParametroFilial p
			 
OPEN ProcMovApuraFiliais;
	
FETCH NEXT FROM ProcMovApuraFiliais INTO @CD_EMP, @CD_FILIAL;

WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO 
		#RsVendasItens
	SELECT
		PDV_VD_IT.CD_EMP,
		PDV_VD_IT.CD_FILIAL,
		PDV_VD_IT.CD_VD,
		PDV_VD_IT.CD_PROD,
		ROW_NUMBER() OVER (PARTITION BY PDV_VD_IT.CD_EMP, 
									PDV_VD_IT.CD_FILIAL,
									PDV_VD_IT.CD_VD
									ORDER BY PDV_VD_IT.CD_EMP, 
									PDV_VD_IT.CD_VD)  CD_IT,
		SUM(PDV_VD_IT.QT_IT) AS QT_IT,
		ROUND(AVG(PDV_VD_IT.TOT_IT / PDV_VD_IT.QT_IT), 2) AS VLR_IT, 
		AVG(PDV_VD_IT.TX_DESC)
	FROM
		PDV_VD_IT 
		INNER JOIN #RSPARAMETROFILIAL F ON
			PDV_VD_IT.CD_EMP = F.CD_EMP
			AND PDV_VD_IT.CD_FILIAL = F.CD_FILIAL
			AND PDV_VD_IT.CD_PROD = F.CD_PAR 
		INNER JOIN PDV_VD ON
			PDV_VD_IT.CD_EMP = PDV_VD.CD_EMP
			AND PDV_VD_IT.CD_FILIAL = PDV_VD.CD_FILIAL
			AND PDV_VD_IT.CD_VD = PDV_VD.CD_VD 
		LEFT JOIN CG_FND_VERBA_NOTA_CREDITO_GRUPO_VENDAS ON
			PDV_VD_IT.CD_EMP = CG_FND_VERBA_NOTA_CREDITO_GRUPO_VENDAS.CD_EMP
			AND PDV_VD_IT.CD_FILIAL = CG_FND_VERBA_NOTA_CREDITO_GRUPO_VENDAS.CD_FILIAL
			AND PDV_VD_IT.CD_VD = CG_FND_VERBA_NOTA_CREDITO_GRUPO_VENDAS.CD_VD 
			AND CG_FND_VERBA_NOTA_CREDITO_GRUPO_VENDAS.CD_CTR_GRUPO = @CD_CTR_GRUPO
	WHERE
		PDV_VD_IT.CD_EMP=@CD_EMP
		AND PDV_VD_IT.CD_FILIAL = @CD_FILIAL
		AND PDV_VD_IT.DT_VD BETWEEN  F.DT_INI AND F.DT_FIM
		AND PDV_VD_IT.ST_IT=1
		AND PDV_VD.ST_VD=0
		AND CG_FND_VERBA_NOTA_CREDITO_GRUPO_VENDAS.CD_EMP IS NULL
	GROUP BY
		PDV_VD_IT.CD_EMP,
		PDV_VD_IT.CD_FILIAL,
		PDV_VD_IT.CD_VD,
		PDV_VD_IT.CD_PROD,
		F.ORDEM

	FETCH NEXT FROM ProcMovApuraFiliais INTO @CD_EMP, @CD_FILIAL;
END;

CLOSE ProcMovApuraFiliais;
DEALLOCATE ProcMovApuraFiliais;


--Deletando vendas que nao com a mesma configuracao do contrato
DELETE FROM 
	I
FROM
	(SELECT
		V.CD_EMP,
		V.CD_FILIAL,
		V.CD_VD,
		COUNT(V.CD_PROD) AS QTDE_PAR
	FROM
		#RSVENDASITENS V
		INNER JOIN #RSPARAMETROFILIAL F ON
			V.CD_EMP = F.CD_EMP AND
			V.CD_FILIAL = F.CD_FILIAL AND
			V.CD_PROD = F.CD_PAR AND
			V.QT_IT >= F.QT_ITEM AND
			V.VLR_IT < F.VLR_ITEM
	GROUP BY
		V.CD_EMP,
		V.CD_FILIAL,
		V.CD_VD) RS 
	INNER JOIN #RSVENDASITENS I ON
		RS.CD_EMP = I.CD_EMP AND
		RS.CD_FILIAL = I.CD_FILIAL AND
		RS.CD_VD = I.CD_VD
WHERE
	RS.QTDE_PAR < @QTDE_PAR_ITENS

--Gravando resultado 
INSERT INTO 
	CG_FND_VERBA_NOTA_CREDITO_GRUPO_VENDAS
SELECT
	DISTINCT F.CD_CTR_GRUPO,
	V.CD_EMP,
	V.CD_FILIAL,
	V.CD_VD,
	GETDATE(),
	F.VLR_RESSARCIMENTO * FLOOR(SUM(V.QT_IT) / F.QT_ITEM)
FROM
	#RSVENDASITENS V 
	INNER JOIN #RSPARAMETROFILIAL F ON
		V.CD_EMP = F.CD_EMP AND
		V.CD_FILIAL = F.CD_FILIAL AND
		V.CD_PROD = F.CD_PAR AND
		V.VLR_IT <= F.VLR_ITEM
GROUP BY
	F.CD_CTR_GRUPO,
	V.CD_EMP,
	V.CD_FILIAL,
	V.CD_VD,
	F.QT_ITEM,
	F.VLR_RESSARCIMENTO
HAVING
	SUM(V.QT_IT) >= F.QT_ITEM
ORDER BY
	F.CD_CTR_GRUPO,
	V.CD_EMP,
	V.CD_FILIAL,
	V.CD_VD

IF EXISTS (SELECT TOP 1 CD_EMP FROM #RSVENDASITENS V)
BEGIN
	UPDATE
		CG_FND_VERBA_NOTA_CREDITO_GRUPO
	SET 
		STS_GRUPO = 1
	WHERE
		CD_CTR_GRUPO=@CD_CTR_GRUPO
END


DROP TABLE #RsVendasItens;
DROP TABLE #RsParametroFilial;
DROP TABLE #RsParametro;
DROP TABLE #QtdeParItens;