public static async Task <IEnumerable <ProductType> > GetProductTypesAsync(this IdapadDataAccess dataAccess)
{
string sql = "SELECT [Id], [Name] FROM dbo.ProductTypes";
var productTypes = await dataAccess.QueryAsync <ProductType>(sql);
return(productTypes);
}
public static async Task <PagedResults <Product> > GetProductByAsync(this IdapadDataAccess dataAccess,
int page, int pageSize, string sort, int?brandId, int?typeId, string search)
{
string sql = "SELECT p.[Id], p.[Name], p.[Description], p.[Price], p.[PictureUrl]," +
"b.[Name] ProductBrand, t.[Name] ProductType " +
"FROM Products p " +
"INNER JOIN ProductBrands b on p.ProductBrandId = b.id " +
"INNER JOIN ProductTypes t on p.ProductTypeId = t.id " +
"WHERE P.ProductBrandId = isnull(@Brandid, P.ProductBrandId) " +
"AND P.ProductTypeId = isnull(@Typeid, P.ProductTypeId) " +
"AND p.[Name] like '%' + isnull(@Search, P.Name) + '%' " +
"ORDER BY " +
"case when @Sort = 'priceAsc' THEN p.Price END ASC, " +
"case when @Sort = 'priceDesc' THEN p.Price END DESC, " +
"case when isnull(@Sort, 'name') = 'name' then p.[Name] end ASC, " +
"case when @Sort = 'namedesc' then p.[Name] end DESC " +
"OFFSET @Offset ROWS " +
"FETCH NEXT @PageSize ROWS ONLY";
string countSql = "SELECT count(*) " +
"FROM Products p " +
"INNER JOIN ProductBrands b on p.ProductBrandId = b.id " +
"INNER JOIN ProductTypes t on p.ProductTypeId = t.id " +
"WHERE P.ProductBrandId = isnull(@Brandid, P.ProductBrandId) " +
"AND P.ProductTypeId = isnull(@Typeid, P.ProductTypeId) " +
"AND p.[Name] like '%' + isnull(@Search, P.Name) + '%'";
var results = new PagedResults <Product>();
var TCparameters = new
{
BrandId = brandId,
TypeId = typeId,
Search = search
};
var TotalCount = await dataAccess.QueryFirstOrDefaultAsync <int>(countSql, TCparameters);
var parameters = new
{
Offset = (page - 1) * pageSize,
PageSize = pageSize,
Sort = sort,
BrandId = brandId,
TypeId = typeId,
Search = search
};
var products = await dataAccess.QueryAsync <Product>(
sql, parameters);
results.Items = products;
results.TotalCount = TotalCount;
return(results);
}
public static async Task <IEnumerable <OrderDelivery> > GetDeliveryMethodsAsync(this IdapadDataAccess dataAccess)
{
var sql = "SELECT[Id],[ShortName],[DeliveryTime],[Description],[Price] " +
" FROM[dbo].[DeliveryMethod] ";
var deliveryMethod = await dataAccess.QueryAsync <OrderDelivery>(sql);
return(deliveryMethod);
}
public static async Task <IEnumerable <FirmProduct> > GetFirmProductItemAsync(this IdapadDataAccess dataAccess, int id)
{
string sql = "SELECT f.[Id], p.[Name] FROM dbo.FirmProduct f " +
"INNER JOIN dbo.Product p on f.ProductId = p.Id " +
"WHERE f.Id = " + id;
var firmproduct = await dataAccess.QueryAsync <FirmProduct>(sql);
return(firmproduct);
}
public static async Task <IEnumerable <FirmOrder> > GetOrderByIdAsync(this IdapadDataAccess dataAccess, string email, int id)
{
string SQL = "SELECT o.[BuyerEmail], o.[OrderDate], o.[DeliveryMethodId],o.[ShipAddressId], " +
"o.[Subtotal],o.[Status], o.[PaymentInterestId], i.[FirmProductId], i.[Price], " +
"i.[Quantity], i.[SizeId], i.[OrderId] FROM [dbo].[Orders] o " +
"INNER JOIN [dbo].[OrderItems] i ON o.Id = i.OrderId " +
" WHERE o.BuyerEmail = '" + email + "'" +
" AND o.id = " + id;
var productItemOrdered = await dataAccess.QueryAsync <FirmOrder>(SQL);
return(productItemOrdered);
}
public static async Task <List <FirmOrder> > GetOrderAsync(this IdapadDataAccess dataAccess, string email)
{
string SQL = "SELECT o.Id, o.[BuyerEmail], o.[OrderDate], o.[DeliveryMethodId], o.[ShipAddressId], " +
"o.[Subtotal], o.[Status], o.[PaymentInterestId], " +
"i.[FirmProductId], i.[FirmProductId], p.Id ProductId, p.[Name] ProductName, " +
"p.[Description] ProductDescription, p.PictureUrl ProductPictureUrl, " +
"i.[Price], i.[Quantity], i.[SizeId], i.[OrderId], i.Id OrderItemId, " +
"d.ShortName DelShortName, d.[DeliveryTime], d.[Description] DelDescription, d.[Price] DelPrice, " +
"a.[StreetAddress], a.[AptAddress], a.[City], a.[State], a.[ZipCode] " +
"FROM [dbo].[Orders] o " +
"INNER JOIN [dbo].[OrderItems] i ON o.Id = i.OrderId " +
"INNER JOIN [dbo].[FirmProducts] fp ON i.FirmProductId = fp.Id " +
"INNER JOIN [dbo].[Products] p ON fp.ProductId = p.Id " +
"INNER JOIN [dbo].[Firm] f ON i.FirmProductId = f.Id " +
"INNER JOIN [dbo].[DeliveryMethod] d on o.[DeliveryMethodId] = d.Id " +
"INNER JOIN FirmAddress a on a.Id = o.Id " +
"WHERE o.BuyerEmail = '" + email + "'";
var ordersflat = await dataAccess.QueryAsync <OrderFlat>(SQL);
List <FirmOrder> orders = new List <FirmOrder>();
//
HashSet <int> orderIds = new HashSet <int>();
foreach (var item in ordersflat)
{
if (!orderIds.Contains(item.Id))
{
var order = new FirmOrder();
order.Id = item.Id;
order.BuyerEmail = item.BuyerEmail;
order.OrderDate = item.OrderDate;
order.Subtotal = item.Subtotal;
FirmAddress shipaddress = new FirmAddress();
shipaddress.StreetAddress = item.StreetAddress;
shipaddress.AptAddress = item.AptAddress;
shipaddress.City = item.City;
shipaddress.State = item.State;
shipaddress.ZipCode = item.ZipCode;
order.ShipToAddress = shipaddress;
OrderDelivery delivery = new OrderDelivery();
delivery.DeliveryTime = item.DeliveryTime;
delivery.ShortName = item.DelShortName;
delivery.Description = item.DelDescription;
delivery.Price = item.DelPrice;
order.DeliveryMethod = delivery;
order.OrderItems = new List <FirmOrderItem>();
foreach (var orderitem in ordersflat.Where(oi => oi.OrderId == order.Id))
{
FirmOrderItem firmorderItem = new FirmOrderItem();
firmorderItem.OrderId = orderitem.OrderId;
firmorderItem.Id = orderitem.OrderItemId;
firmorderItem.Price = orderitem.Price;
firmorderItem.Quantity = orderitem.Quantity;
FirmProductItem firmProductItem = new FirmProductItem();
firmProductItem.ProductId = orderitem.FirmProductId;
firmProductItem.ProductName = orderitem.ProductName;
firmProductItem.PictureUrl = orderitem.ProductPictureUrl;
firmorderItem.ItemOrdered = firmProductItem;
order.OrderItems.Add(firmorderItem);
}
orderIds.Add(order.Id);
orders.Add(order);
}
}
return(orders);
}