/****** Object:  StoredProcedure [dbo].[P_EST_PROD_CPL_ANALISE_FALTAS_FILIAL]    Script Date: 04/08/2014 09:57:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_PROD_CPL_ANALISE_FALTAS_FILIAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_PROD_CPL_ANALISE_FALTAS_FILIAL]
GO
CREATE PROCEDURE [dbo].[P_EST_PROD_CPL_ANALISE_FALTAS_FILIAL] (@CD_EMP tinyint = 1, @DT_INI DATE= NULL, @CD_FILIAL SMALLINT) 
--WITH ENCRYPTION 
AS

--DECLARE @CD_EMP    INT = 1,
--		@DT_INI    DATETIME = '20160829',
--		@CD_FILIAL INT=29

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

PRINT 'Inicio do procedimento - Time: '
			+ CONVERT(VARCHAR, GETDATE(), 108)

IF @DT_INI IS NULL
	BEGIN
		SET @DT_INI=DATEADD(DAY,-1,GETDATE());
	END

DECLARE @CD_FILIAL_DEPO INT,
				@CD_CTR_DEPO    INT,
				@VL_TOT_LJ      FLOAT,
				@QT_TOT_LJ      FLOAT,
				@VL_TOT_RD      FLOAT,
				@QT_TOT_RD      FLOAT,
				@InsereVdRede	tinyint,
				@ANO			 INT =YEAR(@DT_INI) ,
				@MES			 INT =MONTH(@DT_INI);

SELECT TOP 1
	@CD_FILIAL_DEPO = CD_FILIAL_DEPO
	,@CD_CTR_DEPO = CD_CTR_DEPO
FROM
	V_PRC_FILIAL_DEPOSITO
WHERE
	CD_FILIAL = @CD_FILIAL AND
	CD_EMP    = @CD_EMP;

DELETE FROM EST_PROD_CPL_ANALISE_FALTAS_FILIAL
WHERE  
	DT_MOV    = @DT_INI AND
	CD_EMP    = @CD_EMP AND
	CD_FILIAL = @CD_FILIAL;

SELECT
	@VL_TOT_LJ = SUM(VLR_VD_TOTAL)
	,@QT_TOT_LJ = SUM(QTDE_TOTAL)
FROM
	EST_PROD_CPL_ANALISE
WHERE
	CD_EMP    = @CD_EMP AND
	CD_FILIAL = @CD_FILIAL AND
	ANO       = @ANO AND
	MES       = @MES OPTION (RECOMPILE);

SELECT
	@VL_TOT_RD = SUM(VLR_VD_TOTAL)
	,@QT_TOT_RD = SUM(QTDE_TOTAL)
FROM
	EST_PROD_CPL_ANALISE
WHERE
	CD_EMP    = @CD_EMP AND
	CD_FILIAL = @CD_FILIAL_DEPO AND
	ANO       = @ANO AND
	MES       = @MES OPTION (RECOMPILE);;

PRINT 'DATA ' + CONVERT(VARCHAR, @DT_INI, 112)
			+ ' - FILIAL ' + CONVERT(VARCHAR, @CD_FILIAL )
			+ ' - DEPOSITO '
			+ CONVERT(VARCHAR, @CD_FILIAL_DEPO)
			+ ' - CD_CTR_DEPO '
			+ CONVERT(VARCHAR, @CD_CTR_DEPO)

IF @VL_TOT_RD = 0  OR
	 @QT_TOT_RD = 0
	BEGIN
		PRINT 'Rede sem participacao- Time: ' + CONVERT(VARCHAR, GETDATE(), 108);
		RETURN
	END
/**********************************************************************************************************/
-- Cria tabelas auxiliares
/**********************************************************************************************************/

begin try drop table #TmpProdFalta
end try 
begin catch end catch;

begin try drop table #TmpExcessoFalta
end try 
begin catch end catch;

begin try drop table #TmpProdFaltaAnalise
end try 
begin catch end catch;

create table #TmpExcessoFalta ( CD_PROD int,
								EXCESSO money)

create table #TmpProdFaltaAnalise (CD_FILIAL smallint,
								CD_PROD int,
								DS_PROD varchar(250),
								TIPO	varchar(20),
								NM_FABRIC	varchar(150),
								DS_ARV_MERC_LINHA varchar(150),
								DS_SIGLA	varchar(2),
								CD_ARV_MERC_LINHA int,
								CD_SIGLA	tinyint,
								MEDIAF		money,
								EST_MTZ		money,
								QT_PEND_MTZ	money,
								QT_PEND_LJ	money,
								EMAX		money,
								NECESSIDADE money); 

create table #TmpProdFalta (CD_FILIAL	smallint,
								CD_PROD int,
								DT_VD	date,
								PART_QTDE	FLOAT,
								PART_VLR FLOAT) 

/**********************************************************************************************************/

	PRINT 'Insere  os produtos em falta #TmpProdFalta - Time: ' + CONVERT(VARCHAR, GETDATE(), 108);
	
  	CREATE CLUSTERED INDEX IX_#TmpProdFaltaProd ON #TmpProdFalta (CD_FILIAL, CD_PROD);
	insert into #TmpProdFalta
	SELECT
		 a.CD_FILIAL
		 ,a.CD_PROD
		 ,a.DT_VD
		 ,SUM(a.PART_QTDE) AS PART_QTDE
		 ,SUM(a.PART_VLR)  AS PART_VLR
	 FROM
		 EST_PROD_CPL_ANALISE_DIARIO a
	 WHERE
		a.CD_EMP               = @CD_EMP AND
		a.DT_VD                = @DT_INI AND
		a.QT_EST               < 1 AND
		a.STS_PROD_EMP         = 0 AND
		a.STS_PROD_FILIAL      = 0 AND
		a.FLAG_ANALISE_FALTAS  = 0 AND
		a.LJ_FLAG_PARTIC_INDIC = 1 AND
		a.PART_VLR			   > 0 AND
		a.CD_FILIAL            = @CD_FILIAL
	 GROUP  BY
		a.CD_FILIAL
		,a.CD_PROD
		,a.DT_VD;

/*****************************************************************************************************************************************/

	PRINT 'Insere a analise de faltas #TmpProdFalta - Time: ' + CONVERT(VARCHAR, GETDATE(), 108);
	insert into #TmpProdFaltaAnalise
	SELECT
		A.CD_FILIAL
		,A.CD_PROD
		,P.DS_PROD
		,CASE WHEN P.COMPRA_POR_SUPRIMENTO = 0 THEN 'RESSUPRIMENTO'
		WHEN P.COMPRA_POR_SUPRIMENTO = 1 THEN 'SUPRIMENTO'
		WHEN P.COMPRA_POR_SUPRIMENTO = 2 THEN 'SUP-RESSUP'
		END                                        AS TIPO
		,F.NM_FABRIC
		,LINHA.DS_ARV_MERC_LINHA
		,SIGLA.DS_SIGLA
		,LINHA.CD_ARV_MERC_LINHA
		,SIGLA.CD_SIGLA
		,A.MEDIAF
		,A2.QT_EST + A2.QT_EST_EM_TERC
		+ ISNULL(A3.QT_EST, 0)                     EST_MTZ
		,A2.QT_PEND_ENT + ISNULL(A3.QT_PEND_ENT, 0) QT_PEND_MTZ
		,A.QT_PEND_ENT                              QT_PEND_LJ
		,ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0)     EMAX
		,CASE WHEN A.QTDE_LIMITADOR > 0 THEN
			CASE WHEN A.QTDE_LIMITADOR < A.QTDE_FACEAMENTO  OR
						A.QTDE_LIMITADOR < A.QTDE_FINANCIADO  OR
						( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) < A.QTDE_LIMITADOR THEN A.QTDE_LIMITADOR - ( A.QT_EST + A.QT_EST_EM_TERC )
			WHEN ( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) > A.QTDE_FACEAMENTO AND
						( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) > A.QTDE_FACEAMENTO THEN ( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) )
			WHEN A.QTDE_FACEAMENTO > ( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) AND
						A.QTDE_FACEAMENTO > A.QTDE_FINANCIADO THEN A.QTDE_FACEAMENTO - ( A.QT_EST + A.QT_EST_EM_TERC )
			WHEN A.QTDE_FINANCIADO > ( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) AND
						A.QTDE_FINANCIADO > A.QTDE_FACEAMENTO THEN A.QTDE_FINANCIADO - ( A.QT_EST + A.QT_EST_EM_TERC )
			END
		ELSE
			CASE WHEN ( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) > A.QTDE_FACEAMENTO AND
						( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) > A.QTDE_FACEAMENTO THEN ( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) - ( A.QT_EST + A.QT_EST_EM_TERC )
			WHEN A.QTDE_FACEAMENTO > ( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) AND
						A.QTDE_FACEAMENTO > A.QTDE_FINANCIADO THEN A.QTDE_FACEAMENTO - ( A.QT_EST + A.QT_EST_EM_TERC )
			WHEN A.QTDE_FINANCIADO > ( ROUND(( A.MEDIAF / 30.0 ) * A.EMAX, 0) ) AND
						A.QTDE_FINANCIADO > A.QTDE_FACEAMENTO THEN A.QTDE_FINANCIADO - ( A.QT_EST + A.QT_EST_EM_TERC )
			ELSE 0
			END
		END                                        AS NECESSIDADE
	FROM
		EST_PROD_CPL_ANALISE_DIARIO A
		INNER JOIN EST_PROD P ON
			A.CD_EMP = P.CD_EMP AND
			A.CD_PROD = P.CD_PROD
		INNER JOIN EST_PROD_FABRIC F ON
			A.CD_FABRIC = F.CD_FABRIC
		INNER JOIN EST_PROD_CPL_ANALISE_DIARIO A2 ON
			A.CD_EMP = A2.CD_EMP AND
			A.CD_PROD = A2.CD_PROD
		LEFT JOIN (SELECT
								P.CD_EMP
								,P.CD_PROD
								,SUM(A.QT_EST)      QT_EST
								,SUM(A.QT_PEND_ENT) QT_PEND_ENT
							FROM
								EST_PROD_CPL_ANALISE_DIARIO A
								INNER JOIN (SELECT
												CD_EMP
												,CD_FILIAL_DEPO_AUX CD_FILIAL
												,CD_FILIAL_DEPO
												,CD_PROD
											FROM
												PRC_FILIAL_DEPOSITO_AUXILIAR_EST_PROD
											UNION
											SELECT
												CD_EMP
												,CD_FILIAL_DEPO_AUX CD_FILIAL
												,CD_FILIAL_DEPO
												,CD_PROD
											FROM
												PRC_FILIAL_DEPOSITO_AUXILIAR_EST_PROD_CPL) P ON
									A.CD_EMP = P.CD_EMP AND
									A.CD_FILIAL = P.CD_FILIAL AND
									A.CD_PROD = P.CD_PROD
							WHERE
								A.DT_VD  = @DT_INI AND
								A.CD_EMP = @CD_EMP
							GROUP  BY
								P.CD_EMP
								,P.CD_PROD)A3 ON
			A.CD_EMP = A3.CD_EMP AND
			A.CD_PROD = A3.CD_PROD
		INNER JOIN EST_ARV_MERC_LINHA LINHA ON
			A.CD_EMP = LINHA.CD_EMP AND
			A.CD_ARV_MERC_LINHA = LINHA.CD_ARV_MERC_LINHA
		INNER JOIN EST_PROD_CURVA_SIGLA SIGLA ON
			A.CD_EMP = SIGLA.CD_EMP AND
			A.CD_SIGLA = SIGLA.CD_SIGLA
	WHERE
		A.CD_EMP              = @CD_EMP AND
		A.CD_FILIAL           = @CD_FILIAL AND
		A2.CD_FILIAL          = @CD_FILIAL_DEPO AND
		A.DT_VD               = @DT_INI AND
		A2.DT_VD              = @DT_INI AND
		A.FLAG_ANALISE_FALTAS = 0 AND
		A.STS_PROD_EMP        = 0 AND
		A.STS_PROD_FILIAL     = 0;

/*****************************************************************************************************************************************/
 

	PRINT 'Insere os excessos da rede #TmpExcessoFalta - Time: ' + CONVERT(VARCHAR, GETDATE(), 108);
	/**

	-- Desativo pois esta deixando a rotina muito lenta
	insert into #TmpExcessoFalta
	SELECT
		a.CD_PROD
		,SUM(QT_EXCESSO) EXCESSO
	FROM
		EST_PROD_CPL_ANALISE_DIARIO a 
	WHERE
		a.DT_VD  = @DT_INI AND
		a.CD_EMP = @CD_EMP
		AND a.CD_FILIAL IN (SELECT CD_FILIAL FROM 
								V_PRC_FILIAL_DEPOSITO D
								WHERE D.CD_CTR_DEPO = @CD_CTR_DEPO AND
								 D.FLAG_DEPO   = 0)
	GROUP  BY
		a.CD_PROD OPTION (MAXDOP 1);
     PRINT ' ... Time: ' + CONVERT(VARCHAR, GETDATE(), 108);
	DELETE FROM #TmpExcessoFalta WHERE EXCESSO= 0;
     CREATE CLUSTERED INDEX IX_#TmpProdFalta ON #TmpExcessoFalta (CD_PROD);
	PRINT ' ... Time: ' + CONVERT(VARCHAR, GETDATE(), 108);
	RETURN
	
	*/
/*****************************************************************************************************************************************/
	
	PRINT 'Insere a participacao da rede - Time: ' + CONVERT(VARCHAR, GETDATE(), 108);

	-- A tabela ##Tmp_p_est_prod_cpl_analise_faltas_filial_vdrede sera dropada na procedure P_EST_PROD_CPL_ANALISE_FALTAS_FILIAL_EXEC_ALL
	set @InsereVdRede = 0
	IF  OBJECT_ID('TEMPDB.dbo.##Tmp_p_est_prod_cpl_analise_faltas_filial_vdrede') IS NOT NULL
	BEGIN
		if not exists (select 1 from 
							##Tmp_p_est_prod_cpl_analise_faltas_filial_vdrede
							where
								dt_vd=@DT_INI
								and CD_CTR_DEPO=@CD_CTR_DEPO)
		begin
			truncate table ##Tmp_p_est_prod_cpl_analise_faltas_filial_vdrede
			set @InsereVdRede = 1
		end								
	END
	else
	begin
		set @InsereVdRede = 1
		create table ##Tmp_p_est_prod_cpl_analise_faltas_filial_vdrede (CD_CTR_DEPO tinyint,
																			DT_VD	date,
																			VLR_MEDIO_VD	money,
																			QT_VD_DIA		money,
																			PART_QTDE_VD	FLOAT,
																			PART_VLR_VD		FLOAT)

	end

	if @InsereVdRede = 1 
	begin		
		insert into ##Tmp_p_est_prod_cpl_analise_faltas_filial_vdrede 
		SELECT
			@CD_CTR_DEPO 
			,a.DT_VD
			,SUM(a.VLR_MEDIO_VD) AS VLR_MEDIO_VD
			,SUM(a.QT_VD_DIA)    AS QT_VD_DIA
			,SUM(a.PART_QTDE)    AS PART_QTDE_VD
			,SUM(a.PART_VLR)     AS PART_VLR_VD
		FROM
			EST_PROD_CPL_ANALISE_DIARIO a
		WHERE
			a.CD_EMP      = @CD_EMP AND
			a.DT_VD       = @DT_INI AND
			a.CD_FILIAL IN (SELECT CD_FILIAL FROM 
							V_PRC_FILIAL_DEPOSITO D
							WHERE D.CD_CTR_DEPO =  @CD_CTR_DEPO AND
								D.FLAG_DEPO   = 0) 
		GROUP  BY a.DT_VD
	end


/*****************************************************************************************************************************************/
	PRINT 'Insere o resultado de faltas - Time: ' + CONVERT(VARCHAR, GETDATE(), 108);

	INSERT INTO EST_PROD_CPL_ANALISE_FALTAS_FILIAL
							(DT_MOV, CD_FILIAL, CD_EMP, CD_PROD, DS_PROD, NM_FABRIC, DS_SIGLA,
							 DS_ARV_MERC_LINHA, TIPO_COMPRA, VLR_MEDIO_VD, QT_VD_DIA, PART_QTDE,
							 PART_VLR, VLR_PERDA_VD, QTDE_PERDA_VD, MEDIAF, EST_MTZ, EST_PEND_MTZ, EST_PEND_LJ,
							 EMAX, EXCESSO, NECESSIDADE, CD_SIGLA, CD_ARV_MERC_LINHA, CD_CTR_DEPO, PART_LJ_QTD, PART_LJ_VLR)
	SELECT
		VD_PROD.DT_VD
		,VD_PROD.CD_FILIAL
		,CD_EMP = @CD_EMP
		,VD_PROD.CD_PROD
		,DS_PROD = CPL.DS_PROD
		,NM_FABRIC = CPL.NM_FABRIC
		,DS_SIGLA = CPL.DS_SIGLA
		,DS_ARV_MERC_LINHA = CPL.DS_ARV_MERC_LINHA
		,TIPO_COMPRA = CPL.TIPO
		,VLR_MEDIO_VD = VD.VLR_MEDIO_VD
		,QT_VD_DIA = VD.QT_VD_DIA
		,PART_QTDE = ( VD_PROD.PART_QTDE / VD.PART_QTDE_VD ) * 100.0000000000
		,PART_VLR = ( VD_PROD.PART_VLR / VD.PART_VLR_VD ) * 100.0000000000
		,VLR_PERDA_VD = ROUND(( VD.VLR_MEDIO_VD * CASE WHEN VD.PART_VLR_VD > 0 THEN ( VD_PROD.PART_VLR / VD.PART_VLR_VD )
																							ELSE 0
																							END ), 6)
		,QTDE_PERDA_VD = ROUND(( VD.QT_VD_DIA * CASE WHEN VD.PART_QTDE_VD > 0 THEN ( VD_PROD.PART_QTDE / VD.PART_QTDE_VD )
																						ELSE 0
																						END ), 6)
		,MEDIAF = CPL.MEDIAF
		,EST_MTZ = CPL.EST_MTZ
		,EST_PEND_MTZ = CPL.QT_PEND_MTZ
		,EST_PEND_LJ = CPL.QT_PEND_LJ
		,EMAX = CPL.EMAX
		,EXCESSO = ISNULL(E.EXCESSO,0)
		,NECESSIDADE = ISNULL(CPL.NECESSIDADE, 0)
		,CD_SIGLA = CPL.CD_SIGLA
		,CD_ARV_MERC_LINHA=CPL.CD_ARV_MERC_LINHA
		,@CD_CTR_DEPO
		,@QT_TOT_LJ / @QT_TOT_RD
		,@VL_TOT_LJ / @VL_TOT_RD
	FROM
		#TmpProdFalta VD_PROD
		INNER JOIN ##Tmp_p_est_prod_cpl_analise_faltas_filial_vdrede VD ON
			VD_PROD.DT_VD = VD.DT_VD

		INNER JOIN #TmpProdFaltaAnalise CPL ON
			VD_PROD.CD_PROD = CPL.CD_PROD AND
			VD_PROD.CD_FILIAL = CPL.CD_FILIAL
		LEFT JOIN #TmpExcessoFalta E ON
			VD_PROD.CD_PROD = E.CD_PROD ;
			
/*****************************************************************************************************************************************/
	PRINT 'Limpa tabelas temporaria - Time: ' + CONVERT(VARCHAR, GETDATE(), 108);

	begin try drop table #TmpProdFalta
	end try 
	begin catch end catch;

	begin try drop table #TmpExcessoFalta
	end try 
	begin catch end catch;

	begin try drop table #TmpProdFaltaAnalise
	end try 
	begin catch end catch; 

	PRINT 'Fim procedimento P_EST_PROD_CPL_ANALISE_FALTAS_FILIAL - Time: '
				+ CONVERT(VARCHAR, GETDATE(), 108)
