GMW/GMW_data/SqlScripts/V1.1/GMW_00280.sql

create index i_UDC on AS400_BatchConsumoMP(UDC)
go


create index i_UDC on RilPro.RapQual(UDC)
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/***************************************
* STORED stp_consumaMP
*
* Salva il movimento di consumo dell'UDC di MP indicato
*
* Steamware, S.E.L.
* mod: 2010.10.12
*
****************************************/
alter PROCEDURE BatchQueue.stp_consumaMP
(
	@XRPQLN	INT, --è il num rap qualità
	@XNRETI NVARCHAR(10), -- è il numUdc prog
	@XDTPRO	INT, -- data 8 cifre aaaammgg
	@XTRPRO NVARCHAR(5), -- turno
	@XDESMT NVARCHAR(3), -- destinatario int/terzista
	@XCDTER NVARCHAR(6) -- cod terzista
)
AS
	-- BOZZA DA VERIFICARE E COMPLETARE A CURA MAZZUCCONI!!!

	DECLARE @SQL AS NVARCHAR(1000)

	-- Faccio chiamata su AS400
	
	SET @SQL =  N' OPENQUERY( P65220DC,  ''UPDATE MAZZT_DTV3.XOGIX00F' 
	SET @SQL = @SQL + 'SET	XDTPRO =  ' + @XDTPRO  + ', XTRPRO = ' + @XTRPRO
	SET @SQL = @SQL + ', XDESMT = ''' + @XDESMT +''', XMATSC=''S'' , XCDTER = ''' + @XCDTER + ''' '
	SET @SQL = @SQL + 'WHERE XRPQLN =' +  @XRPQLN + ' AND XNRETI = ' + @XNRETI + ''' )'

	EXEC dbo.sp_executesql @SQL
	
RETURN
go

commit
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/***************************************
* STORED stp_processaCodaConsumoMP
*
* Processa la coda dei consumo di UDC di MP e chiaam procedura x salvataggio dato su AS400
*
* Steamware, S.E.L.
* mod: 2010.10.12
*
****************************************/
create PROCEDURE BatchQueue.stp_processaCodaConsumoMP
AS
	-- BOZZA DA VERIFICARE E COMPLETARE A CURA MAZZUCCONI!!!
	
	-- faccio una query con un cursore x processare tutte le righe da inviare ad AS400..
	DECLARE @UDC NVARCHAR(50)
	DECLARE @XRPQLN INT
	DECLARE @XNRETI NVARCHAR(10)
	DECLARE @XDTPRO	INT
	DECLARE @XTRPRO NVARCHAR(5)
	DECLARE @XDESMT NVARCHAR(3)
	DECLARE @XCDTER NVARCHAR(6)
	
	DECLARE @SqlCode NVARCHAR(1000)
	
	DECLARE UdcMpList CURSOR FOR
		SELECT UDC FROM AS400_BatchConsumoMP WHERE DataInvioAs IS NULL
	OPEN UdcMpList
	FETCH NEXT FROM UdcMpList 
	INTO @UDC
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  -- controllo ci sia la riga
	  IF (SELECT COUNT(UDC) FROM AS400_BatchConsumoMP WHERE UDC=@UDC) > 0
		BEGIN
			-- carico valori che mi servono
			SELECT	@XRPQLN=rq.nRapQual, @XNRETI=rq.ProgUDC, @XDTPRO=bcu.DataPrelFus, @XTRPRO=bcu.TurnoPrelFus, 
					@XDESMT=bcu.DestLega, @XCDTER=bcu.DestTerz 
					FROM AS400_BatchConsumoMP as bcu INNER JOIN RilPro.RapQual as rq ON bcu.UDC = rq.UDC
					WHERE bcu.UDC=@UDC
			-- compongo testo x exec
			SET @SqlCode = N'EXEC BatchQueue.stp_consumaMP ' + CAST(@XRPQLN AS NVARCHAR(50)) + ' ' + @XNRETI + ' ' + CAST(@XDTPRO AS NVARCHAR(8)) + ' ' + LTRIM(RTRIM(@XTRPRO)) + ' ' + LTRIM(RTRIM(@XDESMT)) + ' ' + LTRIM(RTRIM(@XCDTER))
			-- lo mostro
			print '%' + LTRIM(RTRIM(@SqlCode)) + '%'
			
			-- chiamo la stored ed eseguo invio
			--EXEC dbo.sp_executesql @SqlCode -- decommentare se va bene...
			FETCH NEXT FROM UdcMpList INTO @UDC
		END
	END
	CLOSE UdcMpList
	DEALLOCATE UdcMpList
	

RETURN
go

commit
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/***************************************
* STORED stp_processaCodaConsumoMP
*
* Processa la coda dei consumo di UDC di MP e chiaam procedura x salvataggio dato su AS400
*
* Steamware, S.E.L.
* mod: 2010.10.12
*
****************************************/
create PROCEDURE BatchQueue.stp_processaCodaConsumoMP
AS
	-- BOZZA DA VERIFICARE E COMPLETARE A CURA MAZZUCCONI!!!
	
	-- faccio una query con un cursore x processare tutte le righe da inviare ad AS400..
	DECLARE @UDC NVARCHAR(50)
	DECLARE @XRPQLN INT
	DECLARE @XNRETI NVARCHAR(10)
	DECLARE @XDTPRO	INT
	DECLARE @XTRPRO NVARCHAR(5)
	DECLARE @XDESMT NVARCHAR(3)
	DECLARE @XCDTER NVARCHAR(6)
	
	DECLARE @SqlCode NVARCHAR(1000)
	
	DECLARE UdcMpList CURSOR FOR
		SELECT UDC FROM AS400_BatchConsumoMP WHERE DataInvioAs IS NULL
	OPEN UdcMpList
	FETCH NEXT FROM UdcMpList 
	INTO @UDC
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  -- controllo ci sia la riga
	  IF (SELECT COUNT(UDC) FROM AS400_BatchConsumoMP WHERE UDC=@UDC) > 0
		BEGIN
			-- carico valori che mi servono
			SELECT	@XRPQLN=rq.nRapQual, @XNRETI=rq.ProgUDC, @XDTPRO=bcu.DataPrelFus, @XTRPRO=bcu.TurnoPrelFus, 
					@XDESMT=bcu.DestLega, @XCDTER=bcu.DestTerz 
					FROM AS400_BatchConsumoMP as bcu INNER JOIN RilPro.RapQual as rq ON bcu.UDC = rq.UDC
					WHERE bcu.UDC=@UDC
			-- compongo testo x exec
			SET @SqlCode = N'EXEC BatchQueue.stp_consumaMP ' + CAST(@XRPQLN AS NVARCHAR(50)) + ' ' + @XNRETI + ' ' + CAST(@XDTPRO AS NVARCHAR(8)) + ' ' + LTRIM(RTRIM(@XTRPRO)) + ' ' + LTRIM(RTRIM(@XDESMT)) + ' ' + LTRIM(RTRIM(@XCDTER))
			-- lo mostro
			print '%' + LTRIM(RTRIM(@SqlCode)) + '%'
			
			-- chiamo la stored ed eseguo invio
			--EXEC dbo.sp_executesql @SqlCode -- decommentare se va bene...
			FETCH NEXT FROM UdcMpList INTO @UDC
		END
	END
	CLOSE UdcMpList
	DEALLOCATE UdcMpList
	

RETURN
go

commit
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/***************************************
* STORED stp_accodaConsumoUdcMP
*
* Accoda alla tab Batch il consumo di un UDC
*
* Steamware, S.E.L.
* mod: 2010.10.10
*
****************************************/
alter PROCEDURE stp_accodaConsumoUdcMP
(
	@UDC NVARCHAR(50),
	@DataPrelFus DATETIME,
	@TurnoPrelFus CHAR(5),
	@DestLega CHAR(3),
	@DestTerz CHAR(6)	
)
AS

BEGIN TRAN

	-- registro movimento da riconciliare
	INSERT INTO AS400_BatchConsumoMP(ProgUDC, UDC, DataPrelFus, TurnoPrelFus, DestLega, DestTerz)
	SELECT ProgUdc, UDC, dbo.dateToAsFormat(@DataPrelFus), @TurnoPrelFus, @DestLega, @DestTerz FROM RilPro.RapQual WHERE UDC = @UDC

	-- aggiorno con data e turno di prelievo su tab RilPro (locale)
	UPDATE RilPro.RapQual
	SET DataPrelFus = dbo.dateToAsFormat(@DataPrelFus), TurnoPrelFus = @TurnoPrelFus
	WHERE UDC = @UDC

COMMIT TRAN

RETURN
go

commit
go










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