def sql_process_information():
result_dict = {}
sql1 = "select id,user_account,readers,cast(time as char) time, feedbacks from feedback fd INNER join user on fd.user_id = user.user_id and fd.state = '1'"
result_dict["untreated"] = mysql_module(sql1)[1]
sql2 = "select id,user_account,readers,cast(time as char) time, feedbacks from feedback fd INNER join user on fd.user_id = user.user_id and fd.state = '0'"
result_dict["processed"] = mysql_module(sql2)[1]
return result_dict
def cancel_collect_book(user_name, book_id):
sql_user_id = "select user_id from user where user_account = '{}'".format(
user_name)
user_id = mysql_module(sql_user_id)[1][0]['user_id']
sql = "DELETE from my_bookshelf where user_id = {} and book_id = '{}'".format(
user_id, book_id)
mysql_module(sql)
return [True, "删除成功"]
def sql_voluntary_activities(contestant, user_name, phone, email, days, times):
sql_user_id = "select user_id from user where user_account = '{}'".format(
user_name)
user_id = mysql_module(sql_user_id)[1][0]['user_id']
sql_select = "select id from voluntary_activities where user_id = {}".format(
user_id)
if mysql_module(sql_select)[1]:
return [False, "您已报名过该活动"]
sql = "insert into voluntary_activities(contestant,phone,email,days,times,user_id) values('{}','{}','{}','{}','{}',{})".format(
contestant, phone, email, days, times, user_id)
result = mysql_module(sql)
return result
def sql_thematic_activities(contestant, phone, works, user_name):
sql_user_id = "select user_id from user where user_account = '{}'".format(
user_name)
user_id = mysql_module(sql_user_id)[1][0]['user_id']
sql_select = "select id from thematic_activities where user_id = {}".format(
user_id)
if mysql_module(sql_select)[1]:
return [False, "您已报名过该活动"]
sql = "insert into thematic_activities(contestant,phone,works,user_id) values('{}','{}','{}',{})".format(
contestant, phone, works, user_id)
result = mysql_module(sql)
return result
def sql_collect_book(user_name, book_id):
sql_user_id = "select user_id from user where user_account = '{}'".format(
user_name)
user_id = mysql_module(sql_user_id)[1][0]['user_id']
sql_select = "select id from my_bookshelf where user_id = {} and book_id = {}".format(
user_id, book_id)
if mysql_module(sql_select)[1]:
return [False, "该书已在您的书架中"]
sql = "insert into my_bookshelf(user_id,book_id) values('{}','{}')".format(
user_id, book_id)
mysql_module(sql)
return [True, "收藏成功"]
def sql_add_book_category(category1, category2):
sql = "select id from book_category where category1 = '{}' and category2 = '{}'".format(
category1, category2)
result = mysql_module(sql)
if not result[0]:
return [False, "信息查询失败"]
if result[1]:
return [False, "该分类已存在"]
sql = "insert into book_category(category1,category2) values ('{}','{}')".format(
category1, category2)
result = mysql_module(sql)
if not result[0]:
return [False, "分类保存失败"]
return [True, "分类保存成功"]
def user_login(username, password, code):
if code == '0':
sql = 'select user_id from user where user_account = "{}" and user_password = "******"'.format(
username, password)
result = mysql_module(sql)
if not result[1]:
return [False, "账号或密码错误"]
return [True, result[1][0]["user_id"]]
elif code == '1':
sql = 'select work_id from admin where work_id = "{}" and work_password = "******"'.format(
username, password)
result = mysql_module(sql)
if not result[1]:
return [False, "账号或密码错误"]
return [True, result[1][0]["work_id"]]
def sql_borrowing_books(user_id):
sql = "select book.book_id,book_name,book.book_publisher,cast(borrow_time as char) borrow_time,cast(return_time as char) return_time,book_room from (select book_id,book_name,book_publisher,book_room from book_info where book_id in (select book_id from borrow_info where user_id = {} and state = 1)) book LEFT JOIN (select borrow_time,return_time,book_id from borrow_info where user_id = {} and state = 1) borrow on book.book_id = borrow.book_id ".format(
user_id, user_id)
result = mysql_module(sql)
if not result[0]:
return [False, "查询出错"]
return result
def sql_my_bookshelf(user_id):
sql = 'SELECT book_id, book_name,book_publisher,book_auther, book_room FROM book_info WHERE book_id IN ( SELECT book_id FROM my_bookshelf WHERE user_id = {} )'.format(
user_id)
result = mysql_module(sql)
if not result[0]:
return [False, "查询出错"]
return [True, result[1]]
def sql_query_book_info_1(book_name, user_id):
sql = "select book_id,book_name, book_img_path from book_info where instr(book_name,'{}') and book_state = '1' and book_id in ( select book_id from borrow_info where user_id = {})".format(
book_name, user_id)
result = mysql_module(sql)
if not result[0]:
return [False, "用户信息查询失败"]
return [True, result[1]]
def sql_query_photo(username):
sql1 = 'select user_photo from user where user_account = "{}"'.format(
username)
result1 = mysql_module(sql1)
if result1[1]:
return result1[1][0]["user_photo"]
return None
def sql_update_info(user_account, user_name, user_sex, email, phone):
sql = "UPDATE user SET user_email = '{}', user_phone = '{}',user_name = '{}',user_sex = '{}' WHERE user_account = '{}'".format(
email, phone, user_name, user_sex, user_account)
result = mysql_module(sql)
if not result[0]:
return [False, "信息更新失败"]
return [True]
def sql_update_password(user_account, new_password):
sql = "update user set user_password = '******' where user_account = '{}'".format(
new_password, user_account)
result = mysql_module(sql)
if not result[0]:
return [False, "密码更改失败"]
return [True]
def sql_email(user_name):
sql = "select user_email from user where user_account = '{}' ".format(
user_name)
result = mysql_module(sql)
if not result[1]:
return [False, "用户名不存"]
return [True, result[1][0]["user_email"]]
def sql_borrowed_records(user_id):
sql = 'SELECT book.book_id,book_name,book_auther,book.book_publisher, cast(borrow_time as char) as borrow_time,cast(actual_return_time as char) as actual_return_time,book_room FROM ( SELECT book_id,book_publisher, book_name, book_auther, book_room FROM book_info WHERE book_id IN ( SELECT book_id FROM borrow_info WHERE user_id = {} ) ) AS book LEFT JOIN borrow_info borrow ON borrow.book_id = book.book_id and borrow.user_id = 21'.format(
user_id)
result = mysql_module(sql)
if not result[0]:
return [False, "查询出错"]
return [True, result[1]]
def sql_query_book_info(book_id):
sql = "select book_id,book_name,book_auther,book_category,book_publisher,book_room,book_bookshelf,book_synopsis,book_state,cast(book_publication_date as char) as book_publication_date ,cast(books_add_time as char) as books_add_time, book_language,book_img_path from book_info where book_id = '{}' ".format(
book_id)
result = mysql_module(sql)
if not result[0]:
return [False, "查询出错"]
return result
def sql_feedbacks(user_id, readers, phone, feedbacks):
st = set_time()
sql = "insert into feedback(user_id,readers,phone,feedbacks,state,time) value ('{}','{}','{}','{}','1','{}')".format(
user_id, readers, phone, feedbacks, st.today())
result = mysql_module(sql)
if not result[0]:
return False
return True
def sql_query_book_info_0(book_name):
sql = "select book_id,book_name,book_img_path from book_info where book_state = '0'"
if book_name:
sql = "select book_id,book_name,book_img_path from book_info where instr(book_name,'{}') and book_state = '0'".format(
book_name)
result = mysql_module(sql)
if not result[0]:
return [False, "用户信息查询失败"]
return [True, result[1]]
def sql_verify_old_password(user_account, verify_password):
sql = "select user_account from user where user_account = '{}' and user_password = '******'".format(
user_account, verify_password)
result = mysql_module(sql)
if not result[0]:
return [False, "查询出错"]
if not result[1]:
return [False, '原密码错误']
return [True]
def into_register_info(user_account, user_phone, username, password, email):
st = set_time()
select_user = "******".format(
username)
result = mysql_module(select_user)
if result[1]:
return [False, "该昵称已存在"]
select_user = "******".format(
username)
result = mysql_module(select_user)
if result[1]:
return [False, "该昵称已存在"]
into_user = '******' \
'user_phone,user_registration_time) VALUES("{}","{}","{}","{}","{}","{}")'.format(
user_account, password, username, email, user_phone, st.today())
result = mysql_module(into_user)
if not result[0]:
return [False, "注册失败"]
return [True, "注册成功"]
def sql_query_user_info(user_name):
if user_name:
sql = "select user_id,user_account, user_email, cast(user_registration_time as char) as user_registration_time from user where instr(user_account,'{}')".format(
user_name)
else:
sql = "select user_id,user_account, user_email, cast(user_registration_time as char) as user_registration_time from user"
result = mysql_module(sql)
if not result[0]:
return [False, "用户信息查询失败"]
return [True, result[1]]
def sql_change_book_info(**kwargs):
list_key = list(kwargs.keys())
list_value = list(kwargs.values())
sql = "UPDATE book_info SET {} = '{}',{} = '{}',{} = '{}',{} = {},{} = '{}',{} = '{}',{} = '{}',{} = '{}',{} = '{}',{} = '{}' where {} = '{}'".format(
list_key[0], list_value[0], list_key[1], list_value[1], list_key[2],
list_value[2], list_key[3], list_value[3], list_key[4], list_value[4],
list_key[5], list_value[5], list_key[6], list_value[6], list_key[7],
list_value[7], list_key[8], list_value[8], list_key[9], list_value[9],
list_key[10], list_value[10])
return mysql_module(sql)
def insertnewbook(**kwargs):
list_key = list(kwargs.keys())
list_value = list(kwargs.values())
sql = "insert into book_info ({},{},{},{},{},{},{},{},{},{},{},{}) values ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(
list_key[0], list_key[1], list_key[2], list_key[3], list_key[4], list_key[5], list_key[6], list_key[7],
list_key[8], list_key[9], list_key[10], list_key[11],
list_value[0], list_value[1], list_value[2], list_value[3], list_value[4], list_value[5], list_value[6],
list_value[7], list_value[8], list_value[9], list_value[10], list_value[11])
into_result = mysql_module(sql)
if not into_result[0]:
return [False, "数据存储失败"]
return [True]
def sql_class_lookup(category1, category2, language):
sql1 = "select id from book_category where 1 = 1 "
if category1:
sql1 = sql1 + " and category1 = '{}'".format(category1)
if category2:
sql1 = sql1 + " and category2 = '{}'".format(category2)
sql2 = "select book_id,book_name,book_auther,book_img_path from book_info where book_category in ({}) ".format(
sql1)
if language:
sql2 = sql2 + " and book_language = '{}'".format(language)
result = mysql_module(sql2)
return result
def sql_delete_user(user_name):
sql_id = "select user_id from user where user_account = '{}'".format(
user_name)
user_id = mysql_module(sql_id)[1][0]["user_id"]
sql_de_bookshelf = "delete from my_bookshelf where user_id = '{}'".format(
user_id)
sql_de_feedback = "delete from feedback where user_id = '{}'".format(
user_id)
sql_de_borrow = "delete from borrow_info where user_id = '{}'".format(
user_id)
sql_de_user = "******".format(user_id)
result = mysql_modules(sql_de_bookshelf, sql_de_feedback, sql_de_borrow,
sql_de_user)
return result
def sql_borrowing_condition():
st = set_time()
time_dict = st.first_half_year()
key = list(time_dict.keys())
times = list(time_dict.values())
result_list = {}
number = []
month = []
sql = 'SELECT * from (select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}") UNION ALL select count(borrow_id) number from borrow_info where instr(borrow_time,"{}")) as total'.format(
times[0], times[1], times[2], times[3], times[4], times[5])
result = mysql_module(sql)
for i in range(6):
number.append(result[1][i]['number'])
month.append(str(key[i]) + "月")
result_list["number"] = number
result_list["month"] = month
return result_list
def sql_conditional_book_info(book_id, book_name, book_publisher, book_room,
book_state):
sql = "1 = 1"
if book_id:
sql = sql + " and book_id = {}".format(book_id)
if book_name:
sql = sql + " and instr(book_name,'{}')".format(book_name)
if book_publisher:
sql = sql + " and instr(book_publisher,'{}')".format(book_publisher)
if book_room:
sql = sql + " and instr(book_room,'{}')".format(book_room)
if book_state:
sql = sql + " and book_state = '{}'".format(book_state)
sql = "select book_id,book_code,book_name,book_auther,category1,category2,book_publisher,book_room,book_bookshelf,book_synopsis,cast(book_publication_date as char) book_publication_date,book_language,book_state from book_info inner JOIN book_category on id = book_category where {}".format(
sql)
result = mysql_module(sql)
return result
def sql_new_arrivals(today_time, past_time, language, category1, category2):
sql1 = "where 1 = 1"
sql_cate = "select id from book_category where 1 = 1"
if today_time:
sql1 = sql1 + " and books_add_time<='{}'".format(today_time)
if past_time:
sql1 = sql1 + " and books_add_time >='{}' ".format(past_time)
if language:
sql1 = sql1 + " and book_language = '{}'".format(language)
if category1:
sql_cate = sql_cate + " and category1 = '{}'".format(category1)
if category2:
sql_cate = sql_cate + " and category2 = '{}'".format(category2)
sql2 = 'select book_id,book_name,book_auther, book_img_path from book_info {} and book_category in ({}) order by books_add_time desc limit 50 '.format(
sql1, sql_cate)
result = mysql_module(sql2)
return [True, result[1]]
def sql_popular_recommendation(today_time, past_time, language, category1,
category2):
sql1 = "where 1 = 1"
sql_cate = "select id from book_category where 1 = 1"
if today_time:
sql1 = sql1 + " and books_add_time<='{}'".format(today_time)
if past_time:
sql1 = sql1 + " and books_add_time >='{}' ".format(past_time)
if language:
sql1 = sql1 + " and book_language = '{}'".format(language)
if category1:
sql_cate = sql_cate + " and category1 = '{}'".format(category1)
if category2:
sql_cate = sql_cate + " and category2 = '{}'".format(category2)
sql2 = "select distinct book.book_id,book.book_name,book_auther, count(book.book_id) as count, book.book_img_path from borrow_info borrow left join book_info book on borrow.book_id = book.book_id {} and book.book_category in ({}) group by book_id order by count desc limit 50".format(
sql1, sql_cate)
result = mysql_module(sql2)
return [True, result[1]]
def sql_conditional_borrow_record(book_id, book_name, book_publisher,
borrow_time_satrt, borrow_time_end,
user_name):
sql = "select borrow_id,book.book_id, book.book_code, book.book_name,book.book_publisher,cast(borrow.borrow_time as char) borrow_time,cast(actual_return_time as char) actual_return_time,user.user_account from borrow_info borrow inner join book_info book on borrow.book_id = book.book_id inner join user on borrow.user_id = user.user_id where borrow.state = '0'"
if book_id:
sql = sql + " and instr(borrow.book_id,'{}')".format(book_id)
if book_name:
sql = sql + " and instr(book_name,'{}')".format(book_name)
if book_publisher:
sql = sql + " and instr(book_publisher,'{}')".format(book_publisher)
if borrow_time_satrt:
sql = sql + " and borrow_time >= '{}'".format(borrow_time_satrt)
if borrow_time_end:
sql = sql + " and borrow_time <= '{}'".format(borrow_time_end)
if user_name:
sql = sql + " and instr(user_account,'{}')".format(user_name)
result = mysql_module(sql)
return result
