createdbobjects.sql

Time Tracker Starter Kit 使您能轻松创建这样一个应用程序

	)
	
	SELECT
	    @@Identity AS UserID    
  END
ELSE
	SELECT -2 AS UserID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE TT_DeleteProject
(
	@ProjectID int
)
AS

DELETE FROM 
	TT_Projects 

WHERE 
	ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE PROCEDURE TT_DeleteTimeEntry
(
	@EntryLogID int
)
AS

DELETE FROM 
	TT_EntryLog
	
WHERE 
	EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE  PROCEDURE TT_DeleteUser
(
	@UserID int
)
AS

DELETE FROM 
	TT_Users
	
WHERE 
	UserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE PROCEDURE TT_GetManagerProjectCount
	@UserID int 
AS
	SELECT COUNT(*) FROM TT_Projects WHERE ManagerUserID = @UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE  PROCEDURE TT_ListMembers
(
	@ProjectID int
)
AS

SELECT 
	u.UserID, UserName
	
FROM 
	TT_ProjectMembers pm
INNER JOIN
	TT_Users u
On 
	u.UserID = pm.UserID
	
WHERE 
	ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO








CREATE    PROCEDURE TT_ListCategoriesByProject
(
    @ProjectID int
)
AS

    SELECT C.CategoryID,
	    C.Name,
            C.Abbreviation AS CategoryShortName,
            EstDuration = CAST(Round(C.EstDuration,0) AS int),
            ISNULL(Sum(EL.Duration), 0) AS ActualHours
    FROM TT_Categories C 
        LEFT OUTER JOIN TT_EntryLog EL
            ON C.CategoryID = EL.CategoryID
    WHERE C.ProjectID = @ProjectID
    GROUP BY C.CategoryID, C.Name, C.Abbreviation, C.EstDuration
    
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE   PROCEDURE TT_GetProject
(
	@ProjectID int
)
AS

SELECT 
	Name, Description, ManagerUserID, EstCompletionDate, EstDuration = CAST(Round(EstDuration,0) AS int)

FROM 
	TT_Projects

WHERE 
	ProjectID = @ProjectID

exec TT_ListMembers @ProjectID
exec TT_ListCategoriesByProject @ProjectID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE  PROCEDURE TT_GetTimeEntry
(
    @EntryLogID int
)
AS

SELECT 
    EL.Description, 
    EL.Duration, 
    EL.EntryDate, 
    EL.ProjectID, 
    EL.UserID, 
    EL.CategoryID, 
    P.Name AS ProjectName

FROM 
    TT_EntryLog EL
        INNER JOIN TT_Projects P
            ON EL.ProjectID = P.ProjectID
    
WHERE 
    EL.EntryLogID = @EntryLogID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE  PROCEDURE TT_GetUserByUserName
(
	@UserName nvarchar(50)
)

AS
	
SELECT 
	UserID, 
	UserName, 
	Password,
-- 	LastName, 
-- 	Email, 
-- 	Telephone, 
	RoleID

FROM 
	TT_Users WHERE UserName = @UserName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create proc TT_GetUserDisplayName
	@Username nvarchar(50)
AS
	SELECT DisplayName FROM TT_Users
	WHERE UserName = @Username

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE  PROCEDURE TT_ListAllProjects

AS

	SELECT ProjectID, 
		Name as ProjectName, 
		Description, 
		ManagerUserID, 
		EstCompletionDate, 
		EstDuration
	FROM TT_Projects
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE TT_ListAllRoles

AS

SELECT 
	RoleID, 
 	Name
FROM 
	TT_Roles
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE PROCEDURE TT_ListCategories
(
	@ProjectID int
)
AS

SELECT 
	CategoryID, Name, Abbreviation, CAST(EstDuration AS int) EstDuration
	
FROM 
	TT_Categories
	
WHERE 
	ProjectID = @ProjectID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO










CREATE    PROCEDURE TT_ListManagers

AS

SELECT 
	UserID, UserName, RoleID
	
FROM 
	TT_Users
Where 
	RoleID = 2
OR
	RoleID = 1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE   PROCEDURE TT_ListProjects
(
	@UserID int,
	@RoleID int
)
AS

IF @RoleID = 1
BEGIN
	SELECT ProjectID, 
		Name as ProjectName, 
		Description, 
		ManagerUserID, 
		UserName,
		EstCompletionDate, 
		EstDuration
	FROM 
		TT_Projects
	INNER JOIN
		TT_Users
	ON 
		ManagerUserID = UserID
END

ELSE IF @RoleID = 2
BEGIN
	SELECT ProjectID, 
		Name as ProjectName, 
		Description, 
		ManagerUserID, 
		UserName,
		EstCompletionDate, 
		EstDuration
	FROM 
		TT_Projects
	INNER JOIN
		TT_Users
	ON 
		ManagerUserID = UserID
	WHERE ManagerUserID = @UserID
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE  PROCEDURE TT_ListProjectsByIDs
(
    @ProjectIDs nvarchar(512),
    @UserID int
)
AS

    DECLARE @sql nvarchar(1024),
            @RoleID int
    
    SELECT @RoleID = RoleID
    FROM TT_Users 
    WHERE UserID = @UserID;
    
    IF (@RoleID = 1)
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
        SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
        SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
        SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'            
        END
    ELSE IF (@RoleID = 2)
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' Sum(EL.Duration) AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P INNER JOIN TT_EntryLog EL'
        SET @sql = @sql + ' ON P.ProjectID = EL.ProjectID '
        SET @sql = @sql + 'WHERE P.ProjectID IN (' + @ProjectIDs + ') '
        SET @sql = @sql + ' AND P.ManagerUserID = ' + CAST(@UserID AS nvarchar(20))
        SET @sql = @sql + 'GROUP BY P.ProjectID, P.Name, P.EstCompletionDate, P.EstDuration'            
        END
    ELSE
        BEGIN
        SET @sql = 'SELECT P.ProjectID,'
        SET @sql = @sql + ' P.Name AS ProjectName,'
        SET @sql = @sql + ' P.EstCompletionDate,'
        SET @sql = @sql + ' P.EstDuration AS EstHours,'
        SET @sql = @sql + ' P.EstDuration AS ActualHours '
        SET @sql = @sql + 'FROM TT_Projects P WHERE 1=0 '
        END
    
    EXEC sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE   PROCEDURE TT_ListProjectsWithMembership
(
	@QueryUserID int,
	@UserID int
)
AS

DECLARE @@QueryUserRoleID int
SELECT @@QueryUserRoleID = TT_Users.RoleID FROM TT_Users WHERE TT_Users.UserID = @QueryUserID

IF @@QueryUserRoleID = 1 OR @QueryUserID = @UserID
  BEGIN
	SELECT 	TT_Projects.ProjectID,
		Name, 
		Description, 
		ManagerUserID, 
		EstCompletionDate, 
		EstDuration
	FROM TT_Projects 
	INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
	WHERE UserID = @UserID
  END

IF @@QueryUserRoleID = 2
  BEGIN
	SELECT 	TT_Projects.ProjectID,
		Name, 
		Description, 
		ManagerUserID, 
		EstCompletionDate, 
		EstDuration
	FROM TT_Projects 
	INNER JOIN TT_ProjectMembers ON TT_ProjectMembers.ProjectID = TT_Projects.ProjectID
	WHERE UserID = @UserID AND ManagerUserID = @QueryUserID
  END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE  PROCEDURE TT_ListResourceByIDs
(
    @UserIDs nvarchar(512),
    @StartDate DateTime,
    @EndDate DateTime
)
AS

    DECLARE @sql nvarchar(1024)
    
    SET @sql = 'SELECT U.UserID, U.UserName, '
    SET @sql = @sql + ' Sum(EL.Duration) AS totalHours'
    SET @sql = @sql + ' FROM TT_EntryLog EL INNER JOIN TT_Users U'
    SET @sql = @sql + ' ON EL.UserID = U.UserID '
    SET @sql = @sql + ' WHERE U.UserID IN (' + @UserIDs + ') '
    SET @sql = @sql + ' AND EL.EntryDate >= ' +CAST(@StartDate as nvarchar(20))+ ' AND EL.EntryDate <= ' + CAST(@EndDate as nvarchar(20))	
    SET @sql = @sql + ' GROUP BY U.UserID, U.UserName'

    EXEC sp_executesql @sql
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO








CREATE PROCEDURE TT_ListTimeEntries
(
	@QueryUserID int,
	@UserID int,