public async Task <ActionResult <IEnumerable <HotelTag> > > AddHotelTag(
[FromQuery] HotelKey hotelKey,
[FromBody] HotelTag hotelTag)
{
hotelTag.SetHotelKey(hotelKey);
if (await _hotelTagStore.CheckExistanceAsync(hotelTag.GetKey()))
{
return(ValidationProblem(
title: "Resource already exists",
detail: string.Format(
"{0} at {1},{2},{3},{4} already exists",
hotelTag.Tag,
hotelTag.HotelChain,
hotelTag.CountryCode,
hotelTag.Town,
hotelTag.Suburb)
));
}
await _hotelTagStore.CreateAsync(hotelTag);
return(CreatedAtAction(
nameof(GetHotelTagDetails),
hotelTag.GetKey(),
hotelTag));
}
public async Task DeleteHotelUserAsync(HotelKey hotelKey, int hotelUserId)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
await connection.ExecuteAsync(
"HotelUser_Drop_tr",
new { HotelUserId = hotelUserId, hotelKey.HotelChain, hotelKey.CountryCode, hotelKey.Town, hotelKey.Suburb },
commandType : CommandType.StoredProcedure);
}
public async Task <IActionResult> DeleteHotel([FromQuery] HotelKey hotelKey)
{
if (!await _hotelStore.CheckExistanceAsync(hotelKey))
{
return(NotFound());
}
await _hotelStore.DeleteAsync(hotelKey);
return(NoContent());
}
public async Task <ActionResult <Hotel> > EditHotel([FromQuery] HotelKey hotelKey, [FromBody] Hotel hotel)
{
if (!await _hotelStore.CheckExistanceAsync(hotelKey))
{
return(NotFound());
}
await _hotelStore.UpdateAsync(hotelKey, hotel);
return(Ok(hotel));
}
public async Task <ActionResult <Hotel> > GetHotelDetails([FromQuery] HotelKey hotelKey)
{
Hotel result = await _hotelStore.FindDetailsAsync(hotelKey);
if (result != null)
{
return(Ok(result));
}
return(NotFound());
}
public async Task DeleteAsync(HotelKey hotelKey)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
await connection.ExecuteAsync(
"Hotel_Drop_tr",
hotelKey,
commandType : CommandType.StoredProcedure);
}
public async Task <ActionResult <ApplicationUser> > GetHotelUserDetails([FromQuery] HotelKey hotelKey, int personId)
{
ApplicationUser result = await _hotelUserStore.FindHotelUserDetailsAsync(hotelKey, personId);
if (result != null)
{
return(Ok(result));
}
return(NotFound());
}
public async Task <IActionResult> RegisterMonitor([FromBody] MonitorRegistration monitorRegistration)
{
HotelKey hotelKey = ParseHotelKeyFromClaims(User);
monitorRegistration.Manufacturer = "Espressif";
monitorRegistration.Model = "ESP32";
monitorRegistration.SWVersion = "0.0.1";
monitorRegistration.SWUpdateDtm = DateTime.UtcNow;
await _monitorStore.CreateAsync(hotelKey, monitorRegistration);
return(Ok(hotelKey));
}
private HotelKey ParseHotelKeyFromClaims(ClaimsPrincipal principal)
{
string hotelClaim = principal.FindFirst(RoomMonitorConstants.ClaimTypes.Hotel).Value;
var serializeOptions = new JsonSerializerOptions
{
PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
};
HotelKey hotelKey = JsonSerializer.Deserialize <HotelKey>(
hotelClaim,
serializeOptions);
return(hotelKey);
}
public async Task <ActionResult <ApplicationUser> > AddHotelUser([FromQuery] HotelKey hotelKey, [FromBody] UserInvitation userInvitation)
{
if (userInvitation.Role == RoomMonitorConstants.UserRoles.SystemAdmin)
{
return(Forbid());
}
// Hotel Admins can only invite Hotel Employees
if (this.User.IsInRole(RoomMonitorConstants.UserRoles.HotelAdmin) && userInvitation.Role != RoomMonitorConstants.UserRoles.HotelEmployee)
{
return(Forbid());
}
var user = new ApplicationUser
{
Email = userInvitation.Email,
Role = userInvitation.Role,
HotelChain = hotelKey.HotelChain,
CountryCode = hotelKey.CountryCode,
Town = hotelKey.Town,
Suburb = hotelKey.Suburb
};
IdentityResult result = await _userManager.CreateAsync(user);
if (result.Succeeded)
{
string code = await _userManager.GeneratePasswordResetTokenAsync(user);
// Using protocol param will force creation of an absolut url. We
// don't want to send a relative URL by e-mail.
var callbackUrl = Url.Action("Activate",
"Account",
new { userId = user.PersonId, code },
protocol: Request.Scheme);
await _emailSender.SendInvitationMail(user.Email, callbackUrl);
return(CreatedAtAction(
nameof(GetHotelUserDetails),
new { personId = user.PersonId, hotelKey.HotelChain, hotelKey.CountryCode, hotelKey.Town, hotelKey.Suburb },
user));
}
else
{
ModelState.AddModelError(nameof(UserInvitation.Email), "Email already taken.");
return(ValidationProblem(
title: "Duplicate Email",
detail: String.Format("Email '{0}' is already taken.", userInvitation.Email)
));
}
}
public async Task <IEnumerable <Room> > FindAllByHotelAsync(HotelKey hotelKey)
{
using SqlConnection connection = new SqlConnection(_connectionString);
var lookup = new Dictionary <Tuple <string, string>, Room>();
await connection.OpenAsync();
var result = await connection.QueryAsync <Room, dynamic, Room>(
@" SELECT
r.*, r.Room as Name, t.Tag
FROM
Room r
LEFT JOIN
RoomTag t
ON
r.HotelChain = t.HotelChain AND
r.CountryCode = t.CountryCode AND
r.Town = t.Town AND
r.Suburb = t.Suburb AND
r.RoomType = t.RoomType AND
r.Room = t.Room
WHERE
r.HotelChain = @HotelChain AND
r.CountryCode = @CountryCode AND
r.Town = @Town AND
r.Suburb = @Suburb",
(r, t) =>
{
var key = Tuple.Create(r.RoomType, r.Name);
if (!lookup.TryGetValue(key, out Room room))
{
lookup.Add(key, room = r);
}
if (room.Tags == null)
{
room.Tags = new List <string>();
}
if (t != null && t.Tag != null)
{
room.Tags.Add(t.Tag);
}
return(room);
},
hotelKey,
splitOn : "Tag"
);
return(lookup.Values);
}
public async Task <Hotel> FindDetailsAsync(HotelKey hotelKey)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var result = await connection.QuerySingleOrDefaultAsync <Hotel>(
@"SELECT * FROM Hotel WHERE
HotelChain = @HotelChain AND
CountryCode = @CountryCode AND
Town = @Town AND
Suburb = @Suburb",
hotelKey
);
return(result);
}
public async Task <IEnumerable <HotelTag> > FindAllByHotelAsync(HotelKey hotelKey)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var result = await connection.QueryAsync <HotelTag>(
@" SELECT *
FROM HotelTag
WHERE
HotelChain = @HotelChain AND
CountryCode = @CountryCode AND
Town = @Town AND
Suburb = @Suburb",
hotelKey
);
return(result);
}
public async Task <ApplicationUser> FindHotelUserDetailsAsync(HotelKey hotelKey, int hotelUserId)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var result = await connection.QuerySingleOrDefaultAsync <ApplicationUser>(
@"SELECT *, PersonType.PersonType AS Role FROM Person
INNER JOIN PersonType ON Person.PersonTypeCode = PersonType.PersonTypeCode
INNER JOIN HotelUser ON PersonId = HotelUserId
WHERE PersonId = @HotelUserId AND
HotelChain = @HotelChain AND
CountryCode = @CountryCode AND
Town = @Town AND
Suburb = @Suburb",
new { HotelUserId = hotelUserId, hotelKey.HotelChain, hotelKey.CountryCode, hotelKey.Town, hotelKey.Suburb }
);
return(result);
}
public async Task <bool> CheckExistanceAsync(HotelKey hotelKey)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var result = await connection.ExecuteScalarAsync(
@"SELECT 1 FROM Hotel WHERE
HotelChain = @HotelChain AND
CountryCode = @CountryCode AND
Town = @Town AND
Suburb = @Suburb",
hotelKey
);
if (result != null)
{
return(true);
}
return(false);
}
public async Task <IActionResult> DeleteHotelUser([FromQuery] HotelKey hotelKey, [FromRoute] int personId)
{
ApplicationUser userToDelete = await _userManager.FindByIdAsync(personId.ToString());
if (userToDelete == null)
{
return(NotFound());
}
// Hotel Admins can only delete Hotel Employees
if (User.IsInRole(RoomMonitorConstants.UserRoles.HotelAdmin) &&
userToDelete.Role != RoomMonitorConstants.UserRoles.HotelEmployee)
{
return(Forbid());
}
await _hotelUserStore.DeleteHotelUserAsync(hotelKey, personId);
return(NoContent());
}
public async Task UpdateAsync(HotelKey hotelKey, Hotel hotel)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
await connection.ExecuteAsync(
"Hotel_Modify_tr",
new
{
hotelKey.HotelChain,
hotelKey.CountryCode,
hotelKey.Town,
hotelKey.Suburb,
NewHotelChain = hotel.HotelChain,
NewCountryCode = hotel.CountryCode,
NewTown = hotel.Town,
NewSuburb = hotel.Suburb,
NewNumStar = hotel.NumStar
},
commandType : CommandType.StoredProcedure);
}
public async Task AddMonitorLogAsync(HotelKey hotelKey, string MACAddress, MonitorLog monitorLog)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
await connection.ExecuteAsync(
"MonitorLog_Add_tr",
new
{
hotelKey.HotelChain,
hotelKey.CountryCode,
hotelKey.Town,
hotelKey.Suburb,
MACAddress,
monitorLog.LogDtm,
monitorLog.BatteryLevel,
monitorLog.ConnFail,
monitorLog.PostFail,
monitorLog.Rssi
},
commandType : CommandType.StoredProcedure);
}
public async Task CreateAsync(HotelKey hotelKey, MonitorRegistration monitorRegistration)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
await connection.ExecuteAsync(
"Monitor_Register_tr",
new
{
hotelKey.HotelChain,
hotelKey.CountryCode,
hotelKey.Town,
hotelKey.Suburb,
monitorRegistration.MACAddress,
monitorRegistration.ConfigurationType,
monitorRegistration.Manufacturer,
monitorRegistration.Model,
monitorRegistration.SWVersion,
monitorRegistration.SWUpdateDtm
},
commandType : CommandType.StoredProcedure);
}
public async Task <ActionResult <Room> > AddRoom([FromQuery] HotelKey hotelKey, [FromBody] Room room)
{
room.SetHotelKey(hotelKey);
if (await _roomStore.CheckExistanceAsync(room.GetKey()))
{
return(ValidationProblem(
title: "Resource already exists",
detail: string.Format(
"{0} '{1}' at {2},{3},{4},{5} already exists",
room.RoomType,
room.Name,
room.HotelChain,
room.CountryCode,
room.Town,
room.Suburb)
));
}
await _roomStore.CreateAsync(room);
return(CreatedAtAction(
nameof(GetRoomDetails),
room.GetKey(),
room));
}
public async Task <ActionResult <IEnumerable <HotelTag> > > GetTagsByHotel([FromQuery] HotelKey hotelKey)
{
IEnumerable <HotelTag> result = await _hotelTagStore.FindAllByHotelAsync(hotelKey);
return(Ok(result));
}
public async Task <ActionResult <IEnumerable <MonitorRoomAlternateKey> > > GetMonitorHistoryByHotel([FromQuery] HotelKey hotelKey)
{
IEnumerable <MonitorRoomAlternateKey> result = await _monitorStore.FindMonitorHistoryByHotelAsync(hotelKey);
return(Ok(result));
}
public async Task <ActionResult <IEnumerable <ApplicationUser> > > GetHotelUsersByHotel([FromQuery] HotelKey hotelKey)
{
IEnumerable <ApplicationUser> result = await _hotelUserStore.FindHotelUsersByHotelAsync(hotelKey);
return(Ok(result));
}
public async Task <IActionResult> HandleMonitorRecord([FromBody] MonitorRecord monitorRecord)
{
HotelKey hotelKey = ParseHotelKeyFromClaims(User);
MonitorKey monitorKey = new MonitorKey
{
HotelChain = hotelKey.HotelChain,
CountryCode = hotelKey.CountryCode,
Town = hotelKey.Town,
Suburb = hotelKey.Suburb,
MACAddress = monitorRecord.MACAddress
};
monitorRecord.Logs.ForEach(async log =>
{
try
{
await _monitorStore.AddMonitorLogAsync(
hotelKey,
monitorRecord.MACAddress,
log);
}
catch (SqlException ex)
{
StringBuilder errorMessages = new StringBuilder();
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
Console.WriteLine(errorMessages.ToString());
}
});
MonitorRoom monitorRoom = await _monitorStore.FindMonitorRoomDetailsAsync(monitorKey);
if (monitorRoom != null)
{
monitorRecord.Readings.ForEach(async reading =>
{
try
{
await _monitorStore.AddMonitorReadingAsync(
monitorRoom,
reading);
}
catch (SqlException ex)
{
StringBuilder errorMessages = new StringBuilder();
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
Console.WriteLine(errorMessages.ToString());
}
});
}
return(StatusCode(StatusCodes.Status201Created));
}
public async Task <ActionResult <IEnumerable <Room> > > GetRoomsByHotel([FromQuery] HotelKey hotelKey)
{
IEnumerable <Room> result = await _roomStore.FindAllByHotelAsync(hotelKey);
return(Ok(result));
}
public async Task <ActionResult <IEnumerable <MonitorUnassigned> > > GetMonitorUnassignedByHotel([FromQuery] HotelKey hotelKey)
{
IEnumerable <MonitorUnassigned> result = await _monitorStore.FindAllMonitorUnassignedByHotelAsync(hotelKey);
return(Ok(result));
}
public async Task <IEnumerable <ApplicationUser> > FindHotelUsersByHotelAsync(HotelKey hotelKey)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var result = await connection.QueryAsync <ApplicationUser>(
@"SELECT *, PersonType.PersonType AS Role FROM Person
INNER JOIN PersonType ON Person.PersonTypeCode = PersonType.PersonTypeCode
INNER JOIN HotelUser ON PersonId = HotelUserId
WHERE HotelChain = @HotelChain AND
CountryCode = @CountryCode AND
Town = @Town AND
Suburb = @Suburb",
hotelKey
);
return(result);
}
public async Task <IEnumerable <MonitorRoomAlternateKey> > FindMonitorHistoryByHotelAsync(HotelKey hotelKey)
{
using SqlConnection connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var result = await connection.QueryAsync <MonitorRoomAlternateKey>(
@"SELECT DISTINCT HotelChain, CountryCode, Town, Suburb, RoomType, Room, Monitor
FROM Reading
WHERE HotelChain = @HotelChain
AND CountryCode = @CountryCode
AND Town = @Town
AND Suburb = @Suburb",
hotelKey
);
return(result);
}
public async Task <ActionResult <HotelData> > GetHotelReadings([FromQuery] HotelKey hotelKey)
{
var result = await _sensorDataStore.FindReadingsByHotelAsync(hotelKey);
return(Ok(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);
}
