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}'
