Samuele Locatelli
278 lines
6.4 KiB
Transact-SQL
278 lines
6.4 KiB
Transact-SQL
set ANSI_NULLS on;
|
|
go
|
|
|
|
/***************************************
|
|
* FUNCTION f_siglaUtente
|
|
*
|
|
* calcola la sigla utente dalla vista sulla tab utenti
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2013.02.20
|
|
*
|
|
****************************************/
|
|
create FUNCTION f_siglaUtente(@utente NVARCHAR(MAX))
|
|
RETURNS NVARCHAR(4) AS
|
|
BEGIN
|
|
|
|
----------------------------------------
|
|
-- dichiaro le componenti del nome
|
|
----------------------------------------
|
|
DECLARE @answ NVARCHAR(4)
|
|
|
|
SELECT @answ = ISNULL(sigla,'-') FROM v_selUtenti WHERE ( Cognome + ' ' + Nome = @utente ) OR ( Nome + ' ' + Cognome = @utente )
|
|
-- se fosse nullo provo a prendere le iniziali..
|
|
IF(ISNULL(@answ,'-') = '-')
|
|
BEGIN
|
|
DECLARE @indice INT
|
|
SELECT @indice = CHARINDEX(' ', @utente)
|
|
SET @answ = LEFT(@utente,1)+SUBSTRING(@utente,@indice+1,1)
|
|
END
|
|
|
|
RETURN UPPER(ISNULL(@answ,'-'))
|
|
END
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
alter table tbDocumenti alter column
|
|
SA nvarchar(4);
|
|
go
|
|
|
|
alter table tbDocumenti alter column
|
|
SR nvarchar(4);
|
|
go
|
|
|
|
alter table tbDocumenti add
|
|
constraint DF_tbDocumenti_Autore default ('-') for Autore,
|
|
constraint DF_tbDocumenti_SA default ('-') for SA,
|
|
constraint DF_tbDocumenti_Redattore default ('-') for Redattore,
|
|
constraint DF_tbDocumenti_SR default ('-') for SR;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/*************************************
|
|
* STORED PROCEDURE stp_tbDocs_insert
|
|
*
|
|
* crea un nuovo doc con i valori minimi x inserimento
|
|
*
|
|
* mod : 2012.10.04
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
alter PROCEDURE stp_tbDocs_insert
|
|
(
|
|
@nome NVARCHAR(250),
|
|
@fullPath NVARCHAR(500),
|
|
@userId NVARCHAR(50),
|
|
@isProtocollo BIT, --significa va calcolato NrProtocollo, Numero, Anno (corrente)
|
|
@DataRic DATETIME,
|
|
@DataDoc DATETIME,
|
|
@NumeroCommessa NVARCHAR(4),
|
|
@AnnoCommessa NVARCHAR(4),
|
|
@Fase NVARCHAR(250),
|
|
@Fonte NVARCHAR(100),
|
|
@Oggetto NVARCHAR(250),
|
|
@InOut NVARCHAR(3),
|
|
@Autore NVARCHAR(50),
|
|
@Redattore NVARCHAR(50),
|
|
@isRed BIT,
|
|
@newIdxFile INT OUTPUT
|
|
)
|
|
AS
|
|
|
|
|
|
BEGIN tran
|
|
|
|
-- dichiarazioni
|
|
DECLARE @Anno INT
|
|
DECLARE @Numero INT
|
|
DECLARE @NrProtocollo INT
|
|
DECLARE @SA NVARCHAR(4)
|
|
DECLARE @SR NVARCHAR(4)
|
|
--DECLARE @fullName NVARCHAR(250)
|
|
|
|
-- calcolo valori base
|
|
SET @Anno = YEAR(GETDATE())
|
|
SET @Numero = ( SELECT CASE WHEN @isProtocollo=1 THEN dbo.f_nextNumeroByAnno(@Anno) ELSE 0 END )
|
|
SET @NrProtocollo = ( SELECT CASE WHEN @isProtocollo=1 THEN dbo.f_nextNrProtocollo() ELSE 0 END )
|
|
|
|
-- calcolo sigla autore e redattore...
|
|
SELECT @SR = dbo.f_siglaUtente(@Redattore)
|
|
SELECT @SA = dbo.f_siglaUtente(@Autore)
|
|
|
|
|
|
-- vero insert
|
|
INSERT INTO tbDocumenti(NomeFile,fullPath,userId,isRed,NrProtocollo,Numero,Anno,DataRic,DataDoc,NumeroCommessa,AnnoCommessa,Fase,Fonte,Oggetto,InOut,Autore,Redattore,SA,SR)
|
|
VALUES (@nome,@fullPath,@userId,@isRed,@NrProtocollo,@Numero,@Anno,@DataRic,@DataDoc,@NumeroCommessa ,@AnnoCommessa,@Fase,@Fonte,@Oggetto,@InOut,@Autore,@Redattore,@SA,@SR)
|
|
|
|
SELECT @newIdxFile = SCOPE_IDENTITY()
|
|
|
|
COMMIT tran
|
|
|
|
RETURN @newIdxFile
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
create VIEW v_selUtenti
|
|
AS
|
|
SELECT UtenteAD, Cognome, Nome, email, sigla, attivo
|
|
FROM ETS_Anagrafica.dbo.Utenti
|
|
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 = "Utenti (ETS_Anagrafica.dbo)"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 291
|
|
Right = 208
|
|
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_selUtenti';
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_selUtenti';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(120, GETDATE())
|
|
GO
|
|
SELECT TOP 10 * FROM LogUpdateDb ORDER BY Versione DESC
|
|
GO
|