//this method gets the largest id number from the database
public int Counter()
{
//creates a counter for the employee id
int idCounter = 0;
//this is a stored procedure call for the idCounter
//ths establishes a connection to the database
SqlConnection cnn = new SqlConnection();
DBcnn daterbase = new DBcnn(cnn);
daterbase.connect(null);
daterbase.open();
//if adding an employee id doesnt work, then this is the culprit
SqlCommand getID = new SqlCommand(@"SELECT MAX(EMPLOYEE_ID) FROM EMPLOYEES", cnn);
using (SqlDataReader reader = getID.ExecuteReader())
{
while (reader.Read())
{
idCounter = Convert.ToInt32(reader[0].ToString());
}
}
daterbase.close();
return(idCounter++);
}
//Initially fill the listview with employees
private void fillListView()
{
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT EMPLOYEE_ID, LAST_NAME, BACKGROUND FROM EMPLOYEES WHERE TITLE = 'New Hire'", cnn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
int id = Convert.ToInt32(dr["EMPLOYEE_ID"]);
string lastName = dr["LAST_NAME"].ToString().Trim(' ');
string background = dr["BACKGROUND"].ToString().Trim(' ');
NewHire newHire = new NewHire(id, lastName, background);
newHireList.Add(newHire);
}
foreach (NewHire newHire in newHireList)
{
int id = newHire.ID;
string lastName = newHire.LastName;
string background = newHire.Background;
ListViewItem i = new ListViewItem(id.ToString());
i.SubItems.Add(lastName);
i.SubItems.Add(background);
employeeListView.Items.Add(i);
}
}
//Constructor - 2 parameters
public Request(int eID, int sID)
{
SqlConnection cnn = new SqlConnection();
DBcnn db = new DBcnn(cnn);
db.connect(null);
db.open();
SqlCommand getID = new SqlCommand(@"SELECT MAX(REQUEST_ID) FROM REQUEST", cnn);
using (SqlDataReader reader = getID.ExecuteReader())
{
while (reader.Read())
{
reqID = Convert.ToInt32(reader[0].ToString());
}
}
db.close();
reqID++;
empID = eID;
supID = sID;
status = 1;
date = DateTime.Now.ToString("dd.MM.yyyy");
}
//Retreives the employee's name based off of their employee ID
private string GetEmployeeName(int eID)
{
string employeeName = null;
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=@eID", cnn);
cmd.Parameters.AddWithValue("@eID", eID);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
employeeName = dr["FIRST_NAME"].ToString() + " " + dr["LAST_NAME"].ToString();
}
return(employeeName);
}
//Handler for the click of the login button
private void loginButton_Click(object sender, EventArgs e)
{
username = usernameTextbox.Text;
password = passwordTextbox.Text;
int attempts = 0;
attempts++;
//user has failed login 3 times then lock them out for 2 hours
if (attempts >= 3)
{
MessageBox.Show("Failed 3 times, wait 2 hours and try again");
usernameTextbox.Enabled = false;
passwordTextbox.Enabled = false;
Thread.Sleep(5000);
attempts = 0;
usernameTextbox.Enabled = true;
passwordTextbox.Enabled = true;
}
else
{
try
{
//Create new database connection and sql connection
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
username = usernameTextbox.Text;
password = passwordTextbox.Text;
database.open();
{
//Create new log in session and give access
Login session = new Login(username, password, cnn);
int result = session.access();
if (result > 0)
//Successful login so look up and open employee's title
{
session.findTitle();
usernameTextbox.Text = null;
passwordTextbox.Text = null;
username = null;
password = null;
database.close();
}
else
{
MessageBox.Show("Incorrect login");
}
}
}
catch (Exception ex)
{
MessageBox.Show("Unexpected error:" + ex.Message);
}
}
}
//pulls employee's information and displays it
private void viewEmployeeButton_Click(object sender, EventArgs e)
{
int id = 0;
if (employeeListView.SelectedItems.Count > 0 && employeeListView.SelectedItems.Count < 2)
{
foreach (ListViewItem i in employeeListView.SelectedItems)
{
id = Convert.ToInt32(i.SubItems[0].Text);
}
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand(@"SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TITLE, DATE_OF_BIRTH, BACKGROUND, GENDER FROM EMPLOYEES
WHERE EMPLOYEE_ID=@id", cnn);
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
database.close();
string firstName = null;
string lastName = null;
string title = null;
string dateOfBirth = null;
string background = null;
string gender = null;
foreach (DataRow dr in dt.Rows)
{
firstName = dr["FIRST_NAME"].ToString();
lastName = dr["LAST_NAME"].ToString();
title = dr["TITLE"].ToString();
dateOfBirth = dr["DATE_OF_BIRTH"].ToString();
background = dr["BACKGROUND"].ToString();
gender = dr["GENDER"].ToString();
}
ViewNewHireForm newHireForm = new ViewNewHireForm();
newHireForm.EmployeeIDLabel = id.ToString();
newHireForm.NameLabel = firstName.TrimEnd(' ') + " " + lastName.TrimEnd(' ');
newHireForm.TitleLabel = title;
newHireForm.DateOfBirthLabel = dateOfBirth;
newHireForm.BackgroundStatusLabel = background;
newHireForm.GenderLabel = gender;
newHireForm.ShowDialog();
}
else
{
MessageBox.Show("Please select an employee");
}
}
//handle for an hr representative creating a new request
private void button1_Click(object sender, EventArgs e)
{
int id = 0;
string background = null;
if (employeeListView.SelectedItems.Count > 0 && employeeListView.SelectedItems.Count < 2)
{
foreach (ListViewItem i in employeeListView.SelectedItems)
{
id = Convert.ToInt32(i.SubItems[0].Text);
}
foreach (ListViewItem i in employeeListView.SelectedItems)
{
background = i.SubItems[2].Text.ToString().Trim(' ');
}
if (background == "Failed")
{
MessageBox.Show("You cannot create a request for this employee because they have not passed their background check.");
}
else
{
DialogResult result = MessageBox.Show("Are you sure you want to create a request for employee " + id.ToString() + "?", "Create Request", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
int supID = 0;
CheckSuperiorForm supForm = new CheckSuperiorForm();
supForm.ShowDialog();
supID = supForm.getSupervisorID();
//Create new request
Request r = new Request(id, supID);
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand add = new SqlCommand("INSERT INTO REQUEST(REQUEST_ID, COUNTER, DATE, SUPERVISOR, EMPLOYEE_ID) VALUES(@requestID, @counter, @date, @supervisor, @employeeID)", cnn);
add.Parameters.Add(new SqlParameter("@requestID", r.RequestID));
add.Parameters.Add(new SqlParameter("@counter", r.Status));
add.Parameters.Add(new SqlParameter("@date", r.Date));
add.Parameters.Add(new SqlParameter("@supervisor", r.SupervisorID));
add.Parameters.Add(new SqlParameter("@employeeID", r.EmployeeID));
add.ExecuteNonQuery();
database.close();
ConcatenateListView();
}
}
}
else
{
MessageBox.Show("Please select an employee to create a request for.");
}
}
//Handler for the complete request button
private void completeRequestButton_Click(object sender, EventArgs e)
{
int requestId = 0;
int empId = 0;
int supId = supID;
string date = null;
int status = 0;
if (newRequestListView.SelectedItems.Count == 1)
{
foreach (ListViewItem i in newRequestListView.SelectedItems)
{
requestId = Convert.ToInt32(i.SubItems[0].Text);
}
DialogResult result = MessageBox.Show("Are you sure you want to complete request #" + requestId.ToString() + "?", "Complete Request", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT * FROM REQUEST WHERE REQUEST_ID=@requestId", cnn);
cmd.Parameters.AddWithValue("@requestId", requestId);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
database.close();
foreach (DataRow dr in dt.Rows)
{
empId = Convert.ToInt32(dr["EMPLOYEE_ID"]);
status = Convert.ToInt32(dr["COUNTER"]);
date = dr["EMPLOYEE_ID"].ToString();
}
Request request = new Request(requestId, supId, empId, 0, 0, 0, date);
RequestForm r = new RequestForm(requestId);
string employeeName = GetEmployeeName(empId);
r.EmployeeNameLabel = employeeName;
r.ShowDialog();
RefreshListViews();
}
}
else
{
MessageBox.Show("Please Select a Request.");
}
}
//Retreives all the outstanding builds in the requests not yet completed
public int GetOutstandingBuilds()
{
int requestID = 0;
string startDate = null;
DateTime endDate = DateTime.Now;
List <int> outstandingList = new List <int>();
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT REQUEST_ID, DATE FROM REQUEST WHERE COUNTER < 4", cnn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
database.close();
foreach (DataRow dr in dt.Rows)
{
int days = 0;
DateTime convertedStartDate = new DateTime();
var culture = new CultureInfo("en-US");
requestID = Convert.ToInt32(dr["REQUEST_ID"]);
startDate = dr["DATE"].ToString().TrimEnd(' ');
startDate = startDate.Replace('.', '/');
string month = startDate[3].ToString() + startDate[4].ToString();
string day = startDate[0].ToString() + startDate[1].ToString();
StringBuilder sb = new StringBuilder(startDate);
sb.Remove(0, 2);
sb.Insert(0, month);
sb.Remove(3, 2);
sb.Insert(3, day);
startDate = sb.ToString();
convertedStartDate = Convert.ToDateTime(startDate);
days = Convert.ToInt32((endDate - convertedStartDate).TotalDays);
if (days > 7)
{
outstandingList.Add(requestID);
}
}
numberOutstandingBuilds = outstandingList.Count;
return(numberOutstandingBuilds);
}
//Refresh the list view
private void RefreshListView()
{
requestListView.Items.Clear();
List <int> requestID = new List <int>();
List <int> supervisorID = new List <int>();
List <string> supervisorName = new List <string>();
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT REQUEST_ID, SUPERVISOR FROM REQUEST WHERE COUNTER = 3", cnn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
database.close();
foreach (DataRow dr in dt.Rows)
{
requestID.Add(Convert.ToInt32(dr["REQUEST_ID"]));
supervisorID.Add(Convert.ToInt32(dr["SUPERVISOR"]));
}
database.open();
foreach (int id in supervisorID)
{
SqlCommand descCmd = new SqlCommand("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=@id", cnn);
descCmd.Parameters.AddWithValue("@id", id);
descCmd.ExecuteNonQuery();
SqlDataAdapter descda = new SqlDataAdapter(descCmd);
DataTable descdt = new DataTable();
descda.Fill(descdt);
foreach (DataRow dr in descdt.Rows)
{
supervisorName.Add(dr["FIRST_NAME"].ToString().TrimEnd(' ') + " " + dr["LAST_NAME"].ToString().TrimEnd(' '));
}
}
database.close();
for (int i = 0; i < requestID.Count; i++)
{
ListViewItem item = new ListViewItem(requestID[i].ToString());
item.SubItems.Add(supervisorName[i]);
requestListView.Items.Add(item);
}
}
//Handler for reject button
private void rejectButton_Click(object sender, EventArgs e)
{
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand reqcmd = new SqlCommand("UPDATE REQUEST SET COUNTER = 1 WHERE REQUEST_ID = @requestID", cnn);
reqcmd.Parameters.AddWithValue("@requestID", requestID);
reqcmd.ExecuteNonQuery();
this.Close();
}
//Adds on a new request that is created into the list view
private void ConcatenateListView()
{
List <Request> requestList = new List <Request>();
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT * FROM REQUEST", cnn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
int rID = Convert.ToInt32(dr["REQUEST_ID"]);
int sID = Convert.ToInt32(dr["SUPERVISOR"]);
int status = Convert.ToInt32(dr["COUNTER"]);
Request r = new Request(rID, sID, status);
requestList.Add(r);
}
Request lastItem = requestList[requestList.Count - 1];
int reqID = lastItem.RequestID;
int supID = lastItem.SupervisorID;
int stat = lastItem.Status;
ListViewItem i = new ListViewItem(reqID.ToString());
i.SubItems.Add(supID.ToString());
if (stat == 1)
{
i.SubItems.Add("Awaiting Supervisor to choose Items");
}
else if (stat == 2)
{
i.SubItems.Add("Awaiting Manager approval");
}
else if (stat == 3)
{
i.SubItems.Add("Request Being Build");
}
else if (stat == 4)
{
i.SubItems.Add("Request Complete");
}
requestListView.Items.Add(i);
}
//Refresh the listviews with items from the database
private void RefreshListViews()
{
int requestID;
newRequestListView.Items.Clear();
inPogressRequestListView.Items.Clear();
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT REQUEST_ID FROM REQUEST WHERE SUPERVISOR=@supID AND COUNTER = 1", cnn);
cmd.Parameters.AddWithValue("@supID", supID);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
requestID = Convert.ToInt32(dr["REQUEST_ID"]);
ListViewItem i = new ListViewItem(requestID.ToString());
newRequestListView.Items.Add(i);
}
SqlCommand newCmd = new SqlCommand("SELECT REQUEST_ID FROM REQUEST WHERE SUPERVISOR=@supID AND COUNTER = 2", cnn);
newCmd.Parameters.AddWithValue("@supID", supID);
newCmd.ExecuteNonQuery();
SqlDataAdapter newda = new SqlDataAdapter(newCmd);
DataTable newdt = new DataTable();
newda.Fill(newdt);
database.close();
foreach (DataRow dr in newdt.Rows)
{
requestID = Convert.ToInt32(dr["REQUEST_ID"]);
ListViewItem i = new ListViewItem(requestID.ToString());
inPogressRequestListView.Items.Add(i);
}
}
//Handler for the confirm request button
private void confirmRequestButton_Click(object sender, EventArgs e)
{
string date = DateTime.Now.ToString("dd.MM.yyyy");
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand reqcmd = new SqlCommand("UPDATE REQUEST SET COUNTER = 4, END_DATE = @date WHERE REQUEST_ID = @requestID", cnn);
reqcmd.Parameters.AddWithValue("@requestID", requestID);
reqcmd.Parameters.AddWithValue("@date", date);
reqcmd.ExecuteNonQuery();
this.Close();
}
//Constructor
public Request(int sID, int eID, decimal sub, decimal tax, decimal price, string d)
{
SqlConnection cnn = new SqlConnection();
DBcnn db = new DBcnn(cnn);
db.connect(null);
db.open();
SqlCommand getID = new SqlCommand(@"SELECT MAX(REQUEST) FROM REQUEST", cnn);
reqID = Convert.ToInt32(getID.ToString()) + 1;
db.close();
supID = sID;
empID = eID;
status = 1;
subtotal = sub;
salesTax = tax;
totalPrice = price;
date = d;
}
//this method adds employees to the database
public void add(List <Employee> nEmployees)
{
//ths establishes a connection to the database
SqlConnection cnn = new SqlConnection();
DBcnn daterbase = new DBcnn(cnn);
daterbase.connect(null);
daterbase.open();
List <string> lastNames = new List <string>();
SqlCommand cmd = new SqlCommand(@"SELECT LAST_NAME FROM EMPLOYEES", cnn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "EMPLOYEES");
foreach (DataRow dr in ds.Tables["EMPLOYEES"].Rows)
{
lastNames.Add(dr["LAST_NAME"].ToString().TrimEnd(' '));
}
//this sql statement takes each attribute of employee "e" and pushes them to the database
foreach (Employee e in nEmployees)
{
if (!(lastNames.Contains(e.lastN)))
{
SqlCommand addin = new SqlCommand("INSERT INTO EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TITLE, PASSWORD, DATE_OF_BIRTH, BACKGROUND, GENDER) VALUES(@employeeID, @firstN, @lastN, @empTitle, @emPassword, @bday, @CBGB, @gander)", cnn);
addin.Parameters.Add(new SqlParameter("@employeeID", e.employeeID));
addin.Parameters.Add(new SqlParameter("@firstN", e.firstN));
addin.Parameters.Add(new SqlParameter("@lastN", e.lastN));
addin.Parameters.Add(new SqlParameter("@empTitle", e.empTitle));
addin.Parameters.Add(new SqlParameter("@emPassword", e.emPassword));
addin.Parameters.Add(new SqlParameter("@bday", e.bday));
addin.Parameters.Add(new SqlParameter("@CBGB", e.CBGB));
addin.Parameters.Add(new SqlParameter("@gander", e.gander));
addin.ExecuteNonQuery();
}
}
daterbase.close();
}
//Initially fill the completed request list view
private void FillCompletedRequestListView()
{
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT REQUEST_ID FROM REQUEST WHERE COUNTER > 1 AND SUPERVISOR = @supID", cnn);
cmd.Parameters.AddWithValue("@supID", supID);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
int requestId = Convert.ToInt32(dr["REQUEST_ID"]);
ListViewItem i = new ListViewItem(requestId.ToString());
inPogressRequestListView.Items.Add(i);
}
database.close();
}
//Populates form
private void FillForm()
{
int counter;
int supervisorID = 0;
int employeeID = 0;
string supervisorName = null;
string employeeName = null;
int quantity = 0;
decimal unitPrice = 0;
decimal lineTotal = 0;
List <int> itemIDList = new List <int>();
List <string> descriptionList = new List <string>();
List <LineItem> request = new List <LineItem>();
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand reqcmd = new SqlCommand("SELECT COUNTER, SUPERVISOR, EMPLOYEE_ID FROM REQUEST WHERE REQUEST_ID = @requestID", cnn);
reqcmd.Parameters.AddWithValue("@requestID", requestID);
reqcmd.ExecuteNonQuery();
SqlDataAdapter reqda = new SqlDataAdapter(reqcmd);
DataTable reqdt = new DataTable();
reqda.Fill(reqdt);
database.close();
foreach (DataRow dr in reqdt.Rows)
{
counter = Convert.ToInt32(dr["COUNTER"]);
supervisorID = Convert.ToInt32(dr["SUPERVISOR"]);
employeeID = Convert.ToInt32(dr["EMPLOYEE_ID"]);
}
//Get supervisor Name
database.open();
SqlCommand supcmd = new SqlCommand("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = @supervisorID", cnn);
supcmd.Parameters.AddWithValue("@supervisorID", supervisorID);
supcmd.ExecuteNonQuery();
SqlDataAdapter supda = new SqlDataAdapter(supcmd);
DataTable supdt = new DataTable();
supda.Fill(supdt);
database.close();
foreach (DataRow dr in supdt.Rows)
{
supervisorName = dr["FIRST_NAME"].ToString().TrimEnd(' ') + " " + dr["LAST_NAME"].ToString().TrimEnd(' ');
}
//Get employee Name
database.open();
SqlCommand empcmd = new SqlCommand("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = @employeeID", cnn);
empcmd.Parameters.AddWithValue("@employeeID", employeeID);
empcmd.ExecuteNonQuery();
SqlDataAdapter empda = new SqlDataAdapter(empcmd);
DataTable empdt = new DataTable();
empda.Fill(empdt);
database.close();
foreach (DataRow dr in empdt.Rows)
{
employeeName = dr["FIRST_NAME"].ToString().TrimEnd(' ') + " " + dr["LAST_NAME"].ToString().TrimEnd(' ');
}
//Assign to labels
requestSupLabel.Text = supervisorName;
employeeLabel.Text = employeeName;
//Get line items on request
database.open();
SqlCommand lineitemcmd = new SqlCommand("SELECT QUANTITY, ITEM_ID, UNIT_PRICE, LINE_TOTAL FROM LINE_ORDER WHERE REQUEST_ID = @requestID", cnn);
lineitemcmd.Parameters.AddWithValue("@requestID", requestID);
lineitemcmd.ExecuteNonQuery();
SqlDataAdapter lineitemda = new SqlDataAdapter(lineitemcmd);
DataTable lineitemdt = new DataTable();
lineitemda.Fill(lineitemdt);
database.close();
foreach (DataRow dr in lineitemdt.Rows)
{
quantity = Convert.ToInt32(dr["QUANTITY"]);
itemIDList.Add(Convert.ToInt32(dr["ITEM_ID"]));
unitPrice = Convert.ToDecimal(dr["UNIT_PRICE"]);
lineTotal = Convert.ToDecimal(dr["LINE_TOTAL"]);
LineItem item = new LineItem(requestID, Convert.ToInt32(dr["ITEM_ID"]), quantity, unitPrice, lineTotal);
request.Add(item);
}
database.open();
foreach (int id in itemIDList)
{
SqlCommand descrCmd = new SqlCommand("SELECT DESCRIPTION FROM SOFTWAREHARDWARE WHERE ITEM_ID = @id", cnn);
descrCmd.Parameters.AddWithValue("@id", id);
descrCmd.ExecuteNonQuery();
SqlDataAdapter descrda = new SqlDataAdapter(descrCmd);
DataTable descrdt = new DataTable();
descrda.Fill(descrdt);
foreach (DataRow dr in descrdt.Rows)
{
descriptionList.Add(dr["DESCRIPTION"].ToString());
}
}
database.close();
//Fill list view
for (int i = 0; i < request.Count; i++)
{
ListViewItem item = new ListViewItem(request[i].Quantity.ToString());
item.SubItems.Add(descriptionList[i]);
item.SubItems.Add(request[i].ItemPrice.ToString());
item.SubItems.Add(request[i].LineTotal.ToString());
requestListView.Items.Add(item);
}
}
//Handler for submit button
private void submitButton_Click(object sender, EventArgs e)
{
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
List <int> itemIDList = new List <int>();
List <int> tempItemIDList = new List <int>();
List <LineItem> request = new List <LineItem>();
foreach (ListViewItem i in requestItemsListView.Items)
{
itemIDList.Add(Convert.ToInt32(i.Text));
}
foreach (int i in itemIDList)
{
if (!(tempItemIDList.Contains(i)))
{
int itemID = i;
string description = null;
decimal price = 0;
SqlCommand cmd = new SqlCommand("SELECT ITEM_ID, DESCRIPTION, UNIT_PRICE FROM SOFTWAREHARDWARE WHERE ITEM_ID=@i", cnn);
cmd.Parameters.AddWithValue("@i", i);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
description = dr["DESCRIPTION"].ToString();
price = Convert.ToDecimal(dr["UNIT_PRICE"]);
}
LineItem lineItem = new LineItem(requestID, itemID, 1, price, price);
request.Add(lineItem);
tempItemIDList.Add(i);
}
else
{
foreach (LineItem item in request)
{
if (item.ItemID == i)
{
item.Quantity++;
item.LineTotal = item.Quantity * item.ItemPrice;
}
}
}
}
foreach (LineItem item in request)
{
SqlCommand addCmd = new SqlCommand("INSERT INTO LINE_ORDER(REQUEST_ID, ITEM_ID, QUANTITY, UNIT_PRICE, LINE_TOTAL, LINE_ITEM_ID) VALUES(@requestID, @itemID, @quantity, @unitPrice, @lineTotal, @lineItemID)", cnn);
addCmd.Parameters.Add(new SqlParameter("@requestID", item.RequestID));
addCmd.Parameters.Add(new SqlParameter("@itemID", item.ItemID));
addCmd.Parameters.Add(new SqlParameter("@quantity", item.Quantity));
addCmd.Parameters.Add(new SqlParameter("@unitPrice", item.ItemPrice));
addCmd.Parameters.Add(new SqlParameter("@lineTotal", item.LineTotal));
addCmd.Parameters.Add(new SqlParameter("@lineItemID", item.LineItemID));
addCmd.ExecuteNonQuery();
}
SqlCommand updateRequestCmd = new SqlCommand("SELECT COUNTER FROM REQUEST WHERE REQUEST_ID = @reqID", cnn);
updateRequestCmd.Parameters.AddWithValue("@reqID", requestID);
updateRequestCmd.ExecuteNonQuery();
SqlDataAdapter updateDA = new SqlDataAdapter(updateRequestCmd);
DataTable updateDT = new DataTable();
updateDA.Fill(updateDT);
int counter = 0;
decimal subtotal = Convert.ToDecimal(subtotalLabel.Text.ToString().TrimStart('$'));
decimal salesTax = Convert.ToDecimal(salesTaxLabel.Text.ToString().TrimStart('$'));
decimal totalPrice = Convert.ToDecimal(totalCostLabel.Text.ToString().TrimStart('$'));
foreach (DataRow dr in updateDT.Rows)
{
counter = Convert.ToInt32(dr["COUNTER"]);
counter++;
}
SqlCommand insertNewDataCmd = new SqlCommand("UPDATE REQUEST SET COUNTER = @counter, SUBTOTAL = @subtotal, SALES_TAX = @salesTax, TOTAL_PRICE = @totalPrice WHERE REQUEST_ID = @reqID", cnn);
insertNewDataCmd.Parameters.AddWithValue("@counter", counter);
insertNewDataCmd.Parameters.AddWithValue("@subtotal", subtotal);
insertNewDataCmd.Parameters.AddWithValue("@salesTax", salesTax);
insertNewDataCmd.Parameters.AddWithValue("@totalPrice", totalPrice);
insertNewDataCmd.Parameters.AddWithValue("@reqID", requestID);
insertNewDataCmd.ExecuteNonQuery();
requestForm.Close();
this.Close();
}
//Handler for the view request button
private void viewRequestButton_Click(object sender, EventArgs e)
{
SupervisorViewRequestForm viewRequestForm = new SupervisorViewRequestForm();
List <LineItem> request = new List <LineItem>();
List <int> idForDescription = new List <int>();
List <string> descriptionList = new List <string>();
int requestID = 0;
int itemID = 0;
int quantity = 0;
string description = null;
decimal price = 0;
decimal lineTotal = 0;
if (inPogressRequestListView.SelectedItems.Count == 1)
{
foreach (ListViewItem i in inPogressRequestListView.SelectedItems)
{
requestID = Convert.ToInt32(i.SubItems[0].Text);
}
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT QUANTITY, ITEM_ID, UNIT_PRICE, LINE_TOTAL FROM LINE_ORDER WHERE REQUEST_ID = @requestID", cnn);
cmd.Parameters.AddWithValue("@requestID", requestID);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
database.close();
foreach (DataRow dr in dt.Rows)
{
quantity = Convert.ToInt32(dr["QUANTITY"]);
itemID = Convert.ToInt32(dr["ITEM_ID"]);
price = Convert.ToDecimal(dr["UNIT_PRICE"]);
lineTotal = Convert.ToDecimal(dr["LINE_TOTAL"]);
LineItem item = new LineItem(requestID, itemID, quantity, price, lineTotal);
request.Add(item);
}
foreach (LineItem item in request)
{
idForDescription.Add(item.ItemID);
}
foreach (int id in idForDescription)
{
database.connect(null);
database.open();
SqlCommand descriptionCmd = new SqlCommand("SELECT DESCRIPTION FROM SOFTWAREHARDWARE WHERE ITEM_ID = @id", cnn);
descriptionCmd.Parameters.AddWithValue("@id", id);
descriptionCmd.ExecuteNonQuery();
SqlDataAdapter descriptionda = new SqlDataAdapter(descriptionCmd);
DataTable descriptiondt = new DataTable();
descriptionda.Fill(descriptiondt);
database.close();
foreach (DataRow dr in descriptiondt.Rows)
{
description = dr["DESCRIPTION"].ToString().TrimEnd(' ');
descriptionList.Add(description);
}
}
for (int i = 0; i < request.Count; i++)
{
ListViewItem item = new ListViewItem(request[i].Quantity.ToString());
item.SubItems.Add(descriptionList[i]);
item.SubItems.Add(request[i].ItemPrice.ToString("C2"));
item.SubItems.Add(request[i].LineTotal.ToString("C2"));
viewRequestForm.RequestListView.Items.Add(item);
}
viewRequestForm.Show();
}
else
{
MessageBox.Show("Please select a Request.");
}
}
//Calculates the average build time of the requests that are already complete
public decimal AverageBuildTime()
{
int requestID = 0;
string startDate = null;
string endDate = null;
DateTime convertedStartDate = new DateTime();
DateTime convertedEndDate = new DateTime();
decimal counter = 0;
decimal totalDays = 0;
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand("SELECT REQUEST_ID, DATE, END_DATE FROM REQUEST WHERE COUNTER = 4", cnn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
database.close();
foreach (DataRow dr in dt.Rows)
{
requestID = Convert.ToInt32(dr["REQUEST_ID"]);
startDate = dr["DATE"].ToString().TrimEnd(' ');
startDate = startDate.Replace('.', '/');
string month = startDate[3].ToString() + startDate[4].ToString();
string day = startDate[0].ToString() + startDate[1].ToString();
StringBuilder sb = new StringBuilder(startDate);
sb.Remove(0, 2);
sb.Insert(0, month);
sb.Remove(3, 2);
sb.Insert(3, day);
startDate = sb.ToString();
convertedStartDate = Convert.ToDateTime(startDate);
endDate = dr["END_DATE"].ToString().TrimEnd(' ');
endDate = endDate.Replace('.', '/');
string emonth = endDate[3].ToString() + endDate[4].ToString();
string eday = endDate[0].ToString() + endDate[1].ToString();
StringBuilder esb = new StringBuilder(endDate);
esb.Remove(0, 2);
esb.Insert(0, emonth);
esb.Remove(3, 2);
esb.Insert(3, eday);
endDate = esb.ToString();
convertedEndDate = Convert.ToDateTime(endDate);
decimal days = Convert.ToDecimal((convertedStartDate - convertedEndDate).TotalDays);
totalDays += days;
counter++;
}
averageBuildTime = (totalDays) / counter;
return(averageBuildTime);
}
//Initially fill the list view
private void FillListView()
{
int quantity = 0;
decimal unitPrice = 0;
decimal lineTotal = 0;
List <int> itemIDList = new List <int>();
List <string> descriptionList = new List <string>();
List <LineItem> request = new List <LineItem>();
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand lineitemcmd = new SqlCommand("SELECT QUANTITY, ITEM_ID, UNIT_PRICE, LINE_TOTAL FROM LINE_ORDER WHERE REQUEST_ID = @requestID", cnn);
lineitemcmd.Parameters.AddWithValue("@requestID", requestID);
lineitemcmd.ExecuteNonQuery();
SqlDataAdapter lineitemda = new SqlDataAdapter(lineitemcmd);
DataTable lineitemdt = new DataTable();
lineitemda.Fill(lineitemdt);
database.close();
foreach (DataRow dr in lineitemdt.Rows)
{
quantity = Convert.ToInt32(dr["QUANTITY"]);
itemIDList.Add(Convert.ToInt32(dr["ITEM_ID"]));
unitPrice = Convert.ToDecimal(dr["UNIT_PRICE"]);
lineTotal = Convert.ToDecimal(dr["LINE_TOTAL"]);
LineItem item = new LineItem(requestID, Convert.ToInt32(dr["ITEM_ID"]), quantity, unitPrice, lineTotal);
request.Add(item);
}
database.open();
foreach (int id in itemIDList)
{
SqlCommand descrCmd = new SqlCommand("SELECT DESCRIPTION FROM SOFTWAREHARDWARE WHERE ITEM_ID = @id", cnn);
descrCmd.Parameters.AddWithValue("@id", id);
descrCmd.ExecuteNonQuery();
SqlDataAdapter descrda = new SqlDataAdapter(descrCmd);
DataTable descrdt = new DataTable();
descrda.Fill(descrdt);
foreach (DataRow dr in descrdt.Rows)
{
descriptionList.Add(dr["DESCRIPTION"].ToString());
}
}
database.close();
//Fill list view
for (int i = 0; i < request.Count; i++)
{
ListViewItem item = new ListViewItem(" ");
item.SubItems.Add(request[i].ItemID.ToString());
item.SubItems.Add(descriptionList[i]);
item.SubItems.Add(request[i].LineTotal.ToString());
itemsListView.Items.Add(item);
}
}
//open the selected request
private void viewRequestButton_Click(object sender, EventArgs e)
{
int requestId = 0;
int counter = 0;
string date = null;
int supervisorId = 0;
int employeeId = 0;
string supervisorName = null;
string employeeName = null;
if (requestListView.SelectedItems.Count > 0 && requestListView.SelectedItems.Count < 2)
{
foreach (ListViewItem i in requestListView.SelectedItems)
{
requestId = Convert.ToInt32(i.SubItems[0].Text);
}
SqlConnection cnn = new SqlConnection();
DBcnn database = new DBcnn(cnn);
database.connect(null);
database.open();
SqlCommand cmd = new SqlCommand(@"SELECT REQUEST_ID, COUNTER, DATE, SUPERVISOR, EMPLOYEE_ID FROM REQUEST
WHERE REQUEST_ID=@requestId", cnn);
cmd.Parameters.AddWithValue("@requestId", requestId);
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
counter = Convert.ToInt32(dr["COUNTER"]);
date = dr["DATE"].ToString();
supervisorId = Convert.ToInt32(dr["SUPERVISOR"]);
employeeId = Convert.ToInt32(dr["EMPLOYEE_ID"]);
}
SqlCommand supervisorCmd = new SqlCommand(@"SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=@supervisorId", cnn);
supervisorCmd.Parameters.AddWithValue("@supervisorId", supervisorId);
supervisorCmd.ExecuteNonQuery();
SqlDataAdapter supDA = new SqlDataAdapter(supervisorCmd);
DataTable supDT = new DataTable();
supDA.Fill(supDT);
foreach (DataRow dr in supDT.Rows)
{
supervisorName = dr["FIRST_NAME"].ToString() + " " + dr["LAST_NAME"].ToString();
}
SqlCommand employeeCmd = new SqlCommand(@"SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=@employeeId", cnn);
employeeCmd.Parameters.AddWithValue("@employeeId", employeeId);
employeeCmd.ExecuteNonQuery();
SqlDataAdapter empDA = new SqlDataAdapter(employeeCmd);
DataTable empDT = new DataTable();
empDA.Fill(empDT);
database.close();
foreach (DataRow dr in empDT.Rows)
{
employeeName = dr["FIRST_NAME"].ToString() + " " + dr["LAST_NAME"].ToString();
}
HRViewRequestForm hrv = new HRViewRequestForm();
hrv.RequestIDLabel = requestId.ToString();
hrv.EmployeeLabel = employeeName;
hrv.SupervisorLabel = supervisorName;
hrv.RequestDateLabel = date;
if (counter == 1)
{
hrv.StatusLabel = "Awaiting Supervisor to choose Items";
}
else if (counter == 2)
{
hrv.StatusLabel = "Awaiting Manager approval";
}
else if (counter == 3)
{
hrv.StatusLabel = "Request Being Build";
}
else if (counter == 4)
{
hrv.StatusLabel = "Request Complete";
}
hrv.ShowDialog();
}
else
{
MessageBox.Show("Please select an employee");
}
}
