public OrderModel LoadSingleOrder(int orderID)
{
CakeServices cakeServices = new CakeServices();
OrderModel result = new OrderModel();
string sqlString = $"SELECT * FROM BILL WHERE ID = {orderID}";
var cakesInOrderList = new List <CakeInOrder>();
var cakesIDList = new List <int>();
using (var cnn = new SQLiteConnection(_connectionString))
{
// load order information
result = cnn.QueryFirst <OrderModel>(sqlString, new DynamicParameters());
// load list cake in order
cakesInOrderList = cnn.Query <CakeInOrder>($"SELECT * FROM CAKEINORDER WHERE ORDERID = {orderID}", new DynamicParameters())
.ToList();
foreach (var x in cakesInOrderList)
{
x.cake = cakeServices.loadSingleCake(x.CakeID);
}
result.listCakes = cakesInOrderList;
}
return(result);
}
public List <OrderModel> LoadAllOrder()
{
CakeServices cakeServices = new CakeServices();
List <OrderModel> result = new List <OrderModel>();
List <int> listOrderID = new List <int>();
using (var cnn = new SQLiteConnection(_connectionString))
{
// load all orderid
listOrderID = cnn.Query <int>("SELECT ID FROM BILL").ToList();
}
foreach (var orderId in listOrderID)
{
result.Add(LoadSingleOrder(orderId));
}
return(result);
}
public List <OrderModel> LoadAllOrderInYear(int year)
{
CakeServices cakeServices = new CakeServices();
List <OrderModel> result = new List <OrderModel>();
List <int> listOrderID = new List <int>();
using (var cnn = new SQLiteConnection(_connectionString))
{
// load all orderid
listOrderID = cnn.Query <int>("SELECT ID FROM BILL").ToList();
}
foreach (var orderId in listOrderID)
{
result.Add(LoadSingleOrder(orderId));
}
// FILTER RESULT WITH YEAR
result = result.Where(r => DateTime.ParseExact(r.Date, "dd-MM-yyyy", CultureInfo.InvariantCulture).Year == year).ToList();
return(result);
}