/****** Object:  StoredProcedure [dbo].[P_SNGPC_MOVIMENTOS_PENDENTE]    Script Date: 10/07/2012 18:09:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_SNGPC_MOVIMENTOS_PENDENTE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_SNGPC_MOVIMENTOS_PENDENTE]
GO
GO
--EXEC P_SNGPC_MOVIMENTOS_PENDENTE 1,38
CREATE PROCEDURE P_SNGPC_MOVIMENTOS_PENDENTE @CD_EMP INT, @CD_FILIAL INT
 WITH 
 ENCRYPTION 
AS
/*
	Esta procedure e processada dentro da procedure
	"P_SNGPC_MOVIMENTOS_PENDENTE_EXECUTE_ALL"
*/
SET NOCOUNT ON 
SET ANSI_WARNINGS OFF; 
DELETE FROM SNGPC_MOVIMENTOS_PENDENTE WHERE CD_EMP=@CD_EMP AND CD_FILIAL=@CD_FILIAL

CREATE TABLE #RSFILIALMOVIMENTO (CD_EMP INT,
				        CD_FILIAL INT,
				        DT_MOVIMENTO DATETIME,
						QT_ENTRADAS INT,
						QT_VENDAS INT,
						QT_SAIDAS INT,
						QT_TRANSF INT,
						QT_PERDAS INT,
						DT_ULT_ARQUIVO DATETIME,
						DT_ULT_VALIDADO DATETIME,
						DT_REINICIO DATETIME,
						QT_REINICIO INT)


CREATE TABLE #RSFILIALREINICIO (CD_EMP INT,
				        CD_FILIAL INT,
				        DT_REINICIO DATETIME,
						QTDE INT)

CREATE TABLE #RSFILIALENVIOULTIMO (CD_EMP INT,
				        CD_FILIAL INT,
				        DT_ENVIO_ULTIMO DATETIME)
--DADOS DA FILIAL
INSERT INTO #RSFILIALMOVIMENTO(CD_EMP,
				        CD_FILIAL,
				        DT_MOVIMENTO,
						QT_ENTRADAS,
						QT_VENDAS,
						QT_SAIDAS,
						QT_TRANSF,
						QT_PERDAS,
						DT_ULT_ARQUIVO,
						DT_ULT_VALIDADO,
						DT_REINICIO,
						QT_REINICIO)
SELECT
	PRC_FILIAL.CD_EMP,
	PRC_FILIAL.CD_FILIAL,
	CONVERT(CHAR(10),GETDATE(),112),
	0 AS QT_ENTRADAS,
	0 AS QT_VENDAS,
	0 AS QT_SAIDAS,
	0 AS QT_TRANSF,
	0 AS QT_PERDAS,
	NULL AS DT_ULT_ARQUIVO,
	NULL AS DT_ULT_VALIDADO,
	NULL AS DT_REINICIO,
	0 AS QT_REINICIO
FROM
	PRC_FILIAL LEFT OUTER JOIN  PRC_FILIAL_DEPOSITO ON
	PRC_FILIAL.CD_EMP = PRC_FILIAL_DEPOSITO.CD_EMP AND
	PRC_FILIAL.CD_FILIAL = PRC_FILIAL_DEPOSITO.CD_FILIAL
WHERE
	PRC_FILIAL.CD_EMP=@CD_EMP
	AND PRC_FILIAL.CD_FILIAL=@CD_FILIAL

INSERT INTO #RSFILIALREINICIO(CD_EMP, CD_FILIAL, DT_REINICIO,QTDE)
SELECT
	C.CD_EMP,
	C.CD_FILIAL,
	MAX(C.DT_REINICIO),
	COUNT(C.CD_FILIAL) AS QTDE
FROM
	EST_SNGPC_REINICIO_INVENTARIO C
WHERE
	CD_EMP=@CD_EMP
	AND CD_FILIAL=@CD_FILIAL
GROUP BY
	CD_EMP,
	CD_FILIAL

INSERT INTO #RSFILIALENVIOULTIMO(CD_EMP, CD_FILIAL, DT_ENVIO_ULTIMO)
SELECT
	B.CD_EMP,
	B.CD_FILIAL,
	MAX(B.DT_FINAL)
FROM
	EST_SNGPC_RELATORIOS_ANVISA B 
WHERE
	B.CD_EMP=@CD_EMP
	AND B.CD_FILIAL=@CD_FILIAL
	AND B.ACEITO = 1
GROUP BY
	CD_EMP,
	CD_FILIAL

UPDATE
	F
SET
	F.DT_MOVIMENTO =CASE WHEN  NOT R.DT_REINICIO IS NULL AND U.DT_ENVIO_ULTIMO IS NULL  THEN
							R.DT_REINICIO  
					WHEN  R.DT_REINICIO IS NULL AND NOT U.DT_ENVIO_ULTIMO IS NULL  THEN
							U.DT_ENVIO_ULTIMO  
					WHEN  R.DT_REINICIO > U.DT_ENVIO_ULTIMO THEN
							R.DT_REINICIO
					  WHEN U.DT_ENVIO_ULTIMO < F.DT_MOVIMENTO THEN
						 U.DT_ENVIO_ULTIMO
					  ELSE
						F.DT_MOVIMENTO
					  END,
	F.QT_REINICIO = R.QTDE,
	F.DT_REINICIO = R.DT_REINICIO
FROM
	#RSFILIALMOVIMENTO F LEFT OUTER JOIN #RSFILIALREINICIO R ON
	F.CD_EMP = R.CD_EMP AND 
	F.CD_FILIAL = R.CD_FILIAL 
	LEFT OUTER JOIN #RSFILIALENVIOULTIMO U ON
	F.CD_EMP = U.CD_EMP AND 
	F.CD_FILIAL = U.CD_FILIAL;


WITH TblQtdeMovimento
AS
(
SELECT
	CD_EMP,
	CD_FILIAL,
	ISNULL(SUM(QT_ENTRADAS),0) AS QT_ENTRADAS,
	ISNULL(SUM(QT_VENDAS),0) AS QT_VENDAS,
	ISNULL(SUM(QT_SAIDAS),0) AS QT_SAIDAS,
	ISNULL(SUM(QT_TRANSF),0) AS QT_TRANSF,
	ISNULL(SUM(QT_PERDAS),0) AS QT_PERDAS
FROM
(
SELECT 
	A.CD_EMP,
	A.CD_FILIAL,
	0 AS QT_ENTRADAS,
	ISNULL(COUNT(A.CD_FILIAL),0) AS QT_VENDAS,
	0 AS QT_SAIDAS,
	0 AS QT_TRANSF,
	0 AS QT_PERDAS
FROM 
	V_EST_SNGPC_VENDAS A(NOLOCK) INNER JOIN #RSFILIALMOVIMENTO M ON
	A.CD_EMP = M.CD_EMP AND
	A.CD_FILIAL = M.CD_FILIAL AND
	A.DT_VD > M.DT_MOVIMENTO
WHERE
	COALESCE(A.LANCADO, 0) < A.QT_IT
GROUP BY
	A.CD_EMP,
	A.CD_FILIAL
UNION ALL
SELECT   
	A.CD_EMP,
	A.CD_FILIAL , 
	ISNULL(COUNT(A.CD_FILIAL),0) AS QT_ENTRADAS,
	0 AS QT_VENDAS,
	0 AS QT_SAIDAS,
	0 AS QT_TRANSF,
	0 AS QT_PERDAS
FROM     
	V_EST_SNGPC_ENTRADAS A (NOLOCK) INNER JOIN #RSFILIALMOVIMENTO M ON
	A.CD_EMP = M.CD_EMP AND
	A.CD_FILIAL = M.CD_FILIAL AND
	A.DT_ENT > M.DT_MOVIMENTO
WHERE
	COALESCE(A.LANCADO,0) < QT_IT 
GROUP BY
	A.CD_EMP,
	A.CD_FILIAL
UNION ALL
	SELECT	    
	NF.CD_EMP,
	NF.CD_FILIAL,
	0 AS QT_ENTRADAS,
	0 AS QT_VENDAS,
	CASE WHEN NOT MOV.TP_MOV IN (10,12,16,24) THEN
		ISNULL(COUNT(NF.CD_FILIAL),0)
	ELSE
		0
	END AS  QT_SAIDAS,
	CASE WHEN MOV.TP_MOV IN (10) THEN
		ISNULL(COUNT(NF.CD_FILIAL),0)
	ELSE
		0
	END AS  QT_TRANSF,
	CASE WHEN MOV.TP_MOV IN (12,16,24) THEN
		ISNULL(COUNT(NF.CD_FILIAL),0)
	ELSE
		0
	END AS QT_PERDAS
	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                        
	INNER JOIN PRC_FILIAL AS FLL ON                            
	NF.CD_CLI = FLL.CD_FILIAL    
	INNER JOIN #RSFILIALMOVIMENTO M ON
	SAI.CD_EMP = M.CD_EMP AND
	SAI.CD_FILIAL = M.CD_FILIAL 	                   
	GROUP  BY                                                   
		NF.CD_EMP,
		NF.CD_FILIAL,                                            
		MOV.TP_MOV) rs
GROUP BY
	CD_EMP,
	CD_FILIAL
)

UPDATE
	F
SET
	F.QT_ENTRADAS = ISNULL(T.QT_ENTRADAS,0),
	F.QT_VENDAS = ISNULL(T.QT_VENDAS,0),
	F.QT_SAIDAS = ISNULL(T.QT_SAIDAS,0),
	F.QT_TRANSF = ISNULL(T.QT_TRANSF,0),
	F.QT_PERDAS = ISNULL(T.QT_PERDAS,0)
FROM
	#RSFILIALMOVIMENTO F LEFT OUTER JOIN TblQtdeMovimento T ON
	F.CD_EMP = T.CD_EMP AND 
	F.CD_FILIAL = T.CD_FILIAL;

WITH TblEnvio
AS
(
SELECT 
rs.CD_EMP,
rs.CD_FILIAL, 
MAX(DT_ACEITO) AS DT_ACEITO,
MAX(DT_ENVIADO) AS DT_ENVIADO
FROM
(SELECT 
	B.CD_EMP,
	B.CD_FILIAL,
	MAX(B.DT_FINAL) AS DT_ACEITO,
	NULL DT_ENVIADO
FROM   
	EST_SNGPC_RELATORIOS_ANVISA B INNER JOIN #RSFILIALMOVIMENTO F ON
	B.CD_EMP = F.CD_EMP AND
	B.CD_FILIAL = F.CD_FILIAL 
WHERE  
	B.ACEITO = 1 
GROUP BY
	B.CD_EMP,
	B.CD_FILIAL
UNION ALL
SELECT 
	B.CD_EMP,
	B.CD_FILIAL,
	NULL AS DT_ACEITO,
	MAX(B.DT_FINAL) DT_ENVIADO
FROM   
	EST_SNGPC_RELATORIOS_ANVISA B INNER JOIN #RSFILIALMOVIMENTO F ON
	B.CD_EMP = F.CD_EMP AND
	B.CD_FILIAL = F.CD_FILIAL 
WHERE  
	B.ENVIADO = 1 
GROUP BY
	B.CD_EMP,
	B.CD_FILIAL) rs
GROUP BY
	rs.CD_EMP,
	rs.CD_FILIAL
)

UPDATE
	F
SET
	F.DT_ULT_ARQUIVO = T.DT_ENVIADO,
	F.DT_ULT_VALIDADO = T.DT_ACEITO
FROM
	#RSFILIALMOVIMENTO F LEFT OUTER JOIN TblEnvio T ON
	F.CD_EMP = T.CD_EMP AND 
	F.CD_FILIAL = T.CD_FILIAL;

INSERT INTO SNGPC_MOVIMENTOS_PENDENTE
(CD_EMP,
CD_FILIAL,
DT_MOVIMENTO,
QT_ENTRADAS,
QT_VENDAS,
QT_SAIDAS,
QT_TRANSF,
QT_PERDAS,
DT_ULT_ARQUIVO,
DT_ULT_VALIDADO,
DT_REINICIO,
QT_REINICIO)

SELECT
	CD_EMP,
	CD_FILIAL,
	DT_MOVIMENTO,
	ISNULL(QT_ENTRADAS,0),
	ISNULL(QT_VENDAS,0),
	ISNULL(QT_SAIDAS,0),
	ISNULL(QT_TRANSF,0),
	ISNULL(QT_PERDAS,0),
	DT_ULT_ARQUIVO,
	DT_ULT_VALIDADO,
	DT_REINICIO,
	ISNULL(QT_REINICIO,0)
FROM
	#RSFILIALMOVIMENTO

DROP TABLE #RSFILIALMOVIMENTO
DROP TABLE #RSFILIALREINICIO
DROP TABLE #RSFILIALENVIOULTIMO
