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(@numOdette > 0)
            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_prtCartLiquidi_F10ByUDC
*
* ottiene il record del Cartellino Liquidi dato l'UDC richiesto x la stampa
*
* Steamware, S.E.L.
* mod: 2011.12.19
*
****************************************/
alter PROCEDURE stp_prtCartLiquidi_F10ByUDC
(
	@UDC NVARCHAR(50)
)
AS

	-- estraggo i trattamenti nelle note ( x max 10 trattamenti )
	DECLARE @tmp		NVARCHAR(500)
	DECLARE @NoteTratt	NVARCHAR(500)
	SET @tmp = ''
	SELECT  TOP 10 @tmp = @tmp + RIGHT(UDC_parent,8) + ' - ' from RelazUDC WHERE UDC_child = @UDC

	SET	@NoteTratt = (SELECT SUBSTRING(@tmp, 0, LEN(@tmp)))

	-- ora seleziono i dati veri e propri
	SELECT
		ISNULL(ElencoCartellini.UDC, N'0000') AS UDC, ISNULL(ElencoCartellini.CodCS, N'') AS CodCS, ISNULL(ElencoCartellini.CodCliente, N'') AS codcliente, 
		ISNULL(ElencoCartellini.RagSociale, N'') AS ragsociale, ISNULL(ElencoCartellini.Particolare, N'') AS particolare,
		ISNULL(ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
		ISNULL(ElencoCartellini.Esponente, N'') AS esponente, ISNULL(ElencoCartellini.CodImpianto, N'') AS CodImpianto,
		ISNULL(ElencoCartellini.DescImpianto, N'') AS DescImpianto, ISNULL(ElencoCartellini.CodStampo, N'') AS codstampo, 
		ISNULL(ElencoCartellini.Figura, N'') AS figura, ISNULL(ElencoCartellini.DataFus, N'') AS datafus,
		ISNULL(ElencoCartellini.TurnoFus, N'') AS turnofus, ISNULL(ElencoCartellini.CodImballo, N'') AS CodImballo, 
		ISNULL(ElencoCartellini.CodSoggetto, N'') AS CodSoggetto, ISNULL(ElencoCartellini.NumCont, N'') AS NumCont,
		ISNULL(ElencoCartellini.Tara, N'') AS tara, ISNULL(ElencoCartellini.Qta, N'') AS qta, ISNULL(ElencoCartellini.CodStato, N'') AS CodStato, 
		ISNULL(ElencoCartellini.IdxPosizione, N'') AS IdxPosizione,
		ISNULL(ElencoCartellini.PesoTot, N'') AS PesoTot, ISNULL(ElencoCartellini.PesoCad, N'') AS PesoCad, ElencoCartellini.CreateDate, 
		ElencoCartellini.ModDate, ISNULL(RilPro.AnagOperatori.Cognome, N'') AS cognome,
		ISNULL(RilPro.AnagOperatori.Nome, N'') AS nome, ISNULL(AnagStatiProdotto.DescStato, N'') AS DescStato, 
		ISNULL(RilPro.AnagImballi.DescImballo, N'') AS DescImballo, RilPro.AnagParticolari.CodFamiglia, RilPro.AnagFamiglie.DescFamiglia,
		ISNULL(ElencoCartellini.Note, N'') AS Note, ISNULL(@NoteTratt, N'') AS NoteTratt
	FROM
		RilPro.AnagParticolari 
	LEFT OUTER JOIN
		RilPro.AnagFamiglie 
	ON
		RilPro.AnagParticolari.CodFamiglia = RilPro.AnagFamiglie.CodFamiglia 
	RIGHT OUTER JOIN
		ElencoCartellini 
	ON
		RilPro.AnagParticolari.Particolare = ElencoCartellini.Particolare 
	LEFT OUTER JOIN
		AnagStatiProdotto 
	ON
		ElencoCartellini.CodStato = AnagStatiProdotto.CodStato 
	LEFT OUTER JOIN
		RilPro.AnagOperatori 
	ON
		ElencoCartellini.CodSoggetto = RilPro.AnagOperatori.CodSoggetto 
	LEFT OUTER JOIN
		RilPro.AnagImballi 
	ON
		ElencoCartellini.CodImballo = RilPro.AnagImballi.CodImballo 
	WHERE
		(ElencoCartellini.UDC = @UDC)
 

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/***************************************
* STORED stp_prtCartLiquidi_F18ByUDC
*
* ottiene il record del Cartellino Liquidi dato l'UDC richiesto x la stampa
*
* Steamware, S.E.L.
* mod: 2011.12.19
*
****************************************/
alter PROCEDURE stp_prtCartLiquidi_F18ByUDC
(
	@UDC NVARCHAR(50)
)
AS

    -- estraggo i trattamenti nelle note ( x max 10 trattamenti )
    -- NON SERVE PER I CARTELLINI F18
	DECLARE @tmp		NVARCHAR(500)
	DECLARE @NoteTratt	NVARCHAR(500)
	SET @tmp = ''
	SELECT  TOP 10 @tmp = @tmp + RIGHT(UDC_parent,8) + ' - ' from RelazUDC WHERE UDC_child = @UDC

	SET	@NoteTratt = (SELECT SUBSTRING(@tmp, 0, LEN(@tmp)))

	-- ora seleziono i dati veri e propri
	SELECT
		ElencoCartellini.UDC, ElencoCartellini.CodCS, ISNULL(ElencoCartellini.CodCliente, N'') AS codcliente, 
		ISNULL(ElencoCartellini.RagSociale, N'') AS ragsociale, ISNULL(ElencoCartellini.Particolare, N'') AS particolare,
		ISNULL(ElencoCartellini.DescParticolare, N'') AS DescParticolare, ISNULL(ElencoCartellini.DisegnoGrezzo, N'') AS DisegnoGrezzo, 
		ISNULL(ElencoCartellini.Esponente, N'') AS esponente, ISNULL(ElencoCartellini.CodImpianto, N'') AS CodImpianto,
		ISNULL(ElencoCartellini.DescImpianto, N'') AS DescImpianto, ISNULL(ElencoCartellini.CodStampo, N'') AS codstampo, 
		ISNULL(ElencoCartellini.Figura, N'') AS figura, ISNULL(ElencoCartellini.DataFus, N'') AS datafus,
		ISNULL(ElencoCartellini.TurnoFus, N'') AS turnofus, ISNULL(ElencoCartellini.CodImballo, N'') AS CodImballo, 
		ISNULL(ElencoCartellini.CodSoggetto, N'') AS CodSoggetto, ISNULL(ElencoCartellini.NumCont, N'') AS NumCont,
		ISNULL(ElencoCartellini.Tara, N'') AS tara, ISNULL(ElencoCartellini.Qta, N'') AS qta, ISNULL(ElencoCartellini.CodStato, N'') AS CodStato, 
		ISNULL(ElencoCartellini.IdxPosizione, N'') AS IdxPosizione,
		ISNULL(ElencoCartellini.PesoTot, N'') AS PesoTot, ISNULL(ElencoCartellini.PesoCad, N'') AS PesoCad, ElencoCartellini.CreateDate, 
		ElencoCartellini.ModDate, ISNULL(RilPro.AnagOperatori.Cognome, N'') AS cognome,
		ISNULL(RilPro.AnagOperatori.Nome, N'') AS nome, ISNULL(AnagStatiProdotto.DescStato, N'') AS DescStato, 
		ISNULL(RilPro.AnagImballi.DescImballo, N'') AS DescImballo, RilPro.AnagParticolari.CodFamiglia, RilPro.AnagFamiglie.DescFamiglia,
		ISNULL(ElencoCartellini.Note, N'') AS Note, @NoteTratt AS NoteTratt
	FROM
		RilPro.AnagParticolari 
	LEFT OUTER JOIN
		RilPro.AnagFamiglie 
	ON
		RilPro.AnagParticolari.CodFamiglia = RilPro.AnagFamiglie.CodFamiglia 
	RIGHT OUTER JOIN
		ElencoCartellini 
	ON
		RilPro.AnagParticolari.Particolare = ElencoCartellini.Particolare 
	LEFT OUTER JOIN
		AnagStatiProdotto 
	ON
		ElencoCartellini.CodStato = AnagStatiProdotto.CodStato 
	LEFT OUTER JOIN
		RilPro.AnagOperatori 
	ON
		ElencoCartellini.CodSoggetto = RilPro.AnagOperatori.CodSoggetto 
	LEFT OUTER JOIN
		RilPro.AnagImballi 
	ON
		ElencoCartellini.CodImballo = RilPro.AnagImballi.CodImballo 
	WHERE
		(ElencoCartellini.UDC = @UDC)
 

RETURN
go

commit;
set ANSI_NULLS on;
go

/***************************************
* FUNCTION f_onlyNumbers
*
* elimina tutti i caratteri alfabetici dalal stringa
*
* Steamware, S.E.L.
* mod: 2010.03.19
*
****************************************/
create FUNCTION f_onlyNumbers (@string NVARCHAR(255))
RETURNS NVARCHAR(255) AS  
BEGIN

	DECLARE @answ NVARCHAR(255)
	-- sostituisco TUTTE le lettere dell'alfabeto
	SET	@answ = (SELECT REPLACE(@string, 'a', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'b', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'c', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'd', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'e', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'f', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'g', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'h', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'i', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'j', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'k', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'l', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'm', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'n', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'o', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'p', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'q', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'r', ''))
	SET	@answ = (SELECT REPLACE(@answ, 's', ''))
	SET	@answ = (SELECT REPLACE(@answ, 't', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'u', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'v', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'w', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'x', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'y', ''))
	SET	@answ = (SELECT REPLACE(@answ, 'z', ''))
	
	RETURN @answ

END
go


go




set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/***************************************
* STORED stp_spostaUdc
*
* sposta un UDC, salvandone la posizione precedente (se esiste) nella tab storica
*
* Steamware, S.E.L.
* mod: 2010.06.11
*
****************************************/
alter PROCEDURE stp_spostaUdc
(
	@CodCS VARCHAR(2),
	@UDC VARCHAR(50),
	@IdxCellaTo INT,
	@resetRLP BIT
)
AS

BEGIN TRAN
	
	-- DICHIARAZIONI iniziali variabili
	DECLARE @IdxCellaFrom INT
	DECLARE @capienza INT
	DECLARE @numUdc INT
	
	-- controllo cella di partenza
	SET		@IdxCellaFrom = (SELECT IdxCella FROM PosizioneUdcCorrente WHERE UDC = @UDC)
	-- controllo se la cella di dest vada indicata come piena
	SET		@capienza = (
							SELECT tc.Capienza 
								FROM TipoCella TC INNER JOIN Celle c ON TC.IdxTipoCella=c.IdxTipoCella
								WHERE c.IdxCella = @IdxCellaFrom	
						)
	SET @numUdc = (SELECT COUNT(*) FROM PosizioneUdcCorrente WHERE IdxCella = @IdxCellaFrom)
	-- se capienza raggiunta aggiorno...
	IF(@numUdc -1 < @capienza)
		BEGIN
			EXEC stp_celle_updPiena @IdxCellaFrom, 0
		END
		
	-- cancello posizione occupata
	DELETE
	FROM	PosizioneUdcCorrente
	WHERE	UDC = @UDC
	
	-- SE RICHIESTO cancello eventuali righe liste di prelievo FUSI per l'UDC
	IF(@resetRLP = 1)
	BEGIN
		DELETE FROM RigheListePrelievo
			WHERE UDC = @UDC AND CodLista IN ( 
				SELECT CodLista FROM ElencoListePrelievo 
				--WHERE CodTipoLista='01-PreFus'
				WHERE CodStatoLista < 4
			)
	END

	
	
	-- creo una nuova posizione per l'UDC
	INSERT INTO PosizioneUdcCorrente(UDC, IdxCella, CodCS, DataRif)
	VALUES(@UDC, @IdxCellaTo, @CodCS, GETDATE())
	
	-- controllo se la cella di dest vada indicata come piena
	SET		@capienza = (
							SELECT tc.Capienza 
								FROM TipoCella TC INNER JOIN Celle c ON TC.IdxTipoCella=c.IdxTipoCella
								WHERE c.IdxCella = @IdxCellaTo	
						)
	SET @numUdc = (SELECT COUNT(*) FROM PosizioneUdcCorrente WHERE IdxCella = @IdxCellaTo)
	-- se capienza raggiunta aggiorno...
	IF(@numUdc >= @capienza)
		BEGIN
			EXEC stp_celle_updPiena @IdxCellaTo, 1
		END
	
COMMIT TRAN

RETURN
go

commit;
go




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