Esempio n. 1
0
        /// <summary>
        /// Verifies a person's login information and retrieves full name and role
        /// </summary>
        /// <param name="username">As a string</param>
        /// <param name="password">As a string</param>
        /// <returns></returns>
        public DataTable GetLogin(string username, string password)
        {
            DataTable dt = new DataTable();
            string    selectStatement =
                "SELECT l.personID, l.userName, l.password, (p.first_name + ' ' + p.last_name) AS 'name', p.role" +
                "FROM login l JOIN person p ON p.id = l.personID" +
                "WHERE username = @username AND password = @password";

            using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection())
            {
                connection.Open();
                SqlCommand sqlCommand = new SqlCommand(selectStatement, connection);
                sqlCommand.Parameters.AddWithValue("@username", username);
                sqlCommand.Parameters.AddWithValue("@password", password);
                SqlDataReader reader = sqlCommand.ExecuteReader();

                dt.Columns.Add("personID", typeof(int));
                dt.Columns.Add("userName", typeof(string));
                dt.Columns.Add("password", typeof(string));
                dt.Columns.Add("name", typeof(string));
                dt.Columns.Add("role", typeof(string));

                dt.Load(reader);
            }
            return(dt);
        }
Esempio n. 2
0
        public List <Shift> GetShifts()
        {
            SqlConnection connection = ScheduleManager_DB_Connection.GetConnection();
            List <Shift>  shiftList  = new List <Shift>();

            string selectStatement = "SELECT s.id, s.scheduleShiftId, s.personId, s.positionId, sh.scheduledStartTime, sh.scheduledEndTime, " +
                                     "sh.scheduledLunchBreakStartTime, sh.scheduledLunchBreakEndTime, sh.actualStartTime, sh.actualEndTime, sh.actualLunchBreakStart, " +
                                     "sh.acutalLunchBreakEnd, p.first_name, p.last_name, ps.position_title " +
                                     "FROM shift AS s " +
                                     "JOIN shiftHours AS sh ON s.scheduleShiftId = sh.id " +
                                     "JOIN person AS p ON s.personId = p.id " +
                                     "JOIN position AS ps ON s.positionId = ps.id";

            using (connection)
            {
                connection.Open();

                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    using (SqlDataReader reader = selectCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Shift shift = new Shift();
                            shift.personLastName  = reader["lastName"].ToString();
                            shift.personFirstName = reader["firstName"].ToString();


                            shiftList.Add(shift);
                        }
                    }
                }
            }
            return(shiftList);
        }
Esempio n. 3
0
        /// <summary>
        /// this method returns all employees
        /// </summary>
        /// <returns></returns>
        public List <Person> GetDesiredPersons(string whereClause)
        {
            List <Person> persons          = new List <Person>();
            string        desiredEmployees = this.selectedPersons + whereClause;

            using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection())
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(desiredEmployees, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Person person = new Person();
                            person.Id            = (int)reader["id"];
                            person.LastName      = reader["last_name"].ToString();
                            person.FirstName     = reader["first_name"].ToString();
                            person.DateOfBirth   = (DateTime)reader["date_of_birth"];
                            person.Ssn           = (char)reader["ssn"];
                            person.Gender        = reader["gender"].ToString();
                            person.StreetAddress = reader["street_address"].ToString();
                            person.Phone         = reader["phone"].ToString();
                            person.Zipcode       = reader["zipcode"].ToString();
                            person.Username      = reader["username"].ToString();
                            person.Password      = (byte[])reader["password"];
                            person.RoleId        = (int)reader["roleId"];
                            person.StatusId      = (int)reader["statusId"];


                            persons.Add(person);
                        }
                    }
                    return(persons);
                }
            }
        }
Esempio n. 4
0
        /// <summary>
        /// This method adds an accepted person to the database
        ///
        ///
        /// Need to set in the View an appropiate auto generated-- password - roleId - StatusId -
        ///
        /// </summary>
        /// <param name="addPerson"></param>
        public void AddPerson(Person addPerson)
        {
            int addedPersonId = -1;

            try
            {
                using (SqlConnection connection = ScheduleManager_DB_Connection.GetConnection())
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        string insertPerson = "INSERT person(" +
                                              "[last_name]" +
                                              " ,[first_name]" +
                                              " ,[date_of_birth]" +
                                              " ,[ssn]" +
                                              " ,[gender]" +
                                              " ,[street_address]" +
                                              " ,[phone]" +
                                              " ,[zipcode]" +
                                              " ,[username]" +
                                              " ,[password]" +
                                              " ,[roleId]" +
                                              " ,[statusId])" +
                                              " VALUES(" +
                                              " @last_name" +
                                              ", @first_name" +
                                              ", @date_of_birth" +
                                              ", @ssn" +
                                              ", @gender" +
                                              ", @street_address" +
                                              ", @phone" +
                                              ", @zipcode" +
                                              ", @username" +
                                              ", HASHBYTES('SHA2_256', @password)" +
                                              ", @roleId" +
                                              ", @statusId)";


                        using (SqlCommand command = new SqlCommand(insertPerson, connection))
                        {
                            command.Parameters.Add(new SqlParameter("@last_name", addPerson.LastName));
                            command.Parameters.Add(new SqlParameter("@first_name", addPerson.FirstName));
                            command.Parameters.Add(new SqlParameter("@date_of_birth", addPerson.DateOfBirth));
                            command.Parameters.Add(new SqlParameter("@ssn", addPerson.Ssn));
                            command.Parameters.Add(new SqlParameter("@gender", addPerson.Gender));
                            command.Parameters.Add(new SqlParameter("@street_address", addPerson.StreetAddress));
                            command.Parameters.Add(new SqlParameter("@phone", addPerson.Phone));
                            command.Parameters.Add(new SqlParameter("@zipcode", addPerson.Zipcode));
                            command.Parameters.Add(new SqlParameter("@username", addPerson.Username));
                            command.Parameters.Add(new SqlParameter("@password", addPerson.Password));
                            command.Parameters.Add(new SqlParameter("@roleId", addPerson.Password));
                            command.Parameters.Add(new SqlParameter("@statusID", addPerson.Password));

                            string selectStatement = "SELECT IDENT_CURRENT('Person') FROM Person";

                            using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                            {
                                selectCommand.Transaction = transaction;
                                addedPersonId             = Convert.ToInt32(selectCommand.ExecuteScalar());
                            }
                            transaction.Commit();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //Message Box won't work - thats wierd
                //MessageBox.Show(ex.Message, "Error");
            }
        }