//Make a public list that hold that customer orders by id
public List <Order> getAllOrdersFromCustomer(int customerId)
{
OrderConnection conn = new OrderConnection();
List <Order> OrderList = new List <Order>();
//Execute a query to retrieve all orders by customer
conn.execute(@"select
OrderId,
DateCompleted,
DateCreated,
UserId
from ""Order""
where DateCompleted IS NOT NULL
AND UserId =" + customerId,
(SqliteDataReader reader) => {
while (reader.Read())
{
OrderList.Add(new Order
{
OrderId = reader.GetInt32(0),
Date = Convert.ToDateTime(reader[1].ToString()),
DateCreated = reader.GetDateTime(2),
CustomerId = reader.GetInt32(3)
});
}
}
);
return(OrderList);
}
public Customer getCustomerByFullName(string fullName)
{
string[] fullNameAsArray = Regex.Split(fullName, @"\s+").Where(s => s != string.Empty).ToArray();
if (fullNameAsArray.Length < 2 || fullNameAsArray.Length > 2)
{
Customer d = null;
return(d);
}
string firstName = fullNameAsArray[0];
string lastName = fullNameAsArray[1];
OrderConnection conn = new OrderConnection();
Customer c = null;
conn.execute(@"SELECT
UserId,
FirstName,
LastName
FROM user
WHERE FirstName = '" + firstName + "' AND " + "LastName = '" + lastName + "'",
(SqliteDataReader reader) => {
while (reader.Read())
{
c = new Customer
{
CustomerId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString()
};
}
});
return(c);
}
/**
* Purpose: Return a single Order from the database, retrieved by OrderId
* Arguments:
* OrderId - the Id of an Order that is being requested
* Return:
* An Order matching the provided OrderId retreived from the database
*/
public Order get(int OrderId)
{
OrderConnection conn = new OrderConnection();
Order order = null;
conn.execute(@"SELECT
OrderId,
DateCompleted,
UserId
FROM ""Order""
WHERE DateCompleted IS NOT null AND OrderId = " + OrderId, (SqliteDataReader reader) => {
while (reader.Read())
{
order = new Order
{
OrderId = reader.GetInt32(0),
Date = reader.GetDateTime(1),
CustomerId = reader.GetInt32(2)
};
}
});
return(order);
}
public List <Customer> getAll()
{
OrderConnection conn = new OrderConnection();
List <Customer> list = new List <Customer>();
conn.execute(@"SELECT
UserId,
FirstName,
LastName
FROM user",
(SqliteDataReader reader) =>
{
while (reader.Read())
{
list.Add(new Customer
{
CustomerId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString()
});
}
});
return(list);
}
/**
* Purpose: returns a customer from the database
* Arguments:
* int CustomerId
* Return:
* Desidred customer that matches customerId
*/
public Customer get(int CustomerId)
{
OrderConnection conn = new OrderConnection();
Customer c = null;
conn.execute(@"select
UserId,
FirstName,
LastName
from user
where UserId = " + CustomerId, (SqliteDataReader reader) => {
while (reader.Read())
{
c = new Customer
{
CustomerId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString()
};
}
});
return(c);
}
