//"大金刚篇,历史篇,混战篇,人类起源篇"
internal static string GetCategoryText(int categoryId)
{
//所有分类,
var sql = @"select distinct sub_category from course where removed=? and course_category=?";
var ds = SqlLiteHelper.ExecuteQuery(sql, false, categoryId);
var list = new List <string>();
foreach (DataRow row in ds.Tables[0].Rows)
{
//ct.AddWork(new WorkInfo { StudentNo = row["student_no"].ToString(), FilePath = row["file_path"].ToString() });
list.Add(row["sub_category"].ToString());
}
string resultingString = String.Join(",", list);
return(resultingString);
}
private static long?PrepareTeacher(CardInfo cardInfo)
{
if (cardInfo == null)
{
return(null);
}
string sql = "select teacher_id,teacher_no,teacher_name from teacher where teacher_rfid=?";
var ds = SqlLiteHelper.ExecuteQuery(sql, cardInfo.Rfid);
bool update = false;
long teacher_id = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{
string teacher_no = row["teacher_no"].ToString();
string teacher_name = row["teacher_name"].ToString();
teacher_id = (long)row["teacher_id"];
if (string.Equals(teacher_name, cardInfo.Name) && string.Equals(teacher_no, cardInfo.No))
{
return(teacher_id);
}
update = true;
}
//need insert or update
if (update)
{
SqlLiteHelper.ExecuteNonQuery("update teacher set teacher_name=?,teacher_no=?,updated_time=datetime('now','localtime') where teacher_id=?", cardInfo.Name, cardInfo.No, teacher_id);
return(teacher_id);
}
else
{
SqlLiteHelper.ExecuteNonQuery("insert into teacher( teacher_name,teacher_no,teacher_rfid,updated_time,created_time) values(?,?,?,datetime('now','localtime'),datetime('now','localtime'))", cardInfo.Name, cardInfo.No, cardInfo.Rfid);
object result = SqlLiteHelper.ExecuteScalar("SELECT last_insert_rowid()");
return((long)result);
}
}
private void btnSyncCourse_Click(object sender, EventArgs e)
{
JsonResultList jr = RemoteServiceProxy.SyncCourse(FrmActive.machineCode, FrmActive.activeCode);
StoreModel.ProcessNotActived(jr);
if (jr.error)
{
AddLog("错误:" + jr.message);
UIHelper.ShowAlert(jr.message);
return;
}
// AddLog("success=" + jr.message);
//save
string sql = @"select course_no,removed from course";
//load all
DataSet ds = SqlLiteHelper.ExecuteQuery(sql);
Dictionary <string, bool> exists_courses = new Dictionary <string, bool>();
foreach (DataRow row in ds.Tables[0].Rows)
{
string course_no = row["course_no"].ToString();
bool removed = (bool)row["removed"];
exists_courses.Add(course_no, removed);
}
//所有数据是否存在,所有都要更新,多出来的还没删掉的,要标记为删除
int updateCount = 0;
int insertCount = 0;
foreach (var row in jr.data)
{
int idx = GetCourseCategoryIndex(row["categoryNo"]);
//去除击中的
string course_no = row["courseNo"];
if (exists_courses.ContainsKey(course_no))
{
//update
int n = SqlLiteHelper.ExecuteNonQuery("update course set course_name=?,key=?,course_category=?,sub_category=?,updated_time=datetime('now','localtime'),removed=? where course_no=?", row["courseName"], row["courseKey"],
idx, row["subCategoryName"], false, course_no);
//remove hits
exists_courses.Remove(course_no);
//AddLog("update result=" + n.ToString());
updateCount++;
}
else
{
//insert
int n = SqlLiteHelper.ExecuteNonQuery(@"insert into course (course_name,key,course_no,course_category,sub_category,created_time,updated_time,removed)
values( ?,?,?,?,?,datetime('now','localtime'),datetime('now','localtime'),?)",
row["courseName"], row["courseKey"], row["courseNo"], idx, row["subCategoryName"], false);
//AddLog("insert result=" + n.ToString());
insertCount++;
}
}
//尚未集中的,如果没有标为删除,则标为删除
int cc = 0;
foreach (var row in exists_courses)
{
if (!row.Value)
{
SqlLiteHelper.ExecuteNonQuery("update course set removed=?,updatedTime=datetime('now','localtime') where course_no=?", true, row.Key);
cc++;
}
}
AddLog("新增课程:" + insertCount.ToString());
AddLog("更新课程:" + updateCount.ToString());
AddLog("移除课程:" + cc.ToString());
}
internal static void FinishReportCourseTask(long id)
{
//关闭课程
string sql = "update course_task set submit_time=datetime('now','localtime'),status=? where course_task_id=?";
int n = SqlLiteHelper.ExecuteNonQuery(sql, CourseTask.STATUS_SUBMITED, id);
}
internal static CourseTask GetById(long id)
{
string sql = @"select ct.*,m.teacher_no as masterNo,m.teacher_name as masterName,m.teacher_rfid as masterRfid,
a1.teacher_no as a1No,a1.teacher_name as a1Name,a1.teacher_rfid as a1Rfid,
a2.teacher_no as a2No,a2.teacher_name as a2Name,a2.teacher_rfid as a2Rfid,
c.course_no
from course_task ct left join teacher m on m.teacher_id = ct.master_id
left join teacher a1 on a1.teacher_id = ct.assistant1_id
left join teacher a2 on a2.teacher_id = ct.assistant2_id
,course c where ct.course_task_id=? and c.course_id=ct.course_id";
var ds = SqlLiteHelper.ExecuteQuery(sql, id);
CourseTask ct = new CourseTask();
ct.Id = id;
foreach (DataRow row in ds.Tables[0].Rows)
{
DateTime beginTime = (DateTime)row["begin_time"];
DateTime endTime = (DateTime)row["end_time"];
ct.CourseNo = row["course_no"].ToString();
ct.Master = new CardInfo {
No = row["masterNo"].ToString(), Name = row["masterName"].ToString(), Rfid = row["masterRfid"].ToString()
};
ct.Assistant1 = new CardInfo {
No = row["a1No"].ToString(), Name = row["a1Name"].ToString(), Rfid = row["a1Rfid"].ToString()
};
if (!row.IsNull("a2No"))
{
ct.Assistant2 = new CardInfo {
No = row["a2No"].ToString(), Name = row["a2Name"].ToString(), Rfid = row["a2Rfid"].ToString()
};
}
ct.BeginTime = beginTime;
ct.EndTime = endTime;
}
//学生数据
sql = @"select s.student_no,s.student_name,s.student_rfid from course_task_student cts left join student s where s.student_id=cts.student_id and cts.course_task_id=?";
ds = SqlLiteHelper.ExecuteQuery(sql, id);
foreach (DataRow row in ds.Tables[0].Rows)
{
ct.AddStudent(new CardInfo {
No = row["student_no"].ToString(), Name = row["student_name"].ToString(), Rfid = row["student_rfid"].ToString()
});
}
//作品数据
sql = @"select student_no,file_path from course_task_file where course_task_id=?";
ds = SqlLiteHelper.ExecuteQuery(sql, id);
foreach (DataRow row in ds.Tables[0].Rows)
{
ct.AddWork(new WorkInfo {
StudentNo = row["student_no"].ToString(), FilePath = row["file_path"].ToString()
});
}
return(ct);
}