Ejemplo n.º 1
0
        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);
        }