ucommerce

adding a country to existing uCommerce catalog

After creating two different catalogs for two different countries (US and Italy) in our uCommerce solution, I needed to add another country. This time however the Italy store and catalog are changed (manually) into an EMEA store. Therefore the new country is added to this EMEA store, in my case, using the same payment method and currency.

Basically you can use the stored procedure for creating a new store and remove the things you do not need. You will need to change the parameters somewhat to link the new country to the payment method (and perhaps other relations).

Here’s the stored procedure that let’s you do that.

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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ruben Verschueren
-- Create date: 2016/07/28
-- Description: creates a new uCommerce country store
-- and adds it to an existing store
-- =============================================
CREATE PROCEDURE AddCountryToStore
@createdBy nvarchar(20),
@currencyIsoCode nvarchar(4),
@currenctExchangeRate int,
@countryName nvarchar(25),
@vatRate decimal(5,2),
@priceGroupDescription nvarchar(250),
@cultureCode nvarchar(6),
@paymentMethodId int,
@feePercent decimal(18,4),
@shippingMethodName nvarchar(128),
@productCatalogId int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @currencyId int
DECLARE @pricegroupId int
DECLARE @countryId int
DECLARE @shippingMethodId int
DECLARE @definitionTypeId int
DECLARE @emailProfileId int
DECLARE @orderNumberId int
DECLARE @productCatalogGroupId int
DECLARE @ProductCatalogPriceGroupRelationId 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'

-- 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'
UPDATE uCommerce_PriceGroup
SET deleted = 0 WHERE name = @countryName AND currencyId = @currencyId
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'
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


-- needed?
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

--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

-- 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
END

Now that we created the stored procedure, we can execute it as many times as we want adding more countries to our store. Obviously you need to change the values of the parameters especially the PaymentMethodId and productCatalogId. In the uCommerce backend you will now see the new country, pricegroup etc. If you open up the catalog, you will now see the new country as an option in the allowed priceGroups. Just enable the checkbox and you’re good to go… after you add all the prices on the products that is.

1
2
3
4
5
6
7
8
9
10
11
12
EXEC AddCountryToStore 
'Ruben' --createdBy
,'EUR' --CurrencyIsoCode
,100 --currentExchangeRate
,'Netherlands' --countryName
,0.21 --vatRate
,'Pricing for NL eCommerce' --priceGroupDescription
,'nl-NL' --cultureCode
,8 --PaymentMethodId
,0.0000 --feePerentage
,'MyShippingMethod' --shippingmethodName
,5 --productCatalogId

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.

UCommerce Extensions

Over the course of my first Sitecore 8 and UCommerce integration project I’ve created some extension methods that might be useful in other projects. Most of these are straight forward so let’s dive into it.

Basket

the culture difference

In case you have different stores for different you need to keep your baskets separated. You cannot have a basket with product X with Euro as currency and product Y with Dollars as currency. So what we decided to do is delete the existing basket when the visitor browses a different Country website. Our site had a country/language selector which allowed the visitor to do that.

The following code does just that. It checks if the basket culture corresponds with the SiteContext culture.

1
2
3
4
5
6
7
8
9
public static void EnsureBasketCulture(this Basket basket)
{

if (!basket.PurchaseOrder.CultureCode.Equals(SiteContext.Current.CurrentCulture.Name))
{
SiteContext.Current.OrderContext.ClearBasketInformation();
basket = SiteContext.Current.OrderContext.GetBasket();
basket.PurchaseOrder.Save();
}
}

Now in case you may not want to loose any basket data, so we want to save that. The following code adds a summary of the existing basket in an audit trail in the new one. Now you might be thinking you want to add the order lines to the new basket and you can do that. However it was out of scope for our project and it could lead you down the rabbit hole since you would have to check if the product is sold in the new country, the prices might be different between countries, not to mention taxes and so on.

This code is just example and you might want to be more defensive in your coding, it’s merely for reference.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public static void EnsureBasketCulture(this Basket basket)
{

if (!basket.PurchaseOrder.CultureCode.Equals(SiteContext.Current.CurrentCulture.Name))
{
string summary = string.Empty;
if (basket.PurchaseOrder != null && basket.PurchaseOrder.OrderLines.Any())
{
summary = string.Join(" | ", basket.PurchaseOrder.OrderLines.Select(
l => string.Format("{0} - #{1}", l.ProductName, l.Quantity)));
}
SiteContext.Current.OrderContext.ClearBasketInformation();
basket = SiteContext.Current.OrderContext.GetBasket();
basket.PurchaseOrder.Save();

var auditline = new OrderStatusAudit();
var order = basket.PurchaseOrder;
auditline.CreatedOn = DateTime.Now;
auditline.Message = string.Format("Country changed, new basket created: {0}", summary);
auditline.PurchaseOrder = order;
auditline.NewOrderStatus = basket.PurchaseOrder.OrderStatus;
auditline.Save();
order.AddOrderStatusAudit(auditline);
}
}

Payment method

the delivery date modification

In the Adyen payment method properties you can set standard shipping time. To be able to show the estimated delivery date of the order, we added the following extension method. It simply retrieves the properties from the payment method and corrects the date (adding only business days).

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
private static DateTime GetDateAdjustment(PaymentMethod paymentMethod)
{

AdyenPaymentMethodProperties properties = new AdyenPaymentMethodProperties(paymentMethod.PaymentMethodProperties);
string shipDays = properties.ShipDays;
string shipHours = properties.ShipHours;
string shipMinutes = properties.ShipMinutes;

DateTime shipBefore = DateTime.Now;
if (!string.IsNullOrEmpty(shipDays))
{
shipBefore = shipBefore.AddBusinessDays(int.Parse(shipDays));
}

if (!string.IsNullOrEmpty(shipHours))
{
shipBefore = shipBefore.AddHours(int.Parse(shipHours));
}

if (!string.IsNullOrEmpty(shipMinutes))
{
shipBefore = shipBefore.AddMinutes(int.Parse(shipMinutes));
}

return shipBefore;
}

the property enquiry

The following extension method avoids duplicating the same code each time you need to access a payment method property. I also saves a lot of time trying to figure out how to do that again.

1
2
3
4
public static string GetPaymentMethodPropertiesValue(this ICollection<PaymentMethodProperty> properties, string key)
{

return properties.FirstOrDefault(p => p.DefinitionField != null && p.DefinitionField.Name.Equals(key)).Value;
}

PuchaseOrder

the American way As we had quite a lot of divergent logic for the US, I added this extension method that inspects the culture to find out if the order is in place in/for the US.

1
2
3
4
public static bool IsAmerican(this PurchaseOrder order)
{

return order.CultureCode.Equals("en-US");
}

the pipeline verification

Sometimes something happens, some times it’s bad. let’s leave it at that. I needed to check if the Checkout pipeline was executed or not, so here’s the code.

1
2
3
4
5
6
7
8
9
10
public static bool CheckoutPipelineHasAlreadyBeenExecutedForPayment(this PurchaseOrder order)
{

if (order.OrderStatus.OrderStatusId == (int)OrderStatusCode.Basket ||
order.OrderStatus.OrderStatusId == (int)OrderStatusCode.Processing)
{
return false;
}

return true;
}

the order number assignment

In case you want to assign an order number at some random point in the order process, this extension method will let you do that. We used it to assign the order number when the customer reaches the confirmation page and the payment was authorised.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public static void AssignOrdernumberIfEmpty(this PurchaseOrder order,string source = "PurchaseOrderExtension")
{
if (string.IsNullOrEmpty(order.OrderNumber))
{
try
{
var numberSeriesService = ObjectFactory.Instance.Resolve<INumberSeriesService>();
order.OrderNumber =
numberSeriesService.GetNumber(order.ProductCatalogGroup.OrderNumberSerie.OrderNumberName);
Log.Info(string.Format("Assigning order number to order. id:
{0}, number {1}", order.OrderId, order.OrderNumber),source);
order.Save();
}
catch (Exception ex)
{
Log.Error(string.Format("An error occured assingning the order ordernumber to order (ID): {0}"
,order.OrderId) ,ex);
}
}
}

Custom Model

the currency format issue

The UCommerce version we were using 6.x had a fixed currency format. So I wrote this class so I could make my own money. You can provide a currency format to the ToString method or use the default one. In our project we only displayed decimals when they’re not zero.

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
public class MyMoney : Money
{
public MyMoney(decimal value, CultureInfo culture, Currency currency)
: base(value, culture, currency)
{

}

public MyMoney(decimal value, Currency currency) : base(value, currency)
{

}

public MyMoney(Money money) : base(money.Value, money.Culture, money.Currency)
{


}

//uCommerce ToString method has a fixed currency format
public string ToString(string format)
{

if (format.IsNullOrEmpty())
{
format = "C0";
}
string cultureSpecificStringValue = Value.ToString(format, Culture);
return cultureSpecificStringValue;
}


//should return the value with 2 decimals if it is not a whole number
public override string ToString()
{

string format = "C0";
if (Value - Math.Truncate(Value) > 0)
{
format = "c2";
}
return ToString(format);
}
}

the money maker

Here’s how you would use that. Let’s say you’re displaying the cost of a product in an orderline. Since the ultimate goal of e-commerce it to turn the product value into cash, we can do that in code. Let’s convert the product cost to MyMoney.

1
2
string displayValue = new MyMoney(orderline.Total.GetValueOrDefault(), 
orderline.PurchaseOrder.BillingCurrency).ToString();