set xact_abort on;
go

begin transaction;
go

create table Storico_BdgtRisorseFasi(
  idxEv          int           not null identity constraint PK_Storico_BdgtRisorseFasi primary key,
  DataEv         datetime,
  idxFase        int,
  CodTipoRisorsa nvarchar(50),
  OreBdgt        decimal(18,2),
  CostoBdgt      numeric(18,2),
  DataRif        datetime,
  modificatoDa   nvarchar(250)
);
go

exec sp_addextendedproperty 'MS_Description', 'BCWS in ore (H)', 'SCHEMA', 'dbo', 'TABLE', 'Storico_BdgtRisorseFasi', 'COLUMN', 'OreBdgt';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

create table Storico_BdgtRisorseFasi(
  idxEv          int           not null identity constraint PK_Storico_BdgtRisorseFasi primary key,
  DataEv         datetime,
  idxFase        int,
  CodTipoRisorsa nvarchar(50),
  OreBdgt        decimal(18,2),
  CostoBdgt      numeric(18,2),
  DataRif        datetime,
  modificatoDa   nvarchar(250)
);
go

exec sp_addextendedproperty 'MS_Description', 'BCWS in ore (H)', 'SCHEMA', 'dbo', 'TABLE', 'Storico_BdgtRisorseFasi', 'COLUMN', 'OreBdgt';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

alter table Storico_DatiCommessa add
  modificatoDa nvarchar(250);
go

create index i_idxFase on Storico_DatiCommessa(idxFase);
go


commit;
go


set xact_abort on;
go

begin transaction;
go

alter table Storico_DatiCommForn add
  modificatoDa nvarchar(250);
go

create index i_idxFase on Storico_DatiCommForn(idxFase);
go

commit;
go


set xact_abort on;
go

begin transaction;
go

alter table Storico_DatiCommInteg add
  modificatoDa nvarchar(250);
go

create index i_idxFase on Storico_DatiCommInteg(idxFase);
go


commit;
go


set xact_abort on;
go

begin transaction;
go

alter table Storico_DatiFase add
  modificatoDa nvarchar(250);
go

create index i_idxFase on Storico_DatiFase(idxFase);
go


commit;
go


set xact_abort on;
go

begin transaction;
go

alter table BdgtRisorseFasi add
  modificatoDa nvarchar(250);
go

set ANSI_NULLS on;
go

/*************************************************************
* Trigger su delete/update
*
* Storicizza valori precedenti della tabella
*
* ultima modifica:	S.E. Locatelli - 2013.10.07
**************************************************************/
create TRIGGER trg_BRF_storicizza
ON BdgtRisorseFasi
FOR UPDATE, DELETE
AS

/* IF UPDATE() ... */

-- in caso di update e delete vado BRUTALMENTE a salvare record precedente in tabella storico relativa...
INSERT INTO Storico_BdgtRisorseFasi
SELECT GETDATE(), d.* FROM DELETED d
go

/**********************************************************
* STORED stp_BRF_InsertQuery
*
* aggiorna risorsa a budget x fasi (con costo budget corrente)
*
* mod: S.E.L. 2013.07.26
*
**********************************************************/
alter PROCEDURE stp_BRF_UpdateQuery
(
    @CodTipoRisorsa             NVARCHAR(50),
    @OreBdgt                    DECIMAL(18, 2),
    @Original_idxFase           INT,
    @Original_CodTipoRisorsa    NVARCHAR(50),
	@modificatoDa				NVARCHAR(250)
)
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(), modificatoDa = @modificatoDa
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
*
**********************************************************/
alter PROCEDURE stp_BRF_InsertQuery
(
    @idxFase		INT,
    @CodTipoRisorsa NVARCHAR(50),
    @OreBdgt		DECIMAL(18, 2),
	@modificatoDa	NVARCHAR(250)
)
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, modificatoDa) 
VALUES (@idxFase, @CodTipoRisorsa, @OreBdgt, @CostoBdgt, GETDATE(), @modificatoDa)
go

/**********************************************************
* STORED stp_BRF_DeleteQuery
*
* elimina risorsa a budget x fasi
*
* mod: S.E.L. 2013.08.30
*
**********************************************************/
alter 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

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*************************************************************
* Trigger su delete/update
*
* Storicizza valori precedenti della tabella
*
* ultima modifica:	S.E. Locatelli - 2013.10.07
**************************************************************/
alter TRIGGER trg_BRF_storicizza
ON BdgtRisorseFasi
FOR UPDATE, DELETE
AS

/* IF UPDATE() ... */

-- in caso di update e delete vado BRUTALMENTE a salvare record precedente in tabella storico relativa...
INSERT INTO Storico_BdgtRisorseFasi
SELECT GETDATE(), d.* FROM DELETED d
go

commit;
go


set xact_abort on;
go

begin transaction;
go

alter table DatiCommessa add
  modificatoDa nvarchar(250);
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_DC_updateQuery
*
* update DatiCommessa
*
* mod: S.E.L. 2013.05.23
*
**********************************************************/
alter PROCEDURE stp_DC_updateQuery
(
	@nickname           NVARCHAR(50),
	@CapoCommessa       NVARCHAR(50),
	@SQC                BIT,
	@Pubblico           BIT,
	@Importo            NUMERIC(18, 2),
	@DataApertura       DATETIME,
	@rifOfferta         NVARCHAR(100),
	@urlOfferta         NVARCHAR(250),
	@rifOrdine          NVARCHAR(100),
	@urlOrdine          NVARCHAR(250),
	@Indirizzo          NVARCHAR(100),
	@Note               NVARCHAR(500),
	@Original_idxFase   INT,
	@modificatoDa		NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;

UPDATE daticommessa 
SET    nickname = @nickname, 
       CapoCommessa = @CapoCommessa, 
       SQC = @SQC, 
       Pubblico = @Pubblico, 
       Importo = @Importo, 
       DataApertura = @DataApertura, 
       rifOfferta = @rifOfferta, 
       urlOfferta = @urlOfferta, 
       rifOrdine = @rifOrdine, 
       urlOrdine = @urlOrdine, 
       Indirizzo = @Indirizzo, 
       Note = @Note, 
       modificatoDa = @modificatoDa 
WHERE  ( idxFase = @Original_idxFase ) 

    SELECT idxFase, 
       AnnoCommessa, 
       NumeroCommessa, 
       nickname, 
       CheckOk, 
       CapoCommessa, 
       SQC, 
       Pubblico, 
       EnteProcuratore, 
       Importo, 
       DataApertura, 
       rifOfferta, 
       urlOfferta, 
       rifOrdine, 
       urlOrdine, 
       Indirizzo, 
       Note, 
       NomeComm, 
       ImportoIntegr, 
       ImportoFornExt, 
       BCWS, 
       modificatoDa 
FROM   daticommessa 
WHERE  ( idxFase = @Original_idxFase )
go

alter table DatiCommForn add
  modificatoDa nvarchar(250);
go

/**********************************************************
* STORED stp_DCF_UpdateQuery
*
* update record DatiCommessaFornitori da idx
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCF_UpdateQuery
(
	@Fornitore			NVARCHAR(250),
	@Referente			NVARCHAR(250),
	@Importo			NUMERIC(18, 2),
	@rifOrdineForn		NVARCHAR(100),
	@urlOrdineForn		NVARCHAR(250),
	@Note				NVARCHAR(500),
	@Original_idxDCF	INT,
	@modificatoDa		NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;
UPDATE  DatiCommForn
SET		Fornitore = @Fornitore, Referente = @Referente, 
		Importo = @Importo, rifOrdineForn = @rifOrdineForn, 
		urlOrdineForn = @urlOrdineForn, Note = @Note,
		modificatoDa = @modificatoDa
WHERE   (idxDCF = @Original_idxDCF)
go

/**********************************************************
* STORED stp_DCF_InsertQuery
*
* insert record DatiCommessaFornitori da idx
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCF_InsertQuery
(
	@idxFase		INT,
	@Fornitore		NVARCHAR(250),
	@Referente		NVARCHAR(250),
	@Importo		NUMERIC(18, 2),
	@rifOrdineForn	NVARCHAR(100),
	@urlOrdineForn	NVARCHAR(250),
	@Note			NVARCHAR(500),
	@modificatoDa	NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;
INSERT INTO dbo.DatiCommForn (idxFase, Fornitore, Referente, Importo, rifOrdineForn, urlOrdineForn, Note, modificatoDa)
VALUES (@idxFase, @Fornitore, @Referente, @Importo, @rifOrdineForn, @urlOrdineForn, @Note, @modificatoDa)
go

/**********************************************************
* STORED stp_DCF_delete
*
* elimina record DatiCommessaFornitori da idx
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCF_delete
(
	@Original_idxDCF int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [dbo].[DatiCommForn] WHERE ([idxDCF] = @Original_idxDCF)
go

alter table DatiCommInteg add
  modificatoDa nvarchar(250);
go

/**********************************************************
* STORED stp_DCI_delete
*
* update record DatiCommessaIntegrazioni
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCI_UpdateQuery
(
	@DataRif			DATETIME,
	@Importo			NUMERIC(18, 2),
	@rifOfferta			NVARCHAR(100),
	@urlOfferta			NVARCHAR(250),
	@rifOrdine			NVARCHAR(100),
	@urlOrdine			NVARCHAR(250),
	@Note				NVARCHAR(500),
	@Original_idxDCI	INT,
	@modificatoDa		NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;
UPDATE	dbo.DatiCommInteg 
SET		DataRif = @DataRif, Importo = @Importo,
		rifOfferta = @rifOfferta, urlOfferta = @urlOfferta, 
		rifOrdine = @rifOrdine, urlOrdine = @urlOrdine, 
		Note = @Note,
		modificatoDa = @modificatoDa
WHERE	(idxDCI = @Original_idxDCI)
go

/**********************************************************
* STORED stp_DCI_InsertQuery
*
* insert record DatiCommessaIntegrazioni
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCI_InsertQuery
(
	@idxFase		INT,
	@DataRif		DATETIME,
	@Importo		NUMERIC(18, 2),
	@rifOfferta		NVARCHAR(100),
	@urlOfferta		NVARCHAR(250),
	@rifOrdine		NVARCHAR(100),
	@urlOrdine		NVARCHAR(250),
	@Note			NVARCHAR(500),
	@modificatoDa	NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;
INSERT INTO dbo.DatiCommInteg (idxFase, DataRif, Importo, rifOfferta, urlOfferta, rifOrdine, urlOrdine, Note, modificatoDa)
VALUES (@idxFase, @DataRif, @Importo, @rifOfferta, @urlOfferta, @rifOrdine, @urlOrdine, @Note, @modificatoDa)
go

/**********************************************************
* STORED stp_DCI_delete
*
* elimina record DatiCommessaIntegrazioni da idx
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCI_delete
(
	@Original_idxDCI int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [dbo].[DatiCommInteg] WHERE ([idxDCI] = @Original_idxDCI)
go

alter table DatiFase add
  modificatoDa nvarchar(250);
go

/**********************************************************
* STORED stp_DFE_updateQuery
*
* aggiorna dati fasi
*
* mod: S.E.L. 2013.08.30
*
**********************************************************/
alter PROCEDURE stp_DFE_updateQuery
(
    @Original_idxFase   INT = 0,
    @DataScad			DATETIME,
    @ScadFixed			BIT,
    @Note				NVARCHAR(250),
	@modificatoDa		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, @modificatoDa)
    END
ELSE
    BEGIN
        -- altrimenti udpate
        UPDATE  DatiFase
        SET     DataScad    = @DataScad
               ,ScadFixed   = @ScadFixed
               ,Note        = @Note
			   ,modificatoDa = @modificatoDa
        WHERE   idxfase = @Original_idxFase
    END
RETURN
go

/**********************************************************
* STORED stp_AF_deleteQuery
*
* elenco fasi da progetto
*
* mod: S.E.L. 2012.11.02
*
**********************************************************/
alter PROCEDURE stp_AF_deleteQuery
(
	@Original_idxFase int
)
AS
	SET NOCOUNT OFF;

	-- controllo: se ci sono record assegnati a fase NON cancello ma archivio...
	DECLARE @trovate INT = 0;
	DECLARE @trovateChild INT = 0;
    
	SET		@trovate = ( SELECT ISNULL(COUNT(*),0) FROM RegAttivita WHERE idxFase = @Original_idxFase )
	SET		@trovateChild = ( SELECT ISNULL(COUNT(*),0) FROM AnagFasi WHERE idxFaseAncest = @Original_idxFase )

	IF(@trovate + @trovateChild > 0)
		BEGIN
			UPDATE	AnagFasi 
			SET		Attivo = 0
			WHERE	idxFase = @Original_idxFase  
		END
	ELSE
		BEGIN
            -- elimino dati extra fase (se ci sono)
			DELETE	
			FROM	DatiFase 
			WHERE	idxFase = @Original_idxFase 

            -- elimino dati extra commessa (se ci sono)
			DELETE	
			FROM	DatiCommessa 
			WHERE	idxFase = @Original_idxFase 

            -- elimino dati fase
			DELETE	
			FROM	AnagFasi 
			WHERE	idxFase = @Original_idxFase 
		END
go

commit;
go


-- VIEW!!!!
set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

create VIEW v_FattureETS
AS
SELECT       NumeroCommessa, AnnoCommessa, [Rif. Commessa] AS Commessa, ISNULL( [Società Fatturante], 'nd') AS Emitt, ISNULL( CLiente, 'nd') AS Dest, Tipologia, [Fattura Nr] AS NrFatt, Data AS DataFatt, Importo
FROM            dbo.v_Scip_ElencoFatture_Tutte

UNION
SELECT        NumeroCommessa, AnnoCommessa, [Rif. Commessa] AS Commessa, ISNULL( [Società Fatturante], 'nd')  AS Emitt, ISNULL( CLiente, 'nd')  AS Dest, Tipologia, [Fattura Nr] AS NrFatt, Data AS DataFatt, - Importo AS Importo
FROM            dbo.v_Scip_ElencoNote_Tutte
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 = "v_Scip_ElencoNote_Tutte"
            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 = 10
         Width = 284
         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', 'v_FattureETS';
go

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

commit;
go


-- stored da sistemare...
set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_AF_deleteQuery
*
* elenco fasi da progetto
*
* mod: S.E.L. 2012.11.02
*
**********************************************************/
alter PROCEDURE stp_AF_deleteQuery
(
	@Original_idxFase int
)
AS
	SET NOCOUNT OFF;

	-- controllo: se ci sono record assegnati a fase NON cancello ma archivio...
	DECLARE @trovate INT = 0;
	DECLARE @trovateChild INT = 0;
    
	SET		@trovate = ( SELECT ISNULL(COUNT(*),0) FROM RegAttivita WHERE idxFase = @Original_idxFase )
	SET		@trovateChild = ( SELECT ISNULL(COUNT(*),0) FROM AnagFasi WHERE idxFaseAncest = @Original_idxFase )

	IF(@trovate + @trovateChild > 0)
		BEGIN
			UPDATE	AnagFasi 
			SET		Attivo = 0
			WHERE	idxFase = @Original_idxFase  
		END
	ELSE
		BEGIN
            -- elimino dati extra fase (se ci sono)
			DELETE	
			FROM	DatiFase 
			WHERE	idxFase = @Original_idxFase 

            -- elimino dati extra commessa (se ci sono)
			DELETE	
			FROM	DatiCommessa 
			WHERE	idxFase = @Original_idxFase 

            -- elimino dati fase
			DELETE	
			FROM	AnagFasi 
			WHERE	idxFase = @Original_idxFase 
		END
go

/**********************************************************
* STORED stp_BRF_DeleteQuery
*
* elimina risorsa a budget x fasi
*
* mod: S.E.L. 2013.08.30
*
**********************************************************/
alter 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
*
**********************************************************/
alter PROCEDURE stp_BRF_InsertQuery
(
    @idxFase		INT,
    @CodTipoRisorsa NVARCHAR(50),
    @OreBdgt		DECIMAL(18, 2),
	@modificatoDa	NVARCHAR(250)
)
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, modificatoDa) 
VALUES (@idxFase, @CodTipoRisorsa, @OreBdgt, @CostoBdgt, GETDATE(), @modificatoDa)
go

/**********************************************************
* STORED stp_BRF_InsertQuery
*
* aggiorna risorsa a budget x fasi (con costo budget corrente)
*
* mod: S.E.L. 2013.07.26
*
**********************************************************/
alter PROCEDURE stp_BRF_UpdateQuery
(
    @CodTipoRisorsa             NVARCHAR(50),
    @OreBdgt                    DECIMAL(18, 2),
    @Original_idxFase           INT,
    @Original_CodTipoRisorsa    NVARCHAR(50),
	@modificatoDa				NVARCHAR(250)
)
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(), modificatoDa = @modificatoDa
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_DCF_delete
*
* elimina record DatiCommessaFornitori da idx
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCF_delete
(
	@Original_idxDCF int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [dbo].[DatiCommForn] WHERE ([idxDCF] = @Original_idxDCF)
go

/**********************************************************
* STORED stp_DCF_InsertQuery
*
* insert record DatiCommessaFornitori da idx
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCF_InsertQuery
(
	@idxFase		INT,
	@Fornitore		NVARCHAR(250),
	@Referente		NVARCHAR(250),
	@Importo		NUMERIC(18, 2),
	@rifOrdineForn	NVARCHAR(100),
	@urlOrdineForn	NVARCHAR(250),
	@Note			NVARCHAR(500),
	@modificatoDa	NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;
INSERT INTO dbo.DatiCommForn (idxFase, Fornitore, Referente, Importo, rifOrdineForn, urlOrdineForn, Note, modificatoDa)
VALUES (@idxFase, @Fornitore, @Referente, @Importo, @rifOrdineForn, @urlOrdineForn, @Note, @modificatoDa)
go

/**********************************************************
* STORED stp_DCF_UpdateQuery
*
* update record DatiCommessaFornitori da idx
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCF_UpdateQuery
(
	@Fornitore			NVARCHAR(250),
	@Referente			NVARCHAR(250),
	@Importo			NUMERIC(18, 2),
	@rifOrdineForn		NVARCHAR(100),
	@urlOrdineForn		NVARCHAR(250),
	@Note				NVARCHAR(500),
	@Original_idxDCF	INT,
	@modificatoDa		NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;
UPDATE  DatiCommForn
SET		Fornitore = @Fornitore, Referente = @Referente, 
		Importo = @Importo, rifOrdineForn = @rifOrdineForn, 
		urlOrdineForn = @urlOrdineForn, Note = @Note,
		modificatoDa = @modificatoDa
WHERE   (idxDCF = @Original_idxDCF)
go

/**********************************************************
* STORED stp_DCI_delete
*
* elimina record DatiCommessaIntegrazioni da idx
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCI_delete
(
	@Original_idxDCI int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [dbo].[DatiCommInteg] WHERE ([idxDCI] = @Original_idxDCI)
go

/**********************************************************
* STORED stp_DCI_InsertQuery
*
* insert record DatiCommessaIntegrazioni
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCI_InsertQuery
(
	@idxFase		INT,
	@DataRif		DATETIME,
	@Importo		NUMERIC(18, 2),
	@rifOfferta		NVARCHAR(100),
	@urlOfferta		NVARCHAR(250),
	@rifOrdine		NVARCHAR(100),
	@urlOrdine		NVARCHAR(250),
	@Note			NVARCHAR(500),
	@modificatoDa	NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;
INSERT INTO dbo.DatiCommInteg (idxFase, DataRif, Importo, rifOfferta, urlOfferta, rifOrdine, urlOrdine, Note, modificatoDa)
VALUES (@idxFase, @DataRif, @Importo, @rifOfferta, @urlOfferta, @rifOrdine, @urlOrdine, @Note, @modificatoDa)
go

/**********************************************************
* STORED stp_DCI_delete
*
* update record DatiCommessaIntegrazioni
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_DCI_UpdateQuery
(
	@DataRif			DATETIME,
	@Importo			NUMERIC(18, 2),
	@rifOfferta			NVARCHAR(100),
	@urlOfferta			NVARCHAR(250),
	@rifOrdine			NVARCHAR(100),
	@urlOrdine			NVARCHAR(250),
	@Note				NVARCHAR(500),
	@Original_idxDCI	INT,
	@modificatoDa		NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;
UPDATE	dbo.DatiCommInteg 
SET		DataRif = @DataRif, Importo = @Importo,
		rifOfferta = @rifOfferta, urlOfferta = @urlOfferta, 
		rifOrdine = @rifOrdine, urlOrdine = @urlOrdine, 
		Note = @Note,
		modificatoDa = @modificatoDa
WHERE	(idxDCI = @Original_idxDCI)
go

/**********************************************************
* STORED stp_DC_updateQuery
*
* update DatiCommessa
*
* mod: S.E.L. 2013.05.23
*
**********************************************************/
alter PROCEDURE stp_DC_updateQuery
(
	@nickname           NVARCHAR(50),
	@CapoCommessa       NVARCHAR(50),
	@SQC                BIT,
	@Pubblico           BIT,
	@Importo            NUMERIC(18, 2),
	@DataApertura       DATETIME,
	@rifOfferta         NVARCHAR(100),
	@urlOfferta         NVARCHAR(250),
	@rifOrdine          NVARCHAR(100),
	@urlOrdine          NVARCHAR(250),
	@Indirizzo          NVARCHAR(100),
	@Note               NVARCHAR(500),
	@Original_idxFase   INT,
	@modificatoDa		NVARCHAR(250)
)
AS
	SET NOCOUNT OFF;

UPDATE daticommessa 
SET    nickname = @nickname, 
       CapoCommessa = @CapoCommessa, 
       SQC = @SQC, 
       Pubblico = @Pubblico, 
       Importo = @Importo, 
       DataApertura = @DataApertura, 
       rifOfferta = @rifOfferta, 
       urlOfferta = @urlOfferta, 
       rifOrdine = @rifOrdine, 
       urlOrdine = @urlOrdine, 
       Indirizzo = @Indirizzo, 
       Note = @Note, 
       modificatoDa = @modificatoDa 
WHERE  ( idxFase = @Original_idxFase ) 

    SELECT idxFase, 
       AnnoCommessa, 
       NumeroCommessa, 
       nickname, 
       CheckOk, 
       CapoCommessa, 
       SQC, 
       Pubblico, 
       EnteProcuratore, 
       Importo, 
       DataApertura, 
       rifOfferta, 
       urlOfferta, 
       rifOrdine, 
       urlOrdine, 
       Indirizzo, 
       Note, 
       NomeComm, 
       ImportoIntegr, 
       ImportoFornExt, 
       BCWS, 
       modificatoDa 
FROM   daticommessa 
WHERE  ( idxFase = @Original_idxFase )
go

/**********************************************************
* STORED stp_DFE_updateQuery
*
* aggiorna dati fasi
*
* mod: S.E.L. 2013.08.30
*
**********************************************************/
alter PROCEDURE stp_DFE_updateQuery
(
    @Original_idxFase   INT = 0,
    @DataScad			DATETIME,
    @ScadFixed			BIT,
    @Note				NVARCHAR(250),
	@modificatoDa		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, @modificatoDa)
    END
ELSE
    BEGIN
        -- altrimenti udpate
        UPDATE  DatiFase
        SET     DataScad    = @DataScad
               ,ScadFixed   = @ScadFixed
               ,Note        = @Note
			   ,modificatoDa = @modificatoDa
        WHERE   idxfase = @Original_idxFase
    END
RETURN
go

/**********************************************************
* STORED stp_EO_getByTipo
*
* elenco opzioni x tipo
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_EO_getByTipo
(
    @CodTipo nvarchar(50)
)
AS
    SET NOCOUNT ON;
SELECT CodTipo, ordine, opzione FROM dbo.ElencoOpzioni
WHERE CodTipo = @CodTipo
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_ResComm_byIdxFase
*
* resoconto completo commessa da idxFase
*
* mod: S.E.L. 2013.09.04
*
**********************************************************/
create PROCEDURE stp_ResComm_byIdxFase
(
    @idxFase	INT
)
AS

SET NOCOUNT ON;


;WITH fasiCTE AS
(
	SELECT	idxFaseAncest, SUM(BCWS_time) AS BCWSFasi_time, SUM(BCWS_money) AS BCWSFasi_money, 
			SUM(ACWP_time) AS ACWPFasi_time, SUM(ACWP_money) AS ACWPFasi_money 
	FROM    ElencoFasiExpl
	WHERE	idxFaseAncest = @idxFase
	GROUP BY idxFaseAncest
)
,fattCTE AS
(
	SELECT	SUM(dc.Importo) AS Importo, dc.idxFase 
	FROM	v_FattureETS f INNER JOIN DatiCommessa dc ON f.NumeroCommessa=dc.NumeroCommessa AND f.AnnoCommessa=dc.AnnoCommessa
	WHERE	dc.idxFase = @idxFase
		AND Tipologia = 'FATTURA'
	GROUP BY dc.idxFase
)
,noteCTE AS
(
	SELECT	SUM(dc.Importo) AS Importo, dc.idxFase 
	FROM	v_FattureETS f INNER JOIN DatiCommessa dc ON f.NumeroCommessa=dc.NumeroCommessa AND f.AnnoCommessa=dc.AnnoCommessa
	WHERE	dc.idxFase = @idxFase
		AND Tipologia = 'NOTA DI ACCREDITO'
	GROUP BY dc.idxFase
)

SELECT	dc.idxFase, AnnoCommessa, NumeroCommessa, nickname, 
		BCWS, BCWSFasi_money, BCWSFasi_time, 
		ACWPFasi_money, ACWPFasi_time, 
		ACWPFasi_money / CASE WHEN BCWSFasi_money > 0 THEN BCWSFasi_money ELSE ACWPFasi_money + 0.0001 END AS ratio_money, 
		ACWPFasi_time / CASE WHEN BCWSFasi_time > 0 THEN BCWSFasi_time ELSE ACWPFasi_time + 0.0001 END AS ratio_time,
		ISNULL(f.Importo,0) - ISNULL(nc.Importo,0) AS Fatturato
FROM	DatiCommessa dc 
	INNER JOIN fasiCTE cte ON dc.idxFase = cte.idxFaseAncest
	LEFT OUTER JOIN fattCTE f ON dc.idxFase = f.idxFase
	LEFT OUTER JOIN noteCTE nc ON dc.idxFase = nc.idxFase
WHERE	dc.idxFase = @idxFase


RETURN
go

/**********************************************************
* STORED stp_SBRF_getByIdxFase
*
* storico dati FASE da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
create PROCEDURE stp_SBRF_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	sbrf.*
FROM	dbo.Storico_BdgtRisorseFasi sbrf INNER JOIN AnagFasi af ON af.idxFase=sbrf.idxFase
WHERE	af.idxFaseAncest = @idxFase
go

/**********************************************************
* STORED stp_SDCF_getByIdxFase
*
* storico dati FORNITORI commessa da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
create PROCEDURE stp_SDCF_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	*
FROM	dbo.Storico_DatiCommForn
WHERE	idxFase = @idxFase
go

/**********************************************************
* STORED stp_SDCI_getByIdxFase
*
* storico dati INTEGRAZIONI commessa da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
create PROCEDURE stp_SDCI_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	*
FROM	dbo.Storico_DatiCommInteg
WHERE	idxFase = @idxFase
go

/**********************************************************
* STORED stp_SDC_getByIdxFase
*
* storico dati commessa da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
create PROCEDURE stp_SDC_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	*
FROM	dbo.Storico_DatiCommessa
WHERE	idxFase = @idxFase
go

/**********************************************************
* STORED stp_SDF_getByIdxFase
*
* storico dati FASE da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
create PROCEDURE stp_SDF_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	sdf.*
FROM	dbo.Storico_DatiFase sdf INNER JOIN AnagFasi af ON af.idxFase=sdf.idxFase
WHERE	af.idxFaseAncest = @idxFase
go

/*************************************
* STORED PROCEDURE stp_vFattGetByComm
*
*  elenco fatture x commessa indicata (num/anno)
*
* mod : 2013.05.25
* aut : S.E. Locatelli
**************************************/
create PROCEDURE stp_vFattGetByComm
(
	@NumeroCommessa		INT = 0,
	@AnnoCommessa		INT = 0
)

AS

SELECT	* 
FROM	v_FattureETS
WHERE	NumeroCommessa	= @NumeroCommessa
	AND	AnnoCommessa	= @AnnoCommessa
ORDER BY DataFatt DESC

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_ResComm_byIdxFase
*
* resoconto completo commessa da idxFase
*
* mod: S.E.L. 2013.09.04
*
**********************************************************/
alter PROCEDURE stp_ResComm_byIdxFase
(
    @idxFase	INT
)
AS

SET NOCOUNT ON;


;WITH fasiCTE AS
(
	SELECT	idxFaseAncest, SUM(BCWS_time) AS BCWSFasi_time, SUM(BCWS_money) AS BCWSFasi_money, 
			SUM(ACWP_time) AS ACWPFasi_time, SUM(ACWP_money) AS ACWPFasi_money 
	FROM    ElencoFasiExpl
	WHERE	idxFaseAncest = @idxFase
	GROUP BY idxFaseAncest
)
,fattCTE AS
(
	SELECT	SUM(dc.Importo) AS Importo, dc.idxFase 
	FROM	v_FattureETS f INNER JOIN DatiCommessa dc ON f.NumeroCommessa=dc.NumeroCommessa AND f.AnnoCommessa=dc.AnnoCommessa
	WHERE	dc.idxFase = @idxFase
		AND Tipologia = 'FATTURA'
	GROUP BY dc.idxFase
)
,noteCTE AS
(
	SELECT	SUM(dc.Importo) AS Importo, dc.idxFase 
	FROM	v_FattureETS f INNER JOIN DatiCommessa dc ON f.NumeroCommessa=dc.NumeroCommessa AND f.AnnoCommessa=dc.AnnoCommessa
	WHERE	dc.idxFase = @idxFase
		AND Tipologia = 'NOTA DI ACCREDITO'
	GROUP BY dc.idxFase
)

SELECT	dc.idxFase, AnnoCommessa, NumeroCommessa, nickname, 
		BCWS, BCWSFasi_money, BCWSFasi_time, 
		ACWPFasi_money, ACWPFasi_time, 
		ACWPFasi_money / CASE WHEN BCWSFasi_money > 0 THEN BCWSFasi_money ELSE ACWPFasi_money + 0.0001 END AS ratio_money, 
		ACWPFasi_time / CASE WHEN BCWSFasi_time > 0 THEN BCWSFasi_time ELSE ACWPFasi_time + 0.0001 END AS ratio_time,
		ISNULL(f.Importo,0) - ISNULL(nc.Importo,0) AS Fatturato
FROM	DatiCommessa dc 
	INNER JOIN fasiCTE cte ON dc.idxFase = cte.idxFaseAncest
	LEFT OUTER JOIN fattCTE f ON dc.idxFase = f.idxFase
	LEFT OUTER JOIN noteCTE nc ON dc.idxFase = nc.idxFase
WHERE	dc.idxFase = @idxFase


RETURN
go

/**********************************************************
* STORED stp_ReW_getByAnnoSett
*
* recupera sett rilasciata dato anno/week number
*
* mod: S.E.L. 2013.05.24
*
**********************************************************/
alter PROCEDURE stp_ReW_getByAnnoSett
(
	@Anno int,
	@Sett int
)
AS
	SET NOCOUNT ON;
SELECT Anno, Sett, UsernameAD, dataConf 
FROM dbo.ReleasedWeek
WHERE Anno=@Anno AND Sett=@Sett
go

/**********************************************************
* STORED stp_SBRF_getByIdxFase
*
* storico dati FASE da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
alter PROCEDURE stp_SBRF_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	sbrf.*
FROM	dbo.Storico_BdgtRisorseFasi sbrf INNER JOIN AnagFasi af ON af.idxFase=sbrf.idxFase
WHERE	af.idxFaseAncest = @idxFase
go

/**********************************************************
* STORED stp_SDCF_getByIdxFase
*
* storico dati FORNITORI commessa da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
alter PROCEDURE stp_SDCF_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	*
FROM	dbo.Storico_DatiCommForn
WHERE	idxFase = @idxFase
go

/**********************************************************
* STORED stp_SDCI_getByIdxFase
*
* storico dati INTEGRAZIONI commessa da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
alter PROCEDURE stp_SDCI_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	*
FROM	dbo.Storico_DatiCommInteg
WHERE	idxFase = @idxFase
go

/**********************************************************
* STORED stp_SDC_getByIdxFase
*
* storico dati commessa da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
alter PROCEDURE stp_SDC_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	*
FROM	dbo.Storico_DatiCommessa
WHERE	idxFase = @idxFase
go

/**********************************************************
* STORED stp_SDF_getByIdxFase
*
* storico dati FASE da idxFase
*
* mod: S.E.L. 2013.10.07
*
**********************************************************/
alter PROCEDURE stp_SDF_getByIdxFase
(
	@idxFase int
)
AS
SET NOCOUNT ON;

SELECT	sdf.*
FROM	dbo.Storico_DatiFase sdf INNER JOIN AnagFasi af ON af.idxFase=sdf.idxFase
WHERE	af.idxFaseAncest = @idxFase
go

/*************************************
* STORED PROCEDURE stp_vFattGetByComm
*
*  elenco fatture x commessa indicata (num/anno)
*
* mod : 2013.05.25
* aut : S.E. Locatelli
**************************************/
alter PROCEDURE stp_vFattGetByComm
(
	@NumeroCommessa		INT = 0,
	@AnnoCommessa		INT = 0
)

AS

SELECT	* 
FROM	v_FattureETS
WHERE	NumeroCommessa	= @NumeroCommessa
	AND	AnnoCommessa	= @AnnoCommessa
ORDER BY DataFatt DESC

RETURN
go

commit;
go





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