public Team GetTeamById(uint id)
{
Team team = null;
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand selectCommand = CreateSelectAllCommand("teams", "Id", "=", id);
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
if (reader.Read())
{
string name = SafeValueReader.GetStringSafe(reader, "Name");
team = new Team(id, name);
}
}
catch (Exception ex)
{
_logger?.Error(ex);
}
}
}
catch (Exception ex)
{
_logger?.Fatal($"{GetType().Name}.GetTeamById(uint id) error.");
_logger?.Fatal(ex);
}
return(team);
}
public string GetPasswordHashByUsername(string username)
{
string hash = string.Empty;
try
{
using (MySqlConnection connection = OpenNewConnection())
{
string columnName = "PasswordHash";
MySqlCommand selectCommand = CreateSelectCommand(columnName, "users", "UserName", "=", username);
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
if (reader.Read())
{
hash = SafeValueReader.GetStringSafe(reader, columnName);
}
}
catch (Exception ex)
{
_logger?.Error(ex);
}
}
}
catch
{
throw;
}
return(hash);
}
public IEnumerable <Story> GetStoriesByBacklogId(uint backlogId)
{
List <Story> stories = new List <Story>();
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand selectCommand = CreateSelectAllCommand(_STORIES_TABLE_NAME, "BacklogId", "=", backlogId);
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
while (reader.Read())
{
uint id = SafeValueReader.GetUIntSafe(reader, "Id");
string name = SafeValueReader.GetStringSafe(reader, "Name");
uint importance = SafeValueReader.GetUIntSafe(reader, "Importance");
uint initialEstimate = SafeValueReader.GetUIntSafe(reader, "InitialEstimate");
string howToDemo = SafeValueReader.GetStringSafe(reader, "HowToDemo");
string notes = SafeValueReader.GetStringSafe(reader, "Notes");
string status = _storyStatusesDataModel.GetStatus(Convert.ToUInt32(reader["Status"]));
uint executorId = SafeValueReader.GetUIntSafe(reader, "ExecutorId");
stories.Add(new Story(id, name, importance, initialEstimate, howToDemo, notes, status, executorId, backlogId));
}
}
catch (Exception ex)
{
_logger?.Error(ex);
throw;
}
}
}
catch
{
throw;
}
return(stories);
}
public Sprint GetSprintByProjectId(uint projectId)
{
Sprint sprint = null;
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand command = new MySqlCommand();
command.CommandText = $"SELECT * FROM `{_SPRINT_TABLE_NAME}` WHERE NOW() >= StartDate AND NOW() <= FinishDate AND ProjectId = {projectId} AND FinishedBeforeTheApointedDate = FALSE;";
command.Connection = connection;
MySqlDataReader reader = command.ExecuteReader();
try
{
if (reader.Read())
{
uint id = SafeValueReader.GetUIntSafe(reader, "Id");
string mainGoal = SafeValueReader.GetStringSafe(reader, "MainGoal");
DateTime startDate = reader.GetDateTime("StartDate");
DateTime finishDate = reader.GetDateTime("FinishDate");
uint backlogId = SafeValueReader.GetUIntSafe(reader, "BacklogId");
bool finishedBeforeTheApointedDate = Convert.ToBoolean(reader["FinishedBeforeTheApointedDate"]);
sprint = new Sprint(id, mainGoal, startDate, finishDate, projectId, backlogId, finishedBeforeTheApointedDate);
}
}
catch (Exception ex)
{
_logger?.Error(ex);
}
}
}
catch (Exception ex)
{
_logger?.Fatal($"{GetType().Name}.GetTeamById(uint id) error.");
_logger?.Fatal(ex);
}
return(sprint);
}
public IEnumerable <Sprint> GetAllProjectSprints(uint projectId)
{
List <Sprint> sprints = new List <Sprint>();
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand command = CreateSelectAllCommand(_SPRINT_TABLE_NAME, "ProjectId", "=", projectId);
command.Connection = connection;
MySqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
uint id = SafeValueReader.GetUIntSafe(reader, "Id");
string mainGoal = SafeValueReader.GetStringSafe(reader, "MainGoal");
DateTime startDate = reader.GetDateTime("StartDate");
DateTime finishDate = reader.GetDateTime("FinishDate");
uint backlogId = SafeValueReader.GetUIntSafe(reader, "BacklogId");
bool finishedBeforeTheApointedDate = Convert.ToBoolean(reader["FinishedBeforeTheApointedDate"]);
sprints.Add(new Sprint(id, mainGoal, startDate, finishDate, projectId, backlogId, finishedBeforeTheApointedDate));
}
}
catch (Exception ex)
{
_logger?.Error(ex);
}
}
}
catch (Exception ex)
{
_logger?.Fatal($"{GetType().Name}.GetAllProjectSprints(uint projectId) error.");
_logger?.Fatal(ex);
}
return(sprints);
}
private void InitializeStatuses()
{
_statuses = new List <Status>();
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand selectCommand = CreateSelectAllCommand(_STORY_STATUSES_TABLE_NAME);
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
while (reader.Read())
{
uint id = SafeValueReader.GetUIntSafe(reader, "Id");
string name = SafeValueReader.GetStringSafe(reader, "Name");
StoryStatus enumValue = GetStatusEnumValue(name);
_statuses.Add(new Status
{
Id = id,
Name = name,
EnumValue = enumValue
});
}
}
catch (Exception ex)
{
_logger?.Error(ex);
throw;
}
}
}
catch
{
throw;
}
}
public IEnumerable <Project> GetProjectsOfTeam(Team team)
{
List <Project> projects = new List <Project>();
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand selectCommand = CreateSelectAllCommand(_PROJECTS_TABLE_NAME, "DevelopmentTeamId", "=", team.Id);
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
while (reader.Read())
{
uint id = Convert.ToUInt32(reader["Id"]);
string name = SafeValueReader.GetStringSafe(reader, "Name");
uint manager = Convert.ToUInt32(reader["Manager"]);
uint developmentTeamId = Convert.ToUInt32(reader["DevelopmentTeamId"]);
uint productBacklogId = Convert.ToUInt32(reader["ProductBacklogId"]);
projects.Add(new Project(id, name, manager, developmentTeamId, productBacklogId));
}
}
catch (Exception ex)
{
_logger?.Error(ex);
throw;
}
}
}
catch
{
throw;
}
return(projects);
}
public Project GetProjectById(uint id)
{
Project project = null;
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand selectCommand = CreateSelectAllCommand(_PROJECTS_TABLE_NAME, "Id", "=", id);
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
if (reader.Read())
{
string name = SafeValueReader.GetStringSafe(reader, "Name");
uint manager = Convert.ToUInt32(reader["Manager"]);
uint developmentTeamId = Convert.ToUInt32(reader["DevelopmentTeamId"]);
uint productBacklogId = Convert.ToUInt32(reader["ProductBacklogId"]);
project = new Project(id, name, manager, developmentTeamId, productBacklogId);
}
}
catch (Exception ex)
{
_logger?.Error(ex);
}
}
}
catch (Exception ex)
{
_logger?.Fatal($"{GetType().Name}.GetProjectById(uint id) error.");
_logger?.Fatal(ex);
}
return(project);
}
public User GetUser(uint id)
{
User user = null;
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand selectCommand = CreateSelectAllCommand("users", "Id", "=", id);
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
if (reader.Read())
{
string firstName = SafeValueReader.GetStringSafe(reader, "FirstName");
string lastName = SafeValueReader.GetStringSafe(reader, "LastName");
string username = SafeValueReader.GetStringSafe(reader, "UserName");
user = new User(id, firstName, lastName, username);
}
}
catch (Exception ex)
{
_logger?.Error(ex);
}
}
}
catch (Exception ex)
{
_logger?.Error(ex);
throw;
}
return(user);
}
public IList <Team> GetTeamsOfUser(uint userId)
{
List <Team> teams = new List <Team>();
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand selectCommand = CreateSelectCommandWithJoin(
new List <string> {
"teams.Id", "teams.Name"
}, "teams", "teammembers", "teams.Id", "teammembers.TeamId", "teammembers.UserId", "=", userId, JoinType.Inner);
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
while (reader.Read())
{
uint id = Convert.ToUInt32(reader["Id"]);
string name = SafeValueReader.GetStringSafe(reader, "Name");
teams.Add(new Team(id, name));
}
}
catch (Exception ex)
{
_logger?.Error(ex);
}
}
}
catch
{
}
return(teams);
}
public IList <Team> GetAllTeams()
{
List <Team> teams = new List <Team>();
try
{
using (MySqlConnection connection = OpenNewConnection())
{
MySqlCommand selectCommand = CreateSelectAllCommand("teams");
selectCommand.Connection = connection;
MySqlDataReader reader = selectCommand.ExecuteReader();
try
{
while (reader.Read())
{
uint id = Convert.ToUInt32(reader["Id"]);
string name = SafeValueReader.GetStringSafe(reader, "Name");
teams.Add(new Team(id, name));
}
}
catch (Exception ex)
{
_logger?.Error(ex);
}
}
}
catch (Exception ex)
{
_logger?.Fatal($"{GetType().Name}.GetAllTeams() error.");
_logger?.Fatal(ex);
}
return(teams);
}