private async Task<List<Homework>> gethomeworkasync(string url)
{
string sql = $"select stuname,stunum,classnum,score,hcount from Homework where stulisturl='{url}'";
List<Homework> c = new List<Homework>();
return await Task.Run(() =>
{
OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath);
if (reader != null)
{
while (reader.Read())
{
Homework a = new Homework();
a.StuNum = reader["stunum"].ToString();
a.StuName = reader["stuname"].ToString();
a.ClassNum = reader["classnum"].ToString();
a.Score = Convert.ToDecimal(reader["score"].ToString());
a.Count = Convert.ToInt32(reader["hcount"].ToString());
this.Dispatcher.Invoke(() =>
{
c.Add(a);
});
}
reader.Close();
AccessDBHelper.CloseConnectDB();
}
return c;
});
}
/// <summary>
/// 获取作业成绩记录
/// </summary>
private async void gethomework()
{
if (currenthomelist != homeworklistview.ItemsSource)
{
//this.progess.Visibility = Visibility.Visible;
StatisticsDetailViewModel sd = (StatisticsDetailViewModel)this.DataContext;
this.Dispatcher.Invoke(() =>
{
sd = (StatisticsDetailViewModel)this.DataContext;
});
string sql = $"select stuname,stunum,classnum,score,hcount from Homework where stulisturl='{sd.DetailCourse.StudentListUrl}';";
await Task.Run(() =>
{
OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath);
if (reader != null)
{
if (attendancelist != null)
this.Dispatcher.Invoke(() =>
{
currenthomelist.Clear();
});
while (reader.Read())
{
Homework a = new Homework();
a.StuNum = reader["stunum"].ToString();
a.StuName = reader["stuname"].ToString();
a.ClassNum = reader["classnum"].ToString();
a.Score = Convert.ToDecimal(reader["score"].ToString());
a.Count = Convert.ToInt32(reader["hcount"].ToString());
this.Dispatcher.Invoke(() =>
{
currenthomelist.Add(a);
});
}
reader.Close();
}
AccessDBHelper.CloseConnectDB();
});
int c = 0;
string avg = $"select homework FROM Score WHERE stulisturl='{sd.DetailCourse.StudentListUrl}'";
OleDbDataReader readerw = AccessDBHelper.ExecuteReader(avg, App.Databasefilepath);
if (readerw != null)
{
while (readerw.Read())
{
try
{
currenthomelist[c++].Avg = Convert.ToDecimal(readerw["homework"].ToString());
}
catch (Exception)
{
readerw.Close();
AccessDBHelper.CloseConnectDB();
break;
}
}
}
this.homeworklistview.ItemsSource = currenthomelist;
this.homeworktime.ItemsSource = (from n in currenthomelist select n.Count).ToList().Distinct();
readhomelist = currenthomelist;
}
}
/// <summary>
/// 添加作业成绩
/// </summary>
private async void edithome_Click(object sender, RoutedEventArgs e)
{
StatisticsDetailViewModel sd = (StatisticsDetailViewModel)this.DataContext;
if (this.edithome.Content.ToString() == "添加成绩")
{
if (this.currenthomelist != null)
this.currenthomelist.Clear();
//string sql = $"select stuname,stunum,classnum,score,count FROM homework stulisturl='{sd.DetailCourse.StudentListUrl}';";
string sql = $"select stuname,stunum,classnum from {sd.DetailCourse.StudentListUrl}";
//this.progess.IsActive = true;
//await Task
//await Task.Delay(1000);
await Task.Run(() =>
{
OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath);
if (reader != null)
{
while (reader.Read())
{
Homework a = new Homework();
a.StuNum = reader["stunum"].ToString();
a.StuName = reader["stuname"].ToString();
a.ClassNum = reader["classnum"].ToString();
a.Score = 0;
a.Count = this.homeworktime.Items.Count + 1;
a.Stulisturl = sd.DetailCourse.StudentListUrl;
this.Dispatcher.Invoke(() =>
{
currenthomelist.Add(a);
});
}
reader.Close();
}
AccessDBHelper.CloseConnectDB();
this.Dispatcher.Invoke(() =>
{
this.homeworklistview.ItemsSource = null;
this.homeworklistview.ItemsSource = currenthomelist;
//this.homeworktime.Items.Add(currenthomelist[0].Count);
this.homeworktime.IsEnabled = false;
this.edithome.Content = "保存成绩";
});
});
//this.progess.IsActive = false;
}
else
{
//保存成绩
string[] SQLTransaction = new string[currenthomelist.Count - 1];
string itempatten = $"insert into Homework (stuname,stunum,classnum,score,coursenum,hcount,stulisturl) values ";
for (int i = 0; i < SQLTransaction.Length; i++)
{
string insert = itempatten + $"('{currenthomelist[i].StuName}','{currenthomelist[i].StuNum}','{currenthomelist[i].ClassNum}','{currenthomelist[i].Score}','{sd.DetailCourse.CourseNum}','{currenthomelist[0].Count}','{currenthomelist[0].Stulisturl}')";
SQLTransaction[i] = insert;
}
AccessDBHelper.Transaction(SQLTransaction, App.Databasefilepath);
this.homeworktime.IsEnabled = true;
this.readhomelist.AddRange(currenthomelist);
string setscore=$"UPDATE score as t1,(SELECT stunum,AVG(score)AS avgscore FROM homework WHERE stulisturl='{currenthomelist[0].Stulisturl}' GROUP BY stunum)as t2 SET t1.homework = t2.avgscore WHERE t1.stunum = t2.stunum";
//string setscore = $"UPDATE score SET homework=( SELECT AVG(score) FROM homework WHERE stunum = score.stunum and stulisturl = '{currenthomelist[0].Stulisturl}' )";
AccessDBHelper.ExecuteNonQuery2(setscore, App.Databasefilepath);
this.edithome.Content = "添加成绩";
this.homeworktime.ItemsSource = null;
this.homeworktime.ItemsSource = (from n in readhomelist select n.Count).ToList().Distinct();
MessageBox.Show("已保存");
}
}
private ObservableCollection<Homework> gethomeworkasync(string url)
{
string sql = $"select stuname,stunum,classnum,score,hcount from Homework where stulisturl='{url}'";
ObservableCollection<Homework> c = new ObservableCollection<Homework>();
//return await Task.Run(() =>
//{
// OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath);
// if (reader != null)
// {
// while (reader.Read())
// {
// Homework a = new Homework();
// a.StuNum = reader["stunum"].ToString();
// a.StuName = reader["stuname"].ToString();
// a.ClassNum = reader["classnum"].ToString();
// a.Score = Convert.ToDecimal(reader["score"].ToString());
// a.Count = Convert.ToInt32(reader["hcount"].ToString());
// c.Add(a);
// }
// reader.Close();
// AccessDBHelper.CloseConnectDB();
// }
// return c;
OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath);
if (reader != null)
{
while (reader.Read())
{
Homework a = new Homework();
a.StuNum = reader["stunum"].ToString();
a.StuName = reader["stuname"].ToString();
a.ClassNum = reader["classnum"].ToString();
a.Score = Convert.ToDecimal(reader["score"].ToString());
a.Count = Convert.ToInt32(reader["hcount"].ToString());
c.Add(a);
}
reader.Close();
AccessDBHelper.CloseConnectDB();
}
return c;
//});
}
