def UpdateUser(self, update_data):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Update() \
.Table(UsersTable.TABLE_NAME) \
.Set() \
.UpdateValues(update_data.memberKeys, update_data.memberValues)\
.Where() \
.ColumnsNoBrackets(update_data.identifierKeys) \
.Like() \
.ValuesNoBrackets(update_data.identifierValues) \
.Build()
cursor.execute(sql, QueryBuilder().CountNulls(sql).BuildNullTuple())
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def RevokeCoachAccess(self, athlete_id, coach_id):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Delete() \
.From() \
.Table(CoachAthleteTable.TABLE_NAME) \
.Where() \
.ColumnsNoBrackets([CoachAthleteTable.ATHLETE_ID]) \
.Like() \
.ValuesNoBrackets([athlete_id]) \
.And() \
.ColumnsNoBrackets([CoachAthleteTable.COACH_ID]) \
.Like() \
.ValuesNoBrackets([coach_id]) \
.Build()
cursor.execute(sql,
QueryBuilder().CountNulls(sql).BuildNullTuple())
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def GetNewInvitesCount(self, user_id):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Select() \
.AllColumns() \
.From() \
.Table(InvitesTable.TABLE_NAME) \
.Where() \
.ColumnsNoBrackets([InvitesTable.SENT_TO]) \
.Like() \
.ValuesNoBrackets([user_id]) \
.And() \
.ColumnsNoBrackets([InvitesTable.STATUS]) \
.Like() \
.ValuesNoBrackets([1]) \
.OrderBy() \
.ColumnsNoBrackets([InvitesTable.SEND_DATE]) \
.Asc() \
.Build()
cursor.execute(sql, QueryBuilder().CountNulls(sql).BuildNullTuple())
return cursor.rowcount
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 DeclineInvite(self, invite_id):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Update() \
.Table(InvitesTable.TABLE_NAME) \
.Set() \
.UpdateValues([InvitesTable.STATUS], [3]) \
.Where() \
.ColumnsNoBrackets([InvitesTable.ID]) \
.Like() \
.ValuesNoBrackets([invite_id]) \
.Build()
cursor.execute(sql)
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
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 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 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 DeleteEvent(self, event_id):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Delete() \
.From() \
.Table(EventsTable.TABLE_NAME) \
.Where() \
.ColumnsNoBrackets([EventsTable.ID]) \
.Like() \
.ValuesNoBrackets([event_id]) \
.Build()
cursor.execute(sql)
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def GetParticipatingEvents(self, participant_id):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Select() \
.ColumnsNoBrackets([EventAttendeesTable.ID]) \
.From() \
.Table(EventAttendeesTable.TABLE_NAME) \
.Where() \
.ColumnsNoBrackets([EventAttendeesTable.USER_ID]) \
.Like() \
.ValuesNoBrackets([participant_id]) \
.Build()
cursor.execute(sql)
event_ids = []
for values in cursor.fetchall():
event_ids.append(values[0])
event_dao = EventDao()
return event_dao.GetEvents(event_ids)
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 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 DeleteWorkout(self, user_id, workout_id):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Delete()\
.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)
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def CreateUser(self, user):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Insert() \
.Into() \
.Table(UsersTable.TABLE_NAME) \
.ColumnsBrackets([
UsersTable.ID,
UsersTable.PASSWORD,
UsersTable.F_NAME,
UsersTable.L_NAME,
UsersTable.E_MAIL,
UsersTable.DOB,
UsersTable.TYPE,
UsersTable.LOCATION,
UsersTable.BIO
]) \
.ValuesKeyword() \
.ValuesBrackets([
user.Id.__str__(),
user.Password.__str__(),
user.F_Name.__str__(),
user.L_Name.__str__(),
user.Email.__str__(),
user.DOB.__str__(),
user.Type.__str__(),
user.Location.__str__(),
user.Bio.__str__()
]) \
.Build()
cursor.execute(sql, QueryBuilder().CountNulls(sql).BuildNullTuple())
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def CreateAccess(self, access):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Insert() \
.Into() \
.Table(CoachAthleteTable.TABLE_NAME) \
.ColumnsBrackets([
CoachAthleteTable.ATHLETE_ID,
CoachAthleteTable.COACH_ID,
CoachAthleteTable.CAN_ACCESS_TRAINING_LOG,
CoachAthleteTable.CAN_ACCESS_TARGETS,
CoachAthleteTable.IS_ACTIVE,
CoachAthleteTable.START_DATE,
CoachAthleteTable.INVITE_ID,
]) \
.ValuesKeyword() \
.ValuesBrackets([
access.Athlete_Id.__str__(),
access.Coach_Id.__str__(),
access.Can_Access_Training_Log.__str__(),
access.Can_Access_Targets.__str__(),
access.Is_Active.__str__(),
access.Start_Date.__str__(),
access.Invite_Id.__str__()
]) \
.Build()
cursor.execute(sql,
QueryBuilder().CountNulls(sql).BuildNullTuple())
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def CreateEvent(self, event):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Insert() \
.Into() \
.Table(EventsTable.TABLE_NAME) \
.ColumnsBrackets([
EventsTable.TYPE,
EventsTable.NAME,
EventsTable.HOST_USERNAME,
EventsTable.CREATED_DATE,
EventsTable.EVENT_DATE
]) \
.ValuesKeyword() \
.ValuesBrackets([
event.Type.__str__(),
event.Name.__str__(),
event.Host_Username.__str__(),
event.Created_Date.__str__(),
event.Event_Date.__str__()
]) \
.Build()
cursor.execute(sql, QueryBuilder().CountNulls(sql).BuildNullTuple())
if cursor.rowcount != 0:
self.db.commit()
eventDao = EventDao()
return eventDao.GetEventId(event)
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def CreateSingleInvite(self, invite):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Insert() \
.Into() \
.Table(InvitesTable.TABLE_NAME) \
.ColumnsBrackets([
InvitesTable.INVITE_TYPE,
InvitesTable.SENT_BY,
InvitesTable.SENT_TO,
InvitesTable.STATUS,
InvitesTable.SEND_DATE,
InvitesTable.EVENT_ID
]) \
.ValuesKeyword() \
.ValuesBrackets([
invite.Invite_Type.__str__(),
invite.Sent_By.__str__(),
invite.Sent_To.__str__(),
invite.Status.__str__(),
invite.Send_Date.__str__(),
invite.Event_Id.__str__()
]) \
.Build()
cursor.execute(sql, QueryBuilder().CountNulls(sql).BuildNullTuple())
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def CreateTarget(self, target):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Insert() \
.Into() \
.Table(TargetsTable.TABLE_NAME) \
.ColumnsBrackets([
TargetsTable.ATHLETE_ID,
TargetsTable.CONTENT,
TargetsTable.STATUS,
TargetsTable.WEEK,
TargetsTable.YEAR
]) \
.ValuesKeyword() \
.ValuesBrackets([
target.Athlete_Id.__str__(),
target.Content.__str__(),
target.Status.__str__(),
target.Week.__str__(),
target.Year.__str__()
]) \
.Build()
cursor.execute(sql,
QueryBuilder().CountNulls(sql).BuildNullTuple())
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def UpdateCoachAccess(self, user_id, coach_id, access):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Update() \
.Table(CoachAthleteTable.TABLE_NAME) \
.Set() \
.UpdateValues([
CoachAthleteTable.CAN_ACCESS_TARGETS,
CoachAthleteTable.CAN_ACCESS_TRAINING_LOG
], [
access.Can_Access_Targets.__str__(),
access.Can_Access_Training_Log.__str__()
]) \
.Where() \
.ColumnsNoBrackets([CoachAthleteTable.ATHLETE_ID]) \
.Like() \
.ValuesNoBrackets([user_id]) \
.And() \
.ColumnsNoBrackets([CoachAthleteTable.COACH_ID]) \
.Like() \
.ValuesNoBrackets([coach_id]) \
.Build()
cursor.execute(sql,
QueryBuilder().CountNulls(sql).BuildNullTuple())
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()
def CreateMultipleInvites(self, invite, recipients):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Insert() \
.Into() \
.Table(InvitesTable.TABLE_NAME) \
.ColumnsBrackets([
InvitesTable.INVITE_TYPE,
InvitesTable.SENT_BY,
InvitesTable.SENT_TO,
InvitesTable.STATUS,
InvitesTable.SEND_DATE,
InvitesTable.EVENT_ID
]) \
.ValuesKeyword() \
.ValuesBrackets([
"%s", "%s", "%s", "%s", "%s", "%s"
]) \
.Build()
invites = []
for recipient in recipients:
invite_tuple = (
invite.Invite_Type,
invite.Sent_By.__str__(),
recipient,
invite.Status.__str__(),
invite.Send_Date.__str__(),
invite.Event_Id.__str__() if invite.Event_Id is not None else None
)
invites.append(invite_tuple)
cursor.executemany(sql, invites)
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
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 UpdateMultipleInvites(self, update_data):
try:
with self.db.cursor() as cursor:
values = []
for item in update_data.memberValues:
values.append("%s")
sql = QueryBuilder() \
.Update() \
.Table(InvitesTable.TABLE_NAME) \
.Set() \
.UpdateValues(update_data.memberKeys, values) \
.Where() \
.ColumnsNoBrackets([InvitesTable.ID]) \
.Like() \
.ValuesNoBrackets(["%s"]) \
.Build()
invites = []
for id in update_data.inviteIds:
base_tuple = (update_data.memberValues[0],)
for i in range(len(update_data.memberValues)):
if i != 0:
base_tuple += (update_data.memberValues[i],)
base_tuple += (id,)
invites.append(base_tuple)
cursor.executemany(sql, invites)
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
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 CanAccess(self, event_id, user_id):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Select() \
.TableColumnsNoBrackets(EventsTable.TABLE_NAME, [EventsTable.ID, EventsTable.HOST_USERNAME]) \
.Comma() \
.TableColumnsNoBrackets(EventAttendeesTable.TABLE_NAME, [EventAttendeesTable.USER_ID]) \
.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]) \
.Like() \
.ValuesNoBrackets([event_id]) \
.And() \
.LeftRoundedBracket() \
.TableColumnsNoBrackets(EventsTable.TABLE_NAME, [EventsTable.HOST_USERNAME]) \
.Like() \
.ValuesNoBrackets([user_id]) \
.Or() \
.TableColumnsNoBrackets(EventAttendeesTable.TABLE_NAME, [EventAttendeesTable.USER_ID]) \
.Like() \
.ValuesNoBrackets([user_id]) \
.RightRoundedBracket() \
.Build()
cursor.execute(sql)
if cursor.rowcount != 0:
return True
else:
return None
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 CreateParticipants(self, event_id, user_ids):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Insert() \
.Into() \
.Table(EventAttendeesTable.TABLE_NAME) \
.ColumnsBrackets([
EventAttendeesTable.ID,
EventAttendeesTable.USER_ID,
EventAttendeesTable.ACCEPTED
]) \
.ValuesKeyword() \
.ValuesBrackets([
"%s", "%s", "%s"
]) \
.Build()
users = []
for user in user_ids:
user_tuple = (
event_id,
user,
0
)
users.append(user_tuple)
cursor.executemany(sql, users)
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
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 RemoveParticipants(self, event_id, user_ids):
try:
with self.db.cursor() as cursor:
sql = QueryBuilder() \
.Delete() \
.From() \
.Table(EventAttendeesTable.TABLE_NAME) \
.Where() \
.ColumnsNoBrackets([EventAttendeesTable.ID]) \
.Like() \
.ValuesNoBrackets(["%s"]) \
.And() \
.ColumnsNoBrackets([EventAttendeesTable.USER_ID]) \
.Like() \
.ValuesNoBrackets(["%s"]) \
.Build()
users = []
for user in user_ids:
user_tuple = (
event_id,
user
)
users.append(user_tuple)
cursor.executemany(sql, users)
if cursor.rowcount != 0:
self.db.commit()
return True
else:
return None
except:
return None
finally:
cursor.close()
self.db.close()