class RescuetimeAdmin(db.Model): # Store RescueTime data id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(50), unique=True) notification = db.Column(db.Boolean, default=False) is_active = db.Column(db.Boolean, default=True) created_date = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, profile): self.email = profile.get('email') self.notification = profile.get('notification') self.is_active = profile.get('is_active') self.created_date = profile.get('created_date') def __repr__(self): result = {'id': self.id, 'email': self.email, 'notification': self.notification, 'is_active' : self.is_active, 'created_date': str(self.created_date)} return json.dumps(result) @staticmethod def add(data): new_row = RescuetimeAdmin(data) db.session.add(new_row) db.session.commit() return (200, 'Successfully added data.', new_row)
class DailyReminderConfig(db.Model): id = db.Column(db.Integer, primary_key=True) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) reminder_time = db.Column(db.String(10)) def __init__(self, info): self.reminder_time = info.get('reminder_time') self.code = info.get('code') def __repr__(self): result = {'id': self.id, 'reminder_time': self.reminder_time, 'code': self.code, 'created_at': str(self.created_at)} return json.dumps(result) @staticmethod def add(info): if not info.get('reminder_time'): return (-1, 'No reminder_time params', -1) existing_experiment = Experiment.query.filter_by(code=info['code']).first() if not existing_experiment: invalid_response = 'Invalid experiment code({})'.format(info['code']) return (-1, invalid_response, -1) new_reminder = DailyReminderConfig(info) db.session.add(new_reminder) db.session.commit() return (200, 'Successfully added daily reminder.', new_reminder)
class TP_DailyResetHour(db.Model): id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) admin_reset_hour = db.Column(db.Integer, default=0) # 0(midnight) to 23(11PM) def __init__(self, info): self.admin_reset_hour = info['admin_reset_hour'] def __repr__(self): result = { 'created_at': str(self.created_at), 'admin_reset_hour': self.admin_reset_hour } return json.dumps(result) @staticmethod def add(info): entry = TP_DailyResetHour(info) db.session.add(entry) db.session.commit() return (200, 'Successfully added reset time: {}.'.format( entry.admin_reset_hour), entry.admin_reset_hour) @staticmethod def get_last_updated_hour(): most_recent = TP_DailyResetHour.query.order_by( TP_DailyResetHour.created_at.desc()).first() return most_recent.admin_reset_hour if most_recent else 0
class Comment(db.Model): ''' model to store associated comments ''' id = db.Column(db.Integer, primary_key=True) text = db.Column(db.String(1000)) image_id = db.Column(db.Integer, db.ForeignKey('image.id'), nullable=False) posted_at = db.Column(db.DateTime)
class MturkMobile(db.Model): created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) worker_id = db.Column(db.String(50), primary_key=True, unique=True) last_installed_ms = db.Column(db.String(30)) pretty_last_installed = db.Column(db.String(30)) app_version_name = db.Column(db.String(10)) app_version_code = db.Column(db.String(10)) phone_model = db.Column(db.String(30)) android_version = db.Column(db.String(10)) device_country = db.Column(db.String(10)) device_id = db.Column(db.String(30)) def __init__(self, info): self.worker_id = info['worker_id'] self.last_installed_ms = info['last_installed_ms'] self.pretty_last_installed = info['pretty_last_installed'] self.app_version_name = info['app_version_name'] self.app_version_code = info['app_version_code'] self.phone_model = info['phone_model'] self.android_version = info['android_version'] self.device_country = info['device_country'] self.device_id = info['device_id'] def __repr__(self): result = {'worker_id': self.worker_id, 'device_id': self.device_id, 'created_at': self.created_at, 'last_installed_ms': self.last_installed_ms, 'pretty_last_installed': self.pretty_last_installed, 'app_version_name': self.app_version_name, 'app_version_code': self.app_version_code, 'phone_model': self.phone_model, 'device_country': self.device_country, 'android_version': self.android_version} return json.dumps(result) @staticmethod def add_user(info): """ add new mturk mobile user and return worker generated code if adding new worker """ existing_worker = MturkMobile.query.filter_by(worker_id=info['worker_id']).first() existing_device = MturkMobile.query.filter_by(device_id=info['device_id']).first() if existing_worker: if existing_worker.app_version_code == info['app_version_code']: return (-1, 'WorkerId already registered (v{})'.format(existing_worker.app_version_code), existing_worker.worker_id) else: MturkMobile.query.filter_by(worker_id=info['worker_id']).delete() db.session.commit() if existing_device: return (-1, 'This device is already registered with another WorkerId.', existing_device.worker_id) worker = MturkMobile(info) db.session.add(worker) db.session.commit() return (200, 'Successfully connected v{}!'.format(worker.app_version_code), worker.worker_id)
class Death(db.Model): __tablename__ = 'death' person_id = db.Column(db.BigInteger, primary_key=True) death_date = db.Column(db.DateTime()) def __init__(self, person_id, death_date): self.person_id = person_id self.death_date = death_date
class ScreenUnlockConfig(db.Model): id = db.Column(db.Integer, primary_key=True) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) time_limit = db.Column(db.Integer) unlocked_limit = db.Column(db.Integer) vibration_strength = db.Column(db.String(10)) show_stats = db.Column(db.Boolean, default=False) enable_user_pref = db.Column(db.Boolean, default=False) start_time = db.Column(db.String(50)) end_time = db.Column(db.String(50)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, info): self.code = info.get('code') self.time_limit = info.get('time_limit') self.unlocked_limit = info.get('unlocked_limit') self.vibration_strength = info.get('vibration_strength') self.show_stats = info.get('show_stats') self.enable_user_pref = info.get('enable_user_pref') self.start_time = info.get('start_time') self.end_time = info.get('end_time') def __repr__(self): result = {'id': self.id, 'code': self.code, 'time_limit': self.time_limit, 'unlocked_limit': self.unlocked_limit, 'vibration_strength': self.vibration_strength, 'show_stats': self.show_stats, 'enable_user_pref': self.enable_user_pref, 'start_time': self.start_time, 'end_time': self.end_time, 'created_at': str(self.created_at)} return json.dumps(result) @staticmethod def add(info): existing_experiment = Experiment.query.filter_by(code=info['code']).first() if not existing_experiment: invalid_response = 'Invalid experiment code({})'.format(info['code']) return (-1, invalid_response, -1) unlock_setting = ScreenUnlockConfig(info) db.session.add(unlock_setting) db.session.commit() return (200, 'Successfully added screen unlock setting.', unlock_setting)
class Mturk(db.Model): worker_id = db.Column(db.String(120), primary_key=True, unique=True) moves_id = db.Column(db.String(120), unique=True) access_token = db.Column(db.String(120), unique=True) refresh_token = db.Column(db.String(120), unique=True) code = db.Column(db.String(120), unique=True) ip = db.Column(db.String(24)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, worker_id): self.worker_id = worker_id self.code = Mturk.generate_unique_id() def __repr__(self): return 'worker_id: {}/moves_id: {}'.format(self.worker_id, self.moves_id) @staticmethod def generate_unique_id(): code = str(uuid.uuid4())[:6] while Mturk.query.filter_by(code=code).first(): code = str(uuid.uuid4())[:6] return code @staticmethod def get_worker(worker_id): """ Return user object from database using primary_key(email). """ return Mturk.query.get(worker_id) @staticmethod def add_user(info): """ add new mturk user and return worker generated code if adding new worker """ existing_worker = Mturk.query.filter_by(worker_id=info['worker_id']).first() existing_moves = Mturk.query.filter_by(moves_id=info['moves_id']).first() if existing_moves: return (-1, 'Moves app has already been connected. Contact Mturk requester.', existing_worker.code) elif existing_worker: return (-1, 'Worker has already been verified. Contact Mturk requester.', existing_worker.code) worker = Mturk(info['worker_id']) worker.moves_id = info['moves_id'] worker.access_token = info['access_token'] worker.refresh_token = info['refresh_token'] worker.code = str(uuid.uuid4()) worker.ip = str(info['ip']) db.session.add(worker) db.session.commit() return (200, 'Successfully connected your moves app!', worker.code) def update_field(self, key, value): """ Set user field with give value and save to database. """ worker = Mturk.query.get(self.worker_id) setattr(worker, key, value) db.session.commit()
class RescuetimeData(db.Model): # Store RescueTime data id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(50)) created_date = db.Column(db.DateTime) date = db.Column(db.DateTime) time_spent = db.Column(db.Integer) num_people = db.Column(db.Integer) activity = db.Column(db.String(120)) category = db.Column(db.String(120)) productivity = db.Column(db.Integer) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) def __init__(self, profile): self.email = profile.get('email') self.created_date = profile.get('created_date') self.date = profile.get('date') self.time_spent = profile.get('time_spent') self.num_people = profile.get('num_people') self.activity = profile.get('activity') self.category = profile.get('category') self.productivity = profile.get('productivity') def __repr__(self): result = {'id': self.id, 'email': self.email, 'created_date': str(self.created_date), 'date': str(self.date), 'time_spent': self.time_spent, 'num_people': self.num_people, 'activity': self.activity, 'category': self.category, 'productivity': self.productivity} return json.dumps(result) @staticmethod def add(data): new_row = RescuetimeData(data) db.session.add(new_row) db.session.commit() return (200, 'Successfully added data.', new_row)
class ImageTextUpload(db.Model): id = db.Column(db.Integer, primary_key=True) image_url = db.Column(db.String(100)) image_name = db.Column(db.String(100)) text = db.Column(db.String(1500)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) def __init__(self, info): self.image_url = info['image_url'] self.image_name = info['image_name'] self.text = info['text'] self.code = info['code'] def __repr__(self): result = {'id': self.id, 'image_url': self.image_url, 'image_name': self.image_name, 'text': self.text, 'code': self.code, 'created_at': str(self.created_at)} return json.dumps(result) @staticmethod def add(info): if ImageTextUpload.query.filter_by(image_url=info['image_url'], text=info['text']).first(): return new_image = ImageTextUpload(info) db.session.add(new_image) db.session.commit()
class Student(db.Model): student_id = db.Column(db.Integer, primary_key=True) first_name = db.Column(db.String(32), unique=False, nullable=False) last_name = db.Column(db.String(32), unique=False, nullable=False) grade = db.Column(db.Integer, nullable=False) username = db.Column(db.String(20), unique=True, nullable=False) password = db.Column(db.String(120), unique=False, nullable=False) school_id = db.Column(db.Integer, db.ForeignKey('school.school_id'), nullable=False) def __repr__(self): return f"Student'({self.username}','{self.image_file}')"
class Example(db.Model): __tablename__='example' id = db.Column(db.Integer, primary_key=True) column1 = db.Column(db.String(32)) column2 = db.Column(db.String(32)) column3 = db.Column(db.String(32)) def __init__(self, col1, col2, col3): self.column1 = col1 self.column2 = col2 self.column3 = col3 @staticmethod def generate(num): for i in range(num): s1 = random.choice(['2016','2017','2018']) s2 = str(random.randint(1,12)) s3 = str(random.randint(3000,5000)) e = Example(col1=s1, col2=s2, col3=s3) db.session.add(e) db.session.commit()
class MturkPrelimRecruit(db.Model): worker_id = db.Column(db.String(50), primary_key=True) worker_code = db.Column(db.String(10), unique=True) device_id = db.Column(db.String(30)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, info): self.worker_id = info['worker_id'] self.worker_code = MturkPrelimRecruit.generate_unique_id() self.device_id = info['device_id'] @staticmethod def generate_unique_id(): code = str(uuid.uuid4())[:6] while MturkPrelimRecruit.query.filter_by(worker_code=code).first(): code = str(uuid.uuid4())[:6] return code def __repr__(self): result = {'device_id': self.device_id, 'worker_id': self.worker_id, 'worker_code': self.worker_code, 'created_at': str(self.created_at)} return json.dumps(result) @staticmethod def add_worker(info): enrolled_worker = MturkPrelimRecruit.query.filter_by(worker_id=info['worker_id']).first() enrolled_device = MturkPrelimRecruit.query.filter_by(device_id=info['device_id']).first() if enrolled_worker: return (200, 'Worker already added.', enrolled_worker) elif enrolled_device: return (-1, 'Device already registered.', enrolled_worker) new_worker = MturkPrelimRecruit(info) db.session.add(new_worker) db.session.commit() return (200, 'Successfully submitted worker id.', new_worker)
class CalendarConfig(db.Model): id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) event_num_limit = db.Column(db.String(5)) event_time_limit = db.Column(db.String(10)) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) def __init__(self, info): self.event_num_limit = info.get('event_num_limit') self.event_time_limit = info.get('event_time_limit') self.code = info.get('code') def __repr__(self): result = {'id': self.id, 'event_time_limit': self.event_time_limit, 'event_num_limit': self.event_num_limit, 'code': self.code, 'created_at': str(self.created_at)} return json.dumps(result) @staticmethod def add(info): if not info.get('event_num_limit') and not info.get('event_time_limit'): print '**********************' print 'calendar params: {}'.format(info) print '**********************' return (-1, 'No calendar params', -1) existing_experiment = Experiment.query.filter_by(code=info['code']).first() if not existing_experiment: invalid_response = 'Invalid experiment code({})'.format(info['code']) return (-1, invalid_response, -1) new_setting = CalendarConfig(info) db.session.add(new_setting) db.session.commit() return (200, 'Successfully added cal setting.', new_setting)
class MobileNotifLogs(db.Model): id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) worker_id = db.Column(db.String(50)) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) time_millis = db.Column(db.BigInteger) app_id = db.Column(db.String(30)) posted_millis = db.Column(db.BigInteger) action = db.Column(db.String(10)) def __init__(self, info): self.worker_id = info['worker_id'] self.code = info['code'] self.time_millis = info['time_millis'] self.app_id = info['app_id'][:30] self.posted_millis = info['posted_millis'] self.action = info['action'] def __repr__(self): result = { 'id': str(self.id), 'created_at': str(self.created_at), 'worker_id': self.worker_id, 'code': self.code, 'time_millis': self.time_millis, 'posted_millis': self.posted_millis, 'action': self.action } return json.dumps(result) @staticmethod def add_stats(info): rows = info['logs'].split(';') for row in rows: if row != "": time_millis, app_id, posted_millis, action = row.split(",") new_stats = MobileNotifLogs({ 'worker_id': info['worker_id'], 'code': info['code'], 'time_millis': time_millis, 'app_id': app_id, 'posted_millis': posted_millis, 'action': action }) db.session.add(new_stats) db.session.commit() return 200, 'Successfully added phone notif logs!', ""
class NafEnroll(db.Model): worker_id = db.Column(db.String(50), primary_key=True) worker_code = db.Column(db.String(10), unique=True) group = db.Column(db.String(10)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, info): self.worker_id = info['worker_id'] self.group = info['group'] self.worker_code = NafEnroll.generate_unique_id() @staticmethod def generate_unique_id(): code = str(uuid.uuid4())[:6] while NafEnroll.query.filter_by(worker_code=code).first(): code = str(uuid.uuid4())[:6] return code def __repr__(self): result = { 'worker_id': self.worker_id, 'worker_code': self.worker_code, 'group': self.group, 'created_at': str(self.created_at) } return json.dumps(result) @staticmethod def add_worker(info): enrolled_worker = NafEnroll.query.filter_by(worker_id=info['worker_id']).first() if enrolled_worker: return (-1, 'Worker already enrolled in experiment.', enrolled_worker) new_worker = NafEnroll(info) db.session.add(new_worker) db.session.commit() return (200, 'Successfully enrolled worker_id in experiment', new_worker)
class ConceptTable(db.Model): __tablename__ = 'concept' concept_id = db.Column(db.Integer, primary_key=True) concept_name = db.Column(db.String(255)) domain_id = db.Column(db.String(20)) def __init__(self, concept_id, concept_name, domain_id): self.concept_id = concept_id self.concept_name = concept_name self.domain_id = domain_id @classmethod def search_concept(self, concept_name, start, limit): # concept_id 반환 if start is None: start = 0 if limit is None: limit = 10 data = db.session.query(self).filter( self.concept_name.ilike(f'%{concept_name}%'))[start:limit + start] return data
class TP_FgAppLog(db.Model): id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) worker_id = db.Column(db.String(50)) app_id = db.Column(db.String(30)) time_seconds = db.Column(db.String(20)) time_millis = db.Column(db.BigInteger) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) def __init__(self, info): self.worker_id = info['worker_id'] self.code = info['code'] self.app_id = info['app_id'][:30] self.time_seconds = info['time_seconds'] self.time_millis = info['time_millis'] def __repr__(self): result = { 'id': str(self.id), 'created_at': str(self.created_at), 'worker_id': self.worker_id, 'code': self.code, 'app_id': self.app_id, 'time_seconds': self.time_seconds, 'time_millis': self.time_millis } return json.dumps(result) @staticmethod def add_stats(info): worker_id = info['worker_id'] code = info['code'] logs = info['logs'] rows = logs.split(';') for row in rows: if row == "": continue app_id, time_seconds, time_millis = row.split(",") entry = { 'worker_id': worker_id, 'code': code, 'app_id': app_id, 'time_seconds': time_seconds, 'time_millis': time_millis } new_stats = TP_FgAppLog(entry) db.session.add(new_stats) db.session.commit() return (200, 'Successfully added fgAppLog stats!', "")
class TP_FacebookLog(db.Model): id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) worker_id = db.Column(db.String(50)) time_millis = db.Column(db.BigInteger) fb_date = db.Column(db.String(30)) time_spent = db.Column(db.Integer) time_open = db.Column(db.Integer) def __init__(self, info): self.worker_id = info['worker_id'] self.time_millis = info['time_millis'] self.fb_date = info['fb_date'] self.time_spent = info['time_spent'] self.time_open = info['time_open'] def __repr__(self): result = { 'id': str(self.id), 'created_at': str(self.created_at), 'worker_id': self.worker_id, 'time_millis': self.time_millis, 'fb_date': self.fb_date, 'time_spent': self.time_spent, 'time_open': self.time_open } return json.dumps(result) @staticmethod def add_stats(info): worker_id = info['worker_id'] logs = info['logs'] rows = logs.split(';') for row in rows: if row == "": continue time_millis, fb_date, time_spent, time_open = row.split(",") entry = { 'worker_id': worker_id, 'time_millis': time_millis, 'fb_date': fb_date, 'time_spent': time_spent, 'time_open': time_open } new_stats = TP_FacebookLog(entry) db.session.add(new_stats) db.session.commit() return (200, 'Successfully added Facebook log!', "")
class School(db.Model): school_id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(64), nullable=False, unique=True ) zipcode = db.Column(db.String(10), nullable=False, unique=True) city = db.Column(db.String(35), nullable=False, unique=True) state = db.Column(db.String(20), nullable=False, unique=True) number_of_students = db.Column(db.Integer) school = db.relationship("Student", backref="school", lazy=True) def __repr__(self): return f"School'({self.name}')"
class RescuetimeConfig(db.Model): id = db.Column(db.Integer, primary_key=True) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) productive_duration = db.Column(db.String(50)) distracted_duration = db.Column(db.String(50)) productive_msg = db.Column(db.String(50)) distracted_msg = db.Column(db.String(50)) show_stats = db.Column(db.Boolean, default=False) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, info): self.code = info.get('code') self.productive_duration = info.get('productive_duration') self.distracted_duration = info.get('distracted_duration') self.productive_msg = info.get('productive_msg') self.distracted_msg = info.get('distracted_msg') self.show_stats = info.get('show_stats') def __repr__(self): result = {'id': self.id, 'code': self.code, 'productive_duration': self.productive_duration, 'distracted_duration': self.distracted_duration, 'productive_msg': self.productive_msg, 'distracted_msg': self.distracted_msg, 'show_stats': self.show_stats, 'created_at': str(self.created_at)} return json.dumps(result) @staticmethod def add(info): existing_experiment = Experiment.query.filter_by(code=info['code']).first() if not existing_experiment: invalid_response = 'Invalid experiment code({})'.format(info['code']) return (-1, invalid_response, -1) rt_config = RescuetimeConfig(info) db.session.add(rt_config) db.session.commit() return (200, 'Successfully added rescuetime config.', rt_config)
class VibrationConfig(db.Model): id = db.Column(db.Integer, primary_key=True) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) app_id = db.Column(db.String(50)) time_limit = db.Column(db.Integer) open_limit = db.Column(db.Integer) vibration_strength = db.Column(db.String(10)) show_stats = db.Column(db.Boolean, default=False) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, info): self.code = info.get('code') self.app_id = info.get('app_id') self.time_limit = info.get('time_limit') self.open_limit = info.get('open_limit') self.vibration_strength = info.get('vibration_strength') self.show_stats = info.get('show_stats') def __repr__(self): result = {'id': self.id, 'code': self.code, 'app_id': self.app_id, 'time_limit': self.time_limit, 'open_limit': self.open_limit, 'vibration_strength': self.vibration_strength, 'show_stats': self.show_stats, 'created_at': str(self.created_at)} return json.dumps(result) @staticmethod def add(info): existing_experiment = Experiment.query.filter_by(code=info['code']).first() if not existing_experiment: invalid_response = 'Invalid experiment code({})'.format(info['code']) return (-1, invalid_response, -1) new_vibr_setting = VibrationConfig(info) db.session.add(new_vibr_setting) db.session.commit() return (200, 'Successfully added vibration setting.', new_vibr_setting)
class MobileSurvey(db.Model): id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(50), db.ForeignKey('participant.email')) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) header = db.Column(db.String(200)) response = db.Column(db.String(500)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, info): self.email = info['email'] self.code = info['code'] self.header = info['header'] self.response = info['response'] def __repr__(self): result = { 'email': self.email, 'code': self.code, 'header': self.header, 'response': self.response, 'created_at': str(self.created_at) } return json.dumps(result) @staticmethod def add_stats(info): rows = info['logs'].split('\n') if rows == "": print print '***************No survey data.***************' print header = rows[0] response = ';'.join(rows[1:]) if len(rows) > 0 and header != "" and response != "": entry = { 'email': info['email'].strip('#'), 'code': info['code'].strip(), 'header': header, 'response': response } new_stat = MobileSurvey(entry) db.session.add(new_stat) db.session.commit() return 200, 'Successfully added survey log!', len(rows)
class ConditionOccurrence(db.Model): __tablename__ = 'condition_occurrence' condition_occurrence_id = db.Column(db.BigInteger, primary_key=True) condition_concept_id = db.Column(db.BigInteger) person_id = db.Column(db.BigInteger) visit_occurrence_id = db.Column(db.Integer) condition_start_datetime = db.Column(db.DateTime()) condition_end_datetime = db.Column(db.DateTime()) def __init__(self, condition_concept_id, person_id, visit_occurrence_id, condition_start_datetime, condition_end_datetime): self.condition_concept_id = condition_concept_id self.person_idperson_id = person_id self.visit_occurrence_id = visit_occurrence_id self.condition_start_datetime = condition_start_datetime self.condition_end_datetime = condition_end_datetime @classmethod def condition_search(self, type, value, start, limit): # concept을 포함한 condition 데이터 반환 if start is None: start = 0 if limit is None: limit = 10 query = "SELECT c.condition_concept_id, c.person_id, c.visit_occurrence_id, a.concept_name as condition_concept_name" \ " , c.condition_start_datetime, c.condition_end_datetime" \ " FROM condition_occurrence c " \ " INNER JOIN concept a ON c.condition_concept_id = a.concept_id " if type == "person_id": query += " WHERE c.person_id ={}".format(value) elif type == "visit_occurrence_id": query += " WHERE c.visit_occurrence_id ={}".format(value) elif type == "concpet_name": query += " WHERE a.concept_name LIKE '%{}%'".format(value) query += " LIMIT {} OFFSET {}".format(limit, start) data = db.session.execute(query).fetchall() return data
class InAppAnalytics(db.Model): id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(50), db.ForeignKey('participant.email')) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) event_time_millis = db.Column(db.BigInteger) event_desc = db.Column(db.String(50)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, info): self.email = info['email'] self.code = info['code'] self.event_time_millis = info['event_time_millis'] self.event_desc = info['event_desc'] def __repr__(self): result = { 'id': self.id, 'email': self.email, 'code': self.code, 'event_time_millis': str(self.event_time_millis), 'event_desc': self.event_desc, 'created_at': str(self.created_at) } return json.dumps(result) @staticmethod def add_stats(info): rows = info['logs'].split('\n') for row in rows: if row == "" or info['email'] == "": continue event_time_millis, event_desc = row.split(",") entry = { 'email': info['email'].strip('#'), 'code': info['code'].strip(), 'event_time_millis': event_time_millis, 'event_desc': event_desc } new_stat = InAppAnalytics(entry) db.session.add(new_stat) db.session.commit() return 200, 'Successfully added InAppAnalytics Event!', ""
class Image(db.Model): ''' Model to store basic image information ''' id = db.Column(db.Integer, primary_key=True) image_location = db.Column(db.String(200)) thumbnail_location = db.Column(db.String(200)) has_food = db.Column(db.Float) not_food = db.Column(db.Float) comments = db.relationship('Comment', backref='image') posted_at = db.Column(db.DateTime) @hybrid_property def num_comments(self): return db.session.query(db.func.count(Comment.id)).filter(Comment.image_id == self.id) @num_comments.expression def _num_comments_expression(cls): return (db.select([db.func.count(Comment.id).label("num_comments")]) .where(Comment.image_id == cls.id) .label("total_comments") )
class GeneralNotificationConfig(db.Model): id = db.Column(db.Integer, primary_key=True) code = db.Column(db.String(10), db.ForeignKey('experiment.code')) intv_id = db.relationship('Intervention', backref='general_notification_config', lazy='select') title = db.Column(db.String(50)) content = db.Column(db.String(50)) app_id = db.Column(db.String(50)) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) def __init__(self, info): self.title = info.get('title') self.content = info.get('content') self.app_id = info.get('app_id') self.code = info.get('code') def __repr__(self): result = {'id': self.id, 'title': self.title, 'content': self.content, 'app_id': self.app_id, 'code': self.code, 'created_at': str(self.created_at)} return json.dumps(result) @staticmethod def add(info): existing_experiment = Experiment.query.filter_by(code=info['code']).first() if not existing_experiment: invalid_response = 'Invalid experiment code({})'.format(info['code']) return (-1, invalid_response, -1) new_notif = GeneralNotificationConfig(info) db.session.add(new_notif) db.session.commit() return (200, 'Successfully added general notification setting.', new_notif)
class TP_Admin(db.Model): id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) worker_id = db.Column(db.String(50)) study_code = db.Column(db.String(10)) admin_experiment_group = db.Column(db.Integer) admin_fb_max_mins = db.Column(db.Integer) admin_fb_max_opens = db.Column(db.Integer) admin_treatment_start = db.Column(db.DateTime) admin_followup_start = db.Column(db.DateTime) admin_logging_stop = db.Column(db.DateTime) def __init__(self, info): self.worker_id = info['worker_id'] self.study_code = info['study_code'] self.admin_experiment_group = info.get('admin_experiment_group') self.admin_fb_max_mins = info.get('admin_fb_max_mins') self.admin_fb_max_opens = info.get('admin_fb_max_opens') self.admin_treatment_start = to_datetime( info.get('admin_treatment_start'), "%Y%m-%d") self.admin_followup_start = to_datetime( info.get('admin_followup_start'), "%Y-%m-%d") self.admin_logging_stop = to_datetime(info.get('admin_logging_stop'), "%Y-%m-%d") def __repr__(self): result = { 'id': self.id, 'created_at': str(self.created_at), 'worker_id': self.worker_id, 'study_code': self.study_code, 'admin_experiment_group': self.admin_experiment_group, 'admin_fb_max_mins': self.admin_fb_max_mins, 'admin_fb_max_opens': self.admin_fb_max_opens, 'admin_treatment_start': str(self.admin_treatment_start), 'admin_followup_start': str(self.admin_followup_start), 'admin_logging_stop': str(self.admin_logging_stop) } return json.dumps(result) @staticmethod def add_user(info): worker = TP_Admin.query.filter_by(worker_id=info['worker_id']).first() if worker: return (200, 'Admin already added worker.', worker) new_worker = TP_Admin(info) db.session.add(new_worker) db.session.commit() return (200, 'Admin successfully added new worker!', new_worker) @staticmethod def update_user(info): worker = TP_Admin.query.filter_by(worker_id=info['worker_id']).first() if not worker: return (-1, 'Error: worker not in admin dashboard.', -1) worker.admin_experiment_group = info.get('admin_experiment_group') worker.admin_fb_max_mins = info.get('admin_fb_max_mins') worker.admin_fb_max_opens = info.get('admin_fb_max_opens') worker.admin_treatment_start = info.get('admin_treatment_start') worker.admin_followup_start = info.get('admin_followup_start') worker.admin_logging_stop = info.get('admin_logging_stop') db.session.commit() return (200, 'Successfully updated admin user settings!', worker)
class TP_Enrolled(db.Model): created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) worker_id = db.Column(db.String(50), primary_key=True, unique=True) worker_code = db.Column(db.String(6), unique=True) last_installed_ms = db.Column(db.String(30)) pretty_last_installed = db.Column(db.String(30)) app_version_name = db.Column(db.String(10)) app_version_code = db.Column(db.String(10)) phone_model = db.Column(db.String(30)) android_version = db.Column(db.String(10)) device_country = db.Column(db.String(10)) device_id = db.Column(db.String(30)) def __init__(self, info): self.worker_code = TP_Enrolled.generate_unique_id() self.worker_id = info['worker_id'] self.last_installed_ms = info['last_installed_ms'] self.pretty_last_installed = info['pretty_last_installed'] self.app_version_name = info['app_version_name'] self.app_version_code = info['app_version_code'] self.phone_model = info['phone_model'] self.android_version = info['android_version'] self.device_country = info['device_country'] self.device_id = info['device_id'] def __repr__(self): result = { 'worker_id': self.worker_id, 'worker_code': self.worker_code, 'device_id': self.device_id, 'created_at': str(self.created_at), 'last_installed_ms': self.last_installed_ms, 'pretty_last_installed': self.pretty_last_installed, 'app_version_name': self.app_version_name, 'app_version_code': self.app_version_code, 'phone_model': self.phone_model, 'device_country': self.device_country, 'android_version': self.android_version } return json.dumps(result) @staticmethod def generate_unique_id(): code = str(uuid.uuid4())[:6] while TP_Enrolled.query.filter_by(worker_code=code).first(): code = str(uuid.uuid4())[:6] return code @staticmethod def add_user(info): existing_worker = TP_Enrolled.query.filter_by( worker_id=info['worker_id']).first() if existing_worker: return (200, 'Welcome back!', existing_worker) existing_device = TP_Enrolled.query.filter_by( device_id=info['device_id']).first() if existing_device: return (200, 'Welcome back! Device already registered.', existing_device) # return (-1, 'Device already registered with another WorkerId.', existing_device) new_worker = TP_Enrolled(info) db.session.add(new_worker) db.session.commit() return (200, 'Successfully connected!', new_worker)
class TP_FBStats(db.Model): id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) worker_id = db.Column(db.String(50)) total_seconds = db.Column(db.Integer) total_opens = db.Column(db.Integer) time_spent = db.Column(db.Integer) time_open = db.Column(db.Integer) ringer_mode = db.Column(db.String(10)) daily_reset_hour = db.Column(db.Integer) # 0(midnight) to 23(11PM) # current params updated through from android app current_experiment_group = db.Column(db.Integer) current_fb_max_mins = db.Column(db.Integer) current_fb_max_opens = db.Column(db.Integer) current_treatment_start = db.Column(db.DateTime) current_followup_start = db.Column(db.DateTime) current_logging_stop = db.Column(db.DateTime) current_firebase_token = db.Column(db.String(500)) current_static_ratio_100 = db.Column(db.Integer) current_adaptive_ratio_100 = db.Column(db.Integer) current_ratio_of_limit = db.Column(db.Float) local_time = db.Column(db.String(25)) time_spent_list = db.Column(db.String(500)) num_opens_list = db.Column(db.String(500)) def __init__(self, info): self.worker_id = info['worker_id'] self.total_seconds = info['total_seconds'] self.total_opens = info['total_opens'] self.time_spent = info['time_spent'] self.time_open = info['time_open'] self.ringer_mode = info['ringer_mode'] self.daily_reset_hour = info['daily_reset_hour'] self.current_experiment_group = info.get('current_experiment_group') self.current_fb_max_mins = info.get('current_fb_max_mins') self.current_fb_max_opens = info.get('current_fb_max_opens') self.current_treatment_start = info.get('current_treatment_start') self.current_followup_start = info.get('current_followup_start') self.current_logging_stop = info.get('current_logging_stop') self.current_firebase_token = info.get('current_firebase_token') self.current_static_ratio_100 = info.get('current_static_ratio_100') self.current_adaptive_ratio_100 = info.get( 'current_adaptive_ratio_100') self.current_ratio_of_limit = info.get('current_ratio_of_limit') self.local_time = info.get('local_time') self.time_spent_list = info.get('time_spent_list') self.num_opens_list = info.get('num_opens_list') def __repr__(self): result = { 'created_at': str(self.created_at), 'worker_id': self.worker_id, 'total_seconds': self.total_seconds, 'total_opens': self.total_opens, 'time_spent': self.time_spent, 'time_open': self.time_open, 'ringer_mode': self.ringer_mode, 'daily_reset_hour': self.daily_reset_hour, 'current_experiment_group': self.current_experiment_group, 'current_fb_max_mins': self.current_fb_max_mins, 'current_fb_max_opens': self.current_fb_max_opens, 'current_treatment_start': str(self.current_treatment_start), 'current_followup_start': str(self.current_followup_start), 'current_logging_stop': str(self.current_logging_stop), 'current_firebase_token': str(self.current_firebase_token), 'current_static_ratio_100': str(self.current_static_ratio_100), 'current_adaptive_ratio_100': str(self.current_adaptive_ratio_100), 'current_ratio_of_limit': str(self.current_ratio_of_limit), 'local_time': str(self.local_time), 'time_spent_list': str(self.time_spent_list), 'num_opens_list': str(self.num_opens_list) } return json.dumps(result) @staticmethod def add_stats(info): new_stats = TP_FBStats(info) db.session.add(new_stats) db.session.commit() return (200, 'Successfully added fbstats!', new_stats)