/// <summary>
/// Delete Daily Activities from Database
/// </summary>
/// <param name="dailyActivities">Daily Activities</param>
public void DailyActivitiesDataDelete(DailyActivitie dailyActivities)
{
connection.Open();
string query =
"DELETE FROM DailyActivities WHERE Id = @USERID";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@USERID", SqlDbType.Int);
cmd.Parameters["@USERID"].Value = dailyActivities.Id;
cmd.ExecuteNonQuery();
connection.Close();
}
/// <summary>
/// Add new DailyActivities to Database
/// </summary>
/// <param name="dailyActivities">Daily Activities</param>
/// <param name="user"User>User</param>
public void DailyActivitiesDataAdd(DailyActivitie dailyActivities, User user)
{
connection.Open();
string query =
"INSERT INTO DailyActivities(Activity, Date, UserId) VALUES (@ACTIVITY, @DATE, @USERID)";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@ACTIVITY", SqlDbType.Int);
cmd.Parameters.Add("@DATE", SqlDbType.DateTime);
cmd.Parameters.Add("@USERID", SqlDbType.Int);
cmd.Parameters["@ACTIVITY"].Value = dailyActivities.Activitie.Id;
cmd.Parameters["@DATE"].Value = dailyActivities.Date;
cmd.Parameters["@USERID"].Value = user.Id;
cmd.ExecuteNonQuery();
connection.Close();
}
/// <summary>
/// Collect all of Daily Activities form Database, which are connected to User Id, at specified Date
/// </summary>
/// <param name="user">User</param>
/// <param name="date">Date</param>
/// <returns></returns>
public List <DailyActivitie> DailyActivitiesDataCollectionDate(User user, DateTime date)
{
List <DailyActivitie> dailyActivities = new List <DailyActivitie>();
connection.Open();
string query = "SELECT * FROM DailyActivities WHERE UserId = @USERID AND Date = @DATE";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@USERID", SqlDbType.Int);
cmd.Parameters.Add("@DATE", SqlDbType.DateTime);
cmd.Parameters["@USERID"].Value = user.Id;
cmd.Parameters["@DATE"].Value = date.Date;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Activity activity = new Activity(1, "a", "a", 1);
int activityId = reader.GetInt32(1);
string query2 = "SELECT * FROM Activities WHERE Id = @ID";
SqlCommand cmd2 = new SqlCommand(query2, connection);
cmd2.Parameters.Add("@ID", SqlDbType.Int);
cmd2.Parameters["@ID"].Value = activityId;
SqlDataReader reader2 = cmd2.ExecuteReader();
int a = 0;
while (reader2.Read() && a == 0)
{
Activity newActivity = new Activity((uint)reader2.GetInt32(0), reader2.GetString(1), reader2.GetString(2),
reader2.GetInt32(3));
a++;
activity = newActivity;
}
reader2.Close();
DailyActivitie dailyActivity = new DailyActivitie((uint)reader.GetInt32(0), activity, date.Date);
dailyActivities.Add(dailyActivity);
}
reader.Close();
connection.Close();
return(dailyActivities);
}
/// <summary>
/// Update DailyActivities in Database
/// </summary>
/// <param name="activity">Activity</param>
/// <param name="dailyActivities">Daily Activities</param>
/// <param name="user">User</param>
public void DailyActivitiesDataUpdate(Activity activity, DailyActivitie dailyActivities, User user)
{
connection.Open();
string query = "UPDATE DailyActivities SET Activity = @ACTIVITY, Date = @DATE, UserId = @USERID WHERE Id = @ID";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.Add("@ACTIVITY", SqlDbType.Int);
cmd.Parameters.Add("@DATE", SqlDbType.DateTime);
cmd.Parameters.Add("@USERID", SqlDbType.Int);
cmd.Parameters.Add("@ID", SqlDbType.Int);
cmd.Parameters["@ACTIVITY"].Value = activity.Id;
cmd.Parameters["@DATE"].Value = dailyActivities.Date;
cmd.Parameters["@USERID"].Value = user.Id;
cmd.Parameters["@ID"].Value = dailyActivities.Id;
cmd.ExecuteNonQuery();
connection.Close();
}
