Modifica di Designbest Core Sviluppo (sezione)

=== Importazione dati ===
Eseguire nell'ordine
* Eseguire script sul database <code>Designbest</code> per normalizzare e copiare le Misure nel campo <code>ProductCulture.AdditionalNotes</code>, '''4 volte''' per tutte le lingue
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
/*
La procedura, per ogni prodotto, normalizza le misure in una unica stringa e ne copia il contenuto
dentro a ProductBargain.AdditionalNotes . 
Ci mette circa 1:25min per la cultura italiana.
*/
BEGIN TRAN

DECLARE @Culture VARCHAR(5) = 'it-IT',
	@LanguageId INT = 1

DECLARE @ProductID INT = 0,
	@MeasuresCurrent NVARCHAR(2000),
	@MeasuresValue NVARCHAR(2000)

DECLARE cur CURSOR FOR
	SELECT ID FROM Product-- WHERE ID = 2

OPEN cur;
FETCH NEXT FROM cur INTO @ProductID
WHILE @@FETCH_STATUS = 0 BEGIN

	SET @MeasuresValue = ''

	DECLARE cur2 CURSOR FOR
		SELECT '<p>' +MeasureCulture.DisplayName + ' ' + L_Product_Measure.[Value] + ' ' + MeasureCulture.Unit + '</p>'
		FROM L_Product_Measure
			INNER JOIN Measure ON L_Product_Measure.MeasureID = Measure.ID
			INNER JOIN MeasureCulture ON Measure.ID = MeasureCulture.MeasureID AND MeasureCulture.Culture = @Culture
		WHERE ProductID = @ProductID
		ORDER BY MeasureCulture.Culture, Measure.Sort

	OPEN cur2;
	FETCH NEXT FROM cur2 INTO @MeasuresCurrent
	WHILE @@FETCH_STATUS = 0 BEGIN
		IF @MeasuresValue = '' BEGIN
			SET @MeasuresValue = @MeasuresCurrent + ' '
		END
		ELSE BEGIN
			SET @MeasuresValue +=  @MeasuresCurrent
		END
		FETCH NEXT FROM cur2 INTO @MeasuresCurrent
	END
	CLOSE cur2;
	DEALLOCATE cur2;

	-- Update di ProductCulture.AdditionalNotes
	UPDATE ProductCulture
	SET AdditionalNotes = 
		CASE WHEN @MeasuresValue = '' THEN NULL
		ELSE @MeasuresValue
	END
	WHERE ProductID = @ProductID AND Culture = @Culture
	PRINT CAST(@ProductID AS VARCHAR(10)) +':'+ @MeasuresValue

	-- QUESTA PARTE SCRIVE DIRETTAMENTE SU DesignbestCore
	--IF @MeasuresValue <> '' BEGIN

	--	-- Tabella Product.Measures (lingua neutra, cioè ita)
	--	IF @Culture = 'it-IT' BEGIN
	--		UPDATE DesignbestCore.dbo.Product
	--		SET Measures = CASE WHEN @MeasuresValue = '' THEN NULL
	--				ELSE @MeasuresValue
	--			END
	--		WHERE ID = @ProductID-- AND Culture = @Culture

	--	END

	--	-- LocalizedProperty
	--	UPDATE DesignbestCore.dbo.LocalizedProperty
	--	SET LocaleValue = @MeasuresValue
	--	WHERE EntityId = @ProductID AND LanguageId = @LanguageId AND LocaleKeyGroup = 'Product' AND LocaleKey = 'Measures'

	--END

	FETCH NEXT FROM cur INTO @ProductID
END

CLOSE cur;
DEALLOCATE cur;

COMMIT TRAN
</syntaxhighlight>
</div>
* Reset_All_Tables (database <code>DesignbestCore</code>) ~ 4 min
* WM_CategorySynchCore
* Eliminare temporaneamente vincolo <code>FK_ProductBargain_ShopPoint</code> da <code>ProductBargain</code>
* WM_ProductSynchCore ~ 3 min
* Fare la chiamata API a <code>https://www.designbestoutlet.com/wp-json/designbest-outlet/v1/listallvendors</code> con Postman e piazzare il file json restituito in <code>D:\Temp\Outlet-Vendors.json</code> sulla macchina <code>BIANCANEVE</code>
* Piazzare il file <code>homepage_highlights.xml</code> in <code>D:\Temp\homepage_highlights.xml</code>
* WM_ShopNetSynchCore
* Ripristinare la chiave esterna <code>FK_ProductBargain_ShopPoint</code> su <code>ProductBargain</code>
* Verificare che non ci siano designer con seoname duplicato sul database <code>Designbest</code>, nel caso correggerli
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
SELECT SEODesigner, COUNT(SEODesigner) AS contegg
FROM Designer
where AllowSearch = 1 OR AllowSearchOutlet = 1
group by SEODesigner
order by contegg desc
</syntaxhighlight>
</div>
* WM_ProductPropertySynchCore
* FullTextSearch, eseguire i comandi del paragrafo [[#Negozi]] nel database <code>DesignbestCore</code>
* WM_PicturesSynchCore (verificare aggiornamento path di XML BrandChannelPhotoGalleries su <code>D:\Temp</code>) su BIANCANEVE
* Rinominare fisicamente le 22 immagini degli ambienti aggiungendo PictureID generato al nome es <code>bagno-14-217177</code>  e copiarle in una cartella a parte (perché la pictures verrà cancellata)
* WM_Nop_SyncCore (verificare i negozi nella <code>DesignbestCore.dbo.NopMapping_ShopNets</code> confrontandoli con la vecchia)
* WM_UsersCore
* WM_TpCustomSynchCore
* Cancellare tutte le immagini nella cartella <code>pictures</code> (occhio, salvarsi prima le 22 immagini degli ambienti)
* Copiare le 22 immagini ambiente che sono state salvate prima dentro alla nuova ''pictures''
* Immagini occasioni - Eseguire script Python tramite <code>D:\Programs\MigrazioneDB_DBCore\DesignbestBargainMigration.bat</code> su ARIEL ''(~ 10 min)''.
* Immagini cover shop - Eseguire script Python <code>D:\Programs\MigrazioneDB_DBCore\DesignbestShopCoverAltMigration.py</code> su ARIEL
* Utility_ProductThumbsMerge (database <code>DesignbestCore</code>)
* Immagini fisiche - Eseguire la query SQL per generare il file batch che sposterà i file, database <code>DesignbestCore</code> ''(~ 3:40h)''
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
SELECT
	'IF EXIST "' + REPLACE(old_PicturePath, '/', '\') + '" (',
	'xcopy /y "' + REPLACE(old_PicturePath, '/', '\') + '" ' +  '"D:\WM3Resources\ImmaginiWM3\pictures\' + EntityValue + '-'+ CAST(EntityID AS VARCHAR(10)) + '-' + CAST(ID AS VARCHAR(10)) + '.jpg*"',
	') ELSE ( echo ' + CAST(ID AS VARCHAR(10)) + '	BigPath >> missingimages.txt )'
FROM Picture
</syntaxhighlight >
</div>
* <del>Update Width e Height delle Pictures - Eseguire script Python tramite <code>D:\Programs\python-CoreGetSetPicturesWidthHeight\main.py</code> su ARIEL ''(~ oo min)''</del>.
* Immagini selezioni TP - Eseguire script Python tramite <code>D:\Programs\MigrazioneDB_DBCore\DesignbestSelectionMigration.bat</code> su ARIEL ''(~ 3 min)''.
* Immagini fisiche selezioni - Eseguire la query SQL per generare il file batch che sposterà i file, database <code>DesignbestCore</code>
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
SELECT
	'IF EXIST "' + REPLACE(old_PicturePath, '/', '\') + '" (',
	'xcopy /y "' + REPLACE(old_PicturePath, '/', '\') + '" ' +  '"D:\WM3Resources\ImmaginiWM3\pictures\' + EntityValue + '-'+ CAST(EntityID AS VARCHAR(10)) + '-' + CAST(ID AS VARCHAR(10)) + '.jpg*"',
	') ELSE ( echo ' + CAST(ID AS VARCHAR(10)) + '	BigPath >> missingimages2.txt )'
FROM Picture_Ext
WHERE old_PicturePath IS NOT NULL
</syntaxhighlight >
</div>
* Update Width e Height delle Pictures delle Selezioni - Eseguire script Python tramite <code>D:\Programs\python-CoreGetSetPicturesWidthHeight\CoreGetSetWidthHeight.bat</code> su ARIEL per le <code>Picture_Ext</code> (occhio allo script, eseguire la parte Picture_Ext e commentare l'altra).
* Update Width e Height delle Pictures delle Selezioni - Eseguire script Python tramite <code>D:\Programs\python-CoreGetSetPicturesWidthHeight\CoreGetSetWidthHeight.bat</code> su ARIEL per le <code>Picture</code> ''(~ 3h e 7 min)''  (occhio allo script, eseguire la parte Picture e commentare l'altra).
* Volendo mentre si aspetta questo task è possibile fare la parte di '''LeadManager''' [[#Operazione per Outlet WP, Nop Commerce e Lead manager]]
* Rilanciare SP -> Utility_ProductThumbsMerge (database <code>DesignbestCore</code>)
* Rilanciare SP -> Utility_ProductThumbsMerge_Ext e FullText_Products_Update_Ext  usando il seguente cursore
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
DECLARE @TrovaprodottiID int

DECLARE cur3 CURSOR FOR
		SELECT DISTINCT Context FROM DesignbestCore.dbo.Product_Ext

OPEN cur3;
FETCH NEXT FROM cur3 INTO @TrovaprodottiID
WHILE @@FETCH_STATUS = 0 BEGIN
	EXEC DesignbestCore.dbo.Utility_ProductThumbsMerge_Ext @TrovaprodottiID, 1
	EXEC DesignbestCore.dbo.FullText_Products_Update_Ext @TrovaprodottiID

	FETCH NEXT FROM cur3 INTO @TrovaprodottiID
END

CLOSE cur3;
DEALLOCATE cur3;
</syntaxhighlight >
</div>

* SU NOP (aggiornare il trigger della tabella Product)
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
USE [Designbest-Commerce]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Product_ChangeQuantity]
	ON [dbo].[Product]
	AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @OccNopID INT = 0, @NumPezziAggiornato INT;

	IF UPDATE([StockQuantity])
		BEGIN
			
			SELECT @OccNopID = ProductBargainCore.NopID FROM Db_ProductBargain as ProductBargainCore
			WHERE ProductBargainCore.NopID IN (
				SELECT i.Id FROM inserted i
			)	

			IF @OccNopID = 0 return;

			SELECT @NumPezziAggiornato = i.StockQuantity FROM inserted i;

			UPDATE Db_ProductBargain
						SET Db_ProductBargain.Pieces = @NumPezziAggiornato
						WHERE Db_ProductBargain.NopID = @OccNopID

			PRINT 'TRIGGER [Product_ChangeQuantity] ESEGUITO e Aggiornato su TP'
			
		END	
	ELSE
		BEGIN
			PRINT 'TRIGGER [Product_ChangeQuantity] ESEGUITO, ma nessuna modifica su TP'
		END
	
END
</syntaxhighlight>
</div>
* Aggiungere e mettere gli '''indici fulltext''' (sotto ''Storage'') per le viste <code>FT_Shops_IT</code>, <code>FT_Shops_EN</code>, <code>FT_Shops_DE</code> e <code>FT_Shops_FR</code>
* Query di '''pulizia dati''', database <code>DesignbestCore</code>
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
-- Pulizia campo ausiliario per oldSelectionID
UPDATE Product SET [Weight] = 0

-- Pulizia campi sporchi TpApproved = 1 quando prodotto non visibile
UPDATE ProductBargain SET TpApproved = 0, OutletApproved = 0, CommerceApproved = 0, OutletToModerate = NULL, CommerceToModerate = NULL
FROM ProductBargain
	INNER JOIN ProductCulture ON ProductBargain.ProductId = ProductCulture.ProductId
WHERE ProductCulture.Visible = 0 AND ProductCulture.LanguageId = 1
</syntaxhighlight>
</div>
* Creazione delle shopnet dei '''Flagshipstore''', database <code>DesignbestCore</code>
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
DECLARE @ShopNetID INT,
	@ID INT,
	@Name NVARCHAR(200),
	@RagSociale VARCHAR(200),
	@Referente NVARCHAR(100),
	@Mail NVARCHAR(255)

SELECT @ShopNetID = ID FROM ShopNet WHERE RagSociale = 'Anagrafiche' 

DECLARE @CurrentShopNetID INT
DECLARE my_cursor CURSOR FOR
	SELECT ID, [Name] AS ShopNetName, [Name] AS RagSociale, Referente, Mail
	FROM ShopPoint
	WHERE ShopNetID = @ShopNetID AND VisibilityTypeID = 0 AND Visible = 1
OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @ID, @Name, @RagSociale, @Referente, @Mail
WHILE @@FETCH_STATUS = 0 BEGIN
	INSERT INTO ShopNet
	SELECT @Name, @RagSociale, @Referente, '000000000005', @Mail, 'Flagship Store',
		0,
		NULL,
		0,
		NULL,
		0,
		0,
		NULL,
		NULL,
		@ID,	-- Main ShopPoint
		NULL, -- PermalinkWP
		NULL  -- PermalinkNop

	SELECT @CurrentShopNetID = @@IDENTITY
	UPDATE ShopPoint SET ShopNetID = @CurrentShopNetID WHERE ID = @ID

	--SELECT * FROM ShopNet WHERE ID = @CurrentShopNetID

	FETCH NEXT FROM my_cursor INTO @ID, @Name, @RagSociale, @Referente, @Mail
END

CLOSE my_cursor;
DEALLOCATE my_cursor;
</syntaxhighlight>
</div>

* '''CustomSorting''', tabella che per ora regola il sort dei prodotti da scheda catalogo, database <code>Designbest</code>
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
TRUNCATE TABLE _DesignbestCore.dbo.CustomSorting

INSERT INTO _DesignbestCore.dbo.CustomSorting
SELECT Product.ID, 'Product', 'ManufacturerProducts', Product.ManufacturerSort
FROM Product
	INNER JOIN Manufacturer ON Product.ManufacturerID = Manufacturer.ID
WHERE Product.Visible = 1 AND Manufacturer.Visible = 1 AND Product.ManufacturerSort <> 50
</syntaxhighlight>
</div>

==== Operazione per Outlet WP, Nop Commerce e Lead manager ====
* Outlet Plugin - Usare il pulsante '''POST IMPORT SKU CHANGE''' per cambiare tutti gli SKU da <code>old_bargainID</code> a nuovo <code>ProductID</code> di DesignbestCore.
* Nop - Sql, copiare il '''trigger''' come specificato nell'ultimo punto del paragrafo precedente (verificare esistenza '''utente''' <code>wmuser</code> su <code>_DesignbestCore</code>)
* LM - Backuppare database <code>LeadManager</code> con il comando ''"Export Data-tier Application"'' in un file <code>bacpac</code>
* LM - Query per <br/>
::modificare il campo <code>category.code</code> con i nuovi id ambiente ai 23 record presenti che non iniziano con ''O'',<br/>
::modificare il campo <code>product.code</code> con i nuovi id occasione, lanciare i risultati di queste due query come update su database <code>LeadManager</code>
<br/><br/>
'''N.B.''' Se si fa una import questa parte risutla sbagliata. Conviene ripristinare il DB da produzione e poi lanciare queste.
<div class="mw-collapsible mw-collapsed" style="border:solid 1px;padding:2rem;">
<syntaxhighlight lang="sql">
-- Lead Manager - Adeguamento nuovi ID category e product (occasioni) (lanciare su database DesignbestCore e eseguire l'output su LeadManager)
SELECT 'UPDATE category SET code = ''' + CAST(ID AS VARCHAR(10)) + ''' WHERE code = ''' + CAST(temp_old_id AS VARCHAR(10)) + ''' AND category = ''' + [Name] + ''''
FROM Category
WHERE CategoryLevel = 1

-- REMEMBER: l'esecuzione di queste update dura quasi 7 minuti (lanciare su database DesignbestCore e eseguire l'output su LeadManager)
SELECT 'UPDATE product SET code = ''' + CAST(ProductId AS VARCHAR(10)) + ''' WHERE code = ''O' + CAST(old_BargainID AS VARCHAR(10)) + ''''
FROM ProductBargain
WHERE old_BargainID <> 0

-- category change in product (database LeadManager)
UPDATE product SET categoryID = 2 WHERE categoryID = 23
UPDATE product SET categoryID = 17 WHERE categoryID = 24
UPDATE product SET categoryID = 2 WHERE categoryID = 28
UPDATE product SET categoryID = 2 WHERE categoryID = 30
UPDATE product SET categoryID = 2 WHERE categoryID = 36
UPDATE product SET categoryID = 10 WHERE categoryID = 37
UPDATE product SET categoryID = 6 WHERE categoryID = 40
UPDATE product SET categoryID = 20 WHERE categoryID = 41
UPDATE product SET categoryID = 12 WHERE categoryID = 44
UPDATE product SET categoryID = 1 WHERE categoryID = 45
UPDATE product SET categoryID = 2 WHERE categoryID = 46
UPDATE product SET categoryID = 3 WHERE categoryID = 47
UPDATE product SET categoryID = 4 WHERE categoryID = 48
UPDATE product SET categoryID = 5 WHERE categoryID = 49
UPDATE product SET categoryID = 6 WHERE categoryID = 50
UPDATE product SET categoryID = 7 WHERE categoryID = 51
UPDATE product SET categoryID = 8 WHERE categoryID = 52
UPDATE product SET categoryID = 20 WHERE categoryID = 53
UPDATE product SET categoryID = 1 WHERE categoryID = 54
UPDATE product SET categoryID = 10 WHERE categoryID = 55
UPDATE product SET categoryID = 11 WHERE categoryID = 56
UPDATE product SET categoryID = 12 WHERE categoryID = 57
UPDATE product SET categoryID = 13 WHERE categoryID = 58
UPDATE product SET categoryID = 14 WHERE categoryID = 59
UPDATE product SET categoryID = 18 WHERE categoryID = 60
UPDATE product SET categoryID = 19 WHERE categoryID = 61
UPDATE product SET categoryID = 21 WHERE categoryID = 62
UPDATE product SET categoryID = 22 WHERE categoryID = 63
-- category change in repository
UPDATE repository SET categoryID = 2 WHERE categoryID = 23
UPDATE repository SET categoryID = 17 WHERE categoryID = 24
UPDATE repository SET categoryID = 2 WHERE categoryID = 28
UPDATE repository SET categoryID = 2 WHERE categoryID = 30
UPDATE repository SET categoryID = 2 WHERE categoryID = 36
UPDATE repository SET categoryID = 10 WHERE categoryID = 37
UPDATE repository SET categoryID = 6 WHERE categoryID = 40
UPDATE repository SET categoryID = 20 WHERE categoryID = 41
UPDATE repository SET categoryID = 12 WHERE categoryID = 44
UPDATE repository SET categoryID = 1 WHERE categoryID = 45
UPDATE repository SET categoryID = 2 WHERE categoryID = 46
UPDATE repository SET categoryID = 3 WHERE categoryID = 47
UPDATE repository SET categoryID = 4 WHERE categoryID = 48
UPDATE repository SET categoryID = 5 WHERE categoryID = 49
UPDATE repository SET categoryID = 6 WHERE categoryID = 50
UPDATE repository SET categoryID = 7 WHERE categoryID = 51
UPDATE repository SET categoryID = 8 WHERE categoryID = 52
UPDATE repository SET categoryID = 20 WHERE categoryID = 53
UPDATE repository SET categoryID = 1 WHERE categoryID = 54
UPDATE repository SET categoryID = 10 WHERE categoryID = 55
UPDATE repository SET categoryID = 11 WHERE categoryID = 56
UPDATE repository SET categoryID = 12 WHERE categoryID = 57
UPDATE repository SET categoryID = 13 WHERE categoryID = 58
UPDATE repository SET categoryID = 14 WHERE categoryID = 59
UPDATE repository SET categoryID = 18 WHERE categoryID = 60
UPDATE repository SET categoryID = 19 WHERE categoryID = 61
UPDATE repository SET categoryID = 21 WHERE categoryID = 62
UPDATE repository SET categoryID = 22 WHERE categoryID = 63

-- Tronchiamo config_company_category
TRUNCATE TABLE config_company_category

-- Cancelliamo le category outlet
DELETE FROM category WHERE categoryID >= 23

-- Ci inseriamo tutto di tutti
INSERT INTO config_company_category(companyID, categoryID)
SELECT DISTINCT company.companyID, category.categoryID
FROM category, company
ORDER BY company.companyID, category.categoryID
</syntaxhighlight>
</div>

==== Eccezione 44334 (DEPRECATO) ====
La shop net di test, 44334, dopo l'import risulta "azzoppata". Bisogna mettere i record dei suoi Manufacturer Custom dentro al mapping:
<syntaxhighlight lang="sql">
INSERT INTO Mapping_ShopPoint_Manufacturer_Ext(Context,ShopPointID,ManufacturerID,DisplayOrder)
SELECT 44334, 44334, Manufacturer_Ext.ID, 100
FROM Manufacturer_Ext
WHERE Context = 44334
</syntaxhighlight>

==== Tabelle Pre-popolate ====
Queste tabelle sono statiche e non vengono toccate dall'importazione:
* Agent
* ShopNetStatus
* City
* Province
* Region
* Language
* Selection
* ContactRoles
* ManufacturerContractType

==== Cosa manca ====
L'importazione lascia vuote le tabelle:<br/>
* Mapping_Selection_ShopPoint
* ShopPointSponsor

==== SEO Title e Description ====
Le nuove tabelle ospitano i campi MetTitle e MetaDescription, nell'importazione vengono lasciati vuoti.<br/>
Questi dati li abbiamo, stanno nella <code>MetaTagsHelper</code>, dobbiamo successivamente pensare a come importarli nei campi giusti.

===== SEO Regole =====
Creata una tabella '''SeoRules''' culturizzata tramite LanguageId.