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 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 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 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 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 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": "修改成功"}
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 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 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 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 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_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 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 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 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 create_advertisement(
user_token: str = Depends(oauth2_scheme),
image: UploadFile = Form(...),
pdf_file: UploadFile = Form(None),
title: str = Form(...),
ad_type: str = Form(...),
web_url: str = Form(''),
content: str = Form(''),
note: str = Form(''),
):
# 验证用户写入数据库
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
# 验证ad_type
if ad_type not in ["file", "web", "content"]:
raise HTTPException(status_code=400,
detail="Unknown Advertisement Type!")
# 创建广告文件保存的文件夹
image_folder = os.path.join(FILE_STORAGE, "ADVERTISEMENT/Image/")
file_folder = os.path.join(FILE_STORAGE, "ADVERTISEMENT/File/")
if not os.path.exists(image_folder):
os.makedirs(image_folder)
if not os.path.exists(file_folder):
os.makedirs(file_folder)
# 生成filename
image_filename = generate_random_filename() + ".png"
image_path = os.path.join(image_folder, image_filename)
image_sql_path = os.path.join("ADVERTISEMENT/Image/", image_filename)
pdf_path, pdf_sql_path = '', ''
if pdf_file:
pdf_filename = generate_random_filename() + ".pdf"
pdf_path = os.path.join(file_folder, pdf_filename)
pdf_sql_path = os.path.join("ADVERTISEMENT/File/", pdf_filename)
# 数据保存并入库
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 or user_info["role"] not in ["superuser", "operator"]:
raise HTTPException(status_code=400, detail="没有权限进行这个操作!")
# 将数据保存起来并入库
cursor.execute(
"INSERT INTO homepage_advertisement(title,ad_type,image,filepath,web_url,content,note) "
"VALUES (%s,%s,%s,%s,%s,%s,%s);",
(title, ad_type, image_sql_path, pdf_sql_path, web_url, content,
note))
# 保存文件
image_content = await image.read() # 将文件保存到目标位置
with open(image_path, "wb") as fp:
fp.write(image_content)
await image.close()
if pdf_file:
pdf_content = await pdf_file.read()
with open(pdf_path, "wb") as fp:
fp.write(pdf_content)
await pdf_file.close()
return {"message": "创建新广告成功!"}
async def change_report_info(
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(
"UPDATE research_report SET is_active=IF(is_active=0,1,0) "
"WHERE creator=%s AND id=%s;", (user_id, report_id))
return {"message": "修改成功!"}
async def modify_sheet_public(sheet_id: int,
user_token=Depends(oauth2_scheme),
is_private: int = Body(...,
ge=0,
le=1,
embed=True)):
user_id, _ = decipher_user_token(user_token)
with MySqlZ() as cursor:
cursor.execute(
"UPDATE industry_user_sheet SET is_private=%s "
"WHERE id=%s AND creator=%s;", (is_private, sheet_id, user_id))
return {"message": "修改成功!"}
async def chart_display(chart_id: int,
user_token=Depends(oauth2_scheme),
is_principal: int = Query(0, ge=0, le=2),
is_petit: int = Query(0, ge=0, le=1),
is_private: int = Query(0, ge=0, le=1)):
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(
"UPDATE industry_user_chart SET is_principal=%s,is_petit=%s,is_private=%s WHERE id=%s;",
(str(is_principal), is_petit, is_private, chart_id))
return {"message": "设置成功!"}
async def user_token_logged(client: str = Query(...,
min_length=36,
max_length=36),
token: str = Depends(verify.oauth2_scheme)):
user_id, user_code = verify.decipher_user_token(token)
if not user_id:
raise HTTPException(status_code=401, detail="登录失败!Token Expired!")
# 查询用户的有效与能否在此客户端登录
with MySqlZ() as cursor:
cursor.execute(
"SELECT `id`,role,username,note,is_active FROM user_user WHERE id=%s;",
(user_id, ))
user_info = cursor.fetchone()
if not user_info:
raise HTTPException(status_code=401, detail="登录失败!USER NOT FOUND!")
cursor.execute(
"SELECT `id`,is_manager FROM basic_client WHERE machine_uuid=%s;",
(client, ))
client_info = cursor.fetchone()
if not client_info:
raise HTTPException(status_code=401, detail="登录失败!INVALID CLIENT!")
today_str = datetime.today().strftime("%Y-%m-%d")
# 1 创建今日在线的数据库
cursor.execute(
"SELECT `id`,online_date FROM user_user_online WHERE `user_id`=%s AND online_date=%s;",
(user_info["id"], today_str))
is_today_online = cursor.fetchone()
if not is_today_online: # 今日还未登录
cursor.execute(
"INSERT INTO user_user_online (user_id,online_date,total_online) VALUES (%s,%s,%s);",
(user_info["id"], today_str, 0))
# 2 非超管和运营查询当前用户是否能在这个客户端登录
if user_info["role"] not in ["superuser", "operator"]:
cursor.execute(
"SELECT userclient.id, userclient.user_id FROM user_user_client AS userclient "
"INNER JOIN basic_client AS clienttb "
"ON userclient.client_id=clienttb.id AND userclient.user_id=%s AND clienttb.machine_uuid=%s "
"AND userclient.expire_date>%s;",
(user_info["id"], client, today_str))
is_client_accessed = cursor.fetchone()
if not is_client_accessed:
raise HTTPException(status_code=403,
detail="Can not login with the client.")
return {
"message": "token登录成功!",
"show_username": user_info["username"],
}
async def sheet_chart(sheet_id: int,
user_token: str = Depends(oauth2_scheme),
chart_option: ChartOption = Body(...)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Token Expired!")
if not all(
[chart_option.title, chart_option.variety_en, chart_option.option]):
raise HTTPException(status_code=400, detail="Option Error!")
relative_path = "ChartOption/{}".format(
generate_chart_option_filepath(user_id))
option_file = os.path.join(FILE_STORAGE, relative_path)
# 创建文件夹
file_folder = os.path.split(option_file)[0]
if not os.path.exists(file_folder):
os.makedirs(file_folder)
with open(option_file, 'w', encoding='utf-8') as fp:
json.dump(chart_option.option, fp, indent=4)
# 保存到数据库
save_dict = {
"creator": user_id,
"title": chart_option.title,
"variety_en": chart_option.variety_en,
"sheet_id": sheet_id,
"option_file": relative_path,
"decipherment": chart_option.decipherment,
"is_private": 1 if chart_option.is_private else 0
}
try:
with MySqlZ() as cursor:
cursor.execute(
"INSERT INTO industry_user_chart "
"(creator,title,variety_en,sheet_id,option_file,decipherment,is_private) "
"VALUES (%(creator)s,%(title)s,%(variety_en)s,%(sheet_id)s,%(option_file)s,"
"%(decipherment)s,%(is_private)s);", save_dict)
# 更新后缀
new_id = cursor._instance.insert_id()
cursor.execute(
"UPDATE industry_user_chart SET suffix=id WHERE id=%s;",
(new_id, ))
except Exception as e:
# 保存失败删除已保存的json文件
if os.path.exists(option_file):
os.remove(option_file)
logger.error("用户保存图形配置失败:{}".format(e))
return {"message": "保存图形失败"}
else:
return {"message": "保存图形配置成功!"}
async def create_discussion(user_token: str = Depends(oauth2_scheme),
discussion_item: DiscussionItem = Body(...)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="UnAuthorization User")
# 加入数据库
data = jsonable_encoder(discussion_item)
data["author_id"] = user_id
replies = list()
with MySqlZ() as cursor:
cursor.execute(
"INSERT INTO delivery_discussion (author_id,content,parent_id) VALUES "
"(%(author_id)s,%(content)s,%(parent_id)s);", data)
if discussion_item.parent_id:
replies = get_sub_reply(cursor, discussion_item.parent_id)
return {"message": "操作成功!", "replies": replies}
async def create_report(user_token: str = Depends(oauth2_scheme),
report_file: UploadFile = Form(...),
date: str = Form(...),
relative_varieties: str = Form(...),
report_type: str = Form(...),
rename_text: str = Form('')):
# 从网络上传的文件信息保存报告
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
# 验证report_type:
if report_type not in [
"daily", "weekly", "monthly", "annual", "special", "others"
]:
raise HTTPException(status_code=400, detail="Unknown Report Type")
# 创建保存的文件夹
date_folder = date[:7] # 以月为单位保存
variety_en = relative_varieties.split(';')[0]
# 创建新文件所在路径
save_folder = "REPORTS/{}/{}/{}/".format(variety_en, report_type,
date_folder)
report_folder = os.path.join(FILE_STORAGE, save_folder)
if not os.path.exists(report_folder):
os.makedirs(report_folder)
filename = report_file.filename
if rename_text:
filename = rename_text + ".pdf"
title = os.path.splitext(filename)[0]
report_path = os.path.join(report_folder, filename)
sql_path = os.path.join(save_folder, filename)
# 创建数据库记录
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,filepath FROM research_report WHERE filepath=%s;",
(sql_path, ))
if not cursor.fetchone():
cursor.execute(
"INSERT INTO research_report (`date`,creator,variety_en,title,report_type,filepath) "
"VALUES (%s,%s,%s,%s,%s,%s);",
(date, user_id, relative_varieties, title, report_type,
sql_path))
content = await report_file.read() # 将文件保存到目标位置
with open(report_path, "wb") as fp:
fp.write(content)
await report_file.close()
return {"message": "上传成功!"}
async def create_report_with_wechat_files(
user_token: str = Depends(oauth2_scheme),
relative_path: str = Path(...),
file_item: ReportFileItem = Body(...)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
file_item.date = verify_date_time(file_item.date) # 验证处理datetime时间
# 验证report_type:
if file_item.report_type not in [
"daily", "weekly", "monthly", "annual", "special", "others"
]:
raise HTTPException(status_code=400, detail="Unknown Report Type")
# 从微信自动保存的文件信息创建报告,
# 报告的相对文件路径(日报日期精确到日): REPORTS/{variety_en}/{report_type}/{年-月[-日]}/xxx.pdf
filepath = os.path.join(WECHAT_FILE_PATH, relative_path) # 原文件所在路径
filename = os.path.split(filepath)[1]
# 新名称
if file_item.rename_text:
filename = file_item.rename_text + ".pdf"
title = os.path.splitext(filename)[0]
variety_en = file_item.relative_varieties.split(';')[0]
date_folder = file_item.date[:7] # 以月为单位保存
# 创建新文件所在路径
save_folder = "REPORTS/{}/{}/{}/".format(variety_en, file_item.report_type,
date_folder)
report_folder = os.path.join(FILE_STORAGE, save_folder)
if not os.path.exists(report_folder):
os.makedirs(report_folder)
report_path = os.path.join(report_folder, filename)
sql_path = os.path.join(save_folder, filename)
# 创建数据库记录
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,filepath FROM research_report WHERE filepath=%s;",
(sql_path, ))
if not cursor.fetchone():
cursor.execute(
"INSERT INTO research_report (`date`,creator,variety_en,title,report_type,filepath) "
"VALUES (%s,%s,%s,%s,%s,%s);",
(file_item.date, user_id, file_item.relative_varieties, title,
file_item.report_type, sql_path))
shutil.move(filepath, report_path) # 将文件移动到目标位置
return {"message": "操作成功!"}
async def modify_warehouse_variety(
fixed_code: str = Depends(verify_warehouse_fixed_code),
user_token: str = Depends(oauth2_scheme),
delivery_item: DeliveryVarietyItem = Body(...)
):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail='Unknown User')
if delivery_item.is_delivery not in [0, 1]:
raise HTTPException(status_code=400, detail='Error Params: `is_delivery` must be 0 or 1')
with MySqlZ() as cursor:
# 查询用户角色
cursor.execute(
"SELECT id,role FROM user_user WHERE id=%s AND role='superuser' OR role='operator';",
(user_id, )
)
if not cursor.fetchone():
raise HTTPException(status_code=401, detail="Operation Disabled")
if delivery_item.is_delivery: # 有交割(数据库无设置唯一索引,乖乖查吧)
cursor.execute(
"SELECT warehouse_code FROM delivery_warehouse_variety WHERE warehouse_code=%s AND variety_en=%s;",
(fixed_code, delivery_item.variety_en)
)
if cursor.fetchone(): # 存在更新
cursor.execute(
"UPDATE delivery_warehouse_variety SET variety=%(variety)s,variety_en=%(variety_en)s,"
"linkman=%(linkman)s,links=%(links)s,premium=%(premium)s,receipt_unit=%(receipt_unit)s,"
"is_active=1 WHERE warehouse_code=%(warehouse_code)s AND variety_en=%(variety_en)s;",
jsonable_encoder(delivery_item)
)
else: # 不存在新增
cursor.execute(
"INSERT INTO delivery_warehouse_variety (warehouse_code,variety,variety_en,linkman,links,"
"premium,receipt_unit,is_active) VALUES "
"(%(warehouse_code)s,%(variety)s,%(variety_en)s,%(linkman)s,%(links)s,"
"%(premium)s,%(receipt_unit)s,1);",
jsonable_encoder(delivery_item)
)
else: # 无交割
cursor.execute(
"UPDATE delivery_warehouse_variety SET is_active=0 WHERE warehouse_code=%s AND variety_en=%s;",
(fixed_code, delivery_item.variety_en)
)
return {"message": "修改成功!"}
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": {}, "modules": []}
# 查询用户的模块权限
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,username,user_code,role FROM user_user WHERE id=%s;",
(query_user, ))
user_info = cursor.fetchone()
cursor.execute(
"SELECT id,user_id,module_id,module_text,expire_date "
"FROM user_user_module WHERE user_id=%s;", (user_info["id"]))
data = cursor.fetchall()
return {"message": "查询用户模块权限成功!", "user": user_info, "modules": data}
async def get_update_folder(user_token: str = Depends(oauth2_scheme),
variety_en: str = Query(...),
group_id: int = Query(0, ge=0),
client: str = Query('',
min_length=36,
max_length=36)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="Unknown User")
client = encryption_uuid(client) # 加密uuid与数据库对应
with MySqlZ() as cursor:
cursor.execute(
"SELECT varitytb.variety_name,grouptb.group_name,foldertb.folder "
"FROM industry_user_folder AS foldertb,basic_variety AS varitytb,industry_sheet_group AS grouptb "
"WHERE foldertb.variety_en=varitytb.variety_en "
"AND foldertb.group_id=grouptb.id AND "
"foldertb.client=%s AND foldertb.variety_en=%s AND foldertb.user_id=%s AND "
"IF(%s=0,TRUE,foldertb.group_id=%s);",
(client, variety_en, user_id, group_id, group_id))
folders = cursor.fetchall()
return {"message": "查询成功!", "folders": folders}
async def delete_sheet(sheet_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,db_table FROM industry_user_sheet "
"WHERE id=%s;", (sheet_id, ))
sheet_info = cursor.fetchone()
if not sheet_info:
raise HTTPException(status_code=400, detail="Unknown Sheet")
if sheet_info["creator"] != user_id:
# 用户删除的是别人的表
# 查询用户的身份
cursor.execute("SELECT id,role FROM user_user WHERE id=%s;",
(user_id, ))
operator = cursor.fetchone()
if not operator:
raise HTTPException(status_code=401, detail="Unknown User")
if operator["role"] not in ["superuser", "operator"]:
raise HTTPException(status_code=403,
detail="Can not Delete Sheet")
# 如果是删除自己的表(或管理员删除),删除相应的原数据和已作图的表
cursor.execute(
"SELECT id,creator,option_file FROM industry_user_chart "
"WHERE sheet_id=%s;", (sheet_id, ))
sheet_charts = cursor.fetchall()
for chart_item in sheet_charts: # 删除数据图形和图形配置文件
option_file = os.path.join(FILE_STORAGE, chart_item["option_file"])
if os.path.exists(option_file):
os.remove(option_file)
cursor.execute("DELETE FROM industry_user_chart WHERE id=%s;",
(chart_item["id"]))
# 删除数据表
delete_variety_sheet(sheet_info["db_table"])
cursor.execute("DELETE FROM industry_user_sheet WHERE id=%s;",
(sheet_id, ))
return {"message": "删除成功!本表相关联数据已被清除."}
async def update_user_online(machine_uuid: str = Query(...,
min_length=36,
max_length=36),
token: str = Depends(oauth2_scheme)):
user_id, user_code = decipher_user_token(token)
today_str = datetime.today().strftime("%Y-%m-%d")
with MySqlZ() as cursor:
cursor.execute(
"SELECT `id`,machine_uuid FROM `basic_client` WHERE machine_uuid=%s;",
machine_uuid)
client_info = cursor.fetchone()
if client_info: # 客户端存在查询今日是否有记录,有则追加更新,无则新建一条2分钟的记录
cursor.execute(
"SELECT `id` online_date FROM basic_client_online WHERE online_date=%s AND client_id=%s;",
(today_str, client_info["id"]))
if cursor.fetchone():
cursor.execute(
"UPDATE `basic_client_online` "
"SET total_online=total_online+2 "
"WHERE online_date=%s AND client_id=%s;",
(today_str, client_info["id"]))
else:
cursor.execute(
"INSERT INTO basic_client_online (client_id,online_date,total_online) "
"VALUES (%s,%s,%s);", (client_info["id"], today_str, 2))
if user_id:
cursor.execute(
"SELECT id,online_date FROM user_user_online WHERE online_date=%s AND user_id=%s;",
(today_str, user_id))
if cursor.fetchone():
cursor.execute(
"UPDATE `user_user_online` SET total_online=total_online+2 "
"WHERE online_date=%s AND user_id=%s;",
(today_str, user_id))
else:
cursor.execute(
"INSERT INTO user_user_online (user_id,online_date,total_online) "
"VALUES (%s,%s,%s);", (user_id, today_str, 2))
return {"message": "用户更新在线时间成功!"}
async def create_sheet_group(variety_en: str = Depends(verify_variety),
user_token: str = Depends(oauth2_scheme),
group_name: str = Body(..., embed=True)):
user_id, _ = decipher_user_token(user_token)
if not user_id:
raise HTTPException(status_code=401, detail="登录信息失效了")
with MySqlZ() as cursor:
cursor.execute(
"SELECT id,variety_en FROM basic_variety WHERE variety_en=%s;",
(variety_en, ))
variety_info = cursor.fetchone()
if not variety_info:
raise HTTPException(status_code=403, detail="不能为不存在的品种添加分组")
cursor.execute(
"INSERT INTO industry_sheet_group (variety_en, group_name) VALUES (%s,%s);",
(variety_info["variety_en"], group_name))
cursor.execute(
"SELECT id,variety_en,group_name FROM industry_sheet_group "
"WHERE variety_en=%s AND is_active=1 ORDER BY suffix DESC;",
(variety_info["variety_en"], ))
groups = cursor.fetchall()
return {"message": "创建数据分组成功!", "groups": groups}
