public JObject GetTableHomeDay([FromRoute] int day, double longy, double lat)
{
string _selectString = "SELECT day.id AS day, day.description, act.hour, act.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)) AND day = " + day + " AND (people = (SELECT MAX(act.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)) AND act.day = " + day + ") OR people = (SELECT MIN(act.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)) AND act.day = " + 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 <TableHomeDayDTO> tableHomeDayDtoList = new List <TableHomeDayDTO>();
while (reader.Read())
{
InterfaceDataReader dataReader = new DataReader();
TableHomeDayDTO tableHomeDayDTO = dataReader.ReadTableHomeDayDTO(reader);
tableHomeDayDtoList.Add(tableHomeDayDTO);
}
IObjectConverter objConverted = new ObjectConverter();
var obj = objConverted.TableHomeDayJson(tableHomeDayDtoList);
return(obj);
}
}
}
}
public TableHomeDayDTO ReadTableHomeDayDTO(NpgsqlDataReader reader)
{
int id = reader.GetInt32(0);
string day = reader.GetString(1);
int hour = reader.GetInt32(2);
int people = reader.GetInt32(3);
TableHomeDayDTO tableHomeDayDTO = new TableHomeDayDTO
{
Id = id,
Day = day,
Hour = hour,
People = people
};
return(tableHomeDayDTO);
}