set xact_abort on;
go

begin transaction;
go

alter table TimbratureExpl drop
  constraint DF_TimbratureExpl_minOrd ,
  constraint DF_TimbratureExpl_minLav ,
  constraint DF_TimbratureExpl_minNonLav ,
  constraint DF_TimbratureExpl_minStra ,
  constraint DF_TimbratureExpl_minPerm ,
  constraint DF_TimbratureExpl_minFer ,
  constraint DF_TimbratureExpl_minMal ,
  constraint DF_TimbratureExpl_minFest ,
  constraint DF_TimbratureExpl_minMpp ,
  constraint DF_TimbratureExpl_isOkTim ,
  constraint DF_TimbratureExpl_chkFunRes ,
  constraint DF_TimbratureExpl_chkFunCod ,
  constraint DF_TimbratureExpl_block ,
  constraint DF_TimbratureExpl_isOk ;
go

exec sp_rename 'PK_TimbratureExpl_1', 'tmp__PK_TimbratureExpl_1', 'OBJECT';
go

exec sp_rename 'TimbratureExpl', 'tmp__TimbratureExpl_0', 'OBJECT';
go

create table TimbratureExpl(
  dataLav       date          not null,
  idxDipendente int           not null,
  CognomeNome   nvarchar(100),
  entrata_1     datetime,
  uscita_1      datetime,
  entrata_2     datetime,
  uscita_2      datetime,
  entrata_3     datetime,
  uscita_3      datetime,
  entrata_4     datetime,
  uscita_4      datetime,
  h_lav         float,
  h_giust        as (((((CONVERT([float],[minPerm],(0))+[minFer])+[minMal])+[minFest])+[minMpp])/(60)),
  minLav        int           constraint DF_TimbratureExpl_minLav default ((0)),
  minOrd        int           constraint DF_TimbratureExpl_minOrd default ((0)),
  minNonLav     int           constraint DF_TimbratureExpl_minNonLav default ((0)),
  minStra       int           constraint DF_TimbratureExpl_minStra default ((0)),
  minPerm       int           constraint DF_TimbratureExpl_minPerm default ((0)),
  minFer        int           not null constraint DF_TimbratureExpl_minFer default ((0)),
  minMal        int           constraint DF_TimbratureExpl_minMal default ((0)),
  minFest       int           constraint DF_TimbratureExpl_minFest default ((0)),
  minMpp        int           constraint DF_TimbratureExpl_minMpp default ((0)),
  minArcoPres    as (datediff(minute,isnull([entrata_1],getdate()),isnull(isnull([uscita_4],isnull([uscita_3],isnull([uscita_2],isnull([uscita_1],[entrata_1])))),getdate()))),
  isOkTim       bit           constraint DF_TimbratureExpl_isOkTim default ((0)),
  isOkApp       bit           constraint DF_TimbratureExpl_isOk default ((1)),
  isOkLav        as (case when [minOrd]<=((((([minLav]+[minPerm])+[minFer])+[minMal])+[minFest])+[minMpp]) then (1) else (0) end),
  isOk           as (([isOkTim]&[isOkApp])&case when [minOrd]<=((((([minLav]+[minPerm])+[minFer])+[minMal])+[minFest])+[minMpp]) then (1) else (0) end),
  block         bit           constraint DF_TimbratureExpl_block default ((0)),
  chkFunCod     nvarchar(50)  constraint DF_TimbratureExpl_chkFunCod default (''),
  chkFunRes     nvarchar(50)  constraint DF_TimbratureExpl_chkFunRes default (''),

  constraint PK_TimbratureExpl_1 primary key(dataLav,idxDipendente)
);
go

create index ix_idxDip on TimbratureExpl(idxDipendente)
  include(dataLav,CognomeNome,h_lav,minOrd,minStra,minPerm,minFer,block,isOkApp,isOkTim);
go

create index ix_TimbrExpl_CognomeNome_DataLav on TimbratureExpl(CognomeNome,dataLav);
go

exec sp_addextendedproperty 'MS_Description', 'totale ore giustificate', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'h_giust';
go

exec sp_addextendedproperty 'MS_Description', 'minuti non lavorati (ovvero se fatti meno di ordinari e senza giustificativi)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'minNonLav';
go

exec sp_addextendedproperty 'MS_Description', 'DATEDIFF(n, ISNULL(entrata_1,GETDATE()), ISNULL(ISNULL(uscita_4,ISNULL(uscita_3,ISNULL(uscita_2,ISNULL(uscita_1,entrata_1)))),GETDATE()))', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'minArcoPres';
go

exec sp_addextendedproperty 'MS_Description', 'dato sintetico x indicare se TUTTE le timbrature componenti siano approvate', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'isOkApp';
go

exec sp_addextendedproperty 'MS_Description', 'determina se la giornata sia ok (oreLav + giustificativi >= oreOrd)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'isOkLav';
go

exec sp_addextendedproperty 'MS_Description', 'determina se il record sia "bloccato" (archiviazione e blocco mesi precedenti...)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'block';
go

exec sp_addextendedproperty 'MS_Description', 'eventuale diagnostica da check function sulla riga indicata (codice)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'chkFunCod';
go

exec sp_addextendedproperty 'MS_Description', 'eventuale diagnostica da check function sulla riga indicata (spiegazione)', 'SCHEMA', 'dbo', 'TABLE', 'TimbratureExpl', 'COLUMN', 'chkFunRes';
go

insert into TimbratureExpl(dataLav,idxDipendente,CognomeNome,entrata_1,uscita_1,entrata_2,uscita_2,entrata_3,uscita_3,entrata_4,uscita_4,h_lav,minLav,minOrd,minNonLav,minStra,minPerm,minFer,minMal,minFest,minMpp,isOkTim,isOkApp,block,chkFunCod,chkFunRes) select dataLav,idxDipendente,CognomeNome,entrata_1,uscita_1,entrata_2,uscita_2,entrata_3,uscita_3,entrata_4,uscita_4,h_lav,minLav,minOrd,minNonLav,minStra,minPerm,minFer,minMal,minFest,minMpp,isOkTim,isOkApp,block,chkFunCod,chkFunRes from tmp__TimbratureExpl_0;
go

drop table tmp__TimbratureExpl_0;
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

alter PROCEDURE stp_giust_insByDate
(
	@idxDipendente	INT = 0,
	@dataRif		DATETIME,
	@codGiust		NVARCHAR(5) = 'PERM'
)
AS
-- =============================================
-- Description: QUANDO VIENE USATA ??? 
-- SE c'è anomalia MPP entra sempre in gestione anomalia MPP per prima cosa
-- =============================================
DECLARE @minPerm INT = 0 -- min necessari x chiudere giornata
DECLARE @isPP    BIT = 0

SELECT  @isPP = CASE WHEN ISNULL(chkFunCod,'')='P' THEN 1 ELSE 0 END 
FROM TimbratureExpl WHERE idxDipendente=@idxDipendente AND dataLav=@dataRif

BEGIN TRAN  -- ???? MA SERVE ???

	IF(@isPP=1)  -- se carico mancata pausa pranzo 
		BEGIN
			-- recupero tipo di fuction da usare
			DECLARE @chkFun NVARCHAR(50) = ''
			DECLARE @minPP      INT = 0
			
			-- imposto variabili
			SELECT  @chkFun = dbo.f_chkFunDip(@idxDipendente), @codGiust = 'MPP'  -- mancata pausa pranzo

			-- controllo tipo di funzione... e imposto risultato!
			/*
			IF(@chkFun = 'PP60m')
				BEGIN
					SET @minPP  = 60
				END
			ELSE IF(@chkFun = 'PP30m')
				BEGIN
					SET @minPP  = 30
				END
			*/
		  IF(@chkFun LIKE 'PP%m')
			  BEGIN
				 SELECT @minPP=REPLACE(REPLACE(@chkFun,'PP',''),'m','')
			  END
			-- calcolo quota permesso
			SELECT	@minPerm = @minPP - (minArcoPres - minLav )
					FROM TimbratureExpl
					WHERE idxDipendente = @idxDipendente AND dataLav = @dataRif
		   
			-- INSERT giustificativo mancata pausa pranzo
			INSERT INTO Giustificativi
		   VALUES (@dataRif,@idxDipendente,@codGiust,@minPerm,1)
		 END
	ELSE
		BEGIN
			-- variabili
		   DECLARE	@minArr INT
		   --SELECT  @minArr=dbo.f_minArrotDip(@idxDipendente)
			SELECT @minArr = ISNULL(valInt,1) FROM AnagKeyValue WHERE nomeVar = 'minutiRoundDay'
		
		   -- calcolo minuti
		   SELECT	@minPerm = (CEILING(CONVERT(FLOAT,minOrd-minLav)/@minArr)*@minArr)-minMpp
		   FROM	TimbratureExpl
		   WHERE	isOkLav = 0
			   AND idxDipendente	= @idxDipendente
			   AND dataLav		= @dataRif 

		   -- INSERT giustificativo!
		   INSERT INTO	Giustificativi
		   VALUES (@dataRif,@idxDipendente,@codGiust,@minPerm,1)		
		END
COMMIT TRAN

RETURN
go

commit;
go


set ANSI_NULLS on;
go

/******************************************************************************
* Function f_oreFestDip
* calcola ore festività dipendente x data
* 
* ATTENZIONE: si suppone valga default a lunedì cioé SET DATEFIRST 1
*
* mod: S.E.L. 2013.05.08
*
*******************************************************************************/
create FUNCTION f_oreFestDip
(
	@idxDipendente	INT,
	@dataRif		DATETIME
)
RETURNS FLOAT
AS

BEGIN

	DECLARE	@oreFerie FLOAT = 0
	DECLARE @trovato INT = 0
	-- controllo se sia un giorno nel calendario ferie...
	SET		@trovato = ( SELECT ISNULL(COUNT(*),0) FROM CalendFesteFerie WHERE data = @dataRif AND CodGiust='FEST' )
	IF(@trovato > 0)
		BEGIN
			SET	@oreFerie = dbo.f_oreOrdDip(@idxDipendente, @dataRif)
		END
	
	RETURN @oreFerie
	
END
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/******************************************************************************
* Function f_oreFerieDip
* calcola ore FERIE dipendente x data
* 
* ATTENZIONE: si suppone valga default a lunedì cioé SET DATEFIRST 1
*
* mod: S.E.L. 2012.11.21
*
*******************************************************************************/
alter FUNCTION f_oreFerieDip
(
	@idxDipendente	INT,
	@dataRif		DATETIME
)
RETURNS FLOAT
AS

BEGIN

	DECLARE	@oreFerie FLOAT = 0
	DECLARE @trovato INT = 0
	-- controllo se sia un giorno nel calendario ferie...
	SET		@trovato = ( SELECT ISNULL(COUNT(*),0) FROM CalendFesteFerie WHERE data = @dataRif AND CodGiust='FER' )
	IF(@trovato > 0)
		BEGIN
			SET	@oreFerie = dbo.f_oreOrdDip(@idxDipendente, @dataRif)
		END
	
	RETURN @oreFerie
	
END
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_timbratureExplFillDate
*
* inserisce nelle TimbratureExpl le date mancanti x ogni dipendente, dato il mese indicato (fino a max data corrente...)
*
* mod: S.E.L. 2012.11.21
*
**********************************************************/
alter PROCEDURE stp_timbratureExplFillDate
(
	@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) )

-- elenco delle date DEL MESE (compresi sab/dom) fino a max data odierna
;WITH myCTE AS
(
	SELECT	CONVERT(DATE,DATEADD(dd, (N-1), @inizio)) as Data, idxDipendente, Cognome + ' ' + Nome + ' [' + Matricola + ']' as CognomeNome
	FROM	Tally, Dipendenti
	WHERE	N <= @numD
)
-- INSERISCO le mancanti con i min da lavorare da tab orari x ogni dipendente e le festività

INSERT INTO TimbratureExpl(dataLav, idxDipendente, CognomeNome, h_lav, minOrd, minFer, minFest, isOkTim)
SELECT mc.Data, mc.idxDipendente, mc.CognomeNome, 0, dbo.f_oreOrdDip(mc.idxDipendente,mc.Data) * 60, dbo.f_oreFerieDip(mc.idxDipendente,mc.Data) * 60, dbo.f_oreFestDip(mc.idxDipendente,mc.Data) * 60, 1
FROM myCte as mc LEFT OUTER JOIN TimbratureExpl te 
ON mc.idxDipendente=te.idxDipendente AND mc.Data = te.dataLav
WHERE te.dataLav IS NULL

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

------------------------------------------------------------------------
-- stp_timbratureExpl_getContinuato
--
-- Recupera la tab timbrature expl filtrando per anomalie di MANCATA PAUSA PRANZO (continuato)
-- mod:	S.E.L. - 2013..05.08
------------------------------------------------------------------------
create PROCEDURE stp_timbratureExpl_getContinuato
(
	@idxDipendente	INT = 0, -- 0 = tutti
	@inizio			DATETIME,
	@fine			DATETIME
)
AS

SELECT	* 
FROM	TimbratureExpl
WHERE	(idxDipendente = @idxDipendente OR @idxDipendente = 0)
	AND	((dataLav >= @inizio) AND (dataLav <= @fine))
	AND	isOkTim = 1 -- IN == OUT
	AND	(NOT (entrata_1 IS NULL) AND (entrata_2 IS NULL)) --entrata_1 ed uscita_1 ci sono, le altre no --> continuato!
	AND minOrd > 240 -- orario con + di 4 ore sul giorno!
	AND chkFunCod ='P'
	AND (minPerm + minFer + minFest + minMPP) = 0 -- non ci sono compensazioni Mancata Pausa Pranzo
ORDER BY dataLav DESC

RETURN
go

commit;
go










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