Timesheets with TFS Continued…

So what were those screen shots?

Lets look at them again.

Screen shot is for TFS 2010, and what is that link saying manage timesheet? and I don’t get the reports tab!

Ok, Lets just talk about Timesheet tab, I will write about reports later. Its one more plugin. I will talk about the problem and solution that tab is catering to in next post.

Back to timesheet.

I wouldn’t want do discuss the steps to make it 2010 compatible, overall this requires some fixes in the master pages, content pages and javascript object model to make it to work. 

For the plug-in to work with multiple collections and projects, I created a table for collections tblCollections. And one more table for Projects. Here are some of the columns

• ID -  called projectcollectionid as this is the combination of project id and collection id.
• Project Name – Same as the TFS Project Name
• Project Manager -  TFS project can have multiple admins, but in most of the cases there is a single manager who manages the tasks and can approve the timesheets.
• Team Size – just a metadata information
• CollectionId – so that you could map with collections
• PGUID – project GUID that TFS maintains, can be handy in reports.

The link you see the screenshot above is admin link available only for super administrator who activates the timesheet feature for a project. This is the time when you capture most of the project metadata information. This is how this new screen looks.

I have used entity framework(.Net 4.0) as my ORM Data access tier. So my collection id determination looks like

public static int GetProjectCollectionId(string ProjectName, string collectionName)
     {
         using (TfsTimesheetEntities ctx = new TfsTimesheetEntities())
         {
             var project = ctx.Projects.Where(x => x.Collection.CollectionName == collectionName && x.ProjectName == ProjectName).FirstOrDefault();
             if (project != null)
             {
                 return project.Id;
             }
         }

         return 0;
     }

Similarly, using the project manager id we could determine if the logged in user is Project admin using following code

public static bool IsProjectAdmin(string logon, int projectCollectionId)
        {
            if (IsUserAdministrator(logon))
                return true;

            using(TfsTimesheetEntities ctx = new TfsTimesheetEntities())
            {
                var admin = ctx.Projects.Where(x => x.Id == projectCollectionId).FirstOrDefault();
                if (admin != null)
                {
                    if (String.Compare(logon, admin.ManagerId.Trim(), true) ==0)
                    {
                        return true;
                    }
                }
            }

            return false;
        }

While rendering the timesheet plugin, to determine if we have to enable any of the admin features, you call the above method to see if user is a project manager for this project.

            if (TimeSheetHelper.IsProjectAdmin(Request.LogonUserIdentity.Name, this.ProjectCollectionId))
            {
                toolbar.Items.Add(new Microsoft.TeamFoundation.WebAccess.Controls.MenuItem(true));
                toolbar.Items.Add(new Microsoft.TeamFoundation.WebAccess.Controls.MenuItem("Admin", "admin", ResolveUrl("~/Resources/Images/admin.gif")));
                toolbar.Items[toolbar.Items.Count - 1].Action = "parent.location='Admin.aspx?pguid=" + this.Locator.ProjectUri.Segments[3]+"'";
            }

Now, bigger part of the work remains is in the database side.

• First you will have to include the project collection id in all calls. So that reference to all entries are related to project collection ids and not to project ids.



• Next, you will have to pass the collection name in all the SPs that makes a cross DB joins to connect to appropriate database. For example…

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS(SELECT * 
            FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[usp_RS_AwaitingApproval]') 
                  AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[usp_RS_AwaitingApproval]
GO
CREATE PROCEDURE [dbo].[usp_RS_AwaitingApproval]
     @collectionName nvarchar(100),
    @projectCollectionId int
AS
BEGIN
    SET NOCOUNT ON
        
    DECLARE @statement nvarchar(1000) = N'SELECT tc.sid, DisplayPart AS displayName, weekStarting
    FROM TimesheetCompletions tc WITH(NOLOCK)
    JOIN TFS_'+ @collectionName +'.dbo.Constants WITH(NOLOCK)
    ON tc.sid = Constants.sid
    WHERE completed = 1 AND 
    tc.ProjectCollectionId ='+ CAST(@projectCollectionId as nvarchar(8)) +' AND
    approved IS NULL'

    EXEC(@statement)

END
GO

Above snippet connects to TFS_<CollectionName> db to get he usernames.

This must have given you an idea on how the plugin can be extended to serve multiple collections. Next part you will be interested in is getting report of who has filled timesheet in your organization. I used SSRS report, following is the query that I used to get the result.

SELECT prj.id, 
        MAX( [ProjectName]) AS ProjectName,
        MAX(ManagerName) AS ManagerName, 
        MAX(WeekStarting) AS WeekStarting, 
        dbo.TFSGetUserName(tc.sid) as DisplayPart, -- this is function i created to get the username 
                                                   -- refer to the constants table easily. 
        Completed as EnteredTimeSheet,
        (approved )as Closed,
        MAX(prj.TeamSize) as TeamSize,
        SUM(te.hours) as HoursLogged
        FROM timesheetcompletions tc
JOIN Projects prj ON tc.projectCollectionId=prj.id
JOIN TimesheetEntries te on tc.sid = te.sid and tc.ProjectCollectionId = te.ProjectCollectionId
AND (te.entryDate > tc.weekStarting AND te.entryDate < DATEADD(DAY,7, tc.weekStarting ) )
where DATEADD(dd, 0, DATEDIFF(dd, 0, WeekStarting)) =DATEADD(dd, 0, DATEDIFF(dd, 0, @Startdate))
GROUP BY prj.id
, tc.sid

Report would look something like…

There is another interesting report involved, that is to figure out which contributor hasn’t filed his/her timesheet. Which is bit complex report compared to this one, I will write about it some other time.