Samuele Locatelli
524 lines
9.9 KiB
Transact-SQL
524 lines
9.9 KiB
Transact-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
|