public List <ExerciseModel> GetAll(string sortField, int userId)
{
List <ExerciseModel> tempExercises = new List <ExerciseModel>();
using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
connection.Open();
SqlCommand dataCommand = new SqlCommand()
{
CommandText = "SELECT * FROM Exercises Inner join User_Exercise on [Exercises].ExerciseId = [User_Exercise].ExerciseId where UserId = '" + userId + "' AND InWorkout = 0 ORDER BY CASE WHEN @sortField = 'Name' Then Name WHEN @sortField = 'Weight' Then Weight WHEN @sortField = 'Repetitions' Then Repetitions WHEN @sortField = 'Date' Then Date END DESC",
Connection = connection
};
SqlParameter sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "@sortfield";
sqlParameter.Value = sortField;
dataCommand.Parameters.Add(sqlParameter);
using (SqlDataReader exerciseReader = dataCommand.ExecuteReader())
{
while (exerciseReader.Read())
{
ExerciseModel tempExercise = new ExerciseModel(Convert.ToInt32(exerciseReader["ExerciseId"]), exerciseReader["Name"].ToString(), Convert.ToInt32(exerciseReader["Weight"]), Convert.ToInt32(exerciseReader["Repetitions"]), ((DateTime)exerciseReader["Date"]).ToString("d/M/yyyy HH:mm:ss"), exerciseReader["Skillevel"].ToString(), (bool)exerciseReader["InWorkout"]);
tempExercises.Add(tempExercise);
}
exerciseReader.Close();
return(tempExercises);
}
}
}
public List <ExerciseModel> GetAllExercises(int workoutId)
{
List <ExerciseModel> tempExercises = new List <ExerciseModel>();
using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
connection.Open();
SqlCommand dataCommand = new SqlCommand()
{
CommandText = "Select* FROM [Exercises] Inner join [Exercise_Workout] on [Exercises].ExerciseId = [Exercise_Workout].ExerciseId where [Exercise_Workout].WorkoutId = '" + workoutId + "'",
Connection = connection
};
using (SqlDataReader exerciseReader = dataCommand.ExecuteReader())
{
while (exerciseReader.Read())
{
ExerciseModel tempExercise = new ExerciseModel(exerciseReader["Name"].ToString(), Convert.ToInt32(exerciseReader["Repetitions"]), exerciseReader["Skillevel"].ToString());;
tempExercises.Add(tempExercise);
}
exerciseReader.Close();
return(tempExercises);
}
}
}
public void Delete(int id)
{
using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
connection.Open();
string query = @"Delete from [Workouts] where WorkoutId = '" + id + "'";
SqlCommand qry = new SqlCommand(query, connection);
qry.ExecuteNonQuery();
}
}
public void DeleteAll()
{
using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
connection.Open();
string query = @"Delete from [Exercises] where inWorkout = 0";
SqlCommand qry = new SqlCommand(query, connection);
qry.ExecuteNonQuery();
}
}
public void UpdateExercise(int id, string name, int weight, int repetitions, string skillevel)
{
using (SqlConnection conn = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
conn.Open();
var query = @"update Exercises set Name ='" + name + "', Weight ='" + weight + "', Repetitions ='" + repetitions + "', Skillevel ='" + skillevel + "' where ExerciseId = '" + id + "'";
using (SqlCommand command = new SqlCommand(query, conn))
{
command.ExecuteNonQuery();
}
}
}
public void Add(WorkoutModel workout, int userId)
{
using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
connection.Open();
string query = @"insert into [Workouts] (Name, Skillevel, Category, Time, CaloriesBurned) values ('" + workout.Name + "','" +
workout.Skillevel + "','" + workout.Category + "','" + workout.Time + "', '" + workout.Time * 4 + "')";
SqlCommand qry = new SqlCommand(query, connection);
qry.ExecuteNonQuery();
string userExerciseQuery = @"insert into [User_Workout] (UserId, WorkoutId) values ('" + userId + "','" + workout.Id + "')";
SqlCommand userExerciseQry = new SqlCommand(userExerciseQuery, connection);
userExerciseQry.ExecuteNonQuery();
}
}
public int GetAmountOfExercises(int id)
{
using (SqlConnection conn = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
conn.Open();
SqlCommand dataCommand = new SqlCommand()
{
CommandText = "SELECT COUNT(Exercise_Workout.ExerciseId) AS NumberOfExercises FROM Exercise_Workout LEFT JOIN Workouts ON Exercise_Workout.WorkoutId = Workouts.WorkoutId where workouts.workoutId ='" + id + "' GROUP BY Workouts.name ",
Connection = conn
};
using (SqlDataReader workoutReader = dataCommand.ExecuteReader())
{
workoutReader.Read();
return(Convert.ToInt32(workoutReader["NumberOfExercises"]));
}
}
}
public int GetIdFromLastExercise()
{
using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
connection.Open();
SqlCommand dataCommand = new SqlCommand()
{
CommandText = "Select Top 1 ExerciseId From [Exercises] Order By ExerciseId Desc",
Connection = connection
};
using (SqlDataReader exerciseReader = dataCommand.ExecuteReader())
{
exerciseReader.Read();
return(Convert.ToInt32(exerciseReader["ExerciseId"]));
}
}
}
public void Add(ExerciseModel exercise, int workoutId, int userId)
{
using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
connection.Open();
string query = @"insert into [Exercises] (Name,Weight,Repetitions,Skillevel, InWorkout) values ('" + exercise.Name + "','" +
exercise.Weight + "','" + exercise.Repetitions + "','" + exercise.Level + "','" + exercise.InWorkout + "')";
SqlCommand qry = new SqlCommand(query, connection);
qry.ExecuteNonQuery();
string userExerciseQuery = @"insert into [User_Exercise] (UserId, ExerciseId) values ('" + userId + "','" + GetIdFromLastExercise() + "')";
SqlCommand userExerciseQry = new SqlCommand(userExerciseQuery, connection);
userExerciseQry.ExecuteNonQuery();
if (workoutId != 0)
{
string exerciseWorkoutQuery = @"insert into [Exercise_Workout](ExerciseId, WorkoutId) values ('" + GetIdFromLastExercise() + "' , '" + workoutId + "')";
SqlCommand exerciseWorkoutQry = new SqlCommand(exerciseWorkoutQuery, connection);
exerciseWorkoutQry.ExecuteNonQuery();
}
}
}
public List <WorkoutModel> GetAll(int userId)
{
List <WorkoutModel> tempWorkouts = new List <WorkoutModel>();
using (SqlConnection connection = new SqlConnection(AppSettingsJson.GetConnectionstring()))
{
connection.Open();
SqlCommand dataCommand = new SqlCommand()
{
CommandText = "SELECT * FROM [dbo].[Workouts] Inner join User_Workout on [Workouts].WorkoutId = User_Workout.WorkoutId where UserId = '" + userId + "' SELECT COUNT(Exercise_Workout.ExerciseId) AS NumberOfExercises FROM Exercise_Workout LEFT JOIN Workouts ON Exercise_Workout.WorkoutId = Workouts.WorkoutId GROUP BY Workouts.name ",
Connection = connection
};
using (SqlDataReader workoutReader = dataCommand.ExecuteReader())
{
while (workoutReader.Read())
{
WorkoutModel tempWorkout = new WorkoutModel(Convert.ToInt32(workoutReader["WorkoutId"]), workoutReader["Name"].ToString(), workoutReader["Skillevel"].ToString(), Convert.ToInt32(workoutReader["Time"]), Convert.ToInt32(workoutReader["CaloriesBurned"]), workoutReader["Category"].ToString(), GetAmountOfExercises(Convert.ToInt32(workoutReader["WorkoutId"])));
tempWorkouts.Add(tempWorkout);
}
workoutReader.Close();
return(tempWorkouts);
}
}
}