def selectdormitory(self):
# print('insert:',self.dormitory_id, self.ceng_num, self.bed_pid, self.price, self.floor_id)
sql = "INSERT into dormitory (dormitory_id, ceng_num, bed_pid, price, floor_id)VALUES (%s,%s,%s,'%s','%s')" % (
self.dormitory_id, self.ceng_num, self.bed_pid, self.price,
self.floor_id)
result = func(sql)
return result
def updatgotomsg(self):
sql = "UPDATE gotomessage SET goto_name='%s' ,goto_phone='%s', goto_dormitory='%s', goto_dormitory_id='%s' ,goto_leavetime='%s',goto_backtime='%s',goto_reason='%s' where goto_id='%s'" % (
self.goto_name, self.goto_phone, self.goto_dormitory,
self.goto_dormitory_id, self.goto_leavetime, self.goto_backtime,
self.goto_reason, self.goto_id)
result = func(sql)
return result
def insertgotomsg(self):
# print('i:',self.goto_id, self.goto_name, self.goto_phone, self.goto_dormitory, self.goto_dormitory_id, self.goto_leavetime, self.goto_backtime,self.goto_reason )
sql = "INSERT into gotomessage( goto_id, goto_name, goto_phone, goto_dormitory_id, goto_dormitory, goto_leavetime, goto_backtime, goto_reason) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s')" % (
self.goto_id, self.goto_name, self.goto_phone, self.goto_dormitory,
self.goto_dormitory_id, self.goto_leavetime, self.goto_backtime,
self.goto_reason)
result = func(sql)
return result
def selectdormitory_edit(self):
sql = "SELECT count(*) FROM student s, dormitory d where d.floor_id = s.stu_dormitory_id and d.dormitory_id = s.stu_dormitory and dormitory_id='%s' and floor_id='%s'" % (
self.dormitory_id, self.floor_id)
re = func(sql)
result = []
for i in re:
result.append(i[0])
return result[0]
def updatedormitory(self):
# print('update:', self.dormitory_id, self.ceng_num,
# self.bed_pid, self.price, self.floor_id)
sql = "UPDATE dormitory SET ceng_num=%s, bed_pid=%s, price='%s' where dormitory_id=%s and floor_id='%s'" % (
self.ceng_num, self.bed_pid, self.price, self.dormitory_id,
self.floor_id)
result = func(sql)
return result
def selecthouse():
sql = "SELECT * From house"
re = func(sql)
result = []
for i in re:
relist = {}
relist['house_id'] = str(i[0])
result.append(relist)
return result
def selectdormitory():
sql = "SELECT distinct dormitory_id From dormitory order by dormitory_id asc"
re = func(sql)
result = []
for i in re:
relist = {}
relist['dormitory_id'] = str(i[0])
result.append(relist)
return result
def selectdormitorylist(self):
sql = "SELECT * From dormitory where dormitory_id= '%s' and floor_id='%s'" % (
self.dormitory_id, self.floor_id)
re = func(sql)
result = []
for i in re:
result.append(i[3])
# print('床位',result)
return result[0]
def selectstudent(self):
# print(self.stu_dormitory, self.stu_dormitory_id)
sql = "select count(*) from student where stu_dormitory='%s' and stu_dormitory_id='%s'" % (
self.stu_dormitory, self.stu_dormitory_id)
re = func(sql)
result = []
for i in re:
result.append(i[0])
# print('人数',result)
return result[0]
def delete_student_msg(self):
sql = "select * from student where stu_id = '%s'" % (self.num)
re = func(sql)
result = []
for i in re:
relist = {}
relist['stu_dormitory'] = str(i[7])
relist['stu_dormitory_id'] = str(i[8])
selectdormitorymsgTB(str(i[7]), str(i[8])).updatecount()
result.append(relist)
return result
def select_dormitory(self):
sql = "select * from dormitory where nid = '%s'" % (self.num)
re = func(sql)
result = []
for i in re:
relist = {}
relist['dormitory_id'] = str(i[1])
relist['ceng_num'] = str(i[2])
relist['bed_pid'] = str(i[3])
relist['price'] = str(i[4])
relist['floor_id'] = str(i[5])
result.append(relist)
return result
def selectUserPwd(self):
sql = "select * from usertable where username = '******' and password = '******' and radio='%s'" % (
self.user, self.pwd, self.radio)
re = func(sql)
result = []
for i in re:
relist = {}
relist['username'] = str(i[0])
relist['password'] = str(i[1])
relist['radio'] = str(i[2])
relist['token'] = UserTB.get_token(i[0])
result.append(relist)
return result
def select_gotomsg(self):
sql = "select * from gotomessage where goto_id = '%s'" % (self.num)
re = func(sql)
result = []
for i in re:
relist = {}
relist['goto_id'] = str(i[0])
relist['goto_name'] = str(i[1])
relist['goto_phone'] = str(i[2])
relist['goto_dormitory'] = str(i[3])
relist['goto_dormitory_id'] = str(i[4])
relist['goto_leavetime'] = str(i[5])
relist['goto_backtime'] = str(i[6])
relist['goto_reason'] = str(i[7])
result.append(relist)
return result
def selectdormitory_parameter(self):
sql = "SELECT * From dormitory where floor_id ='%s'" % (self.num)
re = func(sql)
result = []
for i in re:
relist = {}
relist['nid'] = str(i[0])
relist['dormitory_id'] = str(i[1])
relist['ceng_num'] = str(i[2])
relist['bed_pid'] = str(i[3])
relist['price'] = str(i[4])
relist['floor_id'] = str(i[5])
relist['isfull'] = str(i[6])
relist['people'] = str(i[7])
result.append(relist)
return result
def selectdormitory_btn(self):
args = '%' + self.num + '%'
sql = "SELECT * FROM dormitory d where concat(d.price,d.ceng_num,d.floor_id,d.dormitory_id,d.bed_pid) LIKE '%s'" % args
re = func(sql)
result = []
for i in re:
relist = {}
relist['nid'] = str(i[0])
relist['dormitory_id'] = str(i[1])
relist['ceng_num'] = str(i[2])
relist['bed_pid'] = str(i[3])
relist['price'] = str(i[4])
relist['floor_id'] = str(i[5])
relist['isfull'] = str(i[6])
relist['people'] = str(i[7])
result.append(relist)
return result
def select_leavemsg():
sql = "SELECT * From gotomessage"
re = func(sql)
result = []
for i in re:
relist = {}
relist['goto_id'] = str(i[0])
relist['goto_name'] = str(i[1])
relist['goto_phone'] = str(i[2])
relist['goto_dormitory'] = str(i[3])
relist['goto_dormitory_id'] = str(i[4])
relist['goto_leavetime'] = str(i[5])
relist['goto_backtime'] = str(i[6])
relist['goto_reason'] = str(i[7])
relist['goto_islate'] = str(i[8])
result.append(relist)
return result
def select_studentmsg(self): # 学生功能区编辑按钮获取信息用
sql = "select * from student where stu_id = '%s'" % (self.num)
re = func(sql)
result = []
for i in re:
relist = {}
relist['stu_id'] = str(i[0])
relist['stu_name'] = str(i[1])
relist['stu_gender'] = str(i[2])
relist['stu_age'] = str(i[3])
relist['stu_depart'] = str(i[4])
relist['stu_grade'] = str(i[5])
relist['stu_phone'] = str(i[6])
relist['stu_dormitory'] = str(i[7])
relist['stu_dormitory_id'] = str(i[8])
result.append(relist)
return result
def selectstudent():
sql = "SELECT * From student"
re = func(sql)
result = []
for i in re:
relist = {}
relist['stu_id'] = str(i[0])
relist['stu_name'] = str(i[1])
relist['stu_gender'] = str(i[2])
relist['stu_age'] = str(i[3])
relist['stu_depart'] = str(i[4])
relist['stu_grade'] = str(i[5])
relist['stu_phone'] = str(i[6])
relist['stu_dormitory'] = str(i[7])
relist['stu_dormitory_id'] = str(i[8])
result.append(relist)
return result
def selectmenu():
sql = "SELECT ps_id,ps_pname,ps_name,ps_path From menus m,childrenmenus s where m.ps_id = s.ps_pid"
re = func(sql)
result = []
for i in re:
relist = {}
children = {}
rechild = []
relist['id'] = str(i[0])
relist['authName'] = str(i[1])
children['authName'] = str(i[2])
children['path'] = str(i[3])
rechild.append(children)
#嵌套
relist['children'] = rechild
result.append(relist)
return result
def selectstudent_btn(self):
args = '%'+self.num+'%'
sql = "SELECT * FROM student s where concat(s.stu_id,s.stu_name,s.stu_gender,s.stu_age,s.stu_depart,s.stu_grade,s.stu_phone,s.stu_dormitory,s.stu_dormitory_id) LIKE '%s'" % args
re = func(sql)
result = []
for i in re:
relist = {}
relist['stu_id'] = str(i[0])
relist['stu_name'] = str(i[1])
relist['stu_gender'] = str(i[2])
relist['stu_age'] = str(i[3])
relist['stu_depart'] = str(i[4])
relist['stu_grade'] = str(i[5])
relist['stu_phone'] = str(i[6])
relist['stu_dormitory'] = str(i[7])
relist['stu_dormitory_id'] = str(i[8])
result.append(relist)
return result
def selectgotomessage_btn(self):
args = '%' + self.num + '%'
sql = "SELECT * FROM gotomessage s where concat(s.goto_id,s.goto_name,s.goto_phone,s.goto_dormitory,s.goto_dormitory_id,s.goto_leavetime,s.goto_backtime,s.goto_reason,s.goto_islate) LIKE '%s'" % args
re = func(sql)
result = []
for i in re:
relist = {}
relist['goto_id'] = str(i[0])
relist['goto_name'] = str(i[1])
relist['goto_phone'] = str(i[2])
relist['goto_dormitory'] = str(i[3])
relist['goto_dormitory_id'] = str(i[4])
relist['goto_leavetime'] = str(i[5])
relist['goto_backtime'] = str(i[6])
relist['goto_reason'] = str(i[7])
relist['goto_islate'] = str(i[8])
result.append(relist)
# print(result)
return result
def selectcount():
sql = "select stu_dormitory_id,stu_dormitory,count(*) from student group by stu_dormitory,stu_dormitory_id"
re = func(sql)
if re == (): #student为空的数据,更新数据
print('student为空了')
selectdormitorymsgTB.Student_ifnull()
selectdormitorymsgTB.update_isfull()
selectdormitorymsgTB.update_nofull()
else:
result = []
for i in re:
relist = {}
relist['stu_dormitory_id'] = str(i[0])
relist['stu_dormitory'] = str(i[1])
relist['count'] = str(i[2])
Reupdate(str(i[2]), str(i[0]), str(i[1])).select_update_count()
selectdormitorymsgTB.update_isfull()
selectdormitorymsgTB.update_nofull()
result.append(relist)
return result
def select_student_dormitory(self):
sql = "SELECT * FROM student s, dormitory d where d.floor_id = s.stu_dormitory_id and d.dormitory_id = s.stu_dormitory and nid in (select nid from dormitory where nid = '%s')" % (
self.num)
re = func(sql)
result = []
for i in re:
relist = {}
relist['stu_id'] = str(i[0])
relist['stu_name'] = str(i[1])
relist['stu_gender'] = str(i[2])
relist['stu_age'] = str(i[3])
relist['stu_depart'] = str(i[4])
relist['stu_grader'] = str(i[5])
relist['stu_phone'] = str(i[6])
relist['stu_dormitory'] = str(i[7])
relist['stu_dormitory_id'] = str(i[8])
relist['nid'] = str(i[9])
relist['dormitory_id'] = str(i[10])
relist['ceng_num'] = str(i[11])
relist['bed_pid'] = str(i[12])
relist['price'] = str(i[13])
relist['floor_id'] = str(i[14])
result.append(relist)
return result
def selectdormitory(self):
sql = "SELECT * From dormitory where dormitory_id= '%s' and floor_id='%s'" % (
self.dormitory_id, self.floor_id)
result = func(sql)
return result
def selecthouse_parameter(self):
sql = "SELECT * FROM house h, dormitory d where d.floor_id = h.house_id and house_id='%s' and floor_id='%s'" % (
self.num, self.num)
result = func(sql)
return result
def delete_student(self):
sql = "delete from student where stu_id = '%s'" % (self.num)
result = func(sql)
return result
def updatestate(self):
sql = "UPDATE dormitory SET isfull='%s' where floor_id='%s' and dormitory_id='%s'" % (
self.num, self.floor_id, self.dormitory_id)
result = func(sql)
return result
def updatepeople(self):
sql = "UPDATE dormitory SET people=%s where dormitory_id=%s and floor_id='%s'" % (
self.num, self.dormitory_id, self.floor_id)
result = func(sql)
return result
def inserthouse(self):
sql = "INSERT house (house_id) VALUES ('%s')" % (self.num)
result = func(sql)
return result
def check_dormitory(self): # 宿舍功能区删除按钮删除后查询用的
sql = "select * from dormitory where nid = '%s'" % (self.num)
result = func(sql)
return result
