/// <summary>
/// Get location by id.
/// </summary>
/// <param name="id">Used to identity the data to get.</param>
/// <returns>A <see cref="Location"/> if exists.</returns>
public async Task <Location> GetById(int id)
{
// Initalize temporary obj.
Location tempLocationObj = new Location();
// Initialize command obj.
using SqlCommand cmd = _databaseAccess.GetCommand("GetLocationById", CommandType.StoredProcedure);
// Define input parameters.
cmd.Parameters.AddWithValue("@id", id);
// Initialize data reader.
using SqlDataReader reader = await _databaseAccess.GetSqlDataReader();
// Check if reader has any data.
if (reader.HasRows)
{
// Read the data.
while (await reader.ReadAsync())
{
tempLocationObj.Identifier = reader.GetInt32("Id");
tempLocationObj.Name = reader.GetString("Name");
tempLocationObj.Description = !DataReaderExtensions.IsDBNull(reader, "@description") ? reader.GetString("Description") : string.Empty;
}
}
// Return data obj.
return(tempLocationObj);
}
public async Task <IEnumerable <User> > GetAll()
{
try
{
// Initialize command obj.
using SqlCommand c = _databaseAccess.GetCommand("GetAllUsers", CommandType.StoredProcedure);
// Initialzie data reader.
using SqlDataReader r = await _databaseAccess.GetSqlDataReader();
// Temporary list.
List <User> tempUsers = new List <User>();
// Check for any data.
if (r.HasRows)
{
// Read data.
while (await r.ReadAsync())
{
tempUsers.Add(
new User
{
Identifier = r.GetInt32("Id"),
FirstName = r.GetString("FirstName"),
LastName = r.GetString("LastName"),
Email = r.GetString("Email"),
ConsultantArea = new ConsultantArea()
{
Name = r.GetString("ConsultantAreaName")
},
Location = new Location()
{
Name = r.GetString("LocationName"),
Description = !DataReaderExtensions.IsDBNull(r, "LocationDesc") ? r.GetString("LocationDesc") : string.Empty
}
});
}
}
// Return data list.
return(tempUsers);
}
finally
{
_databaseAccess.CloseConnection();
}
}
public bool IsOrchestrationEnabled(Constants.EntityType type)
{
bool result = false;
IDataParameter[] parameters =
{
GetParameter("@entity_type_id", type)
};
using (IDbConnection conn = GetConnection())
{
conn.Open();
using (var dr = GetReader(conn, "sc_get_orchestration_lookup", parameters, CommandType.StoredProcedure))
{
while (dr.Read())
{
result = DataReaderExtensions.IsDBNull(dr, "is_publish") ? false : DataReaderExtensions.GetValue <bool>(dr, "is_publish");
}
}
}
return(result);
}
public IEnumerable <UpdatedEntityItem> GetUpdatedEntity(Tuple <int?, string> ids)
{
List <UpdatedEntityItem> resultList = new List <UpdatedEntityItem>();
IDataParameter[] parameters =
{
GetParameter("@product_id", ids.Item1),
GetParameter("@supplier_code", ids.Item2),
};
using (IDbConnection conn = GetConnection())
{
conn.Open();
using (var dr = GetReader(conn, "sc_import_get_affected_products", parameters, CommandType.StoredProcedure))
// todo create store procedure
{
while (dr.Read())
{
var entityId = DataReaderExtensions.IsDBNull(dr, "product_id") ? 0 : DataReaderExtensions.GetValue <int>(dr, "product_id");
var entityTypeId = DataReaderExtensions.IsDBNull(dr, "product_type_id") ? 0 : DataReaderExtensions.GetValue <int>(dr, "product_type_id");
var item = new UpdatedEntityItem
{
EntityId = entityId,
EntityTypeId =
entityTypeId == (int)Constants.ProductType.Ingredient
? Constants.EntityType.Ingredient
: Constants.EntityType.Dish,
MessageActionType = Constants.MessageActionType.StarChefEventsUpdated
};
resultList.Add(item);
}
return(resultList);
}
}
}
/// <summary>
/// Get all locations.
/// </summary>
/// <returns>A list of <see cref="Location"/>.</returns>
public async Task <IEnumerable <Location> > GetAll()
{
try
{
// Initialize temporary list.
List <Location> tempLocations = new List <Location>();
// Prepare command obj.
using SqlCommand cmd = _databaseAccess.GetCommand("GetAllLocations", CommandType.StoredProcedure);
// Initialize data reader.
using SqlDataReader reader = await _databaseAccess.GetSqlDataReader();
// Check if any data.
if (reader.HasRows)
{
// Read the data.
while (await reader.ReadAsync())
{
tempLocations.Add(
new Location()
{
Identifier = reader.GetInt32("Id"),
Name = reader.GetString("Name"),
Description = !DataReaderExtensions.IsDBNull(reader, "Description") ? reader.GetString("Description") : string.Empty
});
}
}
// Return data.
return(tempLocations);
}
finally
{
_databaseAccess.CloseConnection();
}
}
async Task <IEnumerable <Clan> > IRepository <Clan> .GetAll()
{
using SqlCommand c = new SqlCommand()
{
CommandText = "proc_clan_getall",
CommandType = CommandType.StoredProcedure,
CommandTimeout = 15,
Connection = SqlDataAccess.Instance.GetSqlConnection
};
try
{
SqlDataAccess.Instance.OpenConnection();
using SqlDataReader r = c.ExecuteReader();
IList <Clan> tempClans = new List <Clan>();
if (r.HasRows)
{
while (await r.ReadAsync())
{
// Check if the clan exists in the temp list.
if (tempClans.Any(x => x.BaseId == r.GetInt32(0)))
{
// Loop through the clans.
foreach (var clan in tempClans)
{
if (clan.BaseId == r.GetInt32(7))
{
clan.ClanAuthorities.Add(new ClanAuthority()
{
BaseId = r.GetInt32(7), UserName = r.GetString(8), IsFounder = r.GetBoolean(9)
});
}
}
}
// Create new clan in the temp list.
else
{
tempClans.Add(
new Clan()
{
BaseId = r.GetInt32(0),
Name = r.GetString(1),
About = r.GetString(2),
ClanPlatform = new ClanPlatform()
{
BaseId = r.GetInt32(4),
Name = r.GetString(5),
PlatformImageURL = r.GetString(6)
},
ClanAuthorities = new List <ClanAuthority>()
});
if (!DataReaderExtensions.IsDBNull(r, "UserName"))
{
// Loop through the clans.
foreach (var clan in tempClans)
{
if (clan.BaseId == r.GetInt32(7))
{
clan.ClanAuthorities.Add(new ClanAuthority()
{
BaseId = r.GetInt32(7), UserName = r.GetString(8), IsFounder = r.GetBoolean(9)
});
}
}
}
}
}
}
return(tempClans);
}
finally
{
SqlDataAccess.Instance.CloseConnection();
}
}
/// <summary>
/// Get all categories with associated specializations.
/// </summary>
/// <returns>A list of <see cref="Category" with a list of <see cref="Category.CategorySpecializations"/>/></returns>
public async Task <IEnumerable <Category> > GetAllCategoriesWithSpecializations()
{
try
{
// Initialize a temporary list with categories.
List <Category> tempCategories = new List <Category>();
// Prepare sql command object with information.
using SqlCommand cmd = _databaseAccess.GetCommand("GetAllCategoriesWithSpecialization", CommandType.StoredProcedure);
using (SqlDataReader reader = await _databaseAccess.GetSqlDataReader())
{
// Check if the reader has any rows.
if (reader.HasRows)
{
// Add data to the temporary list while reading.
while (await reader.ReadAsync())
{
// Initialize temporary objects to store the data received from the database.
var Category = new Category();
var CategorySpecialization = new Specialization();
var jobCategoryData = new Category();
var jobSpecializationData = new Specialization();
// Test
// Category Data
jobCategoryData.Identifier = reader.GetInt32("Id");
jobCategoryData.Name = reader.GetString("CategoryName");
// Specialization Data
if (!DataReaderExtensions.IsDBNull(reader, "SpecId"))
{
jobSpecializationData.Identifier = reader.GetInt32("SpecId");
}
if (!DataReaderExtensions.IsDBNull(reader, "SpecializationName"))
{
jobSpecializationData.Name = reader.GetString("SpecializationName");
}
if (!DataReaderExtensions.IsDBNull(reader, "CategoryId"))
{
jobSpecializationData.CategoryId = reader.GetInt32("CategoryId");
}
// Check if job category has a matching specialization.
if (jobCategoryData.Identifier == jobSpecializationData.CategoryId)
{
// Check if the category exists in the temp list.
if (tempCategories.Any(data => data.Identifier == jobCategoryData.Identifier))
{
// Loop through temp list.
foreach (var cat in tempCategories)
{
// Check if any specialization matches any categories in the temp list.
if (cat.Identifier == jobSpecializationData.CategoryId)
{
cat.Specializations.Add(
new Specialization()
{
Identifier = jobSpecializationData.Identifier,
Name = jobSpecializationData.Name,
CategoryId = jobSpecializationData.CategoryId
});
}
}
}
else
{
// Add category to the temp list, when it's non existent in the temp list.
tempCategories.Add(
new Category()
{
Identifier = jobCategoryData.Identifier,
Name = jobCategoryData.Name,
Specializations = new List <Specialization>()
{
new Specialization()
{
Identifier = jobSpecializationData.Identifier,
Name = jobSpecializationData.Name,
CategoryId = jobSpecializationData.CategoryId
}
}
});
}
}
else
{
// Add category if non existent in the current temp list.
tempCategories.Add(
new Category()
{
Identifier = jobCategoryData.Identifier,
Name = jobCategoryData.Name,
Specializations = null
});
}
}
}
}
// return data.
return(tempCategories);
}
finally
{
_databaseAccess.CloseConnection();
}
}