public void ChildSubmission(Family fam)
{
Person child = null;
foreach (Person per in fam.FamilyMembers)
{
if (per.Guid == Guid.Empty)
{
per.Guid = Guid.NewGuid();
}
if (per.PersonType == PersonType.Child)
{
child = per;
}
}
if (child == null)
{
MessageBox.Show("Cannot add this Family. One CHILD must exist in this Family.");
return;
}
fam.EmployeeAssignedBy = UserName;
FamilyDatabase.CheckInFamily(fam);
MessageBox.Show("This Family has been assigned Family #" + FamilyDatabase.GetFamily(child).FamilyNumber + ". Please let them know!");
BackToFamily();
}
public Flight(Family assignedFamily, Pilot pilot, double maxWeight, int numberOfSeats,
string notes)
{
AssignedFamily = assignedFamily;
AssignedPilot = pilot;
MaxWeight = maxWeight;
NumberOfSeats = numberOfSeats;
Notes = notes;
}
public Boolean CheckInFamily(Family fam)
{
if (!IsConnected) throw new NotConnectedException();
try
{
ICheckinHandler db = CreateChannel(_factory);
return db.CheckInFamily(fam) == ReturnCode.Success;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
throw;
}
}
/// <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 ReturnCode CheckInFamily(Family familyToCheckIn)
{
ReturnCode status = ReturnCode.Success;
try
{
using (SqlCeConnection dbConnection = new SqlCeConnection(
"Data Source=\"SKDDatabase.sdf\""))
{
dbConnection.Open();
SqlCeCommand checkinCommand = new SqlCeCommand {Connection = dbConnection};
SqlCeDataReader dbReader = null;
// If the family is a walk in
if (familyToCheckIn.AssignedTime == null)
{
checkinCommand.CommandText = @"INSERT INTO family (no_show, check_in_time, employee) VALUES (0, @curTime, @empVar )";
SqlCeParameter dateTimeParam = new SqlCeParameter("@curTime", SqlDbType.DateTime)
{
Value = DateTime.Now
};
SqlCeParameter employeeParam = new SqlCeParameter("@empVar", SqlDbType.NVarChar)
{
Value = familyToCheckIn.EmployeeAssignedBy
};
checkinCommand.Parameters.Add(dateTimeParam);
checkinCommand.Parameters.Add(employeeParam);
checkinCommand.ExecuteNonQuery();
checkinCommand.CommandText = @"SELECT MAX(fam_id) AS fam_id
FROM family";
dbReader = checkinCommand.ExecuteReader();
dbReader.Read();
}
else //They have an assigned time, so they pre-registered
{
checkinCommand.CommandText = @"UPDATE family SET check_in_time = @curTime, employee = @empVar, notes = @notesVar WHERE fam_id = @famVar";
SqlCeParameter dateTimeParam = new SqlCeParameter("@curTime", SqlDbType.DateTime)
{
Value = DateTime.Now
};
SqlCeParameter employeeParam = new SqlCeParameter("@empVar", SqlDbType.NVarChar)
{
Value = familyToCheckIn.EmployeeAssignedBy
};
SqlCeParameter notesParam = new SqlCeParameter("@notesVar", SqlDbType.NVarChar)
{
Value = (familyToCheckIn.Notes ?? "NULL")
};
SqlCeParameter famNumParam = new SqlCeParameter("@famVar", SqlDbType.Int)
{
Value = familyToCheckIn.FamilyNumber
};
checkinCommand.Parameters.Add(dateTimeParam);
checkinCommand.Parameters.Add(employeeParam);
checkinCommand.Parameters.Add(notesParam);
checkinCommand.Parameters.Add(famNumParam);
checkinCommand.ExecuteNonQuery();
}
// Now that we know the family exists in table, commit family members
foreach (Person p in familyToCheckIn.FamilyMembers)
{
if (dbReader != null) //New family, assign fam_id to each person
{
p.FamilyId = dbReader.GetInt32(0);
}
CheckInPerson(p, dbConnection);
}
foreach (Person p in familyToCheckIn.DeletedMembers)
{
SqlCeCommand deleteCommand = new SqlCeCommand
{
Connection = dbConnection,
CommandText = @"DELETE FROM people WHERE (guid = @varGuid)"
};
SqlCeParameter varGuid = new SqlCeParameter("@varGuid", SqlDbType.UniqueIdentifier)
{
Value = p.Guid
};
deleteCommand.Parameters.Add(varGuid);
deleteCommand.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Console.WriteLine("Unexpected error in CheckInFamily: " + 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 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);
}