/*
     Modifiche SQL CMS_SC 3.1 -> SCHEMA

     ATTENZIONE SONO DUE SCRIPT SEPARATI
*/
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'Dropping extended properties'
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'ElencoDossier', 'COLUMN', N'Note'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'ElencoDossier', 'COLUMN', N'Stato'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'CodDeroga'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'CodVisib'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'OkValMis'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'Ordinale'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'ValoreMis'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'ValorePar'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_DiagramPane1', 'SCHEMA', N'dbo', 'VIEW', N'v_ElencoDossier', NULL, NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_dropextendedproperty N'MS_DiagramPaneCount', 'SCHEMA', N'dbo', 'VIEW', N'v_ElencoDossier', NULL, NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping foreign keys from [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] DROP CONSTRAINT [FK_ElencoDossier_ElencoMacchineCN]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping foreign keys from [dbo].[SchemaCollMacc]'
GO
ALTER TABLE [dbo].[SchemaCollMacc] DROP CONSTRAINT [FK_SchemaCollMacc_ElencoDossier]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping foreign keys from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [FK_IstanzeMisura_AnagFasi]
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [FK_Misure_SchemaCollMacc]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] DROP CONSTRAINT [PK_ElencoDossier]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] DROP CONSTRAINT [DF_ElencoDossier_Stato]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] DROP CONSTRAINT [DF__ElencoDoss__Note__54968AE5]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [PK_Misure_1]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_Misure_Vers]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_Misure_NumScheda]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_IstanzeMisura_Operatore]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_IstanzeMisura_DataOra]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_Misure_ValorePar]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_Misure_ValoreMis]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_Misure_OkMis]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_Misure_CodDeroga]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_Misure_CodVisib]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] DROP CONSTRAINT [DF_Misure_Ordinale]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Rebuilding [dbo].[Misure]'
GO
CREATE TABLE [dbo].[RG_Recovery_1_Misure]
(
[IdxDossier] [int] NOT NULL,
[CodScheda] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Vers] [int] NOT NULL CONSTRAINT [DF_Misure_Vers] DEFAULT ((0)),
[NumScheda] [int] NOT NULL CONSTRAINT [DF_Misure_NumScheda] DEFAULT ((1)),
[CodMisura] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Fase] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Rev] [int] NOT NULL CONSTRAINT [DF_Misure_Vers1] DEFAULT ((0)),
[Operatore] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_IstanzeMisura_Operatore] DEFAULT (''),
[DataOra] [datetime] NOT NULL CONSTRAINT [DF_IstanzeMisura_DataOra] DEFAULT (getdate()),
[ValorePar] [float] NOT NULL CONSTRAINT [DF_Misure_ValorePar] DEFAULT ((0)),
[ValoreMis] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Misure_ValoreMis] DEFAULT (''),
[OkValMis] [bit] NOT NULL CONSTRAINT [DF_Misure_OkMis] DEFAULT ((0)),
[CodDeroga] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Misure_CodDeroga] DEFAULT (''),
[CodVisib] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Misure_CodVisib] DEFAULT ('FCS'),
[Ordinale] [int] NOT NULL CONSTRAINT [DF_Misure_Ordinale] DEFAULT ((0))
) ON [PRIMARY]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
INSERT INTO [dbo].[RG_Recovery_1_Misure]([IdxDossier], [CodScheda], [Vers], [NumScheda], [CodMisura], [Fase], [Operatore], [DataOra], [ValorePar], [ValoreMis], [OkValMis], [CodDeroga], [CodVisib], [Ordinale]) SELECT [IdxDossier], [CodScheda], [Vers], [NumScheda], [CodMisura], [Fase], [Operatore], [DataOra], [ValorePar], [ValoreMis], [OkValMis], [CodDeroga], [CodVisib], [Ordinale] FROM [dbo].[Misure]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DROP TABLE [dbo].[Misure]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_rename N'[dbo].[RG_Recovery_1_Misure]', N'Misure', N'OBJECT'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_Misure_1] on [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] ADD CONSTRAINT [PK_Misure_1] PRIMARY KEY CLUSTERED  ([IdxDossier], [CodScheda], [Vers], [NumScheda], [CodMisura], [Fase], [Rev]) ON [PRIMARY]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[v_DFR_all]'
GO
CREATE VIEW [dbo].[v_DFR_all]
AS
SELECT DISTINCT IdxDossier, Fase, Rev
FROM     dbo.Misure
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[v_DFR_last]'
GO

CREATE VIEW [dbo].[v_DFR_last]
AS
-- ultima revisione per ogni fase dei Dossier
SELECT  idxDossier, Fase, MAX(Rev) as Rev
FROM dbo.v_DFR_all
GROUP BY IdxDossier, Fase


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



-- =============================================
-- Author:	 S.E. Locatelli
-- Create date: 2018.02.20
-- Description: Recupera per un dossier elenco fasi/rev attive
-- =============================================
CREATE PROCEDURE [dbo].[stp_DFR_getByD]
(
	@IdxDossier   INT
)
AS


SELECT  *
FROM	   v_dfr_last
WHERE   idxdossier = @IdxDossier


GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Rebuilding [dbo].[ElencoDossier]'
GO
CREATE TABLE [dbo].[RG_Recovery_2_ElencoDossier]
(
[IdxDossier] [int] NOT NULL IDENTITY(1, 1),
[Matricola] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DataCreazione] [datetime] NOT NULL CONSTRAINT [DF_ElencoDossier_DataCreazione] DEFAULT (getdate()),
[UserCreazione] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ElencoDossier_UserCreazione] DEFAULT (''),
[FaseAct] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ElencoDossier_Stato] DEFAULT ('001'),
[RevAct] [int] NOT NULL CONSTRAINT [DF_ElencoDossier_Stato1] DEFAULT ((0)),
[Note] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__ElencoDoss__Note__54968AE5] DEFAULT ('')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
SET IDENTITY_INSERT [dbo].[RG_Recovery_2_ElencoDossier] ON
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
INSERT INTO [dbo].[RG_Recovery_2_ElencoDossier]([IdxDossier], [Matricola], [DataCreazione], [UserCreazione], [Note]) SELECT [IdxDossier], [Matricola], ISNULL([DataCreazione], (getdate())), ISNULL([UserCreazione], ('')), ISNULL([Note], ('')) FROM [dbo].[ElencoDossier]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
SET IDENTITY_INSERT [dbo].[RG_Recovery_2_ElencoDossier] OFF
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[ElencoDossier]')
IF @idVal IS NOT NULL
    DBCC CHECKIDENT(N'[dbo].[RG_Recovery_2_ElencoDossier]', RESEED, @idVal)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DROP TABLE [dbo].[ElencoDossier]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_rename N'[dbo].[RG_Recovery_2_ElencoDossier]', N'ElencoDossier', N'OBJECT'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_ElencoDossier] on [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] ADD CONSTRAINT [PK_ElencoDossier] PRIMARY KEY CLUSTERED  ([IdxDossier]) ON [PRIMARY]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[v_ElencoDossier]'
GO


ALTER VIEW [dbo].[v_ElencoDossier]
AS

WITH cteNumSchede AS
(
    SELECT IdxDossier, COUNT(*) as Num
    FROM SchemaCollMacc
    GROUP BY IdxDossier
)
, cteAnagSchede AS
(
    SELECT scm.IdxDossier, COUNT(ds.CodMisura) as Num
    FROM SchemaCollMacc scm
        LEFT OUTER JOIN DettScheda ds ON scm.CodScheda = ds.CodScheda AND scm.Vers = ds.Vers
    GROUP BY scm.IdxDossier
)
, cteMisure AS
(
    -- conteggio misure x Dossier solo per revisione attiva x ogni fase
    SELECT mi.IdxDossier, COUNT(*) AS Num
    FROM dbo.Misure  AS mi
    INNER JOIN dbo.v_DFR_last AS dfr       -- vista ultima rev. per ogni fase
      ON mi.IdxDossier = dfr.idxDossier AND mi.Fase = dfr.Fase AND mi.Rev = dfr.Rev
    GROUP BY mi.IdxDossier
)

SELECT ed.IdxDossier
	 , ed.Matricola
      , em.Descrizione + ' - ' + em.Cliente as DescrMacchina
	 , ed.DataCreazione
	 , ed.UserCreazione
	 , ISNULL(scm.num, 0) AS NumSchede
	 , ISNULL(cas.num, 0) AS NumMisReq
	 , ISNULL(mis.num, 0)  AS NumMisEff
	 , ed.FaseAct
	 , ed.RevAct
	 , ISNULL(ed.Note, '') AS Note
FROM dbo.ElencoDossier ed
    INNER JOIN ElencoMacchineCN em ON ed.Matricola = em.Matricola
    LEFT OUTER JOIN cteNumSchede scm ON ed.IdxDossier = scm.IdxDossier
    LEFT OUTER JOIN cteAnagSchede cas ON ed.IdxDossier = cas.IdxDossier
    LEFT OUTER JOIN cteMisure mis ON ed.IdxDossier = mis.IdxDossier


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

ALTER VIEW [dbo].[v_selElencoDossier]
AS
SELECT        IdxDossier AS value, Matricola + ' - ' + DescrMacchina AS label, FaseAct AS conditio
FROM            dbo.v_ElencoDossier

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



-- =============================================
-- Author:	 S.E. Locatelli
-- Create date: 2018.02.20
-- Description: Recupera per un dossier+fase elenco rev attiva
-- =============================================
CREATE PROCEDURE [dbo].[stp_DFR_getByDF]
(
	 @IdxDossier   INT
	,@Fase		NVARCHAR(50)
)
AS


SELECT  *
FROM	   v_dfr_last
WHERE   idxdossier = @IdxDossier
    AND Fase = @Fase


GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Refreshing [dbo].[v_selSchedaIst]'
GO
EXEC sp_refreshview N'[dbo].[v_selSchedaIst]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_VSS_getByConditio]'
GO
ALTER PROCEDURE [dbo].[stp_VSS_getByConditio]
(
	@conditio int
)
AS
SET NOCOUNT ON;

SELECT '0.0' AS value, '--Selezionare Scheda--' as label, 0 as Ordinale
UNION
SELECT      value, label, Ordinale
FROM        v_selSchedaIst 
WHERE       (conditio = @conditio)
ORDER BY	  Ordinale
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Refreshing [dbo].[v_ElencoMacchine]'
GO
EXEC sp_refreshview N'[dbo].[v_ElencoMacchine]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[f_getStringLang]'
GO

-- ================================================================
-- Author     : Steamware
-- Create date: 2018-02-22
--
-- Description:	Data una Lingua estraggo la parte tra i TAG di inizio e fine richiesti
--
-- esempio    : 
--      SELECT dbo.[f_getStingLang]( '[EN]Good Day[/EN][IT]Buon Giorno[/IT]','IT')
--
--      SELECT IDX, dbo.[f_getStingLang]( Descrizione, 'IT') FROM Table
--
-- 
-- ================================================================
CREATE FUNCTION [dbo].[f_getStringLang]
( 
    @String NVARCHAR(MAX),
    @Lang CHAR(2)   = 'IT',
    @ifBlank AS BIT = 0       -- Se non trovo Tag iniziale se 0 -> blank   1 -> intera stringa
)
RETURNS NVARCHAR(4000)
AS
BEGIN

    -- DECLARE @String AS NVARCHAR(MAX) = '[EN]Good Day[/EN][IT]Buon Giorno[/IT]'
    -- DECLARE @Lang AS NVARCHAR(2) = 'IT' , 

    -- creo i tag di ricerca Inizio e Fine
    DECLARE @SearchIni AS NVARCHAR(20) = '[' + @Lang + ']'    -- es. [EN]
    DECLARE @SearchEnd AS NVARCHAR(20) = '[/' + @Lang + ']'    -- es. [/EN]

    DECLARE @LenIni AS INT = DATALENGTH(@SearchIni)/2
    DECLARE @LenEnd AS INT = DATALENGTH(@SearchEnd)/2

    DECLARE @StringOut AS NVARCHAR(MAX) = ''

    -- SELECT @String, CHARINDEX(@SearchIni, @String) AS Inizio, CHARINDEX(@SearchEnd, @String, CHARINDEX(@SearchIni, @String)) + @LenEnd AS Fine

    -- estraggo stringa tra i TAG ( expression, start, length )
    SET @StringOut = SUBSTRING  ( @String, 
                                ( CHARINDEX( @SearchIni, @String )),                -- posizione TAG Iniziale
                                ( CASE ( CHARINDEX( @SearchIni, @String )) WHEN 0   -- se non trovo TAG iniziale ritorno Stringa vuota
                                    THEN 0
                                    ELSE CHARINDEX( @SearchEnd, @String, ( CHARINDEX(@SearchIni, @String)) ) + @LenEnd - ( CHARINDEX( @SearchIni, @String )) 
                                  END
                                ) )

    -- se vuota passo Blank o l'intera stringa
    IF (@StringOut = '' AND @ifBlank = 1)
        SET @StringOut = @String
    ELSE
        -- tolgo anche tag di ricerca
        SET @StringOut = REPLACE(REPLACE( @StringOut, @SearchEnd,''), @SearchIni,'')
    
    RETURN @StringOut

END

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

-- ================================================================
-- Author     : Steamware
-- Create date: 2018-02-22
--
-- Description: Estrae in forma tabella una o più lingue dalla stringa passata
--
-- esempio    :  
-- 
-- SELECT [IdxDossier]
--       ,[CodScheda]
--       ,[Vers]
--       ,ln.*
-- FROM dbo.SchemaCollMacc
-- CROSS APPLY f_getStingLangInLine( [Descrizione] ,'IT,EN') AS ln
--
-- ================================================================
CREATE FUNCTION [dbo].[f_getStingLangInLine]
( 
    @String NVARCHAR(MAX), @Languages VARCHAR(20)= 'IT,EN'
)
  RETURNS @values TABLE ( Lang VARCHAR(20), StringOut NVARCHAR(4000))
AS
BEGIN
 
    INSERT @values
    SELECT [param] AS Lang 
           ,dbo.[f_getStringLang] ( @String, [param], 0 ) AS StringOut
    FROM [dbo].[fn_MVParam] (@Languages, ',')
    WHERE dbo.[f_getStringLang] ( @String, [param], 0 ) <> ''

--    DELETE FROM @values WHERE ISNULL(StringOut,'') = ''

RETURN

END


GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Refreshing [dbo].[v_selFasi]'
GO
EXEC sp_refreshview N'[dbo].[v_selFasi]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_Mis_getNum2Ins]'
GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.17
-- Description:	conta recorda da inserire
-- =============================================
ALTER PROCEDURE [dbo].[stp_Mis_getNum2Ins]
(
     @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
        
-- 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))+'.', '' )

;WITH cteSchema AS
(
SELECT scm.IdxDossier
    , scm.CodScheda
    , scm.Vers
    , scm.NumScheda
    , ds.CodMisura
    , @Fase AS Fase
    , @Rev AS Rev
    , '' as Operatore
    , GETDATE() as DataOra
    , 0 as ValorePar
    , '' as ValoreMis
    , 0 as OkValMis
    , '' as CodDeroga
    , '' as NomeMisura
    , '' as TipoValMisura
    , '' as NomePar
    , 0 as MinPar
    , 0 as MaxPar
    , 0 as LIVal
    , 0 as LSVal
    , '' as CodSchedaVersNum
    , '' as CodVisib

FROM SchemaCollMacc AS scm
INNER JOIN DettScheda AS ds
    ON scm.CodScheda = ds.CodScheda
        AND scm.Vers = ds.Vers
WHERE   scm.IdxDossier  = @IdxDossier
    AND scm.CodScheda   = @CodScheda 
    AND scm.Vers        = @Vers      
    AND scm.NumScheda   = @NumScheda 
)

SELECT  cte.*
FROM	   cteSchema cte
WHERE   CodMisura NOT IN
(
    SELECT  CodMisura
    FROM    Misure m
    WHERE   IdxDossier  = @IdxDossier
        AND CodScheda   = @CodScheda 
        AND Vers        = @Vers      
        AND NumScheda   = @NumScheda 
        AND Fase        = @Fase  
        AND Rev	    = @Rev 
)

RETURN
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[f_getStingLangInLine_2]'
GO

-- ================================================================
-- Author     : Steamware
-- Create date: 2018-02-22
--
-- Description: Estrae in forma tabella una o più lingue dalla stringa passata
--
-- esempio    :  
-- 
-- SELECT [IdxDossier]
--       ,[CodScheda]
--       ,[Vers]
--       ,ln.*
-- FROM dbo.SchemaCollMacc
-- CROSS APPLY f_getStingLangInLine_2( [Descrizione] ,'IT,EN') AS ln
--
-- ================================================================
CREATE FUNCTION [dbo].[f_getStingLangInLine_2]
( 
    @String NVARCHAR(MAX), @Languages VARCHAR(20)   -- es. 'IT,EN'
)
  RETURNS TABLE -- @values TABLE ( Lang VARCHAR(20), StringOut NVARCHAR(4000))
AS

RETURN 
(
   SELECT [param] AS Lang 
          ,dbo.[f_getStringLang] ( @String, [param], 0 ) AS StringOut
   FROM [dbo].[fn_MVParam] (@Languages, ',')
   WHERE dbo.[f_getStringLang] ( @String, [param], 0 ) <> ''
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[stp_VSS_getByLang]'
GO
CREATE PROCEDURE [dbo].[stp_VSS_getByLang]
(
	@lang NVARCHAR(2)
)
AS
SET NOCOUNT ON;

SELECT '0.0' AS value, '--Selezionare Scheda--' as label
UNION
SELECT      CodScheda + '.' + CAST(Vers AS NVARCHAR(50)) AS value, CodScheda + ' - ' + dbo.f_getStringLang(Descrizione, @Lang,1) as label
FROM        AnagSchede
ORDER BY	  Value
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[v_statoMisure]'
GO


ALTER VIEW [dbo].[v_statoMisure]
AS
     SELECT IdxDossier,
            CodScheda,
            Vers,
            NumScheda,
            Fase,
            Rev,
            COUNT(CodMisura) AS TotMisure,
            SUM(CASE
                    WHEN Operatore <> ''
                    THEN 1
                    ELSE 0
                END) AS MisEffettuate,
            SUM(CASE
                    WHEN OkValMis = 1
                    THEN 1
                    ELSE 0
                END) AS MisOk,
            SUM(CASE
                    WHEN OkValMis = 1
                         OR CodDeroga <> ''
                    THEN 1
                    ELSE 0
                END) AS MisOkDerog
     FROM dbo.Misure
     GROUP BY IdxDossier,
              CodScheda,
              Vers,
              NumScheda,
              Fase,
              Rev;


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

-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.17
-- Description:	inserisce record missing (da inserire)
-- =============================================
ALTER PROCEDURE [dbo].[stp_Mis_insMissing]
(
     @IdxDossier	    INT
    ,@CodSchedaVersNum  NVARCHAR(100) -- formato CodScheda.Vers.Num --> da splittare
    ,@Fase		    NVARCHAR(50)
    ,@Rev			    INT
)
AS

DECLARE @CodScheda		NVARCHAR(50)	= '',
	   @CodSchedaVers	NVARCHAR(50)	= '',
        @Vers			INT			= 0,
        @NumScheda		INT			= 0
        
-- splitto e recupero dati...
SELECT  @CodSchedaVers	= @CodSchedaVersNum
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))+'.', '' )

;WITH cteSchema AS
(
    SELECT scm.IdxDossier
	   , scm.CodScheda
	   , scm.Vers
	   , scm.NumScheda
	   , ds.CodMisura
	   , @Fase AS Fase
	   , @Rev AS Rev
	   , CASE WHEN ds.TipoValMisura='FILE' THEN CAST(@IdxDossier AS NVARCHAR(50)) + '.' + @CodSchedaVersNum + '.' + ds.CodMisura + '.' + @Fase ELSE ISNULL(ds.ValMisuraDef,'') END AS ValoreMis
    --	, '' as Operatore
    --	, '' as DataOra
    --	, 0 as ValorePar
    --	, '' as ValoreMis
    --	, 0 as OkValMis
    --	, '' as CodDeroga
        , scm.Ordinale

    FROM SchemaCollMacc AS scm
    INNER JOIN DettScheda AS ds
	   ON scm.CodScheda = ds.CodScheda
		  AND scm.Vers = ds.Vers
    WHERE   scm.IdxDossier  = @IdxDossier
	   AND scm.CodScheda   = @CodScheda 
	   AND scm.Vers        = @Vers      
	   AND scm.NumScheda   = @NumScheda 
)

INSERT INTO Misure (IdxDossier, CodScheda, Vers, NumScheda, CodMisura, Fase, Rev, ValoreMis, Ordinale)
SELECT cte.*
FROM cteSchema cte
WHERE   CodMisura NOT IN
(
    SELECT  CodMisura
    FROM    Misure m
    WHERE   IdxDossier  = @IdxDossier
        AND CodScheda   = @CodScheda 
        AND Vers        = @Vers      
        AND NumScheda   = @NumScheda 
        AND Fase        = @Fase 
        AND Rev	    = @Rev 
)


RETURN
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, vv.MinPar, vv.MaxPar, vv.LIVal, vv.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
PRINT N'Altering [dbo].[v_statoCollaudi]'
GO

ALTER VIEW [dbo].[v_statoCollaudi]
AS
     SELECT scm.IdxDossier,
            scm.CodScheda,
            scm.Vers,
            scm.NumScheda,
            scm.Descrizione,
            scm.opz,
            scm.FasiEnab,
            scm.Ordinale,
            sm.Fase,
            sm.Rev,
            sm.TotMisure,
            sm.MisEffettuate,
            sm.MisOk,
            sm.MisOkDerog,
            dbo.f_calcStato(sm.TotMisure, sm.MisEffettuate, sm.MisOk, sm.MisOkDerog) AS Stato,
            lv.label AS DescrStato
     FROM dbo.SchemaCollMacc AS scm
          INNER JOIN dbo.v_statoMisure AS sm ON scm.IdxDossier = sm.IdxDossier
                                                AND scm.CodScheda = sm.CodScheda
                                                AND scm.Vers = sm.Vers
                                                AND scm.NumScheda = sm.NumScheda
          LEFT OUTER JOIN dbo.ListValues AS lv ON dbo.f_calcStato(sm.TotMisure, sm.MisEffettuate, sm.MisOk, sm.MisOkDerog) = lv.value
     WHERE(lv.TableName = N'v_statoCollaudi')
          AND (lv.FieldName = N'Stato');

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

-- =============================================
-- Author:	 S.E.L.
-- Create date: 2015.12.01
-- Description: Stato (riassuntivo) delel schede collaudo
-- =============================================
ALTER PROCEDURE [dbo].[stp_StatoCollByDossier]
(
     @idxDossier	INT = 0    
    ,@Fase		NVARCHAR(50)
    ,@Rev			INT = 0
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    -- SELEZIONO!
    SELECT  * 
    FROM	  v_statoCollaudi
    WHERE	  idxDossier  = @idxDossier
	   AND Fase	    = CASE WHEN @Fase <> '' THEN @Fase ELSE Fase END
	   AND Rev	    = CASE WHEN @Rev >= 0 THEN @Rev ELSE Rev END
    ORDER BY Ordinale

END

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

-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.18
-- Description:	update misura (ede eventualmente parametro) con VERIFICA OK
-- =============================================
ALTER PROCEDURE [dbo].[stp_Mis_updateMis]
(
     @Original_IdxDossier   INT
    ,@Original_CodScheda	   NVARCHAR(50)
    ,@Original_Vers		   INT
    ,@Original_NumScheda	   INT
    ,@Original_CodMisura	   NVARCHAR(50)
    ,@Original_Fase		   NVARCHAR(50)
    ,@Original_Rev		   INT
    ,@Operatore		   NVARCHAR(50)
    ,@ValorePar		   FLOAT
    ,@ValoreMis		   NVARCHAR(4000)
    ,@CodVisib			   NVARCHAR(50)
    ,@CodDeroga		   NVARCHAR(50)
)
AS


UPDATE	Misure
SET		Operatore	= CASE WHEN @ValoreMis <> '' THEN @Operatore ELSE '' END
       ,DataOra		= GETDATE()
       ,ValorePar	= ISNULL(@ValorePar, 0)
       ,ValoreMis	= @ValoreMis
       ,OkValMis	= dbo.f_checkVal(CodScheda, Vers, CodMisura, @ValorePar, @ValoreMis)
       ,CodVisib	= ISNULL(@CodVisib, ISNULL(CodVisib,''))
       ,CodDeroga	= ISNULL(@CodDeroga, ISNULL(CodDeroga,''))
WHERE	IdxDossier	= @Original_IdxDossier
    AND CodScheda	= @Original_CodScheda
    AND Vers		= @Original_Vers
    AND NumScheda	= @Original_NumScheda
    AND CodMisura	= @Original_CodMisura
    AND Fase		= @Original_Fase
    AND Rev		= @Original_Rev

RETURN

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_Mis_Deroga]'
GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.18
-- Description:	update misura (ede eventualmente parametro) con VERIFICA OK
-- =============================================
ALTER PROCEDURE [dbo].[stp_Mis_Deroga]
(
      @Original_IdxDossier	INT
	,@Original_CodScheda	NVARCHAR(50)
	,@Original_Vers		INT
	,@Original_NumScheda	INT
	,@Original_CodMisura	NVARCHAR(50)
	,@Original_Fase		NVARCHAR(50)
	,@Original_Rev			INT
	,@CodDeroga			NVARCHAR(50)
)
AS


UPDATE	  Misure
SET		  CodDeroga	= @CodDeroga
WHERE	  IdxDossier	= @Original_IdxDossier
	AND	  CodScheda	= @Original_CodScheda
	AND	  Vers		= @Original_Vers
	AND	  NumScheda	= @Original_NumScheda
	AND	  CodMisura	= @Original_CodMisura
	AND	  Fase		= @Original_Fase
	AND	  Rev		= @Original_Rev

RETURN
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Refreshing [dbo].[v_misureOrfane]'
GO
EXEC sp_refreshview N'[dbo].[v_misureOrfane]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_DS_updateQuery]'
GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.09
-- Description:	update record
-- =============================================
ALTER PROCEDURE [dbo].[stp_DS_updateQuery]
(
	@CodMisura			NVARCHAR(50),
	@NomeMisura			NVARCHAR(500),
	@TipoValMisura		NVARCHAR(50),
	@NomePar			NVARCHAR(50),
	@CodVisib			NVARCHAR(50),
	@Original_CodScheda NVARCHAR(50),
	@Original_Vers		INT,
	@Original_CodMisura NVARCHAR(50)
)
AS
SET NOCOUNT OFF;

UPDATE dbo.DettScheda
SET CodMisura		= @CodMisura
	,NomeMisura		= ISNULL(@NomeMisura, '')
	,TipoValMisura	= ISNULL(@TipoValMisura, '')
	,NomePar		= ISNULL(@NomePar, '')
	,CodVisib		= ISNULL(@CodVisib, '')
WHERE
	(CodScheda		= @Original_CodScheda)
	AND (Vers		= @Original_Vers)
	AND (CodMisura	= @Original_CodMisura)

SELECT	*
FROM	DettScheda
WHERE	CodMisura	= @CodMisura
	AND CodScheda	= @Original_CodScheda
	AND Vers		= @Original_Vers
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_SCM_insMisFasiMissing]'
GO


-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.17
-- Description:	inserisce record missing (da inserire)
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCM_insMisFasiMissing]
(
     @IdxDossier INT
)
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRAN

    DECLARE @CodSchedaVers  NVARCHAR(100)
    DECLARE @Fase		   NVARCHAR(50)
    DECLARE @Rev		   INT = 0
    DECLARE @TotRighe AS INT;
    DECLARE @Riga AS INT;
    SET @Riga = 1

    -- seleziono TUTTE le schede da SchemaCollMacc
    -- ESPLODO tutte le combinazioni di @IdxDossier - @CodSchedaVers - @Fase - @Rev
    
    -- VEDI RISULTATO DI QUESTA (che non splitta FasiEnab...)
    --SELECT  IdxDossier, CodScheda + '.' + CAST(Vers AS NVARCHAR(50)) + '.' + CAST(NumScheda AS NVARCHAR(50)), FasiEnab -- va splittato per '#', se è '' non serve chiamare
    --FROM	SchemaCollMacc
    --WHERE	IdxDossier = @IdxDossier

    SELECT  IdxDossier, CodScheda + '.' + CAST(Vers AS NVARCHAR(50)) + '.' + CAST(NumScheda AS NVARCHAR(50)) AS CodSchedaVers, FasiEnab -- va splittato per '#', se è '' non serve chiamare
            , fn.param AS Fase, ROW_NUMBER() OVER (ORDER BY idxDossier) AS riga
    INTO #SchedaList
    FROM dbo.SchemaCollMacc
    CROSS APPLY dbo.[fn_MVParam] (FasiEnab,'#') AS fn
    WHERE IdxDossier = @IdxDossier AND ISNULL(FasiEnab,'') <> ''
    
    SET @TotRighe = @@ROWCOUNT
    
    -- CHECK dati
    -- SELECT * FROM #SchedaList

    -- ciclo tra le righe
    WHILE @TotRighe > 0
    BEGIN
	   -- leggo parametri stored
        SELECT @CodSchedaVers = CodSchedaVers, @Fase = Fase
        FROM #SchedaList WHERE Riga = @Riga

	   -- ora recupero REVISIONE LAST x la fase del dossier...
	   SELECT @Rev = Rev FROM v_DFR_last WHERE idxDossier=@IdxDossier AND Fase = @Fase

        -- eseguo stored di popolamento dati
        --SELECT @IdxDossier, @CodSchedaVers, @Fase   -- x Test
        EXEC dbo.stp_Mis_insMissing @IdxDossier, @CodSchedaVers, @Fase, @Rev
        
        -- riga successiva o esco
        IF @Riga = @TotRighe
            SET @TotRighe = 0       -- esco
        ELSE 
            SET @Riga = @Riga+1    -- riga successiva
    END

COMMIT TRAN

RETURN

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_DS_insertNew]'
GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.09
-- Description:	inserisce record per scheda al PRIMO posto disponibile...
-- =============================================
ALTER PROCEDURE [dbo].[stp_DS_insertNew]
(
	@CodSchedaVers	NVARCHAR(100) -- formato CodScheda.Vers --> da splittare
	,@NomeMisura	NVARCHAR(500) = ''
)
AS
SET NOCOUNT ON;

DECLARE @CodScheda	NVARCHAR(50)	= '',
		@Vers		INT				= 0,
		@LastNum	INT				= 0,
		@TipoValM	NVARCHAR(50)	= ''
		
-- splitto e recupero dati...
SELECT	@CodScheda	= SUBSTRING ( @CodSchedaVers, 0, CHARINDEX ( '.', @CodSchedaVers ) )
SELECT	@Vers		= REPLACE	( @CodSchedaVers, @CodScheda+'.', '' )

-- cerco ULTIMO record disponibile...
SELECT	@LastNum	= ISNULL(REPLACE(MAX(CodMisura),'M',''),0) 
FROM	dbo.DettScheda
WHERE	CodScheda	= @CodScheda
	AND	Vers		= @Vers

-- copio altri dati da ultima misura...
SELECT @NomeMisura  = CASE WHEN ISNULL(@NomeMisura,'') <> '' THEN @NomeMisura ELSE NomeMisura END
	  ,@TipoValM	= ISNULL(TipoValMisura, '')
FROM	dbo.DettScheda
WHERE	CodScheda	= @CodScheda
	AND	Vers		= @Vers
	AND	CodMisura	= 'M'+ dbo.f_padLeft( @LastNum, 4, '0')

-- inserisco NUOVO valore al primo posto disponibile...
INSERT INTO DettScheda(CodScheda, Vers, CodMisura, TipoValMisura, NomeMisura, NomePar)
VALUES	(@CodScheda, @Vers, 'M'+ dbo.f_padLeft( @LastNum + 1, 4, '0'), @TipoValM, @NomeMisura, '')
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [voc].[stp_deleteQuery]'
GO


CREATE PROCEDURE [voc].[stp_deleteQuery]
(
	@Original_Lingua nvarchar(3),
	@Original_Lemma nvarchar(50)
)
AS
	SET NOCOUNT OFF;
DELETE FROM [dbo].[Vocabolario] WHERE ([Lingua] = @Original_Lingua) AND ([Lemma] = @Original_Lemma)

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_Mis_getByDossier]'
GO
-- =============================================
-- Author:	 S.E. Locatelli
-- Create date: 2015.09.10
-- Description: elenco record x dossier
-- =============================================
ALTER PROCEDURE [dbo].[stp_Mis_getByDossier]
(
	@IdxDossier int
)
AS
	SET NOCOUNT ON;
SELECT  * 
FROM	   dbo.v_Misure
WHERE   IdxDossier = @IdxDossier
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_SCFM_updateQuery]'
GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.12
-- Description:	update record
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCFM_updateQuery]
(
	 @CodSchedaVers				NVARCHAR(100)	= '' -- formato CodScheda.Vers --> da splittare
	,@CodFam					NVARCHAR(50)	= ''
	,@NumSchede					INT				= 1
	,@opz						NVARCHAR(50)	= ''
	,@Descrizione				NVARCHAR(500)	= ''
	,@Original_CodSchedaVers	NVARCHAR(100)	= '' -- formato CodScheda.Vers --> da splittare
	,@Original_CodFam			NVARCHAR(50)	= ''
)
AS
SET NOCOUNT ON;


DECLARE @CodScheda			NVARCHAR(50)	= '',
		@Vers				INT				= 0,
		@Original_CodScheda	NVARCHAR(50)	= '',
		@Original_Vers		INT				= 0
		
-- splitto e recupero dati...
SELECT	@CodScheda			= SUBSTRING ( @CodSchedaVers, 0, CHARINDEX ( '.', @CodSchedaVers ) )
SELECT	@Vers				= REPLACE	( @CodSchedaVers, @CodScheda+'.', '' )
SELECT	@Original_CodScheda	= SUBSTRING ( @Original_CodSchedaVers, 0, CHARINDEX ( '.', @Original_CodSchedaVers ) )
SELECT	@Original_Vers		= REPLACE	( @Original_CodSchedaVers, @Original_CodScheda+'.', '' )

UPDATE	SchemaCollFamMacc
SET		CodScheda	= @CodScheda
	   ,Vers		= @Vers
	   ,CodFam		= @CodFam
	   ,NumSchede	= @NumSchede
	   ,opz			= @opz
	   ,Descrizione	= @Descrizione

WHERE	CodScheda	= @Original_CodScheda
	AND	Vers		= @Original_Vers
	AND	CodFam		= @Original_CodFam
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_ASC_UpdateQuery]'
GO
-- =============================================
-- Author:		S.E. Locatelli
-- Mod date:	2015.09.11
-- Description: update record
-- =============================================
ALTER PROCEDURE [dbo].[stp_ASC_UpdateQuery]
(
	 @CodScheda			NVARCHAR(50)
	,@Vers				INT
	,@Descrizione			NVARCHAR(500)
	,@Path				NVARCHAR(MAX)
	,@FasiEnab			NVARCHAR(MAX)	= ''
	,@CodVisib			NVARCHAR(50)
	,@Original_CodScheda	NVARCHAR(50)
	,@Original_Vers		INT
)
AS

SET NOCOUNT OFF;

BEGIN tran

    -- aggiorno dett scheda
    UPDATE  dbo.DettScheda
	   SET CodVisib		= @CodVisib
    WHERE	  CodScheda = @Original_CodScheda
	   AND Vers = @Original_Vers;

    -- aggiorno schede
    UPDATE  dbo.AnagSchede
	   SET CodScheda		= @CodScheda,
		  Vers			= @Vers,
		  Descrizione	= @Descrizione,
		  FasiEnab		= @FasiEnab,
		  CodVisib		= @CodVisib,
		  [Path]		= @Path
    WHERE	  CodScheda = @Original_CodScheda
	   AND Vers = @Original_Vers;


    SELECT  CodScheda, Vers, Descrizione, Path
    FROM	  AnagSchede
    WHERE	  CodScheda = @CodScheda 
	   AND Vers = @Original_Vers;

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


-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.09.10
-- Description:	aggiorna note dossier
-- =============================================
ALTER PROCEDURE [dbo].[stp_ED_updateNote]
(
	@Original_IdxDossier	INT
	,@FaseAct				NVARCHAR(50)
	,@Note				NVARCHAR(MAX)
)
AS
SET NOCOUNT OFF;

UPDATE	ElencoDossier
SET		Note	= @Note
	  ,  FaseAct	= @FaseAct
WHERE	IdxDossier = @Original_IdxDossier

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_Mis_RemSetMis]'
GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.09.11
-- Description:	elimina record misure!
-- =============================================
ALTER PROCEDURE [dbo].[stp_Mis_RemSetMis]
(
     @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
        
-- 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))+'.', '' )

DELETE 
FROM	Misure
WHERE   IdxDossier  = @IdxDossier
        AND CodScheda   = @CodScheda 
        AND Vers        = @Vers      
        AND NumScheda   = @NumScheda 
        AND Fase        = @Fase
        AND Rev	    = @Rev


RETURN
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[stp_SCM_updateQuery]'
GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.09.11
-- Description:	update record
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCM_updateQuery]
(
	 @Descrizione			NVARCHAR(500)	= ''
	,@opz				NVARCHAR(50)	= ''
	,@FasiEnab			NVARCHAR(MAX)	= ''
	,@Original_IdxDossier	INT
	,@Original_CodScheda	NVARCHAR(50)	= ''
	,@Original_Vers		INT			= 0
	,@Original_NumScheda	INT			= 0
)
AS
SET NOCOUNT ON;

UPDATE  SchemaCollMacc
SET	   Descrizione	= @Descrizione
	   ,opz		= @opz
	   ,FasiEnab	= @FasiEnab
WHERE	CodScheda	= @Original_CodScheda
	AND Vers		= @Original_Vers
	AND IdxDossier	= @Original_IdxDossier
	AND NumScheda	= @Original_NumScheda
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[NoteDossier]'
GO
CREATE TABLE [dbo].[NoteDossier]
(
[IdxDossier] [int] NOT NULL,
[DataMod] [datetime] NOT NULL CONSTRAINT [DF_DossierHist_DtFreeze] DEFAULT (getdate()),
[UserMod] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_NoteDossier_UserCreazione] DEFAULT (''),
[Fase] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Table_1_Stato] DEFAULT ('001'),
[Rev] [int] NOT NULL CONSTRAINT [DF_Table_1_Rev] DEFAULT ((0)),
[Note] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_DossierHist_Note] DEFAULT ('')
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_DossierHist] on [dbo].[NoteDossier]'
GO
ALTER TABLE [dbo].[NoteDossier] ADD CONSTRAINT [PK_DossierHist] PRIMARY KEY CLUSTERED  ([IdxDossier], [DataMod], [UserMod]) ON [PRIMARY]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[stp_ED_CreateRev]'
GO


-- =============================================
-- Author:	 S.E. Locatelli
-- Create date: 2018.02.20
-- Description: REGISTRA la NUOVA rev attiva e duplica il set delle misure...
-- =============================================
CREATE PROCEDURE [dbo].[stp_ED_CreateRev]
(
	@Original_IdxDossier   INT
	,@User			   NVARCHAR(50)
	,@Note			   NVARCHAR(MAX)
)
AS
SET NOCOUNT OFF;
SET XACT_ABORT ON;

DECLARE @FaseAct nvarchar(50) = ''
DECLARE @RevAct INT = 0

BEGIN tran

    -- seleziono ultima revisione
    SELECT  @RevAct = RevAct, @FaseAct = FaseAct 
    FROM	  ElencoDossier 
    WHERE	  IdxDossier = @Original_IdxDossier

    -- in primis registro UNA NOTA EXTRA
    INSERT INTO NoteDossier(IdxDossier, DataMod, UserMod, Fase, Rev, Note)
    SELECT  IdxDossier, GETDATE() AS DataMod, @User AS UserMod, FaseAct AS Fase, RevAct AS Rev, '[NEW REVISION] ' + @Note AS Note
    FROM	  ElencoDossier
    WHERE	  IdxDossier = @Original_IdxDossier


    -- ora duplico TUTTE le misure della fase attuale
    INSERT INTO Misure(IdxDossier, CodScheda, Vers, NumScheda, CodMisura, Fase, Rev, Operatore, DataOra, ValorePar, ValoreMis, OkValMis, CodDeroga, CodVisib, Ordinale)
    SELECT  IdxDossier,
		  CodScheda,
		  Vers,
		  NumScheda,
		  CodMisura,
		  Fase,
		  Rev + 1 AS Rev,
		  Operatore,
		  DataOra,
		  ValorePar,
		  ValoreMis,
		  OkValMis,
		  CodDeroga,
		  CodVisib,
		  Ordinale
    FROM	  Misure
    WHERE	  IdxDossier  = @Original_IdxDossier
	   AND Rev	    = @RevAct
	   AND Fase	    = @FaseAct


    
    -- aggiorno quindi le note e la NUOVA revisione x fase
    UPDATE  ElencoDossier
    SET	  Note	    = @Note
		 ,RevAct	    = RevAct + 1 
    WHERE	  IdxDossier  = @Original_IdxDossier

COMMIT tran


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

-- =============================================
-- Author:	 S.E. Locatelli
-- Create date: 2018.02.20
-- Description: ELENCO Fasi attive per un dato dossier
-- =============================================
CREATE PROCEDURE [dbo].[stp_VSF_getByDossier]
(
	@IdxDossier   INT
)
AS
SET NOCOUNT ON;

SELECT  vsf.value as value, vsf.label AS label 
FROM	   dbo.v_selFasi vsf
    INNER JOIN v_DFR_last vl ON vsf.value = vl.Fase
WHERE   vl.idxDossier = @idxDossier

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



CREATE VIEW [dbo].[v_selRev]
AS
     SELECT DISTINCT
            Rev AS value,
            Rev AS label,
            CAST(idxDossier AS NVARCHAR(50))+'.'+Fase AS conditio
     FROM dbo.v_DFR_all;



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



-- =============================================
-- Author:	 S.E. Locatelli
-- Create date: 2018.02.20
-- Description: ELENCO Revisioni attive per un dato dossier + Fase
-- =============================================
CREATE PROCEDURE [dbo].[stp_VSR_getByDF]
(
	 @IdxDossier   INT
	,@Fase		NVARCHAR(50)
)
AS
SET NOCOUNT ON;

SELECT  vl.value, vl.label, vl.conditio 
FROM	   v_selRev vl 
WHERE   vl.conditio = CAST(@idxDossier as NVARCHAR(50)) + '.' + @Fase
ORDER BY	  vl.value DESC


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



CREATE VIEW [dbo].[v_ElencoDossier_ALL]
AS

-- come la v_ElencoDossier però ritorna il conteggio di tutte le revisioni nelle misure 
-- e non solo l'ultima
WITH cteNumSchede AS
(
    SELECT IdxDossier, COUNT(*) as Num
    FROM SchemaCollMacc
    GROUP BY IdxDossier
)
, cteAnagSchede AS
(
    SELECT scm.IdxDossier, COUNT(ds.CodMisura) as Num
    FROM SchemaCollMacc scm
        LEFT OUTER JOIN DettScheda ds ON scm.CodScheda = ds.CodScheda AND scm.Vers = ds.Vers
    GROUP BY scm.IdxDossier
)
, cteMisure AS
(
    SELECT IdxDossier, COUNT(*) as Num
    FROM Misure 
    GROUP BY IdxDossier
)

SELECT ed.IdxDossier
	 , ed.Matricola
      , em.Descrizione + ' - ' + em.Cliente as DescrMacchina
	 , ed.DataCreazione
	 , ed.UserCreazione
	 , ISNULL(scm.num, 0) AS NumSchede
	 , ISNULL(cas.num, 0) AS NumMisReq
	 , ISNULL(mis.num, 0)  AS NumMisEff
	 , ed.FaseAct
	 , ed.RevAct
	 , ISNULL(ed.Note, '') AS Note
FROM dbo.ElencoDossier ed
    INNER JOIN ElencoMacchineCN em ON ed.Matricola = em.Matricola
    LEFT OUTER JOIN cteNumSchede scm ON ed.IdxDossier = scm.IdxDossier
    LEFT OUTER JOIN cteAnagSchede cas ON ed.IdxDossier = cas.IdxDossier
    LEFT OUTER JOIN cteMisure mis ON ed.IdxDossier = mis.IdxDossier

GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding foreign keys to [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] ADD CONSTRAINT [FK_ElencoDossier_ElencoMacchineCN] FOREIGN KEY ([Matricola]) REFERENCES [dbo].[ElencoMacchineCN] ([Matricola])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding foreign keys to [dbo].[SchemaCollMacc]'
GO
ALTER TABLE [dbo].[SchemaCollMacc] ADD CONSTRAINT [FK_SchemaCollMacc_ElencoDossier] FOREIGN KEY ([IdxDossier]) REFERENCES [dbo].[ElencoDossier] ([IdxDossier]) ON UPDATE CASCADE
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding foreign keys to [dbo].[Misure]'
GO
ALTER TABLE [dbo].[Misure] ADD CONSTRAINT [FK_IstanzeMisura_AnagFasi] FOREIGN KEY ([Fase]) REFERENCES [dbo].[AnagFasi] ([Fase]) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Misure] ADD CONSTRAINT [FK_Misure_SchemaCollMacc] FOREIGN KEY ([IdxDossier], [CodScheda], [Vers], [NumScheda]) REFERENCES [dbo].[SchemaCollMacc] ([IdxDossier], [CodScheda], [Vers], [NumScheda]) ON UPDATE CASCADE
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering extended properties'
GO
EXEC sp_updateextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[41] 4[20] 2[13] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "mi"
            Begin Extent = 
               Top = 15
               Left = 113
               Bottom = 426
               Right = 283
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ds"
            Begin Extent = 
               Top = 12
               Left = 990
               Bottom = 278
               Right = 1160
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "vv"
            Begin Extent = 
               Top = 196
               Left = 484
               Bottom = 434
               Right = 654
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 23
         Width = 284
         Width = 1500
         Width = 2628
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 3372
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1176
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1356
         SortOrder = 1416
         GroupBy = 1350
         Filter = 1356
         Or = 1350
   ', 'SCHEMA', N'dbo', 'VIEW', N'v_Misure', NULL, NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'MS_Description', N'stato avanzamento = FASE ATTUALE della scheda...', 'SCHEMA', N'dbo', 'TABLE', N'ElencoDossier', 'COLUMN', N'FaseAct'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'note sul dossier', 'SCHEMA', N'dbo', 'TABLE', N'ElencoDossier', 'COLUMN', N'Note'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'Revisione incrementale (ULTIMA) dato lo stato', 'SCHEMA', N'dbo', 'TABLE', N'ElencoDossier', 'COLUMN', N'RevAct'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'valore dell''eventuale deroga', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'CodDeroga'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'Codice visibilità tra FCS (uno o tutti in ogni combinazione) per Full Client Short', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'CodVisib'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'esito del controllo di validità ove definito', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'OkValMis'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'valore ordinamento default', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'Ordinale'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'Revisione incrementale dello stato (solo se ultima editing permesso)', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'Rev'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'valore della misura effettuata (NON ancora convertito per TIPO)', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'ValoreMis'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'Valore del parametro (opzionale) di validazione', 'SCHEMA', N'dbo', 'TABLE', N'Misure', 'COLUMN', N'ValorePar'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'stato avanzamento = FASE ATTUALE della scheda...', 'SCHEMA', N'dbo', 'TABLE', N'NoteDossier', 'COLUMN', N'Fase'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'note sul dossier', 'SCHEMA', N'dbo', 'TABLE', N'NoteDossier', 'COLUMN', N'Note'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_Description', N'Revisione incrementale (ULTIMA) dato lo stato', 'SCHEMA', N'dbo', 'TABLE', N'NoteDossier', 'COLUMN', N'Rev'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Misure"
            Begin Extent = 
               Top = 7
               Left = 48
               Bottom = 303
               Right = 242
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', N'dbo', 'VIEW', N'v_DFR_all', NULL, NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @xp int
SELECT @xp=1
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', @xp, 'SCHEMA', N'dbo', 'VIEW', N'v_DFR_all', NULL, NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Misure"
            Begin Extent = 
               Top = 7
               Left = 48
               Bottom = 170
               Right = 242
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', N'dbo', 'VIEW', N'v_DFR_last', NULL, NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @xp int
SELECT @xp=1
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', @xp, 'SCHEMA', N'dbo', 'VIEW', N'v_DFR_last', NULL, NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "v_DFR_all"
            Begin Extent = 
               Top = 7
               Left = 48
               Bottom = 148
               Right = 242
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
         Width = 1200
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', N'dbo', 'VIEW', N'v_selRev', NULL, NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @xp int
SELECT @xp=1
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', @xp, 'SCHEMA', N'dbo', 'VIEW', N'v_selRev', NULL, NULL
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


-- BIS sono solo Contrain

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'Dropping constraints from [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] DROP CONSTRAINT [DF_ElencoDossier_Stato]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] DROP CONSTRAINT [DF_ElencoDossier_Stato1]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] DROP CONSTRAINT [DF__ElencoDoss__Note__54968AE5]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding constraints to [dbo].[ElencoDossier]'
GO
ALTER TABLE [dbo].[ElencoDossier] ADD CONSTRAINT [DF_ElencoDossier_FaseAct] DEFAULT ('001') FOR [FaseAct]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ElencoDossier] ADD CONSTRAINT [DF_ElencoDossier_RevAct] DEFAULT ((0)) FOR [RevAct]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ElencoDossier] ADD CONSTRAINT [DF__ElencoDossier_Note] DEFAULT ('') FOR [Note]
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