TimeTracker

This intranet application is used to keep track of time spent by project members on different projects. It stores details regarding users, projects and time spent by members on projects - time entries.

This application uses ASP.NET pages for presentation. ObjectDataSource is used to get data from Business Logic Layer (BLL), which in turn access Data Access Layer (DAL). Stored procedures are used to perform all important operations related to database in SQL Server.

The following are major activities in this application

Technologies and Products Used

Steps to download, deploy and run this project

The following are the steps to related to be taken to run the existing part of the application. This project makes use of membership feature of ASP.NET. So,we have to configure the website using ASP.NET Configuration tool as explained below.
  1. Download timetracker.rar and unzip it into any directory in your system. For example, if you extract to c:\ then it will create a directory c:\timetracker. The download contains all ASP.NET pages but it has NO DATABASE. We have to create database objects using ASP.NET configuration tool manually.
  2. Open Visual Studio.NET 2008 or Visual Web Developer 2008.
  3. Open the project from the directory into which you extracted project.For example, c:\timetracker
  4. Select Website->ASP.NET Configuration option
  5. Select Security tab
  6. Select Use the security Setup Wizard to configure security step by step.
  7. Select From Internet option in Step 2
  8. Click on Next button in the remaining screens and finally click on Finish.
  9. It create a database called ASPNETDB.MDF with required tables and other database components
  10. Open the database in Server explorer or Database Explorer and create tables - PROJECTS, PROJECT_MEMBERS and TIMEENTRY. The structure for these tables is shown below. PROJECT and PROJECT_MEMBERS tables refer to USERID column of ASPNET_USERS table, which is created by Configuration Tool.

    PROJECTS Table

    Column Name Data Type Remarks
    id int Identify Column
    title varchar(50)  
    description varchar(1000)  
    createdon datetime  
    creatorid uniqueidentifier References USERID column in ASPNET_USERS table
    estduration int  
    manager uniqueidentifier References USERID column in ASPNET_USERS table

    PROJECT_MEMEBRS table

    Column Name Data Type Remarks
    projectid int References ID column in PROJECTS table
    userid uniqueidentifier References USERID column in ASPNET_USERS table

    Note: PROJECTID and USERID together make up composite primary key for PROJECT_MEMBERS table.

    TIMEENTRY table

    Column Name Data Type Remarks
    entryid int Identify Column
    projectid int  
    userid uniqueidentifier  
    entrydate datetime  
    createdon datetime  
    duration int  
    description varchar(1000)  

    Note: PROJECTID and USERSID reference PROJECTID and USERID of PROJECT_MEMEBRS table. It is a composite foreign key.

  11. Create the following stored procedure in the database.
    CREATE PROCEDURE dbo.CreateProject(@title  varchar(50),
    @description varchar(1000),
    @creatorid uniqueidentifier,
    @estduration int,
    @managerid uniqueidentifier)
    AS
        insert into projects values(@title,
        @description,getdate(), @creatorid,@estduration,
        @managerid)
    

    CREATE PROCEDURE dbo.GetAllProjects
    AS
    select * from projects order by id;    
    

    CREATE PROCEDURE dbo.GetAllUsers
    AS
    select  u.userid, u.username, m.email, m.createdate
    from   aspnet_users  u  join  aspnet_membership m
    on ( u.userid = m.userid)
    order by u.username
    

    CREATE PROCEDURE dbo.GetNonMembersOfProject(@projectid int)
    AS
    
     select userid, username from aspnet_users
     where  userid not in (
     select userid from project_members
     where projectid = @projectid)
     
    

    CREATE PROCEDURE dbo.GetProjectDetails
    (@projectid int)
    AS
      select p.id, p.title, p.description, p.createdon,
      p.estduration,  u.username
      from projects p  inner join  aspnet_users u
      on (p.managerid = u.userid) 
      where  p.id = @projectid       
    

    CREATE PROCEDURE dbo.GetProjectMembers(@projectid int)
    AS
    	select userid, username
    	from aspnet_users
    	where userid  in 
    	  (select userid from  project_members
    	   where projectid = @projectid);
    

    CREATE PROCEDURE dbo.GetTimeEntriesByProject
    	(@projectid int)
    AS
       select  t.entryid, t.entrydate, t.duration, t.description, t.createdon, u.username 
       from  timeentry t inner join aspnet_users u
       on ( t.userid = u.userid)
       where  t.projectid  = @projectid
    
    

    CREATE PROCEDURE dbo.AddMemberToProject(@projectid int, @userid uniqueidentifier)
    AS
       insert into project_members values(@projectid, @userid);
       
    

    CREATE PROCEDURE dbo.AddTimeEntry(@projectid int,@userid uniqueidentifier,@entrydate datetime,@duration int,@description varchar(1000))
    AS
      insert into timeentry values(@projectid,@userid,
      @entrydate, getdate(), @duration,@description)
       
    
  12. Goto Solution Explorer and make login.aspx the startup page.
  13. Run project from Visual Studio.NET 2008 or Visual Web Developer 2008.
  14. You should see login.aspx page.