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 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, 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 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):
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 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 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 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 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, 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_form = request.form
utoken = body_form.get('utoken')
user_info = verify_json_web_token(utoken)
if user_info['role_num'] > 2:
return jsonify({"message": "登录已过期或不能进行这个操作."}), 400
ad_title = body_form.get('ad_title')
ad_file = request.files.get('ad_file', None)
ad_image = request.files.get('ad_image', None)
if not all([ad_title, ad_file, ad_image]):
return jsonify({"message": "参数不足!"}), 400
# hash文件名
ad_filename = ad_file.filename
ad_imagename = ad_image.filename
adfile_hash_name = hash_filename(ad_filename)
adimage_hash_name = adfile_hash_name.rsplit('.', 1)[0] + '.' + ad_imagename.rsplit('.', 1)[1]
ad_folder = os.path.join(BASE_DIR, "fileStorage/homepage/ad/")
if not os.path.exists(ad_folder):
os.makedirs(ad_folder)
adfile_path = os.path.join(ad_folder + adfile_hash_name)
adimage_path = os.path.join(ad_folder + adimage_hash_name)
adfile_save_url = "homepage/ad/" + adfile_hash_name
adimage_save_url = "homepage/ad/" + adimage_hash_name
ad_file.save(adfile_path)
ad_image.save(adimage_path)
today = datetime.datetime.today()
# 保存到数据库
save_ad_statement = "INSERT INTO `info_advertisement` (`create_time`,`title`,`file_url`,`image_url`) " \
"VALUES (%s,%s,%s,%s);"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
cursor.execute(save_ad_statement, (today, ad_title, adfile_save_url, adimage_save_url))
db_connection.commit()
except Exception as e:
current_app.logger.error("保存广告错误:{}".format(e)), 400
db_connection.rollback()
db_connection.close()
os.remove(adfile_path)
os.remove(adimage_path)
return jsonify({"message": "新建广告失败!"}), 400
else:
db_connection.close()
return jsonify({"message": "新建广告成功!"}), 201
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.RESEARCH:
return jsonify({"message": "登录已过期或不能进行操作。"}), 400
title = body_form.get('title', None)
investment_file = request.files.get('invest_file')
if not all([title, investment_file]):
return jsonify({"message": "参数错误!"}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
invest_filename = investment_file.filename
file_hash_name = hash_filename(invest_filename)
today = datetime.datetime.today()
year = today.year
month = "%.2d" % today.month
day = "%.2d" % today.day
file_folder = os.path.join(
BASE_DIR,
"fileStorage/pserver/investmentplan/{}/{}/".format(year, month))
if not os.path.exists(file_folder):
os.makedirs(file_folder)
prefix_filename = "{}_".format(day)
file_path = os.path.join(file_folder, prefix_filename + file_hash_name)
file_save_url = "pserver/investmentplan/{}/{}/".format(
year, month) + prefix_filename + file_hash_name
investment_file.save(file_path)
save_statement = "INSERT INTO `info_investmentplan` (`title`,`file_url`,`author_id`) " \
"VALUES (%s,%s,%s);"
try:
user_id = int(user_info['id'])
cursor.execute(save_statement, (title, file_save_url, 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):
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
variety_name = body_json.get('variety_name', None)
variety_name_en = body_json.get('variety_name_en', None)
exchange_num = body_json.get('exchange_num', 0)
parent_id = body_json.get('parent_num', 0)
if not variety_name:
return jsonify("参数错误! NOT FOUND NAME."), 400
# 保存
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
if not parent_id:
insert_statement = "INSERT INTO `info_variety` (`name`) VALUES (%s);"
cursor.execute(insert_statement, variety_name)
else:
parent_id = int(parent_id)
exchange_num = int(exchange_num)
variety_name_en = variety_name_en.upper()
insert_statement = "INSERT INTO `info_variety` (`name`,`name_en`,`parent_id`,`exchange`) VALUES (%s,%s,%s,%s);"
cursor.execute(
insert_statement,
(variety_name, variety_name_en, parent_id, exchange_num))
new_vid = db_connection.insert_id()
cursor.execute(
"UPDATE `info_variety` SET `sort`=%d WHERE `id`=%d;" %
(new_vid, new_vid))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("增加品种错误:{}".format(e))
return jsonify({"message": "增加品种错误."}), 400
else:
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()
try:
user_id = int(user_info['uid'])
delete_statement = "DELETE FROM `investrategy` " \
"WHERE `id`=%d AND `author_id`=%d;" % (rid, user_id)
cursor = db_connection.get_cursor()
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()
return jsonify("删除成功^.^!")
def delete(self, uid, fid):
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()
delete_statement = "DELETE FROM `info_finance` " \
"WHERE `id`=%d AND `author_id`=%d AND DATEDIFF(NOW(),`create_time`) < 3;" % (fid, 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()
return jsonify({"message": "删除成功!"})
def delete(self, tid):
"""删除数据表"""
utoken = request.args.get('utoken')
user_info = verify_json_web_token(utoken)
if not user_info:
return jsonify({"message": "登录已过期.."}), 400
user_id = user_info['id']
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 先找到原数据表
select_statement = "SELECT `id`,`sql_table` FROM `info_trend_table` WHERE `id`=%s AND `author_id`=%s;"
cursor.execute(select_statement, (tid, user_id))
fetch_one = cursor.fetchone()
if not fetch_one:
db_connection.close()
return jsonify({"message": "删除他人数据就是谋财害命..."}), 400
else:
db_connection.begin()
try:
sql_table = fetch_one['sql_table']
table_id = fetch_one['id']
# 删除根据此表数据画的图
delete_relate_chart = "DELETE FROM `info_trend_chart` " \
"WHERE `table_id`=%s;"
cursor.execute(delete_relate_chart, table_id)
# 删除表
drop_statement = "DROP TABLE %s;" % sql_table
# 删除表记录
delete_statement = "DELETE FROM `info_trend_table` " \
"WHERE `id`=%s AND `author_id`=%s;"
cursor.execute(delete_statement, (tid, user_id))
cursor.execute(drop_statement)
db_connection.commit()
except Exception:
db_connection.rollback()
db_connection.close()
return jsonify({"message": "删除出错了..."}), 400
else:
db_connection.close()
return jsonify({"message": "这张表已烟消云散..."})
def post(self):
body_data = request.form
author_id = body_data.get('author_id', None)
if not author_id:
return jsonify("参数错误，HAS NO AUTHORID.")
# 查找用户
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
select_user_statement = "SELECT `id`,`name`,`is_admin` FROM `user_info` WHERE `id`=%s AND `is_active`=1;"
cursor.execute(select_user_statement, author_id)
user_obj = cursor.fetchone()
if not user_obj:
return jsonify("系统没有查到您的信息,无法操作."), 400
if user_obj['is_admin']:
return jsonify('请不要使用用管理员用户添加记录.')
# 不为空的信息判断
title = body_data.get('title', False)
variety = body_data.get('variety', False)
direction = body_data.get('direction', False)
if not title or not variety or not direction:
return jsonify("参数错误,NOT FOUND TITLE,VARIETY,DIRECTION."), 400
# 组织信息
write_time = body_data.get('write_time')
author_id = user_obj['id']
contract = body_data.get('contract','')
build_time = body_data.get('build_date_time')
build_price = body_data.get('build_price')
build_hands = body_data.get('build_hands')
out_price = body_data.get('out_price')
cutloss_price = body_data.get('cutloss_price')
expire_time = body_data.get('expire_time')
is_publish = 1 if body_data.get('is_publish', False) else 0
profit = body_data.get('profit')
level = body_data.get('level', '')
note = body_data.get('note', '')
# 读取文件
annex_file = request.files.get('annex_file', None)
if not annex_file:
filename = ''
annex_url = ''
file_path = ''
else:
# 文件名hash
filename = annex_file.filename
hash_name = hash_file_name(filename)
# 获取保存的位置
file_path = os.path.join(BASE_DIR, "fileStore/investment/" + hash_name)
annex_url = "fileStore/investment/" + hash_name # 数据库路径
annex_file.save(file_path)
# 存入数据库
save_invest_statement = "INSERT INTO `investment`" \
"(`custom_time`,`author_id`,`title`,`variety_id`,`contract`,`direction`,`build_time`," \
"`build_price`,`build_hands`,`out_price`,`cutloss_price`,`expire_time`,`is_publish`,`profit`,`annex`,`annex_url`,`note`,`level`)" \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
try:
# 转换类型
custom_time = datetime.datetime.strptime(write_time, '%Y-%m-%d') if write_time else datetime.datetime.now()
build_time = datetime.datetime.strptime(build_time,'%Y-%m-%dT%H:%M') if build_time else datetime.datetime.now()
expire_time = datetime.datetime.strptime(expire_time,'%Y-%m-%dT%H:%M') if expire_time else datetime.datetime.now()
variety_id = int(variety)
build_price = float(build_price) if build_price else 0
build_hands = int(build_hands) if build_hands else 0
out_price = float(out_price) if out_price else 0
cutloss_price = float(cutloss_price) if cutloss_price else 0
profit = float(profit) if profit else 0
cursor.execute(save_invest_statement,
(custom_time, author_id, title, variety_id,contract, direction, build_time,
build_price, build_hands, out_price, cutloss_price, expire_time, is_publish, profit, filename,annex_url,note, level)
)
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("写入投在方案记录错误:" + str(e))
# 保存错误得删除已保存的文件
if file_path and os.path.exists(file_path):
os.remove(file_path)
return jsonify("参数错误!无法保存。"), 400
else:
db_connection.close()
return jsonify("保存成功!"), 201
def post(self):
body_data = request.form
author_id = body_data.get('author_id', None)
if not author_id:
return jsonify("参数错误，HAS NO AUTHORID.")
# 查找用户
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
select_user_statement = "SELECT `id`,`name`,`is_admin` FROM `user_info` WHERE `id`=%s AND `is_active`=1;"
cursor.execute(select_user_statement, author_id)
user_obj = cursor.fetchone()
if not user_obj:
return jsonify("系统没有查到您的信息,无法操作."), 400
if user_obj['is_admin']:
return jsonify('请不要使用用管理员用户添加记录.')
# 不为空的信息判断
title = body_data.get('title', False)
if not title:
return jsonify("参数错误,NOT FOUND TITLE"), 400
# 组织信息
upload_time = body_data.get('upload_time')
upload_time = datetime.datetime.strptime(
upload_time,
'%Y-%m-%d') if upload_time else datetime.datetime.now()
author_id = user_obj['id']
words = body_data.get('words', 0)
is_publish = body_data.get('is_publish', False)
level = body_data.get('level', 'C')
score = body_data.get('score', 0)
note = body_data.get('work_note', '')
partner = body_data.get('partner_name', '')
# 读取文件
annex_file = request.files.get('annex_file', None)
if not annex_file:
filename = ''
annex_url = ''
file_path = ''
else:
# 文件名hash
filename = annex_file.filename
hash_name = hash_file_name(filename)
# 获取保存的位置
file_path = os.path.join(BASE_DIR,
"fileStore/monographic/" + hash_name)
annex_url = "fileStore/monographic/" + hash_name # 数据库路径
annex_file.save(file_path)
# 存入数据库
save_work_statement = "INSERT INTO `monographic`" \
"(`custom_time`,`author_id`,`title`,`words`,`is_publish`,`level`," \
"`score`,`note`,`partner`,`annex`,`annex_url`)" \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
try:
# 转换类型
words = int(words) if words else 0
score = int(score) if score else 0
is_publish = 1 if is_publish else 0
cursor.execute(save_work_statement,
(upload_time, author_id, title, words, is_publish,
level, score, note, partner, filename, annex_url))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("写入专题研究记录错误:" + str(e))
# 保存错误得删除已保存的文件
if file_path and os.path.exists(file_path):
os.remove(file_path)
return jsonify("参数错误!无法保存。"), 400
else:
db_connection.close()
return jsonify("保存成功!"), 201
def put(self, rid):
body_data = request.form
utoken = body_data.get('utoken')
user_info = verify_json_web_token(utoken)
user_id = user_info['uid']
# 不为空的信息判断
title = body_data.get('title', False)
if not title:
return jsonify("参数错误,NOT FOUND TITLE."), 400
# 组织信息
custom_time = body_data.get('custom_time')
media_name = body_data.get('media_name', '')
rough_type = body_data.get('rough_type', '')
words = body_data.get('words', 0)
checker = body_data.get('checker', '')
allowance = body_data.get('allowance', 0)
partner = body_data.get('partner', '')
note = body_data.get('note', '')
filename = body_data.get('annex', '')
annex_url = body_data.get('annex_url', '')
old_annex_url = annex_url
annex_file = request.files.get('annex_file', None)
file_path = ''
if annex_file:
filename = annex_file.filename
hash_name = hash_file_name(filename)
file_path = os.path.join(BASE_DIR,
"fileStore/artpublish/" + hash_name)
annex_url = "fileStore/artpublish/" + hash_name # 数据库路径
annex_file.save(file_path)
# 更新数据库
update_statement = "UPDATE `article_publish` SET " \
"`custom_time`=%s,`title`=%s,`media_name`=%s,`rough_type`=%s,`words`=%s,`checker`=%s," \
"`allowance`=%s,`partner`=%s,`note`=%s,`annex`=%s,`annex_url`=%s " \
"WHERE `id`=%s AND `author_id`=%s;"
db_connection = MySQLConnection()
try:
# 转换类型
user_id = int(user_id)
custom_time = datetime.datetime.strptime(
custom_time,
'%Y-%m-%d') if custom_time else datetime.datetime.now()
words = int(words) if words else 0
allowance = int(allowance) if allowance else 0
cursor = db_connection.get_cursor()
cursor.execute(
update_statement,
(custom_time, title, media_name, rough_type, words, checker,
allowance, partner, note, filename, annex_url, rid, user_id))
db_connection.commit()
old_file_path = os.path.join(BASE_DIR, old_annex_url)
if annex_file and os.path.isfile(old_file_path):
os.remove(old_file_path)
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("修改文章审核记录错误:" + str(e))
if file_path and os.path.exists(file_path):
os.remove(file_path)
return jsonify("参数错误!无法修改。"), 400
else:
db_connection.close()
return jsonify("修改成功!")
def put(self, rid):
body_data = request.form
utoken = body_data.get('utoken')
user_info = verify_json_web_token(utoken)
user_id = user_info['uid']
# 不为空的信息判断
title = body_data.get('title', False)
if not title:
return jsonify("参数错误,NOT FOUND TITLE"), 400
# 组织信息
custom_time = body_data.get('custom_time')
words = body_data.get('words', 0)
is_publish = body_data.get('is_publish', False)
level = body_data.get('level', 'D')
score = body_data.get('score', 0)
note = body_data.get('note', '')
partner = body_data.get('partner_name', '')
filename = body_data.get('annex', '')
annex_url = body_data.get('annex_url', '')
old_annex_url = annex_url
annex_file = request.files.get('annex_file', None)
file_path = ''
if annex_file:
filename = annex_file.filename
hash_name = hash_file_name(filename)
file_path = os.path.join(BASE_DIR,
"fileStore/monographic/" + hash_name)
annex_url = "fileStore/monographic/" + hash_name # 数据库路径
annex_file.save(file_path)
update_statement = "UPDATE `monographic` SET " \
"`custom_time`=%s,`title`=%s,`words`=%s,`is_publish`=%s,`level`=%s," \
"`score`=%s,`note`=%s,`partner`=%s,`annex`=%s,`annex_url`=%s " \
"WHERE `id`=%s AND `author_id`=%s;"
db_connection = MySQLConnection()
try:
# 转换类型
custom_time = datetime.datetime.strptime(custom_time, '%Y-%m-%d')
words = int(words) if words else 0
score = int(score) if score else 0
is_publish = 1 if is_publish else 0
cursor = db_connection.get_cursor()
cursor.execute(
update_statement,
(custom_time, title, words, is_publish, level, score, note,
partner, filename, annex_url, rid, user_id))
db_connection.commit()
old_file_path = os.path.join(BASE_DIR, old_annex_url)
if annex_file and os.path.isfile(old_file_path): # 有新文件才能删除旧文件
os.remove(old_file_path)
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("修改专题研究记录错误:" + str(e))
# 保存错误得删除已保存的文件
if file_path and os.path.isfile(file_path):
os.remove(file_path)
return jsonify("参数错误!无法保存。")
else:
db_connection.close()
return jsonify("修改成功!")
def post(self):
body_data = request.form
author_id = body_data.get('author_id', None)
if not author_id:
return jsonify("参数错误，HAS NO AUTHORID.")
# 查找用户
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
select_user_statement = "SELECT `id`,`name`,`is_admin` FROM `user_info` WHERE `id`=%s AND `is_active`=1;"
cursor.execute(select_user_statement, author_id)
user_obj = cursor.fetchone()
if not user_obj:
return jsonify("系统没有查到您的信息,无法操作."), 400
if user_obj['is_admin']:
return jsonify('请不要使用用管理员用户添加记录.')
# 不为空的信息判断
title = body_data.get('title', False)
if not title:
return jsonify("参数错误,NOT FOUND TITLE."), 400
# 组织信息
custom_time = body_data.get('custom_time')
custom_time = datetime.datetime.strptime(
custom_time,
'%Y-%m-%d') if custom_time else datetime.datetime.now()
author_id = user_obj['id']
media_name = body_data.get('media_name', '')
rough_type = body_data.get('rough_type', '')
words = body_data.get('words', 0)
checker = body_data.get('checker', '')
allowance = body_data.get('allowance', 0)
partner = body_data.get('partner', '')
note = body_data.get('note', '')
# 读取文件
annex_file = request.files.get('annex_file', None)
if not annex_file:
filename = ''
annex_url = ''
file_path = ''
else:
# 文件名hash
filename = annex_file.filename
hash_name = hash_file_name(filename)
# 获取保存的位置
file_path = os.path.join(BASE_DIR,
"fileStore/artpublish/" + hash_name)
annex_url = "fileStore/artpublish/" + hash_name # 数据库路径
annex_file.save(file_path)
# 存入数据库
save_invest_statement = "INSERT INTO `article_publish`" \
"(`custom_time`,`author_id`,`title`,`media_name`,`rough_type`,`words`,`checker`," \
"`allowance`,`partner`,`note`,`annex`,`annex_url`)" \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
try:
# 转换类型
words = int(words) if words else 0
allowance = int(allowance) if allowance else 0
cursor.execute(
save_invest_statement,
(custom_time, author_id, title, media_name, rough_type, words,
checker, allowance, partner, note, filename, annex_url))
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("写入文章审核记录错误:" + str(e))
# 保存错误得删除已保存的文件
if file_path and os.path.exists(file_path):
os.remove(file_path)
return jsonify("参数错误!无法保存。"), 400
else:
db_connection.close()
return jsonify("保存成功!"), 201
def post(self):
body_data = request.json
author_id = body_data.get('author_id', None)
if not author_id:
return jsonify("参数错误，HAS NO AUTHORID.")
# 查找用户
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
select_user_statement = "SELECT `id`,`name`,`is_admin` FROM `user_info` WHERE `id`=%s AND `is_active`=1;"
cursor.execute(select_user_statement, author_id)
user_obj = cursor.fetchone()
if not user_obj:
return jsonify("系统没有查到您的信息,无法操作."), 400
if user_obj['is_admin']:
return jsonify('请不要使用用管理员用户添加记录.')
# 不为空的信息判断
content = body_data.get('content', False)
variety = body_data.get('variety', False)
direction = body_data.get('direction', False)
if not content or not variety or not direction:
return jsonify("参数错误,NOT FOUND CONTENT,VARIETY,DIRECTION."), 400
# 组织信息
custom_time = body_data.get('custom_time')
custom_time = datetime.datetime.strptime(
custom_time,
'%Y-%m-%d') if custom_time else datetime.datetime.now()
author_id = user_obj['id']
contract = body_data.get('contract', '')
hands = body_data.get('hands', 0)
open_position = body_data.get('open_position', 0)
close_position = body_data.get('close_position', 0)
profit = body_data.get('profit')
note = body_data.get('work_note', '')
# 存入数据库
save_invest_statement = "INSERT INTO `investrategy`" \
"(`custom_time`,`author_id`,`content`,`variety_id`,`contract`,`direction`,`hands`," \
"`open_position`,`close_position`,`profit`)" \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
try:
# 转换类型
variety_id = int(variety)
hands = int(hands) if hands else 0
open_position = int(open_position) if open_position else 0
close_position = int(close_position) if close_position else 0
profit = float(profit) if profit else 0
cursor.execute(
save_invest_statement,
(custom_time, author_id, content, variety_id, contract,
direction, hands, open_position, close_position, profit))
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 post(self):
body_data = request.form
worker_id = body_data.get('worker_id', None)
if not worker_id:
return jsonify("参数错误，HAS NO WORKERID.")
# 查找用户
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
select_user_statement = "SELECT `id`,`name`,`is_admin` FROM `user_info` WHERE `id`=%s;"
cursor.execute(select_user_statement, worker_id)
user_obj = cursor.fetchone()
# 管理员不给添加信息
if user_obj['is_admin']:
return jsonify('请不要使用用管理员用户添加记录.')
# 不为空的信息判断
task_type = body_data.get('task_type', 0)
task_type_text = ABNORMAL_WORK.get(int(task_type), 0)
title = body_data.get('work_title', False)
if not task_type_text or not title:
return jsonify("参数错误,NOT FOUND TASKTYPE AND TITLE"), 400
# 组织信息
custom_time = datetime.datetime.strptime(body_data.get('work_date'), '%Y-%m-%d')
worker = user_obj['id']
sponsor = body_data.get('sponsor', '')
applied_org = body_data.get('applicat_org', '')
applicant = body_data.get('application_person', '')
tel_number = body_data.get('link_number', '')
swiss_coin = body_data.get('ruibi_count', 0)
allowance = body_data.get('income_allowance', 0)
note = body_data.get('work_note', '')
partner = body_data.get('partner_name', '')
# 读取文件
annex_file = request.files.get('annex_file', None)
if not annex_file:
filename = ''
annex_url = ''
file_path = ''
else:
# 文件名hash
filename = annex_file.filename
hash_name = hash_file_name(filename)
# 获取保存的位置
file_path = os.path.join(BASE_DIR, "fileStore/abwork/" + hash_name)
annex_url = "fileStore/abwork/" + hash_name # 数据库路径
annex_file.save(file_path)
# 存入数据库
save_work_statement = "INSERT INTO `abnormal_work`" \
"(`custom_time`,`author_id`,`task_type`,`title`,`sponsor`,`applied_org`," \
"`applicant`,`tel_number`,`swiss_coin`,`allowance`,`note`,`partner`,`annex`,`annex_url`)" \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
try:
swiss_coin = int(swiss_coin) if swiss_coin else 0
allowance = float(allowance) if allowance else 0
cursor.execute(save_work_statement,
(custom_time, worker, task_type, title, sponsor, applied_org,
applicant, tel_number, swiss_coin, allowance, note, partner, filename, annex_url)
)
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("写入非常态工作错误:" + str(e))
# 保存错误得删除已保存的文件
if file_path and os.path.exists(file_path):
os.remove(file_path)
return jsonify("参数错误!无法保存。"), 400
else:
db_connection.close()
return jsonify("保存成功!"), 201
def put(self, work_id):
body_data = request.form
utoken = body_data.get('utoken')
user_info = verify_json_web_token(utoken)
user_id = user_info['uid']
# 不为空的信息判断
task_type = body_data.get('task_type', 0)
task_type_text = ABNORMAL_WORK.get(int(task_type), 0)
title = body_data.get('title', False)
if not task_type_text or not title:
return jsonify("参数错误,NOT FOUND TASKTYPE AND TITLE"), 400
# 组织信息
custom_time = datetime.datetime.strptime(body_data.get('custom_time'), '%Y-%m-%d')
task_type = body_data.get('task_type', 0)
sponsor = body_data.get('sponsor', '')
applied_org = body_data.get('applied_org', '')
applicant = body_data.get('applicant', '')
tel_number = body_data.get('tel_number', '')
swiss_coin = body_data.get('swiss_coin', 0)
allowance = body_data.get('allowance', 0)
note = body_data.get('note', '')
partner = body_data.get('partner_name', '')
filename = body_data.get('annex','')
annex_url = body_data.get('annex_url','')
old_annex_url = annex_url # 保存旧文件路径待删除文件
# 读取文件
annex_file = request.files.get('annex_file', None)
file_path = ''
if annex_file:
filename = annex_file.filename
hash_name = hash_file_name(filename)
# 获取保存的位置
file_path = os.path.join(BASE_DIR, "fileStore/abwork/" + hash_name)
annex_url = "fileStore/abwork/" + hash_name # 数据库路径
annex_file.save(file_path)
# 存入数据库
update_statement = "UPDATE `abnormal_work` SET " \
"`custom_time`=%s,`task_type`=%s,`title`=%s,`sponsor`=%s,`applied_org`=%s," \
"`applicant`=%s,`tel_number`=%s,`swiss_coin`=%s,`allowance`=%s,`note`=%s,`partner`=%s," \
"`annex`=%s,`annex_url`=%s " \
"WHERE `id`=%s AND `author_id`=%s;"
db_connection = MySQLConnection()
try:
swiss_coin = int(swiss_coin) if swiss_coin else 0
allowance = float(allowance) if allowance else 0
cursor = db_connection.get_cursor()
cursor.execute(update_statement,
(custom_time, task_type, title, sponsor, applied_org,
applicant, tel_number, swiss_coin, allowance, note, partner,
filename,annex_url,
work_id, user_id)
)
db_connection.commit()
# 删除原来的文件
old_file_path = os.path.join(BASE_DIR, old_annex_url)
if annex_file and os.path.isfile(old_file_path):
os.remove(old_file_path)
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("修改非常态工作记录错误:" + str(e))
if file_path and os.path.exists(file_path):
os.remove(file_path)
return jsonify("参数错误!无法修改。"), 400
else:
db_connection.close()
return jsonify("修改成功!")
def put(self, rid):
body_data = request.form
utoken = body_data.get('utoken')
user_info = verify_json_web_token(utoken)
user_id = user_info['uid']
# 不为空的信息判断
title = body_data.get('title', False)
variety_id = body_data.get('variety_id', False)
direction = body_data.get('direction', False)
if not title or not variety_id or not direction:
return jsonify("参数错误,NOT FOUND TITLE,VARIETY,DIRECTION."), 400
# 组织信息
write_time = body_data.get('custom_time')
contract = body_data.get('contract', '')
build_time = body_data.get('build_time')
build_price = body_data.get('build_price')
build_hands = body_data.get('build_hands')
out_price = body_data.get('out_price')
cutloss_price = body_data.get('cutloss_price')
expire_time = body_data.get('expire_time')
is_publish = 1 if body_data.get('is_publish', False) else 0
profit = body_data.get('profit')
note = body_data.get('note', '')
level = body_data.get('level', '')
filename=body_data.get('annex', '')
annex_url = body_data.get('annex_url', '')
old_annex_url = annex_url
annex_file = request.files.get('annex_file', None)
file_path = ''
if annex_file:
filename = annex_file.filename
hash_name = hash_file_name(filename)
file_path = os.path.join(BASE_DIR, "fileStore/investment/" + hash_name)
annex_url = "fileStore/investment/" + hash_name # 数据库路径
annex_file.save(file_path)
# 存入数据库
update_statement = "UPDATE `investment` SET " \
"`custom_time`=%s,`title`=%s,`variety_id`=%s,`contract`=%s,`direction`=%s,`build_time`=%s," \
"`build_price`=%s,`build_hands`=%s,`out_price`=%s,`cutloss_price`=%s,`expire_time`=%s," \
"`is_publish`=%s,`profit`=%s,`annex`=%s,`annex_url`=%s,`note`=%s,`level`=%s " \
"WHERE `id`=%s AND `author_id`=%s;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
try:
# 转换类型
variety_id = int(variety_id)
build_price = float(build_price) if build_price else 0
build_hands = int(build_hands) if build_hands else 0
out_price = float(out_price) if out_price else 0
cutloss_price = float(cutloss_price) if cutloss_price else 0
profit = float(profit) if profit else 0
expire_time_format = build_time_format = '%Y-%m-%dT%H:%M:%S'
if len(build_time) < 19:
build_time_format = '%Y-%m-%dT%H:%M'
if len(expire_time) < 19:
expire_time_format = '%Y-%m-%dT%H:%M'
custom_time = datetime.datetime.strptime(write_time, '%Y-%m-%d') if write_time else datetime.datetime.now()
build_time = datetime.datetime.strptime(build_time,build_time_format) if build_time else datetime.datetime.now()
expire_time = datetime.datetime.strptime(expire_time,expire_time_format) if expire_time else datetime.datetime.now()
cursor.execute(update_statement,
(custom_time, title, variety_id, contract, direction, build_time,
build_price, build_hands, out_price, cutloss_price, expire_time, is_publish, profit,filename, annex_url,note,level,
rid, user_id)
)
db_connection.commit()
old_file_path = os.path.join(BASE_DIR, old_annex_url)
if annex_file and os.path.isfile(old_file_path):
os.remove(old_file_path)
except Exception as e:
db_connection.rollback()
db_connection.close()
current_app.logger.error("更新投资方案记录错误:" + str(e))
if file_path and os.path.exists(file_path):
os.remove(file_path)
return jsonify("参数错误!无法更新。"), 400
else:
db_connection.close()
return jsonify("更新成功!"), 201
def create_new_table(self, user_id, variety_id, group_id, title, title_md5,
suffix_index, sql_table_name, table_values, origin):
"""
新建一张数据表
:param user_id: 创建表的用户ID
:param variety_id: 表相关的品种ID
:param group_id: 表所属数据组的ID
:param title: 表的名称
:param title_md5: 表名称的MD5
:param suffix_index: 表的后缀索引
:param sql_table_name: 实际数据表的sql名
:param table_values: 实际数据
:param origin: 备注，数据来源
:return:
"""
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
table_headers = table_values.pop(0) # 取出第一行为表头
free_row = table_values.pop(0) # 再取出第一行为自由行(即上传表中的第三行数据)
# 取得table_values[0]列中的最大值和最小值
table_values_df = pd.DataFrame(table_values)
if table_values_df.empty:
current_app.logger.error("user_id={}保存表:{}为空。".format(
user_id, title))
return jsonify({"message": "待保存数据为空"}), 400
table_values_df[0] = pd.to_datetime(table_values_df[0],
format='%Y-%m-%d') # 转为时间格式
table_values_df.drop_duplicates(subset=[0], keep='last',
inplace=True) # 去除日期完全一致的数据行
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[0] = table_values_df[0].apply(
lambda x: x.strftime('%Y-%m-%d'))
# print('数据中最小时间:', min_date)
# print('数据中最大时间:', max_date)
# 插入自由行到数据中
table_values_df.loc[-1] = free_row
table_values_df.index = table_values_df.index + 1
table_values_df.sort_index(inplace=True)
# 插入表头到数据中
table_values_df.loc[-1] = table_headers
table_values_df.index = table_values_df.index + 1
table_values_df.sort_index(inplace=True)
table_values_df.columns = table_headers
table_values = table_values_df.values.tolist() # 转为列表
# 根据表头生成sql需要的语句片段
sqls_segment = self.generate_sql_segment(table_headers)
create_col = sqls_segment['create']
add_col = sqls_segment['add']
values_col = sqls_segment['values']
db_connection.begin() # 开启事务
try:
# 增加数据表的信息表
insert_statement = "INSERT INTO `info_trend_table` (`title`,`title_md5`,`suffix_index`,`sql_table`,`group_id`," \
"`variety_id`,`author_id`,`updater_id`,`origin`,`min_date`,`max_date`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
cursor.execute(
insert_statement,
(title, title_md5, suffix_index, sql_table_name, group_id,
variety_id, user_id, user_id, origin, min_date, max_date))
# 创建实际数据表
create_table_statement = "CREATE TABLE IF NOT EXISTS %s (" \
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT," \
"`create_time` DATETIME NOT NULL DEFAULT NOW()," \
"`update_time` DATETIME NOT NULL DEFAULT NOW()," \
"%s);" % (sql_table_name, create_col)
cursor.execute(create_table_statement)
# 在新建表中加入数据
insert_data_statement = "INSERT INTO %s (%s) " \
"VALUES (%s);" % (sql_table_name, add_col, values_col)
cursor.executemany(insert_data_statement, table_values)
db_connection.commit()
except Exception as e:
db_connection.rollback()
db_connection.close()
return jsonify({"message": str(e)}), 400
else:
db_connection.close()
return jsonify({"message": "添加数据表成功!"}), 201
