gpw_testci/GPW_Data/SqlScripts/GPW/GPW_00255.sql

set xact_abort on;
go

begin transaction;
go

alter table AnagProgetti add
  avvio datetime,
  chiusura datetime;
go

set ANSI_NULLS on;
go

commit;
go


set xact_abort on;
go

begin transaction;
go

create index ix_RA_inizio on RegAttivita(inizio)
  include(idxDipendente,idxFase,fine);
go

set ANSI_NULLS on;
go

alter TRIGGER trg_RegAtt_upsert
ON RegAttivita
/*----------------------------------
* Trigger su insert/update
*
* Aggiorna la tab dei RegAttivitaExpl
*
* creato da:		S.E. Locatelli - 2013.01.21
* ultima modifica:	
*----------------------------------*/

FOR INSERT, UPDATE
AS

IF(UPDATE(idxDipendente) OR UPDATE(inizio) OR UPDATE(fine) OR UPDATE(idxFase))
BEGIN
	-- dichiarazione variabili
	DECLARE @inizio DATETIME
	DECLARE @fine DATETIME
	DECLARE @idxDipendente INT

	-- recupero valori x inserting
    SELECT @inizio =  DATEADD(DAY,-1,CONVERT(DATE,i.inizio)), @fine = DATEADD(DAY,1,CONVERT(DATE,i.inizio)), @idxDipendente = i.idxDipendente FROM inserted i
    
	--chiamo la stored di ricalcolo
	EXEC stp_ricalcolaRegAttivitaExpl_byPeriodoUser @idxDipendente, @inizio, @fine
END
go

alter TRIGGER trg_RegAtt_delete
ON RegAttivita
/*----------------------------------
* Trigger su delete
*
* Aggiorna la tab RegAttivitaExpl ricalcolando la giornata del record interessato
*
* creato da:		S.E. Locatelli - 2013.01.21
* ultima modifica:	
*----------------------------------*/

FOR DELETE 
AS

-- dichiarazione variabili
DECLARE @inizio DATETIME
DECLARE @fine DATETIME
DECLARE @idxDipendente INT

-- recupero valori x inserting
SELECT @inizio = DATEADD(DAY,-1,CONVERT(DATE,d.inizio)), @fine = DATEADD(DAY,1,CONVERT(DATE,d.inizio)), @idxDipendente = d.idxDipendente FROM deleted d
    
--chiamo la stored di ricalcolo
EXEC stp_ricalcolaRegAttivitaExpl_byPeriodoUser @idxDipendente, @inizio, @fine
go

commit;
go


set xact_abort on;
go

begin transaction;
go

alter table RegAttivita drop
  constraint DF_RegAttivita_money ,
  constraint FK_RegAttivita_AnagFasi ,
  constraint FK_RegAttivita_Dipendenti ;
go

exec sp_rename 'PK_RegAttivita', 'tmp__PK_RegAttivita', 'OBJECT';
go

exec sp_rename 'RegAttivita', 'tmp__RegAttivita_0', 'OBJECT';
go

drop trigger trg_RegAtt_upsert;
go

drop trigger trg_RegAtt_delete;
go

create table RegAttivita(
  idxRA         int           not null identity constraint PK_RegAttivita primary key,
  idxDipendente int           not null,
  idxFase       int           not null,
  inizio        datetime      not null,
  fine          datetime      not null,
  descrizione   nvarchar(500),
  oreTot        decimal(18,0),
  importo       decimal(19,4) constraint DF_RegAttivita_money default ((0))
);
go

alter table RegAttivita add
  constraint FK_RegAttivita_AnagFasi foreign key(idxFase) references AnagFasi(idxFase) on update cascade,
  constraint FK_RegAttivita_Dipendenti foreign key(idxDipendente) references Dipendenti(idxDipendente) on update cascade;
go

create index ix_idxDip on RegAttivita(idxDipendente)
  include(idxFase,inizio);
go

create index ix_idxFase on RegAttivita(idxFase);
go

create index ix_inizio on RegAttivita(inizio,idxDipendente)
  include(fine);
go

create index ix_RA_inizio on RegAttivita(inizio)
  include(idxDipendente,idxFase,fine);
go

exec sp_addextendedproperty 'MS_Description', '([dbo].[f_hourInterval]([inizio],[fine]))', 'SCHEMA', 'dbo', 'TABLE', 'RegAttivita', 'COLUMN', 'oreTot';
go

set identity_insert RegAttivita on;
go

insert into RegAttivita(idxRA,idxDipendente,idxFase,inizio,fine,descrizione,oreTot,importo) select idxRA,idxDipendente,idxFase,inizio,fine,descrizione,convert(decimal(18,0), oreTot),importo from tmp__RegAttivita_0;
go

set identity_insert RegAttivita off;
go

drop table tmp__RegAttivita_0;
go

set ANSI_NULLS on;
go

create TRIGGER trg_RegAtt_upsert
ON RegAttivita
/*----------------------------------
* Trigger su insert/update
*
* Aggiorna la tab dei RegAttivitaExpl
*
* creato da:		S.E. Locatelli - 2013.01.21
* ultima modifica:	
*----------------------------------*/

FOR INSERT, UPDATE
AS

IF(UPDATE(idxDipendente) OR UPDATE(inizio) OR UPDATE(fine) OR UPDATE(idxFase))
BEGIN
	-- dichiarazione variabili
	DECLARE @inizio DATETIME
	DECLARE @fine DATETIME
	DECLARE @idxDipendente INT

	-- recupero valori x inserting
    SELECT @inizio =  DATEADD(DAY,-1,CONVERT(DATE,i.inizio)), @fine = DATEADD(DAY,1,CONVERT(DATE,i.inizio)), @idxDipendente = i.idxDipendente FROM inserted i
    
	--chiamo la stored di ricalcolo
	EXEC stp_ricalcolaRegAttivitaExpl_byPeriodoUser @idxDipendente, @inizio, @fine
END
go

create TRIGGER trg_RegAtt_delete
ON RegAttivita
/*----------------------------------
* Trigger su delete
*
* Aggiorna la tab RegAttivitaExpl ricalcolando la giornata del record interessato
*
* creato da:		S.E. Locatelli - 2013.01.21
* ultima modifica:	
*----------------------------------*/

FOR DELETE 
AS

-- dichiarazione variabili
DECLARE @inizio DATETIME
DECLARE @fine DATETIME
DECLARE @idxDipendente INT

-- recupero valori x inserting
SELECT @inizio = DATEADD(DAY,-1,CONVERT(DATE,d.inizio)), @fine = DATEADD(DAY,1,CONVERT(DATE,d.inizio)), @idxDipendente = d.idxDipendente FROM deleted d
    
--chiamo la stored di ricalcolo
EXEC stp_ricalcolaRegAttivitaExpl_byPeriodoUser @idxDipendente, @inizio, @fine
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/******************************************************************************
* Function f_hourInterval
*
* calcola intervallo orario tra 2 date/time
*******************************************************************************/
alter FUNCTION f_hourInterval
(
	@inizio DATETIME, 
	@fine DATETIME
)

RETURNS DECIMAL(19,4)
AS
BEGIN
	-- sommo e restituisco tot ore
	RETURN CAST(dbo.f_minuteInterval(@inizio,@fine) AS DECIMAL(19,4))/60
END
go

commit;
go


set xact_abort on;
go

begin transaction;
go

alter table RegAttivita drop
  constraint DF_RegAttivita_money ,
  constraint FK_RegAttivita_AnagFasi ,
  constraint FK_RegAttivita_Dipendenti ;
go

exec sp_rename 'PK_RegAttivita', 'tmp__PK_RegAttivita', 'OBJECT';
go

exec sp_rename 'RegAttivita', 'tmp__RegAttivita_1', 'OBJECT';
go

drop trigger trg_RegAtt_delete;
go

drop trigger trg_RegAtt_upsert;
go

create table RegAttivita(
  idxRA         int           not null identity constraint PK_RegAttivita primary key,
  idxDipendente int           not null,
  idxFase       int           not null,
  inizio        datetime      not null,
  fine          datetime      not null,
  descrizione   nvarchar(500),
  oreTot         as ([dbo].[f_hourInterval]([inizio],[fine])),
  importo       decimal(19,4) constraint DF_RegAttivita_money default ((0))
);
go

alter table RegAttivita add
  constraint FK_RegAttivita_AnagFasi foreign key(idxFase) references AnagFasi(idxFase) on update cascade,
  constraint FK_RegAttivita_Dipendenti foreign key(idxDipendente) references Dipendenti(idxDipendente) on update cascade;
go

create index ix_idxDip on RegAttivita(idxDipendente)
  include(idxFase,inizio);
go

create index ix_idxFase on RegAttivita(idxFase);
go

create index ix_inizio on RegAttivita(inizio,idxDipendente)
  include(fine);
go

create index ix_RA_inizio on RegAttivita(inizio)
  include(idxDipendente,idxFase,fine);
go

exec sp_addextendedproperty 'MS_Description', '([dbo].[f_hourInterval]([inizio],[fine]))', 'SCHEMA', 'dbo', 'TABLE', 'RegAttivita', 'COLUMN', 'oreTot';
go

set identity_insert RegAttivita on;
go

insert into RegAttivita(idxRA,idxDipendente,idxFase,inizio,fine,descrizione,importo) select idxRA,idxDipendente,idxFase,inizio,fine,descrizione,importo from tmp__RegAttivita_1;
go

set identity_insert RegAttivita off;
go

drop table tmp__RegAttivita_1;
go

set ANSI_NULLS on;
go

create TRIGGER trg_RegAtt_delete
ON RegAttivita
/*----------------------------------
* Trigger su delete
*
* Aggiorna la tab RegAttivitaExpl ricalcolando la giornata del record interessato
*
* creato da:		S.E. Locatelli - 2013.01.21
* ultima modifica:	
*----------------------------------*/

FOR DELETE 
AS

-- dichiarazione variabili
DECLARE @inizio DATETIME
DECLARE @fine DATETIME
DECLARE @idxDipendente INT

-- recupero valori x inserting
SELECT @inizio = DATEADD(DAY,-1,CONVERT(DATE,d.inizio)), @fine = DATEADD(DAY,1,CONVERT(DATE,d.inizio)), @idxDipendente = d.idxDipendente FROM deleted d
    
--chiamo la stored di ricalcolo
EXEC stp_ricalcolaRegAttivitaExpl_byPeriodoUser @idxDipendente, @inizio, @fine
go

create TRIGGER trg_RegAtt_upsert
ON RegAttivita
/*----------------------------------
* Trigger su insert/update
*
* Aggiorna la tab dei RegAttivitaExpl
*
* creato da:		S.E. Locatelli - 2013.01.21
* ultima modifica:	
*----------------------------------*/

FOR INSERT, UPDATE
AS

IF(UPDATE(idxDipendente) OR UPDATE(inizio) OR UPDATE(fine) OR UPDATE(idxFase))
BEGIN
	-- dichiarazione variabili
	DECLARE @inizio DATETIME
	DECLARE @fine DATETIME
	DECLARE @idxDipendente INT

	-- recupero valori x inserting
    SELECT @inizio =  DATEADD(DAY,-1,CONVERT(DATE,i.inizio)), @fine = DATEADD(DAY,1,CONVERT(DATE,i.inizio)), @idxDipendente = i.idxDipendente FROM inserted i
    
	--chiamo la stored di ricalcolo
	EXEC stp_ricalcolaRegAttivitaExpl_byPeriodoUser @idxDipendente, @inizio, @fine
END
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_AP_Expl_getData
*
* recupera elenco progetti con filtraggio ore totali per 
* - dipendente
* - periodo
*
* mod: S.E.L. 2012.11.06
*
**********************************************************/
create PROCEDURE stp_AP_Expl_getData
(
	@idxDipendente	INT	= 0,    -- 0 = tutti
	@dataFrom		DATETIME = '19000101', 
	@dataTo			DATETIME = '21000101',    
	@idxCliente		INT = 0,    -- 0 = tutti
	@showPrjArch	BIT = 1     -- 1 = mostra tutti
)
AS

-- controllo se date nulle importo a min/max
DECLARE @firstDate DATETIME
DECLARE @lastDate DATETIME

SELECT @firstDate = MIN(inizio), @lastDate=DATEADD(DAY,1,MAX(inizio)) FROM RegAttivita
SELECT  @dataFrom=ISNULL(@dataFrom, @firstDate) , @dataTo=ISNULL(@dataTo, @lastDate) 


;WITH myCTE AS
(
SELECT  ap.idxProgetto, SUM(ISNULL(ra.oreTot, 0)) AS totOre
FROM    AnagProgetti AS ap INNER JOIN
		AnagClienti AS ac ON ac.idxCliente=ap.idxCliente INNER JOIN
        AnagFasi AS af ON ap.idxProgetto = af.idxProgetto INNER JOIN
        RegAttivita AS ra ON af.idxFase = ra.idxFase
WHERE   (ra.idxDipendente = @idxDipendente OR @idxDipendente = 0)
        AND (ra.inizio >= @dataFrom AND ra.inizio  <= @dataTo)
        AND (ac.idxCliente = @idxCliente OR @idxCliente = 0)
        AND (ap.Attivo = CASE WHEN @showPrjArch <> 0 THEN ap.Attivo ELSE 1 END)
GROUP BY ap.idxProgetto
)
SELECT  ac.RagSociale
		, ap.idxProgetto
		, ap.idxCliente
		, ap.nomeProj
		, ap.descrProj
		, ISNULL(ap.budgetTime,0) AS budgetTime
		, ISNULL(ap.budgetMoney,0) AS budgetMoney
		, ISNULL(ap.OldIdx,-1) AS OldIdx
		, ap.Attivo
		, ISNULL(ap.codExt,'') AS codExt
		, ISNULL(cte.totOre, 0) AS totOre
FROM    AnagClienti AS ac INNER JOIN
        AnagProgetti AS ap ON ac.idxCliente = ap.idxCliente LEFT OUTER JOIN
		myCte AS cte ON cte.idxProgetto = ap.idxProgetto
--ORDER BY cte.totOre DESC
go

/**********************************************************
* STORED stp_RA_clonaLastRA_Utente
*
* clona un attività utente: se c'è da ultima altrimenti da zero x una certa data
*
* mod: S.E.L. 2013.01.17
*
**********************************************************/
create PROCEDURE stp_RA_clonaLastRA_Utente
(
	@idxDipendente	INT,
    @dataRif        DATETIME -- data x cui creare un record attività
)
AS

-- variabili
DECLARE @idxFase	INT
DECLARE @idxRA		INT

-- arrotondo data richiesta solo all'ora...
SELECT  @dataRif = DATEADD(HOUR, DATEPART(HOUR,@dataRif), CONVERT(DATETIME,(CONVERT(DATE,@dataRif))))

-- cerco se esista ultima reg attività utente...
SELECT @idxRA=ISNULL(idxRA,0) FROM	RegAttivita WHERE	idxDipendente = @idxDipendente

-- se non ho trovato records, ovvero idxRA = 0, prendo ultima fase da elenco
IF (@idxRA = 0)
    BEGIN
        -- calcolo ultima fase inserita...
        SELECT TOP 1 @idxFase=ISNULL(idxFase,0) FROM AnagFasi ORDER BY idxFase DESC
        -- se trovata fase inserisco!
        IF(@idxFase > 0)
            BEGIN
                INSERT INTO RegAttivita(idxDipendente, idxFase, inizio, fine, descrizione, importo) 
                VALUES      (@idxDipendente, @idxFase, @dataRif, DATEADD(HOUR,1,@dataRif), '...',0)
            END
    END
ELSE
    BEGIN
    -- inserisco su data richeista duplicazione dell'ultima attività utente...
    INSERT INTO RegAttivita(idxDipendente, idxFase, inizio, fine, descrizione, importo)    
    SELECT      idxDipendente, idxFase, @dataRif, DATEADD(minute, DATEDIFF(MINUTE, inizio, fine), @dataRif), descrizione, importo
    FROM        RegAttivita
    WHERE       (idxRA = @idxRA)

    END
go

create PROCEDURE stp_RA_InsertQuery
(
	@idxDipendente int,
	@idxFase int,
	@inizio datetime,
	@fine datetime,
	@descrizione nvarchar(500),
	@importo decimal(19, 4)
)
AS
	SET NOCOUNT OFF;
INSERT INTO RegAttivita
                         (idxDipendente, idxFase, inizio, fine, descrizione, importo)
VALUES        (@idxDipendente,@idxFase,@inizio,@fine,@descrizione,@importo)
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_RAD_Expl_getByIdxDipData
*
* recupera elenco attività (con Expl dei dati da anagrafica) da idxDipendente + dataRif
*
* mod: S.E.L. 2013.01.23
*
**********************************************************/
alter PROCEDURE stp_RAD_Expl_getByIdxDipData
(
	@idxDipendente	INT,
	@dataRif		DATETIME
)
AS

SELECT        RegAttivita.idxDipendente, RegAttivita.inizio, RegAttivita.fine, CASE WHEN ISNULL(descrizione, '') = '' THEN '-' ELSE ISNULL(descrizione, '') 
                         END AS descrizione, RegAttivita.oreTot, RegAttivita.oreTot * 60 AS minTot, RegAttivita.importo, AnagClienti.RagSociale, AnagProgetti.nomeProj, 
                         AnagFasi.nomeFase, RegAttivita.idxRA
FROM            RegAttivita INNER JOIN
                         AnagFasi ON RegAttivita.idxFase = AnagFasi.idxFase INNER JOIN
                         AnagProgetti ON AnagFasi.idxProgetto = AnagProgetti.idxProgetto INNER JOIN
                         AnagClienti ON AnagProgetti.idxCliente = AnagClienti.idxCliente
WHERE        (RegAttivita.idxDipendente = @idxDipendente) AND (CAST(RegAttivita.inizio AS DATE) = CAST(@dataRif AS DATE) OR
                         CAST(RegAttivita.fine AS DATE) = CAST(@dataRif AS DATE))
ORDER BY RegAttivita.inizio
go

/*----------------------------------
* stp_ricalcolaRegAttivitaExpl_byPeriodoUser
*
* Ricalcola le RegAttivitaExpl x utente e intervallo date
* mod:	S.E.L. - 2013.01.21
*----------------------------------*/
alter PROCEDURE stp_ricalcolaRegAttivitaExpl_byPeriodoUser
(
	@idxDipendente	INT = 0,
	@inizio			DATETIME,
	@fine			DATETIME
)
AS

-- dichiaro variabili x calcolo tempo execuzione e num records!
DECLARE	@startProc	DATETIME = GETDATE()
DECLARE @totRecords	INT = 0	

BEGIN
	

	-- faccio una tab temporanea con tallyTable x calcolo periodo intero...
	DECLARE @numD INT
	SET		@numD = ( SELECT DATEDIFF(dd, @inizio, @fine ) )	

	-- crea eventuali record mancanti: data, dip, cognome-nome
	INSERT INTO	RegAttivitaExpl(dataLav, idxDipendente)
	SELECT ty.Data, ty.idxDipendente
	FROM (
		SELECT	CONVERT(DATE,DATEADD(dd, -N, @fine)) as Data, idxDipendente
		FROM	Tally, Dipendenti
		WHERE	N <= @numD -- solo dati desiderati
		AND		(idxDipendente = @idxDipendente OR @idxDipendente = 0) --filtro su dipendenti
	) ty  LEFT OUTER JOIN RegAttivitaExpl rae ON ty.Data=rae.dataLav AND ty.idxDipendente=rae.idxDipendente
	WHERE	rae.dataLav IS NULL -- righe mancanti!

    -- resetto a zero le eventuali righe che non hanno attività registrate
    UPDATE	rae
	SET		minRegAtt = 0
		   ,descrProj = ''
	FROM	RegAttivitaExpl rae
		LEFT OUTER JOIN (
                    SELECT			DISTINCT idxDipendente, CONVERT(DATE,inizio) AS data
						FROM        dbo.RegAttivita
						WHERE		(idxDipendente = @idxDipendente OR @idxDipendente = 0) AND (inizio >= DATEADD(DAY,-1,@inizio) AND inizio <= @fine)
						GROUP BY	idxDipendente, CONVERT(DATE,inizio)
        
        ) vra ON rae.dataLav = vra.data AND rae.idxDipendente=vra.idxDipendente
        WHERE	vra.data IS NULL -- righe mancanti!
        AND     ((rae.idxDipendente = @idxDipendente OR @idxDipendente = 0) AND (rae.dataLav >= @inizio AND rae.dataLav < @fine)) -- solo x dip e periodo selezionati
	
	-- tab temp del record date progetti
	;WITH myCteRA AS(
						SELECT			idxDipendente, CONVERT(DATE,inizio) AS data, SUM(oreTot) AS oreTot, SUM(dbo.f_minuteInterval(inizio, fine)) as minTot, dbo.f_ProgettiByDate(idxDipendente, CONVERT(DATE,inizio)) AS progetti
							FROM        dbo.RegAttivita
							WHERE		(idxDipendente = @idxDipendente OR @idxDipendente = 0) AND (inizio >= DATEADD(DAY,-1,@inizio) AND inizio <= @fine)
							GROUP BY	idxDipendente, CONVERT(DATE,inizio)
					)
	
	-- aggiorno la tab delle RegAttivitaExpl
	UPDATE	rae
	SET		minRegAtt = minTot
		   ,descrProj = ISNULL(progetti,'')
	FROM	RegAttivitaExpl rae
		INNER JOIN myCteRA vra ON rae.dataLav = vra.data AND rae.idxDipendente=vra.idxDipendente	

	-- aggiorno cognome nome
	BEGIN TRY
		UPDATE	rae
		SET		CognomeNome = ISNULL(vcn.CognomeNome,'')
		FROM	RegAttivitaExpl rae INNER JOIN v_cognomeNome vcn ON rae.idxDipendente = vcn.idxDipendente
		WHERE	ISNULL(rae.CognomeNome,'') = ''
	END TRY
	BEGIN CATCH
		-- non faccio nulla
	END CATCH
END

SELECT @numD = @numD * COUNT(*) FROM Dipendenti WHERE (idxDipendente = @idxDipendente OR @idxDipendente = 0)

SELECT @startProc AS startTime, GETDATE() AS endTime, @numD AS numRec, DATEDIFF(ms, @startProc, GETDATE()) AS totMs, CAST(@numD AS FLOAT) * 1000 / DATEDIFF(ms, @startProc, GETDATE()) AS recPerSec

RETURN
go

commit;
go






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