class ScheduleCall(db.Model): # tracks outbound calls to target __tablename__ = 'schedule_call' id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime(timezone=True)) subscribed = db.Column(db.Boolean, default=True) time_to_call = db.Column(db.Time()) # should be UTC last_called = db.Column(db.DateTime(timezone=True)) num_calls = db.Column(db.Integer, default=0) campaign_id = db.Column(db.ForeignKey('campaign_campaign.id')) campaign = db.relationship('Campaign', backref=db.backref('scheduled_call_subscribed', lazy='dynamic')) phone_number = db.Column(phone_number.PhoneNumberType()) job_id = db.Column(db.String(36)) # UUID4 def __init__(self, campaign_id, phone_number, time): self.created_at = utc_now() self.campaign_id = campaign_id self.phone_number = phone_number self.time_to_call = time or utc_now().time() def __repr__(self): return u'<ScheduleCall for {} to {}>'.format(self.campaign.name, self.phone_number.e164) @property def _function_name(self): return 'create_call:{campaign_id}:{phone}'.format( campaign_id=self.campaign_id, phone=self.phone_number.e164) def user_phone(self): return self.phone_number.e164 def start_job(self, location=None): self.subscribed = True weekdays = 'mon,tue,wed,thu,fri' minute = self.time_to_call.minute hour = self.time_to_call.hour # TODO, set a max limit on the number of executions? crontab = '{minute} {hour} {day_of_month} {month} {days_of_week}'.format( minute=minute, hour=hour, day_of_month='*', month='*', days_of_week=weekdays) cron_job = create_call.cron(crontab, self._function_name, self.campaign_id, self.phone_number.e164, location) self.job_id = cron_job.id def stop_job(self): self.subscribed = False rq.get_scheduler().cancel(self.job_id)
class TwilioPhoneNumber(db.Model): __tablename__ = 'campaign_phone' id = db.Column(db.Integer, primary_key=True) twilio_sid = db.Column(db.String(TWILIO_SID_LENGTH)) twilio_app = db.Column(db.String(TWILIO_SID_LENGTH)) call_in_allowed = db.Column(db.Boolean, default=False) call_in_campaign_id = db.Column(db.Integer, db.ForeignKey('campaign_campaign.id')) number = db.Column(phone_number.PhoneNumberType()) call_in_campaign = db.relationship('Campaign', foreign_keys=[call_in_campaign_id]) def __unicode__(self): # use e164 for external apis, but international formatting for display return self.number.international @classmethod def available_numbers(*args, **kwargs): # returns all existing numbers return TwilioPhoneNumber.query # should filter_by(call_in_allowed=False), and also include numbers for this campaign def set_call_in(self, campaign): twilio_client = current_app.config.get('TWILIO_CLIENT') twilio_app_name = 'CallPower (%s)' % campaign.name # set app VoiceUrl post to campaign url campaign_call_url = (url_for('call.incoming', _external=True) + '?campaignId=' + str(campaign.id)) # get or create twilio app by campaign name apps = twilio_client.applications.list(friendly_name=twilio_app_name) if apps: app_sid = apps[0].sid # there can be only one! app = twilio_client.applications.update( app_sid, friendly_name=twilio_app_name, voice_url=campaign_call_url, voice_method="POST") else: app = twilio_client.applications.create( friendly_name=twilio_app_name, voice_url=campaign_call_url, voice_method="POST") success = (app.voice_url == campaign_call_url) # set twilio number to use app twilio_client.phone_numbers.update(self.twilio_sid, voice_application_sid=app.sid) self.twilio_app = app.sid self.call_in_campaign_id = campaign.id return success
class TwilioPhoneNumber(db.Model): __tablename__ = 'campaign_phone' id = db.Column(db.Integer, primary_key=True) twilio_sid = db.Column(db.String(TWILIO_SID_LENGTH)) twilio_app = db.Column(db.String(TWILIO_SID_LENGTH)) call_in_allowed = db.Column(db.Boolean, default=False) call_in_campaign_id = db.Column(db.Integer, db.ForeignKey('campaign_campaign.id')) number = db.Column(phone_number.PhoneNumberType()) call_in_campaign = db.relationship('Campaign', foreign_keys=[call_in_campaign_id]) def __str__(self): # use e164 for external apis, but international formatting for display return self.number.international @classmethod def available_numbers(*args, **kwargs): # returns all existing numbers return TwilioPhoneNumber.query # should filter_by(call_in_allowed=False), and also include numbers for this campaign def set_call_in(self, campaign): twilio_client = current_app.config.get('TWILIO_CLIENT') twilio_app_data = {'friendly_name': 'CallPower (%s)' % campaign.name} # set twilio_app VoiceUrl post to campaign url campaign_call_url = (url_for('call.incoming', _external=True) + '?campaignId=' + str(campaign.id)) twilio_app_data['voice_url'] = campaign_call_url twilio_app_data['voice_method'] = "POST" # set twilio_app StatusCallback post for completed event campaign_status_url = url_for('call.status_inbound', _external=True, campaignId=str(campaign.id)) twilio_app_data['status_callback'] = campaign_status_url twilio_app_data['status_callback_method'] = "POST" # get or create twilio app by campaign name existing_apps = twilio_client.applications.list(friendly_name=twilio_app_data['friendly_name']) if existing_apps: app_sid = existing_apps[0].sid # there can be only one! twilio_app = twilio_client.applications(app_sid).fetch() twilio_app.update(**twilio_app_data) else: twilio_app = twilio_client.applications.create(**twilio_app_data) success = (twilio_app.voice_url == campaign_call_url) # set twilio call_in_number to use app call_in_number = twilio_client.incoming_phone_numbers(self.twilio_sid).fetch() call_in_number.update(voice_application_sid=twilio_app.sid) self.twilio_app = twilio_app.sid self.call_in_campaign_id = campaign.id return success
class TargetOffice(db.Model): __tablename__ = 'campaign_target_office' id = db.Column(db.Integer, primary_key=True) uid = db.Column(db.String(STRING_LEN), index=True, nullable=True) # for US, this is bioguide_id-location_name name = db.Column(db.String(STRING_LEN), nullable=True) address = db.Column(db.String(STRING_LEN), nullable=True, unique=False) location = db.Column(db.String(STRING_LEN), nullable=True, unique=False) number = db.Column(phone_number.PhoneNumberType()) target_id = db.Column(db.Integer, db.ForeignKey('campaign_target.id')) def __unicode__(self): return u"{} {}".format(self.target, self.type) def phone_number(self): return self.number.e164
class Target(db.Model): __tablename__ = 'campaign_target' id = db.Column(db.Integer, primary_key=True) uid = db.Column(db.String(STRING_LEN), index=True, nullable=True) # for US, this is bioguide_id title = db.Column(db.String(STRING_LEN), nullable=True) name = db.Column(db.String(STRING_LEN), nullable=False, unique=False) number = db.Column(phone_number.PhoneNumberType()) def __unicode__(self): return self.uid def full_name(self): return u'{} {}'.format(self.title, self.name) def phone_number(self): return self.number.e164 @classmethod def get_uid_or_cache(cls, uid, prefix=None): if prefix: key = '%s:%s' % (prefix, uid) else: key = uid t = Target.query.filter(Target.uid == key) \ .order_by(Target.id.desc()).first() # return most recently cached target cached = False if not t: cached_obj = cache.get(key) if type(cached_obj) is list: data = adapt_to_target(cached_obj[0], prefix) elif type(cached_obj) is dict: data = adapt_to_target(cached_obj, prefix) else: # do it live data = cached_obj # create target object and save to db t = Target(**data) db.session.add(t) db.session.commit() cached = True return t, cached
class Target(db.Model): __tablename__ = 'campaign_target' id = db.Column(db.Integer, primary_key=True) uid = db.Column(db.String(STRING_LEN), index=True, nullable=True) # for US, this is bioguide_id title = db.Column(db.String(STRING_LEN), nullable=True) name = db.Column(db.String(STRING_LEN), nullable=False, unique=False) district = db.Column(db.String(STRING_LEN), nullable=True) number = db.Column(phone_number.PhoneNumberType()) location = db.Column(db.String(STRING_LEN), nullable=True, unique=False) offices = db.relationship('TargetOffice', backref="target") def __unicode__(self): return self.uid def full_name(self): return u'{} {}'.format(self.title, self.name) def phone_number(self): return self.number.e164 @classmethod def get_or_create(cls, uid, prefix=None, cache=cache): if prefix: key = '%s:%s' % (prefix, uid) else: key = uid t = Target.query.filter(Target.uid == key) \ .order_by(Target.id.desc()).first() created = False data = check_political_data_cache(key, cache) offices = data.pop('offices') if not t: # create target object t = Target(**data) db.session.add(t) created = True elif data: # check for updated data check_attrs = ['location', 'number'] for a in check_attrs: new_val = data.get(a) if new_val and getattr(t, a) != new_val: setattr(t, a, new_val) created = True if offices: existing_target_office_uids = [o.uid for o in t.offices] # need to check against existing offices, because the underlying data may have been updated for office in offices: if office.get('uid') in existing_target_office_uids: # existing office, check to update the location and type o = TargetOffice.query.filter_by( target_id=t.id, uid=office.get('uid')).first() check_attrs = ['name', 'type', 'address', 'number'] for a in check_attrs: if getattr(o, a) != office.get(a): setattr(o, a, office.get(a)) created = True else: # create new office object, link to target o = TargetOffice(**office) o.target = t db.session.add(o) created = True if created: # save to db db.session.commit() return t, created
class User(db.Model, UserMixin): __tablename__ = 'user_user' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(STRING_LEN), nullable=False, unique=True) email = db.Column(db.String(STRING_LEN), nullable=False, unique=True) openid = db.Column(db.String(STRING_LEN), unique=True) activation_key = db.Column(db.String(STRING_LEN)) created_time = db.Column(db.DateTime, default=datetime.utcnow) last_accessed = db.Column(db.DateTime) phone = db.Column(phone_number.PhoneNumberType()) _password = db.Column('password', db.String(PASSWORD_LEN_MAX), nullable=False) def __unicode__(self): return self.name def _get_password(self): return self._password def _set_password(self, password): self._password = generate_password_hash(password) # Hide password encryption by exposing password field only. password = db.synonym('_password', descriptor=property(_get_password, _set_password)) def check_password(self, password): if self.password is None: return False return check_password_hash(self.password, password) # One-to-many relationship between users and roles. role_code = db.Column(db.SmallInteger, default=USER_STAFF) @property def role(self): return USER_ROLE[self.role_code] def is_admin(self): return self.role_code == USER_ADMIN # One-to-many relationship between users and user_statuses. status_code = db.Column(db.SmallInteger, default=USER_ACTIVE) @property def status(self): return USER_STATUS[self.status_code] # ================================================================ # Class methods @classmethod def authenticate(cls, login, password): user = cls.query.filter(db.or_( func.lower(User.name) == func.lower(login), func.lower(User.email) == func.lower(login) )).first() if user: authenticated = user.check_password(password) else: authenticated = False return user, authenticated @classmethod def search(cls, keywords): criteria = [] for keyword in keywords.split(): keyword = '%' + keyword + '%' criteria.append(db.or_( User.name.ilike(keyword), User.email.ilike(keyword), )) q = reduce(db.and_, criteria) return cls.query.filter(q) @classmethod def get_by_id(cls, user_id): return cls.query.filter_by(id=user_id).first_or_404() def check_name(self, name): return User.query.filter(db.and_(User.name == name, User.email != self.id)).count() == 0
class Blocklist(db.Model): # stops __tablename__ = 'admin_blocklist' id = db.Column(db.Integer, primary_key=True) timestamp = db.Column(db.DateTime(timezone=True)) expires = db.Column(db.Interval) phone_number = db.Column(phone_number.PhoneNumberType(), nullable=True) phone_hash = db.Column(db.String(64), nullable=True) # hashed phone number (optional) ip_address = db.Column(db.String(16), nullable=True) hits = db.Column(db.Integer(), default=0) def __init__(self, phone_number=None, ip_address=None): self.timestamp = utc_now() self.phone_number = phone_number self.ip_address = ip_address def __unicode__(self): if self.phone_number: return self.phone_number.e164 if self.phone_hash: return self.phone_hash if self.ip_address: return self.ip_address def is_active(self): if self.expires: try: return utc_now() <= (self.timestamp + self.expires) except TypeError: # sqlite doesn't store timezones in the database # reset it manually aware_timestamp = self.timestamp.replace(tzinfo=pytz.utc) return utc_now() <= (aware_timestamp + self.expires) else: return True def match(self, user_phone, user_ip, user_country='US'): if self.ip_address: return self.ip_address == user_ip if self.phone_hash: return self.phone_hash == hashlib.sha256(user_phone).hexdigest() if self.phone_number: if type(user_phone) == str: return self.phone_number == phone_number.PhoneNumber( user_phone, user_country) elif type(user_phone) == phone_number.PhoneNumber: return self.phone_number.e164 == user_phone.e164 return None @classmethod def active_blocks(cls): return [b for b in Blocklist.query.all() if b.is_active()] @classmethod def user_blocked(cls, user_phone, user_ip, user_country='US'): """ Takes a phone number and/or IP address, check it against blocklist """ active_blocks = cls.active_blocks() if not active_blocks: # exit early if no blocks active return False matched = False for b in active_blocks: if b.match(user_phone, user_ip, user_country): if not b.phone_number: b.phone_number = user_phone matched = True b.hits += 1 db.session.add(b) if matched: db.session.commit() return matched
class User(self.Base): __tablename__ = 'user' id = sa.Column(sa.Integer, autoincrement=True, primary_key=True) name = sa.Column(sa.Unicode(255)) phone_number = sa.Column( phone_number.PhoneNumberType(country_code='FI'))
class Target(db.Model): __tablename__ = 'campaign_target' id = db.Column(db.Integer, primary_key=True) uid = db.Column(db.String(STRING_LEN), index=True, nullable=True) # for US, this is bioguide_id title = db.Column(db.String(STRING_LEN), nullable=True) name = db.Column(db.String(STRING_LEN), nullable=False, unique=False) district = db.Column(db.String(STRING_LEN), nullable=True) number = db.Column(phone_number.PhoneNumberType()) location = db.Column(db.String(STRING_LEN), nullable=True, unique=False) offices = db.relationship('TargetOffice', backref="target") def __unicode__(self): return self.uid def full_name(self): return u'{} {}'.format(self.title, self.name) def phone_number(self): return self.number.e164 @classmethod def get_or_cache_key(cls, uid, prefix=None, cache=cache): if prefix: key = '%s:%s' % (prefix, uid) else: key = uid t = Target.query.filter(Target.uid == key) \ .order_by(Target.id.desc()).first() # return most recently cached target cached = False if not t: adapter = adapt_by_key(key) adapted_key, adapter_suffix = adapter.key(key) cached_obj = cache.get(adapted_key) if type(cached_obj) is list: data = adapter.target(cached_obj[0]) offices = adapter.offices(cached_obj[0]) elif type(cached_obj) is dict: data = adapter.target(cached_obj) offices = adapter.offices(cached_obj) else: current_app.logger.error( 'Target.get_or_cache_key got unknown cached_obj type %s' % type(cached_obj)) # do it live data = cached_obj try: offices = cached_obj.get('offices', []) except AttributeError: offices = [] # create target object t = Target(**data) t.uid = adapted_key db.session.add(t) # create office objects, link to target for office in offices: if adapter_suffix: if not office['uid'] == adapter_suffix: continue o = TargetOffice(**office) o.target = t db.session.add(o) # save to db db.session.commit() cached = True return t, cached