public void loadItems()
{
children = dbConnection.selectAllChildren();
parents = dbConnection.selectAllParents();
employees = dbConnection.selectAllStaff();
contacts = dbConnection.selectAllContacts();
Child exampleChild1 = new Child();
Parent exampleParent1 = new Parent();
exampleParent1.FirstName = "Lewis";
exampleParent1.LastName = "Sharp";
exampleParent1.Title = "Mr";
exampleParent1.Gender = 'M';
exampleParent1.WorkPhone = "01234567890";
exampleParent1.HomePhone = "07704123874";
exampleParent1.Email = "*****@*****.**";
exampleChild1.ChildID = 12;
exampleChild1.FirstName = "Gemima";
exampleChild1.LastName = "Sharp";
exampleChild1.Gender = 'F';
exampleChild1.DOB = new DateTime(2008, 04, 15);
exampleChild1.FirstLanguage = "English";
exampleChild1.RoomAttending = "Teddy's Transformers";
exampleChild1.DateApplied = new DateTime(2009, 06, 17);
exampleChild1.DateLeft = new DateTime();
exampleChild1.Attendance = new bool[5] { true, false, false, true, true };
exampleChild1.ExtraDays = 2;
exampleChild1.Teas = 3;
exampleChild1.ParentsIDs.Add(exampleParent1.ParentID);
children.Add(exampleChild1);
parents.Add(exampleParent1);
}
public frmEditChild(Child childToEdit)
{
InitializeComponent();
dbConnection = new Database();
ChildToEdit = childToEdit;
editing = true;
txtFirstName.Text = ChildToEdit.FirstName;
txtLastName.Text = ChildToEdit.LastName;
txtExtra.Text = ChildToEdit.ExtraDays.ToString();
txtTeas.Text = ChildToEdit.Teas.ToString();
if (ChildToEdit.Gender == 'M' )
{
cmbGender.Text = "Male";
}
else if (ChildToEdit.Gender == 'F')
{
cmbGender.Text = "Female";
}
cmbLanguage.Text = ChildToEdit.FirstLanguage;
cmbRoom.Text = ChildToEdit.RoomAttending;
dtpDOB.Value = ChildToEdit.DOB;
if (ChildToEdit.DateLeft != new DateTime(0001, 1, 1, 0, 0, 0))
{
dtpLeaveDate.Value = ChildToEdit.DateLeft;
}
dtpStartDate.Value = ChildToEdit.DateApplied;
chk1.Checked = ChildToEdit.Attendance[0];
chk2.Checked = ChildToEdit.Attendance[1];
chk3.Checked = ChildToEdit.Attendance[2];
chk4.Checked = ChildToEdit.Attendance[3];
chk5.Checked = ChildToEdit.Attendance[4];
}
private Child constructChild()
{
Child newChild = new Child();
newChild.FirstName = txtFirstName.Text;
newChild.LastName = txtLastName.Text;
newChild.Gender = cmbGender.Text[0];
newChild.DOB = dtpDOB.Value;
newChild.FirstLanguage = cmbLanguage.Text;
newChild.RoomAttending = cmbRoom.Text;
newChild.DateApplied = dtpStartDate.Value;
newChild.DateLeft = dtpLeaveDate.Value;
newChild.ExtraDays = Convert.ToInt16(txtExtra.Text);
newChild.Teas = Convert.ToInt16(txtTeas.Text);
Control[] attendanceBoxes = this.Controls.Find("chk", false);
newChild.Attendance[0] = chk1.Checked;
newChild.Attendance[1] = chk2.Checked;
newChild.Attendance[2] = chk3.Checked;
newChild.Attendance[3] = chk4.Checked;
newChild.Attendance[4] = chk5.Checked;
return newChild;
}
/*----------------------UPDATES-----------------------*/
public bool updateChild(Child childToUpdate)
{
MySqlConnection connection = OpenConnection();
if (connection == null)
return false;
MySqlTransaction transaction = connection.BeginTransaction();
try
{
MySqlCommand updateCommand = new MySqlCommand(null, connection);
MySqlCommand idCommand = new MySqlCommand("SELECT LAST_INSERT_ID()", connection);
//updateCommand.Transaction = transaction;
// Create and prepare an SQL statement.
updateCommand.CommandText = @"UPDATE child
SET First_Name = @firstname, Last_Name = @lastname, Gender = @gender, DOB = @dob, First_Language = @firstlanguage, Room_Attending = @roomattending, Sibling = @sibling, Date_Applied = @dateapplied,Date_Left = @dateleft, Attendance = @attendance, Extra_Days = @extra, Teas = @teas, Medical_Information = @medical;";
//insertMedical(childToUpdate.MedicalInfo);
//int medicalID = (int)idCommand.ExecuteScalar();
updateCommand.Parameters.AddWithValue("@firstname", childToUpdate.FirstName);
updateCommand.Parameters.AddWithValue("@lastname", childToUpdate.LastName);
updateCommand.Parameters.AddWithValue("@gender", childToUpdate.Gender);
updateCommand.Parameters.AddWithValue("@dob", childToUpdate.DOB);
updateCommand.Parameters.AddWithValue("@firstlanguage", childToUpdate.FirstLanguage);
updateCommand.Parameters.AddWithValue("@roomattending", childToUpdate.RoomAttending);
//updateCommand.Parameters.AddWithValue("@sibling", chileT);
updateCommand.Parameters.AddWithValue("@dateapplied", childToUpdate.DateApplied);
updateCommand.Parameters.AddWithValue("@dateleft", null);
updateCommand.Parameters.AddWithValue("@attendance", 1/*childToUpdate.Attendance*/);
updateCommand.Parameters.AddWithValue("@extra", childToUpdate.ExtraDays);
updateCommand.Parameters.AddWithValue("@teas", childToUpdate.Teas);
updateCommand.Parameters.AddWithValue("@medical", 1/*medicalID*/);
// Prepare statement
updateCommand.Prepare();
Console.WriteLine("Executing: [ " + updateCommand.CommandText + "].");
updateCommand.ExecuteNonQuery();
//childID = (int)idCommand.ExecuteScalar();
//insertAttendance(childToUpdate);
//insertMedical(childToUpdate.MedicalInfo);
//insertMedical(childToUpdate.MedicalInfo);
//insertParent(parent);
//parentID = (int)idCommand.ExecuteScalar();
//linkParentChild(childToUpdate, parent);
//insertEmergencyContact(ec);
//contactID = (int)idCommand.ExecuteScalar();
//linkECChild(ec, childToUpdate);
//Perform transaction
transaction.Commit();
}
catch (MySqlException mysqle)
{
transaction.Rollback(); //Something went wrong, rollback
return false;
}
return (CloseConnection(connection)); //Successful or not
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!validateForm())
return;
if (ChildToAdd.ParentsIDs[0] == 0)
{
MessageBox.Show("Add a parent first");
}
if (editing)
{
ChildToEdit = constructChild();
//Update to Database
if (dbConnection.updateChild(ChildToAdd))
MessageBox.Show("Child updated successfully");
else
MessageBox.Show("Problem occurred while updating child");
}
else
{
ChildToAdd = constructChild();
//Add to Database
if (dbConnection.insertChild(ChildToAdd))
MessageBox.Show("Child added successfully");
else
MessageBox.Show("Problem occurred while adding child");
}
this.Close();
}
public void linkECChild(EmergencyContact ecToAdd, Child childToAdd)
{
MySqlConnection connection = OpenConnection();
if (connection == null)
return;
MySqlCommand insertCommand = new MySqlCommand(null, connection);
insertCommand.CommandText = "INSERT INTO child_has_emergency_contact VALUES (@contactID, @childID);";
insertCommand.Parameters.AddWithValue("@contactID", ecToAdd.ContactID);
insertCommand.Parameters.AddWithValue("@childID", childToAdd.ChildID);
Console.WriteLine("Executing: [ " + insertCommand.CommandText + "].");
insertCommand.Prepare();
insertCommand.ExecuteNonQuery();
CloseConnection(connection);
}
public void linkParentChild(Child childToAdd, Parent parent)
{
MySqlConnection connection = OpenConnection();
if (connection == null)
return;
MySqlCommand insertCommand = new MySqlCommand(null, connection);
insertCommand.CommandText = "INSERT INTO child_has_parent_guardian VALUES (@childID, @parentID);";
insertCommand.Parameters.AddWithValue("@childID", childToAdd.ChildID);
insertCommand.Parameters.AddWithValue("@parentID", parent.ParentID);
Console.WriteLine("Executing: [ " + insertCommand.CommandText + "].");
insertCommand.Prepare();
insertCommand.ExecuteNonQuery();
CloseConnection(connection);
}
//TODO: Entity Framework and Linq?
//TODO: Indexes
//TODO: Prepared Statements!
//TODO: Locks/Logs/Priveleges
//TODO: STORED PROCEDURES!
//TODO: VIEWS for levels of access!
//TODO: TRANSACTIONS!
//TODO: Joins instead of multiple selects?
/*--------------INSERTS/UPDATES---------------*/
public bool insertChild(Child childToAdd)
{
MySqlConnection connection = OpenConnection();
if (connection == null)
return false;
MySqlTransaction transaction = connection.BeginTransaction();
try
{
MySqlCommand insertCommand = new MySqlCommand(null, connection);
//updateCommand.Transaction = transaction;
// Create and prepare an SQL statement.
insertCommand.CommandText = @"INSERT INTO child (First_Name, Last_Name, Gender, DOB, First_Language, Room_Attending, Sibling, Date_Applied, Date_Left, Attendance, Extra_Days, Teas, Medical_Information)
VALUES (@firstname, @lastname, @gender, @dob, @firstlanguage, @roomattending, @sibling, @dateapplied, @dateleft, @attendance, @extra, @teas, @medical);";
insertCommand.Parameters.AddWithValue("@firstname", childToAdd.FirstName);
insertCommand.Parameters.AddWithValue("@lastname", childToAdd.LastName);
insertCommand.Parameters.AddWithValue("@gender", childToAdd.Gender);
insertCommand.Parameters.AddWithValue("@dob", childToAdd.DOB);
insertCommand.Parameters.AddWithValue("@firstlanguage", childToAdd.FirstLanguage);
insertCommand.Parameters.AddWithValue("@roomattending", childToAdd.RoomAttending);
//updateCommand.Parameters.Add(siblingParam);
insertCommand.Parameters.AddWithValue("@dateapplied", childToAdd.DateApplied);
insertCommand.Parameters.AddWithValue("@dateleft", null);
insertCommand.Parameters.AddWithValue("@attendance", lastAttendanceID);
insertCommand.Parameters.AddWithValue("@extra", childToAdd.ExtraDays);
insertCommand.Parameters.AddWithValue("@teas", childToAdd.Teas);
insertCommand.Parameters.AddWithValue("@medical", lastMedicalID);
// Prepare statement
Console.WriteLine("Executing: [ " + insertCommand.CommandText + "].");
insertCommand.Prepare();
Console.WriteLine("Executing: [ " + insertCommand.CommandText + "].");
insertCommand.ExecuteNonQuery();
//Remember the last medical id
MySqlCommand idCommand = new MySqlCommand("SELECT LAST_INSERT_ID()", connection);
object test = idCommand.ExecuteScalar();
try
{
lastChildID = int.Parse(test.ToString());
}
catch (ArgumentNullException)
{
}
catch (FormatException)
{
}
//insertAttendance(childToAdd);
//insertMedical(childToUpdate.MedicalInfo);
//insertMedical(childToUpdate.MedicalInfo);
//insertParent(parent);
//parentID = (int)idCommand.ExecuteScalar();
//linkParentChild(childToUpdate, parent);
//insertEmergencyContact(ec);
//contactID = (int)idCommand.ExecuteScalar();
//linkECChild(ec, childToUpdate);
//Perform transaction
//transaction.Commit();
}
catch (MySqlException mysqle)
{
transaction.Rollback(); //Something went wrong, rollback
return false;
}
return (CloseConnection(connection)); //Successful or not
}
public void insertAttendance(Child childToAdd)
{
MySqlConnection connection = OpenConnection();
if (connection == null)
return;
MySqlCommand insertCommand = new MySqlCommand(null, connection);
insertCommand.CommandText = @"INSERT INTO attendance VALUES (@child, @monday, @tuesday, @wednesday, @thursday, @friday);";
insertCommand.Parameters.AddWithValue("@child", childToAdd.ChildID);
insertCommand.Parameters.AddWithValue("@monday", childToAdd.Attendance[0]);
insertCommand.Parameters.AddWithValue("@tuesday", childToAdd.Attendance[1]);
insertCommand.Parameters.AddWithValue("@wednesday", childToAdd.Attendance[2]);
insertCommand.Parameters.AddWithValue("@thursday", childToAdd.Attendance[3]);
insertCommand.Parameters.AddWithValue("@friday", childToAdd.Attendance[4]);
Console.WriteLine("Executing: [ " + insertCommand.CommandText + "].");
insertCommand.Prepare();
insertCommand.ExecuteNonQuery();
//Remember the last attendance id
//Remember the last medical id
MySqlCommand idCommand = new MySqlCommand("SELECT LAST_INSERT_ID()", connection);
object test = idCommand.ExecuteScalar();
try
{
lastAttendanceID = int.Parse(test.ToString());
}
catch (ArgumentNullException)
{
}
catch (FormatException)
{
}
CloseConnection(connection);
}
private Child constructChild(MySqlDataReader childrenReader)
{
Child newChild = new Child();
newChild.ChildID = childrenReader.GetInt32("Child_Id");
newChild.FirstName = childrenReader.GetString("First_Name");
newChild.LastName = childrenReader.GetString("Last_Name");
newChild.Gender = childrenReader.GetChar("Gender");
newChild.DOB = childrenReader.GetDateTime("DOB");
newChild.FirstLanguage = childrenReader.GetString("First_Language");
newChild.RoomAttending = childrenReader.GetString("Room_Attending");
//newChild.Sibling = SafeGetInt(childrenReader, "Sibling");
newChild.DateApplied = childrenReader.GetDateTime("Date_Applied");
newChild.DateLeft = SafeGetDateTime(childrenReader, "Date_Left");
newChild.Attendance = constructAttendance(childrenReader);
newChild.ExtraDays = childrenReader.GetInt16("Extra_Days");
newChild.Teas = childrenReader.GetInt16("Teas");
newChild.MedicalInfo = constructMedical(childrenReader);
//newChild.ParentsIDs.Add(childrenReader.GetInt16(16));
//newChild.EmergencyContactsIDs.Add(childrenReader.GetInt16(16));
//Get parents
//foreach (int parentID in selectChildsParentIDs(newChild.ChildID))
// newChild.Parents.Add(constructParent(childrenReader, 16));
////Get Emergency Contacts
//foreach (int contactID in selectChildsContactIDs(newChild.ChildID))
// newChild.EmergencyContacts.Add(selectEmergencyContact(contactID), 30);
return newChild;
}
public frmChildReport(Child childtoDisplay,DataContainer data)
{
InitializeComponent();
child = childtoDisplay;
this.data = data;
}
public frmChildReport(Child childtoDisplay)
{
InitializeComponent();
child = childtoDisplay;
data = new DataContainer();
}
