def find_relations(query):
branch = g.tran.query(func.row_to_json(text('branches.*'))).select_from(db.Branches) \
.filter_by(_deleted='infinity', _id=db.Operations.branch_id).as_scalar().label('branch')
purpose = g.tran.query(func.row_to_json(text('costcenterpurposes.*'))).select_from(db.CostCenterPurposes) \
.filter_by(_deleted='infinity', _id=db.Operations.purpose_id).as_scalar().label('purpose')
document = g.tran.query(func.row_to_json(text('document.*'))).select_from(db.Document) \
.filter_by(_deleted='infinity', _id=db.Operations.document_id).as_scalar().label('document')
product = g.tran.query(func.row_to_json(text('products.*'))).select_from(db.Products) \
.filter_by(_deleted='infinity', _id=db.Operations.product_id).as_scalar().label('product')
unit = g.tran.query(func.row_to_json(text('units.*'))).select_from(db.Units) \
.filter_by(_deleted='infinity', _id=db.Operations.unit_id).as_scalar().label('unit')
currency = g.tran.query(func.row_to_json(text('currencies.*'))).select_from(db.Currencies) \
.filter_by(_deleted='infinity', _id=db.Operations.currency_id).as_scalar().label('currency')
operation_status = g.tran.query(func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='operation_status') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Operations.operation_status, TEXT)) \
.as_scalar().label('operation_status_value')
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.UserCompany.user_id) \
.as_scalar().label('user')
employee = g.tran.query(func.json_build_object('_id', db.UserCompany._id,
'_rev', db.UserCompany._rev,
'_created', db.UserCompany._created,
'user_id', db.UserCompany.user_id,
'company_id', db.UserCompany.company_id,
'entry_user_id', db.UserCompany.entry_user_id,
'branches_id', db.UserCompany.branches_id,
'roles_id', db.UserCompany.roles_id,
'access', db.UserCompany.access,
'user', user)).select_from(db.UserCompany) \
.filter_by(_deleted='infinity', company_id=db.Operations.company_id, user_id=db.Operations.employee_id)\
.as_scalar().label('employee')
contractor = g.tran.query(func.row_to_json(text('contractors.*'))).select_from(db.Contractors) \
.filter_by(_deleted='infinity', _id=db.Operations.contractor_id).as_scalar().label('contractor')
branch2 = g.tran.query(func.row_to_json(text('branches.*'))).select_from(db.Branches) \
.filter_by(_deleted='infinity', _id=db.Operations.from_branch_id).as_scalar().label('branch2')
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.Operations.entry_user_id)\
.as_scalar().label('entry_user')
query = query.add_columns(branch, purpose, document, product, unit,
currency, contractor, branch2, entry_user,
employee, operation_status)
return query
def test_function_against_subq_record(self, axy_table, connection):
"""
SELECT row_to_json(anon_1) AS row_to_json_1
FROM (SELECT a.id AS id, a.x AS x, a.y AS y
FROM a) AS anon_1
"""
stmt = select(
func.row_to_json(axy_table.select().subquery().table_valued()))
eq_(
connection.execute(stmt).scalars().all(),
[
{
"id": 1,
"x": 5,
"y": 4
},
{
"id": 2,
"x": 15,
"y": 3
},
{
"id": 3,
"x": 7,
"y": 9
},
],
)
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
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 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 test_function_against_row_constructor(self, connection):
stmt = select(func.row_to_json(func.row(1, "foo")))
eq_(connection.scalar(stmt), {"f1": 1, "f2": "foo"})
def get(bag):
advert = g.tran.query(db.Advert) \
.filter_by(_deleted='infinity', _id=bag['id']).one()
advert = orm_to_json(advert)
advert_lots = g.tran.query(db.Advert_lot).filter_by(_deleted='infinity') \
.filter(db.Advert_lot.advert_id == advert["_id"]) \
.order_by(db.Advert_lot._created.desc()).all()
advert['company'] = g.tran.query(db.Companies).filter_by(_deleted='infinity') \
.filter(db.Companies._id == advert["company_id"]).first()
advert['dirsection'] = g.tran.query(db.DirSection).filter_by(_deleted='infinity') \
.filter(db.DirSection._id == advert["dirsection_id"]).first()
if advert.get("dirprocurement_id", ''):
advert['dirprocurement'] = g.tran.query(db.DirProcurement).filter_by(_deleted='infinity') \
.filter(db.DirProcurement._id == advert["dirprocurement_id"]).first()
lot_budget = 0
debt_data = []
companies_appl = []
lind = 0
for lot in advert_lots:
lot_budget += lot.budget
lot.specifications = []
lot.dictionaries = []
lot.applications = []
lind = lind + 1
lot.ind = lind
dircategory = g.tran.query(
db.DirCategory).filter_by(id=lot.dircategory_id).all()
lot.dircategory = dircategory
lot.unit_price = lot.data['unit_price'] if lot.data.get(
'unit_price', '') else 0
if lot.dirunits_id:
lot.dirunit = g.tran.query(db.DirUnits) \
.filter_by(_deleted='infinity', _id=lot.dirunits_id).first()
lot_dictionaries = g.tran.query(db.Advert_lot_dictionaries) \
.filter_by(_deleted='infinity').filter(db.Advert_lot_dictionaries.advert_lot_id == lot._id).all()
for lot_dict in lot_dictionaries:
dict = {}
table = getattr(db, lot_dict.dirname) if hasattr(
db, lot_dict.dirname) else None
dirvalue = g.tran.query(table).filter_by(
_deleted='infinity').filter(
table._id == lot_dict.dictionary_id).first()
dir = next(d for d in tables if d['table'] == lot_dict.dirname)
dict['dirname'] = lot_dict.dirname
dict['name'] = dir['name'] if dir else ''
# dict['displayName'] = dir['name'] if dir else ''
dict['value'] = dirvalue.name
dict['values'] = [orm_to_json(dirvalue)]
lot.dictionaries.append(dict)
lot_specifications = g.tran.query(db.Advert_lot_specification) \
.filter_by(_deleted='infinity').filter(db.Advert_lot_specification.advert_lot_id == lot._id).all()
for prodspec in lot_specifications:
specs = {}
property = g.tran.query(db.SpecificationProperty) \
.filter_by(id=prodspec.specification_property_id).first()
value = g.tran.query(db.SpecificationPropertyValue) \
.filter_by(id=prodspec.specification_property_value_id).first()
specs['property'] = property
specs['value'] = value
lot.specifications.append(specs)
if advert['status'] in ['Evaluation', 'Results']:
lot_applications = g.tran.query(db.Application, db.Companies, db.Product,
(g.tran.query(db.DirCountry._id).select_from(db.ProductDict)
.join(db.DirCountry,
and_(db.ProductDict.dictionary_id == db.DirCountry._id,
db.DirCountry._deleted == INFINITY))
.filter(and_(db.ProductDict._deleted == INFINITY,
db.ProductDict.dirname == 'DirCountry',
db.ProductDict.product_id == db.Application.company_product_id))
).label('country'),
(g.tran.query(db.DirBrand._id).select_from(db.ProductDict)
.join(db.DirBrand,
and_(db.ProductDict.dictionary_id == db.DirBrand._id,
db.DirBrand._deleted == INFINITY))
.filter(and_(db.ProductDict._deleted == INFINITY,
db.ProductDict.dirname == 'DirBrand',
db.ProductDict.product_id == db.Application.company_product_id))
).label('brand')
) \
.outerjoin(db.Companies, and_(db.Application.company_id == db.Companies._id,
db.Companies._deleted == INFINITY)) \
.outerjoin(db.Product, and_(db.Application.company_product_id == db.Product._id,
db.Product._deleted == INFINITY)) \
.filter(and_(db.Application.advert_lot_id == lot._id,
db.Application._deleted == INFINITY, db.Application.status == 'Published')) \
.order_by(db.Application.total, db.Application._created) \
.all()
is_first = True
if 'dirprocurement' in advert and \
advert['dirprocurement'].with_concession and \
advert['dirprocurement'].with_concession is True:
applications_list = []
brand_label = ''
country_label = ''
for appl, company, product, country, brand in lot_applications:
dircountry = g.tran.query(db.DirCountry).filter_by(
_deleted='infinity', _id=country).first()
if g.lang == "ru":
country_label = dircountry.name
elif g.lang == "en":
country_label = dircountry.name_en if dircountry.name_en and dircountry.name_en != 'null' \
else dircountry.name
elif g.lang == "kg":
country_label = dircountry.name_kg if dircountry.name_kg and dircountry.name_kg != 'null' \
else dircountry.name
dirbrand = g.tran.query(db.DirBrand).filter_by(
_deleted='infinity', _id=brand).first()
if g.lang == "ru":
brand_label = dirbrand.name
elif g.lang == "en":
brand_label = dirbrand.name_en if dirbrand.name_en and dirbrand.name_en != 'null' \
else dirbrand.name
elif g.lang == "kg":
brand_label = dirbrand.name_kg if dirbrand.name_kg and dirbrand.name_kg != 'null' \
else dirbrand.name
ap = orm_to_json(appl)
ap['show_reason'] = True if ap['reason'] else False
ap['lot_id'] = lot._id
ap['company'] = company.name if company else ''
ap['country'] = country_label
ap['brand'] = brand_label
ap['total_concession'] = ap['total'] * ((100 - advert['concession']) / 100) \
if company.resident_state == "resident" and product.local == True else ap['total']
if lot.status != 'Canceled':
applications_list.append(ap)
lot.applications = sorted(
applications_list,
key=lambda k:
((datetime.datetime.strptime(k["_created"][:10], '%Y-%m-%d'
)), k['total_concession']))
debt_data = company_debt_check(debt_data, company,
advert["_id"])
else:
brand_label = ''
country_label = ''
for appl, company, product, country, brand in lot_applications:
dircountry = g.tran.query(db.DirCountry).filter_by(
_deleted='infinity', _id=country).first()
debt_data = company_debt_check(debt_data, company,
advert["_id"])
if g.lang == "ru":
country_label = dircountry.name
elif g.lang == "en":
country_label = dircountry.name_en if dircountry.name_en and dircountry.name_en != 'null' \
else dircountry.name
elif g.lang == "kg":
country_label = dircountry.name_kg if dircountry.name_kg and dircountry.name_kg != 'null' \
else dircountry.name
dirbrand = g.tran.query(db.DirBrand).filter_by(
_deleted='infinity', _id=brand).first()
if g.lang == "ru":
brand_label = dirbrand.name
elif g.lang == "en":
brand_label = dirbrand.name_en if dirbrand.name_en and dirbrand.name_en != 'null' \
else dirbrand.name
elif g.lang == "kg":
brand_label = dirbrand.name_kg if dirbrand.name_kg and dirbrand.name_kg != 'null' \
else dirbrand.name
ap = orm_to_json(appl)
ap['show_reason'] = True if ap['reason'] else False
ap['lot_id'] = lot._id
ap['company'] = company.name if company else ''
ap['country'] = country_label
ap['brand'] = brand_label
lot.applications.append(ap)
if advert['status'] in ['Results']:
if lot.status in ['Canceled'] and g.lang == "ru":
lot.company = 'Не состоялась'
lot.total = 'Не состоялась'
if lot.status in ['Canceled'] and g.lang == "en":
lot.company = 'Did not take place'
lot.total = 'Did not take place'
if lot.status in ['Canceled'] and g.lang == "kg":
lot.company = 'өткөрүлгөн жок'
lot.total = 'өткөрүлгөн жок'
else:
application = g.tran.query(db.Application) \
.filter_by(_deleted='infinity', advert_lot_id=lot._id, selected=True).all()
if application:
app2, company = g.tran.query(db.Application, db.Companies) \
.join(db.Companies, and_(db.Application.company_id == db.Companies._id,
db.Companies._deleted == INFINITY)) \
.filter(and_(db.Application.advert_lot_id == lot._id,
db.Application._deleted == INFINITY)) \
.filter(db.Application.selected == True).first()
lot.company = company.name if company else ''
lot.total = app2.total if app2 else ''
if 'with_related' in bag and bag['with_related'] is True:
advert_status_value = g.tran.query(
func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='advert_status') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Advert.status, TEXT)) \
.as_scalar().label('advert_status_value')
advert = advert.add_columns(advert_status_value)
comm_members = advert['data'].get('comm_members', [])
if comm_members:
for cm in comm_members:
u = g.tran.query(db.User).select_from(db.Companyemployees) \
.filter(db.Companyemployees.id == cm['id']).first()
if u:
cm[FULL_NAME] = u.fullname or ''
cm[POSITION] = u.position or ''
cm[EMAIL] = u.email or ''
cm['company'] = u.data.get('comm_member',
{}).get('company', '')
advert['lots'] = advert_lots
advert['debt_data'] = debt_data
advert['budget'] = lot_budget
return {'doc': advert}
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}
def find_relations(query):
branch = g.tran.query(func.row_to_json(text('branches.*'))).select_from(db.Branches) \
.filter_by(_deleted='infinity', _id=db.Payments.branch_id).as_scalar().label('branch')
document = g.tran.query(func.row_to_json(text('document.*'))).select_from(db.Document) \
.filter_by(_deleted='infinity', _id=db.Payments.document_id).as_scalar().label('document')
currency = g.tran.query(func.row_to_json(text('currencies.*'))).select_from(db.Currencies) \
.filter_by(_deleted='infinity', _id=db.Payments.currency_id).as_scalar().label('currency')
payment_direction = g.tran.query(func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='payment_direction') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Payments.payment_direction, TEXT)) \
.as_scalar().label('payment_direction_value')
payment_type = g.tran.query(func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='payment_type') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Payments.payment_type, TEXT)) \
.as_scalar().label('payment_type_value')
payment_status = g.tran.query(func.row_to_json(text('enums.*'))).select_from(db.Enums) \
.filter_by(_deleted='infinity', name='payment_status') \
.filter(db.Enums.data['key'].cast(TEXT) == cast(db.Payments.payment_status, TEXT)) \
.as_scalar().label('payment_status_value')
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.UserCompany.user_id) \
.as_scalar().label('user')
employee = g.tran.query(func.json_build_object('_id', db.UserCompany._id,
'_rev', db.UserCompany._rev,
'_created', db.UserCompany._created,
'user_id', db.UserCompany.user_id,
'company_id', db.UserCompany.company_id,
'entry_user_id', db.UserCompany.entry_user_id,
'branches_id', db.UserCompany.branches_id,
'roles_id', db.UserCompany.roles_id,
'access', db.UserCompany.access,
'user', user)).select_from(db.UserCompany) \
.filter_by(_deleted='infinity', company_id=db.Payments.company_id, user_id=db.Payments.data['employee_id']
.cast(INTEGER))\
.as_scalar().label('employee')
contractor = g.tran.query(func.row_to_json(text('contractors.*'))).select_from(db.Contractors) \
.filter_by(_deleted='infinity', _id=db.Payments.data['contractor_id'].cast(TEXT)) \
.as_scalar().label('contractor')
branch2 = g.tran.query(func.row_to_json(text('branches.*'))).select_from(db.Branches) \
.filter_by(_deleted='infinity', _id=db.Payments.data['branch_id'].cast(TEXT)).as_scalar().label('branch2')
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.Payments.entry_user_id)\
.as_scalar().label('entry_user')
query = query.add_columns(branch, document, currency, payment_direction,
payment_type, payment_status, contractor,
branch2, entry_user, employee)
return query
