public static async Task <bool> UserExists(this IdapadDataAccess dataAccess, string userName)
{
User user = await dataAccess.QueryFirstOrDefaultAsync <User>
("select * from Appuser where username = @username", new { userName });
return((user != null) ? true : false);
}
public static async Task <User> Register(this IdapadDataAccess dataAccess, UserRegister userRegister)
{
byte[] passwordHash, passwordSalt;
CreatePasswordHash(userRegister.Password, out passwordHash, out passwordSalt);
var userToRegister = new UserToRegister
{
FirstName = userRegister.FirstName,
LastName = userRegister.LastName,
UserName = userRegister.UserName.ToLower(),
EmailAddress = userRegister.EmailAddress,
PasswordHash = passwordHash,
PasswordSalt = passwordSalt
};
var id = await dataAccess.ExecuteScalarAsync <int>(
@"INSERT Appuser(FirstName, LastName, UserName, EmailAddress, PasswordHash, PasswordSalt)" +
" output inserted.Id VALUES(@FirstName, @LastName, @UserName, @EmailAddress, @PasswordHash, @PasswordSalt)",
userToRegister);
var user = new User();
user.Id = id;
user.FirstName = userToRegister.FirstName;
user.LastName = userToRegister.LastName;
user.UserName = userToRegister.UserName;
user.EmailAddress = userToRegister.EmailAddress;
return(user);
}
public static Task <User> GetUserByUserNameAsync(this IdapadDataAccess dataAccess, string userName)
{
return(dataAccess.QueryFirstOrDefaultAsync <User>
("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress " +
"from Appuser a " +
"where a.UserName = @UserName", new { userName }));
}
public static async Task <int> LinkFirmToUserAsync(this IdapadDataAccess dataAccess,
FirmUser firmUser)
{
return(await dataAccess.ExecuteScalarAsync <int>(
"Insert FirmUsers (FirmId, UserId) output inserted.Id values" +
"(@FirmId, @UserId)", firmUser));
}
public static async Task <User> Login(this IdapadDataAccess dataAccess, UserRegister userRegister)
{
var userName = userRegister.UserName.ToLower();
UserToRegister userToRegister = await dataAccess.QueryFirstOrDefaultAsync <UserToRegister>
("select a.*,fu.FirmId, f.Name FirmName from Appuser a " +
"left join FirmUsers fu On a.Id = fu.UserId " +
"left join Firm f On f.Id = fu.firmId " +
"where a.UserName = @UserName", new { userName });
if (userToRegister == null)
{
return(null);
}
if (!VerifyPasswordHash(userRegister.Password, userToRegister.PasswordHash, userToRegister.PasswordSalt))
{
return(null);
}
var user = new User();
user.Id = userToRegister.Id;
user.FirstName = userToRegister.FirstName;
user.LastName = userToRegister.LastName;
user.UserName = userToRegister.UserName;
user.EmailAddress = userToRegister.EmailAddress;
user.FirmName = userToRegister.FirmName;
user.FirmId = userToRegister.FirmId;
return(user);
}
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);
}
private static async Task <int> DoestheFirmExists(this IdapadDataAccess dataAccess,
Firm firm)
{
int firmId = await dataAccess.QueryFirstOrDefaultAsync <int>(
"select * from Firm where Name = @Name", firm);
return(firmId);
}
private static async Task <int> DoestheFirmProductExists(this IdapadDataAccess dataAccess,
FirmProduct firmProduct)
{
int firmProductId = await dataAccess.QueryFirstOrDefaultAsync <int>(
"select Id from FirmProducts where ProductId = @ProductId and FirmId = @FirmId", firmProduct);
return(firmProductId);
}
public static async Task <bool> RemoveFirmProductAsync(this IdapadDataAccess dataAccess,
int Id)
{
await dataAccess.ExecuteScalarAsync <bool>(
"Delete FirmProducts WHERE Id = " + Id);
return(true);
}
public static Task <User> GetUserAsync(this IdapadDataAccess dataAccess, int id)
{
return(dataAccess.QueryFirstOrDefaultAsync <User>
("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress, " +
"f.Name FirmName, f.Type FirmType from Appuser a " +
"left join FirmUsers fu On a.Id = fu.UserId " +
"inner join Firm f On f.Id = fu.firmId " +
"where a.Id = @id", new { id }));
}
public static Task <User> GetFirmUserByUserNameAsync(this IdapadDataAccess dataAccess, string userName)
{
return(dataAccess.QueryFirstOrDefaultAsync <User>
("select a.Id, a.FirstName, a.LastName, a.UserName, a.EmailAddress, " +
"f.Name FirmName, f.Type FirmType from Appuser a " +
"left join FirmUsers fu On a.Id = fu.UserId " +
"left join Firm f On f.Id = fu.firmId " +
"where a.UserName = @UserName", new { userName }));
}
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);
}
private static async Task <int> DoestheAddressExists(this IdapadDataAccess dataAccess,
FirmAddress firmaddress)
{
int firmAddressId = await dataAccess.QueryFirstOrDefaultAsync <int>(
"select Id from FirmAddress where " +
"StreetAddress = @StreetAddress " +
"AND isnull(AptAddress, 'AptAddress') = ISNULL(@AptAddress, isnull(AptAddress, 'AptAddress')) " +
"AND City = @City AND State =@State AND ZipCode= @ZipCode", firmaddress);
return(firmAddressId);
}
public static async Task <int> AddFirmProductAsync(this IdapadDataAccess dataAccess,
FirmProduct firmProduct)
{
var firmProductId = await DoestheFirmProductExists(dataAccess, firmProduct);
if (firmProductId < 1)
{
return(await dataAccess.ExecuteScalarAsync <int>(
"Insert FirmProducts (ProductId, FirmId) output inserted.Id values(@ProductId, @FirmId)", firmProduct));
}
return(firmProductId);
}
public static async Task <int> CreateFirmAsync(this IdapadDataAccess dataAccess,
Firm firm)
{
var firmId = await DoestheFirmExists(dataAccess, firm);
if (firmId < 1)
{
return(await dataAccess.ExecuteScalarAsync <int>(
"Insert Firm (Name, Type, AddressId) output inserted.Id values(@Name, @Type, @AddressId)", firm));
}
return(firmId);
}
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 <int> CreateFirmAddressAsync(this IdapadDataAccess dataAccess,
FirmAddress firmaddress)
{
var firmAddressId = await DoestheAddressExists(dataAccess, firmaddress);
if (firmAddressId < 1)
{
return(await dataAccess.ExecuteScalarAsync <int>(
"Insert FirmAddress (TypeId, StreetAddress, AptAddress, City, State, ZipCode) output inserted.Id values" +
"(1, @StreetAddress, @AptAddress, @City, @State, @ZipCode)", firmaddress));
}
return(firmAddressId);
}
public static async Task <Product> GetProductByIdAsync(this IdapadDataAccess dataAccess, int id)
{
string sql = "SELECT p.[Id], p.[Name], p.[PictureUrl], p.[Price], p.[Description], " +
"b.[Name] ProductBrand, t.[Name] ProductType " +
"FROM[dbo].[Products] p INNER JOIN ProductBrands b on p.ProductBrandId = b.id " +
"INNER JOIN ProductTypes t on p.ProductTypeId = t.id " +
"WHERE p.Id = @Id ";
var parameters = new { Id = id };
var product = await dataAccess.QueryFirstOrDefaultAsync <Product>(
sql, parameters, null, (int?)CommandType.StoredProcedure);
return(product);
}
public static async Task <int> CreateOrderAsync(this IdapadDataAccess dataAccess, RedisDataAccess basketAccess,
FirmOrder order, string Email, int DeliveryMethodId, int AddressId)
{
var basket = await basketAccess.GetBasketAsync(order.BasketId);
decimal orderSubTotal = 0;
var orderCreate = new
{
BuyerEmail = Email,
OrderDate = order.OrderDate,
DeliveryMethodId = DeliveryMethodId,
ShipAddressId = AddressId,
Status = order.Status
};
var orderid = await dataAccess.ExecuteScalarAsync <int>(
"Insert Orders (BuyerEmail, OrderDate, DeliveryMethodId, ShipAddressId, Status) " +
"output inserted.Id " +
"values(@BuyerEmail, @OrderDate, @DeliveryMethodId, @ShipAddressId, @Status)", orderCreate);
var items = new List <FirmOrderItem>();
foreach (var item in basket.Items)
{
//var productItem = await dataAccess.GetFirmProductItemAsync(item.Id);
var orderItemCreate = new
{
FirmProductId = item.Id,
Price = item.Price,
Quantity = item.Quantity,
orderid = orderid,
};
var orderitemid = await dataAccess.ExecuteScalarAsync <int>(
"INSERT INTO[dbo].[OrderItems]([FirmProductId],[Price],[Quantity],[OrderId]) " +
" VALUES( @FirmProductId, @Price, @Quantity, @OrderId)", orderItemCreate);
orderSubTotal = orderSubTotal + (decimal)(item.Quantity * item.Price);
}
await dataAccess.ExecuteAsync(
"UPDATE Orders SET SubTotal = " + orderSubTotal +
" WHERE Id = " + orderid);
return(orderid);
}
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);
}
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);
}
