public bool UpdateTaskPositionAndColumn(int userId, TaskModelWithPositionAndNewColumn task)
{
using IDbConnection db = new SqlConnection(_connectionString);
Debug.Assert(task.Id != null, "task.Id != null");
if (!IsAllowed(db, userId, (int)task.Id))
{
return(false);
}
Debug.Assert(task.NewColumnId != null, "task.NewColumnId != null");
if (!ColumnRepository.IsAllowed(db, userId, (int)task.NewColumnId))
{
return(false);
}
int maxTaskPosition = GetTasksCount(db, (int)task.NewColumnId);
if (task.Position < 0 || task.Position > maxTaskPosition)
{
return(false);
}
return(db.Query(
@"DECLARE @OldColumnId INT, @OldTaskPosition INT;
SET @OldColumnId =
(SELECT C.Id
FROM TaskTable AS T
JOIN ColumnTable AS C ON C.Id = T.ColumnId
WHERE T.Id = @taskId);
SET @OldTaskPosition =
(SELECT Position
FROM TaskTable
WHERE Id = @taskId);
UPDATE TaskTable
SET Position = T.Position - 1
FROM TaskTable AS T
JOIN ColumnTable AS C ON C.Id = T.ColumnId
WHERE C.Id = @OldColumnId
AND T.Position > @OldTaskPosition;
UPDATE TaskTable
SET Position = T.Position + 1
FROM TaskTable AS T
JOIN ColumnTable AS C ON C.Id = T.ColumnId
WHERE C.Id = @newColumnId
AND T.Position >= @newTaskPosition;
UPDATE TaskTable
SET Position = @newTaskPosition, ColumnId = @newColumnId
OUTPUT DELETED.Id
WHERE Id = @taskId;",
new { taskId = task.Id, newTaskPosition = task.Position, newColumnId = task.NewColumnId }).Any());
}
public int?CreateTask(int userId, TaskModelColumnLink task)
{
using IDbConnection db = new SqlConnection(_connectionString);
Debug.Assert(task.ColumnId != null, "task.ColumnId != null");
if (!ColumnRepository.IsAllowed(db, userId, (int)task.ColumnId))
{
return(null);
}
return(db.Query <int?>(@"INSERT INTO TaskTable(Name, Description, ColumnId, Position)
OUTPUT INSERTED.Id VALUES
(@taskName, @taskDescription, @columnId, @position);",
new { taskName = task.Name, taskDescription = task.Description, columnId = task.ColumnId, position = GetTasksCount(db, (int)task.ColumnId) }).FirstOrDefault());
}