def post(self):
work_id = request.args.get('workid', 0)
user_token = request.args.get('utoken', '')
examined = request.args.get('checked', 0)
try:
work_id = int(work_id)
examined = int(examined)
if examined not in [0, 1]:
raise ValueError("参数错误")
user_info = verify_json_web_token(user_token)
if not user_info:
raise ValueError('登录过期')
if not int(user_info['is_admin']):
raise ValueError('无权限操作...')
except Exception as e:
return jsonify({"message": str(e)}), 400
# 对这个id的记录进行更新
update_statement = "UPDATE `abnormal_work` SET `is_examined`=%s WHERE id=%s;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.execute(update_statement, (examined, work_id))
db_connection.commit()
db_connection.close()
return jsonify({"message": "修改成功!"})
def patch(self, cid):
body_json = request.json
user_info = verify_json_web_token(body_json.get('utoken', None))
if not user_info or int(user_info['role_num']) > 4:
return jsonify({"message": "登录过期或不能进行这样的操作。"}), 400
decipherment = body_json.get('decipherment', '') # 解说
is_trend_show = body_json.get('is_trend_show', None) # 首页展示
is_variety_show = body_json.get('is_variety_show', None) # 品种页展示
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
update_statement = "UPDATE `info_trend_echart` " \
"SET `decipherment`=%s,`is_trend_show`=%s,`is_variety_show`=%s,`update_time`=%s " \
"WHERE `id`=%s AND `author_id`=%s;"
is_trend_show = 1 if is_trend_show else 0
is_variety_show = 1 if is_variety_show else 0
now = datetime.now()
cursor.execute(update_statement,
(decipherment, is_trend_show, is_variety_show, now, cid,
user_info['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()
custom_time = body_json.get('custom_time')
name = body_json.get('name', '')
area = body_json.get('area', '')
level = body_json.get('level', '')
price = body_json.get('price', 0)
increase = body_json.get('increase', 0)
note = body_json.get('note', '')
if not all([custom_time, name, price, increase]):
return jsonify({"message": "参数错误."}), 400
save_statement = "INSERT INTO `info_spot` " \
"(`create_time`, `custom_time`,`name`,`area`,`level`,`price`,`increase`,`author_id`,`note`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);"
db_connection = MySQLConnection()
try:
custom_time = datetime.datetime.strptime(custom_time, "%Y-%m-%d")
price = float(price)
increase = float(increase)
user_id = user_info['id']
cursor = db_connection.get_cursor()
cursor.execute(save_statement,
(today, custom_time, name, area, level, price,
increase, user_id, note))
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, 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 `onduty_message` " \
"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 post(self):
# 获取当前用户的信息
user_id = request.form.get('uid')
file = request.files.get('file', None)
if not file or not user_id:
return jsonify('参数错误,NOT FILE OR UID'), 400
# 查找用户
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, user_id)
user_obj = cursor.fetchone()
db_connection.close()
# 管理员不给添加信息
if user_obj['is_admin']:
return jsonify('请不要使用用管理员用户添加记录.')
# 文件内容
file_contents = file.read()
file_contents = xlrd.open_workbook(file_contents=file_contents)
# table_data = file_contents.sheets()[0]
# 导入名称为“值班信息”的表
table_data = file_contents.sheet_by_name('值班信息')
# 检查sheet1是否导入完毕
status = file_contents.sheet_loaded('值班信息')
if not status:
return jsonify('文件数据导入失败'), 400
# 读取第一行数据
first_row = table_data.row_values(0)
# 格式判断
if first_row != ["日期", "信息内容", "备注"]:
return jsonify("表格格式有误,请修正."), 400
nrows = table_data.nrows
# ncols = table_data.ncols
# print("行数：", nrows, "列数：", ncols)
# 获取数据
ready_to_save = list() # 准备保存的数据集
start_data_in = False
# 组织数据写入数据库
message = "表格列数据类型有误,请检查后上传."
try:
for row in range(nrows):
row_content = table_data.row_values(row)
# 找到需要开始上传的数据
if str(row_content[0]).strip() == 'start':
start_data_in = True
continue # 继续下一行
if str(row_content[0]).strip() == 'end':
start_data_in = False
continue
if start_data_in:
record_row = list() # 每行记录
# 转换数据类型
try:
record_row.append(
xlrd.xldate_as_datetime(row_content[0], 0))
except Exception:
message = "第一列【日期】请使用日期格式上传."
raise ValueError(message)
record_row.append(user_id)
record_row.append(str(row_content[1]))
record_row.append(str(row_content[2]))
ready_to_save.append(record_row)
if len(ready_to_save) == 0:
raise ValueError('没有读取到数据.')
# 将新的数据转为DataFrame
new_df = pd.DataFrame(ready_to_save)
new_df.columns = ['custom_time', 'author_id', 'content', 'note']
save_list = self.drop_old_date_record(new_df, user_id)
if len(save_list) > 0:
message = "数据保存成功!"
insert_statement = "INSERT INTO `onduty_message`" \
"(`custom_time`,`author_id`,`content`,`note`)" \
"VALUES (%s,%s,%s,%s);"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.executemany(insert_statement, save_list)
db_connection.commit()
db_connection.close()
else:
message = "数据上传成功,没有发现新数据!"
except Exception as e:
return jsonify(str(e)), 400
else:
return jsonify(message)
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 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')
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_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):
# 获取当前用户的信息
user_id = request.form.get('uid')
file = request.files.get('file', None)
if not file or not user_id:
return jsonify('参数错误,NOT FILE OR UID'), 400
# 查找用户
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, user_id)
user_obj = cursor.fetchone()
# 管理员不给添加信息
if user_obj['is_admin']:
return jsonify('请不要使用用管理员用户添加记录.')
# 准备品种信息
# variety_dict = {value: key for key, value in VARIETY_LIB.items()}
query_variety = "SELECT `id`,`name` FROM `variety` WHERE `parent_id` IS NOT NULL;"
cursor.execute(query_variety)
variety_all = cursor.fetchall()
variety_dict = {
variety_item["name"]: variety_item['id']
for variety_item in variety_all
}
db_connection.close()
# 文件内容
file_contents = file.read()
file_contents = xlrd.open_workbook(file_contents=file_contents)
# 导入名称为“投顾策略记录”的表
table_data = file_contents.sheet_by_name('投顾策略记录')
# 检查sheet1是否导入完毕
status = file_contents.sheet_loaded('投顾策略记录')
if not status:
return jsonify('文件数据导入失败'), 400
# 读取第一行数据
first_row = table_data.row_values(0)
# 格式判断
if first_row != [
"日期", "策略内容", "品种", "合约", "方向(多头,空头,套利)", "10万为限对应手数", "策略开仓",
"策略平仓", "策略结果(+/-/0)"
]:
return jsonify("表格格式有误,请修正."), 400
# 读取数据并写入数据库
nrows = table_data.nrows
# ncols = table_data.ncols
ready_to_save = list()
start_row_in = False
message = "表格列数据类型有误,请检查后上传."
try:
for row in range(nrows):
row_content = table_data.row_values(row)
if str(row_content[0]).strip() == "start":
start_row_in = True
continue
if str(row_content[0]).strip() == "end":
start_row_in = False
continue
if start_row_in:
record_row = list()
try:
record_row.append(
xlrd.xldate_as_datetime(row_content[0], 0))
except Exception as e:
message = "第一列【日期】请使用日期格式上传."
raise ValueError(message)
record_row.append(user_id)
record_row.append(str(row_content[1]))
try:
record_row.append(
int(variety_dict.get(str(row_content[2])))) # 品种
except Exception as e:
message = "系统中没有【" + str(row_content[2]) + "】品种信息."
raise ValueError(message)
try:
contract = int(row_content[3])
except Exception:
contract = row_content[3]
record_row.append(str(contract))
record_row.append(str(row_content[4]))
record_row.append(
int(row_content[5]) if row_content[5] else 0)
record_row.append(
float(row_content[6]) if row_content[6] else 0)
record_row.append(
float(row_content[7]) if row_content[7] else 0)
record_row.append(
float(row_content[8]) if row_content[8] else 0)
ready_to_save.append(record_row)
if len(ready_to_save) == 0:
raise ValueError('没有读取到数据.')
new_df = pd.DataFrame(ready_to_save)
new_df.columns = [
'custom_time', 'author_id', 'content', 'variety_id',
'contract', 'direction', 'hands', 'open_position',
'close_position', 'profit'
]
save_list = self.drop_old_date_record(new_df, user_id)
if len(save_list) > 0:
message = "数据保存成功!"
insert_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);"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
cursor.executemany(insert_statement, ready_to_save)
db_connection.commit()
db_connection.close()
else:
message = "数据上传成功,没有发现新数据!"
except Exception as e:
return jsonify(str(e)), 400
else:
return jsonify(message)
def create_others_table():
# 连接mysql
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 创建非常规工作信息表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `abnormal_work` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`custom_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`author_id` INT(11) NOT NULL,"
"`task_type` INT (11) NOT NULL,"
"`title` VARCHAR(128) NOT NULL,"
"`sponsor` VARCHAR(64) DEFAULT '',"
"`applied_org` VARCHAR (128) DEFAULT '',"
"`applicant` VARCHAR (64) DEFAULT '',"
"`tel_number` VARCHAR (128) DEFAULT '',"
"`swiss_coin` INT(11) DEFAULT 0,"
"`allowance` DECIMAL(13,4) DEFAULT 0,"
"`note` VARCHAR(512) DEFAULT '',"
"`partner` VARCHAR (128) DEFAULT '',"
"`annex` VARCHAR (512) DEFAULT '',"
"`annex_url` VARCHAR (512) DEFAULT ''"
");")
# 创建专题研究信息表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `monographic` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`custom_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`author_id` INT(11) NOT NULL,"
"`title` VARCHAR(128) NOT NULL,"
"`words` INT(11) DEFAULT 0,"
"`is_publish` BIT NOT NULL DEFAULT 0,"
"`level` VARCHAR (4) DEFAULT '',"
"`score` INT(11) NOT NULL DEFAULT 0,"
"`note` VARCHAR(512) DEFAULT '',"
"`partner` VARCHAR (128) DEFAULT ''"
");")
# 创建投资方案信息表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `investment` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`custom_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`author_id` INT(11) NOT NULL,"
"`title` VARCHAR(128) NOT NULL,"
"`variety_id` INT(11) NOT NULL,"
"`contract` VARCHAR(64) DEFAULT '',"
"`direction` VARCHAR (8) DEFAULT '',"
"`build_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`build_price` DECIMAL (13,4) DEFAULT 0,"
"`build_hands` INT(11) DEFAULT NULL,"
"`out_price` DECIMAL (13,4) DEFAULT 0,"
"`cutloss_price` DECIMAL (13,4) DEFAULT 0,"
"`expire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`is_publish` BIT NOT NULL DEFAULT 0,"
"`profit` DECIMAL (13,4) DEFAULT 0,"
"`note` VARCHAR(512) DEFAULT '',"
"`annex` VARCHAR (512) DEFAULT '',"
"`annex_url` VARCHAR (512) DEFAULT ''"
");")
# 创建投顾策略信息表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `investrategy` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`custom_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`author_id` INT(11) NOT NULL,"
"`content` VARCHAR(2048) NOT NULL,"
"`variety_id` INT(11) NOT NULL,"
"`contract` VARCHAR(64) DEFAULT '',"
"`direction` VARCHAR (8) DEFAULT '',"
"`hands` INT(11) DEFAULT NULL,"
"`open_position` INT(11) DEFAULT NULL,"
"`close_position` INT(11) DEFAULT NULL,"
"`profit` DECIMAL(13,4) DEFAULT 0,"
"`note` VARCHAR(512) DEFAULT ''"
");")
# 创建文章发表审核记录表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `article_publish` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`custom_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`author_id` INT(11) NOT NULL,"
"`title` VARCHAR(128) NOT NULL,"
"`media_name` VARCHAR(128) NOT NULL DEFAULT '',"
"`rough_type` VARCHAR(32) NOT NULL DEFAULT '',"
"`words` INT(11) DEFAULT 0,"
"`checker` VARCHAR(128) DEFAULT '',"
"`allowance` INT(11) DEFAULT 0,"
"`partner` VARCHAR (128) DEFAULT '',"
"`note` VARCHAR(512) DEFAULT '',"
"`annex` VARCHAR (512) DEFAULT '',"
"`annex_url` VARCHAR (512) DEFAULT ''"
");")
# 创建短讯通记录表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `short_message` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`custom_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`author_id` INT(11) NOT NULL,"
"`content` VARCHAR(1024) NOT NULL,"
"`msg_type` VARCHAR(128) NOT NULL DEFAULT '',"
"`effect_variety` VARCHAR(256) NOT NULL DEFAULT '',"
"`note` VARCHAR(512) DEFAULT ''"
");")
# 值班信息记录表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `onduty_message` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`custom_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`author_id` INT(11) NOT NULL,"
"`content` VARCHAR(1024) NOT NULL,"
"`note` VARCHAR(512) DEFAULT ''"
");")
db_connection.commit()
db_connection.close()
def initial_tables_and_data():
# 连接mysql
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 系统模块信息表
cursor.execute("CREATE TABLE IF NOT EXISTS `work_module` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(32) NOT NULL,"
"`page_url` VARCHAR (255) DEFAULT '',"
"`sort` INT(11) NOT NULL DEFAULT 0,"
"`parent_id` INT(11) DEFAULT NULL,"
"`is_active` BIT NOT NULL DEFAULT 1,"
"`is_private` BIT NOT NULL DEFAULT 0"
");")
# 创建用户信息表
cursor.execute(
"CREATE TABLE IF NOT EXISTS `user_info` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(255) NOT NULL UNIQUE,"
"`fixed_code` VARCHAR(8) NOT NULL UNIQUE,"
"`join_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,"
"`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
"`password` VARCHAR(32) NOT NULL,"
"`phone` VARCHAR(11) NOT NULL DEFAULT '',"
"`email` VARCHAR(64) NOT NULL DEFAULT '',"
"`is_admin` BIT NOT NULL DEFAULT 0,"
"`is_active` BIT NOT NULL DEFAULT 0,"
"`org_id` INT(11) NOT NULL DEFAULT 0"
");")
# 用户与不用提交的模块第三方表
cursor.execute("CREATE TABLE IF NOT EXISTS `user_ndo_module` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`user_id` INT(11) NOT NULL,"
"`module_id` INT(11) NOT NULL,"
"`is_active` BIT NOT NULL DEFAULT 1,"
"UNIQUE KEY `user_id`(`user_id`,`module_id`)"
");")
# 系统中品种信息表
cursor.execute("CREATE TABLE IF NOT EXISTS `variety` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(32) NOT NULL UNIQUE,"
"`en_code` VARCHAR(16) NOT NULL DEFAULT '',"
"`sort` INT(11) NOT NULL DEFAULT 0,"
"`parent_id` INT(11) DEFAULT NULL,"
"`is_active` BIT NOT NULL DEFAULT 1,"
"UNIQUE KEY `name_unique`(`name`,`en_code`)"
");")
"""添加系统默认菜单"""
# 加入系统设置菜单组
save_module = "INSERT INTO `work_module` (`name`,`page_url`,`parent_id`) VALUES ('系统设置','',NULL);"
cursor.execute(save_module)
new_mid = db_connection.insert_id() # 返回的id
# 修改sort
update_sort_statement = "UPDATE `work_module` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_sort_statement, (new_mid, new_mid))
# 插入系统模块管理
insert_statement = "INSERT INTO `work_module` (`name`,`page_url`,`parent_id`) VALUES ('系统模块管理','sys-modules.html',%s);"
cursor.execute(insert_statement, (new_mid, ))
new_mid = db_connection.insert_id() # 返回的id
# 修改sort字段的值
update_sort_statement = "UPDATE `work_module` SET `sort`=%s WHERE `id`=%s;"
cursor.execute(update_sort_statement, (new_mid, new_mid))
"""添加一个默认管理员信息"""
# 新增一个管理员信息
save_admin = "INSERT INTO `user_info` (`name`,`fixed_code`,`password`,`is_admin`,`is_active`) VALUES ('管理员','rdyj321','bbe7977cef5fcf80a39b801fcfdda5e0', 1, 1);"
cursor.execute(save_admin)
# 提交数据
db_connection.commit()
db_connection.close() # 关闭数据库连接
def post(self):
# # 批量增加仓库
# body_json = request.json
# warehouses = body_json.get('warehouses', None)
# db_connection = MySQLConnection()
# cursor = db_connection.get_cursor()
# # 查询所有的简称对应的仓库编号
# cursor.execute("SELECT `name`,`fixed_code` FROM `info_warehouse_fixed_code`;")
# all_fixed_codes = cursor.fetchall()
# all_fixed_codes_dict = dict()
#
# for item in all_fixed_codes:
# if item['name'] not in all_fixed_codes_dict.keys():
# all_fixed_codes_dict[item['name']] = item['fixed_code']
# else:
# print('该仓库简称已存在')
# for new_warehouse in warehouses:
# new_fixed_code = all_fixed_codes_dict.get(strQ2B(new_warehouse['short_name']), None)
# if not new_fixed_code:
# print("发现新仓库:{}!".format(new_warehouse['short_name']))
# # print(new_warehouse)
# # db_connection.close()
# # return jsonify({'message':'有新仓库{}'.format(new_warehouse['short_name'])}), 400
# else:
# new_warehouse['fixed_code'] = new_fixed_code
# save_list = list()
# for house_item in warehouses:
# item_list = list()
# item_list.append(house_item['fixed_code'])
# item_list.append(house_item['area'])
# item_list.append(house_item['name'])
# item_list.append(strQ2B(house_item['short_name']))
# item_list.append(house_item['addr'])
# item_list.append(house_item['arrived'])
# item_list.append(house_item['longitude'])
# item_list.append(house_item['latitude'])
# save_list.append(item_list)
# print(save_list)
# insert_statement = "INSERT INTO `info_warehouse` " \
# "(`fixed_code`,`area`,`name`,`short_name`,`addr`,`arrived`,`longitude`,`latitude`) " \
# "VALUES (%s,%s,%s,%s,%s,%s,%s,%s);"
# cursor.executemany(insert_statement, save_list)
# db_connection.commit()
# db_connection.close()
# 新增单个仓库
body_json = request.json
area = body_json.get('area', None)
name = body_json.get('name', None)
short_name = strQ2B(body_json.get('short_name', ''))
addr = body_json.get('addr', None)
arrived = body_json.get('arrived', '')
longitude = body_json.get('longitude', None)
latitude = body_json.get('latitude', None)
if not all([area, name, short_name, addr, longitude, latitude]):
return jsonify({'message': '参数错误!'}), 400
try:
longitude = float(longitude)
latitude = float(latitude)
except Exception:
return jsonify({'message': '参数错误!'}), 400
# 获取编号并保存数据到数据库
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 查询仓库简称对应编码是否存在
fixed_code_statement = "SELECT `fixed_code` FROM `info_warehouse_fixed_code` where `name`=%s;"
cursor.execute(fixed_code_statement, short_name)
fixed_code_exist = cursor.fetchone()
if not fixed_code_exist:
db_connection.close()
return jsonify({'message': '请先添加该仓库简称信息!否则无法新增仓库.'}), 400
# 查询该编码是否已经在仓库信息中了
fixed_code = fixed_code_exist['fixed_code']
select_statement = "SELECT `id` FROM `info_warehouse` WHERE `fixed_code`=%s;"
cursor.execute(select_statement, fixed_code)
warehouse_exist = cursor.fetchone()
if warehouse_exist:
db_connection.close()
return jsonify({'message': '该仓库已存在...无需重复添加.'}), 400
# 保存仓库信息
insert_statement = "INSERT INTO `info_warehouse` " \
"(`fixed_code`,`area`,`name`, `short_name`,`addr`,`arrived`,`longitude`,`latitude`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s);"
cursor.execute(insert_statement, (fixed_code, area, name, short_name, addr, arrived, longitude, latitude))
db_connection.commit()
db_connection.close()
return jsonify({'message': '新增成功!'}), 201
def get(self, hid):
variety_en = request.args.get('v_en', None)
if variety_en:
try:
variety_en = variety_en.upper()
except Exception:
return jsonify({'message':'参数错误!', 'warehouses_receipts':{}})
# print('品种:', variety_en)
# 获取指定仓库下的仓单详情和交割的品种
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
if variety_en:
query_statement = "SELECT infowhtb.id,infowhtb.fixed_code,infowhtb.name,infowhtb.short_name,infowhtb.addr," \
"lwvtb.variety,lwvtb.variety_en,lwvtb.linkman,lwvtb.links,lwvtb.premium,lwvtb.receipt_unit," \
"infovdly.last_trade,infovdly.receipt_expire,infovdly.delivery_unit,infovdly.limit_holding " \
"FROM `info_warehouse_variety` AS lwvtb " \
"INNER JOIN `info_warehouse` AS infowhtb " \
"ON lwvtb.warehouse_code=infowhtb.fixed_code " \
"LEFT JOIN `info_variety_delivery` AS infovdly " \
"ON infovdly.variety_en=lwvtb.variety_en " \
"WHERE infowhtb.id=%s AND lwvtb.variety_en=%s;"
cursor.execute(query_statement,(hid, variety_en))
else:
query_statement = "SELECT infowhtb.id,infowhtb.fixed_code,infowhtb.name,infowhtb.short_name,infowhtb.addr," \
"lwvtb.variety,lwvtb.variety_en,lwvtb.linkman,lwvtb.links,lwvtb.premium,lwvtb.receipt_unit," \
"infovdly.last_trade, infovdly.receipt_expire,infovdly.delivery_unit,infovdly.limit_holding " \
"FROM `info_warehouse_variety` AS lwvtb " \
"INNER JOIN `info_warehouse` AS infowhtb " \
"ON lwvtb.warehouse_code=infowhtb.fixed_code " \
"LEFT JOIN `info_variety_delivery` AS infovdly " \
"ON infovdly.variety_en=lwvtb.variety_en " \
"WHERE infowhtb.id=%s;"
cursor.execute(query_statement, hid)
query_result = cursor.fetchall()
if len(query_result) <= 0:
db_connection.close()
return jsonify({'message':'获取仓单成功','warehouses_receipts': {}})
# 整理出品种列表以及品种的仓单
response_data = dict()
variety_receipts = list()
# 查询仓单sql
receipt_statement = "SELECT * " \
"FROM `info_warehouse_receipt` " \
"WHERE `variety_en`=%s AND `warehouse_code`=%s " \
"ORDER BY `id` DESC " \
"LIMIT 10;"
variety_first = query_result[0]
response_data['warehouse'] = variety_first['name']
response_data['short_name'] = variety_first['short_name']
response_data['addr'] = variety_first['addr']
response_data['varieties'] = variety_receipts
for variety_item in query_result:
variety_dict = dict()
variety_dict['name'] = variety_item['variety']
variety_dict['name_en'] = variety_item['variety_en']
variety_dict['last_trade'] = variety_item['last_trade'] if variety_item['last_trade'] else ''
variety_dict['receipt_expire'] = variety_item['receipt_expire'] if variety_item['receipt_expire'] else ''
variety_dict['delivery_unit'] = variety_item['delivery_unit'] if variety_item['delivery_unit'] else ''
variety_dict['linkman'] = variety_item['linkman']
variety_dict['links'] = variety_item['links']
variety_dict['premium'] = variety_item['premium']
cursor.execute(receipt_statement, (variety_item['variety_en'], variety_item['fixed_code']))
variety_dict['receipts'] = cursor.fetchall()
variety_dict['receipt_unit'] = variety_item['receipt_unit']
variety_dict['limit_holding'] = variety_item['limit_holding']
variety_receipts.append(variety_dict)
# print(response_data)
db_connection.close()
return jsonify({'message': '获取仓单成功','warehouses_receipts': response_data})
def post(self, wcode):
# # 批量增加仓库的可交割品种
# body_json = request.json
# variety_message = body_json.get('variety_record', '')
# # 查询所有的简称对应的仓库编号
# db_connection = MySQLConnection()
# cursor = db_connection.get_cursor()
# cursor.execute("SELECT `name`,`fixed_code` FROM `info_warehouse_fixed_code`;")
# all_fixed_codes = cursor.fetchall()
# all_fixed_codes_dict = dict()
#
# for item in all_fixed_codes:
# if item['name'] not in all_fixed_codes_dict.keys():
# all_fixed_codes_dict[item['name']] = item['fixed_code']
# else:
# print('该仓库简称已存在')
# # 通过简称找到仓库
# for variety_item in variety_message:
# house_fixed_code = all_fixed_codes_dict.get(variety_item['short_name'],None)
# if not house_fixed_code:
# print('发现新仓库:{}'.format(variety_item['short_name']))
# else:
# variety_item['warehouse_code'] = house_fixed_code
#
# # 整理数据
# save_list = list()
# for new_item in variety_message:
# new_list = list()
# new_list.append(new_item['warehouse_code'])
# new_list.append(new_item['name'])
# new_list.append(new_item['name_en'])
# new_list.append(new_item['linkman'])
# new_list.append(new_item['links'])
# new_list.append(new_item['premium'])
# new_list.append(new_item['receipt_unit'])
# save_list.append(new_list)
#
# print(save_list)
# save_statement = "INSERT INTO `info_warehouse_variety` " \
# "(`warehouse_code`,`variety`,`variety_en`,`linkman`,`links`,`premium`,`receipt_unit`) " \
# "VALUES (%s,%s,%s,%s,%s,%s,%s);"
# cursor.executemany(save_statement, save_list)
# db_connection.commit()
# db_connection.close()
# 新增或修改仓库的可交割品种
body_json = request.json
variety_en = body_json.get('variety_en', None)
variety_text = body_json.get('variety_text', None)
if not all([wcode, variety_en, variety_text]):
return jsonify({'message': '参数错误'}), 400
is_delivery = body_json.get('is_delivery', 0)
delivery_msg = body_json.get('delivery_msg', None)
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 去除可交割品种
delete_statement = "DELETE FROM `info_warehouse_variety` " \
"WHERE `warehouse_code`=%s AND `variety_en`=%s;"
cursor.execute(delete_statement, (wcode, variety_en))
if is_delivery:
linkman = delivery_msg.get('linkman', '')
links = delivery_msg.get('links', '')
premium = delivery_msg.get('premium', '')
receipt_unit = delivery_msg.get('receipt_unit', '')
# 新增
insert_statement = "INSERT INTO `info_warehouse_variety` " \
"(`warehouse_code`,`variety`,`variety_en`,`linkman`,`links`,`premium`,`receipt_unit`) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s);"
cursor.execute(insert_statement, (wcode, variety_text, variety_en, linkman, links, premium, receipt_unit))
db_connection.commit()
db_connection.close()
return jsonify({'message': '操作成功!'})
def get_abnormal_work(self, userid, start_date, end_date, current_page,
page_size):
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
start_id = current_page * page_size
table_headers = [
'日期', '姓名', '标题', '类型', '申请部门', '申请者', '联系电话', '瑞币', '补贴', '备注',
'附件'
]
header_keys = [
'custom_time', 'name', 'title', 'task_type', 'applied_org',
'applicant', 'tel_number', 'swiss_coin', 'allowance', 'note',
'annex_url'
]
if userid == 0:
query_statement = "SELECT usertb.name, abwtb.* " \
"FROM `abnormal_work` AS abwtb INNER JOIN `user_info` AS usertb " \
"ON abwtb.is_examined=1 AND abwtb.author_id=usertb.id AND (abwtb.custom_time BETWEEN %s AND %s) " \
"ORDER BY abwtb.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(abwtb.id) AS `total` " \
"FROM `abnormal_work` AS abwtb INNER JOIN `user_info` AS usertb " \
"ON abwtb.is_examined=1 AND abwtb.author_id=usertb.id AND (abwtb.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, abwtb.* " \
"FROM `abnormal_work` AS abwtb INNER JOIN `user_info` AS usertb " \
"ON abwtb.is_examined=1 AND abwtb.author_id=usertb.id AND abwtb.author_id=%s AND (abwtb.custom_time BETWEEN %s AND %s) " \
"ORDER BY abwtb.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 `abnormal_work` " \
"WHERE `is_examined`=1 AND `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['task_type'] = ABNORMAL_WORK.get(
record_item['task_type'], '')
record_item['swiss_coin'] = record_item[
'swiss_coin'] if record_item['swiss_coin'] else ''
record_item['allowance'] = int(record_item['allowance'])
record_item['annex'] = record_item['annex_url'] if record_item[
'annex_url'] 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 create_tables():
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 仓库编号表
cursor.execute("CREATE TABLE IF NOT EXISTS `info_warehouse_fixed_code` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`name` VARCHAR(128) NOT NULL,"
"fixed_code VARCHAR(4) NOT NULL,"
"`create_time` DATETIME DEFAULT NOW(),"
"`update_time` DATETIME DEFAULT NOW(),"
"UNIQUE KEY `warecode`(`name`,`fixed_code`)"
");")
# 仓库信息表
cursor.execute("CREATE TABLE IF NOT EXISTS `info_warehouse` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"fixed_code VARCHAR(4) NOT NULL DEFAULT '',"
"`area` VARCHAR(4) NOT NULL,"
"`name` VARCHAR(128) NOT NULL DEFAULT '',"
"`short_name` VARCHAR(16) NOT NULL,"
"`addr` VARCHAR(1024) NOT NULL DEFAULT '',"
"`arrived` VARCHAR(1536) NOT NULL DEFAULT '',"
"`create_time` DATETIME DEFAULT NOW(),"
"`update_time` DATETIME DEFAULT NOW(),"
"`longitude` FLOAT(10,4) NOT NULL,"
"`latitude` FLOAT(10,4) NOT NULL,"
"`is_active` BIT NOT NULL DEFAULT 1"
");")
# 仓库交割的品种表
cursor.execute("CREATE TABLE IF NOT EXISTS `info_warehouse_variety` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"warehouse_code VARCHAR(4) NOT NULL,"
"`variety` VARCHAR(8) NOT NULL DEFAULT '',"
"`variety_en` VARCHAR(4) NOT NULL DEFAULT '',"
"`linkman` VARCHAR(32) DEFAULT '' DEFAULT '',"
"`links` VARCHAR(512) NOT NULL DEFAULT '',"
"`premium` VARCHAR(64) NOT NULL DEFAULT '',"
"`receipt_unit` VARCHAR(32) DEFAULT '',"
"`create_time` DATETIME DEFAULT NOW(),"
"`update_time` DATETIME DEFAULT NOW(),"
"`is_active` BIT NOT NULL DEFAULT 1,"
"UNIQUE KEY `warevariety`(`warehouse_code`,`variety_en`)"
");")
# 品种交割基本信息表
cursor.execute("CREATE TABLE IF NOT EXISTS `info_variety_delivery` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`variety` VARCHAR(8) NOT NULL DEFAULT '',"
"`variety_en` VARCHAR(4) NOT NULL DEFAULT '',"
"`last_trade` VARCHAR(32) DEFAULT '' DEFAULT '',"
"`receipt_expire` VARCHAR(512) NOT NULL DEFAULT '',"
"`delivery_unit` VARCHAR(32) NOT NULL DEFAULT '',"
"`limit_holding` VARCHAR(32) DEFAULT '',"
"UNIQUE KEY `varietyaen`(`variety`,`variety_en`)"
");")
# 仓单表
cursor.execute("CREATE TABLE IF NOT EXISTS `info_warehouse_receipt` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`warehouse_code` VARCHAR(4) DEFAULT '',"
"`warehouse_name` VARCHAR(32) NOT NULL,"
"`variety` VARCHAR(8) NOT NULL DEFAULT '',"
"`variety_en` VARCHAR(4) NOT NULL DEFAULT '',"
"`date` VARCHAR(8) NOT NULL,"
"`receipt` INT(11) DEFAULT 0,"
"`increase` INT(11) DEFAULT 0,"
"`create_time` DATETIME DEFAULT NOW()"
");")
# 讨论交流表
cursor.execute("CREATE TABLE IF NOT EXISTS `info_discussion` ("
"`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,"
"`author_id` INT(11) NOT NULL,"
"`content` VARCHAR(2048) NOT NULL,"
"`create_time` DATETIME DEFAULT NOW(),"
"`parent_id` INT(11) DEFAULT NULL"
");")
db_connection.commit()
db_connection.close()
def get(self):
params = request.args
# 解析用户信息
token = params.get('utoken')
user_info = verify_json_web_token(token)
if not user_info:
return jsonify("您的登录已过期,请重新登录查看.")
user_id = user_info['uid']
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')
current_page = int(params.get('page', 1)) - 1
page_size = int(params.get('pagesize', 30))
except Exception:
return jsonify("参数错误:DATE FORMAT ERROR & INT TYPE REQUIRED!")
start_id = current_page * page_size
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# sql内联查询
inner_join_statement = "SELECT usertb.name,usertb.org_id,invsgytb.id,invsgytb.custom_time,invsgytb.content,invsgytb.variety_id, varietytb.name AS variety, invsgytb.contract,invsgytb.direction,invsgytb.hands,invsgytb.open_position," \
"invsgytb.close_position,invsgytb.profit " \
"FROM `user_info` AS usertb INNER JOIN `investrategy` AS invsgytb INNER JOIN `variety` as varietytb ON " \
"(usertb.id=%s AND usertb.id=invsgytb.author_id) AND invsgytb.variety_id=varietytb.id AND (invsgytb.custom_time BETWEEN %s AND %s) " \
"ORDER BY invsgytb.custom_time DESC " \
"limit %s,%s;"
cursor.execute(inner_join_statement,
(user_id, start_date, end_date, start_id, page_size))
result_records = cursor.fetchall()
# print("内连接查投顾策略自方案结果", result_records)
# 查询总条数
count_statement = "SELECT COUNT(invsgytb.id) as total, SUM(invsgytb.profit) AS `sumprofit` " \
"FROM `user_info` AS usertb INNER JOIN `investrategy`AS invsgytb " \
"ON usertb.id=%s AND usertb.id=invsgytb.author_id AND (invsgytb.custom_time BETWEEN %s AND %s);"
cursor.execute(count_statement, (user_id, start_date, end_date))
fetch_one = cursor.fetchone()
# print(fetch_one)
db_connection.close()
if fetch_one:
total_count = fetch_one['total']
sum_porfit = fetch_one['sumprofit']
else:
total_count = sum_porfit = 0
total_page = int((total_count + page_size - 1) / page_size)
# print('total_page',total_page)
# 组织数据返回
response_data = dict()
response_data['records'] = list()
for record_item in result_records:
record_item['custom_time'] = record_item['custom_time'].strftime(
'%Y-%m-%d')
record_item['variety'] = (record_item['variety']
if record_item['variety'] else '') + str(
record_item['contract'])
record_item['org_name'] = ORGANIZATIONS.get(
int(record_item['org_id']), '未知')
record_item['open_position'] = float(record_item['open_position'])
record_item['close_position'] = float(
record_item['close_position'])
record_item['profit'] = float(record_item['profit'])
response_data['records'].append(record_item)
response_data['current_page'] = current_page + 1 # 查询前给减1处理了，加回来
response_data['total_page'] = total_page
response_data['current_count'] = len(result_records)
response_data['total_count'] = total_count
response_data['sum_profit'] = float(sum_porfit) if sum_porfit else 0
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,mgpctb.custom_time,mgpctb.title,mgpctb.words,mgpctb.is_publish,mgpctb.level,mgpctb.score,mgpctb.note " \
"FROM `user_info` AS usertb INNER JOIN `monographic` AS mgpctb ON " \
"usertb.id=%s AND usertb.id=mgpctb.author_id AND (mgpctb.custom_time BETWEEN %s AND %s) " \
"ORDER BY mgpctb.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['title'])
row_content.append(record_item['words'])
row_content.append("是" if record_item['is_publish'] else "否")
row_content.append(record_item['level'])
row_content.append(record_item['score'])
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 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 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
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,invsgytb.custom_time,invsgytb.content,invsgytb.variety_id,invsgytb.contract,invsgytb.direction,invsgytb.hands," \
"invsgytb.open_position,invsgytb.close_position,invsgytb.profit,invsgytb.note " \
"FROM `user_info` AS usertb INNER JOIN `investrategy` AS invsgytb ON " \
"usertb.id=%s AND usertb.id=invsgytb.author_id AND (invsgytb.custom_time BETWEEN %s AND %s) " \
"ORDER BY invsgytb.custom_time ASC;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 查询品种
query_variety = "SELECT `id`,`name` FROM `variety` WHERE `parent_id` IS NOT NULL;"
cursor.execute(query_variety)
variety_all = cursor.fetchall()
variety_dict = {
variety_item["id"]: variety_item['name']
for variety_item in variety_all
}
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(variety_dict.get(record_item['variety_id'], ''))
row_content.append(record_item['contract'])
row_content.append(record_item['direction'])
row_content.append(record_item['hands'])
row_content.append(float(record_item['open_position']))
row_content.append(float(record_item['close_position']))
row_content.append(float(record_item['profit']))
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 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)
serarch_report_file = request.files.get('sreport_file')
if not all([title, serarch_report_file]):
return jsonify({"message": "参数错误!"}), 400
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
market_filename = serarch_report_file.filename
file_hash_name = hash_filename(market_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/searchrp/{}/{}/".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/searchrp/{}/{}/".format(year, month) + prefix_filename + file_hash_name
serarch_report_file.save(file_path)
save_statement = "INSERT INTO `info_searchreport` (`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_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 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_investment(self, userid, start_date, end_date, current_page,
page_size):
start_id = current_page * page_size
table_headers = [
'日期', '姓名', '标题', '品种', '方向', '实建日期', '实建均价', '实建手数', '实出均价',
'止损均价', '有效期', '评级', '结果', '备注', '附件'
]
header_keys = [
'custom_time', 'name', 'title', 'variety', 'direction',
'build_time', 'build_price', 'build_hands', 'out_price',
'cutloss_price', 'expire_time', 'level', 'profit', 'note',
'annex_url'
]
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 准备品种信息
query_variety = "SELECT `id`,`name` FROM `variety` WHERE `parent_id` IS NOT NULL;"
cursor.execute(query_variety)
variety_all = cursor.fetchall()
variety_dict = {
variety_item["id"]: variety_item['name']
for variety_item in variety_all
}
if userid == 0:
query_statement = "SELECT usertb.name, invstb.* " \
"FROM `investment` AS invstb INNER JOIN `user_info` AS usertb " \
"ON invstb.author_id=usertb.id AND (invstb.custom_time BETWEEN %s AND %s ) " \
"ORDER BY invstb.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(invstb.id) AS `total`,SUM(invstb.profit) AS `sumprofit` " \
"FROM `investment` AS invstb INNER JOIN `user_info` AS usertb " \
"ON invstb.author_id=usertb.id AND (invstb.custom_time BETWEEN %s AND %s );"
cursor.execute(total_count_statement, (start_date, end_date))
fetch_one = cursor.fetchone()
if fetch_one:
total_count = fetch_one['total']
sum_porfit = fetch_one['sumprofit']
else:
total_count = sum_porfit = 0
else:
query_statement = "SELECT usertb.name, invstb.* " \
"FROM `investment` AS invstb INNER JOIN `user_info` AS usertb " \
"ON invstb.author_id=usertb.id AND invstb.author_id=%s AND (invstb.custom_time BETWEEN %s AND %s ) " \
"ORDER BY invstb.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`, SUM(`profit`) AS `sumprofit` FROM `investment` " \
"WHERE `author_id`=%s AND `custom_time` BETWEEN %s AND %s;"
cursor.execute(total_count_statement,
(userid, start_date, end_date))
fetch_one = cursor.fetchone()
# print(fetch_one)
if fetch_one:
total_count = fetch_one['total']
sum_porfit = fetch_one['sumprofit']
else:
total_count = sum_porfit = 0
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['build_time'] = record_item['build_time'].strftime(
'%Y-%m-%d %H:%M')
record_item['expire_time'] = record_item['expire_time'].strftime(
'%Y-%m-%d %H:%M')
record_item['variety'] = variety_dict.get(
record_item['variety_id'], '') + str(record_item['contract'])
# record_item['is_publish'] = "是" if record_item['is_publish'] else "否"
record_item['build_price'] = int(record_item['build_price'])
record_item['out_price'] = int(record_item['out_price'])
record_item['cutloss_price'] = int(record_item['cutloss_price'])
record_item['profit'] = float(record_item['profit'])
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
response_data['sum_profit'] = float(sum_porfit) if sum_porfit else 0
return jsonify(response_data)
def get(self):
params = request.args
# 解析用户信息
token = params.get('utoken')
user_info = verify_json_web_token(token)
if not user_info:
return jsonify("您的登录已过期,请重新登录查看.")
user_id = user_info['uid']
# print(user_id)
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')
current_page = int(params.get('page', 1)) - 1
page_size = int(params.get('pagesize', 30))
except Exception:
return jsonify("参数错误:INT TYPE REQUIRED!")
start_id = current_page * page_size
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# sql内联查询
inner_join_statement = "SELECT usertb.name,usertb.org_id,ondmsgtb.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 DESC " \
"limit %s,%s;"
cursor.execute(inner_join_statement,
(user_id, start_date, end_date, start_id, page_size))
result_records = cursor.fetchall()
# print("内连接查询专题研究结果", result_records)
# 查询总条数
count_statement = "SELECT COUNT(ondmsgtb.id) AS total 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);"
cursor.execute(count_statement, (user_id, start_date, end_date))
# print("条目记录：", cursor.fetchone()) 打开注释下行将无法解释编译
# 计算总页数
total_count = cursor.fetchone()['total']
db_connection.close()
total_page = int((total_count + page_size - 1) / page_size)
# print('total_page',total_page)
# 组织数据返回
response_data = dict()
response_data['records'] = list()
for record_item in result_records:
record_item['custom_time'] = record_item['custom_time'].strftime(
'%Y-%m-%d')
record_item['org_name'] = ORGANIZATIONS.get(
int(record_item['org_id']), '未知')
response_data['records'].append(record_item)
response_data['current_page'] = current_page + 1 # 查询前给减1处理了，加回来
response_data['total_page'] = total_page
response_data['current_count'] = len(result_records)
response_data['total_count'] = total_count
return jsonify(response_data)
def export_investment(self, userid, start_date, end_date):
table_headers = [
'日期', '部门小组', '姓名', '标题', '品种', '合约', '方向', '实建日期', '实建均价', '实建手数',
'实出均价', '止损均价', '有效期', '外发', '结果'
]
query_statement = "SELECT usertb.name,usertb.org_id,invstb.custom_time,invstb.title,invstb.variety_id,invstb.contract,invstb.direction,invstb.build_time," \
"invstb.build_price,invstb.build_hands,invstb.out_price,invstb.cutloss_price,invstb.expire_time,invstb.is_publish,invstb.profit " \
"FROM `user_info` AS usertb INNER JOIN `investment` AS invstb " \
"ON (usertb.id=%s AND usertb.id=invstb.author_id) AND (invstb.custom_time BETWEEN %s AND %s) " \
"ORDER BY invstb.custom_time ASC;"
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 准备品种信息
query_variety = "SELECT `id`,`name` FROM `variety` WHERE `parent_id` IS NOT NULL;"
cursor.execute(query_variety)
variety_all = cursor.fetchall()
variety_dict = {
variety_item["id"]: variety_item['name']
for variety_item in variety_all
}
cursor.execute(query_statement, (userid, start_date, end_date))
query_result = cursor.fetchall()
db_connection.close()
file_records = list()
for record_item in query_result:
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['title'])
row_content.append(
variety_dict.get(record_item['variety_id'], '未知'))
row_content.append(record_item['contract'])
row_content.append(record_item['direction'])
row_content.append(
record_item['build_time'].strftime("%Y-%m-%d %H:%M"))
row_content.append(float(record_item['build_price']))
row_content.append(record_item['build_hands'])
row_content.append(float(record_item['out_price']))
row_content.append(float(record_item['cutloss_price']))
row_content.append(
record_item['expire_time'].strftime("%Y-%m-%d %H:%M"))
row_content.append("是" if record_item['is_publish'] else "否")
row_content.append(float(record_item['profit']))
file_records.append(row_content)
export_df = pd.DataFrame(file_records)
export_df.columns = table_headers
file_folder, md5_str = self.generate_file_path(userid)
file_path = os.path.join(file_folder, '{}.xlsx'.format(md5_str))
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 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:
db_connection.close()
return jsonify("系统没有查到您的信息,无法操作."), 400
if user_obj['is_admin']:
db_connection.close()
return jsonify('请不要使用用管理员用户添加记录.')
# 不为空的信息判断
content = body_data.get('content', False)
if not content:
db_connection.close()
return jsonify("参数错误,NOT FOUND CONTENT"), 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']
note = body_data.get('note', '')
# 存入数据库
save_work_statement = "INSERT INTO `onduty_message`" \
"(`custom_time`,`author_id`,`content`,`note`)" \
"VALUES (%s,%s,%s,%s);"
try:
cursor.execute(save_work_statement,
(custom_time, author_id, content, note))
db_connection.commit()
db_connection.close()
except Exception as e:
current_app.logger.error("写入值班记录错误:" + str(e))
return jsonify("参数错误!无法保存。"), 400
else:
return jsonify("保存成功!"), 201
def get_investrategy(self, userid, start_date, end_date, current_page,
page_size):
start_id = current_page * page_size
table_headers = [
'日期', '姓名', '策略内容', '品种', '方向', '手数', '策略开仓', '策略平仓', '结果', '备注'
]
header_keys = [
'custom_time', 'name', 'content', 'variety', 'direction', 'hands',
'open_position', 'close_position', 'profit', 'note'
]
db_connection = MySQLConnection()
cursor = db_connection.get_cursor()
# 准备品种信息
query_variety = "SELECT `id`,`name` FROM `variety` WHERE `parent_id` IS NOT NULL;"
cursor.execute(query_variety)
variety_all = cursor.fetchall()
variety_dict = {
variety_item["id"]: variety_item['name']
for variety_item in variety_all
}
if userid == 0:
query_statement = "SELECT usertb.name, sgytb.* " \
"FROM `investrategy` AS sgytb INNER JOIN `user_info` AS usertb " \
"ON sgytb.author_id=usertb.id AND (sgytb.custom_time BETWEEN %s AND %s) " \
"ORDER BY sgytb.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(sgytb.id) AS `total`,SUM(sgytb.profit) AS `sumprofit` " \
"FROM `investrategy` AS sgytb INNER JOIN `user_info` AS usertb " \
"ON sgytb.author_id=usertb.id AND (sgytb.custom_time BETWEEN %s AND %s);"
cursor.execute(total_count_statement, (start_date, end_date))
fetch_one = cursor.fetchone()
if fetch_one:
total_count = fetch_one['total']
sum_porfit = fetch_one['sumprofit']
else:
total_count = sum_porfit = 0
else:
query_statement = "SELECT usertb.name, sgytb.* " \
"FROM `investrategy` AS sgytb INNER JOIN `user_info` AS usertb " \
"ON sgytb.author_id=usertb.id AND sgytb.author_id=%s AND (sgytb.custom_time BETWEEN %s AND %s) " \
"ORDER BY sgytb.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`, SUM(`profit`) AS `sumprofit` " \
"FROM `investrategy` " \
"WHERE `author_id`=%s AND `custom_time` BETWEEN %s AND %s;"
cursor.execute(total_count_statement,
(userid, start_date, end_date))
fetch_one = cursor.fetchone()
if fetch_one:
total_count = fetch_one['total']
sum_porfit = fetch_one['sumprofit']
else:
total_count = sum_porfit = 0
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['variety'] = variety_dict.get(
record_item['variety_id'], '') + str(record_item['contract'])
record_item['profit'] = float(record_item['profit'])
record_item['open_position'] = float(record_item['open_position'])
record_item['close_position'] = float(
record_item['close_position'])
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
response_data['sum_profit'] = float(sum_porfit) if sum_porfit else 0
return jsonify(response_data)
