private static async Task<List<LcboStore>> GetStoresForSubdivisions(IEnumerable<int> subdivisionIds, Npgsql.NpgsqlConnection conn)
{
var result = new List<LcboStore>();
using (var getStoresCmd = conn.CreateCommand())
{
var subdivIds = string.Join(", ", subdivisionIds);
getStoresCmd.CommandText = $@"select sd.id as subdivision_id,
s.id as id,
s.name as name,
s.city as city,
ST_AsGeoJSON(s.location) as location,
s.beer_volume / 1000.0 as beer_volume,
s.wine_volume / 1000.0 as wine_volume,
s.spirits_volume / 1000.0 as spirits_volume
from subdivisions sd
inner join stores s on ST_Intersects(ST_Transform(s.location::geometry, 4269), sd.boundry)
where sd.id in ({subdivIds})
order by subdivision_id";
using (var storesReader = await getStoresCmd.ExecuteReaderAsync())
{
while (storesReader.Read())
{
result.Add(new LcboStore(storesReader));
}
}
}
return result;
}
