HowTo: Set up a Private Download Area with Logging

Just recently, a client asked me to setup a download area with selected documents, where users should have to provide name and a few other information, being able to download the files.

As he already uses DNN Form and List module a lot and asked for a kind of Master – Detail design, I choose using this module, I created a new instance on the page, and adjusted module settings to inclue the following fields:

• Title (Text)
• Description (RichText)
• DownloadFile (download file)
• Created At and By as well as Last Updated At and By columns are provided by FnL module automatically.

I applied the appropriate custom views and granted access to “All Users”

I also created a folder of type “Secure (File System")” for uploading the pictures to, which has view permission granted to “Registered Users” role only.

This way, users, who click the Download link, have to login first – I added appropriate text on the (custom) download page. I Admin > Site Settings, I set up a proper registration form, including all the information required: Email, Password, PasswordConfirm, First Name, LastName and Company (all set to be required). DisplayName is defined to be created by “[FirstName] [LastName] and Email is used for login.
Now users may click on the download link, get presented a login dialog (if not already logged in) with a register option, which is explained in a text module on the same page.

This configuration was accepted by the client, however, he wants to be able to see, who downloaded which document. Now, Form and List module does provide the download datatype, which has the option of recording clicks, but due to the lack of UI (especially for multiple download columns), we skipped to implement this option, when we added this data type a few years ago. Now, when using LinkClick.aspx (as FnL download datatype does), DNN has the option to track the number of clicks and even record each click in the database. it uses table UrlTracking, where for a Module and URL, Track and Log options are stored as well as in table UrlLogging, where each download is tracked, if tracking is unabled.

DNN FnL module does create a record in UrlTracking for each URL, however, tracking and logging are turned off. To turn it on automatically. I had to find out the moduleID first. it is usually displayed in URL, when entering settings. FnL stores all data serialized in table UserDefinedData (the name is caused by historical reasons). Each value stored is identified by rowId and fieldID. To get the fieldID, I need to query the database, using my moduleID (456 in this case), in Host > SQL:

SELECT UserDefinedFieldId, FieldTitle, ModuleID FROM UserDefinedFields WHERE ModuleID = 456

This lists all column definitions for the module, including title. I noted down the UserDefinedFieldId for my download column (4007 in my case). Now I was able to place a trigger on the table, where data is stored. A table trigger in SQL Server is a piece of SQL code, which is executed, whenever a row is inserted, deleted and/or updated. To create the trigger, I ran the following SQL script:

CREATE TRIGGER [dbo].[UDT_LogDownloads] 
   ON  [dbo].[UserDefinedData]
   AFTER INSERT, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	MERGE INTO UrlTracking T USING (SELECT FieldValue FROM UserDefinedData WHERE UserDefinedFieldID = 4007) U ON T.URL = U.FieldValue
	WHEN MATCHED AND (LogActivity = 0 OR TrackClicks = 0) THEN UPDATE SET LogActivity = 1, TrackClicks = 1
	WHEN NOT MATCHED THEN INSERT (PortalID, URL, UrlType, LogActivity, TrackClicks, ModuleID) VALUES (0, U.FieldValue, 'F', 1, 1, 5930);
END

Now, all download clicks are logged, but there is no UI, to display the download list. For this purpose, I used DNN Reports module.
I placed an instance on the page and restricted visibility to admins only. The users wants a list including Download File name, user email, user displayName and user company
Custom properties like Company are stored in UserProfile table, using PropertyDefinitionID , which I had to lookup in PropertyDefinition table by running the following SQL statement:

SELECT * FROM ProfilePropertyDefinition WHERE ModuleID = 456 AND PropertyName = ‘Company’. 

Using the information of PropertyDefinition = 43, I used the following statement  in report settings, to display all downloads within the past year (sorted by date descending):

SELECT F.FileName, L.ClickDate, U.DisplayName, U.Email, P.Company

FROM UrlTracking T

JOIN Files       F ON SubString(T.Url, 8, 11) = Convert(nVarChar(11), F.FileID)
JOIN UrlLog      L ON T.UrlTrackingID = L.UrlTrackingID

JOIN Users       U ON L.UserID = U.UserID
LEFT JOIN (SELECT UserID, PropertyValue AS Company FROM UserProfile WHERE PropertyDefinitionID = 43) P ON U.UserID = P.UserID

WHERE L.ClickDate > DateAdd(YY, -1, GetDate())

ORDER BY L.ClickDate DESC;

Now my client is happy with his download section and list of downloads.

Q: why don’t you use DNN Documents module?
A: the module is easy, if you are happy with the columns provided and the layout. I needed to implement a specific design for the client, to integrate with his CD (Corporate Design).

Q: Am I able to send an email, whenever the download link is clicked
A: yes, but for doing this without coding, you need to configure SQL Server to send emails and creae an insert trigger for your UrlLog table.