/***************************************
* 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
*
****************************************/
ALTER PROCEDURE [BatchQueue].[stp_processaCodaConsumoMP]
  (
    @ExecBatchInter BIT  -- 0 Eseguita in Batch , 1 in Interattivo
  )
AS
       -- BOZZA DA VERIFICARE E COMPLETARE A CURA MAZZUCCONI!!! PERCHE'??? NON VA VERSO AS400!!!

       -- faccio una query con un cursore x processare tutte le righe da inviare ad AS400..
       DECLARE @UDC NVARCHAR(50)       -- UDC SQL
       DECLARE @XRPQLN INT             -- UDC
       DECLARE @XNRETI NVARCHAR(10)    -- Progr UDC AS400
       DECLARE @XDTPRO INT             -- DataPrelievo
       DECLARE @XTRPRO NVARCHAR(5)     -- Turno Prelievo
       DECLARE @XDESMT NVARCHAR(3)     -- Destinazione Lega
       DECLARE @XCDTER NVARCHAR(6)     -- Cod. Terzista

   -- Dichiaro e carico il cursore
       DECLARE UdcMpList CURSOR LOCAL 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

                       -- BEGIN TRAN qui non va messa perchè se verso AS400 le transazioni non sono gestite
                       -- MANCA IL CONTROLLO SE L'AGGIORNAMENTO E' STATO OK
                       DECLARE @ScritturaASOK AS INT
                       EXECUTE @ScritturaASOK = BatchQueue.stp_consumaMP @XRPQLN  ,@XNRETI  ,@XDTPRO  ,@XTRPRO  ,@XDESMT  ,@XCDTER

                       -- registro la data del movimento BATCH solo se stp_consumaMP è ritorna OK
                       -- NON ANDAVA BENE PERCHE' SE SUCCESSIVA ALL' ERRORE LA ESEGUE CMQ
                       -- Così non aggiorna la tabella MA L'UTENTE NON SI ACCORGE DI NULLA!!!
                       IF @ScritturaASOK = 0
                          UPDATE AS400_BatchConsumoMP SET DataInvioAs = GETDATE() WHERE UDC = @UDC

                       FETCH NEXT FROM UdcMpList INTO @UDC
               END
       END
       CLOSE UdcMpList
       DEALLOCATE UdcMpList

   -- aggiunta per dare errore se schedulata S.Possidonio!!! GCARLO
   IF @ScritturaASOK <> 0 AND @ExecBatchInter = 0
    BEGIN
       DECLARE @ErrorMessage NVARCHAR(4000);
       DECLARE @ErrorSeverity INT;
       DECLARE @ErrorState INT;

       SELECT
       @ErrorMessage = ERROR_MESSAGE()+ ' '+ CONVERT( VARCHAR(10),ERROR_NUMBER()),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();

       RAISERROR (@ErrorMessage,  -- Message text.
              16, -- Severity.
              @ErrorState     -- State.
              );
    END
RETURN


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/***************************************
* STORED stp_prtCartMatPrimaByUDC
*
* ottiene il record del Cartellino Materia Prima dato l'UDC richiesto x la sstampa
*
****************************************/
alter PROCEDURE stp_prtCartMatPrimaByUDC
(
      @UDC VARCHAR(50)
)
AS
SELECT
        RQ.nRapQual,
        RQ.DataRapQual,
        RQ.ProgUDC,
        RQ.CodFor,
        RQ.DestTerz,
        RQ.CodLega,
        RQ.DataPrelFus,
        RQ.TurnoPrelFus,
        RQ.Qta,
        RQ.DestLega,
        RQ.LegaScaric,
        RQ.BenesQual,
        RQ.UDC,
        Fo.DescFornitore,
        Fo.DescFornitore2,
        Le.CodLega AS CodLega2,
        Le.DescLega,
        Le.DescLega2
FROM RilPro.RapQual AS RQ
LEFT JOIN RilPro.AnagFornitori AS Fo
  ON RQ.CodFor = Fo.CodFornitore
LEFT JOIN RilPro.AnagLeghe Le
  ON RQ.CodLega = 'L' + Le.CodLega
WHERE RQ.UDC = RTRIM(@UDC)                  -- 'UB31000000001'
RETURN
go

commit
go

set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

/***************************************
* STORED stp_rappQualConsumabili
*
* Ottiene l'elenco dei record di rapp qualità (e quindi degli UDC) di AS non ancora consumati per range di data di approvazione
*
* Steamware, S.E.L.
* mod: 2010.10.05
*
****************************************/
alter PROCEDURE stp_rappQualConsumabili
(
	@dataFrom DATETIME,
	@dataTo DATETIME
)

AS
	-- restituisce le righe richieste
    SELECT	nRapQual, DataRapQual, ProgUDC, CodFor, DestTerz, CodLega, DataPrelFus, TurnoPrelFus, Qta, DestLega, LegaScaric, BenesQual, UDC
	FROM	RilPro.RapQual 
	WHERE	((DataPrelFus IS NULL) OR (DataPrelFus = 0)) AND -- condizione sul NON aver fatto ancora il prelievo
			((DataRapQual >= dbo.dateToAsFormat(@dataFrom)) AND (DataRapQual < dbo.dateToAsFormat(@dataTo))) -- cond data RQ
			AND (ISNULL(UDC,'') <>'') -- non nullo UDC, quindi stampati...
		
RETURN
go

commit
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

-- Batch submitted through debugger: GMW_00290.sql|183|0|C:\Users\RottGian\AppData\Local\Temp\2\Temp1_GMW_00290.zip\GMW_00290.sql

/***************************************
* STORED stp_selDestListePrelByConditio
*
* Ottiene i destinatari delle liste di prelievo data la condizione indicata
*
* Steamware, S.E.L.
* mod: 2010.11.02
*
****************************************/
alter PROCEDURE stp_selDestListePrelByConditio
(
	@conditio NVARCHAR(20)
)
AS

IF (@conditio='01-PreFus')
BEGIN
	SELECT        CAST('*' AS NVARCHAR(6)) AS value, CAST('*' AS NVARCHAR(50)) AS label, '01-PreFus' AS conditio
END

ELSE IF (@conditio='02-PreCli') 
BEGIN
	SELECT        CAST(CodCliente AS NVARCHAR(6)) AS value, CAST(CodCliente AS NVARCHAR(6)) +'- ' +CAST(RagSociale AS NVARCHAR(50)) AS label, '02-PreCli' AS conditio
	FROM            RilPro.AnagClienti
	ORDER BY label
END

ELSE
BEGIN
	SELECT        CAST(CodTerzista AS NVARCHAR(6)) AS value, CAST(CodTerzista AS NVARCHAR(6))+ '- '+CAST(DescTerzista AS NVARCHAR(50)) AS label, '03-TerWip' AS conditio
	FROM            RilPro.AnagDepositi
	ORDER BY label
END

RETURN
go

commit
go


set xact_abort on
go

begin transaction
go

set ANSI_NULLS on
go

-- Batch submitted through debugger: GMW_00290.sql|183|0|C:\Users\RottGian\AppData\Local\Temp\2\Temp1_GMW_00290.zip\GMW_00290.sql

/***************************************
* STORED stp_selDestListePrelByConditio
*
* Ottiene i destinatari delle liste di prelievo data la condizione indicata
*
* Steamware, S.E.L.
* mod: 2010.11.02
*
****************************************/
alter PROCEDURE stp_selDestListePrelByConditio
(
	@conditio NVARCHAR(20)
)
AS

IF (@conditio='01-PreFus')
BEGIN
	SELECT        CAST('*' AS NVARCHAR(6)) AS value, CAST('*' AS NVARCHAR(50)) AS label, '01-PreFus' AS conditio
END

ELSE IF (@conditio='02-PreCli') 
BEGIN
	SELECT        CAST(CodCliente AS NVARCHAR(6)) AS value, CAST(CodCliente AS NVARCHAR(6)) +'- ' +CAST(RagSociale AS NVARCHAR(50)) AS label, '02-PreCli' AS conditio
	FROM            RilPro.AnagClienti
	ORDER BY label
END

ELSE
BEGIN
	SELECT        CAST(CodTerzista AS NVARCHAR(6)) AS value, CAST(CodTerzista AS NVARCHAR(6))+ '- '+CAST(DescTerzista AS NVARCHAR(50)) AS label, '03-TerWip' AS conditio
	FROM            RilPro.AnagDepositi
	ORDER BY label
END

RETURN
go

commit
go






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