def GetUser(current_user, user_type, username): if not username: return ResponseHelper.ReturnBadRequestResponse() user_dao = UserDao() if username == current_user: user = user_dao.GetUserById(current_user) if user: return ResponseHelper.ReturnOkDataResponse("User Found", { "User": DAOHelper.RemoveValueFromObject(UsersTable.PASSWORD, user) }) else: return ResponseHelper.ReturnNotFoundResponse("User Not Found!") else: user = user_dao.GetUserById(username) if user: return ResponseHelper.ReturnOkDataResponse("Request OK", { "User": DAOHelper.RemoveValueFromObject(UsersTable.PASSWORD, user) }) else: return ResponseHelper.ReturnNotFoundResponse("User Not Found!")
def GetParticipants(self, event_id): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .ColumnsNoBrackets([EventAttendeesTable.USER_ID, EventAttendeesTable.ACCEPTED]) \ .From() \ .Table(EventAttendeesTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([EventAttendeesTable.ID]) \ .Like() \ .ValuesNoBrackets([event_id]) \ .Build() cursor.execute(sql) columns = cursor.description users = [] for values in cursor.fetchall(): users.append(DAOHelper.ConvertResultsToObject(columns, values)[0]) return users except: return None finally: cursor.close() self.db.close()
def CheckHostAccess(self, host_id, event_id): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(EventsTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([EventsTable.HOST_USERNAME]) \ .Like() \ .ValuesNoBrackets([host_id]) \ .And() \ .ColumnsNoBrackets([EventsTable.ID]) \ .Like() \ .ValuesNoBrackets([event_id]) \ .Build() cursor.execute(sql) columns = cursor.description events = [] for values in cursor.fetchall(): events.append(DAOHelper.ConvertResultsToObject(columns, values)[0]) return events except: return None finally: cursor.close() self.db.close()
def GetUserById(self, user_id): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(UsersTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([UsersTable.ID]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .Build() cursor.execute(sql) columns = cursor.description for values in cursor.fetchall(): users = DAOHelper.ConvertResultsToObject(columns, values) return users[0] except: return None finally: cursor.close() self.db.close()
def GetWorkoutById(self, workout_id, user_id): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(TrainingLogTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([TrainingLogTable.ID]) \ .Like() \ .ValuesNoBrackets([workout_id]) \ .And() \ .ColumnsNoBrackets([TrainingLogTable.ATHLETE_ID]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .Build() cursor.execute(sql) columns = cursor.description for values in cursor.fetchall(): workouts = DAOHelper.ConvertResultsToObject( columns, values) return workouts[0] except: return None finally: cursor.close() self.db.close()
def GetEvent(self, event_id): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(EventsTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([EventsTable.ID]) \ .Like() \ .ValuesNoBrackets([event_id]) \ .Build() cursor.execute(sql) columns = cursor.description for values in cursor.fetchall(): events = DAOHelper.ConvertResultsToObject(columns, values) event_dao = EventDao() events[0]["Attendees"] = event_dao.GetParticipants(event_id) return events[0] except: return None finally: cursor.close() self.db.close()
def CheckAccess(self, coach_id, athlete_id): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(CoachAthleteTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([CoachAthleteTable.COACH_ID]) \ .Like() \ .ValuesNoBrackets([coach_id]) \ .And() \ .ColumnsNoBrackets([CoachAthleteTable.ATHLETE_ID]) \ .Like() \ .ValuesNoBrackets([athlete_id]) \ .Build() cursor.execute(sql) columns = cursor.description for values in cursor.fetchall(): users = DAOHelper.ConvertResultsToObject(columns, values) return users[0] except: return None finally: cursor.close() self.db.close()
def GetWorkoutsByWeek(self, week, year, user_id, log_type): date_condition = "" + year + "" + week + "" try: with self.db.cursor() as cursor: base_sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(TrainingLogTable.TABLE_NAME) \ .Where() \ .FunctionColumns("YEARWEEK", [TrainingLogTable.LOG_DATE]) \ .Like() \ .ValuesNoBrackets([date_condition]) \ .And() \ .ColumnsNoBrackets([TrainingLogTable.ATHLETE_ID]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .Build() if log_type: new_sql = QueryBuilder() \ .ExtendQuery(base_sql) \ .And() \ .ColumnsNoBrackets([TrainingLogTable.TYPE_ID]) \ .Equals() \ .ValuesNoBrackets(log_type) \ .Build() sql = new_sql else: sql = base_sql final_sql = QueryBuilder()\ .ExtendQuery(sql)\ .OrderBy()\ .ColumnsNoBrackets([TrainingLogTable.LOG_DATE])\ .Asc()\ .Build() cursor.execute(final_sql) columns = cursor.description workouts = [] for values in cursor.fetchall(): workouts.append( DAOHelper.ConvertResultsToObject(columns, values)[0]) return workouts except: return None finally: cursor.close() self.db.close()
def GetAthleteCoaches(self, athlete_id): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .TableColumnsNoBrackets(UsersTable.TABLE_NAME, [ UsersTable.ID, UsersTable.F_NAME, UsersTable.L_NAME, UsersTable.LOCATION ]) \ .Comma() \ .TableColumnsNoBrackets(CoachAthleteTable.TABLE_NAME, [ CoachAthleteTable.CAN_ACCESS_TARGETS, CoachAthleteTable.CAN_ACCESS_TRAINING_LOG ]) \ .From() \ .Table(UsersTable.TABLE_NAME) \ .Join() \ .Table(CoachAthleteTable.TABLE_NAME) \ .On() \ .TableColumnsNoBrackets(CoachAthleteTable.TABLE_NAME, [CoachAthleteTable.COACH_ID]) \ .Equals() \ .TableColumnsNoBrackets(UsersTable.TABLE_NAME, [UsersTable.ID]) \ .Where() \ .TableColumnsNoBrackets(CoachAthleteTable.TABLE_NAME, [CoachAthleteTable.ATHLETE_ID]) \ .Like() \ .ValuesNoBrackets([athlete_id]) \ .And() \ .TableColumnsNoBrackets(CoachAthleteTable.TABLE_NAME, [CoachAthleteTable.IS_ACTIVE]) \ .Equals() \ .ValuesNoBrackets(["1"]) \ .Build() cursor.execute(sql) columns = cursor.description users = [] for values in cursor.fetchall(): users.append(DAOHelper.ConvertResultsToObject(columns, values)[0]) return users except: return None finally: cursor.close() self.db.close()
def GetEvents(self, event_ids): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .TableAllColumns(EventsTable.TABLE_NAME) \ .From() \ .Table(EventsTable.TABLE_NAME) \ .Join() \ .Table(EventAttendeesTable.TABLE_NAME) \ .On() \ .TableColumnsNoBrackets(EventsTable.TABLE_NAME, [EventsTable.ID]) \ .Equals() \ .TableColumnsNoBrackets(EventAttendeesTable.TABLE_NAME, [EventAttendeesTable.ID]) \ .Where() \ .TableColumnsNoBrackets(EventsTable.TABLE_NAME, [EventsTable.ID]) \ .In() \ .ValuesBrackets(event_ids) \ .And() \ .TableColumnsNoBrackets(EventAttendeesTable.TABLE_NAME, [EventAttendeesTable.ACCEPTED]) \ .Equals() \ .ValuesNoBrackets([1]) \ .OrderBy() \ .ColumnsNoBrackets([EventsTable.EVENT_DATE]) \ .Build() cursor.execute(sql) columns = cursor.description events = [] for values in cursor.fetchall(): events.append(DAOHelper.ConvertResultsToObject(columns, values)[0]) for event in events: event_dao = EventDao() event["Attendees"] = event_dao.GetParticipants(event[EventsTable.ID]) return events except: return None finally: cursor.close() self.db.close()
def GetInvite(self, sent_by, sent_to, status, invite_type, send_date): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(InvitesTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([InvitesTable.SENT_BY]) \ .Like() \ .ValuesNoBrackets([sent_by]) \ .And() \ .ColumnsNoBrackets([InvitesTable.SENT_TO]) \ .Like() \ .ValuesNoBrackets([sent_to]) \ .And() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .Like() \ .ValuesNoBrackets([status]) \ .And() \ .ColumnsNoBrackets([InvitesTable.INVITE_TYPE]) \ .Like() \ .ValuesNoBrackets([invite_type]) \ .And() \ .ColumnsNoBrackets([InvitesTable.SEND_DATE]) \ .Like() \ .ValuesNoBrackets([send_date]) \ .Build() cursor.execute(sql) columns = cursor.description for values in cursor.fetchall(): invites = DAOHelper.ConvertResultsToObject(columns, values) return invites[0] except: return None finally: cursor.close() self.db.close()
def GetEventId(self, event): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .ColumnsNoBrackets([EventsTable.ID]) \ .From() \ .Table(EventsTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([EventsTable.TYPE]) \ .Like() \ .ValuesNoBrackets([event.Type.__str__()]) \ .And() \ .ColumnsNoBrackets([EventsTable.NAME]) \ .Like() \ .ValuesNoBrackets([event.Name.__str__()]) \ .And() \ .ColumnsNoBrackets([EventsTable.HOST_USERNAME]) \ .Like() \ .ValuesNoBrackets([event.Host_Username.__str__()]) \ .And() \ .ColumnsNoBrackets([EventsTable.CREATED_DATE]) \ .Like() \ .ValuesNoBrackets([event.Created_Date.__str__()]) \ .And() \ .ColumnsNoBrackets([EventsTable.EVENT_DATE]) \ .Like() \ .ValuesNoBrackets([event.Event_Date.__str__()]) \ .Build() cursor.execute(sql) columns = cursor.description for values in cursor.fetchall(): events = DAOHelper.ConvertResultsToObject(columns, values) return events[0][str(EventsTable.ID)] except: return None finally: cursor.close() self.db.close()
def GetWorkoutsByMonth(self, month, month_end, year, user_id): date_start_condition = "" + year + "-" + month + "-01" date_end_condition = "" + year + "-" + month + "-" + str( month_end) + "" try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(TrainingLogTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([TrainingLogTable.LOG_DATE]) \ .Between() \ .ValuesNoBrackets([date_start_condition]) \ .And() \ .ValuesNoBrackets([date_end_condition]) \ .And() \ .ColumnsNoBrackets([TrainingLogTable.ATHLETE_ID]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .OrderBy() \ .ColumnsNoBrackets([TrainingLogTable.LOG_DATE]) \ .Asc() \ .Build() cursor.execute(sql) columns = cursor.description workouts = [] for values in cursor.fetchall(): workouts.append( DAOHelper.ConvertResultsToObject(columns, values)[0]) return workouts except: return None finally: cursor.close() self.db.close()
def GetTargetsByWeek(self, user_id, week_number, year_number): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(TargetsTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([TargetsTable.ATHLETE_ID]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .And() \ .ColumnsNoBrackets([TargetsTable.WEEK]) \ .Equals() \ .ValuesNoBrackets([week_number]) \ .And() \ .ColumnsNoBrackets([TargetsTable.YEAR]) \ .Equals() \ .ValuesNoBrackets([year_number]) \ .OrderBy() \ .ColumnsNoBrackets([TargetsTable.ID]) \ .Asc() \ .Build() cursor.execute(sql) columns = cursor.description targets = [] for values in cursor.fetchall(): targets.append( DAOHelper.ConvertResultsToObject(columns, values)[0]) return targets except: return None finally: cursor.close() self.db.close()
def GetTemplates(self, user_id): try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .ColumnsNoBrackets([ TrainingLogTemplateTable.COACH_ID, TrainingLogTemplateTable.ID, TrainingLogTemplateTable.NAME ]) \ .From() \ .Table(TrainingLogTemplateTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([TrainingLogTemplateTable.COACH_ID]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .OrderBy() \ .ColumnsNoBrackets([TrainingLogTemplateTable.NAME]) \ .Asc() \ .Build() cursor.execute(sql) columns = cursor.description templates = [] for values in cursor.fetchall(): templates.append( DAOHelper.ConvertResultsToObject(columns, values)[0]) return templates except: return None finally: cursor.close() self.db.close()
def GetInvites(self, user_id, invite_status_type, invite_type=None): try: with self.db.cursor() as cursor: if invite_type is not None: if invite_status_type == "sent": sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(InvitesTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([InvitesTable.SENT_BY]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .And() \ .ColumnsNoBrackets([InvitesTable.INVITE_TYPE]) \ .Like() \ .ValuesNoBrackets([invite_type]) \ .And() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .In() \ .ValuesBrackets([1, 2]) \ .OrderBy() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .Asc() \ .Comma() \ .ColumnsNoBrackets([InvitesTable.SEND_DATE]) \ .Desc() \ .Build() else: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(InvitesTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([InvitesTable.SENT_TO]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .And() \ .ColumnsNoBrackets([InvitesTable.INVITE_TYPE]) \ .Like() \ .ValuesNoBrackets([invite_type]) \ .And() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .In() \ .ValuesBrackets([1, 2]) \ .OrderBy() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .Asc() \ .Comma() \ .ColumnsNoBrackets([InvitesTable.SEND_DATE]) \ .Desc() \ .Build() else: if invite_status_type == "sent": sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(InvitesTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([InvitesTable.SENT_BY]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .And() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .In() \ .ValuesBrackets([1, 2]) \ .OrderBy() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .Asc() \ .Comma() \ .ColumnsNoBrackets([InvitesTable.SEND_DATE]) \ .Desc() \ .Build() else: sql = QueryBuilder() \ .Select() \ .AllColumns() \ .From() \ .Table(InvitesTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([InvitesTable.SENT_TO]) \ .Like() \ .ValuesNoBrackets([user_id]) \ .And() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .In() \ .ValuesBrackets([1, 2]) \ .OrderBy() \ .ColumnsNoBrackets([InvitesTable.STATUS]) \ .Asc() \ .Comma() \ .ColumnsNoBrackets([InvitesTable.SEND_DATE]) \ .Desc() \ .Build() cursor.execute(sql) columns = cursor.description invites = [] for values in cursor.fetchall(): invites.append(DAOHelper.ConvertResultsToObject(columns, values)[0]) return invites except: return None finally: cursor.close() self.db.close()
def GetUsers(self, search_type, search_query): profile_type = "" if search_type == "athlete": profile_type = "ATH" if search_type == "coach": profile_type = "COA" try: with self.db.cursor() as cursor: sql = QueryBuilder() \ .Select() \ .ColumnsNoBrackets([ UsersTable.ID, UsersTable.F_NAME, UsersTable.L_NAME, UsersTable.LOCATION, ]) \ .From() \ .Table(UsersTable.TABLE_NAME) \ .Where() \ .ColumnsNoBrackets([UsersTable.TYPE]) \ .Like() \ .ValuesNoBrackets([profile_type]) \ .And() \ .LeftRoundedBracket() \ .ColumnsNoBrackets([UsersTable.ID]) \ .Like() \ .QueryValue(search_query) \ .Or() \ .ColumnsNoBrackets([UsersTable.F_NAME]) \ .Like() \ .QueryValue(search_query) \ .Or() \ .ColumnsNoBrackets([UsersTable.L_NAME]) \ .Like() \ .QueryValue(search_query) \ .Or() \ .ColumnsNoBrackets([UsersTable.LOCATION]) \ .Like() \ .QueryValue(search_query) \ .RightRoundedBracket() \ .Build() cursor.execute(sql) columns = cursor.description users = [] for values in cursor.fetchall(): users.append(DAOHelper.ConvertResultsToObject(columns, values)[0]) return users except: return None finally: cursor.close() self.db.close()