def service_prices_without_future(app, request, regions, services, suppliers):
with app.app_context():
db.session.add(ServiceSubType(id=1, name=''))
db.session.add(ServiceSubType(id=2, name='SubType1'))
db.session.flush()
db.session.add(
ServiceTypePriceCeiling(service_type_id=1,
sub_service_id=1,
region_id=1,
supplier_code=1,
price=321.56))
db.session.flush()
db.session.add(
ServiceTypePrice(service_type_id=1,
sub_service_id=1,
region_id=1,
supplier_code=1,
service_type_price_ceiling_id=1,
price=210.60,
date_from='1/1/2016',
date_to=pendulum.Date.yesterday()))
db.session.add(
ServiceTypePrice(service_type_id=1,
sub_service_id=1,
region_id=1,
supplier_code=1,
service_type_price_ceiling_id=1,
price=200.50,
date_from=pendulum.Date.today(),
date_to='1/1/2050'))
db.session.commit()
yield ServiceTypePrice.query.all()
def service_prices_with_multiple_region(app, request, regions, services,
suppliers, service_type_price_ceiling):
with app.app_context():
db.session.add(
ServiceTypePrice(service_type_id=1,
sub_service_id=1,
region_id=1,
supplier_code=1,
service_type_price_ceiling_id=1,
price=210.60,
date_from='1/1/2016',
date_to=pendulum.Date.today()))
db.session.add(
ServiceTypePrice(service_type_id=1,
sub_service_id=1,
region_id=2,
supplier_code=1,
service_type_price_ceiling_id=2,
price=200.50,
date_from=pendulum.Date.today(),
date_to='1/1/2050'))
db.session.flush()
db.session.commit()
yield ServiceTypePrice.query.all()
def get_prices(self,
code,
service_type_id,
category_id,
date,
region_id=None):
prices = db.session.query(ServiceTypePrice)\
.join(ServiceTypePrice.region)\
.filter(ServiceTypePrice.supplier_code == code,
ServiceTypePrice.service_type_id == service_type_id,
ServiceTypePrice.sub_service_id == category_id,
ServiceTypePrice.is_current_price(date))
if region_id:
prices = prices.filter(ServiceTypePrice.region_id == region_id)
prices = prices.distinct(
Region.state, Region.name, ServiceTypePrice.supplier_code,
ServiceTypePrice.service_type_id, ServiceTypePrice.sub_service_id,
ServiceTypePrice.region_id)\
.order_by(
Region.state, Region.name, ServiceTypePrice.supplier_code.desc(),
ServiceTypePrice.service_type_id.desc(), ServiceTypePrice.sub_service_id.desc(),
ServiceTypePrice.region_id.desc(), ServiceTypePrice.updated_at.desc())\
.all()
return [p.serializable for p in prices]
def filter(service_type_id, region_id):
"""Filter suppliers and prices (role=buyer)
---
tags:
- services
security:
- basicAuth: []
parameters:
- name: service_type_id
in: path
type: integer
required: true
default: all
- name: region_id
in: path
type: integer
required: true
default: all
definitions:
Prices:
type: object
properties:
alert:
schema:
$ref: '#/definitions/Alert'
categories:
type: array
items:
$ref: '#/definitions/SubService'
SubService:
type: object
properties:
name:
type: string
suppliers:
type: array
items:
$ref: '#/definitions/SupplierPrice'
SupplierPrice:
type: object
properties:
email:
type: string
name:
type: string
phone:
type: string
price:
type: string
code:
type: integer
Alert:
type: object
properties:
message:
type: string
type:
type: string
responses:
200:
description: A list of prices
schema:
$ref: '#/definitions/Prices'
"""
service_type = db.session.query(ServiceType).get(service_type_id)
region = db.session.query(Region).get(region_id)
if service_type is None or region is None:
return jsonify(), 404
today = pendulum.today(current_app.config['DEADLINES_TZ_NAME']).date()
prices = db.session.query(ServiceTypePrice, Supplier, ServiceSubType)\
.join(Supplier, ServiceTypePrice.supplier_code == Supplier.code)\
.outerjoin(ServiceSubType, ServiceTypePrice.sub_service_id == ServiceSubType.id)\
.filter(
ServiceTypePrice.service_type_id == service_type_id,
ServiceTypePrice.region_id == region_id,
ServiceTypePrice.is_current_price(today))\
.distinct(ServiceSubType.name, ServiceTypePrice.supplier_code, ServiceTypePrice.service_type_id,
ServiceTypePrice.sub_service_id, ServiceTypePrice.region_id)\
.order_by(ServiceSubType.name, ServiceTypePrice.supplier_code.desc(),
ServiceTypePrice.service_type_id.desc(), ServiceTypePrice.sub_service_id.desc(),
ServiceTypePrice.region_id.desc(), ServiceTypePrice.updated_at.desc())\
.all()
supplier_prices = []
for price, supplier, sub_service in prices:
supplier_prices.append((
None if not sub_service else sub_service.name,
{
'price': '{:1,.2f}'.format(price.price),
'name': supplier.name,
'phone': supplier.data.get('contact_phone', None),
'email': supplier.data.get('contact_email', None),
'code': supplier.code
}
))
result = []
for key, group in groupby(supplier_prices, key=lambda x: x[0]):
result.append(dict(name=key, suppliers=list(s[1] for s in group)))
alert = ALERTS['none'] if len(result) == 0 else ALERTS[service_type.fee_type.lower()]
return jsonify(alert=alert, categories=result), 200