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 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 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 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 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 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 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 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 delete(self, cid):
utoken = request.args.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info or int(user_info['role_num']) > 4:
return jsonify({"message": "登录过期或不能进行这样的操作。"}), 400
# 查询配置文件
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
select_option_file = "SELECT `id`,`options_file` FROM `info_trend_echart` WHERE `id`=%s AND `author_id`=%s;"
cursor.execute(select_option_file, (cid, user_info['id']))
fetch_one = cursor.fetchone()
if not fetch_one:
db_connection.close()
return jsonify({'message': '系统中没有此图形信息。'}), 400
options_file_path = fetch_one['options_file']
options_file_path = os.path.join(BASE_DIR, options_file_path)
try:
delete_statement = "DELETE FROM `info_trend_echart` WHERE `id`=%s AND `author_id`=%s;"
cursor.execute(delete_statement, (cid, user_info['id']))
db_connection.commit()
except Exception as e:
db_connection.close()
current_app.logger.error("id={}用户删除图形失败:{}".format(
user_info['id'], e))
return jsonify({'message': '删除失败!'}), 400
else:
db_connection.close()
if os.path.exists(options_file_path):
os.remove(options_file_path)
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
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 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 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 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
def post(self):
body_json = request.json
machine_code = body_json.get("machine_code", None)
client = get_client(machine_code)
if not client:
return jsonify({"message": "参数错误,登录失败。"}), 400
username = body_json.get("username", '')
phone = body_json.get("phone", '')
password = body_json.get("password", '')
# 查询数据库
select_statement = "SElECT `id`,`username`,`phone`,`avatar`,`email`,`role_num`,`note`,`password` " \
"FROM `info_user` " \
"WHERE (`username`=%s OR `phone`=%s) AND `is_active`=1;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(select_statement, (username, phone))
user_info = cursor.fetchone()
if not user_info:
db_connection.close()
return jsonify({"message": "用户名或密码错误!"}), 400
if not check_user_password(password, user_info['password']):
return jsonify({"message": "用户名或密码错误!"}), 400
now = datetime.datetime.now()
if user_info['role_num'] > 4:
# 查询是否能在此客户端登录
can_login_statement = "SELECT `user_id`,`client_id` " \
"FROM `link_user_client` " \
"WHERE `user_id`=%s AND `client_id`=%s AND `expire_time`>%s;"
cursor.execute(can_login_statement,
(user_info['id'], client['id'], now))
can_login = cursor.fetchone()
if not can_login:
db_connection.close()
return jsonify({"message": "无法在此客户端登录,联系管理员开通."}), 400
update_statement = "UPDATE `info_user` SET `update_time`=%s WHERE `id`=%s;"
cursor.execute(update_statement, (now, user_info['id'])) # 更新登录时间
db_connection.commit()
db_connection.close()
# 生成token
utoken = self.generate_json_web_token(uid=user_info['id'],
username=user_info['username'],
avatar=user_info['avatar'],
role_num=user_info['role_num'],
phone=user_info['phone'])
user_data = {
"id": user_info['id'],
"role_num": user_info['role_num'],
"username": user_info['username'],
"phone": phone[0:3] + '****' + phone[7:11],
"avatar": user_info['avatar'],
"utoken": utoken
}
return jsonify({"message": "登录成功!", "user_data": user_data})
def save_file_data(self, spot_file):
utoken = request.form.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify({"message": "登录已过期"}), 400
file_contents = xlrd.open_workbook(file_contents=spot_file.read())
table_data = file_contents.sheets()[0]
if not file_contents.sheet_loaded(0):
return jsonify({"message": "数据导入失败."}), 400
table_headers = table_data.row_values(0)
if table_headers != ["日期", "名称", "地区", "等级", "价格", "增减", "备注"]:
return jsonify({"message": "文件格式有误,请检查后再上传."}), 400
ready_to_save = list()
start_data_in = False
message = "表格数据类型有误，请检查后上传."
today = datetime.datetime.today()
user_id = int(user_info['id'])
try:
for row in range(table_data.nrows):
row_content = table_data.row_values(row)
if row_content[0] == "start":
start_data_in = True
continue
if row_content[0] == 'end':
start_data_in = False
continue
if start_data_in:
record_row = list()
#"日期", "名称","地区","等级","价格","增减","备注"
record_row.append(today)
record_row.append(
xlrd.xldate_as_datetime(row_content[0], 0))
record_row.append(row_content[1])
record_row.append(row_content[2])
record_row.append(row_content[3])
record_row.append(float(row_content[4]))
record_row.append(float(row_content[5]))
record_row.append(row_content[6])
record_row.append(user_id)
ready_to_save.append(record_row)
except Exception as e:
current_app.logger.error("批量上传现货报表数据错误:{}".format(e))
return jsonify({"message": message}), 400
insert_statement = "INSERT INTO `info_spot` " \
"(`create_time`,`custom_time`,`name`,`area`,`level`,`price`,`increase`,`note`,`author_id`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.executemany(insert_statement, ready_to_save)
db_connection.commit()
db_connection.close()
return jsonify({"message": "上传成功"}), 201
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)
variety_id = record_info.get('variety_id', False)
direction = record_info.get('direction', False)
if not content or not variety_id or not direction:
return jsonify("参数错误,NOT FOUND CONTENT,VARIETY,DIRECTION."), 400
# 组织信息
custom_time = record_info.get('custom_time')
contract = record_info.get('contract', '')
hands = record_info.get('hands', 0)
open_position = record_info.get('open_position', 0)
close_position = record_info.get('close_position', 0)
profit = record_info.get('profit')
# 存入数据库
save_invest_statement = "UPDATE `investrategy` SET " \
"`custom_time`=%s,`content`=%s,`variety_id`=%s,`contract`=%s,`direction`=%s,`hands`=%s," \
"`open_position`=%s,`close_position`=%s,`profit`=%s " \
"WHERE `id`=%s AND `author_id`=%s;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
# 转换类型
custom_time = datetime.datetime.strptime(
custom_time,
'%Y-%m-%d') if custom_time else datetime.datetime.now()
variety_id = int(variety_id)
hands = int(hands) if hands else 0
open_position = float(open_position) if open_position else 0
close_position = float(close_position) if close_position else 0
profit = float(profit) if profit else 0
cursor.execute(
save_invest_statement,
(custom_time, content, variety_id, contract, direction, hands,
open_position, close_position, profit, rid, user_id))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("更新投顾策略记录错误:" + str(e))
return jsonify("参数错误!无法修改。"), 400
else:
db_connection.close()
return jsonify("修改成功!"), 201
def put(self, tid):
"""修改id=tid的表内的指定一行数据"""
body_json = request.json
utoken = body_json.get('utoken')
user_info = verify_json_web_token(utoken)
user_id = user_info['id']
record_id = body_json.get('record_id')
record_content = body_json.get('record_content')
# 第一列的数据类型检测
try:
record_content[0] = datetime.datetime.strptime(
record_content[0], '%Y-%m-%d').strftime('%Y-%m-%d')
except Exception as e:
return jsonify({"message": "第一列时间数据格式错误!\n{}".format(e)}), 400
select_table_info = "SELECT `sql_table` " \
"FROM `info_trend_table` " \
"WHERE `is_active`=1 AND `id`=%d;" % tid
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(select_table_info)
table_info = cursor.fetchone()
table_sql_name = table_info['sql_table']
if not table_sql_name:
db_connection.close()
return jsonify({"message": "数据表不存在"}), 400
# 修改数据
col_str = ""
for col_index in range(len(record_content)):
if col_index == len(record_content) - 1:
col_str += "`column_{}`=%s".format(col_index)
else:
col_str += "`column_{}`=%s,".format(col_index)
update_statement = "UPDATE `%s` " \
"SET %s " \
"WHERE `id`=%s;" % (table_sql_name, col_str, record_id)
# print("修改的sql:\n", update_statement)
try:
today = datetime.datetime.today()
cursor.execute(update_statement, record_content)
update_table_info = "UPDATE `info_trend_table` " \
"SET `updater_id`=%s,`update_time`=%s " \
"WHERE `id`=%s;"
cursor.execute(update_table_info, (user_id, today, tid))
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": "修改记录成功!"}), 200
def post(self):
body_form = request.form
utoken = body_form.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info or user_info['role_num'] > enums.COLLECTOR:
return jsonify({"message": "登录已过期或不能进行操作。"}), 400
title = body_form.get('title', None)
notice_file = request.files.get('notice_file')
category = body_form.get('category', 0)
if not all([title, notice_file]):
return jsonify({"message": "参数错误!"}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
notice_filename = notice_file.filename
file_hash_name = hash_filename(notice_filename)
today = datetime.datetime.today()
year = today.year
month = "%.2d" % today.month
day = "%.2d" % today.day
exnotice_folder = os.path.join(
BASE_DIR,
"fileStorage/homepage/exnotice/{}/{}/".format(year, month))
if not os.path.exists(exnotice_folder):
os.makedirs(exnotice_folder)
prefix_filename = "{}_".format(day)
file_path = os.path.join(exnotice_folder,
prefix_filename + file_hash_name)
file_save_url = "homepage/exnotice/{}/{}/".format(
year, month) + prefix_filename + file_hash_name
notice_file.save(file_path)
save_statement = "INSERT INTO `info_exnotice` (`create_time`,`title`,`file_url`,`category`,`author_id`) " \
"VALUES (%s,%s,%s,%s,%s);"
try:
user_id = int(user_info['id'])
category = int(category)
if category < 0 or category > 3:
raise ValueError('分类错误!')
cursor.execute(save_statement,
(today, title, file_save_url, category, user_id))
db_connection.commit()
except Exception as e:
db_connection.rollback()
os.remove(file_path)
db_connection.close()
current_app.logger.error("上传交易通知错误:{}".format(e))
return jsonify({'message': "上传交易通知错误"}), 400
else:
db_connection.close()
return jsonify({"message": "上传交易通知成功!"}), 201
def post(self, user_id):
utoken = request.json.get('utoken')
operator_user = psd_handler.verify_json_web_token(utoken)
if not operator_user or not operator_user['is_admin']:
return jsonify("登录已过期或无权限操作!"), 400
password = request.json.get('password')
new_password = psd_handler.hash_user_password(password)
# 设置新密码
update_statement = "UPDATE `user_info` SET `password`=%s WHERE `id`=%s;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(update_statement, (new_password, user_id))
db_connection.commit()
db_connection.close()
return jsonify("重置用户密码成功!\n新密码为:123456")
def update_exist_table(self, user_id, max_date, sql_table, table_values):
"""更新一张已存在的数据表(新增数据)"""
max_date = pd.to_datetime(max_date, format='%Y-%m-%d')
table_headers = table_values.pop(0)
free_row = table_values.pop(0) # 去除自由数据行
table_values_df = pd.DataFrame(table_values)
table_values_df[0] = pd.to_datetime(table_values_df[0],
format='%Y-%m-%d')
new_max_date = (table_values_df[0].max()).strftime(
'%Y-%m-%d') # 数据的最大时间
# min_date = (table_values_df[0].min()).strftime('%Y-%m-%d')
table_values_df.drop_duplicates(subset=[0], keep='last',
inplace=True) # 去重
table_values_df = table_values_df[table_values_df[0] >
max_date] # 取日期大于max_date的
table_values_df[0] = table_values_df[0].apply(
lambda x: x.strftime('%Y-%m-%d')) # 日期再转为字符串
if table_values_df.empty:
# print(sql_table, "空增加")
return jsonify({'message': "更新成功!"})
else:
# print(sql_table, '有数据')
# 根据表头生成sql需要的语句片段
sqls_segment = self.generate_sql_segment(table_headers)
add_col = sqls_segment['add']
values_col = sqls_segment['values']
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
table_values = table_values_df.values.tolist()
insert_data_statement = "INSERT INTO %s (%s) " \
"VALUES (%s);" % (sql_table, add_col, values_col)
cursor.executemany(insert_data_statement, table_values)
# 更新数据信息
today = datetime.datetime.today()
update_info_statement = "UPDATE `info_trend_table` SET " \
"`update_time`=%s,`updater_id`=%s,`max_date`=%s " \
"WHERE `sql_table`=%s;"
cursor.execute(update_info_statement,
(today, user_id, new_max_date, sql_table))
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": "更新数据成功"})
def post(self):
body_json = request.json
utoken = body_json.get('utoken')
password = body_json.get('password')
user_info = psd_handler.verify_json_web_token(utoken)
if not user_info:
return jsonify("登录已过期，重新登录"), 400 # 重定向
user_id = user_info['uid']
new_password = psd_handler.hash_user_password(password)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
modify_statement = "UPDATE `user_info` SET `password`=%s WHERE `id`=%s;"
cursor.execute(modify_statement, (new_password, user_id))
db_connection.commit()
db_connection.close()
return jsonify("修改密码成功!请重新登录!") # 重定向
def post(self):
# 批量
# body_json = request.json
# houses = body_json.get('houses', None)
# db_connection = MySQLConnection()
# cursor = db_connection.get_cursor()
#
# insert_statement = "INSERT INTO `info_warehouse_fixed_code`(" \
# "`name`,`fixed_code`) " \
# "VALUES (%s,%s);"
# cursor.executemany(insert_statement, houses)
# db_connection.commit()
# db_connection.close()
# 增加一个
body_json = request.json
name = body_json.get('name', None)
# 将简称字符转为半角
name = strQ2B(name)
if not name:
return jsonify({'message': '简称不能为空!'}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
exist_query = "SELECT `name` FROM `info_warehouse_fixed_code` WHERE `name`=%s;"
cursor.execute(exist_query, name)
exist_ = cursor.fetchone()
if exist_:
db_connection.close()
return jsonify({'message': '该仓库已存在..'}), 400
else:
# 生成fixed_code
maxid_select = "SELECT MAX(`id`) AS `maxid` FROM `info_warehouse_fixed_code`;"
cursor.execute(maxid_select)
max_id = cursor.fetchone()['maxid']
fixed_code = '%04d' % (max_id + 1)
insert_statement = "INSERT INTO `info_warehouse_fixed_code` (`name`,`fixed_code`) " \
"VALUES (%s, %s);"
cursor.execute(insert_statement, (name, fixed_code))
db_connection.commit()
# 将仓单信息库中的对应简称的仓库编码写上
update_statement = "UPDATE `info_warehouse_receipt` SET `warehouse_code`=%s WHERE `warehouse_name`=%s;"
cursor.execute(update_statement, (fixed_code, name))
db_connection.commit()
db_connection.close()
return jsonify({'message': '保存成功!', 'fixed_code': fixed_code}), 201
def post(self, tid):
"""给一张表新增数据"""
body_json = request.json
utoken = body_json.get('utoken')
user_info = verify_json_web_token(utoken)
user_id = user_info['id']
new_contents = body_json.get('new_contents')
if len(new_contents) <= 0:
return jsonify({"message": '没有发现任何新数据..'}), 400
new_headers = body_json.get('new_header')
select_table_info = "SELECT `sql_table` " \
"FROM `info_trend_table` " \
"WHERE `is_active`=1 AND `id`=%d;" % tid
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(select_table_info)
table_info = cursor.fetchone()
table_sql_name = table_info['sql_table']
if not table_sql_name:
db_connection.close()
return jsonify({"message": "数据表不存在"}), 400
col_str = ""
content_str = ""
for col_index in range(len(new_headers)):
if col_index == len(new_headers) - 1:
col_str += "`column_{}`".format(col_index)
content_str += "%s"
else:
col_str += "`column_{}`,".format(col_index)
content_str += "%s,"
insert_statement = "INSERT INTO `%s` (%s) " \
"VALUES (%s);" % (table_sql_name, col_str, content_str)
today = datetime.datetime.today()
try:
cursor.executemany(insert_statement, new_contents)
update_info_statement = "UPDATE `info_trend_table` " \
"SET `update_time`=%s, `updater_id`=%s " \
"WHERE `id`=%s;"
cursor.execute(update_info_statement, (today, user_id, tid))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
return jsonify({'message': "失败{}".format(e)})
else:
db_connection.close()
return jsonify({"message": '添加成功'}), 201
def post(self):
body_json = request.form
utoken = body_json.get('utoken')
user_info = verify_json_web_token(utoken)
if user_info['role_num'] > 3:
return jsonify({"message": "登录已过期或不能进行这个操作."}), 400
bulletin_title = body_json.get('bulletin_title')
bulletin_file = request.files.get('bulletin_file', None)
if not all([bulletin_title, bulletin_file]):
return jsonify({"message": "参数错误!NOT FOUND NAME OR FILE."}), 400
# hash文件名
bulletin_filename = bulletin_file.filename
file_hash_name = hash_filename(bulletin_filename)
today = datetime.datetime.today()
year = today.year
month = "%.2d" % today.month
day = "%.2d" % today.day
bulletin_folder = os.path.join(
BASE_DIR,
"fileStorage/homepage/bulletin/{}/{}/".format(year, month))
if not os.path.exists(bulletin_folder):
os.makedirs(bulletin_folder)
prefix_filename = "{}_".format(day)
file_path = os.path.join(bulletin_folder,
prefix_filename + file_hash_name)
file_save_url = "homepage/bulletin/{}/{}/".format(
year, month) + prefix_filename + file_hash_name
bulletin_file.save(file_path)
# 保存到数据库
save_bulletin_statement = "INSERT INTO `info_bulletin` (`create_time`,`title`,`file_url`) " \
"VALUES (%s, %s,%s);"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
cursor.execute(save_bulletin_statement,
(today, bulletin_title, file_save_url))
db_connection.commit()
except Exception as e:
current_app.logger.error("保存公告错误:{}".format(e)), 400
db_connection.rollback()
db_connection.close()
return jsonify({"message": "上传公告失败!"}), 400
else:
db_connection.close()
return jsonify({"message": "上传公告成功!"}), 201
def post(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_name = body_json.get('variety_name', '')
variety_id = body_json.get('variety_id', None)
group_name = body_json.get('group_name', '')
group_id = body_json.get('group_id', None)
machine_code = body_json.get('machine_code')
file_folder = body_json.get('file_folder', None)
client_info = get_client(machine_code)
if not all([user_id, variety_id, group_id, client_info, file_folder]):
return jsonify({'message': '参数错误'}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
now = datetime.datetime.now()
client_id = client_info['id']
# 查找当前配置是否存在
exist_statement = "SELECT `id` FROM `info_tablesource_configs` " \
"WHERE `client_id`=%s AND `variety_id`=%s AND `group_id`=%s AND `user_id`=%s;"
cursor.execute(exist_statement,
(client_id, variety_id, group_id, user_id))
record_exist = cursor.fetchone()
if record_exist: # 更新
exist_id = record_exist['id']
update_statement = "UPDATE `info_tablesource_configs` SET " \
"`update_time`=%s,`variety_name`=%s, `variety_id`=%s,`group_name`=%s,`group_id`=%s, `client_id`=%s,`user_id`=%s,`file_folder`=%s " \
"WHERE `id`=%s;"
cursor.execute(
update_statement,
(now, variety_name, variety_id, group_name, group_id,
client_id, user_id, file_folder, exist_id))
else:
insert_statement = "INSERT INTO `info_tablesource_configs` " \
"(`update_time`,`variety_name`, `variety_id`,`group_name`,`group_id`, `client_id`,`user_id`,`file_folder`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s);" \
cursor.execute(insert_statement,
(now, variety_name, variety_id, group_name,
group_id, client_id, user_id, file_folder))
db_connection.commit()
db_connection.close()
return jsonify({'message': '配置成功!'})
def post(self, uid):
body_json = request.json
utoken = body_json.get('utoken')
operate_user = verify_json_web_token(utoken)
if not operate_user:
return jsonify({"message": "登录已过期."}), 400
if operate_user['role_num'] > 2:
return jsonify({"message": "您无法进行这个操作"}), 400
is_active = body_json.get('is_active', 0)
variety_id = body_json.get('variety_id', 0)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
operate_statement = "REPLACE INTO `link_user_variety` (`user_id`,`variety_id`, `is_active`) " \
"VALUES (%s,%s,%s);"
cursor.execute(operate_statement, (uid, variety_id, is_active))
db_connection.commit()
db_connection.close()
return jsonify({"message": "操作成功"})
