0.66.sqldataprovider.sql

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

AS

SELECT ProjectMailbox.*,
	Users.DisplayName AssignToName,
	Type.Name IssueTypeName
FROM 
	ProjectMailbox
	INNER JOIN Users ON Users.UserID = AssignToUserID
	INNER JOIN Type ON Type.TypeID = IssueTypeID	
WHERE
	ProjectId = @ProjectId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Project_GetProjectByCode
 	@ProjectCode nvarchar(3)
AS
SELECT
	ProjectId,
	Name,
	Code,
	Description,
	UploadPath,
	ManagerId,
	CreatorUserId,
	CreateDate,
	Project.Active,
	AccessType,
	Managers.DisplayName ManagerDisplayName,
	Creators.DisplayName CreatorDisplayName
FROM 
	Project
	INNER JOIN Users Managers ON Managers.UserId = ManagerId	
	INNER JOIN Users Creators ON Creators.UserId = CreatorUserId	
WHERE
	Code = @ProjectCode
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Project_GetProjectById
 @ProjectId INT
AS
SELECT
	ProjectId,
	Name,
	Code,
	Description,
	UploadPath,
	ManagerId,
	CreatorUserId,
	CreateDate,
	Project.Active,
	AccessType,
	Managers.DisplayName ManagerDisplayName,
	Creators.DisplayName CreatorDisplayName
FROM 
	Project
	INNER JOIN Users Managers ON Managers.UserId = ManagerId	
	INNER JOIN Users Creators ON Creators.UserId = CreatorUserId	
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_Project_GetProjectsByUserId
	@UserId int,
	@ActiveOnly bit
AS
SELECT DISTINCT
	Project.ProjectId,
	Name,
	Code,
	Description,
	UploadPath,
	ManagerId,
	CreatorUserId,
	CreateDate,
	Project.Active,
	AccessType,
	Managers.DisplayName ManagerDisplayName,
	Creators.DisplayName CreatorDisplayName
FROM 
	Project
	Left JOIN Users Managers ON Managers.UserId = ManagerId	
	Left JOIN Users Creators ON Creators.UserId = CreatorUserId
	Left JOIN UserProjects ON UserProjects.ProjectId = Project.ProjectId
WHERE
	 (Project.AccessType = 1 AND Project.Active = @ActiveOnly) OR  UserProjects.UserId = @UserId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Project_GetPublicProjects
AS
SELECT
	ProjectId,
	Name,
	Code,
	Description,
	UploadPath,
	ManagerId,
	CreatorUserId,
	CreateDate,
	Project.Active,
	AccessType,
	Managers.DisplayName ManagerDisplayName,
	Creators.DisplayName CreatorDisplayName
FROM 
	Project
	INNER JOIN Users Managers ON Managers.UserId = ManagerId	
	INNER JOIN Users Creators ON Creators.UserId = CreatorUserId

WHERE AccessType = 1 AND Project.Active = 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_Project_RemoveUserFromProject
	@UserId Int,
	@ProjectId Int 
AS
DELETE 
	UserProjects
WHERE
	UserId = @UserId
	AND 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_Project_UpdateProject
 @ProjectId 	int,
 @Name	nvarchar(50),
 @Code		nvarchar(3),
 @Description 	nvarchar(80),
 @ManagerId 	int,
 @UploadPath 	nvarchar(80),
 @AccessType int,
 @Active 	int
AS
UPDATE Project SET
	Name = @Name,
	Code = @Code,
	Description = @Description,
	ManagerID = @ManagerId,
	UploadPath = @UploadPath,
	AccessType = @AccessType,
	Active = @Active
WHERE
	ProjectId = @ProjectId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_RelatedBug_CreateNewRelatedBug
	@BugId Int,
	@LinkedBugId int
AS
IF NOT EXISTS( SELECT RelatedBugId FROM RelatedBug WHERE @BugId = @BugId  AND LinkedBugId = @LinkedBugId)
BEGIN
	INSERT RelatedBug
	(
		BugId,
		LinkedBugId
	)
	VALUES
	(
		@BugId,
		@LinkedBugId
	)
	INSERT RelatedBug
	(
		BugId,
		LinkedBugId
	)
	VALUES
	(
		@LinkedBugId,
		@BugId
	)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_RelatedBug_DeleteRelatedBug
	@BugId Int,
	@LinkedBugId int
AS

DELETE 
	RelatedBug
WHERE
	BugId =  @BugId AND
	LinkedBugId = @LinkedBugId
DELETE 
	RelatedBug
WHERE
	BugId = @LinkedBugId AND
	LinkedBugId = @BugId

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 OFF 
GO

CREATE PROCEDURE BugNet_RelatedBug_GetRelatedBugsByBugId
	@BugId int
As
Select * from BugsView join RelatedBug on BugsView.BugId = RelatedBug.LinkedBugId
WHERE RelatedBug.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_Resolution_GetAllResolutions
AS
SELECT
	ResolutionId,
	Name
FROM 
	Resolution

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Resolution_GetResolutionById
	@ResolutionId int
AS
SELECT
	ResolutionId,
	Name
FROM 
	Resolution
WHERE
	ResolutionId = @ResolutionId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Role_AddUserToRole
	@UserId int,
	@RoleId int
AS
IF NOT EXISTS (SELECT UserId FROM UserRoles WHERE UserId = @UserId AND RoleId = @RoleId)
BEGIN
	INSERT  UserRoles
	(
		UserId,
		RoleId
	)
	VALUES
	(
		@UserId,
		@RoleId
	)
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Role_CreateNewRole
  @ProjectId 	int,
  @Name 	nvarchar(50),
  @Description 	nvarchar(256)
AS
	INSERT Roles
	(
		ProjectID,
		RoleName,
		Description
	)
	VALUES
	(
		@ProjectId,
		@Name,
		@Description
	)
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_Role_DeleteRole
	@RoleId Int 
AS
DELETE 
	Roles
WHERE
	RoleId = @RoleId
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_Role_GetAllRoles
AS
SELECT RoleId, RoleName FROM Roles

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Role_GetRoleById
	@RoleId int
AS
SELECT RoleId, ProjectId,RoleName, Description FROM Roles
WHERE RoleId = @RoleId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Role_GetRolesByProject
	@ProjectId int
AS
SELECT RoleId,ProjectId, RoleName, Description FROM Roles
WHERE ProjectId = @ProjectId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure BugNet_Role_GetRolesByUser
    
@UserId        int,
@ProjectId      int
as

select Roles.RoleName,
	Roles.ProjectId,
Roles.Description,
       Roles.RoleId
from UserRoles
inner join Users on UserRoles.UserId = Users.UserId
inner join Roles on UserRoles.RoleId = Roles.RoleId
where  Users.UserId = @UserId
and    Roles.ProjectId = @ProjectId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure BugNet_Role_GetRolesByUserId
    
@UserId        int
as

select Roles.RoleName,
	Roles.ProjectId,
Roles.Description,
       Roles.RoleId
from UserRoles
inner join Users on UserRoles.UserId = Users.UserId
inner join Roles on UserRoles.RoleId = Roles.RoleId
where  Users.UserId = @UserId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

create procedure BugNet_Role_IsUserInRole
    
@UserId        int,
@RoleId        int,
@ProjectId      int

as

select UserRoles.UserId,
       UserRoles.RoleId
from UserRoles
inner join Roles on UserRoles.RoleId = Roles.RoleId
where  UserRoles.UserId = @UserId
and    UserRoles.RoleId = @RoleId
and    Roles.ProjectId = @ProjectId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Role_RemoveUserFromRole
	@UserId Int,
	@RoleId Int 
AS
DELETE 
	UserRoles
WHERE
	UserId = @UserId
	AND RoleId = @RoleId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NU