Admin's Friend: Javascript

Not so much SQL this time, but still super useful for an admin: Add this code to the Welcome Message section of your Login Logout module when using the Arena Basic template to add two useful links at the top: Edit This Page (takes you to the Pages view for the current page) and Refresh Cache (...exactly that).

<script type="text/javascript">
function RefreshCache() {
var rr = document.location.href

if (rr.indexOf("?")>0)
window.location = rr + "&refreshcache=true";
else
window.location = rr + "?refreshcache=true";
}
function getQueryVariable(variable) {
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var i=0;i<vars.length;i++) {
var pair = vars[i].split("=");
if (pair[0] == variable) {
return pair[1];
}
}
return 3;
}
function EditPage() {
window.location = "default.aspx?page=34&pageid=" + getQueryVariable("page");
}
</script>
<b><a href="javascript:EditPage()">|Edit This Page|</a>&nbsp;&nbsp;&nbsp;<a href="javascript:RefreshCache()">|Refresh Cache|</a></b>&nbsp;&nbsp;&nbsp;Welcome ##nickname## ##lastname##.

Staff Birthdays

The goal: show all staff members whose birth days are either within the last week (previous 7 days) or in the coming month (next 30 days), with past birthdates slightly greyed out. The mechanism: HTML From Stored Procedure. The result:

CREATE PROC dbo.cust_sp_staff_birthdays_html
AS

DECLARE @datatable TABLE (nick_name VARCHAR(50),last_name VARCHAR(50),normalized_birth_date DATETIME,birth_day VARCHAR(50),person_guid UNIQUEIDENTIFIER)

INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE()),DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE()),DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

IF (MONTH(GETDATE())=12)
INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE())+1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE())+1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

IF (MONTH(GETDATE())=1)
INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE())-1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE())-1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

SELECT ISNULL(REPLACE(REPLACE(
'<style type="text/css">
a.pastdate{color:#888888;font-family: Verdana, Arial, Helvetica, sans-serif;font-size: 10px;text-decoration: none;}
a.foredate{color:#000000;font-family: Verdana, Arial, Helvetica, sans-serif;font-size: 10px;text-decoration: none;}
</style>
<table cellspacing="2" cellpadding="0" class="smallText">'+
'<tr><td colspan="2"><h2>Staff Birthdays</h2></td></tr>'+
(SELECT
'<tr >
<td width="160"><a href="default.aspx?page=7&guid='+ISNULL(CONVERT(VARCHAR(50),person_guid),'')+'" style="text-decoration:none;" class="'+(CASE WHEN normalized_birth_date < DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0) THEN 'pastdate' ELSE 'foredate' END)+'" >'+ISNULL(nick_name,'')+' '+ISNULL(last_name,'')+'</a></td>
<td width="30"><a href="default.aspx?page=7&guid='+ISNULL(CONVERT(VARCHAR(50),person_guid),'')+'" style="text-decoration:none;" class="'+(CASE WHEN normalized_birth_date < DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0) THEN 'pastdate' ELSE 'foredate' END)+'" >'+ISNULL(birth_day,'')+'</a></td>
</tr>'
FROM @datatable
ORDER BY normalized_birth_date, last_name,nick_name
FOR XML PATH(''))
+'</table>'
,'&lt;','<'),'&gt;','>'),'') AS [html]