Beispiel #1
0
        //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;
        }
Beispiel #16
0
        //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);
            }
        }
Beispiel #19
0
        //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");
            }
        }