create table ElencoReport(
  IdxRep     int           not null identity constraint PK_ElencoReport primary key,
  ReportName nvarchar(50),
  ReportUrl  nvarchar(500),
  imgUrl     nvarchar(50)
);
go


set xact_abort on
go

begin transaction
go

set identity_insert dbo.ElencoReport on
go

INSERT INTO dbo.ElencoReport
  (IdxRep, ReportName, ReportUrl, imgUrl)
  VALUES (1, N'Report Ore: Cliente - Progetto - Utente', N'GPW+-+Report+Presenze%2fGPW+-+Ore+per+Cliente-Progetto-Utente', NULL)
INSERT INTO dbo.ElencoReport
  (IdxRep, ReportName, ReportUrl, imgUrl)
  VALUES (2, N'Report Completo: Elenco Ore Attivita Progetti', N'GPW+-+Report+Presenze%2fGPW+-+Elenco+Ore+Attivita+Progetti', NULL)
go

set identity_insert dbo.ElencoReport off
go

commit transaction
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
*
**********************************************************/
alter 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) 
    --AND     NOT (entrata_1 IS NULL)
    ORDER BY CognomeNome, dataLav
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_AP_Expl_getData
*
* recupera elenco progetti con filtraggio ore totali per 
* - dipendente
* - periodo
*
* mod: S.E.L. 2013.01.30
*
**********************************************************/
alter PROCEDURE stp_AP_Expl_getData
(
	@idxDipendente	INT	= 0,    -- 0 = tutti
	@dataFrom		DATETIME = '19000101', 
	@dataTo			DATETIME = '99991231',    
	@idxCliente		INT = 0,    -- 0 = tutti
	@showPrjArch	BIT = 1,     -- 1 = mostra tutti, 0 = nasconde archiviati
	@showPrjZeroH	BIT = 1     -- 1 = mostra tutti, 0 = nasconde se zero ore caricate
)
AS

-- 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 cteOreReal AS
(
SELECT  ap.idxProgetto, SUM(ISNULL(ra.oreTot, 0)) AS totOre
FROM    AnagProgetti AS ap INNER JOIN
		AnagClienti AS ac ON ac.idxCliente=ap.idxCliente INNER JOIN
        AnagFasi AS af ON ap.idxProgetto = af.idxProgetto 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 (ac.idxCliente = @idxCliente OR @idxCliente = 0)
        AND (ap.Attivo = CASE WHEN @showPrjArch <> 0 THEN ap.Attivo ELSE 1 END)
GROUP BY ap.idxProgetto
)
,cteOreBudget as
(
SELECT 
	 idxProgetto
	,SUM(ISNULL(budgetTime,0)) as budgetTime
	,SUM(ISNULL(budgetMoney,0)) as budgetMoney
FROM AnagFasi
GROUP BY idxProgetto
)

SELECT  ac.RagSociale
		, ap.idxProgetto
		, ap.idxCliente
		, ap.nomeProj
		, ap.descrProj
		, ISNULL(bdgt.budgetTime,0) AS budgetTime
		, ISNULL(bdgt.budgetMoney,0) AS budgetMoney
		, ISNULL(ap.OldIdx,-1) AS OldIdx
		, ap.Attivo
		, ISNULL(ap.codExt,'') AS codExt
		, ISNULL(cte.totOre, 0) AS totOre
        , ISNULL(ap.avvio,'19000101') AS avvio
        , ISNULL(ap.chiusura,'99991231') AS chiusura
FROM    AnagClienti AS ac INNER JOIN
        AnagProgetti AS ap ON ac.idxCliente = ap.idxCliente LEFT OUTER JOIN
        cteOreBudget AS bdgt ON ap.idxProgetto = bdgt.idxProgetto LEFT OUTER JOIN
		cteOreReal AS cte ON cte.idxProgetto = ap.idxProgetto
WHERE   (ac.idxCliente = @idxCliente OR @idxCliente = 0)
        AND (ap.Attivo = CASE WHEN @showPrjArch <> 0 THEN ap.Attivo ELSE 1 END)
        AND (totOre > 0 OR @showPrjZeroH = 1)
ORDER BY ac.RagSociale, ap.nomeProj
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/**********************************************************
* STORED stp_RA_clonaLastRA_byFaseUtente
*
* clona un attivitā utente: ultima di un dato tipo di fase
*
* mod: S.E.L. 2013.02.20
*
**********************************************************/
alter PROCEDURE stp_RA_clonaLastRA_byFaseUtente
(
	 @idxDipendente	INT
    ,@dataRif       DATETIME -- data x cui creare un record attivitā
    ,@idxFase       INT
    ,@minuti        INT = 0 -- se 0 fa last value
)
AS

-- variabili
DECLARE @idxRA		INT
DECLARE @inizio		DATETIME

-- calcolo fine ultima attivitā del giorno x l'utente (se c'č...)
SELECT	TOP 1 @inizio = ISNULL(fine, @dataRif)
FROM	RegAttivita
WHERE	idxDipendente = @idxDipendente
AND		CONVERT(DATE,fine) = @dataRif
ORDER BY fine DESC


DECLARE @round AS INT
SET		@round = ( SELECT valInt FROM AnagKeyValue WHERE nomeVar = 'minutiRoundDay' )

-- arrotondo data richiesta...
SELECT  @dataRif = DATEADD(n, (ROUND(dbo.TimeSpanUnits('m', dbo.TimeOnly(DATEADD(n,-1,@dataRif))) / @round, 2) + 1) * @round , CAST(CAST(@dataRif AS DATE) AS DATETIME))

-- controlla che x la data richiesta non ci siano giā altre attivitā, in quel caso "accoda"...
DECLARE @trovate INT = 0
SELECT  @trovate = COUNT(idxRA) FROM RegAttivita WHERE	idxDipendente = @idxDipendente AND CAST(@dataRif AS DATE) = CAST(inizio AS DATE)

IF @trovate > 0
BEGIN
    -- imposto @dataRif per iniziare alla fine dell'ultimo record
    SELECT TOP 1 @dataRif=fine FROM	RegAttivita WHERE	idxDipendente = @idxDipendente AND CAST(@dataRif AS DATE) = CAST(inizio AS DATE) ORDER BY fine DESC
END

-- cerco ultima reg attivitā utente x fase indicata...
SELECT @idxRA=ISNULL((SELECT TOP 1 idxRA FROM	RegAttivita WHERE	idxDipendente = @idxDipendente AND idxFase = @idxFase ORDER BY inizio DESC),0) 

-- inserisco su data richeista duplicazione dell'ultima attivitā utente...
INSERT INTO RegAttivita(idxDipendente, idxFase, inizio, fine, descrizione, importo)    
SELECT      idxDipendente, idxFase, @dataRif, DATEADD(minute, CASE WHEN @minuti = 0 THEN DATEDIFF(MINUTE, inizio, fine) ELSE @minuti END, @dataRif), descrizione, importo
FROM        RegAttivita
WHERE       (idxRA = @idxRA)
go

commit;
go


set ANSI_NULLS on;
go

/**********************************************************
* FUNCTION f_dataOraITA
*
* formatta data ora in modo che sia compatibilit con excel ITA
*
**********************************************************/
create FUNCTION f_dataOraITA
(
    @valore DATETIME
)
RETURNS NVARCHAR(50)
AS
BEGIN
	RETURN CONVERT(NVARCHAR(50), @valore, 120)
END
go

/**********************************************************
* FUNCTION f_floatITA
*
* formatta data ora in modo che sia compatibilita con excel ITA
*
**********************************************************/
create FUNCTION f_floatITA
(
    @valore FLOAT
)
RETURNS NVARCHAR(50)
AS
BEGIN
	RETURN REPLACE(CONVERT(NVARCHAR(50), @valore),'.',',')
END
go











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