def GetRecommandShow(self, id):
mysql = Mysql()
sql = "select * from recommand where userid='" + id + "'"
try:
User = mysql.getAll(sql)
if len(User) == 0:
print("No recommand found!")
else:
#print("The recommand you found:")
if User:
rs = []
for row in User:
user = Recommand()
user.setbookname(row[1])
user.setauthor(row[2])
user.setpublisher(row[3])
user.setversion(row[4])
user.setrecomreason(row[5])
user.setstatue(row[6])
rs.append(user)
rs = tuple(rs)
return rs
except Exception as e:
print("query error!")
mysql.dispose()
def GetUserRecord(self, Dict):
mysql = Mysql()
sql = "select * from user where "
keys = tuple(Dict.keys())
vals = tuple(Dict.values())
Len = len(Dict)
for i in range(Len):
if (i != Len - 1):
sql = sql + keys[i] + "='" + str(vals[i]) + "' and "
else:
sql = sql + keys[i] + "='" + str(vals[i]) + "'"
try:
User = mysql.getAll(sql)
if len(User) == 0:
print("No user found!")
else:
print("The user you found:")
if User:
for row in User:
print("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" %
(row[0], row[1], row[2], row[3], row[4], row[5],
row[6], row[7], row[8], row[9]))
return User
except Exception as e:
print(e)
mysql.dispose()
def GetMaxIllegalid(self):
mysql = Mysql()
sql = "select max(illegalid) from illegal"
result = mysql.getAll(sql)
mysql.dispose()
result = result[0]
result = result[0]
return result
def GetUserTypeRecord(self):
mysql = Mysql()
sql = "select * from usertype"
rs = mysql.getAll(sql)
for row in rs:
print("身份：%s\t同时可借最多图书数目：%s\t一次性最多可推荐图书数目：%s\t免费借阅期限：%s\t" %
(row[0], row[1], row[2], row[3]))
return rs
def GetMaxBorrowid(self):
mysql = Mysql()
sql = "select max(borrowid) from borrow"
result = mysql.getAll(sql)
mysql.dispose()
result = result[0]
result = result[0]
return result
def GetBookByName(self, keyword):
mysql = Mysql()
book_list = mysql.getAll("select * from book where bookname like '%" + keyword + "%'")
mysql.dispose()
if book_list == False:
print('no record')
return 0
show = MiddleLayer()
tums = show.ShowBook(book_list)
return tums
def GetAllAdminRecord(self):
'''
:param Dict:
输出admin表所有字段
'''
mysql = Mysql()
sqlAll = "select * from admin"
result = mysql.getAll(sqlAll)
mysql.dispose()
show = MiddleLayer()
tums = show.ShowAdmin(result)
return tums
def GetUserIllegal(self, obj):
# 拼接
Seq = []
Val = []
if obj.get_illegalid() != '':
Seq.append('illegalid')
Val.append(obj.get_illegalid())
if obj.get_userid() != '':
Seq.append('userid')
Val.append(obj.get_userid())
if obj.get_bookid() != '':
Seq.append('bookid')
Val.append(obj.get_bookid())
if obj.get_amount() != '':
Seq.append('amount')
Val.append(obj.get_amount())
if obj.get_isprocessed() != '':
Seq.append('isprocessed')
Val.append(obj.get_isprocessed())
if obj.get_illegaldate() != '':
Seq.append('illegaldate')
Val.append(obj.get_illegaldate())
if obj.get_illegaltype() != '':
Seq.append('illegaltype')
Val.append(obj.get_illegaltype())
Ile = dict(zip(Seq, Val))
mysql = Mysql()
sql = "select * from illegal where "
keys = tuple(Ile.keys())
vals = tuple(Ile.values())
Len = len(Ile)
for i in range(Len):
if (i != Len - 1):
sql = sql + keys[i] + "='" + str(vals[i]) + "' and "
else:
sql = sql + keys[i] + "='" + str(vals[i]) + "'"
Ilegal = mysql.getAll(sql)
if Ilegal == False:
print("No Illegal record!")
else:
print("Illegal records:")
if Ilegal:
for row in Ilegal:
print("%s\t%s\t%s\t%s\t%s\t%s\t%s" %
(row[0], row[1], row[2], row[3], row[4], row[5],
row[6]))
mysql.dispose()
show = MiddleLayer()
tums = show.ShowIllegal(Ilegal)
return tums
def GetAllIlegal(self, num):
mysql = Mysql()
if (str(num) == '0'):
sqlAll = "select * from Illegal"
else:
sqlAll = "select * from Illegal limit " + str(num)
result = mysql.getAll(sqlAll)
# if result :
# for row in result :
# print("%s\t%s\t%s\t%s\t%s\t%s\t%s" %(row[0],row[1],row[2],row[3],row[4],row[5],row[6]))
mysql.dispose()
show = MiddleLayer()
tums = show.ShowIllegal(result)
return tums
def GetBorrowRecordByField(self, obj):
# 拼接
Seq = []
Val = []
if obj.get_borrowid() != '':
Seq.append('borrowid')
Val.append(obj.get_borrowid())
if obj.get_userid() != '':
Seq.append('userid')
Val.append(obj.get_userid())
if obj.get_bookid() != '':
Seq.append('bookid')
Val.append(obj.get_bookid())
if obj.get_borrowdate() != '':
Seq.append('borrowdate')
Val.append(obj.get_borrowdate())
if obj.get_presretdate() != '':
Seq.append('presretdate')
Val.append(obj.get_presretdate())
if obj.get_actretdate() != '':
Seq.append('actretdate')
Val.append(obj.get_actretdate())
Dict = dict(zip(Seq, Val))
mysql = Mysql()
sql = "select * from borrow where "
keys = tuple(Dict.keys())
vals = tuple(Dict.values())
Len = len(Dict)
for i in range(Len):
if (i != Len - 1):
sql = sql + keys[i] + "='" + str(vals[i]) + "' and "
else:
sql = sql + keys[i] + "='" + str(vals[i]) + "'"
Bor = mysql.getAll(sql)
if Bor == False:
print('no record')
# Book = mysql.getAll(sql)
# if Book == False:
# print('no record')
# if len(Book) == 0:
# print("No borrow record found!")
# else:
# print("The borrow record you found:")
# if Book:
# for row in Book:
# print("%s\t%s\t%s\t%s\t%s\t%s" % (row[0], row[1], row[2], row[3], row[4], row[5]))
mysql.dispose()
show = MiddleLayer()
tums = show.ShowBorrow(Bor)
return tums
def GetBookByDis(self, desci):
mysql = Mysql()
Dis_List = mysql.getAll("select * from book where discipline = '" + desci + "'")
# print("Classification of " + desci + ":\n")
# print("bookid\tbookname\tauthor\tpages\tcollecttime\tversion\tmajor\tdiscipline\tisbn\tbooklanguage\tpublisher\tstatus\tabstract\tstack\tshelf\tfloor\tbookvalue")
# if Dis_List:
# for row in Dis_List:
# print("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" %(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16]))
mysql.dispose()
if Dis_List == False:
print('no record')
return 0
show = MiddleLayer()
tums = show.ShowBook(Dis_List)
return tums
def RetureIllegalRecord(self):
mysql = Mysql()
# 获取系统时间
systime = datetime.datetime.now().strftime('%Y-%m-%d')
# 返回超过系统时间还未还的图书借阅记录
# sql = "select * from borrow where actretdate < '" + systime + "' and presretdate = '0000-00-00'"
sql = "select * from borrow where actretdate < '" + "1996-01-01" + "' and presretdate = '0000-00-00'"
result = mysql.getAll(sql)
mysql.dispose()
if (result == False):
print('no record')
return 0
show = MiddleLayer()
tums = show.ShowBorrow(result)
return tums
def GetAllBookRecord(self, num=0):
mysql = Mysql()
if (str(num) == '0'):
sqlAll = "select * from book"
else:
sqlAll = "select * from book limit " + str(num)
result = mysql.getAll(sqlAll)
# print("bookid\tbookname\tauthor\tpages\tcollecttime\tversion\tmajor\tdiscipline\tisbn\tbooklanguage\tpublisher\tstatus\tabstract\tstack\tshelf\tfloor\tbookvalue")
# if result :
# for row in result :
# print("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" %(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16]))
mysql.dispose()
if result == False:
print('no record')
return 0
show = MiddleLayer()
tums = show.ShowBook(result)
return tums
def GetBorrowRecord(self, num=0):
mysql = Mysql()
if (str(num) == '0'):
sqlAll = "select * from borrow"
else:
sqlAll = "select * from borrow limit " + str(num)
result = mysql.getAll(sqlAll)
print("borrowid\tuserid\tbookid\tborrowdate\tpresretdate\tactretdate")
if result:
for row in result:
print("%s\t%s\t%s\t%s\t%s\t%s" % \
(row[0], row[1], row[2], row[3], row[4], row[5]))
mysql.dispose()
if result == False:
print('no record')
return 0
show = MiddleLayer()
tums = show.ShowBorrow(result)
return tums
def GetRecommandRecord(self, Dict):
mysql = Mysql()
sql = "select * from recommand where "
keys = tuple(Dict.keys())
vals = tuple(Dict.values())
Len = len(Dict)
for i in range(Len):
if (i != Len - 1):
sql = sql + keys[i] + "='" + str(vals[i]) + "' and "
else:
sql = sql + keys[i] + "='" + str(vals[i]) + "'"
try:
User = mysql.getAll(sql)
if len(User) == 0:
print("No recommand found!")
else:
print("The recommand you found:")
if User:
for row in User:
print("%s\t%s\t%s\t%s\t%s\t%s\t%s" % (
row[0], row[1], row[2], row[3], row[4], row[5], row[6]))
except Exception as e:
print(e)
mysql.dispose()
# 插入推荐
#test = RecommandAPI()
#user = Recommand()
#user.setbookname('哈哈p')
#user.setauthor('ni')
#user.setpublisher('ni')
#user.setversion('98')
#user.setrecomreason('good')
#test.InsertRecommandRecord(user, '11002')
# 查询推荐
#test = RecommandAPI()
#rs = test.GetRecommandShow('11000')
#for i in rs:
# print(i.getbookname())
def GetBookByField(self, obj):
# 拼接成Dict函数
# 例：{'bookid': '100014', 'author': '韩强军'}
Seq = []
Val = []
if obj.get_bookid() != '':
Seq.append('bookid')
Val.append(obj.get_bookid())
if obj.get_bookname() != '':
Seq.append('bookname')
Val.append(obj.get_bookname())
if obj.get_author() != '':
Seq.append('author')
Val.append(obj.get_author())
if obj.get_pages() != '':
Seq.append('pages')
Val.append(obj.get_pages())
if obj.get_collecttime() != '':
Seq.append('collecttime')
Val.append(obj.get_collecttime())
if obj.get_version() != '':
Seq.append('version')
Val.append(obj.get_version())
if obj.get_major() != '':
Seq.append('major')
Val.append(obj.get_major())
if obj.get_discipline() != '':
Seq.append('discipline')
Val.append(obj.get_discipline())
if obj.get_isbn() != '':
Seq.append('isbn')
Val.append(obj.get_isbn())
if obj.get_booklanguage() != '':
Seq.append('booklanguage')
Val.append(obj.get_booklanguage())
if obj.get_publisher() != '':
Seq.append('publisher')
Val.append(obj.get_publisher())
if obj.get_status() != '':
Seq.append('status')
Val.append(obj.get_status())
if obj.get_abstract() != '':
Seq.append('abstract')
Val.append(obj.get_abstract())
if obj.get_stack() != '':
Seq.append('stack')
Val.append(obj.get_stack())
if obj.get_shelf() != '':
Seq.append('shelf')
Val.append(obj.get_shelf())
if obj.get_floor() != '':
Seq.append('floor')
Val.append(obj.get_floor())
if obj.get_bookvalue() != '':
Seq.append('bookvalue')
Val.append(obj.get_bookvalue())
Dict = dict(zip(Seq, Val))
mysql = Mysql()
sql = "select * from book where "
keys = tuple(Dict.keys())
vals = tuple(Dict.values())
Len = len(Dict)
for i in range(Len):
if (i != Len - 1):
sql = sql + keys[i] + "='" + str(vals[i]) + "' and "
else:
sql = sql + keys[i] + "='" + str(vals[i]) + "'"
Book = mysql.getAll(sql)
if Book == False:
print('no record')
return 0
# if len(Book) == 0:
# print("No books found!")
# else:
# print("The book you found:")
# if Book:
# for row in Book:
#
# print("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s" %\
# (row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],\
# row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16]))
mysql.dispose()
show = MiddleLayer()
tums = show.ShowBook(Book)
return tums
