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