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();
}
}
public static List<RoleViewModel> FetchRoles(Person currentPerson)
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
return Cache.SecurityRoles(context, currentPerson);
}
}
public static int SavePerson(PersonViewModel person, Person currentPerson)
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
if (!currentPerson.HasPermission(Permissions.EditChurchPersonalDetails))
{
if (currentPerson.HasPermission(Permissions.EditGroupPersonalDetails))
{
if (!CheckSavePermissionGroup(person, currentPerson, context)) { return person.PersonId; }
}
else if (currentPerson.HasPermission(Permissions.EditOwnDetails))
{
if (!CheckSavePermissionPersonal(person, currentPerson, context)) { return person.PersonId; }
}
else
{
return person.PersonId;
}
}
bool sendWelcomeEmail;
Church church;
Person personToSave;
GetPersonToSaveEntity(person, currentPerson, context, out sendWelcomeEmail, out church, out personToSave);
bool anniversaryHasChanged = SavePersonalDetails(person, currentPerson, context, personToSave);
SaveRole(person, currentPerson, context, personToSave);
bool addedToNewGroup = AddPersonToGroup(person, currentPerson, context, personToSave);
SaveContactInformation(person, personToSave);
SaveAddressInformation(person, personToSave);
UpdateRelationships(person, context, personToSave, anniversaryHasChanged);
context.SaveChanges();
personToSave = FetchPerson(personToSave.PersonId, context, currentPerson);
SaveWindowsLiveId(person, personToSave, context);
SendEmails(person, sendWelcomeEmail, church, personToSave);
EmailGroupLeader(person, currentPerson, context, church, personToSave, addedToNewGroup);
context.SaveChanges();
return personToSave.PersonId;
}
}
public static string ResetPassword(string emailAddress)
{
using (oikonomosEntities context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
var people = (from p in context.People
where p.Email == emailAddress
select p).ToList();
if (people.Count == 1)
{
var person = FetchPerson(people[0].PersonId, context, null);
string password = RandomPasswordGenerator.Generate(RandomPasswordOptions.AlphaNumeric);
person.PasswordHash = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "sha1");
context.SaveChanges();
return Email.SendResetPasswordEmail(person, password);
}
else
{
return "Your email address could not be found in the database. Please contact your church administrator for further assistance";
}
}
}
public static Person Login(string email, string password, ref string message)
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
var loggedOnPerson = CheckEmailPassword(email, password, context);
if (loggedOnPerson == null)
{
message= "Invalid Email or Password";
return null;
}
var fullName = loggedOnPerson.Firstname + " " + loggedOnPerson.Family.FamilyName;
message = "Welcome " + fullName + " from " + loggedOnPerson.Church.Name;
return loggedOnPerson;
}
}
public static PersonViewModel FetchPersonViewModel(int personId, Person currentPerson)
{
if (currentPerson.HasPermission(Permissions.EditChurchPersonalDetails) ||
currentPerson.HasPermission(Permissions.EditGroupPersonalDetails) ||
currentPerson.HasPermission(Permissions.EditOwnDetails))
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
try
{
CheckThatChurchIdsMatch(personId, currentPerson, context);
var familyId = (from p in context.People
where p.PersonId == personId
select p.FamilyId).FirstOrDefault();
var person = FetchPerson(personId, context, currentPerson);
if(person==null)
throw new ApplicationException("Invalid PersonId");
//SetupPermissions(context, person, currentPerson.Church);
var personViewModel = new PersonViewModel
{
PersonId = person.PersonId,
FamilyId = person.FamilyId,
Firstname = person.Firstname,
Surname = person.Family.FamilyName,
Email = person.Email,
DateOfBirth_Value = person.DateOfBirth,
Anniversary_Value = person.Anniversary,
HomePhone = person.Family.HomePhone,
CellPhone = person.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.CellPhone)==null ? string.Empty : person.PersonOptionalFields.First(c => c.OptionalFieldId == (int)OptionalFields.CellPhone).Value,
WorkPhone = person.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.WorkPhone) == null ? string.Empty : person.PersonOptionalFields.First(c => c.OptionalFieldId == (int)OptionalFields.WorkPhone).Value,
Skype = person.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.Skype) == null ? string.Empty : person.PersonOptionalFields.First(c => c.OptionalFieldId == (int)OptionalFields.Skype).Value,
Twitter = person.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.Twitter) == null ? string.Empty : person.PersonOptionalFields.First(c => c.OptionalFieldId == (int)OptionalFields.Twitter).Value,
FacebookId = person.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.Facebook) == null ? string.Empty : person.PersonOptionalFields.First(c => c.OptionalFieldId == (int)OptionalFields.Facebook).Value,
Occupation = person.Occupation,
Gender = person.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.Gender) == null ? string.Empty : person.PersonOptionalFields.First(c => c.OptionalFieldId == (int)OptionalFields.Gender).Value,
Address1 = person.Family.Address.Line1,
Address2 = person.Family.Address.Line2,
Address3 = person.Family.Address.Line3,
Address4 = person.Family.Address.Line4,
Lat = person.Family.Address.Lat,
Lng = person.Family.Address.Long,
HasUsername = person.Username != null,
FindFamily = false,
GroupId = 0,
Site = person.SiteId.HasValue ? person.Site.Name : "Select site...",
HeardAbout = person.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.HeardAbout) == null ? string.Empty : person.PersonOptionalFields.First(c => c.OptionalFieldId == (int)OptionalFields.HeardAbout).Value,
RoleId = person.RoleId,
RoleName = person.Role.Name
};
SetGroupId(personId, currentPerson, context, personViewModel);
personViewModel.FamilyMembers = FetchFamilyMembers(personId, familyId, context);
personViewModel.SecurityRoles = Cache.SecurityRoles(context, currentPerson);
return personViewModel;
}
catch (Exception ex)
{
Email.SendExceptionEmail(ex);
return null;
}
}
}
throw new Exception(ExceptionMessage.InvalidCredentials);
}
public static Person FetchPersonFromWindowsLiveId(string liveId)
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
var person = (from p in context.People
join po in context.PersonOptionalFields
on p.PersonId equals po.PersonId
where po.OptionalFieldId == (int)OptionalFields.WindowsLive
&& po.Value == liveId
select p).FirstOrDefault();
if (person == null)
return null;
SetupPermissions(context, person, false);
return person;
}
}
public static List<Person> FetchPersonFromName(string fullname, string firstname, string surname, string email)
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
var persons = (from p in context.People.Include("Family")
join f in context.Families
on p.FamilyId equals f.FamilyId
where p.Firstname == firstname
&& f.FamilyName == surname
&& p.Email == email
select p).ToList();
if (persons.Count == 0)
{
var fullnames = fullname.Split(' ');
if (fullnames.Length > 2)
{
//Try and search for the person using the first two names in the full name
//Takes out the maiden surname
firstname = fullnames[0];
surname = fullnames[1];
persons = (from p in context.People.Include("Family")
join f in context.Families
on p.FamilyId equals f.FamilyId
where p.Firstname == firstname
&& f.FamilyName == surname
&& p.Email == email
select p).ToList();
if (persons.Count == 0)
{
firstname = fullnames[0];
surname = fullnames[fullnames.Length-1];
persons = (from p in context.People.Include("Family")
join f in context.Families
on p.FamilyId equals f.FamilyId
where p.Firstname == firstname
&& f.FamilyName == surname
&& p.Email == email
select p).ToList();
}
}
}
foreach (var person in persons)
{
SetupPermissions(context, person, false);
}
return persons;
}
}
public static AutoCompleteViewModel[] FetchPersonAutoComplete(string term, Person currentPerson, bool wholeChurch)
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
var query = (from p in context.People.Include("PersonOptionalField").Include("Address")
from c in p.PersonChurches
join r in context.Roles
on c.RoleId equals r.RoleId
where c.ChurchId == currentPerson.ChurchId
&& r.ChurchId == currentPerson.ChurchId
select p);
if (term.Contains(" "))
{
var searchStrings = term.Split(' ');
var searchString1 = searchStrings[0];
var searchString2 = searchStrings[1];
query = query.Where(p => p.Firstname.Contains(searchString1) && p.Family.FamilyName.Contains(searchString2));
}
else
{
query = query.Where(p => p.Firstname.Contains(term) || p.Family.FamilyName.Contains(term));
}
if (!wholeChurch)
{
//Find out the persons role
if (currentPerson.HasPermission(Permissions.EditChurchPersonalDetails))
{
//No filter required
query = query.Take(12);
}
else if (currentPerson.HasPermission(Permissions.EditGroupPersonalDetails))
{
var grp = (from g in context.Groups
where g.LeaderId == currentPerson.PersonId
|| g.AdministratorId == currentPerson.PersonId
select g).FirstOrDefault();
if (grp == null)
{
return new AutoCompleteViewModel[0];
}
//Filter for the group
query = (from q in query
join pg in context.PersonGroups
on q.PersonId equals pg.PersonId
where pg.GroupId == grp.GroupId
select q).Take(12);
}
else
{
return new AutoCompleteViewModel[0];
}
}
return (from p in query.OrderBy(p => p.Firstname)
select new AutoCompleteViewModel
{
id = p.PersonId,
label = p.Firstname + " " + p.Family.FamilyName,
value = p.Firstname + " " + p.Family.FamilyName
}).ToArray();
}
}
public static Person FetchPerson(int personId)
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
return FetchPerson(personId, context, null);
}
}
public static JqGridData FetchPeopleJQGrid(Person currentPerson, JqGridRequest request, int roleId)
{
using (var context = new oikonomosEntities(ConfigurationManager.ConnectionStrings["oikonomosEntities"].ConnectionString))
{
var people = (from p in context.People.Include("Family").Include("PersonGroups")
from pc in p.PersonChurches
where pc.ChurchId == currentPerson.ChurchId
&& (pc.RoleId == roleId)
select p);
if (request._search)
{
foreach (var rule in request.filters.rules)
{
var ruleData = rule.data;
//If we use rule.data throughout we get some strange errors in the SQL that Linq generates
switch (rule.field)
{
case "Group":
{
var groupIds = (from g in context.Groups
where g.Name.Contains(ruleData)
select g.GroupId).ToList();
people = (from p in people
from pg in p.PersonGroups
where groupIds.Contains(pg.GroupId)
select p);
break;
}
case "Firstname":
{
people = (from p in people
where p.Firstname.Contains(ruleData)
select p);
break;
}
case "Surname":
{
people = (from p in people
where p.Family.FamilyName.Contains(ruleData)
select p);
break;
}
case "Date":
{
DateTime dStart;
if (DateTime.TryParse(ruleData, out dStart))
{
var dEnd = dStart.AddDays(1);
people = (from p in people
where p.Created >= dStart && p.Created < dEnd
select p);
}
break;
}
case "Site":
{
people = (from p in people
where p.Site.Name.Contains(ruleData)
select p);
break;
}
case "HomePhone":
{
people = (from p in people
where p.Family.HomePhone.Contains(ruleData)
select p);
break;
}
case "CellPhone":
{
people = (from p in people
from po in p.PersonOptionalFields
where (po.OptionalFieldId == (int)OptionalFields.CellPhone
&& po.Value.Contains(ruleData))
select p);
break;
}
case "Email":
{
people = (from p in people
where p.Email.Contains(ruleData)
select p);
break;
}
}
}
}
int totalRecords = people.Count();
switch (request.sidx)
{
case "Firstname":
{
people = request.sord.ToLower() == "asc" ? people.OrderBy(p => p.Firstname).Skip((request.page - 1) * request.rows).Take(request.rows) : people.OrderByDescending(p => p.Firstname).Skip((request.page - 1) * request.rows).Take(request.rows);
break;
}
case "Surname":
{
people = request.sord.ToLower() == "asc" ? people.OrderBy(p => p.Family.FamilyName).Skip((request.page - 1) * request.rows).Take(request.rows) : people.OrderByDescending(p => p.Family.FamilyName).Skip((request.page - 1) * request.rows).Take(request.rows);
break;
}
case "Date":
{
people = request.sord.ToLower() == "asc" ? people.OrderBy(p => p.Created).Skip((request.page - 1) * request.rows).Take(request.rows) : people.OrderByDescending(p => p.Created).Skip((request.page - 1) * request.rows).Take(request.rows);
break;
}
case "Group":
{
people = request.sord.ToLower() == "asc" ? people.OrderBy(p => p.PersonGroups.FirstOrDefault().Group.Name).Skip((request.page - 1) * request.rows).Take(request.rows) : people.OrderByDescending(p => p.PersonGroups.FirstOrDefault().Group.Name).Skip((request.page - 1) * request.rows).Take(request.rows);
break;
}
case "Site":
{
people = request.sord.ToLower() == "asc" ? people.OrderBy(p => p.Site.Name).Skip((request.page - 1) * request.rows).Take(request.rows) : people.OrderByDescending(p => p.Site.Name).Skip((request.page - 1) * request.rows).Take(request.rows);
break;
}
case "HomePhone":
{
people = request.sord.ToLower() == "asc" ? people.OrderBy(p => p.Family.HomePhone).Skip((request.page - 1) * request.rows).Take(request.rows) : people.OrderByDescending(p => p.Family.HomePhone).Skip((request.page - 1) * request.rows).Take(request.rows);
break;
}
case "Email":
{
people = request.sord.ToLower() == "asc" ? people.OrderBy(p => p.Email).Skip((request.page - 1) * request.rows).Take(request.rows) : people.OrderByDescending(p => p.Email).Skip((request.page - 1) * request.rows).Take(request.rows);
break;
}
}
var peopleGridData = new JqGridData()
{
total = (int)Math.Ceiling((float)totalRecords / request.rows),
page = request.page,
records = totalRecords,
rows = (from p in people.AsEnumerable()
select new JqGridRow()
{
id = p.PersonId.ToString(),
cell = new string[] {
p.PersonId.ToString(),
p.Firstname,
p.Family.FamilyName,
p.Family.HomePhone,
p.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.CellPhone)==null?"":p.PersonOptionalFields.FirstOrDefault(c => c.OptionalFieldId == (int)OptionalFields.CellPhone).Value,
p.Email,
p.Created.ToString("dd MMM yyyy"),
p.PersonGroups.Count > 1 ? "Multiple" : (p.PersonGroups.Count==0 ? "None" : p.PersonGroups.First().Group.Name),
p.Site== null ? string.Empty : p.Site.Name
}
}).ToArray()
};
return peopleGridData;
}
}
