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); }