Samuele E. Locatelli
337 lines
7.4 KiB
Transact-SQL
337 lines
7.4 KiB
Transact-SQL
/*
|
|
Deployment script for C2P_prod
|
|
|
|
This code was generated by a tool.
|
|
Changes to this file may cause incorrect behavior and will be lost if
|
|
the code is regenerated.
|
|
*/
|
|
|
|
GO
|
|
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
|
|
|
|
SET NUMERIC_ROUNDABORT OFF;
|
|
|
|
|
|
GO
|
|
:setvar DatabaseName "C2P"
|
|
:setvar DefaultFilePrefix "C2P_prod"
|
|
:setvar DefaultDataPath "G:\SQL2008R2\"
|
|
:setvar DefaultLogPath "F:\SQL2008R2\"
|
|
|
|
GO
|
|
:on error exit
|
|
GO
|
|
/*
|
|
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
|
|
To re-enable the script after enabling SQLCMD mode, execute the following:
|
|
SET NOEXEC OFF;
|
|
*/
|
|
:setvar __IsSqlCmdEnabled "True"
|
|
GO
|
|
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
|
|
BEGIN
|
|
PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
|
|
SET NOEXEC ON;
|
|
END
|
|
|
|
|
|
GO
|
|
USE [$(DatabaseName)];
|
|
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[v_selItems]...';
|
|
|
|
|
|
GO
|
|
CREATE VIEW dbo.v_selItems
|
|
AS
|
|
SELECT CodItem AS value, ItemDescr + ' [' + CodItem + ']' AS label
|
|
FROM dbo.ItemDet
|
|
WHERE (CodItem <> '')
|
|
GO
|
|
|
|
PRINT N'Creating [dbo].[v_selRawMat]...';
|
|
|
|
|
|
GO
|
|
CREATE VIEW dbo.v_selRawMat
|
|
AS
|
|
SELECT RawMat AS value, RawMat AS label
|
|
FROM dbo.RawMatDet
|
|
WHERE (RawMat <> '')
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2014.02.18
|
|
-- Description: Ricerca materiale Al
|
|
-- =============================================
|
|
create PROCEDURE [dbo].stp_VSRM_SearchLike
|
|
(
|
|
@value NVARCHAR(50)
|
|
)
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT value
|
|
,label
|
|
FROM v_selRawMat
|
|
WHERE (value LIKE '%' + @value + '%')
|
|
OR (label LIKE '%' + @value + '%')
|
|
go
|
|
|
|
|
|
PRINT N'Creating [dbo].[stp_esplodiVocab]...';
|
|
|
|
|
|
GO
|
|
-- =============================================
|
|
-- Author: S.E.Locatelli
|
|
-- Create date: 2011-10-24
|
|
-- Description:
|
|
-- =============================================
|
|
create PROCEDURE stp_esplodiVocab
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
-- svuoto vocabolario
|
|
DELETE FROM ZZZ_VocabExpl
|
|
|
|
-- carico termini ITA
|
|
INSERT INTO ZZZ_VocabExpl(Lemma,IT)
|
|
SELECT Lemma, Traduzione AS IT
|
|
FROM Vocabolario
|
|
WHERE Lingua = 'IT'
|
|
|
|
-- update termini ENG
|
|
UPDATE ZZZ_VocabExpl
|
|
SET EN=ISNULL(v.Traduzione,ve.EN)
|
|
FROM ZZZ_VocabExpl ve INNER JOIN Vocabolario v ON ve.Lemma=v.Lemma
|
|
WHERE v.Lingua='EN'
|
|
|
|
-- update termini BRA
|
|
UPDATE ZZZ_VocabExpl
|
|
SET BR=ISNULL(v.Traduzione,ve.BR)
|
|
FROM ZZZ_VocabExpl ve INNER JOIN Vocabolario v ON ve.Lemma=v.Lemma
|
|
WHERE v.Lingua='BR'
|
|
|
|
-- update termini CHN
|
|
UPDATE ZZZ_VocabExpl
|
|
SET CN=ISNULL(v.Traduzione,ve.CN)
|
|
FROM ZZZ_VocabExpl ve INNER JOIN Vocabolario v ON ve.Lemma=v.Lemma
|
|
WHERE v.Lingua='CN'
|
|
|
|
-- update termini CHN
|
|
UPDATE ZZZ_VocabExpl
|
|
SET MX=ISNULL(v.Traduzione,ve.MX)
|
|
FROM ZZZ_VocabExpl ve INNER JOIN Vocabolario v ON ve.Lemma=v.Lemma
|
|
WHERE v.Lingua='MX'
|
|
|
|
-- update termini CHN
|
|
UPDATE ZZZ_VocabExpl
|
|
SET PL=ISNULL(v.Traduzione,ve.PL)
|
|
FROM ZZZ_VocabExpl ve INNER JOIN Vocabolario v ON ve.Lemma=v.Lemma
|
|
WHERE v.Lingua='PL'
|
|
|
|
|
|
END
|
|
GO
|
|
PRINT N'Creating [dbo].[stp_VSItem_SearchLike]...';
|
|
|
|
|
|
GO
|
|
-- =============================================
|
|
-- Author: S.E. Locatelli
|
|
-- Create date: 2014.02.18
|
|
-- Description: Ricerca items
|
|
-- =============================================
|
|
create PROCEDURE [dbo].stp_VSItem_SearchLike
|
|
(
|
|
@value NVARCHAR(50)
|
|
)
|
|
|
|
AS
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT value
|
|
,label
|
|
FROM v_selItems
|
|
WHERE (value LIKE '%' + @value + '%')
|
|
OR (label LIKE '%' + @value + '%')
|
|
GO
|
|
PRINT N'Creating [voc].[stp_insNewLemma]...';
|
|
|
|
|
|
GO
|
|
/*************************************
|
|
* STORED PROCEDURE stp_insNewLemma
|
|
*
|
|
* elimina record anagrafica classe prodotto
|
|
*
|
|
* mod : 14/05/2012
|
|
* aut : S.E. Locatelli
|
|
**************************************/
|
|
CREATE PROCEDURE [voc].[stp_insNewLemma]
|
|
(
|
|
@lemma NVARCHAR(50),
|
|
@traduzione NVARCHAR(500)
|
|
)
|
|
AS
|
|
|
|
-- elimino se c'era già...
|
|
DELETE
|
|
FROM Vocabolario
|
|
WHERE lemma = @lemma
|
|
|
|
-- inserisco x tutte le lingue attive...
|
|
INSERT INTO Vocabolario
|
|
SELECT lingua, @lemma, @traduzione
|
|
FROM Lingue
|
|
|
|
SELECT *
|
|
FROM Vocabolario
|
|
WHERE lemma = @lemma
|
|
|
|
RETURN
|
|
GO
|
|
PRINT N'Creating [dbo].[v_selItems].[MS_DiagramPane1]...';
|
|
|
|
|
|
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 = "ItemDet"
|
|
Begin Extent =
|
|
Top = 6
|
|
Left = 38
|
|
Bottom = 254
|
|
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 = 4635
|
|
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', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_selItems';
|
|
|
|
|
|
GO
|
|
PRINT N'Creating [dbo].[v_selItems].[MS_DiagramPaneCount]...';
|
|
|
|
|
|
GO
|
|
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 1, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'v_selItems';
|
|
|
|
|
|
GO
|
|
PRINT N'Update complete.'
|
|
GO
|