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)