set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

create VIEW v_selParticolari
AS
SELECT        Particolare AS value, DescParticolare AS label, ClassifArticolo AS conditio
FROM            RilPro.AnagParticolari
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*****************************************
* STORED stp_particolariByConditio
*
* Recupera elenco particolari data la condizione
*
* Steamware, S.E.L.
* mod: 2012.03.16
*
****************************************/
create PROCEDURE stp_particolariByConditio
(
	@conditio NVARCHAR(50)
)
AS

SELECT	*
FROM	v_selParticolari
WHERE	conditio = @conditio

RETURN
go

/*****************************************
* STORED stp_particolariByConditioLikePref
*
* Recupera elenco particolari data la condizione e l'inizio del campo value (ricerca like)
*
* Steamware, S.E.L.
* mod: 2012.03.16
*
****************************************/
create PROCEDURE stp_particolariByConditioLikePref
(
	@conditio NVARCHAR(50),
	@prefText NVARCHAR(50)
)
AS

SELECT	*
FROM	v_selParticolari
WHERE	conditio = @conditio
AND		value LIKE @prefText + '%'

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

alter VIEW V_MagazziniOverview
AS
SELECT        TOP (100) PERCENT dbo.AnagMag.CodCS, dbo.AnagMag.CodMag, dbo.AnagMag.DescMag, dbo.Blocchi.IdxBlocco, dbo.Blocchi.CodBlocco, dbo.Blocchi.DescBlocco, 
                         dbo.TipoCella.Capienza * dbo.Blocchi.NumX * dbo.Blocchi.NumY * dbo.Blocchi.NumZ AS TotCelle, dbo.TipoCella.Capienza * COUNT(DISTINCT dbo.Celle.IdxCella) 
                         AS CelleAttive, COUNT(dbo.PosizioneUdcCorrente.UDC) AS CelleOccupate, dbo.TipoCella.Capienza * COUNT(DISTINCT dbo.Celle.IdxCella) 
                         - COUNT(dbo.PosizioneUdcCorrente.UDC) AS CelleLibere, ISNULL(MIN(dbo.PosizioneUdcCorrente.DataRif), GETDATE()) AS Oldest, 
                         ISNULL(MAX(dbo.PosizioneUdcCorrente.DataRif), GETDATE()) AS Newest
FROM            dbo.TipoCella INNER JOIN
                         dbo.AnagMag INNER JOIN
                         dbo.Blocchi ON dbo.AnagMag.CodMag = dbo.Blocchi.CodMag AND dbo.AnagMag.CodCS = dbo.Blocchi.CodCS ON dbo.TipoCella.CodMag = dbo.AnagMag.CodMag AND
                          dbo.TipoCella.CodCS = dbo.AnagMag.CodCS LEFT OUTER JOIN
                         dbo.Celle ON dbo.TipoCella.IdxTipoCella = dbo.Celle.IdxTipoCella AND dbo.Blocchi.IdxBlocco = dbo.Celle.IdxBlocco LEFT OUTER JOIN
                         dbo.PosizioneUdcCorrente ON dbo.Celle.IdxCella = dbo.PosizioneUdcCorrente.IdxCella
WHERE        (dbo.Celle.Attiva = 1)
GROUP BY dbo.AnagMag.CodCS, dbo.AnagMag.CodMag, dbo.AnagMag.DescMag, dbo.Blocchi.IdxBlocco, dbo.Blocchi.CodBlocco, dbo.Blocchi.NumX, dbo.Blocchi.NumY, 
                         dbo.Blocchi.NumZ, dbo.TipoCella.Capienza, dbo.Blocchi.DescBlocco
ORDER BY dbo.AnagMag.CodMag, dbo.Blocchi.CodBlocco
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[39] 4[23] 2[11] 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 = "TipoCella"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 135
               Right = 224
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AnagMag"
            Begin Extent = 
               Top = 6
               Left = 662
               Bottom = 118
               Right = 832
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Blocchi"
            Begin Extent = 
               Top = 36
               Left = 974
               Bottom = 257
               Right = 1144
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Celle"
            Begin Extent = 
               Top = 149
               Left = 629
               Bottom = 313
               Right = 799
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PosizioneUdcCorrente"
            Begin Extent = 
               Top = 159
               Left = 201
               Bottom = 288
               Right = 371
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 13
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1665
         Width = 1665
         Width = 1500
         Width = 1500
      End
   End
   B', 'SCHEMA', 'dbo', 'VIEW', 'V_MagazziniOverview';
go

exec sp_updateextendedproperty 'MS_DiagramPane2', 'egin CriteriaPane = 
      Begin ColumnWidths = 12
         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_MagazziniOverview';
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/***************************************
* STORED stp_ParticolariOverview
*
* elenco di overview sullo stato dei magazzini (dettaglio a "maglie larghe")
*
* Steamware, S.E.L.
* mod: 2010.06.02
*
****************************************/
alter PROCEDURE stp_magazzinoOverview
(
	@CodCS VARCHAR(2)
)
AS

	SELECT     AnagMag.CodCS, AnagMag.CodMag, AnagMag.DescMag, Blocchi.IdxBlocco, Blocchi.CodBlocco, Blocchi.DescBlocco, 
               TipoCella.Capienza * Blocchi.NumX * Blocchi.NumY * Blocchi.NumZ AS TotCelle, 
               TipoCella.Capienza * COUNT(DISTINCT Celle.IdxCella) AS CelleAttive, 
               COUNT(PosizioneUdcCorrente.UDC) AS CelleOccupate, 
               TipoCella.Capienza * COUNT(DISTINCT Celle.IdxCella) - COUNT(PosizioneUdcCorrente.UDC) AS CelleLibere, 
               ISNULL(MIN(PosizioneUdcCorrente.DataRif), GETDATE()) AS Oldest, ISNULL(MAX(PosizioneUdcCorrente.DataRif), GETDATE()) AS Newest
FROM			TipoCella INNER JOIN
                AnagMag INNER JOIN
                Blocchi ON AnagMag.CodMag = Blocchi.CodMag AND AnagMag.CodCS = Blocchi.CodCS ON TipoCella.CodMag = AnagMag.CodMag AND 
                TipoCella.CodCS = AnagMag.CodCS LEFT OUTER JOIN
                Celle ON TipoCella.IdxTipoCella = Celle.IdxTipoCella AND Blocchi.IdxBlocco = Celle.IdxBlocco LEFT OUTER JOIN
                PosizioneUdcCorrente ON Celle.IdxCella = PosizioneUdcCorrente.IdxCella
WHERE			(Celle.Attiva = 1) AND (AnagMag.CodCS = @CodCS) AND (ISNULL(AnagMag.Nascosto, 0) = 0)
GROUP BY		AnagMag.CodCS, AnagMag.CodMag, AnagMag.DescMag, Blocchi.IdxBlocco, Blocchi.CodBlocco, Blocchi.DescBlocco, 
				Blocchi.NumX, Blocchi.NumY, Blocchi.NumZ, 
                TipoCella.Capienza
ORDER BY		AnagMag.CodMag, Blocchi.CodBlocco

RETURN
go

/***************************************
* STORED stp_ParticolariOverview
*
* restituisce solo la riga con il num max di celle DI UN DATO magazzino
*
* Steamware, S.E.L.
* mod: 2010.06.02
*
****************************************/
alter PROCEDURE stp_magazzinoOverviewByMag_maxCelle
(
	@CodCS VARCHAR(2),
	@CodMag VARCHAR(50)
)
AS


WITH cte_celle AS(
	SELECT     b.CodBlocco, c.IdxTipoCella, COUNT(c.IdxCella) * t.Capienza AS numCelle
	FROM         Celle AS c INNER JOIN
						  Blocchi AS b ON c.IdxBlocco = b.IdxBlocco INNER JOIN
						  TipoCella AS t ON c.IdxTipoCella = t.IdxTipoCella
	WHERE     (b.CodCS = @CodCS) AND (b.CodMag = @CodMag)
	GROUP BY b.CodBlocco, c.IdxTipoCella, t.Capienza
)


	SELECT		TOP (1) AnagMag.CodCS, AnagMag.CodMag, AnagMag.DescMag, b.IdxBlocco, b.CodBlocco, b.DescBlocco, 
				ISNULL(c.numCelle,ISNULL(b.NumX * b.NumY * b.NumZ,1)) AS TotCelle, 
	            0 AS CelleAttive, 0 AS CelleOccupate, 0 AS CelleLibere, GETDATE() AS Oldest, GETDATE() AS Newest
	FROM        AnagMag INNER JOIN Blocchi b ON AnagMag.CodMag = b.CodMag AND AnagMag.CodCS = b.CodCS INNER JOIN cte_celle c ON c.CodBlocco = b.CodBlocco
	WHERE       (AnagMag.CodCS = @CodCS) AND AnagMag.CodMag = @CodMag
	ORDER BY	TotCelle DESC
RETURN
go

/***************************************
* STORED stp_ParticolariOverview
*
* restituisce solo la riga con il num max di celle
*
* Steamware, S.E.L.
* mod: 2010.06.02
*
****************************************/
alter PROCEDURE stp_magazzinoOverview_maxCelle
(
	@CodCS VARCHAR(2)
)
AS

WITH cte_celle AS(
	SELECT     b.CodBlocco, c.IdxTipoCella, COUNT(c.IdxCella) * t.Capienza AS numCelle
	FROM         Celle AS c INNER JOIN
						  Blocchi AS b ON c.IdxBlocco = b.IdxBlocco INNER JOIN
						  TipoCella AS t ON c.IdxTipoCella = t.IdxTipoCella
	WHERE     (b.CodCS = @CodCS)
	GROUP BY b.CodBlocco, c.IdxTipoCella, t.Capienza
)


	SELECT		TOP (1) AnagMag.CodCS, AnagMag.CodMag, AnagMag.DescMag, b.IdxBlocco, b.CodBlocco, b.DescBlocco,
				ISNULL(c.numCelle,ISNULL(b.NumX * b.NumY * b.NumZ,1)) AS TotCelle, 
	            0 AS CelleAttive, 0 AS CelleOccupate, 0 AS CelleLibere, GETDATE() AS Oldest, GETDATE() AS Newest
	FROM        AnagMag INNER JOIN Blocchi b ON AnagMag.CodMag = b.CodMag AND AnagMag.CodCS = b.CodCS INNER JOIN cte_celle c ON c.CodBlocco = b.CodBlocco
	WHERE       (AnagMag.CodCS = @CodCS)
	ORDER BY	TotCelle DESC

RETURN
go

commit;
go







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