-----------------------------------------------------------------------------------------'
-- SPGT_CREATEVIEWSVARIANTI: stored procedure creazione viste per gestione varianti									 
-----------------------------------------------------------------------------------------'

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SPGT_CREATEVIEWSVARIANTI]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[SPGT_CREATEVIEWSVARIANTI]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SPGT_CREATEVIEWSVARIANTI]
AS
	BEGIN
		declare @ditta nvarchar(50)
		declare @gammaDB nvarchar(128)
		declare @Stmt  nvarchar(MAX)

		declare CAziende cursor local for 
		select GT01_CODICEGE,GT01_PARALF1 from GT01_TRASCODIFICHE
		where GT01_TIPO=1

		open CAziende fetch next from CAziende into @ditta, @gammaDB
		while (@@FETCH_STATUS=0)
		begin
			--Tabella MG5E_OPZIONI
				set @Stmt = '
				IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''dbo.VGT_MG5E_OPZIONI_' + right('00000' + @ditta,5) + '''))
				begin
					DROP VIEW dbo.VGT_MG5E_OPZIONI_' + right('00000' + @ditta,5) + '
				end'
				exec(@Stmt)

				set @Stmt = '
				CREATE VIEW dbo.VGT_MG5E_OPZIONI_' + right('00000' + @ditta,5) + '
				AS 
				SELECT 
					MG5E_DITTA_CG18			-- Codice Azienda
					,MG5E_CODICEVAR_MG5F	-- Codice variante 
											-- A0, A1, ...A20
					,MG5E_OPZIONE			-- Codice opzione
					,MG5E_DESCR				-- Descrizione opzione
					,MG5E_IDMEDIA_CG99		-- Id per Hypermedia
				FROM ' + @gammaDB + '.dbo.MG5E_OPZIONI'
				exec(@Stmt)

			--Tabella MG5F_DIMECONF
				set @Stmt = '
				IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''dbo.VGT_MG5F_DIMECONF_' + right('00000' + @ditta,5) + '''))
				begin
					DROP VIEW dbo.VGT_MG5F_DIMECONF_' + right('00000' + @ditta,5) + '
				end'
				exec(@Stmt)

				set @Stmt = '
				CREATE VIEW dbo.VGT_MG5F_DIMECONF_' + right('00000' + @ditta,5) + '
				AS 
				SELECT  
					MG5F_DITTA_CG18		-- Ditta
					,MG5F_CODICEVAR     -- Opzione 
					,MG5F_SIZE			-- Dimensione
					,MG5F_DESCR_VAR		-- Descrizione
					,MG5F_FLGRAGGR		-- Se ha raggruppamenti
					,MG5F_INDDESCRART	-- Se viene utilizzata per la descrizione articolo
										-- per ora gestire solo 0,1,2 non 3,4,5
										-- Eventualmente gestire anche MG5S
					,MG5F_POSINIZDESCR	--	
					,MG5F_POSFINDESCR	--
					,MG5F_SEQDESCR		--
					,MG5F_INDNATURA		-- Alfanumerico/numerico
					,MG5F_NUMINT		-- 
					,MG5F_NUMDEC		-- 
					,MG5F_INDGRIGLIA	-- Gestione griglia
					,MG5F_INDMOSTRACOD	-- Indicatore Mostra codice, descrizione
					,MG5F_INDGENGRIDDOC	-- ...
					,MG5F_FLGOBBLIG		-- Se obbligatoria
					,MG5F_FLGDEFAULT	-- se utilizzare il Default
					,MG5F_VARDEFAULT	-- Valore di default
					,MG5F_INDPROGCONFIG	-- 1 = Gestita tramite conf.comm
				FROM ' + @gammaDB + '.dbo.MG5F_DIMECONF
				where MG5F_DITTA_CG18 = ' + @ditta
				exec(@Stmt)

			--Tabella MG5G_RAGGVAR
				set @Stmt = '
				IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''dbo.VGT_MG5G_RAGGVAR_' + right('00000' + @ditta,5) + '''))
				begin
					DROP VIEW dbo.VGT_MG5G_RAGGVAR_' + right('00000' + @ditta,5) + '
				end'
				exec(@Stmt)

				set @Stmt = '
				CREATE VIEW dbo.VGT_MG5G_RAGGVAR_' + right('00000' + @ditta,5) + '
				AS 
				SELECT 
					MG5G_DITTA_CG18	
					,MG5G_CODICEVAR_MG5F	
					,MG5G_CODRAGGVAR		-- Codice raggruppamento
					,MG5G_DESCRRAGGVAR		-- Descrizione raggruppamento
					,MG5G_IDMEDIA_CG99		
				FROM ' + @gammaDB + '.dbo.MG5G_RAGGVAR
				where MG5G_DITTA_CG18 = ' + @ditta
				exec(@Stmt)

			--Tabella MG5H_COMPRAGVAR
				set @Stmt = '
				IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''dbo.VGT_MG5H_COMPRAGVAR_' + right('00000' + @ditta,5) + '''))
				begin
					DROP VIEW dbo.VGT_MG5H_COMPRAGVAR_' + right('00000' + @ditta,5) + '
				end'
				exec(@Stmt)

				set @Stmt = '
				CREATE VIEW dbo.VGT_MG5H_COMPRAGVAR_' + right('00000' + @ditta,5) + '
				AS 
				SELECT 
					MG5H_DITTA_CG18
					,MG5H_CODICEVAR_MG5F
					,MG5H_RAGGR_MG5G
					,MG5H_SEQOPZIONE		-- Ordine per lookup o griglia
					,MG5H_OPZIONE_MG5E
					,MG5H_DESCR				--Eventuale descrizione che sovrascrive quella della M55E
				FROM ' + @gammaDB + '.dbo.MG5H_COMPRAGVAR
				where MG5H_DITTA_CG18= ' + @ditta
				exec(@Stmt)

			--Tabella MG6B_GESVARART
				set @Stmt = '
				IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''dbo.VGT_MG6B_GESVARART_' + right('00000' + @ditta,5) + '''))
				begin
					DROP VIEW dbo.VGT_MG6B_GESVARART_' + right('00000' + @ditta,5) + '
				end'
				exec(@Stmt)

				set @Stmt = '
				CREATE VIEW dbo.VGT_MG6B_GESVARART_' + right('00000' + @ditta,5) + '
				AS 
				SELECT 
					MG6B_DITTA_CG18
					,MG6B_CODART_MG66
					,MG6B_CODICEVAR_MG5F
					,MG6B_FLGGESTVAR		-- Flag articolo gestisce la variante
					,MG6B_CODRAGGVAR_MG5G	-- o tutte lavarianti oppure un solo raggruppamento.
					,MG6B_INDOBBLIG			
					,MG6B_INDDEFAULT
					,MG6B_VARDEFAULT	
					,MG6B_INDPREZZO
					,MG6B_INDQTA
					,MG6B_PREZZO
					,MG6B_PERCMAGG
					,MG6B_QTA
					,MG6B_FLGSINGOLA
					,MG6B_FLGOBBLIG
				FROM ' + @gammaDB + '.dbo.MG6B_GESVARART
				where MG6B_DITTA_CG18 = ' + @ditta
				exec(@Stmt)

			--Tabella MG6D_FLGTABARTVAR
				set @Stmt = '
				IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''dbo.VGT_MG6D_FLGTABARTVAR_' + right('00000' + @ditta,5) + '''))
				begin
					DROP VIEW dbo.VGT_MG6D_FLGTABARTVAR_' + right('00000' + @ditta,5) + '
				end'
				exec(@Stmt)

				set @Stmt = '
				CREATE VIEW dbo.VGT_MG6D_FLGTABARTVAR_' + right('00000' + @ditta,5) + '
				AS 
				SELECT 
					MG6D_DITTA_CG18
					,MG6D_CODART_MG66
					,MG6D_PROG
					,MG6D_INDGESTAB
				FROM ' + @gammaDB + '.dbo.MG6D_FLGTABARTVAR
				where MG6D_DITTA_CG18 = ' + @ditta
				exec(@Stmt)

			--Tabella MG5S_VARCLIFOR
				set @Stmt = '
				IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''dbo.VGT_MG5S_VARCLIFOR_' + right('00000' + @ditta,5) + '''))
				begin
					DROP VIEW dbo.VGT_MG5S_VARCLIFOR_' + right('00000' + @ditta,5) + '
				end'
				exec(@Stmt)

				set @Stmt = '
				CREATE VIEW dbo.VGT_MG5S_VARCLIFOR_' + right('00000' + @ditta,5) + '
				AS 
				SELECT MG5S_DITTA_CG18
					,MG5S_CODICEVAR_MG5F
					,MG5S_OPZIONE_MG5E
					,MG5S_TIPOCF_CG44
					,MG5S_CLIFOR_CG44
					,MG5S_DESCR
				FROM ' + @gammaDB + '.dbo.MG5S_VARCLIFOR'
				exec(@Stmt)

			fetch next from CAziende into @ditta, @gammaDB
		end

		close CAziende
		deallocate CAziende
	END