//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 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 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.");
}
}
//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);
}
}
//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.");
}
}
