Custom Field Filling in WhosOn Database

I had a requirement from a customer to collect the order numbers from their website, but at their current state they were unable to use the normal method of modifying the tracking code to set the order details in WhosOn. Fortunately, the details of the order were stored inside the URL for the customer, on the thank you page thankyou.aspx as the parameter OrderID.

I wanted this to show up as a form fill variable so it can easily be used for querying the order number based on chatted, invited etc.

The route I went down for this was modification of the AddPageView procedure. This procedure finds the page from the page header information (or inserts it if this page hasn't been seen before) and inserts a view record for the visitor.

The code below could be applied to the WhosOn Server Database in order to AddPageView

ALTER PROCEDURE [dbo].[AddPageView]

@VisitID int,

@VisitorID int,

@SiteKey smallint,

@PageName varchar(250),

@PageDate datetime,

@PageNumber smallint,

@ContentGroup varchar(50),

@HTTPStatus smallint,

@HTTPSubStatus smallint,

@CachedPageHeaderID int,

@PageHeaderID int OUTPUT,

@LastModifiedDate datetime OUTPUT,

@LastCheckedDate datetime OUTPUT,

@TitleTag varchar(250) OUTPUT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @cgroup varchar(50)

DECLARE @orderref varchar(50)

IF CHARINDEX('thankyou.aspx',@PageName) > 0

BEGIN

DECLARE @ia INT;

DECLARE @iaa INT;

SET @ia = CHARINDEX('OrderID', @PageName);

SET @ia = CHARINDEX('=',@PageName, @ia);

SET @iaa = CHARINDEX('&',@PageName, @ia);

if @iaa = 0

BEGIN

SET @iaa = LEN(@PageName);

END

SET @orderref = SUBSTRING(@PageName,@ia+1, @iaa-@ia);

INSERT INTO dbo.VisitFormVars (VisitID, SiteKey, VarForm, VarName, VarData, VarTime)

VALUES (@VisitID, @SiteKey, 'Order', 'OrderNumber', @orderref, GETDATE())

SET @PageName = 'thankyou.aspx';

SET @CachedPageHeaderID = 0;

END

IF @CachedPageHeaderID = 0

BEGIN

SELECT @PageHeaderID = PageHeaderID,

@LastCheckedDate = LastChecked,

@LastModifiedDate = LastModified,

@TitleTag = TitleTag,

@cgroup = ContentGroup

FROM PageHeader

WHERE SiteKey = @SiteKey AND Page = @PageName

IF @PageHeaderID IS NULL

BEGIN

INSERT INTO PageHeader (

Page,

ContentGroup, LastModified,

LastChecked, SiteKey, TitleTag)

VALUES

(@PageName, @ContentGroup, '2000-01-01 00:00:00','2000-01-01 00:00:00', @SiteKey, '');

SET @LastCheckedDate = '2000-01-01 00:00:00';

SET @LastModifiedDate = '2000-01-01 00:00:00';

SET @TitleTag = '';

SET @PageHeaderID = SCOPE_IDENTITY()

END

ELSE

BEGIN

IF NOT @cgroup = @ContentGroup

UPDATE PageHeader SET ContentGroup = @ContentGroup

WHERE PageHeaderID = @PageHeaderID;

END

END

ELSE

SET @PageHeaderID = @CachedPageHeaderID

INSERT INTO PageViews

(VisitID, PageID, VisitorID, SiteKey, PageDate, PageNumber,

HTTPStatus, HTTPSubStatus)

VALUES

(@VisitID, @PageHeaderID, @VisitorID, @SiteKey,

@PageDate, @PageNumber, @HTTPStatus, @HTTPSubStatus);

END