This blog is the second part of Episerver Coupon codes Tool: Download single-use coupons. Let’s further extend the Coupon codes tool.
Once the website starts generating thousands or millions of coupon codes, it’s imminent that we need to create house-keeping functionality. We need functionality to delete expired voucher codes because we do not want the database to be flooded with expired coupon codes.
We also needs an ability to bulk delete existing coupon codes because of various reasons.
- Codes might get generated by mistake
- The campaign is no longer active
- Or we simply do not need these codes
Bulk Delete button
In the last blog, we created the Bulk operations section. We can add a new button in this session that will delete all existing coupon codes in the currently selected campaign discount.
Now UniqueCouponService already has a method DeleteByPromotionId. This method takes PromotionCode and delete all coupon codes for this promotion and invalidate the cache.
public bool DeleteByPromotionId(int id)
{
try
{
var connectionString = _connectionHandler.Commerce.ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var command = new SqlCommand
{
Connection = transaction.Connection,
Transaction = transaction,
CommandType = CommandType.StoredProcedure,
CommandText = "UniqueCoupons_DeleteByPromotionId"
};
command.Parameters.Add(new SqlParameter("@PromotionId", id));
command.ExecuteNonQuery();
transaction.Commit();
}
InvalidatePromotionCache(id);
}
return true;
}
catch (Exception exn)
{
_logger.Error(exn.Message, exn);
}
return false;
}for now, i’m going to use this method for delete.
The first step is to create Delete All button in EditPromotionCoupons.cshtml
The complete code of EditPromotionCoupons.cshtml will be as follow
@using EPiServer.Shell
@inherits WebViewPage<Foundation.Commerce.Marketing.PromotionCouponsViewModel>
<head>
<title>Edit promotion coupons</title>
</head>
<main class="dash-content">
<div class="container-fluid">
<div class="row">
<div class="col-12">
<h3>Manage Coupon Codes for Promotion @Model.Promotion.Name</h3>
<div class="card spur-card">
<div class="card-header">
<div class="spur-card-icon">
<i data-feather="gift"></i>
</div>
<div class="spur-card-title">Generate coupons</div>
</div>
<div class="card-body">
@using (Html.BeginForm("Generate", "SingleUseCoupon", FormMethod.Post, new { @class = "form-horizontal" }))
{
@Html.AntiForgeryToken()
@Html.HiddenFor(x => x.PromotionId)
<div class="form-row">
<div class="form-group col-md-6 col-xl-3">
@Html.LabelFor(x => x.ValidFrom)
@Html.TextBoxFor(x => x.ValidFrom, new { @class = "form-control", @type = "date" })
</div>
<div class="form-group col-md-6 col-xl-3">
@Html.LabelFor(x => x.Expiration)
@Html.TextBoxFor(x => x.Expiration, new { @class = "form-control", @type = "date" })
</div>
<div class="form-group col-md-6 col-xl-3">
@Html.LabelFor(x => x.Quantity)
@Html.TextBoxFor(x => x.Quantity, new { @class = "form-control", @type = "number" })
</div>
<div class="form-group col-md-6 col-xl-3">
@Html.LabelFor(x => x.MaxRedemptions)
@Html.TextBoxFor(x => x.MaxRedemptions, new { @class = "form-control", @type = "number" })
</div>
</div>
<button type="submit" class="btn btn-primary">Generate</button>
}
<br />
<div class="row">
<div class="col-12">
<h3>Bulk operations</h3>
<div class="col-md-6 col-xl-3">
@using (Html.BeginForm("Download", "SingleUseCoupon", FormMethod.Post, new { @class = "form-horizontal" }))
{
@Html.AntiForgeryToken()
@Html.HiddenFor(x => x.PromotionId)
<button type="submit" class="btn btn-primary">Download</button>
}
</div>
<div class="col-md-6 col-xl-3">
@using (Html.BeginForm("DeleteAll", "SingleUseCoupon", FormMethod.Post, new { @class = "form-horizontal" }))
{
@Html.AntiForgeryToken()
@Html.HiddenFor(x => x.PromotionId)
<button type="submit" class="btn btn-primary">Delete All</button>
}
</div>
</div>
</div>
<div class="mvc-grid">
@using (Html.BeginForm("UpdateOrDeleteCoupon", "SingleUseCoupon", FormMethod.Post, new { @class = "jsCouponUpdateForm" }))
{
@Html.AntiForgeryToken()
<table class="gift-cards-table">
<thead>
<tr>
<th>Code</th>
<th>Created</th>
<th>Valid From</th>
<th>Expiration</th>
<th>Max Redemptions</th>
<th>Used Redemptions</th>
<th>Actions</th>
</tr>
</thead>
<tbody class="js-users-table-body">
@for (var i = 0; i < Model.Coupons.Count; i++)
{
<tr>
<td>
@Html.HiddenFor(x => Model.Coupons[i].Id)
@Html.HiddenFor(x => Model.Coupons[i].PromotionId)
@Html.TextBoxFor(x => Model.Coupons[i].Code, new { @class = "form-control" })
</td>
<td>
@Model.Coupons[i].Created
</td>
<td>
@Html.TextBoxFor(x => Model.Coupons[i].ValidFrom, "{0:yyyy-MM-dd}", new { @class = "form-control", @type = "date" })
</td>
<td>
@Html.TextBoxFor(x => Model.Coupons[i].Expiration, "{0:yyyy-MM-dd}", new { @class = "form-control", @type = "date" })
</td>
<td>
@Html.TextBoxFor(x => Model.Coupons[i].MaxRedemptions, new { @class = "form-control", @type = "number" })
</td>
<td>
@Html.TextBoxFor(x => Model.Coupons[i].UsedRedemptions, new { @class = "form-control", @type = "number" })
</td>
<td style="text-align: right">
<i class="fa fa-save jsUpdateCoupon" style="cursor: pointer;" />
<i class="fa fa-trash-alt jsDeleteCoupon" style="cursor: pointer; color: red"></i>
</td>
</tr>}
</tbody>
</table>}
</div>
</div>
</div>
</div>
</div>
</div>
</main>
<div style="position: fixed; top: 75px; right: 75px; display: none;" class="coupon-status alert alert-info" role="alert">
Updating, please wait ...
</div>
<div style="position: fixed; top: 75px; right: 75px; display: none;" class="coupon-alert alert" role="alert">
</div>
@section AdditionalScripts {
<script type="text/javascript" src="@Paths.ToClientResource("Foundation.Commerce", "ClientResources/js/Coupons.js")"></script>
<script type="text/javascript">
$(document).ready(function () {
Coupons.init();
});
</script>
}Now we need to add ActionResult in SingleUseCouponController.cs controller. The process will be similar as i have described in previous blog
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult DeleteAll(PromotionCouponsViewModel model)
{
var deleted = _couponService.DeleteByPromotionId(model.PromotionId);
return RedirectToAction("EditPromotionCoupons", new { id = model.PromotionId });
}The complete manage token will look like below
Delete Expired coupon codes job
Once we started generating a lot of coupon codes we need a background job to clean up the mess and keep the house in order.
We will write a Episerver Schedule Job that runs once a day and simply delete all expired coupon codes from database.
First of all, Let’s write a new store procedure “UniqueCoupons_DeleteExpiredCoupons” in Commerce database
USE [Epi.Foundation.Commerce]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
Create PROCEDURE [dbo].[UniqueCoupons_DeleteExpiredCoupons]
AS
BEGIN
DELETE FROM UniqueCoupons
WHERE Expiration < GETDATE()
END
The next step is to create a new method in Unique Coupon Service. The schedule job calls the Delete Expired Coupons method to delete all expired coupon codes.
public bool DeleteExpiredCoupons()
{
try
{
var connectionString = _connectionHandler.Commerce.ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var command = new SqlCommand
{
Connection = transaction.Connection,
Transaction = transaction,
CommandType = CommandType.StoredProcedure,
CommandText = "UniqueCoupons_DeleteExpiredCoupons"
};
command.ExecuteNonQuery();
transaction.Commit();
}
}
return true;
}
catch (Exception exn)
{
_logger.Error(exn.Message, exn);
}
return false;
}Now let’s create a schedule job.
using EPiServer.PlugIn;
using EPiServer.Scheduler;
using Foundation.Commerce.Marketing;
namespace Foundation.Infrastructure.Jobs
{
[ScheduledPlugIn(DisplayName = "Delete expired coupon job.", GUID = "E665179B-E188-4D1B-919A-183EC5456309")]
public class DeleteExpiredCouponJob : ScheduledJobBase
{
private bool _stopSignaled;
private readonly UniqueCouponService _couponService;
public DeleteExpiredCouponJob(UniqueCouponService couponService)
{
IsStoppable = true;
_couponService = couponService;
}
public override void Stop() => _stopSignaled = true;
public override string Execute()
{
OnStatusChanged(string.Format("Starting execution of {0}", GetType()));
var result =_couponService.DeleteExpiredCoupons();
if (_stopSignaled)
{
return "Stop of job was called";
}
if (result)
{
return "Job runs sucessfully";
}
else
{
return "There is problem with job execution";
}
}
}
}