public void AddStudentToGroup(string userId, string groupId)
{
handler = new DataHandler();
string insert =
"INSERT INTO user_UsersInGroups (UserId, GroupId) VALUES ('" + userId + "','" + groupId + "')";
handler.ExecuteNonQuery(insert);
}
public PlaceHolder BuildNotifications(string userId)
{
DataHandler handler = new DataHandler();
string query;
PlaceHolder ph = new PlaceHolder();
DataTable[] tables = new DataTable[2];
ArrayList messages = new ArrayList();
GroupService groupService = new GroupService();
//http://stackoverflow.com/questions/5672862/check-if-datetime-instance-falls-in-between-other-two-datetime-objects
//notification: you added a teacher
query =
"SELECT * FROM user_UserHasTeachers " +
"WHERE TeacherId = ''" +
"ORDER BY Timestamp";
tables[0] = handler.GetDataTable(query);
foreach (DataRow row in tables[0].Rows)
{
}
//make sense of the data tables
//for each table
foreach (DataTable dt in tables)
{
//check timestamp
//insert into position
}
return ph;
}
public DataTable GetMemberOfData(string UserId)
{
handler = new DataHandler();
string select =
"SELECT * FROM user_Groups, user_UsersInGroups " +
"WHERE user_UsersInGroups.UserId = '" + UserId + "' " +
"AND user_Groups.GroupId = user_Groups.GroupId";
return handler.GetDataTable(select);
}
public void CreateGroup(string GroupId, string GroupName, string UserId)
{
handler = new DataHandler();
string insert =
"INSERT INTO user_Groups (GroupId, GroupName, TeacherId) " +
"VALUES ('" + GroupId + "','" + GroupName + "','" + UserId + "')";
handler.ExecuteNonQuery(insert);
}
public DataTable GetHasTeachersData(string UserId)
{
handler = new DataHandler();
string select =
"SELECT user_UserHasTeachers.TeacherId, aspnet_Users.UserName " +
"FROM user_UserHasTeachers, aspnet_Users " +
"WHERE user_UserHasTeachers.TeacherId = aspnet_Users.UserId " +
"AND user_UserHasTeachers.UserId = '" + UserId + "'";
return handler.GetDataTable(select);
}
public static void SubscribeToTeacher(string teacherEmail, string userId)
{
//get the teacher's userId from the email address the user entered
DataHandler handler = new DataHandler();
string select = "SELECT UserId FROM aspnet_Membership WHERE LoweredEmail = '" + teacherEmail.ToLower() + "'";
string teacherId = handler.ExecuteScalar(select);
//add the teacher to the student by the teacher's userId
string insert = "INSERT INTO user_UserHasTeachers (TeacherId, UserId) VALUES ('" + teacherId + "', '" + userId + "')";
handler.ExecuteNonQuery(insert);
}
public int GetProspectiveStudentCount(string UserId)
{
handler = new DataHandler();
//this find students that added a teacher but do not exist in their group
//critical: needs to include a comparison to the teacher's groups
//right now, a student could be in someone else's group and this would exlude from from this teacher's query
string select =
"SELECT COUNT(aspnet_Users.UserName) " +
"FROM user_UserHasTeachers, aspnet_Users " +
"WHERE TeacherId = '" + UserId + "' " +
"AND aspnet_Users.UserId = user_UserHasTeachers.UserId " +
"AND aspnet_Users.UserId NOT IN " +
"(SELECT UserId FROM user_UsersInGroups)";
int count;
int.TryParse(handler.ExecuteScalar(select), out count);
return count;
}
public DataTable GetUsersInGroup(string groupId)
{
handler = new DataHandler();
string select =
"SELECT aspnet_Users.UserId, aspnet_Users.UserName " +
"FROM user_UsersInGroups, aspnet_Users " +
"WHERE user_UsersInGroups.UserId = aspnet_Users.UserId " +
"AND user_UsersInGroups.GroupId = '" + groupId + "'";
return handler.GetDataTable(select);
}
public DataTable GetTable(string userId, string tableId)
{
handler = new DataHandler();
string select;
switch (tableId)
{
case "supervisorOf":
select = "SELECT * FROM user_Groups WHERE TeacherId = '" + userId + "'";
return handler.GetDataTable(select);
case "memberOf":
select =
"SELECT * FROM user_Groups, user_UsersInGroups " +
"WHERE user_UsersInGroups.UserId = '" + userId + "' " +
"AND user_Groups.GroupId = user_Groups.GroupId";
return handler.GetDataTable(select);
}
return null;
}
public DataTable GetSupervisorOfData(string UserId)
{
handler = new DataHandler();
string select = "SELECT * FROM user_Groups WHERE TeacherId = '" + UserId + "'";
return handler.GetDataTable(select);
}
public DataTable GetProspectiveStudentsData(string UserId)
{
handler = new DataHandler();
string select =
"SELECT aspnet_Users.UserName, aspnet_Users.UserId " +
"FROM user_UserHasTeachers, aspnet_Users " +
"WHERE TeacherId = '" + UserId + "' " +
"AND aspnet_Users.UserId = user_UserHasTeachers.UserId " +
"AND aspnet_Users.UserId NOT IN " +
"(SELECT UserId FROM user_UsersInGroups)";
return handler.GetDataTable(select);
}
