private Boolean DatabaseWrite(DataEntry inputEntry)
{
// Writes a given inputEntry to the Access Database
string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DatabaseLocation + ";Persist Security Info=False;";
string EmployeeInsertCommand = "INSERT INTO `Client Contact Information` (";
string CaseInsertCommand = "INSERT INTO Documentation (";
// Writing Insert Command strings
String[] EmployeeFields = new String[10] {
"Employment Number", "Last Name", "First Name", "Street Address", "City", "Province", "Postal Code", "Phone Number (Home)", "Phone Number (Work)", "Email"
}; // Consider replacing with reference // Also may differ from actual DB
for (int i = 0; i < EmployeeFields.Length; i++)
{
EmployeeInsertCommand += "`" + EmployeeFields[i] + "`";
if (i != EmployeeFields.Length - 1)
{
EmployeeInsertCommand += ",";
}
}
EmployeeInsertCommand += ") VALUES (";
if (!inputEntry.ExistingPerson)
{
for (int i = 0; i < EmployeeFields.Length; i++)
{
object entryValue = inputEntry.PersonalFields[EmployeeFields[i]];
if (entryValue is int)
{
EmployeeInsertCommand += entryValue.ToString();
}
else
{
EmployeeInsertCommand += "\"" + entryValue.ToString() + "\"";
}
if (i != EmployeeFields.Length - 1)
{
EmployeeInsertCommand += ",";
}
}
}
EmployeeInsertCommand += ")";
String[] CaseFields = new String[7] {
"Employee Number", "Status", "LTD Eligible", "Referral Recieved", "Sick Leave Start", "Hourly Salary", "Hours Worked/Day"
}; //Replace with reference probably //Ignores ID and non filled fields
for (int i = 0; i < CaseFields.Length; i++)
{
CaseInsertCommand += "`" + CaseFields[i] + "`";
if (i != CaseFields.Length - 1)
{
CaseInsertCommand += ",";
}
}
CaseInsertCommand += ") VALUES (";
for (int i = 0; i < CaseFields.Length; i++)
{
object entryValue = inputEntry.RTWFields[CaseFields[i]];
if (entryValue == null)
{
entryValue = "";
}
if (entryValue is int || entryValue is bool || entryValue is decimal)
{
CaseInsertCommand += entryValue.ToString();
}
else
{
CaseInsertCommand += "\"" + entryValue.ToString() + "\"";
}
if (i != CaseFields.Length - 1)
{
CaseInsertCommand += ",";
}
}
CaseInsertCommand += ")";
OleDbConnection DatabaseConnection = new OleDbConnection(ConnectionString);
OleDbCommand EmployeeCommand = new OleDbCommand(EmployeeInsertCommand, DatabaseConnection);
OleDbCommand CaseCommand = new OleDbCommand(CaseInsertCommand, DatabaseConnection);
// Attempts to Connect to the database
try
{
DatabaseConnection.Open();
// DB Access Here is the first table as there is only one because of the query
if (!inputEntry.ExistingPerson)
{
EmployeeCommand.ExecuteNonQuery();
}
CaseCommand.ExecuteNonQuery();
DatabaseConnection.Close();
return(true);
}
catch (Exception ex)
{
// Make sure to close DB on fail or success
DatabaseConnection.Close();
MessageBox.Show(ex.Message);
if (MessageBox.Show("Error occurred in Database Connect", "Retry?", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
{
return(DatabaseWrite(inputEntry));
}
else
{
return(false);
}
}
}
private void SubmitEntryButton_Click(object sender, EventArgs e)
{
// On submit button press all entered data is added as a DataEntry object to AddedEntries
DataEntry NewEntry = new DataEntry();
int test; //Used to test if the employee number is valid
if ((EmployeeNumberField.Text != "" && Int32.TryParse(EmployeeNumberField.Text, out test)) || (ExistingPerson && ExistingPersonReference != null))
{
// Potentially find a better way to fill these
if (ExistingPerson && ExistingPersonReference != null)
{
// Use Existing Person to fill instead of fields
NewEntry.PersonalFields["Employment Number"] = ExistingPersonReference.PersonalFields["Employment Number"];
NewEntry.PersonalFields["First Name"] = ExistingPersonReference.PersonalFields["First Name"];
NewEntry.PersonalFields["Last Name"] = ExistingPersonReference.PersonalFields["Last Name"];
NewEntry.PersonalFields["Street Address"] = ExistingPersonReference.PersonalFields["Street Address"];
NewEntry.PersonalFields["City"] = ExistingPersonReference.PersonalFields["City"];
NewEntry.PersonalFields["Province"] = ExistingPersonReference.PersonalFields["Province"];
NewEntry.PersonalFields["Postal Code"] = ExistingPersonReference.PersonalFields["Postal Code"];
NewEntry.PersonalFields["Phone Number (Home)"] = ExistingPersonReference.PersonalFields["Phone Number (Home)"];
NewEntry.PersonalFields["Phone Number (Work)"] = ExistingPersonReference.PersonalFields["Phone Number (Work)"];
NewEntry.PersonalFields["Email"] = ExistingPersonReference.PersonalFields["Email"];
NewEntry.RTWFields["Employee Number"] = ExistingPersonReference.PersonalFields["Employment Number"];
NewEntry.ExistingPerson = true;
}
else
{
NewEntry.PersonalFields["Employment Number"] = Int32.Parse(EmployeeNumberField.Text);
NewEntry.PersonalFields["First Name"] = FirstNameField.Text;
NewEntry.PersonalFields["Last Name"] = LastNameField.Text;
NewEntry.PersonalFields["Street Address"] = StreetAddressField.Text;
NewEntry.PersonalFields["City"] = CityField.Text;
NewEntry.PersonalFields["Province"] = ProvinceField.Text;
NewEntry.PersonalFields["Postal Code"] = PostalCodeField.Text;
NewEntry.PersonalFields["Phone Number (Home)"] = HomeNumberField.Text;
NewEntry.PersonalFields["Phone Number (Work)"] = WorkNumberField.Text;
NewEntry.PersonalFields["Email"] = EmailField.Text;
NewEntry.RTWFields["Employee Number"] = Int32.Parse(EmployeeNumberField.Text);
}
NewEntry.RTWFields["Status"] = StatusField.Text;
NewEntry.RTWFields["LTD Eligible"] = LTDEligibleField.Checked;
NewEntry.RTWFields["Referral Recieved"] = ReferralReceivedField.Value;
NewEntry.RTWFields["Sick Leave Start"] = StartDateField.Value;
// Because these are currency types it converts to decimal if possible otherwise defaults to 0 // Probably change to 0 on "" else error
decimal testDecimal;
if (Decimal.TryParse(DailyHoursWorkedField.Text, out testDecimal))
{
NewEntry.RTWFields["Hours Worked/Day"] = Decimal.Parse(DailyHoursWorkedField.Text);
}
else
{
NewEntry.RTWFields["Hours Worked/Day"] = 0m;
}
if (Decimal.TryParse(HourlySalaryField.Text, out testDecimal))
{
NewEntry.RTWFields["Hourly Salary"] = Decimal.Parse(HourlySalaryField.Text);
}
else
{
NewEntry.RTWFields["Hourly Salary"] = 0m;
}
NewEntry.ContainsCaseInfo = true;
NewEntry.ContainsPersonalInfo = true;
//Potentially add calculated fields, otherwise let database fill them
if (AddedEntries.Count == 0)
{
// Sets up option to publish
PublishButton.Visible = true;
}
AddedEntries.Add(NewEntry);
ClearFields();
}
else
{
if (ExistingPerson)
{
MessageBox.Show("Please select an employee");
}
else
{
MessageBox.Show("Entered Employee number must be a number");
}
}
}
// Database Access Functions
private Boolean DatabaseRead(String commandString)
{
// Connects to the access database and reads entries into MainDatabase
// Connect string determines the table to read or a custom command string
// Returns true on success false on failure
string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DatabaseLocation + ";Persist Security Info=False;";
string StringCommand;
switch (commandString)
{
case "Employee":
StringCommand = "SELECT * FROM `Client Contact Information`;";
break;
case "Case":
StringCommand = "SELECT * FROM Documentation;";
break;
default:
StringCommand = commandString;
break;
}
OleDbConnection DatabaseConnection = new OleDbConnection(ConnectionString);
OleDbCommand DatabaseCommand = new OleDbCommand(StringCommand, DatabaseConnection);
OleDbDataAdapter DatabaseAdapter = new OleDbDataAdapter(DatabaseCommand);
DataSet DatabaseSet = new DataSet();
// Attempts to Connect to the database
try
{
DatabaseConnection.Open();
DatabaseAdapter.Fill(DatabaseSet);
// DB Access Here is the first table as there is only one because of the query
DataRowCollection DatabaseRows = DatabaseSet.Tables[0].Rows;
if (commandString == "Employee")
{
foreach (DataRow SingleRow in DatabaseRows)
{
// Reads the values into a Data Entry - this is order dependent
DataEntry ReadEntry = new DataEntry();
String[] EmployeeFields = new String[] { "Employment Number", "Last Name", "First Name", "Street Address", "City", "Province", "Postal Code", "Phone Number (Home)", "Phone Number (Work)", "Email" }; // Consider replacing with reference
int SetLength;
if (SingleRow.ItemArray.Length < EmployeeFields.Length)
{
SetLength = SingleRow.ItemArray.Length;
}
else
{
SetLength = EmployeeFields.Length;
}
for (int i = 0; i < SetLength; i++)
{
ReadEntry.PersonalFields[EmployeeFields[i]] = SingleRow.ItemArray[i];
}
ReadEntry.ContainsPersonalInfo = true;
MainDatabase.Add(ReadEntry);
}
}
else if (commandString == "Case")
{
foreach (DataRow SingleRow in DatabaseRows)
{
// Reads the values into a Data Entry - this is order dependent
DataEntry ReadEntry = new DataEntry();
String[] CaseFields = new String[] { "ID", "Employee Number", "Status", "LTD Eligible", "Referral Recieved", "Sick Leave Start", "Sick Leave Expiry", "180 Days Follow-Up", "LTD Application Required",
"LTD Application Sent", "Employee Stat to GWL", "Benefits Sheet Required", "Day 160", "Day 181 + 3 Months", "Benefits Sheet Sent", "Benefits Sheet Recieved", "Accomodation Start Date",
"Return to Work", "Return to Work Date", "Return To Work Follow-Up", "RTW Follow-Up Complete", "Return To Work End Plan", "Accomodation Follow-Up", "Number of Days Absent", "Hourly Salary",
"Hours Worked/Day", "SL Cost/Day" }; // Consider replacing with reference
int SetLength;
if (SingleRow.ItemArray.Length < CaseFields.Length)
{
SetLength = SingleRow.ItemArray.Length;
}
else
{
SetLength = CaseFields.Length;
}
for (int i = 0; i < SetLength; i++)
{
ReadEntry.RTWFields[CaseFields[i]] = SingleRow.ItemArray[i];
}
ReadEntry.ContainsCaseInfo = true;
MainDatabase.Add(ReadEntry);
}
}
DatabaseConnection.Close();
return(true);
}
catch (Exception ex)
{
// DB should always close
DatabaseConnection.Close();
MessageBox.Show(ex.Message);
if (MessageBox.Show("Error occurred in Database Connect", "Retry?", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes)
{
return(DatabaseRead(commandString));
}
else
{
return(false);
}
}
}
