WebGIM/GimDB/dwh/Views/VDWH_ElencoInterventi.sql

CREATE VIEW [dwh].[VDWH_ElencoInterventi]
AS
/*
   messo in cte i campi della dbo.v_elencoIntervFilt + aggiunti i campi mancanti da da VDWH_DettaglioInterv
*/
WITH cte AS 
(
    SELECT
         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
        ,dbo.f_diffOreLavorative(dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento, dbo.InterventiMtz.idxMacchina) AS durataMtz
        ,CASE (isFermo) WHEN 1 THEN dbo.f_diffOreLavorative(dbo.InterventiMtz.guasto, dbo.InterventiMtz.fineIntervento, InterventiMtz.idxMacchina) 
                        WHEN 0 THEN dbo.f_diffOreLavorative(dbo.InterventiMtz.inizioIntervento, dbo.InterventiMtz.fineIntervento, InterventiMtz.idxMacchina) 
         END AS durataOff
        ,dbo.InterventiMtz.guasto
        ,dbo.InterventiMtz.idxStato
        ,dbo.InterventiMtz.idxImpianto
        ,dbo.InterventiMtz.idxMacchina
    --    ,dbo.InterventiMtz.isFermo
        ,CASE dbo.InterventiMtz.isFermo WHEN 'True' THEN 'SI' ELSE 'NO' END AS isFermo
        ,dbo.InterventiMtz.scheduled
        -- aggiunti da VDWH_DettaglioInterv
        ,dbo.InterventiMtz.turnoLav
        ,dbo.InterventiMtz.matr
        ,dbo.AnagAmbitoGuasto.descrAmbitoGuasto
        ,dbo.AnagPriorita.descrPriorita
        ,dbo.AnagTipoGuasto.descrTipo
        ,dbo.AnagStati.DescrStato
        ,ISNULL(dbo.InterventiMtz.presaInCarico, dbo.InterventiMtz.richiesta) AS presaInCarico
        ,dbo.InterventiMtz.isPreventivabile
        ,dbo.AnagFamMacchine.nomeFamMacch
    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  JOIN dbo.v_interventiErogati  ON dbo.InterventiMtz.numIntMtz      = dbo.v_interventiErogati.numIntMtz
    LEFT  JOIN dbo.AnagCausali          ON dbo.InterventiMtz.idxCausale     = dbo.AnagCausali.idxCausale
    INNER JOIN dbo.AnagFamMacchine      ON dbo.AnagCausali.idxFamMacchine   = dbo.AnagFamMacchine.idxFamMacchine
    -- ORDER BY dbo.InterventiMtz.numIntMtz DESC
)
SELECT   numIntMtz          AS [Richiesta Webgim Nr.]
        ,codImpianto        AS [Codice Reparto]
        ,nomeImpianto       AS Reparto
        ,nomeMacchina       AS [Descrizione Macchina ]
        ,codMacchina        AS [Codice macchina]
        ,dbo.f_fixCR(descrizione)           AS [Descrizione Guasto]
        ,dbo.f_fixCR(descrizioneIntervento) AS [Descrizione Intervento MTZ]
        ,guasto             AS [Ora Guasto]
        ,richiesta          AS [Ora Richiesta Webgim]
        ,inizioIntervento   AS [Ora Inizio Intervento]
        ,fineIntervento     AS [Ora Fine Intervento]
        ,durataMtz          AS [Ore intervento MTZ]
        ,durataOff          AS [Ore fermo Officina]
        ,minErogati         AS [Ore operatori MTZ]
        ,turnoLav           AS Turno
        ,matr               AS Matricola
        ,descrAmbitoGuasto  AS [Ambito Guasto]
        ,descrPriorita      AS [Descrizione Priorita]
        ,DescrStato         AS [Descrizione Stato]
        ,presaInCarico      AS [Presa in Carico]
        ,isPreventivabile   AS [Preventivabile]
        ,descrCausale       AS [Descrizione Causale]
        ,isFermo            AS [isFermo]
        ,descrTipo          AS [Tipo Guasto]
        ,nomeFamMacch       AS [Famiglia Causale]
FROM cte

/*
SELECT   numIntMtz          AS [Richiesta Webgim Nr.]
        ,codImpianto        AS [Codice Reparto]
        ,nomeImpianto       AS Reparto
        ,nomeMacchina       AS [Descrizione Macchina ]
        ,codMacchina        AS [Codice macchina]
        ,dbo.f_fixCR(descrizione)           AS [Descrizione Guasto]
        ,dbo.f_fixCR(descrizioneIntervento) AS [Descrizione Intervento MTZ]
        ,guasto             AS [Ora Guasto]
        ,richiesta          AS [Ora Richiesta Webgim]
        ,inizioIntervento   AS [Ora Inizio Intervento]
        ,fineIntervento     AS [Ora Fine Intervento]
        ,durataMtz          AS [Ore intervento MTZ]
        ,durataOff          AS [Ore fermo Officina]
        ,minErogati         AS [Ore operatori MTZ]
FROM dbo.v_elencoIntervFilt
*/





GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[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 = "v_elencoIntervFilt"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 136
               Right = 240
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      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
', @level0type = N'SCHEMA', @level0name = N'dwh', @level1type = N'VIEW', @level1name = N'VDWH_ElencoInterventi';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 1, @level0type = N'SCHEMA', @level0name = N'dwh', @level1type = N'VIEW', @level1name = N'VDWH_ElencoInterventi';