I am in the process of developing some extensions for the HTML Pro module to allow my users to 1) compare revisions of a module as they change, and 2) view how a page looked on a specific point in time with the ability to print and see all the modules on the page along with the one being viewed. 

The idea is that when a user opens the Edit Content panel on the HTML Pro module there will be a couple of additional sections.  One that allows them to compare different versions of the module and another that allows them to select from a dropdown list (based on the versions in the HTMLText table) to display how the page looked on a particular day including the other modules that were on the page at the time of the change.  Currently I am able to compare revisions and display the page and the changes to the selected module being viewed as intended.

So far, I have managed to make all my controls generate dynamically so all code is in a code-behind that can be added to a class later so I am simply able to add the class to the bin and make a couple of minor changes to the edit module and module.css under the HTML DesktopModules to make everything function as intended.

The issue I have is that I cannot locate anywhere in the existing tables to obtain the history of module changes, deletions, and additions so I can display the other modules as they were and where they were on the page at the specific point in time selected.  I can add a trigger to the HTMLText table and a couple of others to capture the changes as they occur and load them into additional tables so they can be recalled via new spored procedures, but I would prefer to avoid this if the information is already available somewhere in the existing table structure.

I am trying to keep everything as clean as possible so I can submit the changes to the core team once I have them functioning as intended.

Any assistance anyone can provider would be greatly appreciated.

Thanks,
Jeff

I can see where the versions are available for the updates to the module in the HtmlText table, but (at least in the HtmlText table I am working from) I can see modules listed in the TabModules table that are no longer in the HtmlText table so I was assuming that the missing modules were at one time added to the page, but have since been deleted from this table which means that I cannot pull those modules content while viewing historical pages. Also, looking at the StateID in the HtmlTextLog table it appears that these are being pulled from the WorkFlowStates , which does not include the Deleted items just the states from this table (unless there is somewhere else I can pull from).

I have started putting together some SQL in a Stored Procedure that could be used to pull the information from the HtmlText and TabModules tables based on the selected Module and DateTime chosen. This could either be done in a Trigger to load a snapshot table or real time if I am able to pickup everything that was on the page at the time chosen. Here is my code thus far (obviously I am capturing information I will not need to reproduce the page and it need further consideration priot to production, but this gives you an idea of what i am trying to accomplish):

Declare @ItemID int
SET @ItemID = 53
Select *
FROM [DNNDev6_JC].[dbo].[TabModules] A
LEFT OUTER JOIN [DNNDev6_JC].[dbo].[HtmlText] B
ON (A.ModuleID = B.ModuleID)
WHERE A.TabID IN (
SELECT TabID
FROM [DNNDev6_JC].[dbo].[TabModules]
WHERE ModuleID IN (
SELECT ModuleID
FROM [DNNDev6_JC].[dbo].[HtmlText]
WHERE ItemID = @ItemID
)
)
AND A.ModuleID IN (Select ModuleID FROM [DNNDev6_JC].[dbo].[HtmlText])
AND B.IsPublished = 1
AND B.ItemID IN (
Select MAX(ItemID) AS ItemID
From [DNNDev6_JC].[dbo].[HtmlText]
WHERE CreatedOnDate <= (Select LastModifiedOnDate from [DNNDev6_JC].[dbo].[HtmlText] WHERE ItemID = @ItemID)
AND IsPublished = 1
GROUP BY ModuleID
)

Any additional information you can provide concerning how I can reproduce the page would be greatly appreciated as well as any additional thoughts you have on this.

Thanks,
Jeff

Ok, if your interested, this seems to be returning the results I expect:

Declare @ItemID int
SET @ItemID = 54
Select GetDate() AS [ModifyDate], A.TabID, A.ModuleID, B.ItemID, A.PaneName, A.ModuleOrder
, B.Version, B.StateID, B.Content, B.LastModifiedOnDate
, A.DisplayTitle, A. DisplayPrint, A.IsDeleted, A.Moduletitle, A.Header, A.Footer
FROM [DNNDev6_JC].[dbo].[TabModules] A
LEFT OUTER JOIN [DNNDev6_JC].[dbo].[HtmlText] B
ON (A.ModuleID = B.ModuleID)
WHERE A.TabID IN (
SELECT TabID
FROM [DNNDev6_JC].[dbo].[TabModules]
WHERE ModuleID IN (
SELECT ModuleID
FROM [DNNDev6_JC].[dbo].[HtmlText]
WHERE ItemID = @ItemID
)
)
AND A.ModuleID IN (Select ModuleID FROM [DNNDev6_JC].[dbo].[HtmlText])
AND B.IsPublished = 1
AND B.ItemID IN (
Select MAX(ItemID) AS ItemID
From [DNNDev6_JC].[dbo].[HtmlText]
WHERE IsPublished = 1
AND CreatedOnDate <= (Select LastModifiedOnDate from [DNNDev6_JC].[dbo].[HtmlText] WHERE ItemID = @ItemID)
GROUP BY ModuleID
)