IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_BUSCA_VENDAS_MENSAL]') 
				AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION dbo.UFD_BUSCA_VENDAS_MENSAL
GO
--SELECT * FROM UFD_BUSCA_VENDAS_MENSAL(1,900,GETDATE(), 12,37813) order by mes
CREATE  FUNCTION [dbo].[UFD_BUSCA_VENDAS_MENSAL](@CD_EMP		INT , --Codigo da Empresa
												 @CD_FILIAL		INT,   --Codigo da Filial
												 @DT_ATUAL		DATETIME,     
												 @QTD_MESES		INT,
												 @CD_PROD		INT )
RETURNS @RsRetorno TABLE (SEQ INT IDENTITY (1, 1),								
						ANO						INT,
						MES						INT,
						CD_EMP					INT,
						CD_FIL					INT,
						CD_PROD					INT,
						QTD_VENDIDO				MONEY,
						VLR_VENDIDO				MONEY,	
						VLR_CUSTO_MD				MONEY,
						LINHA					VARCHAR(300),							
						QTD_VENDIDO_LINHA	    MONEY,
						VLR_VENDIDO_LINHA	    MONEY,
						VLR_CUSTO_MD_LINHA			MONEY,								
						FABRICANTE					VARCHAR(300),
						QTD_VENDIDO_FABRICANTE	MONEY,
						VLR_VENDIDO_FABRICANTE	MONEY,
						VLR_CUSTO_MD_FABRICANTE  MONEY,		
						CATEGORIA					VARCHAR(300),						
						QTD_VENDIDO_CATEGORIA	MONEY,
						VLR_VENDIDO_CATEGORIA	MONEY,
						VLR_CUSTO_MD_CATEGORIA     MONEY, 
						FAMILIA					VARCHAR(300),
						QTD_VENDIDO_FAMILIA 	MONEY,
						VLR_VENDIDO_FAMILIA	    MONEY,
						VLR_CUSTO_MD_FAMILIA       MONEY) 
AS
BEGIN

DECLARE @RsTemp TABLE (SEQ INT IDENTITY (1, 1),								
						ANO						INT,
						MES						INT,
						CD_EMP					INT,
						CD_FIL					INT,
						CD_PROD					INT,
						QTD_VENDIDO				MONEY,
						VLR_VENDIDO				MONEY,
						VLR_CUSTO_MD			MONEY,	
						LINHA					VARCHAR(300),							
						QTD_VENDIDO_LINHA	    MONEY,
						VLR_VENDIDO_LINHA	    MONEY,	
						VLR_CUSTO_MD_LINHA      MONEY,							
						FABRICANTE					VARCHAR(300),
						QTD_VENDIDO_FABRICANTE	MONEY,
						VLR_VENDIDO_FABRICANTE	MONEY,
						VLR_CUSTO_MD_FABRICANTE MONEY,		
						CATEGORIA					VARCHAR(300),						
						QTD_VENDIDO_CATEGORIA	MONEY,
						VLR_VENDIDO_CATEGORIA	MONEY,
						VLR_CUSTO_MD_CATEGORIA MONEY, 
						FAMILIA					VARCHAR(300),
						QTD_VENDIDO_FAMILIA 	MONEY,
						VLR_VENDIDO_FAMILIA	    MONEY,
						VLR_CUSTO_MD_FAMILIA MONEY) 
DECLARE @CD_ARV_MERC_LINHA INT
DECLARE @CD_FABRIC INT
DECLARE @CD_ARV_MERC_CATEG INT
DECLARE @CD_ARV_MERC_FAMILIA INT
DECLARE @CD_FILIAL_CD INT

SELECT
	@CD_ARV_MERC_LINHA=CD_ARV_MERC_LINHA,
	@CD_ARV_MERC_CATEG=CD_ARV_MERC_CATEG,
	@CD_FABRIC=CD_FABRIC,
	@CD_ARV_MERC_FAMILIA=CD_ARV_MERC_FAMILIA
FROM
	EST_PROD_EST_ARV_MERCADOLOGICA INNER JOIN EST_PROD ON
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_EMP = EST_PROD.CD_EMP AND
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD = EST_PROD.CD_PROD 
WHERE
	EST_PROD_EST_ARV_MERCADOLOGICA.CD_PROD=@CD_PROD

DECLARE @MES INT
DECLARE @ANO INT

DECLARE @RsAno TABLE (ANO INT, MES INT, ANO_MES INT)
DECLARE @QTD_ATUAL INT
DECLARE @DATA_ATUAL DATETIME	
	SET @QTD_ATUAL = 1;

	WHILE (@QTD_ATUAL - @QTD_MESES) <= 0
		BEGIN
			SELECT @DATA_ATUAL = DATEADD ( mm , (-1 * @QTD_ATUAL), @DT_ATUAL) 
			SELECT @MES = MONTH(@DATA_ATUAL)
			SELECT @ANO = YEAR(@DATA_ATUAL)

			INSERT INTO @RsAno (ANO,MES,ANO_MES)
			SELECT 
				@ANO,
				@MES,
				@ANO*100+@MES
		SELECT @QTD_ATUAL = @QTD_ATUAL + 1
	END

DECLARE @RsProdMovimento TABLE (CD_EMP INT,
						  CD_PROD INT,
						  ANO INT,
						  MES INT,
						  QT_VD MONEY,
						  VLR_VD MONEY,
						  VLR_CUSTO_MD MONEY)

IF EXISTS (SELECT
				CD_EMP
		   FROM
				PRC_FILIAL_DEPOSITO
			WHERE
				CD_EMP =@CD_EMP
				AND CD_FILIAL = @CD_FILIAL)
begin --Soma das filiais
	INSERT INTO @RsProdMovimento
	SELECT
		CD_EMP,
		CD_PROD,
		V.ANO,
		V.MES,
		QT_VD AS QT_VD_PROD,
		VLR_VD AS VLR_VD_PROD,
		VLR_CUSTO_MD AS VLR_CUSTO_MD
	FROM
		EST_PROD_VD V INNER JOIN @RsAno a ON
		V.ANO_MES=a.ANO_MES
end
else --Filiais
begin
	INSERT INTO @RsProdMovimento
	SELECT
		CD_EMP,
		CD_PROD,
		V.ANO,
		V.MES,
		QT_VD AS QT_VD_PROD,
		VLR_VD AS VLR_VD_PROD,
		VLR_CUSTO_MD AS VLR_CUSTO_MD
	FROM
		EST_PROD_CPL_VD V INNER JOIN @RsAno a ON
		V.ANO_MES=a.ANO_MES
	WHERE
		V.CD_EMP =@CD_EMP
		AND V.CD_FILIAL =@CD_FILIAL
end

INSERT INTO @RsTemp (
				ANO,
				MES,
				CD_EMP,
				CD_FIL,
				CD_PROD,
				QTD_VENDIDO,
				VLR_VENDIDO,
				VLR_CUSTO_MD,
				LINHA,								
				QTD_VENDIDO_LINHA,
				VLR_VENDIDO_LINHA,	
				VLR_CUSTO_MD_LINHA,
				FABRICANTE,							
				QTD_VENDIDO_FABRICANTE,
				VLR_VENDIDO_FABRICANTE,
				VLR_CUSTO_MD_FABRICANTE,	
				CATEGORIA,							
				QTD_VENDIDO_CATEGORIA,
				VLR_VENDIDO_CATEGORIA,
				VLR_CUSTO_MD_CATEGORIA,
				FAMILIA,
				QTD_VENDIDO_FAMILIA,
				VLR_VENDIDO_FAMILIA,
				VLR_CUSTO_MD_FAMILIA)
SELECT
	ANO,
	MES,
	@CD_EMP,
	@CD_FILIAL,
	@CD_PROD,
	SUM(QT_VD_PROD) AS QT_VD_PROD,
	SUM(VLR_VD_PROD) AS VLR_VD_PROD,
	SUM(VLR_CUSTO_MD) AS VLR_CUSTO_MD,
	'' AS LINHA,
	SUM(QT_VD_LINHA) AS QT_VD_LINHA,
	SUM(VLR_VD_LINHA) AS VLR_VD_LINHA,
	SUM(VLR_CUSTO_MD_LINHA) AS VLR_CUSTO_MD_LINHA,
	FABRICANTE,
	SUM(QT_VD_FABRICANTE) AS QT_VD_FABRICANTE,
	SUM(VLR_VD_FABRICANTE) AS VLR_VD_FABRICANTE,
	SUM(VLR_CUSTO_MD_FABRICANTE) AS VLR_CUSTO_MD_FABRICANTE,
	CATEGORIA,
	SUM(QT_VD_CATEGORIA) AS QT_VD_CATEGORIA,
	SUM(VLR_VD_CATEGORIA) AS VLR_VD_CATEGORIA,
	SUM(VLR_CUSTO_MD_CATEGORIA) AS VLR_CUSTO_MD_CATEGORIA,
	FAMILIA,
	SUM(QT_VD_FAMILIA) AS QT_VD_FAMILIA,
	SUM(VLR_VD_FAMILIA) AS VLR_VD_FAMILIA,
	SUM(VLR_CUSTO_MD_FAMILIA) AS VLR_CUSTO_MD_FAMILIA
FROM
(
SELECT
	V.ANO,
	V.MES,
	SUM(QT_VD) AS QT_VD_PROD,
	SUM(VLR_VD) AS VLR_VD_PROD,
	SUM (VLR_CUSTO_MD) AS VLR_CUSTO_MD,
	'' AS LINHA,
	0 AS QT_VD_LINHA,
	0 AS VLR_VD_LINHA,
	0 AS VLR_CUSTO_MD_LINHA,
	'' AS FABRICANTE,
	0 AS QT_VD_FABRICANTE,
	0 AS VLR_VD_FABRICANTE,
	0 AS VLR_CUSTO_MD_FABRICANTE,
	'' AS CATEGORIA,
	0 AS QT_VD_CATEGORIA,
	0 AS VLR_VD_CATEGORIA,
	0 AS VLR_CUSTO_MD_CATEGORIA,
	'' AS FAMILIA,
	0 AS QT_VD_FAMILIA,
	0 AS VLR_VD_FAMILIA,
	0 AS VLR_CUSTO_MD_FAMILIA
FROM
	@RsProdMovimento V 
WHERE
	CD_EMP =@CD_EMP
	AND CD_PROD =@CD_PROD	
GROUP BY
	V.ANO,
	V.MES
UNION ALL
SELECT
	V.ANO,
	V.MES,
	0 AS QT_VD_PROD,
	0 AS VLR_VD_PROD,
	0 AS VLR_CUSTO_MD,
	'' AS LINHA,
	SUM(QT_VD) AS QT_VD_LINHA,
	SUM(VLR_VD) AS VLR_VD_LINHA,
	SUM(VLR_CUSTO_MD) AS VLR_CUSTO_MD_LINHA,
	'' AS FABRICANTE,
	0 AS QT_VD_FABRICANTE,
	0 AS VLR_VD_FABRICANTE,
	0 AS VLR_CUSTO_MD_FABRICANTE,
	'' AS CATEGORIA,
	0 AS QT_VD_CATEGORIA,
	0 AS VLR_VD_CATEGORIA,
	0 AS VLR_CUSTO_MD_CATEGORIA,
	'' AS FAMILIA,
	0 AS QT_VD_FAMILIA,
	0 AS VLR_VD_FAMILIA,
	0 AS VLR_CUSTO_MD_FAMILIA
FROM
	@RsProdMovimento  v INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
	v.CD_EMP = ARV.CD_EMP AND
	v.CD_PROD = ARV.CD_PROD
WHERE
	V.CD_EMP =@CD_EMP
	AND ARV.CD_ARV_MERC_LINHA=@CD_ARV_MERC_LINHA
GROUP BY
	V.ANO,
	V.MES
UNION ALL
SELECT
	V.ANO,
	V.MES,
	0 AS QT_VD_PROD,
	0 AS VLR_VD_PROD,
	0 AS VLR_CUSTO_MD,
	'' AS LINHA,
	0 AS QT_VD_LINHA,
	0 AS VLR_VD_LINHA,
	0 AS VLR_CUSTO_MD_LINHA,
	'' AS FABRICANTE,
	SUM(QT_VD) AS QT_VD_FABRICANTE,
	SUM(VLR_VD) AS VLR_VD_FABRICANTE,
	SUM(VLR_CUSTO_MD) AS VLR_CUSTO_MD_FABRICANTE,
	'' AS CATEGORIA,
	0 AS QT_VD_CATEGORIA,
	0 AS VLR_VD_CATEGORIA,
	0 AS VLR_CUSTO_MD_CATEGORIA,
	'' AS FAMILIA,
	0 AS QT_VD_FAMILIA,
	0 AS VLR_VD_FAMILIA,
	0 AS VLR_CUSTO_MD_FAMILIA
FROM
	@RsProdMovimento  v  INNER JOIN EST_PROD ARV ON
	v.CD_EMP = ARV.CD_EMP AND
	v.CD_PROD = ARV.CD_PROD
WHERE
	V.CD_EMP =@CD_EMP
	AND ARV.CD_FABRIC=@CD_FABRIC
GROUP BY
	V.ANO,
	V.MES
UNION ALL
SELECT
	V.ANO,
	V.MES,
	0 AS QT_VD_PROD,
	0 AS VLR_VD_PROD,
	0 AS VLR_CUSTO_MD,
	'' AS LINHA,
	0 AS QT_VD_LINHA,
	0 AS VLR_VD_LINHA,
	0 AS VLR_CUSTO_MD_LINHA,
	'' AS FABRICANTE,
	0 AS QT_VD_FABRICANTE,
	0 AS VLR_VD_FABRICANTE,
	0 AS VLR_CUSTO_MD_FABRICANTE,
	'' AS CATEGORIA,
	SUM(QT_VD) AS QT_VD_CATEGORIA,
	SUM(VLR_VD) AS VLR_VD_CATEGORIA,
	SUM(VLR_CUSTO_MD) AS VLR_CUSTO_MD_CATEGORIA,
	'' AS FAMILIA,
	0 AS QT_VD_FAMILIA,
	0 AS VLR_VD_FAMILIA,
	0 AS VLR_CUSTO_MD_FAMILIA
FROM
	@RsProdMovimento v  INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
	v.CD_EMP = ARV.CD_EMP AND
	v.CD_PROD = ARV.CD_PROD
WHERE
	V.CD_EMP =@CD_EMP
	AND ARV.CD_ARV_MERC_CATEG=@CD_ARV_MERC_CATEG
GROUP BY
	V.ANO,
	V.MES
) RS 
GROUP BY
	ANO,
	MES,
	LINHA,
	FABRICANTE,
	CATEGORIA,
	FAMILIA

IF @CD_ARV_MERC_FAMILIA>0
begin
UPDATE
	rs
SET
	rs.FAMILIA='',
	rs.QTD_VENDIDO_FAMILIA=RsFamilia.QT_VD_FAMILIA,
	rs.VLR_VENDIDO_FAMILIA=RsFamilia.VLR_VD_FAMILIA
FROM
	@RsTemp rs inner join (SELECT
							V.ANO,
							V.MES,
							SUM(QT_VD) AS QT_VD_FAMILIA,
							SUM(VLR_VD) AS VLR_VD_FAMILIA
						FROM
							@RsProdMovimento  v INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
							v.CD_EMP = ARV.CD_EMP AND
							v.CD_PROD = ARV.CD_PROD
						WHERE
							V.CD_EMP =@CD_EMP
							AND ARV.CD_ARV_MERC_FAMILIA=@CD_ARV_MERC_FAMILIA
						GROUP BY
							V.ANO,
							V.MES
						) RsFamilia ON
		rs.ANO = RsFamilia.ANO AND
		rs.MES = RsFamilia.MES
end
INSERT INTO @RsTemp (
				ANO,
				MES,
				CD_EMP,
				CD_FIL,
				CD_PROD,
				QTD_VENDIDO,
				VLR_VENDIDO,
				VLR_CUSTO_MD,
				LINHA,								
				QTD_VENDIDO_LINHA,
				VLR_VENDIDO_LINHA,	
				VLR_CUSTO_MD_LINHA,
				FABRICANTE,							
				QTD_VENDIDO_FABRICANTE,
				VLR_VENDIDO_FABRICANTE,	
				VLR_CUSTO_MD_FABRICANTE,
				CATEGORIA,							
				QTD_VENDIDO_CATEGORIA,
				VLR_VENDIDO_CATEGORIA,
				VLR_CUSTO_MD_CATEGORIA,
				FAMILIA,
				QTD_VENDIDO_FAMILIA,
				VLR_VENDIDO_FAMILIA,
				VLR_CUSTO_MD_FAMILIA)
SELECT
	a.ANO,
	a.MES,
	@CD_EMP,
	@CD_FILIAL,
	@CD_PROD,
	0 AS QT_VD_PROD,
	0 AS VLR_VD_PROD,
	0 AS VLR_CUSTO_MD,
	'',
	'' AS QT_VD_LINHA,
	'' AS VLR_VD_LINHA,
	0 AS VLR_CUSTO_MD_LINHA,
	'' AS FABRICANTE,
	0 AS QT_VD_FABRICANTE,
	0 AS VLR_VD_FABRICANTE,
	0 AS VLR_CUSTO_MD_FABRICANTE,
	'' AS CATEGORIA,
	0 AS QT_VD_CATEGORIA,
	0 AS VLR_VD_CATEGORIA,
	0 AS VLR_CUSTO_MD_CATEGORIA,
	'' AS FAMILIA,
	0 AS QT_VD_FAMILIA,
	0 AS VLR_VD_FAMILIA,
	0 AS VLR_CUSTO_MD_FAMILIA
FROM
	@RsAno a LEFT OUTER JOIN @RsTemp t ON
	a.ANO = t.ANO AND
	a.MES = t.MES
WHERE
	t.ANO IS NULL

INSERT INTO @RsRetorno
SELECT
	rs.ANO,
	rs.MES,
	rs.CD_EMP,
	rs.CD_FIL,
	rs.CD_PROD,
	rs.QTD_VENDIDO,
	rs.VLR_VENDIDO,
	rs.VLR_CUSTO_MD,
	EST_ARV_MERC_LINHA.DS_ARV_MERC_LINHA as LINHA,								
	rs.QTD_VENDIDO_LINHA,
	rs.VLR_VENDIDO_LINHA,	
	rs.VLR_CUSTO_MD_LINHA,
	EST_PROD_FABRIC.NM_FABRIC AS FABRICANTE,							
	rs.QTD_VENDIDO_FABRICANTE,
	rs.VLR_VENDIDO_FABRICANTE,	
	rs.VLR_CUSTO_MD_FABRICANTE,
	DS_ARV_MERC_CATEG AS CATEGORIA,							
	rs.QTD_VENDIDO_CATEGORIA,
	rs.VLR_VENDIDO_CATEGORIA,
	rs.VLR_CUSTO_MD_CATEGORIA,
	EST_ARV_MERC_FAMILIA.DS_ARV_MERC_FAMILIA AS FAMILIA,
	rs.QTD_VENDIDO_FAMILIA,
	rs.VLR_VENDIDO_FAMILIA,
	rs.VLR_CUSTO_MD_FAMILIA
FROM
	@RsTemp rs INNER JOIN EST_PROD_EST_ARV_MERCADOLOGICA ARV ON
	@CD_EMP = ARV.CD_EMP AND
	@CD_PROD = ARV.CD_PROD
	INNER JOIN EST_ARV_MERC_LINHA ON
	arv.CD_EMP = EST_ARV_MERC_LINHA.CD_EMP AND
	arv.CD_ARV_MERC_LINHA=EST_ARV_MERC_LINHA.CD_ARV_MERC_LINHA
	INNER JOIN EST_PROD ON
	ARV.CD_EMP = EST_PROD.CD_EMP AND
	ARV.CD_PROD = EST_PROD.CD_PROD 
	INNER JOIN EST_PROD_FABRIC ON
	EST_PROD.CD_FABRIC=EST_PROD_FABRIC.CD_FABRIC
	INNER JOIN EST_ARV_MERC_CATEGORIA ON
	arv.CD_EMP = EST_ARV_MERC_CATEGORIA.CD_EMP AND
	arv.CD_ARV_MERC_CATEG=EST_ARV_MERC_CATEGORIA.CD_ARV_MERC_CATEG
	INNER JOIN EST_ARV_MERC_FAMILIA ON
	arv.CD_EMP = EST_ARV_MERC_FAMILIA.CD_EMP AND
	arv.CD_ARV_MERC_FAMILIA=EST_ARV_MERC_FAMILIA.CD_ARV_MERC_FAMILIA


	RETURN		
END