/// <summary>
/// Initializes a new instance of the DataViewModel class.
/// </summary>
public DataViewModel(MainViewModel parent, Family family)
{
_parent = parent;
_family = family;
FamilySubmitCommand = new RelayCommand(FamilySubmit, CanSubmitFamily);
BackUpCommand = new RelayCommand(BackUpSelected);
AddFamilyMemberCommand = new RelayCommand(AddFamilyMember);
DeleteSelectedCommand = new RelayCommand(DeleteSelectedMember, CanDeleteSelected);
if (_family.FamilyMembers.Count == 0)
{
// No default Family member, create them.
_family.AddFamilyMember(new Person("", "", PersonType.Child, 0.0));
}
_selectedFamilyMember = _family.FamilyMembers[0];
}
/// <summary>
/// Returns a list of all Families in the database. Does not include associated
/// family members
/// </summary>
/// <returns></returns>
public List<Family> GetFamilies()
{
List<Family> families = new List<Family>();
try
{
using (SqlCeConnection dbConnection = new SqlCeConnection(
"Data Source=\"SKDDatabase.sdf\""))
{
dbConnection.Open();
SqlCeCommand getFamilyCommand = new SqlCeCommand(
@"SELECT * FROM family", dbConnection);
SqlCeDataReader dbFamilyReader = getFamilyCommand.ExecuteReader();
while (dbFamilyReader.Read())
{
Family family = new Family
{
FamilyNumber = dbFamilyReader.IsDBNull(0) ? (int?) null : dbFamilyReader.GetInt32(0),
AssignedTime = dbFamilyReader.IsDBNull(1) ? (DateTime?) null : dbFamilyReader.GetDateTime(1),
NoShow = (dbFamilyReader.IsDBNull(2) ? (bool?) null : dbFamilyReader.GetByte(2) != 0),
CheckinTime = dbFamilyReader.IsDBNull(3) ? (DateTime?) null : dbFamilyReader.GetDateTime(3),
EmployeeAssignedBy = dbFamilyReader.IsDBNull(4) ? null : dbFamilyReader.GetString(4),
Guid = dbFamilyReader.IsDBNull(5) ? (Guid?) null : dbFamilyReader.GetGuid(5),
Notes = dbFamilyReader.IsDBNull(6) ? null : dbFamilyReader.GetString(6)
};
SqlCeCommand getPeopleCommand = new SqlCeCommand(
@"SELECT * FROM people INNER JOIN family ON family.fam_id = people.fam_id WHERE family.fam_id = " + family.FamilyNumber, dbConnection);
SqlCeDataReader dbPeopleReader = getPeopleCommand.ExecuteReader();
while (dbPeopleReader.Read())
{
Person person = new Person
{
FamilyId = dbPeopleReader.IsDBNull(0) ? (int?) null : dbPeopleReader.GetInt32(0),
FirstName = dbPeopleReader.IsDBNull(1) ? null : dbPeopleReader.GetString(1),
LastName = dbPeopleReader.IsDBNull(3) ? null : dbPeopleReader.GetString(3),
PersonType = dbPeopleReader.IsDBNull(4) ? (PersonType?) null : (PersonType) dbPeopleReader.GetInt16(4),
Weight = dbPeopleReader.IsDBNull(5) ? (double?) null : dbPeopleReader.GetDouble(5),
Guid = dbPeopleReader.IsDBNull(6) ? (Guid?) null : dbPeopleReader.GetGuid(6),
Notes = dbPeopleReader.IsDBNull(7) ? null : dbPeopleReader.GetString(7)
};
family.AddFamilyMember(person);
}
families.Add(family);
}
}
}
catch (Exception e)
{
Console.WriteLine("Unexpected error: " + e.Message);
}
return families;
}
public Family GetFamily(Person per)
{
if (!IsConnected) throw new NotConnectedException();
try
{
ICheckinHandler db = CreateChannel(_factory);
return db.GetFamily(per);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
}
private void AddFamilyMember()
{
Person newMember = new Person("Member", "New", PersonType.Guest, _family.FamilyNumber);
_family.AddFamilyMember(newMember);
List<Person> newList = new List<Person>(_family.FamilyMembers);
FamilyMembers = newList;
SelectedFamilyMember = newMember;
// RaisePropertyChanged("FamilyMembers");
}
public void AddFamilyMember(Person newMember)
{
FamilyMembers.Add(newMember);
}
/// <summary>
/// Initializes a new instance of the EditPersonDataViewModel class.
/// </summary>
public EditPersonDataViewModel(Person selectedFamilyMember)
{
_selectedFamilyMember = selectedFamilyMember;
}
private ReturnCode CheckInPerson(Person person, SqlCeConnection dbConnection)
{
ReturnCode status = ReturnCode.Success;
try
{
SqlCeCommand checkinCommand = new SqlCeCommand();
SqlCeParameter fname = new SqlCeParameter("@fnameVar", SqlDbType.NVarChar) {Value = person.FirstName};
SqlCeParameter lname = new SqlCeParameter("@lnameVar", SqlDbType.NVarChar) {Value = person.LastName};
SqlCeParameter type = new SqlCeParameter("@typeVar", SqlDbType.SmallInt)
{
Value = person.PersonType
};
SqlCeParameter weight = new SqlCeParameter("@weightVar", SqlDbType.Float) {Value = person.Weight};
SqlCeParameter notes = new SqlCeParameter("@notesVar", SqlDbType.NVarChar);
if (person.Notes == null)
{
notes.Value = DBNull.Value;
}
else
{
notes.Value = person.Notes;
}
// notes.Value = (person.notes != null ? person.notes : "NULL");
checkinCommand.Parameters.Add(fname);
checkinCommand.Parameters.Add(lname);
checkinCommand.Parameters.Add(type);
checkinCommand.Parameters.Add(weight);
checkinCommand.Parameters.Add(notes);
// Determine if new person
SqlCeCommand checkCommand = new SqlCeCommand
{
CommandText = @"SELECT COUNT(guid) AS num FROM people WHERE (guid = @varGuid)"
};
SqlCeParameter guid = new SqlCeParameter("@varGuid", SqlDbType.UniqueIdentifier) {Value = person.Guid};
checkCommand.Parameters.Add(guid);
checkCommand.Connection = dbConnection;
checkCommand.ExecuteResultSet(ResultSetOptions.Scrollable);
SqlCeDataReader dbRead = checkCommand.ExecuteReader();
dbRead.Read();
// new person, need to insert into the table
if (dbRead.GetInt32(0) == 0)
{
checkinCommand.CommandText = @"INSERT INTO people (guid, fam_id, first_name, last_name, person_type, weight, notes) VALUES ( @varGuid, @famIdVar, @fnameVar, @lnameVar, @typeVar, @weightVar, @notesVar)";
SqlCeParameter famIdParam = new SqlCeParameter("@famIdVar", SqlDbType.Int) {Value = person.FamilyId};
checkinCommand.Parameters.Add(famIdParam);
SqlCeParameter varGuid = new SqlCeParameter("@varGuid", SqlDbType.UniqueIdentifier)
{
Value = person.Guid
};
checkinCommand.Parameters.Add(varGuid);
}
else //person exists in table, just update the row
{
checkinCommand.CommandText = @"UPDATE people SET first_name = @fnameVar, last_name = @lnameVar, person_type = @typeVar, weight = @weightVar, notes = @notesVar WHERE guid = @guidVar";
SqlCeParameter guidParam = new SqlCeParameter("@guidVar", SqlDbType.UniqueIdentifier)
{
Value = person.Guid
};
checkinCommand.Parameters.Add(guidParam);
}
checkinCommand.Connection = dbConnection;
int rowsAffected = checkinCommand.ExecuteNonQuery();
if (rowsAffected != 1)
{
Console.WriteLine("More than 1 row affected by CheckInPerson");
status = ReturnCode.UnexpectedResults;
}
if (rowsAffected == 0)
{
Console.WriteLine("No rows affected by CheckInPerson");
status = ReturnCode.UnexpectedResults;
}
}
catch (Exception e)
{
Console.WriteLine("Unexpected error in CheckInPerson: " + e.Message);
status = ReturnCode.Fail;
}
return status;
}
public Family GetFamily(Person child)
{
List<Person> familyMembers = new List<Person>();
Family retValue = new Family();
try
{
using (SqlCeConnection dbConnection = new SqlCeConnection(
"Data Source=\"SKDDatabase.sdf\""))
{
dbConnection.Open();
// Determine the family id from the childs GUID
SqlCeCommand checkCommand = new SqlCeCommand
{
CommandText = @"SELECT fam_id AS fam_id FROM people WHERE (guid = @varGuid)"
};
SqlCeParameter guid = new SqlCeParameter("@varGuid", SqlDbType.UniqueIdentifier)
{
Value = child.Guid
};
checkCommand.Parameters.Add(guid);
checkCommand.Connection = dbConnection;
checkCommand.ExecuteResultSet(ResultSetOptions.Scrollable);
SqlCeDataReader dbRead = checkCommand.ExecuteReader();
dbRead.Read();
// new person, need to insert into the table
int famNum = dbRead.GetInt32(0);
SqlCeCommand familyCommand = new SqlCeCommand(
@"SELECT family.assigned_time, family.fam_id, people.first_name, people.last_name, people.person_type, people.weight,
people.notes, people.guid
FROM family INNER JOIN
people ON family.fam_id = people.fam_id
WHERE family.fam_id = " + famNum,
dbConnection);
SqlCeDataReader familyMemberReader = familyCommand.ExecuteReader();
while (familyMemberReader.Read())
{
if (retValue.AssignedTime == null)
{
if (!familyMemberReader.IsDBNull(0))
{
retValue.AssignedTime = familyMemberReader.GetDateTime(0);
}
retValue.FamilyNumber = familyMemberReader.GetInt32(1);
retValue.Guid = familyMemberReader.GetGuid(7);
}
Person familyMember = new Person(familyMemberReader.GetString(2), familyMemberReader.GetString(3),
(PersonType)familyMemberReader.GetInt16(4),
familyMemberReader.GetDouble(5))
{
FamilyId = familyMemberReader.GetInt32(1)
};
if (!familyMemberReader.IsDBNull(6))
{
familyMember.Notes = familyMemberReader.GetString(6);
}
familyMember.Guid = familyMemberReader.GetGuid(7);
familyMembers.Add(familyMember);
}
retValue.FamilyMembers = familyMembers;
}
}
catch (Exception e)
{
Console.WriteLine("Unexpected error in GetFamily: " + e.Message);
return null;
}
return retValue;
}
public List<Person> GetChildrenNotCheckedIn()
{
List<Person> listOfChildren = new List<Person>();
try
{
using (SqlCeConnection dbConnection = new SqlCeConnection(
"Data Source=\"SKDDatabase.sdf\""))
{
dbConnection.Open();
SqlCeCommand childCommand = new SqlCeCommand(
@"SELECT people.first_name, people.last_name, people.person_type, people.guid, family.fam_id
FROM family INNER JOIN
people ON family.fam_id = people.fam_id
WHERE (family.check_in_time IS NULL) AND (people.person_type = 0)",
dbConnection);
SqlCeDataReader childReader = childCommand.ExecuteReader();
while (childReader.Read())
{
Person child = new Person(childReader.GetString(0), childReader.GetString(1), (PersonType)childReader.GetInt16(2), (int?)childReader.GetInt32(4))
{
Guid = childReader.GetGuid(3)
};
listOfChildren.Add(child);
}
}
}
catch (Exception e)
{
Console.WriteLine("Unexpected error in GetChildrenNotCheckedIn: " + e.Message);
}
return listOfChildren;
}
public void NewChild()
{
Family newFam = new Family();
Person firstPerson = new Person("Test", "Member", PersonType.Child, 0.0) {Guid = Guid.NewGuid()};
newFam.AddFamilyMember(firstPerson);
CurrentView = new DataViewModel(this, newFam);
}
public void EditChild(Person child)
{
CurrentView = new DataViewModel(this, FamilyDatabase.GetFamily(child));
}
