Egalware/SSC
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
SSC/CMS_SC/sql/Update 3.1_Schema_01.sql
T
giancarlo 64574af7bb Carica file su 'CMS_SC/sql' 
Script di aggiornamento del DB - schema e dati
2018-03-01 13:25:02 +00:00

2265 lines
65 KiB
Transact-SQL
Raw Permalink Blame History

/*
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
Reference in New Issue View Git Blame Copy Permalink