public async Task <IEnumerable <CoffeeShopPreview> > GetSupplyShops(SearchSupplyShopModel query)
{
PropertyInfo[] possibleProperties = typeof(SearchSupplyShopModel).GetProperties();
var builder = new SqlBuilder();
var additionalStatements = "";
var innerjoin = "";
var mapping = MappingM2DB.CoffeShopMap;
foreach (PropertyInfo property in possibleProperties)
{
var properties = new Dictionary <string, object>();
mapping.TryGetValue(property.Name.ToLower(), out string propertyName);
if (property.GetValue(query) != null && !string.IsNullOrEmpty(propertyName) && propertyName == "bus_station_name")
{
innerjoin += " inner join reachable_by_bus r on r.coffee_shop_id = id ";
properties.Add(propertyName, query.BusStation);
builder.Where("r." + propertyName + " = ANY" + "(@bus_station_name)", properties);
}
else if (property.GetValue(query) != null && !string.IsNullOrEmpty(propertyName) && propertyName == "poi_name")
{
innerjoin += " inner join near_by n on n.coffee_shop_id = id ";
properties.Add("poi", query.Poi);
builder.Where("n.poi_name" + " = ANY" + "(@poi)", properties);
}
else if (property.GetValue(query) != null && !string.IsNullOrEmpty(propertyName) && propertyName == "equipment_category_name")
{
innerjoin += " inner join supplies s on s.coffee_shop_id = id ";
properties.Add(propertyName, query.EquipmentCategories);
builder.Where("s.equipment_category_name::text" + " = ANY" + "(@equipment_category_name)", properties);
}
else if (property.GetValue(query) != null && !string.IsNullOrEmpty(propertyName) && propertyName.Contains("bean_name"))
{
innerjoin += " inner join provides p on p.coffee_shop_id = id ";
properties.Add("beans", query.Beans);
builder.Where("p.bean_name" + " = ANY" + "(@beans)", properties);
}
else if (property.GetValue(query) != null && !string.IsNullOrEmpty(propertyName) && propertyName.Contains("blend_name"))
{
innerjoin += " inner join offers o on o.coffee_shop_id = id ";
properties.Add("blends", query.Blends);
builder.Where("o.blend_name" + " = ANY" + "(@blends)", properties);
}
else if (property.GetValue(query) != null && !string.IsNullOrEmpty(propertyName) && (propertyName == "name" || propertyName.Contains("address")))
{
properties.Add(property.Name, "%" + property.GetValue(query) + "%");
builder.Where(propertyName + "::citext" + " LIKE " + "@" + property.Name, properties);
}
else if (property.GetValue(query) != null && !string.IsNullOrEmpty(propertyName) && propertyName == "roast")
{
innerjoin += "inner join provides pr on pr.coffee_shop_id = id " +
"inner join (select provenance, roast, name as bname from bean) br on pr.bean_name = bname and br.provenance = pr.bean_provenance ";
properties.Add(property.Name, property.GetValue(query));
builder.Where(propertyName + " = " + "@" + property.Name, properties);
}
else if (property.GetValue(query) != null && !string.IsNullOrEmpty(propertyName))
{
properties.Add(propertyName, "%" + property.GetValue(query) + "%");
builder.Where(propertyName + "::text" + " LIKE " + "@" + propertyName, properties);
}
}
additionalStatements += " inner join coffee_shop_image ci on ci.coffee_shop_id = c.id ";
additionalStatements += " inner join image i on ci.image_file_name = i.file_name and i.content_type = 'preview'";
additionalStatements += " inner join rated_by_user rbu on rbu.coffee_shop_id = c.id ";
additionalStatements += " inner join user_rating ur on rbu.user_rating_id = ur.rating_id ";
// builder.Where("i.content_type = 'preview'");
using (IDbConnection dbConnection = Connection)
{
var sql = "Select distinct on (id) id, * from (Select distinct c.*, i.file_name, to_char(AVG (ur.total),'9D9') as average_total from coffee_shop c " + additionalStatements + " group by c.id, i.file_name" +
" union select c1.*, null as file_name, null as average_total from coffee_shop c1) as t " + innerjoin + " /**where**/ order by id, file_name";
var filterCoffeeShops = builder.AddTemplate(sql);
Console.WriteLine(sql);
if (possibleProperties.Length == 0)
{
return(await GetAll());
}
var result = await dbConnection.QueryAsync <CoffeeShopPreview>(filterCoffeeShops.RawSql, filterCoffeeShops.Parameters);
return(result.ToList());
}
}
public async Task <IEnumerable <CoffeeShopPreview> > GetSupplyShops([FromQuery] SearchSupplyShopModel searchQuery)
{
var coffeeShops = await _coffeeShopRepo.GetSupplyShops(searchQuery);
return(coffeeShops);
}
