Modifica di Esportazione prodotti in CSV

Ci siamo trovati a dover esportare una parte del nostro catalogo in formato CSV per un cliente che ha Magento (Lombardo).

La query estrapola solo i campi necessari all'importatore del plugin Magento.

<div>
<syntaxhighlight lang="sql">
DECLARE 
	@LanguageID INT = 1,
	@ProductID INT, --= 106971, --107935
	@ImageType nvarchar(10) = 'gallery'

CREATE TABLE #Results(
	ProductId INT PRIMARY KEY,
	ProductName NVARCHAR(4000),
	[Description] NVARCHAR(4000),
	ManufacturerName NVARCHAR(4000),
	AmbientName NVARCHAR(4000),
	AmbientId INT,
	TypeName NVARCHAR(4000),
	Designer NVARCHAR(4000),
	Materials NVARCHAR(4000),
	Measures NVARCHAR(4000),
	Pictures NVARCHAR(4000)
)


DECLARE my_cursor CURSOR FOR
	SELECT ID
	FROM Product
	WHERE ManufacturerID IN (
	2238
	,1087
	,57
	,74
	,72
	,115
	,116
	,4125
	,3981
	,163
	,166
	,174
	,181
	,4169
	,216
	,243
	,249
	,1839
	,282
	,4186
	,4172
	,4260
	,1382
	,324
	,330
	,4139
	,356
	,331
	,360
	,378
	,4124
	,2267
	,2206
	,407
	,224
	,415
	,4117
	,431
	,437
	,4131
	,455
	,4128
	,2240
	,4022
	,2204
	,508
	)

OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @ProductID
WHILE @@FETCH_STATUS = 0 
BEGIN
	INSERT INTO #Results
	SELECT DISTINCT Product.ID AS 'ID prodotto'
		,CASE
		WHEN LocalizedPropertyName.LocaleValue IS NULL OR LocalizedPropertyName.LocaleValue = '' THEN [Product].[Name]
		ELSE LocalizedPropertyName.LocaleValue
		END AS 'nome prodotto'  
		,CASE
		WHEN LocalizedPropertyDescr.LocaleValue IS NULL OR LocalizedPropertyDescr.LocaleValue = ''  THEN dbo.STRIP_TAGS([Product].[Description])
		ELSE dbo.STRIP_TAGS(LocalizedPropertyDescr.LocaleValue)
		END AS 'descrizione'  
		,Manufacturer.[Name] AS 'brand'	
		,ISNULL(LocalizedAmbientName.LocaleValue,CategoryAmbient.[Name]) AS 'ambiente'
		,CategoryAmbient.ID AS 'ambienteID'
		,ISNULL(LocalizedTypeName.LocaleValue, CategoryType.[Name]) AS 'tipologia'	      
		, Designer.[Name] AS 'designer'
		, CASE
		WHEN LocalizedPropertyMaterial.LocaleValue IS NULL OR LocalizedPropertyMaterial.LocaleValue = ''  THEN [Product].Material
		ELSE LocalizedPropertyMaterial.LocaleValue
		END AS 'materiale'
		,CASE
		WHEN LocalizedPropertyMeasures.LocaleValue IS NULL OR LocalizedPropertyMeasures.LocaleValue = ''  THEN  dbo.STRIP_TAGS([Product].Measures)
		ELSE  dbo.STRIP_TAGS(LocalizedPropertyMeasures.LocaleValue)
		END AS 'misure'
		,Pictures.immagini
		--,'https://img.designbest.com/pictures/' + PictureMaster.EntityValue +'-'+CAST(Product.ID as nvarchar(10))+'-'+CAST(PictureMaster.ID as nvarchar(10))+'.jpg' as 'Immagine'	 
	FROM [Product]
		INNER JOIN ProductCulture ON [Product].ID = ProductCulture.ProductId AND ProductCulture.Visible = 1 AND ProductCulture.LanguageId = @LanguageID
		INNER JOIN Category as CategoryType ON Product.CategoryId = CategoryType.ID AND CategoryType.CategoryLevel = 2
		INNER JOIN Category as CategoryAmbient ON CategoryType.ParentCategoryId = CategoryAmbient.ID AND CategoryAmbient.CategoryLevel = 1    
		LEFT OUTER JOIN Picture as PictureMaster ON [Product].PictureId = PictureMaster.ID
		LEFT OUTER JOIN ProductBargain ON [Product].ID  = ProductBargain.ProductId --AND ProductBargain.OutletApproved = 1
		LEFT OUTER JOIN ProductBargainCulture ON [Product].ID = ProductBargainCulture.ProductID AND ProductBargainCulture.LanguageId = @LanguageID
		LEFT OUTER JOIN Manufacturer ON [Product].ManufacturerId = Manufacturer.ID    
		LEFT OUTER JOIN [LocalizedProperty] as LocalizedPropertyName ON  [Product].ID = LocalizedPropertyName.EntityId AND LocalizedPropertyName.LanguageId = @LanguageID AND LocalizedPropertyName.LocaleKeyGroup = 'Product' AND LocalizedPropertyName.LocaleKey = 'Name'    
		LEFT OUTER JOIN [LocalizedProperty] as LocalizedPropertyDescr ON  [Product].ID = LocalizedPropertyDescr.EntityId AND LocalizedPropertyDescr.LanguageId = @LanguageID AND LocalizedPropertyDescr.LocaleKeyGroup = 'Product' AND LocalizedPropertyDescr.LocaleKey = 'Description'  
		LEFT OUTER JOIN [LocalizedProperty] as LocalizedPropertyMaterial ON  [Product].ID = LocalizedPropertyMaterial.EntityId AND LocalizedPropertyMaterial.LanguageId = @LanguageID AND LocalizedPropertyMaterial.LocaleKeyGroup = 'Product' AND LocalizedPropertyMaterial.LocaleKey = 'Material'
		LEFT OUTER JOIN [LocalizedProperty] as LocalizedPropertyMeasures ON  [Product].ID = LocalizedPropertyMeasures.EntityId AND LocalizedPropertyMeasures.LanguageId = @LanguageID AND LocalizedPropertyMeasures.LocaleKeyGroup = 'Product' AND LocalizedPropertyMeasures.LocaleKey = 'Measures'
		LEFT OUTER JOIN LocalizedProperty as LocalizedTypeName ON CategoryType.ID = LocalizedTypeName.EntityId AND LocalizedTypeName.LanguageId = @LanguageId AND LocalizedTypeName.LocaleKeyGroup = 'Category' AND LocalizedTypeName.LocaleKey = 'Name'  
		LEFT OUTER JOIN LocalizedProperty as LocalizedAmbientName ON CategoryAmbient.ID = LocalizedAmbientName.EntityId AND LocalizedAmbientName.LanguageId = @LanguageId AND LocalizedAmbientName.LocaleKeyGroup = 'Category' AND LocalizedAmbientName.LocaleKey = 'Name'  
		LEFT OUTER JOIN (
		SELECT Mapping_Product_ProductPropertyValue.ProductID, ProductPropertyValue.[Name]
		FROM ProductPropertyValue
			INNER JOIN Mapping_Product_ProductPropertyValue ON Mapping_Product_ProductPropertyValue.ProductPropertyValueID = ProductPropertyValue.ID
			INNER JOIN ProductProperty ON ProductPropertyValue.ProductPropertyId = ProductProperty.ID
		WHERE ProductProperty.ID = 26 AND Mapping_Product_ProductPropertyValue.ProductID = @ProductID
		) Designer ON Product.ID = Designer.ProductID
		LEFT OUTER JOIN (
		SELECT 
			@ProductID AS ProductID,
			STRING_AGG('https://img.designbest.com/pictures/' +Picture.EntityValue + '-'+CAST(EntityId as nvarchar(10))+'-'+CAST(ID as nvarchar(10))+'.jpg', ' ') as immagini
		FROM Picture
		WHERE EntityId = @ProductID AND EntityGroup = 'Product' AND EntityKey = @ImageType
		) Pictures ON Product.ID = Pictures.ProductID
	WHERE ProductBargain.ProductId IS NULL AND Product.ID = @ProductID


	FETCH NEXT FROM my_cursor INTO @ProductID
END
CLOSE my_cursor;
DEALLOCATE my_cursor;
	
SELECT 
	--ProductId AS 'ID prodotto',
	--ProductName AS 'nome prodotto',
	--[Description] AS 'descrizione',
	--ManufacturerName AS 'brand',
	--AmbientName AS 'ambiente',
	--AmbientId AS 'ambienteID',
	--TypeName AS 'tipologia',
	--Designer AS 'designer',
	--Materials AS 'materiale',
	--Measures AS 'misure',
	--Pictures AS 'immagini'
	'ID prodotto,nome prodotto,descrizione,brand,ambiente,ambienteID,tipologia,designer,materiale,misure,immagini' +
	CAST(ProductId AS VARCHAR(100)) + ',' +
	'"' + ProductName +'",' +
	'"' + ISNULL([Description],'') +'",' +
	'"' + ManufacturerName +'",' +
	'"' + AmbientName +'",' +
	CAST(AmbientId AS VARCHAR(100)) + ',' +
	'"' + TypeName  +'",' +
	'"' + ISNULL(Designer,'') +'",' +
	'"' + ISNULL(Materials,'') +'",' +
	'"' + ISNULL(Measures,'') +'",' +
	'"' + Pictures
FROM #Results
ORDER BY ManufacturerName, AmbientName, TypeName, ProductName

DROP TABLE #Results
</syntaxhighlight>
</div>