public async Task <RxPriceDTO> FindAll(string name)
{
var parameters = new { Name = $"%{name}%" };
var sql = $@"SELECT rp.Id, rp.Name, rp.Quantity, rp.Dose, rp.Price,
rp.Location, v.Id AS VendorId, v.Name AS Vendor,
v.Url, u.Id AS UserId, u.Name AS Username
FROM RxPrices AS rp
INNER JOIN Vendors AS v
ON rp.VendorId = v.Id
LEFT JOIN RxPriceUsers AS ru
ON rp.Id = ru.RxPriceId
LEFT JOIN Users AS u
ON ru.UserId = u.Id
WHERE LOWER(rp.Name) LIKE LOWER(@Name)
ORDER BY rp.Price";
using (var connection = new SqlConnection(_config.GetConnectionString("Default")))
{
var rxPrices = await connection.QueryAsync(sql, parameters);
var rxPriceDTO = new RxPriceDTO
{
Method = $"Find All RxPrices: {name}",
Count = rxPrices.Count(),
RxPrices = rxPrices
};
return(rxPriceDTO);
}
}
public async Task <RxPriceDTO> FindBy(string name, string column, string value)
{
if (column.Length > 8)
{
throw new ArgumentException($"Invalid Column Argument: {column}!");
}
var parameters = new { Name = $"%{name}%", Value = value };
var sql = $@"SELECT rp.Id, rp.Name, rp.Quantity, rp.Dose, rp.Price,
rp.Location, v.Id AS VendorId, v.Name AS Vendor, v.Url
FROM RxPrices AS rp
INNER JOIN Vendors AS v
ON rp.VendorId = v.Id
WHERE LOWER(rp.Name) LIKE LOWER(@Name)
AND rp.{column} = @Value
ORDER BY rp.Price";
using (var connection = new SqlConnection(_config.GetConnectionString("Default")))
{
var rxPrices = await connection.QueryAsync(sql, parameters);
var rxPriceDTO = new RxPriceDTO
{
Method = $"Find {name} By {column}: {value}",
Count = rxPrices.Count(),
RxPrices = rxPrices
};
return(rxPriceDTO);
}
}
public async Task <RxPriceDTO> FindMedication(string name, string location, float price)
{
var parameters = new { Name = $"%{name}%", Location = $"%{location}%", Price = price };
var sql = @"SELECT rp.Id, rp.Name, rp.Quantity, rp.Dose, rp.Price,
rp.Location, v.Id AS VendorId, v.Name AS Vendor, v.Url
FROM RxPrices AS rp
INNER JOIN Vendors AS v
ON rp.VendorId = v.Id
WHERE LOWER(rp.Name) LIKE LOWER(@Name)
AND LOWER(rp.Location) LIKE LOWER(@Location)
AND rp.Price <= @Price
ORDER BY rp.Price";
using (var connection = new SqlConnection(_config.GetConnectionString("Default")))
{
var rxPrices = await connection.QueryAsync(sql, parameters);
var rxPriceDTO = new RxPriceDTO
{
Method = $"Get Medication: {name}",
Count = rxPrices.Count(),
RxPrices = rxPrices
};
return(rxPriceDTO);
}
}
