alter table Giustificativi add
  constraint DF_Giustificativi_automatico default (0) for automatico
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/*----------------------------------
* stp_ricalcolaPeriodoLav
*
* Aggiorna la tab dei periodi lavorati ricalcolando la giornata del record interessato
*
* creato da:		S.E.L. - 2008.06.09
* ultima modifica:	S.E.L. - 2012.02.06
*----------------------------------*/
alter PROCEDURE stp_ricalcolaPeriodoLav
(
	@idxDipendente AS INT,
	@dataRif AS DATETIME
)
AS

DECLARE @round AS INT
SET		@round = ( SELECT valInt FROM AnagKeyValue WHERE nomeVar = 'minutiRound' )

-- creo la tabella temporanea con riga incrementale
DECLARE @entrate TABLE
(
	idxDipendente INT NULL,
	oraTimbrata DATETIME NULL,
	RowNum INT NOT NULL
)
DECLARE @uscite TABLE
(
	idxDipendente INT NULL,
	oraTimbrata DATETIME NULL,
	RowNum INT NOT NULL
)

-- elimino i periodi della data indicata...
DELETE FROM PeriodiLav
WHERE    (idxDipendente = @idxDipendente) AND (CONVERT(varchar(8), entrata, 112) = CONVERT(varchar(8), @dataRif, 112))


-- ENTRATE!!!
INSERT INTO	@entrate
	SELECT  idxDipendente, 
            DATEADD(n, (ROUND(dbo.TimeSpanUnits('m', dbo.TimeOnly(dateadd(n,-1,dataOra))) / @round, 2) + 1) * @round , dbo.DateOnly(dataOra)) AS oraTimbrata
           ,ROW_NUMBER() OVER(ORDER BY dataOra) AS 'RowNum'
	FROM  Timbrature
	WHERE (idxDipendente = @idxDipendente) 
		AND dbo.DateOnly(dataOra) = dbo.DateOnly(@dataRif) 
		AND (entrata = 1)

-- USCITE!!!
INSERT INTO	@uscite
SELECT  idxDipendente
      , DATEADD(n, (ROUND(dbo.TimeSpanUnits('m', dbo.TimeOnly(dateadd(n,-1,dataOra))) / @round, 2) + 1) * @round , dbo.DateOnly(dataOra)) AS oraTimbrata
      , ROW_NUMBER() OVER(ORDER BY dataOra) AS 'RowNum'
FROM Timbrature
WHERE  (idxDipendente = @idxDipendente) AND dbo.DateOnly(dataOra) = dbo.DateOnly(@dataRif) AND (entrata = 0)



INSERT INTO PeriodiLav(idxDipendente, entrata, uscita, rowNum)
SELECT	cte_in.idxDipendente, cte_in.oraTimbrata, cte_out.oraTimbrata, cte_in.RowNum 
FROM	@entrate AS cte_in LEFT OUTER JOIN @uscite AS cte_out ON cte_in.idxDipendente=cte_out.idxDipendente AND cte_in.RowNum=cte_out.RowNum


-- inserisco eventuali singole timbrature uscita come in/out uguali
INSERT INTO PeriodiLav(idxDipendente, entrata, uscita, rowNum)
SELECT	cte_out.idxDipendente, cte_out.oraTimbrata, cte_out.oraTimbrata, cte_out.RowNum 
FROM	@entrate AS cte_in RIGHT OUTER JOIN @uscite AS cte_out ON cte_in.idxDipendente=cte_out.idxDipendente AND cte_in.RowNum=cte_out.RowNum
WHERE	cte_in.RowNum IS NULL

RETURN
go

/*----------------------------------
* stp_timbratureExpl_ByUserDate
*
* Recupera la tab delletimbrature expl x utente e intervallo date
* mod:	S.E.L. - 2012.09.17
*----------------------------------*/
alter PROCEDURE stp_timbratureExpl_ByUserDate
(
	@idxDipendente	INT,
	@dataFrom		DATETIME,
	@dataTo			DATETIME,
	@showWE			BIT = 1 -- imposto visualizzazione week-end a true di default
)
AS

-- imposto LUN x inizio settimana
SET DATEFIRST 1

SELECT	* 
FROM	TimbratureExpl
WHERE	(idxDipendente = @idxDipendente OR @idxDipendente = 0)
AND		dataLav >= @dataFrom AND dataLav < @dataTo
AND		DATEPART(dw, dataLav) <= CASE WHEN @showWE=0 THEN 5 ELSE 7 END --solo lun-ven, 1-5
ORDER BY dataLav DESC

RETURN
go

commit
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/*----------------------------------
* stp_ricalcolaTimbExpl_byPeriodoUser
*
* Ricalcola le TimbratureExpl x utente e intervallo date
* mod:	S.E.L. - 2012.09.17
*----------------------------------*/
create PROCEDURE stp_ricalcolaTimbExpl_byPeriodoUser
(
	@idxDipendente	INT = 0,
	@inizio			DATETIME,
	@fine			DATETIME
)
AS

SET DATEFIRST 1
-- quanti giorni mi servono? conto giorni del mese
DECLARE @numD INT
SET		@numD = ( SELECT DATEDIFF(dd, @inizio, @fine) )

-- contatore per ciclo
DECLARE	@currRow	INT = 0
DECLARE	@currIdxDip INT = 0
DECLARE	@currDate	DATETIME


-- creo la tabella temporanea con riga incrementale
DECLARE @dateAndUser TABLE
	(
	Riga int IDENTITY(1,1) NOT NULL,
	DATA DATETIME NULL,
	idxDipendente INT NULL
	)

-- elenco delle date DEL MESE (compresi sab/dom) fino a max data odierna
;WITH myCTE AS
(
	SELECT	dbo.DateOnly(DATEADD(dd, (N-1), @inizio)) as Data, idxDipendente
	FROM	Tally, Dipendenti
	WHERE	N <= @numD
	AND		(idxDipendente = @idxDipendente OR @idxDipendente = 0)
)

-- inserisco valori cte in temp table!
INSERT INTO	@dateAndUser
SELECT * FROM myCTE

-- eseguo update! Iterazione sulle righe della CTE
WHILE 1 = 1
BEGIN  

	-- Get next customerId
	SELECT		TOP 1 @currRow=Riga, @currIdxDip=idxDipendente, @currDate=Data
	FROM		@dateAndUser
	WHERE		Riga > @currRow
	ORDER BY	Riga

	-- Exit loop if no more customers
	IF @@ROWCOUNT = 0 BREAK;

	-- ESEGUO!
	--select @currIdxDip, @currDate
	EXEC stp_ricalcolaTimbratureExpl @currIdxDip, @currDate

END


RETURN
go

commit
go








-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(200, GETDATE())
GO