async def create_role(response: Response, body: pb.GetRoleBody):
sql = """
INSERT INTO ef_role (name, info)
SELECT "{}", "{}" FROM DUAL
WHERE NOT EXISTS (SELECT * FROM ef_role WHERE name="{}");
""".format(body.name, body.info, body.name)
change = cursor.execute(sql)
if change == const.NOCHANGE:
response.status_code = status.HTTP_422_UNPROCESSABLE_ENTITY
err_results = funcs.gen_error("10104", "Role exist")
return err_results
db.commit()
response.status_code = status.HTTP_201_CREATED
err_results = funcs.gen_error("201", "Account created successfully")
return err_results
async def create_user(response: Response, body: pb.GetUserBody):
sql = """
INSERT INTO ef_user (account, password, name, mobilephone)
SELECT "{}", "{}", "{}", "{}" FROM DUAL
WHERE NOT EXISTS (SELECT * FROM ef_user WHERE account="{}");
""".format(body.account, body.password, body.name, body.mobilephone,
body.account)
change = cursor.execute(sql)
if change == const.NOCHANGE:
response.status_code = status.HTTP_422_UNPROCESSABLE_ENTITY
err_results = funcs.gen_error("10103", "Account exist")
return err_results
db.commit()
response.status_code = status.HTTP_201_CREATED
err_results = funcs.gen_error("201", "Account created successfully")
return err_results
async def delete_role(response: Response, role_id: int):
sql = """
DELETE FROM ef_role
WHERE id={}
""".format(role_id)
cursor.execute(sql)
db.commit()
response.status_code = status.HTTP_204_NO_CONTENT
err_results = funcs.gen_error("204", "Successfully deleted")
return err_results
async def update_role(response: Response, body: pb.UpdateRoleBody,
role_id: int):
sql = """
UPDATE ef_role
SET name="{}", info="{}"
WHERE id={}
""".format(body.name, body.info, role_id)
cursor.execute(sql)
db.commit()
response.status_code = status.HTTP_201_CREATED
err_results = funcs.gen_error("201", "Successfully modified")
return err_results
async def update_user(response: Response, body: pb.UpdateUserBody,
user_id: int):
sql = """
UPDATE ef_user
SET account="{}", password="******", mobilephone="{}"
WHERE id={}
""".format(body.account, body.password, body.mobilephone, user_id)
cursor.execute(sql)
db.commit()
response.status_code = status.HTTP_201_CREATED
err_results = funcs.gen_error("201", "Successfully modified")
return err_results
async def create_user_role(response: Response, body: pb.UserRoleBody,
user_id: int):
sql = """
INSERT INTO ef_user_role (user_id, role_id)
VALUES ({}, {})
ON DUPLICATE KEY UPDATE
role_id={}
""".format(user_id, body.role_id, body.role_id)
cursor.execute(sql)
db.commit()
response.status_code = status.HTTP_201_CREATED
err_results = funcs.gen_error("201", "Successfully modified")
return err_results
async def login(response: Response, body: pb.LoginBody):
sql = """
SELECT id, account, password, name, mobilephone, is_admin, status FROM ef_user
WHERE account="{}" AND status={}
""".format(body.account, const.VALIDUSER)
cursor.execute(sql)
select_results = cursor.fetchall()
if len(select_results) == 0:
response.status_code = status.HTTP_404_NOT_FOUND
err_results = funcs.gen_error("10101", "Invaild account")
return err_results
results = dict()
results["use_id"] = select_results[0][0]
results["account"] = select_results[0][1]
results["password"] = select_results[0][2]
if body.password != results["password"]:
response.status_code = status.HTTP_401_UNAUTHORIZED
err_results = funcs.gen_error("10102", "Password is wrong")
return err_results
results["user_name"] = select_results[0][3]
results["mobilephone"] = select_results[0][4]
results["is_admin"] = select_results[0][5]
results["use_status"] = select_results[0][6]
sql = """SELECT role_id FROM ef_user_role
WHERE user_id={}
""".format(results["use_id"])
cursor.execute(sql)
select_results = cursor.fetchall()
if len(select_results) == 0:
results["role_id"] = ""
else:
results["role_id"] = select_results[0][0]
sql = """SELECT name, info, status FROM ef_role
WHERE id={}
""".format(results["role_id"])
cursor.execute(sql)
select_results = cursor.fetchall()
results["role_name"] = select_results[0][0]
results["role_info"] = select_results[0][1]
results["role_status"] = select_results[0][2]
sql = """SELECT b.id, b.name
FROM ef_role_access a INNER JOIN ef_access b
WHERE a.access_id=b.id AND a.role_id = {}
ORDER BY b.id ASC
""".format(results["role_id"])
cursor.execute(sql)
select_results = cursor.fetchall()
for row in select_results:
results[row[0]] = row[1]
return results
