File: //home/arjun/projects/buyercall_new/buyercall/buyercall/blueprints/leads/views.py
import csv
import json
from contextlib import closing
from datetime import date
import math
from datetime import datetime, timedelta
from io import StringIO
from html import unescape
import logging
import boto3
from flask import (
Blueprint,
request,
flash,
jsonify,
url_for,
current_app,
make_response,
redirect,
render_template)
from sqlalchemy import extract, or_, and_, desc, func, text
from sqlalchemy.orm import contains_eager, load_only
from flask_login import login_required, current_user
from flask_babel import gettext as _
from math import ceil
from twilio.twiml.voice_response import VoiceResponse
from buyercall.extensions import csrf
from buyercall.extensions import db
from buyercall.lib.util_twilio import account_client
from .forms import LeadForm, LeadNoteForm
from .models import Lead, LeadNotes
from buyercall.blueprints.agents.models import Agent
from buyercall.blueprints.user.decorators import role_required
from buyercall.blueprints.filters import format_phone_number
leads = Blueprint('leads', __name__, template_folder='templates')
log = logging.getLogger(__name__)
# Searches the statuses dictioary for the correct key
def get_call_status_key(status_value):
result = ''
status_value = status_value.replace('_', ' ')
for k, v in Lead.STATUSES.items():
if k == status_value:
result = k
return result
def days_between(d1, d2):
d1 = datetime.strptime(d1, "%m%d%Y")
d2 = datetime.strptime(d2, "%m%d%Y")
return abs((d2 - d1).days)
def getboolean(param):
if param == 'true' or param == 'True':
return True
else:
return False
# Leads onboarding page
@leads.route('/leads_onboarding', methods=['GET', 'POST'])
@csrf.exempt
@login_required
def onboarding_leads():
if request.method == 'POST':
current_user.leads_onboard = True
db.session.commit()
if current_user.leads_onboard:
flash(_(
'Great, you are ready to get started with the My Leads page. '
'Remember to checkout the support section or FAQ if you have any '
'additional leads questions.'
), 'success')
return redirect(url_for('leads.call_leads'))
return render_template('leads/leads_onboarding.jinja2')
@leads.route('/leads')
@login_required
@role_required('admin', 'agent')
def call_leads():
agent_id = current_user.agent.id if current_user.agent else None
current_app.logger.debug('The agent id is {}'.format(agent_id))
partnership_account_id = current_user.partnership_account_id
is_admin_in_group = current_user.is_admin_user_with_groups
viewing_partnership_account = current_user.is_viewing_partnership
# Check if being viewed by super partner
if is_admin_in_group and viewing_partnership_account:
partnership_account_id = current_user.get_user_viewing_partnership_account_id
elif not viewing_partnership_account and is_admin_in_group:
return redirect(url_for('partnership.company_accounts'))
from buyercall.blueprints.phonenumbers.models import Phone
phonenumber = Phone.query.filter(Phone.partnership_account_id == partnership_account_id).first()
if current_user.role == 'agent':
filter_by = (agent_id == Lead.agent_id) | (agent_id == Lead.agent_assigned_id) |\
(Lead.partnership_account_id == current_user.partnership_account_id)
else:
filter_by = and_(
Lead.partnership_account_id == partnership_account_id, current_user.role == 'admin')
# Get current year, month
now = datetime.now()
year = now.year
month = now.month
total_calls = Lead.query \
.filter(or_(filter_by)).count()
monthly_calls = Lead.query.filter(or_(filter_by)) \
.filter(extract('year', Lead.created_on) == year) \
.filter(extract('month', Lead.created_on) == month).count()
# Check if onboarding was excepted
if current_user.leads_onboard is False:
return redirect(url_for('leads.onboarding_leads'))
day_delta = timedelta(days=31)
then = now - day_delta
date_from_value = datetime.strftime(then, "%m/%d/%Y")
date_to_value = datetime.strftime(now, "%m/%d/%Y")
return render_template(
'leads/leads.jinja2',
date_from=date_from_value,
date_to=date_to_value,
calls=0,
total_calls=total_calls,
monthly_calls=monthly_calls,
phonenumber=phonenumber,
unique_calls=0,
answered_calls=0,
percentage_answered=0,
percentage_missed=0,
call_duration_min=0,
avg_call_duration_min=0,
avg_call_duration_sec=0
)
# Display lead details and Add notes to a lead on edit page
@leads.route('/leads/edit/<int:id>', methods=['GET', 'POST'])
@login_required
def lead_edit(id):
partnership_account_id = current_user.partnership_account_id
is_admin_in_group = current_user.is_admin_user_with_groups
viewing_partnership_account = current_user.is_viewing_partnership
# Check if being viewed by super partner
if is_admin_in_group and viewing_partnership_account:
partnership_account_id = current_user.get_user_viewing_partnership_account_id
elif not viewing_partnership_account and is_admin_in_group:
return redirect(url_for('partnership.company_accounts'))
lead = Lead.query.filter(
Lead.id == id, Lead.partnership_account_id == partnership_account_id
).first()
if not lead:
flash('Lead with ID {} not found.'.format(id))
return redirect(url_for('leads.call_leads'))
# Use update_lead function to update some call info, like
# status, duration, start time etc.
# try:
# Lead.update_lead_twilio(lead)
# except Exception as e:
# log.error('Unable to update lead information from Twilio: {}'.format(
# e.message
# ))
form = LeadForm(obj=lead)
form_note = LeadNoteForm(obj=lead)
# Return the agent associated with the lead
agent = Agent.query.filter(lead.agent_id == Agent.id).first()
agent_name = agent.full_name if agent else ""
# Get list of agents
form.agent_assigned_id.choices = [(g.id, g.full_name) for g in Agent.query.filter(
(Agent.partnership_account_id == current_user.partnership_account_id),
Agent.is_deactivated.is_(False)).order_by('firstname')]
form.agent_assigned_id.choices.insert(0, (0, ''))
# Get list of statuses
from buyercall.blueprints.contacts.models import Status
status_list = Status.get_assigned_status_list(partnership_account_id)
form.progress_status.choices = [(s.status, s.display_name) for s in status_list]
# Set the lead assigned id in the db
lead.agent_assigned_id = request.form.get('agent_assigned_id')
# Return response time in minutes
if lead.response_time_seconds is not None:
response_time_min = int(lead.response_time_seconds / 60)
response_time_sec = int(lead.response_time_seconds % 60)
else:
response_time_min = 0
response_time_sec = 0
duration_minute = None
if lead.duration is not None:
duration_minute = int(ceil(float(lead.duration)/60))
if duration_minute != 1:
minutes = 'minutes'
else:
minutes = 'minute'
if form.validate_on_submit():
form.populate_obj(lead)
# Update status across contact and lead
from buyercall.blueprints.contacts.models import Contact
Contact.update_status(lead.contact_id, None, form.data['progress_status'], partnership_account_id, True)
lead.save()
if form_note.validate_on_submit():
form_note.populate_obj(lead)
lead_form_note = lead.notes
lead.notes = ""
lead.save()
notequery = ''
if form.edited_notes_str.data is not None:
notequery = str(unescape(form.edited_notes_str.data))
calls = Lead\
.query\
.filter(
lead.phonenumber == Lead.phonenumber,
lead.partnership_account_id == Lead.partnership_account_id)\
.all()
for each_call in calls:
each_call.progress_status = lead.progress_status
db.session.commit()
if len(str(lead_form_note)) > 0:
note = {
'text': lead_form_note,
'lead_id': lead.id,
'created_on': datetime.now(),
'updated_on': datetime.now(),
'user_id': int(current_user.id),
'is_enabled': True
}
result = LeadNotes.create(note)
else:
result = True
if len(notequery) > 0:
noteobject = json.loads(notequery)
for lpnote in noteobject:
lpresult = LeadNotes.update(
int(lpnote["id"]),
str(lpnote["newtext"]),
datetime.now(),
int(current_user.id),
getboolean(str(lpnote["isenabled"]))
)
if result:
flash(_('The lead has been updated successfully.'), 'success')
return redirect(url_for('leads.lead_edit', id=lead.id))
# Return all calls for this number
previous_calls = Lead.query.filter(
Lead.partnership_account_id == partnership_account_id,
Lead.phonenumber == lead.phonenumber
).options(
load_only("firstname", "lastname", "starttime", "call_type", "status")
).order_by(text('starttime desc')).limit(10).all()
# Return the number of calls for this number
previous_calls_count = Lead.query.filter(
Lead.partnership_account_id == partnership_account_id,
Lead.phonenumber == lead.phonenumber
).count()
# Return the lead notes of all calls for this number
all_lead_notes = LeadNotes.query.join(Lead).filter(
lead.phonenumber == Lead.phonenumber,
LeadNotes.is_enabled
).order_by(desc(LeadNotes.created_on)).all()
return render_template('leads/edit.jinja2', lead=lead,
duration_minute=duration_minute,
form=form,
response_time_min=response_time_min,
response_time_sec=response_time_sec,
agent_name=agent_name,
minutes=minutes,
lead_source=lead.source,
question=lead.question,
my_phone=lead.my_phone,
number_log=previous_calls,
number_log_count=previous_calls_count,
number_notes=all_lead_notes)
# Voice Request URL used for click to call modal on home page
@csrf.exempt
@leads.route('/call', methods=['POST'])
def call():
# Get phone number we need to call from the phone field
phone_number = request.form.get('phoneNumber', None)
# Set the account_id to 10. This will be set to the
# user_id for lead
account_id = 113
# Get the partnership id to find correct twilio credentials
from buyercall.blueprints.partnership.models import Partnership, PartnershipAccount
partner_account = PartnershipAccount.query \
.filter(PartnershipAccount.id == account_id).first()
partner = Partnership.query.filter(Partnership.id == partner_account.partnership_id).first()
try:
twilio_client = account_client(partner.id)
except Exception as e:
msg = 'Missing configuration variable: {0}'.format(e)
return jsonify({'error': msg})
try:
call = twilio_client.calls.create(
from_=current_app.config['TWILIO_CALLER_ID'],
to=phone_number,
url=url_for('leads.outbound',
_external=True, _scheme='https'))
except Exception as e:
current_app.logger.error(e)
return jsonify({'error': str(e)})
existing_lead = Lead.query.filter(and_(Lead.phonenumber == format_phone_number(phone_number),
Lead.partnership_account_id == current_user.partnership_account_id
)).order_by(Lead.created_on.desc()).first()
if existing_lead is not None:
progress_status = existing_lead.progress_status
else:
progress_status = 'no status'
# Assign the lead to the lead variable with available call
# info and user input fields
lead = Lead(partnership_account_id=account_id,
status=call.status,
firstname=request.form.get('firstName', None),
lastname=request.form.get('lastName', None),
email=request.form.get('email', None),
phonenumber=phone_number,
duration=call.duration,
starttime=call.start_time,
endtime=call.end_time,
call_sid=call.sid,
progress_status=progress_status)
# Save the lead to the database
db.session.add(lead)
db.session.commit()
# Retrieve the current lead it data
lead_id = Lead.query.get(lead.id)
# Use update_lead function to update some call info, like
# status, duration, start time etc.
Lead.update_lead_twilio(lead_id)
return jsonify({'message': 'Call incoming!'})
@csrf.exempt
@leads.route('/outbound', methods=['POST'])
def outbound():
response = VoiceResponse()
response.say("Thank you for contacting our sales department. If this "
"click to call application was in production, we would "
"dial out to your sales team with the Dial verb.",
voice='alice')
'''
# Uncomment this code and replace the number with the number you want
# your customers to call.
with response.dial() as dial:
dial.number("+16518675309")
'''
return str(response)
# Frontend method to retrieve the presigned audio url
@leads.route('/presigned_audio_url')
@csrf.exempt
@login_required
def get_presigned_audio_url():
try:
url = str(request.args.get('url'))
if url:
from buyercall.lib.util_boto3_s3 import get_recording_url_details
details = get_recording_url_details(url)
if details and details['key'] and details['bucket']:
from buyercall.lib.util_boto3_s3 import generate_presigned_aws_url
result = generate_presigned_aws_url(details['key'], details['bucket'], True)
if result:
return result
else:
return ''
except Exception as e:
log.error("Error retrieving presigned url. Error: {}".format(e))
return ''
log.error("Unable to retrieve presigned url.")
return ''
# return data into jquery datatables
@leads.route('/data')
@csrf.exempt
@login_required
def data():
"""Return server side data."""
search = request.args.get('search[value]', '')
order = int(request.args.get('order[0][column]', '-1'))
direction = request.args.get('order[0][dir]', 'asc')
offset = int(request.args.get('start', 0))
limit = int(request.args.get('length', 99))
agent_id = current_user.agent.id if current_user.agent else None
date_from = str(request.args.get('df'))
date_to = str(request.args.get('dt'))
audio = str(request.args.get('ad'))
answered_agent = str(request.args.get('ab'))
assigned_agent = str(request.args.get('aa'))
progress_status = str(request.args.get('ps'))
call_source = str(request.args.get('so'))
call_status = str(request.args.get('st'))
type_direction = str(request.args.get('tp'))
phone_number = str(request.args.get('pn'))
date_difference = days_between(date_from, date_to)
# filter initiation section
filter_by_type = text("")
filter_by_audio = text("")
filter_by_assigned_agent = text("")
filter_by_answered_agent = text("")
filter_by_progress_status = text("")
filter_by_call_status = text("")
filter_by_call_source = text("")
filter_by_type_direction = text("")
partnership_account_id = current_user.partnership_account_id
viewing_partnership_account = current_user.is_viewing_partnership
# Check if being viewed by super partner
if viewing_partnership_account:
partnership_account_id = current_user.get_user_viewing_partnership_account_id
converted_dateFrom = datetime.strptime(date_from, "%m%d%Y").date()
converted_dateTo = datetime.strptime(date_to, "%m%d%Y").date()
filter_by_date = and_(
func.date(Lead.created_on) >= converted_dateFrom,
func.date(Lead.created_on) <= converted_dateTo
)
if current_user.role == 'agent':
filter_by = (agent_id == Lead.agent_id) | (agent_id == Lead.agent_assigned_id) | \
(Lead.partnership_account_id == partnership_account_id)
else:
filter_by = and_(
Lead.partnership_account_id == partnership_account_id, current_user.role == 'admin')
from buyercall.blueprints.contacts.models import Status
columns_search = [
Lead.id,
Lead.firstname,
Lead.lastname,
Lead.caller_id,
Lead.phonenumber,
Lead.my_phone,
Lead.status,
Lead.call_type,
Status.status,
Lead.source
]
columns_window = [
Lead.id,
Lead.firstname,
Lead.caller_id,
Lead.phonenumber,
Lead.my_phone,
Lead.created_on,
Lead.status,
Lead.call_type,
Status.status,
Lead.recording_url,
Agent.full_name,
Lead.agent_id,
Lead.source,
Agent.id,
Lead.contact_id,
Lead.lastname
]
if phone_number and phone_number != 'null' and phone_number != 'None':
if phone_number.count('-') == 4:
filter_by_type = text("widgets.guid = '{}'".format(phone_number))
else:
filter_by_type = text("phonenumbers.id = {}".format(phone_number))
# Audio filter section
if audio != 'null' and audio:
if audio.lower() == 'true':
filter_by_audio = and_(text("leads.recording_url != ''"))
elif audio.lower() == 'false':
filter_by_audio = and_(text("leads.recording_url = ''"))
# Answered agent filter section
if answered_agent != 'null' and answered_agent:
filter_by_answered_agent = and_(text("leads.agent_id = {}".format(answered_agent)))
# Assigned agent filter section
if assigned_agent != 'null' and assigned_agent:
filter_by_assigned_agent = and_(text("leads.agent_assigned_id = {}".format(assigned_agent)))
# Progress status filter section
if progress_status != 'null' and progress_status:
filter_by_progress_status = and_(text("leads.progress_status = '{}'".format(
progress_status
)))
# Class status filter section
if call_status != 'null' and call_status:
filter_by_call_status = and_(text("leads.status = '{}'".format(get_call_status_key(call_status))))
# Class source filter section
if call_source != 'null' and call_source:
if call_source.count('-') == 4:
filter_by_call_source = and_(text("leads.widget_guid = '{}'".format(call_source)))
else:
filter_by_call_source = and_(text("leads.inbound_id = '{}'".format(call_source)))
# Type direction filter section
if type_direction != 'null' and type_direction:
call_type_direction = "inbound"
if type_direction != 'in':
call_type_direction = "outbound"
filter_by_type_direction = and_(text("leads.call_type = '{}'".format(call_type_direction)))
# define data columns for the counts
columns_total = [
Lead.id
]
total = Lead.query\
.outerjoin(Lead.agent)\
.outerjoin(Lead.widget) \
.join(Status, Lead.progress_status == Status.status) \
.outerjoin(Lead.inbound).options(
contains_eager(Lead.agent).load_only('firstname', 'lastname'),
contains_eager(Lead.widget).load_only('id', 'name'),
contains_eager(Lead.inbound).load_only('id', 'friendly_name'),
).filter(filter_by)\
.filter(filter_by_date)\
.filter(filter_by_type)\
.filter(filter_by_audio)\
.filter(filter_by_answered_agent)\
.filter(filter_by_assigned_agent)\
.filter(filter_by_progress_status)\
.filter(filter_by_call_status)\
.filter(filter_by_call_source)\
.filter(filter_by_type_direction)
filtered = total
if search:
total_list = total.with_entities(*columns_search).all()
searched_result_list = [x for x in total_list
if x.firstname.lower() + ' ' + x.lastname.lower() == search.lower()
or x.firstname.lower() == search.lower()
or x.lastname.lower() == search.lower()
or x.caller_id.lower() == search.lower()
or x.phonenumber.replace("+1", "").replace("(", "").replace(")", "").replace("-", "")
.replace(" ", "") == search.replace("+1", "").replace("(", "").replace(")", "")
.replace("-", "").replace(" ", "")
or x.my_phone.replace("+1", "").replace("(", "").replace(")", "").replace("-", "")
.replace(" ", "") == search.replace("+1", "").replace("(", "").replace(")", "")
.replace("-", "").replace(" ", "")
or x.call_type.lower() == search.lower()
or x.status.lower() == search.lower()
or x.source.lower() == search.lower()]
searched_result_ids = [x.id for x in searched_result_list]
filtered = Lead.query.outerjoin(Lead.agent).outerjoin(Lead.widget)\
.join(Status, Lead.progress_status == Status.status)\
.outerjoin(Lead.inbound).options(
contains_eager(Lead.agent).load_only('firstname', 'lastname'),
contains_eager(Lead.widget).load_only('id', 'name'),
contains_eager(Lead.inbound).load_only('id', 'friendly_name'))\
.filter(Lead.id.in_(searched_result_ids))
sorted_ = filtered.with_entities(*columns_window)
if order in range(len(columns_window)):
order_pred = columns_window[order]
if direction == 'desc':
order_pred = desc(order_pred)
sorted_ = sorted_.order_by(order_pred)
sorted_ = sorted_.offset(offset).limit(limit)
data = [
{i: row[i] for i in range(len(row))
} for row in sorted_.all()
]
# set specific columns for the total count
filtered_total = filtered.with_entities(*columns_total)
lead_id_list = list()
for lead in filtered_total.all():
lead_id_list.append(lead[0])
total_call_leads = len(lead_id_list)
from buyercall.lib.supervisor_manager import current_supervisor_user
for lp_data in data:
if lp_data[9] is not None:
try:
if current_supervisor_user.role in ('limitsysadmin', 'partner'):
lp_data[9] = 'Restricted'
except AttributeError:
pass
# Get selectors
parent_type_filter = extract('day', func.date(Lead.created_on))
child_type_filter = extract('month', func.date(Lead.created_on))
year_selector = False
order_by_year = 'parent'
# Check filter type days/month/year
if date_difference <= 31:
parent_type_filter = extract('day', func.date(Lead.created_on))
child_type_filter = extract('month', func.date(Lead.created_on))
elif 32 <= date_difference <= 366:
parent_type_filter = extract('month', func.date(Lead.created_on))
child_type_filter = extract('year', func.date(Lead.created_on))
if converted_dateFrom.year < converted_dateTo.year:
order_by_year = 'child'
elif date_difference > 366:
parent_type_filter = extract('year', func.date(Lead.created_on))
child_type_filter = extract('year', func.date(Lead.created_on))
year_selector = True
# Total calls data and values for Line Graph for the given date/time range
labels = db.session.query(
parent_type_filter.label('parent'),
child_type_filter.label('child'),
func.count(Lead.created_on).label('total'))\
.filter(filter_by_date)\
.filter(filter_by) \
.filter(Lead.id.in_(lead_id_list))\
.group_by('parent', 'child')\
.order_by(order_by_year)
filter_data_points = list()
filter_labels = list()
filter_date = list()
for label in labels:
split_filter_date = str(label.parent).split('.')
split_filter_child = str(label.child).split('.')
if label.parent not in filter_date:
filter_date.append(label.parent)
if year_selector:
filter_labels.append(split_filter_date[0])
else:
filter_labels.append('{}/{}'.format(split_filter_date[0], split_filter_child[0]))
filter_data_points.append(label.total)
# Monthly total answered call data and values for Line Graph
complete_totals = labels.filter(filter_by, Lead.status == 'completed')
complete_date = list()
complete_value = []
for row in complete_totals:
complete_date.append(row.parent)
complete_value.append(row.total)
answered_data_points = []
complete_point = 0
for day in filter_date:
if day in complete_date:
if complete_point < len(complete_value):
answered_data_points.append(complete_value[complete_point])
complete_point = complete_point + 1
else:
answered_data_points.append(0)
# Total answered call data and values for Line Graph
missed_totals = labels.filter(filter_by, Lead.status == 'missed')
missed_date = []
missed_value = []
for row in missed_totals:
missed_date.append(row.parent)
missed_value.append(row.total)
missed_data_points = []
missed_point = 0
for day in filter_date:
if day in missed_date:
missed_data_points.append(missed_value[missed_point])
missed_point = missed_point + 1
else:
missed_data_points.append(0)
missed = []
for i in missed_data_points:
missed.append(i)
answered = []
for i in answered_data_points:
answered.append(i)
totals = []
for i in filter_data_points:
totals.append(i)
points = []
for i in filter_labels:
points.append(str(i))
# Side bar data
# total call count
total_calls = filtered.count()
# Unique call count
total_unique_calls = filtered.filter(or_(filter_by)).distinct(Lead.phonenumber).count()
# Answered inbound call count
total_answered_calls = filtered.filter(or_(Lead.status == 'completed'), filter_by).count()
# Answered inbound percentage missed call count
total_percentage_answered = int(float(total_answered_calls) * 100 / float(total_calls)) if total_calls else 0
# Missed inbound call count
total_missed_calls = filtered.filter(or_(Lead.status == 'missed'), filter_by).count()
# Missed inbound percentage missed call count
total_percentage_missed = int(float(total_missed_calls) * 100 / float(total_calls)) if total_calls else 0
# Establish total call duration in minutes
total_call_duration = filtered.with_entities(Lead.duration).filter(or_(filter_by)).all()
# Check for None values in the duration column and set them to 0
if total_call_duration is None:
total_call_duration_min = 0
else:
new_call_duration = [i.duration for i in total_call_duration]
call_duration_list = []
for duration in new_call_duration:
if not duration:
duration_zero = float(0)
call_duration_list.append(duration_zero)
else:
float_duration = math.ceil(float(duration) / 60.0)
call_duration_list.append(float_duration)
total_call_duration_min = int(sum(call_duration_list))
# total_call_duration_min = int(total_call_duration / 60)
# Establish average call duration
total_avg_call_duration = filtered.with_entities(func.avg(Lead.duration).label('average')).scalar()
# Check for None values in the duration column and set them to 0
if total_avg_call_duration is None:
total_avg_call_duration = 0
else:
pass
total_avg_call_duration_min = int(total_avg_call_duration / 60)
total_avg_call_duration_sec = int(total_avg_call_duration % 60)
return jsonify(
draw=request.args['draw'],
recordsFiltered=total_call_leads,
recordsTotal=total_call_leads,
data=data,
graphMissed=missed,
graphAnswered=answered,
graphTotal=totals,
graphPoints=points,
calls=total_calls,
unique_calls=total_unique_calls,
answered_calls=total_answered_calls,
percentage_answered=total_percentage_answered,
missed_calls=total_missed_calls,
percentage_missed=total_percentage_missed,
call_duration_min=total_call_duration_min,
avg_call_duration_min=total_avg_call_duration_min,
avg_call_duration_sec=total_avg_call_duration_sec
)
# return data for the filter options
@leads.route('/leads/filteroptions', methods=['GET'])
@csrf.exempt
@login_required
def filteroptions():
agent_id = current_user.agent.id if current_user.agent else None
phonenumber_result = {}
answered_agent_result = {}
assigned_agent_result = {}
status_result = {}
progress_result = {}
source_result = {}
partnership_account_id = current_user.partnership_account_id
viewing_partnership_account = current_user.is_viewing_partnership
# Check if being viewed by super partner
if viewing_partnership_account:
partnership_account_id = current_user.get_user_viewing_partnership_account_id
filter_by = (agent_id == Lead.agent_id) | and_(
Lead.partnership_account_id == partnership_account_id, current_user.role == 'admin')
# retrieve the inbound friendly names from phone numbers
inbound_data = Lead.query.outerjoin(Lead.inbound).options(
contains_eager(Lead.inbound)
).with_entities('phonenumbers.id', 'phonenumbers.friendly_name', 'phonenumbers.phonenumber',
'phonenumbers.is_active', 'phonenumbers.is_deactivated', 'phonenumbers.partnership_account_id')\
.filter(filter_by)\
.distinct()
# retrieve the outbound friendly names from widgets
outbound_data = Lead.query.outerjoin(Lead.widget).options(
contains_eager(Lead.widget)
).with_entities('widgets.guid', 'widgets.name', 'widgets.enabled', 'widgets.partnership_account_id')\
.filter(filter_by)\
.distinct()
# retrieve the agents linked to the account
answered_agent_data_list = Agent.query.join(Lead, Lead.agent_id == Agent.id)\
.filter(filter_by)\
.distinct()\
.order_by(Agent.lastname)
# retrieve the agents linked to leads for an account
assigned_agent_data_list = Agent.query.join(Lead, Lead.agent_assigned_id == Agent.id)\
.filter(Lead.partnership_account_id == partnership_account_id)\
.distinct().order_by(Agent.lastname)
source_data_list_phones = inbound_data.filter(
and_(text('phonenumbers.is_active'), text('phonenumbers.is_deactivated = false'),
text('phonenumbers.partnership_account_id = {}'.format(partnership_account_id)))
).all()
source_data_list_widgets = outbound_data.filter(
and_(text('widgets.enabled'), text('widgets.partnership_account_id = {}'.format(partnership_account_id)))
).all()
# retrieve progress
from buyercall.blueprints.contacts.models import Status
progress_status_list = Status.get_assigned_status_list(partnership_account_id)
# retrieve statuses
status_list = Lead.STATUSES
# populate result with the phonenumbers
for i in inbound_data:
if i:
if i[0] and i[2]:
id = str(i[0])
name = str(i[2])
friendly_name = str(i[1])
phonenumber_result[id] = '{} ({})'.format(name, friendly_name)
# populate result with outbound friendly names
for i in outbound_data:
if i:
if i[0] and i[1]:
id = str(i[0])
name = str(i[1])
phonenumber_result[id] = name
# populate result with agents
for i in answered_agent_data_list:
if i:
id = str(i.id)
name = i.full_name
answered_agent_result[id] = name
# populate result with assigned agents
for i in assigned_agent_data_list:
if i:
id = str(i.id)
name = i.full_name
assigned_agent_result[id] = name
# populate progress result
for item in progress_status_list:
id = item.status
progress_result[id] = item.display_name
# populate status result
for key in status_list:
if key:
if status_list[key]:
id = str(key).replace(' ', '_')
name = str(status_list[key])
status_result[id] = name
# populate source result
for i in source_data_list_phones:
if i and str(i[0]):
source_result[str(i[0])] = str(i[1])
for i in source_data_list_widgets:
if i and str(i[0]):
source_result[str(i[0])] = str(i[1])
return jsonify(
phonenumber_data=phonenumber_result,
answered_agent_data=answered_agent_result,
assigned_agent_data=assigned_agent_result,
progress_data=progress_result,
status_data=status_result,
source_data=source_result
)
@leads.route('/leads/csv', methods=['GET'])
@login_required
def data_csv():
phone_number = str(request.args.get('pn', ''))
date_from = str(request.args.get('df'))
date_to = str(request.args.get('dt'))
audio = str(request.args.get('ad'))
answered_agent = str(request.args.get('ab'))
assigned_agent = str(request.args.get('aa'))
progress_status = str(request.args.get('ps'))
call_source = str(request.args.get('so'))
call_status = str(request.args.get('st'))
type_direction = str(request.args.get('tp'))
# filter initiation section - defaulting to 1=1
filter_by_type = text("")
filter_by_audio = text("")
filter_by_assigned_agent = text("")
filter_by_answered_agent = text("")
filter_by_progress_status = text("")
filter_by_call_status = text("")
filter_by_call_source = text("")
filter_by_type_direction = text("")
partnership_account_id = current_user.partnership_account_id
viewing_partnership_account = current_user.is_viewing_partnership
# Check if being viewed by super partner
if viewing_partnership_account:
partnership_account_id = current_user.get_user_viewing_partnership_account_id
converted_dateFrom = datetime.strptime(date_from, "%m%d%Y").date()
converted_dateTo = datetime.strptime(date_to, "%m%d%Y").date()
agent_id = current_user.agent.id if current_user.agent else None
search = request.args.get('search[value]', '')
header = [
'No', 'First Name', 'Last Name', 'Caller Id', 'Email', 'Phone Number', 'My Phone',
'Question', 'Duration (secs)', 'Start Time', 'End Time',
'Created On', 'Call Type', 'Call Status', 'Progress Status', 'Recording URL', 'Notes','Assigned To', 'Answered By',
'Friendly Name'
]
current_app.logger.debug('The agent id is {}'.format(agent_id))
if current_user.role == 'agent':
filter_by = (agent_id == Lead.agent_id) | (agent_id == Lead.agent_assigned_id) | \
(Lead.partnership_account_id == partnership_account_id)
else:
filter_by = and_(
Lead.partnership_account_id == partnership_account_id, current_user.role == 'admin')
filter_by_date = and_(
func.date(Lead.created_on) >= converted_dateFrom,
func.date(Lead.created_on) <= converted_dateTo
)
if phone_number and phone_number != 'null' and phone_number != 'None':
if phone_number.count('-') == 4:
filter_by_type = text("widgets.guid = '{}'".format(phone_number))
else:
filter_by_type = text("phonenumbers.id = {}".format(phone_number))
# Audio filter section
if audio != 'null' and audio:
if audio.lower() == 'true':
filter_by_audio = and_(text("leads.recording_url != ''"))
elif audio.lower() == 'false':
filter_by_audio = and_(text("leads.recording_url = ''"))
# Answered agent filter section
if answered_agent != 'null' and answered_agent:
filter_by_answered_agent = and_(text("leads.agent_id = {}".format(answered_agent)))
# Assigned agent filter section
if assigned_agent != 'null' and assigned_agent:
filter_by_assigned_agent = and_(text("leads.agent_assigned_id = {}".format(assigned_agent)))
# Progress status filter section
if progress_status != 'null' and progress_status:
filter_by_progress_status = and_(text("leads.progress_status = '{}'".format(
progress_status
)))
# Call status filter section
if call_status != 'null' and call_status:
filter_by_call_status = and_(text("leads.status = '{}'".format(get_call_status_key(call_status))))
# Call source filter section
if call_source != 'null' and call_source:
if call_source.count('-') == 4:
filter_by_call_source = and_(text("leads.widget_guid = '{}'".format(call_source)))
else:
filter_by_call_source = and_(text("leads.inbound_id = '{}'".format(call_source)))
# Type direction filter section
if type_direction != 'null' and type_direction:
call_type_direction = "inbound"
if type_direction != 'in':
call_type_direction = "outbound"
filter_by_type_direction = and_(text("leads.call_type = '{}'".format(call_type_direction)))
from buyercall.blueprints.contacts.models import Status
total = Lead.query\
.outerjoin(Lead.agent)\
.outerjoin(Lead.widget)\
.outerjoin(Lead.inbound) \
.join(Status, Lead.progress_status == Status.status) \
.options(
contains_eager(Lead.agent).load_only('firstname', 'lastname'),
contains_eager(Lead.widget).load_only('id', 'name'),
contains_eager(Lead.inbound).load_only('id', 'friendly_name'),
).filter(filter_by)\
.filter(filter_by_date)\
.filter(filter_by_type)\
.filter(filter_by_audio)\
.filter(filter_by_assigned_agent)\
.filter(filter_by_answered_agent)\
.filter(filter_by_progress_status)\
.filter(filter_by_call_source)\
.filter(filter_by_call_status)\
.filter(filter_by_type_direction)
filtered = total
columns_search = [
Lead.id,
Lead.firstname,
Lead.lastname,
Lead.caller_id,
Lead.phonenumber,
Lead.my_phone,
Lead.status,
Lead.call_type,
Status.status,
Lead.source
]
if search:
total_list = total.with_entities(*columns_search).all()
searched_result_list = [x for x in total_list
if x.firstname.lower() + ' ' + x.lastname.lower() == search.lower()
or x.firstname.lower() == search.lower()
or x.lastname.lower() == search.lower()
or x.caller_id.lower() == search.lower()
or x.phonenumber.replace("+1", "").replace("(", "").replace(")", "").replace("-", "")
.replace(" ", "") == search.replace("+1", "").replace("(", "").replace(")", "")
.replace("-", "").replace(" ", "")
or x.my_phone.replace("+1", "").replace("(", "").replace(")", "").replace("-", "")
.replace(" ", "") == search.replace("+1", "").replace("(", "").replace(")", "")
.replace("-", "").replace(" ", "")
or x.call_type.lower() == search.lower()
or x.status.lower() == search.lower()
or x.source.lower() == search.lower()]
searched_result_ids = [x.id for x in searched_result_list]
filtered = Lead.query.outerjoin(Lead.agent).outerjoin(Lead.widget) \
.join(Status, Lead.progress_status == Status.status) \
.outerjoin(Lead.inbound).options(
contains_eager(Lead.agent).load_only('firstname', 'lastname'),
contains_eager(Lead.widget).load_only('id', 'name'),
contains_eager(Lead.inbound).load_only('id', 'friendly_name')) \
.filter(Lead.id.in_(searched_result_ids))
query = filtered.order_by(Lead.id)
# Get status list
progress_status_list = Status.get_complete_status_list_as_dict()
# Build the CSV
row_no = 0
with closing(StringIO()) as out:
writer = csv.writer(out)
writer.writerow(header)
for lead in query.all():
row_no += 1
csv_row = [
row_no,
lead.firstname,
lead.lastname,
lead.caller_id,
lead.email,
lead.phonenumber,
lead.my_phone,
lead.question,
lead.duration,
format_date(lead.starttime),
format_date(lead.endtime),
lead.created_on,
lead.call_type,
lead.status,
Status.get_formatted_status(progress_status_list, lead.progress_status),
lead.recording_url,
lead.notes,
lead.assigned_full_name if lead.agent_assigned_id else '',
lead.agent.full_name if lead.agent else '',
(
lead.widget and lead.widget.name
) or (lead.inbound and lead.inbound.friendly_name) or ''
]
writer.writerow([x for x in csv_row])
filename = 'Buyercall Leads - {}.csv'.format(
date.today().strftime('%Y-%m-%d')
)
resp = make_response(out.getvalue())
resp.headers['Content-Type'] = 'text/csv'
resp.headers['Content-Disposition'] = \
'attachment; filename="{}"'.format(filename)
return resp
@leads.route('/api/leads/call/<int:lead_id>', methods=['GET'])
@login_required
@csrf.exempt
def get_lead_call_info(lead_id):
""" Return this lead's call information in JSON format, with the following
fields:
- agentNumber: The phone number of the agent used to call the lead.
- routingNumber: The routing number used to call the lead.
- allAgentNumbers: An array of all agent numbers for this user.
- allRoutingNumbers: An array of all routing numbers for this user.
The numbers should be returned in E.123 format.
"""
from buyercall.blueprints.phonenumbers.models import Phone
partnership_account_id = current_user.partnership_account_id
viewing_partnership_account = current_user.is_viewing_partnership
# Check if being viewed by super partner
if viewing_partnership_account:
partnership_account_id = current_user.get_user_viewing_partnership_account_id
lead = Lead.query.filter(
Lead.partnership_account_id == partnership_account_id, Lead.id == lead_id
).first()
if not lead:
return ''
agent_nos = []
if current_user.role == 'agent':
agents = Agent.query.filter(
Agent.user_id == current_user.id
).all()
else:
agents = Agent.query.filter((Agent.partnership_account_id == current_user.partnership_account_id),
Agent.is_deactivated.is_(False)).all()
for agent in agents:
if agent.phonenumber:
agent_nos.append({
"agent": "{}, Phone ({})".format(
agent.full_name, agent.phonenumber),
"number": agent.phonenumber
})
if agent.mobile and (
agent.mobile != agent.phonenumber or not agent.phonenumber
):
agent_nos.append({
"agent": "{}, Mobile ({})".format(
agent.full_name, agent.mobile),
"number": agent.mobile
})
if agent.app_number and (
agent.app_number != agent.mobile or agent.app_number != agent.phonenumber or not agent.phonenumber
):
agent_nos.append({
"agent": "{}, App ({})".format(
agent.full_name, agent.app_number),
"number": agent.app_number
})
routings = Phone.query.filter(and_(
Phone.partnership_account_id == partnership_account_id,
Phone.active == True,
Phone.is_deactivated == False
)).all()
routing_nos = [
{
"friendlyName": '{} ({})'.format(x.friendly_name, x.phonenumber),
"number": x.phonenumber
} for x in routings
]
return jsonify({
"agentNumber": lead.agent and lead.agent.phonenumber,
"allAgentNumbers": agent_nos,
"routingNumber": lead.my_phone,
"allRoutingNumbers": routing_nos
})
@leads.route('/api/leads/call/<int:lead_id>', methods=['POST'])
@csrf.exempt
def call_lead_again(lead_id):
from buyercall.blueprints.phonenumbers.models import Phone
from buyercall.blueprints.agents.models import Agent
from buyercall.blueprints.phonenumbers.tasks import connect_lead_to_agent
partnership_account_id = current_user.partnership_account_id
viewing_partnership_account = current_user.is_viewing_partnership
# Check if being viewed by super partner
if viewing_partnership_account:
partnership_account_id = current_user.get_user_viewing_partnership_account_id
data = request.get_json()
agent_number, routing_number = (
data.get('agentNumber'), data.get('routingNumber')
)
from buyercall.blueprints.sysadmin.utilities.request_log_task_call import LogRequestTaskTrigger
LogRequestTaskTrigger().log_request_task_trigger(
request, "phone_call")
routing = Phone.query.filter(
Phone.partnership_account_id == partnership_account_id,
Phone.phonenumber == routing_number
).options(load_only('id')).first()
if not routing:
return 'Cannot find routing phone number', 400
agent = Agent.query.filter(
Agent.partnership_account_id == partnership_account_id,
or_(Agent.phonenumber == agent_number,
Agent.mobile == agent_number,
Agent.app_number == agent_number), # TODO: Include extension!
).filter(Agent.is_deactivated == '0').options(load_only('id')).first()
if not agent:
return 'Cannot find agent', 400
old_lead = Lead.query.filter(
Lead.partnership_account_id == partnership_account_id, Lead.id == lead_id
).first()
if not old_lead:
return 'Lead with ID {} not found'.format(lead_id), 400
from buyercall.blueprints.contacts.models import Contact
contact = Contact.query.filter(Contact.partnership_account_id == partnership_account_id,
Contact.id == old_lead.contact_id).first()
lead = Lead(
partnership_account_id=partnership_account_id,
firstname=contact.firstname,
lastname=contact.lastname,
phonenumber=contact.phonenumber_1,
email=contact.email,
starttime=datetime.utcnow(),
call_type='outbound',
my_phone=routing_number,
inbound_id=routing.id,
agent_id=agent.id,
progress_status=contact.status,
status='ringing',
contact_id=contact.id,
originating_number=routing_number,
call_source='Agent Outbound Call',
)
lead.save()
# A hack to specify how to contact the agent
if agent.mobile == agent_number:
contact_using = 'mobile'
elif agent.app_number == agent_number:
contact_using = 'app'
else:
contact_using = 'phone'
call_settings = {
'agents': [{'id': agent.id, 'contactUsing': contact_using}]
}
if routing.type == 'tracking':
from buyercall.blueprints.widgets.bw_outbound import agent_manual_outbound_call
agent_manual_outbound_call(lead, call_settings)
else:
connect_lead_to_agent.delay(lead.id, agent.id, call_settings)
return ''
def format_date(date_obj):
""" Format a local date for consumption by Excel
"""
if not date_obj:
return ''
return date_obj.strftime('%Y-%m-%d %H:%M:%S')