def examStudentInsert(id,name,class_,finger,face,is_ic):
with configDB.configServerDB() as cur:
print('server.examStudentInsert')
cur.execute("insert into student(id,name,class,finger,face,is_ic) \
values({0},'{1}',{2},{3},{4},{5})\
on conflict(id) do update set\
name = excluded.name, class = excluded.class, finger = excluded.finger,\
face = excluded.face, is_ic = excluded.is_ic".format(id,name,class_,finger,face,is_ic))
def loginUserDownload(): #OK
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("loginUserDownload")
curServer.execute('''select * from "user" ''')
rows = curServer.fetchall()
curLocal.execute('''delete from "user"''')
for row in rows:
curLocal.execute('''insert into "user" values %s ''', (row, ))
def examStudentDownloadFiles(*exam_ids):
with configDB.configServerDB() as cur:
with configFTP() as ftp:
print("examStudentDownloadFiles")
for exam_id in exam_ids:
cur.execute("select stu_id from exam_member where exam_id=%s"%(exam_id,))
stu_ids = cur.fetchall()
for stu_id in stu_ids:
stu_id = stu_id[0]
cur.execute("select finger, face from student where id=%s"%(stu_id,))
fingerCnt, faceCnt = cur.fetchone()
downloadGroupFiles(ftp, stu_id,".txt",fingerCnt,config.localExamStudentDir, config.serverStudentDir)
downloadGroupFiles(ftp, stu_id,".jpg",faceCnt,config.localExamStudentDir, config.serverStudentDir)
def examExamDownload(*exam_ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("examExamDownload")
for exam_id in exam_ids:
dir = config.localExamRecordDir + '/{0}'.format(exam_id)
print(dir)
if os.path.exists(dir) == False:
os.mkdir(dir)
curServer.execute("select * from exam where id=%s" %
(exam_id, ))
row = curServer.fetchone()
curLocal.execute(
"insert into exam values %s on conflict(id) do update set \
name=excluded.name, location=excluded.location, teacher=excluded.teacher"
% (row, ))
def examMemberDownload(*exam_ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("examMemberDownload")
for exam_id in exam_ids:
curServer.execute(
"select * from exam_member where exam_id=%s" % (exam_id, ))
rows = curServer.fetchall()
for row in rows:
curLocal.execute(
"insert into exam_member values %s on conflict(exam_id, stu_id)\
do update set exam_id=excluded.exam_id, stu_id=excluded.stu_id",
(row, ))
curLocal.execute(
"insert into exam_record(exam_id,stu_id) values %s\
on conflict(exam_id,stu_id) do nothing", (row, ))
def examStudentDownload(*exam_ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("examStudentDownload")
for exam_id in exam_ids:
configFTP.examStudentDownloadFiles(exam_id)
curServer.execute("select id, name, class, finger, face \
from student, exam_member \
where id=stu_id \
and exam_id =%s" % (exam_id, ))
rows = curServer.fetchall()
for row in rows:
curLocal.execute(
"insert into exam_student values %s \
on conflict(id) do update set\
name=excluded.name, class=excluded.class,\
finger=excluded.finger, face=excluded.face", (row, ))
def loginUserUpload(*user_ids): #OK
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("loginUserUpload")
if (user_ids == None):
print("empty input")
for user_id in user_ids:
curLocal.execute(
"select id, passwd, rights from user_add where id = %s",
(user_id, ))
row = curLocal.fetchone()
print(row)
curServer.execute(
'''insert into "user" values %s on conflict(id)\
do update set passwd=excluded.passwd ,rights=excluded.rights ''', (row, ))
curLocal.execute(
"update user_add set is_uploaded=true where id=%s" %
(user_id, ))
def registerStudentUpload(*ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("registerStudentUpload")
if (ids == None):
print("empty input")
for id in ids:
configFTP.registerStudentUploadFiles(id)
curLocal.execute(
"select id, name, class, face, finger, is_ic \
from register_student where id=%s " % (id, ))
row = curLocal.fetchone()
curServer.execute(
"insert into student values %s on conflict(id) do update set\
name=excluded.name, class=excluded.class, face=excluded.face, \
finger=excluded.finger, is_ic=excluded.is_ic,time=excluded.time", (row, ))
curLocal.execute(
"update register_student set is_uploaded=true where id=%s"
% (id, ))
def speechSpeechUpload(*spe_id_locals):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("speechSpeechUpload")
for spe_id_local in spe_id_locals:
curLocal.execute(
"select name, location from speech where id=%s",
(spe_id_local, ))
row = curLocal.fetchone()
curServer.execute(
"insert into speech values \
(nextval('seq_spe'),%s,%s,to_char(current_timestamp,'YYYY-MM-DD HH:MI'))",
row)
curServer.execute("select lastval() from speech")
spe_id_server = curServer.fetchone()[0]
curLocal.execute("insert into speech_id_relation values(%s,%s)\
on conflict(id_local, id_server) do nothing" %
(spe_id_local, spe_id_server))
curLocal.execute(
"update speech set is_uploaded=true where id={0}".format(
spe_id_local))
def examRecordUpload(*ids):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("examRecordUpload")
for exam_id, stu_id in ids:
configFTP.examRecordUploadFiles((exam_id, stu_id), )
curLocal.execute(
"select exam_id, stu_id, finger, sim_finger, face, sim_face,\
is_ic, is_appended, is_matched \
from exam_record\
where exam_id=%s and stu_id=%s" % (exam_id, stu_id))
rows = curLocal.fetchall()
for row in rows:
curServer.execute(
"insert into exam_record(exam_id, stu_id,\
finger, sim_finger, face, sim_face, \
is_ic, is_appended, is_matched)\
values(%s,%s,%s,%s,%s,%s,%s,%s,%s)\
on conflict(exam_id, stu_id) do update set\
finger=excluded.finger, sim_finger=excluded.sim_finger,\
face=excluded.face, sim_face=excluded.sim_face,\
is_ic=excluded.is_ic, is_appended=excluded.is_appended,\
is_matched=excluded.is_matched,\
time=excluded.time" % row)
curLocal.execute("update exam_record set is_uploaded=true\
where exam_id=%s and stu_id=%s" % (exam_id, stu_id))
curLocal.execute(
"select stu_id from exam_record where is_appended = true")
ids = curLocal.fetchall()
for id in ids:
print(id)
curLocal.execute(
"select name,class from exam_student where id = %s" %
(id[0], ))
stu_name, stu_class = curLocal.fetchone()
curServer.execute(
"insert into student (id,name,class) values(%s,'%s',%s)\
on conflict(id) do update set name=excluded.name,class=excluded.class"
% (id[0], stu_name, stu_class))
def speechRecordUpload(*spe_id_locals):
with configDB.configLocalDB() as curLocal:
with configDB.configServerDB() as curServer:
print("speechRecordUpload")
print(spe_id_locals)
for spe_id_local in spe_id_locals:
curLocal.execute(
"select id_server from speech_id_relation where id_local=%s"
% (spe_id_local, ))
spe_id_server = curLocal.fetchone()[0]
curLocal.execute(
"select stu_id from speech_record where signin_first=true and signin_second=true\
and spe_id=%s" % (spe_id_local, ))
stu_ids = curLocal.fetchall()
for stu_id in stu_ids:
curServer.execute(
"insert into speech_record values(%s,%s) \
on conflict(spe_id,stu_id) do nothing", (spe_id_server, stu_id[0]))
curLocal.execute(
"update speech_record set is_uploaded=true where spe_id=%s and stu_id=%s"
% (spe_id_local, stu_id[0]))
curLocal.execute(
"update speech set is_uploaded=true where id = %s" %
(spe_id_local, ))
def loginUserInsert(id,passwd,rights):
with configDB.configServerDB() as cur:
print('server.loginUserInsert')
cur.execute('''insert into "user"(id,passwd,rights) values({0},'{1}',{2}) \
on conflict(id) do update set\
passwd = excluded.passwd, rights = excluded.rights'''.format(id,passwd,rights))
def resetServerDB():
print('resetServerDB')
with configDB.configServerDB() as cur:
cur.execute('''truncate "user",student,exam,exam_member,exam_record,speech,speech_record''')
cur.execute('''insert into "user" values(1111111111,'111111',127)''')
def examMemberInsert(exam_id,stu_id):
with configDB.configServerDB() as cur:
print('server.examMemberInsert')
cur.execute("insert into exam_member(exam_id,stu_id) values({0},{1})\
on conflict(exam_id,stu_id) do nothing".format(exam_id,stu_id))
def examExamInsert(id,name,location,teacher):
with configDB.configServerDB() as cur:
print('server.examExamInsert')
cur.execute("insert into exam(id,name,location,teacher) values({0},'{1}','{2}','{3}')\
on conflict(id) do update set\
name = excluded.name, location = excluded.location, teacher = excluded.teacher".format(id,name,location,teacher))