def _count_omics_processing_summary(
self, db: Session, conditions: List[ConditionSchema]) -> Query:
subquery = OmicsProcessingQuerySchema(
conditions=conditions).query(db).subquery()
query = (db.query(
models.OmicsProcessing.annotations["omics_type"].astext.label(
"omics_processing_type"),
func.count(
models.OmicsProcessing.id).label("omics_processing_count"),
models.OmicsProcessing.study_id.label(
"omics_processing_study_id_sub"),
).join(subquery, subquery.c.id == models.OmicsProcessing.id).filter(
models.OmicsProcessing.annotations["omics_type"] != None).group_by(
models.OmicsProcessing.study_id,
models.OmicsProcessing.annotations["omics_type"].astext,
)).subquery()
return db.query(
func.jsonb_agg(
func.jsonb_build_object(
"type",
query.c.omics_processing_type,
"count",
query.c.omics_processing_count,
)).label("omics_processing_summary"),
query.c.omics_processing_study_id_sub.label(
"omics_processing_study_id"),
).group_by(query.c.omics_processing_study_id_sub)
def _get_datasets_geojson(self, where_clause):
return self._engine.execute(
select([
func.jsonb_build_object(
'type', 'FeatureCollection',
'features',
func.jsonb_agg(
func.jsonb_build_object(
# TODO: move ID to outer id field?
'type', 'Feature',
'geometry', func.ST_AsGeoJSON(
func.ST_Transform(
DATASET_SPATIAL.c.footprint,
self._target_srid(),
)).cast(postgres.JSONB),
'properties', func.jsonb_build_object(
'id', DATASET_SPATIAL.c.id,
# TODO: dataset label?
'region_code', DATASET_SPATIAL.c.region_code.cast(String),
'creation_time', DATASET_SPATIAL.c.creation_time,
'center_time', DATASET_SPATIAL.c.center_time,
),
)
)
).label('datasets_geojson')
]).where(where_clause)
).fetchone()['datasets_geojson']
def get(self, bag):
query = g.tran.query(db.Companies) \
.filter_by(_deleted='infinity', _id=bag['id'])
doc_vars = vars(db.Companies)
for var in doc_vars:
if isinstance(doc_vars[var], InstrumentedAttribute):
query = query.add_column(doc_vars[var])
if 'with_related' in bag and bag['with_related'] is True:
company_status_value = g.tran.query(
func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='company_status') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Companies.company_status, TEXT)) \
.as_scalar().label('company_status_value')
company_type_value = g.tran.query(
func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='company_type') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Companies.company_type, TEXT)) \
.as_scalar().label('company_type_value')
entry_user = g.tran.query(func.json_build_object(
"id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date,
"data", db.User.data, "role", db.User.role)).select_from(db.User).filter_by(
id=db.Companies.entry_user_id) \
.as_scalar().label('entry_user')
typeofownership = g.tran.query(func.row_to_json(text('typeofownership.*'))).select_from(db.Typeofownership) \
.filter_by(_deleted='infinity', _id=db.Companies.typeofownership_id).as_scalar() \
.label('typeofownership')
dircountry = g.tran.query(func.row_to_json(text('dircountry.*'))).select_from(db.DirCountry) \
.filter_by(_deleted='infinity', _id=db.Companies.dircountry_id).as_scalar() \
.label('dircountry')
dircoate = g.tran.query(func.row_to_json(text('dircoate.*'))).select_from(db.DirCoate) \
.filter_by(_deleted='infinity', _id=db.Companies.dircoate_id).as_scalar() \
.label('dircoate')
roles = g.tran.query(func.jsonb_agg(func.row_to_json(text('roles.*')))).select_from(db.Roles) \
.filter_by(_deleted='infinity') \
.filter(type_coerce(db.Companies.roles_id).has_any(array([db.Roles._id]))) \
.as_scalar().label('roles')
company_users = g.tran.query(db.Companyemployees.user_id).filter_by(_deleted='infinity',
company_id=bag['id']).all()
company_users = []
for user_id in company_users:
user = g.tran.query(func.json_build_object(
"id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date,
"data", db.User.data, "role", db.User.role)).select_from(db.User) \
.filter_by(id=user_id).first()
company_users.append(user)
query = query.add_columns(company_status_value, company_type_value, entry_user, company_users, typeofownership,
dircountry, roles, dircoate)
company = query.one()
return {'doc': orm_to_json(company)}
def get_routes(self, request_id):
source_port = aliased(Airport, name='src')
destination = aliased(Airport, name='dst')
itineraries = db.query(Itinerary,
func.jsonb_agg(func.jsonb_build_array(
source_port.iata,
destination.iata)).label('route')) \
.select_from(Itinerary) \
.outerjoin(onward_trip, Itinerary.onward_trip) \
.outerjoin(Flight, onward_trip.flights) \
.join(source_port, Flight.source) \
.join(destination, Flight.destination) \
.group_by(Itinerary.id)
return [
tuple(tuple(v) for v in x[0]) for x in db.query(
itineraries.selectable.alias().c.route).distinct()
]
def connection_block(field: ASTNode,
parent_name: typing.Optional[str]) -> Alias:
return_type = field.return_type
sqla_model = return_type.sqla_model
block_name = slugify_path(field.path)
if parent_name is None:
join_conditions = [True]
else:
join_conditions = to_join_clause(field, parent_name)
filter_conditions = to_conditions_clause(field)
limit = to_limit(field)
has_total = check_has_total(field)
is_page_after = "after" in field.args
is_page_before = "before" in field.args
totalCount_alias = field.get_subfield_alias(["totalCount"])
edges_alias = field.get_subfield_alias(["edges"])
node_alias = field.get_subfield_alias(["edges", "node"])
cursor_alias = field.get_subfield_alias(["edges", "cursor"])
pageInfo_alias = field.get_subfield_alias(["pageInfo"])
hasNextPage_alias = field.get_subfield_alias(["pageInfo", "hasNextPage"])
hasPreviousPage_alias = field.get_subfield_alias(
["pageInfo", "hasPreviousPage"])
startCursor_alias = field.get_subfield_alias(["pageInfo", "startCursor"])
endCursor_alias = field.get_subfield_alias(["pageInfo", "endCursor"])
# Apply Filters
core_model = sqla_model.__table__
core_model_ref = (
select(core_model.c).select_from(core_model).where(
and_(
# Join clause
*join_conditions,
# Conditions
*filter_conditions,
))).alias(block_name)
new_edge_node_selects = []
new_relation_selects = []
for subfield in get_edge_node_fields(field):
# Does anything other than NodeID go here?
if subfield.return_type == ID:
# elem = select([to_node_id_sql(sqla_model, core_model_ref)]).label(subfield.alias)
elem = to_node_id_sql(sqla_model,
core_model_ref).label(subfield.alias)
new_edge_node_selects.append(elem)
elif isinstance(subfield.return_type,
(ScalarType, CompositeType, EnumType)):
col_name = field_name_to_column(sqla_model, subfield.name).name
elem = core_model_ref.c[col_name].label(subfield.alias)
new_edge_node_selects.append(elem)
else:
elem = build_relationship(subfield, block_name)
new_relation_selects.append(elem)
# Setup Pagination
args = field.args
after_cursor = args.get("after", None)
before_cursor = args.get("before", None)
first = args.get("first", None)
last = args.get("last", None)
if first is not None and last is not None:
raise ValueError('only one of "first" and "last" may be provided')
pkey_cols = get_primary_key_columns(sqla_model)
if after_cursor or before_cursor:
local_table_name = get_table_name(field.return_type.sqla_model)
cursor_table_name = before_cursor.table_name if before_cursor else after_cursor.table_name
cursor_values = before_cursor.values if before_cursor else after_cursor.values
if after_cursor is not None and before_cursor is not None:
raise ValueError(
'only one of "before" and "after" may be provided')
if after_cursor is not None and last is not None:
raise ValueError(
'"after" is not compatible with "last". Use "first"')
if before_cursor is not None and first is not None:
raise ValueError(
'"before" is not compatible with "first". Use "last"')
if cursor_table_name != local_table_name:
raise ValueError("Invalid cursor for entity type")
pagination_clause = tuple_(
*[core_model_ref.c[col.name] for col in pkey_cols]).op(
">" if after_cursor is not None else "<")(
tuple_(*[cursor_values[col.name] for col in pkey_cols]))
else:
pagination_clause = True
order_clause = [
asc(core_model_ref.c[col.name])
for col in get_primary_key_columns(sqla_model)
]
reverse_order_clause = [
desc(core_model_ref.c[col.name])
for col in get_primary_key_columns(sqla_model)
]
total_block = (select([func.count(ONE).label("total_count")]).select_from(
core_model_ref.alias()).where(has_total)).alias(block_name + "_total")
node_id_sql = to_node_id_sql(sqla_model, core_model_ref)
cursor_sql = to_cursor_sql(sqla_model, core_model_ref)
# Select the right stuff
p1_block = (
select([
*new_edge_node_selects,
*new_relation_selects,
# For internal Use
node_id_sql.label("_nodeId"),
cursor_sql.label("_cursor"),
# For internal Use
func.row_number().over().label("_row_num"),
]).select_from(core_model_ref).where(pagination_clause).order_by(
*(reverse_order_clause if
(is_page_before or last is not None) else order_clause),
*order_clause).limit(cast(limit + 1,
Integer()))).alias(block_name + "_p1")
# Drop maybe extra row
p2_block = (select([
*p1_block.c,
(func.max(p1_block.c._row_num).over() > limit).label("_has_next_page")
]).select_from(p1_block).limit(limit)).alias(block_name + "_p2")
ordering = (desc(literal_column("_row_num")) if
(is_page_before or last is not None) else asc(
literal_column("_row_num")))
p3_block = (select(p2_block.c).select_from(p2_block).order_by(ordering)
).alias(block_name + "_p3")
final = (select([
func.jsonb_build_object(
literal_string(totalCount_alias),
func.coalesce(func.min(total_block.c.total_count), ZERO)
if has_total else None,
literal_string(pageInfo_alias),
func.jsonb_build_object(
literal_string(hasNextPage_alias),
func.coalesce(
func.array_agg(p3_block.c._has_next_page)[ONE], FALSE),
literal_string(hasPreviousPage_alias),
TRUE if is_page_after else FALSE,
literal_string(startCursor_alias),
func.array_agg(p3_block.c._nodeId)[ONE],
literal_string(endCursor_alias),
func.array_agg(p3_block.c._nodeId)[func.array_upper(
func.array_agg(p3_block.c._nodeId), ONE)],
),
literal_string(edges_alias),
func.coalesce(
func.jsonb_agg(
func.jsonb_build_object(
literal_string(cursor_alias),
p3_block.c._nodeId,
literal_string(node_alias),
func.cast(
func.row_to_json(literal_column(p3_block.name)),
JSONB()),
)),
func.cast(literal("[]"), JSONB()),
),
).label("ret_json")
]).select_from(p3_block).select_from(
total_block if has_total else select([1]).alias())).alias()
return final
def view_listing(bag):
product = g.tran.query(db.Products._id).filter_by(_deleted='infinity')
if g.company.company_type == 'agent':
insurance_company = g.tran.query(db.Companies).filter_by(_deleted='infinity') \
.filter(db.Companies.agents_id.contains(type_coerce([g.company._id], JSONB))).first()
product = product.filter(
or_(db.Products.company_id == insurance_company._id,
db.Products.company_id == g.company._id))
else:
product = g.tran.query(db.Products._id).filter_by(
company_id=g.company._id, _deleted='infinity')
for col in db.Products.__table__.c:
product = product.add_column(col)
company = g.tran.query(func.jsonb_agg(
func.jsonb_build_object(
'_id', db.Companies._id,
'name', db.Companies.name
)
)).select_from(db.Companies) \
.filter_by(_deleted='infinity').filter(db.Companies._id == db.Products.company_id).as_scalar() \
.label('company')
factor = g.tran.query(func.jsonb_agg(func.row_to_json(text('factors.*')))) \
.select_from(db.Factors) \
.filter_by(_deleted='infinity').filter(db.Factors._id == db.ProductsFactor.factor_id).as_scalar() \
.label('factor')
productsfactor = g.tran.query(func.jsonb_agg(
func.jsonb_build_object(
'_id', db.ProductsFactor._id,
'product_id', db.ProductsFactor.product_id,
'factor_id', db.ProductsFactor.factor_id,
'values', db.ProductsFactor.values,
'coefficient', db.ProductsFactor.coefficient,
'coefficient_type', db.ProductsFactor.coefficient_type,
'is_default', db.ProductsFactor.is_default,
'factor', factor
)
)).select_from(db.ProductsFactor) \
.filter_by(_deleted='infinity').filter(db.ProductsFactor.product_id == db.Products._id)
basefactors = g.tran.query(func.jsonb_agg(func.row_to_json(text('productsbasefactor.*')))) \
.select_from(db.ProductsBaseFactor) \
.filter_by(_deleted='infinity').filter(db.ProductsBaseFactor.product_id == db.Products._id).as_scalar() \
.label('basefactors')
productsfactor = productsfactor.as_scalar() \
.label('factors')
product = product.add_columns(productsfactor, basefactors, company)
product = orm_to_json(product.first())
countries = orm_to_json(
g.tran.query(db.Countries).filter_by(_deleted='infinity').all())
currencies = orm_to_json(
g.tran.query(db.Currencies).filter_by(_deleted='infinity').all())
return {
'product': product,
'countries': countries,
'currencies': currencies
}
def listing(bag):
table_name = bag["type"]
table = getattr(db, table_name) if hasattr(db, table_name) else None
if table is None or not issubclass(table, (db.Base, db.CouchSync)):
raise CbsException(TABLE_NOT_FOUND)
query = g.tran.query(table._id).filter_by(_deleted='infinity')
doc_vars = vars(table)
for var in doc_vars:
if isinstance(doc_vars[var], InstrumentedAttribute):
query = query.add_column(doc_vars[var])
if table == db.Menus:
menus = []
if hasattr(g, 'user'):
if g.user.role == 10:
if 'all' in bag:
menus = g.tran.query(
db.Menus).filter_by(_deleted='infinity').all()
else:
menus = g.tran.query(db.Menus).filter_by(_deleted='infinity') \
.filter(and_(db.Menus.role == 10, db.Menus.active)).all()
return {"docs": menus, "count": len(menus)}
if g.user.role < 10 and g.user.role != 1:
menus_id = []
roles = g.tran.query(db.Roles).filter_by(_deleted='infinity') \
.filter(db.Roles.roletype == g.user.roleType['roleType']).all()
if roles:
for role in roles:
menus_id.extend(role.menus_id)
menus = g.tran.query(db.Menus).filter_by(_deleted='infinity') \
.filter(and_(db.Menus._id.in_(menus_id), db.Menus.active)).all()
return {"docs": menus, "count": len(menus)}
elif g.user.role == 1:
menus_id = []
roles = g.tran.query(db.Roles).filter_by(_deleted='infinity') \
.filter(db.Roles._id.in_(g.user.roles_id if g.user.roles_id is not None else [])).all()
if roles:
for role in roles:
menus_id.extend(role.menus_id)
menus = g.tran.query(db.Menus).filter_by(_deleted='infinity') \
.filter(and_(db.Menus._id.in_(menus_id), db.Menus.active)).all()
return {"docs": menus, "count": len(menus)}
else:
menus = g.tran.query(db.Menus).filter_by(_deleted='infinity') \
.filter(and_(db.Menus.role == 0, db.Menus.active)).all()
return {"docs": menus, "count": len(menus)}
if table == db.Companies:
if hasattr(g, 'user') and g.user.role != 1 and g.user.role != 10:
comps_id = []
user_empl = g.tran.query(db.Companyemployees).filter(
db.Companyemployees.user_id == g.user.id).all()
if user_empl:
for uc in user_empl:
comps_id.append(uc.company_id)
user_company = g.tran.query(db.Companies).filter_by(
_deleted='infinity', user_id=g.user.id).all()
if user_company:
for co in user_company:
comps_id.append(co._id)
query = query.filter(db.Companies._id.in_(comps_id))
if 'current' in bag and bag['current'] is True and hasattr(
g, 'company'):
query = query.filter(db.Companies._id == g.company._id)
if g.client != '1':
query = query.filter(db.Companies.company_type == 'supplier')
if table == db.Company_product:
if hasattr(g, 'company'):
query = query.filter(
db.Company_product.company_id == g.company._id)
if table == db.Companybank:
if hasattr(g, 'company'):
query = query.filter(db.Companybank.company_id == g.company._id)
if table == db.Companyqualification:
if hasattr(g, 'company'):
query = query.filter(
db.Companyqualification.company_id == g.company._id)
if table == db.Companydocument:
if hasattr(g, 'company'):
query = query.filter(
db.Companydocument.company_id == g.company._id)
if table == db.DirSection:
if "local" in bag and bag["local"] is True:
products = g.tran.query(db.Product).filter_by(_deleted='infinity') \
.filter(db.Product.local == bag["local"]).all()
spec_ids = []
spec_in_ids = []
for product in products:
specification_ids = []
prodspecs = g.tran.query(db.ProductSpec).filter_by(
_deleted='infinity', product_id=product._id).all()
for prodspec in prodspecs:
if prodspec.specification_id not in specification_ids:
specification_ids.append(prodspec.specification_id)
sii = Set(spec_in_ids)
si = Set(specification_ids)
ds = sii.symmetric_difference(si)
if len(ds) > 0:
spec_ids.extend(specification_ids)
dirsections = g.tran.query(db.DirSection) \
.filter_by(_deleted='infinity').all()
dir_id = []
for dirsection in dirsections:
ds = Set(dirsection.dircategories_id)
sids = Set(spec_ids)
drs = ds.intersection(sids)
if len(drs) > 0:
dir_id.append(dirsection._id)
query = query.filter(db.DirSection._id.in_(dir_id))
elif "local" in bag and bag["local"] is False:
company_products = g.tran.query(db.Company_product).filter_by(
_deleted='infinity', status='active').all()
spec_ids = []
spec_in_ids = []
for product in company_products:
specification_ids = []
prodspecs = g.tran.query(db.ProductSpec) \
.filter_by(_deleted='infinity', product_id=product.product_id).all()
for prodspec in prodspecs:
if prodspec.specification_id not in specification_ids:
specification_ids.append(prodspec.specification_id)
sii = Set(spec_in_ids)
si = Set(specification_ids)
ds = sii.symmetric_difference(si)
if len(ds) > 0:
spec_ids.extend(specification_ids)
dirsections = g.tran.query(db.DirSection) \
.filter_by(_deleted='infinity').all()
dir_id = []
for dirsection in dirsections:
ds = Set(dirsection.dircategories_id)
sids = Set(spec_ids)
drs = ds.intersection(sids)
if len(drs) > 0:
dir_id.append(dirsection._id)
query = query.filter(db.DirSection._id.in_(dir_id))
if table == db.Typeofownership:
if 'filter' in bag and 'type_owner' in bag['filter']:
query = query.filter(
db.Typeofownership.type_owner == bag["filter"]["type_owner"])
del bag["filter"]["type_owner"]
if table == db.DirCountry:
query = query.order_by(db.DirCountry.data['index'].asc())
if "filter" in bag:
if "data" in bag["filter"] and isinstance(bag["filter"]["data"], dict):
query = query.filter(
table.data.contains(type_coerce(bag["filter"]["data"], JSONB)))
del bag["filter"]["data"]
query = query.filter_by(**bag["filter"])
if "order_by" in bag:
query = query.order_by(*bag["order_by"])
count = query.count()
if "limit" in bag:
query = query.limit(bag["limit"])
if "offset" in bag:
query = query.offset(bag["offset"])
if "with_roles" in bag and bag["with_roles"] is True:
if table == db.Companies:
roles = g.tran.query(func.json_build_object(
"id", db.Roles._id, "name", db.Roles.name, "data", db.Roles.data)).select_from(db.Roles) \
.filter_by(_deleted='infinity') \
.filter(type_coerce(db.Companies.roles_id, JSONB)
.contains(type_coerce(func.jsonb_build_array(db.Roles._id), JSONB))).as_scalar().label(
'roles')
query = query.add_columns(roles)
if "with_related" in bag and bag["with_related"] is True:
if table == db.Companies:
companybank = g.tran.query(func.jsonb_agg(func.row_to_json(text('companybank.*')))) \
.select_from(db.Companybank) \
.filter_by(_deleted='infinity').filter(db.Companybank.company_id == db.Companies._id).as_scalar() \
.label('companybank')
companuqualifications = g.tran.query(func.jsonb_agg(func.row_to_json(text('companyqualification.*')))) \
.select_from(db.Companyqualification) \
.filter_by(_deleted='infinity').filter(
db.Companyqualification.company_id == db.Companies._id).as_scalar() \
.label('companuqualifications')
roles = g.tran.query(func.json_build_object(
"id", db.Roles._id, "name", db.Roles.name)).select_from(db.Roles) \
.filter_by(_deleted='infinity') \
.filter(type_coerce(db.Companies.roles_id, JSONB)
.contains(type_coerce(func.jsonb_build_array(db.Roles._id), JSONB))).as_scalar().label(
'roles')
query = query.add_columns(companybank, companuqualifications,
roles)
elif table == db.Message:
comment = g.tran.query(func.jsonb_agg(func.row_to_json(text('comments.*')))) \
.select_from(db.Comments) \
.filter_by(_deleted='infinity').filter(db.Comments.message_id == db.Message._id).as_scalar() \
.label('comment')
query = query.add_columns(comment)
elif table == db.Companyemployees:
user = g.tran.query(func.json_build_object(
"id", db.User.id, "fullname", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date,
"data", db.User.data, "role", db.User.role)).select_from(db.User) \
.filter_by(id=db.Companyemployees.user_id) \
.as_scalar().label('user')
company = g.tran.query(func.json_build_object(
"id", db.Companies._id, "name", db.Companies.name, "inn", db.Companies.inn)).select_from(db.Companies) \
.filter_by(_deleted='infinity', _id=db.Companyemployees.company_id).as_scalar().label('company')
roles = g.tran.query(func.json_build_object(
"id", db.DirPosition._id, "name", db.DirPosition.name)).select_from(db.DirPosition) \
.filter_by(_deleted='infinity') \
.filter(type_coerce(db.Companyemployees.roles_id, JSONB)
.contains(type_coerce(func.jsonb_build_array(db.DirPosition._id), JSONB))).as_scalar().label(
'roles')
query = query.add_columns(user, company, roles)
result = orm_to_json(query.all())
if "with_related" in bag and bag["with_related"] is True:
result = find_relations(result, table_name)
if table == db.Companies:
for r in result:
if 'inn' in r and r['inn'] in ['00609201310130', '01209201710029']:
r['_created'] = '2020-07-13 00:00:00'
r['end_date'] = '2021-01-13 00:00:00'
elif 'inn' in r and r['inn'] in [
'02301201710287', '02202201310102', '01207201610238'
]:
r['_created'] = '2020-07-22 00:00:00'
r['end_date'] = '2021-01-22 00:00:00'
return {
"docs": result,
"count": count,
"current_date": datetime.datetime.today()
}
def listing(bag):
table_name = bag["type"]
table = getattr(db, table_name) if hasattr(db, table_name) else None
if table is None or not issubclass(table, (db.Base, db.CouchSync)):
raise CbsException(TABLE_NOT_FOUND)
query = g.tran.query(table._id).filter_by(_deleted='infinity')
doc_vars = vars(table)
for var in doc_vars:
if isinstance(doc_vars[var], InstrumentedAttribute):
query = query.add_column(doc_vars[var])
if table == db.Menus:
if not is_admin():
menus_id = []
roles = g.tran.query(db.Roles).filter_by(_deleted='infinity') \
.filter(db.Roles._id.in_(g.user.roles_id if g.user.roles_id is not None else [])).all()
for role in roles:
menus_id.extend(role.menus_id)
query = query.filter(db.Menus._id.in_(menus_id))
if table == db.Journals:
if not is_admin():
query = g.tran.query(
db.Journals).filter(db.Journals.entry_user_id == g.user.id)
if table == db.Queue:
dissov = g.tran.query(db.Dissov).filter_by(_deleted='infinity')\
.filter(db.Dissov.secretary == g.user.id).first()
query = query.filter(table.dissov_id == dissov._id)
if table == db.New:
if "sectionsofpublication_id" in bag:
query = query.filter(db.New.sectionsofpublication_id ==
bag["sectionsofpublication_id"])
del bag["sectionsofpublication_id"]
if table == db.Dcomposition:
if "dissov_id" in bag:
query = query.filter(db.Dcomposition.dissov_id == bag["dissov_id"])
del bag["dissov_id"]
if table == db.Theme:
if 'user_id' in bag:
query = query.filter(table.entry_user_id == bag["user_id"])
del bag["user_id"]
if 'search' in bag:
query = query.filter(
or_(func.concat(table.name,
' ')).ilike(u"%{0}%".format(bag['search'])))
del bag["search"]
if "filter" in bag:
if 'filter' in bag and 'user_id' in bag['filter']:
query = query.filter(table.user_id == bag["filter"]["user_id"])
del bag["filter"]["user_id"]
if 'filter' in bag and 'academicdegree_id' in bag['filter']:
query = query.filter(
table.academicdegree_id == bag["filter"]["academicdegree_id"])
del bag["filter"]["academicdegree_id"]
if 'filter' in bag and 'branchesofscience_id' in bag['filter']:
query = query.filter(table.branchesofscience_id == bag["filter"]
["branchesofscience_id"])
del bag["filter"]["branchesofscience_id"]
if 'filter' in bag and 'specialty_id' in bag['filter']:
query = query.filter(
table.specialty_id == bag["filter"]["specialty_id"])
del bag["filter"]["specialty_id"]
if "data" in bag["filter"] and isinstance(bag["filter"]["data"], dict):
query = query.filter(
table.data.contains(type_coerce(bag["filter"]["data"], JSONB)))
del bag["filter"]["data"]
query = query.filter_by(**bag["filter"])
if 'search' in bag:
query = query.filter(
or_(func.concat(table.name_ru, ' ', table.name_kg,
'')).ilike(u"%{}%".format(bag['search'])))
del bag["search"]
if 'user_id' in bag:
query = query.filter(table.user_id == bag["user_id"])
del bag["user_id"]
if "order_by" in bag:
query = query.order_by(*bag["order_by"])
elif bag.get(IDS) and hasattr(table, "_id"):
query = query.filter(table._id.in_(bag.get(IDS)))
count = query.count()
if "limit" in bag:
query = query.limit(bag["limit"])
if "offset" in bag:
query = query.offset(bag["offset"])
if "with_related" in bag and bag["with_related"] is True:
if table == db.Documents:
theme = g.tran.query(func.jsonb_agg(func.row_to_json(text('theme.*')))) \
.select_from(db.Theme) \
.filter_by(_deleted='infinity').filter(
db.Theme._id == db.Documents.theme_id).as_scalar() \
.label('theme')
query = query.add_columns(theme)
if table == db.Userdegree:
degreespeciality = g.tran.query(func.jsonb_agg(func.row_to_json(text('degreespeciality.*')))) \
.select_from(db.Degreespeciality) \
.filter_by(_deleted='infinity').filter(db.Degreespeciality.userdegree_id == db.Userdegree._id).as_scalar() \
.label('degreespeciality')
query = query.add_columns(degreespeciality)
if table == db.Dissov:
programs = g.tran.query(func.jsonb_agg(func.row_to_json(text('dspecialty.*'))))\
.select_from(db.Dspecialty)\
.filter_by(_deleted='infinity').filter(db.Dspecialty.dissov_id == db.Dissov._id).as_scalar()\
.label('programs')
compositions = g.tran.query(func.jsonb_agg(func.row_to_json(text('dcomposition.*')))) \
.select_from(db.Dcomposition) \
.filter_by(_deleted='infinity').filter(db.Dcomposition.dissov_id == db.Dissov._id).as_scalar() \
.label('compositions')
query = query.add_columns(programs, compositions)
if table == db.DisApplication:
application_status = g.tran.query(
func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='status') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.DisApplication.status, TEXT)) \
.as_scalar().label('application_status')
theme = g.tran.query(func.jsonb_agg(func.row_to_json(text('theme.*')))) \
.select_from(db.Theme) \
.filter_by(_deleted='infinity').filter(
db.Documents._id == db.DisApplication.document_id, db.Theme._id == db.Documents.theme_id).as_scalar() \
.label('theme')
remark = g.tran.query(func.jsonb_agg(func.row_to_json(text('disremarks.*')))) \
.select_from(db.DisRemarks) \
.filter_by(_deleted='infinity').filter(
db.DisRemarks.dissov_id == db.DisApplication.dissov_id).as_scalar() \
.label('remark')
query = query.add_columns(application_status, theme, remark)
result = orm_to_json(query.all())
if "with_related" in bag and bag["with_related"] is True:
result = find_relations(result, table_name)
return {"docs": result, "count": count}
def listing(bag):
query = g.tran.query(db.Document._id).filter_by(_deleted='infinity', company_id=g.session['company_id'])
doc_vars = vars(db.Document)
for var in doc_vars:
if isinstance(doc_vars[var], InstrumentedAttribute):
query = query.add_column(doc_vars[var])
query = query.filter(
or_(db.Document.data.contains(type_coerce({"user_id": g.user.id}, JSONB)),
db.Document.data.contains(type_coerce({"executor_id": g.user.id}, JSONB)),
type_coerce(db.Document.approval['roles_id'], JSONB).has_any(array(g.user.roles_id)) if len(
g.user.roles_id) > 0 else None))
if "own" in bag and bag["own"] is True:
query = query.filter(db.Document.data['user_id'] == g.user.id)
else:
query = query.filter(db.Document.document_status != 'draft')
if "filter" in bag:
if "data" in bag["filter"] and isinstance(bag["filter"]["data"], dict):
query = query.filter(db.Document.data.contains(type_coerce(bag["filter"]["data"], JSONB)))
del bag["filter"]["data"]
query = query.filter_by(**bag["filter"])
if "order_by" in bag:
query = query.order_by(*bag["order_by"])
else:
query = query.order_by(db.Document._created.desc())
count = query.count()
if "limit" in bag:
query = query.limit(bag["limit"])
if "offset" in bag:
query = query.offset(bag["offset"])
if 'with_related' in bag and bag['with_related'] is True:
document_status_value = g.tran.query(
func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='document_status') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Document.document_status, TEXT)) \
.as_scalar().label('document_status_value')
document_type_value = g.tran.query(
func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='document_type') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Document.document_type, TEXT)) \
.as_scalar().label('document_type_value')
roles = g.tran.query(func.jsonb_agg(func.row_to_json(text('roles.*')))).select_from(db.Roles) \
.filter_by(_deleted='infinity')\
.filter(type_coerce(db.Document.approval['roles_id'], JSONB).has_any(array([db.Roles._id])))\
.as_scalar().label('roles')
entry_user = g.tran.query(func.json_build_object(
"id", db.User.id, "username", db.User.username, "email", db.User.email, "rec_date", db.User.rec_date,
"data", db.User.data, "role", db.User.role)).select_from(db.User).filter_by(
id=db.Document.entry_user_id) \
.as_scalar().label('entry_user')
query = query.add_columns(document_status_value, document_type_value, entry_user, roles)
return {'docs': orm_to_json(query.all()), 'count': count}