private async void getscore()
{
if (scorelist.ItemsSource != readcore)
{
StatisticsDetailViewModel sd = (StatisticsDetailViewModel)this.DataContext;
string sql = $"select * from Score where stulisturl='{sd.DetailCourse.StudentListUrl}'";
readcore.Clear();
await Task.Run(() =>
{
OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath);
if (reader != null)
{
while (reader.Read())
{
Score a = new Score();
a.StuNum = reader["stunum"].ToString();
a.StuName = reader["stuname"].ToString();
a.Attendance = Convert.ToDecimal(reader["attendance"].ToString());
a.Homework = Convert.ToDecimal(reader["homework"].ToString());
a.Addition = Convert.ToDecimal(reader["addition"].ToString());
a.Exam = Convert.ToDecimal(reader["exam"].ToString());
a.Final = Convert.ToDecimal(reader["final"].ToString());
this.Dispatcher.Invoke(() =>
{
readcore.Add(a);
});
}
reader.Close();
AccessDBHelper.CloseConnectDB();
this.Dispatcher.Invoke(() =>
{
this.scorelist.ItemsSource = readcore;
if (readcore != null && readcore.Count != 0)
this.ediscore.Content = "保存成绩";
});
}
});
}
}
bool get(string teachernum)
{
string filename = String.Format("{0}.mdb", teachernum);//以教师号命名数据库文件
FileInfo dbfile = new FileInfo(filename);
string filePath = new DirectoryInfo(".").FullName + "\\" + filename;
App.Databasefilepath = filePath;
if (dbfile.Exists)
{
//考勤表
AccessDBHelper.CreateTable("create table Attendance (id autoincrement primary key,stuname text(50), stunum text(20),classnum text(10),coursenum text(10),coursetime text(50), arrivestate text(1),stulisturl text(50))", filePath);
//作业表
AccessDBHelper.CreateTable("create table Homework (id autoincrement primary key,stuname text(50), stunum text(20),classnum text(10),coursenum text(10),score text(10),hcount text(5),stulisturl text(50))", filePath);
//成绩表
// AccessDBHelper.CreateTable("create table Score (id autoincrement primary key,stuname text(50),stunum text(20),coursenum text(10), attendance text(5), homework text(5),addition text(5), exam text(5),final text(5))", filePath);
AccessDBHelper.CreateTable("create table Score (id autoincrement primary key,stuname text(50),stunum text(20),stulisturl text(50), attendance text(10), homework text(10),addition text(10), exam text(10),final text(10))", filePath);
//作业内容表
AccessDBHelper.CreateTable("create table Record (id autoincrement primary key, stulisturl text(50), hcount text(5),publishtime text(50),content text(200))", filePath);
return true;
}
else
{
List<ClassDetail> classtable = TableHelp.GetClassTable(teachernum);
if (classtable == null)
{
File.Delete(App.Databasefilepath);
return false;
}
//创建数据库
AccessDBHelper.CreateDB(filePath);
//AccessDBHelper.ConnectDB(filePath);
//创建数据表
AccessDBHelper.CreateTable("create table classtable (id autoincrement primary key, coursenum TEXT(10), coursename TEXT(100), classroom TEXT(100), lastweeks TEXT(100), classtype text(50) ,subject TEXT(80), stuclassnum TEXT(100), stulisturl TEXT(50),courseday TEXT(10),coursetime TEXT(10))", filePath);
string itempattentb = "insert into classtable (coursenum, coursename, classroom, lastweeks , classtype ,subject, stuclassnum, stulisturl,courseday,coursetime) values";
foreach (var item in classtable)
{
string LastWeeks = "";
string StuClassNum = "";
for (int i = 0; i < item.LastWeeks.Length; i++)
{
//if (item.LastWeeks[i] == -1)
//{
// LastWeeks = "单周";
// break;
//}
//if (item.LastWeeks[i] == -2)
//{
// LastWeeks = "双周";
// break;
//}
if (i != 0)
LastWeeks += ",";
LastWeeks += item.LastWeeks[i];
}
for (int i = 0; i < item.StuClassNum.Length; i++)
{
if (i != 0)
StuClassNum += ",";
StuClassNum += item.StuClassNum[i];
}
string insert = String.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", item.CourseNum, item.CourseName, item.Classroom, LastWeeks, item.ClassType, item.Subject, StuClassNum, item.StudentListUrl, item.CourseDay, item.CourseTime);
string sql = itempattentb + insert;
AccessDBHelper.CreateTable(sql, filePath);
}
//学生名单表们
var stuurls = from url in classtable where url.StudentListUrl != null select url.StudentListUrl;
//url一样的删掉，获得这课的学生名单号。
List<string> disurls = stuurls.ToList();
disurls = disurls.Distinct().ToList();
for (int i = 0; i < disurls.Count; i++)
{
//实验课学生名单获取
if (disurls[i] != null)
{
List<TeachClassStu> stu = TableHelp.GetJxbStuList(disurls[i]);
string tablename = disurls[i];
string[] SQLTransaction = new string[stu.Count];
string sql = "create table " + tablename + " (stunum TEXT(50) primary key, stuname TEXT(100),sex TEXT(4),subject TEXT(100),classnum TEXT(100),classstate TEXT(50),classtype TEXT(50),num autoincrement)";
//创建教学班表
AccessDBHelper.CreateTable(sql, filePath);
string itempatten = "insert into " + tablename + " (stunum, stuname, sex, subject, classnum, classstate, classtype) values ";
for (int trans = 0; trans < SQLTransaction.Length; trans++)
{
string insert = String.Format(itempatten + "('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", stu[trans].StuNum, stu[trans].StuName, stu[trans].Sex, stu[trans].Subject, stu[trans].ClassNum, stu[trans].ClassState, stu[trans].ClassType);
SQLTransaction[trans] = insert;
}
AccessDBHelper.Transaction(SQLTransaction, filePath);
}
//理论课学生名单获取
//else if (disurls[i].Substring(20).StartsWith("A") || disurls[i].Substring(20).StartsWith("R"))
//{
// List<TeachClassStuA> stu = TableHelp.GetJxbStuList(disurls[i]) as List<TeachClassStuA>;
// string tablename = disurls[i].Substring(20);
// string[] SQLTransaction = new string[stu.Count];
// string sql = "create table " + tablename + " (num Integer,subject TEXT(100),stunum TEXT(100),stuname TEXT(100),sex TEXT(100),classnum TEXT(100),inrollyear TEXT(4),classstate TEXT(100))";
// AccessDBHelper.CreateTable(sql, filePath);
// string itempatten = "insert into " + tablename + " (num, subject, stunum, stuname, sex, classnum,inrollyear,classstate) values ";
// for (int trans = 0; trans < SQLTransaction.Length; trans++)
// {
// string insert = String.Format(itempatten + "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", stu[trans].Num, stu[trans].Subject, stu[trans].StudentNum, stu[trans].Name, stu[trans].Sex, stu[trans].ClassNum, stu[trans].Year, stu[trans].ClassState);
// SQLTransaction[trans] = insert;
// }
// AccessDBHelper.Transaction(SQLTransaction, filePath);
//}
//超出我的预料啊
else
{
return false;
}
}
//建表
//考勤表
AccessDBHelper.CreateTable("create table Attendance (id autoincrement primary key,stuname text(50), stunum text(20),classnum text(10),coursenum text(10),coursetime text(50), arrivestate text(1),stulisturl text(50))", filePath);
//作业表
AccessDBHelper.CreateTable("create table Homework (id autoincrement primary key,stuname text(50), stunum text(20),classnum text(10),coursenum text(10),score text(10),hcount text(5),stulisturl text(50))", filePath);
//成绩表
// AccessDBHelper.CreateTable("create table Score (id autoincrement primary key,stuname text(50),stunum text(20),coursenum text(10), attendance text(5), homework text(5),addition text(5), exam text(5),final text(5))", filePath);
AccessDBHelper.CreateTable("create table Score (id autoincrement primary key,stuname text(50),stunum text(20),stulisturl text(50), attendance text(10), homework text(10),addition text(10), exam text(10),final text(10))", filePath);
//作业内容表
AccessDBHelper.CreateTable("create table Record (id autoincrement primary key, stulisturl text(50), hcount text(5),publishtime text(50),content text(200))", filePath);
List<Score> readcore = new List<Score>();
//为成绩表添加内容
for (int i = 0; i < disurls.Count; i++)
{
string sql = $"select stuname,stunum,classnum from {disurls[i]}";
OleDbDataReader reader = AccessDBHelper.ExecuteReader(sql, App.Databasefilepath);
if (reader != null)
{
while (reader.Read())
{
Score a = new Score();
a.StuNum = reader["stunum"].ToString();
a.StuName = reader["stuname"].ToString();
a.Homework = 0;
a.Attendance = 0;
a.Addition = 0;
a.Exam = 0;
a.Final = 0;
readcore.Add(a);
}
reader.Close();
AccessDBHelper.CloseConnectDB();
string[] SQLTransaction = new string[readcore.Count];
string itempatten = $"insert into Score (stuname,stunum,stulisturl,attendance,homework,addition,exam,final) values ";
for (int j = 0; j < SQLTransaction.Length; j++)
{
string insert = itempatten + $"('{readcore[j].StuName}','{readcore[j].StuNum}','{disurls[i]}','{readcore[j].Attendance}','{readcore[j].Homework}','{readcore[j].Addition}','{readcore[j].Exam}','{readcore[j].Final}')";
SQLTransaction[j] = insert;
}
AccessDBHelper.Transaction(SQLTransaction, App.Databasefilepath);
readcore.Clear();
}
}
return true;
}
}
