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()