duplicating a UCommerce Store

In a recent project we used uCommerce (6.XX) with Sitecore 8.1 to implement an international webshop.

Each language in sitecore will eventually be linked to it’s own shop,  starting with the italian one (linked to it-IT).  We opted to create a separate store per country/language in uCommerce because each of them would have some unique features. Some would have different prices, SKU’s, tax calculation and so on.

After we finished the Italian shop, we wanted to duplicate it so that the content editors wouldn’t have to enter all the common data again. As it turns out this isn’t supported (yet) by uCommerce. After confirming with our uCommerce buddy, I wrote an SQL script that would do just that.

If you are reading this it means you might be thinking of doing the same thing, so I must warn you I took a few shortcuts here and there.  For instance the product definitions are not duplicated and the script has some hard coded things like disabling product reviews. All this can be easily changed in the stored procedure code below. As the goal was only to do the bulk of the work you might want to create/duplicate more data.

Here’s a list of what the script creates or duplicates:

  • Currency
  • PriceGroup
  • Country (and existing country is activated)
  • Definition
  • Payment Method
  • Shipping Method
  • ProductCatalogGroup
  • Mapping between Shipping Method and ProductCatalogGroup
  • Mapping between Shipping Methood and Payment Method
  • Product Catalog
  • Mapping between Product Catalog and PriceGroup
  • Categories of products
  • Products (duplicating existing only)
  • Product properties
  • Product description
  • Product Category relation
  • Price
  • Variants
  • Variant Properties
  • Variant Description
  • Variant Price

You can call the stored procedure with the following script. All you have to do is feed in the correct parameters for you situation. The one shortcut I really don’t like is the SKU suffix. product SKU’s have to be unique so I add the suffix to garantee that. Since the content editor has to enter new ones anyway it’s not that big of a deal.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EXEC CreateStore 
'Ruben' --createdBy
,'USD' --CurrencyIsoCode
,143 --currentExchangeRate
,'United States' --countryName
,0.22 --vatRate
,'Pricing for US eCommerce' --priceGroupDescription
,'en-US' --cultureCode
,'Adyen' --definitionName
,'Configuration for Adyen' --definitionDescription
,'Adyen Test' --PaymentMethodName
,'Adyen' --paymentMethodSeviceName
,0.0000 --feePerentage
,'ShipmentName' --shippingmethodName
,'United States' --productCatalogGroupName
,'CatalogName' --productCatalogName
,24 --parentCatalog
,'CategoryName' --productCategoryDefinitionName
,'_US' --SKU_suffix

And finally the stored procedure script. With the exception of the products (and related data) I always check if the data already exists, so you could execute the stored procedure several times if you wanted to. This was mostly handy during the development and saved me a lot of cleaning up. This way I could add the next bit and just execute the entire thing again without having to restore my database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
CREATE PROCEDURE CreateStore	
@createdBy nvarchar(20),
@currencyIsoCode nvarchar(4),
@currenctExchangeRate int,
@countryName nvarchar(25),
@vatRate decimal(5,2),
@priceGroupDescription nvarchar(250),
@cultureCode nvarchar(6),
@definitionName nvarchar(512),
@definitionDescription nvarchar(max),
@paymentMethodName nvarchar(50),
@paymentMethodServiceName nvarchar(50),
@feePercent decimal(18,4),
@shippingMethodName nvarchar(128),
@productCatalogGroupName nvarchar(128),
@productCatalogName nvarchar(128),
@parentCatalog int,
@productCategoryDefinitionName nvarchar(128),
@skuSuffix nvarchar(5)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @currencyId int
DECLARE @pricegroupId int
DECLARE @countryId int
DECLARE @paymentMethodId int
DECLARE @shippingMethodId int
DECLARE @definitionId int
DECLARE @definitionTypeId int
DECLARE @emailProfileId int
DECLARE @orderNumberId int
DECLARE @productCatalogGroupId int
DECLARE @productCatalogId int
DECLARE @ProductCatalogPriceGroupRelationId int
DECLARE @ProjectCategoryId int

SELECT @definitionTypeId = definitionTypeId
FROM uCommerce_DefinitionType
WHERE name = 'PaymentMethod'

SELECT @emailProfileId = emailprofileid
FROM uCommerce_EmailProfile
WHERE name ='Default'

SELECT @orderNumberId = ordernumberid
FROM uCommerce_OrderNumberSerie
WHERE OrderNumberName = 'Default'

SELECT @ProjectCategoryId = definitionid
FROM ucommerce_Definition
WHERE name = @productCategoryDefinitionName

-- create currency
SELECT @currencyId = currencyid
FROM uCommerce_Currency
WHERE isocode = @currencyIsoCode AND deleted = 0
IF (@currencyId is not null)
BEGIN
PRINT 'currency already exists'
END
ELSE
BEGIN
INSERT uCommerce_Currency(isocode,exchangerate,deleted,guid)
VALUES (@currencyIsoCode, @currenctExchangeRate, 0, NEWID())
SELECT @currencyId = SCOPE_IDENTITY()
END

-- create pricegroup
SELECT @pricegroupId = pricegroupid
FROM uCommerce_PriceGroup
WHERE name = @countryName AND currencyId = @currencyId
IF (@pricegroupId is not null)
BEGIN
PRINT 'pricegroup already exists'
END
ELSE
BEGIN
INSERT uCommerce_PriceGroup(name,currencyid,vatrate,description,
createdon,createdby,modifiedon,modifiedby,deleted,guid)
VALUES (@countryName ,@currencyId,@vatRate, @priceGroupDescription,
getdate(),@createdBy,getdate(),@createdBy,0,NEWID())
SELECT @pricegroupId = SCOPE_IDENTITY()
END

-- create country
SELECT @countryId = countryid
FROM uCommerce_Country
WHERE name = @countryName AND Culture = @cultureCode
IF (@countryId is not null)
BEGIN
PRINT 'country already exists, activating it'
UPDATE uCommerce_Country
SET deleted = 0
WHERE name = @countryName
AND Culture = @cultureCode
END
ELSE
BEGIN
INSERT uCommerce_Country(name,culture,deleted)
VALUES (@countryName, @cultureCode, 0)
SELECT @countryId = SCOPE_IDENTITY()
END

-- create definition
SELECT @definitionId = definitionId
FROM uCommerce_Definition
WHERE name = @definitionName AND DefinitionTypeId = @definitionTypeId
IF (@definitionId is not null)
BEGIN
PRINT 'definition already exists'
END
ELSE
BEGIN
INSERT uCommerce_Definition(name,definitiontypeid,description,deleted,sortorder,guid,builtin)
VALUES (@definitionName, @definitionTypeId, @definitionDescription,0,0,NEWID(),0)
SELECT @definitionId = SCOPE_IDENTITY()
END

-- create payment method
SELECT @paymentMethodId = paymentMethodId
FROM uCommerce_PaymentMethod
WHERE name = @paymentMethodName
AND paymentMethodServiceName = @paymentMethodServiceName
AND definitionId = @definitionId
IF (@paymentmethodid is not null)
BEGIN
PRINT 'payment method already exists'
END
ELSE
BEGIN
INSERT ucommerce_paymentmethod(name,feepercent,paymentmethodservicename,enabled,deleted,
modifiedon,modifiedby,pipeline,definitionid)
VALUES (@paymentmethodname, @feepercent, @paymentmethodservicename,1,0,
getdate(),@createdby,'checkout',@definitionid)
SELECT @paymentmethodid = scope_identity()
END

-- create shippingMethod
SELECT @shippingMethodId = ShippingMethodId
FROM uCommerce_ShippingMethod
WHERE name = @shippingMethodName
AND paymentmethodId = @paymentMethodId

IF (@shippingMethodId is not null)
BEGIN
PRINT 'shipping method already exists'
END
ELSE
BEGIN
INSERT uCommerce_ShippingMethod(name,PaymentMethodId,ServiceName,deleted)
VALUES (@shippingMethodName, @paymentMethodId, 'SinglePriceService', 0)
SELECT @shippingMethodId = SCOPE_IDENTITY()
END

-- create ProductCatalogGroup
SELECT @productCatalogGroupId = productCatalogGroupId
FROM uCommerce_ProductCatalogGroup
WHERE name = @productCatalogGroupName
AND emailprofileId = @emailProfileId
AND currencyid= @currencyId AND ordernumberId = @orderNumberId
IF (@productCatalogGroupId is not null)
BEGIN
PRINT 'ProductCatalogGroup already exists'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalogGroup(name,EmailProfileId,currencyid,domainid,
OrderNumberId,deleted,CreateCustomersAsMembers,ProductReviewsRequireApproval,guid )
VALUES (@productCatalogGroupName, @emailProfileId,@currencyid,'website',
@orderNumberId,0,0,0,NEWID())
SELECT @productCatalogGroupId = SCOPE_IDENTITY()
END

--map shipping method to product catalog group
IF((SELECT count(*) FROM uCommerce_ProductCatalogGroupShippingMethodMap
WHERE productcataloggroupid = @productCatalogGroupId AND shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ProductCatalogGroupShippingMethodMap(productcataloggroupid,shippingMethodId)
VALUES(@productCatalogGroupId,@shippingMethodId)
END

--map shipping method to country
IF((SELECT count(*) FROM uCommerce_ShippingMethodCountry
WHERE CountryId = @countryId AND shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ShippingMethodCountry(CountryId,shippingMethodId)
VALUES(@CountryId,@shippingMethodId)
END

--map shipping method to paymentmethod
IF((SELECT count(*) FROM uCommerce_ShippingMethodPaymentMethods
WHERE PaymentMethodId = @paymentMethodId AND shippingMethodId = @shippingMethodId) = 0)
BEGIN
INSERT uCommerce_ShippingMethodPaymentMethods(paymentMethodId,shippingMethodId)
VALUES(@paymentMethodId,@shippingMethodId)
END

-- create ProductCatalog
SELECT @productCatalogId = productCatalogId
FROM uCommerce_ProductCatalog
WHERE name = @productCatalogName
AND ProductCatalogGroupId = @productCatalogGroupId
AND PriceGroupId = @pricegroupId
IF (@productCatalogId is not null)
BEGIN
PRINT 'ProductCatalog already exists'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalog(productcataloggroupid,name,pricegroupid,ShowPricesIncludingVAT,
IsVirtual,DisplayOnWebSite,LimitedAccess,Deleted,CreatedBy,CreatedOn,ModifiedBy,ModifiedOn,
SortOrder,guid)
VALUES (@productCatalogGroupId,@productCatalogName,@pricegroupId,0,0,1,0,0,@createdBy,getdate(),
@createdBy,getdate(),0,NEWID())
SELECT @productCatalogId = SCOPE_IDENTITY()
END

-- create ProductCatalog pricegroup relation
SELECT @ProductCatalogPriceGroupRelationId = ProductCatalogPriceGroupRelationId
FROM uCommerce_ProductCatalogPriceGroupRelation
WHERE ProductCatalogId = @productCatalogId and PriceGroupId = @pricegroupId
IF (@ProductCatalogPriceGroupRelationId is not null)
BEGIN
PRINT 'ProductCatalog is already linked to pricegroup'
END
ELSE
BEGIN
INSERT uCommerce_ProductCatalogPriceGroupRelation(productcatalogid,PriceGroupId,SortOrder)
VALUES (@productCatalogId,@priceGroupId,0)
SELECT @ProductCatalogPriceGroupRelationId = SCOPE_IDENTITY()
END

-- create Categories for catalog
IF((SELECT count(categoryId) FROM uCommerce_Category
WHERE productCatalogId = @productCatalogId AND definitionId = @ProjectCategoryId) = 0)
INSERT INTO uCommerce_Category(name,DisplayOnSite,createdon,ParentCategoryId,
ProductCatalogId,ModifiedOn,ModifiedBy,Deleted,SortOrder,CreatedBy,DefinitionId,guid)
SELECT name,DisplayOnSite,getdate(),ParentCategoryId,
@productCatalogId,getdate(),@createdBy,0,0,@createdBy,@ProjectCategoryId,NEWID()
FROM ucommerce_category
WHERE productcatalogid = @parentCatalog

-- create products
IF OBJECT_ID('tempdb..#Products') IS NOT NULL DROP TABLE #Products

SELECT productid
INTO #Products
FROM uCommerce_Product
WHERE parentproductid IS NULL AND variantsku IS NULL

DECLARE @Id int
DECLARE @newProductId int
DECLARE @categoryId int

WHILE EXISTS(SELECT * FROM #Products)
BEGIN
SELECT Top 1 @Id = productid FROM #Products
/*
* create product
* add product/category relation => [uCommerce_CategoryProductRelation]
=> duplicate FROM existing, change id's to correct product/category id's
* add price => [uCommerce_PriceGroupPrice]
*/


INSERT INTO uCommerce_Product(sku,name,displayonsite,weight,productDefinitionId,
AllowOrdering,modifiedBy, ModifiedOn, CreatedBy, CreatedOn, Guid)
SELECT sku + @skuSuffix,name,displayonsite,weight,productdefinitionId,
allowordering,@createdBy, getdate(),@createdBy, getdate(),NEWID()
FROM uCommerce_Product
WHERE productid = @Id

SELECT @newProductId = SCOPE_IDENTITY()

PRINT 'creating properties for' + cast(@newProductId as varchar(5))
--create product properties
INSERT INTO uCommerce_ProductProperty(value,ProductDefinitionFieldId,ProductId)
SELECT value, productdefinitionfieldid, @newProductId
FROM uCommerce_ProductProperty
WHERE productid = @Id

PRINT 'creating descriptions for' + cast(@newProductId as varchar(5))
--create product descriptions
INSERT INTO uCommerce_ProductDescription(productid,DisplayName,shortdescription,
LongDescription,CultureCode)
SELECT @newProductId,displayname,shortdescription,LongDescription,CultureCode
FROM uCommerce_ProductDescription
WHERE productid = @id

SELECT @categoryId = u2.categoryid
FROM uCommerce_category u1
INNER JOIN uCommerce_category u2 on u1.name = u2.name
INNER JOIN uCommerce_CategoryProductRelation cpr
on u1.categoryId = cpr.CategoryId
WHERE cpr.ProductId = @Id
AND u2.definitionId = @ProjectCategoryId
AND u2.productcatalogid = @productCatalogId

PRINT 'creating product/category relations for' + cast(@newProductId as varchar(5))
--create category/product relation
INSERT uCommerce_CategoryProductRelation(ProductId,CategoryId,SortOrder)
VALUES (@newProductId,@categoryId,0)


PRINT 'adding prices for' + cast(@newProductId as varchar(5))
--add price
INSERT uCommerce_PriceGroupPrice(productId,Price,DiscountPrice,PriceGroupId)
SELECT @newProductId, price, discountprice, @pricegroupId
FROM uCommerce_PriceGroupPrice
WHERE ProductId = @Id

--add product variants and prices
IF OBJECT_ID('tempdb..#Productvariants') IS NOT NULL DROP TABLE #Productvariants
SELECT productid
INTO #Productvariants
FROM uCommerce_Product
WHERE parentproductid = @Id
AND variantsku IS NOT NULL

DECLARE @variantId int
DECLARE @newVariantId int

WHILE EXISTS(SELECT * FROM #Productvariants)
BEGIN
SELECT Top 1 @variantId = productid
FROM #Productvariants

INSERT INTO uCommerce_Product(sku,variantsku,ParentProductId,name,displayonsite,weight,
productDefinitionId,AllowOrdering,modifiedBy, ModifiedOn, CreatedBy, CreatedOn, Guid)
SELECT sku+ @skuSuffix ,variantsku + @skuSuffix,@newProductId,name,displayonsite,weight,
productdefinitionId,allowordering,@createdBy, getdate(),@createdBy, getdate(),NEWID()
FROM uCommerce_Product
WHERE productid = @variantId

SELECT @newVariantId = SCOPE_IDENTITY()

--create product variant properties
INSERT INTO uCommerce_ProductProperty(value,ProductDefinitionFieldId,ProductId)
SELECT value, productdefinitionfieldid, @newVariantId
FROM uCommerce_ProductProperty
WHERE productid = @variantId

--create product descriptions
--INSERT INTO uCommerce_ProductDescription(productid,DisplayName,shortdescription,
LongDescription,CultureCode)
--SELECT @newProductId,displayname,shortdescription,LongDescription,CultureCode
--FROM uCommerce_ProductDescription
--WHERE productid = @variantId

--add variant price
INSERT uCommerce_PriceGroupPrice(productId,Price,DiscountPrice,PriceGroupId)
SELECT @newVariantId, price, discountprice, @pricegroupId
FROM uCommerce_PriceGroupPrice
WHERE ProductId = @variantId

Delete #Productvariants WHERE productid = @variantId
END
--end product variants and prices
Delete #Products WHERE productid = @Id
END
END

At first it seemed that we didn’t need duplicate product definitions since they would be identical. As it tends to happen in IT… things change. So here’s a script that will do just that. Since the relations between the definitions and the field values will probably change, I did not duplicate them. Here’s a list of what the script duplicates: * ProductDefinition * ProductDefinitionField

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- fields and inheritance should be set manually
-- add ProductDefinitions
IF OBJECT_ID('tempdb..#ProductDefinitions') IS NOT NULL DROP TABLE #ProductDefinitions
SELECT productdefinitionid,name,deleted
INTO #ProductDefinitions
FROM uCommerce_ProductDefinition

DECLARE @name nvarchar(512)
DECLARE @deleted bit
DECLARE @newDefinitionId int
DECLARE @productdefinitionId int
DECLARE @skuSuffix nvarchar(50)

WHILE EXISTS(SELECT * FROM #ProductDefinitions)
BEGIN
SELECT Top 1 @productdefinitionId = productdefinitionId,
@name = name, @deleted = deleted
FROM #ProductDefinitions

INSERT INTO uCommerce_ProductDefinition(name,deleted,guid)
SELECT name + @skuSuffix, @deleted, NEWID()
FROM uCommerce_ProductDefinition
WHERE productdefinitionId = @productdefinitionId

SELECT @newDefinitionId = SCOPE_IDENTITY()

--create product definition fields
INSERT INTO uCommerce_ProductDefinitionField
SELECT DataTypeId,@newDefinitionId,Name,DisplayOnSite,IsVariantProperty,Multilingual
,RenderInEditor,Searchable,Deleted,SortOrder,Facet,NEWID()
FROM uCommerce_ProductDefinitionField
WHERE productdefinitionid = @productdefinitionId


Delete #ProductDefinitions WHERE productdefinitionid = @productdefinitionid
END

And that’s the end of this chapter. The presented code here may not be a silver bullet but I hope it’s at least a machete clearing a path for you.