public static void FetchBulkSmsUsernameAndPassword(Person currentPerson, out string username, out string password)
        {
            if (currentPerson.HasPermission(common.Permissions.SmsChurch) || currentPerson.HasPermission(common.Permissions.SmsGroupLeaders) || currentPerson.HasPermission(common.Permissions.SmsGroupMembers))
            {
                using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
                {
                    username = (from c in context.ChurchSmsProviders
                                where c.ChurchId == currentPerson.ChurchId
                                    && c.SmsProviderId == (int)SmsProviders.BulkSmsSouthAfrica
                                select c.Username)
                                .FirstOrDefault();

                    password = (from c in context.ChurchSmsProviders
                                where c.ChurchId == currentPerson.ChurchId
                                    && c.SmsProviderId == (int)SmsProviders.BulkSmsSouthAfrica
                                select c.Password)
                                .FirstOrDefault();

                    return;
                }
            }

            username = null;
            password = null;
        }

 public static string GetVisitorWelcomeLetter(int churchId)
 {
     using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
     {
         return context.ChurchEmailTemplates.FirstOrDefault(x => x.ChurchId == churchId && x.EmailTemplateId == (int)EmailTemplates.WelcomeVisitors).Template;
     }
 }

        public static string DeleteSite(Person currentPerson, int siteId)
        {
            if (currentPerson.HasPermission(Permissions.DeleteSite))
            {
                using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
                {
                    var siteToDelete = (from s in context.Sites
                                        where s.ChurchId == currentPerson.ChurchId
                                        && s.SiteId == siteId
                                        select s).FirstOrDefault();

                    if (siteToDelete == null)
                    {
                        return "Could not delete site";
                    }

                    //Remove all the people linked to this site
                    var peopleLinkedToSite = context.People.Where(p => p.SiteId == siteId);

                    foreach (var p in peopleLinkedToSite)
                    {
                        p.SiteId = null;
                        p.Changed = DateTime.Now;
                    }

                    context.Sites.DeleteObject(siteToDelete);

                    context.SaveChanges();
                    return "Site succesfully removed";
                }
            }
            return "Could not delete site";
        }

        public static List<StandardCommentViewModel> AddStandardComment(Person currentPerson, string standardComment)
        {
            using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                if (currentPerson.HasPermission(common.Permissions.AddEvent))
                {
                    var check = CheckToSeeIfTheCommentIsAlreadyThere(currentPerson, standardComment, context);

                    if (check == 0)
                    {
                        var newStandardComment = new StandardComment
                                               {
                                                   StandardComment1= standardComment,
                                                   ChurchId        = currentPerson.ChurchId
                                               };

                        context.StandardComments.AddObject(newStandardComment);
                        context.SaveChanges();
                    }
                }

                return (from e in context.StandardComments
                        where e.ChurchId == currentPerson.ChurchId
                        select new StandardCommentViewModel
                        {
                            StandardCommentId = e.StandardCommentId,
                            StandardComment = e.StandardComment1
                        }).ToList();
            }
        }

 public static int FetchDefaultRoleId(Person currentPerson)
 {
     using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
     {
         return context.Roles.Where(r => r.ChurchId == currentPerson.ChurchId).First().RoleId;
     }
 }

        public static IEnumerable<string> FetchChurchCellPhoneNos(Person currentPerson, bool search, string searchField, string searchString)
        {
            var validatedNumbers = new List<string>();
            using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                var personList = FetchChurchList(currentPerson, search, searchField, searchString, context);

                var cellPhoneList = (from p in personList
                                     join po in context.PersonOptionalFields
                                         on p.PersonId equals po.PersonId
                                     where po.OptionalFieldId == (int)OptionalFields.CellPhone
                                         && po.Value != null
                                     orderby p.Family.FamilyName, p.PersonId
                                     select po.Value)
                                    .Distinct()
                                    .ToList();

                foreach (var cellPhoneNo in cellPhoneList.Where(cellPhoneNo => cellPhoneNo != null && cellPhoneNo.Trim() != string.Empty).Where(cellPhoneNo => !validatedNumbers.Contains(cellPhoneNo)))
                {
                    validatedNumbers.Add(cellPhoneNo);
                }
            }

            return validatedNumbers;
        }

        public static void AddAttendanceEvents(HomeGroupEventViewModel hgEvent, oikonomosEntities context, int eventValue, string eventName, Person currentPerson)
        {
            OldEvent groupEvent = new OldEvent();
            groupEvent.Created = DateTime.Now;
            groupEvent.CreatedByPersonId = currentPerson.PersonId;
            groupEvent.ChurchId = currentPerson.ChurchId;

            //Check to see if it is not already in the database
            var groupCheck = (from e in context.OldEvents
                              where e.TableId == (int)Tables.Group
                              && e.Reference == hgEvent.GroupId
                              && e.EventDate == hgEvent.EventDate
                              && e.Description == eventName
                              select e).FirstOrDefault();

            if (groupCheck != null)
            {
                groupEvent = groupCheck;
            }
            else
            {
                context.OldEvents.AddObject(groupEvent);
            }

            groupEvent.TableId = (int)Tables.Group;
            groupEvent.Reference = hgEvent.GroupId;
            groupEvent.Description = eventName;
            groupEvent.EventDate = hgEvent.EventDate;
            groupEvent.Value = eventValue.ToString();
            groupEvent.EventVisibilityId = (int)EventVisibilities.GroupAdministrators;
            groupEvent.Changed = DateTime.Now;
            groupEvent.ChangedByPersonId = currentPerson.PersonId;
        }

        public static List<string> FetchChurchCellPhoneNos(Person currentPerson, bool search, string searchField, string searchString)
        {
            List<string> validatedNumbers = new List<string>();
            using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                var personList = FetchChurchList(currentPerson, search, searchField, searchString, context);

                var cellPhoneList = (from p in personList
                                     join po in context.PersonOptionalFields
                                         on p.PersonId equals po.PersonId
                                     where po.OptionalFieldId == (int)OptionalFields.CellPhone
                                         && po.Value != null
                                     orderby p.Family.FamilyName, p.PersonId
                                     select po.Value)
                                    .Distinct()
                                    .ToList();

                foreach (string cellPhoneNo in cellPhoneList)
                {
                    if (cellPhoneNo != null && cellPhoneNo.Trim() != string.Empty)
                    {
                        string intNo = Utils.ConvertCellPhoneToInternational(cellPhoneNo, currentPerson.Church.Country);
                        if (!validatedNumbers.Contains(intNo))
                        {
                            validatedNumbers.Add(intNo);
                        }
                    }
                }
            }

            return validatedNumbers;
        }

        public static JqGridData FetchPermissionsForRoleJQGrid(Person currentPerson, JqGridRequest request, int roleId)
        {
            using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                var permissions = (from p in context.Permissions
                              join pr in context.PermissionRoles
                              on p.PermissionId equals pr.PermissionId
                              join r in context.Roles
                              on pr.RoleId equals r.RoleId
                              where r.ChurchId == currentPerson.ChurchId
                                && (pr.RoleId == roleId)
                              select p);

                if (!currentPerson.HasPermission(Permissions.SystemAdministrator))
                {
                    permissions = permissions.Where(p => p.IsVisible == true);
                }

                int totalRecords = permissions.Count();

                switch (request.sidx)
                {
                    case "Permission":
                        {
                            if (request.sord.ToLower() == "asc")
                            {
                                permissions = permissions.OrderBy(p => p.Name).Skip((request.page - 1) * request.rows).Take(request.rows);
                            }
                            else
                            {
                                permissions = permissions.OrderByDescending(p => p.Name).Skip((request.page - 1) * request.rows).Take(request.rows);
                            }
                            break;
                        }
                }

                JqGridData peopleGridData = new JqGridData()
                {
                    total = (int)Math.Ceiling((float)totalRecords / (float)request.rows),
                    page = request.page,
                    records = totalRecords,
                    rows = (from p in permissions.AsEnumerable()
                            select new JqGridRow()
                            {
                                id = p.PermissionId.ToString(),
                                cell = new string[]
                                {
                                    p.PermissionId.ToString(),
                                    p.Name
                                }
                            }).ToArray()
                };

                return peopleGridData;
            }
        }

 public static void DeletePerson(int personId, Person currentPerson)
 {
     if(!currentPerson.HasPermission(Permissions.DeletePerson))
         return;
     using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
     {
         if (RemovePersonFromChurchSpecificTables(personId, currentPerson, context))
         {
             DeletePerson(personId, context);
         }
         context.SaveChanges();
     }
 }

        public static List<FamilyMemberViewModel> AddPersonToFamily(int familyId, int personId)
        {
            using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                var person = (from p in context.People
                              where p.PersonId == personId
                              select p).First();

                person.FamilyId = familyId;
                person.Changed = DateTime.Now;
                context.SaveChanges();

                return FetchFamilyMembers(personId, familyId, context);
            }
        }

 public static string ChangePassword(int personId, string currentPassword, string newPassword)
 {
     using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
     {
         var loggedOnPerson = CheckEmailPassword(personId, currentPassword, context);
         if (loggedOnPerson == null)
         {
             return "Invalid Password";
         }
         //Change password
         var passwordHash = FormsAuthentication.HashPasswordForStoringInConfigFile(newPassword, "sha1");
         loggedOnPerson.PasswordHash = passwordHash;
         loggedOnPerson.Changed = DateTime.Now;
         context.SaveChanges();
         return "Password succesfully changed";
     }
 }

 public static List<EventListModel> FetchCommentHistory(int personId, Person currentPerson)
 {
     using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
     {
         return (from e in context.OldEvents
                 where e.TableId == (int)Tables.Person
                 && e.Reference == personId
                 && e.CreatedByPersonId == currentPerson.PersonId
                 && e.Description == EventNames.Comment
                 orderby e.EventDate descending
                 select new EventListModel
                 {
                     EntityName = e.Description,
                     Description = e.Comments,
                     Date = e.EventDate
                 }).Take(5).ToList();
     }
 }

        public static List<GroupClassificationViewModel> AddGroupClassification(Person currentPerson, string groupClassification)
        {
            using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                if (currentPerson.HasPermission(common.Permissions.AddGroupClassification))
                {//Check to see if it is not already in the db
                    var check = (from g in context.GroupClassifications
                                 where g.ChurchId == currentPerson.ChurchId
                                 && g.Name == groupClassification
                                 select g).Count();
                    if (check == 0)
                    {
                        GroupClassification gc = new GroupClassification();
                        gc.Created = DateTime.Now;
                        gc.Changed = DateTime.Now;
                        gc.Name = groupClassification;
                        gc.ChurchId = currentPerson.ChurchId;
                        if (currentPerson.ChurchId == 3) //ebenezer
                        {
                            gc.GroupTypeId = (int)GroupTypes.LifeGroup;
                        }
                        else
                        {
                            gc.GroupTypeId = (int)GroupTypes.HomeGroup;
                        }

                        context.GroupClassifications.AddObject(gc);
                        context.SaveChanges();
                    }

                }

                return (from g in context.GroupClassifications
                        where g.ChurchId == currentPerson.ChurchId
                        select new GroupClassificationViewModel
                        {
                            GroupClassificationId = g.GroupClassificationId,
                            GroupClassification = g.Name
                        }).ToList();

            }
        }

        public static ChurchViewModel FetchChurch(string churchName)
        {
            using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                return (from c in context.Churches
                        where c.Name == churchName
                        select new ChurchViewModel
                        {
                            ChurchId = c.ChurchId,
                            ChurchName = c.Name,
                            SiteHeader = c.SiteHeader,
                            SiteDescription = c.SiteDescription,
                            BackgroundImage = c.BackgroundImage,
                            UITheme = c.UITheme,
                            GoogleSearchRegion = c.Province,
                            ShowFacebookLogin = c.ChurchOptionalFields.FirstOrDefault(co => co.OptionalFieldId == (int)OptionalFields.Facebook).Visible
                        }).FirstOrDefault();

            }
        }

        public static void AddPermissionsToRole(Person currentPerson, int roleId, List<int> permissionIds)
        {
            if (!currentPerson.HasPermission(Permissions.EditPermissions))
                return;
            using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                foreach (var permissionId in permissionIds)
                {
                    PermissionRole pr = new PermissionRole()
                    {
                        RoleId = roleId,
                        Changed = DateTime.Now,
                        Created = DateTime.Now,
                        PermissionId = permissionId
                    };

                    context.PermissionRoles.AddObject(pr);
                }

                context.SaveChanges();

            }
        }

        public static IEnumerable<PersonListViewModel> FetchChurchList(Person currentPerson, bool search, string searchField, string searchString)
        {
            using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                var personList = FetchChurchList(currentPerson, search, searchField, searchString, context);

                return (from p in personList
                        orderby p.Family.FamilyName, p.PersonId
                        select new PersonListViewModel
                        {
                            PersonId = p.PersonId,
                            FamilyId = p.FamilyId,
                            Firstname = p.Firstname,
                            Surname = p.Family.FamilyName,
                            HomePhone = p.Family.HomePhone,
                            CellPhone = p.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.CellPhone).Value,
                            WorkPhone = p.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.WorkPhone).Value,
                            Email = p.Email,
                            Site = p.Site == null ? string.Empty : p.Site.Name,
                            RelationshipId = context.PersonRelationships.Where(pr => pr.PersonRelatedToId == p.PersonId).OrderBy(pr => pr.RelationshipId).FirstOrDefault() == null ? 99 : context.PersonRelationships.Where(pr => pr.PersonRelatedToId == p.PersonId).OrderBy(pr => pr.RelationshipId).FirstOrDefault().RelationshipId
                        }).OrderBy(pp=>pp.Surname).ThenBy(pp=>pp.RelationshipId).ThenBy(pp=>pp.PersonId).ToList();
            }
        }

        public static List<string> FetchChurchEmailAddresses(Person currentPerson, bool search, string searchField, string searchString)
        {
            var validEmails = new List<string>();

            using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                var personList = FetchChurchList(currentPerson, search, searchField, searchString, context);

                var emailList = (from p in personList
                                 where p.Email != null
                                     && p.Email != string.Empty
                                 orderby p.Family.FamilyName, p.PersonId
                                 select p.Email)
                                .Distinct()
                                .ToList();

                foreach (var email in emailList.Where(email => Utils.ValidEmailAddress(email) && !validEmails.Contains(email)))
                {
                    validEmails.Add(email);
                }
            }

            return validEmails;
        }

        public static Person FetchPersonFromPublicId(string publicId)
        {
            using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                var person = (from p in context.People.Include("PersonChurches")
                              where p.PublicId == publicId
                              select p).FirstOrDefault();

                if (person == null)
                    return null;

                SetupPermissions(context, person, false);
                return person;
            }
        }

        public static void RemovePermissionsFromRole(Person currentPerson, int roleId, List<int> permissionIds)
        {
            if (!currentPerson.HasPermission(Permissions.EditPermissions))
                return;
            using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
            {
                var permissionRoles = (from p in context.PermissionRoles
                                       where p.RoleId == roleId
                                       && permissionIds.Contains(p.PermissionId)
                                       select p).ToList();
                if (permissionRoles != null)
                {
                    foreach (var permissionRole in permissionRoles)
                    {
                        context.DeleteObject(permissionRole);
                    }
                }

                context.SaveChanges();

            }
        }