File: //home/arjun/projects/buyercall_new/buyercall/buyercall/blueprints/leads/models.py
from collections import OrderedDict
from sqlalchemy import or_, and_
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import load_only
from sqlalchemy.sql import text, select
from buyercall.blueprints.filters import format_phone_number_bracket, format_phone_number
from buyercall.lib.util_crypto import EncryptedValue
from buyercall.lib.util_sqlalchemy import ResourceMixin
from buyercall.lib.util_twilio import account_client
from buyercall.extensions import db
class LeadNotes(db.Model):
__tablename__ = 'lead_notes'
id = db.Column(db.Integer, primary_key=True)
lead_id = db.Column(db.Integer, db.ForeignKey('leads.id'), nullable=False)
created_on = db.Column(db.DateTime(), nullable=False)
updated_on = db.Column(db.DateTime(), nullable=False)
is_enabled = db.Column(db.Boolean, nullable=False)
text = db.Column(db.Text(), nullable=True, server_default='')
user = db.relationship('User', backref='lead_notes')
user_id = db.Column(
db.Integer,
db.ForeignKey(
'users.id',
name='lead_notes_user_id_fkey',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=False
)
@classmethod
def create(cls, note):
"""
Return whether or not the agent schedule was created successfully.
:return: bool
"""
leadnote = note
db.session.add(LeadNotes(**leadnote))
db.session.commit()
return True
@classmethod
def update(cls, lead_note_id, lead_note_text, lead_note_updated_date, lead_note_user_id, lead_note_enabled):
"""
Return whether or not the agent schedule was updated successfully.
:return: bool
"""
if lead_note_enabled:
db.session.query(LeadNotes).filter_by(id=lead_note_id).update(
{"updated_on": lead_note_updated_date,
"text": lead_note_text,
"is_enabled": lead_note_enabled,
"user_id": lead_note_user_id}
)
db.session.commit()
else:
db.session.query(LeadNotes).filter_by(id=lead_note_id).update({"updated_on": lead_note_updated_date,
"is_enabled": lead_note_enabled,
"user_id": lead_note_user_id})
db.session.commit()
return True
@property
def date_edited(self):
""" Return the date the note was created
"""
return self.updated_on.strftime('%Y-%m-%d %H:%M:%S')
@property
def user_fullname(self):
""" Return the full name of the user that created the note
"""
return "{} {}".format(self.user.firstname, self.user.lastname)
class Lead(ResourceMixin, db.Model):
# This status dictionary is purely used for data population and not for production.
STATUSES = OrderedDict([
('ringing', 'ringing'),
('in-progress', 'in-progress'),
('completed', 'lead has been captured'),
('missed', 'lead was missed, and there will be no more retries'),
('retry-pending', 'lead was missed, but we will call them back')
])
PROGRESS = OrderedDict([
('new lead', 'New Lead'),
('general interest', 'General Interest'),
('follow-up required', 'Follow-up Required'),
('application started', 'Application Started'),
('under contract', 'Under Contract'),
('application submitted - credit union', 'Application Submitted - Credit Union'),
('application submitted - autopay', 'Application Submitted - AutoPay'),
('credit union declined no autopay', 'Credit Union Declined No AutoPay'),
('autopay declined', 'AutoPay Declined'),
('credit union contracted', 'Credit Union Contracted'),
('autopay contracted', 'AutoPay Contracted'),
('previous customer', 'Previous Customer'),
('service call', 'Service Call'),
('accounts call', 'Accounts Call'),
('sold customer', 'Sold Customer'),
('completed conversation', 'Completed Conversation'),
('do not contact', 'Do Not Contact')
])
__tablename__ = 'leads'
id = db.Column(db.Integer, primary_key=True)
# Relationships.
agent = db.relationship('Agent', back_populates='leads')
lead_notes = db.relationship(
'LeadNotes',
backref='lead',
cascade='all, delete-orphan',
lazy='dynamic',
order_by='desc(LeadNotes.created_on)'
)
partnership_account_id = db.Column(
db.Integer,
db.ForeignKey(
'partnership_accounts.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=False
)
""" The valid call sources are:
- `adf`
- `form`
- ``
"""
call_source = db.Column(db.String(128), nullable=True)
status = db.Column(db.String(64), index=True, nullable=False)
""" The valid lead statuses are:
- `ringing`
- `in-progress`
- `completed` - lead has been captured
- `missed` - lead was missed, and there will be no more retries
- `retry-pending` - lead was missed, but we will call them back
- `other` - everything else
"""
progress_status = db.Column(db.String(128), index=True, nullable=False,
server_default='no status')
firstname = db.Column(EncryptedValue(128), index=True, nullable=False,
server_default='')
caller_id = db.Column(EncryptedValue(128), index=True, nullable=False,
server_default='')
lastname = db.Column(EncryptedValue(128), index=True, nullable=False,
server_default='')
email = db.Column(EncryptedValue(128), index=True, nullable=False,
server_default='')
phonenumber = db.Column(db.String(), index=True, nullable=False,
server_default='')
my_phone = db.Column(db.String(), nullable=False, server_default='')
question = db.Column(db.String(255), server_default='')
duration = db.Column(db.Integer, nullable=True)
starttime = db.Column(db.DateTime(), nullable=True)
endtime = db.Column(db.DateTime(), nullable=True)
call_type = db.Column(db.String(10), nullable=False)
call_sid = db.Column(db.String(128), index=True, nullable=True,
server_default='')
transfer_call_id = db.Column(db.String(128), index=True, nullable=True,
server_default='')
missed_call_cause = db.Column(db.String(128), index=True, nullable=True,
server_default='')
cause_description = db.Column(db.String(512), index=False, nullable=True,
server_default='')
recording_url = db.Column(EncryptedValue(1024), nullable=True, server_default='')
recording_id = db.Column(db.String(128), index=True, nullable=True, server_default='')
transcription_text = db.Column(EncryptedValue(5120), nullable=True, server_default='')
transcription_text_2 = db.Column(EncryptedValue(5120), nullable=True, server_default='')
transcription_1_confidence = db.Column(db.String(16), nullable=True, server_default='')
transcription_2_confidence = db.Column(db.String(16), nullable=True, server_default='')
originating_number = db.Column(EncryptedValue(64),
index=True,
nullable=False,
server_default=''
)
widget_guid = db.Column(
db.String, db.ForeignKey(
'widgets.guid',
name='leads_widget_guid_fkey',
onupdate='CASCADE',
ondelete='SET NULL'
)
)
inbound_id = db.Column(db.Integer, db.ForeignKey(
'phonenumbers.id', name='leads_inbound_fkey', onupdate='CASCADE',
ondelete='SET NULL'
), index=True)
agent_id = db.Column(
db.Integer, db.ForeignKey(
'agents.id',
name='leads_agent_id_fkey',
onupdate='CASCADE', ondelete='SET NULL'
)
)
# The contact id associated with the lead
contact_id = db.Column(db.Integer, db.ForeignKey(
'contacts.id', name='lead_contact_fkey',
onupdate='CASCADE', ondelete='CASCADE'),
index=True, nullable=True)
agent_assigned_id = db.Column(db.Integer, nullable=True)
notes = db.Column(db.Text(), nullable=True, server_default='')
call_count = db.Column(db.Integer(), nullable=False, server_default='0')
# Tracking call response time.
response_time_seconds = db.Column(db.Float())
# Additional information
additional_info = db.Column(postgresql.JSON)
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return text('')
search_query = '%{0}%'.format(query)
return text(
or_(Lead.email.ilike(search_query),
Lead.firstname.ilike(search_query),
Lead.lastname.ilike(search_query),
Lead.status.ilike(search_query)
)
)
# This classmethod will update the lead twilio fields with the twilio API
@classmethod
def update_lead_twilio(cls, lead):
# import partnership information to get partnership id
from buyercall.blueprints.partnership.models import Partnership, PartnershipAccount
partner_account = PartnershipAccount.query \
.filter(PartnershipAccount.id == lead.partnership_account_id).first()
partner = Partnership.query.filter(Partnership.id == partner_account.partnership_id).first()
twilio_client = account_client(partner.id)
call = twilio_client.calls.get(lead.call_sid)
"""
Change an unknown lead type to contacted.
:param issue: Lead instance
:type issue: Lead instance
:return: Lead instance
"""
lead.status = call.status
lead.starttime = call.start_time
lead.endtime = call.end_time
lead.duration = call.duration
lead.save()
return lead
@classmethod
def get_last_known_name(cls, partnership_account_id, number):
""" Returns the last known first and last name for this phone number.
:param partnership_account_id: the ID of the current buyercall account
:param number: a US phone number in E.123 format (+1xxxyyyzzzz)
"""
lead = Lead.query.filter(
Lead.partnership_account_id == partnership_account_id,
Lead.phonenumber == number,
not Lead.firstname, # noqa
Lead.firstname != '',
Lead.firstname != 'Unknown'
).options(
load_only("firstname", "lastname")
).order_by(text('id desc')).first()
return (lead.firstname, lead.lastname) if lead else ('', '')
@hybrid_property
def assigned_full_name(self):
from buyercall.blueprints.agents.models import Agent
agents = Agent.query.filter(self.agent_assigned_id == Agent.id).first()
if agents:
return agents.full_name
@hybrid_property
def full_name(self):
return '{} {}'.format(self.firstname, self.lastname)
@full_name.setter
def full_name(self, value):
if ' ' in value:
self.firstname, self.lastname = value.split(' ', 1)
else:
self.firstname, self.lastname = value, ''
@full_name.expression
def full_name(cls):
return db.func.concat(cls.firstname, ' ', cls.lastname)
@property
def note_count(self):
""" Return the number of notes for this number
"""
count_result = LeadNotes.query.join(Lead).filter(
self.phonenumber == Lead.phonenumber,
LeadNotes.is_enabled
).count()
return count_result
@property
def interaction_count(self):
""" Return the number of calls for this number
"""
note_count = Lead.query.filter(
Lead.partnership_account_id == self.partnership_account_id,
Lead.phonenumber == self.phonenumber
).count()
return note_count
@property
def interaction_time(self):
""" Return the number of calls for this number
"""
return self.starttime.strftime('%Y-%m-%d %H:%M:%S')
@property
def agent_name(self):
if self.agent is None:
return ''
return self.agent.full_name
@hybrid_property
def source(self):
return (
self.widget and self.widget.name
) or (
self.inbound and self.inbound.friendly_name
) or ''
@source.expression
def source(cls):
from buyercall.blueprints.widgets.models import Widget
from buyercall.blueprints.phonenumbers.models import Phone
return db.func.coalesce(
Widget.name,
Phone.friendly_name,
''
)
@hybrid_property
def source_name(self):
return self.inbound.friendly_name or ''
@source_name.expression
def source_name(cls):
from buyercall.blueprints.phonenumbers.models import Phone
return select([Phone.friendly_name.label("name")]).where(
Phone.id == cls.inbound_id
).limit(1).label("phone")
@hybrid_property
def agent_full_name(self):
if self.agent is None:
return ''
return self.agent.full_name
@agent_full_name.expression
def agent_full_name(cls):
from buyercall.blueprints.agents.models import Agent
return select([Agent.full_name.label("name")]).where(
Agent.id == cls.agent_id
).limit(1).label("agent")
@hybrid_property
def source_id(self):
return (
self.widget and self.widget_guid
) or (
self.inbound and str(self.inbound_id)
) or ''
@property
def created_datetime(self):
""" Return the date/time this lead occurred
"""
return self.created_on.strftime('%Y-%m-%d %H:%M:%S')
@property
def updated_datetime(self):
""" Return the date/time this lead record was updated
"""
return self.updated_on.strftime('%Y-%m-%d %H:%M:%S')
@property
def contact_match(self):
""" Return the date/time this lead record was updated
"""
formatted_number = format_phone_number(self.phonenumber)
from buyercall.blueprints.contacts.models import Contact
contact = Contact.query.filter(and_(Contact.partnership_account_id == self.partnership_account_id,
Contact.phonenumber_1 == formatted_number)).first()
if contact and contact.firstname:
return "{} {}".format(contact.firstname, contact.lastname)
else:
if self.caller_id and self.caller_id.lower() != 'unknown':
call_id = "({})".format(self.caller_id)
else:
call_id = ''
return "{} {}".format(format_phone_number_bracket(self.phonenumber), call_id)
# @source.expression
# def source_id(cls):
# from ..widgets.models import Widget
# from ..phonenumbers.models import Phone
#
# return db.func.coalesce(
# Widget.guid,
# str(Phone.id),
# ''
# )
class LeadReporting(ResourceMixin, db.Model):
__tablename__ = 'leads_reporting'
id = db.Column(db.Integer, primary_key=True)
partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id',
name='lead_reporting_partnership_account_fkey'),
index=True, nullable=False)
call_source = db.Column(db.String(128), nullable=True)
status = db.Column(db.String(64), index=True, nullable=True)
progress_status = db.Column(db.String(128), nullable=True)
duration = db.Column(db.Integer, nullable=True)
starttime = db.Column(db.DateTime(), nullable=True)
endtime = db.Column(db.DateTime(), nullable=True)
call_type = db.Column(db.String(10), nullable=True)
call_sid = db.Column(db.String(128), nullable=True)
missed_call_cause = db.Column(db.String(128), nullable=True)
cause_description = db.Column(db.String(512), nullable=True)
recording_id = db.Column(db.String(128), nullable=True)
widget_guid = db.Column(db.String, db.ForeignKey(
'widgets.guid', name='lead_reporting_widget_guid_fkey'), nullable=True, index=True)
inbound_id = db.Column(db.Integer, db.ForeignKey(
'phonenumbers.id', name='lead_reporting_inbound_id_fkey'), index=True, nullable=False)
agent_id = db.Column(db.Integer, db.ForeignKey(
'agents.id', name='lead_reporting_agent_id_fkey'), index=True, nullable=True)
contact_id = db.Column(db.Integer, db.ForeignKey(
'contacts.id', name='lead_reporting_contact_id_fkey'), index=True, nullable=False)
# Tracking call response time.
response_time_seconds = db.Column(db.Float())