IF EXISTS (SELECT * FROM SYS.procedures WHERE NAME = 'P_PAINEL_SNGPC')
BEGIN
	DROP PROCEDURE P_PAINEL_SNGPC
END
GO

CREATE PROCEDURE P_PAINEL_SNGPC ( @CD_FILIAL INT=0)
AS
set nocount on;
IF  OBJECT_ID('TEMPDB.DBO.#TMP_RESULT') IS NOT NULL
BEGIN
		DROP TABLE #TMP_RESULT
END
IF  OBJECT_ID('TEMPDB.DBO.#RS_FIL') IS NOT NULL
BEGIN
		DROP TABLE #RS_FIL
END
IF  OBJECT_ID('TEMPDB.DBO.#RS_FIL_DATA') IS NOT NULL
BEGIN
		DROP TABLE #RS_FIL_DATA
END

CREATE TABLE #RS_FIL_DATA
	(
		 CD_FILIAL INT,
		 DATA      DATETIME
	)

CREATE TABLE #RS_FIL
	(
		 CD_FILIAL INT,
		 DATA      DATETIME
	)

CREATE TABLE #TMP_RESULT
	(
		 CD_FILIAL INT,
		 TIPO      VARCHAR(50),
		 TOTAL     MONEY
	)


INSERT INTO #RS_FIL_DATA
SELECT
	F.CD_FILIAL
	,ISNULL(MAX(DT_FINAL), '2015-01-01') ULT_REL_ANVISA
FROM
	PRC_FILIAL F
	LEFT JOIN EST_SNGPC_RELATORIOS_ANVISA B ON
		F.CD_EMP = B.CD_EMP AND
		F.CD_FILIAL = B.CD_FILIAL AND
		B.ACEITO = 1
WHERE
	F.CD_EMP=1
	AND CASE WHEN @CD_FILIAL =0 THEN 0 ELSE
		F.CD_FILIAL END = @CD_FILIAL
GROUP  BY
	F.CD_FILIAL

INSERT INTO #RS_FIL_DATA
SELECT
	F.CD_FILIAL
	,ISNULL(MAX(C.DT_REINICIO), '2015-01-01') ULT_INVENT
FROM
	PRC_FILIAL F
	LEFT JOIN EST_SNGPC_REINICIO_INVENTARIO C ON
		F.CD_EMP = C.CD_EMP AND
		F.CD_FILIAL = C.CD_FILIAL
WHERE
	F.CD_EMP=1
	AND CASE WHEN @CD_FILIAL =0 THEN 0 ELSE
		F.CD_FILIAL END = @CD_FILIAL
GROUP  BY
	F.CD_FILIAL

INSERT INTO #RS_FIL
SELECT
	CD_FILIAL
	,MAX(DATA)
FROM
	#RS_FIL_DATA
WHERE
	CD_FILIAL NOT IN(SELECT CD_FILIAL FROM V_PRC_FILIAL_RELACAO_DEPOSITOS)
GROUP  BY
	CD_FILIAL;

-------------------VENDAS----------------------------------  

INSERT INTO #TMP_RESULT
SELECT
	A.CD_FILIAL
	,'VENDAS'           AS TIPO
	,COUNT(A.CD_FILIAL) TOTAL
FROM
	V_EST_SNGPC_VENDAS A
	JOIN #RS_FIL F ON
		A.CD_FILIAL = F.CD_FILIAL AND
		A.DT_VD > F.DATA
WHERE
	COALESCE(LANCADO, 0) < QT_IT AND
	A.CD_EMP = 1
GROUP  BY
	A.CD_FILIAL;

-----------------ENTRADAS-------------------------------
INSERT INTO #TMP_RESULT
SELECT
	A.CD_FILIAL
	,'ENTRADAS' AS TIPO
	,COUNT(A.CD_FILIAL)
FROM
	V_EST_SNGPC_ENTRADAS A
	JOIN #RS_FIL F ON
		A.CD_FILIAL = F.CD_FILIAL AND
		A.DT_ENT > F.DATA
WHERE
	COALESCE(LANCADO, 0) < QT_IT AND
	CD_EMP  = 1
GROUP  BY
	A.CD_FILIAL

-------------------SAIDAS NF------------------------------
INSERT INTO #TMP_RESULT
SELECT
	F.CD_FILIAL
	,CASE WHEN MOV.TP_MOV = 10 THEN 'TRASFERENCIA'
	 WHEN MOV.TP_MOV = 12 THEN 'PERDAS'
	 WHEN MOV.TP_MOV = 16 THEN 'PERDAS'
	 WHEN MOV.TP_MOV = 24 THEN 'PERDAS'
	 ELSE 'SAIDA NOTA FISCAL'
	 END                 AS TIPO
	,COUNT(NF.CD_FILIAL) AS TOTAL
FROM
	EST_NF_SAI_IT_EST_SNGPC_PENDENTE AS SAI
	INNER JOIN EST_NF_SAI AS NF ON
		SAI.CD_EMP = NF.CD_EMP AND
		SAI.CD_FILIAL = NF.CD_FILIAL AND
		SAI.CD_NF = NF.CD_NF
	INNER JOIN GLB_MOV_OP AS MOV ON
		MOV.CD_MOV_OP = NF.CD_NT_MOV AND
		MOV.TP_MOV NOT IN ( 11, 26, 28 ) AND
		MOV.TP_MOV_OP_TRANSA = 1 AND 
		MOV.DEST_ORIG=2 -->filiais
	INNER JOIN #RS_FIL AS F ON
		NF.CD_CLI = F.CD_FILIAL AND
		NF.DT_EMI_NF > F.DATA
WHERE
	SAI.CD_EMP = 1
GROUP  BY
	F.CD_FILIAL
	,MOV.TP_MOV 

SELECT * 
FROM 
	#TMP_RESULT
	
IF  OBJECT_ID('TEMPDB.DBO.#TMP_RESULT') IS NOT NULL
BEGIN
	DROP TABLE #TMP_RESULT
END
IF  OBJECT_ID('TEMPDB.DBO.#RS_FIL') IS NOT NULL
BEGIN
	DROP TABLE #RS_FIL
END
IF  OBJECT_ID('TEMPDB.DBO.#RS_FIL_DATA') IS NOT NULL
BEGIN
	DROP TABLE #RS_FIL_DATA
END


