XPS/XPS_data/SqlScript/V 1.2/sqlUpdate_150.sql

set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*************************************
* STORED PROCEDURE sp_schedaIdent
*       restituisce i dati x stampare 1 scheda di identificazione
*
* modif.:       S.E.L.
* il:           2012.02.13
**************************************/
alter PROCEDURE sp_schedaIdent
(
       @IdxObj VARCHAR(50)
)
AS

SELECT	Cod AS IdxObj, CodCliente, Cliente, DDT, DataDDT AS DDT_del, DataArrivo, descrizione AS CodProdotto, DataPrevista AS ConsegnaPrevista, Peso,
		CASE CodCli WHEN 'Fosf' THEN 'X' ELSE ' ' END AS FosfManganese, CASE CodCli WHEN 'Brun' THEN 'X' ELSE ' ' END AS Brunitura, 
		CASE CodCli WHEN 'Fosf_Tefl' THEN 'X' ELSE ' ' END AS FosfTeflon, CASE CodCli WHEN 'Sabb' THEN 'X' ELSE ' ' END AS Sabbiatura, ' ' AS F1, ' ' AS F2, 
        ' ' AS FosfZinco, ' ' AS Teflon, ' ' AS FosfPreVern, ' ' AS PzRoto, ' ' AS Zinchlorex, ' ' AS PeekVicote, CodCli
FROM    v_ULP
WHERE   (Cod = @IdxObj)

RETURN
go

commit;
go

set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*---------------------------------------------------------
* TRIGGER controllo aggiornamento extKey
*
* - in caso di cambio extKey (ciclo lavorazione), SE è Obj master, 
*   aggiorna a cascata i child 
---------------------------------------------------------*/

create TRIGGER trg_extKeyUpd
ON IstObj
FOR UPDATE

AS
IF UPDATE (ExtKey) 

DECLARE @isMasterObj BIT
SET		@isMasterObj = ( SELECT CASE WHEN SUBSTRING(IdxObj, LEN(idxobj),1) = 'D' THEN 1 ELSE 0 END FROM inserted )

-- verifico se sia un obj master (=finisce x "D")
IF(@isMasterObj = 1)
BEGIN
	DECLARE @IdxObj NVARCHAR(50)
	DECLARE	@ExtKey NVARCHAR(50)
	
	SELECT @IdxObj = IdxObj, @ExtKey = ExtKey FROM inserted 
	
	-- aggiorno child!
	UPDATE	IstObj
	SET		ExtKey	= @ExtKey
	WHERE	IdxObj LIKE REPLACE(@IdxObj,'D','')+'%' AND IdxObj <> @IdxObj
	
END
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*---------------------------------------------------------
* TRIGGER controllo aggiornamento extKey
*
* - in caso di cambio extKey (ciclo lavorazione), SE è Obj master, 
*   aggiorna a cascata i child 
---------------------------------------------------------*/

alter TRIGGER trg_extKeyUpd
ON IstObj
FOR UPDATE

AS
IF UPDATE (ExtKey) 

DECLARE @isMasterObj BIT
SET		@isMasterObj = ( SELECT CASE WHEN SUBSTRING(IdxObj, LEN(idxobj),1) = 'D' THEN 1 ELSE 0 END FROM inserted )

-- verifico se sia un obj master (=finisce x "D")
IF(@isMasterObj = 1)
BEGIN
	DECLARE @IdxObj NVARCHAR(50)
	DECLARE	@ExtKey NVARCHAR(50)
	DECLARE @dataMod DATETIME

	-- calcolo valori
	SET @dataMod = GETDATE()
	SELECT @IdxObj = IdxObj, @ExtKey = ExtKey FROM inserted 
	
	-- aggiorno child!
	UPDATE	IstObj
	SET		ExtKey	= @ExtKey,
			dataMod	= @dataMod
	WHERE	IdxObj LIKE REPLACE(@IdxObj,'D','')+'%' AND IdxObj <> @IdxObj
	
END
go

commit;
go


set xact_abort on;
go

begin transaction;
go

alter table IstObj add
  emailReq bit constraint DF_IstObj_emailReq default ((0)),
  emailSent datetime;
go


RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*---------------------------------------------------------
* TRIGGER controllo aggiornamento extKey
*
* - in caso di cambio extKey (ciclo lavorazione), SE è Obj master, 
*   aggiorna a cascata i child 
---------------------------------------------------------*/
alter TRIGGER trg_extKeyUpd
ON IstObj
FOR UPDATE

AS
IF UPDATE (ExtKey) 

UPDATE	child
SET		ExtKey	= mother.ExtKey,
		dataMod	= mother.dataMod
FROM	inserted AS mother INNER JOIN IstObj as child ON mother.IdxObj = child.IdxObjMamma
WHERE	mother.IdxObjMamma IS NULL
go

commit;
go


--- update emailReq a zero x esistenti...
update IstObj 
set emailReq = 0
where emailReq is null


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/*************************************
* STORED PROCEDURE sp_creaSetObj
* 	crea un set iniziale di valori da tracciare x la commessa
*
* modif.:	S.E.L.
* il:		2008.10.17
**************************************/
alter PROCEDURE sp_creaSetObj
(
	@IdxObj VARCHAR(50),
	@CodSet VARCHAR(50),
	@CodCliente VARCHAR(50),
	@NumDDT VARCHAR(50),
	@dataDDT as DATETIME,
	@Nome VARCHAR(50),
	@UserName VARCHAR(50),
	@note VARCHAR(50),
	@emailReq BIT
)
AS

DECLARE @livello VARCHAR(50)
DECLARE @IdxObjMamma VARCHAR(50)
DECLARE @numRow INT
DECLARE @adesso DATETIME
DECLARE @depth INT

BEGIN tran

/* creazione oggetti in qta minima */

	SET @adesso = GETDATE()
	SET @depth = 1
	-- parto da obj top #.
	SET @livello = '#.'
	INSERT INTO IstObj(IdxObj, Livello,ExtKey , DataRif, Rev,CodObj, Posizione,Nome ,UserCreaz,DataCreaz,UserMod,DataMod,CodStato,Note,CodCliente,emailReq)
	SELECT     @IdxObj + Object2Set.CodObj AS idx, @depth, @Nome, @dataDDT, 0, Object2Set.CodObj, 1, @NumDDT AS nome, 
						  @UserName AS userCreaz, @adesso AS Expr6, @UserName AS userMod, @adesso AS Expr8, V_statoStartFam.CodStato,@note,@CodCliente, @emailReq
	FROM         AnagFamStati INNER JOIN
						  AnagObj ON AnagFamStati.FamStato = AnagObj.FamStato INNER JOIN
						  Object2Set ON AnagObj.CodObj = Object2Set.CodObj INNER JOIN
						  V_statoStartFam ON AnagFamStati.FamStato = V_statoStartFam.FamStato
	WHERE     (Object2Set.Posizione = @livello) 
	-- salvo idx mamma...
	SET @IdxObjMamma = (SELECT IdxObj FROM IstObj WHERE DataCreaz=@adesso  AND Livello=@depth)
	-- ora creo gli obj dei livelli successivi... aggiungo "#." al livello e conto se ce ne solo con ciclo while...
	SET @livello = @livello + '#.'
	SET @numRow = (SELECT     count(IdxSet)
	FROM         Object2Set
	WHERE     (Posizione = @livello))
	WHILE(@numRow > 0)
		BEGIN
			SET @depth = @depth +1
			-- inserisco valori child
			INSERT INTO IstObj(IdxObj, Livello, ExtKey, DataRif, Rev,CodObj, Posizione, Nome,UserCreaz,DataCreaz,UserMod,DataMod,CodStato,Note,CodCliente, IdxObjMamma, emailReq)
	SELECT     @IdxObj  + Object2Set.CodObj +'01' AS idx, @depth, @Nome, @dataDDT, 0, Object2Set.CodObj, 1, @NumDDT , 
						  @UserName AS Expr5, @adesso, @UserName AS Expr7, @adesso AS Expr8, v_statoStartFam.CodStato,@note,@CodCliente, @IdxObjMamma, @emailReq
	FROM         AnagFamStati INNER JOIN
						  AnagObj ON AnagFamStati.FamStato = AnagObj.FamStato INNER JOIN
						  Object2Set ON AnagObj.CodObj = Object2Set.CodObj INNER JOIN
						  v_statoStartFam ON AnagFamStati.FamStato = v_statoStartFam.FamStato
	WHERE     (Object2Set.Posizione = @livello)
			-- salvo idx mamma...
			SET @IdxObjMamma = (SELECT IdxObj FROM IstObj WHERE DataCreaz=@adesso AND Livello=@depth)
			-- aggiorno conteggio...
			SET @livello = @livello + '#.'
			SET @numRow = (SELECT     count(IdxSet)
			FROM         Object2Set
			WHERE     (Posizione = @livello))
		END

/* creazione fasi std per ogni oggetto creato */


COMMIT tran

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

alter table AnagClienti add
  email nvarchar(500) constraint DF_AnagClienti_email default ('');
go

update AnagClienti set email=('');
go

commit;
go


set xact_abort on;
go

begin transaction;
go

alter table IstObj drop
  constraint FK_IstObj_AnagClienti ;
go

alter table AnagClienti drop
  constraint DF_AnagClienti_email ;
go

exec sp_rename 'PK_AnagClienti', 'tmp__PK_AnagClienti', 'OBJECT';
go

exec sp_rename 'AnagClienti', 'tmp__AnagClienti_0', 'OBJECT';
go

create table AnagClienti(
  CodCliente     nvarchar(50)  not null constraint PK_AnagClienti primary key,
  RagioneSociale nvarchar(50),
  piva           nvarchar(20),
  email          nvarchar(500) constraint DF_AnagClienti_email default (''),
  indirizzo      nvarchar(50),
  cap            nvarchar(5),
  localita       nvarchar(50),
  provincia      nvarchar(5)
);
go

insert into AnagClienti(CodCliente,RagioneSociale,email) select CodCliente,RagioneSociale,email from tmp__AnagClienti_0;
go

drop table tmp__AnagClienti_0;
go

alter table IstObj add
  constraint FK_IstObj_AnagClienti foreign key(CodCliente) references AnagClienti(CodCliente);
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/***************************************
* STORED stp_AnagCliUpdate
*
* aggiorna dati anagrafica clienti
*
* Steamware, S.E.L.
* mod: 2012.07.12
*
****************************************/
create PROCEDURE stp_AnagCliUpdate
(
	@Original_CodCliente	NVARCHAR(50),
	@RagioneSociale			NVARCHAR(50),
	@piva					NVARCHAR(20),
	@email					NVARCHAR(500),
	@indirizzo				NVARCHAR(50),
	@cap					NVARCHAR(5),
	@localita				NVARCHAR(50),
	@provincia				NVARCHAR(5)
)
AS

UPDATE	dbo.AnagClienti 
SET		RagioneSociale = @RagioneSociale, 
		piva = @piva,
		email = @email, 
		indirizzo = @indirizzo, 
		cap = @cap, 
		localita = @localita, 
		provincia = @provincia 
WHERE	(CodCliente = @Original_CodCliente)

RETURN
go

commit;
go

set xact_abort on
go

begin transaction
go

INSERT INTO dbo.Eventi
  VALUES (N'07', N'BC', N'Invio email', N'act')
go

commit transaction
go


set xact_abort on
go

begin transaction
go

INSERT INTO dbo.TraEv2Stati
  VALUES (N'E_BC', N'BC', N'05', N'07', N'*', N'07', N'sendMail', N'Invia Email')
go

commit transaction
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/***************************************
* STORED stp_AnagCliGetByKey
*
* ottiene record anagrafica clienti da chiave
*
* Steamware, S.E.L.
* mod: 2012.07.12
*
****************************************/
create PROCEDURE stp_AnagCliGetByKey
(
	@CodCliente	NVARCHAR(50)
)
AS

SELECT	*
FROM	AnagClienti 
WHERE	CodCliente = @CodCliente

RETURN
go

commit;
go


set xact_abort on;
go

begin transaction;
go

set ANSI_NULLS on;
go

/***************************************
* STORED stp_IstObj_updateEmailSent
*
* aggiorna email sent con data/ora correnti
*
* Steamware, S.E.L.
* mod: 2011.10.27
*
****************************************/
create PROCEDURE stp_IstObj_updateEmailSent
(
	@IdxObj NVARCHAR(50)
)
AS

UPDATE	IstObj
SET		emailsent	= GETDATE()
WHERE	IdxObj		= @IdxObj

RETURN
go

commit;
go





-- update email a Vuota!
update AnagClienti set email=''
where email is null

























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