📄 createdbobjects.sql

📁 Time Tracker Starter Kit 使您能轻松创建这样一个应用程序
💻 SQL
📖 第 1 页 / 共 3 页
字号:
上一页 1 23
	@StartDate datetime,
	@EndDate datetime
)
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 
			EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID, 
			TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
			ManagerUserID, TT_Categories.Abbreviation AS CatShortName
		FROM 
			TT_EntryLog 
				INNER JOIN 
				  TT_Categories 
				ON 
				  TT_EntryLog.CategoryID = TT_Categories.CategoryID 
				INNER JOIN 
				  TT_Projects 
				ON 
				  TT_EntryLog.ProjectID = TT_Projects.ProjectID	
		WHERE 
			UserID = @UserID 
			AND 
			Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
			AND 
			Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
	END
ELSE IF @@QueryUserRoleID = 2
	BEGIN
		SELECT 
			EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID, 
			TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
			ManagerUserID, TT_Categories.Abbreviation AS CatShortName
		FROM 
			TT_EntryLog 
				INNER JOIN 
				  TT_Categories 
				ON 
				  TT_EntryLog.CategoryID = TT_Categories.CategoryID 
				INNER JOIN 
				  TT_Projects 
				ON 
				  TT_EntryLog.ProjectID = TT_Projects.ProjectID	
		WHERE 
			UserID = @UserID 
			AND 
			Convert(nvarchar, EntryDate, 1) >= Convert(nvarchar, @StartDate, 1)
			AND 
			Convert(nvarchar, EntryDate, 1) <= Convert(nvarchar, @EndDate, 1)
			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_ListTimeEntriesByCategory
(
    @CategoryID int
)
AS

	SELECT 
		U.UserName, 
		U.UserID, 
		temp.MinEntryDate,
		temp.MaxEntryDate,
		Sum(EL.Duration) AS Duration
	FROM TT_EntryLog EL
	    INNER JOIN TT_Users U
	        ON EL.UserID = U.UserID
	    INNER JOIN (SELECT UserID, MIN(EntryDate) AS MinEntryDate, MAX(EntryDate) AS MaxEntryDate FROM TT_EntryLog GROUP BY UserID) AS temp
	    	ON temp.UserID = EL.UserID
	WHERE EL.CategoryID = @CategoryID
	GROUP BY U.UserName, U.UserID, temp.MinEntryDate, temp.MaxEntryDate
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE   PROCEDURE TT_ListTimeEntriesByUsers
(
	@UserIDList nvarchar(255),
	@StartDate datetime,
	@EndDate datetime
)
AS

DECLARE 
	@sSqlString nvarchar(512)

SET
	@sSqlString = 'SELECT EntryLogID, Description, Duration, EntryDate, ProjectID, UserID, CategoryID'
	
SET
	@sSqlString = @sSqlString + ' FROM TT_EntryLog WHERE UserID IN (' + @UserIDList + ')'

SET	
	@sSqlString = @sSqlString + ' and EntryDate >= '+ CAST(@StartDate AS nvarchar(24)) + ' and EntryDate <=  ' + CAST(@EndDate AS nvarchar(24))

SET
	@sSqlString = @sSqlString + ' GROUP BY UserID'

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE  PROCEDURE TT_ListUserTimeSummary
(
    @ManagerUserID int,
    @UserIDList nvarchar(512),
    @StartDate datetime,
    @EndDate datetime
)
AS

DECLARE 
    @sSqlString nvarchar(1024),
    @sSubSql nvarchar(1024),
    @RoleID int
    
SELECT @RoleID = RoleID
FROM TT_Users 
WHERE UserID = @ManagerUserID;

IF (@RoleID = 1)
    BEGIN
    SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @UserIDList + ')'
    SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    END
ELSE IF (@RoleID = 2)
    BEGIN   
    SET @sSubSql = 'SELECT PM.UserID FROM TT_Projects P INNER JOIN TT_ProjectMembers PM'
    SET @sSubSql = @sSubSql + ' ON P.ProjectID = PM.ProjectID WHERE P.ManagerUserID = @3 AND PM.UserID IN (' + @UserIDList + ')' 
    
    SET @sSqlString = 'SELECT Sum(EL.Duration) TotalHours, U.UserID, U.UserName'
    SET @sSqlString = @sSqlString + ' FROM TT_EntryLog EL Inner Join TT_Users U On EL.UserID = U.UserID WHERE U.UserID IN (' + @sSubSql + ')'
    SET @sSqlString = @sSqlString + ' AND EL.ProjectID IN (SELECT ProjectID From TT_Projects Where ManagerUserID = @3) '
    SET @sSqlString = @sSqlString + ' and EL.EntryDate >= @1 and EL.EntryDate <= @2  GROUP BY U.UserID, U.UserName'
    END
ELSE 
    SET @sSqlString = 'SELECT U.UserID AS TotalHours, U.UserID, U.UserName From TT_Users U Where 1=0'

EXEC sp_executesql @sSqlString, N'@1 datetime, @2 datetime, @3 int', @StartDate, @EndDate, @ManagerUserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







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

IF @RoleID = 1
	BEGIN
	  SELECT 
		UserID, 
		UserName, 
		TT_Users.RoleID,
		TT_Roles.Name 'RoleName'
	  FROM 
		TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
	END
ELSE IF @RoleID = 2
	BEGIN
	  SELECT DISTINCT
		TT_Users.UserID,
		TT_Users.UserName,
		TT_Users.RoleID,
		TT_Roles.Name 'RoleName'
	  FROM TT_Users 
	  INNER JOIN TT_ProjectMembers ON TT_Users.UserID=TT_ProjectMembers.UserID
	  INNER JOIN TT_Projects ON TT_ProjectMembers.ProjectID=TT_Projects.ProjectID
	  INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
	  WHERE	@UserID = TT_Projects.ManagerUserID OR @UserID = TT_Users.UserID

	END
ELSE
	SELECT
		UserID, 
		UserName, 
		TT_Users.RoleID,
		TT_Roles.Name 'RoleName'
	FROM 
		TT_Users INNER JOIN TT_Roles ON TT_Users.RoleID = TT_Roles.RoleID
	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_UpdateProject
(
	@ProjectID int,
	@Name nvarchar(50),
	@Description nvarchar(1024),
	@ManagerUserID int,
	@EstCompletionDate datetime,
	@EstDuration int,
	@SelectedMembers nvarchar(2000),
	@Categories nvarchar(4000)
)
AS


	DECLARE @Error int
	DECLARE @TempTable TABLE(UserID int PRIMARY KEY)
	DECLARE @TempString varchar(2000)
	DECLARE @Temp varchar(4000)
	DECLARE @Count int
	DECLARE @TempCatTable TABLE(CategoryID int primary key)
	DECLARE @InnerTemp nvarchar(50)
	DECLARE @CatID int
	DECLARE @CatName varchar(20)
	DECLARE @Abbrev varchar(5)
	DECLARE @Duration decimal(10,2)
	DECLARE @InnerCount int

	BEGIN TRANSACTION 

	-- Update the Project
	UPDATE 
		TT_Projects
	SET 	
		Name=@Name,
	        	Description = @Description,
		ManagerUserID = @ManagerUserID,
		EstCompletionDate = @EstCompletionDate,
		EstDuration = @EstDuration
	       
	WHERE 
		ProjectID = @ProjectID

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	SET @TempString = @SelectedMembers

	SET @Count = CHARINDEX(',', @TempString)

	WHILE @Count > 0
	BEGIN
		SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))
		INSERT INTO @TempTable VALUES(CAST(@Temp AS int))
		SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
		SET @Count = CHARINDEX(',', @TempString)
	END

	INSERT INTO @TempTable VALUES(CAST(@TempString AS int))

	DELETE 
		TT_ProjectMembers 
	WHERE 
		ProjectID = @ProjectID
	AND UserID NOT IN(SELECT UserID FROM @TempTable)

	INSERT INTO TT_ProjectMembers  
		SELECT @ProjectID, UserID FROM @TempTable WHERE UserID NOT IN 
			(SELECT UserID FROM TT_ProjectMembers WHERE ProjectID = @ProjectID)

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	SET @TempString = @Categories
	
	SET @Count = CHARINDEX(';', @TempString)
	WHILE @Count > 0
	BEGIN
		SET @Temp = SUBSTRING(@TempString, 1, (@Count - 1))

		SET @InnerCount = CHARINDEX(',', @Temp)

		SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
		SET @CatID = @InnerTemp

		SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
		SET @InnerCount = CHARINDEX(',', @Temp)

		SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
		SET @CatName = @InnerTemp

		SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
		SET @InnerCount = CHARINDEX(',', @Temp)

		SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
		SET @Abbrev = @InnerTemp
			
		SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
		SET @InnerCount = CHARINDEX(',', @Temp)

 		SET @Duration =  CAST(Round(@Temp,0) AS int)

		IF not exists(SELECT CategoryID from TT_Categories WHERE ProjectID = @ProjectID AND CategoryID = @CatID)
		BEGIN

			INSERT INTO TT_Categories
			(
				ProjectID, 
				[Name], 
				Abbreviation,
				EstDuration
			)
			VALUES
			(   
				@ProjectID,
				@CatName,
				@Abbrev,
				@Duration
			)

			SET @Error = @@ERROR
			IF @Error != 0 GOTO ERROR_HANDLER
			
			SELECT @CatID = @@IDENTITY
		
		END
		else
		BEGIN
			UPDATE 
				TT_Categories
			SET
				[Name] = @CatName,
				Abbreviation = @Abbrev,
				EstDuration = @Duration
			WHERE 
				ProjectID = @ProjectID
			AND 
				CategoryID = @CatID

			SET @Error = @@ERROR
			IF @Error != 0 GOTO ERROR_HANDLER
		END
		
		INSERT INTO @TempCatTable VALUES(@CatID)
		
		SET @TempString = SUBSTRING(@TempString, (@Count + 1), (LEN(@TempString) - @Count))
		SET @Count = CHARINDEX(';', @TempString)
	END

	set @Temp =  @TempString
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
	SET @CatID = @InnerTemp

	SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
	SET @CatName = @InnerTemp

	SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @InnerTemp = SUBSTRING(@Temp, 1, (@InnerCount - 1))
	SET @Abbrev = @InnerTemp

	SET @Temp = SUBSTRING(@Temp, (@InnerCount + 1), (LEN(@Temp) - @InnerCount))
	SET @InnerCount = CHARINDEX(',', @Temp)

	SET @Duration =  CAST(Round(@Temp,0) AS int)

		IF not exists(SELECT CategoryID from TT_Categories WHERE ProjectID = @ProjectID AND CategoryID = @CatID)
		BEGIN
			INSERT INTO TT_Categories
			(
				ProjectID, 
				[Name], 
				Abbreviation,
				EstDuration
			)
			VALUES
			(   
				@ProjectID,
				@CatName,
				@Abbrev,
				@Duration
			)
		
			SET @Error = @@ERROR
			IF @Error != 0 GOTO ERROR_HANDLER
	
			SELECT @CatID = @@IDENTITY
		
		END
		ELSE
		BEGIN
			UPDATE 
				TT_Categories
			SET
				[Name] = @CatName,
				Abbreviation = @Abbrev,
				EstDuration = @Duration
			WHERE 
				ProjectID = @ProjectID
			AND 
				CategoryID = @CatID

			SET @Error = @@ERROR
			IF @Error != 0 GOTO ERROR_HANDLER
		END
		
		INSERT INTO @TempCatTable VALUES(@CatID)


	DELETE 
		TT_Categories 
	WHERE 
		ProjectID = @ProjectID
	AND 
		CategoryID NOT IN(SELECT CategoryID FROM @TempCatTable)

	SET @Error = @@ERROR
	IF @Error != 0 GOTO ERROR_HANDLER

	COMMIT TRANSACTION
	RETURN 0

ERROR_HANDLER:
	IF @@TRANCOUNT != 0 ROLLBACK TRANSACTION
	RETURN @Error


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE  PROCEDURE TT_UpdateTimeEntry
(
	@EntryLogID int,
	@UserID int,
	@ProjectID int,
	@CategoryID int,
	@EntryDate datetime,
	@Description nvarchar(255),
	@Duration decimal(10,2)
)
AS

UPDATE
	 TT_EntryLog
		SET 	
			UserID=@UserID,
		     	ProjectID = @ProjectID,
			CategoryID = @CategoryID,
			EntryDate = @EntryDate,
			Description = @Description,
			Duration = @Duration
	       
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_UpdateUser
(
	@UserID int,
	@UserName nvarchar(50),
	@Password nvarchar(50),
	@DisplayName nvarchar(50),
--	@FirstName nvarchar(50),
--	@LastName nvarchar(50),
--	@Email nvarchar(50),
--	@Telephone nvarchar(50),
	@RoleID int
)
AS

--Verify that the UserName is not being updated to a UserName that already exists
IF Not Exists (SELECT UserName FROM TT_Users WHERE UserName=@UserName AND UserID<>@UserID)
   BEGIN
	UPDATE 
		TT_Users
		SET UserName=@UserName,
			Password = @Password,
			DisplayName = @DisplayName,
--			FirstName=@FirstName,
--		        LastName=@LastName,
--			Email = @Email,
--			Telephone = @Telephone,
		        RoleID = @RoleID
	WHERE 
		UserID=@UserID

	IF (@@Error<>0) GOTO ErrorHandler
	SELECT 
		1 AS retval
   END
-- If UserName is not unique, indicate so with -2
ELSE
	SELECT
		-2 AS retval

IF @@Error<>0
	GOTO ErrorHandler
ELSE
   BEGIN
--	COMMIT TRANSACTION
	RETURN (0)
   END

ErrorHandler:
   BEGIN
--	ROLLBACK TRANSACTION
	RETURN (1)
   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_UserLogin
    (
        @UserName  nvarchar(100),
        @Password nvarchar(50)
    )
    AS

    SELECT
        UserName

    FROM
        TT_Users

    WHERE
        UserName = @UserName
    AND
        Password = @Password


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
上一页 1 23
💿 文件大小 646 K
👤 上传用户 ludingpc
📂 所属分类企业管理
🏷️ 相关标签

#Starter #Tracker #Time #Kit
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -