public List <Student> GetStudentsFromClassId(int id)
{
List <Student> tempList = new List <Student>();
using (var con = new DBCon())
{
int tempId = 0;
string tempName = null;
SqlDataReader rs = con.ExecuteStringGet("SELECT * FROM student WHERE ClassID = " + id);
if (rs.HasRows)
{
while (rs.Read())
{
tempId = (int)rs.GetValue(0);
tempName = (string)rs.GetValue(2);
Student tempStudent = new Student(tempId, tempName);
tempList.Add(tempStudent);
}
}
}
return(tempList);
}
public bool CreateGroup(string name, List <int> studentId)
{
int groupId = 0;
int groupSize = studentId.Count();
bool done = false;
using (var con = new DBCon())
{
SqlDataReader rs = con.ExecuteStringGet("INSERT INTO [Group] (name, size) OUTPUT Inserted.id VALUES('" + name + "', '" + groupSize + "');");
while (rs.Read())
{
groupId = (int)rs.GetValue(0);
done = true;
}
}
using (var con = new DBCon())
{
foreach (int id in studentId)
{
done = con.ExecuteStringPut("UPDATE Student SET groupId = " + groupId + " WHERE id = " + id + ";");
}
}
return(done);
}
public bool TestGroupRoom(int grouproomId, DateTime dateStart, DateTime dateEnd)
{
bool isAvailable = false;
using (var con = new DBCon())
{
List <GroupRoom> roomList = new List <GroupRoom>();
string sqlCmd = string.Format(
"SELECT [GroupRoom].id FROM [GroupRoom] " +
"LEFT JOIN Rent ON GroupRoom.id = Rent.GroupRoomId " +
"WHERE(StartDate NOT BETWEEN '{0}' AND '{1}' " +
"AND EndDate NOT BETWEEN '{0}' AND '{1}' " +
"AND '{0}' NOT BETWEEN StartDate AND EndDate " +
"AND '{1}' NOT BETWEEN StartDate AND EndDate) " +
"OR Rent.GroupRoomId IS NULL " +
"AND[GroupRoom].id = '{2}';", dateStart, dateEnd, grouproomId);
SqlDataReader rs = con.ExecuteStringGet(sqlCmd);
while (rs.Read())
{
if (rs.GetInt32(0) == grouproomId)
{
isAvailable = true;
}
}
}
return(isAvailable);
}
public Student GetStudentById(int id)
{
Student tempStudent = null;
using (var con = new DBCon())
{
SqlDataReader rs = con.ExecuteStringGet("SELECT * FROM Student where id=" + id + ");");
int tempId = 0;
string tempName = null;
string tempPassword = null;
if (rs.HasRows)
{
while (rs.Read())
{
tempId = (int)rs.GetValue(0);
tempName = rs.GetString(1);
tempPassword = rs.GetString(2);
}
}
tempStudent = new Student(tempId, tempName, tempPassword);
}
return(tempStudent);
}
public List <ClassRoom> GetClassRoomByAttributes(bool whiteboard, bool monitor, bool projector)
{
List <ClassRoom> classRoomList = new List <ClassRoom>();
using (var con = new DBCon())
{
String exString = ("SELECT * FROM ClassRoom WHERE(1=1");
if (whiteboard)
{
exString = exString + " AND whiteboard = 1";
}
if (monitor)
{
exString = exString + " AND monitor = 1";
}
if (projector)
{
exString = exString + " AND projector = 1";
}
exString = exString + ");";
SqlDataReader rs = con.ExecuteStringGet(exString);
int classRoomId;
string classRoomName;
int classRoomSize;
int classRoomRequestMach;
bool classRoomWhiteboard;
bool classRoomMonitor;
bool classRoomProjector;
while (rs.Read())
{
classRoomId = rs.GetInt32(0);
classRoomName = rs.GetString(1);
classRoomSize = rs.GetInt32(2);
classRoomWhiteboard = rs.GetBoolean(3);
classRoomMonitor = rs.GetBoolean(4);
classRoomProjector = rs.GetBoolean(5);
classRoomRequestMach = CreateBinaryCode(
classRoomWhiteboard, classRoomMonitor, classRoomProjector);
ClassRoom tempClassRoom = new ClassRoom(
classRoomId, classRoomSize, classRoomName, classRoomRequestMach, classRoomSize, 0);
//ClassRoom tempClassRoom = new ClassRoom(
// classRoomId, classRoomSize, classRoomWhiteboard, classRoomMonitor, classRoomProjector);
classRoomList.Add(tempClassRoom);
}
}
return(classRoomList);
}
public List <GroupRoom> GetGroupRoomList(DateTime dateStart, DateTime dateEnd, int grStrl, bool whiteboard, bool monitor)
{
List <GroupRoom> roomList = new List <GroupRoom>();
using (var con = new DBCon())
{
//Her kunne man have brugt en 'SELECT x FROM (SELECT y FROM...)'
//For at lette overskuelighed
string sqlCmd = string.Format(
"SELECT * FROM [GroupRoom] " +
"LEFT JOIN Rent ON GroupRoom.id = Rent.GroupRoomId " +
"WHERE ((StartDate NOT BETWEEN '{0}' AND '{1}' " +
"AND EndDate NOT BETWEEN '{0}' AND '{1}' " +
"AND '{0}' NOT BETWEEN StartDate AND EndDate " +
"AND '{1}' NOT BETWEEN StartDate AND EndDate) " +
"OR Rent.GroupRoomId IS NULL) " +
"AND ([GroupRoom].whiteboard = '{2}' " +
"OR [GroupRoom].whiteboard = '1') " +
"AND ([GroupRoom].monitor = '{3}' " +
"OR [GroupRoom].monitor = '1'); ", dateStart, dateEnd, whiteboard, monitor);
SqlDataReader rs = con.ExecuteStringGet(sqlCmd);
List <GroupRoom> tempList = new List <GroupRoom>();
while (rs.Read())
{
tempList.Add(new GroupRoom(rs.GetInt32(0), rs.GetString(1), rs.GetBoolean(2), rs.GetBoolean(3)));
}
while (tempList.Count > 0)
{
roomList.Add(tempList[0]);
tempList.Remove(tempList[0]);
foreach (GroupRoom g in tempList)
{
if ((roomList.Count > 0) && (g.Id == (roomList[roomList.Count - 1].Id)))
{
roomList.Remove(roomList[roomList.Count - 1]);
}
}
}
}
return(roomList);
}
public List <GroupRoom> GetGroupRooms()
{
List <GroupRoom> groupRooms = new List <GroupRoom>();
using (var con = new DBCon())
{
string sqlCommand = "SELECT * FROM [GroupRoom]";
SqlDataReader rs = con.ExecuteStringGet(sqlCommand);
while (rs.Read())
{
bool whiteboard = (bool)rs.GetValue(2);
bool monitor = (bool)rs.GetValue(2);
GroupRoom room = new GroupRoom(rs.GetInt32(0), rs.GetString(1), whiteboard, monitor);
groupRooms.Add(room);
}
}
return(groupRooms);
}
// Her laves liste LessThanThree med klasselokaler hentet fra DB. Denne liste bruges til
// algoritmesammenligningen. LessThanThree refererer til at objekterne kun skal blive på
// listen så længe groupCount er under 3.
public List <ClassRoom> LessThanThree()
{
List <ClassRoom> LessThanThree = new List <ClassRoom>();
using (var con = new DBCon())
{
SqlDataReader rs = con.ExecuteStringGet("SELECT * FROM ClassRoom");
int tempId = 0;
string tempName = null;
int tempSize = 0;
bool bitWhiteboard = false;
bool bitMonitor = false;
bool bitProjector = false;
int requestMatch = 0;
int groupCount = 0;
int spaceLeft = 0;
if (rs.HasRows)
{
while (rs.Read())
{
tempId = (int)rs.GetValue(0);
tempName = rs.GetString(1);
tempSize = (int)rs.GetValue(2);
bitWhiteboard = (bool)rs.GetValue(3);
bitMonitor = (bool)rs.GetValue(4);
bitProjector = (bool)rs.GetValue(5);
requestMatch = CreateBinaryCode(bitWhiteboard, bitMonitor, bitProjector);
ClassRoom classroom = new ClassRoom(tempId, tempSize, tempName, requestMatch, spaceLeft, groupCount);
LessThanThree.Add(classroom);
}
}
}
return(LessThanThree);
}
public bool HasGroupRooms()
{
bool hasRooms = false;
using (var con = new DBCon())
{
string dbCommand = "SELECT GroupRoom.id FROM GroupRoom LEFT JOIN Rent ON GroupRoom.id = Rent.GroupRoomId WHERE Rent.GroupRoomId IS NULL";
SqlDataReader rs = con.ExecuteStringGet(dbCommand);
while (rs.Read())
{
if (rs.HasRows)
{
hasRooms = true;
}
else
{
hasRooms = false;
}
}
}
return(hasRooms);
}
public Class GetClassFromStudentId(int id)
{
Class tempClass = null;
using (var con = new DBCon())
{
SqlDataReader rs = con.ExecuteStringGet("SELECT * FROM Class where id=(SELECT classId FROM Student where id=" + id + ");");
int tempId = 0;
string tempName = null;
if (rs.HasRows)
{
while (rs.Read())
{
tempId = (int)rs.GetValue(0);
tempName = rs.GetString(1);
}
}
tempClass = new Class(tempId, tempName);
}
return(tempClass);
}
public bool RentClassRoom(int classRoomId, int groupId, DateTime startDate, DateTime endDate)
{
bool rentCreated = false;
using (var con = new DBCon())
{
string sqlCmd = string.Format(
"INSERT INTO Rent(ClassRoomID, GroupID, StartDate, EndDate) " +
"OUTPUT INSERTED.id " +
"VALUES('{0}', '{1}', '{2}', '{3}');", classRoomId, groupId, startDate.ToString("s"), endDate.ToString("s"));
SqlDataReader rs = con.ExecuteStringGet(sqlCmd);
while (rs.Read())
{
if (rs.GetValue(0) != null)
{
rentCreated = true;
}
}
}
return(rentCreated);
}
public bool RentGroupRoom(int grouproomId, int groupId, DateTime dateStart, DateTime dateEnd)
{
bool isRented = false;
using (var con = new DBCon())
{
string sqlCmd = string.Format(
"INSERT INTO Rent(GroupRoomID, GroupID, StartDate, EndDate) " +
"OUTPUT INSERTED.id " +
"VALUES('{0}', '{1}', '{2}', '{3}');", grouproomId, groupId, dateStart, dateEnd);
SqlDataReader rs = con.ExecuteStringGet(sqlCmd);
while (rs.Read())
{
if (rs.GetValue(0) != null)
{
isRented = true;
}
}
}
return(isRented);
}
//takes boolbits from DB and creates a binary code to int... Denne metode skal bruges når DB-table
// over classRoomWaitingList skal omskrives til objekter. Objektet skal dannes som et
// RequestClassroom med denne property requestCode.
public List <RequestClassroom> GetAllRequests()
{
List <RequestClassroom> stillNotFulfilled = new List <RequestClassroom>();
using (var con = new DBCon())
{
SqlDataReader rs = con.ExecuteStringGet("SELECT * FROM ClassRoomWaitinglist");
int tempId = 0;
int tempGroupId = 0;
int tempSize = 0;
int requestCode = 0;
bool bitWhiteboard = false;
bool bitMonitor = false;
bool bitProjector = false;
if (rs.HasRows)
{
while (rs.Read())
{
tempId = (int)rs.GetValue(0);
tempGroupId = (int)rs.GetValue(1);
tempSize = (int)rs.GetValue(2);
bitWhiteboard = (bool)rs.GetValue(3);
bitMonitor = (bool)rs.GetValue(4);
bitProjector = (bool)rs.GetValue(5);
requestCode = CreateBinaryCode(bitWhiteboard, bitMonitor, bitProjector);
RequestClassroom requestClassroom = new RequestClassroom(tempGroupId, tempSize, requestCode);
stillNotFulfilled.Add(requestClassroom);
}
}
}
return(stillNotFulfilled);
}
public bool CanTheyRent(int groupId, DateTime dateStart, DateTime dateEnd)
{
bool canTheyRent = true;
using (var con = new DBCon())
{
string sqlCmd = string.Format(
"SELECT GroupID FROM Rent " +
"WHERE(StartDate BETWEEN '{1}' AND '{2}' " +
"OR EndDate BETWEEN '{1}' AND '{2}' " +
"AND GroupId = '{0}');", groupId, dateStart, dateEnd);
SqlDataReader rs = con.ExecuteStringGet(sqlCmd);
while (rs.Read())
{
if (rs.GetInt32(0) == groupId)
{
canTheyRent = false;
}
}
}
return(canTheyRent);
}