CREATE procedure [dbo].[MssWebGetGlobalTasks]
    @inSysUserId int,
    @inTaskUserIds IntList readonly,
    @inStartDate date = null,
    @inEndDate date = null
as
begin

    declare @theToDoDateStart date
    declare @theToDoDateEnd date

    set @theToDoDateStart = isnull(@inStartDate, '1753-01-01')
    set @theToDoDateEnd = isnull(@inEndDate,'9999-12-31')

    -- These are all tasks that are assigned to the current user with full security access level.
    ;with UserOrderInfoAccessLevel as (
        select BranchId = SecProfileDetail.BranchPriKey,
        AccessLevel = case when SecProfileDetail.ManagerFlag = 1 then 3
            else SecProfileDetail.AccessPriKey end
        from UserAccess
        cross join SecModules
        inner join SecurityProfile on SecurityProfile.SecProfilePriKey = UserAccess.SecProfilePriKey
        inner join SecProfileDetail on SecProfileDetail.SecProfilePriKey = SecurityProfile.SecProfilePriKey
        and SecModules.ModulePriKey = SecProfileDetail.ModulePriKey
        where UserAccess.SysUserID= @inSysUserId and SecModules.Description ='Order Information' and SecModules.Active = 1
    ), TodoIdsAndSecAccessLevel as (
        select
            Id = Todo.ToDoID,
            SecurityAccessLevel = isnull(UserOrderInfoAccessLevel.AccessLevel,0),
            TaskDuedate = (case
            when Todo.Complete = 1 or Todo.NotApplicableFlag = 1 then Todo.DueDateWhenDischarged
            else Todo.DueDate end)
        from ToDo
        inner join Orders on Todo.OrderID = Orders.PriKey
        left outer join UserOrderInfoAccessLevel on UserOrderInfoAccessLevel.BranchId = Orders.BranchPriKey
        where DueDate between  @theToDoDateStart and @theToDoDateEnd  and
            Todo.TaskUserFID = @inSysUserId and
            Orders.OrderStatus != 'Cancelled' and
            Orders.OrderStatus != 'Voided'
        union
        -- These are all tasks that are assigned to the other users.
        select
            Id = Todo.ToDoID,
            SecurityAccessLevel = UserOrderInfoAccessLevel.AccessLevel,
            TaskDuedate = (case
            when Todo.Complete = 1 or Todo.NotApplicableFlag = 1 then Todo.DueDateWhenDischarged
            else Todo.DueDate end)
        from ToDo
        inner join @inTaskUserIds taskIds on taskIds.Item = Todo.TaskUserFID and ToDo.TaskUserFID <> @inSysUserId
        inner join Orders on Todo.OrderID = Orders.PriKey
        inner join UserOrderInfoAccessLevel on UserOrderInfoAccessLevel.BranchId = Orders.BranchPriKey
        where DueDate between  @theToDoDateStart and @theToDoDateEnd  and
            Orders.OrderStatus != 'Cancelled' and
            Orders.OrderStatus != 'Voided'
    )
    select
        top 1000
        Id = Todo.ToDoID,
        [Description] = Tasks.taskdescription,
        OrderId = Orders.priKey,
        OrderNo = case Orders.Archived when 1 then Orders.ArchivedOrderNo + ' [A]' else Orders.OrderNo end,
        CustomerName =  dbo.FormatFirstNameLastName( Orders.FirstName, Orders.LastName ),
        Identifier = Todo.Identifier,
        ProfileId = Todo.profileId,
        RolloutId = Todo.rolloutId,
        CompletedBy = dbo.GetSysuserName( Todo.CompletedBy, 1 ),
        Duedate = TodoIdsAndSecAccessLevel.TaskDuedate,
        OriginalDueDate = Todo.OriginalDueDate,
        CoordinatorId = Todo.TaskUserFID,
        CoordinatorName = dbo.GetSysuserName( Todo.TaskUserFID, 1 ),
        AssignedRoleId = Tasks.coordinatortypeid,
        AssignedRole = Coordinatortype.CoordinatorType,
        DateTimeCompleted = Todo.DateTimeCompleted,
        NotApplicableUserName = dbo.GetSysuserName( Todo.NotApplicableSysUserFID, 1 ),
        NotApplicableDateTime = Todo.NotApplicableDateTime,
        PriorityId = TaskPriority.TaskPriorityID,
        PriorityNumber = TaskPriority.PriorityNumber,
        PriorityDesc = TaskPriority.[Description],
        Note = ToDoNote.Notes,
        CreatedBy = dbo.GetSysuserName( Todo.CreatedBySysuserFID, 1 ),
        CreatedOn = Todo.DateTimeStamp,
        SecurityAccessLevel = TodoIdsAndSecAccessLevel.SecurityAccessLevel
    from TodoIdsAndSecAccessLevel
    inner join Todo on Todo.ToDoID = TodoIdsAndSecAccessLevel.Id
    inner join Orders on Todo.OrderID = Orders.PriKey
    inner join Tasks on Todo.TaskID = Tasks.TaskID
    left outer join ToDoNote on ToDoNote.ToDoFID = Todo.ToDoID
    left outer join Coordinatortype on Coordinatortype.coordinatortypeid = Tasks.coordinatortypeid
    left outer join DependencyDate dd on Tasks.DependencyDateID = dd.DependencyDateID
    left outer join TaskPriority on Todo.TaskPriorityFID = TaskPriority.TaskPriorityID
    order by isnull(TodoIdsAndSecAccessLevel.TaskDuedate,'9999-12-31'), TaskPriority.PriorityNumber asc
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetGlobalTasks] TO [MssExec]
GO