/// <summary>
/// Get all floors in the building and the accessrights the user has in those rooms
/// </summary>
/// <param name="userToken">The current user's token</param>
/// <param name="buildingId">The building id</param>
/// <param name="getRoomsRecursivly">boolean to indicate if rooms should be retrieved recursivly, or ignored</param>
/// <returns>The list of Floors</returns>
public static List<Floor> GetFloors(Guid userToken, UInt32 buildingId, Boolean getRoomsRecursivly)
{
var floors = new List<Floor>();
using (var connection = new SqlConnection(ConstantHelper.ConnectionString))
{
connection.Open();
string query = String.Format("SELECT [username] FROM [token] WHERE [token] = '{0}'", userToken);
var command = new SqlCommand(query, connection);
//Validate token
var username = command.ExecuteScalar() as String;
if (String.IsNullOrWhiteSpace(username))
{
throw new APIException("The provided userToken is invalid or has expired", "userToken");
}
else
{
//SUBSELECT FOR A SINGLE BIT!
query = String.Format(@"SELECT [building].*, [userBuildingCouple].[accessRights],
CAST((SELECT MAX(CAST([hasAlarm] AS int)) FROM [room] WHERE [room].[buildingId] = [building].[buildingId])
AS bit) AS [hasAlarm]
FROM [building]
LEFT JOIN [userBuildingCouple] ON [building].[parentId] = [userBuildingCouple].[buildingId]
LEFT JOIN [user] ON [user].[userId] = [userBuildingCouple].[userId]
WHERE [user].[username] = '{0}' and [building].[parentId] = {1}", username, buildingId);
command = new SqlCommand(query, connection);
//Fill collection
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var floor = new Floor
{
AccessRole = Building.ParseAccessRightsFromString(reader["accessRights"].ToString()),
BuildingID = UInt32.Parse(reader["buildingId"].ToString()),
BuildingName = reader["name"].ToString(),
Parent = UInt32.Parse(reader["parentId"].ToString()),
HasAlarm = Boolean.Parse(reader["hasAlarm"].ToString())
};
if (getRoomsRecursivly)
{
floor.Rooms = GetRooms(userToken, floor.BuildingID);
}
floors.Add(floor);
}
}
}
//return collection
return floors;
}
/// <summary>
/// Get all floors in the building and the accessrights the user has in those rooms
/// </summary>
/// <param name="userToken">The current user's token</param>
/// <param name="buildingId">The building id</param>
/// <param name="getRoomsRecursivly">boolean to indicate if rooms should be retrieved recursivly, or ignored</param>
/// <returns>The list of Floors</returns>
public List<Floor> GetFloors(Guid userToken, int buildingId, bool getRoomsRecursivly)
{
List<Floor> floors = new List<Floor>();
using (SqlConnection connection = new SqlConnection(ConstantHelper.ConnectionString))
{
connection.Open();
string query = String.Format("SELECT [username] FROM [token] WHERE [token] = '{0}'", userToken);
SqlCommand command = new SqlCommand(query, connection);
//Validate token
string username = command.ExecuteScalar() as String;
if (String.IsNullOrWhiteSpace(username))
{
throw new APIException("The provided userToken is invalid or has expired", "userToken");
}
else
{
//join buildings on users rights
query = String.Format(@"SELECT [building].*, [userBuildingCouple].[accessRights] FROM [building]
LEFT JOIN [userBuildingCouple] ON building.buildingId = [userBuildingCouple].[buildingId]
LEFT JOIN [user] ON [user].[userId] = [userBuildingCouple].[userId]
WHERE [user].[username] = '{0}' and [building].[parentId] = {1}", username, buildingId);
command = new SqlCommand(query, connection);
//Fill collection
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Floor floor = new Floor()
{
AccessRole = Building.ParseAccessRightsFromString(reader["accessRights"].ToString()),
BuildingID = Int32.Parse(reader["buildingId"].ToString()),
BuildingName = reader["name"].ToString(),
Parent = Int32.Parse(reader["parentId"].ToString())
};
if (getRoomsRecursivly)
{
floor.Rooms = GetRooms(userToken, floor.BuildingID);
}
floors.Add(floor);
}
}
}
//return collection
return floors;
}