def associate_child_with_caregiver(caregiver_id, child_id):
query = "INSERT INTO 'caregiver_to_child' " \
"(caregiver_id, child_id)" \
"VALUES (?, ?)"
get_db().execute(query, (caregiver_id, child_id))
get_db().commit()
def create_task(user_id, name, points, max_day, max_week):
query = "INSERT INTO home_points (user_id, name, points, max_day, max_week) VALUES (?, ?, ?, ?, ?)"
cur = get_db().cursor()
params = (user_id, name, points, max_day, max_week)
cur.execute(query, params)
get_db().commit()
def give_ban(child_id, duration_minutes):
bans_status = check_bans_status(child_id)
start = datetime.now()
start_timestamp = start.isoformat()
try:
if bans_status[6]['start']:
# jeśli jest wpis bazie to robimy update
# sprawdzamy czy ban jest aktualnie aktywny
if bans_status[6]['active']:
# jeśli jest aktywny to aktualizujemy czas końca bana
end = bans_status[6]['stop'] + timedelta(minutes=duration_minutes)
end_timestamp = end.isoformat()
query = 'UPDATE bans SET end_timestamp = ? WHERE child_id = ? AND ban_id = ?'
params = (end_timestamp, child_id, 6)
else:
# jeśli jest ban ale nieaktywny to ustawimy czasy od początku,
end = start + timedelta(minutes=duration_minutes)
end_timestamp = end.isoformat()
query = 'UPDATE bans SET start_timestamp = ?, end_timestamp = ? WHERE child_id = ? AND ban_id = ?'
params = (start_timestamp, end_timestamp, child_id, 6)
else:
# jeśli nie ma wpisu w bazie to robimy nowy wpis
end = start + timedelta(minutes=duration_minutes)
end_timestamp = end.isoformat()
query = 'INSERT INTO bans VALUES (NULL, ?, ?, ?, ?)'
params = (child_id, 6, start_timestamp, end_timestamp)
get_db().execute(query, params)
get_db().commit()
except IndexError as e:
print(e)
def update_task(user_id, name, points, max_day, max_week, is_active, task_id):
query = "UPDATE 'home_points' " \
"SET user_id = ?, name = ?, points = ?, max_day = ?, max_week = ?, is_active = ?" \
"WHERE id = ?"
params = (user_id, name, points, max_day, max_week, is_active, task_id)
get_db().cursor().execute(query, params)
get_db().commit()
def add_new_task(user_id, name, points, max_day, max_week, is_active):
query = "INSERT INTO 'home_points' " \
"(user_id, name, points, max_day, max_week, is_active) " \
"VALUES (?, ?, ?, ?, ?, ?) "
params = (user_id, name, points, max_day, max_week, is_active)
get_db().execute(query, params)
get_db().commit()
def add_new_user(user_data):
query = "INSERT INTO 'users' " \
"(username, password, role, firstname) " \
"VALUES (?, ?, ?, ?) "
get_db().execute(query, user_data)
get_db().commit()
def create_prize(user_id, name, points, max_day, max_week, max_month):
query = "INSERT INTO prizes (user_id, name, points, max_day, max_week, max_month) VALUES (?, ?, ?, ?, ?, ?)"
cur = get_db().cursor()
params = (user_id, name, points, max_day, max_week, max_month)
cur.execute(query, params)
get_db().commit()
def deactivate_task(child_id, task_id):
query = "UPDATE 'home_points' " \
"SET is_active = 0 " \
"WHERE user_id = ? AND id = ? "
cur = get_db().cursor()
params = (child_id, task_id)
cur.execute(query, params)
get_db().commit()
def deactivate_prize(child_id, prizes_id):
query = "UPDATE 'prizes' " \
"SET is_active = 0 " \
"WHERE user_id = ? AND id = ? "
cur = get_db().cursor()
params = (child_id, prizes_id)
cur.execute(query, params)
get_db().commit()
def add_warn_per_ban_id(child_id, ban_id):
start = datetime.now()
start_timestamp = start.isoformat()
end_timestamp = calculate_end_time_warn(start, ban_id)
query = 'INSERT INTO bans VALUES (NULL, ?, ?, ?, ?)'
params = (child_id, ban_id, start_timestamp, end_timestamp)
get_db().execute(query, params)
get_db().commit()
def update_prize(user_id, name, points, max_day, max_week, max_month,
prize_id):
query = "UPDATE 'prizes' " \
"SET user_id = ?, name = ?, points = ?, max_day = ?, max_week = ?, max_month = ?" \
"WHERE id = ?"
params = (user_id, name, points, max_day, max_week, max_month, prize_id)
get_db().execute(query, params)
get_db().commit()
def insert_default_ban(child_id, ban_id, ban_name):
query = """
INSERT INTO 'bans_name'
VALUES (NULL, ?, ?, ?)
"""
params = (child_id, ban_id, ban_name)
get_db().execute(query, params)
get_db().commit()
def update_warn_per_ban_id(child_id, ban_id):
start = datetime.now()
start_timestamp = start.isoformat()
end_timestamp = calculate_end_time_warn(start, ban_id)
query = 'UPDATE bans SET start_timestamp = ?, end_timestamp = ? WHERE child_id = ? AND ban_id = ?'
params = (start_timestamp, end_timestamp, child_id, ban_id)
get_db().execute(query, params)
get_db().commit()
def get_weekly_highscore(user_id):
query = 'select school_weekly_highscore from users where id = ?'
result = get_db().execute(query, [user_id])
row = result.fetchone()
if row:
return row['school_weekly_highscore']
return None
def get_bans_name(child_id):
query = 'select ban_id, ban_name from bans_name where child_id = ? ORDER BY ban_id'
result = get_db().execute(query, [
child_id,
])
result = dict(result.fetchall())
return result
def get_only_points(child_id):
query = 'select points from main_points where child_id = ?'
result = get_db().execute(query, [child_id])
row = result.fetchone()
if row:
return row['points']
return None
def get_username_id_and_role_by_username(username):
query = 'select id, role from users where username = ?'
result = get_db().execute(query, (username,))
row = result.fetchone()
if row:
return dict(row)
return None
def get_child_points(child_id):
query = """
SELECT * FROM main_points
WHERE main_points.child_id=?
"""
result = get_db().execute(query, (child_id, ))
child_points = dict(result.fetchone())
return child_points
def app():
"""Create and configure a new app instance for each test."""
# create a temporary file to isolate the database for each test
db_fd, db_path = tempfile.mkstemp()
# create the app with common test config
app = create_app({"TESTING": True, "DATABASE": db_path})
# create the database and load test data
with app.app_context():
get_db().executescript(_init_data_sql)
get_db().executescript(_data_sql)
yield app
# close and remove the temporary database
os.close(db_fd)
os.unlink(db_path)
def get_user_data(user_id):
query = 'select * from users where id = ?'
result = get_db().execute(query, (user_id, ))
row = result.fetchone()
if row:
# TODO: load only necessary data to g, not the whole dict
return dict(row)
return None
def get_all_bans(child_id):
query = 'select * from bans where child_id = ? ORDER BY ban_id'
bans = get_db().execute(query, [child_id])
result = {}
for ban in bans.fetchall():
result[ban[2]] = {'start': ban[3], 'stop': ban[4]}
return result
def get_user_id(username):
query = """
SELECT id FROM users
WHERE username = ?
"""
result = get_db().execute(query, (username, ))
row = result.fetchone()
if row:
return row['id']
return False
def get_points_history_limits(child_id, dt_string):
query = 'SELECT p.points_change, p.change_timestamp, u.firstname ' \
'FROM points_history p ' \
'INNER JOIN users u ' \
'ON (p.id_changing_user=u.id) ' \
'WHERE p.child_id = ? ' \
'ORDER BY p.id DESC LIMIT 10'
result = get_db().execute(query, [
child_id,
])
return result.fetchall()
def get_child_data(child_id):
query = """
SELECT u.*
FROM caregiver_to_child AS ctc
JOIN users AS u on ctc.child_id = u.id
WHERE ctc.child_id = ?
AND u.role = 'child'
"""
result = get_db().execute(query, (child_id, ))
child = dict(result.fetchone())
child['bans'] = check_bans_status(child_id)
return child
def get_most_important_warn_ban(child_id):
now = datetime.now().strftime("%Y-%m-%dT%H:%M:%S")
query = 'select bans_name.ban_id, bans_name.ban_name, bans.start_timestamp, bans.end_timestamp from bans ' \
f'inner JOIN bans_name ON bans.id=bans_name.ban_id where bans_name.child_id={child_id} ' \
f'and bans.child_id={child_id} and bans.end_timestamp>\'{now}\' order by bans_name.ban_id desc'
bans = get_db().execute(query)
try:
result = bans.fetchone()
raw_start = result["start_timestamp"].replace('T', ' ').split(':')
raw_end = result["end_timestamp"].replace('T', ' ').split(':')
start = f'{raw_start[0]}:{raw_start[1]}'
end = f'{raw_end[0]}:{raw_end[1]}'
info_str = f'{result["ban_name"]} Od: {start} Do: {end};red'
except:
info_str = 'brak aktywnych warnów/banów;green'
return info_str
def get_caregivers_children(user_id):
# TODO remove _update_with_bans_and_points_data to data_access/bans.py
def _update_with_bans_and_points_data(children):
new_children = []
for child in children:
child = dict(child)
child['bans'] = check_bans_status(child['id'])
child['child_points'] = get_child_points(child['id'])
new_children.append(child)
return new_children
query = """
SELECT u.*
FROM caregiver_to_child AS ctc
JOIN users AS u on ctc.child_id = u.id
WHERE ctc.caregiver_id = ?
AND u.role = 'child'
"""
result = get_db().execute(query, (user_id, ))
children = result.fetchall()
children = _update_with_bans_and_points_data(children)
return children
def change_points_by(target_id, points, user_id):
""" used both to add and subtract points from the current amount """
query = 'UPDATE main_points SET points = points + ?, last_insert_id = ? WHERE child_id = ?;'
get_db().execute(query, [points, user_id, target_id])
get_db().commit()
def get_prizes(child_id):
query = "SELECT * FROM prizes WHERE user_id = ? ORDER BY points"
result = get_db().execute(query, (child_id,))
return result.fetchall()
def get_tasks(child_id):
query = "SELECT * FROM home_points WHERE user_id = ? AND is_active=1 ORDER BY points LIMIT 10"
result = get_db().execute(query, (child_id, ))
return result.fetchall()
def add_exp(exp, child_id):
query = 'UPDATE main_points SET exp = exp + ? WHERE child_id = ?;'
get_db().execute(query, [exp, child_id])
get_db().commit()
