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 campaigns():
current_user = g.user
store_id = get_or_set_store_id()
from application.forms.campaign import CampaignSearchForm
form = CampaignSearchForm(request.args)
type = form.type.data
keywords = form.keywords.data
days = None
active = False
if type == 'active-campaigns':
days = 15
active = True
query = {
'days': days,
'active': active,
'keywords': keywords,
}
sort_params = SortMixin.get_order_query(form)
if sort_params:
query.update(sort_params)
query.update(get_page_info(request))
campaigns = Campaign.find_all_by_store_in_recent_days_and_keywords(
store_id, **query)
return render_template('campaigns/campaigns.html',
selected_menu=CAMPAIGN_MGMT,
form=form,
campaigns=campaigns,
back_endpoint=request.args.get(
'back_endpoint', None))
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 calllogs():
current_user = g.user
store_id = get_or_set_store_id()
from application.forms.calllog import CalllogSearchForm
search_form = CalllogSearchForm(request.args)
search_form.sales_filter.choices = get_sales_selection(store_id)
query = dict()
if search_form.start_date.data and search_form.start_date.data != '':
query['start_date'] = search_form.start_date.data
if search_form.end_date.data and search_form.end_date.data != '':
query['end_date'] = search_form.end_date.data
if search_form.sales_filter.data not in ('None', 'all'):
query['sales_filter'] = search_form.sales_filter.data
if search_form.keywords.data:
query['keywords'] = search_form.keywords.data
sort_params = SortMixin.get_order_query(search_form)
if sort_params:
query.update(sort_params)
query.update(get_page_info(request))
calllogs = Calllog.find_all_by_query_params_in_store(store_id, **query)
return render_template('calllogs/calllogs.html', selected_menu=CALL_LOG, form=search_form,
calllogs=calllogs, back_endpoint=request.args.get('back_endpoint', None))
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 customers():
current_user = g.user
store_id = get_or_set_store_id()
from application.forms.customer import CustomerSearchForm
search_form = CustomerSearchForm(request.args)
search_form.intent_level_filter.choices = get_intent_level_selection(
store_id)
search_form.intent_car_ids_filter.choices = get_intent_car_ids_selection(
store_id)
search_form.last_instore_filter.choices = get_last_instore_selection(
store_id)
search_form.status_filter.choices = get_status_selection()
search_form.sales_filter.choices = get_sales_selection(store_id)
# build query
query = dict()
if search_form.intent_level_filter.data not in ('None', 'all'):
query['intent_level'] = search_form.intent_level_filter.data
if search_form.intent_car_ids_filter.data not in ('None', 'all'):
query['intent_car_ids'] = search_form.intent_car_ids_filter.data
if search_form.last_instore_filter.data not in ('None', 'all'):
query['last_instore'] = search_form.last_instore_filter.data
if search_form.status_filter.data not in ('None', 'all'):
query['status'] = search_form.status_filter.data
if search_form.keywords.data:
query['keywords'] = search_form.keywords.data
if search_form.sales_filter.data not in ('None', 'all'):
query['sales_id'] = search_form.sales_filter.data
if not current_user.has_role_in_current_store(USER_ROLE_STORE_MANAGER):
query.pop('sales_id', None)
# TODO:
scope = current_user.getDataScope('customer', store_id)
if scope:
scope.pop('store_id', None)
query.update(scope)
sort_params = SortMixin.get_order_query(search_form)
if sort_params:
query.update(sort_params)
query.update(get_page_info(request))
customers_list = Customer.find_all_with_last_appt_by_query_params_in_store(
store_id, **query)
return render_template('customers/customers.html',
selected_menu=CUSTOMER_MGMT,
form=search_form,
customers=customers_list,
back_endpoint=request.args.get(
'back_endpoint', None))
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 orders():
store_id = request.args.get('store_id', None)
if not store_id:
store_id = get_or_set_store_id()
from application.forms.order import OrderSearchForm
search_form = OrderSearchForm(request.args)
search_form.ordered_car_ids_filter.choices = get_intent_car_ids_selection(
store_id)
search_form.sales_filter.choices = get_sales_selection(store_id)
search_form.status_filter.choices = get_status_selection()
# build query
query = dict()
if search_form.start_date.data and search_form.start_date.data != '':
query['start_date'] = search_form.start_date.data
if search_form.end_date.data and search_form.end_date.data != '':
query['end_date'] = search_form.end_date.data
if search_form.ordered_car_ids_filter.data not in ('None', 'all'):
query['ordered_car_ids'] = search_form.ordered_car_ids_filter.data
if search_form.sales_filter.data not in ('None', 'all'):
query['sales_id'] = search_form.sales_filter.data
if search_form.status_filter.data not in ('None', 'all'):
query['status'] = search_form.status_filter.data
if search_form.keywords.data:
query['keywords'] = search_form.keywords.data
if search_form.history.data:
query['history'] = search_form.history.data
sort_params = SortMixin.get_order_query(search_form)
if sort_params:
query.update(sort_params)
query.update(get_page_info(request))
cancel_form = OrderCancelForm()
orders_list = Order.find_all_by_query_params_in_store(store_id, **query)
return render_template('orders/orders.html',
selected_menu=ORDERS_MGMT,
form=search_form,
orders=orders_list,
cancel_form=cancel_form,
back_endpoint=request.args.get(
'back_endpoint', None))
def receptions():
current_user = g.user
store_id = get_or_set_store_id()
from application.forms.reception import RxSearchForm
search_form = RxSearchForm(request.args)
search_form.type_filter.choices = get_type_selection()
search_form.sales_filter.choices = get_sales_selection(store_id)
search_form.status_filter.choices = get_status_selection()
query = dict()
if search_form.start_date.data and search_form.start_date.data != '':
query['start_date'] = search_form.start_date.data
if search_form.end_date.data and search_form.end_date.data != '':
query['end_date'] = search_form.end_date.data
if search_form.type_filter.data and search_form.type_filter.data not in (
'None', 'all'):
query['type_filter'] = search_form.type_filter.data
if search_form.sales_filter.data not in ('None', 'all'):
query['sales_filter'] = search_form.sales_filter.data
if search_form.incomplete.data:
query['incomplete'] = search_form.incomplete.data
if search_form.status_filter.data not in ('None', 'all'):
query['status'] = search_form.status_filter.data
sort_params = SortMixin.get_order_query(search_form)
if sort_params:
query.update(sort_params)
query.update(get_page_info(request))
receptions = Reception.find_all_by_query_params_in_store(store_id, **query)
return render_template('receptions/receptions.html',
selected_menu=RX_VIEW,
form=search_form,
receptions=receptions,
back_endpoint=request.args.get(
'back_endpoint', None),
today=datetime.date.today())
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)
def appts():
current_user = g.user
store_id = get_or_set_store_id()
from application.forms.appt import ApptSearchForm
search_form = ApptSearchForm(request.args)
search_form.type_filter.choices = get_type_selection()
search_form.status_filter.choices = get_status_selection()
search_form.sales_filter.choices = get_sales_selection(store_id)
query = dict()
if search_form.start_date.data and search_form.start_date.data != '':
query['start_date'] = search_form.start_date.data
if search_form.end_date.data and search_form.end_date.data != '':
query['end_date'] = search_form.end_date.data
if search_form.type_filter.data and search_form.type_filter.data not in (
'None', 'all'):
query['type_filter'] = search_form.type_filter.data
if search_form.status_filter.data and search_form.status_filter.data not in (
'None', 'all'):
query['status'] = search_form.status_filter.data
if search_form.sales_filter.data and search_form.sales_filter.data not in (
'None', 'all'):
query['sales_filter'] = search_form.sales_filter.data
sort_params = SortMixin.get_order_query(search_form)
if sort_params:
query.update(sort_params)
query.update(get_page_info(request))
appts = Appointment.find_all_by_query_params_in_store(store_id, **query)
return render_template('appts/appts.html',
selected_menu=APPTS_VIEW,
form=search_form,
back_endpoint=request.args.get(
'back_endpoint', None),
appts=appts)