public int GetIDAbove(int memID) //Get the id of person who above a specific member
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var query = (from Relationship in db.Relationships
where Relationship.person2ID == memID
select Relationship.person1ID).SingleOrDefault(); //Get the person1ID which indicates a father of a member
if (query == null)
{
return(memID); //If there's no id above, return that member's ID
}
else //Else query to have the grandfather
{
var query2 = (from Relationship in db.Relationships
where Relationship.person2ID == int.Parse(query.ToString())
select Relationship.person1ID).SingleOrDefault();
if (query2 == null)
{
return(int.Parse(query.ToString()));
}
else
{
return(int.Parse(query2.ToString()));
}
}
}
public List <Member> GetAllMemOfAParentage(int pID) //Get all member of a specific parentage
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
List <int> listID1 = new List <int>();
var query = from Relationship in db.Relationships
where Relationship.parentageID == pID
select Relationship.person1ID; //Get all person1ID of that parentage
foreach (int id1 in query)
{
listID1.Add(id1);
}
List <int> listID2 = new List <int>();
var query2 = from Relationship in db.Relationships
where Relationship.parentageID == pID
select Relationship.person2ID; //Get all person2ID of that parentage
foreach (int id2 in query2)
{
listID2.Add(id2);
}
List <int> ID = listID1.Union(listID2).ToList(); //Union 2 list to have all member ID of that parentage
List <Member> memList = new List <Member>();
foreach (int id in ID)
{
memList.Add(DAOMember.Instance.GetMemberByID(id));
}
return(memList);
}
public Parentage GetParentageByID(int pID) //Get a parentage from a specific ID
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var query = (from Parentage in db.Parentages
where Parentage.pID == pID
select Parentage).Single();
return(query);
}
public Member GetMemberByID(int memID)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var query = from Member in db.Members //Get member where id equal memID
where Member.memID == memID
select Member;
return(query.Single());;
}
public List <Member> searchMember(string memName)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var searchList = GetListMember();
var searchQuery = from Member in searchList
where Member.memName.Contains(memName)
select Member;
return(searchQuery.ToList());
}
public bool DeleteRelation(int p1ID, int p2ID) //Delete a specific relation satisfy p1ID and p2ID
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var delQuery = from Relationship in db.Relationships
where Relationship.person1ID == p1ID && Relationship.person2ID == p2ID || Relationship.person1ID == p2ID && Relationship.person2ID == p1ID
select Relationship;
db.Relationships.DeleteAllOnSubmit(delQuery);
db.SubmitChanges();
return(true);
}
public bool DeleteMember(int memID)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var delQuery = (from Member in db.Members
where Member.memID == memID
select Member).Single();
db.Members.DeleteOnSubmit(delQuery);
db.SubmitChanges();
return(true);
}
public bool DeleteMemberRelation(int memID) //Delete all relation of a member
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var delQuery = from Relationship in db.Relationships
where Relationship.person1ID == memID || Relationship.person2ID == memID
select Relationship;
db.Relationships.DeleteAllOnSubmit(delQuery);
db.SubmitChanges();
return(true);
}
public bool DeleteParentage(int pID)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var delQuery = from Parentage in db.Parentages
where Parentage.pID == pID
select Parentage;
db.Parentages.DeleteAllOnSubmit(delQuery);
db.SubmitChanges();
return(true);
}
public bool InsertParentage(int pID, string pName)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
Parentage newParentage = new Parentage();
newParentage.pID = pID;
newParentage.pName = pName;
db.Parentages.InsertOnSubmit(newParentage);
db.SubmitChanges();
return(true);
}
public List <Parentage> GetListParentage() //Get the list of parentage
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var query = from Parentage in db.Parentages
select Parentage;
List <Parentage> lstParentage = new List <Parentage>();
foreach (Parentage parentage in query)
{
lstParentage.Add(parentage);
}
return(lstParentage);
}
public bool InsertRelation(int mem1ID, int mem2ID, string relation, int pID)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
Relationship newRelation = new Relationship();
newRelation.person1ID = mem1ID;
newRelation.person2ID = mem2ID;
newRelation.relation = relation;
newRelation.parentageID = pID;
db.Relationships.InsertOnSubmit(newRelation);
db.SubmitChanges();
return(true);
}
public List <Member> GetListMember() //Get all member in the database
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var query = from Member in db.Members
orderby Member.memID
select Member;
List <Member> lstMember = new List <Member>();
foreach (var member in query)
{
lstMember.Add(member);
}
return(lstMember);
}
public DataTable GetAllRelationOfAFamily(int pID) //Get all relations of a parentage
{
DataTable allRelation = new DataTable();
FamilyManagementDataContext db = new FamilyManagementDataContext();
var selectQuery = from Relationship in db.Relationships
where Relationship.parentageID == pID
select Relationship;
foreach (var r in selectQuery)
{
allRelation.Rows.Add(r);
}
return(allRelation);
}
public bool Login(string userName, string passWord)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var result = db.Authentications.ToList();
foreach (var r in result)
{
if (r.username.Trim() == userName && r.password.Trim() == passWord)
{
return(true);
}
}
return(false);
}
public List <Member> GetChildList(int mem1Id)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var query = from Relationship in db.Relationships
where Relationship.person1ID == mem1Id
select Relationship.person2ID;
List <Member> childList = new List <Member>();
foreach (int id in query)
{
childList.Add(DAOMember.Instance.GetMemberByID(id));
}
return(childList);
}
public bool ModifyParentage(int pID, string pName)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var modQuery = (from Parentage in db.Parentages
where Parentage.pID == pID
select Parentage).Single();
if (modQuery != null)
{
modQuery.pID = pID;
modQuery.pName = pName;
db.SubmitChanges();
}
return(true);
}
public List <Parentage> searchParentage(string pName)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var searchList = GetListParentage();
foreach (Parentage parentage in searchList)
{
convertToUnSign(parentage.pName);
}
var searchQuery = from Parentage in searchList
where Parentage.pName.Contains(pName)
select Parentage;
return(searchQuery.ToList());
}
public bool ModifyRelation(int p1ID, int p2ID, string relation, int pID)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var modQuery = (from Relationship in db.Relationships
where Relationship.person1ID == p1ID && Relationship.person2ID == p2ID
select Relationship).Single();
if (modQuery != null)
{
modQuery.relation = relation;
modQuery.parentageID = pID;
db.SubmitChanges();
}
return(true);
}
public bool InsertMember(int memID, string name, string rustic, string gender, DateTime dob, DateTime dod, string curAdd)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
Member newMem = new Member();
newMem.memID = memID;
newMem.memName = name;
newMem.rustic = rustic;
newMem.gender = gender;
newMem.dob = dob;
newMem.dod = dod;
newMem.address = curAdd;
db.Members.InsertOnSubmit(newMem);
db.Members.Context.SubmitChanges();
return(true);
}
public bool ModifyMember(int memID, string name, string rustic, string gender, DateTime dob, DateTime dod, string curAdd)
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
var modQuery = (from Member in db.Members
where Member.memID == memID
select Member).Single();
if (modQuery != null)
{
modQuery.memName = name;
modQuery.memID = memID;
modQuery.rustic = rustic;
modQuery.gender = gender;
modQuery.dob = dob;
modQuery.dod = dod;
modQuery.address = curAdd;
db.SubmitChanges();
}
return(true);
}
public System.Data.Linq.Table <Relationship> GetListRelationship()
{
FamilyManagementDataContext db = new FamilyManagementDataContext();
return(db.Relationships);
}
