Samuele Locatelli
314 lines
8.1 KiB
Transact-SQL
314 lines
8.1 KiB
Transact-SQL
-- TABELLE!
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
create table BazaarRisorse(
|
|
Anno int not null,
|
|
Sett int not null,
|
|
idxDipendente int not null,
|
|
idxFase int not null,
|
|
Allocazione int,
|
|
SchemaWeek as ([dbo].[f_padLeft]([Allocazione],(5),(0))),
|
|
OreTot as ([dbo].[f_sumAllocSett]([Allocazione])),
|
|
|
|
constraint PK_BazaarRisorse primary key(Anno,Sett,idxDipendente,idxFase)
|
|
);
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_Description', 'allocazione oraria prevista, numerica, nel formato 00000-88888 (numero di ore x ogni gg della settimana indicata)', 'SCHEMA', 'dbo', 'TABLE', 'BazaarRisorse', 'COLUMN', 'Allocazione';
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_Description', 'calcola totale settimanale sommando i valori delel singole giornate (max 7...) - ([dbo].[f_sumSingleChar]([Allocazione]))', 'SCHEMA', 'dbo', 'TABLE', 'BazaarRisorse', 'COLUMN', 'OreTot';
|
|
go
|
|
|
|
create table ImpiegoRisorse(
|
|
Anno int not null,
|
|
Sett int not null,
|
|
idxDipendente int not null,
|
|
idxFase int not null,
|
|
OreTot int,
|
|
|
|
constraint PK_ImpiegoRisorse primary key(Anno,Sett,idxDipendente,idxFase)
|
|
);
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_Description', 'allocazione oraria prevista, numerica, nel formato 00000-88888 (numero di ore x ogni gg della settimana indicata)', 'SCHEMA', 'dbo', 'TABLE', 'ImpiegoRisorse', 'COLUMN', 'OreTot';
|
|
go
|
|
|
|
create table TallyTable(
|
|
n int not null identity
|
|
);
|
|
go
|
|
|
|
create unique clustered index idx_1 on TallyTable(n);
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
alter table TallyTable add
|
|
constraint PK_TallyTable primary key nonclustered(n);
|
|
go
|
|
|
|
|
|
-- x aggiungere condizioone su tab DatiComemssa (campo nickname NOT NULL) PRIMA imposto a ''
|
|
UPDATE DatiCommessa
|
|
SET nickname = ''
|
|
WHERE ISNULL(nickname,'') = ''
|
|
GO
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
update DatiCommessa set nickname = '' where nickname is null;
|
|
go
|
|
|
|
alter table DatiCommessa alter column
|
|
nickname nvarchar(50) not null;
|
|
go
|
|
|
|
alter table DatiCommessa add
|
|
constraint DF_DatiCommessa_nickname default ('') for nickname;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
alter VIEW v_selCommesse
|
|
AS
|
|
SELECT dbo.DatiCommessa.idxFase AS value, dbo.f_padLeft(dbo.DatiCommessa.NumeroCommessa, 4, '0') + '-' + dbo.f_padLeft(dbo.DatiCommessa.AnnoCommessa,
|
|
4, '0') + ' | ' + dbo.DatiCommessa.nickname + ' (' + dbo.DatiCommessa.NomeComm + ') [' + dbo.f_shortFormComm(dbo.DatiCommessa.NumeroCommessa,
|
|
dbo.DatiCommessa.AnnoCommessa) + ']' AS label, dbo.AnagFasi.idxProgetto AS conditio
|
|
FROM dbo.DatiCommessa INNER JOIN
|
|
dbo.AnagFasi ON dbo.DatiCommessa.idxFase = dbo.AnagFasi.idxFase
|
|
go
|
|
|
|
create VIEW v_selCommesseAttive
|
|
AS
|
|
SELECT dbo.DatiCommessa.idxFase AS value, dbo.f_padLeft(dbo.DatiCommessa.NumeroCommessa, 4, '0') + '-' + dbo.f_padLeft(dbo.DatiCommessa.AnnoCommessa,
|
|
4, '0') + ' | ' + dbo.DatiCommessa.nickname + ' (' + dbo.DatiCommessa.NomeComm + ') [' + dbo.f_shortFormComm(dbo.DatiCommessa.NumeroCommessa,
|
|
dbo.DatiCommessa.AnnoCommessa) + ']' AS label, dbo.AnagFasi.idxProgetto AS conditio
|
|
FROM dbo.DatiCommessa INNER JOIN
|
|
dbo.AnagFasi ON dbo.DatiCommessa.idxFase = dbo.AnagFasi.idxFase
|
|
WHERE (dbo.AnagFasi.Attivo = 1)
|
|
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 = "AnagFasi"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 268
|
|
Bottom = 216
|
|
Right = 439
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 1
|
|
End
|
|
Begin Table = "DatiCommessa"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 230
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 13140
|
|
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_selCommesseAttive';
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_selCommesseAttive';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
alter table Dip2ATR add
|
|
Gruppo nvarchar(50);
|
|
go
|
|
|
|
exec sp_addextendedproperty 'MS_Description', 'raggruppamento (funzionale) del dipendente', 'SCHEMA', 'dbo', 'TABLE', 'Dip2ATR', 'COLUMN', 'Gruppo';
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_D2ATR_getBazOrd
|
|
*
|
|
* recupera elenco dipendenti abilitati a bazaar ordinati x i gruppi validi
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_D2ATR_getBazOrd
|
|
|
|
AS
|
|
|
|
SELECT *
|
|
FROM Dip2ATR
|
|
WHERE ISNULL(Gruppo,'') <>''
|
|
ORDER BY Gruppo
|
|
|
|
RETURN
|
|
go
|
|
|
|
/**********************************************************
|
|
* STORED stp_baz_creaBulkByIdxFase
|
|
*
|
|
* crea in blocco tanti record di Bazaar Risorse quante le risorse disponibili per la fase, anno e settimana selezionate
|
|
*
|
|
* mod: S.E.L. 2013.09.04
|
|
*
|
|
**********************************************************/
|
|
create PROCEDURE stp_BAZ_creaBulkByIdxFase
|
|
(
|
|
@Anno INT = 0,
|
|
@Sett INT = 0,
|
|
@idxFase INT = 0
|
|
)
|
|
AS
|
|
|
|
-- creo 1 record x ogni dip in Dip2ATR con un gruppo valido (non nullo/vuoto) x il set Anno/Sett/Fase
|
|
INSERT INTO BazaarRisorse(Anno,Sett,idxDipendente,idxFase,Allocazione)
|
|
SELECT @Anno, @Sett, idxDipendente, @idxFase, 0
|
|
FROM Dip2ATR
|
|
WHERE ISNULL(Gruppo,'') <>''
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(191, GETDATE())
|
|
GO
|
|
SELECT TOP 10 * FROM LogUpdateDb ORDER BY Versione DESC
|
|
GO
|