create table AnagTipoCheck(
  chkFun   nvarchar(50) not null constraint PK_AnagTipoCheck primary key,
  descrFun nvarchar(50)
);
go


set xact_abort on
go

begin transaction
go

INSERT INTO dbo.AnagTipoCheck
  VALUES (N'PP30m', N'Pausa pranzo 30 min')
INSERT INTO dbo.AnagTipoCheck
  VALUES (N'PP60m', N'Pausa pranzo 60 min')
go

commit transaction
go


set xact_abort on;
go

begin transaction;
go

alter table AnagOrari add
  chkFun nvarchar(50) constraint DF_AnagOrari_checkFun default ('');
go

exec sp_addextendedproperty 'MS_Description', 'parametro (opzionale) da passare alla funzione di check "finale" per verifica giornate lavorate in tab TimbratureExpl', 'SCHEMA', 'dbo', 'TABLE', 'AnagOrari', 'COLUMN', 'chkFun';
go

update AnagOrari set chkFun=('');
go

set ANSI_NULLS on;
go

commit;
go

/******************************************************************************
* FUNCTION f_chkFunDip
* 
* restituisce il nome della funzione di check da applicare dato profilo orario
*
* mod: S.E.L. 2013.03.28
*
*******************************************************************************/
create FUNCTION f_chkFunDip
(
	@idxDipendente	INT
)
RETURNS NVARCHAR(50)
AS

BEGIN

    DECLARE	@chkFun NVARCHAR(50) = ''
	
	
    SELECT	@chkFun = ISNULL(chkFun,'') 
    FROM	AnagOrari ao INNER JOIN Dipendenti d ON ao.codOrario=d.codOrario
    WHERE	d.idxDipendente = @idxDipendente
	
    RETURN @chkFun

END
go


set xact_abort on;
go

begin transaction;
go

alter table TimbratureExpl drop
  constraint DF_TimbratureExpl_minOrd ,
  constraint DF_TimbratureExpl_minLav ,
  constraint DF_TimbratureExpl_minStra ,
  constraint DF_TimbratureExpl_minNonLav ,
  constraint DF_TimbratureExpl_minFest ,
  constraint DF_TimbratureExpl_minPerm ,
  constraint DF_TimbratureExpl_minFer ,
  constraint DF_TimbratureExpl_minFest1 ,
  constraint DF_TimbratureExpl_block ,
  constraint DF_TimbratureExpl_isOkTim ,
  constraint DF_TimbratureExpl_isOk ;
go

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

exec sp_rename 'TimbratureExpl', 'tmp__TimbratureExpl_2', '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])/(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           constraint DF_TimbratureExpl_minFer default ((0)),
  minMal        int           constraint DF_TimbratureExpl_minFest1 default ((0)),
  minFest       int           constraint DF_TimbratureExpl_minFest 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]) then (1) else (0) end),
  isOk           as (([isOkTim]&[isOkApp])&case when [minOrd]<=(((([minLav]+[minPerm])+[minFer])+[minMal])+[minFest]) then (1) else (0) end),
  block         bit           constraint DF_TimbratureExpl_block default ((0)),
  chkFunCod     nvarchar(50)  constraint DF_TimbratureExpl_chkFunRes default (''),
  chkFunRes     nvarchar(50)  constraint DF_TimbratureExpl_chkFunRes1 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,isOkTim,isOkApp,block) 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,isOkTim,isOkApp,block from tmp__TimbratureExpl_2;
go

drop table tmp__TimbratureExpl_2;
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

create VIEW v_selCodOrario
AS
SELECT        codOrario AS value, codOrario AS label
FROM            dbo.AnagOrari
go

exec sp_addextendedproperty 'MS_DiagramPane1', '[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "AnagOrari"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 200
               Right = 223
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 3810
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', 'dbo', 'VIEW', 'v_selCodOrario';
go

exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_selCodOrario';
go

create VIEW v_selTipoChk
AS
SELECT        chkFun AS value, descrFun AS label
FROM            dbo.AnagTipoCheck
go

exec sp_addextendedproperty 'MS_DiagramPane1', '[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "AnagTipoCheck"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 102
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1755
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', 'dbo', 'VIEW', 'v_selTipoChk';
go

exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'v_selTipoChk';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_AD_insertQuery
*
* aggiunge un dipendente in anagrafica
*
* mod: S.E.L. 2012.10.29
*
**********************************************************/
alter PROCEDURE stp_AD_insertQuery
(
	@matricola NVARCHAR(50),
	@CF NVARCHAR(16),
	@Cognome NVARCHAR(50),
	@Nome NVARCHAR(50),
	@dataNascita DATETIME,
	@luogoNascita NVARCHAR(50),
	@provNascita NVARCHAR(50),
	@nazNascita NVARCHAR(50),
	@email NVARCHAR(250),
	@dominio NVARCHAR(50),
	@utente NVARCHAR(50),
	@codOrario NVARCHAR(50),
	@attivo BIT
)
AS
	SET NOCOUNT OFF;
INSERT INTO Dipendenti
                         (matricola, CF, Cognome, Nome, dataNascita, luogoNascita, provNascita, nazNascita, email, dominio, utente,codOrario,attivo)
VALUES        (@matricola,@CF,@Cognome,@Nome,@dataNascita,@luogoNascita,UPPER(@provNascita),UPPER(@nazNascita),@email,@dominio,@utente,@codOrario,@attivo);
	 
SELECT * FROM Dipendenti WHERE (idxDipendente = SCOPE_IDENTITY())
go

/**********************************************************
* STORED stp_AD_updateQuery
*
* aggiorna un dipendente da anagrafica
*
* mod: S.E.L. 2012.10.29
*
**********************************************************/
alter PROCEDURE stp_AD_updateQuery
(
	@matricola NVARCHAR(50),
	@CF NVARCHAR(16),
	@Cognome NVARCHAR(50),
	@Nome NVARCHAR(50),
	@dataNascita datetime,
	@luogoNascita NVARCHAR(50),
	@provNascita NVARCHAR(50),
	@nazNascita NVARCHAR(50),
	@email NVARCHAR(250),
	@dominio NVARCHAR(50),
	@utente NVARCHAR(50),
	@codOrario NVARCHAR(50),
	@attivo BIT,
	@Original_idxDipendente int
)
AS
	SET NOCOUNT OFF;
UPDATE       Dipendenti
SET          matricola = @matricola, CF = @CF, Cognome = @Cognome, Nome = @Nome, dataNascita = @dataNascita, luogoNascita = @luogoNascita, 
             provNascita = UPPER(@provNascita), nazNascita = UPPER(@nazNascita), email = @email, dominio = @dominio, utente = @utente
             ,codOrario = @codOrario, attivo = @attivo
WHERE        (idxDipendente = @Original_idxDipendente);
	 
SELECT * FROM Dipendenti WHERE (idxDipendente = @Original_idxDipendente)
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*******************************************************************************
* STORED PROCEDURE stp_TE_doChkFun
*
* effettua check finali sulla riga di timbrature esplore indicata, contiene varie logiche
* anche da + installazioni clienti
*
* mod: S.E.L. 2013.03.28
*
*******************************************************************************/
create PROCEDURE stp_TE_doChkFun
(
	@idxDipendente	INT,
	@dataRif		DATETIME
)
AS
SET NOCOUNT ON;


-- recupero tipo di fuction da usare
DECLARE @chkFun NVARCHAR(50) = ''
DECLARE @minPP      INT = 0

SELECT  @chkFun = dbo.f_chkFunDip(@idxDipendente)
-- controllo tipo di funzione... e imposto risultato!
IF(@chkFun = 'PP60m')
    BEGIN
        SET @minPP  = 60
        
        UPDATE	TimbratureExpl
        SET		chkFunCod = 'P',
                chkFunRes = 'Mancata pausa pranzo 60m'
        WHERE   idxDipendente = @idxDipendente AND dataLav = @dataRif
                AND (minArcoPres - minLav) < @minPP -- controllo pause > PP imposta
                AND (minOrd > 0 AND minLav > 0) -- controllo DEBBA lavorare e ABBIA lavorato
    END
ELSE IF(@chkFun = 'PP30m')
    BEGIN
        SET @minPP  = 30
        
        UPDATE	TimbratureExpl
        SET		chkFunCod = 'P',
                chkFunRes = 'Mancata pausa pranzo 30m'
        WHERE   idxDipendente = @idxDipendente AND dataLav = @dataRif
                AND (minArcoPres - minLav) < @minPP -- controllo pause > PP imposta
                AND (minOrd > 0 AND minLav > 0) -- controllo DEBBA lavorare e ABBIA lavorato
    END

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*******************************************************************************
* stp_ricalcolaTimbratureExpl
*
* Aggiorna la tab timbrature expl ricalcolando la giornata del record interessato
* mod:	S.E.L. - 2012.02.06
********************************************************************************/
alter PROCEDURE stp_ricalcolaTimbratureExpl
(
	@idxDipendente AS INT,
	@dataRif AS DATETIME
)
AS

-- variabili
DECLARE	@minArr INT
SELECT  @minArr=dbo.f_minArrotDip(@idxDipendente)

-- CONTROLLO SE IL RECORD NON SIA BLOCCATO!!!
DECLARE	@block	BIT
SET		@block = ( SELECT ISNULL(( SELECT block FROM TimbratureExpl WHERE    (idxDipendente = @idxDipendente) AND CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif) ),0) )
IF(@block = 0)
BEGIN
	-- elimino i periodi della data indicata...
	DELETE FROM TimbratureExpl
	WHERE    (idxDipendente = @idxDipendente) AND CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)

	-- inserisco i dati minimi per la data indicata
	INSERT INTO TimbratureExpl(idxDipendente, dataLav)
	VALUES(@idxDipendente, CONVERT(DATE,@dataRif))

	-- aggiorno cognome nome
	BEGIN TRY
		UPDATE	te
		SET		CognomeNome = ISNULL(vcn.CognomeNome,'')
		FROM	TimbratureExpl te INNER JOIN v_cognomeNome vcn ON te.idxDipendente = vcn.idxDipendente
		WHERE	CONVERT(DATE,te.dataLav) = CONVERT(DATE,@dataRif) AND te.idxDipendente = @idxDipendente
	END TRY
	BEGIN CATCH
		-- non faccio nulla
	END CATCH

	-- aggiorno 1° timbratura
	UPDATE	te
	SET		te.entrata_1 = pl.entrata,
			te.uscita_1 = pl.uscita
	FROM	TimbratureExpl te INNER JOIN PeriodiLav pl ON
			te.idxDipendente = pl.idxDipendente AND 
			(CONVERT(DATE,te.dataLav) = CONVERT(DATE,pl.entrata))
	WHERE	pl.rowNum = 1 AND te.idxDipendente = @idxDipendente AND CONVERT(DATE,pl.entrata) = CONVERT(DATE,@dataRif)

	-- aggiorno 2° timbratura
	UPDATE	te
	SET		te.entrata_2 = pl.entrata,
			te.uscita_2 = pl.uscita
	FROM	TimbratureExpl te INNER JOIN PeriodiLav pl ON te.idxDipendente = pl.idxDipendente 
			AND (CONVERT(DATE,te.dataLav) = CONVERT(DATE,pl.entrata))
	WHERE	pl.rowNum = 2 AND te.idxDipendente = @idxDipendente AND CONVERT(DATE,pl.entrata) = CONVERT(DATE,@dataRif)

	-- aggiorno 3° timbratura
	UPDATE	te
	SET		te.entrata_3 = pl.entrata,
			te.uscita_3 = pl.uscita
	FROM	TimbratureExpl te INNER JOIN PeriodiLav pl ON te.idxDipendente = pl.idxDipendente 
			AND (CONVERT(DATE,te.dataLav) =CONVERT(DATE,pl.entrata))
	WHERE	pl.rowNum = 3 AND te.idxDipendente = @idxDipendente AND CONVERT(DATE,pl.entrata) = CONVERT(DATE,@dataRif)

	-- aggiorno 4° timbratura
	UPDATE	te
	SET		te.entrata_4 = pl.entrata,
			te.uscita_4 = pl.uscita
	FROM	TimbratureExpl te INNER JOIN PeriodiLav pl ON te.idxDipendente = pl.idxDipendente 
			AND (CONVERT(DATE,te.dataLav) =CONVERT(DATE,pl.entrata))
	WHERE	pl.rowNum = 4 AND te.idxDipendente = @idxDipendente AND CONVERT(DATE,pl.entrata) = CONVERT(DATE,@dataRif)

	-- calcolo ore totali...
	UPDATE	TimbratureExpl
	SET		h_lav = CAST(FLOOR((ISNULL(DATEDIFF(n,entrata_1,uscita_1),0) + ISNULL(DATEDIFF(n,entrata_2,uscita_2),0) + ISNULL(DATEDIFF(n,entrata_3,uscita_3),0) + ISNULL(DATEDIFF(n,entrata_4,uscita_4),0))/@minArr) AS FLOAT)*@minArr/60
	FROM	TimbratureExpl 
	WHERE	(CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente

	-- verifico: se c'č anche solo 1 timbratura non approvata metto flag isOkApp a falso
	UPDATE	TimbratureExpl
	SET		isOkApp = ISNULL((
		SELECT MIN(CASE WHEN Approv=0 THEN 0 ELSE 1 END) AS isOk 
		FROM	Timbrature
		WHERE	idxDipendente = @idxDipendente
		AND		(CONVERT(DATE,dataOra) = CONVERT(DATE,@dataRif))
		GROUP BY CONVERT(DATE,dataOra)
	),1)
	FROM	TimbratureExpl
	WHERE	(CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente

	-- verifico: se entrate <> uscite flag isOkTim a falso
	UPDATE	TimbratureExpl
	SET		isOkTim = (
		SELECT CASE WHEN 
		ISNULL(
			(SELECT COUNT(*) as timb
			 FROM	Timbrature AS t
			 WHERE	t.idxDipendente = @idxDipendente
					AND		(CONVERT(DATE,dataOra) = CONVERT(DATE,@dataRif))
			 GROUP BY t.idxDipendente , CONVERT(DATE,t.dataOra)
			 HAVING COUNT ( CASE Entrata WHEN 1 THEN dataOra END ) <> COUNT ( CASE Entrata WHEN 0 THEN dataOra END )
			),0) > 0 THEN 0 ELSE 1 END
	)
	FROM	TimbratureExpl
	WHERE	(CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente

	-- calcolo resoconto MINUTI giornaliero, imposto LUN x inizio settimana
	SET DATEFIRST 1

	-- MIN lavorati, ordinari (con arrotondamento...)
	UPDATE	TimbratureExpl
	SET		minLav		= CAST((ISNULL(DATEDIFF(n,entrata_1,uscita_1),0) + ISNULL(DATEDIFF(n,entrata_2,uscita_2),0) + ISNULL(DATEDIFF(n,entrata_3,uscita_3),0) + ISNULL(DATEDIFF(n,entrata_4,uscita_4),0)) AS FLOAT)
		   ,minOrd		= dbo.f_oreOrdDip(idxDipendente,dataLav) * 60 -- da schema orario dip attivo		   
	FROM	TimbratureExpl
	WHERE	(CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente

	-- MIN non lavorati, straordinari (con arrotondamento...)
	UPDATE	TimbratureExpl
	SET		minNonLav	= (CEILING(CONVERT(FLOAT,CASE WHEN minOrd <= minLav THEN (0) ELSE minOrd-minLav END)/@minArr)*@minArr)
		   ,minStra		= (FLOOR(CONVERT(FLOAT,CASE WHEN minOrd < minLav AND minLav > (0) THEN minLav-minOrd ELSE (0) END)/@minArr)*@minArr)
	FROM	TimbratureExpl
	WHERE	(CONVERT(DATE,dataLav) = CONVERT(DATE,@dataRif)) AND idxDipendente = @idxDipendente


	-- MIN da tab giustificativi (feste, ferie, malattia, permessi)
	UPDATE	TimbratureExpl
	SET		minMal	    = CASE WHEN g.codGiust='MAL'	THEN g.minuti ELSE minMal	END,
			minFer	    = CASE WHEN g.codGiust='FER'	THEN g.minuti ELSE minFer	END,
			minFest	    = CASE WHEN g.codGiust='FEST'	THEN g.minuti ELSE minFest	END,
			minPerm	    = CASE WHEN g.codGiust='PERM'	THEN g.minuti ELSE minPerm	END,
            chkFunCod   = '',
            chkFunRes   = ''
	FROM	TimbratureExpl te INNER JOIN Giustificativi g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav
	WHERE	(CONVERT(DATE,te.dataLav) = CONVERT(DATE,@dataRif)) AND te.idxDipendente = @idxDipendente

    -- verifico SE codice orario richieda una chkFun eseguo calcolo...    
    IF(dbo.f_chkFunDip(@idxDipendente)<>'')
    BEGIN 
        EXEC stp_TE_doChkFun @idxDipendente, @dataRif
    END
    
END

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_lcuByDipDate
*
* elenco dati COMPLETI di timbratura e commessa per utente/periodo
*
* mod: S.E.L. 2012.11.02
*
**********************************************************/
alter PROCEDURE stp_TE_RA_ByUserDate
(
	@idxDipendente	INT,
	@dataFrom		DATETIME,
	@dataTo			DATETIME,
	@showWE			BIT = 1, -- imposto visualizzazione week-end a true di default
	@maxErrMin		INT =  0, -- minuti massimi di scostamento consentiti per DIFETTO, default 0
	@maxErrPlus		INT =  0  -- minuti massimi di scostamento consentiti per ECCESSO, default 0
)
with recompile
AS

BEGIN

	-- imposto LUN x inizio settimana
	SET DATEFIRST 1

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

	-- tab dei record RegAttivitā progetti
	;WITH myCteRA AS
		(
			SELECT			idxDipendente, dataLav, minRegAtt, descrProj
				FROM        dbo.RegAttivitaExpl
				WHERE		(idxDipendente = @idxDipendente OR @idxDipendente = 0) AND (dataLav >= DATEADD(DAY,-1,@dataFrom) AND dataLav <= @dataTo)
		)

	SELECT	mc.Data as dataLav
		   ,mc.idxDipendente
		   ,ISNULL(dip.Cognome + ' ' + dip.Nome,'') AS CognomeNome
		   -- ore base + progetti
		   ,ISNULL(te.h_lav,0) AS h_lav
		   ,ISNULL(te.h_giust,0) AS h_giust
		   ,CAST(ISNULL(vra.minRegAtt,0) AS DECIMAL(9,3))/60 AS h_com
		   ,ISNULL(vra.descrProj,'') as progetti
		   -- check coerenza
		   ,CASE WHEN ISNULL(vra.minRegAtt,0)-ISNULL(te.h_lav,0)*60 >= @maxErrMin AND ISNULL(vra.minRegAtt,0)-ISNULL(te.h_lav,0)*60 <= @maxErrPlus THEN 1 ELSE 0 END AS okLavCom
		   ,ISNULL(te.isOkTim,0) AS isOkTim
		   ,ISNULL(te.isOkApp,0) AS isOkApp
		   ,ISNULL(te.isOkLav,0) AS isOkLav
		   ,ISNULL(te.isOk,0) AS isOk
		   -- check blocco
		   ,ISNULL(te.block,0) AS block
		   -- dettaglio minuti 		   
		   ,ISNULL(te.minOrd,0) AS minOrd
		   ,ISNULL(te.minNonLav,0) AS minNonLav
		   ,ISNULL(te.minStra,0) AS minStra
		   ,ISNULL(te.minPerm,0) AS minPerm
		   ,ISNULL(te.minFer,0) AS minFer
		   ,ISNULL(te.minMal,0) AS minMal
		   ,ISNULL(te.minFest,0) AS minFest
		   ,ISNULL(te.minArcoPres,0) AS minArcoPres
		   ,ISNULL(vra.minRegAtt,0) AS minRegAtt
		   -- dettaglio entrate
		   , te.entrata_1, te.uscita_1
		   , te.entrata_2, te.uscita_2
		   , te.entrata_3, te.uscita_3
		   , te.entrata_4, te.uscita_4
           -- dati check
		   , te.chkFunCod, te.chkFunRes

	FROM	(SELECT	CONVERT(DATE,DATEADD(dd, -N, @dataTo)) as Data, idxDipendente
		FROM	Tally, Dipendenti
		WHERE	N <= @numD
		AND		(idxDipendente = @idxDipendente OR @idxDipendente = 0) 
		) AS mc 
		LEFT OUTER JOIN myCteRA vra ON mc.Data = vra.dataLav AND mc.idxDipendente=vra.idxDipendente
		LEFT OUTER JOIN TimbratureExpl te ON mc.Data=te.dataLav AND te.idxDipendente=mc.idxDipendente
        LEFT OUTER JOIN Dipendenti dip ON dip.idxDipendente=mc.idxDipendente
	WHERE	(te.idxDipendente = @idxDipendente OR @idxDipendente = 0)
	AND		DATEPART(dw, mc.Data) <= CASE WHEN @showWE=0 THEN 5 ELSE 7 END --solo lun-ven, 1-5
	ORDER BY mc.Data DESC, dip.Cognome, dip.Nome

END

RETURN
go

commit;
go






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