public static ActionResult <List <OrderModel> > Execute(Guid webSessionId, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // select user associated with given web session id command.CommandText = @$ " SELECT users.id FROM users JOIN web_sessions ON users.id = web_sessions.user_id WHERE web_sessions.id = '{webSessionId}' AND web_sessions.expired IS NULL "; var reader = command.ExecuteReader(); // if no rows returned, user was not found if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned row to get user id reader.Read(); var userId = reader["id"].ToString(); reader.Close(); // select all active orders for selected user command.CommandText = @$ " SELECT orders.* FROM orders WHERE orders.user_id = '{userId}' AND orders.completed IS NULL "; reader = command.ExecuteReader(); // read returned rows to get active orders var activeOrders = new List <OrderModel>(); while (reader.Read()) { activeOrders.Add(new OrderModel(reader)); } reader.Close(); return(new OkObjectResult(activeOrders)); }
public static ActionResult <List <OrderModel> > Execute(Guid webSessionId, List <PostNewOrdersType> data, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // select user associated with given web session id command.CommandText = @$ " SELECT users.id FROM users JOIN web_sessions ON users.id = web_sessions.user_id WHERE web_sessions.id = '{webSessionId}' AND web_sessions.expired IS NULL "; var reader = command.ExecuteReader(); // if no rows returned, user was not found if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned row to get user id reader.Read(); var userId = reader["id"].ToString(); reader.Close(); // iterate through new orders var newOrders = new List <OrderModel>(); foreach (var order in data) { // insert into database command.CommandText = @$ " INSERT INTO orders ( part , user_id , quantity , orderer , work_order , notes ) OUTPUT inserted.* VALUES ( '{order.part}' , '{userId}'
public static ActionResult <UserModel> Execute(Guid webSessionId, PostResetUserPasswordType data, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // get user with same username as given value command.CommandText = @$ " SELECT users.* FROM users WHERE users.username = '******' "; var reader = command.ExecuteReader(); // if no rows returned, no user found with given username if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned rows to get user id reader.Read(); var user = new UserModel(reader); reader.Close(); // hash new password var newSalt = UserController.NewSalt(); var newSaltString = UserController.EncodeSalt(newSalt); var newHashedPassword = UserController.ApplyHash( newSalt, data.newPassword ); // insert new password command.CommandText = @$ " INSERT INTO passwords ( user_id , hashed_password , salt ) VALUES ( '{user.id}' , '{newHashedPassword}'
public static ActionResult <List <OrderModel> > Execute(Guid webSessionId, DateTime startDate, DateTime endDate, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // select user associated with given web session id command.CommandText = @$ " SELECT users.id FROM users JOIN web_sessions ON users.id = web_sessions.user_id WHERE web_sessions.id = '{webSessionId}' AND web_sessions.expired IS NULL "; var reader = command.ExecuteReader(); // if no rows returned, user was not found if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned row to get user id reader.Read(); var userId = reader["id"].ToString(); reader.Close(); // select historical orders for given user command.CommandText = @$ " SELECT orders.* FROM orders WHERE orders.user_id = '{userId}' AND orders.placed > '{startDate.ToShortDateString()}'
public static ActionResult <UserModel> Execute(Guid webSessionId, Guid userId, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // delete passwords associated with given user command.CommandText = @$ " DELETE FROM passwords WHERE passwords.user_id = '{userId}' "; command.ExecuteNonQuery(); // delete user command.CommandText = @$ " DELETE FROM users OUTPUT deleted.* WHERE users.id = '{userId}' "; var reader = command.ExecuteReader(); // if no rows returned then user was not deleted if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned row to get deleted user reader.Read(); var deletedUser = new UserModel(reader); reader.Close(); return(new OkObjectResult(deletedUser)); }
public static ActionResult <List <string> > Execute(Guid webSessionId, List <string> partNames, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // iterate through new parts var newParts = new List <string>(); foreach (var part in partNames) { // add part to database command.CommandText = @$ " INSERT INTO parts ( name ) VALUES ( '{part}' ) OUTPUT inserted.* "; var reader = command.ExecuteReader(); // if no rows returned, part was not inserted if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned row to get inserted part reader.Read(); newParts.Add(reader["name"].ToString()); reader.Close(); } return(new OkObjectResult(newParts)); } }
public static ActionResult <List <OrderModel> > Execute(Guid webSessionId, int limit, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // select the last however many orders for all users command.CommandText = @$ " SELECT orders.* FROM orders WHERE orders.completed IS NOT NULL ORDER BY orders.completed DESC LIMIT {limit} "; var reader = command.ExecuteReader(); // read returned rows to get historical orders var historicalOrders = new List <OrderModel>(); while (reader.Read()) { historicalOrders.Add(new OrderModel(reader)); } reader.Close(); return(new OkObjectResult(historicalOrders)); } } catch (Exception ex) { Console.WriteLine(ex); return(new BadRequestResult()); } }
public static ActionResult <UserModel> Execute(Guid webSessionId, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // get user with given username command.CommandText = @$ " SELECT users.* FROM users JOIN web_sessions ON web_sessions.user_id = users.id WHERE web_sessions.id = '{webSessionId}' AND web_sessions.expired IS NULL "; var reader = command.ExecuteReader(); // if nothing returned, user does not exist if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returning row to create new user object reader.Read(); var user = new UserModel(reader); reader.Close(); return(new OkObjectResult(user)); } }
public static ActionResult <OrderModel> Execute(Guid webSessionId, PostUpdateOrderType data, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // update order with completion time command.CommandText = @$ " UPDATE orders SET orders.completed = GETDATE() , orders.completed_notes = {(data.completedNotes != null ? " '" + data.completedNotes + "' " : " null ")} OUTPUT inserted.* WHERE orders.id = '{data.id}' "; var reader = command.ExecuteReader(); // if no rows affected, given order was not found if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned row to get updated order reader.Read(); var order = new OrderModel(reader); reader.Close(); return(new OkObjectResult(order)); }
public static ActionResult <UserModel> Execute(Guid webSessionId, PostVerifyUserType data, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // update user, set verified time to now and set verifier user id to given value command.CommandText = @$ " UPDATE users SET verified = GETDATE() , verifier_user_id = (SELECT users.id FROM users JOIN web_sessions ON users.id = web_sessions.user_id WHERE web_sessions.id = '{data.webSessionId}') WHERE username = '******' "; var rowsAffected = command.ExecuteNonQuery(); // if no rows affected, user was not updated if (rowsAffected != 1) { return(new BadRequestResult()); } // select updated user from database command.CommandText = @$ " SELECT * FROM users WHERE username = '******'
public static ActionResult <List <string> > Execute(Guid webSessionId, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // get all part names from database command.CommandText = @$ " SELECT * FROM parts "; var reader = command.ExecuteReader(); // read rows to get part names var partIds = new List <string>(); while (reader.Read()) { partIds.Add(reader["name"].ToString()); } reader.Close(); return(new OkObjectResult(partIds)); } } catch (Exception ex) { Console.WriteLine(ex); return(new BadRequestResult()); } }
public static ActionResult <string> Execute(Guid webSessionId, string partName, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // delete part from database command.CommandText = @$ " DELETE FROM parts OUTPUT deleted.* WHERE parts.name = '{partName}' "; var reader = command.ExecuteReader(); // if no rows returned, part was not deleted if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned row to get deleted part reader.Read(); var deletedPart = reader["name"].ToString(); reader.Close(); return(new OkObjectResult(deletedPart)); } }
public static ActionResult <UserModel> Execute(Guid webSessionId, PostUpdateUserRoleType data, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // update user with given username to be new role command.CommandText = @$ " UPDATE users SET user_role = '{data.newUserRole}' WHERE users.username = '******' "; var rowsAffected = command.ExecuteNonQuery(); // if no rows affected, user was not sucessfully updated if (rowsAffected != 1) { return(new BadRequestResult()); } // get updated user command.CommandText = @$ " SELECT * FROM users WHERE username = '******'
public static ActionResult <List <OrderModel> > Execute(Guid webSessionId, DateTime startDate, DateTime endDate, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // select all orders within given date range command.CommandText = @$ " SELECT orders.* FROM orders WHERE orders.placed > '{startDate.ToShortDateString()}' AND orders.placed < '{endDate.ToShortDateString()}' "; var reader = command.ExecuteReader(); // read returned rows to get historical orders var historicalOrders = new List <OrderModel>(); while (reader.Read()) { historicalOrders.Add(new OrderModel(reader)); } reader.Close(); return(new OkObjectResult(historicalOrders)); }
public static ActionResult <UserModel> Execute(Guid webSessionId, PostUpdateUserPasswordType data, string connectionString) { try { using (var connection = new SqlConnection(connectionString)) { // create command object var command = new SqlCommand(); command.Connection = connection; command.Connection.Open(); // authenticate web session if (!WebSessionCheck.Check(webSessionId, connection, command)) { return(new UnauthorizedResult()); } // get user and password details for user with given username command.CommandText = @$ " SELECT users.* , passwords.hashed_password , passwords.salt FROM users JOIN passwords ON users.id = passwords.user_id WHERE users.username = '******' AND passwords.expired IS NULL "; var reader = command.ExecuteReader(); // if no rows returned, no user found with given username if (!reader.HasRows) { reader.Close(); return(new BadRequestResult()); } // read returned rows to get user details reader.Read(); var user = new UserModel(reader); var passwordSalt = reader["salt"].ToString(); var passwordHashed = reader["hashed_password"].ToString(); reader.Close(); // check old password is the same as password in database var oldHashedPassword = UserController.ApplyHash( Convert.FromBase64String(passwordSalt), data.oldPassword ); if (oldHashedPassword != passwordHashed) { return(new UnauthorizedResult()); } // hash new password var newSalt = UserController.NewSalt(); var newSaltString = UserController.EncodeSalt(newSalt); var newHashedPassword = UserController.ApplyHash( newSalt, data.newPassword ); // insert new password command.CommandText = @$ " INSERT INTO passwords ( user_id , hashed_password , salt ) VALUES ( '{user.id}' , '{newHashedPassword}'