public async Task <MonitorData> FindReadingsByMonitorAsync(MonitorRoomAlternateKey monitorRoomAltKey) { using SqlConnection connection = new SqlConnection(_connectionString); await connection.OpenAsync(); MonitorData result = new MonitorData() { Monitor = monitorRoomAltKey.Monitor, ApplicationMeasureData = new List <ApplicationMeasureData>(), ApplicationSwitchData = new List <ApplicationSwitchData>() }; var measureData = await connection.QueryAsync <ApplicationMeasureData, SensorMeasureData, ApplicationMeasureData>( @" SELECT ApplicationMeasure AS Application, ReadingDtm, [Value] FROM ReadingMeasure WHERE HotelChain = @HotelChain AND CountryCode = @CountryCode AND Town = @Town AND Suburb = @Suburb AND RoomType = @RoomType AND Room = @Room AND Monitor = @Monitor ORDER BY ReadingDtm", (application, sensorData) => { ApplicationMeasureData applicationMeasureData = result.ApplicationMeasureData.FirstOrDefault(a => a.Application == application.Application); if (applicationMeasureData == null) { result.ApplicationMeasureData.Add(application); applicationMeasureData = application; applicationMeasureData.SensorData = new List <SensorMeasureData>(); } applicationMeasureData.SensorData.Add(sensorData); return(applicationMeasureData); }, monitorRoomAltKey, splitOn : "ReadingDtm" ); var switchData = await connection.QueryAsync <ApplicationSwitchData, SensorSwitchData, ApplicationSwitchData>( @" SELECT ApplicationSwitch AS Application, ReadingDtm, [Value] FROM ReadingSwitch WHERE HotelChain = @HotelChain AND CountryCode = @CountryCode AND Town = @Town AND Suburb = @Suburb AND RoomType = @RoomType AND Room = @Room AND Monitor = @Monitor ORDER BY ReadingDtm", (application, sensorData) => { ApplicationSwitchData applicationSwitchData = result.ApplicationSwitchData.FirstOrDefault(a => a.Application == application.Application); if (applicationSwitchData == null) { result.ApplicationSwitchData.Add(application); applicationSwitchData = application; applicationSwitchData.SensorData = new List <SensorSwitchData>(); } applicationSwitchData.SensorData.Add(sensorData); return(applicationSwitchData); }, monitorRoomAltKey, splitOn : "ReadingDtm" ); return(result); }
public async Task <IEnumerable <HotelData> > FindReadingsAsync(MonitorRoomAlternateKey monitorRoomAltKey) { using SqlConnection connection = new SqlConnection(_connectionString); await connection.OpenAsync(); List <HotelData> result = new List <HotelData>(); var measureData = await connection.QueryAsync <HotelData, RoomData, MonitorData, ApplicationMeasureData, SensorMeasureData, HotelData>( @" SELECT HotelChain, CountryCode, Town, Suburb, RoomType, Room, Monitor, ApplicationMeasure AS Application, ReadingDtm, [Value] FROM ReadingMeasure WHERE HotelChain = @HotelChain AND CountryCode = @CountryCode AND Town = @Town AND Suburb = @Suburb AND Room = @Room AND RoomType = @RoomType AND Monitor = @Monitor ORDER BY ReadingDtm", (hotel, room, monitor, application, sensorData) => { HotelKey hotelKey = new HotelKey { HotelChain = hotel.HotelChain, CountryCode = hotel.CountryCode, Town = hotel.Town, Suburb = hotel.Suburb }; HotelData hotelData = result.FirstOrDefault(h => h.HotelChain == hotel.HotelChain && h.CountryCode == hotel.CountryCode && h.Town == hotel.Town && h.Suburb == hotel.Suburb); if (hotelData == null) { result.Add(hotel); hotelData = hotel; hotelData.RoomData = new List <RoomData>(); } RoomData roomData = hotelData.RoomData.FirstOrDefault(r => r.RoomType == room.RoomType && r.Room == room.Room); if (roomData == null) { hotelData.RoomData.Add(room); roomData = room; roomData.MonitorData = new List <MonitorData>(); } MonitorData monitorData = roomData.MonitorData.FirstOrDefault(m => m.Monitor == monitor.Monitor); if (monitorData == null) { roomData.MonitorData.Add(monitor); monitorData = monitor; monitorData.ApplicationMeasureData = new List <ApplicationMeasureData>(); monitorData.ApplicationSwitchData = new List <ApplicationSwitchData>(); } ApplicationMeasureData applicationMeasureData = monitorData.ApplicationMeasureData.FirstOrDefault(a => a.Application == application.Application); if (applicationMeasureData == null) { monitorData.ApplicationMeasureData.Add(application); applicationMeasureData = application; applicationMeasureData.SensorData = new List <SensorMeasureData>(); } applicationMeasureData.SensorData.Add(sensorData); return(hotelData); }, monitorRoomAltKey, splitOn : "HotelChain, RoomType, Monitor, Application, ReadingDtm" ); var switchData = await connection.QueryAsync <HotelData, RoomData, MonitorData, ApplicationSwitchData, SensorSwitchData, HotelData>( @" SELECT HotelChain, CountryCode, Town, Suburb, RoomType, Room, Monitor, ApplicationSwitch AS Application, ReadingDtm, [Value] FROM ReadingSwitch WHERE HotelChain = @HotelChain AND CountryCode = @CountryCode AND Town = @Town AND Suburb = @Suburb AND Room = @Room AND RoomType = @RoomType AND Monitor = @Monitor ORDER BY ReadingDtm", (hotel, room, monitor, application, sensorData) => { HotelKey hotelKey = new HotelKey { HotelChain = hotel.HotelChain, CountryCode = hotel.CountryCode, Town = hotel.Town, Suburb = hotel.Suburb }; HotelData hotelData = result.FirstOrDefault(h => h.HotelChain == hotel.HotelChain && h.CountryCode == hotel.CountryCode && h.Town == hotel.Town && h.Suburb == hotel.Suburb); if (hotelData == null) { result.Add(hotel); hotelData = hotel; hotelData.RoomData = new List <RoomData>(); } RoomData roomData = hotelData.RoomData.FirstOrDefault(r => r.RoomType == room.RoomType && r.Room == room.Room); if (roomData == null) { hotelData.RoomData.Add(room); roomData = room; roomData.MonitorData = new List <MonitorData>(); } MonitorData monitorData = roomData.MonitorData.FirstOrDefault(m => m.Monitor == monitor.Monitor); if (monitorData == null) { roomData.MonitorData.Add(monitor); monitorData = monitor; monitorData.ApplicationMeasureData = new List <ApplicationMeasureData>(); monitorData.ApplicationSwitchData = new List <ApplicationSwitchData>(); } ApplicationSwitchData applicationSwitchData = monitorData.ApplicationSwitchData.FirstOrDefault(a => a.Application == application.Application); if (applicationSwitchData == null) { monitorData.ApplicationSwitchData.Add(application); applicationSwitchData = application; applicationSwitchData.SensorData = new List <SensorSwitchData>(); } applicationSwitchData.SensorData.Add(sensorData); return(hotelData); }, monitorRoomAltKey, splitOn : "HotelChain, RoomType, Monitor, Application, ReadingDtm" ); return(result); }
public async Task <RoomData> FindReadingsByRoomAsync(RoomKey roomKey) { using SqlConnection connection = new SqlConnection(_connectionString); await connection.OpenAsync(); RoomData result = new RoomData() { RoomType = roomKey.RoomType, Room = roomKey.Name, MonitorData = new List <MonitorData>() }; var measureData = await connection.QueryAsync <MonitorData, ApplicationMeasureData, SensorMeasureData, MonitorData>( @" SELECT Monitor, ApplicationMeasure AS Application, ReadingDtm, [Value] FROM ReadingMeasure WHERE HotelChain = @HotelChain AND CountryCode = @CountryCode AND Town = @Town AND Suburb = @Suburb AND RoomType = @RoomType AND Room = @Name ORDER BY ReadingDtm", (monitor, application, sensorData) => { MonitorData monitorData = result.MonitorData.FirstOrDefault(m => m.Monitor == monitor.Monitor); if (monitorData == null) { result.MonitorData.Add(monitor); monitorData = monitor; monitorData.ApplicationMeasureData = new List <ApplicationMeasureData>(); monitorData.ApplicationSwitchData = new List <ApplicationSwitchData>(); } ApplicationMeasureData applicationMeasureData = monitorData.ApplicationMeasureData.FirstOrDefault(a => a.Application == application.Application); if (applicationMeasureData == null) { monitorData.ApplicationMeasureData.Add(application); applicationMeasureData = application; applicationMeasureData.SensorData = new List <SensorMeasureData>(); } applicationMeasureData.SensorData.Add(sensorData); return(monitorData); }, roomKey, splitOn : "Application, ReadingDtm" ); var switchData = await connection.QueryAsync <MonitorData, ApplicationSwitchData, SensorSwitchData, MonitorData>( @" SELECT Monitor, ApplicationSwitch AS Application, ReadingDtm, [Value] FROM ReadingSwitch WHERE HotelChain = @HotelChain AND CountryCode = @CountryCode AND Town = @Town AND Suburb = @Suburb AND RoomType = @RoomType AND Room = @Name ORDER BY ReadingDtm", (monitor, application, sensorData) => { MonitorData monitorData = result.MonitorData.FirstOrDefault(m => m.Monitor == monitor.Monitor); if (monitorData == null) { result.MonitorData.Add(monitor); monitorData = monitor; monitorData.ApplicationMeasureData = new List <ApplicationMeasureData>(); monitorData.ApplicationSwitchData = new List <ApplicationSwitchData>(); } ApplicationSwitchData applicationSwitchData = monitorData.ApplicationSwitchData.FirstOrDefault(a => a.Application == application.Application); if (applicationSwitchData == null) { monitorData.ApplicationSwitchData.Add(application); applicationSwitchData = application; applicationSwitchData.SensorData = new List <SensorSwitchData>(); } applicationSwitchData.SensorData.Add(sensorData); return(monitorData); }, roomKey, splitOn : "Application, ReadingDtm" ); return(result); }
public async Task <HotelData> FindReadingsByHotelAsync(HotelKey hotelKey) { using SqlConnection connection = new SqlConnection(_connectionString); await connection.OpenAsync(); HotelData result = new HotelData() { HotelChain = hotelKey.HotelChain, CountryCode = hotelKey.CountryCode, Town = hotelKey.Town, Suburb = hotelKey.Suburb, RoomData = new List <RoomData>() }; var measureData = await connection.QueryAsync <RoomData, MonitorData, ApplicationMeasureData, SensorMeasureData, RoomData>( @" SELECT RoomType, Room, Monitor, ApplicationMeasure AS Application, ReadingDtm, [Value] FROM ReadingMeasure WHERE HotelChain = @HotelChain AND CountryCode = @CountryCode AND Town = @Town AND Suburb = @Suburb ORDER BY ReadingDtm", (room, monitor, application, sensorData) => { RoomData roomData = result.RoomData.FirstOrDefault(r => r.RoomType == room.RoomType && r.Room == room.Room); if (roomData == null) { result.RoomData.Add(room); roomData = room; roomData.MonitorData = new List <MonitorData>(); } MonitorData monitorData = roomData.MonitorData.FirstOrDefault(m => m.Monitor == monitor.Monitor); if (monitorData == null) { roomData.MonitorData.Add(monitor); monitorData = monitor; monitorData.ApplicationMeasureData = new List <ApplicationMeasureData>(); monitorData.ApplicationSwitchData = new List <ApplicationSwitchData>(); } ApplicationMeasureData applicationMeasureData = monitorData.ApplicationMeasureData.FirstOrDefault(a => a.Application == application.Application); if (applicationMeasureData == null) { monitorData.ApplicationMeasureData.Add(application); applicationMeasureData = application; applicationMeasureData.SensorData = new List <SensorMeasureData>(); } applicationMeasureData.SensorData.Add(sensorData); return(roomData); }, hotelKey, splitOn : "Monitor, Application, ReadingDtm" ); var switchData = await connection.QueryAsync <RoomData, MonitorData, ApplicationSwitchData, SensorSwitchData, RoomData>( @" SELECT RoomType, Room, Monitor, ApplicationSwitch AS Application, ReadingDtm, [Value] FROM ReadingSwitch WHERE HotelChain = @HotelChain AND CountryCode = @CountryCode AND Town = @Town AND Suburb = @Suburb ORDER BY ReadingDtm", (room, monitor, application, sensorData) => { RoomData roomData = result.RoomData.FirstOrDefault(r => r.RoomType == room.RoomType && r.Room == room.Room); if (roomData == null) { result.RoomData.Add(room); roomData = room; roomData.MonitorData = new List <MonitorData>(); } MonitorData monitorData = roomData.MonitorData.FirstOrDefault(m => m.Monitor == monitor.Monitor); if (monitorData == null) { roomData.MonitorData.Add(monitor); monitorData = monitor; monitorData.ApplicationMeasureData = new List <ApplicationMeasureData>(); monitorData.ApplicationSwitchData = new List <ApplicationSwitchData>(); } ApplicationSwitchData applicationSwitchData = monitorData.ApplicationSwitchData.FirstOrDefault(a => a.Application == application.Application); if (applicationSwitchData == null) { monitorData.ApplicationSwitchData.Add(application); applicationSwitchData = application; applicationSwitchData.SensorData = new List <SensorSwitchData>(); } applicationSwitchData.SensorData.Add(sensorData); return(roomData); }, hotelKey, splitOn : "Monitor, Application, ReadingDtm" ); return(result); }