public JsonResult AddOrder(string job, string media, string mediaCatalog, string content)
{
MyDatabaseEntities ctx = new MyDatabaseEntities();
Order o = new Order();
o.JobType = job;
o.Media = media;
o.CatalogNumber = int.Parse(mediaCatalog);
o.Content = content;
o.UserId = int.Parse(Session["id"].ToString());
o.Status = "PENDING";
string response = "failed";
ctx.Orders.Add(o);
ctx.SaveChanges();
response = "success";
return this.Json(response, JsonRequestBehavior.AllowGet);
}
public JsonResult AddOrder(string payOption)
{
MyDatabaseEntities ctx = new MyDatabaseEntities();
Order o = new Order();
o.JobType = Session["job"].ToString();
o.Media = Session["media"].ToString();
o.CatalogNumber = int.Parse(Session["mediaCatalog"].ToString());
o.Content = Session["content"].ToString();
o.UserId = int.Parse(Session["id"].ToString());
o.PaymentMethod = payOption;
o.Status = "PENDING";
string response = "pass";
ctx.Orders.Add(o);
ctx.SaveChanges();
return this.Json(response, JsonRequestBehavior.AllowGet);
}
public Order GetOrder(int oid)
{
Order order = null;
try
{
conn.Open();
string select = @"select * from tbl_order where order_id = @id";
MySqlCommand cmd = new MySqlCommand(select, conn);
MySqlParameter idParam = new MySqlParameter("@id", MySqlDbType.Int16);
idParam.Value = oid;
cmd.Parameters.Add(idParam);
MySqlDataReader datareader = cmd.ExecuteReader();
while (datareader.Read())
{
int id = datareader.GetInt16("order_id");
double totaal = datareader.GetDouble("totaal");
int gebruiker = datareader.GetInt16("gebruiker_id");
order = new Order { ID = id, Totaal = totaal, StrStatus = "..", aantalProducten = 0, Gebruiker = gebruiker };
}
return order;
}
catch (Exception e)
{
return null;
}
finally
{
conn.Close();
}
}
public void UpdateStatus(Order order)
{
MySqlTransaction trans = null;
try
{
conn.Open();
trans = conn.BeginTransaction();
string update = @"update tbl_order set order_status_id = @sid where order_id = @oid";
MySqlCommand cmd = new MySqlCommand(update, conn);
MySqlParameter oidParam = new MySqlParameter("@oid", MySqlDbType.Int16);
MySqlParameter sidParam = new MySqlParameter("@sid", MySqlDbType.Int16);
oidParam.Value = order.ID;
sidParam.Value = order.Status;
cmd.Parameters.Add(sidParam);
cmd.Parameters.Add(oidParam);
cmd.Prepare();
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
}
finally
{
conn.Close();
}
}
public List<OrderViewModel> GetProductenPerOrder(int oid)
{
List<OrderViewModel> orders = new List<OrderViewModel>();
try
{
conn.Open();
string select = @"select * from tbl_orderregel orr join tbl_product p on orr.product_id = p.product_id join tbl_order o on orr.order_id = o.order_id where o.order_id = @id";
MySqlCommand cmd = new MySqlCommand(select, conn);
MySqlParameter idParam = new MySqlParameter("@id", MySqlDbType.Int32);
idParam.Value = oid;
cmd.Parameters.Add(idParam);
MySqlDataReader datareader = cmd.ExecuteReader();
while (datareader.Read())
{
Product productnaam = new Product();
int id = datareader.GetInt16("order_id");
double totaal = datareader.GetDouble("totaal");
int gebruiker = datareader.GetInt16("gebruiker_id");
Order order = new Order { ID = id, Totaal = totaal, StrStatus = "..", aantalProducten = 0, Gebruiker = gebruiker };
int productID = datareader.GetInt16("Product_ID");
int aantal = datareader.GetInt16("Aantal");
double subtotaal = datareader.GetDouble("Subtotaal");
productnaam = productdbcontroller.GetProduct(productID);
OrderRegel orderregel = new OrderRegel { ProductID = productID, OrderID = oid, ProductNaam = productnaam.Naam, Aantal = aantal, Subtotaal = subtotaal };
OrderViewModel orderview = new OrderViewModel { Order = order, Orderregel = orderregel };
orders.Add(orderview);
}
return orders;
}
catch (Exception e)
{
return null;
}
finally
{
conn.Close();
}
}
public List<Order> GetAllOrdersPerKlant(int klantid)
{
List<Order> orders = new List<Order>();
try
{
conn.Open();
string select = @"Select o.order_id, o.totaal, os.status, Sum(orr.aantal) as aantal from tbl_order o join tbl_order_status os on o.order_status_id = os.order_status_id join tbl_orderregel orr on o.order_id = orr.order_id where gebruiker_id = @gebruikerid group by o.order_id";
MySqlCommand cmd = new MySqlCommand(select, conn);
MySqlParameter idParam = new MySqlParameter("@gebruikerid", MySqlDbType.Int16);
idParam.Value = klantid;
cmd.Parameters.Add(idParam);
MySqlDataReader datareader = cmd.ExecuteReader();
while (datareader.Read())
{
int id = datareader.GetInt16("order_id");
double totaal = datareader.GetDouble("totaal");
string status = datareader.GetString("status");
int aantal = datareader.GetInt16("aantal");
Order order = new Order { ID = id, Totaal = totaal, StrStatus = status, aantalProducten = aantal };
orders.Add(order);
}
return orders;
}
catch (Exception e)
{
return null;
}
finally
{
conn.Close();
}
}
public List<Order> GetAllOrders(int uid, int status = -1, bool ondate = false)
{
List<Order> orderlist = new List<Order>();
try
{
conn.Open();
String con = null;
if (status > 0)
{
con += " AND o.Order_status_ID=" + uid;
}
if (ondate)
{
con += " AND (o.Create_date > DATE_SUB(NOW(), INTERVAL 7 DAY))";
}
string select = @"SELECT * FROM tbl_order o JOIN tbl_order_status s ON s.Order_status_ID=o.Order_status_ID WHERE o.Gebruiker_ID='" + uid + "'" + con + " ORDER BY o.Order_ID DESC";
MySqlCommand cmd = new MySqlCommand(select, conn);
MySqlDataReader datareader = cmd.ExecuteReader();
while (datareader.Read())
{
int orderID = datareader.GetInt16("Order_ID");
DateTime createdate = datareader.GetDateTime("Create_date");
int gebruikerID = datareader.GetInt16("Gebruiker_ID");
int statusID = datareader.GetInt16("Order_status_ID");
string Status = datareader.GetString("Status");
double totaal = datareader.GetDouble("Totaal");
Order order = new Order { ID = orderID, CreateDate = createdate, Gebruiker = gebruikerID, Status = statusID, StrStatus = Status, Totaal = totaal };
orderlist.Add(order);
}
return orderlist;
}
catch (Exception e)
{
Console.WriteLine("ProductDBController GetAllProducten() " + e);
return null;
}
finally
{
conn.Close();
}
}