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}