SEO - Beta

Da Webmobili Wiki.

Nuovo Sito x Seo

https://beta.dbdemo47.com/

Task in sospeso con Wolf Agency

Il progetto è stato terminato a ottobre 2024 (vedi mail del 04/10/2024, 18:04).

Il file condiviso con le attività svolte è qua
https://docs.google.com/spreadsheets/d/10sCcJpTcVsuqZmKAEwI-1TaaCGQAAqpCEJEp-eczQ74/edit?gid=0#gid=0

Test 301

Nel file delle attività vi è specificato che si doveva creare un elenco di url che sarebbero cambiate da dare in pasto ai test di Wolf Agency.
https://drive.google.com/drive/folders/1ZT1uI7PYfVJfRMY3oT0xsATvZMCZEpLb

Siccome saranno vecchi, per rigenerarli è necessario eseguire la seguente query: 

USE DesignbestCore

DECLARE @LanguageID INT = 1
DECLARE @Dominio VARCHAR(100) = 'https://beta.dbdemo47.com'

DECLARE @Lang VARCHAR(2) = CASE
	WHEN @LanguageID = 1 THEN 'it'
	WHEN @LanguageID = 2 THEN 'en'
	WHEN @LanguageID = 3 THEN 'fr'
	ELSE 'de'
END
DECLARE @Catalogo VARCHAR(20) = CASE 
	WHEN @LanguageID = 1 THEN 'catalogo'
	WHEN @LanguageID = 2 THEN 'catalog'
	WHEN @LanguageID = 3 THEN 'catalogue'
	ELSE 'katalog'
END
DECLARE @Prodotti VARCHAR(20) = CASE 
	WHEN @LanguageID = 1 THEN 'prodotti'
	WHEN @LanguageID = 2 THEN 'products'
	WHEN @LanguageID = 3 THEN 'produits'
	ELSE 'produkte'
END
DECLARE @Novita VARCHAR(20) = CASE 
	WHEN @LanguageID = 1 THEN 'novita'
	WHEN @LanguageID = 2 THEN 'news'
	WHEN @LanguageID = 3 THEN 'nouveautes'
	ELSE 'news'
END
DECLARE @ChiSiamo VARCHAR(20) = CASE 
	WHEN @LanguageID = 1 THEN 'chisiamo'
	WHEN @LanguageID = 2 THEN 'whoweare'
	WHEN @LanguageID = 3 THEN 'quisommesnous'
	ELSE 'ueberuns'
END
DECLARE @Arredamento VARCHAR(20) = CASE 
	WHEN @LanguageID = 1 THEN 'arredamento'
	WHEN @LanguageID = 2 THEN 'furniture'
	WHEN @LanguageID = 3 THEN 'meubles'
	ELSE 'moebel'
END
DECLARE @Negozi VARCHAR(20) = CASE 
	WHEN @LanguageID = 1 THEN 'negozi'
	WHEN @LanguageID = 2 THEN 'stores'
	WHEN @LanguageID = 3 THEN 'magasins'
	ELSE 'einrichtungshaeuser'
END


--******************************
--********* Ambienti
SELECT DISTINCT
	AmbientSeoLocale.LocaleValue AS AmbientSeoName,
	@Dominio +
	'/'+@Lang +
	'/'+@Catalogo+
	'/'+@Prodotti+
	'/'+AmbientSeoLocale.LocaleValue AS Link
FROM Category as tipi
	INNER JOIN [Category] as ambienti ON tipi.ParentCategoryId = ambienti.ID AND ambienti.CategoryLevel = 1
	INNER JOIN LocalizedProperty as tipoSeoLocale ON tipi.ID = tipoSeoLocale.EntityId AND tipoSeoLocale.LanguageId = @LanguageId AND tipoSeoLocale.LocaleKeyGroup = 'Category' AND tipoSeoLocale.LocaleKey = 'SeoName'
	INNER JOIN LocalizedProperty as AmbientSeoLocale ON ambienti.ID = AmbientSeoLocale.EntityId AND AmbientSeoLocale.LanguageId = @LanguageId AND AmbientSeoLocale.LocaleKeyGroup = 'Category' AND AmbientSeoLocale.LocaleKey = 'SeoName'
	INNER JOIN LocalizedProperty as AmbientSeoIT ON ambienti.ID = AmbientSeoIT.EntityId AND AmbientSeoIT.LanguageId = 1 AND AmbientSeoIT.LocaleKeyGroup = 'Category' AND AmbientSeoIT.LocaleKey = 'SeoName'
WHERE tipi.Visible = 1 AND ambienti.Visible = 1
ORDER BY AmbientSeoLocale.LocaleValue--, tipoSeoLocale.LocaleValue

--******************************
--********* Ambienti-Tipologie
SELECT DISTINCT
	AmbientSeoLocale.LocaleValue AS AmbientSeoName,
	tipoSeoLocale.LocaleValue AS TypeSeoName,
	@Dominio +
	'/'+@Lang +
	'/'+@Catalogo+
	'/'+@Prodotti+
	'/'+AmbientSeoLocale.LocaleValue+
	'/'+tipoSeoLocale.LocaleValue AS Link
FROM Category as tipi
	INNER JOIN [Category] as ambienti ON tipi.ParentCategoryId = ambienti.ID AND ambienti.CategoryLevel = 1
	INNER JOIN LocalizedProperty as tipoSeoLocale ON tipi.ID = tipoSeoLocale.EntityId AND tipoSeoLocale.LanguageId = @LanguageId AND tipoSeoLocale.LocaleKeyGroup = 'Category' AND tipoSeoLocale.LocaleKey = 'SeoName'
	INNER JOIN LocalizedProperty as AmbientSeoLocale ON ambienti.ID = AmbientSeoLocale.EntityId AND AmbientSeoLocale.LanguageId = @LanguageId AND AmbientSeoLocale.LocaleKeyGroup = 'Category' AND AmbientSeoLocale.LocaleKey = 'SeoName'
	INNER JOIN LocalizedProperty as AmbientSeoIT ON ambienti.ID = AmbientSeoIT.EntityId AND AmbientSeoIT.LanguageId = 1 AND AmbientSeoIT.LocaleKeyGroup = 'Category' AND AmbientSeoIT.LocaleKey = 'SeoName'
WHERE tipi.Visible = 1 AND ambienti.Visible = 1
ORDER BY AmbientSeoLocale.LocaleValue, tipoSeoLocale.LocaleValue

--******************************
--********* Manufacturer
SELECT DISTINCT Manufacturer.SeoName,
	@Dominio +
	'/'+@Lang +
	'/'+@Catalogo+
	'/'+Manufacturer.SeoName AS Link
FROM Manufacturer
	INNER JOIN ProductThumbs ON Manufacturer.ID = ProductThumbs.ManufacturerId AND ProductThumbs.LanguageId = @LanguageID
WHERE Manufacturer.Visible = 1 AND ManufacturerContractTypeID < 4
ORDER BY Manufacturer.SeoName

-- Manu + Ambient (non più indicizzato)
SELECT DISTINCT Manufacturer.SeoName, ProductThumbs.AmbientSeoName,
	@Dominio +
	'/'+@Lang +
	'/'+@Catalogo+
	'/'+Manufacturer.SeoName +
	'/' + ProductThumbs.AmbientSeoName AS Link
FROM Manufacturer
	INNER JOIN ProductThumbs ON Manufacturer.ID = ProductThumbs.ManufacturerId AND ProductThumbs.LanguageId = @LanguageID
WHERE Manufacturer.Visible = 1 AND ManufacturerContractTypeID < 4
ORDER BY Manufacturer.SeoName, ProductThumbs.AmbientSeoName

-- Manu + Ambient + Type (non più indicizzato)
SELECT DISTINCT Manufacturer.SeoName, ProductThumbs.AmbientSeoName, ProductThumbs.TypeSeoName,
	@Dominio +
	'/'+@Lang +
	'/'+@Catalogo+
	'/'+Manufacturer.SeoName +
	'/' + ProductThumbs.AmbientSeoName +
	'/' + ProductThumbs.TypeSeoName AS Link
FROM Manufacturer
	INNER JOIN ProductThumbs ON Manufacturer.ID = ProductThumbs.ManufacturerId AND ProductThumbs.LanguageId = @LanguageID
WHERE Manufacturer.Visible = 1 AND ManufacturerContractTypeID < 4
ORDER BY Manufacturer.SeoName, ProductThumbs.AmbientSeoName, ProductThumbs.TypeSeoName

--******************************
--********* Prodotti
SELECT ProductId as ID,
	@Dominio +
	'/'+@Lang +
	'/'+@Catalogo +
	'/'+ManufacturerSeoName +'/'+AmbientSeoName +
	'/'+TypeSeoName +
	'/'+ ProductSeoName +
	'/'+ CONVERT(varchar(10), ProductId) AS Link
	--ManufacturerSeoName, ProductSeoName as SeoName, Picture, LastUpdate
FROM ProductThumbs
WHERE LanguageId = @LanguageID AND DiscountPrice IS NULL
ORDER BY LastUpdate DESC

--******************************
--********* Negozi
SELECT 
	Region.[SeoName] as RegionSeoName
	,Province.[SeoName] as ProvinceSeoName				
	,[ShopPoint].[ID] as ShopPointId      		
	,[ShopPoint].[SeoName],
	@Dominio +
	'/'+@Lang +
	'/pro/showroom' +
	'/'+[ShopPoint].[SeoName] +
	'/'+ CONVERT(varchar(10), [ShopPoint].[ID]) AS Link
FROM [ShopPoint]
  INNER JOIN City ON [ShopPoint].[CityID] = City.ID
  INNER JOIN Province ON City.ProvinceID = Province.ID
  INNER JOIN Region ON Province.RegionID = Region.ID
WHERE [ShopPoint].[VisibilityTypeID] = 0 AND [ShopPoint].[Visible] = 1 AND City.LanguageID = @LanguageID AND Province.LanguageID = @LanguageID AND Region.LanguageID = @LanguageID
ORDER BY Region.[Name],Province.[Name],[ShopPoint].[Name]

-- Prodotti del Negozio
SELECT 
	Region.[SeoName] as RegionSeoName
	,Province.[SeoName] as ProvinceSeoName				
	,[ShopPoint].[ID] as ShopPointId      		
	,[ShopPoint].[SeoName],
	@Dominio +
	'/'+@Lang +
	'/pro/showroom' +
	'/'+[ShopPoint].[SeoName] +
	'/'+ CONVERT(varchar(10), [ShopPoint].[ID]) +
	'/'+@Catalogo AS Link
FROM [ShopPoint]
  INNER JOIN City ON [ShopPoint].[CityID] = City.ID
  INNER JOIN Province ON City.ProvinceID = Province.ID
  INNER JOIN Region ON Province.RegionID = Region.ID
WHERE [ShopPoint].[VisibilityTypeID] = 0 AND [ShopPoint].[Visible] = 1 AND City.LanguageID = @LanguageID AND Province.LanguageID = @LanguageID AND Region.LanguageID = @LanguageID
ORDER BY Region.[Name],Province.[Name],[ShopPoint].[Name]

-- Prodotti del Negozio per ambiente (non più indicizzato)
SELECT DISTINCT
	Region.[Name] as RegionName
	,Province.[Name] as ProvinceName				
	,[ShopPoint].[ID] as ShopPointId      		
	,[ShopPoint].[Name] AS ShopPointName, ProductThumbs.AmbientSeoName,
	@Dominio +
	'/'+@Lang +
	'/pro/showroom' +
	'/' + [ShopPoint].[SeoName] +
	'/' + CONVERT(varchar(10), [ShopPoint].[ID]) +
	'/' + @Catalogo +
	'/' + ProductThumbs.AmbientSeoName AS Link
FROM [ShopPoint]
  INNER JOIN City ON [ShopPoint].[CityID] = City.ID
  INNER JOIN Province ON City.ProvinceID = Province.ID
  INNER JOIN Region ON Province.RegionID = Region.ID
  INNER JOIN Mapping_ShopPoint_Manufacturer ON ShopPoint.ID = Mapping_ShopPoint_Manufacturer.ShopPointID
  INNER JOIN ProductThumbs ON Mapping_ShopPoint_Manufacturer.ManufacturerID = ProductThumbs.ManufacturerId AND ProductThumbs.LanguageId = @LanguageID
WHERE [ShopPoint].[VisibilityTypeID] = 0 AND [ShopPoint].[Visible] = 1 AND City.LanguageID = @LanguageID AND Province.LanguageID = @LanguageID AND Region.LanguageID = @LanguageID
ORDER BY Region.[Name],Province.[Name],[ShopPoint].[Name], ProductThumbs.AmbientSeoName

-- Prodotti del Negozio per ambiente e tipologia (non più indicizzato)
SELECT DISTINCT
	Region.[Name] as RegionName
	,Province.[Name] as ProvinceName				
	,[ShopPoint].[ID] as ShopPointId      		
	,[ShopPoint].[Name] AS ShopPointName, ProductThumbs.AmbientSeoName, ProductThumbs.TypeSeoName,
	@Dominio +
	'/'+@Lang +
	'/pro/showroom' +
	'/' + [ShopPoint].[SeoName] +
	'/' + CONVERT(varchar(10), [ShopPoint].[ID]) +
	'/' + @Catalogo +
	'/' + ProductThumbs.AmbientSeoName +
	'/' + ProductThumbs.TypeSeoName  AS Link
FROM [ShopPoint]
  INNER JOIN City ON [ShopPoint].[CityID] = City.ID
  INNER JOIN Province ON City.ProvinceID = Province.ID
  INNER JOIN Region ON Province.RegionID = Region.ID
  INNER JOIN Mapping_ShopPoint_Manufacturer ON ShopPoint.ID = Mapping_ShopPoint_Manufacturer.ShopPointID
  INNER JOIN ProductThumbs ON Mapping_ShopPoint_Manufacturer.ManufacturerID = ProductThumbs.ManufacturerId AND ProductThumbs.LanguageId = @LanguageID
WHERE [ShopPoint].[VisibilityTypeID] = 0 AND [ShopPoint].[Visible] = 1 AND City.LanguageID = @LanguageID AND Province.LanguageID = @LanguageID AND Region.LanguageID = @LanguageID
ORDER BY Region.[Name],Province.[Name],[ShopPoint].[Name], ProductThumbs.AmbientSeoName, ProductThumbs.TypeSeoName


--******************************
--********* Pagine statiche
IF @Lang = 'it' BEGIN
	SELECT @Dominio + '/' + @Lang + '/' + @Novita AS Link
	UNION
	SELECT @Dominio + '/' + @Lang + '/info/' + @ChiSiamo
	UNION
	SELECT @Dominio + '/' + @Lang + '/occasioni-design'
	UNION
	SELECT 	@Dominio + '/'+@Lang +	'/'+ @Arredamento
	UNION
	SELECT 	@Dominio + '/'+@Lang +	'/pro/'+ @Negozi
END
ELSE BEGIN
	SELECT @Dominio + '/' + @Lang + '/' + @Novita AS Link
	UNION
	SELECT @Dominio + '/' + @Lang + '/info/' + @ChiSiamo
	UNION
	SELECT 	@Dominio + '/'+@Lang +	'/'+ @Arredamento
	UNION
	SELECT 	@Dominio + '/'+@Lang +	'/pro/'+ @Negozi
END


--******************************
--********* Percorso Manufacturer 

-- ambiente (non più indicizzate)
SELECT DISTINCT ProductThumbs.AmbientSeoName,
	@Dominio +
	'/'+@Lang +
	'/'+@Arredamento+
	'/' + ProductThumbs.AmbientSeoName AS Link
FROM Manufacturer
	INNER JOIN ProductThumbs ON Manufacturer.ID = ProductThumbs.ManufacturerId AND ProductThumbs.LanguageId = @LanguageID
WHERE Manufacturer.Visible = 1 AND ManufacturerContractTypeID < 4
ORDER BY ProductThumbs.AmbientSeoName

-- ambiente + type (non più indicizzate)
SELECT DISTINCT ProductThumbs.AmbientSeoName, ProductThumbs.TypeSeoName,
	@Dominio +
	'/'+@Lang +
	'/'+@Arredamento+
	'/' + ProductThumbs.AmbientSeoName +
	'/' + ProductThumbs.TypeSeoName AS Link
FROM Manufacturer
	INNER JOIN ProductThumbs ON Manufacturer.ID = ProductThumbs.ManufacturerId AND ProductThumbs.LanguageId = @LanguageID
WHERE Manufacturer.Visible = 1 AND ManufacturerContractTypeID < 4
ORDER BY ProductThumbs.AmbientSeoName, ProductThumbs.TypeSeoName

--******************************
--********* Percorso Negozi
-- Regione
SELECT DISTINCT
	Region.[SeoName] as RegionSeoName,	
	@Dominio +
	'/'+@Lang +
	'/pro' +
	'/'+@Negozi +
	'/'+ Region.[SeoName] AS Link
FROM [ShopPoint]
  INNER JOIN City ON [ShopPoint].[CityID] = City.ID
  INNER JOIN Province ON City.ProvinceID = Province.ID
  INNER JOIN Region ON Province.RegionID = Region.ID
WHERE [ShopPoint].[VisibilityTypeID] = 0 AND [ShopPoint].[Visible] = 1 AND City.LanguageID = @LanguageID AND Province.LanguageID = @LanguageID AND Region.LanguageID = @LanguageID
ORDER BY Region.[SeoName]

-- Regione + provincia
SELECT DISTINCT
	Region.[SeoName] as RegionSeoName
	,Province.[SeoName] as ProvinceSeoName		,		
	@Dominio +
	'/'+@Lang +
	'/pro' +
	'/'+@Negozi +
	'/'+ Region.[SeoName] +
	'/'+Province.[SeoName] AS Link
FROM [ShopPoint]
  INNER JOIN City ON [ShopPoint].[CityID] = City.ID
  INNER JOIN Province ON City.ProvinceID = Province.ID
  INNER JOIN Region ON Province.RegionID = Region.ID
WHERE [ShopPoint].[VisibilityTypeID] = 0 AND [ShopPoint].[Visible] = 1 AND City.LanguageID = @LanguageID AND Province.LanguageID = @LanguageID AND Region.LanguageID = @LanguageID
ORDER BY Region.[SeoName],Province.[SeoName]

Merge

Nel caso di modifiche dell'attuale designbest su trunk, entro nel branche e:

Estratto da "https://wiki.wmdemo.it/index.php?title=SEO_-_Beta&oldid=4856"