0.66.sqldataprovider.sql

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

SELECT
	EnvironmentId,
	Name
FROM 
	Environment

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_GetEnvironmentById
	@EnvironmentId int
AS
SELECT
	EnvironmentId,
	Name
FROM 
	Environment
WHERE 
	EnvironmentId = @EnvironmentId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Hardware_GetAllHardware
AS
SELECT
	HardwareId,
	Name
FROM 
	Hardware

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Hardware_GetHardwareById
	@HardwareId int
AS
SELECT
	HardwareId,
	Name
FROM 
	Hardware
WHERE
	HardwareId = @HardwareId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_History_CreateNewHistory
  @BugId int,
  @UserId int,
  @FieldChanged nvarchar(50),
  @OldValue nvarchar(50),
  @NewValue nvarchar(50)
AS
	INSERT BugHistory
	(
		BugId,
		UserId,
		FieldChanged,
		OldValue,
		NewValue,
		CreatedDate
	)
	VALUES
	(
		@BugId,
		@UserId,
		@FieldChanged,
		@OldValue,
		@NewValue,
		GetDate()
	)
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_History_GetHistoryByBugId
	@BugId int
AS
 SELECT
	BugHistoryID,
	BugId,
	BugHistory.UserId,
	FieldChanged,
	OldValue,
	NewValue,
	CreatedDate,
	CreateUser.DisplayName
FROM 
	BugHistory
JOIN 
	Users CreateUser 
ON
	BugHistory.UserId = CreateUser.UserId
WHERE 
	BugId = @BugId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_HostSettings_GetHostSettings AS

SELECT SettingName, SettingValue FROM HostSettings
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_HostSettings_UpdateHostSetting
 @SettingName	nvarchar(50),
 @SettingValue 	nvarchar(256)
AS
UPDATE HostSettings SET
	SettingName = @SettingName,
	SettingValue = @SettingValue
WHERE
	SettingName  = @SettingName
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_OperatingSystem_GetAllOperatingSystems
AS
SELECT
	OperatingSystemId,
	Name
FROM 
	OperatingSystem

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_OperatingSystem_GetOperatingSystemById
	@OperatingSystemId int
AS
SELECT
	OperatingSystemId,
	Name
FROM 
	OperatingSystem
WHERE
	OperatingSystemId = @OperatingSystemId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Permission_AddRolePermission
	@PermissionId int,
	@RoleId int
AS
IF NOT EXISTS (SELECT PermissionId FROM RolePermission WHERE PermissionId = @PermissionId AND RoleId = @RoleId)
BEGIN
	INSERT  RolePermission
	(
		PermissionId,
		RoleId
	)
	VALUES
	(
		@PermissionId,
		@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_Permission_DeleteRolePermission
	@PermissionId Int,
	@RoleId Int 
AS
DELETE 
	RolePermission
WHERE
	PermissionId = @PermissionId
	AND 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_Permission_GetAllPermissions AS

SELECT PermissionId,PermissionKey, Name  FROM Permission
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE BugNet_Permission_GetPermissionsByRole
	@RoleId int
 AS
SELECT Permission.PermissionId,PermissionKey, Name  FROM Permission
Inner join RolePermission on RolePermission.PermissionId = Permission.PermissionId
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_Permission_GetRolePermission  AS

Select R.RoleId, R.ProjectId,P.PermissionId,P.PermissionKey,R.RoleName
FROM RolePermission RP 
JOIN
Permission P ON RP.PermissionId = P.PermissionId
JOIN
Roles R ON RP.RoleId = R.RoleId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Priority_GetAllPriorities
AS
SELECT
	PriorityId,
	Name,
	ImageUrl
FROM 
	Priority


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE BugNet_Priority_GetPriorityById
	@PriorityId int
AS
SELECT
	PriorityId,
	Name,
	ImageUrl
FROM 
	Priority
WHERE
	PriorityId = @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_Project_AddUserToProject
@UserId int,
@ProjectId int
AS
IF NOT EXISTS (SELECT UserId FROM UserProjects WHERE UserId = @UserId AND ProjectId = @ProjectId)
BEGIN
	INSERT  UserProjects
	(
		UserId,
		ProjectId,
		CreatedDate
	)
	VALUES
	(
		@UserId,
		@ProjectId,
		getdate()
	)
END
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_CreateNewProject
 @Name nvarchar(50),
 @Code nvarchar(3),
 @Description 	nvarchar(80),
 @ManagerId 	 Int,
 @UploadPath nvarchar(80),
 @Active int,
 @AccessType int,
 @CreatorUserId	int
AS
IF NOT EXISTS( SELECT ProjectId  FROM Project WHERE LOWER(Name) = LOWER(@Name))
BEGIN
	INSERT Project 
	(
		Name,
		Code,
		Description,
		UploadPath,
		ManagerId,
		CreateDate,
		CreatorUserId,
		AccessType,
		Active
	) 
	VALUES
	(
		@Name,
		@Code,
		@Description,
		@UploadPath,
		@ManagerId,
		GetDate(),
		@CreatorUserId,
		@AccessType,
		@Active
	)
 	RETURN @@IDENTITY
END
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_Project_CreateProjectMailbox
	@MailBox nvarchar (100),
	@ProjectID int,
	@AssignToUserID int,
	@IssueTypeID int
AS
INSERT ProjectMailBox 
(
	MailBox,
	ProjectID,
	AssignToUserID,
	IssueTypeID
)
VALUES
(
	@MailBox,
	@ProjectID,
	@AssignToUserID,
	@IssueTypeID
)
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_Project_DeleteProject
	@ProjectId int
AS

DELETE FROM Project where ProjectId = @ProjectId

IF @@ROWCOUNT > 0 
	RETURN 0
ELSE
	RETURN 1
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_DeleteProjectMailbox
	@ProjectMailboxId int
AS
DELETE  ProjectMailBox 
WHERE
	ProjectMailboxId = @ProjectMailboxId

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_Project_GetAllProjects
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
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_GetMailboxByProjectId
	@ProjectId int