async def get_sample_subs(self, db_connection, user_id, limit, offset):
"""
SELECT pr.name, pr.url, pr.reference, pr.parameters,
sh.name as shop_label,
json_agg(prs.parameters) as selected_options
FROM sub_user as sub
INNER JOIN sub_user_stock_ix as subi ON subi.sub_id = sub.id
INNER JOIN product_stock as prs ON prs.id = subi.stock_id
INNER JOIN product as pr ON pr.id = prs.product_id
INNER JOIN shop as sh ON sh.id = pr.shop_id
WHERE user_id = 1
GROUP BY sub.id, pr.id, sh.id
ORDER BY sub.dt_created DESC
"""
query = sa.select((sub_user.c.id, product.c.name, product.c.url, product.c.reference, product.c.parameters,
shop.c.label.label('shop_label'),
func.json_agg(product_stock.c.parameters).label('selected_options'))
)\
.select_from(sub_user
.join(sub_user_stock_ix, sub_user_stock_ix.c.sub_id == sub_user.c.id)
.join(product_stock, product_stock.c.id == sub_user_stock_ix.c.stock_id)
.join(product, product.c.id == product_stock.c.product_id)
.join(shop, shop.c.id == product.c.shop_id)
)\
.where(sub_user.c.user_id == user_id)\
.group_by(sub_user.c.id, product.c.id, shop.c.id)\
.order_by(sub_user.c.dt_created.desc())\
.limit(limit).offset(offset)
res = await db_connection.execute(query)
records = await res.fetchall()
return [Subscription.from_row(row) for row in records]
def get_suppliers_with_expiring_documents(self, days):
today = datetime.now(pytz.timezone('Australia/Sydney'))
# Find out which of the supplier's documents have expired or are expiring soon
liability = (select([Supplier.code, Supplier.name, literal('liability').label('type'),
Supplier.data['documents']['liability']['expiry'].astext.label('expiry')])
.where(and_(Supplier.data['documents']['liability']['expiry'].isnot(None),
func.to_date(Supplier.data['documents']['liability']['expiry'].astext, 'YYYY-MM-DD') ==
(today.date() + timedelta(days=days)))))
workers = (select([Supplier.code, Supplier.name, literal('workers').label('type'),
Supplier.data['documents']['workers']['expiry'].astext.label('expiry')])
.where(and_(Supplier.data['documents']['workers']['expiry'].isnot(None),
func.to_date(Supplier.data['documents']['workers']['expiry'].astext, 'YYYY-MM-DD') ==
(today.date() + timedelta(days=days)))))
expiry_dates = union(liability, workers).alias('expiry_dates')
# Aggregate the document details so they can be returned with the results
documents = (db.session.query(expiry_dates.columns.code, expiry_dates.columns.name,
func.json_agg(
func.json_build_object(
'type', expiry_dates.columns.type,
'expiry', expiry_dates.columns.expiry)).label('documents'))
.group_by(expiry_dates.columns.code, expiry_dates.columns.name)
.subquery('expired_documents'))
# Find email addresses associated with the supplier
email_addresses = self.get_supplier_contacts_union()
# Aggregate the email addresses so they can be returned with the results
aggregated_emails = (db.session.query(email_addresses.columns.code,
func.json_agg(
email_addresses.columns.email_address
).label('email_addresses'))
.group_by(email_addresses.columns.code)
.subquery())
# Combine the list of email addresses and documents
results = (db.session.query(documents.columns.code, documents.columns.name, documents.columns.documents,
aggregated_emails.columns.email_addresses)
.join(aggregated_emails,
documents.columns.code == aggregated_emails.columns.code)
.order_by(documents.columns.code)
.all())
return [r._asdict() for r in results]
def get_suppliers_with_expiring_documents(self, days):
today = datetime.now(pytz.timezone('Australia/Sydney'))
# Find out which of the supplier's documents have expired or are expiring soon
liability = (select([Supplier.code, Supplier.name, literal('liability').label('type'),
Supplier.data['documents']['liability']['expiry'].astext.label('expiry')])
.where(and_(Supplier.data['documents']['liability']['expiry'].isnot(None),
func.to_date(Supplier.data['documents']['liability']['expiry'].astext, 'YYYY-MM-DD') ==
(today.date() + timedelta(days=days)))))
workers = (select([Supplier.code, Supplier.name, literal('workers').label('type'),
Supplier.data['documents']['workers']['expiry'].astext.label('expiry')])
.where(and_(Supplier.data['documents']['workers']['expiry'].isnot(None),
func.to_date(Supplier.data['documents']['workers']['expiry'].astext, 'YYYY-MM-DD') ==
(today.date() + timedelta(days=days)))))
expiry_dates = union(liability, workers).alias('expiry_dates')
# Aggregate the document details so they can be returned with the results
documents = (db.session.query(expiry_dates.columns.code, expiry_dates.columns.name,
func.json_agg(
func.json_build_object(
'type', expiry_dates.columns.type,
'expiry', expiry_dates.columns.expiry)).label('documents'))
.group_by(expiry_dates.columns.code, expiry_dates.columns.name)
.subquery('expired_documents'))
# Find email addresses associated with the supplier
email_addresses = self.get_supplier_contacts_union()
# Aggregate the email addresses so they can be returned with the results
aggregated_emails = (db.session.query(email_addresses.columns.code,
func.json_agg(
email_addresses.columns.email_address
).label('email_addresses'))
.group_by(email_addresses.columns.code)
.subquery())
# Combine the list of email addresses and documents
results = (db.session.query(documents.columns.code, documents.columns.name, documents.columns.documents,
aggregated_emails.columns.email_addresses)
.join(aggregated_emails,
documents.columns.code == aggregated_emails.columns.code)
.order_by(documents.columns.code)
.all())
return [r._asdict() for r in results]
def _add_relations(query, catalog_name, collection_name):
gob_model = GOBModel()
collection = gob_model.get_collection(catalog_name, collection_name)
has_states = collection.get('has_states', False)
src_table, _ = get_table_and_model(catalog_name, collection_name)
for reference in collection['references']:
relation_name = get_relation_name(gob_model, catalog_name,
collection_name, reference)
if not relation_name:
continue
rel_table, _ = get_table_and_model('rel', relation_name)
select_attrs = [
getattr(rel_table, 'src_id'),
getattr(rel_table, 'src_volgnummer'),
] if has_states else [
getattr(rel_table, 'src_id'),
]
subselect = session \
.query(
*select_attrs,
func.json_agg(
func.json_build_object(
FIELD.SOURCE_VALUE, getattr(rel_table, FIELD.SOURCE_VALUE),
FIELD.REFERENCE_ID, getattr(rel_table, 'dst_id')
)
).label('source_values')
).filter(
and_(
getattr(rel_table, FIELD.DATE_DELETED).is_(None),
or_(
getattr(rel_table, FIELD.EXPIRATION_DATE).is_(None),
getattr(rel_table, FIELD.EXPIRATION_DATE) > func.now()
)
)
).group_by(
*select_attrs
).subquery()
join_clause = [
getattr(src_table, FIELD.ID) == getattr(subselect.c, 'src_id'),
getattr(src_table, FIELD.SEQNR) == getattr(subselect.c,
'src_volgnummer')
] if has_states else [
getattr(src_table, FIELD.ID) == getattr(subselect.c, 'src_id'),
]
query = query.join(subselect, and_(*join_clause), isouter=True) \
.add_columns(
getattr(subselect.c, 'source_values').label(f"ref:{reference}")
)
return query
def _get_permissions_query(self, session, identifier):
"""
select domain, json_agg(parts) as permissions from
(select domain, row_to_json(r) as parts from
(select domain, action, array_agg(distinct target) as target from
(select (case when domain is null then '*' else domain end) as domain,
(case when target is null then '*' else target end) as target,
array_agg(distinct (case when action is null then '*' else action end)) as action
from permission
group by domain, target
) x
group by domain, action)
r) parts
group by domain;
"""
thedomain = case([(Domain.name == None, "*")], else_=Domain.name)
theaction = case([(Action.name == None, "*")], else_=Action.name)
theresource = case([(Resource.name == None, "*")], else_=Resource.name)
action_agg = func.array_agg(theaction.distinct())
stmt1 = (
session.query(
Permission.domain_id,
thedomain.label("domain"),
Permission.resource_id,
theresource.label("resource"),
action_agg.label("action"),
)
.select_from(User)
.join(role_membership_table, User.pk_id == role_membership_table.c.user_id)
.join(role_permission_table, role_membership_table.c.role_id == role_permission_table.c.role_id)
.join(Permission, role_permission_table.c.permission_id == Permission.pk_id)
.outerjoin(Domain, Permission.domain_id == Domain.pk_id)
.outerjoin(Action, Permission.action_id == Action.pk_id)
.outerjoin(Resource, Permission.resource_id == Resource.pk_id)
.filter(User.identifier == identifier)
.group_by(Permission.domain_id, Domain.name, Permission.resource_id, Resource.name)
).subquery()
stmt2 = (
session.query(stmt1.c.domain, stmt1.c.action, func.array_agg(stmt1.c.resource.distinct()).label("resource"))
.select_from(stmt1)
.group_by(stmt1.c.domain, stmt1.c.action)
).subquery()
stmt3 = (
session.query(stmt2.c.domain, func.row_to_json(as_row(stmt2)).label("parts")).select_from(stmt2)
).subquery()
final = (
session.query(stmt3.c.domain, cast(func.json_agg(stmt3.c.parts), Text))
.select_from(stmt3)
.group_by(stmt3.c.domain)
)
return final
async def get_units(conn):
j = join(unit_table, room_table, unit_table.c.id == room_table.c.unit_id)
rooms_agg = func.json_agg(text(room_table.name)).label('rooms')
query = (select((
unit_table,
rooms_agg,
)).select_from(j).group_by(unit_table.c.id).order_by(
unit_table.c.created_at))
res = await conn.execute(query)
return list(map(dict, await res.fetchall()))
def daily_statistics_agg(session):
"""
Aggregate statistics for each day in each year.
Result columns:
- day_of_year, year
- bid
- post_count, edit_count, posts_length, threads_created, active_users
- active_threads: list of dicts of the most active threads (w.r.t. post count) of the day.
Each dict consists of json_thread_columns (tid, [sub]title) plus "thread_post_count".
"""
year = func.extract('year', Post.timestamp).label('year')
cte = aggregate_stats_segregated_by_time(session, func.extract('doy', Post.timestamp), 'day_of_year').subquery()
json_thread_columns = (Thread.tid, Thread.title, Thread.subtitle)
threads_active_during_time = (
session
.query(*json_thread_columns,
func.count(Post.pid).label('thread_post_count'),
func.extract('doy', Post.timestamp).label('doy'),
year,
Thread.bid,
func.row_number().over(
partition_by=tuple_(year, Thread.bid, func.extract('doy', Post.timestamp)),
order_by=tuple_(desc(func.count(Post.pid)), Thread.tid)
).label('rank'))
.join(Post.thread)
.group_by(*json_thread_columns, 'doy', Thread.bid, year)
).subquery('tadt')
active_threads = (
session
.query(threads_active_during_time.c.doy,
threads_active_during_time.c.year,
threads_active_during_time.c.bid,
func.json_agg(column('tadt')).label('active_threads'))
.select_from(threads_active_during_time)
.filter(threads_active_during_time.c.rank <= 5)
.group_by('doy', 'bid', 'year')
.subquery()
)
return (
session
.query(
*cte.c,
active_threads.c.active_threads)
.join(active_threads, and_(active_threads.c.doy == cte.c.day_of_year,
active_threads.c.year == cte.c.year,
active_threads.c.bid == cte.c.bid))
)
async def get_unit_with_rooms(conn, unit_id):
j = join(
unit_table, room_table,
and_(unit_table.c.id == room_table.c.unit_id,
unit_table.c.id == unit_id))
rooms_agg = func.json_agg(text(room_table.name)).label('rooms')
query = (select((
unit_table,
rooms_agg,
)).select_from(j).group_by(unit_table.c.id).order_by(
unit_table.c.created_at))
res = await conn.execute(query)
return dict((await res.first()))
def search_from_user_query(self, query_string):
ar = self.metadata.tables["article"]
re = self.metadata.tables["reference"]
wr = self.metadata.tables["writes"]
au = self.metadata.tables["author"]
da = self.metadata.tables["dataset"]
# Every dataset referenced in a paper for a given author
intermediate_join = (re.join(
ar, re.c.article_id == ar.c.article_id).join(
wr, wr.c.article_id == re.c.article_id).join(
au, au.c.author_id == wr.c.author_id).join(
da, da.c.dataset_id == re.c.dataset_id))
text_search_match_records = (select(
[ar.c.article_id, au.c.author_id,
da.c.dataset_id]).select_from(intermediate_join).where(
func.concat(au.c.author_name, ar.c.article_title,
da.c.dataset_name).ilike(
f"""%{ query_string.replace(" ", "%") }%"""))
).cte("text_search_match_records")
# Join in search result data from IDs in the CTE
resultset_join = (text_search_match_records.join(
ar,
ar.c.article_id == text_search_match_records.c.article_id).join(
wr, wr.c.article_id == ar.c.article_id).join(
au, au.c.author_id == wr.c.author_id).join(
re, re.c.article_id == ar.c.article_id).join(
da, da.c.dataset_id == re.c.dataset_id))
return self.conn.execute(
select([
ar.c.article_title,
func.json_agg(au.c.author_name),
func.json_agg(da.c.dataset_name),
]).group_by(
ar.c.article_title).select_from(resultset_join)).fetchall()
def get_agencies(self):
subquery = (db.session.query(
AgencyDomain.agency_id,
func.json_agg(
func.json_build_object(
'id', AgencyDomain.id, 'domain', AgencyDomain.domain,
'active', AgencyDomain.active)).label('domains')).group_by(
AgencyDomain.agency_id).subquery())
result = (db.session.query(
Agency.id, Agency.name, Agency.domain, Agency.category,
Agency.state, Agency.body_type, Agency.whitelisted,
Agency.reports, subquery.c.domains).join(
subquery,
subquery.c.agency_id == Agency.id).order_by(Agency.name).all())
return [r._asdict() for r in result]
async def get_notice_product_iter(self):
"""
SELECT
sub.user_id, p.id as product_id, p.name as product_name, p.url as product_url,
s.label as shop_label, p.parameters as product_parameters,
json_agg(json_build_object('data', ns.data, 'parameters', ps.parameters, 'discount', ps.discount))
FROM sub_user_stock_ix as subi
INNER JOIN notice_stock as ns ON ns.stock_id = subi.stock_id
INNER JOIN sub_user as sub ON sub.id = subi.sub_id
INNER JOIN product_stock as ps ON ps.id = subi.stock_id
INNER JOIN product as p ON p.id = ps.product_id
INNER JOIN shop as s ON s.id = p.shop_id
GROUP BY sub.user_id, p.id, s.id
ORDER BY sub.user_id, p.id
"""
async with db.engine.acquire() as db_connection:
select_exists = sa.select((sa.exists(sa.select((notice_stock.c.stock_id, ))),))
res = await db_connection.execute(select_exists)
exists = await res.scalar()
if not exists:
return
query = sa.select((
sub_user.c.user_id,
product.c.id.label('product_id'), product.c.name.label('product_name'),
product.c.reference.label('product_reference'), product.c.url.label('product_url'),
product.c.parameters.label('product_parameters'),
func.json_agg(func.json_build_object(
'data', notice_stock.c.data,
'parameters', product_stock.c.parameters,
'discount', product_stock.c.discount,
)).label('notice_data'),
shop.c.label.label('shop_label')
))\
.select_from(
sub_user_stock_ix
.join(notice_stock, notice_stock.c.stock_id == sub_user_stock_ix.c.stock_id)
.join(sub_user, sub_user.c.id == sub_user_stock_ix.c.sub_id)
.join(product_stock, product_stock.c.id == sub_user_stock_ix.c.stock_id)
.join(product, product.c.id == product_stock.c.product_id)
.join(shop, shop.c.id == product.c.shop_id)
)\
.group_by(sub_user.c.user_id, product.c.id, shop.c.id)\
.order_by(sub_user.c.user_id, product.c.id)
async for row in db_connection.execute(query):
yield row
def get_agency(self, agency_id):
subquery = (db.session.query(
AgencyDomain.agency_id,
func.json_agg(
func.json_build_object(
'id', AgencyDomain.id, 'domain', AgencyDomain.domain,
'active', AgencyDomain.active)).label('domains')).group_by(
AgencyDomain.agency_id).subquery())
result = (db.session.query(
Agency.id, Agency.name, Agency.domain, Agency.category,
Agency.state, Agency.body_type, Agency.whitelisted, Agency.reports,
Agency.must_join_team,
subquery.c.domains).join(subquery,
subquery.c.agency_id == Agency.id).filter(
Agency.id == agency_id).one_or_none())
return result._asdict()
def get_suppliers_with_unassessed_domains_and_all_case_studies_rejected(self):
case_study_query = (
db.session.query(
CaseStudy.supplier_code.label('supplier_code'),
CaseStudy.data['service'].astext.label('domain'),
func.count(CaseStudy.id).label('count')
)
.group_by(CaseStudy.supplier_code, CaseStudy.data['service'].astext)
)
subquery = (
case_study_query
.intersect(
case_study_query
.filter(CaseStudy.status == 'rejected')
)
.subquery()
)
results = (
db
.session
.query(
Supplier.id,
Supplier.code,
Supplier.name,
func.json_agg(aggregate_order_by(Domain.name, Domain.name)).label('domains')
)
.join(SupplierDomain, Domain)
.join(subquery, and_(
Supplier.code == subquery.columns.supplier_code,
Domain.name == subquery.columns.domain
))
.filter(
Supplier.status != 'deleted',
Supplier.data['recruiter'].astext.in_(['no', 'both']),
SupplierDomain.status == 'unassessed'
)
.group_by(Supplier.id, Supplier.code, Supplier.name)
.all()
)
return [r._asdict() for r in results]
def get_suppliers_with_unassessed_domains_and_all_case_studies_rejected(self):
case_study_query = (
db.session.query(
CaseStudy.supplier_code.label('supplier_code'),
CaseStudy.data['service'].astext.label('domain'),
func.count(CaseStudy.id).label('count')
)
.group_by(CaseStudy.supplier_code, CaseStudy.data['service'].astext)
)
subquery = (
case_study_query
.intersect(
case_study_query
.filter(CaseStudy.status == 'rejected')
)
.subquery()
)
results = (
db
.session
.query(
Supplier.id,
Supplier.code,
Supplier.name,
func.json_agg(aggregate_order_by(Domain.name, Domain.name)).label('domains')
)
.join(SupplierDomain, Domain)
.join(subquery, and_(
Supplier.code == subquery.columns.supplier_code,
Domain.name == subquery.columns.domain
))
.filter(
Supplier.status != 'deleted',
Supplier.data['recruiter'].astext.in_(['no', 'both']),
SupplierDomain.status == 'unassessed'
)
.group_by(Supplier.id, Supplier.code, Supplier.name)
.all()
)
return [r._asdict() for r in results]
def venues():
# TODO: replace with real venues data.
# num_shows should be aggregated based on number of upcoming shows per venue.
# data=[{
# "city": "San Francisco",
# "state": "CA",
# "venues": [{
# "id": 1,
# "name": "The Musical Hop",
# "num_upcoming_shows": 0,
# }, {
# "id": 3,
# "name": "Park Square Live Music & Coffee",
# "num_upcoming_shows": 1,
# }]
# }, {
# "city": "New York",
# "state": "NY",
# "venues": [{
# "id": 2,
# "name": "The Dueling Pianos Bar",
# "num_upcoming_shows": 0,
# }]
# }]
query = db.session.query(func.json_build_object(
'city', Venue.city,
'state', Venue.state,
'venues', func.json_agg(func.json_build_object(
'id', Venue.id,
'name', Venue.name,
'num_upcoming_shows', len(get_upcoming_or_past_shows(Venue, 'venue_id')['upcoming_shows'])
)
))).group_by(Venue.city, Venue.state).all()
# Remove inner tuples
data = [q[0] for q in query]
return render_template('pages/venues.html', areas=data)
def get_approved_case_studies_by_supplier_code(self, supplier_code,
domain_id):
subquery = (db.session.query(
CaseStudy.id.label('cs_id'),
CaseStudy.data.label('case_study_data'),
Domain.name.label('category_name')).join(
Domain,
Domain.name == CaseStudy.data['service'].astext).filter(
CaseStudy.supplier_code == supplier_code,
CaseStudy.status == 'approved',
Domain.id == domain_id).subquery())
result = (db.session.query(
subquery.c.category_name,
func.json_agg(
func.json_build_object(
'id', subquery.c.cs_id, 'data',
subquery.c.case_study_data)).label('cs_data')).group_by(
subquery.c.category_name))
results = result.one_or_none()
return results._asdict() if results else {}
def get_mapped_tasks_by_user(project_id: int):
""" Gets all mapped tasks for supplied project grouped by user"""
results = (
db.session.query(
User.username,
User.mapping_level,
func.count(distinct(Task.id)),
func.json_agg(distinct(Task.id)),
func.max(TaskHistory.action_date),
User.date_registered,
User.last_validation_date,
)
.filter(Task.project_id == TaskHistory.project_id)
.filter(Task.id == TaskHistory.task_id)
.filter(Task.mapped_by == User.id)
.filter(Task.project_id == project_id)
.filter(Task.task_status == 2)
.filter(TaskHistory.action_text == "MAPPED")
.group_by(
User.username,
User.mapping_level,
User.date_registered,
User.last_validation_date,
)
)
mapped_tasks_dto = MappedTasks()
for row in results:
user_mapped = MappedTasksByUser()
user_mapped.username = row[0]
user_mapped.mapping_level = MappingLevel(row[1]).name
user_mapped.mapped_task_count = row[2]
user_mapped.tasks_mapped = row[3]
user_mapped.last_seen = row[4]
user_mapped.date_registered = row[5]
user_mapped.last_validation_date = row[6]
mapped_tasks_dto.mapped_tasks.append(user_mapped)
return mapped_tasks_dto
def get_approved_evidence(self, evidence_id):
category_name_max_daily_rate = (db.session.query(
Domain.name.label('category'),
Evidence.data['maxDailyRate'].label('maxDailyRate')).join(
Evidence, Evidence.domain_id == Domain.id).filter(
Evidence.id == evidence_id).subquery())
evidence_domain_criteria = (db.session.query(
Evidence.id.label('evidence_id'),
func.json_array_elements_text(
Evidence.data['criteria']).label('domain_criteria_id')).filter(
Evidence.id == evidence_id).subquery())
subquery = (db.session.query(
evidence_domain_criteria.c.domain_criteria_id,
DomainCriteria.name.label('dc_name'), Evidence.data['evidence'][
evidence_domain_criteria.c.domain_criteria_id].label(
'evidence_data')).join(
DomainCriteria,
DomainCriteria.id == evidence_domain_criteria.c.
domain_criteria_id.cast(Integer)).filter(
Evidence.id == evidence_id).subquery())
evidence_data = (db.session.query(
category_name_max_daily_rate.c.category,
func.json_agg(
func.json_build_object(
'dc_id', subquery.c.domain_criteria_id,
'domain_criteria_name', subquery.c.dc_name,
'evidence_data',
subquery.c.evidence_data)).label('evidence')).group_by(
category_name_max_daily_rate.c.category).subquery())
result = (db.session.query(category_name_max_daily_rate.c.category,
category_name_max_daily_rate.c.maxDailyRate,
evidence_data.c.evidence))
results = result.one_or_none()
return results._asdict() if results else {}
async def get_sub_by_id(self, db_connection, sub_id, user_id):
query = sa.select((sub_user.c.id, sub_user.c.user_id,
product.c.name, product.c.url, product.c.reference, product.c.parameters,
shop.c.label.label('shop_label'),
func.json_agg(product_stock.c.parameters).label('selected_options'))
)\
.select_from(sub_user
.join(sub_user_stock_ix, sub_user_stock_ix.c.sub_id == sub_user.c.id)
.join(product_stock, product_stock.c.id == sub_user_stock_ix.c.stock_id)
.join(product, product.c.id == product_stock.c.product_id)
.join(shop, shop.c.id == product.c.shop_id)
)\
.where(sub_user.c.id == sub_id)\
.group_by(sub_user.c.id, product.c.id, shop.c.id)
res = await db_connection.execute(query)
row = await res.first()
if not row:
raise ObjectDoesNotExist
if row.user_id != user_id:
raise PermissionDenied
return Subscription.from_row(row)
def get_suppliers(self):
subquery = (
db
.session
.query(
SupplierDomain.supplier_id,
func.json_agg(
func.json_build_object(
'category', Domain.name,
'status', SupplierDomain.status,
'price_status', SupplierDomain.price_status
)
).label('categories')
)
.join(Domain)
.group_by(SupplierDomain.supplier_id)
.subquery()
)
result = (
db
.session
.query(
Supplier.code,
Supplier.name,
Supplier.abn,
Supplier.status,
Supplier.creation_time,
Supplier.data['seller_type']['sme'].astext.label('sme'),
subquery.columns.categories
)
.join(subquery, Supplier.id == subquery.columns.supplier_id)
.order_by(Supplier.code)
.all()
)
return [r._asdict() for r in result]
def get_observations_by_user_id(user_id):
try:
observations = (db.session.query(
ObservationModel,
ProgramsModel,
UserModel.username,
func.json_agg(
func.json_build_array(MediaModel.filename,
MediaModel.id_media)).label("images"),
LAreas.area_name,
LAreas.area_code,
).filter(ObservationModel.id_role == user_id).join(
LAreas,
LAreas.id_area == ObservationModel.municipality,
isouter=True).join(
ProgramsModel,
ProgramsModel.id_program == ObservationModel.id_program,
isouter=True,
full=True,
).join(
ObservationMediaModel,
ObservationMediaModel.id_data_source ==
ObservationModel.id_observation,
isouter=True,
).join(
MediaModel,
ObservationMediaModel.id_media == MediaModel.id_media,
isouter=True,
).join(UserModel,
ObservationModel.id_role == UserModel.id_user,
full=True).group_by(
ObservationModel.id_observation,
ProgramsModel.id_program,
UserModel.username,
LAreas.area_name,
LAreas.area_code,
))
observations = observations.order_by(
desc(ObservationModel.timestamp_create))
# current_app.logger.debug(str(observations))
observations = observations.all()
try:
if current_app.config.get("API_TAXHUB") is not None:
taxon_repository = []
taxhub_list_id = []
for observation in observations:
if observation.ProgramsModel.taxonomy_list not in taxhub_list_id:
taxhub_list_id.append(
observation.ProgramsModel.taxonomy_list)
for tax_list in taxhub_list_id:
taxon_repository.append(mkTaxonRepository(tax_list))
features = []
except Exception as e:
return {"message": str(e)}, 500
for observation in observations:
feature = get_geojson_feature(observation.ObservationModel.geom)
feature["properties"]["municipality"] = {
"name": observation.area_name,
"code": observation.area_code,
}
# Observer
feature["properties"]["observer"] = {
"username": observation.username
}
# Observer submitted media
feature["properties"]["image"] = ("/".join([
"/api",
current_app.config["MEDIA_FOLDER"],
observation.images[0][0],
]) if observation.images and observation.images != [[None, None]]
else None)
# Photos
feature["properties"]["photos"] = [{
"url":
"/media/{}".format(filename),
"id_media":
id_media
} for filename, id_media in observation.images
if id_media is not None]
# Municipality
observation_dict = observation.ObservationModel.as_dict(True)
for k in observation_dict:
if k in obs_keys and k != "municipality":
feature["properties"][k] = observation_dict[k]
# Program
program_dict = observation.ProgramsModel.as_dict(True)
for program in program_dict:
if program == "title":
feature["properties"]["program_title"] = program_dict[
program]
# TaxRef
if current_app.config.get("API_TAXHUB") is None:
taxref = Taxref.query.filter(Taxref.cd_nom == observation.
ObservationModel.cd_nom).first()
if taxref:
feature["properties"]["taxref"] = taxref.as_dict(True)
medias = TMedias.query.filter(TMedias.cd_ref == observation.
ObservationModel.cd_nom).all()
if medias:
feature["properties"]["medias"] = [
media.as_dict(True) for media in medias
]
else:
try:
for taxon_rep in taxon_repository:
for taxon in taxon_rep:
if (taxon["taxref"]["cd_nom"] ==
observation.ObservationModel.cd_nom):
feature["properties"]["nom_francais"] = taxon[
"nom_francais"]
feature["properties"]["taxref"] = taxon[
"taxref"]
feature["properties"]["medias"] = taxon[
"medias"]
except StopIteration:
pass
features.append(feature)
return FeatureCollection(features), 200
except Exception as e:
raise e
current_app.logger.critical("[get_program_observations] Error: %s",
str(e))
return {"message": str(e)}, 400
def json_agg(table):
return func.json_agg(literal_column('"' + table.name + '"'))
def listing(self,
text_query=None,
type=None,
start_date=None,
end_date=None,
contributor_person_ids=None,
contributor_group_ids=None,
affiliation_group_ids=None,
related_work_ids=None,
offset=0,
limit=100,
order_by=None,
principals=None):
selected_work_ids = None
if contributor_person_ids:
query = self.session.query(Contributor.work_id.label('id'))
query = query.filter(sql.or_(*[Contributor.person_id == pid
for pid in contributor_person_ids]))
query = query.group_by(Contributor.work_id)
selected_work_ids = query.cte('selected_work_ids')
elif contributor_group_ids:
query = self.session.query(Contributor.work_id.label('id'))
query = query.filter(sql.or_(*[Contributor.group_id == gid
for gid in contributor_group_ids]))
query = query.group_by(Contributor.work_id)
selected_work_ids = query.cte('selected_work_ids')
elif affiliation_group_ids:
query = self.session.query(Affiliation.work_id.label('id'))
query = query.filter(sql.or_(*[Affiliation.group_id == gid
for gid in affiliation_group_ids]))
query = query.group_by(Affiliation.work_id)
selected_work_ids = query.cte('selected_work_ids')
elif related_work_ids:
query = self.session.query(Relation.work_id.label('id'))
query = query.filter(sql.or_(*[Relation.target_id == wid
for wid in related_work_ids]))
query = query.group_by(Relation.work_id)
selected_work_ids = query.cte('selected_work_ids')
work_query = self.session.query(Work.id)
if selected_work_ids is not None:
work_query = work_query.join(
selected_work_ids, selected_work_ids.c.id == Work.id)
acl_filters = self.acl_filters(principals)
if acl_filters:
group_filters = [f for f in acl_filters
if f.left.table.name == 'affiliations']
person_filters = [f for f in acl_filters
if f.left.table.name == 'contributors']
if group_filters:
query = self.session.query(Affiliation.work_id.label('id'))
query = query.filter(sql.or_(*group_filters))
query = query.group_by(Affiliation.work_id)
allowed_work_ids = query.cte('allowed_work_ids')
allowed_group_query = query
if person_filters:
query = self.session.query(Contributor.work_id.label('id'))
query = query.filter(sql.or_(*person_filters))
query = query.group_by(Contributor.work_id)
allowed_work_ids = query.cte('allowed_work_ids')
allowed_person_query = query
if group_filters and person_filters:
query = allowed_group_query.union(
allowed_person_query).group_by('id')
allowed_work_ids = query.cte('allowed_work_ids')
work_query = work_query.join(
allowed_work_ids, allowed_work_ids.c.id == Work.id)
if start_date or end_date:
duration = DateInterval([start_date, end_date])
work_query = work_query.filter(Work.during.op('&&')(duration))
if text_query:
work_query = work_query.filter(
Work.title.ilike('%%%s%%' % text_query))
if type:
work_query = work_query.filter(Work.type == type)
total = work_query.count()
work_query = work_query.order_by(order_by or Work.issued.desc())
work_query = work_query.limit(limit).offset(offset)
filtered_work_ids = work_query.cte('filtered_work_ids')
listed_works = self.session.query(
Work.id.label('id'),
Work.type.label('type'),
Work.issued.label('issued'),
Work.title).join(
filtered_work_ids,
filtered_work_ids.c.id == Work.id).cte('listed_works')
Target = aliased(Work)
full_listing = self.session.query(
listed_works,
func.json_agg(
func.json_build_object('id', Contributor.id,
'position', Contributor.position,
'name', Person.name,
'person_id', Person.id,
'initials', Person.initials,
'prefix', Person.family_name_prefix,
'given_name', Person.given_name,
'family_name', Person.family_name,
'description', Contributor.description,
'group_id', Contributor.group_id,
'role', Contributor.role)
).label('contributors'),
func.json_agg(
func.json_build_object('id', Relation.id,
'relation_type', Relation.type,
'type', Target.type,
'location', Relation.location,
'starting', Relation.starting,
'ending', Relation.ending,
'volume', Relation.volume,
'issue', Relation.issue,
'number', Relation.number,
'title', Target.title)
).label('relations'),
func.array_agg(
sql.distinct(
func.concat(Group.id,
':',
Group.name))).label('affiliations')
)
full_listing = full_listing.outerjoin(
Contributor, listed_works.c.id == Contributor.work_id).outerjoin(
Person, Person.id == Contributor.person_id)
full_listing = full_listing.outerjoin(
Affiliation,
Contributor.id == Affiliation.contributor_id).outerjoin(
Group, Group.id == Affiliation.group_id)
full_listing = full_listing.outerjoin(
Relation, listed_works.c.id == Relation.work_id).outerjoin(
Target, Target.id == Relation.target_id)
full_listing = full_listing.group_by(listed_works).order_by(
listed_works.c.issued.desc())
hits = []
contributor_role_ids = set(contributor_person_ids or [])
for hit in full_listing.all():
aff_labels = dict([tuple(a.split(':', 1)) for a in hit.affiliations])
contributors = []
roles = set()
# filter out contributor rows with a null id.
# this happens with course relations
contributor_rows = [c for c in hit.contributors if c.get('id')]
contributor_rows.sort(key=itemgetter('position'))
for contributor in contributor_rows:
if contributor['person_id'] in contributor_role_ids:
roles.add(contributor['role'])
if (contributors and
contributors[-1]['position'] == contributor['position']):
# same contributor as previous one, (but other affiliation
# it's hard to remove this with a distinct clause
# in the json agg, so we remove it here
continue
cg_id = contributor['group_id']
if cg_id and str(cg_id) in aff_labels:
# a group contributor is always added as an affiliation
contributor['group_name'] = aff_labels[str(cg_id)]
contributors.append(contributor)
affiliations = []
for affiliation in hit.affiliations:
id, name = affiliation.split(':', 1)
affiliations.append(dict(id=id, name=name))
hits.append({'id': hit.id,
'title': hit.title,
'type': hit.type,
'roles': list(roles),
'issued': hit.issued.strftime('%Y-%m-%d'),
'relations': hit.relations,
'affiliations': affiliations,
'contributors': contributors})
return {'total': total,
'hits': hits,
'limit': limit,
'offset': offset}
