public IEnumerable <FlatCounterDataViewModel> GetTodayFlats()
{
List <FlatCounterDataViewModel> dataList = new List <FlatCounterDataViewModel>();
using (SqlConnection conn = new SqlConnection(connString))
{
string sql = "select f.Id as flatID,f.XpathName,c.Id as CounterId, c.SerialNumber, cd.Value,cd.MeasureDateTime, " + //
"s.Name as Street,hn.Name as HouseNumber,f.FlatNumber " + //
"from flats f,counters c, CounterData cd, streets s, houseNumbers hn " + //
"where f.CounterId = c.Id and cd.CounterId = c.Id " + //
"and s.Id = hn.StreetId " +
"and hn.Id=f.HouseNumberId " +
"and cd.Id = (select dbo.GetLastCounterDataID(f.Id))";
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
FlatCounterDataViewModel fcd = new FlatCounterDataViewModel();
// flat
fcd.Flat.Id = Convert.ToInt32(rdr["flatID"]);
fcd.Flat.XpathName = rdr["XpathName"].ToString();
fcd.Flat.Street = rdr["Street"].ToString();
fcd.Flat.HouseNumber = rdr["HouseNumber"].ToString();
fcd.Flat.FlatNumber = Convert.ToInt32(rdr["flatID"]);
// Counter
fcd.Counter.Id = Convert.ToInt32(rdr["CounterId"]);
fcd.Counter.SerialNumber = rdr["SerialNumber"].ToString();
// Counter Data
fcd.CounterData.Value = Convert.ToInt32(rdr["Value"]);
fcd.CounterData.MeasureDateTime = Convert.ToDateTime(rdr["MeasureDateTime"]);
fcd.Counters = new SelectList(GetCounters().ToList(), "Id", "SerialNumber", fcd.Counter.Id);
//
dataList.Add(fcd);
}
conn.Close();
}
return(dataList);
}