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
