private void mainScreenFormEmployee_Load(object sender, EventArgs e)
{
//Load event for the Main Screen. getQ is a Queries object that is used to provide all of the SQL queries for the
//Main Screen.
Queries getQ = new Queries();
try
{
//Connecting to the database and retrieving data.
employeeListView.View = View.Details;
RetrieveData rD = new RetrieveData(getQ.getMainScreenQuery());
//Call to retrieveData() actually sends query to database.
DataTable dataTable = rD.retrieveData();
//dataTable contains all of the information returned from the database. This for loop parses out that info
//and loads it into a ListViewItem called listData. Information is assigned to the correct "field" so that when
//the ListView is displayed it will show the info exactly as retrieved from the database.
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow dataRow = dataTable.Rows[i];
ListViewItem listData = new ListViewItem(dataRow["project_id"].ToString());
listData.SubItems.Add(dataRow["project_name"].ToString());
listData.SubItems.Add(dataRow["client_name"].ToString());
listData.SubItems.Add(dataRow["project_short_description"].ToString());
listData.SubItems.Add(dataRow["project_start_date"].ToString());
employeeListView.Items.Add(listData);
}
}
catch
{
MessageBox.Show("Database May have disconnected");
}
}
private void deactivateEmployeeButton_Click(object sender, EventArgs e)
{
//Deactivates a selected employee.
try
{
//Checks to ensure an employee is selected.
if (employeeListingListView.SelectedItems.Count == 0)
{
MessageBox.Show("Employee not selected, select an Employee and try again.");
}
//Loops through and deletes only the selected employee.
foreach (int i in employeeListingListView.SelectedIndices)
{
DsUtil utility = new DsUtil();
int employee = utility.getEmployeeId(employeeListingListView.Items[i].Text);
employeeListingListView.Items.Remove(employeeListingListView.Items[i]);
string query = "DELETE FROM software_eng_db_1.employee WHERE employee.employee_id = " + employee + ";";
RetrieveData rD = new RetrieveData(query);
if (rD.updateData())
{
//Shows a success message upon sucessful deletion from the database.
MessageBox.Show("Employee Deactivated.");
}
}
}
catch
{
MessageBox.Show("Database may have disconnected, or selected data is not available.");
}
}
//Translates Client Names into Client ID's. Takes a name, returns an ID.
public int getClientID(string clientName)
{
int clientID = 0;
string query = "SELECT customer.client_id FROM software_eng_db_1.customer "
+ "WHERE customer.client_name = '" + clientName + "';";
RetrieveData rD = new RetrieveData(query);
DataTable table = rD.retrieveData();
if (table.Rows.Count == 0)
{
string empQuery = "INSERT INTO software_eng_db_1.customer(client_name, address_id) "
+ "VALUES('" + clientName + "', 0);";
RetrieveData update = new RetrieveData(empQuery);
update.updateData();
DataTable table2 = rD.retrieveData();
DataRow dR2 = table2.Rows[0];
clientID = int.Parse(dR2["client_id"].ToString());
return(clientID);
}
else
{
DataRow dR = table.Rows[0];
clientID = int.Parse(dR["client_id"].ToString());
return(clientID);
}
}
private void employeeListingForm_Load(object sender, EventArgs e)
{
try
{
//Retrieves and populates Employee data from the database.
string query = "SELECT employee.employee_name, employee.job_title, employee.employee_skills FROM employee";
employeeListingListView.View = View.Details;
RetrieveData rD = new RetrieveData(query);
DataTable dataTable = rD.retrieveData();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow dataRow = dataTable.Rows[i];
ListViewItem listData = new ListViewItem(dataRow["employee_name"].ToString());
listData.SubItems.Add(dataRow["job_title"].ToString());
listData.SubItems.Add(dataRow["employee_skills"].ToString());
employeeListingListView.Items.Add(listData);
}
}
catch
{
MessageBox.Show("Database may have disconnected, or selected data is not available.");
}
}
private void applyChangesButton_Click(object sender, EventArgs e)
{
//This button is used for two different things depending on the context in which it is clicked. The "handler" variable
//is responsible for providing context as to whether this screen is creating or editing a project. Once clicked, the
//buton methods will either create a new project or edit an existing one based on the current setting in the ProjectHandler.cs
//class.
DsUtil utility = new DsUtil();
handler = new ProjectHandler(DsUtil.formID);
int project = DsUtil.selectedIndex;
string projectName = projectNameTextBox.Text;
int clientID = utility.getClientID(clientNameTextBox.Text);
string shortDesc = shortDescTextBox.Text;
DateTime date = dateTimePicker.Value;
bool flag = false;
bool flag1 = false;
if (projectName != "" && clientID != 0 && shortDesc != "")
{
if (handler.getID() == 0)
{
//Create Project
RetrieveData update = new RetrieveData();
flag = update.createProject(projectName, shortDesc, date, clientID);
}
else
{
//Edit Project
RetrieveData edit = new RetrieveData();
flag1 = edit.editProject(projectName, shortDesc, date, clientID);
}
if (flag)
{
MessageBox.Show("Project created successfully.");
}
if (flag1)
{
MessageBox.Show("Project edited successfully.");
}
}
else
{
MessageBox.Show("Data missing, please ensure you have filled out all required fields.");
}
}
private void createEmployeeButton_Click(object sender, EventArgs e)
{
//Set variables to user input.
int addressID = 0;
int wageID = 0;
string fullName = fullNameTextBox.Text;
string jobTitle = jobTitleTextBox.Text;
string skill = skillTextBox.Text;
int trueCheck = 1;
//Check for full-time status
if (fullTimeCheckBox.Checked == true)
{
trueCheck = 1;
}
else
{
trueCheck = 0;
}
//If the required textboxes are not blank...
if (fullName != "" && jobTitle != "" && skill != "")
{
try
{
RetrieveData rD = new RetrieveData();
//...Attempt to create the new employee.
if (rD.insertEmployee(fullName, trueCheck, skill, addressID, wageID, jobTitle))
{
//If successful, display message.
MessageBox.Show("Successfully created Employee.");
}
else
{
//If unsuccessful, also display message.
MessageBox.Show("Failed to create Employee.");
}
}
catch
{
}
}
}
//Translates Employee Names into Employee ID's. Takes a Name, returns an ID.
public int getEmployeeId(string employeeName)
{
string quary = "SELECT employee.employee_id FROM software_eng_db_1.employee " +
"WHERE employee.employee_name = '" + employeeName + "';";
int employeeID = -1;
RetrieveData rD = new RetrieveData(quary);
DataTable table = rD.retrieveData();
if (table.Rows.Count == 0)
{
return(employeeID);
}
else
{
DataRow dR = table.Rows[0];
employeeID = int.Parse(dR["employee_id"].ToString());
return(employeeID);
}
}
//Translates Employee ID's into Employee Names. Takes an ID, returns a Name.
public string getEmployeeName(int employeeId)
{
string quary = "SELECT employee.employee_name FROM software_eng_db_1.employee " +
"WHERE employee.employee_id = " + employeeId + ";";
string employeeName = "";
RetrieveData rD = new RetrieveData(quary);
DataTable table = rD.retrieveData();
if (table.Rows.Count == 0)
{
return(employeeName = "No data returned, input invalid.");
}
else
{
DataRow dR = table.Rows[0];
employeeName = dR["employee_name"].ToString();
return(employeeName);
}
}
//Translates Material Names into Material ID's. Takes a Name, returns an ID.
public int getMaterialId(string materialName)
{
string query = "SELECT material.material_id FROM software_eng_db_1.material "
+ "WHERE material.material_name = '" + materialName + "';";
int materialID = 0;
RetrieveData rD = new RetrieveData(query);
DataTable table = rD.retrieveData();
if (table.Rows.Count == 0)
{
return(materialID);
}
else
{
DataRow dR = table.Rows[0];
materialID = int.Parse(dR["material_id"].ToString());
return(materialID);
}
}
private void enterTaskButton_Click(object sender, EventArgs e)
{
DsUtil utility = new DsUtil();
//Set variables to user input
int project = DsUtil.selectedIndex;
string taskDesc = taskNameTextBox.Text;
int hours = int.Parse(hoursTextBox.Text);
int completedBy = utility.getEmployeeId(completedTextBox.Text);
int materialID = utility.getMaterialId(materialNameTextBox.Text);
bool flag = false;
try
{
//Create SQL query.
string query = "INSERT INTO software_eng_db_1.task (task_description, task_hours_to_complete, employee_id, project_id, material_id)"
+ " VALUES('" + taskDesc + "', " + hours + ", " + completedBy + ", " + project + ", " + materialID + ");";
//Send query to data retrieval class.
RetrieveData rD = new RetrieveData(query);
if (rD.updateData())
{
//If update is successful, set flag.
flag = true;
}
if (flag)
{
MessageBox.Show("New Task created.");
}
}
catch
{
MessageBox.Show("Database may have disconnected, or selected data is not available.");
}
}
//Used for the "Select Project" button to both assign the correct index for the Item selected in the ListView,
//but also to send the correct project code in the SQL query to the database. Needed because as projects are added,
//deleted, and assigned other codes, the AUTO_INCREMENT setting on the Primary Key in the mySQL database does not
//update to reflect newly unassigned primary keys.
public void assignIndex(int index)
{
string query = "SELECT project.project_id FROM software_eng_db_1.project;";
RetrieveData utility = new RetrieveData(query);
DataTable table = utility.retrieveData();
string[] projArray;
projArray = new string[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
projArray[i] = row["project_id"].ToString();
}
for (int j = 0; j < table.Rows.Count; j++)
{
if (j == index)
{
selectedIndex = int.Parse(projArray[j]);
}
}
}
private void projectScreenForm_Load(object sender, EventArgs e)
{
try
{
//Calculates the Hours Worked for a given project based off the sum of Hours to Complete for the tasks for
//that project.
int project = DsUtil.selectedIndex;
getQ = new Queries(DsUtil.selectedIndex);
RetrieveData update = new RetrieveData(getQ.getHoursWorkedQuery());
bool flag = false;
flag = update.updateData();
}
catch
{
MessageBox.Show("Hours computation failed.");
}
try
{
//Retrieves the same Project Info as listed on the Main Screen from the database and populates it on the
//Project Screen
int project = DsUtil.selectedIndex;
getQ = new Queries(DsUtil.selectedIndex);
pScreenDetailsListView.View = View.Details;
RetrieveData rD = new RetrieveData(getQ.getEditProjectQueryOne());
DataTable dataTable = rD.retrieveData();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow dataRow = dataTable.Rows[i];
ListViewItem listData = new ListViewItem(dataRow["project_id"].ToString());
listData.SubItems.Add(dataRow["project_name"].ToString());
listData.SubItems.Add(dataRow["client_name"].ToString());
listData.SubItems.Add(dataRow["project_short_description"].ToString());
listData.SubItems.Add(dataRow["project_start_date"].ToString());
pScreenDetailsListView.Items.Add(listData);
}
}
catch
{
MessageBox.Show("Database may have disconnected, or selected data is not available.");
}
try
{
//Retrieves and populates Assigned Employees and Materials fdor a given project.
DsUtil utility = new DsUtil();
int project = DsUtil.selectedIndex;
getQ = new Queries(DsUtil.selectedIndex);
pScreenEmpListView.View = View.Details;
RetrieveData rD = new RetrieveData(getQ.getEditProjectQueryTwo());
DataTable dataTable = rD.retrieveData();
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow dataRow = dataTable.Rows[i];
ListViewItem listData = new ListViewItem(utility.getEmployeeName((int)dataRow["employee_id"]));
listData.SubItems.Add(dataRow["hours_worked"].ToString());
listData.SubItems.Add(dataRow["material_name"].ToString());
listData.SubItems.Add(dataRow["material_price"].ToString());
pScreenEmpListView.Items.Add(listData);
}
}
catch
{
MessageBox.Show("Assigned employees failed to load.");
}
try
{
//Retrieves and populates Task and related Employee information for a given project.
DsUtil utility = new DsUtil();
int project = DsUtil.selectedIndex;
getQ = new Queries(DsUtil.selectedIndex);
pScreenTaskListView.View = View.Details;
RetrieveData rD2 = new RetrieveData(getQ.getEditProjectQueryThree());
DataTable dataTable2 = rD2.retrieveData();
for (int i = 0; i < dataTable2.Rows.Count; i++)
{
DataRow dataRow = dataTable2.Rows[i];
ListViewItem listData = new ListViewItem(dataRow["task_description"].ToString());
listData.SubItems.Add(dataRow["task_hours_to_complete"].ToString());
listData.SubItems.Add(utility.getEmployeeName((int)dataRow["employee_id"]));
pScreenTaskListView.Items.Add(listData);
}
}
catch
{
MessageBox.Show("Database may have disconnected, or selected data is not available.");
}
}