set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/**********************************************************
* STORED stp_baz_creaBulkByIdxFase
*
* clona in blocco i dati dalla sett precedente a quella richiesta (anno e settimana), valori a zero...
*
* mod: S.E.L. 2013.09.13
*
**********************************************************/
create PROCEDURE stp_BAZ_clonePrevWeek
(
    @Anno           INT = 0,
    @Sett           INT = 0
)
AS

-- calcolo sett precedente..

DECLARE @AnnoFrom INT = 0
DECLARE @SettFrom INT = 0

SELECT @AnnoFrom = CASE WHEN @Sett = 1 THEN @Anno - 1 ELSE @Anno END
SELECT @SettFrom = CASE WHEN @Sett = 1 THEN (SELECT ISNULL(MAX(Sett),0) FROM BazaarRisorse WHERE Anno = @Anno - 1) ELSE @Sett - 1 END

INSERT INTO BazaarRisorse(Anno,Sett,idxDipendente,idxFase,Allocazione) 
SELECT	    @Anno, @Sett, idxDipendente, idxFase, 0
FROM	    BazaarRisorse 
WHERE	    Anno = @AnnoFrom
		AND Sett = @SettFrom
		AND idxFase NOT IN	(
								SELECT DISTINCT idxFase
								FROM	    BazaarRisorse 
								WHERE	    Anno = @Anno
										AND Sett = @Sett
							)

RETURN
go

/**********************************************************
* STORED stp_BAZ_getByAnnoSettCC
*
* recupera record di Bazaar Risorse per anno, settimana e CC selezionati (SOLO del CC)
*
* mod: S.E.L. 2013.09.13
*
**********************************************************/
create PROCEDURE stp_BAZ_getByAnnoSettCC
(
    @Anno			INT,
    @Sett			INT,
    @idxDipendente	INT = 1,
	@CapoCommessa	NVARCHAR(50)
)
AS
    SET NOCOUNT ON;
SELECT	Anno, Sett, idxDipendente, idxFase, Allocazione, SchemaWeek, OreTot
FROM	BazaarRisorse
WHERE	Anno = @Anno AND Sett = @Sett AND idxDipendente = @idxDipendente
	AND	idxFase IN (SELECT DISTINCT idxFase FROM DatiCommessa WHERE CapoCommessa = @CapoCommessa)
go

commit
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/**********************************************************
* STORED stp_IR_getFullSettPareto
*
* recupera riga tabella PIENA x anno, settimana x DIPENDENTE FINTO (=0) ordinando commesse in logica pareto ore tot settimana
*
* mod: S.E.L. 2013.09.12
*
**********************************************************/
alter PROCEDURE stp_IR_getFullSettPareto
(
    @Anno           INT = 0,
    @Settimana      INT = 0
)
AS
/*
SELECT	Anno, Sett, 0 as idxDipendente, idxFase, SUM(OreTot) as OreTot
FROM	ImpiegoRisorse
WHERE	Anno = @Anno AND Sett = @Settimana
GROUP BY Anno, Sett, idxFase
ORDER BY SUM(OreTot)
*/

-- creo CTE di appoggio x generare matrice
;WITH cteFasi AS
(
	SELECT DISTINCT idxFase
	FROM	BazaarRisorse
	WHERE	Anno = @Anno AND Sett = @Settimana
)
, cteExpl AS
(
	--SELECT	    @Anno as Anno, @Sett AS Sett, 0 as idxDipendente, cte.idxFase, 0 as OreTot
	--FROM	    cteFasi cte
	--WHERE	    ISNULL(d2a.Gruppo,'') <>''
	SELECT	    @Anno as Anno, @Settimana AS Sett, cte.idxFase
	FROM	    cteFasi cte
)



SELECT	cte.Anno, cte.Sett, 0 AS idxDipendente, cte.idxFase, ISNULL(SUM(ir.OreTot),0) as OreTot
FROM	cteExpl cte LEFT OUTER JOIN ImpiegoRisorse ir ON cte.Anno = ir.Anno AND cte.Sett = ir.Sett
GROUP BY cte.Anno, cte.Sett, cte.idxFase
ORDER BY SUM(ir.OreTot)


RETURN
go

/**********************************************************
* STORED stp_IR_getFullSettParetoCC
*
* recupera riga tabella PIENA x anno, settimana x DIPENDENTE FINTO (=0) ordinando commesse in logica pareto ore tot settimana SOLO X COMMESSE DI CUI é CC
*
* mod: S.E.L. 2013.09.12
*
**********************************************************/
create PROCEDURE stp_IR_getFullSettParetoCC
(
    @Anno           INT = 0,
    @Settimana      INT = 0,
	@CapoCommessa	NVARCHAR(50)
)
AS


-- creo CTE di appoggio x generare matrice
;WITH cteFasi AS
(
	SELECT DISTINCT idxFase
	FROM	BazaarRisorse
	WHERE	Anno = @Anno AND Sett = @Settimana
		AND	idxFase IN (SELECT DISTINCT idxFase FROM DatiCommessa WHERE CapoCommessa = @CapoCommessa)
)
, cteExpl AS
(
	--SELECT	    @Anno as Anno, @Sett AS Sett, 0 as idxDipendente, cte.idxFase, 0 as OreTot
	--FROM	    cteFasi cte
	--WHERE	    ISNULL(d2a.Gruppo,'') <>''
	SELECT	    @Anno as Anno, @Settimana AS Sett, cte.idxFase
	FROM	    cteFasi cte
)



SELECT	cte.Anno, cte.Sett, 0 AS idxDipendente, cte.idxFase, ISNULL(SUM(ir.OreTot),0) as OreTot
FROM	cteExpl cte LEFT OUTER JOIN ImpiegoRisorse ir ON cte.Anno = ir.Anno AND cte.Sett = ir.Sett
GROUP BY cte.Anno, cte.Sett, cte.idxFase
ORDER BY SUM(ir.OreTot)

RETURN
go

commit
go





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