def addNewOrder(buyerid, bookid, number):
ts = int(time.time()) # 秒级时间戳
orderid = buyerid + 'A' + str(ts)
state = "未完成"
with DBContext() as context:
if not context.exec("SELECT price,state from book where bookid=?;",
(bookid, )):
return {'state': State.DBErr}
result = context.get_cursor().fetchone()
if not result:
return {'state': State.NoBookErr}
total = result[0]
bookstate = result[1]
if bookstate != "待售":
return {'state': State.NoSale}
if not context.exec("INSERT INTO orders values(?,?,?,?,?,?);",
(bookid, orderid, Tools.get_current_time(),
number, total, state)):
return {'state': State.DBErr}
if not context.exec(
"SELECT userid from user_book_publish where bookid=?;",
(bookid, )):
return {'state': State.DBErr}
result = context.get_cursor().fetchone()
if not result:
return {'state': State.NoBookErr}
result = result[0]
if not context.exec("INSERT INTO user_order values(?,?,?,?);",
(bookid, orderid, buyerid, result)):
return {'state': State.DBErr}
if not context.exec("update book set state='已售' where bookid=?;",
(bookid, )):
return {'state': State.DBErr}
return {'orderid': orderid, 'state': State.OK, 'price': total}
def collect_book(userid, bookid):
with DBContext() as con:
if not con.exec(_sql_collect_book,
(userid, bookid, Tools.get_current_time())):
return {'state': State.DBErr}
return {'state': State.OK}
pass
def changeBookState(bookid, newstate):
# 更改书籍状态
with DBContext() as con:
if not con.exec(_sql_modify_state, (newstate, bookid)):
return {'state': State.DBErr, "success": False}
return {'state': State.OK, 'success': True}
pass
def getSellerID(bookid):
with DBContext() as con:
if not con.exec(_sql_get_sellerid, (bookid,)):
return {'state': State.DBErr}
res = con.get_cursor().fetchone()
return {'state': State.OK, 'userid': res}
pass
def publish_book(userid, name, price, detail, isbn, number, picture,
author, bookclass):
_bookid = "B{:04d}{:10d}".format(random.randint(0, 9999),
int(time.time()))
if len(name) < 0:
return {'state': State.FormErr}
try:
_price = float(price)
_number = int(number)
except:
return {'state': State.FormErr}
_detail = (detail if len(detail) > 0 else "无详情")
_isbn = (isbn if len(isbn) > 0 else "无")
_state = "待审核"
_author = (author if len(author) > 0 else "无")
_class = (bookclass if bookclass in _book_class else "其他")
_time = Tools.get_current_time()
with DBContext() as con:
if not con.exec(_sql_pub_book,
(_bookid, name, _price, _detail, _isbn, _number,
picture, _state, _author, _class)):
return {'state': State.DBErr}
if not con.exec(_sql_pub_book2, (_bookid, userid, _time)):
return {'state': State.DBErr}
return {'state': State.OK}
pass
def addNewOrder(self, buyerid, bookid, number):
t = time.time()
t = int(round(t * 1000)) # 毫秒级时间戳
orderid = buyerid + str(t)
state = "待付款"
with DBContext() as context:
for i in range(len(bookid)):
context.get_cursor().execute(
"SELECT price from book where bookid=?", (bookid[i], ))
result = context.get_cursor().fetchone()[0]
print("书的单价为", result)
total = int(number[i]) * result
context.get_cursor().execute(
"INSERT INTO orders values(?,?,?,?,?,?)",
(bookid[i], orderid, t, number, total, state))
result = context.get_cursor().fetchone()
print("将订单写入数据库Orders", result)
context.get_cursor().execute(
"SELECT userid from user_book_publish where bookid=?",
(bookid[i], ))
result = context.get_cursor().fetchone()[0]
print("当前书的发布者id是", result)
context.get_cursor().execute(
"INSERT INTO user_order values(?,?,?,?)",
(bookid[i], orderid, buyerid, result))
result = context.get_cursor().fetchone()
print("将订单写入数据库User_Order", result)
return orderid
def add_book_admin_table(bookid, adminid):
#添加管理员与审核书籍的关系
with DBContext() as con:
if not con.exec(_sql_book_admin, (bookid, adminid)):
return {'state': State.DBErr}
return {'state': State.OK}
pass
def collectBook(userid, bookid):
# 通过关联用户和书籍进行收藏
with DBContext() as con:
ts = time.time()
ts = int(ts) # 秒级时间戳
dt = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(ts))
if not con.exec(_sql_insert_collect, (userid, bookid, dt)):
return {'state': State.DBErr, "sucess": False}
return {'state': State.OK, 'sucess': True}
pass
def getBookInfo(bookid):
# 通过书籍id查询书籍信息
with DBContext() as con:
if not con.exec(_sql_getbook_info, (bookid,)):
return {'state': State.DBErr}
tempList = con.get_cursor().fetchone()
if not tempList:
return {'state': State.BookNExit}
return {'state': State.OK, 'bookinfo': Tools.tuple2dict(_key_book_info, tempList)}
pass
def viewOrders(self, userid):
temp = []
with DBContext() as context:
context.get_cursor().execute(
"SELECT orderid from user_order where buyerid=? ", (userid, ))
result = context.get_cursor().fetchall()
for i in range(len(result)):
# context.get_cursor().execute("SELECT * from orders where orderid=? ", (result[i][0],))
temp[i] = result[i][0] # 订单号数组
return temp
def view_user():
# 查看用户信息
with DBContext() as con:
if not con.exec(_sql_user_info):
return {'state': State.DBErr}
userList = con.get_cursor().fetchall()
try:
res = Tools.list_tuple2dict(_key_user_info, userList)
except:
return {'state': State.Error}
return {'state': State.OK, 'userlist': res}
def search_reviewed_book():
# 查询已审核的书
with DBContext() as con:
if not con.exec(_sql_reviewed_book):
return {'state': State.DBErr}
tempList = con.get_cursor().fetchall()
try:
res = Tools.list_tuple2dict(_key_book_info, tempList)
except:
return {'state': State.Error}
return {'state': State.OK, 'booklist': res}
def admin_login(adminid, pwd):
#管理员登录
_sql_admin_login = "******"
with DBContext() as con:
if not con.exec(_sql_admin_login, (adminid, pwd)):
return {'state': State.Error}
res = con.get_cursor().fetchone()
if not res:
return {'state': State.Error}
return {'state': State.OK}
pass
def check_order():
#查看订单
with DBContext() as con:
if not con.exec(_sql_check_order):
return {'state': State.DBErr}
orderlist = con.get_cursor().fetchall()
try:
res = Tools.list_tuple2dict(_key_order_info, orderlist)
except:
return {'state': State.Error}
return {'state': State.OK, 'orderlist': res}
pass
def get_collection(userid):
with DBContext() as con:
if not con.exec(_sql_view_collect, (userid, )):
return {'state': State.DBErr}
res = con.get_cursor().fetchall()
if not res:
return {'state': State.Error}
try:
res = Tools.list_tuple2dict(_key_view_collect, res)
except:
return {'state': State.Error}
return {'state': State.OK, 'booklist': res}
pass
def add_user(phone, pwd, idnumber, name, address):
# 注册新用户
userid = UserDBmanagement._create_userid()
with DBContext() as con:
if not con.exec(_sql_phone, (phone, )):
return {'state': State.DBErr}
if con.get_cursor().fetchone():
return {'state': State.RegErr}
if not con.exec(_sql_reg,
(userid, pwd, phone, idnumber, name, address)):
return {'state': State.DBErr}
return {'state': State.OK}
pass
def getSearchBook(keyword):
# 通过关键字查询书籍列表
# 构造模糊搜索
fuzzy = "%" + keyword + "%"
with DBContext() as con:
if not con.exec(_sql_search, (fuzzy,)):
return {'state': State.DBErr}
tempList = con.get_cursor().fetchall()
try:
res = Tools.list_tuple2dict(_key_book_info, tempList)
except:
return {'state': State.Error}
return {'state': State.OK, 'booklist': res}
def changeOrderState(self, orderid, state):
with DBContext() as context:
context.get_cursor().execute(
"SELECT * FROM orders where orderid=?", (orderid, ))
result = context.get_cursor().fetchone()
context.get_cursor().execute(
"UPDATE orders set state=? where orderid=? ", (state, orderid))
if not result:
boolean = False
else:
boolean = True
print("是否存在订单", boolean)
return boolean
def sold_out_book(bookid):
#下架书籍
with DBContext() as con:
if not con.exec("select state from book where bookid=?;", (bookid,)):
return {'state': State.DBErr}
res = con.get_cursor().fetchone()
if not res:
return {'state': State.DBErr}
if res[0] == '下架':
return {'state': State.Debug}
if not con.exec(_sql_delete_book, ('下架', bookid)):
return {'state': State.DBErr}
return {'state': State.OK}
pass
def check_publish(userid):
# 查看发布
with DBContext() as con:
if not con.exec(_sql_user_order, (userid, )):
return {'state': State.DBErr}
res = con.get_cursor().fetchall()
if not res:
return {'state': State.Error}
try:
res = Tools.list_tuple2dict(_key_user_order, res)
except:
return {'state': State.Error}
return {'state': State.OK, 'bookinfo': res}
pass
def get_user_info(userid):
# 获取用户个人的信息
with DBContext() as con:
if not con.exec(_sql_user_info, (userid, )):
return {'state': State.DBErr}
res = con.get_cursor().fetchone()
if not res:
return {'state': State.Error}
res = Tools.tuple2dict(_key_user_info, res)
if not res:
return {'state': State.Error}
res.pop('password')
res['state'] = State.OK
return res
pass
def check_login(phone, pwd):
# 登录
with DBContext() as con:
if not con.exec(_sql_phone, (phone, )):
return {'state': State.DBErr}
res = con.get_cursor().fetchone()
if not res:
return {'state': State.ActErr}
if not con.exec(_sql_login, (phone, pwd)):
return {'state': State.DBErr}
res = con.get_cursor().fetchone()
if not res:
return {'state': State.PwdErr}
return {'state': State.OK, 'userid': res[0]}
pass
def get_class_books(bookclass):
# 根据书籍的分类来获取书籍的列表
if bookclass not in _book_class:
return {'state': State.FormErr}
with DBContext() as con:
if not con.exec(_sql_get_class, (bookclass,)):
return {'state': State.DBErr}
res = con.get_cursor().fetchall()
if not res:
return {'state': State.ListNone}
try:
res = Tools.list_tuple2dict(_key_book_info, res)
except:
return {'state': State.Error}
return {'state': State.OK, 'booklist': res}
pass
def admin_register():
'''
默认注册6个管理员
往后暂不提供注册功能
'''
_sql_admin_reg = "insert into admin values (?,?);"
_const_admin = [('gydi', 'gydi'), ('jiashuo', 'jiashuo'),
('chang', 'chang'), ('wujie', 'wujie'),
('guangyun', 'guangyun'), ('shijie', 'shijie'),
('test', 'test'), ('other', 'other')]
with DBContext() as con:
con.get_cursor().executemany(_sql_admin_reg, _const_admin)
if con.is_error():
return {'state': State.DBErr}
return {'state': State.OK}
pass
def get_recommand(max_fetch):
# 根据传入的最大获取数来获取数据
# 获取 推荐的 书籍
if not isinstance(max_fetch, int):
return {'state': State.Error}
with DBContext() as con:
if not con.exec(_sql_recommand):
return {'state': State.DBErr}
res = con.get_cursor().fetchmany(max_fetch)
if not res:
return {'state': State.ListNone}
try:
res = Tools.list_tuple2dict(_key_book_info, res)
except:
return {'state': State.Error}
return {'state': State.OK, 'booklist': res}
pass
def viewOrderDetail(orderid, buyerornot):
with DBContext() as context:
if not context.exec(
"SELECT name,time,orders.number,total,orders.state from orders join book using (bookid) where orderid=? ",
(orderid, )):
return {'state': State.DBErr}
result = context.get_cursor().fetchone()
if not result:
return {'state': State.NoOrderErr}
# bookname = result[0]
if buyerornot == "True":
if not context.exec(
"SELECT address,phone,name from user_order join user on userid=sellerid where orderid=? ",
(orderid, )):
return {'state': State.DBErr}
temp = context.get_cursor().fetchone()
if not temp:
return {'state': State.NoOrderErr}
useraddress = temp[0]
username = temp[2]
userphone = temp[1]
else:
if not context.exec(
"SELECT address,phone,name from user_order join user on userid=buyerid where orderid=? ",
(orderid, )):
return {'state': State.DBErr}
temp = context.get_cursor().fetchone()
if not temp:
return {'state': State.NoOrderErr}
useraddress = temp[0]
username = temp[2]
userphone = temp[1]
return {
'state': State.OK,
'bookname': result[0],
'orderid': orderid,
'time': result[1],
'number': result[2],
'total': result[3],
'orderstate': result[4],
'username': username,
'userphone': userphone,
'useraddress': useraddress
}
def view_orders(userid, buyerornot):
_sql_view_template = '''
select bookid,orderid,time,total,orders.state,name,picture,author,class
from orders join user_order using (bookid,orderid) join book using (bookid)
where {}=?;
'''
_sql = _sql_view_template.format(
('buyerid' if buyerornot == 'True' else 'sellerid'))
with DBContext() as con:
if not con.exec(_sql, (userid, )):
return {'state': State.DBErr}
result = con.get_cursor().fetchall()
if not result:
return {'state': State.Error}
try:
result = Tools.list_tuple2dict(_const_key_view_orders, result)
except:
return {'state': State.Error}
return {'state': State.OK, 'orderlist': result}
pass
def changeOrderState(orderid, orderstate):
if orderstate != "完成" and orderstate != '已取消':
return {'state': State.Debug}
with DBContext() as context:
if not context.exec(
"SELECT bookid,orders.state,book.state FROM orders join book using (bookid) where orderid=?;",
(orderid, )):
return {'state': State.DBErr}
result = context.get_cursor().fetchone()
if not result:
return {'state': State.NoOrderErr}
bookid, orders_state, book_state = result
if orders_state != '未完成' and book_state != '已售':
return {'state': State.Error}
if not context.exec("UPDATE orders set state=? where orderid=? ",
(orderstate, orderid)):
return {'state': State.DBErr}
if orderstate == '已取消' and not context.exec(
"UPDATE book set state=? where bookid=? ", ('待售', bookid)):
return {'state': State.DBErr}
return {'state': State.OK}
def revise_info(userid,
phone=None,
pwd=None,
idnumber=None,
name=None,
address=None):
# 修改个人信息
val_list = []
str_list = []
if phone:
str_list.append('phone=?,')
val_list.append(phone)
if pwd:
str_list.append('password=?,')
val_list.append(pwd)
if idnumber:
str_list.append('idnumber=?,')
val_list.append(idnumber)
if name:
str_list.append('name=?,')
val_list.append(name)
if address:
str_list.append('address=?,')
val_list.append(address)
if len(str_list) == 0:
return {'state': State.Error}
val_list.append(userid)
val_tuple = tuple(val_list)
sql_str = _sql_revise_.format(''.join(str_list)[:-1])
with DBContext() as con:
if phone and con.exec(_sql_phone, (phone, )):
if con.get_cursor().fetchone():
return {'state': State.RegErr}
if not con.exec(sql_str, val_tuple):
return {'state': State.DBErr}
return {'state': State.OK}
pass
def viewOrderDetail(self, orderid):
with DBContext() as context:
context.get_cursor().execute(
"SELECT * from orders where orderid=? ", (orderid, ))
result = context.get_cursor().fetchone() # 订单详情
return result