public JObject GetTopChart1Day()
{
string _selectString = "SELECT b.\"Gid\", b.\"Id\", a.\"DaysAct\", a.\"ZoneAct\", SUM(a.\"CountAct\") AS people, b.\"Geom\" FROM \"MtcActivitys\" a INNER JOIN \"Mtcs\" b ON a.\"ZoneAct\" = b.\"Gid\" WHERE a.\"DaysAct\" = 1 GROUP BY b.\"Gid\", b.\"Id\", a.\"DaysAct\", a.\"ZoneAct\", b.\"Geom\" ORDER BY people DESC LIMIT 5";
using (var conn = new NpgsqlConnection(connectionString))
{
conn.Open();
using (var cmd = new NpgsqlCommand(_selectString, conn))
{
using (var reader = cmd.ExecuteReader())
{
List <TopDayDTO> TopDayDtoList = new List <TopDayDTO>();
while (reader.Read())
{
InterfaceDataReader idr = new DataReader();
TopDayDTO topDayDTO = idr.ReadTopDayDTO(reader);
TopDayDtoList.Add(topDayDTO);
}
IObjectConverter objConverted = new ObjectConverter();
var obj = objConverted.TopDayPeopleJson(TopDayDtoList);
return(obj);
}
}
}
}
public TopDayDTO ReadTopDayDTO(NpgsqlDataReader reader)
{
var obj = new JObject();
int gid = reader.GetInt32(0);
int id = reader.GetInt32(1);
int daysAct = reader.GetInt32(2);
int zoneAct = reader.GetInt32(3);
int people = reader.GetInt32(4);
Geometry geom = reader.GetValue(5) as NetTopologySuite.Geometries.Geometry;
TopDayDTO topDayDTO = new TopDayDTO
{
Gid = gid,
Id = id,
DaysAct = daysAct,
ZoneAct = zoneAct,
People = people,
Geom = geom
};
return(topDayDTO);
}
