public static List <FamilyMember> forFamilyID(CMSDataContext cmsdb, CMSImageDataContext cmsidb, int familyID, int campus, DateTime date, bool returnPictureUrls)
{
List <FamilyMember> members = new List <FamilyMember>();
DataTable table = new DataTable();
string qMembers = @"SELECT *
FROM (SELECT
person.PeopleId AS id,
person.Name AS name,
ISNULL( person.Age, 0 ) AS age,
person.BDate AS birthday,
gender.Id AS genderID,
EmailAddress AS email,
CellPhone AS mobile
FROM dbo.People AS person
LEFT JOIN lookup.Gender AS gender ON person.GenderId = gender.Id
WHERE person.FamilyId = @familyID
UNION
SELECT
person.PeopleId AS id,
person.Name AS name,
ISNULL( person.Age, 0 ) AS age,
person.BDate AS birthday,
gender.Id AS genderID,
EmailAddress AS email,
CellPhone AS mobile
FROM dbo.PeopleExtra AS extra
INNER JOIN People AS person on person.PeopleId = extra.PeopleId
LEFT JOIN lookup.Gender AS gender ON person.GenderId = gender.Id
WHERE extra.Field = 'Parent'
AND extra.IntValue IN (SELECT person.PeopleId
FROM dbo.People AS person
WHERE person.FamilyId = @familyID)
) AS familyMembers
ORDER BY familyMembers.Age DESC, familyMembers.genderID" ;
using (SqlCommand cmd = new SqlCommand(qMembers, cmsdb.ReadonlyConnection() as SqlConnection)) {
SqlParameter parameter = new SqlParameter("familyID", familyID);
cmd.Parameters.Add(parameter);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(table);
}
foreach (DataRow row in table.Rows)
{
FamilyMember member = new FamilyMember();
member.populate(row);
member.loadPicture(cmsdb, cmsidb, returnPictureUrls);
member.loadGroups(cmsdb, campus, date);
members.Add(member);
}
return(members);
}
