private static long PrepareStudent(CardInfo cardInfo)
{
string sql = "select student_id,student_no,student_name from student where student_rfid=?";
var ds = SqlLiteHelper.ExecuteQuery(sql, cardInfo.Rfid);
bool update = false;
long student_id = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{
string student_no = row["student_no"].ToString();
string student_name = row["student_name"].ToString();
student_id = (long)row["student_id"];
if (string.Equals(student_name, cardInfo.Name) && string.Equals(student_no, cardInfo.No))
{
return(student_id);
}
update = true;
}
//need insert or update
if (update)
{
SqlLiteHelper.ExecuteNonQuery("update student set student_name=?,student_no=?,updated_time=datetime('now','localtime') where student_id=?", cardInfo.Name, cardInfo.No, student_id);
return(student_id);
}
else
{
SqlLiteHelper.ExecuteNonQuery("insert into student( student_name,student_no,student_rfid,created_time,updated_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);
}
}
internal static CardInfo GetCardInfo(string p, int type)
{
DataSet ds = null;
if (type == 1)
{
//student
var sql = @"select student_no as no ,student_name as name ,student_rfid as rfid from student where student_no=?";
ds = SqlLiteHelper.ExecuteQuery(sql, p);
}
else
{
var sql = @"select teacher_no as no ,teacher_name as name ,teacher_rfid as rfid from student where teacher_no=?";
ds = SqlLiteHelper.ExecuteQuery(sql, p);
}
//first one
foreach (DataRow row in ds.Tables[0].Rows)
{
return(new CardInfo {
No = row["no"].ToString(), Name = row["name"].ToString(), Rfid = row["rfid"].ToString()
});
}
return(null);
}
public static bool IsActive()
{
DataSet ds = SqlLiteHelper.ExecuteQuery("select machine_code,node_name,active_code,expire_time from system");
//AddLog("Count=" + ds.Tables.Count.ToString());
if (ds.Tables[0].Rows.Count > 0)
{
string machine_code = ds.Tables[0].Rows[0]["machine_code"].ToString();
string name = ds.Tables[0].Rows[0]["node_name"].ToString();
activeCode = ds.Tables[0].Rows[0]["active_code"].ToString();
if (ds.Tables[0].Rows[0].IsNull("expire_time"))
{
expireTime = null;
}
else
{
expireTime = (DateTime?)ds.Tables[0].Rows[0]["expire_time"];
}
if (expireTime != null && expireTime.Value.CompareTo(System.DateTime.Now) <= 0)
{
//到期了
expired = true;
}
if (String.Equals(machine_code, machineCode))
{
//AddLog("machine code match!");
}
else
{
//need update
//AddLog("machine code not match ,old =" + machine_code);
SqlLiteHelper.ExecuteNonQuery("update system set machine_code=?,active_code=null ", machineCode);
//机器码,不同,强制设为无效
activeCode = null;
//AddLog("updated");
}
//AddLog("machine name=" + name);
}
else
{
//AddLog("don't have machine code yet ");
int n = SqlLiteHelper.ExecuteNonQuery("insert into system( machine_code) values(?)", machineCode);
//AddLog("inserted :" + n.ToString());
}
if (string.IsNullOrEmpty(activeCode) || expired)
{
return(false);
}
return(true);
}
//用以上报
internal static List <long> GetFinishCourseTaskIds()
{
List <long> ids = new List <long>();
string sql = "select course_task_id as id from course_task where status=?";
var ds = SqlLiteHelper.ExecuteQuery(sql, CourseTask.STATUS_END);
foreach (DataRow row in ds.Tables[0].Rows)
{
long id = (long)row["id"];
ids.Add(id);
}
return(ids);
}
internal static List <Course> GetCourses(string p)
{
var sql = @"select course_id,course_name,course_no from course where removed=? and sub_category=? order by course_no";
var ds = SqlLiteHelper.ExecuteQuery(sql, false, p);
var list = new List <Course>();
foreach (DataRow row in ds.Tables[0].Rows)
{
list.Add(new Course {
No = row["course_no"].ToString(), Name = row["course_name"].ToString(), Id = (long)row["course_id"]
});
}
return(list);
}
internal static Course GetCourse(string p)
{
var sql = @"select course_id,course_name,course_no,key from course where removed=? and course_no=?";
var ds = SqlLiteHelper.ExecuteQuery(sql, false, p);
var list = new List <Course>();
foreach (DataRow row in ds.Tables[0].Rows)
{
//first one
return(new Course {
No = row["course_no"].ToString(), Name = row["course_name"].ToString(), Id = (long)row["course_id"], Key = row["key"].ToString()
});
}
return(null);
}
//"大金刚篇,历史篇,混战篇,人类起源篇"
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 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 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);
}