public async Task <Order> GetOrderAsync(int id)
{
using (var connection = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
var result = await connection.QueryAsync <dynamic>(
@"select o.Id as ordernumber,o.OrderDate as date, o.Description as description,
o.Address_City as city, o.Address_Country as country, o.Address_State as state, o.Address_Street as street, o.Address_ZipCode as zipcode,
os.Name as status,
oi.ProductName as productname, oi.Units as units, oi.UnitPrice as unitprice, oi.PictureUrl as pictureurl
FROM `orders` o
LEFT JOIN `orderItems` oi ON o.Id = oi.orderid
LEFT JOIN `orderstatus` os on o.OrderStatusId = os.Id
WHERE o.Id=@id"
, new { id }
);
if (result.AsList().Count == 0)
{
throw new KeyNotFoundException();
}
return(MapOrderItems(result));
}
}
public async Task <IEnumerable <CardType> > GetCardTypesAsync()
{
using (var connection = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
return(await connection.QueryAsync <CardType>("SELECT * FROM `cardtypes`"));
}
}
public async Task <IEnumerable <OrderSummary> > GetOrdersAsync()
{
using (var connection = new MySqlConnectorAlias::MySql.Data.MySqlClient.MySqlConnection(_connectionString))
{
connection.Open();
return(await connection.QueryAsync <OrderSummary>(@"SELECT o.Id as ordernumber,o.OrderDate as date,os.Name as status,SUM(oi.units*oi.unitprice) as total
FROM `orders` o
LEFT JOIN `orderItems` oi ON o.Id = oi.orderid
LEFT JOIN `orderstatus` os on o.OrderStatusId = os.Id
GROUP BY o.Id, o.OrderDate, os.Name
ORDER BY o.Id"));
}
}
