SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Project table ALTER TABLE [dbo].[Project] DROP CONSTRAINT [DF_Project_PrivateProject]; ALTER TABLE [dbo].[Project] DROP CONSTRAINT [FK_Project_Project]; ALTER TABLE [dbo].[Release] DROP CONSTRAINT [FK_Release_Project]; ALTER TABLE [dbo].[ProjectUsers] DROP CONSTRAINT [FK_ProjectUsers_Project]; ALTER TABLE [dbo].[Task] DROP CONSTRAINT [FK_Task_Project]; ALTER TABLE [dbo].[Sprint] DROP CONSTRAINT [FK_Sprint_Project]; ALTER TABLE [dbo].[StoryTemplate] DROP CONSTRAINT [FK_StoryTemplate_Project]; ALTER TABLE [dbo].[ProjectIntegration] DROP CONSTRAINT [FK_ProjectIntegration_Project]; ALTER TABLE [dbo].[ProjectSettings] DROP CONSTRAINT [FK_ProjectSettings_Project]; ALTER TABLE [dbo].[Team] DROP CONSTRAINT [FK_Project_Team]; GO -- PRINT N'Starting rebuilding table [dbo].[Project]...'; SET ANSI_NULLS, QUOTED_IDENTIFIER ON; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; BEGIN TRANSACTION; CREATE TABLE [dbo].[tmp_ms_xx_Project] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (50) NOT NULL, [BacklogPath] NVARCHAR (MAX) NULL, [Start] DATETIME NULL, [Timestamp] TIMESTAMP NOT NULL, [HomePage] NVARCHAR (MAX) NULL, [DefaultSprintLength] INT NULL, [ParentProjectId] INT NULL, [Description] NVARCHAR (MAX) NULL, [Logo] VARBINARY (MAX) NULL, [PrivateProject] BIT CONSTRAINT [DF_Project_PrivateProject] DEFAULT ('True') NULL ); ALTER TABLE [dbo].[tmp_ms_xx_Project] ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_Project] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); IF EXISTS (SELECT TOP 1 1 FROM [dbo].[Project]) BEGIN SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Project] ON; INSERT INTO [dbo].[tmp_ms_xx_Project] ([Id], [Name], [BacklogPath], [Start], [HomePage], [DefaultSprintLength], [ParentProjectId], [Description], [Logo], [PrivateProject]) SELECT [Id], [Name], [BacklogPath], [Start], [HomePage], [DefaultSprintLength], [ParentProjectId], [Description], [Logo], [PrivateProject] FROM [dbo].[Project] ORDER BY [Id] ASC; SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Project] OFF; END DROP TABLE [dbo].[Project]; EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Project]', N'Project'; EXECUTE sp_rename N'[dbo].[tmp_ms_xx_clusteredindex_PK_Project]', N'PK_Project', N'OBJECT'; COMMIT TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; GO -- PRINT N'Creating [dbo].[Filter]...'; CREATE TABLE [dbo].[Filter] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (150) NOT NULL, [Description] NVARCHAR (MAX) NULL, [Definition] NVARCHAR (MAX) NOT NULL, [IsPublic] BIT NULL, [Author] NVARCHAR (150) NULL, [ProjectId] INT NOT NULL, [Timestamp] TIMESTAMP NOT NULL ); GO ALTER TABLE [dbo].[Filter] ADD CONSTRAINT [PK_Filter] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); GO -- PRINT N'Creating PK_PokerRoundUserVote...'; ALTER TABLE [dbo].[PokerRoundUserVote] ADD CONSTRAINT [PK_PokerRoundUserVote] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF); GO -- PRINT N'Creating [dbo].[Task].[IX_Task]...'; CREATE NONCLUSTERED INDEX [IX_Task] ON [dbo].[Task]([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0) ON [PRIMARY]; GO ALTER TABLE [dbo].[Project] WITH NOCHECK ADD CONSTRAINT [FK_Project_Project] FOREIGN KEY ([ParentProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION; GO ALTER TABLE [dbo].[Release] WITH NOCHECK ADD CONSTRAINT [FK_Release_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION; GO ALTER TABLE [dbo].[ProjectUsers] WITH NOCHECK ADD CONSTRAINT [FK_ProjectUsers_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE; GO -- PRINT N'Creating FK_Task_Project...'; ALTER TABLE [dbo].[Task] WITH NOCHECK ADD CONSTRAINT [FK_Task_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION; GO ALTER TABLE [dbo].[Sprint] WITH NOCHECK ADD CONSTRAINT [FK_Sprint_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE; GO ALTER TABLE [dbo].[StoryTemplate] WITH NOCHECK ADD CONSTRAINT [FK_StoryTemplate_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION; GO ALTER TABLE [dbo].[ProjectIntegration] WITH NOCHECK ADD CONSTRAINT [FK_ProjectIntegration_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION; GO ALTER TABLE [dbo].[ProjectSettings] WITH NOCHECK ADD CONSTRAINT [FK_ProjectSettings_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION; GO ALTER TABLE [dbo].[Team] WITH NOCHECK ADD CONSTRAINT [FK_Project_Team] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE [dbo].[Filter] WITH NOCHECK ADD CONSTRAINT [FK_Filter_Project] FOREIGN KEY ([ProjectId]) REFERENCES [dbo].[Project] ([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION; GO -------------------------------------------------------------------------------------------------------- ALTER TRIGGER [dbo].[trgTaskChanged] ON [dbo].[Task] AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; -- ObjectType = 1 (Task) --> Pre rozne tabulky staci menit len toto podla typu objektu v tabulke DECLARE @ObjectType int SET @ObjectType = 1 -- POUZITE KONSTANTY -- ChangeType: 0(NoChange) -- 1(Delete) -- 2(Insert) -- 3(Update) DECLARE @Count int -- Updatovane itemy SELECT @Count = COUNT(*) FROM DELETED DEL JOIN INSERTED INS ON DEL.Id=INS.Id IF @Count > 0 BEGIN -- Tasky sa uz nemazu s tabulky ale nastavi sa iba priznak IsDeleted na true -- podla priznaku nastavim typ zmeny (vid hore) DECLARE @ChangeType int SET @ChangeType = (SELECT TOP 1 (CASE WHEN INS.IsDeleted = 1 THEN 1 ELSE 3 END) FROM INSERTED INS) SELECT @ChangeType -- Ak uz zaznam existuje IF EXISTS(SELECT SourceId FROM [dbo].[SynchroHistory] WHERE SourceId IN (SELECT Id FROM DELETED)) BEGIN -- ak ide o zmazanie IF(@ChangeType = 1) BEGIN -- Odmazu sa zaznamy, ktore sa nenachadzaju v inom systeme DELETE FROM [dbo].[SynchroHistory] WHERE SourceId IN (SELECT Id FROM DELETED) AND DestinationId IS NULL -- Ak uz zaznam existuje aj v inom systeme, updatuje sa jeho cas a typ zmeny UPDATE [dbo].[SynchroHistory] SET ChangeType=1, ChangeTime=getdate() WHERE SourceId IN (SELECT Id FROM DELETED) AND DestinationId IS NOT NULL END ELSE -- v pripade ze nejde o delete updatuje sa jeho cas a typ zmeny UPDATE [dbo].[SynchroHistory] SET ChangeType=@ChangeType, ChangeTime=getdate() WHERE SourceId IN (SELECT Id FROM DELETED) END ELSE IF(@ChangeType = 3) -- Ak este neexistuje zaznam v tabulke zmien, vytvori sa zaznam o update -- Ak ide o delete tak sa netreba nic vytvarat INSERT INTO [dbo].[SynchroHistory] (IntegrationType, ObjectType, SourceId, DestinationId, ChangeType, ChangeTime, ProjectIntegrationId) SELECT PRI.IntegrationType, @ObjectType, INS.Id, null, @ChangeType, getdate(), PRI.Id FROM [dbo].[ProjectIntegration] AS PRI JOIN INSERTED AS INS ON PRI.ProjectId=INS.ProjectId IF @@ERROR != 0 ROLLBACK TRAN END ELSE BEGIN -- Zmazane itemu po starom ked este neexistoval priznak IsDeleted -- momentalne by sa nemal nikdy spustit a mozeme tento kod vyhodit SELECT @Count = COUNT(*) FROM DELETED IF @Count > 0 BEGIN -- Odmazu sa zaznamy, ktore sa nenachadzaju v inom systeme DELETE FROM [dbo].[SynchroHistory] WHERE SourceId IN (SELECT Id FROM DELETED) AND DestinationId IS NULL -- Ak uz zaznam existuje aj v inom systeme, updatuje sa jeho cas a typ zmeny UPDATE [dbo].[SynchroHistory] SET ChangeType=1, ChangeTime=getdate() WHERE SourceId IN (SELECT Id FROM DELETED) AND DestinationId IS NOT NULL END IF @@ERROR != 0 ROLLBACK TRAN -- Insertovane itemu (predpokladam, ze zaznam pre objekt este neexistuje) SELECT @Count = COUNT(*) FROM INSERTED IF @Count > 0 BEGIN INSERT INTO [dbo].[SynchroHistory] (IntegrationType, ObjectType, SourceId, DestinationId, ChangeType, ChangeTime, ProjectIntegrationId) SELECT PRI.IntegrationType, @ObjectType, INS.Id, null, 2, getdate(), PRI.Id FROM [dbo].[ProjectIntegration] AS PRI JOIN INSERTED AS INS ON PRI.ProjectId=INS.ProjectId END IF @@ERROR != 0 ROLLBACK TRAN END END GO CREATE TRIGGER [dbo].[trgSprintChanged] ON [dbo].[Sprint] AFTER INSERT,DELETE,UPDATE AS BEGIN SET NOCOUNT ON; -- ObjectType = 1 (Task) -- ObjectType = 2 (Impediment) -- ObjectType = 3 (Idea) -- ObjectType = 4 (Sprint) DECLARE @ObjectType int SET @ObjectType = 4 -- POUZITE KONSTANTY -- ChangeType: 0(NoChange) -- 1(Delete) -- 2(Insert) -- 3(Update) DECLARE @Count int DECLARE @SourceId int -- Updatovane itemy SELECT @Count = COUNT(*) FROM DELETED DEL JOIN INSERTED INS ON DEL.Id=INS.Id IF @Count > 0 BEGIN -- Ak uz zaznam existuje, updatuje sa jeho cas a typ zmeny IF EXISTS(SELECT SourceId FROM [dbo].[SynchroHistory] WHERE SourceId IN (SELECT Id FROM DELETED)) UPDATE [dbo].[SynchroHistory] SET ChangeType=3, ChangeTime=getdate() WHERE SourceId IN (SELECT Id FROM DELETED) ELSE -- Ak este neexistuje zaznam v tabulke zmien, vytvori sa zaznam o update INSERT INTO [dbo].[SynchroHistory] (IntegrationType, ObjectType, SourceId, DestinationId, ChangeType, ChangeTime, ProjectIntegrationId) SELECT PRI.IntegrationType, @ObjectType, INS.Id, null, 3, getdate(), PRI.Id FROM [dbo].[ProjectIntegration] AS PRI JOIN INSERTED AS INS ON PRI.ProjectId=INS.ProjectId IF @@ERROR != 0 ROLLBACK TRAN END ELSE BEGIN -- Zmazane itemu SELECT @Count = COUNT(*) FROM DELETED IF @Count > 0 BEGIN -- Odmazu sa zaznamy, ktore sa nenachadzaju v inom systeme DELETE FROM [dbo].[SynchroHistory] WHERE SourceId IN (SELECT Id FROM DELETED) AND DestinationId IS NULL -- Ak uz zaznam existuje aj v inom systeme, updatuje sa jeho cas a typ zmeny UPDATE [dbo].[SynchroHistory] SET ChangeType=1, ChangeTime=getdate() WHERE SourceId IN (SELECT Id FROM DELETED) AND DestinationId IS NOT NULL END IF @@ERROR != 0 ROLLBACK TRAN -- Insertovane itemu (predpokladam, ze zaznam pre objekt este neexistuje) SELECT @Count = COUNT(*) FROM INSERTED IF @Count > 0 BEGIN INSERT INTO [dbo].[SynchroHistory] (IntegrationType, ObjectType, SourceId, DestinationId, ChangeType, ChangeTime, ProjectIntegrationId) SELECT PRI.IntegrationType, @ObjectType, INS.Id, null, 2, getdate(), PRI.Id FROM [dbo].[ProjectIntegration] AS PRI JOIN INSERTED AS INS ON PRI.ProjectId=INS.ProjectId END IF @@ERROR != 0 ROLLBACK TRAN END END GO IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgTaskChangeLogChanged]')) DROP TRIGGER [dbo].[trgTaskChangeLogChanged] GO IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgTaskChangeLog_D]')) DROP TRIGGER [dbo].[trgTaskChangeLog_D] GO CREATE TRIGGER [dbo].[trgTaskChangeLog_D] ON [dbo].[Task] AFTER DELETE AS BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, Changed, UserName, ProjectId ) SELECT 'D', 'Task' , convert(varchar(100),D.Id) , '-' AS PropertyName, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), ( SELECT [loginName] FROM [User] WHERE Id = D.LastModifiedBy ), D.ProjectId FROM deleted D; END GO IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgTaskChangeLog_I]')) DROP TRIGGER [dbo].[trgTaskChangeLog_I] GO CREATE TRIGGER [dbo].[trgTaskChangeLog_I] ON [dbo].[Task] AFTER INSERT AS BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, Changed, UserName, ProjectId ) SELECT 'I', 'Task' , convert(varchar(100),I.Id) , '-' AS PropertyName, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), ( SELECT [loginName] FROM [User] WHERE Id = I.LastModifiedBy ), I.ProjectId FROM inserted I; END GO IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgTaskChangeLog_U]')) DROP TRIGGER [dbo].[trgTaskChangeLog_U] GO CREATE TRIGGER [dbo].[trgTaskChangeLog_U] ON [dbo].[Task] AFTER UPDATE AS BEGIN -- Subject ************************************************************ IF UPDATE(Subject) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Subject', d.Subject, i.Subject, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Subject IS NULL AND i.Subject IS NOT NULL) OR (d.Subject IS NOT NULL AND i.Subject IS NULL) OR (d.Subject <> i.Subject ) END -- Description ************************************************************ IF UPDATE(Description) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Description', d.Description, i.Description, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Description IS NULL AND i.Description IS NOT NULL) OR (d.Description IS NOT NULL AND i.Description IS NULL) OR (d.Description <> i.Description ) END -- EstimatedStoryPoints ************************************************************ IF UPDATE(EstimatedStoryPoints) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'EstimatedStoryPoints', d.EstimatedStoryPoints, i.EstimatedStoryPoints, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.EstimatedStoryPoints IS NULL AND i.EstimatedStoryPoints IS NOT NULL) OR (d.EstimatedStoryPoints IS NOT NULL AND i.EstimatedStoryPoints IS NULL) OR (d.EstimatedStoryPoints <> i.EstimatedStoryPoints ) END -- Importance ************************************************************ IF UPDATE(Importance) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Importance', d.Importance, i.Importance, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Importance IS NULL AND i.Importance IS NOT NULL) OR (d.Importance IS NOT NULL AND i.Importance IS NULL) OR (d.Importance <> i.Importance ) END -- State ************************************************************ IF UPDATE(State) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'State', d.State, i.State, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.State IS NULL AND i.State IS NOT NULL) OR (d.State IS NOT NULL AND i.State IS NULL) OR (d.State <> i.State ) END -- TaskBacklog ************************************************************ IF UPDATE(TaskBacklog) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'TaskBacklog', d.TaskBacklog, i.TaskBacklog, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.TaskBacklog IS NULL AND i.TaskBacklog IS NOT NULL) OR (d.TaskBacklog IS NOT NULL AND i.TaskBacklog IS NULL) OR (d.TaskBacklog <> i.TaskBacklog ) END -- OperatorId ************************************************************ IF UPDATE(OperatorId) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'OperatorId', d.OperatorId, i.OperatorId, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.OperatorId IS NULL AND i.OperatorId IS NOT NULL) OR (d.OperatorId IS NOT NULL AND i.OperatorId IS NULL) OR (d.OperatorId <> i.OperatorId ) END -- SprintId ************************************************************ IF UPDATE(SprintId) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'SprintId', d.SprintId, i.SprintId, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.SprintId IS NULL AND i.SprintId IS NOT NULL) OR (d.SprintId IS NOT NULL AND i.SprintId IS NULL) OR (d.SprintId <> i.SprintId ) END -- StoryPointsRemaining ************************************************************ IF UPDATE(StoryPointsRemaining) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'StoryPointsRemaining', d.StoryPointsRemaining, i.StoryPointsRemaining, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.StoryPointsRemaining IS NULL AND i.StoryPointsRemaining IS NOT NULL) OR (d.StoryPointsRemaining IS NOT NULL AND i.StoryPointsRemaining IS NULL) OR (d.StoryPointsRemaining <> i.StoryPointsRemaining ) END -- ParentTaskId ************************************************************ IF UPDATE(ParentTaskId) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'ParentTaskId', d.ParentTaskId, i.ParentTaskId, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.ParentTaskId IS NULL AND i.ParentTaskId IS NOT NULL) OR (d.ParentTaskId IS NOT NULL AND i.ParentTaskId IS NULL) OR (d.ParentTaskId <> i.ParentTaskId ) END -- Category ************************************************************ IF UPDATE(Category) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Category', d.Category, i.Category, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Category IS NULL AND i.Category IS NOT NULL) OR (d.Category IS NOT NULL AND i.Category IS NULL) OR (d.Category <> i.Category ) END -- ProjectId ************************************************************ IF UPDATE(ProjectId) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'ProjectId', d.ProjectId, i.ProjectId, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.ProjectId IS NULL AND i.ProjectId IS NOT NULL) OR (d.ProjectId IS NOT NULL AND i.ProjectId IS NULL) OR (d.ProjectId <> i.ProjectId ) END -- Width ************************************************************ IF UPDATE(Width) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Width', d.Width, i.Width, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Width IS NULL AND i.Width IS NOT NULL) OR (d.Width IS NOT NULL AND i.Width IS NULL) OR (d.Width <> i.Width ) END -- DisplayStyle ************************************************************ IF UPDATE(DisplayStyle) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'DisplayStyle', d.DisplayStyle, i.DisplayStyle, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.DisplayStyle IS NULL AND i.DisplayStyle IS NOT NULL) OR (d.DisplayStyle IS NOT NULL AND i.DisplayStyle IS NULL) OR (d.DisplayStyle <> i.DisplayStyle ) END -- Color ************************************************************ IF UPDATE(Color) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Color', d.Color, i.Color, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Color IS NULL AND i.Color IS NOT NULL) OR (d.Color IS NOT NULL AND i.Color IS NULL) OR (d.Color <> i.Color ) END -- BacklogDisplayStyle ************************************************************ IF UPDATE(BacklogDisplayStyle) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'BacklogDisplayStyle', d.BacklogDisplayStyle, i.BacklogDisplayStyle, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.BacklogDisplayStyle IS NULL AND i.BacklogDisplayStyle IS NOT NULL) OR (d.BacklogDisplayStyle IS NOT NULL AND i.BacklogDisplayStyle IS NULL) OR (d.BacklogDisplayStyle <> i.BacklogDisplayStyle ) END -- ReleaseId ************************************************************ IF UPDATE(ReleaseId) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'ReleaseId', d.ReleaseId, i.ReleaseId, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.ReleaseId IS NULL AND i.ReleaseId IS NOT NULL) OR (d.ReleaseId IS NOT NULL AND i.ReleaseId IS NULL) OR (d.ReleaseId <> i.ReleaseId ) END -- EstimatedDuration ************************************************************ IF UPDATE(EstimatedDuration) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'EstimatedDuration', d.EstimatedDuration, i.EstimatedDuration, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.EstimatedDuration IS NULL AND i.EstimatedDuration IS NOT NULL) OR (d.EstimatedDuration IS NOT NULL AND i.EstimatedDuration IS NULL) OR (d.EstimatedDuration <> i.EstimatedDuration ) END -- Height ************************************************************ IF UPDATE(Height) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Height', d.Height, i.Height, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Height IS NULL AND i.Height IS NOT NULL) OR (d.Height IS NOT NULL AND i.Height IS NULL) OR (d.Height <> i.Height ) END -- OrderInParent ************************************************************ IF UPDATE(OrderInParent) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'OrderInParent', d.OrderInParent, i.OrderInParent, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.OrderInParent IS NULL AND i.OrderInParent IS NOT NULL) OR (d.OrderInParent IS NOT NULL AND i.OrderInParent IS NULL) OR (d.OrderInParent <> i.OrderInParent ) END -- Tags ************************************************************ IF UPDATE(Tags) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Tags', d.Tags, i.Tags, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Tags IS NULL AND i.Tags IS NOT NULL) OR (d.Tags IS NOT NULL AND i.Tags IS NULL) OR (d.Tags <> i.Tags ) END -- IsDeleted ************************************************************ IF UPDATE(IsDeleted) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'IsDeleted', d.IsDeleted, i.IsDeleted, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.IsDeleted IS NULL AND i.IsDeleted IS NOT NULL) OR (d.IsDeleted IS NOT NULL AND i.IsDeleted IS NULL) OR (d.IsDeleted <> i.IsDeleted ) END -- NegativeValue ************************************************************ IF UPDATE(NegativeValue) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'NegativeValue', d.NegativeValue, i.NegativeValue, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.NegativeValue IS NULL AND i.NegativeValue IS NOT NULL) OR (d.NegativeValue IS NOT NULL AND i.NegativeValue IS NULL) OR (d.NegativeValue <> i.NegativeValue ) END -- PositiveValue ************************************************************ IF UPDATE(PositiveValue) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'PositiveValue', d.PositiveValue, i.PositiveValue, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.PositiveValue IS NULL AND i.PositiveValue IS NOT NULL) OR (d.PositiveValue IS NOT NULL AND i.PositiveValue IS NULL) OR (d.PositiveValue <> i.PositiveValue ) END -- Risk ************************************************************ IF UPDATE(Risk) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Risk', d.Risk, i.Risk, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Risk IS NULL AND i.Risk IS NOT NULL) OR (d.Risk IS NOT NULL AND i.Risk IS NULL) OR (d.Risk <> i.Risk ) END -- DependenciesIndex ************************************************************ IF UPDATE(DependenciesIndex) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'DependenciesIndex', d.DependenciesIndex, i.DependenciesIndex, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.DependenciesIndex IS NULL AND i.DependenciesIndex IS NOT NULL) OR (d.DependenciesIndex IS NOT NULL AND i.DependenciesIndex IS NULL) OR (d.DependenciesIndex <> i.DependenciesIndex ) END -- Satisfaction ************************************************************ IF UPDATE(Satisfaction) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Satisfaction', d.Satisfaction, i.Satisfaction, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Satisfaction IS NULL AND i.Satisfaction IS NOT NULL) OR (d.Satisfaction IS NOT NULL AND i.Satisfaction IS NULL) OR (d.Satisfaction <> i.Satisfaction ) END -- AcceptanceCriteria ************************************************************ IF UPDATE(AcceptanceCriteria) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'AcceptanceCriteria', d.AcceptanceCriteria, i.AcceptanceCriteria, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.AcceptanceCriteria IS NULL AND i.AcceptanceCriteria IS NOT NULL) OR (d.AcceptanceCriteria IS NOT NULL AND i.AcceptanceCriteria IS NULL) OR (d.AcceptanceCriteria <> i.AcceptanceCriteria ) END -- Type ************************************************************ IF UPDATE(Type) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Type', d.Type, i.Type, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Type IS NULL AND i.Type IS NOT NULL) OR (d.Type IS NOT NULL AND i.Type IS NULL) OR (d.Type <> i.Type ) END -- CompletedDuration ************************************************************ IF UPDATE(CompletedDuration) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'CompletedDuration', d.CompletedDuration, i.CompletedDuration, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.CompletedDuration IS NULL AND i.CompletedDuration IS NOT NULL) OR (d.CompletedDuration IS NOT NULL AND i.CompletedDuration IS NULL) OR (d.CompletedDuration <> i.CompletedDuration ) END -- TimeSpent ************************************************************ IF UPDATE(TimeSpent) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'TimeSpent', d.TimeSpent, i.TimeSpent, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.TimeSpent IS NULL AND i.TimeSpent IS NOT NULL) OR (d.TimeSpent IS NOT NULL AND i.TimeSpent IS NULL) OR (d.TimeSpent <> i.TimeSpent ) END -- IsFlagged ************************************************************ IF UPDATE(IsFlagged) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'IsFlagged', d.IsFlagged, i.IsFlagged, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.IsFlagged IS NULL AND i.IsFlagged IS NOT NULL) OR (d.IsFlagged IS NOT NULL AND i.IsFlagged IS NULL) OR (d.IsFlagged <> i.IsFlagged ) END -- IsInternal ************************************************************ IF UPDATE(IsInternal) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'IsInternal', d.IsInternal, i.IsInternal, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.IsInternal IS NULL AND i.IsInternal IS NOT NULL) OR (d.IsInternal IS NOT NULL AND i.IsInternal IS NULL) OR (d.IsInternal <> i.IsInternal ) END -- PersonalId ************************************************************ IF UPDATE(PersonalId) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'PersonalId', d.PersonalId, i.PersonalId, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.PersonalId IS NULL AND i.PersonalId IS NOT NULL) OR (d.PersonalId IS NOT NULL AND i.PersonalId IS NULL) OR (d.PersonalId <> i.PersonalId ) END -- Moscow ************************************************************ IF UPDATE(Moscow) BEGIN INSERT INTO EntityChangeLog ( ChangeType, ObjectType, ObjectId, PropertyName, OldValue, NewValue, Changed, UserName, ProjectId ) SELECT 'U', 'Task', i.Id, 'Moscow', d.Moscow, i.Moscow, CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114), (SELECT [loginName] FROM [User] WHERE Id = COALESCE(i.LastModifiedBy,d.LastModifiedBy)), i.ProjectId FROM inserted i inner join deleted d on i.id=d.id WHERE (d.Moscow IS NULL AND i.Moscow IS NOT NULL) OR (d.Moscow IS NOT NULL AND i.Moscow IS NULL) OR (d.Moscow <> i.Moscow ) END END GO ALTER PROCEDURE [dbo].[DeleteProject] @projectId int AS BEGIN SET NOCOUNT ON; delete from StoryTemplate where ProjectId = @projectId delete from TasksRelation where TaskId IN (select task.Id from Task where ProjectId = @projectId) or RelatedTaskId IN (select task.Id from Task where ProjectId = @projectId) delete from Task where ProjectId = @projectId delete from dbo.SprintRetrospectiveIdea where SprintId in ( Select Id from Sprint where ProjectId = @projectId) delete from Sprint where ProjectId = @projectId delete from Release where ProjectId = @projectId delete from dbo.Project where Id = @projectId END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteProject_2]') AND type in (N'P', N'PC')) BEGIN DROP PROCEDURE [dbo].[DeleteProject_2] END GO CREATE PROCEDURE [dbo].[DeleteProject_2] @projectId int AS BEGIN SET NOCOUNT ON; delete from StoryTemplate where ProjectId = @projectId delete from TasksRelation where TaskId IN (select task.Id from Task where ProjectId = @projectId) or RelatedTaskId IN (select task.Id from Task where ProjectId = @projectId) delete from Task where ProjectId = @projectId delete from dbo.SprintRetrospectiveIdea where SprintId in ( Select Id from Sprint where ProjectId = @projectId) delete from Sprint where ProjectId = @projectId delete from Release where ProjectId = @projectId delete from dbo.Project where Id = @projectId END GO GRANT EXECUTE ON [dbo].[DeleteProject_2] TO [ScrumDeskUsers] GO ALTER PROCEDURE [dbo].[GetEntityChangeLogMessages2] -- Add the parameters for the stored procedure here @startDate DATETIME, @endDate DATETIME = NULL, @limit INT = 1000, @projectId INT, @userName NVARCHAR(255) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF @endDate IS NULL BEGIN -- if end date is 'now' add 1 day to be sure to include latest change log entries SET @endDate = DATEADD(day, 1, getdate()) END -- helper table containg PropertyName values that we are interested in DECLARE @EntityPropertyTbl TABLE ( ObjectType NVARCHAR(50), PropertyName NVARCHAR(255) ); DECLARE @TaskState TABLE ( [State] INT, [Name] NVARCHAR(255) ); DECLARE @ImpedimentState TABLE ( [State] INT, [Name] NVARCHAR(50) ); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Subject'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Description'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','StoryPointsRemaining'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Duration'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','State'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Importance'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','EstimatedStoryPoints'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Category'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Color'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Tags'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','NegativeValue'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','PositiveValue'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Risk'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','Satisfaction'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','AcceptanceCriteria'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','SprintId'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','ReleaseId'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','OperatorId'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Task','IsDeleted'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Impediment','State'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Impediment','Subject'); INSERT INTO @EntityPropertyTbl(ObjectType,PropertyName) VALUES('Impediment','ResponsibleId'); INSERT INTO @TaskState([State],[Name]) VALUES (1, 'checked out'); INSERT INTO @TaskState([State],[Name]) VALUES (2, 'done'); INSERT INTO @TaskState([State],[Name]) VALUES (3, 'ToDo'); INSERT INTO @TaskState([State],[Name]) VALUES (5, 'solved'); INSERT INTO @ImpedimentState([State],[Name]) VALUES (0, 'new'); INSERT INTO @ImpedimentState([State],[Name]) VALUES (1, 'processing'); INSERT INTO @ImpedimentState([State],[Name]) VALUES (2, 'solved'); WITH Rowset AS ( SELECT ObjectId, ObjectType, Changed, PropertyName, OldValue, NewValue, UserName AS LoginName, ChangeType, CASE ObjectType WHEN 'Task' THEN COALESCE( (SELECT '''' + [Subject]+ '''' FROM [Task] WHERE Id = ObjectId), '') WHEN 'Impediment' THEN COALESCE( (SELECT '''' + [Subject]+ '''' FROM [Impediment] WHERE Id = ObjectId), '') END AS ObjectTypeSubject, COALESCE( (SELECT _U.FirstName + ' ' + _U.LastName FROM [User] _U WHERE _U.[LoginName] = [UserName]), [UserName] )AS UserName FROM EntityChangeLog WHERE ProjectId = @ProjectId and UserName <> @userName AND Changed BETWEEN @startDate AND @endDate AND ( ( ObjectType = 'Task' AND ( PropertyName IN (SELECT PropertyName FROM @EntityPropertyTbl WHERE ObjectType = 'Task') OR ChangeType IN ('I') ) ) OR ( ObjectType = 'Impediment' AND ( PropertyName IN (SELECT PropertyName FROM @EntityPropertyTbl WHERE ObjectType = 'Impediment') OR ChangeType IN ('I') ) ) ) ) SELECT TOP (@limit) Changed, UserName, ObjectId, ObjectType, CASE ObjectType WHEN 'Task' THEN CASE ChangeType WHEN 'U' THEN CASE PropertyName WHEN 'Subject' THEN (UserName + ' has updated name of "' + OldValue + '" to "' + NewValue + '"') WHEN 'Description' THEN (UserName + ' has updated description of ' + ObjectTypeSubject) WHEN 'StoryPointsRemaining' THEN (UserName + ' has updated estimated duration of '+ ObjectTypeSubject + ' from ' + OldValue + ' to ' + NewValue) WHEN 'Duration' THEN (UserName + ' has updated duration of '+ ObjectTypeSubject + ' from ' + OldValue + ' to ' + NewValue) WHEN 'State' THEN (UserName + ' has set status of '+ ObjectTypeSubject + ' to ' + (SELECT [Name] FROM @TaskState WHERE [State] = NewValue)) WHEN 'Importance' THEN (UserName + ' has changed importance of '+ ObjectTypeSubject + ' to ' + NewValue) WHEN 'EstimatedStoryPoints' THEN (UserName + ' has changed estimation of '+ ObjectTypeSubject + ' from ' + OldValue + ' to ' + NewValue) WHEN 'Category' THEN (UserName + ' has changed category of '+ ObjectTypeSubject) WHEN 'Color' THEN (UserName + ' has changed color of '+ ObjectTypeSubject) WHEN 'Tags' THEN (UserName + ' has changed tags of '+ ObjectTypeSubject) WHEN 'NegativeValue' THEN (UserName + ' has changed negative value of '+ ObjectTypeSubject) WHEN 'PositiveValue' THEN (UserName + ' has changed positive value of '+ ObjectTypeSubject) WHEN 'Risk' THEN (UserName + ' has changed risk of '+ ObjectTypeSubject) WHEN 'Satisfaction' THEN (UserName + ' has changed satisfaction of '+ ObjectTypeSubject) WHEN 'AcceptanceCriteria' THEN (UserName + ' has changed acceptance criteria of '+ ObjectTypeSubject) WHEN 'SprintId' THEN ( ObjectTypeSubject + ' is assigned to ' + COALESCE((SELECT [Name] FROM [Sprint] WHERE Id=NewValue),'')) WHEN 'ReleaseId' THEN ( ObjectTypeSubject + ' is assigned to ' + COALESCE((SELECT [Name] FROM [Release] WHERE Id=NewValue),'')) WHEN 'OperatorId' THEN ( ObjectTypeSubject + ' is assigned to ' + COALESCE((SELECT [FirstName] + ' ' + LastName FROM [User] WHERE Id=NewValue),'')) WHEN 'IsDeleted' THEN ( ObjectTypeSubject + ' was deleted by ' + LoginName) END WHEN 'I' THEN (UserName + ' has created new task/story ' + ObjectTypeSubject) END WHEN 'Impediment' THEN CASE ChangeType WHEN 'U' THEN CASE PropertyName WHEN 'State' THEN(UserName + ' has changed state of the impediment ' + ObjectTypeSubject + ' to ' + (SELECT [Name] FROM @ImpedimentState WHERE [State] = NewValue)) WHEN 'Subject' THEN(UserName + ' has changed subject of the impediment from' + OldValue+ ' to ' + NewValue) WHEN 'Subject' THEN(UserName + ' has changed subject of the impediment from' + OldValue+ ' to ' + NewValue) WHEN 'ResponsibleId' THEN(UserName + ' has changed responsible for removing impediment ' + ObjectTypeSubject ) END WHEN 'I' THEN (UserName + ' has created new impediment ' + ObjectTypeSubject) END END AS [Message] FROM Rowset ORDER BY Changed DESC END GO ALTER PROCEDURE [dbo].[LoadProjectTeams_3] @ProjectId int AS BEGIN SET NOCOUNT ON; ;WITH teamRowset AS ( SELECT [Id] ,[Name] ,[Description] ,[Logo] ,[ParentTeam] ,[ProjectId] ,[Timestamp] , 0 As TeamLevel FROM [Team] WHERE ProjectId = @projectId AND ParentTeam IS NULL UNION ALL SELECT T.[Id] ,T.[Name] ,T.[Description] ,T.[Logo] ,T.[ParentTeam] ,T.[ProjectId] ,T.[Timestamp] , TeamLevel + 1 FROM [Team] T JOIN teamRowset ON teamRowset.Id = T.ParentTeam ) SELECT * FROM teamRowset ORDER BY TeamLevel, ParentTeam, [Name]; SELECT [User].[Id], [LoginName], [Password], [FirstName], [LastName], [EMail], [IMType], [CallToNumber], [IM], [PhoneNumber], [WWW], [Picture], [GMTOffset], [CompanyPosition], [Profile], (SELECT TeamId FROM ProjectUsers PU WHERE PU.UserId=[User].[Id] AND PU.ProjectId=@ProjectId) AS TeamId, (SELECT UserRole FROM ProjectUsers PU WHERE PU.UserId=[User].[Id] AND PU.ProjectId=@ProjectId) AS UserRole, (SELECT Id FROM ProjectUsers PU WHERE PU.UserId=[User].[Id] AND PU.ProjectId=@ProjectId) AS ProjectUserId, [Timestamp], (SELECT Timestamp FROM ProjectUsers PU WHERE PU.UserId=[User].[Id] AND PU.ProjectId=@ProjectId) AS ProjectUserTimeStamp, [User].[TimeZone], [User].[NotifyByEmail] FROM [User] WHERE [User].[Id] IN (SELECT UserId FROM dbo.ProjectUsers WHERE ProjectId= @ProjectId) OR [User].[id] IN(SELECT OperatorId FROM dbo.Task WHERE ProjectId = @ProjectId GROUP BY OperatorId) END GO ALTER PROCEDURE [dbo].[LoadStory] @StoryId int AS BEGIN SET NOCOUNT ON; SELECT [Id], [Subject], [Description], [EstimatedStoryPoints], [StoryPointsRemaining], [Importance], [State], [TaskBacklog], [PositionLeft], [PositionTop], [OperatorId], [SprintId], [Category], [BacklogPositionLeft], [BacklogPositionTop], [Width], [DisplayStyle], [Color], [BacklogDisplayStyle], [ReleaseId], [ParentTaskId], [EstimatedDuration], [Height], [OrderInParent], [Tags], [NegativeValue], [PositiveValue], [Risk], [DependenciesIndex], [Satisfaction], [AcceptanceCriteria], [Type], [Timestamp], [TimeSpent], [IsFlagged], [IsInternal], [PersonalId], [Moscow] FROM [Task] WHERE Id = @StoryId END GO ALTER PROCEDURE [dbo].[LoginUser_3] ( @UserName nvarchar(50), @PswdHash nvarchar(50), @UserId int output ) AS BEGIN SET NOCOUNT ON; select @UserId = Id from [User] where LoginName = @UserName and Password = @PswdHash SELECT [Id], [LoginName], [Password], [FirstName], [LastName], [EMail], [IMType], [CallToNumber], [IM], [PhoneNumber], [WWW], [Picture], [GMTOffset], [CompanyPosition], [Profile], NULL, -- team Id NULL, -- user role NULL, -- ProjectUser.Id [Timestamp], NULL, -- ProjectUserTimeStamp [TimeZone], [NotifyByEmail] FROM [User] WHERE Id=@UserId END GO ALTER PROCEDURE [dbo].[PokerRoundUserVote_Insert] @ID int OUTPUT, @Timestamp timestamp OUTPUT, @UserId int, @StoryId int, @Vote float, @Voted datetime AS BEGIN INSERT INTO [dbo].[PokerRoundUserVote] ([UserId] ,[StoryId] ,[Vote] ,[Voted]) VALUES (@UserId ,@StoryId ,@Vote ,@Voted) -- Get the identity value SET @ID = SCOPE_IDENTITY() -- Select computed columns into output parameters SELECT @Timestamp = [Timestamp] FROM dbo.[PokerRoundUserVote] WHERE [ID] = @ID END GO ALTER PROCEDURE [dbo].[StoryTemplate_Update3] ( @ID int, @Timestamp timestamp OUTPUT, @ParentStoryId int, @ProjectId int, @TemplateName nvarchar(255), @Subject nvarchar(255), @Description nvarchar(MAX), @EstimatedDuration float, @EstimatedStoryPoints float, @Importance int, @Category nvarchar(50), @Color int, @ModifiedDate datetime, @Author nvarchar(150), @TemplateDescription nvarchar(MAX), @OrderInParent int, @Type nvarchar(100) ) AS BEGIN UPDATE [dbo].[StoryTemplate] SET [ParentStoryId]=@ParentStoryId, [ProjectId]=@ProjectId, [TemplateName]=@TemplateName, [Subject]=@Subject, [Description]=@Description, [EstimatedDuration]=@EstimatedDuration, [EstimatedStoryPoints]=@EstimatedStoryPoints, [Importance]=@Importance, [Category]=@Category, [Color]=@Color, [ModifiedDate]=@ModifiedDate, [Author]=@Author, [TemplateDescription]=@TemplateDescription, [OrderInParent] = @OrderInParent, [Type] = @Type WHERE [ID]=@ID -- Select computed columns into output parameters SELECT @Timestamp = [Timestamp] FROM [dbo].[StoryTemplate] WHERE [ID] = @ID END GO ALTER PROCEDURE [dbo].[Task_Update_11] ( @ID int, @Timestamp timestamp OUTPUT, @Subject nvarchar(255), @Description nvarchar(MAX), @Importance int, @State smallint, @TaskBacklog nvarchar(255), @PositionLeft float, @PositionTop float, @OperatorId int, @SprintId int, @StoryPointsRemaining float, @EstimatedStoryPoints float, @Category nvarchar(50), @ParentTaskId int, @ProjectId int, @BacklogPositionLeft float, @BacklogPositionTop float, @Width float, @Height float, @DisplayStyle int, @Color int, @BacklogDisplayStyle int, @ReleaseId int, @EstimatedDuration float, @OrderInParent int, @Tags nvarchar(MAX), @NegativeValue float, @PositiveValue float, @Risk float, @DependenciesIndex int, @Satisfaction int, @AcceptanceCriteria nvarchar(MAX), @Type nvarchar(MAX), @LastModifiedBy int, @TimeSpent float, @IsFlagged bit, @IsInternal bit, @PersonalId int, @Moscow int ) AS BEGIN UPDATE [dbo].[Task] SET [Subject]=@Subject ,[Description]=@Description ,[EstimatedStoryPoints]=@EstimatedStoryPoints ,[Importance]=@Importance ,[State]=@State ,[TaskBacklog]=@TaskBacklog ,[PositionLeft]=@PositionLeft ,[PositionTop]=@PositionTop ,[OperatorId]=@OperatorId ,[SprintId]=@SprintId ,[StoryPointsRemaining]=@StoryPointsRemaining ,[Category]=@Category ,[ParentTaskId]=@ParentTaskId ,[ProjectId]=@ProjectId ,[BacklogPositionLeft]=@BacklogPositionLeft ,[BacklogPositionTop]=@BacklogPositionTop ,[Width]=@Width ,[Height]=@Height ,[DisplayStyle]=@DisplayStyle ,[Color]=@Color ,[BacklogDisplayStyle]=@BacklogDisplayStyle ,[ReleaseId]=@ReleaseId ,[EstimatedDuration]=@EstimatedDuration ,[OrderInParent] = @OrderInParent ,[Tags] = @Tags ,[NegativeValue]= @NegativeValue ,[PositiveValue] = @PositiveValue ,[Risk] = @Risk ,[DependenciesIndex] = @DependenciesIndex ,[Satisfaction] = @Satisfaction ,[AcceptanceCriteria] = @AcceptanceCriteria ,[Type] = @Type ,[LastEditDate] = GetDate() ,[LastModifiedBy] = @LastModifiedBy ,[TimeSpent]=@TimeSpent ,[IsFlagged] = @IsFlagged ,[IsInternal] = @IsInternal ,[PersonalId] = @PersonalId ,[Moscow] = @Moscow WHERE [ID]=@ID -- Select computed columns into output parameters SELECT @Timestamp = [Timestamp] FROM dbo.Task WHERE [ID] = @ID END GO ALTER PROCEDURE [dbo].[User_LogActivity_2] ( @Operation varchar(50) = null, @UserId int = null, @Info nvarchar(255) = null, @ProjectId int = null ) AS BEGIN INSERT INTO [dbo].[Audit] (Operation,UserId,Info,Date, ProjectId) VALUES (@Operation, @UserId, @Info,getdate(),@ProjectId) END GRANT EXECUTE ON [dbo].[User_LogActivity_2] TO ScrumDeskUsers GO ALTER PROCEDURE [dbo].[uspDuplicateAttachment] ( @AttachmentId int, @StoryId int ) AS BEGIN insert dbo.Attachment (TaskId,UserId,Modified,URL,FileContent,FileThumbnail) select TaskId,UserId,Modified,URL,FileContent,FileThumbnail from dbo.Attachment where id=@AttachmentId UPDATE dbo.Attachment SET TaskId=@StoryId WHERE [Attachment].[Id] = SCOPE_IDENTITY() SELECT [Attachment].[Id], [Attachment].[TaskId], [Attachment].[UserId], [Attachment].[Modified], [Attachment].[URL] FROM [Attachment] WHERE [Attachment].[Id] = SCOPE_IDENTITY() END GRANT EXECUTE ON [dbo].[uspDuplicateAttachment] TO ScrumDeskUsers GO ALTER PROCEDURE [dbo].[uspGetProjectSettings] -- Add the parameters for the stored procedure here @projectId int AS BEGIN SET NOCOUNT ON; SELECT [Property], [Value] FROM [ProjectSettings] WHERE [ProjectId] = @projectId END GO ALTER PROCEDURE [dbo].[uspGetProjectSprints_2] @projectId int AS BEGIN SET NOCOUNT ON; SELECT [Id], [Name],[Goal], [Start],[EstimatedDuration], [Done], [ProjectId], [State],[DemoDate],[EstimatedStoryPoints],[FocusFactor],[Notes],[StartRemaining], [StartProgress], [MeetingRoom], [MeetingTime] FROM [Sprint] WHERE [ProjectId] = @projectId ;WITH rowset AS ( SELECT [Id], [Name],[Goal], [Start],[EstimatedDuration], [Done], [ProjectId], [State],[DemoDate],[EstimatedStoryPoints],[FocusFactor],[Notes],[StartRemaining], [StartProgress], [MeetingRoom], [MeetingTime] FROM [Sprint] WHERE [ProjectId] = @projectId ) SELECT [Id] ,[SprintId] ,[StoriesCount] ,[TasksCount] ,[EstimatedStoryPoints] ,[EstimatedDuration] ,[RemainingDuration] ,[Date] ,[Timestamp] ,[SprintState] FROM [SprintProgress] WHERE SprintId IN (SELECT Id FROM rowset) ORDER BY [Timestamp] END GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspHubSprint_Insert]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[uspHubSprint_Insert] ( @ID int OUTPUT, @Name nvarchar(MAX), @Goal nvarchar(MAX), @StartDate datetime, @ProjectID int, @EstimatedDuration int, @State int, @DemoDate datetime, @EstimatedStoryPoints int, @Notes nvarchar(MAX), @ProjectIntegrationId int, @DestinationId nvarchar(max) ) AS BEGIN INSERT INTO [dbo].[Sprint] ([Name] ,[Goal] ,[Start] ,[ProjectId] ,[EstimatedDuration] ,[State] ,[DemoDate] ,[EstimatedStoryPoints] ,[Notes] ) VALUES (@Name, @Goal, @StartDate, @ProjectID, @EstimatedDuration, @State, @DemoDate, @EstimatedStoryPoints, @Notes ) -- Get the identity value SET @ID = SCOPE_IDENTITY() --musim aktualizovat SynchroHistory tak, aby mi neprisla spatna zmena od ScrumDesku DECLARE @ChangeTime datetime SELECT @ChangeTime = ChangeTime FROM [dbo].[SynchroHistory] WHERE [ProjectIntegrationId] = @ProjectIntegrationId AND [ObjectType]= 4 AND [SourceId] = @ID UPDATE [dbo].[SynchroHistory] SET [DestinationId] = @DestinationId, [SyncTime] = @ChangeTime WHERE [ProjectIntegrationId] = @ProjectIntegrationId AND [ObjectType]= 4 AND [SourceId] = @ID END ' END GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspHubStory_Select_2]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[uspHubStory_Select_2] ( @Id int ) AS BEGIN SET NOCOUNT ON; SELECT T.Id, T.Subject, T.Description, T.Category, T.State, T.EstimatedStoryPoints, T.Importance, T.StoryPointsRemaining, T.EstimatedDuration, T.ParentTaskId, PRJ.Name AS Project, SPR.Name AS Sprint, REL.Name AS Release,T.OperatorId, T.AcceptanceCriteria, T.PositiveValue, T.Tags, T.IsDeleted FROM [dbo].[Task] AS T LEFT JOIN [dbo].[Project] AS PRJ ON T.ProjectId=PRJ.Id LEFT JOIN [dbo].[Sprint] AS SPR ON T.SprintId=SPR.Id LEFT JOIN [dbo].[Release] AS REL ON T.ReleaseId=Rel.Id WHERE T.Id=@Id END' END GO CREATE PROCEDURE [dbo].[Filter_Delete] ( @ID int ) AS DELETE [dbo].[Filter] WHERE [ID]=@ID GO CREATE PROCEDURE [dbo].[Filter_Insert_1] ( @ID int OUTPUT, @Timestamp timestamp OUTPUT, @ProjectId int, @Name varchar(MAX), @Description varchar(MAX), @Definition varchar(MAX), @IsPublic bit, @Author varchar(MAX) ) AS BEGIN INSERT INTO [Filter] ([Name] ,[Description] ,[Definition] ,[IsPublic] ,[Author] ,[ProjectId] ) VALUES (@Name ,@Description ,@Definition ,@IsPublic ,@Author ,@ProjectId ) -- Get the identity value SET @ID = SCOPE_IDENTITY() -- Select computed columns into output parameters SELECT @Timestamp = [Timestamp] FROM dbo.Filter WHERE [ID] = @ID END GO CREATE PROCEDURE [dbo].[Filter_Update_1] ( @ID int, @Timestamp timestamp OUTPUT, @ProjectId int, @Name varchar(MAX), @Description varchar(MAX), @Definition varchar(MAX), @IsPublic bit, @Author varchar(MAX) ) AS BEGIN UPDATE [Filter] SET [Name] = @Name ,[Description] = @Description ,[Definition] = @Definition ,[IsPublic] = @IsPublic ,[Author] = @Author ,[ProjectId] = @ProjectId WHERE [ID]=@ID -- Select computed columns into output parameters SELECT @Timestamp = [Timestamp] FROM dbo.Filter WHERE [ID] = @ID END GO CREATE PROCEDURE [dbo].[GetProjectSprints_4] @projectId int AS BEGIN SET NOCOUNT ON; SELECT DISTINCT S.[Id], S.[Name],S.[Goal], S.[Start],S.[EstimatedDuration], S.[Done], S.[ProjectId], S.[State],S.[DemoDate],S.[EstimatedStoryPoints],S.[FocusFactor],S.[Notes],S.[StartRemaining], S.[StartProgress], S.[MeetingRoom], S.[MeetingTime] FROM Sprint S JOIN Task T ON T.SprintId = S.Id JOIN Release R ON T.ReleaseId = R.Id OR T.ReleaseId IS NULL WHERE S.ProjectId = @projectId AND (R.IsArchived = 0 OR R.IsArchived IS NULL) ;WITH rowset AS ( SELECT DISTINCT S.[Id], S.[Name],S.[Goal], S.[Start],S.[EstimatedDuration], S.[Done], S.[ProjectId], S.[State],S.[DemoDate],S.[EstimatedStoryPoints],S.[FocusFactor],S.[Notes],S.[StartRemaining], S.[StartProgress], S.[MeetingRoom], S.[MeetingTime] FROM Sprint S JOIN Task T ON T.SprintId = S.Id JOIN Release R ON T.ReleaseId = R.Id OR T.ReleaseId IS NULL WHERE S.ProjectId = @projectId AND (R.IsArchived = 0 OR R.IsArchived IS NULL) ) SELECT [Id] ,[SprintId] ,[StoriesCount] ,[TasksCount] ,[EstimatedStoryPoints] ,[EstimatedDuration] ,[RemainingDuration] ,[Date] ,[Timestamp] ,[SprintState] ,[TimeSpent] FROM [SprintProgress] WHERE SprintId IN (SELECT Id FROM rowset) ORDER BY [Timestamp] END GO CREATE PROCEDURE [dbo].[GetStoryHistory] @StoryId int AS BEGIN SET NOCOUNT ON; SELECT [Id] ,[ObjectId] ,[ObjectType] ,[Changed] ,[PropertyName] ,[OldValue] ,[NewValue] ,[UserName] ,[ChangeType] FROM [EntityChangeLog] WHERE [ObjectType] = 'Task' AND [ObjectId] = @StoryId ORDER BY Changed DESC END GO CREATE PROCEDURE [dbo].[Task_Delete_3] ( @ID int, @SubtaskIds varchar(MAX) ) AS BEGIN UPDATE [dbo].[Task] SET [IsDeleted]=1 WHERE [ID]=@ID CREATE TABLE #TempList (SubtaskId int) DECLARE @SubtaskID varchar(10), @Pos int SET @SubtaskIds = LTRIM(RTRIM(@SubtaskIds))+ ',' SET @Pos = CHARINDEX(',', @SubtaskIds, 1) IF REPLACE(@SubtaskIds, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @SubtaskID = LTRIM(RTRIM(LEFT(@SubtaskIds, @Pos - 1))) IF @SubtaskID <> '' BEGIN INSERT INTO #TempList (SubtaskId) VALUES (CAST(@SubtaskID AS int)) END SET @SubtaskIds = RIGHT(@SubtaskIds, LEN(@SubtaskIds) - @Pos) SET @Pos = CHARINDEX(',', @SubtaskIds, 1) END END UPDATE tk SET [IsDeleted]=1 FROM [dbo].[Task] as tk --, #TempList as tl INNER JOIN #TempList as tl on tk.Id = tl.SubtaskId WHERE tk.ParentTaskId = @ID END GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspHubImpediment_Insert]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[uspHubImpediment_Insert] ( @ID int OUTPUT, @TaskId int, @UserId int, @Modified datetime, @Subject nvarchar(MAX), @State int, @ResponsibleId int, @SolvedDate datetime, @ProjectIntegrationId int, @DestinationId nvarchar(max) ) AS BEGIN INSERT INTO [dbo].[Impediment] ([TaskId] ,[UserId] ,[Modified] ,[Subject] ,[State] ,[ResponsibleId] ,[SolvedDate] ) VALUES ( @TaskId ,@UserId ,@Modified ,@Subject ,@State ,@ResponsibleId ,@SolvedDate ) -- Get the identity value SET @ID = SCOPE_IDENTITY() --musim aktualizovat SynchroHistory tak, aby mi neprisla spatna zmena od ScrumDesku DECLARE @ChangeTime datetime SELECT @ChangeTime = ChangeTime FROM [dbo].[SynchroHistory] WHERE [ProjectIntegrationId] = @ProjectIntegrationId AND [ObjectType]= 2 AND [SourceId] = @ID UPDATE [dbo].[SynchroHistory] SET [DestinationId] = @DestinationId, [SyncTime] = @ChangeTime WHERE [ProjectIntegrationId] = @ProjectIntegrationId AND [ObjectType]= 2 AND [SourceId] = @ID END' END GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspHubImpediment_Update]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[uspHubImpediment_Update] ( @ID int OUTPUT, @TaskId int, @UserId int, @Modified datetime, @Subject nvarchar(MAX), @State int, @ResponsibleId int, @SolvedDate datetime, @ProjectIntegrationId int, @DestinationId nvarchar(max) ) AS BEGIN UPDATE [dbo].[Impediment] SET [TaskId]=@TaskId ,[UserId]=@UserId ,[Modified]=@Modified ,[Subject]=@Subject ,[State]= @State ,[ResponsibleId] = @ResponsibleId ,[SolvedDate]= @SolvedDate WHERE [ID] = @ID --musim aktualizovat SynchroHistory tak, aby mi neprisla spatna zmena od ScrumDesku DECLARE @ChangeTime datetime SELECT @ChangeTime = ChangeTime FROM [dbo].[SynchroHistory] WHERE [ProjectIntegrationId] = @ProjectIntegrationId AND [ObjectType]= 2 AND [SourceId] = @ID UPDATE [dbo].[SynchroHistory] SET [DestinationId] = @DestinationId, [SyncTime] = @ChangeTime WHERE [ProjectIntegrationId] = @ProjectIntegrationId AND [ObjectType]= 2 AND [SourceId] = @ID END' END GO -- PRINT N'Checking existing data against newly created constraints'; ALTER TABLE [dbo].[Project] WITH CHECK CHECK CONSTRAINT [FK_Project_Project]; ALTER TABLE [dbo].[Release] WITH CHECK CHECK CONSTRAINT [FK_Release_Project]; ALTER TABLE [dbo].[ProjectUsers] WITH CHECK CHECK CONSTRAINT [FK_ProjectUsers_Project]; ALTER TABLE [dbo].[Task] WITH CHECK CHECK CONSTRAINT [FK_Task_Project]; ALTER TABLE [dbo].[Sprint] WITH CHECK CHECK CONSTRAINT [FK_Sprint_Project]; ALTER TABLE [dbo].[StoryTemplate] WITH CHECK CHECK CONSTRAINT [FK_StoryTemplate_Project]; ALTER TABLE [dbo].[ProjectIntegration] WITH CHECK CHECK CONSTRAINT [FK_ProjectIntegration_Project]; ALTER TABLE [dbo].[ProjectSettings] WITH CHECK CHECK CONSTRAINT [FK_ProjectSettings_Project]; ALTER TABLE [dbo].[Team] WITH CHECK CHECK CONSTRAINT [FK_Project_Team]; ALTER TABLE [dbo].[Filter] WITH CHECK CHECK CONSTRAINT [FK_Filter_Project]; GO GRANT INSERT,UPDATE,DELETE,SELECT ON [dbo].[Project] TO ScrumDeskUsers GRANT INSERT,UPDATE,DELETE,SELECT ON [dbo].[Filter] TO ScrumDeskUsers GRANT EXECUTE ON [dbo].[DeleteProject_2] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[LoadProjectTeams_3] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[LoadStory] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[LoginUser_3] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[PokerRoundUserVote_Insert] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[StoryTemplate_Update3] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[Task_Update_11] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[User_LogActivity_2] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[uspDuplicateAttachment] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[uspGetProjectSettings] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[uspHubSprint_Insert] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[uspHubStory_Select_2] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[Filter_Delete] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[Filter_Insert_1] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[Filter_Update_1] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[GetProjectSprints_4] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[GetStoryHistory] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[Task_Delete_3] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[uspHubImpediment_Insert] TO [ScrumDeskUsers] GRANT EXECUTE ON [dbo].[uspHubImpediment_Update] TO [ScrumDeskUsers] GO -- configure new version number update Settings set [Value]='5.0.2.0' where [Parameter]='DBVersion' GO