USE [GECOV]
GO
/****** Object:  StoredProcedure [dbo].[GetPendenciasContratoColaborador]    Script Date: 14/07/2015 13:24:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetPendenciasContratoColaborador] 
	-- Add the parameters for the stored procedure here
	@Usuario varchar(max),
	@Credor varchar(max),
	@UF int,
	@DataInicial datetime,
	@DataFinal datetime,
	@Status varchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT 
	CR.Nome AS NOME,
	CR.Cnpj as CNPJ,
	CC.NumeroContrato as CONTRATO,
	CONCAT((SELECT [dbo].[FormatarCPF](CC.CpfDevedor)), (SELECT [dbo].[FormatarCNPJ](CC.CnpjDevedor))) AS DEVEDOR,
	CH.Chassi AS CHASSI,
	CC.Status AS SITUACAO,
	CC.DataInclusao AS DATACRIACAO, 
	US.Nome AS CRIADOPOR, 
	MF_PendenciaQuali.Valor as QUALIFICACAO,
	MF_PendenciaProto.Valor AS PROTOCOLO,
	MF_PendenciaDesc.Valor AS DESCRICAO,
	UF.UF,
	DATEDIFF(DAY, COALESCE(CAST(MF_ModificadoEm.Valor AS DATETIME), CC.DataInclusao), GETDATE()) as DIASPENDENCIA
FROM Contrato CC

	JOIN ContratoChassiModelo CCH ON CC.ContratoId = CCH.ContratoId
	JOIN UF UF ON UF.UFId = CC.UFId
	JOIN Chassi CH ON CCH.ChassiId = CH.ChassiId
	JOIN Usuario US ON CC.UsuarioInclusaoId = US.UsuarioId
	JOIN CREDOR CR ON CR.CredorId = CC.CredorId

LEFT JOIN MetadadoLaserfiche MF_PendenciaQuali ON cc.IdentificadorDocumentoLaserfiche = MF_PendenciaQuali.IdDocLf AND MF_PendenciaQuali.PROPRIEDADE = 'Pendência Qualificação'
LEFT JOIN MetadadoLaserfiche MF_PendenciaProto ON cc.IdentificadorDocumentoLaserfiche = MF_PendenciaProto.IdDocLf AND MF_PendenciaProto.PROPRIEDADE = 'Pendência Protocolo'
LEFT JOIN MetadadoLaserfiche MF_PendenciaDesc ON cc.IdentificadorDocumentoLaserfiche = MF_PendenciaDesc.IdDocLf AND MF_PendenciaDesc.PROPRIEDADE = 'Pendência Descrição'
LEFT JOIN MetadadoLaserfiche MF_ModificadoEm ON CC.IdentificadorDocumentoLaserfiche = MF_ModificadoEm.IdDocLf AND MF_ModificadoEm.PROPRIEDADE = 'Modificado em'

WHERE CC.DataInclusao BETWEEN @DataInicial AND @DataFinal
AND CC.Deletado = 0
AND (@UF IS NULL OR UF.UFId = @UF)
AND (US.UsuarioId in (SELECT Item FROM dbo.SplitString(@Usuario, ',')) OR (@Usuario IS NULL)) 
AND (CR.CredorId in (SELECT Item FROM dbo.SplitString(@Credor, ',')) OR (@Credor IS NULL)) 
AND CC.Status in (SELECT Item FROM dbo.SplitString(@Status, ','))

order by cc.DataInclusao
END