Filed under Development , SQL
This was no easy task and took arund 3 days of banging my head against the wall but got there in the end...
We had an old ecommerce system which we were migrating to the new (and Fantastic) Nop Commerce 2.30, there is currently no import option in this version, removed in version 2.0 i believe. So here is a step by step process of what I had to do.
Step 1 - Create Hashed passwords and Salt Key
Using the old database table for Customers, in my case "RegisteredUsers" which only stored the passwords in plain text i created two new columns, NopPassword and PasswordSalt.
Against that table i ran the following SQL Query.
DECLARE @NopPassword VarChar(50)
DECLARE @MyUserID INT
DECLARE myMaintblCursor CURSOR FOR
SELECT RegisteredUsers.UserID FROM RegisteredUsers
OPEN myMaintblCursor
--Read the initial User ID value from the cursor
FETCH NEXT FROM myMaintblCursor
INTO @MyUserID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Salt VarChar(50)
EXEC dbo.sp_GenerateRandomString 8, @Salt OUTPUT
UPDATE RegisteredUsers
SET NopPassword = SUBSTRING(UPPER(CONVERT(VarChar(50), HASHBYTES('SHA1', Password + @Salt),1)),3,40), PasswordSalt = @Salt
--CONVERT(NVARCHAR(MAX), HASHBYTES('SHA1', Password + @Salt),1), PasswordSalt = @Salt
--SUBSTRING(UPPER(master.dbo.fn_varbintohexstr(CAST(HASHBYTES('SHA1', Password + @Salt)as varbinary ))),0,40), PasswordSalt = @Salt
WHERE RegisteredUsers.UserID = @MyUserID
--get the next User ID value from the cursor
FETCH NEXT FROM myMaintblCursor
INTO @MyUserID
END
DEALLOCATE myMaintblCursor
Probably not the best way to do this but worked for me, I had to use a cursor so that I could generate a Randon Salt key for every row, then i added this to the origonal user password. This was where i had the biggest problem, i intitally created the new Columns with NVARCHAR(MAX) to match the NOP DB, which meant the HASHED version of the password was coming in wierd symbols, Then i tried to convert this using "master.dbo.fn_varbintohexstr" but all i ever ended up with was a HEX STRING value which was wrong.
Solution was to change the NopPassword and Password salt to VARCHAR(50) which gave me the Key, but 2 characters to big and lowercase. Nop does not like the password hash in lower case so thats where the following line came in.
SUBSTRING(UPPER(CONVERT(VarChar(50), HASHBYTES('SHA1', Password + @Salt),1)),3,40), PasswordSalt = @Salt
I used the following website to check if things were correct, at last it was! Woop woop!
Also you will need the following SP to generate the random Salt Key
PROCEDURE [dbo].[sp_GenerateRandomString]
(
@sLength INT = 10 ,
@randomString NVARCHAR(255) OUTPUT
)
AS
BEGIN
DECLARE @counter INT ;
DECLARE @nextChar NCHAR(1) ;
SET @counter = 1 ;
SET @randomString = '' ;
WHILE @counter <= @sLength
BEGIN
SET @nextChar = NCHAR(ROUND(RAND() * 93 + 33, 0)) ;
IF ASCII(@nextChar) NOT IN( 34, 39, 40, 41, 44, 46, 96, 58, 59 )
BEGIN
SET @randomString = @randomString + @nextChar ;
SET @counter = @counter + 1 ;
END ;
END ;
END ;
Step 2 - Import User details
From here i wrote an import script to import the users from the old DB into NOP.
INSERT INTO Nop.dbo.Customer
(Id,CustomerGuid, Username, Email, Password,PasswordFormatId, PasswordSalt, TaxDisplayTypeId, IsTaxExempt, VatNumberStatusId, UseRewardPointsDuringCheckout, Deleted,IsSystemAccount, CreatedOnUtc, LastActivityDateUtc, Active, BillingAddress_Id, ShippingAddress_Id)
SELECT UserID, NEWID(),EmailAddress, EmailAddress, NopPassword,'1', PasswordSalt, '0','0', '10', '0', '0', '0', GETDATE(),GETDATE(), '1',UserID, UserID FROM OldSite.dbo.RegisteredUsers
WHERE OldSite.dbo.RegisteredUsers.ApprovedStatus = 'Approved'
NOTE: Indenity needs to be set to NO as we need to keep the same ID for the users across all the relavent tables, remember to turn that back to Yes.
Step 3 - Setup Address mapping
next up is the address.
INSERT INTO Nop.dbo.CustomerAddresses
(Customer_Id, Address_Id)
SELECT Id, Id FROM Nop.dbo.Customer
WHERE Nop.dbo.Customer.Id > 7
I only used the where statement becuase I had some existing data in the new Nop DB.
NOTE: You will need to turn of the Foreign Key retrainst on both Shipping and billing
Step 4 - Setup Roles
Then the Role mapping
INSERT INTO Nop.dbo.Customer_CustomerRole_Mapping
(Customer_Id, CustomerRole_Id)
SELECT Id, '3' FROM Nop.dbo.Customer
WHERE Nop.dbo.Customer.Id > 7
3 is the ID for registered users, but you could use any ID you wanted.
Step 4 - Import Address's
Then the Users address's.
INSERT INTO Nop.dbo.Address
(Id,FirstName, LastName, Email, Company, City, Address1, Address2, ZipPostalCode, PhoneNumber, CreatedOnUtc)
SELECT UserID, FirstName, Surname, EmailAddress, CompanyName, TownCity, Address1, Address2, Postcode, Telephone, GETDATE() FROM Live.dbo.RegisteredUsers
WHERE Live.dbo.RegisteredUsers.ApprovedStatus = 'Approved'
Optional
The only thing which i did not do which you could is add in the address/name and email details into the CustomerAttribute table, you need this if you want the users to see thier address on the first page of "My Account".
Hope this helps anyone having to do the same thing and saves them some time.!
b781e483-d025-4ffa-8dea-26b6367fc304|0|.0