Samuele E. Locatelli
936 lines
26 KiB
Transact-SQL
936 lines
26 KiB
Transact-SQL
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
alter VIEW v_selMag
|
|
AS
|
|
SELECT CodMag AS value, DescMag AS label, CodCS AS conditio
|
|
FROM dbo.AnagMag AS AnagMag_1
|
|
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 = "AnagMag_1"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 208
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
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_selMag';
|
|
go
|
|
|
|
alter VIEW v_selMagLogico
|
|
AS
|
|
SELECT DISTINCT CAST(ec.IdxPosizione AS NVARCHAR(20)) AS value, 'Mag. ' + CAST(ec.IdxPosizione AS NVARCHAR(20)) AS label, ec.CodCS AS conditio
|
|
FROM dbo.ElencoCartellini AS ec INNER JOIN
|
|
dbo.AnagMag AS am ON CAST(ec.IdxPosizione AS NVARCHAR(50)) = am.CodMag
|
|
WHERE (ec.IdxPosizione > 0)
|
|
go
|
|
|
|
alter VIEW v_selPeriodiTrad
|
|
AS
|
|
SELECT AnagPeriodi_1.codPeriodo AS value, dbo.v_vocabolario.Traduzione AS label, dbo.v_vocabolario.Lingua AS conditio
|
|
FROM dbo.AnagPeriodi AS AnagPeriodi_1 INNER JOIN
|
|
dbo.v_vocabolario ON AnagPeriodi_1.lemmaPeriodo = dbo.v_vocabolario.Lemma
|
|
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 = "AnagPeriodi_1"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 102
|
|
Right = 208
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "v_vocabolario"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 246
|
|
Bottom = 118
|
|
Right = 416
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 9
|
|
Width = 284
|
|
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_selPeriodiTrad';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
alter VIEW V_MagazziniLogiciOverview
|
|
AS
|
|
SELECT el.CodCS, anpo.IdxPosizione AS CodMagLogico, anpo.DescPosizione AS DescMagLogico, dbo.Blocchi.IdxBlocco, dbo.AnagMag.CodMag,
|
|
dbo.Blocchi.CodBlocco, COUNT(el.UDC) AS CelleOccupate, ISNULL(MIN(PosUdcCorr.DataRif), GETDATE()) AS Oldest, ISNULL(MAX(PosUdcCorr.DataRif),
|
|
GETDATE()) AS Newest
|
|
FROM dbo.Celle INNER JOIN
|
|
dbo.Blocchi INNER JOIN
|
|
dbo.AnagMag ON dbo.Blocchi.CodMag = dbo.AnagMag.CodMag AND dbo.Blocchi.CodCS = dbo.AnagMag.CodCS ON
|
|
dbo.Celle.IdxBlocco = dbo.Blocchi.IdxBlocco INNER JOIN
|
|
dbo.ElencoCartellini AS el INNER JOIN
|
|
dbo.PosizioneUdcCorrente AS PosUdcCorr ON el.UDC = PosUdcCorr.UDC INNER JOIN
|
|
dbo.AnagPosizioni AS anpo ON el.IdxPosizione = anpo.IdxPosizione ON dbo.Celle.IdxCella = PosUdcCorr.IdxCella
|
|
GROUP BY el.CodCS, anpo.IdxPosizione, anpo.DescPosizione, dbo.Blocchi.IdxBlocco, dbo.Blocchi.CodBlocco, dbo.AnagMag.CodMag
|
|
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 = "Celle"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 710
|
|
Bottom = 135
|
|
Right = 896
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "Blocchi"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 486
|
|
Bottom = 135
|
|
Right = 672
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagMag"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 1158
|
|
Bottom = 118
|
|
Right = 1344
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "el"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 224
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "PosUdcCorr"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 262
|
|
Bottom = 136
|
|
Right = 448
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "anpo"
|
|
Begin Extent =
|
|
Top = 138
|
|
Left = 38
|
|
Bottom = 251
|
|
Right = 232
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 12
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 1500
|
|
Width ', 'SCHEMA', 'dbo', 'VIEW', 'V_MagazziniLogiciOverview';
|
|
go
|
|
|
|
exec sp_updateextendedproperty 'MS_DiagramPane2', '= 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
End
|
|
End
|
|
Begin 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_MagazziniLogiciOverview';
|
|
go
|
|
|
|
alter VIEW V_MagazziniOverview
|
|
AS
|
|
SELECT 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
|
|
go
|
|
|
|
alter VIEW v_RapQualSunto
|
|
AS
|
|
SELECT CAST(nRapQual AS NVARCHAR(20)) AS nRapQual, DataRapQual, CodFor, CodLega, SUM(Qta) AS Qta, BenesQual, COUNT(*) AS UdcTot, COUNT(UDC)
|
|
AS UdcAssoc, LegaScaric
|
|
FROM RilPro.RapQual AS RapQual_1
|
|
GROUP BY nRapQual, DataRapQual, CodFor, CodLega, BenesQual, LegaScaric
|
|
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[42] 4[20] 2[14] 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 = "RapQual_1"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 224
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 10
|
|
Width = 284
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
End
|
|
End
|
|
Begin CriteriaPane =
|
|
Begin ColumnWidths = 12
|
|
Column = 1440
|
|
Alias = 1305
|
|
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_RapQualSunto';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
alter VIEW RilPro.vDatiLineeGiornata
|
|
AS
|
|
WITH cteUltStampo AS (SELECT CodStampo, InizioValidita, ProgFigure, FineValidita, NumCommessa, NumFigure, CodCliente, CodArticolo, Particolare
|
|
FROM RilPro.StampoArticolo AS A
|
|
WHERE (GETDATE() BETWEEN InizioValidita AND FineValidita)), cteEspAttivi AS
|
|
(SELECT s.CodStampo, s.Esponente, s.EspAttivo, s.DataInserim, s.DataValidita
|
|
FROM RilPro.StampoEsponente AS s INNER JOIN
|
|
(SELECT CodStampo, MAX(DataValidita) AS DataValidita
|
|
FROM RilPro.StampoEsponente
|
|
WHERE (DataValidita <= GETDATE()) AND (EspAttivo = 'S')
|
|
GROUP BY CodStampo) AS s2 ON s.CodStampo = s2.CodStampo AND s.DataValidita = s2.DataValidita), cteLineeAttive AS
|
|
(SELECT CodImpianto, CodStampo, NumStampate, CONVERT(DATETIME, DataInizio) AS DataInizio, DataFine, TurnoInizio, TurnoFine
|
|
FROM RilPro.LineaStampi
|
|
WHERE (DataFine = '99991231'))
|
|
SELECT l.CodImpianto, l.CodStampo, l.NumStampate, l.DataInizio, l.TurnoInizio, g.InizioValidita, g.FineValidita, g.NumFigure, CAST(g.CodCliente AS varchar(50))
|
|
AS CodCliente, cl.RagSociale, CAST(g.CodArticolo AS varchar(50)) AS CodArticolo, g.Particolare, pa.DescParticolare, pa.DisegnoGrezzo, pa.CodFamiglia,
|
|
fa.DescFamiglia, fi.Figura, es.Esponente
|
|
FROM cteLineeAttive AS l INNER JOIN
|
|
cteUltStampo AS g ON l.CodStampo = g.CodStampo LEFT OUTER JOIN
|
|
RilPro.StampoFigure AS fi ON l.CodStampo = fi.CodStampo LEFT OUTER JOIN
|
|
cteEspAttivi AS es ON g.CodStampo = es.CodStampo LEFT OUTER JOIN
|
|
RilPro.AnagParticolari AS pa ON g.Particolare = pa.Particolare LEFT OUTER JOIN
|
|
RilPro.AnagFamiglie AS fa ON pa.CodFamiglia = fa.CodFamiglia LEFT OUTER JOIN
|
|
RilPro.AnagClienti AS cl ON g.CodCliente = cl.CodCliente
|
|
go
|
|
|
|
alter VIEW V_DettMagPart
|
|
AS
|
|
SELECT dbo.AnagMag.CodCS, dbo.AnagMag.CodMag, dbo.AnagMag.DescMag, dbo.Blocchi.IdxBlocco, dbo.Blocchi.CodBlocco, ElencoCartellini_1.Particolare,
|
|
ElencoCartellini_1.Esponente, COUNT(dbo.PosizioneUdcCorrente.UDC) AS NumUDC, ISNULL(MIN(dbo.PosizioneUdcCorrente.DataRif), GETDATE()) AS Oldest,
|
|
ISNULL(MAX(dbo.PosizioneUdcCorrente.DataRif), GETDATE()) AS Newest, SUM(ElencoCartellini_1.Qta) AS QtaTotPz, dbo.AnagStatiProdotto.DescStato
|
|
FROM dbo.AnagMag INNER JOIN
|
|
dbo.Blocchi ON dbo.AnagMag.CodMag = dbo.Blocchi.CodMag AND dbo.AnagMag.CodCS = dbo.Blocchi.CodCS INNER JOIN
|
|
dbo.Celle INNER JOIN
|
|
dbo.ElencoCartellini AS ElencoCartellini_1 INNER JOIN
|
|
dbo.PosizioneUdcCorrente ON ElencoCartellini_1.UDC = dbo.PosizioneUdcCorrente.UDC ON dbo.Celle.IdxCella = dbo.PosizioneUdcCorrente.IdxCella ON
|
|
dbo.Blocchi.IdxBlocco = dbo.Celle.IdxBlocco INNER JOIN
|
|
dbo.AnagStatiProdotto ON ElencoCartellini_1.CodStato = dbo.AnagStatiProdotto.CodStato
|
|
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, ElencoCartellini_1.Particolare, dbo.AnagStatiProdotto.DescStato, ElencoCartellini_1.Esponente
|
|
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 = "AnagMag"
|
|
Begin Extent =
|
|
Top = 22
|
|
Left = 1012
|
|
Bottom = 134
|
|
Right = 1198
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "Blocchi"
|
|
Begin Extent =
|
|
Top = 16
|
|
Left = 737
|
|
Bottom = 145
|
|
Right = 923
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "Celle"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 486
|
|
Bottom = 135
|
|
Right = 672
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "ElencoCartellini_1"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 136
|
|
Right = 224
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "PosizioneUdcCorrente"
|
|
Begin Extent =
|
|
Top = 116
|
|
Left = 35
|
|
Bottom = 245
|
|
Right = 221
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
Begin Table = "AnagStatiProdotto"
|
|
Begin Extent =
|
|
Top = 226
|
|
Left = 650
|
|
Bottom = 321
|
|
Right = 836
|
|
End
|
|
DisplayFlags = 280
|
|
TopColumn = 0
|
|
End
|
|
End
|
|
End
|
|
Begin SQLPane =
|
|
End
|
|
Begin DataPane =
|
|
Begin ParameterDefaults = ""
|
|
End
|
|
Begin ColumnWidths = 13
|
|
Width = 284
|
|
Width = 1', 'SCHEMA', 'dbo', 'VIEW', 'V_DettMagPart';
|
|
go
|
|
|
|
exec sp_updateextendedproperty 'MS_DiagramPane2', '500
|
|
Width = 1500
|
|
Width = 2145
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 1500
|
|
Width = 2415
|
|
Width = 1500
|
|
End
|
|
End
|
|
Begin 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_DettMagPart';
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
set xact_abort on;
|
|
go
|
|
|
|
begin transaction;
|
|
go
|
|
|
|
set ANSI_NULLS on;
|
|
go
|
|
|
|
/***************************************
|
|
* STORED stp_selMagByConditio
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2012.10.08
|
|
*
|
|
************************************/
|
|
create PROCEDURE stp_selMagByConditio
|
|
(
|
|
@conditio NVARCHAR(2)
|
|
)
|
|
AS
|
|
|
|
SELECT value, label, conditio
|
|
FROM v_selMag
|
|
WHERE (conditio = @conditio)
|
|
ORDER BY label
|
|
|
|
RETURN
|
|
go
|
|
|
|
/***************************************
|
|
* STORED stp_selMagLogicoByConditio
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2012.10.08
|
|
*
|
|
************************************/
|
|
create PROCEDURE stp_selMagLogicoByConditio
|
|
(
|
|
@conditio NVARCHAR(2)
|
|
)
|
|
AS
|
|
|
|
SELECT value, label, conditio
|
|
FROM v_selMagLogico
|
|
WHERE (conditio = @conditio)
|
|
ORDER BY label
|
|
|
|
RETURN
|
|
go
|
|
|
|
commit;
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(512, GETDATE())
|
|
GO
|
|
|
|
SELECT TOP 10 * FROM LogUpdateDb ORDER BY Versione DESC
|