def get_by_owner(owner_id, survey_id=None, status=None):
instances = Model.repository.instances
surveys = Model.repository.surveys
if survey_id is None:
instance_list = instances.select(InnerJoin(instances, surveys,
instances.survey_id,
InnerJoin.EQUAL,
surveys.id),
Where(surveys.owner_id,
Where.EQUAL, owner_id),
force_list=True)
else:
instance_list = instances.select(
InnerJoin(instances, surveys, instances.survey_id,
InnerJoin.EQUAL, surveys.id),
Where(surveys.owner_id, Where.EQUAL,
owner_id).AND(surveys.id, Where.EQUAL, survey_id),
force_list=True)
if status is None:
return instance_list
else:
final_list = []
for instance in instance_list:
state = StateService.get_next_state_in_instance(
instance, status)
if state is not None and status.value == state.status:
final_list.append(instance)
return final_list
def session_valid(owner_id, session_id):
sessions = Model.repository.sessions
session = sessions.select(Where(sessions.id, Where.E, session_id))
if session is None:
return False
if datetime.now() > session.expires:
sessions.delete(Where(sessions.id, Where.E, session_id))
return False
return session.owner_id == owner_id
def save_update(self):
instr = ''
for key in self.model.columns:
instr += key + " = " + self.parse_value(
self.__dict__[key]) + ', '
instr = instr[:-2]
sql = "UPDATE " + self.model.table_name + " SET " + instr + " WHERE id = %s"
logger.debug(sql)
connection = Model.dao.get_connection()
try:
with connection.cursor() as cursor:
cursor.execute(sql, self.__dict__['id'])
logger.debug("Executing sql: %s", sql)
connection.commit()
updated_row_id = cursor.lastrowid
finally:
connection.close()
return self.model.select(
Where(self.model.id, Where.EQUAL, updated_row_id))
def get_unfinished_states(instance):
instance_id = instance.id
states = Model.repository.states
return states.select(Where(states.instance_id, Where.EQUAL,
instance_id).AND(states.status,
Where.LESS_THAN, 500),
force_list=True)
def get_session_owner_id(session_id):
sessions = Model.repository.sessions
session = sessions.select(Where(sessions.id, Where.E, session_id))
if session is None:
return None
return session.owner_id
def get_next_state_in_instance(instance, status=None):
instance_id = instance.id
states = Model.repository.states
where = Where(states.instance_id, Where.EQUAL, instance_id)
if status is not None:
where = where.AND(states.status, Where.EQUAL, status.value)
state_list = states.select(where, force_list=True)
if len(state_list) > 0:
lowest_state = state_list[0]
else:
return None
for state in state_list:
if state.priority < lowest_state.priority:
lowest_state = state
return lowest_state
def create_instances(survey_id):
# Get survey
surveys = Model.repository.surveys
survey = surveys.select(Where(surveys.id, Where.E, survey_id))
# Get all participants in enrollment
participants = Model.repository.participants
participant_list = participants.select(Where(
participants.enrollment_id, Where.E, survey.enrollment_id),
force_list=True)
instances = Model.repository.instances
returnable = []
for participant in participant_list:
instance = instances.create()
instance.survey_id = survey_id
instance.participant_id = participant.id
instance.created = datetime.now(tz=pytz.utc)
returnable.append(instance.save())
return returnable
def get_participant(participant_id):
participants = Model.repository.participants
participant = participants.select(
Where(participants.id, Where.EQUAL, participant_id))
if participant is None:
return None
if EnrollmentService.is_enrollment_open(participant.enrollment_id):
return participant
else:
raise secure.SecurityException("Participant no longer accessible")
def create_session(owner_id):
one = os.urandom(16)
two = os.urandom(16)
session_id = binascii.hexlify(
hashlib.pbkdf2_hmac('sha512', one, two, 100000)).decode()
sessions = Model.repository.sessions
sessions.delete(Where(sessions.owner_id, Where.E, owner_id))
session = sessions.create()
session.id = session_id
session.owner_id = owner_id
session.expires = datetime.now() + timedelta(days=7)
return session.save(id_override=session_id)
def save_new(self, id_override=None):
columns, values = self.get_column_tuples()
sql = "INSERT INTO " + self.model.table_name + " " + columns + " VALUES %s"
connection = Model.dao.get_connection()
try:
with connection.cursor() as cursor:
cursor.execute(sql, [values])
logger.debug("Executing sql: %s", sql)
connection.commit()
new_row_id = cursor.lastrowid
finally:
connection.close()
if id_override is None:
return self.model.select(
Where(self.model.id, Where.EQUAL, new_row_id))
else:
return self.model.select(
Where(self.model.id, Where.E, id_override))
def parse_value(value):
if isinstance(value, str):
return "'" + value + "'"
if isinstance(value, datetime):
return "'" + value.strftime('%Y-%m-%d %H:%M:%S') + "'"
if isinstance(value, list) or isinstance(
value, set) or isinstance(value, tuple):
s = '('
for i in value:
s += Where.format_second_clause(i) + ', '
return s[:-2] + ')'
return str(value)
def get(name, domain):
owners = Model.repository.owners
return owners.select(
Where(owners.name, Where.EQUAL, name).AND(owners.domain,
Where.EQUAL, domain))
def get_by_id(owner_id):
owners = Model.repository.owners
return owners.select(Where(owners.id, Where.EQUAL, owner_id))
def get_participants_in_enrollment(enrollment_id):
participants = Model.repository.participants
return participants.select(Where(participants.enrollment_id,
Where.EQUAL, enrollment_id),
force_list=True)
def get_plugin(plugin_id):
plugins = Model.repository.plugins
return plugins.select(Where(plugins.id, Where.EQUAL, plugin_id))
def get_by_owner_id(owner_id):
plugins = Model.repository.plugins
return plugins.select(Where(plugins.owner_id, Where.E, owner_id), force_list=True)
def delete_session(session_id):
sessions = Model.repository.sessions
sessions.delete(Where(sessions.id, Where.E, session_id))
def get_task(task_id):
tasks = Model.repository.tasks
return tasks.select(Where(tasks.id, Where.E, task_id))
def get_protocol(protocol_id):
protocols = Model.repository.protocols
return protocols.select(Where(protocols.id, Where.EQUAL, protocol_id))
def get_surveys_by_owner(owner_id):
surveys = Model.repository.surveys
return surveys.select(Where(surveys.owner_id, Where.E, owner_id),
force_list=True)
def get_tasks_by_survey_id(survey_id):
tasks = Model.repository.tasks
return tasks.select(Where(tasks.survey_id, Where.E, survey_id),
force_list=True)
def delete_states_for_instances(instances):
states = Model.repository.states
instance_ids = [instance.id for instance in instances]
states.delete(Where(states.instance_id, Where.IN, instance_ids))
def delete_state(state):
if StateService.get_state(state.id) is not None:
states = Model.repository.states
states.delete(Where(states.id, Where.EQUAL, state.id))
def delete_plugin(plugin_id):
plugins = Model.repository.plugins
plugins.delete(Where(plugins.id, Where.E, plugin_id))
def get_survey(survey_id):
surveys = Model.repository.surveys
return surveys.select(Where(surveys.id, Where.EQUAL, survey_id))
def get_state_by_instance_and_question(instance_id, question_id):
states = Model.repository.states
return states.select(
Where(states.instance_id, Where.EQUAL,
instance_id).AND(states.question_id, Where.EQUAL,
question_id))
def get_tasks_since(task):
tasks = Model.repository.tasks
return tasks.select(Where(tasks.id, Where.GREATER_THAN, task.id),
force_list=True)
def get_protocols_owned_by(owner_id):
protocols = Model.repository.protocols
return protocols.select(Where(protocols.owner_id, Where.E, owner_id),
force_list=True)
def delete_task(task_id):
tasks = Model.repository.tasks
tasks.delete(Where(tasks.id, Where.E, task_id))
def get_state(state_id):
states = Model.repository.states
return states.select(Where(states.id, Where.EQUAL, state_id))
