/*
Deployment script for CMS-SC

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "CMS-SC"
:setvar DefaultFilePrefix "CMS-SC"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
PRINT N'Altering [dbo].[f_checkVal]...';


GO

-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.10.20
-- Description:	update misura (ede eventualmente parametro) con VERIFICA OK
-- =============================================
ALTER FUNCTION [dbo].[f_checkVal]
(
     @CodScheda	NVARCHAR(50)
    ,@Vers		INT
    ,@CodMisura	NVARCHAR(50)
    ,@ValorePar	FLOAT
    ,@ValoreMis	NVARCHAR(4000)
)
RETURNS BIT
AS
    BEGIN
        DECLARE	@answ           BIT             = 0
               ,@ValFlt         FLOAT           = 0
               ,@TipoValMisura  NVARCHAR(50)    = ''
               ,@NumVal         INT             = 0

        DECLARE @delim          AS VARCHAR(2)
        DECLARE @chrind         INT = 1

        -- recupero num righe x validazione parametro
        SELECT  @NumVal     = COUNT(*)
        FROM    ValidVal
        WHERE   CodScheda   = @CodScheda
            AND Vers        = @Vers 
            AND CodMisura   = @CodMisura 

        -- check 1: DEVE ESSERCI ALMENO un valore di validazione da controllare
        IF(@NumVal > 0)
            BEGIN
                -- recupero tipo val misura
                SELECT  @TipoValMisura = ISNULL(TipoValMisura,'')
                FROM    DettScheda
                WHERE   CodScheda   = @CodScheda
                    AND Vers        = @Vers 
                    AND CodMisura   = @CodMisura 
            
                -- check 2: tipo di valore, se float/int faccio cast e test...
                IF (@TipoValMisura = 'INT' OR @TipoValMisura = 'FLT')
                    BEGIN

                        IF LTRIM(RTRIM(ISNULL(@ValoreMis,''))) <> ''
                            BEGIN
                                -- recupero valore misura come FLOAT
                                SELECT @ValFlt = ISNULL(CAST(REPLACE(@ValoreMis,',','.') AS FLOAT), -999999)

                                IF @ValFlt > -999999
                                    BEGIN
                                        SELECT  @answ = CASE WHEN @ValFlt BETWEEN LIVal AND LSVal THEN 1 ELSE 0 END
                                        FROM    ValidVal
                                        WHERE   CodScheda   =  @CodScheda
                                            AND Vers        =  @Vers 
                                            AND CodMisura   =  @CodMisura 
                                            AND MinPar      <= @ValorePar 
                                            AND MaxPar      >  @ValorePar 
                                    END
                            END                        
                    END

                -- check 2: se è boolean è test DIRETTO
                ELSE IF (@TipoValMisura = 'BOL')
                    BEGIN
                        SELECT  @answ = ISNULL(CAST(@ValoreMis AS BIT), 0)
                    END

                -- check 3: se è AFLT è test per tutti i valori passati
                ELSE IF (@TipoValMisura = 'AFLT')
                    BEGIN
                        SET @answ = 0
                        -- verifico il formato del delimitatore passato se / o spazio                   
                        IF CHARINDEX('/', @ValoreMis) > 0
                            BEGIN
                                SET @ValoreMis = REPLACE(@ValoreMis,' ','')
                                SET @delim = '/'
                            END
                        ELSE
                            IF CHARINDEX(' ', @ValoreMis) > 0
                             BEGIN
                                SET @ValoreMis = REPLACE(@ValoreMis,'  ',' ')    -- se ci sono spazi doppi li riduco cmq a 1
                                SET @delim = ' '
                             END
                            ELSE
                               SET @delim = '' -- solo un valore

                        SET @ValoreMis  = RTRIM(LTRIM(REPLACE(@ValoreMis,',','.')))

                        WHILE @chrind > 0 AND ISNULL(@ValoreMis,'') <> '' 
                            BEGIN

                                SELECT @chrind = CHARINDEX(@delim, @ValoreMis)

                                IF @chrind > 0
                                    SET @ValFlt = CAST(LEFT(@ValoreMis, @chrind - 1) AS FLOAT)
                                ELSE
                                    SELECT @ValFlt = CAST(@ValoreMis AS FLOAT)

                                SELECT  @answ = CASE WHEN @ValFlt BETWEEN LIVal AND LSVal THEN 1 ELSE 0 END
                                FROM    ValidVal
                                WHERE   CodScheda   =  @CodScheda
                                    AND Vers        =  @Vers 
                                    AND CodMisura   =  @CodMisura
                                    AND MinPar      <= @ValorePar
                                    AND MaxPar      >  @ValorePar
 
                                SELECT @ValoreMis = RIGHT(@ValoreMis, LEN(@ValoreMis) - @chrind)

                                IF LEN(@ValoreMis) = 0 OR @answ = 0  -- se anche solo un valore KO esco con @answ = 0
                                    BREAK
                            END				
                    END
        END
        ELSE
            BEGIN
                -- ...(altrimenti è OK per "partito preso" SE HO un valore e KO se non ce l'ho...)
                SELECT  @answ = CASE WHEN @ValoreMis <> '' THEN 1 ELSE 0 END
            END

        RETURN @answ
    END
GO
PRINT N'Altering [dbo].[v_ElencoFamMacc]...';


GO
ALTER VIEW dbo.[v_ElencoFamMacc]
AS
SELECT        dbo.ElencoFamMacc.CodFam, dbo.ElencoFamMacc.DescrFam, COUNT(dbo.SchemaCollFamMacc.CodScheda) AS numRowChild
FROM            dbo.ElencoFamMacc LEFT OUTER JOIN
                         dbo.SchemaCollFamMacc ON dbo.ElencoFamMacc.CodFam = dbo.SchemaCollFamMacc.CodFam
GROUP BY dbo.ElencoFamMacc.CodFam, dbo.ElencoFamMacc.DescrFam
GO
PRINT N'Altering [dbo].[v_ElencoMacchine]...';


GO
ALTER VIEW dbo.v_ElencoMacchine
AS
SELECT        dbo.ElencoMacchineCN.Matricola, dbo.ElencoMacchineCN.Descrizione, dbo.ElencoMacchineCN.Cliente, dbo.ElencoMacchineCN.CodFam, dbo.ElencoMacchineCN.CorsaX, dbo.ElencoMacchineCN.CorsaY, 
                         dbo.ElencoMacchineCN.CorsaZ, ISNULL(dbo.ElencoDossier.IdxDossier, 0) AS IdxDossier
FROM            dbo.ElencoMacchineCN LEFT OUTER JOIN
                         dbo.ElencoDossier ON dbo.ElencoMacchineCN.Matricola = dbo.ElencoDossier.Matricola
GO
PRINT N'Altering [dbo].[stp_AFM_deleteQuery]...';


GO

ALTER PROCEDURE [dbo].[stp_AFM_deleteQuery] (@Original_CodFam NVARCHAR(50))
AS
SET NOCOUNT OFF;

BEGIN TRAN

-- cancello eventuale riga tabellone associazione schede/famiglie
DELETE
FROM SchemaCollFamMacc
WHERE ([CodFam] = @Original_CodFam)


-- cancello vera e propria famiglia
DELETE
FROM [dbo].[ElencoFamMacc]
WHERE ([CodFam] = @Original_CodFam)

COMMIT TRAN
GO
PRINT N'Altering [dbo].[stp_AM_deleteQuery]...';


GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.09
-- Description:	delete record
-- =============================================
ALTER PROCEDURE [dbo].[stp_AM_deleteQuery]
(
    @Original_Matricola nvarchar(50)
)
AS

BEGIN tran
    SET NOCOUNT OFF;
    
    DELETE 
    FROM	Tags2Macchine
    WHERE	Matricola = @Original_Matricola

    DELETE 
    FROM	ElencoMacchineCN
    WHERE	Matricola = @Original_Matricola

COMMIT tran
GO
PRINT N'Altering [dbo].[stp_AM_getByMatr]...';


GO
-- =============================================
-- Author:	 S.E. Locatelli
-- Create date: 2015.10.19
-- Description: recupero record da matricola
-- =============================================
ALTER PROCEDURE [dbo].[stp_AM_getByMatr]
(
    @Matricola		NVARCHAR(50)
)
AS
SET NOCOUNT OFF;
    
SELECT  *
FROM	   v_ElencoMacchine
WHERE   Matricola = @Matricola
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)
    ,@Operatore		   NVARCHAR(50)
    ,@ValorePar		   FLOAT
    ,@ValoreMis		   NVARCHAR(4000)
    ,@CodVisib			   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,''))
WHERE	IdxDossier	= @Original_IdxDossier
    AND CodScheda	= @Original_CodScheda
    AND Vers		= @Original_Vers
    AND NumScheda	= @Original_NumScheda
    AND CodMisura	= @Original_CodMisura
    AND Fase		= @Original_Fase

RETURN
GO
PRINT N'Altering [dbo].[stp_SCFM_clonaFamMacc]...';


GO
-- =============================================
-- Author:	 S.E. Locatelli
-- Create date: 2015.10.16
-- Description: clona assegnazioni famiglia
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCFM_clonaFamMacc]
(
     @CodFamFrom	NVARCHAR(50) = '' -- sorgente
    ,@CodFamTo	NVARCHAR(50) = '' -- destinazione
)
AS
BEGIN

    BEGIN tran

    -- calcolo ordinale
    DECLARE @Ordinale	INT			= 0

    SELECT  @Ordinale	= ISNULL(MAX(Ordinale), 0) + 1 
    FROM	  SchemaCollFamMacc
    WHERE   CodFam		= @CodFamTo
    GROUP BY CodFam

    -- merge: se manca inserisco, se c'è faccio + 1 ...
    MERGE SchemaCollFamMacc AS target
       USING (SELECT  CodScheda, Vers, @CodFamTo AS CodFam, NumSchede, Descrizione, opz, Ordinale
             FROM	  SchemaCollFamMacc
             WHERE	  CodFam = @CodFamFrom) AS source (CodScheda, Vers, CodFam, NumSchede, Descrizione, opz, Ordinale)
       ON (target.CodFam = source.CodFam AND target.CodScheda = source.CodScheda AND target.Vers = source.Vers)
       WHEN MATCHED THEN 
          UPDATE SET NumSchede = target.NumSchede + 1
        WHEN NOT MATCHED THEN	
           INSERT (CodScheda, Vers, CodFam, NumSchede, Descrizione, opz, Ordinale)
           VALUES (source.CodScheda, source.Vers, source.CodFam, source.NumSchede, source.Descrizione, source.opz, source.Ordinale + @Ordinale);

       --INSERT INTO SchemaCollFamMacc(CodScheda, Vers, CodFam, NumSchede, Descrizione, opz)
       --SELECT  CodScheda, Vers, @CodFamTo AS CodFam, NumSchede, Descrizione, opz
       --FROM	  SchemaCollFamMacc
       --WHERE	  CodFam = @CodFamFrom


    -- ora effettuo ricalcolo dell'ordinamento per "togliere eventuali buchi"
    ;WITH cteNum AS
    (
       SELECT ROW_NUMBER() OVER(PARTITION BY CodFam ORDER BY  Ordinale, CodScheda, Vers) AS Riga, *
       FROM SchemaCollFamMacc
        WHERE   CodFam		= @CodFamTo
    )

    UPDATE  scfm
    SET	   Ordinale   = cte.Riga
    FROM	   SchemaCollFamMacc scfm
       INNER JOIN cteNum cte ON scfm.CodFam=cte.codFam AND scfm.CodScheda = cte.CodScheda AND scfm.Vers = cte.Vers
    WHERE   scfm.CodFam = @CodFamTo

    COMMIT tran
END
GO
PRINT N'Altering [dbo].[stp_SCFM_deleteQuery]...';


GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.12
-- Description:	delete record
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCFM_deleteQuery]
(
     @Original_CodSchedaVers	NVARCHAR(100) -- formato CodScheda.Vers --> da splittare
    ,@Original_CodFam			NVARCHAR(50)	= ''
)
AS
SET NOCOUNT ON;

BEGIN tran

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

    DELETE
    FROM	SchemaCollFamMacc
    WHERE	CodScheda	= @Original_CodScheda
        AND	Vers		= @Original_Vers
        AND	CodFam		= @Original_CodFam

    -- Riordino!
    ;WITH cteNum AS
    (
       SELECT	 ROW_NUMBER() OVER(PARTITION BY CodFam ORDER BY  Ordinale, CodScheda, Vers) AS Riga, *
       FROM	 SchemaCollFamMacc
       WHERE	 CodFam		= @Original_CodFam
    )

    UPDATE  scfm
    SET	  Ordinale    = cte.Riga
    FROM	  SchemaCollFamMacc scfm
       INNER JOIN cteNum cte ON scfm.CodFam=cte.codFam AND scfm.CodScheda = cte.CodScheda AND scfm.Vers = cte.Vers
    WHERE	  scfm.CodFam = @Original_CodFam

COMMIT tran
GO
PRINT N'Altering [dbo].[stp_SCFM_getByCodSchedaVersFamMacc]...';


GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.11
-- Description:	recupera record da scheda e FamMacc (SE disponibili)
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCFM_getByCodSchedaVersFamMacc]
(
     @CodSchedaVers	NVARCHAR(100)	= '' -- formato CodScheda.Vers --> da splittare
    ,@CodFam		NVARCHAR(50)	= ''
    ,@filtByScheda	BIT
    ,@filtByFamMac	BIT
)
AS
SET NOCOUNT ON;

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

IF ( ISNULL(@CodSchedaVers, '')	<> '')
    BEGIN		
        -- splitto e recupero dati...
        SELECT	@CodScheda	= SUBSTRING ( @CodSchedaVers, 0, CHARINDEX ( '.', @CodSchedaVers ) )
        SELECT	@Vers		= REPLACE	( @CodSchedaVers, @CodScheda+'.', '' )
    END

SELECT	*
FROM	dbo.v_SchemaCollFamMacc
WHERE	CodScheda	= CASE WHEN @filtByScheda	= 1	THEN @CodScheda		ELSE CodScheda	END
        AND	Vers	= CASE WHEN @filtByScheda	= 1	THEN @Vers			ELSE Vers		END
        AND	CodFam	= CASE WHEN @filtByFamMac	= 1	THEN @CodFam		ELSE CodFam		END
ORDER BY Ordinale, CodSchedaVers
GO
PRINT N'Altering [dbo].[stp_SCFM_getByFamMacc]...';


GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.11
-- Description:	recupera record da FamMacc
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCFM_getByFamMacc]
(
    @CodFam nvarchar(50)
)
AS
SET NOCOUNT ON;

SELECT *
FROM dbo.v_SchemaCollFamMacc
WHERE CodFam = @CodFam
ORDER BY Ordinale, CodSchedaVers
GO
PRINT N'Altering [dbo].[stp_SCFM_insertQuery]...';


GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.12
-- Description:	insert record
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCFM_insertQuery]
(
     @CodSchedaVers				NVARCHAR(100) -- formato CodScheda.Vers --> da splittare
    ,@CodFam					NVARCHAR(50)	= ''
)
AS
SET NOCOUNT ON;


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

SELECT  @Ordinale	= ISNULL(MAX(Ordinale), 0) + 1 
FROM	   SchemaCollFamMacc
WHERE   CodFam		= @CodFam
GROUP BY CodFam

-- merge: se manca inserisco, se c'è faccio + 1 ...
MERGE SchemaCollFamMacc AS target
    USING (SELECT  @CodScheda, @Vers, @CodFam, 1, Descrizione, 'X'
            FROM    AnagSchede 
            WHERE   CodScheda   = @CodScheda) AS source (CodScheda, Vers, CodFam, NumSchede, Descrizione, opz)
    ON (target.CodFam = source.CodFam AND target.CodScheda = source.CodScheda AND target.Vers = source.Vers)
    WHEN MATCHED THEN 
        UPDATE SET NumSchede = target.NumSchede + 1
    WHEN NOT MATCHED THEN	
        INSERT (CodScheda, Vers, CodFam, NumSchede, Descrizione, opz, Ordinale)
        VALUES (source.CodScheda, source.Vers, source.CodFam, source.NumSchede, source.Descrizione, source.opz, @Ordinale);
GO
PRINT N'Altering [dbo].[stp_SCM_deleteQuery]...';


GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.16
-- Description:	delete record
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCM_deleteQuery]
(
     @Original_IdxDossier	INT
    ,@Original_CodScheda	NVARCHAR(50)	= ''
    ,@Original_Vers			INT				= 0
    ,@Original_NumScheda	INT				= 0
)
AS
SET NOCOUNT ON;

BEGIN tran
    -- elimino tutte le misure child
    
    DELETE FROM Misure 
    WHERE	CodScheda	= @Original_CodScheda
        AND Vers		= @Original_Vers
        AND IdxDossier	= @Original_IdxDossier
        AND NumScheda	= @Original_NumScheda

    -- elimino la singola riga di collaudo
    DELETE FROM SchemaCollMacc 
    WHERE	CodScheda	= @Original_CodScheda
        AND Vers		= @Original_Vers
        AND IdxDossier	= @Original_IdxDossier
        AND NumScheda	= @Original_NumScheda


    -- RIORDINO sia SCM...    
    ;WITH cteNum AS
    (
       SELECT	 ROW_NUMBER() OVER(PARTITION BY IdxDossier ORDER BY Ordinale, CodScheda, Vers, NumScheda) AS Riga, *
       FROM	 SchemaCollMacc
       WHERE	 IdxDossier	= @Original_IdxDossier
    )
    UPDATE  scm
    SET	   Ordinale	   = cte.Riga
    FROM	   SchemaCollMacc scm
       INNER JOIN cteNum cte ON scm.IdxDossier=cte.IdxDossier AND scm.CodScheda = cte.CodScheda AND scm.Vers = cte.Vers AND scm.NumScheda = cte.NumScheda
    WHERE	  scm.IdxDossier  = @Original_IdxDossier

    -- ...che Misure...
    UPDATE  mis
    SET	   Ordinale = scm.Ordinale
    FROM	   Misure mis
       INNER JOIN SchemaCollMacc scm ON mis.IdxDossier=scm.IdxDossier AND mis.CodScheda = scm.CodScheda AND mis.Vers = scm.Vers AND mis.NumScheda = scm.NumScheda
    WHERE	  mis.IdxDossier  = @Original_IdxDossier

COMMIT tran
GO
PRINT N'Altering [dbo].[stp_SCM_insByCodSchedaVers]...';


GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.16
-- Description:	insert record da scheda
-- =============================================
ALTER PROCEDURE [dbo].[stp_SCM_insByCodSchedaVers]
(
     @IdxDossier	INT
    ,@CodSchedaVers	NVARCHAR(100) -- formato CodScheda.Vers --> da splittare
)
AS
SET NOCOUNT ON;

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

-- calcolo ordinale
SELECT  @Ordinale   = ISNULL(MAX(Ordinale), 0) + 1 
FROM	  SchemaCollMacc
WHERE   IdxDossier  = @IdxDossier
GROUP BY IdxDossier

-- splitto e recupero dati...
SELECT	@CodScheda	= SUBSTRING ( @CodSchedaVers, 0, CHARINDEX ( '.', @CodSchedaVers ) )
SELECT	@Vers		= REPLACE	( @CodSchedaVers, @CodScheda+'.', '' )


MERGE SchemaCollMacc AS target
    USING (SELECT  @IdxDossier as IdxDossier, sc.CodScheda, sc.Vers, MAX(ISNULL(scm.NumScheda, 0)) + 1 as NumScheda, sc.Descrizione, sc.FasiEnab, @Ordinale AS Ordinale
          FROM	   AnagSchede sc
             LEFT OUTER JOIN SchemaCollMacc scm ON sc.CodScheda = scm.CodScheda AND sc.Vers = scm.Vers AND scm.IdxDossier	 = @IdxDossier
          WHERE   sc.CodScheda	 = @CodScheda
             AND sc.Vers		 = @Vers			
          GROUP BY sc.CodScheda, sc.Vers, sc.Descrizione, sc.FasiEnab) AS source (IdxDossier, CodScheda, Vers, NumScheda, Descrizione, FasiEnab, Ordinale)
                
   -- USING (SELECT	@IdxDossier, CodScheda, Vers, 1, Descrizione, FasiEnab
            --FROM	AnagSchede
            --WHERE	CodScheda	= @CodScheda
            --	AND Vers		= @Vers) AS source (IdxDossier, CodScheda, Vers, NumScheda, Descrizione, FasiEnab)
    ON (target.IdxDossier = source.IdxDossier AND target.CodScheda = source.CodScheda AND target.Vers = source.Vers AND target.NumScheda = source.NumScheda)
--    WHEN MATCHED THEN 
--        UPDATE SET Traduzione = source.traduzione
    WHEN NOT MATCHED THEN	
        INSERT (IdxDossier, CodScheda, Vers, NumScheda, Descrizione, opz, FasiEnab, Ordinale)
        VALUES (source.IdxDossier, source.CodScheda, source.Vers, source.NumScheda, source.Descrizione, 'M', source.FasiEnab, source.Ordinale);
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 @TotRighe AS INT;
    DECLARE @Riga AS INT;
    SET @Riga = 1

    -- seleziono TUTTE le schede da SchemaCollMacc
    -- ESPLODO tutte le combinazioni di @IdxDossier - @CodSchedaVers - @Fase
    
    -- 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
       
        -- eseguo stored di popolamento dati
        --SELECT @IdxDossier, @CodSchedaVers, @Fase   -- x Test
       EXEC dbo.stp_Mis_insMissing @IdxDossier, @CodSchedaVers, @Fase
        
        -- riga successiva o esco
        IF @Riga = @TotRighe
            SET @TotRighe = 0       -- esco
        ELSE 
            SET @Riga = @Riga+1    -- riga successiva
    END

COMMIT TRAN

RETURN
GO
PRINT N'Altering [dbo].[stp_AM_insertQuery]...';


GO
-- =============================================
-- Author:		S.E. Locatelli
-- Create date: 2015.06.09
-- Description:	insert record
-- =============================================
ALTER PROCEDURE [dbo].[stp_AM_insertQuery]
(
    @Matricola	NVARCHAR(50),
    @User		NVARCHAR(50),
    @Descrizione	NVARCHAR(50),
    @Cliente		NVARCHAR(50),
    @CodFam		NVARCHAR(50),
    @CorsaX		INT,
    @CorsaY		INT,
    @CorsaZ		INT
)
AS

BEGIN tran

    SET NOCOUNT OFF;

    DECLARE @IdxDossier INT = 0

    -- inserisco macchina
    INSERT INTO ElencoMacchineCN (Matricola, Descrizione, Cliente, CodFam, CorsaX, CorsaY, CorsaZ) 
    VALUES (@Matricola, @Descrizione, @Cliente, @CodFam, @CorsaX, @CorsaY, @CorsaZ);
    
    -- creo DOSSIER x matricola...
    EXEC stp_ED_InsertQuery @Matricola, @User
    -- leggo numero dossier!
    SELECT  @IdxDossier = IdxDossier
    FROM	  ElencoDossier 
    WHERE	  Matricola = @Matricola

    -- inserisco schema collaudi da famiglia macchina nel dossier...
    EXEC stp_SCM_insByFamMac @IdxDossier, @CodFam
    
    
    -- inserisco istanza prove standard	x varie fasi...
    EXEC stp_SCM_insMisFasiMissing @IdxDossier

    SELECT  *
    FROM	  ElencoMacchineCN 
    WHERE	  Matricola = @Matricola

COMMIT tran
GO
PRINT N'Altering [dbo].[v_Misure].[MS_DiagramPane1]...';


GO
EXECUTE sp_updateextendedproperty @name = N'MS_DiagramPane1', @value = 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 = 48
               Left = 302
               Bottom = 366
               Right = 472
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ds"
            Begin Extent = 
               Top = 138
               Left = 794
               Bottom = 268
               Right = 964
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "vv"
            Begin Extent = 
               Top = 138
               Left = 38
               Bottom = 268
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 23
         Width = 284
         Width = 1500
         Width = 2625
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 3375
         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 = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
    ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_Misure';


GO
PRINT N'Altering [dbo].[v_Misure].[MS_DiagramPane2]...';


GO
EXECUTE sp_updateextendedproperty @name = N'MS_DiagramPane2', @value = N'     Or = 1350
         Or = 1350
      End
   End
End
', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_Misure';


GO
PRINT N'Altering [dbo].[v_SchemaCollFamMacc].[MS_DiagramPane1]...';


GO
EXECUTE sp_updateextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[33] 4[28] 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 = "SchemaCollFamMacc"
            Begin Extent = 
               Top = 6
               Left = 33
               Bottom = 263
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagSchede"
            Begin Extent = 
               Top = 23
               Left = 365
               Bottom = 191
               Right = 535
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         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 = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_SchemaCollFamMacc';


GO
PRINT N'Altering [dbo].[v_selSchedaIst].[MS_DiagramPane1]...';


GO
EXECUTE sp_updateextendedproperty @name = N'MS_DiagramPane1', @value = 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 = "scm"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 229
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 2145
         Width = 4335
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 5475
         Width = 1500
      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
', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_selSchedaIst';


GO
PRINT N'Creating [dbo].[Misure].[Ordinale].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'valore ordinamento default', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Misure', @level2type = N'COLUMN', @level2name = N'Ordinale';


GO
PRINT N'Creating [dbo].[SchemaCollFamMacc].[Ordinale].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'valore ordinamento default', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SchemaCollFamMacc', @level2type = N'COLUMN', @level2name = N'Ordinale';


GO
PRINT N'Creating [dbo].[SchemaCollMacc].[Ordinale].[MS_Description]...';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'valore ordinamento default', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'SchemaCollMacc', @level2type = N'COLUMN', @level2name = N'Ordinale';


GO
PRINT N'Update complete.';


GO