/// <summary>
        /// Gets the leaderboard item for user identifier.
        /// </summary>
        /// <param name="employeeId">The user identifier.</param>
        /// <returns></returns>
        public async Task<LeaderboardItemDTO> GetLeaderboardItemForUserNameAsync(string userName)
        {
            using (var dataContext = new Entities())
            {
                dataContext.Database.Connection.Open();
                var leaderBoardItem = await (from emp in dataContext.Employees
                                             join eb in dataContext.EarnedBadges on emp.EmployeeId equals eb.EmployeeId into grp
                                             where emp.ADName == userName && emp.EmploymentEndDate == null
                                             select new LeaderboardItemDTO
                                             {
                                                 EmployeeId = emp.EmployeeId,
                                                 EmployeeFirstName = emp.FirstName,
                                                 EmployeeLastName = emp.LastName,
                                                 EmployeeADName = emp.ADName,
                                                 EmployeeLocation = emp.Location,
                                                 EmployeeDepartment = emp.Department,
                                                 EarnedBadges = grp.Select(b => new EarnedBadgeItemDTO()
                                                 {
                                                     Id = b.BadgeId,
                                                     Name = b.BadgeName,
                                                     Description = b.BadgeDescription,
                                                     Type = (Common.Enums.BadgeType)b.BadgeTypeId,
                                                     ImagePath = b.BadgePath,
                                                     Tagline = b.BadgeTagLine,
                                                     AwardDate = b.AwardDate,
                                                     AwardPoints = b.AwardAmount,
                                                     PaidOut = b.PaidOut,
                                                     BadgePriority = b.BadgePriority,
                                                     DisplayOnce = b.DisplayOnce
                                                 })
                                             }).SingleAsync();

                return leaderBoardItem;
            }
        }
        public async Task<CustomIdentityDTO> RetrieveIdentityAsync(string userName)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var employeeList = await (from t in ctx.Employees
                    where t.ADName == userName
                    select new CustomIdentityDTO
                    {
                        Id = t.EmployeeId,
                        ADName = t.ADName
                    }).ToArrayAsync();

                var employee = employeeList.SingleOrDefault();

                if (employee != null)
                {
                    var roles = from r in ctx.EmployeePermissions
                        where r.EmployeeId == employee.Id
                        select new
                        {
                            r.PermissionId
                        };

                    foreach (var role in roles)
                    {
                        ((List<string>) employee.Roles).Add(((Common.Enums.PermissionType) role.PermissionId).ToString());
                    }
                }
                return employee;
            }
        }
 /// <summary>
 /// Gets the leader board based on a search term.
 /// </summary>
 /// <param name="searchTerm">The search term.</param>
 /// <returns></returns>
 public async Task<IEnumerable<LeaderboardItemDTO>> SearchLeaderboardAsync(string searchTerm)
 {
     using (var dataContext = new Entities())
     {
         dataContext.Database.Connection.Open();
         var leaderBoardItems = await (from emp in dataContext.Employees
                                       join eb in dataContext.EarnedBadges on emp.EmployeeId equals eb.EmployeeId into grp
                                       where (emp.ADName.Contains(searchTerm) || emp.FirstName.Contains(searchTerm) || emp.LastName.Contains(searchTerm))
                                             && emp.EmploymentEndDate == null
                                       orderby emp.LastName, emp.FirstName
                                       select new LeaderboardItemDTO
                                       {
                                           EmployeeId = emp.EmployeeId,
                                           EmployeeFirstName = emp.FirstName,
                                           EmployeeLastName = emp.LastName,
                                           EmployeeADName = emp.ADName,
                                           EarnedBadges = grp.Select(b => new EarnedBadgeItemDTO()
                                           {
                                               Id = b.BadgeId,
                                               Name = b.BadgeName,
                                               Type = (Common.Enums.BadgeType)b.BadgeTypeId,
                                               ImagePath = b.BadgePath,
                                               Tagline = b.BadgeTagLine,
                                               AwardDate = b.AwardDate,
                                               AwardPoints = b.AwardAmount,
                                               PaidOut = b.PaidOut,
                                               BadgePriority = b.BadgePriority,
                                               DisplayOnce = b.DisplayOnce
                                           })
                                       }).ToArrayAsync();
         return leaderBoardItems;
     }
 }
        public EarnedBadgeItemDTO GetEarnedBadge(int badgeAwardId)
        {
            using (var ctx = new Entities())
            {
                BadgeAward badgeAward = ctx.BadgeAwards.SingleOrDefault(badges => badges.BadgeAwardId == badgeAwardId);
                if (badgeAward == null)
                { 
                    throw new NotFoundException(string.Format("Badge award with id {0} was not found", badgeAwardId));
                }
               
                var earnedBadge = new EarnedBadgeItemDTO
                {
                    AwardDate = badgeAward.AwardDate,
                    AwardPoints = badgeAward.Badge.BadgeAwardValueAmount,
                    BadgePriority = badgeAward.Badge.BadgePriority,
                    DisplayOnce = badgeAward.Badge.DisplayOnce,
                    EmployeeADName = badgeAward.Employee.ADName,
                    Id = badgeAward.BadgeAwardId,
                    ImagePath = badgeAward.Badge.BadgePath,
                    Name = badgeAward.Badge.BadgeName,
                    PaidOut = badgeAward.PaidOut,
                    Tagline = badgeAward.Badge.BadgeTagLine,
                    Type = (Common.Enums.BadgeType)badgeAward.Badge.BadgeTypeId                    
                };

                return earnedBadge;
            }
        }
        public CustomIdentityDTO SaveIdentity(AuthorizeLogOnDTO customIdentity)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var employee = ctx.Employees.SingleOrDefault(e => e.ADName == customIdentity.UserName);
                if (employee == null)
                {
                    employee = new Employee();
                    LoadEmployee(employee, customIdentity);
                    employee.AwardPayoutThreshold = EmployeeConstants.DefaultAwardPayoutThreshold;
                    ctx.Employees.Add(employee);
                }
                else
                {
                    LoadEmployee(employee, customIdentity);
                }
                ctx.SaveChanges();

                AttachAndSavePermission(ctx, employee.EmployeeId, Common.Enums.PermissionType.User);

                return new CustomIdentityDTO
                {
                    Id = employee.EmployeeId,
                    ADName = employee.ADName
                };
            }
        }
 public void SaveEarnedBadges(IQueryable<BadgeAwardDTO> badges)
 {
     using (var ctx = new Entities())
     {
         ctx.Database.Connection.Open();
         var badgeAwards = new List<BadgeAward>();
         foreach (var badge in badges)
         {
             var saveBadge = LoadData(badge);
             ctx.BadgeAwards.Add(saveBadge);
             badgeAwards.Add(saveBadge);
         }
         ctx.SaveChanges();
         foreach (var badgeAward in badgeAwards)
         {
             var queueItem = new QueueItem
             {
                 BadgeAwardId = badgeAward.BadgeAwardId,
                 QueueItemCreated = badgeAward.AwardDate
             };
             ctx.QueueItems.Add(queueItem);
         }
         ctx.SaveChanges();
     }
 }
        public QueueEventLogDTO Add(QueueEventLogDTO item)
        {
            using (Entities context = new Entities())
            {
                QueueEventLog addedItem = context.QueueEventLogs.Add(
                    new QueueEventLog
                    {
                        Message = item.Message,
                        QueueEventCreated = item.QueueEventCreated,
                        QueueEventId = item.QueueEventId,
                        BadgeAwardId = item.BadgeAwardId
                    });
                
                context.SaveChanges();

                return new QueueEventLogDTO
                {
                    Message = addedItem.Message,
                    QueueEventCreated = addedItem.QueueEventCreated,
                    QueueEventId = addedItem.QueueEventId,
                    QueueEventLogId = addedItem.QueueEventLogId,
                    BadgeAwardId = addedItem.BadgeAwardId
                };
            }
        }
        public async Task<IEnumerable<ApproveActivityItemDTO>> GetActivitiesToApproveForManagerAsync(int managerEmployeeId)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var activityList = await (from t in ctx.ActivitySubmissions
                                          join e in ctx.Employees on t.EmployeeId equals e.EmployeeId
                                          join a in ctx.Activities on t.ActivityId equals a.ActivityId
                                          where t.SubmissionStatusId == (int)ActivitySubmissionStatus.AwaitingApproval
                                          where (e.ApprovingManagerId1 == managerEmployeeId
                                          || e.ApprovingManagerId2 == managerEmployeeId)
                                          select new ApproveActivityItemDTO
                                          {
                                              SubmissionId = t.ActivitySubmissionId,
                                              SubmissionDate = t.SubmissionDate,
                                              ActivityId = t.ActivityId,
                                              ActivityDescription = a.ActivityDescription,
                                              ActivityName = a.ActivityName,
                                              ApprovedById = t.SubmissionApprovedById ?? 0,
                                              EmployeeId = t.EmployeeId,
                                              EmployeeADName = e.ADName,
                                              EmployeeFirstName = e.FirstName,
                                              EmployeeLastName = e.LastName,
                                              Status = (ActivitySubmissionStatus)t.SubmissionStatusId,
                                              SubmissionNotes = t.SubmissionDescription
                                          }).ToArrayAsync();

                return activityList;
            }
        }
        public async Task<ApproveBadgeItemDTO> GetBadgeToApproveByIdAsync(int badgeId)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var activityList = await(from t in ctx.Badges
                                         where t.BadgeStatusId == (int)BadgeStatus.AwaitingApproval
                                         where t.BadgeId == badgeId
                                         select new ApproveBadgeItemDTO
                                         {
                                             BadgeId = t.BadgeId,
                                             Description = t.BadgeDescription,
                                             Name = t.BadgeName,
                                             ImagePath = t.BadgePath,
                                             Tagline = t.BadgeTagLine,
                                             Type = (Common.Enums.BadgeType)t.BadgeTypeId,
                                             AwardValueAmount = t.BadgeAwardValueAmount,
                                             Created = t.BadgeCreated,
                                             ApprovedById = t.BadgeApprovedById ?? 0,
                                             ApprovedDate = t.BadgeApprovedDate,
                                             BadgeStatus = (BadgeStatus)t.BadgeStatusId
                                         }).ToArrayAsync();

                return activityList.Single();
            }
        }
 public async Task<IEnumerable<SubmittedActivityItemDTO>> GetSubmittedActivitiesForEmployeeIdByActivityIdAsync(int employeeId, int activityId, DateTime? startDate, DateTime? endDate)
 {
     using (var ctx = new Entities())
     {
         ctx.Database.Connection.Open();
         var badgeList = await(from s in ctx.ActivitySubmissions
                               join a in ctx.Activities on s.ActivityId equals a.ActivityId
                               where s.EmployeeId == employeeId
                               where a.ActivityId == activityId
                               where s.SubmissionDate >= (startDate.HasValue ? startDate.Value : DateTime.MinValue)
                               where s.SubmissionDate <= (endDate.HasValue ? endDate.Value : DateTime.MaxValue)
                               select new SubmittedActivityItemDTO
                               {
                                   Id = s.ActivitySubmissionId,
                                   ActivityId = s.ActivityId,
                                   ActivityName = a.ActivityName,
                                   ActivitySubmissionDate = s.SubmissionDate,
                                   ApprovedById = s.SubmissionApprovedById ?? 0,
                                   Status = (ActivitySubmissionStatus)s.SubmissionStatusId,
                                   SubmissionNotes = s.SubmissionDescription,
                                   EmployeeId = s.EmployeeId
                               }).ToArrayAsync();
         return badgeList;
     }
 }
 public BadgeAwardEditDTO Update(BadgeAwardEditDTO data)
 {
     using (var ctx = new Entities())
     {
         ctx.Database.Connection.Open();
         var saveBadgeAward = ctx.BadgeAwards.Where(ba => ba.BadgeAwardId == data.Id).Single();
         saveBadgeAward.AwardAmount = data.AwardAmount;
         ctx.SaveChanges();
     }
     return data;
 }
 public IEnumerable<string> GetActiveAdUsers()
 {
     using (var ctx = new Entities())
     {
         ctx.Database.Connection.Open();
         var userList = (from t in ctx.Employees
                           where !t.EmploymentEndDate.HasValue
                                  select t.ADName);
         return userList.ToList();
     }
 }
        public async Task<IEnumerable<ApproveActivityItemDTO>> GetActivitiesToApproveForManagerAsync(IApproveActivityCollectionCriteria criteria)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var activityList = await (from t in ctx.ActivitySubmissions
                                          join e in ctx.Employees on t.EmployeeId equals e.EmployeeId
                                          join a in ctx.Activities on t.ActivityId equals a.ActivityId
                                          where t.SubmissionStatusId == (int)ActivitySubmissionStatus.AwaitingApproval
                                          where (e.ApprovingManagerId1 == criteria.ManagerEmployeeId
                                          || e.ApprovingManagerId2 == criteria.ManagerEmployeeId)
                                          select new ApproveActivityItemDTO
                                          {
                                              SubmissionId = t.ActivitySubmissionId,
                                              SubmissionDate = t.SubmissionDate,
                                              ActivityId = t.ActivityId,
                                              ActivityDescription = a.ActivityDescription,
                                              ActivityName = a.ActivityName,
                                              ApprovedById = t.SubmissionApprovedById ?? 0,
                                              EmployeeId = t.EmployeeId,
                                              EmployeeADName = e.ADName,
                                              EmployeeFirstName = e.FirstName,
                                              EmployeeLastName = e.LastName,
                                              Status = (ActivitySubmissionStatus)t.SubmissionStatusId,
                                              SubmissionNotes = t.SubmissionDescription
                                          }).ToArrayAsync();

                foreach (var activity in activityList)
                {
                    var activityInfo = new ActivityInfoDTO
                    {
                        EmployeeId = activity.EmployeeId,
                        ActivityId = activity.ActivityId,
                        Status = ActivitySubmissionStatus.Approved
                    };
                    var badgeIds = criteria.AwardBadges.CreateBadges(activityInfo).Select(b => b.BadgeId).ToArray();

                    activity.ApproveActivityBadgeItemCollection = await (from t in ctx.Badges
                                                                         where badgeIds.Contains(t.BadgeId)
                                                                         select new ApproveActivityBadgeItemDTO
                                                                         {
                                                                             BadgeId = t.BadgeId,
                                                                             Name = t.BadgeName,
                                                                             Type = (Common.Enums.BadgeType)t.BadgeTypeId,
                                                                             ImagePath = t.BadgePath,
                                                                             BadgePriority = t.BadgePriority,
                                                                             AwardValueAmount = t.BadgeAwardValueAmount
                                                                         }).ToArrayAsync();
                }

                return activityList;
            }
        }
        public async Task<bool> ActivityNameExistsAsync(string name)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var activityExists = (await (from a in ctx.Activities 
                     where a.ActivityName == name select new {a.ActivityName})
                     .ToListAsync()).Any();

                return activityExists;
            }
        }
        public ActivityEditDTO Insert(ActivityEditDTO data)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var saveActivity = LoadData(data);
                ctx.Activities.Add(saveActivity);

                ctx.SaveChanges();
                data.Id = saveActivity.ActivityId;
            }
            return data;
        }
        public SubmitActivityDTO Insert(SubmitActivityDTO data)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var saveActivitySubmission = LoadData(data);
                ctx.ActivitySubmissions.Add(saveActivitySubmission);

                ctx.SaveChanges();
                data.Id = saveActivitySubmission.ActivitySubmissionId;
            }
            return data;
        }
 private void AttachAndSavePermission(Entities ctx, int employeeId, Common.Enums.PermissionType permissionType)
 {
     if (!ctx.EmployeePermissions.Any(ep => ep.EmployeeId == employeeId &&
         ep.PermissionId == (int)permissionType))
     {
         var permission = new EmployeePermission
         {
             EmployeeId = employeeId,
             PermissionId = (int)permissionType
         };
         ctx.EmployeePermissions.Add(permission);
         ctx.SaveChanges();
     }
 }
 public void Delete(int activityId)
 {
     using (var ctx = new Entities())
     {
         ctx.Database.Connection.Open();
         var deleteActivity = new Activity
         {
             ActivityId = activityId
         };
         ctx.Activities.Attach(deleteActivity);
         ctx.Activities.Remove(deleteActivity);
         ctx.SaveChanges();
     }
 }
        public QueueItemDTO Add(QueueItemDTO item)
        {
            using (Entities context = new Entities())
            {
                QueueItem addedItem = context.QueueItems.Add(new QueueItem 
                {
                    BadgeAwardId = item.BadgeAwardId,
                    QueueItemCreated = DateTime.Now
                });

                context.SaveChanges();

                return new QueueItemDTO(addedItem.QueueItemId, addedItem.BadgeAwardId, addedItem.QueueItemCreated);
            }
        }
 public AccountInfoEditDTO Update(AccountInfoEditDTO data)
 {
     using (var ctx = new Entities())
     {
         ctx.Database.Connection.Open();
         ctx.Configuration.ValidateOnSaveEnabled = false;
         var saveEmployee = LoadData(data);
         ctx.Employees.Attach(saveEmployee);
         var objectState = ((IObjectContextAdapter)ctx).ObjectContext.ObjectStateManager;
         objectState.GetObjectStateEntry(saveEmployee).SetModifiedProperty("AwardPayoutThreshold");
         
         ctx.SaveChanges();
     }
     return data;
 }
        public async Task<BadgeEditDTO> GetBadgeByIdAsync(int badgeEditId)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                
                var badgeList = await (from t in ctx.Badges.Include("BadgeActivities")
                                       where t.BadgeId == badgeEditId
                                       select t).ToListAsync();
                var badge = badgeList.Single();
                var returnValue = LoadReturnData(badge);

                return returnValue;
            }
        }
        /// <summary>
        /// Sends the activity notification.
        /// </summary>
        public void SendActivityNotifications()
        {
            using (Entities context = new Entities())
            {
                var activitySubmissions = context.ActivitySubmissions
                    .Where(sub => sub.ItemStatu.ItemStatusId == (int)ActivitySubmissionStatus.AwaitingApproval)
                    .ToList();

                var employees = context.Employees.ToList();
                var peopleToEmail = activitySubmissions
                    .Join(employees, asub => asub.EmployeeId, emp => emp.EmployeeId, (asub, emp) => new { asub = asub, emp = emp });

                var emailAddresses = new List<string>();
                foreach (var person in peopleToEmail)
                {
                    if (person.emp.ApprovingManagerId1.HasValue)
                    {
                        var manager = employees
                            .Where(emp => emp.EmployeeId == person.emp.ApprovingManagerId1.Value)
                            .SingleOrDefault();

                        if (manager != null)
                        {
                            emailAddresses.Add(manager.EmailAddress);
                        }
                    }

                    if (person.emp.ApprovingManagerId2.HasValue)
                    {
                        var manager = employees
                            .Where(emp => emp.EmployeeId == person.emp.ApprovingManagerId2.Value)
                            .SingleOrDefault();

                        if (manager != null)
                        {
                            emailAddresses.Add(manager.EmailAddress);
                        }
                    }
                }

                // TODO: figure out a better way to do this. Maybe using RazorEngine?
                emailAddresses = emailAddresses.Distinct().ToList();
                var emailSubject = String.Format(CultureInfo.CurrentCulture, ApplicationResources.ActivityNotificationSubject);
                var emailBody = String.Format(CultureInfo.CurrentCulture, ApplicationResources.ActivityNotificationBody);

                SendMessage(emailAddresses, emailSubject, emailBody);
            }
        }
 public ApproveBadgeItemDTO Update(ApproveBadgeItemDTO data)
 {
     using (var ctx = new Entities())
     {
         ctx.Database.Connection.Open();
         ctx.Configuration.ValidateOnSaveEnabled = false;
         var saveBadge = LoadData(data);
         ctx.Badges.Attach(saveBadge);
         var objectState = ((IObjectContextAdapter)ctx).ObjectContext.ObjectStateManager;
         objectState.GetObjectStateEntry(saveBadge).SetModifiedProperty("BadgeApprovedById");
         objectState.GetObjectStateEntry(saveBadge).SetModifiedProperty("BadgeStatusId");
         objectState.GetObjectStateEntry(saveBadge).SetModifiedProperty("BadgeApprovedDate");
         ctx.SaveChanges();
     }
     return data;
 }
        public QueueItemDTO Get(int id)
        {
            using (Entities context = new Entities())
            {
                QueueItem item = context
                    .QueueItems
                    .SingleOrDefault(i => i.QueueItemId == id);

                if (item == null)
                {
                    throw new NotFoundException(string.Format("QueueItem with Id {0} could not be found", id));
                }

                return new QueueItemDTO(item.QueueItemId, item.BadgeAwardId, item.QueueItemCreated);
            }
        }
        public async Task<IEnumerable<ActivityItemDTO>> GetAllActvitiesAsync()
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var activityList = await (from t in ctx.Activities
                                          select new ActivityItemDTO
                                          {
                                              Id = t.ActivityId,
                                              Name = t.ActivityName,
                                              BadgeIds = t.BadgeActivities.Select(ba => ba.BadgeId),
                                          }).ToArrayAsync();

                return activityList;
            }
        }
        public async Task<IEnumerable<ActivityEditDTO>> GetAllActvitiesAsync()
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var activityList = await (from t in ctx.Activities
                                          select new ActivityEditDTO
                    {
                        Id = t.ActivityId,
                        Name = t.ActivityName,
                        Description = t.ActivityDescription,
                        RequiresApproval = t.RequiresApproval,
                    }).ToArrayAsync();

                return activityList;
            }
        }
        public QueueItemDTO Peek()
        {
            using (Entities context = new Entities())
            {
                QueueItem item = context
                    .QueueItems
                    .OrderByDescending(i => i.QueueItemCreated)
                    .FirstOrDefault();
                
                if (item == null)
                {
                    return null;
                }

                return new QueueItemDTO(item.QueueItemId, item.BadgeAwardId, item.QueueItemCreated);
            }
        }
        public void Delete(int id)
        {
            using (Entities context = new Entities())
            {
                QueueEventLog item = context
                    .QueueEventLogs
                    .SingleOrDefault(e => e.QueueEventLogId == id);                    
                    
                if (item == null)
                {
                    throw new NotFoundException(string.Format("QueueEventLog with Id {0} could not be found so it was not deleted", id));
                }

                context.QueueEventLogs.Remove(item);
                context.SaveChanges();
            }
        }
        public IList<BadgeAwardDTO> GetAwardedBadgesForUser(int employeeId)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var badgeList = from t in ctx.BadgeAwards
                                where t.EmployeeId == employeeId
                                select new BadgeAwardDTO
                                {
                                    BadgeId = t.BadgeId,
                                    EmployeeId = t.EmployeeId,
                                    AwardAmount = t.AwardAmount,
                                    AwardDate = t.AwardDate
                                };

                return badgeList.ToList();
            }
        }
        public ActivityEditDTO Update(ActivityEditDTO data)
        {
            using (var ctx = new Entities())
            {
                ctx.Database.Connection.Open();
                var saveActivity = LoadData(data);
                ctx.Activities.Attach(saveActivity);
                var objectState = ((IObjectContextAdapter)ctx).ObjectContext.ObjectStateManager;
                objectState.GetObjectStateEntry(saveActivity).SetModifiedProperty("ActivityName");
                objectState.GetObjectStateEntry(saveActivity).SetModifiedProperty("ActivityDescription");
                objectState.GetObjectStateEntry(saveActivity).SetModifiedProperty("RequiresApproval");
                objectState.GetObjectStateEntry(saveActivity).SetModifiedProperty("CreateEmployeeId");

                ctx.SaveChanges();
                data.Id = saveActivity.ActivityId;
            }
            return data;
        }