File: //home/arjun/projects/buyercall_forms/buyercall/buyercall/blueprints/contacts/models.py
import logging as log
from sqlalchemy.dialects import postgresql as pg
from sqlalchemy import and_, extract, Date, cast
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
from sqlalchemy.orm import load_only, joinedload, column_property
from sqlalchemy.sql import select, func, case, exists
from sqlalchemy.sql.expression import update
from datetime import datetime, date, timedelta
#from buyercall.lib.util_crypto import AESCipher, EncryptedValue
from buyercall.lib.util_crypto import AESCipher
from sqlalchemy.ext.hybrid import Comparator
from flask import current_app as app
import pytz
from buyercall.lib.util_sqlalchemy import ResourceMixin
from buyercall.extensions import db
from buyercall.blueprints.filters import format_phone_number_bracket
from buyercall.blueprints.leads.models import Lead
from buyercall.blueprints.sms.models import Message
from buyercall.blueprints.user.models import User
from buyercall.blueprints.form_leads.models import FormLead
class ContactVehicle(ResourceMixin, db.Model):
__tablename__ = 'contact_vehicles'
id = db.Column(db.Integer, primary_key=True)
contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id', name='contact_vehicles_fkey'), nullable=False)
current_vin = db.Column(db.String(64), nullable=False, server_default='')
current_make = db.Column(db.String(32), nullable=False, server_default='')
current_model = db.Column(db.String(32), nullable=False, server_default='')
current_year = db.Column(db.Integer, nullable=True, server_default=None)
current_mileage = db.Column(db.Integer, nullable=True, server_default=None)
current_condition = db.Column(db.String(64), nullable=False, server_default='')
current_value = db.Column(db.Numeric, nullable=True, server_default=None)
interest_vin = db.Column(db.String(64), nullable=False, server_default='')
interest_make = db.Column(db.String(32), nullable=False, server_default='')
interest_model = db.Column(db.String(32), nullable=False, server_default='')
interest_trim = db.Column(db.String(256), nullable=False, server_default='')
interest_stock = db.Column(db.String(256), nullable=False, server_default='')
interest_year = db.Column(db.Integer, nullable=True, server_default=None)
interest_price = db.Column(db.Numeric, nullable=True)
interest_status = db.Column(db.String(4), nullable=False, server_default='')
interest_mileage = db.Column(db.Integer, nullable=True, server_default=None)
interest_condition = db.Column(db.String(64), nullable=False, server_default='')
interest_listing_url = db.Column(db.String(512), nullable=False, server_default='')
@classmethod
def create(cls, contact_id, current_vin, current_make, current_model, current_year, current_mileage,
current_condition, current_value, interest_vin, interest_make, interest_model, interest_year,
interest_trim, interest_stock, interest_price, interest_status, interest_mileage, interest_condition,
interest_listing_url):
"""
Create a contact vehicle (and interested vehicle)
"""
try:
contact_vehicle = ContactVehicle(
contact_id=contact_id,
current_vin=current_vin,
current_make=current_make,
current_model=current_model,
current_year=current_year,
current_mileage=current_mileage,
current_condition=current_condition,
current_value=current_value,
interest_vin=interest_vin,
interest_make=interest_make,
interest_model=interest_model,
interest_year=interest_year,
interest_trim=interest_trim,
interest_stock=interest_stock,
interest_price=interest_price,
interest_status=interest_status,
interest_mileage=interest_mileage,
interest_condition=interest_condition,
interest_listing_url=interest_listing_url
)
db.session.add(contact_vehicle)
db.session.commit()
return True
except Exception as e:
log.error('Error creating contact vehicle. Error: {}'.format(e))
return False
@classmethod
def update(cls, contact_id, current_vin, current_make, current_model, current_year, current_mileage,
current_condition, current_value, interest_vin, interest_make, interest_model, interest_trim,
interest_stock, interest_year, interest_price, interest_status, interest_mileage, interest_condition,
interest_listing_url):
"""
Update a contact vehicle
:return: bool
"""
result = False
contact_vehicle = ContactVehicle\
.query\
.filter(and_(ContactVehicle.contact_id == contact_id))\
.first()
if contact_vehicle:
if current_vin:
contact_vehicle.current_vin = current_vin
if current_make:
contact_vehicle.current_make = current_make
if current_model:
contact_vehicle.current_model = current_model
if current_year:
contact_vehicle.current_year = current_year
if current_mileage:
contact_vehicle.current_mileage = current_mileage
if current_condition:
contact_vehicle.current_condition = current_condition
if current_value:
contact_vehicle.current_value = current_value
if interest_vin:
contact_vehicle.interest_vin = interest_vin
if interest_make:
contact_vehicle.interest_make = interest_make
if interest_model:
contact_vehicle.interest_model = interest_model
if interest_year:
contact_vehicle.interest_year = interest_year
if interest_trim:
contact_vehicle.interest_trim = interest_trim
if interest_stock:
contact_vehicle.interest_stock = interest_stock
if interest_price:
contact_vehicle.interest_price = interest_price
if interest_status:
contact_vehicle.interest_status = interest_status
if interest_mileage:
contact_vehicle.interest_mileage = interest_mileage
if interest_condition:
contact_vehicle.interest_condition = interest_condition
if interest_listing_url:
contact_vehicle.interest_listing_url = interest_listing_url
db.session.commit()
result = True
return result
class BdcStatuses(ResourceMixin, db.Model):
__tablename__ = 'bdc_statuses'
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.String(128), nullable=False)
display_name = db.Column(db.String(128), nullable=False)
default = db.Column('default', db.Boolean(), nullable=False, server_default='0')
@classmethod
def add_status(cls, display_name, partnership_account_id):
"""
Create new bdc status
"""
try:
# Check if status exists
exists = BdcStatuses\
.query\
.filter(func.lower(BdcStatuses.status) == display_name.lower())\
.first()
if exists:
BdcStatusPartnershipAccountTie.add_status_tie(exists.id, partnership_account_id)
tie_count = BdcStatusPartnershipAccountTie\
.query\
.filter(BdcStatusPartnershipAccountTie.bdc_status_id == exists.id)\
.limit(2).all()
if len(tie_count) > 1:
exists.default = True
db.session.commit()
else:
# Add status
new_bdc_status = BdcStatuses()
new_bdc_status.status = display_name.lower()
new_bdc_status.display_name = display_name
new_bdc_status.default = False
db.session.add(new_bdc_status)
db.session.flush()
status_id = new_bdc_status.id
BdcStatusPartnershipAccountTie.add_status_tie(status_id, partnership_account_id)
except Exception as e:
log.error('Error adding bdc status. Error: ' + str(e))
@classmethod
def add_status_dict(cls, status_dict, partnership_account_id):
"""
Add bdc status to partnership account
"""
ties_exist = BdcStatusPartnershipAccountTie\
.query\
.filter(BdcStatusPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.first()
for sd in status_dict:
BdcStatuses.add_source(status_dict[sd], partnership_account_id)
if ties_exist is None:
BdcStatuses.add_status_defaults_to_tie(partnership_account_id)
@classmethod
def add_status_defaults_to_tie(cls, partnership_account_id):
"""
Add bdc status to defaults partnership account
"""
default_statuses = BdcStatuses\
.query\
.with_entities(BdcStatuses.id)\
.filter(BdcStatuses.default == True)\
.all()
status_ties = BdcStatusPartnershipAccountTie\
.query\
.with_entities(BdcStatusPartnershipAccountTie.bdc_status_id) \
.filter(BdcStatusPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.all()
for ds in default_statuses:
if ds not in status_ties:
BdcStatusPartnershipAccountTie.add_status_tie(ds, partnership_account_id)
@classmethod
def remove_status(cls, status_id, partnership_account_id):
"""
Delete bdc status.
"""
try:
BdcStatusPartnershipAccountTie.remove_status_tie(status_id, partnership_account_id)
tie_count = BdcStatusPartnershipAccountTie\
.query\
.filter(BdcStatusPartnershipAccountTie.bdc_status_id == status_id)\
.count()
if tie_count == 0:
exists = BdcStatuses \
.query \
.filter(BdcStatuses.id == status_id) \
.first()
if exists and not exists.default:
db.session.delete(exists)
db.session.commit()
except Exception as e:
log.error('Error removing bdc status tie. Error: ' + str(e))
@classmethod
def get_assigned_status_list(cls, partnership_account_id):
"""
Get bdc status list.
"""
statuses = BdcStatuses\
.query \
.join(BdcStatusPartnershipAccountTie) \
.filter(BdcStatusPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.all()
if statuses and len(statuses) > 0:
return statuses
else:
sources = BdcStatuses \
.query \
.filter(BdcStatuses.default == True) \
.all()
return sources
@classmethod
def get_assigned_status_list_as_dict(cls, partnership_account_id):
"""
Get bdc status list as dictionary.
"""
result = {}
statuses = BdcStatuses \
.query \
.join(BdcStatusPartnershipAccountTie) \
.with_entities(BdcStatuses.status, BdcStatuses.display_name) \
.filter(BdcStatusPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.all()
if not statuses or len(statuses) == 0:
statuses = BdcStatuses \
.query \
.with_entities(BdcStatuses.status, BdcStatuses.display_name) \
.filter(BdcStatuses.default == True) \
.all()
for s in statuses:
result[s.status] = s.display_name
return result
@classmethod
def get_complete_status_list_as_dict(cls):
"""
Get list of all bdc statuses in dictionary format.
"""
status_list = {}
statuses = BdcStatuses\
.query\
.with_entities(BdcStatuses.status, BdcStatuses.display_name)\
.all()
for s in statuses:
status_list[s.status] = s.display_name
return status_list
@classmethod
def get_formatted_status(cls, list, value):
"""
Takes in a dictionary of statuses and a value.
Returns suitable display text.
"""
result = ''
if list:
if value:
if value in list:
result = list[value]
else:
result = value
return result
class BdcStatusPartnershipAccountTie(ResourceMixin, db.Model):
__tablename__ = 'bdc_status_partnership_account_tie'
bdc_status_id = db.Column(db.Integer, db.ForeignKey('bdc_statuses.id'), primary_key=True)
partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id'), primary_key=True)
@classmethod
def add_status_tie(cls, status_id, partnership_account_id):
"""
Create new bdc status tie.
"""
try:
existing_tie = BdcStatusPartnershipAccountTie \
.query \
.filter(BdcStatusPartnershipAccountTie.bdc_status_id == status_id,
BdcStatusPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.first()
if not existing_tie:
new_status_tie = BdcStatusPartnershipAccountTie()
new_status_tie.partnership_account_id = partnership_account_id
new_status_tie.marketing_source_id = status_id
db.session.add(new_status_tie)
db.session.commit()
except Exception as e:
log.error('Error adding bdc status tie. Error: ' + str(e))
@classmethod
def remove_source_tie(cls, status_id, partnership_account_id):
"""
Delete bdc status tie.
"""
try:
BdcStatusPartnershipAccountTie \
.query \
.filter(BdcStatusPartnershipAccountTie.bdc_status_id == status_id,
BdcStatusPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.delete()
db.session.commit()
except Exception as e:
log.error('Error removing bdc status tie. Error: ' + str(e))
class Campaigns(ResourceMixin, db.Model):
__tablename__ = 'campaigns'
id = db.Column(db.Integer, primary_key=True)
display_name = db.Column(db.String(128), nullable=False)
default = db.Column('default', db.Boolean(), nullable=False, server_default='0')
@classmethod
def is_not_default_campaign(cls, display_name):
# Check if display_name is default campaign
test_display_name = display_name.lower().replace(" ", "")
if test_display_name != 'nocampaign' and display_name:
return True
return False
@classmethod
def add_campaign(cls, display_name, partnership_account_id):
"""
Create new add campaign
"""
campaign_id = -1
try:
# Check if campaign can be added
if Campaigns.is_not_default_campaign(display_name):
# Check if campaign exists
exists = Campaigns\
.query\
.filter(func.lower(Campaigns.display_name) == display_name.lower())\
.first()
if exists:
CampaignPartnershipAccountTie.add_campaign_tie(exists.id, partnership_account_id)
campaign_id = exists.id
tie_count = CampaignPartnershipAccountTie \
.query \
.filter(CampaignPartnershipAccountTie.campaign_id == exists.id) \
.limit(2).all()
if len(tie_count) > 1:
exists.default = True
db.session.commit()
else:
# Add campaign
new_campaign = Campaigns()
new_campaign.display_name = display_name
new_campaign.default = False
db.session.add(new_campaign)
db.session.flush()
campaign_id = new_campaign.id
CampaignPartnershipAccountTie.add_campaign_tie(campaign_id, partnership_account_id)
return campaign_id
except Exception as e:
log.error('Error adding campaign. Error: ' + str(e))
return campaign_id
@classmethod
def update_campaign(cls, id, display_name, partnership_account_id):
"""
Update existing campaign
"""
try:
exists = Campaigns \
.query \
.join(CampaignPartnershipAccountTie) \
.filter(CampaignPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.filter(Campaigns.id == id) \
.first()
if exists:
if not exists.default:
exists.display_name = display_name
db.session.commit()
return True
except Exception as e:
log.error('Error updating campaign. Error: ' + str(e))
return False
@classmethod
def add_campaign_dict(cls, campaign_dict, partnership_account_id):
"""
Add campaign_dict to partnership account
"""
# Check if campaign can be added
if cls.is_not_default_campaign:
ties_exist = CampaignPartnershipAccountTie\
.query\
.filter(CampaignPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.first()
for sd in campaign_dict:
Campaigns.add_campaign(campaign_dict[sd], partnership_account_id)
if ties_exist is None:
Campaigns.add_campaign_defaults_to_tie(partnership_account_id)
@classmethod
def add_campaign_defaults_to_tie(cls, partnership_account_id):
"""
Add campaign to defaults partnership account
"""
default_campaigns = Campaigns\
.query\
.with_entities(Campaigns.id)\
.filter(Campaigns.default == True)\
.all()
campaign_ties = CampaignPartnershipAccountTie\
.query\
.with_entities(CampaignPartnershipAccountTie.campaign_id) \
.filter(CampaignPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.all()
for ds in default_campaigns:
if ds not in campaign_ties:
CampaignPartnershipAccountTie.add_campaign_tie(ds, partnership_account_id)
@classmethod
def remove_campaign(cls, campaign_id, partnership_account_id):
"""
Delete campaign.
"""
try:
CampaignPartnershipAccountTie.remove_campaign_tie(campaign_id, partnership_account_id)
tie_count = CampaignPartnershipAccountTie\
.query\
.filter(CampaignPartnershipAccountTie.campaign_id == campaign_id)\
.count()
if tie_count == 0:
exists = Campaigns \
.query \
.filter(Campaigns.id == campaign_id) \
.first()
if exists and not exists.default:
db.session.delete(exists)
db.session.commit()
except Exception as e:
log.error('Error removing campaign tie. Error: ' + str(e))
@classmethod
def get_assigned_campaign_list(cls, partnership_account_id):
"""
Get campaign list.
"""
campaigns = Campaigns\
.query \
.join(CampaignPartnershipAccountTie) \
.filter(CampaignPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.all()
if campaigns and len(campaigns) > 0:
return campaigns, False
else:
campaigns = Campaigns \
.query \
.filter(Campaigns.default == True) \
.all()
return campaigns, True
@classmethod
def get_assigned_campaign_list_as_dict(cls, partnership_account_id):
"""
Get campaign list as dictionary.
"""
result = {}
defaultCampaigns = True
campaigns = Campaigns \
.query \
.join(CampaignPartnershipAccountTie) \
.filter(CampaignPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.all()
if not campaigns or len(campaigns) == 0:
campaigns = Campaigns \
.query \
.filter(Campaigns.default == True) \
.all()
else:
defaultCampaigns = False
for s in campaigns:
result[s.id] = s.display_name
return result, defaultCampaigns
@classmethod
def get_complete_campaign_list_as_dict(cls):
"""
Get list of all campaign in dictionary format.
"""
campaign_list = {}
campaigns = Campaigns\
.query\
.with_entities(Campaigns.display_name)\
.all()
for s in campaigns:
campaign_list[s.id] = s.display_name
return campaign_list
@classmethod
def get_formatted_campaign(cls, list, value):
"""
Takes in a dictionary of campaigns and a value.
Returns suitable display text.
"""
result = ''
if list:
if value:
if value in list:
result = list[value]
else:
result = value
return result
@classmethod
def get_campaign_by_name(cls, value, partnership_account_id):
campaigns = Campaigns\
.query \
.join(CampaignPartnershipAccountTie) \
.filter(CampaignPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.all()
class CampaignPartnershipAccountTie(ResourceMixin, db.Model):
__tablename__ = 'campaign_partnership_account_tie'
campaign_id = db.Column(db.Integer, db.ForeignKey('campaigns.id'), primary_key=True)
partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id'), primary_key=True)
@classmethod
def add_campaign_tie(cls, campaign_id, partnership_account_id):
"""
Create new campaign tie.
"""
try:
existing_tie = CampaignPartnershipAccountTie \
.query \
.filter(CampaignPartnershipAccountTie.campaign_id == campaign_id,
CampaignPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.first()
if not existing_tie:
new_campaign_tie = CampaignPartnershipAccountTie()
new_campaign_tie.partnership_account_id = partnership_account_id
new_campaign_tie.campaign_id = campaign_id
db.session.add(new_campaign_tie)
db.session.commit()
except Exception as e:
log.error('Error adding campaign tie. Error: ' + str(e))
@classmethod
def remove_campaign_tie(cls, campaign_id, partnership_account_id):
"""
Delete campaign tie.
"""
try:
CampaignPartnershipAccountTie \
.query \
.filter(CampaignPartnershipAccountTie.campaign_id == campaign_id,
CampaignPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.delete()
db.session.commit()
except Exception as e:
log.error('Error removing campaign tie. Error: ' + str(e))
class MarketingSources(ResourceMixin, db.Model):
__tablename__ = 'marketing_sources'
id = db.Column(db.Integer, primary_key=True)
source = db.Column(db.String(128), nullable=False)
display_name = db.Column(db.String(128), nullable=False)
default = db.Column('default', db.Boolean(), nullable=False, server_default='0')
@classmethod
def add_source(cls, display_name, partnership_account_id):
"""
Create new marketing source
"""
try:
# Check if source exists
exists = MarketingSources\
.query\
.filter(func.lower(MarketingSources.source) == display_name.lower())\
.first()
if exists:
MarketingSourcePartnershipAccountTie.add_source_tie(exists.id, partnership_account_id)
tie_count = MarketingSourcePartnershipAccountTie\
.query\
.filter(MarketingSourcePartnershipAccountTie.marketing_source_id == exists.id)\
.limit(2).all()
if len(tie_count) > 1:
exists.default = True
db.session.commit()
else:
# Add source
new_marketing_source = MarketingSources()
new_marketing_source.source = display_name.lower()
new_marketing_source.display_name = display_name
new_marketing_source.default = False
db.session.add(new_marketing_source)
db.session.flush()
source_id = new_marketing_source.id
MarketingSourcePartnershipAccountTie.add_source_tie(source_id, partnership_account_id)
except Exception as e:
log.error('Error adding marketing source. Error: ' + str(e))
@classmethod
def add_source_dict(cls, source_dict, partnership_account_id):
"""
Add marketing source to partnership account
"""
ties_exist = MarketingSourcePartnershipAccountTie\
.query\
.filter(MarketingSourcePartnershipAccountTie.partnership_account_id == partnership_account_id)\
.first()
for sd in source_dict:
MarketingSources.add_source(source_dict[sd], partnership_account_id)
if ties_exist is None:
MarketingSources.add_source_defaults_to_tie(partnership_account_id)
@classmethod
def add_source_defaults_to_tie(cls, partnership_account_id):
"""
Add marketing source to defaults partnership account
"""
default_sources = MarketingSources\
.query\
.with_entities(MarketingSources.id)\
.filter(MarketingSources.default == True)\
.all()
source_ties = MarketingSourcePartnershipAccountTie\
.query\
.with_entities(MarketingSourcePartnershipAccountTie.marketing_source_id) \
.filter(MarketingSourcePartnershipAccountTie.partnership_account_id == partnership_account_id)\
.all()
for ds in default_sources:
if ds not in source_ties:
MarketingSourcePartnershipAccountTie.add_source_tie(ds, partnership_account_id)
@classmethod
def remove_source(cls, source_id, partnership_account_id):
"""
Delete marketing source.
"""
try:
MarketingSourcePartnershipAccountTie.remove_source_tie(source_id, partnership_account_id)
tie_count = MarketingSourcePartnershipAccountTie\
.query\
.filter(MarketingSourcePartnershipAccountTie.marketing_source_id == source_id)\
.count()
if tie_count == 0:
exists = MarketingSources \
.query \
.filter(MarketingSources.id == source_id) \
.first()
if exists and not exists.default:
db.session.delete(exists)
db.session.commit()
except Exception as e:
log.error('Error removing marketing source tie. Error: ' + str(e))
@classmethod
def get_assigned_source_list(cls, partnership_account_id):
"""
Get marketing source list.
"""
sources = MarketingSources\
.query \
.join(MarketingSourcePartnershipAccountTie) \
.filter(MarketingSourcePartnershipAccountTie.partnership_account_id == partnership_account_id)\
.all()
if sources and len(sources) > 0:
return sources
else:
sources = MarketingSources \
.query \
.filter(MarketingSources.default == True) \
.all()
return sources
@classmethod
def get_assigned_source_list_as_dict(cls, partnership_account_id):
"""
Get source list as dictionary.
"""
result = {}
sources = MarketingSources \
.query \
.join(MarketingSourcePartnershipAccountTie) \
.with_entities(MarketingSources.source, MarketingSources.display_name) \
.filter(MarketingSourcePartnershipAccountTie.partnership_account_id == partnership_account_id) \
.all()
if not sources or len(sources) == 0:
sources = MarketingSources \
.query \
.with_entities(MarketingSources.source, MarketingSources.display_name) \
.filter(MarketingSources.default == True) \
.all()
for s in sources:
result[s.source] = s.display_name
return result
@classmethod
def get_complete_source_list_as_dict(cls):
"""
Get list of all marketing sources in dictionary format.
"""
source_list = {}
sources = MarketingSources\
.query\
.with_entities(MarketingSources.source, MarketingSources.display_name)\
.all()
for s in sources:
source_list[s.source] = s.display_name
return source_list
@classmethod
def get_formatted_source(cls, list, value):
"""
Takes in a dictionary of sources and a value.
Returns suitable display text.
"""
result = ''
if list:
if value:
if value in list:
result = list[value]
else:
result = value
return result
class MarketingSourcePartnershipAccountTie(ResourceMixin, db.Model):
__tablename__ = 'marketing_source_partnership_account_tie'
marketing_source_id = db.Column(db.Integer, db.ForeignKey('marketing_sources.id'), primary_key=True)
partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id'), primary_key=True)
@classmethod
def add_source_tie(cls, source_id, partnership_account_id):
"""
Create new marketing source tie.
"""
try:
existing_tie = MarketingSourcePartnershipAccountTie \
.query \
.filter(MarketingSourcePartnershipAccountTie.marketing_source_id == source_id,
MarketingSourcePartnershipAccountTie.partnership_account_id == partnership_account_id) \
.first()
if not existing_tie:
new_marketing_tie = MarketingSourcePartnershipAccountTie()
new_marketing_tie.partnership_account_id = partnership_account_id
new_marketing_tie.marketing_source_id = source_id
db.session.add(new_marketing_tie)
db.session.commit()
except Exception as e:
log.error('Error adding marketing source tie. Error: ' + str(e))
@classmethod
def remove_source_tie(cls, source_id, partnership_account_id):
"""
Delete marketing source tie.
"""
try:
MarketingSourcePartnershipAccountTie \
.query \
.filter(MarketingSourcePartnershipAccountTie.marketing_source_id == source_id,
MarketingSourcePartnershipAccountTie.partnership_account_id == partnership_account_id) \
.delete()
db.session.commit()
except Exception as e:
log.error('Error removing marketing source tie. Error: ' + str(e))
class Status(ResourceMixin, db.Model):
__tablename__ = 'statuses'
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.String(128), nullable=False)
display_name = db.Column(db.String(128), nullable=False)
default = db.Column('default', db.Boolean(), nullable=False, server_default='0')
@classmethod
def add_status_dict(cls, status_dict, partnership_account_id):
"""
Add statuses to partnership account
"""
ties_exist = StatusPartnershipAccountTie\
.query\
.filter(StatusPartnershipAccountTie.partnership_account_id == partnership_account_id)\
.first()
for sd in status_dict:
Status.add_status(status_dict[sd], partnership_account_id)
if ties_exist is None:
Status.add_status_defaults_to_tie(partnership_account_id)
@classmethod
def add_status(cls, display_name, partnership_account_id):
"""
Create new status
"""
try:
# Check if source exists
exists = Status \
.query \
.filter(func.lower(Status.status) == display_name.lower()) \
.first()
if exists:
StatusPartnershipAccountTie.add_status_tie(exists.id, partnership_account_id)
tie_count = StatusPartnershipAccountTie \
.query \
.filter(StatusPartnershipAccountTie.status_id == exists.id) \
.limit(2).all()
if len(tie_count) > 1:
exists.default = True
db.session.commit()
else:
# Add status
new_status = Status()
new_status.status = display_name.lower()
new_status.display_name = display_name
new_status.default = False
db.session.add(new_status)
db.session.flush()
status_id = new_status.id
StatusPartnershipAccountTie.add_status_tie(status_id, partnership_account_id)
except Exception as e:
log.error('Error adding status. Error: ' + str(e))
@classmethod
def add_status_defaults_to_tie(cls, partnership_account_id):
"""
Add status to defaults partnership account
"""
default_statuses = Status \
.query \
.with_entities(Status.id) \
.filter(Status.default == True) \
.all()
status_ties = StatusPartnershipAccountTie \
.query \
.with_entities(StatusPartnershipAccountTie.status_id) \
.filter(StatusPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.all()
for ds in default_statuses:
if ds not in status_ties:
StatusPartnershipAccountTie.add_status_tie(ds, partnership_account_id)
@classmethod
def remove_status(cls, status_id, partnership_account_id):
"""
Delete status.
"""
try:
StatusPartnershipAccountTie.remove_status_tie(status_id, partnership_account_id)
tie_count = StatusPartnershipAccountTie \
.query \
.filter(StatusPartnershipAccountTie.status_id == status_id) \
.count()
if tie_count == 0:
exists = Status \
.query \
.filter(Status.id == status_id) \
.first()
if exists and not exists.default:
db.session.delete(exists)
db.session.commit()
except Exception as e:
log.error('Error removing status tie. Error: ' + str(e))
@classmethod
def get_assigned_status_list(cls, partnership_account_id):
"""
Get status list.
"""
statuses = Status \
.query \
.join(StatusPartnershipAccountTie) \
.filter(StatusPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.all()
if statuses and len(statuses) > 0:
return statuses
else:
statuses = Status \
.query \
.filter(Status.default == True) \
.all()
return statuses
@classmethod
def get_assigned_status_list_as_dict(cls, partnership_account_id):
"""
Get status list as dictionary.
"""
result = {}
statuses = Status \
.query \
.join(StatusPartnershipAccountTie) \
.with_entities(Status.status, Status.display_name) \
.filter(StatusPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.all()
if not statuses or len(statuses) == 0:
statuses = Status \
.query \
.with_entities(Status.status, Status.display_name) \
.filter(Status.default == True) \
.all()
for s in statuses:
result[s.status] = s.display_name
return result
@classmethod
def get_complete_status_list_as_dict(cls):
"""
Get list of all statuses in dictionary format.
"""
status_list = {}
statuses = Status.query.with_entities(Status.status, Status.display_name).all()
for s in statuses:
status_list[s.status] = s.display_name
return status_list
@classmethod
def get_formatted_status(cls, list, value):
"""
Takes in a dictionary of statuses and a value.
Returns suitable display text.
"""
result = ''
if list:
if value:
if value in list:
result = list[value]
else:
result = value
return result
class StatusPartnershipAccountTie(ResourceMixin, db.Model):
__tablename__ = 'status_partnership_account_tie'
status_id = db.Column(db.Integer, db.ForeignKey('statuses.id'), primary_key=True)
partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id'), primary_key=True)
@classmethod
def add_status_tie(cls, status_id, partnership_account_id):
"""
Create new status tie.
"""
try:
existing_tie = StatusPartnershipAccountTie \
.query \
.filter(StatusPartnershipAccountTie.status_id == status_id,
StatusPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.first()
if not existing_tie:
new_status_tie = StatusPartnershipAccountTie()
new_status_tie.partnership_account_id = partnership_account_id
new_status_tie.status_id = status_id
db.session.add(new_status_tie)
db.session.commit()
except Exception as e:
log.error('Error adding status tie. Error: ' + str(e))
@classmethod
def remove_status_tie(cls, status_id, partnership_account_id):
"""
Delete status tie.
"""
try:
StatusPartnershipAccountTie \
.query \
.filter(StatusPartnershipAccountTie.status_id == status_id,
StatusPartnershipAccountTie.partnership_account_id == partnership_account_id) \
.delete()
db.session.commit()
except Exception as e:
log.error('Error removing status tie. Error: ' + str(e))
class CreditReports(ResourceMixin, db.Model):
__tablename__ = 'credit_reports'
id = db.Column(db.Integer, primary_key=True)
service_provider = db.Column(db.String(128), index=False, nullable=False, server_default='')
iframe_url = db.Column(db.String(1024), index=False, nullable=False, server_default='')
transaction_id = db.Column(db.String(256), index=False, nullable=False, server_default='')
credit_score = db.Column(db.String(64), index=False, nullable=False, server_default='')
score_card_name = db.Column(db.String(512), index=False, nullable=False, server_default='')
credit_bureau = db.Column(db.String(128), index=False, nullable=False, server_default='')
product_type = db.Column(db.String(128), index=False, nullable=False, server_default='')
description = db.Column(db.String(512), index=False, nullable=False, server_default='')
is_successful = db.Column('is_successful', db.Boolean(), nullable=False, server_default='0')
is_approved = db.Column('is_approved', db.Boolean(), nullable=False, server_default='0')
trades = db.Column(pg.JSON, nullable=False, server_default='{}')
contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=False)
partnership_account_id = db.Column(db.Integer, db.ForeignKey('partnership_accounts.id',
onupdate='CASCADE',
ondelete='CASCADE'),
index=True, nullable=False)
@property
def created_datetime(self):
""" Return the date/time this credit report was added
"""
date = self.created_on
tz = pytz.timezone('US/Eastern') # timezone you want to convert to from UTC
local_dt = date.astimezone(tz)
est_time = local_dt.strftime('%Y-%m-%d %H:%M:%S')
return est_time
@classmethod
def contact_credit_score_object(cls, cid, paid):
"""
Find and return the a partnership account's contact credit score object.
"""
credit_score_object = (db.session.query(cls))\
.filter(cls.partnership_account_id == paid,
cls.contact_id == cid).all()
if credit_score_object is not None:
return credit_score_object
else:
return None
@classmethod
def contact_credit_report_count(cls, cid, paid):
"""
Find and return the a partnership account's contact credit score object.
"""
credit_report_count = (db.session.query(cls)) \
.filter(cls.partnership_account_id == paid,
cls.contact_id == cid).count()
if credit_report_count is not None:
return credit_report_count
else:
return 0
@classmethod
def current_month_eq_credit_report_count(cls, paid):
"""
Get equifax lead count for current month per account
"""
current_year = datetime.now().year
current_month = datetime.now().month
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.service_provider == 'Offerlogix',
extract('year', cls.created_on) == current_year,
extract('month', cls.created_on) == current_month))\
.distinct(cls.contact_id).count()
if count:
return count
else:
return 0
@classmethod
def last_month_eq_credit_report_count(cls, paid):
"""
Get equifax lead count for previous month per account
"""
current_year = datetime.now().year
last_month = datetime.now().month - 1
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.service_provider == 'Offerlogix',
extract('year', cls.created_on) == current_year,
extract('month', cls.created_on) == last_month)).distinct(cls.contact_id).count()
if count:
return count
else:
return 0
@classmethod
def last_two_month_eq_credit_report_count(cls, paid):
"""
Get equifax lead count for previous 2 month per account
"""
current_year = datetime.now().year
last_month = datetime.now().month - 2
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.service_provider == 'Offerlogix',
extract('year', cls.created_on) == current_year,
extract('month', cls.created_on) == last_month)).distinct(cls.contact_id).count()
if count:
return count
else:
return 0
@classmethod
def today_eq_credit_report_count(cls, paid):
"""
Get equifax lead count for current month per account
"""
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.service_provider == 'Offerlogix',
cast(cls.created_on, Date) == date.today())).distinct(cls.contact_id).count()
if count:
return count
else:
return 0
@classmethod
def yesterday_eq_credit_report_count(cls, paid):
"""
Get equifax lead count for yesterday
"""
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.service_provider == 'Offerlogix',
cast(cls.created_on, Date) == date.today() - timedelta(days=1)))\
.distinct(cls.contact_id).count()
if count:
return count
else:
return 0
class CreditReportsReporting(ResourceMixin, db.Model):
__tablename__ = 'credit_reports_reporting'
id = db.Column(db.Integer, primary_key=True)
service_provider = db.Column(db.String(128), index=False, nullable=True)
transaction_id = db.Column(db.String(256), index=False, nullable=True)
credit_bureau = db.Column(db.String(128), index=False, nullable=True)
product_type = db.Column(db.String(128), index=False, nullable=True)
description = db.Column(db.String(512), index=False, nullable=True)
is_successful = db.Column('is_successful', db.Boolean(), nullable=True)
contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id', name='credit_reports_reporting_contact_id_fkey'),
index=True, nullable=False)
partnership_account_id = db.Column(db.Integer, db.ForeignKey(
'partnership_accounts.id', name='credit_reports_reporting_partnership_account_fkey'),
index=True, nullable=False)
class ContactNotes(db.Model):
__tablename__ = 'contact_notes'
id = db.Column(db.Integer, primary_key=True)
created_on = db.Column(db.DateTime(), nullable=False)
updated_on = db.Column(db.DateTime(), nullable=False)
contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id'), nullable=False)
is_enabled = db.Column(db.Boolean, nullable=False)
text = db.Column(db.Text(), nullable=True, server_default='')
user = db.relationship('User', backref='contact_notes')
user_id = db.Column(
db.Integer,
db.ForeignKey(
'users.id',
name='contact_notes_user_id_fkey',
onupdate='CASCADE',
ondelete='CASCADE'
),
index=True, nullable=False
)
partnership_account_id = db.Column(
db.Integer,
db.ForeignKey(
'partnership_accounts.id',
name='contact_note_partnership_account_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
"""
contactnote = note
db.session.add(ContactNotes(**contactnote))
db.session.commit()
return True
@classmethod
def update(cls, contact_note_id, contact_note_text, contact_note_updated_date, contact_note_user_id, contact_note_enabled):
"""
Return whether or not the contact was updated successfully.
:return: bool
"""
if contact_note_enabled:
db.session.query(ContactNotes).filter_by(id=contact_note_id).update(
{"updated_on": contact_note_updated_date,
"text": contact_note_text,
"is_enabled": contact_note_enabled,
"user_id": contact_note_user_id
}
)
db.session.commit()
else:
db.session.query(ContactNotes).filter_by(id=contact_note_id).update(
{"updated_on": contact_note_updated_date,
"is_enabled": contact_note_enabled,
"user_id": contact_note_user_id
}
)
db.session.commit()
return True
@classmethod
def delete(cls, contact_note_id):
"""Return whether or not the contact was deleted successfully.
:return: bool
"""
try:
_deleted = db.session.query(ContactNotes).filter_by(id=contact_note_id).delete()
db.session.commit()
return True
except Exception:
return False
@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 self.user.firstname + ' ' + self.user.lastname
@hybrid_property
def name(self):
return f'{self.user.firstname} {self.user.lastname}'
@name.expression
def name(cls):
return select(
[func.concat(User.firstname, " ", User.lastname).label("name")]
).where(User.id == cls.user_id).label("user")
class Contact(ResourceMixin, db.Model):
## Statuses for contact leads
#STATUSES = OrderedDict([
# ('no status', 'No Status'),
# ('new lead', 'New Lead'),
# ('general interest', 'General Interest'),
# ('follow-up required', 'Follow-up Required'),
# ('application started', 'Application Started'),
# ('application approved', 'Application Approved'),
# ('application declined', 'Application Declined'),
# ('under contract', 'Under Contract'),
# ('previous customer', 'Previous Customer'),
# ('appointment scheduled', 'Appointment Scheduled'),
# ('service', 'Service'),
# ('billing and accounts', 'Billing and Accounts'),
# ('sold customer', 'Sold Customer'),
# ('completed conversation', 'Completed Conversation'),
# ('do not contact', 'Do Not Contact'),
# ('other', 'Other')
#])
## Marketing sources for contact leads
#MARKETING_SOURCES = OrderedDict([
# ('no source', 'No Source'),
# ('website inventory', 'Website Inventory'),
# ('website application', 'Website Application'),
# ('website contact', 'Website Contact'),
# ('3pl - cars direct', '3PL - Cars Direct'),
# ('3pl - integrity', '3PL - Integrity'),
# ('3pl - auto loan option', '3PL - Auto Loan Option'),
# ('sem call', 'SEM Call'),
# ('sem application', 'SEM Application'),
# ('self-generated', 'Self-Generated'),
# ('walk-in', 'Walk-in'),
# ('customer referral', 'Customer Referral'),
# ('dealership referral', 'Dealership Referral'),
# ('facebook', 'Facebook'),
# ('call center', 'Call Center'),
# ('email campaign', 'Email Campaign')
#])
__tablename__ = 'contacts'
id = db.Column(db.Integer, primary_key=True)
firstname = db.Column(db.String(128), nullable=False, server_default='')
lastname = db.Column(db.String(128), nullable=False, server_default='')
caller_id = db.Column(db.String(128), nullable=False, server_default='')
phonenumber_1 = db.Column(db.String(64), nullable=False, server_default='')
phonenumber_2 = db.Column(db.String(64), nullable=False, server_default='')
email = db.Column(db.String(64), nullable=False, server_default='')
#email = db.Column(EncryptedValue(128), nullable=False, server_default='')
address_1 = db.Column(db.String(128), nullable=False, server_default='')
address_2 = db.Column(db.String(128), nullable=False, server_default='')
city = db.Column(db.String(64), nullable=False, server_default='')
state = db.Column(db.String(64), nullable=False, server_default='')
zip = db.Column(db.String(64), nullable=False, server_default='')
country = db.Column(db.String(64), nullable=False, server_default='')
ip = db.Column(db.String(32), nullable=False, server_default = '')
external_source_type = db.Column(db.String(32), nullable=False, server_default='')
# The partnership account id associated with the contact
partnership_account_id = db.Column(db.Integer, db.ForeignKey(
'partnership_accounts.id', name='contact_partnership_account_fkey', onupdate='CASCADE', ondelete='CASCADE'),
index=True, nullable=False)
# The do not call field indicating a contact should not be call
is_do_not_call = db.Column('is_do_not_call', db.Boolean(), nullable=False, server_default='0')
# Unscribed field for contact
is_unsubscribe = db.Column('is_unsubscribe', db.Boolean(), nullable=False, server_default='0')
# assigned to agent field
agent_assigned = db.Column(db.String(128), nullable=True)
# progress Status for the lead
status = db.Column(db.String(128), index=True, nullable=False,
server_default='no status')
bdc_status = db.Column(db.String(128), index=True, nullable=False,
server_default='no status')
marketing_source = db.Column(db.String(128), index=True, nullable=False,
server_default='no source')
campaign_id = db.Column(db.Integer, nullable=True)
agent_id = db.Column(db.Integer, nullable=True)
birthday = db.Column(db.String(64), nullable=False, server_default='')
avatar = db.Column(db.String(512), nullable=False, server_default='')
large_avatar = db.Column(db.String(512), nullable=False, server_default='')
api_source = db.Column(db.Boolean(), nullable=False, server_default='0', default=False)
api_token_id = db.Column(db.Integer, nullable=True)
deactivated_on = db.Column(db.DateTime(), nullable=True)
is_deactivated = db.Column(db.Boolean(), nullable=False,
server_default='0', default=False)
# Identifies if contact is an Equifax lead
is_eq_lead = db.Column('is_eq_lead', db.Boolean(), nullable=False, server_default='0')
# Relationships
messages = db.relationship(Message, backref="contact")
leads = db.relationship(Lead, backref='contact', passive_deletes=True)
vehicle = db.relationship(ContactVehicle, backref='contact', uselist=False, passive_deletes=True)
form_leads = db.relationship(FormLead, backref='contact', passive_deletes=True)
note_list = db.relationship(ContactNotes, backref="contact")
sms_count = column_property(
select([func.count(Message.id.distinct())]).where(
and_(Message.contact_id == id, Message.partnership_account_id == partnership_account_id)
).label('sms_count'), deferred=True
)
form_count = column_property(
select([func.count(FormLead.id.distinct())]).where(
and_(FormLead.contact_id == id, FormLead.partnership_account_id == partnership_account_id)
).label('form_count'), deferred=True
)
phone_count = column_property(
select([func.count(Lead.id.distinct())]).where(
and_(Lead.contact_id == id, Lead.partnership_account_id == partnership_account_id)
).label('phone_count'), deferred=True
)
sms_source = column_property(
select([func.count(Message.id.distinct())]).where(
and_(Message.contact_id == id, Message.partnership_account_id == partnership_account_id)
).label('sms_source'), deferred=True
)
phone_source = column_property(
select([Lead.id.distinct()]).where(
and_(Lead.contact_id == id, Lead.partnership_account_id == partnership_account_id)
).limit(1).label('phone_source'), deferred=True
)
source = column_property(db.func.concat(
case([(func.lower(external_source_type) == 'api', 'api-')], else_=''),
case([(func.lower(external_source_type) == 'import', 'import-')], else_=''),
case([(func.lower(external_source_type) == 'mobile', 'mobile-')], else_=''),
case([(func.lower(external_source_type) == 'adf_import', 'adf-')], else_=''),
case([(is_eq_lead == '1', 'credit-')], else_=''),
case([(sms_count.expression > 0, 'sms-')
], else_=''),
case([(phone_count.expression > 0, 'phone-')
], else_=''),
case([(form_count.expression > 0, 'form')
], else_=''))
)
credit_score = column_property(select([CreditReports.credit_score]).where(
and_(CreditReports.contact_id == id,
CreditReports.partnership_account_id == partnership_account_id,
CreditReports.is_successful.is_(True))
).order_by(CreditReports.created_on.desc()).limit(1).label('credit_score'))
@hybrid_property
def name(self):
result = 'Unknown'
if self.firstname and self.lastname:
result = "{} {}".format(self.firstname, self.lastname)
elif self.firstname and not self.lastname:
result = self.firstname
elif not self.firstname and not self.lastname and self.caller_id:
result = self.caller_id
return result
@name.expression
def name(cls):
if cls.firstname and cls.lastname:
return cls.firstname + " " + cls.lastname
elif cls.firstname and not cls.lastname:
return cls.firstname
elif not cls.firstname and not cls.lastname and cls.caller_id:
return cls.caller_id
return 'Unknown'
@hybrid_property
def message_name(self):
result = 'Unknown'
if self.firstname and self.lastname:
result = "{} {}".format(self.firstname, self.lastname)
elif self.firstname and not self.lastname:
result = self.firstname
elif not self.firstname and not self.lastname and self.phonenumber_1:
result = self.phonenumber_1
return result
@property
def user_fullname(self):
""" Return the full name of the user that created the note
"""
return self.firstname + ' ' + self.lastname
@property
def total_calls(self):
return select([func.count(Lead.id)]).where(
and_(Lead.contact_id == self.id, Lead.partnership_account_id == self.partnership_account_id)
)
@property
def note_count(self):
""" Return the number of notes for this number
"""
count_result = ContactNotes.query.join(Contact).filter(
self.id == ContactNotes.contact_id,
ContactNotes.is_enabled == True
).count()
return count_result
@property
def created_datetime(self):
""" Return the date/time this contact was created
"""
return self.created_on.strftime('%Y-%m-%d %H:%M:%S')
@property
def updated_datetime(self):
""" Return the date/time this contact was updated
"""
return self.updated_on.strftime('%Y-%m-%d %H:%M:%S')
@property
def deactivated_on_datetime(self):
""" Return the date/time this contact was deactivated on.
"""
if self.deactivated_on is not None and self.deactivated_on is not '':
return self.deactivated_on.strftime('%Y-%m-%d %H:%M:%S')
else:
return ''
@property
def phonenumber(self):
"""Return formatted phonenumber for the contact"""
if self.phonenumber_1:
return format_phone_number_bracket(self.phonenumber_1)
elif self.phonenumber_2:
return format_phone_number_bracket(self.phonenumber_2)
return "unavailable"
@property
def formatted_phonenumber_1(self):
"""Return formatted phonenumber_1 for the contact"""
if self.phonenumber_1:
return format_phone_number_bracket(self.phonenumber_1)
return ""
@property
def formatted_phonenumber_2(self):
"""Return formatted phonenumber_1 for the contact"""
if self.phonenumber_2:
return format_phone_number_bracket(self.phonenumber_2)
return ""
@classmethod
def current_month_equifax_lead_count(cls, paid):
"""
Get equifax lead count for current month
"""
current_year = datetime.now().year
current_month = datetime.now().month
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.is_eq_lead.is_(True),
extract('year', cls.created_on) == current_year,
extract('month', cls.created_on) == current_month)).count()
if count:
return count
else:
return 0
@classmethod
def last_month_equifax_lead_count(cls, paid):
"""
Get equifax lead count for previous month
"""
year = datetime.now().year
last_month = datetime.now().month - 1
if last_month <= 0:
last_month = 12 + last_month
year = year - 1
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.is_eq_lead.is_(True),
extract('year', cls.created_on) == year,
extract('month', cls.created_on) == last_month)).count()
if count:
return count
else:
return 0
@classmethod
def last_two_month_equifax_lead_count(cls, paid):
"""
Get equifax lead count for previous month
"""
year = datetime.now().year
last_month = datetime.now().month - 2
if last_month <= 0:
last_month = 12 + last_month
year = year - 1
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.is_eq_lead.is_(True),
extract('year', cls.created_on) == year,
extract('month', cls.created_on) == last_month)).count()
if count:
return count
else:
return 0
@classmethod
def last_three_month_equifax_lead_count(cls, paid):
"""
Get equifax lead count for previous month
"""
year = datetime.now().year
last_month = datetime.now().month - 3
if last_month <= 0:
last_month = 12 + last_month
year = year - 1
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.is_eq_lead.is_(True),
extract('year', cls.created_on) == year,
extract('month', cls.created_on) == last_month)).count()
if count:
return count
else:
return 0
@classmethod
def today_equifax_lead_count(cls, paid):
"""
Get equifax lead count for current month
"""
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.is_eq_lead.is_(True),
cast(cls.created_on, Date) == date.today())).count()
if count:
return count
else:
return 0
@classmethod
def yesterday_equifax_lead_count(cls, paid):
"""
Get equifax lead count for current month
"""
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.is_eq_lead.is_(True),
cast(cls.created_on, Date) == (date.today() - timedelta(days=1)))).count()
print(count)
if count:
return count
else:
return 0
@classmethod
def day_before_equifax_lead_count(cls, paid):
"""
Get equifax lead count for 2 days back from today
"""
count = cls.query.filter(and_(cls.partnership_account_id == paid,
cls.is_eq_lead.is_(True),
cast(cls.created_on, Date) == (date.today() - timedelta(days=2)))).count()
print(count)
if count:
return count
else:
return 0
@classmethod
def api_create(cls, params):
"""
Create contacts via the API.
:return: int
"""
contact_id = -1
try:
contact = Contact(**params)
contact.api_source = True
db.session.add(contact)
db.session.flush()
contact_id = contact.id
db.session.commit()
except Exception as e:
log.error('Error creating contact. Error: {}'.format(e))
return None
return contact_id
@classmethod
def api_update(cls, partnership_account_id, api_token_id, contact_id,
firstname, lastname, phonenumber, agent_id, birthday,
avatar, large_avatar, email, address_1, address_2, city,
state, country, zip, ip, is_deactivated,
status, is_do_not_call, is_unsubscribe, caller_id):
"""
Update contacts via the API.
:return: bool
"""
result = False
contact = Contact\
.query\
.filter(and_(Contact.partnership_account_id == partnership_account_id, Contact.id == contact_id))\
.first()
if contact is not None:
contact.api_source = True
if firstname is not None and firstname is not '':
contact.firstname = firstname
if lastname is not None and lastname is not '':
contact.lastname = lastname
if phonenumber is not None and phonenumber is not '':
contact.phonenumber_1 = phonenumber
if agent_id is not None:
contact.agent_id = agent_id
if birthday is not None:
contact.birthday = birthday
if avatar is not None:
contact.avatar = avatar
if large_avatar is not None:
contact.large_avatar = large_avatar
if email is not None:
contact.email = email
if address_1 is not None:
contact.address_1 = address_1
if address_2 is not None:
contact.address_2 = address_2
if city is not None:
contact.city = city
if state is not None:
contact.state = state
if country is not None:
contact.country = country
if zip is not None:
contact.zip = zip
if ip is not None:
contact.ip = ip
if is_deactivated is not None:
contact.is_deactivated = is_deactivated
if status is not None:
contact.status = status
if is_do_not_call is not None:
contact.is_do_not_call = is_do_not_call
if is_unsubscribe is not None:
contact.is_unsubscribe = is_unsubscribe
if caller_id is not None:
contact.caller_id = caller_id
contact.api_token_id = api_token_id
db.session.commit()
result = True
return result
@classmethod
def api_deactivate(cls, id, partnership_account_id):
"""
Deactivate contact. The is_active flag will be set to false.
:return: bool
"""
contact = Contact.query\
.filter(and_(Contact.id == id, Contact.partnership_account_id == partnership_account_id))\
.first()
if contact is not None:
contact.is_deactivated = True
contact.deactivated_on = datetime.now()
db.session.commit()
return True
return False
@classmethod
def mobile_api_update(cls, id, partnership_account_id, **kwargs):
"""
Update contacts via the mobile API.
:return: bool
"""
contact = Contact.query.filter(
and_(Contact.id == id, Contact.partnership_account_id == partnership_account_id)
).first()
if contact:
try:
for arg in ['is_unsubscribe', 'is_do_not_call']:
if arg in kwargs.keys():
setattr(contact, arg, True)
del kwargs[arg]
continue
setattr(contact, arg, False)
for key, value in kwargs.items():
setattr(contact, key, value)
contact.deactivated_on = datetime.now()
db.session.commit()
return True
except Exception as e:
print(e)
return False
return False
@classmethod
def update_status(cls, id, status_id, status_text, partnership_account_id, on_contact):
"""
Update the contact status in contacts and leads.
"""
contact = Contact.query\
.filter(and_(Contact.id == id,
Contact.partnership_account_id == partnership_account_id))\
.first()
if contact:
if status_id:
status = Status\
.query\
.filter(Status.id == status_id)\
.first()
if status_text:
status = Status\
.query\
.filter(Status.status == status_text.lower())\
.first()
if status:
if on_contact:
contact.status = status.status
db.session.commit()
elif on_contact is False:
update_stmt = update(Lead).where(Lead.contact_id == id).values(progress_status=status.status)
db.session.execute(update_stmt)
db.session.commit()
elif on_contact is None:
contact.status = status.status
db.session.commit()
update_stmt = update(Lead).where(Lead.contact_id == id).values(progress_status=status.status)
db.session.execute(update_stmt)
db.session.commit()
class ContactReporting(ResourceMixin, db.Model):
__tablename__ = 'contacts_reporting'
id = db.Column(db.Integer, primary_key=True)
city = db.Column(db.String(64), nullable=False, server_default='')
state = db.Column(db.String(64), nullable=False, server_default='')
zip = db.Column(db.String(64), nullable=False, server_default='')
external_source_type = db.Column(db.String(32), nullable=False, server_default='')
partnership_account_id = db.Column(db.Integer, db.ForeignKey(
'partnership_accounts.id', name='contact_reporting_partnership_account_fkey'),
index=True, nullable=False)
is_do_not_call = db.Column('is_do_not_call', db.Boolean(), nullable=False, server_default='0')
is_unsubscribe = db.Column('is_unsubscribe', db.Boolean(), nullable=False, server_default='0')
agent_assigned = db.Column(db.String(128), nullable=True)
status = db.Column(db.String(128), index=True, nullable=True)
bdc_status = db.Column(db.String(128), index=True, nullable=False, server_default='no status')
marketing_source = db.Column(db.String(128), index=True, nullable=False,
server_default='no source')
campaign_id = db.Column(db.Integer, nullable=True)
agent_id = db.Column(db.Integer, nullable=True)
api_source = db.Column(db.Boolean(), nullable=False, server_default='0', default=False)
api_token_id = db.Column(db.Integer, nullable=True)
is_eq_lead = db.Column('is_eq_lead', db.Boolean(), nullable=False, server_default='0')