IF OBJECT_ID('P_EST_MOVIMENTO_VENDAS_DIARIO_CONSULTA') IS NOT NULL
	BEGIN
		DROP PROCEDURE P_EST_MOVIMENTO_VENDAS_DIARIO_CONSULTA
	END
GO
--EXEC P_EST_MOVIMENTO_VENDAS_DIARIO_CONSULTA 1,'2018-08-08',0,'1,2,3,4,5,6,7'
CREATE PROCEDURE [dbo].[P_EST_MOVIMENTO_VENDAS_DIARIO_CONSULTA] (@CD_EMP INT,
																 @DT_PROC DATETIME, 
																 @TP_CUSTO AS INT=0, 
																 @CD_FILIAL_PAR VARCHAR(MAX) = '') AS 
SET NOCOUNT ON;
Print 'buscando codificacao da data do movimento - Time ' + convert(varchar(20),getdate(),108);

CREATE TABLE #RsVendasQtde(CD_EMP INT, 
							CD_FILIAL INT,
							TIPO_VENDA INT,
							QTDE_VD INT,
							VLR_PMC MONEY,
							VLR_DESC_TOTAL MONEY,
							VLR_VD_LIQ MONEY,
							VLR_CUSTO MONEY,
							VLR_CUSTO_MEDIO MONEY,
							VLR_CUSTO_GER MONEY,
							QTDE_ITENS MONEY,
							VLR_MARGEM MONEY,
							PERC_MARGEM MONEY,
							PERC_CMV MONEY)


CREATE TABLE #Filiais
(CD_EMP    INT
, CD_FILIAL INT
);

PRINT 'Inicio da rotina ' +convert(varchar(10), getdate(), 108) ;

IF @CD_FILIAL_PAR = ''
BEGIN
	INSERT INTO 
		#Filiais
	SELECT 
		CD_EMP,
		CD_FILIAL
	FROM 
		PRC_FILIAL
END
ELSE
BEGIN
	INSERT INTO #Filiais
	SELECT 
		@CD_EMP,
		ID
	FROM 
		UFD_PRM_IN_STRING(@CD_FILIAL_PAR)
	OPTION(RECOMPILE);
END

Print 'Gerando a quantidade de vendas - Time ' + convert(varchar(20),getdate(),108);
Print 'Vendas cupons fiscais - Time ' + convert(varchar(20),getdate(),108);
--Vendas cupom fiscal
INSERT INTO #RsVendasQtde
(CD_EMP, 
CD_FILIAL,
TIPO_VENDA,
QTDE_VD,
VLR_PMC,
VLR_DESC_TOTAL,
VLR_VD_LIQ,
VLR_CUSTO,   
VLR_CUSTO_MEDIO,
VLR_CUSTO_GER,
QTDE_ITENS)
SELECT       
	i.CD_EMP,   
	i.CD_FILIAL,
	0,
	COUNT(DISTINCT i.CD_VD) AS QTDE_VD,  
	SUM(ISNULL((CASE WHEN ISNULL(EST_PROD_CPL.TP_PROD,0) IN (10,11) THEN 
		ISNULL(I.TOT_IT,0)
    ELSE 
		ISNULL(I.VLR_PMC, 0)*I.QT_IT
    END),0)) AS VLR_PMC,
	SUM(ROUND(((I.QT_IT * I.VLR_IT) * I.TX_DESC) / 100, 2) + 
	(CASE WHEN ISNULL(I.VLR_PMC,0) > I.VLR_IT THEN  
			(I.VLR_PMC - I.VLR_IT) * I.QT_IT   
	ELSE  
			0  
	END)) AS VLR_DESC_ITENS, 
	SUM((CASE WHEN (I.TOT_IT - I.VLR_DESC_TOT_RAT) > 0 THEN 
		I.TOT_IT - I.VLR_DESC_TOT_RAT
	ELSE
		0
	END))  AS VLR_VD_LIQ, 

	SUM((CASE WHEN EST_PROD_CPL.TP_PROD IN (10,11) THEN 
		  I.TOT_IT - (I.TOT_IT * (EST_PROD_CPL.PERC_COMISSAO_SERVICO / 100)) 
	ELSE 
		 ISNULL(I.VLR_CUSTO,0) * I.QT_IT
	END)) AS VLR_CUSTO,

	SUM((CASE WHEN EST_PROD_CPL.TP_PROD IN (10,11) THEN 
		  I.TOT_IT - (I.TOT_IT * (EST_PROD_CPL.PERC_COMISSAO_SERVICO / 100)) 
	ELSE 
		 ISNULL(I.VLR_CUSTO_MEDIO,0) * I.QT_IT
	END)) AS VLR_CUSTO_MEDIO,

	SUM((CASE WHEN EST_PROD_CPL.TP_PROD IN (10,11) THEN 
		  I.TOT_IT - (I.TOT_IT * (EST_PROD_CPL.PERC_COMISSAO_SERVICO / 100)) 
	ELSE 
		 ISNULL(I.VLR_CUSTO_GER,0) * I.QT_IT
	END)) AS VLR_CUSTO_GER,

	SUM(I.QT_IT) AS QT_IT
FROM 
	PDV_VD AS V INNER JOIN PDV_VD_IT AS I ON   
	V.CD_EMP        = I.CD_EMP AND   
	V.CD_FILIAL = I.CD_FILIAL AND   
	V.CD_VD         = I.CD_VD  AND 
	V.DT_VD = I.DT_VD 
	INNER JOIN EST_PROD_CPL ON 
    i.CD_EMP = EST_PROD_CPL.CD_EMP AND
    i.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND
    i.CD_PROD = EST_PROD_CPL.CD_PROD 
	INNER JOIN #Filiais F ON
	V.CD_EMP = F.CD_EMP AND
	V.CD_FILIAL = F.CD_FILIAL
WHERE       
	i.CD_EMP =@CD_EMP
	AND i.DT_VD =@DT_PROC
	AND V.ST_VD=0
	AND I.ST_IT = 1
GROUP BY
	i.CD_EMP,
	i.CD_FILIAL

Print 'Vendas de recarga - Time ' + convert(varchar(20),getdate(),108);
--Vendas recarga
INSERT INTO #RsVendasQtde
(CD_EMP, 
CD_FILIAL,
TIPO_VENDA,
QTDE_VD,
VLR_PMC,
VLR_DESC_TOTAL,
VLR_VD_LIQ,
VLR_CUSTO,   
VLR_CUSTO_MEDIO,
VLR_CUSTO_GER,
QTDE_ITENS)
SELECT
	PDV_CUPOM_NAO_FISCAL.CD_EMP,
	PDV_CUPOM_NAO_FISCAL.CD_FILIAL,
	2 AS TIPO_VENDA,
	COUNT(PDV_CUPOM_NAO_FISCAL.CD_CTR) AS QTDE_VD, 
	SUM(PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM) AS VLR_PMC,
	0 AS VLR_DESC_ITENS,
	SUM(VLR_TOT_CUPOM) AS VLR_VD_LIQ,
	SUM(PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM - (PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM * ISNULL(GLB_OPER_CEL.PERC_COMIS,0))/100) AS VLR_CUSTO,   
	SUM(PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM - (PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM * ISNULL(GLB_OPER_CEL.PERC_COMIS,0))/100) AS VLR_CUSTO_MEDIO,
	SUM(PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM - (PDV_CUPOM_NAO_FISCAL.VLR_TOT_CUPOM * ISNULL(GLB_OPER_CEL.PERC_COMIS,0))/100) AS VLR_CUSTO_GER,
	COUNT(PDV_CUPOM_NAO_FISCAL.CD_CTR) AS QT_IT
FROM
	PDV_CUPOM_NAO_FISCAL INNER JOIN GLB_DT_MOVIMENTO on
	PDV_CUPOM_NAO_FISCAL.DT_CUPOM = GLB_DT_MOVIMENTO.DT_MOVIMENTO
	INNER JOIN FP_FUNC ON
	PDV_CUPOM_NAO_FISCAL.CD_USU = FP_FUNC.CD_FUNC_USU
	LEFT OUTER JOIN GLB_OPER_CEL_CPL ON
	PDV_CUPOM_NAO_FISCAL.CD_OPER_CEL = GLB_OPER_CEL_CPL.NR_OPER_CEL
	LEFT OUTER JOIN GLB_OPER_CEL ON
	GLB_OPER_CEL_CPL.CD_EMP= GLB_OPER_CEL.CD_EMP AND
	GLB_OPER_CEL_CPL.CD_OPER_CEL = GLB_OPER_CEL.CD_OPER_CEL
	INNER JOIN #Filiais F ON
	PDV_CUPOM_NAO_FISCAL.CD_EMP = F.CD_EMP AND
	PDV_CUPOM_NAO_FISCAL.CD_FILIAL = F.CD_FILIAL
WHERE
	PDV_CUPOM_NAO_FISCAL.TP_CUPOM_NAO_FISCAL=3
	AND PDV_CUPOM_NAO_FISCAL.CD_EMP=@CD_EMP
	AND PDV_CUPOM_NAO_FISCAL.DT_CUPOM =@DT_PROC
GROUP BY
	PDV_CUPOM_NAO_FISCAL.CD_EMP,
	PDV_CUPOM_NAO_FISCAL.CD_FILIAL

--Vendas devolvidas
INSERT INTO #RsVendasQtde
(CD_EMP, 
CD_FILIAL,
TIPO_VENDA,
QTDE_VD,
VLR_PMC,
VLR_DESC_TOTAL,
VLR_VD_LIQ,
VLR_CUSTO,   
VLR_CUSTO_MEDIO,
VLR_CUSTO_GER,
QTDE_ITENS)
SELECT
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL,
	3 AS TIPO_VENDA,	
	COUNT(DISTINCT PDV_VD_DEV_EST_PROD_IMP.CD_VD) *-1, 
	SUM(ISNULL(PDV_VD_DEV_EST_PROD_IMP.VLR_PMC_IT,0) * PDV_VD_DEV_EST_PROD_IMP.QT_IT) *-1 AS VLR_PMC,
	SUM(( PDV_VD_DEV_EST_PROD_IMP.VLR_DESC_IT * PDV_VD_DEV_EST_PROD_IMP.QT_IT ) +
	( CASE WHEN PDV_VD_DEV_EST_PROD_IMP.VLR_PMC_IT > PDV_VD_DEV_EST_PROD_IMP.VLR_IT_BRUTO THEN 
		(( PDV_VD_DEV_EST_PROD_IMP.VLR_PMC_IT - PDV_VD_DEV_EST_PROD_IMP.VLR_IT_BRUTO ) * PDV_VD_DEV_EST_PROD_IMP.QT_IT)
	ELSE 
		0
	END ) ) *-1 AS VLR_DESC_ITENS,
	SUM(PDV_VD_DEV_EST_PROD_IMP.VLR_IT * PDV_VD_DEV_EST_PROD_IMP.QT_IT ) *-1 AS VLR_LIQ_VD,
	SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_CUSTO) *-1 AS VLR_CUSTO,
	SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_CUSTO_MEDIO) *-1 AS VLR_CUSTO_MEDIO,
	SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT * PDV_VD_DEV_EST_PROD_IMP.VLR_CUSTO_MEDIO) *-1 AS VLR_CUSTO_GER,
	SUM(PDV_VD_DEV_EST_PROD_IMP.QT_IT) *-1
FROM
	PDV_VD_DEV_EST_PROD_IMP INNER JOIN PDV_VD ON
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = PDV_VD.CD_EMP AND
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = PDV_VD.CD_FILIAL AND
	PDV_VD_DEV_EST_PROD_IMP.CD_VD = PDV_VD.CD_VD
	INNER JOIN GLB_DT_MOVIMENTO on
	PDV_VD_DEV_EST_PROD_IMP.DT_DEVO = GLB_DT_MOVIMENTO.DT_MOVIMENTO
    INNER JOIN EST_PROD_CPL ON 
    PDV_VD_DEV_EST_PROD_IMP.CD_EMP = EST_PROD_CPL.CD_EMP AND
    PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND
    PDV_VD_DEV_EST_PROD_IMP.CD_PROD = EST_PROD_CPL.CD_PROD 
	INNER JOIN #Filiais F ON
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = F.CD_EMP AND
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL = F.CD_FILIAL
WHERE
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP = @CD_EMP 
	AND PDV_VD_DEV_EST_PROD_IMP.DT_DEVO =@DT_PROC
GROUP BY
	PDV_VD_DEV_EST_PROD_IMP.CD_EMP,
	PDV_VD_DEV_EST_PROD_IMP.CD_FILIAL


Print 'Vendas notas fiscais - Time ' + convert(varchar(20),getdate(),108);
--Vendas nota fiscal
INSERT INTO #RsVendasQtde
(CD_EMP, 
CD_FILIAL,
TIPO_VENDA,
QTDE_VD,
VLR_PMC,
VLR_DESC_TOTAL,
VLR_VD_LIQ,
VLR_CUSTO,   
VLR_CUSTO_MEDIO,
VLR_CUSTO_GER,
QTDE_ITENS)
SELECT
	EST_NF_SAI_IT.CD_EMP,
	EST_NF_SAI_IT.CD_FILIAL,
	10 AS TIPO_VENDA,
	COUNT(EST_NF_SAI_IT.CD_NF) AS QTDE_VD,
	SUM(ISNULL(EST_NF_SAI_IT.VLR_PMC,0) * EST_NF_SAI_IT.QT_IT ) AS VLR_PMC,
	SUM(EST_NF_SAI_IT.VLR_DESC_IT) AS VLR_DESC_ITENS,
	SUM(((EST_NF_SAI_IT.VLR_TOT_IT  + ISNULL(EST_NF_SAI_IT.VLR_PROD_FRETE,0) )- EST_NF_SAI_IT.VLR_DESC_IT)) AS VLR_LIQ_VD,
	SUM(EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_CUSTO_MD) AS VLR_CUSTO,
	SUM(EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_CUSTO_MD) AS VLR_CUSTO_MEDIO,
	SUM(EST_NF_SAI_IT.QT_IT * EST_NF_SAI_IT.VLR_CUSTO_MD) AS VLR_CUSTO_GER,
	SUM(EST_NF_SAI_IT.QT_IT)  AS QT_IT
FROM
	EST_NF_SAI_IT INNER JOIN GLB_DT_MOVIMENTO on
	EST_NF_SAI_IT.DT_EMI_NF = GLB_DT_MOVIMENTO.DT_MOVIMENTO
    INNER JOIN EST_NF_SAI ON 
    EST_NF_SAI_IT.CD_EMP = EST_NF_SAI.CD_EMP AND
    EST_NF_SAI_IT.CD_FILIAL = EST_NF_SAI.CD_FILIAL AND
    EST_NF_SAI_IT.CD_NF = EST_NF_SAI.CD_NF AND
	EST_NF_SAI_IT.DT_EMI_NF = EST_NF_SAI.DT_EMI_NF
	INNER JOIN EST_NF_SAI_VENDAS ON
	EST_NF_SAI.CD_EMP = EST_NF_SAI_VENDAS.CD_EMP AND
	EST_NF_SAI.CD_FILIAL = EST_NF_SAI_VENDAS.CD_FILIAL AND
	EST_NF_SAI.CD_NF = EST_NF_SAI_VENDAS.CD_NF 
	INNER JOIN #Filiais F ON
	EST_NF_SAI.CD_EMP = F.CD_EMP AND
	EST_NF_SAI.CD_FILIAL = F.CD_FILIAL		
WHERE
	EST_NF_SAI.CD_EMP = @CD_EMP 
	AND EST_NF_SAI.DT_EMI_NF =@DT_PROC
	AND EST_NF_SAI.STS_NF =0
GROUP BY
	EST_NF_SAI_IT.CD_EMP,
	EST_NF_SAI_IT.CD_FILIAL

DELETE FROM #RsVendasQtde
WHERE VLR_VD_LIQ <= 0;

IF @TP_CUSTO=0 
begin
UPDATE 
	#RsVendasQtde
SET
	VLR_MARGEM=(VLR_VD_LIQ - VLR_CUSTO_MEDIO), 
	PERC_MARGEM=((VLR_VD_LIQ - VLR_CUSTO_MEDIO)/VLR_VD_LIQ)*100,
	PERC_CMV=(VLR_CUSTO_MEDIO/VLR_VD_LIQ)*100
FROM
	#RsVendasQtde
end	

IF @TP_CUSTO=1 
begin
UPDATE 
	#RsVendasQtde
SET
	VLR_MARGEM=(VLR_VD_LIQ - VLR_CUSTO_GER), 
	PERC_MARGEM=((VLR_VD_LIQ - VLR_CUSTO_GER)/VLR_VD_LIQ)*100,
	PERC_CMV=(VLR_CUSTO_GER/VLR_VD_LIQ)*100
FROM
	#RsVendasQtde
end	

IF @TP_CUSTO=2 
begin
UPDATE 
	#RsVendasQtde
SET
	VLR_MARGEM=(VLR_VD_LIQ - VLR_CUSTO), 
	PERC_MARGEM=((VLR_VD_LIQ - VLR_CUSTO)/VLR_VD_LIQ)*100,
	PERC_CMV=(VLR_CUSTO/VLR_VD_LIQ)*100
FROM
	#RsVendasQtde
end	

SELECT
	v.CD_FILIAL AS Filial,
	V_BI_FILIAL.FilialNome,
	V_BI_FILIAL.UF,
	V_BI_FILIAL.Cidade,
	V_BI_FILIAL.Bairro,
	V_BI_FILIAL.RegiaoFilial,
	V_BI_FILIAL.Supervisor,
	V_BI_MOVIMENTO_TIPO.DsTipoMovimento,
	v.QTDE_VD as QtdeClientes ,
	v.VLR_PMC as VlrPMC,
	v.VLR_DESC_TOTAL as VlrDescTotal,
	v.VLR_VD_LIQ as VlrVdLiquida,
	v.VLR_CUSTO as VlrCustoUltimo,   
	v.VLR_CUSTO_MEDIO as VlrCustoMedio,
	v.VLR_CUSTO_GER as VlrCustoGer,
	v.QTDE_ITENS as QtdeItens,
	v.VLR_MARGEM AS VlrMargem
FROM
	#RsVendasQtde v INNER JOIN V_BI_MOVIMENTO_TIPO ON
	v.TIPO_VENDA = V_BI_MOVIMENTO_TIPO.TipoMovimento
	INNER JOIN V_BI_FILIAL ON
	v.CD_FILIAL = V_BI_FILIAL.Filial
RETURN
DROP TABLE #RsVendasQtde;


