Samuele E. Locatelli
80 lines
3.1 KiB
Transact-SQL
80 lines
3.1 KiB
Transact-SQL
/*************************************
|
|
* STORED PROCEDURE sp_caricaUpdateMAcchine
|
|
* carica impianti/macchien da tab temporanea
|
|
*
|
|
* modif.: S.E.L. - 2015.02.04
|
|
**************************************/
|
|
CREATE PROCEDURE sp_caricaUpdateMAcchine
|
|
AS
|
|
|
|
SET XACT_ABORT ON;
|
|
|
|
DECLARE @Id AS INT = 0;
|
|
|
|
BEGIN TRAN
|
|
|
|
-- reset idx tabelle
|
|
SELECT @Id = ISNULL(MAX(idxImpianto),0) FROM dbo.AnagImpianti
|
|
DBCC CHECKIDENT ('AnagImpianti', RESEED, @Id);
|
|
|
|
SELECT @Id = ISNULL(MAX(idxMacchina),0) FROM dbo.AnagMacchine
|
|
DBCC CHECKIDENT ('AnagMacchine', RESEED, @Id);
|
|
|
|
SELECT @Id = ISNULL(MAX(idxFamMacchine),0) FROM dbo.AnagFamMacchine
|
|
DBCC CHECKIDENT ('AnagFamMacchine', RESEED, @Id);
|
|
|
|
-- cerco se ci sono nuovi impianti e lo carico
|
|
INSERT INTO dbo.AnagImpianti(codImpianto, nomeImpianto, tipoImpianto, funzDal, funzAl)
|
|
SELECT 'IT' + dbo.f_padLeft(ROW_NUMBER() OVER (ORDER BY reparto), 2,'0') as codImpianto,
|
|
reparto as nomeImpianto,
|
|
'Reparto' as tipoImpianto,
|
|
CONVERT(DATETIME, '01-01-'+CAST(MIN(anno) AS NVARCHAR(4)), 110) as funzDal, '9999-12-31' as funzAl
|
|
FROM tmp_importaMacchine tmp
|
|
LEFT OUTER JOIN AnagImpianti ai ON tmp.reparto = ai.nomeImpianto
|
|
WHERE ai.codImpianto IS NULL
|
|
GROUP BY reparto
|
|
|
|
-- carico famiglie nuove e le carico
|
|
INSERT INTO dbo.AnagFamMacchine
|
|
SELECT DISTINCT Gruppo FROM dbo.tmp_importaMacchine AS i
|
|
LEFT JOIN dbo.AnagFamMacchine AS f ON RTRIM(i.Gruppo) = RTRIM(f.nomeFamMacch)
|
|
WHERE f.nomeFamMacch IS NULL
|
|
|
|
-- cerco se ci sono nuove macchine e le carico
|
|
INSERT INTO dbo.AnagMacchine
|
|
(codMacchina, nomeMacchina, modello, funzDal, idxImpianto, funzAl, kgPeso, kwConsumo, anno, matricola)
|
|
SELECT ISNULL(i.idGest, N'--') AS codMacchina,
|
|
ISNULL(i.denominazione, N'nd') AS nomeMacchina,
|
|
ISNULL(i.costruttore, N'nd') AS modello,
|
|
CAST(CAST(ISNULL(i.anno, 1900) AS varchar) + '-01-01 12:00:00' AS datetime) AS funzDal,
|
|
AnagImpianti.idxImpianto,
|
|
CAST('9999-12-31' AS datetime) AS fine,
|
|
1 AS kg,
|
|
1 AS kw,
|
|
ISNULL(i.anno, 1900) AS anno,
|
|
i.matricola
|
|
FROM dbo.tmp_importaMacchine AS i
|
|
INNER JOIN AnagImpianti ON i.reparto = AnagImpianti.nomeImpianto
|
|
LEFT JOIN dbo.AnagMacchine AS am ON i.idGest = am.codMacchina
|
|
WHERE am.codMacchina IS NULL -- solo nuove
|
|
|
|
-- carico Macchine2FamMacchine - solo x macchine nuove ( NON faccio update )
|
|
INSERT INTO dbo.Macchine2FamMacchine
|
|
SELECT m.idxMacchina, f.idxFamMacchine FROM dbo.AnagMacchine AS m
|
|
INNER JOIN dbo.tmp_importaMacchine AS i ON m.codMacchina = i.idGest
|
|
INNER JOIN dbo.AnagFamMacchine AS f ON i.Gruppo = f.nomeFamMacch
|
|
LEFT JOIN dbo.Macchine2FamMacchine AS m2f ON m.idxMacchina = m2f.idxMacchina
|
|
WHERE m2f.idxMacchina IS NULL
|
|
|
|
/* Test
|
|
SELECT * FROM tmp_importaMacchine
|
|
SELECT * FROM AnagFamMacchine
|
|
SELECT * FROM AnagMacchine
|
|
SELECT * FROM AnagImpianti
|
|
SELECT * FROM dbo.Macchine2FamMacchine
|
|
*/
|
|
|
|
COMMIT
|
|
|
|
RETURN
|