This blog post is a continuity of the last two blog posts regarding extending the Episerver Coupon Codes Tool.
Episerver Coupon codes Tool: Download single-use coupons
Episerver Coupon codes Tool: Delete expired / unused coupons
Episerver foundation team has now added extended code to the Foundation starter website.
Now we have a tool to create bulk coupon codes and we have the ability to download these coupon codes in CSV and send them to an external system to use in email marketing.
There are times when customers click on an email link and comes to our website and we want to give the customer an incentive to make a purchase. We will record the customer’s visit and assign a unique time-limited coupon code and we can show this coupon code on-page as well to encourage the customer to buy.
For this example, the links in emails should have appropriate promotion Id (int) and customer Id (guide). You can modify it according to your need.
First of all, we need to create a Store procedure. This store procedure takes CustomerId and Promotion Id and assign Customer id to a unique coupon code and return this code to show on-page.
Store Procedure: UniqueCoupons_AssignCouponToCustomer
USE [epicommerce]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create PROCEDURE [dbo].[UniqueCoupons_AssignCouponToCustomer]
(
@CustomerId uniqueidentifier,
@Id BIGINT
)
AS
BEGIN
Update top(1) UniqueCoupons
Set CustomerId = @CustomerId
where
PromotionId = @Id
SELECT * FROM UniqueCoupons
WHERE CustomerId = @CustomerId and PromotionId = @Id
END
Now we need to update UniqueCouponService to call this store procedure. I have created a new method “AssignCouponToCustomer”
public UniqueCoupon AssignCouponToCustomer(int id, Guid customerid)
{
try
{
UniqueCoupon coupon = null;
var connectionString = [Connection String];
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand
{
Connection = connection,
CommandType = CommandType.StoredProcedure,
CommandText = "UniqueCoupons_AssignCouponToCustomer"
};
command.Parameters.Add(new SqlParameter("@CustomerId", customerid));
command.Parameters.Add(new SqlParameter("@Id", id));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
coupon = GetUniqueCoupon(reader);
}
}
}
return coupon;
}
catch (Exception exn)
{
_logger.Error(exn.Message, exn);
}
return null;
}
Dynamic Coupon Code Block
Now next step to utilize the UniqueCouponService method AssignCouponToCustomer and assign a unique coupon code to the customer records.
I have created a block with a Custom controller to implement this functionality because
- Block can show Coupon code on page and you can use it on any page with content area
- Block can be used with combination of visitor group personalization
- Blocks have their own controller / view. So separation of logic.
Below is a code snippet of my Dynamic Coupon Code Block controller & ViewModel
[TemplateDescriptor(Inherited = true,
TemplateTypeCategory = TemplateTypeCategories.MvcPartialController,
Default = true,
AvailableWithoutTag = true)]
public class DynamicCouponCodeBlockController : BlockController<DynamicCouponCodeBlock>
{
private readonly ICouponService _couponService;
public DynamicCouponCodeBlockController(ICouponService couponService)
{
_couponService = couponService;
}
public override ActionResult Index(DynamicCouponCodeBlock currentBlock)
{
var strPrmotionId = Request.QueryString["Id"] ?? string.Empty;
var strCustId = Request.QueryString["cId"] ?? string.Empty;
var promotionId = int.Parse(strPrmotionId);
Guid newGuid = Guid.Parse(strCustId);
var coupon = _couponService.AssignCouponToCustomer(promotionId, newGuid);
var viewModel = new DynamicCouponCodeBlockViewModel(currentBlock)
{
CouponCode = coupon
};
return PartialView("DynamicCouponCodeBlock", viewModel);
}
}
public class DynamicCouponCodeBlockViewModel : BlockViewModel<DynamicCouponCodeBlock>
{
public DynamicCouponCodeBlockViewModel(DynamicCouponCodeBlock currentBlock) : base(currentBlock)
{
}
public UniqueCoupon CouponCode { get; set; }
}