async def modify_client_authority(operate_token: str = Depends(
verify.oauth2_scheme),
modify_item: UserClientAuthItem = Body(...)):
operate_user, _ = verify.decipher_user_token(operate_token)
if not operate_user:
raise HTTPException(status_code=401, detail="您登录过期了,重新登录后再操作!")
# 验证expire_date
try:
datetime.strptime(modify_item.expire_date, '%Y-%m-%d')
except Exception:
raise HTTPException(status_code=400, detail="数据格式有误,修改失败!")
with MySqlZ() as cursor:
cursor.execute(
"SELECT user_id,client_id FROM user_user_client WHERE client_id=%s AND user_id=%s;",
(modify_item.client_id, modify_item.modify_user))
is_exist = cursor.fetchone()
if is_exist:
cursor.execute(
"UPDATE user_user_client SET expire_date=%(expire_date)s "
"WHERE user_id=%(modify_user)s AND client_id=%(client_id)s;",
jsonable_encoder(modify_item))
else:
cursor.execute(
"INSERT INTO user_user_client (user_id, client_id, expire_date) "
"VALUES (%(modify_user)s,%(client_id)s,%(expire_date)s);",
jsonable_encoder(modify_item))
return {"message": "修改用户客户端登录权限成功!"}
async def modify_client_authority(
operate_token: str = Depends(verify.oauth2_scheme),
modify_item: UserVarietyAuthItem = Body(...)):
operate_user, _ = verify.decipher_user_token(operate_token)
if not operate_user:
raise HTTPException(status_code=401, detail="您登录过期了,重新登录后再操作!")
# 验证expire_date
try:
datetime.strptime(modify_item.expire_date, '%Y-%m-%d')
if not re.match(r'^[A-Z]{1,2}$', modify_item.variety_en):
raise ValueError("INVALID VARIETY.")
except Exception:
raise HTTPException(status_code=400, detail="数据格式有误,修改失败!")
with MySqlZ() as cursor:
cursor.execute(
"SELECT user_id,variety_id FROM user_user_variety WHERE variety_id=%s AND user_id=%s;",
(modify_item.variety_id, modify_item.modify_user))
is_exist = cursor.fetchone()
if is_exist:
cursor.execute(
"UPDATE user_user_variety SET expire_date=%(expire_date)s "
"WHERE user_id=%(modify_user)s AND variety_id=%(variety_id)s;",
jsonable_encoder(modify_item))
else:
cursor.execute(
"INSERT INTO user_user_variety (user_id, variety_id, variety_en, expire_date) "
"VALUES (%(modify_user)s,%(variety_id)s,%(variety_en)s,%(expire_date)s);",
jsonable_encoder(modify_item))
return {"message": "修改用户品种权限成功!"}
async def all_users(role: UserRole = Query(...),
user_token: str = Depends(oauth2_scheme)):
user_id, _ = decipher_user_token(user_token) # 用户登录了才能获取
if not user_id:
return {"message": "查询用户成功!", "users": [], "query_role": ""}
with MySqlZ() as cursor:
cursor.execute("SELECT `id`,role FROM user_user WHERE `id`=%s;",
(user_id, ))
user_info = cursor.fetchone()
if not user_info:
return {"message": "查询用户成功!", "users": [], "query_role": ""}
if user_info["role"] not in [role.superuser.name, role.operator.name]:
return {
"message": "查询用户成功!",
"users": [],
"query_role": user_info["role"]
}
cursor.execute(
"SELECT `id`,username,DATE_FORMAT(join_time,'%%Y-%%m-%%d') AS `join_time`, DATE_FORMAT(recent_login,'%%Y-%%m-%%d') AS `recent_login`,"
"user_code,phone,email,role,is_active,note "
"FROM user_user "
"WHERE IF('all'=%s,TRUE,role=%s);", (role.name, role.name))
all_user = cursor.fetchall()
return {
"message": "查询用户成功!",
"users": all_user,
"query_role": user_info["role"]
}
async def user_module_authority(user_token: str = Depends(
verify.oauth2_scheme),
query_user: int = Query(...)):
operate_user, _ = verify.decipher_user_token(user_token)
if not operate_user:
return {"message": "登录已过期了,重新登录再进行操作!", "user": {}, "clients": []}
# 查询用户的客户端登录权限
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,username,user_code,role FROM user_user WHERE id=%s;",
(query_user, ))
user_info = cursor.fetchone()
if not user_info:
return {"message": "操作的用户不存在!", "user": {}, "clients": []}
cursor.execute(
"SELECT cliettb.id, cliettb.client_name,cliettb.machine_uuid,cliettb.is_manager,cliettb.is_active,uctb.expire_date "
"FROM basic_client AS cliettb "
"LEFT JOIN user_user_client AS uctb "
"ON uctb.user_id=%s AND cliettb.id=uctb.client_id;",
(user_info["id"], ))
clients = cursor.fetchall()
for client_item in clients:
if user_info["role"] in ["superuser", "operator"]: # 超级管理员和运营员都有权限登录
client_item["expire_date"] = "3000-01-01"
return {"message": "查询用户客户端登录权限成功!", "user": user_info, "clients": clients}
async def get_report_info(query_date: str = Query(...),
report_type: str = Query(...),
variety_en: str = Query('0')):
# 验证report_type:
if report_type not in REPORT_TYPES.keys():
raise HTTPException(status_code=400, detail="Unknown Report Type")
with MySqlZ() as cursor:
if variety_en == '0':
cursor.execute(
"SELECT id,`date`,variety_en,title,report_type,filepath,is_active FROM research_report "
"WHERE `date`=%s AND report_type=%s;",
(query_date, report_type))
else:
cursor.execute(
"SELECT id,`date`,variety_en,title,report_type,filepath,is_active FROM research_report "
"WHERE `date`=%s AND report_type=%s AND LOCATE(%s,variety_en) > 0;",
(query_date, report_type, variety_en))
reports = cursor.fetchall()
# 处理文件名
for report_item in reports:
report_item["filename"] = os.path.split(report_item["filepath"])[1]
report_item["type_text"] = REPORT_TYPES.get(report_item["report_type"],
report_item["report_type"])
report_item["variety_zh"] = VARIETY_ZH.get(report_item["variety_en"],
report_item["variety_en"])
return {"message": "查询成功!", "reports": reports}
async def chart_option_values(chart_id: int):
# 查询出表格和配置
with MySqlZ() as cursor:
cursor.execute(
"SELECT charttb.id,charttb.title,charttb.option_file,sheettb.db_table "
"FROM industry_user_chart AS charttb "
"INNER JOIN industry_user_sheet AS sheettb "
"ON charttb.sheet_id=sheettb.id AND charttb.id=%s;", (chart_id, ))
chart_info = cursor.fetchone()
if not chart_info:
return {}
# 获取配置
option_file = os.path.join(FILE_STORAGE, chart_info["option_file"])
if not os.path.exists(option_file):
return {}
with open(option_file, 'r') as fp:
base_option = json.load(fp)
# 查询出表的具体数据
sheet_table = chart_info["db_table"]
with VarietySheetDB() as cursor:
cursor.execute("SELECT * FROM %s;" % sheet_table)
sheet_data = cursor.fetchall()
# 处理数据
chart_values, headers_dict = sheet_data_handler(base_option,
pd.DataFrame(sheet_data))
return {
"message": "获取数据成功!",
"chart_type": base_option["chart_category"],
"base_option": base_option,
"chart_values": chart_values,
"sheet_headers": headers_dict
}
async def create_warehouse(
user_token: str = Depends(oauth2_scheme),
warehouse_item: WarehouseItem = Body(...)
):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
with MySqlZ() as cursor:
# 查询编号
cursor.execute(
"SELECT id,fixed_code FROM delivery_warehouse_number WHERE `name`=%s;", (warehouse_item.short_name, )
)
fixed_code_info = cursor.fetchone()
if not fixed_code_info:
raise HTTPException(status_code=400, detail="请先添加仓库简称后再添加仓库...")
fixed_code = fixed_code_info["fixed_code"]
cursor.execute(
"SELECT id FROM delivery_warehouse WHERE fixed_code=%s;", (fixed_code, )
)
exist = cursor.fetchone()
if exist:
raise HTTPException(status_code=400, detail="仓库已存在无需重复添加...")
# 新增仓库信息
warehouse_dict = jsonable_encoder(warehouse_item)
warehouse_dict["fixed_code"] = fixed_code
cursor.execute(
"INSERT INTO delivery_warehouse (fixed_code,area,`name`,short_name,addr,arrived,longitude,latitude) "
"VALUES (%(fixed_code)s,%(area)s,%(name)s,%(short_name)s,%(addr)s,%(arrived)s,%(longitude)s,%(latitude)s);",
warehouse_dict
)
return {"message": "新增成功!"}
async def get_variety_sheet(user_token: str = Depends(oauth2_scheme),
variety_en: str = Depends(verify_variety),
group_id: int = Query(0, ge=0),
is_own: int = Query(0, ge=0, le=1)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
return {"message": "查询品种表成功!", "sheets": []}
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,DATE_FORMAT(create_time,'%%Y-%%m-%%d') AS create_date,"
"DATE_FORMAT(update_time,'%%Y-%%m-%%d %%H:%%i') AS update_date,creator,update_by,"
"variety_en,group_id,sheet_name,min_date,max_date,update_count,origin,note,is_private "
"FROM industry_user_sheet "
"WHERE variety_en=%s AND "
"IF(0=%s,TRUE,group_id=%s) AND IF(0=%s,TRUE,creator=%s) AND IF(%s=creator,TRUE,is_private=0) "
"ORDER BY suffix ASC;",
(variety_en, group_id, group_id, is_own, user_id, user_id))
sheets = cursor.fetchall()
cursor.execute(
"SELECT id,username FROM user_user WHERE role<>'normal';")
user_list = cursor.fetchall()
user_dict = {
user_item["id"]: user_item["username"]
for user_item in user_list
}
for sheet_item in sheets:
sheet_item["creator"] = user_dict.get(sheet_item["creator"], "未知")
sheet_item["update_by"] = user_dict.get(sheet_item["update_by"], "未知")
return {"message": "查询数据表成功!", "sheets": sheets}
async def query_czce_rank(query_date: str = Depends(verify_date)):
query_sql = "SELECT * FROM `czce_rank` WHERE `date`=%s;" % query_date
with MySqlZ() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"id": "ID",
"date": "日期",
"variety_en": "品种",
"contract": "合约",
"rank": "排名",
"trade_company": "公司简称",
"trade": "成交量",
"trade_increase": "成交量增减",
"long_position_company": "公司简称",
"long_position": "持买仓量",
"long_position_increase": "持买仓量增减",
"short_position_company": "公司简称",
"short_position": "持卖仓量",
"short_position_increase": "持卖仓量增减"
})
return {
"message": "郑州商品交易所{}日持仓排名数据查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def create_warehouse_number(warehouse_short_name: str = Body(..., embed=True)):
# 将简称字符转为半角
warehouse_short_name = strQ2B(warehouse_short_name)
# 查看当前名称是否已经存在
with MySqlZ() as cursor:
cursor.execute(
"SELECT `name` FROM delivery_warehouse_number WHERE `name`=%s", (warehouse_short_name, )
)
is_exist = cursor.fetchone()
if is_exist:
return {"message": "仓库已存在,无需重复添加!", 'fixed_code': 0}
# 生成fixed_code
cursor.execute(
"SELECT MAX(`id`) AS `maxid` FROM `delivery_warehouse_number`;"
)
max_id = cursor.fetchone()['maxid']
fixed_code = '%04d' % (max_id + 1)
cursor.execute(
"INSERT INTO delivery_warehouse_number (`name`,fixed_code) "
"VALUES (%s,%s);",
(warehouse_short_name, fixed_code)
)
# 将仓单表中简称为这个的填上仓库编号
cursor.execute(
"UPDATE delivery_warehouse_receipt SET `warehouse_code`=%s WHERE `warehouse_name`=%s;",
(fixed_code, warehouse_short_name)
)
return {'message': '保存成功!', 'fixed_code': fixed_code}
async def query_dce_rank_sum(query_date: str = Depends(verify_date),
rank: int = Query(20, ge=1, le=20)):
query_sql = "SELECT `date`,variety_en,contract," \
"sum(trade) AS total_trade," \
"sum(trade_increase) AS total_trade_increase," \
"sum(long_position) AS total_long_position," \
"sum(long_position_increase) AS total_long_position_increase," \
"sum(short_position) AS total_short_position," \
"sum(short_position_increase) AS total_short_position_increase," \
"(sum(long_position) - sum(short_position)) AS net_position " \
"FROM `dce_rank` " \
"WHERE `date`=%s AND `rank`>=1 AND `rank`<= %d " \
"GROUP BY variety_en;" % (query_date, rank)
with MySqlZ() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"date": "日期",
"variety_en": "品种",
"total_trade": "成交量合计(手)",
"total_trade_increase": "成交量增加合计",
"total_long_position": "持买仓量合计(手)",
"total_long_position_increase": "持买仓量增减合计",
"total_short_position": "持卖仓量合计(手)",
"total_short_position_increase": "持卖仓量增减合计",
"net_position": "净持仓(手)"
})
return {
"message": "大连商品交易所{}日持仓统计数据查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def query_dce_daily(query_date: str = Depends(verify_date)):
query_sql = "SELECT * FROM `dce_daily` WHERE `date`=%s;" % query_date
with MySqlZ() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"id": "ID",
"date": "日期",
"variety_en": "品种",
"contract": "合约",
"pre_settlement": "前结算",
"open_price": "开盘价",
"highest": "最高价",
"lowest": "最低价",
"close_price": "收盘价",
"settlement": "结算价",
"zd_1": "涨跌1",
"zd_2": "涨跌2",
"trade_volume": "成交量",
"trade_price": "成交额",
"empty_volume": "持仓量",
"increase_volume": "增减量"
})
return {
"message": "大连商品交易所{}日交易行情数据查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def query_discussion(c_page: int = Query(1, ge=1),
page_size: int = Query(20, ge=20)):
query_page = c_page - 1 # 减1处理才能查到第一页
# 查询当前页码下的数据
limit_start = query_page * page_size
with MySqlZ() as cursor:
cursor.execute(
"SELECT distb.id,distb.content,distb.create_time,"
"usertb.username,usertb.phone,usertb.avatar "
"FROM delivery_discussion AS distb "
"INNER JOIN user_user AS usertb "
"ON distb.author_id=usertb.id "
"WHERE distb.parent_id=0 "
"ORDER BY distb.create_time DESC "
"LIMIT %s,%s;", (limit_start, page_size))
records_result = cursor.fetchall()
# 查询总条数
cursor.execute(
"SELECT COUNT(distb.id) AS total FROM delivery_discussion AS distb "
"INNER JOIN user_user AS usertb ON distb.author_id=usertb.id "
"WHERE distb.parent_id=0;")
# 计算总页数
total_count = cursor.fetchone()['total']
total_page = int((total_count + page_size - 1) / page_size)
response_data = list()
for dis_item in records_result:
dis_dict = handle_discuss_item(dis_item)
# 查询回复
dis_dict['replies'] = get_sub_reply(cursor, dis_item['id'])
response_data.append(dis_dict)
return {
'message': '查询成功!',
'discussions': response_data,
'total_page': total_page
}
async def variety_contract(variety_en: str = Depends(verify_variety)):
# 从redis获取当前品种的所有合约
with RedisZ() as rs:
contracts_str = rs.get("dce{}_contracts".format(variety_en))
if contracts_str:
contracts = contracts_str.split(";")
else:
# 查询当前数据下的所有品种,保存到redis,有效期为12h
with MySqlZ() as cursor:
cursor.execute(
"SELECT `contract` FROM dce_daily "
"WHERE `variety_en`=%s "
"GROUP BY `contract`;", variety_en)
contract_indb = cursor.fetchall()
if contract_indb:
contracts_str = ';'.join(
[item['contract'] for item in contract_indb])
rs.set("dce{}_contracts".format(variety_en),
contracts_str,
ex=43200)
contracts = contracts_str.split(";")
else:
contracts = []
contracts.reverse()
return {
"message": "获取大商所{}所有合约成功!".format(variety_en),
"contracts": contracts
}
async def modify_variety_delivery_message(
variety_en: str,
user_token: str = Depends(oauth2_scheme),
delivery_msg_item: VarietyDeliveryMsgItem = Body(...)
):
if not re.match(r'^[A-Z]{1,2}$', variety_en):
raise HTTPException(status_code=400, detail="Error Param `variety_en` [A-Z]{1,2}")
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
with MySqlZ() as cursor:
cursor.execute(
"SELECT role FROM user_user WHERE id=%s AND (role='superuser' OR role='operator');", (user_id, )
)
if not cursor.fetchone():
raise HTTPException(status_code=403, detail="Operation Denied")
# 修改信息
cursor.execute(
"UPDATE delivery_variety_message SET "
"variety=%(variety)s,variety_en=%(variety_en)s,last_trade=%(last_trade)s,"
"receipt_expire=%(receipt_expire)s,delivery_unit=%(delivery_unit)s,limit_holding=%(limit_holding)s "
"WHERE variety_en=%(variety_en)s;",
jsonable_encoder(delivery_msg_item)
)
return {"message": "修改成功"}
def migrate_sheet_group():
with OldSqlZ() as cursor:
cursor.execute(
"SELECT varietytb.name_en,grouptb.name,grouptb.author_id "
"FROM info_variety_trendgroup AS grouptb "
"INNER JOIN info_variety AS varietytb "
"ON grouptb.variety_id=varietytb.id;")
groups = cursor.fetchall()
to_saves = list()
for group_item in groups:
if group_item["author_id"] == 19:
continue
if group_item["name_en"] == "PTA":
group_item["name_en"] = "TA"
if group_item["name_en"] == "GZ":
group_item["name_en"] = "GP"
if group_item["name_en"] == "GZQH":
group_item["name_en"] = "GZ"
if group_item["name_en"] == "HGJJ":
group_item["name_en"] = "HG"
if group_item["name_en"] == "GZWH":
group_item["name_en"] = "WH"
item = {
"variety_en": group_item["name_en"],
"group_name": group_item["name"]
}
to_saves.append(item)
with MySqlZ() as cursor:
cursor.executemany(
"INSERT INTO industry_sheet_group (variety_en,group_name) VALUES "
"(%(variety_en)s,%(group_name)s);", to_saves)
async def modify_short_message(
msg_id: int,
message_content: str = Body(..., embed=True),
user_token: str = Depends(oauth2_scheme),
):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
with MySqlZ() as cursor:
# 修改短信通(先尝试修改自己发的短信通)
effect_row = cursor.execute(
"UPDATE short_message SET content=%s WHERE id=%s AND creator=%s;",
(message_content, msg_id, user_id))
if effect_row == 0: # 表示这条并非自己发的
cursor.execute("SELECT id,role FROM user_user WHERE id=%s;",
(user_id, ))
user_info = cursor.fetchone()
if not user_info or user_info["role"] not in [
"operator", "superuser"
]:
raise HTTPException(status_code=401, detail="UnAuthenticated")
cursor.execute("UPDATE short_message SET content=%s WHERE id=%s;",
(
message_content,
msg_id,
))
return {"message": "操作完成!"}
async def create_update_folder(user_token: str = Depends(oauth2_scheme),
body_item: UpdateFolderItem = Body(...)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
body_item.client = encryption_uuid(body_item.client) # 加密改变uuid与客户端数据库对应
# 查询增加或更新
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,user_id FROM industry_user_folder "
"WHERE client=%s AND user_id=%s AND variety_en=%s AND group_id=%s;",
(body_item.client, user_id, body_item.variety_en,
body_item.group_id))
is_exist = cursor.fetchone()
if is_exist: # 存在则更新
cursor.execute(
"UPDATE industry_user_folder SET folder=%s "
"WHERE client=%s AND variety_en=%s AND group_id=%s AND user_id=%s;",
(body_item.folder_path, body_item.client, body_item.variety_en,
body_item.group_id, user_id))
else:
cursor.execute(
"INSERT INTO industry_user_folder (variety_en,group_id,folder,client,user_id) "
"VALUES (%s,%s,%s,%s,%s);",
(body_item.variety_en, body_item.group_id,
body_item.folder_path, body_item.client, user_id))
return {"message": "配置成功!"}
async def modify_user_information(operator_token: str = Depends(oauth2_scheme),
user_info: ModifyUserItem = Body(...)):
operate_user, _ = decipher_user_token(operator_token)
if not operate_user:
raise HTTPException(status_code=401, detail="登录信息过期了,重新登录再操作!")
if user_info.modify_id: # 管理者修改用户信息
# 查询管理者身份
with MySqlZ() as cursor:
cursor.execute("SELECT id,role FROM user_user WHERE id=%s;",
(operate_user, ))
operator = cursor.fetchone()
if not operator or operator["role"] not in [
"superuser", "operator"
]:
raise HTTPException(status_code=401, detail="非法用户或无权限,无法操作!")
if operator["role"] != "superuser" and user_info.role == "operator":
raise HTTPException(status_code=401, detail="您不能设置用户角色为运营者!")
# 进行用户信息的修改
cursor.execute(
"UPDATE user_user SET username=%(username)s,phone=%(phone)s,email=%(email)s,"
"role=%(role)s,is_active=%(is_active)s,note=%(note)s "
"WHERE id=%(modify_id)s;", jsonable_encoder(user_info))
return {"message": "修改 {} 的信息成功!".format(user_info.user_code)}
else: # 用户自己修改信息
return {"message": "修改成功!"}
async def delete_report_file(
report_id: int,
user_token: str = Depends(oauth2_scheme),
):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,filepath,creator FROM research_report WHERE id=%s;",
(report_id, ))
report_info = cursor.fetchone()
if not report_info:
raise HTTPException(status_code=400, detail="Unknown Report")
# 是否是创建者删除
if report_info["creator"] != user_id:
# 查询用户是否是管理员
cursor.execute("SELECT id,role FROM user_user WHERE id=%s;",
(user_id, ))
user_info = cursor.fetchone()
if not user_info:
raise HTTPException(status_code=401, detail="Unknown User")
if user_info["role"] not in ["superuser", "operator"]:
return {"message": "不能删除别人上传的报告!"}
# 删除报告
cursor.execute("DELETE FROM research_report WHERE id=%s;",
(report_id, ))
report_path = os.path.join(FILE_STORAGE, report_info["filepath"])
if os.path.exists(report_path) and os.path.isfile(report_path):
os.remove(report_path)
return {"message": "删除成功!"}
async def save_delivery_receipt(
user_token: str = Depends(oauth2_scheme),
receipt_item: List[ReceiptItem] = Body(...)
):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="UnAuthorization")
with MySqlZ() as cursor:
cursor.execute(
"SELECT id, role FROM user_user WHERE id=%s;", (user_id, )
)
user_role = cursor.fetchone()
if not user_role or user_role["role"] not in ["superuser", "operator"]:
raise HTTPException(status_code=401, detail="UnAuthorization")
# 查询今日仓单是否已经存在,存在则不添加
today = receipt_item[0].date
cursor.execute("SELECT `date` FROM delivery_warehouse_receipt WHERE `date`=%s;", (today, ))
if cursor.fetchone():
raise HTTPException(status_code=403, detail="Today Receipts Exist.")
# 保存数据到数据库
save_count = cursor.executemany(
"INSERT INTO delivery_warehouse_receipt (warehouse_code,warehouse_name,variety,"
"variety_en,`date`,receipt,increase) VALUES (%(warehouse_code)s,%(warehouse_name)s,%(variety)s,"
"%(variety_en)s,%(date)s,%(receipt)s,%(increase)s);",
jsonable_encoder(receipt_item)
)
return {"message": "保存成功!", "save_count": save_count}
async def register(
is_image_code_passed: bool = Depends(checked_image_code),
phone: str = Form(...),
nickname: str = Form(""),
email: str = Form(""),
weixin: str = Form(""),
password: str = Form(...),
):
if not is_image_code_passed:
raise HTTPException(status_code=400, detail="Got an error image code.")
print("phone:%s\n nickname:%s\n password:%s" % (phone, nickname, password))
time.sleep(3)
# 将用户信息保存到数据库中
user_to_save = UserInDB(
unique_code=verify.generate_user_unique_code(), # 生成系统号
username=nickname,
phone=phone,
email=email,
weixin=weixin,
password_hashed=verify.get_password_hash(password) # hash用户密码
)
with MySqlZ() as cursor:
cursor.execute(
"INSERT INTO `user_user` (`unique_code`,`username`,`phone`,`email`,`weixin`,`password_hashed`) "
"VALUES (%(unique_code)s,%(username)s,%(phone)s,%(email)s,%(weixin)s,%(password_hashed)s);",
(jsonable_encoder(user_to_save)))
back_user = User(unique_code=user_to_save.unique_code,
username=user_to_save.username,
phone=user_to_save.phone,
email=user_to_save.email,
weixin=user_to_save.weixin)
return {"message": "注册成功!", "user": back_user}
async def add_basic_variety(
variety: VarietyItem = Body(...)
):
variety_item = {
"variety_name": variety.variety_name,
"variety_en": variety.variety_en,
"exchange_lib": variety.exchange_lib.name,
"group_name": variety.group_name.name,
}
try:
with MySqlZ() as cursor:
cursor.execute(
"INSERT INTO `basic_variety`"
"(`variety_name`,`variety_en`,`exchange_lib`,`group_name`) "
"VALUES (%(variety_name)s,%(variety_en)s,%(exchange_lib)s,%(group_name)s);",
variety_item
)
except IntegrityError:
raise HTTPException(
detail="variety_name and variety_en team repeated!",
status_code=400
)
except ProgrammingError:
raise HTTPException(
detail="The app inner error.created variety fail!",
status_code=500
)
return {"message": "添加品种成功!", "new_variety": variety}
async def modify_client(client_id: int,
client: ModifyClient = Body(...),
user_token: str = Depends(oauth2_scheme)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
with MySqlZ() as cursor:
cursor.execute("SELECT id,role FROM user_user WHERE id=%s;",
(user_id, ))
operator = cursor.fetchone()
if not operator or operator["role"] not in ["superuser", "operator"]:
raise HTTPException(status_code=401, detail="Unknown User")
# 验证is_active
if client.is_active not in [0, 1]:
raise HTTPException(status_code=400, detail="Error Params")
# 验证is_manager
if client.is_manager not in [0, 1]:
raise HTTPException(status_code=400, detail="Error Params")
if client.client_id != client_id:
raise HTTPException(status_code=400, detail="Client Error")
# 修改客户端信息
cursor.execute(
"UPDATE basic_client SET client_name=%(client_name)s,is_manager=%(is_manager)s,"
"is_active=%(is_active)s WHERE id=%(client_id)s;",
jsonable_encoder(client))
return {"message": "修改{}客户端信息成功!".format(client.client_uuid)}
async def get_sheet_charts(request: Request,
sheet_id: int,
is_own: int = Query(0, ge=0, le=1),
token: str = Query('')):
user_id, _ = decipher_user_token(token)
if not user_id:
return {"message": "UnAuthenticated!"}
# 查询这个表格所有图形信息
with MySqlZ() as cursor:
cursor.execute(
"SELECT id, DATE_FORMAT(create_time,'%%Y-%%m-%%d') AS create_time,creator,"
"title,variety_en,sheet_id,option_file,decipherment,suffix,is_principal,is_petit "
"FROM industry_user_chart "
"WHERE IF(%s=creator,TRUE,is_private=0) AND IF(%s=0,TRUE,creator=%s) AND sheet_id=%s "
"ORDER BY suffix ASC;", (
user_id,
is_own,
user_id,
sheet_id,
))
charts = cursor.fetchall()
return template.TemplateResponse("sheet_charts.html", {
"request": request,
"has_chart": len(charts),
"sheet_charts": charts
})
async def delete_chart(chart_id: int,
user_token: str = Depends(oauth2_scheme)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
# 查询图形信息
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,creator,option_file FROM industry_user_chart WHERE id=%s;",
(chart_id, ))
chart_info = cursor.fetchone()
if not chart_info:
raise HTTPException(status_code=400, detail="Unknown Chart")
if chart_info["creator"] != user_id:
# 查询操作用户信息
cursor.execute("SELECT id,role FROM user_user WHERE id=%s;",
(user_id, ))
operator = cursor.fetchone()
if not operator or operator["role"] not in [
"superuser", "operator"
]:
raise HTTPException(status_code=403,
detail="Can not Delete Chart ")
# 删除图形和配置
option_file = os.path.join(FILE_STORAGE, chart_info["option_file"])
if os.path.exists(option_file):
os.remove(option_file)
cursor.execute("DELETE FROM industry_user_chart WHERE id=%s;",
(chart_id, ))
return {"message": "删除成功!"}
async def chart_decipherment(chart_id: int,
decipherment: str = Body(..., embed=True)):
with MySqlZ() as cursor:
cursor.execute(
"UPDATE industry_user_chart SET decipherment=%s WHERE id=%s;",
(decipherment, chart_id))
return {"message": "修改成功!"}
async def register(is_image_code_passed: bool = Depends(checked_image_code),
role: str = Depends(get_default_role),
phone: str = Form(...),
username: str = Form(""),
email: str = Form(""),
password: str = Form(...),
client_uuid: str = Form(...)):
if not is_image_code_passed:
return {"message": "验证码有误!", "user": {}}
time.sleep(3)
# 解码phone和password
phone = base64.b64decode(phone.encode('utf-8')).decode('utf-8')
password = base64.b64decode(password.encode('utf-8')).decode('utf-8')
# 手动验证邮箱和手机号
if not re.match(
r'^([a-zA-Z0-9]+[_|\_|\.]?)*[a-zA-Z0-9]+@([a-zA-Z0-9]+[_|\_|\.]?)*[a-zA-Z0-9]+\.[a-zA-Z]{2,3}$',
email):
return {"message": "邮箱格式有误!", "user": {}}
if not re.match(r'^[1][3-9][0-9]{9}$', phone):
return {"message": "手机号格式有误!", "user": {}}
# 将用户信息保存到数据库中
user_to_save = UserInDB(
user_code=verify.generate_user_unique_code(),
username=username,
phone=phone,
email=email,
role=role,
password_hashed=verify.get_password_hash(password) # hash用户密码
)
try:
with MySqlZ() as cursor:
cursor.execute(
"INSERT INTO `user_user` (`user_code`,`username`,`phone`,`email`,`password_hashed`,`role`) "
"VALUES (%(user_code)s,%(username)s,%(phone)s,%(email)s,%(password_hashed)s,%(role)s);",
(jsonable_encoder(user_to_save)))
# 创建用户可登录的客户端
new_user_id = cursor._instance.insert_id()
cursor.execute(
"SELECT `id`,client_name FROM `basic_client` WHERE machine_uuid=%s;",
client_uuid)
client_info = cursor.fetchone()
if not client_info:
raise ClientNotFound("Client Not Found")
cursor.execute(
"INSERT INTO `user_user_client` (user_id,client_id,expire_date) "
"VALUES (%s,%s,%s);",
(new_user_id, client_info["id"], "3000-01-01"))
except IntegrityError as e:
logger.error("用户注册失败:{}".format(e))
return {"message": "手机号已存在!", "user": {}}
except ClientNotFound:
return {"message": "无效客户端,无法注册!", "user": {}}
back_user = User(user_code=user_to_save.user_code,
username=user_to_save.username,
phone=user_to_save.phone,
email=user_to_save.email,
role=user_to_save.role)
return {"message": "注册成功!", "user": back_user}
async def get_client_with_uuid(client_uuid: str):
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,DATE_FORMAT(join_time,'%%Y-%%m-%%d') AS join_date,client_name,"
"machine_uuid,is_manager,is_active "
"FROM basic_client WHERE machine_uuid=%s;", (client_uuid, ))
clients = cursor.fetchall()
return {"message": "查询成功!", "clients": clients}
async def chart_base_info(chart_id: int):
with MySqlZ() as cursor:
cursor.execute(
"SELECT title,variety_en,decipherment,suffix "
"FROM industry_user_chart WHERE id=%s;", (chart_id, ))
chart = cursor.fetchone()
return {"message": "查询成功!", "data": chart}
