public JObject GetTableHomeWeek([FromRoute] double longy, double lat)
{
string _selectString = "SELECT day.id AS day, day.description, SUM(act.people) AS people FROM \"MtcActivitys\" AS act INNER JOIN \"Days\" as day ON act.day = day.id INNER JOIN \"Mtcs\" AS zone ON act.zone = zone.id WHERE ST_Contains(zone.geom, ST_SetSRID(ST_MakePoint(" + longy + ", " + lat + "), 4326)) GROUP BY day.id HAVING SUM(people) >= ALL(SELECT SUM(people) FROM \"MtcActivitys\" AS act INNER JOIN \"Mtcs\" AS zone ON act.zone = zone.id WHERE ST_Contains(zone.geom, ST_SetSRID(ST_MakePoint(" + longy + ", " + lat + "), 4326)) GROUP BY day) OR SUM(people) <= ALL(SELECT SUM(people) FROM \"MtcActivitys\" AS act INNER JOIN \"Mtcs\" AS zone ON act.zone = zone.id WHERE ST_Contains(zone.geom, ST_SetSRID(ST_MakePoint(" + longy + ", " + lat + "), 4326)) GROUP BY day) ORDER BY people DESC";
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
using (var cmd = new NpgsqlCommand(_selectString, conn))
{
using (var reader = cmd.ExecuteReader())
{
List <TableHomeWeekDTO> tableHomeWeekDtoList = new List <TableHomeWeekDTO>();
while (reader.Read())
{
InterfaceDataReader dataReader = new DataReader();
TableHomeWeekDTO tableHomeWeekDTO = dataReader.ReadTableHomeWeekDTO(reader);
tableHomeWeekDtoList.Add(tableHomeWeekDTO);
}
IObjectConverter objConverted = new ObjectConverter();
var obj = objConverted.TableHomeWeekJson(tableHomeWeekDtoList);
return(obj);
}
}
}
}
public TableHomeWeekDTO ReadTableHomeWeekDTO(NpgsqlDataReader reader)
{
int id = reader.GetInt32(0);
string day = reader.GetString(1);
int people = reader.GetInt32(2);
TableHomeWeekDTO tableHomeWeekDTO = new TableHomeWeekDTO
{
Id = id,
Day = day,
People = people
};
return(tableHomeWeekDTO);
}
