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)