SET xact_abort ON
GO

BEGIN TRANSACTION
GO

SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		Marco Salvi
-- Create date: 11/03/2009
-- Description:	Stored procedure per ricostruire gli indici e fare lo shrink su un db
-- =============================================
CREATE PROCEDURE sp_IndiciAndShrink 
(
	@databaseName sysname
)
AS
BEGIN
	
	-- elimino eventuale tab temporanea preesistente
	IF (SELECT COUNT(*) FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TMP_nomiTabelle') > 0
	  BEGIN
		DROP TABLE ##TMP_nomiTabelle
	  END

	-- enumero le tab del db in una tab temporanea
	SELECT	IDENT = IDENTITY (INT,1,1), NAME
	INTO ##TMP_nomiTabelle
	FROM sys.Tables

	-- eseguo reindicizzazione
	DECLARE @VAR1 INT
	SET @VAR1 = 1
	WHILE @VAR1 < = ( SELECT COUNT(*) FROM ##TMP_nomiTabelle)
	BEGIN 
		
		DECLARE @OBJECTNAME VARCHAR(256)
		
		-- carico il nome della tab i-esima
		SELECT	@OBJECTNAME = NAME
		FROM	##TMP_nomiTabelle 
		WHERE	[IDENT] = @VAR1
		
		-- rebuild indice
		DBCC DBREINDEX(@OBJECTNAME)

		-- incremento
		SET @VAR1 = @VAR1 + 1

	END

	--shrink del db
	DBCC SHRINKDATABASE (@databaseName)

END
GO

COMMIT
GO


SET xact_abort ON
GO

BEGIN TRANSACTION
GO

SET ANSI_NULLS ON
GO

ALTER VIEW v_elencoIntervFilt
AS
SELECT     TOP (100) PERCENT dbo.InterventiMtz.numIntMtz, dbo.InterventiMtz.richiesta, dbo.AnagImpianti.codImpianto, dbo.AnagImpianti.nomeImpianto, 
                      dbo.AnagMacchine.codMacchina, dbo.AnagMacchine.nomeMacchina, dbo.InterventiMtz.descrizione, dbo.InterventiMtz.inizioIntervento, 
                      dbo.InterventiMtz.fineIntervento, ISNULL(dbo.InterventiMtz.descrizioneIntervento, 'n.d. (report mtz)') AS descrizioneIntervento, ISNULL(dbo.AnagCausali.descrCausale, 
                      'n.d. (causale)') AS descrCausale, ROUND(CAST(ISNULL(dbo.v_interventiErogati.totMinErogati, 0) AS FLOAT) / 60, 2) AS minErogati, 
                      ROUND(CAST(ISNULL(DATEDIFF(minute, dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento), 0) AS FLOAT) / 60, 2) AS durataMtz, CASE (isFermo) 
                      WHEN 1 THEN ROUND(CAST(ISNULL(DATEDIFF(minute, dbo.InterventiMtz.guasto, dbo.InterventiMtz.fineIntervento), 0) AS FLOAT) / 60, 2) 
                      WHEN 0 THEN ROUND(CAST(ISNULL(DATEDIFF(minute, dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento), 0) AS FLOAT) / 60, 2) END AS durataOff, 
                      dbo.InterventiMtz.guasto, dbo.InterventiMtz.idxStato, dbo.InterventiMtz.idxImpianto, dbo.InterventiMtz.idxMacchina
FROM         dbo.InterventiMtz INNER JOIN
                      dbo.AnagAmbitoGuasto ON dbo.InterventiMtz.idxAmbito = dbo.AnagAmbitoGuasto.idxAmbito INNER JOIN
                      dbo.AnagImpianti ON dbo.InterventiMtz.idxImpianto = dbo.AnagImpianti.idxImpianto INNER JOIN
                      dbo.AnagMacchine ON dbo.InterventiMtz.idxMacchina = dbo.AnagMacchine.idxMacchina INNER JOIN
                      dbo.AnagTipoGuasto ON dbo.InterventiMtz.idxTipo = dbo.AnagTipoGuasto.idxTipo INNER JOIN
                      dbo.AnagPriorita ON dbo.InterventiMtz.idxPriorita = dbo.AnagPriorita.idxPriorita INNER JOIN
                      dbo.AnagStati ON dbo.InterventiMtz.idxStato = dbo.AnagStati.idxStato LEFT OUTER JOIN
                      dbo.v_interventiErogati ON dbo.InterventiMtz.numIntMtz = dbo.v_interventiErogati.numIntMtz LEFT OUTER JOIN
                      dbo.AnagCausali ON dbo.InterventiMtz.idxCausale = dbo.AnagCausali.idxCausale
ORDER BY dbo.InterventiMtz.numIntMtz DESC
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 = "InterventiMtz"
            Begin Extent = 
               Top = 8
               Left = 486
               Bottom = 354
               Right = 671
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagAmbitoGuasto"
            Begin Extent = 
               Top = 11
               Left = 82
               Bottom = 89
               Right = 255
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagImpianti"
            Begin Extent = 
               Top = 123
               Left = 219
               Bottom = 231
               Right = 370
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagMacchine"
            Begin Extent = 
               Top = 190
               Left = 826
               Bottom = 298
               Right = 977
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagTipoGuasto"
            Begin Extent = 
               Top = 90
               Left = 1143
               Bottom = 168
               Right = 1294
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagPriorita"
            Begin Extent = 
               Top = 23
               Left = 958
               Bottom = 116
               Right = 1109
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagStati"
            Begin Extent = 
               Top = 263
               Left = 90
               Bottom = 356
               Right = 241
       ', 'SCHEMA', 'dbo', 'VIEW', 'v_elencoIntervFilt'
GO

EXEC sp_updateextendedproperty 'MS_DiagramPane2', '     End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "v_interventiErogati"
            Begin Extent = 
               Top = 3
               Left = 739
               Bottom = 81
               Right = 890
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagCausali"
            Begin Extent = 
               Top = 291
               Left = 1011
               Bottom = 369
               Right = 1162
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 30
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1665
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1575
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         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_elencoIntervFilt'
GO

COMMIT
GO



-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(5, GETDATE())
GO