def filter(cls, **kwargs):
params = ['TRUE']
values = []
for key, value in kwargs.items():
params.append("{}.{}=%s".format(cls.__name__.lower(), key))
values.append(value)
exp = '''SELECT * FROM {table_name} FULL JOIN users ON feedbacks.reviewer = users.id
JOIN questions ON feedbacks.question=questions.id
WHERE {filter} ORDER BY feedbacks.id DESC'''.format(
table_name=cls.__name__.lower(),
filter=' and '.join(params),
)
rows = db_client.fetch(exp, values)
objects = []
for row in rows:
t = Users(*row[cls.sql_field_number:cls.sql_field_number +
Users.sql_field_number])
q = Questions(*row[cls.sql_field_number + Users.sql_field_number:])
q.teacher = Users.get(id=q.teacher.id)
fb = FeedBacks(*row[:cls.sql_field_number])
fb.quality_rate = int(fb.quality_rate)
fb.difficulty_rate = int(fb.quality_rate)
fb.reviewer = t
fb.question = q
objects.append(fb)
return QueryList(objects)
def save(self):
if self.id:
update_set = ','.join([
"{key}=%s".format(key='name'),
"{key}=%s".format(key='surname'),
"{key}=%s".format(key='email'),
"{key}=%s".format(key='phone_number'),
"{key}=%s".format(key='password'),
"{key}=%s".format(key='role'),
"{key}=%s".format(key='iban'),
])
exp = '''UPDATE {table_name} SET {values} WHERE id=%s RETURNING id'''.format(
table_name=self.__class__.__name__.lower(),
values=update_set,
)
self.id = db_client.fetch(exp, (self.name,
self.surname,
self.email,
self.phone_number,
self.password,
self.role,
self.iban,
self.id))[0][0]
else:
self.password = generate_password_hash(self.password)
exp = '''INSERT INTO {table_name} ({table_fields}) VALUES ({values}) RETURNING id'''.format(
table_name=self.__class__.__name__.lower(),
table_fields=','.join([
'{}'.format('name'),
'{}'.format('surname'),
'{}'.format('email'),
'{}'.format('phone_number'),
'{}'.format('password'),
'{}'.format('role'),
'{}'.format('iban'),
]),
values=','.join(['%s', '%s', '%s', '%s', '%s', '%s', '%s'])
)
self.id = db_client.fetch(exp, (self.name,
self.surname,
self.email,
self.phone_number,
self.password,
self.role,
self.iban))[0][0]
return self
def delete(self):
if not self.id:
return None
exp = '''DELETE FROM {table_name} WHERE id=%s RETURNING id'''.format(
table_name=self.__class__.__name__.lower(), )
id = self.id
self.id = None
return db_client.fetch(exp, [id])[0][0]
def save(self):
if self.id:
update_set = ','.join([
"{key}=%s".format(key='question_image',
value=self.question_image),
"{key}=%s".format(key='answer_image', value=self.answer_image),
"{key}=%s".format(key='choice', value=self.choice),
"{key}=%s".format(key='course', value=self.course),
"{key}=%s".format(key='subject', value=self.subject),
"{key}=%s".format(key='comment', value=self.comment),
"{key}=%s".format(key='teacher', value=self.teacher.id),
])
exp = '''UPDATE {table_name} SET {values} WHERE id=%s RETURNING id'''.format(
table_name=self.__class__.__name__.lower(),
values=update_set,
)
self.id = db_client.fetch(
exp, (self.question_image, self.answer_image, self.choice,
self.course, self.subject, self.comment, self.teacher.id,
self.id))[0][0]
else:
exp = '''INSERT INTO {table_name} ({table_fields}) VALUES ({values}) RETURNING id'''.format(
table_name=self.__class__.__name__.lower(),
table_fields=','.join([
'{}'.format('question_image'),
'{}'.format('answer_image'),
'{}'.format('choice'),
'{}'.format('course'),
'{}'.format('subject'),
'{}'.format('comment'),
'{}'.format('teacher'),
]),
values=','.join(['%s', '%s', '%s', '%s', '%s', '%s', '%s']))
self.id = db_client.fetch(exp,
(self.question_image, self.answer_image,
self.choice, self.course, self.subject,
self.comment, self.teacher.id))[0][0]
return self
def save(self):
if self.id:
update_set = ','.join([
"{key}=%s".format(key='comment'),
"{key}=%s".format(key='quality_rate'),
"{key}=%s".format(key='difficulty_rate'),
"{key}=%s".format(key='is_proper'),
"{key}=%s".format(key='reviewer'),
])
exp = '''UPDATE {table_name} SET {values} WHERE id=%s RETURNING id, created_at'''.format(
table_name=self.__class__.__name__.lower(),
values=update_set,
)
query = db_client.fetch(
exp, (self.comment, self.quality_rate, self.difficulty_rate,
self.is_proper, self.reviewer.id, self.id))
self.id = query[0][0]
self.created_at = query[0][1]
else:
exp = '''INSERT INTO {table_name} ({table_fields}) VALUES ({values}) RETURNING id, created_at'''.format(
table_name=self.__class__.__name__.lower(),
table_fields=','.join([
'{}'.format('comment'),
'{}'.format('question'),
'{}'.format('quality_rate'),
'{}'.format('difficulty_rate'),
'{}'.format('is_proper'),
'{}'.format('reviewer'),
]),
values=','.join(['%s', '%s', '%s', '%s', '%s', '%s']))
self.id, self.created_at = db_client.fetch(
exp,
(self.comment, self.question.id, self.quality_rate,
self.difficulty_rate, self.is_proper, self.reviewer.id))[0]
return self
def get_feedbacks(self):
exp = '''SELECT comment, quality_rate, difficulty_rate, is_proper, created_at, users.name, users.surname
FROM feedbacks JOIN users ON feedbacks.reviewer = users.id WHERE question=%s'''
rows = db_client.fetch(exp, (self.id, ))
r = []
for row in rows:
d = {}
d['comment'] = row[0]
d['quality_rate'] = int(row[1])
d['difficulty_rate'] = int(row[2])
d['is_proper'] = row[3]
d['created_at'] = row[4].isoformat()
d['reviewer'] = '{} {}'.format(row[5], row[6])
r.append(d)
return r
def filter(cls, **kwargs):
params = ['TRUE']
values = []
for key, value in kwargs.items():
params.append("{}=%s".format(key))
values.append(value)
exp = '''SELECT * FROM {table_name} WHERE {filter} ORDER BY id ASC'''.format(
table_name=cls.__name__.lower(),
filter=' and '.join(params),
)
rows = db_client.fetch(exp, values)
objects = [cls(*row) for row in rows]
return QueryList(objects)
def filter(cls, **kwargs):
params = ['TRUE']
values = []
for key, value in kwargs.items():
params.append("{}.{}=%s".format(cls.__name__.lower(), key))
values.append(value)
exp = '''SELECT * FROM {table_name} JOIN users ON questions.teacher = users.id
WHERE {filter} ORDER BY questions.id DESC '''.format(
table_name=cls.__name__.lower(),
filter=' and '.join(params),
)
rows = db_client.fetch(exp, values)
objects = []
for row in rows:
t = Users(*row[cls.sql_field_number:])
q = Questions(*row[:cls.sql_field_number])
q.teacher = t
objects.append(q)
return QueryList(objects)