CREATE PROCEDURE WebApagaGruposFamiliasSubFamiliasRepetidos
AS


UPDATE tbDescontos  
SET tbDescontos.inArtigo_Grupo=QueryUpdate.GrupoNova
FROM 
(
	SELECT
		Query.inCodigo,
		Query.GrupoNova
	FROM 
	(
		SELECT 
			tbArtigos_Grupos.inCodigo,
			tbArtigos_Grupos.teDescricao,
			CASE
				WHEN Repetidas.IdMinimo=tbArtigos_Grupos.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbArtigos_Grupos.inCodigo THEN 1
			END AS Apagar,	
			CASE
				WHEN Repetidas.IdMinimo=tbArtigos_Grupos.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbArtigos_Grupos.inCodigo THEN Repetidas.IdMinimo
			END AS GrupoNova
		FROM tbArtigos_Grupos
			INNER JOIN 
			(
				SELECT teDescricao,MIN(inCodigo) as IdMinimo
				FROM tbArtigos_Grupos
				GROUP BY  teDescricao
				HAVING COUNT(*) >1
			) AS Repetidas  ON Repetidas.teDescricao=tbArtigos_Grupos.teDescricao
	) Query
	WHERE Query.Apagar=1
) QueryUpdate
WHERE  tbDescontos.inArtigo_Grupo=QueryUpdate.inCodigo


UPDATE tbArtigos  
SET tbArtigos.inArtigo_Grupo=QueryUpdate.GrupoNova
FROM 
(
	SELECT
		Query.inCodigo,
		Query.GrupoNova
	FROM 
	(
		SELECT 
			tbArtigos_Grupos.inCodigo,
			tbArtigos_Grupos.teDescricao,
			CASE
				WHEN Repetidas.IdMinimo=tbArtigos_Grupos.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbArtigos_Grupos.inCodigo THEN 1
			END AS Apagar,	
			CASE
				WHEN Repetidas.IdMinimo=tbArtigos_Grupos.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbArtigos_Grupos.inCodigo THEN Repetidas.IdMinimo
			END AS GrupoNova
		FROM tbArtigos_Grupos
			INNER JOIN 
			(
				SELECT teDescricao,MIN(inCodigo) as IdMinimo
				FROM tbArtigos_Grupos
				GROUP BY  teDescricao
				HAVING COUNT(*) >1
			) AS Repetidas  ON Repetidas.teDescricao=tbArtigos_Grupos.teDescricao
	) Query
	WHERE Query.Apagar=1
) QueryUpdate
WHERE  tbArtigos.inArtigo_Grupo=QueryUpdate.inCodigo

DELETE tbArtigos_Grupos
FROM 
(
	SELECT
		Query.inCodigo,
		Query.GrupoNova
	FROM 
	(
		SELECT 
			tbArtigos_Grupos.inCodigo,
			tbArtigos_Grupos.teDescricao,
			CASE
				WHEN Repetidas.IdMinimo=tbArtigos_Grupos.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbArtigos_Grupos.inCodigo THEN 1
			END AS Apagar,	
			CASE
				WHEN Repetidas.IdMinimo=tbArtigos_Grupos.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbArtigos_Grupos.inCodigo THEN Repetidas.IdMinimo
			END AS GrupoNova
		FROM tbArtigos_Grupos
			INNER JOIN 
			(
				SELECT teDescricao,MIN(inCodigo) as IdMinimo
				FROM tbArtigos_Grupos
				GROUP BY  teDescricao
				HAVING COUNT(*) >1
			) AS Repetidas  ON Repetidas.teDescricao=tbArtigos_Grupos.teDescricao
	) Query
	WHERE Query.Apagar=1
) QueryUpdate
WHERE  tbArtigos_Grupos.inCodigo=QueryUpdate.inCodigo


UPDATE tbDescontos  
SET tbDescontos.inSubFamilia=QueryUpdate.SubFamiliaNova
FROM 
(
	SELECT
		Query.inFamilia,
		Query.inCodigo,
		Query.SubFamiliaNova
	FROM 
	(
		SELECT 
			tbSubFamilias.inFamilia,
			tbSubFamilias.inCodigo,
			tbSubFamilias.teDescricao,
			CASE
				WHEN Repetidas.IdMinimo=tbSubFamilias.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbSubFamilias.inCodigo THEN 1
			END AS Apagar,	
			CASE
				WHEN Repetidas.IdMinimo=tbSubFamilias.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbSubFamilias.inCodigo THEN Repetidas.IdMinimo
			END AS SubFamiliaNova
		FROM tbSubFamilias
			INNER JOIN 
			(
				SELECT inFamilia, teDescricao,MIN(inCodigo) as IdMinimo
				FROM tbSubFamilias
				GROUP BY inFamilia, teDescricao
				HAVING COUNT(*) >1
			) AS Repetidas  ON tbSubFamilias.inFamilia =Repetidas.inFamilia and Repetidas.teDescricao=tbSubFamilias.teDescricao
	) Query
	WHERE Query.Apagar=1
) QueryUpdate
WHERE tbDescontos.inFamilia=QueryUpdate.inFamilia and tbDescontos.inSubFamilia=QueryUpdate.inCodigo

UPDATE tbArtigos  
SET tbArtigos.inSubFamilia=QueryUpdate.SubFamiliaNova
FROM 
(
	SELECT
		Query.inFamilia,
		Query.inCodigo,
		Query.SubFamiliaNova
	FROM 
	(
		SELECT 
			tbSubFamilias.inFamilia,
			tbSubFamilias.inCodigo,
			tbSubFamilias.teDescricao,
			CASE
				WHEN Repetidas.IdMinimo=tbSubFamilias.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbSubFamilias.inCodigo THEN 1
			END AS Apagar,	
			CASE
				WHEN Repetidas.IdMinimo=tbSubFamilias.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbSubFamilias.inCodigo THEN Repetidas.IdMinimo
			END AS SubFamiliaNova
		FROM tbSubFamilias
			INNER JOIN 
			(
				SELECT inFamilia, teDescricao,MIN(inCodigo) as IdMinimo
				FROM tbSubFamilias
				GROUP BY inFamilia, teDescricao
				HAVING COUNT(*) >1
			) AS Repetidas  ON tbSubFamilias.inFamilia =Repetidas.inFamilia and Repetidas.teDescricao=tbSubFamilias.teDescricao
	) Query
	WHERE Query.Apagar=1
) QueryUpdate
WHERE tbArtigos.inFamilia=QueryUpdate.inFamilia and tbArtigos.inSubFamilia=QueryUpdate.inCodigo

DELETE tbSubFamilias
FROM 
(
	SELECT
		Query.inFamilia,
		Query.inCodigo,
		Query.SubFamiliaNova
	FROM 
	(
		SELECT 
			tbSubFamilias.inFamilia,
			tbSubFamilias.inCodigo,
			tbSubFamilias.teDescricao,
			CASE
				WHEN Repetidas.IdMinimo=tbSubFamilias.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbSubFamilias.inCodigo THEN 1
			END AS Apagar,	
			CASE
				WHEN Repetidas.IdMinimo=tbSubFamilias.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbSubFamilias.inCodigo THEN Repetidas.IdMinimo
			END AS SubFamiliaNova
		FROM tbSubFamilias
			INNER JOIN 
			(
				SELECT inFamilia, teDescricao,MIN(inCodigo) as IdMinimo
				FROM tbSubFamilias
				GROUP BY inFamilia, teDescricao
				HAVING COUNT(*) >1
			) AS Repetidas  ON tbSubFamilias.inFamilia =Repetidas.inFamilia and Repetidas.teDescricao=tbSubFamilias.teDescricao
	) Query
	WHERE Query.Apagar=1
) QueryUpdate
WHERE tbSubFamilias.inFamilia=QueryUpdate.inFamilia and tbSubFamilias.inCodigo=QueryUpdate.inCodigo


--Apaga a tabela Temporaria caso exista
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.tbTempFamiliasRepetidas'))
BEGIN
	DROP TABLE tbTempFamiliasRepetidas
END

--Crio a nova tabela temporaria
SELECT
	Final.CodigoFamiliaApagar,
	Final.CodigoNovaFamilia,
	Final.FamiliaAlterar,
	Final.SubFamiliaAlterar,
	Final.CodigoNovaSubFamilia 
	into tbTempFamiliasRepetidas
FROM
(
	SELECT
	 Query.inCodigo CodigoFamiliaApagar,
	 Query.FamiliaNova CodigoNovaFamilia,
	 tbSubFamilias.inFamilia 'FamiliaAlterar',
	 tbSubFamilias.inCodigo 'SubFamiliaAlterar',
	 0 'CodigoNovaSubFamilia'
	FROM 
	(
		SELECT 
			tbFamilias.inCodigo,
			tbFamilias.teDescricao,
			CASE
				WHEN Repetidas.IdMinimo=tbFamilias.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbFamilias.inCodigo THEN 1
			END AS Apagar,	
			CASE
				WHEN Repetidas.IdMinimo=tbFamilias.inCodigo THEN 0
				WHEN Repetidas.IdMinimo!=tbFamilias.inCodigo THEN Repetidas.IdMinimo
			END AS FamiliaNova
		FROM tbFamilias
			INNER JOIN 
			(
				SELECT  teDescricao,MIN(inCodigo) as IdMinimo
				FROM tbFamilias
				GROUP BY  teDescricao
				HAVING COUNT(*) >1
			) AS Repetidas ON Repetidas.teDescricao=tbFamilias.teDescricao
	) Query
	INNER JOIN tbSubFamilias ON tbSubFamilias.inFamilia=Query.inCodigo
	WHERE Query.Apagar=1
) AS Final

--Gero um novo id para as subfamilias que tem que ser realocadas
UPDATE tbTempFamiliasRepetidas
SET tbTempFamiliasRepetidas.CodigoNovaSubFamilia=tbTempFamiliasRepetidas.Id
FROM
(
	SELECT tbTempFamiliasRepetidas.CodigoNovaSubFamilia, 
			(select count(*) from  tbSubFamilias)+ row_number() over (order by newid()) as Id
	FROM tbTempFamiliasRepetidas
) tbTempFamiliasRepetidas

--Faço o update as novas subfamilias
update tbSubFamilias
set inCodigo=tbTempFamiliasRepetidas.CodigoNovaSubFamilia,inFamilia=tbTempFamiliasRepetidas.CodigoNovaFamilia
FROM tbTempFamiliasRepetidas
where tbSubFamilias.inCodigo=tbTempFamiliasRepetidas.SubFamiliaAlterar and tbSubFamilias.inFamilia=FamiliaAlterar

--Faz o Udate aos artigos 
update tbArtigos
set inSubFamilia=tbTempFamiliasRepetidas.CodigoNovaSubFamilia,inFamilia=tbTempFamiliasRepetidas.CodigoNovaFamilia
FROM tbTempFamiliasRepetidas
where tbArtigos.inSubFamilia=tbTempFamiliasRepetidas.SubFamiliaAlterar and tbArtigos.inFamilia=FamiliaAlterar

--Faz o Udate aos descontos 
update tbDescontos
set inSubFamilia=tbTempFamiliasRepetidas.CodigoNovaSubFamilia,inFamilia=tbTempFamiliasRepetidas.CodigoNovaFamilia
FROM tbTempFamiliasRepetidas
where tbDescontos.inSubFamilia=tbTempFamiliasRepetidas.SubFamiliaAlterar and tbDescontos.inFamilia=FamiliaAlterar

--Apago as familias
DELETE tbFamilias
FROM tbTempFamiliasRepetidas
WHERE tbFamilias.inCodigo=tbTempFamiliasRepetidas.CodigoFamiliaApagar

--Apaga a tabela Temporaria caso exista
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('dbo.tbTempFamiliasRepetidas'))
BEGIN
	DROP TABLE tbTempFamiliasRepetidas
END