def delete(self, uid, nid):
utoken = request.args.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info or user_info['id'] != uid:
return jsonify({"message": "参数错误"}), 400
db_connection = MySQLConnection()
try:
cursor = db_connection.get_cursor()
select_statement = "SELECT `file_url` FROM `info_exnotice` WHERE `id`=%d;" % nid
cursor.execute(select_statement)
file_url = cursor.fetchone()['file_url']
delete_statement = "DELETE FROM `info_exnotice` " \
"WHERE `id`=%d AND `author_id`=%d AND DATEDIFF(NOW(),`create_time`) < 3;" % (nid, uid)
lines_changed = cursor.execute(delete_statement)
if lines_changed <= 0:
raise ValueError("不能删除较早期的通知了.")
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
return jsonify({"message": "删除失败{}".format(e)}), 400
else:
db_connection.close()
# 删除文件
if file_url:
file_addr = os.path.join(BASE_DIR, "fileStorage/" + file_url)
if os.path.isfile(file_addr):
os.remove(file_addr)
return jsonify({"message": "删除成功!"})
def patch(self, uid):
body_json = request.json
utoken = body_json.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify({'message': '登录已过期，重新登录再进行修改'}), 400
user_id = int(user_info['id'])
modify_dict = dict()
for can_modify_key in ['username', 'password', 'phone', 'email']:
if can_modify_key in body_json:
if can_modify_key == 'password':
modify_dict['password'] = hash_user_password(
body_json['password'])
else:
modify_dict[can_modify_key] = body_json[can_modify_key]
update_str = ""
for modify_key in modify_dict:
update_str += "`{}`='{}',".format(modify_key,
modify_dict[modify_key])
update_str = update_str[:-1]
update_statement = "UPDATE `info_user` SET %s WHERE `id`=%d;" % (
update_str, user_id)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(update_statement)
db_connection.commit()
db_connection.close()
return jsonify({"message": "修改成功!"})
def post(self, uid): # 修改头像
avatar_file = request.files.get('image', None)
if not avatar_file:
return jsonify({'message': '修改成功!'})
avatar_folder = os.path.join(BASE_DIR,
"fileStorage/avatars/{}/".format(uid))
if not os.path.exists(avatar_folder):
os.makedirs(avatar_folder)
avatar_file_name = avatar_file.filename
avatar_hash_name = hash_filename(avatar_file_name)
file_path = os.path.join(avatar_folder, avatar_hash_name)
file_save_url = "avatars/{}/{}".format(uid, avatar_hash_name)
avatar_file.save(file_path)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 找出旧头像文件删除
select_old_avatar = "SELECT `avatar` FROM `info_user` WHERE `id`=%s;"
cursor.execute(select_old_avatar, uid)
fetch_avatar = cursor.fetchone()
if fetch_avatar:
old_url = fetch_avatar['avatar']
old_path = os.path.join(BASE_DIR, 'fileStorage/{}'.format(old_url))
if os.path.exists(old_path):
os.remove(old_path)
save_statement = "UPDATE `info_user` SET `avatar`=%s WHERE `id`=%s;"
cursor.execute(save_statement, (file_save_url, uid))
db_connection.commit()
db_connection.close()
return jsonify({'message': '修改成功!', 'avatar_url': file_save_url})
def post(self, user_id, module_id):
token = request.json.get('utoken', None)
is_active = True if request.json.get('is_checked', False) else False
if not psd_handler.user_is_admin(token):
return jsonify('登录已过期或没有权限进行这个操作'), 400
try:
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
nowork_select_statement = "SELECT `user_id`,`module_id`,`is_active` FROM `user_ndo_module` WHERE `user_id`=%s AND `module_id`=%s;"
cursor.execute(nowork_select_statement, (user_id, module_id))
nowork_module = cursor.fetchone()
# print(nowork_module)
if not nowork_module: # 数据库不存在记录增加一条
add_statement = "INSERT INTO `user_ndo_module` (user_id,module_id,is_active) VALUES (%s,%s,%s);"
cursor.execute(add_statement,
(user_id, module_id, not is_active))
else: # 存在记录就更新
update_statement = "UPDATE `user_ndo_module` SET `is_active`=%s WHERE `user_id`=%s AND `module_id`=%s;"
cursor.execute(update_statement,
(not is_active, user_id, module_id))
db_connection.commit()
db_connection.close()
except Exception as e:
logger = current_app.logger
logger.error("修改用户需要工作的模块失败:" + str(e))
return jsonify('操作错误,500 SERVER ERROR'), 400
else:
return jsonify("操作成功")
def get(self):
token = request.args.get("utoken")
if not psd_handler.user_is_admin(token):
return jsonify("登录已过期或没有权限进行这个操作"), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 查询所有用户信息
select_statement = "SELECT id,name,fixed_code,join_time,phone,email,update_time,is_active,is_admin,org_id FROM user_info;"
cursor.execute(select_statement)
# 重新组织用户数据
user_data = list()
for user_item in cursor.fetchall():
user_dict = dict()
user_dict['id'] = user_item['id']
user_dict['name'] = user_item['name']
user_dict['fixed_code'] = user_item['fixed_code']
user_dict['join_time'] = user_item['join_time'].strftime(
'%Y-%m-%d %H:%M:%S')
user_dict['update_time'] = user_item['update_time'].strftime(
'%Y-%m-%d %H:%M:%S')
user_dict['is_active'] = user_item['is_active']
user_dict['is_admin'] = user_item['is_admin']
user_dict['organization'] = ORGANIZATIONS.get(
user_item['org_id'], '未知')
user_dict['phone'] = user_item['phone']
user_dict['email'] = user_item['email']
user_data.append(user_dict)
db_connection.close()
return jsonify(user_data)
def post(self):
body_json = request.json
utoken = body_json.get('utoken', None)
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify({"message": "登录已过期!"}), 400
if user_info['role_num'] > 2:
return jsonify({"message": "没有权限进行这个操作!"}), 400
module_name = body_json.get('module_name', None)
parent_id = body_json.get('parent_id', None)
if not module_name:
return jsonify({"message": "参数错误! NOT FOUND NAME."}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
if not parent_id:
insert_statement = "INSERT INTO `info_module` (`name`) VALUES (%s);"
cursor.execute(insert_statement, module_name)
else:
parent_id = int(parent_id)
insert_statement = "INSERT INTO `info_module`(`name`,`parent_id`) VALUES (%s,%s);"
cursor.execute(insert_statement, (module_name, parent_id))
new_mid = db_connection.insert_id() # 新加入的id
update_statement = "UPDATE `info_module` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_statement, (new_mid, new_mid))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
return jsonify({"message": "添加失败{}".format(e)}), 400
else:
db_connection.close()
return jsonify({"message": "添加成功!"}), 201
def save_json_data(self):
body_json = request.json
utoken = body_json.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info or user_info['role_num'] > enums.COLLECTOR:
return jsonify({"message": "登录已过期或不能操作."}), 400
today = datetime.datetime.today()
date = body_json.get('date')
time = body_json.get('time')
country = body_json.get('country', '')
event = body_json.get('event', '')
expected = body_json.get('expected', '')
if not all([date, time, event]):
return jsonify({"message": "参数错误."}), 400
save_statement = "INSERT INTO `info_finance` " \
"(`create_time`,`date`, `time`,`country`,`event`,`expected`,`author_id`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s);"
db_connection = MySQLConnection()
try:
date = datetime.datetime.strptime(date, "%Y-%m-%d")
time = datetime.datetime.strptime(time, "%H:%M:%S")
user_id = user_info['id']
cursor = db_connection.get_cursor()
cursor.execute(
save_statement,
(today, date, time, country, event, expected, user_id))
db_connection.commit()
except Exception as e:
db_connection.close()
current_app.logger.error("写入财经日历错误:{}".format(e))
return jsonify({"message": "错误:{}".format(e)})
else:
return jsonify({"message": "添加成功!"}), 201
def get(self, uid):
group = request.args.get('group')
variety = request.args.get('variety')
try:
group_id = int(group)
variety_id = int(variety)
except Exception:
return jsonify({"message": "参数错误"}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
if group_id == 0: # 获取全部
query_statement = "SELECT * " \
"FROM `info_trend_table` " \
"WHERE `author_id`=%d AND `variety_id`=%d AND `is_active`=1;" % (uid, variety_id)
else:
query_statement = "SELECT * " \
"FROM `info_trend_table` " \
"WHERE `author_id`=%d AND `variety_id`=%d AND `is_active`=1 AND `group_id`=%d;" % (uid, variety_id, group_id)
cursor.execute(query_statement)
db_connection.close()
query_result = cursor.fetchall()
for record_item in query_result:
record_item['create_time'] = record_item['create_time'].strftime(
'%Y-%m-%d')
record_item['update_time'] = record_item['update_time'].strftime(
'%Y-%m-%d')
return jsonify({"message": "查询成功!", "tables": query_result})
def get(self):
try:
query_year = int(request.args.get('year', 0))
query_month = int(request.args.get('month', 0)) # 获取查询的月份
except Exception:
return jsonify("参数错误"), 400
if not query_month:
start_date, end_date = statisticutils.get_year_start_year_end(
query_year)
query_statement = "SELECT DATE_FORMAT(invstb.custom_time,'%%Y-%%m') AS `date`, invstb.author_id, usertb.name, COUNT(*) AS `count` " \
"FROM `investment` as invstb INNER JOIN `user_info` as usertb " \
"ON (invstb.author_id=usertb.id) AND (DATE_FORMAT(invstb.custom_time,'%%Y-%%m-%%d') BETWEEN %s AND %s) GROUP BY invstb.author_id, DATE_FORMAT(invstb.custom_time,'%%Y-%%m') ORDER BY DATE_FORMAT(invstb.custom_time,'%%Y-%%m') ASC;"
target_date_array = statisticutils.generate_month_array(query_year)
else:
start_date, end_date = statisticutils.get_first_month_last_month(
query_year, query_month)
query_statement = "SELECT DATE_FORMAT(invstb.custom_time,'%%Y-%%m-%%d') AS `date`, invstb.author_id, usertb.name, COUNT(*) AS `count` " \
"FROM `investment` as invstb INNER JOIN `user_info` as usertb " \
"ON (invstb.author_id=usertb.id) AND (DATE_FORMAT(invstb.custom_time,'%%Y-%%m-%%d') BETWEEN %s AND %s) GROUP BY invstb.author_id, DATE_FORMAT(invstb.custom_time,'%%Y-%%m-%%d') ORDER BY DATE_FORMAT(invstb.custom_time,'%%Y-%%m-%%d') ASC;"
target_date_array = statisticutils.generate_date_array(
query_year, query_month)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(query_statement, (start_date, end_date))
amount_all = cursor.fetchall()
db_connection.close()
if not amount_all: # 没有数据记录
return jsonify({})
statistics_arr = statisticutils.query_result_into_stuffs(
target_date_array, amount_all)
return jsonify(statistics_arr)
def get(self):
is_json = request.args.get('is_json', False)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
select_statement = "SELECT `id`,`create_time`,`update_time`,`title`,`decipherment`,`is_trend_show`,`is_variety_show` " \
"FROM `info_trend_echart` WHERE `is_trend_show`=1;"
cursor.execute(select_statement)
user_charts = cursor.fetchall()
db_connection.close()
if is_json: # 返回json数据
for chart_item in user_charts:
chart_item['create_time'] = chart_item['create_time'].strftime(
"%Y-%m-%d")
chart_item['update_time'] = chart_item['update_time'].strftime(
"%Y-%m-%d")
return jsonify({
"message": 'get charts of current table successfully!',
"charts": user_charts
})
else: # 直接渲染数据
has_chart = 0
if len(user_charts) > 0:
has_chart = 1
return render_template('trend/charts_render.html',
user_charts=user_charts,
has_chart=has_chart)
def post(self, uid):
body_json = request.json
title = body_json.get('title', None)
table_id = body_json.get('table_id', None)
bottom_axis = body_json.get('bottom_axis', None)
left_axis = body_json.get('left_axis', None)
if not all([title, table_id, bottom_axis, left_axis]):
return jsonify({'message': '参数不足'}), 400
is_watermark = body_json.get('is_watermark', False)
watermark = body_json.get('watermark', '')
decipherment = body_json.get('decipherment', '')
right_axis = body_json.get('right_axis', '{}')
# 通过table_id获取group_id 和variety_id
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
table_info_statement = "SELECT `sql_table`,`group_id`,`variety_id` " \
"FROM info_trend_table " \
"WHERE `id`=%s;"
cursor.execute(table_info_statement, table_id)
fetch_table_one = cursor.fetchone()
sql_table = fetch_table_one['sql_table']
variety_id = fetch_table_one['variety_id']
group_id = fetch_table_one['group_id']
# save_chart
save_chart_statement = "INSERT INTO `info_trend_chart` (" \
"`title`, `table_id`,`sql_table`,`is_watermark`,`watermark`,`variety_id`, `group_id`," \
"`bottom_axis`,`left_axis`,`right_axis`,`author_id`,`updater_id`,`decipherment`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
cursor.execute(save_chart_statement,
(title, table_id, sql_table, is_watermark, watermark,
variety_id, group_id, bottom_axis, left_axis,
right_axis, uid, uid, decipherment))
db_connection.commit()
db_connection.close()
return jsonify({"message": '保存配置成功'}), 201
def post(self):
body_json = request.json
utoken = body_json.get('utoken', None)
operate_user = verify_json_web_token(utoken)
if not operate_user or operate_user['role_num'] > 2:
return jsonify({'message': '登录过期或不能进行这个操作!'}), 400
current_id = body_json.get('c_id', None)
target_id = body_json.get('t_id', None)
try:
current_id = int(current_id)
target_id = int(target_id)
except Exception as e:
return jsonify({'message': '参数错误！'}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
query_statement = "SELECT `sort` FROM `info_module` WHERE `id`=%s;"
cursor.execute(query_statement, current_id)
current_sort = cursor.fetchone()
cursor.execute(query_statement, target_id)
target_sort = cursor.fetchone()
if not all([current_sort, target_sort]):
db_connection.close()
return jsonify({'message': '要排序的模块不存在!'}), 400
current_sort = current_sort['sort']
target_sort = target_sort['sort']
update_statement = "UPDATE `info_module` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_statement, (target_sort, current_id))
cursor.execute(update_statement, (current_sort, target_id))
db_connection.commit()
db_connection.close()
return jsonify({'message': '排序成功!'})
def post(self):
body_json = request.json
try:
variety_id = int(body_json.get('variety_id'))
group_name = body_json.get('name')
if not group_name:
raise ValueError('group ERROR')
utoken = body_json.get('utoken')
except Exception as e:
return jsonify({"message": "参数错误"}), 400
user_info = verify_json_web_token(utoken)
if not user_info or user_info['role_num'] > enums.RESEARCH:
return jsonify({"message": "登录已过期或不能操作"})
user_id = user_info['id']
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 查询用户权限
auth_statement = "SELECT `id` FROM `link_user_variety` " \
"WHERE `user_id`=%d AND `variety_id`=%d;" % (user_id, variety_id)
cursor.execute(auth_statement)
if not cursor.fetchone():
db_connection.close()
return jsonify({"message": "没有权限,不能这样操作"}), 400
save_statement = "INSERT INTO `info_variety_trendgroup` " \
"(`name`,`variety_id`,`author_id`) " \
"VALUES (%s,%s,%s);"
cursor.execute(save_statement, (group_name, variety_id, user_id))
new_id = db_connection.insert_id()
update_sort_statement = "UPDATE `info_variety_trendgroup` SET `sort`=%d WHERE `id`=%d;" % (
new_id, new_id)
cursor.execute(update_sort_statement)
db_connection.commit()
db_connection.close()
return jsonify({"message": "添加组成功!"}), 201
def get(self, uid):
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 查询当前用户信息
query_user = "******" \
"FROM `info_user` WHERE `id`=%d;" % uid
cursor.execute(query_user)
user_info = cursor.fetchone()
if user_info['role_num'] == 1:
query_statement = "SELECT `id` AS `variety_id`,`name`,`is_active` " \
"FROM `info_variety` WHERE `is_active`=1 AND `parent_id` IS NOT NULL;"
else:
query_statement = "SELECT linkuv.id,linkuv.user_id,linkuv.variety_id,linkuv.is_active,infov.name " \
"FROM `link_user_variety` AS linkuv INNER JOIN `info_variety` AS infov " \
"ON linkuv.user_id=%d AND linkuv.variety_id=infov.id;" % uid
cursor.execute(query_statement)
query_result = cursor.fetchall()
db_connection.close()
# print(query_result)
return jsonify({
"message": "查询成功!",
"variety": query_result,
"user_info": user_info
})
def get(self):
query_statement = "SELECT infodis.id,infodis.content,infodis.create_time," \
"usertb.username,usertb.phone,usertb.avatar " \
"FROM `info_discussion` AS infodis " \
"INNER JOIN `info_user` AS usertb " \
"ON infodis.author_id=usertb.id " \
"WHERE infodis.parent_id is NULL " \
"ORDER BY infodis.create_time DESC " \
"LIMIT 8;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(query_statement)
query_all = cursor.fetchall()
response_data = list()
for dis_item in query_all:
dis_dict = dict()
dis_dict['id'] = dis_item['id']
if dis_item['username']:
username = dis_item['username']
else:
phone = dis_item['phone']
username = phone[:4] + '****' + phone[8:]
dis_dict['username'] = username
dis_dict['avatar'] = dis_item['avatar']
dis_dict['create_time'] = dis_item['create_time'].strftime('%Y-%m-%d')
dis_dict['text'] = dis_item['content']
# 查询回复
dis_dict['replies'] = get_sub_reply(cursor, dis_item['id'])
response_data.append(dis_dict)
db_connection.close()
return jsonify({'message': '查询成功!', 'discussions': response_data})
def patch(self):
body_json = request.json
utoken = body_json.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify({'message': '请登录后再操作..'}), 400
user_id = user_info['id']
variety_id = body_json.get('variety_id', None)
group_id = body_json.get('group_id', None)
machine_code = body_json.get('machine_code')
client_info = get_client(machine_code)
if not all([user_id, variety_id, client_info, group_id]):
return jsonify({'message': '参数错误'}), 400
client_id = client_info['id']
now = datetime.datetime.now()
update_statement = "UPDATE `info_tablesource_configs` " \
"SET `update_time`=%s " \
"WHERE `client_id`=%s AND `user_id`=%s AND `variety_id`=%s AND `group_id`=%s;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(update_statement,
(now, client_id, user_id, variety_id, group_id))
db_connection.commit()
db_connection.close()
return jsonify({'message': '修改成功'})
def put(self, rid):
body_json = request.json
record_info = body_json.get('record_data')
utoken = body_json.get('utoken')
user_info = verify_json_web_token(utoken)
user_id = user_info['uid']
# 不为空的信息判断
content = record_info.get('content', False)
if not content:
return jsonify("参数错误,NOT FOUND CONTENT"), 400
# 组织信息
custom_time = record_info.get('custom_time')
note = record_info.get('note', '')
# 存入数据库
update_statement = "UPDATE `onduty_message` SET " \
"`custom_time`=%s,`content`=%s,`note`=%s" \
"WHERE `id`=%s AND `author_id`=%s;"
try:
user_id = int(user_id)
custom_time = datetime.datetime.strptime(
custom_time,
'%Y-%m-%d') if custom_time else datetime.datetime.now()
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(update_statement,
(custom_time, content, note, rid, user_id))
db_connection.commit()
db_connection.close()
except Exception as e:
current_app.logger.error("修改值班记录错误:" + str(e))
return jsonify("参数错误!无法修改。"), 400
else:
return jsonify("修改成功!")
def post(self):
body_json = request.json
machine_code = body_json.get('machine_code', '')
is_manager = body_json.get('is_manager', 0)
if not machine_code:
return jsonify("NOT FOUND machine_code!"), 400
query_statement = "SELECT `id` FROM `info_client` WHERE `machine_code`=%s;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(query_statement, machine_code)
client = cursor.fetchone()
agent = request.headers.get('User-Agent', '')
origin = ''
if agent.startswith('Delivery'):
origin = 'delivery'
if not client:
# 创建客户端
insert_statement = "INSERT INTO `info_client` (`machine_code`, `is_manager`,`origin`) VALUES (%s, %s,%s);"
cursor.execute(insert_statement,
(machine_code, is_manager, origin))
else:
# 更新打开时间
now = datetime.datetime.now()
update_statement = "UPDATE `info_client` SET `update_time`=%s,`origin`=%s WHERE `id`=%s;"
cursor.execute(update_statement, (now, origin, client['id']))
db_connection.commit()
db_connection.close()
return jsonify({
"message": "检测或注册客户端成功!",
'machine_code': machine_code
})
def get(self, uid):
params = request.args
try:
current_page = int(params.get('page', 1)) - 1
page_size = int(params.get('page_size', 50))
except Exception:
return jsonify({"message": "参数错误。"}), 400
id_start = current_page * page_size
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
query_statement = "SELECT `id`, `custom_time`,`content` " \
"FROM `info_shortmessage` " \
"WHERE `author_id`=%d ORDER BY `custom_time` DESC " \
"LIMIT %d,%d;" % (uid, id_start, page_size)
cursor.execute(query_statement)
query_result = cursor.fetchall()
# 查询总数量
cursor.execute("SELECT COUNT(`id`) AS `total` FROM `info_shortmessage` WHERE `author_id`=%d;" % uid)
total_count = cursor.fetchone()['total']
db_connection.close()
total_page = int((total_count + page_size - 1) / page_size)
for sms_item in query_result:
sms_item['custom_time'] = sms_item['custom_time'].strftime('%Y-%m-%d %H:%M:%S')
return jsonify({
"message": "查询成功!",
"records": query_result,
"current_page": current_page,
"total_page": total_page,
"page_size": page_size,
"total_count": total_count
})
def drop_duplicates(new_df, user_id): # 遗弃，本方式不好用，出现未知错误
# 查询旧的数据
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
query_statement = "SELECT `custom_time`,`author_id`,`content`,`note` FROM " \
"`onduty_message` WHERE `author_id`=%s;"
cursor.execute(query_statement, user_id)
old_df = pd.DataFrame(cursor.fetchall())
db_connection.close()
if old_df.empty:
new_df['custom_time'] = pd.to_datetime(new_df['custom_time'],
format='%Y-%m-%d')
new_df['custom_time'] = new_df['custom_time'].apply(
lambda x: x.strftime('%Y-%m-%d'))
save_df = new_df.drop_duplicates(subset=['custom_time', 'content'],
keep='last',
inplace=False)
else:
old_df['custom_time'] = pd.to_datetime(old_df['custom_time'],
format='%Y-%m-%d')
old_df['custom_time'] = old_df['custom_time'].apply(
lambda x: x.strftime('%Y-%m-%d'))
new_df['custom_time'] = pd.to_datetime(new_df['custom_time'],
format='%Y-%m-%d')
new_df['custom_time'] = new_df['custom_time'].apply(
lambda x: x.strftime('%Y-%m-%d'))
concat_df = pd.concat([old_df, new_df, old_df])
save_df = concat_df.drop_duplicates(
subset=['custom_time', 'content'], keep=False, inplace=False)
if save_df.empty:
return []
else:
save_df = save_df.copy()
# save_df['custom_time'] = save_df['custom_time'].apply(lambda x:x.strftime('%Y-%m-%d'))
return save_df.values.tolist()
def post(self):
body_json = request.json
utoken = body_json.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info or user_info['role_num'] > enums.RESEARCH:
return jsonify({"message":"登录过期或不能执行操作."}), 400
today = datetime.datetime.today()
custom_time = body_json.get('custom_time', today)
content = body_json.get('content')
if not content:
return jsonify({"message":"请输入内容"}), 400
db_connection = MySQLConnection()
try:
custom_time = datetime.datetime.strptime(custom_time, "%Y-%m-%d %H:%M:%S")
author_id = int(user_info['id'])
save_statement = "INSERT INTO `info_shortmessage` (`create_time`,`custom_time`,`content`,`author_id`) " \
"VALUES (%s,%s,%s,%s);"
cursor = db_connection.get_cursor()
cursor.execute(save_statement,(today, custom_time,content,author_id))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("添加短信通错误:{}".format(e)), 400
return jsonify({'message':'添加错误:{}'.format(e)})
else:
db_connection.close()
return jsonify({"message":"添加成功!"}), 201
def delete(self, uid, sid):
utoken = request.args.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info or user_info['id'] != uid:
return jsonify({"message": "参数错误"}), 400
db_connection = MySQLConnection()
try:
cursor = db_connection.get_cursor()
select_statement = "SELECT `file_url` FROM `info_searchreport` WHERE `id`=%d;" % sid
cursor.execute(select_statement)
file_url = cursor.fetchone()['file_url']
delete_statement = "DELETE FROM `info_searchreport` " \
"WHERE `id`=%d AND `author_id`=%d;" % (sid, uid)
cursor.execute(delete_statement)
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
return jsonify({"message": "删除失败{}".format(e)}), 400
else:
db_connection.close()
# 删除文件
if file_url:
file_addr = os.path.join(BASE_DIR, "fileStorage/" + file_url)
if os.path.isfile(file_addr):
os.remove(file_addr)
return jsonify({"message": "删除成功!"})
def get(self, uid):
params = request.args
try:
current_page = int(params.get('page', 1)) - 1
page_size = int(params.get('page_size', 50))
except Exception:
return jsonify({"message": "参数错误。"}), 400
id_start = current_page * page_size
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
query_statement = "SELECT `id`, DATE_FORMAT(`create_time`, '%%Y-%%m-%%d') AS `create_time`,`title`,`file_url`, `is_active` " \
"FROM `info_searchreport` " \
"WHERE `author_id`=%d ORDER BY `create_time` DESC " \
"LIMIT %d,%d;" % (uid, id_start, page_size)
cursor.execute(query_statement)
query_result = cursor.fetchall()
# 查询总数量
cursor.execute("SELECT COUNT(`id`) AS `total` FROM `info_searchreport` WHERE `author_id`=%d;" % uid)
total_count = cursor.fetchone()['total']
db_connection.close()
total_page = int((total_count + page_size - 1) / page_size)
return jsonify({
"message": "查询成功!",
"records": query_result,
"current_page": current_page,
"total_page": total_page,
"page_size": page_size,
"total_count": total_count
})
def delete(self, rid):
utoken = request.args.get('utoken')
user_info = verify_json_web_token(utoken)
db_connection = MySQLConnection()
annex_file_path = None
try:
cursor = db_connection.get_cursor()
annex_query_statement = "SELECT `annex_url` FROM `monographic` WHERE `id`=%d;" % rid
cursor.execute(annex_query_statement)
annex_file = cursor.fetchone()
if annex_file:
annex_file_path = annex_file['annex_url']
user_id = int(user_info['uid'])
delete_statement = "DELETE FROM `monographic` " \
"WHERE `id`=%d AND `author_id`=%d;" % (rid, user_id)
lines_changed = cursor.execute(delete_statement)
db_connection.commit()
if lines_changed <= 0:
raise ValueError("删除错误,没有记录被删除>…<")
except Exception as e:
db_connection.rollback()
db_connection.close()
return jsonify(str(e))
else:
db_connection.close()
if annex_file_path:
file_local_path = os.path.join(BASE_DIR, annex_file_path)
if os.path.isfile(file_local_path):
os.remove(file_local_path)
return jsonify("删除成功^.^!")
def get(self):
params = request.args
try:
current_page = int(params.get('page', 1)) - 1
page_size = int(params.get('page_size', 50))
except Exception:
return jsonify({"message": "参数错误。"}), 400
id_start = current_page * page_size
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
query_statement = "SELECT `id`, DATE_FORMAT(`create_time`, '%%Y-%%m-%%d') AS `create_time`,`title`,`file_url` " \
"FROM `info_bulletin` " \
"WHERE `is_active`=1 ORDER BY `create_time` DESC " \
"LIMIT %d,%d;" % (id_start, page_size)
cursor.execute(query_statement)
query_result = cursor.fetchall()
# 查询总数量
cursor.execute(
"SELECT COUNT(`id`) AS `total` FROM `info_bulletin` WHERE `is_active`=1;"
)
total_count = cursor.fetchone()['total']
db_connection.close()
total_page = int((total_count + page_size - 1) / page_size)
response_data = dict()
response_data['bulletins'] = query_result
response_data['total_page'] = total_page
response_data['page_size'] = page_size
response_data['current_page'] = current_page
return jsonify({
"message": "查询成功!",
"bulletins": query_result,
"current_page": current_page,
"total_page": total_page,
"page_size": page_size
})
def get(self, uid):
t_client = request.args.get('t', 0)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 查询用户的角色
role_statement = "SELECT `role_num` FROM `info_user` WHERE `id`=%s;"
cursor.execute(role_statement, uid)
user_role_num = cursor.fetchone()['role_num']
select_statement = "SELECT client.id, client.name, client.machine_code, client.is_manager, linkuser.user_id, linkuser.client_id, linkuser.expire_time " \
"FROM `info_client` AS client LEFT JOIN `link_user_client` AS linkuser " \
"ON client.id=linkuser.client_id AND linkuser.user_id=%s AND linkuser.expire_time>NOW() " \
"WHERE client.is_manager=%s;"
cursor.execute(select_statement, (uid, t_client))
client_information = cursor.fetchall()
result = list()
for item in client_information:
new_item = dict()
new_item['id'] = item['id']
new_item['name'] = item['name']
new_item['machine_code'] = item['machine_code']
new_item['is_manager'] = '管理端' if item['is_manager'] else '普通端'
if user_role_num <= 2:
new_item['accessed'] = 1
new_item['expire_time'] = '3000-01-01'
else:
new_item['accessed'] = 1 if item['user_id'] else 0
new_item['expire_time'] = item['expire_time'].strftime(
'%Y-%m-%d') if item['expire_time'] else ''
result.append(new_item)
db_connection.close()
return jsonify({'message': '查询成功!', 'information': result})
def get_monographic(self, userid, start_date, end_date, current_page,
page_size):
start_id = current_page * page_size
table_headers = [
'日期', '姓名', '题目', '字数', '外发情况', '等级', '得分', '备注', '附件'
]
header_keys = [
'custom_time', 'name', 'title', 'words', 'is_publish', 'level',
'score', 'note', 'annex_url'
]
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
if userid == 0:
query_statement = "SELECT usertb.name, mogytb.* " \
"FROM `monographic` AS mogytb INNER JOIN `user_info` AS usertb " \
"ON mogytb.author_id=usertb.id AND (mogytb.custom_time BETWEEN %s AND %s) " \
"ORDER BY mogytb.custom_time DESC " \
"LIMIT %s,%s;"
cursor.execute(query_statement,
(start_date, end_date, start_id, page_size))
query_result = cursor.fetchall()
total_count_statement = "SELECT COUNT(mogytb.id) AS `total` " \
"FROM `monographic` AS mogytb INNER JOIN `user_info` AS usertb " \
"ON mogytb.author_id=usertb.id AND (mogytb.custom_time BETWEEN %s AND %s);"
cursor.execute(total_count_statement, (start_date, end_date))
total_count = cursor.fetchone()['total']
else:
query_statement = "SELECT usertb.name, mogytb.* " \
"FROM `monographic` AS mogytb INNER JOIN `user_info` AS usertb " \
"ON mogytb.author_id=usertb.id AND mogytb.author_id=%s AND (mogytb.custom_time BETWEEN %s AND %s) " \
"ORDER BY mogytb.custom_time DESC " \
"LIMIT %s,%s;"
cursor.execute(query_statement,
(userid, start_date, end_date, start_id, page_size))
query_result = cursor.fetchall()
total_count_statement = "SELECT COUNT(`id`) AS `total` FROM `monographic` " \
"WHERE `author_id`=%s AND `custom_time` BETWEEN %s AND %s;"
cursor.execute(total_count_statement,
(userid, start_date, end_date))
total_count = cursor.fetchone()['total']
total_page = int((total_count + page_size - 1) / page_size)
db_connection.close()
response_data = dict()
records = list()
for record_item in query_result:
record_item['custom_time'] = record_item['custom_time'].strftime(
"%Y-%m-%d")
record_item['words'] = int(record_item['words'])
record_item[
'is_publish'] = "是" if record_item['is_publish'] else "否"
records.append(record_item)
response_data['records'] = records
response_data['table_headers'] = table_headers
response_data['header_keys'] = header_keys
response_data['current_page'] = current_page + 1 # 查询前给减1处理了，加回来
response_data['total_page'] = total_page
response_data['total_count'] = total_count
return jsonify(response_data)
def get(self):
params = request.args
utoken = params.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify("登录已过期!刷新网页重新登录."), 400
try:
start_date = params.get('startDate')
end_date = params.get('endDate')
end_date = datetime.datetime.strptime(
end_date, '%Y-%m-%d') + datetime.timedelta(days=1)
end_date = (
end_date +
datetime.timedelta(seconds=-1)).strftime('%Y-%m-%d %H:%M:%S')
except Exception:
return jsonify("参数错误:DATE FORMAT ERROR!")
query_statement = "SELECT usertb.name,usertb.org_id,ondmsgtb.custom_time,ondmsgtb.content,ondmsgtb.note " \
"FROM `user_info` AS usertb INNER JOIN `onduty_message` AS ondmsgtb ON " \
"usertb.id=%s AND usertb.id=ondmsgtb.author_id AND (ondmsgtb.custom_time BETWEEN %s AND %s) " \
"ORDER BY ondmsgtb.custom_time ASC;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(query_statement,
(user_info['uid'], start_date, end_date))
records_all = cursor.fetchall()
db_connection.close()
# 生成承载数据的文件
t = "%.4f" % time.time()
md5_hash = hashlib.md5()
md5_hash.update(t.encode('utf-8'))
md5_hash.update(user_info['name'].encode('utf-8'))
md5_str = md5_hash.hexdigest()
file_folder = os.path.join(BASE_DIR, 'fileStore/exports/')
if not os.path.exists(file_folder):
os.makedirs(file_folder)
file_path = os.path.join(file_folder, '{}.xlsx'.format(md5_str))
file_records = list()
for record_item in records_all:
row_content = list()
row_content.append(record_item['custom_time'].strftime("%Y-%m-%d"))
row_content.append(ORGANIZATIONS.get(record_item['org_id'], '未知'))
row_content.append(record_item['name'])
row_content.append(record_item['content'])
row_content.append(record_item['note'])
file_records.append(row_content)
export_df = pd.DataFrame(file_records)
export_df.columns = ['日期', '部门小组', '姓名', '信息内容', '备注']
export_df.to_excel(excel_writer=file_path,
index=False,
sheet_name='值班信息记录')
return send_from_directory(
directory=file_folder,
filename='{}.xlsx'.format(md5_str),
as_attachment=True,
attachment_filename='{}.xlsx'.format(md5_str))
def get_article_publish(self, userid, start_date, end_date, current_page,
page_size):
start_id = current_page * page_size
table_headers = [
'日期', '姓名', '题目', '发表/采访媒体', '稿件形式', '字数', '审核人', '收入奖励', '合作人',
'备注', '附件'
]
header_keys = [
'custom_time', 'name', 'title', 'media_name', 'rough_type',
'words', 'checker', 'allowance', 'partner', 'note', 'annex_url'
]
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
if userid == 0:
query_statement = "SELECT usertb.name, arptb.* " \
"FROM `article_publish` AS arptb INNER JOIN `user_info` AS usertb " \
"ON arptb.author_id=usertb.id AND (arptb.custom_time BETWEEN %s AND %s) " \
"ORDER BY arptb.custom_time DESC " \
"LIMIT %s,%s;"
cursor.execute(query_statement,
(start_date, end_date, start_id, page_size))
query_result = cursor.fetchall()
total_count_statement = "SELECT COUNT(arptb.id) AS `total` " \
"FROM `article_publish` AS arptb INNER JOIN `user_info` AS usertb " \
"ON arptb.author_id=usertb.id AND (arptb.custom_time BETWEEN %s AND %s);"
cursor.execute(total_count_statement, (start_date, end_date))
total_count = cursor.fetchone()['total']
else:
query_statement = "SELECT usertb.name, arptb.* " \
"FROM `article_publish` AS arptb INNER JOIN `user_info` AS usertb " \
"ON arptb.author_id=usertb.id AND arptb.author_id=%s AND (arptb.custom_time BETWEEN %s AND %s) " \
"ORDER BY arptb.custom_time DESC " \
"LIMIT %s,%s;"
cursor.execute(query_statement,
(userid, start_date, end_date, start_id, page_size))
query_result = cursor.fetchall()
total_count_statement = "SELECT COUNT(`id`) AS `total` FROM `article_publish` " \
"WHERE `author_id`=%s AND `custom_time` BETWEEN %s AND %s;"
cursor.execute(total_count_statement,
(userid, start_date, end_date))
total_count = cursor.fetchone()['total']
total_page = int((total_count + page_size - 1) / page_size)
db_connection.close()
response_data = dict()
records = list()
for record_item in query_result:
record_item['custom_time'] = record_item['custom_time'].strftime(
'%Y-%m-%d')
records.append(record_item)
response_data['records'] = records
response_data['table_headers'] = table_headers
response_data['header_keys'] = header_keys
response_data['current_page'] = current_page + 1 # 查询前给减1处理了，加回来
response_data['total_page'] = total_page
response_data['total_count'] = total_count
return jsonify(response_data)
def post(self):
json_body = request.json
imgcid = json_body.get('image_code_id', '')
machine_code = json_body.get('machine_code', None)
client = get_client(machine_code)
if not client:
return jsonify({'message': 'INVALID CLIENT,无法注册!'})
role_num = 5
if client['is_manager'] == 1:
role_num = 4
username = json_body.get('username', None)
password = json_body.get('password', None)
phone = json_body.get('phone', None)
email = json_body.get('email', '')
image_code = json_body.get('imgcode', None)
agent = request.headers.get('User-Agent', '')
user_origin = ''
if agent.startswith('Delivery'):
user_origin = 'delivery'
if not all([username, password, phone, image_code]):
return jsonify({'message': '请提交完整数据.'})
if not re.match(r'^[1][3-9][0-9]{9}$', phone): # 手机号验证
return jsonify({"message": "手机号有误!"})
redis_connection = RedisConnection()
real_imgcode = redis_connection.get_value('imgcid_%s' %
imgcid) # 取出验证码
if not real_imgcode or image_code.lower() != real_imgcode.lower():
return jsonify({"message": "验证码错误!"})
password = hash_user_password(password)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
save_statement = "INSERT INTO `info_user`(`username`,`password`,`phone`,`email`,`role_num`,`origin`)" \
"VALUES (%s,%s,%s,%s,%s,%s);"
cursor.execute(
save_statement,
(username, password, phone, email, role_num, user_origin))
# 写入第三方表(记录用户可登录的客户端表)
new_user_id = db_connection.insert_id()
client_id = int(client['id'])
expire_time = datetime.datetime.strptime("3000-01-01", "%Y-%m-%d")
uc_save_statement = "INSERT INTO `link_user_client`(`user_id`,`client_id`,`expire_time`)" \
"VALUES (%s,%s,%s);"
cursor.execute(uc_save_statement,
(new_user_id, client_id, expire_time))
db_connection.commit()
except Exception as e:
current_app.logger.error("用户注册错误:{}".format(e))
db_connection.rollback() # 事务回滚
db_connection.close()
return jsonify({"message": "注册失败%s" % str(e)}), 400
else:
return jsonify({"message": "注册成功"}), 201
