marco
80 lines
2.1 KiB
Transact-SQL
80 lines
2.1 KiB
Transact-SQL
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
|