public List<Course> GetCourseList(DateTime startDate, DateTime endDate)
{
var result = new List<Course>();
MySqlConnection connection = new MySqlConnection(Properties.Settings.Default.CourseDbConnectionString);
MySqlConnection priceConnection = new MySqlConnection(Properties.Settings.Default.CourseDbConnectionString);
MySqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT l.id, t.veranstalter, f.fachbereich ressort, t.beginn start_t, t.von, t.ende ende_t, t.bis, t.ort ort_t,l.inhalt,l.untertitel, l.voraussetzung,
l.start start_l, l.ende ende_l, l.lehrgang_nr,l.titel,l.ort ort_l, t.meldeschluss meldeschluss_t, l.meldeschluss meldeschluss_l ,
l.zielgruppe, l.ziel, l.leiter, l.fachbereich
FROM `tbl_lehrgang` l
join `tbl_fachbereich` f on l.fachbereich = f.id
left outer join `tbl_termine` t on t.lehrgang = l.id
where t.beginn > @startdate and t.beginn < @enddate
order by titel,untertitel,inhalt,lehrgang_nr";
command.Parameters.AddWithValue("@startdate", startDate);
command.Parameters.AddWithValue("@enddate", endDate);
MySqlDataReader Reader;
connection.Open();
Reader = command.ExecuteReader();
Course course = new Course();
CourseDate courseDate= new CourseDate();
string courseNo = string.Empty;
string TitleSubtitleDesc = string.Empty;
while (Reader.Read())
{
if (IsNewCourseDate(Reader, courseNo))
{
if (IsNewCourse(Reader, TitleSubtitleDesc))
{
// general information
course = new Course();
GetHost(Reader, course);
course.Department = Reader.GetString("ressort");
course.Title = Reader.GetString("titel");
course.Subtitle = Reader.GetString("untertitel");
course.Description = Reader.GetString("inhalt");
course.Category = (CourseCategory)Reader.GetInt32("fachbereich");
course.Requirements = Reader.GetString("voraussetzung");
course.TargetAudience = Reader.GetString("zielgruppe");
course.AP = Reader.GetString("ziel").Contains("AP Fortbildung: Ja");
course.Juleica = Reader.GetString("ziel").Contains("JuLeiCa Fortbildung: Ja");
course.Prices = GetPriceData(priceConnection, Reader.GetInt32("id"));
result.Add(course);
TitleSubtitleDesc = course.Title + course.Subtitle + course.Description;
}
courseDate = new CourseDate();
courseDate.CourseNo = Reader.GetString("lehrgang_nr");
courseDate.CheckinDeadline = Reader.GetDateTime("meldeschluss_l");
courseDate.Supervisor = Reader.GetString("Leiter");
courseDate.CheckinDeadline = Reader.GetDateTime("meldeschluss_l");
courseDate.Id = Reader.GetInt32("id");
course.Dates.Add(courseDate);
courseNo = courseDate.CourseNo;
log.DebugFormat("Lehrgang {0} verarbeitet", courseDate.Id);
}
var part = new CoursePart()
{
Start = Reader.GetDateTime("start_t"),
End = Reader.GetDateTime("ende_t"),
Location = Reader.GetString("ort_t")
};
part.Start = part.Start.Date + DateTime.ParseExact(Reader.GetString("von"), "HH:mm:ss", CultureInfo.InvariantCulture).TimeOfDay;
part.End = part.End.Date + DateTime.ParseExact(Reader.GetString("bis"), "HH:mm:ss", CultureInfo.InvariantCulture).TimeOfDay;
courseDate.Parts.Add(part);
}
course.Dates.Add(courseDate);
result.Add(course);
return result;
}
public Course getCourse(int courseId)
{
SqlDataReader reader = null;
Course course = null;
using (SqlCommand c = new SqlCommand(SELECT_COURSE_WITH_ID, connection))
{
c.Parameters.AddWithValue("course", courseId);
reader = c.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
course = new Course(reader.GetInt32(0), reader.GetString(1), reader.GetString(2),reader.GetInt32(3));
}
}
reader.Close();
return course;
}
private static void GetHost(MySqlDataReader Reader, Course course)
{
int host = Reader.GetInt32("veranstalter");
if (host == 1)
{
course.Host = "Stammverband";
}
else
{
course.Host = "Jugend";
}
}
public GradedCourse(Course course)
{
this.course = course;
}
