File: //home/arjun/projects/buyercall_new/buyercall/buyercall/blueprints/partnership/models.py
import uuid
import math
import logging
import traceback
from flask import jsonify
from datetime import datetime
from buyercall.blueprints.sms.models import Message
from buyercall.blueprints.user.models import User
from buyercall.blueprints.contacts.models import Contact, CreditReports
from buyercall.blueprints.widgets.models import Widget
from buyercall.blueprints.agents.models import Agent
from buyercall.blueprints.issue.models import Issue
from buyercall.blueprints.activity.models import ActivityLogs
from buyercall.lib.util_sqlalchemy import ResourceMixin
from sqlalchemy.sql.expression import update
from buyercall.blueprints.billing.models.invoice import Invoice
from buyercall.blueprints.leads.models import Lead
from buyercall.extensions import db
from sqlalchemy import or_, and_, extract
from collections import OrderedDict
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import text
from buyercall.blueprints.filters import format_phone_number
log = logging.getLogger(__name__)
BILLING_TYPE = OrderedDict([
('partnership', 'Bill Partnership'),
('account', 'Bill Account'),
('invoice', 'By Invoice')
])
BUSINESS_TYPE = OrderedDict([
('automotive', 'Automotive'),
('general', 'General'),
('healthcare', 'Healthcare')
])
class ApiToken(ResourceMixin, db.Model):
__tablename__ = 'api_tokens'
id = db.Column(db.Integer, primary_key=True)
active = db.Column('is_active', db.Boolean(), nullable=False, server_default='0')
api_token_hash = db.Column(db.String(64), nullable=False)
@classmethod
def create(cls):
"""
Create a new api token
:return: int / id
"""
from buyercall.lib.util_crypto import SHA
token = str(uuid.uuid4())
new_api_token = ApiToken(
active=False,
api_token_hash=SHA.encrypt(token)
)
db.session.add(new_api_token)
db.session.flush()
result_api_id = new_api_token.id
db.session.commit()
return result_api_id
@classmethod
def check_token(cls, token):
"""
Check to see if REST API token is valid, return the id of the token
"""
found_token_id = -1
from buyercall.lib.util_crypto import SHA
token_hash = SHA.encrypt(token)
found_token_id = db.session.query(ApiToken.id)\
.filter(and_(ApiToken.active == True, ApiToken.api_token_hash == token_hash))\
.first()
if found_token_id:
if len(found_token_id) >= 1:
found_token_id = found_token_id[0]
if found_token_id >= 1:
return found_token_id
return None
@classmethod
def regenerate_token(cls, id):
"""
Regenerate the token for a specific token entry. The new token is returned.
"""
token_hash = db.session.query(ApiToken)\
.filter(ApiToken.id == id)\
.first()
if token_hash is not None:
new_token = str(uuid.uuid4())
from buyercall.lib.util_crypto import SHA
api_token_hash = SHA.encrypt(new_token)
token_hash.api_token_hash = api_token_hash
token_hash.save()
db.session.commit()
return new_token
else:
return None
class ExternalApiServiceProviders(ResourceMixin, db.Model):
__tablename__ = 'external_api_service_providers'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), index=True, nullable=False,
server_default='')
@classmethod
def get_provider_name(cls, pa_external_api_service_provider_id):
"""
Returns the API service provider name. If one does not exist, an empty string is returned.
:return: external api service provider name
"""
result = ''
service_provider = db.session.query(ExternalApiServiceProviders)\
.filter(ExternalApiServiceProviders.id == pa_external_api_service_provider_id)\
.first()
if service_provider is not None:
result = service_provider.name
return result
class ExternalApiServiceProvidersPartnershipAccountTie(ResourceMixin, db.Model):
__tablename__ = 'external_api_service_providers_partnership_account_tie'
external_api_service_provider_id = db.Column(
db.Integer,
db.ForeignKey(
'external_api_service_providers.id',
name='partnership_account_external_api_tie_sp_id_fkey'
),
primary_key=True,
index=True
)
partnership_account_id = db.Column(
db.Integer,
db.ForeignKey(
'partnership_accounts.id',
name='partnership_account_external_api_tie_pa_fkey'
),
primary_key=True
)
active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1')
username = db.Column(db.String(128), nullable=False, server_default='')
password = db.Column(db.String(128), nullable=False, server_default='')
url = db.Column(db.String(128), nullable=False, server_default='')
token_url = db.Column(db.String(128), nullable=False, server_default='')
client_id = db.Column(db.String(128), nullable=False, server_default='')
secret = db.Column(db.String(128), nullable=False, server_default='')
source = db.Column(db.String(128), nullable=False, server_default='')
app_source = db.Column(db.String(128), nullable=False, server_default='')
@classmethod
def delete(cls, pa_external_api_service_provider_id, pa_partnership_account_id):
"""
Delete the partnership account's external api service provider details.
:return: bool
"""
result = False
partnership_provider_tie = db.session.query(ExternalApiServiceProvidersPartnershipAccountTie).filter(
and_(ExternalApiServiceProvidersPartnershipAccountTie.partnership_account_id == pa_partnership_account_id,
ExternalApiServiceProvidersPartnershipAccountTie.external_api_service_provider_id ==
pa_external_api_service_provider_id))\
.first()
if partnership_provider_tie is not None:
db.session.delete(partnership_provider_tie)
db.session.commit()
result = True
return result
@classmethod
def update(cls, pa_external_api_service_provider_id, pa_partnership_account_id, pa_client_id,
pa_username, pa_password, pa_url, pa_token_url, pa_source, pa_app_source, pa_secret):
"""
Update the partnership account's external api service provider details.
:return: bool
"""
partnership_provider_tie = db.session.query(ExternalApiServiceProvidersPartnershipAccountTie)\
.filter(
ExternalApiServiceProvidersPartnershipAccountTie.partnership_account_id == pa_partnership_account_id,
ExternalApiServiceProvidersPartnershipAccountTie.external_api_service_provider_id == pa_external_api_service_provider_id
).first()
if partnership_provider_tie is not None:
partnership_provider_tie.external_api_service_provider_id = pa_external_api_service_provider_id
partnership_provider_tie.username = pa_username
partnership_provider_tie.password = pa_password
partnership_provider_tie.url = pa_url
partnership_provider_tie.secret = pa_secret
partnership_provider_tie.client_id = pa_client_id
partnership_provider_tie.token_url = pa_token_url
partnership_provider_tie.source = pa_source
partnership_provider_tie.app_source = pa_app_source
db.session.commit()
return True
elif partnership_provider_tie is None:
partnership_provider_tie = ExternalApiServiceProvidersPartnershipAccountTie(
external_api_service_provider_id=pa_external_api_service_provider_id,
partnership_account_id=pa_partnership_account_id,
client_id=pa_client_id,
token_url=pa_token_url,
username=pa_username,
password=pa_password,
url=pa_url,
app_source=pa_app_source,
source=pa_source,
secret=pa_secret)
db.session.add(partnership_provider_tie)
db.session.flush()
db.session.commit()
return True
else:
return False
@classmethod
def exists(cls, pa_partnership_account_id, pa_service_provider_id):
"""
Check whether or not the partnership accounts is assigned to provided external api service provider.
:return: external api service provider id
"""
partnership_provider_tie = db.session.query(ExternalApiServiceProvidersPartnershipAccountTie)\
.filter(
ExternalApiServiceProvidersPartnershipAccountTie.partnership_account_id == pa_partnership_account_id,
ExternalApiServiceProvidersPartnershipAccountTie.external_api_service_provider_id == pa_service_provider_id
).first()
if partnership_provider_tie is not None:
return partnership_provider_tie.external_api_service_provider_id
return -1
@classmethod
def service_provider_name(cls, pa_partnership_account_id):
"""
Returns the API service provider name. If one does not exist, an empty string is returned.
:return: external api service provider name
"""
result = ''
partnership_provider_tie = db.session.query(ExternalApiServiceProvidersPartnershipAccountTie)\
.filter(
ExternalApiServiceProvidersPartnershipAccountTie.partnership_account_id == pa_partnership_account_id
).first()
if partnership_provider_tie is not None:
result = ExternalApiServiceProviders.get_provider_name(
partnership_provider_tie.external_api_service_provider_id
)
return result
@classmethod
def exists_for_service_provider(cls, partnership_account_id, service_provider_name):
"""
Check whether or not the partnership accounts is assigned to provided external api service provider.
:return: external api service provider object
"""
partnership_provider_tie = db.session\
.query(ExternalApiServiceProvidersPartnershipAccountTie)\
.join(ExternalApiServiceProviders)\
.filter(ExternalApiServiceProvidersPartnershipAccountTie.partnership_account_id == partnership_account_id,
ExternalApiServiceProvidersPartnershipAccountTie.active)\
.filter(ExternalApiServiceProviders.name == service_provider_name)\
.first()
return partnership_provider_tie
class PartnershipAccountGroupTie(ResourceMixin, db.Model):
__tablename__ = 'partnership_account_group_tie'
partnership_account_group_id = db.Column(
db.Integer,
db.ForeignKey(
'partnership_account_groups.id',
name='partnership_account_group_account_tie_id_fkey'
),
primary_key=False, index=True
)
partnership_account_id = db.Column(
db.Integer,
db.ForeignKey(
'partnership_accounts.id',
name='partnership_account_group_id_tie_fkey'
),
primary_key=True
)
@classmethod
def update(cls, pa_partnership_account_group_id, pa_partnership_account_id):
"""
Update the partnership account's group details.
:return: bool
"""
# account_group = db.session.query(PartnershipAccountGroupTie)\
# .filter(PartnershipAccountGroupTie.partnership_account_id == pa_partnership_account_id).first()
#
# if account_group is not None:
# account_group.partnership_account_group_id = pa_partnership_account_group_id
# db.session.commit()
# return True
# elif account_group is None:
# account_group = PartnershipAccountGroupTie(partnership_account_group_id = pa_partnership_account_group_id,
# partnership_account_id = pa_partnership_account_id)
# db.session.add(account_group)
# db.session.flush()
# db.session.commit()
# return True
# else:
return False
@classmethod
def exists(cls, partnership_account_group_id, partnership_account_id):
"""
Check whether or not the two partnership accounts are in the same group.
:return: bool
"""
account_group = db.session.query(PartnershipAccountGroupTie)\
.filter(and_(PartnershipAccountGroupTie.partnership_account_group_id == partnership_account_group_id,
PartnershipAccountGroupTie.partnership_account_id == partnership_account_id)).first()
if account_group is not None:
return True
else:
return False
class PartnershipAccountGroup(ResourceMixin, db.Model):
__tablename__ = 'partnership_account_groups'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), index=True, nullable=False,
server_default='')
class PartnershipAccountCreditTie(ResourceMixin, db.Model):
__tablename__ = 'partnership_account_credit_tie'
id = db.Column(db.Integer, primary_key=True)
service_provider = db.Column(db.String(128), index=True, nullable=False, server_default='')
active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1')
partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=False)
product_type = db.Column(db.String(128), index=True, nullable=False, server_default='')
api_account = db.Column(db.String(256), index=True, nullable=False, server_default='')
api_username = db.Column(db.String(256), index=True, nullable=False, server_default='')
api_password = db.Column(db.String(256), index=True, nullable=False, server_default='')
experian_enabled = db.Column('experian_enabled', db.Boolean(), nullable=False, server_default='0')
transunion_enabled = db.Column('transunion_enabled', db.Boolean(), nullable=False, server_default='0')
equifax_enabled = db.Column('equifax_enabled', db.Boolean(), nullable=False, server_default='0')
@classmethod
def partner_account_seven_hundred_credit_info(cls, paid, product_type):
"""
Find and return a partnership account's 700 credit credentials.
"""
seven_hundred_credit_as_provider = (db.session.query(cls))\
.filter(cls.partnership_account_id == paid,
cls.service_provider == '700Credit',
cls.product_type == product_type,
cls.active.is_(True)).first()
if seven_hundred_credit_as_provider is not None:
return seven_hundred_credit_as_provider
else:
return None
@classmethod
def partner_account_finserv_credit_info(cls, paid, product_type):
"""
Find and return a partnership account's Finserv credit credentials.
"""
finserv_credit_as_provider = (db.session.query(cls)) \
.filter(cls.partnership_account_id == paid,
cls.service_provider == 'Finserv',
cls.product_type == product_type,
cls.active.is_(True)).first()
if finserv_credit_as_provider is not None:
return finserv_credit_as_provider
else:
return None
class PartnershipAccount(ResourceMixin, db.Model):
__tablename__ = 'partnership_accounts'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), index=True, nullable=False,
server_default='')
partnership_id = db.Column(db.Integer, db.ForeignKey('partnerships.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=True)
subscription_id = db.Column(
db.Integer,
db.ForeignKey(
'subscriptions.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=True
)
active = db.Column('is_active', db.Boolean(), nullable=False, server_default='0')
adf_assigned_email = db.Column(db.String(255), nullable=True)
users = db.relationship(User, backref="partnership_account")
agents = db.relationship(Agent, backref="partnership_account")
issues = db.relationship(Issue, backref="partnership_account")
import_leads = db.Column('import_leads', db.Boolean(), nullable=False, server_default='0')
invoices = db.relationship(Invoice, backref='partnership_account')
leads = db.relationship(Lead, backref='partnership_account', passive_deletes=True)
messages = db.relationship(Message, backref="partnership_account", passive_deletes=True)
contacts = db.relationship(Contact, backref="partnership_account", uselist=False)
widgets = db.relationship(Widget, backref="partnership_account")
activity_logs = db.relationship(ActivityLogs, backref="partnership_account")
credit_provider = db.relationship(PartnershipAccountCreditTie, backref="partnership_account")
credit_reports = db.relationship(CreditReports, backref="partnership_account", uselist=False)
billing_type = db.Column(
db.Enum(*BILLING_TYPE, name='billing_types'),
index=True,
nullable=False,
server_default='partnership'
)
api_token_id = db.Column(
db.Integer,
db.ForeignKey(
'api_tokens.id',
onupdate='CASCADE',
ondelete='CASCADE'
),
index=True,
nullable=True
)
partner_account_code = db.Column(
db.String(64),
index=True,
nullable=False,
server_default=''
)
business_type = db.Column(
db.String(128), nullable=False, server_default='general',
index=True
)
is_2fa_email_enabled = db.Column(db.Boolean(), nullable=False,
server_default='0')
is_2fa_sms_enabled = db.Column(db.Boolean(), nullable=False,
server_default='1')
partnership_account_group = db.relationship(PartnershipAccountGroupTie, backref='partnership_account')
# partnership_account_group_id = db.Column(db.Integerdb.ForeignKey('partnership_account_group_tie.partnerid',
# onupdate='CASCADE'),
# index=True, nullable=True, server_default=None)
# REST API authentication token
# api_token_hash = db.Column(db.String(60), nullable=True)
@classmethod
def create(cls, name, partnership_id, active, billing_type):
"""
Create a partnership account.
:return: bool
"""
new_account = PartnershipAccount(
name=name,
partnership_id=partnership_id,
active=active,
billing_type=billing_type
)
api_token_id = ApiToken.create()
if api_token_id is not None and api_token_id > 0:
new_account.api_token_id = api_token_id
db.session.add(new_account)
db.session.commit()
return True
@classmethod
def create_partial(cls, name, partnership_id, active, partner_account_code, business_type=None):
"""
Create a partnership account.
:return: bool
"""
try:
subscription_id = Partnership.query\
.with_entities(Partnership.subscription_id)\
.filter(Partnership.id == partnership_id).first()
if business_type:
bt = business_type
else:
bt = 'general'
new_account = PartnershipAccount(
name=name,
partnership_id=partnership_id,
partner_account_code=partner_account_code,
subscription_id=subscription_id,
active=active,
business_type=bt,
)
api_token_id = ApiToken.create()
if api_token_id is not None and api_token_id > 0:
new_account.api_token_id = api_token_id
db.session.add(new_account)
db.session.flush()
partnership_account_id = new_account.id
db.session.commit()
return partnership_account_id
except Exception as e:
log.error('Error creating partnership account. Error: {}'.format(e))
return -1
@classmethod
def update(cls, id, name, billing_type):
"""
Update the partnership account's details.
:return: bool
"""
account = PartnershipAccount.query.filter(PartnershipAccount.id == id).first()
if account is not None:
account.name = name
if billing_type is not None and billing_type in ['partnership', 'account', 'invoice']:
account.billing_type = billing_type
db.session.commit()
return True
else:
return False
@classmethod
def api_update(cls, id, partnership_id, name, is_active, partner_account_code,
partner_account_credit_service_provider, users=None):
"""
Update the partnership account's details.
:return: bool
"""
account = PartnershipAccount.query\
.filter(and_(PartnershipAccount.id == id, PartnershipAccount.partnership_id == partnership_id)).first()
if account is not None:
if name is not None and name != '':
account.name = name
if is_active is not None:
account.active = is_active
if partner_account_code is not None and partner_account_code != '':
account.partner_account_code = partner_account_code
if partner_account_credit_service_provider is not None and partner_account_credit_service_provider != '':
partner_account_credit_service_provider_lower = partner_account_credit_service_provider.lower()
existing_credit_tie = (PartnershipAccountCreditTie.query
.filter(and_(PartnershipAccountCreditTie.partnership_account_id == id,
PartnershipAccountCreditTie.service_provider ==
partner_account_credit_service_provider_lower)).first())
if not existing_credit_tie:
new_credit_sp = PartnershipAccountCreditTie(
service_provider=partner_account_credit_service_provider_lower,
active=True,
partnership_account_id=id,
product_type='prequalify',
equifax_enabled=True
)
db.session.add(new_credit_sp)
if users is not None:
for usr in users:
new_user = User(
firstname=usr['firstname'],
lastname=usr['lastname'],
email=usr['email'].lower(),
phonenumber=format_phone_number(str(usr['phonenumber'])),
password=usr['password'],
role=usr['role'],
partnership_account_id=account.id,
partnership_id=account.partnership_id,
company=account.name,
tos_agreement=False
)
db.session.add(new_user)
db.session.flush()
db.session.commit()
return True
else:
return False
@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)
search_chain = (PartnershipAccount.name.ilike(search_query))
# return or_(*search_chain)
return search_chain
@classmethod
def get_partnership_account_by_token(cls, api_token_id):
"""
Find a PartnershipAccount by their REST API token id.
"""
partnership_account = db.session.query(PartnershipAccount)\
.filter(and_(PartnershipAccount.api_token_id == api_token_id, PartnershipAccount.active == True))\
.first()
if partnership_account:
return partnership_account
return None
@classmethod
def regenerate_api_token(cls, id):
"""
Regenerates the PartnershipAccount REST API token.
:return: api token
"""
partnership_account = db.session.query(PartnershipAccount)\
.filter(PartnershipAccount.id == id)\
.first()
if partnership_account is not None:
# Check if api token id already exists, if so, just regenerate
if partnership_account.api_token_id is not None and partnership_account.api_token_id > 0:
return ApiToken.regenerate_token(partnership_account.api_token_id)
# API token id does not exist, need to create a new one and regenerate the key
else:
new_token = ApiToken.create()
if new_token is not None and new_token > 0:
partnership_account.api_token_id = new_token
partnership_account.save()
db.session.commit()
return ApiToken.regenerate_token(new_token)
else:
return None
else:
# No partnership account found
return None
@classmethod
def find_by_token(cls, token):
"""
Find a PartnershipAccount by their REST API token.
"""
# token_hashes = db.session.query(PartnershipAccount.id, PartnershipAccount.api_token_hash).filter(
# PartnershipAccount.api_token_hash.isnot(None)
# ).all()
# for id, hash in token_hashes:
# if bcrypt.check_password_hash(hash, token):
# return PartnershipAccount.query.filter(PartnershipAccount.id == id).first()
return None
@classmethod
def total_calls_count(cls, paid):
total_calls = Lead.query.filter(Lead.partnership_account_id == paid).count()
return total_calls
@classmethod
# TODO: Review me
def total_inbound_calls_count(cls, paid):
total_calls = Lead.query.filter(and_(Lead.partnership_account_id == paid,
Lead.call_type == 'inbound')).count()
return total_calls
@classmethod
# TODO: Review me
def total_inbound_calls_count_for_phone(cls, paid, pnid):
total_calls = Lead\
.query\
.filter(and_(Lead.partnership_account_id == paid, Lead.call_type == 'inbound'))\
.filter(Lead.inbound_id == pnid)\
.count()
return total_calls
@classmethod
# TODO: Review me
def monthly_total_inbound_calls_count(cls, paid):
now = datetime.now()
year = now.year
month = now.month
total_calls = Lead.query.with_entities(Lead.id)\
.filter(and_(Lead.partnership_account_id == paid, Lead.call_type == 'inbound')) \
.filter(extract('year', Lead.created_on) == year) \
.filter(extract('month', Lead.created_on) == month) \
.count()
return total_calls
@classmethod
# TODO: Review me
def previous_total_inbound_calls_count(cls, paid):
now = datetime.now()
year = now.year
month = now.month
if month == 1:
year = now.year - 1
month = 12
else:
month = month - 1
total_calls = Lead.query.with_entities(Lead.id)\
.filter(and_(Lead.partnership_account_id == paid, Lead.call_type == 'inbound')) \
.filter(extract('year', Lead.created_on) == year) \
.filter(extract('month', Lead.created_on) == month) \
.count()
return total_calls
@classmethod
# TODO: Review me
def total_outbound_calls_count(cls, paid):
total_calls = db.session.query(Lead.id)\
.filter(and_(Lead.partnership_account_id == paid, Lead.call_type == 'outbound'))\
.distinct()\
.count()
return total_calls
@classmethod
# TODO: Review me
def total_outbound_calls_count_for_phone(cls, paid, pnid):
total_calls = db.session.query(Lead.id)\
.filter(and_(Lead.partnership_account_id == paid, Lead.call_type == 'outbound'))\
.filter(Lead.inbound_id == pnid)\
.distinct()\
.count()
return total_calls
@classmethod
# TODO: Review me
def monthly_total_outbound_calls_count(cls, paid):
now = datetime.now()
year = now.year
month = now.month
total_calls = db.session.query(Lead).with_entities(Lead.id)\
.filter(and_(Lead.partnership_account_id == paid, Lead.call_type == 'outbound')) \
.filter(extract('year', Lead.created_on) == year) \
.filter(extract('month', Lead.created_on) == month) \
.distinct()\
.count()
return total_calls
@classmethod
# TODO: Review me
def previous_total_outbound_calls_count(cls, paid):
now = datetime.now()
year = now.year
month = now.month
if month == 1:
year = now.year - 1
month = 12
else:
month = month - 1
total_calls = Lead.query.with_entities(Lead.id)\
.filter(and_(Lead.partnership_account_id == paid, Lead.call_type == 'outbound')) \
.filter(extract('year', Lead.created_on) == year) \
.filter(extract('month', Lead.created_on) == month) \
.count()
return total_calls
@classmethod
def monthly_minute_usage(cls, paid):
now = datetime.now()
year = now.year
month = now.month
partnership_account_month_minutes = Lead.query.with_entities(Lead.duration) \
.filter(Lead.partnership_account_id == paid) \
.filter(extract('year', Lead.created_on) == year) \
.filter(extract('month', Lead.created_on) == month)\
.all()
new_call_duration = [i.duration for i in partnership_account_month_minutes]
call_duration_list = 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)
month_minutes = int(sum(call_duration_list))
return month_minutes
@classmethod
def previous_month_minute_usage(cls, paid):
now = datetime.now()
year = now.year
month = now.month
if month == 1:
year = now.year - 1
month = 12
else:
month = month - 1
partnership_account_previous_month_minutes = Lead.query.with_entities(Lead.duration) \
.filter(Lead.partnership_account_id == paid) \
.filter(extract('year', Lead.created_on) == year) \
.filter(extract('month', Lead.created_on) == month).all()
previous_new_call_duration = [i.duration for i in partnership_account_previous_month_minutes]
previous_call_duration_list = list()
for previous_duration in previous_new_call_duration:
if not previous_duration:
previous_duration_zero = float(0)
previous_call_duration_list.append(previous_duration_zero)
else:
previous_float_duration = math.ceil(float(previous_duration) / 60.0)
previous_call_duration_list.append(previous_float_duration)
previous_month_minutes = int(sum(previous_call_duration_list))
return previous_month_minutes
@classmethod
def total_minute_usage(cls, paid):
partnership_account_total_minutes = Lead.query.with_entities(Lead.duration) \
.filter(Lead.partnership_account_id == paid).all()
new_total_duration = [i.duration for i in partnership_account_total_minutes]
total_duration_list = list()
for total in new_total_duration:
if not total:
total_zero = float(0)
total_duration_list.append(total_zero)
else:
total_float_duration = math.ceil(float(total) / 60.0)
total_duration_list.append(total_float_duration)
total_minutes = int(sum(total_duration_list))
return total_minutes
@classmethod
# TODO: Review me
def total_active_phonenumbers(cls, paid):
from buyercall.blueprints.phonenumbers.models import Phone
total_phonenumbers = Phone.query.filter(
and_(Phone.partnership_account_id == paid, Phone.is_deactivated == False)
).count()
return total_phonenumbers
@classmethod
# TODO: Review me
def total_priority_phonenumbers(cls, paid):
from buyercall.blueprints.phonenumbers.models import Phone
total_phonenumbers = Phone.query.filter(
Phone.partnership_account_id == paid
).filter(Phone.type == 'priority', Phone.is_deactivated == '0').count()
return total_phonenumbers
@classmethod
# TODO: Review me
def total_tracking_phonenumbers(cls, paid):
from buyercall.blueprints.phonenumbers.models import Phone
total_phonenumbers = Phone.query.filter(
Phone.partnership_account_id == paid
).filter(Phone.type == 'tracking', Phone.is_deactivated == '0').count()
return total_phonenumbers
@classmethod
# TODO: Review me
def total_mobile_phonenumbers(cls, paid):
from buyercall.blueprints.phonenumbers.models import Phone
total_phonenumbers = Phone.query.filter(
Phone.partnership_account_id == paid
).filter(Phone.type == 'mobile', Phone.is_deactivated == '0').count()
return total_phonenumbers
@classmethod
# TODO: Review me
def total_messages(cls, paid):
total_messages_count = Message.query.filter(Message.partnership_account_id == paid)\
.distinct()\
.count()
return total_messages_count
@classmethod
# TODO: Review me
def total_inbound_messages(cls, paid):
total_messages = Message.query\
.filter(Message.partnership_account_id == paid)\
.filter(or_(Message.direction == 'in', Message.direction == 'inbound'))\
.count()
return total_messages
@classmethod
# TODO: Review me
def total_inbound_messages_for_phone(cls, paid, pnid):
total_messages = Message.query\
.filter(Message.partnership_account_id == paid)\
.filter(or_(Message.direction == 'in', Message.direction == 'inbound'))\
.filter(Message.inbound_id == pnid) \
.distinct() \
.count()
return total_messages
@classmethod
# TODO: Review me
def total_monthly_inbound_messages(cls, paid):
now = datetime.now()
year = now.year
month = now.month
total_messages = Message.query\
.filter(Message.partnership_account_id == paid)\
.filter(or_(Message.direction == 'in', Message.direction == 'inbound'))\
.filter(extract('year', Message.created_on) == year) \
.filter(extract('month', Message.created_on) == month) \
.count()
return total_messages
@classmethod
# TODO: Review me
def previous_total_monthly_inbound_messages(cls, paid):
now = datetime.now()
year = now.year
month = now.month
if month == 1:
year = now.year - 1
month = 12
else:
month = month - 1
total_messages = Message.query\
.filter(Message.partnership_account_id == paid)\
.filter(or_(Message.direction == 'in', Message.direction == 'inbound'))\
.filter(extract('year', Message.created_on) == year) \
.filter(extract('month', Message.created_on) == month) \
.count()
return total_messages
@classmethod
# TODO: Review me
def total_outbound_messages(cls, paid):
total_messages = Message.query\
.filter(Message.partnership_account_id == paid)\
.filter(or_(Message.direction == 'out', Message.direction == 'outbound'))\
.distinct()\
.count()
return total_messages
@classmethod
# TODO: Review me
def total_outbound_messages_for_phone(cls, paid, pnid):
total_messages = Message.query\
.filter(Message.partnership_account_id == paid)\
.filter(or_(Message.direction == 'out', Message.direction == 'outbound')) \
.filter(Message.inbound_id == pnid) \
.distinct()\
.count()
return total_messages
@classmethod
# TODO: Review me
def total_monthly_outbound_messages(cls, paid):
now = datetime.now()
year = now.year
month = now.month
total_messages = Message.query\
.filter(Message.partnership_account_id == paid)\
.filter(or_(Message.direction == 'out', Message.direction == 'outbound'))\
.filter(extract('year', Message.created_on) == year) \
.filter(extract('month', Message.created_on) == month) \
.distinct() \
.count()
return total_messages
@classmethod
# TODO: Review me
def previous_total_monthly_outbound_messages(cls, paid):
now = datetime.now()
year = now.year
month = now.month
if month == 1:
year = now.year - 1
month = 12
else:
month = month - 1
total_messages = Message.query\
.filter(Message.partnership_account_id == paid)\
.filter(or_(Message.direction == 'out', Message.direction == 'outbound'))\
.filter(extract('year', Message.created_on) == year) \
.filter(extract('month', Message.created_on) == month) \
.distinct() \
.count()
return total_messages
@hybrid_property
def api_token_hash(self):
"""
Returns the REST API token for this partnership account.
"""
token = db.session.query(ApiToken.api_token_hash) \
.filter(ApiToken.id == self.api_token_id).first()
if token is not None:
return token
else:
return None
@hybrid_property
def has_active_subscription(self):
if not self.subscription:
return False
return self.subscription.status == 'active'
@has_active_subscription.expression
def has_active_subscription(cls):
if not cls.subscription:
return False
return cls.subscription.status == 'active'
@property
def created_datetime(self):
""" Return the date/time this partnership account occurred
"""
return self.created_on.strftime('%Y-%m-%d %H:%M:%S')
@property
def updated_datetime(self):
""" Return the date/time this partnership account record was updated
"""
return self.updated_on.strftime('%Y-%m-%d %H:%M:%S')
@classmethod
def phone_number_usage(cls, paid, pnid):
try:
inbound_call_count_result = cls.total_inbound_calls_count_for_phone(paid, pnid)
outbound_call_count_result = cls.total_outbound_calls_count_for_phone(paid, pnid)
inbound_message_count_result = cls.total_inbound_messages_for_phone(paid, pnid)
outbound_message_count_result = cls.total_outbound_messages_for_phone(paid, pnid)
return jsonify(
inbound_call_count=inbound_call_count_result,
outbound_call_count=outbound_call_count_result,
inbound_message_count=inbound_message_count_result,
outbound_message_count=outbound_message_count_result
)
except Exception as e:
log.error(traceback.format_exc())
return ''
class Partnership(ResourceMixin, db.Model):
__tablename__ = 'partnerships'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), index=True, nullable=False,
server_default='')
active = db.Column('is_active', db.Boolean(), nullable=False,
server_default='1')
account_invitation_url_token = db.Column(
db.String(255),
index=True,
nullable=False,
server_default=''
)
subscription_id = db.Column(db.Integer, db.ForeignKey('subscriptions.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=True)
users = db.relationship(User, backref="partnership")
partnership_accounts = db.relationship(PartnershipAccount, backref="partnership")
issues = db.relationship(Issue, backref="partnership")
invoices = db.relationship(Invoice, backref='partnership')
logo = db.Column(db.String(2000), index=True, nullable=False,
server_default='')
default_billing_type = db.Column(db.Enum(*BILLING_TYPE, name='billing_types'),
index=True, nullable=False, server_default='partnership')
default_provider = db.Column(
db.String(10), nullable=False, server_default='twilio',
index=True,
)
api_token_id = db.Column(
db.Integer,
db.ForeignKey(
'api_tokens.id',
onupdate='CASCADE',
ondelete='CASCADE'
),
index=True, nullable=True
)
partner_url = db.Column(
db.String(256), nullable=False, server_default='',
index=True,
)
email_sender = db.Column(
db.String(64), nullable=False, server_default='',
index=True,
)
partner_type = db.Column(
db.String(256), nullable=False, server_default='general',
index=True,
)
business_type = db.Column(
db.String(128), nullable=False, server_default='general',
index=True
)
operational_number = db.Column(
db.String(32), nullable=False, server_default='',
index=False,
)
custom_styles = db.Column(
db.String(128), nullable=False, server_default='',
index=False,
)
is_2fa_email_enabled = db.Column(db.Boolean(), nullable=False,
server_default='0')
is_2fa_sms_enabled = db.Column(db.Boolean(), nullable=False,
server_default='1')
is_2fa_enforced = db.Column(db.Boolean(), nullable=False,
server_default='0')
@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)
search_chain = (Partnership.name.ilike(search_query))
# return or_(*search_chain)
return search_chain
@classmethod
def get_partnership_by_token(cls, api_token_id):
"""
Find a Partnership by their REST API token id.
"""
partnership = db.session.query(Partnership)\
.filter(and_(Partnership.api_token_id == api_token_id, Partnership.active == True))\
.first()
if partnership is not None:
return partnership
else:
return None
@classmethod
def regenerate_api_token(cls, id):
"""
Regenerates the Partnership REST API token.
:return: api token
"""
partnership = db.session.query(Partnership)\
.filter(Partnership.id == id)\
.first()
if partnership is not None:
# Check if api token id already exists, if so, just regenerate
if partnership.api_token_id is not None and partnership.api_token_id > 0:
return ApiToken.regenerate_token(partnership.api_token_id)
# API token id does not exist, need to create a new one and regenerate the key
else:
new_token = ApiToken.create()
if new_token is not None and new_token > 0:
partnership.api_token_id = new_token
partnership.save()
db.session.commit()
return ApiToken.regenerate_token(new_token)
else:
return None
else:
# No partnership found
return None
@classmethod
def get_partnership_accounts(cls):
"""
Find and return a list of all the partnership accounts linked to this partnership.
"""
partnership_accounts = (db.session.query(PartnershipAccount)) \
.filter(and_(PartnershipAccount.partnership_id == cls.id,
PartnershipAccount.active == True))\
.order_by(PartnershipAccount.name)\
.all()
return partnership_accounts
@classmethod
def get_partnership_account(cls, paid):
"""
Find and return a partnership accounts linked to this partnership based on partnership account id.
"""
partnership_account = (db.session.query(PartnershipAccount)) \
.filter(and_(PartnershipAccount.partnership_id == cls.id,
PartnershipAccount.active == True,
PartnershipAccount.id == paid)).first()
return partnership_account
@hybrid_property
def api_token_hash(self):
"""
Returns the REST API token for this partnership account.
"""
token = db.session.query(ApiToken.api_token_hash) \
.filter(ApiToken.id == self.api_token_id)\
.first()
if token is not None:
return token
else:
return None
@hybrid_property
def has_active_subscription(self):
if not self.subscription:
return False
return self.subscription.status == 'active'
@hybrid_property
def billing_type(self):
return self.default_billing_type
@classmethod
def update_business_type(cls, partnership_id, business_type):
"""
Updates the busines type of all related partnership accounts.
"""
if partnership_id and business_type:
update_stmt = update(PartnershipAccount)\
.where(PartnershipAccount.partnership_id == partnership_id)\
.values(business_type=business_type)
db.session.execute(update_stmt)
db.session.commit()
class PartnershipCpaasProviders(ResourceMixin, db.Model):
__tablename__ = 'partnership_cpaas_providers'
id = db.Column(db.Integer, primary_key=True)
cpaas_provider_name = db.Column(db.String(32), index=True, nullable=False, server_default='')
active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1')
partnership_id = db.Column(db.Integer, db.ForeignKey('partnerships.id',
onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False)
cpaas_default_provider = db.Column('is_default_provider', db.Boolean(), nullable=False, server_default='0')
cpaas_account_id = db.Column(db.String(128), index=True, nullable=False, server_default='')
cpaas_api_token = db.Column(db.String(128), index=True, nullable=False, server_default='')
cpaas_api_secret = db.Column(db.String(128), index=True, nullable=False, server_default='')
cpaas_user_id = db.Column(db.String(128), index=True, nullable=False, server_default='')
cpaas_api_username = db.Column(db.String(128), index=True, nullable=False, server_default='')
cpaas_api_password = db.Column(db.String(128), index=True, nullable=False, server_default='')
cpaas_site_id = db.Column(db.String(128), index=True, nullable=False, server_default='')
cpaas_location_id = db.Column(db.String(64), index=True, nullable=False, server_default='')
cpaas_sms_application_id = db.Column(db.String(64), index=True, nullable=False, server_default='')
cpaas_voice_application_id = db.Column(db.String(64), index=True, nullable=False, server_default='')
cpaas_caller_id = db.Column(db.String(12), index=True, nullable=False, server_default='')
cpaas_cnam_password = db.Column(db.String(64), index=True, nullable=False, server_default='')
@classmethod
def partnership_twilio_credentials(cls, paid):
"""
Find and return the partnership CPaaS twilio provider otherwise return default provider.
"""
twilio_as_provider = (db.session.query(cls))\
.filter(cls.partnership_id == paid, cls.cpaas_provider_name == 'twilio', cls.active.is_(True)).first()
if twilio_as_provider is not None:
return twilio_as_provider
else:
default_twilio_as_provider = (db.session.query(cls))\
.filter(cls.cpaas_default_provider.is_(True), cls.cpaas_provider_name == 'twilio',
cls.active.is_(True)).first()
return default_twilio_as_provider
@classmethod
def partnership_bandwidth_credentials(cls, paid):
"""
Find and return the partnership CPaaS bandwidth provider otherwise return default provider.
"""
twilio_as_provider = (db.session.query(cls)) \
.filter(cls.partnership_id == paid, cls.cpaas_provider_name == 'bandwidth', cls.active.is_(True)).first()
if twilio_as_provider is not None:
return twilio_as_provider
else:
default_twilio_as_provider = (db.session.query(cls)) \
.filter(cls.cpaas_default_provider.is_(True), cls.cpaas_provider_name == 'bandwidth',
cls.active.is_(True)).first()
return default_twilio_as_provider
class PartnershipCpaasPhoneNumberSubscriptions(ResourceMixin, db.Model):
__tablename__ = 'partnership_cpaas_phonenumber_subscriptions'
id = db.Column(db.Integer, primary_key=True)
cpaas_provider_name = db.Column(db.String(32), index=True, nullable=False, server_default='')
partnership_id = db.Column(db.Integer, db.ForeignKey('partnerships.id',
onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False)
order_type = db.Column(db.String(32), index=True, nullable=False, server_default='')
subscription_id = db.Column(db.String(128), index=True, nullable=False, server_default='')
callback_url = db.Column(db.String(128), index=True, nullable=False, server_default='')
@classmethod
def partnership_bandwidth_phonenumber_subscription(cls, paid):
"""
Find and return the subscription for bandwidth phone number order
"""
partnership_id = paid
return partnership_id
class PartnershipCreditTie(ResourceMixin, db.Model):
__tablename__ = 'partnership_credit_tie'
id = db.Column(db.Integer, primary_key=True)
service_provider = db.Column(db.String(128), index=True, nullable=False, server_default='')
active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1')
partnership_id = db.Column(db.Integer, db.ForeignKey('partnerships.id',
onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False)
product_type = db.Column(db.String(128), index=True, nullable=False, server_default='')
api_account = db.Column(db.String(256), index=True, nullable=False, server_default='')
api_username = db.Column(db.String(256), index=True, nullable=False, server_default='')
api_password = db.Column(db.String(256), index=True, nullable=False, server_default='')
experian_enabled = db.Column('experian_enabled', db.Boolean(), nullable=False, server_default='0')
transunion_enabled = db.Column('transunion_enabled', db.Boolean(), nullable=False, server_default='0')
equifax_enabled = db.Column('equifax_enabled', db.Boolean(), nullable=False, server_default='0')
@classmethod
def partner_finserv_credit_info(cls, pid, product_type):
"""
Find and return the a partnership account's Finserv credit credentials.
"""
finserv_credit_as_provider = (db.session.query(cls)) \
.filter(cls.partnership_id == pid,
cls.service_provider == 'Finserv',
cls.product_type == product_type,
cls.active.is_(True)).first()
if finserv_credit_as_provider is not None:
return finserv_credit_as_provider
else:
return None
class PartnershipAccountCounts(ResourceMixin, db.Model):
__tablename__ = 'partnership_account_counts'
partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id'),
primary_key=True, index=True, nullable=False)
contact_count = db.Column(db.Integer, nullable=True)
call_count = db.Column(db.Integer, nullable=True)
form_lead_count = db.Column(db.Integer, nullable=True)
message_count = db.Column(db.Integer, nullable=True)