alter table AnagKeyValue add
  descrizione nvarchar(250);
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*----------------------------------
* Trigger su insert/update/delete
*
* Aggiorna giustificativi nella tab TimbratureExpl
*
* creato da:		S.E. Locatelli - 2012.12.04
* ultima modifica:	
*----------------------------------*/
alter TRIGGER trg_giustUpdateTimbr
ON Giustificativi
FOR INSERT, UPDATE
AS

-- controllo se rescord esista altrimenti lo creo!!!
DECLARE @trovate INT = 0
DECLARE @idxDipendente INT
DECLARE @dataRif DATETIME
SET     @trovate = ISNULL( ( SELECT COUNT(*) FROM	TimbratureExpl te INNER JOIN inserted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav) ,0)
IF(@trovate = 0)
BEGIN
    SELECT  @idxDipendente = idxDipendente, @dataRif=dataLav FROM inserted
    EXEC stp_ricalcolaTimbratureExpl @idxDipendente, @dataRif
END


-- CONTROLLO SE IL RECORD NON SIA BLOCCATO!!!
DECLARE	@block	BIT
SET		@block = ( SELECT te.block FROM	TimbratureExpl te INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav )
IF(@block = 0)
BEGIN
	-- prendo tutte le "vecchie" righe (deleted) e x le giornate relative TOLGO i minuti dei giustificativi
	UPDATE	TimbratureExpl
	SET		minMal	= CASE WHEN g.codGiust='MAL'	THEN 0 ELSE minMal	END,
			minFer	= CASE WHEN g.codGiust='FER'	THEN 0 ELSE minFer	END,
			minFest	= CASE WHEN g.codGiust='FEST'	THEN 0 ELSE minFest END,
			minPerm	= CASE WHEN g.codGiust='PERM'	THEN 0 ELSE minPerm END
	FROM	TimbratureExpl te INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav

END

SET		@block = ( SELECT te.block FROM	TimbratureExpl te INNER JOIN inserted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav )
IF(@block = 0)
BEGIN
	-- prendo le "nuove" righe (inserted) e x le giornate relative AGGIUNGO i minuti dei giustificativi 
	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
	FROM	TimbratureExpl te INNER JOIN inserted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav

END
go

/*----------------------------------
* Trigger su insert/update/delete
*
* Aggiorna giustificativi nella tab TimbratureExpl
*
* creato da:		S.E. Locatelli - 2012.12.04
* ultima modifica:	
*----------------------------------*/
create TRIGGER trg_giustDelUpdTimbr
ON Giustificativi
FOR DELETE
AS

-- controllo se rescord esista altrimenti lo creo!!!
DECLARE @trovate INT = 0
DECLARE @idxDipendente INT
DECLARE @dataRif DATETIME

-- CONTROLLO SE IL RECORD NON SIA BLOCCATO!!!
DECLARE	@block	BIT
SET		@block = ( SELECT te.block FROM	TimbratureExpl te INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav )
IF(@block = 0)
BEGIN
	-- prendo tutte le "vecchie" righe (deleted) e x le giornate relative TOLGO i minuti dei giustificativi
	UPDATE	TimbratureExpl
	SET		minMal	= CASE WHEN g.codGiust='MAL'	THEN 0 ELSE minMal	END,
			minFer	= CASE WHEN g.codGiust='FER'	THEN 0 ELSE minFer	END,
			minFest	= CASE WHEN g.codGiust='FEST'	THEN 0 ELSE minFest END,
			minPerm	= CASE WHEN g.codGiust='PERM'	THEN 0 ELSE minPerm END
	FROM	TimbratureExpl te INNER JOIN deleted g ON te.idxDipendente = g.idxDipendente AND te.dataLav = g.dataLav

END
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

alter VIEW v_selFasi
AS
SELECT        idxFase AS value, nomeFase AS label, idxProgetto AS conditio, enableTime, codFase, idxFase * enableTime AS valueGroup
FROM            dbo.AnagFasi
go

exec sp_updateextendedproperty '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 = "AnagFasi"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 284
               Right = 209
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 11985
         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_selFasi';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

alter VIEW v_selProgetti
AS
SELECT        dbo.AnagProgetti.idxProgetto AS value, dbo.AnagClienti.RagSociale + ' - ' + dbo.AnagProgetti.nomeProj AS label, dbo.AnagClienti.idxCliente AS conditio, 
                         dbo.AnagProgetti.Attivo
FROM            dbo.AnagClienti INNER JOIN
                         dbo.AnagProgetti ON dbo.AnagClienti.idxCliente = dbo.AnagProgetti.idxCliente
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_AF_getByIdxProj
*
* elenco fasi da progetto
*
* mod: S.E.L. 2012.10.31
*
**********************************************************/
alter PROCEDURE stp_AF_getByIdxProj
(
	@idxProgetto	INT
)
AS
	SET NOCOUNT ON;
/*
SELECT        idxFase, idxProgetto, codFase, idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo, codClasse, codExt
FROM            AnagFasi
WHERE        (idxProgetto = @idxProgetto)
ORDER BY codFase
*/

-- dichiaro variabili "accessorie"
DECLARE @idxDipendente	INT	= 0    -- 0 = tutti
DECLARE @dataFrom		DATETIME = '19000101' 
DECLARE @dataTo			DATETIME = '99991231'

-- controllo se date nulle importo a min/max
DECLARE @firstDate DATETIME
DECLARE @lastDate DATETIME

SELECT @firstDate = MIN(inizio), @lastDate=DATEADD(DAY,1,MAX(inizio)) FROM RegAttivita
SELECT @dataFrom = ISNULL(@dataFrom, @firstDate), @dataTo=ISNULL(@dataTo, @lastDate) 


;WITH myCTE AS
(
SELECT  af.idxFase, SUM(ISNULL(ra.oreTot, 0)) AS totOre
FROM    AnagFasi AS af INNER JOIN
        RegAttivita AS ra ON af.idxFase = ra.idxFase
WHERE   (ra.idxDipendente = @idxDipendente OR @idxDipendente = 0)
        AND (ra.inizio >= @dataFrom AND ra.inizio  <= @dataTo)
        AND (af.idxProgetto = @idxProgetto OR @idxProgetto = 0)
GROUP BY af.idxFase
)
SELECT  af.idxFase 
		, af.idxProgetto
		, af.codFase
		, af.idxFaseAncest
		, af.nomeFase
		, af.descrizioneFase
		, af.enableTime
		, af.enableMoney
		, af.Attivo
		, ISNULL(af.budgetTime,0) AS budgetTime
		, ISNULL(af.budgetMoney,0) AS budgetMoney
		, ISNULL(af.codClasse,'') AS codClasse
		, ISNULL(af.codExt, '') AS codExt
		, ISNULL(cte.totOre, 0) AS totOre
FROM    AnagFasi AS af LEFT OUTER JOIN
		myCte AS cte ON cte.idxFase = af.idxFase
WHERE   (af.idxProgetto = @idxProgetto OR @idxProgetto = 0)
ORDER BY af.codFase, af.nomeFase
go

/**********************************************************
* STORED stp_AF_InsertQuery
*
* elenco fasi da progetto
*
* mod: S.E.L. 2012.11.02
*
**********************************************************/
alter PROCEDURE stp_AF_InsertQuery
(
	@idxProgetto INT,
	@idxFaseAncest INT,
	@nomeFase NVARCHAR(50),
	@descrizioneFase NVARCHAR(250),
	@enableTime BIT,
	@enableMoney BIT,
	@budgetTime decimal(19, 4),
	@budgetMoney decimal(19, 4),
	@Attivo BIT
)
AS
	SET NOCOUNT OFF;
INSERT INTO AnagFasi ( codFase, idxProgetto, idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo, budgetTime, budgetMoney)
VALUES ('', @idxProgetto, @idxFaseAncest, @nomeFase, @descrizioneFase, @enableTime, @enableMoney, @Attivo, @budgetTime, @budgetMoney);
	
SELECT idxFase, idxProgetto, codFase, idxFaseAncest, nomeFase, descrizioneFase, enableTime, enableMoney, Attivo FROM AnagFasi WHERE (idxFase = SCOPE_IDENTITY())
go

/**********************************************************
* STORED stp_AF_getByIdxProj
*
* elenco fasi da progetto
*
* mod: S.E.L. 2012.11.02
*
**********************************************************/
alter PROCEDURE stp_AF_updateQuery
(
	@nomeFase nvarchar(50),
	@descrizioneFase nvarchar(250),
	@enableTime bit,
	@enableMoney bit,
	@Attivo int,
	@budgetTime     DECIMAL(19, 4),
	@budgetMoney    DECIMAL(19, 4),
	@Original_idxFase int
)
AS
	SET NOCOUNT OFF;
UPDATE	AnagFasi 
SET		nomeFase = @nomeFase, 
		descrizioneFase = @descrizioneFase,
		enableTime = @enableTime,
		enableMoney = @enableMoney,
        budgetTime = @budgetTime, 
		budgetMoney = @budgetMoney,
		Attivo = @Attivo 
WHERE	idxFase = @Original_idxFase;
	
SELECT * FROM AnagFasi WHERE idxFase = @Original_idxFase
go

/*----------------------------------
* STORED PROCEDURE stp_giust_insByDate
*
* Aggiunge un giustificativo per coprire la durata necessaria a chiudere la giornata indicata
* mod:	S.E.L. - 2012.12.10
*----------------------------------*/
alter PROCEDURE stp_giust_insByDate
(
	@idxDipendente	INT = 0,
	@dataRif		DATETIME,
	@codGiust		NVARCHAR(5) = 'PERM'		
)
AS

BEGIN TRAN
	--min necessari x chiudere giornata
	DECLARE @minPerm	INT = 0
		
	-- 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)
	FROM	TimbratureExpl
	WHERE	isOkLav = 0
		AND idxDipendente	= @idxDipendente
		AND dataLav			= @dataRif 

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

RETURN
go

/**********************************************************
* STORED stp_RAD_Expl_getByIdxDipData
*
* recupera elenco attivitą (con Expl dei dati da anagrafica) da idxDipendente + dataRif
*
* mod: S.E.L. 2013.01.23
*
**********************************************************/
alter PROCEDURE stp_RAD_Expl_getByIdxDipData
(
	@idxDipendente	INT,
	@dataRif		DATETIME
)
AS

SELECT  d.Cognome + ' ' + d.Nome AS CognomeNome, ra.idxDipendente, ra.inizio, ra.fine
        , CASE WHEN ISNULL(descrizione, '') = '' THEN '-' ELSE ISNULL(descrizione, '') END AS descrizione
        , ra.oreTot, ra.oreTot * 60 AS minTot, ra.importo, ac.RagSociale, ap.nomeProj, af.nomeFase, ra.idxRA
FROM    RegAttivita ra INNER JOIN
        AnagFasi af ON ra.idxFase = af.idxFase INNER JOIN
        AnagProgetti ap ON af.idxProgetto = ap.idxProgetto INNER JOIN
        AnagClienti ac ON ap.idxCliente = ac.idxCliente INNER JOIN
        Dipendenti d ON ra.idxDipendente = d.idxDipendente
WHERE   (ra.idxDipendente = @idxDipendente OR @idxDipendente = 0) AND (CAST(ra.inizio AS DATE) = CAST(@dataRif AS DATE) OR
        CAST(ra.fine AS DATE) = CAST(@dataRif AS DATE))
ORDER BY ra.inizio
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_ReportOreDip
*
* elenco ore dipendenti
*
* mod: S.E.L. 2013.02.04
*
**********************************************************/
create PROCEDURE export.stp_ReportOreDip
(
	@idxDipendente	INT = 0, -- 0 = tutti
	@dataFrom		DATETIME,
	@dataTo		    DATETIME
)
AS
	SET NOCOUNT OFF;
    DECLARE @minArr INT
    
    SELECT @minArr = ISNULL(valInt,1) FROM AnagKeyValue WHERE nomeVar = 'minutiRoundDay'
     
    -- elenco mensile...
    SELECT  dataLav, CognomeNome, 
            entrata_1, uscita_1, entrata_2, uscita_2, 
            entrata_3, uscita_3, entrata_4, uscita_4, 
            FLOOR(CAST(h_lav AS DECIMAL(9,3))*60/@minArr)/(60/@minArr) AS h_lavorate, 
            FLOOR(CAST(minStra AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_straordinarie,
			CEILING(CAST(minPerm AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_permessi, 
            CEILING(CAST(minFer AS DECIMAL(9,3))/@minArr)/(60/@minArr)  AS h_ferie, 
			CEILING(CAST(minMal AS DECIMAL(9,3))/@minArr)/(60/@minArr)  AS h_malattia, 
			CEILING(CAST(minFest AS DECIMAL(9,3))/@minArr)/(60/@minArr) AS h_festivita
    FROM    TimbratureExpl
    WHERE	(idxDipendente = @idxDipendente OR @idxDipendente = 0)
    AND     dataLav >= CAST(@dataFrom AS DATE) 
    AND     dataLav <= CAST(@dataTo AS DATE) 
    ORDER BY CognomeNome, dataLav
go

/**********************************************************
* STORED stp_RAD_Expl_getByIdxDipPeriodo
*
* recupera elenco attivitą (con Expl dei dati da anagrafica) da idxDipendente + periodo + opzionali cliente e progetto
*
* mod: S.E.L. 2013.02.01
*
**********************************************************/
create PROCEDURE stp_RAD_Expl_getByIdxDipPeriodo
(
	@idxDipendente	INT,
	@dataFrom		DATETIME,
	@dataTo		    DATETIME,
    @idxCliente     INT = 0, -- 0 = tutti
    @idxProgetto    INT = 0,  -- 0 = tutti
    @soloAncest     BIT = 0  -- 0 = tutti
)
AS

SELECT  d.Cognome + ' ' + d.Nome AS CognomeNome, ra.idxDipendente, ra.inizio, ra.fine
        , CASE WHEN ISNULL(descrizione, '') = '' THEN '-' ELSE ISNULL(descrizione, '') END AS descrizione
        , ra.oreTot, ra.oreTot * 60 AS minTot, ra.importo, ac.RagSociale, ap.nomeProj, 
        af.nomeFase, ra.idxRA
FROM    RegAttivita ra INNER JOIN
        AnagFasi af ON ra.idxFase = af.idxFase INNER JOIN
        AnagProgetti ap ON af.idxProgetto = ap.idxProgetto INNER JOIN
        AnagClienti ac ON ap.idxCliente = ac.idxCliente INNER JOIN
        Dipendenti d ON ra.idxDipendente = d.idxDipendente
WHERE   (ra.idxDipendente = @idxDipendente OR @idxDipendente=0) 
        AND (ra.inizio >= CAST(@dataFrom AS DATE) AND ra.inizio <=  CAST(@dataTo AS DATE))
        AND (ac.idxCliente = @idxCliente OR @idxCliente <= 0)
        AND (ap.idxProgetto = @idxProgetto OR @idxProgetto <= 0)
        AND (af.idxFaseAncest = CASE WHEN @soloAncest <> 0 THEN 0 ELSE af.idxFaseAncest END )
ORDER BY ra.inizio
go

/**********************************************************
* STORED stp_RA_updateFase
*
* update fase x record selezionato
*
* mod: S.E.L. 2013.02.01
*
**********************************************************/
create PROCEDURE stp_RA_updateFase
(
	@idxFase        int,
	@Original_idxRA int
)
AS
	SET NOCOUNT OFF;
UPDATE  RegAttivita
SET     idxFase = @idxFase
WHERE        (idxRA = @Original_idxRA);
go

/*************************************
* STORED PROCEDURE stp_VSFasiOpt_ByPrj
*
*  elenco fasi dato progetto con calmpo valore a zero se fase "ancestor"
*
* mod : 2013.02.01
* aut : S.E. Locatelli
**************************************/
create PROCEDURE stp_VSFasiOpt_ByPrj
(
	@conditio	INT = 0
    ,@soloAttivi BIT = 0 -- 0 = tutti, 1 = solo attivi x time tracking
)
AS

SELECT  value as valueGroup, label, conditio, valueGroup as value
FROM    v_selFasi
WHERE	conditio = @conditio
AND     (enableTime = @soloAttivi OR @soloAttivi = 0)
ORDER BY codFase, label


RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*************************************
* STORED PROCEDURE stp_VSFasi_ByPrj
*
*  elenco fasi dato progetto
*
* mod : 26/09/2012	
* aut : S.E. Locatelli
**************************************/
alter PROCEDURE stp_VSFasi_ByPrj
(
	@conditio	INT = 0
    ,@soloAttivi BIT = 0 -- 0 = tutti, 1 = solo attivi x time tracking
)
AS

SELECT  value, label, conditio
FROM    v_selFasi
WHERE	conditio = @conditio
AND     (enableTime = @soloAttivi OR @soloAttivi = 0)
ORDER BY codFase, label


RETURN
go

/*************************************
* STORED PROCEDURE stp_VSPrj
*
*  elenco progetti "formattato" by cliente
*
* mod : 26/09/2012	
* aut : S.E. Locatelli
**************************************/
alter PROCEDURE stp_VSPrj
(
	@soloAttivi BIT = 0 -- 0 = tutti, 1 = solo attivi
)
AS
/*
WITH myCTE AS
(
SELECT MIN(value) as value, conditio AS label, conditio
FROM	v_selProgetti
GROUP BY conditio

UNION

SELECT	*
FROM	v_selProgetti
)

SELECT		*
FROM		myCTE
ORDER BY	conditio, label
*/

SELECT	*
FROM	v_selProgetti
WHERE   (Attivo = @soloAttivi OR @soloAttivi = 0)
ORDER BY label

RETURN
go

/*************************************
* STORED PROCEDURE stp_VSPrj
*
*  elenco progetti "formattato" by cliente
*
* mod : 26/09/2012	
* aut : S.E. Locatelli
**************************************/
alter PROCEDURE stp_VSPrj_byConditio
(
	 @conditio NVARCHAR(50)
    ,@soloAttivi BIT = 0 -- 0 = tutti, 1 = solo attivi

)
AS
	SET NOCOUNT ON;
SELECT  value, label, conditio, Attivo
FROM    v_selProgetti
WHERE   (conditio = @conditio OR @conditio=0)
AND     (Attivo = @soloAttivi OR @soloAttivi = 0)
ORDER BY label
go

commit;
go








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