Page Retirement Methods

Continuing my effort to document the various ways I've used the ODBC connector for the NetQoS products, here's my next query and controls I've built and that I use in production.  Today's query comes from a need to notify users that a particular page is being retired. Instead of immediately deleting a page and having to deal with confused users, I used the browser view to display a banner telling the users that they have X days until the page is retired.  This could be done manually by creating a different static HTML page display a different date for each page, but I wanted a way to make it easy.
First of all, I didn't want to have to rebuilt the banner for each page that is going to be retired.  So, I needed a way that each instance of the banner have a way to store an individual expiration date.  I also wanted a way for users who don't want their precious pages deleted to notify me.  A simple email would have done, but that would have almost always involved an immediate ping back to the user asking which page.  So, the banner needs a way to start an email and automatically put the URL to the page.
The implementation is pretty easy.

1. First of all, get this first section of code, update 'email@company.com' to your email address on the fourth to last line and save it as pageexpiring.html in your custom content directory.  Download a copy of the code here.
2. Then put a browser view on the page to be retired and set the URL to point to pageexpiring.html in your custom content directory and save the view.  When the page refreshes, you should see a message saying that the page has been scheduled to be retired on an unknown date.  
3. To add a specific date, edit the browser view and append ?expirationdate=3/31/2013 to the URL (replacing 3/31/2013 with the page retirement date).
4. Repeat steps 2 and 3 for any remaining pages to be retired.

<center> <h1 style="color:red;font-size:50px;"> ATTENTION: This Page Is Scheduled for Retirement</h1> <table width=50%> <tr><td> <p id="explanationtext" style="font-size:40px;font-weight:bold;"> </p> <script> var expirationdate = 'an unknown date'; var iframeurl=location.search; iframeurl=iframeurl.replace('?',''); var iframeargsarray=iframeurl.split('&'); var iframeargsmatrix = new Array(iframeargsarray.length); for (var i = 0; i < iframeargsarray.length; i++){iframeargsmatrix[i] = new Array(2);iframeargsmatrix[i] = iframeargsarray[i].split('=');} for (var j=0; j < iframeargsmatrix.length; j++){if(iframeargsmatrix[j][0] == 'expirationdate'){expirationdate = iframeargsmatrix[j][1];break;}} document.getElementById('explanationtext').innerHTML='This page has been scheduled to be retired on ' + expirationdate + '.' </script> <p> From that time forward, the page will be unavailable for reporting. If you would like to prevent the retirement of this page, send an email to <a id="pageexpiringemailer"></a> requesting that this page not be retired. This may require moving the page to a different menu within NPC.</p> </td></tr> </table> <script> var parenturl=parent.document.location; var targetemail='email@company.com'; document.getElementById('pageexpiringemailer').href='mailto:' + targetemail + '?subject=Postpone%20NPC%20Page%20Retirement&body=I%20need%20this%20page%2C%20so%20please%20don\'t%20retire%20it%20yet: ' + parenturl; document.getElementById('pageexpiringemailer').innerHTML=targetemail; </script></center>

Now that you've marked a couple pages to be retired, you can use an ODBC connection to display all those pages.  Here's the SelectCommand and OdbcConnection String to put in the configuration.xml:
<selectcommand Name="PageExpiration" Property="False" SupportedViewTypes="Table"> <![CDATA[ select substring(a.propertyvalue,instr(a.propertyvalue,'=')+1) as ExpirationDate , convert(concat('<a href=\'default.aspx?pg=',b.pageid,'\'>',b.pagetitle,'</a>') using utf8) as pagelink , b.menutitle , b.pagetitle from control_properties as a, pageinfo as b where a.controlid=64 and a.propertyname='browserurl' and a.propertyvalue like '/content/PageExpirationBanner%' and a.pageid=b.pageid; ]]> </SelectCommand> <odbcconnectionstring Name="NPC">Driver={MySQL ODBC 5.1 Driver};Server=192.168.10.10;Port=3308;Option=3;Database=netqosportal;User=netqos;Password=netqos;</OdbcConnectionString>

To create the view, run the following SQL commands against the NPC server:
INSERT INTO controls SELECT if(min(id)-1>0,min(id)-1,29999), 'Expiring NPC Pages', 'Graph', 'NPC-ODBC', '/nqWidgets/Integration/wicTable.ascx', 1, 0x200000, 0, 0, 0, 1, '|nc|g|', '', 'Y' FROM controls WHERE datasourcetype=0x200000; INSERT INTO control_properties (ControlID, Editable, PropertyName, PropertyValue) VALUES (-1, 'N', 'Title', 'Expiring NPC Pages'), (-1, 'N', 'DisplayColumns', 'pagelink;ExpirationDate'), (-1, 'N', 'DisplayFormats', 'string,string'), (-1, 'N', 'DisplayNames', 'Page,Expiration Date'), (-1, 'N', 'ColumnWidths', ','), (-1, 'N', 'HorizontalAlignments', 'Left,Left'), (-1, 'N', 'SortField', 'ExpirationDate'), (-1, 'N', 'SortDirection', 'Asc'), (-1, 'N', 'Integration.Parameter.SelectCommandName', 'PageExpiration'), (-1, 'N', 'Integration.DataType', 'SelectCommand'), (-1, 'N', 'Integration.Parameter.DateTimeFormat', 'yyyy-MM-dd HH:mm'), (-1, 'N', 'Integration.Parameter.TopN', '200'), (-1, 'N', 'Integration.Parameter.OdbcConnectionStringName','NPC'); UPDATE control_properties SET ControlID = (select min(id) from controls where datasourcetype=0x200000) WHERE ControlID = -1;