set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

alter VIEW v_selCommesseShort
AS
SELECT        dc.idxFase AS value, LEFT(dc.CapoCommessa, 1) + SUBSTRING(dc.CapoCommessa, CHARINDEX(' ', dc.CapoCommessa) + 1, 3) + ' | ' + dbo.f_padLeft(dc.NumeroCommessa, 4, '0') 
                         + '-' + dbo.f_padLeft(dc.AnnoCommessa, 4, '0') + ' | ' + dc.nickname AS label, af.idxProgetto AS conditio
FROM            dbo.DatiCommessa AS dc INNER JOIN
                         dbo.AnagFasi AS af ON dc.idxFase = af.idxFase
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 = "dc"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 270
               Right = 230
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "af"
            Begin Extent = 
               Top = 6
               Left = 268
               Bottom = 136
               Right = 439
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 4140
         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_selCommesseShort';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
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
*
**********************************************************/
alter 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)
*/

-- faccio cte
;with myCte AS
(
	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)
)

-- per poter ordinare con label delel commesse short (csu cui metto CC)
SELECT c.* 
FROM myCte c INNER JOIN v_selCommesseShort vscs ON c.idxFase = vscs.value
ORDER BY vscs.label DESC
go

/**********************************************************
* STORED stp_BAZ_getByAnnoSettDip
*
* recupera record di Bazaar Risorse per anno, settimana e dipendente selezionate
*
* mod: S.E.L. 2013.09.04
*
**********************************************************/
alter PROCEDURE stp_BAZ_getByAnnoSettDip
(
    @Anno INT,
    @Sett INT,
    @idxDipendente INT = 1
)
AS

SET NOCOUNT ON;
/*
SELECT Anno, Sett, idxDipendente, idxFase, Allocazione, SchemaWeek, OreTot
FROM dbo.BazaarRisorse
WHERE Anno = @Anno AND Sett = @Sett AND idxDipendente = @idxDipendente
*/


-- faccio cte
;with myCte AS
(
	SELECT Anno, Sett, idxDipendente, idxFase, Allocazione, SchemaWeek, OreTot
	FROM dbo.BazaarRisorse
	WHERE Anno = @Anno AND Sett = @Sett AND idxDipendente = @idxDipendente
)

-- per poter ordinare con label delel commesse short (csu cui metto CC)
SELECT c.* 
FROM myCte c INNER JOIN v_selCommesseShort vscs ON c.idxFase = vscs.value
ORDER BY vscs.label DESC
go

/**********************************************************
* STORED stp_IR_getFullSettPareto
*
* recupera riga tabella PIENA x anno, settimana x dipendente FINTO ordinando commesse in logica pareto ore tot settimana
* PER LE SOLE COMMESSE SCHEDULATE in bazaar
*
* mod: S.E.L. 2013.09.12
*
**********************************************************/
alter PROCEDURE stp_IR_getBazSettPareto
(
    @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
	AND idxFase IN (SELECT DISTINCT idxFase FROM BazaarRisorse 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
)

-- mod x ordinamento su CC
/*
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)
*/

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
		INNER JOIN v_selCommesseShort vscs ON cte.idxFase = vscs.value
GROUP BY cte.Anno, cte.Sett, cte.idxFase, vscs.label
--ORDER BY SUM(ir.OreTot)
ORDER BY vscs.label DESC

RETURN
go

/**********************************************************
* STORED stp_IR_getBazSettParetoCC
*
* 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 
*  - x cui esista programmazione bazaar
*
* mod: S.E.L. 2013.09.16
*
**********************************************************/
alter PROCEDURE stp_IR_getBazSettParetoCC
(
    @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)
*/


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
		INNER JOIN v_selCommesseShort vscs ON cte.idxFase = vscs.value
GROUP BY cte.Anno, cte.Sett, cte.idxFase, vscs.label
--ORDER BY SUM(ir.OreTot)
ORDER BY vscs.label DESC


RETURN
go

commit;
go






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