0.7.880.0.sqldataprovider.sql

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

	ProjectID = @ProjectID,
	Name = @Name,
	ParentComponentID = @ParentComponentID
WHERE ComponentID = @ComponentID
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[BugNet_Component_GetRootComponentsByProjectId]'
GO
ALTER PROCEDURE [dbo].[BugNet_Component_GetRootComponentsByProjectId]
	@ProjectId int
AS
SELECT
	ComponentId,
	ProjectId,
	Name,
	ParentComponentId,
	(SELECT COUNT(*) FROM Component WHERE ParentComponentId=c.ComponentId) ChildCount
FROM Component c
WHERE 
ProjectId = @ProjectId AND c.ParentComponentId = 0
ORDER BY Name
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_Bug_GetBugFixedInVersionCountByProject]'
GO
CREATE PROCEDURE BugNet_Bug_GetBugFixedInVersionCountByProject 
	@ProjectId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT v.Name, COUNT(nt.FixedInVersionId) AS Number, v.VersionID
	FROM Version v 
	LEFT OUTER JOIN (SELECT FixedInVersionId
	FROM Bug b  
	WHERE (b.StatusID <> 4) AND (b.StatusID <> 5)) nt ON v.VersionID = nt.FixedInVersionId 
	WHERE (v.ProjectID = @ProjectId) 
	GROUP BY v.Name, v.VersionID,v.SortOrder
	ORDER BY v.SortOrder ASC
END

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[BugNet_Component_GetChildComponentsByComponentId]'
GO
ALTER PROCEDURE [dbo].[BugNet_Component_GetChildComponentsByComponentId]
	@ComponentId int
AS
SELECT
	ComponentId,
	ProjectId,
	Name,
	ParentComponentId,
	(SELECT COUNT(*) FROM Component WHERE ParentComponentId=c.ComponentId) ChildCount
FROM Component c
WHERE 
c.ParentComponentId = @ComponentId
ORDER BY Name

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[BugsView]'
GO
/* Handles 'unassigned' version 
*/
ALTER VIEW dbo.BugsView
AS
SELECT        dbo.Bug.BugID, dbo.Bug.Summary, dbo.Bug.Description, dbo.Bug.ReportedDate, dbo.Bug.StatusID, dbo.Bug.PriorityID, dbo.Bug.TypeID, 
                         dbo.Bug.ComponentID, dbo.Bug.ProjectID, dbo.Bug.ResolutionID, dbo.Bug.VersionID, dbo.Bug.LastUpdate, dbo.Bug.ReporterUserId, 
                         dbo.Bug.AssignedToUserId, dbo.Bug.LastUpdateUserId, dbo.Status.Name AS StatusName, dbo.Component.Name AS ComponentName, 
                         dbo.Priority.Name AS PriorityName, dbo.Project.Name AS ProjectName, dbo.Project.Code AS ProjectCode, dbo.Resolution.Name AS ResolutionName, 
                         dbo.Type.Name AS TypeName, ISNULL(dbo.Version.Name, 'Unassigned') AS VersionName, LastUpdateUsers.UserName AS LastUpdateUserName, 
                         ReportedUsers.UserName AS ReporterUserName, ISNULL(AssignedUsers.UserName, 'Unassigned') AS AssignedToUserName, dbo.Bug.DueDate, 
                         dbo.Bug.FixedInVersionId, ISNULL(FixedInVersion.Name, 'Unassigned') AS FixedInVersionName, dbo.Bug.Visibility, ISNULL
                             ((SELECT        SUM(Duration) AS Expr1
                                 FROM            dbo.BugTimeEntry AS BTE
                                 WHERE        (BugId = dbo.Bug.BugID)), 0.00) AS TimeLogged, dbo.Bug.Estimation
FROM            dbo.Bug LEFT OUTER JOIN
                         dbo.Component ON dbo.Bug.ComponentID = dbo.Component.ComponentID LEFT OUTER JOIN
                         dbo.Priority ON dbo.Bug.PriorityID = dbo.Priority.PriorityID LEFT OUTER JOIN
                         dbo.Project ON dbo.Bug.ProjectID = dbo.Project.ProjectID LEFT OUTER JOIN
                         dbo.Resolution ON dbo.Bug.ResolutionID = dbo.Resolution.ResolutionID LEFT OUTER JOIN
                         dbo.Status ON dbo.Bug.StatusID = dbo.Status.StatusID LEFT OUTER JOIN
                         dbo.Type ON dbo.Bug.TypeID = dbo.Type.TypeID LEFT OUTER JOIN
                         dbo.Version ON dbo.Bug.VersionID = dbo.Version.VersionID LEFT OUTER JOIN
                         dbo.aspnet_Users AS AssignedUsers ON dbo.Bug.AssignedToUserId = AssignedUsers.UserId LEFT OUTER JOIN
                         dbo.aspnet_Users AS ReportedUsers ON dbo.Bug.ReporterUserId = ReportedUsers.UserId LEFT OUTER JOIN
                         dbo.aspnet_Users AS LastUpdateUsers ON dbo.Bug.LastUpdateUserId = LastUpdateUsers.UserId LEFT OUTER JOIN
                         dbo.Version AS FixedInVersion ON dbo.Bug.FixedInVersionId = FixedInVersion.VersionID

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BugNet_Project_CloneProject]'
GO

CREATE PROCEDURE [dbo].[BugNet_Project_CloneProject] 
(
  @ProjectId INT,
  @ProjectName NVarChar(256)
)
AS
-- Copy Project
INSERT Project
(
  Name,
  Code,
  Description,
  UploadPath,
  CreateDate,
  Active,
  AccessType,
  CreatorUserId,
  ManagerUserId,
  AllowAttachments
)
SELECT
  @ProjectName,
  Code,
  Description,
  UploadPath,
  GetDate(),
  Active,
  AccessType,
  CreatorUserId,
  ManagerUserId,
  AllowAttachments
FROM 
  Project
WHERE
  ProjectId = @ProjectId
  
DECLARE @NewProjectId INT
SET @NewProjectId = @@IDENTITY

-- Copy Versions / Milestones
INSERT Version
(
  ProjectId,
  Name,
  SortOrder
)
SELECT
  @NewProjectId,
  Name,
  SortOrder
FROM
  Version
WHERE
  ProjectId = @ProjectID  

-- Copy Project Members
INSERT UserProjects
(
  UserId,
  ProjectId,
  CreatedDate
)
SELECT
  UserId,
  @NewProjectId,
  GetDate()
FROM
  UserProjects
WHERE
  ProjectId = @ProjectId

-- Copy Project Roles
INSERT Roles
( 
	ProjectId,
	RoleName,
	Description,
	AutoAssign
)
SELECT 
	@NewProjectId,
	RoleName,
	Description,
	AutoAssign
FROM
	Roles
WHERE
	ProjectId = @ProjectId

CREATE TABLE #OldRoles
(
  OldRowNumber INT IDENTITY,
  OldRoleId INT,
)

INSERT #OldRoles
(
  OldRoleId
)
SELECT
	RoleId
FROM
  Roles
WHERE
  ProjectId = @ProjectId
ORDER BY RoleId

CREATE TABLE #NewRoles
(
  NewRowNumber INT IDENTITY,
  NewRoleId INT,
)

INSERT #NewRoles
(
  NewRoleId
)
SELECT
  RoleId
FROM
  Roles
WHERE
  ProjectId = @NewProjectId
ORDER BY RoleId

INSERT UserRoles
(
	UserId,
	RoleId
)
SELECT 
	UserId,
	RoleId = NewRoleId
FROM #OldRoles INNER JOIN #NewRoles ON  OldRowNumber = NewRowNumber
INNER JOIN UserRoles UR ON UR.RoleId = OldRoleId

-- Copy Custom Fields
INSERT ProjectCustomFields
(
  ProjectId,
  CustomFieldName,
  CustomFieldRequired,
  CustomFieldDataType,
  CustomFieldTypeId
)
SELECT
  @NewProjectId,
  CustomFieldName,
  CustomFieldRequired,
  CustomFieldDataType,
  CustomFieldTypeId
FROM
  ProjectCustomFields
WHERE
  ProjectId = @ProjectId
  
-- Copy Custom Field Selections
CREATE TABLE #OldCustomFields
(
  OldRowNumber INT IDENTITY,
  OldCustomFieldId INT,
)
INSERT #OldCustomFields
(
  OldCustomFieldId
)
SELECT
	CustomFieldId
FROM
  ProjectCustomFields
WHERE
  ProjectId = @ProjectId
ORDER BY CustomFieldId

CREATE TABLE #NewCustomFields
(
  NewRowNumber INT IDENTITY,
  NewCustomFieldId INT,
)

INSERT #NewCustomFields
(
  NewCustomFieldId
)
SELECT
  CustomFieldId
FROM
  ProjectCustomFields
WHERE
  ProjectId = @NewProjectId
ORDER BY CustomFieldId

INSERT ProjectCustomFieldSelection
(
	CustomFieldId,
	CustomFieldSelectionValue,
	CustomFieldSelectionName,
	CustomFieldSelectionSortOrder
)
SELECT 
	CustomFieldId = NewCustomFieldId,
	CustomFieldSelectionValue,
	CustomFieldSelectionName,
	CustomFieldSelectionSortOrder
FROM #OldCustomFields INNER JOIN #NewCustomFields ON  OldRowNumber = NewRowNumber
INNER JOIN ProjectCustomFieldSelection CFS ON CFS.CustomFieldId = OldCustomFieldId

-- Copy Project Mailboxes
INSERT ProjectMailbox
(
  MailBox,
  ProjectId,
  AssignToUserId,
  IssueTypeId
)
SELECT
  Mailbox,
  @NewProjectId,
  AssignToUserId,
  IssueTypeId
FROM
  ProjectMailBox
WHERE
  ProjectId = @ProjectId

-- Copy Categories
INSERT Component
(
  ProjectId,
  Name,
  ParentComponentId
)
SELECT
  @NewProjectId,
  Name,
  ParentComponentId
FROM
  Component
WHERE
  ProjectId = @ProjectId  


CREATE TABLE #OldCategories
(
  OldRowNumber INT IDENTITY,
  OldComponentId INT,
)

INSERT #OldCategories
(
  OldComponentId
)
SELECT
  ComponentId
FROM
  Component
WHERE
  ProjectId = @ProjectId
ORDER BY ComponentId

CREATE TABLE #NewCategories
(
  NewRowNumber INT IDENTITY,
  NewComponentId INT,
)

INSERT #NewCategories
(
  NewComponentId
)
SELECT
  ComponentId
FROM
  Component
WHERE
  ProjectId = @NewProjectId
ORDER BY ComponentId


UPDATE Component SET
  ParentComponentId = NewComponentId
FROM
  #OldCategories INNER JOIN #NewCategories ON OldRowNumber = NewRowNumber
WHERE
  ProjectId = @NewProjectId
  And ParentComponentID = OldComponentId 
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[BugNet_Bug_GetRoadMapProgress]'
GO
ALTER PROCEDURE [dbo].[BugNet_Bug_GetRoadMapProgress]
	@ProjectId int,
	@FixedInVersionId int
AS
	/* SET NOCOUNT ON */ 
SELECT (SELECT Count(*) from BugsView 
WHERE ProjectId = @ProjectId AND FixedInVersionId = @FixedInVersionId AND StatusId In (4,5)) As ClosedCount , (SELECT Count(*) from BugsView 
WHERE ProjectId = @ProjectId AND FixedInVersionId = @FixedInVersionId) As TotalCount


GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

INSERT INTO HostSettings(SettingName,SettingValue) Values('ApplicationTitle','BugNET Issue Tracker')
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO