def find_all_by_store_in_recent_days_and_keywords(cls, store_id, **kwargs):
today = date.today()
filters = [cls.store_id == store_id]
days = kwargs.get('days', None)
if days:
target_day = today + timedelta(days=days)
filters.append(cls.start <= target_day)
active = kwargs.get('active', False)
if active:
filters.append(cls.end >= today)
keywords = kwargs.get('keywords', None)
if keywords:
filters.append(
or_(cls.title.like('%' + keywords + '%'),
cls.content.like('%' + keywords + '%')))
page = kwargs.get('page', DEFAULT_PAGE_START)
per_page = kwargs.get('per_page', DEFAULT_PAGE_SIZE)
criteria = and_(*filters)
query = cls.query.filter(criteria)
sortable_fields = ('start', 'end', 'notify_date')
query_order_fixed = SortMixin.add_order_query(query, cls,
sortable_fields, kwargs)
return query_order_fixed.paginate(page, per_page)
def find_all_by_query_params_in_store(cls, store_id, **kwargs):
query = cls.query.join(Calllog.customer).filter(
and_(cls.store_id == store_id))
if kwargs.get('start_date'):
query = query.filter(
db.func.date(cls.created_on) >= kwargs.get('start_date'))
if kwargs.get('end_date'):
query = query.filter(
db.func.date(cls.created_on) <= kwargs.get('end_date'))
if kwargs.get('sales_filter'):
query = query.filter(
cls.sales_id == int(kwargs.get('sales_filter')))
if kwargs.get('keywords'):
keywords = '%' + kwargs.get('keywords') + '%'
query = query.filter(
or_(cls.mobile.like(keywords), Customer.name.like(keywords)))
page = kwargs.get('page', DEFAULT_PAGE_START)
per_page = kwargs.get('per_page', DEFAULT_PAGE_SIZE)
sortable_fields = ('call_start', 'sales_id', 'customer_id',
'call_start', 'duration')
query_order_fixed = SortMixin.add_order_query(query, cls,
sortable_fields, kwargs)
return query_order_fixed.paginate(page, per_page)
def find_all_by_query_params_in_store(cls, store_id, **kwargs):
query = cls.query.options(joinedload(cls.sales)).filter(
and_(cls.store_id == store_id, cls.status != 'cancelled'))
if kwargs.get('status'):
query = query.filter(cls.status == kwargs.get('status'))
if kwargs.get('start_date'):
if 'delivered' == kwargs.get('status'):
query = query.filter(
and_(
db.func.date(cls.delivered_date) >= kwargs.get(
'start_date')))
else:
query = query.filter(
and_(
db.func.date(cls.created_on) >= kwargs.get(
'start_date')))
if kwargs.get('end_date'):
if 'delivered' == kwargs.get('status'):
query = query.filter(
and_(
db.func.date(cls.delivered_date) <= kwargs.get(
'end_date')))
else:
query = query.filter(
and_(
db.func.date(cls.created_on) <= kwargs.get('end_date'))
)
if kwargs.get('ordered_car_ids'):
query = query.filter(
cls.ordered_car_id == kwargs.get('ordered_car_ids'))
if kwargs.get('sales_id'):
query = query.filter(cls.sales_id == int(kwargs.get('sales_id')))
if kwargs.get('keywords'):
from application.models.customer import Customer
keywords = '%' + kwargs.get('keywords') + '%'
query = query.filter(
or_(cls.order_no.like(keywords),
cls.customer.has(Customer.name.like(keywords)),
cls.sales.has(User.username.like(keywords)),
cls.ordered_car_name.like(keywords),
cls.receipt_title.like(keywords)))
if not kwargs.get('history'):
query = query.filter(cls.history_order == 0)
page = kwargs.get('page', DEFAULT_PAGE_START)
per_page = kwargs.get('per_page', DEFAULT_PAGE_SIZE)
sortable_fields = ('created_on', 'delivered_date', 'status')
query_order_fixed = SortMixin.add_order_query(query, cls,
sortable_fields, kwargs)
return query_order_fixed.paginate(page, per_page)
def find_all_with_last_appt_by_query_params_in_store(cls, store_id, **kwargs):
from application.models.appointment import Appointment
relationship_query = db.session.query(func.max(Appointment.id).label('last_id'),
Appointment.customer_id).filter(
and_(Appointment.remark != None, Appointment.remark != u'', Appointment.store_id == store_id)).group_by(
Appointment.customer_id).subquery()
query = db.session.query(cls, Appointment.remark).options(joinedload(cls.sales)).outerjoin(
relationship_query,
cls.id == relationship_query.columns.customer_id).outerjoin(Appointment,
relationship_query.columns.last_id ==
Appointment.id).filter(
and_(cls.store_id == store_id, cls.status != 'cancelled', cls.status != 'duplicated'))
# hack to support paginate
query.__class__ = BaseQuery
if kwargs.get('intent_level'):
query = query.filter(cls._intent_level == kwargs.get('intent_level'))
if kwargs.get('intent_car_ids'):
query = query.filter(
functions.concat(',', cls.intent_car_ids).like('%,' + kwargs.get('intent_car_ids') + '%'))
if kwargs.get('last_instore', None):
if kwargs.get('last_instore') == 'none' or kwargs.get('last_instore') == '-1':
query = query.filter(cls.last_reception_date == None)
else:
try:
days = int(kwargs.get('last_instore'))
last_instore_date = date.today() - timedelta(days=days)
query = query.filter(db.func.date(cls.last_reception_date) >= last_instore_date)
except:
pass
if kwargs.get('status'):
query = query.filter(cls.status == kwargs.get('status'))
if kwargs.get('keywords'):
keywords = '%' + kwargs.get('keywords') + '%'
query = query.filter(or_(cls.name.like(keywords), cls.mobile.like(keywords)))
if kwargs.get('sales_id'):
query = query.filter(cls.sales_id == int(kwargs.get('sales_id')))
page = kwargs.get('page', DEFAULT_PAGE_START)
per_page = kwargs.get('per_page', DEFAULT_PAGE_SIZE)
sortable_fields = ('sales_id', 'status', '_intent_level', 'last_reception_date')
query_order_fixed = SortMixin.add_order_query(query, cls, sortable_fields, kwargs)
return query_order_fixed.paginate(page, per_page)
def find_all_by_query_params_in_store(cls, store_id, **kwargs):
query = cls.query.filter(and_(cls.store_id == store_id))
if kwargs.get('type_filter'):
query = query.filter(cls.type == kwargs.get('type_filter'))
if kwargs.get('start_date'):
query = query.filter(cls._appt_date >= kwargs.get('start_date'))
if kwargs.get('end_date'):
query = query.filter(cls._appt_date <= kwargs.get('end_date'))
if kwargs.get('status'):
status = kwargs.get('status')
if status == 'closed':
query = query.filter(
or_(
and_(cls.type == 'followup',
cls.status.in_((status, 'cancelled'))),
and_(cls.type != 'followup', cls.status == status)))
elif status == 'cancelled':
query = query.filter(
and_(cls.type != 'followup', cls.status == status))
else:
query = query.filter(cls.status == status)
if kwargs.get('sales_filter'):
query = query.filter(
cls.sales_id == int(kwargs.get('sales_filter')))
page = kwargs.get('page', DEFAULT_PAGE_START)
per_page = kwargs.get('per_page', DEFAULT_PAGE_SIZE)
sortable_fields = ('customer_id', '_appt_datetime', 'type',
'customer_id', 'sales_id')
query_order_fixed = SortMixin.add_order_query(query, cls,
sortable_fields, kwargs)
return query_order_fixed.paginate(page, per_page)