set xact_abort on
go

begin transaction
go

INSERT INTO dbo.AnagTipoEvento
  VALUES (N'MagPreOdet', N'Prelievo Standard Odette')
go

commit transaction
go



set xact_abort on
go

begin transaction
go

INSERT INTO dbo.TipoListaPrelievo
  VALUES (N'04-Odette', N'Lista di Prelievo per Odette TK', N'LPO010101', N'MagPreOdet', 0, 1, N'P')
go

commit transaction
go

set xact_abort on
go

begin transaction
go

DELETE FROM dbo.TipoListaPrelievo WHERE CodTipoLista=N'04-OdetTK'
go

commit transaction
go


set xact_abort on
go

begin transaction
go

DELETE FROM dbo.TabTranPosizEventi WHERE CodEvento=N'MagPreGbox' and IdxPosizione=24
INSERT INTO dbo.TabTranPosizEventi
  VALUES (N'MagPreOdet', 24, 200, 0, NULL, N'LDP Odette')
INSERT INTO dbo.TabTranPosizEventi
  VALUES (N'MagPreOdet', 200, 24, 0, NULL, N'LDP Odette - Reset')
go

commit transaction
go


set xact_abort on
go

begin transaction
go

UPDATE dbo.TabTranPosizEventi SET 
  Note=N'LDP a Cliente'
  WHERE CodEvento=N'MagPreFin' and IdxPosizione=24
INSERT INTO dbo.TabTranPosizEventi
  VALUES (N'MagPreFin', 200, 24, 0, NULL, N'LDP a Cliente - Reset')
go

commit transaction
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*****************************************
* STORED stp_ODETTE_upsertUdc
*
* effettua upsert x l'UDC (ovver crea nuovo solo se non c'è...)
*
* Steamware, S.E.L.
* mod: 2011.04.28
*
****************************************/
alter PROCEDURE stp_ODETTE_upsertUdc
(
	@UDC NVARCHAR(50),
	@CodCS VARCHAR(2),
	@numBolla NVARCHAR(6),
	@dataBolla NVARCHAR(8),
	@CodMag NVARCHAR(2),
    @GrpBolla NVARCHAR(1),
    @Particolare NVARCHAR(15),
    @CodLista NVARCHAR(12),
	@IndStabFrom NVARCHAR(50),
	@qtaCont NVARCHAR(50)
)
AS


-- DECLARE iniziali x gestione contatori odette
DECLARE @Flusso AS VARCHAR(2)
DECLARE @Anno AS VARCHAR(2)
DECLARE @numOdette AS INT
DECLARE @nextOdette AS INT
     
------------------------------------------------------------------------------------------------------
-- Fix flusso ed anno x 'OD' e '00' (contatore non spezzato su anno...
------------------------------------------------------------------------------------------------------
SET @Flusso = 'OD'
SET @Anno = '00'
           
------------------------------------------------------------------------------------------------------
-- Contatore Odette
------------------------------------------------------------------------------------------------------
-- controllo se ci sia già un odette per company / flusso / anno
BEGIN TRAN
      -- cerco nella tab contatori UDC l'ultimo valido
      /*
      SET @numOdette = (
            SELECT		COUNT(*)
            FROM		Odette
            WHERE		CodCS       =     @CodCS
      )
      */
      -- controllo se record c'è...
      IF EXISTS(
                SELECT		*
                FROM		ContatoriUdc
                WHERE		CodCS       = @CodCS
                AND         Flusso      = @Flusso
                AND         Anno        = @Anno
                )
            BEGIN
                  -- se c'è incremento di 1 in tab
                  UPDATE	ContatoriUdc
                  SET		LastIdx		= LastIdx  + 1
                  WHERE		CodCS       = @CodCS
                  AND       Flusso      = @Flusso
                  AND       Anno        = @Anno
            END
      ELSE
            BEGIN
                  -- lo creo!
                  INSERT INTO ContatoriUdc(CodCS, Flusso, Anno, LastIdx)
                  VALUES (@CodCS, @Flusso, @Anno, 1)
            END        
      -- aggiorno numero UDC
      SET @numOdette = (
            SELECT      LastIdx
            FROM		ContatoriUdc
            WHERE		CodCS       = @CodCS
            AND         Flusso      = @Flusso
            AND         Anno        = @Anno
      )
COMMIT TRAN

-- cerco se ci sia già la riga Odette
DECLARE @udcTrovati AS INT
SET		@udcTrovati = ( SELECT COUNT(*) FROM Odette WHERE CampoUDC = @UDC )

-- controllo se esista già... se non c'è creo
IF (@udcTrovati = 0)
	BEGIN
		INSERT INTO Odette(CampoUDC, StatoOk, CodCS)
		VALUES(@UDC, 0, @CodCS)
	END

-- calcolo valori x update
DECLARE @DataB AS NVARCHAR(7)
SET		@DataB = ( SELECT 'D'+ SUBSTRING(@dataBolla, 3, 6) )
DECLARE @udcShort NVARCHAR(10)
SET		@udcShort = dbo.f_onlyNumbers(@UDC)
	
-- update valori (sovrascrivo...)
UPDATE	Odette
SET		Campo3_2	= @numBolla,
		Campo3_3	= @numBolla,
		Campo4_2	= @IndStabFrom,
		Campo9_2	= @qtaCont,
		Campo9_3	= @qtaCont,
		Campo13_2	= @DataB,
		Campo15_2	= @numOdette,
		Campo15_3	= @numOdette,
		Campo16_2	= @udcShort, --@UDC, -- DA VERICARE, mettere UDC da anno in poi?!? @udcShort calcolato sopra? magari è sbagliato... 
		CodMag		= @CodMag,
		GrpBolla	= @GrpBolla,
		DataBolla	= CAST(@dataBolla AS NUMERIC(8,0)),
		NumBolla	= CAST(@numBolla AS NUMERIC(6,0)),
		Particolare	= @Particolare,
		CodLista	= @CodLista
WHERE	CampoUDC	= @UDC

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/***************************************
* 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='01-PreAnim')
BEGIN
	SELECT        CAST(CodCliente AS NVARCHAR(6)) AS value, CAST(CodCliente AS NVARCHAR(6)) +'- ' +CAST(RagSociale AS NVARCHAR(50)) AS label, '01-PreAnim' AS conditio
	FROM            RilPro.AnagClienti
	ORDER BY label
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 IF (@conditio='04-Odette') 
BEGIN
	SELECT        CAST(CodCliente AS NVARCHAR(6)) AS value, CAST(CodCliente AS NVARCHAR(6)) +'- ' +CAST(RagSociale AS NVARCHAR(50)) AS label, '04-Odette' AS conditio
	FROM            RilPro.AnagClienti
	ORDER BY label
END

ELSE IF (@conditio='05-TerAn') 
BEGIN
	SELECT        CAST(CodTerzista AS NVARCHAR(6)) AS value, CAST(CodTerzista AS NVARCHAR(6))+ '- '+CAST(DescTerzista AS NVARCHAR(50)) AS label, '05-TerAn' AS conditio
	FROM            RilPro.AnagDepositi
	ORDER BY label
END

ELSE IF (@conditio='03-TerWip') 
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

ELSE   -- Se non configurata prendo elenco destinatari esterni
BEGIN
	SELECT       '-99999' AS value, 'STORED [stp_selDestListePrelByConditio] da configurare!!!' AS label, '999-ND' AS conditio
	FROM            RilPro.AnagDepositi
	ORDER BY label
END

RETURN
go

commit;
go









-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(533, GETDATE())
GO
SELECT TOP 5 * FROM LogUpdateDb ORDER BY Versione DESC
GO