public CFIDetailDialog(object selectedItem)
{
this.InitializeComponent();
detail = selectedItem as CFIDetail;
binding(richEBCRN, detail.CRN);
binding(richEBSubjectCode, detail.SubjectCode);
binding(richEBCompetencyName, detail.CompetencyName);
binding(richEBStartDate, detail.StartDate);
binding(richEBEndDate, detail.EndDate);
binding(richEBDayOfWeek, detail.DayOfWeek);
binding(richEBTime, detail.Time);
binding(richEBRoom, detail.Room);
binding(richEBLecturer, detail.Lecturer);
binding(richEBCampus, detail.Campus);
}
protected override async void OnNavigatedTo(NavigationEventArgs e)
{
String selectedSubject = e.Parameter.ToString();
try
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
string TB_SR2018 = "tblSISCRNs_SR004_2018_S2";
string TB_CP = "tblSubjectCompetencies";
string TB_CS = "tblCRNSessions";
string TB_SD = "tblStaffDetails";
//Basic Query
String BQuery = String.Format("SELECT {0}.CRN,{1}.ITSubject as ITSubject,{2}.Time as Time,{2}.`Day_of_Week` as Day_Of_Week," +
"{3}.`unique_name` as Lecturer,`Course Title`,`Meeting Start Date`," +
"`Meeting Finish Date`, Room,`Lecturer ID`, Campus from {0}", TB_SR2018, TB_CP, TB_CS, TB_SD);
//Left join Compentency
String LJCP = String.Format("left join {1} on {0}.`Course Code`= {1}.CourseNumber", TB_SR2018, TB_CP);
//Left join CRNSession
String LJCS = String.Format("left join {1} on {1}.CRN = {0}.CRN", TB_SR2018, TB_CS);
//Left join StaffDetails
String LJSD = String.Format("left join {1} on {1}.`InstructorID` = {0}.`Lecturer ID`", TB_SR2018, TB_SD);
//Where
String WHERE = String.Format("where `Course Title` = \"{0}\" and Day_Of_Week!=\"\" and Room!=\"\"", selectedSubject);
//Final QueryString
String sqlQueryString = String.Format("{0} {1} {2} {3} {4}", BQuery, LJCP, LJCS, LJSD, WHERE);
connection.Open();
MySqlCommand getCommand = connection.CreateCommand();
getCommand.CommandText = sqlQueryString;
using (MySqlDataReader reader = getCommand.ExecuteReader())
{
mResultList.Clear();
while (reader.Read())
{
CFIDetail detail = new CFIDetail();
detail.CRN = reader.GetString("CRN");
detail.SubjectCode = reader.GetString("ITSubject");
detail.CompetencyName = reader.GetString("Course Title");
detail.StartDate = reader.GetString("Meeting Start Date");
detail.EndDate = reader.GetString("Meeting Finish Date");
detail.DayOfWeek = reader.GetString("Day_Of_Week");
detail.Time = reader.GetString("Time");
detail.Room = reader.GetString("Room");
detail.Lecturer = reader.GetString("Lecturer");
detail.Campus = reader.GetString("Campus");
mResultList.Add(detail);
}
if (mResultList.Count > 0)
{
btnFiltering.IsEnabled = true;
btnSorting.IsEnabled = true;
}
else
{
btnFiltering.IsEnabled = false;
btnSorting.IsEnabled = false;
}
RefreshListView(mResultList);
}
}
}
catch (Exception ex)
{
MessageDialog dialog = new MessageDialog("Couldn't connect to database!");
await dialog.ShowAsync();
}
finally
{
}
}
