Samuele Locatelli
472 lines
13 KiB
Transact-SQL
472 lines
13 KiB
Transact-SQL
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
alter table Tags2Doc drop
|
|
constraint FK_Tags2Doc_tbDocumenti ;
|
|
go
|
|
|
|
alter table tbDocumenti drop
|
|
constraint DF_tbDocumenti_isRed ,
|
|
constraint DF_tbDocumenti_Spunta ,
|
|
constraint DF_tbDocumenti_Autore ,
|
|
constraint DF_tbDocumenti_SA ,
|
|
constraint DF_tbDocumenti_Redattore ,
|
|
constraint DF_tbDocumenti_SR ;
|
|
go
|
|
|
|
exec sp_rename 'PK_tbDocumenti', 'tmp__PK_tbDocumenti', 'OBJECT';
|
|
go
|
|
|
|
exec sp_rename 'tbDocumenti', 'tmp__tbDocumenti_0', 'OBJECT';
|
|
go
|
|
|
|
create table tbDocumenti(
|
|
idxFile int not null identity constraint PK_tbDocumenti primary key,
|
|
NomeFile nvarchar(250) not null,
|
|
Nome as ('-'),
|
|
fullPath nvarchar(500) not null,
|
|
userId nvarchar(50) not null,
|
|
isRed bit constraint DF_tbDocumenti_isRed default ((0)),
|
|
NrProtocollo int,
|
|
Numero int,
|
|
Anno int,
|
|
DataRic datetime,
|
|
DataDoc datetime,
|
|
Commessa as ((right(replicate('0',(4))+[NumeroCommessa],(4))+'-')+right(replicate('0',(4))+[AnnoCommessa],(4))),
|
|
NumeroCommessa nvarchar(4),
|
|
AnnoCommessa nvarchar(4),
|
|
Fase nvarchar(250),
|
|
Fonte nvarchar(100),
|
|
Oggetto nvarchar(250),
|
|
InOut nvarchar(3),
|
|
Tipologia nvarchar(100),
|
|
Riferimenti nvarchar(100),
|
|
Attivita nvarchar(100),
|
|
Autore nvarchar(50) constraint DF_tbDocumenti_Autore default ('-'),
|
|
SA nvarchar(4) constraint DF_tbDocumenti_SA default ('-'),
|
|
Redattore nvarchar(50) constraint DF_tbDocumenti_Redattore default ('-'),
|
|
SR nvarchar(4) constraint DF_tbDocumenti_SR default ('-'),
|
|
Annotazioni ntext,
|
|
LegUtente nvarchar(25),
|
|
NoteSystemManager ntext,
|
|
Spunta int constraint DF_tbDocumenti_Spunta default ((0)),
|
|
Num_Fattura nvarchar(50)
|
|
);
|
|
go
|
|
|
|
set NUMERIC_ROUNDABORT off
|
|
set ANSI_NULLS on
|
|
set ANSI_PADDING on
|
|
set ANSI_WARNINGS on
|
|
set CONCAT_NULL_YIELDS_NULL on
|
|
set QUOTED_IDENTIFIER on
|
|
set ARITHABORT on;
|
|
go
|
|
|
|
create index ix_tbDocumenti_Commessa on tbDocumenti(Commessa desc);
|
|
go
|
|
|
|
create index i_commessa on tbDocumenti(Commessa);
|
|
go
|
|
|
|
create index i_fase on tbDocumenti(Fase);
|
|
go
|
|
|
|
create index i_fonte on tbDocumenti(Fonte);
|
|
go
|
|
|
|
create index i_InOut on tbDocumenti(InOut);
|
|
go
|
|
|
|
create index ix_dataRic on tbDocumenti(DataRic);
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_Description', '([dbo].[f_shortFileName]([Numero],[Anno],[DataDoc],[InOut],[Oggetto],substring([NomeFile],(0),len([NomeFile])-(3)),substring([NomeFile],len([NomeFile])-(3),(4))))', 'SCHEMA', 'dbo', 'TABLE', 'tbDocumenti', 'COLUMN', 'Nome';
|
|
go
|
|
|
|
set identity_insert tbDocumenti on;
|
|
go
|
|
|
|
insert into tbDocumenti(idxFile,NomeFile,fullPath,userId,isRed,NrProtocollo,Numero,Anno,DataRic,DataDoc,NumeroCommessa,AnnoCommessa,Fase,Fonte,Oggetto,InOut,Tipologia,Riferimenti,Attivita,Autore,SA,Redattore,SR,Annotazioni,LegUtente,NoteSystemManager,Spunta,Num_Fattura) select idxFile,NomeFile,fullPath,userId,isRed,NrProtocollo,Numero,Anno,DataRic,DataDoc,NumeroCommessa,AnnoCommessa,Fase,Fonte,Oggetto,InOut,Tipologia,Riferimenti,Attivita,Autore,SA,Redattore,SR,Annotazioni,LegUtente,NoteSystemManager,Spunta,Num_Fattura from tmp__tbDocumenti_0;
|
|
go
|
|
|
|
set identity_insert tbDocumenti off;
|
|
go
|
|
|
|
drop table tmp__tbDocumenti_0;
|
|
go
|
|
|
|
alter table Tags2Doc add
|
|
constraint FK_Tags2Doc_tbDocumenti foreign key(idxFile) references tbDocumenti(idxFile) on update cascade;
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
create VIEW v_selFonti_expl
|
|
AS
|
|
SELECT Codice AS value, Descrizione AS label, ISNULL(Telefono, '') AS Telefono, ISNULL(Fax, '') AS Fax, ISNULL(Email, '') AS Email
|
|
FROM SciP_2000.dbo.fornitori
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPane1', '[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 = "fornitori (SciP_2000.dbo)"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 353
|
|
Right = 223
|
|
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
|
|
', 'SCHEMA', 'dbo', 'VIEW', 'v_selFonti_expl';
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_selFonti_expl';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
create PROCEDURE stp_VSFE_getByFonte
|
|
(
|
|
@fonte nvarchar(200)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SELECT value, label, Telefono, Fax, Email FROM dbo.v_selFonti_expl
|
|
WHERE label = @fonte
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
drop procedure stp_utility_updateNomi;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/***************************************
|
|
* FUNCTION f_shortFileName
|
|
*
|
|
* calcola nome breve del file da regola
|
|
* nnnn-aaaa_BB_Descrizione breve troncata_nomefile.estensione
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2013.02.22
|
|
*
|
|
****************************************/
|
|
alter FUNCTION f_shortFileName(@numero INT, @anno INT, @dataDoc DATETIME, @inOut NVARCHAR(3), @oggetto NVARCHAR(250), @nome NVARCHAR(250), @tipoDoc NVARCHAR(4), @fullPath NVARCHAR(250))
|
|
RETURNS NVARCHAR(250) AS
|
|
BEGIN
|
|
|
|
----------------------------------------
|
|
-- dichiaro le componenti del nome
|
|
----------------------------------------
|
|
DECLARE @AA NVARCHAR(10), @BB NVARCHAR(2), @CC NVARCHAR(150), @DD NVARCHAR(130), @nomeFin NVARCHAR(MAX);
|
|
|
|
----------------------------------------
|
|
-- dichiaro variabili x gestione limite LEN
|
|
----------------------------------------
|
|
DECLARE @maxFullPathLenght INT = 225 -- 250 - @aa(14) - 4 (.txt) x la parte finale del nome ".xxx"
|
|
DECLARE @fullPathLenght INT
|
|
DECLARE @nomeLen INT = 0
|
|
DECLARE @extraLen INT = 0
|
|
DECLARE @sxPart INT = 15 -- lungh minima a sx...
|
|
|
|
----------------------------------------
|
|
-- calcolo le varie componenti
|
|
----------------------------------------
|
|
-- protocollo!
|
|
SET @AA = dbo.f_padLeft(CAST(CAST(@numero AS INT) AS NVARCHAR(10)), 5,'0') + '-' + CAST(@Anno AS NVARCHAR(4));
|
|
-- check digit
|
|
SET @BB = dbo.f_checkDigit(@numero, @anno, @dataDoc, @inOut, @nome);
|
|
-- nome trimmato @fullPath + @nome <= @maxFullPathLenght, trimmo nome prendendo left/right)
|
|
SET @fullPathLenght = LEN(@fullPath + @nome)
|
|
IF (@fullPathLenght > @maxFullPathLenght)
|
|
BEGIN
|
|
-- calcolo LEN del nome, es 202
|
|
SET @nomeLen = LEN(@nome)
|
|
-- calcolo "sforamento", es 301-225=76
|
|
SET @extraLen = @fullPathLenght - @maxFullPathLenght
|
|
-- 4 char "bloccati" a dx
|
|
SET @sxPart = (@nomeLen - @extraLen)
|
|
SET @DD = LEFT(@nome, @sxPart)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @DD = @nome;
|
|
END
|
|
|
|
----------------------------------------
|
|
-- nome completo finale
|
|
----------------------------------------
|
|
--SET @nomeFin = @AA + '_' + @BB + '_' + @CC + '_' + @DD -- versione vecchia: oggetto eliminato perchè in path! 2012.10.22
|
|
SET @nomeFin = @AA + '_' + @BB + '_' + @DD
|
|
-- ulteriore pulizia...
|
|
SET @nomeFin = REPLACE(REPLACE(@nomeFin,'/','-'),'\','-')
|
|
|
|
RETURN REPLACE(@nomeFin + '.','..','.') + REPLACE(@tipoDoc,'.','')
|
|
END
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
alter table Tags2Doc drop
|
|
constraint FK_Tags2Doc_tbDocumenti ;
|
|
go
|
|
|
|
alter table tbDocumenti drop
|
|
constraint DF_tbDocumenti_isRed ,
|
|
constraint DF_tbDocumenti_Autore ,
|
|
constraint DF_tbDocumenti_SA ,
|
|
constraint DF_tbDocumenti_Redattore ,
|
|
constraint DF_tbDocumenti_SR ,
|
|
constraint DF_tbDocumenti_Spunta ;
|
|
go
|
|
|
|
exec sp_rename 'PK_tbDocumenti', 'tmp__PK_tbDocumenti', 'OBJECT';
|
|
go
|
|
|
|
exec sp_rename 'tbDocumenti', 'tmp__tbDocumenti_1', 'OBJECT';
|
|
go
|
|
|
|
create table tbDocumenti(
|
|
idxFile int not null identity constraint PK_tbDocumenti primary key,
|
|
NomeFile nvarchar(250) not null,
|
|
Nome as ([dbo].[f_shortFileName]([Numero],[Anno],[DataDoc],[InOut],[Oggetto],substring([NomeFile],(0),len([NomeFile])-(3)),substring([NomeFile],len([NomeFile])-(3),(4)),[fullPath])),
|
|
fullPath nvarchar(500) not null,
|
|
userId nvarchar(50) not null,
|
|
isRed bit constraint DF_tbDocumenti_isRed default ((0)),
|
|
NrProtocollo int,
|
|
Numero int,
|
|
Anno int,
|
|
DataRic datetime,
|
|
DataDoc datetime,
|
|
Commessa as ((right(replicate('0',(4))+[NumeroCommessa],(4))+'-')+right(replicate('0',(4))+[AnnoCommessa],(4))),
|
|
NumeroCommessa nvarchar(4),
|
|
AnnoCommessa nvarchar(4),
|
|
Fase nvarchar(250),
|
|
Fonte nvarchar(100),
|
|
Oggetto nvarchar(250),
|
|
InOut nvarchar(3),
|
|
Tipologia nvarchar(100),
|
|
Riferimenti nvarchar(100),
|
|
Attivita nvarchar(100),
|
|
Autore nvarchar(50) constraint DF_tbDocumenti_Autore default ('-'),
|
|
SA nvarchar(4) constraint DF_tbDocumenti_SA default ('-'),
|
|
Redattore nvarchar(50) constraint DF_tbDocumenti_Redattore default ('-'),
|
|
SR nvarchar(4) constraint DF_tbDocumenti_SR default ('-'),
|
|
Annotazioni ntext,
|
|
LegUtente nvarchar(25),
|
|
NoteSystemManager ntext,
|
|
Spunta int constraint DF_tbDocumenti_Spunta default ((0)),
|
|
Num_Fattura nvarchar(50)
|
|
);
|
|
go
|
|
|
|
set NUMERIC_ROUNDABORT off
|
|
set ANSI_NULLS on
|
|
set ANSI_PADDING on
|
|
set ANSI_WARNINGS on
|
|
set CONCAT_NULL_YIELDS_NULL on
|
|
set QUOTED_IDENTIFIER on
|
|
set ARITHABORT on;
|
|
go
|
|
|
|
create index ix_tbDocumenti_Commessa on tbDocumenti(Commessa desc);
|
|
go
|
|
|
|
create index i_commessa on tbDocumenti(Commessa);
|
|
go
|
|
|
|
create index i_fase on tbDocumenti(Fase);
|
|
go
|
|
|
|
create index i_fonte on tbDocumenti(Fonte);
|
|
go
|
|
|
|
create index i_InOut on tbDocumenti(InOut);
|
|
go
|
|
|
|
create index ix_dataRic on tbDocumenti(DataRic);
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_Description', 'dbo.f_shortFileName([Numero],[Anno],[DataDoc],[InOut],[Oggetto],substring([NomeFile],(0),len([NomeFile])-(3)),substring([NomeFile],len([NomeFile])-(3),(4)),fullPath)', 'SCHEMA', 'dbo', 'TABLE', 'tbDocumenti', 'COLUMN', 'Nome';
|
|
go
|
|
|
|
set identity_insert tbDocumenti on;
|
|
go
|
|
|
|
insert into tbDocumenti(idxFile,NomeFile,fullPath,userId,isRed,NrProtocollo,Numero,Anno,DataRic,DataDoc,NumeroCommessa,AnnoCommessa,Fase,Fonte,Oggetto,InOut,Tipologia,Riferimenti,Attivita,Autore,SA,Redattore,SR,Annotazioni,LegUtente,NoteSystemManager,Spunta,Num_Fattura) select idxFile,NomeFile,fullPath,userId,isRed,NrProtocollo,Numero,Anno,DataRic,DataDoc,NumeroCommessa,AnnoCommessa,Fase,Fonte,Oggetto,InOut,Tipologia,Riferimenti,Attivita,Autore,SA,Redattore,SR,Annotazioni,LegUtente,NoteSystemManager,Spunta,Num_Fattura from tmp__tbDocumenti_1;
|
|
go
|
|
|
|
set identity_insert tbDocumenti off;
|
|
go
|
|
|
|
drop table tmp__tbDocumenti_1;
|
|
go
|
|
|
|
alter table Tags2Doc add
|
|
constraint FK_Tags2Doc_tbDocumenti foreign key(idxFile) references tbDocumenti(idxFile) on update cascade;
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(125, GETDATE())
|
|
GO
|
|
SELECT TOP 10 * FROM LogUpdateDb ORDER BY Versione DESC
|
|
GO
|