create table Dip2ATR(
  idxDipendente  int          not null,
  CodTipoRisorsa nvarchar(50) not null,

  constraint PK_Dip2ATR primary key(idxDipendente,CodTipoRisorsa)
);
go


set xact_abort on
go

begin transaction
go

INSERT INTO dbo.Dip2ATR
  VALUES (1, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (2, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (3, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (4, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (5, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (6, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (7, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (8, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (9, N'SEN')
INSERT INTO dbo.Dip2ATR
  VALUES (10, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (11, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (12, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (13, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (14, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (15, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (16, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (17, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (18, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (19, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (20, N'JUN')
INSERT INTO dbo.Dip2ATR
  VALUES (21, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (22, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (23, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (24, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (25, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (26, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (27, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (28, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (29, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (30, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (31, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (32, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (33, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (34, N'DIS')
INSERT INTO dbo.Dip2ATR
  VALUES (35, N'DIS')
go

commit transaction
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*************************************************************
* Trigger su delete
* Aggiorna tempo budget sulle fasi PROJ-ETS
*
* ultima modifica:	S.E. Locatelli - 2013.04.29
**************************************************************/
create TRIGGER trg_BRF_delBdgtOre
ON BdgtRisorseFasi
FOR DELETE
AS

SET XACT_ABORT ON

--IF UPDATE(BCWS)
BEGIN TRAN
    -- controllo se è stato aggiornato valore BCWS della commessa, in tal caso RICALCOLA a livello di (sotto)FASI il budget di tempo (ore) previsto
    UPDATE	af
    SET		budgetTime = ISNULL(brf.totOre,0)
           ,budgetMoney = ISNULL(brf.totMoney,0)
    FROM deleted d
    INNER JOIN AnagFasi af ON af.idxFase = d.idxFase
    LEFT OUTER JOIN	
    (
    SELECT	SUM(ISNULL(OreBdgt,0)) as totOre, SUM(ISNULL(OreBdgt,0)*ISNULL(CostoBdgt,0)) as totMoney, idxFase
    FROM	BdgtRisorseFasi 
    GROUP BY idxFase
    ) as brf ON d.idxFase = brf.idxFase

COMMIT TRAN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

create VIEW RegAttByFase
AS
SELECT        dbo.RegAttivita.idxFase, SUM(dbo.RegAttivita.oreTot) AS oreTot, SUM(dbo.RegAttivita.oreTot * dbo.AnagTipoRisorse.costoOrario) AS costoTot
FROM            dbo.AnagTipoRisorse INNER JOIN
                         dbo.Dip2ATR ON dbo.AnagTipoRisorse.CodTipoRisorsa = dbo.Dip2ATR.CodTipoRisorsa RIGHT OUTER JOIN
                         dbo.RegAttivita ON dbo.Dip2ATR.idxDipendente = dbo.RegAttivita.idxDipendente
GROUP BY dbo.RegAttivita.idxFase
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 = "RegAttivita"
            Begin Extent = 
               Top = 24
               Left = 345
               Bottom = 304
               Right = 515
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Dip2ATR"
            Begin Extent = 
               Top = 28
               Left = 626
               Bottom = 124
               Right = 814
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagTipoRisorse"
            Begin Extent = 
               Top = 70
               Left = 905
               Bottom = 183
               Right = 1100
            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 = 12
         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', 'RegAttByFase';
go

exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'RegAttByFase';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

alter VIEW ElencoFasiExpl
AS
SELECT        af.idxFase, af.idxProgetto, af.codFase, af.idxFaseAncest, af.nomeFase, af.descrizioneFase, af.enableTime, af.enableMoney, af.Attivo, ISNULL(af.budgetTime, 
                         0) AS BCWS_time, ISNULL(af.budgetMoney, 0) AS BCWS_money, ISNULL(df.DataScad, '9999-12-31') AS DataScad, ISNULL(df.ScadFixed, 0) AS ScadFixed, 
                         ISNULL(df.Note, '') AS Note, ISNULL(raf.oreTot, 0) AS ACWP_time, ISNULL(raf.costoTot, 0) AS ACWP_money, ISNULL(raf.oreTot, 0) 
                         / CASE WHEN ISNULL(af.budgetTime, 0) > 0 THEN af.budgetTime ELSE ISNULL(raf.oreTot, 1) END AS percUtil_time, ISNULL(raf.costoTot, 0) 
                         / CASE WHEN ISNULL(af.budgetMoney, 0) > 0 THEN af.budgetMoney ELSE ISNULL(raf.costoTot, 1) END AS percUtil_money
FROM            dbo.AnagFasi AS af LEFT OUTER JOIN
                         dbo.RegAttByFase AS raf ON af.idxFase = raf.idxFase LEFT OUTER JOIN
                         dbo.DatiFase AS df ON af.idxFase = df.idxFase
WHERE        (af.idxFaseAncest > 0)
go

exec sp_updateextendedproperty '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 = "af"
            Begin Extent = 
               Top = 29
               Left = 733
               Bottom = 288
               Right = 904
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "df"
            Begin Extent = 
               Top = 58
               Left = 407
               Bottom = 213
               Right = 577
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "raf"
            Begin Extent = 
               Top = 42
               Left = 1110
               Bottom = 157
               Right = 1280
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 20
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         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
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', 'dbo', 'VIEW', 'ElencoFasiExpl';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_ATR_getUnusedByFase
*
* recupera elenco risorse a budget x fasi 
*
* mod: S.E.L. 2013.07.26
*
**********************************************************/
create PROCEDURE stp_ATR_getUnusedByFase
(
    @idxFase    INT = 0
)
AS

SELECT  CodTipoRisorsa, DescrTipoRisorsa, costoOrario 
FROM    dbo.AnagTipoRisorse
WHERE   CodTipoRisorsa NOT IN
(
    SELECT  CodTipoRisorsa
    FROM    BdgtRisorseFasi
    WHERE   idxFase = @idxFase
)
ORDER BY costoOrario

RETURN
go

/**********************************************************
* STORED stp_BRF_DeleteQuery
*
* elimina risorsa a budget x fasi
*
* mod: S.E.L. 2013.08.30
*
**********************************************************/
create PROCEDURE stp_BRF_DeleteQuery
(
    @Original_idxFase           INT,
    @Original_CodTipoRisorsa    NVARCHAR(50)
)
AS

SET NOCOUNT OFF;

DELETE FROM BdgtRisorseFasi WHERE (idxFase = @Original_idxFase) AND CodTipoRisorsa = @Original_CodTipoRisorsa
go

/**********************************************************
* STORED stp_BRF_InsertQuery
*
* inserisce nuova risorsa a budget x fasi (con costo budget corrente)
*
* mod: S.E.L. 2013.08.30
*
**********************************************************/
create PROCEDURE stp_BRF_InsertQuery
(
    @idxFase int,
    @CodTipoRisorsa nvarchar(50),
    @OreBdgt decimal(18, 2)
)
AS

SET NOCOUNT OFF;

-- calcolo costo std della risorsa
DECLARE @CostoBdgt numeric(18, 2)

SELECT @CostoBdgt = ISNULL(costoOrario, 0) FROM AnagTipoRisorse WHERE CodTipoRisorsa = @CodTipoRisorsa

-- insert!
INSERT INTO BdgtRisorseFasi (idxFase, CodTipoRisorsa, OreBdgt, CostoBdgt, DataRif) 
VALUES (@idxFase, @CodTipoRisorsa, @OreBdgt, @CostoBdgt, GETDATE())
go

/**********************************************************
* STORED stp_BRF_InsertQuery
*
* aggiorna risorsa a budget x fasi (con costo budget corrente)
*
* mod: S.E.L. 2013.07.26
*
**********************************************************/
create PROCEDURE stp_BRF_UpdateQuery
(
    @CodTipoRisorsa             NVARCHAR(50),
    @OreBdgt                    DECIMAL(18, 2),
    @Original_idxFase           INT,
    @Original_CodTipoRisorsa    NVARCHAR(50)
)
AS

SET NOCOUNT OFF;

-- calcolo costo std della risorsa
DECLARE @CostoBdgt numeric(18, 2)

SELECT @CostoBdgt = ISNULL(costoOrario, 0) FROM AnagTipoRisorse WHERE CodTipoRisorsa = @CodTipoRisorsa

UPDATE dbo.BdgtRisorseFasi 
SET CodTipoRisorsa = @CodTipoRisorsa, OreBdgt = @OreBdgt, CostoBdgt = @CostoBdgt, DataRif = GETDATE() 
WHERE (idxFase = @Original_idxFase) AND CodTipoRisorsa = @Original_CodTipoRisorsa
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_DFE_updateQuery
*
* aggiorna dati fasi
*
* mod: S.E.L. 2013.08.30
*
**********************************************************/
create PROCEDURE stp_DFE_updateQuery
(
    @Original_idxFase    INT = 0,
    @DataScad   DATETIME,
    @ScadFixed  BIT,
    @Note       NVARCHAR(250)
)
AS

DECLARE @trovati INT = 0

SELECT @trovati = COUNT(*)
FROM DatiFase
WHERE   idxfase = @Original_idxFase

IF(@trovati = 0) 
    BEGIN
        -- se non c'è record creo...
        INSERT INTO  DatiFase
        VALUES  (@Original_idxFase, @DataScad, @ScadFixed, @Note)
    END
ELSE
    BEGIN
        -- altrimenti udpate
        UPDATE  DatiFase
        SET     DataScad    = @DataScad
               ,ScadFixed   = @ScadFixed
               ,Note        = @Note
        WHERE   idxfase = @Original_idxFase
    END
RETURN
go

commit;
go






-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(187, GETDATE())
GO
SELECT TOP 10 * FROM LogUpdateDb ORDER BY Versione DESC
GO