travislowdermilk.com

I have currently been tasked with the duty of providing a web part that displays patient satisfaction scores for the hospital. However, there was one stipulation:

“Can you make the web part reflect the unit that the nurse is working on?”.

In other words, could I create a web part that would observe the user’s login and display appropriate information based on where, in the hospital, that employee worked. In this case, the requester (ahem, my boss) wanted the scores to display for the nurse’s unit (think of a unit as a “wing” or “floor” in a hospital).

Provocative.

Right off the bat, I figured the best way to access the nurse’s unit was by pulling the “Department” property from our Active Directory schema.

But then I thought: “Why not build a function that would return any property I might want from Active Directory?” That way, from here on out, as I am building my web parts I could make decisions of the data to display based on a user’s department, job title, director, etc.

Essentially, creating web parts that are “user-aware”.

Good idea self!

Below is a namespace (ActiveDirectoryInfo) , class (UserInfo), and function (GetPropertyValue) that I created to do just this:

using Microsoft.Office.Server.Audience;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using System;

using System.Collections.Generic;

using System.DirectoryServices;

using System.Text;

using System.Web;

namespace ActiveDirectoryInfo

{

class UserInfo

{

public String GetPropertyValue(string PropKey)

{

//Get user’s Department from Active Directory

SPWeb spw = SPControl.GetContextWeb(HttpContext.Current); //Establishes the current SharePoint site.

SPUser spu = spw.CurrentUser; //Grabs user information from the logged in state of the user.

DirectoryEntry dentry = new DirectoryEntry(@”LDAP://DC=<hidden>,DC=<hidden>”, “<domain_user>”, “<domain_user_password>”);

DirectorySearcher dsearch = new DirectorySearcher(dentry);

String retUserName= “”;

dsearch.Filter = “(objectSid=” + spu.Sid + “)”; //Filters the search result based on the user’s SID.

//dsearch.Filter = “(sAMAccountName=” + spu.LoginName.Replace(@”Domain\”, “”) + “)”; //Filters the search result based on the user’s login (without Domain\).

dsearch.PropertiesToLoad.Add(PropKey); //Loads the specific property to be viewed.

SearchResult result = dsearch.FindOne(); //Peforms the search for one matching record.

if (null == result)

{

//Nothing

}

else

{

retUserName = result.Properties[PropKey][0].ToString();

}

return retUserName;

}

}

}

So here is how we might call upon this function within a web part:

protected override void Render(System.Web.UI.HtmlTextWriter writer)

{

UserInfo ui = new ActiveDirectoryInfo.UserInfo();

writer.Write(ui.GetPropertyValue(“cn”)); //Returns user’s full name

writer.Write(ui.GetPropertyValue(“mail”)); //Returns user’s e-mail

writer.Write(ui.GetPropertyValue(“Department”)); //Returns user’s Department

}

I found myself in a tricky situation the other day. In the hospital we have patient units. Think of them as floors or “wings” in a hospital. We also have sections in each one of the units. A section might be food, nurses, a room, or the discharge process. As a hospital, we survey our patients and have them rate the quality of a section in the unit they stayed in. Got me so far? Okay.

Well, I needed to create a report to reflect the managers that are responsible for a particular section in a particular unit. Rather than have each manager and the section they are accountable for listed in rows, I wanted to have one field in a column that represented a list of managers (separated by commas).

For example, something like this:

Unit	Section	Managers
North Wing	Meals	Martha Stewart, Steve Jobs, Paul Allen

In SQL Management Studio, I created a Scalar_valued Function and put the following code inside:

– Add the parameters for the function here

@unit nvarchar(50),

@section int

)

RETURNS nvarchar(500)

AS

BEGIN

– Declare the return variable here

DECLARE @Individuals CURSOR

DECLARE @Ac_Individuals nvarchar(50)

DECLARE @RV nvarchar(500)

– Add the T-SQL statements to compute the return value here

SET @Individuals = CURSOR FOR

SELECT Fname + ‘ ‘ + Lname AS Individuals

FROM PG_Individuals A INNER JOIN

PG_Accountable B ON A.IID = B.IID

WHERE A.Active = 1 AND UID = @unit AND SID = @section

OPEN @Individuals

WHILE @@FETCH_STATUS = 0

BEGIN

IF @RV IS NULL

BEGIN

SELECT @RV = @Ac_Individuals

END

ELSE

SELECT @RV = @Ac_Individuals + ‘, ‘ + @RV

FETCH NEXT FROM @Individuals INTO @Ac_Individuals

END

CLOSE @Individuals

DEALLOCATE @Individuals

–SELECT @Ac_Individuals = @unit

– Return the result of the function

RETURN @RV

@unit and @section are the parameters that are passed into the function.

@Individuals is a CURSOR that loops through a SELECT query that selects all the managers and joins them to the accountable table, thus pairing them up with their respective accountabilities. This query is filtered using the @unit and @section parameters.

I then begin to FETCH each individual record and store it into the @Ac_Individuals variable. Cycling through I add each entry from @Ac_Individuals into the @RV variable appending a comma.

Finally, I return the @RV variable that is representative of the comma separated list.

Now, I can use this function in the following way:

SELECT dbo.<name_of_my_function>(A.UID, A.SID) AS Accountability