0.66.sqldataprovider.sql

BugNET is an issue tracking and project issue management solution built using the ASP.NET web applic

SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetBugComponentCountByProject
 @ProjectId int,
 @ComponentId int
AS
	SELECT     Count(BugId) From Bug Where ProjectId = @ProjectId 
	AND ComponentId = @ComponentId AND StatusId <> 4 AND StatusId <> 5

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetBugPriorityCountByProject
 @ProjectId int
AS
	SELECT p.Name, COUNT(nt.PriorityID) AS Number, p.PriorityID 
	FROM   Priority p 
	LEFT OUTER JOIN (SELECT  PriorityID, ProjectID FROM   
	Bug b WHERE  (b.StatusID <> 4) AND (b.StatusID <> 5)) nt 
	ON p.PriorityID = nt.PriorityID AND nt.ProjectID = @ProjectId
	GROUP BY p.Name, p.PriorityID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetBugStatusCountByProject
 @ProjectId int
AS
	SELECT s.Name,Count(b.StatusID) as 'Number',s.StatusID 
	From Status s 
	LEFT JOIN Bug b on s.StatusID = b.StatusID AND b.ProjectID = @ProjectId 
	Group BY s.Name,s.StatusID Order By s.StatusID ASC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetBugTypeCountByProject
	@ProjectId int
AS
	SELECT     t.Name, COUNT(nt.TypeID) AS Number, t.TypeID, t.ImageUrl
	FROM  Type t 
	LEFT OUTER JOIN (SELECT TypeID, ProjectID 
	FROM Bug b WHERE (b.StatusID <> 4) 
	AND (b.StatusID <> 5)) nt 
	ON t.TypeID = nt.TypeID 
	AND nt.ProjectID = @ProjectId
	GROUP BY t.Name, t.TypeID,t.ImageUrl


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetBugUnassignedCountByProject
 @ProjectId int
AS
	SELECT     COUNT(BugID) AS Number 
	FROM Bug 
	WHERE (AssignedTo = 0) 
		AND (ProjectID = @ProjectId) 
		AND (StatusID <> 4) 
		AND (StatusID <> 5)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetBugUserCountByProject
 @ProjectId int
AS
	SELECT u.UserID,u.DisplayName, COUNT(b.BugID) AS Number FROM UserProjects pm 
	LEFT OUTER JOIN Users u ON pm.UserID = u.UserID 
	LEFT OUTER JOIN Bug b ON b.AssignedTo = u.UserID
	 WHERE (pm.ProjectID = @ProjectId) 
	 AND (b.ProjectID= @ProjectId ) 
	 AND (b.StatusID <> 4) 
	 AND (b.StatusID <> 5)  
	 GROUP BY u.DisplayName, u.UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetBugVersionCountByProject
	@ProjectId int
AS
	SELECT v.Name, COUNT(nt.VersionID) AS Number, v.VersionID 
	FROM Version v 
	LEFT OUTER JOIN (SELECT VersionID  
	FROM Bug b  
	WHERE (b.StatusID <> 4) AND (b.StatusID <> 5)) nt ON v.VersionID = nt.VersionID 
	WHERE (v.ProjectID = @ProjectId) 
	GROUP BY v.Name, v.VersionID
	ORDER BY v.VersionID DESC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.BugNet_Bug_GetBugsByCriteria
(
    @ProjectId int = NULL,
    @ComponentId int = NULL,
    @VersionId int = NULL,
    @PriorityId int = NULL,
    @TypeId int = NULL,
    @ResolutionId int = NULL,
    @StatusId int = NULL,
    @AssignedTo int = NULL,
    @HardwareId int = NULL,
    @OperatingSystemId int = NULL,
    @Keywords nvarchar(256) = NULL,
    @IncludeComments bit = NULL
)
AS
if @StatusId = 0 
 SELECT
    *
FROM
    BugsView 
WHERE
    ((@ProjectId IS NULL) OR (ProjectId = @ProjectId)) AND
    ((@ComponentId IS NULL) OR (ComponentId = @ComponentId)) AND
    ((@VersionId IS NULL) OR (VersionId = @VersionId)) AND
    ((@PriorityId IS NULL) OR (PriorityId = @PriorityId))AND
    ((@TypeId IS NULL) OR (TypeId = @TypeId)) AND
    ((@ResolutionId IS NULL) OR (ResolutionId = @ResolutionId)) AND
    ((@StatusId IS NULL) OR (StatusId In (1,2,3))) AND
    ((@AssignedTo IS NULL) OR (AssignedTo = @AssignedTo)) AND
    ((@HardwareId IS NULL) OR (HardwareId = @HardwareId)) AND
    ((@OperatingSystemId IS NULL) OR (OperatingSystemId = @OperatingSystemId))  AND 
    ((@Keywords IS NULL) OR (Description LIKE '%' + @Keywords + '%' )  OR (Summary LIKE '%' + @Keywords + '%' ) )
else
SELECT
    *
FROM
    BugsView
WHERE
    ((@ProjectId IS NULL) OR (ProjectId = @ProjectId)) AND
    ((@ComponentId IS NULL) OR (ComponentId = @ComponentId)) AND
    ((@VersionId IS NULL) OR (VersionId = @VersionId)) AND
    ((@PriorityId IS NULL) OR (PriorityId = @PriorityId))AND
    ((@TypeId IS NULL) OR (TypeId = @TypeId)) AND
    ((@ResolutionId IS NULL) OR (ResolutionId = @ResolutionId)) AND
    ((@StatusId IS NULL) OR (StatusId = @StatusId)) AND
    ((@AssignedTo IS NULL) OR (AssignedTo = @AssignedTo)) AND
    ((@HardwareId IS NULL) OR (HardwareId = @HardwareId)) AND
    ((@OperatingSystemId IS NULL) OR (OperatingSystemId = @OperatingSystemId)) AND
    ((@Keywords IS NULL) OR (Description LIKE '%' + @Keywords + '%' )  OR (Summary LIKE '%' + @Keywords + '%' ))
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetBugsByProjectId
	@ProjectId int
As
Select * from BugsView WHERE ProjectId = @ProjectId
Order By StatusId,PriorityName

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Bug_GetChangeLog 
	@ProjectId int
AS

Select * from BugsView WHERE ProjectId = @ProjectId  AND StatusID = 5
Order By VersionId DESC,ComponentName ASC, TypeName ASC, AssignedUserDisplayName ASC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_GetRecentlyAddedBugsByProject
	@ProjectId int
AS
SELECT TOP 5
	*
FROM 
	BugsView
WHERE
	ProjectId = @ProjectId
ORDER BY BugID DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Bug_UpdateBug
  @BugId Int,
  @Summary nvarchar(500),
  @Description text,
  @Url nvarchar(500),
  @ProjectId Int,
  @ComponentId Int,
  @StatusId Int,
  @PriorityId Int,
  @VersionId Int,
  @HardwareId Int,
  @EnvironmentId int,
  @TypeId Int,
  @OperatingSystemId Int,
  @ResolutionId Int,
  @AssignedTo Int,
  @LastUpdateUserName NVarChar(200)
AS
DECLARE @newIssueId Int
-- Get Last Update UserID
DECLARE @LastUpdateUserId Int
SELECT @LastUpdateUserId = UserId FROM Users WHERE Username = @LastUpdateUserName
	Update Bug Set
		Summary = @Summary,
		Description =@Description,
		Url =@Url,
		StatusID =@StatusId,
		PriorityID =@PriorityId,
		TypeId = @TypeId,
		EnvironmentID =@EnvironmentId,
		ComponentID = @ComponentId,
		AssignedTo=@AssignedTo,
		HardwareId =@HardwareId,
		OperatingSystemId =@OperatingSystemId,
		ProjectId =@ProjectId,
		ResolutionId =@ResolutionId,
		VersionId =@VersionId,
		LastUpdateUser = @LastUpdateUserId,
		LastUpdate = GetDate()
	WHERE 
		BugId = @BugId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Comment_CreateNewComment
	@BugId int,
	@CreatorUserName NVarChar(100),
	@Comment text
AS
-- Get Last Update UserID
DECLARE @CreatorUserId Int
SELECT @CreatorUserId = UserId FROM Users WHERE Username = @CreatorUserName
INSERT BugComment
(
	BugId,
	UserId,
	CreatedDate,
	Comment
) 
VALUES 
(
	@BugId,
	@CreatorUserId,
	GetDate(),
	@Comment
)
RETURN @@IDENTITY
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Comment_DeleteComment
	@BugCommentId Int
AS
DELETE 
	BugComment
WHERE
	BugCommentId = @BugCommentId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Comment_GetCommentById
 @BugCommentId INT
AS
SELECT
	BugCommentId,
	BugId,
	BugComment.UserId,
	CreatedDate,
	Comment,
	Creators.UserName CreatorUserName,
	Creators.Email CreatorEmail,
	Creators.DisplayName CreatorDisplayName
FROM 
	BugComment
	INNER JOIN Users Creators ON Creators.UserId = BugComment.UserId	
WHERE
	BugCommentId = @BugCommentId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Comment_GetCommentsByBugId
 @BugId INT
AS
SELECT
	BugCommentId,
	BugId,
	BugComment.UserId,
	CreatedDate,
	Comment,
	Creators.UserName CreatorUserName,
	Creators.Email CreatorEmail,
	Creators.DisplayName CreatorDisplayName
FROM 
	BugComment
	INNER JOIN Users Creators ON Creators.UserId = BugComment.UserId	
WHERE
	BugId = @BugId
ORDER BY 
	CreatedDate DESC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Comment_UpdateComment
	@BugCommentId int,
	@BugId int,
	@CreatorId int,
	@Comment ntext
AS

UPDATE BugComment SET
	BugId = @BugId,
	UserId = @CreatorId,
	Comment = @Comment
WHERE BugCommentId= @BugCommentId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Component_CreateNewComponent
  @ProjectId int,
  @Name nvarchar(50),
  @ParentComponentId int
AS
	INSERT Component
	(
		ProjectID,
		Name,
		ParentComponentID
	)
	VALUES
	(
		@ProjectId,
		@Name,
		@ParentComponentId
	)
RETURN @@IDENTITY
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Component_DeleteComponent
	@ComponentId Int 
AS
DELETE 
	Component
WHERE
	ComponentId = @ComponentId
IF @@ROWCOUNT > 0 
	RETURN 0
ELSE
	RETURN 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Component_GetComponentById
	@ComponentId int
AS
SELECT
	ComponentId,
	ProjectId,
	Name,
	ParentComponentId
FROM Component
WHERE 
ComponentId = @ComponentId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Component_GetComponentsByProjectId
	@ProjectId int
AS
SELECT
	ComponentId,
	ProjectId,
	Name,
	ParentComponentId
FROM Component
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 BugNet_Environment_GetAllEnvironments
AS