def get_invigilation_pool(mydb, exam_table, main_invigilation=True, deputy_invigilation=False):
print('4')
if (exam_table == 'CET4' or exam_table == 'cet4'):
exam_table = 'cet4'
elif (exam_table == 'CET6' or exam_table == 'cet6'):
exam_table = 'cet6'
rooms=[]
if (main_invigilation and deputy_invigilation):
print('只能选择一个监考位置')
if (main_invigilation):
#主监考为空时进行插入
sql = 'SELECT DISTINCT exam_id FROM '+exam_table+' WHERE main_invigilation is NULL'
#print(sql)
val = database_comment.SearchTable(mydb, sql_comment=sql)
for i in val:
if(isinstance(i,tuple)):
rooms.append(i[0])
return rooms
if (deputy_invigilation):
sql = 'SELECT DISTINCT exam_id FROM '+exam_table+' WHERE deputy_invigilation is NULL'
val = database_comment.SearchTable(mydb, sql_comment=sql)
for i in val:
if (isinstance(i, tuple)):
rooms.append(i[0])
return rooms
else:
print('必须选择一个监考位置')
def search_teacher(mydb,job_id=None):
if(job_id==None):
print("工号为空")
return
sql="SELECT DISTINCT * FROM teacher WHERE job_id=%s"
job_id=(str(job_id),)
#print(job_id)
return database_comment.SearchTable(mydb,sql,job_id)
def search_invigilation(mydb,exam_table,exam_id,main_invigilation=True):
print('6')
if (exam_table == 'CET4' or exam_table == 'cet4'):
exam_table = 'cet4'
elif (exam_table == 'CET6' or exam_table == 'cet6'):
exam_table = 'cet6'
if(main_invigilation):
sql="SELECT main_invigilation FROM "+ exam_table+" WHERE exam_id= %s"
exam_id=(str(exam_id),)
#print(sql,exam_id)
person = database_comment.SearchTable(mydb,sql,exam_id)
return person
else:
sql = "SELECT deputy_invigilation FROM " + exam_table + " WHERE exam_id= %s"
exam_id = (str(exam_id),)
person = database_comment.SearchTable(mydb,sql, exam_id)
return person
def search_pre_invigilation_teacher(mydb,job_id=None):
if (job_id == None):
print("工号为空")
return
sql = "SELECT DISTINCT * FROM pre_invigilation WHERE job_id=%s "
print("1")
job_id = (str(job_id))
print("2")
# print(job_id)
return database_comment.SearchTable(mydb, sql, job_id)
def init_invigialtion(mydb,exam_table='cet4'):
get_sql='SELECT level,exam_id,peoples,main_invigilation,' \
'deputy_invigilation,exam_location FROM '+ exam_table
#print(get_sql)
values = database_comment.SearchTable(mydb,get_sql)
if(len(values)==0):
print(exam_table+'表中没有信息')
else:
set_sql='INSERT INTO invigilation values (%s,%s,%s,%s,%s,%s)'
database_comment.AddTable(mydb,sql_comment=set_sql,sql_val=values)
def search_all_exam(mydb, exam_table, exam_id):
if (exam_table == 'CET4' or exam_table == 'cet4'):
exam_table = 'cet4'
elif (exam_table == 'CET6' or exam_table == 'cet6'):
exam_table = 'cet6'
exam_id = (str(exam_id),)
sql = 'SELECT DISTINCT * FROM ' + exam_table + ' WHERE exam_id=%s'
val = database_comment.SearchTable(mydb, sql_comment=sql, sql_value=exam_id)
if (len(val) == 0):
print('没有这个考场信息')
return val
def pre_assignation(mydb,exam_table,teacher_id,teacher_sex):
if (exam_table == 'CET4' or exam_table == 'cet4'):
exam_table = 'cet4'
elif (exam_table == 'CET6' or exam_table == 'cet6'):
exam_table = 'cet6'
sql='SELECT exam_id FROM '+exam_table+' WHERE main_invigilation=%s OR deputy_invigilation=%s'
sql_val=(str(teacher_id),str(teacher_id))
results=database_comment.SearchTable(mydb,sql,sql_val)
if(len(results)==0):
exam,main_deputy = assignation(mydb=mydb, exam_table=exam_table, teacher_id=teacher_id, teacher_sex=teacher_sex)
if(exam!=None):
exam_select=exam_information.search_all_exam(mydb,exam_table,exam)
location=exam_select[0][10]
tes = teacher_data.search_teacher(mydb,teacher_id)
exam_information.add_invigilation_table(mydb,exam_select,teacher_id,tes[0][1],main_deputy)
return exam,location,main_deputy
return None,False,None
def Export_Excel(mydb,table,path):
print('9')
sql="SELECT * FROM "+table
results=database_comment.SearchTable(mydb,sql,excel=True)
#print(results)
#创建Excel
excel=xlwt.Workbook()
sheet = excel.add_sheet("sheet1")
#添加数据到Excel
for field in range(len(results[0])):
sheet.write(0,field,results[0][field][0])
#print(results[0][field][0])
for row in range(1,len(results[1])+1):
for col in range(0, len(results[0])):
#print(results[1][row - 1][col])
sheet.write(row, col, '%s' % results[1][row - 1][col])
excel.save(path)
def select_invigilation_table(mydb,exam_level,exam_id):
print('7')
sql="SELECT * FROM invigilation WHERE level=%s AND exam_id =%s"
val=(str(exam_level),str(exam_id))
temp=database_comment.SearchTable(mydb,sql_comment=sql,sql_value=val)
print(temp)
def isexist_teacher(mydb, teacher_id):
sql = "SELECT 1 FROM teacher WHERE job_id = %s limit 1"
return database_comment.SearchTable(mydb, sql, teacher_id)
def select_pre_teacher_flag(mydb, exam_level, teacher_id):
sql = "SELECT flag FROM pre_invigilation WHERE job_id = %s AND exam_level = %s"
values = (teacher_id, exam_level)
return database_comment.SearchTable(mydb, sql, values)
def search_teacher_all(mydb):
sql="SELECT DISTINCT * FROM teacher"
return database_comment.SearchTable(mydb,sql)
def select_invigilator_id_and_name_by_name(mydb, values=None):
sql = "SELECT job_id, teacher_name FROM pre_invigilation WHERE exam_level = %s AND teacher_name = %s AND flag = 0"
return database_comment.SearchTable(mydb, sql, values)
def isexist_invigilator_id(mydb, teacher_id, exam_level):
msg = ((exam_level, str(teacher_id), str(teacher_id)))
sql = "SELECT 1 FROM invigilation WHERE exam_level = %s AND (main_invigilation = %s OR deputy_invigilation = %s)"
return database_comment.SearchTable(mydb, sql, msg)
