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