def push_data(u: User):
ds = datasource()
ds.connect()
SQL = """
UPDATE USERDATA SET
EMAIL = %s,
PASSWORD_HASH = %s,
NAME = %s,
LASTNAME = %s,
CLASS = %s,
PERMISSION_LEVEL = %s,
LAST_IP = %s,
ACTIVE = %s,
TEMPHASH = %s
WHERE ID = %s
"""
PARAM = (
u.EMAIL,
u.PASSWORD_HASH,
u.NAME,
u.LASTNAME,
u.CLASS,
u.PERMISSION_LEVEL,
str(u.LAST_IP),
u.ACTIVE,
u.TEMPHASH,
u.ID
)
ds.execute(SQL, PARAM)
ds.commit()
ds.close()
def push_data(c: Clickboard):
ds = datasource()
ds.connect()
SQL = """
UPDATE CLICKBOARDS SET
NAME = %s,
AUTHOR = %s,
SHORT_DESCRIPTION = %s,
IMG_URL = %s,
DOK_URL = %s,
SCH_URL = %s,
BRD_URL = %s,
STP_URL = %s
WHERE ID = %s
"""
PARAM = (
c.NAME,
c.AUTHOR,
c.SHORT_DESCRIPTION,
c.IMG_URL,
c.DOK_URL,
c.SCH_URL,
c.BRD_URL,
c.STP_URL
)
ds.execute(SQL, PARAM)
ds.commit()
ds.close()
def is_teacher(EMAIL: str):
ds = datasource()
ds.connect()
ds.execute("SELECT EMAIL FROM GLOBAL_TEACHERS WHERE EMAIL = %s", (EMAIL,))
data: str = ds.fetch_row()
ds.close()
if data is not None:
if data[0].casefold() == EMAIL.casefold():
return True
return False
return False
async def activate_account(hash: str):
checkedhash = htmlspecialchars(hash)
ds = datasource()
ds.connect()
SQL = "UPDATE USERDATA SET ACTIVE = 1, TEMPHASH = NULL WHERE TEMPHASH = %s"
PARAM = (checkedhash,)
ds.execute(SQL, PARAM)
ds.commit()
ds.close()
return RedirectResponse(url='https://toolbox.philsoft.at')
def get_all_clickboards():
ds = datasource()
ds.connect()
ds.execute("SELECT CLICKBOARDS.*, USERDATA.NAME, USERDATA.LASTNAME FROM CLICKBOARDS JOIN USERDATA ON CLICKBOARDS.AUTHOR = USERDATA.ID")
allClickboards = list()
data = ds.fetch_all()
ds.close()
for x in data:
allClickboards.append(fetch_data(x))
return allClickboards
def get_all_users():
ds = datasource()
ds.connect()
ds.execute("SELECT * FROM USERDATA")
allUsers = dict()
data = ds.fetch_all()
ds.close()
for x in data:
allUsers[x[0]] = fetch_data(x)
return remove_passwordhash(allUsers)
def get_clickboard(ID: PositiveInt):
ds = datasource()
ds.connect()
if ID is not None:
ds.execute("""
SELECT CLICKBOARDS.*, USERDATA.NAME, USERDATA.LASTNAME FROM CLICKBOARDS JOIN USERDATA
ON CLICKBOARDS.AUTHOR = USERDATA.ID
WHERE CLICKBOARDS.ID = %s
""", (ID,))
data = ds.fetch_row()
ds.close()
if data is not None:
return Clickboard(**data)
else:
return ValueError('No clickboard found')
def getApp(ID: Optional[int] = None,
NAME: Optional[str] = None,
API_KEY: Optional[str] = None):
ds = datasource()
ds.connect()
global SQL, PARAM
if ID is not None:
SQL = "SELECT * FROM toolbox.APPS WHERE ID = %s"
PARAM = (ID, )
elif NAME is not None:
SQL = "SELECT * FROM toolbox.APPS WHERE NAME = %s"
PARAM = (NAME, )
elif API_KEY is not None:
SQL = "SELECT * FROM toolbox.APPS WHERE API_KEY = %s"
PARAM = (API_KEY, )
ds.execute(SQL, PARAM)
data = ds.fetch_dict()
ds.close()
return App(**data)
def create_clickboard(c: TempClickboard):
ds = datasource()
ds.connect()
SQL = """
INSERT INTO CLICKBOARDS (NAME, AUTHOR, SHORT_DESCRIPTION, IMG_URL, DOK_URL, SCH_URL, BRD_URL, STP_URL)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
"""
PARAM = (
c.NAME,
c.AUTHOR_ID,
c.SHORT_DESCRIPTION,
c.IMG_URL,
c.DOK_URL,
c.SCH_URL,
c.BRD_URL,
c.STP_URL
)
ds.execute(SQL, PARAM)
ds.commit()
ds.close()
def create_user(u: User):
ds = datasource()
ds.connect()
SQL = """
INSERT INTO USERDATA (EMAIL, PASSWORD_HASH, NAME, LASTNAME, CLASS, PERMISSION_LEVEL, LAST_IP, ACTIVE, TEMPHASH)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
PARAM = (
u.EMAIL,
u.PASSWORD_HASH,
u.NAME,
u.LASTNAME,
u.CLASS,
u.PERMISSION_LEVEL,
str(u.LAST_IP),
u.ACTIVE,
u.TEMPHASH
)
ds.execute(SQL, PARAM)
ds.commit()
ds.close()
def get_user(ID: Optional[int] = None, EMAIL: Optional[str] = None, TEMPHASH: Optional[str] = None):
ds = datasource()
ds.connect()
if ID is not None:
if e.DEBUG:
print(ID)
ds.execute("SELECT * FROM USERDATA WHERE ID = %s", (ID,))
elif EMAIL is not None:
if e.DEBUG:
print(EMAIL)
ds.execute("SELECT * FROM USERDATA WHERE EMAIL = %s", (EMAIL,))
elif TEMPHASH is not None:
if e.DEBUG:
print(TEMPHASH)
ds.execute("SELECT * FROM USERDATA WHERE TEMPHASH = %s", (TEMPHASH,))
else:
raise ValueError('USER not Valid')
data = ds.fetch_row()
ds.close()
if data is not None:
return fetch_data(data)
else:
raise ValueError('No user found')
async def form_create_user(api_key: str, user: preUser, ip: str):
try:
ApiKey(APIKEY=api_key)
except ValidationError as e:
return JSONResponse(
status_code=status.HTTP_403_FORBIDDEN,
content=e.errors()
)
ds = datasource()
ds.connect()
SQL = "SELECT EMAIL FROM USERDATA WHERE EMAIL = %s"
PAR = (user.EMAIL,)
ds.execute(SQL, PAR)
data = ds.fetch_row()
ds.close()
if data is not None:
raise HTTPException(
status_code=status.HTTP_409_CONFLICT,
detail="User is already registered"
)
PERMISSION_LEVEL = 0
HASH = uuid.uuid1().hex
isTeacher = is_teacher(user.EMAIL)
# Check if User is in the Global Teacher Database
if isTeacher:
PERMISSION_LEVEL = 1
user.CLASS = "LEHRER"
elif user.CLASS == "LEHRER":
raise HTTPException(
status_code=status.HTTP_417_EXPECTATION_FAILED,
detail="User is not a teacher"
)
NAME = user.EMAIL.split(".")[0].capitalize()
LASTNAME = user.EMAIL.split(".")[1].split("@")[0].capitalize()
if LASTNAME[-2:].isdigit():
LASTNAME = LASTNAME[:-2]
try:
account = User(
EMAIL=user.EMAIL,
PASSWORD_HASH=get_password_hash(user.PASSWORD),
NAME=NAME,
LASTNAME=LASTNAME,
CLASS=user.CLASS,
PERMISSION_LEVEL=PERMISSION_LEVEL,
LAST_IP=ip,
ACTIVE=False,
TEMPHASH=HASH
)
except ValidationError as e:
raise HTTPException(
status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
detail=e.errors()
)
create_user(account)
mail = Mail(
to=user.EMAIL,
subject="Account Aktivieren",
message="https://api.toolbox.philsoft.at/account/activate/" + account.TEMPHASH,
html=False
)
if mail.send():
return JSONResponse(
status_code=status.HTTP_200_OK,
content="User successfully created"
)
else:
return HTTPException(
status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
detail="There was some error with the mail processing"
)