/*
    UPDATE SQL Schema 3.2
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[v_Misure]'
GO

ALTER VIEW [dbo].[v_Misure]
AS
     SELECT mi.IdxDossier,
            mi.CodScheda+'.'+CAST(mi.Vers AS NVARCHAR(50))+'.'+CAST(mi.NumScheda AS NVARCHAR(50)) AS CodSchedaVersNum,
            mi.CodScheda,
            mi.Vers,
            mi.NumScheda,
            mi.CodMisura,
            mi.Fase,
            mi.Rev,
            mi.Operatore,
            mi.DataOra,
            mi.ValorePar,
            mi.ValoreMis,
            mi.OkValMis,
            mi.CodDeroga,
            ds.NomeMisura,
            ds.TipoValMisura,
            ds.NomePar,
            ISNULL(vv.MinPar,0) AS MinPar,
            ISNULL(vv.MaxPar,999999999) AS MaxPar,
            ISNULL(vv.LIVal,0) AS LIVal,
            ISNULL(vv.LSVal,999999999) AS LSVal,
            mi.CodVisib,
            mi.Ordinale
     FROM dbo.Misure AS mi
          INNER JOIN dbo.DettScheda AS ds ON mi.CodScheda = ds.CodScheda
                                             AND mi.Vers = ds.Vers
                                             AND mi.CodMisura = ds.CodMisura
          LEFT OUTER JOIN dbo.v_ValidVal AS vv ON ds.CodScheda = vv.CodScheda
                                                  AND ds.Vers = vv.Vers
                                                  AND ds.CodMisura = vv.CodMisura
                                                  AND mi.ValorePar >= ISNULL(vv.MinPar, 0)
     WHERE(ISNULL(vv.MaxPar, 999999999) > mi.ValorePar);

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_Mis_getDataFilt]'
GO

-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.17
-- Description:	elenco records filtrati
-- =============================================
ALTER PROCEDURE [dbo].[stp_Mis_getDataFilt]
(
     @IdxDossier	INT
    ,@CodSchedaVers	NVARCHAR(100) -- formato CodScheda.Vers.Num --> da splittare
    ,@Fase		NVARCHAR(50)
    ,@Rev			INT
)
AS

DECLARE @CodScheda	NVARCHAR(50)	= '',
        @Vers		INT				= 0,
        @NumScheda	INT				= 0

-- solo se HO dei dati da splittare..
IF @CodSchedaVers <> ''
    BEGIN        
        -- splitto e recupero dati...
        SELECT	@CodScheda	= SUBSTRING ( @CodSchedaVers, 0, CHARINDEX ( '.', @CodSchedaVers ) )
        SELECT @CodSchedaVers	= REPLACE	( @CodSchedaVers, @CodScheda+'.', '' )
        SELECT	@Vers		= SUBSTRING ( @CodSchedaVers, 0, CHARINDEX ( '.', @CodSchedaVers ) )
        SELECT	@NumScheda	= REPLACE	( @CodSchedaVers, CAST(@Vers AS NVARCHAR(10))+'.', '' )
    END

SELECT *
FROM v_Misure
WHERE		IdxDossier  = CASE WHEN @IdxDossier  <> 0    THEN @IdxDossier	ELSE IdxDossier END
        AND CodScheda   = CASE WHEN @CodScheda	  <> ''   THEN @CodScheda	ELSE CodScheda	 END
        AND Vers        = CASE WHEN @Vers		  <> 0    THEN @Vers		ELSE Vers		 END
        AND NumScheda   = CASE WHEN @NumScheda	  <> 0    THEN @NumScheda	ELSE NumScheda	 END
        AND Fase        = CASE WHEN @Fase		  <> ''   THEN @Fase		ELSE Fase		 END
        AND Rev        = CASE WHEN @Rev			  >= 0	THEN @Rev			ELSE Rev		 END
ORDER BY	Ordinale, CodScheda, Vers, NumScheda, CodMisura, NomeMisura

RETURN

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO