/// <summary>
/// Gets all order line items associated with an order ID.
/// </summary>
/// <param name="orderid"></param>
/// <returns></returns>
private static List <OrderLine> GetOrderLineItemsByOrderID(int orderid)
{
List <OrderLine> lineItems = new List <OrderLine>();
DatabaseFacilitator database = new DatabaseFacilitator();
database.ConnectToDatabase(); // connect
// firstly, get order.
string query = "SELECT * FROM [DBO].[ORDER_LINE_ITEM] WHERE [ORDER_ID] = @ORDER_ID"; //sql query
using (SqlCommand cmd = new SqlCommand(query, database.GetDatabaseConnection())) //create sql command
{
cmd.Parameters.Add("@ORDER_ID", SqlDbType.Int, 50).Value = orderid;
using (SqlDataReader reader = cmd.ExecuteReader()) // execute query
{
while (reader.Read()) // iterate through results - should only be 1.
{
OrderLine ol = new OrderLine();
ol.item = ItemCatalogFacilitator.GetItemByID(Int32.Parse(reader[2].ToString()));
ol.quantity = Int32.Parse(reader[3].ToString());
ol.price = Double.Parse(reader[4].ToString());
lineItems.Add(ol);
}
}
}
// Close database connection
database.CloseDatabaseConnection();
return(lineItems);
}
/// <summary>
/// Retrieves a customer from the database, using the customer's id.
/// </summary>
/// <param name="customerid"></param>
/// <returns></returns>
private static Customer GetCustomerByID(int customerid)
{
Customer customer = null;
// Initialise database connection
DatabaseFacilitator database = new DatabaseFacilitator();
database.ConnectToDatabase(); // connect
// firstly, get order.
string query = "SELECT * FROM [DBO].[CUSTOMER] WHERE [CUSTOMER_ID] = @CUSTOMER_ID"; //sql query
using (SqlCommand cmd = new SqlCommand(query, database.GetDatabaseConnection())) //create sql command
{
cmd.Parameters.Add("@CUSTOMER_ID", SqlDbType.Int, 50).Value = customerid;
using (SqlDataReader reader = cmd.ExecuteReader()) // execute query
{
while (reader.Read()) // iterate through results - should only be 1.
{
customer = new Customer();
customer.customerID = Int32.Parse(reader[0].ToString()); // get customer ID
customer.name = reader[1].ToString();
customer.address = reader[2].ToString();
switch (reader[3].ToString())
{
case "PayPal":
customer.paymentMethod = PayentMethodEnum.PayPal; break;
case "CreditCard":
customer.paymentMethod = PayentMethodEnum.CreditCard; break;
default:
customer.paymentMethod = PayentMethodEnum.PayPal; break;
}
customer.email = reader[4].ToString();
customer.phone = reader[5].ToString();
}
}
}
// Close database connection
database.CloseDatabaseConnection();
return(customer);
}
/// <summary>
/// Retrieves all items from the database and returns them
/// in a list.
/// </summary>
/// <returns></returns>
public static List <Item> GetAllItems()
{
List <Item> items = new List <Item>();
// Initialise database connection
DatabaseFacilitator database = new DatabaseFacilitator();
database.ConnectToDatabase(); // connect
string query = "SELECT * FROM [DBO].[Item]"; //sql query
using (SqlCommand cmd = new SqlCommand(query, database.GetDatabaseConnection())) //create sql command
{
using (SqlDataReader reader = cmd.ExecuteReader()) // execute query
{
while (reader.Read()) // iterate through results
{
Item item = new Item();
item.itemID = Int32.Parse(reader[0].ToString()); // get item ID
item.name = reader[1].ToString(); // get item name
item.description = reader[2].ToString(); // get item description
item.unitPrice = Double.Parse(reader[3].ToString()); // get item's unit price
item.sellingPrice = Double.Parse(reader[4].ToString()); // get item's selling price
item.category = reader[5].ToString(); // get item categoy
item.imageURL = reader[6].ToString(); // get image URL
items.Add(item);
}
}
}
// Close database connection
database.CloseDatabaseConnection();
return(items);
}
/// <summary>
/// Gets an item from the database using the item ID.
/// </summary>
/// <param name="itemID"></param>
/// <returns></returns>
public static Item GetItemByID(int itemID)
{
Item item = null;
// Initialise database connection
DatabaseFacilitator database = new DatabaseFacilitator();
database.ConnectToDatabase(); // connect
string query = "SELECT * FROM [DBO].[Item] WHERE [ITEM_ID] = @ITEM_ID"; //sql query
using (SqlCommand cmd = new SqlCommand(query, database.GetDatabaseConnection())) //create sql command
{
cmd.Parameters.Add("@ITEM_ID", SqlDbType.Int, 50).Value = itemID;
using (SqlDataReader reader = cmd.ExecuteReader()) // execute query
{
while (reader.Read()) // iterate through results
{
item = new Item();
item.itemID = Int32.Parse(reader[0].ToString()); // get item ID
item.name = reader[1].ToString(); // get item name
item.description = reader[2].ToString(); // get item description
item.unitPrice = Double.Parse(reader[3].ToString()); // get item's unit price
item.sellingPrice = Double.Parse(reader[4].ToString()); // get item's selling price
item.category = reader[5].ToString(); // get item categoy
item.imageURL = reader[6].ToString(); // get image URL
}
}
}
// Close database connection
database.CloseDatabaseConnection();
return(item);
}
/// <summary>
/// Retrieve order by its ID.
///
/// First, get the order, ensuring it exists in the database.
///
/// Secondly, if order exists get all corresponding line items.
///
///
/// </summary>
/// <param name="orderid"></param>
/// <returns>returns null if not existent.</returns>
public static Order GetOrderByID(int orderid)
{
Order order = null;
// Initialise database connection
DatabaseFacilitator database = new DatabaseFacilitator();
database.ConnectToDatabase(); // connect
// firstly, get order.
string query = "SELECT * FROM [DBO].[Order] WHERE [ORDER_ID] = @ORDER_ID"; //sql query
using (SqlCommand cmd = new SqlCommand(query, database.GetDatabaseConnection())) //create sql command
{
cmd.Parameters.Add("@ORDER_ID", SqlDbType.Int, 50).Value = orderid;
using (SqlDataReader reader = cmd.ExecuteReader()) // execute query
{
while (reader.Read()) // iterate through results - should only be 1.
{
order = new Order();
order.orderID = Int32.Parse(reader[0].ToString()); // get order ID
order.customer = new Customer();
order.customer.customerID = Int32.Parse(reader[1].ToString()); // get customer ID, populate rest later.
order.date = DateTime.Parse(reader[2].ToString());
// TODO; CONVERT TO SWITCH STATEMENT
if (reader[3].ToString() == "Pending")
{
order.status = OrderStatus.Pending;
}
else if (reader[3].ToString() == "Delivered")
{
order.status = OrderStatus.Delivered;
}
else if (reader[3].ToString() == "New")
{
order.status = OrderStatus.New;
}
else if (reader[3].ToString() == "Shipped")
{
order.status = OrderStatus.Shipped;
}
else if (reader[3].ToString() == "Closed")
{
order.status = OrderStatus.Closed;
}
order.deliveryAddress = reader[4].ToString();
}
}
}
// Close database connection
database.CloseDatabaseConnection();
// Get customer using id from order
order.customer = GetCustomerByID(order.customer.customerID);
// get order line items
order.orderLineItems = GetOrderLineItemsByOrderID(order.orderID);
return(order);
}
