set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

------------------------------------------------------------------------
-- stp_DipendentiAndAnomalie
--
-- Elenco dipendenti con email, data, tipo anomalia...  (con AND!!!)
-- mod:	S.E.L. - 2013.01.10
------------------------------------------------------------------------
create PROCEDURE stp_DipendentiAndAnomalie
(
	@inizio			DATETIME,
	@fine			DATETIME,
	@notOkApp		BIT = 0, -- 0 = tutte, 1 solo con anomalia
	@notOkTim		BIT = 0, -- 0 = tutte, 1 solo con anomalia
	@notOkLav		BIT = 0  -- 0 = tutte, 1 solo con anomalia
)
AS

SELECT	DISTINCT d.idxDipendente, te.CognomeNome, d.email
FROM	TimbratureExpl te INNER JOIN Dipendenti d ON te.idxDipendente = d.idxDipendente
WHERE	((dataLav >= @inizio) AND (dataLav <= @fine))
	AND	isOkApp = CASE WHEN @notOkApp = 1 THEN 0 ELSE isOkApp END
	AND	isOkTim = CASE WHEN @notOkTim = 1 THEN 0 ELSE isOkTim END
	AND	isOkLav = CASE WHEN @notOkLav = 1 THEN 0 ELSE isOkLav END
ORDER BY te.CognomeNome

RETURN
go

------------------------------------------------------------------------
-- stp_timbratureExpl_getByAnomalia
--
-- Recupera la tab timbrature expl filtrando per tipo di anomalia (con AND!!!)
-- mod:	S.E.L. - 2013.01.10
------------------------------------------------------------------------
create PROCEDURE stp_timbratureExpl_getByAnomalia
(
	@idxDipendente	INT = 0, -- 0 = tutti
	@inizio			DATETIME,
	@fine			DATETIME,
	@notOkApp		BIT = 0, -- 0 = tutte, 1 solo con anomalia
	@notOkTim		BIT = 0, -- 0 = tutte, 1 solo con anomalia
	@notOkLav		BIT = 0  -- 0 = tutte, 1 solo con anomalia
)
AS

SELECT	* 
FROM	TimbratureExpl
WHERE	(idxDipendente = @idxDipendente OR @idxDipendente = 0)
	AND	((dataLav >= @inizio) AND (dataLav <= @fine))
	AND	isOkApp = CASE WHEN @notOkApp = 1 THEN 0 ELSE isOkApp END
	AND	isOkTim = CASE WHEN @notOkTim = 1 THEN 0 ELSE isOkTim END
	AND	isOkLav = CASE WHEN @notOkLav = 1 THEN 0 ELSE isOkLav END
ORDER BY dataLav DESC

RETURN
go

commit
go








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