public SemesterItem getCurrentSemesterByStudyGroup(string studyGroup)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnectionTimeTable();
SemesterItem Item = new SemesterItem();
try
{
using (sqlConnection)
{
string SQL = "SELECT Min( Tag ) AS Tag_start, Max( Tag ) AS Tag_ende,[Semester] FROM [SG_anwesend] " +
"WHERE SGID = '" + studyGroup + "' AND[Semester] IN(SELECT[Semester] FROM[SG_anwesend] " +
"WHERE SGID = '" + studyGroup + "' AND Tag BETWEEN CONVERT(date, DATEADD(WEEK, -2, GETDATE())) AND CONVERT(date, DATEADD(WEEK, 2, GETDATE())) Group BY Semester) Group BY Semester;";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
if (myReader.Read())
{
Item.Semester = Convert.ToInt32(myReader["Semester"]);
Item.Start = Convert.ToDateTime(myReader["Tag_start"]);
Item.End = Convert.ToDateTime(myReader["Tag_ende"]);
Item.StudyGroup = studyGroup;
}
sqlConnection.Close();
sqlConnection = null;
return(Item);
}
}
catch (System.Exception)
{
return(null);
}
}
public SemesterItem[] getSemesterItem(string studyGroup)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnectionTimeTable();
SemesterItem Item = new SemesterItem();
List <SemesterItem> ListItem = new List <SemesterItem>();
try
{
using (sqlConnection)
{
string SQL = "SELECT Min(Tag ) AS Tag_start, Max( Tag ) AS Tag_ende,[Semester] FROM[SG_anwesend] WHERE SGID = 'WI16-1' Group BY Semester; ";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
Item.Semester = Convert.ToInt32(myReader["Semester"]);
Item.Start = Convert.ToDateTime(myReader["Tag_start"]);
Item.End = Convert.ToDateTime(myReader["Tag_ende"]);
Item.StudyGroup = studyGroup;
ListItem.Add(Item);
Item = new SemesterItem();
}
sqlConnection.Close();
sqlConnection = null;
return(ListItem.ToArray());
}
}
catch (System.Exception)
{
return(null);
}
}
private LectureItem[] getTimeTable(string value, DateTime startTime, DateTime endTime, int typ)
{
sqlConnection = null;
sqlConnection = TimeTableDatabase.getConnectionTimeTable();
try
{
using (sqlConnection)
{
//DateTime endTime = new DateTime();
endTime = Convert.ToDateTime(endTime);//DateTime.Now;
DateTime StartHourLessons = new DateTime();
DateTime EndHourLessons = new DateTime();
DateTime LessonsDay = new DateTime();
LectureItem SQLItem = new LectureItem();
List <LectureItem> list = new List <LectureItem>();
//SELECT[Unterricht].[Tag], [Unterricht].Stunde,[Unterricht].OrtsID,[Unterricht].DozentID,[Unterricht].NutzungsID,[Unterricht].Bemerkung,[Stunde].Anfang,[Stunde].Ende,[Zuhoerer].SGID FROM[Unterricht]
//LEFT JOIN[Stunde] ON[Unterricht].Stunde = [Stunde].Stunde
//LEFT JOIN[Zuhoerer] ON[Unterricht].Tag = [Zuhoerer].Tag AND[Unterricht].Stunde = [Zuhoerer].Stunde AND[Unterricht].OrtsID = [Zuhoerer].OrtsID
//WHERE[Zuhoerer].Tag BETWEEN '2017-01-01 00:00:00' AND '2019-01-01 00:00:00' AND[SGID]='WI16-1' ORDER BY[Zuhoerer].SGID, [Unterricht].Tag, [Stunde].Stunde
string SQLWhere = "";
if (typ == 1)
{
SQLWhere = " AND[DozentID] = '" + value + "' ";
}
else
{
SQLWhere = " AND[SGID]= '" + value + "' ";
}
string SQL = "SELECT[Unterricht].[Tag], [Unterricht].Stunde,[Unterricht].OrtsID,[Unterricht].DozentID,[Unterricht].NutzungsID,[Unterricht].Bemerkung,[Stunde].Anfang,[Stunde].Ende,[Zuhoerer].SGID FROM[Unterricht] " +
"LEFT JOIN[Stunde] ON[Unterricht].Stunde = [Stunde].Stunde " +
"LEFT JOIN[Zuhoerer] ON[Unterricht].Tag = [Zuhoerer].Tag AND[Unterricht].Stunde = [Zuhoerer].Stunde AND[Unterricht].OrtsID = [Zuhoerer].OrtsID " +
"WHERE[Zuhoerer].Tag BETWEEN '" + startTime + "' AND '" + endTime + "' " +
SQLWhere + " ORDER BY[Zuhoerer].SGID, [Unterricht].Tag, [Stunde].Stunde";
sqlConnection.Open();
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(SQL, sqlConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
if (myReader["Anfang"].ToString() != "")
{
StartHourLessons = Convert.ToDateTime(myReader["Anfang"]);
}
else
{
StartHourLessons = Convert.ToDateTime("01.01.2000 00:00");
}
if (myReader["Ende"].ToString() != "")
{
EndHourLessons = Convert.ToDateTime(myReader["Ende"]);
}
else
{
EndHourLessons = Convert.ToDateTime("01.01.2000 00:00");
}
LessonsDay = Convert.ToDateTime(myReader["Tag"]);
SQLItem.Start = Convert.ToDateTime(LessonsDay.Date + StartHourLessons.TimeOfDay);
SQLItem.End = Convert.ToDateTime(LessonsDay.Date + EndHourLessons.TimeOfDay);;
SQLItem.Lecturer = myReader["DozentID"].ToString();
SQLItem.Place = myReader["OrtsID"].ToString();
SQLItem.StudyGroup = myReader["SGID"].ToString();
SQLItem.Title = myReader["NutzungsID"].ToString();
//Bemerkungsfeld feld noch
list.Add(SQLItem);
SQLItem = new LectureItem();
}
sqlConnection.Close();
sqlConnection = null;
return(list.ToArray());
}
}
catch (System.Exception)
{
return(null);
}
}
