SSC/CMS_SC/sql/SQL Update 3.2.18 schema.sql

/*
UPDATE SCHEMA CMS 3_2_18
*/
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
/*
 2018.03.26 sistemata solo formattazione
*/

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 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_rep_getDossier]'
GO


-- =============================================
-- Author:		Steamware
-- Description:	elenco records x dossier x report
-- Create date: 2015.06.18
-- Mod. Date  : 2018.03.19
-- Mod. Date  : 2018.03.26  agg. Note revisione
-- Mod. Date  : 2018.03.26  tolte misure di tipo FILE
-- =============================================
ALTER PROCEDURE [dbo].[stp_rep_getDossier]
(
     @IdxDossier	INT
    ,@Fase			NVARCHAR(50)
    ,@Rev			INT				= 0
    ,@Lingua		NVARCHAR(3)		= 'IT'
    ,@CodVisib		NVARCHAR(50)	= 'F'	
)
AS

DECLARE @NoteRevis AS NVARCHAR(max) = '';

-- solo per il report Full estraggo le note revisione
IF @CodVisib = 'F'
    -- se presente prende sempre le note in elencoDossier altrimenti prendo l'ultima valida dello storico (dovrebbe cmq essere una sola)
    WITH cteNote AS
    (
        SELECT Note, DataMod
        FROM dbo.NoteDossier
        WHERE IdxDossier = @IdxDossier AND Fase = @Fase AND Rev = @Rev
          UNION
        SELECT Note, GETDATE() FROM dbo.ElencoDossier
        WHERE IdxDossier = @IdxDossier AND FaseAct = @Fase AND RevAct = @Rev
    )
    SELECT TOP 1 @NoteRevis = Note 
    FROM cteNote
    ORDER BY DataMod DESC

-- estrazione dati misure
SELECT	mi.IdxDossier, 
		mi.CodSchedaVersNum, 
		dbo.f_getStringLang(sc.Descrizione, @Lingua,0) AS DescrizioneScheda,
		mi.CodScheda, 
		mi.Vers, 
		mi.NumScheda, 
		mi.CodMisura, 
		mi.Fase, 
		mi.Rev, 
		mi.Operatore, 
		mi.DataOra, 
		mi.ValorePar, 
		mi.ValoreMis, 
		mi.OkValMis, 
		mi.CodDeroga, 
		dbo.f_getStringLang(mi.NomeMisura, @Lingua,0) AS NomeMisura,
		mi.TipoValMisura, 
		dbo.f_getStringLang(mi.NomePar, @Lingua,0) AS NomePar,
		mi.MinPar, 
		mi.MaxPar, 
		mi.LIVal, 
		mi.LSVal, 
		mi.CodVisib, 
		mi.Ordinale, 
		fa.Descrizione AS DescFase, 
		em.Cliente, 
		em.Matricola   AS MatrMacchina, 
		em.Descrizione AS DescMacchina,
        @NoteRevis     AS NoteVersione
FROM    dbo.v_Misure			AS mi 
INNER JOIN dbo.AnagFasi			AS fa ON mi.Fase = fa.Fase 
INNER JOIN dbo.ElencoDossier	AS do ON mi.IdxDossier = do.IdxDossier 
INNER JOIN dbo.ElencoMacchineCN	AS em ON do.Matricola = em.Matricola 
INNER JOIN dbo.AnagSchede		AS sc ON mi.CodScheda = sc.CodScheda AND mi.Vers = sc.Vers
WHERE	( mi.IdxDossier	= @IdxDossier	OR @IdxDossier = 0 )            -- mettere ISNULL su variabile x estrarre anche con NULL?
	AND ( mi.Fase		= @Fase			OR @Fase = '' )
	AND ( mi.Rev		= @Rev)
    AND mi.TipoValMisura <> 'FILE'                    -- escludo misure di tipo FILE 2018.03.27
	AND ( mi.CodVisib	LIKE '%'+@CodVisib+'%' )
ORDER BY CodScheda, Vers, NumScheda, CodMisura, NomeMisura


RETURN


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


/*************************************
* STORED PROCEDURE stp_getByLemmaLike
*
* elenco record da ricerca LIKE su lemma
* Se passo come lingua LB mostra il lemma e non la traduzione nel caso serva sapere la LABEL
*
* aut : S.E. Locatelli
* mod : 2014.04.28
* 
**************************************/
ALTER PROCEDURE [voc].[stp_getByLinguaLemmaLike]
(
	@Lingua NVARCHAR(3),
	@search NVARCHAR(50)
)
AS

SET NOCOUNT ON;

SELECT   Lingua, Lemma
        ,CASE WHEN @Lingua = 'LB' THEN CONVERT(NVARCHAR(500),Lemma) 
                                  ELSE Traduzione END 
         AS Traduzione          -- la traduzione corrisponde ai campi/lemmi x i report
FROM dbo.Vocabolario
WHERE (Lemma LIKE @search + '%')
      AND ( Lingua =  CASE WHEN @Lingua = 'LB'      -- se richiede le Label dei campi estraggo i lemmi in inglese
                           THEN 'EN' 
                           ELSE @Lingua 
                        END )
/* OLD
SELECT  Lingua, Lemma, Traduzione
FROM	Vocabolario
WHERE   (Lemma LIKE @search + '%')
	AND	(Lingua = @Lingua)
*/


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