/****** Object:  StoredProcedure [dbo].[P_RESOLVE_EXTRATIFICACAO_DESCONTO_FILIAL]    Script Date: 10/21/2013 11:39:07 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_RESOLVE_EXTRATIFICACAO_DESCONTO_FILIAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_RESOLVE_EXTRATIFICACAO_DESCONTO_FILIAL]
GO
GO
GO
GO

--EXEC P_RESOLVE_EXTRATIFICACAO_DESCONTO_FILIAL 1,1,'2017-05-01','2017-05-31',1
Create procedure [dbo].[P_RESOLVE_EXTRATIFICACAO_DESCONTO_FILIAL](@CD_EMP INT, 
													   @CD_FILIAL INT, 
													   @DT_INI DATETIME,
													   @DT_FIM DATETIME,
													   @FLAG_REPROCESSAR INT=0) AS 

set deadlock_priority high;
SET NOCOUNT ON;
DECLARE @CD_VD INT
CREATE TABLE #RsVendas (CD_EMP INT, 
						CD_FILIAL INT, 
						CD_VD INT, 
						CD_IT INT,
						CD_PROD INT,
						VLR_PMC MONEY,
						VLR_PMZ MONEY,
						VLR_OFERTA MONEY,
						VLR_IT MONEY,
						TIPO_DESCONTO INT,
						TX_DESC MONEY,
						VLR_DESC_CAD MONEY,
						PERC_DESC_FIDEL MONEY,
						PERC_DESC_FIDEL_MAX MONEY,
						DESC_FIXADO_CAD INT,
						DT_VD DATETIME,
						VLR_DESC_IT MONEY,
						VLR_TOTAL_ITEM MONEY,
						QT_IT MONEY)

print 'Buscando dados da venda para analise - Time : ' + convert(varchar,getdate(),108);

IF @FLAG_REPROCESSAR=0
begin
INSERT INTO #RsVendas
SELECT
	PDV_VD_IT.CD_EMP,
	PDV_VD_IT.CD_FILIAL,
	PDV_VD_IT.CD_VD,
	PDV_VD_IT.CD_IT,
	PDV_VD_IT.CD_PROD,
	ISNULL(PDV_VD_IT.VLR_PMC,0) AS VLR_PMC,
	ISNULL(PDV_VD_IT.VLR_PMZ,0) AS VLR_PMZ,
	ISNULL(PDV_VD_IT.VLR_OFERTA,0) AS VLR_OFERTA,
	PDV_VD_IT.VLR_IT,
	PDV_VD_IT.TIPO_DESCONTO,
	PDV_VD_IT.TX_DESC,
	PDV_VD_IT.VLR_DESC_CAD,
	PDV_VD_IT.PERC_DESC_FIDEL,
	PDV_VD_IT.PERC_DESC_FIDEL_MAX,
	PDV_VD_IT.DESC_FIXADO_CAD,
	PDV_VD_IT.DT_VD,
	ROUND(((PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT) * PDV_VD_IT.TX_DESC) / 100, 2),
	PDV_VD_IT.TOT_IT,
	QT_IT
FROM
	PDV_VD_IT with (nolock)
WHERE
	PDV_VD_IT.CD_EMP =@CD_EMP
	AND PDV_VD_IT.CD_FILIAL=@CD_FILIAL
	AND PDV_VD_IT.DT_VD BETWEEN @DT_INI AND @DT_FIM
	AND PDV_VD_IT.ST_IT=1
	AND isnull(PDV_VD_IT.FLAG_RODOU_EXTRAT_DESC,0) =0
end
else
begin
INSERT INTO #RsVendas
SELECT
	PDV_VD_IT.CD_EMP,
	PDV_VD_IT.CD_FILIAL,
	PDV_VD_IT.CD_VD,
	PDV_VD_IT.CD_IT,
	PDV_VD_IT.CD_PROD,
	ISNULL(PDV_VD_IT.VLR_PMC,0) AS VLR_PMC,
	ISNULL(PDV_VD_IT.VLR_PMZ,0) AS VLR_PMZ,
	ISNULL(PDV_VD_IT.VLR_OFERTA,0) AS VLR_OFERTA,
	PDV_VD_IT.VLR_IT,
	PDV_VD_IT.TIPO_DESCONTO,
	PDV_VD_IT.TX_DESC,
	PDV_VD_IT.VLR_DESC_CAD,
	PDV_VD_IT.PERC_DESC_FIDEL,
	PDV_VD_IT.PERC_DESC_FIDEL_MAX,
	PDV_VD_IT.DESC_FIXADO_CAD,
	PDV_VD_IT.DT_VD,
	ROUND(((PDV_VD_IT.QT_IT * PDV_VD_IT.VLR_IT) * PDV_VD_IT.TX_DESC) / 100, 2),
	PDV_VD_IT.TOT_IT,
	QT_IT
FROM
	PDV_VD_IT
WHERE
	PDV_VD_IT.CD_EMP =@CD_EMP
	AND PDV_VD_IT.CD_FILIAL=@CD_FILIAL
	AND PDV_VD_IT.DT_VD BETWEEN @DT_INI AND @DT_FIM
end

UPDATE
	#RSVENDAS	
SET 
	TX_DESC=((VLR_IT*QT_IT)- VLR_TOTAL_ITEM)/(VLR_IT*QT_IT)*100
WHERE
	TX_DESC>0
	AND TX_DESC <> (((VLR_IT*QT_IT)- VLR_TOTAL_ITEM)/(VLR_IT*QT_IT)*100)

--Tratando Valor do PMC
print 'Tratando o valor PMC menor que o tabela - Time : ' +convert(varchar,getdate(),108)
UPDATE
	i
SET 
	i.VLR_PMC=EST_PROD_PRECO.VLR_TABELA,
	i.VLR_PMZ=EST_PROD_PRECO.VLR_TABELA
FROM
	#RsVendas i INNER JOIN EST_PROD_PRECO ON
	i.CD_EMP = EST_PROD_PRECO.CD_EMP AND
	i.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL AND
	i.CD_PROD = EST_PROD_PRECO.CD_PROD
WHERE
	(i.VLR_PMC <> EST_PROD_PRECO.VLR_TABELA)
	OR (i.VLR_PMC < I.VLR_IT)

UPDATE
	i
SET 
	i.VLR_PMC=I.VLR_IT,
	i.VLR_PMZ=I.VLR_IT
FROM
	#RsVendas i
WHERE
	(i.VLR_PMC < I.VLR_IT)

UPDATE
	#RSVENDAS	
SET 
	VLR_OFERTA=(VLR_PMC-VLR_IT)
WHERE
	VLR_OFERTA > 0
	AND VLR_OFERTA<>(VLR_PMC-VLR_IT)
	
--Tratando Valor de oferta
print 'Tratando o valor oferta quando tabela e igual ao PMC - Time : ' +convert(varchar,getdate(),108)
UPDATE
	i
SET 
	i.VLR_OFERTA=0
FROM
	#RsVendas i
WHERE
	i.VLR_PMC = i.VLR_IT
	AND i.VLR_OFERTA > 0

print 'Tratando o valor de oferta - Time : ' +convert(varchar,getdate(),108)
--Desconto Valor Oferta
UPDATE 
	I
SET     
	TIPO_DESCONTO =6
FROM         
	#RsVendas i 
WHERE     
	(i.TIPO_DESCONTO <> 6 AND i.TIPO_DESCONTO <> 5)
	AND (i.VLR_OFERTA > 0 OR  i.VLR_PMC <> i.VLR_IT)
	AND i.TX_DESC =0

print 'Tratando o desconto PBM - Time : ' +convert(varchar,getdate(),108)
--Desconto PBM
UPDATE
	i
SET
    TIPO_DESCONTO = 5
FROM         
	#RsVendas i INNER JOIN PDV_VD_IT_VD_PBM p ON 
	i.CD_VD = p.CD_VD AND 
	i.CD_FILIAL = p.CD_FILIAL AND 
    i.CD_EMP = p.CD_EMP AND
    i.CD_IT = p.CD_IT
WHERE     
	i.TIPO_DESCONTO <> 5

print 'Tratando o desconto cadastro - Time : ' +convert(varchar,getdate(),108)
--Desconto por percentual no cadastro
UPDATE
	i
SET	
	i.TIPO_DESCONTO = 1 
FROM         
	#RsVendas i 
WHERE     
	i.TIPO_DESCONTO = 0 
	AND i.TX_DESC <= i.VLR_DESC_CAD 
	AND i.VLR_DESC_CAD > i.PERC_DESC_FIDEL
	AND i.TX_DESC > 0.01

print 'Tratando o desconto fidelidade - Time : ' +convert(varchar,getdate(),108)
--Desconto por fidelidade quanto existir desconto fidelidade maximo
UPDATE
	i
SET	
	i.TIPO_DESCONTO = 4 
FROM         
	#RsVendas i 
WHERE     
	i.TIPO_DESCONTO = 0 
	AND i.TX_DESC >= i.PERC_DESC_FIDEL
	AND i.TX_DESC <= i.PERC_DESC_FIDEL_MAX
	AND (i.VLR_DESC_CAD < i.PERC_DESC_FIDEL OR i.VLR_DESC_CAD < i.PERC_DESC_FIDEL_MAX)
	AND i.TX_DESC > 0.01
	AND i.DESC_FIXADO_CAD = 1

print 'Tratando o desconto fidelidade maximo - Time : ' +convert(varchar,getdate(),108)
--Desconto por fidelidade quanto existir sem desconto fidelidade maximo
UPDATE
	i
SET	
	i.TIPO_DESCONTO = 4 
FROM         
	#RsVendas i 
WHERE     
	i.TIPO_DESCONTO = 0 
	AND i.TX_DESC >= i.PERC_DESC_FIDEL
	AND i.PERC_DESC_FIDEL_MAX = 0
	AND i.VLR_DESC_CAD < i.PERC_DESC_FIDEL
	AND i.TX_DESC > 0.01

print 'Tratando o desconto fidelidade maximo - Time : ' +convert(varchar,getdate(),108)
--Desconto por fidelidade quanto existir sem desconto fidelidade maximo
UPDATE
	i
SET	
	i.TIPO_DESCONTO = 2
FROM         
	#RsVendas i 
WHERE     
	i.TIPO_DESCONTO = 0 
	AND i.PERC_DESC_FIDEL_MAX > 0
	AND i.TX_DESC > 0.01

print 'Tratando o PMC zerado - Time : ' +convert(varchar,getdate(),108)
--Preco PMC zerado ou menor que o valor de venda ou nulo
UPDATE
	i
SET
	i.VLR_PMC = i.VLR_IT
FROM         
	#RsVendas i 
WHERE     
	i.VLR_PMC =0 OR 
	(i.VLR_PMC < i.VLR_IT) OR 
	(i.VLR_PMC IS NULL)

UPDATE
	PDV_VD_IT
SET
	PDV_VD_IT.VLR_PMC = i.VLR_PMC,
	PDV_VD_IT.VLR_PMZ = i.VLR_PMZ,
	PDV_VD_IT.TIPO_DESCONTO = i.TIPO_DESCONTO,
	PDV_VD_IT.FLAG_RODOU_EXTRAT_DESC=1,
	PDV_VD_IT.TX_DESC = i.TX_DESC,
	PDV_VD_IT.VLR_OFERTA=i.VLR_OFERTA
FROM
	PDV_VD_IT INNER JOIN #RsVendas i on
	PDV_VD_IT.CD_EMP = i.CD_EMP AND
	PDV_VD_IT.CD_FILIAL = i.CD_FILIAL AND
	PDV_VD_IT.CD_VD = i.CD_VD AND
	PDV_VD_IT.CD_IT = i.CD_IT AND
	PDV_VD_IT.DT_VD = i.DT_VD

---Colocar na venda o tipo do produto e o percentual da comissao
UPDATE
	IT
SET
	IT.TP_PROD = ISNULL(EST_PROD_CPL.TP_PROD,0),
	IT.PERC_COMISSAO_SERVICO=ISNULL(EST_PROD_CPL.PERC_COMISSAO_SERVICO,0)
FROM 
	PDV_VD_IT IT INNER JOIN #RsVendas i on
	IT.CD_EMP = i.CD_EMP AND
	IT.CD_FILIAL = i.CD_FILIAL AND
	IT.CD_VD = i.CD_VD AND
	IT.CD_IT = i.CD_IT AND
	IT.DT_VD = i.DT_VD
	INNER JOIN EST_PROD_CPL ON 
	IT.CD_EMP = EST_PROD_CPL.CD_EMP AND 
	IT.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND 
	IT.CD_PROD = EST_PROD_CPL.CD_PROD
WHERE 
	EST_PROD_CPL.TP_PROD <> 0 

print 'Tratando venda sem cliente vinculado - Time : ' +convert(varchar,getdate(),108)
DECLARE @CD_CLI INT
SET @CD_CLI = ISNULL((SELECT 
						TOP 1 VALOR 
						FROM 
							PRC_FILIAL_CONFIG 
						WHERE 
							CD_EMP = 1 
							AND CD_CHAVE = 'CD_CLI_PADRAO'
							AND CD_FILIAL=@CD_FILIAL),0);
IF @CD_CLI=0 
begin
	SET @CD_CLI = ISNULL((SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = 1 AND CD_CHAVE = 'CD_CLI_PADRAO'),0)
end

if @CD_CLI<>0
begin
DECLARE CVendasCliente CURSOR FAST_FORWARD FOR 
select
	pdv_vd.cd_vd
from
	pdv_vd left outer join rc_cli on
	pdv_vd.cd_emp = rc_cli.cd_emp and
	pdv_vd.cd_cli = rc_cli.cd_cli
WHERE     
	pdv_vd.CD_EMP = @CD_EMP 
	AND pdv_vd.CD_FILIAL = @CD_FILIAL 
	AND pdv_vd.DT_VD BETWEEN @DT_INI AND @DT_FIM
	AND rc_cli.cd_cli is null

OPEN CVendasCliente
FETCH NEXT FROM CVendasCliente INTO @CD_VD
	WHILE @@FETCH_STATUS=0
	  begin
		
		PRINT 'Filial ' + CONVERT(VARCHAR, @CD_FILIAL) + ' ' + CONVERT(VARCHAR, @DT_INI,112) + ' a ' + CONVERT(VARCHAR, @DT_FIM,112)
		update
			pdv_vd
		set 
			CD_CLI=@CD_CLI
		from
			pdv_vd 
		WHERE     
			pdv_vd.CD_EMP = @CD_EMP 
			AND pdv_vd.CD_FILIAL = @CD_FILIAL 
			AND pdv_vd.CD_VD =@CD_VD

	 FETCH NEXT FROM CVendasCliente INTO @CD_VD
	  end

PRINT 'Concluído.'
CLOSE CVendasCliente
DEALLOCATE CVendasCliente

end