public FlatCounterDataViewModel()
{
Flat = new Flat();
Counter = new Counter();
CounterOld = new Counter();
CounterData = new CounterData();
}
public IEnumerable <Flat> GetCountersToUpdate(string streetId, string houseNumberId)
{
List <Flat> data = new List <Flat>();
using (SqlConnection conn = new SqlConnection(connString))
{
string sql = "select " +
"s.Name as Street, " +
"hn.Name as HouseNumber, " +
"f.FlatNumber, " +
"c.SerialNumber " +
"from counters c, streets s, houseNumbers hn, flats f " +
"where c.Id = f.CounterId " +
"and s.Id = @StreetId " +
"and " +
"hn.Id = @HouseNumberId " +
"and " +
"f.HouseNumberId=hn.Id " +
"and " +
"(CAST(NextCheckDate AS DATE) <= (CAST(getdate() AS DATE))) ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@StreetId", streetId);
cmd.Parameters.AddWithValue("@HouseNumberId", houseNumberId);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Flat flat = new Flat();
flat.Street = rdr["Street"].ToString();
flat.HouseNumber = rdr["HouseNumber"].ToString();
flat.FlatNumber = Convert.ToInt32(rdr["FlatNumber"]);
flat.Counter = new Counter();
flat.Counter.SerialNumber = rdr["SerialNumber"].ToString();
data.Add(flat);
}
conn.Close();
}
return(data);
}
//
public IEnumerable <Flat> GetFlats()
{
List <Flat> flats = new List <Flat>();
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand("select f.Id as flatID,f.XpathName,c.Id as CounterId, c.SerialNumber from flats f,counters c where f.CounterId=c.Id", conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Flat flat = new Flat();
flat.Id = Convert.ToInt32(rdr["flatID"]);
flat.XpathName = rdr["XpathName"].ToString();
flat.Counter = new Counter();
flat.CounterId = Convert.ToInt32(rdr["CounterId"]);
flat.Counter.Id = Convert.ToInt32(rdr["CounterId"]);
flat.Counter.SerialNumber = rdr["SerialNumber"].ToString();
flats.Add(flat);
}
conn.Close();
}
return(flats);
}