//Orders kunen maar een paar statussen hebben
internal bool CreateOrder(Order order)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "INSERT INTO \"order\"(date_time, user_id, order_status)"
+ "VALUES(@date_time, @user_id, @order_status);";
cmd.Parameters.AddWithValue("date_time", order.DTime);
cmd.Parameters.AddWithValue("user_id", (long)order.User.Id);
cmd.Parameters.AddWithValue("order_status", (int)order.Status);
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
foreach (OrderLine ol in order.OrderLines)
{
CreateOrderline(ol, GetOrderId(order));
}
return success; //Commit als het sucessvol is
}
_transaction.Rollback();
_transaction.Dispose();
return success;
}
}
public bool placeOrderToAddress(int id)
{
var result = getCurrentUser().Addresses.Where(a => a.AddressID == id);
if (result.Count() == 1)
{
if (result.First().UserID != getCurrentUserId())
{
return false;
}
var user = getCurrentUser();
var order = new Order();
order.User = user;
order.UserID = user.UserID;
order.AddressID = id;
order.OrderTime = DateTime.Now;
foreach (var i in user.ShoppingCartItems)
{
var oi = new OrderItem();
oi.Order = order;
oi.OrderID = order.OrderID;
oi.Product = i.Product;
oi.ProductID = i.ProductID;
oi.Quantity = i.Quantity;
order.OrderItems.Add(oi);
}
var items = user.ShoppingCartItems.ToList();
for (var a = items.Count() - 1; a >= 0; --a)
{
db.ShoppingCartItems.Remove(items[a]);
}
db.Orders.Add(order);
db.SaveChanges();
}
return true;
}
public ActionResult Order(ulong orderId)
{
CheckSession();
Order order = new Order();
using (DatabaseQuery query = new DatabaseQuery())
{
order = query.GetOrder(orderId);
}
return View(order);
}
public ActionResult CreateOrder()
{
CheckSession();
Session session = ((Session)this.Session["__MySessionObject"]);
if (session.User != null)
{
Order order = new Order();
order.DTime = DateTime.Now;
order.OrderLines = session.ShoppingBag.OrderLines;
order.User = session.User;
order.Status = OrderStatus.TOBEPAID;
using (DatabaseQuery query = new DatabaseQuery())
{
query.CreateOrder(order);
}
session.ShoppingBag = new ShoppingBag();
return RedirectToAction("Account", "Home");
}
else
{
return RedirectToAction("Login", "Home");
}
}
internal bool UpdateOrderStatus(Order order)
{
//Maken van een product
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "UPDATE \"order\" SET order_status=@order_status where id = @id;";
cmd.Parameters.AddWithValue("order_status", (int)order.Status);
cmd.Parameters.AddWithValue("id", (long)order.Id);
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
return success; //Commit als het sucessvol is
}
_transaction.Rollback();
_transaction.Dispose();
return success;
}
}
internal List<Order> GetOrdersByUser(User user)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
//kan ook join gebruiken o join orderline ol on ol.order_id = o.id
cmd.CommandText = "SELECT * FROM \"order\" where user_id=@user_id";
cmd.Parameters.AddWithValue("user_id", (long)user.Id);
NpgsqlDataReader reader = cmd.ExecuteReader();
List<Order> orders = new List<Order>();
while (reader.Read())
{
Order order = new Order();
order.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
order.DTime = (DateTime)reader.GetDateTime(reader.GetOrdinal("date_time"));
order.User = this.GetUser((ulong)reader.GetInt64(reader.GetOrdinal("user_id")));
order.OrderLines = this.getOrderlines(order.Id);
order.Status = (OrderStatus)Enum.ToObject(typeof(OrderStatus), reader.GetInt64(reader.GetOrdinal("order_status")));
orders.Add(order);
}
reader.Close();
return orders;
}
}
internal List<Order> GetOrders()
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
cmd.CommandText = "SELECT * FROM \"order\" o join orderline ol on ol.order_id = o.id";
NpgsqlDataReader reader = cmd.ExecuteReader();
List<Order> orders = new List<Order>();
while (reader.Read())
{
Order order = new Order();
order.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
order.DTime = (DateTime)reader.GetDateTime(reader.GetOrdinal("date_time"));
order.User = this.GetUser((ulong)reader.GetInt64(reader.GetOrdinal("user_id")));
order.Status = (OrderStatus)Enum.ToObject(typeof(OrderStatus), reader.GetInt64(reader.GetOrdinal("order_status")));
order.OrderLines = this.getOrderlines(order.Id);
orders.Add(order);
}
reader.Close();
return orders;
//voeg alle parameters toe an de query en Execute
}
}
internal ulong GetOrderId(Order order)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
cmd.CommandText = "SELECT * FROM \"order\" where date_time=@date_time and user_id=@user_id and order_status=@order_status";
cmd.Parameters.AddWithValue("date_time", order.DTime);
cmd.Parameters.AddWithValue("user_id", (long)order.User.Id);
cmd.Parameters.AddWithValue("order_status", (int)order.Status);
NpgsqlDataReader reader = cmd.ExecuteReader();
if(reader.Read())
{
order.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
}
reader.Close();
return order.Id;
//voeg alle parameters toe an de query en Execute
}
}
internal Order GetOrder(ulong orderId)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
cmd.CommandText = "SELECT * FROM \"order\" where id=@id";
cmd.Parameters.AddWithValue("id", (long)orderId);
NpgsqlDataReader reader = cmd.ExecuteReader();
Order order = new Order();
if (reader.Read())
{
order.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
order.DTime = (DateTime)reader.GetDateTime(reader.GetOrdinal("date_time"));
order.User = this.GetUser((ulong)reader.GetInt64(reader.GetOrdinal("user_id")));
order.Status = (OrderStatus)Enum.ToObject(typeof(OrderStatus), reader.GetInt64(reader.GetOrdinal("order_status")));
order.OrderLines = this.getOrderlines(order.Id);
}
reader.Close();
return order;
//voeg alle parameters toe an de query en Execute
}
}
