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}